1. Home » 
2. Excel講座の総目次 » 
3. Excel(エクセル)講座 Q&A(Tips) » 
4. 計算関連

Excel(エクセル)講座 Q&A(Tips)：計算関連

成人の日、体育の日、海の日、敬老の日の求め方（ハッピーマンデイ　第2、第3月曜日の求め方）　　Topへ

第2月曜日を計算で求める

• =14-MOD(WEEKDAY(DATE(年,月,1))+4,7)
  • 年には求める年を西暦で入れます。（2003とか2004のように）
  • 月に1か10を入れます。
  • (例)2003年1月の成人の日は
    =14-MOD(WEEKDAY(DATE(2003,1,1))+4,7)
    となります。
• 上の数式の意味を考えるのは面倒と思ったら、日付を決め打ちする方法もあります。
  • A1セルに求めた月の1日がシリアル値で入力されているとします。
    =DATE(YEAR(A1),MONTH(A1),CHOOSE(WEEKDAY(A1),9,8,14,13,12,11,10))

第3月曜日を計算で求める

• 2003年から海の日(7月)と敬老の日(9月)は第3月曜日となりました。
  これらも同様に求められます。
• =21-MOD(WEEKDAY(DATE(年,月,1))+4,7)
  • (例)2003年7月の海の日は
    =21-MOD(WEEKDAY(DATE(2003,7,1))+4,7)
    となります。
• 近年はややこしいので、内閣府の　「国民の祝日」について　http://www8.cao.go.jp/chosei/shukujitsu/gaiyou.html をご覧ください。
  
• 国民の祝日（2018年)
  

  元日	1月1日	年のはじめを祝う。
  成人の日	1月の第2月曜日	おとなになったことを自覚し、みずから生き抜こうとする青年を祝いはげます。
  建国記念の日	政令で定める日	建国をしのび、国を愛する心を養う。
  春分の日	春分日	自然をたたえ、生物をいつくしむ。
  昭和の日	4月29日	激動の日々を経て、復興を遂げた昭和の時代を顧み、国の将来に思いをいたす。
  憲法記念日	5月3日	日本国憲法の施行を記念し、国の成長を期する。
  みどりの日	5月4日	自然に親しむとともにその恩恵に感謝し、豊かな心をはぐくむ。
  こどもの日	5月5日	こどもの人格を重んじ、こどもの幸福をはかるとともに、母に感謝する。
  海の日	7月の第3月曜日	海の恩恵に感謝するとともに、海洋国日本の繁栄を願う。
  山の日	8月11日	山に親しむ機会を得て、山の恩恵に感謝する。
  敬老の日	9月の第3月曜日	多年にわたり社会につくしてきた老人を敬愛し、長寿を祝う。
  秋分の日	秋分日	祖先をうやまい、なくなった人々をしのぶ。
  体育の日	10月の第2月曜日	スポーツにしたしみ、健康な心身をつちかう。
  文化の日	11月3日	自由と平和を愛し、文化をすすめる。
  勤労感謝の日	11月23日	勤労をたっとび、生産を祝い、国民たがいに感謝しあう。
  天皇誕生日	12月23日	天皇の誕生日を祝う。

  • 国民の祝日に関する法律第2条に規定する建国記念の日は、2月11日とする。
    平成32年（2020年）以降、「体育の日」は「スポーツの日」になります。
    平成32年（2020年）に限り、「海の日」は7月23日に、「体育の日（スポーツの日）は7月24日に、「山の日」は8月10日になります。

　　　　　　(参考)　　祝日一覧表（2004年)

• 春分の日と秋分の日は政府が決定して、前年の2月に官報で発表されます。
  • 計算で求めるには
    春分の日= Int(20.8431 + 0.242194 * (年 - 1980) - Int((年 - 1980) / 4))
    秋分の日= Int(23.2488 + 0.242194 * (年 - 1980) - Int((年 - 1980) / 4))
    として、2099年までは求められるようですが、正確には前年の2月まで正解かどうかは判らないことになります。
    • 秋分の日の式に間違いがありましたので修正しました。(2012/1/11)
      uenoさん、ご指摘ありがとうございました。

生年月日から干支を求めたい　　　　Topへ

• 関数を使った例です
• B2セルに生年月日を入れます。
  干支は12年周期ですから年月日の入力されたセル（B2セル)の年を12で割り余りを求めます。
  文字を取り出すために余り「0」では都合が悪いので「1」を加算します。
  文字列『干支』の並びは計算結果の「1」に対応する「申」から並べておきます。
  

  	B	C
  2	2003/1/1	=MID("申酉戌亥子丑寅卯辰巳午未",MOD(YEAR(B2),12)+1,1)

月の最終日(月末)を求める　　　Topへ

• DATE関数やEOMONTH関数を使います。
  (*)EOMONTH関数は、ツール →　アドイン　で 分析ツールにチェックを入れます。（Excel2003以前）
  (参考) 日付の関数について

月末を求める

• 下の例は当月の月末を求める例です。
• 何ヶ月後の場合は赤字の数値を変更します。来月であれば、「+2」や「1」となります。
  

  	B	C	D
  2	2004/1/1	=DATE(YEAR(B2),MONTH(B2)+1,0)	2004/1/31
  3		=DATE(YEAR(B2),MONTH(A1)+1,1)-1	2004/1/31
  4		=EOMONTH(B2,0)	2004/1/31

土日、祝日を除いた月末を求める

• 土日、祝日を除いた月末を求めるにはWORKDAY関数を使い、祝日の表を作成します。
  この例ではD3：D4に作成しています。
• =WORKDAY(EOMONTH(A1,0)+1,-1,祝日)　　
• =WORKDAY(EOMONTH(A1,0)+1,-1,D2:D3)　
• のように、翌月の1日を基準に前の稼働日を求めればよいです。
  

  	B	C	D
  2	2005/4/1	=WORKDAY(EOMONTH(B2,0)+1,-1,D3:D4)	祝日
  3		=WORKDAY(DATE(YEAR(B2),MONTH(B2)+1,1),-1,D3:D4)	2005/4/29
  4			2005/5/3

偏差値の計算式　　　　Topへ

• 偏差値：　=(得点-平均点)/標準偏差*10+50
  平均点：　=AVERAGE(範囲)
  標準偏差 ：　=STDEV(範囲)
  まとめると、
• 偏差値：　=(得点-AVERAGE(範囲))/STDEV(範囲)*10+50
  と、なります。

串刺し集計の方法　　　　Topへ

• 何枚かの同形式のシートを集計する方法です。
• 参照されるシートはシート見出しに連続して並んでいる必要があります。
• 参考資料（Excel2010、Excel2013,Excel2016の操作手順はこちらをご覧ください）
  • 複数のシートを串刺し集計する（3D集計)
  • 複数のシートのデータを集計する：3-D集計を使う方法を説明しています。
  • フィル機能の作業グループへコピーするを使って複数シートへコピーする

(例)　ここからはExcel2003以前での操作方法です。
Sheet1〜Sheet4に第1四半期〜第4四半期のデータがあります。
　　　　 Sheet5にこれらの合計を計算したいと思います。

1. Sheet5のB4セルをクリックしてアクティブにします。
2. オートSUM（Σ）ボタンをクリックします。
   セルには『=SUM()』と入力されます。
   
3. シート見出し【Ｓｈｅｅｔ１】をクリックします。
4. Ｂ4セルをクリックします。
5. 【Sｈｉｆｔ】キーを押しながら、シート見出し【Sheet4】をクリックします。
   数式バーに『=SUM('Sheet1:Sheet4'!B4)』と表示されます。
6. 【Enter】キーを押して、式を確定します。
   
7. 表示画面がSheet5に戻ります。
8. B4セルをクリックし、【コピー】ボタンをクリックします。
9. セル範囲「B5:B8」をドラッグして選択し、セル範囲「C4:D8」を【Ctrl】キーを押しながらクリックして選択します。
10. 【貼り付け】ボタンをクリックして数式をコピー＆貼り付けして完成です。
    

(注意)　串刺し集計を行っている全シートの一部分を修正する場合は、すべてのシートを選択して修正します。
　　　　　1枚1枚修正するのは大変ですし、間違いの元になります。
　(例) C列に『大分支店』を入れたい場合。

1. 『Sheet1』〜『Sheet5』を選択します。
2. C列を選択し、【挿入】→【列】で列を挿入し、C3セルに『大分支店』と入力します。
   選択している全シートのC3セルに入力されます。
3. 挿入したC列には数式が入っていませんので、数式をコピーして貼付けます。

(Memo) 3-D参照では使用できる関数に制限があります。

• SUM、AVERAGE、AVERAGEA、COUNT、COUNTA、MAX、MAXA、MIN、MINA、PRODUCT、STDEV、STDEVA、STDEVP、STDEVPA、VAR、VARA、VARP、および VARPA とされています。

シート間のデータの統合　　　　　　Topへ

(もっとも複雑な例です)

• 3枚のシートにそれぞれのデータが入力されています。
• 売上高合計シートにA支店とB支店売上高シートのデータを統合します。
• 商品名の項目数や月の並びが異なっています。

A支店の売上高　　統合元1	B支店の売上高　　統合元2
売上高の合計　　　統合先

Excel2010,2013,2016での操作手順は以下のページをご覧ください。

• データの統合（Excel2010)
• Excel2013でデータを統合する
• Excel 2016で統合機能を使って複数シートを集計する
• Excel 2016で統合機能を使って重複データを集計する
• Excel 2016で統合機能を使って必要な項目だけ集計する

【Excel2003以前の操作手順】

1. 統合先の基準となるセルをクリックして、アクティブにします。
   売上高の合計シート(統合先）　の　B5セル　をクリック。
2. メニューバーの【データ】→【統合】を選択します。
3. 【集計の方法】は【合計】を選択しました。
4. 【統合元範囲】を入力します。
   A支店の売上高シートのシート見出しをクリックして、C6:F11を選択します(項目を含めます)。
5. 【追加】ボタンをクリックして、
   B支店の売上高シートのシート見出しをクリックして、B5:E10を選択します(項目を含めます)。　
   ダイアログが邪魔になる時は、下図(3)(ダイアログ縮小ボタン）をクリックして小さくします。
6. 【統合の基準】は【上端行】と【左端列】にチェックを入れます。
   月数と商品名を基準に合計するため。
7. 【OK】ボタンをクリックします。

(3)と(4)　の操作	(4)〜(6)の操作
(7)統合された結果です。

統合の基準

• 統合元の列と行の項目が同数で、同じ順序に並んでいる時には、【上端行】、【左端列】のチェックは不要です。
• 列や行項目のどちらかまたは両方の数や並びが違う時にチェックを入れます。

シート間の統合でリンクする場合

• 【統合元のデータとリンクする】にチェックをつけます。
• 統合元のデータが変更されると、統合先のデータも変更されます。

一行おきに合計する　　　　　Topへ　　　　

SUMPRODUCT関数を使う方法

1. 偶数行の合計
   • C12セル：=SUMPRODUCT((MOD(ROW(C2:C11),2)=0)*C2:C11)
   • (注)C列に文字列が入力されている場合、下のように「，」を使うとよいです。
     =SUMPRODUCT(MOD(ROW(C2:C11)-1,2),C2:C11)
2. 奇数行の合計
   • C13セル：=SUMPRODUCT((MOD(ROW(C2:C11),2)=1)*C2:C11)
   • 　　　　 =SUMPRODUCT(MOD(ROW(C2:C11),2),C2:C11)

作業列を使う方法

• 表の形式上、止むを得ず一行おきに数値が入力してある場合の合計方法です。
• (例)　C列の収入/支出をそれぞれ合計します。作業列としてD列を使用します。

【操作手順】

1. データの同一行の列（例ではD列)に作業列を作成します。
   実用上はD列を非表示にすれば目に触れません。
2. D列の数式：D1セルに『=MOD(ROW(),2)』と入力します。
   • 意味：もし行番号を2で割った時の余りを表示します。
     　　　つまり、奇数行なら1、偶数行なら0となります。
3. C列の数式
   収入の合計：C9セルに『=SUMIF(D1:D8,1,C1:C8)』
   • 意味：D1〜D8セルが1ならC1〜C8セルの値を合計しなさい。つまり、奇数行のセルを合計しなさい。
   支出の合計：C10セルに『=SUMIF(D1:D8,0,C1:C8)』
   • 意味：D1〜D8セルが0ならC1〜C8セルの値を合計しなさい。つまり、偶数行のセルを合計しなさい。

• では、3行ごとの場合は？
  • 『=MOD(ROW(),3)』を使えば余りは1,2,0となります。
  • 『=SUMIF(D1:D8,1,C1:C8)』、『=SUMIF(D1:D8,2,C1:C8)』、『=SUMIF(D1:D8,0,C1:C8)』
  • 後は・・・・(同じ考え方でOKです)

3行おき、4行おきの計算は?

• 同様に　MOD(ROW(),3)　や　MOD(ROW(),4)　を使えばよいです。

一列おきに合計する　　　　　Topへ

SUMPRODUCT関数を使う方法

• 上(行)と同じような数式で計算できます。
  • 偶数列の合計：=SUMPRODUCT(B4:K4*(MOD(COLUMN(B4:K4),2)=0))
  • 奇数列の合計：=SUMPRODUCT(B4:K4*(MOD(COLUMN(B4:K4),2)=1))

作業列を使う方法

• 表の形式上、止むを得ず一列ごとに数値が入力してある場合の合計方法です。
• (例)　B4〜I6セルの女性/男性をそれぞれ合計します。作業列として1行目を使用します。
  

【操作手順】

1. 1行目に作業行を作成します。
   実用上は1行目を非表示にすれば目に触れません。または、フォントの色をセルと同色の白色にします。
2. 1行目の数式
   A1セルに『=MOD(COLUMN(),2)』と入力します。
   意味：列番号を2で割った時の余りを表示します。奇数列(男性)なら1、偶数列(女性)なら0となります。
3. J4:K6セルの数式
   • 女性の合計：J4セルに『=SUMIF($B$1:$I$1,0,B4:I4)』
     • 意味：B1〜I1セルが0ならB4〜I4セルの値を合計しなさい。つまり、偶数列のセルを合計しなさい。
   • 男性の合計：K4セルに『=SUMIF($B$1:$I$1,1,B4:I4)』
     • 意味：B1〜I1セルが1ならB4〜I4セルの値を合計しなさい。つまり、奇数列のセルを合計しなさい。
4. J4:K4セルを選択し、フィルハンドルをドラッグして（オートフィルで）6行目までコピーします。

• 上記、説明に間違いがありました。2008/11/17に修正しました　m(__)m

重複データのチェック　　　　　Topへ

• 作業列を使って、重複データをチェックする方法です。
  ここでの重複は品目のみの重複を調べます。

1. E列を作業列として使用します。
2. E3セルに「=IF(COUNTIF($B$3:B3,B3)>1,"重複","")」と入力し、E7セルまでコピーします。
3. 重複データの行に「重複」と表示されます。
4. 「データ」→「フィルタ」→「オートフィルタ」を選択します。
5. 「▼」をクリックして、「重複」を選択すると、重複データが抽出できます。
   
6. 抽出された行を選択して、「編集」→「削除」で選択行を削除します。
   
7. 「データ」→「フィルタ」→「オートフィルタ」で解除します。
   しかし、個数と合計の値に注意してください。削除した行の値も削除されますので、
   計算を行っているデータ範囲にはこの方法は適切ではありません。
   

重複データを入力できないようにする(入力規則)　　　Topへ

1. A列を選択し、[データ]タブの[データの入力規則]を実行します。
   
2. 入力値の種類で ユーザー設定　を選択します。
   数式に　=COUNTIF($A$1:A1,A1)<2　と入力します。
   [OK]ボタンをクリックします。
   • ここでは、A列全部を選択していますが、A1:A10といった一部分でも同じ数式でOKです。
   
3. 同じ値を入力すると、エラーメッセージが表示されて、入力ができません。
   

分数の計算をする　　　　　　Topへ

• 分数の計算方法です。　(→分数の入力方法)

(例1)　下表は入力例です。

• 真分数は「0」「Space」・・・と入力します。
• 帯分数は間に「Space」を入力します。

(計算結果)

複数条件での合計を求める　　　　　Topへ

• 複数の条件で合計を計算するには
  1. SUMPRODUCT関数が使用できます。
  2. 作業列を使うと、SUMIF関数で計算できます。
  3. 条件をセルに書き出せば、DSUM関数で計算できます。

(A) SUMPRODUCT関数を使った例

(例) 商品名「みかん」　サイズ「S」の金額を合計します。

• 条件を数式に書き込む場合
  D3セル　 =SUMPRODUCT((B6:B12="みかん")*(C6:C12="S"),(D6:D12))
• 条件がセルに入力されている場合
  D3セル　 =SUMPRODUCT((B6:B12=B3)*(C6:C12=C3),(D6:D12))

(SUMPRODUCT関数での計算過程の説明)

• 数式　『=SUMPRODUCT((B6:B12="みかん")*(C6:C12="S"),(D6:D12))』を以下の表で分解してみます。
• B列に「B6:B12="みかん"」を満足するセルはTRUEになります。(今回の例ではB6:B12の全てがTRUE)
• C列に「C6:C12="S"」を満足するセルはTRUE、満足しないセルはFALSE　となります。(C6:C12セル)
• =SUMPRODUCT((B6:B12="みかん")*(C6:C12="S"),(D6:D12))
  =SUMPRODUCT(B列*C列,D列)
  TRUE=1　FALSE=0　なので、
  E列の計算結果を合計した結果が得られます。

(注意)

SUMPRODUCT関数では、セル範囲を「A:A」のように列全体を指定した場合、エラーとなります。
「A1:A65536」のようにセル範囲を指定します。

(B)　SUMIF関数を使った例

• SUMIF関数を使って、商品名「みかん」　サイズ「S」の金額を合計します。

【操作手順】E列を作業列として使用します。

1. E6セルに　=B6&C6　と入力し、フィルハンドルをダブルクリックしてE12セルまで数式をコピーします。
2. D3セルに数式を入力します。
   =SUMIF(E6:E12,B3&C3,D6:D12)
   2，3行目を表示したくない場合は、数式に値を入れます。
   =SUMIF(E6:E12,”みかんS",D6:D12)

(C)　DSUM関数を使った例

• DSUM関数を使って、商品名「みかん」　サイズ「S」の金額を合計します。

1. B2:C2に項目と条件を入力します。
2. D3セルに数式を入力します。
   　=DSUM(B5:D12,D2,B2:C3)
   または、
   　=DSUM(B5:D12,"金額",B2:C3)

(例2)セルの数を数える

• SUMPRODUCT関数を使った例を示します。
• (例1)と同じように、COUNTIF関数やDCOUNTA関数を使うこともできます。

(例)

• 商品名「みかん」　サイズ「S」のセルの数を求めます。
• D3セル=SUMPRODUCT((B6:B12=B3)*(C6:C12=C3))
  または=SUMPRODUCT((B6:B12="みかん")*(C6:C12="S"))

(例3) 月毎の集計をしたい

• 販売月が「1」、商品名「みかん」、サイズ「S」の販売金額の合計を求めます。
• =SUMPRODUCT((MONTH(B6:B12)=B3)*(C6:C12=C3)*(D6:D12=D3),E6:E12)
  または
  =SUMPRODUCT((MONTH(B6:B12)=1)*(C6:C12="みかん")*(D6:D12="S"),E6:E12)
• ところが、空白のセル(B12)がある時、=MONTH(B12)=MONTH(N(""))=1 となってしまいます。
  そこで、セルの値が空白ではないという条件を追加します。
  =SUMPRODUCT((MONTH(B6:B12)=B3)*(C6:C12=C3)*(D6:D12=D3)*(B6:B12<>""),E6:E12)
  または
  =SUMPRODUCT((MONTH(B6:B12)=1)*(C6:C12="みかん")*(D6:D12="S")*(B6:B12<>""),E6:E12)

文字列の数式を計算する　　　　　　Topへ

ユーザー定義関数を使う方法

1. 【Alt】+【F11】でVBEを開き、標準モジュールに
   Function KEISAN(R As Range)
   　　　KEISAN = Evaluate(R.Value)
   End Function
   と入力します。
2. A列には文字列として数式が入力してあります。
   B1セルに定義した関数「keisan」を使って、『=keisan(A1)』と入力すると計算されます。
   

四則演算子が全角で使用されている場合

1. かけ算に「×」、割り算に「÷」が使われている場合には、それぞれの記号を「*」「/」に置換します。
2. 上記のコードを下のように変更します。
   
3. シートでの使用例です。
   

EVALUATEを名前定義で使用する方法

1. [数式]タブの定義された名前グループにある[名前の定義]をクリックします。
   
2. 名前に　keisan2　と入力しました。
   参照範囲に　=EVALUATE(Sheet1!A1)+NOW()*0 と入力しました。
   [OK]ボタンをクリックします。
   
3. A列には文字列として数式が入力してあります。
   B1セルに定義した関数「keisan2」を使って、『=keisan2』と入力すると計算結果が表示されます。
   

文字列から数字を取り出す　　　Topへ

• 文字と数字が混在している文字列から数字を取り出す。

• 	Ｂ	Ｃ	Ｄ	Ｅ
  2	12円	12	約20	20
  3	25冊	25	Ｎｏ120	120

  • C2：=IF(COUNT(LEFT(B2)),LOOKUP(10^16,--LEFT(B2,ROW($1:$16))),"")
  • E2：=IF(COUNT(RIGHT(D2)),LOOKUP(10^16,--RIGHT(D2,ROW($1:$16))),"")
    • ROW($1:$16)の部分は、{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16}と配列になっています。
    • LOOKUP(検査値,配列)で検査値「10^16」は数値と認識できる値の最大値の意味で、検査値が見つからないと、配列の先頭行または先頭列に含まれている検査値以下の最大の値が返されます。
      よって、数字の文字数を得ることができます。

列/行の最終値を求める

• セル範囲の最終値を求める
• (例1)セル範囲A1:A10の一番下の行に入力されている数値を求める
  • =LOOKUP(10^16,A1:A10)
• (例2)セル範囲B1:B10の一番下の行に入力されている文字列を求める
  • =LOOKUP(10^9,CODE(B1:B10),B1:B10)
• 

SUM関数の答えが合わない？

• 加算の数式とSUM関数の数式とで答えが異なることがあるという例です。
• 滅多にないとは思いますが、こんなこともあるということで・・・

• (問題)
  • 下図のようにB1セルには『=SUM(A1:A4)』　B2セルには『=A1+A2+A3+A4』と数式が入力されています。
  • ところが、B1は「70」、B2は「100」と異なった値が表示されています。なぜ？？
    
• （解答)
  • C1セルに『=TYPE(A1)』と入力し、C4セルまでコピーします。
  • すると、C3セルは「2」となり、文字列になっているのが分かります。
  • つまり、SUM関数は文字列は無視しますが、「+」を使った加算では計算できる文字列は数値と見なして計算をする為です。
  • 
  • ※　この例では、一見しては、文字列なのか数値なのか区別できないように、
    A3セルの表示形式を文字列にして右揃えしています。　　

値の比較が思うようにならない　　　Topへ

• 記号や空白を含む全ての文字は数値よりも大きいと判断されます。
• 文字と文字の比較は、文字コード番号を基準に行われます。また、記号や空白も同様にコード番号によって大小の比較が行われます。
• 「""」は 0 バイトの文字列 (Null 値) として認識され、全ての数値よりも大きく、全ての文字よりも小さいと判断されます。
• 【参考】 以下のリンク先は削除されましたので、リンクを解除しました。
  • [XL2000]比較演算子による比較結果について
    http://support.microsoft.com/kb/416983/JA/
  • [XL2000]数字の入力方法と並べ替え順序
    http://support.microsoft.com/kb/412061/JA/

スポンサードリンク

　PageViewCounter
　
　Since2006/2/27