Кнопки группировки можно скрывать!

У вас на листе группировка. Она нужна, но кнопки + / - занимают слишком много места.

Нажмите Ctrl + 8, и они исчезнут. Текущая группировка никуда не денется, она сохранится. Менять ее не получится, но достаточно нажать Ctrl + 8, чтобы вернуть кнопки и менять группировку опять.

А для быстрой группировки используйте горячие клавиши:
Alt + Shift + → (группировать)
Alt + Shift + ← (разгруппировать)

_ _ _
Мини-курс "Магия новых функций Excel. Революция в табличных формулах" 🔥

Вычисляем отступ в ячейке

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

А там понадобилось фильтровать и убирать строки не по каким-то значимым признакам, а только по форматированию, причем по отступу (есть такие кнопки — "Увеличить отступ" и "Уменьшить" на вкладке "Главная").
А когда-то давно клиент просил переделывать выгрузку из 1С, где нужно было данные с разными отступами, которые были в одном столбце, раскидывать по разным столбцам.

Тут понадобится VBA, но в случае с вычислением отступа будет одна строчка кода.

Открываем редактор VBA — Alt + F11
Вставляем новый модуль в текущую книгу, если задача "на один раз".
Если функция нужна будет в разных файлах: создаем новую книгу, вставляем модуль там, сохраняем как надстройку Excel (формат .XLAM) и добавляем эту надстройку в Параметрах (Параметры — Надстройки — Надстройки Excel).

В модуле в любом случае вводим такой код:

Function ОТСТУП(cell As Range)
    ОТСТУП = cell.IndentLevel
End Function

Мы даем функции в качестве аргумента ячейку, а возвращает она значение отступа (IndentLevel) этой ячейки.

Помогал коллегам: нужно формировать пачку документов Word по шаблону (в реальной жизни нужно было выбирать один из шаблонов под разные типы контрагентов, тут для примера упростим до одного шаблона), подставляя много данных из таблицы: реквизиты контрагента, всякую информацию о предмете договора – десятки столбцов. Подумал, что такое нужно многим и решил поделиться.

Для такой задачи нужен макрос и он не очень сложный – главная идея в том, что мы в таблице в заголовках придумываем какие-то заглушки, которые подставляем и в документ. Это может быть что угодно, мы будем это заменять макросом (как делали бы вручную через Ctrl + H) на конкретные значения.
Заглушка может выглядеть в духе {СНИЛС}.

Один внешний цикл: по всем строкам таблицы, то есть по каждому договору, который нужно заменить. В нем создаем документ по шаблону и сохраняем под своим именем.
И далее внутренний цикл – по каждому столбцу в этой строке. Заменяем то, что в заголовке, на то, что в строке в этом столбце. То есть заглушку на значение.
Это циклы по всем строкам и столбцам. То есть можно добавлять новые поля (заглушки), строк (будущих договоров) тоже может быть сколько угодно.

Скачать файл с модулем (кодом макроса) можно по ссылке.
Чтобы добавить его к себе в книгу Excel, нажмите в ней Alt+F11 и в редакторе VBA щелкните правой кнопкой в Project Explorer, выбрав «Import File».

В видео со звуком – краткий обзор макроса и демонстрация работы.

Сортируем данные внутри текстовой строки

Это один из многочисленных примеров курса "Магия новых функций Excel. Революция в табличных формулах" 🔥

У нас есть текст (в одной ячейке) вида "Лемур 87,Штрудель 47,Вишенка 92". Нам надо получить "Вишенка 92,Лемур 87,Штрудель 47", то есть отсортировать и снова получить одно текстовое значение, а не таблицу.

С новыми функциями все это можно проделать формулой.
Сначала превратим текст в таблицу с помощью ТЕКСТРАЗД / TEXTSPLIT — для этого зададим два разделителя, запятую для строк и пробел для столбцов:

=ТЕКСТРАЗД(A1;" ";",")

Потом отсортируем с помощью функции СОРТ / SORT:

=СОРТ(ТЕКСТРАЗД(A1;" ";",");2;-1)

Полученную таблицу, уже упорядоченную, обработаем функцией BYROW — значения из каждой строки будем объединять функцией ОБЪЕДИНИТЬ / TEXTJOIN:

=BYROW(СОРТ(ТЕКСТРАЗД(A1;" ";",");2;-1);LAMBDA(стр; ОБЪЕДИНИТЬ(" ";;стр)))

Получим уже одномерный массив (столбец). И его объединим в одну текстовую строку с помощью еще одной функции ОБЪЕДИНИТЬ, только теперь уже с другим разделителем (запятой):

=ОБЪЕДИНИТЬ(",";;BYROW(СОРТ(ТЕКСТРАЗД(A1;" ";",");2;-1);LAMBDA(стр; ОБЪЕДИНИТЬ(" ";;стр))))

Хотите, чтобы в фигуре отображался какой-нибудь текст, сформированный формулой?

Например, текущее время с какой-нибудь надписью (Текущее время: 12:00") или что-то другое ("Выручка на дату 01.06: 1,2 млн")?

Для этого формируем в ячейке текст формулой, а потом ссылаемся на ячейку с формулой из фигуры (выделяйте фигуру, вводите знак "равно" и кликайте по ячейке, как в обычной формуле).

В примере используется функция ТДАТА / NOW — это текущие дата и время. И функция ТЕКСТ / TEXT — напоминаем, при объединении в текст числовых (а дата и время = число) значений они теряют форматирование. Если вам нужно время в заданном формате, например, ЧЧ:ММ, используйте функцию ТЕКСТ, которая превращает число в текст, но в нужном формате.

_ _ _
Мини-курс "Магия новых функций Excel. Революция в табличных формулах" 🔥

Книги по Excel — 2024

Microsoft Excel VBA and Macros (Office 2021 and Microsoft 365)
Амазон

В этом году дочитывал Билла Джелена про макросы. Мощнейший автор, конечно. Умеет объяснять сложное. Хотя именно по макросам еще мне нравится старая книга Уокенбаха "Профессиональное программирование на VBA", хотя она по 2013 Excel, в VBA мало что изменилось. Но у Уокенбаха больше деталей и тем, а у Джелена потрясающее объяснение и некоторые скрытые фишки, а так же есть про новые возможности (как LAMBDA, Power Query). Кстати, он отмечает, что эти инструменты вполне себе заменяют в ряде случаев макросы.

А у Джелена еще серия книг по сводным таблицам по каждой версии и по Excel в целом. В том числе следующая книга.

MrExcel 2024: Igniting Excel
Амазон

Хотя я читал и у меня есть такая же за 2022 год, эту купил не задумываясь. Ибо: во-первых, в начале есть отдельный список "что нового в этом издании", и этого нового много. Во-вторых, только у него бывают такие вещи, о которых вообще больше нигде не узнаешь.

И именно книги Джелена из этой серии Igniting Excel я бы назвал так, как названа следующая книга (скрытые сокровища Excel).

Exploring Microsoft Excel's Hidden Treasures
Амазон

Неплохо, но все-таки не так круто, и я от силы 3-4 штуки пометил для себя. Плюс не порадовало малое количество скриншотов. Так что не могу прямо-таки рекомендовать, если прочитано все, то можно посмотреть, но в качестве одной из первых смысла брать нет вообще.
Темы — всего понемногу, от условного форматирования и умных таблиц до Power Query и формул (вплоть до LAMBDA).
В комплекте файлы и цветные скриншоты в отдельном PDF.

The Definitive Guide to Power Query (M): Mastering complex data transformation with Power Query
Амазон
ДМК-Пресс (книга на русском)

Хотя я покупал электронку сразу после выхода, но когда появилось российское издание, заказал его в бумаге. В российском издании 750 страниц. Фундаментальная штука именно про язык M, а не Power Query, для тех, кто разобрался с интерфейсом и основными вещами там и хочет переходить на следующий уровень и разобраться с этим языком и творить магию в PQ. Тираж всего 200 экземпляров, не откладывайте!
Скриншоты в российском издании оригинальные, команды на двух языках, текст на русском, понятное дело :) Файлы и примеры есть на GitHub и на сайте издательства оригинала. И рисунки в цвете отдельно, как в предыдущей книге, но в российском издании они и так в цвете.

Основы визуализации данных
Озон
Это не совсем про Excel, но книга очень понравилась, так что ее тоже добавлю. Хотя не все в ней можно построить в Excel и она не завязана на какое-то приложение, но многое можно и вообще полезно. Примеров, идей и правил много, полезно. Книга цветная (у O'Reilly, напомню, это можно понять по тому, цветной ли зверь на обложке).

Быстрое скрытие столбцов

Чтобы скрыть столбцы (выделенные столбцы или столбцы, относящиеся к выделенным ячейкам), нажмите Ctrl + 0
Сработает и для несмежных столбцов (если вы заранее выделите ячейки, зажав Ctrl).

А как показать скрытые столбцы?
Это сочетание Ctrl + Shift + 0. Но оно может не работать. И тогда вам придется чуть пошаманить в настройках Windows — см скриншот :)

Для строк: то же самое, но с девяткой. Ctrl + 9 и Ctrl + Shift + 9.

Ссылка на несколько листов и функция SHEETS / ЛИСТЫ

Функция SHEETS возвращает число листов в ссылке — ее единственном аргументе.
Погодите-ка, какое еще число листов? Разве ссылка не на один лист всегда?

Нет, как многие знают, можно ссылаться на группу листов. При вводе формулы просто зажмите Shift и щелкните на ярлык листа, до которого вам нужно ссылаться — получите ссылку вида:

=СУММ(Январь:Июнь!A1)

(функция/функции могут быть любыми, не только СУММ, разумеется)

Такая ссылка — это ссылка на все ячейки A1 на листах от "Января" до "Июня" в том порядке, как их ярлыки идут в книге. То есть если между этими листами добавится новый — он попадет в сумму. А если вы перетащите "Апрель" правее "Июня", то он не будет участвовать в вычислении.

Функция SHEETS / ЛИСТЫ позволяет такие ссылки проверять и узнавать, сколько там листов.
Например, можно выдавать сумму диапазонов A2:A10 на всех листах, только если в ссылке 12 листов, а иначе — текст с сообщением об ошибке:
=ЕСЛИ(ЛИСТЫ(Январь:Декабрь!A1)=12 ;
СУММ(Январь:Декабрь!A2:A10);
"Ошибка! Проверьте, что все листы расположены в правильном порядке")

Хотите добавить маркеры (буллеты как в Word) к тексту в ячейках?

Выделяем ячейки, заходим в окно форматирования (Ctrl + 1)

Вводим маркер сочетанием Alt + 7 (семерка на цифровой клавиатуре)

Добавляем @ — это символ, обозначающий текст (значение ячейки).

Что-что там такое появилось в функции XLOOKUP?

А об этом уже завтра утром узнают подписчики рассылки "Магия таблиц"😸

А также про еще одно обновление и про сверхскрытие листов Excel.

Присоединяйтесь! Раз в 1-2 неделю, кратко про новости и приемы. И даже без спама:
https://shagabutdinov.ru/#subscription

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

Предыдущие выпуски рассылки можно посмотреть по ссылкам:
Первый. Новости и немного про графической слой Excel и про срезы — один из типов объектов, живущих на нем.

Второй. про ссылки на умные таблицы в Google Spreadsheets, линейчатую диаграмму для визуализации план-факта или чего-то подобного и немного личного — про путешествие на край света🥝.

Третий. Макрос для создания Word’овских документов по шаблону и лайфхаки для навигации по листам Excel. А также книжные итоги года.

Машинное обучение сквозь призму Excel

Это книга не для изучения Excel (есть очень краткий ликбез в начале книги, но базовые навыки лучше иметь перед началом чтения), а для изучения машинного обучения при помощи Excel. Книга подойдет для погружения в тему. Потому что, как автор отмечает, Excel позволяет данные "пощупать", видеть промежуточные шаги вычислений на разных этапах и поэтому разбирать методы машинного обучения пошагово.
Автор рекомендует и книгу и для преподавателей, которым она поможет построить структуру курса и сделать примеры для студентов.

Тираж всего 100 экземпляров и если вам интересна тема, лучше не откладывать.

Книга цветная. Формулы на двух языках. Скриншоты из оригинала, на английском.

Сайт издательства

Оглавление и фрагмент — по этой ссылке

Горизонтальная прокрутка в Excel

Колесом мыши можно скроллить горизонтально вправо-влево двумя вариантами:
1 если у вас есть второе колесико (например, у мыши Logitech MX Master 3S)
2 Ctrl + Shift + основное (единственное) колесико мыши

Ну а Ctrl + колесо меняет масштаб.

Новая функция УРЕЗДИАПАЗОН / TRIMRANGE

Что она делает? Уменьшает диапазон, отбрасывая пустые строки / столбцы — из начала, конца или с обеих сторон.

Первый аргумент — диапазон.
Второй и третий — как обрезать строки и столбцы. 0 = ничего не отбрасывать, 1 = отбрасывать пустые строки/столбцы в начале, 2 = в конце, 3 = с двух сторон (это вариант по умолчанию).

Еще появился новый тип ссылок, делающий то же самое.

Столбец A без пустых строк в начале и в конце:

=A.:.A  

Столбец A без пустых строк в конце:

=A:.A

Фильтр по значению ячейки

Эта опция работает не только внутри диапазона, в котором уже стоит фильтр.

Но можно и так: дописываете числовое условие вида ">15000" в первой пустой ячейке под столбцом с числовыми данными.

Правая кнопка — Фильтр — Фильтр по значению выделенной ячейки (Filter — By Selected Cells Value).

И фильтр установится, и данные сразу отфильтруются по этому столбцу.

Эту опцию, кстати, можно добавить на панель быстрого доступа. Параметры Excel — Панель быстрого доступа — Все команды — Автофильтр.

Да, она называется в списке команд просто как "Автофильтр", но это именно фильтрация по выделенной ячейке. Теперь у вас всегда под рукой кнопка, с помощью которой можно отфильтровать диапазон по значению активной ячейки, даже если фильтра в нем еще нет.

Выделяем всю строку / столбец

Ctrl + пробел — выделение всего столбца. Если вы в умной таблице, то это будет по порядку:
Первое нажатие: столбец таблицы, без заголовков и итогов;
Второе: с заголовками и итогами;
Третье: весь столбец листа.
Можно использовать и при вводе формулы! Выделили одну ячейку умной таблицы, Ctrl + пробел и вот у вас уже ссылка вида Таблица[Столбец].

В обычном диапазоне это сразу будет весь столбец.

Shift + пробел — выделение всей строки (в случае с умной таблицей сначала ее строки, затем строки всего листа)

Как запомнить? Shift обычно длиннее. Как строка 😸

Не пойти ли нам в бар? В StatusBar!

К строке состояния в Excel можно получить доступ через макросы и отображать там то, что захочется.

Например, если мы хотим показывать, сколько ячеек выделено:
1 Заходим в редактор макросов (Alt + F11)
2 Выбираем в окне Project текущую рабочую книгу
3 В ее модуле сверху выбираем в выпадающих списках Workbook, а справа в списке событий SheetSelectionChange
4 Создается процедура, в которую мы добавляем одну строчку с тем, что хотим в строке (StatusBar объекта Application, то есть строка состояния Excel) показывать:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Application.StatusBar = "Выделено ячеек: " & Target.Cells.Count
End Sub

Теперь в ПРОСМОТРX / XLOOKUP есть поддержка регулярных выражений. Ее добавили в декабре 2024 и пока она доступна только подписчикам Microsoft 365.

Чтобы использовать регулярные выражения при поиске с помощью этой функции, нужно задать пятый аргумент – если этого не сделать, то по умолчанию будет точный поиск. Нам нужно 3 – соответствие регулярных выражений. Кстати, напомню, что вариант 2 – это возможность использовать символы подстановки * (любое, в том числе нулевое, количество любых знаков) и ? (один любой знак). Старая ВПР / VLOOKUP, кстати, умеет работать со звездочкой и знаком вопроса по умолчанию.

Вот пример с регуляркой в ПРОСМОТРX: ищем цену первого товара, в котором будет либо 3xUSB в названии, либо 4xUSB (символы в квадратных скобках в регулярках = один символ из набора).

Аналогично регулярки теперь поддерживаются и ПОИСКПОЗX / XMATCH.

___
А кто узнал об этом первым? А первым об этом узнали подписчики рассылки "Магия таблиц" 😸Присоединяйтесь:
https://shagabutdinov.ru/#subscription

Друзья, если вы покупали курс "Магия новых функций Excel", заглядывайте в личный кабинет!

Там вас ждет новый урок про функцию TRIMRANGE на 15 минут и файлы-примеры к нему.
В уроке дополнительно покрутим функции FILTER и UNIQUE, поговорим про то, как в старых версиях сделать именованный динамический диапазон с помощью ИНДЕКСа и, собственно, покрутим разные примеры с основным блюдом — TRIMRANGE / УРЕЗДИАПАЗОН

Если вы не покупали — теперь в курсе 15 уроков, и вот они:

1 / Старые и новые формулы. Новые типы ссылок — собачка (@) и решетка (#). Новая ошибка #SPILL. Старые функции в новых формулах. Новые формулы и старые инструменты Excel. Функция UNIQUE
2 / Функции SORT, SORTBY и FILTER
3 / Функция SEQUENCE: формируем последовательность из чисел или дат (или букв? 😉) одной формулой
4 / Функции для разделения текста: TEXTSPLIT и другие
5 / Функции для работы с регулярными выражениями
6 / Функции для перевода и определения языка
7 / Делаем плоское квадратным и наоборот: TOCOL и TOROW, WRAPROWS и WRAPCOLS
8 / Объединяем массивы/таблицы: функции VSTACK и HSTACK
9 / Извлекаем строки и столбцы: функции CHOOSECOLS и CHOOSEROWS, DROP и TAKE
10 / Функция LAMBDA и вспомогательная функция MAP
11 / Функция MAP: собираем данные с разных листов
12 / Функции BYROW и BYCOL: последовательно обрабатываем каждую строку (столбец) в диапазоне
13 / Функция REDUCE: применяем вычисление к каждому элементу и получаем накопленный итог и функция SCAN: рассчитываем нарастающие итоги с разной магией
14 / Функции PIVOTBY и GROUPBY — сводные таблицы… формулами!
15 / Функция TRIMRANGE: удаляем пустые строки и столбцы

https://shagabutdinov.ru/magic-excel

Два способа перемещения между листами Excel с помощью клавиатуры

Первый:
Сочетания клавиш Ctrl + PgUp (предыдущий лист, влево) и Ctrl + PgDn (следующий, вправо)

Второй:
Нажмите F6 (и отпустите). Выделится ярлык текущего листа.
После этого стрелками на клавиатуре выберите нужный вам лист и нажмите Enter.

_ _ _
Мини-курс "Магия новых функций Excel. Революция в табличных формулах" 🔥

💥Магия табличных формул. Обучение по подписке

Друзья, рад анонсировать новый формат обучения — по подписке. Понимаю, что большие курсы давят своим объемом: когда тебе открываются сразу десятки уроков. Здесь все будет постепенно: каждую неделю минимум одно видео (можно настроить уведомления о новых видео по почте — удобно). И вот так плавно мы будем погружаться в тему табличных формул все глубже и глубже.
Быстро — это медленно без перерывов: через несколько месяцев вы будете на другом уровне. Эти месяцы пройдут в любом случае, а вот получится ли научиться новому — зависит только от вас :)

Какие особенности?
— На вас не давит большое количество видео, уроки появляются каждую неделю. На меня не давит длительность видео (ведь я не выдаю вам сразу 30 уроков по 15 минут, а по одному видео за раз), так что я более подробно, чем обычно, рассматриваю каждую тему с отступлениями вправо и влево и отсылками к другим темам и инструментам. Более глубокое погружение.
— Начнем с основ, но даже в простых темах будут нюансы, о которых, уверен, знали и не все опытные экселье. Постепенно дойдем и до многоэтажных формул, и до создания собственных (пользовательских) функций с помощью VBA и LAMBDA-функций.
— Видео свежие, то есть все обновления будут учтены.
— Подходит для всех версий Excel. Снимаю в интерфейсе Excel на русском, но буду озвучивать названия функций на английском. Так как говорить будем про формулы, то 95% информации и функций актуально и для Google Таблиц, многое и для российского Р7.
— Как всегда — хороший монтаж без шумов, пауз, с приближением экрана и стрелочками-кнопочками. Ко всем видео — исходные и готовые файлы. Можно (и нужно) повторять самостоятельно и смотреть готовые решения.

Подписаться можно тут:
https://sponsr.ru/excel_magic/

Три видео уже доступны подписчикам: обзор книг и сравнение формул/макросов/Power Query, урок про ссылки на ячейки, имена и стиль ссылок R1C1, урок про ссылки на умные таблицы, другие листы и книги.

Один из уроков в открытом доступе. Можно посмотреть и оценить формат.

Первые 100 подписчиков получают цену с хорошей скидкой ;) Такой цены больше не будет.

С любыми вопросами приходите в личные сообщения или на почту [email protected].