関数の最近のブログ記事


前回は元データを利用し、グラフ内に移動平均を表示しました。
今回は、移動平均を計算して表示させてみましょう。

移動平均の計算式は
 当月を含む過去一定期間の数値の平均です。

e0155.jpg
1.前回使用した表に3ヶ月移動平均
  5ヶ月移動平均のフィールドを
  作成しましょう。














e0156.jpg
 2.セルD6に計算式を入力しましょう。
   「=AVERAGE(C4:C6)」と入力します。
   数式をD15までコピーしましょう。
   ※今回数式はAVERAGE関数を用いましたが、
     「=SUM(C4:C6)/3」としても同じ結果を
     えることができます。










e0157.jpg

3.同様にセルE8に5ヶ月の移動平均を
  求め、セルE15まで数式を
  コピーしましょう。
 

 ※移動平均のみのグラフを作成する場合は、
   グラフの作成範囲を、B3:B15,D3:E15とし、
   折れ線グラフを選択すればOKです








分析ツールを使用して移動平均の値を求める

 上記方法は、AVERAGE関数を用いて移動平均を求めましたが、Excelのアドイン機能である
分析ツールを使用して移動平均を求めることができます。
 ※メニューバーのツールメニューの中に「分析ツール」が表示されていない場合は、
   メニュー→アドイン→分析ツールの前のチェックをつけて利用できるようにしましょう。
   Excel2007の場合は、
   画面左上のOfficeボタン→Excelのオプション→画面左側の「アドイン」をクリック→
   画面右側の下にあるExcelアドイン右側の「設定」ボタンをクリックし、
   「分析ツール」の前にあるチェックをつけましょう。

1.月度売上高が入力されているシートを開きましょう。
  売上高の右側に3ヶ月と5ヶ月の移動平均を表示させるフィールドを作成しておきましょう。

2.メニューバーのツール→分析ツールを選択し、「データ分析」ダイアログボックスを
  開きましょう。
  ※Excel2007では、データタブ→分析→データ分析でダイアログボックスが
    開いてきます。

  e0158.jpg
  上記データ分析のダイアログボックスの中から、「移動平均」を選択しOKボタンを
  押下します。

3.移動平均ダイアログボックスでは、3ヶ月の移動平均を表示する場合には下図のように設定をします。

  e0159.jpg
  ※入力範囲は、フィールド名称を含まず、売上高のセル全てを範囲選択します
  ※区間は、ここでは3ヶ月の移動平均を求めるので「3」を入力します
    (5ヶ月の場合は、「5」を入力します)
  ※出力先は、ここでは3ヶ月の移動平均を表示したいのでD4を選択します
    (5ヶ月の場合は、「E5」を選択します)
    出力先は値を出力する範囲の先頭のセルを必ず選択しましょう

4.3ヶ月の移動平均は、1月と2月は、求める式の範囲にデータがないので
  エラー値(#N/A)が表示されます。
  3月以降の値はAVERAGE関数で求められています。
  e0160.jpg



移動平均グラフを作成すると、データの傾向が平均化され傾向を読み取りやすくすることができます。
Excelのグラフ機能の中にある近似曲線を利用し移動平均のグラフを作成することができます。

e0148.jpg
1.左図の月別売上高のデータから、
  3カ月移動平均と5カ月移動平均のグラフを
  作成してみましょう。


















e0149.jpg
2.グラフを作成する範囲(B3:C15)を
  範囲選択しましょう。

3.グラフウィザードを起動しましょう。

4.グラフの種類:縦棒
  グラフの形式:集合縦棒
  上記2点を確認し、次へ進みましょう。

5.グラフタイトルは
  「月別売上高と移動平均」としましょう。
  グラフの作成場所は「新しいシート」と
  してみましょう。
  (同一シート内でも作成することが
   可能です)

6.次に近似曲線をグラフ内に追加して
  みましょう。
  データ系列(どの棒でも構いません)を
                              右クリックしてましょう。
  
e0150.jpg
  7.左図のメニューが表示されますので、
    近似曲線の追加をクリックしましょう。

e0150-1.jpg










上の図はExcel2007のメニュー        上の図はExcel2003までのメニュー


e0152.jpg
8.左図の近似曲線の書式設定が
  表示されます。
  (左図はExcel2007の画像)















e0152-1.jpg
8−1.左図の近似曲線の追加画面は
    Excel2003までの画像です。















9.「種類」タブ(Excel2007では近似曲線のオプション)であることを確認しましょう。
  移動平均アイコンをクリックし、区間を3に変更しましょう。
  変更が完了したらOKボタン(Excel2007では閉じるボタン)をクリックしましょう。
  グラフ内に移動平均のグラフが折れ線グラフで表示されたことを確認しましょう。
  
  確認が出来たら、同様に任意のデータ系列を右クリックし、移動平均を選択後
  区間を5に変更して5カ月の移動平均を表示しましょう。
  下図のように移動平均の折れ線グラフが表示されたことを確認しましょう。
  折れ線グラフの色の変更は通常の変更方法と同様です。

  e0154.jpg

次回は、元データを表示せず、移動平均のみのグラフを表示する方法をご紹介いたします。


開始年月日から終了年月日までの日数から、土曜日、日曜日および指定された祭日を除く日数を稼働日数として求めてみましょう。

=NETWORKDAYS(開始日,終了日,祭日)

開始日から終了日までの期間に含まれる稼働日数を返します。
稼働日とは、土曜日、日曜日、指定された祭日や休日を除く日のことです。

開始日:対象期間の初日を日付で指定
終了日:対象期間の最終日を日付で指定
祭  日:祭日や夏季休暇など稼働日数の計算から除外したい日を日付で指定

 ※NETWORKDAYS関数はアドイン機能により追加れる関数です。
  あらかじめアドインより、「分析ツール」をオンに設定して組み込んでおく必要があります。

完成図
e0142.jpg

1.稼働日数を算出するセルD4をクリックしましょう

2.「=NETWORKDAYS(B4,C4、E4:H4)」と数式を入力しましょう。
  稼働日数「18」と計算されました。

3.D4の数式をD15までコピーしましょう。

「1/1」と入力をすると、入力した年の日付が表示されますので、
翌年の稼働日数表を作成する場合は注意しましょう。

売上伝票から、売上伝票明細表を作成すると、伝票番号を指定し
求める明細データを検索することができます。

=VLOOKUP(検索値,データ範囲,列番号,検索の型)
 ※検索値、データ範囲のセル範囲は、数式を他のセルにコピーするために
   絶対参照にしましょう。
 ※データ範囲を選択する際、表の見出し(項目名)は含めません。
 ※列番号の数値の意味
   選択したテーブル範囲の左端列から、検索したい列を1、2、3と
   数えていきます。

 検索値:表の左端列で検索したい値を指定します
      左端列のデータは必ず昇順に並べることが必要です
 範  囲:検索対象となるデータの範囲を指定
 列番号:検索値のあるレコードで検索したいデータの列番号を
      左端列から1,2,3と数えた数で指定
 検索型:TRUEまたはFALSEを指定
      省略するとTRUEが指定されます
      TRUE−検索値が内場合は検索値未満で最大の値を選択
      FALSE−検索値と完全に一致する値だけが検索される
            (見つからない場合はエラー値「#N/A」が返される)


完成例
e0140.jpg

1.検索するための検索表を準備しましょう
  7行目の項目名称を2行目にコピーしましょう

2.関数を入力していきましょう
  セルC3をクリックし以下の数式を入力しましょう
  =VLOOKUP($B$3,$B$8:$I$277,2,FALSE)
  ※画像では、見えませんがデータは277行目まで入力されています。

3.数式をI列までコピーし、列番号を修正しましょう
  (セル表示が日付に変わってしまいますので、通貨スタイル等に変更しましょう)

伝票番号がない場合
e0141.jpg

伝票番号(検索値)がない場合は、上記のようにエラー表示されます。

伝票番号がない場合にエラー表示をせずに、「欠番」と表示する場合には、
IF関数とISERROR関数を組み合わせます。

=IF(ISERROR(VLOOKUP($B$3,$B$8:$I$277,2,FALSE)),"欠番",VLOOKUP($B$3,$B$8:$I$277,2,FALSE))

IS関数
 ISが付いているIS関数と呼ばれる関数がExcelには9つあります。
 IS関数はセルの内容をチェックし、TRUEまたはFALSEの戻り値を返します。
 IF関数と併用し活用できます。

ISBLANK(テストの対象)
 テストの対象が空白の時TRUEを返します

ISERR(テストの対象)
 テストの対象が#N/Aを除くエラー値の時TRUEを返します

ISERROR(テストの対象)
 テストの対象が任意のエラー値の時TRUEを返します

ISLOGOCAL(テストの対象)
 テストの対象が論理値の時TRUEを返します

ISNA(テストの対象)
 テストの対象がエラー値#N/Aの時TRUEを返します

ISNONTEXT(テストの対象)
 テストの対象が文字列でない項目の時TRUEを返します

ISNUMBER(テストの対象)
 テストの対象が数値の時TUREを返します

ISREF(テストの対象)
 テストの対象がセル範囲の時TRUEを返します

ISTEXT(テストの対象)
 テストの対象が文字列の時TRUEを返します

各関数は、上記解説以外の時はFALSEを返します


VLOOKUP関数の利用方法
 伝票番号あるいは製品コード等の項目を使うと、問い合わせに役立ちます。
 同じ番号が発行されないように番号発行管理上の注意も必要になります。
 

IF関数
 =IF(論理式,真の場合,偽の場合)
  条件により異なる内容を出力します。
  論理式が成り立つ場合は、真の場合の値を返し、成り立たない場合は、
  偽の場合の値を返します。

法人部門の売り上げが150以上であれば「優」150未満であれば「良」と
E列に表示されるようにしましょう。

e0138.jpg

1.セルE5に数式を入力していきましょう。
  =IF(B5>150,"優","良")
 
  e0139.jpg

  ※「真(TRUE)の場合」=論理式が成り立つ場合
    「偽(FALSE)の場合」=論理式が成り立たない場合

  ※文字列や記号を入力し、別のテキストボックスをクリックすると自動的に
    ダブルコーテーションで
    囲まれ「"優"」のように表示されます。

  ※式の意味
   セルE5に入力した数式「=IF(B5>150,"優","良")」は
   セルB5が150以上ならば優を表示し、150以上が成り立たない場合は
   良を表示するという意味です

  ※「""」と入力した場合は空(NULL)を表示、または空のセルという意味

見積計算書において、合計金額の小数点未満を四捨五入、切り捨て、切り上げた値を求めてみましょう。

小数点の特定の桁数以下を四捨五入したり、切り捨てしたり、切り上げをしたりしますが、今回はこれらを行う関数をご紹介します。

四捨五入の場合
 =ROUND(数値,桁数)
  数値を四捨五入し、指定された桁数にします

切り捨ての場合
 =ROUNDDOWN(数値,桁数)
  数値を指定された桁数にして、残りを切り捨てます

切り上げの場合
 =ROUNDUP(数値,桁数)
  数値を指定された桁数にして、残りを切り上げます

※数値=四捨五入、切り捨て、切り上げをしたい数値
  桁数=数値の結果の桁数を指定
      桁数に0を指定すると数値は最も近い整数として計算されます
      桁数に負の数を指定すると、数値は小数点の左側(整数部分)で、
      桁数に正の数を指定すると、数値は小数点の右側(小数点以下)で計算されます。

e0136.jpg

1.セルE9の合計を四捨五入した値をセルE9に算出してみましょう。
  セルE9に数式を入力します。
  =ROUND(E7,2)

2.セルE9の合計を切り捨てした値をセルE10に算出してみましょう。
  セルE10に数式を入力します。
  =ROUNDDOWN(E7,2)

3.セルE9の合計を切り上げした値をセルE11に算出してみましょう。
  セルE11に数式を入力します。
  =ROUNDUP(E7,2)


※整数にしたい場合に使用するINT関数
  =INT(数値)
  数値を超えない最大の整数を算出します。
  

SUBTOTAL関数を用い、引数を変えるだけで、
「件数」「平均値」「最大値」「最小値」「標準偏差」「分散」などの集計値を
算出することができます。

数式は
 =SUBTOTAL(集計方法,範囲)

集計方法の指定番号と算出される集計値
 指定 
 番号 
算出される集計値  同じ機能の関数 
1  平均値  AVERAGE
2  数値データの個数   COUNT
3  空白でないセルの個数   COUNTA
4  最大値  MAX
5  最小値  MIN
6  すべての引数の積   PRODUCT
7  引数を標本とみなした標準偏差   STDEV
8  引数を母集団とみなした標準偏差   STDEVP
9  合計  SUM
10  引数を標本とみなした分散  VAR
11  引数を母集団とみなした分散  VARP


下図のように、右の成績表をもとに、左表の集計値一覧表を作成してみましょう。

e0135.jpg

1.セルC5に数式を入れて国語の件数を求めてみましょう。
  「=SUBTOTAL(2,$H5:$J12)」

2.下方向にセルC5の数式をコピーしてみましょう。
  すべて、件数が表示されていますので、集計方法を修正してみましょう。
  平均=1 最大値=4 最小値=5 標準偏差=7 分散=10
  数式を修正したら、右へ全て数式コピーしましょう。
  上図の左側の「集計値一覧表」のように表示されましたね。


※調査データ等の場合、オートフィルタを設定すると、リストを抽出して非表示になった行は
  無視されるため、抽出リストだけの集計値を表示することができます。



下図のように、11月の費目と金額が日付順に入力されています。
(実際は1カ月すべての経費を入力しましょう)
経費台帳から、費目別集計表にそれぞれの値を作成してみましょう。

e0134-1.jpg


1.費目別集計を行うための表を作成しましょう。
  今回は、F5からF7には「合計」「件数」「平均」と入力をします。
  同じように、G3からJ3には「費目」と入力をします。
  (今回集計を行うのは、C列にフィールド名称が「費目」と記載されている
   各項目を集計するので、フィールド名を記載します)
  また、G4からJ4には、各費目名称を入力します。
  これで費目別集計を行うための準備ができました。

2.費目別「合計」を計算してみましょう。
  「合計」を算出する関数は「DSUM」を使用します。
  
  使い方は、

  =DSUM(データベース範囲,フィールド,検索条件範囲)
  
  データベース範囲:データベースの全範囲(フィールド名を含みます)
  
  フィールド:計算対象となるフィールド名が入力されているセル
        
  検索条件範囲:データベースの検索条件が設定されているセル範囲

  セルG5に数式をいれてみましょう。
   「=DSUM($B$4:$D$17,$D$4,G$3:G$4)
   ・データベース範囲は、必ず絶対参照で指定しましょう
   ・フィールドも絶対参照で指定しましょう
   ・検索条件範囲は、数式をコピーすることを考え、今回は行のみ絶対参照としましょう

3.費目別「件数」を計算してみましょう。
  「件数」を算出する関数は「DCOUNT」を使用します。
  使い方は「DSUM」と同様です。

4.費目別「平均」を計算してみましょう。
  「平均」を算出する関数は「DAVERAGE」を使用します。
  使い方は、「DSUM」と同様です。

5.消耗品費の「合計」「件数」「平均」が算出されました。
  J列の「荷造運賃」まで数式をコピーして完成です。




データベース関数は、コマンドによる集計方法と同じです。
メリットは、検索条件を指定し、シート上の任意のセルに特定項目の集計ができることです。

データベース関数の用途
売上伝票明細表から賞品別の件数、平均値、合計
アンケート調査の一覧表から男女別等属性別の各集計
成績一覧表から科目別成績の各集計




下図のように、アルバイトの勤務時刻を求める式を入力し、明細表に勤務時間と支給額が求められるようにしましょう。

e0133.jpg


1.勤務時間の計算式を入力しましょう
  ・セルH4に「=G4−F4」と数式を入れましょう
  ・セルH4の数式をオートフィル機能を使用してコピーしましょう
  ・勤務時間計のセルC5には「=SUM(H4:H11)」と数式を入れましょう

2.シリアル値を表示して、時間に換算してみましょう
  ・セルC5を選択し、
   Excel2003までの方は、
    メニューバーの「編集」→「クリア」→「書式」を選択します
   Excel2007の方は、
    「ホーム」→「編集」→「クリア」→「書式のクリア」を選択します
   Mac版の方は、
    「編集」→「消去」→「書式」を選択します
   これで、勤務時間計が時刻のシリアル値で表示されます
  ・時間換算のセルC6に「=C5*24」と数式を入れましょう
   勤務時間の合計が時間単位で表示されます

  ※セルに設定されている日付(時刻)の書式をクリアすると、 セルの表示形式は標準に変更されます
  ※シリアル値とは
    日付や時刻がセルに表示されるのは見かけの表示形式ですが、実態はシリアル値です。
    日付は1900年1月1日から1日ごとに1増える整数値で、時刻は24時間を1として午前0時ら1秒ごとに増える
    小数値でExcelは認識しています。この整数値や小数値のことをシリアル値と呼んでいます

3.支給額を算出してみましょう
  支給額のセルC7も「=C4*C6」と数式を入れましょう
  アルバイトの支給額が表示されます



ポイント
 ・日付/時刻関数の基礎となるシリアル値を用いて、勤務時刻から勤務時間と支給額を算出

覚えておこう!
 ・セルに「14:30」と入力をすると、時刻形式で表示されますね。
  見た目は時刻表示ですが、Excelが認識しているデータは「0.604166666666667」という
  値(シリアル値)です。
  時刻は午前0時0分0秒から翌日の午前0時0分0秒までの24時間を1として、すべて小数値で
  把握されます。そのため、加算、減算等の計算ができます。
  60分を超えると時間表示されます。時間表示が24時間を超えると、その部分は日単位に
  表示されませんが記憶されています。
  そのため、シリアル値に換算して時間換算すると正しい時間が計算されます。
  時刻は「13:90」のように入力したり、午前0時を超える場合、
  例えば
  午前2時30分なら「26:30」と入力しても、勤務時間の計算には正しく反映されます。

  日付の場合、Windows版Excelの日付システムは
  1900年1月1日から9999年12月31日までの連続する日付に
  1から2958465までの整数値(シリアル値)を割り当てています。
  Macintosh版Excelでは、1904年1月2日を1とするシステムを使います。

ユーザー定義の日付や時刻の種類

種類 表示形式と記述例 表示 備考
和暦 ge.m.d H22.11.15 gが元号、eが年を表示
ggge"年" 平成09年 1桁の年も2桁で表示
ggge"年"m"月"d"日" 平成22年11月26日 mが月、dが日を表示
西暦 yyyy/m/d 2010/11/15 yyyyが西暦年を表示
yyyy"年"m"月"d"日"    
yy 10 年を下2桁で表示
mm 08 1桁の月も2桁で表示
mmm Nov  
時刻 h:mm AM/PM 12:00AM hが時、mmが分を表示
h:mm:ss 12:00:00 ssが秒を表示
h"時"mm"分"ss"秒" 0時00分00秒  



2011年10月

            1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30 31