• Архив

    «   Март 2024   »
    Пн Вт Ср Чт Пт Сб Вс
            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
                 

Обновление статьи : Количество уникальных значений

Разбираем пять способов получить количество уникальных значений в исходном диапазоне данных:
  • формулой массива
  • макросом на VBA
  • удалением дубликатов
  • простой сводной
  • сводной по Модели Данных


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

Новая статья : Сравнение любых произвольных дат и/или товаров в Power BI

При создании отчётов в Microsoft Power BI часто требуется сравнить текущий год (или месяц) с предыдущим, а какой-то выбранный товар - с его соседями из той же категории. С этим проблем нет - в Power BI есть куча встроенных функций на этот счёт.

Но что делать, если хочется сравнивать произвольно выбранные объекты? Т.е. любой выбранный год - с любым другим произвольно выбранным, а не обязательно предыдущим? Или выбранный товар нужно сравнить с каким-то другим, заданным? А может и то, и другое сразу, т.е. хочется выбирать интересные мне любые 2 года и сравнивать любые товары в них?


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

Новая статья : Мультистраничный веб-запрос в Power Query

Мультистраничный веб-запрос в Power Query - это подход, который позволяет загрузить данные не с одной, а сразу с нескольких веб-страниц, объединив их в единую таблицу. Например, загрузить результаты чемпионатов по гольфу из википедии за несколько лет (где каждый год - отдельная страница). Чтобы провернуть такую штуку, нам придётся пройти следующие этапы:

  1. Создать сначала одиночный веб-запрос к любой странице из интересующего списка
  2. Создать параметр для года и внедрить его в запрос
  3. Преобразовать запрос в функцию (параметр станет её аргументом)
  4. Создать список интересующих нас лет (2010 - 2021) и вызвать созданную функцию, подставив ей список в качестве аргумента
  5. Объединить полученные результаты в единую таблицу за все годы


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

Новая статья: Год-к-году (YoY) в сводной таблице

3 способа сравнить и наглядно вывести год-к-году в сводной таблице: формулами, дополнительным полем или с помощью мер на DAX в Power Pivot.


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

Новая статья: Хитрости сортировки в Power BI

Разбираем нюансы и хитрости сортировки данных в таблицах и диаграммах Microsoft Power BI: простые и многоуровневые сортировки, неявную сортировку, сортировку месяцев и пользовательскую сортировку в нашей собственной логической последовательности.


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

Новая статья: Основы работы в Microsoft Power BI

Если отбросить новомодный ChatGPT, то на втором месте по по количеству хайпа в моем личном рейтинге будет Power BI с его нечеловеческой красоты интерактивными дашбордами. Причем за последние пару лет количество людей интересующихся этой темой (в том числе среди пользователей Excel, которых я часто вижу) - выросло кратно. С одной стороны, это хорошо, ибо по мощи, гибкости, "всеядности" и красоте визуализаций у Power BI сейчас конкурентов практически нет. С другой стороны, у многих людей я до сих пор встречаю заблуждение, что Power BI - это такой навороченный Power Point (может дело в похожем названии?).

В общем, ловите новый большой видеоурок по основам работы в Microsoft Power BI. Если вы с ним уже работаете, то никаких Америк я вам не открою, скорее всего. Но если раньше вы с ним не сталкивались или до сих пор смутно представляете, что это за штука, то - добро пожаловать. Экспертом по аналитике за полчаса я вас, конечно, не сделаю, но все главные принципы и этапы работы мы с вами разберём:
  • Что такое Power BI, из чего он состоит, и зачем он нужен
  • Как загрузить туда и исходные данные и привести их в приличный вид
  • Как и зачем связывать таблицы между собой
  • Как создавать вычисляемые столбцы и меры на встроенном в Power BI языке DAX
  • Как добавить визуализации на ваш дашборд и опубликовать его потом в облако

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

Новое видео: Диаграмма Парето в Excel

Обновил старую статью про создание диаграммы Парето в Microsoft Excel - добавил пару новых способов и видеоурок:


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

Новая статья: 5 примеров использования новой функции IMAGE в Excel

Уже несколько лет в Google Sheets существует функция IMAGE, позволяющая вставлять в ячейки листа картинки по ссылке из интернета. Что, впрочем, вполне естественно, поскольку Google-таблицы изначально заточены под работу онлайн - им сам бог велел такую возможность использовать.

У Excel же ничего подобного долго не было. И вот, наконец, осенью 2022 года Microsoft начала среди добровольцев-тестировщиков из программы Office Insider обкатку своего аналога -  новой функции ИЗОБРАЖЕНИЕ (IMAGE), также позволяющей вставлять по веб-ссылке картинки из интернета прямо в ячейки листа Microsoft Excel. В начале 2023 года эту функцию уже стали потихонечку разливать с обновлениями подписчикам Office 365 и недавно она, наконец, добралась и до меня. А значит я могу вам о ней рассказать на нескольких практических примерах, а именно:

  • Создание штрих и QR-кодов
  • Вставка логотипов компаний по их названию
  • Нестандартные диаграммы
  • Вставка скриншотов веб-страниц
  • Вставка географических карт


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

Новая статья: Искуcственный интеллект ChatGTP при работе в Excel

Думаю, про ChatGPT вам уже, как минимум, слышали, а может даже и попробовали использовать. Вопрос в том, насколько полезен этот ИИ может быть для простого пользователя Excel.

Пробуем переложить на ChatGPT создание формул и макросов на Visual Basic и смотрим, что из этого получится:


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

Новая статья: Поиск отличий в двух текстовых строках

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

  • лишние пробелы
  • использование английских букв похожих на русские и наоборот (русская "эс" и английская "си", например)
  • использование цифр вместо букв (ноль вместо заглавной "О")
  • разные версии тире (короткое, длинное, среднее)
  • неразрывный пробел вместо обычного
  • невидимые символы переноса строки (Alt+Enter)
  • ... и т.д.

Вопрос в том, где именно эти отличия находятся и как их быстро найти в исходных данных?

Самое простое - это сделать за пару минут прямо в Excel своими руками универсальный сравниватель, который будет наглядно показывать в чем именно не совпадают две любые заданные текстовые строки:


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

Новая статья: Диаграмма выполнения плана в Excel

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


Делается достаточно легко и почти не требует подготовительных действий с таблицей.

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

Новая статья: Переключение вычислений в сводной таблице с помощью срезов

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

 

Реализовать такое очень легко - потребуется лишь пара формул на DAX, вспомогательная таблица и сводная по модели данных Power Pivot.

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

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

Как быстро скопировать в Буфер сумму выделенных на листе ячеек. А также не только сумму (среднее, количество и т.д.) и не только
выделенных (только видимых, удовлетворяющих заданным условиям и т.д.)

В новой версии Excel эта возможность встроена, а в старых придётся чуть-чуть пошаманить с макросами, чтобы её имитировать.

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

Новая статья: Как правильно разворачивать таблицы в Power Query

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

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

Новая статья: Симулятор лотереи в Microsoft Excel

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

В этой статье (и видеоуроке) мне хотелось бы ответить на часть этих вопросов. Благо, в  Excel для решения таких задач достаточно инструментов, многие из  которых, кстати, могут пригодиться и в более прозаических рабочих  ситуациях.


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

Новая статья: Столбец из примеров - искусственный интеллект в Power Query

Одно из самых просматриваемых видео у меня на YouTube-канале - это видео про Мгновенное заполнение (Flash Fill) в Microsoft Excel. Суть этого инструмента в том, что если вам надо как-то преобразовать ваши исходные данные, то достаточно просто начать набирать в соседнем с ними столбце тот результат, который вы хотите получить. После нескольких вручную набранных ячеек (обычно хватает 2-3) Excel "поймёт" логику нужных вам преобразований и автоматически продолжит набранное, завершив всю монотонную работу за вас.

Квинтэссенция эффективности. Волшебная кнопка "сделать всё хорошо", которую мы все так любим, правда?

На самом деле, аналог подобного инструмента есть и в Power Query - там он называется Столбец из примеров (Column from Examples). По сути, это маленький встроенный в Power Query искусственный интеллект, который умеет быстро обучаться на ваших данных и затем их преобразовывать.  Давайте детально рассмотрим его возможности на нескольких практических сценариях, чтобы понять, где он может нам с вами пригодиться в реальных задачах.



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

Новая статья: Диаграмма по выделенной ячейке

Хотите, чтобы диаграмма строилась только  по данным из текущей строки, т. е. строки, куда вы поставили активную ячейку? Примерно так:


Реализовать такое очень легко - потребуется лишь две формулы и один крохотный макрос в 3 строки.

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

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

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

 

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

 

Само собой, использовать для этого будем наиболее подходящий инструмент - Power Query:



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

Новая статья: Нарастающие итоги в Excel


Что такое нарастающие итоги (Running Totals) и как вычислить в Microsoft  Excel разными способами:
  • формулами,
  • в сводной таблице,
  • в запросе Power  Query.


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

Новая статья: LAMBDA - новая суперфункция Excel

Подробный разбор новой оберточной суперфункции LAMBDA, недавно добавленной в Excel. Её синтаксис, варианты применения, нюансы и особенности - от простых формул до динамических массивов и рекурсии:


Плюс подробное видео:



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

Новая статья: Сохранение истории обновлений запросов Power Query

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

Мысль не новая и стандартным ответом на неё будет "нет" - Power Query  по умолчанию настроен именно на замену старых данных новыми (что и  требуется в абсолютном большинстве случаев). Однако, если очень хочется,  то можно обойти это ограничение. Причем способ, как вы дальше увидите,  весьма простой.

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

Новая статья: Деление слипшегося текста функцией ФИЛЬТР.XML

Функцию ФИЛЬТР.XML можно использовать не только для импорта XML-данных из интернета - основной  задачи, для которой эта функция, собственно, и предназначена. Есть ещё одно - неожиданное и красивое - применение этой функции для быстрого деления слипшегося текста по ячейкам:


Подробный разбор механизма - в новой статье и видео:



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

Новая статья: Обновляемый курс валют в Excel

Я уже неоднократно разбирал способы импорта данных в Excel из интернета с последующим автоматическим обновлением. В частности:

К этим способам в последних версиях Microsoft Excel теперь можно  добавить ещё один - импорт данных из интернета в формате XML с помощью  встроенных функций ВЕБСЛУЖБА и ФИЛЬТР.XML.

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

Обновление в приемах: Связанные выпадающие списки по неотсортированному справочнику

Дополнительно к уже разобранным способам создания связанных выпадающих списков добавил ещё один - на основе неотсортированных справочников и функции СМЕЩ (OFFSET):



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