2022年02月06日

目次

イチ推しマーク

基本


テーブル


データ分析



関数

カテゴリ別一覧/サブメニュー

名前順一覧/サブメニュー


関数の応用



グラフ



特集記事

時間計算について / サブメニュー


Excel一般




Excelで困った時は / こんな時どうする?


書式設定




条件付き書式



データ
データの入力規制






ファイル操作



ワークシート分析



キーボード・ショートカット



小技・裏技集(Tips)



Excelの応用



Excel VBA



ラベル:EXCEL エクセル
posted by haku1569 at 00:00| Excel | このブログの読者になる | 更新情報をチェックする

2014年06月29日

エラー値についてまとめてみた(#VALUE!、#DIV/0!、#NAME?、#N/A、#REF!、#NUM!、#NULL!とか)/Excel/関数

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

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

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

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

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

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

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

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

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

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

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

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

これもエラーが表示されます。
20140629_val12.JPG


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

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

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

同様にエラーが表示されます。
20140629_div5.JPG


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

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

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

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

これもエラーになります。
20140629_nam4.JPG

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

文字列がつながりました。
20140629_nam6.JPG

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

エラーになります。
20140629_nam8.JPG


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

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

エラーが表示されます。
20140629_na2.JPG

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

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

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

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

その行が削除されてしまうと(空白ではありません)エラーが表示されます。
20140629_ref2.JPG

“VLOOKUP関数”で
20140629_ref3.JPG

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


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

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

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

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


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

例えば“SUM関数”で範囲指定の“:”(コロン)の代わりにスペースが入っています。
20140629_nul1.JPG

すると、エラーが表示されます。
20140629_nul2.JPG

間違って“;”(セミコロン)を入れようとすると
間違い指摘画面(?)が表示されます。
20140629_nul3.JPG

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


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

本気で(!?)“##############################・・・”が表示されます。
20140629_##2.JPG
セル幅を増やしてもダメですよ。

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



関連ページ
関数一覧(カテゴリ別)
関数一覧(名前別)
posted by haku1569 at 18:45| Excel | このブログの読者になる | 更新情報をチェックする

2014年04月26日

Excel関数一覧(カテゴリ別)



1. 論理

論理式の結果で指定した値を表示する / IF
エラーの時に表示する値を設定してみた / IFERROR


2. 文字列関数

全角カタカナを半角カタカナに変換 / ASC

文字列内の半角文字を全角文字に変換する / JIS

文字列に含まれる英字を小文字に変換する / LOWER

文字列に含まれる英字を大文字に変換する / UPPER

文字列に含まれる英単語の最初の文字を大文字に変換する / PROPER

文字列中の指定した数の文字を他の文字に変換する / REPLACE

指定した文字列を他の文字列の中で変換する / FIND

文字列中の指定した文字を他の文字に置換する / SUBSTITUTE

テキスト(文字列)の数字を数値に変換する / VALLUE

数値を四捨五入して書式設定した文字列に変換する / FIXED

数値を四捨五入して円書式の文字列に変換する / YEN

数値を四捨五入してドル書式の文字列に変換する / DOLLAR

数値を書式設定した文字列に変換する / TEXT

数値をASCII またはJISコードとしてそれに対応する文字を表示する / CHAR

文字列の長さ(文字数)を求める / LEN

文字列の右端から指定した数の文字を取り出す / RIGHT

文字列の左端から指定した数の文字を取り出す / LEFT

指定した位置から指定した数の文字を取り出す / MID

文字列からふりがなを取り出す / PHONETIC

文字列から印刷できない文字を削除する / CLEAN

文字列から余分なスペースを削除する / TRIM

文字列の先頭文字に対応するASCII またはJISコード番号を表示する / CODE

複数の文字列を統合して1つの文字列を作る / CONCATENATE

2つの文字列が等しいか確認する / EXACT

文字列を指定した回数繰り返して表示させる / REPT

文字列に含まれる文字の位置(左から何番目か)を求める / SEARCH

数値を文字列に変換する / TEXT


3. 数学関数

数値の絶対値を求める / ABS

セルの合計を求める / SUM

条件に一致したセルの合計を求める / SUMIF

掛け算の積を求める / PRODUCT

配列の掛け算の合計を求める / SUMPRODUCT

乱数を発生させる(指定した範囲内) / RANDBETWEEN

組合せを求める / COMBIN

順列を求める / PERMUT


端数処理

数値を四捨五入する / ROUND

数値を切り上げする / ROUNDUP

数値を切り下げする / ROUNDDOWN

指定した値の倍数で切上げする / CEILING


割り算の余り(剰余)を求める / MOD


4. 三角関数

角度(度)から弧度(ラジアン)を求める / RADIANS

角度から直角三角形の辺の比を求める / SIN、COS、TAN

三角形の辺の比率から角度を求める(SINの逆関数)/ ASIN

三角形の辺の比率から角度を求める(COSの逆関数) / ACOS


5. 統計関数

・平均値を求める / AVERAGE
・文字を含んだ範囲の平均値を求める / AVERAGEA
・最小値を求める / MIN
・最大値を求める / MAX
・最頻値を求める / MODE
・中央値を求める / MEDIAN
・最大値、最小値から指定の範囲を除いた平均値を求める / TRIMMEAN
・分散を求める / VARP
・標準偏差(標本集団)を求める / STDEV
・四分位点を求める / QUARTILE
・度数分布を求める / FREQUENCY
・正規分布の確率密度・累積分布を求める / NORMDIST
・尖度を求める / KURT
・歪度を求める / SKEW
・データが入力されているセルの数(標本数)を求める / COUNT
・データが入力されていない空白セルの数を求める / COUNTBLANK
・で条件に一致するセルの数を求める / COUNTIF
・指定した順番目に大きい値を求める / LARGE
・指定した順番目に小さい値を求める / SMALL
・順位を求める / RANK
・共分散を求める / COVAR
・相関係数を求める / CORREL
・組合せを求める / COMBIN
・順列を求める / PERMUT
・値(例えば販売数量とか)を予想する / FORECAST
・重回帰分析で値を予測する / TREND


6. エンジニアリング関数

物理量の単位を変換する / CONVERT

値がしきい値以上かどうかを求める / GESTEP

2つの値が同じだったら“1”そうでなかったら“0”を表示 / DELTA


7. 日付と時刻の関数

指定された日付のシリアル値を求める / DATE

開始日から起算して、指定した月数だけ前か後の日付に対するシリアル値を求める / EDATE

開始日から起算して、指定した月数だけ前か後の月末日を求める / EOMONTH

日付を表す文字列をシリアル値に変換する / DATEVALUE

日付データから“年”を取り出す / YEAR

日付データから“月”を取り出す / MONTH

日付データから週番号(第何週目か)を求める / WEEKNUM

日付データから“日”を取り出す / DAY

日付データから“曜日”を求める / WEEKDAY

“年”、“月”、“日”の整数値から日付データ(シリアル値)を求める / DATE

時刻データから“時刻(整数)”を求める / HOUR

時刻データから“分(整数)”を求める / MINUTE

時刻データから“秒(整数)”を求める / SECOND

“時”、“分”、“秒”の整数値から時刻データ(シリアル値)を求める / TIME

開始日から終了日までの期間(年、月、日)を求める / DATEDIF

開始日から終了日の指定範囲の土日を除いた日数(稼働日)を求めます / NETWORKDAYS

文字列の時刻から時刻データ(シリアル値)を求める / TIMEVALUE

1年を360日として支払の計算に使用される2つの日付の間の日数を求める / DAYS360

現在の日付を求める / TODAY

現在の時刻を求める / NOW


8. 検索/行列

セルの位置や範囲をテキストで参照させる / INDIRECT

関数の引数の範囲を設定する / OFFSET

1行、1列の検索から指定した行、列の値を表示する(検索対象の左側の列の値を表示させる)LOOKUP

列方向の検索 / VLOOKUP

指定した番号から引数のリストの値を求める / CHOOSE

行番号を求める / ROW

列番号を求める / COLUMN

列数を求める / COLUMNS

検索対象の位置を数値で表示させてみた / MATCH

指定した行と列の交差したセルの値を求める / INDEX

行と列を入れ替えてリンクさせる / TRANSPOSE


8. データベース関数

指定された列を検索し条件を満たすレコードの平均値を求める / DAVERAGE

指定された列を検索し条件を満たすレコードの最大値を求める / DMAX

指定された列を検索し条件を満たすレコードの最小値を求める / DMIN

条件表を満足するデータの合計を求める / DSUM

指定された列を検索し条件を満たすレコードの空白ではないセルの個数を求める / DCOUNTA

指定された列を検索し条件を満たすレコードの数値が入力されているセルの個数を求める / DCOUNT

列から指定した条件を満たす一つの値を抽出する / DGET

指定された列を検索し条件を満たすレコードの特定のフィールドの値の積を求める / DPRODUCT

指定された列を検索し条件を満たすレコードを母集団の標本と見なして、母集団に対する標準偏差を求める / DSTDEV

指定された列を検索し条件を満たすレコードを母集団全体と見なして、母集団の標準偏差を求める / DSTDEVP

指定された列を検索し条件を満たすレコードを母集団の標本と見なして、母集団に対する分散を求める / DVAR

指定された列を検索し条件を満たすレコードを母集団全体と見なして、母集団の分散を求める / DVARP


9. 情報関数

奇数か偶数かを求める / ISODD、ISEVEN

空白セルかどうか調べる / ISBLANK

セルがエラーかどうか調べる / ISERROR

セルのいろいろな情報を表示する / CELL
posted by haku1569 at 14:49| Excel | このブログの読者になる | 更新情報をチェックする

2014年04月20日

日付から「年月」を作成する/Excel

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

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

ここで「=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関数で日付データから“月”を取り出してみた
ピボットテーブルで日毎のデータを年月で集計してみた(グループ化)
posted by haku1569 at 14:12| Comment(0) | Excel | このブログの読者になる | 更新情報をチェックする

日付に曜日を表示させる/Excel

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

セルの書式設定画面を開きます。
予め用意されている日付の設定には無いので「ユーザー定義」を選択します。
「種類(T)」のボックスに日付の表示の後に「(aaa)」と入力するとカッコの中に曜日が表示されます。
201404020_02.JPG
posted by haku1569 at 13:31| Comment(0) | Excel | このブログの読者になる | 更新情報をチェックする

2009年09月29日

すべてのページにタイトル行を印刷する / Excel

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

例えば、こんな長い表があります。(この画像では全然長くないけどね。。)
20140426-12.jpg

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

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

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

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

すると、このように2ページ目以降も指定した行が印刷されます。
20140426-16.jpg
posted by haku1569 at 21:10| Comment(0) | Excel | このブログの読者になる | 更新情報をチェックする

2009年09月27日

数式バーを表示、非表示にする / Excel

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

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

メニューの「表示」のリボンの「表示/非表示」に、「数式バー」のチェックボックスがありますから、ここのチェックを外すと表示されなくなります。
20140426-22.JPG
posted by haku1569 at 19:19| Comment(0) | Excel | このブログの読者になる | 更新情報をチェックする

2009年09月24日

クリップボードを使いこなす / Excel/ホーム

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

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

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

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

「貼付け」をクリックすると「バナナキャンデー」が貼りつきました。
20140426-64.JPG
posted by haku1569 at 18:33| Comment(0) | Excel | このブログの読者になる | 更新情報をチェックする