• Архив

    «   Октябрь 2024   »
    Пн Вт Ср Чт Пт Сб Вс
      1 2 3 4 5 6
    7 8 9 10 11 12 13
    14 15 16 17 18 19 20
    21 22 23 24 25 26 27
    28 29 30 31      

Новая статья: Разделение многоуровневого списка по столбцам

Три способа (формулы, Power Query и макрос) чтобы разложить многоуровневый список с вложенной нумерацией по отдельным столбцам:


Читать статью полностью

Новая статья: Правильный шрифт в строке формул

Уже страшно вспомнить сколько лет пользователи просят Microsoft сделать в строке формул Excel правильный шрифт. В ячейках на листе данные могут отображаться в любом самом безумном дизайне, который только захочет пользователь (хоть Comic Sans'ом пишите), но строка формул - другое дело. Когда пишешь или редактируешь длинную, сложную формулу с кучей вложенных друг в друга функций, то удобный шрифт очень важен.

Разбираемся, каким должен быть правильный шрифт и как его прикрутить к строке формул (но не к ячейкам листа!) в Microsoft Excel.


Читать статью полностью

Новая статья: Календарный дашборд на Power Pivot

Создаём календарный дашборд в Excel с помощью Power Pivot для наглядного отображения любых событий (например, заказов) в календаре:



Читать статью полностью

Обновление статьи : Количество уникальных значений

Разбираем пять способов получить количество уникальных значений в исходном диапазоне данных:
  • формулой массива
  • макросом на VBA
  • удалением дубликатов
  • простой сводной
  • сводной по Модели Данных


Читать статью полностью

Новая статья : Сравнение любых произвольных дат и/или товаров в Power BI

При создании отчётов в Microsoft Power BI часто требуется сравнить текущий год (или месяц) с предыдущим, а какой-то выбранный товар - с его соседями из той же категории. С этим проблем нет - в Power BI есть куча встроенных функций на этот счёт.

Но что делать, если хочется сравнивать произвольно выбранные объекты? Т.е. любой выбранный год - с любым другим произвольно выбранным, а не обязательно предыдущим? Или выбранный товар нужно сравнить с каким-то другим, заданным? А может и то, и другое сразу, т.е. хочется выбирать интересные мне любые 2 года и сравнивать любые товары в них?


Читать статью полностью

Обновление надстройки PLEX - версия 2023.1

Рад сообщить, что выпустил обновление своей надстройки PLEX для Microsoft Excel - версию 2023.1.
Из нового:

Фиксация содержимого диалоговых окон

В большинство диалоговых окон PLEX добавлена кнопка с символом замка, чтобы зафиксировать введённое в это окно содержимое и его параметры (содержимое полей ввода, положение переключателей, включенные флажки и т.д.) в течение текущего сеанса работы с Microsoft Excel. Данная функция может быть весьма полезна тем, кто многократно запускает какие-то инструменты PLEX с одинаковыми настройками и не хочет каждый раз задавать их вручную.


Для отмены фиксации нужно открыть соответствующее окно, отжать кнопку с замком и нажать на кнопку Отмена.

Выбор "умных" таблиц вместо диапазонов

В некоторых диалоговых окнах в полях ввода исходных данных добавлена стрелка слева, которая нужна для переключения между двумя режимами ввода – ручного указания диапазона (как обычно) или выбора одной из имеющихся в книге «умных» динамических таблиц из выпадающего списка:


Если вы используете "умные" таблицы, то это сильно упростит вам жизнь при работе в PLEX.

Новые функции для работы с датами

Добавлены новые пользовательские функции для работы с датами:

  • Quarter - возвращает квартал для указанной даты в заданном формате (только номер или "1 кв" или "Q-1" и т.д.)
  • DateInterval - возвращает разницу между двумя исходными датами в формате "Х лет Y мес Z дн"
  • FirstDate и LastDate - возвращают самую раннюю и позднюю даты из указанного диапазона (при этом игнорируют в нём всё кроме дат)

17 новых функций динамических массивов

Если у вас 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
  • Выгрузить из текущей книги пользовательские стили PLEX
  • Полностью очистить диапазон (и содержимое и форматы)
  • Удалить все строки / столбцы на листе кроме выделенных
  • Добавить к выделенным ячейкам функцию перехвата ошибок ЕСЛИОШИБКА (IFERROR)
  • Перевести число в текущей ячейке в сумму прописью

Курсы валют Армении и Венгрии

В дополнение к уже имеющимся 9 странам, в инструмент Курсы валют добавлена возможность получить курс любой валюты на любую дату с сайтов ЦБ Армении и НБ Венгрии:


Также были добавлены пользовательские функции для решения аналогичной задачи напрямую в ячейках листа:

  • =CBA(Дата;Валюта)  - вставка курсов ЦБ Армении
  • =MNB(Дата;Валюта)  - вставка курсов Нац.Банка Венгрии

У всех функций стандартный синтаксис, совпадающий с уже имеющимися функциями вставки курсов валют, состоящий из 2 аргументов:

  • Ячейка с датой, для которой нужно получить курс (если не указана, то берётся текущая дата).
  • Трёхбуквенный код валюты в кавычках ("USD", "EUR" и т.д.) или ссылка на ячейку, где он лежит (уже без кавычек). Если этот аргумент опущен, то по умолчанию берётся доллар США.
Прочие улучшения
  • В инструмент Сравнить добавлена возможность задавать номер ключевого столбца индивидуально для каждого диапазона и возможность вывода результатов на новый лист.
  • В окне инструмента Обновить все запросы в папке в разделе Power Query добавлена галочка Включая вложенные папки, позволяющая обновлять запросы не только в текущей, но и во всех вложенных в неё папках (любого уровня вложенности).
  • В Мини-калькулятор добавлена возможность умножать на ноль.
  • В Календарь добавлены даты до 2030 года.
  • В окне Текст добавлена возможность выделять цветом ячейки, содержимое которых изменилось после применённых в этом окне преобразований (например, очистки текста от лишних пробелов и т.п.)
Исправления ошибок
  • Исправлена ошибка, возникающая при попытке назначить или очистить сочетание клавиш в инструменте Горячие клавиши.
  • Исправлена работа Прицела на пустых листах.
  • Исправлено некорректное определение последней рабочей ячейки листа в некоторых случаях в инструменте Очистка книги.
  • Исправлено некорректное склонение по падежам некоторых имен в функции FIO, а также улучшено распознавание имён и обновлена их коллекция.
  • Исправлены ошибки и неточности перевода некоторых команд в английской версии PLEX.
  • Исправлена ошибка, возникающая при попытке удалить символы в инструменте Текст.
  • Исправлена ошибка, возникающая в инструменте Сборка данных с листов при выборе опции Игнорировать скрытые листы.

Новая статья : Мультистраничный веб-запрос в Power Query

Мультистраничный веб-запрос в Power Query - это подход, который позволяет загрузить данные не с одной, а сразу с нескольких веб-страниц, объединив их в единую таблицу. Например, загрузить результаты чемпионатов по гольфу из википедии за несколько лет (где каждый год - отдельная страница). Чтобы провернуть такую штуку, нам придётся пройти следующие этапы:

  1. Создать сначала одиночный веб-запрос к любой странице из интересующего списка
  2. Создать параметр для года и внедрить его в запрос
  3. Преобразовать запрос в функцию (параметр станет её аргументом)
  4. Создать список интересующих нас лет (2010 - 2021) и вызвать созданную функцию, подставив ей список в качестве аргумента
  5. Объединить полученные результаты в единую таблицу за все годы


Читать статью полностью

Новая статья: Год-к-году (YoY) в сводной таблице

3 способа сравнить и наглядно вывести год-к-году в сводной таблице: формулами, дополнительным полем или с помощью мер на DAX в Power Pivot.


Читать статью полностью

Новая статья: Хитрости сортировки в Power BI

Разбираем нюансы и хитрости сортировки данных в таблицах и диаграммах Microsoft Power BI: простые и многоуровневые сортировки, неявную сортировку, сортировку месяцев и пользовательскую сортировку в нашей собственной логической последовательности.


Читать статью полностью

Новая статья: Основы работы в Microsoft Power BI

Если отбросить новомодный ChatGPT, то на втором месте по по количеству хайпа в моем личном рейтинге будет Power BI с его нечеловеческой красоты интерактивными дашбордами. Причем за последние пару лет количество людей интересующихся этой темой (в том числе среди пользователей Excel, которых я часто вижу) - выросло кратно. С одной стороны, это хорошо, ибо по мощи, гибкости, "всеядности" и красоте визуализаций у Power BI сейчас конкурентов практически нет. С другой стороны, у многих людей я до сих пор встречаю заблуждение, что Power BI - это такой навороченный Power Point (может дело в похожем названии?).

В общем, ловите новый большой видеоурок по основам работы в Microsoft Power BI. Если вы с ним уже работаете, то никаких Америк я вам не открою, скорее всего. Но если раньше вы с ним не сталкивались или до сих пор смутно представляете, что это за штука, то - добро пожаловать. Экспертом по аналитике за полчаса я вас, конечно, не сделаю, но все главные принципы и этапы работы мы с вами разберём:
  • Что такое Power BI, из чего он состоит, и зачем он нужен
  • Как загрузить туда и исходные данные и привести их в приличный вид
  • Как и зачем связывать таблицы между собой
  • Как создавать вычисляемые столбцы и меры на встроенном в Power BI языке DAX
  • Как добавить визуализации на ваш дашборд и опубликовать его потом в облако

Читать статью полностью

Новое видео: Диаграмма Парето в Excel

Обновил старую статью про создание диаграммы Парето в Microsoft Excel - добавил пару новых способов и видеоурок:


Читать статью полностью

Новая статья: 5 примеров использования новой функции IMAGE в Excel

Уже несколько лет в Google Sheets существует функция IMAGE, позволяющая вставлять в ячейки листа картинки по ссылке из интернета. Что, впрочем, вполне естественно, поскольку Google-таблицы изначально заточены под работу онлайн - им сам бог велел такую возможность использовать.

У Excel же ничего подобного долго не было. И вот, наконец, осенью 2022 года Microsoft начала среди добровольцев-тестировщиков из программы Office Insider обкатку своего аналога -  новой функции ИЗОБРАЖЕНИЕ (IMAGE), также позволяющей вставлять по веб-ссылке картинки из интернета прямо в ячейки листа Microsoft Excel. В начале 2023 года эту функцию уже стали потихонечку разливать с обновлениями подписчикам Office 365 и недавно она, наконец, добралась и до меня. А значит я могу вам о ней рассказать на нескольких практических примерах, а именно:

  • Создание штрих и QR-кодов
  • Вставка логотипов компаний по их названию
  • Нестандартные диаграммы
  • Вставка скриншотов веб-страниц
  • Вставка географических карт


Читать статью полностью

Новая статья: Искуcственный интеллект ChatGTP при работе в Excel

Думаю, про ChatGPT вам уже, как минимум, слышали, а может даже и попробовали использовать. Вопрос в том, насколько полезен этот ИИ может быть для простого пользователя Excel.

Пробуем переложить на ChatGPT создание формул и макросов на Visual Basic и смотрим, что из этого получится:


Читать статью полностью

Новая статья: Поиск отличий в двух текстовых строках

Если вы когда-нибудь использовали в своих вычислениях в Excel логические функции (типа ЕСЛИ) или функции подстановки (типа ВПР), то, вполне возможно, уже сталкивались с ситуацией, когда две визуально неотличимых ячейки Microsoft Excel почему-то считает неодинаковыми, что автоматически приводит к неработоспособности функций, ломает фильтрацию, сводные таблицы и т.д. Причин для такого может быть много:

  • лишние пробелы
  • использование английских букв похожих на русские и наоборот (русская "эс" и английская "си", например)
  • использование цифр вместо букв (ноль вместо заглавной "О")
  • разные версии тире (короткое, длинное, среднее)
  • неразрывный пробел вместо обычного
  • невидимые символы переноса строки (Alt+Enter)
  • ... и т.д.

Вопрос в том, где именно эти отличия находятся и как их быстро найти в исходных данных?

Самое простое - это сделать за пару минут прямо в Excel своими руками универсальный сравниватель, который будет наглядно показывать в чем именно не совпадают две любые заданные текстовые строки:


Читать статью полностью

Новая статья: Диаграмма выполнения плана в Excel

Уж сколько было копий сломано на тему визуализации план-факта, но это вечная история и каждый решает её по-своему. Предлагаю рассмотреть ещё один вариант в вашу копилку:


Делается достаточно легко и почти не требует подготовительных действий с таблицей.

Читать статью + видеоурок

Обновление надстройки PLEX - версия 2022.3

Выпустил досрочно-внеплановое обновление моей надстройки PLEX для Microsoft Excel - третье в этом году. Обновления и улучшения коснулись, в основном, импорта курсов валют.

Новые страны в Курсах валют


Теперь помимо уже имеющихся России, Украины, Беларуси и Казахстана, можно вставить курс любой валюты на любую дату для:

  • Грузии
  • Азербайджана
  • Таджикистана
  • Туркменистана
  • Молдовы

Для всех банков вставка производится с делением на соответствующую размерность, т.е. если, например, на сайте указан курс за 100 иен, то после вставки он будет поделен на 100.

Также все макросы импорта курсов валют переписаны на использование XML, RSS и JSON-потоков от банков, что ускоряет процесс загрузки и делает импорт независимым от будущих изменений дизайна сайтов банков.

Новые функции вставки курсов

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

=NBM(Дата;Валюта)  - вставка курсов Нац.Банка Молдовы

=NBTJ(Дата;Валюта)  - вставка курсов Нац.Банка Таджикистана

=NBTN(Дата;Валюта)  - вставка курсов Нац.Банка Туркменистана

=NBAZ(Дата;Валюта) - вставка курсов ЦБ Азербайджана

=NBG(Дата;Валюта)  - вставка курсов Нац.Банка Грузии

У всех функций стандартный синтаксис, совпадающий с уже имеющимися функциями вставки курсов валют, состоящий из 2 аргументов:

  • Ячейка с датой, для которой нужно получить курс (если не указана, то берётся текущая дата).
  • Трёхбуквенный код валюты в кавычках ("USD", "EUR" и т.д.) или ссылка на ячейку, где он лежит (уже без кавычек). Если этот аргумент опущен, то по умолчанию берётся доллар США.

Исправления ошибок и улучшения

Как всегда, попутно исправлено несколько ошибок и выполнена незаметная снаружи, но важная внутренняя оптимизация, а именно:

  • Исправлена ошибка, возникающая при вставке курсов при нестандартных числовых разделителях и нероссийских региональных настройках.
  • Исправлена ошибка, возникающая при удалении N-го по счету слова в инструменте Текст.
  • Исправлена ошибка, возникающая в инструменте Свертка при добавлении в область значений числовых полей.
  • Исправлена ошибка с регистром начальных символов, возникающая при транслитерации в инструменте Текст.

Как обычно, скачать последнюю версию можно из раздела PLEX.

Не забудьте после скачивания разблокировать файл надстройки.

Новая статья: Решение системы уравнений в Excel

Всем школьникам и студентам (и не только) посвящается :) Как решить систему уравнений в Microsoft Excel. Разбираем два принципиально разных подхода - с помощью обратной матрицы Крамера функциями МОБР и МУМНОЖ (для систем линейных уравнений) и подбором в надстройке Поиск решения (Solver).(для любых, в т.ч. и нелинейных).




Читать статью полностью

Новая статья: РегистроНЕчувствительность в Power Query

Необходимость учитывать регистр (регистрочувствительность) - одно из первых заметных принципиальных отличий, с которыми сталкиваются те, кто начинают работать в Power Query. В отличие от Excel, который прописные и строчные буквы в подавляющем большинстве случаев не различает, Power Query в этом вопросе строг. При любых операциях с данными (фильтрации, сортировке, удалении дубликатов, в исходном М-коде запросов и т.д.) Query воспринимает большие и маленькие буквы как совершенно разные.

Конечно, рано или поздно, к этому привыкаешь и начинаешь относиться как к данности и учитывать в работе. Так, например, многие пользователи перед фильтрацией, чтобы она была регистроНечувствительной, сначала делают дубликат столбца, в котором затем преобразуют весь текст к одному регистру и только потом фильтруют. Вполне себе способ.

На самом деле, решить эту проблему можно гораздо изящнее, если использовать встроенную в языке М в Power Query функцию с громоздким названием Comparer.OrdinalIgnoreCase.



Читать статью полностью
Страницы: 1 | 2 | 3 | 4 | 5 | ... | 30 | След.
Наверх