Подбор параметра (Goal Seek) находится в коллекции «Анализ “Что если”» (What-If Analysis) на вкладке ленты «Данные».

Он помогает ответить на вопрос "какой должна быть переменная X, чтобы на выходе получить N". Допустим, у нас есть простейшая модель с выручкой и маржинальной прибылью. Есть параметры, введенные как значения (производство в штуках, цена и себестоимость) и есть результирующие показатели, которые вычисляются формулами.

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

Как извлечь из текстовой строки все символы, кроме первых N (например, первых двух)?

1. Чтобы вычислить, сколько символов нужно извлечь, смотрим на число символов в тексте (функция ДЛСТР / LEN) и вычитаем N. Так получим число знаков, которые нужно извлечь.

2. Ну а чтобы их извлечь, используем ПРАВСИМВ / RIGHT — эта функция извлекает из текста (первый аргумент) заданное во втором аргументе число символов. Если нужно было бы вырезать с начала — то ЛЕВСИМВ / LEFT.

Получается:
=ПРАВСИМВ(текст; ДЛСТР(текст) — N)

Альтернатива без формул — мгновенное заполнение. Вводим в первой строке то, что нужно извлечь — Enter — Ctrl+E — вуаля!

Горячие клавиши для быстрого перемещения и выделения в Excel🔥

Ctrl + PgDn/PgUp — следующий/предыдущий рабочий лист

Ctrl + Backspace — возвращаемся к активной ячейке

Ctrl + A — выделяем всю текущую область (диапазон)

Shift + пробел — выделяем всю строку (если активна "умная таблица" — то в пределах таблицы, иначе — в пределах всего листа)
Ctrl + пробел — выделяем весь столбец

Ctrl + стрелки — перемещаемся в конец диапазона (в направлении стрелки). Вместе с Shift — выделяем до конца диапазона.

Ctrl + End — перемещаемся в конец активной области на листе (в самые последние строку и столбец с данными)

P.S. Если делаете какое-то действие часто, на последнем шаге, когда кликаете на какую-то команду, остановитесь на секунду, наведите курсор на команду и посмотрите на подсказку — вполне вероятно, что там будет сочетание клавиш для нее.

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

Shift позволяет выделять сразу несколько объектов/символов от активного до того, на который щелкнете.

Это работает во многих ситуациях:
— При выделении диапазонов — щелкните на любую ячейку с зажатой Shift и выделится весь диапазон от активной до той, на которую щелкнули.
— Группируем листы в книге Excel — с Ctrl можно выделять по одному, а вот с Shift'ом — сразу от текущего до любого (нужно кликнуть на последний группируемый лист с зажатой клавишей Shift)
— В срезах можно выделять сразу несколько элементов с Shift'ом
— Фрагмент формулы при ее редактировании
— И не только в Excel — в текстовых редакторах и браузере можно выделять текст, в Проводнике — файлы и папки

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

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

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

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

Бесплатный мини-курс по новым функциям Excel на Stepik

Друзья, записал крошечный курс (5 тем — небольшое текстовое описание функций + видео-уроки) по новым функциям Excel.

— Функция ПРОСМОТРX / XLOOKUP — замена легендарной ВПР / VLOOKUP
— Динамические массивы Excel — новые правила работы с массивами в Excel и появившиеся благодаря ним функции
— Новые функции для работы с массивами — как ВСТОЛБИК / VSTACK, позволяющая объединять массивы в один или ВЫБОРСТРОК / CHOOSEROWS, с которой можно извлечь отдельные строки из массива
— Функция LAMBDA — с ней можно создавать собственные функции или обрабатывать циклично каждую строку или столбец массива (и многое другое).

Можно посмотреть уроки абсолютно бесплатно на Stepik — по этой ссылке:
https://stepik.org/course/182713

Буду признателен, если поделитесь ссылкой с коллегами, а после прослушивания напишете в комментариях на платформе или здесь, как вам уроки!

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

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

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

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

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

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

Изменяем стандартную диаграмму

Построить диаграмму в Excel можно очень быстро — практически одной лапой, а точнее, сочетанием клавиш Alt + F1.
Эта комбинация вызывает вставку стандартной гистограммы (столбиков).

Что если вы хотите строить диаграмму другого вида? Например, не обычную гистограмму, а с накоплением (когда отдельные составляющие выстраиваются в один общий столбец), с таблицей данных (значения внизу диаграммы в таблице), с осью в тысячах или еще с чем-то?

Настройте диаграмму как вам хочется. После этого:
1 Щелкаем правой кнопкой и нажимаем "Сохранить как шаблон..." (Save as Template...)

2 В появившемся окне придумываем название, под которым шаблон диаграммы будет сохранен в файловой системе

3 Нажимаем на ленте инструментов во вкладке "Конструктор диаграмм" (Design) кнопку "Изменить диаграмму" (Change Chart Type)

4 Заходим в папку "Шаблоны" (Templates). Во-первых, мы уже можем пользоваться этим шаблоном отсюда и при вставке новых диаграмм! Но нам остается финальный штрих, чтобы именно эта диаграмма строилась по сочетанию клавиш Alt + F1.

5 Щелкаем по шаблону правой кнопкой мыши и нажимаем "Сделать стандартной" (Set as Default Chart).

P.S. Если хотите, чтобы стандартной были вообще не столбики, а другой тип — допустим, круговая (пирог, pie chart) — щелкните на этот тип в окне "Изменение типа диаграммы" и нажмите туда же — "Сделать стандартной".

Хочу изучить конкретную тему в рамках Excel. Какую одну книгу мне прочитать?

Excel в целом
Microsoft Excel Inside Out (Office 2021 and Microsoft 365)
На русском:
Excel 2019. Библия пользователя — Куслейка, Александер

Макросы
Microsoft Excel VBA and Macros — Bill Jelen
На русском: Excel 2016. Профессиональное программирование на VBA — Александер, Куслейка (не пугайтесь версии 2016 — макросы не меняются десятилетиями)

Сводные таблицы
Сводные таблицы в Microsoft Excel 2021 и Microsoft 365 — Джелен

Power Query
Скульптор данных в Excel с Power Query — Николай Павлов
или / и
Приручи данные с помощью Power Query в Excel и Power Bi — Пульс, Эскобар

Power Pivot и язык формул DAX (который используется и в Power BI / других решениях Microsoft)
Анализ данных при помощи Microsoft Power BI и Power Pivot для Excel — Руссо, Феррари

Очень глубоко и основательно про DAX: Подробное руководство по DAX: бизнес-аналитика с Microsoft Power BI, SQL Server Analysis Services и Excel — Руссо, Феррари

Для первого ознакомления с Power Pivot можно начать с глав в книге Джелена про сводные

Формулы в целом
С новыми формулами (LAMBDA, новые массивы), от начального до продвинутого уровня: главы про формулы в Microsoft Excel Inside Out.
С новыми формулами посложнее: Advanced Excel Formulas: Unleashing Brilliance with Excel Formulas
На русском с новыми формулами: главы про формулы у меня в "Магии таблиц"
На русском до 2019 включительно от начального до продвинутого: главы про формулы в Excel 2019. Библия пользователя
На русском до 2019 включительно посложнее: Мастер формул — Николай Павлов

Старые формулы массива (до 2019 включительно)
Ctrl+Shift+Enter Mastering Excel Array Formulas: Do the Impossible with Excel Formulas Thanks to Array Formula Magic — Girvin
На русском: Мастер формул — Николай Павлов

Новые формулы массива (динамические массивы)
Up Up and Array!: Dynamic Array Formulas for Excel 365 and Beyond
На русском: немного есть у меня в "Магии таблиц"

Визуализация
Визуализация данных при помощи дашбордов и отчетов в Excel — Куслейка

Подробный обзор, где больше книг — по постоянному адресу:
https://teletype.in/@renat_shagabutdinov/excellent_books

Вычисляем период в днях/месяцах/годах: функция РАЗНДАТ / DATEDIF

Если вам нужно вычислить разницу между двумя датами не в днях (для чего достаточно вычесть из одной даты другую или воспользоваться функцией ДНИ / DAYS), а в месяцах или годах (например, возраст) — пользуйтесь функцией РАЗНДАТ / DATEDIF. В Excel при ее вводе не будут отображаться всплывающая подсказка с аргументами, Excel не предложит ее дописать, но не обращайте на это внимания — она работает во всех версиях. И в Google Таблицах тоже!

 =РАЗНДАТ(дата_начала; дата_окончания; единица измерения)

Первые два аргумента — даты начала и окончания периода. Они могут быть указаны прямо в формуле в кавычках либо в виде ссылок на ячейки с датами, а также быть заданными функцией СЕГОДНЯ / TODAY.

Единица измерения задается в кавычках. Есть следующие возможные варианты:

"d" — число дней (такой параметр не имеет особого смысла, так как для этой задачи подойдет и функция ДНИ / DAYS, и просто вычитание);
"m" — число полных месяцев в периоде;
"y" — число полных лет в периоде;
"md" — разница в днях без учета месяца и года (например, между 01.01.2021 и 15.06.2022 — 14 дней);
"ym" — разница в месяцах без учета дня и года (например, между 01.01.2021 и 15.06.2022 — 5 месяцев);
"yd" — разница в днях без учета года (например, между 01.01.2021 и 15.06.2022 —165 дней).

Видео про функцию ПРОСМОТРX / XLOOKUP

Это чудо для поиска (объединения таблиц) появилось в Excel 2021 и в Google Таблицах. И лишено некоторых минусов функции ВПР — легендарной функции, чего уж там!

— ВПР ищет только в первом столбце таблицы, а ПРОСМОТРX ссылается на отдельные столбцы (где ищем и откуда возвращаем данные) — ей все равно, какая структура данных;
— ПРОСМОТРX по умолчанию ищет текст (точное совпадение), а ВПР — ближайшее наименьшее число;
— В режиме поиска числа ПРОСМОТРX не требует сортировки данных и умеет искать и ближайшее наибольшее тоже;
— Есть отдельный аргумент для замены ошибок (когда ничего не найдено) на другое значение.

Но зато ВПР умеет работать с символами подстановки (* и ?) по умолчанию, а ПРОСМОТРX — нет, нужно задавать специальный аргумент для этого.

Вот видео про эту функцию:
https://youtu.be/4wigZhde7jY

Это первое видео бесплатного открытого мини-курса на Stepik про новые функции Excel, заглядывайте на огонек:
https://stepik.org/course/182713/

Вот такая табличка: сравнение старого и нового Excel и Google Таблиц по доступности новых функций.

Это один из многих слайдов практикума "Новые функции", который пройдет 7, 14 и 20 ноября.
На самих занятиях слайды смотреть не будем — это дополнительный материал, а во время вебинаров будет много практики и ответы на вопросы.

Практиковаться можно будет и в Google Таблицах (если нет подписки 365), и в Excel!

Присоединяйтесь!
https://www.mann-ivanov-ferber.ru/courses/practicum-excel/

Лемур принес вам скидочку 35% — по промокоду LEMUREC, действующему до 7 ноября.

А тут, напоминаем, подробное сравнение Excel и Google Таблиц по всем нюансам.

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

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

2 Двойной щелчок по заголовку поля ("Сумма по полю...")

3 Щелчок по полю в редакторе сводной таблицы — "Параметры полей значений"

Видите неактивную опцию "Число разных элементов"? Это подсчет уникальных значений. Он будет доступен, если сводная будет построена на основе модели данных. Так можно сделать, даже если исходные данные — всего одна таблица. При построении сводной включите флажок "Добавить эти данные в модель данных"

Удаляем пустые строки

Выделяем диапазон, в котором нужно удалить пустые ячейки (в видео - все данные на листе с помощью Ctrl+Shift+End).

Для этого нужен инструмент "Найти и выделить" (на ленте на вкладке "Главная", Home — Go To) — выбираем там "Выделить группу ячеек" и в появившемся диалоговом окне — "Пустые ячейки" (Blanks).
Еще можно нажать F5 или Ctrl + G и в появившемся окне нажать "Выделить".

После этого остается нажать Ctrl + - (Ctrl и минус) — это удаление ячеек/строк/столбцов. И выбрать "строку".

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

Избранное: актуальная и обновленная подборка самых сочных материалов нашего канала

Ctrl + Backspace - очень удобное сочетание клавиш для возвращения к активной ячейке

Макрос для сравнения двух файлов (книг Excel)

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

Макрос: создаем оглавление в книге

Анализируем сезонность в сводной таблице

Поиск по двум критериям

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

Видеоурок: "старые" и новые формулы массивов

Функция СУММЕСЛИМН / SUMIFS: сумма по условиям

Собираем данные с разных листов в Excel и Google Таблицах (список листов - динамический)

"Протягивание формул" (двойной щелчок и сочетания клавиш)

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

Сравнение списков (Видео)

Нарастающий итог: закрепляем только первую ячейку в диапазоне

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

1. Ссылаемся на первую ячейку, эфчетырим ее (то есть нажимаем F4, чтобы сделать ссылку абсолютной, "закрепить")

2. Вводим двоеточие и ту же самую ячейку, но уже оставляем относительной. Получается диапазон с началом и концов в одной ячейке, но конец не закреплен - так что при протягивании/копировании формулы будет меняться.
=СУММ($B$2:B2)

3. Протягиваем и получаем диапазон с началом в одной и той же ячейке и концом в текущей строке.

Хотите видеть какую-то книгу Excel всегда наверху в списке последних файлов?

На стартовом экране (Backstage) наводите курсор на нужный файл — справа появится кнопка (которая выглядит как... кнопка) "Закрепить" (Pin). Нажимайте и книга будет всегда наверху.
Точно так же можно открепить обратно.

Хотите, чтобы стартовый экран не показывался при включении Excel (или другого приложения Office)?
Заходите в Параметры — Общие — отключайте "Показывать начальный экран при запуске этого приложения"

Функция ТЕКСТ / TEXT: превращаем число в текстовое значение в заданном числовом формате

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

Для чего нужна?
Допустим, вы хотите "склеить" в одну текстовую строку текст и число.
Чтобы получить в таблице надпись вида "По состоянию на: 30.06.23" или "Сумма продаж: 20 500". То есть текст из фиксированной части и какого-то вычисления/функции, как-то суммы чисел или текущей даты.

Проблема в том, что если сделать это "в лоб" без функции ТЕКСТ / TEXT, форматирование потеряется. Число будет без разделителей разрядов, со всеми знаками после запятой; дата будет в виде числа ("По состоянию на: 44742") — потому что вот так даты хранятся в Excel и Таблицах.

И функция ТЕКСТ позволяет это исправить — укажите нужный формат во втором аргументе, как если бы вводили его в окне "Формат ячеек" (Ctrl + 1).

Итак, для даты в нашем примере нужна будет такая формула:

="По состоянию на: " & ТЕКСТ (дата; "ДД.ММ.ГГ")

Подробнее про пользовательские числовые форматы можно посмотреть в видео — оно на основе Google Таблиц, но все работает практически идентично.

Как разделить текст по нескольким разделителям?

Например, по косой черте и дефису, как в примере.

В новой версии Excel можно воспользоваться функцией ТЕКСТРАЗД / TEXTSPLIT.
А чтобы она работала с несколькими разделителями, отправим их в массив:

{"первый разделитель"; "второй"; ... }

Если бы мы сделали так (не массив, а одна текстовая строка) — то оба символа считались бы одним разделителем:

"/-"

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

=SPLIT(A2; "/-")

или
=SPLIT(A2; "/-"; 1)

Выводим формулой список всех рабочих дней — от заданной до сегодняшней (так в примере; но можно и наоборот — как вам нужно)

Для этого:
1 вычислим число рабочих дней в периоде (функция ЧИСТРАБДНИ / NETWORKDAYS)

2 Засунем это число в функцию ПОСЛЕД / SEQUENCE и получим последовательность чисел от 1 до числа рабочих дней в периоде

3 отправим эту последовательность в функцию РАБДЕНЬ / WORKDAY — она возвращает дату, которая наступит по прошествии N рабочих дней от заданной. В нашем случае она выдаст много дат, по одной для каждого числа полученной на прошлом шаге последовательности.

Формула такая:

=РАБДЕНЬ(начальная дата-1;ПОСЛЕД(ЧИСТРАБДНИ(начальная дата ;конечная дата)))

На скриншоте конечная дата задается функцией СЕГОДНЯ / TODAY — так что список будет обновляться каждый день (кроме выходных 😉)

P.S. G.S. В Google Sheets тоже будет работать, только не забудьте нажать Ctrl+Shift+Enter, чтобы добавилась функция ArrayFormula.

А как быть в старых версиях Excel (до 2019 включительно), где функции ПОСЛЕД / SEQUENCE нет?

Там можно воспользоваться прогрессией (Series). Ищите инструмент на вкладке "Главная" в коллекции команд "Заполнить" (стрелка вниз).
Увы, это будет статичная история, а не динамическая, как в случае с формулами... но тоже неплохо!