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

計算関連のQ&A(Tips)

2022/10/15

偏差値の計算式　　　　Topへ

• 偏差値：　=(得点-平均点)/標準偏差*10+50
  平均点：　=AVERAGE(範囲)
  標準偏差 ：　=STDEV.P(範囲)
  まとめると、
  偏差値：　=(得点-AVERAGE(範囲))/STDEV.P(範囲)*10+50
  と、なります。
• 計算例です。
  F3セルには　=(E3-AVERAGE(B2:B101))/STDEV.P(B2:B101)*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)

スポンサードリンク

　PageViewCounter
　
　Since2006/2/27