Для работы и учёбы полезны формулы Excel, которые считают значения по условиям, находят данные, очищают текст, рассчитывают даты и обрабатывают ошибки. Ниже - 15 функций с примерами, требованиями к исходным данным и способами проверки. Формулы приведены для русской версии Excel; разделитель аргументов обычно точка с запятой.
Какие формулы Excel закрывают самые частые задачи
- Для подсчёта и суммирования по одному или нескольким условиям используйте СЧЁТЕСЛИ, СЧЁТЕСЛИМН, СУММЕСЛИ и СУММЕСЛИМН.
- Чтобы находить значения в таблицах, подходят ВПР, ИНДЕКС и ПОИСКПОЗ.
- СЖПРОБЕЛЫ очищает лишние пробелы, СЦЕПИТЬ собирает текст, а ТЕКСТ задаёт отображение числа или даты.
- РАБДЕНЬ рассчитывает рабочую дату, РАЗНДАТ - промежуток между датами.
- ЕСЛИ задаёт проверку условия, ЕСЛИОШИБКА помогает обработать ошибочный результат.
Подсчёт и суммирование по условиям: СЧЁТЕСЛИ, СУММЕСЛИ и их аналоги
Эти формулы подходят, когда нужно посчитать записи или сложить суммы по категории, статусу, дате либо нескольким признакам. Не применяйте их к непроверенным диапазонам: лишние строки, текст вместо чисел и несогласованные размеры диапазонов могут исказить результат.
Поиск данных в таблицах: ВПР, ИНДЕКС и ПОИСКПОЗ
Нужны таблица с уникальным ключом, одинаковый формат ключей в исходных данных и диапазоне поиска, а также точное совпадение, если требуется найти конкретный код. ВПР ищет ключ в первом столбце выбранного диапазона; связка ИНДЕКС и ПОИСКПОЗ позволяет искать столбец результата независимо от его положения относительно ключа.
Очистка и сборка текста: СЖПРОБЕЛЫ, СЦЕПИТЬ и ТЕКСТ
Перед выполнением шагов проверьте исходные данные: формулы не заменяют резервную копию и не исправляют все виды ошибок ввода.
- Сохраните отдельную копию книги или исходный столбец перед массовой очисткой.
- СЖПРОБЕЛЫ удаляет лишние обычные пробелы, но не гарантирует удаление неразрывных пробелов и других невидимых символов.
- ТЕКСТ преобразует число или дату в текстовое представление: такой результат может вести себя иначе при сортировке и вычислениях.
- СЦЕПИТЬ может быть недоступна или помечаться как устаревающая в некоторых версиях Excel; в новых версиях для объединения текста доступны другие функции.
- Очистите пробелы. Если текст находится в A2, введите в B2 формулу
=СЖПРОБЕЛЫ(A2). Сравните результат с исходной ячейкой, прежде чем заменять исходные данные. - Объедините фрагменты. Чтобы собрать имя из ячеек A2 и B2 с пробелом между частями, используйте
=СЦЕПИТЬ(A2;" ";B2). Проверьте, что пустые ячейки не создают нежелательные пробелы. - Задайте формат текстом. Для отображения даты из A2 в виде год-месяц-день используйте
=ТЕКСТ(A2;"гггг-мм-дд"). Убедитесь, что A2 содержит дату, а не строку, которая лишь выглядит как дата. - Проверьте результат на нескольких строках. Протяните формулу вниз и проверьте обычные, пустые и нестандартные значения. Только после проверки решайте, нужно ли копировать результаты и вставлять их как значения.
Даты и сроки: РАБДЕНЬ и РАЗНДАТ для учебных и рабочих графиков
РАБДЕНЬ вычисляет дату после заданного количества рабочих дней, а РАЗНДАТ возвращает разницу между двумя датами в выбранных единицах. Примеры: =РАБДЕНЬ(A2;10) добавляет к дате в A2 десять рабочих дней без учёта праздничных дней; =РАЗНДАТ(A2;B2;"d") считает число дней между датами.
- Убедитесь, что исходные значения распознаны Excel как даты.
- Для РАБДЕНЬ проверьте, нужно ли учитывать праздничные дни; при необходимости передайте диапазон праздников третьим аргументом.
- Проверьте, что начальная дата стоит раньше конечной, если рассчитываете промежуток через РАЗНДАТ.
- Для РАЗНДАТ задайте подходящую единицу: например, "d" для дней или "m" для полных месяцев.
- Сверьте полученную дату с календарём, учитывая выходные и используемый список праздников.
- Если формула возвращает ошибку, проверьте формат ячеек и порядок дат.
Логика и обработка ошибок: ЕСЛИ и ЕСЛИОШИБКА
ЕСЛИ выбирает результат в зависимости от условия. Например, =ЕСЛИ(B2>=60;"зачёт";"пересдать") выводит отметку по значению в B2. ЕСЛИОШИБКА возвращает заданный результат вместо ошибки: =ЕСЛИОШИБКА(A2/B2;"проверьте данные").
Частые причины неверного результата:
- Условие ЕСЛИ сравнивает текст с числом или проверяет не ту ячейку.
- В формуле пропущен разделитель аргументов или скобка.
- Текстовый результат не заключён в кавычки.
- Ссылки на ячейки смещаются после копирования формулы; при необходимости закрепите ссылку знаками доллара.
- ЕСЛИОШИБКА скрывает исходную проблему, поэтому на этапе проверки лучше выяснить причину ошибки.
- В ячейке число хранится как текст, из-за чего сравнение или вычисление работает не так, как ожидалось.
Как выбрать формулу и проверить результат на ошибках
В таблице собраны все 15 формул Excel для работы и учёбы. Названия некоторых функций, доступность и разделители аргументов зависят от языка интерфейса и версии Excel.
| Задача | Формула | Пример | Назначение и примечание |
|---|---|---|---|
| Подсчёт | СЧЁТ | =СЧЁТ(B2:B10) |
Считает ячейки с числами. |
| Подсчёт | СЧЁТЕСЛИ | =СЧЁТЕСЛИ(A2:A10;"Сдано") |
Считает ячейки по одному условию. |
| Подсчёт | СЧЁТЕСЛИМН | =СЧЁТЕСЛИМН(A2:A10;"Сдано";B2:B10;">=60") |
Считает строки по нескольким условиям. |
| Суммирование | СУММЕСЛИ | =СУММЕСЛИ(A2:A10;"Книги";B2:B10) |
Суммирует значения по одному условию. |
| Суммирование | СУММЕСЛИМН | =СУММЕСЛИМН(C2:C10;A2:A10;"Книги";B2:B10;"Оплачено") |
Суммирует значения по нескольким условиям. |
| Поиск | ВПР | =ВПР(E2;A2:C10;3;ЛОЖЬ) |
Ищет E2 в первом столбце диапазона и возвращает значение из третьего столбца; требует подходящей структуры таблицы. |
| Поиск | ИНДЕКС | =ИНДЕКС(B2:B10;3) |
Возвращает значение по позиции в диапазоне. |
| Поиск | ПОИСКПОЗ | =ПОИСКПОЗ(E2;A2:A10;0) |
Находит позицию точного совпадения E2 в диапазоне. |
| Очистка текста | СЖПРОБЕЛЫ | =СЖПРОБЕЛЫ(A2) |
Удаляет лишние обычные пробелы в тексте. |
| Сборка текста | СЦЕПИТЬ | =СЦЕПИТЬ(A2;" ";B2) |
Объединяет фрагменты текста; в новых версиях может уступать более современным средствам склейки. |
| Форматирование | ТЕКСТ | =ТЕКСТ(A2;"гггг-мм-дд") |
Преобразует число или дату в текст по указанному формату. |
| Рабочие даты | РАБДЕНЬ | =РАБДЕНЬ(A2;10) |
Сдвигает дату на заданное число рабочих дней; праздники можно передать отдельным диапазоном. |
| Разница дат | РАЗНДАТ | =РАЗНДАТ(A2;B2;"d") |
Возвращает интервал между датами; функция может отсутствовать в списке подсказок. |
| Логика | ЕСЛИ | =ЕСЛИ(B2>=60;"зачёт";"пересдать") |
Возвращает один из результатов в зависимости от условия. |
| Обработка ошибок | ЕСЛИОШИБКА | =ЕСЛИОШИБКА(A2/B2;"проверьте данные") |
Подставляет заданный результат, если вычисление завершается ошибкой. |
- Выберите способ поиска. Используйте ВПР, если ключ находится в первом столбце диапазона. Если положение столбцов ограничивает поиск, сочетайте ИНДЕКС и ПОИСКПОЗ.
- Проверьте диапазоны. У СЧЁТЕСЛИМН и СУММЕСЛИМН диапазоны условий должны соответствовать диапазону подсчёта или суммирования по размеру.
- Сверьте результат независимо. На небольшой выборке вручную посчитайте несколько строк или сопоставьте результат с фильтром таблицы.
- Учитывайте версию Excel. В новых версиях может быть доступна замена СЦЕПИТЬ; названия функций и разделители могут отличаться в зависимости от языка приложения. РАЗНДАТ может вычисляться, даже если не предлагается в подсказках.
Для формулы Excel для работы выбирайте функцию по структуре таблицы и проверяйте результат на примерах. Такие полезные формулы Excel удобно осваивать на копии реальных данных: это практичный способ начать обучение Excel формулы и закрепить базовые навыки. Если нужны формулы Excel для начинающих, начните с СЧЁТ, СЧЁТЕСЛИ, СУММЕСЛИ и ЕСЛИ, затем переходите к поиску и обработке дат.
Разбор частых затруднений при работе с формулами Excel
Почему Excel показывает ошибку в формуле?
Проверьте разделители аргументов, скобки, кавычки и названия функций для языка вашей версии Excel. Также убедитесь, что данные имеют подходящий формат.
Почему ВПР не находит значение?
При точном поиске используйте последний аргумент ЛОЖЬ и проверьте, что искомое значение находится в первом столбце диапазона. Сверьте пробелы и тип данных ключей: текстовый код и число могут не совпасть.
Когда выбирать СЧЁТЕСЛИМН вместо СЧЁТЕСЛИ?
СЧЁТЕСЛИ подходит для одного условия, а СЧЁТЕСЛИМН - для нескольких одновременно. Например, можно учитывать и статус, и порог балла.
Почему РАЗНДАТ не отображается в подсказках Excel?
Функция может не появляться в списке автозаполнения, но при этом поддерживаться приложением. Введите её вручную и проверьте результат на датах, порядок которых известен.
Можно ли заменить исходные данные результатами СЖПРОБЕЛЫ?
Сначала проверьте очищенный столбец и сохраните исходные значения. Если результат верен, скопируйте его и вставьте как значения, чтобы заменить формулы.
Стоит ли всегда оборачивать формулы в ЕСЛИОШИБКА?
Нет: такая обёртка может скрыть причину неисправности. Сначала выясните, почему возникла ошибка, и только затем задавайте понятный пользователю запасной результат.
Почему формула ТЕКСТ мешает дальнейшим вычислениям?
ТЕКСТ возвращает текстовое представление значения, а не исходное число или дату. Для вычислений используйте исходную ячейку, а ТЕКСТ - только когда требуется вывод в заданном формате.