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

Бонджорно!

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

​​Всем привет!

Очередная фича недели.
Если ввести перед формулой знак апострофа (’), то Excel прочитает данные после апострофа как текст, а не как число.

Хотите записать формулу в ячейки и показать ее всем без расчета? Тогда эта фича для вас.
Запись ‘=3+3 не даст ответ 6, а так и покажется = 3 + 3 без преобразования выражения в формулу.

К сведению, апостроф можно поставить с клавишей Э на английской раскладке клавиатуры.

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

​​Салют, друзья!

Заканчиваем цикл массивов и начинаем новый – цикл материалов о МАКРОСАХ. О да, те самые, великие и ужасные. Те самые, которыми владеют джедаи.

Цикл будет длинный, поэтому каждую среду смело можно наливать себе большую кружку кофе и влезать в эту интересную тему. Сегодня начнем с теории.

Что за макрос? Макрос – это набор команд, который вы можете записать и воспроизводить в любое удобное время.

Представьте, что это горячая комбинация словно CTRL + C.
НО, в макрос вы можете записать более сложные систематически действия:

— изменение листов
— изменение больших диапазонов данных за одно нажатие
— применение сложных формул (без написания формул каждый раз)

С макросами нужно дружить. За знание ВПР, ПОИСКПОЗ и др. вас с руками заберет HR, а знание как работать с макросами уронит челюсти ваших коллег и руководителя на -1 этаж.

Что есть макрос вы поняли. Записать макрос можно 2-мя способами:

1. Запись через макрорекордер – как видео записать и потом поставить его на повтор.
2. Написать код на VBA – пишем код на Visual Basic внутри офисного пакета.

Шаг 1. Включим панель ленты с макросами.

1. Зайдите на вкладку Файл – Параметры.
2. Выбирайте Параметры Excel – Настроить ленту
3. Поставьте чек-бокс напротив вкладки Разработчик


Шаг 2. Записываем через Макрорекордер. Как запись видео с телефона, если не проще.

1. На вкладке ленты Разработчик выбираем Записать макрос.
2. В открывшемся окне называем макрос (также можете назначить комбинацию клавиш на этот макрос) и запускаем запись. После этого любое ваше действие запишется в макрос в виде кода.
3. После того как записали действие, нажмите Остановить запись.
4. Запустите макрос комбинацией. Если ее нет, то нажмите на Макросы, выбирайте ваш и нажмите Выполнить. Магия работает.

Дополнительные кнопки записи и остановки записи макроса есть в нижнем левом углу (посмотрите в конце видео).

Ограничения макрорекордера:

— Не сможете придумать функцию, которой нет в Excel. Только текущий функционал.
— Нет больших возможностей по работе с условиями / циклами.

На сегодня все. Пример записи макроса по удалению диапазонов ячеек смотрите в видео⬇️
В следующую среду вместе шагнем в сторону кода на VBA. Будет интересно 🔥