- Home »
- Excel講座の総目次 »
- Excel講座 Q&A(Tips) »
- 計算関連
2022/10/15
- 偏差値: =(得点-平均点)/標準偏差*10+50
平均点: =AVERAGE(範囲)
標準偏差 : =STDEV.P(範囲)
まとめると、
偏差値: =(得点-AVERAGE(範囲))/STDEV.P(範囲)*10+50
と、なります。
- 計算例です。
F3セルには =(E3-AVERAGE(B2:B101))/STDEV.P(B2:B101)*10+50 と入力しています。
- 何枚かの同形式のシートを集計する方法です。
- 参照されるシートはシート見出しに連続して並んでいる必要があります。
- 参考資料(Excel2010、Excel2013,Excel2016の操作手順はこちらをご覧ください)
(例) ここからはExcel2003以前での操作方法です。
Sheet1〜Sheet4に第1四半期〜第4四半期のデータがあります。
Sheet5にこれらの合計を計算したいと思います。

- Sheet5のB4セルをクリックしてアクティブにします。
- オートSUM(Σ)ボタンをクリックします。
セルには『=SUM()』と入力されます。
- シート見出し【Sheet1】をクリックします。
- B4セルをクリックします。
- 【Shift】キーを押しながら、シート見出し【Sheet4】をクリックします。
数式バーに『=SUM('Sheet1:Sheet4'!B4)』と表示されます。
- 【Enter】キーを押して、式を確定します。
- 表示画面がSheet5に戻ります。
- B4セルをクリックし、【コピー】ボタンをクリックします。
- セル範囲「B5:B8」をドラッグして選択し、セル範囲「C4:D8」を【Ctrl】キーを押しながらクリックして選択します。
- 【貼り付け】ボタンをクリックして数式をコピー&貼り付けして完成です。
(注意) 串刺し集計を行っている全シートの一部分を修正する場合は、すべてのシートを選択して修正します。
1枚1枚修正するのは大変ですし、間違いの元になります。
(例) C列に『大分支店』を入れたい場合。
- 『Sheet1』〜『Sheet5』を選択します。
- C列を選択し、【挿入】→【列】で列を挿入し、C3セルに『大分支店』と入力します。
選択している全シートのC3セルに入力されます。
- 挿入したC列には数式が入っていませんので、数式をコピーして貼付けます。
(Memo) 3-D参照では使用できる関数に制限があります。
- SUM、AVERAGE、AVERAGEA、COUNT、COUNTA、MAX、MAXA、MIN、MINA、PRODUCT、STDEV、STDEVA、STDEVP、STDEVPA、VAR、VARA、VARP、および
VARPA とされています。
(もっとも複雑な例です)
- 3枚のシートにそれぞれのデータが入力されています。
- 売上高合計シートにA支店とB支店売上高シートのデータを統合します。
- 商品名の項目数や月の並びが異なっています。
A支店の売上高 統合元1 |
B支店の売上高 統合元2 |
 |
 |
売上高の合計 統合先 |
 |
Excel2010,2013,2016での操作手順は以下のページをご覧ください。
【Excel2003以前の操作手順】
- 統合先の基準となるセルをクリックして、アクティブにします。
売上高の合計シート(統合先) の B5セル をクリック。
- メニューバーの【データ】→【統合】を選択します。
- 【集計の方法】は【合計】を選択しました。
- 【統合元範囲】を入力します。
A支店の売上高シートのシート見出しをクリックして、C6:F11を選択します(項目を含めます)。
- 【追加】ボタンをクリックして、
B支店の売上高シートのシート見出しをクリックして、B5:E10を選択します(項目を含めます)。
ダイアログが邪魔になる時は、下図(3)(ダイアログ縮小ボタン)をクリックして小さくします。
- 【統合の基準】は【上端行】と【左端列】にチェックを入れます。
月数と商品名を基準に合計するため。
- 【OK】ボタンをクリックします。
(3)と(4) の操作 |
(4)〜(6)の操作 |
 |
 |
(7)統合された結果です。 |
 |
統合の基準
- 統合元の列と行の項目が同数で、同じ順序に並んでいる時には、【上端行】、【左端列】のチェックは不要です。
- 列や行項目のどちらかまたは両方の数や並びが違う時にチェックを入れます。
シート間の統合でリンクする場合
- 【統合元のデータとリンクする】にチェックをつけます。
- 統合元のデータが変更されると、統合先のデータも変更されます。

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 |
- 偶数行の合計
- C12セル:=SUMPRODUCT((MOD(ROW(C2:C11),2)=0)*C2:C11)
- (注)C列に文字列が入力されている場合、下のように「,」を使うとよいです。
=SUMPRODUCT(MOD(ROW(C2:C11)-1,2),C2:C11)
- 奇数行の合計
- C13セル:=SUMPRODUCT((MOD(ROW(C2:C11),2)=1)*C2:C11)
- =SUMPRODUCT(MOD(ROW(C2:C11),2),C2:C11)
作業列を使う方法
- 表の形式上、止むを得ず一行おきに数値が入力してある場合の合計方法です。
- (例) C列の収入/支出をそれぞれ合計します。作業列としてD列を使用します。

【操作手順】
- データの同一行の列(例ではD列)に作業列を作成します。
実用上はD列を非表示にすれば目に触れません。
- D列の数式:D1セルに『=MOD(ROW(),2)』と入力します。
- 意味:もし行番号を2で割った時の余りを表示します。
つまり、奇数行なら1、偶数行なら0となります。
- 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) を使えばよいです。
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行目の数式
A1セルに『=MOD(COLUMN(),2)』と入力します。
意味:列番号を2で割った時の余りを表示します。奇数列(男性)なら1、偶数列(女性)なら0となります。
- 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セルの値を合計しなさい。つまり、奇数列のセルを合計しなさい。
- J4:K4セルを選択し、フィルハンドルをドラッグして(オートフィルで)6行目までコピーします。
- 上記、説明に間違いがありました。2008/11/17に修正しました m(__)m
- 作業列を使って、重複データをチェックする方法です。
ここでの重複は品目のみの重複を調べます。
|
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,"重複","") |
- E列を作業列として使用します。
- E3セルに「=IF(COUNTIF($B$3:B3,B3)>1,"重複","")」と入力し、E7セルまでコピーします。
- 重複データの行に「重複」と表示されます。
- 「データ」→「フィルタ」→「オートフィルタ」を選択します。
- 「▼」をクリックして、「重複」を選択すると、重複データが抽出できます。
- 抽出された行を選択して、「編集」→「削除」で選択行を削除します。
- 「データ」→「フィルタ」→「オートフィルタ」で解除します。
しかし、個数と合計の値に注意してください。削除した行の値も削除されますので、
計算を行っているデータ範囲にはこの方法は適切ではありません。
- A列を選択し、[データ]タブの[データの入力規則]を実行します。
- 入力値の種類で ユーザー設定 を選択します。
数式に =COUNTIF($A$1:A1,A1)<2 と入力します。
[OK]ボタンをクリックします。
- ここでは、A列全部を選択していますが、A1:A10といった一部分でも同じ数式でOKです。
- 同じ値を入力すると、エラーメッセージが表示されて、入力ができません。
(例1) 下表は入力例です。
- 真分数は「0」「Space」・・・と入力します。
- 帯分数は間に「Space」を入力します。
|
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 |
- 複数の条件で合計を計算するには
- SUMPRODUCT関数が使用できます。
- 作業列を使うと、SUMIF関数で計算できます。
- 条件をセルに書き出せば、DSUM関数で計算できます。
(A) SUMPRODUCT関数を使った例
(例) 商品名「みかん」 サイズ「S」の金額を合計します。
- 条件を数式に書き込む場合
D3セル =SUMPRODUCT((B6:B12="みかん")*(C6:C12="S"),(D6:D12))
- 条件がセルに入力されている場合
D3セル =SUMPRODUCT((B6:B12=B3)*(C6:C12=C3),(D6:D12))
|
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関数での計算過程の説明)
- 数式 『=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列の計算結果を合計した結果が得られます。
|
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関数を使った例
- SUMIF関数を使って、商品名「みかん」 サイズ「S」の金額を合計します。
- 【操作手順】E列を作業列として使用します。
-
- E6セルに =B6&C6 と入力し、フィルハンドルをダブルクリックしてE12セルまで数式をコピーします。
- 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関数を使った例
- DSUM関数を使って、商品名「みかん」 サイズ「S」の金額を合計します。
- B2:C2に項目と条件を入力します。
- 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)セルの数を数える
- SUMPRODUCT関数を使った例を示します。
- (例1)と同じように、COUNTIF関数やDCOUNTA関数を使うこともできます。
(例)
- 商品名「みかん」 サイズ「S」のセルの数を求めます。
- D3セル=SUMPRODUCT((B6:B12=B3)*(C6:C12=C3))
または=SUMPRODUCT((B6:B12="みかん")*(C6:C12="S"))
|
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) 月毎の集計をしたい
- 販売月が「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)
|
B |
C |
D |
E |
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)|計算関連のQ&A(Tips)
PageViewCounter

Since2006/2/27