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

偏差値の計算式　　　　Topへ

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

串刺し集計の方法　　　　Topへ

• 何枚かの同形式のシートを集計する方法です。
• 参照されるシートはシート見出しに連続して並んでいる必要があります。

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

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列を選択し【データ】→【入力規則】を選択し、【設定】タブで
   • 『入力値の種類』を「ユーザー設定」
   • 『数式』の欄に「=COUNTIF($A$1:A1,A1)<2」
   と入力します。します。
2. 同じ値を入力すると、エラーメッセージが表示されます。

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