Страницы: 1 2 След.
RSS
Как заставить автоматически проставлять формулы в таблице, выгруженную с помощью PowerQuery
 
Всем привет.

Есть таблица данных (в примере это лист "Данные"). Из нее с помощью PowerQuery извлекаем и преображаем то, что нам нужно (в примере это лист "PowerQuery"). Далее мы к выгруженной таблице (та, что на листе "PowerQuery") добавляем столбец (в примере это "№") и прописываем формулу.

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

Вопрос - как это исправить или автоматизировать?

Благодарю за помощь.
Изменено: EvgeniyLFC - 14.01.2019 20:30:39
 
Вот эта настройка активирована?
Вот горшок пустой, он предмет простой...
 
Добрый день!
все обновляется - любые строки и столбцы добавленные в умную таблицы
после добавления данных нажимали - "обновить все" ?
 
PooHkrd, да, активирована.

Anton555, еще раз: в умной таблице на листе "Данные" добавите строчку с маркой и плавкой, нажмите на "обновить все" и увидите, что на листе "PowerQuery" два этих столбца обновились, но третий столбец (тот, который я добавил после выгрузки PQ) не заполняет все ячейки формулами. А делает пропуски. Скинул пример.

Получается на втором листе умная таблица состоит из 3-х столбцов: два - это PQ и еще один добавленный для расчета, но именно он не просчитывается автоматически, если в первых двух столбцам появляются новые строчки.
Как это исправить/настроить?
 
EvgeniyLFC, вон оно что вы делаете. ответ - никак, делайте все средствами PQ, после чего выгружайте
 
Anton555, очень жаль.
Насколько я знаю, в PQ нельзя реализовать функции ВПР и ПОИСКПОЗ с ИНДЕКСом, поэтому приходится брать данные, отбирать/группировать их через PQ и лишь потом делать расчеты с помощью функций.
 
Доброе время суток
Цитата
EvgeniyLFC написал:
в PQ нельзя реализовать функции ВПР и ПОИСКПОЗ с ИНДЕКСом
Вы правы, в Power Query действительно нет таких функций, но это не значит, что его средствами нельзя решать задачи. Осталось выяснить, что собственно вы хотите сделать.
 
Цитата
EvgeniyLFC написал:
Насколько я знаю
ну так спросите или кто-то у вас за это берет деньги? на то он и форум
Цитата
Андрей VG написал:
Power Query действительно нет таких функций
коллега немного лукавит)
более того я почти уверен на 100%, что ваши потребности в расчетах, pq полностью осилит, просто вы, по всей видимости, много чего не знаете...
 
Anton555, Андрей VG, ребята, благодарю за помощь.

Суть проблемы следующая (опишу общую схему):
1. Имеется выгрузка из 1С в Excel данных по заявкам.
2. Далее с помощью с PQ форматирую/отсортировываю/настраиваю таблицу под себя.
3. Далее создаю столбцы к существующей от PQ умной таблице, и провожу там расчеты (что-то типо матиматического можелирования).
4. Проблема в том, что у меня имеется отдельный лист с данными, где много маленьких табличек со значениями. С этих табличек и берутся данные для расчета (с помощью ВПР, ПОИСКПОЗ, ИНДЕКС и многих других).
Вопрос - разве в PQ можно реализовать вытаскивание необходимых значенмй с другого листа документа?

Если это возможно, то, наверное, лучше создать отдельную тему, в которой бы попросил помочь с осуществлением всех этих функций в PQ?

Но если это невозможно, тогда смысла нет копаться в PQ (язык M не знаю, но какие-то базовые функции понять могу)?
Изменено: EvgeniyLFC - 15.01.2019 07:48:53
 
EvgeniyLFC, попробуйте столбцы с формулами добавлять в запросе power query и обновлять макросом
 
Цитата
EvgeniyLFC написал: Насколько я знаю, в PQ нельзя реализовать функции ВПР и ПОИСКПОЗ с ИНДЕКСом
Зря вы так, возможности pq на несколько порядков превосходят формулы (и то, и другое является функциональным языком) .особенно в указанном вами вопросе, посмотрите на этом сайте статью про объединение нескольких таблиц, как раз то, что вам нужно.

Нз
Цитата
EvgeniyLFC написал: 4. Проблема в том, что у меня имеется отдельный лист с данными, где много маленьких табличек со значениями. С этих табличек и берутся данные для расчета (с помощью ВПР, ПОИСКПОЗ, ИНДЕКС и многих других).
Есть множество способов сделать это, один из них - Table.Join
https://docs.microsoft.com/en-us/powerquery-m/table-join

Но в вашем случае (если таблицы не огромные), может быть удобнее сделать функцию, возвращающую ваш параметр по имени таблицы и ключу. Доеду до работы, напишу в свободную минутку.  
 
artyrH, то есть сначала проверяем правильность формулы на простой табице, потом сгоняем формулу как текст в столбец PQ, а потом макросом преобразуем этот текст в формулу? Вариант хороший, но:
- с массивами будет работать (в массивах использую функции ПОИСКПОЗ + ИНДЕКС)?
- как быть, если итоговые значения в некоторых таблицах нужны в десятичных долях, а в некоторых - в целых (в примере 3-ий столбец всегда в целых, а как его сделать в десятичных)?
- что за столбец с "=АА" и почему он показывает 12?
И еще - уже, наверное, не соображаю, как на кнопку назначить сначала "обновить все", а преобразования теста в формулы, что нужно дописать?

Alexey_Spb, не пойму Ваш метод. Зачем мне объединять таблицы?
На одном листе я веду данные, например, табличка "Скорость": по столбцу "Марка" ищу марку, а по столбцу "Скорость" извлекаю нужное число. При этом есть случаи, когда нужно по 3-м параметрам искать (например, марка&диаметр&столбец_расчета), для этого уже использую формулу массива с функциями ПОИСКПОЗ + ИНДЕКС и &.
Изменено: EvgeniyLFC - 15.01.2019 09:20:03
 
сразу скажу что макросами только пользуюсь. писать их не пишу. power query только начал позновать, и то очень медленно.
Цитата
EvgeniyLFC написал: с массивами будет работать (в массивах использую функции ПОИСКПОЗ + ИНДЕКС)?
не знаю. в примере покажите - дальше видно будет.

Цитата
в таблицах нужны в десятичных долях
скорее всего, макросом можно менять формат.

Цитата
он показывает 12?
смотрите в диспетчере имен

Цитата
на кнопку назначить сначала "обновить все"
Код
ActiveWorkbook.RefreshAll

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

Цитата
Зачем мне объединять таблицы?
так работает ВПР в PQ)
 
Цитата
Anton555 написал:
коллега немного лукавит
В чём? Подскажите пожалуйста аналогичную ВПР с аргументом поиска приблизительного совпадения функцию в Power Query. И если оставаться в рамках вопроса темы, то не проще ли подсказать, что последовательная нумерация строк решается просто созданием индексного столбца.
 
Цитата
Андрей VG написал:
аналогичную ВПР с аргументом поиска приблизительного совпадения функцию
функцию не знаю. вообще пока функций не знаю, зато  кнопками уже научился)
 
Выкладываю второй пример:
- лист "Данные" содержит основные показатели производства;
- лист "Таблица" содержит выгрузку заявок из 1С (фактически это реестр);
- лист "PowerQuery" содержит выгрузку из реестра, а к ней дополнительно применяется расчет (желтая заливка).

Проблема в том, что когда добавляются новые заявки, то при обновлении таблицы PQ дополнительный расчет (тот, что в желтой заливке) не проставляется автоматически. Каждый раз приходится протягивать мышкой, чтобы формулы заполнили пустые ячейки.

Теперь про варианты решения проблемы:
1. artyrH, мне понравилась идея, но как будут работать формулы массивов (мне кажется не будут) + придется две кнопки нажимать (хотелось бы одну) + все числа округляются до целого, когда это не нужно. Может кто помочь решить эти недочеты?
2. Alexey_Spb, Вы предлагаете объеденить таблицы, чтобы была возможность использовать ВПР в PQ, но принцип мне не понятен. Буду благодарен, если разъясните и покажите пример. Также вопрос про формулы массивов - как провести аналогичный расчет в PQ?

Андрей VG, к сожалению, не проще. В первом примере просто показал в чем проблема, сейчас показываю - какие формулы использую и как произвожу расчет. В совокупности, каждый раз перепроверять, где образовалась пустая строчка без расчетов, когда таблица содержит более 1000 строк, не удобно и, если честно, подбешивает. Ищу вариант решения проблемы.
 
Цитата
EvgeniyLFC написал:
Выкладываю второй пример:
Если предоставляете в рамках данного топика, тогда и название темы предлагайте новое. Модераторы поменяют. Либо создавайте новую.
Вот горшок пустой, он предмет простой...
 
artyrH, отлично :) только я бы добавил в первую сортировку также сортировку по индексу по убыванию - чтобы границы интервалов гарантировано были выше, чем равные им значения, при заполнении вниз.

Иначе смотрите, добавил для примера еще несколько 6-к, смотрите, что получается:

и с 30-кой тоже там проблема
Изменено: Максим Зеленский - 15.01.2019 12:16:57
F1 творит чудеса
 
Цитата
Андрей VG написал: Подскажите пожалуйста аналогичную ВПР с аргументом поиска приблизительного совпадения функцию в Power Query.
Отвечу за Anton555. Мне неизвестная такая функция, но ведь можно в PQ создать ее аналог, в том числе и нечеткий поиск текста, что-то типа FuzzyLookup. Конечно, на PQ это не самая оптимальная вещь, но тем не менее возможность есть.

Цитата
EvgeniyLFC написал:
2.  Alexey_Spb , Вы предлагаете объеденить таблицы, чтобы была возможность использовать ВПР в PQ, но принцип мне не понятен. Буду благодарен, если разъясните и покажите пример. Также вопрос про формулы массивов - как провести аналогичный расчет в PQ?
Вот вам простой пример - количество конечных коробок подставляется из таблицы Упаковка по ключу - Модель катушки. Только у вас там два варианта коробок, и не известно какой в какой отгрузке используется.
Что касается, формул массивов - а что вам нужно.
Изменено: Alexey_Spb - 15.01.2019 16:05:40
 
Цитата
Максим Зеленский написал:
смотрите, что получается
ничего себе! :oops: Максим Зеленский, спасибо что поправили, а то я бы так и пользовался бы.  вообще то я здесь увидел этот способ, но без знания английского чего то не усмотрел. он там, случаем, не сказал чтоб были только уникальные значения))
 
EvgeniyLFC, затолкал формулы, только что то не так с одним столбцом. заменил формулы с массивным вводом
Изменено: artyrH - 15.01.2019 13:59:00
 
Цитата
EvgeniyLFC написал:
Вы предлагаете объеденить таблицы, чтобы была возможность использовать ВПР в PQ, но принцип мне не понятен. Буду благодарен, если разъясните и покажите пример.

Чтобы вам было понятнее - приведу такую аналогию. PQ это такой же функциональный язык, как и.. формулы.

То есть по своим принципам работа с PQ ничем не отличается от работы с формулами - нет операций присвоения в том смысле, как это принято, например, в VBA. Результат вычисляется во время обновления по принципу "как растет дерево" - от корня и к ветвям. По этому же принципу в формуле вы используете значения других ячеек, где хранятся промежуточные результаты, но и там они вычисляются в случае необходимости, обращаясь к другим ячейкам и т.д.

Присвоение имени на PQ можно сравнить с объявлением именнованого диапазона ячеек в Excel. По этой причине понятно что не может быть двух диапазонов с одинаковыми именами, ровно как и двух одинаковых имен в пределах одной зоны видимости в PQ.

Попробуйте зайти в Advanced Editor PQ и, стерев там абсолютно все, написать просто:
Код
2  + 2

или
Код
"Привет, " & "Вася"
И нажать ОК.

Аналогом "скобок" в формулах, в PQ является конструкция let .. in - между let и in пишутся промежуточные этапы, а то, что после in - итоговое возвращаемое значение.

Код
let
  Privet = "Привет, " 
in Privet & "Вася"
Код
let
  Privet1 = "Привет, ",
  Privet2 = "Вася",
  Privet3 = Privet1 & Privet2
in Privet3
Последний пример является основной формой М - кода, которую генерирует встроенный редактор. Когда вы делаете Query from Table, вместо Privet1 добавляется функция, возвращающая из книги вашу таблицу по ее имени, вместо Privet2 и Privet3 - те операции, которые вы в редакторе делаете.

Внутри let имена перечисляются через запятую, перед in она не ставится.

Эти "cкобки" можно вставлять где угодно, только в некоторых случаях встроенный редактор перестанет вас понимать (если ваш запрос не сможет разбить на послоедовательные шаги). Не рекомендую в начале это делать, лучше делайте код в редакторе запросов, добавляя или редактируя по необходимости.

Ну вот пример со скобками:
Код
let  Privet1 = "Привет, ",
  Privet2 = let Va = "Ва", Sya = "ся" in Va & Sya,
  Privet3 = Privet1 & Privet2
in Privet3

Имя Privet2 будет присвоено выражению Va & Sya, элементы которого, в свою очередь, определяется внутри let.

По вышесказанным причинам, вычисление начинается с того, что идет после последнего in и далее PQ "смотрит" на что внутри let ссылается это конечное выражение, в случае необходимости нужные значения вычисляются и т.д.

Имхо, самая наглядная картина - рост дерева из семечка.

P.S. В PQ существует множество типов - например, список (List), таблица (Table) запись (Record), что угодно (Any) и т.д. Более того, функции и типы данных тоже являются типами, а элементами любых составных элементов могут быть любые типы.

Вы можете создать таблицу, ячейками которой будут, например, другие таблицы или даже функции.

Функция может быть рекурсивной (вызывать саму себя), может быть с переменным числом аргументов

Массивов в привычном понимании тут нет - есть списки, элементами которого являются другие списки, либо, например, таблицы.
Изменено: Alexey_Spb - 15.01.2019 14:42:52
 
Alexey_Spb, разобрался. Отличная штука. Но:
- у меня в текущей таблице идут формулы ЕСЛИ(ИЛИ(логическое выражение 1; логическое выражение 2);формула с ВПР;значение другого столбца) - в PQ это можно реализовать в рамках одного столбика (или нужно раскидывать каждую операцию по разным столбцам, а после - объединять их формулой и ненужные удалять)?
- PQ как долго будет обрабатывать, если количество строк будет более 1000, а количество таких вот функций - 30-50 для каждой строки, не зависнет ли файл при работе, не будет ли тормозить?

artyrH, Вы решили заменить ПОИСКПОЗ функцией АГРЕГАТОР. К моему стыду эту функцию не знаю вообще. Ее понимание для меня пока сложновато. Как я понял, так Вы решили уйти от массивов?
Обновил таблицу - запустил макрос - ошибка на второй строчке. Что-то не так. А с каким столбцом не получается, вроде при открытии файла все данные получаются верными, даже с дробной частью.
Изменено: EvgeniyLFC - 15.01.2019 15:21:12
 
Цитата
EvgeniyLFC написал:
Нужна помощь
Операция в конструкторе называется Merge Queries.

Вы сами выбираете столбцы, по значениям которых определяется идентичность строк двух таблиц.

Так же можете выбрать тип объединения (у вас выбрано Left - то есть из первой (главной  таблицы грузятся все строки, из второй добавляются только те, где ключевые столбцы двух таблиц совпали) - посмотрите настройки операции объединения, нажав на значок рядом с её именем в списке.  
Изменено: Alexey_Spb - 15.01.2019 14:59:43
 
Ребята, вроде бы все получаться стало. Попробую все свои расчеты загнать в PQ. Если где-то заклинет, то вновь попрошу помощи :)
Всем огромная благодарность за помощь!
 
Цитата
EvgeniyLFC написал:
уйти от массивов?
уйти от массивного ввода.
Цитата
EvgeniyLFC написал:
А с каким столбцом не получается
как оказалось, все получается. когда скачал файл из #16, то в столбце Z были другие значения. из за этого и вывод что получилось не то.
Alexey_Spb,  а нет ли у Вас литературы по PQ на понятном языке, как в #22?
 
Цитата
artyrH написал:
а нет ли у Вас литературы по PQ на понятном языке, как в #22?

Есть шикарный мануал по М от мелкомягких, так же как онлайн хелп, но там все на английском. И написано под программистов, ну, по крайней мере, знакомых с программированием. Может кто знает подобное на русском, я не знаю. Может, тут есть статьи..


Редактор запросов это круто, но по себе заметил что он уводит от сути языка М тех, кто только с ним знакомится.

Цитата
EvgeniyLFC написал:
СЛИ(ИЛИ(логическое выражение 1; логическое выражение 2);формула с ВПР;значение другого столбца) - в PQ это можно реализовать в рамках одного столбика (или нужно раскидывать каждую операцию по разным столбцам, а после - объединять их формулой и ненужные удалять)?

"впр", точнее, объединение таблиц сразу по нескольким столбцам возможно, в настройках выберите их несколько))

Так же в более сложном случае можно создать Столбец с вычислениями и объединять по нему  
Изменено: Alexey_Spb - 15.01.2019 16:01:33
 
Alexey_Spb, ну, следите же за бесполезными строками! Приведите в порядок сообщение.
 
Цитата
Alexey_Spb написал:
онлайн хелп
может, как то скините, браузер какникак переводит. заранее, спасибо
 
https://docs.microsoft.com/en-us/powerquery-m/power-query-m-reference
Страницы: 1 2 След.
Наверх