Десять формул в Excel, которые облегчат вам жизнь
Сегодня рабочий мир невозможно представить без электронных таблиц. Excel от Microsoft — по-прежнему один из самых мощных инструментов для работы с данными, анализа и расчётов. В этой статье мы рассмотрим десять полезных формул в Excel, которые можно использовать в самых разных рабочих задачах.
Любите работать с данными?
Excel — лишь один из инструментов анализа данных. На курсе «Аналитик данных» от Changellenge >> Education вы сможете освоить ключевые навыки дата-аналитика и подготовиться к старту в этой профессии за 8 месяцев
СЧЁТ
Функция СЧЁТ позволяет подсчитывать количество числовых значений в диапазоне ячеек. Это полезный инструмент для анализа больших объемов данных, когда нужно быстро определить количество чисел в таблице. Например, если вы менеджер по продажам и хотите узнать, сколько сделок было заключено за последний месяц, функция СЧЁТ поможет вам быстро справиться с этой задачей:
=СЧЁТ(A1:A100)
Функция СЧЁТ требует один аргумент — диапазон ячеек, в котором нужно подсчитать числовые значения. Этот пример подсчитает количество чисел в диапазоне A1: А100, где указаны все сделки за месяц.
ЕСЛИ
Функция ЕСЛИ помогает выполнять логические проверки в таблицах. Она позволяет задавать условия и возвращать разные значения в зависимости от выполнения этих условий. Например, если вы аналитик, который анализирует производительность сотрудников, и хотите определить, кто прошел тестирование. В столбце А, с 1 по 70 строку у вас есть данные по сотрудникам, кто прошел тестирование и их баллы за экзамен. Пусть проходной балл тестирования — 71. Тогда вы можете использовать следующую формулу:
=ЕСЛИ(A1>70; "Прошел"; "Не прошел")
Функция ЕСЛИ требует три аргумента:
1. Условие (A1>70) — если значение в ячейке A1 больше 70.
2. Значение, если условие выполнено («Прошел»).
3. Значение, если условие не выполнено («Не прошел»).
Эта формула проверяет значение ячейки A1 и возвращает «Прошел», если значение больше 70, и «Не прошел», если нет. Если значение в ячейке A1 будет 72, формула вернет «Прошел». Если значение в ячейке A1 будет 70, формула вернет «Не прошел».
1. Условие (A1>70) — если значение в ячейке A1 больше 70.
2. Значение, если условие выполнено («Прошел»).
3. Значение, если условие не выполнено («Не прошел»).
Эта формула проверяет значение ячейки A1 и возвращает «Прошел», если значение больше 70, и «Не прошел», если нет. Если значение в ячейке A1 будет 72, формула вернет «Прошел». Если значение в ячейке A1 будет 70, формула вернет «Не прошел».
СЧЁТЕСЛИМН
Функция СЧЁТЕСЛИМН используется для подсчета ячеек, соответствующих нескольким критериям. Это особенно полезно, когда необходимо учитывать несколько условий при анализе данных. Допустим, вы работаете в отделе кадров и хотите узнать, сколько сотрудников соответствует определенным критериям: возраст старше 30 лет и стаж работы более 5 лет. Ваша таблица содержит возраст в столбце A и стаж в столбце B:
=СЧЁТЕСЛИМН(A1:A100; ">30"; B1:B100; ">5")
Функция СЧЁТЕСЛИМН требует пары аргументов — диапазон и условие. В данном случае:
1. Диапазон A1 с условием «>30».
2. Диапазон B1 с условием «>5».
Эта формула подсчитает количество сотрудников, где значения в диапазоне A1 больше 30 и одновременно значения в диапазоне B1 больше 5. Если возраст сотрудника в ячейке A1 — 35, а стаж в ячейке B1 — 6 лет, то эта пара будет учтена. Если возраст сотрудника — 28 лет или стаж — 4 года, эта пара не будет учтена.
1. Диапазон A1 с условием «>30».
2. Диапазон B1 с условием «>5».
Эта формула подсчитает количество сотрудников, где значения в диапазоне A1 больше 30 и одновременно значения в диапазоне B1 больше 5. Если возраст сотрудника в ячейке A1 — 35, а стаж в ячейке B1 — 6 лет, то эта пара будет учтена. Если возраст сотрудника — 28 лет или стаж — 4 года, эта пара не будет учтена.
СУММЕСЛИМН
СУММЕСЛИМН позволяет суммировать значения ячеек, соответствующих нескольким критериям. Это мощный инструмент для анализа данных и создания отчетов.
Например, если вы аналитик, который анализирует продажи, и хотите узнать общую сумму продаж для клиентов старше 30 лет, которые сделали более 3 покупок, используйте следующую формулу. Возраст клиентов находится в столбце A, количество покупок в столбце B и суммы покупок в столбце C:
Например, если вы аналитик, который анализирует продажи, и хотите узнать общую сумму продаж для клиентов старше 30 лет, которые сделали более 3 покупок, используйте следующую формулу. Возраст клиентов находится в столбце A, количество покупок в столбце B и суммы покупок в столбце C:
=СУММЕСЛИМН(C1:C100; A1:A100; ">30"; B1:B100; ">3")
Функция СУММЕСЛИМН требует четыре аргумента:
1. Диапазон для суммирования (C1:С100).
2. Диапазон с условием (A1:А100) и само условие («>30»).
3. Диапазон с условием (B1:В100) и само условие («>3»).
Эта формула суммирует значения в диапазоне C1, если соответствующие значения в A1 больше 30 и в B1 больше 3. Если возраст клиента в ячейке A1 — 32 года, количество покупок в ячейке B1 — 4, а сумма продаж в ячейке C1 — 5000, то эти 5000 будут учтены. Если возраст клиента — 28 лет или количество покупок — 2, эта запись не будет учтена.
1. Диапазон для суммирования (C1:С100).
2. Диапазон с условием (A1:А100) и само условие («>30»).
3. Диапазон с условием (B1:В100) и само условие («>3»).
Эта формула суммирует значения в диапазоне C1, если соответствующие значения в A1 больше 30 и в B1 больше 3. Если возраст клиента в ячейке A1 — 32 года, количество покупок в ячейке B1 — 4, а сумма продаж в ячейке C1 — 5000, то эти 5000 будут учтены. Если возраст клиента — 28 лет или количество покупок — 2, эта запись не будет учтена.
ВПР
Функция ВПР (Вертикальный просмотр) используется для поиска значений в таблице по заданному ключу.
Например, если вы работаете с большой таблицей данных и вам нужно найти информацию о конкретном клиенте по его ID, функция ВПР значительно упростит эту задачу. Допустим, у вас есть таблица с данными о клиентах, где в столбце A указаны ID клиентов, а в столбце B — их имена. Чтобы найти имя клиента по его ID (101), используйте следующую формулу:
Например, если вы работаете с большой таблицей данных и вам нужно найти информацию о конкретном клиенте по его ID, функция ВПР значительно упростит эту задачу. Допустим, у вас есть таблица с данными о клиентах, где в столбце A указаны ID клиентов, а в столбце B — их имена. Чтобы найти имя клиента по его ID (101), используйте следующую формулу:
=ВПР(101; A1:B10; 2; ЛОЖЬ)
Функция ВПР требует четыре аргумента:
1. Искомое значение (101) — значение, которое нужно найти в первом столбце указанного диапазона.
2. Диапазон для поиска (A1:В10) — область, где производится поиск. Первый столбец этого диапазона используется для поиска ключа (в нашем случае — 101), а столбцы справа содержат значения, которые будут возвращены.
3. Номер столбца (2) — номер столбца в указанном диапазоне, из которого нужно вернуть значение. В данном случае это второй столбец.
4. Логическое значение (ЛОЖЬ) — указывает на точное (ЛОЖЬ) или приблизительное (ИСТИНА) совпадение. В данном случае мы ищем точное совпадение.
Эта формула ищет значение 101 в первом столбце диапазона A1 и возвращает значение из второго столбца.
У вас есть таблица, где:
Используя формулу =ВПР (101; A1: B10; 2; ЛОЖЬ), вы найдете имя клиента с ID 101. Если в ячейке A2 находится значение 101, а в ячейке B2 — Иванов, то формула вернет «Иванов».
Комментарий от Марии Черепановой, ведущего аналитика в компании Sumsub (UK) и эксперта программ Changellenge >> Education:
1. Искомое значение (101) — значение, которое нужно найти в первом столбце указанного диапазона.
2. Диапазон для поиска (A1:В10) — область, где производится поиск. Первый столбец этого диапазона используется для поиска ключа (в нашем случае — 101), а столбцы справа содержат значения, которые будут возвращены.
3. Номер столбца (2) — номер столбца в указанном диапазоне, из которого нужно вернуть значение. В данном случае это второй столбец.
4. Логическое значение (ЛОЖЬ) — указывает на точное (ЛОЖЬ) или приблизительное (ИСТИНА) совпадение. В данном случае мы ищем точное совпадение.
Эта формула ищет значение 101 в первом столбце диапазона A1 и возвращает значение из второго столбца.
У вас есть таблица, где:
- A2 содержат ID клиентов (например, 101, 102, 103 и т. д.),
- B2 содержат имена клиентов (например, Иванов, Петров, Сидоров и т. д.).
Используя формулу =ВПР (101; A1: B10; 2; ЛОЖЬ), вы найдете имя клиента с ID 101. Если в ячейке A2 находится значение 101, а в ячейке B2 — Иванов, то формула вернет «Иванов».
Комментарий от Марии Черепановой, ведущего аналитика в компании Sumsub (UK) и эксперта программ Changellenge >> Education:
Функция ВПР (или VLOOKUP на английском) достаточно часто используется в аналитике данных. В комбинации с функцией ЕСЛИ (IF на английском) можно создавать условные вычисления, которые позволяют более гибко и точно обрабатывать данные. Можно использовать ЕСЛИ для проверки наличия значения в таблице перед использованием ВПР, чтобы избежать ошибок. Например, установить критерий, что искать данные по выручке по конкретному продукту (ВПР) нужно только ЕСЛИ этот продукт входит в портфель конкретного подразделения. Совместное использование этих функций значительно повышают эффективность работы в Excel.
ИНДЕКС
Функция ИНДЕКС возвращает значение ячейки, находящейся на пересечении заданного ряда и столбца в указанном диапазоне.
Допустим, у нас есть таблица, где столбцы представляют месяцы, а строки — категории товаров. Таблица выглядит так:
Допустим, у нас есть таблица, где столбцы представляют месяцы, а строки — категории товаров. Таблица выглядит так:
=ИНДЕКС(A2:C4; 2; 2)
Функция ИНДЕКС требует три аргумента:
1. Диапазон ячеек (A2:С4).
2. Номер строки (2).
3. Номер столбца (2).
Формула =ИНДЕКС (A2:C4; 2; 2) возвращает значение 200, которое находится на пересечении второй строки и второго столбца.
Таким образом, функция ИНДЕКС позволяет точно извлекать данные из таблицы, основываясь на известной позиции строки и столбца, что особенно полезно в случаях, когда данные организованы в предсказуемом порядке, и не требуется полагаться на ключевые значения, как в функции ВПР.
1. Диапазон ячеек (A2:С4).
2. Номер строки (2).
3. Номер столбца (2).
Формула =ИНДЕКС (A2:C4; 2; 2) возвращает значение 200, которое находится на пересечении второй строки и второго столбца.
Таким образом, функция ИНДЕКС позволяет точно извлекать данные из таблицы, основываясь на известной позиции строки и столбца, что особенно полезно в случаях, когда данные организованы в предсказуемом порядке, и не требуется полагаться на ключевые значения, как в функции ВПР.
Используем функцию ИНДЕКС.
1
2
3
4
A
B
C
Январь
100
150
200
Февраль
200
250
300
Март
300
350
400
ПОИСКПОЗ
Функция ПОИСКПОЗ используется для поиска позиции элемента в диапазоне. Она часто используется вместе с функцией ИНДЕКС для создания сложных вычислений.
Предположим, у вас есть список продуктов и их идентификаторы. Ваша задача — найти позицию конкретного продукта (апельсинов) в списке, чтобы определить его местоположение.
Предположим, у вас есть список продуктов и их идентификаторы. Ваша задача — найти позицию конкретного продукта (апельсинов) в списке, чтобы определить его местоположение.
=ПОИСКПОЗ("Апельсины"; A1:A10; 0)
Функция ПОИСКПОЗ требует три аргумента:
1. Искомое значение («Апельсины»).
2. Диапазон для поиска (A1:А10).
3. Тип соответствия (0 означает точное соответствие).
Эта формула возвращает позицию «Искомого значения» в диапазоне A1: А10. Если в списке апельсины располагались на третьей позиции, то формула вернет значение 3.
Комментарий от Дениса Хайретдинова, заместителя директора департамента цифровой трансформации — начальника отдела цифровой стратегии (Росатом) и эксперта программ Changellenge >> Education:
1. Искомое значение («Апельсины»).
2. Диапазон для поиска (A1:А10).
3. Тип соответствия (0 означает точное соответствие).
Эта формула возвращает позицию «Искомого значения» в диапазоне A1: А10. Если в списке апельсины располагались на третьей позиции, то формула вернет значение 3.
Комментарий от Дениса Хайретдинова, заместителя директора департамента цифровой трансформации — начальника отдела цифровой стратегии (Росатом) и эксперта программ Changellenge >> Education:
При работе с финансовыми моделями (особенно, если там имеются длинные перечни — контракты, покупатели, различная нормативно-справочная информация по производству, штатное расписание и т. д.) я сам часто использую сочетание ИНДЕКС (ПОИСКПОЗ). Но если применять такие функции в большом количестве ячеек модели, это ее сильно утяжеляет, и производительность падает. Выходом является матричное умножение, которое можно реализовать через функцию СУММПРОИЗВ (), где аргументами будут массив, по которому производится отбор, и любое количество условий реализованных с помощью операторов «>», «=», «<» или их комбинаций. Таким образом, функция СУММПРОИЗВ () может заменить и ИНДЕКС (ПОИСКПОЗ ()), и СУММЕСЛИМН ().
СЦЕП
Функция СЦЕП объединяет текст из нескольких ячеек в одну строку. Это полезно для создания отчетов и форм, где требуется соединение данных. Например, если вам нужно объединить фамилию и имя сотрудников из двух разных ячеек, вы можете использовать следующую формулу:
=СЦЕП(A1; " "; B1)
Функция СЦЕП требует несколько аргументов, которые представляют собой текстовые строки или ссылки на ячейки. В данном случае это:
1. Значение из ячейки A1.
2. Пробел (" ").
3. Значение из ячейки B1.
Эта формула объединяет имя из ячейки A1 и фамилию из ячейки B1, поставив между ними пробел.
Комментарий от Марии Черепановой, ведущего аналитика в компании Sumsub (UK) и эксперта программ Changellenge >> Education:
1. Значение из ячейки A1.
2. Пробел (" ").
3. Значение из ячейки B1.
Эта формула объединяет имя из ячейки A1 и фамилию из ячейки B1, поставив между ними пробел.
Комментарий от Марии Черепановой, ведущего аналитика в компании Sumsub (UK) и эксперта программ Changellenge >> Education:
Функция СЦЕП (CONCAT на английском) особенно полезна при подготовке отчетов, когда для работы с данными требуется разделенная по ячейкам информация (например, страна — город — локация), а для некоторой аналитики нужна работа со сгруппированными данными (сколько продаж было в такой-то стране/городе/локации по месяцам). Помните о необходимости добавления пробелов в функцию для читабельного отображения данных, про них часто забывают. Иногда можно не прибегать к функции СЦЕП и для экономии времени просто использовать оператор & (пример: =A1 & B1). Также в случае объединения ячеек с разными форматами можно использовать ТЕКСТ, которая преобразует числа в текст для дальнейшего отображения — удобно для работы с датами или валютами.
ЛЕВСИМВ и ПРАВСИМВ
Функция ЛЕВСИМВ извлекает заданное количество символов с начала текста в ячейке. Это удобно для обработки текстовых данных. Например, если у вас есть список артикулов товаров, и вам нужно получить первые несколько символов каждого артикула, вы можете использовать следующую формулу:
=ЛЕВСИМВ(A1; 5)
Функция ЛЕВСИМВ требует два аргумента:
1. Текст или ссылка на ячейку (A1).
2. Количество символов для извлечения (5).
Эта формула извлекает первые 5 символов из текста в ячейке A1.
Функция ПРАВСИМВ аналогична ЛЕВСИМВ, но извлекает символы с конца текста. Она полезна для работы с текстовыми данными, когда нужно получить последние символы строки. Например, если у вас есть список кодов товаров, и вам нужно получить последние несколько символов каждого кода, вы можете использовать следующую формулу:
1. Текст или ссылка на ячейку (A1).
2. Количество символов для извлечения (5).
Эта формула извлекает первые 5 символов из текста в ячейке A1.
Функция ПРАВСИМВ аналогична ЛЕВСИМВ, но извлекает символы с конца текста. Она полезна для работы с текстовыми данными, когда нужно получить последние символы строки. Например, если у вас есть список кодов товаров, и вам нужно получить последние несколько символов каждого кода, вы можете использовать следующую формулу:
=ПРАВСИМВ(A1; 7)
Функция ПРАВСИМВ требует два аргумента:
1. Текст или ссылка на ячейку (A1).
2. Количество символов для извлечения (7).
Эта формула извлекает последние 7 символов из текста в ячейке A1.
Применение данных функции может быть полезно для различных задач. Например, для создания уникальных кодов товаров, где первые символы артикула могут содержать информацию о времени, месте или стране производства. Это особенно важно для логистики и управления товарными запасами, где важно быстро идентифицировать товары по определенным характеристикам, закодированным в их артикуле.
С этими формулами Excel вы легко решите задачи по автоматизации расчётов, сортировке данных и созданию отчетов. Они помогают быстрее обрабатывать большие объёмы информации и делать работу более продуктивной. Помните, что умение эффективно использовать функции Excel — это навык, который высоко ценится почти в любой сфере.
1. Текст или ссылка на ячейку (A1).
2. Количество символов для извлечения (7).
Эта формула извлекает последние 7 символов из текста в ячейке A1.
Применение данных функции может быть полезно для различных задач. Например, для создания уникальных кодов товаров, где первые символы артикула могут содержать информацию о времени, месте или стране производства. Это особенно важно для логистики и управления товарными запасами, где важно быстро идентифицировать товары по определенным характеристикам, закодированным в их артикуле.
С этими формулами Excel вы легко решите задачи по автоматизации расчётов, сортировке данных и созданию отчетов. Они помогают быстрее обрабатывать большие объёмы информации и делать работу более продуктивной. Помните, что умение эффективно использовать функции Excel — это навык, который высоко ценится почти в любой сфере.
Получите новую высокооплачиваемую профессию
На курсе «Аналитик данных» от Changellenge >> Education вы освоите ключевые навыки дата-аналитика и получите реальный опыт решения практических задач и бизнес-кейсов за 8 месяцев