![](/upload/blog/avatar/500/nikolay-pavlov3.jpg)
Новая статья: Разделение многоуровневого списка по столбцам
![](/upload/blog/avatar/500/nikolay-pavlov3.jpg)
Новая статья: Правильный шрифт в строке формул
![](/upload/blog/avatar/500/nikolay-pavlov3.jpg)
Разбираемся, каким должен быть правильный шрифт и как его прикрутить к строке формул (но не к ячейкам листа!) в Microsoft Excel.
Новая статья: Календарный дашборд на Power Pivot
![](/upload/blog/avatar/500/nikolay-pavlov3.jpg)
![](https://www.planetaexcel.ru/upload/medialibrary/48f/u20wbgr61svuo00n7xnrd65ehwp9xdf8/calendar-dashboard-gif.gif)
Обновление статьи : Количество уникальных значений
![](/upload/blog/avatar/500/nikolay-pavlov3.jpg)
- формулой массива
- макросом на VBA
- удалением дубликатов
- простой сводной
- сводной по Модели Данных
Новая статья : Сравнение любых произвольных дат и/или товаров в Power BI
![](/upload/blog/avatar/500/nikolay-pavlov3.jpg)
При создании отчётов в Microsoft Power BI часто требуется сравнить текущий год (или месяц) с предыдущим, а какой-то выбранный товар - с его соседями из той же категории. С этим проблем нет - в Power BI есть куча встроенных функций на этот счёт.
Но что делать, если хочется сравнивать произвольно выбранные объекты? Т.е. любой выбранный год - с любым другим произвольно выбранным, а не обязательно предыдущим? Или выбранный товар нужно сравнить с каким-то другим, заданным? А может и то, и другое сразу, т.е. хочется выбирать интересные мне любые 2 года и сравнивать любые товары в них?
Обновление надстройки PLEX - версия 2023.1
![](/upload/blog/avatar/500/nikolay-pavlov3.jpg)
Из нового:
Фиксация содержимого диалоговых окон
В большинство диалоговых окон PLEX добавлена кнопка с символом замка, чтобы зафиксировать введённое в это окно содержимое и его параметры (содержимое полей ввода, положение переключателей, включенные флажки и т.д.) в течение текущего сеанса работы с Microsoft Excel. Данная функция может быть весьма полезна тем, кто многократно запускает какие-то инструменты PLEX с одинаковыми настройками и не хочет каждый раз задавать их вручную.
Для отмены фиксации нужно открыть соответствующее окно, отжать кнопку с замком и нажать на кнопку Отмена.
Выбор "умных" таблиц вместо диапазоновВ некоторых диалоговых окнах в полях ввода исходных данных добавлена стрелка слева, которая нужна для переключения между двумя режимами ввода – ручного указания диапазона (как обычно) или выбора одной из имеющихся в книге «умных» динамических таблиц из выпадающего списка:
Если вы используете "умные" таблицы, то это сильно упростит вам жизнь при работе в PLEX.
Новые функции для работы с датамиДобавлены новые пользовательские функции для работы с датами:
- Quarter - возвращает квартал для указанной даты в заданном формате (только номер или "1 кв" или "Q-1" и т.д.)
- DateInterval - возвращает разницу между двумя исходными датами в формате "Х лет Y мес Z дн"
- FirstDate и LastDate - возвращают самую раннюю и позднюю даты из указанного диапазона (при этом игнорируют в нём всё кроме дат)
Если у вас Excel 2021 или новее, то ваша версия Excel поддерживает динамические массивы (dynamic arrays) - революционно-новый и супермощный инструмент для работы с данными. Если вы с ними ещё не знакомы - см.
Изначально для выполнения операций с ДМ в Excel было всего 5 базовых функций (FILTER, SORT, SORTBY, UNIQUE, SEQUENCE), а в последних обновлениях добавили ещё несколько (но они доступны только подписчикам Office 365 😪).
Следующие 17 функций PLEX предназначены восполнить этот пробел и дать вам возможность управлять динамическими массивами гораздо более гибко:
- VPile - составляет исходные диапазоны по вертикали столбиком друг на друга
- HPile - объединяет исходные диапазоны, размещая их по горизонтали слева-направо
- Append - соединяет исходные диапазоны, учитывая имена столбцов в шапке каждого диапазона, т.е. правильно собирая друг-под-друга столбцы с одинаковыми наименованиями в строке заголовка
- Extract - извлекает из диапазона N строк и/или столбцов от начала или конца
- Matches - выводит списком все совпадения в двух указанных диапазонах
- Mismatches - выводит списком все отличия в двух заданных диапазонах (все элементы первого диапазона, которых нет во втором)
- ConvertToRow - преобразует двумерную таблицу в одномерный массив-строку
- ConvertToColumn - преобразует двумерную таблицу в одномерный массив-столбец
- CloneRows - дублирует исходный диапазон N раз, размещая копии друг-под-другом сверху-вниз
- CloneCols - дублирует исходный диапазон N раз, размещая копии рядом слева-направо
- SelectRows - извлекает из таблицы строки с заданными номерами
- SelectCols - извлекает из таблицы столбцы с заданными номерами
- ReverseRows - размещает строки в таблицы в обратном порядке
- ReverseCols - размещает столбцы в таблице в обратном порядке
- RemoveBlankRows - удаляет из исходного диапазона все полностью пустые строки
- Pivoted - сворачивает исходную плоскую таблицу в сводную
- Unpivot - выполняет отмену свёртывания (нормализацию) исходной двумерной таблицы в плоскую
Подробную информацию по каждой функции, включая синтаксис и примеры использования - см. в приложенном к PLEX файле справки и на сайте в разделе
В
- Специальная вставка из буфера только значений
- Специальная вставка из буфера только значений с сохранением ширины столбцов
- Подгрузить в текущую книгу пользовательские стили PLEX
- Выгрузить из текущей книги пользовательские стили PLEX
- Полностью очистить диапазон (и содержимое и форматы)
- Удалить все строки / столбцы на листе кроме выделенных
- Добавить к выделенным ячейкам функцию перехвата ошибок ЕСЛИОШИБКА (IFERROR)
- Перевести число в текущей ячейке в сумму прописью
В дополнение к уже имеющимся 9 странам, в инструмент
Также были добавлены пользовательские функции для решения аналогичной задачи напрямую в ячейках листа:
- =CBA(Дата;Валюта) - вставка курсов ЦБ Армении
- =MNB(Дата;Валюта) - вставка курсов Нац.Банка Венгрии
У всех функций стандартный синтаксис, совпадающий с уже имеющимися функциями вставки курсов валют, состоящий из 2 аргументов:
- Ячейка с датой, для которой нужно получить курс (если не указана, то берётся текущая дата).
- Трёхбуквенный код валюты в кавычках ("USD", "EUR" и т.д.) или ссылка на ячейку, где он лежит (уже без кавычек). Если этот аргумент опущен, то по умолчанию берётся доллар США.
- В инструмент Сравнить добавлена возможность задавать номер ключевого столбца индивидуально для каждого диапазона и возможность вывода результатов на новый лист.
- В окне инструмента Обновить все запросы в папке в разделе Power Query добавлена галочка Включая вложенные папки, позволяющая обновлять запросы не только в текущей, но и во всех вложенных в неё папках (любого уровня вложенности).
- В Мини-калькулятор добавлена возможность умножать на ноль.
- В Календарь добавлены даты до 2030 года.
- В окне Текст добавлена возможность выделять цветом ячейки, содержимое которых изменилось после применённых в этом окне преобразований (например, очистки текста от лишних пробелов и т.п.)
- Исправлена ошибка, возникающая при попытке назначить или очистить сочетание клавиш в инструменте Горячие клавиши.
- Исправлена работа Прицела на пустых листах.
- Исправлено некорректное определение последней рабочей ячейки листа в некоторых случаях в инструменте Очистка книги.
- Исправлено некорректное склонение по падежам некоторых имен в функции FIO, а также улучшено распознавание имён и обновлена их коллекция.
- Исправлены ошибки и неточности перевода некоторых команд в английской версии PLEX.
- Исправлена ошибка, возникающая при попытке удалить символы в инструменте Текст.
- Исправлена ошибка, возникающая в инструменте Сборка данных с листов при выборе опции Игнорировать скрытые листы.
Новая статья : Мультистраничный веб-запрос в Power Query
![](/upload/blog/avatar/500/nikolay-pavlov3.jpg)
- Создать сначала одиночный веб-запрос к любой странице из интересующего списка
- Создать параметр для года и внедрить его в запрос
- Преобразовать запрос в функцию (параметр станет её аргументом)
- Создать список интересующих нас лет (2010 - 2021) и вызвать созданную функцию, подставив ей список в качестве аргумента
- Объединить полученные результаты в единую таблицу за все годы
Новая статья: Год-к-году (YoY) в сводной таблице
![](/upload/blog/avatar/500/nikolay-pavlov3.jpg)
Новая статья: Хитрости сортировки в Power BI
![](/upload/blog/avatar/500/nikolay-pavlov3.jpg)
Новая статья: Основы работы в Microsoft Power BI
![](/upload/blog/avatar/500/nikolay-pavlov3.jpg)
В общем, ловите новый большой видеоурок по основам работы в Microsoft Power BI. Если вы с ним уже работаете, то никаких Америк я вам не открою, скорее всего. Но если раньше вы с ним не сталкивались или до сих пор смутно представляете, что это за штука, то - добро пожаловать. Экспертом по аналитике за полчаса я вас, конечно, не сделаю, но все главные принципы и этапы работы мы с вами разберём:
- Что такое Power BI, из чего он состоит, и зачем он нужен
- Как загрузить туда и исходные данные и привести их в приличный вид
- Как и зачем связывать таблицы между собой
- Как создавать вычисляемые столбцы и меры на встроенном в Power BI языке DAX
- Как добавить визуализации на ваш дашборд и опубликовать его потом в облако
Новое видео: Диаграмма Парето в Excel
![](/upload/blog/avatar/500/nikolay-pavlov3.jpg)
Обновил старую статью про
Новая статья: 5 примеров использования новой функции IMAGE в Excel
![](/upload/blog/avatar/500/nikolay-pavlov3.jpg)
Уже несколько лет в Google Sheets существует
У Excel же ничего подобного долго не было. И вот, наконец, осенью 2022 года Microsoft начала среди добровольцев-тестировщиков из программы Office Insider обкатку своего аналога - новой функции ИЗОБРАЖЕНИЕ (IMAGE), также позволяющей вставлять по веб-ссылке картинки из интернета прямо в ячейки листа Microsoft Excel. В начале 2023 года эту функцию уже стали потихонечку разливать с обновлениями подписчикам Office 365 и недавно она, наконец, добралась и до меня. А значит я могу вам о ней рассказать на нескольких практических примерах, а именно:
- Создание штрих и QR-кодов
- Вставка логотипов компаний по их названию
- Нестандартные диаграммы
- Вставка скриншотов веб-страниц
- Вставка географических карт
Новая статья: Искуcственный интеллект ChatGTP при работе в Excel
![](/upload/blog/avatar/500/nikolay-pavlov3.jpg)
Думаю, про ChatGPT вам уже, как минимум, слышали, а может даже и попробовали использовать. Вопрос в том, насколько полезен этот ИИ может быть для простого пользователя Excel.
Пробуем переложить на ChatGPT создание формул и макросов на Visual Basic и смотрим, что из этого получится:
Новая статья: Поиск отличий в двух текстовых строках
![](/upload/blog/avatar/500/nikolay-pavlov3.jpg)
Если вы когда-нибудь использовали в своих вычислениях в Excel логические функции (типа ЕСЛИ) или функции подстановки (типа ВПР), то, вполне возможно, уже сталкивались с ситуацией, когда две визуально неотличимых ячейки Microsoft Excel почему-то считает неодинаковыми, что автоматически приводит к неработоспособности функций, ломает фильтрацию, сводные таблицы и т.д. Причин для такого может быть много:
- лишние пробелы
- использование английских букв похожих на русские и наоборот (русская "эс" и английская "си", например)
- использование цифр вместо букв (ноль вместо заглавной "О")
- разные версии тире (короткое, длинное, среднее)
- неразрывный пробел вместо обычного
- невидимые символы переноса строки (Alt+Enter)
- ... и т.д.
Вопрос в том, где именно эти отличия находятся и как их быстро найти в исходных данных?
Самое простое - это сделать за пару минут прямо в Excel своими руками универсальный сравниватель, который будет наглядно показывать в чем именно не совпадают две любые заданные текстовые строки:
Новая статья: Диаграмма выполнения плана в Excel
![](/upload/blog/avatar/500/nikolay-pavlov3.jpg)
![](https://www.planetaexcel.ru/upload/medialibrary/754/qg3z17pt2h0qi2r7pv96iqo40b974p4j/plan-fact-chart1.png)
Делается достаточно легко и почти не требует подготовительных действий с таблицей.
Обновление надстройки PLEX - версия 2022.3
![](/upload/blog/avatar/500/nikolay-pavlov3.jpg)
Новые страны в Курсах валют
![](https://www.planetaexcel.ru/upload/medialibrary/c8b/2zrizka48xm0r1xrwglxb12cmgz1i53j/plex-rates-new-banks.png)
Теперь помимо уже имеющихся России, Украины, Беларуси и Казахстана, можно вставить курс любой валюты на любую дату для:
- Грузии
- Азербайджана
- Таджикистана
- Туркменистана
- Молдовы
Для всех банков вставка производится с делением на соответствующую размерность, т.е. если, например, на сайте указан курс за 100 иен, то после вставки он будет поделен на 100.
Также все макросы импорта курсов валют переписаны на использование XML, RSS и JSON-потоков от банков, что ускоряет процесс загрузки и делает импорт независимым от будущих изменений дизайна сайтов банков.
Новые функции вставки курсовОдновременно с добавлением новых стран (нац.банков) в диалоговом окне Курсы валют, были добавлены пользовательские функции для решения аналогичной задачи напрямую в ячейках листа:
=NBM(Дата;Валюта) - вставка курсов Нац.Банка Молдовы
=NBTJ(Дата;Валюта) - вставка курсов Нац.Банка Таджикистана
=NBTN(Дата;Валюта) - вставка курсов Нац.Банка Туркменистана
=NBAZ(Дата;Валюта) - вставка курсов ЦБ Азербайджана
=NBG(Дата;Валюта) - вставка курсов Нац.Банка Грузии
У всех функций стандартный синтаксис, совпадающий с уже имеющимися функциями вставки курсов валют, состоящий из 2 аргументов:
- Ячейка с датой, для которой нужно получить курс (если не указана, то берётся текущая дата).
- Трёхбуквенный код валюты в кавычках ("USD", "EUR" и т.д.) или ссылка на ячейку, где он лежит (уже без кавычек). Если этот аргумент опущен, то по умолчанию берётся доллар США.
Как всегда, попутно исправлено несколько ошибок и выполнена незаметная снаружи, но важная внутренняя оптимизация, а именно:
- Исправлена ошибка, возникающая при вставке курсов при нестандартных числовых разделителях и нероссийских региональных настройках.
- Исправлена ошибка, возникающая при удалении N-го по счету слова в инструменте Текст.
- Исправлена ошибка, возникающая в инструменте Свертка при добавлении в область значений числовых полей.
- Исправлена ошибка с регистром начальных символов, возникающая при транслитерации в инструменте Текст.
Как обычно, скачать последнюю версию можно из
Не забудьте после скачивания
Новая статья: Решение системы уравнений в Excel
![](/upload/blog/avatar/500/nikolay-pavlov3.jpg)
![С улыбкой :)](/upload/main/smiles/3/bx_smile_smile.png)
![](https://www.planetaexcel.ru/upload/medialibrary/98a/qciv4wj81exs173000g003d55hf0ayp0/equation-system3.png)
Новая статья: РегистроНЕчувствительность в Power Query
![](/upload/blog/avatar/500/nikolay-pavlov3.jpg)
Конечно, рано или поздно, к этому привыкаешь и начинаешь относиться как к данности и учитывать в работе. Так, например, многие пользователи перед фильтрацией, чтобы она была регистроНечувствительной, сначала делают дубликат столбца, в котором затем преобразуют весь текст к одному регистру и только потом фильтруют. Вполне себе способ.
На самом деле, решить эту проблему можно гораздо изящнее, если использовать встроенную в языке М в Power Query функцию с громоздким названием Comparer.OrdinalIgnoreCase.