Магия Excel

Делаем кнопку группировки более наглядной Функция SUBTOTAL / ПРОМЕЖУТОЧНЫЕ.ИТОГИ(подробнее про нее здесь) агрегирует только данные из видимых ячеек. Соответственно, мы можем проверять, раскрыта ли ячейка строкой ниже — если применим функцию COUNTA / СЧЁТЗ, то есть подсчет любых значений. В SUBTOTAL это функция номер 3 (номер функции в первом аргументе). И если строка скрыта, то функция вернет 0. Тогда можно выдать текст в духе "Подробнее" или "Раскрыть". Если не 0 — значит, строка ниже видима (и в ней что-то есть), выдаем другую надпись ("Скрыть"). =ЕСЛИ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(103;A3)=0; "⬇️Подробнее"; "⬆️Скрыть") Мини-курс "Магия новых функций Excel"🔥

Точку данных в диаграмме (например, в линейчатой) можно заменить изображением Для этого скопируйте изображение (Ctrl + C), выделите диаграмму, выделите нужный элемент (просто щелкните еще раз после выделения диаграммы на нужный элемент — вы поймете, что он выделен, когда круглые маркеры по углам останутся только у этого столбика). И Ctrl + V — вставляем изображение. После этого можно зайти в панель форматирования (Ctrl + 1), чтобы уменьшить боковой зазор между столбиками. Тогда они станут шире/выше. И наконец, чтобы у вас не был один очень длинный кот или утка или что там у вас было на вставленной картинке, выберите в параметрах (Параметры ряда — Границы и заливка) вариант "размножить". Мини-курс "Магия новых функций Excel"🔥

Отличия по строкам Вы хотите быстро выделить цветом ячейки, в которых план отличается от факта (один столбец от другого — в общем случае)? 1 Выделяем столбцы (можно быстро быстро выделить их сочетанием Ctrl + Shift + стрелка вниз) 2 Ctrl + G —> Выделить (Special) 3 Отличия по строкам (Row differences) 4 Красим выделенные ячейки нужным цветом. Готово! Смотрим на GIF (без звука) Мини-курс "Магия новых функций Excel"🔥

Магия новых функций Excel: революция в формулах🔥 Друзья, мы с Лемуром рады анонсировать новый мини-курс, посвященный новым же функциям Excel (и — по большей части — Google Таблиц). Это функции, которые могут решать задачи, ради которых раньше пришлось бы писать макросы или создавать запросы в Power Query! Некоторые из них делают то же, что и стандартные инструменты Excel (как сортировка, фильтрация, удаление дубликатов или разделение текста на столбцы). Но, будучи формулами, делают это в режиме реального времени, с обновлением при изменении данных.Некоторые из этих функций кажутся сложными. Но на самом деле они проще тех же самых макросов и мы в этом убедимся. Некоторые из них кажутся бесполезными. Но их просто нужно уметь готовить — и вся их мощь раскрывается зачастую только при сочетании нескольких в одной формуле. Мы рассмотрим мно-о-ого таких комбинаций. В честь запуска новой версии сайта и этого курса — зверская цена от Лемура — 890 рублей!🔥 Никаких уловок и вечных скидок не будет: 18 ноября цена вырастет до 2300 и больше уже никогда не вернется к старой. Внутри: 14 видео от 5 до 25 минут с качественным монтажом Дополнительные текстовые материалы Файлы со всеми примерами и формулами — исходные и готовые. Покупать тут — и там же подробная программа и примеры: https://shagabutdinov.ru/magic-excel

Еще немного 🔥 клавиш. Что умеет F11? Просто F11 — это вставка диаграммы. Но не простой, а диаграммы на отдельном листе (на котором не будет ячеек). Обычная диаграмма (внедренная) — это Alt + F1. Alt + F11 — редактор VBA (макросов). Ну а Shift + F11 — вставка нового листа.

Отображаем все скрытые строки сразу 1 Выделяем первую строку 2 Выделяем все строки вниз: Ctrl + Shift + ↓ 3 Нажимаем Ctrl + Shift + 9 Готово🔥

Поиск и окно "Найти и заменить" в Excel и Google Таблицах Казалось бы, все просто — нажимай Ctrl+F да ищи, нажимай Ctrl+H да заменяй/удаляй. Но есть приятные опции: — Можно искать/заменять в диапазоне/на листе/на всех листах — Можно искать/заменять с учетом регистра — В Google Таблицах в окне "Найти и заменить" можно использовать регулярные выражения (смотрите примеры в статье: можно поменять формат дат на другие или сделать еще какую-нибудь магию), а в Excel символы подстановки — В Google Таблицах можно искать по ссылкам (а в обоих редакторах — по формулам); — В Excel можно менять формат ячеек — достаточно выбрать образец для поиска и образец для замены.

Готовим с Лемуром новый курс — по новым же функциям Excel (365). Вот такие и многие-многие другие интересности можно с этими новыми функциями делать. Уже совсем скоро будет доступен!

Есть прекрасный сайт с производственным календарем для каждого года, начиная с 2014: http://xmlcalendar.ru/?country=ru Форматы там есть разные — например, XML (в этом формате у дат есть атрибут t с типом, и можно извлечь только праздничные (1) или только сокращенные рабочие дни (2) или рабочие СБ/ВС (тип 3)) или TXT, где просто список всех выходных и праздничных дней без типов. Как получать данные? Можно разово вручную — скачать CSV и открыть в Excel или открыть ссылку с TXT-форматом, выделить все (Ctrl + A), скопировать (Ctrl + C) и вставить (Ctrl + V) в Excel. Но так придется для каждого года вставлять данные вручную. Если вы хотите формулу, которая будет возвращать данные для текущего года, можно воспользоваться функциями ГОД / YEAR и СЕГОДНЯ / TODAY. Следующее сочетание будет возвращать номер текущего года: ГОД(СЕГОДНЯ()) И его можно будет подставить в ссылку вместо значения конкретного года, так как ссылки меняются только на номер года: http://xmlcalendar.ru/data/ru/2024/calendar.txt http://xmlcalendar.ru/data/ru/2025/calendar.txt Соответственно, ссылка на календарь текущего года в TXT-формате в формуле будет выглядеть так: "http://xmlcalendar.ru/data/ru/" & ГОД(СЕГОДНЯ()) & "/calendar.txt" А далее можно ее загрузить одной из функций. В Google Таблицах это IMPORTDATA: =IMPORTDATA("http://xmlcalendar.ru/data/ru/" & YEAR(TODAY()) & "/calendar.txt") В Excel (только на WIndows) это ВЕБСЛУЖБА / WEBSERVICE. Чтобы она не возвращала все даты "слипшимся" списком в одной ячейке, добавим функцию ТЕКСТРАЗД / TEXTSPLIT, чтобы разделить по переносу строку (символ с номером 10 — его нельзя напечатать, поэтому используем функцию СИМВОЛ / CHAR): =ТЕКСТРАЗД(ВЕБСЛУЖБА("http://xmlcalendar.ru/data/ru/"&ГОД(СЕГОДНЯ()) &"/calendar.txt");;СИМВОЛ(10)) Но Excel не воспринимает как даты эти значения. Придется немного поколдовать: убрать непечатаемые символы через функцию ПЕЧСИМВ / CLEAN и сделать текст настоящей датой с помощью ДАТАЗНАЧ / DATEVALUE: =ДАТАЗНАЧ(ПЕЧСИМВ(ТЕКСТРАЗД(ВЕБСЛУЖБА("http://xmlcalendar.ru/data/ru/"&ГОД(СЕГОДНЯ()) &"/calendar.txt");;СИМВОЛ(10))))

Горячие клавиши для быстрого перемещения и выделения в Excel🔥 Ctrl + PgDn/PgUp — следующий/предыдущий рабочий лист (а если открыто диалоговое окно, то перемещение между вкладками этого окна) Ctrl + Backspace — возвращаемся к активной ячейке Ctrl + A — выделяем всю текущую область (диапазон) Shift + пробел — выделяем всю строку (если активна "умная таблица" — то выделяется столбец без заголовков — только данные — пределах таблицы, иначе — столбец в пределах всего листа) Ctrl + пробел — выделяем весь столбец Ctrl + стрелки — перемещаемся в конец диапазона (в направлении стрелки). Вместе с Shift — выделяем до конца диапазона. Ctrl + End — перемещаемся в конец активной области на листе (в самые последние строку и столбец с данными) P.S. Если делаете какое-то действие часто, на последнем шаге, когда кликаете на какую-то команду, остановитесь на секунду, наведите курсор на команду и посмотрите на подсказку — вполне вероятно, что там будет сочетание клавиш для нее.

Пробел: пересечение диапазонов Немного экзотики. Пробел между диапазонами в Excel = пересечение этих диапазонов. Например, такая формула: =A1:A3 A2:C2 Вернет значение из A2 — общей ячейки диапазонов A1:A3 и A2:C2. Можно использовать и с именованными диапазонами — пример в видео. =Магия_Excel_2 Март Кстати, чтобы быстро присвоить всем столбцам и/или строкам диапазона имена, можно использовать команду "Создать из выделенного" с вкладки "Формулы" (смотрите на видео) или нажать Ctrl + Shift + F3.

Считаем уникальные значения: PowerQuery Наконец, четвертый вариант — если у вас есть Power Query, а это куда больше версий, чем в случае с новыми функциями. Загружаем данные в Power Query: Данные — Получить данные — Из таблицы / диапазона Удаляем все, кроме двух столбцов — по одному будем группировать (у нас это "Клиент"), по другому считать уникальные значения (у нас это "Товар") Преобразование — Группировать по — выбираем нужные столбцы и операцию "Количество уникальных строк" Закрыть и загрузить в — выбираем "Таблица" на новый или существующий лист. Готово!