Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1 2 След.
RSS
Найти категорию груза на другом листе по двум критериям
 
#1
01.10.2018 22:43:43
Всем привет! Прошу помочь в решении моей задачи.
Необходимо, исходя из столбца D:D (Лист1), определить категорию груза на столбце B:B (Лист2).
Сложность заключается в том, что поиск происходит по двум критериям:
- название магазина (столбец A:A, Лист1);
- вес заказа (столбец D:D, Лист1);
- неточное соответствие(заказ от трех килограмм влезает в промежуток о 0 до 5.
Изменено: Strizh - 02.10.2018 11:17:42
 
 
 
#2
02.10.2018 00:28:32
=ПРОСМОТР(D6;Лист2!$C$2:$C$78;Лист2!$B$2:$B$78)
 
 
 
#3
02.10.2018 05:28:41
Михаил, огромное спасибо!
Немного изменил формулу:
=ЕСЛИ(ПОИСКПОЗ($B6;Лист2!$A$1:$A$999);
ПРОСМОТР($F6;Лист2!$C:$C;Лист2!$B:$B))
Изменено: Strizh - 02.10.2018 09:14:16
 
 
 
#4
02.10.2018 10:14:15
При добавлении второго магазина формула выдает некорректные значения. Друзья, кто-нибудь может помочь?
 
 
 
#5
02.10.2018 10:17:33
Пример в студию.
 
 
 
#6
02.10.2018 10:35:44
Информация во вложении.
Я пробовал завязать все на функции просмотр, но на 100-й строке формула упала с жалобой на большое количество  значений.
 
 
 
#7
02.10.2018 10:48:17
Вы ж написали
Цитата
Strizh написал:
Немного изменил формулу:=ЕСЛИ(ПОИСКПОЗ($B6;Лист2!$A$1:$A$999);
, а в файле опять берете весь столбец.
Ограничьте диапазон 500 или 1000 строк,
=ЕСЛИ(ПОИСКПОЗ($A6;
Лист2!$A6:$A500;
0);
ПРОСМОТР($D6;Лист2!C6:C500;Лист2!B6:B500))
 
 
 
#8
02.10.2018 10:51:29
В этом и заключается проблема. В Лист2 будет добавлено более 60 магазинов. У некоторых по 70 значений (Вес_1, Вес_2... Вес_70)
Будет более 3000 строк.  
 
 
 
#9
02.10.2018 12:08:09
Strizh, добрый день, вот как вариант - немного объемный, но если другого не подскажут вдруг...обязательно на листе сделать сортировку по двум столбцам сразу по А и С - от меньшего к большему... и последние максимальные велечены веса указать с запасом - т.е. у Вас 9 стоит а когда вес 120  то не ищет поставите  999 и будет работать( в примере сделал именно так) .
Не бойтесь совершенства. Вам его не достичь.
 
 
 
#10
02.10.2018 12:21:34
Цитата
Strizh написал:
Будет более 3000 строк.
даже 10 000 строк - не мильон, как в целом столбце.
 
 
 
#11
02.10.2018 12:27:45
a.i.mershik, огромное спасибо!

Михаил С., я не смог наладить работу формулы. Она продолжала ошибаться с ценами и названиями магазинов.  
Изменено: Strizh - 02.10.2018 12:41:20
 
 
 
#12
02.10.2018 12:32:22
Закрепите диапазоны поиска и просмотра:
=ЕСЛИ(ПОИСКПОЗ($A6;Лист2!$A6:$A500;0);ПРОСМОТР($D6;Лист2!$C$6:$C$500;Лист2!$B$6:$B$500))
 
 
 
#13
02.10.2018 12:54:08
Михаил С.,закрепил и внес в файл, информация во вложении.
Ошибка осталась, в столбце i сравниваются названия магазинов (H:H и A:A). Ложь присваивается в случае несовпадения.
 
 
 
#14
02.10.2018 13:46:10
Strizh,
Цитата
Strizh написал:
Ложь присваивается в случае несовпадения.
потому что просомтр ищет точное совпадение...
Не бойтесь совершенства. Вам его не достичь.
 
 
 
#15
02.10.2018 14:23:26
Цитата
Strizh написал:
Ложь присваивается в случае несовпадения.
Понятно.
Я не совсем верно понял вчера задачу. Чуть позже дам решение, в этом же сообщении.

зы. зачем вы так пишете формулу в строке формул?:

=ЕСЛИ( МАКС(ОКРУГЛВВЕРХ(B8;
                                                         );
                                                     ОКРУГЛВВЕРХ(C8;
                                                         ))=0;
               1;
               МАКС(ОКРУГЛВВЕРХ(B8;
                                                         );
                            ОКРУГЛВВЕРХ(C8;
                                                         )))

И, кстати, та часть, что я выделил красным, в ваших условиях будет ИСТИНА только, если и В8 и С8 будут пустые или равны нулю; имхо, эта проверка лишняя.
 
 
 
#16
02.10.2018 14:40:53
Вы про отступы? Это функция Plex. Они помогают разбираться в больших формулах, тут я случайно проставил.

Цитата
Михаил С. написал:
В8 и С8 будут пустые или равны нулю;
Партнер может передать заказы с нулевым весом, а наш склад забудет их взвесить. Эта формула служит некой подстраховкой в расчетах excel.
 
 
 
#17
02.10.2018 14:43:53
Strizh, вообще решение правильное в #9 ?
Не бойтесь совершенства. Вам его не достичь.
 
 
 
#18
02.10.2018 15:00:36
a.i.mershik, да, формула верно отображает название магазина. Сейчас в ней разбираюсь, чтобы понять как подвесить стоимость. Еще раз спасибо Вам!
Думаю, что у этого варианта должно быть более простое решение. Если у Михаила С. не получится, то завтра вкручиваю в файл Вашу формулу.
 
 
 
#19
02.10.2018 15:03:34
Strizh, да там особо ничего такого нет просто определяется первая и последняя ячейка нужного нам магазина посе чего по этому диапозону уже деаем не точный поиск функцией поискпоз и все... но сортировка нужна и максимальное значение так же
Не бойтесь совершенства. Вам его не достичь.
 
 
 
#20
02.10.2018 15:08:22
см. файл
 
 
 
#21
02.10.2018 15:38:15
Михаил С., это помогло, очень Вам признателен!

Слабо понял предназначение двоеточий, но буду разбираться.
Код
=ПРОСМОТР(D68;
ИНДЕКС(Лист2!$C$2:$C$13;ПОИСКПОЗ(A68;Лист2!$A$2:$A$13;0))[SIZE=26pt][B][COLOR=#ee1d24][SIZE=20pt]:[/SIZE][/COLOR][/B][/SIZE]
ИНДЕКС(Лист2!$C$2:$C$13;ПОИСКПОЗ(A68;Лист2!$A$2:$A$13;1));
ИНДЕКС(Лист2!$D$2:$D$13;ПОИСКПОЗ(A68;Лист2!$A$2:$A$13;0))[B][COLOR=#ee1d24][SIZE=20pt]:[/SIZE][/COLOR][/B]
ИНДЕКС(Лист2!$D$2:$D$13;ПОИСКПОЗ(A68;Лист2!$A$2:$A$13;1)))

a.i.mershik, Вашу формулу к сожалению, не пришлось применить. Я совершенно ничего не знал о ДВССЫЛ и подобных методах. Чувствую, что еще не раз перепишу ее прежде чем пойму. Спасибо Вам!
 
 
 
#22
02.10.2018 15:41:04
Strizh, ну у Михаил С : то как раз то же что и у меня только для восприятия у него гораздо проще.
первая и последняя запись нужного нам магазина, можно сказать диапазон ...до ":" первое значение(точное совпадение ищем "0")  после  ":" последнее (не четкий поиск 1)
Изменено: a.i.mershik - 02.10.2018 15:41:38
Не бойтесь совершенства. Вам его не достичь.
 
 
 
#23
02.10.2018 18:02:56
Как вариант: на втором листе делаете умную таблицу (для простоты динамического диапазона), столбец "категория вес" делаете последним, т.е. вместо "B" станет "D".

На первом листе в 6-й строке в пустой ячейке формула:
=ВПР(D6;СМЕЩ(Лист2!$A$1;ПОИСКПОЗ(A6;Таблица1[Название магазина];0);1;СЧЁТЕСЛИ(Таблица1[Название магазина];A6);3);3;ИСТИНА)
 
 
 
#24
02.10.2018 23:08:53
luksan, спасибо за совет! В этом месяце я почти закончил отчет, формула получилась очень капризной, ломается при любом ветерке, но работает:)
В следующем месяце обязательно попробую.
 
 
 
#25
03.10.2018 09:09:45
Друзья, прошу еще чуть-чуть помочь.
По прежнему принципу формула считала следующим образом:
от 0 до 1 кг цена равнялась 100
от 1 до 3 кг цена тоже равнялась 100 рублей. Фактически, это является ошибкой:(
Есть какая-то возможность переделать формулу для учета двух столбцов на листе БРЕЙК?
Расчетная формула находится на листе TDSheet (G:G)
Это моя ошибка, пытался упростить расчет и доупрощался - убрав столбец с начальными значениями.

Принцип описанный ниже не подойдет, на листе Брейк будут умещены порядка 50 магазинов. Общее количество строк будет варьироваться от 1000 до 2000.
если(и(Значение>=0;Значение>1);100;если....  
Изменено: Strizh - 03.10.2018 09:15:47
 
 
 
#26
03.10.2018 09:26:23
если ровно 1, 3, 5, 10, 20 и т.д. - куда относим? к большему или меньшему значению?
 
 
 
#27
03.10.2018 09:28:17
Михаил С., вес 1 = 100,  а 1,001 уже 150  
 
 
 
#28
03.10.2018 09:35:22
у меня результаты отличаются от ваших почему у вас категория СГТ3 если вес 37,1 и относится к диапазону от 30 до 45 а это КГТ1, вообщем смотрите в файле
Лень двигатель прогресса, доказано!!!
 
 
 
#29
03.10.2018 09:41:41
Сергей,  формула берет в расчет Брейк (D:D); значение до19.9999 будет равняться СГТ 2.
 
 
 
#30
03.10.2018 09:47:24
вообщем определитесь с диапазонами и все у вас заработает  
Лень двигатель прогресса, доказано!!!
 
 
 
Страницы: 1 2 След.
Наверх