INDEX и SMALL возвращают только один результат

У меня есть приведенная ниже формула Excel, которую я написал с ошибкой. = ЕСЛИОШИБКА (ИНДЕКС ([Testbook2.xlsx] Лист1 $ A $ 2: $ B $ 6, МАЛЫЙ (ЕСЛИ ([Testbook2.xlsx] Лист1 $ B $ 2: $ B $ 6 = "А", СТРОКА ([Testbook2.xlsx] Лист1! $ A $ 2: $ A $ 6) -ROW ([Testbook2.xlsx] Sheet1! $ A $ 2) +1), ROW ([Testbook2.xlsx] Sheet1! 1: 1)), 1), «Нет активных добровольцев «)

Код должен выглядеть в другой книге (Testbook2) в столбце, где введенный текст должен быть A или I. Затем предполагается, что все имена будут заменены рядом с ними. Однако он возвращает только первое имя.

Любые идеи о том, почему это могло произойти?

Я не очень большой поклонник сделать формулы сложнее, чем они должны быть. Целью функции ROW является создание серии последовательных чисел. Он используется, чтобы отметить позицию в пределах диапазона, а не фактическую строку на листе. Неважно, где ссылается строка; он не должен ссылаться на рабочую книгу / рабочий лист, где вы собираете данные.

Эти,

ROW([Testbook2.xlsx]Sheet1!$A$2:$A$6) - ROW([Testbook2.xlsx]Sheet1!$A$2) + 1 ROW([Testbook2.xlsx]Sheet1!1:1) 

… можно сократить до,

 ROW($1:$5) ROW(1:1) 

Есть 5 ячеек в [Testbook2.xlsx] Sheet1! $ A $ 2: $ A $ 6, поэтому все, что вам нужно, это число от 1 до 5. ROW(1:1) – это 1, который станет 2, а затем 3, и т. д., когда он заполнен. Он предоставляет параметр k функции SMALL . Неважно, откуда оно взялось.

Как только я убрал все, что было на самом деле необходимо, стало очевидно, что ваши формулы работают в текущем состоянии. Однако это формула массива , а не формула SQL.

 =IFERROR(INDEX([Testbook2.xlsx]Sheet1!$A$2:$A$6, SMALL(IF([Testbook2.xlsx]Sheet1!$B$2:$B$6="A", ROW($1:$5)), ROW(1:1))), "No Active Volunteers") 

Формулы массива должны быть завершены с помощью Ctrl + Shift + Enter↵ . После правильного ввода в первую ячейку они могут быть заполнены или скопированы или правы точно так же, как и любая другая формула. Без специального завершения CSE вы получите только первое значение, а затем ошибки.

Если вы ищете стандартную формулу, которая не требует CSE, функция функций AGGREGATE¹ может обеспечивать обработку с псевдо-массивом без использования CSE.

 =IFERROR(INDEX([Testbook2.xlsx]Sheet1!$A$2:$A$6, AGGREGATE(15, 6, ROW($1:$5)/([Testbook2.xlsx]Sheet1!$B$2:$B$6="A"), ROW(1:1))),"No Active Volunteers") 

¹ Функция AGGREGATE была введена в Excel 2010. Она недоступна в более ранних версиях.

Для версий Excel Excel до 2010 года эта стандартная формула также не требует CSE.

 =IFERROR(INDEX([Testbook2.xlsx]Sheet1!$A$2:$A$6, SMALL(INDEX(ROW($1:$5)+([Testbook2.xlsx]Sheet1!$B$2:$B$6<>"A")*1E+99, , ), ROW(1:1))),"No Active Volunteers") 

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

В качестве формулы массива (со всеми диапазонами одинакового размера)

 =IFERROR(INDEX('[Volunteer Contact list July 2015.xlsm]Volunteers'!$A$2:$A$65, SMALL(IF('[Volunteer Contact list July 2015.xlsm]Volunteers'!$Q$2:$Q$65="A",ROW($1:$64)),ROW(1:1))),"No Active Volunteers") 

В качестве стандартной формулы (со всеми диапазонами одинакового размера)

 =IFERROR(INDEX('[Volunteer Contact list July 2015.xlsm]Volunteers'!$A$2:$A$65, SMALL(INDEX(ROW($1:$64)+('[Volunteer Contact list July 2015.xlsm]Volunteers'!$Q$2:$Q$65<>"A")*1E+99, , ), ROW(1:1))),"No Active Volunteers") 

Введите каждый в ячейку соответствующим образом для типа формулы. Заполните, если необходимо, для последующих записей.

  • Excel - суммировать содержимое между заголовками, если строка найдена
  • Вызов несовместимости Excel VBA kernel32
  • Selecton.Rows.AutoFit не работает - почему?
  • Как преобразовать excel-вход yyyy-mm-dd-HH.mm.ss.000000 в "YYYY-MM-DD HH: MM"
  • Как проверить дублирующиеся строки и рассчитать эти строки в файле excel?
  • счетчик инкремента с использованием таймера интервалов
  • Скопировать через лист1
  • Как я могу заменить несколько ячеек данных на одну и ту же ячейку, если у них есть эквивалентная ячейка идентификации?
  • VBA EXCEL- Сравнение элемента списка с ячейкой (строка)
  • Excel VBA: передача аргументов
  • Возвращает true для первого экземпляра числа в другом столбце
  • Давайте будем гением компьютера.