Формулы и функции в Excel: подробное руководство для начинающих

Формулы Microsoft Excel позволяют мгновенно и без ошибок производить сложнейшие расчеты. Их ценность возрастает в разы, когда приходится работать с огромными массивами данных. Стоит запустить правильный алгоритм, и Excel за считаные секунды перелопатит тонны информации, выдав готовый результат.

В этой статье мы разберем пять ключевых типов формул и функций, которые заложат прочный фундамент для вашей аналитической работы. Попутно мы покажем несколько простых способов их ввода в таблицу.

О чем расскажем:

• Что такое формула в Excel?

• Что такое функция?

• Базовые матоперации и функции

• Логическая функция ЕСЛИ

• Функции СУММЕСЛИ и СЧЁТЕСЛИ

• Функция ССЫЛКА/СЦЕПИТЬ

• Функция ВПР

• Как создавать формулы с помощью Copilot

❯ Что такое формула в Excel?

По сути, формула – математическое или логическое выражение, производящее операции над содержимым ячеек. С их помощью можно делать расчеты любой сложности: от банального сложения чисел в столбце до расчета эксцесса (показателя островершинности распределения) в сложных статистических выборках. Формулы незаменимы, когда нужно превратить цифры в наглядные бизнес-показатели, на основе которых принимаются важные решения.

❯ Что такое функция?

Функция – своего рода «заготовленная» формула, встроенная в сам Excel. Это готовый шаблон, облегчающий проведение расчетов. Всего в программе насчитывается около 500 встроенных функций, и этот арсенал ежегодно пополняется. К счастью, для решения 90% повседневных задач за глаза хватит всего десятка самых популярных инструментов.

1. Базовые математические операции и функции

Запомните золотое правило: любая формула в Excel всегда начинается со знака равенства (=).

Сложение, вычитание, умножение и деление

Допустим, нам нужно сложить значения двух ячеек. Кликните по пустой ячейке, где должен появиться результат, и введите знак =, дав программе понять, что далее последует формула.

Теперь щелкните по ячейке с первым слагаемым. Ее адрес (например, A2) автоматически отобразится в строке ввода сразу после знака равенства.

Введите знак плюс (+), а затем кликните по ячейке со вторым числом – ее адрес (например, A3) встанет на свое место в формуле. В итоге выражение для сложения двух ячеек примет классический вид:

=A2+A3

Обратите внимание: формула дублируется в строке формул, расположенной прямо над листом таблицы. После ввода знака = вы можете продолжать писать выражение непосредственно там. Зачастую редактировать длинные формулы в этой специальной строке удобнее, чем тесниться внутри самой ячейки.

Если нужно приплюсовать еще несколько чисел, продолжайте ставить знак плюс и кликать по нужным ячейкам. Закончив ввод, нажмите клавишу Enter – и вуаля, на месте формулы появится готовая сумма.

Вычитание, умножение и деление строятся точно так же. Достаточно сменить математический оператор: вместо плюса поставить дефис (–) для вычитания, звездочку (*) для умножения или косую черту (/) для деления.

Быстрое сложение с помощью функции СУММ (SUM)

Если чисел много, складывать их поодиночке утомительно. Куда практичнее использовать встроенную функцию СУММ (в англоязычной версии – SUM).

В русской версии Excel разделителем аргументов функций служит точка с запятой (;), а в английской – запятая (,). Это важно учитывать, чтобы избежать ошибок при вводе.

Для начала выберите ячейку для вывода результата. Наберите =СУММ (или =SUM). Excel тут же предложит подходящие функции из выпадающего списка. Дважды щелкните по строке СУММ, чтобы открыть круглую скобку.

Под ячейкой появится всплывающая подсказка с синтаксисом:

=СУММ(число1; [число2]; …)

Чтобы сложить разрозненные ячейки, просто кликайте по ним, разделяя адреса точкой с запятой. Также адреса можно ввести вручную с клавиатуры.

Если нужно суммировать целый диапазон (непрерывный столбец или строку), выберите первую ячейку группы, зажмите клавишу Shift и кликните по последней. Еще вы можете вписать этот диапазон вручную через двоеточие – выражение A2:A7 охватит ячейки A2, A7 и всё, что между ними.

Убедившись, что все нужные ячейки выделены, нажмите Enter. Готово! На экране появится итоговая сумма. Если снова щелкнуть по этой ячейке, в строке формул отразится полное выражение. В нашем примере это:

=СУММ(A2:A7)

Важное свойство формул в Excel – динамичность (или относительность). Если изменить значение в любой из ячеек исходного диапазона, итоговая сумма автоматически пересчитается.

Если же вам нужно «зафиксировать» полученное число, превратив его в статичное значение (константу, которая не изменится при редактировании исходных строк), скопируйте ячейку (клик правой кнопкой мыши → «Копировать»). Затем снова щелкните по ней правой кнопкой мыши и в разделе «Параметры вставки» выберите пункт «Значения» (иконка с планшетом и цифрами 123).

Теперь при выборе этой ячейки в строке формул будет отображаться обычное статичное число, а не алгоритм расчета.

Лайфхак: в некоторых случаях процесс можно ускорить благодаря функции «Автосумма». Если у вас есть непрерывный ряд чисел в строке или столбце, поставьте курсор в соседнюю пустую ячейку (справа от строки или снизу под столбцом) и нажмите кнопку Автосумма (со знаком греческой буквы сигма Σ), которая находится на вкладке «Главная» в правой части ленты. Программа сама определит диапазон данных, и вам останется лишь подтвердить расчет нажатием Enter.

Расчет среднего значения с помощью функции СРЗНАЧ (AVERAGE)

Чтобы вычислить среднее арифметическое, проделайте те же шаги, но введите функцию =СРЗНАЧ (=AVERAGE), затем выделите нужные ячейки с числами.

Лайфхак: для вычисления среднего значения тоже есть быстрый ярлык. Поставьте курсор справа от заполненной строки или под столбцом с числами. Нажмите на стрелку рядом с «Автосуммой» и выберите в выпадающем меню пункт Среднее, после чего подтвердите выбор клавишей Enter. Excel рассчитает среднее арифметическое.

2. Логическая функция ЕСЛИ (IF)

Этот инструмент позволяет автоматизировать принятие решений прямо внутри таблицы по принципу «если условие выполняется, то переходим к действию А, иначе – к действию Б». Программа проверяет заданное логическое условие и выводит результат в зависимости от исхода проверки. К примеру, вы можете настроить проверку возраста: если в ячейке указано число 18 или больше, вернуть «Да», если меньше – «Нет».

Для разнообразия опробуем еще один способ ввода формул – через вкладку «Формулы». Там все встроенные функции разложены по полочкам: автосумма, финансовые, логические, текстовые, даты и времени и т. д. Классификация очень выручит, когда не помнишь точное название инструмента или сомневаешься в правильности написания.

Чтобы воспользоваться нужной командой, выделите пустую ячейку, откройте вкладку Формулы, нажмите Логические и выберите в списке пункт ЕСЛИ (IF).

Другой вариант – нажать Вставить функцию (fx), расположенную в левом углу той же панели. В открывшемся окне отобразится список часто используемых инструментов.

Выберите в списке пункт ЕСЛИ и нажмите OK. Если искомой функции нет в перечне часто используемых, смените категорию на «Полный алфавитный перечень» – там вы гарантированно найдете всё, что скрыто в недрах программы.

На экране откроется окно «Аргументы функции», а в выбранной ячейке появится заготовка =ЕСЛИ().

Синтаксис ЕСЛИ следующий:

=ЕСЛИ(логическое_выражение; значение_если_истина; значение_если_ложь)

В окне аргументов увидите три поля с идентичными названиями. В нашем примере с совершеннолетием логический тест проверит, больше или равно ли число в ячейке B2 числу 18. Если да, программа выдаст «Да», если нет – «Нет». Прямо в поля формы впишите соответствующие значения:

• Логическое_выражение: B2>=18

• Значение_если_истина: «Да»

• Значение_если_ложь: «Нет»

Можно обойтись без графической формы и просто прописать формулу в ячейке:

=ЕСЛИ(B2>=18; «Да»; «Нет»)

Лайфхак: вам не придется заново прописывать формулу для каждой строки вручную. Просто зажмите левой кнопкой мыши маркер автозаполнения (черный квадрат в правом нижнем углу ячейки) и потяните его вниз по столбцу. Excel автоматически скопирует алгоритм во все строки пониже, подстроив ссылки на ячейки под нужные диапазоны. То есть формула, ссылавшаяся на B2, при перетаскивании ниже станет автоматически считывать данные из B3.

3. Выборка по условию: функции СУММЕСЛИ (SUMIF) и СЧЁТЕСЛИ (COUNTIF)

СУММЕСЛИ – продвинутая версия функции сложения. Она позволяет суммировать не всё подряд, а лишь те значения из диапазона, которые соответствуют критерию. Для настройки вам нужно указать целевой диапазон ячеек для проверки, собственно критерий отбора и, опционально, диапазон суммирования (если складывать нужно значения из других ячеек, а не из тех, которые проверяли).

Формула имеет следующий синтаксис:

=СУММЕСЛИ(диапазон; условие; [диапазон_суммирования])

Важное правило: любое текстовое условие, а также любые математические или логические символы здесь заключаются в двойные кавычки.

Представим реальную ситуацию: перед вами таблица продаж и нужно быстро сложить сумму сделок, превышающих 100 долларов. Область проверки – диапазон от C2 до C9, а условие – «>100». Поскольку мы складываем числа из того же самого проверяемого столбца, указывать отдельный диапазон суммирования не нужно. Формула получится простой:

=СУММЕСЛИ(C2:C9; «>100»)

А если задача сложнее? Допустим, нам необходима сумма заказов только по региону «East» (восточный). В данном случае диапазон проверки (столбец с регионами B2:B9) и диапазон суммирования (столбец с продажами C2:C9) различаются. Поэтому в аргументах пишем обе эти области:

=СУММЕСЛИ(B2:B9; B2; C2:C9)

Удобно то, что необязательно вбивать само слово «East». Достаточно сослаться на ячейку B2, и программа считает и применит содержащийся в ней текст.

По схожему принципу работает СЧЁТЕСЛИ (COUNTIF). Она подсчитывает количество ячеек, которые удовлетворяют заданному критерию. Ее синтаксис лаконичен:

=СЧЁТЕСЛИ(диапазон; условие)

Если хотим подсчитать количество сделок, совершенных в западном регионе («West»), мы задаем область поиска (B2:B9) и критерий (значение в ячейке B3). В итоге получится выражение:

=СЧЁТЕСЛИ(B2:B9; B3)

Если перед вами стоит еще более хитрая задача – например, подсчитать общие продажи книг именно в восточном регионе или узнать количество чеков выше 100 долларов исключительно на западе, – Excel предложит тяжелую артиллерию: функции СУММЕСЛИМН (SUMIFS) и СЧЁТЕСЛИМН (COUNTIFS).

4. Склеивание строк: функция СЦЕПИТЬ (CONCAT)

Иногда данные хранятся по кусочкам в разных ячейках, но их нужно объединить в одну строку. Популярный пример – соединить разнесенные по разным столбцам имя и фамилию. Инструмент также отлично решает задачи по сборке почтовых адресов, склейке номеров артикулов, генерации путей к файлам на диске или формированию URL-ссылок. Базовая структура выглядит так:

=СЦЕПИТЬ(текст1; текст2; текст3; …)

Попробуем собрать воедино имя и фамилию из соседних ячеек, не забыв добавить разделительный пробел между ними. Поставим курсор в пустую ячейку C2, введем обрывочное =СЦЕ и кликнем на предложенную функцию СЦЕПИТЬ (CONCAT). Сначала выберем ячейку с именем (A2), затем поставим разделитель, введем пустой пробел в кавычках (» «), снова поставим разделитель и укажем ячейку с фамилией (B2). Завершаем ввод клавишей Enter. Выражение примет следующий вид:

=СЦЕПИТЬ(A2; » «; B2)

Осталось потянуть маркер автозаполнения вниз от ячейки C2, чтобы моментально склеить имена для всех остальных сотрудников в таблице.

5. Легендарная функция ВПР (VLOOKUP)

Это безоговорочный фаворит аналитиков и одна из самых популярных функций в Excel. ВПР (VLOOKUP) расшифровывается как «вертикальный просмотр». Она ищет заданное значение в крайнем левом столбце диапазона и выводит данные из соседней ячейки в той же строке. Полезно, когда вы хотите сопоставить две разные таблицы, вытащить недостающие реквизиты или сверить два независимых списка.

Чтобы запустить механизм, понадобятся три обязательных аргумента и один опциональный:

1. Искомое_значение (строка или число, которое пытаемся найти);

2. Таблица (целевой диапазон ячеек справочника, где осуществляется поиск);

3. Номер_столбца (порядковый номер колонки в таблице справочника, откуда нужно забрать искомое значение);

4. Интервальный_просмотр (необязательный аргумент выбора точности поиска: ИСТИНА или ЛОЖЬ). Вариант «ЛОЖЬ» означает, что нужен только стопроцентно точный результат. Выбор «ИСТИНА» разрешает искать близкое приближенное значение. Если опустить параметр, Excel применит поиск по умолчанию – «ИСТИНА».

В готовом виде формула выглядит так:

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

Держите в уме критически важное условие: искомое значение обязано находиться исключительно в самом первом (крайнем левом) столбце выделяемой таблицы справочника. Этот столбец считается первым (индекс 1), следующий за ним вправо – вторым (индекс 2) и так далее.

Рассмотрим пример: нужно узнать, к какому региону прикреплен сотрудник.

1. Сначала укажем объект поиска – имя «Mike» (ячейка A2).

2. Далее выделим диапазон ячеек со справочником (наша целевая таблица поиска) – F2:G8.

3. Затем укажем, из какого столбца забирать результат. Считаем колонки слева направо в границах нашего справочника F2:G8. Название региона находится во второй колонке, значит, пишем цифру 2.

4. И наконец, определимся с точностью совпадения. Режим «ИСТИНА» обычно используют для числовых интервалов. Нам же необходимо железно найти конкретного человека, поэтому выбираем ЛОЖЬ (чтобы гарантировать точное совпадение). Большинство опытных пользователей по умолчанию ставят в конце формулы именно «ЛОЖЬ» (или 0).

Итоговый вид формулы:

=ВПР(A2; F2:G8; 2; ЛОЖЬ)

Конечно, на крохотной демонстрационной таблице преимущество неочевидно. Но когда перед вами база на десятки тысяч позиций, ВПР сэкономит часы монотонного труда и застрахует от опечаток.

❯ Пишем формулы без усилий: ИИ-помощник Copilot

Если вы счастливый обладатель доступа к Copilot в Excel, можно делегировать составление формул искусственному интеллекту. Вам нужно лишь описать задачу человеческим языком, а нейросеть спроектирует нужную синтаксическую конструкцию. Сейчас разберем два наглядных примера.

Чтобы активировать ИИ, нажмите на значок Copilot на панели инструментов или в правом нижнем углу экрана.

Справа откроется боковая панель ИИ-чата. Сюда мы и будем отправлять наши пожелания. Попробуем с простого: попросим ИИ просуммировать наши сделки и четко укажем ячейку для расположения формулы:

Создай в ячейке B7 формулу для подсчета суммы всех продаж

Нажмите кнопку отправки запроса. Помощник проанализирует контекст страницы, соберет нужную формулу и добавит ее в нужную ячейку.

Позовем ИИ на помощь в более запутанном кейсе, представленном на скриншоте ниже.

Отправим нейросети многоступенчатый запрос:

Создай в ячейке G2 формулу, рассчитывающую общую выручку за единицу товара с учетом скидки на каждую покупку. Затем протяни эту формулу вниз для всех остальных строк и сформируй сводную таблицу с суммой выручки по регионам (Region).

Отправляем промпт на обработку и наблюдаем за магией. Результат работы ИИ выглядит круто:

Лайфхак: работать с Copilot станет комфортнее, если вы предварительно отформатируете рабочий диапазон как умную таблицу. Так нейросеть будет четко видеть границы структурированных данных и задействует нужные диапазоны без промахов.

❯ Для дальнейшего изучения

Сегодня вы заглянули за занавес возможностей Excel, и это лишь вершина гигантского ледника. Как только освоитесь с базовыми алгоритмами, откроются сотни готовых инструментов программы и огромный набор вложенных функций разного уровня сложности. Идеальная отправная точка для углубленного изучения – справочник Microsoft «Общие сведения о формулах в Excel».

Может быть интересно:

• Transfer 2.0, или Как я перестал бояться и полюбил миграции облачных серверов

• Гранит-Навигатор 4.11. Оживляем самый крутой транспортный информатор

• Кинопоиск всё-таки отключил статистику по фильмам — я создал свою на основе Letterboxd и TMDB

Новости, обзоры продуктов и конкурсы от команды Timeweb.Cloud — в нашем Telegram-канале ↩