Запилил большую и подробную статью + аж получасовое видео с пошаговым подробным разбором всего процесса создания своей собственной надстройки в Microsoft Excel. Основные этапы:
создание файла надстройки
наполнение его макросами и пользовательскими функциями
различные способы запуска макросов надстройки
создание отдельной вкладки с кнопками для надстройки
Так что теперь вы можете запросто сделать свой вариант PLEX
Для тех, кто не в теме (если такие ещё остались): 1 сентября в России впервые выступал коуч, тренер, спикер и бизнесмен Тони Роббинс. За последние 30 лет он работал с президентами, звездами спорта, известными актерами, телеведущими, бизнесменами всех уровней и т.д. Владеет полсотней компаний, написал кучу книг, не его семинарах побывало больше 3 млн. человек. Выступление собрало больше 26 тыс. зрителей в "Олимпийском" в Москве.
Я, откровенно говоря, не часто хожу на бизнес-форумы, но такое пропустить не мог, потому как:
я сам тренер и шанс посмотреть на работу специалиста такого уровня пропускать нельзя, учиться у других тренеров - это святое
читал его книги и смотрел его видеоуроки на YouTube и на TED - понравилось, многое резонирует, хотелось увидеть как это всё происходит вживую
После семинара народ в интернетах реагировал по-разному: спектр разнится от восхищения до лютой ненависти. Даже приличные сайты типа Forbes, Esquire и VC не погнушались погреть на этой теме руки, выкатив желтоватые статейки, а уж тролли всех мастей на ТВ и в соцсетях рвали гармони по-полной. Причем подавляющее большинство хейтеров на самом мероприятии даже не были, о чем откровенно и заявляют. Вспоминается легендарное: "Давайте спорить о вкусе устриц с теми, кто их ел" (с) Жванецкий
Знакомые спрашивают про мои впечатления от посещения. Специально попробую сформулировать не по горячим следам, а спустя несколько дней, когда "муть осела".
1. Организация, конечно, подкачала (мягко выражаясь). Я был в "Олимпийском" на концертах на ДДТ и Deep Purple. Оба раза был полный зал, но такого бардака на входе не видел никогда. Два часа стоять на жаре в две очереди (на регистрацию и потом еще на вход) - это нечто. У кого-то из соседей не было устройств перевода, кому-то их дали, но без батареек. В довершение всего выяснилось, что моё место в зале уже занято - нам обоим продали билеты на одно место! Причем таких людей было достаточно много и их организаторы, в итоге, просто рассаживали на любые соседние свободные места. Одним словом, колхоз.
2. Выступающий на разогреве Алан Пиз весьма порадовал. Бодрый дядька, чью книжку "Язык телодвижений", изданную каким-то левым издательством на туалетной бумаге, я прочитал, наверное, еще лет в 15 и частично помню до сих пор. Выступил коротко, интересно и с юмором, рассказав о своих принципах создания списков личных целей на примерах из своей жизни. Живая легенда.
3. А потом вышел Тони. Ну, что сказать? Надо честно признать: харизма и энергетика у него, конечно, термоядерные. И всё это помножено на знание НЛП, отлично поставленную речь, жестикуляцию и врожденный талант актера и психолога. И мастерски приправлено подаваемыми в нужные моменты светом и музыкой. Убийственное сочетание - проймет и мертвого. Начинаешь понимать, почему некоторые ездят за ним по всему миру, посещая каждый его семинар. Сразу вспомнились многочисленные убогие отечественные подражатели-коучи-самоучки из разных "б.молодостей" - повторить такое нереально, даже если тупо копировать всё слово-в-слово. Такой уровень энергии и харизму не скопируешь. 4 часа зал слушал, записывал, прыгал, делал упражнения, орал во весь голос и ни о еде, ни о туалете, такое ощущение, никто и не вспоминал.
4. Что касается самой программы семинара и его смыслового наполнения, то КПД тут, конечно, весьма скромный и некое разочарование имело место. Однако, подумав, я пришел к мысли, что ожидать чего-то большего было наивно. В Штатах Тони ведет семинар 4-5 дней с утра и до позднего вечера. Все участники разбиваются на группы по 10-15 человек и работают под руководством специальных инструкторов из его команды. Плюс еще и домашние задания получают каждый день. Это совершенно другой уровень проработки материала и совершенно другая эффективность. А тут - всего 4 часа и 26 тыс. человек без всяких помощников, да еще и с синхропереводом. Так что были с десяток слайдов, пара-тройка простых упражнений в малых группах и много движения, чтобы держать всех в тонусе. После прочитанных его книжек и просмотренных видео, я ничего революционно нового для себя не открыл и никаких качественных прорывов не заметил, честно говоря. Но посмотреть на всё это вживую было интересно, да.
А что касается токсичных статеек и хайпа, раздутого на всем этом в соцсетях, то думаю, что многие ждали волшебной таблетки. Чтобы р-р-рр-аз - и все! И сразу счастье, много денег и нет лишнего веса. Так не бывает. Для настоящих перемен в жизни нужна воля и постоянная работа над собой, а не селфи с Тони на заднем плане.
Выпустил новую версию моей надстройки PLEX для Microsoft Excel - третье обновление в этом году (v.2018.3) Исправлено несколько ошибок и по-мелочи допилены некоторые моменты, но главное изменение коснулось системы лицензирования. Если вы уже используете надстройку или собирались её приобрести в ближайшее время - обязательно прочитайте этот текст до конца.
Новая система лицензирования Раньше у надстройки PLEX было две версии - полная платная, со всеми последними фишками и инструментами и без каких-либо ограничений и бесплатная демо-версия, отстающая от полной на 1.5-2 года по возможностям, с закрытыми исходными кодами макросов и ограниченная 500 запусками. В этом было два отрицательных момента.
Во-первых, устаревшая демо-версия не показывала все последние возможности надстройки, т.е. не выполняла свою главную функцию. А во-вторых, открытый код и отсутствие системы защиты привело к тому, что единожды купленную надстройку безлимитно копировали на десятки ПК. Некоторые особо талантливые даже умудрялись перепродавать её от своего имени, да ещё и с подсаженными внутрь "троянцами" в качестве бонуса.
Так что теперь, начиная с версии PLEX v.2018.3, ситуация меняется: будет единая самая последняя версия PLEX с максимальным функционалом и закрытыми исходными кодами макросов, которая при первой установке на компьютер сначала работает в бесплатном триальном режиме 30 дней, а затем работа надстройки прекращается, и её надо либо отключить, либо ввести лицензионный ключ и активировать полную версию в появившемся окне:
Для активации необходим доступ в интернет. При желании, можно не ждать окончания триального периода и активировать полную версию досрочно, используя на вкладке PLEX команду О программе ( About):
Единожды активированная надстройка больше не будет спрашивать ключ и будет пожизненно работать на данном компьютере, в т.ч. после установки новых версий PLEX. Проверить текущее состояние лицензии и версию можно всё там же – в окне О программе ( About). Разрешается производить до 5 активаций на каждого пользователя. Где получить лицензионный ключ Если вы уже покупали полную версию надстройки ранее, то ваш лицензионный ключ всегда можно найти на сайте в разделе PLEX по ссылке (если зайти на сайт под вашим логином-паролем). Новым покупателям лицензионный ключ также будет продублирован по почте в письме после покупки.
Как теперь обновлять надстройку Так же, как и раньше, только скачать последнюю версию надстройки теперь может любой (даже без регистрации на сайте) по ссылке в разделе PLEX. А дальше просто заменяете все файлы из папки PLEX на файлы из распакованного скачанного архива - и все. Повторно активировать лицензионный ключ (на данном компьютере) уже не потребуется.
Сильно обновил (по факту, практически полностью переписал статью) про . Стандартными средствами такое Excel не умеет, но, при желании, все-таки можно реализовать с помощью Power Query, макросов или Мастера Сводных таблиц из старых версий Excel:
P.S. Забавно, как недоступные или трудоемкие задачи, которые пару лет назад можно было решить только хардкорным программированием на VBA, сейчас легко и изящно решаются с помощью надстройки Power Query. Будущее уже здесь, да
Термины "Power Query", "Power Pivot", "Power BI" и прочие "пауэры" все чаще всплывают в статьях и материалах о Microsoft Excel. По моему опыту, далеко не все ясно представляют себе что скрывается за этими понятиями, как они между собой взаимосвязаны и как могут помочь простому пользователю Excel. Давайте разберемся.
Иногда бывают ситуации, когда заранее неизвестно сколько именно и каких строк нужно импортировать из исходных данных. Допустим, мы должны загрузить в Power Query данные из текстового файла, что, на первый взгляд, не представляет большой проблемы. Сложность в том, что файл регулярно обновляется, и завтра в нем может быть другое количество строк с данными, шапка из трех, а не двух строк и т.д.:
То есть мы заранее не можем с определенностью сказать, начиная с какой строки и сколько именно строк нужно импортировать. А это проблема, т.к. эти параметры жестко прописываются в М-коде запроса. И если сделать запрос по первому файлу (импорт 5 строк начиная с 4-ой), то он уже не будет правильно работать со вторым. Было бы здорово, если бы наш запрос мог сам определять начало и конец «плавающего» текстового блока для импорта.
Еще с 2011 года для Excel существует крайне полезная (и совершенно бесплатная!) надстройка Fuzzy Lookup от Microsoft, которая умеет искать ближайшие похожие текстовые строки в двух списках, т.е. работает как известная функция ВПР (VLOOKUP), но при неточном совпадении названий:
В некоторых ситуациях (например, при поиске похожих, но не точно совпадающих адресов) эта надстройка совершенно незаменима. Давайте рассмотрим подробнее как она работает.
Досрочно (спасибо ЦБ РФ) выпустил второе обновление своей надстройки PLEX в этом году. Что изменилось:
Автогруппировка из столбцов
В выпадающий список Трансформация добавлен инструмент Автогруппировка - для автоматического создания многоуровневой иерархической группировки строк по исходному положению подписей в столбцах:
Обратите внимание, что последний столбец подписей (уровень 4) выделять не нужно, иначе получим лишний уровень группировки. Также, любые группировки на листе всегда можно легко удалить с помощью команды Разгруппировать – Удалить структуру с вкладки Данные (Data – Ungroup – Clear Outline).
Разгруппировать в столбцы
Инструмент для обратного преобразования (аналогично предыдущему) – по имеющейся многоуровневой структуре группировки раскладывает подписи в разные столбцы:
Перехват ошибок в формулах
В группе Формулы появился инструмент для быстрого добавления ко всем формулам в выделенном диапазоне стандартной Excel’евской функции перехвата ошибок ЕСЛИОШИБКА (IFERROR):
После его применения вокруг текущих формул будет аккуратно дописана эта полезная функция, заменяющая любые ошибки (#ЗНАЧ, #Н/Д, #ДЕЛ/0 и т.д.) на указанное желаемое значение:
Если оставить поле ввода пустым, то ошибки будут заменены на “” (пустые кавычки), т.е. ячейки с ошибками будут выглядеть как пустые.
Функции проверки правильности ИНН, ОГРН и СНИЛС
Добавлены пользовательские функции для проверки в Excel правильности ИНН (для юрлиц и физлиц), ОГРН (для ООО и ИП) и СНИЛС. Проверяют контрольные числа и суммы по официальным алгоритмам и выдают на выходе логическую ИСТИНУ или ЛОЖЬ:
Должны пригодиться кадровикам и не только. Как обычно, ввести эти функции можно либо напрямую вручную в ячейки, либо воспользоваться кнопкой Библиотека Формул с вкладки PLEX.
Исправления ошибок
Как всегда, обновление содержит общую оптимизацию кода и исправление обнаруженных ошибок, в частности:
Исправлена ошибка в инструменте Курс валют и функции CBR, связанная с изменением адреса и редизайном сайта ЦБ РФ.
Исправлена ошибка вывода диалоговых окон PLEX за пределы рабочей области монитора, возникающая при подключении второго экрана или проектора.
Убрано хранение параметров надстройки в реестре, чтобы уменьшить вероятность ложного срабатывания некоторых антивирусов на PLEX.
Исправлена ошибка Менеджера Листов, возникающая в том случае, если в текущем файле нет ни одного листа.
Исправлена ошибка инструмента Очистить, связанная с удалением примечаний.
Как всегда, для всех уже купивших надстройку - обновление бесплатно. Заходите на и качайте по желтой ссылке в правом верхнем углу страницы.
Если вы столкнулись с задачей переноса данных из файла PDF в Microsoft Excel, то у вас есть несколько вариантов. Если вы счастливый обладатель недешевого FineReader, то все проще. А если нет? На самом деле, вполне можно выкрутиться с помощью связки Word + Power Query:
Выпустил первое в этом году обновление для своей надстройки PLEX для Microsoft Excel. Что изменилось: Макросы для работы с файлами Добавлен набор инструментов для работы с открытыми книгами – выпадающий список Файлы:
В набор входят следующие макросы:
Сохранить все – сохраняет все открытые книги одним движением. Будет полезно, если, например, только что в комнату, где вы работаете, вбежал человек со словами "Пожарная тревога! Сейчас отключат электричество!" Ну, или вы торопитесь куда-нибудь.
Закрыть все без сохранения – закрывает все открытые книги без сохранения.
Закрыть все сохраненные – закрывает только те книги, которые не имеют несохраненных изменений.
Заново открыть эту книгу – закрывает текущую книгу без сохранения и открывает ее заново. Очень полезно, если вы во время работы увлеклись и настолько накосячили в файле, что уже проще начать все заново
Текущая папка – открывает в Проводнике папку, где находится текущая рабочая книга.
Поддержка регулярных выражений Добавлена пользовательская функция для работы с регулярными выражениями RegExpExtract в следующем синтаксисе: =RegExpExtract(Txt; Pattern; Item; Delimiter; CaseSensitive) где
Txt – ячейка с текстом, из которого нужно извлечь заданную подстроку
Pattern – шаблон на основе регулярных выражений (подробнее о языке регулярных выражений можно
Item – порядковый номер извлекаемой подстроки. Для извлечения первой встречной подходящей под шаблон подстроки используйте 1, для второй 2 и т.д. Для извлечения последней используйте -1, второй с конца -2 и т.д. Если Item=0, то будет извлечен весь массив найденных подстрок через заданный символ-разделитель (см. следующий аргумент). По умолчанию этот аргумент принимается равным 1.
Delimiter – символ-разделитель в случае, если извлекается весь набор найденных подстрок (по умолчанию – запятая)
CaseSensitive – нужно (1) или нет (0) учитывать регистр при поиске по шаблону. По умолчанию – 0.
Функция запроса курсов с сайта НБУ Аналогично уже имеющейся функции CBR к сайту ЦБ России, реализована функция запроса курса заданной валюты на заданную дату с сайта Нац.Банка Украины. =NBU(InputDate; Money) где
InputDate – дата, на которую необходимо получить курс
Money – стандартный банковский код валюты (“EUR”, “USD”, “RUB” и т.д.) По умолчанию – “USD”
Прочие улучшения и доработки
Мини-калькулятор теперь работает с учетом отфильтрованных ячеек.
Уровни вложенности функций для инструмента Авто-отступы увеличены с 10 до 50.
В инструмент Случайно добавлена возможность создания списка названий штатов США.
Инструмент Сортировка по цвету теперь умеет работать с текстом, где символы имеют разное начертание или цвет внутри одной ячейки.
Появилась возможность добавлять свой префикс и суффикс к имени файла при сохранении листов как отдельных файлов в инструменте Сохранить как файлы из группы Листы.
В инструменте Сборка листов появилась возможность быстро добавлять все файлы из текущей папки и задавать желаемые имена для собираемых листов:
Плюс, само-собой, исправление ошибок и общая оптимизация кода и дизайна по-мелочи.
Как всегда, все купившие надстройку, могут смело по желтой ссылке в верхней части страницы. Обо всех обнаруженных ошибках и глюках можно смело писать здесь же в комментариях, в или мне на почту.
Среди задач работы с текстом, которые мы неоднократно уже разбирали, немного особняком стоит вопрос извлечения из текста последнего слова или фрагмента по заданному разделителю. Вытащить первое слово - не проблема, а вот с последним все не так легко и очевидно, т.к. количество слов в каждой ячейке может различаться. Давайте разберем несколько принципиально разных способов это сделать, а именно:
Написал подробную статью о том, как создать и использовать Личную Книгу Макросов - удобное хранилище персональной коллекции макросов, рано или поздно образующейся у каждого уважающего себя пользователя Excel
Эта статья родилась после вопроса одного из моих слушателей с тренинга "". Вопрос был о том, как из вот такой таблицы по проектам:
... получить вот такую:
Т.е. "размазать" бюджеты по дням для каждого проекта, получив на выходе что-то похожее на диаграмму Ганта. Формулами такое делать скучно, макросами - сложно, вручную - лениво, а вот бесплатная надстройка Power Query для Excel в такой ситуации как раз блещет всей своей мощью и красотой.
Если вам часто приходится работать с текстом, анализируя и разбирая его на отдельные фрагменты по заданным шаблонам и правилам, то определенно имеет смысл внедрить в свою работу регулярные выражения (RegExp или, в народе, "регулярки";) - супермощный инструмент обработки текста, давно применяемый в современных языках программирования и текстовых редакторах. Excel не поддерживает регулярные выражения по умолчанию, но это можно легко исправить с помощью простой функции на VBA.
Спектр задач, которые могут решать регулярки - очень широк. Разберем несколько примеров:
Ну, что - всех с Наступившим! Надеюсь, что все вы хорошо отдохнули, отъелись (ха-ха) и готовы к новым трудовым подвигам. Хочется начать этот год с чего-нибудь не совсем обычного...
Что такое средневзвешенное значение (weighted average) и чем оно отличается от обычного среднего арифметического? Как рассчитать средневзвешенное в Excel? Формулами - легко, а вот с расчетом средневзвешенного в сводной придется повозиться и привлечь тяжелую артиллерию в виде Power Pivot и DAX.
Друзья, коллеги, форумчане, постоянные и случайные гости сайта! Позвольте поздравить вас с Наступающим Новым Годом и пожелать:
Здоровья вам и вашим близким - это самое главное. Заботьтесь о своем теле, кормите его правильной едой, гоняйте на спорт. Помните - оно у вас одно
Взаимопонимания и терпения, особенно при общении с идиотами. И поменьше этих самых идитов вокруг, а побольше - хороших людей. Помните, что вы - среднее арифметическое тех людей, которыми себя окружаете.
Стойкости и смелости в своих решениях. Вы не обязаны никому нравиться и не перед кем (кроме себя) отчитываться. Ваша жизнь - ваши правила. Оставайтесь собой, развивайтесь и жмите вперед.
Не терять фокус. Наметьте цели на год, декомпозируйте их на отдельные мелкие задачи и держите все это перед глазами. За дневной текучкой легко потерять истинные ориентиры и потратить время и энергию на рутину - не дайте этому произойти.
А чтобы было проще планировать и реализовывать свои планы - вот вам в подарок традиционный календарь в Excel на 2018 год с праздниками, выходными днями, нормами рабочих часов и списком задач:
Добавил в него Календарик-Пинарик - шикарный инструмент для личной мотивации от . Попробуйте - вам понравится
Ну что, пора подводить итоги года по традиции? Дабы не терять ориентиров и отслеживать динамику.
Тренинги
В 2017 году мы с коллегами провели 1402 академических часов тренингов (это примерно 175 тренинговых дней) = 44 корпоративных тренинга провел я лично + 29 тренинга коллеги. Обучили, в общей сложности, порядка 1100 человек из 33 компаний и организаций.
Съездили с коллегами на выездные сессии в Питер, Белгород, Астану, Алматы, Нижний Новгород, Киров, Воронеж, Шую, Гагарин, Махачкалу, Ульяновск
Две трети заказов от старых клиентов - и это здорово: Национальный Рекламный Альянс, Мегафон, Takeda, Egger, Globus, НижФарм (Stada), ВТБ, Pirelli, Renova, Xylem, Газпромнефть, Casio, Yokogawa, KIA, РосАтом...
Подружились с несколькими новыми компаниями: Brenntag, Flatglass, Geberit, Камеди Клаб, Белая Птица, Меркурий, Банк СОЮЗ, Tele2, Bridgestone, EDC Eurasia Management, Альфа-Банк, Фасад-Сервис, CNH Indastrial, ГК ВИК, Илим Групп, Абипродукт, ВерхнеЛенское речное пароходство... прошу простить, если кого не упомянул.
Собрал и провел 14 открытых тренингов для всех желающих, где обучил еще 198 человек (физлиц и одиночек от компаний). уже есть.
Новые курсы
Запустил два новых тренинга - "Скульптор Данных в Excel с Power Query" и продолжение тренинга по макросам "VBA Pro: Профессиональная разработка на VBA в Excel". В следующем году увеличу первый из них до 2 дней, т.к. материала и плюшек по Power Query оказалось сильно больше, чем умещается в один день
Почти готов однодневный тренинг по анализу данных в Excel с Power Pivot и DAX - планирую провести его . В разработке тренинг по красотам Power BI - будет логичным продолжением цепочки Excel + Power Query + Power Pivot.
Консалтинг и разработка
Выполнили с коллегами 2 проекта по консалтингу (дизайн презентаций) и два по разработке и автоматизации на VBA.
Впервые в своей практике сделал проект на чистом Power Query без VBA - кайф!
Сайт
В 2017 году на "Планете" побывало 5.1 млн уникальных пользователей, просмотревших 18 млн. страниц, т.е. чуть больше полумиллиона пользователей в месяц.
На моем канале YouTube перешагнул 43 тыс. подписчиков и 6 млн. просмотров. Ужас какой
Написал 17 новых статей (большинство из них с видео) в . Также теперь наравне с классическими формулами и макросами, стараюсь везде, где можно показывать решение проблем с помощью Power Query - за ним будущее, однозначно.
Провел , где ежедневно (почти) выкладывал новую статью с подробным описанием и примерами тридцати самых нужных функций Excel.
Надстройка PLEX для Excel 2007-2016
Выпустил 4 обновления своей надстройки в этом году.
Наконец-то перевел весь интерфейс на английский, так что теперь есть оба варианта.
Книги
Вышла в печать и в электронном виде моя вторя книга для тех, кто перерос ВПР.
Первая книжка , как ни странно, до сих пор продается. Буду готовить в этом году обновление и второе издание, т.к. за последние пару лет много что поменялось в версиях - есть, что добавить
Вот как-то так. На следующий год уже планов громадьё, корпоративными заказами забит до мая под завязку.
Как просчитать прибыльность инвестиционного портфеля криптовалюты, загрузив в Excel с помощью Power Query обновляемый курс покупки с сайта обменника, биржи или торговой площадки. Посвящяется тем 100500 человекам, что за последние пару месяцев написали мне в почту или в личку на форуме этот вопрос
Ничего особо сложного или выдающегося тут нет - для Power Query это "семечки", делается за пару минут. Кроме того, подобным образом можно импортировать в Excel курс не только биткойна, но и любой другой валюты - принцип совершенно аналогичный.
Выпустил очередное, четвертое в этом году обновление своей надстройки PLEX для Microsoft Excel. Что нового?
Мини-калькулятор Добавлен мини-калькулятор для выполнения простых арифметических действий над выделенными ячейками «на лету» без формул:
Выделяете ячейки (можно несколько несмежных диапазонов, удерживая Ctrl), выбираете нужно математическое действие, вводите число и жмете кнопку со знаком «равно». Умеет складывать, вычитать, умножать, делить и прибавлять или вычитать N-е кол-во процентов:
Удобно для быстрого подсчета НДС, скидок, наценок, деления на 1000 и т.п. Кроме чисел в поле ввода также поддерживаются несложные математические формулы и выражения со знаками +, -, *, /, например:
Если в исходных ячейках не числа-константы, а формулы, то при выполнении математической операции они сохраняются и к ним добавляется соответствующий фрагмент. Так после умножения ячейки с функцией суммирования, например, на 2 получится:
Автоотступы в сложных формулах Добавлена возможность делать автоматические переносы строк и отступы в длинных и сложных формулах – это позволяет наглядно отображать аргументы, вложенность функций и сильно облегчает процесс отладки и поиска ошибок в тяжелых вычислениях, формулах массива и т.д.
Так, например, выглядит сложная формула до применения авто-отступов: А так – после:
Умеет работать как с обычными, так и с формулами массива, поддерживает «умные таблицы». Разделение объединенных ячеек с сохранением текста В пару к уже имеющемуся в PLEX макросу объединения ячеек с сохранением текста, добавился аналогичный инструмент для разделения объединенных ячеек с последующим дублированием текста в каждой получившейся отдельной ячейке:
Просто выделяете диапазон с объединенными ячейками:
... затем выполняете команду Разделить (Unmerge) и получаете:
Создание листов с именами из ячеек по шаблону В инструмент создания листов с именами из выделенных ячеек добавлена возможность создавать не только пустые листы, но и использовать при создании копию имеющегося лист как шаблон, на основе которого создаются новые листы:
Умное автозаполнение вниз и вправо В группу Формулы добавлены кнопки для макросов У много автозаполнения вниз и вправо:
По сути, это более совершенный и удобный вариант "протягивания" формул или значений двойным щелчком по черному крестику в правом нижнем углу ячейки. Но, в отличие, от классического автозаполнения, этот инструмент:
позволяет протягивать формулы и значения не только вниз, но и вправо до упора
не «спотыкается» на пустых и заполненных ранее ячейках
умеет тянуть сразу несколько диапазонов (выделенных с Ctrl)
Дата прописью Добавлена пользовательская макрофункция для вывода даты прописью PropisDate:
Синтаксис предельно простой:
первый аргумент - ячейка с любой корректной датой (начиная с 01.01.1900 до 31.12.2099)
второй необязательный аргумент =1 - надо ли выводить дату в родительном, а не именительном падеже
Новые функции для нарезки текста В Excel давно есть стандартные функции для извлечения из текста нужного количества символов – ЛЕВСИМВ (LEFT), ПРАВСИМВ (RIGHT) и ПСТР (MID). Однако, использовать их бывает не всегда удобно, а для подсчета количества извлекаемых символов часто приходится еще вкладывать в них функцию поиска и т.п. Для облегчения жизни в подобных ситуациях в PLEX 2017.4 добавлены три новых макро-функции для извлечения подстрок из текста:
TextLeft – выдает все символы от начала текста до заданного разделителя
TextRight – выдает все символы от конца до заданного разделителя
TextMid – выдает все символы между двумя заданными разделителями
У всех функций можно вводить порядковый номер вхождения разделителей (если не указано, то подразумевается 1) и, при необходимости, учитывать регистр. Прочие доработки и изменения
В инструмент Текст добавлен универсальный вариант Авто для преобразования чисел-как-текст в полноценные числа.
Во все диалоговые окна добавлены оранжевые круглые кнопки с вопросительным знаком для вызова соответствующих разделов онлайн-справки и получения подробной информации по каждому инструменту надстройки.
Инструмент Редизайн кросс-таблицы в плоскую переехал из меню Сводная таблица в выпадающий список Трансформация:
Плюс, как всегда, множество мелких доработок, исправлений ошибок, оптимизация кода и т.д. Купившие полную версию, как и всегда, качают обнову бесплатно по желтой ссылке в заголовке на . Английская версия там же - в архиве.
Есть много способов сравнить две таблицы в Microsoft Excel и на эту тему я уже неоднократно писал статьи и делал видеоуроки. Теперь же захотелось "подвести черту" и подробно описать три самых любимых мной способа сравнения:
функцией ВПР (VLOOKUP) - классика жанра
с помощью сводной таблицы - хороший вариант для больших таблиц
с помощью надстройки Power Query - самый красивый способ, да еще и с автоматическим обновлением
Разбор вариантов будем делать на двух версиях прайс-листа - старой и новой. Задача: быстро найти отличия, т.е. новые и удаленные товары и товары, у которых изменилась цена:
Иногда при выгрузке из каких-либо корпоративных программ, данные достаются нам в не очень удобном виде. Один из самых неприятных вариантов - это вся информация в одном длинном столбце, из которого придется делать нормальную двумерную таблицу:
Давайте разберем несколько способов сделать это быстро и красиво, а именно:
формулами и, в частности, функцией ДВССЫЛ (INDIRECT)
Иногда при работе в Microsoft Excel приходится искать самую длинную последовательность значений, удовлетворяющих заданному условию. Например, самую длинную непрерывную цепочку поставок, самую длинную последовательность сделок, рабочих дней в табеле и т.д. В спорте подобную штуку называют еще "победной серией" (winning streak):
Решить проблему можно с помощью изящной формулы массива, но чтобы разобраться как именно и почему она работает, придется зайти немного издалека...
На практике часто приходится сталкиваться с ситуациями, когда с одним файлом Excel должны одновременно работать несколько пользователей. В Microsoft Excel всегда была такая возможность, но в прошлых версиях реализована она была слабо. Начиная с Excel 2016 появился новый режим совместного редактирования (Co-Authoring) - более удобный и мощный:
Но обратной стороной медали стало Автосохранение - переключатель которого появился в левом верхнему углу окна на панели быстрого доступа:
Наравне с плюсами, Автосохранение имеет и много неприятных минусов, о которых стоит рассказать подробно.
Для автоматического выделения ячеек цветом по условию в Excel есть условное форматирование. А как реализовать похожую технику, но для столбцов в диаграмме? Чтобы некоторые столбцы автоматически выделялись цветом при выполнении определенного условия. Например, подсвечивать:
все столбцы со значениями выше среднего
три самых больших (Top-3)
минимальное и максимальное значение
столбец, выбранный в выпадающем списке в ячейке листа и т.д.
В большинстве случаев повторы в наших данных нежелательны и мы с вами стараемся от них избавиться разными способами. Но иногда случается, что дубликаты нужны и полезны, и более того - нам необходимо их создавать!
Сделать это можно двумя основными способами: при помощи макросов и через Power Query.
