• Архив

    «   Ноябрь 2019   »
    Пн Вт Ср Чт Пт Сб Вс
            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  

Тренинги в Астане 26-28 ноября 2019 г.


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

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

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

Новые статьи про динамические массивы в Excel

Выложил две статьи (обе с видео) про революционное, прямо скажем, обновление вычислительного движка Excel, добавляющее поддержку динамических массивов (Dynamic Arrays) и новые функции для работы с ними:
Это очень-очень круто и даже немного жаль, что таких функций не было лет 10 назад - сколько времени можно было бы сэкономить! :)

Обновление надстройки PLEX v.2019.1

Выпустил долгожданное обновление моей надстройки 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.
Найденные баги, пожелания или идеи по поводу работы надстройки можно смело слать мне на почту.

Вышла моя книга по Power Query

Теперь уже вполне официально можно сказать, что я стал "многодетным отцом" в книжном смысле - только что вышла моя третья книга "Скульптор данных в Excel с Power Query". Электронная версия уже доступна на сайте с прошлой недели, а сегодня до меня доехал первый бумажный тираж из типографии:



330 страниц А4, качественная мелованная бумага, черно-белая печать.

Надеюсь, первая на русском языке книга о Power Query получилась достойной. Во всяком случае, я очень старался.
Несколько глав пришлось корректировать уже после написания пару раз, т.к. за последний год вышло очень много обновлений Excel, порой, весьма ощутимо меняющих интерфейс и возможности этой надстройки.
На данный момент идут переговоры с сетью книжных магазинов "Читай-Город", чтобы все мои книги в бумажном варианте появились на их полках и в интернет-магазине. Так что скоро, надеюсь, эти томики окажутся в зоне вашей досягаемости.

Подробнее о книге
 

Новая статья: Как открыть новый Excel в отдельном окне

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



Есть несколько способов это сделать в зависимости от вашей версии Excel и установленных обновлений:
  • В лоб
  • Мышью
  • Макросом
  • Командной строкой
  • Файлом сценария VBScript
Читать статью полностью

Новое видео: 7 способов преобразовать формулы в значения

Обновил старую, но не теряющую актуальность статью про конвертацию формул в значения. Дописал пару способов + запилил короткое видео:


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

Новая статья: Дубликаты внутри ячеек

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

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

Новая статья: Суперсила Мгновенного заполнения (Flash Fill)

Хотя инструмент Мгновенное заполнение (Flash Fill) появился в Excel ещё с 2013-й версии, но почему-то для многих пользователей этот факт остался незамеченным. И совершенно напрасно. Во многих случаях он оказывается проще, легче и быстрее, чем аналогичные решения на формулах или макросах. По моему опыту, на тренингах эта тема вызывает постоянное "вау!" аудитории - независимо от продвинутости и/или усталости слушателей.

Механизм работы этого инструмента прост: если у вас есть один или несколько столбцов с исходными данными и вы начинаете набирать рядом в соседнем столбце их же, но в каком-либо нужном вам измененном виде, то Excel рано или поздно намекнёт, что готов продолжить дальше за вас:



Чтобы выявить логику (шаблон, pattern) преоборазования и запустить эту функцию Excel обычно хватает ввода 1-3 первых результирующих значений вручную. Если предложенный вариант вам подходит, то достаточно нажать Enter - и остаток списка будет доделан моментально.
С помощью Мгновенного заполнения можно:
  • резать и клеить текст
  • менять местами слова
  • исправлять регистр
  • выдергивать числа из буквенно-цифровой каши
  • превращать числа-как-текст в нормальные числа
  • ... и многое другое.
Давайте рассмотрим несколько примеров использования этого инструмента на практике, чтобы понять его возможности.

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

Новая статья: Тонкости работы с переносами строк (Alt+Enter) в Excel

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

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


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

Новая статья: Сборка данных со всех листов книги в одну таблицу

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

Давайте рассмотрим их подробно:


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

Открытые тренинги в Астане 2-3 апреля 2019 г.



2-3 апреля буду в Астане Нурсултане вести открытый тренинг по Power Query. В программе:
  • Загрузка данных в Excel и Power BI из всевозможных нестандартных источников
  • Массовая загрузка файлов с данными и сборка отчетов
  • Трансформация и зачистка данных (группировка, хитрые сортировки и фильтрации, свертывание и анпивотинг).
  • Создание связей между загруженными таблицами данных.
  • Выгрузка результатов в виде сводной или в Модель Данных Power Pivot для дальнейшей аналитики.
Принимающей стороной, как всегда, выступает замечательный Учебный Центр Career Center.
Запись и подробности можно узнать по телефону +7(7172) 785-437 , +7 (701) 533-2899

Новая статья: Генератор фраз из заданных фрагментов

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


В математике такая операция называется декартовым произведением. Официальное определение звучит так: декартовым произведением множеств А и В называется множество всех пар, первая компонента которых принадлежит множеству А, а вторая компонента принадлежит множеству В. Причем элементами множеств могут быть как числа, так и текст.
Давайте рассмотрим пару способов реализовать подобное в Microsoft Excel: традиционными формулами и с помощью Power Query.

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

Новое видео: Заполнение пустых ячеек соседними значениями

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


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

Итоги 2018


У кого как, а у меня конец года обычно весьма жаркое и суматошное время - многие компании под конец года вспоминают, что у них остались бюджеты на обучение и в авральном темпе пытаются их потратить на что-нибудь полезное. "Полезным" часто оказываюсь я и мои коллеги-тренеры, поэтому заказов в декабре обычно больше, чем всегда. Тренингом 30 декабря, когда половина офиса уже улетела в отпуск, а другая с утра начала отмечать - меня не удивишь.
Поэтому итоги года в этот раз подводились уже в первых числах января, после праздников, когда суета спала, в почте только редкие поздравления и никаких рабочих задач. В этот раз всё было даже ещё приятнее, т.к. мы перед самым Новым годом умотали "на моря" и анализ прошедшего года происходил под шум волн, прохладный (+15), но приятный турецкий ветерок и бокальчик апероля впридачу.

Тренинги и консалтинг
  • Провел 874 часа (109 дней) тренингов лично (48 корпоративных и 18 открытых) + 92 часа консалтинга.
  • Организовал 536 часа (67 дней) тренингов силами своих коллег-тренеров по команде.
  • В общей сложности на наших тренингах по Excel побывало примерно 1700-1800 человек (трудно сказать точно, т.к. по головам не считаем).
  • Как обычно, больше половины заказов от старых клиентов - народ с радостью возвращается: Национальный Рекламный Альянс, Газпром с "дочками", Глобус, КИА Моторс, Эггер, Такеда, Нижфарм, Капитал Групп, Рольф, Тюменьнефтегаз, Geberit, ВИП Сервис и т.д.
  • Есть много новых лиц: НефтеТрансСервис, Посольство Австралии, Московский Индустриальный Банк, Efes, супермаркеты ОКей, Bayer, Nestle, Orica, Руст, Epson, Haier, Levi's, Simple и другие. Добро пожаловать в хорошую компанию!
  • Съездил с тренингами в Санкт-Петербург, Астану, Гагарин, Шую, Россошь, Тюмень, Алматы.
  • Выполнил и сдал 3 проекта по автоматизации бизнеса средствами Excel и Office (2 из них почти полностью на Power Query).
  • Подготовил, обкатал и выпустил тренинг "Анализ данных в Excel с Power Pivot" - от построения моделей до основ DAX.
Сайт, форум и YouTube-канал
  • Мой канал на YouTube перешагнул отметку в 8 млн.просмотров и планку в 60 тыс. подписчиков. Почти догнал Bill Jelen, но до Дудя ещё далеко. За 2018 год я опубликовал 13 видеоуроков, было почти 2 млн.просмотров и добавилось 16 тыс. подписчиков.
  • На "Планете" за этот год побывало 4.7 млн. пользователей, просмотревших 17 млн. страниц. Это на 6% меньше, чем в прошлом году. Будем смотреть из-за чего так вышло, пока трудно сказать, надо анализировать статистику. И лучше стараться, видимо.
  • На Форуме за 2018 год было создано более 12 тыс. тем с вопросами по Microsoft Excel, на большинство из которых были получены ответы - еще раз хочу сказать спасибо всем, кто помогал другим и опубликовал там свои решения.
Надстройка PLEX
  • Выпустил 4 обновления надстройки своей надстройки PLEX для Excel. Добавлена куча функций и инструментов - см.подробную историю версий.
  • Помимо продаж всем желающим физлицам через сайт, корпоративную лицензию на PLEX для своих сотрудников в этом году купили компании LG, Норильский Никель, Мегафон, ПИИТ, Газпромнефть Сахалин и др.
Личное
  • Прочитал 14 книг по бизнесу-психологии-саморазвитию и 4 по Excel, разным Power'ам и DAX'у (не считая тех, что бросил после 40-50 страниц, если не пошло). По ощущениям, темп по сравнению с прошлым годом снизился, но выросло качество - теперь прорабатываю каждую книжку более тщательно. Надо бы написать по ним отчет, кстати.
  • Написал 80% своей третьей книги по Power Query. В феврале должна уйти в издательство. Книжка будет - огонь!
  • Бесплатно обучили (сам лично и силами коллег-тренеров) почти 30 сотрудников благотворительных фондов "Милосердие" и детского хосписа "Дом с маяком". Когда вспоминаю об этом, то все продолбанные в этом году задачи уже не кажутся такими ужасными - год прожит не зря.
  • Немного не дотянул до поставленного себе на этот год по плану заработка, но все равно побил собственный рекорд за прошлый год почти на 20%. Но это без учета инфляции.
  • Главные покупки года: планшет-ноутбук Microsoft Surface 5 (мощный, легкий и удобный - замена паре ноут+айпад, для выездных тренингов самое то), наушники с шумоподавлением Bose Quiet Comfort (теперь можно спать в самолетах и работать рядом с перфоратором - незаменимая вещь) и офисное кресло Herman Miller Aeron (забыл про боли в спине).
  • Отдохнули с женой и сыном на, Кипр, в Тай, Испанию и Турцию. Банальный набор, я знаю. Надеюсь, в наступившем году добавить экзотики, т.к. сын подрос и даже пошел в школу.
  • Сходил в сентябре на семинар Тони Роббинса.
  • Завел собаку Баскервилей
Наверх