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

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


表示されるエラーの原因と解消方法　　　　Topへ

表示 原因 対処法
##### セル幅が狭くて数値を表示しきれない時に表示されます。 セル幅を広げます。
書式で縮小して全体を表示します。
計算結果が負の値になる日付または時刻が入力されている場合に表示されます。 日付または時刻以外の表示形式を指定します。
計算式をチェックします。
#DIV/O! ゼロ (0) による除算(割り算)が行われた場合に表示されます。
除数（割るほうの数）が入力されていない場合にも表示されます。		 ゼロ以外の数値をいれます。
#N/A LOOKUP関数などの検索系の関数で、検索した値が指定した範囲内に存在しないとき表示されます。 検索値、検索範囲をチェックします。
ISNA関数で表示しないようにします。
#NULL! 指定した 2 つのセル範囲に共通部分がない場合に表示されます。
 参照範囲をチェックします。範囲が正しいのであれば、２つの範囲の間に「,」(カンマ)が入力されているかチェックします。
#NAME? 数式に使用している関数名、セル範囲の名前などが間違っている場合に表示されます。 数式に使用している名前や関数に間違いがないかどうか調べます。
アドイン：分析ツールを組み込む必要がある関数を使用している場合に表示されます。 ツール→アドインの分析ツールのチェックを入れます。
#NUM! エクセルで処理できる数値の範囲を超えている場合に表示されます。 計算結果が -1×10＾307 から 1×10＾307までの範囲に収まるようにします。
引数として数値を使用すべき部分に他の値が使用されている場合に表示されます。 引数が数値であるかを確認します。
IRR関数やRATE関数などの反復計算で、答えが見つからない場合に表示されます。 ワークシート関数の初期値を変更します。
数式の反復計算を行う回数を変更します。
#REF! 参照セル、あるいはリンク元が削除された場合に表示されます。 参照先を変更する、あるいは正しく参照を設定し直します。
#VALUE! 数値や論理値（TURE・FALSEなど）が必要な部分に文字列などがある場合
単独セルを参照するように設定した先が複数の引数が設定されている場合に表示されます。		 数式をチェックします

数式が表示され計算できない　　　Topへ

  1. セルの表示形式が文字列になっている場合
    1. セルの表示形式を標準にする。
    2. セル内を編集状態(【Ｆ2】キーを押す or セル内をダブルクリック）にし、【Enter】キーを押す。
  2. 【ツール】→【オプション】 の 「表示」タブで「数式」にチェックが付いている場合、「数式」のチェックを外します。

検索関数で検索値が見つからない

  1. 検索値にスペースが紛れ込んでいないか確認してみます。特に最後にスペースが含まれる場合は気づきにくいです。
  2. 表示形式は文字列であるが、データは数値と認識されていることがあります。
    この様な場合は、=TYPE(A)　とTYPE関数を使ってセルのデータ形式を確認します。

(参考)

計算方法が勝手に手動になってしまう(手動→自動に切り替える方法)　　　　Topへ

自動と手動の設定を切り替える方法

  1. ツール→オプションを選択し、計算方法タブで「自動」または「手動」を選択します。

ショートカットキーで再計算を実行する　　　Topへ

[F9] キー 開いているすべてのブックの中で、最後に計算されたとき以降に変更された数式と、その数式を参照する数式を再計算します。
[Shift]＋[ F9] キー  作業中のワークシートの中で、最後に計算されたとき以降に変更された数式と、その数式を参照する数式を再計算します。
[Ctrl]＋[Alt]＋[F９]キー 開いているすべてのブックの数式を再計算します。最後に計算されたとき以降に変更されたかどうかは問いません。
[Ctrl]＋[Shift]＋[Alt]＋[F９]キー 参照先の数式を再度チェックし、開いているすべてのブックの数式を再計算します。最後に計算されたとき以降に変更されたかどうかは問いません。

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

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

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

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

元旦 1月1日 海の日 7月第3月曜日
成人の日 1月第2月曜日 敬老の日 9月第3月曜日
建国記念日 2月11日 秋分の日 下記参照
春分の日 下記参照 体育の日 10月第2月曜日
みどりの日 4月29日 文化の日 11月3日
憲法記念日 5月3日 勤労感謝の日 11月23日
国民の休日 5月4日 天皇誕生日 12月23日
子供の日 5月5日

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

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

月末を求める

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

偏差値の計算式　　　　Topへ

串刺し集計の方法　　　　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参照では使用できる関数に制限があります。

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

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

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


【操作手順】

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

分数の計算をする　　　　　　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 みかんＭ
8 みかん S 2,100 =B8&C8 みかんＳ
9 みかん S 3,000 =B9&C9 みかんＳ
10 みかん M 2,500 =B10&C10 みかんＭ
11 みかん S 1,200 =B11&C11 みかんＳ
12 みかん S 2,100 =B12&C12 みかんＳ

(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. A列には文字列として数式が入力してあります。
    B1セルに定義した関数「keisan2」を使って、『=keisan2』と入力すると計算結果が表示されます。

文字列から数字を取り出す　　　Topへ

列/行の最終値を求める

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

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

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

　PageViewCounter
　Counter
　Since2006/2/27