Забыли пароль?


Хитрости »
Основные понятия (24)
Сводные таблицы и анализ данных (10)
Графики и диаграммы (5)
Работа с VB проектом (12)
Power BI и Power Query (17)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (65)
Разное (42)
Баги и глюки Excel (4)

Как в PowerQwery обратиться к предыдущей строке


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

  Предыдущая строка в PowerQwery.xlsx (50,3 KiB, 150 скачиваний)


С виду задача достаточно тривиальная и не сложная с точки зрения работы в Excel: сравнить значение ячейки столбца с ячейкой того же столбца, но в строке выше. Возьмем простую таблицу курсов валют:
Таблица
Нужен еще один столбец, в котором в зависимости от сравнения текущей ячейки с предыдущей будет выводить текст Повышение или Понижение, в зависимости от того, стал ли в текущей ячейке курс выше или ниже относительно предыдущей ячейки. В самом Excel такое сравнение делается простой формулой: =B3>B2
Полная формула при этом выглядит так(вписываем в ячейку C3):
=ЕСЛИ(B3>B2;"Повышение";ЕСЛИ(B3<B2;"Понижение";""))
Просто, легко и понятно.
Но как быть, если те же самые действия надо проделать прямо в запросе PowerQwery? К сожалению там нет прямого механизма ссылок на строки. Мы можем обратить к конкретной строке или ячейке в ней таким образом:
Источник{Номер_строки}[Имя_столбца]
Где Источник – это загруженная в запрос таблица. Номер строки указывается в фигурных скобках(отчет идет с 0), а имя столбца – в квадратных. Т.е. если хотим обратиться к 3-ей строке столбца Курс, то записать можно так: Источник{2}[Курс]
Здесь основная проблема в том, что в PowerQwery нет функции, которая бы возвращала для каждой строки её номер. Что-то вроде CurrentRow(ТекущаяСтрока). Но там есть возможность добавления столбца Индекса: Добавить столбец(Add Column) -Столбец индекса(Index Column). Им мы и воспользуемся. Добавляем такой столбец (лучше брать с 0, т.к. в PowerQwery нумерация строк так же начинается с нуля) и назовем его Индекс(если такое название не было дано автоматически):
Power Qwery столбец индекса
Теперь у нас по факту есть индексация строк, которую мы сможет использовать. Чтобы обратиться к предыдущей строке нам необходимо будет взять имя источника из последнего шага запроса (для лучшего понимания я буду использовать обращение Источник) и добавить к нему в фигурных скобках ссылку на индекс строки -1, а в квадратных – имя столбца, значение из которого надо получить:
Источник{[Индекс]-1}[Курс]
Теперь применяем это к нашей ситуации - добавляем новый столбец (Добавить столбец(Add Column) -Настраиваемый столбец(Custom Column)) и прописываем в нем конструкцию:

if [Курс]>Источник{[Индекс]-1}[Курс] then
   "Повышение"
Else "Понижение"

По сути это и есть формула ЕСЛИ: если значение текущей строки курса больше значения предыдущей строки, то возвращаем «Повышение», иначе – «Понижение». Условие уже обрабатывается, за исключением двух но:

  1. Помимо понижения есть еще и равенство. Значит надо добавить еще одну проверку:
    if [Курс]<Источник{[Индекс]-1}[Курс] then
       "Понижение"
    Else null

    Если текущая ячейка меньше вышестоящей, то пишем «Понижение», иначе записываем значение null(это аналог пустой ячейки в Excel в PowerQwery)

  2. В таком виде на первой строке таблицы наш запрос неизбежно выдаст ошибку «Индекс не может быть отрицательным». И хотя ошибка возникнет только на одно первой строке – это все равно не очень приятно, т.к. в итоге запрос может просто не загрузиться(хотя в последних версиях это на загрузку не влияет). Чтобы этого избежать просто добавляем еще одно условие «поверх» уже созданных – проверка на то, что текущий индекс больше 0:
    if [Индекс] > 0 then

В итоге наша "формула" будет выглядеть так:

if [Индекс] > 0 then
    if [Курс]>Источник{[Индекс]-1}[Курс] then 
        "Повышение"
    else
        if [Курс]<Источник{[Индекс]-1}[Курс] then 
            "Понижение"
        else null
else null

Все, можно нажимать Ок. Будет добавлен столбец, в котором «формула» обработает все строки. Останется только удалить столбец индекса, т.к. он нам больше не нужен и выгрузить данные на лист: Главная(Home)Закрыть и загрузить(Close & Load).

Скачать файл с готовым запросом:

  Предыдущая строка в PowerQwery.xlsx (50,3 KiB, 150 скачиваний)



Статья помогла? Поделись ссылкой с друзьями!
  Плейлист   Видеоуроки

Поиск по меткам

Access apple watch Multex Power Query и Power BI VBA управление кодами Бесплатные надстройки Дата и время Записки Надстройки Печать Политика Конфиденциальности Почта Программы Работа с приложениями Разработка приложений Тренинги и вебинары Финансовые Форматирование Функции Excel акции MulTEx ссылки
Поделитесь своим мнением

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


Для оформления сообщений Вы можете использовать следующие тэги:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Тренинги

Заказать
Юридическая информация

Использование материалов сайта

Политика Конфиденциальности

ИП Щербаков Дмитрий Валентинович
ОГРНИП: 318502700083307
ИНН: 504013350772

Наши партнеры

Перейти
Перейти

Счетчики

Рейтинг@Mail.ru Яндекс.Метрика
© 2020 Excel для всех   Войти