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

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


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

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

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

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

Рад сообщить, что выпустил обновление своей надстройки 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.
  
• Исправлена ошибка, возникающая при попытке удалить символы в инструменте Текст.
  
• Исправлена ошибка, возникающая в инструменте Сборка данных с листов при выборе опции Игнорировать скрытые листы.
  

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


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

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

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

• Что такое Power BI, из чего он состоит, и зачем он нужен
  
• Как загрузить туда и исходные данные и привести их в приличный вид
  
• Как и зачем связывать таблицы между собой
  
• Как создавать вычисляемые столбцы и меры на встроенном в Power BI языке DAX
  
• Как добавить визуализации на ваш дашборд и опубликовать его потом в облако
  

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

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

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

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

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

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

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

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

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

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

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

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


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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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