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

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


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

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

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

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


Качайте

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



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

В конце ноября буду вести два открытых тренинга в Астане по самым востребованным сейчас у продвинутых пользователей Excel инструментам - Power Query и Power Pivot. Если давно хотели выйти в работе с Excel на качественно новый уровень - добро пожаловать.

Организаторами выступают мои проверенные казахские коллеги из учебного центра CareerCenter.kz
Телефон для записи +7 (7122) 755-140

Приходите, будет интересно

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

Список нововведений таков:

Выпадающий список с автопоиском

Уберфича. Новый инструмент для суперудобного и быстрого ввода повторяющихся данных – Выпадающий список с автопоиском. Механика его работы проста: ставим активную ячейку в столбец, куда нужно ввести данные и выбираем соответствующую команду:


Появляется компактное окно с выпадающим списком, который поддерживает моментальный поиск и фильтрацию «на лету» по первым введенным символам:


Достаточно набрать небольшую часть требуемого текста и нажать клавишу Enter или Tab, чтобы подтвердить ввод остального.
Инструмент поддерживает любые выпадающие списки, сделанные с помощью классической команды Excel Данные – Проверка данных (Data – Data Validation), в том числе и динамические, сделанные с помощью функций типа ДВССЫЛ, ИНДЕКС и т.п. Если в текущей ячейке такого списка нет, то автоматически будет сформирован список уникальных значений из всех ячеек текущего столбца на листе или в «умной» таблице.
Для быстрого вызова этого окна можно назначить ему любое удобное вам сочетание клавиш в Диспетчере горячих клавиш (кнопка Горячие клавиши на вкладке PLEX):



Улучшения в почтовой рассылке

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

• Отложенная отправка, чтобы начинать рассылку в нужное время.
• Пауза между сообщениями (в минутах, часах или днях), чтобы не заваливать сервер большим количеством сообщений сразу, а дозировать нагрузку.
• Высокая важность
• Пометка «К исполнению» (флаг Follow Up)
• Уведомление о прочтении
  

Запуск нового независимого экземпляра Excel

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



Фильтрация сводных таблиц по именованным диапазонам

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

Алгоритм его использования прост:

1. Сначала нужно создать именованный диапазон со списком тех товаров, сделок, клиентов и т.п., которые вам нужны. Для этого можно использовать инструмент PLEX Вып.список – Создать новый или стандартный Диспетчер имен (Name Manager) на вкладке Формулы (Formulas).
2. Затем установите активную ячейку в любое место сводной и выберите на вкладке PLEX – Сводные таблицы – Фильтр по именованным диапазонам. В открывшемся окне задайте какой именованный диапазон вы хотите применить к какому полю в сводной:
   

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

Подсветка, удаление и извлечение дубликатов внутри ячеек

Если в вашей работе попадаются ситуации, где внутри ячеек бывают повторяющиеся слова, то пригодятся две новые команды в меню Уникальные:


Первая из них выделяет повторяющиеся слова цветом шрифта:




... а вторая – удаляет повторы, оставляя каждое слово только один раз.
Для извлечения повторяющихся слов можно использовать новую макрофункцию GetDuplicates:



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

Курсы валют Национального Банка Казахстана

Теперь PLEX умеет запрашивать и выводить официальные курсы валют Национального Банка Казахстана. Сделать это можно с помощью инструмента Курсы валют:



Или с помощью новой макрофункции NBK, которую можно ввести через Библиотеку Формул:



… или напрямую в ячейку:



Сумма прописью – добавлены тенге, гривна, белорусский рубль и юань

В знакомую многим функцию Propis кроме рубля, доллара и евро добавлена возможность выводить сумму прописью (на русском и английском) для валют Казахстана, Украины, Республики Беларусь и Китая.
За выбор соответствующей валюты отвечает второй аргумент этой функции, который теперь может принимать значения:

• RUB
• EUR
• USD
• UAH
• KZT
• BYR
• CNY
  

Третий аргумент, как и было ранее, задает язык результатов ("RU" или "EN" )



Вставка текста при смене буквы на цифры и смене регистра

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



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

Удаление зачеркнутого текста

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




Теперь можно в два щелчка убрать из текста все вычеркнутые фрагменты:


Номера недель в Календаре

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



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

Приходилось ли вам когда-нибудь по несколько минут ждать пока в вашей книге Excel отработает макрос, обновится запрос Power Query или пересчитаются тяжелые формулы? Можно, конечно, заполнить случившуюся паузу чайком-кофейком на вполне законных основаниях, но наверняка вам приходила в голову и другая мысль: а не открыть ли рядом другую книгу Excel и не не поработать ли пока с ней?



Есть несколько способов это сделать в зависимости от вашей версии Excel и установленных обновлений:

• В лоб
• Мышью
• Макросом
• Командной строкой
• Файлом сценария VBScript
  

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

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

Хорошо видно, что некоторые имена в списках внутри ячеек повторяются. Давайте посмотрим, что можно с этим сделать, а именно:

• Как быстро найти все ячейки, содержащие повторы
• Как подсветить дубликаты цветом
• Как удалить дубликаты, оставив только неповторяющиеся значения
  

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

Переносы строк внутри одной ячейки, добавляемые с помощью сочетания клавиш Alt+Enter - дело весьма частое и привычное. Иногда их делают сами пользователи, чтобы добавить красоты длинному тексту. Иногда такие переносы добавляются автоматически при выгрузке данных из каких-либо рабочих программ (привет 1С, SAP и т.д.) Проблема в том, что на такие таблицы приходится потом не просто любоваться, а с ними работать - и вот тогда эти невидимые символы переноса могут стать проблемой. А могут и не стать - если уметь правильно с ними обращаться.

Давайте-ка мы разберёмся в этом вопросе поподробнее.  А именно:

• Научимся удалять нежелательные переносы заменой или формулами
• Научимся делить по Alt+Enter'ам на столбцы или строки (макросом или через Power Query)
  

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

2-3 апреля буду в Астане Нурсултане вести открытый тренинг по Power Query. В программе:

• Загрузка данных в Excel и Power BI из всевозможных нестандартных источников
• Массовая загрузка файлов с данными и сборка отчетов
• Трансформация и зачистка данных (группировка, хитрые сортировки и фильтрации, свертывание и анпивотинг).
• Создание связей между загруженными таблицами данных.
• Выгрузка результатов в виде сводной или в Модель Данных Power Pivot для дальнейшей аналитики.
  

Принимающей стороной, как всегда, выступает замечательный Учебный Центр Career Center.
Запись и подробности можно узнать по телефону +7(7172) 785-437 , +7 (701) 533-2899

Записал и выложил новое видео к старой статье по заполнению пустых ячеек соседними значениями. Разобрал все основные способы: формулы, макросы, с помощью Power Query и даже PLEX.


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

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

Если вы наш постоянный "житель" и хотя бы раз помогли кому-то на форуме или в комментах - поклон вам от меня до земли, бесконечный респект и благодарность от всего человечества и от меня лично. Спасибо, что вы есть, и что вам не всё равно. За терпение и желание помогать другим и делиться знаниями, опытом и набитыми шишками с теми, кто выбрал Excel в качестве "боевого оружия".

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

Позвольте пожелать вам удачи во всех ваших начинаниях. Пусть в наступающем году мечты перестанут быть мечтами, а перейдут в категорию "планы". А планы пусть разобьются на задачи и неумолимо выполнятся, шаг за шагом - принося вам  радость и удовольствие, а всем окружающим пользу.  Берегите себя и своих близких, да пребудет с вами сила, аминь!