Уникальные пары значений

Нам нужны уникальные пары значений. Например, пары "Клиент" — "Продукт" (полный список клиентов и продуктов — кто что покупал)

Если столбцы рядом — то можно просто сослаться функцией УНИК / UNIQUE на эти два столбца.

Если они не рядом — то предварительно выбрать их функцией ВЫБОРСТОЛБЦ / CHOOSECOLS. Например, если нам нужны первый и третий столбцы:
=УНИК(ВЫБОРСТОЛБЦ(Сделки;1;3))

Сортируем данные по имени, даже если это не первое слово в ячейке

Дано: хотим сортировать в режиме реального времени таблицу по именам. Но имена у нас в столбце с фамилией и именем — на втором месте, после пробела. То есть просто сортировать по этому столбцу не получится — будет сортировка по первому слову (точнее, всему тексту, Фамилия+имя).

Выход: сортируем функцией СОРТПО / SORTBY по виртуальному столбцу с именами.

Виртуальный столбец получим функцией ТЕКСТПОСЛЕ / TEXTAFTER — будем извлекать текст после пробела. Но не для отдельной ячейки, а сразу для всего столбца ФИО.

Как водится с новыми функциями, магия доступна в Microsoft 365 / Excel Online.

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

Первый вариант — формулами (любая версия, быстро и на один раз)

Закрепляем верхнюю строку, если еще не)
Идем в конец диапазона (Ctrl+End или Ctrl+стрелка вниз по заполненному столбцу)

Выделяем столбец с пустыми ячейками (можно зажать Shift с активированной последней пустой ячейкой и кликнуть на заголовок)

Нажимаем F5 и далее «Выделить» — «пустые ячейки»

Вводим формулу — нажимаем «равно» и на стрелку вверх
То есть ссылаемся на ячейку сверху (это R[-1]C в нотации R1C1 или =A2 в привычном стиле A1 — для формулы в ячейке A3)

И нажатием Ctrl+Enter вводим формулы во все выделенные ячейки (а это, вспомним, только пустые!)

То есть в каждой пустой теперь ссылаемся на ячейку сверху

Дальше можно формулы скопировать и вставить как значения (Ctrl+C и Ctrl+Shift+V / Ctrl+Alt+V)

Алгоритм в коротком видео (без звука).

Второй вариант — Power Query (2010-2013 с надстройкой, 2016+, если нужно неоднократно применять такое и, возможно, другие манипуляции с источником данных)

Добавляем данные в Power Query (Данные — Из таблицы / диапазоны).
Правой кнопкой по столбцу — Заполнить — Вниз.

Третий вариант — макросы (любая версия, если нужно часто применять в работе — работает мгновенно по нажатию кнопки/сочетанию клавиш)
Про макросы напишем в следующем посте!

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

Не забывайте, что действие макросов через Ctrl+Z отменить нельзя!

Как добавлять макросы в личную книгу:
https://t.me/lemur_excel/30

Как заполнить пустые ячейки (вниз, то есть значениями сверху): макросы

Здесь может быть много вариантов.
Допустим, вы хотите заполнять все пустые ячейки в активном диапазоне — это тот диапазон, который будет выделен по нажатию Ctrl+A (до пустых строк/столбцов).
К нему в коде можно обращаться так:

ActiveCell.CurrentRegion

Вся команда будет выглядеть так: выделяем пустые (как в предыдущем варианте вручную делали через F5 — Выделить) — это метод SpecialCells с параметром (типом выделяемых ячеек) xlCellTypeBlanks. И вставляем во все пустые формулу R[-1]C — то есть ссылаемся на ячейку сверху. Это один из множества примеров того, как формулы со стилем ссылок R1C1 пригождаются в макросах. Не переживайте, если у вас стиль ссылок A1, формулы на листе будут выглядеть именно так: =A2, =A3 и так далее.

ActiveCell.CurrentRegion.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"

Так как при отсутствии пустых ячеек в диапазоне будет выдаваться ошибку (ибо выделять будет нечего) — лучше добавить строку для игнорирования ошибок
On Error Resume Next

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

ActiveCell.CurrentRegion.Value = ActiveCell.CurrentRegion.Value

———
Если вы хотите заполнять ячейки только в текущем столбце от активной ячейки вниз, то CurrentRegion не подойдет.
В таком случае лучше взять диапазон от активной ячейки (ActiveCell) до последней ячейки в активной области листа (на нее можно ссылаться так — UsedRange).

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

АктивнаяЯчейка. Увеличиваем (строк на листе — строка активной ячейки + 1)

Например, активна ячейка A10. На листе 3000 строк. Значит, мы добавляем к активной ячейке 3000-10+1 = 2991 строку и получаем диапазон A10:A3000 (в Resize указывается общее число строк (и столбцов во втором аргументе, если нужно), которое нужно включить в получаемый диапазон).

Узнать число строк в диапазоне можно с помощью свойства Rows, строку активной ячейки — Row.

Все вместе будет выглядеть так:

ActiveCell.Resize(ActiveSheet.UsedRange.Rows.Count - ActiveCell.Row + 1).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"

———
Оба макроса в прикрепленном файле в соседнем посте!

Коллеги порадовали хорошими новостями — первый тираж "Магии таблиц" ушел менее чем за полгода — и издательство будет делать новый, уже 3000 экземпляров (первый был 2500) 🔥

Из первого тиража книги еще остаются на маркетплейсах, а также в розничных магазинах (как Читай-город или Буквоед), если хотите сделать полезный подарок друзьям/коллегам — поспешите! Новый тираж в любом случае будет печататься пару месяцев и до НГ, естественно, книг не прибавится.

На сайте издательства (там же электрическая книга)
Озон
Wildberries

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

Убираем выбросы формулой: например, 10% самых маленьких и 10% больших заказов.

Для этого: посчитаем (для расчета числа значений используем СЧЁТЗ / COUNTA), а сколько вообще в таблице заказов и умножим на 10% или 0,1, чтобы получить число заказов, которые нужно убрать:
СЧЁТЗ(столбец из таблицы)*10%

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

СОРТ(Таблица; номер столбца)

И далее дважды применяем функцию СБРОСИТЬ / DROP — она убирает строки из начала (если второй аргумент положительный) или из конца (если аргумент отрицательный) массива/таблицы/диапазона.
Нам придется два раза — первые и последние 10%.
Здесь число строк = сколько строк нужно удалить (вычислили на первом шаге):
=СБРОСИТЬ(СБРОСИТЬ(отсортированная таблица; число строк); -число строк)

Все вместе для нашей таблицы будет выглядеть так:
=СБРОСИТЬ(СБРОСИТЬ(
СОРТ(Заказы;3);
СЧЁТЗ(Заказы[Код заказа])*10%);
-СЧЁТЗ(Заказы[Код заказа])*10%)

Добавляем к дате день недели и выделяем выходные

Допустим, мы с вами хотим видеть в каждой дате день недели - не "01.01.2023", как по умолчанию, а "01.01.2023 Вс".

Для этого заходим в формат ячеек (Ctrl + 1) и добавляем к формату "ДДД" (DDD). Это краткое обозначение дня недели ("Вс"). Для полного ("Воскресенье") понадобится код "ДДДД" (DDDD).

Ну а чтобы выделить цветом выходные (или другие дни) - воспользуемся условным форматированием (Conditional Formatting).
Зададим правило с формулой, а в ней будем использовать функцию ДЕНЬНЕД / WEEKDAY.
Она возвращает порядковый номер дня недели. Чтобы нумерация была привычной для нас с вами, добавьте второй аргумент, равный двойке:

=ДЕНЬНЕД (ячейка с первой датой в диапазоне; 2)

Тогда понедельнику будет соответствовать единица (иначе - воскресенью), вторнику - двойка и так далее.

И остается добавить условие - день недели у нас должен быть больше 5 (то есть 6 или 7, суббота или воскресенье), чтобы ячейка заливалась цветом.
Все показываем на видео!

Вытаскиваем из даты всякое разное: подборка функций и формул

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

Ловите пачку полезных формул для работы с датами в Excel!

Конец месяца:

=КОНМЕСЯЦА(дата;0)

Начало месяца:

=КОНМЕСЯЦА(дата;-1)+1

Месяц:

=МЕСЯЦ(дата)

День:

=ДЕНЬ(дата)

Год:

=ГОД(дата)

День недели цифрой:

=ДЕНЬНЕД(дата;2)

День недели текстом:

=ТЕКСТ(дата;"ДДДД")

10 рабочих дней от даты:

=РАБДЕНЬ(дата;10)

Рабочих дней в месяце:

=ЧИСТРАБДНИ(КОНМЕСЯЦА(дата;-1)+1;КОНМЕСЯЦА(дата;0))

Кол-во вторников в месяце:

=ЧИСТРАБДНИ.МЕЖД(КОНМЕСЯЦА(дата;-1)+1;КОНМЕСЯЦА(дата;0);"1011111")

Квартал - вариант 1:

=ЦЕЛОЕ((МЕСЯЦ(дата)+2)/3)

Квартал - вариант 2:

=ВЫБОР(МЕСЯЦ(дата);1;1;1;2;2;2;3;3;3;4;4;4)

Номер недели (ГОСТ):
=НОМНЕДЕЛИ.ISO(дата)

Давайте продолжим тему ПРОСМОТРов. И поговорим о старой функции LOOKUP / ПРОСМОТР.

Функция по синтаксису похожа на новую XLOOKUP / ПРОСМОТРX, о которой мы писали выше. Но она как раз была давно — в Excel даже есть примечание, что функция LOOKUP / ПРОСМОТР остается для совместимости. И у нее есть минусы :она требует постоянной сортировки данных, не особо подходит для поиска текста.

Поэтому лучше использовать VLOOKUP, XLOOKUP или INDEX+MATCH. Во всяком случае, для объединения таблиц по текстовому ключу.
Но все же в некоторых экзотических случаях LOOKUP используют до сих пор. Например, для нечеткого текстового поиска, когда нужно находить в названиях какое-то слово и заменять все значения с этим слово на одно и то же типовое (исправлять разные наименования компаний или товаров, например) — такой пример есть в статье.

Группировка нескольких текстовых элементов в сводной

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

Для этого:
1 Выделяем несколько элементов (зажав клавишу Ctrl);

2 Щелкаем правой кнопкой и в контекстном меню выбираем Группировать / Group
или
2 Нажимаем на ленте на вкладке "Анализ сводной таблицы" (PivotTable Analyze) — "Группировка по выделенному" (Group Selection)

3 Щелкаем на название группы (по умолчанию будет "Группа1") и переименовываем.

Если применяете гистограммы, обращайте внимание на ширины столбцов!

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

В нашем примере получается, что за счет широкого столбца B 115 тыс. во Владимире в 2020 году выглядит как вдвое большее значение, чем те же 115 тыс. в Саранске в 2021 году!

Так что если вставляете гистограммы в диапазон из 2 и более столбцов, делайте ширину этих столбцов абсолютно одинаковой (для этого выделите все столбцы и поменяйте ширину любого — она будет применена ко всем выделенным столбцам).

Код макроса для создания отдельных файлов для каждого значения в выбранном столбце

Макрос: создаем по отдельному файлу для каждого продукта/города/клиента (для каждого уникального значения в столбце)

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

1 В папке с вашей книгой Excel будет создана папка с заголовком ("Продукт", если у вас была активна ячейка с таким заголовком перед вызовом макроса)

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

Как добавить макрос в личную книгу макросов, чтобы он был доступен при работе с любыми файлами Excel — читайте здесь. Сам макрос в соседнем сообщении (сохраняйте файл с макросом, заходите Alt+F11 в редактор макросов, добавляйте файл в личную книгу макросов PERSONAL.xlsb — для этого выберите Import File в контекстном меню по правой кнопке мыши)

В очень коротком видео со звуком показываю пример, как именно происходит магия.

Другие макросы:
Макрос для сравнения двух файлов (книг Excel)
Макрос: создаем оглавление в книге
Макрос: удаляем пустые листы
Два варианта макросов для заполнения пустых ячеек

Столбик в гистограмме можно заменить изображением

Для этого скопируйте изображение (Ctrl + C), выделите диаграмму, выделите нужный столбик (просто щелкните еще раз после выделения диаграммы на нужный элемент — вы поймете, что он выделен, когда круглые маркеры по углам останутся только у этого столбика).

И Ctrl + V — вставляем изображение.

После этого можно зайти в панель форматирования (Ctrl + 1), чтобы уменьшить боковой зазор между столбиками. Тогда они станут шире. В нашем случае это поможет с пропорциями!

Динамический именованный диапазон

Диапазонам и ячейкам в Excel можно присваивать имена (Ctrl+F3 или поле "Имя слева от строки формул или вкладка "Формулы" на ленте).

Что если мы хотим создать имя, за которым будут скрываться все заполненные ячейки в столбце, начиная с A2, например?
То есть чтобы одним и тем же именем (например, Компании, как в примере) назывался диапазон динамического размера — в зависимости от числа заполненных ячеек в столбце.

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

=$A$2:функция(...)

Это функции ЕСЛИ / IF, ВЫБОР / CHOOSE, ЕСЛИМН / IFS, ДВССЫЛ / INDIRECT, СМЕЩ / OFFSET, ПЕРЕКЛЮЧ / SWITCH и ПРОСМОТРX / XLOOKUP.

Для нашей задачи можно использовать ИНДЕКС — будем с помощью нее получать адрес последней заполненной ячейки в столбце A. Чтобы узнать, какая строка последняя — посчитаем, сколько заполненных ячеек в столбце A с помощью СЧЁТЗ / COUNTA.

=СЧЁТЗ($A:$A)

Значение из последней заполненной ячейки можно получить так:

=ИНДЕКС($A:$A;СЧЁТЗ($A:$A))

Но мы засунем эту конструкцию после ссылки на первую ячейку диапазона, и она не будет возвращать значение из последней заполненной ячейки, а ссылку на нее:

=$A$2:ИНДЕКС($A:$A;СЧЁТЗ($A:$A))

Все это остается отправить в поле "Диапазон" вновь созданного имени.

Навигация по листам в книге Excel

В книге много листов?
Щелкните правой кнопкой мыши на стрелки в левом нижнем углу. Откроется список всех листов. Там смотреть удобнее, чем просто по ярлыкам.

А к следующему и предыдущему листу можно переходить с помощью сочетаний клавиш Ctrl + PgDn и Ctrl+PgUp.

Отключаем фильтр в отдельных столбцах

Такая секретная магия доступна только через макросы.
Для отключения кнопки у отдельного столбца достаточно одной строчки кода:

ActiveCell.AutoFilter Field:=N, VisibleDropDown:=False


Где N — номер столбца в фильтруемом диапазоне.

Удивите коллег таким нестандартным фильтром 😺

Два нажатия Ctrl+Shift+L (или кнопки Фильтр на ленте) вернет все обратно.

Друзья, новость про курс "Магия Excel" в МИФе: записали с Лемуром новый модуль про Power Pivot.

Это вводная информация для знакомства с моделью данных Excel (Power Pivot):
— Как настроить отношения между таблицами и построить сводную на основе нескольких таблиц, даже не открывая Power Pivot
— В чем преимущества сводной на основе модели данных: подсчет уникальных значений, использование функций DAX в мерах (можно, например, вычислить медиану), превращать сводную в формулы (функции кубов) и другие плюсы
— Импорт данных из текстовых файлов (в модуле пример с 2 миллионами строк, что в самом Excel даже не вставить), других книг Excel, любых источников — через Power Query (пример с курсами валют с сайта ЦБ)
— Вычисления в Power Pivot (вычисляемые столбцы и меры)
— Power Map: 3D-карты на основе модели данных

Как и во всех остальных модулях курса, помимо видео есть файлы-примеры в исходном и готовом состоянии и домашняя работа.

Всего в курсе теперь 66 видео и все темы — от форматирования и формул до LAMBDA, визуализации данных и Power Query с Power Pivot. Новые функции тоже есть, и новые видео постоянно добавляются — например, анонсированные в ноябре 2023 функции GROUPBY и PIVOTBY появились в виде видеоурока в курсе уже ... в ноябре 2023.

⭐️Слушатели поставили 436 оценок урокам курса. Из них только 4 четверки и ни одной двойки/тройки.

https://www.mann-ivanov-ferber.ru/courses/magicexcel/

Ловите скидку:
Код LEMURY
-35% до 4 февраля включительно

Панель быстрого доступа работает не только в самом Excel (и других приложениях Office!), но в и окнах Power Query и Power Pivot

Все работает аналогично: правой кнопкой по команде, которую используете часто —> Добавить на панель быстрого доступа (Add to Quick Access Toolbar)

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

Подробнее про панель быстрого доступа в интерфейсе Excel:
Сыграть на Alt'е - доступ к командам на ленте с помощью клавиатуры
Закрепление верхней строки в Excel в один клик: добавляем команды на панель быстрого доступа
Как добавить макрос на панель быстрого доступа