INDEX関数とMATCH関数で複数該当する条件を検索

抽出データの通し番号列の追加

まず、抽出の目印として、COUNTIFS関数を使ってデータの入っている時間帯に順番に番号を振った列を追加します。COUNTIFS関数は ( 検索条件範囲1, 検索条件1, [検索条件範囲2, 検索条件2], … ) の順で設定し、今回は条件1のみを使用しています。

1: COUNTIFS関数で条件一致セルを数える式 の設定

"統合データ"シートのJ1セルへ見出し行として "抽出番号" と入力します。そしてJ2セルに =COUNTIFS($G$2:G2,"<>0") と入力し、オートフィルで以降の列にも自動入力します。

ポイントは、検索条件範囲の開始位置を絶対位置、終了位置を相対位置にすることです。これをオートフィルで入力すると、開始位置は固定で行が変わるごとに検索範囲が１行づつ広がりますので、条件一致セルを数えられます。検索条件には0以外を意味する "<>0" を設定し、データの入ったセルを数えます。

オートフィルの入力結果を確認すると、流動結果が入っている行が現れるごとに抽出番号が増えているのが分かります。しかし、間の0が入っている行の抽出番号にも同じ数値が重複して入っています。これでは抽出に使えませんのでもうひと手間加えます。

2: IF関数を追加した式に変更し、重複を消す

J列へ入力した数式を消して、２行目のJ2セルの数式を =IF(G2=0,0,COUNTIFS($G$2:G2,"<>0")) へ変更します。この数式のIF関数の部分は、もしG2セル(チップ数)が0なら0、違う場合はCOUNTIFS関数を実行、となっています。

これで重複が取り除けますので、変更したJ2セルの数式をオートフィルで自動入力します。

うまくいきました。データの入っている時間帯のみに順番に番号を振ることが出来ました。

データ抽出の事前準備

続いて、抽出データを収める場所を用意します。

ブックに"統合データ(抽出)"シートを新しく作り、１行目は見出し行としてA1セルへ "抽出番号" と入力し、以降には"統合データ"シートと同じ見出しを入力します。そしてA列のA2セル以降には、1から300までの通し番号を"連続データの作成" で入力します。

INDEX関数とMATCH関数を組み合わせてデータ抽出

それでは、INDEX関数とMATCH関数を使ったデータ抽出の設定を進めていきます。

INDEX関数は 配列 で指定した抽出範囲内から指定された行・列のセルの値を返します。MATCH関数は範囲の上端もしくは左端セルを１として、見つけた検索値セルまでのセル数を返します。

このため、INDEX関数の行・列指定項目にMATCH関数を組み込むことで、MATCH関数で検索してINDEX関数で抽出することが出来ます。

INDEX関数は( 配列, 行番号, [列番号], [領域番号] ) の４項目があり、今回は４項目目の [領域番号] は使いませんので設定不要です。他の３項目を順に設定します。

1: 配列【抽出範囲】の設定

"統合データ(抽出)"シートのB2セルを選択し、 =INDEX() と入力します。

カーソルが =INDEX() のカッコの中にある状態で"統合データ"シートに移り、ショートカットキーを使ってA2セルからJ1465セルまでを選択して 配列【抽出範囲】を設定します。そして項目区切りの " , " を入力して確定させます。

抽出行列が配列の外だと参照エラー #REF! となります。抽出対象は全て配列内に収めましょう。

2: 行番号【抽出行の指定】の設定

続いて抽出行を指定する 行番号 の設定です。ここへMATCH関数を挿入し、検索機能を加えます。

行番号 へ MATCH() と入力し、カーソルを MATCH() のカッコの中にある状態にします。

3: MATCH関数 検索値 の設定

では、MATCH関数の検索条件設定に移ります。MATCH関数は ( 検索値, 検査範囲, [照合の種類] ) の３項目を設定します。

検索は両方のシートに共通する項目で行うので、先ほど準備した "抽出番号" を使います。検索値 へ A2 と入力し、"統合データ(抽出)"シートのA2セルを指定して、項目区切りの " , " で確定させます。

4: MATCH関数 検査範囲 と 照合の種類 の設定

検査範囲 には、"統合データ"シートの "抽出番号" 列を指定します。

"統合データ"シートへ切り替え、ショートカットキーでJ2セルからJ1465セルまでを選択し、項目区切りの " , " で確定させます。照合の種類 は、近似値を拾わない様に、完全一致の 0 を設定します。

以上でMATCH関数の設定が完了です。

5: 列番号【抽出列の指定】の設定

続いて抽出列の指定です。抽出するのは 配列 内の左から１番目の"番号"列(A列)ですから、このセルの抽出列は 1 です。しかし、オートフィルで横方向に入力する為にCOLUMN関数で設定します。

列番号 へ COLUMN()-1 と入力します。COLUMN関数は指定したセルがA列を1として何列目かを返します。()内の指定を省略すると関数を設定したセルの位置(B2セルならば 2 )で返します。

これでオートフィルで横方向に展開すると、COLUMN関数の数値が1つずつ増え並んだ列を順番に抽出できます。

6: 絶対値指定へ変更、オートフィル入力

最後に、このままオートフィルで残りのセルを自動入力すると、検索範囲がずれて値が取得できませんので、一部を絶対値指定へ変更します。

入力されている 配列 【抽出範囲】の A2 J1465 と、 検査範囲 の J2 J1465 をマウスで順にクリックし、それぞれ [F4]キー を1回ずつ押して絶対指定に変更します。次に 検索値 の A2 をクリックして [F4]キー を3回押して列方向のみ絶対指定にします。

これでオートフィルによる自動入力が可能となります。

B2セル右下のフィルボタンをJ列までドラックし、次にフィルボタンをダブルクリックします。

これでB～J列へ自動入力され、ロットが流動した時間帯のみが抽出できました。なお、画像は一部セルの書式を整えた後となります。

7: IFERROR関数の追加

ただ、スクロールすると…

最後の数行は"抽出番号"が見つからずエラーとなっています。そこで、IFERROR関数を使い、関数がエラーとなった時の代わりの値を設定します。

B2セルの数式を =IFERROR(INDEX(統合データ!$A$2:$J$1465,MATCH($A2,統合データ!$J$2:$J$1465,0),COLUMN()-1),"") へ変更し、数式を完成させます。この式はエラーとなった時は 空白 を返す設定です。先ほど同様にオートフィルで各列末尾まで自動入力します。

うまくエラーを消すことができました。

8: 結果の確認

ついに、業者から届いたデータと現場の熱処理時間記録から抽出したロット流動結果が、有効データのみのデータセットとして整いました。これでチュートリアル１：データの整え方 は完成です。

せっかくデータセットを整えたので、歩留まりを追加して量産試作の結果を確認してみます。

あー、残念ながら気温が下がると共に歩留まりも低下していますね。開発部によると今回の量産試作品は生産性向上のために熱処理時間を短縮しているそうなのですが、その悪い影響がでてしまっているのかもしれません。この案件、まだまだ先は長い様です。