Вот формула решения. Я использовал 20 строк и извлек строки, которые содержат 5 верхних строк для каждого столбца - вы можете расширить до необходимого количества строк.
С данными в A1: B20 используйте эту формулу в D1, подтвержденную с помощью CTRL + SHIFT + ENTER и скопируйте в E1 и вниз по обоим столбцам:
=IFERROR(INDEX(A$1:A$20,SMALL(IF(($A$1:$A$20>=LARGE($A$1:$A$20,5))+($B$1:$B$20>=LARGE($B$1:$B$20,5)),ROW(A$1:A$20)-ROW(A$1)+1),ROWS(D$1:D1))),"")
Примечание: извлечено только восемь строк, потому что некоторые из них содержат значения из первых 5 для обоих столбцов. Я добавил выделение в столбцы A и B, чтобы более четко проиллюстрировать
см. снимок экрана ниже
![введите описание изображения здесь](https://i.stack.imgur.com/tPgg2.jpg)
Изменить:
Из комментариев ниже кажется, что вам нужна комбинация строк, которые содержат наибольшее значение для этого столбца .... и строк, содержащих наибольшее общее для обоих столбцов.
В исходной формуле есть два условия, соединенные знаком «+», т. Е.
($A$1:$A$20>=LARGE($A$1:$A$20,5))+($B$1:$B$20>=LARGE($B$1:$B$20,5)
Знак "+" дает вам функциональность типа "OR"
, например в этом случае строки включаются, если отдельные значения находятся в первых 5 в этом конкретном столбце. Вы можете добавить другие условия, поэтому, если вы хотите также добавить любые строки, которые находятся в первой пятерке с учетом суммы обоих столбцов, вы можете добавить еще одно «предложение», т. Е.
($ A $ 1: $ A $ 20> = БОЛЬШОЙ ($ A $ 1: $ A $ 20,5)) + ($ B $ 1: $ B $ 20> = БОЛЬШОЙ ($ B $ 1: $ B $ 20,5) + ($ A $ 1: $ A $ 20 + $ B $ 1: $ B $ 20> = БОЛЬШОЙ ($ A $ 1: $ A $ 20 + $ B $ 1: $ B $ 20,5))
.... и включив это в полную формулу, вы получите эту версию:
=IFERROR(INDEX(A$1:A$20,SMALL(IF(($A$1:$A$20>=LARGE($A$1:$A$20,5))+($B$1:$B$20>=LARGE($B$1:$B$20,5))+($A$1:$A$20+$B$1:$B$20>=LARGE($A$1:$A$20+$B$1:$B$20,5)),ROW(A$1:A$20)-ROW(A$1)+1),ROWS(D$1:D1))),"")
Вы можете дополнительно уточнить это, используя комбинации +
и *
(для AND
), например для нового условия вы можете захотеть включить только строки с итогом в первых 5, если одно из отдельных значений находится в первых 10 для этого столбца ...
Пояснение:
Вышеупомянутая часть показывает, как вы можете использовать +
для условий OR
. В формуле, если эти условия равны TRUE
, тогда функция IF
возвращает «относительный номер строки» диапазона (с использованием ROW(A$1:A$20)-ROW(A$1)+1
).
Затем функция SMALL извлекает k-е наименьшее значение, k определяется ROWS (D $ 1: D1), которое начинается с 1 в D1 (или E1) и увеличивается на 1 в каждой строке.
INDEX
затем берет фактическое значение из этой строки.
Когда у вас закончатся подходящие строки SMALL
функция вернет #NUM!
ошибку, которая IFERROR
здесь преобразуется в пустую
person
barry houdini
schedule
22.12.2014