「Excel関数の中に関数を入れる」の勉強法

2023.02.12

Excel関数を単体で紹介する情報は多くありますが、複数を組み合わせて使うという情報はあまりないような気がします。実際に現場で関数を使うという場合、関数の中に関数を入れるという使い方が多いし、それなりに理にかなっているので、できるようになっていても損はありません。

しかし、Excelを組み合わせるというのはいくつかのハードルがあるようにも思います。

  • 組わせること自体の発想ができない
  • どう組み合わせていいかわからない
  • 組み合わせて入力する方法がわからない

もしこのようなことで関数を組み合わせることに悩んでいたら、一回この記事を読んでみてください。

主目的を考える

一番大事なことは何か考えます。例えばよくあるのは「VLOOKUP関数で検索値が空白だったら空白を出す」ということをしますが。その中身には「VLOOKUP関数で探す」「空白だったら空白にする」の2つをいっぺんに考えています。

このように2つあった場合、そのセルには優先して何を求めたいのかを考えます。この場合は基本的に「VLOOKUP関数で探した値」を出したくて、例外的に「空白だったら空白」にするのですから、「VLOOKUP関数で探した値」を出します。それ以外の目的はあとで考えます。

上司から「E列は小計で、単価×数量が10000円超えたら1割引きで小数点以下切り捨て」と言われた場合はどうなるでしょうか。この場合、E列に求める優先は「単価×数量」です。この指示は主語が欠落している感じがします。日本語の難しいところで、実際の現場での指示はテキストに書いてあるような言葉のように親切ではありません。

もう一つ、「日付の列が月末だったらその月の合計を計算して」ではどうでしょうか。この場合は「その月の合計を求める」というのが優先になります。

例外を省いて何を求めるのかを取り出していくという作業が必要です。たとえ例外の方が数が多くても、例外は例外で考えます。

内側から考える

主目的を見つけたら、まずはその主目的を叶える関数などの計算式を作成します。VLOOKUP関数なら、1つのVLOOKUP関数、掛け算の計算式ならその計算式のみです。それ以外はまずは作りません。

それが成功したら、はじめてそれに付属する機能を、さっき作った計算式の外側に作っていきます。

「VLOOKUP関数で検索値が空白だったら空白を出す」の例

検索値はセルA2、検索範囲は絶対参照でセルE2からF5、対象列は2列目、完全一致の例です。

  1. まず主目的のVLOOKUP関数を作ります。
    =VLOOKUP(A2,$E$2:$F$5,2,FALSE)
  2. 計算式がうまくいったことを確認したら、検索値が空白だったらということを考えていきます。
    「検索値が空白だったら」は「もしも、検索値が空白だったら」と読み替えれば「IF関数」であることがわかります。このように特に「もしも」の場合は文章の中にそのキーワードとなる言葉が欠落している場合もあるので、自分で補完する必要があります。
    =IF(A2="","",
  3. IF関数をはじめに入力したVLOOKUP関数の前に入力します。
    そうすると後ろの「)」が一つ不足するので入力します。
    =IF(A2="","", VLOOKUP(A2,$E$2:$F$5,2,FALSE))

「E列は小計で、単価×数量が10000円超えたら1割引きで小数点以下切り捨て」の例

この文章の場合、私ならひとつ情報が不足しているので、これを指示した上司に確認します。あまりその上司も気にしていないのかもしれませんが。「小数点以下切り捨て」は1割引きをした時の数値にのみ適用するのか、そうではない場合でも適用するのかどうかがかわかりません。

常識的に考えれば金額なので小数点以下はどんな場合であれ無視するように思えるかもしれません。ただ、複数の販売したものの合計を出す場合、小数点以下を一販売ごとに設定した合計値を出すのか、そうではなく小数点以下を含んだ合計値を出すかどうかによって数円の差が生じます。そこをどのように考えるのかの意識は統一しなければなりません。決まってなければ決めてもらう必要があります。その時に前記の理由があると理解してもらうのに少し労力が必要です。

その2つの場合での計算式の違いについても解説します。

セルA1からテーブルがあって、そこに単価と数量がある場合の計算式で同じテーブルの中に結果を求める例です。

まずは、「小数点以下切り捨てが1割引きだけの適用」の場合です。

  1. 主目的である、単価×数量を求めます。
    =[単価]*[数量]
  2. 超えていたら1割引きになるので、1割引きの計算式を作成します。
    IF関数から作りたくなるのですが、内側から作っていきたいので、そう考えると1割引きを計算する方が先になります。そうすると、主目的の計算式が壊れてしまうので、テーブルの新規列に当たらに計算した方がいいかもしれません。
    =[単価]*[数量]*0.9
  3. 1割引きした計算式を小数点以下切り捨てするためのINT関数を使います。
    =INT([単価]*[数量]*0.9)
  4. 材料が揃ったのでIF関数を作ります。
    =IF([単価]*[数量]>10000,
  5. 全部を合体します。
    =IF([単価]*[数量]>10000,INT([単価]*[数量]*0.9),[単価]*[数量])

今度は、「小数点以下切り捨てが1割引きでもそうでない場合でも適用」の場合です。

  1. 主目的である、単価×数量を求めます。
    =@[単価]*@[数量]
  2. 1割引きの計算式を作成します。
    =@[単価]*@[数量]*0.9
  3. ここでIF関数を考えます。
    切り捨ては全体にかかるので最後になります。
    =IF(@[単価]*@[数量]>10000,
  4. IF関数に合体します。
    =IF(@[単価]*@[数量]>10000,@[単価]*@[数量]*0.9,@[単価]*@[数量])
  5. 全体を切り捨てます。
    =INT(IF(@[単価]*@[数量]>10000,@[単価]*@[数量]*0.9,@[単価]*@[数量]))

このように、2つの場合でかなり考え方もやり方も最終的な仕上がりの計算式も変わりますので、指示者に面倒でも確認をするようにしています。

また、どちらの場合でも、「@[単価]*@[数量]」が何度も使われているためExcelの新関数「LET関数」の出番になります。

LET関数の詳細はこちらから

「日付の列が月末だったらその月の合計を計算して」の例

これはテーブルに日付と売り上げが入っていて、その月の最終行になっていたらその月の合計値を表示したいという、テクニカルなのですが、よくある例です。

この情報からはExcelの関数に落とし込むまで、かなりな日本語分解力が求められます。

月末を求める

  • もしも月末だったらの条件を求める
  • 月計を求める
  • 月計の範囲を求める
  • 範囲を求めるために月初日を求める
  • 範囲を求めるために月末日を求める

このようなことが必要になり、情報の整理がなかなかつきにくいこと、そして、主目的である「月計を求める」前にその条件がないとできないことにも気づかなければなりませんし、条件を作るには月末を考えなければいけないということになります。この場合の発想方法は次のようなプロセスで考えていく一例です。

  1. 月計を求めるにはその日が属している月の合計になる
    =SUMIF？
    その日が属しているのを判定するには、その月がいつからいつまでの範囲にあるかを調べる、つまり条件は2つあることに気付くのがポイントです。ここではSUMIF関数などの条件付き集計関数には、1つだけの条件と複数条件の2つがあることを知っていなければこの発想はできません。・
  2. SUMIF関数は複数条件はできないので、SUMIFS関数にする
    =SUMIFS
  3. SUMIFS関数の条件の月初日を求める
    =EOMONTH([@販売日],-1)+1
    月初日は、前の月の1日後なので、月末日を求める必要があります。月末日を求める関数はEOMONTH関数であることにたどり着く必要があります。前の月の-1、翌日+1を計算式します。
  4. 月末日を求める
    =EOMONTH([@販売日],0)
    当月の月末なので「0」です。
  5. 月計を求める
    =SUMIFS([売上],[販売日],">="&EOMONTH([@販売日],-1)+1,[販売日],">="&EOMONTH([@販売日],0))
    SUMIFS関数の使い方はもとより、「”>=”」「”<=”」の不等号を「&」演算子で結ぶことができるということにもたどり着いておく必要があります。
  6. 月末ならばの条件を求める
    =IF(EOMONTH([@販売日],0)=[@販売日],
    その行の販売日がその月の月末だったらという条件を作ります。
  7. 合体する
    =IF(EOMONTH([@販売日],0)=[@販売日], SUMIFS([売上],[販売日],">="&EOMONTH([@販売日],-1)+1,[販売日],">="&EOMONTH([@販売日],0)),"")
    そうでなければの条件に空白を設定します。

このようにそれぞれを求める関数が何か調べながらの作業になります。

関数を調べる方法はこちら

数式コピーの手順

数式を作成していると、一時的に別のセルに計算式を取っておきたいときがあります。その計算式を実際の計算式に入れる方法は、コピーをすればいいのですが、そのコピーするときに若干のクセがあるので、操作方法を解説します。

  1. 一時的に保存しておいた計算式のセルをダブルクリックし入力モードにする

  1. コピーしたい計算式をドラッグで範囲選択

  1. Ctrl＋Cでコピー
  2. ここでESCキーで入力モードを解除※ここがポイント

  1. 本番の計算式が入るセルをダブルクリック

  1. 置き換える範囲をドラッグで範囲選択またはとっておいた計算式が入るところをクリック

  1. Ctrl＋Vで貼り付け

  1. Enterキーで確定

入力モードの解除のESCキーを忘れると計算式がわからなくなるので、ここだけ押さえておきましょう。

一時的に値を計算するセルを用意する

このような複雑な計算式を作成する場合、上記のように全部の計算を1セルに収めるのではなく、計算のプロセスごとにセルを用意するというのも良い手です。計算の途中経過を見せたくない、操作させたくないのであれば列を非表示にします。

  • 月初日の計算式：=EOMONTH([@販売日],-1)+1
  • 月末日の計算式；=EOMONTH([@販売日],0)
  • 月計の計算式：=SUMIFS([売上],[販売日],”>=”&[@月初日],[販売日],”<=”&[@月末日])
  • 月末かどうかの計算式：=EOMONTH([@販売日],0)=[@販売日]
  • 最終の計算式：=IF([@月末かどうか],[@月計],””)

まとめ

このような複雑な関数を組み合わせることを覚える方法なかなか難しいとは思いますが、一言でいえば経験数です。いろいろな困ったケースに当たったときに、いかに解決し、その解決した方法が便利だなと印象付いたのかの数です。

この記事は発想法のスタートですが、ここから、もし独学でレベルを上げたいということであれば、問題集やドリルといったものに取り組むことでその経験をすることができます。

一気にレベルを上げるには、相当難しい問題集をしないといけないのですが、そのような問題集には当たったことがありません。できれば解説はないような問題集に取り組み、最終的な完成例を見て、今回の記事のようなプロセスでこう発想していくんだという経験を詰めれば最高です。

私のようなExcelの情報を発信している人間がどれだけそういった課題を発信していけるか、また、そういう経験をしてわからなくなったときに、手取り足取りではなく自ら答えにたどり着けるインストラクションができるかにかかっているので、その部分はがんばっていきたいと思っています。

今回複数の関数を組み合わせた計算式がどのような発想の元、作成されているかを説明しましたが、本当にExcelができるというのは決してVLOOKUP関数が使えるだけではなく、このような他の計算式を組み合わせられるスキルを持っているかどうかなので、関数の組み合わせができるかどうかというのは、それくらいExcelにとって重要なことなのです。

コメント

タイトルとURLをコピーしました