• Архив

    «   Июнь 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    

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

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



Давайте подробно разберем три принципиально разных способа:
  • удаление дубликатов
  • формулу массива
  • через 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). При этом она весьма полезна, т.к. выполняет простое, но важное действие - превращает свой аргумент в число. На практике такое бывает полезно, например, для добавления текстового комментария прямо в формулу:



Простенько, но со вкусом. Пользуйтесь :)

Марафон "30 дней = 30 функций" #27: Поиск минимального и максимального по условию с МИНЕСЛИ и МАКСЕСЛИ

В Microsoft Excel давно есть в стандартном наборе функции СЧЁТЕСЛИ (COUNTIF), СУММЕСЛИ (SUMIF) и СРЗНАЧЕСЛИ (AVERAGEIF) и их аналоги, позволяющие искать количество, сумму и среднее в таблице по одному или нескольким условиям. Но что если нужно найти не сумму или среднее, а минимум или максимум по условию(ям)?
Помогут новые функции, появившиеся в Excel 2016 - МИНЕСЛИ (MINIFS) и МАКСЕСЛИ (MAXIFS):



Если у вас (или тех, кто будет потом работать с вашим файлом) более старые версии, то придется шаманить другими способами:
  • писать формулу массива
  • использовать функцию ДМИН
  • делать сводную таблицу и вытаскивать данные из нее
Все способы - в очередной статье нашего марафона-ликбеза.

Марафон "30 дней = 30 функций" #26: Поиск ближайшего рабочего дня функцией РАБДЕНЬ (WORKDAY)

Что если при расчете сроков нужная вам дата выпадет на выходные? Как найти ближайший рабочий день к заданной дате, учитывая выходные и праздники (а также отпуска, отгулы, декреты и т.п.)? Поможет функция РАБДЕНЬ (WORKDAY), которая умеет учитывать нерабочие дни в таких случаях:

Марафон "30 дней = 30 функций" #25: Подробный разбор на примерах функции ДВССЫЛ (INDIRECT)

На первый взгляд (особенно при чтении справки) функция ДВССЫЛ (INDIRECT) выглядит простой и даже ненужной. Ее суть в том, чтобы превращать текст похожий на ссылку - в полноценную ссылку.



Однако, первое впечатление обманчиво, поверьте. Эта функция может помочь в огромном количестве ситуаций, например:
  • для создания несбиваемых ссылок
  • для сбора данных с нескольких листов или книг
  • для суммирования по интервалу-окну на листе
  • для транспонирования формулами
  • ...
Подробный разбор всех нюансов + видео - в очередной статье нашего марафона-ликбеза по ключевым функциям Microsoft Excel.

Марафон "30 дней = 30 функций" #24: Замена текста функцией ПОДСТАВИТЬ (SUBSTITUTE)

Замена одного текста на другой внутри заданной текстовой строки - весьма частая ситуация при работе с данными в Excel. Реализовать подобное можно двумя функциями: ПОДСТАВИТЬ (SUBSTITUTE) и ЗАМЕНИТЬ (REPLACE). Эти функции во многом похожи, но имеют и несколько принципиальных отличий и плюсов-минусов в разных ситуациях. Давайте подробно и на примерах разберем сначала первую из них.

Марафон "30 дней = 30 функций" #23: Как склеивать текст функциями СЦЕП, СЦЕПИТЬ и ОБЪЕДИНИТЬ

Часто возникает необходимость склеивать текст из нескольких ячеек в одну длинную фразу. Причем иногда нужно дополнительно вставлять между фрагментами еще и заданный символ-разделитель (пробел, запятую и т.д.)
Microsoft Excel в подобной ситуации может помочь тремя функциями:
  • СЦЕП (CONCAT)
  • СЦЕПИТЬ (CONCATENATE)
  • ОБЪЕДИНИТЬ (TEXTJOIN)
Подробная статья про все варианты в очередной порции нашего марафона по функциям Excel.

Марафон "30 дней = 30 функций" #22: Извлечение данных из сводной таблицы с ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ

Если сводная таблица для вас еще не финал, и нужно извлечь из нее данные для дальнейших расчетов, анализа или прогнозирования, то пригодится функция с длинным названиемПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (GETPIVOTDATA), которую мало кто (по моему опыту) использует правильно.
А она может быть очень и очень полезной:

Марафон "30 дней = 30 функций" #21: Удаление лишних пробелов из текста функцией СЖПРОБЕЛЫ (TRIM)

Весьма часто в нашем распоряжении оказываются данные, которые хоть и содержат полезную информацию, но не пригодны для немедленного анализа. Одной из весьма распространенных проблем является наличие лишних пробелов - в начале, в конце или внутри между словами.
Лечится эта проблема очень легко - специальной функцией СЖПРОБЕЛЫ (TRIM). Она убирает все лишние пробелы, но оставляет по одному пробелу между словами:

Марафон "30 дней = 30 функций" #20: Анализ предельных значений функциями НАИБОЛЬШИЙ и НАИМЕНЬШИЙ

Скорее всего вам известны простые функции МИН (MIN) и МАКС (MAX), позволяющие быстро найти минимальное или максимальное значение в таблице. Но что если нам требуется найти, например, не самое большое, а 2-е или 5-е значение в ТОПе? Здесь помогут функции НАИБОЛЬШИЙ (LARGE) и НАИМЕНЬШИЙ (SMALL).
С их помощью можно не только найти k-й по счету элемент списка, но и, если нужно:
  • отсортировать таблицу формулами "на лету"
  • построить рейтинг или Топ10
  • вытащить из списка все числовые значения по совпадению (Мульти ВПР)


Подробнее - в очередной статье нашего марафона по самым нужным функциям Excel.

Марафон "30 дней = 30 функций" #19: Номер недели по дате функцией НОМНЕДЕЛИ (WEEKNUM)

Потребность знать номер рабочей недели для заданной даты (или группы дат) весьма востребованная вещь в управленческом учете. Понедельные отчеты, еженедельный контроль выполнения плана, недельная сетка вещания в рекламе – все это требует умения определять номер рабочей недели для заданной даты.
В Microsoft Excel есть основная функция НОМНЕДЕЛИ (WEEKNUM) и ее аналог для стандарта ISO - функция НОМНЕДЕЛИ.ISO (WEEKNUM.ISO):. Плюс ко всему, можно, конечно же, вычислить номер недели формулой "в лоб".

Все варианты в очередной статье марафона "30 дней = 30 функций":

Марафон "30 дней = 30 функций" #18: Частотный анализ функцией ЧАСТОТА (FREQUENCY)

Продолжаем наш марафон. День 18-й.
При анализе данных периодически возникает задача подсчитать количество значений, попадающих в заданные интервалы "от и до" (в статистике их называют карманы). Для решения подобной задачи можно воспользоваться функцией ЧАСТОТА (FREQUENCY).

Марафон "30 дней = 30 функций" #17: Подсчет только по видимым ячейкам функцией АГРЕГАТ (AGGREGATE)

Если вы используете фильтрацию или скрываете некоторые строки на листе (вручную или группировкой), то может возникнуть желание подсчитывать итоги только по оставшимся (видимым) строчкам. В такой ситуации может помочь либо классическая функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTALS), либо ее более совершенный преемник в последних версиях Excel - функция АГРЕГАТ (AGGREGATE):

Марафон "30 дней = 30 функций" #16: Поиск последнего значения в строке-столбце функцией ПРОСМОТР (LOOKUP)

Разбор применения редкой функции ПРОСМОТР (LOOKUP) для решения задачи поиска последней занятой ячейки в строке или столбце (по тексту и числам):

Марафон "30 дней = 30 функций" #15: Создание внешних и внутренних ссылок функцией ГИПЕРССЫЛКА (HYPERLINK)

При внешней простоте, функция ГИПЕРССЫЛКА (HYPERLINK) на практике оказывается весьма полезной и позволяет создавать ссылки на всевозможные объекты:
  • ячейки и диапазоны внутри текущей книги
  • внешние файлы, расположенные на жестком диске или сервере
  • именованные диапазоны
  • фрагменты умных таблиц
  • веб-страницы
  • заполненные бланки электронной почты
Все варианты применения - в очередной статье нашего марафона - просвещайтесь ;)

Марафон "30 дней = 30 функций" #14: Зачем нужна функция ВЫБОР (CHOOSE)

Это одна из тех функций, про которые, при первом знакомстве, обычно говорят "и что?". На самом деле функция ВЫБОР (CHOOSE) умеет не только выбирать заданный элемент из набора по номеру, а гораздо больше:
  • выдавать ссылку на диапазон из набора
  • склеивать диапазоны прямо в формуле в любом порядке
  • работать с вложенными функциями

Марафон "30 дней = 30 функций" #13: Превращение строк в столбцы и обратно функцией ТРАНСП (TRANSPOSE)

Если нужно конвертировать вертикальный диапазон в горизонтальный или наоборот, то есть много способов, кроме очевидной специальной вставки. Один из них - использовать функцию ТРАНСП (TRANSPOSE), введя ее как формулу массива.

Страницы: 1 | 2 | 3 | 4 | 5 | ... | 21 | След.