Hello Excel

​​Хай, френдс! Продолжаем цикл о МАКРОСАХ В предыдущем посте мы записали макрос через макрорекордер. Сегодня попробуем написать свой первый код для макроса 👀 Шаг 1. Переходим в Visual Basic. 1. Зайдите на вкладку Разработчик – Visual Basic. 2. Откроется новое окно, в левой части вы увидите листы в вашей книги, саму книгу 3. Для создания макроса, который может выполняться в любой части книги мы должны перейти на вкладку Insert, выбрать Module. Откроется окно, куда можно писать код для будущего макроса. Шаг 2. Кодим! Начнем с простого макроса. В ячейке А4 посчитать сумму А2 и А3 то есть А4 = А2 + А3. 1. Любой макрос начинается с Sub и заканчиваается End Sub. Так система понимает, что между этими значениями записан код для выполнения. 2. После Sub необходимо написать имя вашего макроса и обязательно после него поставить пустые скобки. У меня это будет: Sub SuperMacro() End Sub 3. В строку между началом и концом напишем код нашей формулы. Сначала покажу формулу, а потом объясню: А4 = А2 + А3 на языке Excel – это Cells(4, 1).Formula = Cells(2, 1) + Cells(3, 1) на языке Visual Basic. — Ячейка – это Cells. — Адрес ячейки – это цифры в скобка после Cells. Первая цифра = номер строки, вторая цифра – номер столбца. — .Formula – это свойство ячейки, то есть говорим системе что формула для ячейки А4 будет….. — Далее указываем через аналогичную адресацию сумму А2 и А3. Шаг 3. Запускаем макрос В итоге мы получили: Sub SuperMacro() Cells(4, 1).Formula = Cells(2, 1) + Cells(3, 1) End Sub Для его запуска перейдите на Разработчик – Макросы – найдите макрос SuperMacro и нажмите Выполнить. В ячейке А4 появится результат суммы. И самое главное, в окне ввода формулы вы не увидите самой формулы. Если замените значения в ячейках А2 и А3 и еще раз выполните макрос, то значение суммы изменится. На сегодня хватит кодерства. Видео ниже⬇️ Подписывайтесь на Hello Excel

​​Привет, друзья! Мало кто знает, что в сводных таблицах есть превосходные заменители автофильтров. Называются Слайсеры или Срезы. 1. Поставьте курсор на сводную таблицу, перейдите на вкладку Анализ сводной таблицы - выбирайте инструмент Вставить срез 2. В открывшемся окне нужно выбрать по какому полю хотите фильтровать данные. 3. Появится плашка с доступными значениями для фильтрации. Хотите выбрать что-то одно – нажмите ЛКМ по одному. Хотите множественный выбор? Зажмите Ctrl и выбирайте несколько значений. Главное преимущество слайсера от автофильтра: вы видите ваш выбор. Подписывайтесь на Hello Excel #фичанедели

​​Buongiorno amico! Старая-новая фича недели. Постоянно вижу в таблицах много свободного места в ячейках. Авторы похоже планировали размещать цитаты Джейсона Стэтхема, но по ходу пьесы передумали, и поместили лишь четырехзначное число. Надо бороться с лишним местом. Ну не руками же править? Выделяйте нужные ячейки или строки / столбцы. Вспоминайте, что на вкладке Главная есть кнопка Формат, там есть инструменты: 1. Автоподбор высоты строки 2. Автоподбор ширины столбца Чистая магия 🔮 Подписывайтесь на Hello Excel #фичанедели

​​Салют, друзья! Заканчиваем цикл массивов и начинаем новый – цикл материалов о МАКРОСАХ. О да, те самые, великие и ужасные. Те самые, которыми владеют джедаи. Цикл будет длинный, поэтому каждую среду смело можно наливать себе большую кружку кофе и влезать в эту интересную тему. Сегодня начнем с теории. Что за макрос? Макрос – это набор команд, который вы можете записать и воспроизводить в любое удобное время. Представьте, что это горячая комбинация словно CTRL + C. НО, в макрос вы можете записать более сложные систематически действия: — изменение листов — изменение больших диапазонов данных за одно нажатие — применение сложных формул (без написания формул каждый раз) С макросами нужно дружить. За знание ВПР, ПОИСКПОЗ и др. вас с руками заберет HR, а знание как работать с макросами уронит челюсти ваших коллег и руководителя на -1 этаж. Что есть макрос вы поняли. Записать макрос можно 2-мя способами: 1. Запись через макрорекордер – как видео записать и потом поставить его на повтор. 2. Написать код на VBA – пишем код на Visual Basic внутри офисного пакета. Шаг 1. Включим панель ленты с макросами. 1. Зайдите на вкладку Файл – Параметры. 2. Выбирайте Параметры Excel – Настроить ленту 3. Поставьте чек-бокс напротив вкладки Разработчик Шаг 2. Записываем через Макрорекордер. Как запись видео с телефона, если не проще. 1. На вкладке ленты Разработчик выбираем Записать макрос. 2. В открывшемся окне называем макрос (также можете назначить комбинацию клавиш на этот макрос) и запускаем запись. После этого любое ваше действие запишется в макрос в виде кода. 3. После того как записали действие, нажмите Остановить запись. 4. Запустите макрос комбинацией. Если ее нет, то нажмите на Макросы, выбирайте ваш и нажмите Выполнить. Магия работает. Дополнительные кнопки записи и остановки записи макроса есть в нижнем левом углу (посмотрите в конце видео). Ограничения макрорекордера: — Не сможете придумать функцию, которой нет в Excel. Только текущий функционал. — Нет больших возможностей по работе с условиями / циклами. На сегодня все. Пример записи макроса по удалению диапазонов ячеек смотрите в видео⬇️ В следующую среду вместе шагнем в сторону кода на VBA. Будет интересно 🔥

​​Всем привет! Очередная фича недели. Если ввести перед формулой знак апострофа (’), то Excel прочитает данные после апострофа как текст, а не как число. Хотите записать формулу в ячейки и показать ее всем без расчета? Тогда эта фича для вас. Запись ‘=3+3 не даст ответ 6, а так и покажется = 3 + 3 без преобразования выражения в формулу. К сведению, апостроф можно поставить с клавишей Э на английской раскладке клавиатуры. Подписывайтесь на Hello Excel #фичанедели

🧮 Вы хотели узнать как использовать формулы в проверке данных. Пришло время! Листайте новые карточки👉 Подписывайтесь на Hello Excel

​​Привет, друзья! Давно ли вы делали многоуровневую формулу ЕСЛИ(ЕСЛИ(ЕСЛИ……? Уже чувствую боль от прочтения таких конструкций. Привыкайте говорить «Нет» вложениям множеству ЕСЛИ и говорить «Да» формуле массивов 😊. Задача: — Список регионов продаж и фамилий менеджеров находится в 2-х диапазонах А2:А5, В2:В5. — Суммы продажнаходится в диапазоне С2:С5 — В ячейках Е2 и Е3 находятся условия поиска: наименование региона и имя менеджера. Необходимо найти продажи по менеджеру и региону. Вспоминаем, что знак умножения в формулах массивов (и не только) является аналогом оператора И. Он позволяет сцеплять несколько условий. То, что нам нужно! Пишем формулу: =СУММ((A2:A5=E2)*(B2:B5=E3)*(C2:C5)) и не забываем нажать Ctrl + Shift + Enter. Посмотрите видео⬇️ Подписывайтесь на 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 #фичанедели

​​💯Считаем по грейдам Итак, еще одно практическое применение массивам – подсчет попадания в «грейды». Это можно применить для подсчета оценок/результатов в: — Спортивных мероприятиях — Обучении и тестировании — Подведении результатов предприятия — Выполнении KPI — И много чего еще Грейды – диапазоны оценок. У нас есть KPI «Выполнение плана продаж». У KPI есть диапазон выполнения в 80%, 90%, 100% и так далее. И вот есть менеджеры, которые завершили продажи в отчетном месяце. Пришло время подвести итоги. У кого-то 123% выполнение, у кого-то 54%. Наша задача: подсчитать количество попавших под планку до 80%, до 90%, до 100% и далее. С этим быстро справляется формула массивов функции ЧАСТОТА. Синтаксис простой: = ЧАСТОТА (массив данных; массив интервалов) массив данных - указываем результаты за месяц. массив интервалов - указываем грейды или оценки. Не забудьте завершить данную формулу через Ctrl + Shift + Enter. Важно: какая бы у вас ни была сетка оценок, всегда закладывайте возможность перевыполнения. Если у вас максимальная оценка 140%, то найдется тот, что выполнит на 156%. Чтобы система посчитала такой результат, при выделении ячеек для вывода результатов взять количество ячеек из массива интервалов + 1 пустую ячейку. В эту пустую ячейку и будет вестись подсчет всех сверхнормативных результатов. Посмотрите пример в видео, станет понятнее ⬇️ Подписывайтесь на Hello Excel

​​ 🅰️Проверяем регистр 🅱️ Бонджорно! Еще одна фича недели. Задача: у вас выгрузка кодов, где есть цифры, маленькие и большие буквы. Необходимо проверить какие из кодов содержат строчные буквы (маленькие буквы). Список большой, глазами проверять – не вариант. Список находится начиная с А2 и ниже. Добавляем автоматизацию: в В2 набивайте формулу = СОВПАД (A2;ПРОПИСН(A2)) и протягиваем вниз. Функция СОВПАД проверяет одно значение с другим и возвращает результат (истина или ложь). В примере мы проверяем значение из ячейки А2 со значением А2 принудительно переведенным в прописные буквы (большие). Таким образом найдем коды с маленькими буквами. Подписывайтесь на Hello Excel #фичанедели

❇️ Новые карточки о проверке данных. Листайте 👉 Подписывайтесь на 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