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

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

Для владельцев определенных подписок M365 доступна генеративная нейросеть – ИИ-помощник Copilot. К теме искусственного интеллекта мы вернемся ближе к концу статьи, а начнем с детального разбора базовых формул и функций.

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

• Что такое формула в 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».

Статья является переводом. Автор оригинала: Shimon Brathwaite

Автор текста:ZheleznyChel

Написано при поддержке Timeweb Cloud↩

Больше интересных статей и новостей в нашем блоге на Хабре и телеграм-канале.

📚 Вам может быть интересно:

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

• Хватит кормить Chrome! Как я подружил браузер с ноутбуком 2007 года

• 45 лет под напряжением: как я спасал от электролита карманного «PACKRI MONSTER»

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

Предварительные требования

Во всех примерах статьи используется пакет spire.xls, который позволяет читать, записывать, форматировать и пакетно обрабатывать файлы Excel без установки Microsoft Excel.

Установите библиотеку командой:

pip install spire.xls.free

Подход 1. Хранение данных Excel в виде двумерного списка

Как это работает

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

Полный пример

from spire.xls import Workbook

# Загружаем книгу и рабочий лист

workbook = Workbook()

workbook.LoadFromFile("SalesReport.xlsx")

sheet = workbook.Worksheets[0]

# Получаем используемый диапазон

cell_range = sheet.AllocatedRange

# Сохраняем все данные в двумерный список

excel_data = []

for row_idx in range(cell_range.RowCount):

single_row = []

for col_idx in range(cell_range.ColumnCount):

# В Spire.XLS используются индексы, начинающиеся с 1

single_row.append(

cell_range[row_idx + 1, col_idx + 1].Value

)

excel_data.append(single_row)

# Освобождаем ресурсы

workbook.Dispose()

Преимущества и недостатки

Преимущества

• Максимально простая реализация

• Полностью сохраняет исходную структуру строк и столбцов

• Не требует дополнительного преобразования данных

Недостатки

Доступ к данным осуществляется только через числовые индексы:

excel_data[row][col]

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

Подходит для

• Быстрых прототипов

• Одноразовых скриптов

• Матричных вычислений

• Временного анализа данных

Для производственных приложений такая структура обычно не является оптимальным решением.

Подход 2. Преобразование строк в словари

Как это работает

Чтобы избавиться от жёстко заданных индексов столбцов, можно использовать первую строку как заголовки столбцов и преобразовать каждую последующую строку в словарь.

Вместо доступа к данным по позиции мы получаем доступ по имени поля. Это устраняет зависимость от порядка столбцов и значительно улучшает читаемость кода.

Полный пример

from spire.xls import Workbook

workbook = Workbook()

workbook.LoadFromFile("SalesReport.xlsx")

sheet = workbook.Worksheets[0]

cell_range = sheet.AllocatedRange

# Извлекаем заголовки из первой строки

rows = list(cell_range.Rows)

headers = [

cell_range[1, col_idx + 1].Value

for col_idx in range(cell_range.ColumnCount)

]

# Формируем список словарей

data_list = []

for row in rows[1:]: # Пропускаем строку заголовков

row_dict = {}

for idx, cell in enumerate(row.Cells):

row_dict[headers[idx]] = cell.Value

data_list.append(row_dict)

workbook.Dispose()

Преимущества и недостатки

Преимущества

Теперь данные можно получать по понятным именам полей:

data_list[0]["Sales"]

Преимущества такого подхода:

• Более высокая читаемость кода

• Независимость от порядка столбцов

• Простая сериализация в JSON

• Удобная интеграция с API и конвейерами обработки данных

• Хорошая совместимость с Pandas

Недостатки

Структуры на основе словарей по-прежнему являются слабо типизированными. Перед использованием данных может потребоваться дополнительная проверка и преобразование типов.

Подходит для

• Импорта и экспорта данных

• Очистки и подготовки данных

• Генерации полезной нагрузки для API

• Бизнес-отчётности

• Универсальной обработки Excel

Для большинства приложений это оптимальный баланс между простотой и удобством сопровождения.

Подход 3. Сопоставление строк с пользовательскими бизнес-объектами

Как это работает

При работе с фиксированными схемами данных и более сложной бизнес-логикой словари могут оказаться недостаточно удобными. Они не обеспечивают типобезопасность, поддержку IntelliSense и не предоставляют естественного места для размещения бизнес-правил.

Более надёжный подход заключается в создании класса бизнес-сущности и преобразовании каждой строки Excel в экземпляр этого класса.

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

Полный пример

# Определение бизнес-сущности

class Employee:

def __init__(self, name: str, age: int | None, department: str):

self.name = name

self.age = age

self.department = department

def is_adult(self) -> bool:

"""Возвращает True, если сотрудник является совершеннолетним."""

return self.age >= 18 if self.age else False

from spire.xls import Workbook

workbook = Workbook()

workbook.LoadFromFile("EmployeeData.xlsx")

sheet = workbook.Worksheets[0]

cell_range = sheet.AllocatedRange

employee_list = []

# Пропускаем строку заголовков

for row in list(cell_range.Rows)[1:]:

name = row.Cells[0].Value

age = (

int(row.Cells[1].Value)

if row.Cells[1].Value

else None

)

department = row.Cells[2].Value

employee = Employee(

name,

age,

department

)

employee_list.append(employee)

workbook.Dispose()

Преимущества и сценарии использования

Преимущества

• Строгая типизация благодаря явному преобразованию типов

• Более качественная проверка данных

• Автодополнение и подсказки IDE

• Инкапсуляция бизнес-логики

• Улучшенная поддерживаемость кода

• Более чистый объектно-ориентированный дизайн

Например:

employee.is_adult()

Бизнес-правила могут быть реализованы непосредственно внутри сущности, а не распределены по всему приложению.

Подходит для

• Корпоративных приложений

• Стабильных и хорошо определённых схем данных

• Систем со сложными бизнес-правилами

• Долгосрочных проектов, требующих удобной поддержки

Как выбрать подходящую структуру

Оптимальный выбор зависит от сложности приложения и способа использования данных.

Заключение

Переход от:

• Числовых индексов в двумерных списках

• Семантического доступа через словари

• Строго типизированных бизнес-объектов

отражает более широкий переход от ориентированного на данные программирования к моделированию, ориентированному на бизнес-логику.

Простые скрипты не требуют сложных абстракций. Для большинства реальных задач обработки Excel список словарей обеспечивает отличный баланс между гибкостью и удобством сопровождения. Если же приложение содержит сложные бизнес-правила и использует стабильные схемы данных, пользовательские объекты-сущности становятся наиболее надёжным долгосрочным решением.

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