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

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

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

Отлична книга, великолепная подача, все «разжевано». Можно читать как подряд, так и местами, которые нужны. Пока это самое понятное пособие по 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 Таблицах).

Объединяем умные таблицы в одну: формулы и 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

И вжух!