Если ты аналитик или просто часто работаешь с данными, то ты точно знаешь это чувство: куча данных, срочный запрос от руководства и мысль «как бы это всё быстро посчитать?».
Паниковать не надо, так как все решаемо. Попробуй, разберись, освой и ты регулярно будешь экономить себе кучу рабочего времени!
Поможет нам в этом Excel. Это наш верный помощник, а формулы в нём — это как суперспособности. У всех кто уже научился профессионально работать с этой чудо-программой есть свои фишки, которыми они пользуются чаще всего.
Предлагаю перенять их опыт и разобрать те формулы, которые реально спасают жизнь аналитиков и всех. кто вынужден работать с данными каждый день. Беру только самое важное, ибо возможности Excel неисчислимы. Жизни не хватит, чтобы освоить их все.
Краткая инструкция: как применять формулы в Excel
Если вдруг вы пробуете работать с формулами первый раз, небольшие подсказки в помощь).
1. Все формулы начинаются со знака «=»
Без этого Excel подумает, что ты просто вводишь текст. Хочешь посчитать? Ставь «=» первым символом.
2. Пиши формулы прямо в ячейке или в строке формул
Кликнул на ячейку → написал =A1+B1 → нажал Enter. Результат появится в этой же ячейке.
3. Ссылайся на другие ячейки, а не вводи числа вручную
Не так: =100+50
А так: =A2+B2 (где в A2 — 100, в B2 — 50)
Зачем: если данные в A2 или B2 поменяются, результат пересчитается автоматически.
4. Используй Мастера функций (fx)
Не помнишь точное название формулы? Нажми кнопку fx слева от строки формул. Появится меню, где можно найти нужную функцию по категории или описанию. Excel сам подскажет, какие аргументы куда вставлять.
5. Копируй формулы правильно — с помощью маркера заполнения
Написал формулу в первой ячейке? Наведи курсор на её правый нижний угол (появится чёрный крестик «+»), зажми левую кнопку мыши и протяни вниз или в сторону. Формула скопируется, но ссылки автоматически изменятся относительно новой позиции (например, =A1+B1 превратится в =A2+B2).
Хитрость: чтобы ссылка не менялась при копировании, используй абсолютную ссылку со знаком $:
- =$A$1 — не изменится никогда (зафиксированы и столбец, и строка).
- =A$1 — при копировании вниз строка останется 1, а столбец может меняться.
6. Проверяй и исправляй ошибки
Если видишь вместо результата #ДЕЛ/0!, #Н/Д, #ИМЯ? — значит, где-то ошибка.
#ДЕЛ/0! — пытаешься делить на ноль.
#Н/Д — функция не нашла данные (часто в ВПР).
#ИМЯ? — Excel не понял название функции (опечатка, например, =СУМА вместо =СУММ).
Исправляй исходные данные или саму формулу.
7. Для отладки используй клавишу F9
Выдели часть формулы в строке формул → нажми F9 → увидишь, какой результат даёт этот кусок кода. Не забудь нажать Esc для отмены, а не Enter!
8. Сочетания клавиш для скорости
F2 — быстро перейти в ячейку для редактирования.
Ctrl + ` (клавиша над Tab) — показать/скрыть все формулы на листе.
Alt + = — быстро вставить =СУММ() над выделенным диапазоном.
Кратко:
- Определи задачу (посчитать сумму, сравнить, найти значение).
- Выбери ячейку для результата.
- Поставь «=».
- Введи название функции или начни писать простые действия (A1*B1).
- Используй Мастер функций (fx), если не уверен.
- Нажми Enter.
- Протяни формулу на остальные строки, если нужно.
💥 Главный принцип: Экспериментируй! Сделал что-то не так — всегда можно отменить (Ctrl+Z) или просто удалить формулу и начать заново.
Какие формулы используют в Excel те, кто много работает с данными?
Фундамент: без этого никуда
Начнём с базиса. Эти формулы — как хлеб с маслом. Те, кто работают с данными используют их постоянно, даже не задумываясь.
1. СУММ (SUM) — всё понятно из названия. Но хитрость в том, что суммировать можно не только простой диапазон =СУММ(A1:A10), но и выборочно: =СУММ(A1:A10; C1:C5). Главный помощник для итогов.
2. СРЗНАЧ (AVERAGE) — среднее арифметическое. Помни, что Excel игнорирует пустые ячейки, но учитывает нули. Если нужна средняя без нулей — придётся хитрить (но об этом позже).
3. СЧЁТ (COUNT) и СЧЁТЗ (COUNTA) — близнецы-братья, но разные. СЧЁТ считает только ячейки с числами. СЧЁТЗ — все непустые ячейки (и текст, и даты, и числа). Незаменимы, когда нужно понять, сколько у тебя вообще записей в столбце.
4. МАКС (MAX) и МИН (MIN) — быстро находим крайние значения. Подозрительно большой заказ? Ищем максимум. Аномально низкая посещаемость? Смотрим минимум.
Магия поиска и ссылок: VLOOKUP и его друзья
Вот мы и подошли к святому святых аналитика. Эти формулы — главные «склеиватели» данных из разных таблиц.
1. ВПР (VLOOKUP) — легендарная и немного капризная функция. Ищет значение в первом столбце таблицы и возвращает данные из другого столбца в той же строке.
=ВПР(Что_ищем; Где_ищем_таблицу; Номер_столбца_для_вывода; [Точный_поиск])
Запомни раз и навсегда: четвёртый аргумент — ЛОЖЬ (FALSE) для точного поиска (используется в 95% случаев). И да, искомое значение всегда должно быть в первом столбце диапазона поиска. Это её главный минус.
2. ГПР (HLOOKUP) — то же самое, но для горизонтальных таблиц. Используется реже, но знать стоит.
3. ПОИСКПОЗ (MATCH) + ИНДЕКС (INDEX) — мощная альтернатива ВПР. Не стоит боятся, если столбец для вывода левее столбца поиска.
=ИНДЕКС(Столбец_с_нужными_данными; ПОИСКПОЗ(Что_ищем; Столбец_для_поиска; 0))
Сначала ПОИСКПОЗ находит номер строки, а затем ИНДЕКС вытаскивает значение из этой строки в другом столбце. Гибко и без ограничений ВПР.
4. XLOOKUP (только в новых версиях Excel) — мечта аналитика. Объединил в себе силу ВПР и ИНДЕКС/ПОИСКПОЗ. Ищет в любом направлении, возвращает из любого. Синтаксис простой: =XLOOKUP(Что_ищем; Где_ищем; Что_вернуть). Если у тебя есть новая версия — начинай использовать сразу же.
Логика и условия: чтобы Excel «думал» за тебя
Здесь мы учим Excel принимать решения. Основа любой аналитической модели.
1. ЕСЛИ (IF) — король условий.
=ЕСЛИ(Логическое_выражение; Значение_если_ИСТИНА; Значение_если_ЛОЖЬ)
Например: =ЕСЛИ(B2>100; "Крупный заказ"; "Малый заказ").
Но не стоит делать из неё матрёшку больше 3-4 уровней — становится нечитаемо. Для сложных условий есть...
2. И (AND), ИЛИ (OR), НЕ (NOT) — логические операторы. Часто живут внутри ЕСЛИ.
=ЕСЛИ(И(A2>10; B2<5); "Да"; "Нет") — вернёт «Да», только если оба условия true.
3. ЕСЛИМН (IFS — в новых Excel) и СУММЕСЛИ (SUMIF) — твои лучшие друзья.
ЕСЛИМН позволяет записывать несколько условий без вложений: =ЕСЛИМН(A1>90; "Отлично"; A1>70; "Хорошо"; A1>50; "Удовл.").
СУММЕСЛИ и его брат СУММЕСЛИМН (SUMIFS) для суммирования по условию — это must have. =СУММЕСЛИМН(Столбец_для_суммы; Столбец_условия1; Условие1; Столбец_условия2; Условие2; ...). Хочешь просуммировать продажи только по «Москве» и только за «январь»? Это твой инструмент. Аналогично работают СРЗНАЧЕСЛИ (AVERAGEIF) и СЧЁТЕСЛИ (COUNTIF).
Текст: разбираем, склеиваем, извлекаем
Данные редко бывают чистыми. Часто нужно вытащить из ячейки кусок текста или собрать что-то из частей.
1. ЛЕВСИМВ (LEFT), ПРАВСИМВ (RIGHT), ПСТР (MID) — твои хирургические инструменты.
=ЛЕВСИМВ(A1; 3) — возьмёт 3 символа слева.
=ПРАВСИМВ(A1; 5) — 5 символов справа.
=ПСТР(A1; 4; 2) — начнёт с 4-го символа и возьмёт 2 символа. Идеально, чтобы вытащить код из строки вроде «ORD-2023-5678».
2. НАЙТИ (FIND) и ПОИСК (SEARCH) — ищут позицию нужного текста внутри строки. НАЙТИ — чувствителен к регистру, ПОИСК — нет. Часто используются в паре с ПСТР.
3. СЦЕПИТЬ (CONCAT) и ТЕКСТОБЪЕДИН (TEXTJOIN — новая) — мастера склейки. ТЕКСТОБЪЕДИН — это просто песня: можно указать разделитель и игнорировать пустые ячейки. =ТЕКСТОБЪЕДИН(", "; ИСТИНА; A1; B1; C1) — склеит значения через запятую, пропуская пустые.
4. ЗАМЕНИТЬ (REPLACE) и ПОДСТАВИТЬ (SUBSTITUTE) — меняют текст. ПОДСТАВИТЬ меняет конкретный символ или слово: =ПОДСТАВИТЬ(A1; "."; ",") — точки на запятые (для смены разделителя).
Даты и время: когда каждая секунда на счету
Работа с датами — отдельный вид искусства. Excel хранит даты как числа (количество дней с 01.01.1900), поэтому с ними можно делать арифметику.
1. СЕГОДНЯ (TODAY) и ТДАТА (NOW) — возвращают текущую дату (и время). Динамические формулы — завтра их значение само обновится. Незаменимы для отчётов «по состоянию на».
2. ДЕНЬ (DAY), МЕСЯЦ (MONTH), ГОД (YEAR) — разбирают дату на части. =МЕСЯЦ(A1) вернёт номер месяца из даты в ячейке A1.
3. ДАТА (DATE) — собирает дату из частей. =ДАТА(2023; 12; 31) вернёт 31.12.2023. Очень помогает при создании динамических дат.
4. РАБДЕНЬ (WORKDAY) и РАБДЕНЬ.МЕЖД (WORKDAY.INTL) — расчёт рабочих дней, исключая выходные и праздники. Хочешь узнать, когда будет выполнена задача через 10 рабочих дней? Это сюда.
5. ДОЛЯГОДА (YEARFRAC) — вычисляет долю года между двумя датами. Ключевая функция для финансовых расчётов и стажа.
Продвинутая аналитика: чуть-чуть магии
Эти формулы уже для тех, кто хочет копнуть глубже.
1. ЕЧИСЛО (ISNUMBER), ЕТЕКСТ (ISTEXT), ЕОШИБКА (ISERROR) — проверяют тип содержимого или наличие ошибок. Используются внутри ЕСЛИ для защиты формул от сбоев: =ЕСЛИ(ЕОШИБКА(A1/B1); 0; A1/B1).
2. ВЫБОР (CHOOSE) — своего рода «переключатель». =ВЫБОР(номер_индекса; значение1; значение2; ...). Если номер_индекса равен 1 — вернёт значение1, если 2 — значение2 и т.д.
3. СМЕЩ (OFFSET) и ПРОСМОТР (LOOKUP) — для сложных динамических диапазонов и нестандартного поиска. Мощно, но требует аккуратного обращения.
4. УНИК (UNIQUE), СОРТИРОВКА (SORT), ФИЛЬТР (FILTER) — новые динамические функции! Это просто революция. Они возвращают не одно значение, а целый массив, который автоматически обновляется. =УНИК(A2:A100) — вернёт список уникальных значений из диапазона. =СОРТИРОВКА(A2:B100; 2; -1) — отсортирует диапазон по второму столбцу по убыванию. Выучи их, если работаешь в современном Excel или Google Sheets.
Финальный лайфхак: F9 для отладки
Выдели часть своей длинной формулы в строке формул и нажми F9. Excel покажет результат вычисления именно этого куска. Нажал Esc — всё вернётся обратно. Это самый быстрый способ понять, где в сложной формуле закралась ошибка.
Запомни: не нужно зубрить всё. Начни с СУММЕСЛИМН, ВПР/ИНДЕКСПОИСК и ЕСЛИ. Остальному научишься по мере необходимости, когда возникнет конкретная задача.
Excel — это инструмент, и главное — понимать, какой «ключ» подойдет к твоему «замку». Удачи в анализе! Пусть твои отчёты будут быстрыми, а выводы — точными.
Ну а если вообще ничего непонятно, но очень хочется научиться пользоваться формулами в Excel и автоматизировать по максиму свою работу, то всегда можно пройти онлайн-обучение.
Курсы по Ecxel краткосрочные, стоят немного, учиться можно вечерами или в выходные. 🚀 В этой статье я как раз о них рассказывал.
Результатом будет то, что вы освободите для себя очень существенную часть своего рабочего времени. Делать работу за вас уже будет Excel!
Было полезно? Буду признателен за лайк и подписку на свой канал!