Ссылка на несколько листов и функция 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].

Не показывать нули в ячейках: разные варианты

На уровне всей книги или отдельного листа: заходим в Параметры Excel — Дополнительно — выбираем лист или книгу — Показывать нули в ячейках, которые содержат нулевые ячейки
(этот вариант на скриншоте)

На уровне диапазона: выделяем диапазон — Ctrl + 1 — (все форматы) — задаем пользовательский формат, в котором третий формат (для нуля) оставляем пустым, например:

0;-0;

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

На уровне поля сводной таблицы:
правая кнопка мыши по любому значению в области значений — Числовой формат (не "Формат ячейки", т.к. это форматирование конкретной ячейки, а вот "Числовой формат" — всего поля) — и там тоже задаем пользовательский формат ("все форматы").

--
💥Магия табличных формул — обучение по подписке. Всего 390 рублей / месяц для первых подписчиков!

Ставили цели на этот год? Обратите внимание, что 13% года уже прошли 😈

Как это можно вычислить и визуализировать?

Используем функцию ДОЛЯГОДА / YEARFRAC. У нее два обязательных аргумента — две даты.

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

=ДОЛЯГОДА(ДАТА(ГОД(СЕГОДНЯ());1;1);СЕГОДНЯ())

Самая лаконичная визуализация прогресса — гистограмма условного форматирования. Просто копируем формулу во вторую ячейку (или ссылаемся на эту ячейку), вставляем гистограмму (Главная — Условное форматирование — Гистограммы), меняем минимум и максимум на ноль и единицу. Можно добавить заливку ячейки другим цветом, как в примере.

--
💥Магия табличных формул — обучение по подписке. Всего 390 рублей / месяц для первых подписчиков!

Переключение дэшборда между днями и неделями — с помощью функции SEQUENCE

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

Статья и пример в Google Таблицах. Но в новом Excel такое тоже можно реализовать — и флажки, и функция SEQUENCE / ПОСЛЕД в наличии!

https://shagabutdinov.ru/blog/tpost/jbrryezom1-pereklyuchenie-deshborda-mezhdu-dnyami-i

--
💥Магия табличных формул — обучение по подписке. Всего 390 рублей / месяц для первых подписчиков!

Вывести все имена и соответствующие диапазоны на лист

Вот как можно сформировать табличку (диапазон) со списком всех или некоторых имен и их диапазонов:

Вкладка "Формулы" — Определенные имена — Использовать в формуле — Вставить имена
Formulas — Defined Names — Use in Formula — Paste Names

А зачем может пригодиться? Если вы применили имя в формуле, а потом удалили это имя (это можно сделать в диспетчере имен, Ctrl + F3), формула будет возвращать ошибку, хотя само имя в формуле останется.

То есть если у вас было =Выручка*Налог, то так оно и останется, "Налог" не будет заменен на ту ячейку, которая названа этим именем.

А если вывести куда-то список имен, то сможете посмотреть, какой диапазон каким именем был назван.

Можно ли отключить такое поведение? Чтобы при удалении имени оно исчезало из формулы, заменялось на диапазон?
Можно. Для конкретного листа:
Файл — Параметры — Дополнительно — Параметры совместимости с Lotus 1-2-3 — Преобразовывать формулы в формат Excel при вводе

--
💥Магия табличных формул — обучение по подписке. Всего 390 рублей / месяц для первых подписчиков!

Функция ЛИСТ / SHEET

Возвращает она порядковый номер (индекс) листа.
И этот номер может меняться. Он зависит от положения листа — они нумеруются от 1 до N, где N — количество листов в книге. Скрытые листы считаются.

Функция без аргументов будет возвращать номер листа, на котором находится:

=ЛИСТ()

С аргументом (ссылкой) будет возвращать номер листа, на который ссылка:

=ЛИСТ(Лист2!A1)

Если лист переместить, то его номер меняется. Соответственно, можно придумать формулу с проверкой. Например, такую, которая будет сигнализировать об ошибке, если лист с оглавлением передвинуть вправо (как на видео):

=ЕСЛИ(ЛИСТ()>1; "Ошибка!Переместите лист в начало книги";"Оглавление")

--
💥Магия табличных формул — обучение по подписке. Всего 390 рублей / месяц для первых подписчиков!

Гистограммы — простой и очень полезный инструмент для визуализации.

Вашему вниманию статья про них:
— Как работают гистограммы. Как их вставлять и что лучше с ними не делать
— Меняем минимум и максимум у гистограмм
— Задаем мин/макс формулой
— Убираем числа, показывая только гистограммы
— Применяем гистограммы в сводной, в том числе только к одному уровню

https://shagabutdinov.ru/tpost/7cfjpyxsj1-gistogrammi-v-excel-prostoi-instrument-v

Задача: посчитать стоимость (то есть перемножить цену и количество) с условием (то есть не по всем подряд строкам)

Если бы просто перемножить два столбца — цена и остатки — то все просто. Берем функцию СУММПРОИЗВ / SUMPRODUCT — она перемножает значения из нескольких массивов, а потом суммирует полученные произведения:

=СУММПРОИЗВ(Прайс[Цена];Прайс[Остатки])

Но нам нужно не все подряд, а, допустим, только строки, в которых встречается определенный бренд — например, Orijen.

Тогда добавим третий аргумент (массив) в функцию. С помощью функции НАЙТИ / FIND будем определять, есть ли искомый бренд в столбце "Название". Если функция выдаст ошибку (проверим это с помощью ЕОШИБКА / ISERROR), значит, бренда нет, а нам нужно, чтобы ошибки не было — так что мы будем превращать ИСТИНА (=ошибка есть, название не найдено) в ЛОЖЬ и наоборот. Таким образом, следующая конструкция выдаст ИСТИНА там, где искомое слово найдено:

НЕ(ЕОШИБКА(НАЙТИ("Orijen";Прайс[Название])))

Но это будет массив из логических значений ИСТИНА и ЛОЖЬ, и мы превратим его в единицы и нули, умножив на -1 дважды:

--НЕ(ЕОШИБКА(НАЙТИ("Orijen";Прайс[Название])))

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

=СУММПРОИЗВ(Прайс[Цена];Прайс[Остатки];--НЕ(ЕОШИБКА(НАЙТИ("Orijen";Прайс[Название]))))

---
💥Магия табличных формул — обучение по подписке. Всего 390 рублей / месяц для первых подписчиков!

Код макроса