Шпаргалка по функциям для Excel и Google Таблиц

Функции в электронных таблицах позволяют автоматизировать рутинные вычисления, анализировать большие массивы данных и избегать ошибок ручного ввода. Чтобы начать эффективно работать с таблицами, достаточно освоить базовый набор: арифметические операторы (СУММ, СРЗНАЧ), логические условия (ЕСЛИ) и функции поиска (ВПР или ПРОСМОТРX). Ниже приведены подробные инструкции и примеры их применения.

Агрегация и статистика: быстрый подсчет данных

Эти функции используются чаще всего для подведения итогов. Они игнорируют пустые ячейки и текстовые значения (если не указано иное).

Базовые математические операции

Условная агрегация

Если нужно посчитать сумму или количество только при выполнении определенного условия, используйте функции с суффиксом «ЕСЛИ»:

• СУММЕСЛИ (=СУММЕСЛИ(диапазон_условия; условие; диапазон_суммы)) — суммирует значения, соответствующие одному критерию.
  • Пример: =СУММЕСЛИ(A2:A10; "Яблоки"; B2:B10) — сумма продаж только яблок.
• СЧЁТЕСЛИ (=СЧЁТЕСЛИ(диапазон; условие)) — считает количество ячеек, отвечающих условию.
  • Пример: =СЧЁТЕСЛИ(C2:C100; ">1000") — сколько сделок превысили 1000 рублей.

Для нескольких условий существуют аналоги СУММЕСЛИМН и СЧЁТЕСЛИМН, где сначала указывается диапазон для суммирования/подсчета, а затем пары «диапазон условия – само условие».

Логические функции: ветвление сценариев

Функция ЕСЛИ (IF) позволяет таблице принимать решения. Она проверяет условие и возвращает одно значение, если оно истинно, и другое, если ложно.

Синтаксис: =ЕСЛИ(логическое_выражение; значение_если_истина; значение_если_ложь)

Практический пример

Необходимо проставить статус «Бонус» сотрудникам, чьи продажи выше 50 000, и «Стандарт» остальным.

=ЕСЛИ(B2>50000; "Бонус"; "Стандарт")

Вложенные условия

Если вариантов больше двух, можно вкладывать одни ЕСЛИ в другие (до 64 уровней вложений), но это усложняет чтение формулы.

=ЕСЛИ(B2>100000; "Золото"; ЕСЛИ(B2>50000; "Серебро"; "Бронза"))

Поиск и сопоставление данных

Самая сложная часть для новичков — объединение данных из разных таблиц.

ВПР (VLOOKUP): классический поиск

Функция ищет значение в первом столбце указанного диапазона и возвращает значение из другого столбца той же строки.

Синтаксис: =ВПР(что_ищем; где_ищем; номер_столбца_результата; тип_совпадения)

• что_ищем: значение или ссылка на ячейку.
• где_ищем: таблица, причем искомое значение должно быть в первом столбце этого диапазона.
• номер_столбца: порядковый номер столбца в диапазоне, из которого нужно вернуть данные.
• тип_совпадения: 0 (или ЛОЖЬ) для точного совпадения, 1 (или ИСТИНА) для приблизительного.

Пример: Найти цену товара по его артикулу. =ВПР(A2; D2:F100; 3; 0)

ПРОСМОТРX (XLOOKUP): современная замена

В Excel 365 и новых обновлениях Google Таблиц доступна функция ПРОСМОТРX. Она проще, надежнее и не требует, чтобы поисковый столбец был первым.

=ПРОСМОТРX(искомое; диапазон_поиска; диапазон_возврата; "не найдено")

Работа с ошибками и текстом

Часто формулы ломаются из-за деления на ноль или отсутствия данных. Чтобы таблица выглядела аккуратно, ошибки нужно маскировать.

Обработка ошибок

Функция ЕСЛИОШИБКА (IFERROR) перехватывает любую ошибку (#Н/Д, #ДЕЛ/0!, #ЗНАЧ!) и заменяет её на заданное вами значение.

• Плохо: =A2/B2 (вернет #ДЕЛ/0!, если B2 пусто).
• Хорошо: =ЕСЛИОШИБКА(A2/B2; 0) (вернет 0 при ошибке).

Текстовые манипуляции

• СЦЕПИТЬ или оператор &: объединяет текст.
  • =A2 & " " & B2 (Объединит имя и фамилию через пробел).
• ЛЕВСИМВ / ПРАВСИМВ: извлекают указанное количество символов слева или справа.
  • =ЛЕВСИМВ(A2; 3) — первые три буквы.
• ДЛСТР: считает количество символов в ячейке.

Даты и время

Работа с датами критична для отчетности. Важно помнить, что внутри таблицы дата — это число (количество дней от 1 января 1900 года).

• СЕГОДНЯ(): возвращает текущую дату (обновляется при каждом пересчете листа).
• ТДАТА(): возвращает текущие дату и время.
• РАЗНДАТ: скрытая, но полезная функция для расчета полного количества лет, месяцев или дней между двумя датами.
  • =РАЗНДАТ(A1; B1; "Y") — полных лет между датами.

Частые ошибки при работе с формулами

1. Относительные и абсолютные ссылки. При копировании формулы вниз ссылки вида A1 меняются на A2, A3. Если нужно зафиксировать ячейку (например, курс валют в одной ячейке), используйте знак доллара: $A$1.

   • Совет: Нажимайте F4 (Windows) или Cmd+T (Mac) при редактировании формулы, чтобы переключать типы ссылок.

2. Ошибка #Н/Д в ВПР. Чаще всего возникает из-за лишних пробелов в данных («Товар » не равно «Товар») или разного формата данных (число сохранено как текст). Используйте функцию СЖПРОБЕЛЫ для очистки данных или проверьте формат ячеек.

3. Деление на ноль. Всегда оборачивайте операции деления в ЕСЛИОШИБКА или предварительно проверяйте знаменатель через ЕСЛИ.

4. Слишком длинные формулы. Если формула занимает более 2-3 строк, разбейте её на части. Вынесите промежуточные вычисления в отдельные скрытые столбцы. Это упростит отладку.

FAQ

В чем разница между СЧЁТ и СЧЁТЗ? СЧЁТ учитывает только числа и даты. СЧЁТЗ считает любые непустые ячейки, включая текст, логические значения и ошибки.

Почему ВПР не находит данные, которые явно есть в таблице? Проверьте форматы. Часто в одном столбце числа хранятся как числа, а в другом — как текст (обычно помечаются зеленым треугольником в углу ячейки). Приведите их к одному формату через «Текст по столбцам» или функцию ЗНАЧЕН.

Как посчитать сумму по нескольким разным условиям? Используйте СУММЕСЛИМН. Например, =СУММЕСЛИМН(C2:C100; A2:A100; "Москва"; B2:B100; "Январь") просуммирует продажи в Москве за январь.

Работают ли эти формулы в Google Таблицах? Да, большинство функций (СУММ, ВПР, ЕСЛИ) имеют идентичный синтаксис в Excel и Google Sheets. Однако функции вроде ПРОСМОТРX или динамические массивы могут отличаться доступностью в зависимости от версии ПО.