よねさんのWordとExcelの小部屋Excel(エクセル)講座の総目次Excel(エクセル)講座 Q&A(Tips)|計算関連 3/4(複数条件など)
よねさんのWordとExcelの小部屋

Excel(エクセル)講座 Q&A(Tips):計算関連 3/4(複数条件など)


スポンサードリンク

偏差値の計算式    Topへ

串刺し集計の方法    Topへ

(例) 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へ    

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. 同じ値を入力すると、エラーメッセージが表示されます。

分数の計算をする      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

スポンサードリンク


よねさんのWordとExcelの小部屋Excel(エクセル)講座の総目次Excel(エクセル)講座 Q&A(Tips)|計算関連 3/4(複数条件など)

 PageViewCounter
 Counter
 Since2006/2/27