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

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

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

Вызываем окно "Найти и заменить" — 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/

Начал готовить курс по визуализации данных в Excel/Google Таблицах

Традиционно прикупил свежее по теме (две книги на переднем плане) и смотрю старое, тут не все, конечно, многое в электронке (тут подробнее про многие книги)

Прочитал уже первую из них «Графики, которые убеждают всех» Александра Богачева и могу рекомендовать тем, кто только входит в тему — кратко, наглядно, хорошие примеры (из настоящих отчетов/презентаций в то числе!). Есть одна опечатка (пример про цвета, а диаграмма ч/б), но это даже не капля дегтя, а молекулы.

Не специфично для Excel, хотя иногда есть уточнения, в том числе в классификации диаграмм автор уточняет, что есть там (правда, не уточняя версии Excel; диаграммы там постоянно добавляются).

Если читали что-то еще хорошее по теме, порекомендуйте, пожалуйста.

И снова в личные сообщения пришел вопрос: как удалить пробелы из выгрузки? Числа с пробелами (и поэтому это де-факто текст, который не обработать нормально), но "Найти и заменить" (Ctrl + H) не помогает. Проблема в том, что пробелы бывают разные 🤯 Короткий ответ: пробел лучше копировать, то есть брать именно тот пробел, что есть в выгрузке, который нужно удалить из чисел. А не вводить с клавиатуры.

В видео разбираемся более детально: как с помощью функции КОДСИМВ / CODE понять, что за символ вообще перед нами — в данном случае это не обычный пробел, а неразрывный, поэтому его не удалишь вводом обычного пробела с клавиатуры в окне "Найти и заменить". Его нужно либо копировать из данных и вставлять в окно "Найти и заменить", либо вводить с помощью кода — Alt+0160.

И делаем макрос, который позволит удалять ненужные символы одним нажатием кнопки или сочетанием клавиш Alt + цифра.

Код макроса можно сократить до такого:

Selection.Replace What:=" ", Replacement:=""

На месте пробела может быть другой символ, который вам нужно удалять в выделенном диапазоне.
Не забывайте: действие макросов отменить через Ctrl+Z нельзя!

Всю задачу разбираем в видео со звуком.

Типы данных

Может ли в одной ячейке быть несколько значений? Может, если это типы данных (data types).

Например, если вы введете в ячейки названия стран (на английском даже при русском интерфейсе Excel) и преобразуете их в тип данных "География" (Данные — Типы данных — География, Data — Data Types — Geography), то из таких ячеек можно будет извлекать данные формулой. Ссылаемся на ячейку с названием страны, ставим точку и видим варианты — что можно извлечь.

Либо просто нажмите на смарт-тэг(иконку), которая появляется в ячейке со страной, и выберите нужные данные, формула сформируется автоматом в соседнем столбце.

Уровней может быть несколько! Допустим, нам нужно население столиц:

=A1.[Capital/Major City].Population

А если бы мы хотели просуммировать население всех столиц стран из списка?

=СУММ(A1:A3.[Capital/Major City].Population)

Еще есть типы данных "акции", "валюты", в студенческой версии 365 еще и Wolfram (справочная система). А свои типы данных можно создавать с помощью Power Query.

Типы данных работают в 365 и Excel Online.

Магия двойных щелчков в Excel

Клац-клац 🐱Это действие много где может пригодиться, напоминает кот Лемур. В частности:

— Двойной щелчок по названию вкладки ленты инструментов скрывает и раскрывает ленту

— По кисточке "Формата по образцу". Если на нее щелкать один раз — то вы сможете применить формат выделенной ячейки один раз к другой ячейке / диапазону. А если дважды — то будете в режиме форматирования по образцу, пока не нажмете Esc. То есть сможете форматировать много отдельных ячеек и диапазонов, выделяя их 🔥.

— По названию (ярлыку) листа — можно его переименовать. По тексту в фигуре — отредактировать текст.

— По границе выделенной ячейки — перемещение в конец диапазона (например, если щелкнуть дважды на нижний край ячейки , то это будет аналог Ctrl + ↓, перемещение в конец диапазона вниз — до последней заполненной ячейки)

— В сводной таблице можно "провалиться" до исходных данных. Двойной клик по ячейке = создание отдельного листа с данными, которые сформировали то значение в сводной, по которому вы кликнули дважды.

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

— Наконец, двойной клик по самому-самому левому верхнему углу окна Excel = закрытие книги.

Друзья, залили на Kinescope наши видео. Изучайте на здоровье без регистрации, СМС, ВПН и других аббревиатур:

Автоматическая нумерация списков в Excel

Новые функции Excel: GROUPBY / ГРУПППО и PIVOTBY / СВОДПО. Агрегируем данные разными способами

Флажки в Excel: в ячейках (новые) и элементы управления (в старых версиях)

Объединяем умные таблицы в одну: формулы и Power Query

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

Абсолютные и относительные ссылки в Excel. Стиль ссылок R1C1

Новая функция REGEXEXTRACT в Excel: извлекаем электронную почту, даты и другие фрагменты из текста

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

Гильдия магов Excel

Еще несколько бесплатных видео вашему вниманию — уже более длинных 🤠

Потому что это вебинары "Гильдии магов Excel" в МИФе. Примерно 1 час по каждой из следующих тем:

Колдуем с текстом в Excel. Регистр, пробелы, переносы, форматы
Интерфейс и быстрый ввод данных. Лента инструментов, панель быстрого доступа
Формулы Excel для новичков. Разбираемся со ссылками, знаками и функциями.
Новые функции и инструменты 2021-2023. Что появилось в Excel 2021-2023

1 Заходим по ссылке:
https://www.mann-ivanov-ferber.ru/courses/guild-excel

2 Выбираем интересующую тему, нажимаем "Получить запись"

3 Вводим почту

4 Получаем ссылку и смотрим (без VPN)! Приятного просмотра.

Обязательно ли показывать структуру круговой диаграммой?

Она не самая наглядная и удобная для восприятия. Если нам важнее сравнить значения друг с другом, можно использовать линейчатую.

А если все же хочется показать доли каждого значения, можно отразить их в подписях данных вместо абсолютных значений.

Для этого рассчитаем доли в отдельном столбце (поделим каждое значение на общую сумму) и далее в параметрах подписей используем вариант "значения из ячеек".

Он, увы, доступен не во всех версиях Excel 😿 Можно, конечно, править подписи вручную и вставить доли в старой версии таким образом, но это будет немного грустно (и совсем без обновления).

Ссылки на несколько листов в формулах Excel

Общий вид ссылки на несколько листов (это ссылка на листы от первого и до последнего по ярлыкам слева направо; если порядок листов в книге изменится, ссылка в формуле не поменяется):

'Первый лист:Последний лист'!Диапазон

Следующая формула суммирует числа из ячеек B2 на листах от "$ счет" и до "Счет в юанях" включительно:

=СУММ('$ счет:Счет в юанях'!B2)

В названиях листов можно использовать символ подстановки — звездочку. Если в книге много листов со словом "Расходы" в названии ("Расходы январь", "Расходы февраль", . . . ). Следующая формула позволит просуммировать ячейки A1 со всех этих листов:

=СУММ('Расходы*'!A1)

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

=СУММ('Расходы январь'!A1;'Расходы февраль'!A1;'Расходы март'!A1;...)

Получаем название листа формулой

Функция ЯЧЕЙКА / CELL может выдавать разную информацию: например, полное имя файла (книги) вместе с листом. Для этого ее первый и единственный обязательный аргумент должен быть равен "имяфайла" ("filename").

А дальше — дело техники — вытаскиваем только имя листа текстовыми функциями.
В новом Excel совсем удобно: ТЕКСТПОСЛЕ / TEXTAFTER вытащит все, что после квадратной скобки.

=ТЕКСТПОСЛЕ(ЯЧЕЙКА("имяфайла");"]")

В старых версиях Excel воспользуемся комбинацией функций:
НАЙТИ / FIND подскажет, на какой позиции находится скобка, ДЛСТР / LEN — сколько в имени вообще символов — исходя из этого поймем, какая длина названия листа — в нашем случае 5 символов — именно столько извлечем с конца текстовой строки с помощью функции ПРАВСИМВ / RIGHT.

=ПРАВСИМВ(ЯЧЕЙКА("имяфайла");ДЛСТР(...)-НАЙТИ("]";...))

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

И Excel такое дело помечает ошибкой с зеленым треугольником — "Число сохранено как текст".

Эти ошибки можно отключить. Отправляемся сюда:
Параметры Excel — Формулы — Правила проверки ошибок — отключаем "Числа, отформатированные как текст или с предшествующим апострофом".

Excel Options — Formulas — Error Checking Rules — Numbers Formatted As Text of Preceded By An Apostrophe

Если при создании сводной таблицы вы включите флажок "Добавить эти данные в модель данных" (Add this data to Data Model), то впоследствии можно будет превратить сводную таблицу в формулы.

Что это за формулы такие? Формулы куба. Они могут напомнить вам функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (GETPIVOTDATA), но она получает данные из существующей сводной. А функции куба заменяют собой сводную! И проще всего получить их в готовом виде, чтобы изучить, преобразовав существующую сводную таблицу.

Строим сводную таблицу — включаем флажок "Добавить эти данные в модель данных" — переходим на вкладку "Анализ сводной таблицы" и там выбираем "Средства OLAP" — Преобразовать в формулы" (OLAP tools — Convert to Formulas).

Что изменится? Каждая ячейка сводной станет независимой формулой. Больше не будет единого объекта — сводной таблицы — будут отдельные формулы. Вы сможете переупорядочить вашу сводную как вам захочется (см пример на видео без звука).

Какие это функции?
КУБЗНАЧЕНИЕ / CUBEVALUE — это как область значений сводной, в ней задается подключение (к модели данных, первый аргумент = "ThisWorkbookDataModel") и ссылки на заголовки столбца, названия элементов (в нашем примере категория ресторана из области строк), может быть ссылка и на срез.
Заголовки в сводной (и заголовок области значений в духе "Сумма по полю ...", и названия элементов в строках/столбцах) будут заданы другой функцией — КУБЭЛЕМЕНТ / CUBEMEMBER.
Еще и другие функции куборв, например, КУБМНОЖ / CUBESET (возвращает список всех значений из столбца) и КУБПОРЭЛЕМЕНТ / CUBERANKEDMEMBER, которая из ячейки с функцией КУБМНОЖ может извлечь значение по его порядковому номеру.

Случайность в квадрате: как визуализировать вероятность

Если мы хотим наглядно показать, что что-то будет происходить примерно в 10%, 20% или N% случаев, можно поступить так:

1. Сгенерировать случайные числа в каком-то интервале, например от 1 до 100
В Excel 365 нам поможет функция СЛМАССИВ / RANDARRAY, в старых версиях СЛЧИС / RAND
(число от 0 до 1) или СЛУЧМЕЖДУ / RANDBETWEEN (целое число в заданном интервале)

Одна формула для нового Excel:

=СЛМАССИВ(число строк; число столбцов; 1; 100)

Отдельная формула, которую нужно вставить в каждую ячейку — для старых версий:

=СЛУЧМЕЖДУ(1;100)

2. Оставить в ячейках какой-нибудь знак, допустим, единицу, для тех случаев, когда случайное число меньше N, допустим, 10:

=ЕСЛИ(СЛМАССИВ(число строк; число столбцов; 1; 100)<=10; 1; "")

=ЕСЛИ(СЛУЧМЕЖДУ(1;100)<=10;1;"")

3 Сделать правило условного форматирования — для ячеек с единицей применять заливку какого-то цвета и шрифт того же цвета (чтобы скрыть единицы). Готово!
Для большей красоты можно добавить границу вокруг всего диапазона, убрать сетку на листе — на что хватит фантазии.

Весь процесс в коротком видео без звука.

Декартово произведение (все комбинации значений) формулой

С новыми функциями можно и формулой (а без них — через Power Query, о чем будет в отдельном посте).

Сначала получаем первый список без пустых значений. Функция ПОСТОЛБЦ / TOCOL вернет его без пустых значений, то есть мы можем сослаться на весь столбец, но исключить пустые вторым аргументом функции (равным 1 для такого случая), чтобы предусмотреть появление новых значений в будущем.

Второй список сделаем строкой с помощью ПОСТРОК / TOROW.

Потом склеим их амперсандом (&), добавив пробел. Получим то, что вы видите на скриншоте справа в столбцах F-I (произведение, а точнее, конкатенация в данном случае, строки на столбец дает прямоугольный диапазон).

Останется сделать его плоским списком — снова с помощью ПОСТОЛБЦ.

=ПОСТОЛБЦ(ПОСТОЛБЦ(первый список;1)&" "&ПОСТРОК(второй список;1))