１１５:仕事でよく使うエクセルの式を紹介

（）括弧のなかに入れるものを、引数（ひきすう）といいます。引数を複数必要となる場合には、”,”（カンマ）で区切り、（引数１,引数２,引数３,・・・）となります。今のエクセルでは、”＝”（イコール）を入れて式を入れるのが標準となっておりますが、”+”（半角たす）の方が、テンキーがある場合、”+”は一つのキーで賄えるので、”＋”を入力するのを覚えたほうが仕事が早くなると思います。”＝”はシフトを押さないと入力できないので、面倒です。

合計

合計　：　+sum（範囲）

sumは、引数の順番とかなく、”,”（カンマ）で区切りれば、いつくでも選択できます。

普通は、”：”（コロン）で結んで、＋sum(C10：C15)とか記載するのが一般的です。

ただ、セルが隣接しない場合や、行や列が複数にまたがる場合には、”,”（カンマ）を使って+SUM(C3,D4,E5:E8)のように使うこともできます。

個数

個数　：+count(範囲)

セルの中に数字がいくつあるかを数えるのが、+count()の式です。

また、数字も文字もなにか入っていれば数えるのが、+counta()です。

小数点を切り捨て

3.14を整数の３にする。：+int(セル番地）

応用として、10円単位に丸める方法は、そのものを10で割って、10を乗じる方法で計算できます。

1268 → 1260　: +int(セル番地/10)*10　

また、応用することで、整数を除いた小数点だけにすることもできます。

3.14 → 0.14 : +(セル番地)-int(セル番地)

個数（条件にあうものだけ）

条件に合う個数　：+countif(範囲,条件)

この式は、引数の順番が決まってます。sumやcountとは”,”（カンマ）の意味が違います。

参照式　VLOOKUP関数

他のテーブルから参照:+vlookup( キーとなる値,検索テーブルの範囲.テーブルの列の相対位置,0か1)

サンプルのエクセルファイルを以下に入れておきました。

excel_DBsample0001-1 ダウンロード)で

名称の個所はすべて+VLOOKUP()の式を立てておきましたので、その式を下にコピーして表を完成させてください。

説明

キーとなる値は、一つのセルを指定してください。　テーブルの範囲は、先に指定したキーとなる値が入っている列を一番左側になるように列を選択して、表示されたい値がはいっている列まで、範囲を広く選択してください。テーブルの横位置とは、選択されている列の最も左側の列を1.右に行くにつれて、2.3.4と数えて表示したい列の順番を入れてください。最後の0か1を入れる部分ですが、これは、何も考えずに0を入れるとしてください。1を入れると、近いものを持ってきてしまいます。無い時は、エラーを出してもらう方が良いので、なんとなくの数字は、普通はいりません。また、最後のこの引数は、省略できますが、省略すると、1とみなすようなので、いつも0を入れると覚えておいた方が良いです。

説明を加えます。まず、エクセルシートのマスターのシートを選んでください。左下にあります。

マスターのシートには、左側に数字、その右側に名称というテーブル（番号と関連されている内容が一覧表になっているもの）が４つあります。このテーブル毎が範囲に相当します。VLOOKUP(指定セル,範囲,左から番号,0)の範囲の部分です。小分類コードを例にとると、範囲は、”A4:B12”となります。

上記のエクセルはD4のセルの内容を、式の表示をしておりますが、

=+VLOOKUP(C4,マスター!$A$4:$B$12,2,0)

キーとなる値　：　引数１

上記（）の中を説明をします。()の中の初めの引数が、C４とあるのが、キーになっている数字（この場合には文字）です。２つ上の小分類コードの左側にある数字と、上のシートにあるC4の数字に同じものがあるのがわかると思います。C4は、”０３０”です。２つ上のマスターシートの小分類テーブルにある”０３０”の隣に”動物”と入力してあるのがわかります。

範囲　：　引数２

なにやら、マスター!$A$4:$B$12　とすごく難しい内容が入っていますが、これは、マスターシートのA4からB12を絶対値しているだけです。この内容を直接手で入力する人はいないので、難しいようで超簡単です。まず、+VLOOKUP(C4,まで入ったら、マスターシートのタブを選択してマスターシートを表示して、A4:B12をマウスで選択してF4（ファンクション４）を押して絶対値にするだけです。絶対値にするのは、下にコピーしたときに相対値していだと、範囲も下に下がってしまうので、指定したいテーブルから外れてしまうことを防止するためです。

列番号　：　引数３

次の２は、左から数えて２列目を意味します。これは、テーブル内の左から数えてということで、今回のサンプルではすべて、2列からなっているテーブルなので、”２”が範囲の次の引数となってます。

最後の０　:　引数４

最後の０ですが、忘れずに必ず０を入れるようにしておいてください。+VLOOKUP()の式は、引数が３つでも成立しますが、４つめに必ず０を入れてください。

おまけ

それとわたしは、”+VLOOKUP(”　を　”る”　に単語登録しております。エクセルをよく使う人は、”る”で、一発で+VLOOKUP(がでるので、”る”で+VLOOKUP(を出したのち、漢字キーを外して、セルを選択していくことをお勧めいたします。

平均

平均:+average(範囲)

範囲の引数は、sumと同じ要領です。

ＩＦ関数

IF関数:+if(条件式,条件に合う時に表示するもの,条件が合わない時に表示するもの )

この関数も結構つかいます。他の人がピボットテーブルを作ったシートとか、標準のままで、項目が一番上にしかなくて、同じは、空白とかいうテーブルを扱うことがあります。

上のシートで、左側のテーブルから右のテーブルにしてデータベース形式としたい場合に、重宝する式です。

まず、集計行はいらないので、集計行がわかるようにしたいと思います。　D4のセルに=+IF(+RIGHT(A4,1)=”計”,”集計行”,””)　と入れました。これは、計が最後についていると、集計行と表示しないさいという意味です。

また、製造区分は空白を埋めて、データベース形式とします。E4のセルに

=+IF(A4=””,E3,A4)　と入れました。なにもなければ、今ある上のセルを持ってきて、なにか入っていればA4のセルを表示しなさい。という式です。

さらにサービスで、製造区分が同じであれば、累計していき、製造区分が変わったところで、累計がリセットされる列も作りました。F4には、

=+IF(E3=E4,F3+C4,C4)　という式を入れて。すぐ上の行と同じであれば、足していくというものです。

どれも、相対値指定にしてあるので、最下部までコピーすることで、全部の計算ができます。

上記のように、どれも、if()関数で、可能です。

説明

条件式は、A3=5 とか入れます。これは、A3のセルに入っている数字が　数字の5ですか？と、聞いています。もしも、5であれば、[条件に合う時に表示するもの]を表示します。ここには、文字で有れば、””(ダブルコーテイション )で囲んだものを入れれば良いし、さらに式とか数字とか入れる事ができます。例えば、”正解”とか、5とかです。次の引数の[条件が合わない時に表示するもの]は、A3のセルに入っている数字が　数字の5では無い時に表示する内容です。

左からの文字列

文字列を左側から抜き取る：+left( 指定セル, 文字数）

右からの文字列

文字列を右側から抜き取る：+right( 指定セル, 文字数）

文字列の中の文字を抜き取る

文字列の真ん中の文字を抜き取る：+mid( 指定セル, 左から何番目, そこから右に何文字）

条件が合う場合だけ合計

指定した文字がテーブルの別の行にある場合、その条件があう数字の合計：+sumif(条件範囲, 条件のセル, 合計される数字が入った範囲）

例えば、先ほど使った、売上データのシートで、K55のセルに、+SUMIF(H4:H53,H53,K4:K53)と入れてみてください。H4:H5　は、条件の範囲で、この中からという意味です。次の　H53 は、具体的に ”国産品” とかでも良いですが、だいたい条件の中に入っているものなので、ここでは、H53の”国産品”を条件としております。最後の K4:K53 は、売上金額が入った範囲です。つまり、「製造区分名の中で、”国産品”であるものを探して、その売上金額を合計してください。」という意味になります。あっているかどうかは、フィールド名でフィルターをかけて、国産品だけを出して、売上を合計してみて合っているか確認してください。

四捨五入

四捨五入：+round(セル, 桁数）

桁数は、小数点以下何桁という引数になります。この桁数を０にすれば、小数点第１位を四捨五入して整数で返します。この桁数を２にすれば、小数点３桁を四捨五入して、小数点2桁で返します。例えば、123.456　という数字を、+round(123.456,2) とすれば、123.46　を返すということになります。