☰

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

tag-icon • tag-icon microsoft-excel worksheet-function
Получить ссылки из ячеек, которые не являются пустыми

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

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

введите описание изображения здесь

решение1

Следующие формулы массива будут работать, даже если какие-либо имена идентичны. Введите их 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()функция извлекает имена, используя индексы.

решение2

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

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

введите описание изображения здесь

Эта формула массива вводится в ячейку 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)),"")

Как формула массива, она должна быть введена с CtrlShiftEnter, а не просто 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))),"")

Связанный контент