Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Подсчет разницы значений между постоянно появляющимися новыми столбцами в Power Query или Power Pivot
 
#1
04.02.2022 21:11:57
Имеется: Таблица с данными ежемесячных складских запасов обработанная в Power Query (данные получаем из разных книг эксель "выгрузки складских запасов 1С"), получаемая от контрагента, где имеются значения количества остатков товара и артикулы, затем после обработки файлов со стоками подставляются цены товаров из прайс листа поставщика. На выходе получается объединенная таблица всех стоков товаров по датам и цена за единицу.

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

В формулах Power Query или Power Pivot не силен (только приступил к изучению), уже 5 дней просматриваю различные форумы и видео ролики, но пока не нашел подходящего решения, поэтому прошу помощи знатоков с этим решением.

Постарался описать свою задачу в примере в файле. Очень надеюсь на помощь!
 
 
 
#2
04.02.2022 23:03:27
Цитата
Tim 888 написал:
постоянно появляются новые столбцы со значениями по новым датам
Такого быть не должно. Могут появляться строки с данными на новые даты. Но не столбцы. Таблица данных в столбцах должна содержать измерения (дата, товар, количество, поставщик и тп), а в строках значения этих измерений. На вашем примере:

Задача комплексная. Больше подходит для платного раздела.
Цитата
Tim 888 написал:
могут возникнуть отрицательные значения, т.к. могло быть новое поступление товаров между этими датами
Без данных о приходах никакой аналитики не получится. Она будет неверной даже если все значения положительные.
 
 
 
#3
05.02.2022 05:54:41
Спасибо за быстрый ответ!

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

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

Цитата
Без данных о приходах никакой аналитики не получится. Она будет неверной даже если все значения положительные.
Данные о количестве прихода не учитываются, так как существуют только данные по отгрузкам данному контрагенту от поставщика, когда он их оприходует не известно. Приход можно увидеть лишь после обработки остатков на новую дату (контрагент отправляет остатки раз в месяц). Из данных по отгрузкам после обработки в Power Query формируется прайс лист (все данные объединяются в одну таблицу и удаляются дубликаты), затем эти цены участвуют в подсчете суммы товарных остатков.

Цитата
Задача комплексная. Больше подходит для платного раздела.
Готов обсудить, если сможете помочь.
 
 
 
#4
05.02.2022 10:47:50
Цитата
Tim 888 написал:
как вычислить разницу между количеством в остатках (столбец кол-во), учитывая два критерия артикул и дату и в каком виде потом будет выглядеть таблица?
Можно мерой, но, так как в датах остатков есть разрывы, то она будет не самой простой.
Можно немного преобразовать в PQ данные и меры будут простыми:


Если необходимо платное решение - попросите модераторов перенести тему в раздел Работа.
Изменено: surkenny - 05.02.2022 19:29:39
 
 
 
#5
05.02.2022 12:42:27
Цитата
surkenny написал:
Можно немного преобразовать в PQ данные и меры будут простыми:
В платном разделе я не увижу конечного решения)
Код
// Объединение стоков вариант 1
let
    Источник = Excel.CurrentWorkbook(){[Name="Стоки25"]}[Content],
    #"Измененный тип" = Table.TransformColumnTypes(Источник,{{"Дата", type date}, {"Артикул", type text}, {"Кол-во", Int64.Type}}),
    #"Добавленный запрос" = Table.Combine({#"Измененный тип", Стоки22, Стоки20}),
    #"Сортированные строки" = Table.Sort(#"Добавленный запрос",{{"Дата", Order.Ascending}}),
    #"Измененный тип1" = Table.TransformColumnTypes(#"Сортированные строки",{{"Цена", type number}}),
    #"Grouped Rows" = Table.Group(#"Измененный тип1", {"Артикул"}, {{"a", each 
 let a=_, b=Table.ToColumns(a),c={List.First(b{2})}&List.RemoveLastN(b{2}) in Table.FromColumns(b&{c},Table.ColumnNames(a)&{"Кол-во пред"})}})[a],
    Custom1 = Table.Combine(#"Grouped Rows")
in
    Custom1
НА МЯСО В ПРОФИЛЕ
 
 
 
#6
05.02.2022 13:45:30
Михаил Л, как когда-то где-то писал Максим Зеленский, нет уверенности, что группировка сохраняет корректный порядок строк. Я поэтому всегда перестраховываюсь сортировкой в группировке :) А так, такой же код :)
Код
  group = Table.Group (
    typed, 
    { "Артикул" }, 
    {
      {
        "data", 
        ( tbl ) =>
          [
            sort       = Table.Sort ( tbl, { "Дата", Order.Ascending } ), 
            remain     = sort[#"Кол-во"], 
            prevRemain = { remain{0} } & List.RemoveLastN ( remain, 1 ), 
            data       = Table.FromColumns ( Table.ToColumns ( sort ) & { prevRemain }, Table.ColumnNames ( sort ) & { "Кол-во пред" } )
          ][data]
      }
    }
  ), 
  combine = Table.Combine ( group[data] )

Но в файле ТС вообще жесть. Если остатки на 60 дат, 60 запросов делается? :)
Так что тут комплекс мероприятий для нормального рабочего инструмента нужен :)
 
 
 
#7
05.02.2022 16:25:36
Цитата
surkenny написал:
перестраховываюсь сортировкой в группировке
А сортировки достаточно? Может, индекс добавить)
Вот ссылка на тему Максима Зеленского
НА МЯСО В ПРОФИЛЕ
 
 
 
#8
05.02.2022 19:26:58
Цитата
Михаил Л написал:
А сортировки достаточно?
Даже для параноиков :) В любом случае, до или во время группировки сортировку сделать нужно.
Изменено: surkenny - 05.02.2022 19:27:37
 
 
 
#9
07.02.2022 18:48:00
Извиняюсь за поздний ответ и спасибо всем за ваши советы.

Добрый день Михаил! Ваше решение помогло мне получить желаемый результат, спасибо большое за помощь. Теперь постараюсь сам продолжить с ABC анализом.
 
 
 
Страницы: 1
Читают тему
Наверх