Показать сразу несколько листов

Относительно новая штука (появилась в конце 2020 года для подписчиков Microsoft 365) — можно показать сразу несколько скрытых листов.
Когда вы щелкаете правой кнопкой по любому ярлыку видимого листа и выбираете "Показать..." (Unhide...) — появляется этот список.
Теперь в нем можно выбрать несколько отдельных листов (с зажатой Ctrl) либо список идущих подряд листов (для этого зажмите Shift и щелкните на лист, до которого включительно вам нужно выделить листы в списке).

Форматирование устаревших значений

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

Для этого достаточно включить флажок "Формат устаревших значений" (Format Stale Values) на вкладке "Формулы".
Работать это будет, конечно, при ручном пересчете формул.

Друзья, если пользуетесь "Дзеном", теперь можно подписаться на нас и там — все новые публикации будут выходить и там тоже!

https://dzen.ru/excel_magic

Не устаем рассказывать про одно из наших любимых сочетаний Ctrl+Backspace — это возвращение к активной ячейке. Удобно при работе с формулами, расширенным фильтром, другими окнами.

Например. Многие из вас знают одно из любимых Лемуром сочетаний клавиш Ctrl + Shift + стрелки (⌘ + ⇧ + стрелки).
Оно позволяет (если ловкости лап хватит все это нажать одновременно) выделить диапазон до последней заполненной ячейки в направлении стрелки. В том числе при вводе формулы — можно выделить диапазон в аргументе функции, как на видео.

Но в результате мы можем переместиться довольно далеко от формулы. И чтобы вернуться к текущей ячейке (в которую мы эту самую формулу вводим, не заканчивая при этом ввод формулы), пригодится чуть менее часто используемое (ну, как нам кажется) сочетание — Ctrl + Backspace (⌃ + Delete).

В новом Excel (365 или 2021) можно делать то, что делают сводные — формулами 😺

Если хотим считать сумму продаж по каждому клиенту (или другому условию — городу, товару), можно вывести всех клиентов функцией УНИК / UNIQUE, и посчитать сумму продаж по каждому через СУММЕСЛИМН / SUMIFS. При этом мы ссылаемся на список клиентов, выводимый функцией УНИК, через ссылку с решеткой (писали о таком типе ссылок выше), то есть при появлении новых клиентов в исходных данных все обновится автоматически.

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

Подробнее в коротком видео со звуком.

P.S. В старых версиях тоже можно решить эту задачу, но без обновления: получить список уникальных клиентов через инструмент "Удалить дубликаты", протянуть по каждому отдельную функцию СУММЕСЛИМН. Так что там лучше сводными :)

Как вычислить стаж (возраст, разницу в годах), если в данных то полная дата, то один год?

Вот такой вопрос был от одного из участников корпоративного обучения.
Задачка решается в общем виде так:
Проверяем функцией ЕСЛИ / IF, какие данные у нас — год или дата, и в зависимости от этого просто вычитаем из сегодняшнего года тот, что в ячейке, либо считаем разницу между датой в ячейке и сегодняшней датой.

Как выяснить, что в ячейке?
Можно по числу цифр — функция ДЛСТР / LEN — если их 5, то дата, если 4, то год. Но тогда получится, что 9999 будет считаться годом. А мы помним, что любая дата в Excel — это число (число дней с 1 января 1900). Так что 9999 — это 17 мая 1927, и если у вас в данных могут быть такие даты рождения, то лучше второй способ).

Можно просто по числу — если оно больше 2100, то это дата. Тут порог надежнее: 2100 — это 30 сентября 1905, вряд ли у вас будут такие даты.

Итак, задачу решили такой формулой:

=ЕСЛИ(ячейка>2100;РАЗНДАТ(ячейка;СЕГОДНЯ();"y");ГОД(СЕГОДНЯ())-ячейка)

Проверяем значение в ячейке и если оно больше 2100, то с помощью РАЗНДАТ / DATEDIF находим разницу в годах (

"y"

—третий аргумент — полные годы) между датой и сегодняшним числом.
Если меньше — значит, это год. Вычитаем из текущего года — который вычисляем с помощью функции ГОД / YEAR от текущей даты — год в ячейке.

Сравнение списков

Небольшое видео (со звуком) для новичков.

Сравниваем списки двумя способами:
— через условное форматирование (визуально)
— формулами (с помощью функции СЧЁТЕСЛИ / COUNTIF)

Также обсуждаем, что делать, если вдруг в рамках одного списка будут дубликаты.

Заходит как-то в бар (Formula Bar) курьер (а точнее, какой-то другой моноширинный шрифт)...

Небольшое, но приятное обновление в Excel: теперь в строке формул моноширинный шрифт!

Такие шрифты в основном используются в программировании; название намекает, что все символы одной ширины, что упрощает выравнивание, чтение кода. А чем длинные формулы в Excel не код?

Обновление появилось у тех, кто подписан на бета-канал обновлений и потихоньку будет выкатываться на всех пользователей, получающих обновления.

Заодно Лемур хочет вам напомнить, что с помощью Alt+Enter можно вставлять в формуле перенос строки и таким образом делать сложные формулы читабельнее.

Официальная новость тут.

А вот еще одна новинка, но несопоставимого масштаба... код Python будет работать прямо в Excel!

Пока все только начинается — с бета-каналом обновлений группа Python не появилась сама, ее можно добавить как на скриншоте — через параметры ленты инструментов. Но все равно ничего еще не работает.

Работать будет прямо в ячейках — можно будет начинать ввод с =PY в формуле или через ленту инструментов.
А вычисления будут производиться в облаке. То есть будет нужен доступ к сети.

Для ссылок на объекты (ячейки и диапазоны, данные в "умных" таблицах) Excel будет использоваться функция Python xl().

Ссылки:
Официальная новость
Общие сведения о Python в Excel
Начало работы с Python в Excel
Python in Excel (на странице дистрибутива Anaconda)

Формулы Excel для новичков: разбираемся с разными типами ссылок (относительные, абсолютные и имена)

Вашему вниманию небольшой фрагмент из книги "Магия таблиц" для тех, кто только начинает работать с формулами и хочет разобраться наконец с долларами и ссылками на ячейки в Excel (или Google Таблицах).

Объединяем умные таблицы в одну: формулы и Power Query

В видео разбираем такую задачу: собрать данные из нескольких умных таблиц.

Если у вас Microsoft 365, то можно наслаждаться новыми формулами и использовать функцию ВСТОЛБИК / VSTACK. Так же в видео разбираем, как с ее помощью в сочетании с функцией ФИЛЬТР / FILTER фильтровать данные "в режиме реального времени" и добавить к результату фильтрации заголовки.

Если версии 2010 и новее, то можно с помощью Power Query объединить таблицы в один запрос и далее анализировать данные вместе с помощью сводной таблицы или просто выгрузить на лист как одну таблицу.

Функция ISOMITTED / ПРОПУЩЕНО: добавляем к пользовательским функциям необязательные аргументы

Вашему вниманию кусочек видео из будущего нового модуля курса "Магия Excel", посвященного функции LAMBDA.
LAMBDA позволяет создавать собственные функции. Синтаксис у нее такой:

=LAMBDA([переменная]; … ; [переменная]; формула)

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

=LAMBDA(план ; факт ; факт / план - 1)

В самих ячейках LAMBDA работать напрямую не будет — ведь тут параметры, а не конкретные значения / ячейки. Вы можете ее проверить, добавив конкретные значения в скобках после функции:

=LAMBDA(план ; факт ; факт / план - 1)(B2;C2)

Но в целом все это затевается ради того, чтобы использовать новую функцию под ее именем уже без всяких лямбд. Для этого нужно сохранить ее в диспетчере имен (Ctrl + F3) под любым именем, какое вы хотите присвоить этой функции — например, “ТемпПрироста”. И дальше использовать эту функцию в пределах книги (а если хочется перенести ее в другую — можно скопировать создать пустой лист в книге и скопировать/переместить его в другую книгу — это приведет к переносу имен, а значит, и функции).

И в функциях можно даже создавать необязательные аргументы — для этого и нужна ISOMITTED / ПРОПУЩЕНО — она возвращает ИСТИНА / TRUE, когда аргумент пропущен. В видео пример создания пользовательской функции с необязательным аргументом.

Если в сводной таблице нужно произведение нескольких столбцов, обычное вычисляемое поле не подойдет: в нем все значения будут сначала суммироваться, а потом умножаться.
А нужно считать произведение в каждой строке (например, проданные штуки умножать на цену) и потом суммировать результаты.

Для этого нужно либо добавлять столбец в исходных данных, где вычислять нужное произведение, либо построить сводную на основе модели данных (если в вашей версии она, то есть Power Pivot, есть) и использовать меры. В мерах можно использовать функции DAX, в частности, SUMX, которая вычисляет выражение построчно и только потом суммирует — смотрим пример в видео!

Вставили текстовые значения откуда-то и хотите быстро объединить?

Можно использовать команду "Выровнять" (Justify).

Сделайте ширину столбца такой, чтобы все значения, объединенные вместе в один текст, поместились в одну строку.

После этого выделяйте ячейки и выбирайте команду "Выровнять" на вкладке "Главная" в коллекции "Заполнить".
Home — Fill — Justify

И вжух!

Код макроса для удаления пустых листов

Макрос: удаляем пустые листы

На примере простой задачки (удалить все листы в текущей книге, на которых нет ни одного значения в ячейках) рассматриваем, как создать макрос в личной книге макросов и затем добавить на панель быстрого доступа, чтобы он всегда был под рукой.

Код макроса — выше в файле формата .bas — для импорта просто зайдите в редактор VBA (Alt + F11) и нажмите там Ctrl + M для импорта.

Если личной книги макросов еще нет, включите запись макроса, выберите сохранение в личную книгу макросов (в видео это есть) и сразу остановите запись. Тогда будет создана личная книга макросов PERSONAL.XLSB. Макросы, которые лежат в ней, доступны вам в любых книгах Excel.

Друзья, наша с Лемуром книга тем временем стала №1 в жанре "Руководства по пользованию программами" в Лабиринте — в очень достойной компании😺 (особое внимание в этом топе обращаем на "Библию пользователя", хоть она и по 2019 Excel, книгу по визуализации Дика Куслейки и, конечно, книгу про сводные Билла Джелена — очень полезные вещи — про эти и другие книги можете почитать в обзоре)

Если уже прочитали, пожалуйста, поставьте оценку / оставьте отзыв в любом из магазинов, мы будем очень благодарны!

А купить можно тут:
На сайте издательства
Book24
Лабиринт
Озон
Литрес (электрическая)
Wildberries
И в оффлайне — например, в Библио-Глобусе или в регионах в Читай-городе, Буквоеде и других магазинах

Калькулятор в Excel... Ну, вообще-то строка формул (точнее, сами формулы, в любой ячейке) — вполне себе калькулятор, но если вы знаете толк в извращениях, вам нужны кнопочки M+, M-, CE и вы хотите вызывать калькулятор WIndows из Excel — почему нет 😺
(шутки шутками, но кнопок там намного больше, потому что есть разные режимы — инженерный, графики, вычисления с датами, преобразования)

Заходим в параметры, панель быстрого доступа — и там из списка всех команд выбираем калькулятор и на эту самую панель быстрого доступа добавляем. Теперь у вас всегда наверху будет кнопка для открытия калькулятора (и еще будет сочетание клавиш Alt + цифра для него же).

Options — Quick Access Toolbar — All Commands — Calculator — Add.

Извлекаем из таблицы строки с самой большой и маленькой сделкой (наименьшим и наибольшим числом)

С новыми функциями это получается просто: сначала сортируем таблицу по сделкам (по аналогии можно сортировать по датам, тогда вы сможете взять самую старую и новую строки) с помощью SORT / СОРТ:

=СОРТ(таблица; номер столбца, по которому сортируем)

Если нужно по убыванию, то задаем третий аргумент, равный -1.

Ну а далее, не выводя на лист отсортированный результат, сразу отправляем его внутрь функции CHOOSEROWS / ВЫБОРСТРОК — и берем первую (1) и последнюю (-1) строки.
=ВЫБОРСТРОК(СОРТ(таблица; номер столбца для сортировки);1;-1)

В общем виде ВЫБОРСТРОК имеет такой синтаксис:
=ВЫБОРСТРОК(диапазон / массив; номер строки, которую извлекаем ; [еще номер строки]; ...)

То есть можем извлечь и одну строку, и несколько — перечисляем столько номеров, сколько нужно.
А если нужны все нечетные строки, например, с 1 по 100, можно использовать функцию ПОСЛЕД / SEQUENCE, чтобы не вводить столько чисел вручную:
=ВЫБОРСТРОК(диапазон; ПОСЛЕД(50;;1;2))

Мгновенное заполнение — один из самых простых и полезных инструментов Excel

Появилось мгновенное заполнение в Excel 2013. Этот инструмент позволяет преобразовать данные или извлечь какой-то фрагмент: достаточно задать 1-2 образца того, что надо получить из исходного столбца (или нескольких столбцов), и мгновенное заполнение заполнит весь столбец значениями, исходя из заданного вами паттерна (шаблона).

Это может быть не очевидным для тех, кто только знакомится с этим инструментом, но мгновенное заполнение анализирует всю строку, все смежные столбцы (то есть нужно вводить данные в любом соседнем столбце с данными, без перерыва в виде пустого столбца). И это значит, что можно обрабатывать (извлекать и объединять) данные из нескольких столбцов.