Формула в Excel: как завязать и использовать — полное руководство

Краткая памятка по работе с формулами в Excel

1. Любая формула начинается со знака равенства (=).
2. Используйте относительные ссылки (A1) для копирования с изменением.
3. Используйте абсолютные ссылки ($A$1) для фиксации ячейки.
4. Для суммирования применяйте функцию СУММ.
5. Для проверки условия используйте функцию ЕСЛИ.
6. Избегайте деления на ноль, чтобы не получить ошибку #ДЕЛ/0!.
7. Для поиска данных применяйте функцию ВПР.
8. Копируйте формулы с помощью маркера автозаполнения.
9. Для копирования только результата используйте «Специальную вставку».
10. Проверяйте порядок операций с помощью круглых скобок.
11. Используйте клавишу F2 для просмотра и редактирования формулы.
12. При ошибках проверяйте типы данных в ячейках.

Что такое формулы и функции в Excel

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

Для маркетолога, финансиста, руководителя или другого специалиста Excel решает три главные задачи:

1. Учет и структурирование данных. Вносите ли вы список клиентов, бюджет на рекламу или результаты A/B-тестов — Excel помогает организовать информацию в удобном виде: строки, столбцы, фильтры, сортировки.
2. Автоматизация расчетов. Вместо того чтобы каждый раз складывать цифры на калькуляторе или пересчитывать проценты вручную, вы один раз прописываете формулы, и таблица считает сама. Поменялись исходные данные — результат обновился автоматически.
3. Анализ данных и принятие решений. На основе собранных данных можно строить сводные таблицы, графики, вычислять ключевые метрики: ROI, CAC, LTV, конверсию, средний чек. Это помогает видеть, что работает, а что нет, и перераспределять бюджеты в пользу эффективных каналов.

Всё это становится доступным благодаря двум китам Excel — формулам и функциям. Они превращают набор цифр в полезные выводы: сколько потратили за месяц, какой канал приносит больше лидов, на сколько выросли продажи.

Чем формула отличается от функции

=B2+B3+B4+B5+B6+B7+B8
Простой пример: вам нужно сложить расходы на рекламу за неделю. Вместо того чтобы считать на калькуляторе, вы пишете в ячейке формулу и сразу получаете сумму. Если поменялось число в одной из ячеек — результат пересчитается автоматически.

=B2+B3+B4+B5+B6+B7+B8 =СУММ(B2:B8)
Функция — это встроенный в Excel готовый блок для часто встречающихся расчетов. Вместо того чтобы писать длинное выражение вручную, вы вызываете функцию и задаете ей параметры. Например, вместо можно написать. Это также суммирует значения в нужных ячейках, но для вас будет быстрее, удобнее и исключает ошибки.

В Excel сотни функций на все случаи жизни: математические, статистические, логические, текстовые, финансовые и другие.

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

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

Кому нужны формулы и где применяются

Таблица №1

Excel с формулами — это инструмент, который используют в любой компании, где есть цифры
Руководитель отдела продаж	Анализирует работу менеджеров: кто сколько закрыл сделок, какой средний чек, какова конверсия из встречи в оплату. С помощью формул можно мгновенно подсчитать бонусы каждому сотруднику по итогам месяца.
Аналитик	Строит прогнозы на основе исторических данных, считает сезонные коэффициенты, выявляет аномалии, готовит дашборды для руководства. Без формул это превращается в бесконечную возню с калькулятором.
Логист	Рассчитывает стоимость доставки по разным маршрутам, оптимизирует загрузку транспорта, считает километраж и расход топлива. Одна правильно настроенная таблица экономит часы работы каждую неделю.
HR-менеджер	Ведет учет персонала, график отпусков, табель учета рабочего времени, принимает больничные листы, отслеживает текучесть кадров. Формулы помогают автоматически подсчитать стаж сотрудников и количество неиспользованных дней отпуска.
Рядовой сотрудник	Планирует личный бюджет, считает расходы на проекты, ведет списки задач с приоритетами, отслеживает дедлайны. Даже простые суммы и средние значения экономят время и нервы.

Главное, что объединяет всех этих людей: Excel с формулами и функциями становится инструментом, который считает сам. Но чтобы программа начала приносить реальную пользу, мало знать, кому и зачем она нужна. Стоит понять, как Excel устроен, чтобы любой расчет стал простым инструментом.

Из чего состоит формула в Excel

Любая формула в Excel — это набор элементов, которые вместе говорят программе, что именно нужно посчитать и каким способом. Разберем основные элементы — строительные блоки любой формулы.

Знак равенства и строка формул

Что делает. Функция И проверяет, выполняются ли все условия одновременно. Если да — возвращает ИСТИНА, если хотя бы одно не выполнено — ЛОЖЬ. Чаще всего используется внутри функции ЕСЛИ.

Как проверить, что клиент подходит под все условия сегмента — возраст от 25 до 35 лет и доход выше среднего: =ЕСЛИ(И(B2>=25; B2<=35; C2=»выше среднего«); «ЦА»; «Не ЦА»)

Ссылки на ячейки и диапазоны

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

• Ссылка на одну ячейку: A3, C5, F10.
• Ссылка на диапазон (прямоугольную область): A1:A10 (все ячейки с A1 по A10), B2:E5 (прямоугольник от B2 до E5).

A1:A10 B2:E5
Ссылка на диапазон (прямоугольную область): (все ячейки с A1 по A10), (прямоугольник от B2 до E5).

1. Относительными, которые меняются при копировании формулы в другую ячейку.
2. Абсолютными остаются неизменными, обозначаются знаком $, например $A$1.
3. Смешанными, например, $A1 или A$1.

Операторы

Операторы — это знаки действий, которые нужно выполнить. Это необходимые знаки для работы любой Excel-формулы. В Excel есть несколько типов операторов — разбираем их в таблице.

Таблица №2

Тип операторов и назначение	Знаки и действия	Пример
Арифметические проводят вычисления	+ сложение — вычитание * умножение / деление ^ возведение в степень % процент	=A1*1,2 — умножить значение из ячейки A1 на 1,2.
Операторы сравнения возвращают логическое значение ИСТИНА или ЛОЖЬ	= равно > больше < меньше >= больше или равно <= меньше или равно <> не равно	=B2>100 — проверить, больше ли значение в ячейке B2 100
Оператор объединения текста соединяет несколько текстовых строк в одну	& амперсанд	=»Продажи за » & A1 & «: » & B1 — склеит текст с содержимым ячеек
Операторы ссылок	: двоеточие для задания диапазона ячеек ; точка с запятой разделяет аргументы (значения в формуле) и также объединяет несколько диапазонов в один	A1:A10 — двоеточие задает диапазон ячеек с A1 до A10 =СУММ(A1:A5; C1:C5) — формула суммирует значение в двух диапазонах с ячейки A1 по ячейку A5 и с ячейки С1 по C5

Константы и аргументы

=A1*0,15
Константы — это значения, которые вводятся напрямую в формулу и не меняются. Это могут быть числа, текст или логические значения. Например, в формуле число 0,15 — константа.

=СУММ(A1:A10; 100; B5)
Аргументы — это данные, которые формула использует для вычислений. В роли аргументов выступают ссылки на ячейки, диапазоны, числа, текст, логические значения или другие функции. Они заключаются в скобки и разделяются точкой с запятой. Например, в функции аргументы — диапазон A1:A10, число 100 и ячейка B5.

Порядок выполнения операций

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

1. Операции в скобках ().
2. Возведение в степень ^.
3. Умножение и деление * /.
4. Сложение и вычитание + -.
5. Операторы сравнения (=, &lt;, &gt; и т. д.).

Этот порядок нужно учитывать, чтобы получать правильные результаты. Если вы хотите изменить приоритет, используйте скобки. Например, формула =2+3*4 даст 14 (сначала умножение), а =(2+3)*4 даст 20 (сначала сложение в скобках).

От понимания состава и порядка выполнения операций зависит, как у вас будет получаться строить расчеты. В следующем блоке перейдем к практике и разберем, как вставить формулу в таблице Excel.

Элементы формулы

4. Операторы. Оператор ^ («крышка») применяется для возведения числа в степень, а оператор * («звездочка») — для умножения.

Использование операторов в формулах

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

Типы операторов

Приложение Microsoft Excel поддерживает четыре типа операторов: арифметические, текстовые, операторы сравнения и операторы ссылок.

Арифметические операторы

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

Операторы сравнения

Операторы сравнения используются для сравнения двух значений. Результатом сравнения является логическое значение: ИСТИНА либо ЛОЖЬ.

Текстовый оператор конкатенации

Амперсанд (&) используется для объединения (соединения) одной или нескольких текстовых строк в одну.

Соединение или объединение последовательностей знаков в одну последовательность

Операторы ссылок

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

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

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

Порядок выполнения Excel в Интернете формулах

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

Порядок вычислений

Формулы вычисляют значения в определенном порядке. Формула всегда начинается со знака равно(=).Excel в Интернете интерпретирует знаки после знака равно как формулу. После знака равно вычисляются элементы (операнды), такие как константы или ссылки на ячейки. Они разделены операторами вычислений. Excel в Интернете вычисляет формулу слева направо в соответствии с определенным порядком для каждого оператора в формуле.

Использование круглых скобок

Чтобы изменить порядок вычисления формулы, заключите ее часть, которая должна быть выполнена первой, в скобки. Например, следующая формула дает результат 11, так как Excel в Интернете умножение выполняется перед с добавлением. В этой формуле число 2 умножается на 3, а затем к результату прибавляется число 5.

Если же изменить синтаксис с помощью скобок, Excel в Интернете сбавляет 5 и 2, а затем умножает результат на 3, чтобы получить 21.

В следующем примере скобки, в которые заключена первая часть формулы, принудительно Excel в Интернете сначала вычислить ячейки B4+25, а затем разделить результат на сумму значений в ячейках D5, E5 и F5.

Использование ссылок в формулах

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

Стиль ссылок A1

Стиль ссылок по умолчанию По умолчанию в Excel в Интернете используется стиль ссылок A1, который ссылается на столбцы буквами (от A до XFD, всего 16 384 столбца) и ссылается на строки с числами (от 1 до 1 048 576). Эти буквы и номера называются заголовками строк и столбцов. Для ссылки на ячейку введите букву столбца, и затем — номер строки. Например, ссылка B2 указывает на ячейку, расположенную на пересечении столбца B и строки 2.

Ссылка на другой лист. В приведенном ниже примере функция СРЗНАЧ используется для расчета среднего значения диапазона B1:B10 на листе «Маркетинг» той же книги.

Стиль трехмерных ссылок

Что происходит при перемещении, копировании, вставке или удалении листов. Нижеследующие примеры поясняют, какие изменения происходят в трехмерных ссылках при перемещении, копировании, вставке и удалении листов, на которые такие ссылки указывают. В примерах используется формула =СУММ(Лист2:Лист6!A2:A5) для суммирования значений в ячейках с A2 по A5 на листах со второго по шестой.

Вставка или копирование Если вставить листы между листами 2 и 6, Excel в Интернете будет включать в расчет все значения из ячеек с A2 по A5 на добавленных листах.

Удалить Если удалить листы между листами 2 и 6, Excel в Интернете вы вычислите их значения.

Переместить Если переместить листы между листами 2 и 6 в место за пределами диапазона, на который имеется ссылка, Excel в Интернете удалит их значения из вычислений.

Перемещение конечного листа Если переместить лист 2 или 6 в другое место книги, Excel в Интернете скорректирует сумму с учетом изменения диапазона листов.

Удаление конечного листа Если удалить лист 2 или 6, Excel в Интернете скорректирует сумму с учетом изменения диапазона листов между ними.

Стиль ссылок R1C1

Можно использовать такой стиль ссылок, при котором нумеруются и строки, и столбцы. Стиль ссылок R1C1 удобен для вычисления положения столбцов и строк в макросах. В стиле R1C1 Excel в Интернете указывает на расположение ячейки с помощью R, за которым следует номер строки, и C, за которым следует номер столбца.

При записи макроса Excel в Интернете некоторые команды с помощью стиля ссылок R1C1. Например, если записать команду (например, нажать кнопку «Автоумма»), чтобы вставить формулу, в которую добавляется диапазон ячеек, Excel в Интернете записи формулы со ссылками с помощью стиля R1C1, а не A1.

Типы ссылок в формулах

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

Относительные

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

=A2*1,2 =A3*1,2 =A4*1,2
Например, в ячейке B2 можно указать формулу (добавить 20% наценки к цене в столбце А). Если затем скопировать эту формулу в ячейку B3 она автоматически превратится в, в B4 — в и так далее. Excel понял: умножать надо на значение из ячейки слева в той же строке.

Абсолютные

$A$1
Абсолютная ссылка жестко фиксирует и строку, и столбец. Она обозначается знаком доллара $ перед буквой столбца и номером строки:.

=A2*$B$1
Пример. В ячейке C2 вам нужно посчитать цену с НДС: цена без НДС в ячейке A2 умножается на ставку НДС, которая записана в ячейке B1. Формула в C2 должна выглядеть так:. При копировании вниз первая ссылка (A2) будет меняться (A3, A4…), а вторая ($B$1) останется неизменной, всегда указывая на ячейку со ставкой НДС.

Смешанные

Если нужно зафиксировать только столбец или строку, то нужно добавить знак доллара перед той частью, которую нельзя менять:

• $A1 — столбец А абсолютный, строка 1 относительная (при копировании по строкам номер строки будет меняться, столбец останется).
• A$1 — столбец А относительный, строка 1 абсолютная (при копировании по столбцам буква столбца будет меняться, строка останется).

$A1
— столбец А абсолютный, строка 1 относительная (при копировании по строкам номер строки будет меняться, столбец останется).

A$1
— столбец А относительный, строка 1 абсолютная (при копировании по столбцам буква столбца будет меняться, строка останется).

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

Ссылки на другие листы и книги

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

Ссылка на ячейку из другой книги (внешняя ссылка) выглядит сложнее, но создается так же просто — щелчком мыши по нужной ячейке в открытой книге. Синтаксис такой:

=’[Бюджет]Лист1’!$C$4
Например:. Обратите внимание — внешние ссылки по умолчанию вставляются как абсолютные.

Важно: если если файл-источник будет перемещен или переименован, связь нарушится.

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

Использование имен в формулах

Можно создавать определенные имена для представления ячеек, диапазонов ячеек, формул, констант и Excel в Интернете таблиц. Имя — это значимое краткое обозначение, поясняющее предназначение ссылки на ячейку, константы, формулы или таблицы, так как понять их суть с первого взгляда бывает непросто. Ниже приведены примеры имен и показано, как их использование упрощает понимание формул.

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

В отдельных случаях для пересчета формул следует воспользоваться специальным инструментом, но это рассмотрим в отдельном разделе посвященным вычислениям по формулам.

Использование функций и вложенных функций в формулах

Функции — это заранее определенные формулы, которые выполняют вычисления по заданным величинам, называемым аргументами, и в указанном порядке. Эти функции позволяют выполнять как простые, так и сложные вычисления.

Синтаксис функций

Приведенный ниже пример функции ОКРУГЛ, округляющей число в ячейке A10, демонстрирует синтаксис функции.

1. Структура. Структура функции начинается со знака равно (=), за которым следуют имя функции, открывая скобка, аргументы функции, разделенные запятой, и закрывая скобка.

2. Имя функции. Чтобы отобразить список доступных функций, щелкните любую ячейку и нажмите клавиши SHIFT+F3.

4. Всплывающая подсказка аргумента. При вводе функции появляется всплывающая подсказка с синтаксисом и аргументами. Например, всплывающая подсказка появляется после ввода выражения =ОКРУГЛ(. Всплывающие подсказки отображаются только для встроенных функций.

Ввод функций

Чтобы упростить создание и редактирование формул и свести к минимуму количество опечаток и синтаксических ошибок, пользуйтесь автозавершением формул. После того как вы введите знак » ocpSection» role=»region» aria-label=»Вложенные функции»>

Вложенные функции

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

Предельное количество уровней вложенности функций. В формулах можно использовать до семи уровней вложенных функций. Если функция Б является аргументом функции А, функция Б находится на втором уровне вложенности. Например, в приведенном выше примере функции СРЗНАЧ и СУММ являются функциями второго уровня, поскольку обе они являются аргументами функции ЕСЛИ. Функция, вложенная в качестве аргумента в функцию СРЗНАЧ, будет функцией третьего уровня, и т. д.

Виды формул: простые, сложные, комбинированные

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

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

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

Здесь сначала считается разность в скобках, потом умножается на D2, затем делится на 100.

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

Здесь функция СУММ вложена в функцию ЕСЛИ: сначала считается сумма, потом проверяется условие.

Вложенность может быть глубокой (ЕСЛИ внутри ЕСЛИ, несколько функций подряд). Excel допускает до 64 вложенных уровней, но на практике больше 3–4 уже тяжело читать. Для сложных случаев лучше использовать вспомогательные столбцы или функции типа ЕСЛИМН.

Создание формулы, ссылающейся на значения в других ячейках

Нажмите клавишу ВВОД. В ячейке с формулой отобразится результат вычисления.

Как создать формулу в таблице Excel — пошаговая инструкция

Есть несколько способов, как можно сделать формулу в Excel— от простого набора с клавиатуры до использования встроенных помощников. Каждый из них удобен в своей ситуации.

Ввод вручную

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

1. Кликните на ячейку, где должен появиться результат.
2. Нажмите клавишу = (знак равенства). Excel перейдет в режим ввода формулы.
3. Начните вводить формулу, используя числа, ссылки на ячейки, операторы и функции. Например: =A1*B1.
4. Для указания ссылки на ячейку можно не писать ее адрес вручную, а просто кликнуть по нужной ячейке мышкой — ее адрес автоматически подставится в формулу.
5. После завершения ввода нажмите Enter. В ячейке отобразится результат, а формула останется в строке формул.

Подходит для простых вычислений и случаев, когда вы уверены в синтаксисе.

Через строку формул

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

1. Выделите ячейку, в которой будет формула.
2. Поставьте курсор в строку формул (просто кликните по ней мышкой) и начните ввод со знака =.
3. После завершения нажмите Enter или зеленую галочку слева от строки формул, чтобы подтвердить ввод. Красный крестик отменяет редактирование.

=
Поставьте курсор в строку формул (просто кликните по ней мышкой) и начните ввод со знака.

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

Мастер функций (fx)

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

1. Выделите ячейку для результата.
2. Нажмите кнопку «Вставить функцию» (кнопка со значком fx) слева от строки формул.
3. Откроется диалоговое окно «Мастер функций». Вы можете выбрать категорию (например, «Статистические», «Логические», «Математические») или найти функцию по поиску, введя ее назначение (например, «сумма»).
4. Выберите нужную функцию из списка и нажмите «ОК».
5. Появится окно «Аргументы функции». В поля ввода нужно подставить значения или ссылки на ячейки. Excel показывает описание каждого аргумента и предварительный результат.
6. После заполнения нажмите «ОК» — формула вставится в ячейку.

Нажмите кнопку «Вставить функцию» (кнопка со значком fx) слева от строки формул.

Откроется диалоговое окно «Мастер функций». Вы можете выбрать категорию (например, «Статистические», «Логические», «Математические») или найти функцию по поиску, введя ее назначение (например, «сумма»).

Появится окно «Аргументы функции». В поля ввода нужно подставить значения или ссылки на ячейки. Excel показывает описание каждого аргумента и предварительный результат.

Автосумма

Самый частый расчет в бизнес-таблицах — это сумма чисел. Для этого случая у Excel есть специальная кнопка «Автосумма» на вкладке «Главная» в группе «Редактирование».

1. Выделите ячейку, в которую нужно вставить сумму. Обычно это ячейка сразу под или справа от суммируемого диапазона.
2. Нажмите кнопку «Автосумма». Excel автоматически проанализирует соседние ячейки и предложит диапазон для суммирования, выделив его пунктирной рамкой.
3. Если диапазон предложен верно, просто нажмите Enter. Если нет — выделите нужный диапазон мышью и затем нажмите Enter.

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

Нажмите кнопку «Автосумма». Excel автоматически проанализирует соседние ячейки и предложит диапазон для суммирования, выделив его пунктирной рамкой.

Если диапазон предложен верно, просто нажмите Enter. Если нет — выделите нужный диапазон мышью и затем нажмите Enter.

Кстати, «Автосумма» умеет не только суммировать. Если нажать на стрелочку рядом с кнопкой, откроется меню с другими популярными функциями: среднее значение, количество чисел, максимум, минимум.

Заполнение листов Excel формулами

Для выполнения вычислений и расчетов следует записать формулу в ячейку Excel. В таблице из предыдущего урока (которая отображена ниже на картинке) необходимо посчитать суму, надлежащую к выплате учитывая 12% премиальных к ежемесячному окладу. Как в Excel вводить формулы в ячейки для проведения подобных расчетов?

Задание 1. В ячейке F2 введите следующую формулу следующим образом: =D2+D2*E2. После ввода нажмите «Enter».

Задание 2. В ячейке F2 введите только знак «=». После чего сделайте щелчок по ячейке D2, дальше нажмите «+», потом еще раз щелчок по D2, дальше введите «*», и щелчок по ячейке E2. После нажатия клавиши «Enter» получаем аналогичный результат.

Существуют и другие способы введения формул, но в данной ситуации достаточно и этих двух вариантов.

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

Копирование формул в колонку

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

Задание 1. Перейдите в ячейку F3 и нажмите комбинацию клавиш CTRL+D. Таким образом, автоматически скопируется формула, которая находится в ячейке выше (F2). Так Excel позволяет скопировать формулу на весь столбец. Также сделайте и в ячейке F4.

Задание 2. Удалите формулы в ячейках F3:F4 (выделите диапазон и нажмите клавишу «delete»). Далее выделите диапазон ячеек F2:F4. И нажмите комбинацию клавиш CTRL+D. Как видите, это еще более эффективный способ заполнить целую колонку ячеек формулой из F2.

Задание 3. Удалите формулы в диапазоне F3:F4. Сделайте активной ячейку F2, переместив на нее курсор. Далее наведите курсор мышки на точку в нижнем правом углу прямоугольного курсора. Курсор мышки изменит свой внешний вид на знак плюс «+». Тогда удерживая левую клавишу мыши, проведите курсор вниз еще на 2 ячейки, так чтобы выделить весь диапазон F2:F4.

Как только вы отпустите левую клавишу, формула автоматически скопируется в каждую ячейку.

• с помощью инструментов на полосе;
• с помощью комбинации горячих клавиш;
• с помощью управления курсором мышки и нажатой клавишей «CTRL».

Эти способы более удобны для определенных ситуаций, которые мы рассмотрим на следующих уроках.

Готовые решения для Excel с помощью формул работающих с целыми диапазонами данных в процессе сложных вычислений и расчетов.

Синтаксис формул в Excel

Начинайте любую формулу со знака равно (=). Знак равно говорит Excel, что набор символов, которые вы вводите в ячейку — это математическая формула. Если вы забудете знак равно, то Excel будет трактовать ввод как набор символов.

• Наиболее распространенная координатная ссылка — это использование буквы или букв, представляющих столбец, а за ней номер строки, в которой находится ячейка: например, А1 указывает на ячейку в столбце А и строке 1. Если вы добавите строки над ячейкой, то ссылка на ячейку изменится, чтобы отобразить ее новую позицию; добавление строки над ячейкой А1 и столбца слева от нее, изменит ссылку на нее на В2 во всех формулах, которые ее используют.
• Разновидность этой формулы — сделать строковую либо столбцовую ссылки абсолютными, добавив знак доллара ($) перед ними. Хотя ссылка на ячейку A1 изменится, если будет добавлена строка над ней или столбец слева от нее, ссылка $A$1 всегда будет указывать на верхнюю левую левую ячейку на листе; таким образом, в формуле, ячейка $A$1 может иметь другое или даже недопустимое значение в формуле, если строки или столбцы вставляются на лист. (При желании, вы можете использовать абсолютную ссылку для столбца или строки отдельно, например, $A1 или A$1).
• Другой способ сделать ссылку на ячейку — это числовой метод, в формате RxCy, где «R» указывает на «строку,» «C» указывает на «столбец,» а «x» и «y» — номера строки и столбца соответственно. Например, ссылка R5C4 в этом формате указывает на то же место, что и ссылка $D$5. Ссылка типа RxCy указывает на ячейку относительно левого верхнего угла листа, то есть есть если вы вставите строку над ячейкой или столбец слева от ячейки, то ссылка на нее изменится.
• Если вы используете в формуле только знак равно и ссылку на единственную ячейку, то вы, фактически, копируете значение из другой ячейки в новую ячейку. Например, ввод «=A2» в ячейку B3 скопирует значение, введенное в ячейку А2, в ячейку В3. Чтобы скопировать значение из ячейки на другом листе, добавьте имя листа, а за ним восклицательный знак (!). Ввод «=Лист1!B6» in Cell F7 на Лист2 отобразит значение ячейки В6 на Лист1 в ячейке F7 на Лист2.

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

• Отрицание: Знак минус (-). Эта операция возвращает число, противоположное по знаку числу или ссылке на ячейку (это эквивалентно умножению на -1). Этот оператор нужно ставить перед числом.
• Процент: Знак процента (%). Эта операция вернет десятичный эквивалент процента числовой константы.Этот оператор нужно ставить после числа.
• Возведение в степень: Знак вставки (^). Эта операция возводит число (либо значение ссылки), стоящее до знака вставки, в степень, равную числу (либо значению ссылки) после знака вставки. Например, «=3^2» — это 9.
• Умножение: Звездочка (*). Звездочка используется для умножения, чтобы умножение не путали с буквой «x.»
• Деление: Косая черта (/). Умножение и деление имеют одинаковый приоритет, они выполняются слева направо.
• Сложение: Знак плюс (+).
• Вычитание: Знак минус (-). У сложения и вычитания одинаковый приоритет, они выполняются слева направо.

Используйте операторы сравнения, чтобы сравнить значения в ячейках. Чаще всего, вы буде использовать операторы сравнения с функцией ЕСЛИ. Вы ставите ссылку на ячейку, числовую константу или функцию, которая возвращает числовое значение, по обе стороны оператора сравнения. Операторы сравнения указаны ниже:

• Равно: Знак равно (=).
• Не равно (&lt;&gt;).
• Меньше (&lt;).
• Меньше или равно (&lt;=).
• Больше (&gt;).
• Больше или равно (&gt;=).

Используйте амперсанд (&), чтобы соединить текстовые строки. Соединение текстовых строк в одну называется конкатенация, и амперсанд — это оператор, который делает в Excel конкатенацию. Можно использовать амперсанд со строками или ссылками на строки; например, ввод «=A1&B2» в ячейку C3 отобразит «АВТОЗАВОД», если в ячейку A1 введено «АВТО», а в ячейку B2 введено «ЗАВОД».

Используйте ссылочные операторы при работе с областью ячеек. Наиболее часто вы будете использовать область ячеек с функциями Excel, такими как СУММ, которая находит сумму значений области ячеек. Excel использует 3 ссылочных оператор:

• Оператор области: двоеточие (:). Оператор области указывает на все ячейки в области, которая начинается с ячейки перед двоеточием и заканчивается ячейкой после двоеточия. Обычно, все ячейки в той же строке или столбце; «=СУММ(B6:B12)» отобразит результат сложения значений ячеек B6, B7, B8, B9, B10, B11, B12, в то время как «=СРЗНАЧ(B6:F6)» отобразит среднее арифметическое значений ячеек с B6 до F6.
• Оператор объединения: запятая (,). Оператор объединения включает все ячейки или области ячеек до и после него; «=СУММ(B6:B12, C6:C12)» суммирует значения ячеек с B6 до B12 и с C6 до C12.
• Оператор пересечения: пробел (). Оператор пересечения ищет ячейки, общие для 2-х или более областей; например, «=B5:D5 C4:C6» это только значение ячейки C5, поскольку она встречается и с первой, и второй области.

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

• Функции — это заранее определенные формулы. Такие, как SIN, COS или TAN, требуют один аргумент, в то время как ЕСЛИ, СУММ или СРЗНАЧ могут принимать много аргументов. Аргументы внутри функции отделяются запятой, например, «=ЕСЛИ (A4 &gt;=0, «ПОЛОЖИТЕЛЬНОЕ,» «ОТРИЦАТЕЛЬНОЕ»)» для функции ЕСЛИ. Функции могут быть вложены в другие функции, до 64-х уровней.
• В формулах с математическими операциями, операции внутри скобок выполняются раньше, чем вне их; например, в «=A4+B4*C4,» B4 умножается на C4 и результат прибавляется к A4, а в «=(A4+B4)*C4,» сначала складываются A4 и B4, а затем результат умножается на C4. Скобки в операциях могут быть вложены одна в другую, операция внутри самой внутренней пары скобок будет выполнена первой.
• Не имеет значения встречаются ли вложенные скобки в математических операциях или во вложенных скобках, всегда следите за тем, чтобы количество открывающихся скобок равнялось количеству закрывающихся, иначе получите сообщение об ошибке.

Ввод формул

Введите знак равно в ячейку или в строку формулы. Строка формулы находится над строками и столбцами ячеек и под строкой меню и лентой.

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

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

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

Как копировать и протягивать формулу

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

Маркер автозаполнения

В правом нижнем углу активной ячейки есть маленький квадратик — это маркер заполнения. Если на него навести курсор, он превращается в тонкий черный крестик.

1. Выделите ячейку с готовой формулой.
2. Наведите курсор на маркер заполнения, пока он не станет черным крестиком.
3. Зажмите левую кнопку мыши и протяните вниз (если данные в столбце) или вправо (если в строке). Excel автоматически скопирует формулу во все выделенные ячейки, адаптируя относительные ссылки.

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

Копирование через буфер обмена

Ctrl+C Ctrl+V
Способ — работает и в таблицах, но с нюансами — если так вставить, то Excel копирует не только формулу, но и формат ячейки (заливку, границы, шрифт).

Ctrl+Alt+V
Если нужно скопировать только формулу, то понадобится специальная вставка и выбрать вариант «Формулы». Тогда форматирование исходной ячейки не перенесется на новую.

Что происходит со ссылками при копировании

При копировании и протягивании Excel ведет себя по‑разному в зависимости от типа ссылок:

• Относительные ссылки (A1) автоматически сдвигаются пропорционально смещению. Если формулу из ячейки B2 скопировали в B3, ссылка A2 превратится в A3. Это позволяет быстро обрабатывать столбцы данных.
• Абсолютные ссылки ($A$1) остаются неизменными при любом копировании — они всегда указывают на одну и ту же ячейку.
• Смешанные ссылки ($A1 или A$1) фиксируют только одну часть (столбец или строку), а другая часть меняется при копировании.

Относительные ссылки (A1) автоматически сдвигаются пропорционально смещению. Если формулу из ячейки B2 скопировали в B3, ссылка A2 превратится в A3. Это позволяет быстро обрабатывать столбцы данных.

Абсолютные ссылки ($A$1) остаются неизменными при любом копировании — они всегда указывают на одну и ту же ячейку.

Смешанные ссылки ($A1 или A$1) фиксируют только одну часть (столбец или строку), а другая часть меняется при копировании.

#ССЫЛКА!
Если после копирования формула стала выдавать неверные значения или ошибку — проверьте, не стали ли абсолютные ссылки относительными и наоборот.

Кстати, если случайно скопировать формулу в пустые ячейки, то ячейка покажет ноль или такую же ошибку.

Теперь, когда мы разобрались, как написать и протянуть формулу в Excel, перейдем к использованию конкретных формул.

Математические формулы с примерами

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

СУММ

=СУММ(число1; [число2];…)
Синтаксис., где в качестве аргументов чаще всего выступают диапазоны ячеек, например A1:A100.

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

=СУММ(D2:D32)
Чтобы посчитать общие расходы на рекламу на месяц и сложить все значения из столбца D со 2-й по 32-ю строку, используют формулу.

СРЗНАЧ

=СРЗНАЧ(число1; [число2];…)
Синтаксис.. Пустые ячейки и ячейки с текстом игнорируются.

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

Для англоязычной версии Excel будет работать формула =AVERAGE(A1:A10), вычислит среднее значение чисел в столбце A от 1 до 10.

МИН и МАКС

Что делает. Находят минимальное и максимальное значение в диапазоне. Например, можно найти день с минимальными/максимальными продажами за месяц или с минимальным/максимальным трафиком на сайте.

ПРОИЗВЕД

Что делает. Перемножает заданные числа. Удобно, когда нужно посчитать произведение нескольких ячеек или диапазонов, не записывая громоздкую формулу типа A1*B1*C1.

Поможет посчитать общую стоимость товара на складе: =ПРОИЗВЕД(B2; C2) — умножает цену (B2) на количество (C2).

ОКРУГЛ

Что делает. Округляет число до указанного количества десятичных знаков и помогает избавиться от «длинных хвостов» после запятой.

• Если число_разрядов положительное, округление идет до сотых, тысячных и т. д. Пример — строкой выше.
• Если равно 0 — до целого. Пример: =ОКРУГЛ(1111,2233; 0) даст 1111.
• Если отрицательное — до десятков, сотен и т. д.Пример: =ОКРУГЛ(1111,2233; −2) даст 1100.

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

Если отрицательное — до десятков, сотен и т. д.Пример: =ОКРУГЛ(1111,2233; −2) даст 1100.

• привести цену к стандартному виду с двумя знаками после запятой: =ОКРУГЛ(A2; 2);
• округлить сумму НДС до рублей (без копеек): =ОКРУГЛ(B2*0,2; 0);
• округлить выручку до тысяч рублей для укрупненного отчета: =ОКРУГЛ(СУММ(C:C); -3).

=ОКРУГЛ(A2; 2)
привести цену к стандартному виду с двумя знаками после запятой:;

=ОКРУГЛ(СУММ(C:C); -3)
округлить выручку до тысяч рублей для укрупненного отчета:.

ОСТАТ

Например, =ОСТАТ(10;3) вернет 1, так как 10 / 3 = 3 (целых), остаток = 1.

• быстро проверить, является ли число четным. Если =ОСТАТ(A2;2) равно 0 — число четное;
• понять, сколько товара не поместилось в коробки. Например, есть 23 единицы товара, в коробку помещается по 5 штук. =ОСТАТОК(23; 5) → 3. Четыре полные коробки (20 шт) и 3 штуки не поместились.

быстро проверить, является ли число четным. Если =ОСТАТ(A2;2) равно 0 — число четное;

понять, сколько товара не поместилось в коробки. Например, есть 23 единицы товара, в коробку помещается по 5 штук. =ОСТАТОК(23; 5) → 3. Четыре полные коробки (20 шт) и 3 штуки не поместились.

СУММЕСЛИ

Что делает. Суммирует ячейки только в том случае, если они соответствуют заданному условию.

• диапазон — ячейки, которые проверяются на соответствие условию.
• критерий — само условие (число, текст, выражение).
• диапазон_суммирования — фактические ячейки для суммирования. Если не указан, суммируются ячейки из первого диапазона.

=СУММЕСЛИ(A:A; «Яндекс Директ»; B:B)
Так можно посчитать, сколько денег потрачено на рекламу в Яндекс Директе: в столбце A — названия каналов, в столбце B — расходы. Формула:.

СУММЕСЛИМН

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

Обратите внимание на порядок: первым всегда идет диапазон суммирования, а потом пары «диапазон-критерий».

Представьте, что нужно рассчитать расходы на Яндекс Директ за февраль. В столбце A — каналы, в столбце B — месяцы, в столбце C — суммы. Формула: =СУММЕСЛИМН(C:C; A:A; «Яндекс Директ»; B:B; «Февраль»).

Логические функции с примерами

Логические функции в Excel — это инструменты для принятия решений прямо внутри таблицы. Они позволяют проверять данные на соответствие условиям и в зависимости от результата выдавать нужный текст, выполнять расчеты или помечать строки. Давайте разберем основные логические функции Excel.

ЕСЛИ

Что делает. Она проверяет условие и возвращает одно значение, если условие истинно, и другое — если ложно.

=ЕСЛИ(B2<=C2; «План выполнен»; «План не выполнен»)
Самый простой пример использования — проверка выполнения плана. Допустим, в ячейке B2 — план, в C2 — фактическая выручка. Формула:

ЕСЛИМН

Что делает. Она проверяет условия по очереди и возвращает значение, соответствующее первому истинному условию.

Тогда Excel проверит условия по порядку: если G2<1, вернет «Низкая», если нет — проверит G2<3 и т.д.

И

Таблица №3

Критерий	Excel	Google Таблицы (Sheets)
Массивы — набор данных, объединенных в одну структуру, например, столбец, строка, прямоугольный диапазон	Требуют ввода через Ctrl+Shift+Enter в старых версиях. В новых версиях массивы обрабатываются динамически	Работают «из коробки», поддерживаются динамические массивы
Производительность	Быстрее работает с очень большими таблицами (сотни тысяч строк) и сложными вычислительными моделями	Может замедляться при работе с большими объемами данных (более 10–20 тыс. строк) или вложенными формулами
Доступность	Требует установки, работает офлайн	В браузере, доступно с любого устройства
Совместная работа	Ограничена, через OneDrive или SharePoint	Редактирование в реальном времени, история изменений

На маркетплейсе eLama вы найдете инструменты для организации сквозной аналитики, в том числе Roistat. Они сводят данные из популярных рекламных площадок, CRM-систем и других источников и формируют удобные и понятные отчеты. Инструменты доступны абсолютно бесплатно клиентам eLama.

ИЛИ

Что делает. Функция ИЛИ возвращает ИСТИНА, если выполняется хотя бы одно из перечисленных условий, ЛОЖЬ — только если все условия ложны. Тоже обычно используется в связке с ЕСЛИ.

Как выявить проблемные заказы. Если заказ просрочен (столбец M = «да») ИЛИ сумма меньше минимальной (столбец N < 1000), то требует внимания:

НЕ

Что делает. Функция НЕ меняет логическое значение на противоположное: ИСТИНА превращает в ЛОЖЬ, ЛОЖЬ — в ИСТИНУ. Часто полезна, когда нужно исключить какие-то данные.

Так можно проверить, что значение не пустое. Функция НЕ(ЕПУСТО(A2)) вернет ИСТИНА (TRUE), если ячейка не пуста. Это удобно для фильтрации.

ЕСЛИОШИБКА

Что делает. Она проверяет, не приводит ли вычисление к ошибке (например, #ДЕЛ/0!, #Н/Д, #ЗНАЧ!), и если ошибка есть, подменяет ее на указанное вами значение. Это делает отчеты аккуратными, без пугающих красных надписей и позволяет избежать каскадных ошибок в расчетах.

• значение — выражение или формула, которую нужно проверить;
• значение_если_ошибка — что вернуть, если в выражении возникла ошибка.

#ДЕЛ/0! =ЕСЛИОШИБКА(C2/B2; 0)
Допустим, вы считаете конверсию как =Заявки/Клики. Если кликов ноль, Excel выдаст. Чтобы этого избежать, используйте:. Тогда в отсутствии кликов в ячейке будет честный ноль, а не ошибка.

Текстовые функции с примерами

Текстовые функции помогают приводить данные к единому виду, склеивать значения из разных ячеек, извлекать части информации, чистить текст от лишних пробелов и символов. Функции помогают готовить отчеты и обрабатывать выгрузки из CRM.

СЦЕПИТЬ

Что делает. Соединяет (склеивает) несколько текстовых строк в одну. Устаревшая функция, но всё еще популярна. В новых версиях Excel есть более удобный аналог — ОБЪЕДИНИТЬ или оператор &.

Так можно собрать из имени и фамилии полное ФИО. В ячейке A2 — имя, B2 — фамилия.С формулой =СЦЕПИТЬ(B2; » «; A2) — получится «Иванов Иван».

ЛЕВСИМВ

Что делает. Извлекает заданное количество символов из начала (слева) текстовой строки.

=ЛЕВСИМВ(текст; [количество_знаков])
Синтаксис.. Если количество_знаков не указано, возвращается первый символ.

Так можно выделить код категории (первые три буквы) из артикула товара. Например, из «ABC-123-футболка» =ЛЕВСИМВ(A2; 3) вернет «ABC».

ПРАВСИМВ

Что делает. Извлекает заданное количество символов с конца (справа) текстовой строки.

Из того же артикула можно выделить название товара (последние восемь символов): =ПРАВСИМВ(A2; 8) — вернет «футболка».

ПСТР

Что делает. Извлекает подстроку из текста, начиная с указанной позиции и заданной длины.

Например, можно вытащить домен из email. Если email в ячейке B2, то =ПСТР(B2; НАЙТИ(«@»; B2)+1; ДЛСТР(B2)-НАЙТИ(«@«; B2)) вернет часть после @.

ДЛСТР

Так можно посчитать количество символов в поле «Описание» перед публикацией, чтобы проверить на ограничение по длине.

СЖПРОБЕЛЫ

Что делает. Удаляет все лишние пробелы из текста: убирает начальные и конечные пробелы, а также заменяет множественные пробелы внутри на одинарные. Такие «мусорные» пробелы часто встречаются при выгрузке из CRM.

ПРОПИСН

Эта формула помогает привести все названия брендов к единому регистру для сводных таблиц. =ПРОПИСН(H2) — «adidas» превратится в «ADIDAS».

СТРОЧН

Формула помогает унифицировать email-адреса для поиска (обычно email в нижнем регистре). =СТРОЧН(I2) — «Ivanov@» станет «ivanov@».

Функции поиска и подстановки

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

ВПР

Что делает. Ищет значение в первом столбце таблицы и возвращает соответствующее значение из другого столбца в той же строке.

• искомое_значение — что ищем (например, артикул товара).
• таблица — диапазон, в котором нужно искать (первый столбец этого диапазона должен содержать искомые значения).
• номер_столбца — номер столбца в диапазоне (от 1), из которого нужно вернуть данные.
• интервальный_просмотр — ЛОЖЬ (0) для точного совпадения, ИСТИНА (1) для приблизительного.

номер_столбца — номер столбца в диапазоне (от 1), из которого нужно вернуть данные.

интервальный_просмотр — ЛОЖЬ (0) для точного совпадения, ИСТИНА (1) для приблизительного.

С помощью формулы можно найти цену товара по его артикулу. В прайсе (столбцы A:B) в столбце A — артикул, в B — цена. В текущей таблице в ячейке A2 — артикул.

ГПР

Что делает. То же, что и ВПР, но ищет не в первом столбце, а в первой строке таблицы. Горизонтальная подстановка.

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

=ГПР(«Март»; A1:Z1; 5; 0) — найдет столбец с «Март» в первой строке и вернет значение из 5-й строки этого столбца.

ИНДЕКС+ПОИСКПОЗ

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

Если в таблице столбец A — ID товара, B — артикул, C — цена. Нужно найти цену по артикулу (ищем в столбце B, возвращаем из C): =ИНДЕКС(C:C; ПОИСКПОЗ(«А123″; B:B; 0))

ПРОСМОТРX

Что делает. Доступна в новых версиях Excel (Office 365, Excel 2026 и выше). Позволяет искать значение в любом диапазоне и возвращать результат из любого другого диапазона, поддерживает поиск как по вертикали, так и по горизонтали.

Функции дат и времени

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

СЕГОДНЯ

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

=»Отчет за » & ТЕКСТ(СЕГОДНЯ(); «ДД.ММ.ГГГГ») — получится «Отчет за 15.03.2026».

ТДАТА

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

ДНИ

=ДНИ(кон_дата; нач_дата)
Синтаксис.. Важно: сначала указывается конечная дата, потом начальная.

Рассчитать просрочку платежа. Если плановая дата оплаты указана в J2, а фактическая — в K2, то просрочка: =ДНИ(K2; J2) (если дата оплаты позже, результат будет положительным).

ГОД / МЕСЯЦ

=ЦЕЛОЕ((МЕСЯЦ(D2)-1)/3)+1 — универсальная формула для квартала. Важно, что формат ячейки должен быть либо числовой, либо общий — тогда формула будет работать корректно.

ДАТА

Что делает. Собирает дату из отдельных компонентов: года, месяца, дня. Очень полезно, когда год, месяц и день лежат в разных столбцах.

СЧЁТ

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

Помогает посчитать, сколько сотрудников получили зарплату (в столбце B сумма начисления, если есть число — значит начислено).

СЧЁТЗ

Что делает. Подсчитывает количество непустых ячеек в диапазоне (и текст, и числа, и любые символы).

Помогает посчитать количество заполненных строк в таблице с данными, например, о сотрудниках (где в столбце D стоит ФИО).

СЧЁТЕСЛИ

Что делает. Подсчитывает количество ячеек в диапазоне, которые удовлетворяют одному условию.

Посчитать, сколько раз в столбце «Регион» встречается «Москва»: =СЧЁТЕСЛИ(E:E; «Москва»).

СЧЁТЕСЛИМН

Что делает. Подсчитывает количество ячеек, которые соответствуют нескольким условиям одновременно. Порядок аргументов: сначала пары «диапазон; критерий».

Количество товаров, проданных в Москве на сумму более 5000 руб. (столбец K — город, L — сумма): =СЧЁТЕСЛИМН(K:K; «Москва»; L:L; «>5000»).

СРЗНАЧЕСЛИ

Что делает. Вычисляет среднее арифметическое только для тех ячеек, которые удовлетворяют указанному в формуле условию.

• диапазон — ячейки, к которым применяется условие
• критерий — условие, например «Яндекс Директ» или «&gt;100»
• [диапазон_усреднения] — необязательный диапазон, значения которого будут усреднены. Если его не указать, усредняется сам диапазон с условием.

[диапазон_усреднения] — необязательный диапазон, значения которого будут усреднены. Если его не указать, усредняется сам диапазон с условием.

Средняя стоимость клика (CPC) только для кампаний в Яндекс Директ (столбец O — канал, P — CPC): =СРЗНАЧЕСЛИ(O:O; «Яндекс Директ»; P:P).

Частые ошибки в формулах и как их исправить

Любая ошибка в Excel подсказывает, что пошло не так. Осталось в них разобраться и научиться исправлять.

#ЗНАЧ! (#VALUE!)

Что это значит. В формуле используется не тот тип данных. Например, вы пытаетесь сложить число и текст, или вместо числа подставили дату в неверном формате.

• Проверьте, все ли ячейки, на которые ссылается формула, содержат числа.
• Убедитесь, что в ячейках нет скрытых пробелов или непечатаемых символов (используйте функцию СЖПРОБЕЛЫ).
• Если вы импортировали данные, числа могут быть в текстовом формате — преобразуйте их в числовой.

Убедитесь, что в ячейках нет скрытых пробелов или непечатаемых символов (используйте функцию СЖПРОБЕЛЫ).

Если вы импортировали данные, числа могут быть в текстовом формате — преобразуйте их в числовой.

#ССЫЛКА! (#REF!)

Что это значит. Формула ссылается на ячейку, которая была удалена или заменена.

• Отмените последние действия (Ctrl+Z), если удаление произошло случайно.
• Проверьте, не перемещался ли диапазон, на который ссылается формула.
• Вручную скорректируйте ссылки в формуле, указав правильный диапазон.

Отмените последние действия (Ctrl+Z), если удаление произошло случайно.

#ДЕЛ/0! (#DIV/0!)

Что это значит. Попытка делить на ноль. Это частая ошибка при расчете конверсии или средних значений, когда в знаменателе ноль.

• Проверьте ячейку-знаменатель: там действительно ноль или пустота.
• Используйте функцию ЕСЛИОШИБКА или ЕСЛИ, чтобы избежать деления на ноль: =ЕСЛИ(B2=0; 0; A2/B2).
• Или оберните формулу в ЕСЛИОШТИБКА: =ЕСЛИОШИБКА(A2/B2; 0).

=ЕСЛИ(B2=0; 0; A2/B2)
Используйте функцию ЕСЛИОШИБКА или ЕСЛИ, чтобы избежать деления на ноль:.

#ИМЯ? (#NAME?)

Что это значит. Excel не распознает текст в формуле. Чаще всего это опечатка в имени функции или не заключенный в кавычки текст.

• Проверьте, правильно ли написано название функции (например, СУММ, а не СУММА).
• Убедитесь, что текстовые значения внутри формулы заключены в двойные кавычки: =ЕСЛИ(A1=»Москва»;…).
• Возможно, вы используете функцию, которая есть в новой версии Excel, а у вас старая.

Проверьте, правильно ли написано название функции (например, СУММ, а не СУММА).

=ЕСЛИ(A1=»Москва»;…)
Убедитесь, что текстовые значения внутри формулы заключены в двойные кавычки:.

Возможно, вы используете функцию, которая есть в новой версии Excel, а у вас старая.

#Н/Д (#N/A)

Что это значит. «Нет данных». Обычно появляется при работе с функциями поиска (ВПР, ПОИСКПОЗ, ПРОСМОТРX), когда искомое значение не найдено.

• Проверьте, действительно ли искомое значение существует в таблице.
• Используйте функцию ЕСЛИОШИБКА, чтобы подменить ошибку на понятный текст: =ЕСЛИОШИБКА(ВПР(A2; B:C; 2; 0); «Не найдено»)
• Убедитесь, что в таблице нет лишних пробелов или несовпадений в регистре.

Убедитесь, что в таблице нет лишних пробелов или несовпадений в регистре.

Кольцевая ссылка (#REF!)

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

• Внимательно посмотрите на формулу: она не должна ссылаться сама на себя.
• Если вы случайно использовали неправильную ссылку, исправьте ее на корректную.
• Иногда кольцевая ссылка возникает из-за перекрестных ссылок на других листах — проверьте всю цепочку.

Внимательно посмотрите на формулу: она не должна ссылаться сама на себя.

Если вы случайно использовали неправильную ссылку, исправьте ее на корректную.

Иногда кольцевая ссылка возникает из-за перекрестных ссылок на других листах — проверьте всю цепочку.

Просмотр формулы

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

Горячие клавиши и лайфхаки

Горячие клавиши экономят часы жизни. Собрали самые полезные сочетания для работы с формулами и данными:

Таблица №4

Действие	Горячие клавиши
Переместиться к краю области данных	Ctrl + стрелка ←↑→↓
Выделить до края области	Ctrl + Shift + стрелка ←↑→↓
Быстрое суммирование выделенного диапазона	Alt + =
Открывает окно «Перейти», позволяет мгновенно переместиться к нужной ячейке или диапазону	F5
Копирование формулы с протягиванием в рамках выделенного диапазона. Формула должна быть в первой ячейке	Ctrl + D (вниз), Ctrl + R (вправо)
Открыть формат ячеек	Ctrl + 1
Отображение всех формул на листе вместо результатов	Ctrl + ` (тильда, она же буква Ё в русской раскладке)
Пересчет всех формул вручную	F9
Закрепить строку или столбец при просмотре больших таблиц	Вид → Закрепить области (мышкой, но запомнить стоит)
Выводит на экран диалоговое окно «Вставка гиперссылки» для новых гиперссылок или «Изменение гиперссылки» для существующей выбранной гиперссылки	Ctrl + K
Выделить все ячейки, содержащие комментарии	Ctrl + Shift + O
Выделить строку	Shift + Space
Выделить столбец	Ctrl + Space
Переключение между листами	Ctrl + PageUp / PageDown

* Для Excel и Google Таблиц сочетания могут различаться. Точные значения можно посмотреть в справке:

• для Excel — значок вопроса или F1;
• для Google Таблиц — Ctrl+/

Кстати, в Google Таблицах можно поставить флажок «Включить совместимые быстрые клавиши для таблиц». Тогда в списке будут только сочетания, которые аналогично работают и в Excel.

Общий совет. Если вы часто переносите формулы из одной среды в другую, лучше использовать английские названия функций (SUM, IF, VLOOKUP) — они работают и там, и там (в Google Таблицах обязательно английские, в Excel тоже поймут, если у вас английская версия или включена поддержка).

Как проверить, нет ли в формулах ошибок?

Чтобы проверить, нет ли ошибок, запустите проверку: вкладка «Формулы» → «Проверить наличие ошибок». Excel подсветит проблемные места.

Как быстро пересчитать все формулы в большом файле, который тормозит?

Перейдите в «Формулы» → «Параметры вычислений» и установите «Вручную». Когда нужно обновить данные, нажмите F9. Это позволит избежать постоянных пересчетов при каждом изменении.

Почему формула не обновляется автоматически?

Проверьте, не включен ли ручной режим вычислений (см. выше). Также убедитесь, что формат ячейки не текстовый. Если ячейка имеет текстовый формат, формула не будет считаться — исправьте на общий или числовой и нажмите F2, Enter.

Нажмите правой кнопку на ячейку или диапазон, выберите «Формат ячеек» и найдите нужный формат.

Как посмотреть все зависимости формулы (на какие ячейки она ссылается)?

Выделите ячейку и нажмите Ctrl+[(открыть квадратную скобку), важно, чтобы была установлена английская раскладка клавиатуры. Чтобы увидеть, какие ячейки ссылаются на текущую, используйте Ctrl+].

Если способ выше не работает, есть другой: выберите ячейку, во вкладке «Формулы» выберите влияющие или зависимые ячейки — в зависимости от нужного результата.

Можно ли в формуле использовать данные с другого листа или книги?

Да. Ссылка на другой лист: =Лист2!A1. Ссылка на другую книгу: =’[Бюджет]Лист1’!$C$4. При копировании формул с внешними ссылками будьте осторожны: если файл-источник переместится, ссылка сломается.

Как сделать так, чтобы при копировании формулы ссылка на конкретную ячейку не менялась?

Используйте абсолютные ссылки со знаком $. Например, $A$1 останется неизменной при любом копировании. Для фиксации только строки или только столбца применяйте смешанные ссылки: $A1 или A$1. Клавиша F4 при редактировании формулы быстро перебирает все варианты.

Как скопировать только результат формулы, а не саму формулу?

Скопируйте ячейки, затем нажмите правую кнопку мыши → «Специальная вставка» → выберите «Значения» (или клавиши Ctrl+Alt+V, затем V). Тогда останутся только цифры, а формула исчезнет.

Что делать, если Excel пишет «Недостаточно памяти» при работе с большими таблицами?

• Закрыть другие программы.
• Уменьшить количество вычисляемых формул, заменив их значениями там, где это возможно.
• Переключиться в ручной режим вычислений. Excel может перегружаться, потому что пытается пересчитать все формулы при каждом изменении, поэтому стоит параметры вычислений переключить на ручной режим — тогда будет нужно запускать пересчет самостоятельно по кнопке F9 или комбинации Shift + F9.
• Использовать более простые формулы или сводные таблицы.

Уменьшить количество вычисляемых формул, заменив их значениями там, где это возможно.

Переключиться в ручной режим вычислений. Excel может перегружаться, потому что пытается пересчитать все формулы при каждом изменении, поэтому стоит параметры вычислений переключить на ручной режим — тогда будет нужно запускать пересчет самостоятельно по кнопке F9 или комбинации Shift + F9.

Скачивание книги "Учебник по формулам"

Мы подготовили для вас книгу Начало работы с формулами, которая доступна для скачивания. Если вы впервые пользуетесь Excel или даже имеете некоторый опыт работы с этой программой, данный учебник поможет вам ознакомиться с самыми распространенными формулами. Благодаря наглядным примерам вы сможете вычислять сумму, количество, среднее значение и подставлять данные не хуже профессионалов.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

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

Excel в Интернете делает это с помощью формул в ячейках. Формула выполняет вычисления или другие действия с данными на листе. Формула всегда начинается со знака равенства (=), за которым могут следовать числа, математические операторы (например, знак «плюс» или «минус») и функции, которые значительно расширяют возможности формулы.

Ниже приведен пример формулы, умножающей 2 на 3 и прибавляющей к результату 5, чтобы получить 11.

Следующая формула использует функцию ПЛТ для вычисления платежа по ипотеке (1 073,64 долларов США) с 5% ставкой (5% разделить на 12 месяцев равняется ежемесячному проценту) на период в 30 лет (360 месяцев) с займом на сумму 200 000 долларов:

=КОРЕНЬ(A1) Использует функцию КОРЕНЬ для возврата значения квадратного корня числа в ячейке A1.

=ПРОПИСН(«привет») Преобразует текст «привет» в «ПРИВЕТ» с помощью функции ПРОПИСН.

=ЕСЛИ(A1>0) Анализирует ячейку A1 и проверяет, превышает ли значение в ней нуль.

Ответы на частые вопросы по созданию формул в Excel

Вопрос: Как начать ввод формулы в Excel?
Ответ: Всегда начинайте ввод формулы со знака равенства (=) в строке формул или непосредственно в ячейке.

Вопрос: Что такое относительная ссылка в формуле?
Ответ: Это ссылка, которая автоматически меняется при копировании формулы в другую ячейку (например, A1).

Вопрос: Как зафиксировать ячейку в формуле, чтобы она не менялась при копировании?
Ответ: Используйте абсолютную ссылку, добавив знаки доллара ($A$1).

Вопрос: Какую функцию использовать для суммирования значений?
Ответ: Используйте функцию СУММ (SUM), указав диапазон ячеек.

Вопрос: Что делать, если после ввода формулы ячейка показывает #ДЕЛ/0!?
Ответ: Это ошибка деления на ноль. Проверьте, что делитель в формуле не равен нулю или пустой ячейке.

Вопрос: Как быстро применить одну и ту же формулу ко всему столбцу?
Ответ: Используйте маркер автозаполнения (маленький квадрат в правом нижнем углу ячейки) или дважды кликните по нему.

Вопрос: Как посмотреть, из каких ячеек состоит формула?
Ответ: Выделите ячейку с формулой и нажмите клавишу F2, чтобы войти в режим редактирования, или используйте инструмент «Влияющие ячейки».

Вопрос: Можно ли использовать данные с другого листа в формуле?
Ответ: Да, для этого укажите название листа и восклицательный знак перед ссылкой на ячейку (Лист2!A1).

Вопрос: Как скопировать только значение, а не формулу?
Ответ: Скопируйте ячейку, затем нажмите правой кнопкой мыши и выберите «Специальная вставка» → «Значения».

Вопрос: Почему формула не пересчитывается автоматически?
Ответ: Возможно, в настройках книги включен ручной режим вычислений. Переключите его на автоматический в разделе «Формулы».