Я хочу создать формулу Excel, которая проходит через массив и возвращает N результатов сопоставления. То есть я бы сначала разместил эту формулу в ячейке A1 (например), и она бы возвращала первый результат сопоставления. Когда я перетаскиваю ее вниз в ячейки A2, A3 и т. д., она возвращает 2-й и 3-й результат сопоставления соответственно. В настоящее время у меня есть формула, которая делает следующее:
INDEX(Sheet2!$B$2:$B$10081,AGGREGATE(15,3,(Sheet2!$AA$2:$AA$10081="TRUE")/(Sheet2!$AA$2:$AA$10081="TRUE")*(ROW(Sheet2!$AA$2:$AA$10081)-ROW(Sheet2!$AA$1)),ROWS($R$73:R73))))
Однако я пытаюсь использовать функцию ИЛИ, поэтому вместо того, чтобы просто проверять, является ли диапазон ячеек ИСТИНА или ЛОЖЬ (в настоящее время это делается с помощью вспомогательных ячеек/столбцов), я хочу просто иметь возможность проверить диапазон на соответствие любому из следующих условий:
- Ячейки, значение которых превышает 999999
- Ячейки, значение которых равно 0
- Ячейки, значение которых отрицательное (<0)
Моя попытка представлена ниже:
INDEX(Sheet2!$A$2:$A$10081,AGGREGATE(15,3,(OR(Sheet2!$B$2:$B$10081>999999,Sheet2!$B$2:$B$10081=0,Sheet2!$B$2:$B$10081<0))/(OR(Sheet2!$B$2:$B$10081>999999,Sheet2!$B$2:$B$10081=0,Sheet2!$B$2:$B$10081<0))*(ROW(Sheet2!$B$2:$B$10081)-ROW(Sheet2!$B$1)),ROWS($P$73:P73))))
Однако это не работает, поскольку 3-й аргумент агрегата() должен быть массивом, а я использовал функцию OR, которая вычисляется как логическое значение. Учитывая это, я не уверен, как правильно интегрировать функции OR для проверки 3 вышеуказанных условий.
решение1
Дополнение сделаетИЛИфункция эквивалентна каждой записи. Итак:
(myRng>999999)+(myRng<=0)
вернет массив в {1..0}зависимости от того, выполняются ли ваши критерии.
Чтобы создать массив для AGGREGATEфункции, сделайте что-то вроде:
1/((myRng>999999)+(myRng<=0))*myRng
решение2
Если вы используете Excel 365 и у вас есть доступ к функции FILTER, вы можете использовать FILTER для возврата массива. Каждое условие OR может быть заключено в скобки и разделено +, как описано здесь:
Функция ФИЛЬТР - Поддержка Office
Конкретно:
...мы используем функцию ФИЛЬТР с оператором сложения (+), чтобы вернуть все значения в нашем диапазоне массива (A5:D20), которые содержат яблоки ИЛИ находятся в восточном регионе...
Даже не зная слишком много о ваших данных, что-то подобное этому должно вернуть правильный массив (который на самом деле не нужно агрегировать с помощью small, если вы хотите вернуть все соответствующие строки).
=FILTER(Sheet2!$A$2:$A$10081,(Sheet2!$B$2:$B$10081>999999)+(Sheet2!$B$2:$B$10081=0)+(Sheet2!$B$2:$B$10081<0))
ОДНАКО... Я только что увидел ваш комментарий, в котором говорилось, что это будет использоваться в Excel 2016, поэтому FILTER не будет работать (я оставлю это в ответе, возможно, это поможет другим).
В Excel 2016 (и в Excel 365) вы можете использовать Power Query, поскольку, судя по всему, вы на самом деле просто пытаетесь найти строки, соответствующие определенным критериям.
Я создал несколько примеров данных:
Теперь просто выберите любую ячейку в данных и используйте Данные>Получить и преобразовать данные>Из таблицы/диапазона, чтобы открыть редактор Power Query с запросом к вашим данным.
В редакторе Power Query отфильтруйте значение по критериям, которые вы хотите использовать:
Теперь используйте команду «Главная»>«Закрыть и загрузить», чтобы вернуть данные в книгу:
Последний шаг — убедиться, что запрос обновляется с соответствующей периодичностью.
Установите курсор на результаты запроса, перейдите в Запрос>Свойства и настройте свойства обновления по своему усмотрению:
