この使い方はデータ抽出には必見です。こんな方法があったかと、あっと驚くでしょう！エクセルINDEX関数に、ROW関数、COLUMN関数を組み合わせて、まとまったデータをそのまま抽出する方法を紹介しています。INDEX関数の応用編です。INDEX関数では、このようなことができるという使い方を分かりやすく説明しています。ブロックごとにまとまって入力されているデータがあります。このまとまったデータを、ブロックごと抽出することができます。
目次
INDEX、ROWを組み合わせてブロックごと抽出する応用編
エクセルINDEX関数、ROW関数を組み合わせて、２行のブロックを２列に表示させてみましょう。
F2に、「愛媛の前期の売上金額」を、表示させる式を入力しましょう。
F2 =INDEX($C$1:$C$9,(ROW()-1)*2)
INDEX関数で範囲を、「$C$1:$C$9」で指定します。
ROW関数で行番号を取得します。E2に入力した「=ROW()」は、行番号「2」を返します。
「=(ROW()-1)*2」は、「(2-1)*2」で行番号「2」です。
INDEX関数の列番号は、省略したので「1」です。
E2の式は、範囲「$C$1:$C$9」で、２行目、１列目の値を返します。
「28,646,000」が返されているのを、確認しましょう。
F2の式を、「F3:F5」にコピーしましょう。
「F3:F5」に、各県の「前期の売上金額」が表示されました！
次に、G2に「愛媛の後期の売上金額」を、表示させる式を入力しましょう。
G2 =INDEX($C$1:$C$9,(ROW()-1)*2+1)
INDEX関数で範囲を、「$C$1:$C$9」で指定します。
ROW関数で行番号を取得します。F2に入力した「=ROW()」は、行番号２を返します。
「=(ROW()-1)*2+1」は、「(2-1)*2+1」で行番号「3」です。
INDEX関数の列番号は、省略したので「1」です。F2の式は、範囲「$C$1:$C$9」で、３行目、１列目の値を返します。
「21,748,000」が、返されているのを確認しましょう。
G2の式を、「G3:G5」にコピーしましょう。
「G3:G5」に、各県の後期の売上金額が表示されました！
INDEX、COLUMNを組み合わせてブロックごと抽出する応用編
エクセルINDEX関数、COLUMN関数を組み合わせて、２列のブロックを２行に表示させてみましょう。
B6に、「本社の2016年度の売上金額」を、表示させる式を入力しましょう。
B6 =INDEX($A$3:$E$3,,(COLUMN()-1)*2)
INDEX関数で範囲を、「$A$3:$E$3」で指定します。
範囲を1列で指定したので、行番号は省略できます。B5に入力した「=COLUMN()」は、列番号「2」を返します。
「=(COLUMN()-1)*2」は、「(2-1)*2」で列番号「2」です。
B5の式は、範囲「$A$3:$E$3]で、１行目、２列目の値を返します。
「21,314,000」が、返されているのを確認しましょう。
B6の式を、C6にコピーしましょう。
C6は「19,385,000」です。
これで、「北海道の2016年度の売上金額」が表示されました！
次に、「本社の2017年度の売上金額」を、B7に表示させる式を入力しましょう。
B7 =INDEX($A$3:$E$3,,(COLUMN()-1)*2+1)
INDEX関数で範囲を、「$A$3:$E$3」で指定します。
範囲を1列で指定したので、行番号は省略できます。B6に入力した「=COLUMN()」は、列番号「2」を返します。
「=(COLUMN()-1)*2+1」は、「(2-1)*2+1」で列番号「3」です。
B6の式は、範囲「$A$3:$E$3」で、1行目、3列目の値を返します。
「22,637,000」が返されているのを確認しましょう。
B7の式を、C7にコピーしましょう。
B7は「19,262,000」です。これで「北海道の2017年度の売上金額」が表示されました。
これで完了です！
INDEX、ROW、COLUMNを組み合わせてブロックごと抽出する応用編
エクセルINDEX関数、ROW関数、COLUMN関数を組み合わせて、１列に入力されているデータをブロックごと列に表示させてみましょう。
E1に、式を入力しましょう。
E1 =INDEX($B$1:$B$9,ROW(A1)+(COLUMN(A1)-1)*3)
INDEX関数で範囲を、「$B$1:$B$9」で指定します。
D1に入力した「ROW(A1)+(COLUMN(A1)-1)*3」は、「$B$1:$B$9」の範囲で「１列、1～3行」を取得する式です。列は１列なので省略できます。
なので、D1の式は、範囲「$B$1:$B$9」で、1番上から1行目を返します。
「MN320548」が返されているのを、確認しましょう。
「E2:E3」に、この式をコピーしましょう。
E2に「AP325L」、E3に「企画開発」が表示されます。
「E1:E3」の式を、「Ｆ1:F3」「G1:G3」にコピーしましょう。
「E1:E3」のセルの範囲を選択し、そのまま右に列Gまでドラッグします。
「Ｆ1:F3」「G1:G3」に式が入力されます。
「B1:B9」の値が、3行毎「E1:G3」に表示されました！
INDEX関数
セルの範囲にある値、またはその値のセル参照を返します。
INDEX関数、配列形式の書式と引数
INDEX(配列, 行番号, [列番号])
| 配列 | セルの範囲を指定します。配列が１行または１列の場合、行番号または列番号を省略できます。 |
| 行番号 | 配列内の値を返す行番号を、数値で指定します。行番号を省略した場合は、必ず列番号を指定します。範囲外のセルを指定するとエラー値 #REF! が返されます。 |
| 列番号 | 任意です。配列内の値を返す列番号を、数値で指定します。列番号を省略した場合は、必ず行番号を指定します。範囲外のセルを指定するとエラー値 #REF! が返されます。 |
INDEX関数、配列形式の使用例
G2に、商品№「A120032」の表参道店の在庫数を、抽出する式を入力しましょう。店舗に入力されているのは在庫数です。
G2 =INDEX(A2:D5,3,3)
「10」を返します。
G2の式は、範囲の上から３番目の行、左から３番目の列の値を返します。
INDEX関数、セル範囲形式の書式と引数
INDEX(参照, 行番号, [列番号], [領域番号])
| 参照 | 1つまたは複数のセルの参照を指定します。複数の範囲を指定する場合は、複数指定した範囲全体ををかっこ () で囲み、1つの範囲と範囲の間を半角のコンマ (,) で区切ります。範囲が1列のみである場合は、INDEX(範囲,行番号) と指定できます。 |
| 行番号 | 範囲の中にある、セル参照を返す行の位置を、数値で指定します。 |
| 列番号 | 範囲の中にある、セル参照を返すセルの列位置を数値で返します。 |
| 領域番号 | 任意です。行番号と列番号が交差する位置を返す参照の範囲を１つ選択します。最初の領域の番号が１、2番目の領域は２となります。省略すると１が選択されます。領域は全て同じシートにない場合は、#VALUE! エラーになります。 |
INDEX関数、セル配列形式の使用例
F2に、型番「PN203」の個数を抽出する式を入力しましょう。
F2 =INDEX((A2:C3,A5:C7),2,3,2)
「62」を返します。
F2の式は、範囲「A2:C3」と「A5:C7」で、領域番号を「2」に指定します。２番目の領域「A5:C7」が選択されるので、２番目の領域「A5:C7」の上から２番目行、左から３番目の列の値を返します。
こちらの記事もご覧ください。⇒エクセルINDEX関数とMATCH関数を使って検索、抽出する方法
こちらの記事もご覧ください。⇒エクセル関数INDEXとMATCHで複数条件で検索、抽出！
ROW関数
指定した配列の行番号を返します。
ROW関数の書式と引数
| 範囲 | 行番号を調べたいセルまたはセル範囲を指定します。省略した場合、ROWが入力されているセルの行番号が返されます。範囲には複数の範囲を指定することはできません。 |
ROW関数の使用例
B4 =ROW(A2:A5)
範囲「A2:A5」の１行目の行番号を調べましょう。１行目はA2なので、行番号は「2」です。
行番号「2」が返されます。
COLUMN関数
指定した配列の列番号を返します。
COLUMN関数の書式と引数
| 範囲 | 列番号を調べたいセルまたはセル範囲を指定します。省略した場合、COLUMN 関数が入力されているセルの列番号が返されます。 |
COLUMN関数の使用例
数式が入力されている行の、行番号を調べましょう。
B2 =COLUMN()
B2の列番号は「2」です。
列番号「2」が返されます。
B3 =COLUMN(C1)
C1の列番号は「３」です。
列番号「３」が返されます。
B4 =COLUMN(A2:C2)
範囲「A2:C2」の1列目の列番号を調べましょう。１列目はA2なので、列番号は「1」です。
列番号「1」が返されます。
ROW、COLUMNは、他の関数と組み合わせて使うと便利です
INDEX関数、ROW関数、COLUMN関数を組み合わせて使うと色々な抽出ができます。ROW関数は行番号を返す関数です。それに対しCOLUMN関数は列番号を返します。ROW関数、COLUMN関数はそれ自体あまり意味をなさない関数で、単独で使われることはあまりないですが、他の関数と組み合わせて使うと便利な関数です。
ROW関数、COLUMN関数と組み合わせて便利な関数には、VLOOKUP関数、HLOOKUP関数、INDEX関数、OFFSET関数などがあります。ROW関数、COLUMN関数と同じ検索/行列関数で、組み合わせることによって色々な検索、抽出ができます。ROW関数、COLUMN関数が重要な役割をします。ROW関数、COLUMN関数は、データの集計、加工において便利な関数です。
こちらの記事もご覧ください。⇒エクセルINDEX関数とMATCH関数を使って複数条件で値を取得する
