Excel（エクセル）で条件に合うデータを別表に順番に抽出する方法/If、Large、Row、CountA関数等の組み合わせ方

「条件に合う」といっても色々あるんですが、ここでいうのは「A1に○がついてたら、B1のデータを別のセルに移し、その下に順に同様のデータをくっつけていく」というような内容です。この「順に」というのが結構厄介なので、まずは検証例をいくつか見てみたいと思います。

検証例
関数で順番にデータを抽出する
式をある程度圧縮

検証例

一応全て目的は達成していますので、別にこれがダメというわけではないです。

検証①手動でフィルタかけてみる

こんな感じの表があるとして、

今回与えられたミッションは、この中で既婚者のみを別表に順に抜き出す、というものです。

まずはフィルタをかけてみましょう。表を全て選択し……。

挿入タブのテーブルをクリックします。

表がテーブル化されたので、既婚者の▼をクリックし……、

「○」がついた（既婚者の）データだけを表示します。

既婚者だけが表示されるので、これをコピペします。一部データがコピーされてないように見えますが……。

フィルタを解除すると、非表示になっている行にコピーされていることが確認できました。
しかし、色がついている、元通りにするならさらに表をテーブルから戻さないといけない、頭に空白がある、といった理由で、いまいちです。

検証②手動で並び変えしてみる

表の左端に列を作り、頭から数字を割り振っていきます。

右端の列をクリックして列全体を選択し、

ホームタブ右端の並び変えとフィルタ→昇順ないしは降順を選びます。

選択範囲外の扱いを訊かれるので選択範囲を拡張するを選び、OKを押します。

並び変えが行われました。

「○」が付いている名前が順番になっているので、これをコピペします。

あとは数字を昇順で並べ替えしなおせばいいんですが、この場合部署にも数字を割り振っておかないと、一番下にきてしまいます。

なので、一番下の見出しを一番上に挿入し直して完成です。
これは悪くないです。ただし結構手順が多く、あとからデータを追加した時にいちいちこれをやるのか、という問題はあります（マクロ組めるのであればこれでも支障ないです）。

検証③If文で振り分ける

というわけで関数使ってみます。If文を使い、=IF(E2 = “○”,C2,””)のように記述します。E2が○ならC2を代入し、そうでなければ空白を返します。

するとこのように歯抜けになります。そして並び変えとフィルタをしても、関数を並び変えするのでぴったりと合わせることができません。
一応値のみ貼り付けし直して並び変えてもいいんですが、関数使うなら全自動でやりたいのが人情です。

関数で順番にデータを抽出する

アルゴリズム

前置きが長くなりましたが本題です。

この件を関数で達成するためにどうすればいいかというと、まず○がついてる行番号を抜き出します。

次に数字の大きさに関係なく、抜き出した行番号の数を数えます。

今回は6個数字がありますので、6番目の行番号の数字、5番目の数字、4番目の数字……と順番に並べます。

この並べた数字をCとくっつける（=C2とかにする）と、既婚者の名前が抜き出されるという塩梅です。

行番号を抜き出し、その個数を数える（Row関数、Count関数）

まずIf関数とRow関数を使って、○がついてるセルの行番号を抜き出します。今回は=if(E2=”○”,row(E2),””)と記述しています。
=row(セル)とすることで行番号が抽出できます。

数字の個数はCount（範囲)またはCountA（範囲)のように記述して数えられます。6個数字が抜き出せたことがわかりました。

数字を順に並べる

Large関数を使うと、範囲内の○番目のデータを抜き出すことができます。=large(範囲,順位)のように記述します。
G13は「抜き出した行番号の個数」なので、G13番目の数値（今回は6）を求めれば一番下の順位（今回は6番目）の数字を求められることになります。
よって=large(G$2:G$12,$G$13)と記述します。

同様に、5番目の数値はG13から1を引いた数（今回は5）となります。手打ちしてもちゃんと動きますが、

面倒なので行番号なり通し番号なり使います。1からひとつずつ大きくなってればなんでもいいです。
今回はA1セルの行番号（つまり1）を指定し、=large(G$2:G$12,$G$13 – row(A1))と記述しました。

順に下に下っていき、0番目の数字までくるとエラーになります。Large関数がそんな順位の数字はないよ、と言ってるエラーです。

さきほど行番号や通し番号を1、2、3、4……とマイナスするのに使ってきましたが、ここでも同様に1から順に大きくなるなんらかの数字を使います。
例えば、数字の個数（この場合6）がこの数字と同じ、またはこの数字より小さい場合は空白を返したいので、=if(G$13>row(A6)（A6の行番号より抜き出した数字の個数が大きい場合のみ～）を付ければ実装できます。
もしくは=if(G$13>count(G$14:G19)のようにして、ここまで使った数字の数と数字の個数を比較してもよいです。通し番号使ってもいいです。
式としては=IF(G$13>ROW(A6),LARGE(G$2:G$12,$G$13-ROW(A6)),””)のようになります。2番目に小さい数字（今回は5）以降全てこれにしておくことで、既婚者の数に数字が調整されます。

順番になった数と列番号をくっつける

で、最後に数字と列番号をくっつけていきます。
ここでたとえば=”C” & G14のようにすると……。

文字列として認識されます。

そこでIndirect関数を使い、文字列を参照先として認識できるようにします。ざっくり言えばIndirectは&でつないだ文字列に、参照していい旨許可を与える的な関数です。
式で言うと=indirect(“C” & G14)となります。

さらにこれをオートフィルするとエラるので、

Iferror関数を使ってエラーになった時の処理を記述します。使い方はほぼif分と同じですが、「エラーになった時」以外の条件は記述しないです。
今回は=iferror(indirect(“C” & G14),””)となります。一応これで完成。

式をある程度圧縮

ここまでは説明のために色んなデータを個別に置いていましたが、実用性を考えるともうちょっと圧縮したいところです。
まずは数字の個数ですが、

参照している「○のついた数字の数」とは、「○の数」と同じです。なので、直接○の数をCountAで数えることもできます。
なので今までG2:G12を参照していたのを、=counta(E$2:E$12)と変更しても結果は変わりません。
式の圧縮というのは、こんな感じで中継地点を省略していく作業です。

さらに6番目～の数字で参照している「数字の個数」（=G13）も、

直接E2～E12の○の数を参照するよう書き変えてしまいます。
具体的に言うと$G$13の部分を、G13が参照しているcounta(E$2:E$12)で上書きします。中継地点を省略するイメージです。

同様にIf文のG13部分も……。。

こんな感じで上書きします。G13（数字の個数）を参照している式はこれで無くなりました。

なので消します。
やればやるほど式の可読性が下がっていくので、わかりづらければあえて中継地点を残すという選択肢もあります。

こっちで参照しているG14というのも……。

つまりコレのことなので……。

=以外をまるっとコピーして、

G14のあった場所に貼っ付けます。

これでIf文を使ってない「6番目の数字」は削除できます。

以下同様、5番目の数字の式もコピーして……。

G15を参照している式にペーストします。

オートフィルで下のコピペし、G15以下は削除します。だいぶすっきりした表になりました。

結果として一行目が=IFERROR(INDIRECT(“C” & LARGE(G2:G12,COUNTA(E$2:E$12))),””)、
二行目以降が=IFERROR(INDIRECT(“C” & IF(COUNTA(E$2:E$12)>ROW(A1),LARGE(G$2:G$12,COUNTA(E$2:E$12)-ROW(A1)),””)),””)という式になりました。
以上です。○のついてる行番号部分は残りますが、割と感覚的に理解しやすい方法なのではないかと思います。
一応配列数式とか使うと行番号も消せますが、力尽きたので一回これでシメます。