Перетаскивание формулы индекса вниз при использовании массивов

Question

На этой старой странице Дэн О'Дей предлагает отличную формулу Excel, которая помогает искать и возвращать несколько ссылочных значений для данного ключа в отдельных столбцах. Формула:

=INDEX($C$2:$C$8, SMALL(IF($B$12=$B$2:$B$8, ROW($B$2:$B$8)-MIN(ROW($B$2:$B$8))+1, ""), COLUMN(A1)))

Тем не менее, я нахожу , что я не могу перетащить формулу вниз к другим столбцам , так как массивы предотвратить формулу признать новую ячейку, в частности , после того, как

SMALL(IF($B$12
         ^^^^^

Я могу перетаскивать столбцы, но не вертикально. Кроме перехода в каждую новую вертикальную ячейку и корректировки номера ячейки в формуле, как я могу перетащить формулу вниз, чтобы она автоматически обновлялась с новой информацией о ячейке?

Answer 1

Это функция COLUMN в конце, которую нужно изменить, если вы хотите скопировать формулу вниз, а не поперек - вы можете использовать ROW следующим образом

= ИНДЕКС ($ C $ 2:$ C $ 8, МАЛЫЙ (ЕСЛИ ($ B $ 12 = $ B $ 2:$ B $ 8, СТРОКА ($ B $ 2:$ B $ 8)-MIN (СТРОКА ($ B $ 2:$ B $ 8))+1), ROW (A1)))

или на самом деле более " надежно " использовать функцию ROWS поэтому, если первая формула в D3, используйте эту версию, скопированную

= ИНДЕКС ($ C $ 2:$ C $ 8, МАЛЫЙ (ЕСЛИ ($ B $ 12 = $ B $ 2:$ B $ 8, СТРОКА ($ B $ 2:$ B $ 8)-MIN (СТРОКА ($ B $ 2:$ B $ 8))+1), ROWS (D $ 3:D3)))