Магия Excel

Ссылка на несколько листов и функция SHEETS / ЛИСТЫ Функция SHEETS возвращает число листов в ссылке — ее единственном аргументе. Погодите-ка, какое еще число листов? Разве ссылка не на один лист всегда? Нет, как многие знают, можно ссылаться на группу листов. При вводе формулы просто зажмите Shift и щелкните на ярлык листа, до которого вам нужно ссылаться — получите ссылку вида: =СУММ(Январь:Июнь!A1) (функция/функции могут быть любыми, не только СУММ, разумеется) Такая ссылка — это ссылка на все ячейки A1 на листах от "Января" до "Июня" в том порядке, как их ярлыки идут в книге. То есть если между этими листами добавится новый — он попадет в сумму. А если вы перетащите "Апрель" правее "Июня", то он не будет участвовать в вычислении. Функция SHEETS / ЛИСТЫ позволяет такие ссылки проверять и узнавать, сколько там листов. Например, можно выдавать сумму диапазонов A2:A10 на всех листах, только если в ссылке 12 листов, а иначе — текст с сообщением об ошибке: =ЕСЛИ(ЛИСТЫ(Январь:Декабрь!A1)=12 ; СУММ(Январь:Декабрь!A2:A10); "Ошибка! Проверьте, что все листы расположены в правильном порядке")

Динамический именованный диапазон Диапазонам и ячейкам в 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)) Все это остается отправить в поле "Диапазон" вновь созданного имени.

Макрос: создаем по отдельному файлу для каждого продукта/города/клиента (для каждого уникального значения в столбце) Итак, вы хотите быстро получить отдельные файлы с данными по каждому значению в том или ином столбце. Забирайте этот макрос, добавляйте его в личную книгу макросов, добавляйте кнопку на панель быстрого доступа и теперь вы можете в любом файле выбрать заголовок любой таблицы/диапазона, нажать эту кнопку и произойдет следующее: 1 В папке с вашей книгой Excel будет создана папка с заголовком ("Продукт", если у вас была активна ячейка с таким заголовком перед вызовом макроса) 2 В этой новой папке будет созданы книги для каждого значения из столбца — по одной на значение. В каждой книге будет данные только по одному этому значению (в случае с продуктом — по одной книге с данными по каждому продукту). Как добавить макрос в личную книгу макросов, чтобы он был доступен при работе с любыми файлами Excel — читайте здесь. Сам макрос в соседнем сообщении (сохраняйте файл с макросом, заходите Alt+F11 в редактор макросов, добавляйте файл в личную книгу макросов PERSONAL.xlsb — для этого выберите Import File в контекстном меню по правой кнопке мыши) В очень коротком видео со звуком показываю пример, как именно происходит магия. Другие макросы: Макрос для сравнения двух файлов (книг Excel) Макрос: создаем оглавление в книге Макрос: удаляем пустые листы Два варианта макросов для заполнения пустых ячеек

Если применяете гистограммы, обращайте внимание на ширины столбцов! Ведь ширина гистограммы зависит как от данных, так и от столбца, в котором она находится. (гистограммы строятся по умолчанию, если не менять настройки, так: для самого большого значения в диапазоне гистограмма будет занимать всю ячейку, а остальные будут отображаться относительно этой максимальной) В нашем примере получается, что за счет широкого столбца B 115 тыс. во Владимире в 2020 году выглядит как вдвое большее значение, чем те же 115 тыс. в Саранске в 2021 году! Так что если вставляете гистограммы в диапазон из 2 и более столбцов, делайте ширину этих столбцов абсолютно одинаковой (для этого выделите все столбцы и поменяйте ширину любого — она будет применена ко всем выделенным столбцам).

Магия двойных щелчков в Excel Клац-клац 🐱Это действие много где может пригодиться, напоминает кот Лемур. В частности: — Двойной щелчок по названию вкладки ленты инструментов скрывает и раскрывает ленту — По кисточке "Формата по образцу". Если на нее щелкать один раз — то вы сможете применить формат выделенной ячейки один раз к другой ячейке / диапазону. А если дважды — то будете в режиме форматирования по образцу, пока не нажмете Esc. То есть сможете форматировать много отдельных ячеек и диапазонов, выделяя их. — По названию (ярлыку) листа — можно его переименовать. По тексту в фигуре — отредактировать текст. — По границе выделенной ячейки — перемещение в конец диапазона (например, если щелкнуть дважды на нижний край ячейки , то это будет аналог Ctrl + ↓, перемещение в конец диапазона вниз — до последней заполненной ячейки) — В сводной таблице можно "провалиться" до исходных данных. Двойной клик по ячейке = создание отдельного листа с данными, которые сформировали то значение в сводной, по которому вы кликнули дважды. — Конечно же, по правому краю заголовка столбца, чтобы изменить его ширину (или ширину всех выделенных столбцов) автоматически ровно так, чтобы все данные отображались полностью. — Наконец, двойной клик по самому-самому левому верхнему углу окна Excel = закрытие книги.

Навигация по листам в книге Excel В книге много листов? Щелкните правой кнопкой мыши на стрелки в левом нижнем углу. Откроется список всех листов. Там смотреть удобнее, чем просто по ярлыкам. А к следующему и предыдущему листу можно переходить с помощью сочетаний клавиш Ctrl + PgDn и Ctrl+PgUp.

Вытаскиваем из даты всякое разное: подборка функций и формул Нужно получить номер квартала или посчитать число пятниц в периоде? Получить начало и конец месяца для заданной даты? Ловите пачку полезных формул для работы с датами в 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(дата)

Отключаем фильтр в отдельных столбцах Такая секретная магия доступна только через макросы. Для отключения кнопки у отдельного столбца достаточно одной строчки кода: ActiveCell.AutoFilter Field:=N, VisibleDropDown:=False Где N — номер столбца в фильтруемом диапазоне. Удивите коллег таким нестандартным фильтром 😺 Два нажатия Ctrl+Shift+L (или кнопки Фильтр на ленте) вернет все обратно.

Добавляем к дате день недели и выделяем выходные Допустим, мы с вами хотим видеть в каждой дате день недели - не "01.01.2023", как по умолчанию, а "01.01.2023 Вс". Для этого заходим в формат ячеек (Ctrl + 1) и добавляем к формату "ДДД" (DDD). Это краткое обозначение дня недели ("Вс"). Для полного ("Воскресенье") понадобится код "ДДДД" (DDDD). Ну а чтобы выделить цветом выходные (или другие дни) - воспользуемся условным форматированием (Conditional Formatting). Зададим правило с формулой, а в ней будем использовать функцию ДЕНЬНЕД / WEEKDAY. Она возвращает порядковый номер дня недели. Чтобы нумерация была привычной для нас с вами, добавьте второй аргумент, равный двойке: =ДЕНЬНЕД (ячейка с первой датой в диапазоне; 2) Тогда понедельнику будет соответствовать единица (иначе - воскресенью), вторнику - двойка и так далее. И остается добавить условие - день недели у нас должен быть больше 5 (то есть 6 или 7, суббота или воскресенье), чтобы ячейка заливалась цветом. Все показываем на видео!

Убираем выбросы формулой: например, 10% самых маленьких и 10% больших заказов. Для этого: посчитаем (для расчета числа значений используем СЧЁТЗ / COUNTA), а сколько вообще в таблице заказов и умножим на 10% или 0,1, чтобы получить число заказов, которые нужно убрать: СЧЁТЗ(столбец из таблицы)*10% Отсортируем (функция СОРТ / SORT) таблицу с заказами по столбцу, на основе которого убираем выбросы — например, в нашем случае по сумме заказа в третьем столбце СОРТ(Таблица; номер столбца) И далее дважды применяем функцию СБРОСИТЬ / DROP — она убирает строки из начала (если второй аргумент положительный) или из конца (если аргумент отрицательный) массива/таблицы/диапазона. Нам придется два раза — первые и последние 10%. Здесь число строк = сколько строк нужно удалить (вычислили на первом шаге): =СБРОСИТЬ(СБРОСИТЬ(отсортированная таблица; число строк); -число строк) Все вместе для нашей таблицы будет выглядеть так: =СБРОСИТЬ(СБРОСИТЬ( СОРТ(Заказы;3); СЧЁТЗ(Заказы[Код заказа])*10%); -СЧЁТЗ(Заказы[Код заказа])*10%)

Как заполнить пустые ячейки (вниз, то есть значениями сверху): макросы Здесь может быть много вариантов. Допустим, вы хотите заполнять все пустые ячейки в активном диапазоне — это тот диапазон, который будет выделен по нажатию 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"

Сортируем данные по имени, даже если это не первое слово в ячейке Дано: хотим сортировать в режиме реального времени таблицу по именам. Но имена у нас в столбце с фамилией и именем — на втором месте, после пробела. То есть просто сортировать по этому столбцу не получится — будет сортировка по первому слову (точнее, всему тексту, Фамилия+имя). Выход: сортируем функцией СОРТПО / SORTBY по виртуальному столбцу с именами. Виртуальный столбец получим функцией ТЕКСТПОСЛЕ / TEXTAFTER — будем извлекать текст после пробела. Но не для отдельной ячейки, а сразу для всего столбца ФИО. Как водится с новыми функциями, магия доступна в Microsoft 365 / Excel Online.