Excel: へなちょこOLのおもちゃ箱

TOP

2018年05月14日

【Excel】移動平均

移動平均とは

Wikipediaより引用
　時系列データ(より一般的には時系列に限らず系列データ)を平滑化する手法である。

平滑化とか小難しいよ!!
ざっくり言うと、平均を求める範囲を移動させていく、ということです。

例えばこんなデータで、過去3日の移動平均を出す場合

　5/3の移動平均値は、5/1~5/3の平均
　5/4の移動平均値は、5/2~5/4の平均
　5/5の移動平均値は、5/3~5/5の平均

株価とか、その日時点での平均値を求めたい場合に便います。

準備

移動平均を自動生成する場合、アドイン機能を使うため、設定が必要です。

?@[ファイル]→[オプション]→[アドイン]→[設定]

?A｢分析ツール｣にチェックを入れて｢OK｣
　※｢分析ツール｣は初期設定はオフです
　※Excelそのものの設定なので、一度オンにすれば、再起動してもオンのままです

サンプル

過去3日間の平均値と売上のグラフを作ってみる

?@｢データ｣タブ→[データ分析]

※｢分析ツール｣にチェックを入れていないと、[データ分析]ボタンは表示されません

?A[移動平均]を選択し、[OK]

?Bイロイロ指定する

入力範囲：移動平均を求めたいすべてのデータ範囲

先頭行をラベルとして使用：入力範囲にフィールド名(タイトルとか項目)が含まれている場合はチェックオン

区間：移動平均を出したい間隔
　　　　今回は3日間ごとで出すので、3

出力先：移動平均値が生まれる範囲
　　　　　ここにデータが含まれていると、下記メッセージが出ます
　　　　　上書きするかは、状況次第です

グラフ作成：チェックを付けると、移動平均を求めたうえ、グラフを自動生成してくれます

?C移動平均が出力される

　自動生成されたデータは、AVERAGE関数が入っています。
　5/1と5/2は過去3日のデータがないため、#N/Aになります。
　#N/Aや端数処理が必要な場合は、生成されたデータを編集してください。

グラフのカスタマイズ

●凡例の編集
　?@プロットエリアを右クリック→[データの選択]

　?A凡例名を変更したい凡例を選択し、[編集]

　?B系列名に、凡例に表示させたいものを入れる
　　　元のデータの項目名は変えたくないけど、凡例は変えたい場合は直接入力でも可
　　　セル参照でもOK

　?C｢OK]

●横(X)軸の表示変更
　?@プロットエリアを右クリック→[データの選択]

　?A横(項目)軸ラベルの[編集]

　?B軸ラベルの範囲に、横軸に表示させたいセル範囲を指定

●凡例の位置
　?@凡例を右クリック→[凡例の書式設定]

　?A右以外を選択

　?B凡例をドラッグでお好きな位置に移動

　凡例を右上とか右下とかに移動すると、グラフエリアいっぱいにプロットエリアを広げられます

軸ラベルを削除して、グラフタイトルを変更して、こんなカンジ

　あとはお好みで

作ってみて思ったこと

自動生成されるグラフは、自動で2軸にしてくれるのは便利♪
でもラベルとかが自分で直さなきゃいけないのはめんどくさい。
どうせならそこまでちゃんとやってくれればいいのに。

値を出すだけで、計算方法がわかっていれば、へなちょこ的は分析ツールアドインは使わないかな(笑)

タグ： Excel

posted by へなちょこ at 06:00 | Comment(0) | TrackBack(0) | Excel

2017年11月28日

【Excel】入力規則で、特定の文字列と1以上の整数しか入力できないようにしたい

お題

入力規則のリストや数値指定を使っている方も多いと思いますが、｢Nothingという文字列と1以上の整数しか入力できないようにしたい｣というご要望があったので、メモ。

入力規則はユーザー設定にすると数式を入れることができるので、複数条件が可能となります。
式の結果がTRUEの時だけ入力が受け付けられ、FALSEの場合はエラーを出し入力を拒否します。
なので、ユーザー設定の数式はTRUEかFALSEが返るような式である必要があります。
IF関数の第一引数(条件)を入れれば良いのです。

実装

1.入力規則の入力値の種類を｢ユーザー設定｣にします。

2.一番下に数式を入力する欄が表示されるので、ここに式を入れます。

=IF(ISNUMBER(A1),AND(MOD(A1,1)=0,A1>0),A1="Nothing")

　※上記式の｢A1｣と、設定をしたいセル番地を置き換えてください。

3.今回は、数値もしくは｢Nothing｣のみ、つまり半角英数でしか入力させたくないので、｢日本語入力｣タブで[無効]にします。
　この設定をしたセルに入力カーソルを合わせたとき、IMEはオフになり、[半角/全角]キーを押しても全角入力にすることができなくなります。
　[オフ(英語モード)]だと、入力カーソルを合わせたときはIMEはオフになりますが、[半角/全角]キーを押すと全角入力に変わります。

尚、入力規則の数式に入力間違いや論理エラーが合っても、教えてくれませんので、まずはセルに式を入れてみて、自分の要望通りの答えが返るか確認したほうが良いです。

MOD関数について

MOD関数は余りを返す関数です。
プログラム言語には余りを返す演算子があったりしますが、Excelはそういう演算子はなく、MOD関数しかありません。
今回は、整数かどうかを判断したいのですが、Excelには整数か判断させる関数がないので、MOD関数を応用します。

=MOD(対象,割りたい数値)

1で割ったとき、割り算としての答えは元の数値と同じですが、MODの返り値は余り、すなわち小数部分を返します。
なので、MODの返り値がゼロのときは元の値は整数、ゼロ以外だったら元の値は整数ではない、という判断ができます。

第二引数(割りたい数値)がゼロの場合は、#DIV/0!になるので、引数をセル番地にするときはご注意ください。

ちなみに整数商を求めたいときは、QUOTIENT関数を使います。

ざっくり説明

単純に、

=OR(AND(ISNUMBER(A1),MOD(A1,1)=0,A1>0),A1="Nothing")

とした場合、入力した値が文字列だった場合、MODや>(比較)ができないためエラーとなります。

なので、最初にIF関数で数値か判断し、数値の場合は整数かつ0より大きいかを判断し、数値でない場合は｢Nothing｣と一致するか、という式にしました。
1以上はA1>0でもA1>=1でもどちらでも良いです。
ややこしい場合は、[fx]ボタンをクリックして関数ボックスを表示させるとわかりやすいです。

よくANDとORの使い分けが難しい、と聞きますが、自分の要望を文章にしてみるとわかりやすくなると思います。

posted by へなちょこ at 06:00 | Comment(0) | TrackBack(0) | Excel

2017年10月20日

【Excel】SUM関数とセル番地+セル番地の違い

Excelで合計を出すとき、SUM関数を使いますか？
セル番地+セル番地で出しますか？

ちょっと前に｢足し算にエラーが出る!!｣と相談を受けて説明したのですが、意外と知らない人が多いようなので、書いてみます。

どう違うの？

この場合、結果はどちらも30です。

ではこちらは？

2行目は、文字が入っているため、#VALUE!になります。
※#VALUEについてはコチラ

人間だったら、数字に文字を足すなんてできないのはわかっているから文字を無視して10という答えを出せますが、パソコンは勝手に気の利かせてくれるなんてしてくれません。(されたら困る)
ワイ、無理ぽ(　ﾟдﾟ)と言われちゃいます。

では、なぜSUM関数の場合、#VALUE!にならず30が返るのでしょうか？

SUM関数でエラーが出ない理由

SUM関数の ~~わかりずらい~~ ヘルプを見ると

参照先のセルに数値以外 (テキスト) の値がある場合、数式が破損し、#VALUE! エラーが返される場合があります。 SUM はテキスト値を無視して、数値だけを合計します。

と書いてあります。

そう、SUM関数は気の利く子なんです!!
SUM関数は、セルの値をチェックして、数字だけを拾って合計します。
セル番地+セル番地は単純な四則演算のみで、セルの値のチェックまではしてくれません。

さらに言うと、Excelはセル参照は遅いという弱点があります。
セル番地+セル番地だと、いちいちどのセルにどんな値が入っているかを見にいきます。
対してSUM関数の引数は配列です。
(配列とは：超ざっくり言うと、内部メモリに保持した値。内部メモリから値を取得するので、セル参照より処理が速い)

画像のようなちょっとしたものだったら全然わかりませんが、何万行もあるデータだと再計算の処理時間が体感できるくらい変わることもあります。

で、どっちがいいの？

どちらも正解です。
例えば、システムから落としてきたようなデータ等絶対に数字!というデータだったらパフォーマンス優先でSUM関数がオススメです。

ですが、人間が手入力しているデータ等は、気を付けていても文字が入ってしまうこともあります。
その場合、SUM関数だとエラーが出ないので、正しく入力できていないことに気づけなかったり。
あえてエラーを出すほうが良いときもあったりします。

この特性を知っておけば、使い分けができますね♪
これはどのバージョンでも共通です。(今のところ)

へなちょこが扱うデータはこーゆーのが多いです。

B列がブランクだと入力モレなのか本当にゼロなのかがわからないので、｢無し｣と入力してもらっています。
お金に関わることだから、後で入力モレが発覚すると困るんです。
最終的にはC列の合計を集計するので、セル番地+セル番地だと｢無し｣の場合、C列は#VALUE!になってしまうので集計されなくなってしまいます。
｢無し｣の場合でも合計の値がほしいので、SUM関数を使っています。
※サンプルなので2列しかありませんが、実際のデータは列数も行数も多いので、目視とか無理

小技

1.離れたセルをSUMしたかったら

=SUM(A1,C1)

というように、セル番地をカンマで区切ればおｋ。

2.SUM関数を挿入するショートカットキー
　セルを選択した状態で(セル内にカーソルがあっちゃだめ)

←同時に押す

　自動的に=SUM()が挿入され、隣接する連続した範囲が引数に入ります♪
　データが飛び飛びだと自分の希望の範囲じゃなかったりしますが、自動的に入力フォーカスがセル範囲になるので、そのままドラッグで範囲選択したり、手入力すれば早いです。

SUM関数ってよくスキルチェックで判断材料に使われることが多いので、初心者向けな関数だと思われがちですが、実はとっても優秀なんです。
合計を出すだけなら、IF関数をネストしまくってエラー回避する必要なんてありません。
SUM関数を使うだけで、#VALUE!回避になるんです。
へなちょこが勝手に思う、優秀関数第2位です。

タグ：関数 Excel

posted by へなちょこ at 06:00 | Comment(0) | TrackBack(0) | Excel

2017年10月16日

【Excel2016】[F1]ヘルプを抹殺する

問題点

Excelでの[F2]キーはセル内編集。
1日に何十回と押すキーですが・・・、なぜ隣にヘルプがいる??
ヘルプが起動されるまで待たされ続けて早XX年。

・・・殺るか。

操作方法

レジストリ操作です。

Microsoftからの警告
　レジストリエディタの使い方を誤ると、深刻な問題が発生することがあります。最悪の場合、オペレーティングシステムの再インストールが必要になることがあります。マイクロソフトは、レジストリエディタの誤用により発生した問題に関しては、一切責任を負わないものとします。レジストリエディタは、自己の責任においてご使用ください。

｢レジストリってなーに?｣｢レジストリエディタってどうやって起動するの?｣って方はあきらめてください。
へなちょこも責任取れません。

1.下記キーを作成

\HKEY_CURRENT_USER\Software\Policies\Microsoft\office\ 16.0 \excel\DisabledShortcutKeysCheckBoxes

※16.0は、Officeのバージョンによって異なります
　バージョン13.0はありません

バージョン	キー
2003	11.0
2007	12.0
2010	14.0
2013	15.0

2.1のキーに、[新規]→[文字列値(S)]

名前	種類	データ
何でも良い	REG_SZ	112,0

[F1]キーの呪縛から、今、解き放たれる。

※personalのブックオープンイベントでも無効化できますが、へなちょこのpersonalはカオスってるので今回はやりません。
　そのうち気が向いたら書くかも。

タグ： Excel カスタムレジストリ

posted by へなちょこ at 06:00 | Comment(0) | TrackBack(0) | Excel