Магия табличных формул

Что там с видеоуроками на Sponsr?

Более 30 видеоуроков уже доступны — это 5 модулей:
1 Основы формул. Все нюансы, формулы в Google Таблицах, ошибки в формулах и т.д.
2 Даты и время. От текущей даты до рабочих дней и извлечения элементов даты
3 Текст. Основы, функции, регулярки.
4 Логические значения и условия. От флажков до расширенного фильтра с формулами. Формулы в проверке данных и условном форматировании.
5 Поиск данных. ВПР, ПРОСМОТРX, магия функции ИНДЕКС (ей будут посвящены несколько видеоуроков), поиск по 2 критериям, на разных листах, и многое другое

Что впереди?
— Еще видео про поиск, включая функцию СМЕЩ и полностью универсальную формулу поиска
— Формулы массива — старые, новые и гуглотабличные
— LET и LAMBDA
— Формулы кубов и Power Pivot
— Пользовательские функции
— И многое другое. Пожелания и заявки принимаются 😸

Все это с файлами-примерами.

Где подписаться?
Тут: https://sponsr.ru/excel_magic

Старине Экселю сегодня 40 🤯
Мы его с этим мощным юбилеем поздравляем.

Хотите прочувствовать масштаб изменений в табличном редакторе за это время?
Загляните сюда — тут мы писали, сколько функций было в 1979 году в VisiCalc.

Сегодня в честь праздника в Excel Blog анонсировали очередное новшество — Agent Mode. Мало им функции COPILOT, теперь в Excel будет встроен ИИ. Большинству простых людей вроде нас с вами это удовольствие пока недоступно — все впереди. Но если у нас есть хотя бы Excel 2013 — значит, у нас есть Мгновенное заполнение, а это почти ИИ 😸

Картинка оттуда же, из блога Excel.

С нашей стороны в честь ДР кот Лемур организует лютую скидку на два наших табличных курса. На самом деле, очень скоро мы прекратим полностью их продажу на сайте и они будут доступны только в издательстве МИФ — позже будут все детали (в любом случае у всех купивших есть и будет вечный доступ и все обновления)
Магия новых функций
Сводные таблицы Google Spreadsheets

Сколько?
790 рублей за любой из курсов.
Это вечный доступ, в том числе ко всем будущим обновлениям (например, в курсе по новым функциям сначала было на 5 уроков меньше, чем сейчас, ибо ... появляются новые новые функции в Excel).

Что в курсах?

Магия новых функций Excel. Массивы, регулярные выражения и многое другое
✅Все новые функции: 11 друзей LAMBDA, LET, манипуляции с массивами, регулярки, SORT и FILTER, ПРОСМОТРX, PIVOTBY и GROUPBY, флажки и многое другое
✅20 видео + текстовые материалы, исходные и готовые файлы в формате XLSX
✅Для счастливых обладателей Microsoft 365 с новыми функциями и для пользователей Google Таблиц (ибо там есть почти все функции, бесплатно… но с регистрацией аккаунта, конечно)

Сводные таблицы Google Spreadsheets. От основ и нюансов до построения сводных с помощью QUERY и LAMBDA
✅Для начинающих и продолжающих пользователей Google Таблиц и переходящих туда из Excel. Вообще все про сводные Google, от основ до нюансов вокруг сводных (от подготовки данных и рассчитываемых полей до визуализации и построения "сводных" LAMBDA-формулами)
✅20 видео, исходные и готовые файлы в формате Google Таблиц
✅Сводные — что в Excel, что в Google — зачастую могут решать до 80-90% ваших задач по анализу данных :)

Выделяем все влияющие или все зависимые ячейки

Многие из вас знают про стрелки к зависимым / влияющим ячейкам — их можно включить на вкладке "Формулы", как на скриншоте. И отключить там же.

Но можно обойтись и без стрелок.
Чтобы просто выделить ячейки, задействованные в формуле (влияющие), нажмите Ctrl + [

А чтобы выделить зависимые (те, в которых в вычислении используется активная ячейка), нажмите Ctrl + ]

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

Сочетания клавиш не работают? Значит, перед запуском Excel у вас была не английская раскладка.
Та же история, что со вставкой текущих даты/времени.

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

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

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

Если не работает видео прямо в телеграме, смотрите его в Kinescope — бесплатно, без рекламы, доступно в России:
https://kinescope.io/gSHd23FBDG6bun6bZHR3GY

Отличия по строкам
Вы хотите быстро выделить цветом ячейки, в которых план отличается от факта (один столбец от другого — в общем случае)?

1 Выделяем столбцы (можно быстро быстро выделить их сочетанием Ctrl + Shift + стрелка вниз)
2 Ctrl + G —> Выделить (Special)
3 Отличия по строкам (Row differences)
4 Красим выделенные ячейки нужным цветом. Готово!

Смотрим на GIF (без звука)

Показываем, как отображать числа в тысячах с помощью пользовательского числового формата.

При этом сами числа никак не меняются, меняется только внешнее отображение (форматирование).

Вычисления (с помощью формул, сводных таблиц) производятся именно с числами, и форматирование никак на это не влияет.

Так что если нам удобнее смотреть на числа в тысячах, но при этом производить вычисления с исходными данными в единицах — формат подходит лучше всего.

Для того, чтобы числа отображались в тысячах, нужно в формате после кода числа добавить пробел. Два пробела — и числа будут в миллионах. Три — в миллиардах, ну а далее - вы поняли ;)

0 " тыс." 

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

При американских региональных настройках вместо пробела нужно будет использовать запятую. В Google Таблицах в пользовательских форматах при любых региональных настройках тоже нужно использовать запятую, а не пробел.

Группировка в сводной по тексту

У вас есть элементы в исходных данных — например, отделы. И вы хотите объединить отделы в какие-то более крупные подразделения, но сделать эту группировку только в отчете сводной таблицы, не добавляя столбцов к самим данным.

Смотрим в коротком видео со звуком.

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

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

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

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

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

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

Задать указанную точность

Этот инструмент округлит все ваши числа с такой точностью, с какой они отображаются (отформатированы)

Так что использовать его надо очень осторожно и это скорее предупреждение, но вдруг кому-то пригодится 😸

Когда мы просто форматируем число — меняем его формат, например, снижая число знаков после запятой — мы никак не влияем на значение. И вычисления будут идти с точными значениями, а не отображаемыми.

А эта опция именно изменит числа, как если бы:
1 вы применили одну из функций рабочего листа для округления, например, ОКРУГЛ / ROUND, или
2 изменили тип данных в столбце в Power Query (на целое число, например)

В коротком видео на 2 минуты со звуком — демонстрация того, как это происходит и где найти в параметрах.
А именно — по этому адресу:
Файл — Параметры — Дополнительно — Задать указанную точность

Ctrl + левая кнопка мыши: быстрое копирование листов или объектов

Нужно создать копию листа? Зажимаем Ctrl и тянем ярлык существующего листа мышкой. Получаем копию.

Это чудо работает не только с листами, но и с фигурами, например (см видео). Или с диаграммами.

И не только в Excel, но и в других приложениях. Например, в Power Point или Google Презентациях 🔥

Добавляем к дате день недели и выделяем выходные

Допустим, мы с вами хотим видеть в каждой дате день недели — не "01.01.2024", как по умолчанию, а "01.01.2024 Пн".

Для этого заходим в формат ячеек (Ctrl + 1) и добавляем к формату "ДДД" (DDD). Это краткое обозначение дня недели ("Пн"). Для полного ("Понедельник") понадобится код "ДДДД" (DDDD).

Ну а чтобы выделить цветом выходные (или другие дни) — воспользуемся условным форматированием (Conditional Formatting).
Зададим правило с формулой, а в ней будем использовать функцию ДЕНЬНЕД / WEEKDAY.
Она возвращает порядковый номер дня недели. Чтобы нумерация была привычной для нас с вами, добавьте второй аргумент, равный двойке:
=ДЕНЬНЕД (ячейка с первой датой в диапазоне; 2)
Тогда понедельнику будет соответствовать единица (иначе неделя будет начинаться с воскресенья, если пропустить второй аргумент функции), вторнику — двойка и так далее.

И остается добавить условие — день недели у нас должен быть больше 5 (то есть 6 или 7, суббота или воскресенье), чтобы ячейка заливалась цветом.

Быстрая фильтрация в сводной таблице

Если вам нужно быстро исключить некоторые значения из сводной: выделите то, что нужно убрать (в строках или столбцах отчета сводной таблицы) и нажмите Ctrl + - (минус).

Данные будут отфильтрованы, те значения, что вы выделяли, будут исключены в фильтре.

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

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

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

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

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

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

Слегка экзотическое применение формул массива: защищаем строки от удаления

Выделяете много строк где-нибудь далеко справа.

И вводите в них формулу массива, возвращающую ничего

=""  

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

=0  

И вводите формулу сочетанием клавиш Ctrl + Shift + Enter.
Это старые формулы массива, работающие во всех версиях.

Готово — теперь удалить отдельную строчку будет нельзя, ибо в этих строках формула массива, которую можно удалить только целиком.