• Архив

    «   Август 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 29 30 31      

Новая версия надстройки PLEX 2017.3 для Excel

Выпустил третье в этом году обновление своей надстройки PLEX для Microsoft Excel 2007-2016.
Из главного - теперь надстройка доступна и в англоязычном варианте!



Полностью переведены на английский и все формулы в Библиотеке Формул:



.. а также все диалоговые окна, всплывающие подсказки, сообщения и т.д. В общем - полноценная локализация.

Для установки английской версии нужно подключать не файл PLEX.xlam, а файл PLEX_Eng.xlam. Обе версии одновременно подключать не рекомендуется - будут конфликты.

Плюс доработки и улучшения по мелочи:
  • Добавлено поле От в почтовую рассылку для выбора адреса отправителя при настройке Outlook на несколько почтовых аккаунтов.
  • В инструмент Текст добавлен универсальный вариант преобразования даты-как-текст в полноценную дату – Другой. Умеет преобразовывать множество текстовых вариантов написания даты в полноценную дату.
  • При вставке формулы из Библиотеки Формул она теперь вставляется со внутренними относительными, а не абсолютными ссылками (как раньше).
  • Также теперь Библиотека Формул корректно работает в том случае, если в Excel используется запятая как разделитель аргументов в функциях (а не точка с запятой как обычно).
Также была проведена большая работа над ошибками и исправлено множество мелких и не очень багов, улучшена стабильность и общая надежность работы.

Полный список изменений и исправлений можно всегда посмотреть на странице изменения версий.

Свежую версию, как всегда, можно скачать на странице надстройки PLEX.

Новая статья: Секционные диаграммы

Если вам нужно визуализировать на диаграмме множество элементов сгруппированных по смыслу (товары по категориям, города по странам, месяцы за несколько лет и т.п.), то может здорово помочь секционная диаграмма:



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

Читать статью полностью + видеоурок

Мои тренинги в Казахстане



Предварительно запланировали с коллегами из тренинговой компании Career Center проведение трех дней семинаров по Microsoft Excel в Астане с моим участием в качестве преподавателя. В программе самые лучшие фишки и приемы эффективной работы в Excel:
  • вычисления с помощью формул и функций
  • визуализация данных с помощью стандартных и нестандартных диаграмм
  • использование макросов для автоматизации рутинных операций
  • приемы обработки и анализа больших объемов данных
Тренинги пройдут 2-4 октября 2017 г.
Все подробности и запись по тел. +8 7015 332-899, +7 7172 785-437 или по почте  info@careercenter.kz

Новая статья: Поиск совпадений в двух списках

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


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

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

По мотивам одноименной статьи записал видео решения задачи по созданию сводной с текстом через PowerQuery:



Читать статью целиком

Новая статья: Слияние двух списков без дубликатов

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



Давайте подробно разберем три принципиально разных способа:
  • удаление дубликатов
  • сводная таблица
  • формулу массива
  • через Power Query
Читать статью полностью

Новая статья: Визуализация движения по маршруту в Excel

Как наглядно отобразить движение по заданному маршруту с привязкой ко времени на географической карте в Excel? Подробный разбор на реальном примере поезда "Москва - Самара"




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

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

Вышло очередное, второе в этом году, обновление моей надстройки PLEX для Microsoft Excel 2007-2016. Краткий обзор изменений и улучшений:

Очистка книги
Вместо отдельных функций очистки от макросов и лишних стилей добавлен мощный комплексный инструмент Очистка книги для глобальной очистки файлов Excel от «мусора», их облегчения и ускорения:


Умеет:
  • Удалять все пустые и скрытые листы в книге
  • Сбрасывать последнюю ячейку, когда из-за неиспользуемых пустых строк и столбцов под и справа от данных очень увеличивается размер файла, плохо работает прокрутка и т.д.
  • Удалять ненужные стили, чтобы вылечить «стилевой ад» и избежать ошибки «Слишком много разных форматов ячеек»
  • Удалять примечания на всех листах
  • Удалять мертвые именованные диапазоны с ошибками #ССЫЛКА (#REF), тормозящие пересчет книги.
  • Удалять избыточное форматирование строк и столбцов целиком (привет тем, кто любит выделить целый столбец и залить-обрамить "с запасиком", а потом удивляется дикому весу и торможению файла).
  • Удалять любую графику: картинки, автофигуры, надписи, элементы управления и ActiveX (выпадающие списки, чекбоксы, кнопки и т.д.)
  • Вычищать из книги макросы, код из модулей листов, пользовательские формы и прочие следы VBA.
Фильтр по выделению
Очень простая, но удобная штука: выделяете одну или несколько ячеек (удерживая Ctrl) с интересующими вас значениями в таблице, например, нас интересуют продажи ананасов в феврале менеджером Ивановым:

… жмете Фильтровать по выделению в выпадающем списке Фильтровать:


... и получаете фильтрацию по выбранным значениям в соответствующих столбцах:


Одним словом, некий аналог стандартного фильтра, но быстрее и удобнее в некоторых ситуациях. Умеет работать с умными таблицами тоже.

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

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


Также теперь можно скопировать отобранные строки вместе с шапкой (галочка Продублировать строку заголовка).

Переход по ссылке в формуле
Олдскульная фишка :) В старых версиях Excel было очень удобное сочетание клавиш Ctrl+[, по которому тоскуют многие пользователи, и о котором меня регулярно спрашивают на тренингах. Оно делало простую, но нужную вешь - заставляло Excel переходить по ссылке в ячейке. Т.е., например, если вы выделяете ячейку на итоговом листе с формулой =Лист1!A1 и жмете Ctrl+[, то тут же перепрыгиваете на Лист1 в ячейку А1, чтобы понять что там лежит. Удобно, особенно, если у вас много ссылок между листами. Проблема в том, что после 2003 года это сочетание работает уже не у всех (зависит от версии Excel и языковых параметров ПК).
Так что я написал макрос, который эмулирует это же действие. Причем, работает всегда и в любой версии и умеет переходить по ссылке не только в пределах книги (как было раньше), а и в другие файлы, и даже в закрытую книгу. Сочетание клавиш можно задать любое - в Диспетчере горячих клавиш, как и для всех других инструментов надстройки PLEX.


Сумма выделенных ячеек в Буфере
Еще одна простая, но полезная фишка - по просьбам пользователей. Жмете удобное вам сочетание клавиш (какое конкретно - можно задать там же в Диспетчере горячих клавиш) и в буфер помещается сумма значений в выделенных ячейках. Учитываются только видимые (отфильтрованные) ячейки.


Сохранение листов книги как отдельных файлов
Интерфейс окна этого инструмента был переработан в сторону упрощения и большего удобства. Также была добавлена возможность задавать папку для сохранения созданных из листов файлов:


Добавления в инструменты сводных таблиц


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

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

Функции склейки текста по условию(-ям)
В набор пользовательских функций и Библиотеку Формул добавлены функции MergeIf и MergeIfs для выборочной склейки текста по условию, как это было подробно описано здесь:


Прочее
  • Двойной щелчок левой кнопкой мыши по названию макроса в Диспетчере горячих клавиш запускает этот макрос.
  • В инструментах сводных таблиц команда Отвязать от данных переименована в более понятную Копировать как значения.
  • Несчетное количество мелких улучшений и исправлений ошибок, как всегда :)

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

Обновление статьи Склейка текста по условию

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


Дописал еще два способа: реализацию этой задачи формулами и через Power Query. Плюс сделал видеоурок по всем способам:


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

Заплатки от вируса WannaCrypt

Думаю, большинство уже в курсе, ибо даже по ТВ озвучили. Для тех, кто не в теме, если коротко: 12 мая началась массированная атака нового вируса-шифровальщика WannaCrypt на компьютеры по всему миру. Заражает через дырку в протоколе SMB через интернет без всякого участия пользователя, достаточно просто доступа в интернет. Шифрует рабочие файлы и просит 300$ за восстановление.
Меры защиты:
  • если у вас Windows 7 или 10 с автоматической установкой всех обновлений - выдыхайте, эта дырка закрыта еще мартовскими обновлениями
  • если автообновлений нет, то ставьте вручную https://technet.microsoft.com/en-us/library/security/ms17-010.aspx
  • если вы еще на Windows XP и Windows 8 - для этих ОС Microsoft вчера вне очереди выкатила спецобновление, хотя эти версии Windows уже 3 года без официальной поддержки
Вчера один хороший человек сильно притормозил эпидемию, зарегистирировав бессмысленный домен, оставленный в коде вируса как тестовый выключатель, но это, скорее всего, временно.

Надеюсь, что вас не зацепило, и у вас все хорошо. "Патчей нет, но вы держитесь" :)

Свежие книжки

Что-то я подзабил писать обзоры на прочитанное, а это - неправильно. Исправляюсь.
Чарльз Уиллан "Голая статистика"
Неплохо об основах статистики. Если вы основательно забыли вузовский курс статистики (или его у вас не было), то - самое оно. В книге почти нет формул, но много доходчивых примеров из жизни, на которых объяснются базовые понятия типа корреляции, медианы, среднеквадратического отклонения, предельной теоремы и т.д.  Как по мне, слишком уж популярно, хочется посложнее и поглубже, но пару-тройку примеров для своего тренинга по прогнозированию утащил, да :)
Александр Левитас "Экспресс-маркетинг. Быстро, конкретно, прибыльно"
Набор простых, практических рекомендаций для повышения продаж. Чем-то похожа на "Маркетинг без бюджета" Игоря Манна, но слегка в другом ключе. Большинство идей, конечно, больше про живой бизнес (магазины, кафе и т.д.), но и для онлайн тоже очень много полезного. Выписал себе с десяток в задачи - будем внедрять.
Энтони Роббинс "Деньги. Мастер игры"
Главная идея книги: "хочешь безбедную старость - начинай откладывать и инвестировать уже сейчас". Тони, пользуясь личными связями, опросил самых известных инвесторов, финансистов, управляющих фондов и т.д. на предмет "какие финансовые знания вы бы оставили своим детям, если бы не смогли оставить им деньги?". В итоге получился набор советов и стратегий для максимально эффективного и безопасного инвестирования. К сожалению, не все из предложенного применимо у нас из-за различий нашего и американского налогообложения, финансовых инструментов и т.д., но книга однозначно стоит внимательного изучения если вы не планировали в старости прозябать на соцпособие (если оно вообще у нашего поколения будет).
Барбара Демик "Повседневная жизнь в Северной Корее"
Единственная книга, написанная по воспоминаниям людей бежавших из Северной Кореи. Пришлось сделать над собой усилие, чтобы дочитать, но не потому, что книга плохо написана (с этим проблем нет), а потому что - жутко. Сюр. Борхес. Липкий ночной кошмар в отдельно взятой стране, которую "большие дяди" зачем-то разделили по 38-параллели на две половины. И вдвойне страшнее становится, когда понимаешь, что это не все не сто-двести лет назад, не в войну - это здесь и сейчас, максимум - 10 лет назад. Повседневный голод, талоны на еду, работа без зарплаты, официальное стукачество, тотальная промывка мозгов с детства. И местами даже узнаешь антураж, к сожалению. Но нам до них далеко.
Джоэл Спольски "И снова о программировании"
Продолжение первой книги, о которой я уже писал. Все также тонко, умно и смешно о жизни разработчиков, программировании, управлении командой IT-спецов и компанией из этой области. Непрограммистам можно не читать - не поймете львиную долю намеков и фишек. Программистам читать обязательно :)
Было еще штук десять книжек, которые начал и бросил - про них не пишу. И в общем и целом, в последнее время стараюсь практиковать "правило первых 50 страниц" - нефиг на макулатуру время тратить :)

Авторизация через социальные сети

Прикрутили к сайту авторизацию через модные нынче социальные сети. Так что теперь на странице входа и регистрации внизу есть красивые кнопочки "для ленивых":

Марафон "30 дней = 30 функций" #30: Выборочное суммирование по нескольким условиям с БДСУММ (DSUM)

Ну, вот мы с вами и добрались до последней 30-й статье в нашем марафоне "30 дней = 30 функций". По факту, получилось чуть больше и дней и функций, но это не со зла :)
А замыкающей у нас будет редкая, но весьма полезная функция БДСУММ (DSUM).

Для выборочного подсчета по нескольким условиям в больших таблицах можно использовать несколько способов: фильтры, сводные таблицы, функции СУММЕСЛИ и СУММЕСЛИМН и т.д. Еще одним, относительно экзотическим, но весма мощным инструментом является функция БДСУММ (DSUM) из категории Работа с базой данных (Database). При внешней простоте, она позволяет гибко фильтровать списки по нескольким сложным и связанным между собой И-ИЛИ условиям и подсчитывает сумму найденных записей по заданному столбцу.

Марафон "30 дней = 30 функций" #29: Быстрый прогноз функцией ПРЕДСКАЗ (FORECAST)

Умение строить прогнозы, предсказывая (хотя бы примерно!) будущее развитие событий - неотъемлемая и очень важная часть любого современного бизнеса. Само-собой, это отдельная весьма сложная наука с кучей методов и подходов, но часто для грубой повседневной оценки ситуации достаточно простых техник. Одна из них - это функция ПРЕДСКАЗ (FORECAST), которая умеет считать прогноз по линейному тренду.

Марафон "30 дней = 30 функций" #28: Скрытый комментарий к формуле функцией Ч(N)

Это одна из самых лаконичных функций Microsoft Excel, состоящая всего из одной буквы - Ч (N). При этом она весьма полезна, т.к. выполняет простое, но важное действие - превращает свой аргумент в число. На практике такое бывает полезно, например, для добавления текстового комментария прямо в формулу:



Простенько, но со вкусом. Пользуйтесь :)
Страницы: 1 | 2 | 3 | 4 | 5 | ... | 22 | След.