2017年07月30日

参照元のトレースでシートの計算式をチェックしてみた(一括チェックも)/ Excel / ワークシート分析

Excelでワークシートの計算式の参照元や参照範囲を「ワークシート分析」の「参照元のトレース」を使ってチェックしてみます。

長い間使いまわししている、ワークシートや他の人が作ったワークシートの計算式の参照元や参照範囲が何かの拍子でズレたり、範囲が変わってしまったりすることってたまーにあります。
それを知らずに使っていた大変なことになってしまってからでは時既に遅し!
と、言うことで事前にチェックするようにしましょう。

どうするかと言うと、、、
こんな、家計の月毎の集計表があります。
一番下の行には毎月の合計、右端の列には各項目の合計が計算されていて、右下のセルには総合計が計算されています。
20170729_01.JPG

右下のセルを選択します。
“列O”の合計が“SUM関数”で計算されています。
20170729_02.JPG

ここでメニューの「数式」、「ワークシート分析」から「参照元のトレース」を選択します。
20170729_03.JPG

すると、“列O”の範囲が青枠で囲まれて(図だと分かり辛くてスミマセン(;'∀'))矢印がひかれました。
総合計のセルの参照範囲が見て分かるようになりました。
20170729_04.JPG

で、更に「参照元のトレース」を選択すると、今度はその“列O”の各項目の合計の参照範囲が同じ様に表示されました。
20170729_05.JPG

ちなみに、矢印を消去するには「トレース矢印の削除」をクリックすると消えます。
20170729_06.JPG

で、12月の合計のセルを選択して
20170729_07.JPG

「参照元のトレース」を選択するとこの様になります。
20170729_08.JPG

と言うわけで、シートの中の計算式が入力してあるセルを一つ一つ選択して「参照元のトレース」を行うのは面倒くさいんで、何か一括で全ての計算してあるセルの参照元のトレースが出来ないかとググっていたらありました。裏技が、、、

複数のサイトで同じやり方が載っていたので、元々考案した人がどなたか分かりませんが、ここからは引用です。

まず、どこか適当なセルに“=”を入力します。
で、まだ「enter」は押しません。
20170729_09.JPG

で、そのまま“列A”と“行1”の角をクリックしてワークシート全体を選択します。
すると、先ほどのセルは“=1:1048576”となります。
20170729_10.JPG

ちまり、このセルはワークシート全体を参照元にしたということになります。
なので、このセルの参照元をたどれば、ワークシートに含まれる計算式が入力されたセルの参照元が辿れるということになるわけです。
これを考えた人、あっタマいいなー。。。
まずこのセルをせんたくして、
20170729_11.JPG

「参照元のトレース」をクリックします。
20170729_12.JPG

で、ここからです!
もう一度「参照元のトレース」をクリックします。
と、こんな感じになりますした。
20170729_13.JPG

がー、、、ごちゃごちゃしすぎて分かり辛いですねー。。。
試しにわざと間違えた式にしてみます。
1月の合計を“家賃”から“教育費”までにして、“交通費”と“医療費”が外れた状態にしてみます。
さて、これを発見することが出来るでしょか?
20170729_14.JPG

( ゚Д゚)ハァ?
これじゃ、さっきと一緒!漏れてるのがわかりません!
20170729_15.JPG

1月だけトレースしてみると分かりますが、
20170729_16.JPG

ここに、医療費の合計のトレースも入るともう分からなくなってしまいます。
こんなクロス集計の場合は分かり辛いですね。。。
20170729_17.JPG

一つ一つ確認しなければならないのなら、「F2」キーでもよさそうですね。。
20170729_18.JPG

用途に応じて上手く使い分けして、計算式の参照が正しいか確認しながら使いましょう!!




関連ページ
posted by haku1569 at 15:41| Excelデータ分析 | このブログの読者になる | 更新情報をチェックする

2016年10月23日

数式パレットを表示させてみた / Excel / 関数

Excelで関数を入力する時にメニューの「数式」の「関数ライブラリ」から関数を選択すると「数式パレット」が開き引数等の入力の簡単な説明等が表示されたり、ヘルプのリンクが貼られていたりと使い慣れていない関数を使用する時に色々便利なのだけど、、、
関数ライブラリ以外から関数を挿入する時の「数式パレット」の表示をいろいろ試してみました。

1.関数の挿入を使用した時
2.既に関数が入力されている時
3.手入力で関数を入力する時


1.関数の挿入を使用した時
「数式バー」の「fx」(関数の挿入)ボタンを押します。
20161023_01.JPG

または、メニューの「数式」から「fx(関数の挿入)」を押します。
20161023_02.JPG

「関数の挿入」画面から関数を選択します
20161023_03.JPG

「数式パレット」が開きます
20161023_04.JPG

2.既に関数が入力されている時
関数が入力されているセルを選択します。
20161023_05.JPG

そこで「数式バー」の「fx」(関数の挿入)ボタンを押すと「数式パレット」が開きます。
20161023_06.JPG

3.手入力で関数を入力する時
手入力で関数を入力していきます。
途中まで入力すると候補の関数が表示されます。
20161023_07.JPG

そこから選択するか、さらに手入力して“(”まで入力します。
20161023_08.JPG

そこで「数式バー」の「fx」(関数の挿入)ボタンを押します。
20161023_09.JPG

すると「数式パレット」が開きます。
20161023_10.JPG

結論は、
関数が“(”まで入力されている時に「fx」(関数の挿入)ボタンを押すと数式パレットが開くことが分かります。



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

2016年04月17日

幾何平均(相乗平均)とは? / データ分析の解説

普通に「平均」というと「算術平均(相加平均とも言います)」の事を言いますが、他に「幾何平均(相乗平均)」と「調和平均」と言うのがあります。
今回は「幾何平均(相乗平均)」についてまとめてみます。

例えば、会社の売り上げが2014年は前年(2013年)の5%アップ(1.05倍)、2015年は前年(2014年)の10%アップ(1.1倍)でした(好調ですね!)
この時、2014年と2015年の2年間の平均アップ率は何%か?

これを、単に算術平均で、
(1.05+1.1)÷2=1.075(7.5%アップ)
でいいかと言うと。。。?

実際に計算してみましょう。
2013年の売上を100万円としてみましょう。
2014年は5%アップなので、
1,000,000円x1.05=1,050,000円
2015年はその10%アップなので、
1,050,000円x1.1=1,155,000円になります。

では、算術平均で求めた年平均7.5%で計算してみると?
2014年は、
1,000,000円x1.075=1,075,000円
2015年は、
1075,000円x1.075=1,155,625円
になってしまいました。間違いですね!

とこのような時に幾何平均(相乗平均)Geometric Meanを用います。
幾何平均とは、掛け算の積の平均のことで、算術平均の時は足し算の平均で足し算の和を値の数nで割りましたが、幾何平均は掛け算の積を累乗根(n乗根)します。
式で書くとこうなります。
20160417_01.jpg

先ほどの例題を解いてみると、
√(1.05x1.1)=1.07470926301(かなり細かいですが。。。(*_*;)
2014年は、
1,000,000円x1.07470926301=1,0747,092.6301円
2015年は、
1,0747,092.6301円x1.07470926301=1,155,000円
になりました!

幾何学的に解説すると、2つの値の幾何平均は同じ面積の正方形の1辺、3つの値の幾何平均は同じ体積の立方体の1辺の長さを求めることと同じになります。
20160417_02.jpg
こうしてみると、これが平均になることが理解しやすくなりますね。

次回は、3つ目の平均、調和平均についてまとめてみましょう!


バックナンバー
1.平均値と代表値(特性値)


関連ページ
算術平均(相加平均)と加重平均の違いは?「重み」ってナニ!?
中央値(median / メジアン)についてまとめてみた
データ分析の解説/メニュー
posted by haku1569 at 19:24| Excelデータ分析 | このブログの読者になる | 更新情報をチェックする

2015年08月15日

フィルタで抽出したセルに「形式を選択して貼り付け」をしてみると!? / Excel / 一般

Excelで列にフィルタを適用させて抽出したセルに「形式を選択して貼り付け」でデータを貼り付けると、何故だかは分かりませんが、抽出されている行の間の表示されていない行のセルにも値が貼り付いてしまいます。

大量の件数のあるワークシートでうっかりこのようなミスをしてしまうと取り返しのつかない状態になってしまいます。

まず、こんなシートがあります。
この記号が"A"の値に"10"を貼り付けてみます。
20150809_01.JPG

「記号」のフィルタボタン(▼)を押して、"A"だけをチェックして「OK」します。
20150809_02.JPG

すると、"A"だけの行(レコード)が抽出されました。
20150809_03.JPG

普通に貼り付けてみる
そこで、最初の「値」のセルに"10"を入力します。
20150809_04.JPG

で、そのセルを選択して右クリし「コピー」をします。
20150809_05.JPG

んで、下の2つのセルを選択して「貼り付け」をします。
20150809_06.JPG

と、こんな風に貼り付けが出来ました。
20150809_07.JPG

またフィルタボタンを押して「"記号"からフィルタをクリア」します。
20150809_08.JPG

すると、"A"のレコードだけ"値"に"10"がコピーされました。
20150809_09.JPG

形式を選択して貼り付けしてみる
では次に、同じように"10"をコピーして下の2つのセルを選択してから、「貼り付け」ではなく「形式を選択して貼り付け」を選択します。
20150809_10.JPG

「形式を選択して貼り付け」の画面から、貼り付け方法を選択します。
ここでは「すべて」のラジオボックスを入れていますが、実際「形式を選択して貼り付け」を選択する場合は、「数式」や「値」のケースが多いのではないかと思います。
が、いずれも結果は同じなので。。。
20150809_11.JPG

と"A"に"10"が貼り付けられました。
20150809_07.JPG

で、「"記号"からフィルタをクリア」してみると、
あれれ!?
2番目の"A"から3番目の"A"までの全て"10"が貼り付けられてしまいました!!
20150809_13.JPG

フィルハンドルで貼り付けてみる
フィルハンドル(セルの右下角の小さい■)をドラッグして貼り付けてみると?
20150809_14.JPG

"A"に"10"が貼り付けられて、、
20150809_15.JPG

フィルタをクリアしてみると、
問題なく"A"だけに貼り付けが出来ています。
20150809_09.JPG

つまり。。。

フィルタで抽出されているセルに「形式を選択して貼り付け」を行うと、抽出されている行の間の表示されていない行のセルにも値が貼り付いてしまいます。。。
くれぐれもこんな貼り付けをしないように!

予告編
では、次回は「非表示」にした行には貼り付けられないようにする「可視化セル」の選択について紹介します。この方法を使えば、フィルタで抽出した(見えている)行だけを間違いなく選択範囲にすることが可能です。

関連ページ
オートフィルで連続データが入力できない!?
抽出したセルに連続数を入力してみた(可視化セルにオートフィルをかける)
posted by haku1569 at 19:03| Excelデータ分析 | このブログの読者になる | 更新情報をチェックする

2015年07月26日

Ctrl + ] でセルを数式で参照しているセルを選択してみた / ショートカット / Excel

Ctrl + ] で指定したセルを数式で参照しているセルを選択してみます。
20150726_00.jpg

例えば、こんなデータがあります。
20150726_01.JPG

"D3"と"E4"には"123"と入力されているのではなく、"B3"の値を数式で参照しています。
20150726_02.JPG

"B3"を選択します。
20150726_03.JPG

Ctrl + ] を押します。
20150726_05.JPG

と、"B3"を数式で参照している"D3"と"E4"が選択されました。
20150726_04.JPG


関連ページ
ショートカット一覧
posted by haku1569 at 16:16| Excelデータ分析 | このブログの読者になる | 更新情報をチェックする

2015年07月12日

Ctrl + Space で選択範囲を列全体に広げてみた

Ctrl + Space でセルの選択範囲を列全体に広げてみます。

20150712_00.jpg

ひとつのセルが選択されています。
20150712_02.JPG

Ctrl + Space を押します。
20150712_01.JPG

と、選択範囲が列全体に広がりました。
20150712_03.JPG

因みに、この選択範囲を解除するには Shift + Backspace を押します。


関連ページ
ショートカット一覧
posted by haku1569 at 19:10| Excelデータ分析 | このブログの読者になる | 更新情報をチェックする

2015年07月05日

Ctrl + Shift + ^ で表示形式を標準に戻す/ショートカット/Excel

Ctrl + Shift + ^ でセルの表示形式を標準に戻します。
20150705_00.jpg

表示形式を標準にしたいセルを選択して、、、
20150705_02.JPG

Ctrl + Shift + ^ を押します。
20150705_01.JPG

と、何の設定もされてないプレーン(?)な標準の表示になりました。
20150705_03.JPG


関連ページ
ショートカット一覧
posted by haku1569 at 18:44| Excelデータ分析 | このブログの読者になる | 更新情報をチェックする

2015年03月14日

これは便利!異なる名前の複数のシートを自動で作ってみた /Excel/ピボットテーブル

例えば、日付の名前の複数のシートを作ったり、社員の氏名毎のシートを作ったりとかあると思うのですが、予めシート名の一覧表を作っておけば自動で名前のついたシートを自動作成させることができます!
こんな感じ。
20150313_212.JPG

ピボットテーブルを使うのですが、今回はシートの自動作成が目的なのでピボットテーブルを使わない、使ったことがまだない、Excel初心者でも出来るような初歩的な解説をします。ピボットテーブルを使いこなしている人にはまどろっこしいかも知れませんが、応用の場面を考えながら見てください。

3月の日付毎のシートを作ってみます。
まず、"日付"と入力したセルの下に"3/1"と入力します。
20150313_201.JPG

「Enter」キーを押すと、日付と判断されて"3月1日"と表示されます。
そのセルの右下にマウスを合わせるとカーソルが"+"に変わります。
20150313_202.JPG

それをドラッグしたまま下に引っ張るとオートフィルで日付が連続で増えていく表示が現れます。
20150313_203.JPG

それを、3月31日になるまで引っ張って放すと3月31日まで日付の入力ができます。
20150313_204.JPG
あっ、ちなみに書式の設定で曜日も表示させることもできますよ。

3月1日のセルを選択して「挿入」、「ピボットテーブル」を選択します。
20150313_205.JPG

「ピボットテーブルの作成」画面が開きます。
何も変更せず「OK」します。
20150313_206.JPG

すると、日付を入力した「Sheet1」の前に新しいSheetが挿入されてピボットテーブルのシートが出来上がりました。
20150313_207.JPG

そこで、シートの右側の「ピボットテーブルのフィールドリスト」の"日付"を"レポートフィルタ"の中にドラッグ&ドロップします。
20150313_208.JPG

すると、こんな風なピボットテーブルが出来ました。
20150313_209.JPG

ピボットテーブルの"日付"を選択して「ピボットテーブルツール」、「オプション」、「ピボットテーブルオプション」から「オプション」の右側の▼を押して「レポートフィルタページの表示」をクリックします。
20150313_210.JPG

「レポートフィルタページの表示」画面が開きます。
そのまま「OK」します。
20150313_211.JPG

すると、あら不思議!?自動でシートが出来ちゃいました!!
20150313_212.JPG

この全てのシートにはそれぞれの日付のレポートフィルタのピボットテーブルが出来ています。シートを作るだけであればこのピボットテーブルは不要なので削除するのですが。。。
これが、すんなりと削除させてくれません。ので、、、
"3月1日"のシートの名前の上で右クリし「すべてのシートを選択」を選択します。
20150313_213.JPG

すると、シートの名前のタブがすべて選択状態になり白くなりました。
20150313_214.JPG

そこで、シートの左上をにカーソルを合わせると、白十字に変わります、そこでクリックするとシート全体が選択されます。
20150313_215.JPG

そこで右クリし「コピー」を選択します。
20150313_216.JPG

さらに右クリし「形式を選択して貼り付け」を選択します。
20150313_217.JPG

「形式を選択して貼り付け」画面が開きます。
「値」のラジオボタンを押して「OK」します。
20150313_218.JPG

すると、ピボットテーブルが無くなり表示だけがのこります。
20150313_219.JPG

その状態(シート全体が選択)のままで「Delete」キーを押して削除します。
これで、何もないシートができました。
20150313_220.JPG

最後に、シートの名前の上で右クリし「作業グループの解除」を選択し、すべてのソートの選択を解除してやれば完了です!
20150313_221.JPG

ピボットテーブルをまだ使ったことのない人はぜひこの機会に使ってみてください。Excelのスキルがレベルアップしますよ!


関連ページ
ピボットテーブルを作ってみた(更新や、用語の説目も)
レポートフィルタで特定のアイテムのデータを抽出してみた
posted by haku1569 at 18:49| Excelデータ分析 | このブログの読者になる | 更新情報をチェックする

2015年02月14日

ピボットテーブルで層別のヒストグラム(度数分布グラフ)を作ってみた/Excel/ピボットテーブル

さて、前回はピボットテーブルを使ってヒストグラム(度数分布グラフ)を作ってみましたが、今回は更に分析する為に"層別のヒストグラム"を作ってみます。
層別のヒストグラムについてはコチラの解説をご覧ください!

前回に引き続き、サンプルデータは機械加工品の寸法検査データです。
前回と違うところは、加工した機械を記録した"機械"と言うフィールドが追加されています。
20150214_01.JPG

前回の方法でグループ化を使ったピボットテーブルで度数分布表を作るとこうなります。
20150214_02.JPG
単にピボットテーブルを作ってしまうと、データの無い区間間隔が表示されなくなり、正しいヒストグラムが作成されなくなりますので忘れないで「データの無いアイテムの表示」「空白セルに表示する値」の設定をしてくださね!

ここで、フィールドリストを見ると、"機械"というフィールドがあります。
20150214_03.JPG

これを、"列ラベル"にドラッグします。
20150214_04.JPG

すると、ピボットテーブルは"機械A"と"機械B"の2つのフィールド毎の度数が集計されました。
20150214_05.JPG

で、これをグラフにすると"機械A"と"機械B"の層別のヒストグラムが出来上がりました。
20150214_06.JPG


バックナンバー
ピボットテーブルで度数分布表、ヒストグラムを作ってみた

関連ページ
統計分析の基本中の基本、度数分布表についてまとめてみた
数値データの分布をみるヒストグラムについてまとめてみた
ヒストグラムを層別にしてみると!?
posted by haku1569 at 18:12| Excelデータ分析 | このブログの読者になる | 更新情報をチェックする

2015年02月11日

統計分析の基本中の基本、度数分布表についてまとめてみた/データ分析の解説

度数分布(Frequency Distribution)とは、、、
項目(アイテム・カテゴリ)やデータの値(値の範囲)にあるデータの個数(度数)の分布(Distribution)のことで、データのバラつき具合を把握することができます。
その集計表、度数分布表を作りグラフ化することでデータの分布の様子を視覚化(見える化)することができます。

項目(アイテム・カテゴリ)毎の度数分布(パレート図)
項目というのは数値以外のラベルのことで、商品名とか、人の名前とか、、、
例えば、商品毎の販売数量とか売上金額とか、営業マンごとの受注金額だったり、、そんな集計表になります。
それらをグラフにする場合は値の大きい順に項目を並べ替えた"パレート図"を用いるのが一般的になります。
それに基づいてABC分析が行われます。
20140906_00パレート図.jpg

データの値(値の範囲)毎の度数分布(ヒストグラム)
数値データの集計の場合は、その数値データを一定の範囲に区切りその中に当てはまるデータの個数を集計していきます。
そして、それをグラフにしたものを"ヒストグラム"といい、まさに分布の状態(バラつきの様子)を把握する為に用いられます。
20140506-07.JPG
で、このヒストグラムをみて正規分布かどうかなんてことが、評価されます。。。

では、次はヒストグラムについてもうちょっと掘ってみましょう!?

パレート図について知りたい人はこちらから。

バックナンバー

関連ページ
FREQUENCY関数で度数分布を求めてみた
Excelのグラフでパレート図を画いてみた
ピボットテーブル+ピボットグラフでパレート図を画いてみた
ピボットテーブルで度数分布表、ヒストグラムを作ってみた
データ分析ツールでヒストグラムを作ってみた
正規分布についてまとめてみた
データ分析の解説/メニュー
posted by haku1569 at 16:59| Excelデータ分析 | このブログの読者になる | 更新情報をチェックする