Как работать с формулами в Excel и решать нестандартные рабочие задачи в два клика

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

Освоить базовые функции и формулы Excel можно по бесплатным YouTube-урокам и книгам, эти знания уже значительно ускорят подготовку отчётов. Например, быстро посчитать итоговую стоимость товаров — проданных книг. Для этого нужно выделить ячейки В (количество) и С (цена), нажать значок «Функции» → «ПРОИЗВЕД» и протянуть вниз за нижний уголок ячейки D.

С таблицами можно работать в Google Docs — там эта функция называется «PRODUCT»

С помощью формулы «МАКС» можно посмотреть, какая из книг продалась с максимальной прибылью. Для этого нужно ввести в ячейке формулу: =MAX(D2:D8).

МАКС — одна из статистических функций для анализа диапазона данных и поиска наибольшего числа

Работа с формулами в Excel невозможна без понимания элементов, из которых они состоят.

В Excel действуют математические законы. Как только пользователь ставит в ячейку «=», программа понимает, что в эту ячейку не просто впишут данные: будет задана конкретная формула. Например, запись «=2^3» означает, что двойку нужно возвести в третью степень.

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

Суммирование — одна из самых распространённых операций Excel. Её выполняют с помощью:

● Арифметических формул
=B2+C2+D2+E2+F2

● Автосуммы
=SUM(B2:B8)

● Функций
=SUM (B2+C2+D2+E2+F2)

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

1. Арифметическая формула

Щёлкнуть на ячейку G2, поставить знак «=». Перейти к В2, щёлкнуть левой кнопкой мыши, поставить знак «+» — по аналогии от С2 до F2. В G2 постепенно отображаются вводимые знаки и ячейки.

2. Автосумма

Кликнуть на ячейку G2 → выбрать значок «Функции» → «SUM» → кликнуть по ячейкам, которые нужно суммировать, — B2, C2, D2, E2, F2.

Автосумма в Excel упрощает процесс анализа данных и выполнения математических операций — автоматизирует его

3. Функция «СУММ»

Щёлкнуть на ячейку G2, поставить знак «=», вручную прописать «СУММ», открыть скобку, приплюсовать нужные ячейки — B2 + C2 + D2 + E2 + F2, закрыть скобку.

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

Эту формулу в Excel можно видоизменять. Допустим, нужно понять, на какой неделе конкретная книга продавалась лучше. Формула в этом случае будет такой:
=СУММ(B2+C2+D2+E2+F2)-СУММ(B12+C12+D12+E12+F12).

Формулы можно усложнять в зависимости от задачи

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

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

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

Для начала нужно объединить таблицы в одном Excel-файле — скопировать оба листа с данными.

Первый лист — здесь собраны планировки квартир и их стоимость

Второй лист — здесь собраны личные данные покупателей

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

Вот так работает формула ВПР

Сумма, произведение, округление — простые математические действия. К этой же категории относятся текстовые формулы: преобразование текста из обычного шрифта в заглавные буквы (=ПРОПИСН), удаление пробелов (=СЖПРОБЕЛЫ) и другие.

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

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

Функция =ЕСЛИ
Проверяет, выполняются ли заданные пользователем условия в определённом диапазоне таблицы.

Менеджер книжного магазина хочет понять, заказывать ли дополнительные экземпляры «‎Трилогии»‎ Юна Фоссе. Для этого нужно проанализировать текущие продажи. Если продали больше 3 000 книг, нужно заказать в два раза больше, если нет — столько же, сколько уже продано в данный момент.

Для ячейки B5 задаём первый параметр: «=IF (B5>3000)». Второй параметр: «значение, если истина» — «B5*2». Третий — «значение, если ложь» — «B5*1».

Формула проверяет, выполнено заданное в первой части условие или нет. Если да — отображает одно из двух прописанных значений.

В примере условие выполнено, книг куплено 3 010, значит, в ячейке появится число 6 020 — по формуле «B5*2»

Даже опытные пользователи Excel совершают ошибки в формулах — рассмотрим самые распространённые:

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

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

❌Использование неправильных функций. Ошибка возникает, если выбрана неправильная функция или неправильно указаны аргументы функции. Например, когда вместо функции «СРЕДН» для вычисления среднего значения используется функция «СУММ».

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

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

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

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

● Если формула не даëт ожидаемых результатов, с помощью функции «Отследить цепочку» можно проверить, какие ячейки используются в формуле, и исправить ошибки.