Excel VBA。Excel VBAで日付を抽出する方法を改めて確認してみよう。【filter】

Excel VBA。Excel VBAで日付を抽出する方法を改めて確認してみよう

＜Excel VBA＞

日頃同じような処理をする場合、面倒なのでマクロにすることが多くあります。今回ご紹介する日付を抽出するというケースは、昔からよく作られるマクロなのですが、Excel 2003で作った場合にはExcel 2007以降では、すんなり稼働しないので、修正する必要があります。

では、早速Excel VBAで日付を抽出するマクロを作成していきましょう。
次のような表を用意しました。

【基本形から確認しましょう。】
営業日が2020/2/15のみを抽出するための構文をつくってみましょう。

Sub 日付抽出()
    Range("a1").AutoFilter field:=1, Criteria1:="2020/2/15"
End Sub

たったこれだけですが、実行してみましょう。

このように抽出することができました。
構文を説明すると、
Range("a1").AutoFilterは、A1を起点として、オートフィルターを設定します。

field:=1, Criteria1:="2020/2/15"
こちらは、
field:=1は、1列目でフィルターをかけます。

そして、
Criteria1:= "2020/2/15"
Criteriaは、条件のことですね。

意外と簡単なのですが、注意する必要があります。
それが、『表示形式』です。

たとえば、営業日の表示形式を年月日に変更して、マクロを実行してみましょう。

すると、抽出してくれないことがわかります。

ようするに、日付の抽出では、シリアル値を考えるのではなく、表示形式を考えないといけませんので、注意が必要です。
固定日の抽出というケースは少ないと思いますので、次は、今年のデータを抽出するようにアレンジしてきます。
データを一部変更しました。

Exce VBAの構文を次のようにアレンジしました。
Sub 日付抽出()
    Range("a1").AutoFilter field:=1, Operator:=xlFilterDynamic, Criteria1:=xlFilterThisYear
End Sub
実行してみましょう。

このように、2020年だけが抽出されましたね。
構文を確認しておきましょう。
Operator:=xlFilterDynamic
OperatorにxlFilterDynamicという定数を設定することで、様々な抽出を設定することができます。今年という条件で抽出したいので、
Criteria1:=xlFilterThisYear
と設定すればいいということになります。

このxlFilterDynamicは、Excelの日付フィルターのところを行っています。

この中の、今年が、「xlFilterThisYear」というわけです。
なので、
日にち
今日　xlFilterToday
昨日　xlFilterYesterday
明日　xlFilterTomorrow

週
今週　xlFilterThisWeek
先週　xlFilterLastWeek
来週　xlFilterNextWeek

月
今月　xlFilterThisMonth
先月　xlFilterLastMonth
来月　xlFilterNextMonth
1月　xlFilterAllDatesInPeriodJanuary
2月　xlFilterAllDatesInPeriodFebruray
3月　xlFilterAllDatesInPeriodMarch
4月　xlFilterAllDatesInPeriodApril
5月　xlFilterAllDatesInPeriodMay
6月　xlFilterAllDatesInPeriodJune
7月　xlFilterAllDatesInPeriodJuly
8月　xlFilterAllDatesInPeriodAugust
9月　xlFilterAllDatesInPeriodSeptember
10月　xlFilterAllDatesInPeriodOctober
11月　xlFilterAllDatesInPeriodNovember
12月　xlFilterAllDatesInPeriodDecember

四半期
今四半期　xlFilterThisQuarter
前四半期　xlFilterLastQuarter
来四半期　xlFilterNextQuarter
第1四半期　xlFilterAllDatesInPeriodQuarter1
第2四半期　xlFilterAllDatesInPeriodQuarter2
第3四半期　xlFilterAllDatesInPeriodQuarter3
第4四半期　xlFilterAllDatesInPeriodQuarter4

年
今年　xlFilterThisYear
昨年　xlFilterLastYear
来年　xlFilterNextYear

期間
今年の初めから今日まで　xlFilterYearToDate

という定数を設定することで、日付を抽出することができます。
ちなみに、2月なのですが…
期間内の全日付:2月xlFilterAllDatesInPeriodFebruray
2月の単語は、「February」のはずですが、なぜか、「Februray」と間違っていますが、間違っているのが正しいので、入力する時に注意する必要があります。

Excel Technique_BLOG Categoryに追加しました。2020/2/21

Excel Technique_BLOG Categoryに追加しました。

＜目次サイト＞

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

Excel。スケジュール帳。期間の日付を入力するとセルが自動的に塗りつぶすガントチャート

スケジュール帳やガントチャートなどを作るときに、開始日と終了日を入力するとその期間のセルを自動的に塗りつぶすことが出来たら便利ですよね。

＜続きはこちら＞
Excel。スケジュール帳。期間の日付を入力するとセルが自動的に塗りつぶすガントチャート
https://infoyandssblog.blogspot.com/2015/03/exceldiary.html

Excel。条件付書式のデータバーを数値に重ねない方法

スパークラインではありませんが、セル内に棒グラフを表示することが出来るようになります。

＜続きはこちら＞
Excel。条件付書式のデータバーを数値に重ねない方法
https://infoyandssblog.blogspot.com/2015/03/excel2013conditional-formatting.html

Excel。塗りつぶしたセルを数えられると3交代シフトも作れちゃう

午前・午後・夜間の3交代シフトを作れないかなぁ～といわれたもので、こんなものを作ってみた

＜続きはこちら＞
Excel。塗りつぶしたセルを数えられると3交代シフトも作れちゃう
https://infoyandssblog.blogspot.com/2015/03/excelshift-table3.html

Excel。お客の年齢階層別ピラミッドグラフをつくってみる。

ピラミッドグラフというと、グラフがピラミッド。三角錐のグラフではなくて、よく、年齢別の人口分布などに使われるグラフの事ですね。

＜続きはこちら＞
Excel。お客の年齢階層別ピラミッドグラフをつくってみる。
https://infoyandssblog.blogspot.com/2015/03/excelgraph_25.html

Excel。部分二重円グラフを作るにはドーナツグラフと円グラフの組み合わせで作ります

Excel。部分二重円グラフを作るにはドーナツグラフと円グラフの組み合わせで作ります

＜部分二重円グラフ＞

Excelのグラフは、アイディアによって様々なグラフを作れます。

今回は、円グラフの一部分が二重になっている円グラフの作り方を確認していきます。

単純な二重ドーナツグラフならば簡単に作成することができますが、今回のように、円グラフだけど、二重になっているところをどのようにするのか？というのがポイントです。

今回このグラフを作るための表を次のように用意しました。

円グラフを最初から二重で描くことができないので、最初は、ドーナツグラフで作る必要があります。

その場合、左側のデータが内側に、右側のデータが外側に描かれますので、表の作り方に注意する必要があります。

A2:C5を範囲選択して、ドーナツグラフを描きます。

挿入タブの「円またはドーナツグラフの挿入」にあるドーナツをクリックします。

二重ドーナツグラフが描かれました。

グラフタイトルと凡例を今回は削除します。

また、グラフエリアは白色なので、説明上わかりにくいので、見やすいように、グラフエリアをグレーに変更しておきます。

ドーナツグラフ自体（系列が「地域」でも「売上高」のどちらでもかまいません。）をクリックして、グラフのデザインタブにある「グラフの種類の変更」をクリックします。

グラフの種類の変更ダイアログボックスが表示されます。

すべてのグラフタブの「組み合わせ」が選択されていることを確認します。

系列名の上段のデータ。
今回は、売上高という外側のドーナツのグラフの種類を「円」に変更します。

系列名の下段のデータ。
今回は、地域という内側のドーナツは、グラフの種類はそのまま「ドーナツ」にしておき、第2軸にチェックマークをいれて、OKボタンをクリックします。

塗りつぶしの色を変更し、今回のグラフは枠線が白になっているので、枠線は色なしに変更します。

あとは、データラベルを表示させていきます。

データラベルを表示したら、フォントサイズを修正して完成です。

今回のグラフは、円グラフを、第2軸にすることができないというのが、ポイントでした。

最初ドーナツグラフを作成しておいてから、外周のドーナツグラフを円グラフに変更し、内側のドーナツを第2軸に変更するという流れが必要になるのがわかりにくいところですね。

現場では様々なグラフを釣れるようになるとわかりやすい資料をつくれるようになるかもしれませんので、色々なグラフに挑戦してみるといいかもしれませんね。

今週のFacebookページの投稿 2020/2/10-2020/2/16

今週のFacebookページの投稿　2020/2/10-2020/2/16

＜Facebookページ＞

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

2月10日
Excel。COMPLEX関数。
読み方は、コンプレックスで、複素数を表す文字列を生成します

2月11日
Excel。CONCAT関数。
読み方は、コンキャットで、複数の文字列を統合します

2月12日
Excel。CONCATENATE関数。
読み方は、コンカティネイトで、複数の文字列を統合します

2月13日
Excel。CONFIDENCE関数。
読み方は、コンフィデンスで、正規分布で母集団に対する信頼区間の1/2幅を算出します

2月14日
Excel。CONFIDENCE.NORM関数。
読み方は、コンフィデンス・ノーマルで、正規分布で母集団に対する信頼区間の1/2幅を算出します

2月15日
Excel。CONFIDENCE.T関数。
読み方は、コンフィデンス・ティーで、t分布で母集団に対する信頼区間の1/2幅を算出します

2月16日
Excel。CONVERT関数。
読み方は、コンバートで、数値の単位を変換します

Excelテクニック and  MS-Office recommended by PC training
https://www.facebook.com/exceltechniqueandmsoffice/

Excel。時間の条件分岐はTIMEVALUE関数を使うのが便利です。【TIMEVALUE】

Excel。時間の条件分岐はTIMEVALUE関数を使うのが便利です。

＜TIMEVALUE関数＞

作業時間が、時間以内に終了しているならば、「○」時間を超過しているならば、「タイムオーバー」というような判断分岐をする場合、意外と落とし穴があるので確認しておきましょう。

次の表を用意しました

D列の所要時間は、終了時間から開始時間を減算した結果を表示してあります。
数式は、
=C2-B2
ですね。

時間以内に終了しているならば、「○」時間を超過しているならば、「タイムオーバー」という判断分岐をしたいので、当然IF関数を使うわけですね。

0:50を超過していたらということを論理式に設定しますので、
D2<=0:50
と設定してみると、正しくありませんと表示されてしまいます。

一応、OKボタンをクリックしてみると、

問題があると、冷たい反応…
どうやら文字として認識しようとしています。

では、次のように数式を修正してみるとどうでしょうか？

論理式を、
D2<="0:50"
としました。「”（ダブルコーテーション）」で0:50を囲んでみました。

今度は、うまくいきそうな感じがしますので、OKボタンをクリックして、数式をオートフィルでコピーしてみましょう。

成功したと思ったら、なんと、50分オーバーしているところも「○」が表示されてしまっています。

どうしてでしょう？

ここに落とし穴があるわけです。

そもそも、時間通しを減算していますので、算出されているD列の値は、時間なわけです。

Excelは、1日を1としたシリアル値を使っていますから、例えばExcelで1時間は1/24ということになります。

今回算出されているD列も、表示形式を標準にしてみましょう。

1以下なのが本来の姿です。

そして、”0:50”としたことで、文字列になっています。

文字と数値では数値の方が文字コード上小さいので、IF関数の結果、すべて「○」になってしまったわけです。

【TIMEVALUE関数を使うと問題解決】

問題なのは、文字型なのと時刻型という「型」が異なっているのが原因です。

そこで、TIMEVALUE関数を使うと、時刻型とすることができるので、問題を解決することができます。

では、IF関数を修正していきます。

論理式を次のようにTIMEVALUE関数を使って修正します。
D2<=TIMEVALUE("0:50")

OKボタンをクリックしたら、オートフィルで数式をコピーしましょう。

今回は、50分超過しているデータは、「タイムオーバー」と表示されていることが確認できました。

今回のように、日付や時間を使うときには、Excelであっても、Access同様に「型」を意識して数式を作る必要がありますので、算出結果がおかしな時には、「型」を確認するといいのかもしれませんね。

Excel関数辞典 VOL.25。ERF関数～ERROR.TYPE関数

Excel関数辞典　VOL.25。ERF関数～ERROR.TYPE関数

＜Excel関数＞

今回は、ERF関数～ERROR.TYPE関数までをご紹介しております。

ERF関数
イーアールエフ
誤差関数の積分値を算出
ERF(下限[,上限])


ERFC関数
イーアールエフシー
相補誤差関数の積分値を算出
ERFC(下限)


ERFC.PRECISE関数
イーアールエフシー・プリサイズ
相補誤差関数の積分値を算出　Excel2010
ERFC.PRECISE(下限)


ERF.PRECISE関数
イーアールエフ・プリサイズ
誤差関数の「0～下限」までの積分値を算出
ERF.PRECISE（下限[,上限]）


ERROR.TYPE関数
エラー・タイプ
エラーのタイプを表す数値を算出する
ERROR.TYPE(エラー値)

Excel。ベルヌーイ分布をExcelで確認してみよう。【Bernoulli distribution】

Excel。ベルヌーイ分布をExcelで確認してみよう。

＜COUNTIF関数＞

Office365 Insiderに登場した「RANDARRAY」という関数を確認する表を作ったので、削除するのはもったいないので、今回は、Excelでベルヌーイ分布を紹介していきます。

ベルヌーイ分布というのは、ある事象が発生する・しないのように、2通りしかない事象を「0」と「1」で表す確率変数の分布のことで、比率を考える時の基本となる分布と考えられています。

RANDARRAY関数の確認で作った表をつかうので、サイコロの目の出る確率で、しかも、1と2が出る確率と3～6の出る確率という表で行っていきます。

本来ならば、1と2は0。
3～6を1と置換するのですが、COUNTIF関数を使えば同じなので、置換させずそのままで行っていきます。

100行×10列のデータなので、こんかいのサンプルデータ数は、1000個で行っていきます。

最初は、サイコロを投げる試行を算出しておきます。

1～6の目なので、6つ。そして、3～6の目が4つ。なので、4/6=2/3。

2÷3は割り切れないので、ROUND関数で、小数点第4位までの表示として四捨五入しています。

【データをもとに度数分布表を算出】

サイコロの1～2の目が1000回中に何度登場したのかを算出します。M2の数式は、
=COUNTIF($A$1:$J$100,"<=2")
2以下の数値がいくつあるのかを算出させればいいわけなので、使う関数はCOUNTIF関数で十分ですね。

とても簡単に度数を算出することができました。

なお、範囲に絶対参照を設定しているのは、3～6の目の度数を算出するにあたり、この数式を引用するのが楽なためです。

同じように、M3にサイコロの目が3～6の度数を算出していきます。
M3の数式は、
=COUNTIF($A$1:$J$100,">=3")

条件に比較演算子を使う場合で、直接数値などを使う場合は、「”（ダブルコーテーション）」で囲む必要がありますので、注意しましょう。

今回は、ベルヌーイ分布と比較するために、相対度数表も作成しておきましょう。

相対度数は、「構成比」ですから、N2の数式は、
=M2/$M$4
このように、簡単な数式で算出することができます。
N3も同様です。
=M3/$M$4

【ベルヌーイ分布表をつくる】

O2の数式は、
=1-$L$7
O3は、サイコロを投げる試行と同じです。

ベルヌーイ分布自体の算出は、驚くほど簡単な数式を使って算出することができます。

今回の結果のように、相対度数とベルヌーイ分布の間には、大差がありません。

ベルヌーイ分布に従って得られた多くのデータの相対度数は、ベルヌーイ分布に確率的に一致するようになっています。

仮にサンプルのデータがベルヌーイ分布と、大差が発生する場合には、何か原因があると想定されます。