Еще один вопрос с недавнего вебинара:
Был столбец с пунктами типа 10.1, 10.2, 10.3...10.10, 10.11, формат текстовый... Поменял формат на числовой, после чего часть цифр превратилась в пятизначные типа 45430, 44438, часть в даты.

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

'10.1

А если нужно уже имеющиеся данные превратить в порядковые пункты, можно использовать такую формулу:
=ДЕНЬ(ячейка) & "." & МЕСЯЦ(ячейка)
За счет того, что мы склеиваем несколько значений в одну текстовую строку (с помощью амперсанда), результат будет текстом.

Оператор неявного пересечения @

Вряд ли вы будете использовать этот оператор в формулах в обычных диапазонах (за пределами "умных" таблиц), но можете с ним столкнуться.
Произойти это может, когда вы откроете книгу в новых версиях (Excel 2021 / 365) с формулами, сделанными в старых версиях.

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

В новых версиях ссылка на диапазон — это сразу ссылка на диапазон, а не на пересечение с ним. То есть
=A1:A10 — это сразу весь диапазон A1:A10, формула вернет результатом размером в 10 строк.
А
=@A1:A10 — это пересечение с диапазоном A1:A10, то есть одно значение из той же строки, в которой находится формула.

Следующая формула в новых версиях будет возвращать диапазон из 12 значений — разницу между парами значений в столбцах C и B:
=C2:C13-B2:B13

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

P.S. Ну а в "умных"таблицах (чтобы создать такую, нажмите Ctrl + T) этот оператор используется в любой версии по умолчанию, но не с диапазоном, а с названием столбца в квадратных скобках. Например, [@План] — ссылка на столбец с заголовком "План" на эту же строку (ту, где формула).

Up Up and Array!: Dynamic Array Formulas for Excel 365 and Beyond

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

Речь про функции SORT, SORTBY, FILTER, UNIQUE, SEQUENCE и про функции для работы с массивами — RANDARRAY, TOCOL, TOROW, WRAPCOLS, WRAPROWS, VSTACK, HSTACK, CHOOSECOLS, CHOOSEROWS, EXPAND, TAKE и DROP, а также про текстовые функции TEXTSPLIT, TEXTBEFORE, TEXTAFTER.
Тут максимум деталей и нюансов (для новичков) и много примеров применения новых функций, в том числе их комбинаций (это будет интересно и новичкам, и продолжающим) — насколько же проще стало решать многие задачи!
Есть файлы с примерами.

Ну а полный обзор табличных книг вместе с этой новинкой — по постоянному адресу:
https://teletype.in/@renat_shagabutdinov/excellent_books

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

Общая логика формулы такая:
Берем первый кусочек, приклеиваем (&) к нему нужный символ, который повторяем много раз с помощью функции ПОВТОР / REPT, и потом справа приклеиваем (&) второй кусочек.
Сколько раз повторять символ (в нашем случае дефис)?
Берем какое-нибудь число, которое будет больше, чем наши текстовые фрагменты, и из него вычитаем число символов (ДЛСТР / LEN) в наших склеиваемых текстовых фрагментах. Таким образом получится, что число символов-заполнителей везде будет разное, но общее число символов одинаковое.

P.S. Магия сработает только с моноширинным шрифтом — например, с Courier New.

Тем временем книга получила статус "Хит" на Озоне и "Бестселлер", "Хит продаж" на Литресе!

А вот и первые отзывы (пока на Озоне):
отличная подача материала. намного дешевле курса!

Большое количество полезных фишек для ускорения работы

Отлична книга, великолепная подача, все «разжевано». Можно читать как подряд, так и местами, которые нужны. Пока это самое понятное пособие по Excel, которое я читала

Собрали для вас с Лемуром ссылки на основные магазины, где можно купить:

На сайте издательства
Book24
Лабиринт
Озон
Литрес (электрическая)
Wildberries

Если уже прочитали, напишите отзыв тоже, будем признательны 😺

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

Формула будет выглядеть так:

=СОВПАД(первая ячейка диапазона; ПРОПНАЧ(первая ячейка диапазона))

Функция СОВПАД / EXACT проверяет совпадение с учетом регистра. А ПРОПНАЧ / PROPER меняет регистр текста — делает первые буквы каждого слова заглавными.
Таким образом, мы проверяем, совпадает ли текущее значение ячейки с ним же, но в "правильном" регистре.
И если не совпадает, то функция СОВПАД возвращает ЛОЖЬ / FALSE — и проверка данных ругается.

Если дата в ячейках записана как текстовое значение вида ДДММГГГГ, без точек/дефисов/других разделителей, можно превратить такой текст в настоящую дату формулой:

=ДАТА( ПРАВСИМВ( ячейка с датой; 4) ; ПСТР (ячейка; 3; 2) ; ЛЕВСИМВ (ячейка; 2) )

Функция ДАТА / DATE возвращает дату, заданную тремя параметрами — годом, месяцем и днем.
Ее аргументы мы получаем текстовыми функциями:
Год — извлекая первые цифры цифры с помощью ПРАВСИМВ / RIGHT (она возвращает первые N символов из текстовой строки)
Месяц — извлекая два символа, начиная с третьего, с помощью ПСТР / MID.
День — последние две цифры с помощью функции ЛЕВСИМВ / RIGHT.

Связанные выпадающие списки

Если вы хотите, чтобы список возможных значений в одном списке определялся на основе другого — можно использовать функцию ФИЛЬТР / FILTER для формирования списка — например, товаров только одной, выбранной категории.

И далее можно на эту формулу ссылаться из проверки данных (Data Validation) с помощью нового типа ссылок с решетками, о которых мы рассказывали здесь:
https://t.me/lemur_excel/165

Подробно — в коротком видео со звуком. Отдельным сообщением прикрепляем файл с формулой выше.

P.S. Радость эта будет доступна, увы, только в новом Excel.

Друзья, если вы работаете не только в Excel, но и в Google Таблицах, то подписывайтесь на одноименный, дружественный и вообще лучший канал по теме. К которому в комплекте идет чат, населенный настоящими профессионалами, готовыми помочь (при понятной формулировке задачи и наличии примера 😺)
https://t.me/google_sheets
@google_spreadsheets_chat

Вот на днях выложил там большую статью про функцию IMPORTRANGE, которая позволяет загружать данные из одной таблицы в другую:
Главная статья импорта

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

Относительно новая штука (появилась в конце 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 Таблицах).