【Excel】
if関数の組み合わせパターン 7選!
ここでは、IF関数との組み合わせを考えてみます。
CEILING関数の使い道
今や出退勤管理は、社員証カードで端末にピッとタッチすれば、自動的にその時刻がデータとしてサーバーに送られる会社も多いと思います。少し昔の話で恐縮ですが、アルバイトさんやパートさんの出退勤を紙のタイムカードを端末にガシャッと差し込んで印字していたときがありました。
そのプロジェクトでは、発注先にかかった人件費を請求することになっていて、請求時給単価に勤務時間をかけて金額を算出し明細を発注先に提出するというものでした。その際、明細書の紙だけでなくExcelでのデータが欲しいとのことで、この関数と出会ったと記憶しています。
物流センターというところは、現場作業員のみならず事務作業員も数多く携わっている場所です。極力手入力を減らし、効率的に行う方法を模索していたと思います。
CEILING関数(読み方:シーリングかんすう)
この関数は、時間計算の際によく使われます。
〇〇を切り上げるという結果を表示させます。
(〇〇切り下げる時はFLOOR関数(呼び方:フロアーかんすう)
物流センターなどでは、アルバイトやパートの方々が多く仕事をしています。出退勤管理の方法が、タイムカードを手で差し込み印字するという会社もあるでしょう。
そうしたローカル管理する際に便利な関数が、CEIING関数です。
一般的にアルバイトやパートさんの時給計算は、10分単位や15分単位、30分単位などで計算されていたりすることが多いと思いますので、ここでは、その計算をする場面を想定してみます。
if関数＋CEILING関数
人件費の単位は、会社によってマチマチです。多くの会社から"人"を出してもらっている物流センターでは、その支払形態の種類も多くなります。そして、人件費計算時の時間単位も様々なところが多いです。
一人一人の労働時間を、15分切り上げて表示してください。
下の表が用意されているとします。
ここでは、労働時間欄の数値を「労働時間15分切上げ欄」に、CEILING関数を使って結果を表示させたいと思ってください。
下の表の赤で囲った部分です。
次に、労働時間15分切上げ欄の赤枠セルに以下のように関数を入力します。
「F3を15分切り上げなさい」という意味になります。15分は、0:15をダブルクォーテーションで囲みます。つまり「労働時間5:23を、15分単位に切り上げなさい」となります。
Enterキーを押すと、以下のように「5:30」と表示されます。
次に、この計算式を2項目と3項目にコピーしたいと思います。
このままコピーするとNO.2はF3(6:29)を、NO3はF4(2:35)を参照しに行きます(相対参照と言います)が、それで正解なのでOKです。
そこで、計算式を入力したセル(今は「5:30」と表示されているセル)にカーソルをもっていき、黄色の矢印に向かって下の2つのセルを選択して、右クリックし、「形式を選択して貼り付け」→「数式」にチェックを入れて、下の「OK」ボタンを押します。
カーソルが点滅しているので、Escキーを押して点滅を消します。
そうすると、以下のようになります。
これで、完成です。
ここで説明を終わってもいいのですが、最初にIF関数との組み合わせをお話したのでもう少し続けます...
今度は、「もしアルバイトならば15分切上げ、そうでないなら(つまりパートならば)20分切上げ」という条件にしたいと思います。
下の表の赤枠で囲った部分に結果を表示させたいと思います。
(※この場合、2択が前提です。)
次に、下の表のように条件を書いておきます。(赤で囲ったところ)
次に、労働時間15分切上げ欄の赤枠のセルに以下のように関数を入力します。
つまり、IF関数の中にCEILING関数が入っている計算式です。「もしC3がC9ならば、F3を15分切り上げ、そうでないならば20分切り上げなさい」という意味になります。ゆえに、「もしNO.1の区分がバイトならば、5:23を15分切り上げ、そうでないならば20分切り上げなさい」ということです。
(5:23を15分単位で切り上げると5:30、15分単位で切り下げれば5:15です)
Enterキーを押すと、以下のように表示されます。
続いて、2項目と3項目にコピーします。
G3セル(ここでは5:30と表示されているセル)にカーソルを持っていき、F2キーを押すと計算式が表示されます。そこでC9のみに$マークをつけて絶対参照にします。
Enterキーを押したあとG3セル(5:30)セルをコピーし、下の2段を選択し「セルの書式設定」→「数式」→「OK」ボタンを押し、カーソルの点滅を消すために、Escキーを押します。
すると、以下のようになります。
ただ折角ですので、時給1200円の場合の支払額を計算してみましょう。
合計欄を作成して足すと14時間50分(14:50)です。
支払額合計欄を作成し、以下の計算式を入れます。
すると、以下のようになります。
計算式には、14:50に「×24」が入っています。これですが、画面上の時間表示は実際は小数点で管理されています。24時間を「1」とする「シリアル値」と呼ばれる表示ですので、計算しやすいように数字化します。
一応、検算してみますと...
1バイト:5:30→5時間30分→(1200×5)＋(1200×30÷60)＝6600
2パート:6:40→6時間40分→(1200×4)＋(1200×40÷60)＝8000
3バイト:2:40→2時間40分→(1200×2)＋(1200×40÷60)＝3200
合計は、17,800になり、OKです。
FLOOR関数は、逆に切り下げの時に使用します。
CEILINGをFLOORに置き換えて使用してください。
-
バラッド３ ～the album of LOVE～
（サザンオールスターズ）
2000年のベストアルバム。シングル曲、アルバム収録曲など全28曲を収録しています。シリーズの第3弾。ロックからバラードまでの名曲の数々は、日本人の心のヒダに染み入るメロディーばかりです。歌声に限らず多彩な才能と感覚は、一流の"エンターテイナー"そのものだと思っています。名盤。
2000年のシングル曲。ダブルミリオンを記録。バラエティ番組内のコーナーのテーマ曲でした。初めて聴いたとき"いい曲だなぁ"と素直に思いました。切ないラブバラード。鍵盤の単音から始まるメロディーが私を感傷的にさせます。サビからの大きな展開の歌詞とメロディは耳と心に強く迫ってくる感じ。哀愁を帯びた歌声。間奏のアレンジワークが格好いい。溢れんばかりの多彩な才能が凝縮された名曲。圧倒的な楽曲の"力"を感じる一曲です。
PVの印象が強くあるからか、一本の上質な映画を観たような気持ちになったことを思い出します。寄せては返す波のごとく、感情を強く揺さぶられた記憶。
ISERROR関数の使い道
Excelで資料作りをしていると、「#N/A、#DIV/0!、#VALUE!」などのエラー値が表示されることがあります。例えば「1÷0」を計算すると、#DIV/0!のエラー値が出ます。(逆に「0÷1」は0です)
上司によってはこのエラー値を好まず、「消してくれ!」と怒ったり「そのままでいいよ」とおっしゃる方もいました。ただ、何か重要なオフィシャル的な資料ですと、やはりエラー値を空白(ブランク)にするように求められることが多かったと思います。
損益資料の作成に関わったときに、基礎資料にそのままエラー値を載せたら相当怒られまして、その記憶が鮮明にあります。ですから、何とかエラー値を消す方法はないかと職場の人に聞いて(当時は今のようなネット環境はなく･･･)出逢ったのがISERROR関数でした。
ですから、エラー値の場合は空白を表示させ、そうでない時はそのまま計算結果を表示させるというIF関数とISERROR関数のセット利用が身についてしまっています。この関数を見ると、どこか懐かしく苦い思い出が蘇ってくるのです。
ISERROR関数(読み方:イズエラーかんすう)
この関数は、表示結果がエラー値(#N/A、#DIV/0!、#VALUE!など)の場合、TRUEを表示させます。逆に正しければ、FALSEを表示させます。つまり、計算結果がエラーかどうかの判定をする関数です。
多くは、これまたIF関数とセットで使ってきましたので、ここでは、IF関数とISERROR関数との組み合わせをしたいと思います。これにより、エラー値を特定の言葉に変えることができます。ISERROR単体で使用することはあまりないと思いますので、IF関数以外の他の関数との組み合わせも試してみると良いでしょう。
if関数＋ISERROR関数
トラック輸送において「積載率」は重要な要素です。積載率が落ちれば、それだけ利益がダウンしていくことになります。また、物流センターでは、主にターミナル間の長距離輸送で活躍する10tトラック。このトラックには、1.1m×1.1mパレット(通称:イチイチパレット)を16枚積むことができます。ここでは、積載率を計算する場面を想定してみます。
もし積載率欄がエラー表示されるならば「未確定」と表示させて下さい、そうでないならば、そのまま積載率を表示させて下さい。
下の表が用意されているとします。
その前に、E3からE5までを選択し、マウス右クリック→セルの書式設定→表示形式→パーセンテージ→OKボタンを押して、%表示されるようにしておきます。
ここでは、IF関数とISERROR関数を使って、実績パレット数÷積載可能パレット数の値を、積載率(%)欄に表示させたいと思ってください。
下の表の赤で囲った部分です。
次に、積載率(%)欄の赤枠のセルに以下のように関数を入力ます。
これは、「実績パレット数÷積載可能パレット数」という意味です。
Enterキーを押すと以下のように「#VALUE!」と表示されます。これがエラー値です。
次に、このエラー値「#VALUE!」を『未確定』に変更して表示させたいと思います。そこで、計算式を入力したセル(今は「#VALUE!」と表示されているセル)にカーソルをもっていき、F2キーを押します。
すると計算式が表示されますので、以下のように、IF関数を加えます。Enterキーを押します。
すると、以下のように「未確定」と表示されました。
ちなみに未確定ではなく「空白」にしたい場合は「""」とダブルクォーテーション2つにすると、空白になります。
次に、この計算式を2項目と3項目にコピーしたいと思います。
このままコピーします。すると802はD4/C3を、803はD5/C4を参照しに行きます(相対参照と言います)が、それで正解なのでOKです。
そこで、計算式を入力したセル(今は「未確定」と表示されているセル)にカーソルをもっていきコピーし、黄色の矢印に向かって下の2つのセルを選択して、右クリックし、「形式を選択して貼り付け」→「数式」にチェックを入れて、下の「OK」ボタンを押します。
カーソルが点滅しているので、Escキーを押してその点滅を消します。
すると以下のように表示されます。
これで、完成です。
つまり、IF関数の中にISERROR関数が入っている計算式です。
「もしD3/C3がエラー値ならば、未確定と表示し、そうでないならD3/C3の結果を表示しなさい」という意味になります。
このIF(ISERROR(～は、ホントによく使用しますので、知っておくと便利だと思います。
-
SO REAL [CD]
1988年のオリジナルアルバム。初期のシングル曲、アルバム曲など全12曲を収録。ソウルフルなナンバーばかりで、タイアップ曲も多いせいか、耳に残るテイストが散りばめられています。天性の歌声と英語の発音がとてもキレイな実力派シンガーのエッセンスを味わえる一枚。
1987年のシングル曲。アニメ番組のオープニング曲でした。声量があり英語の発音もキレイ。"パワーあるなぁ"という印象です。幅広い音域を歌いこなす実力派シンガーの一曲。
ホントに歌がお上手で"驚きと感嘆"。すごいシンガーが出てきたと強く思いました。歌うことが大好きという雰囲気が伝わってくるのです。このアニメは当時からよく観ていましたが、再放送もよくあったと思います。単行本はほぼ全巻購入していて、楽しませてもらいました。主人公が格好いい。忘れられない一曲。
ROUND関数の使い道
物流会社に勤めていると、容積や重量などとは切っても切れない関係になります。どちらも料金計算には必ず絡んできますし、物流センター内に製品をどれくらい保管できるか、はたまた容積を重量換算したりといろいろあります。
ある時、何千件という出荷データの処理をしなくてはならないときがあり、そのデータの重量が10.24kgみたいな小数点以下2位もあれば、10kgといった整数のものもありまして、そのデータの重量を、すべて整数にする必要に迫られました。
まさか、一個一個電卓を叩くわけにもいかず、同僚に聞いたらいい関数があるよと教えてくれました。
それが、ROUND関数との出会いでした。
その後は、ある位での四捨五入や切り上げ、切り下げなどもやり方が分かるようになったと思います。ですから、ROUND関数と聞くと"重量"を思い浮かべるクセがついてしまっています。
ROUND関数(読み方:ラウンドかんすう)
この関数は、四捨五入する関数です。
似たものに、ROUNDUP(読み方:ラウンドアップ;切り上げる)、ROUNDDOWN(読み方:ラウンドダウン;切り下げる)があります。ROUNDの代わりに入れ替えればいいだけなので、ここではROUND関数のみ扱います。
if関数＋ROUND関数
IF関数とROUNDR関数との組み合わせをしたいと思います。物流会社には、当然納品明細があります。紙ベースであったり、電子データだったりします。基本的に、物流は重量をベースに動いていることが多いと思います。ですから、今回はその重量を対象にして考えます。ここでは、納品先の重量を計算する場面を想定してみます。
もし製品aならば、重量を小数点第一位にて四捨五入し、そうでないならば(a製品以外ならば)、十の位にて四捨五入を表示させて下さい。
下の表が用意されているとします。
その前に、F3からF4までを選択し、マウス右クリック→セルの書式設定→表示形式→数値→小数点以下の数値:2→OKボタンを押して、小数点以下2位まで表示されるようにしておきます。
ここでは、重量(kg)欄の数値を、a製品なら小数点第一位にて四捨五入、そうでないならば(つまりb製品ならば)十の位にて四捨五入した数値を、IF関数とROUND関数を使って結果を表示させたいと思ってください。
下の表の赤で囲った部分です。
次に、四捨五入後(kg)欄の赤枠セルに以下のように関数を入力します。
「もしNO.1において製品欄がaならば、32.45を小数点第一位にて四捨五入、そうでないならば、32.45を十の位にて四捨五入」という意味です。
この＝ROUND(E3,1)の1ですが、1は小数点第一位にて四捨五入を導き示します。0であれば、一の位にて四捨五入を導き出します。－1であれば、十の位にて四捨五入を導き出します。
Enterキーを押すと、以下のようになります。
次に、この計算式を2項目にコピーしたいと思います。
このままコピーします。NO.2はE4を参照しに行きます(相対参照と言います)が、それで正解なのでOKです。
そこで、計算式を入力したセル(今は「32.50」と表示されているセル)にカーソルをもっていきコピーし、黄色の矢印に向かって下のセルを選択して右クリックし、「形式を選択して貼り付け」→「数式」にチェックを入れて、下の「OK」ボタンを押します。
カーソルが点滅しているので、Escキーを押してその点滅を消します。
すると以下のように表示されます。
これで、完成です。
つまり、IF関数の中にROUND関数が入っている計算式です。
ちなみにまとめますと、
- round(～...は四捨五入
- roundup(～...は切り上げ
- rounddown(～...は切り下げ
そして()内の最後の数字ですが、
- 0➝1の位で、四捨五入、切り上げ、切り下げをします
- 1➝小数点第一位で、四捨五入、切り上げ、切り下げをします(2ならば小数点第ニ位で)
- -1➝10の位で、四捨五入、切り上げ、切り下げをします(-2ならば100の位で)
-
SINGLES BEST
2008年のベストアルバム。シングル曲、アルバム収録曲など全28曲を収録。バラードからポップまで多彩な魅力を放つ楽曲ばかりです。ハイトーンの美しさは論を待ちませんが、そのメロディアスな旋律を生み出す才能も抜群です。心の奥底の心情を、言葉にして伝える力も兼ね備えたシンガーソングライターだと思っています。良質な楽曲が揃っている一枚。
1990年のシングル曲。疾走感あふれるポップなナンバーです。アレンジもお洒落で気持ちを一段上に運んでくれる感じ。しっとりとしたバラードとはまた一味違う明るさが心地いい。サビからのメロデイーライン展開が爽快です。
フィルムメーカーのCMソングで一躍注目を浴び、ランキング番組では、番組終了までに髪の毛をカットするという企画が印象的でした。当時からハスキーな高音は健在ですが、清涼感ある歌声とメロディは、沈んだ気持ちを前向きにさせてくれます。人生の中の幾つもの苦しい場面では必ず聴いている、そんな楽曲。
VLOOKUP関数の使い道
自分が欲しいデータを抽出して表示させるVLOOKUP関数。
やはり、Excel関数の中では花形だと思います。はじめて、この関数を使って答えを導き出せたときの感動を忘れることができません。この関数を知る前と後では、見つめる世界が変わったという感じでしょうか。
その後数カ月間やたらVLOOKUP関数を使っていて、とにかく＝VLOOKUP(～と、キーボードで手入力していくことが楽しくて仕方なかったのです。物流会社での配送料金の計算には、必要不可欠の関数です。料金表(料金タリフとも言います)は、納品先と重量別で作成されている場合が多く、2元的な検索をしないと料金を表示させることができないからです。
私にExcelの面白さと凄さを教えてくれたのが、このVLOOKUP関数なのです。
VLOOKUP関数(読み方:ブイルックアップかんすう)
この関数は、特定のデータを抽出する関数です。
少し小難しい言い方ですが、一次元検索で縦一列の検索を行います。
ある範囲の中から、〇〇という条件に合致するものを表示しなさいという関数です。
類似関数として、HLOOKUP(読み方:エイチルックアップ;横一列の検索)がありますが、ここでは省いてVLOOKUP関数を考えます。
if関数＋VLOOKUP関数
IF関数とVLOOKUP関数との組み合わせをしたいと思います。物流会社では、車両の手配はかかせません。モノを運ぶには鉄道コンテナや航空貨物便などがありますが、やはりトラックの役割が大きいと思います。そこで、重量の料金計算の重要性が増してきます。ここでは、販売価格を抽出する場面を想定してみます。
車両名に該当する販売価格を表示し、もし価格表(価格マスタ)にないときは、「調整中」と表示させて下さい。
下の表が用意されているとします。
ここでは、データの販売価格欄に、価格マスタの該当する販売価格をIF関数とVLOOKUP関数を使って結果を表示させたいと思ってください。
下の表の赤で囲った部分です。
まず、販売価格欄の赤枠のセルに以下のように関数を入力します。
(これがVLOOKUP関数そのものです)
「計算式の左横(この場合は新潟行き10トン)と同じ車両名を価格マスタの中で検索し 存在すれば、その販売価格(この場合は新潟行き10トン)の数値を表示しなさい」という意味です。
計算式の中身ですが、B10は新潟行き10トンで、これが検索キーです。そして、B3:D5は検索する範囲(価格マスタ内)です。最後の3は、選択範囲(価格マスタ内)を一番左(車両名の列)から数えて、"販売価格欄(いまはこの価格がほしいので)が何番目か"という数字です。この場合は3列目ですから、3という数値にしています。
仮に、支払価格を求めたい場合はこのケースだと2列目なので、計算式の最後の数字は、2になります。そして最後にFALSE(読み方:フォルス)と入れます。ここには検索の型が入るのですが、FALSEは完全に一致したデータを返すことを意味し、TRUE(読み方:トゥルー)は一番近いデータを返すことを意味します。ここでは、価格マスタの中に完全に一致したデータを表示させたいのですから、FALSEと入力します。
イメージとしては以下の表のようになります。
Enterキーを押すと、以下のようになります。
すると、「#N/A」が表示されました。そうです、前にISERROR関数のところで学習したエラー値です。新潟行き10トンは価格マスタには存在しません。ですからエラー値が表示されています。
そこで、IF関数＋ISERROR関数を加えて、エラー値を「調整中」と表示させることにします。
以下のように計算式を変更します。
Enterキーを押すと、以下のようになります。
次に、この計算式を2項目と3項目にコピーしたいと思います。
このままコピーすると、参照する価格マスタの表が一段下にずれてしまいます(これを相対参照といいます)。そこで、計算式を入力したセル(今は「調整中」と表示されているセル)にカーソルをもっていき、「F2」キーを押します。すると計算式が表示されますので、ここでB10はそのままで、Bの左に「$」マークを、3の左に「$」マークを、Dの左に「$」マークを、5の左に「$」マーク(後半部にもう一か所ありますから、そちらも$マークを付けます)を入力します。
これを絶対参照といい、どこに計算式を貼り付けても、価格マスタの表の場所は、ずれることはないというものです。
すると以下のようになります。
Enterキーを押すと、以下のようになります。
最後に、この「調整中」のセル(C10)をコピーして、黄色の矢印に向かって下の2つのセルを選択して、右クリックし、「形式を選択して貼り付け」→「数式」にチェックを入れて、下の「OK」ボタンを押します。カーソルが点滅しているので、Escキーを押してその点滅を消します。
すると以下のようになります。
これで、完成です。
つまり、IF関数の中にVLOOKUP関数が入っている計算式です。
私のExcel師匠(と言っても同期ですが)に言われた言葉をひとつ。
「関数の使いこなしは"センスと感性"。それらを磨くには、分からなくても、少しだけ今までと違う方法で考えてみること"」
-
スカーレット
1997年のシングル曲。ベストアルバム「CYCLE HIT 1991-1997 Spitz Complete Single Collection」に収録。シンプルなサウンドが、澄んだ歌声とともに一貫して流れています。大いに共感できる言葉の数々。サビの4音の響きは、音としてのそれを超えてきている感じ。"今この瞬間"という輝きを感じるのです。名曲。
二人で冬の動物園へ。
キリンの顔が驚くほど近くまで来て、同時に声を上げた。帰りのバスは大混雑で一緒に吊革に掴まって立っていた。途中で運良く席が一つ空いたので、"座って"と勧めたのだが、かぶりを振ってしがみついてきた。愛おしいと思った記憶。
IF関数＋INDEX関数とMATCH関数の使い道
縦と横にタイトルのある表の中の数値を、一発で抽出する関数が、INDEX関数＋MATCH関数です。
これが使えるようになると、一気にスキルアップが加速する印象です。これまで、INDEX関数単体で又はMATCH関数単体で使ったことはあまりなく、INDEX関数とMATCH関数の組み合わせでの使用がほとんどだったと思います。組み合わせてこそ、威力を発揮するものと捉えています。
物流会社でコスト管理担当になったときに、設定されている料金表からの価格の抽出は必須でした。それまで知っていたVLOOKUP関数では何故できないのか。
その理由として、VLOOKUP関数では「何列目のデータを抽出しなさい」というふうに、あらかじめ「列番」を指定することにより抽出できましたが、その列番が決まっていない場合、通常のVLOOKUP関数では厳しいのだと気づきました。(できないこともないと思いますが...)
物流センターの運用だけやっていてコストに無頓着だった私に、コスト意識を持たせてくれたのがINDEX関数＋MATCH関数なのです。
INDEX関数(読み方:インデックスかんすう)
この関数は、指定された行と列が交差するセルの値を表示させる関数です。INDEX関数は、MATCH関数(読み方:マッチかんすう)と組み合わせて使うことが多いので、ここでは行と列の交差する値を求めるものとだけ理解しておいてください。
簡単に説明します。
下の表が用意されているとします。
B製品を九州まで運んだときの料金を求めたいと思います。検索するデータ範囲は、数字が入っているところ(1～9)です。B製品は2行目、九州は3列目です。
INDEX関数の公式は、以下の通りです。
検索するデータ範囲にC3:E5を、行番号のところに2を、列番号のところに3を入力します。
すると以下のようになります。
Enterキーを押すと、以下のようになります。
2行目3列目の「6」が表示されました。
これで完成です。
MATCH関数(読み方:マッチかんすう)
この関数は、指定した範囲内で探したいセルが、どの列行にあるかを表示させる関数です。MATCH関数は、INDEX関数(読み方:インデックスかんすう)と組み合わせて使うことが多いので、ここでは、どの行と列に探すセルがあるかを求める(行番号と列番号を表示させる)もの、とだけ理解しておいてください。
簡単に説明します。
下の表が用意されているとします。
B製品は何行目か、九州は何列目かを求めたいと思います。先程の例と同じですので、結果はB製品は2行目、九州は3列目と表示されるはずです。
MATCH関数の公式は、以下の通りです。
まず、B製品の行数を求めてみますが、検索キーにB7(B製品)を、検索する項目データのところにB3:B5(A製品、B製品、C製品)を、最後に0を入力します。
すると以下のようになります。
Enterキーを押すと、以下のようになります。
"2"行目と表示され、正解ですのでOKです。
次に、九州の列数を求めてみますが、検索キーにB8(九州)を、検索する項目データのところにC2:E2(東北、関東、九州)を、最後に0を入力します。
すると以下のようになります。
Enterキーを押すと、以下のようになります。
3列目と表示され、正解ですのでOKです。
INDEX関数とMATCH関数の組み合わせ
ここでは、これまでのINDEX関数とMATCH関数とを組み合わせて、特定のデータを抽出してみたいと思います。
復習ですが、MATCH関数は行番号と列番号を求めます。INDEX関数は行番号と列番号をベースに特定のセルを表示します。勘のいい方はお分かりかと思いますが、そうです、INDEX関数の中にMATCH関数を入れ子(中に入れるということ)にすることができるということです。
イメージは以下のような感じです。
つまり、計算式の形は、以下のようになります(横一列に入らないので3段に分けていますが、入力するときは1列です)。この数式のカタチをしっかり理解して下さい。とても重要です。
本題に入ります。
if関数＋INDEX関数＋MATCH関数
物流会社では料金表の計算が欠かせないことは前にも触れましたが、大抵そのパターンは決まっていることが多いです。ここでは、料金表から必要なデータを抽出します。物流センターでは欠かせない料金表。そこから出荷データをもとに金額を抽出することが基本です。データ量が少なければ電卓という手段もあり得ますが、現実的には...と思います。ここでは、配送料を抽出する場面を想定してみます。
東京都に、重さ3kgの製品を運ぶ際の配送料を表示させて下さい。
下の表が用意されているとします。
ここでは、配送料欄に料金表の該当する配送料を、INDEX関数とMATCH関数を使って表示させたいと思ってください。
下の表の赤で囲った部分です。
まず、配送料欄の赤枠のセルに以下のように関数を入力ます。
ここで計算式の中身ですが、C4:E6のデータの中から東京都と合致する行番号と、重さ3kgと合致する列番号とが交差するセルの値(この場合:102)を表示しなさいという意味になります。
Enterキーを押すと、以下のようになります。
東京都に重さ3kgは102です。そう表示されていますからOKです。
これで、完成です。
つまり、INDEX関数の中にMATCH関数が入っている計算式です。
このINDEX＋MATCH関数は、2次元検索であり、一般的なある程度の知識がある人でも出来ない人もいると思います。この2次元検索が出来ると、少し自信がつくかと思います。
IF関数とINDEX関数とMATCH関数の組み合わせ
この章の最後として、IF関数と、これまでのINDEX関数とMATCH関数とを組み合わせて、特定のデータを抽出してみたいと思います。今までの知識の応用版として、これが使用頻度が高いと思っています。是非とも理解したいところです。この関数の組み合わせは、お決り的でして、よくあるパターンです。前のテーマとの違いは重量に幅がある価格表だということです。ゆえに、IF関数も使うということです。
東京都まで11kgの製品を運んだ際の料金を求めて下さい。
下の表が用意されているとします。
東京都まで11kgの製品を運んだときの配送料を求めたいと思います。
まず、0kgのときは0というIF関数を作ります。
(0の行を作らないとうまくいかないため、料金表には0の行を作っています。)
以下のようになります。
もし重さが0ならば、0と表示しなさい、そうでないならば･･･という意味になります。
次に、INDEX関数とMATCH関数の組み合わせです。
すると以下のようになります。
ここで、この計算式に疑問が出てくると思います。
1番目のMATCH関数のカッコ内の最後の1ですが、これは以前に書いたように「以下」という意味です。通常、料金表は1kg単位ではなく、このように10kg単位のように幅がありますので、今回の11kgのように、料金表上の数値と完全一致することは少ないと思います。そこで〇kgまでという際には"1"を使います。
そうなると、今回の11kgは20以下の数値ですので、10kgまでの行の数値を見に行ってしまいます。そこで、1行下の数値を見に行かせるために、match()のうしろに＋1と書き込みます。
すると、20kgまでの行を見に行ってくれて、120が表示されます。
ちなみに、2つめのMATCH関数の最後が0になっていますが、これは都道府県名が完全一致しないものはないからです。
Enterキーを押すと、以下のようになります。
これで完成です。
※
ちなみに、料金表が〇kg「以上」の場合は、＋1は不要です。料金表が、東京都で10kg"以上"は100となっていれば、11kgは20kg以下ですから、10kg以上の行を見に行き、これは10kg以上を指しているのでこのままでOK。よって＋1は、いらないということです。
以下のような感じになります。
つまり、IF関数の中に、INDEX関数とMATCH関数が入っている計算式です。
まず、INDEX関数とMATCH関数をきちんと理解することが大切です。そうすると、組み合わせてのデータ抽出が可能になります。それに加えてIF関数を組み合わせるとどうなるのかを考えると分かりやすいと思います。すべて論理的に考えるクセをつけたいところです。
-
ダイジョウブ
2007年のシングル曲。朝の連続テレビ小説の主題曲。ドラマの舞台が東北でもあるということは縁かもしれません。限りなく優しい言葉の数々と、洗練されたメロディーラインは、いつ聴いても、どこで聴いても美しい。大上段に構えず、平易な、それでいて心に響くこの楽曲を、私は忘れないことでしょう。
IF関数＋INDEX関数､MATCH関数､VLOOKUP関数の使い道
これまでのデータ抽出は、その検索範囲が限られた表中からでした。
しかし、実際の料金表は、重さが無制限に大きくなっても対応できるように「50kg以降は10kgごとに100円」のような条件を設定してある場合が多いです。そこで活躍するのが、これまでのIF関数＋INDEX関数＋MATCH関数に、更に「VLOOKUP関数」を加えたものです。
大げさに言えば、この形は料金表の検索に用いる関数のひとつの到達点といえるのではないでしょうか。
これまで様々な料金表を作成してきました。そして、よくこの関数を使用して陸送便、航空便、宅配便などの料金計算や試算を行っていました。私にとても大きな満足感を与えてくれる、そんな関数たちです。
if関数＋
INDEX関数＋MATCH関数＋VLOOKUP関数
先程は、IF関数とINDEX関数、MATCH関数の組み合わせで計算してきました。
今度は、VLOOKUP関数をさらに加えて考えます。
東京都に6kgの製品を運んだ際の料金を求めて下さい。
下の表が用意されているとします。
東京都に、6kgの製品を運んだ場合の配送料を求めたいと思います。赤枠のところです。この場合、先程までの関数では3kg以上の計算が困難です(料金表には3kgまでしかないから)。
また、3kg以上は1kg毎の追加料金が設定されています。どう考えたらいいでしょうか(重さに上限はないものとします)。
まず、追加料金のところですが、セルの中身が文章的(数字だけではないという意味です)ですから、10円の10、20円20、30円の30を外に書きます(数字だけを扱いたいため)。
赤く囲ったところです。
次は、できるところから始めます。3kgまでは料金表がそのまま使えますから、前回までのパターンです。
=IF(INDEX(MATCHの関数から始めます。
もし届け先データの重さが3kg以下ならば･･･の計算式になります。
ちなみに以下は「<＝」、以上は「>＝」、同一はもちろん「＝」です。
この計算式は、「もし届け先データの重さ(ここでは6)が3kg以下ならば、INDEX＋MATCHの内容である[3kgまでの料金表から、届け先データの都道府県(ここでは東京都)と重さ(ここでは6)とが交差するセル(つまり配送料)の数値を表示させる]を実行しなさい」という意味です。
ややこしいですが、ゆっくりと考えてみたいところです。
次に、3kg以下でないならば(つまり3kgより大きいとき)を考えて、この計算式のうしろに何か追加します。どうしましょうか?。やり方はいろいろあると思いますが、以前やりましたVLOOKUP関数を使ってみます。
こう考えてみます。「まずは3kgの配送料を求める、そしてここでは6kgの配送料を求めたいのですから、差分(6-3)の3kg分を求める、最後にこの2つを足す」。これならば、6kgの重量の配送料が求められます。
では、まずは3kgの配送料を求めます。
VLOOKUP関数を使って下記のように書きます。
3段目のVLOOKUPは、3kgの配送料を求めています。計算式の中の4は、赤枠のデータ範囲で左から4列目を意味しています。4段目のVLOOKUPは、6kg-3kgの差の3kg分の配送料を求めています。計算式の中の5は、赤枠のデータ範囲で左から5列目を意味しています(1kg毎の金額:太字のところ10、20、30)。
(D10-3)は6kg-3kgを意味しています。
この場合は、東京都3kgの102と、差分の3kg×1kg毎の10＝30を足した「132」が正解となります。
Enterキーを押すと、以下のようになります。
これで完成です。
このIF関数・INDEX関数・MATCH関数・VLOOKUP関数を使った計算は、なかなか難しく、もしかしたら上級レベルの領域かもしれません。
ただ、今回のようにExcelに料金表を作成し、そこで結果を求めるためには必要な技法です。たとえ料金表でなくてもこれを使って様々な場面で応用することができます。運賃計算専用のプログラムを組んでシステム的にすれば必要ないかもしれません。また、Accessを使えばもっと簡単に結果を抽出することが出来るかもしれません。そんな中でExcelで結果を抽出するメリットは、視覚的に確認することができるのと、修正や調整が簡単であることだと思っています。是非マスターしたい方法です。
※
論理だてて考るクセをつけたいものです。「自分が欲している答えを表示させるためにはどうすればいいのか」を考え抜くことが大切です。間違ったりスペルミスしたりと「ああでもない、こうでもない」と悩みながら辿り着くことに意味があると思っています。それが"チカラ"になると信じて。
このページでの関数の使い方が、私の最も伝えたいモノです。いろいろ自分なりに試してみましょう。
私のExcel師匠(と言っても同期ですが)からの言葉をひとつ。
「エクセルの"壁"にぶち当たり、あきらめてしまうのが中級。"何とかして結果が出るように工夫しよう"と試行錯誤をし始める気概を持っている人が上級」
何でも知っている人が上級者ではなく、壁を乗り越えようとする人が上級者であると、私は解釈しています。
-
J.BOY（Live Version）
1990年のシングル曲。エレキとドラムで始まるカッコいいイントロ。サビ前のアレンジは"サビへのジャンプ台"として秀逸。間奏のアレンジワークも洒脱で、サビフレーズの力強い"ワード"は、2語とは思えない輝きを放っています。そして何よりも魂の熱唱を感じてしまう。名曲。
悩み多き多感の時期に聞いた楽曲。もがき苦しみながらも、そこを突破していこうとする主人公に自分をダブらせていました。若さというのは、理屈抜きに突き進むことができる特別なものなのかもしれません。当時、サビフレーズが頭の中をループしていた記憶。
SUMIF関数とIF(SUM～関数の違い
COUNTIF関数とIF(COUNT～関数の違い
SUMIF関数とIF(SUM～関数 COUNTIF関数とIF(COUNT～関数は、一見形は似ているように見えますが意味合いは全く異なります。
それぞれ見ていきます。
SIMIF関数(読み方:サムイフかんすう)
この関数は、ある条件のものだけ合計するものです。
ちなみにSUM関数というものがありますが、これは単純に数値を合計するものです。
下の表が用意されているとします。赤く囲んだ所に合計を表示させます。
計算式と合計は以下のとおりです。
ここから本題です。
A製品だけの出荷数を、合計に表示させて下さい。
合計の右セルにA製品と入力します。ここで、SUMIF関数の計算式は以下のとおりです。
検索するデータ範囲は、製品名の上から3項目(A製品、B製品、A製品)、検索キーは、合計の右のA製品、集計するデータ範囲は、出荷数の上から3項目です。
以下のようになります。
そうすると、200と400だけを合計して表示してくれます。
これで完成です。
◆IF(SUM...との違い
ここで、似たような関数について考えてみたいと思います。このコラムのテーマである、IF関数との関係です。
IF(SUM～という計算式もよく使います。
SUMIF(～と何が違うのでしょうか。
SUMIF関数は、検索キー(この場合はA製品)と合致する場合のみ、その出荷数を合計しなさいという意味です。
それに対して、IF(SUM～は、
もし～の合計が～ならば、～しなさいという意味になります。
つまり、
IF(SUM(データ抽出範囲)＝0,X,Y)という計算式ならば、
「もし合計が0ならばXを、そうでないならばYを表示しなさい」
ということです。
関数の形は似ていますが、意味は全く違い、命令している内容が違います。
SUMIF(～は、「合計しなさい」という命令。
IF(SUM～,〇〇は、「合計が～ならば、〇〇しなさい」という命令。(〇〇が合計しなさいという場合も、もちろんあり得ます)
形が似ていても内容が全く違う意味を持つものは多いです。こうした際に論理的に物事を考えられるかがキーになってきます。ゆっくりと焦らずに考えれば自ずと答えに辿り着くはずです。
-
SINGLES
1994年のベストアルバム。シングル曲をメインにアルバム収録曲など全16曲を収録。メジャー調、マイナー調を曲想に合わせて自在に操り、洗練されたメロディーを生み出す名シンガー。しっとりとしたバラードから踊り出したくなる軽快なポップまで、その才能の奥行きの深さを感じずにはいられません。超高音でも透き通るハイトーンの伸びとビブラートは飛び抜けています。名盤。
- 君のいる星
1992年のシングル曲。軽やかなテンポのいい楽曲です。ポップバラードと言ったところでしょうか。声量があり気持ちのいいハイトーンが響き渡っています。優しい歌詞の情景が目に浮かぶ感じ。サビの下から上へのジャンプは、シンプルな歌詞と相まって高揚感が大。コーラスアレンジも洒脱です。
明日も会えるからと言っているのに、携帯を切らない。"おやすみ"と言って切っても、すぐに掛かってきて"もう寝た?"というターンを5回くらいはしていた。何度も途中で眠りに落ちた記憶。
COUNTIF関数(読み方:カウントイフかんすう)
この関数は、ある条件のデータ数を合計するものです。(先程のSUMIFは値の合計)
ちなみにCOUNT関数というものがありますが、これは単純にデータ数(件数といったほうが分かりやすいかも)を合計するものです。
下の表が用意されているとします。赤く囲んだ所に合計を表示させます。
計算式と"データ数"は以下のとおりです。
ここから本題です。
A製品だけのデータ数(件数)を、表示させて下さい。
データ数(件数)の右セルにA製品と入力します。ここで、COUNTIF関数の計算式は以下のとおりです。
検索するデータ範囲は、製品名の上から3項目(A製品、B製品、A製品)、検索キーは、データ数(件数)の右のA製品です。
以下のようになります。
この、3件のデータのうちA製品のデータは2件ですので、"2"と表示してくれます。
これで完成です。
◆IF(COUNT...との違い
ここで、似たような関数について考えてみたいと思います。このコラムのテーマである、IF関数との関係です。
IF(COUNT～という計算式もよく使います。
COUNTIF(～と何が違うのでしょうか。
COUNTIF関数は、検索キー(この場合はA製品)と合致する場合のみ、
そのデータ数を表示しなさいという意味です。
それに対して、IF(COUNT～は、
もし～のデータ数が～ならば、～しなさいという意味になります。
つまり、
IF(COUNT(データ抽出範囲)＝0,X,Y)という計算式ならば、
「もしデータ数が0ならばXを、そうでないならばYを表示しなさい」
ということです。
関数の形は似ていますが、意味は全く違い、命令している内容が違います。
COUNTIF(～は、「データの数を数えなさい」という命令。
IF(COUNT～、〇〇は、「データ数が～ならば、〇〇しなさい」という命令。(〇〇がデータ数を数えなさいという場合も、もちろんあり得ます)
【参考】
類似関数として、
COUNTA関数(読み方:カウンタかんすう)というものがあります。
これは、前述のCOUNT関数とほぼ同じですが、違うところとしては、データを数える際にエラー値のセルも数えるということです。
COUNT関数では、エラー値は数えてくれません。
下の表で見てみます。
B製品の出荷数がエラー値ですが、そのセルをカウントし、"3"件と表示されました。
いろんな関数がありますが、状況に応じて使えることが大切かと思います。
ここまで書いてきて申し上げたいことは、何か一冊でいいので、Excelの書籍を手元に置いておいたほうが便利だということです。ただ、こうしてネットで教えてくれるサイトが他にもたくさんあることには、感謝しかありません。
ですから、紙ベースの参考書的なものが一冊くらいはあったほうがいいなぁという実感。やはり読みやすいのでは?と思います。ですので、一冊だけ紹介することにしました。また、この本に限らず、すべてを読み込こなし理解する必要はなく、辞書代わりの感じで使っていくと、"チカラ"が付いてくるのかもしれません。
そして、結果を表示させる方法はひとつではないということです。ケースバイケースで「今回はあの機能の方を使おう」というふうになれけば最高です。つまり引出しをいっぱい持っていることに意味があります(あまり使わなくても)。そこにその人のセンスが反映されてきたりもします。いろいろtryしてみましょう。
-
湘南
1991年のオリジナルアルバム。シングル曲、アルバム収録曲など全11曲を収録。タイトル通り、"湘南"と"夏"を感じさせる内容の楽曲で構成されています。雰囲気満載。バラエティに富んだ楽曲の数々はバンドの魅力を存分に感じさせてくれています。手放せない一枚。
1991年の当アルバム収録曲。荘厳なイントロから始まるこの楽曲、しっとりとした中にも強さを感じるラブバラードで、サビから大きく放たれるストレートな気持ちが胸を打ちます。美しいメロデイーライン、純な気持ちを言葉に込めた歌詞、心地いい歌声、すべてが素敵。一貫してエレキのアレンジワークが冴え渡っている感じ。象徴的なラストのフレーズは心に余韻を残し、静かな中にも熱いものがこみ上げてくる感じ。バラードofバラードで名曲。
夜、友人からレストランに呼び出された。可愛らしい子がいた。後輩だという。この日は卒業式だったようでキレイに着飾り輝いていた。彼ははっきりとは言わなかった。6年後、二人の結婚式で緊張しながら祝辞を読んだ記憶。