haku1569　Excel でらくらく データ分析！

Excelで式や関数を使って計算している時に色々なエラー値が表示される時があります。
今回はそれらのエラー値がどういう時に表示されるかをまとめてみました。
紹介するエラー値は、
“#VALUE!”、“#DIV/0!”、“#NAME?”、“#N/A”、“#REF!”、“#NUM!”、“#NULL!”と“#####”。


#VALUE!（Wrong type value / バリュー）
計算や関数で使用する値の種類が間違っている時に表示されます。
例えば、足し算で合計を求めます。


この時にセルに文字“A”があるとエラーになります。


エラーが表示されたセルにカーソルを置くと左側に！マークが表示されます。
ドロップダウンリストから「計算の過程を表示」を選択します。


すると「数式の検証」画面が表示され、間違っている部分が斜体で表示されます。


またエラーが表示されたセルを引用して足し算をしてみます。


すると同じエラー値が表示されます。
ドロップダウンリストから「エラーのトレース」を選択してみます。


引用したエラー値のセルが赤い矢印で表示されます。


ちなみに、この矢印を消すには、「数式」「ワークシート分析」から「トレース矢印の削除」を選択します。


ちなみに、文字混じりのセルの合計をする場合は“SUM関数”を使います。


そうすると文字が混じっていても数値のみの合計が計算できます。


また、セルの足し算のところをSUM関数の様に間違って範囲指定してしまいました。


これもエラーが表示されます。



#DIV/0!（Divided by 0 / ディバイド・パー・ゼロ）
“0”や空白セルで割った時に表示されます。


空白セルで割った時もエラーが表示されま。



エラーが表示されたセルを引用して足し算をしてみます。


同様にエラーが表示されます。



#NAME?（Unrecognized name / ネーム）
認識されていない“名前”が使われた時に表示されます。エクセルではセルや範囲に“名前”をつけることができます。その名前はもちろん文字なのですが、文字を引数として使う場合は“”（ダブルクォーテーション）で囲みますが、“”が無い場合、引数の文字ではなく“名前”として認識されるのです。
例えば、“SUM関数”でセルの範囲指定の時に間違って“：”をつけ忘れました。


すると“#NAME?”エラーになります。


これは、セルの範囲を表す“：”が無かったために、“B4B8”という範囲名だと認識されてしまったからです。

また、文字列を繋げる式の中で文字列“東京”に“”をつけ忘れてしまいました。


これもエラーになります。


“東京都”というセル名だと認識されたからです。
で“”を付けてあげると


文字列がつながりました。


また、“SUM関数”のつもりが間違って“SAM”と入力してしまうと


エラーになります。



#N/A（No Assign / ノーアサイン）
計算に使用できる値が無い時にひょうじされます。
また、セルに“#N/A”を入力しておくとそれを引用した計算は行われずに“#N/A”エラーが表示されます。

“SUM関数”のセル範囲の中に“#N/A”を入力しておくと


エラーが表示されます。


“VLOOKUP関数”があって、引数の“検索方法”で完全一致の“FALSE”を指定します。


で、検索値を削除してしまうと、エラーになります。


また、存在しない検索値を入力しもエラーになります。


#REF!（A reference to a cell that does not exist / レファレンス）
参照セルが無い時に表示されます。
足し算の式があって、


その行が削除されてしまうと（空白ではありません）エラーが表示されます。


“VLOOKUP関数”で


検索値が入力されているセルの行が削除されてしまったりすると
同じくエラーになります。



#NUM!（An invalid number /ナンバー）
エクセルで値が計算できる範囲を超えた時に表示されます。
10の308乗を2倍しようとすると、


数式により処理できる最大値は“1.7976931348623158E+308”のためエラーになります


また、1900/1/1以前の日付を求めようと“EOMONTH関数”で5か月前の月末日（1899年8月31日）を求めようとすると


計算で使用できる一番古い日付が“1900/1/1”のためエラーになります。



#NULL!（Null intersection / ヌル）
セルの範囲指定が間違っている場合に表示されます

例えば“SUM関数”で範囲指定の“：”（コロン）の代わりにスペースが入っています。


すると、エラーが表示されます。


間違って“；”（セミコロン）を入れようとすると
間違い指摘画面（？）が表示されます。


「いいえ」を選択しても更に間違い指摘画面が表示されて式の入力をさせてくれませんでした。。。



################（シャープ）
通常、セルに“####”が表示される時は、セルの幅よりも表示する数値の桁数が大きい場合で、セル幅を増やしてあげればいいのですが、
日付のシリアル値が負の数字になる式があると


本気で（!?）“##############################・・・”が表示されます。

セル幅を増やしてもダメですよ。

とりあえず、今回はこの辺でおしまい。。。



関連ページ
関数一覧（カテゴリ別）
関数一覧（名前別）

例えば「2014年4月20日」等の日付データを「2014/04」と言う年月のデータにしてみます。
セルの書式設定で表示を年月にすることは可能ですが、それは表示が変わっただけでセルのデータそのものは何も変わっていません。
方法は、日付データから「年」を取り出し、次に「月」を取り出して最後に「年」と「月」を「/」で繋いだテキストデータにします。


では、セルに入力した式の内容を見てみましょう。
こうなっています。


ここで「=year()」は日付から年を返す日付関数。
「=month()」は月を返す日付関数になります。
ですが、「月」を求める時に一工夫しています。
最終的に年月を「/」で繋いだテキストデータになっていますので、月のデータを「=month()」だけで求めてしまうと、結果は
「1」・「2」・「3」・・・「10」・「11」・「12」となります。
テキストデータとしてこれを昇順で並び変えをするとどうなるかと言うと。。
「1」・「10」・「11」・「12」・「2」・「3」・・・「9」となってしまうんです。
これではデータとして都合が悪いため
「01」・「02」・「03」・・・「10」・「11」・「12」と桁を揃えたい訳です。
なので、「月」を求めるセルでは「=TEXT(MONTH(C5),"#00")」として
monthで求めた結果を2桁のテキスト（1桁の時は頭に“0”を付ける）にする式にしています。

さて、ピボットテーブルではグループ化で年月の集計をすることができますよ。。


関連ページ
YEAR関数で日付データから“年”を取り出してみた
MONTH関数で日付データから“月”を取り出してみた
ピボットテーブルで日毎のデータを年月で集計してみた（グループ化）

日付のデータにセルの書式設定で曜日を表示させてみましょう
こんな、かんじに。。。


セルの書式設定画面を開きます。
予め用意されている日付の設定には無いので「ユーザー定義」を選択します。
「種類（T）」のボックスに日付の表示の後に「(aaa)」と入力するとカッコの中に曜日が表示されます。

何ページにもなる長い表を印刷させる時、2ページ以降にもタイトル行が無いためとても見辛くなってしまいます。
印刷した時すべてのページにタイトル行を印刷する方法をみてみましょう。

例えば、こんな長い表があります。（この画像では全然長くないけどね。。）


印刷プレビューを見ると、1ページ目にはもちろんタイトル行がありますが。。。


2ページ以降にはタイトルが無いためとても見辛い表になってしまいます。


まず、メニューの「ファイル」→「ページ設定」を選択します。


ページ設定画面で、 行のタイトルに印刷したい行番号を範囲指定します。今回は印刷させたいのは行番号3だけなので“$3:$3”と入力します。


すると、このように2ページ目以降も指定した行が印刷されます。

何気なく使っている、セルに設定した計算式などを表示する「数式バー」は非表示にすることができます。
また、なんかの拍子に知らないうちに非表示になってしまったら、この方法で再表示させられます。

「fx」の部分を「数式バー」と言って、セルに設定された数式の中身が表示されます。


メニューの「表示」のリボンの「表示/非表示」に、「数式バー」のチェックボックスがありますから、ここのチェックを外すと表示されなくなります。

クリップボードと言うと、データを１つしか格納しておけないように思いますが、Officeでは複数のデータを保管して、それらから自由に選択して貼り付けることができます。

今複数のデータがコピーされてクリップボードに格納されています。
「ホーム」から「クリップボード」の右下のアイコンをクリックします。


すると、クリップボードに格納されたデータが24個まで表示されます。ここでは8個の格納されたデータが表示されています。


カーソルをクリップボードのデータを貼り付けたい位置に置いてから、貼り付けたいデータを選択します。
ここでは「バナナキャンデー」を選択し、右の矢印からドロップダウンリストを表示させます。


「貼付け」をクリックすると「バナナキャンデー」が貼りつきました。