Магия Excel

В строке формул можно переходить на следующую строку с помощью Alt+Enter. Это позволяет визуально разделить отдельные фрагменты/функции — тогда формулу будет проще воспринимать (вашим коллегам и вам самим в будущем, когда вы уже забудете ее логику). Это может помочь, если у вас уже многоэтажная формула, а в ней возникает синтаксическая ошибка. Обратите внимание, что высоту строки формул можно менять — достаточно потянуть за нижнюю границу, удерживая нажатой левую кнопку мыши. Также можно пробелами ставить отступы в формуле, если это поможет вам с восприятием формулы На скриншоте формула с переносами строк, но без отступов. Каждая функция начинается с новой строки. На работу формулы это не влияет.

Видеоурок: модель данных Power Pivot. Создание отношений между таблицами. Друзья, делюсь с вами одним из уроков нового модуля курса "Магия Excel", посвященного модели данных Excel (Power Pivot). https://www.youtube.com/watch?v=IR-rjAsC968 А весь курс можно найти тут — в нем 14 модулей и сотни минут таких видеоуроков, домашки и файлы со всеми примерами — в исходном и готовом виде: https://www.mann-ivanov-ferber.ru/courses/magicexcel/

Получаем последнюю дату из таблицы Если нам просто нужно последнее значение из столбца (по порядку, нижнее) - можно использовать функцию ПРОСМОТР / LOOKUP. Введем у нее в первом аргументе число, которое априори больше любой даты (можно просто 100 000). И тогда ПРОСМОТР вернет последнее значение. (подробнее про ПРОСМОТРчитайте здесь) Если нужна самая поздняя дата, то можно вспомнить, что любая дата в Excel — это число, и просто взять максимальное число из столбца с помощью функции МАКС / MAX. Это будет последняя дата, в какой бы строке она ни находилась. А если нужна не просто самая поздняя, а поздняя у определенного администратора? С условием, иначе говоря. Если у вас Excel 2019, 2021, Google Таблицы — можно воспользоваться функцией МАКСЕСЛИ / MAXIFS (подробнее о ней тут; она работает как СУММЕСЛИМН / SUMIFS, только не суммирует, а ищет максимум). А если другие версии Excel? Сделаем МАКСЕСЛИ сами. Из... МАКС и ЕСЛИ :) Функцией ЕСЛИ будем проверять столбец с именами на соответствие нужному (B2:B132="Лемур") и возвращать при выполнении условия даты из столбца A. При невыполнении условия функция ЕСЛИ вернет просто логическое значение ЛОЖЬ, так как мы ничего явно в третьем аргументе не указали. На выходе получим массив, где будут даты, когда работал Лемур, и значения ЛОЖЬ, когда работали другие. Функция МАКС в этом массиве найдет самую большую дату. Чтобы формула сработала, ее нужно ввести как формулу массива — заклинанием Ctrl+Shift+Enter.

Вот так новости! Наконец-то в Excel появились функции для работы с регулярными выражениями. Но, увы, как водится с новинками — только у подписчиков 365. Напоминаем, что в Google Таблицах функции для работы с регулярками есть и доступны всем — там они называются REGEXEXTRACT (извлекаем), REGEXMATCH (проверяем соответствие), REGEXREPLACE (заменяем). В Excel до появления этих функций можно было работать с регулярками через макросы — вот статья маэстро Николая Павлова на эту тему. Официальная новость про функции Вот некоторые материалы по регулярным выражениям из нашего канала про Google Таблицы (больше найдете в канале по поиску, примеров очень много): Вытаскиваем utm из ссылки Приводим mm-dd к dd-mm не формулой Меняем формат даты с ММ/ДД/ГГГГ на ДД.ММ.ГГГГ формулой Таблица с примерами регулярок от участников сообщества Извлекаем числа, едим пончики Извлекаем актуальное число подписчиков телеграм-каналов из ссылки вида t.me/канал

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

Функция SCAN: нарастающий итог — простой, по каждому году/месяцу или с условием SCAN — одна из вспомогательных функций LAMBDA, которая позволяет пробегаться по массиву, обращаясь к каждому элементу и накопленному итогу. И творить всякую магию. Доступно это удовольствие в Google Таблицах и в Excel 365 / Excel Online. В этой статье разбираем ее синтаксис и разные варианты расчета нарастающего итога: — простой нарастающий итог — для демонстрации работы функции — нарастающий итог в рамках каждого месяца(периода). То есть одной формулой для всей таблицы получаем нарастающий итог в рамках месяца (или года/недели/другого периода), а с началом периода он обнуляется и начинается по новой. — нарастающий итог по условию. То есть считаем только определенные строки, а не все (например, выручку только в те дни, когда работал определенный администратор). Строки, в которых условие не выполняется, в нарастающий итог не попадают. Файлы с примерами из статьи: Рабочая книга Excel Google Таблица https://teletype.in/@renat_shagabutdinov/scanexcelsheets

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

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

Окно «Найти и заменить» (Find and Replace) во многих случаях помогает решить задачи по обработке текстовых значений (и не только) без применения сложных функций и формул. Это окно позволяет исправить большое количество формул, поменять форматирование всех однотипных ячеек, удалить определенные слова или символы из диапазона или из всей книги Excel. Его можно вызвать сочетаниями клавиш Ctrl + F (⌘ + F) или Ctrl + H (⌃ + H) — в обоих случаях откроется одно и то же диалоговое окно, но в первом случае на вкладке «Найти» (Find), а во втором — «Заменить» (Replace). Вот несколько нюансов: — Если вы предварительно выделили диапазон ячеек, то поиск/замена будут производиться в пределах этого диапазона. Если же нет — то на листе или в книге (изменить этот параметр можно в поле «Искать» (Within) в окне «Найти и заменить»; по умолчанию будет лист). — Если вы хотите что-то удалять, а не заменять, просто оставьте поле «Заменить на» пустым. Заменить на ничто = удалить, не так ли? — Можно производить изменения сразу с большим количеством формул. Например, вам нужно поменять диапазон или функцию во многих формулах. Выделите диапазон с формулами, вызовите окно «Найти и заменить» и введите в поле «Найти» тот фрагмент формул, который вы хотите изменить, а в «Заменить на» — то, на что хотите его изменить. Убедитесь, что в списке «Область поиска» (Look in) заданы «Формулы» (Formulas).

Часто создаете "умные" таблицы? Хорошая практика — их переименовывать (чтобы в формулах ссылаться не на "Таблица1", "Таблица2", а на "Прайс" или "Остатки") Если приходится переименовывать их часто, поле "Имя таблицы" можно добавить на панель быстрого доступа! И оно всегда будет наверху во всех книгах Excel при любой активной вкладке ленты инструментов. Активно оно будет, конечно, только когда вы будете трогать руками таблицы. При активации обычных диапазонов поле будет серым, но с панели быстрого доступа никуда не уйдет. Чтобы добавить инструмент на панель, просто щелкните по нему правой кнопкой мыши и выберите соответствующую команду в контекстном меню.

Мгновенное заполнение (Flash Fill) — когда вы нажимаете Ctrl+E (или включаете мгновенное заполнение с ленты инструментов, Главная — Заполнить), Excel анализирует всю строку, а не один столбец. Так что можно собирать данные из нескольких столбцов — Можно исправлять регистр текста — Можно добавлять символы, например, точки Все это — в примере с ФИО, где мы добавляем точки, делая инициалы вместо полных ИО, собираем данные из трех столбцов, исправляем регистр. Все это без формул и быстро :) Но только с версии Excel 2013 включительно.

Выделяем цветом формулы по какому-то признаку Вы хотите выделить визуально "старые формулы массива" (из версий до 2019 включительно), или формулы, ссылающиеся на какой-то лист, или формулы с определенными функциями. Получить текст формулы можно с помощью функции Ф.ТЕКСТ / FORMULATEXT. Искать в этом тексте какой-то признак можно с помощью функции НАЙТИ / FIND. И если все это засунуть в условное форматирование, то мы получим возможность выделять визуально формулы, содержащие что-нибудь! Например, старые формулы массива можно выделить по наличию фигурной скобки: =НАЙТИ("{";Ф.ТЕКСТ(первая ячейка форматируемого диапазона)) Ссылки на лист с названием — по этому самому названию =НАЙТИ("название листа";Ф.ТЕКСТ(первая ячейка ...)) Определенные функции — по их названию. Например, ПРОСМОТРX, которой нет в старых версиях: =НАЙТИ("ПРОСМОТРX";Ф.ТЕКСТ(ячейка)) А вот выделить формулы со старой функцией ПРОСМОТР можно, добавив к "запросу" скобку — иначе будут выделяться формулы, где есть и ПРОСМОТР, и ПРОСМОТРX. =НАЙТИ("ПРОСМОТР(";Ф.ТЕКСТ(ячейка))