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

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

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

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

​​Салют!

Итак, новая фича недели.
Мы составили распорядок недели: простая таблица, где по горизонтали дни недели, по вертикали временные слоты. В ячейках (C2:I17) указана категория вашей деятельности: Работа, Спорт, Отдых, Свободное время и Сон.
Количество часов в слоте указано отдельным столбцом (A2:A17).

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

1. Выписываем категории в отдельный столбец, где хотим посчитать результаты.
2. Вспоминаем об отличной функции СУММПРОИЗВ. Прописываете формулу:

= СУММПРОИЗВ (($A$2:$A$17)*($C$2:$I$17=K2))

($A$2:$A$17) – ссылаемся на столбец с количеством часов в слоте.
($C$2:$I$17=K2)) – говорим системе во всей таблице найди конкретную категорию, допустим Сон.
* - знак умножения выполняет роль оператора И.

Формула умножает количество упоминаний категории на часы в слотах. Получается итоговое время.
Теперь можете грамотно оценивать распределение вашего времени.
Смотрите в видео⬇️

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

​​Привет, друзья!
Давно ли вы делали многоуровневую формулу ЕСЛИ(ЕСЛИ(ЕСЛИ……? Уже чувствую боль от прочтения таких конструкций.

Привыкайте говорить «Нет» вложениям множеству ЕСЛИ и говорить «Да» формуле массивов 😊.

Задача:
— Список регионов продаж и фамилий менеджеров находится в 2-х диапазонах А2:А5, В2:В5.
— Суммы продажнаходится в диапазоне С2:С5
— В ячейках Е2 и Е3 находятся условия поиска: наименование региона и имя менеджера.

Необходимо найти продажи по менеджеру и региону.


Вспоминаем, что знак умножения в формулах массивов (и не только) является аналогом оператора И. Он позволяет сцеплять несколько условий. То, что нам нужно!

Пишем формулу: =СУММ((A2:A5=E2)*(B2:B5=E3)*(C2:C5)) и не забываем нажать Ctrl + Shift + Enter.

Посмотрите видео⬇️

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