Как работать с формулами в Excel: гайд для чайников

Введение в формулы Excel

С формулами в Excel можно автоматически выполнять расчёты, работать с текстом, анализировать данные и автоматизировать задачи. Принцип такой: вы вводите формулу в ячейку, нажимаете Enter — и программа мгновенно выполняет вычисления вместо вас и показывает результат.

Формулы работают с разными типами данных:

• числами — для арифметических операций;

• текстом — для объединения и обработки строк;

• датами и временем — для расчётов и преобразований;

• логическими значениями (ИСТИНА и ЛОЖЬ) — для условий и проверок.

Применение формул упрощает работу с таблицами, сокращает количество ошибок и позволяет автоматизировать расчёты. 

Структура формулы в Excel

Формула состоит из нескольких элементов:

• Знак равно (=) — всегда стоит в начале. Без него Excel не распознает ввод как формулу.

• Операнды — это данные, с которыми работает формула. Они бывают трёх типов: числовые значения (используются напрямую или берутся из ячеек), текстовые строки (заключаются в кавычки), логические значения (применяются в условиях и проверках).

• Операторы — задают действия внутри формулы. Основные группы: арифметические (выполняют математические расчёты), текстовый & (объединяет строки), сравнительные (используются для сравнения значений).

• Ссылки на ячейки — нужны для работы с данными из таблицы. Они бывают относительными, абсолютными и смешанными. Подробнее о них расскажем дальше.

Создание и вставка формул

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

Ручная вставка

Самый простой и универсальный способ — ввести формулу прямо в ячейку:

• выберите ячейку, где должен появиться результат;

• введите знак «=», затем формулу;

• нажмите Enter — Excel выполнит расчёт.

При вводе можно использовать ссылки на другие ячейки, диапазоны и функции. Например, =A1+B1 сложит значения из двух ячеек, а =СУММ(A1:A10) рассчитает сумму чисел в указанном диапазоне.

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

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

• перейдите во вкладку «Формулы»;

• выберите нужную категорию, например, «Математические» или «Логические»;

• нажмите на функцию, заполните аргументы в открывшемся окне;

• подтвердите ввод — Excel автоматически вставит формулу в ячейку.

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

Автосуммирование

Для быстрого подсчёта суммы можно использовать кнопку «Автосумма» (∑) во вкладке «Формулы» или на панели «Главная». Это автоматически создаёт формулу =СУММ.

Для этого:

• выберите ячейку, где должен появиться результат;

• нажмите «Автосумма» — Excel предложит диапазон для сложения;

• если диапазон указан верно, нажмите Enter, если нет — выделите нужные ячейки и подтвердите ввод.

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

Типы формул в Excel

Формулы в Excel могут быть разные — от простых вычислений до сложных конструкций с вложенными функциями. Разберём основные типы.

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

Это базовые выражения, выполняющие простые расчёты. Они состоят из чисел, операторов и ссылок на ячейки. Например:

• =A1+A2 — сложение двух значений;

• =B3*10 — умножение на число;

• =C5/2 — деление.

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

Сложные формулы

Здесь уже задействовано несколько операций, и важно учитывать приоритет действий. Например:

• =A1+B1*C1 — Excel сначала умножит B1 на C1, а затем прибавит A1.

• =(A1+B1)*C1 — скобки меняют порядок: сначала сложение, потом умножение. Использование скобок помогает избежать ошибок и сделать расчёты предсказуемыми.

Комбинированные формулы

Это выражения, в которых используются несколько функций или логические проверки. Например, =ЕСЛИ(A1>B1;"Много";"Мало") — проверяет значение и возвращает нужный текст. Такие формулы позволяют автоматизировать сложные расчёты и анализ данных.

Встроенные функции

Excel содержит сотни функций для работы с числами, текстом, датами и логикой. Они записываются в виде =ФУНКЦИЯ(аргументы). Например:

• =СУММ(A1:A10) — сумма;

• =СРЗНАЧ(B1:B10) — среднее;

• =СЕГОДНЯ() — текущая дата и время.

Функции позволяют ускорить работу и минимизировать ошибки при расчётах.

Основные функции Excel

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

СУММ (SUM) — сложение чисел. Складывает все числа в указанном диапазоне. Вместо того чтобы писать =A1+A2+A3+A4, проще использовать =СУММ(A1:A4).

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

• =СУММ(A1:A10) — складывает все числа в диапазоне A1:A10;

• =СУММ(A1, A3, A5) — суммирует только выбранные ячейки.

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

СРЗНАЧ (AVERAGE) — вычисление среднего значения группы чисел. 

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

• =СРЗНАЧ(B1:B10) — найдёт среднее арифметическое всех чисел в диапазоне;

• =СРЗНАЧ(A1, A3, A5) — посчитает среднее только для указанных ячеек.

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

ЕСЛИ (IF) — проверка условия. Позволяет задать условие и выполнить разные действия в зависимости от результата.

Как это работает: =ЕСЛИ(A1>100;"Превышение";"Норма") — если в A1 число больше 100, покажет «Превышение», иначе — «Норма».

Пригодится для автоматической классификации данных — например, прошёл ли студент экзамен или нет. Или для выявления ошибок в таблице.

СУММЕСЛИ (SUMIF) — суммирование по условию. Складывает только те числа, которые соответствуют определённому критерию.

Как это работает: =СУММЕСЛИ(A1:A10;">200") — сложит только числа больше 200.

Пригодится для подсчёта продаж по высокочеку и анализа расходов по категориям.

МАКС (MAX) и МИН (MIN) — функции находят максимальное и минимальное значение в диапазоне.

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

• =МАКС(A1:A10) — покажет наибольшее число;

• =МИН(B1:B20) — найдёт наименьшее значение.

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

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

Работа с ячейками и диапазонами

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

Ссылки на ячейки

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

Относительные ссылки (A1) используются, когда формулу нужно применять к разным строкам и столбцам. А ещё они позволяют копировать формулы без необходимости менять адреса ячеек вручную. При копировании они меняются относительно новой позиции: =A1+B1 в строке 2 превратится в =A2+B2.

Абсолютные ссылки ($A$1) фиксируют конкретную ячейку, чтобы при копировании формулы ссылка оставалась неизменной: =$A$1+B1 всегда будет использовать значение из A1.

Смешанные ссылки (A$1 или $A1) фиксируют либо строку, либо столбец и удобны в сложных таблицах, где часть данных должна изменяться, а часть — нет. Пример: =$A1+B1 при копировании вниз изменит только номер строки ($A2+B2).

Использование констант

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

• Прямо в формуле: =A1*1.2, где 1.2 — константа (например, налог или процент). Однако если ставка увеличится или уменьшится, придётся всё исправлять вручную.

• В отдельной ячейке: =A1*$B$1, где B1 содержит коэффициент. Если значение изменится, пересчитывать ничего не нужно.

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

Правильная работа со ссылками и константами делает формулы гибкими и удобными в масштабировании.

Ошибки при работе с формулами

При работе в Excel ошибки случаются часто. Разберём самые распространённые из них и способы их исправления.

#ДЕЛ/0! — деление на ноль

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

Как исправить: проверить, что в знаменателе есть число и/или использовать ЕСЛИ: =ЕСЛИ(B1=0; "Ошибка"; A1/B1).

#ИМЯ? — ошибка в названии функции или ссылки

Появляется, когда есть ошибка в написании имени функции (СУМ вместо СУММ), ссылка на несуществующую ячейку или формула введена без «=» в начале.

Как исправить: проверить название функции и ссылку. Убедиться, что перед формулой стоит «=».

#ЗНАЧ! — несовместимые типы данных

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

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

#ССЫЛКА! — ошибка в адресе ячейки

Возникает, когда формула ссылается на удалённую ячейку.

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

#ЧИСЛО! — неверное числовое значение

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

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

###### — столбец слишком узкий

Возникает, если число или дата не помещаются в ячейку.

Как исправить: расширить столбец (двойной клик по границе заголовка).

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

Если вы до сих пор думаете, что Excel — это просто таблицы, посмотрите на вакансии: в 111 000+ из них требуется профессиональное владение этой программой. Хотите быстрее справляться с задачами, автоматизировать рутину и зарабатывать больше? Тогда вам нужно владеть Excel на уровне эксперта. 

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

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