Урок 5. Формулы Excel

Формулы суммы

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

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

1. Напишите формулу =СУММ() в свободной ячейке.

2. Укажите в круглых скобках ячейки, которые нужно просуммировать. Перечислите их через точку с запятой или знак плюс =СУММ(B3;B4;B5). А ещё вы можете выделить нужные ячейки мышкой, зажав Ctrl/Cmd.

3. Если вам нужно просуммировать значения из целого диапазона, укажите его начальную и конечную ячейку через двоеточие =СУММ(B3:B5) или выделите его в таблице.

4. Для суммирования нескольких диапазонов перечислите их через точку с запятой, так же как и ячейки: =СУММ(B3:B5;C3:C5).
5. Нажмите Enter.

Функцию «Автосуммы» можно использовать вместо прописывания формулы вручную. Для этого:

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

3. Проверьте, корректна ли формула и какие в неё попали значения. Нажмите Enter.

Сумма при условии

Чтобы использовать эту формулу:

1. Выберите ячейку для суммирования и напишите формулу =СУММЕСЛИМН().
2. Выберите диапазон ячеек для суммирования.
3. Выберите диапазон условия, в котором эта формула будет искать необходимые значения.
4. Выберите условие, по которому Excel будет отбирать аргументы суммирования.
5. Нажмите Enter.

1.55-3.02

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

1. Кликните по ячейке с формулой.
2. В самой формуле выделите мышкой те значения, которые должны остаться неизменными.
3. Нажмите F4 — перед названием ячейки появится значок доллара. Если нужно закрепить часть формулы, например число, то лучше ставить знак доллара вручную с помощью Shift +4, не прибегая к F4.

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

4. Нажмите Enter.

3.05-4.27

Промежуточные итоги

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

1. Выберите ячейку для суммы и напишите формулу =ПРОМЕЖУТОЧНЫЕ.ИТОГИ().
2. Выберите номер нужной функции.
3. Выберите диапазон или отдельные ячейки, к которым необходимо применить формулу, → нажмите Enter.
4. Примените фильтр, чтобы проверить, работает ли формула.

5.00-5.59

Чтобы использовать формулу:

1. Выберите ячейку, в которой будет вестись счёт ячеек, содержащих числа.
2. Пропишите в ней формулу =СЧЁТ().
3. Выберите нужный диапазон.

Счёт с условиями

Чтобы применить формулу:

1. Выберите ячейку для вывода результатов и напишите формулу =СЧЁТЕСЛИ().
2. Укажите диапазон для подсчёта.
3. Через точку с запятой и в кавычках укажите условие — значение, которое должны содержать ячейки для подсчёта.

Счёт с несколькими условиями

Чтобы применить формулу:

1. Выберите ячейку для вывода результатов и введите формулу =СЧЁТЕСЛИМН().
2. Укажите диапазон для подсчёта.
3. Через точку с запятой и в кавычках укажите условие — значение для определения ячеек, которые формула будет считать.
4. Поставьте точку с запятой и укажите второй диапазон.
5. Через точку с запятой и в кавычках укажите второе условие для второго диапазона.
6. Нажмите Enter.

Логические формулы

Формулы обработки текста

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

1. Пропишите формулу =ДЛСТР() и укажите нужную ячейку.
2. Нажмите Enter.

Формула может быть полезна для работы со скопированным текстом, который содержит лишние пробелы. Чтобы использовать её:

1. Пропишите формулу =СЖПРОБЕЛЫ() и укажите нужную ячейку.
2. Нажмите Enter.

Формула замены при ошибке

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

Чтобы избежать ошибок:

1. Скопируйте исходную формулу.
2. В этой же ячейке введите =ЕСЛИОШИБКА().
3. В скобках укажите скопированную исходную формулу.
4. Через точку с запятой укажите значение, которое нужно использовать в случае возникновения ошибки.
5. Нажмите Enter.

10.56-11.22

Формула для вертикального переноса данных

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

Рассмотрим её использование на примере. Допустим, вам нужно соотнести данные по продажам и по кликам из отчётов CRM и Яндекс Директа по каждой кампании.

1. Создайте документ и импортируйте туда оба отчёта в разные листы.
2. На листе Директа создайте новый столбец для данных по продажам.

3. Выделите ячейку, в которой будут данные из другой таблицы, напишите формулу =ВПР().

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

   В этом случае искомым значением будет название кампании.

5. Нажмите кнопку функции f(x) возле формулы над таблицей. Откроется окно «Аргументы функции».

6. Кликните по полю «Таблица», откройте лист со второй таблицей, откуда вы хотите перенести данные, и выделите её.

7. Кликните по полю «Номер_столбца». Укажите номер столбца во второй таблице, данные из которого нужно импортировать. Столбец А будет номером 1, столбец B — 2 и так далее. В этом примере нужны данные по продажам из столбца B, то есть 2. Нажмите «ОК».

8. Зафиксируйте выбранный диапазон с помощью значков $ — проставьте его вручную.
9. Протяните формулу вниз.

Задание

Потренируйтесь использовать разные функции Excel в своих документах.

1. Просуммируйте показатели с помощью формулы «СУММ».
2. Посчитайте количество ячеек с определённым значением с помощью формулы счёта:
• Напишите формулу =СЧЁТЕСЛИ()
• Укажите диапазон для подсчёта
• Через точку с запятой и в кавычках укажите условие — значение, которое должны содержать ячейки для подсчёта формулой → нажмите Enter
3. С помощью формул обработки текста уберите лишние пробелы и посчитайте количество знаков в тексте:

   «Спешите приобрести новую вышивальную машину „Матрикс 10“, которая обладает уникальными возможностями и работает в семь раз быстрее промышленного аналога. При этом машинка выдаёт качественную стёжку, сравнимую с ручной работой. Торопитесь, тест-драйв уже скоро».

4. С помощью формулы «ВПР» перенесите данные из одной таблицы в другую:
• Объедините данные двух таблиц в одной на разных листах
• Убедитесь, что в таблицах есть похожий столбец для соотнесения данных
• Создайте столбец для импорта данных в одной из таблиц, в ячейке пропишите =ВПР()
• На этом же листе выберите ячейку, по значению которой формула будет соотносить данные из второй таблицы
• Нажмите на кнопку функции f(x) возле формулы над таблицей
• В окне «Аргументы функции» кликните по полю «Таблица», откройте лист со второй таблицей, откуда вы хотите перенести данные, и выделите её
• Кликните по полю «Номер_столбца», укажите во второй таблице номер столбца, данные из которого нужно импортировать
• Нажмите «ОК»
• Зафиксируйте значения диапазона в формуле с помощью значков $
• Протяните формулу вниз по столбцу

Выводы

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

В следующем блоке рассмотрим, как работать со сводными таблицами в Excel.