Магия Excel

Видео про функцию ПРОСМОТРX / XLOOKUP Это чудо для поиска (объединения таблиц) появилось в Excel 2021 и в Google Таблицах. И лишено некоторых минусов функции ВПР — легендарной функции, чего уж там! — ВПР ищет только в первом столбце таблицы, а ПРОСМОТРX ссылается на отдельные столбцы (где ищем и откуда возвращаем данные) — ей все равно, какая структура данных; — ПРОСМОТРX по умолчанию ищет текст (точное совпадение), а ВПР — ближайшее наименьшее число; — В режиме поиска числа ПРОСМОТРX не требует сортировки данных и умеет искать и ближайшее наибольшее тоже; — Есть отдельный аргумент для замены ошибок (когда ничего не найдено) на другое значение. Но зато ВПР умеет работать с символами подстановки (* и ?) по умолчанию, а ПРОСМОТРX — нет, нужно задавать специальный аргумент для этого. Вот видео про эту функцию: https://youtu.be/4wigZhde7jY Это первое видео бесплатного открытого мини-курса на Stepik про новые функции Excel, заглядывайте на огонек: https://stepik.org/course/182713/

Вычисляем период в днях/месяцах/годах: функция РАЗНДАТ / 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 дней).

Хочу изучить конкретную тему в рамках 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

Изменяем стандартную диаграмму Построить диаграмму в 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) — щелкните на этот тип в окне "Изменение типа диаграммы" и нажмите туда же — "Сделать стандартной".

Получаем название листа формулой Функция ЯЧЕЙКА / CELL может выдавать разную информацию: например, полное имя файла (книги) вместе с листом. Для этого ее первый и единственный обязательный аргумент должен быть равен "имяфайла" ("filename"). А дальше — дело техники — вытаскиваем только имя листа текстовыми функциями. В новом Excel совсем удобно: ТЕКСТПОСЛЕ / TEXTAFTER вытащит все, что после квадратной скобки. =ТЕКСТПОСЛЕ(ЯЧЕЙКА("имяфайла");"]") В старых версиях Excel воспользуемся комбинацией функций: НАЙТИ / FIND подскажет, на какой позиции находится скобка, ДЛСТР / LEN — сколько в имени вообще символов — исходя из этого поймем, какая длина названия листа — в нашем случае 5 символов — именно столько извлечем с конца текстовой строки с помощью функции ПРАВСИМВ / RIGHT. =ПРАВСИМВ(ЯЧЕЙКА("имяфайла");ДЛСТР(...)-НАЙТИ("]";...))

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

Ctrl + левая кнопка мыши: быстрое копирование листов или объектов Нужно создать копию листа? Зажимаем Ctrl и тянем ярлык существующего листа мышкой. Получаем копию. Это чудо работает не только с листами, но и с фигурами, например (см видео). Или с диаграммами. И не только в Excel, но и в других приложениях. Например, в Power Point или Google Презентациях 🔥

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

Горячие клавиши для быстрого перемещения и выделения в Excel🔥 Ctrl + PgDn/PgUp — следующий/предыдущий рабочий лист Ctrl + Backspace — возвращаемся к активной ячейке Ctrl + A — выделяем всю текущую область (диапазон) Shift + пробел — выделяем всю строку (если активна "умная таблица" — то в пределах таблицы, иначе — в пределах всего листа) Ctrl + пробел — выделяем весь столбец Ctrl + стрелки — перемещаемся в конец диапазона (в направлении стрелки). Вместе с Shift — выделяем до конца диапазона. Ctrl + End — перемещаемся в конец активной области на листе (в самые последние строку и столбец с данными) P.S. Если делаете какое-то действие часто, на последнем шаге, когда кликаете на какую-то команду, остановитесь на секунду, наведите курсор на команду и посмотрите на подсказку — вполне вероятно, что там будет сочетание клавиш для нее.

Как извлечь из текстовой строки все символы, кроме первых N (например, первых двух)? 1. Чтобы вычислить, сколько символов нужно извлечь, смотрим на число символов в тексте (функция ДЛСТР / LEN) и вычитаем N. Так получим число знаков, которые нужно извлечь. 2. Ну а чтобы их извлечь, используем ПРАВСИМВ / RIGHT — эта функция извлекает из текста (первый аргумент) заданное во втором аргументе число символов. Если нужно было бы вырезать с начала — то ЛЕВСИМВ / LEFT. Получается: =ПРАВСИМВ(текст; ДЛСТР(текст) — N) Альтернатива без формул — мгновенное заполнение. Вводим в первой строке то, что нужно извлечь — Enter — Ctrl+E — вуаля!

Подбор параметра (Goal Seek) находится в коллекции «Анализ “Что если”» (What-If Analysis) на вкладке ленты «Данные». Он помогает ответить на вопрос "какой должна быть переменная X, чтобы на выходе получить N". Допустим, у нас есть простейшая модель с выручкой и маржинальной прибылью. Есть параметры, введенные как значения (производство в штуках, цена и себестоимость) и есть результирующие показатели, которые вычисляются формулами. Если мы хотим идти от результата (допустим, маржинальная прибыль = 750 000) и понять, каким должен быть входящий параметр (один из — допустим, себестоимость при прочих равных) для желаемого результата, нам нужен подбор параметра.

В очередной раз на корпоративном обучении выяснилось, что многие не знают про двойной щелчок для протягивания формул или значений (и это нормально и здорово: значит, те из вас, кто про это еще не слышал, на этом сэкономят немало времени) Итак, если мы наводим курсор на правый нижний угол ячейки, и он превращается в черный крестик, можно щелкнуть дважды и формула скопируется до конца столбца (до последней строки с данными). Это сработает и не только для формул, но и для последовательности чисел или дат (допустим, у вас 1 и 2 в первых двух ячейках — двойной щелчок продолжит ряд, как если бы вы использовали инструмент "Прогрессия" или тянули руками за уголок ячейки). До Excel 2010 магия ломалась на пустых ячейках в соседнем столбце, но потом починили: если даже есть пропуски в столбце слева, формула протянется до конца. Какие еще варианты? Можно использовать Таблицы (Tables) — Ctrl+T или Ctrl+L и вперед — формулы в Таблицах автоматически копируются на все строки. Еще можно использовать сочетание Ctrl + D для заполнения вниз. Но для этого придется сначала выделить все ячейки в столбце. То есть пойти в конец диапазона (Ctrl + End), потом выделить столбец до первой ячейки (Ctrl + Shift + ↑). Кстати, Ctrl + R — это заполнение вправо, тоже может пригодиться. P.S. Google Таблицы сами предлагают протянуть формулу в таких ситуациях — просто можно нажать галочку или Ctrl + Enter. Двойной щелчок и Ctrl + D / Ctrl + R там тоже работают.