13 Апр 2022
10 мин
49723

Как работать с формулами в Excel

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

Содержание

Формулы в таблицах Excel используют, когда данных очень много. Например, чтобы посчитать сумму нескольких чисел быстрее, чем на калькуляторе. Преимуществ много, поэтому работодатели часто указывают эту программу в требованиях. В конце ноября 2023 года больше 110 000 вакансий на хедхантере содержали формулировки вроде «уверенный пользователь Excel», «работа с формулами в Excel».

Кому важно знать Excel и где выучить основы

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

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

На онлайн-курсе Skypro «Аналитик данных» научитесь владеть базовыми формулами Excel, работать с нестандартными данными, статистикой. Кроме Excel вы изучите Metabase, SQL, Power BI, язык программирования Python. Программа подойдет даже тем, у кого совсем нет опыта в анализе и кто не любит математику. Вас ждут живые вебинары, мастер-классы, домашки с разбором, помощь наставников.

Курс «Аналитик данных»

Урок из курса «Аналитик данных» в Skypro

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

Основные знаки:

= с него начинают любую формулу;

( ) заключают формулу и ее части;

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

: ставят, чтобы обозначить диапазон ячеек, а не выбирать всё подряд вручную.

В таблицах Excel работают с простыми математическими действиями:

сложением +

вычитанием —

умножением *

делением /

возведением в степень ^

Еще в таблицах Excel используют символы сравнения:

равенство =

меньше <

больше >

меньше либо равно <=

больше либо равно >=

не равно <>

Основные виды

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

Многие помнят их с уроков информатики в школе. Но даже если вы совсем не знакомы с таблицами Excel, на курсе Skypro «Аналитик данных» обучение начинают с основ. Не нужны глубокие знания математики и навыки работы с программами для анализа данных.

Простые

Применяют, когда нужно совершить одно простое действие, например сложить или умножить.

СУММ. Складывает несколько чисел. Сумму можно посчитать для нескольких ячеек или целого диапазона в таблице.

=СУММ(А1;В1) — для соседних ячеек;

=СУММ(А1;С1;H1) — для определенных ячеек;

=СУММ(А1:Е1) — для диапазона.

Как найти сумму всех ячеек в excel

Сумма всех чисел в ячейках от А1 до Е1

ПРОИЗВЕД. Умножает числа в соседних, выбранных вручную ячейках или диапазоне таблицы.

=ПРОИЗВЕД(А1;В1)

=ПРОИЗВЕД(А1;С1;H1)

=ПРОИЗВЕД(А1:Е1)

Как найти произведение ячеек в Excel

Произведение всех чисел в ячейках от А1 до Е1

ОКРУГЛ. Округляет дробное число до целого в большую или меньшую сторону. Укажите ячейку таблицы с нужным числом, в качестве второго значения — 0.

=ОКРУГЛВВЕРХ(А1;0) — к большему целому числу;

=ОКРУГЛВНИЗ(А1;0) — к меньшему.

Как округлить до целого числа в Excel

Округление в меньшую сторону

ВПР. Находит данные в таблице или определенном диапазоне.

=ВПР(С1;А1:В6;2)
  • С1 — ячейка, в которую выписывают известные данные. В примере это код цвета.
  • А1 по В6 — диапазон ячеек в таблице. Ищем название цвета по коду.
  • 2 — порядковый номер столбца таблицы для поиска. В нём указаны названия цвета.
Как создать функцию ВПР в Excel

Формула вычислила, какой цвет соответствует коду

СЦЕПИТЬ. Объединяет данные диапазона ячеек таблицы, например текст или цифры. Между содержимым ячеек можно добавить пробел, если объединяете слова в предложения.

=СЦЕПИТЬ(А1;В1;С1) — текст без пробелов;

=СЦЕПИТЬ(А1;» «;В1;» «С1) — с пробелами.

Как сцепить ячейки в Excel

Формула объединила три слова в одно предложение

КОРЕНЬ. Вычисляет квадратный корень числа в ячейке таблицы.

=КОРЕНЬ(А1)
Как извлечь корень в Excel

Квадратный корень числа в ячейке А1

ПРОПИСН. Преобразует текст в верхний регистр, то есть делает буквы заглавными.

=ПРОПИСН(А1:С1)
Как изменить регистр текста в Excel

Формула преобразовала строчные буквы в прописные

СТРОЧН. Переводит текст в нижний регистр, то есть делает из больших букв маленькие.

=СТРОЧН(А2)

СЧЕТ. Считает количество ячеек таблицы с числами.

=СЧЕТ(А1:В5)
Как сделать подсчет в Excel

Формула вычислила, что в диапазоне А1:В5 четыре ячейки с числами

СЖПРОБЕЛЫ. Убирает лишние пробелы. Например, когда переносите текст из другого документа и сомневаетесь, правильно ли там стоят пробелы.

=СЖПРОБЕЛЫ(А1)
Как удалить двойные пробелы в Excel

Формула удалила двойные и тройные пробелы

Сложные

ПСТР. Выделяет определенное количество знаков в тексте, например одно слово.

=ПСТР(А1;9;5)
  1. Введите =ПСТР.
  2. Кликните на ячейку таблицы, где нужно выделить знаки.
  3. Укажите номер начального знака: например, с какого символа начинается слово. Пробелы тоже считайте.
  4. Поставьте количество знаков, которые нужно выделить из текста. Например, если слово состоит из пяти букв, впишите цифру 5.
Как использовать функцию ПСТР в Excel

В ячейке А1 формула выделила 5 символов, начиная с 9-го

ЕСЛИ. Анализирует данные по условию. Например, когда в таблице нужно сравнить одно с другим.

=ЕСЛИ(A1>25;"больше 25";"меньше или равно 25")

В формуле указали:

  • А1 — ячейку с данными;
  • >25 — логическое выражение;
  • больше 25, меньше или равно 25 — истинное и ложное значения.

Первый результат возвращается, если сравнение истинно. Второй — если ложно.

Как использовать функцию ЕСЛИ в Excel

Число в А1 больше 25. Поэтому формула показывает первый результат — больше 25.

СУММЕСЛИ. Складывает числа, которые соответствуют критерию. Обычно критерий — числовой промежуток или предел.

=СУММЕСЛИ(В2:В5;">10")

В формуле указали:

  • В2:В5 — диапазон ячеек таблицы;
  • >10 — критерий, то есть числа меньше 10 не будут суммироваться.
Как использовать функцию СУММЕСЛИ в Excel

Число 8 меньше указанного в условии, то есть 10. Поэтому оно не вошло в сумму.

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

=СУММЕСЛИМН(D2:D6;C2:C6;"сувениры";B2:B6;"ООО ХY")
  • D2:D6 — диапазон, из которого суммируем числа;
  • C2:C6 — диапазон ячеек таблицы для категории;
  • сувениры — условие, то есть числа другой категории учитываться не будут;
  • B2:B6 — диапазон ячеек таблицы для компании;
  • ООО XY — условие, то есть числа другой компании учитываться не будут.
Как использовать функцию СУММЕСЛИМН в Excel

Под условия подошли только ячейки D3 и D6: их сумму и вывела формула

Комбинированные

В  таблицах Excel можно комбинировать несколько функций: сложение, умножение, сравнение и другие. Например, вам нужно найти сумму двух чисел. Если значение больше 65, сумму нужно умножить на 1,5. Если меньше — на 2.

=ЕСЛИ(СУММ(A1;B1)<65;СУММ(A1;B1)*1,5;(СУММ(A1;B1)*2))

То есть если сумма двух чисел в А1 и В1 окажется меньше 65, программа посчитает первое условие — СУММ(А1;В1)*1,5. Больше 65 — Excel задействует второе условие — СУММ(А1;В1)*2.

Как использовать несколько функций в Excel

Сумма в А1 и В1 больше 65, поэтому формула посчитала по второму условию: умножила на 2

Встроенные

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

  1. Поместите курсор в нужную ячейку таблицы.
  2. Откройте диалоговое окно мастера: нажмите клавиши Shift + F3. Откроется список функций.
  3. Выберите нужную формулу. Нажмите на нее, затем на «ОК». Откроется окно «Аргументы функций».
  4. Внесите нужные данные. Например, числа, которые нужно сложить.
Как вставить формулу в Excel

Ищите формулу по алфавиту или тематике, выбирайте любую из тех, что использовали недавно

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

Как скопировать

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

  1. Впишите функцию в ячейку таблицы и кликните на нее.
  2. Наведите курсор на правый нижний угол — курсор примет форму креста.
  3. Нажмите левую кнопку мыши, удерживайте ее и тяните до нужной ячейки таблицы.
  4. Отпустите кнопку. Появится итог.
Как скопировать функцию Excel

Посчитали сумму ячеек в трех строках

Как обозначить постоянную ячейку

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

  1. Нажмите на ячейку таблицы с формулой.
  2. Поместите курсор в нужную ячейку таблицы и нажмите F4.
  3. В формуле фрагмент с описанием ячейки таблицы приобретет вид $A$1. Если вы протянете формулу, то ссылка на ячейку $A$1 останется на месте.

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

Когда нужна не формула, а данные, например +10 °С:

  1. Кликните правой кнопкой по ячейке таблицы.
  2. Выберите «Формат ячеек».
  3. Отметьте «Текстовый», нажмите «ОК».
  4. Поставьте = или +, затем нужное число.
  5. Нажмите Enter.

Как сделать фильтр по столбцам в Google Sheets

Фильтрация данных — это процесс выделения из набора строк и столбцов только тех, которые подходят определенным, заранее установленным условиям. Благодаря этому пользователям проще анализировать конкретные части данных и работать с ними.

Представим, что перед нами задача — собрать данные о клиентах: возраст до тридцати лет, из Москвы и Казани. Для этого нужно будет поработать с фильтром.

Таблица Excel. Работа с фильтром

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

Дальше точно так же кликните по выпадающему списку столбца «Возраст» и выберите опцию «Фильтровать по условию». Выберите в списке «Меньше или равно», а затем выставьте значение «30». Нажмите «ОК».
Если нужно вернуть исходный вид данных, можно сбросить настройки таблицы. Для этого перейдите по значку фильтра.
В онлайн-университете Skypro вы можете за пару месяцев пройти курс по Excel и получить востребованный навык для аналитики. Программа курса состоит из важнейших блоков: первичная обработка информации, работа со сложными данными и прогнозирование. Еще вы создадите проект по анализу бизнес-метрик.

Как создать сводную таблицу в Google Sheets

С помощью сводной таблицы можно объединять данные из разных таблиц и листов, чтобы посчитать общее значение.

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

Сводная таблица в Google Sheets

Выделяем ячейку А1. Так программа поймет, c какими значениями нужно работать.

В верхнем меню «Вставка» выбираем «Создать сводную таблицу».

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

Сводная таблица в Google Sheets

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

Главное о формулах в Excel

  • Формула состоит из математических знаков. Чтобы ее вписать, используют символы = ( ) ; : .
  • С помощью простых формул числа складывают, умножают, округляют, извлекают из них квадратный корень. Чтобы отредактировать текст, используют формулы поиска, изменения регистра, удаления лишних пробелов.
  • Сложные и комбинированные формулы помогают делать объемные вычисления, когда нужно соблюдать несколько условий.
  • Значительно упростит жизнь аналитика возможность создавать сводную таблицу для расчетов и фильтровать данные по столбцам

Содержание

Добавить комментарий

Выиграть iPhone 15