「か～～～め～～～は～～～め～～～は～～～～！！！」
小学生の頃、ドラゴンボールのキャラの悟空になりきっていました（笑）
この記事ではドラゴンボールを題材に、
・OFFSET関数でドラゴンボールを見つける
・INDEX関数とMATCH関数で、ベジータとブルマの子どもで「迷子になったトランクス」を見つける
・関数のネストに自然に慣れる
についての概要を掴めます。
ご存知かと思いますが、「ドラゴンボール」について簡単に書きますと、ボールを7個集めると願いが叶うという格闘少年漫画です。
オレンジ色のボールに、★が1つから7つまで入ったボールがあり、それらをコンプリートすると、願いが叶います。
それではまず、OFFSET関数から見て行きます。
【 OFFSET関数 】
下の様な簡単なシートを用意します。
表の作り方の基本的なことですが、A列縦の1〜2まで入力して、そこを選択して下にフィルをすると1ずつの連番が振れます。
また、横の数字はA列縦の1〜4まで選択してコピーをして、貼り付け先のセルB1を選択して、[Ctrl]+[Alt]+[V]を押して、下部の「行/列の入れ替え」にチェックを入れてOKを押します。
表が整いましたら、まず基準は、表の左上隅にある、赤い色で塗りつぶしたセルA1の「おうち」に致します。
その「おうち」を基準にして、黄色いセルG1の所に、オレンジのセルE4の「ドラゴンボール★」を呼び出します。
では早速、セルG1にOFFSET関数を入力します。
最初に数式の意味をお伝えしますと、
=OFFSET(おうち , 3行下 , 4列右)
という意味の式を入力します。
おうちのあるセルA1を基準にして、下へ3歩、右へ4歩移動しました。
では、実際に式を入れていきます。
（引数の指定は、下に下がる方を先に書きます。）
=OFFSET(A1,3,4)
すると、セルG1にドラゴンボール★が呼び出されました。
次に、同じセルG1に「ドラゴンボール★★★」を呼び出しますので、式を書き換えます。[F2]を押します。（お使いのノートPCによっては、[Fn]+[F2]キー）
今度は、「おうち」から10歩下に行って1歩右に行きますので、
=OFFSET(A1,10,1)
と指定します。
OFFSET関数はここまでです。
基準のセルA1のお家は「0（ゼロ）」としています。
次に掲載するINDEX関数と混同しない為に、「OFFSET」の「O（オー）」は「0（ゼロっぽい）」と抑えると分かりやすくなると思います。
【 INDEX関数 】
では次に、INDEX関数の基本的な使い方を見てみます。
この様な表を用意します。
先ほどのシートをコピーしてA列と1行目を挿入します。
B列のセルの1〜7までを選択してコピーし、セルA2の所で、[Ctrl]+[Alt]+[V]→[V]で書式なしの番号のみが貼り付けられます。
同様にして、1行目も番号を振ります。
A列の1〜5までをコピーして、セルB1の所で[Ctrl]+[Alt]+[V]→ 「（下の方にある）行列の入れ替えのチェックボックスを入れてOK」で番号が貼り付けられました。
内容を下記の様にキャラクターを入れて書き換えます。
ベジータとブルマの間には、トランクスという子どもがいます。
迷子になったトランクスを探します。
上の表から、「ベジータがいる行」と、「ブルマがいる列」を目視すると、「ベジータは5行目」、「ブルマは4列目」になり、そこで迷子のトランクスが泣いています。
また、INDEX関数や、この後にお伝えするMATCH関数を理解する時にイメージとして
「縦の糸はあなた」、「横の糸は私」
・・・みたいな関数だと解釈すると親しみやすいと思いました（笑）
フィーリングはどんな勉強においても結構大事かもしれないです。
ここでオレンジのセルB17にINDEX関数を使って、ベジータとブルマの子どもで、迷子になってしまった「トランクス」を見つけます。
セルB17に
=INDEX(「おうち」を含めた表の範囲,
ベジータの行の5, ブルマの列の4)
である、
=INDEX(B2:F8,5,4)
と入力すると、無事に迷子になったトランクスが無事に見つかりました！！
めでたし、めでたし！！
さて、上記の方法でも迷子のトランクスを見つけ出せましたが、実務では表の増減などの影響もあり、ベジータの行を5、ブルマの列を4といったベタ打ちは殆ど使わないのです。
GPSを使わないでヤマカンで見つけた様なものですから。
そこでGPS的な役割として、MATCH関数を用いて、それぞれベジータの行とブルマの列を求めます。
【 MATCH関数の使い方 】
最初にベジータの行を求めます。「ベジータ」と書かれているすぐ下の黄色いセルB11に、
=MATCH
(ベジータ,「おうち」を含めた「行」のキャラ名の範囲,完全一致)
という意味の、
=MATCH(B10,B2:B8,0)
と入力します。
この式の意味は、B10の「ベジータ」を検索値として、
「おうち」を含めた行の部分の、B2からB8のキャラクター名の範囲を調べ、
5番目であることの結果が返って来ました。
最後の0は、ピッタリ一致する、完全一致という意味です。
この式の最後の「0」の他に、近似一致と言ってこの部分を「1」にすると最も近い値を求めることも出来ますが、今回は「ベジータ」がいる行を求めたいので、ピッタリ一致する「0」を指定しました。
次にブルマの列を調べます。
ブルマの下のセルB14に式を入れます。
=MATCH
(ブルマ,「おうち」を含めた「列」のキャラ名の範囲,完全一致)
という意味である、
=MATCH(B13,B2:F2,0)
と式を入れました。
【 INDEX関数とMATCH関数の合わせ技 】
この表から一旦セルB17に入った式を削除して、現在下の様にして、セルB17に迷子になったトランクスを探します。
オレンジのセルに、
=INDEX
( おうちを含む表の全て , ベジータ行 , ブルマ列 )
を意味する、
=INDEX(B2:F8,B11,B14)
を入れると、おうちから数えて5行目下、4列目右に行った所のセルの「トランクス」が求まります。
また、ベジータとブルマのセルを参照しない方法で、関数のネストを使って、INDEX関数の中にMATCH関数を用いることでも同様に求まります。
一度、オレンジのセルのB17を消して、
=INDEX(おうちを含む表の全て,
MATCH(ベジータ,行範囲,0),
MATCH(ブルマ,列範囲,0)
)
と、関数のネストを使用した数式に入れると、おうちを含む表の全ての所に、B2〜F8が入り、
「ベジータ行」の所にMATCH関数で求めた5行目、
「ブルマ列」の所にMATCH関数で求めた4列目が入りました。
=INDEX(B2:F8,MATCH(B10,B2:B8,0),MATCH(B13,B2:F2,0))
トランクスも見つかって、一安心。
この記事で紹介した、OFFSET関数、INDEX関数とMATCH関数の合わせ技を使いこなせる様になると、VLOOKUP関数の限界を突破して、Excelでの表現力が増しますね(=^x^=)！
お疲れ様でした。ブレイクタイムPhotoは、
スカイツリーと河津桜です。
河津桜はソメイヨシノよりも少々早咲きです。
ご精読誠にありがとうございました。
■ Excelの学習にオススメ本の紹介 ■
「できるYouTuber式 Excel現場の教科書（長内孝平氏）」
単行本（ソフトカバー）
Kindle版
「なんの才能もない発達障害者の声: 〜 届かない声を届けたい 〜 (障害者雇用向け)」
私、猫★シエスタの著作です。殆どが「発達障害者として働くことや障害とSNSについて」ですが、障害者雇用の事務作業で特に使用頻度の高いキーに絞ったExcelなどのショートカットキーの紹介と、演習問題も掲載しております。
Kindle版
■ 関連記事 ■