• Личные макросы
• Создать личную книгу макросов
• Сохранить макрос в личной книге
• Где находится файл личной книги макросов PERSONAL.XLSB
• Как открыть и сделать видимой личную книгу макросов
• Скопировать код из личной книги макросов
• Удалить макрос из личной книги макросов
• Удалить личную книгу макросов
• Заключение

Я начинал свой путь в программировании с простых макросов, записанных через встроенный макрорекордер Excel. Это было удобно: нажал кнопку — Excel сам записывает действия. Со временем я узнал о личной книге макросов — способе сохранять свои процедуры так, чтобы они были всегда под рукой, в любом файле. Это стало большим шагом вперёд.

Личные макросы

При работе в Excel часто возникает необходимость автоматизировать повседневные действия: найти, удалить или изменить в таблицах определенные символы, слова, ячейки или целые диапазоны ячеек. Для этого удобно использовать макросы — они запускаются в один клик и выполняют заранее записанную последовательность команд.

Я использую макросы ежедневно. Они значительно ускоряют работу и позволяют сосредоточиться на сути задачи, а не на рутине. Каждый такой макрос создаётся под конкретные нужды: под формат моих таблиц, под логику, с которой я работаю. Это и есть личные макросы — те, что используются конкретным пользователем в его собственной среде.

Однако при записи макроса Excel по умолчанию сохраняет его в ту книгу, которая была открыта в момент записи. В результате макрос работает только в одном файле, и в других документах его уже не видно. Это неудобно: приходится копировать макросы вручную, переносить модули, следить за актуальностью и идентичностью макросов в разных файлах.

Возникает вполне логичный вопрос: где в Excel можно хранить личные макросы, чтобы они были всегда доступны? Как сделать так, чтобы они запускались в любой книге Excel, автоматически открывались при запуске, и при этом не мешали работе с документами?

Решением становится так называемая личная книга макросов. Это специальный файл, который:

• создаётся один раз,
• открывается и остается открыт, пока открыта хотя бы одна таблица Excel,
• при этом не отображается среди открытых книг (остаётся в фоне)
• сохраняет в себе пользовательские макросы, функции и другие данные.

В следующем разделе расскажу, как её создать и правильно настроить.

Создать личную книгу макросов

Чтобы личные функции (UDF) и процедуры (макросы) были всегда под рукой, нужно один раз создать личную книгу макросов. Создание такой книги начинается с самого обычного действия — записи макроса. Чтобы Excel предложил сохранить макрос в личной книге, достаточно воспользоваться встроенной записью макросов: выбрать во вкладке «Вид» или «Разработчик» пункт «Запись макроса», указать имя и в качестве места сохранить выбрать «Личная книга макросов».

Например, можно записать изменение цвета заливки ячейки или изменение ширины столбца. Можно и вовсе ничего не выполнять и остановить запись сразу после старта записи. Главное — не само действие, а запуск механизма создания. Excel автоматически создаст файл PERSONAL.XLSB, добавит модуль в редактор VBA и предложит сохранить изменения при выходе из программы.

Создание личной книги — это базовый шаг. Один раз сделать — и больше не возвращаться к вопросу, где хранить свои макросы. В следующих разделах я расскажу, где именно находится этот файл, как его открыть для редактирования, как скопировать, удалить или перенести макросы.

Сохранить макрос в личной книге

После того, как личная книга макросов создана, в неё можно сохранять любые процедуры — как записанные вручную, так и написанные в редакторе VBA. Это удобно: макрос сохраняется один раз и остаётся доступным при работе с любым файлом Excel.

Второй способ — напрямую вставить код через редактор VBA. Для этого нужно открыть редактор (Alt+F11), найти в списке проектов PERSONAL.XLSB, добавить новый модуль (если нужно), и вставить в него процедуру. Здесь удобно хранить как простые, так и многофункциональные макросы, сгруппированные по задачам.

Каждое добавление макроса — это обновление файла PERSONAL.XLSB. Он сохраняется при выходе из Excel, поэтому важно при закрытии программы соглашаться на предложение о сохранении книги. В противном случае новые макросы не будут записаны в файл.

Где находится файл личной книги макросов PERSONAL.XLSB

После первого сохранения файл личной книги макросов будет храниться в специальной системной папке Excel. Файл называется PERSONAL.XLSB и лежит в директории автозагрузки Excel. Макросы, записанные в личную книгу макросов, вставляются именно в этот файл. Именно оттуда он автоматически загружается при каждом запуске Excel.

Этот путь можно узнать через редактор VBA или через параметры Excel — он зависит от версии операционной системы и самого MS Excel.

Путь к этой папке в операционной системе Windows чаще всего выглядит так:

C:\Users\Имя_пользователя\AppData\Roaming\Microsoft\Excel\XLSTART

В этом расположении Excel ищет и открывает все файлы, которые должны запускаться при старте. PERSONAL.XLSB загружается оттуда в фоне, оставаясь скрытым. Он не отображается среди обычных документов, хотя технически считается открытым. Если зайти в редактор VBA (Alt+F11), можно увидеть, что файл открыт и активен.

Найти его вручную можно через проводник Windows, предварительно включив отображение скрытых папок. Также путь можно посмотреть в настройках Excel или получить через VBA.

Универсальный путь к папке через переменную окружения %appdata% (вставить в путь проводника Windows):

%appdata%\Microsoft\Excel\XLSTART

Если вы еще не создали книгу макросов, папка может отсутствовать, и попытка перейти по указанному пути выдаст ошибку.

Важно помнить: файл PERSONAL.XLSB должен всегда находиться именно в этой папке автозагрузки. Если он будет перемещён, переименован или удалён — личные макросы перестанут загружаться при открытии Excel.

Как открыть и сделать видимой личную книгу макросов

Личная книга макросов — это обычный файл Excel с расширением .xlsb. Он ничем не отличается от других рабочих книг: у него есть листы, можно добавлять данные, формулы, сохранять таблицы. Отличие только в том, что Excel при запуске скрывает этот файл по умолчанию.

Файл персональной книги макросов действительно открыт каждый раз, когда запускается Excel, но остаётся в фоновом режиме. Чтобы его отобразить, нужно перейти на вкладку «Вид» и нажать «Отобразить». В списке окон будет PERSONAL.XLSB. После выбора книга станет видимой, как и любая другая.

Если отобразить книгу PERSONAL.XLSB, с ней можно работать, как с обычной книгой: вводить данные на лист, вставлять справочные таблицы, сохранять заметки, использовать формулы. Всё это сохранится вместе с макросами. Хотя чаще всего в этой книге не хранят данные, технически это не запрещено — просто не принято.

Когда работа завершена, PERSONAL.XLSB можно снова скрыть через ту же команду «Скрыть». Excel запомнит это состояние и в следующий раз откроет файл в скрытом виде. Это удобно: макросы доступны, но книга не мешает на экране.

Редактировать макросы внутри PERSONAL.XLSB можно через редактор VBA (Alt+F11), как и в любой другой книге. При необходимости можно добавить модули, удалить процедуры, изменить код — всё это делается напрямую внутри файла.

Скопировать код из личной книги макросов

Макросы из личной книги макросов можно легко скопировать и перенести в другие файлы Excel. Это удобно, если нужно передать готовое решение коллеге, встроить макрос в рабочую книгу или собрать собственную библиотеку функций и/или процедур.

Для этого нужно

1. открыть редактор VBA (Alt+F11),
2. найти в списке проектов PERSONAL.XLSB, а в нём – список модулей, где хранятся макросы.
3. скопировать нужные функции и процедуры:
   • выделяем нужный код,
   • копируем Ctrl+C и
   • вставляем (Ctrl+V) в модуль другой книги.

Если в целевой книге модуля ещё нет, его можно добавить через меню «Вставка» → «Модуль».

Аналогично работает и обратный процесс: если есть готовый макрос в другой книге Excel, его можно вставить в личную книгу макросов, чтобы он был всегда доступен.

Вместо копирования отдельных процедур можно перенести целый модуль. Перенесите обычным перетаскиванием модуль в структуре проектов редактора VBA — например, из книги Book1.xlsm в PERSONAL.XLSB. Так сохраняется вся структура кода, включая вспомогательные процедуры и комментарии. При этом исходный код останется, но в результирующей книге будет создан идентичный модуль.

Если макрос нужно перенести на другой компьютер, я копирую файл PERSONAL.XLSB из системной папки автозагрузки (XLSTART) и переношу его в ту же папку на новом устройстве. Главное — убедиться, что Excel настроен на автозагрузку из этой папки, и макросы не блокируются настройками безопасности.

Удалить макрос из личной книги макросов

Если макрос в личной книге больше не нужен, его можно удалить напрямую через редактор VBA. Я обычно открываю редактор (Alt+F11), нахожу в проекте PERSONAL.XLSB нужный модуль, выбираю процедуру и просто удаляю её код. Если модуль содержит только один макрос, можно удалить весь модуль: правый клик по модулю → «Удалить модуль». Excel предложит сохранить копию на случай, если код может еще понадобиться. Рекомендую не пренебрегать возможностью.

Удаление макроса не требует пересохранения файла вручную — Excel спросит, сохранить ли изменения в PERSONAL.XLSB при выходе. Если согласиться, изменения вступают в силу.

Удалить личную книгу макросов

Если нужно удалить не отдельный макрос, а всю личную книгу:

1. закройте Excel,
2. перейдите в папку, где лежит файл PERSONAL.XLSB,
3. удалите его как обычный файл.

После этого Excel перестанет открывать личную книгу при запуске, и все сохранённые в ней макросы станут недоступны.

Заключение

Личная книга макросов — это удобное решение, если вы хотите, чтобы ваши макросы были доступны в любой книге Excel. Она позволяет записывать и хранить персональные процедуры, ускоряя повседневную работу. Когда функций и процедур немного, это идеальный вариант.

Но однажды мне перестало хватать возможностей встроенного макрорекордера и личной книги макросов. Процедур и функций стало слишком много, и вызывать их через линейный список стало затруднительно. Хотелось более user-friendly решение — с четкой иерархией процедур, меню, кнопочками, чекбоксами и т.д. :)

Так началась работа над надстройкой !SEMTools для Excel — инструментом, который вырос из потребности автоматизировать повседневные задачи и сделать Excel удобнее для себя и других.

Что делает эта функция?

Функция КОНМЕСЯЦА возвращает последнюю дату месяца, смещённую на заданное количество месяцев от исходной даты.

Если смещение равно 0, будет возвращён конец того же месяца. Если 1 — конец следующего, -1 — конец предыдущего месяца.

Форматирование

Результат — это дата. Она может отображаться в стандартном или произвольном формате. При необходимости можно изменить формат отображения через Ctrl + 1.

Синтаксис

=КОНМЕСЯЦА(дата; смещение_в_месяцах)

Аргументы:

• дата — обязательный аргумент. Это может быть:
  • ссылка на ячейку с датой
  • результат другой функции, например СЕГОДНЯ()
  • текстовое значение, которое Excel распознаёт как дату
• смещение_в_месяцах — обязательное целое число (может быть отрицательным), указывающее, на сколько месяцев сдвинуться вперёд или назад

Примеры

Тройной клик по формуле выделит её всю для копирования.

Особенности

• Если дата введена некорректно, Excel вернёт ошибку.
• Учитываются високосные года: в феврале может быть 28 или 29 дней.
• Функция полезна для расчёта периодов, финансовых отчётов, дат закрытия месяца, расчёта отсрочек и пр.
• Несмотря на то, что Excel считает 1900 год високосным, и в нем существует 29 февраля 1900 года, функция КОНМЕСЯЦА для всех дат февраля 1900 (даже 29 февраля) возвращает 28 февраля.

Автоматизация через !SEMTools

Если вы хотите получить последнюю дату месяца сразу для всего столбца, можно воспользоваться командой на вкладке !SEMTools.

Как использовать:

• Выделите столбец с датами;
• Откройте вкладку !SEMTools;
• Перейдите в меню “ИЗВЛЕЧЬ” – “Атрибуты” – дат
• Выберите команду «Конец месяца».

Если активен чекбокс “выводить результат справа“, рядом появится столбец с последними датами соответствующих датам месяцев. Вставляются только значения, без формул.

Другие функции “Дата и время” в Excel

ВРЕМЗНАЧ, ВРЕМЯ, ГОД, ДАТА, ДАТАЗНАЧ, ДАТАМЕС, ДЕНЬ, ДЕНЬНЕД, ДОЛЯГОДА, МЕСЯЦ, МИНУТЫ, НОМНЕДЕЛИ и НОМНЕДЕЛИ.ISO, РАБДЕНЬ, РАЗНДАТ, СЕГОДНЯ, СЕКУНДЫ, ТДАТА, ЧАС, ЧИСТРАБДНИ

Что делает эта функция?

Функция МЕСЯЦ возвращает номер месяца из переданной даты. Результатом будет число от 1 до 12.

Примеры:

• 17 июня 2025 → 6
• 01.01.2024 → 1

Функция используется для извлечения месяца из полной даты и последующей группировки, фильтрации, сравнения, сортировки.

Синтаксис

=МЕСЯЦ(дата)

Форматирование

Функция возвращает обычное целое число. При необходимости можно настроить числовой формат ячейки (например, убрать десятичные знаки), но чаще всего это не требуется.

Если вы хотите получить название месяца (например, «январь»), используйте функцию ТЕКСТ:

=ТЕКСТ(A2;"мммм")

Аргумент:

• дата — обязательный аргумент. Это может быть:
  • ссылка на ячейку с датой
  • дата, возвращаемая другой функцией (например, СЕГОДНЯ())
  • текстовая строка, которую Excel может преобразовать в дату (например, “2025-12-31”)

Примеры

=МЕСЯЦ(A2)

=МЕСЯЦ(СЕГОДНЯ())

Особенности

• Если дата введена как текст, Excel должен уметь её распознать. Иначе возникнет ошибка.
• Функция не возвращает название месяца, только его номер.
• Если дата не указана или ячейка пуста, результат будет 0.

Другие функции “Дата и время” в Excel

ВРЕМЗНАЧ, ВРЕМЯ, ГОД, ДАТА, ДАТАЗНАЧ, ДАТАМЕС, ДЕНЬ, ДЕНЬНЕД, ДОЛЯГОДА, КОНМЕСЯЦА, МИНУТЫ, НОМНЕДЕЛИ и НОМНЕДЕЛИ.ISO, РАБДЕНЬ, РАЗНДАТ, СЕГОДНЯ, СЕКУНДЫ, ТДАТА, ЧАС, ЧИСТРАБДНИ

Что делает эта функция?

Функция ГОД извлекает четырехзначный номер года из переданной даты. Например, из даты 15.03.2025 будет возвращено значение 2025.

Примеры:

• 01.01.2024 → 2024
• 31.12.1999 → 1999

Функция полезна, если нужно проанализировать распределение данных по годам, построить сводные таблицы, сгруппировать события или продажи по календарному году.

Форматирование

Результат — целое число. При необходимости можно изменить числовой формат ячейки, но чаще всего это не требуется.

Если требуется отобразить год как текст, можно использовать функцию ТЕКСТ:

=ТЕКСТ(A2;"гггг")

Синтаксис

=ГОД(дата)

• дата — обязательный аргумент. Может быть:
  • ссылка на ячейку с датой
  • результат функции (например, СЕГОДНЯ() или ТДАТА())
  • текст, который Excel распознает как дату (например, “2025-03-15”)

Примеры

Особенности

• Функция возвращает только номер года, без месяца и дня.
• Если ячейка пуста или содержит текст, который Excel не может распознать как дату, результат будет ошибка.
• Excel рассматривает даты, начиная с 1 января 1900 года.

Другие функции “Дата и время”

ВРЕМЗНАЧ, ВРЕМЯ, ДАТА, ДАТАЗНАЧ, ДАТАМЕС, ДЕНЬ, ДЕНЬНЕД, ДОЛЯГОДА, КОНМЕСЯЦА, МЕСЯЦ, МИНУТЫ, НОМНЕДЕЛИ и НОМНЕДЕЛИ.ISO, РАБДЕНЬ, РАЗНДАТ, СЕГОДНЯ, СЕКУНДЫ, ТДАТА, ЧАС, ЧИСТРАБДНИ

Что делает эта функция?

Функция ДЕНЬНЕД возвращает номер дня недели, соответствующий дате. В зависимости от выбранного типа возврата, неделя может начинаться с воскресенья, понедельника или другого дня.

Например:

• Понедельник → 2 (если неделя начинается с воскресенья)
• Понедельник → 1 (если неделя начинается с понедельника)

Синтаксис

=ДЕНЬНЕД(дата;[тип])

Аргументы:

• дата — обязательный аргумент. Это может быть:
  • ссылка на ячейку с датой
  • результат другой функции, например СЕГОДНЯ()
  • текстовая дата (например, “2025-06-17”), если Excel может её распознать
• тип — необязательный аргумент, определяющий, с какого дня начинается неделя и какие значения будут возвращены. Если не указан, используется тип 1.

Варианты типа:

Примеры

Форматирование

Функция возвращает число от 1 до 7. Это числовое значение можно использовать для сортировки, фильтрации или построения графиков. Чтобы отобразить название дня недели, используйте функцию ТЕКСТ:

=ТЕКСТ(A2;"дддд")

Например, формула выше вернёт «воскресенье» или «понедельник» в зависимости от даты. Важно понимать, что аргументом функции ТЕКСТ нужно делать исходную дату, а не число, соответствующее дню недели.

Особенности

• Если аргумент дата не является допустимой датой, возвращается ошибка.
• Тип 2 (понедельник = 1) чаще используется в деловой и европейской практике.
• Чтобы отобразить день недели текстом, используйте функцию ТЕКСТ с форматом “дддд”.

Другие функции “Дата и время” в Excel

ВРЕМЗНАЧ, ВРЕМЯ, ГОД, ДАТА, ДАТАЗНАЧ, ДАТАМЕС, ДЕНЬ, ДОЛЯГОДА, КОНМЕСЯЦА, МЕСЯЦ, МИНУТЫ, НОМНЕДЕЛИ и НОМНЕДЕЛИ.ISO, РАБДЕНЬ, РАЗНДАТ, СЕГОДНЯ, СЕКУНДЫ, ТДАТА, ЧАС, ЧИСТРАБДНИ

Что делает функция СЕГОДНЯ()?

Функция СЕГОДНЯ возвращает текущую системную дату компьютера, на котором открыт файл. Время при этом не учитывается — результатом будет только дата.

Функция используется при расчётах с датами: для сравнения, автоматического определения просроченных задач, динамических отчётов и календарей.

Форматирование

По умолчанию Excel отображает результат в формате даты. При необходимости можно изменить формат вручную (например, на числовой или текстовый).

Чтобы получить текстовый вид текущей даты, используйте функцию ТЕКСТ:

=ТЕКСТ(СЕГОДНЯ();"дд.ММ.гггг")

Синтаксис

=СЕГОДНЯ()

Аргументы:

Функция не требует аргументов. Но скобки “()” обязательны.

Примеры

Особенности

• Функция пересчитывается каждый раз при открытии файла или при любом обновлении листа.
• Чтобы сохранить «снимок» даты, скопируйте ячейку с формулой и вставьте “как значения”.
• Функция зависит от системных настроек даты на компьютере пользователя.

Другие функции “Дата и время” в Excel

ВРЕМЗНАЧ, ВРЕМЯ, ГОД, ДАТА, ДАТАЗНАЧ, ДАТАМЕС, ДЕНЬ, ДЕНЬНЕД, ДОЛЯГОДА, КОНМЕСЯЦА, МЕСЯЦ, МИНУТЫ, НОМНЕДЕЛИ и НОМНЕДЕЛИ.ISO, РАБДЕНЬ, РАЗНДАТ, СЕКУНДЫ, ТДАТА, ЧАС, ЧИСТРАБДНИ

Что делает эта функция?

Функция ТДАТА, несмотря на её название, возвращает не только текущую дату. Её возвращает функция СЕГОДНЯ. Наша же героиня возвращает и дату и время – в виде одного значения. Недаром её название на английском – NOW, что переводится как “сейчас”.

Почему функцию, которая возвращает дату и время сейчас, не назвали “СЕЙЧАС”, остается загадкой.

Как и СЕГОДНЯ, функция ТДАТА волатильна — её результат обновляется каждый раз при изменении или пересчёте рабочего листа, а также при открытии файла.

Пример возвращаемого значения:

17.06.2025 14:45

Используется в отчётах, в логах, при отметке времени изменения данных или расчётах сроков.

Текущее дата и время с точностью до миллисекунд

Функция ТДАТА возвращает текущее время с точностью до 10 миллисекунд(!). Вряд ли вам такое потребуется, но всё же :)

Чтобы показать текущее время с такой точностью, используйте функцию ТЕКСТ:

=ТЕКСТ(ТДАТА();"ДД.ММ.ГГГГ чч:мм:сс,000")

или

=ТЕКСТ(ТДАТА();"ДД.ММ.ГГГГ чч:мм:сс.000")

в зависимости от того, какой у вас разделитель указан в настройках Excel – точка или запятая.

Форматирование

По умолчанию Excel отображает и дату, и время. Чтобы оставить только время или только дату, нужно изменить формат ячейки.

Пример формулы для вывода только времени в виде текста:

=ТЕКСТ(ТДАТА();"чч:мм")

Для даты:

=ТЕКСТ(ТДАТА();"ДД.ММ.ГГГГ")

Синтаксис

=ТДАТА()

Аргументы:

Функция не принимает аргументов. Скобки обязательны.

Примеры

Особенности

• Функция волатильна, иными словами, обновляется автоматически при открытии книги и любом изменении данных или событии в ней, если в книге включены автоматические вычисления (автопересчёт формул).
• Чтобы зафиксировать значение, скопируйте ячейку и используйте вставку “как значения”.
• Фактический результат функции — число с дробной частью: целая часть — дата, дробная — время.

Другие функции “Дата и время” в Excel

ВРЕМЗНАЧ, ВРЕМЯ, ГОД, ДАТА, ДАТАЗНАЧ, ДАТАМЕС, ДЕНЬ, ДЕНЬНЕД, ДОЛЯГОДА, КОНМЕСЯЦА, МЕСЯЦ, МИНУТЫ, НОМНЕДЕЛИ и НОМНЕДЕЛИ.ISO, РАБДЕНЬ, РАЗНДАТ, СЕГОДНЯ, СЕКУНДЫ, ЧАС, ЧИСТРАБДНИ

Что делает функция ДЕНЬ?

Функция ДЕНЬ возвращает день месяца из переданной даты. Результатом всегда будет число от 1 до 31.

Примеры:

• =ДЕНЬ(“2025-06-17”) → 17
• =ДЕНЬ(A2) → если в A2 дата 01.01.2024, результат будет 1

Эта функция полезна, если нужно сгруппировать или отфильтровать данные по числам месяца, например — по всем событиям, произошедшим «первого числа».

Форматирование

ДЕНЬ() возвращает целое число — его можно сразу использовать в вычислениях, фильтрации или визуализации. Никакого дополнительного форматирования не требуется.

Если результат отображается как дата, просто измените формат ячейки на «Общий» или «Числовой» (Ctrl+1 → Число).

Синтаксис

=ДЕНЬ(дата)

Дата — единственный и обязательный аргумент функции. Может быть:

• ссылкой на ячейку с датой (A1);
• результатом другой функции (СЕГОДНЯ(), ДАТА(2025;1;1));
• текстовой строкой с датой (например, “2025-01-01”), хотя это не рекомендуется.

Примеры использования

=ДЕНЬ(A2)

=ДЕНЬ(СЕГОДНЯ())

=ДЕНЬ("2025-12-31")

Особенности

• Функция ДЕНЬ возвращает только номер дня месяца, независимо от формата отображения даты.
• Если аргумент — нераспознаваемый текст, Excel выдаст ошибку #ЗНАЧ!.
• Если передана пустая ячейка, результат будет 0.

Другие функции “Дата и время” в Excel

ВРЕМЗНАЧ, ВРЕМЯ, ГОД, ДАТА, ДАТАЗНАЧ, ДАТАМЕС, ДЕНЬНЕД, ДОЛЯГОДА, КОНМЕСЯЦА, МЕСЯЦ, МИНУТЫ, НОМНЕДЕЛИ и НОМНЕДЕЛИ.ISO, РАБДЕНЬ, РАЗНДАТ, СЕГОДНЯ, СЕКУНДЫ, ТДАТА, ЧАС, ЧИСТРАБДНИ

Что делает эта функция?

Функция СЧЁТЕСЛИ в Excel используется для подсчета количества ячеек в диапазоне, которые удовлетворяют определенному критерию. СЧЁТЕСЛИ очень полезна, когда вам нужно узнать, сколько раз встречается в наборе данных определенное значение. Этим она может значительно упростить процесс обработки больших наборов данных в Excel.

Синтаксис функции

=СЧЁТЕСЛИ(диапазон, критерий)

диапазон: Это диапазон ячеек, который вы хотите проверить.
критерий: Это условие, которое должны удовлетворять ячейки в диапазоне. Критерий может быть числом, текстовым выражением, ячейкой со значением или формулой.

Критерии и операторы функции СЧЁТЕСЛИ

Для функции СЧЁТЕСЛИ в Excel в качестве критериев могут выступать следующие объекты:

• Напрямую заданные значения в различных форматах:
  • Числа: Прямое указание числа, например, 10 или 3.14.
  • Текстовые строки: Любые текстовые значения, заключенные в кавычки, например, “Слово” или “Текст”.
  • Логические значения: ИСТИНА или ЛОЖЬ.
  • Даты: Могут использоваться даты в формате, который Excel распознает как дату, например, “2024-01-01” или DATE(2024, 1, 1).
  • Проценты, например, “25%”, “>50%”
• Ячейки: Ссылка на ячейку, содержащую критерий. Например, если критерий находится в ячейке A1, то можно указать A1 как критерий.
• Диапазоны ячеек (тогда функция возвращает массив, равный диапазону, указываемому в качестве критерия).
• Формулы: Критерий может быть задан формулой, результат которой используется для сравнения с ячейками диапазона.

Важно учесть, что критерий должен быть задан в соответствии с типом данных в анализируемом диапазоне, чтобы функция СЧЁТЕСЛИ работала корректно.

Операторы функции СЧЁТЕСЛИ

Операторы условно делятся на числовые и текстовые. Нужно учитывать, что СЧЁТЕСЛИ:

• может использовать числовые операторы сравнения для текста (например, Б > А, а ББ > БА)
• но при этом не умеет использовать текстовые операторы для чисел (например, 200 входит в 2002 как текст, но если формат значения не текстовый, будет возвращаться 0)

Во втором случае вам может пригодиться функция ТЕКСТ для преобразования чисел в текстовый формат.

Числовые операторы

1. Больше (>): Подсчитывает ячейки, содержащие значения больше указанного.
2. Меньше (<): Подсчитывает ячейки, содержащие значения меньше указанного.
3. Больше или равно (>=): Подсчитывает ячейки, содержащие значения больше или равные указанному.
4. Меньше или равно (<=): Подсчитывает ячейки, содержащие значения меньше или равные указанному.
5. Равно (=): Подсчитывает ячейки, строго равные указанному значению. Обычно оператор “=” опускается, так как он подразумевается по умолчанию.
6. Не равно (<>): Подсчитывает ячейки, которые не равны указанному значению.

Эти операторы используются в сочетании с числовым значением или ссылкой на ячейку в качестве критерия для подсчета количества ячеек, удовлетворяющих определенному условию.

Текстовые операторы

Функция СЧЁТЕСЛИ также поддерживает и текстовые операторы (они же подстановочные символы). Они позволяют осуществлять подсчёт ячеек по шаблону (в профессиональном сленге также присутствует выражение “по маске“). Текстовые операторы работают и с числовыми значениями. Всего таковых два:

• Знак вопроса (?): Используется в критериях для замены одного любого символа. Например, критерий “а?” будет соответствовать любому двухбуквенному тексту, который начинается с “а”.
• Звездочка (*): Используется для замены любой последовательности символов, включая их отсутствие. Например, критерий “а*” будет соответствовать любому тексту, который начинается на “а” или равен “а“.

“?*” или “*?” в качестве критерия позволит посчитать ячейки, содержащие текстовые символы в диапазоне, т.к. текстовый критерий не сработает на числовых и логических значениях.

Примеры использования функции

Давайте рассмотрим подробно формулы, указанные вверху страницы. Перед нами каталог товаров интернет-магазина электроники с указанием их категории и ценами. Наша задача определить количество позиций, удовлетворяющих некоторым числовым или текстовым критериям.

Примеры с текстовыми критериями

Допустим, нам нужно посчитать товары с вхождением определенного бренда в их названии. Тогда нам потребуется использовать следующие формулы:

Сами формулы находятся в ячейках C14 и C15. Для удобства копирования, вот они:

=СЧЁТЕСЛИ(A2:A11, *Samsung*)

=СЧЁТЕСЛИ(A2:A11, *&B15&*)

Отличие второй формулы от первой в том, что её можно больше не редактировать, а изменять значение ячейки, от которой она зависит. Еще удобнее, если во влияющей ячейке будет выпадающий список брендов. Звёздочки в формуле позволяют искать текст по вхождению, а не полному совпадению.

При поиске по полному совпадению ячейки, например, по категории товара, операторы не требуются. По умолчанию поиск осуществляется именно по нему.

Соответствующие формулы:

=СЧЁТЕСЛИ(B2:B11;"Аксессуары")

=СЧЁТЕСЛИ(B2:B11;B17)

Здесь вторая формула отличается от первой аналогично предыдущему примеру.

Примеры формул с числовыми критериями

Когда нужно посчитать ячейки, удовлетворяющие некоторым числовым критериям, главная особенность в том, что, несмотря на то, что они числовые, указываются они внутри кавычек, как строковый параметр.

Ниже текст соответствующих формул для удобства копирования.

1. =СЧЁТЕСЛИ(C2:C11;">3000")

2. =СЧЁТЕСЛИ(C2:C11;">"&B19)

3. =СУММ(--(ABS(D2:D11/C2:C11-1)>20%))

Обратите внимание на третью формулу. Это формула массива, и она используется здесь по той причине, что функция СЧЁТЕСЛИ не работает с массивами. Т.е. нельзя разделить значения двух диапазонов друг на друга и сравнить их с целевым значением скидки в массиве с её помощью.

Абсолютное значение скидки (его возвращает функция ABS) сравнивается с целевым значением 20%, и массив, состоящий из логических значений ИСТИНА и ЛОЖЬ, превращается в нули и единицы методом двойного отрицания. Далее функция СУММ возвращает сумму единиц.

4. =СЧЁТЕСЛИ(E2:E11;"*99")

Четвертая формула также имеет свою особенность. Если бы мы применили её на числовых ячейках в столбце D, она вернула бы 0. Поэтому нам потребовался дополнительный столбец, в котором функция ТЕКСТ перевела числа в текстовый формат.

Ещё примеры

Хотите больше примеров использования функции? Ниже список статей на этом сайте, где функция СЧЁТЕСЛИ используется как вспомогательная в сочетании с другими:

Посчитать слова в ячейках Excel

Посчитать ячейки с цифрами

N-gram анализ в Excel

Поиск повторяющихся значений в диапазоне