Ищем данные в разных таблицах с помощью ВПР / VLOOKUP и ДВССЫЛ / INDIRECT

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

У разных подразделений разная шкала оценки — например, где-то третий разряд присваивается с 60 лет, а где-то с 50.
Как быть?
Если бы задача была с одной таблицей, то все просто решается функцией ВПР / VLOOKUP: ищем возраст сотрудника в таблице, получаем разряд из второго столбца. Последний (четвертый аргумент) ВПР не трогаем, т.к. по умолчанию у этой функции интервальный просмотр, то есть поиск ближайшего наименьшего числа, а именно это нам и нужно в данном случае.

=ВПР(возраст сотрудника; таблица с возрастами и разрядами; 2)

Но у нас таблица не одна! Во втором аргументе ВПР могут быть разные таблицы, в зависимости от должности.
Поступим так:
— превратим таблицы для каждого отдела в "умные" таблицы (Форматировать как таблицу / Format as Table или Ctrl + T или Ctrl + L)
— назовем каждую по имени отдела
— теперь можно ссылаться на таблицы по имени. Нам надо получить название отдела по сотруднику (найти должность в списке "должность-отдел" и подтянуть отдел) — это и будет название нужной таблицы. Чтобы название таблицы из текста стало ссылкой, мы засунем всю конструкцию в ДВССЫЛ / INDIRECT — функцию, превращающую текст в ссылку.

В общем виде будет так:

=ВПР(возраст сотрудника; ДВССЫЛ(формула для определения названия нужной таблицы); 2)

Разбор задачи — в видео, а в соседнем посте файл (книга Excel) с формулой. Эту идею можно использовать в любой подобной задаче, когда нужно искать значение в нескольких диапазонах, а не в одном.

Интерфейс Excel: приемы и горячие клавиши для ускорения работы

Настраиваем интерфейс Excel:
— закрепляем и скрываем ленту инструментов
— вызываем команды с помощью клавиш
— "создаем" собственные сочетания клавиш для команд
— добавляем на панель быстрого доступа любые инструменты и команды — даже те, которых нет на ленте.

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

Кстати, эти знания пригодятся вам и для настройки других приложений MS Office.

https://www.youtube.com/watch?v=hRGpkN787Yw

Извлекаем из таблицы строки с самой большой и маленькой сделкой (наименьшим и наибольшим числом)

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

=СОРТ(таблица; номер столбца, по которому сортируем)

Если нужно по убыванию, то задаем третий аргумент, равный -1

Ну а далее, не выводя на лист отсортированный результат, сразу отправляем его внутрь функции CHOOSEROWS / ВЫБОРСТРОК — и берем первую (1) и последнюю (-1) строки.
=ВЫБОРСТРОК(СОРТ(таблица; номер столбца для сортировки);1;-1)

В общем виде ВЫБОРСТРОК имеет такой синтаксис:
=ВЫБОРСТРОК(диапазон / массив; номер строки, которую извлекаем ; [еще номер строки]; ...)

То есть можем извлечь и одну строку, и несколько — перечисляем столько номеров, сколько нужно.
А если нужны все нечетные строки, например, с 1 по 100, можно использовать функцию ПОСЛЕД / SEQUENCE, чтобы не вводить столько чисел вручную:
=ВЫБОРСТРОК(диапазон; ПОСЛЕД(50;;1;2))

Как я готовлю и провожу корпоративное обучение по Excel и Google Таблицам

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

А подробнее — в статье. Обычно в "комплект" обучения входят не только собственно вебинары (для которых нужно подготовить и файлы с примерами, часто на основе рабочих файлов заказчика), но и слайды, ответы на вопросы, чат обучения, домашние работы с проверкой — так что одними уроками дело не ограничивается.
Если захотите организовать обучение для своей команды — пишите на почту ([email protected]) или в телеграм @r_shagabutdinov, буду рад помочь!

https://shagabutdinov.ru/sheets-excel-training/

Флажки (checkboxes) есть и в Excel, и в Google Таблицах. Это переключатели, которые могут в быть в одном из двух положений — вкл/выкл и не зависят при этом друг от друга (в отличие от радиокнопок).

В Google Таблицах флажки в ячейках появились несколько лет назад, в Excel — в 2024 году только в Microsoft 365. Но в старых версиях флажки тоже были, только они не привязаны к ячейкам и поэтому не так удобны (каждый флажок вставляется отдельно, а сразу в целый диапазон ячеек добавить флажки нельзя).

Как их можно использовать?

— Чек-лист: поставили галочку, пункт списка закрасился/зачеркнулся.
— Столбец с неким условием: даем ли скидку, едет ли сотрудник на корпоратив и так далее. Затем этот столбец используем в расчетах (формулах). Или формируем выборку по этому столбцу: два отдельных списка (со скидкой и без, едут и не едут).
— Задаем параметры, используем флажки в интерактивных отчетах: сортируем ли данные, выводим ли их по месяцам или дням, какие столбцы забираем из исходника и так далее.
— Выбираем, какие ряды (регионы/филиалы/показатели и что угодно) отображать в диаграмме.
— И многое другое, на что хватит фантазии :)

Рассматриваем некоторые сценарии в статье:
https://shagabutdinov.ru/checkboxes-excel-sheets/

Или смотрим видео:
https://youtu.be/12k5sVL5iu4

План-факт через комбинированную диаграмму

Вот такая диаграмма для сравнения двух показателей (план и факт, производство и продажи, ...). Как ее построить?

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

— Делаем разные значения бокового зазора у обеих гистограмм, чтобы столбики отличались по ширине. Заливку у обеих делаем прозрачной (в примере 40%). Это настраивается в панели "Формат", выделяем столбики и нажимаем Ctrl+1.

— Подписи забираем из ячеек в столбце D. Там формула (просто темп прироста, факт делим на план и вычитаем единицу) и пользовательский формат:

+0%* 🔥;-0%* 👎

(смайлики выберите по вкусу)

— Добавляем таблицу данных вместо основных подписей и легенды и убираем всякое ненужное (линии сетки, например).

P.S. Файл с диаграммой прикреплен в отдельном сообщении выше — забирайте!

Выпадающие списки в Excel с автоматическим добавлением новых значений

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

Решения могут быть разные в зависимости от того, на разных ли листах данные.

Данные на том же листе — превращаем справочник в Таблицу и ссылаемся на него.
Данные на другом листе и у вас Excel 2010+ — просто ссылаемся на другой лист.
Данные на другом листе и будут пополняться новыми и/или у вас Excel 2007 — ссылаемся через функцию ДВССЫЛ / INDIRECT или через именованный диапазон.

Обсуждаем все нюансы в видео:
https://youtu.be/Mf9fMmWPUCw

Возрастно-половая пирамида в Excel через условное форматирование (гистограммы)

Алгоритм такой:
1 Создаем два отдельных правила
выделяем по очереди данные по женщинам и мужчинам и —> Главная — Условное форматирование — Гистограммы

Заходим в параметры каждого правила здесь:
Главная — Условное форматирование — Управление правилами — выделяем правило — Изменить правило

И:

2.1 У левого столбца (Женщины в примере) в параметрах меняем направление на "Справа налево"

2.2 У обоих задаем одинаковый максимум, чтобы масштаб тоже был одинаковый. С той же целью обязательно делаем ширины столбцов с гистограммами одинаковыми.

Еще один трюк с гистограммами: вставляем сверху текст

Как?

1 Сначала берем нужные данные. Они могут уже быть в ячейках, как у нас в столбце B, но если вы хотите оставить числа отдельно, то в другом столбце сошлитесь на данные:
=B2
(для первой строки)

2 Далее строим гистограмму. Условное форматирование — Гистограммы — выбираем цвет по вкусу.

3 Эта гистограмма будет отображаться вместе с данными (числами). Уберем числа и оставим только гистограмму:
Условное форматирование — Управление правилами — выбираем нашу гистограмму — Изменить правило — Показывать только столбец

4 Осталось скопировать нужный текст ( у нас это A2:A12), вставить в диапазон с гистограммами (можно в первую ячейку) и далее нажать на смарт-тэг "Параметры вставки" справа внизу (см скриншот) и выбрать "Связанный рисунок".

Астрологи объявили неделю гистограмм!

Итак, на этот раз мы будем применять их не ко всем значениям, а к верхним N.
Например, к топ-5 регионов.

Для этого сначала вставим гистограммы (Главная — Условное форматирование — Гистограммы) и далее создадим правило условного форматирования, которое будет применять никакое форматирование к последним значениям (в нашем случае последним 6, потому что всего регионов 11, а мы хотим оставить топ-5)

Главная — Условное форматирование — Правила отбора первых и последних значений — Последние 10 элементов - меняем 10 на 6 или другое нужное число — выбираем "пользовательский формат" и в нем не меняем ничего, то есть оставляем форматирование пустым.

Идем в Управление правилами условного форматирования и там:
1 Убеждаемся, что у правила с пустым форматированием верхний приоритет (оно идет первым в списке — если это не так, поменяйте порядок с помощью стрелок справа)
2 Включаем флажок "Остановить, если истина" у этого правила.

Так-так-так, смотрите, какие функции подвезли в Excel!

То, что было доступно в Google Таблицах около 10 000 лет, появилось и в Excel — пока только у инсайдеров, но в любом случае рано или поздно функции доедут до всех подписчиков 365.

Это TRANSLATE и DETECTLANGUAGE. На русском — ПЕРЕВОД и ОПРЕДЕЛИТЬЯЗЫК.

В Google Таблицах эти функции называются GOOGLETRANSLATE и DETECTLANGUAGE.
Вот пример применения функций в Гугл — определяем язык и в зависимости от этого формулой выдаем ссылку на российский или американский книжный магазин.

Объединение ячеек: почему это не очень хорошо и чем заменить с тем же визуальным эффектом

Объединение ячеек в Excel приводит к тому, что значение хранится только в одной из объединенных ячеек. Если мы рассчитываем использовать эти ячейки в формулах, мы будем иметь дело с пустыми значениями.

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

Найти и заменить: меняем форматы, а не значения

У вас есть много ячеек, разбросанных по листу/книге, с определенным набором параметров форматирования: допустим, голубая заливка, какое-то выравнивание, полужирное начертание и т.д.

И вам нужно их все переформатировать по другому образцу. Допустим, без полужирного начертания.

Вызываем окно "Найти и заменить" — Ctrl + H

Выбираем справа Формат — Выбрать формат из ячейки
Напротив поля "Найти" выбираем образец, какие ячейки будем менять
А напротив "Заменить на" — выбираем образец, как они должны выглядеть

Нажимаем "Заменить все". Готово!

Оси спарклайнов

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

Вкладка ленты "Спарклайны" — Ось — параметры минимального/максимального значения по вертикальной оси — Фиксированное для всех спарклайнов.

F9: вычисляем фрагмент формулы

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

Это можно использовать, чтобы посмотреть, какой результат возвращает один из промежуточных этапов формулы, что мы получаем в ней на входе.
В таком случае важно не забыть после нажать Esc, чтобы выделенный фрагмент не остался в формуле статичным значением.

Но иногда превратить диапазон в значения — именно то, что нужно! F9 можно применять и так. Если выделить диапазон и нажать F9, то вместо ссылки в формуле будет массив из значений, то есть тот же самый диапазон, но уже "внутри формулы". И тогда ваша формула будет работать без вспомогательной таблицы. Если вы хотите избавиться от такой таблицы — то этот способ вам подойдет. Все показываем в видео!

P.S. Проговорим еще раз: так как после этой манипуляции все значения остаются только в формуле, что-то изменять придется в ней. Этот вариант подходит, если таблица не будет изменяться в будущем.

Задача: посчитать стоимость (то есть перемножить цену и количество) с условием (то есть не по всем подряд строкам)

Если бы просто перемножить два столбца — цена и остатки — то все просто. Берем функцию СУММПРОИЗВ / SUMPRODUCT — она перемножает значения из нескольких массивов, а потом суммирует полученные произведения:

=СУММПРОИЗВ(Прайс[Цена];Прайс[Остатки])

Но нам нужно не все подряд, а, допустим, только строки, в которых встречается определенный бренд — например, Orijen.

Тогда добавим третий аргумент (массив) в функцию. С помощью функции НАЙТИ / FIND будем определять, есть ли искомый бренд в столбце "Название". Если функция выдаст ошибку (проверим это с помощью ЕОШИБКА / ISERROR), значит, бренда нет, а нам нужно, чтобы ошибки не было — так что мы будем превращать ИСТИНА (=ошибка есть, название не найдено) в ЛОЖЬ и наоборот. Таким образом, следующая конструкция выдаст ИСТИНА там, где искомое слово найдено:

НЕ(ЕОШИБКА(НАЙТИ("Orijen";Прайс[Название])))

Но это будет массив из логических значений ИСТИНА и ЛОЖЬ, и мы превратим его в единицы и нули, умножив на -1 дважды:

--НЕ(ЕОШИБКА(НАЙТИ("Orijen";Прайс[Название])))

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

=СУММПРОИЗВ(Прайс[Цена];Прайс[Остатки];--НЕ(ЕОШИБКА(НАЙТИ("Orijen";Прайс[Название]))))

Диаграмма "Карта" в Excel

Если у вас есть данные по странам (и есть Excel 2019 / 2021 😸) — можно построить диаграмму "Картограмма":

Вставка — Карты — Картограмма
Insert — Maps — Filled Map

Но настроек тут немного. Можно отображать названия стран, менять цвета заливки (2 или 3 цвета), включать и выключать отображение стран, для которых данных нет.

В общем, не сравнить по возможностям с Power Map (3D-картами, которые можно строить на основе модели данных Power Pivot). Но для простых задач может хватить.

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

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

Ловите пачку полезных формул для работы с датами в 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(дата)

Написали в РБК с Лемуром про несколько свежих задач, когда даже не самые сложные формулы и манипуляции помогают экономить очень много рабочих часов.
Особенно там, где объемы большие, а до этого работали в ручном или почти ручном режиме!

https://companies.rbc.ru/news/ylp76KL1rl/kak-tablitsyi-ekonomyat-kompaniyam-sotni-rabochih-chasov/