[Excel] 空白セルや特定文字列のセルを上に詰める関数式

概要

Excelのセル参照にて、空白セルや特定文字列のセルをスキップして取得したい場合の関数式の書き方についてまとめました。

上記画像のA列B列のようなデータがあり、D列E列に空白セルを除いたデータだけを抽出したい場合、D1セルの関数式は以下のようになる。

=IFERROR(INDEX($A$2:$A$13, SMALL(IF(($A$2:$A$13<>""), ROW($A$2:$A$13)-MIN(ROW($A$2:$A$13))+1, ""), ROW(A1))), "")

上記の式はROW(A1)の部分だけが相対参照で他は絶対参照になっているので、D2・D3・D4セルにはD1のセルをコピーして貼り付けるだけで良い。

例えば、D4セルは以下の関数式となる。

=IFERROR(INDEX($A$2:$A$13, SMALL(IF(($A$2:$A$13<>""), ROW($A$2:$A$13)-MIN(ROW($A$2:$A$13))+1, ""), ROW(A4))), "")

E列は参照先がB列になるので、E1列は以下の関数式となる。

=IFERROR(INDEX($B$2:$B$13, SMALL(IF(($B$2:$B$13<>""), ROW($B$2:$B$13)-MIN(ROW($B$2:$B$13))+1, ""), ROW(B1))), "")

E2・E3・E4セルにはE1にセルをコピーして貼り付けるだけで良い。

例えば、E4セルは以下の関数式となる。

=IFERROR(INDEX($B$2:$B$13, SMALL(IF(($B$2:$B$13<>""), ROW($B$2:$B$13)-MIN(ROW($B$2:$B$13))+1, ""), ROW(B4))), "")

上記画像のA列B列のようなデータがあり、D列E列に「東京」を除いたデータだけを抽出したい場合、D1セルの関数式は以下のようになる。

=IFERROR(INDEX($A$2:$A$10, SMALL(IF(($A$2:$A$10<>"東京"), ROW($A$2:$A$10)-MIN(ROW($A$2:$A$10))+1, ""), ROW(A1))), "")

上記の式はROW(A1)の部分だけが相対参照で他は絶対参照になっているので、D2〜D7セルにはD1のセルをコピーして貼り付けるだけで良い。

例えば、D7セルは以下の関数式となる。

=IFERROR(INDEX($A$2:$A$10, SMALL(IF(($A$2:$A$10<>"東京"), ROW($A$2:$A$10)-MIN(ROW($A$2:$A$10))+1, ""), ROW(A7))), "")

E列は参照先がB列になるが、「東京」かどうかはA列を参照する必要があるため、E1列は以下の関数式となる。

=IFERROR(INDEX($B$2:$B$10, SMALL(IF(($A$2:$A$10<>"東京"), ROW($B$2:$B$10)-MIN(ROW($B$2:$B$10))+1, ""), ROW(B1))), "")

E2〜E7セルにはE1にセルをコピーして貼り付けるだけで良い。

例えば、E7セルは以下の関数式となる。

=IFERROR(INDEX($B$2:$B$10, SMALL(IF(($A$2:$A$10<>"東京"), ROW($B$2:$B$10)-MIN(ROW($B$2:$B$10))+1, ""), ROW(B7))), "")

上記画像のA列B列のようなデータがあり、D列E列に空白と「東京」の両方を除いたデータだけを抽出したい場合、D1セルの関数式は以下のようになる。

=IFERROR(INDEX($A$2:$A$14, SMALL(IF(($A$2:$A$14<>"")*($A$2:$A$14<>"東京"), ROW($A$2:$A$14)-MIN(ROW($A$2:$A$14))+1, ""), ROW(A1))), "")

※ ($A$2:$A$14<>"")*($A$2:$A$14<>"東京")の部分で空白と「東京」を除外している

上記の式はROW(A1)の部分だけが相対参照で他は絶対参照になっているので、D2〜D7セルにはD1のセルをコピーして貼り付けるだけで良い。

例えば、D7セルは以下の関数式となる。

=IFERROR(INDEX($A$2:$A$14, SMALL(IF(($A$2:$A$14<>"")*($A$2:$A$14<>"東京"), ROW($A$2:$A$14)-MIN(ROW($A$2:$A$14))+1, ""), ROW(A7))), "")

E列は参照先がB列になるが、空白または「東京」かどうかはA列を参照する必要があるため、E1列は以下の関数式となる。

=IFERROR(INDEX($B$2:$B$14, SMALL(IF(($A$2:$A$14<>"東京")*($A$2:$A$14<>""), ROW($B$2:$B$14)-MIN(ROW($B$2:$B$14))+1, ""), ROW(B1))), "")

E2〜E7セルにはE1にセルをコピーして貼り付けるだけで良い。

例えば、E7セルは以下の関数式となる。

=IFERROR(INDEX($B$2:$B$14, SMALL(IF(($A$2:$A$14<>"東京")*($A$2:$A$14<>""), ROW($B$2:$B$14)-MIN(ROW($B$2:$B$14))+1, ""), ROW(B7))), "")