Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Пользователи » GrayMagellan
Выбрать дату в календаре…Выбрать дату в календаре

Страницы: 1 2 След.
Использование умных таблиц в проверке данных, Умные таблицы, проверка данных
 
#
26.02.2024 12:37:48
Цитата
написал:
=ДВССЫЛ("Таблица2[Столбец1]")
Спасибо большое! Идеально подходящее решение!
 
Перейти    
Результат запроса и дополнительные столбцы, прикреплённые к ним для ввода дополнительной информации, Если таблица состоит из смеси столбцов, возвращаемых PQ, и столбцов с ручным вводом данных, то при вставке/удалении строк в результате запроса дополнительные столбцы не меняются
 
#
23.05.2023 15:21:38
Цитата
написал:
Почитайте  вот эту статью .
Вообще тот факт, что какой-то англосакс (тогда) и я (сейчас) захотели работать с Экселем таким образом, говорит о том, что фича-то востребована! И мелкомягким стоило бы подумать насчёт того, чтобы корректно обрабатывать эту ситуацию в PQ.
 
Перейти    
Результат запроса и дополнительные столбцы, прикреплённые к ним для ввода дополнительной информации, Если таблица состоит из смеси столбцов, возвращаемых PQ, и столбцов с ручным вводом данных, то при вставке/удалении строк в результате запроса дополнительные столбцы не меняются
 
#
22.05.2023 17:10:49
Цитата
написал:
Цитата
GrayMagellan написал:
есть некий разрыв головного мозга...
бывает. Вам надо "затащить" таблицу с ручным вводом обратно в PQ и сделать join исходной таблицы (источник) с этим "новым" запросом по какому-то уникальному полю (это важно!). Затем "вытаскиваете" вашу руками редактируемую колонку и все. Self Referencing Query. Почитайте  вот эту статью .
Работает, ура! Но пока не могу до конца понять, как оно работает! Вроде должен получиться замкнутый круг.... Ладно, в любом случае вам большое спасибо за знания. Магия какая-то...
Изменено: GrayMagellan - 22.05.2023 17:31:53
 
Перейти    
Результат запроса и дополнительные столбцы, прикреплённые к ним для ввода дополнительной информации, Если таблица состоит из смеси столбцов, возвращаемых PQ, и столбцов с ручным вводом данных, то при вставке/удалении строк в результате запроса дополнительные столбцы не меняются
 
#
19.05.2023 18:06:55
И как вообще тогда Microsoft предлагает обогащать данными результаты запроса?! Ну не всегда же есть возможность внести обогащаемые данные в источник данных для запроса.
 
Перейти    
Результат запроса и дополнительные столбцы, прикреплённые к ним для ввода дополнительной информации, Если таблица состоит из смеси столбцов, возвращаемых PQ, и столбцов с ручным вводом данных, то при вставке/удалении строк в результате запроса дополнительные столбцы не меняются
 
#
19.05.2023 18:05:21
Но, вообще, Microsoft немного удивила... Раз позволили добавить пользовательские колонки к таблице с результатами запроса PQ, то юзер рассчитывает на то, что обновление результатов запроса с добавленными к нему кастомными колонками и данными в них будет происходить корректно... А так не происходит, и есть некий разрыв головного мозга...
Изменено: GrayMagellan - 19.05.2023 18:05:55
 
Перейти    
Результат запроса и дополнительные столбцы, прикреплённые к ним для ввода дополнительной информации, Если таблица состоит из смеси столбцов, возвращаемых PQ, и столбцов с ручным вводом данных, то при вставке/удалении строк в результате запроса дополнительные столбцы не меняются
 
#
19.05.2023 17:56:00
И, в принципе, если напрячь юзеров быть крайне внимательными и коммуницировать друг с другом, то наверное можно было бы заставить такую конструкцию работать, если не одно "но"! При редактировании зеленой таблицы команды вставить ячейку со сдвигом диапазона вниз нет, и поэтому легко и быстро скомпенсировать такую раздвижку левой половины таблиц сдвижкой вниз ячеек правой половины таблицы-приёмника не получается :(. Меню становится другое, и работать с отдельными ячейками (диапазонами ячеек) в умной таблице Excel не позволяет больше (см. скриншот).
Изменено: GrayMagellan - 19.05.2023 17:57:03
 
Перейти    
Результат запроса и дополнительные столбцы, прикреплённые к ним для ввода дополнительной информации, Если таблица состоит из смеси столбцов, возвращаемых PQ, и столбцов с ручным вводом данных, то при вставке/удалении строк в результате запроса дополнительные столбцы не меняются
 
#
19.05.2023 17:49:46
Прикладываю оба файла Excel с демонстрацией проблемы. Проблему становится видно, если обновить файл "Приёмник.xlsx" новыми данными из файла "Источник.xlsx".
 
Перейти    
Результат запроса и дополнительные столбцы, прикреплённые к ним для ввода дополнительной информации, Если таблица состоит из смеси столбцов, возвращаемых PQ, и столбцов с ручным вводом данных, то при вставке/удалении строк в результате запроса дополнительные столбцы не меняются
 
#
19.05.2023 17:43:22
Можно ли как-то заставить Excel раздвигать вниз всю итоговую таблицу полностью при появлении в источнике новых строк с данными?!
 
Перейти    
Результат запроса и дополнительные столбцы, прикреплённые к ним для ввода дополнительной информации, Если таблица состоит из смеси столбцов, возвращаемых PQ, и столбцов с ручным вводом данных, то при вставке/удалении строк в результате запроса дополнительные столбцы не меняются
 
#
19.05.2023 17:42:45
Затем другой юзер после получения информации от первого что данные изменились, обновляет свой файл "Приёмник.xlsx", и сталкивается с неприятной особенностью - таблица раздвигается вниз частично, в результате чего содержимое строк расползается (см. скриншот)
 
Перейти    
Результат запроса и дополнительные столбцы, прикреплённые к ним для ввода дополнительной информации, Если таблица состоит из смеси столбцов, возвращаемых PQ, и столбцов с ручным вводом данных, то при вставке/удалении строк в результате запроса дополнительные столбцы не меняются
 
#
19.05.2023 17:41:34
И вроде бы вся эта конструкция нормально работает, но только до первого изменения данных в исходном файле "Источник.xlsx". Предположим, другой юзер в исходном файле в середину таблицы добавил новое значение (см. скриншот)
 
Перейти    
Результат запроса и дополнительные столбцы, прикреплённые к ним для ввода дополнительной информации, Если таблица состоит из смеси столбцов, возвращаемых PQ, и столбцов с ручным вводом данных, то при вставке/удалении строк в результате запроса дополнительные столбцы не меняются
 
#
19.05.2023 17:40:30
Затем надо обогатить полученные из файла-источника данные дополнительными данными, и использовать эту таблицу в качестве источника для других запросов. Юзер добавляет свой столбец, и наполняет его своими дополнительными данными (см. скриншот)
 
Перейти    
Результат запроса и дополнительные столбцы, прикреплённые к ним для ввода дополнительной информации, Если таблица состоит из смеси столбцов, возвращаемых PQ, и столбцов с ручным вводом данных, то при вставке/удалении строк в результате запроса дополнительные столбцы не меняются
 
#
19.05.2023 17:39:32
Рядом делаю файл "Приёмник.xlsx", в котором подтягиваются данные из файла "Источник.xlsx" следующим запросом:
let
   Источник = Excel.Workbook(File.Contents("D:\Новая папка (11)\Источник.xlsx"), null, true),
   тблИсточник_Table = Источник{[Item="тблИсточник",Kind="Table"]}[Data],
   #"Измененный тип" = Table.TransformColumnTypes(тблИсточник_Table,{{"Имя", type text}, {"Код", Int64.Type}})
in
   #"Измененный тип"

Результат запроса подтягивается в файл "Приемник.xlsx" в таком виде (см. скриншот)
 
Перейти    
Результат запроса и дополнительные столбцы, прикреплённые к ним для ввода дополнительной информации, Если таблица состоит из смеси столбцов, возвращаемых PQ, и столбцов с ручным вводом данных, то при вставке/удалении строк в результате запроса дополнительные столбцы не меняются
 
#
19.05.2023 17:34:57
Да блин... Почему при редактировании сообщения я вижу картинки нормально, а после публикации получается какая-то буквенно-цифровая хрень?!
 
Перейти    
Результат запроса и дополнительные столбцы, прикреплённые к ним для ввода дополнительной информации, Если таблица состоит из смеси столбцов, возвращаемых PQ, и столбцов с ручным вводом данных, то при вставке/удалении строк в результате запроса дополнительные столбцы не меняются
 
#
19.05.2023 17:30:35
Но столкнулся с такой проблемой:

Предположим, у нас есть файл "Источник.xlsx" с вот такими исходными данными (см. файл):
Изменено: GrayMagellan - 19.05.2023 17:38:34
 
Перейти    
Результат запроса и дополнительные столбцы, прикреплённые к ним для ввода дополнительной информации, Если таблица состоит из смеси столбцов, возвращаемых PQ, и столбцов с ручным вводом данных, то при вставке/удалении строк в результате запроса дополнительные столбцы не меняются
 
#
19.05.2023 17:29:44
Всем привет!

Пытался использовать результат запроса PQ, обогащаемый потом введенными пользователем руками дополнительными данными, которые, в свою очередь, являлись бы источником данных для последующих запросов.
 
Перейти    
Как в запросе Power Query (PQ) заполнить столбец значением именованной ячейки из внешнего Excel-файла?
 
#
25.01.2019 15:37:38
Все равно никак не могу отделаться от ощущения, что PQ - это объектно-ориентированный язык, где мы также получаем данные через "точку" (имею ввиду {} и []) свойства и методы объектов :).
 
Перейти    
Как в запросе Power Query (PQ) заполнить столбец значением именованной ячейки из внешнего Excel-файла?
 
#
25.01.2019 15:19:00
Цитата
Alexey_Spb написал:
Excel.CurrentWorkbook()
Да, я тоже обратил внимание на то, что Excel.CurrentWorkbook() и Excel.Workbook() возвращают разные структуры. Отсюда мне стало понятно, когда брать [Content], а когда [Data]
 
Перейти    
Как в запросе Power Query (PQ) заполнить столбец значением именованной ячейки из внешнего Excel-файла?
 
#
25.01.2019 13:51:57
Хорошо, спасибо. А можно еще один ламерский вопрос - не пойму, когда возникает [Content], а когда [Data]? Хвост, как правило, у них одинаковый - {0}[Column1]
Изменено: GrayMagellan - 25.01.2019 13:52:31
 
Перейти    
Как в запросе Power Query (PQ) заполнить столбец значением именованной ячейки из внешнего Excel-файла?
 
#
25.01.2019 12:47:59
Цитата
Alexey_Spb написал:
P.S. Еще раз, вы обращаетесь не к объектам, вы по сути выбираете из таблицы, содержащей потроха экселя, нужный вам. Делаете это конструкцией, которая сначала возвращает строку таблицы (а строка таблицы имеет тип Record), соответствующую заданным значением одной (Name) или двух одновременно (Item, Kind) колонок.После этого обращаетесь к полю записи (поле записи = имени столбца) - [Data], а там хранится ваша таблица.Data - это не ключевое слово, а имя колонки :-)
Пошел осмысливать ваши слова. Спасибо!
 
Перейти    
Как в запросе Power Query (PQ) заполнить столбец значением именованной ячейки из внешнего Excel-файла?
 
#
25.01.2019 12:46:13
Гм... Наверное, Item/Kind предпочтительней, т.к. имя типа "лист Добавляем значение!ExternalData_1" мне более понятно и я хотя бы понимаю где это и что это такое, а вот имя "ExternalData_1" непонятно откуда взялось...
 
Перейти    
Как в запросе Power Query (PQ) заполнить столбец значением именованной ячейки из внешнего Excel-файла?
 
#
25.01.2019 12:43:15
Сделал так, как вы говорили:
let
   Источник = Excel.Workbook(File.Contents("\\UNC путь...\Тест.xlsx"), null, true)
in
   #"Источник"

Получил такую картинку:
 
Перейти    
Как в запросе Power Query (PQ) заполнить столбец значением именованной ячейки из внешнего Excel-файла?
 
#
25.01.2019 12:37:49
Гм... Если делаю запрос такого вида (без слова [Data] в конце):

let
   Источник = Excel.Workbook(File.Contents("\\UNC путь...\Тест.xlsx"), null, true){[Name="тблРезультатВВидеЗначения"]}
in
   #"Источник"

то PQ мне показывает такую картинку:


Т.е. в принципе там есть и Name, и Item/Kind. Я вижу оба, и свободно могу выбрать Name или Item/Kind, после чего обращаюсь к объекту Data через ключевое слово [Data]?
Изменено: GrayMagellan - 25.01.2019 12:38:34
 
Перейти    
Как в запросе Power Query (PQ) заполнить столбец значением именованной ячейки из внешнего Excel-файла?
 
#
25.01.2019 12:26:57
Блин... Все-таки мне интересно, в чем разница между

let
   Источник = Excel.Workbook(File.Contents("\\UNC путь...\Тест.xlsx"), null, true){[Item="тблРезультатВВидеЗначения",Kind="Table"]}[Data]
in
   #"Источник"


и


let
   Источник = Excel.Workbook(File.Contents("\\UNC путь...\Тест.xlsx"), null, true){[Name="тблРезультатВВидеЗначения"]}[Data]
in
   #"Источник"

Обе - работают! Зачем MS сделала дублирующиеся механизмы доступа к данным? И какой все же лучше и предпочтительней использовать? Интерактивный помощник как по методу из видео строит путь по первому варианту. Второй я уже просто из чистого любопытства попробовал, и не ожидал что он тоже сработает.
Изменено: GrayMagellan - 25.01.2019 12:29:27
 
Перейти    
Как в запросе Power Query (PQ) заполнить столбец значением именованной ячейки из внешнего Excel-файла?
 
#
23.01.2019 17:48:39
Уфф... С локальной работой в рамках одной книги разобрался. Теперь буду делать то же самое, но с забором константы, полученной первым запросом в первом файле (дополнительный), во второй запрос, расположенный во втором файле (основном).
 
Перейти    
Как в запросе Power Query (PQ) заполнить столбец значением именованной ячейки из внешнего Excel-файла?
 
#
23.01.2019 17:46:10
Я понял! Автоматом Эксель взял данные не из умной таблицы, содержащей результат запроса к исходной умной таблице, а прямо из исходной умной таблицы (сорри за "масло масляное"). Это немного не то, что мне нужно было. Если предположить, что исходная умная таблица содержит много данных, то результатом запроса к ней будет одно итоговое число. Именно его я хочу брать. Поменял немного автоматически полученный код, и наконец-то получил то, что нужно:
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица4"]}[Content],
    #"Добавлен столбец заполненый константой из простой именованной ячейки" = Table.AddColumn(Источник, "Константа из простой именованной ячейки", each Excel.CurrentWorkbook(){[Name="КонстантаВПростойИменованнойЯчейке"]}[Content]{0}[Column1]),
    #"Добавлен столбец заполненый константой из исходной умной таблицы" = Table.AddColumn(#"Добавлен столбец заполненый константой из простой именованной ячейки", "Константа из исходной умной таблицы", each Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content]{0}[Константа в умной таблице]),
    #"Добавлен столбец заполненый константой из умной таблицы заполненной результатом запроса" = Table.AddColumn(#"Добавлен столбец заполненый константой из исходной умной таблицы", "Константа из результата запроса к умной таблице", each Excel.CurrentWorkbook(){[Name="тблКонстантаИзУмнойТаблицы"]}[Content]{0}[Константа в умной таблице])
in
    #"Добавлен столбец заполненый константой из умной таблицы заполненной результатом запроса"
Изменено: GrayMagellan - 23.01.2019 17:46:53
 
Перейти    
Как в запросе Power Query (PQ) заполнить столбец значением именованной ячейки из внешнего Excel-файла?
 
#
23.01.2019 17:35:52
Цитата
PooHkrd написал:
Ознакомьтесь с вот этим  приемом  от хозяина сайта
Ознакомился :). Спасибо большое за этот способ визуального построения пути к данным! Использовав прием, описанный в видео, получил наконец-то работающую конструкцию:
Код
= Table.AddColumn(#"Добавлен столбец заполненый константой из простой именованной ячейки", "Константа из умной таблицы, заполненной результатом запроса", each Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content]{0}[Константа в умной таблице])


Непонятно, конечно, как Эксель получил эту конструкцию... Это работает, но остались вопросы:
1) Почему Name, а не Item/KInd?
2) Почему [Content], а не [Data]?
3) Почему Таблица1, а не тблКонстантаИзУмнойТаблицы?

Работающая моделька в этом файле:
Изменено: GrayMagellan - 23.01.2019 17:51:11
 
Перейти    
Как в запросе Power Query (PQ) заполнить столбец значением именованной ячейки из внешнего Excel-файла?
 
#
23.01.2019 16:46:23
Конструкция:

= Excel.CurrentWorkbook(){[Item="тблКонстантаИзУмнойТаблицы",Kind="Table"]}[Data
]{0}[Константа в умной таблице]

не работает :(
 
Перейти    
Как в запросе Power Query (PQ) заполнить столбец значением именованной ячейки из внешнего Excel-файла?
 
#
23.01.2019 16:38:52
Если я просто из Экселя обращаюсь к умной таблице, содержащей константу-результат запроса, то мне достаточно написать:

=тблКонстантаИзУмнойТаблицы[Константа в умной таблице]
 
Перейти    
Как в запросе Power Query (PQ) заполнить столбец значением именованной ячейки из внешнего Excel-файла?
 
#
23.01.2019 16:31:48
Цитата
Дмитрий(The_Prist) Щербаков написал:
[Item="_04___Дефолтный_срез_С1",Kind="Table"]
А где можно прочитать об этих Item/Kind и их всех допустимых значениях (пересечениях значений) и концептуально понять что и как в них заполнять? А то я как слепой кролик тыкаюсь в них, пытаясь заполнить наобум :(.

P.S. Попытался использовать как вы сказали - не получилось. Та же ошибка :(.

Уважаемый Пух, а не могли бы вы по последнему файлу "Дополнительный" сказать мне команду адресации к результатам запроса константы?

И чем Name, который я видел в самой первой конструкции, отличается от Item/Kind?
Изменено: GrayMagellan - 23.01.2019 16:35:31
 
Перейти    
Как в запросе Power Query (PQ) заполнить столбец значением именованной ячейки из внешнего Excel-файла?
 
#
23.01.2019 16:19:09
В общем, как я понимаю, моя проблема в том, что я из одного запроса PQ обращаюсь не к простой именованной ячейке, а к ячейке из результатов другого запроса PQ. И нужно как-то правильно адресоваться к результатам того, другого, запроса... Я-то думал, что если я ячейку в результате другого запроса объявлю именованной, что Эксель будет к ней обращаться по тем же правилам, что и к простой именованной.
Изменено: GrayMagellan - 23.01.2019 16:20:59
 
Перейти    
Страницы: 1 2 След.
Наверх