データのある行だけ抽出する計算式の解説

リンパ節の転移数と郭清数を入力すると入力した項目のある行だけを抽出することができるようにしたいので、

https://gyazo.com/7af310c34f6c33edc7abc010a364e74c

裏側ではこんな感じになってます。

https://gyazo.com/9cd77c8e80f412d456a29f0c6e548614

この抽出されたデータを並び替えて、完成式を出力しています。

データのある行だけ抽出する計算式の解説

外部リンクです　(マイクロソフト SWAY)

以下には文章のみ転記します。

次のようにA列の中から空白を削除した一覧表をD列に作成する計算式を解説します。

解説する計算式

計算式は次の通りです。

=IF(COUNTA(シートA!$B$2:$B$1000)<ROW(A1),"",INDEX(シートA!A:A,SMALL(INDEX((シートA!$B$2:$B$1000="")*10^5+ROW($A$2:$A$1000),),ROW(A1)),0))

元データのシート名は「シートA」、上記の図ではA1からA13の範囲に相当する元の一覧表範囲はシートAのB2からB1000の範囲です。

この計算式が、どこかの列の2行目から1000行目までコピーされているとします。

計算不要な部分は空白とする

計算が必要な行のみ計算し、それ以外は計算せずに空白とします。

それが計算式の次の赤文字の部分です。

=IF(COUNTA(シートA!$B$2:$B$1000)<ROW(A1),"",INDEX(シートA!A:A,SMALL(INDEX((シートA!$B$2:$B$1000="")*10^5+ROW($A$2:$A$1000),),ROW(A1)),0))

COUNTA関数は、空白セル以外のセル個数を数える関数です。

COUNTA(シートA!$B$2:$B$1000)

これでシートAのB2からB1000までの空白以外のセル個数を数えています。

B2とB1000には$がついています。Excelは、計算式を下にコピーすると、B2だったものがB3、B4とずれていきますが、$を付けることでコピーしても位置関係を動かさないことができます。これは絶対参照というテクニックです。

今回はコピーしても必ずB2からB1000までの範囲を見るということになります。

ROW関数は、対象の行番号を調べます。

ROW(A1)

これはセルA1の行番号を調べています。

セルA1の行番号は1ですね。

下にコピーすることによって、A1はA2、A3とずれていきますので、下にコピーされた式では2、3と続いていきます。

COUNTA(シートA!$B$2:$B$1000)<ROW(A1)

は、計算式を入力する行の番号がシートAのB2からB1000までの中の空白ではないセルの数より大きかったら、ということになります。

考えてみると、空白ではない行の数以上は計算しなくていいので、このような判定をしています。

IF関数は、もしもどうだったらどうする、そうではなかったらどうするという関数です。

=IF(A1<B1,1,2)

でセルA1よりもB1が小さかったら、1とする、そうではなかったら2とするという計算式になります。

IF(COUNTA(シートA!$B$2:$B$1000)<ROW(A1),"",

は、判定式の結果があっていたら、空白とする、そうではなかったら、という意味です。

""

は空白という意味です。

その後ろの

,

が、そうではなかったら、という意味で、もしも計算行の番号がシートAの空白ではないセルの数より大きくなかったらの場合を、この後に続けて入力します。

空白の判定

その行が空白かどうかを判定しているのは、次の計算式の赤文字の部分です。

=IF(COUNTA(シートA!$B$2:$B$1000)<ROW(A1),"",INDEX(シートA!A:A,SMALL(INDEX((シートA!$B$2:$B$1000="")*10^5+ROW($A$2:$A$1000),),ROW(A1)),0))

わかりやすいように、下のような表を作成しました。

セルB1に、

=$A$1:$A$10=""

という計算式を入力し、B2からB10までの範囲にコピーしました。

A列で空白のセルがTRUE、そうではない場合はFALSEとなっています。

(シートA!$B$2:$B$1000="")*10^5

はその空白かどうか調べた結果に対して、10の5乗、つまり10000をかけた値です。

TRUE、FALSEという値は、実は数字としても扱うことができて、TRUEは1です。FALSEは0です。

つまり、その行が空白ならば10000、空白でなければ0になります。

この10000という数字は、とても大きな値という意味でしかありません。それが9999でも100万でもかまいません。その理由は後程解説します。

その後に足されている計算式、

ROW($A$2:$A$1000)

これは、上記の論理から考えれば、その行の行番号が求められます。

しかし、本来のROW関数の使い方ではありません。ROW関数は、指定したセルの行番号を出すもので、指定できるのは1つのセルなのです。しかし、今回はセル範囲を指定しています。

ちなみに次の数式をセルB1に入力し、セルB10までコピーしてみると次のようになります。

=ROW($A$1:$A$10)

セルA1に$が付いているのでセルA1の行番号しか見ないのですね。また、ROW関数は範囲を指定してもその中の先頭しか見ないのです。この理由も後述します。

今回はINDEX関数を2つ使っています。

まずINDEX関数のキホンを抑えていきましょう。

INDEX関数は、範囲の中から指定した番目のデータを抜き出します。

=INDEX(範囲,縦の番目,横の番目)

上記の表で、

=INDEX(A1:C6,3,2)

は、セルB3の値の30が答えになります。

1列だけの指定なら横の番目は指定せずに縦の番目のみ指定できます。

=INDEX(B1:B6,4)

は、セルB4の値の40が答えになります。

=INDEX(B1:B6,4,0)

のように横の番目になぜか０を指定することもできます。この０は未入力にすることができます。０番目は「データ全部」という意味を持っています。横データはB列１列だけなので全部といっても１列しか指定されていないのでエラーにはなりません。でもこの０や未入力の本当の使い方は次の架空の一覧表の作成の項目で説明します。このINDEX関数ですが、単体としての動作では単純な動きをしますが、他の関数と組み合わせるととても複雑な動きをします。

架空の一覧表の作成

=IF(COUNTA(シートA!$B$2:$B$1000)<ROW(A1),"",INDEX(シートA!A:A,SMALL(INDEX((シートA!$B$2:$B$1000="")*10^5+ROW($A$2:$A$1000),),ROW(A1)),0))

この赤い部分では、そのまま計算式の結果としてシートに求めることはできないのですが、計算式の中でのみ使えるような一覧表の作成をすることができます。

今回の動作に、一番わかりやすい形の近い状態を作ってみました。

セルC1には、

=($A$1:$A$6="")*10000+ROW()

という計算式が入力され、セルC6までコピーしています。

つまり、上記の赤字の部分は、「もしもB列が空白ならばとても大きな数字、そうでなければ行番号」という一覧表を作成しています。INDEX関数は、縦の番目、横の番目を未入力や０にすることで、計算式内だけで使える架空の一覧表を作成できるのです。

実際にこのようなINDEX関数の使い方は公式には認められていないので、あまりお勧めできません。

SMALL関数は、下から指定した番目の値を求めます。

書式は次の通りです。

=SMALL(一覧表,番目)

ではわかりやすいように、先ほどの表のD列にSMALL関数を入力してみます。

セルD1に次の計算式を入力し、D6までコピーしています。

=SMALL($C$1:$C$6,ROW())

C1からC6の範囲で、行番号に対する番目のデータを出しています。

実はSMALL関数とROW関数を組み合わせると小さい順に並べ替えた一覧にすることができます。

つまり、

=IF(COUNTA(シートA!$B$2:$B$1000)<ROW(A1),"",INDEX(シートA!A:A,SMALL(INDEX((シートA!$B$2:$B$1000="")*10^5+ROW($A$2:$A$1000),),ROW(A1)),0))

この赤い部分では、「もしもB列が空白ならばとても大きな数字、そうでなければ行番号とした一覧表を小さい順に並べ替える」という動作をしています。

これで、必要なデータの行番号が求まるのと、空白となってものすごく大きくなった数字は後ろの方に追いやられるようになります。

必要なデータの抜き出し

これが、必要なセル数だけの中で動作する最終段階です。

上記までで、「必要なデータの行番号の小さい順に並んでいる一覧と、余計なデータの区別の一覧表」が得られました。

今度はその一覧表を使って、求めたいA列の中から対応する空白列を求める計算式です。

IF(COUNTA(シートA!$B$2:$B$1000)<ROW(A1),"",INDEX(シートA!A:A,SMALL(INDEX((シートA!$B$2:$B$1000="")*10^5+ROW($A$2:$A$1000),),ROW(A1)),0))

INDEX関数で、シートAのA列すべての範囲から、計算式が入力されている行番号に対する番目の、必要なデータ一覧に記載されている行番号のデータを抜き出します。

セルE1に次の計算式を入力し、セルE6までコピーしています。A列の値は数値のままだとわかりにくいのでアルファベットに書き換えました。

=INDEX($A$1:$A$6,D1)

下２つはエラーになっていますが、10000行以上の関係ない番目となっているためです。

このエラーは初めに解説した、「計算不要な部分は空白とする」で説明したように、計算式が入力されている行番号が、空白ではないセルの数を超えたら空白とする処理によって空白になります。

セルF1には、次の計算式が入力され、セルF6までコピーされています。

=IF(COUNTA($A$1:$A$6)<ROW(),"",E1)

サイトメニュー