Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1 2 След.
RSS
Приблизительный поиск по нескольким условиям
 
#1
21.02.2019 23:24:19
Приветствую, друзья. Прошу помощи, зашел в тупик.

Необходимо в таблице определить тариф. Тариф выбирается по максимальному значению- если цена больше по весу- указываем цену за вес, если по объему- за объем.
Для удобства тарифы с листа "Тариф" перевел в построчный справочник, на листе "справочник"
Я никак не могу  найти решение в данной задаче, с двумя условиями и точным поиском проблем нет, но вот когда  нужно искать город точно, а вес приблизительно- все слетает.

Рассматриваю решение любым способом
Нужно бежать со всех ног, чтобы только оставаться на месте, а чтобы куда-то попасть, надо бежать как минимум вдвое быстрее!
 
 
 
#2
21.02.2019 23:50:33
Может так?
=MAX(LOOKUP($A2;Справочник!$E$2:$E$11;INDEX(Справочник!$G:$G;MATCH($C2;Справочник!$B:$B;0)):INDEX(Справочник!$G:$G;MATCH($C2;Справочник!$B:$B;0)+9))*$A2;LOOKUP($B2;Справочник!$F$2:$F$11;INDEX(Справочник!$H:$H;MATCH($C2;Справочник!$B:$B;0)):INDEX(Справочник!$H:$H;MATCH($C2;Справочник!$B:$B;0)+9))*$B2)

Или прямо так из тарифа, с некоторой модификацией
=MAX(LOOKUP($A4;Тариф!$J$3:$J$12;INDEX(Тариф!$B$3:$I$12;;MATCH("*"&$C4&"*";Тариф!$B$1:$I$1;0)))*$A4;LOOKUP($B4;Тариф!$K$3:$K$12;INDEX(Тариф!$B$3:$I$12;;MATCH("*"&$C4&"*";Тариф!$B$1:$I$1;0)+1))*$B4)
Изменено: БМВ - 21.02.2019 23:58:35
По вопросам из тем форума, личку не читаю.
 
 
 
#3
21.02.2019 23:57:49
БМВ,ох. Круто. Сам бы не дошел. Спасибо за помощь и уделенное время!
Нужно бежать со всех ног, чтобы только оставаться на месте, а чтобы куда-то попасть, надо бежать как минимум вдвое быстрее!
 
 
 
#4
21.02.2019 23:59:10
см. второй вариант. и проще и …. ну на выбор :-)
По вопросам из тем форума, личку не читаю.
 
 
 
#5
22.02.2019 00:01:10
БМВ,Второй- вообще крутой!! Спасибо! Честно говоря, я и не думал, что не используя Power Query, из таблички тарифа можно это было вытащить формулами
Нужно бежать со всех ног, чтобы только оставаться на месте, а чтобы куда-то попасть, надо бежать как минимум вдвое быстрее!
 
 
 
#6
22.02.2019 00:03:57
Цитата
Dyroff написал:
Честно говоря, я и не думал, что не используя Power Query, из таблички тарифа можно это было вытащить формулами
А я тоже самое думаю, о PowerQuery :-)
Во втором варианте есть минус, правда в примере его нет, но ….. Может получится что поиск *пункт* даст некорректный результат при вхождении . например *омск* найдет Томск.  тут только менять шапку, добавляя разделитель в начало и конец " Сковородино, Б.Невер, Соловьевск, Тында," и искать "* Б.Невер,*"
Изменено: БМВ - 22.02.2019 00:10:07
По вопросам из тем форума, личку не читаю.
 
 
 
#7
22.02.2019 00:05:49
БМВ, :D  
Нужно бежать со всех ног, чтобы только оставаться на месте, а чтобы куда-то попасть, надо бежать как минимум вдвое быстрее!
 
 
 
#8
04.03.2019 18:37:52
БМВ,Михаил, вот какая штука обнаружилась.
Груз весом 14 кг и объемом 0,11м3 уже переходит из категории  "до 20 кг, до 0,1м3" в следующую.(так как объем превысил 0,1м3)
А расчет все равно идет по первой.
Изменено: Dyroff - 04.03.2019 18:41:40
Нужно бежать со всех ног, чтобы только оставаться на месте, а чтобы куда-то попасть, надо бежать как минимум вдвое быстрее!
 
 
 
#9
04.03.2019 18:47:34
Гм, как раз с месяц назад такое же ваяли для ДХЛовских тарифов.
Если никто не поможет, то завтра гляну дам решение в PQ. Там с виду не сложно. Щас с телефона не смогу. А что ваш перевозчик такую штуку как объемный вес не использует?
Изменено: PooHkrd - 04.03.2019 18:48:20
Вот горшок пустой, он предмет простой...
 
 
 
#10
04.03.2019 19:04:14
PooHkrd,Спасибо , очень интересно посмотреть на решение через Power Query.

Именно этот перевозчик не использует. Выбираем тариф по максимальному, либо по весу, либо по объему.

Примечание:
Если груз попадает в категорию до 20 кг и до 0,1м3 то тариф- не перемножается на вес или объем, там просто константа (1300 например) А вот во всех других цена за каждый кг или за каждый кубометр.

В формуле я это поправил сам, но в Power Query, боюсь могу завязнуть)
Нужно бежать со всех ног, чтобы только оставаться на месте, а чтобы куда-то попасть, надо бежать как минимум вдвое быстрее!
 
 
 
#11
04.03.2019 19:06:06
Чего там вязнуть? Главное все по плоским таблицам растащить, а дальше пара-тройка джойнов и условный столбец. Ну, в общем до завтра.
Вот горшок пустой, он предмет простой...
 
 
 
#12
04.03.2019 19:11:08
PooHkrd,я не знаю, как приблизительный поиск организовать в Power Query, точный- да, но вот как с приблизительным быть- не знаю.
Нужно бежать со всех ног, чтобы только оставаться на месте, а чтобы куда-то попасть, надо бежать как минимум вдвое быстрее!
 
 
 
#13
04.03.2019 19:26:35
Цитата
Dyroff написал:
Тариф выбирается по максимальному значению- если цена больше по весу- указываем цену за вес, если по объему- за объем.
Вот тут собака.
Что означает эта фраза? Если выбирается наибольший прайс за единицу, то одно, если все ж стоимость итоговая  (что ,было  и реализовано), то иное.
Цитата
Dyroff написал:
Выбираем тариф по максимальному, либо по весу, либо по объему
тариф или стоимость?
Как нужно?
Сам поиск находит все корректно, потом перемножает на вес или объем и выбирает максимум .
И это правильно, так как если по иному, то введите объем 0,09, что должно получится?
Ну вариант с иным алгоритмом в приложении.
Изменено: БМВ - 04.03.2019 19:55:09
По вопросам из тем форума, личку не читаю.
 
 
 
#14
04.03.2019 21:15:29
БМВ,Запутал я Вас... Надо сразу было мне задачу описывать полностью..

Стоимость перевозки определяется по следующему алгоритму:
Если груз по своим характеристикам веса и объема  попадает в категорию "0,01-0,1 куб.м    1-20 кг" -стоимость будет равна тарифу указанному в таблице значению (без переумножения веса и объема и определения максимального значения) и составит 1300 руб.
Например:
Для города  Алдан: груз весит 14 кг и его объем 0,05м3 - стоимость перевозки составит 1300 руб (любой груз весом до 20 кг и объемом до 0,1м3 будет стоить в Алдане 1300 руб)

Все грузы, чьи характеристики превышают 20кг или 0.1м3 рассчитываются следующим образом:

Если вес  превышает 20 кг или объем превышает 0,1м3 - следует определить в какую категорию тарифа попадает груз.
Далее тариф указанный в таблице за вес умножить на вес груза, тариф за объем умножить на  объем груза. Стоимость определяется по максимальной сумме.

Например:
Для города Алдан: груз весит 14 кг, но при этом его объем равен 0,11м3 - груз попадает в категорию "0,1-0,5 куб.м.       20-100 кг" (по весу груз попадает еще в первую категорию, но объем уже превышен, поэтому определяем категорию по объему)
Расчет будет следующий:
14*30 =420
0,11*6000 =660
Определяем максимальное из этих двух сумм, Стоимость равна 660 руб.
Изменено: Dyroff - 04.03.2019 21:18:23
Нужно бежать со всех ног, чтобы только оставаться на месте, а чтобы куда-то попасть, надо бежать как минимум вдвое быстрее!
 
 
 
#15
04.03.2019 21:35:26
вроде то что нужно
только хотел бы увидеть решение в Power Query
 
 
 
#16
04.03.2019 21:47:20
artyrH,  Да, спасибо за вариант!
Немного поправил, добавив:

=ЕСЛИ(И(A2<20;B2<=0,1);ИНДЕКС(Справочник!$G$2:$G$261;ПОИСКПОЗ(Данные!C2;Справочник!$B$2:$B$261;0));МАКС(ЕСЛИОШИБКА(A2*ИНДЕКС(Справочник!$G$1:$G$1000;АГРЕГАТ(15;6;СТРОКА($E$1:$E$2000)/(B2<Справочник!$J$1:$J$1000)/(A2>=Справочник!$E$1:$E$1000)/(A2<=Справочник!$I$1:$I$1000)/(C2=Справочник!$B$1:$B$1000);1));0);ЕСЛИОШИБКА(B2*ИНДЕКС(Справочник!$H$1:$H$1000;АГРЕГАТ(15;6;СТРОКА($E$1:$E$2000)/(A2<Справочник!$I$1:$I$1000)/(C2=Справочник!$B$1:$B$1000)/(B2>=Справочник!$F$1:$F$1000)/(B2<=Справочник!$J$1:$J$1000);1));0)))

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

Сам жду вариант на Power Query   интересно....
Нужно бежать со всех ног, чтобы только оставаться на месте, а чтобы куда-то попасть, надо бежать как минимум вдвое быстрее!
 
 
 
#17
04.03.2019 21:56:04
.
Код
=МАКС(ЕСЛИОШИБКА(B2*ПРОСМОТР(2;1/(A2<Справочник!$I$1:$I$1000)/(C2=Справочник!$B$1:$B$1000)/(B2>=Справочник!$F$1:$F$1000)/(B2<=Справочник!$J$1:$J$1000);(Справочник!$H$1:$H$1000));0);ЕСЛИОШИБКА(A2*ПРОСМОТР(2;1/(B2<Справочник!$J$1:$J$1000)/(A2>=Справочник!$E$1:$E$1000)/(A2<=Справочник!$I$1:$I$1000)/(C2=Справочник!$B$1:$B$1000);Справочник!$G$1:$G$1000);0))
 
 
 
#18
04.03.2019 22:00:54
В формулы не вникал, в глаза бросилось следующее:
Цитата
Dyroff написал:
Для города  Алдан: груз весит 14 кг и его объем 0,05м3 - стоимость перевозки составит 1300 руб (любой груз весом до 20 кг и объемом до 0,1м3 будет стоить в Алдане 1300 руб)
...
Для города Алдан: груз весит 14 кг, но при этом его объем равен 0,11м3 - груз попадает в категорию "0,1-0,5 куб.м... Стоимость равна 660 руб.
Это нормально вообще, что груз в 2 раза более объемный стОит в 2 раза дешевле?
Изменено: Казанский - 04.03.2019 22:02:18
 
 
 
#19
04.03.2019 22:06:10
Казанский,угу) вот такая тарификация странная)
Там просто после 20 начинается переход, и начинается прогрессивная шкала,  с каждым кг. увеличивается стоимость. Но вот сразу за границами 20 кг- согласен, очень нелогично. Но, уж как есть)

artyrH, что-то Ваша формула мне 0 выдает :)

Цитата
БМВ написал: Ну вариант с иным алгоритмом в приложении.
Да, все верно. Спасибо большое за помощь! и интересное решение.
Нужно бежать со всех ног, чтобы только оставаться на месте, а чтобы куда-то попасть, надо бежать как минимум вдвое быстрее!
 
 
 
#20
04.03.2019 22:10:09
Цитата
Dyroff написал:
он мне 0 выдает по этому варианту:)
не знаю не знаю, я Вам другую формулу давал)
 
 
 
#21
04.03.2019 22:18:43
=MAX(INDEX(Справочник!$G:$G;MATCH($C3;Справочник!$B:$B;0)-1+MAX(MATCH($A3;Справочник!$E$2:$E$11);MATCH($B3;Справочник!$F$2:$F$11)))*$A3;INDEX(Справочник!$H:$H;MATCH($C3;Справочник!$B:$B;0)-1+MAX(MATCH($A3;Справочник!$E$2:$E$11);MATCH($B3;Справочник!$F$2:$F$11)))*B3)
или массивная
=MAX(INDEX(Справочник!$G:$H;MATCH($C2;Справочник!$B:$B;0)-1+MAX(MATCH($A2;Справочник!$E$2:$E$11);MATCH($B2;Справочник!$F$2:$F$11));)*$A2:B2)
или для варианта 2
=MAX(INDEX(Тариф!$B$3:$I$12;MAX(MATCH(A2;Тариф!$J$3:$J$12);MATCH(B2;Тариф!$K$3:$K$12));MATCH("*"&$C2&"*";Тариф!$B$1:$I$1;0))*A2;INDEX(Тариф!$B$3:$I$12;MAX(MATCH(A2;Тариф!$J$3:$J$12);MATCH(B2;Тариф!$K$3:$K$12));MATCH("*"&$C2&"*";Тариф!$B$1:$I$1;0)+1)*B2)
Изменено: БМВ - 04.03.2019 22:28:29
По вопросам из тем форума, личку не читаю.
 
 
 
#22
04.03.2019 22:21:49
БМВ, Сколько вариантов))
Спасибо большое) буду пробовать

Цитата
artyrH написал: не знаю не знаю, я Вам другую формулу давал)
Эммм... ну в сообщении #17 именно она:)
Нужно бежать со всех ног, чтобы только оставаться на месте, а чтобы куда-то попасть, надо бежать как минимум вдвое быстрее!
 
 
 
#23
04.03.2019 22:29:00
Цитата
Dyroff написал:
в сообщении #17 именно она:)
согласен, суть та, диапазоны только..)
 
 
 
#24
04.03.2019 22:29:01
Я выше сократил. Убрал лишнее.
По вопросам из тем форума, личку не читаю.
 
 
 
#25
04.03.2019 22:31:55
БМВ,массивная-прям крутая!
Нужно бежать со всех ног, чтобы только оставаться на месте, а чтобы куда-то попасть, надо бежать как минимум вдвое быстрее!
 
 
 
#26
04.03.2019 22:37:44
Цитата
Dyroff написал:
крутая!
короткая, но мне больше нравится последняя, так как не требует коррекции таблицы тарифов.
По вопросам из тем форума, личку не читаю.
 
 
 
#27
04.03.2019 22:40:18
БМВ, это да, конечно, очень удобно
Нужно бежать со всех ног, чтобы только оставаться на месте, а чтобы куда-то попасть, надо бежать как минимум вдвое быстрее!
 
 
 
#28
05.03.2019 12:51:56
БМВ, попробовал в вашем файле из поста №21 сочетание 14 кг и 0.09 м3. 18200 руб это нормально? если 19 кг и тот же объем, то цена 24700.
Dyroff, прежде чем что-то лепить, хочу уточнить:
1. сначала мы считаем вес округленный до КГ, умножаем на цену тарифа за КГ из соответствующей весовой категории (не накопительно по каждой категории, как это обычно делается, а именно весь вес умножается на тариф за кг из этой категории?)
2. потом берем объем без округлений и умножаем на тариф по соответствующей категории (не накопительно по каждой категории, как это обычно делается, а именно весь объем умножается на тариф за 1 м3 из этой категории?)
3. выбираем максимум по цене
4. проверяем, если груз в категории до 20 кг И до 0.1 м3 то берем цену из этой категории
З.Ы. ничего более странного из логистических тарифов я еще не видел, при том что вот сейчас ковыряюсь в как минимум шести тарифных сектах крупнейших ТК РФ.
Вот горшок пустой, он предмет простой...
 
 
 
#29
05.03.2019 13:09:06
Да все верно:
На всякий случай еще раз про категорию до 20 кг и до 1м3:
По ней просто берем сумму указанную в тарифе -это фикс, Ничего не переумножаем.
По остальным все верно - определяем категорию, по наибольшему весу или объему, потом - кг*тариф,  м3* тариф     макс(тариф кг; тариф м3)

И ещё одно важное условие, которое появилось вот только что:
Если итоговая сумма меньше чем сумма по самому первому тарифу (который до 20 кг и до 1м3) - то ставим сумму по первому тарифу.
Другими словами- минимальная сумма= стоимости по тарифу до 20 кг и до 1м3



Цитата
PooHkrd написал:
З.Ы. ничего более странного из логистических тарифов я еще не видел, при том что вот сейчас ковыряюсь в как минимум шести тарифных сектах крупнейших ТК РФ
Я в первой двойке из них) Если брать федеральный уровень.
Это тарифы для северных городов до которых нет нормального регулярного сообщения. Там своеобразные тарифы:)
Изменено: Dyroff - 05.03.2019 13:13:51
Нужно бежать со всех ног, чтобы только оставаться на месте, а чтобы куда-то попасть, надо бежать как минимум вдвое быстрее!
 
 
 
#30
05.03.2019 13:15:50
Цитата
PooHkrd написал:
из поста №21 сочетание 14 кг и 0.09 м3. 18200 руб это нормально? если 19 кг и тот же объем, то цена 24700.
Я тоже был удивевлен
Цитата
БМВ написал:
И это правильно, так как если по иному, то введите объем 0,09, что должно получится?
и
Цитата
Казанский написал:
Это нормально вообще, что груз в 2 раза более объемный стОит в 2 раза дешевле?
но что так что этак при такой сетке получается ерунда.
Возможно это отсекающий тариф, типа с мелочью не удобно работать, ведь это реально гемор при перевозках, но если хотите, то задорого. Но есть лайвхак, добавьте пузырчатки до 0,1m3 и отправьте теже 14кu почти даром :-)

Цитата
Dyroff написал:
Я в первой двойке из них) Если брать федеральный уровень.
в личку если не секрет в какой?
Цитата
Dyroff написал:
Если итоговая сумма меньше чем сумма по самому первому тарифу (который до 20 кг и до 1м3) - то ставим сумму по первому тарифу.
-тогда это все поясняяет.


P.S. У UPS есть понятие объемный вес. Там объем делится на 5000 и результат приравнивается к КГ, перемножается на прайс и из вес на тариф и объемный вес на тариф выбирается большее.
=MAX(INDEX(Справочник!$G:$H;MATCH($C2;Справочник!$B:$B;0)-1+MAX(MATCH($A2;Справочник!$E$2:$E$11);MATCH($B2;Справочник!$F$2:$F$11));)*$A2:B2;
INDEX(Справочник!$G:$H;MATCH($C2;Справочник!$B:$B;0);)*$A2:B2)
ну и
=MAX(INDEX(Тариф!$B$3:$I$12;MAX(MATCH(A2;Тариф!$J$3:$J$12);MATCH(B2;Тариф!$K$3:$K$12));MATCH("*"&$C2&"*";Тариф!$B$1:$I$1;0))*A2;INDEX(Тариф!$B$3:$I$12;MAX(MATCH(A2;Тариф!$J$3:$J$12);MATCH(B2;Тариф!$K$3:$K$12));MATCH("*"&$C2&"*";Тариф!$B$1:$I$1;0)+1)*B2;
INDEX(Тариф!$B$3:$I$12;1;MATCH("*"&$C2&"*";Тариф!$B$1:$I$1;0))*A2;INDEX(Тариф!$B$3:$I$12;1;MATCH("*"&$C2&"*";Тариф!$B$1:$I$1;0)+1)*B2)
Изменено: БМВ - 05.03.2019 13:26:20
По вопросам из тем форума, личку не читаю.
 
 
 
Страницы: 1 2 След.
Наверх