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

shelf IT

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

Advertisements

検証例

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

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

marunomi1
こんな感じの表があるとして、
marunomi2
今回与えられたミッションは、この中で既婚者のみを別表に順に抜き出す、というものです。
marunomi3
まずはフィルタをかけてみましょう。表を全て選択し……。
marunomi4
挿入タブテーブルをクリックします。
marunomi5
表がテーブル化されたので、既婚者の▼をクリックし……、
marunomi6
「○」がついた(既婚者の)データだけを表示します。
marunomi7
既婚者だけが表示されるので、これをコピペします。一部データがコピーされてないように見えますが……。
marunomi8
フィルタを解除すると、非表示になっている行にコピーされていることが確認できました。
しかし、色がついている、元通りにするならさらに表をテーブルから戻さないといけない、頭に空白がある、といった理由で、いまいちです。

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

marunomi9
表の左端に列を作り、頭から数字を割り振っていきます。
marunomi10
右端の列をクリックして列全体を選択し、
marunomi11
ホームタブ右端の並び変えとフィルタ昇順ないしは降順を選びます。
marunomi12
選択範囲外の扱いを訊かれるので選択範囲を拡張するを選び、OKを押します。
marunomi13
並び変えが行われました。
marunomi14
「○」が付いている名前が順番になっているので、これをコピペします。
marunomi15
あとは数字を昇順で並べ替えしなおせばいいんですが、この場合部署にも数字を割り振っておかないと、一番下にきてしまいます。
marunomi16
なので、一番下の見出しを一番上に挿入し直して完成です。
これは悪くないです。ただし結構手順が多く、あとからデータを追加した時にいちいちこれをやるのか、という問題はあります(マクロ組めるのであればこれでも支障ないです)。

検証③If文で振り分ける

marunomi17
というわけで関数使ってみます。If文を使い、=IF(E2 = “○”,C2,””)のように記述します。E2が○ならC2を代入し、そうでなければ空白を返します。
marunomi18
するとこのように歯抜けになります。そして並び変えとフィルタをしても、関数を並び変えするのでぴったりと合わせることができません。
一応値のみ貼り付けし直して並び変えてもいいんですが、関数使うなら全自動でやりたいのが人情です。

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

アルゴリズム

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

marunomi19
この件を関数で達成するためにどうすればいいかというと、まず○がついてる行番号を抜き出します。
marunomi20
次に数字の大きさに関係なく、抜き出した行番号の数を数えます。
marunomi21
今回は6個数字がありますので、6番目の行番号の数字、5番目の数字、4番目の数字……と順番に並べます。
marunomi22
この並べた数字をCとくっつける(=C2とかにする)と、既婚者の名前が抜き出されるという塩梅です。

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

marunomi23
まずIf関数とRow関数を使って、○がついてるセルの行番号を抜き出します。今回は=if(E2=”○”,row(E2),””)と記述しています。
=row(セル)とすることで行番号が抽出できます。
marunomi24
数字の個数はCount(範囲)またはCountA(範囲)のように記述して数えられます。6個数字が抜き出せたことがわかりました。

数字を順に並べる

marunomi25
Large関数を使うと、範囲内の○番目のデータを抜き出すことができます。=large(範囲,順位)のように記述します。
G13は「抜き出した行番号の個数」なので、G13番目の数値(今回は6)を求めれば一番下の順位(今回は6番目)の数字を求められることになります。
よって=large(G$2:G$12,$G$13)と記述します。
marunomi26
同様に、5番目の数値はG13から1を引いた数(今回は5)となります。手打ちしてもちゃんと動きますが、
marunomi28
面倒なので行番号なり通し番号なり使います。1からひとつずつ大きくなってればなんでもいいです。
今回はA1セルの行番号(つまり1)を指定し、=large(G$2:G$12,$G$13 – row(A1))と記述しました。
marunomi27
順に下に下っていき、0番目の数字までくるとエラーになります。Large関数がそんな順位の数字はないよ、と言ってるエラーです。
marunomi29
さきほど行番号や通し番号を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)以降全てこれにしておくことで、既婚者の数に数字が調整されます。

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

marunomi30
で、最後に数字と列番号をくっつけていきます。
ここでたとえば=”C” & G14のようにすると……。
marunomi31
文字列として認識されます。
marunomi32
そこでIndirect関数を使い、文字列を参照先として認識できるようにします。ざっくり言えばIndirectは&でつないだ文字列に、参照していい旨許可を与える的な関数です。
式で言うと=indirect(“C” & G14)となります。
marunomi33
さらにこれをオートフィルするとエラるので、
marunomi34
Iferror関数を使ってエラーになった時の処理を記述します。使い方はほぼif分と同じですが、「エラーになった時」以外の条件は記述しないです。
今回は=iferror(indirect(“C” & G14),””)となります。一応これで完成。
Advertisements

式をある程度圧縮

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

marunomi35
参照している「○のついた数字の数」とは、「○の数」と同じです。なので、直接○の数をCountAで数えることもできます。
なので今までG2:G12を参照していたのを、=counta(E$2:E$12)と変更しても結果は変わりません。
式の圧縮というのは、こんな感じで中継地点を省略していく作業です。
marunomi36
さらに6番目~の数字で参照している「数字の個数」(=G13)も、
marunomi37
直接E2~E12の○の数を参照するよう書き変えてしまいます。
具体的に言うと$G$13の部分を、G13が参照しているcounta(E$2:E$12)で上書きします。中継地点を省略するイメージです。
marunomi38
同様にIf文のG13部分も……。。
marunomi39
こんな感じで上書きします。G13(数字の個数)を参照している式はこれで無くなりました。
marunomi40
なので消します。
やればやるほど式の可読性が下がっていくので、わかりづらければあえて中継地点を残すという選択肢もあります。
marunomi41
こっちで参照しているG14というのも……。
marunomi42
つまりコレのことなので……。
marunomi43
=以外をまるっとコピーして、
marunomi44
G14のあった場所に貼っ付けます。
marunomi45
これでIf文を使ってない「6番目の数字」は削除できます。
marunomi46
以下同様、5番目の数字の式もコピーして……。
marunomi47
G15を参照している式にペーストします。
marunomi48
オートフィルで下のコピペし、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)),””)),””)という式になりました。
以上です。○のついてる行番号部分は残りますが、割と感覚的に理解しやすい方法なのではないかと思います。
一応配列数式とか使うと行番号も消せますが、力尽きたので一回これでシメます。

やりたいことから方法を探すエクセルExcel操作・関数・VBA(マクロ)逆引きまとめ
逆引き(やりたいことから探す)Excel記事まとめ
関数・演算子・メソッド・プロパティ名から探すExcel/VBA(マクロ)使い方・組み合わせ方まとめ
こちらはExcelやメソッドの諸機能を、機能の名称から探せるまとめ記事です。

コメント