Когда простые формулы не помогают | Массивы функций | Операторы и формулы
Если не читали (https://ok.me/dxu71)первую часть (https://ok.me/exu71), то напомню что такое Массивы функций. Массив функций Excel позволяет решать сложные задачи в автоматическом режиме. Те, которые выполнить посредством обычных функций невозможно. Фактически это группа функций, которые одновременно обрабатывают группу данных и сразу выдают результат.
В этой статье рассмотрим Операторы и Функции.
Оператор массива И
Оператор И (*) возвращает значение ИСТИНА в случаях, когда все условия выражения возвращают значение ИСТИНА. Пример на картинке показывает его использование между массивами.
Оператор массива ИЛИ
Оператор ИЛИ (+) возвращает значение ИСТИНА, если хотя бы одно из условий выражения возвращает значение ИСТИНА. Пример на картинке показывает его использование между массивами.
Консолидация данных по более чем одному условию
Мы также можем использовать формулу массива для поиска суммы продаж менеджера с максимальными продажами. Функция ЕСЛИ возвращает массив отдельных сумм продаж менеджера совпадающего с менеджером с максимальными продажами, иначе 0. Затем мы используем функцию СУММ для суммирования всех этих значений массива.
Предположим, у вас есть набор данных в ячейках D2:D10, и вы хотите отсортировать его в порядке возрастания. Для этого понадобится функция НАИМЕНЬШИЙ(), а также диапазон, в котором мы будем производить вычисления. Обычная функция НАИМЕНЬШИЙ для одной ячейки выглядит так =НАИМЕНЬШИЙ(D2:D10;1). Необходимо скопировать эту функцию во все остальные ячейки и внести изменения во второй аргумент, чтобы получить отсортированный список. Для начала выделим диапазон, в котором хотим увидеть список, затем вводим формулу в первую ячейку и жмем CTRL + SHIFT + ENTER. Формула будет скопирована на весь диапазон, результатом станет отсортированный список.
Предположим, мы хотим выяснить имя менеджера с наибольшими продажами. Если бы мы использовали обычные формулы, понадобилось столько же строк, сколько и менеджеров. Однако мы можем сделать тоже самое в одну формулу массива: =СМЕЩ(A1;МАКС(ЕСЛИ(СУММЕСЛИ((A2:A10);(A2:A10);(D2:D10))=МАКС(СУММЕСЛИ((A2:A10);(A2:A10);(D2:D10)));СТРОКА(A2:A10);»»))-1;0) То, что мы делаем здесь — сравниваем сумму продаж конкретного менеджера с суммой продаж максимального менеджера. Если условие истинно, возвращаем номер строки. Функция ЕСЛИ возвращает массив номеров строк, относящихся к менеджеру с наибольшим показателем продаж, в противном случае возвращается пустота. С помощью функции МАКС мы находим строку, где происходит последнее вхождение имени, а затем с помощью СМЕЩ возвращаем имя из этой строки.
* https://dzen.ru/worceller?share_to=link * Викиум – это онлайн-платформа для тренировки памяти, внимания и мышления. * GeekBrains — это образовательная экосистема. В ней любой человек с нуля может освоить профессию в области программирования, маркетинга, управления и дизайна и гарантированно найти работу. * Skypro — онлайн-университет рентабельного образования. Учим IT-профессиям с нуля и гарантируем новую работу. * Знанио — ведущий портал дополнительного образования для взрослых и детей. * Напишем - напишут всё что нужно.
Worcel
:Артем Сеферян
Когда простые формулы не помогают | Массивы функций | Операторы и формулы
Если не читали (https://ok.me/dxu71)первую часть (https://ok.me/exu71), то напомню что такое Массивы функций. Массив функций Excel позволяет решать сложные задачи в автоматическом режиме. Те, которые выполнить посредством обычных функций невозможно.Фактически это группа функций, которые одновременно обрабатывают группу данных и сразу выдают результат.
Оператор массива И
Оператор И (*) возвращает значение ИСТИНА в случаях, когда все условия выражения возвращают значение ИСТИНА. Пример на картинке показывает его использование между массивами.Оператор массива ИЛИ
Оператор ИЛИ (+) возвращает значение ИСТИНА, если хотя бы одно из условий выражения возвращает значение ИСТИНА.Пример на картинке показывает его использование между массивами.
Консолидация данных по более чем одному условию
Мы также можем использовать формулу массива для поиска суммы продаж менеджера с максимальными продажами.Функция ЕСЛИ возвращает массив отдельных сумм продаж менеджера совпадающего с менеджером с максимальными продажами, иначе 0.
Затем мы используем функцию СУММ для суммирования всех этих значений массива.
Сортировка с помощью формулы массива
Предположим, у вас есть набор данных в ячейках D2:D10, и вы хотите отсортировать его в порядке возрастания.Для этого понадобится функция НАИМЕНЬШИЙ(), а также диапазон, в котором мы будем производить вычисления.
Обычная функция НАИМЕНЬШИЙ для одной ячейки выглядит так =НАИМЕНЬШИЙ(D2:D10;1).
Необходимо скопировать эту функцию во все остальные ячейки и внести изменения во второй аргумент, чтобы получить отсортированный список.
Для начала выделим диапазон, в котором хотим увидеть список, затем вводим формулу в первую ячейку и жмем CTRL + SHIFT + ENTER.
Формула будет скопирована на весь диапазон, результатом станет отсортированный список.
Поиск уникального значения
Предположим, мы хотим выяснить имя менеджера с наибольшими продажами.Если бы мы использовали обычные формулы, понадобилось столько же строк, сколько и менеджеров. Однако мы можем сделать тоже самое в одну формулу массива:
=СМЕЩ(A1;МАКС(ЕСЛИ(СУММЕСЛИ((A2:A10);(A2:A10);(D2:D10))=МАКС(СУММЕСЛИ((A2:A10);(A2:A10);(D2:D10)));СТРОКА(A2:A10);»»))-1;0)
То, что мы делаем здесь — сравниваем сумму продаж конкретного менеджера с суммой продаж максимального менеджера. Если условие истинно, возвращаем номер строки.
Функция ЕСЛИ возвращает массив номеров строк, относящихся к менеджеру с наибольшим показателем продаж, в противном случае возвращается пустота.
С помощью функции МАКС мы находим строку, где происходит последнее вхождение имени, а затем с помощью СМЕЩ возвращаем имя из этой строки.
Поддержите канал, подпишитесь на него в Дзен
* https://dzen.ru/worceller?share_to=link* Викиум – это онлайн-платформа для тренировки памяти, внимания и мышления.
* GeekBrains — это образовательная экосистема. В ней любой человек с нуля может освоить профессию в области программирования, маркетинга, управления и дизайна и гарантированно найти работу.
* Skypro — онлайн-университет рентабельного образования. Учим IT-профессиям с нуля и гарантируем новую работу.
* Знанио — ведущий портал дополнительного образования для взрослых и детей.
* Напишем - напишут всё что нужно.