Магия 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 = закрытие книги.