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

Вход

Регистрация

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

 

= Мир MS Excel/Выборка данных по двум диапазонам имён. - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Выборка данных по двум диапазонам имён. (Формулы/Formulas)
Выборка данных по двум диапазонам имён.
Shambala Дата: Вторник, 06.08.2019, 04:52 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 18
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Есть два диапазона имён $A$3:$A$12 и $C$3:$C$12 . Если имена содержащиеся в этих двух диапазонах пересекаются с именами в столбцах E и F, то нужно эти данные автоматически выбирать в столбцы J и К. Если имена в столбцах E и F совпадают лишь с одним из диапазонов по выборке, к примеру $A$3:$A$12, то такие данные выбирать не нужно.
Пример того, как по замыслу это должно работать на скрине.



Выборка между одним диапазоном и одним столбцом решается формулой

Код
{=ЕСЛИОШИБКА(ИНДЕКС(E$1:E$41;НАИМЕНЬШИЙ(ЕСЛИ(ЕЧИСЛО(ПОИСКПОЗ($E$3:$E$41;$A$3:$A$12;));СТРОКА(E$3:E$41));СТРОКА(J1)));"")}


Но способ решения выборки между двумя диапазонами и двумя столбцами для выборки для меня остаётся тайной. Хотелось бы в идеале решить задачу или хотя бы понять решаема ли оная? Или это сложно и нужно просто взять, отпустить и больше не вспоминать :D
К сообщению приложен файл: 777777777.xlsx (13.5 Kb)
 
Ответить
СообщениеЕсть два диапазона имён $A$3:$A$12 и $C$3:$C$12 . Если имена содержащиеся в этих двух диапазонах пересекаются с именами в столбцах E и F, то нужно эти данные автоматически выбирать в столбцы J и К. Если имена в столбцах E и F совпадают лишь с одним из диапазонов по выборке, к примеру $A$3:$A$12, то такие данные выбирать не нужно.
Пример того, как по замыслу это должно работать на скрине.



Выборка между одним диапазоном и одним столбцом решается формулой

Код
{=ЕСЛИОШИБКА(ИНДЕКС(E$1:E$41;НАИМЕНЬШИЙ(ЕСЛИ(ЕЧИСЛО(ПОИСКПОЗ($E$3:$E$41;$A$3:$A$12;));СТРОКА(E$3:E$41));СТРОКА(J1)));"")}


Но способ решения выборки между двумя диапазонами и двумя столбцами для выборки для меня остаётся тайной. Хотелось бы в идеале решить задачу или хотя бы понять решаема ли оная? Или это сложно и нужно просто взять, отпустить и больше не вспоминать :D
Автор - Shambala
Дата добавления - 06.08.2019 в 04:52
bmv98rus Дата: Вторник, 06.08.2019, 07:53 | Сообщение № 2
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4106
Репутация: 768 ±
Замечаний: 0% ±

Excel 2013/2016
чуть длиннее
Код
=IFERROR(INDEX(E:E;SMALL(IF((ISNUMBER(MATCH($E$3:$E$14;$A$3:$A$12;))+ISNUMBER(MATCH($E$3:$E$14;$C$3:$C$12;)))*(ISNUMBER(MATCH($F$3:$F$14;$A$3:$A$12;))+ISNUMBER(MATCH($F$3:$F$14;$C$3:$C$12;)));ROW($E$3:$E$14));ROW($A1)));"")


так лучше
Код
=IFERROR(INDEX(E:E;SMALL(IF(COUNTIF($A$3:$C$12;$E$3:$E$14)*COUNTIF($A$3:$C$12;$F$3:$F$14);ROW($E$3:$E$14));ROW($A1)));"")

Замечательный Временно просто медведь , процентов на 20.

Сообщение отредактировал bmv98rus - Вторник, 06.08.2019, 08:58
 
Ответить
Сообщениечуть длиннее
Код
=IFERROR(INDEX(E:E;SMALL(IF((ISNUMBER(MATCH($E$3:$E$14;$A$3:$A$12;))+ISNUMBER(MATCH($E$3:$E$14;$C$3:$C$12;)))*(ISNUMBER(MATCH($F$3:$F$14;$A$3:$A$12;))+ISNUMBER(MATCH($F$3:$F$14;$C$3:$C$12;)));ROW($E$3:$E$14));ROW($A1)));"")


так лучше
Код
=IFERROR(INDEX(E:E;SMALL(IF(COUNTIF($A$3:$C$12;$E$3:$E$14)*COUNTIF($A$3:$C$12;$F$3:$F$14);ROW($E$3:$E$14));ROW($A1)));"")
Автор - bmv98rus
Дата добавления - 06.08.2019 в 07:53
Светлый Дата: Вторник, 06.08.2019, 08:35 | Сообщение № 3
Группа: Друзья
Ранг: Старожил
Сообщений: 1829
Репутация: 510 ±
Замечаний: 0% ±

Excel 2013, 2016
Пока такой вариант. Формула массива (Ctrl+Shift+Enter):
Код
=ЕСЛИОШИБКА(ИНДЕКС(E:E;НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($A$3:$A$12;$E$3:$E$14)*СЧЁТЕСЛИ($C$3:$C$12;$F$3:$F$14)+СЧЁТЕСЛИ($A$3:$A$12;$F$3:$F$14)*СЧЁТЕСЛИ($C$3:$C$12;$E$3:$E$14);СТРОКА($3:$14));СТРОКА(A1)));"")
*Или чуть короче:
Код
=ЕСЛИОШИБКА(ИНДЕКС(E:E;НАИМЕНЬШИЙ(ЕСЛИ(МУМНОЖ(СЧЁТЕСЛИ($A$3:$A$12;$E$3:$F$14)*2+СЧЁТЕСЛИ($C$3:$C$12;$E$3:$F$14);{1:1})=3;СТРОКА($3:$14));СТРОКА(A1)));"")

Программировать проще, чем писать стихи.

Сообщение отредактировал Светлый - Вторник, 06.08.2019, 08:50
 
Ответить
СообщениеПока такой вариант. Формула массива (Ctrl+Shift+Enter):
Код
=ЕСЛИОШИБКА(ИНДЕКС(E:E;НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($A$3:$A$12;$E$3:$E$14)*СЧЁТЕСЛИ($C$3:$C$12;$F$3:$F$14)+СЧЁТЕСЛИ($A$3:$A$12;$F$3:$F$14)*СЧЁТЕСЛИ($C$3:$C$12;$E$3:$E$14);СТРОКА($3:$14));СТРОКА(A1)));"")
*Или чуть короче:
Код
=ЕСЛИОШИБКА(ИНДЕКС(E:E;НАИМЕНЬШИЙ(ЕСЛИ(МУМНОЖ(СЧЁТЕСЛИ($A$3:$A$12;$E$3:$F$14)*2+СЧЁТЕСЛИ($C$3:$C$12;$E$3:$F$14);{1:1})=3;СТРОКА($3:$14));СТРОКА(A1)));"")
Автор - Светлый
Дата добавления - 06.08.2019 в 08:35
Nic70y Дата: Вторник, 06.08.2019, 08:53 | Сообщение № 4
Группа: Друзья
Ранг: Экселист
Сообщений: 8759
Репутация: 2272 ±
Замечаний: 0% ±

Excel 2010
вдруг правильно
Код
=ЕСЛИОШИБКА(ИНДЕКС(E:E;НАИМЕНЬШИЙ(ЕСЛИ(ЕЧИСЛО(ПОИСКПОЗ("*"&$E$3:$E$14&$F$3:$F$14&"*";$A$3:$A$12&$C$3:$C$12&$A$3:$A$12;));СТРОКА($E$3:$E$14));СТРОКА(A1)));"")
К сообщению приложен файл: 7293335.xlsx (14.0 Kb)

ЮMoney 41001841029809
 
Ответить
Сообщениевдруг правильно
Код
=ЕСЛИОШИБКА(ИНДЕКС(E:E;НАИМЕНЬШИЙ(ЕСЛИ(ЕЧИСЛО(ПОИСКПОЗ("*"&$E$3:$E$14&$F$3:$F$14&"*";$A$3:$A$12&$C$3:$C$12&$A$3:$A$12;));СТРОКА($E$3:$E$14));СТРОКА(A1)));"")
Автор - Nic70y
Дата добавления - 06.08.2019 в 08:53
bmv98rus Дата: Вторник, 06.08.2019, 09:19 | Сообщение № 5
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4106
Репутация: 768 ±
Замечаний: 0% ±

Excel 2013/2016
Светлый, вроде ж без умножения
Код
=IFERROR(INDEX(E:E;SMALL(IF(MMULT(COUNTIF($A$3:$A$12;$E$3:$F$14)+COUNTIF($C$3:$C$12;$E$3:$F$14);{1;1})>1;ROW($E$3:$E$14));ROW($A1)));"")

[p.s.]не люблю ради краткости всю строку в ROW загонять.

Замечательный Временно просто медведь , процентов на 20.
 
Ответить
СообщениеСветлый, вроде ж без умножения
Код
=IFERROR(INDEX(E:E;SMALL(IF(MMULT(COUNTIF($A$3:$A$12;$E$3:$F$14)+COUNTIF($C$3:$C$12;$E$3:$F$14);{1;1})>1;ROW($E$3:$E$14));ROW($A1)));"")

[p.s.]не люблю ради краткости всю строку в ROW загонять.
Автор - bmv98rus
Дата добавления - 06.08.2019 в 09:19
Nic70y Дата: Вторник, 06.08.2019, 09:33 | Сообщение № 6
Группа: Друзья
Ранг: Экселист
Сообщений: 8759
Репутация: 2272 ±
Замечаний: 0% ±

Excel 2010
Цитата bmv98rus, 06.08.2019 в 09:19, в сообщении № 5 ()
не люблю ради
не люблю массивные
доп. D
Код
=ЕСЛИ(СЧЁТЕСЛИМН(A$3:A$12;E3;C$3:C$12;F3)+СЧЁТЕСЛИМН(A$3:A$12;F3;C$3:C$12;E3);D2+1;D2)
и соот.
Код
=ЕСЛИОШИБКА(ВПР(СТРОКА(A1);$D:E;СТОЛБЕЦ(B1););"")
К сообщению приложен файл: 9852472.xlsx (14.1 Kb)

ЮMoney 41001841029809
 
Ответить
Сообщение
Цитата bmv98rus, 06.08.2019 в 09:19, в сообщении № 5 ()
не люблю ради
не люблю массивные
доп. D
Код
=ЕСЛИ(СЧЁТЕСЛИМН(A$3:A$12;E3;C$3:C$12;F3)+СЧЁТЕСЛИМН(A$3:A$12;F3;C$3:C$12;E3);D2+1;D2)
и соот.
Код
=ЕСЛИОШИБКА(ВПР(СТРОКА(A1);$D:E;СТОЛБЕЦ(B1););"")
Автор - Nic70y
Дата добавления - 06.08.2019 в 09:33
Светлый Дата: Вторник, 06.08.2019, 10:10 | Сообщение № 7
Группа: Друзья
Ранг: Старожил
Сообщений: 1829
Репутация: 510 ±
Замечаний: 0% ±

Excel 2013, 2016
Цитата bmv98rus, 06.08.2019 в 09:19, в сообщении № 5 ()
вроде ж без умножения
Лишние строки получаются Огонь-Вода и Земля-Воздух

Программировать проще, чем писать стихи.
 
Ответить
Сообщение
Цитата bmv98rus, 06.08.2019 в 09:19, в сообщении № 5 ()
вроде ж без умножения
Лишние строки получаются Огонь-Вода и Земля-Воздух
Автор - Светлый
Дата добавления - 06.08.2019 в 10:10
bmv98rus Дата: Вторник, 06.08.2019, 11:15 | Сообщение № 8
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4106
Репутация: 768 ±
Замечаний: 0% ±

Excel 2013/2016
Цитата Светлый, 06.08.2019 в 10:10, в сообщении № 7 ()
Лишние строки получаются

Если читать между строк , то да, я понял задание иначе. :-) В описании про то что пара должна состоять из значений разных диапазонов нечего не сказано :-)

Замечательный Временно просто медведь , процентов на 20.
 
Ответить
Сообщение
Цитата Светлый, 06.08.2019 в 10:10, в сообщении № 7 ()
Лишние строки получаются

Если читать между строк , то да, я понял задание иначе. :-) В описании про то что пара должна состоять из значений разных диапазонов нечего не сказано :-)
Автор - bmv98rus
Дата добавления - 06.08.2019 в 11:15
Светлый Дата: Вторник, 06.08.2019, 11:28 | Сообщение № 9
Группа: Друзья
Ранг: Старожил
Сообщений: 1829
Репутация: 510 ±
Замечаний: 0% ±

Excel 2013, 2016
Цитата bmv98rus, 06.08.2019 в 09:19, в сообщении № 5 ()
не люблю ради краткости всю строку в ROW загонять
Согласен, но если не мешает... Вообще причесал:
Код
=ЕСЛИОШИБКА(ИНДЕКС(E:E;НАИМЕНЬШИЙ(ЕСЛИ(МУМНОЖ(СЧЁТЕСЛИ($A:$A;$E$3:$F20)*2+СЧЁТЕСЛИ($C:$C;$E$3:$F20);{1:1})=3;СТРОКА($3:20));СТРОКА(A1)));"")
Для МУМНОЖ {1:1} надо.
Цитата bmv98rus, 06.08.2019 в 11:15, в сообщении № 8 ()
пара должна состоять из значений разных диапазонов нечего не сказано
Я только по картинке из первого поста ориентировался.

Программировать проще, чем писать стихи.

Сообщение отредактировал Светлый - Вторник, 06.08.2019, 11:32
 
Ответить
Сообщение
Цитата bmv98rus, 06.08.2019 в 09:19, в сообщении № 5 ()
не люблю ради краткости всю строку в ROW загонять
Согласен, но если не мешает... Вообще причесал:
Код
=ЕСЛИОШИБКА(ИНДЕКС(E:E;НАИМЕНЬШИЙ(ЕСЛИ(МУМНОЖ(СЧЁТЕСЛИ($A:$A;$E$3:$F20)*2+СЧЁТЕСЛИ($C:$C;$E$3:$F20);{1:1})=3;СТРОКА($3:20));СТРОКА(A1)));"")
Для МУМНОЖ {1:1} надо.
Цитата bmv98rus, 06.08.2019 в 11:15, в сообщении № 8 ()
пара должна состоять из значений разных диапазонов нечего не сказано
Я только по картинке из первого поста ориентировался.
Автор - Светлый
Дата добавления - 06.08.2019 в 11:28
Shambala Дата: Среда, 07.08.2019, 20:21 | Сообщение № 10
Группа: Пользователи
Ранг: Новичок
Сообщений: 18
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Спасибо всем! Только с дороги сразу же на форум, пробую последнюю формулу и всё работает идеально. Чудо!

Светлый, Мир вашему доброму и мудрому сердцу! first
 
Ответить
СообщениеСпасибо всем! Только с дороги сразу же на форум, пробую последнюю формулу и всё работает идеально. Чудо!

Светлый, Мир вашему доброму и мудрому сердцу! first
Автор - Shambala
Дата добавления - 07.08.2019 в 20:21
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Выборка данных по двум диапазонам имён. (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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