​​📉МИН и МАКС в массивах📈

Продолжаем цикл постов по формулам массивов.
Сегодня применим их с функциями МИН и МАКС. Вы можете проводить математические операции с массивами данных и «поверх» использовать МИН / МАКС.

Кейс №1. Разница

Перед вами цены товаров на 1-ый месяц и на 2-ой месяц после применения праздничной скидки. Суммы разные. Вы хотите понять максимальный и минимальный размер скидки во 2-ом месяце.

1. Встаем в одну ячейку, прописываем формулу поиска минимума, указывая адреса одного и второго диапазона ячеек с ценами, =МИН(B2:B7-C2:C7)
2. Нажимаете Ctrl + Shift + Enter.
3. Для нахождения максимума в соседней ячейки вписываете: =МАКС(B2:B7-C2:C7)
4. Нажимаете Ctrl + Shift + Enter.

Excel поочередно вычитает из одну цену из другой. Далее ищет минимум/максимус.

Кейс №2. Находим МАКС/МИН по условию

А сейчас заставим трепетать кадровиков, которые пишут в вакансиях требуется знание ВПР 😅
Сделаем аналог ВПР: будем искать максимальную цену среди цен товара Кресло К44.

— В А10 находится искомое значение - Кресло К44.
— В A2:A7 – наименования товаров.
— В B2:B7 – цены товаров.

Поехали:

1. Встаем в ячейку, где планируем получить результат поиска.
2. Прописываем формулу =МАКС(ЕСЛИ(A10=A2:A7;B2:B7)).
3. Нажимаете Ctrl + Shift + Enter.

Мы фактически сказали системе, если где-то в диапазоне A2:A7 будет Кресло К44, то верни его значение. И она возвращает несколько значений. Какая молодец!
А затем среди них находит максимум.

Формулы массивов – это весьма интересно и даже красиво. Видео⬇️

Подписывайтесь на Hello Excel.

​​⚡️Молниеносная фильтрация⚡️

Всем салют!
Новая фича недели. Часто бывает, что строка заголовков таблицы не закреплена, листаете вниз, нашли нужно значение и хотите найти все эти значения в таблице.
Как говорится, нужно «вертать всё в зад», подниматься наверх, ставить автофильтр, фильтровать по значению. Неудобно и нудно.

Вместо этого предлагаю молниеносный способ фильтрации по найденному значению:

1. ПКМ по ячейке.
2. Фильтр – Фильтр по значению выделенной ячейке.

Готово. У вас стоит автофильтр с выбранным значением.

Подписывайтесь на Hello Excel
#фичанедели

​​Константы в Константинополе

Итак, мы уже повертели формулы массивов, знаем как они работают. Теперь обсудим одну, казалось бы, бесполезную тему (однако нет).
В Excel можно создавать массивы констант.

Теория

Что такое константа? Это постоянное значение, которое невозможно изменить. Как год вашего рождения.

Допустим вы можете забить в «память» Excel массив в виде строки со значениями 1 2 3.
Чтобы это сделать, вам нужно:

1. Выделить 3 ячейки в длину.
2. Прописать формулу = {1;2;3}.
3. Нажать Ctrl + Shift + Enter.

Захотели сделать вертикальный массив? Не вопрос:

1. Выделяйте 3 ячейки в высоту.
2. Пропишите формулу = {1:2:3}.
3. Нажмите Ctrl + Shift + Enter.

Обратите внимание: когда делаете горизонтальный одномерный массив (строку) в качестве разделителя используйте точку с запятой (;). Когда вертикальный массив – двоеточие (:).

Хотите сделать двумерный массив 2х2? Пойдем, покажу:

1. Выделяйте 4 ячейки: 2 столбца на 2 строки.
2. Пропишите формулу = {1;2:3;4}.
3. Нажать Ctrl + Shift + Enter.

Вы говорите системе: запиши 1 и 2 друг за другом одной строкой, а затем перейдите на следующую строку и запиши 3 и 4.

Так, теорию посмотрели, прекрасно. Теперь двигаем к живым примерам.

Кейс №1

Массивам констант, как и любым диапазонам можно давать имена.

1. Перейдите на вкладку ленты Формулы – Диспетчер имен – Создать.
2. В поле Имя назовите ваш массив.
3. В поле Диапазон указывайте массив констант со всеми правилами из блока выше: фигурные скобки, разделители. Нажмите ОК.

Далее используйте массив констант по имени в формулах. Если назвали массив Мебель, то прямо так и пишите в формулах, Excel покажет его в списке.

Кейс №2

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

НО. Писать вручную массив констант на сотни строк – себя не уважать. Как сделать проще?

1. В свободной ссылке сделайте ссылку на диапазон значений, который хотите спрятать: =А2:В240. Не уходите с ячейки.
2. Встаньте в строку ввода формулы и нажмите F9 (отладка формул). Вместо формулы в строке ввода покажется массив.
3. Выделяйте и копируйте его.
4. Затем создавайте имя на этот массив как описано выше. Сделали имя Мебель.
5. Удаляйте данные в А2:В240, они вам не нужны.
6. Используем ВПР: =ВПР(D2;Мебель;2;0). Данных нет, а ВПР работает, магия.

Надеюсь, что последний кейс вам понравился. Теперь вы понимаете как правильно использовать массивы констант. Видео⬇️

Подписывайтесь на Hello Excel

​​ 🅰️Проверяем регистр 🅱️

Бонджорно!

Еще одна фича недели. Задача: у вас выгрузка кодов, где есть цифры, маленькие и большие буквы. Необходимо проверить какие из кодов содержат строчные буквы (маленькие буквы). Список большой, глазами проверять – не вариант.
Список находится начиная с А2 и ниже.

Добавляем автоматизацию: в В2 набивайте формулу = СОВПАД (A2;ПРОПИСН(A2)) и протягиваем вниз.

Функция СОВПАД проверяет одно значение с другим и возвращает результат (истина или ложь).

В примере мы проверяем значение из ячейки А2 со значением А2 принудительно переведенным в прописные буквы (большие). Таким образом найдем коды с маленькими буквами.

Подписывайтесь на Hello Excel
#фичанедели

​​💯Считаем по грейдам

Итак, еще одно практическое применение массивам – подсчет попадания в «грейды». Это можно применить для подсчета оценок/результатов в:

— Спортивных мероприятиях
— Обучении и тестировании
— Подведении результатов предприятия
— Выполнении KPI
— И много чего еще

Грейды – диапазоны оценок. У нас есть KPI «Выполнение плана продаж». У KPI есть диапазон выполнения в 80%, 90%, 100% и так далее.

И вот есть менеджеры, которые завершили продажи в отчетном месяце. Пришло время подвести итоги. У кого-то 123% выполнение, у кого-то 54%. Наша задача: подсчитать количество попавших под планку до 80%, до 90%, до 100% и далее.

С этим быстро справляется формула массивов функции ЧАСТОТА. Синтаксис простой:

= ЧАСТОТА (массив данных; массив интервалов)

массив данных - указываем результаты за месяц.
массив интервалов - указываем грейды или оценки.

Не забудьте завершить данную формулу через Ctrl + Shift + Enter.

Важно: какая бы у вас ни была сетка оценок, всегда закладывайте возможность перевыполнения. Если у вас максимальная оценка 140%, то найдется тот, что выполнит на 156%. Чтобы система посчитала такой результат, при выделении ячеек для вывода результатов взять количество ячеек из массива интервалов + 1 пустую ячейку. В эту пустую ячейку и будет вестись подсчет всех сверхнормативных результатов.

Посмотрите пример в видео, станет понятнее ⬇️

Подписывайтесь на Hello Excel