Режим перехода в конец (End mode)

Нажмите клавишу End — и в строке состояния появится надпись "Режим перехода в конец" (End mode)

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

Заполняем промежуточные шаги с помощью прогрессии

Допустим, вы знаете первое значение и то, к которому нужно прийти. Выделите весь диапазон от первого значения до последнего и вызовите инструмент "Прогрессия" (Series):
Главная — Заполнить (кнопка со стрелкой вниз в правой части ленты) — Прогрессия

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

Средневзвешенная цена

Если у нас есть данные по продажам товаров с разными ценами, мы не можем считать среднюю цену просто функцией СРЗНАЧ / AVERAGE. Ведь мы не будем учитывать число проданных товаров, и, как в примере, средняя цена будет некорректная.

= (650 + 1400 + 2200) / 3 = 1417.

Но мы продали совсем мало товаров за 2200 и за 1400! Фактическая средняя цена должна быть ближе к 650.

Поэтому правильно вычислить сумму продаж в деньгах и потом поделить на все штуки.

Сумму продаж в деньгах можно вычислить одной функций СУММПРОИЗВ / SUMPRODUCT — она перемножает числа из нескольких диапазонов и потом суммирует результаты. Останется только разделить результат на сумму проданных штук. И получится 752 — это уже похоже на правду!

Дано: есть данные за несколько лет с выручкой (или чем-то еще) по дням.

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

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

После группировки можно посмотреть на сумму показателя по месяцам, а можно на среднее значение. Еще раз уточняем: теперь это данные за все январи в периоде , то есть мы не смотрим на динамику во времени, а смотрим на сезонность! Если нам нужна динамика от месяца к месяцу, то нужна группировка и по годам, и по месяцам, как было изначально при построении сводной (в большинстве версий Excel поле с датами само группируется в таком формате при его переносе в область строк)

Смотрим на видео!

Нужно выделить все формулы?

Нажимаем Ctrl+G

В открывшемся окне "Переход" нажимаем "Выделить" (Special)

Далее — "Формулы" (Formulas)

Готово. Можно теперь покрасить ячейки с формулами, если хочется.

Хотите, чтобы в фигуре отображался какой-нибудь текст, сформированный формулой?

Например, текущее время с какой-нибудь надписью (Текущее время: 12:00") или что-то другое ("Выручка на дату 01.06: 1,2 млн")?

Для этого формируем в ячейке текст формулой, а потом ссылаемся на ячейку с формулой из фигуры (выделяйте фигуру, вводите знак "равно" и кликайте по ячейке, как в обычной формуле).

В примере используется функция ТДАТА / NOW — это текущие дата и время. И функция ТЕКСТ / TEXT — напоминаем, при объединении в текст числовых (а дата и время = число) значений они теряют форматирование. Если вам нужно время в заданном формате, например, ЧЧ:ММ, используйте функцию ТЕКСТ, которая превращает число в текст, но в нужном формате.

Суммируем с условием только видимые строки

Просто суммировать (а также считать среднее и еще несколько базовых операций) скрытые строки — это функция SUBTOTAL / ПРОМЕЖУТОЧНЫЕ.ИТОГИ.
А сумма с условием — это SUMIFS / СУММЕСЛИМН. Эта функция считает не скрытые, а все строки.

Как совместить и считать по видимым строкам с условием?

Во всех версиях — со вспомогательным столбцом, в Microsoft 365 можно и формулой, оба варианта здесь:
https://teletype.in/@renat_shagabutdinov/subtotal_sumifs

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

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

Получить текст формулы можно с помощью функции Ф.ТЕКСТ / FORMULATEXT. Искать в этом тексте какой-то признак можно с помощью функции НАЙТИ / FIND.
И если все это засунуть в условное форматирование, то мы получим возможность выделять визуально формулы, содержащие что-нибудь!

Например, старые формулы массива можно выделить по наличию фигурной скобки:

=НАЙТИ("{";Ф.ТЕКСТ(первая ячейка форматируемого диапазона))

Ссылки на лист с названием — по этому самому названию

=НАЙТИ("название листа";Ф.ТЕКСТ(первая ячейка ...))

Определенные функции — по их названию. Например, ПРОСМОТРX, которой нет в старых версиях:

=НАЙТИ("ПРОСМОТРX";Ф.ТЕКСТ(ячейка))

А вот выделить формулы со старой функцией ПРОСМОТР можно, добавив к "запросу" скобку — иначе будут выделяться формулы, где есть и ПРОСМОТР, и ПРОСМОТРX.
=НАЙТИ("ПРОСМОТР(";Ф.ТЕКСТ(ячейка))

Мгновенное заполнение (Flash Fill)

— когда вы нажимаете Ctrl+E (или включаете мгновенное заполнение с ленты инструментов, Главная — Заполнить), Excel анализирует всю строку, а не один столбец. Так что можно собирать данные из нескольких столбцов
— Можно исправлять регистр текста
— Можно добавлять символы, например, точки

Все это — в примере с ФИО, где мы добавляем точки, делая инициалы вместо полных ИО, собираем данные из трех столбцов, исправляем регистр. Все это без формул и быстро :) Но только с версии Excel 2013 включительно.

Часто создаете "умные" таблицы?

Хорошая практика — их переименовывать (чтобы в формулах ссылаться не на "Таблица1", "Таблица2", а на "Прайс" или "Остатки")

Если приходится переименовывать их часто, поле "Имя таблицы" можно добавить на панель быстрого доступа! И оно всегда будет наверху во всех книгах Excel при любой активной вкладке ленты инструментов.

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

Чтобы добавить инструмент на панель, просто щелкните по нему правой кнопкой мыши и выберите соответствующую команду в контекстном меню.

Окно «Найти и заменить» (Find and Replace) во многих случаях помогает решить задачи по обработке текстовых значений (и не только) без применения сложных функций и формул. Это окно позволяет исправить большое количество формул, поменять форматирование всех однотипных ячеек, удалить определенные слова или символы из диапазона или из всей книги Excel.

Его можно вызвать сочетаниями клавиш Ctrl + F (⌘ + F) или Ctrl + H (⌃ + H) — в обоих случаях откроется одно и то же диалоговое окно, но в первом случае на вкладке «Найти» (Find), а во втором — «Заменить» (Replace).

Вот несколько нюансов:
— Если вы предварительно выделили диапазон ячеек, то поиск/замена будут производиться в пределах этого диапазона. Если же нет — то на листе или в книге (изменить этот параметр можно в поле «Искать» (Within) в окне «Найти и заменить»; по умолчанию будет лист).

— Если вы хотите что-то удалять, а не заменять, просто оставьте поле «Заменить на» пустым. Заменить на ничто = удалить, не так ли?

— Можно производить изменения сразу с большим количеством формул. Например, вам нужно поменять диапазон или функцию во многих формулах. Выделите диапазон с формулами, вызовите окно «Найти и заменить» и введите в поле «Найти» тот фрагмент формул, который вы хотите изменить, а в «Заменить на» — то, на что хотите его изменить. Убедитесь, что в списке «Область поиска» (Look in) заданы «Формулы» (Formulas).

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

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

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

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

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

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

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

Функция SCAN: нарастающий итог — простой, по каждому году/месяцу или с условием

SCAN — одна из вспомогательных функций LAMBDA, которая позволяет пробегаться по массиву, обращаясь к каждому элементу и накопленному итогу. И творить всякую магию. Доступно это удовольствие в Google Таблицах и в Excel 365 / Excel Online.

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

Файлы с примерами из статьи:
Рабочая книга Excel
Google Таблица

https://teletype.in/@renat_shagabutdinov/scanexcelsheets

Если дата в ячейках записана как текстовое значение вида ДДММГГГГ, без точек/дефисов/других разделителей, можно превратить такой текст в настоящую дату формулой:

=ДАТА( ПРАВСИМВ( ячейка с датой; 4) ; ПСТР (ячейка; 3; 2) ; ЛЕВСИМВ (ячейка; 2) )

Функция ДАТА / DATE возвращает дату, заданную тремя параметрами — годом, месяцем и днем.
Ее аргументы мы получаем текстовыми функциями:
Год — извлекая первые цифры цифры с помощью ПРАВСИМВ / RIGHT (она возвращает первые N символов из текстовой строки)
Месяц — извлекая два символа, начиная с третьего, с помощью ПСТР / MID.
День — последние две цифры с помощью функции ЛЕВСИМВ / RIGHT.

Вот так новости!
Наконец-то в Excel появились функции для работы с регулярными выражениями. Но, увы, как водится с новинками — только у подписчиков 365.

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

Официальная новость про функции

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

Приводим mm-dd к dd-mm не формулой

Меняем формат даты с ММ/ДД/ГГГГ на ДД.ММ.ГГГГ формулой

Таблица с примерами регулярок от участников сообщества

Извлекаем числа, едим пончики

Извлекаем актуальное число подписчиков телеграм-каналов из ссылки вида www.tg-me.com/канал

Вам пришло на карту 20 000 ₽ за пару дней работы

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

Такому мастерству реально научиться за 3 месяца на курсе Skillbox «Как зарабатывать на презентациях». Первые заказы можно брать во время обучения.

На курс действует скидка. Потом поднимем цены.

Получать за слайд 2000 ₽: https://epic.st/PlPUf?erid=2Vtzqx2Ewf3

Реклама. ЧОУ ДПО «Образовательные технологии «Скилбокс (Коробка навыков)», ИНН: 9704088880

Получаем последнюю дату из таблицы

Если нам просто нужно последнее значение из столбца (по порядку, нижнее) - можно использовать функцию ПРОСМОТР / LOOKUP. Введем у нее в первом аргументе число, которое априори больше любой даты (можно просто 100 000). И тогда ПРОСМОТР вернет последнее значение.
(подробнее про ПРОСМОТР читайте здесь)

Если нужна самая поздняя дата, то можно вспомнить, что любая дата в Excel — это число, и просто взять максимальное число из столбца с помощью функции МАКС / MAX. Это будет последняя дата, в какой бы строке она ни находилась.

А если нужна не просто самая поздняя, а поздняя у определенного администратора? С условием, иначе говоря.
Если у вас Excel 2019, 2021, Google Таблицы — можно воспользоваться функцией МАКСЕСЛИ / MAXIFS (подробнее о ней тут; она работает как СУММЕСЛИМН / SUMIFS, только не суммирует, а ищет максимум).
А если другие версии Excel?
Сделаем МАКСЕСЛИ сами. Из... МАКС и ЕСЛИ :)

Функцией ЕСЛИ будем проверять столбец с именами на соответствие нужному (B2:B132="Лемур") и возвращать при выполнении условия даты из столбца A. При невыполнении условия функция ЕСЛИ вернет просто логическое значение ЛОЖЬ, так как мы ничего явно в третьем аргументе не указали. На выходе получим массив, где будут даты, когда работал Лемур, и значения ЛОЖЬ, когда работали другие.
Функция МАКС в этом массиве найдет самую большую дату.
Чтобы формула сработала, ее нужно ввести как формулу массива — заклинанием Ctrl+Shift+Enter.

Видеоурок: модель данных Power Pivot. Создание отношений между таблицами.

Друзья, делюсь с вами одним из уроков нового модуля курса "Магия Excel", посвященного модели данных Excel (Power Pivot).
https://www.youtube.com/watch?v=IR-rjAsC968

А весь курс можно найти тут — в нем 14 модулей и сотни минут таких видеоуроков, домашки и файлы со всеми примерами — в исходном и готовом виде:
https://www.mann-ivanov-ferber.ru/courses/magicexcel/