Вы в основном спрашиваете, как получить n-ое отфильтрованное значение из некоторого столбца в n-ую строку другого столбца.
Microsoft объясняет получение n-го отфильтрованного значения в Excel: Нахождение n-го значения, соответствующего условию. При этом используются "формулы массива" для выполнения расчетов над элементами в массиве (списке). Пример Microsoft может быть расширен до получения значения, соответствующего номеру строки. Если ваш массив в A2:A8
, и вы хотите показать только значения >= 0
, то в B2 можно использовать следующее B2:B8
(не проверено):
=iferror(index(A2:A8, small( if(A2:A8 >= 0, row()-1, ""), row()-1) ), "")
Будьте внимательны, чтобы ввести это как формулу массива: сначала выделите ячейки с B2
по B8
, затем вставьте вышеуказанное в строку формул и сохраните его, нажав Ctrl+Shift+Enter (или Command+Return in Office на Mac, или Command+Shift+Return в OpenOffice на Mac).
Для C2:C8
повторите с условием >= 0
замененным на < 0
.
Чтобы расшифровать этот шаг за шагом, я добавил следующие формулы в таблицу OpenOffice Calc, результаты которой приведены ниже. Обратите внимание, что в OpenOffice нужна точка с запятой для разделения параметров функции, в то время как в Excel может понадобиться запятая, поэтому точки с запятой в следующих формулах.
В D2:D8
:
=if(A2:A8 >= 0; row()-1; "")
Это показывает номер строки минус 1, если значение в той же строке в A2:A8
является положительным, или пустая ячейка в противном случае. Вычитание 1 преобразует строки 2-8 в положение от 1 до 7, чтобы игнорировать первую строку заголовка.
Итак, теперь мы знаем, что 1-е, 4-е, 5-е и 7-е значения из A2:A8
являются положительными.
Далее в E2:E8
:
=small( if(A2:A8 >= 0; row()-1; ""); row()-1 )
Он принимает более ранние результаты в качестве входных данных для small(..., k)
который принимает k-е наименьшее число, показанное ранее в D2:D8
. (Этот список сортируется от 1 до 7, но это даже не требуется для small
функции.) Здесь, положит k
текущей строке числа минус 1 игнорировать строку заголовка снова. Таким образом, для 3-го ряда мы получаем позицию 2-го положительного значения в A2:A8
. Но для E6
и вниз значение не найдено, показывая ошибку.
В F2:F8
:
=index(A2:A8; small( if(A2:A8 >= 0; row()-1; ""); row()-1 ))
Это принимает позиции 1, 4, 5 и 7 (и ошибки) из предыдущего шага в качестве входных данных для index(..., k)
, который находит k-е значение из A2:A8
, или ошибки, если k
недействительным.
Теперь F2:F8
прежнему показывает ошибку для последних строк. В Excel 2007 и более поздних версиях можно использовать iferror
чтобы ничего не показывать в случае ошибки, как в самой первой формуле выше. OpenOffice не поддерживает это, но нуждается в if(iserror(...); ""; ...)
, заменяя оба ...
формулой из F2:F8
. Не хорошо.
В качестве альтернативы используйте index(A2:A9; ...)
чтобы включить ячейку A9
при получении фактического значения из первого столбца, а затем каким-то образом заставить OpenOffice использовать small(...; 8)
если больше значений не найдено. Как в G2:G8
:
=if(A2:A8 >= 0; row()-1; 8)
Это все еще ищет положительные числа в 7 значениях из A2:A8
, но теперь возвращает 8
если не положительное.
В H2:H8
:
=small( if(A2:A8 >= 0; row()-1; 8); row()-1 )
И наконец в I2:I8
:
=index(A2:A9; small( if(A2:A8 >= 0; row()-1; 8); row()-1 ))
Здесь значение 8
отображается на значение в A9
. В примере снимка экрана A9
содержит несколько точек, которые затем отображаются в H6:H8
как больше не найдено положительных чисел.
Как пишет Microsoft: если вы действительно хотите освоить формулы в Excel, вам нужно знать, как использовать формулы массива. Наслаждаться.