ВПР (VLOOKUP), ГПР, XLOOKUP, INDEX+MATCH — как подтянуть данные из другой таблицы без SQL.

ВПР (VLOOKUP):
=ВПР(искомое_значение; таблица; номер_столбца; [точное_совпадение])

Пример: подтянуть город по user_id
=ВПР(A2; $F$2:$H$100; 2; 0)
  A2          — что ищем (user_id)
  $F$2:$H$100 — таблица с ответами
  2           — номер столбца с результатом (город = 2-й)
  0           — ЛОЖЬ = точное совпадение (всегда!)

Ограничения ВПР:
  • Ищет только СЛЕВА направо
  • Медленно на больших данных
  • При добавлении столбца — ломается (номер 2 сместится)

INDEX + MATCH (лучше ВПР):
=ИНДЕКС(столбец_результата; ПОИСКПОЗ(искомое; столбец_поиска; 0))
=INDEX(H:H; MATCH(A2; F:F; 0))
  Работает в любую сторону, быстрее, стабильнее.

XLOOKUP (Excel 2021+, Google Sheets = XLOOKUP):
=XLOOKUP(A2; F:F; H:H; "Не найдено")
  Проще, поддерживает возврат нескольких столбцов.

Группировка, фильтрация, вычисляемые поля — как за 2 минуты сделать то, что в SQL занимает 20.

Создание сводной таблицы:
  Выдели данные → Вставка → Сводная таблица

Структура:
  Строки    = GROUP BY (например, Месяц, Страна)
  Столбцы   = второе GROUP BY (например, Категория)
  Значения  = агрегат (SUM, COUNT, AVERAGE)
  Фильтры   = WHERE (применяется ко всей таблице)

Полезные настройки:
  • Правая кнопка на значение → «Дополнительные вычисления»:
    % от итога, % от строки, % от столбца, нарастающий итог
  • Группировка дат: правая кнопка → Группировать → по месяцам
  • Вычисляемое поле: Анализ → Поля и элементы → Поле
    Формула: = Выручка / Заказы  → средний чек

Слайсеры (Slicers):
  Анализ → Вставить срез → удобный фильтр для дашбордов

Google Sheets QUERY — аналог SQL в таблицах:
=QUERY(A:F; "SELECT B, SUM(E) WHERE C='KZ' GROUP BY B LABEL SUM(E) 'Выручка'")

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

СУММЕСЛИ / SUMIF:
=СУММЕСЛИ(диапазон_условия; условие; диапазон_суммирования)
=СУММЕСЛИ(C:C; "KZ"; E:E)   — сумма только по Казахстану

СУММЕСЛИМН / SUMIFS (несколько условий):
=СУММЕСЛИМН(E:E; C:C; "KZ"; D:D; "paid")

СЧЁТЕСЛИ / COUNTIF:
=СЧЁТЕСЛИ(C:C; "KZ")         — количество строк по Казахстану
=СЧЁТЕСЛИ(C:C; ">"&10000)    — количество сумм > 10000

ЕСЛИ / IF:
=ЕСЛИ(E2>50000; "крупный"; ЕСЛИ(E2>10000; "средний"; "малый"))

ЕСЛИОШИБКА / IFERROR:
=ЕСЛИОШИБКА(ВПР(A2;F:G;2;0); "Не найдено")  — убирает #Н/Д

ТЕКСТ / TEXT (форматирование дат):
=ТЕКСТ(A2; "ММММ ГГГГ")    → "Январь 2024"
=ТЕКСТ(A2; "ДД.ММ.ГГГГ")   → "15.01.2024"

УНИКАЛЬНЫЕ / UNIQUE (Google Sheets / Excel 365):
=УНИКАЛЬНЫЕ(C2:C1000)        — список уникальных значений

ФИЛЬТР / FILTER:
=ФИЛЬТР(A2:E100; C2:C100="KZ")   — фильтрует диапазон

Какой тип графика выбрать, conditional formatting для KPI-таблиц, мини-спарклайны.

Правило выбора графика:
  Динамика во времени      → Линейный (Line)
  Сравнение категорий      → Столбчатый (Bar/Column)
  Доли от целого           → Круговой / Кольцевой (только если <= 5 сегментов)
  Распределение            → Гистограмма (Histogram)
  Корреляция двух метрик   → Точечный (Scatter)
  Отклонение от цели       → Bullet Chart / Gauge

Условное форматирование (Conditional Formatting):
  Выдели ячейки → Главная → Условное форматирование
  • Цветовые шкалы: красный/жёлтый/зелёный
  • Иконки: стрелки роста/падения
  • Правило формулы: =$E2<1000 → красная заливка

Спарклайны (Sparklines):
  Вставка → Спарклайны → мини-график в ячейке
  Показывает тренд без занятия места

Dashboard-советы:
  1. Один экран — одна страница (без скролла)
  2. Цвет = значение (зелёный = хорошо, красный = плохо)
  3. Самое важное — в левый верхний угол
  4. Заголовки описывают инсайт, а не тип графика
     «Выручка за квартал» ✗
     «Выручка Q1 выросла на 23% — рекорд за 2 года» ✓

Топ-20 шорткатов которые ускоряют работу в Excel в 3 раза. Навигация, выделение, форматирование.

Навигация:
  Ctrl + End          → последняя заполненная ячейка
  Ctrl + Home         → начало таблицы (A1)
  Ctrl + Стрелка      → к краю заполненного диапазона
  Ctrl + Shift + End  → выделить до последней ячейки

Редактирование:
  Ctrl + D            → скопировать ячейку сверху
  Ctrl + R            → скопировать ячейку слева
  Alt + Enter         → перенос строки внутри ячейки
  F2                  → войти в режим редактирования ячейки
  F4                  → переключить $A$1 → A$1 → $A1 → A1

Форматирование:
  Ctrl + 1            → диалог «Формат ячеек»
  Ctrl + Shift + $    → денежный формат
  Ctrl + Shift + %    → процентный формат
  Alt + H + H         → выбор цвета заливки

Данные:
  Ctrl + T            → создать умную таблицу (Table)
  Alt + D + F + F     → автофильтр
  Ctrl + Shift + L    → включить/выключить фильтры

Умные таблицы (Ctrl+T) — must have:
  • Автоматически расширяются при добавлении строк
  • Формулы ссылаются на [@ColumnName] вместо A2
  • Сводные таблицы автоматически обновляются