Получить ссылки из ячеек, которые не являются пустыми

Question

Мне нужна помощь с функцией. В столбце AI есть список имен, но они разделены пустыми ячейками. Есть ли способ показать эти имена в столбце B, но без пустых ячеек?

Результат должен выглядеть так:

Answer 1

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

Формула для этого требует строки над первой строкой данных. Заголовки служат этой цели в таблице ниже.

Эта формула массива вводится в B2:

=IFERROR(INDEX($A$2:$A$10,MATCH(0,COUNTIF($B$1:B1,$A$2:$A$10&"")+IF($A$2:$A$10=",1,0),0)),"")

Как формула массива, она должна вводиться с помощью Ctrl Shift Enter, а не просто Enter. Если введено правильно, Excel окружит формулу фигурными скобками {} в строке формул.

После ввода формулы щелкните ячейку B2 и заполните формулу вниз.

В случае, если необходимо указать повторяющиеся имена, эта формула массива удалит только пробелы и не удалит дубликаты:

=IFERROR(INDEX(A:A,SMALL(INDEX(NOT(ISBLANK($A$2:$A$10))*ROW($A$2:$A$10),0),COUNTBLANK($A$2:$A$10)+ROW(C1))),"")

Answer 2

Следующие формулы массива будут работать, даже если любое из имен будет идентичным. Введите их в B2 с помощью Ctrl+Shift+Enter, затем заполните.

Этот использует фиксированный диапазон, основанный на ваших данных образца:

{=IFERROR(INDEX(A:A,SMALL(IF(A$1:A$10<>"",ROW(A$1:A$10),""),ROW()-ROW(A$1)+1)),"")}

Этот использует динамический диапазон, автоматически корректирующийся при добавлении новых имен в столбец A:A:

=IFERROR(INDEX(A:A,SMALL(IF(A$1:INDEX(A:A,MATCH("*",A:A,-1))<>"",ROW(A$1:INDEX(A:A,MATCH("*",A:A,-1))),""),ROW()-ROW(A$1)+1)),"")

Та же динамическая формула, что и выше, в развернутом виде:

=
IFERROR(
  INDEX(
    A:A
  , SMALL(
      IF(
        A$1:INDEX(A:A,MATCH("*",A:A,-1))<>""
      , ROW(A$1:INDEX(A:A,MATCH("*",A:A,-1)))
      , ""
      )
    , ROW()-ROW(A$1)+1
    )
  )
, ""
)

Как видите, эта вторая формула является просто первой, где все A$10 заменены на INDEX(A:A,MATCH("*",A:A,-1)) .

Объяснение:

Функция IF() эквивалентна:

IF(
  {"";"Camilo Georgi";"";"Carla Suarez Navarro";"";"Belinda Bencic";"";"Grace Min";"";"Johanna Larsson"}<>""
, {1;2;3;4;5;6;7;8;9;10}
, ""
)

который, поскольку Excel автоматически расширяет константы в соответствующие массивы констант длины, становится:

IF(
  {FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE}
, {1;2;3;4;5;6;7;8;9;10}
, {"";"";"";"";"";"";"";"";"";""}
)

который оценивает:

{"";2;"";4;"";6;"";8;"";10}

Функция SMALL() становится:

SMALL({"";2;"";4;"";6;"";8;"";10},ROW()-1+1)

и поскольку SMALL() игнорирует строки, это эквивалентно:

SMALL({2;4;6;8;10},ROW())

Обратите внимание, что числа являются индексами непустых имен. Для ячейки B1 SMALL() возвращает 2 , для B2 4 и т.д. Для B6 и ниже он возвращает #NUM! ошибка. (Вот почему есть IFERROR() . Он преобразует эти ошибки в пробелы.)

Наконец, функция INDEX() извлекает имена, используя индексы.