☰

空白でないセルから参照を取得します

tag-icon • tag-icon microsoft-excel worksheet-function
空白でないセルから参照を取得します

関数について助けが必要です。列 A には名前のリストがありますが、それらは空白のセルで区切られています。これらの名前を空白のセルなしで列 B に表示する方法はありますか?

結果は次のようになります。

ここに画像の説明を入力してください

答え1

次の配列数式は、名前のいずれかが同一であっても機能します。+ +B2を使用して入力し、下方向に入力します。CtrlShiftEnter

これはサンプル データに基づいて固定範囲を使用します。

{=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
    )
  )
, ""
)

ご覧のとおり、この 2 番目の数式は、最初の数式ですべての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、 の場合はB24 を返します。B6以下では#NUM!エラーを返します。(これが関数が存在する理由ですIFERROR()。この関数はこれらのエラーを空白に変換します。)

最後に、INDEX()関数はインデックスを使用して名前を取得します。

答え2

空白セルを除外して値をリストする方法の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)),"")

配列数式であるため、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))),"")

関連情報