• Архив

    «   Февраль 2017   »
    Пн Вт Ср Чт Пт Сб Вс
        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          

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

Что делать, если вы построили несколько сводных таблиц на основании одного источника и теперь не получается группировать их отдельно - группировка одной сводной влияет на все остальные и наоборот. Как отвязать сводную от общего кэша? Подробно разобрал три варианта:
  • Построить сводную с помощью Мастера, кнопку для которого нужно сначала вытащить в интерфейс
  • Отвязать уже имеющуюся сводную от общего кэша руками
  • Использовать готовый макрос


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

Новое видео: Почтовая рассылка средствами PLEX

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

Не вопрос - записал:

Загрузка плеера

Новая статья: Сборка таблиц из разных файлов с помощью Power Query

Что делать, если нужно быстро собрать данные из большого количества файлов Excel в одну таблицу? Вариантов тут, обычно, несколько:
  • тупо копипастить вручную (с кучей ошибок в процессе)
  • писать макрос для сбора данных или заказывать его знакомому VBA-программисту
  • делегировать задачу подчиненным :)
На самом деле, начиная с Excel 2013 есть еще один путь - сильно проще и на порядок удобнее - надстройка Power Query. Для Excel 2013 ее можно совершенно бесплатно скачать и подключить, а в Excel 2016 она уже входит по-умолчанию. Написал подробную статью о том, как использовать ее в подобной ситуации. Основные шаги тут следующие:
  1. импорт одного файла для примера
  2. преобразование процедуры импорта и "причесывания" данных в функцию
  3. импорт всех файлов из папки
  4. добавление созданной пользовательской функции для загрузки данных из каждой книги
Быстро и изящно.



Если у вас пока нет Excel 2013-2016 - все равно гляньте, чтобы оценить красоту решения :)

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

Новая статья: Левый ВПР

Что делать, если нужно найти в таблице заданное значение, скажем, в пятом столбце, а извлечь результат из той же строки, но 3-его столбца? Т.е. реализовать "левый ВПР"?



Написал статью о том, как решить эту распространенную проблему несколькими способами:
  • в лоб (ручной перестановкой столбцов)
  • виртуальной перестановкой столбцов функцией ВЫБОР
  • связкой функций ИНДЕКС и ПОИСКПОЗ
  • функцией выборочного суммирования СУММЕСЛИМН
  • макросом
Выбирайте, какой вам больше нравится :)

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

Тестируем новый антиспам




День добрый, коллеги!

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

Я руками чистил и банил с плеча, что мог, но затем было решено подойти к вопросу стратегически.

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

Большая просьба сообщать о всех обнаруженных странностях на форуме и сайте в целом для их устранения. Личные сообщения на форуме включены обратно.

Живите и процветайте :)

Новая версия надстройки PLEX 2017.1

Выложил новую версию надстройки PLEX для Microsoft Excel с кучей обновлений и улучшений.

Как многие могли с ходу заметить - изменил систему нумерации версий, т.к. в этих 2.1, 2.2 и т.д. уже сам начал путаться. Теперь все просто - версия будет обозначаться годом и номером версии в году. Так что первая выпущенная в этом году версия имеет номер 2017.1, следующая будет 2017.2 и т.д.

Из крупных улучшений:

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


Сортировка по цвету
Переработан и дополнен инструмент сортировки строк по цвету - теперь он умеет сортировать не только по цвету заливки, но и по цвету шрифта и по начертанию (полужирный, курсив) и в заданном направлении:


В версии Excel 2010-2016 сортировка учитывает фактический экранный цвет ячеек, т.е. цвет с учетом условного форматирования (в Excel 2007 это невозможно, к сожалению). Также этот инструмент теперь полноценно умеет работать с умными таблицами.

Работа с почтой
Доработан инструмент Почтовая рассылка:

  • теперь можно прикреплять к сообщениям не только файлы, но и листы (автоматически будут выделены из текущей книги в отдельные файлы и прикреплены к письму)
  • добавлять в сообщения подписи из Outlook
  • вместо текстовых путей к файлам можно использовать гиперссылки (добавленные в ячейки через Вставка - Гиперссылка, например)
  • заранее проверяются недостающие файлы и листы
Также добавлена команда для быстрой отправки текущего листа по почте (в старых версиях такая функция Excel была штатной, но потом ее зачем-то убрали). Вместе с почтовой рассылкой эта команда теперь находится в списке E-mail:


Ключевая ставка
В инструмент Курсы валют кроме ставки рефинансирования добавлена ключевая ставка (действует с 13 сентября 2013 г по настоящее время). По решению ЦБ РФ с 1 января 2016 года она заменяет ставку рефинансирования:


Работа с текстом
Доработки и улучшения в инструменте Работа с текстом:
  • Отображаются неразрывные пробелы при отображении непечатаемых символов (привет 1С-никам!)
  • Удаляются неразрывные пробелы при зачистке командой Удалить непечатаемые символы
  • Добавлена подсветка кириллицы в латинице и латиницы в кириллице красным


Добавлена конвертация в число из разных форматов чисел-как-текст:


Добавлена конвертация в полноценную дату из разных форматов дата-как-текст (включая Unix-вид):


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




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




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

Функция VLookups
Добавлена функция VLookups (еще один улучшенный аналог ВПР):
  • ищет сразу по нескольким столбцам (до 3)
  • может извлекать нужное по счету вхождение (а не только первое, как ВПР)
  • можно задать выводимое значение в случае ошибки (не нужна функция ЕСЛИОШИБКА)


Функция WorkHours
Добавлена функция WorkHours. Подсчитывает количество рабочих часов между двумя заданными датами с учетом определенного времени начала и окончания рабочего дня и праздников:


Плюс энное количество исправлений ошибок и мелких улучшений.

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

Купить полную версию надстройки PLEX можно здесь.

Поздравления и интерактивный календарь в Excel на 2017 год

Друзья! Уже несколько раз замечал, что в предновогодней суете твои поздравления как-то смазываются и теряются. Поэтому в этом году я решил оригинально поздравить вас не 31 декабря, а 1 января, когда "пыль осела", торопиться уже никуда не надо, а можно спокойно выспаться. Плюс мысль о том, что в холодильнике еще на 3 дня еды - греет душу :)

Так что - с Наступившим вас!

А пожелать вам (и себе) в этом году хочу побольше осознанности и вовлеченности. Заниматься одним, а думать про другое, действуя на автопилоте и бессмысленно проматывая "счетчик" - это мы все мастера, да. Планы, конечно, надо строить, но жить надо не будущим (и тем более не прошлым), а настоящим. Брюс Уиллис как-то сказал: "Жизнь не кино, дублей не будет." А буддисты говорят: "Если моешь чашку - мой чашку!". Включайтесь на 100% в то, что делаете в данный момент, даже если это мелочи. Прочувствуйте текущий момент, ощутите его, пропустите через себя. И будьте благодарны за то, что у вас есть сейчас: родных и близких (они не всегда будут с вами), крышу над головой (у многих ее сейчас нет), работу (которая приносит окружающим добро и пользу, а не только ЗП на карточку), чашку вкусного кофе в руках. Ловите момент - и не отпускайте.

Вот так. Несколько высокопарно получилось, но я и правда так думаю :)

Ну, и в качестве традиционного подарка выкладываю новый календарь на 2017 год в виде книги Excel:




Умеет:
  • отображать все государственные, профессиональные и некоторые основные православные праздники (идеально, если нужен повод)
  • содержит таблицу официальных норм рабочего времени для разных типов трудовой недели (полезно кадровикам и не только)
  • встроенные макросы позволяют вводить, хранить и быстро отображать запланированные события для любой даты (если вы не используете для этого Outlook, Google Calendar или Todoist, например)
Скачать календарь на 2017 год с праздниками в формате Excel

P.S. Не забудьте разрешить запуск макросов при открытии.

Итоги 2016



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

Корпоративные тренинги
  • Провели в этом году с коллегами по команде 105 тренингов общей длительностью 1420 акад. часов (примерно 178 дней). Обучили, в общей сложности, порядка 1400 человек из 36 компаний.
  • Лично я провел 68 тренингов общей длительностью 924 акад. часа = 115 дней и обучил плюс-минус 890 человек.
  • Больше половины компаний-клиентов пригласили нас по второму-третьему-и т.д. разу: Мегафон, Гедеон Рихтер, ВТБ, Xylem, ФармСтандарт, ФПК, Видео Интернейшнл, Reima, Valeant, ПГК, Газпромнефть, Globus, KIA, Coca-Cola, Takeda...
  • Добавились несколько новых, но уже любимых клиентов: Crocs, Casio, Yokogawa, Toyota MH, Игристые Вина, Авилон, Верофарм, Pirelli, Вимм-Билль-Данн, Nissan, ГК "Черкизово", Rohde&Schvartz
  • По структуре: Excel - 70%, Power Point - 15%, Outlook, Project и Word - по 5%. Excel, само-собой, был и будет нашей основной специализацей, но приятно иногда разбавить :)
  • Сделал новый тренинг по бизнес-аналитике (BI) в Excel и "обкатал" его в двух компаниях. На праздниках внесу последние штрихи и выложу официально в раздел Тренингов.
Открытые тренинги
  • Переехал на полностью "собственные рельсы" в организации своих открытых тренингов (раньше с набором групп, оплатой и оформлением помогал замечательный "Центр Мастеров";). Все оказалось не так уж и сложно: нашел подходящий класс в центре, настроил систему оплаты через Яндекс.Кассу, шаблоны договоров и т.д. и уже своими силами провел в осеннем треке 6 открытых тренингов (10 тренинговых дней - от начального курса до продвинутого).
  • Приятно удивило, что группы набрались полные весьма шустро. Многим даже не хватило мест и пришлось оперативно спланировать расписание на весну-2017 (половина мест там уже тоже заполнена, кстати).
  • Итого на открытых тренингах в этом году у меня побывало 98 человек (65 физлиц + 33 от компаний).
Сайт
  • За 2016 год на сайте побывало 4.7 млн. уникальных пользователей (+1.2 млн. к прошлому году) и было 17.7 млн. просмотров страниц (+3.5 млн. к прошлому году).
  • Написал 15 новых статей в Приемах
  • Выложил в мае "на пробу" свой первый онлайн-курс "Прожиточный минимум в Microsoft Excel" с целью понять стоит ли, в принципе, дальше копать в направлении дистанционных курсов и делать их еще, ибо аналогов вокруг как грязи, торренты не спят и т.д.
  • Записал и выложил 12 обучающих видео (не считая онлайн-курса, где их еще 10). На своем канале в YouTube незаметно перешел планку в 4 млн. просмотров и набрал почти 30 тыс. подписчиков
Книгописательское
  • Первая моя книга "Microsoft Excel: Готовые решения - бери и пользуйся!", как это ни удивительно, до сих пор продается (онлайн и офлайн) и даже приносит какую-то ощутимую денежку, хотя давно лежит в торрентах. Спасибо всем, кто честно купил.
  • Закончил вторую книгу "Microsoft Excel: Мастер Формул" - подробное руководство по "высшему пилотажу" в формулах и функциях Excel. На данный момент книга прошла все подготовительные этапы (редактура-корректура, верстка, дизайн обложки, присвоение ISBN и т.д.) и ушла в печать - жду первый тираж после НГ-праздников. Чуть попозже выложу и электронную версию здесь на сайте в разделе Книги, само-собой.
Книгочитательское
  • За этот год прочитал около 25 новых книг (художественных и по бизнесу - примерно в пополаме). Заметил, что все больше стал не читать, а перечитывать, отлавливая нюансы и идеи. И штук 10 книг бросил без сожалений на 30-50 странице, если "не пошло" - раньше дочитывал через силу.
  • К сожалению, совершенно не было времени писать о прочитанных книжках, а зря - было много хороших, мыслями о которых хочется поделиться. Виноват, да, я знаю - обещаю исправиться.
Надстройка PLEX для Excel 2007-2016
  • Наконец-то зарегистрировал официально свои права на PLEX. Да-да, я знаю, что это почти ничего не значит и больше для внутреннего успокоения, но все же :)
  • Выпустил два обновления (2.5 и 2.7) и обновил дизайн. Реализовано 20+ новых функций и улучшений имеющихся инструментов.
Прочее
  • В июньском номере журнала "Финансовый Директор" вышла моя статья по хитростям работы в Excel (доступна только по подписке, к сожалению).
  • Провел на платформе того же ФД открытый вебинар по особенностям использования функций подстановки (ВПР и иже с ней). Участвовало больше 1000 человек - побил собственный рекорд.
  • В 8-й раз получил номинацию Most Valuable Professional (MVP) по Excel от Microsoft.
Вот как-то так. Не всем доволен, конечно, но как пел Егор "все идет по плану".
А как прошел ваш год? :)

Новая статья: 5 вариантов использования функции ИНДЕКС

Написал статью и записал видео про одну из самых неоднозначных, многоплановых и полезных функций Microsoft Excel - функцию ИНДЕКС (INDEX):



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

Новое видео: Редизайн кросс-таблицы в плоскую

Записал видео к старой статье про редизайн кросс-таблиц в плоские с помощью макросов и надстройки Power Query из Excel 2013-2016:

Загрузка плеера

Новая статья: сводная таблица с текстом в значениях

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



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

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

Выпустил очередное плановое обновление своей надстройки PLEX - набора макросов на все случаи жизни при работе в Microsoft Excel. Что изменилось:

Преобразование чисел в текст
Само-собой, чаще требуется обратное - преобразование чисел-как-текст в полноценные числа, но иногда нужно и наоборот. Теперь надстройка PLEX это умеет в инструменте Текст на вкладке Исправить:



Преобразование можно реализовать форматом или добавлением апострофа (текстового префикса).

Удаление принудительных переносов строк
Если нужно избавиться от сочетаний Alt+Enter, вызывающих принудительные переносы строк внутри ячеек, то не нужно делать это заменой - теперь это также есть в инструменте Текст на вкладке Удалить:




Исправление случайных русских букв в английском тексте

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



Сохранение листов как отдельных файлов в текстовых форматах
Инструмент Сохранение листов книги как отдельных файлов теперь умеет сохранять не только в форматах Excel и PDF, но и в CSV и TXT в разных кодировках (Windows, MSDOS, Mac и т.д.)



Инструменты для работы с дубликатами


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



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



Инструмент Удаление повторов убирает дубликаты (но не удаляет сами ячейки, как это делает стандартный инструмент Excel на вкладке Данные – Удалить дубликаты):


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



По-сути, очень похожа на функцию FindSame, но гораздо лучше ищет слова с ошибками и опечатками.

Функция склеивания текста TextJoin

Добавлена функция TextJoin (аналог появившейся в последнем обновлении Office365), сцепляющая текст из диапазона ячеек с заданным разделителем и умеющая игнорировать пустые ячейки:


Office365 с последними обновлениями есть далеко не у всех, а функция весьма полезная.

Исправления ошибок и прочие доработки
  • Инструмент для создания динамических диапазонов теперь работает на функциях ИНДЕКС (INDEX), а не на СМЕЩ (OFFSET), что повышает быстродействие, т.к. функция СМЕЩ пересчитывается при любом изменении листа, а ИНДЕКС – только при изменении аргументов.
  • Исправлена ошибка при сборке листов, возникающая если файл недоступен.
  • Исправлена ошибка, возникающая, если при создании книги с листами с заданными именами были использованы недопустимые или повторяющиеся имена листов.
  • Исправлена ошибка, возникающая при преобразовании в значения сразу нескольких листов.
  • Исправлен ошибка создания бэкапа в Excel 2016.
  • Убрана функция SumBetween, т.к. начиная с версии Excel 2007 уже есть стандартная СУММЕСЛИМН (SUMIFS).
  • Функция GetText дополнительно убирает лишние пробелы между словами, но сами слова не склеивает:

  • Функция GetNumbers теперь умеет извлекать не только целые, но и дробные числа из текстово-числовой каши:



Как обычно, обновление до новой версии абсолютно бесплатно для всех, кто купил надстройку в прошлом. Войдите на сайт под своим логином в ищите сверху в меню желтую ссылку на скачивание в разделе PLEX.
Полное и подробное описание всех функций надстройки PLEX.

Новая статья: Анимированная пузырьковая диаграмма

Если вам приходится строить пузырьковые диаграммы, то вы оцените красивый и быстрый способ их анимировать с помощью стандартной Microsoft'овской надстройки Power View из пакета бизнес-анализа (BI):



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

Новая статья: Определение пола по имени

Стандартнейшая задача: есть список имен и надо проставить М или Ж напротив каждого. Желательно - не руками. Если есть отчества, то все проще, а если их нет? Или в списке есть экспаты?

Написал статью с подробным разбором каждого варианта и записал видео:

Загрузка плеера


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

Новое видео: Двумерный поиск в таблице (ВПР 2D)

Записал видеоурок к статье про двумерный поиск в таблице сразу по строчкам и по столбцам одновременно. Кто любит не читать, а смотреть-слушать - welcome :)

Загрузка плеера

Новая статья: Создание базы данных в Excel

Если вам хочется автоматизировать свой бизнес, но ваша компания пока не уровня "Газпрома", то можно попробовать использовать для этой цели Microsoft Excel. После небольшой доработки напильником из него вполне возможно создать небольшую, но полноценную БД: с отчетами, связанными таблицами, формами ввода и т.д. Потребуется немного времени, десяток формул и один небольшой макрос.



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