5/31/2017

Excel。棒グラフと積み上げ面グラフを使って、平均値で色分けする。Excel2013版【Area graph】

Excel。棒グラフと積み上げ面グラフを使って、平均値で色分けする。Excel2013版

<集合縦棒グラフ&積み上げ面グラフ>


以前紹介したことがあります、
集合縦棒グラフ&積み上げ面グラフのコンビネーショングラフに関して、
Excel2013だとExcel2010のように、どこをどうしていいのかが、
わかり難くて困っているとのご質問をいただきましたので、
今回は、Excel2013での作り方をご紹介していきます。

まずは、次のようなグラフを作りたいわけです。

オレンジ色が平均値で、グレーが背面色というグラフですね。

そして、用意する表は次のようになります。

C列の平均値は売上高に応じて変動させたいので、

C3の数式は、
=AVERAGE($B$3:$B$7)
D3の数式は、
=600-C3

この600は、塗りつぶしのための縦軸の高さです。

売上高の最高値が555なので、600としてあります。
用途に合わせて修正や、別のセルを参照する数式でもいいでしょう。

A2:D7を範囲選択して、
『複合グラフの挿入』の『積み上げ面-集合縦棒』を選択します。

Excel2013から複合グラフを作成できるボタンが登場しましたので、
せっかくなので使ってみましょう。

複合グラフが挿入されましたが、
希望通りではないので、修正していきます。

デザインタブの『グラフの種類の変更』をクリックします。

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

売上高を集合縦棒
平均値を積み上げ面で第2軸にチェックマークをつけます。
塗りつぶしを積み上げ面で第2軸にチェックマークをつけます。

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

形にはなってきましたので、グラフタイトルに売上高。

凡例から、塗りつぶしを削除したら、
縦軸と第2軸縦(値)軸の両方とも最高値を600に設定します。

縦軸をダブルクリックするか、書式タブのグラフ要素から、
縦 (値) 軸を選択して、選択対象の書式設定をクリックして、
画面左側に作業ウインドウを表示しましょう。

軸の書式設定の『軸のオプション』にして、

最大値を600にします。
同じように、第 2 軸 縦 (値) 軸も最大値を600にします。

なお、この作業ウインドウはグラフ作成中は表示したままにしておきましょう。

これで完成といいたいところなのですが、
積み上げ面グラフの両端が塗りつぶされていないので、
新宿と上野の棒グラフが半分外にはみ出してしまっていて、
見た目がよくありません。

そこで、第2横軸を表示させていきます。

デザインタブの『グラフ要素を追加』の軸から第2横軸をクリックします。

第2横軸は表示できたのですが、グラフ自体がおかしくなってしまいました。

第2横軸を表示するとこのような表示になるだけなので、修正していきます。

第2縦軸をクリックして、軸の書式設定作業ウインドウを表示します。

横軸との交点を、『自動』
『軸を反転する』にチェックマークをつけます。

なぜ、軸を反転させるのかというと、
第2横軸の線だけが最終的に残ってしまうのを避けるためです。

ただ、軸を反転してしまったので、
平均値と塗りつぶしが逆になってしまいましたので、元に戻す作業をします。

デザインタブの『データの選択』をクリックして、
データソースの選択ダイアログボックスを表示します。

凡例項目(系列)の平均値と塗りつぶしを上下入れ替えます。
OKボタンをクリックすると、グラフはここまで出来ています。

第2軸横(値)軸をクリックして、軸の書式設定作業ウインドウを表示します。

『軸位置』を目盛
目盛の『目盛の種類』をなし
ラベルの『ラベルの位置』をなし

すると、積み上げ面グラフが伸びて、背景色になりました。

あとは、第2軸縦(値)軸を消しますので、クリックしてDelキーで削除しましょう。

これで完成しました。

このように、Excel2013ではグラフの作り方が
旧来と異なっているところが見受けられます。

作業ウインドウの項目を確認して作業すると、
【解決】出来るかもしれませんので、アレコレ挑戦してみましょう。

5/29/2017

今週のFacebookページの投稿 2017/05/22-2017/05/28

今週のFacebookページの投稿 2017/05/22-2017/05/28

<Facebookページ>

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

5月22日
Wordショートカット。
Alt + Ctrl + . キーで省略記号

5月23日
Wordショートカット。
Ctrl + Shift + C キーでテキストから書式をコピーします。

5月24日
Wordショートカット。
Ctrl + Shift + V キーでコピーした書式をテキストに適用します。

5月25日
Wordショートカット。
Ctrl + Shift + F キーで[フォント] ダイアログ ボックスを開いてフォントを変更します。

5月26日
Wordショートカット。
Ctrl + Shift + > キーでフォントのサイズを大きくします。

5月27日
Wordショートカット。
Ctrl + Shift + < キーでフォントのサイズを小さくします。

5月28日
Wordショートカット。
Ctrl + 終わり角かっこ (]) キーでフォント サイズを 1 ポイント大きくします。

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

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

5/28/2017

Access。フィールドがないのでクエリで抽出出来ない。そんな時はWhere条件

Access。フィールドがないのでクエリで抽出出来ない。そんな時はWhere条件

<Access Where条件>


Accessのクエリは多岐にわたって、現場で作られるのですが、
希望する条件で抽出する際に知っておきたいテクニックのひとつに、
Where条件】というのがあります。

このWhere条件は、とても有名なのでご存知の方も多いと思いますが、
改めて確認していきましょう。

次のような合計値を算出してあるクエリがあります。

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

集計してグループ化してあります。

さて、このクエリなのですが、
全体の合計数値が算出されているので、
例えば、6月だけの合計値だったら?など、
期間合計のクエリを作るとしたらどうしたらいいのでしょうか?

当然、
「日付フィールドを使ってクエリを作ればいいんだろう」と思われますが、

今のクエリに日付フィールドがない。

では、日付フィールドを追加してみると、どうなるのか確認してみると、

データシートビューで確認すると、

内藤さんが複数件表示されてしまっています。

これは、日付フィールドがグループ化されているので、
別々の日ごとで表示されてしまっていて合計されていません。

そして、そもそも日付フィールドは表示されていては困ります。

そこで、クエリで抽出する歳に、
必要なフィールド以外で条件をクエリに追加するためには、
【Where条件】を使うことによって、対応することが出来ます。

では、デザインビューに変えましょう。

集計のグループ化にある▼をクリックして、
一覧から『Where条件』をクリックしましょう。

自動的に表示のチェックマークが外れていますね。

あくまでも、このWhere条件は、表示したいフィールドではないけれど、
期間集計や条件のある集計をするために使うフィールドだからです。

今回は、2017年6月のデータの集計を行ってみたいと思いますので、

抽出条件に、
Between #2017/06/01# And #2017/06/30#
と入力しましょう。

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

このように、グループでWhere条件を使うことによって、
期間で合計を算出するなどの集計抽出をすることが可能になりますので、
Accessのクエリでは知っておきたいテクニックの一つですね。

さて、Accessのクエリを勉強すると必ず登場してくる、
Where条件なのですが、ある出版社さんの基礎編テキストには、
掲載されていて、
もう一方の出版社さんの基礎編テキストには掲載されていないので、

どちらかだけで、勉強しちゃうと、
知る・知らないが出てしまうので、Where条件

是非使えるようにしてみるといいかもしれませんね。

5/25/2017

Excel。セル結合対応の一行おき塗りつぶしを行うための方法【MOD&COUNTA】

Excel。セル結合対応の一行おき塗りつぶしを行うための方法

<条件付き書式・MOD&COUNTA関数>


一行おきに塗りつぶしを行うには、
MOD&ROW関数と条件付き書式を使うことによって、
表現することができますが、
次のような表の場合はどのようにしたらいいのでしょうか?

AB列の表があります。
このようにデータベースではなくて【表】として作成することもありますが、
A列が結合されているわけですね。ここが問題になるわけですね。

そして完成したいのは、
DE列のように、そのブロックというか、
結合されているセルを一行おきに塗りつぶしたいわけです。

このぐらいの行で塗りつぶしをするならば自力で…と思いますが、
表のサイズが大きくなると、時短の観点からみても、
自力というわけにはいかないでしょう。

MOD&ROW関数と条件付き書式』で解決するように思えますが、
確認してみましょう。

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

新しい書式ルールダイアログボックスが表示されますので、
『数式を使用して、書式設定するセルを決定』をクリックして、
『次の数式を満たす場合に値を書式設定』に

=MOD(ROW(),2)

と設定します。

=MOD(ROW(),2)=1
としていないのには、1という結果になればExcelではTRUEと判定されます。

そして、条件付き書式は、TRUE。
すなわち成立すれば、設定した書式を反映させるようになっていますので、
今回は=1を省略しております。

そして、書式ボタンをクリックして、
塗りつぶしの色を設定してOKボタンをクリックしてみましょう。

やっぱりうまくいきませんね。

中国語とイタリア語が塗りつぶされていますが、
これは、結合されていても、
結合されている最初セル番地のA7とA11が
ROW関数で7と11の奇数で算出されるので、

2で除算すると、余りが1になり、
条件付き書式で設定した書式が反映されますので、
中国語とイタリア語は隣接していても、塗りつぶされているわけです。

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

ポイントになるのは、結合されている列ですね。

このようにやりたいことが、
何かパターンがあるかどうかを見つけるようにすると、
解決方法が見つかりやすいかと思います。

見てみると、【イベント数】が使えそうですね。

英会話が1件目。中国語が2件目。
イタリア語が3件目。フランス語が4件目…

このように文字を数える関数。
つまり、『COUNTA関数』を使ってあげれば解決しそうですね。

では、A2:B18を範囲選択して、
条件付き書式から
新しいルールダイアログボックスを表示するところまでいきましょう。

『次の数式を満たす場合に値を書式設定』に
=mod(counta($a$2:$a2),2)
と設定しましょう。

書式から塗りつぶしたい色を選択したら、OKボタンをクリックしましょう。

すると、セル結合対応の一行おきの塗りつぶしが出来ました。

イマイチどのようになっているのか、わかりにくいので、CD列に
=counta($a$2:$a2)
で算出してみましょう。

算出された結果をみれば、
偶数・奇数に分かれていることがわかりますね。

結合セルを含めた一行おきの塗りつぶしを行いたい場合には、
軸になる結合セルの件数を数えてあげるようにすると、設定できるようです。

5/22/2017

Excel。マクロ007。表の中の空白セルや数式のセル以外を選択する方法【xlCellTypeBlanks】

Excel。マクロ007。表の中の空白セルや数式のセル以外を選択する方法

<VBA:xlCellTypeBlanks>


ExcelのVBAって難しくて…と事務職の方からよくいわれます。
確かに、VBAってプログラム言語ですから難しそうに感じますよね。
けど、VBAを知れば確かに効率的に作業できるし。
ということで、少しずつ慣れていくといいかと思います。

そして今回ご紹介するのは、
表の中の空白セル】を選択する方法をご紹介していきます。
前回までご紹介していた、範囲選択のアレンジって感じですね。

では、次の表があります。

この表の中で空白のセルを選択したいわけです。
Sub 空白セル()
    On Error GoTo エラー
    Range("a1", Range("a1").End(xlDown).End(xlToRight)).SpecialCells(xlCellTypeBlanks).Select
    Exit Sub
 
エラー:
    MsgBox Err.Description
End Sub

この手のVBAですが、まず用意しておきたいものがあります。
それが、エラーに関してです。
空白のセルがなかった場合ということも当然考えられますよね。

なので、
    On Error GoTo エラー
は、エラーだったら、エラーってところにジャンプしてね。という意味です。

エラー:
    MsgBox Err.Description
にジャンプしてきたら、

このようなメッセージボックスが表示することができます。

    Range("a1", Range("a1").End(xlDown).End(xlToRight)).SpecialCells(xlCellTypeBlanks).Select
これは、前回紹介した範囲選択のアレンジで、A1: Range("a1").End(xlDown).End(xlToRight)。A1から最終行の一番右端を範囲選択という意味です。
なお、Bloggerの表示上、Range("a1",で切れていますが、これで一行です。

SpecialCells(xlCellTypeBlanks).Select
これは、xlCellTypeBlanks=空白セルという意味なので、
それをセレクトしてということです。

これだけで、表の中の空白セルを選択することができます。

【数式のセルだけ残して削除する】

このxlCellTypeBlanks以外にも数式のセルなどTypeで指定することができます。
そこで、次の表があります。

B7:C9とE7:E9にはVLOOKUP関数などの数式が設定されています。

今回は3件程度ですので、
数式が設定されているセル以外を削除するという作業は大変ではありませんが、
データ量が増えると範囲選択して削除するだけでも面倒です。

そこで、
数式が設定されているセル以外のデータを削除するという
VBAを作ってみましょう。

Sub 数式残し削除()
    On Error GoTo エラー
    Range("a7:e9").SpecialCells(xlCellTypeConstants, xlNumbers + xlTextValues).ClearContents
    Exit Sub
エラー:
    MsgBox Err.Description
End Sub

今回も、該当セルがないといけないので、エラー時の設定を作っておきましょう。

そして、xlCellTypeBlanksは空白でしたが、
xlCellTypeConstantsは、定数のセル。

つまり数式が設定されているセル以外ですね。

あと、xlCellTypeConstantsには、xlErrors=エラー値。
xlLogical=論理値。
xlNumbers=数値。
xlTextValues=文字
という設定をすることが出来ますので、

今回は、文字や数値を選択したいので、
xlNumbers + xlTextValues
を設定しております。

それを削除したいので、
ClearContents
を使えば、削除することができます。
実際に実行してみると、

このように、数式は削除されませんでした。

たった数行ですが、
これだけでも十分VBAを使って作業効率を改善することが出来ます。

ただ、VBAも大事ですが、
ご覧のように、
#VALUE!のエラーが表示してしまう数式を直せるスキルも大事ですね。

5/21/2017

今週のFacebookページの投稿 2017/05/15-2017/05/21

今週のFacebookページの投稿 2017/05/15-2017/05/21

<Facebookページ>

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

5月15日
Wordショートカット。
Shift + Enter キーで改行することができますね。

5月16日
Wordショートカット。
Ctrl + Enter キーで改ページすることができますね。

5月17日
Wordショートカット。
Ctrl + Shift + Enter キーで段区切りを挿入することができますね。

5月18日
Wordショートカット。
Ctrl + Shift + Space キーで改行をしないスペース

5月19日
Wordショートカット。
Alt + Ctrl + C キーで著作権記号

5月20日
Wordショートカット。
Alt + Ctrl + R キーで登録商標記号

5月21日
Wordショートカット。
Alt + Ctrl + T キーで商標記号

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

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

5/19/2017

Excel。積み上げ横棒グラフに合計値のラベルを表示したいけど、どうしたらいいの?【Horizontal bar chart】

Excel。積み上げ横棒グラフに合計値のラベルを表示したいけど、どうしたらいいの?

<積み上げ横棒グラフ>


積み上げ縦棒グラフに合計値のデータラベルを表示する方法は、
何度かご紹介したことがあるのですが、

今回は、
積み上げ横棒グラフ』の合計値はどうやったらいいのでしょうか?
というご質問。

こういうグラフを作りたいわけですね。

積み上げ縦棒グラフの合計値の場合は、
合計値を第2軸にして折れ線グラフを使っていく方法などをご紹介しておりますが、
今回のように、『積み上げ横棒グラフ』だと、
折れ線グラフを縦で描くこと自体が大変面倒なことになってしまい、
作業効率の面から、あまりよろしくないかと思います。

そこで、第2軸を使うアイディアはいいのですが、
折れ線グラフではないグラフで作成していくと
比較的簡単に合計値のデータラベルを表示することが出来ます。

まず、念のために確認なのですが、
次の表を『積み上げ横棒グラフ』にして
合計値のデータラベルが表示されるか確認してみましょう。

A1:D5を範囲選択して、
挿入タブの『積み上げ横棒グラフ』をクリックしましょう。

すると、『積み上げ横棒グラフ』が挿入されます

データラベルを表示するようにしてみましょう。

Excel2010ならば、
レイアウトタブのデータラベルから「中央」をクリックするのが楽ですね。

しかし、表示してみると、

ご覧の通り、合計値のデータラベルを表示することは出来ません。

そこで、表に合計値の列を追加しましょう。

A1:E5を範囲選択して、
改めて『積み上げ横棒グラフ』を作ってみましょう。

月が縦軸に表示されてしまっていますので、
「行/列の切り替え」を行いましょう。

ついでに、凡例も下に移動しましょう。

これで完成ってわけではないですね。合計値も積み上がっちゃっています。

系列 "合計"を選択して、
第2軸に変更しますので、選択対象の書式設定をクリックして、
データ系列の書式設定ダイアログボックスを表示しましょう。

使用する軸を、第2軸にして、閉じるボタンをクリックしましょう。

系列 "合計"が選択されているままなので、
データラベルを表示します。

データラベルは外側に移動しますので、中央でも構いません。

表示されたデータラベルを外側(左側)に移動させて、
塗りつぶしなしにします。

また、線の色も、線なしにしましょう。

第2軸の横軸を非表示にしますので、
上側の横軸が第2軸の横軸なので、クリックする。

あるいは、グラフ要素から、第2軸横(値)軸を選択して、

あとは、凡例にある”合計”を削除しましょう。

『積み上げ横棒グラフ』に合計値のデータラベルを
表示することができました。

このように、このグラフでこうしたいけど…という場合は、
表のアレンジと、どうしたら補えるかというアイディアを合わせることで、
様々なグラフを作ることが出来そうですので、色々挑戦してみてください。