Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Power Query: как заменить количество, на количество кратное в описании с суммированием цены?
 
#1
21.07.2017 13:26:41
Добрый день, в файле есть столбец LEVEL3 там две группы товаров "EM" и "ГСМ".
В группе "ГСМ" есть масла с указанием цены за 1 литр, в столбце PART_NAMER идет описание товара с указанием фактической тары.
То есть за масло 5л канистре стоит ценник как за 1 л.

Необходимо сделать кол-во кратное фактической таре, то есть если количество стоит 20,65, то должно остаться кратное 5, то есть остается кол-во 4, а все что не кратно 5 не попадать в количество, и т.д. для 208л, 20 л.....
Цена соответственно так же должна умножаться на кратное кол-во, то есть в данном случае на 5.

Как-то можно так сделать в Power Query?

Файл с примером прикрепил
 
 
 
#2
21.07.2017 15:06:26
Честно говоря, не увидел "количество 20,65"  :)
Вы можете справа от таблицы руками написать хотя бы несколько значений, которые должны получиться?

Или вот: получается, что (для ГСМ) в столбце "Количество" у вас указано всегда количество литров, а в "Макс цена" - всегда цена за 1 литр. И у вас в последних круглых скобках (если они есть) в наименовании всегда указан объем тары. И надо Количество нацело разделить на объем, а Цену умножить на объём. Так?

P.S. Вы могли, кстати, и файл-источник "выгрузка наличия ниссан.xlsx" приложить, хотя бы с несколькими десятками строк, чтобы можно было запросы на ваших данных проверить...
Изменено: AndreTM - 21.07.2017 15:15:02
 
 
 
#3
21.07.2017 15:58:36
Если правильно понял, что было достаточно трудно:
Код
let
    Source = Excel.CurrentWorkbook(){[Name="тест3"]}[Content],
    Extract = Table.AddColumn(Source, "Кратность", each if Text.EndsWith([PART_NAMER],"L)") then Splitter.SplitTextByEachDelimiter({"L)","("}, null, true)([PART_NAMER]){1} else 1),
    ChType = Table.TransformColumnTypes(Extract,{{"Кратность", type number}, {"Количество", type number}, {"Макс цена", type number}}),
    WholeCans = Table.AddColumn(ChType, "К-во тары", each Number.IntegerDivide([Количество], [Кратность]), type number),
    Canned = Table.AddColumn(WholeCans, "Кратно таре", each [Кратность] * [#"К-во тары"], type number),
    Multiply = Table.AddColumn(Canned, "Сумма", each [Макс цена] * [Кратно таре], type number)
in
    Multiply

Единственно, не понял, что нужно, если количество меньше 1 единицы тары.
F1 творит чудеса
 
 
 
#4
21.07.2017 16:36:32
Цитата
Единственно, не понял, что нужно, если количество меньше 1 единицы тары.
Если кол-во меньше 1 единицы, а в описании указан к примеру 1L, то количество не кратно 1 и соответственно у этой позиции кол-во равно 0.
А в целом огромное спасибо!!! В Вашем файле вроде все так как и надо.
 
 
 
#5
21.07.2017 17:53:25
Цитата
Максим Зеленский написал:
что нужно, если количество меньше 1 единицы тары
Я бы порекомендовал добавить еще один столбец "Имеется в розлив"  :)
Код
NoWholeCans = Table.AddColumn(Multiply, "Имеется в розлив", each [Количество] - [Кратно таре], type number)
для указания остатка литров, чтобы общее количество сходилось...

P.S. Максим, а у вас тоже Text.BetweenDelimiters() неопределён в PQ для Excel?
Изменено: AndreTM - 21.07.2017 17:55:53
 
 
 
#6
21.07.2017 20:33:39
Цитата
AndreTM написал:
Text.BetweenDelimiters() неопределён в PQ для Excel?
Работает и в 2010 и 2016. Появилась в конце апреля 2017 - может обновления для Excel не встали? Или пора поставить свежую версию PQ, если не 2016 :)
 
 
 
#7
21.07.2017 23:17:30
Excel 2010
PQ 2.44.4675.281 (последний раз скачивал 12.06.17)

И нифига, "... не определена"  :cry:

--- добавлено ---
Три раза ха-ха... полез посмотрел - и действительно, в каталоге PQ все MashUp'ы от 22.02.17.
Скачал свежую 2.47.4766.281 (движок от 24.05.17), будем посмотреть...

--- добавлено ---
Да, всё получилось:
Код
let
    Source = Excel.CurrentWorkbook(){[Name="тест"]}[Content],
    Extract = Table.AddColumn(Source, "Кратность", each if Text.EndsWith([PART_NAMER],"L)") then Text.BetweenDelimiters([PART_NAMER],"(","L)",{0, RelativePosition.FromEnd}, 0) else 1),
    ChType = Table.TransformColumnTypes(Extract,{{"Кратность", type number}, {"Количество", type number}, {"Макс цена", type number}}),
    WholeCans = Table.AddColumn(ChType, "К-во тары", each Number.IntegerDivide([Количество], [Кратность]), type number),
    Canned = Table.AddColumn(WholeCans, "Кратно таре", each [Кратность] * [#"К-во тары"], type number),
    Multiply = Table.AddColumn(Canned, "Сумма", each [Макс цена] * [Кратно таре], type number),
    NoWholeCans = Table.AddColumn(Multiply, "Имеется в розлив", each [Количество] - [Кратно таре], type number)
in
    NoWholeCans
Изменено: AndreTM - 21.07.2017 23:47:32
 
 
 
#8
21.07.2017 23:48:45
Чтобы не быть голословным. Пример с новыми функциями для Text
 
 
 
#9
23.07.2017 12:51:15
Ну да, была необновленная PQ.
Но в смысле операций разницы нет, только syntax sugar. Эти три функции (before, after, between) внутри построены на стандартных сплиттерах
Изменено: Максим Зеленский - 23.07.2017 12:51:46
F1 творит чудеса
 
 
 
#10
24.07.2017 14:08:04
Цитата
Extract = Table.AddColumn(Source, "Кратность", each if Text.EndsWith([PART_NAMER],"L)") then Splitter.SplitTextByEachDelimiter({"L)","("}, null, true)([PART_NAMER]){1} else 1),
Добрый день, у меня есть другой файл, там без скобок указана фасовка, по этой формуле у меня все стало кратно 1, я так понимаю дело в отсутствии скобок в наименовании товара?
В прошлом файле:
МАСЛО МОТОРНОЕ NISSAN 5W30  (208 L)
В этом файле:
МАСЛО МОТОРНОЕ Helix HX8 Syn 5W40 SN 209L уже без скобок

Как должна измениться эта формула, что бы её можно было применить к наименованию без скобок?
 
 
 
#11
24.07.2017 14:19:47
Красное поудаляйте, должно работать.
Цитата
Extract = Table.AddColumn(Source, "Кратность", each if Text.EndsWith([PART_NAMER],"L)") then Splitter.SplitTextByEachDelimiter({"L)","("}, null, true)([PART_NAMER]){1} else 1),
Но учтите, что если название просто заканчивается на L (например, TOTAL), то нужны еще какие-то условия отбора. Например, по названию LEVEL3, проводить операции только для ГСМ
тогда примерно так
Код
Extract = Table.AddColumn(Source, "Кратность", each if [LEVEL3]="ГСМ" and Text.EndsWith([PART_NAMER],"L") then Splitter.SplitTextByEachDelimiter({"L","("}, null, true)([PART_NAMER]){1} else 1),
F1 творит чудеса
 
 
 
Страницы: 1
Читают тему
Наверх