今週のFacebookページの投稿 2017/09/11-2017/09/17

今週のFacebookページの投稿　2017/09/11-2017/09/17

＜Facebookページ＞

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

9月11日
Wordショートカット。
Alt + Shift + F2 キーで[保存] コマンドを選択します。

9月12日
Wordショートカット。
Alt + Shift + F7 キーで[リサーチ] 作業ウィンドウを表示します。

9月13日
Wordショートカット。
Alt + Shift + F9 キーでフィールドの実行結果を
表示しているフィールドから
GOTOBUTTON フィールド
または MACROBUTTON フィールドを実行します。

9月14日
Wordショートカット。
Alt + Shift + F10 キーで実行できる操作のメニュー
またはメッセージを表示します。

9月15日
Wordショートカット。
Alt + Shift + F12 キーで目次コンテナーが
アクティブの状態で
目次コンテナーの [目次] ボタンを選択します。

9月16日
Wordショートカット。
Ctrl + Alt + F1 キーでマイクロソフトのシステム情報を表示します。

9月17日
Wordショートカット。
Ctrl + Alt + F2 キーで[開く] コマンドを選択します。

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

Excelのショートカット一覧はこちらで書いております。
http://yandss.p2.weblife.me/shortcutkey.html

Excel。数値を一つずつセルごとに表示するにはどうしたいいの？【Number】

Excel。数値を一つずつセルごとに表示するにはどうしたいいの？

＜LEFT&RIGHT&COLUMN関数＞

文字を一つずつセルごとに表示する方法をご紹介したところ、
その下に表示していた数値も同じようしても、
一つずつのセルに表示するのがうまくできないので、
教えてほしいということで、

今回は、文字ではなくて、
数値を一つずつセルごとに表示する方法をご紹介していきます。

前回は、【長宗我部元親】さんを使って、1行目を作ったわけですね。
D1の数式は、
=MID($A$1,COLUMN(A1),1)
でしたが、A1を123456789と変更してみましょう。

数値なのに、1行目は、文字と同じように、
左側から始まってしまっていますね。

3行目は、右側に詰まっていますよね。

前回のMID&COLUMN関数では、対応できないわけなんですね。

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

今回は、右側に一の位が表示したいわけですね。
となると、文字列の一番右側の一文字をN列に表示したいわけです。

そこで、右側の文字を抽出できる、RIGHT関数を使うのと、
N列が一の位。M列が十の位。L列が百の位。とスライドさせる。

すなわち、オートフィルで数式をコピーすることも考慮すると、
COLUMN関数も使った方がいいでしょう。

では、N1に次のような数式を作ってみましょう。
=RIGHT($A$3,COLUMN())
結果は、当然ながらダメですよね。

この数式だと、COLUMN()はN列=14を使ってしまうので、
右側から14文字を抽出しちゃいます。

一文字だけほしいので、1にする必要があります。ということは、

15- COLUMN()と修正してみましょう。

一の位が表示できましたので、オートフィルで数式をコピーしてみましょう。

ありゃま。完成ではないんですね。

十の位を表示したいM列は、右側から2文字分を表示してしまっています。

89のうち、今度は、左側の1文字を抽出したいわけなので、
LEFT関数を使って、数式をアレンジしていきます。

=LEFT(RIGHT($A$3,15-COLUMN()),1)

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

アレレ。まだ完成じゃないんですね。

一桁ずつ表示できたと思ったら、
数値がない桁が空白になっていませんね。

これを解決しないといけませんね。

IF関数とか色々考えてしまいそうですが、
よく考えれば、左側から1文字抽出するようになっていますので、
最初に空白を追加した状態にしてあげるとよさそうですね。

数式を次のようにアレンジしていきます。
=LEFT(RIGHT(" "&$A$3,15-COLUMN()),1)

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

これで完成しましたね。
動きを確認してみましょう。
E列を使って動きを確認してみましょう。

RIGHT(" "&$A$3,15-COLUMN())の引数は、
15-5なので、右側から10文字を抽出した結果は、
” 123456789”と空白が先頭についています。

その左側の1文字なので、空白が抽出されます。

このように、数値の場合は、ちょっと複雑になりますが、
請求書などで使うこともあるかもしれませんね。

Access。クロス集計クエリで日付を月ごとにグループ化する方法【Format関数】

Access。クロス集計クエリで日付を月ごとにグループ化する方法

＜クロス集計クエリ Format関数＞

Accessではクエリを使うことによって、
様々なデータを抽出することが出来ますが、
意外と、ちょっとした事が出来ないで困るということを耳にしますので、
今回は、下記のクエリをアレンジしていきながら、
日付を月ごとにグループ化する方法までをご紹介していきます。

デザインビューで確認してみると、

というクエリですが、データシートビューで見ると縦長でわかりにくいので、
縦横のクロス集計クエリに変更してみましょう。

現在は選択クエリなので、クロス集計クエリをクリックしましょう。

すると、デザインビューの設定項目が変更されたことが確認出来ますね。

「集計」行が加わり、グループ化の設定が出来るようになりましたし、
「行列の入れ替え」行も加わりましたね。

ここを設定すると、クロス集計の縦軸と横軸の設定をする事が出来ますね。

今回は、日付を行にして、商品名を列に、金額を値に設定していきます。

集計行の変更箇所は、金額フィールドを合計に変更します。

行列の入れ替え行の変更箇所は、
日付フィールドには、行見出し
商品名フィールドには、列見出し
金額フィールドには、値
を設定しました。

それでは、データシートビューに変えて確認してみましょう。

選択クエリをクロス集計クエリに変更することが出来ましたね。

ところで日付ですが、
年月ごとにまとめて算出したい場合はどうしたらいいのでしょうか？

Excelのピボットテーブルならば、
簡単にグループ化することが出来ますので、
Accessのクエリでも簡単に設定できると思いきや、
残念ながら、そのような機能はないのです。

ではどうしたらいいのかというと、
Format関数を使うことで、年月でグループ化することが出来ます。

それでは、デザインビューに戻して、日付フィールドをアレンジしてみましょう。

日付フィールドのままだと、同じフィールド名が使えませんので、
年月というフィールドを作っていきます。

年月: Format([日付],"yyyy/mm")

とアレンジするだけで、日付を月ごとにグループ化することが出来ます。

"yyyy/mm"は説明するまでもないと思いますが、
yyyyは年を西暦の4桁表示で、mmは月を表しています。

では、データシートビューに切り替えてみましょう。

このように月ごとにグループ化することができましたね。
ところがこのクロス集計クエリ、
月ごとの合計値がないので、追加してみましょう。

デザインビューに切り替えます。

新しいフィールドを作ります。

挿入場所はどこでもOKですが、今回は最後尾に作ります。

金額フィールドで金額を使っていますので、
合計というフィールドで作成する事にします。

フィールドには、合計: 金額
テーブルには、Q受注表
集計には、合計
行列の入れ替えには、行見出し
と設定したら、データシートビューに切り替えてみましょう。

このように、合計値を表示することが出来ましたね。

Excelでは簡単に出来る事も、
Accessでは、ひと工夫する必要もありますので、
知っておくのもいいかもしれませんね。

今週のFacebookページの投稿 2017/09/04-2017/09/10

今週のFacebookページの投稿　2017/09/04-2017/09/10

＜Facebookページ＞

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

9月04日
Wordショートカット。
Alt + F6 キーでダイアログ ボックスを開いたまま、
ドキュメントを切り替えます
 (この操作をサポートするダイアログ ボックスで使用できます)。

9月05日
Wordショートカット。
Alt + F7 キーで次のスペル ミスまたは文法エラーを検索します。

9月06日
Wordショートカット。
Alt + F8 キーでマクロを実行します。

9月07日
Wordショートカット。
Alt + F9 キーですべてのフィールド コードと
その実行結果の表示を切り替えます。

9月08日
Wordショートカット。
Alt + F10 キーで
[オブジェクトの選択と表示] 作業ウィンドウを表示します。

9月09日
Wordショートカット。
Alt + F11 キーでMicrosoft Visual Basic コードを表示します。

9月10日
Wordショートカット。
Alt + Shift + F1 キーで前のフィールドに移動します。

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

Excelのショートカット一覧はこちらで書いております。
http://yandss.p2.weblife.me/shortcutkey.html

Excel。Excel2013で第2軸のグラフを作る方法を再確認してみよう。【Graph】

Excel。Excel2013で第2軸のグラフを作る方法を再確認してみよう。

＜複合グラフ＞

複合グラフを使うことで、
Excelのグラフは様々な表現をすることができるようになりますが、
Excel2013以前の作り方に慣れていると、
Excel2013以降では、作り方が異なっていて、
わかりにくいということをお聞きしますので、
今回は、Excel2013以降での第2軸の複合グラフの作り方を再確認していきます。

次の表を複合グラフにしていきます。

B列の売上高とC列の構成比では、数値の大きさが違い過ぎるので、
同じ縦軸を使ったグラフで表現すると、
わかりにくいグラフになってしまいます。

例えば、集合縦棒グラフにしてみると、

当然、構成比が見えなくなってしまうわけですね。

コレじゃダメですよね。

そこで、登場するのが、第2軸を使った複合グラフなんですね。

では、A2:C7を範囲選択して、
まずは、Excel2013以前のバージョンっぽい作り方でやってみましょう。

挿入タブの縦棒の2-D縦棒から「集合縦棒グラフ」をクリックして、
集合縦棒グラフを挿入しましょう。

今までのバージョンでしたら、系列 "構成比"をクリックして、
ダイアログボックスを表示して…という作り方でした。

当然、それでもOKなのですが、簡単に出来るようになっているので、
Excel2013での作り方で紹介していきます。

では、グラフをアクティブにして、
グラフツールのデザインタブにある「グラフの種類の変更」をクリックします。

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

すべてのグラフの「組み合わせ」に合わせて、
構成比のグラフの種類を『マーカー付き折れ線』に変更して、
第2軸にチェックマークをつけます。

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

これで基本的な第2軸を使った複合グラフを作ることが出来ましたね。

以前のように、系列 "構成比"のダイアログボックスを表示して、
第2軸にするという作業はしなくても、
非常に簡単に作成することが出来ましたが、

Excel2013以降では、
そもそも、「複合グラフの挿入」というボタンが登場しましたので、
次は、そのボタンを使った作成方法を確認してみましょう。

A2:C7を範囲選択して、挿入タブのグラフにある、
「複合グラフの挿入」をクリックしましょう。

そして、真ん中の「集合縦棒-第2軸の折れ線」をクリックしてみましょう。

なんと、これで第2軸を使った複合グラフが、
簡単に作成することが出来てしまうのです。

このように、
第2軸を使った複合グラフが簡単に作れるようになったのですが、
逆に考えると、
スタンダードなグラフになったことを意味するのではないでしょうか？

となると、今までのようなスタンダードなグラフだけではなくて、
複合グラフを使った様々な表現のグラフを現場で作る必要が
発生しているのではないでしょうか？

ということで、少しずつ、グラフのテクニックを増やしていくといいですね。

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。マクロ013。見出しと罫線をマクロVBAで設定する方法

Excel。マクロ013。見出しと罫線をマクロVBAで設定する方法

＜VBA＞

事務職でも、少しExcel VBAを知っていると、
確かに作業効率は改善されるようですが、
難しいと感じている人も多いようです。
とはいえ、少しずつでいいので、慣れていくといいですね。

ということで、
今回は、
【見出し行に塗りつぶしと中央揃え】と【罫線】
について確認してみましょう。

次の表があります。

この表を、次のように加工したいマクロVBAを作っていきましょう。

加工する内容は、
見出し行を、「中央揃え」にして、「塗りつぶし」を行うようにします。

表には、「罫線」を設定して、外枠は「太線」にします。

このマクロVBAを使うことで、
常にデータを入力した後に、
見出し行と罫線を瞬時に設定することが出来ますので、
知っていると便利なマクロVBAですよ。

では、どのようなマクロVBAなのか見てみましょう。

Sub 見出し行()
    Range("a1:g1").HorizontalAlignment = xlCenter
    Range("a1:g1").Interior.ColorIndex = 15
    Range("a1").CurrentRegion.Borders.LineStyle = xlContinuous
    Range("a1").CurrentRegion.BorderAround LineStyle:=xlContinuous, Weight:=xlMedium
End Sub

たったこの4行で、出来てしまうんですね。

1行目。
Range("a1:g1").HorizontalAlignment = xlCenter
Range("a1:g1")は、A1:G1を範囲選択します。

HorizontalAlignmentプロパティは、
セルの文字の配置を指定するプロパティです。

xlCenterは設定値で、中央揃えをすることが出来ます。

ちなみに、xlLeftは左揃えで、xlRightは右揃えを設定することが出来ます。

2行目。
Range("a1:g1").Interior.ColorIndex = 15
Interiorオブジェクトは、
セルに塗りつぶしの設定を出来るプロパティです。

ColorIndexプロパティは、カラーパレットと連動した番号です。

3行目。
Range("a1").CurrentRegion.Borders.LineStyle = xlContinuous
A1から始まる表（CurrentRegion）を選択します。

Bordersプロパティは、セルに罫線を引くことが出来るプロパティです。

今回は、使いませんが、例えば、セルの下端に線を引くには、
Borders(xlEdgeBottom)とすると
セルの下端に線を設定することが出来ますし、

Borders(xlDiagonalUp)とすれば、
左下から右上への斜線を設定することが出来ます。

LineStyleプロパティは、
罫線の種類を指定することが出来るプロパティです。

xlContinuousは、線の種類のうち、実線を設定することが出来ます。

ちなみに、xlDashを使うと、破線。xlDotは点線を引くことが出来ます。

4行目。
Range("a1").CurrentRegion.BorderAround LineStyle:=xlContinuous, Weight:=xlMedium
A1から始まる表（CurrentRegion）を選択します。

BorderAroundメソッドは、
セル範囲の外枠に罫線を引くのに便利なメソッドです。

そして、LineStyle:=xlContinuousですが、
3行目で紹介したのと同じで、
罫線の種類を実線で引きますよという設定をしています。

最後に、Weight:=xlMediu
このWeightプロパティは、
罫線の太さを指定することが出来るプロパティです。

ただし、このWeightプロパティですが、
線の種類によっては、
想像しているような太さで表示されないことがありますので、
注意が必要です。

このように、たった4行ですが、Excelでお馴染みの作業ばかりですので、知っておくと作業効率が改善することが出来ますよ。