SUBTOTAL関数を使用し計算できる種類の一覧

Excel2013のSUBTOTAL関数を使用し、計算できる種類の一覧です。

非表示の 値を含む	非表示の 値を無視	同様の関数	計算内容
1	101	AVERAGE	平均
2	102	COUNT	数値の個数
3	103	COUNTA	データの個数
4	104	MAX	最大値
5	105	MIN	最小値
6	106	PRODUCT	積
7	107	STDEV	標本標準偏差
8	108	STDEVP	母標準偏差
9	109	SUM	合計
10	110	VAR	標本分散
11	111	VARP	母分散

「非表示の値を含む」を使うと、オートフィルターの抽出結果を反映できます。
「非表示の値を無視」を使うと、オートフィルターの抽出結果を反映しません。結果、通常のワークシート関数を使うのと同じになります。

SUBTOTAL関数の入力方法

Excel2013のSUBTOTAL関数の入力方法です。

関数の入力セルに、=SUBTOTAL(　と入力すると、計算方法のリストが表示されるので選択します。

 ,（コンマ）を入力し、計算範囲を指定します。

 ）を入力し完了です。

SUBTOTAL関数とは

Excel2013のSUBTOTAL関数とは、オートフィルタ結果を反映し計算する関数です。SUBTOTAL関数では平均や合計など11種類の計算ができます。
ここではSUBTOTAL関数とAVERAGE関数を使い、オートフィルタ結果でどのようになるか違いを見ます。

全国の都道府県の人口一覧表です。
セルD2には、 =SUBTOTAL(1,D9:D55)　と入力されていてSUBTOTAL関数で人口の平均を算出しています。

セルD4には、 =AVERAGE(D9:D55) と入力されていてAVERAGE関数で人口の平均を算出しています

1. ［都道府県名］と［人口］の項目名を選択します。
2. リボンの［データ］－並べ替えとフィルターグループの［フィルター］をクリックします。

人口セルの▼をクリックし、表示されるメニューの［数値フィルター］－［指定の値以下］をクリックします。

オートフィルターオプションダイアログボックスが開きます。ここでは人口が５千万人以下の都道府県の平均を計算したいので、人口に「5000000」と入力し［OK］ボタンをクリックします。

オートフィルターが実行され、人口が５千万人以下の都道府県が抽出されました。
セルD2のSUBTOTAL関数は、 オートフィルターの抽出結果で計算結果が変わりました。
セルD4のAVERAGE関数は、 オートフィルター実行前と変わりなく同じです。
このようにSUBTOTAL関数を使うと、オートフィルターの抽出結果で計算値を反映させることができます。

COUNTIF関数で指定したデータに一致した個数を数える

Excel2013のCOUNTIF関数で、指定したデータに一致した個数を数えます。

下の表から「休み」の個数をカウントします。

セルC2には、=COUNTIF(C5:E16,"休み")　と入力されています。

• C5:E16はカウントするセル範囲です。
• "休み"は検索するデータです。

結果カウントされた結果が「5」と表示されました。

COUNTA関数で空白を含まないデータ個数を数える

Excel2013のCOUNTA関数で、空白を含まないデータ個数を数えます。

セルB2には、=COUNTA(B4:B13) と入力されています。
 B4:B13はカウントする範囲です。
下のシートは空白セルがない場合です。

 空白セルを含めた場合です。空白セルがある場合でも、正常にデータ個数がカウントできました。

HYPERLINK関数でハイパーリンクを作成する

Word2013のHYPERLINK関数でハイパーリンクを作成します。ハイパーリンクとは、クリックするとインターネット等のネットワークのサイトを開くことができる機能です。
お気に入りや仕事でよく使うサイト一覧などで使うと便利です。

セルB2には、=HYPERLINK("http://excel2013-function.blogspot.jp/")　と入力されています。

• http://excel2013-function.blogspot.jp/　はリンク先のURLです。

=HYPERLINK("http://excel2013-function.blogspot.jp/","Excel2013関数")　のように別名を設定することができます。URLでは分かりにくい場合、別名を設定します。

RANK関数で点数に順位を付ける

Excel2013のRANK関数で点数に順位を付けます。

点数の一覧表です。D列に順位を付けます。

セルD3には、=RANK(C12,$C$3:$C$12) とRANK関数が入力されています。

• C12 ： 点数が入力されているセルです。
• $C$3:$C$12 ： 点数が入力されているセル範囲です。セル範囲は変わらず固定なので、$を付けて絶対番地にしています。

下のセルにコピーすれば、全体に順位を付けることができます。

NOW関数で今の日時を表示する

Excel2013のNOW関数で今の日時を表示します。

セルB2には、=NOW() と入力されていて、今の日付と時刻が表示されます。

=NOW()+10　と入力すると10日後の日付を表示させることができます。

=NOW()+"1:10"　と入力すると1時間10分後の時刻を表示させることができます。

TODAY関数で今日の日付を表示する

Excel2013のTODAY関数で今日の日付を表示します。

セルB2には、=TODAY() と入力されています。これでパソコンに設定されている今日の日付が表示されます。

=TODAY()-10　のように計算式を入力でき、これで10日前の日付が表示されます。

REPT関数で同じ文字列を指定回数表示する

Excel2013のREPT関数で、同じ文字列を指定回数表示します。

セルC2には、=REPT(B2,5)　と入力されています。

• B2は繰返す文字列を指定しています。
• 5は繰返す回数です。

《実行結果》

• 2行目　：　半角数字　123　を5回繰返しています。
• 3行目　：　B3には =B2+1 と計算式を5回繰返しています。結果文字列として処理されています。
• 4行目　：　全角文字列を5回繰返しています。

Weekday関数を使わずに、TEXT関数で日付から曜日を表示させる

Excel2013で、Weekday関数を使わずに、TEXT関数で日付から曜日を表示させる方法です。

C3セルには、=TEXT(B3,"aaaa")　と入力されています。これで曜日が表示できました。
Weekday関数では右寄せでしたが、 TEXT関数では左寄せで表示されています。

• B3は曜日の元になる日付です。
• "aaaa"は曜日を表す書式です。　"aaaa"で土曜日、"aaa"で土と表示されます。

=TEXT(B3,"aaa")　に変更した結果です。「土曜日」から「土」になりました。

下の列にコピーし、全ての日付から曜日が表示されました。

DSUM関数で指定した項目で集計する

Excel2013のDSUM関数で、指定した項目で集計する方法です。

 支店別売上表です。
売上の合計を、指定した支店別に集計します。
D15には、=DSUM(C2:D11,"売上",C14:C15)　と入力されています。

• C2:D11は［支店名］と［売上］の範囲です。
• "売上"は集計する項目名です。これは=DSUM(C2:D11,D2,C14:C15)のように項目名のセルを指定することもできます。　
• C14:C15は集計する項目名です。ここでは［支店名］になります。

これで合計の支店名に入力した、A支店の売上が集計できます。
D15には、=DSUM(C2:D11,"売上",C14:C15)　と入力
 集計項目をB支店に変更した結果です。B支店の売上が集計できています。
集計項目をB支店に変更した結果

日付から曜日を表示させるWeekday関数

Excel2013で、日付から曜日を表示させるWeekday関数を使います。

B列には日付が入力されていて、その日付から曜日を表示させます。
C3には、 =WEEKDAY(B3)　と入力されています。

• B3は元になる日付です。

結果曜日を表す番号が表示されます。番号はデフォルトで下記になります。

• 1 ： 日曜日
• 2 ： 月曜日
• 3 ： 火曜日
• 4 ： 水曜日
• 5 ： 木曜日
• 6 ： 金曜日
• 7 ： 土曜日

この番号を書式設定を変更し、曜日名で表示させます。

リボンの［ホーム］－フォントグループの右下ボタンをクリックします。

セルの書式設定が開きます。

1. 表示形式タブにします。
2. 分類から［ユーザー定義］を選択します。
3. 種類に,aaa と入力します。これで上のサンプルのように、曜日の頭文字が表示されます。

種類に,aaaa （半角aを４文字）と入力すると、上のサンプルのように、「曜日」を付けて表示されます。

書式を設定した結果です。曜日が表示されました。

別のセルにコピーし全ての日付から、曜日を表示させることができました。

IF関数で合否を判定する

Excel2013のIF関数で合否を判定する方法です。

下の得点判定表の得点に入力すると、80点以上なら○、そうでないなら×と判定します。
セルC5には、 =IF(B5>=80,"○","×")　と入力されています。

• B5>=80　はB5の値が80以上ならばという意味です。
• "○"　は80以上なら○を表示します。
• "×"　は80以上でなければ×を表示します。

これを下のセルにコピーします。得点が入力されていない状態では、80点以上でないので×と表示されます。

得点に80を入力すると、IF関数の判定結果は○と表示されます。

得点に79を入力すると、IF関数の判定結果は×と表示されます。

全ての得点を入力した結果です。

VLOOKUPの使い方

Excel2013の関数のVLOOKUPを使い、コードを入力すると名前や単価などのデータが自動的に入力できるようにします。これを表引きといいます。

表引きの元になるSheet2の部品一覧表です。

Sheet1の部品在庫チェック表です。これの「部品コード」を入力すると、［部品名］と［単価］が自動的に入力されます。
部品名のセルC5には、=VLOOKUP(B5,Sheet2!$B$3:$D$12,2)　が入力されています。

• B5　は検索する部品コードを入力するセル位置です。
• Sheet2!$B$3:$D$12　はSheet2に作成した表引きの元になるセル範囲です。
• 2　部品名は表引きの元になる表の2列目という意味です。

これを部品名の下のセルにコピーします。
#N/A は部品コードが入力されていないので表示されているエラー表示です。

単価のセルD5には、=VLOOKUP(B5,Sheet2!$B$3:$D$12,3)　が入力されています。

• B5　は検索する部品コードを入力するセル位置です。
• Sheet2!$B$3:$D$12　はSheet2に作成した表引きの元になるセル範囲です。
• 3　単価は表引きの元になる表の3列目という意味です。

これを単価の下のセルにコピーします。 

部品コードを入力します。

Enterキーで確定すると、［部品名］と［単価］が表示されます。