Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1 2 След.
RSS
ВПР по двум критериям из 2 таблиц
 
#1
17.02.2014 17:47:46
Суть такова, что нужно заполнить поле табельный номер на листе "задание" сами табельные номера хранятся на листе Табельный номер. Изменять таблицу нельзя, можно заполнять только пустые строки. Обычные функции тут не подходят, пытался впихнуть в ВПР функцию сцепить, но ничего не получилось. Прошу помочь
 
 
 
#2
17.02.2014 18:08:38
asgfl, кроме как использовать сцепить там где нельзя я могу предложить ожидать написания макроса соответствующими гуру.

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
 
 
#3
17.02.2014 18:22:12
Код
=СУММЕСЛИМН('Табельный номер'!$D$3:$D$13;'Табельный номер'!$A$3:$A$13;A5;'Табельный номер'!$B$3:$B$13;B5;'Табельный номер'!$C$3:$C$13;Задание!C5)
 
 
 
#4
17.02.2014 18:24:34
Я просто думаю, может есть какие нибудь другие функции кроме ВПР, с помощью которых можно решить данную задачу.
 
 
 
#5
17.02.2014 18:27:43
Цитата
Nic70y
Спасибо Вам :-) Всё получилось
 
 
 
#6
17.02.2014 18:42:33
Nic70y, аплодирую стоя. Я не догадался, что эту функцию можно применить к табельному номеру, который состоит из одних цифр. Но как быть, если в номере будут не только цифры?..

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
 
 
#7
17.02.2014 19:13:56
Цитата
JayBhagavan пишет:
будут не только цифры?
формула массива:
Код
 =ИНДЕКС('Табельный номер'!$D$3:$D$13;ПОИСКПОЗ(A5&B5&C5;'Табельный номер'!$A$3:$A$13&'Табельный номер'!$B$3:$B$13&'Табельный номер'!$C$3:$C$13;0))
но конечно лучше доп.столбцы.
 
 
 
#8
17.02.2014 21:38:51
=ПРОСМОТР(2;1/(МУМНОЖ(Ч(A5:C5='Табельный номер'!$A$3:$C$13);{1:1:1})=3);'Табельный номер'!$D$3:$D$13)
"..Сладку ягоду рвали вместе, горьку ягоду я одна."
 
 
 
#9
18.02.2014 18:32:49
Добрый день.
Огромная просьба помочь. Нужно подтянуть значения из одного листа в другой при помощи ВПР. Проблема в том что подтянуть нужно по двум критериям.
Во вложении файл. Весь интернет уже перерыла, несколько дней сижу...не понимаю почему не выходит!!! И индексом пробовала.....Может быть проблема в том что колонка "А" в текстовом формате? но переводя их в числовой выходят цифры с буквами. Очень прошу вас помочь((((
Изменено: Eleni - 18.02.2014 18:42:46
 
 
 
#10
18.02.2014 18:37:02
Eleni, нет вложения.

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
 
 
#11
18.02.2014 18:43:03
Исправила...прошу прощения
 
 
 
#12
18.02.2014 18:56:45
Eleni, не понятно, что нужно получить, откуда и куда нужно полученное положить?

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
 
 
#13
18.02.2014 19:01:40
Нужно из листа1 перенести единицы на лист "листинг по аптекам" согласно их IDD и коду товара. Т.е. на листе1 на пересечении кода 703398 и IDD 10027640000372757 (C5) стоит "1", необходимо чтоб в листе "листинг по аптекам" на этом же коде и IDD стояла "1", если не стоит ничего то нужно чтоб перенеслось так же....ничего. И все это нужно довести до автоматизма. Нужно чтоб все ячейки на листе "листинг по аптекам" были заполнены.
Изменено: Eleni - 18.02.2014 19:02:00
 
 
 
#14
18.02.2014 19:10:05
Вероятно у меня сходная задача, но пока решения для неё никак не могу найти.
Задача по сортировке.xlsx (10.45 КБ)
Задача во вложении. Буду благодарен, если кто-то сможет помочь.
 
 
 
#15
18.02.2014 19:32:46
Eleni, можно на суммпроизв() делать - а чтоб убрать нули, добавить ещё ЕСЛИ()
 
 
 
#16
18.02.2014 19:34:36
awarebeaver, попробуйте
=ИНДЕКС($C$5:$C$23;ПОИСКПОЗ(F5;$A$5:$A$23;0);ПОИСКПОЗ(G5;$B$5:$B$23;0))
 
 
 
#17
18.02.2014 19:36:31
Eleni, попробуйте так:
Код
=ЕСЛИОШИБКА(ИНДЕКС(Лист1!$1:$1048576;ПОИСКПОЗ($A3;Лист1!$A:$A;0);ПОИСКПОЗ(СЦЕПИТЬ(I$2);Лист1!$2:$2;0));"-")
вводите в ячейку I3.

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
 
 
#18
18.02.2014 19:37:37
Hugo, спасибо огромное. А нельзя сделать именно ВПР?!
 
 
 
#19
18.02.2014 19:45:59
JayBhagavan, ошибка в формуле вот в этой части "Лист1!$1:$1048576"
 
 
 
#20
18.02.2014 19:59:33
Eleni, замените Лист1!$1:$1048576 на диапазон всего Вашего листа. Из-за различий версий экселя могут различаться предельные диапазоны.
Можно и так:
Код
=ЕСЛИОШИБКА(ВПР($A3;Лист1!$1:$1048576;ПОИСКПОЗ(СЦЕПИТЬ(I$2);Лист1!$2:$2;0);0);"-")

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
 
 
#21
18.02.2014 20:19:48
Я приложила кусок того файла что нужно мне сделать. В этом куске все получается. А в нужном файле (большом) стоят только прочерки.  Приложить большой файл не могу, он очень много весит!
 
 
 
#22
18.02.2014 20:35:20
Все работает!!!!))) Спасибо огромное!!!!!!! Цены вам нет)))))
 
 
 
#23
18.02.2014 20:39:17
Eleni, попробуйте так:
Код
=ЕСЛИОШИБКА(ВПР($A3;Лист1!$1:$1048576;ПОИСКПОЗ(I$2;Лист1!$2:$2;0);0);ЕСЛИОШИБКА(ВПР($A3;Лист1!$1:$1048576;ПОИСКПОЗ(СЦЕПИТЬ(I$2);Лист1!$2:$2;0);0);"-"))
Если не поможет, то постарайтесь сделать копию файла-оригинала, обрезать содержимое копии до максимально допустимого размера или просто запаковать файл в архив с максимальной степенью сжатия.

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
 
 
#24
19.02.2014 10:09:42
Eleni,

К сожалению данная формула
Цитата
Eleni пишет:
=ИНДЕКС($C$5:$C$23;ПОИСКПОЗ(F5;$A$5:$A$23;0);ПОИСКПОЗ(G5;$B$5:$B$23;0))

не подходит, сам тоже уже пробовал подобную написать. ПОИСКПОЗ находит только первую позицию по искомому критерию. Таким образом, формула корректно отрабатывает только поиск первых 2 значений...
Есть ли ещё идеи как решить данную задачу?
 
 
 
#25
19.02.2014 11:19:39
awarebeaver, вижу решение такое:
Код
=СУММЕСЛИМН($C:$C;$A:$A;F$5;$B:$B;G5)
Попробуйте ещё построить сводную по массиву данных, возможно это решение Вам больше подойдёт.

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
 
 
#26
19.02.2014 11:56:55
JayBhagavan,  как раз с час назад сам до этого неожиданно простого решения додумался.

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

Только вот возник один вопрос: А какую формулу использовать, если при наличии таких одинаковых пар нужно значение из третьего столбца для каждой пары?
 
 
 
#27
19.02.2014 12:05:08
awarebeaver, приведите, пожалуйста, файл с примером что есть и что надо. (у меня туго с визуализацией, извините)

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
 
 
#28
19.02.2014 17:25:49
JayBhagavan, условия задачи даже усложнились, так что прежнее решение уже не оптимально. Файл сюда почему-то не подгружается, выдает ошибку.

Выложил сюда http://files.mail.ru/391D1D65F8094DFEA2D769872AAD38BE

Буду благодарен за помощь!
 
 
 
#29
19.02.2014 18:09:53
awarebeaver, задача мудрёно составлена. Попробую изложить, как я её понял:
Условие 2 - заполняем первым найденным значением по условиям 0 и 1;
Условие 3 - сумма по условиям 0 и 1;
Условие 4 - минимум, максимум, первое попавшееся, что именно сюда ставить по условиям 0 и 1;
Условие 5 - сумма по условиям 0 и 1.
Будьте добры, подтвердите/уточните/исправьте.
Спасибо.

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
 
 
#30
20.02.2014 10:14:26
JayBhagavan,

В целом верно, есть несколько уточнений:
Цитата
JayBhagavan пишет:
Условие 4 - минимум, максимум, первое попавшееся, что именно сюда ставить по условиям 0 и 1;
Первое попавшееся, которое будет у
Цитата
JayBhagavan пишет:
Условие 2 - заполняем первым найденным значением по условиям 0 и 1;

По
Цитата
JayBhagavan пишет:
Условие 3 - сумма по условиям 0 и 1;
Цитата
JayBhagavan пишет:
Условие 5 - сумма по условиям 0 и 1.
По сути сумма не нужна, так как нет таких значений, которые полностью были бы идентичны по Условие 0 + Условие 1 + Условие 4. Но можно искать и СУММЕСЛИМН по, так как будет лишь одно условие суммировать.

И последний пункт задачи:
После выборки уникальных пар, автоматически найти для каждого кластера "Результат" все оставшиеся уникальные пары Условие 0 + Условие 1 + Условие 4, подходящие под условия сортировки
Цитата
JayBhagavan пишет:
Условие 2 - заполняем первым найденным значением по условиям 0 и 1;
Вот такая вот задача. Сам немного офигел, когда формализовал её  :)  Уже второй третий пытаюсь найти решение.
Изменено: awarebeaver - 20.02.2014 10:25:47
 
 
 
Страницы: 1 2 След.
Читают тему
Наверх