Excel関数を組み合わせる方法(Excel)

概要

「Excel関数の組み合わせる(ネスト)する方法がよく分からない…大泣き」と
相談頂く事が多かった為、関数の組み合わせ方を説明致します！

例題(index×matchを組み合わせよう)

仮に、下記「給与テーブル」から

  • 北海道×Python
  • 岩手県×JavaScript
  • 福島県×ExcelVBA

の行列一致した金額を抽出したい！となったとします。

※index×match関数を組み合わせた後のindex関数

上記ケースで使用する関数は「index」「match」関数ですが、
「関数1個ずつなら書けるんだけど、どうやって組み合わせれば良いんだろう…考え中」
と悩む人が多いのだと思っております。

「index」「match」と
関数1個ずつ書ける人ならば、必ず組み合わせられますので、
是非ともご参考下さい！

オススメの関数の書き方

「関数の挿入」機能を使う

下記のように、セルにいきなり「=INDEX(…)」と書き始めていないでしょうか？
実はこの書き方、上級者向けなのです！
(Twitterで聞いたところ、「関数の挿入」を使っていない人が大多数でしたが←)

※上記のような関数エラーが表示され、
「何回書き直しても関数を保存出来ない！」とイライラした経験ないでしょうか？

オススメの書き方は、
【「数式」タブ→「関数の挿入」をクリックして「関数の挿入」画面で関数を記載する】です！

「使いたい関数を簡単に選ぶことができ、入力値をどうやって設定するかのヒントが表示されます。」

とあるので、使わない手はありません！
※下記オレンジ枠内

まずは関数を1個ずつ確認しながら書く

関数の組み合わせは、プラモデルで人型ロボットを作るようなイメージに近いです。

(よっぽど偏屈でなければ)
「頭」「胴体」「腕」「足」等のパーツ毎に作成し、
最後に各パーツを組み合わせますよね？

関数も全く同じ事で、
目的毎にパーツ(関数)を作成し、最後に各パーツを組み合わせていくのが王道なのです。

今回組み合わせる「index」「match」関数の場合は、
「index」関数が下記説明文のように、行列を指定する必要がある為、

◆INDEX関数
指定された行と列が交差する位置にある値またはセルの参照を返します

まずは「match」関数にて下記行列を取得出来る関数を作成していきましょう！

  1. A列にて、指定の都道府県が存在する行位置 (福島県の場合は「9」行目)
  2. 2行目にて、指定の言語が存在する行位置 (ExcelVBAの場合は「2」列目)

行列の位置を取得する「match」関数を作成

まずは「関数の挿入」画面を開く前に、新しい列に項目名「行」と入れておきましょう。
(※H列「給与」に書かなくて良いの？と思われるかもしれませんが、最後に組み合わせるのでご安心ください)

「関数の挿入」画面を開き、「すべて表示」を選択。
※普段使わない関数は特に、「すべて表示」にしないと見つかりません

適当な関数をクリックし、
探したい関数名の頭文字(「match」関数の場合は「ma」等)を入力し、
「match」関数を選択し、「OK」を押す。

※慣れると数秒で関数を選択できます

A列内に「北海道」がある行位置を取得出来るように設定し、「OK」を押す

「北海道」は3行目にあるから「3」でOK！と
設定した関数の結果が正しく出力されている事を確認する
※正しくならない場合は、Google検索して関数を調べましょう。

「行」関数を下まで伸ばし、下記都道府県がある行数が出力されているか確認する

  • 北海道：3行目にある
  • 岩手県：5行目にある
  • 福島県：9行目にある

これで行位置を取得する関数(パーツ)が完成いたしました！クラッカー

上記と全く同じ流れで「列」を取得する関数も作成致します。
※J列に「列」と記載し、match関数で列数を取得

行、列位置を取得するパーツを作成出来たので、INDEX関数に組み合わせていきましょう！

関数を組み合わせる

まずは、行、列の関数出力セルを参照して組み合わせる

「関数の挿入」を開き、「INDEX」関数を選択して「OK」を押す

「参照,行番号,列番号,領域番号」を選択

行番号、列番号に、
先程作成した「match」関数出力セルを参照し、「OK」を押す

関数を下まで延ばし、
下記「給与テーブル」から

  • 北海道×Python
  • 岩手県×JavaScript
  • 福島県×ExcelVBA

の行列一致した金額を抽出出来た事を確認する

給与を取得出来ましたクラッカー
…が、現状では

  • 「給与」(本体)
  • 「行」(パーツ)
  • 「列」(パーツ)

と関数が数列に分かれている為、
「給与」に、「行」「列」関数を組み合わせていきます！

本体に各パーツを組み合わせる

「給与」関数を選択し、左上の「関数の挿入」ボタンを押す

(先程と同じく)「参照,行番号,列番号,領域番号」を選択

下記gifを参考に「行」「列」記載関数を組み合わせる

  1. 「行」記載の関数を先頭「=」を除いてコピー
  2. INDEX関数の「行番号」に「行」関数を貼り付け
  3. 「列」記載の関数を先頭「=」を除いてコピー
  4. INDEX関数の「列番号」に「列」関数を貼り付け
  5. パーツを本体に取り込めたので、「行」「列」は不要となり、削除

これでindex×match関数を組み合わせる事が出来ました！クラッカー

いかがでしたか？
どんな複雑な関数の組み合わせでも、

  1. 関数を1個ずつ、パーツ毎に作成
  2. パーツ毎の出力結果に間違いがないか確認
  3. 最後に組み合わせる

と順々に考えれば、そこまで難しくはありません！

おまけ1　組み合わさった関数を解読する場合

「関数の組み合わせ方は分かったけど、関数の解読が出来ない…大泣き」という方は、
「関数の挿入」を使って、下記gifを参考に関数を1個1個解体してみましょう！

  1. 「行」記載の関数を先頭「=」を付けて、I列に関数を入れる
  2. INDEX関数の「行番号」をI列参照にする
  3. 「列」記載の関数を先頭「=」を付けて、J列に関数を入れる
  4. INDEX関数の「列番号」をJ列参照にする

複雑に組み合わさった関数は解読出来ずとも、
パーツ毎に分解すれば、必ず単純な関数となります！

※↓率直な疑問をぶつけて頂き、ありがとうございました！祈る

おまけ2　「関数の挿入」の知名度

「関数の挿入」をどのくらいの頻度で使っているか
Twitterで意見を求めましたが、「全く使わない」派が大多数で驚きましたギャー

(※沢山のご意見ありがとうございました祈る)

もし、
「本記事を参考に「関数の挿入」を使ってみたら
関数を組み合わせる事が出来るようになりました！」
という人がいらっしゃいましたら、Twitterにてコメント頂けると嬉しいですやっほー

編集履歴

2022/05/29　新規作成

PAGE TOP