Excel и Google Sheets — формулы

НЕ ОБЯЗАТЕЛЬНО ДЛЯ НОВИЧКОВ

Приветствую! Здесь вы наверняка найдете, что ищете. Примеры в лаборатории рассчитаны на то, что мы разбираем что-то конкретное.

Текущая статья посвящена готовым формулы Excel и Google Таблиц с разбором по частям.

Поэтому за теорией по текущей теме вам — в энциклопедию. Если ещё не погружались, то маршрут прост:

  1. Основы
  2. Система и сеть
  3. Данные и разметка
  4. Код и разработка
  5. Языки
  6. Искусственный интеллект
  7. Проект
  8. Инфраструктура и безопасность
  9. Спин-офф

Обязательно пройдитесь.

А теперь приступим к нашему предмету.

Теория и соседние материалы

Ячейки, форматы, фильтр — Работа с Microsoft Excel.

Полный список функций — Справочник по Excel.

Анализ данных — EDA в Excel.

Тот же смысл в коде — Pandas.

Формулы для PDF-отчёта — LaTeX.

Подборка готовых формул для Excel и Google Таблиц с разбором каждой части — «что написано» и «зачем так».

Навигация по примерам

Раздел Тема
Как устроена формула excel формула с чего начинается, что такое диапазон a1 a10
СУММ excel сумма столбца, формула сумма ячеек
СРЗНАЧ — средний балл средний балл excel, average формула
Процент и скидка процент скидки excel, как посчитать процент от числа
ЕСЛИ excel если то, if больше меньше, оценка по баллам
СУММЕСЛИ сумма если условие, sumif пример
СУММЕСЛИМН сумма по двум условиям excel
ВПР vlookup excel, впр как пользоваться, подставить цену по коду
ИНДЕКС + ПОИСКПОЗ index match вместо vlookup
Текст из email вырезать текст до собаки excel
Даты сколько дней между датами excel, сегодня формула
Журнал оценок таблица оценок средний балл, автоматическая оценка
Частые ошибки #доступ, #н/д, #дел/0

Разбор формулы по частям

Любая формула читается слева направо, как короткое математическое предложение.

Пример: =СУММЕСЛИ(A2:A10; "Яблоки"; B2:B10)

Часть Название Смысл
= начало Без него Excel воспримет текст как подпись, а не расчёт
СУММЕСЛИ функция Встроенная команда «сложи, но только где…»
(..) аргументы Всё внутри скобок — входные данные для функции
A2:A10 диапазон Столбец A со 2-й по 10-ю строку (двоеточие = «от … до …»)
; разделитель В русском Excel аргументы отделяют точкой с запятой
"Яблоки" текст-критерий Точное совпадение; кавычки обязательны
B2:B10 диапазон сумм Какие числа складывать, когда в A нашлось «Яблоки»

Google Sheets: та же логика, но SUMIF и запятые вместо ;: =SUMIF(A2:A10, "Яблоки", B2:B10).

Excel (RU) и Google Sheets

Элемент Excel (RU) Google Sheets
Начало = =
Ячейка / диапазон A1, $B$2, A1:A10 то же
Разделитель аргументов ; ,
Сумма СУММ SUM
Условие ЕСЛИ IF
Поиск ВПР VLOOKUP

Обязательные правила

Правило Пример Зачем
Формула с = =5*2 → 10 иначе в ячейке останется текст «=5*2»
Ссылка на ячейку =A1*C1 при копировании адрес сдвигается вместе с формулой
Абсолютная $ =$B$1*A2 ставка НДС в B1 не съедет, когда тянете формулу вниз
Текст в ".." "Сдал" без кавычек таблица ищет имя «Сдал» на листе
Ошибки #ДЕЛ/0!, #Н/Д, #ЗНАЧ! деление на 0, ключ не найден, текст вместо числа

F4 в Excel — стоя курсором на A1 внутри формулы, нажимайте F4: A1 → $A$1 → A$1 → $A1 → снова A1. Так фиксируют столбец или строку при автозаполнении.

Пример абсолютной ссылки. Таблица умножения: в A1 число 7 (множитель), в B1:K1 заголовки 1…10, в A2:A11 числа 1…10. В B2:

= $A$1 * B$1
Часть При копировании вправо При копировании вниз
$A$1 остаётся 7 остаётся 7
B$1 станет C$1, D$1… (столбец заголовка) остаётся B$1
$A2 (если множитель в столбце A) остаётся строка 2 станет A3, A4…

Стартовые формулы

Пять шаблонов, с которых начинают почти любую учебную таблицу.

1. Сложение и СУММ

Формулировка из задания: «В столбце C выведите сумму чисел из столбцов A и B. Внизу — итог по столбцу C».

A B C
1 10 3
2 5 8
3
4 Итого

C1 — сумма первой строки:

= A1 + B1

Получится: 13

Разбор:

Часть Смысл
A1 взять значение из ячейки пересечения столбца A и строки 1
+ сложить
B1 второе слагаемое

C2: скопируйте формулу из C1 — Excel сам заменит на =A2+B2, получится 13.

C4 — итог по столбцу:

= СУММ(C1:C2)

Google Sheets: =SUM(C1:C2)

Разбор СУММ(C1:C2):

Часть Смысл
СУММ функция «сложи все числа в…»
C1:C2 диапазон: от C1 до C2 включительно
Результат 13 + 13 = 26

Смысл шаблона: A1+B1 — сумма одной строки; СУММ(C1:C2) — итог по столбцу. В отчётах почти всегда нужны оба приёма.

2. Среднее и количество

Формулировка: «По оценкам в B2:B5 найдите средний балл, максимум и сколько оценок».

B
2 4
3 5
4 5
5 9
Задача Формула Результат
Средний =СРЗНАЧ(B2:B5) 5,75
Максимум =МАКС(B2:B5) 9
Сколько чисел =СЧЁТ(B2:B5) 4
Непустых ячеек =СЧЁТЗ(B2:B5) 4

Google Sheets: AVERAGE, MAX, COUNT, COUNTA.

Разбор =СРЗНАЧ(B2:B5):

Шаг Что происходит
1 Excel берёт числа 4, 5, 5, 9
2 Складывает: 23
3 Делит на количество: 23 ÷ 4 = 5,75

СЧЁТ считает только числа; пустая ячейка или текст «нет» не войдут. СЧЁТЗ считает всё непустое, включая текст.

3. Процент и скидка

Формулировка: «Цена 2000 руб., скидка 15 %. Найдите цену со скидкой».

A B
1 2000 15

Вариант A — в B1 записан процент целым числом (15, не 0,15):

= A1 * (1 - B1/100)

Получится: 1700

Разбор по шагам:

Шаг Выражение Значение
1 B1/100 15 ÷ 100 = 0,15 (доля скидки)
2 1 - 0,15 0,85 (платите 85 % цены)
3 A1 * 0,85 2000 × 0,85 = 1700

Вариант B — в B1 уже доля 0,15:

= A1 * (1 - B1)

Смысл: (1 - доля) — «сколько остаётся заплатить». Умножение на цену даёт итог со скидкой.

Только размер скидки в рублях: =A1*B1/100 или =A1*B1 если B1 — доля.

4. ЕСЛИ — «если … то … иначе …»

Формулировка: «Если балл ≥ 50 — "Сдал", иначе — "Не сдал"».

A B
1 62
2 41

B1:

= ЕСЛИ(A1>=50; "Сдал"; "Не сдал")

Получится: Сдал (для A1=62), в B2 — Не сдал (для A2=41).

Разбор ЕСЛИ(A1>=50; "Сдал"; "Не сдал"):

Аргумент Позиция Смысл
A1>=50 1 — условие Истина, если балл не меньше 50
"Сдал" 2 — если истина Текст результата
"Не сдал" 3 — если ложь Альтернативный текст

Google Sheets: =IF(A1>=50, "Сдал", "Не сдал")

Вложенные ЕСЛИ — буквенная оценка (частый запрос «5 если больше 90 excel»):

= ЕСЛИ(A1>=90; "5";
    ЕСЛИ(A1>=75; "4";
      ЕСЛИ(A1>=60; "3"; "2")))

Пример: A1 = 82 → проверка 90 (нет) → 75 (да) → "4".

Excel читает изнутри наружу: сначала внутреннее условие, потом следующее. Не более 64 уровней вложенности — на практике хватает 3–4.

5. СУММЕСЛИ — сумма по одному условию

Формулировка: «В столбце A — товар, в B — выручка. Посчитайте выручку только по "Яблоки"».

A B
1 Яблоки 100
2 Груши 80
3 Яблоки 120 
= СУММЕСЛИ(A:A; "Яблоки"; B:B)

Получится: 220 (100 + 120)

Разбор:

Аргумент Смысл
A:A Где смотреть условие (весь столбец A)
"Яблоки" Что искать — точное совпадение текста
B:B Что складывать — числа из B в тех же строках

Если третий аргумент опустить, суммируется сам первый диапазон: =СУММЕСЛИ(A:A; "Яблоки") сложит только ячейки A с «Яблоки» (обычно там текст, не числа — для денег нужен третий аргумент).

Google Sheets: =SUMIF(A:A, "Яблоки", B:B)

Критерий «больше 10»: =СУММЕСЛИ(B:B; ">10"; C:C) — критерий с оператором в кавычках.

Примеры формул

1. Арифметика и ссылки

1.1. Четыре действия

A B C (сложение) D (разность) E (произведение) F (частное)
100 4 104 96 400 25
Ячейка Формула
C1 =A1+B1
D1 =A1-B1
E1 =A1*B1
F1 =A1/B1

Разбор =A1/B1: если B1 = 0, Excel покажет #ДЕЛ/0!. Оборачивают в =ЕСЛИОШИБКА(A1/B1; "—").

1.2. Ссылка на другой лист

Задача: на листе Итог в A1 — сумма продаж с листа Продажи.

= СУММ(Продажи!B2:B100)
Часть Смысл
Продажи! имя листа + восклицательный знак
B2:B100 диапазон на том листе

Лист с пробелом: 'Март 2026'!B2:B100. Google Sheets — тот же синтаксис.

1.3. Процент изменения

Формулировка: «Было 100, стало 125. На сколько процентов выросло?»

A (было) B (стало) C (прирост)
100 125

C1:

= (B1 - A1) / A1

Получится: 0,25 → формат Процентный → отображение 25 %

Шаг Смысл
B1 - A1 абсолютный прирост (+25)
/ A1 делим на исходное значение (относительное изменение)
Формат ячейки умножает на 100 для подписи «%»

2. Агрегаты и статистика

2.1. СУММ, СРЗНАЧ, МЕДИАНА

Медиана — «серединное» значение; устойчива к выбросам (одна оценка «2» при остальных «5»).

= МЕДИАНА(B2:B5)

Для 4, 5, 5, 9 медиана = 5 (среднее между двумя центральными при чётном количестве).

2.2. СУММЕСЛИМН — два условия сразу

Формулировка: «Сумма продаж отдела "Продажи" за месяц "Янв"».

A (отдел) B (месяц) C (сумма)
Продажи Янв 1000
Продажи Фев 1200
Склад Янв 400 
= СУММЕСЛИМН(C:C; A:A; "Продажи"; B:B; "Янв")

Получится: 1000

Разбор (пары «диапазон + условие»):

Пара Смысл
C:C что суммировать (столбец сумм)
A:A + "Продажи" 1-е условие
B:B + "Янв" 2-е условие

Все диапазоны условий должны быть одинаковой длины. Google Sheets: =SUMIFS(C:C, A:A, "Продажи", B:B, "Янв").

2.3. СУММПРОИЗВ — взвешенное среднее

Формулировка: «Оценки 5, 4, 3 с весами 0,5; 0,3; 0,2. Найдите итоговый балл».

B (оценка) C (вес)
5 0,5
4 0,3
3 0,2 
= СУММПРОИЗВ(B2:B4; C2:C4)

Получится: 5×0,5 + 4×0,3 + 3×0,2 = 4,3

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

2.4. СЧЁТЕСЛИ и СЧЁТЕСЛИМН

= СЧЁТЕСЛИ(A2:A50; "Сдан")
= СЧЁТЕСЛИМН(A:A; "Продажи"; C:C; ">500")

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

3. Условная логика

3.1. И и ИЛИ внутри ЕСЛИ

Задача: зачёт только при балле ≥ 60 и посещаемости ≥ 80 %.

A (балл) B (доля посещ.) C (итог)
70 0,85

C1:

= ЕСЛИ(И(A1>=60; B1>=0,8); "Зачёт"; "Незачёт")

Разбор И(A1>=60; B1>=0,8): оба условия должны быть истинны. ИЛИ — достаточно одного: =ЕСЛИ(ИЛИ(A1>10000; B1="VIP"); "Скидка"; "Нет").

3.2. ЕСЛИОШИБКА

= ЕСЛИОШИБКА(A1/B1; "—")

Если в A1/B1 любая ошибка (#ДЕЛ/0!, #ЗНАЧ!…), в ячейке будет "—" вместо красного #ДЕЛ/0!. Удобно в отчётах для преподавателя.

3.3. ПЕРЕКЛЮЧ / SWITCH

Замена длинной цепочки ЕСЛИ при сопоставлении кода и текста:

= ПЕРЕКЛЮЧ(A1; 1; "Январь"; 2; "Февраль"; 3; "Март"; "Другой")

Последний аргумент без пары — значение по умолчанию, если A1 не 1, 2 и не 3.

4. ВПР — подставить цену по коду

**** «Есть код товара — нужно подтянуть цену из прайса».

Лист «Справочник»:

A (код) B (название) C (цена)
101 Болты 50
102 Гайки 30

Лист «Заказ», ячейка A2 = 101, нужна цена в C2:

= ВПР(A2; Справочник!A:C; 3; ЛОЖЬ)

Получится: 50

Разбор по аргументам:

№ Аргумент Значение Смысл
1 A2 101 Что ищем — ключ
2 Справочник!A:C таблица Где ищем — A, B, C на листе Справочник
3 3 номер столбца Вернуть значение из 3-го столбца диапазона (C = цена)
4 ЛОЖЬ точный поиск 101 должно точно совпасть; ИСТИНА — «примерно» (для чисел редко)

Google Sheets: =VLOOKUP(A2, Справочник!A:C, 3, FALSE)

Как Excel ищет (пошагово):

  1. Берёт ключ из A2 → 101.
  2. Смотрит первый столбец диапазона A:C → столбец A.
  3. Идёт сверху вниз до первого точного совпадения.
  4. Смещается на 3 столбца вправо от начала диапазона → столбец C.
  5. Возвращает 50.
Почему #Н/Д
  • Кода нет в первом столбце справочника.
  • Лишний пробел: в A2 «101 », в справочнике «101» — используйте СЖПРОБЕЛЫ.
  • Число vs текст: «101» и 101 — разные ключи; приведите формат.
  • Номер столбца считают от левого края диапазона, а не от буквы C на листе.

Ограничение: ключ должен быть слева от ответа. Цена левее кода — ВПР не поможет → ИНДЕКС + ПОИСКПОЗ или ПРОСМОТРX.

4.2. ПРОСМОТРX / XLOOKUP

= ПРОСМОТРX(A2; Справочник!A:A; Справочник!C:C; "Не найдено")
Аргумент Смысл
A2 ключ
A:A столбец поиска
C:C столбец ответа (может быть левее A — в отличие от ВПР)
"Не найдено" текст, если ключ отсутствует (без #Н/Д)

4.3. ИНДЕКС + ПОИСКПОЗ

Формулировка: «Универсальный аналог ВПР — найти строку и взять ячейку из нужного столбца».

= ИНДЕКС(Справочник!C:C; ПОИСКПОЗ(A2; Справочник!A:A; 0))

Разбор:

Функция Роль
ПОИСКПОЗ(A2; A:A; 0) «На какой строке встретился ключ?» → например, 2
ИНДЕКС(C:C; 2) «Возьми значение из C2» → 50
0 в ПОИСКПОЗ точное совпадение (как ЛОЖЬ в ВПР)

Смысл связки: ПОИСКПОЗ находит номер строки, ИНДЕКС достаёт ячейку из любого столбца этой строки.

5. Текстовые функции

5.1. ФИО в одной ячейке

= A1&" "&B1&" "&C1

или =СЦЕП(A1; " "; B1; " "; C1). Google Sheets: =TEXTJOIN(" ", TRUE, A1:C1) — пробел между непустыми.

5.2. Логин из email

A1 = ivan@school.ru

= ЛЕВСИМВ(A1; ПОИСК("@"; A1)-1)
Шаг Результат
ПОИСК("@"; A1) позиция @ → 5
5 - 1 4 символа слева
ЛЕВСИМВ(A1; 4) ivan

Google Sheets: =LEFT(A1, FIND("@", A1)-1)

5.3. СЖПРОБЕЛЫ

= СЖПРОБЕЛЫ(A1)

Убирает пробелы по краям и двойные пробелы внутри — часто нужно перед ВПР, если данные копировали из Word.

6. Даты и время

6.1. Разница в днях

A (старт) B (финиш) C (дней)
01.03.2026 15.03.2026

C1: =B1-A1 → 14

Excel хранит дату как число (1 = 01.01.1900). Вычитание дат даёт целое число дней.

6.2. Дней до дедлайна

= B1 - СЕГОДНЯ()

СЕГОДНЯ() — только дата, без времени; обновляется при открытии файла. Отрицательное значение — просрочка.

6.3. Рабочие дни

= ЧИСТРАБДНИ(A1; B1)

Суббота и воскресенье не считаются. Google Sheets: NETWORKDAYS.

7. Финансы и проценты

7.1. НДС

Задача Формула (A — без НДС, B — ставка 0,2)
НДС в рублях =A1*B1
Сумма с НДС =A1*(1+B1)
Выделить НДС из «с НДС» =A1/(1+B1)*B1

Разбор «с НДС»: сумма A1 уже включает налог → делим на (1+ставка), получаем базу → умножаем на ставку = доля НДС.

7.2. Платёж по кредиту

= ПЛТ(B1/12; B2*12; -B3)
Аргумент Пример Смысл
B1/12 0,01 месячная ставка (годовая / 12)
B2*12 60 число месяцев
-B3 -500000 сумма кредита (минус — деньги «к вам» по соглашению Excel)

Google Sheets: =PMT(..)

8. Учебные сценарии — разбор «под ключ»

8.1. Журнал оценок

Формулировка: «Для каждого ученика — средний балл и автоматическая итоговая оценка (≥4,5 → 5, ≥3,5 → 4…).»

A B C D E (средний) F (итог)
Аня 5 4 5
Боря 3 3 4

E2:

= СРЗНАЧ(B2:D2)

Скопируйте E2 вниз → для Ани 4,67, для Бори 3,33.

F2 — итоговая оценка по среднему:

= ЕСЛИ(E2>=4,5; 5;
    ЕСЛИ(E2>=3,5; 4;
      ЕСЛИ(E2>=2,5; 3; 2)))

Разбор F2 для Ани (E2=4,67):

Проверка Результат
4,67 >= 4,5? да → 5

Для Бори (3,33): 4,5? нет → 3,5? нет → 2,5? да → 3.

8.2. Бюджет семьи

= СУММЕСЛИ(A:A; "Еда"; B:B)    → только «Еда»
= СУММ(B:B)                     → всё

8.3. Склад

D2: =B2-C2 (остаток). E2: =ЕСЛИ(D2<20; "Да"; "Нет") (заказать, если мало).

8.4. Посещаемость

Всего занятий A1=30, пропусков B1=4:

= (A1 - B1) / A1

Формат Процентный → 86,67 % присутствия.

8.5. Таблица умножения автозаполнением

  • A2:A11 — числа 1…10.
  • B1 = =A2*1, C1 = =A2*2 (или заголовки 1…10, B2 = =$A2*B$1 с абсолютной строкой заголовка).
  • Потяните маркер — Excel сам сдвигает ссылки.

9. Динамические массивы (Excel 365, Google Sheets)

9.1. ФИЛЬТР

= ФИЛЬТР(A2:C100; B2:B100>50)

Одна формула разливается на несколько строк — все записи, где B > 50.

9.2. УНИК

= УНИК(A2:A100)

Список без повторов (уникальные фамилии, категории).

9.3. СОРТ + ФИЛЬТР

= СОРТ(ФИЛЬТР(A2:C100; B2:B100>50); 3; -1)

Отфильтровали → отсортировали по 3-му столбцу по убыванию (-1).

10. Шаблон «прайс + заказ» — полный разбор

Лист Прайс: A — код, B — название, C — цена.

Лист Заказ:

A (код) B (шт) C (цена) D (сумма)
101 10
999 1

C2 — цена из прайса или 0, если код битый:

= ЕСЛИОШИБКА(ВПР(A2; Прайс!A:C; 3; ЛОЖЬ); 0)
Часть Зачем
ВПР(..) подставить цену
ЕСЛИОШИБКА(..; 0) код 999 не найден → 0, а не #Н/Д

D2: =B2*C2 → для 101: 10×50 = 500.

D100 (итог): =СУММ(D2:D99).

Скопируйте C2:D2 вниз — формулы подстроятся под A3, A4…

11. Каркас «Расходы» для лабораторной

A B C D
Статья Сумма Дата Крупная?
Продукты 5000 01.03.2026
Транспорт 1200 05.03.2026
Итого
  • B4: =СУММ(B2:B3) → 6200
  • D2: =ЕСЛИ(B2>3000; "Да"; "Нет") → для 5000 Да; скопировать в D3 → Нет

Частые ошибки

Ошибка Причина Что сделать
#ДЕЛ/0! Деление на ноль =ЕСЛИОШИБКА(..) или проверка =ЕСЛИ(B1=0; "—"; A1/B1)
#Н/Д ВПР не нашёл ключ Проверить код, СЖПРОБЕЛЫ, ЛОЖЬ в 4-м аргументе
#ЗНАЧ! Текст там, где ждали число ЗНАЧЕН() или исправить ячейку
#ИМЯ? Опечатка в имени функции СУММ, не SUM в русском Excel
#ПУСТО! Пересечение диапазонов A1:A5 и B3:B7 в одной формуле Один диапазон или ; между аргументами, не пробел
Формула как текст Нет = или ячейка в формате «Текст» Поставить =, формат «Общий»
При копировании «плывёт» ставка Относительная ссылка $B$1 для фиксированной ячейки
15 % даёт неверную скидку В ячейке 15, а не 0,15 Делить на 100: B1/100

Шпаргалка

Задача Excel (RU) Google Sheets
Сумма СУММ SUM
Среднее СРЗНАЧ AVERAGE
Условие ЕСЛИ IF
Сумма по условию СУММЕСЛИ SUMIF
Два условия СУММЕСЛИМН SUMIFS
Поиск ВПР / ПРОСМОТРX VLOOKUP / XLOOKUP
Универсальный поиск ИНДЕКС+ПОИСКПОЗ INDEX+MATCH
Сцепить текст & или СЦЕП TEXTJOIN
Сегодня СЕГОДНЯ() TODAY()
Ошибка → текст ЕСЛИОШИБКА IFERROR
Уникальные УНИК UNIQUE
Фильтр ФИЛЬТР FILTER

Что гуглят — короткие ответы

Запрос Ответ в одну строку Раздел
Как сложить ячейки в excel =A1+B1 или =СУММ(A1:A10) СУММ
Средний балл формула =СРЗНАЧ(B2:D2) СРЗНАЧ
Процент от числа =A1*B1/100 если B1=15 Скидка
Если больше 50 то =ЕСЛИ(A1>50; "да"; "нет") ЕСЛИ
Сумма если текст совпадает =СУММЕСЛИ(A:A; "текст"; B:B) СУММЕСЛИ
Vlookup не работает Ключ в 1-м столбце, 4-й аргумент ЛОЖЬ ВПР
Дни между датами =B1-A1 Даты
Excel, Google Sheets или Python?

Таблицы — быстрый старт для школьной и студенческой работы: видно данные, преподаватель проверяет формулы в строке fx.

Когда строк тысячи и нужен git — смотрите Pandas и маршрут Excel → R → Python.