Магия Excel

Когда при вводе формулы вы выделяете диапазон, появляется вот такая подсказка с числом строк (R) и столбцов (C) в нем. Удобно, когда нужно понять, из скольки вариантов выбирать случайный, из какого по счету столбца тянуть данные его высочество ВПР'ом и т.д.

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

Панель быстрого доступа работает не только в самом Excel (и других приложениях Office!), но в и окнах Power Query и Power Pivot Все работает аналогично: правой кнопкой по команде, которую используете часто —> Добавить на панель быстрого доступа (Add to Quick Access Toolbar) После этого кнопки будут всегда наверху при любой активированной вкладке ленты инструментов. А еще вы получите возможность вызывать их сочетанием клавиш Alt + цифра (какая именно цифра — зависит от положения команды на панели). Подробнее про панель быстрого доступа в интерфейсе Excel: Сыграть на Alt'е - доступ к командам на ленте с помощью клавиатуры Закрепление верхней строки в Excel в один клик: добавляем команды на панель быстрого доступа Как добавить макрос на панель быстрого доступа

Друзья, новость про курс "Магия 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 февраля включительно

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

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

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

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

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

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

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