Освойте ключевые формулы Excel для быстрых расчетов
Для выполнения большинства расчетов в Excel достаточно знать 10–12 базовых функций. Они позволяют суммировать данные, находить средние значения, проверять условия и искать информацию в таблицах. Основные инструменты: СУММ (сложение), СРЗНАЧ (среднее), ЕСЛИ (логика) и ВПР (поиск). Ниже приведены синтаксис и готовые примеры для копирования, адаптированные под русскую версию программы.
В русской версии Excel аргументы функций разделяются точкой с запятой (;), а не запятой. Убедитесь, что используете правильный разделитель при вводе формул.
Арифметические операции: суммы и итоги
Функция СУММ — фундамент любых вычислений. Она складывает числа в указанном диапазоне, игнорируя текст и пустые ячейки.
Пример: Подсчет общей выручки за неделю (диапазон A1:A7).
=СУММ(A1:A7)
Если нужно сложить значения только по определенному критерию, используйте СУММЕСЛИ. Она суммирует ячейки, соответствующие заданному условию.
Пример: Сумма продаж только для товара «Ноутбук» (товары в B1:B10, суммы в A1:A10).
=СУММЕСЛИ(B1:B10; "Ноутбук"; A1:A10)
Для сложных условий (например, продажи «Ноутбуков» менеджером «Иван») применяется СУММЕСЛИМН:
=СУММЕСЛИМН(A1:A10; B1:B10; "Ноутбук"; C1:C10; "Иван")
Статистический анализ: среднее, минимум и максимум
Чтобы понять общую картину данных, недостаточно одной суммы. Используйте функции для поиска центральных тенденций и экстремумов.
- СРЗНАЧ — вычисляет среднее арифметическое. Полезно для расчета средней зарплаты или температуры.
=СРЗНАЧ(A1:A10) - МЕДИАНА — находит значение посередине отсортированного ряда. В отличие от среднего, медиана не искажается резкими выбросами (например, одной очень большой премией).
=МЕДИАНА(A1:A10) - МИН и МАКС — находят наименьшее и наибольшее значение в списке.
=МИН(A1:A10)/=МАКС(A1:A10)
Разница между СЧЁТ и СЧЁТЗ: функция СЧЁТ учитывает только ячейки с числами, а СЧЁТЗ подсчитывает все заполненные ячейки (включая текст). Используйте СЧЁТЗ для подсчета количества сотрудников или наименований товаров.
Логические функции: проверка условий
Функция ЕСЛИ позволяет автоматизировать принятие решений в таблице. Она проверяет условие и возвращает одно значение, если оно истинно, и другое — если ложно.
Синтаксис: =ЕСЛИ(условие; значение_если_истина; значение_если_ложь)
Пример: Проверка плана продаж. Если продажа (A2) больше 10000, пишем «План выполнен», иначе «Недовыполнение».
=ЕСЛИ(A2>10000; "План выполнен"; "Недовыполнение")
Для многоступенчатой проверки условия вкладываются друг в друга (вложенные ЕСЛИ):
=ЕСЛИ(A2>20000; "Отлично"; ЕСЛИ(A2>10000; "Норма"; "Плохо"))
Поиск данных: функция ВПР
ВПР (Вертикальный Просмотр) ищет значение в первом столбце таблицы и возвращает данные из той же строки другого столбца. Это незаменимый инструмент для подтягивания цен, имен или статусов по коду.
Синтаксис: =ВПР(искомое_значение; таблица; номер_столбца; [интервальный_просмотр])
Пример: Есть таблица товаров (код в столбце A, цена в столбце B, диапазон A2:B100). Нужно найти цену для кода 555.
=ВПР(555; A2:B100; 2; 0)
Где 2 — номер столбца с ценой, а 0 (или ЛОЖЬ) означает точное совпадение.
Функция ВПР ищет значение только в первом столбце выбранного диапазона. Если ваш ключ (код товара) находится во втором столбце, ВПР не сработает. В таких случаях меняйте порядок столбцов или используйте связку ИНДЕКС/ПОИСКПОЗ.
Работа с датами и текстом
Автоматизация отчетов часто требует работы с текущей датой и форматирования текста.
- СЕГОДНЯ() — возвращает текущую дату. Обновляется при каждом открытии файла.
=СЕГОДНЯ() - ДАТАРАЗН — вычисляет разницу между двумя датами.
=ДАТАРАЗН(A1; СЕГОДНЯ(); "D")— количество дней между датой в A1 и сегодня. - СЦЕПИТЬ (или символ
&) — объединяет текст из разных ячеек.=A1 & " " & B1— объединит Имя и Фамилию через пробел. - ТЕКСТ — форматирует число как текст с заданным видом.
=ТЕКСТ(A1; "0,00 руб.")— превратит число 100 в строку «100,00 руб.»
Округление чисел
При финансовых расчетах важно контролировать количество знаков после запятой. Функция ОКРУГЛ делает это стандартным математическим способом.
Пример: Округлить результат деления до двух знаков.
=ОКРУГЛ(A1/B1; 2)
Если нужно всегда округлять вверх (для запаса материалов) используйте ОКРУГЛВВЕРХ, а вниз — ОКРУГЛВНИЗ.
Частые ошибки при работе с формулами
| Ошибка | Причина | Как исправить |
|---|---|---|
| #ЗНАЧ! | В формуле участвует текст там, где должно быть число | Проверьте диапазоны на наличие букв или лишних пробелов |
| #Н/Д | Функция ВПР не нашла искомое значение | Убедитесь, что ищете точное совпадение (последний аргумент 0) |
| #ССЫЛКА! | Удалена ячейка, на которую ссылалась формула | Восстановите ячейку или исправьте диапазон вручную |
| #ИМЯ? | Опечатка в названии функции | Проверьте написание (например, СУММ вместо SUM в русской версии) |
FAQ
Как скопировать формулу на весь столбец? Введите формулу в первую ячейку, наведите курсор на правый нижний угол ячейки (появится черный крестик) и дважды кликните левой кнопкой мыши. Формула протянется до конца заполненных данных слева.
В чем разница между абсолютной и относительной ссылкой?
Относительная ссылка (A1) меняется при копировании формулы. Абсолютная ($A$1) фиксирует ячейку. Чтобы зафиксировать ссылку, выделите её в формуле и нажмите клавишу F4.
Почему формула не пересчитывается автоматически? Проверьте режим вычислений: вкладка «Формулы» → «Параметры вычисления» → выберите «Автоматически». Возможно, включен ручной режим.