Для подобной задачи, если я правильно помню, Sanja предложил пользовательскую функцию, которая очень хорошо работает - именно количество уникальных значений в отфильтрованном диапазоне (хоть числа, хоть тексты). Спасибо ему!
Посчитать количество уникальных в отфильтрованном диапазоне
Разворачивание нескольких полей вложенной таблицы PQ
Разворачивание нескольких полей вложенной таблицы PQ
|
13.03.2025 08:40:49
Здравствуйте! При использовании PQ регулярно встает задача развернуть вложенную таблицу. Когда нужно вытащить одно поле - вопросов нет. А когда их много, приходится дублировать шаги, что иногда приводит к путанице и всегда к увеличению времени выполнения запроса. Задал вопрос ИИ по составлению функции, которая на входе примет имя поля с вложенной таблицей и список полей этой вложенной таблицы, которые нужно развернуть, создать одноименные поля в результирующей таблице и в этих полях объединить значения через указанный разделитель. Получил такой код функции:
Но при использовании возникает ошибка, что "Поле1" не существует. Прошу помощи в корректировке функции и ее применения. Спасибо. |
|||||
|
|
|
Сохранение функций PQ
Регулярное выражение для замены подстроки и смены регистра
|
12.08.2024 14:37:07
Kuzmich, на просторах инета нашел код для UDF
Как его можно скорректировать с учетом Вашего предложения? Я не смог (( |
|||
|
|
|
Регулярное выражение для замены подстроки и смены регистра
|
12.08.2024 11:24:10
Добрый день.
Использую регулярку \d+)([а-яА-Я]) для поиска подстроки с заменой $1 $2 для исправления адресов, в которых литера дома "слипается" с номером (типа "дом 5а" или "5А". То есть просто вставляю пробел. Но хотелось бы привести литеру к нижнему регистру, чтобы в обоих случаях получалось "дом 5 а". И никак не получается сменить регистр. Прошу помочь. Спасибо! |
|
|
|
|
В PowerQuery отображаются не все запросы БД
|
02.02.2024 09:55:10
Добрый день, гуру!
Если вопрос не по адресу, прошу модераторов простить. Ситуация такая: из БД Access данные выгружаются в Excel через PQ. Раньше сталкивался с тем, что долго обрабатываются в PQ, поэтому максимум обработки задаю в БД, формируя там запросы, а уже их и выгружаю. Но почему-то не все запросы, сделанные в БД, отображаются в PQ и, следовательно, не выгружаются в Excel. На картинках списки запросов в БД и PQ, выделены запросы, которые мне нужны для выгрузки. Я предположил, что запросы на объединение не выгружаются, а также запросы с пользовательскими функциями (в приложенном документе текст запроса на выборку с включением пользовательской функции - своего рода "множественный ВПР"). Использование этой функции в Excel или через объединение таблиц в PQ сильно тормозит работу. Собственно, вопрос: я прав, что пользовательские функции и запросы "не на выборку" не могут выгружаться? Если это так, есть ли способ обойти это ограничение? Спасибо! |
|
|
|
|
Plex что бы хотелось ещё, Тема просто как фиксатор идей для такого замечатального инмтрумента как Plex , ничему не обязывая Николая,
Склонение сложных должностей (или поиск и замена части текста в ячейке текстом из другой ячейки)
Подсчет количества уникальных значений из нескольких диапазонов по условию
Подсчет количества уникальных значений из нескольких диапазонов по условию
Подсчет количества уникальных значений из нескольких диапазонов по условию
|
16.06.2023 10:44:14
МатросНаЗебре, спасибо! Я правильно понимаю, что на каждом листе нужно сделать дополнительные столбцы? Ваш первый вариант (без доп.столбцов), к сожалению, не сработал - после изменения исходных данных подсчет был неправильный. Но принцип без допстолбцов мне нравится больше.
|
|
|
|
|
Подсчет количества уникальных значений из нескольких диапазонов по условию
|
16.06.2023 09:35:30
Добрый день, уважаемые мастера! Прошу помочь.
Используется Excel2016. Имеется несколько таблиц на разных листах ("умные таблицы") - это выгрузки, сделанные из PQ. Значения могут дублироваться как в рамках одной таблицы, так и в разных. Стоит задача подсчитать количество уникальных значений, учитывая условия, указанные на листе "Результат". То есть, если в столбце "Дата" любой таблицы значение попадает в указанный диапазон И в столбце "Имена" значение равно указанному, значение из столбца "Данные1(2,3)" нужно учитывать при подсчете. Пока эту задачу я решал так: фильтровал каждую таблицу поочередно, копировал отфильтрованные данные на новый лист и удалял дубликаты. Естественно, очень непродуктивно, в том числе потому что при изменении условий фильтра все приходится повторять. Надеюсь понятно изложил )) Желательно использовать формулы, хотя возможно и PQ. Заранее спасибо откликнувшимся! |
|
|
|
|
Импорт данных из папки с несколькими файлами (Power Query)
Макрос проверки контрагентов через сервис ФНС
Plex что бы хотелось ещё, Тема просто как фиксатор идей для такого замечатального инмтрумента как Plex , ничему не обязывая Николая,
Ошибка в PQ "Expression.Error: Индекс находится за пределами границ записи", При загрузке отдельных таблиц из БД в PQ возникает ошибка
|
24.06.2022 07:59:10
Методом проб и ошибок удалось выяснить, что ошибки возникают при импорте таблиц, в которых есть подстановки из других таблиц с возможностью множественных значений (например, в таблице Лицензий есть указание на несколько записей из справочной таблицы лицензированных программ). Но это, увы, не приводит к пониманию пути решения задачи.
Хелп! |
|
|
|
|
Ошибка в PQ "Expression.Error: Индекс находится за пределами границ записи", При загрузке отдельных таблиц из БД в PQ возникает ошибка
|
23.06.2022 14:45:17
Добрый день!
Из БД в акссесс в excel-файл должны выгружаться через PQ отдельные данные. Часть таблиц выгружается нормально, но большинство выдает ошибку Expression.Error: Индекс находится за пределами границ записи (пример ошибки на скрине, код запроса ниже). Поиск такой ошибки в инете дал только совет: проверить имена полей в таблице БД. Но имена полей нормальные, никаких спецсимволов нет. В чем может быть причина такой ошибки? Заранее благодарен.
|
|||
|
|
|
Разнести составные почтового адреса по столбцам
АвтоФильтр не фильтрует ведущие и хвостовые пробелы, Autofilter can't Filter the Srings with spaces in the begining or/and in the end of the text
|
13.01.2022 11:46:07
Вчера только столкнулся с подобной ситуацией: в ячейке ТОЛЬКО пробел. Фильтр по столбцу показывает пустое значение (вместе с ячейками, в которых нет НИЧЕГО). А PowerQuеry посчитал ячейку непустой. Долго искал
|
|
|
|
|
Исправление UDF (склонение по падежам), UDF работает некорректно
Исправление UDF (склонение по падежам), UDF работает некорректно
|
09.11.2021 09:14:48
Спасибо огромное, Дмитрий! Честно говоря, в первом варианте у меня и было обращение к TwisWorkbook, а потом я решил, что если функция применяется в другом файле, то именно к этому файлу ThisWorkbook будет относиться и что нужно прямо указать имя надстройки.
С готовностью и благодарностью приму советы по оптимизации кода.
Изменено: - 09.11.2021 09:15:27
|
|
|
|
|
Исправление UDF (склонение по падежам), UDF работает некорректно
|
09.11.2021 08:29:32
Всем добрый день. О склонении по падежам уже немало поднято тем, но применить для своей задачи удалось немного. Передо мной стоит задача склонять по падежам названия организаций. Я сделал функцию, вложил ее в надстройку. Логика работы функции такая: название (например, муниципальное учреждение "Школа №15" делится на части по первой кавычке: муниципальное учреждение - и "Школа №15"), затем первая часть разбивается по словам, каждое слово ищется в словаре и по принципу ВПР заменяется на соответствующее. Если кавычки нет, то берется все название целиком. Затем выбранные слова сцепляются между собой и потом со второй частью, находящейся в кавычках.
Словарь находится в файле надстройки на листе "Справочник". Проблема в том, что если в файле, в котором я применяю функцию, нет листа "Справочник", функция не срабатывает. Прошу помочь решить эту проблему. Также буду признателен за любые предложения по оптимизации кода функции. В том числе, мне кажется, лучше если словарь будет загружаться в память однократно, а то сейчас по логике функции он подгружается при каждом ее использовании. |
|
|
|
|
Собрать уникальные значения в одну ячейку макросом
|
13.08.2021 13:57:40
|
|||
|
|
|
Собрать уникальные значения в одну ячейку макросом
Подсчет уникальных значений в столбце по заданному фильтру и вывод значений в отдельную ячейку
|
14.07.2021 06:25:48
Добрый день. Передо мной стояла практически такая же задача. Макрос от МатросаНаЗебре помог, но адаптировать под себя я не смог. А вот в книге Н.Павлова по PowerQuery на страницах 88-90 пошагово описано решение. Посмотрите, может, пригодиться.
Использованный макрос сможете найти в моем вопросе 1-2 недели назад. |
|
|
|
|
Собрать уникальные значения в одну ячейку макросом
|
06.07.2021 12:05:57
Уважаемые форумчане!
Возобновляю тему, потому что задача, заявленная в начальном посте, становится регулярной. Но, к сожалению, не получается адаптировать макрос на листах с похожими данными, но на которых идентификаторы объектов и группы событий находятся в иных столбцах. В этой связи прошу "допилить" макрос, учитывая: 1. искать группы нужно по идентификаторам, а не названиям объектов, потому что идентификаторы уникальны, а названия объектов могут совпадать. 2. предоставить пользователю возможность через inputbox указывать столбцы (или начальные ячейки диапазонов), в которых находятся идентификаторы на листе объектов и группы на листе событий. Спасибо всем, кто откликнется! |
|
|
|
|
Собрать уникальные значения в одну ячейку макросом
|
03.06.2021 17:56:34
МатросНаЗебре, макрос сработал на файле-приеме, спасибо. Правда, не уверен, что на рабочем файле разрешат запускать макросы, но попробую завтра.
Можно попросить комментарии к коду, чтобы адаптировать его к реальности? А формулами никак не получится? |
|
|
|
|
Собрать уникальные значения в одну ячейку макросом
|
03.06.2021 17:49:44
Mershik, ВПР я использовал на реальном файле. Сейчас из дома сделал "модель" (что есть и что нужно) и обращаюсь за помощью.
Я делал во вспомогательных столбцах, вытаскивая из таблицы событий группы, а потом пытался их сцеплять. Выбрать уникальные не удалось от слова "совсем", а вот с вытаскиванием групп в случае единичных вхождений - с этим я столкнулся: в таблице событий есть, а в объекты не попала. |
|
|
|
|
Собрать уникальные значения в одну ячейку макросом
|
03.06.2021 17:33:18
Добрый день, уважаемые форумчане!
Что есть: таблица объектов с уникальными идентификаторами, таблица событий, случившихся с этими объектами. События объединены в группы. Общее число групп известно - их около 40. С каждым объектом могут происходить события как из одной группы, так и из разных, а могут не происходить вовсе. Таблицы разнесены по разным листам. Передо мной стоит задача: в таблице объектов в одной ячейке перечислить ВСЕ группы событий, случившихся с ним. При этом если с одним объектом случаются несколько событий одной группы, то группа должна указываться только один раз. Перечень групп в этой ячейке должен быть разделен переводом строки (Alt-Enter). Я пробовал использовать множественный ВПР из Приемов на этом сайте и функции из Plex, но качественного результата не получил. В частности, если у объекта только одно событие (естественно, только одна группа), то значение не подгрузилось. Используется Excel16. Пример, сделанный вручную, "как хочется, чтобы было", приложен. Очень надеюсь на помощь гуру. Заранее благодарю.
Изменено: - 06.07.2021 12:52:49
|
|
|
|
|