Отбор из диапазона всех совпадений

Страница 1 из 1 1
Модератор форума: китин, _Boroda_, DrMini

Отбор из диапазона всех совпадений (Формулы/Formulas)

AVI

Дата: Суббота, 21.07.2018, 17:44 | Сообщение № 1

Группа: Проверенные

Ранг: Ветеран

Сообщений: 523

Репутация: 17 ±

Замечаний: 0% ±

Excel 2016

Добрый день!
В этой http://www.excelworld.ru/forum/2-38618-1 теме есть что-то похожее, но я не понял как сделать то, что мне нужно.
Мне нужно, что бы в результат попадали все, что удовлетворяет обоим условиям. Как в примере.

И еще вопрос: планируется, что данных будет все больше и больше. Если написать макрос для описанной процедуры - он будет работать быстрее, чем формула?

Исправил

К сообщению приложен файл: _Microsoft_Exce.xlsx (8.8 Kb)

Сообщение отредактировал AVI - Суббота, 21.07.2018, 18:58

Ответить

Светлый

Дата: Суббота, 21.07.2018, 18:24 | Сообщение № 2

Группа: Друзья

Ранг: Старожил

Сообщений: 1881

Репутация: 544 ±

Замечаний: 0% ±

Excel 2013, 2016

Добрый день!
Файл пустой.

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

Ответить

Светлый

Дата: Суббота, 21.07.2018, 19:25 | Сообщение № 3

Группа: Друзья

Ранг: Старожил

Сообщений: 1881

Репутация: 544 ±

Замечаний: 0% ±

Excel 2013, 2016

Формула массива:

Код

=ЕСЛИОШИБКА(ИНДЕКС(P:P;НАИМЕНЬШИЙ(ЕСЛИ(D$1&D$2=N$2:N$29&O$2:O$29;СТРОКА($2:$29));СТРОКА(C1)));"")

А если без повторений:

Код

=ЕСЛИОШИБКА(ИНДЕКС(P:P;НАИМЕНЬШИЙ(ЕСЛИ((D$1&D$2=N$2:N$29&O$2:O$29)*(ПОИСКПОЗ(N$2:N$29&O$2:O$29&P$2:P$29;N$2:N$29&O$2:O$29&P$2:P$29;)=СТРОКА($1:$28));СТРОКА($2:$29));СТРОКА(C1)));"")

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

Сообщение отредактировал Светлый - Суббота, 21.07.2018, 19:41

Ответить

jakim

Дата: Суббота, 21.07.2018, 20:11 | Сообщение № 4

Группа: Друзья

Ранг: Старожил

Сообщений: 1218

Репутация: 316 ±

Замечаний: 0% ±

Excel 2010

Формула

Код

=IFERROR(INDEX(P$2:P$2200;AGGREGATE(15;6;ROW(A$1:A$2200)/(N$2:N$2200=D$1)/(O$2:O$2200=D$2);ROWS($7:7)));"")

К сообщению приложен файл: Microsoft_Exce.xlsx (9.3 Kb)

Ответить

AVI

Дата: Воскресенье, 22.07.2018, 05:39 | Сообщение № 5

Группа: Проверенные

Ранг: Ветеран

Сообщений: 523

Репутация: 17 ±

Замечаний: 0% ±

Excel 2016

Спасибо за ответы!

Я все пытаюсь разобраться. Вроде понимаю, а вроде нет.

Вот пример такого же вопроса, только данные другие.

Одно условие и цифровое. У меня самого получается найти, но отразить полученный результат так как в столбце D никак не получается. Там какая-то магия с

Код

Наименьший()

Код

Строка()

, но никак мне не даются эти массивы...

К сообщению приложен файл: 1743508.xlsx (10.1 Kb)

Ответить

Светлый

Дата: Воскресенье, 22.07.2018, 10:30 | Сообщение № 6

Группа: Друзья

Ранг: Старожил

Сообщений: 1881

Репутация: 544 ±

Замечаний: 0% ±

Excel 2013, 2016

Формула (массивная) должна быть такая;

Код

=ЕСЛИОШИБКА(ИНДЕКС(P:P;НАИМЕНЬШИЙ(ЕСЛИ($B$1=Таблица1[А];СТРОКА(Таблица1[Б]));СТРОКА(F1)));"")

По ЕСЛИ определяем строки с 3 и задаём им значение номера строки. Остальные останутся со значением ЛОЖЬ. Функция НАИМЕНЬШИЙ определяет этот номер, соответствующий строке в результирующей таблице. А ИНДЕКС берёт само значение.
Доб.
В Функции СТРОКА используем ячейки таблицы для синхронизации формулы при изменении размеров таблицы. Можно было бы использовать СТРОКА($2;$25).

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

Сообщение отредактировал Светлый - Воскресенье, 22.07.2018, 10:37

Ответить

_Boroda_

Дата: Воскресенье, 22.07.2018, 22:27 | Сообщение № 7

Группа: Админы

Ранг: Местный житель

Сообщений: 17007

Репутация: 6667 ±

Замечаний: ±

2003; 2007; 2010; 2013 RUS

Еще вариант формулы (тоже массивная, вводится одновременным нажатием Контрл Шифт Ентер)

Код

=ИНДЕКС(P:P;НАИМЕНЬШИЙ(ЕСЛИ(B$1=Таблица1[А];СТРОКА(Таблица1[Б]);9^6);СТРОКА(I1)))&""

К сообщению приложен файл: 1743508_1.xlsx (10.8 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995

Ответить

Отбор из диапазона всех совпадений (Формулы/Formulas)

Страница 1 из 1
1