Всем привет! Я - практикующий исследователь данных, и на этом канале делюсь тем, что реально работает в IT. Никакой сухой теории, только личный опыт, рабочие инструменты и грабли, на которые я уже наступил за вас. Рад, что вы здесь!🧸
За несколько лет работы аналитиком я использовал десятки формул Excel. Большинство оказались бесполезны, несколько реально нужны. Вот формулы которые использую почти каждый день и которые решают 90% задач аналитика (если это конечно касается Excel и мне не проще сделать по-быстрому с Pandas).
В основном формулы я использую в Excel и Гугл таблицах, но бывает и на своем ноуте в Libre Office, там бывает формулы отличаются, если не подходят формулы от Excel, то лучше погуглить и не отчаиваться.😄
Еще важный момент, я описываю функции на английском, так как я всегда ставлю формулы в настройках на английском, мне так легче из-за схожести функций в Python. Но думаю не мне одному проще, хотя помню когда учился в универе преимущественно формулы ставил русские, то есть вместо sum() было сумм() и т.д.
Базовые агрегатные функции
Это основа. Используются постоянно.
SUM - сумма
=SUM(A1:A10)
Суммирует диапазон ячеек. Используется постоянно.
Практические примеры:
=SUM(A1:A100) # Сумма всех значений
=SUM(A1:A10, C1:C10) # Сумма нескольких диапазонов
=SUM(A1:A10)*1.2 # Сумма с умножением
Когда использовать:
- Подсчет общей выручки
- Суммирование метрик
- Итоговые значения
- Проверка сумм
AVERAGE - среднее
=AVERAGE(A1:A10)
Считает среднее значение. Для анализа метрик.
Практические примеры:
=AVERAGE(A1:A100) # Среднее всех значений
=AVERAGE(A1:A10, C1:C10) # Среднее нескольких диапазонов
Когда использовать:
- Средняя цена товара
- Средний чек
- Средняя конверсия
- Типичные значения
COUNT - количество
=COUNT(A1:A10)
Считает количество чисел. Для подсчета записей.
Практические примеры:
=COUNT(A1:A100) # Количество чисел
=COUNT(A:A) # Вся колонка
Когда использовать:
- Количество заказов
- Количество клиентов
- Количество записей
- Подсчет элементов
COUNTA - количество непустых
=COUNTA(A1:A10)
Считает все непустые ячейки. Включая текст.
Практические примеры:
=COUNTA(A1:A100) # Все непустые ячейки
=COUNTA(A:A) # Вся колонка
Когда использовать:
- Количество заполненных полей
- Количество записей с данными
- Проверка заполненности
MAX / MIN - максимум / минимум
=MAX(A1:A10)
=MIN(A1:A10)
Находит максимальное и минимальное значение.
Практические примеры:
=MAX(A1:A100) # Максимальное значение
=MIN(A1:A100) # Минимальное значение
=MAX(A1:A10)-MIN(A1:A10) # Размах значений
Когда использовать:
- Самая высокая цена
- Самая низкая цена
- Диапазон значений
- Выбросы
Условные функции
Основа для сложной логики. Используются постоянно.
IF - условие
=IF(A1>100, "Высокий", "Низкий")
Проверяет условие, возвращает значение. Основа для сложной логики.
Практические примеры:
=IF(A1>1000, "Дорогой", "Дешевый") # Простое условие
=IF(A1>100, "Высокий", IF(A1>50, "Средний", "Низкий")) # Вложенные условия
=IF(AND(A1>100, B1<50), "Да", "Нет") # С логическими операторами
Когда использовать:
- Классификация значений
- Условная логика
- Проверки
- Маркировка данных
AND / OR - логические операторы
=IF(AND(A1>100, B1<50), "Да", "Нет")
=IF(OR(A1>100, B1<50), "Да", "Нет")
Комбинирует условия. Для сложных проверок.
Практические примеры:
=IF(AND(A1>1000, A1<5000), "Средний", "Другой") # Диапазон
=IF(OR(A1="A", A1="B"), "Категория A/B", "Другая") # Одно из значений
Когда использовать:
- Сложные условия
- Множественные проверки
- Комбинированные логические выражения
IFERROR - обработка ошибок
=IFERROR(A1/B1, 0)
Возвращает значение если ошибка. Избегает #DIV/0! и других ошибок.
Практические примеры:
=IFERROR(A1/B1, 0) # Заменить ошибку на 0
=IFERROR(VLOOKUP(A1, Table, 2, FALSE), "Не найдено") # Обработка VLOOKUP
Когда использовать:
- Деление на ноль
- Ошибки в формулах
- Отсутствие значений
- Чистые отчеты
Функции поиска
Самая важная группа. Используются постоянно.
VLOOKUP (ВПР по русски) - поиск по вертикали
=VLOOKUP(A1, Table, 2, FALSE)
Ищет значение в таблице, возвращает из другой колонки. Самая важная формула.
Параметры:
- A1 - что ищем
- Table - где ищем (таблица)
- 2 - номер колонки для возврата
- FALSE - точное совпадение (обязательно!)
Практические примеры:
=VLOOKUP(A1, Products!A:B, 2, FALSE) # Поиск цены товара
=VLOOKUP(A1, Table, 2, FALSE) # Простой поиск
Когда использовать:
- Объединение таблиц
- Поиск значений
- Справочники
- Связывание данных
Ограничения:
- Ищет только слева направо
- Первая колонка должна быть ключом
- Медленно на больших данных
INDEX + MATCH - гибкий поиск
=INDEX(Table, MATCH(A1, Column, 0), 2)
Более гибкая альтернатива VLOOKUP. Работает в обе стороны.
Практические примеры:
=INDEX(B:B, MATCH(A1, A:A, 0)) # Поиск в колонке B по значению в A
=INDEX(Table, MATCH(A1, Column, 0), 2) # Поиск в таблице
Когда использовать:
- Поиск справа налево
- Гибкий поиск
- Более сложные случаи
- Когда VLOOKUP не подходит
Преимущества:
- Работает в обе стороны
- Более гибкий
- Быстрее на больших данных
- Нет ограничений VLOOKUP
XLOOKUP - новый поиск
=XLOOKUP(A1, Column, ResultColumn)
Современная замена VLOOKUP. Проще и быстрее. Если есть в вашей версии Excel.
Практические примеры:
=XLOOKUP(A1, A:A, B:B) # Простой поиск
=XLOOKUP(A1, A:A, B:B, "Не найдено") # С значением по умолчанию
Когда использовать:
- Если есть в вашей версии Excel
- Более простой синтаксис
- Быстрее чем VLOOKUP
- Лучшая альтернатива
Текстовые функции
Для обработки текстовых данных. Используются часто.
LEFT / RIGHT / MID - извлечение текста
=LEFT(A1, 5)
=RIGHT(A1, 5)
=MID(A1, 2, 5)
Извлекает часть текста. Для обработки данных.
Практические примеры:
=LEFT(A1, 3) # Первые 3 символа
=RIGHT(A1, 5) # Последние 5 символов
=MID(A1, 2, 10) # 10 символов начиная с 2
Когда использовать:
- Извлечение кодов
- Обработка текста
- Парсинг данных
- Очистка данных
FIND / SEARCH - поиск в тексте
=FIND("текст", A1)
=SEARCH("текст", A1)
Находит позицию текста. FIND чувствителен к регистру, SEARCH нет.
Практические примеры:
=FIND("@", A1) # Позиция @ в email
=SEARCH("text", A1) # Поиск без учета регистра
Когда использовать:
- Поиск подстрок
- Проверка наличия текста
- Парсинг данных
- Обработка текста
CONCAT / CONCATENATE - объединение
=CONCAT(A1, " ", B1)
=CONCATENATE(A1, " ", B1)
Объединяет текст. CONCAT проще.
Практические примеры:
=CONCAT(A1, " ", B1) # Объединение с пробелом
=CONCATENATE(A1, "-", B1) # С разделителем
Когда использовать:
- Объединение колонок
- Создание ключей
- Форматирование данных
TRIM - удаление пробелов
=TRIM(A1)
Удаляет лишние пробелы. Для очистки данных.
Практические примеры:
=TRIM(A1) # Удалить пробелы
=TRIM(" text ") # Результат: "text"
Когда использовать:
- Очистка данных
- Удаление лишних пробелов
- Подготовка данных
Функции дат
Для работы с датами. Используются часто.
TODAY / NOW - текущая дата
=TODAY()
=NOW()
Возвращает текущую дату и время. Для динамических отчетов.
Практические примеры:
=TODAY() # Текущая дата
=NOW() # Текущая дата и время
=A1-TODAY() # Разница дней
Когда использовать:
- Динамические отчеты
- Автоматическое обновление
- Расчеты с датами
YEAR / MONTH / DAY - части даты
=YEAR(A1)
=MONTH(A1)
=DAY(A1)
Извлекает год, месяц, день. Для группировки по периодам.
Практические примеры:
=YEAR(A1) # Год
=MONTH(A1) # Месяц
=DAY(A1) # День
=YEAR(A1)&"-"&MONTH(A1) # Год-месяц
Когда использовать:
- Группировка по периодам
- Анализ по месяцам
- Фильтрация по датам
DATEDIF - разница дат
=DATEDIF(A1, B1, "d")
Считает разницу между датами. "d" для дней, "m" для месяцев, "y" для лет.
Практические примеры:
=DATEDIF(A1, B1, "d") # Разница в днях
=DATEDIF(A1, B1, "m") # Разница в месяцах
=DATEDIF(A1, B1, "y") # Разница в годах
Когда использовать:
- Возраст клиента
- Длительность периода
- Расчет сроков
EOMONTH - конец месяца
=EOMONTH(A1, 0)
Возвращает последний день месяца. Для отчетов по месяцам.
Практические примеры:
=EOMONTH(A1, 0) # Конец текущего месяца
=EOMONTH(A1, 1) # Конец следующего месяца
Когда использовать:
- Отчеты по месяцам
- Расчет периодов
- Группировка данных
Условные агрегации
Очень полезные функции. Используются постоянно.
COUNTIF - условный подсчет
=COUNTIF(A1:A10, ">100")
Считает ячейки по условию. Очень полезная формула.
Практические примеры:
=COUNTIF(A1:A100, ">1000") # Больше 1000
=COUNTIF(A1:A100, "=A") # Равно A
=COUNTIF(A1:A100, "*текст*") # Содержит текст
Когда использовать:
- Подсчет по условию
- Фильтрация данных
- Анализ данных
SUMIF - условная сумма
=SUMIF(A1:A10, ">100", B1:B10)
Суммирует по условию. Для фильтрации данных.
Практические примеры:
=SUMIF(A1:A100, ">1000", B1:B100) # Сумма где A > 1000
=SUMIF(A1:A100, "=A", B1:B100) # Сумма для категории A
Когда использовать:
- Сумма по условию
- Фильтрация и агрегация
- Анализ данных
AVERAGEIF - условное среднее
=AVERAGEIF(A1:A10, ">100", B1:B10)
Считает среднее по условию.
Практические примеры:
=AVERAGEIF(A1:A100, ">1000", B1:B100) # Среднее где A > 1000
Когда использовать:
- Среднее по условию
- Фильтрация и агрегация
COUNTIFS / SUMIFS - множественные условия
=COUNTIFS(A1:A10, ">100", B1:B10, "<50")
=SUMIFS(C1:C10, A1:A10, ">100", B1:B10, "<50")
Несколько условий одновременно. Мощная формула.
Практические примеры:
=COUNTIFS(A1:A100, ">1000", B1:B100, "=A") # Два условия
=SUMIFS(C1:C100, A1:A100, ">1000", B1:B100, "=A") # Сумма с двумя условиями
Когда использовать:
- Множественные условия
- Сложная фильтрация
- Детальный анализ
Сводные таблицы
Сводные таблицы решают большинство задач аналитика. Группировка, агрегация, фильтрация без формул.
Когда использовать
Группировка данных:
- По категориям
- По периодам
- По любым полям
- Быстро и легко
Агрегация по категориям:
- Сумма
- Среднее
- Количество
- Любые метрики
Быстрый анализ:
- Не нужно писать формулы
- Визуальный интерфейс
- Быстро
- Интерактивно
Интерактивные отчеты:
- Фильтры
- Срезы
- Динамические изменения
- Презентация данных
Практические примеры
Топ-10 товаров:
- Создайте сводную таблицу
- Группируйте по товару
- Сортируйте по сумме продаж
- Покажите топ-10
Сравнение периодов:
- Используйте VLOOKUP для поиска предыдущего периода
- Считайте разницу
- Считайте процент изменения
- Визуализируйте
Фильтрация данных:
- Используйте COUNTIFS для подсчета по условиям
- Используйте SUMIFS для суммирования по условиям
- Комбинируйте условия
- Анализируйте результаты
Типичные задачи
Разбор реальных задач.
Задача 1: Топ-10 по продажам
Способ 1: Формулы
=SUMIF(A:A, A1, B:B) # Сумма по товару
=COUNTIFS(A:A, A1, B:B, ">1000") # Количество продаж > 1000
Способ 2: Сводная таблица
- Создайте сводную таблицу
- Товар в строки
- Сумма продаж в значения
- Сортировка по убыванию
- Топ-10
Способ 2 проще и быстрее.
Задача 2: Сравнение периодов
Формулы:
=VLOOKUP(A1, Period1, 2, FALSE) # Предыдущий период
=A1-B1 # Разница
=(A1-B1)/B1 # Процент изменения
Практический пример:
Текущий период: 1000
Предыдущий период: 800
Разница: 200
Рост: 25%
Задача 3: Фильтрация данных
COUNTIFS:
=COUNTIFS(A:A, ">1000", B:B, "=A") # Количество где A > 1000 и B = A
SUMIFS:
=SUMIFS(C:C, A:A, ">1000", B:B, "=A") # Сумма где A > 1000 и B = A
Что не нужно знать
Избегайте сложных формул если не нужны.
Слишком сложные формулы:
- Массивы (если не нужны)
- Макросы (если можно без них)
- Формулы которые редко используются
- Формулы которые можно заменить сводными таблицами
Когда использовать:
- Только если действительно нужно
- Если проще не получается
- Если понимаете как работает
- Если используете регулярно
Практические советы
Рекомендации для работы с формулами.
- Начните с базовых функций. SUM, AVERAGE, COUNT - это основа. Освойте их сначала.
- Изучите VLOOKUP (ВПР). Это самая важная формула для аналитика. Используется постоянно.
- Используйте сводные таблицы. Часто проще чем формулы. Группировка и агрегация без формул.
- Комбинируйте формулы. IF + VLOOKUP, SUMIFS + другие формулы. Комбинируйте для сложных задач.
- Документируйте сложные формулы. Комментируйте что делает формула. Помогает в будущем.
- Проверяйте результаты. Всегда проверяйте формулы на тестовых данных. Убедитесь что правильно.
- Используйте именованные диапазоны. Упрощает формулы. Легче читать и понимать.
Что делать дальше
Начните с базовых функций. Освойте SUM, AVERAGE, COUNT, VLOOKUP (ВПР). Этого достаточно для большинства задач.
Для изучения рекомендую:
- Практику на реальных данных - применяйте формулы на практике
- Создание своих шаблонов - переиспользуйте формулы
- Изучение сводных таблиц - часто проще чем формулы
- Комбинирование формул - для сложных задач
Важнее понимать что искать в данных, чем знать все формулы наизусть, ИМХО. Начинайте с простого, усложняйте постепенно.
Я не претендую на последнюю инстанцию, я пишу о своём пути и опыте. Спасибо что дочитали до конца. Подпишитесь👇👇👇, ставьте лайки 👍🏽👍🏽👍🏽 впереди много интересных статей про навыки, инструменты, обучение, лайфхаки и пути аналитика.