Магия Excel

Курсы по Excel и Google Драйву Друзья, несколько новостей по моим табличным курсам в МИФе Во-первых, добавил новые видео и модули в эти курсы: - В оба курса — модуль про функцию LAMBDA и вспомогательные функции BYROW/ BYCOL, SCAN и REDUCE, MAP и ISOMITTED (последняя в Excel). В случае с Гугл Драйвом — отдельное видео про именованные функции в Google Таблицах. - Видео про ПРОСМОТРX / XLOOKUP в Google Таблицах — добавлено некоторое время назад, благо функция там появилась вскоре после Excel. В курсе про Excel эта функция и так, разумеется, была. - Большое видео про Power Pivot (модель данных) в Магии Excel будет скоро. Во-вторых, с 1 ноября цена на оба курса вырастет. Так что если думали про то, чтобы изучить основательно ту или иную тему или обучить свою команду/компанию (а на этих курсах у нас учатся сотрудники самых разных компаний, включая компанию из топ-10 крупнейших в России и национальный исследовательский университет) — самое время брать курс по старой цене с новыми материалами. Тем более несколько дней будет скидка — см ниже. Немного фактов. В каждом курсе есть исходные и заполненные файлы с примерами к каждому уроку, а в отдельных тарифах и подробные конспекты (суммарно как книга). В Магии Excel 60+ уроков, в Гугл Драйве 90+. Урокам можно ставить оценки. За все время у Гугл Драйва 600 оценок (из них 7 четверок и 2 тройки), у Магии Excel 245 оценок (тоже почти все пятерки: только 4 четверки, ни одной тройки). Коллеги подкинули промокод на скидку, ловите: LEMURY 35% до 5 октября. https://www.mann-ivanov-ferber.ru/courses/magicexcel/ https://www.mann-ivanov-ferber.ru/courses/gdrive/

Мгновенное заполнение — один из самых простых и полезных инструментов Excel Появилось мгновенное заполнение в Excel 2013. Этот инструмент позволяет преобразовать данные или извлечь какой-то фрагмент: достаточно задать 1-2 образца того, что надо получить из исходного столбца (или нескольких столбцов), и мгновенное заполнение заполнит весь столбец значениями, исходя из заданного вами паттерна (шаблона). Это может быть не очевидным для тех, кто только знакомится с этим инструментом, но мгновенное заполнение анализирует всю строку, все смежные столбцы (то есть нужно вводить данные в любом соседнем столбце с данными, без перерыва в виде пустого столбца). И это значит, что можно обрабатывать (извлекать и объединять) данные из нескольких столбцов.

Извлекаем из таблицы строки с самой большой и маленькой сделкой (наименьшим и наибольшим числом) С новыми функциями это получается просто: сначала сортируем таблицу по сделкам (по аналогии можно сортировать по датам, тогда вы сможете взять самую старую и новую строки) с помощью SORT / СОРТ: =СОРТ(таблица; номер столбца, по которому сортируем) Если нужно по убыванию, то задаем третий аргумент, равный -1. Ну а далее, не выводя на лист отсортированный результат, сразу отправляем его внутрь функции CHOOSEROWS / ВЫБОРСТРОК — и берем первую (1) и последнюю (-1) строки. =ВЫБОРСТРОК(СОРТ(таблица; номер столбца для сортировки);1;-1) В общем виде ВЫБОРСТРОК имеет такой синтаксис: =ВЫБОРСТРОК(диапазон / массив; номер строки, которую извлекаем ; [еще номер строки]; ...) То есть можем извлечь и одну строку, и несколько — перечисляем столько номеров, сколько нужно. А если нужны все нечетные строки, например, с 1 по 100, можно использовать функцию ПОСЛЕД / SEQUENCE, чтобы не вводить столько чисел вручную: =ВЫБОРСТРОК(диапазон; ПОСЛЕД(50;;1;2))

Калькулятор в Excel... Ну, вообще-то строка формул (точнее, сами формулы, в любой ячейке) — вполне себе калькулятор, но если вы знаете толк в извращениях, вам нужны кнопочки M+, M-, CE и вы хотите вызывать калькулятор WIndows из Excel — почему нет 😺 (шутки шутками, но кнопок там намного больше, потому что есть разные режимы — инженерный, графики, вычисления с датами, преобразования) Заходим в параметры, панель быстрого доступа — и там из списка всех команд выбираем калькулятор и на эту самую панель быстрого доступа добавляем. Теперь у вас всегда наверху будет кнопка для открытия калькулятора (и еще будет сочетание клавиш Alt + цифра для него же). Options — Quick Access Toolbar — All Commands — Calculator — Add.

Друзья, наша с Лемуром книга тем временем стала №1 в жанре "Руководства по пользованию программами" в Лабиринте — в очень достойной компании😺 (особое внимание в этом топе обращаем на "Библию пользователя", хоть она и по 2019 Excel, книгу по визуализации Дика Куслейки и, конечно, книгу про сводные Билла Джелена — очень полезные вещи — про эти и другие книги можете почитать в обзоре) Если уже прочитали, пожалуйста, поставьте оценку / оставьте отзыв в любом из магазинов, мы будем очень благодарны! А купить можно тут: На сайте издательства Book24 Лабиринт Озон Литрес (электрическая) Wildberries И в оффлайне — например, в Библио-Глобусе или в регионах в Читай-городе, Буквоеде и других магазинах

Макрос: удаляем пустые листы На примере простой задачки (удалить все листы в текущей книге, на которых нет ни одного значения в ячейках) рассматриваем, как создать макрос в личной книге макросов и затем добавить на панель быстрого доступа, чтобы он всегда был под рукой. Код макроса — выше в файле формата .bas — для импорта просто зайдите в редактор VBA (Alt + F11) и нажмите там Ctrl + M для импорта. Если личной книги макросов еще нет, включите запись макроса, выберите сохранение в личную книгу макросов (в видео это есть) и сразу остановите запись. Тогда будет создана личная книга макросов PERSONAL.XLSB. Макросы, которые лежат в ней, доступны вам в любых книгах Excel.

Код макроса для удаления пустых листов

Вставили текстовые значения откуда-то и хотите быстро объединить? Можно использовать команду "Выровнять" (Justify). Сделайте ширину столбца такой, чтобы все значения, объединенные вместе в один текст, поместились в одну строку. После этого выделяйте ячейки и выбирайте команду "Выровнять" на вкладке "Главная" в коллекции "Заполнить". Home — Fill — Justify И вжух!

Если в сводной таблице нужно произведение нескольких столбцов, обычное вычисляемое поле не подойдет: в нем все значения будут сначала суммироваться, а потом умножаться. А нужно считать произведение в каждой строке (например, проданные штуки умножать на цену) и потом суммировать результаты. Для этого нужно либо добавлять столбец в исходных данных, где вычислять нужное произведение, либо построить сводную на основе модели данных (если в вашей версии она, то есть Power Pivot, есть) и использовать меры. В мерах можно использовать функции DAX, в частности, SUMX, которая вычисляет выражение построчно и только потом суммирует — смотрим пример в видео!

Функция ISOMITTED / ПРОПУЩЕНО: добавляем к пользовательским функциям необязательные аргументы Вашему вниманию кусочек видео из будущего нового модуля курса "Магия Excel", посвященного функции LAMBDA. LAMBDA позволяет создавать собственные функции. Синтаксис у нее такой: =LAMBDA([переменная]; … ; [переменная]; формула) Например, мы можем задать два аргумента — план и факт — и потом использовать их в вычислении, сделав формулу для расчета темпа прироста: =LAMBDA(план ; факт ; факт / план - 1) В самих ячейках LAMBDA работать напрямую не будет — ведь тут параметры, а не конкретные значения / ячейки. Вы можете ее проверить, добавив конкретные значения в скобках после функции: =LAMBDA(план ; факт ; факт / план - 1)(B2;C2) Но в целом все это затевается ради того, чтобы использовать новую функцию под ее именем уже без всяких лямбд. Для этого нужно сохранить ее в диспетчере имен (Ctrl + F3) под любым именем, какое вы хотите присвоить этой функции — например, “ТемпПрироста”. И дальше использовать эту функцию в пределах книги (а если хочется перенести ее в другую — можно скопировать создать пустой лист в книге и скопировать/переместить его в другую книгу — это приведет к переносу имен, а значит, и функции). И в функциях можно даже создавать необязательные аргументы — для этого и нужна ISOMITTED / ПРОПУЩЕНО — она возвращает ИСТИНА / TRUE, когда аргумент пропущен. В видео пример создания пользовательской функции с необязательным аргументом.

Объединяем умные таблицы в одну: формулы и Power Query В видео разбираем такую задачу: собрать данные из нескольких умных таблиц. Если у вас Microsoft 365, то можно наслаждаться новыми формулами и использовать функцию ВСТОЛБИК / VSTACK. Так же в видео разбираем, как с ее помощью в сочетании с функцией ФИЛЬТР/FILTER фильтровать данные "в режиме реального времени" и добавить к результату фильтрации заголовки. Если версии 2010 и новее, то можно с помощью Power Query объединить таблицы в один запрос и далее анализировать данные вместе с помощью сводной таблицы или просто выгрузить на лист как одну таблицу.

Формулы Excel для новичков: разбираемся с разными типами ссылок (относительные, абсолютные и имена) Вашему вниманию небольшой фрагмент из книги "Магия таблиц" для тех, кто только начинает работать с формулами и хочет разобраться наконец с долларами и ссылками на ячейки в Excel (или Google Таблицах).