Excel。これは便利。VLOOKUP関数の列番号にIFS関数をつかってみた。【flexible】

Excel。これは便利。VLOOKUP関数の列番号にIFS関数をつかってみた。

＜VLOOKUP＋IFS関数＞

次の表があります。

 

A4:D8に店舗別の販売表があります。

この表から、A2に店舗名を入力したら、B2にB1の見出しの売上データを表示したいわけです。

5月売上と固定していれば、次のような数式をB2に設定します。

=VLOOKUP(A2,A5:D8,3,FALSE)

確かに、5月売上の中野店の販売金額を表引きすることができます。

ただ、VLOOKUP関数の3番目の引数の列番号を「3」と固定してしまうと、4月売上や6月売上の列から表引きすることができません。

B1の見出しを変更したら、それに合わせて、列番号を変更するのは面倒です。]

どうにか、表引きすることはできないものでしょうか。

そこで、列番号にIF関数などの条件分岐する関数をつかってみたらどうなるのでしょうか。

今回は、4月売上・5月売上・6月売上を3列ありますから、IFS関数をつかって、VLOOKUP関数を修正してみます。

=VLOOKUP(A2,A5:D8,IFS(B1="4月売上",2,B1="5月売上",3,TRUE,4),FALSE)

では、B1の見出しを6月売上で、A2の店舗名を渋谷に変更してみます。

3639と正しく表引きされていることが確認できます。

では、設定したVLOOKUP関数を確認しておきましょう。

=VLOOKUP(A2,A5:D8,IFS(B1="4月売上",2,B1="5月売上",3,TRUE,4),FALSE)

最初の引数は、検索値。

店舗名で表引きをしますので、A2です。

2番目の引数は、範囲。

表なので、A5:D8。見出し行は不要です。

そして、3番目の引数、列番号。

ここは2番目の引数で設定した範囲の左から何列目のデータを表引きするのかという数値を設定します。

4月売上から6月売上まで自由に選択したいわけなので、列番号は2～４となるわけです。

ここで条件分岐ということで、IFS関数をつかってみました。

IFS(B1="4月売上",2,B1="5月売上",3,TRUE,4)

4月売上ならば、2。

5月売上ならば、3

それ以外は、4

と条件分岐するようにしました。

VLOOKUP関数の最後の引数は、完全一致なので、FALSEです。

これで、複数の見出し列に対応した自由度の高い表引きをすることができました。

Excel。年齢から、F1～F3の年齢層を手早く求めたい【Age group】

Excel。年齢から、F1～F3の年齢層を手早く求めたい

＜IFS関数＞

マーケティングで年齢によって、F1～F3（女性）やM1～M3（男性）というように区分けすることがあります。

F1は、20歳〜34歳の女性

F2は、35歳〜49歳の女性

F3は、50歳以上の女性

となっています。

次の表を使って、どのようにしたら、手早く、F1～F3と区分けすることができるのかを考えていきます。

B列に年齢が入力されています。

C列に、年齢に合わせたF1からF3と区分けして入力したいわけです。

やり方ですが、IF関数を複数ネストする方法や、別表をつくって、VLOOKUP関数をつかって表引きする方法など、色々な方法が考えられます。

そこで、今回は、IFS関数をつかって、区分けしていきます。

 

C2にIFS関数をつかった数式を設定しました。

=IFS(B2:B12>=50,"F3",B2:B12>=35,"F2",B2:B12>=20,"F1",TRUE,"")

これで、F1～F3という年齢層での区分けを求めることができました。

IFS関数は、多分岐処理ができるIF関数です。

論理式1にB2:B12>=50とB2:B12という範囲で設定したことで、スピル機能に対応したIFS関数にすることができます。

オートフィルで数式をコピーする必要はありません。

この数式のポイントは、50以上という条件から設定することで、つぎの、B2]B12>=35と設定しても、50以上はすでに、判定済みなので、35～49の場合ということになります。

0～19は対象外なので、TRUEで「””（ダブルコーテーション×2）」として空白を表示する設定にしています。

Excel。1つまたは複数の条件で分岐処理するのが、IFS関数です。【IFS】

Excel。1つまたは複数の条件で分岐処理するのが、IFS関数です。

＜関数辞典：IFS関数＞

IFS関数

読み方： イフズ　　

読み方： イフエス

分類： 論理 

IFS(論理式1,値が真の場合1,…)

1つまたは複数の条件で分岐して異なる計算結果を返す

Excel。ABC分析の表をスピル機能で、手早くつくってみよう。【ABC analysis】

Excel。ABC分析の表をスピル機能で、手早くつくってみよう。

＜SUM関数・IFS関数＞

パレート図のもとになる、「ABC分析」。

色々な関数をつかってABC分析の表をつくるのですが、Excelに加わったスピル機能をつかってつくってみると、今までよりも、手早く、つくることができます。

C列の構成比を算出します。

C2に設定した数式は、新宿の売上高を全体の合計で除算するわけですが、合計値はありませんね。

また、合計を絶対参照にして、除算しなければいけません。

ところが、スピル機能の登場によって、C2の数式は、次のように設定することができます。

=B2:B11/SUM(B2:B11)

オートフィルで数式をコピーすることもなく、スピル機能によって、C3:C11に数式が反映（ゴースト）されます。

D列の構成比の累計値は、始点のセルを絶対参照にした、数式をつくります。

D2の数式は、

=SUM($C$2:C2)

オートフィルで数式をコピーします。

C列とD列は、パーセント表示の小数点第一位という設定をすることで、視覚的にわかりやすくします。

E列は、構成比の累計からABCのランク分けを行います。

今回は、80%未満は、A。90%未満はBで、それ以外はCとします。

三分岐なので、色々な関数で算出することができますが、今回はIFS関数を採用しました。

E2のIFS関数をつかった数式は、

=IFS(D2:D11<80%,"A",D2:D11<90%,"B",TRUE,"C")

スピル機能が使えるので、セル番地を指定するのではなく、範囲選択をD2:D11とすることで、オートフィルで数式をコピーする必要がありません。

IFS関数では、「TRUE」を論理式でつかうことで、「それ以外」として設定することができます。

これで、ABCとランク分けすることができました。

今までつかっていた数式もスピル機能に対応した数式に変更することによって、可読性が向上するなど、メリットもあるかもしれませんので、見直してみるのもいいかもしれませんね。

Excel。多分岐処理のIFS関数。「それ以外」の判断はどう設定すればいいの【other than that】

Excel。多分岐処理のIFS関数。「それ以外」の判断はどう設定すればいいの

＜IFS関数＞

今までは、IF関数にIF関数を重ねたネストで多分岐処理に対応していました。

そして、登場したIFS関数で多分岐処理を設定するのが、より手早く設定できるようになりました。

ただ、条件を設定するときに、「それ以外」をどうしたらいいのかが、わかりにくいので、確認しておきましょう。

次の表を用意しました。

得点が90より大きければ「A」。

60より大きければ「B」。

それ以外は「C」とします。

C2の数式は、

=IFS(B2:B7>90,"A",B2:B7>60,"B",TRUE,"C")

と設定しています。

なお、スピル機能によって、オートフィルで数式をコピーする必要はありません。

それでは、引数を確認しておきます。

1番目の引数は、論理式1。

条件を設定しますので、「B2:B7>90」と、B2:B7とフィールドで範囲選択を指定することで、スピル機能をつかうことができます。

2番目の引数が、論理式1が真の場合の結果を表示させます。

このあとは、これを繰り返すことで、多分岐処理を設定できます。

そして、「TRUE」を設定することで、それ以外の場合の条件を設定することができます。

また、Excelでは、TRUEを「1」としていますので、

=IFS(B2:B7>90,"A",B2:B7>60,"B",1,"C")

としても、対応してくれます。

ただし、この「1」はなんなのか、わかりにくいので、「TRUE」とするようにした方がいいように思えます。

Excel関数辞典　VOL.36。IF関数～IMAGINARY関数

＜Excel関数＞

今回は、IF関数～IMAGINARY関数までをご紹介しております。

IF関数

イフ

条件で分岐して異なる計算結果を返す

IF(論理式,真の場合[,偽の場合])

IFERROR関数

イフエラー

対象がエラーの場合に指定した値を返す

IFERROR(計算式,エラーの場合の値)

IFNA関数

イフエヌエー

結果が#N/Aの場合は指定した値を返す　 イフ・ノン・アプリカブルの略

IFNA(計算式,エラーの場合の値)

IFS関数

イフエス（イフズ）

1つまたは複数の条件で分岐して異なる計算結果を返す

IFS(論理式1,真の場合1[,論理式2,真の場合2…])

IMABS関数

アイエムアブス

複素数の絶対値を算出する

IMABS(複素数)

IMAGINARY関数

イマジナリー

複素数の虚数係数を取り出す

IMAGINARY(複素数)

Excel。まだIFIFIFIF関数で条件分岐させているの？IFS関数使ってみませんか？【IFS】

Excel。まだIFIFIFIF関数で条件分岐させているの？IFS関数使ってみませんか？

＜IF関数・IFS関数＞

昔から使っているファイルで使用している関数をそのまま使うのもいいのですが、最近のExcelには、新しい関数が追加されています。

もしかしたら、使う使わないは別として、知っていればよかったというケースもあるかもしれません。

そこで、今回は、多重分岐でお馴染みのIF関数の大量ネストをIFS関数でおこなったらどうなるのか、確認していきます。

次の表を用意しました。

 

やりたいことは、D列にアルファベット評価を算出したいわけです。

その区分の条件が、90点以上がA。70点以上がB。60点以上がC。50点以上がDで、それ以外はEとしております。

Excel経験者だと、「VLOOKUP関数でやればいいじゃん。」って思うところですが、今回は、ご了承のほど。

IF関数を多重に使用したネストで数式を確認してみます。

D2の数式は、

=IF(C2>=90,"A",IF(C2>=70,"B",IF(C2>=60,"C",IF(C2>=50,"D","E"))))

ご覧のように、IF関数が4回使用することで、A～Eを算出しておりますが、結構大変ですし、面倒。

しかも、繰り返すことで、ミスも発生しやすくなります。

当然のことながら、区分の条件がさらに増えれば、それだけIF関数を繰り返す回数が増えるわけで、大変です。

そこで、新しく登場したIFS関数を使うことで、かなりスマートな数式で対応することができるようになりました。

今度は、IFS関数で、算出してみます。

D2をクリックして、IFS関数ダイアログボックスを表示しましょう。

 

論理式1に条件を設定して、それが真の場合なら、どうする？というのを繰り返していきます。

IF関数の中にIF関数をつくるよりも、条件を繰り返し設定していくだけで完成します。

それでは、D2にIFS関数でつくった、数式を確認しておきます。

=IFS(C2>=90,"A",C2>=70,"B",C2>=60,"C",C2>=50,"D",TRUE,"E")

とても、IF関数を繰り返すネストよりも、シンプルな数式で算出することができるようになりました。

このIFS関数。毎日毎日作るわけではないので、忘れがちになるのが、最後の「TRUE」です。

条件と完全合致ならば、いいのですが、今回のように、50未満は全部「E判定」なわけです。

つまり、『それ以外は～』の部分。

これがある場合には、最後の論理式に、「TRUE」を入力する必要があります。

 

Excel関数のスキルアップには、どうしても、「ネスト」というスキルは必要なので、昔から、IF関数にお世話になっていましたが、IFS関数も覚えておいて損はなさそうです。

なお、「値が真の場合」には、IF関数同様に、IF関数をネストすることもできます。

あまり意味がありませんが、このようにすることもできます。

=IFS(C2>=90,IF(B2="豊田","Y","A"),C2>=70,"B",C2>=60,"C",C2>=50,"D",TRUE,"E")

もし90点以上だったら、氏名が「豊田」だったら、「Y」にして、それ以外は、「A」のんままという数式を作ることもできます。

 

IFS関数も、アイディアによって、色々使える可能性がありそうですね。

Excel。データをABCで振り分ける、パレート図用のABC分析の表を作ってみよう。【Pareto chart】

Excel。データをABCで振り分ける、パレート図用のABC分析の表を作ってみよう。

＜IFS関数＞

データをABCというステータスで振り分けることで、そのデータが全体の位置の中でどのあたりのポジションにあるのかがわかりやすくなるのが、ABC分析です。

ABC分析を元にして、縦棒グラフと折れ線で構成されたグラフがパレート図になるわけですが、今回は、パレート図を作るためのABC分析の表を作っていきましょう。

【並び替えをしておく】

既存の軸となるデータを降順で並び替えをしておきます。

今回は、金額を軸にしております。この金額の合計も算出しておきましょう。

なお、オートSUMボタンの合計やSUM関数を使うのであれば、先に降順で並び替えをしておきませんと、合計行も含めて並び替えを行ってしまい、やり直す必要が発生しますので、手順について注意が必要です。

【構成比を算出】

それぞれのデータがどのぐらいのシェア。
つまり構成比をもっているのかを算出する必要がありますので、構成比を算出していきます。

構成比は、そのデータを全体の合算値で除算すれば算出できます。
合算値である、B21を使うので、絶対参照を使う必要があります。

C2の数式は、
=B2/$B$21
と設定し、オートフィルを使って数式をコピーします。

0.192…と小数点で表示されますが、これでは、わかりにくいので、パーセント表示にしています。なお、今回は小数点第一位までの表示としています。

【構成比の累計を算出する】

つづいて、構成比の累計を算出します。ここで算出する値をつかって、ABCに振り分けていきます。

累計を算出するには、前のデータまでの合算値とデータとの合算値で求めることができますが、次のような数式を作ることで簡単に算出することができます。

D2に設定した数式は、
=SUM($C$2:C2)
範囲選択の先頭である始点を絶対参照で留めておきます、そして、終点には相対参照のままにします。

これにより、オートフィルを使って数式をコピーすると、始点を固定しておくことができ、データの範囲が拡張することができます。

このやり方だと、この数式だけで累計の算出が出来ます。

【ABCと条件分岐で判定させる】

いよいよ、ABCに振り分けます。ランキングのようなABCなので、RANK.EQ関数を使うように思うかもしれませんが、あくまでも条件分岐です。

今回は、累計が80%未満なら、「A」80%～90%未満なら「B」。それ以外は「C」とします。

旧来ならば、IF+IF関数のようなIF関数のネストで算出させるのですが、今回は、Office365から登場したIFS関数を使うと算出が簡単に作成できます。

E2にIFS関数を使った数式を設定します。

=IFS(D2<80%,"A",D2<90%,"B",1,"C")
IFS関数ダイアログボックスで確認してみましょう。

論理式1に、D2<80%というように、条件を設定して、真の場合を”A”と設定します。これを繰り返していきます。今回の最後は論理式3ですが、ここには、「1」。
TRUEを入力します。

Excelは、TRUEを「1」。FALSEが「0」と設定されていますので、「1」と入力しています。

あとは、オートフィルを使って数式をコピーすると、完成です。

このように、簡単な計算式で、ABC分析の表を作ることができます。

Excel。初めての人が困る。IF+IF関数のネスト。そしてIFS関数【IFS】

Excel。初めての人が困る。IF+IF関数のネスト。そしてIFS関数

＜IF関数・IFS関数＞

Excelの経験が少ない人。初心者の人。
初めての人が困りやすいテクニックというのがどうしてもありまして、
今回は、複数の関数をつかう。

【ネスト】の代表格のIF+IF関数を改めてご紹介していきます。

そして、今、Offiice365のExcel2016から登場したIFS関数を知っていれば、
このIF+IF関数から逃げることができますので、
そちらも合わせて確認しておきましょう。

次の表があります。

C列の判定には、
得点が80点以上なら　A
得点が60点以上なら　B
それ以外は　C
という判定をしたいわけです。

このぐらいの件数ならば、
根性で『目視』というのでもOKかもしれませんが、
件数が増えたらそういうわけにもいきません。

ですから、数式を作って、
オートフィルで数式をコピーしていくというのが
基本的な作業の流れになるわけですね。

ここでポイントになるのが2つあります。

1つめは、図解にすること。Excelの経験値が増えてくると、
この図解を頭の中でイメージするのですが、経験値が少ない人は、
図解＝イメージ図を作ることが最優先だと考えます。

流れとしては、最初の質問をします。
その結果が80点以上なのかを『判定』させます。
その後、二つ目の質問をして、60点以上なのかを『判定』させることをしたい。

これで、関数の引数＝カッコの中の情報が揃いましたので、
関数を作っていく作業に入っていきます。

2つ目は、”以上”というのExcelでは、
【比較演算子】というのを使って表現します。

さすがに、日本語で”以上”と書いてもExcelは理解してくれません。

当然英語でもダメですね。

今回は、”以上”は>=と書きます。
≧という字は直接入力でないので、>=と書きます。

なお、=>と逆に書くとエラーになってしまいます。
この比較演算子を知る必要があります。

では、実際に作っていきます。Excelのベテランさんは手入力だと思いますが、
今回は、関数挿入ダイアログボックスで作っていきます。

関数挿入ダイアログボックスを表示して、IF関数を探します。

関数名からIFを選択してOKボタンをクリックすると、
IF関数のダイアログボックスを表示します。

論理式は、質問文なので、ここには、
B3>=80
値が真の場合は、YESなので、
“A”
そして、値が偽の場合には、次の質問文をセットする。

つまり、再度IF関数を作る必要があります。

ここが、今回最大のポイントですね。

名前ボックスが、ネスト用の関数挿入ボックスに変わりますので、
▼をクリックして、一覧からIF関数を見つけてクリックします。

最初、IF関数が消えちゃったと思ってしまうのですが、
数式バーをみると、IF関数を作っている途中ということがわかりますので、
慌てずに作成を継続していきましょう。

論理式には、
B3>=60
値が真の場合には、”B”
値が偽の場合には、”C”
と入力して、あとはOKボタンをクリックして完成です。

完成した数式をオートフィルでコピーして作業終了ですね。

C3の数式は、
=IF(B3>=80,"A",IF(B3>=60,"B","C"))
と入力されていることが確認できます。

今回のIF+IF関数ですが、Excelの経験が少ない人は、
まずIF関数を練習してから
IF+IF関数を練習するとスキルが定着しやすいように感じます。

ビジネスでは、知っているといいスキルの一つですので、
練習していきましょう。

そして、ご存知の方は、経験が少ない人に教えてあげていきましょう。

【新しく加わったIFS関数】

IF+IF関数というのは、
Excel関数の初級から中級へ向かう一つのステップなのですが、
Office365のExcel2016に【IFS関数】というのが登場して、

今回のような判断分岐は、IFS関数を使うと便利になっていますので、
IFS関数の場合も合わせて確認しておきましょう。

C3をクリックして、IFS関数ダイアログボックスを表示します。

論理式1には、B3>=80
値が真の場合1には、”A”
論理式2には、B3>=60
値が真の場合2には、”B”
論理式3には、”TRUE”
値が真の場合3には、”C”
そして、OKボタンをクリックして、オートフィルで数式をコピーしましょう。

“TRUE”がそれ以外ということですね。
ここがこの関数のポイントなのかも？
なかなかTRUEって思い浮かばないかも…

C3の数式は、
=IFS(B3>=80,"A",B3>=60,"B",TRUE,"C")

このように、IFS関数というのも新しく登場しましたので、
IF+IF関数もIFS関数も両方とも使えるようになるといいですね。

Excel。条件分岐の新しい関数。IFS関数は多分岐だけじゃなくて、とっても便利

Excel。条件分岐の新しい関数。IFS関数は多分岐だけじゃなくて、とっても便利

＜IFS関数＞

Office365のExcel2016に加わった、新しい関数。【IFS関数】。

この関数は、今までのIF関数に”S”を付いただけでなく、
とっても便利な関数なので、今回はIFS関数をご紹介していきます。

次の表があります。

点数が80点以上ならばA。それ以外ならば、Bというようにするならば、
IF関数を使いますよね。

では、
点数が80点以上ならばA。
点数が60点以上ならばB。
点数が40点以上ならばC。
点数が20点以上ならばD。
それ以外は、E。

というように条件分岐をする場合は、どのようにするのでしょうか？

このような場合、IF関数しかなかった時には、
IF関数の中にIF関数をいれるネストで数式を構成していきますよね。

しかし、IF+IF関数のネストぐらいならばいいのですが、
今回のように、5分岐させるとしたら、数式はどのようになるのでしょうか？

C2に数式を作ると次のようになりますね。

=IF(B2>=80,"A",IF(B2>=60,"B",IF(B2>=40,"C",IF(B2>=20,"D","E"))))

これだけでも、ネストの回数が多い感じがしますが、
10分岐とかなれば、IF関数を使うとしたら、想像しただけでも、
頭が痛くなりそうですよね。

そうなると、VLOOKUP関数を使ったり、
あるいは、CHOOSE関数を使ったりする方法がありますよね。

ただ、VLOOKUP関数はリストを作らないといけないし、
CHOOSE関数は、どうやったら、
1という数値を導き出せるかを考えなければいけません。

今回のように20ずつの差を考えると、
CHOOSE関数を使うのは難しいでしょう。

そこで、今回登場したIFS関数が便利なんです。

では、C2をクリックして、IFS関数ダイアログボックスを表示しましょう。

論理テスト1に、B2>=80
値が真の場合1に、”A”
と入力して、それを繰り返していきます。

通常のIF関数ならば、偽の場合に、IF関数をネストしますが、
IFS関数はネストしません。

最後の論理テスト5には、B2>=0でもいいのですが、
それ以外ということ、TRUEを入力します。
そして、値が真の場合5には、”E”と入力して、

OKボタンをクリックしましょう。

C2の数式は、
=IFS(B2>=80,"A",B2>=60,"B",B2>=40,"C",B2>=20,"D",TRUE,"E")

では、オートフィルで数式をコピーしてみましょう。

このように、算出することが出来ましたね。

数式自体、スリムでわかりやすいですね。

これならば、仮に条件分岐が10個あっても、
楽に数式を作ることができますよね。

でも、このIFS関数の真骨頂はココから。

次の表をご覧ください。

説明すると、E列に氏名の列があります。

G2に1と入力したら、氏名の1番目の内藤さんをH2に抽出するとしたら、
どのようにしたらいいでしょうか？

VLOOKUP関数を思い浮かべる人もいるかと思いますが、担当者番号などの通し番号。

すなわち主キーがありませんよね。
これでは、VLOOKUP関数は使えません。
ROW関数とかいろいろな関数のアイディアを
使わないといけないイメージがありますが、
このようなケースも、IFS関数で簡単に設定できるのです。

では、H2をクリックして、IFS関数ダイアログボックスを表示しましょう。

論理テスト1に、G2=1
値が真の場合1に、E2
として、繰り返していきます。

最後は、
論理テスト6に、G2=6
値が真の場合6に、E7
として、OKボタンをクリックしましょう。

数式は、
=IFS(G2=1,E2,G2=2,E3,G2=3,E4,G2=4,E5,G2=5,E6,G2=6,E7)

これで、出来ちゃうんです！

アイディア如何で色々なところで使えそうな感じがする、IFS関数。

機会があれば使ってみてください。

ただ、残念ながら、2018年1月1日時点で、
Office365のExcel2016にしか実装されていませんので注意が必要です。

通常のExcel2016には、ありませんので、ご注意のほど。

そうそう、注意が必要なのは、Excelのベテランさん。

Office365のExcel2016から勉強すると、IF+IF関数じゃなくて、
IFS関数を勉強してくるでしょうから、
現場で、IF+IF関数を読めない人が増えると思いますので、
教える準備を将来しておかないといけないのかもね。