• Архив

    «   Декабрь 2018   »
    Пн Вт Ср Чт Пт Сб Вс
              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            

Новая статья: Запуск макроса по времени

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

Реализовать такое можно двумя способами:
  • макросом, используя метод OnTime из VBA
  • использовать Планировщик Задач (Task Scheduler), встроенный в Windows


У каждого метода есть свои плюсы и минусы. Давайте рассмотрим их подробно.

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

Новая статья: Новые типы данных Акции и География в Excel 2016

С летними обновлениями 2018 года Excel 2016 получил революционно новую возможность добавления в ячейки данных нового типа - биржевой (Stocks) и географической информации (Geography). Соответствующие иконки появились на вкладке Данные (Data) в группе Типы данных (Data types):



Что это такое и с чем это едят? Как это можно использовать в работе? Какая часть этого функционала применима для нашей российской действительности? Давайте разберемся.

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

Новая статья: Как создать свою надстройку в Excel



Запилил большую и подробную статью + аж получасовое видео с пошаговым подробным разбором всего процесса создания своей собственной надстройки в Microsoft Excel. Основные этапы:
  • создание файла надстройки
  • наполнение его макросами и пользовательскими функциями
  • различные способы запуска макросов надстройки
  • создание отдельной вкладки с кнопками для надстройки
Так что теперь вы можете запросто сделать свой вариант PLEX ;)

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

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

Сильно обновил (по факту, практически полностью переписал статью) про 3 способа создания сводной таблицы на основе нескольких диапазонов данных. Стандартными средствами такое Excel не умеет, но, при желании, все-таки можно реализовать с помощью Power Query, макросов или Мастера Сводных таблиц из старых версий Excel:

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

P.S. Забавно, как недоступные или трудоемкие задачи, которые пару лет назад можно было решить только хардкорным программированием на VBA, сейчас легко и изящно решаются с помощью надстройки Power Query. Будущее уже здесь, да :)

Новая статья: Что такое Power Query / Pivot / Map / View / BI

Термины "Power Query", "Power Pivot", "Power BI" и прочие "пауэры" все чаще всплывают в статьях и материалах о Microsoft Excel. По моему опыту, далеко не все ясно представляют себе что скрывается за этими понятиями, как они между собой взаимосвязаны и как могут помочь простому пользователю Excel. Давайте разберемся.


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

Новая статья: Импорт плавающего фрагмента через Power Query в Excel

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




То есть мы заранее не можем с определенностью сказать, начиная с какой строки и сколько именно строк нужно импортировать. А это проблема, т.к. эти параметры жестко прописываются в М-коде запроса. И если сделать запрос по первому файлу (импорт 5 строк начиная с 4-ой), то он уже не будет правильно работать со вторым.
Было бы здорово, если бы наш запрос мог сам определять начало и конец «плавающего» текстового блока для импорта.

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

Новая статья: Нечёткий текстовый поиск с Fuzzy Lookup

Еще с 2011 года для Excel существует крайне полезная (и совершенно бесплатная!) надстройка Fuzzy Lookup от Microsoft, которая умеет искать ближайшие похожие текстовые строки в двух списках, т.е. работает как известная функция ВПР (VLOOKUP), но при неточном совпадении названий:


В некоторых ситуациях (например, при поиске похожих, но не точно совпадающих адресов) эта надстройка совершенно незаменима. Давайте рассмотрим подробнее как она работает.

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

Новая статья: Импорт данных из PDF в Excel

Если вы столкнулись с задачей переноса данных из файла PDF в Microsoft Excel, то у вас есть несколько вариантов. Если вы счастливый обладатель недешевого FineReader, то все проще. А если нет? На самом деле, вполне можно выкрутиться с помощью связки Word + Power Query:


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

Новая статья: Последнее слово

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



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

Новая статья: Личная Книга Макросов в Microsoft Excel

Написал подробную статью о том, как создать и использовать Личную Книгу Макросов - удобное хранилище персональной коллекции макросов, рано или поздно образующейся у каждого уважающего себя пользователя Excel :)



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

Новая статья: Диаграмма Ганта в Power Query

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


... получить вот такую:


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

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

Новая статья: Анализ текста регулярными выражениями (RegExp) в Excel

Если вам часто приходится работать с текстом, анализируя и разбирая его на отдельные фрагменты по заданным шаблонам и правилам, то определенно имеет смысл внедрить в свою работу регулярные выражения (RegExp или, в народе, "регулярки";) - супермощный инструмент обработки текста, давно применяемый в современных языках программирования и текстовых редакторах. Excel не поддерживает регулярные выражения по умолчанию, но это можно легко исправить с помощью простой функции на VBA.

Спектр задач, которые могут решать регулярки - очень широк. Разберем несколько примеров:
  • извлекаем город из адреса
  • номер телефона из описания контакта
  • ИНН из реквизитов контрагента
  • автомобильный номер
  • сумму и номер счета из банковской выписки и т.д.

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

Новая статья: Расчет средневзвешенного значения в Excel (формулами и в сводной)

Ну, что - всех с Наступившим! Надеюсь, что все вы хорошо отдохнули, отъелись (ха-ха) и готовы к новым трудовым подвигам. Хочется начать этот год с чего-нибудь не совсем обычного...

Что такое средневзвешенное значение (weighted average) и чем оно отличается от обычного среднего арифметического? Как рассчитать средневзвешенное в Excel? Формулами - легко, а вот с расчетом средневзвешенного в сводной придется повозиться и привлечь тяжелую артиллерию в виде Power Pivot и DAX.


Для пущей наглядности записал еще и видео вдогон:


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

Новая статья: Импорт курса биткойна в Excel через Power Query

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



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

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

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

Есть много способов сравнить две таблицы в Microsoft Excel и на эту тему я уже неоднократно писал статьи и делал видеоуроки. Теперь же захотелось "подвести черту" и подробно описать три самых любимых мной способа сравнения:
  • функцией ВПР (VLOOKUP) - классика жанра
  • с помощью сводной таблицы - хороший вариант для больших таблиц
  • с помощью надстройки Power Query - самый красивый способ, да еще и с автоматическим обновлением
Разбор вариантов будем делать на двух версиях прайс-листа - старой и новой. Задача: быстро найти отличия, т.е. новые и удаленные товары и товары, у которых изменилась цена:



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

Новая статья: Трансформация столбца в таблицу

Иногда при выгрузке из каких-либо корпоративных программ, данные достаются нам в не очень удобном виде. Один из самых неприятных вариантов - это вся информация в одном длинном столбце, из которого придется делать нормальную двумерную таблицу:


Давайте разберем несколько способов сделать это быстро и красиво, а именно:
  • формулами и, в частности, функцией ДВССЫЛ (INDIRECT)
  • через Power Query
  • макросом из надстройки PLEX
Читать статью полностью

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

Иногда при работе в Microsoft Excel приходится искать самую длинную последовательность значений, удовлетворяющих заданному условию. Например, самую длинную непрерывную цепочку поставок, самую длинную последовательность сделок, рабочих дней в табеле и т.д. В спорте подобную штуку называют еще "победной серией" (winning streak):



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

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

Новая статья: Совместное редактирование и Автосохранение в Excel 2016

На практике часто приходится сталкиваться с ситуациями, когда с одним файлом Excel должны одновременно работать несколько пользователей. В Microsoft Excel всегда была такая возможность, но в прошлых версиях реализована она была слабо. Начиная с Excel 2016 появился новый режим совместного редактирования (Co-Authoring) - более удобный и мощный:


Но обратной стороной медали стало Автосохранение - переключатель которого появился в левом верхнему углу окна на панели быстрого доступа:


Наравне с плюсами, Автосохранение имеет и много неприятных минусов, о которых стоит рассказать подробно.

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

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

Для автоматического выделения ячеек цветом по условию в Excel есть условное форматирование. А как реализовать похожую технику, но для столбцов в диаграмме? Чтобы некоторые столбцы автоматически выделялись цветом при выполнении определенного условия. Например, подсвечивать:
  • все столбцы со значениями выше среднего
  • три самых больших (Top-3)
  • минимальное и максимальное значение
  • столбец, выбранный в выпадающем списке в ячейке листа и т.д.
На самом деле, все очень несложно




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