3/30/2021

Excel。月間や四半期などの期間計算をするにはどうしたらいいの?【Period calculation】

Excel。月間や四半期などの期間計算をするにはどうしたらいいの?

<SUMIFS関数>

データからこの期間の合計値を算出したいということは多々あるケースではありますが、意外とトラブルを起こすというか、上手くいかないことがあるようなので、確認してみましょう。


次の表を用意しました。


このデータから7月1日~9月30日までの売上高の四半期合計をE1に算出していきます。


期間計算を行う場合のポイントは、開始と終了という2つの条件が発生するというわけです。

Accessだと、クエリなどで期間を抽出するときには、Between~And演算子という比較演算子をつかうことで簡単に算出することができますが、Excelには、Between~And演算子というのはありません。


そこで、2つの条件ということなので、複数条件に対応した合計値を算出できる関数。【SUMIFS関数】をつかうことで、算出することができます。


E1には、次の数式を設定したら、確認してみましょう。

=SUMIFS(B2:B10,A2:A10,">=2021/7/1",A2:A10,"<2021/10/1")


算出結果は、次のようになります


複数条件のうえ、合計値を算出したいわけなので、『SUMIFS関数』をつかうわけですが、引数を確認したいので、SUMIFS関数ダイアログボックスを表示します。


確認したいところは、条件1と条件2です。

条件1には、「">=2021/7/1"」

条件2には、「"<2021/10/1"」

と設定しています。


ポイントが2つあって、一つ目として、条件に「”(ダブルコーテーション)」で比較演算子も含めて囲んでいることです。


文字型ではありませんが、「”(ダブルコーテーション)」で囲まないとエラーになってしまいます。


ただ、注意点があって、今回は直接条件の日付を引数内で入力しているので、条件全体に「”(ダブルコーテーション)」で囲んでいますが、セル番地を使う場合は、「”>=”&A1」のように、比較演算子を「”(ダブルコーテーション)」で囲み、「&(アンパサンド)」でセル番地を接続させた形で設定します。


引数内直接条件なのか、セル参照なのかによって、設定の仕方が、かわるところがちょっと厄介に感じるところかもしれませんね。


もう一つのポイントですが、条件2を「期間の翌日」にしている点です。


15日とかはっきりした日ならば、終了日そのものでいいのですが、「月末」の場合、2月のうるう年問題があり、過去の場合その年が、うるう年だったのか確認しないといけないので、いちいち、28日なのか29日なのかを確認するぐらいなら、翌月の一日にしたほうが、間違いがありません。


このように、期間計算をするときには、SUMIFS関数をつかって算出することがあると思いますので、その時には、注意しておくといいかもしれませんね。

3/29/2021

今週のFacebookページの投稿 2021/3/22-2021/3/28【one thing】

今週のFacebookページの投稿 2021/3/22-2021/3/28

<Facebookページ>

Facebookページで【書いてみた】ワンポイントです。

3月22日 Excel。

YIELDDISC関数。

読み方は、イールドディスクで、割引債の年利回りを算出します。


3月23日 Excel。

YIELDMAT関数。

読み方は、イールドマットで、満期利付債の利回りを算出します。


3月24日 Excel。

ZTEST関数。

読み方は、ゼットテストで、Z検定の上側確率を算出します。


3月26日 Excel。

Z.TEST関数。

読み方は、ゼット・テストで、Z検定の上側確率を算出 Excel2010以降


3月27日 Excel。

範囲選択が基本中の基本ですよね。


3月28日 Excel。

範囲選択。

複数のセルを選択するときは、Ctrlキーを押しながらクリックですね。

3/27/2021

Excel。移動平均グラフを作りたいけど、横軸を5日おきにしたい。【moving average】

Excel。移動平均グラフを作りたいけど、横軸を5日おきにしたい。

<移動平均グラフの横軸アレンジ>

移動平均グラフというのがあります。


売上金額など、日々のデータの推移をみると、データが均等というわけではなくて、多い日もあれば少ない日もあるわけです。

そこで、移動平均というのをつかうのですが、数値として算出しただけでは、イメージしにくいこともあるので、「移動平均グラフ」をつくることがあります。


移動平均グラフを作ること自体は難しくはないのですが、移動平均グラフは長期間にわたるデータを使ってつくるので、横軸の表示数が多くなってしまい、見やすくするはずの移動平均グラフが、横軸が原因で見づらくなってしまうことがあります。


そこで今回は、横軸の表示にも考慮した、移動平均グラフをつくっていきます。


移動平均グラフをつくるには、まず移動平均を算出した表を作る必要があります。


C7の数式は、単純に5日間の平均値を算出しております。

=AVERAGE(B3:B7)

なお、データは、5月31日までの2ヶ月としています。


それでは、移動平均グラフをつくっていきます。


見出し行のA2:C2とA7:C63を範囲選択します。

5日平均を折れ線グラフにしますので、挿入タブの「すべてのグラフを表示」をクリックします。


グラフの挿入ダイアログボックスが表示されます。


すべてのグラフタブの「組み合わせ」を選択します。

売上高のグラフの種類を「集合縦棒」に設定します。

5日平均のグラフの種類は、「折れ線」に設定したら、OKボタンをクリックします。


移動平均グラフが挿入されました。

説明の都合があるので、グラフタイトルを削除して、大きく表示させるようにしております。


グラフが挿入された迄はいいのですが、横軸がグラフの大きさに合わせて、表示数が多くなっていきます。

それでいいのかもしれませんが、横軸の表示を10日おきに表示するように設定していきます。


横軸をクリックして、書式タブのグラフの要素が「横(項目)軸」になっていることを確認したら、「選択対象の書式設定」をクリックします。


軸の書式設定作業ウィンドウが表示されます。


軸のオプションの単位の「主」を10と入力します。

グラフの横軸は、このように変わりました。


データが多いく横軸が日付などの場合は、一定期間で横軸を設定することができます。

あとは、グラフエリアやプロットエリアの背景色を設定したり、フォントサイズを変更したりして、完成ですね。


今回は、移動平均グラフを例にしましたが、データ量が多い場合などでは、横軸の表示方法をアレンジすることができますので、状況によって使ってみるといいかもしれませんね。

3/26/2021

Excel。グラフの復習。マイナスデータの3-D集合縦棒~五角形横棒グラフ【graph】

Excel。グラフの復習。マイナスデータの3-D集合縦棒~五角形横棒グラフ

<グラフの復習>

Excelのグラフは、用途に合わせて様々なグラフを作ることができます。


今回は、グラフの復習ということ、4つをピックアップ

・Excel。マイナスデータの3-D集合縦棒グラフを作ってみよう。

・Excel。作る機会が少ないバブルチャートの作り方を確認しておきましょう。

・Excel。ABC分析でお馴染みのパレート図をOffice365で作ってみよう。

・Excel。横グラフの先端を鋭角にした五角形横棒グラフを作るには?


Excel。マイナスデータの3-D集合縦棒グラフを作ってみよう。

マイナスデータがある3-D集合縦棒グラフの場合、補足ポイントを確認していくことにします。


<続きはコチラ>

Excel。マイナスデータの3-D集合縦棒グラフを作ってみよう。

https://infoyandssblog.blogspot.com/2019/07/excel3-dcollective-vertical-bar-chart.html



Excel。作る機会が少ないバブルチャートの作り方を確認しておきましょう。

通常グラフは2つの要素から構成させたものですが、3つの要素から構成されたグラフである、「バブルチャート」は、あまり作成する頻度が少ないので、作り方の確認をしておきましょう。


<続きはコチラ>

Excel。作る機会が少ないバブルチャートの作り方を確認しておきましょう。

https://infoyandssblog.blogspot.com/2019/08/excelbubble-chart.html



Excel。ABC分析でお馴染みのパレート図をOffice365で作ってみよう。

パレート図は、集合縦棒グラフと折れ線グラフの2種類が混在したグラフですので、第2軸を使用したグラフを作成します。


<続きはコチラ>

Excel。ABC分析でお馴染みのパレート図をOffice365で作ってみよう。

https://infoyandssblog.blogspot.com/2019/08/excelabcoffice365pareto-chart.html



Excel。横グラフの先端を鋭角にした五角形横棒グラフを作るには?

横棒グラフの先端を鋭角にした五角形横棒グラフを作るにはどのようにしたらいいのでしょうか?


<続きはコチラ>

Excel。横グラフの先端を鋭角にした五角形横棒グラフを作るには?

https://infoyandssblog.blogspot.com/2019/09/excelmodified-bar-chart.html

3/24/2021

Excel。シートを大量に追加して、指定したシート名に変更するのが面倒なので楽したい。【Add sheets】

Excel。シートを大量に追加して、指定したシート名に変更するのが面倒なので楽したい。

<Excel VBA>

Excelの処理において、とても単純な作業。

例えば、新しいシートを追加して、そのシート名を変更するなんてことは、簡単な作業ではありますが、処理をする量が増えてしまうと、面倒以外の何物でもありません。


単純作業の繰り返しで、面倒に感じる場合には、Excel VBAでマクロを作ってExcelに処理させちゃうことができれば、作業効率も改善するし、自分自身のワーク負荷も緩和することが出来るかもしれません。


そこで、今回は、大量な新しシートを追加して、しかも追加したシートのシート名も指定して変更までするマクロを作っていきます。


意外と短いプログラム文で、出来ちゃいます。


最初に用意するシートがあります。


追加するシート用のシート名のデータを用意します。


今回は、5件分ですが、100件用意すれば100枚の新しいシートを追加することができて、そのシート名も変更することができます。

もしも五十音順にしておきたい場合には、並び替えを事前にしておくとよいかと思います。


作成するExcel VBAのプログラム文を見ていくことにしましょう。


Sub シート作成()

    Dim i As Long

    Dim sheet_name As String

    Dim lastrow As Long

        

    sheet_name = ""

    lastrow = Cells(Rows.Count, "a").End(xlUp).Row

    

    For i = 2 To lastrow

    sheet_name = Worksheets("シート名作成").Cells(i, "a")

    Sheets.Add(after:=Sheets(Sheets.Count)).Name = sheet_name

    Next

End Sub


まずは実行して確認してみましょう。


このように、A列のシート名の順番通りにシートが追加されていることが確認できます。


ちょっとしたことですが、知っていると便利かと思われます。


では、プログラム文を確認しておきましょう。


まずは、お馴染みの変数宣言ですね。

    Dim i As Long

    Dim sheet_name As String

    Dim lastrow As Long

変数名「sheet_name」はシート名を代入しておく変数です。

変数名「lastrow」は、繰り返し数のための変数です。


sheet_name = ""

「sheet_name」を初期化しておきます。


lastrow = Cells(Rows.Count, "a").End(xlUp).Row

シート名の件数を確認するためのプログラム文です。


動きとしては、シートの最終行まで一度行き、そこから、上に向かって移動すれば、データの最終行になるという考え方ですね。


For i = 2 To lastrow ~ Next 文で、繰り返し処理をします。

データが2行目からなので、2から始めて、最終行の行番号まで繰り返します。


sheet_name = Worksheets("シート名作成").Cells(i, "a")

変数「sheet_name」に、シート名を代入するプログラム文ですね。


Sheets.Add(after:=Sheets(Sheets.Count)).Name = sheet_name


Sheets.Addで、シートを追加することが出来ます。

after:=Sheets(Sheets.Count) は、Sheets.Countで、シート数を数えます。


例えば2枚のシートがあれば、Sheets(2)となり、「after」なので、Sheets(2)の右側に挿入されます。


afterを設定しないと、左側にシートが挿入されてしまうので、移動するのが面倒なので、afterの設定をしておきました。


Name = sheet_name で、シート名を設定することができます。


比較的、わかりやすいプログラム文ではありますが、大量のシートを挿入しないといけない時などに知っておくといいかもしれませんね。

3/23/2021

今週のFacebookページの投稿 2021/3/15-2021/3/21【one thing】

今週のFacebookページの投稿 2021/3/15-2021/3/21

<Facebookページ>

Facebookページで【書いてみた】ワンポイントです。

3月15日 Excel。

XIRR関数。読み方は、エックスアイアールアールで、非定期キャッシュフローに対しる内部利益率を算出します。


3月16日 Excel。

XNPV関数。読み方は、エックスエヌピーヴイで、非定期キャッシュフローに対する正味現在価値を算出します。


3月17日 Excel。

XOR関数。読み方は、エクスクルーシブ・オアで、複数の条件で奇数の数を満たすかどうかを調べる


3月18日 Excel。

YEAR関数。読み方は、イヤーで、日付から年を算出します。


3月19日 Excel。

YEARFRAC関数。読み方は、イヤーフラクションで、2つの日付の間の期間を年数で算出


3月20日 Excel。

YEN関数。読み方は、エンで、数値を四捨五入して、円記号(¥)を付けた文字列に変換する


3月21日 Excel。

YIELD関数。読み方は、イールドで、定期利付債の利回りを算出します。

3/21/2021

Excel。スピル機能で使いやすくなったFREQUENCY関数で度数を算出してみよう【Spill】

Excel。スピル機能で使いやすくなったFREQUENCY関数で度数を算出してみよう

<FREQUENCY関数>

ただデータを集めて表にしただけでは、もったいないので、ちょっとした数値を算出してみると、違ったことが見えるというか想像したり、仮定をたてたりすることができるかもしれません。


例えば、次の表。


B列の各店舗における商品Aの販売数のデータですが、このままではただのデータでしかないので、例えば、販売数が101~200の店舗(件数)がいくつあるのかが、わかれば、なぜ、販売個数が多くなるのかなど、仮定をたてることも出来たりするかもしれません。


そこで、100ごとの階級として、それぞれの階級に何店舗該当するのかを算出してみようと思います。

このような階級ごとの数値のことを「度数」と呼んでいます。


Excelでは、度数を算出する関数が用意されています。

それが、「FREQUENCY関数」です。


このFREQUENCY関数ですが、ちょっと厄介な関数でして、配列関数のため、算出したい範囲を選択しておいてから、数式をつくり、確定させる時に、Ctrl + Shift + Enterキーを押して設定させる必要がありました。


しかし、Office365のExcelやExcel2019に追加された新機能である「スピル機能」をつかうことで、FREQUENCY関数が、普通の関数と同じような感じで数式を作れるようになりました。


では、スピル機能をつかった、FREQUENCY関数を確認していきましょう。


G2にFREQUENCY関数の数式を設定してきます。


FREQUENCY関数ダイアログボックスで数式を設定してもいいですが、

FREQUENCY(データ配列,区分配列)

とFREQUENCY関数は引数が2個と少ないので、手入力で作成していくといいでしょう。


G2の数式は、

=FREQUENCY(B2:B11,F2:F11)


数式を確定すると、スピル機能によって、G2の数式が、下記方向にコピー(溢れて)されます。

配列関数のときは、絶対参照が必要になりますが、スピル機能は絶対参照の設定は不要です。


配列関数で使う、Ctrl + Shift + Enterキーを押す必要はありません。

なお、今まで通り、Ctrl + Shift + Enterキーをつかった、配列関数にしても、問題はありません。


ただ、G12までスピル機能で算出されていますが、これは、1001以上の度数を算出しているので、不要であったとしても、スピル機能で管理している範囲の為、G12の数式だけを削除することができません。


今回のFREQUENCY関数だけではなくて、スピル機能の登場で、今まで以上に、使い勝手がより良くなっている関数があるかもしれませんので、色々確認してみるといいかもしれませんね。

3/20/2021

Excel。絶対値を算出するなら、ABS関数で解決します【function:ABS】

Excel。絶対値を算出するなら、ABS関数で解決します

<ABS関数>

Excelには様々な関数が用意されていますが、今回は、ABS関数を紹介。


数値から「正」「負」の記号を取り除いた数値である『絶対値』を算出したいときにしようするのが、ABS関数です。


ABS関数の読み方は「アブス」といいます。

絶対値を英語だと、「Absolute value」というので、「エービーエス」って読むよりも、「アブス」ってほうがしっくりきそうですね。


ABS関数の引数は、

ABS(数値)

関数の分類は、「数学・三角」に所属しています。


次の表を使って確認してみましょう。


B2に数式をつくり、オートフィル機能を使って数式をコピーします。

B2につくる数式は、

=ABS(A2)

結果はこのようになりました。


B列のABS実行後の算出された値は同じになりましたね。

絶対値は、「0(ゼロ)」からの距離ということなので、プラスやマイナスの記号がとれた数値のみという結果になります。


ABS関数のみで使用することは少ないかもしれませんが、関数を多く知ることで、作業効率が改善できる可能性が広がりますので、少しずつ知っている関数を増やしていくのもいいかもしれませんね。

3/18/2021

Excel。セル内の一部の文字が合致したらそのデータの行全体を塗りつぶしたい。【Fill the line】

Excel。セル内の一部の文字が合致したらそのデータの行全体を塗りつぶしたい。

<FIND関数+条件付き書式>

条件付き書式をつかえば、セル内の文字に条件と合致するデータが含まれていれば、そのセル自体に書式を設定することは簡単です。


例えば、次の表。


C列の住所のフィールドのデータから、横浜市のデータに塗りつぶしを設定したいとします。

範囲選択をして、ホームタブの条件付き書式から「セルの強調表示ルール」にある「文字列」をクリックします。


文字列ダイアログボックスが表示されますので、「横浜市」と設定して、書式を設定したら、OKボタンをクリックします。


すると、横浜市という文字が含まれているセルに書式が反映されました。


このように、セルに該当する文字列があれば、条件付き書式を設定することで、書式を反映させることができます。


しかしながら、セルは塗りつぶすことができましたが、該当するレコード。

つまり行全体に条件付き書式を設定し、反映するにはどのようにしたらいいのでしょうか?


それでは、該当するレコード。行全体に条件付き書式を設定していきます。

A2:C10まで範囲選択します。


ホームタブの条件付き書式にある「新しいルール」をクリックします。


新しいルールダイアログボックスが表示されます。


ルールの種類を「数式を使用して、書式設定するセルを決定」をクリックします。


行全体など、アレンジしたい時は、「数式を使用して、書式設定するセルを決定」をつかうことで、対応することができます。


次の数式を満たす場合に値を書式設定に、次の数式を設定します。

=find("横浜市",$C2)

FIND関数は、セル内に検索したい文字が左から何文字目にあるのかを算出することができる関数です。


今回の場合、セル内に、「横浜市」という文字がなければ、#VALUEという判定になり、条件を満たすことができないと判断されます。

「横浜市」という文字があれば、数値を算出してくれます。


また、引数内の「$C2」ですが、列固定の複合参照とすることで、行を対象として書式を反映することができます。

仮に、「C$2」のように行固定の複合参照にすれば、列を対象として書式を反映することができます。


あとは、書式ボタンをクリックして、任意の塗りつぶしを設定します。


最後に、新しいルールダイアログボックスのOKボタンをクリックします。


ご覧のように、住所のセル内に「横浜市」という文字がある行全体が塗りつぶされていることが確認できました。


条件付き書式は、関数を組み合わせつかうことで、色々対応することができるので、社内の資料などで、つかってみると、よりわかりやすくなるなるかもしれませんね。

3/17/2021

Excel Technique_BLOG Categoryに追加しました。2020/3/17【Technique】

Excel Technique_BLOG Categoryに「期間内の祝日の日数」などを追加しました。

<目次サイト>

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

Excel。期間内の祝日の日数を数えたいけど、どうしたらいい?

祝日を数える方法は簡単そうですが、ちょっとアイディアが必要ですよね。


<続きはコチラ>

Excel。期間内の祝日・祭日の日数を数えたいけど、どうしたらいい?

https://infoyandssblog.blogspot.com/2015/12/excelholiday.html


Excel。並び替えをしても、常に1~の通し番号順にしたいけど、どうしたらいいの?

並び替えをしても、常に、1~の通し番号順にしたい


<続きはコチラ>

Excel。並び替えをしても、常に1~の通し番号順にしたいけど、どうしたらいいの?

https://infoyandssblog.blogspot.com/2015/12/excelsort1.html



Excel。塗りつぶしを1行目・2行目・3行目と異なり色で設定して繰り返したい

1・2・3行目のそれぞれが異なる色で塗りつぶすにはどうしたらいいのでしょうか?


<続きはコチラ>

Excel。塗りつぶしを1行目・2行目・3行目と異なり色で設定して繰り返したい

https://infoyandssblog.blogspot.com/2015/12/excelfill123.html

3/15/2021

Access。平均値以上のデータを抽出するには、どうしたらいいの?【Above average】

Access。平均値以上のデータを抽出するには、どうしたらいいの?

<Access:DAvg関数>

Excelだと、オートフィルターをつかうことで、平均値以上のデータを抽出することが簡単にできます。

では、Accessでは、どのようにしたらいいのでしょうか?


テーブルのフィルターで探してみると、数値フィルター内に、平均値以上というのはありません。

そもそも、テーブルのフィルターはどちらかといえば、「即席」という面があり、Accessで抽出といったら、やはりクエリをつかって抽出していくわけですね。


では、次のテーブルの売上高フィールドにある値が平均値以上のデータを抽出していくことにします。


作成タブの「クエリデザイン」をクリックします。


今回は、すべてのフィールドをつかってクエリを作っていくことにしますが、用途に合わせてフィールドを取捨していきましょう。


根本的な問題として、どうやったら「平均値」を算出することができるのか?

それをどうやって条件としてつかうのかと考えるところです。


Excelのように、どこかのセルに算出しておいて、その結果を使うのかなどアレコレ考えてしまいます。


幸いAccessには、DAvg関数というのがあって、その関数をつかうことで、簡単に、平均以上を算出することが出来るのです。


売上高フィールドの抽出条件に、

>=DAvg("[売上高]","T商品A売上")

と設定します。


今回は、「以上」という条件なので、比較演算子の「>=」をつかうことで、条件を設定することができます。

なので、仮に「平均値より大きい」ということでしたら「>」になるというわけですね。


また、このDAvg関数は、

DAvg(フィールド名,テーブル名,条件)

という引数をもっています。ポイントは、テーブル名→フィールド名という順番でないということ。結構間違いやすいので、注意が必要ですね。


では、クエリを実行して確認してみましょう。


このような結果となりました。

しかし本当に平均値以上なのでしょうか?

テーブルをExcelにエクスポートして確認してみましょう。


平均値以上のデータかどうか確認してみると、合致していることがわかりました。


無事に、平均値以上のデータを抽出することができたようです。


今回は、Accessのテーブルデータから平均値以上のデータを抽出する方法をご紹介いたしました。

DAvg関数は、比較演算子での抽出や、Between And演算子をつかった抽出ほど、有名ではありませんが、Excelと同じように、様々な関数が用意されていますので、ちょっとした抽出をしたい時には知っておくといいかもしれませんね。

3/14/2021

今週のFacebookページの投稿 2021/3/8-2021/3/14【one thing】

今週のFacebookページの投稿 2021/3/8-2021/3/14

<Facebookページ>

Facebookページで【書いてみた】ワンポイントです。


3月8日

Excel。WEBSERVICE関数。

読み方は、ウェブサービスで、XML形式のデータから必要な情報だけを取り出します。



3月9日

Excel。WEEKDAY関数。

読み方は、ウィークデイで、日付から曜日に該当する数値を算出する



3月10日

Excel。WEEKNUM関数。

読み方は、ウィークナンバーで、日付の週の番号を算出します。



3月11日

Excel。WEIBULL関数。

読み方は、ワイブルで、ワイブル分布の累積確率か確率密度を算出します。



3月12日

Excel。WEIBULL.DIST関数。

読み方は、ワイブル・ディストで、ワイブル分布の累積確率か確率密度を算出

Excel2010以降



3月13日

Excel。WORKDAY関数。

読み方は、ワークデイで、稼働日数後の日付を算出します。



3月14日 Excel。WORKDAY.INTL関数。

読み方は、ワークデイ・インターナショナルで、週末(曜日指定OK)と祝日を除いた日数後の日付を算出します。

3/12/2021

Excel。そこに関係性はあるのか?ないのか?相関関係はCORREL関数で一発算出できます。【Correlation】

Excel。そこに関係性はあるのか?ないのか?相関関係はCORREL関数で一発算出できます。

<CORREL関数>

データを集めただけでは、ただの数字の情報にすぎません。

しかしながら、こちらの数値とあちらの数値には関係性があるなど、Excelには、ちょっとした関数をつかうことで、今まで見えなかった角度から、仮説をみつけることができたりもします。


例えば、次の資料をつかってみることにします。


B列のWritingの数値と、C列のListeningの数値には、例えば、Writingの数値が良ければ、Listeningの数値も良いなどの、関係性があるのでしょうか?


一目見ただけではわかりませんね。

この手の資料をみて、『直感』というのは、ビジネスではちょっと怖いですよね。


そこで、関係性。

つまり、相関関係があるのか、ないのか数値的な観点から判断したいわけです。


この相関関係を一発で算出してくれる関数が、Excelには用意されています。その関数は「CORREL関数」です。


F1にCORREL関数をつかって、相関関係を算出してきます。


CORREL関数の引数は、CORREL(配列1,配列2)となっていて、それぞれの数値の範囲なので、CORREL関数ダイアログボックスを表示せず、手入力で数式をつくっても、問題はないかと思われます。


F1の数式は、

=CORREL(B2:B11,C2:C11)


関数の数式としては、簡単に作成できます。

0.19457と相関係数は、算出されました。


算出はされたのですが、いったいどのようなことがわかったというのでしょうか?

相関係数は、「1」に近ければ、「正の相関」が強いと判断できます。

「-1」に近ければ「負の相関」が強いわけですが、「0(ゼロ)」に近いのであれば、「無関係」ということが判断できるようになっています。


今回は、0.19457ですから、「1」よりも「0(ゼロ)」に近いと判断できますので、「無関係」であると、仮説を立てることができるわけです。


Writingの数値と、Listeningの数値をそれぞれ降順にしてみたデータで、算出してみましょう。

 

全員のデータをWritingが大きければ、Listeningも大きいデータとしましたから、F1の相関係数は、0.93992と算出されました。


今回の結果は、「0(ゼロ)」よりも「1」に近いので、「関係性」があると判断できるわけですね。


なので、数値だけ集めただけの資料ではもったいなので、色々な角度から見てみるといいのかもしれませんね。


最後に、ちょっと注意しないといけないことがあって、CORREL関数をつかって簡単に算出することはできるのですが、『疑似相関』といって、隠れた要因があるために、相関関係があるように見えたりもします。


ただ、業務を改善したいなど、どこから考えたらいいのかわからない、つまりゼロから過程を建てるのは難しい場合は、CORREL関数をはじめとした、様々な関数をつかい、その結果から仮説を生んで、検討していくというのもいいのかもしれませんね。

3/11/2021

Excel関数辞典 VOL.44。ISPMT関数~KURT関数【dictionary】

Excel関数辞典 VOL.44。ISPMT関数~KURT関数

<Excel関数>

今回は、ISPMT関数~KURT関数までをご紹介しております。

ISPMT関数

アイエスピーエムティー:イズ・ペイメント

元利均等返済における指定期間の利息を算出:Lotus1-2-3互換性維持

ISPMT(利率,期,期間,現在価値)



ISREF関数

イズリファレンス

対象がセル参照の場合にTRUEを返す

ISREF(テストの対象)



ISTEXT関数

イズテキスト

対象が文字列の場合にTRUEを返す

ISTEXT(テストの対象)



JIS関数

ジス

半角文字を全角に変換する

JIS(文字列)



KURT関数

カート

データセットの尖度(せんど)を算出

KURT(数値1,[数値2]…)

3/09/2021

Excel。VBA。読み込んだデータで、月が替わる行に小計行を追加したい【The end of the month】

Excel。VBA。読み込んだデータで、月が替わる行に小計行を追加したい

<Excel VBA>

データを読み込んだら、ついでに、面倒な処理を自動的に行わせることができたら便利ですよね。


例えば、次のようなデータを読み込んだとします。


読み込んだ後に自動的に、月が替わったら、行を追加して、追加した行に月ごとの小計を算出したいとします。

こんな感じですね。


月が替わるところを見つけて、行を挿入して、さらに、月ごとの小計を算出するだけですが、イチイチこの作業を繰り返すのは面倒ですね。


しかも、月ごとのデータの件数が異なっていれば、SUM関数などの引数の範囲をその都度設定するのは、さらに面倒です。


そこで、Excel VBAでマクロを作ってしまうと、大幅に時短できますし、何より楽ができます。


色々なプログラム文があるとは思いますが、次のように作ってみました。


Sub 月別小計()

    Dim i As Long

    Dim mon As Integer

    Dim n_mon As Integer

    Dim sub_t As Long

        

    i = 2

    mon = Month(Range("a2"))

    sub_t = 0

    

    Do While Cells(i, "a") <> ""

       n_mon = Month(Cells(i, "a"))

       

       If mon = n_mon Then

            sub_t = sub_t + Cells(i, "b")

       Else

            Cells(i, "a").EntireRow.Insert

            Cells(i, "a").Value = mon & "月合計"

            Cells(i, "b").Value = sub_t

            sub_t = 0

            mon = n_mon

       End If


       i = i + 1

       

       If Cells(i, "a") = "" Then

            Cells(i, "a").Value = mon & "月合計"

            Cells(i, "b").Value = sub_t

            i = i + 1

       End If

    Loop

End Sub


それでは、説明していきます。

お馴染みの変数宣言の文からスタートですね。

Dim i As Long

Dim mon As Integer

Dim n_mon As Integer

Dim sub_t As Long


monは、今月の値をいれる変数です。

n_monは、次のデータの月をいれる変数です。

sub_tは、月ごとの小計を算出する時に使う変数です。


初期値を設定します。

i = 2

mon = Month(Range("a2"))

sub_t = 0

    

mon = Month(Range("a2")) は、最初のデータの月を代入しています。


Do While Cells(i, "a") <> "" ~ Loop をつかって繰り返し処理をしています。


Do While Cells(i, "a") <> "" は、A列の値が空白でないという意味ですね。

つまり、データが無くなるまで繰り返し処理を行いたいわけです。


繰り返し処理のDo Whileの中を確認していきましょう。


n_mon = Month(Cells(i, "a")) は、次のデータの月の値を代入しています。


月替わりしたかどうかを確認するために、If~Then~Else~End Ifで条件分岐させた処理をします。


If mon = n_mon Then で、今までの月の値と、このデータの月の値を比べます。


もしも、同じだったらば、

sub_t = sub_t + Cells(i, "b") で、小計の合計値に、このデータの売上高のデータを追加していきます。


Else で、月の値に違いが発生している場合、つまり月が替わった時の処理を行います。

Cells(i, "a").EntireRow.Insert で、該当のセルの下側に空白行を追加することができます。


Cells(i, "a").Value = mon & "月合計" は、追加した行に、「○月合計」という見出しを入力させています。


Cells(i, "b").Value = sub_t は、月ごとの合計値を入力しています。


次の2行は、月が替わったので、初期化の作業をしています。

sub_t = 0

mon = n_mon


次の行のデータを参照させるための作業が、i = i + 1 です。


これで、メインのプログラムは終わっているのですが、このまま実行すると、空白行まで繰り返すという処理なので、最終月。

今回は、12月にあたりますが、その合計値の行を表示する作業を行わないで終了してしまいます。


そこで、次のプログラム文をいれました。

If Cells(i, "a") = "" Then

    Cells(i, "a").Value = mon & "月合計"

    Cells(i, "b").Value = sub_t

    i = i + 1

End If


これで、月が替わった時に、自動的に空白行を追加してその追加した行に月ごとの合計値を算出することができました。


このように、ちょっとしたプログラム文でも作業効率を改善できるかもしれませんので、Excel VBAで対応できそうなところがあれば、少しずつでも移行していくといいのかもしれませんね。