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

Страницы: 1
Выбор числа в массиве по нескольким показателям, ближайшим к фактическим значениям и не превышающим их
 
#
17.08.2023 19:55:05
Добрый день!
Из таблицы необходимо выбрать значение на пересечении строк, удовлетворяющее условию: каждый показатель ближайшее меньшее или равное соответствующему показателю из факта. Пробовал с "суммпроизв", но не получается выбрать БЛИЖАЙШЕЕ меньшее.
Помогите, пожалуйста, с соответствующей формулой.
Пример прилагаю, на вопросы отвечу.
Заранее благодарен откликнувшимся.
 
Перейти    
Поиск наличия в справочнике подстроки обрабатываемой строки, возврат значения из соседней колонки
 
#
27.05.2020 16:40:42
Добрый день!

Прошу помощи в генерации хитрой формулы, которая смогла бы перебрать справочник, найти в колонке справочнике значение, которое является подстрокой обрабатываемого значения и вернула значение в соседней колонке справочника.
Файл прикладываю.
Или такое лучше в VBA делать?
Изменено: quasarrr - 27.05.2020 16:43:49
 
Перейти    
PQ. Поиск в столбце таблицы вхождений текста из 2-х столбцов другой таблицы и присоединение найденных столбцов.
 
#
21.05.2020 07:29:43
Здравствуйте, форумчане.
Что-то затупил, а время поджимает.
Необходимо присоединить к таблице1 строки из таблицы 2 в случае если в таблице 2 столбцы Наименование и Инв являются подстроками строки в столбце  Наименование таблицы 1.
Пример в файле. Желательно на PQ.
Заранее спасибо.
 
Перейти    
PQ функция List.Generate. Получить значение предыдущего элемента списка при расчёте следующего внутри функции next
 
#
16.05.2020 20:50:26
Уважаемые форумчане, добрый вечер!

На просторах интернета, а именно на сайте
http://excel-inside.pro/ru/blog/2017/06/15/генератор-произвольных-списков-в-power-query-и-pow/

нашёл следующее описание алгоритма работы функции List.Generate

Цитата
  1. Начальное значение CurrentValue = результат вычисления функции initial .
  2. Передать CurrentValue на вход функции condition , проверить условие и дать ответ true  или false
  3. Если результат вычисления condition равен false – закончить создание списка.
  4. Если результат вычисления condition равен true – создать очередной элемент списка по следующему правилу:
    1. Если параметр selector задан, то вычислить значение функции selector, получив на вход CurrentValue.
    2. Если параметр selector отсутствует, очередной элемент создаваемого списка будет равен CurrentValue.
  5. Вычислить значение функции next  (получив на вход CurrentValue) и присвоить переменной CurrentValue новое значение – результат этого вычисления.
  6. Перейти к шагу 2.

Интересует п.5. Возможно ли в теле функции next обратиться к значению CurrentValue, которое подано функции на вход? Как это сделать?
 
Перейти    
PQ. Применение фнкции Table.AddColumn к списку имён добавляемых полей
 
#
14.04.2020 11:51:15
Уважаемые форумчане, добрый день!

Прошу помощи. Есть список полей для добавления к исходной таблице. В процессе добавления образуемые столбцы необходимо заполнить.
Во вложенном файле Таблица1 - исходная, её нужно дополнить полями из таблицы2, строки заполнить значениями, выдаваемыми функцией.

Пример схематичный, вопрос в том, как организовать множественное применение табличной функции (в данном случае Table.AddColumn) к списку полей.
Возможно ли это?

Спасибо.
Изменено: quasarrr - 14.04.2020 12:12:54
 
Перейти    
Какие инструменты для решения задачи учёта номерных бланков выбрать (PQ, PP, VBA)
 
#
10.04.2020 14:08:09
Уважаемые форумчане, добрый день!

Решаю задачу учёта бланков сверхстрогой отчётности    :)
Суть: люди получают номерные бланки и далее их используют по назначению, сдают назад либо самым бессовестным образом теряют или портят (в приложенном файле на странице "Движение"). Факт получения обозначается новой записью в таблице и сопровождается обязательным проставлением даты получения, факт списания - проставлением даты списания и причины (основания) списания.
Далее руководство хочет ежемесячно получать отчёт вида (в приложенном файле на странице "Шаблон отчёта"):
- на начало месяца у ФИО было столько-то бланков с такими-то номерами,
- в течение месяца он получил столько-то бланков с такими-то номерами,
- в течение месяца с него списано столько-то бланков с такими-то номерами (при этом необходимо разбивка по причинам (основаниям) списания),
- на конец месяца у ФИО было столько-то бланков с такими-то номерами.

Предполагаю следующий алгоритм решения. К таблице "Движение" создаю дополнительные столбцы: "На начало", "Получил", "Израсходовал", "Потерял", "Испортил", "Вернул", "На конец", которые в зависимости от того, фигурировал ли данный бланк в данной категории в заданном периоде, заполняю либо номером бланка либо оставляю пустым. Например, в случае если дата получения сотрудником бланка лежит в периоде отчёта, ставлю в столбце "Получил" номер бланка, если же он получен ранее или позднее, то пусто. Поскольку номеров на самом деле много, прошу обратить внимание в отчёте на конструкции вида 5025 - 5028.

И тут классические муки выбора. Какие инструменты либо их сочетания использовать для решения задачи (VBA, PQ, PP)? Наверно, будет некий симбиоз...
В идеале, хотелось бы чтобы пользователь выбрал временной срез (ну, или на крайняк ввёл даты) и отчёт оперативно перезаполнился.

Собственно вопросы, уважаемые форумчане:
1. В какой среде (PP, PQ) лучше сделать добавление вышеперечисленных вычисляемых столбцов? Или задачу можно решить альтернативным способом?
2. В какой среде лучше сделать сбор номеров для организации диапазонов (5025 - 5028).
3. В какой среде лучше сделать заполнение отчёта по форме в файле? VBA и переносить данные с какой-нибудь сводной? Можно ли к отчёту прилепить временной срез?

Ограничение: на рабочих компах стоит EXCEL 2013.

Помогите принять решение. Не прошу решать задачу - это мой крест. Но за экспертное мнение по заданным вопросам буду премного благодарен.

Спасибо.
Изменено: quasarrr - 10.04.2020 14:10:56
 
Перейти    
Как выгрузить в Power Query таблицу из модели данных
 
#
08.03.2020 12:48:22
Уважаемые форумчане!

Подскажите, пожалуйста, есть ли принципиальная возможность вытащить из модели данных при помощи Power Query нужную таблицу (содержит исторически накопленные сведения), дополнить данными текущего месяца и закачать назад в модель данных без загрузки на лист?

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

Подскажите, в каком направлении копать, как заставить pq увидеть модель и скачать нужную таблицу?

Спасибо.
 
Перейти    
PQ. Формирование справочника курсов валют
 
#
28.02.2020 15:45:43
Уважаемые форумчане!

С помощью PQ формирую справочник курсов валют с сайта cbr за выбранный период по выбранным валютам. Что-то наколхозил, споткнулся на 2-х моментах.
Вроде запрос работает, но в конце  сообщает, что загрузка завершилась ошибкой. Хотя таблица, вроде, на листе формируется... Но в модель данных не загружается...

Далее, по логике, мне нужно два раза нажать на "Использовать первую строку в качестве заголовков", но для полного счасть в колонке "Код валюты" на второй строке должна быть запись "Код валюты". И вот смешно, но я не могу изменить значение в этом столбце и записи на нужное.

Поможите, кому не жалко. Пример прилагаю.
 
Перейти    
PQ. Как отлаживать пользовательские функции?
 
#
14.02.2020 23:34:35
Уважаемые форумчане!

Вопрос возник в результате попытки осмысления решения, предложенного на задачу участника под ником stanislove. Надеюсь, он не обидится на меня, если я сошлюсь на его задачу и предложенное решение в данной теме.

Задача была сформулирована в теме "Power Bi: посчитать разговоры сотрудников, между которыми была пауза более указанной ".
В результате было предложено решение, в котором активно используется достаточно сложная (прошу не кидаться помидорами), по крайней мере, состоящая из нескольких операторов пользовательская функция.

Вот запрос с содержанием этой функции:
Код
 Source = Excel.CurrentWorkbook(){[Name="Лист1"]}[Content],
    calc = Table.Group(Source, {"Источник (кто)"}, {"temp", (sub) =>
    let
        setOrder = Table.Sort(sub, {"Дата вызова", "Время начала разговора"}),
        addInfo = Table.AddColumn(setOrder, "temp", each 
            [start = [Дата вызова] + Duration.From([Время начала разговора]),
             finish = start + #duration(0, 0, 0, [#"Продолжительность разговора (сек)"])
        ]),
        baseData = Table.ExpandRecordColumn(addInfo[[temp]], "temp", {"start", "finish"}),
        before = Table.ToColumns(Table.RemoveLastN(baseData, 1)),
        after = Table.ToColumns(Table.RemoveFirstN(baseData, 1)),
        result = Table.FromColumns(before & after)
    in
        Table.AddColumn(result, "Column5", each Duration.TotalSeconds([Column3] - [Column2]))
    }),
    #"Expanded {0}" = Table.ExpandTableColumn(calc, "temp", {"Column1", "Column2", "Column3", "Column4", "Column5"}, {"Column1", "Column2", "Column3", "Column4", "Column5"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded {0}",{{"Column1", "Звонок1"}, {"Column2", "Завершён1"}, {"Column3", "Звонок2"}, {"Column4", "Завершён2"}, {"Column5", "Простой (сек)"}})
in
    #"Renamed Columns"

Собственно, к вопросу.
Пользовательская функция достаточно сложная. Очевидно её нужно отлаживать. Во избежание...

Вопрос: но как? Какие механизмы?
Есть ли способ пройти пользовательскую функцию по шагам, чтобы посмотреть, как отрабатывает каждый оператор, входящий в  функцию? А то в окне "примененные шаги" вся функция, совместно с оператором вызова - это всё один большой шаг...
 
Перейти    
Power query изменение значений в поле таблицы в зависимости от значения этого поля и других полей
 
#
06.02.2020 10:50:02
Добрый день.

Новичок в pq, прошу помощи.

Какой функцией можно изменить значение в поле1 таблицы для ячеек в которых: поле1 = null, а поле2 <> null на фиксированное значение "получилось!" для каждой записи таблицы.

Наткнулся, что можно использовать функцию Table.AddColumn, но хотелось бы без добавлений столбцов обойтись.
 
Перейти    
Вставить данные, полученные в результате выполнения запроса в Power Query, в исходную умную таблицу
 
#
03.02.2020 13:08:02
Добрый день!

Поскольку вопрос теоретический, думаю, файл с примером нужен не особенно. Суть в следующем.

Есть некий объект (изделие), имеющий уникальный идентификационный номер (ID). Информация о появлении (выпуске) изделия с указанием ID хранится в умной таблице Excel. Изделие может быть вдальнейшем использовано, либо испорчено (брак).
Информация об использовании изделий, периодически выгружается из большой базы данных производственной службы за период (неделя). Естественно с указанием ID, а таккже указанием ряда дополнительных показателей. Выгрузка из БД происходит в таблицу excel.
В свою очередь, информация о порче (браке) заводится в умную таблицу "врукопашную".
Задача состоит в том, чтобы перетянуть в умную таблицу показатели выгрузки из базы данных.

Сейчас это реализовано с помощью кода VBA. Идёшь по выгрузке, берёшь изделие, переходишь в умную таблицу, ищешь ID, перетягиваешь информацию. И всё это по каждой позиции выборки. Долго и неэффективно.
Попробовал реализовать задачи отбора значений и связки между данными умной таблицы и выгрузки в Power Query. Получилось значительно веселей. Выгрузил на лист, получил записи, которые должны быть вставлены в умную таблицу. Или, точнее, получил записи умной таблицы, которые должны быть скорректированы.

Собственно, вопрос. Есть ли механизм переноса информации из запроса в таблицу - источник без использования VBA, например, с помощью Power Query? Или ещё как-то?
В последнее время склоняюсь к тому. что несмотря на предельную гибкость, предоставляемую VBA, эффективность программ оставляет желать.

Попытка сохранить результаты запроса на самой первоначальной умной таблице (фактически, перезаписать таблицу) не увенчалась успехом, что, наверно, логично.
 
Перейти    
Страницы: 1
Наверх