Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel (архив)
Страницы: 1
RSS
ПОИСКПОЗ, ВПР, ИНДЕКС и т.п. Как подтянуть разные данные к повторяющимся значениям?
 
#1
01.10.2009 15:52:04
Добрый день!  
 
Как подтянуть к повторяющимся данным разные данные? Функции ПОИСКПОЗ, ВПР и т.п. ищут только первое совпадение.  
 
На Вашем Форуме много сообщений на эту тему, но они касаются поиска дубликатов. А как подтянуть нужные данные с помощью формулы....  
Ищем решение уже давно, пока не получается :-(
 
 
 
#2
01.10.2009 16:11:36
Может здесь?  
http://www.planetaexcel.ru/tip.php?aid=62
 
 
 
#3
01.10.2009 16:18:32
Была такая тема недавно пишу формулу по аналогии =ИНДЕКС($A:$A;НАИМЕНЬШИЙ(ЕСЛИ(($B$3:$B$8)=F4;СТРОКА($A$3:$A$8);СТРОКА($A$10));СТРОКА()-СТРОКА($A$2))) но торможу с этими строками опять    
Виктор , Саша посмотрите  что не так?
 
 
 
#4
01.10.2009 16:20:16
{quote}{login=}{date=01.10.2009 04:11}{thema=}{post}Может здесь?  
http://www.planetaexcel.ru/tip.php?aid=62{/post}{/quote}  
Сделал по аналогии
 
 
 
#5
01.10.2009 16:39:06
{quote}{login=}{date=01.10.2009 04:20}{thema=Re: }{post}{quote}{login=}{date=01.10.2009 04:11}{thema=}{post}Может здесь?  
http://www.planetaexcel.ru/tip.php?aid=62{/post}{/quote}  
Сделал по аналогии{/post}{/quote}  
А если 3 или 4 одинаковых значения будут? Если наворотим штук 5 ?
 
 
 
#6
01.10.2009 16:42:05
{quote}{login=Микки}{date=01.10.2009 04:18}{thema=}{post}А если 3 или 4 одинаковых значения будут? Если наворотим штук 5 ?{/post}{/quote}  
Было бы желание, а побороть и это можно. Не все же за кого-то решать?!
 
 
 
#7
01.10.2009 18:10:09
Спасибо огромное! Просто неоценимая помощь!  
Всё работает!  
Действительно, мы и сами сможем решить задачку с повторяющимися значениями 2,3 и более раз, главное - направление на пусть истинный! :)
 
 
 
#8
01.10.2009 18:51:01
В порядке бреда:  
=ИНДЕКС($A$3:$A$8;ПОИСКПОЗ(НАИБОЛЬШИЙ($B$3:$B$8-СТРОКА($B$3:$B$8)/987,654;E3);$B$3:$B$8-СТРОКА($B$3:$B$8)/987,654;0))  
"времени нет, написал первое, что пришло в голову"
 
 
 
#9
02.10.2009 00:37:19
Так вот...  
Микки, в целом попытка правильная, но там вообще не нужно указывать значение лжи, т к значений одинаковое количество. Если формулы заносятся в эм...таблицу сортировки "с запасом", то можно, конечно. Ошибка у тебя с агрументом k функции наименьший. Т к k должно быть отличным от единицы только в случае повтора значения текущей строки. Использовать можно, к примеру, счётесли.  
А вообще, советую смотреть этапы вычисления, что является незаменимой вещью в случая самообразования.    
Т е должно выглядеть, если хочется решать через наименьший, примерно так:  
I3=ИНДЕКС(A:A;НАИМЕНЬШИЙ(ЕСЛИ($B$3:$B$8=F3;СТРОКА($B$3:$B$8));СЧЁТЕСЛИ($F$3:F3;F3)))  
Обрати внимание, что у счётесли закреплёно только начало диапазона, т е он увеличивается при растягивании.  
Тут можно или наибольший или наименьший использовать, в зависимости от того, в каком порядке хотелось бы выводить повторяющееся значения. Если "с запасом", то в случае использования функции наибольший() ссылаться нужно на "левую ячейку" выше таблицы и наоборот.
 
 
 
#10
02.10.2009 00:38:20
Забыл зайти.
 
 
 
#11
02.10.2009 01:03:43
Для Микки. Дополню предыдущего оратора :)  
ЕСЛИ(($B$3:$B$8)=F3;СТРОКА($A$3:$A$8);СТРОКА($A$10)) - масив из десяток  (СТРОКА($A$10) и номеров строк, где встречается значение F3.  
НАИМЕНЬШИЙ(ЕСЛИ(...);СТРОКА()-СТРОКА($A$2)) - значение предыдущего массива, зависящее от № строки с формулой, № первой встретившейся строки с нужным значением.  
ИНДЕКС($A:$A;номер_найденной_строки)  
Для первой строки как будто работает. Во второй строке результат - ноль. Почему?  
ЕСЛИ дает массив {10;10;10;6;10;10}  
НАИМЕНЬШИЙ ищет ВТОРОЕ (строка-то вторая) меньшее значение массива - а это 10.  
ИНДЕКС, естественно, показывает значение из А10.  
Вывод: ошибка в коэффициенте функции ИНДЕКС, он должен быть неизменным - единица.  
Как определять повторные, Саша рассказал.
 
 
 
#12
02.10.2009 01:19:24
Упрощенное решение.  
Доп. столбец:  
=СЧЁТЕСЛИ($C$3:C3;C3)&"_"&C3  
Определяем имя:  
=ВПР(СЧЁТЕСЛИ($G$3:G3;G3)&"_"&G3;$A$3:$B$8;2;0)
 
 
 
#13
02.10.2009 01:39:55
vikttur, блин, надо бы время "выделить" чтобы себе мозг вынести недавно упомянутыми макро функциями excel 4 и kl с zvi попросить мастер класс провести по трёхмерным массивам, а так же расспросить про хитрости при использовании трансп(). Может поднимем тему? А то уже давненько ничего "принципиально" нового не узнавал :(  
Пытался тут вчера бессонной ночью решить задачу с первой страницы форума, связанную с поиском повторяющихся значений в другом файле, через вышеупомянутый массив чтобы не было нужно открывать все файлы для пересчёта. Приемлемого результата так и не добился :(
 
 
 
#14
02.10.2009 01:59:39
Мозг не трогать! Еще пригодится :)  
А новое проскакивает. Недавно KL показал применение СУММ(СЧЕТЕСЛИ_с_массивом). Не знал. Запрет добавления строк с помощью формул массива (@Nik)- нестандартно.  
Раньше Raptor заходил. Жаль, давно не появлялся, красивые решения у него получались. С ТРАНСП() тоже были примеры.  
А тему можно поднимать, модераторы не накажут :)
 
 
 
#15
02.10.2009 09:54:36
Виктор Саша спасибо огромное , я в Вас верил, что есть более элегантный способ без доп стольца.  
P/S Саша зря Вы считаете что я не смотрю Вычисление формулы, любимое занятие, но не все понимаю.
 
 
 
#16
02.10.2009 11:39:34
{quote}{login=Саша}{date=02.10.2009 01:39}{thema=}{post}vikttur Пытался тут вчера бессонной ночью решить задачу с первой страницы форума, связанную с поиском повторяющихся значений в другом файле, через вышеупомянутый массив чтобы не было нужно открывать все файлы для пересчёта.{/post}{/quote}  
Ну попробуй сооруди небольшой файл-пример.... подумаем тут все вместе ;-)
 
 
 
#17
02.10.2009 14:35:57
Да не я пытался, а Саша :)
 
 
 
#18
02.10.2009 14:50:41
@Nik, у меня знаний по эм.."обработке массивов" мало, вопросов куча. И терминология хромает.    
К примеру, есть ли возможность сократить массив, оставив только определённые значения?  
Т е есть массив {ложь;1;"вася";2;истина}  
Есть ли возможность из этого массива создать массив {1;"вася";2} или {1;2}?  
 
Или, скажем, есть исходные данные:  
x  
1  
2  
3  
y  
4  
5  
6  
z  
7  
8  
9  
 
Как получить массивы следующего вида?  
x  
1x  
2x  
3x  
y  
4y  
5y  
6y  
z  
7z  
8z  
9z  
---  
x y z  
1 4 7  
2 5 8  
3 6 9  
---  
x 1 2 3  
y 4 5 6  
z 7 8 9  
---  
1 4 7  
2 5 8  
3 6 9  
 
Это только примеры вопросов, их на самом деле гора...    
Последний вопрос дублирую в прикреплённом файле.
 
 
 
#19
02.10.2009 17:32:43
Если вы имели в виду сделать всё это сугубо формулами, то будет как-то так (см. влож.)  
Напомню, что для того, чтобы не копировать форматы ячеек при распространении, достаточно выделить область, на которую треб. распространить формулу и нажать Ctrl+Enter  
Но я так и не увидел примера с поиском дубликатов в другом файле, который вызвал у вас сложности...
 
 
 
#20
02.10.2009 17:54:26
Результат должен быть в виде массива, с которым потом уже можно будет работать, т е возвращаться данные массивы(одно- или двумерные) должны одной формулой.  
Собственно, на этом этапе у меня и возникли проблемы при решении той задачи.  
 
Если формула возвращает несколько значений, эксель, грубо говоря, возвращает первое значение массива. Если же вводить данную формулу в область, как формулу массива, то он возвращает значения в виде массива по порядку(см. приложение). Таблички я сделал "вручную" просто для наглядности по аналогии с введением формулы, как формулы массива. Вручную вводил, т к не знаю, как ввести это формулой и, собственно, в этом и заключается вопрос.
 
 
 
#21
02.10.2009 18:12:15
Такие варианты. Формулы медленные, т.ч. написаны исключительно из спортивного интереса :-)
KL
 
 
 
#22
02.10.2009 18:20:10
Пока писал, вижу постановка задачи изменилась :-)  
 
На всякий случай поясняю, почему мои формулы сложнее, чем предложенные @Nik - я исходил из того, что столбцы/строки могут быть неодинаковой длинны.
KL
 
 
 
#23
02.10.2009 18:23:19
Кстати, если совместимость с нерусской локализацией важна, то для поиска последней текстовой строки в массиве рекомендую использовать:  
 
=ПОИСКПОЗ("*",A1:A100,-1)  
 
а не  
 
=ПОИСКПОЗ("яяяяя",A1:A100)
KL
 
 
 
#24
02.10.2009 18:25:37
Я видимо в первичной постановке вопросов неправильно объяснил :(
 
 
 
#25
03.10.2009 02:27:36
Саша, а ты пробовал вводить мои формулы как массив для всего ранга ? :-) Из 4х вариантов решения только первый не адаптирован для такого ввода, остальные дают как раз то, что ты хочешь :-) Адаптировать первый вариант под твоё условие можно заменой всех вхождений ссылки на ячейку "А5" на такой формат ссылки: ИНДЕКС($A$5:$A$16;СТРОКА()-СТРОКА($A$4))  
Окончательная формула в яч. [E5] будет такой:
=ЕСЛИ(ЕЧИСЛО(ИНДЕКС($A$5:$A$16;СТРОКА()-СТРОКА($A$4)));ИНДЕКС($A$5:$A$16;СТРОКА()-СТРОКА($A$4));"")&ИНДЕКС($A$5:$A$16;ПОИСКПОЗ("яяяя";$A$5:ИНДЕКС($A$5:$A$16;СТРОКА()-СТРОКА($A$4))))  
 
2 KL:  
Ты, как всегда, умеешь приятно удивить :-) Хоть твой вариант и гораздо сложнее, он всё равно вызывает улыбку и положительные эмоции, поскольку ты сумел замутить такое хитроумное решение :-)  
 
{quote}{login=KL}{date=02.10.2009 06:20}{thema=}{post}я исходил из того, что столбцы/строки могут быть неодинаковой длинны.{/post}{/quote}  
Что-то подобное попытался сделать и я (см. вложение)
 
 
 
#26
03.10.2009 03:56:59
@Nik,  
 
Спасибо на добром слове. Вообще-то я имел в виду ситуацию, например, с такими исходными данными:  
 
x  
1  
2  
3  
y  
4  
5  
6  
7  
z  
8  
9
KL
 
 
 
#27
03.10.2009 10:32:46
Ну тогда согласен :-))
 
 
 
#28
05.10.2009 15:10:44
{quote}{login=Саша}{date=02.10.2009 01:39}{thema=}{post}Пытался ... решить задачу с первой страницы форума, связанную с поиском повторяющихся значений в другом файле, через вышеупомянутый массив чтобы не было нужно открывать все файлы для пересчёта. Приемлемого результата так и не добился :({/post}{/quote}Ну, то мы файл-пример увидим? ;-)
 
 
 
#29
05.10.2009 22:05:46
@Nik, бегло просмотрел последние несколько страниц-не нашёл. На днях обязательно найду и выложу, просто в небольшой запарке сейчас. Ну и, как выразился KL, решение той конкретной задачи этим эм...методом в любом случае имеет смысл делать только из спортивного интереса.  
 
Я бегло просмотрел приведённые вами формулы, вроде бы это не то, отчасти в связи с тем, что я неправильно объяснил задачу. Не отвечал, т к подробно не рассмотрел ещё, обязательно подниму эту тему в ближайшем будущем, сформулировав проблему нормально.  
Я не неблагодарная скотинка, честно, просто, опять же, не успел ещё просмотреть полностью... Большое спасибо и тебе и KL за обучение меня неразумного. :)
 
 
 
Страницы: 1
Читают тему
Наверх