• Архив

    «   Август 2020   »
    Пн Вт Ср Чт Пт Сб Вс
              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            

Новая статья: Регулярные выражения (RegExp) в Power Query

Пару лет назад я уже делал большую статью с видео о том, как добавить в Microsoft Excel поддержку регулярных выражений (RegExp). Это здорово развязывает нам руки при анализе и парсинге текста и предоставляет в наше распоряжение мощный и гибкий функционал, рядом с которым стандартные текстовые функции Excel и рядом не стояли.
Открытым, однако, остался вопрос - можно ли добавить поддержку регулярных выражений в Power Query? Справка и тех.поддержка Microsoft отвечают на этот вопрос сугубо отрицательно, но, на самом деле, есть способ обойти это ограничение...



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

Новая статья: Обманчивая простота функции ПОСЛЕД (SEQUENCE)



Эта статья является логическим продолжением предыдущего материала про новые динамические массивы (ДМ), появившиеся в Excel в Office 365. Обновление Office 365, которое подарило Microsoft Excel новый  вычислительный движок с поддержкой динамических массивов, также добавило  к нашему арсеналу 7 новых функций, заточенных специально для работы с  массивами. Про три самых важных функции: СОРТ (SORT), ФИЛЬТР (FILTER) и УНИК (UNIQUE) я уже писал, а в этой статье хотел разобрать ещё одну весьма приятную обнову - функцию ПОСЛЕД (SEQUENCE).
Эта функция предназначена для совершенно, на первый взгляд, банальной  задачи - генерации числовых последовательностей (рядов, арифметических  прогрессий) с определённым шагом и от заданного стартового значения. Звучит скучно, но не ведитесь на первое обманчивое впечатление - этой функции есть, чем вас удивить!

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

Новая статья: ВПР и числа-как-текст

Разбор классической проблемы с функцией ВПР (VLOOKUP), которая отказывается находить числовые значения, если они отформатированы как текст:



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

Новая статья: План-факт анализ в сводной с Power Pivot и Power Query

Большой комплексный пример: пошаговый разбор решения задачи план-факт анализа с использованием сводных таблиц, модели данных Power Pivot со связями "многие-ко-многим", простых мер на DAX и допиливании входных данных с помощью Power Query.



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

Новое видео: Выделение дубликатов цветом

Обновил старую статью про выделение дубликатов в списке цветом с помощью условного форматирования и добавил к ней видео с разбором 3 способов:
  • быстрое выделение дубликатов-ячеек
  • правило УФ на основе формулы для выделения всей строки
  • правило УФ с формулой массива(!), когда нет ключевого столбца с уникальными значениями и нужно делать склейку всех значений в строке



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

Новая статья: Нечеткий текстовый поиск с Power Query

Я когда-то уже писал подробный обзор на бесплатную надстройку Fuzzy Lookup от Microsoft, позволяющую находить соответствия двух списков при неточном совпадении данных. Недавно, с последними обновлениями Office 365, аналогичный функционал пришёл и в Power Query в Excel. До Power BI Desktop, кстати, он тоже добрался.
Давайте разберёмся, как этот инструмент работает, его плюсы, минусы и нюансы применения.


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

Новая статья: Самый быстрый ВПР

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

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


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

Новая статья: Функция ПРОСМОТРХ как наследник ВПР

Написал подробную статью про возможности новой функции ПРОСМОТРX (XLOOKUP), недавно появившейся в Excel из Office 365. Эту функцию Microsoft позиционирует, как замену и наследницу для классической ВПР (VLOOKUP). В чем фишки, плюсы и минусы "новой легенды" - мы подробно разберем в этой статье и я расскажу в видео:


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

Новогодние поздравления и игра "Жизнь" в Excel на макросах

Последний в этом году пост и видео хотелось бы сделать не в привычном формате видеоурока. А именно, совместить приятное с полезным и развлечь вас написанием у вас на глазах за 15 минут самой настоящей компьютерной игры на VBA в Excel - известной игры "Жизнь" (LIFE) британского математика Джона Конвея:



И, пользуясь случаем, хотелось бы от всей души поздравить всех пользователей "Планеты" - старожилов, новичков и просто заглянувших сюда людей - с наступающим Новым годом! Позвольте пожелать здоровья вам и вашим близким, а вам профессиональной и личной самореализации, внутренней гармонии и стойкости, интересных задач и быстрых формул (запросов, макросов) в Microsoft Excel. Здесь, на этом сайте вы всегда найдете поддержку среди коллег и единомышленников по работе в этой замечательной программе.

Берегите себя, ведь вы - лучшие!

С Новым годом!

Обновление статьи: Выпадающий список с пополнением

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



Просвещайтесь!

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

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

Новые статьи про динамические массивы в Excel

Выложил две статьи (обе с видео) про революционное, прямо скажем, обновление вычислительного движка Excel, добавляющее поддержку динамических массивов (Dynamic Arrays) и новые функции для работы с ними:
Это очень-очень круто и даже немного жаль, что таких функций не было лет 10 назад - сколько времени можно было бы сэкономить! :)

Новая статья: Как открыть новый Excel в отдельном окне

Приходилось ли вам когда-нибудь по несколько минут ждать пока в вашей книге Excel отработает макрос, обновится запрос Power Query или пересчитаются тяжелые формулы? Можно, конечно, заполнить случившуюся паузу чайком-кофейком на вполне законных основаниях, но наверняка вам приходила в голову и другая мысль: а не открыть ли рядом другую книгу Excel и не не поработать ли пока с ней?



Есть несколько способов это сделать в зависимости от вашей версии Excel и установленных обновлений:
  • В лоб
  • Мышью
  • Макросом
  • Командной строкой
  • Файлом сценария VBScript
Читать статью полностью

Новое видео: 7 способов преобразовать формулы в значения

Обновил старую, но не теряющую актуальность статью про конвертацию формул в значения. Дописал пару способов + запилил короткое видео:


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

Новая статья: Суперсила Мгновенного заполнения (Flash Fill)

Хотя инструмент Мгновенное заполнение (Flash Fill) появился в Excel ещё с 2013-й версии, но почему-то для многих пользователей этот факт остался незамеченным. И совершенно напрасно. Во многих случаях он оказывается проще, легче и быстрее, чем аналогичные решения на формулах или макросах. По моему опыту, на тренингах эта тема вызывает постоянное "вау!" аудитории - независимо от продвинутости и/или усталости слушателей.

Механизм работы этого инструмента прост: если у вас есть один или несколько столбцов с исходными данными и вы начинаете набирать рядом в соседнем столбце их же, но в каком-либо нужном вам измененном виде, то Excel рано или поздно намекнёт, что готов продолжить дальше за вас:



Чтобы выявить логику (шаблон, pattern) преоборазования и запустить эту функцию Excel обычно хватает ввода 1-3 первых результирующих значений вручную. Если предложенный вариант вам подходит, то достаточно нажать Enter - и остаток списка будет доделан моментально.
С помощью Мгновенного заполнения можно:
  • резать и клеить текст
  • менять местами слова
  • исправлять регистр
  • выдергивать числа из буквенно-цифровой каши
  • превращать числа-как-текст в нормальные числа
  • ... и многое другое.
Давайте рассмотрим несколько примеров использования этого инструмента на практике, чтобы понять его возможности.

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

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

Классика жанра - вопрос из серии "как мне собрать таблицы со всех листов моей книги на один?". Представьте, что у вас есть файл с больше, чем полусотней листов-отчетов по городам. Всё это счастье нужно собрать в одну таблицу для построения в будущем, допустим, сводной. Выручить могут два основных подхода: макросы и Power Query.

Давайте рассмотрим их подробно:


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

Новая статья: Генератор фраз из заданных фрагментов

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


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

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

Новое видео: Заполнение пустых ячеек соседними значениями

Записал и выложил новое видео к старой статье по заполнению пустых ячеек соседними значениями. Разобрал все основные способы: формулы, макросы, с помощью Power Query и даже PLEX.


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

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

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


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

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

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

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


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