AI Reports
Excel — это инструмент, который почти все в компании знают, но используют на 10% его возможностей. Большинство людей ограничиваются SUM и простыми расчётами. А между тем, несколько правильно использованных формул могут сэкономить часы работы.
Давайте разберёмся, какие формулы действительно нужны для аналитики, и как их использовать на практике.
Категории формул
Все формулы Excel для анализа можно разделить на несколько групп:
- Поиск и соответствие — VLOOKUP, INDEX/MATCH, XLOOKUP
- Суммирование с условиями — SUMIF, SUMIFS, COUNTIF, COUNTIFS
- Анализ текста — LEFT, MID, FIND, CONCATENATE
- Дата и время — DATE, DATEDIF, MONTH, YEAR
- Логика — IF, AND, OR, NESTED IF
- Сводные таблицы — Pivot Table (не формула, но инструмент)
- Массивы — FILTER, UNIQUE (новые функции в Excel 365)
1. Поиск: VLOOKUP и INDEX/MATCH
VLOOKUP — вертикальный поиск
Самая популярная формула для поиска значения в таблице.
Синтаксис:
=VLOOKUP(искомое_значение, таблица_поиска, номер_столбца, [диапазон_поиска])Пример: У вас есть таблица продаж с ID клиента. Нужно добавить имя клиента из другой таблицы.
Таблица 1 (продажи):
| ID | Сумма |
|---|---|
| 101 | 5000 |
| 102 | 3000 |
Таблица 2 (клиенты):
| ID | Имя |
|---|---|
| 101 | Иван |
| 102 | Мария |
Формула:
=VLOOKUP(A2, Таблица2!$A$:$B$, 2, FALSE)Это вернёт “Иван” для ID 101.
Проблема с VLOOKUP:
- Работает только слева направо
- Если данные слева от нужного столбца, не работает
- Медленная на больших объёмах
INDEX/MATCH — более гибкий поиск
Синтаксис:
=INDEX(возвращаемый_диапазон, MATCH(значение, диапазон_поиска, 0))Пример: Того же результата, но более гибко
=INDEX(Таблица2!$B$:$B$, MATCH(A2, Таблица2!$A$:$A$, 0))Преимущества:
- Работает в любом направлении
- Быстрее чем VLOOKUP на больших данных
- Более понятно с первого взгляда
XLOOKUP — новое (Excel 2021+)
Если у вас свежий Excel, используйте XLOOKUP:
=XLOOKUP(A2, Таблица2!$A$:$A$, Таблица2!$B$:$B$)Это проще и понятнее. Рекомендуется для новых файлов.
Когда использовать:
- Нужно добавить данные из другой таблицы по совпадению ключа (ID, email, дата)
2. Суммирование с условиями
SUMIF — сумма при условии
Синтаксис:
=SUMIF(диапазон_для_проверки, критерий, диапазон_суммирования)Пример: Сумма продаж по каждому менеджеру
| Менеджер | Сумма |
|---|---|
| Иван | 5000 |
| Мария | 3000 |
| Иван | 2000 |
Нужно найти общую сумму Ивана:
=SUMIF($A$:$A$, "Иван", $B$:$B$)Результат: 7000 (5000 + 2000)
SUMIFS — сумма с несколькими условиями
Когда нужны несколько условий одновременно.
Синтаксис:
=SUMIFS(диапазон_суммирования, диапазон1, условие1, диапазон2, условие2, ...)Пример: Сумма продаж Ивана в Москве
| Менеджер | Город | Сумма |
|---|---|---|
| Иван | Москва | 5000 |
| Мария | СПб | 3000 |
| Иван | СПб | 2000 |
=SUMIFS($C$:$C$, $A$:$A$, "Иван", $B$:$B$, "Москва")Результат: 5000 (только строка с Иваном и Москвой)
COUNTIF и COUNTIFS — подсчёт
Синтаксис:
=COUNTIF(диапазон, критерий)
=COUNTIFS(диапазон1, условие1, диапазон2, условие2, ...)Пример: Сколько заказов от Ивана?
=COUNTIF($A$:$A$, "Иван")Сколько заказов от Ивана в Москве?
=COUNTIFS($A$:$A$, "Иван", $B$:$B$, "Москва")На практике:
| Менеджер | Заказов | Средний чек |
|---|---|---|
| Иван | =COUNTIF($A$:$A$, A2) | =SUMIF($A$:$A$, A2, $C$:$C$) / COUNTIF($A$:$A$, A2) |
| Мария | 5 | 1500 |
Это дает быстрый анализ по менеджерам.
3. Анализ текста
CONCATENATE и & — объединение текста
Нужно объединить несколько текстовых полей.
Вариант 1:
=CONCATENATE(A1, " ", B1)Вариант 2 (проще):
=A1&" "&B1Пример: Объединить Фамилию и Имя
=A2&" "&B2“Иванов” + ” ” + “Иван” = “Иванов Иван”
LEFT, MID, RIGHT — извлечение части текста
Извлечь часть строки.
LEFT(текст, количество_символов) — первые N символов
=LEFT(A1, 3)“Иванов” → “Ива”
MID(текст, начало, длина) — средняя часть
=MID(A1, 2, 2)“Иванов” → “ва”
RIGHT(текст, количество) — последние N символов
=RIGHT(A1, 3)“Иванов” → “нов”
FIND и SEARCH — поиск в тексте
Найти позицию подстроки.
=FIND("@", A1)“ivanov@mail.ru” → 7 (позиция @)
Применение: Проверить, есть ли @ в email
=IF(ISERROR(FIND("@", A1)), "Ошибка", "OK")TRIM — удалить лишние пробелы
=TRIM(A1)” Иван ” → “Иван” (пробелы удалены)
Применение: Очистить данные перед анализом (часто данные импортируются с лишними пробелами)
4. Дата и время
DATE — создание даты
=DATE(год, месяц, день)=DATE(2026, 3, 16)- → 16.03.2026
DATEDIF — разница между датами
=DATEDIF(дата_начала, дата_конца, единица)Единицы: “D” (дни), “M” (месяцы), “Y” (годы)
=DATEDIF(A1, A2, "D")Если A1=01.01.2026, A2=16.03.2026, результат = 74 дня
Применение: Посчитать возраст клиента, дни с момента покупки
=DATEDIF(DATE(1990, 5, 15), TODAY(), "Y")Вернёт возраст в годах
MONTH, YEAR, DAY — извлечение части даты
=MONTH(A1)16.03.2026 → 3
=YEAR(A1)16.03.2026 → 2026
Применение: Сгруппировать данные по месяцам
=SUMIF($A$:$A$, MONTH(A2)=3, $B$:$B$)Сумма всех заказов в марте
5. Логические функции
IF — условие
Синтаксис:
=IF(условие, если_правда, если_ложь)Пример: Если сумма > 5000, то “большой заказ”, иначе “маленький”
=IF(B2>5000, "Большой", "Маленький")NESTED IF — вложенные условия
=IF(B2>10000, "Большой", IF(B2>5000, "Средний", "Маленький"))AND, OR — логические операторы
=IF(AND(B2>5000, C2="Москва"), "ВИП", "Обычный")Если сумма > 5000 И город Москва, то “ВИП”
=IF(OR(B2>10000, C2="Иван"), "Отследить", "OK")Если сумма > 10000 ИЛИ менеджер Иван, то “Отследить”
6. Сводные таблицы (Pivot Table)
Сводная таблица — это не формула, но инструмент для быстрого анализа.
Когда использовать:
- Нужно быстро сгруппировать данные по разным параметрам
- Нужны кросс-табуляции (матрица)
- Нужны промежуточные итоги
Пример: У вас есть таблица с продажами (менеджер, город, дата, сумма). Нужно получить:
- Общая сумма по менеджерам
- Общая сумма по городам
- Общая сумма по месяцам
Как создать:
- Выделите данные (Ctrl+A)
- Вставка → Сводная таблица
- Перетащите поля: в Rows (менеджер), в Values (сумма)
- Результат — таблица с суммами по менеджерам
Результат:
| Менеджер | Итого |
|---|---|
| Иван | 25000 |
| Мария | 18000 |
| Общий итог | 43000 |
За 30 секунд получили анализ.
7. Новые функции (Excel 365)
FILTER — фильтрация данных
=FILTER(диапазон, условие)Пример: Вернуть только заказы Ивана
=FILTER(A:C, A:A="Иван")UNIQUE — уникальные значения
=UNIQUE(диапазон)Пример: Получить список уникальных менеджеров
=UNIQUE(A:A)Результат: Иван, Мария (без дубликатов)
SEQUENCE — последовательность чисел
=SEQUENCE(строк, столбцы, начало, шаг)=SEQUENCE(5, 1, 1, 1)Вернёт: 1, 2, 3, 4, 5
Практический пример: Анализ продаж
Имеются данные:
| ID | Дата | Менеджер | Город | Сумма |
|---|---|---|---|---|
| 1 | 01.03 | Иван | Москва | 5000 |
| 2 | 02.03 | Мария | СПб | 3000 |
| 3 | 03.03 | Иван | Москва | 2000 |
| 4 | 04.03 | Мария | Москва | 4000 |
Нужны метрики:
- Общая выручка за март
- Сумма по менеджерам
- Сумма по городам
- Средний чек по менеджеру
Используем формулы:
Общая выручка: =SUM(E2:E5)
Результат: 14000
Иван (SUMIF): =SUMIF($C$2:$C$5, "Иван", $E$2:$E$5)
Результат: 7000
Москва (SUMIF): =SUMIF($D$2:$D$5, "Москва", $E$2:$E$5)
Результат: 11000
Средний чек Ивана: =SUMIF($C$2:$C$5, "Иван", $E$2:$E$5) / COUNTIF($C$2:$C$5, "Иван")
Результат: 3500 (7000 / 2)Или используем сводную таблицу:
- Выделяем данные
- Вставка → Сводная таблица
- Rows: Менеджер, Values: Сумма
- За 30 секунд получили анализ
Частые ошибки
Ошибка 1: Забыли про абсолютные ссылки ($)
=SUMIF(A:A, "Иван", B:B) ❌ При копировании формула сломается
=SUMIF($A$:$A$, "Иван", $B$:$B$) ✓ ПравильноОшибка 2: VLOOKUP с сортировкой
Если данные отсортированы, VLOOKUP может вернуть неправильный результат.
=VLOOKUP(A2, Таблица2!$A$:$B$, 2, FALSE) ✓ Правильно (точное совпадение)
=VLOOKUP(A2, Таблица2!$A$:$B$, 2, TRUE) ❌ Неправильно (приблизительный поиск)Ошибка 3: Слишком сложные вложенные IF
=IF(A1>10, IF(B1>5, IF(C1="OK", "ДА", "НЕТ"), "НЕТ"), "НЕТ") ❌ Сложно читать
=IF(AND(A1>10, B1>5, C1="OK"), "ДА", "НЕТ") ✓ ПрощеКогда переходить на программирование
Excel формулы хороши, но есть лимиты:
Используйте Excel если:
- Данные < 100 000 строк
- Анализ простой (суммы, средние, условия)
- Нужна быстрая итерация (меняем числа, пересчитывается)
Переходите на Python/SQL если:
- Данные > 1 млн строк
- Анализ сложный (регрессия, кластеризация)
- Нужна автоматизация
- Нужны красивые графики и дашборды
Заключение
Десять формул, которые нужно знать каждому аналитику:
- VLOOKUP или INDEX/MATCH — поиск
- SUMIF — сумма с условием
- SUMIFS — сумма с несколькими условиями
- COUNTIF — подсчёт
- IF — логика
- LEFT/MID/RIGHT — работа с текстом
- TRIM — очистка данных
- DATE/DATEDIF — работа с датами
- Pivot Table — группировка
- CONCATENATE — объединение
Освойте эти формулы, и вы сможете решить 80% аналитических задач в Excel.
Если же анализ становится сложнее (много данных, сложные расчёты, красивые отчёты), рассмотрите специализированные инструменты: Python, SQL, BI системы, или готовые сервисы для анализа типа AI Reports, которые автоматически генерируют аналитические выводы из Excel без необходимости писать формулы.
Похожие статьи
