Access。切り上げ。ExcelではROUNDUP関数がありますがAccessにはありません。【ROUNDUP】

Access。切り上げ。ExcelではROUNDUP関数がありますがAccessにはありません。

＜Int関数＞

例えば、小数点第1位までで切り上げたいという時、ExcelではROUNDUP関数をつかいます。

Accessならどうしたらいいのでしょうか。

ROUNDUP関数をつかえば…と思うかもしれませんが、Accessには、切り上げをする関数は用意されていません。

つまり、ROUNDUP関数は ない というわけです。

Round関数は四捨五入の関数なので、コントロールができないので、Int関数をつかいます。

ただ、Int関数は整数化しますが、整数化するために、切り捨てる関数です。

そのため、Int関数で切り捨てる前に、切り上げておかないといけません。

クエリで演算フィールドをつくるのですが、次のような演算式を用意します。

小数第2位: Int([数値]*(10^2)+0.999999)/(10^2)

小数第1位: Int([数値]*(10^1)+0.999999)/(10^1)

整数: Int([数値]*(10^0)+0.999999)/(10^0)

一の位: Int([数値]*(10^-1)+0.999999)/(10^-1)

この演算フィールドの演算式の特徴は、Int関数を実行する前に、0.999999をわざと足しておくことがポイントです。

0.9だと、「押し上げ量が足りない」ケースが必ず出るからです。

逆に、0.999999999とかにすると、数値型（Double）を越えてしまう恐れがあります。

なので、やみくもに桁数を多くしてもダメです。

Accessで切り上げを行う場合には、「0.999999」が決まった値　として認知されています。

可用性 99.9999%を “six nines” と呼ぶのとなんか似ていますが、9が6個です。

では、クエリをつくって確認してみます。

ExcelのようにROUNDUP関数があれば、いいのですが、Accessにはありません。

切り上げを行いたい場合には、Int関数をつかって演算式を作る必要があります。

Excel。10:00に3.5時間を足した結果がおかしいのでどうしたらいいの。【time calculation】

Excel。10:00に3.5時間を足した結果がおかしいのでどうしたらいいの。

＜TIME+INT関数＞

次のような経過時間の表をつくってみたら、結果がおかしなことになっています。

A2に10:00と入力してあります。

B2には、3.5時間経過した時間が入力されています。

C2には、開始時間に経過時間を足した結果を表示したいので、

=A2+B2

という数式を設定したのですが、結果がおかしなことになっています。

またオートフィルで数式をコピーしたところ、C3も、おかしい結果になっています。

原因は、シリアル値という考え方が抜けていることです。

Excelでは、日付や時間をシリアル値という数値で管理しています。

C2をクリックして、数式タブにある、数式の検証をつかうとよくわかります。

数式の検証をクリックします。

検証ボタンをクリックすると、10:00が、0.41666…となっていることがわかります。

Excelが1日を1としたシリアル値で管理しています。

10時というのは、1/24で1時間なので、1/24*10ということになります。

この値に、単純に3.5を足しても、13.5にはならないことがわかりました。

時間計算は、常にシリアル値を考えないといけないわけです。

つまり、経過時間を次のようにすれば、いいことがわかりました。

経過時間の3.5を3:30と時間に変換することで、単位が揃い、終了時間が正しく求めることができたというわけです。

確かに、結果は求めることができました。

もし、当初のように、3.5としたままで、結果を求めるには、どのようにしたらいいのでしょうか。

TIME関数をつかって、時間に変更することで対応できます。

C2に設定した数式です。

=A2+TIME(INT(B2),(B2-INT(B2))*60,0)

B2を数値から時間に変換するので、TIME関数をつかいます。

TIME関数は、時・分・秒という引数が用意されています。

時は、3.5の3なので、小数点を除いた値をつくりたい。

そこで、INT関数で整数化します。

分は、時と逆で、小数点を求めたいので、B2から整数の値を減算すれば、小数点以下を求めたいので、先程の時で求めた値であるINT(B2)をB2から減算すれば、小数点だけが残ります。

残った小数点に60を掛けます。

60かけるのは、分だからです。

最後の引数の秒は不要ですが、無いとエラーになるので、「0（ゼロ）」を設定します。

これで、経過時間を足した終了時間を求めることができます。

Excel。INT関数は最も近い整数に切り下げる関数です。【INT】

Excel。INT関数は最も近い整数に切り下げる関数です。

【関数辞典：INT関数】

INT関数

読み方： イント　　

読み方： インテジャー

分類： 数学/三角 

INT(数値)

最も近い整数に切り下げる

Excel。同じ数だけの連続した日付をつくるには、オートフィルだけではダメなんです【consecutive dates】

Excel。同じ数だけの連続した日付をつくるには、オートフィルだけではダメなんです

＜DATE+INT+ROW関数＞

連続した日付を入力するには、オートフィルをつかうことで、手早く設定することができます。

ところが、次のような複数の同じ数だけの連続した日付を入力したい場合、オートフィルだけでは、対応することができません。

次の表のように、2日ごと3日ごとに同じ数だけの連続した日付を入力しています。

このような場合、数式をつかって対応します。

A2には、

=DATE(2024,5,INT(ROW(A2)/2))

という数式を設定します。

あとは、オートフィルで数式をコピーするだけで、日付を入力することができます。

数式を確認します。

DATE関数は、日付を作る関数です。

年と月は、今回直接入力しました。

日がポイントです。要するに、1をどのようにつくるのか、そして、2個1を用意することができるのかという点です。

そこで、INT+ROW関数で対応するというわけです。

INT関数は整数にする関数。

ROW関数は行番号を算出する関数です。

ROW(A2)なので、2。

それを2で除算するわけです。

結果は、2÷2なので、1ですね。

A3ならば、3÷2なので、1.5ですが、INT関数で整数化されるので、1ですね。

また、C2に設定した数式は、

=DATE(2024,5,INT(ROW(C3)/3))

設定したい日になるようにすれば、3日ごとでも4日ごとでもつくれます。

Excel。何時間後の時刻を、単純な和算で算出できないのでどうしたらいいの。【hours later】

Excel。何時間後の時刻を、単純な和算で算出できないのでどうしたらいいの。

＜TIME+INT関数＞

Excelは時間計算が苦手というか、少々考えないと算出できないものがあります。

例えば、何時間後の時刻を算出したい場合も、そのうちの一つです。

A列には、時刻が入力してあります。

B列には、処理時間が入力されていて、時刻にこの処理時間が経過した時間を終了予定時刻として、算出してあるのがC列です。

C2には、A2+B2としたいところですが、希望するように算出してくれません。

Excelは、日時について、「シリアル値」というのをつかっています。

1日を1としているのがシリアル値です。

なので、単純に処理時間を足してもダメというわけです。

なぜならば、1時間は1/24だからですね。

そこで、C2には、次の数式を設定すると、算出することができます。

=A2+TIME(INT(B2),(B2-INT(B2))*60,0)

B列の処理時間は、シリアル値を考慮して数式を作る必要があります。

TIME(INT(B2),(B2-INT(B2))*60,0)　

を確認しておきましょう。

シリアル値で考える必要があります。

A列が時間なので、B列も時間にしなければいけません。

時間を作ることができる関数が、TIME関数です。

TIME関数の最初の引数は「時」です。

1時間半を1.5というように入力することを考慮して、整数化するために、INT関数をつかっています。

INT(B2)とすることで、「時」を抽出することができます。

2つ目の引数が「分」です。

小数点を抽出したいので、B2の値から、INT(B2)として整数を減算すれば、小数点以下を算出することができます。

3.5-3=0.5

「0.5」が算出されるのですが、これをそのまま「分」としてつかえません。

1時間は60分なので、60を掛けてあげる必要があります。

そのため、「分」の引数を、

(B2-INT(B2))*60

としているわけです。

最後の引数は「秒」ですが、今回は、不要なので「0」と設定します。

これで、何時間後の時刻を算出することができます。

Excel。手早く指定行ごとの累計を算出するには、どうすればいいの。【Cumulative】

Excel。手早く指定行ごとの累計を算出するには、どうすればいいの。

＜SUM+OFFSET+INT+ROW関数＞

やりたいことはイメージできても、実際にそれを表現するのが難しいことがあります。

例えば、4行1組となっている表で、その4行1組ごとに累計を算出したい場合も、そのようなケースだと思います。

表のD列のように算出したいわけです。

最初に、D2の数式をご紹介します。

=SUM(OFFSET($C$2,INT((ROW()-2)/4)*4,0):C2)

この数式を、オートフィルでコピーするだけで、4行ごとに累計を算出することができます。

ただ、この数式だけでは、いったい何がどうなっているのか、わかりにくいので、説明していきます。

抑えておきたいのは、累計を算出する数式です。

D列に売上高の累計を単純に算出するならば、

=SUM($C$2:C2)

という始点を絶対参照にして、終点を相対参照のままにすれば、累計を算出することができました。

終点の相対参照は、オートフィルで自動的に参照がかわりますので、考えるのは、始点ということになります。

始点を4行ごとにするには、どうしたらいいのかを、考えてみましょう。

ポイントは4行おきに始点をずらしたいわけです。

「ずらす」というキーワードがでたら、OFFSET関数の登場だと思っていいでしょう。

今度は、OFFSET関数の引数を確認します。

OFFSET(参照,行数,列数,[高さ],[幅])

行数を4行おきに、「+1」すれば、うまくいきそうです。

列数は、ずらさないので、「0」

「高さ」と「幅」は、今回省略します。

あとは、どうやって「行数」をつくっていくかを考えます。

ここで、思い出したいのは、オートフィルをするということ。

オートフィルでずれた時に、変わるものといったら、行数です。

行数を算出するのは、ROW関数です。

行方向に何かするときには、ROW関数にお世話になります。

=SUM(OFFSET($C$2,INT((ROW()-2)/4)*4,0):C2)

この数式の、「INT((ROW()-2)/4)」を抽出したE列をつくってみました。

E2は、ROW関数で算出される行番号は「2」。

この値をデータの始点である2行目の「2」で減算します。

算出した値「0」を4行1組の組数の「4」で除算した値を、INT関数をつかって、整数化します。

最後に、「INT((ROW()-2)/4)」で算出された値に、「×4」する。

この4倍は、4組の「4」です。

そうすれば、OFFSET関数の行数として、つかうことができます。

色々な関数が登場しましたが、その中でも、OFFSET関数をつかうことで、今まで、手早く算出することができなかった帳票類も改善することができるかもしれませんね。

Excel。整数化するINT関数とTRUNC関数の違いはこうすれば、すぐにわかります。【Integerization】

Excel。整数化するINT関数とTRUNC関数の違いはこうすれば、すぐにわかります。

＜INT&TRUNC関数＞

Excelの関数には、同じような処理をする関数がチラホラあります。

例えば、整数にする関数にINT関数とTRUNC関数があります。

次の表で確認してみましょう。

B2の数式は、

=INT(A5)

と設定しています。

INT関数は、整数だけを抽出するので、A5の整数部分を算出しますので、120と算出されています。

C2の数式は、

=A5-B2

とすることで、小数部分を算出することができます。

B3の数式は、TRUNC関数をつかっています。

=TRUNC(A5)

TRUNC関数もまた、整数部分を算出する関数なので、120と算出されています。

C3の数式も、C2と同様に、減算した数式が設定されています。

=A5-B3

C2と同様に、小数部分を算出させています。

このように、INT関数をつかったとしても、TRUNC関数をつかったとしても、算出結果は変わりません。

では、なぜ、この二つの関数は存在しているのでしょうか？

それは、数値が「負数」のときに違いが発生するからです。

A5の値を「-120.34」と負数に変更してみます。

結果を見れば、一目瞭然。

TRUNC関数は、「-120」「-0.34」と算出されているのに対して、INT関数は「-121」「0.66」と違った数値を算出しまっています。

負数の場合、TRUNC関数は、0（ゼロ）に近い数値で算出するのですが、INT関数は、繰り上がる形で、0（ゼロ）から遠くなってしまうのです。

このため、「-121」と算出してしまったわけです。

整数化するにあたり、負数がないと断言できる場合は、INT関数をつかってもいいですが、確定していない場合には、TRUNC関数を使う必要があります。

このように、似ている関数というのが他にもありますが、何かときに、全く予期していない結果を算出する場合がありますので、注意する必要がありそうですね。

Excel。2行連続・3行連続と連続して連番を設定したいけど、楽に設定するにはどうするの？【Serial number】

Excel。2行連続・3行連続と連続して連番を設定したいけど、楽に設定するにはどうするの？

＜INT&ROW関数＞

連番を設定する作業は、オートフィル機能を使うことで、いとも簡単に設定することができますが、次の帳票のように、連続した行になっていないと、オートフィルでは対応することが出来ません。

今回は、店舗ごとに連番を設定したいというのがやりたいこと。

各店舗には、上半期と下半期の2行で構成されています。

このような帳票だと、オートフィルで一発というわけにはいかないのです。

A列の左側にNOという見出しの列を挿入します。

A2に「1」と入力して、オートフィルをつかって、「連続データ」にすると連番を設定することができます。

ただ、今回は、店舗ごとに連番を設定したいわけです。

それでは、A2:A3に「1」と入力して、オートフィルをつかえば、連番が設定できるように思えますが、実行してみると、うまくいきません。

データの件数が少なければ、自力で入力するということでも、いいかもしれませんが、大変な事には変わりありません。

そこで、ちょっとした数式を作ることで、この問題から解放されます。

使うのは、INT関数とROW関数の2つです。

INT関数は、整数化するための関数ですね。

ROW関数は、行番号を算出することができる関数ですね。

では、A2に次の数式を設定しましょう。手入力でも対応できると思います。

=INT(ROW(A2)/2)

たったこれだけですが、数式を設定したら、オートフィルで数式をコピーしてみましょう。

 

このように、2行連続で、同じ番号で連番を設定することができました。

ポイントは、この数式の仕組みですね。仕組みがわからないと、表の起点が変わる、要するに、何行目にその行があるのかによって、「1」にするために、ちょっと考える必要が発生します。

数式の説明をしておきましょう。

ROW(A2)ですが、2行目なので、行番号の「2」が算出されます。

算出された「2」を2で除算します。算出結果は「1」ですね。

算出結果の「1」をINT関数で整数化されるので、「1」が算出されたというわけです。

なので、奇数行は、2で除算して、0.5の余りが発生しても、INT関数で整数化されるために連番を設定することができたわけです。

この仕組みがわかれば、3行連続とか、四半期ごとなどでも、連番を設定することができるようになります。

四半期の場合だと、次のようにすればOKですね。

A2の数式は、次のように設定してあります。

=INT(ROW(A4)/4)

ポイントは、どうやったら「1」を表示することができるのかですね。

Excel関数辞典 VOL.41。INFO関数～IRR関数【dictionary】

Excel関数辞典　VOL.41。INFO関数～IRR関数

＜Excel関数＞

今回は、INFO関数～IRR関数までをご紹介しております。

INFO関数

インフォ

Excelの動作環境に関する情報を返す

INFO(検査の種類)

INT関数

イント

最も近い整数に切り下げる

INT(数値)

INTERCEPT関数

インターセプト

回帰直線の切片を算出

INTERCEPT(既知のY,既知のX)

INTRATE関数

イントレート

満期に償還される証券の利率を算出

INTRATE(受渡日,満期日,投資額,償還価格,[基準])

IPMT関数

アイピーエムティー：インタレストペイメント

元利均等返済における指定期間の利息を算出

IPMT(利率,期,期間,現在価値,[将来価値],[支払期日])

IRR関数

アイアールアール

定期キャッシュフローに対する内部利益率を算出

IRR（範囲,[推定値]）

Excel。4月-6月を第1四半期として、四半期別集計を算出したい。

Excel。4月-6月を第1四半期として、四半期別集計を算出したい。

＜INT+MOD+MONTH関数,SUMIF関数＞

ピボットテーブルを使えば、”秒殺”で四半期別集計は算出することができるのですが、表が出来上がっていて、該当のセルに結果を表示したい場合など、ピボットテーブルを使用しないで、算出させたいというケースもあります。

そこで、今回は、4月-6月を第1四半期として、四半期別集計を算出していきます。

次の表があります。

E列の四半期ですが、表示形式をつかって、第○四半期と表示させています。

E2:E5までは、1～４の数値のみがセルに入力されています。

さて、どのようにしたら、4月から6月を第1四半期と判断することができるのでしょうか？

SWITCH関数など4ならば1というような設定をしてもいいのですが、これだと多くの引数が必要になってしまい、わかりやすい反面、効率がわるいです。

そこで、次の手順を踏むことで4月から6月を第1四半期と判断する計算手順があります。

H列には、1月から12月までの数値が入力されています。
H2の1は1月を意味しています。

最初に行うのは、I列のマイナス4を行います。
マイナス3では？と思うかもしれませんが、マイナス3にすると、ズレが生じてしまい、上手くいきません。

I2の数式は、
=H2-4

2番目は、
1年は12カ月なので、12で除算しますが、必要なのは、除算した結果の”余り”なので、MOD関数を使います。
J2の数式は、
=MOD(I2,12)

3番目は、四半期が3カ月分で構成されているので、3で除算します。
K2の数式は、
=J2/3

4番目は、誤差を調整しますので、+1をします。
L2の数式は、
=K2+1

最後は、整数部分だけが必要なので、INT関数で整数部分を取り出します。
M2の数式は、
=INT(L2)

このような手順によって、4月から6月を第1四半期と判断することができます。
これをまとめて数式化しています。

C2をクリックして、次の数式を作ります。
=INT(MOD(MONTH(A2)-4,12)/3+1)
手順とは逆で、INT関数から作成しますので、注意しましょう。

算出出来たら、オートフィルを使って数式をコピーします。

あとは、この結果を使って、集計します。

F2をクリックして、SUMIF関数ダイアログボックスを表示します。

範囲には、$C$2:$C$20
検索条件には、E2
合計範囲には、$B$2:$B$20

F2の数式は、
=SUMIF($C$2:$C$20,E2,$B$2:$B$20)
当然、手入力で数式を作成してもOKです。

それでは、オートフィルを使って数式をコピーします。

これで、4月から6月を第1四半期として集計することができました。

簡単なようですが、これもクセがありますので、パズルのような手順を踏んで数式を作っていくといいですね。

面倒だと感じたら、ピボットテーブルを使って算出してみると、いいかもしれません。

Excel Technique_BLOG Categoryに追加しました。2018/12/21

Excel Technique_BLOG Categoryに追加しました。2018/12/21

＜目次サイト＞

このBLOGの記事を、
カテゴリー分けにした【Excel Technique_BLOG Category】に追加しました。

Excel。負の時にINT関数では、使えない時がある。INT関数とTRUNC関数。絶対値のABS関数

Excel。小数点以下を切り落として、整数化するときに、INT関数を使いますが、このINT関数は、場合によって、アンマッチになることがあります。
それは、負の数の時。
その時は、TRUNC関数を使うことをお勧めします。
＜続きはこちらへ＞
https://infoyandssblog.blogspot.com/2014/01/excelint.html

Excel。表示形式で聞かれる通貨と会計の違い。

Excelの表示形式。非常に奥が深くて、知っていると便利なこともあるのですが、
この表示形式の質問で結構聞かれるものの中に、通貨と会計って何が違うのですか？
というのがあります。そこで、今回は、通貨と会計の違いをご紹介しましょう。
＜続きはこちらへ＞
https://infoyandssblog.blogspot.com/2014/01/excel_16.html

Excel。24h以上の表示が出来ませんので表示形式を変更します。

Excelの表示形式。これまた、よく使うというか、知らないと困るものの中に、24時間以上の表記が出来ないというのがあります。
深夜26:30から放送。
というこの26時というのが、Excelでは、ストレートに出来ないのです。
24h以上の表示をするための方法をご紹介します。
＜続きはこちらへ＞
https://infoyandssblog.blogspot.com/2014/01/excel24h.html

Excel。なんで、時給計算がうまくいかない！解決方法は24倍にします。

Excelの講座で、必ずといっていい程、ご紹介するのが、時給計算です。

なぜかというと、Excelは、非常に高度な計算をサクサクとこなすのですが、時間計算及び、時給計算が苦手。すんなり算出してくれません。

＜続きはこちらへ＞

https://infoyandssblog.blogspot.com/2014/01/excel24.html

Excel。入力規則で小数点第一位までしか入力できないようにしたい【Decimal point】

Excel。入力規則で小数点第一位までしか入力できないようにしたい

＜入力規則+INT関数＞

次のような出退勤管理の表があります。

出勤・退勤は、8:28に出勤ならば、8.5というように、
30分毎に0.5で区切って入力する表の場合、
間違えて、小数点第一位以降小数点を入力させないようにしたいとします。

このように、入力にルールを設けるには、入力規則を使うといいのですが、
小数点第一位以降の小数点を入力できないようにするには、
少しアイディアが必要なんですね。

では、入力規則を確認してみましょう。

B3:C7を範囲選択して、データタブの「データの入力規則」をクリックして、
データの入力規則ダイアログボックスを表示しましょう。

設定タブの条件設定にある入力値の種類を「小数点数」にしてみて、
データを「次の値以上」で最小値を「0.1」にして
OKボタンをクリックしてみたらどうなるのでしょうか？

では、B3に8.5と入力してみると、問題なく入力できます。

しかし、8.55と入力してみましょう。

小数点第二位まで入力出来てしまいましたよね。

これでは、今回やりたいこととは違います。

最小値を0.1にしても、0.11は入力出来てしまいます。
つまり、小数点第一位までという入力規則を用意されている
入力値の種類をそのまま使うことは難しいようですね。

そこで、ユーザー設定を使ってみましょう。

改めて、B3:C7に入力規則を設定していきます。

なお、入力規則はクリアしておきます。

設定タブの入力値の種類を「ユーザー設定」にすると、
数式ボックスが登場しますので、そこに、

=B3*10=INT(B3*10)

という数式を設定します。

そして、OKボタンをクリックしましょう。

では、B3に8.55と入力してみましょう。

今回は、エラーメッセージが表示されて、入力することが出来ませんね。

これで、今回の目的である、
小数点第一位までしか入力することが出来ないように
入力規則を使うことで出来ました。

では、数式に設定した、

=B3*10=INT(B3*10)

を確認してみましょう。

B3の値を10倍した値、先程の8.5を使ってみると、85。

INT関数のB3の値を10倍した値は、8.5だと、85なので等しくなります。

8.55の場合どうなるのでしょうか？
B3の値を10倍した値、8.55を使ってみると、85.5。

8.55をINT関数のB3の値を10倍した値は、85.5なのですが、
INT関数を使うことで整数化されていますから、85になり、
85.5と85を比べますので、等しくない。

つまり、小数点第一位以降の数値が入力されているということが
わかるようになっています。

なお、小数点第二位としたい場合は、100倍すれば対応可能ですよ。

簡単な数式ですが、入力規則と合わせて使うと効果抜群ですね。

Excel。事務職のデータ分析その9。移動平均グラフを作ってみよう【Moving Average】

Excel。事務職のデータ分析その9。移動平均グラフを作ってみよう

＜移動平均：AVERAGE&INT&IFERROR関数＞

最近は、事務職でも資料作りの一環で、
データ分析系の資料作成をする人が増えてきたそうですので、
あまり馴染みがないものも少しずつ慣れていくようにしましょう。

今回は、移動平均グラフを作っていきます。

移動平均を使うことで、
短期間での状況把握だけでなく、長期間での状況把握をおこない、
傾向を把握することが出来るようになります。

算出自体は基本的に平均値を算出するだけですが、
一定の期間をスライドさせながら平均値を算出することによって、
移動平均を算出することが出来ます。

小売業に限らず、
「季節変動」や「無作為変動」などがありますので、
現場ではよく使われている資料の一つだと思います。

次のような売上表があります。

数値だけ見ると、何がなんだかよくわかりませんね。
傾向として安定しているのか？そうでないのか？がわかりません。

では、移動平均グラフを作成したいのですが、
この表のままではグラフを作成することが出来ません。

データを一列に変更する必要があります。

G列には、
2016年1月から2018年12月までの売上高を一列に結合しております。

H列には、移動平均を算出します。

今回の期間（区間）は、12ヶ月としますので、
12ヶ月分のデータが揃っている、
H14に12ヶ月分の平均値を算出しますので、

=AVERAGE(G3:G14)

という数式を作成して、
上下それぞれに、オートフィルで数式をコピーしてみましょう。

算出は出来たのですが、数値が綺麗でないというか、
アレンジが必要な感じですね。

まずは、今回は、
数値を整数化してG列と同じように小数点がない形式に変更しますので、
INT関数を使って数式を変更しましょう。H14の数式を次のように修正します。

=INT(AVERAGE(G3:G14))

では、オートフィルで数式をコピーしましょう。

次に#REF!というエラーが表示されたままだと、
格好悪いので、IFERROR関数を数式に加えて空白にしましょう。

H14の数式は、
=IFERROR(INT(AVERAGE(G3:G14)),"")

オートフィルで数式をコピーしておきます。
これで空白に変えることが出来ました。

データの準備が出来ましたので、グラフを作成していきましょう。

F3:H38を範囲選択して、マーカー付き折れ線グラフを挿入していきましょう。

G3:H38でもいいのですが、ある特徴がありますので、F3:H38で作成します。

グラフは10月から描かれていますよね。

これは、H列の移動平均が空白になっているので、
10月から描かれています。

その説明のために、F列を範囲選択に含めてみました。

グラフをアレンジしていきましょう。

横軸は不要ですので、横軸をクリックして、DELキーで非表示にしましょう。

グラフタイトルも変更しておきましょう。

あとは、凡例ですね。
売上高がメチャクチャになっていますので、売上高という文字だけにしましょう。

グラフツールのデザインタブにある「データの選択」をクリックします。

データソースの選択ダイアログボックスが表示されますので、

凡例項目（系列）にある、売上高583…をクリックして、
編集ボタンをクリックします。

系列名を、
=移動平均!$G$2

に変更してOKボタンをクリックします。

同じように、凡例の移動平均も修正します。

そして、データソースの選択ダイアログボックスのOKボタンを
クリックします。

グラフは次のように修正されました。

このようにして、移動平均グラフを作ることで、
長期間の動きや傾向も把握しやすくなりますよ。

Excel。島田秀平さんが語っていた今年の運勢の求め方をExcelで作ってみる。

Excel。島田秀平さんが語っていた今年の運勢の求め方をExcelで作ってみる。

＜SUM&INT&MOD&VLOOKUP関数＞

2017年もスタートしまして、初詣で【おみくじ】を引きましたという人も多いかと思いますが、
元旦のCX。ワイドナショー元旦SPという番組で、
芸能人さんの島田秀平さんが、
「今年の運勢がわかる計算方法」のようなことをやっておりまして、
まぁ、占い等々が当たる・当たらないはともかく、
講義のネタとしては面白いなぁ～と思いまして、その計算方法をExcelで作ってみようかと。

ということで、早速作り方をご説明します。

完成のイメージはこんな。

2行目ですが、今年を西暦で、
そして誕生日をそれぞれ、数値を一つずつセルに入力するように作成します。

入力するのはこの2行目だけです。

そして、この2行目の数値を合計するのだそうです。

今回は、2+0+1+7+0+9+2+7ということなので、C3にはSUM関数を使えば問題ありませんね。

C3には、2行目の数値の合計値を算出した。

数式は、

=SUM(C2:L2)

すると、28という数値が算出されました。
そして再び2+8というように、数値をバラして合計させるということを繰り返して、
最終的に1桁になるまで計算させていきます。

そこで、C3に算出された28を十の位と一の位に分ける必要があります。

ここでLEFT関数やRIGHT関数を使ってしまうと、
文字になってしまい合計することが出来なくなるので、注意が必要です。

C4の十の位の数式は、

=INT(C3/10)

10で除算してあげて、整数化してあげれば、十の位を求めることが出来ますね。

D4の一の位の数式は、

=MOD(C3,10)

MOD関数を使えば、10で割った余りを算出することができますので、
一の位を求めることが出来ます。

C5とC6とD6とC7も同じように数式を作成していきます。
C5には、=SUM(C4:D4)
C6には、=INT(C5/10)
D6には、=MOD(C5,10)
C7には、=SUM(C6:D6)
という数式が設定されています。

そして、C7が求められた計算結果になります。
その数値に基づき、今年がどんな一年になるのかがわかるのだそうでして…

1 スタート・種まき
2 出会い・人脈づくり
3 花開く・楽しみ切る
4 基礎固め・家関係
5 転機・チャレンジ
6 自己犠牲・人に尽くす
7 自己投資・勉強
8 あるがまま・自然に
9 けじめ・集大成・断捨離

なんだそうです。
「へぇ～。ルーティーンというかめぐってくるものなんですかね？」

で、C7に数値が求められていますので、
その内容をC9表示させるのに、
いちいちCOPYとかでは面倒なので、VLOOKUP関数を使うと便利ですね。

では、C9をクリックして、VLOOKUP関数ダイアログボックスを表示しましょう。

検索値はC7
範囲は、$A$12:$B$20　今回は、オートフィルで数式をコピーしませんが、
絶対参照を付けております。
列番号は、2
検索方法は、完全一致のFALSE。または、0。
確認が出来ましたら、OKボタンをクリックしましょう。

なお、C9の数式は、

=VLOOKUP(C7,$A$12:$B$20,2,FALSE)

これで、完成しました。

このように、ちょっとした内容から、
Excelの関数を勉強することもできますので、
興味があるものを目にしましたら、
Excelだとどうやるのかな？なんて遊び心も加えていくと、
Excel力もアップしやすいのかなぁ～と思いますので、
日々少しずつスキルアップをしていきましょう。

Excel。Average。平均は平均でも、移動平均を指定した日数で算出する方法

Excel。平均は平均でも、移動平均を指定した日数で算出する方法

＜移動平均＞

平均を算出するなかでも、場合によっては、
ただ単に平均を算出すればOKというものばかりではないですよね。

例えば、移動平均などがそうですよね。

移動平均は、一定期間の平均を連続的に求めることで、データの変動を吸収できるので、
全体の方向性を掴みやすいツールですが、
この移動平均を指定した期間で変更できる表を作ろうとすると、なかなか大変なので、
今回は、移動平均表の作り方をやってみましょう。

まずは、下記の表があります。

B1には、平均日数が入力されています。
ここを変えると、平均日数が変わって、その日数で移動平均を出せるようにします。

C列には、移動平均を算出することになります。

さて、C4の移動平均は、平均日数が、3日なので、B4:B6の数値の平均値ですから、
単純にAVERAGE関数で算出することは可能です。

C4には、=AVERAGE(B4:B6) という数式が設定されていて、オートフィルハンドルを使って、
数式をコピーした結果が表示されております。

これで、全く持って問題は無いのですが、これでは、汎用性がないので、
今回は、B1に数値を入力した期間で移動平均を算出しようというものなのです。

では、C4に数式を作っていくことにしましょう。

まず問題になるのが、どうやったら、3日間分の範囲選択が出来るのか？ということですよね。
このような場合には、OFFSET関数を使うといいですね。

このOFFSET関数は、起点から指定した行や列を参照することが出来る関数なのです。

では、先程AVERAGE関数を使って算出しましたら、小数点以下が表示されましたので、
INT関数を使って整数化するようにしてみましょう。

C4をクリックして、INT関数ダイアログボックスを表示しましょう。

数値にAVERAGE関数を設定してきます。

数値1に、OFFSET関数ダイアログボックスを設定してきましょう。

いよいよ、OFFSET関数ですね。引数が多くてわかりにくいですが、
一つずつ設定していきましょう。

参照には、起点となるB4　を入力します。
行数は、0（ゼロ）

列数も、0（ゼロ）

高さは、平均日数が格納されている、B1ですが、
オートフィルハンドルを使って数式をコピーしますので、絶対参照を設定しますので、$B$1

幅は、ありませんので、省略します。

あとは、OKボタンをクリックしましょう。

これで、数式は完成しましたね。
C4には、

=INT(AVERAGE(OFFSET(B4,0,0,$B$1)))

という数式が設定されています。

移動平均が算出できましたね。では、B1を6に変更してみましょう。

移動平均が変わりましたね。このようにOFFSET関数を組合すことで、
範囲選択をする関数も汎用性をアップすることが出来るようになりますよ。