Access。25日締めなので、それ以降を翌月支払月として計上したい【Closing date】

Access。25日締めなので、それ以降を翌月支払月として計上したい

＜Access＞

月末締めならば、わかりやすいのですが、例えば、10日締めとか25日締めとか仕入の支払いついてありますが、例えば25日締めの場合は、26日～31日までを翌月の支払いに回す必要がありますよね。

それを、Accessのクエリを使って算出してみましょう。

次のT仕入日一覧というテーブルがあります。

仕入日フィールドを使って、最終的に次のような集計一覧表を作りたいわけです。

【25日以降かどうかで判断させる】

仕入日が25日以降かどうかを判断して、25日より大きければ、翌月。そうでなければ、当月と判断できればいいわけですね。

この動きは、ExcelのIF関数と同じ考え方ですね。
Accessでは、IF関数ではなくて、IIf関数を使うと大丈夫そうですね。

それでは、クエリを作っていくわけですが、このIIf関数を使った演算フィールドが少し厄介というか、長くなってしまいます。

まずは、市販のテキストなどに掲載している演算フィールドで作ってみることにします。

作成タブの「クエリデザイン」をクリックして、T仕入日一覧を選択します。

次のようにフィールドを設定していきます。

NOと商品名と仕入日はそのままで、演算フィールドは、支払月として作成します。

支払月: IIf(Format([仕入日],"dd")>25,Format(DateAdd("d",7,[仕入日]),"yyyy/mm"),Format([仕入日],"yyyy/mm"))

説明しないとわけがわかりませんが、単純にExcelのIF関数と同じです。

Format([仕入日],"dd")>25
ここは、論理式で、仕入日フィールドから”日”を抽出して、その数値が25より大きいかを判断させています。

そのためにFormat関数を使っています。

Format関数はAccessでは定番の関数で、指定した書式に合わせて値の表示形式を変更する関数です。

真の場合が
Format(DateAdd("d",7,[仕入日]),"yyyy/mm")
DateAddは、仕入日フィールドのデータの”d”。
すなわち日に、”7”を足す。
26日でも7を足せば、翌月になりますよね。だから7。

そして、支払月がわかるようにしたいので、Format関数をつかって、表示形式を年月である、”yyyy/mm”で設定しております。

偽の場合が、
Format([仕入日],"yyyy/mm")
該当外なので、表示形式を年月にしております。

では、実行してみましょう。

6件目の1月27日は、翌月の2月になっていますよね。
このように25日締めならば、翌月にすることができました。

しかし、演算フィールドが長い！

DataAdd関数とか紹介したいから長くなってしまったように思えます。

なので、もっとシンプルに、仕入日が25より大きいかという判断でOK真ならば+7した日付にするようにすればいいので、次のような演算フィールドでもOKです。

支払月2: IIf(Day([仕入日])>25,Format([仕入日]+7,"yyyy/mm"),Format([仕入日],"yyyy/mm"))

では、実行してみましょう。

先程の演算フィールドと同じ結果になっているのがわかります。あとは仕入高のフィールドも追加しておきます。

【クエリから集計クエリをつくる】

あとは、作成した「Q支払月一覧」クエリから集計用クエリをつくります。

クエリツールのデザインタブにある、「集計」ボタンをクリックして、集計行を表示しましょう。

支払月ごとにまとめますので、「グループ化」にして、仕入高は合計金額を算出したいので、「合計」と設定します。

あとは、実行して確認してみましょう。

このように、年月ごとに仕入高を集計することができましたね。
AccessもExcelと基本的に考え方は変わらないようですね。

今週のFacebookページの投稿 2019/1/21-2019/1/27

今週のFacebookページの投稿　2019/1/21-2019/1/27

＜Facebookページ＞

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

1月21日
Excel。エラー。
#REF!　レフ　正式は。A REFerence to a cell that does not exit （ア・リファレンス・トゥ・ア・コール・ザット・ダズ・ノット・イグジット）です。
セルが参照できない時に登場します。

1月22日
Excel。エラー。
#NAME?　ネーム　正式は、Unrecognized Name （アンリコグナイズド・ネイム）です。
関数名やセル範囲名などの名前が正しくない時に登場します。

1月23日
Excel。エラー。
#NUM!　ナム　正式は、An Invalid Number （アン・インヴァリッド・ナンバー）です。
数値が大きすぎる。または、小さすぎると登場します。

1月24日
Excel。エラー。
#N/A　エヌエー　正式は、No Assign （ノー・アサイン）です。値がない時に登場します。Vlookup関数の時によく見かけますね。

1月26日
Excel。エラーじゃないのですが、表示が#######となると、ビックリしますが、桁あふれしているだけなので、列幅を広げれば大丈夫ですね。

1月27日
Excel。オートSUMボタンの▼で、平均を選べば簡単に平均が算出できますね。

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

Excel。相関図（相関グラフ）を作るにはどうしたらいいの？【Correlation diagram】

Excel。相関図（相関グラフ）を作るにはどうしたらいいの？

＜相関図（相関グラフ）＞

2つの項目が交わるところがわかるグラフで、2つの項目の相関関係を『見える化』したグラフが、【相関図】。

相関グラフともいわれたりしますが、この作り方がちょっと、わかりにくいということなので、今回は、改めて作り方を確認してみましょう。

最初にデータを用意します。

店舗ごとの売上高と来店客数のデータから構成されている、この表の、B列の売上高とC列の来店客数を使って、相関図として作成していきます。

【散布図を作る】

最初に作るのは、『散布図』を描いてからアレンジすることで、相関図を描くことができますので、B1:B13を選択しましょう。

挿入タブの「散布図、またはバブルチャート」の散布図を選択すると、散布図が挿入されます。

ここで、完成！となればいいのですが、残念ながら、横軸がデータの個数になってしまっています。

そして、データラベルも表示されていませんので、どの点が、どの店舗なのか、全くわかりませんね。

【横軸を2つ目の項目にする】

売上高と来店客数との相関図なのに、このグラフでは、売上高しか使って描いていません。
そこで、データの範囲をアレンジしていく必要があります。

グラフツールのデザインタブから「データの選択」をクリックして、データソースの選択ダイアログボックスを表示します。

左側の凡例項目（系列）の編集ボタンをクリックします。系列の編集ダイアログボックスが表示されます。

系列Xの値が空欄になっていますので、もう一つの項目である来店客数のC2:C13を範囲選択して、OKボタンをクリックします。

データソースの選択ダイアログボックスに戻ってきます。

横（項目）軸ラベルに数値が表示されたことがわかりますね。
では、OKボタンをクリックしましょう。

横軸がデータの件数から来店客数にかわりましたね。
交点0からプロットされているデータが離れているので、縦軸・横軸とも最小値を変更しておきましょう。

【データラベルを表示しよう】

プロットされたマーカーだけでは、どこの店舗なのかがわかりませんので、データラベルを表示させていきましょう。

グラフツールのデザインタブにある「グラフ要素の追加」からデータラベルの「その他のデータラベルオプション」をクリックして、データラベルの書式設定作業ウィンドウを表示します。

ラベルオプションのラベルの内容にある、「セルの値」にチェックマークをいれます。

データラベル範囲ダイアログボックスが表示されますので、店舗名のA2:A13を範囲選択してOKボタンをクリックします。

再び、データラベルの書式設定作業ウィンドウに戻って、ラベルオプションのラベルの内容で、Y値のチェックマークを外し、ラベルの位置を上に修正して完成ですね。

このように、相関図を作るには、少しずつアレンジを加えながら作っていく必要がありますね。

Excel。マクロ040。ハイパーリンクを一括挿入（設定）・一括削除をマクロで時短したい【Excel VBA】

Excel。マクロ040。ハイパーリンクを一括挿入（設定）・一括削除をマクロで時短したい

＜Excel VBA：マクロ＞

一覧表などのメールアドレスにハイパーリンクを設定したいとか、ハイパーリンクの設定をまとめて削除したりする場合、ちょっと面倒だったりします。

例えば、次のような表があります。

C列にメールアドレスを入力すると、基本的設定では、ハイパーリンクが設定されるようになっているので、ハイパーリンクでお馴染みの、「青い文字で下線」という設定が自動的にされるようになっています。

入力直後に表示される、オートコレクトオプションの設定で、元に戻すをその都度選択するのも面倒ですね。

範囲選択して一括で削除を行うこともできますが、今回は、Excel VBAでマクロをつくって、時短するにはどのようにしたらいいのか、考えてみましょう。

Sub ハイパーリンク解除()
    Dim i As Long
   
    For i = 2 To 5
        Range("c" & i).Hyperlinks.Delete
    Next
End Sub

では、実行してみましょう。

このように、簡単に削除することができました。

Excel VBAを簡単に説明しておきましょう。

Dim i As Long
お馴染みの変数宣言ですね。基本的に、データが複数ある場合は、繰り返し処理を行うことになるので、変数が必要になります。

そして、
For i = 2 To 5～Next
お馴染みのFOR TO NEXTですね。

2から5まで繰り返すという処理をさせるわけですが、何を処理させるのかというと、

Range("c" & i).Hyperlinks.Delete

読めちゃうと思いますが、C2～C5までのセルに設定されているハイパーリンクを、Delete。すなわち、解除することができます。

たった一行で、ハイパーリンクを解除することができました。

さて、今度は、設定したい場合ですね。

TEXTファイルやCSVファイルのデータをインポートしたものの、ハイパーリンクの設定はされていないわけですね。

そこで、今度は、一括設定したいわけです。

これこそ、時短のためには、Excel VBAが活躍しますね。一軒ずつ設定していたのでは、大変ですね。

Excel VBAでつくってみましょう。
Sub ハイパーリンク挿入()
    Dim i As Long
    Dim lastrow As Long

    lastrow = Range("a1048576").End(xlUp).Row
   
    For i = 2 To lastrow
        ActiveSheet.Hyperlinks.Add anchor:=Range("c" & i), _
            Address:="mailto:" & Range("c" & i).Value
    Next
End Sub

まずは、実行してみましょう。

該当するセルにハイパーリンクを設定することができました。

それでは、確認しておきましょう。

お馴染みの変数宣言ですね。lastrowには、データ最終行をいれます。

Dim i As Long
Dim lastrow As Long

Excelシートの最終行は1048576なので、そこから、一番上。つまりデータがある行までジャンプさせて、そのRow。つまり行番号を変数にいれます。

lastrow = Range("a1048576").End(xlUp).Row
   
For i = 2 To lastrow～Next　で繰り返し処理をします。

ActiveSheet.Hyperlinks.Add anchor:=Range("c" & i),Address:="mailto:" & Range("c" & i).Value

Hyperlinks.Addを使うと、ハイパーリンクを挿入することができます。

anchorは、ハイパーリンクの挿入先を指定することができます。
セルに挿入する場合には、Rangeオブジェクトを指定します。

Address:="mailto:" & Range("c" & i).Value
Addressは、リンク先のアドレスを指定します。
今回は、メールアドレスなので、先頭に、”mailto:”をつける必要があります。

実際の記述では、一文が長くなりますので、「 _」（半角スペース+アンダーバー）を使って行継続文字で書いています。

このようにすることで、簡単に、ハイパーリンクを設定（挿入）したり、解除したりすることができますよ。

Excel Technique_BLOG Categoryに追加しました。2019/01/23

Excel Technique_BLOG Categoryに追加しました。2019/01/23

＜目次サイト＞

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

Excel。y=2x。一次元方程式のグラフの作り方。

「一次元方程式のグラフがうまく作れない」
ので、困っているといわれまして、確かに、通常のようなグラフの作り方では、うまく作ることが出来ないんですよね。
仕事で使いたいケースもあると思いますので、今回は、一次元方程式のグラフ作成についてご紹介しましょう。

＜続きはこちらへ＞
https://infoyandssblog.blogspot.com/2014/02/excely2x.html

Excel。y=x^2-1。二次元方程式のグラフの作り方。

一次元方程式の作り方を紹介したら、二次元方程式は出来るのか？とご質問があったので、早速ご紹介しました。

＜続きはこちらへ＞
https://infoyandssblog.blogspot.com/2014/02/excelyx2-1.html

Excel。初心者ビックリネタでお馴染みの縦横合計をΣで一発算出。

ΣのオートSUMボタンを一度クリックすると、縦横計算が一発で算出されるテクニックがありますので、今回は、それを紹介することにします。

＜続きはこちらへ＞
https://infoyandssblog.blogspot.com/2014/02/excel_25.html

Excel。構成比シェアを求めるには、絶対参照が必須です。

最初の関門になるかなぁ～というテクニックは、絶対参照じゃないかと。
四則演算を勉強して、Σオートsumボタンを紹介するところまでは、初心者の方でも、仕事に着いたばかりの方でも、わりかし、トラブルもなくスキル習得出来るのですが、今日紹介する、絶対参照は、引っかかる方がいるところなんですね。

＜続きはこちらへ＞
https://infoyandssblog.blogspot.com/2014/02/excel_28.html

Excel。元年表示。昭和も平成も新元号の元年が存在する場合は条件付き書式で対応【GENGOU】

Excel。元年表示。昭和も平成も新元号の元年が存在する場合は条件付き書式で対応

＜条件付き書式＋表示形式とAND+OR関数＞

平成の次の元号は、2019年4月1日に発表と決まったようで、5月1日から「新元号元年」がスタートするわけですね。

単純に、新元号元年を表示するには、表示形式で、
[<43586]ggge"年"m"月"d"日";[<43831]"新元号元年"m"月"d"日";ggge"年"m"月"d"日"
と設定するだけで、よかったのですが、顧客名簿や社員名簿などで、平成元年と新元号元年が混ざっているリストだったりすると、この方法では、対応しきれません。

そこで、今回は、上記のように昭和・平成・新元号の元年が混ざっている場合の対応方法を紹介していきます。

【条件付き書式と表示形式で対応】

3回同じことを繰り返すことになりますが、条件付き書式を使うことで比較的簡単に設定できます。

まず、次のような表を用意します。

C列は、B列と同じ日付を表示形式で和暦に変換しています。

C2:C4を範囲選択して、ホームタブの条件付き書式から、「新しいルール」をクリックします。

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

「指定の値を含むセルだけを書式設定」を選択して、次のセルのみを書式設定には、セルの値・次の値の間・1926/12/25・1926/12/31　と設定したら、書式ボタンをクリックします。

セルの書式設定ダイアログボックスが表示されますので、分類の「ユーザー定義」を選択して、種類には、ggg"元年"m"月"d"日"　と入力してOKボタンをクリックします。

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

和暦を確認すると、昭和1年が昭和元年に変わったのが確認できますね。

同じように、平成と新元号も設定してあげればいいわけです。

Microsoftさんのアップデートで新元号は対応すると思いますが、上手くアップデートできない場合などは、表示形式を次のようにすると、対応可能です。

"新元号元年"m"月"d"日"

この新元号の文字を、発表があり次第、新しい元号名に変えてあげればOKですが、アップデートで変わると思います。

省略していますが、大正元年も同じように対応可能です。

【AND関数とOR関数】

しかし、先程の方法ですと、同じような処理を3回繰り返さないといけないわけですね。

そこで、次のようなAND関数とOR関数を使った方法もありますので、合わせてご紹介しておきましょう。

先程設定した条件付き書式を解除しておきましょう。

参考までにD列は、元年のスタート日のシリアル値を表示してあります。

E列には、同年12月31日のシリアル値を表示してあります。

C2:C4を範囲選択して、条件付き書式の新しいルールをクリックします。

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

「数式を使用して、書式設定するセルを決定」を選択して、ボックスには、次の数式を入力します。

=OR(AND(C2>=9856,C2<=9862),AND(C2>=32516,C2<=32873),AND(C2>=43586,C2<=43830))

そして、書式ボタンをクリックして、ユーザー定義で、ggg"元年"m"月"d"日"と設定します。

OKボタンをクリックして完成です。

新元号が、平成元年と表示されていますが、アップデートされて新元号に対応すれば、新元号元年と表示されます。

今週のFacebookページの投稿 2019/1/14-2019/1/20

今週のFacebookページの投稿　2019/1/14-2019/1/20

＜Facebookページ＞

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

1月14日
Excel。
文字と文字も足す（合体）することもできます。&を使うと、簡単にできますね。

1月15日
Excel。
Σ。オートSUMボタンをクリックして、範囲を決定するだけで合計を求めることができます。

1月16日
Excel。
オートSUMボタンで、合計するときに離れている所を計算したい時は、Ctrlキーでクリックすれば、算出できますね。

1月17日
Excel。
エラーが表示されるとビックリしますよね。
けど、ちょっと知っていると、ビックリしなくなります。

1月18日
Excel。エラー。
#NULL!　ヌル　正式は、Null Intersection （ナル・インターセクション）です。
セル指定の「:（コロン）」や「,（カンマ）」がない時や、セル範囲に共通部分がないと登場します。

1月19日
Excel。エラー。
#DIV/0!　ディブゼロ　正式は、DIVided by 0（ディヴァイディッド・バイ・ゼロ）です。
0で割り算をしちゃうと、登場しますね。

1月20日
Excel。エラー。
#VALUE!　バリュー　正式は、Wrong Type Value （ローング・タイプ・バリュー）です。
不適切なデータが入っている時に登場しますね。

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

Excel。積み上げ帯横棒グラフをつくりたい。だけどどうやって、隙間を縮めるの？【Horizontal bar chart】

Excel。積み上げ帯横棒グラフをつくりたい。だけどどうやって、隙間を縮めるの？

＜積み上げ帯横棒グラフ＞

新聞などをみていると、このグラフをExcelで作るにはどうしたらいいのかな？というグラフがありまして、今回は、その中から、「積み上げ横棒グラフ」のアレンジである、『積み上げ帯横棒グラフ』を作っていきます。

ということで、作りたいグラフは、次のようなグラフです。

別に難しくないだろうと思うかもしれませんが、結構厄介なんですよ。これ。

では、グラフを作成する表は、次のような表をイメージするかもしれません。

この表で積み上げ横棒グラフをつくると、棒グラフの間が空きすぎてしまいます。

この隙間というか、間を縮めたいわけですね。

さらに積み上げ横棒グラフには欠点があって、合計値を外側に表示させたいと思っても、できません。

そこで、表を次のようにアレンジします。

簡単にいえば、空白行を挿入しています。
ただ、G2:G5、G6:G9、G10:G13はセルを結合しています。

G1:K13を範囲選択して積み上げ横棒グラフを挿入しましょう。

色々修正する必要があります。

カラーリファレンスを使うと楽なので、最初は、青色の棒グラフを選択します。
赤色のカラーリファレンスの範囲をI1にして、青色を、I2:I13と変更します。紫色も、G2:H13と変更します。

女性のJ列。K列の合計も同じように範囲を変更します。

出来上がってきたように見えますが、品川の縦軸がおかしいことに気が付きます。

変な線があります。

これはどうやら、13行目が空白のデータなので、グラフを描く必要がないと判断されたようです。

そこで、全角スペースというダミーデータをH13に入力すると、線が消えます。

【合計値のデータラベルを表示する】

合計も含めたのは、合計値のデータラベルを表示させるためです。

グラフツールのデザインタブにある、「グラフの種類の変更」をクリックして、グラフの種類の変更ダイアログボックスを表示します。

すべてのグラフの組み合わせを選択して、男性・女性を第2軸にします。

合計は、グラフの種類を集合横棒に変更して、OKボタンをクリックします。

凡例から合計を削除しておきます。

縦軸が表と合致していません。新宿・渋谷・品川の順にしますので、縦（項目）軸をクリックして、書式タブの「選択対象の書式設定」をクリックして、軸の書式設定作業ウィンドウが表示されますので、軸のオプションの「横軸との交点」を最大項目に変更して、「軸位置」を軸を反転するにチェックをつけましょう。

縦（項目）軸はかわりましたが、これは、第1縦軸を変更しただけなので、第2縦軸を表示して同じように処理をします。

グラフツールのデザインタブから「グラフ要素を追加」から軸の第2縦軸をクリックして表示させて反転させます。

あとは、第2軸の横軸と縦軸は不要なので、選択してDELキーで削除します。

ここで、書式タブのグラフ要素から、系列 "合計"を選択して、グラフツールのデザインタブから「グラフ要素を追加」からテータラベルの「外側」をクリックします。

これで、データラベルを外側に表示することができましたので、あとは、フォントサイズを大きくして、棒グラフを太くして完成ですね。

このように、積み上げ帯横棒グラフをつくるのは、すこし面倒ですが、比較するのにわかりやすいグラフを作ることできますので、機会があれば作ってみませんか？