トップ > スキル : アプリケーション > Excel(エクセル) > 基礎編(関数をマスターする)

Excel

関数をマスターする

エクセルには300を越す関数があります。すべてを知る必要はありませんが、ユーザがビジネス及び個人で利用できる関数は必須です。また、関数を組み合わせることによって、さまざまな利用効率の良い作業が実現できます。
このテキストでは、お馴染みの関数も再度確認という形でご紹介していますが、初めて知られる関数もあるかと思います。これを機会に、ご自分で利用できるものは是非マスターしてください。
また、このテキストでは数式パレットやオートSUMボタンなどは使用せず、手入力で行うことを前提にしています。

数値を計算、集計

合計、端数処理、給与計算などの数値の計算や集計に関する関数の利用方法をご紹介します。

指定した条件で合計を計算する

◆ 売上金額の合計を計算する(SUM関数)

=SUM(数値1,数値2…)

数値の合計を求めます。数値にセル範囲を指定すると、範囲内の数値を合計します。

セルに入力された数値を合計するにはSUM(サム)関数を使います。引数に、指定した値の合計を計算して表示します。連続しているセル内の数値を合計するのなら、マウスでドラッグして指定します。

合計したい数値が離れている場合は、Ctrlキーを使って合計したい数値のあるセルをクリックしていきます。
連続したセルの指定は「」(コロン)、離れたセルの指定は「, 」(カンマ)で区切ります。


◆ 項目別の金額を合計する(SUMIF関数)

=SUMIF(範囲,検索条件,合計範囲)

範囲内で指定した検索条件に合うデータを探し、見つかったセルに対応する位置にある合計範囲内のセルを合計します。

複数の項目があるなかで、特定の費用だけを合計したいときには、SUMIF(サムイフ)関数を使います。引数は「範囲、検索条件、合計範囲」の3つがあり、範囲内で検索条件に合うデータを探して、そのセルと同じ行にある合計範囲のセルの値だけを合計します。

例題の交通費だけであれば、「=SUMIF(B3:B17,F3,D3:D17)」と設定しても問題ありませんが、その数式を下の費用金額にもコピーするのであれば、範囲と合計範囲がズレないように絶対参照を設定しておく必要があります。一般的に、引数の中に「範囲」となっているセル範囲には絶対参照を施しておけば間違いないです。


◆ 指定した複数の条件に合致する金額を合計(DSUM関数)

=DSUM(Database,フィールド,Criteria)

表の中で検索条件に合うデータを探し、指定した項目(列)を合計します。引数のDatabaseには、調べる表全体のセル範囲を指定、フィールドには合計する項目名を「"」を挟んで指定します。検索条件は、シート内に別表を作成して指定する必要があり、この別表の範囲を引数Criteriaで指定します。

SUMIF関数は、1つの条件に合致するセルの値を合計しますが、複数の条件がある場合はDSUM(ディーサム)関数を使います。このDSUM関数のポイントは、別表の書き方です。別表は、検索対象の一覧表と同じ項目名を並べ、その下に検索条件を記述します。検索条件の配置によって、「かつ」「または」と違う意味になります。横に並べると「AかつB」、縦に並べると「AまたはB」という意味になります。斜めに配置すると、異なる項目の「または」条件になります。
例題であれば、「消耗品かつ澤田」となり、「澤田さんが計上した消耗品の合計金額」が計算されます。
なお、エクセル2007では、新しく加わったSUMIFS(サムイフス)関数が加わって、指定した複数の条件をすべて満たすセルの合計を計算できます。

また、指定した期間の売上金額を合計することも出来ます。このときのポイントは、検索期間の日付を指定するところです。計算用日付の換算表を別に作成し、開始のセルに「=">="&F3」、終了のセルに「=">="&G3」と入力します。表示される数値はシリアル値で、実際に日付計算に用いる数値です。DSUM関数のCriteriaにこの範囲を設定します。


◆ 指定した行間の金額を合計する(SUM、OFFSET関数)

=OFFSET(基準,行数,列数,高さ,幅)

基準とするセルから、指定した行数、列数だけ移動した位置にあるセルを参照します。セル範囲を参照するときは、何行分の高さ、何列分の幅を持つ範囲かを、それぞれ数値で指定します。省略は可能です。

例題の一覧表で、「2月から4月」など指定した範囲の合計をしたい時には、SUM関数とOFFSET(オフセット)関数を組み合わせます。OFFSET関数は、あるセルを基準として指定した行数、列数だけ移動したセルから、高さ、幅の数だけ広げたセル範囲を求めます。ここでは、B2セルを基準として、合計するセル範囲をOFFSET関数で求めています。

具体的に説明すると、合計範囲の先頭のセルが、もし「2月から」ならB4セル、「4月から」ならB6セルのように、基準のB2セルから開始月を指定するD2セルの値だけ下のセルになります。そこで、引数「行数」にD2セルの値を、「列数」は変わらないので、「0」を指定します。次に合計するセル範囲の「高さ」ですが、「2月から4月」ならB4~B6セルなので3行分、「3月から7月」なら5行分です。これは、D3セルからD2セルの値を引いたものに1を加えた数になります。「幅」は1列分なので、「1」を設定します。


◆ 1行おきに数値を合計(IF,MOD,ROW関数)

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

論理式に指定した条件が満たされるときは、「真の場合」に指定した内容を処理し、満たされないときは「偽の場合」の処理が行われます。

=MOD(数値,除数)

指定した除数で数値を割ったときの余りを求めます。

=ROW(範囲)

指定したセルの行番号を調べます。引数を省略すると、式に入力したセルの行番号を求めます。

例題の表は、午前と午後の講習会の申し込み数が1行おきに交互に記載されています。これを午前と午後で別々に人数を合計するには、午前は奇数行、午後は偶数行であることに注目します。つまり、奇数行番号にある申し込み数を合計すると午前の申込者数、偶数行番号であれば午後の申込者数になります。

そこでまず、セルの行番号を調べる必要があります。それにはROW(ロウ)関数を使い、次に行番号が奇数か偶数かを調べるのにMOD(モッド)関数を使います。引数に数値と除数を指定すると、数値を除数で割った余りを計算できるので、行番号を「2」で割った余りが「1」なら奇数、「0」なら偶数と判断できます。

あとは、IF(イフ)関数を用いて、行番号が奇数であれば、申し込み数の数値を表示し、奇数でなければ空白を表示するように設定します。

セルD12には、SUM関数でE列の数値を合計するように指定すれば、午前の申込者数が合計されます。もし、午後を計算するのであれば、「=IF(MOD(ROW(),2)=0,D3,"")」とします。


◆ 価格と売上個数から一気に売上金額を計算(SUMPRODUCT関数)

=SUMPRODUCT(配列1,配列2…)

同じ大きさの複数の範囲を指定し、対応する位置にある値をそれぞれ掛け算し、その結果を合計します。

例題のように、扱っている商品の販売価格と注文数が同じ配置の表にまとまっている場合は、この2つの表を使って、売上金額の合計を1つの式で求めることが出来ます。

売上金額の合計を計算するには、2つの表の同じ位置にある数値を掛け合わせて、その計算結果をすべて合計すればよいです。SUMPRODUCT(サムプロダクト)関数を使うと、1つの式で計算できます。


◆ オートフィルタで見えている部分だけを合計(SUBTOTAL関数)

=SUBTOTAL(集計方法,範囲)

指定した集計方法で、範囲を集計します。範囲のうち、表示されているデータだけを集計の対象とします。集計方法を「9」にすると、範囲を合計します。

シートに表示されているデータだけを計算するにはSUBTOTAL(サブトータル)関数を使います。引数に集計方法と葉煮を指定すると、指定した集計方法で範囲内を計算します。

集計方法 計算内容
1 平均を計算
2 数値のセルを数える
3 データのあるセルを数える
4 最大値を計算
5 最小値を計算
9 合計を計算

数値を指定した桁数で丸める

◆ 切り上げ/切り捨て/四捨五入を行う(ROUNDUP,ROUNDDOWN,ROUND関数)

=ROUNDUP(数値,桁数)

数値を指定した桁数で切り上げる。

=ROUNDDOWN(数値,桁数)

数値を指定した桁数で切り捨てる。

=ROUND(数値,桁数)

数値を指定した桁数で四捨五入する。

例題の表の「セール価格」は、15%引き価格の1円単位で切り上げたいので、セルD3には「=ROUNDUP(C3,-1)」と入力して、セルD5までコピーします。
また、「見積金額」のセルD8は、セール価格の合計(D6)を値引きとして1000円単位で切り捨てますので、ROUNDDOWN(ラウンドダウン)関数を使います。
また、セール価格を10円の位で四捨五入したいのであれば、セルD3には「=ROUND(C3,-2)」と入力します。

小数点以下を切り捨て、切り上げしたい場合は、引数の「桁数」は「0」とします。

◆ 指定の桁で五捨六入する(ROUNDDOWN関数)

五捨六入する関数はエクセルにはありません。そこで、十の位を五捨六入するには、あらかじめ数値に「40」を足した上で、十の位を切り捨てます。

例題で十の位を五捨六入する場合、「40」を足すことで、「60」以上の数値は合計が100以上となるので繰り上がり、60未満の数値は足しても100未満なので、桁が上がりません。同じように、百の位で五捨六入するなら、「400」を足してから百の位で切り捨てればよいです。

◆ 税込み金額から本体価格を逆算(ROUNDUP関数)

表示価格(税込み金額)を決めるときに、表示価格を先に決め、それから本体価格と消費税を逆算することがあります。消費税は本体価格の5%で、これを上乗せしたものが表示価格となります。つまり、本体価格に1.05を掛けたものになります。逆に、表示価格を1.05で割れば、本体価格が計算できます。表示価格から本体価格を引けば、消費税になります。ここで注意をしなければならないのは、消費税の1円未満の扱いです。1円未満を「切り捨て」するというルールで計算している場合は、逆算した本体価格の1円未満を切り上げます。逆に1円未満を「切り上げる」ルールの時は、逆算した本体価格の1円未満を切り捨てます。
消費税の1円未満を切り捨てる場合は、ROUNDUP(ラウンドアップ)関数で本体価格を求めます。

ただし、逆算した時、まれに計算結果が合わないことがあります。例えば、表示価格を「1049円」とすると、本体価格が「1000円」、消費税が「49円」になり、計算が合いません。これは、1.05倍して1049となる数字自体が存在しないからです。算出した表示価格が適切か、本体価格を1.05倍して確認しておく必要があります。

数値を別の単位に換算する

◆ 発注個数をケース単位に切り上げる(CEILING関数)

=CEILING(数値,基準値)

数値を基準値の倍数のうち最も近い値に切り上げる(0から遠いほうの倍数にする)

商品を“ケース単位”で発注する際、商品が不足しないように必要個数と同数または多くなるように、ケース数を定める必要があります。このような計算は、数値を基準値の倍数で切り上げるCEILING(シーリング)関数を用います。

例題を見ると、「単1乾電池」では、1ケースに6個入っている商品が45個必要です。45以上の6の倍数で45に最も近い数値を「換算個数」として計算し、この換算個数を1ケースあたりの個数で割れば、発注するケース数が求められます。


◆ 発注個数をケース単位で切り下げる(FLOOR関数)

=FLOOR(数値,基準値)

数値を基準値の倍数のうち最も近い値に切り下げる(0に近いほうの倍数にする)

商品を発注する際、ケース単位で発注しつつ、端数がでれば単品で注文したいことがあります。このような時は、数値を基準値の倍数にした上で切り下げた値を求めるFLOOR(フロア)関数を使います。

例題で見ると、「単1乾電池」では、発注したい個数がB3セル、1ケースあたりの個数はC3なので、D3セルに「=FLOOR(B3,C3)」という式を立てます。これで、換算個数が求められます。これを1ケースあたりの個数で割れば、発注ケース数を求められ、換算個数から発注個数を引けば、単品で発注する個数が計算できます。


◆ 余りに応じて発注ケース数を変える(MROUND関数)

=MROUND(数値,基準値)

数値の値に最も近い、基準値の倍数を計算します。数値が2つの倍数のちょうど中央だった場合は、0から遠いほう(正の数なら大きいほう)の倍数になる。

例題では、端数が1ケースあたりの個数の半数未満ならそれを除き、半数以上なら1ケース増やして発注するという場合を想定します。この場合、1ケースあたりの個数の倍数のうち、発注個数に最も近いものを換算個数とします。これを計算するのがMROUND(エムラウンド)関数です。


◆ 設定した上限額で金額を切り捨てる(MIN関数)

=MIN(数値1,数値2,…)

指定した数値やセル範囲に含まれる数値データの最小限を調べます。

例題は、交通費や宿泊費などを支給する際に、上限額を定めて、実費が上限額以上の場合は上限額を、上限額未満ならその実費をそのまま支払うというケースです。このケースでは、上限額を基準として上か下かではなく、実費と上限額のどちらか小さいほうが支給額となるという考え方をします。範囲の中で小さい数値を調べるのがMIN(ミン)関数です。また例題では、上限額のセルは共通のため、コピーした時に参照先がズレないように、上限値に絶対参照を施しています。


◆ 勤務時間と時給から給与計算(SUM関数と表示形式)

出勤と退勤の時刻の一覧表から給与を計算したい場合、時間の合計をするのは、SUM関数で良いです。しかし、単純に合計しただけでは、正しい給与計算はできません。エクセルで時間を合計する場合には、一工夫する必要があります。

例題で見ていきましょう。勤務時間は、「=退勤時刻-出勤時刻」で求めます。すべての勤務時間を合計したセルD9には「=SUM(D3:D8 )」と入力します。

次に、勤務時間を実際の時間数に計算する必要があります。1日、つまり24時間が「1」なので、時間を24倍すれば時間に換算できます。セルD11には「=D9*24」と入力します。
後は、給与となるセルF5に「=F3*D11」と入力します。

また、エクセルの初期設定では、時間は24時間表記になっています。例えば、25時間の結果は1時間として表記されます。働いた時間の合計が25時間であっても、1時間になってしまうわけです。そこで、セルD9の[表示形式]をユーザー定義の「[h]:mm」に設定します。この設定で、時間の合計が正しく表記されます。


◆ 土日の時給を割り増しして給与を計算(WEEKDAY、SUMIF関数)

=WEEKDAY(シリアル値,種類)

指定した日付データ(シリアル値)に応じた曜日を調べ、1~7(または0~6)の曜日番号で示します。引数「種類」に1~3の数値を指定することで、曜日番号の振り分けを変えられます。

休日や土日の出勤に対する時給は、割り増しとなることが多いです。このような時の給与計算は、出勤した日付の曜日を調べる必要があります。日付データの「曜日」を求めるにはWEEKDAY(ウィークデイ)関数を使います。この関数の結果は、曜日番号となります。引数の種類によって表のように表示結果が変わります。

引数(種類)
1(省略可) 2 3 4 5 6 7 1
2 1 2 3 4 5 6 7
3 0 1 2 3 4 5 6

平日と土日を分けるには「2」のパターンを使います。
平日勤務時間は、曜日番号が5以下の勤務時間を足し、土日の勤務時間は曜日番号が6以上の勤務時間を足せばよいです。条件を指定して合計しますので、SUMIF関数を使います。

セルH5には「=SUMIF(E3:E15,"<=5",D3:D15)
セルH6には「=SUMIF(E3:E15,">=6",D3:D15)」と入力します。
また、給与総額のセルH8には、「=H2*H5*24+H3*H6*24」と入力して、時間に換算します。