Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/ВПР на 2 условия (или аналог ВПР) - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_, DrMini  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » ВПР на 2 условия (или аналог ВПР) (Формулы/Formulas)
ВПР на 2 условия (или аналог ВПР)
Anton1013 Дата: Четверг, 18.06.2015, 11:26 | Сообщение № 1
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 203
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Форумчане, доброго дня!

Такой вопрос: есть список с 2-мя значениями, нужно подтянуть 3-е из другой таблички. Недостаток в том что должно быть полное совпадение как по 1му так и по 2му столбцу. Пример прилагаю (желтым - место куда нужно подтянуть). :'(
К сообщению приложен файл: 6485770.xlsx (9.2 Kb)

Никогда не поздно научиться!
 
Ответить
СообщениеФорумчане, доброго дня!

Такой вопрос: есть список с 2-мя значениями, нужно подтянуть 3-е из другой таблички. Недостаток в том что должно быть полное совпадение как по 1му так и по 2му столбцу. Пример прилагаю (желтым - место куда нужно подтянуть). :'(
Автор - Anton1013
Дата добавления - 18.06.2015 в 11:26
китин Дата: Четверг, 18.06.2015, 11:34 | Сообщение № 2
Группа: Модераторы
Ранг: Экселист
Сообщений: 7034
Репутация: 1079 ±
Замечаний: 0% ±

Excel 2007;2010;2016
Код
=ИНДЕКС($P$8:$P$11;ПОИСКПОЗ(D8&E8;$N$8:$N$11&$O$8:$O$11;0))
формула массива
К сообщению приложен файл: Anton1013.xlsx (8.9 Kb)

Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852

Сообщение отредактировал китин - Четверг, 18.06.2015, 11:35
 
Ответить
Сообщение
Код
=ИНДЕКС($P$8:$P$11;ПОИСКПОЗ(D8&E8;$N$8:$N$11&$O$8:$O$11;0))
формула массива
Автор - китин
Дата добавления - 18.06.2015 в 11:34
_Boroda_ Дата: Четверг, 18.06.2015, 11:37 | Сообщение № 3
Группа: Админы
Ранг: Местный житель
Сообщений: 16895
Репутация: 6613 ±
Замечаний: ±

2003; 2007; 2010; 2013 RUS
Обычная формула
Код
=ПРОСМОТР(;-1/(N$8:N$11=D8)/(O$8:O$11=E8);P$8:P$12)
К сообщению приложен файл: 6485770_1.xlsx (9.2 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеОбычная формула
Код
=ПРОСМОТР(;-1/(N$8:N$11=D8)/(O$8:O$11=E8);P$8:P$12)
Автор - _Boroda_
Дата добавления - 18.06.2015 в 11:37
Anton1013 Дата: Четверг, 18.06.2015, 11:46 | Сообщение № 4
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 203
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Оба варианта хороши, даж не знаю какой выбрать :D
Спасибо!
closed

Никогда не поздно научиться!
 
Ответить
СообщениеОба варианта хороши, даж не знаю какой выбрать :D
Спасибо!
closed
Автор - Anton1013
Дата добавления - 18.06.2015 в 11:46
DJ_Marker_MC Дата: Четверг, 18.06.2015, 12:57 | Сообщение № 5
Группа: Друзья
Ранг: Ветеран
Сообщений: 991
Репутация: 213 ±
Замечаний: 0% ±

Excel 2019
Anton1013, если не знаете какой брать один из двух - берите третий :D B)
Код
=СУММПРОИЗВ((D8=($N$8:$N$11))*(E8=$O$8:$O$11);$P$8:$P$11)
К сообщению приложен файл: 6113915.xlsx (9.3 Kb)
 
Ответить
СообщениеAnton1013, если не знаете какой брать один из двух - берите третий :D B)
Код
=СУММПРОИЗВ((D8=($N$8:$N$11))*(E8=$O$8:$O$11);$P$8:$P$11)
Автор - DJ_Marker_MC
Дата добавления - 18.06.2015 в 12:57
Che79 Дата: Четверг, 18.06.2015, 13:29 | Сообщение № 6
Группа: Друзья
Ранг: Старожил
Сообщений: 1649
Репутация: 306 ±
Замечаний: 0% ±

2013 Win, 365 Mac
Anton1013, можно еще и четвертый вариант :)
Код
=СУММЕСЛИМН($O$8:$O$11;$M$8:$M$11;D8;$N$8:$N$11;E8)

[offtop] прошу прощения у модераторов, глюк какой-то КРИВЫЕ РУКИ, формула никак не хочет оформляться тэгом...[/offtop]
[moder]Обращайте внимание на "/" в тегах - без него - открывает, с ним - закрывает. А у Вас открывающий был с /
К сообщению приложен файл: 180615_1.xlsx (8.9 Kb)

Делай нормально и будет нормально!

Сообщение отредактировал Che79 - Четверг, 18.06.2015, 14:18
 
Ответить
СообщениеAnton1013, можно еще и четвертый вариант :)
Код
=СУММЕСЛИМН($O$8:$O$11;$M$8:$M$11;D8;$N$8:$N$11;E8)

[offtop] прошу прощения у модераторов, глюк какой-то КРИВЫЕ РУКИ, формула никак не хочет оформляться тэгом...[/offtop]
[moder]Обращайте внимание на "/" в тегах - без него - открывает, с ним - закрывает. А у Вас открывающий был с /
Автор - Che79
Дата добавления - 18.06.2015 в 13:29
pabchek Дата: Четверг, 18.06.2015, 16:06 | Сообщение № 7
Группа: Проверенные
Ранг: Ветеран
Сообщений: 931
Репутация: 218 ±
Замечаний: 0% ±

Excel 2007
Озадачился я скоростью работы этих формул ))) (ибо сам работаю обычно с массивами от полумиллиона строк). На массиве из 20 тыс. строк. Скорость вычисления формул (не самая мощная машинка у меня на работе): №1 ИНДЕКС(массивный)=18 сек(но, протягивание формулы ИНДЕКС заняло 38 сек), №2 ПРОСМОТР=29сек, №3 СУММПРОИЗВ=30сек, №4 СУММЕСЛИМН=7сек. Кроме того, СУММЕСЛИМН и СУММПРОИЗВ в предложенной редакции показывают одинаковые результаты вычислений, остальные все разные.

"Учиться, учиться и еще раз учиться!"
WM: R399923528092

Сообщение отредактировал pabchek - Пятница, 19.06.2015, 10:59
 
Ответить
СообщениеОзадачился я скоростью работы этих формул ))) (ибо сам работаю обычно с массивами от полумиллиона строк). На массиве из 20 тыс. строк. Скорость вычисления формул (не самая мощная машинка у меня на работе): №1 ИНДЕКС(массивный)=18 сек(но, протягивание формулы ИНДЕКС заняло 38 сек), №2 ПРОСМОТР=29сек, №3 СУММПРОИЗВ=30сек, №4 СУММЕСЛИМН=7сек. Кроме того, СУММЕСЛИМН и СУММПРОИЗВ в предложенной редакции показывают одинаковые результаты вычислений, остальные все разные.
Автор - pabchek
Дата добавления - 18.06.2015 в 16:06
jakim Дата: Четверг, 18.06.2015, 17:24 | Сообщение № 8
Группа: Друзья
Ранг: Старожил
Сообщений: 1218
Репутация: 316 ±
Замечаний: 0% ±

Excel 2010
Ещё пятая простая формула

Код
=INDEX($P$8:$P$11;MATCH(1;INDEX(($N$8:$N$11=D8)/($O$8:$O$11=E8);0);0))

и шестая массивная и наверное самая быстрая

Код
=INDEX($P$8:$P$11;MATCH(1;($N$8:$N$11=D8)/($O$8:$O$11=E8)))
 
Ответить
Сообщение
Ещё пятая простая формула

Код
=INDEX($P$8:$P$11;MATCH(1;INDEX(($N$8:$N$11=D8)/($O$8:$O$11=E8);0);0))

и шестая массивная и наверное самая быстрая

Код
=INDEX($P$8:$P$11;MATCH(1;($N$8:$N$11=D8)/($O$8:$O$11=E8)))
Автор - jakim
Дата добавления - 18.06.2015 в 17:24
pabchek Дата: Пятница, 19.06.2015, 11:06 | Сообщение № 9
Группа: Проверенные
Ранг: Ветеран
Сообщений: 931
Репутация: 218 ±
Замечаний: 0% ±

Excel 2007
№5=29сек, №6=26сек. Кроме того, №3 и 4 более универсальны, поскольку считают правильно, даже если в массиве есть повторяющиеся строки. И №4 в любом случае в 4 раза быстрее всех остальных...
Но, для счастливых необладателей более поздних версий (с функцией СУММЕСЛИМН) и без потребности суммировать повторяющиеся строки, №6 получается в самом деле самая быстрая.

"Учиться, учиться и еще раз учиться!"
WM: R399923528092

Сообщение отредактировал pabchek - Пятница, 19.06.2015, 11:09
 
Ответить
Сообщение№5=29сек, №6=26сек. Кроме того, №3 и 4 более универсальны, поскольку считают правильно, даже если в массиве есть повторяющиеся строки. И №4 в любом случае в 4 раза быстрее всех остальных...
Но, для счастливых необладателей более поздних версий (с функцией СУММЕСЛИМН) и без потребности суммировать повторяющиеся строки, №6 получается в самом деле самая быстрая.
Автор - pabchek
Дата добавления - 19.06.2015 в 11:06
Мир MS Excel » Вопросы и решения » Вопросы по Excel » ВПР на 2 условия (или аналог ВПР) (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2025 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!