よねさんのWordとExcelの小部屋

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


スポンサードリンク

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

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

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

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

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

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

月末を求める

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

偏差値の計算式    Topへ

串刺し集計の方法    Topへ

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

  1. Sheet5のB4セルをクリックしてアクティブにします。
  2. オートSUM(Σ)ボタンをクリックします。
    セルには『=SUM()』と入力されます。
  3. シート見出し【Sheet1】をクリックします。
  4. B4セルをクリックします。
  5. 【Shift】キーを押しながら、シート見出し【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参照では使用できる関数に制限があります。


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

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

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

Excel2010,2013,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関数を使う方法

B C
2 収入 2,000
3 支出 1,500
4 収入 2,500
5 支出 1,200
6 収入 3,700
7 支出 2,000
8 収入 1,400
9 支出 4,200
10 収入 3,500
11 支出 2,500
12 総収入 13,100
13 総支出 11,400
  1. 偶数行の合計
  2. 奇数行の合計

作業列を使う方法



【操作手順】

  1. データの同一行の列(例ではD列)に作業列を作成します。
    実用上はD列を非表示にすれば目に触れません。
  2. D列の数式:D1セルに『=MOD(ROW(),2)』と入力します。
  3. C列の数式
    収入の合計:C9セルに『=SUMIF(D1:D8,1,C1:C8)』 支出の合計:C10セルに『=SUMIF(D1:D8,0,C1:C8)』

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

一列おきに合計する     Topへ

SUMPRODUCT関数を使う方法

作業列を使う方法

【操作手順】

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

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

B C D E
2 品目 個数 合計 チェック
3 みかん 5 16 =IF(COUNTIF($B$3:B3,B3)>1,"重複","")
4 りんご 4 4 =IF(COUNTIF($B$3:B4,B4)>1,"重複","")
5 みかん 7 16 =IF(COUNTIF($B$3:B5,B5)>1,"重複","")
6 ぶどう 5 5 =IF(COUNTIF($B$3:B6,B6)>1,"重複","")
7 みかん 4 16 =IF(COUNTIF($B$3:B7,B7)>1,"重複","")
  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]ボタンをクリックします。
  3. 同じ値を入力すると、エラーメッセージが表示されて、入力ができません。

分数の計算をする      Topへ

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

B C D
2 0 1/2 0 1/3 =B2+C2
3 2 1/2 1 2/3 =B3+C3
4 2 1/2 0 1/6 =B4+C4


(計算結果)

B C D
2 0 1/2 0 1/3 5/6
3 2 1/2 1 2/3 4 1/6
4 2 1/2 0 1/6 2 2/3

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

(A) SUMPRODUCT関数を使った例

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

B C D
2 商品名 サイズ 金額
3 みかん S 9,400
4
5 商品名 サイズ 金額
6 みかん S 1,000
7 みかん M 1,200
8 みかん S 2,100
9 みかん S 3,000
10 みかん M 2,500
11 みかん S 1,200
12 みかん S 2,100


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

B C D E
2 商品名 サイズ 金額
3 みかん S 9,400
4
5 商品名 サイズ 金額
6 TRUE TRUE 1,000 1*1*1,000=1,000
7 TRUE FALSE 1,200 1*0*1,200=0
8 TRUE TRUE 2,100 1*1*2,100=2,100
9 TRUE TRUE 3,000 1*1*3,000=3,000
10 TRUE FALSE 2,500 1*0*2,500=0
11 TRUE TRUE 1,200 1*1*1,200=1,200
12 TRUE TRUE 2,100 1*1*2,100=2,100
13 合計→ 9,400
(注意)
SUMPRODUCT関数では、セル範囲を「A:A」のように列全体を指定した場合、エラーとなります。
「A1:A65536」のようにセル範囲を指定します。

(B) SUMIF関数を使った例

【操作手順】E列を作業列として使用します。
  1. E6セルに =B6&C6 と入力し、フィルハンドルをダブルクリックしてE12セルまで数式をコピーします。
  2. D3セルに数式を入力します。
    =SUMIF(E6:E12,B3&C3,D6:D12)
    2,3行目を表示したくない場合は、数式に値を入れます。
    =SUMIF(E6:E12,”みかんS",D6:D12)
B C D E F
2 商品名 サイズ 金額
3 みかん S 9,400
4
5 商品名 サイズ 金額 ↓作業列 ↓E列の表示
6 みかん S 1,000 =B6&C6 みかんS
7 みかん M 1,200 =B7&C7 みかんM
8 みかん S 2,100 =B8&C8 みかんS
9 みかん S 3,000 =B9&C9 みかんS
10 みかん M 2,500 =B10&C10 みかんM
11 みかん S 1,200 =B11&C11 みかんS
12 みかん S 2,100 =B12&C12 みかんS

(C) DSUM関数を使った例

  1. B2:C2に項目と条件を入力します。
  2. D3セルに数式を入力します。
     =DSUM(B5:D12,D2,B2:C3)
    または、
     =DSUM(B5:D12,"金額",B2:C3)
B C D
2 商品名 サイズ 金額
3 みかん S 9,400
4
5 商品名 サイズ 金額
6 みかん S 1,000
7 みかん M 1,200
8 みかん S 2,100
9 みかん S 3,000
10 みかん M 2,500
11 みかん S 1,200
12 みかん S 2,100

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

(例)

B C D
2 商品名 サイズ セルの数
3 みかん S 5
4
5 商品名 サイズ 金額
6 みかん S 1,000
7 みかん M 1,200
8 みかん S 2,100
9 みかん S 3,000
10 みかん M 2,500
11 みかん S 1,200
12 みかん S 2,100

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

2 販売月 商品名 サイズ 販売金額
3 1 みかん S 4,300
4
5 販売月日 商品名 サイズ 販売金額
6 1/10 みかん S 1,000
7 1/11 みかん M 1,200
8 1/12 みかん S 2,100
9 2/10 みかん S 3,000
10 2/11 みかん M 2,500
11 1/14 みかん S 1,200
12 みかん S 2,100

文字列の数式を計算する      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へ

列/行の最終値を求める

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


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

スポンサードリンク



よねさんのWordとExcelの小部屋Excel(エクセル)講座の総目次Excel(エクセル)講座 Q&A(Tips)|計算関連

 PageViewCounter
 Counter
 Since2006/2/27