Полезные формулы Excel помогают быстро складывать данные, считать записи, проверять условия, находить значения и работать с датами и текстом. Начните с проверки таблицы, затем выберите формулу под задачу и проверьте результат на нескольких строках. В примерах ниже используются русские названия функций и точка с запятой между аргументами.
Формулы, которые ускорят повседневную работу в Excel
- СУММ складывает числа в указанном диапазоне, а СЧЁТ подсчитывает числовые ячейки.
- СЧЁТЗ помогает посчитать непустые ячейки, включая текстовые записи.
- ЕСЛИ проверяет одно условие; ЕСЛИМН последовательно проверяет несколько условий.
- СЧЁТЕСЛИ и СУММЕСЛИ считают записи и суммы по критерию.
- ПРОСМОТРX ищет совпадение в таблице; если функция недоступна, используйте подходящую альтернативу, например ВПР.
Подготовьте данные: проверьте диапазоны, заголовки и пустые ячейки
Эти приёмы подходят для домашних бюджетов, списков покупок, учёта заказов и рабочих отчётов. Не добавляйте формулы в исходные данные, если они должны оставаться неизменными: создайте отдельный столбец или копию листа.
- Сделайте заголовки короткими и понятными: например, "Дата", "Категория", "Сумма".
- Проверьте, что значения одного типа находятся в одном столбце: даты - с датами, суммы - с числами.
- Убедитесь, что в диапазон не попали заголовки и посторонние строки.
- Проверьте пустые ячейки, лишние пробелы и числа, сохранённые как текст.
При работе с важной таблицей сначала сохраните копию. Так можно безопасно проверить формулы и сравнить результат с исходными данными.
Суммы и подсчёты: применяйте СУММ, СЧЁТ и СЧЁТЗ
Для примеров предположим, что суммы покупок находятся в ячейках B2:B20, а список записей - в A2:A20. Достаточно обычной версии Excel, доступа к книге и корректного диапазона; подключение к интернету для этих функций не нужно.
| Задача | Формула | Что проверять |
|---|---|---|
| Сложить покупки | =СУММ(B2:B20) |
В диапазоне должны быть числовые значения. |
| Посчитать ячейки с числами | =СЧЁТ(B2:B20) |
Текстовые значения не считаются числами. |
| Посчитать заполненные записи | =СЧЁТЗ(A2:A20) |
Учитываются непустые ячейки, в том числе с текстом. |
Если итоговая сумма неожиданно мала, проверьте, не сохранена ли часть чисел как текст. Если СЧЁТ показывает меньше записей, чем ожидалось, убедитесь, что в диапазоне действительно числа, а не текстовые обозначения.
Условия в ячейках: используйте ЕСЛИ и ЕСЛИМН
Перед пошаговой инструкцией подготовьте небольшой тестовый участок таблицы:
- Скопируйте несколько строк или создайте копию листа.
- Определите, какие значения должны соответствовать каждому результату.
- Проверьте, что сравниваемые значения имеют одинаковый формат.
- Уточните, какую надпись или число нужно вывести, если условие не выполнено.
-
Выберите проверяемую ячейку.
Например, если сумма заказа записана в C2, формулу можно разместить в соседнем столбце, не меняя исходное значение. -
Задайте одно условие через ЕСЛИ.
Формула=ЕСЛИ(C2>=1000;"Крупный";"Обычный")выводит "Крупный", если сумма не меньше 1000, и "Обычный" в остальных случаях. -
Добавьте несколько вариантов через ЕСЛИМН.
Например,=ЕСЛИМН(C2>=5000;"Высокий";C2>=1000;"Средний";C2<1000;"Низкий")проверяет условия по порядку и возвращает результат для первого истинного условия. -
Проверьте граничные значения.
Подставьте значения ниже порога, равные порогу и выше него. Это помогает заметить неверный знак сравнения или пропущенный вариант. -
Скопируйте формулу на остальные строки.
Перетащите маркер автозаполнения или скопируйте ячейку, затем проверьте несколько результатов вручную.
Подсчёт и суммирование по критериям: СЧЁТЕСЛИ и СУММЕСЛИ
Допустим, в A2:A20 указаны категории расходов, а в B2:B20 - суммы. Формула =СЧЁТЕСЛИ(A2:A20;"Еда") посчитает записи с категорией "Еда", а =СУММЕСЛИ(A2:A20;"Еда";B2:B20) сложит соответствующие суммы.
Проверьте результат по списку:
- Диапазон критерия и диапазон сумм охватывают соответствующие строки.
- Название критерия написано так же, как в исходной таблице.
- В критерии не затесались лишние пробелы.
- Для текстового критерия использованы кавычки.
- СУММЕСЛИ ссылается на столбец, который нужно сложить.
- Итог не включает строки за пределами выбранного диапазона.
Поиск значений в таблицах: сравнение ВПР и ПРОСМОТРX
Обе функции помогают найти связанное значение, например цену товара по его коду. ВПР подходит для таблиц, где столбец поиска расположен слева от столбца результата; ПРОСМОТРX позволяет задавать диапазоны поиска и результата отдельно.
| Функция | Пример | Типичная ошибка |
|---|---|---|
| ВПР | =ВПР(E2;A2:C20;3;ЛОЖЬ) |
Номер столбца результата указан неверно или пропущен точный поиск ЛОЖЬ. |
| ПРОСМОТРX | =ПРОСМОТРX(E2;A2:A20;C2:C20;"Не найдено") |
Функция может отсутствовать в старой версии Excel; диапазоны поиска и результата должны соответствовать друг другу. |
Перед использованием формулы проверьте распространённые ошибки:
- Искомый код отсутствует в таблице или содержит лишний пробел.
- Код в одной ячейке хранится как число, а в другой - как текст.
- В ВПР выбран не тот столбец результата.
- Для ВПР не задан точный поиск, хотя требуется точное совпадение.
- Диапазон ВПР изменился после копирования формулы вниз.
- В ПРОСМОТРX указаны несоразмерные диапазоны поиска и результата.
- Функция ПРОСМОТРX недоступна в установленной версии Excel.
Даты и текст: извлекайте части строки и рассчитывайте сроки
Для разных форматов данных подходят разные способы:
- ДЕНЬ, МЕСЯЦ и ГОД - когда дата хранится в Excel как дата и нужно получить её часть. Например,
=МЕСЯЦ(A2)возвращает номер месяца. - Разность дат - если нужно узнать число дней между датой начала и сроком:
=B2-A2. Проверьте, что обе ячейки распознаны как даты. - ЛЕВСИМВ, ПСТР и ПРАВСИМВ - когда нужно извлечь часть текста по её позиции, например первые символы кода:
=ЛЕВСИМВ(A2;3). - Текст по столбцам - когда данные разделены одним и тем же символом и удобнее разнести их по отдельным столбцам через инструменты Excel.
Если дата отображается как число, проверьте формат ячейки. Если расчёт срока даёт ошибку, убедитесь, что в исходных ячейках находятся даты, а не текстовые строки.
Нюансы формул Excel: ошибки, разделители и ссылки
Почему Excel не принимает формулу?
Проверьте название функции, скобки и разделители аргументов. В русской локали часто используется точка с запятой, но настройки конкретной системы могут отличаться.
Чем отличаются СЧЁТ и СЧЁТЗ?
СЧЁТ подсчитывает ячейки с числами. СЧЁТЗ считает непустые ячейки, в том числе содержащие текст.
Как закрепить диапазон при копировании формулы?
Используйте абсолютную ссылку со знаками доллара, например $A$2:$A$20. Такая ссылка не меняется при копировании формулы в другие ячейки.
Почему СУММЕСЛИ не учитывает нужные строки?
Сверьте текст критерия с данными, проверьте пробелы и убедитесь, что диапазоны соответствуют друг другу. Для суммирования укажите именно столбец с числами.
Что делать, если ПРОСМОТРX не распознаётся?
Проверьте, поддерживает ли установленная версия Excel эту функцию. Если нет, используйте ВПР, если структура таблицы позволяет искать слева направо.
Можно ли использовать эти формулы при обучении Excel для начинающих?
Да. Начните с СУММ и ЕСЛИ на копии таблицы, а затем добавьте критерии и поиск. Такой порядок полезен и для самостоятельной практики, и при выборе курсов Excel онлайн.
Где искать функции Excel с примерами для рабочих задач?
Подбирайте формулу по типу задачи и проверяйте её на небольшом фрагменте своих данных. Так проще выбрать полезные формулы Excel и понять, какие формулы Excel для работы подходят именно для вашей таблицы.
Автор: Сергей Лебедев