Давно, в PQ написал «запрос», рассчитывающий распределение величины уровня складского запаса по временным интервалам (в разных контекстах).

От 0 до 45 дн – 1000руб

От 46 до 90 дн – 2000руб

И т.д.

Логика расчета простая, запас в на дату отчета, делим на среднедневные продажи и полученное значение распределяем по временным интервалам.

Сейчас возникла потребность добавить «срез», в котором можно выбрать «период» за который должна рассчитаться среднедневная продажа. Для решения задачи «расчётчик» нужно перенести из PQ в PP. А с PP я не «очень»

Исходные данные – таблица продаж, и таблица с остатками.

Я создал 2 виртуальные таблицы

1я с остатками

EVALUATE
 var t1 = 
     ADDCOLUMNS(SUMMARIZE (
         'Stock',
         'Stock'[ЦС],
         'Stock'[Код]),
         "Кол",
         CALCULATE(SUM('stock'[Запас])))
  RETURN 
 t1

2я со среднедневной отгрузкой

EVALUATE
 var t2 = ADDCOLUMNS(
     SUMMARIZE (
     'Sale',
     'Sale'[Склад],
     'Sale'[Код]),
     "AV_S", 
         CALCULATE(
             DIVIDE (
                 [реал],
                 SUMX (
                     VALUES ( 
                         'sale'[Дата] ),
                         CALCULATE (
                             MAX ( 'sale'[Кол-во дней] ) ) ) )))
         
  RETURN 
  t2

Для расчета уровня запаса и его декомпозиции по временным интервалам, как я понял, нужно объединить эти виртуальные таблицы по ключам «Склад», «Код». Вот с этим возникли сложности.

Прошу оказать помощь и подсказать варианты решения.

Спасибо.

Добрый день.

Есть задачка распределить потребность в sku  по поставщикам.

Исходные данные:

Требуемое кол-во sku

Желаемая доля поставщика в закупке sku

Минимальное кол-во, от которого поставщик готов запустить заказ в производство (МинНЗ)

Максимальное кол-во, возможности производства поставщика. (МахНЗ )

За ключевое значение для достижения желаемой цели я выбрал корректировку “Желаемая доля поставщика в закупке sku” - если вышли за границы диапазона  минимальное - максимальное кол-во доступное для заказа.

Как пересчитать доли с учетом ограничения по максимальному кол-ву я реализовал следующий алгоритм:

Рассчитываем:

Верх_гр = МахНЗ/ Требуемое кол-во sku

Полученное значение (Верх_гр) сравниваем с Желаемой долей

Если Верх_гр <= Желаемая доля тогда по данному поставщику значение желаемой доли меняем на Верх_гр.

По остальным строкам пересчитываем долю:

(Желаемая доля / сумма (желаемая доля по оставшимся строкам))*(1- сумма (желаемая доля по замененным значениям на Верх_гр)

Далее, алгоритм повторяем пока не останется значений где Верх_гр <= Желаемая доля.

Пример (сформированный на «коленке» во вложении)

Не понимаю, как сформировать алгоритм учитывающий одновременно верхние и нижние границы допустимого диапазона.

Если реализовывать перерасчет отдельно для минимальной границы и максимальной границы, при о переделённых значениях процесс подбора «долей» может работать бесконечно.

Прошу оказать помощь в создании алгоритма. (идеально в PQ)

Столкнулся с проблемой, не пойму как ее решить… Прошу оказать помощь.

Дано:

1.       Таблица с данными по продажам: номер недели (Week) и Реализация (Sale)

Данные представлены за 10 недель

2.       Справочник «Неделя» (Week)

Данные представлены за 3 недели.

3.       В модели данных PowerPivot таблицы связаны между собой по столбцу Week

Сформированная на листе EXCEL сводная таблица со столбцами

Week (справочник) и Sale (Таблица с данными по продажам) и срез, по столбцу Week (справочник)

возвращают запись (пусто).

Подскажите, пож-та, вариант решения, позволяющий убрать из среза и таблицы запись (пусто).

PS. Без внесения изменений в «меры».

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

=var curweek=MAX('Week'[Week])
var rd=CALCULATE(SUM('Sale'[Sale]);'Sale'[week]=curweek)
return
rd

Но в боевом файле слишком много мер.  Хотелось бы найти более простой вариант решения.

Пример файла во вложении

Спасибо.

Добрый день.

В ходе решения задачи по парсингу складских остаток возникла потребность из html вытянуть часть текста между разделителями по нескольким совпадениям. На просторах интернета нашел код (см. пример), адаптировал его и получил желаемый результат. Но, не могу понять, как работает функция с рекурсией. Для саморазвития, хочу разобраться.  Если не сложно, распишите, пож-та, принцип рекурсии на примере данного кода. Спасибо.

Добрый день.

Пытаюсь усовершенствовать функцию, которая по регулярному выражению извлекает текст из html кода.

Столкнулся со следующими сложностями (не разбираюсь в javascript):

1.       Не могу понять как к регулярному выражению добавить модификаторы «/gim»

2.       Не получается склеить несколько значений (не понимаю как в Join указать разделитель заданный в функции)

Возможно, п.2 не получилось реализовать, из-за проблемы п.1 –  функция возвращает только 1е значение.

В примере функция возвращает одну цену.

Необходимо вернуть 2 цены, разделенные через «;» - 12733; 14533.

Прошу оказать помощь. Спасибо.

Иногда возникает потребность парсить цены с сайта https://leroymerlin.ru/

Предыдущий парсер (макрос + регулярные выражения) перестал работать из-за реализации защиты.

По подсказкам с сайта «https://excelvba.ru/programmes/Parser/manuals/errors/protection» написал следующий код

let    url = "http://178.248.234.184/", 
headers = [#"Accept" = "text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.9",        #"Accept-Language" = "en-US,en;q=0.9",        #"Connection" = "keep-alive",        #"Upgrade-Insecure-Requests" = "1",        #"Origin" = "http://178.248.234.184",       #"Referer" = "http://178.248.234.184/product/rozetka-vstraivaemaya-legrand-valena-s-zazemleniem-s-kryshkoy-cvet-be...;,        #"User-Agent" = "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/107.0.0.0 Safari/537.36"    ],
parameters = [    ],
response =Web.Contents(url, [        Query = parameters,        Headers = headers    ]) 
in   response

Вышеуказанный код выдает ошибку: «Доступ к сайту запрещен»

Пытаюсь обойти защиту, но столкнулся с проблемой передачи данных через cookie:

- не понимаю как передать данные через cookie.

- не понимаю  какие именно данные нужно передать через cookie.

PS. Набор данных cookie могу скопировать из браузера….

Прошу оказать помощь.

Спасибо.

Прошу оказать помощь в написании меры.

Дано:

Таблица с продажами.  (Год, неделя, Реализация)

Срез по столбцу «Неделя»

В «срезе» может быть выбрано рандомное количество недель и не по порядку…

Нужна «мера» возвращающая последние 7 значений «Реализация» по выбранным в «срезе» неделям. (для примера ГОД=2021)

Пример и мера возвращающая  последние 7 значений (без учета «среза») во вложении..

Спасибо.

Не очень у меня получается с преобразованием таблиц в PQ.

Прошу оказать посильную помощь в решении след задачи:

Имеется таблица с результатами испытаний.

Новые результаты измерений заносятся в виде новых столбцов с названиями: Испытание ХХ, Результат ХХ.

Необходимо преобразовать таблицу в плоскую (пример во вложении),  с учетом возможного увеличения кол-ва столбцов в будущем…

Спасибо…

Предыстория:

Имеется таблица, где пользователь распределяет расходные материалы по произведенным продуктам. Набор расходных материалов меняется, добавляются новые продукты. Соответственно таблица меняется в размерах. (может увеличится кол-во столбцов и измениться кол-во строк.) Естественно, находятся «одаренные» личности, которые портят формулы, формирующие сравнение план-факт.

Решение данной проблемы вижу в формировании именованных динамических диапазонов

Задача, достаточно простая, но формула

=СУММ(ДВССЫЛ(АДРЕС(СТРОКА();4)&":"&АДРЕС(СТРОКА();вправо)))

в именованном диапазоне возвращает ошибку (в ячейке работает).

Формула со «СМЕЩ» работает корректно  

=СУММ(СМЕЩ(Лист2!C4;0;1;1;вправо-3)).

Но, т.к. вычисляемых значений очень много, при расчете «смещения» возрастает риск ошибки….

Подскажите, что нужно исправить в 1м варианте формулы….

Пример во вложении

Добрый день

Несколько лет назад написал макрос на VBA, который распределяет сверхнормативные запасы по складам компании.

Сейчас пытаюсь реализовать тоже самое в PQ  с помощью list.generate. Но к сожалению, знаний не хватает.

Задача:

Необходимо распределить сверхнормативные запасы по складам, где есть потребность.

Потребность и излишки уже рассчитаны и представлены в таблице. (см. пример)

Необходимо сформировать таблицу содержащую след данные

Код товара, наименование откуда переместить (Источник), Куда переместить (Потребитель), перемещаемое значение (кол-во)

Логика проста.

1.       Ищем максимальное количество в источнике и потребителе.

2.       Из полученных значений на шаге 1 определяем минимальное.

3.       Формируем запись код, наименование, откуда переместить (Источник), Куда переместить (Потребитель), перемещаемое значение (кол-во).

4.       Найденные значения в п.1 уменьшаем на перемещаемое кол-во

5.       Далее повторяем все шаги, пока сумма в столбцах (по коду) Источник или Потребитель не станет равной 0.

Прошу подсказать решение в PQ

Спасибо.

Добрый день.

Помогите, пож-та, с расчетом в PQ

Имеем таблицу с историей цен. (см. вложение)

Необходимо добавить столбцы:

- % изменения цены (столбец Изменение цены, %). Рассчитывается по формуле – «(текущее значение цены/предыдущее значение цены)-1». Если предыдущего значения цены нет, тогда возвращаем «пусто»

- изменение цены в абсолютном значении (столбец Изменение цены, руб). Рассчитывается по формуле – «текущее значение цены-предыдущее значение цены». Если предыдущего значения цены нет, тогда возвращаем «пусто»

- срок действия цены. (Срок действия цены). Рассчитывается по формуле – количество дней между датой начала действия текущей цены и датой начала действия следующей цены.

Если цена действует (следующей цены нет), тогда срок действия цены рассчитываем, как количество дней между Датой начала действия цены и текущей датой.

Спасибо.

Очень дано, написал макрос, который с внутреннего корпоративного сайта "забирает" справочник сотрудников компании.
В текущем месяце макрос перестал работать, по моему мнению,  из-за обновления Internet Explorer или завершением его поддержки.
(Теперь после запуска макроса открывается страница IE и макрос завершает работу с ошибкой)
"Рабочий пример" предоставить не могу, т.к. эта информация ДСП, да и доступа к ресурсам компании из "вне" нет.
Ниже часть кода,
Подскажите, пож-та, возможные варианты решения проблемы....

Dim oIE As InternetExplorerMedium, sHtml As String
Dim tmp, i As Long
Dim sAnswer As String, IE As Object, avArr, li As Long
Set oIE = CreateObject("InternetExplorer.Application") 'запускаем explorer
Set oIE = New InternetExplorerMedium
oIE.Visible = 0 'видимость explorer - 0-скрыть 1-показать
s = "****************" 'ссылка
oIE.Navigate (s) 'загружаем сайт
While oIE.Busy Or oIE.ReadyState <> READYSTATE_COMPLETE: DoEvents: Wend
Set NodeList = oIE.Document.getElementsByTagName("Input") ' выбираем все поля ввода
Set maPageHtml = oIE.Document: DoEvents: DoEvents
If oIE.LocationURL = s Then
maPageHtml.getElementsByName("USER_LOGIN").Item(0).Value = "********"
maPageHtml.getElementsByName("USER_PASSWORD").Item(0).Value = "*******"
NodeList(6).Click
End If
While oIE.Busy Or (oIE.ReadyState <> 4): DoEvents: Wend

    sAnswer = oIE.Document.body.innerHTML
    avArr = Split(sAnswer, Chr(10))
    schet = 0

Добрый день.
Столкнулся со странной ситуацией при объединении двух таблиц в PQ.

Странность заключается в том, что после объединения, в одном столбце (Столбец «Место») меняются значения. Есть подозрение, что данная проблема связана со способом формирования значений этого столба (Индекс строки)ю

Прошу Вас помочь и объяснить в из-за чего меняются значения после объединения и объяснить (показать) как сделать так, что бы значения не менялись?

Пример во вложении.

Спасибо.

Добрый день.

«Споткнулся на ровном месте». Суть проблемы ниже. Помогите пож-та

Переделываю дашборд, для работы на Mac OC. Меняю «меры» на расчеты в формулах excel

Столкнулся с проблемой построения диаграмм.

Имеем таблицу, в которой кол-во строк = количеству недель в году + заголовок таблицы

В части строк данные отсутствуют, т.к. данный период еще не наступил.

Соответственно нужно построить множество графиков на основании данных этой таблицы, так что бы на оси не отображались периоды за которые данных еще нет. И по мере появления данных они появлялись на графике.

Пытался использовать именованные диапазоны – Не получилось

Пример во вложении.