Имеем список объектов (например, товаров) с пометкой, к какому набору  (корзине) каждый из них относится. Необходимо разложить объекты по своим  наборам, сформировав таблицу как на рисунке справа:

 

Похожие задачи встречаются на практике весьма часто - в случаях, когда приходится распределять те или иные ресурсы:

• сотрудников по командам
  
• водителей по маршрутам
  
• клиентов по менеджерам
  
• товары по корзинам и т.д.
  

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

Чтобы у вас был выбор, давайте разберем несколько способов решения этой задачи.

Обновил старую статью о том, как создать в Microsoft Excel выпадающий список с показом выбранного изображения.
В новых версиях Excel описанный там подход уже не работает и нужен другой метод:



Само-собой, к статье приложен и видеоурок:

Читать статью и смотреть видео

Разбираем два способа построить вафельную диаграмму (waffle chart) в Excel - с помощью условного форматирования и отрихтованной линейчатой диаграммы (bar chart), чтобы получить вот такую красоту:



Отлично подойдет для отображения прогресса по любым задачам.

Читать статью полностью и смотреть видео
 

Ура! Сделал и выложил новый дистанционный онлайн-курс "Функции Excel - самое нужное".

На сегодняшний день в Microsoft Excel 477 встроенных функций. Это число немного пугает

На самом деле, для решения 90% задач, возникающих при работе в Excel,  достаточно знать одну десятую от этого количества. Этот курс как раз и  посвящен подробному разбору этих 50 ключевых функций, без которых сейчас  невозможно представить ни один современный проект в Microsoft Excel.

Спектр изучения - от СУММ до ВПР-ИНДЕКС-ПОИСКПОЗ, т.е. те самые 20%, которые решают 80% проблем.

В этом курсе вы научитесь:

• Быстро и легко вводить, редактировать и комбинировать между собой любые функции Excel разными способами.
  
• Склеивать, резать и зачищать текст с помощью удобных текстовых функций Excel.
  
• Выполнять любые операции с датами, считать сроки, длительности, стаж или возраст в календарных или рабочих днях
  с помощью функций дат и времени.
  
• Задавать проверки и сложные условия для реализации хитрой логики в ваших расчетах.
  
• Использовать мощные функции поиска и подстановки (ВПР, ИНДЕКС, ПОИСКПОЗ...) для связывания таблиц.
  

Курс состоит из 6 глав, 22 уроков и обучающих видео общей длительностью  более 3 часов. К каждому уроку приложен текстовый конспект, файл-пример и  ДЗ для самостоятельной проработки всех навыков.


Посмотреть страницу курса

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


К сожалению, в MS Excel нет простых встроенных инструментов для решения этой проблемы, поэтому придется привлекать относительно сложные формулы массива (но Ctrl+Shift +Enter жать не придется).

В следующей статье разберём, как реализовать подобное с помощью макросов и хитрого запроса Power Query.

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

Пару лет назад я уже делал большую статью с видео о том, как добавить в Microsoft Excel поддержку регулярных выражений (RegExp). Это здорово развязывает нам руки при анализе и парсинге текста и предоставляет в наше распоряжение мощный и гибкий функционал, рядом с которым стандартные текстовые функции Excel и рядом не стояли.
Открытым, однако, остался вопрос - можно ли добавить поддержку регулярных выражений в Power Query? Справка и тех.поддержка Microsoft отвечают на этот вопрос сугубо отрицательно, но, на самом деле, есть способ обойти это ограничение...



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

Разбор классической проблемы с функцией ВПР (VLOOKUP), которая отказывается находить числовые значения, если они отформатированы как текст:



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

В текущей ситуации острой нехватки позитивных новостей, эта новость - приятна вдвойне.

Коллеги из издательства "ДМК Пресс" сообщили, что ушла в печать и уже доступна для заказа первая на русском языке книга по Microsoft Power BI и Power Pivot - "Анализ данных при помощи Microsoft Power BI и Power Pivot для Excel":



Это перевод известной в узких кругах книжки "Analyzing Data with Power BI and Power Pivot in Excel" двух самых достойных экспертов в этой теме: Альберто Феррари и Марко Руссо, авторов проекта www.sqlbi.com.

Я эту книгу, помнится, читал еще на английском пару лет назад, купив её на Amazon (а теперь с удовольствием перечитаю на русском еще разок) - однозначный мастхэв для любого аналитика или просто продвинутого пользователя Excel. Большинство описанных в книге инструментов прекрасно работают не только в модном нынче Power BI, но и в классическом Power Pivot.

Также коллеги из "ДМК Пресс" попросили меня написать предисловие к русскому изданию (что я с удовольствием и сделал) и поделились промо-кодом на 10% скидку DMKPRESS_2020, которым я, в свою очередь, делюсь с вами.

Так что с чистой совестью рекомендую всем, кто в теме. Лучший способ провести это смутное время с пользой.

Страница книги на сайте издательства

P.S. Уточнение для озабоченных - издательство не платило мне ни копейки за рекламу. Все вышесказанное - сугубо мое личное мнение и рекомендация.

Благодаря самоизоляции, закончил и выложил очередное обновление моей надстройки PLEX для Microsoft Excel.
Список новых "плюшек" следующий:

Выделятор
Встроенные возможности Microsoft Excel по выделению ячеек по заданным условиям (Главная – Найти и выделить – Выделить группу ячеек) весьма скромны. Начиная с этой версии, в надстройке PLEX в группе Диапазоны появился новый инструмент Выделятор с гораздо более мощными инструментами в этом плане.  
Идея его работы проста – выделять ячейки по определенным условиям:



Список вариантов по критериям внушительный:

• Числа: больше, меньше, (не) равно, (не) между.
• Текст: (не) равен, (не) содержит, (не) начинается с, (не) заканчивается на, соответствует шаблону (можно использовать символы подстановки *?#), (не) совпадает с любой ячейкой из указанного списка, (не) содержит любое слово из указанного диапазона, содержит любой символ из списка, содержит цифры, содержит кириллицу (А-Я), содержит латиницу (A-Z), содержит лишние пробелы, содержит непечатаемые знаки.
• Даты: равна, до, после, (не) между, попадает на рабочий день, попадает на выходной, попадает на определённый день недели, попадает в заданный год, квартал или месяц.
• Формат: содержит полужирный текст, курсив, подчеркивание, зачёркнутый текст, есть любая заливка, любой цвет шрифта кроме чёрного, заливка как в ячейке-образце, цвет шрифта как в ячейке-образце.
• Другое: (не) пустые ячейки, ячейки с формулами, с константами, со ссылками на другие листы/книги, с ошибками, (не) защищенные, с заданным уровнем группировки, с гиперссылками.
  

При нахождении ячейки удовлетворяющей условию, можно выделить только её или же всю строку/столбец, где она находится.

Склонение ФИО по падежам

По многочисленным просьбам добавлена пользовательская функция FIO, которая умеет:

• Выстраивать ФИО в правильном порядке, если в исходных данных они стоят неправильно.
• Склонять ФИО в родительном и дательном падежах.
• Выводить, при необходимости, сокращенную форму (Фамилия И.О.)
  

Синтаксис функции:
=FIO(NameAsText; NameCase; ShortForm)
где:

• NameAsText – ячейка с исходным ФИО (полным или без отчества)
• NameCase – символьный код падежа ("Д" или "D" – дательный; "Р" или "R" – родительный). По умолчанию – без преобразования.
• ShortForm – логическое значение, обозначающее нужно ли (1 или ИСТИНА) или нет (0 или ЛОЖЬ) преобразовывать ФИО в сокращенную форму (Фамилия И.О.). По умолчанию, принимается за 0 (без преобразования).
  

В самом простом варианте эту функцию можно использовать для выстраивания ФИО в правильном порядке:

Со вторым аргументом можно преобразовать ФИО в требуемый падеж:

Третий (необязательный) логический аргумент позволит выводить ФИО в нужном падеже и, дополнительно, в сокращенной форме:

Безусловно, найдутся зубодробительные ФИО, где эта функция сработает некорректно, ибо русский язык велик и могуч. Однако, с 99,9% процентами типичных российских имен все работает вполне успешно (тестировал на 5000+ ФИО реальных людей).

Определение пола по имени

Добавлена функция с романтическим названием GetSex для определения пола человека по имени (полному или частичному ФИО).  Возвращает 1 (женский), -1 (мужской) или 0 (не определен):



Опять же, предусмотреть все возможные несуразные имена, которые родители умудряются давать своим детям, невозможно в принципе. Но на большинстве наших с вами соотечественников (в том числе СНГ) работает вполне надёжно.

Конвертация условного форматирования в статическое


Условное форматирование – красивая, но весьма ресурсоёмкая штука. Чтобы избежать торможения на больших таблицах, можно преобразовать условное форматирование в статическое. Внешний вид ячейки сохранится, но не будет больше меняться при изменении данных (и, соответственно, тормозить).
Обратите внимание на 3 момента:

• Обратное преобразование невозможно.
• Такие виды условного форматирования, как гистограммы или значки нельзя преобразовать в статику.
• Из-за ограничений самого Excel эта функция работает только начиная с 2010-й версии Microsoft Excel.
  

Исправление УФ-ада

Если у вас есть таблица с несколькими правилами условного форматирования и вы, работая с этой таблицей:

• удаляете в ней строки
• меняете строки местами
• вырезаете-копируете фрагменты таблицы
  

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


Пользовательские стили

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


Как выглядят эти стили на практике можно посмотреть в том же файле styles.xlsx, просто открыв его:


При необходимости вы можете сами в будущем добавлять в этот файл свои стили через команду Главная – Стили – Создать стиль ячейки.

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

• Исправлена ошибка в инструменте Курс валют, связанная с редизайном сайта ЦБ РФ.
• В инструменте Текст разделено удаление букв, чисел, знаков препинания и пробелов для большего удобства и наглядности.
• Большинство веб-запросов (курсы валют, ставки, онлайн-справка и т.д.) переведены на безопасный протокол HTTPS.
• Исправлена ошибка в функциях SumByCellColor, SumByFontColor, CountByCellColor, CountByFontColor, возникающая при некоторых экзотических оттенках используемых цветов.
• Добавлена возможность выполнять вычисления с дробными числами в мини-калькуляторе.
• Исправлено более 30 мелких ошибок, улучшена стабильность и быстродействие.
  

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

Если в ваших таблицах всего лишь несколько десятков строк, то, скорее всего, эта статья не будет для вас актуальной. На таких небольших объемах данных любой способ будет работать достаточно шустро, чтобы вы этого не замечали. Если же число строк в ваших списках измеряется тысячами, то важным фактором становится время. И выбрать правильный - самый быстрый! - способ уже критически важно.

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


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

Выкладываю традиционный новогодний минипрезент в виде календаря на 2020 год в Excel. Кроме, собственно, календаря там есть:

• все официальные праздники, переносы рабочих дней и т.д.
• количество рабочих и выходных дней и часов при различных типах рабочей недели
  
• все памятные даты, "дни взятия Бастилии" и прочие поводы отметить
• возможность планировать свои дела с шагом 1 час на любой день года
• календарик-пинарик
  

Внутри всё на макросах, так что не забудьте их разрешить при открытии файла.


Качайте

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



Просвещайтесь!