Магия Excel

Нумерация с помощью формул В этом видео рассматриваем несколько вариантов автоматической нумерации строк в диапазоне / таблице: - с помощью функции СТРОКА / ROW, в том числе с учетом положения таблицы на листе (то есть при вставке новых строк над таблицей нумерация все равно не будет меняться, она останется в пределах таблицы - с помощью функции ПОСЛЕД / SEQUENCE (живущей в Excel 2021 и Microsoft 365) - формулой из функций ЕСЛИ / IF, ЕПУСТО / ISBLANK, СТРОКА / ROW и ДВССЫЛ / INDIRECT для нумерации с пропуском пустых строк. Файл с формулами из видео прикрепляем отдельным сообщением!

Выводим все даты текущего месяца формулой Тут понадобится функция ПОСЛЕД / SEQUENCE, которая может вернуть последовательность чисел (ищите эту прелесть в Excel 2021, Microsoft 365, Google Таблицах). У нее задается число строк, столбцов, начальное значение и шаг. Шаг - самое простое, у нас это единица, один день. Число строк или столбцов будет равно единице - в зависимости от ориентации списка. Если вы хотите список в один столбец (как в примере на картинке), то столбец будет один, а число строк будет определяться числом дней в месяце. Начало - первое число месяца. Единицы добыть легко - их есть на клавиатуре, а вот как получить остальное? Первое число месяца - это дата, у которой день = единица, месяц текущий (функция МЕСЯЦ / MONTH вернет нам месяц СЕГОДНЯшней даты), год текущий (функция ГОД / YEAR тоже от текущей даты, которую мы получим с помощью функции СЕГОДНЯ / TODAY). Собрать дату из трех кусочков можно функцией ДАТА / DATE. Ее аргументы - номера года, месяца, дня: ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(СЕГОДНЯ());1) Последнее число месяца - это функция КОНМЕСЯЦА / EOMONTH. Первый аргумент - дата (мы зададим текущую). Второй - на сколько месяцев вперед (положительное число) или назад от нее отступить. То есть КОНМЕСЯЦА(СЕГОДНЯ();-1) - это последнее число предыдущего месяца. Нам нужен текущий, так что без отступов - второй аргумент равен нулю. КОНМЕСЯЦА(СЕГОДНЯ();0) Все вместе (для списка в один столбец): =ПОСЛЕД(ДЕНЬ(КОНМЕСЯЦА(СЕГОДНЯ();0)); 1; ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(СЕГОДНЯ());1); 1)

Самый компактный вариант для визуализации прогресса в Excel - гистограмма (условное форматирование) По умолчанию при вставке гистограмм для максимального значения гистограмма занимает всю ячейку, а для остальных - относительно максимального. Но можно задавать вручную максимальное значение. Если таковым сделать план, а самой гистограммой визуализировать факт - то и получится визуализация выполнения. Сделать такой простенький прогресс-бар можно так: - Сослаться на фактическое значение в отдельной ячейке, куда мы вставим гистограмму; - Вставить гистограмму в эту ячейку по вкусу; - Задать в настройках правила условного форматирования максимальное значение как план/цель - тогда гистограмма будет шириной во всю ячейку, когда факт будет равняться плану. Иначе - будет показываться % выполнения; - Можно включить опцию "Показывать только столбец", фактическое значение нам здесь не нужно - оно и так есть в исходной ячейке. А тут пусть будет только наш прогресс-бар.

Быстрая ссылка на другую книгу Если вам нужно связать книги - сослаться на значение/диапазон из другой книги, можно сделать это довольно быстро следующим образом: - Открыть обе книги на экране (Вид - Упорядочить, View - Arrange All) - Правой кнопкой мыши потянуть за границу ячейки или диапазона, перетащить в новую книгу, выбрать в появившемся меню "Связать" (Link Here). Также можно скопировать ячейку или диапазон, вставить в другую книгу и только после этого выбрать вариант со связью, нажав на смарт-тег (справа снизу от ячейку, куда вставили). Можно вставить скопированный диапазон и через окно специальной вставки (Ctrl + Alt + V, Вставить связь / Paste Link), и через контекстное меню, вызываемое правой кнопкой мыши. В данном случае мы просто создаем ссылку на ячейки (транслируем данные из источника). Если нужно данные из другой книги обрабатывать функциями в формуле, то сначала нужно будет ввести функцию, а потом ссылаться из нее на диапазоны в другой книге. Здесь тоже пригодится опция "Упорядочить".

Выделяем цветом формулы по какому-то признаку Вы хотите выделить визуально "старые формулы массива" (из версий до 2019 включительно), или формулы, ссылающиеся на какой-то лист, или формулы с определенными функциями. Получить текст формулы можно с помощью функции Ф.ТЕКСТ / FORMULATEXT. Искать в этом тексте какой-то признак можно с помощью функции НАЙТИ / FIND. И если все это засунуть в условное форматирование, то мы получим возможность выделять визуально формулы, содержащие что-нибудь! Например, старые формулы массива можно выделить по наличию фигурной скобки: =НАЙТИ("{";Ф.ТЕКСТ(первая ячейка форматируемого диапазона)) Ссылки на лист с названием - по этому самому названию =НАЙТИ("название листа";Ф.ТЕКСТ(первая ячейка ...)) Определенные функции - по их названию. Например, ПРОСМОТРX, которой нет в старых версиях: =НАЙТИ("ПРОСМОТРX";Ф.ТЕКСТ(ячейка)) А вот выделить формулы со старой функцией ПРОСМОТР можно, добавив к "запросу" скобку - иначе будут выделяться формулы, где есть и ПРОСМОТР, и ПРОСМОТРX. =НАЙТИ("ПРОСМОТР(";Ф.ТЕКСТ(ячейка))

Функция ВЗЯТЬ / TAKE: суммируем последние N сделок Эта функция извлекает заданное количество столбцов или строк из массива. В частном случае можно вытаскивать не только строки или столбцы, но и значения (если на входе задан одномерный массив). Например, если нам нужны первые 10 значений в столбце таблицы, будет такая формула (в общем виде): =ВЗЯТЬ(Таблица[Столбец]; 10) А если нужны последние, а не первые? Прелесть в том, что функция умеет и так - просто укажите отрицательное количество строк в ее аргументе. =ВЗЯТЬ(Таблица[Столбец]; -10) Если нужна сумма последних 10, то добавим сверху функцию СУММ / SUM: =СУММ(ВЗЯТЬ(Таблица[Столбец]; -10)) Теперь формула всегда будет возвращать сумму значений из 10 последних строк таблицы из выбранного столбца - даже когда будут добавляться новые строки. ВЗЯТЬ доступна только в Microsoft 365, увы.

Тепловая карта (шкала) без чисел В условном форматировании со значками можно отключать отображение данных (показывали здесь), как и в случае с гистограммами (здесь). А в случае с цветовой шкалой нет опции "Показывать только шкалу". Но это можно исправить, просто применив пользовательский формат, в котором все данные будут скрыты. В пользовательских форматах можно задавать отдельные форматы для четырех типов данных - положительных чисел, отрицательных, нуля и текста. Задаются они через точку с запятой. И если ввести такой формат: ;;; То не будет отображаться ничего. Потому что мы явным образом задаем форматы для всех четырех типов данных, но все они пустые. То есть мы не отображаем ничего. Данные в ячейках никуда не деваются, разумеется - речь только про форматирование. Подробнее про пользовательские форматы - в этом видео (оно на основе Google Таблиц, но форматы работают одинаково и там, и в Excel за вычетом косметических отличий).

Собираем данные с разных листов в Excel и Google Таблицах (список листов - динамический) Допустим, наша задача - собирать данные с нескольких листов, причем список листов может меняться - листы могут меняться, могут удаляться из списка, могут добавляться новые. Чтобы обработать несколько листов и с каждым проделывать какие-то манипуляции, будем забирать названия листов из массива (диапазона или умной таблицы) с помощью MAP и передавать в LAMBDA, где будет нужное нам вычисление. В заметке разбираем по шагам + вспоминаем, что в Excel в любых версиях (а в старых, увы, функции LAMBDA нет) можно ссылаться на пачку листов или использовать символ подстановки в названиях листов (правда, только один раз 🤷🏻‍♂️).

Протягиваем даты одной правой Если тянуть дату правой кнопкой мыши, то можно сразу выбрать вариант - заполнение по рабочим дням, месяцам или годам. Впрочем, если начали по привычке левой, то всегда можно исправить результат и тоже выбрать один из этих вариантов, если щелкнуть на тег внизу справа. P.s. Лемур заметил, что на гиф в конце опечатка 🙀. «Как обычно» — это левой кнопкой мыши. Приносим извинения

Истина (True) где-то рядом Сегодня пятничный и не совсем серьезный прием. Поделитесь, если вам пригодится в работе и придумаете, где это использовать. Представим, что вам нужно проверять, какой язык у пользователя — русский или английский (любой другой). Например, чтобы учесть региональные настройки (хотя они и язык в интерфейсе Excel независимы, это нужно иметь в виду), или чтобы поменять выводимые формулами тексты/обозначения, если в вашей компании говорят на нескольких языках. Ну или просто поздороваться с пользователем 😺 Возьмем функцию ЛЕВСИМВ / LEFT — она извлекает первый символ своего аргумента (или несколько, если явно задать количество во втором аргументе). И в качестве аргумента введем ИСТИНА. В Excel с англоязычным интерфейсом это будет TRUE. Так что можно проверять — ЕСЛИ первая буква И (T), то язык интерфейса в моменте — русский (английский). Если нет — значит, другой. =ЕСЛИ(ЛЕВСИМВ(ИСТИНА)="И";"Добро пожаловать в таблицу!";"Hello world") Чтобы формула обновлялась автоматически при открытии книги и любом действии в ней, можно добавить вместо ИСТИНЫ что-нибудь не менее истинное, но волатильное (то есть обновляющееся при любом изменении в книге). Допустим, функцию СЕГОДНЯ. Можно просто сравнивать сегодняшнюю дату с ней же самой, это всегда будет возвращать ИСТИНА. =ЕСЛИ(ЛЕВСИМВ(СЕГОДНЯ()=СЕГОДНЯ())="И";"Добро пожаловать в таблицу!";"Hello world")

Личная панель инструментов Вот какую новинку предложил Excel с утра (вероятно, только для программы Office Insider пока — даже сказано, что "предварительная версия) Личная панель инструментов: набор команд и инструментов под лентой. А чем же была плоха обычная панель быстрого доступа? 😺Ведь ее тоже можно разместить под лентой и добавить любые инструменты. Возможно, в случае с новой панелью Excel научится сам собирать те команды, которые вы используете чаще всего. Пока этого не случилось — там был стандартный набор. Отличий от старой панели быстрого доступа тогда немного. В новом режиме лента никогда не "фиксируется" — она раскрывается только при щелчке на вкладку. Внешний вид немного отличается, 2 варианта отображения команд в наличии — см. видео, есть опция "Показать метки команд". Панель раскрывается, то есть команд, вероятно, можно собрать больше, чем на панель быстрого доступа. Нельзя (надеемся, пока) добавить отдельную команду не с ленты или из коллекции (как закрепление верхней строки). А на панель быстрого доступа можно любую команду Excel добавить — писали об этом выше. Может, в будущем под новую панель параметры дополнят ее настройками. Ваши команды с панели быстрого доступа исчезают :( То есть нельзя (во всяком случае, пока) собрать инструментов и туда, и туда. Доступ к командам на панели с клавиатуры (Alt + отображаемые цифры/буквы) есть, как был и к панели быстрого доступа.