Excel。pyramid_graph。お客の年齢階層別ピラミッドグラフを条件付き書式でつくってみる。

Excel。お客の年齢階層別ピラミッドグラフを
条件付き書式でつくってみる。

条件付き書式のデータバー

先日ご紹介しました、年齢層別に比較できるピラミッドグラフの作り方ですが、
グラフで作る必要が無いとした場合、

実は、条件付き書式のデータバーを使うともっと簡単に作ることが出来るのです。

実際につくってみると、
【条件付き書式】奥が深いって思えますよ。

こんなものを作りたいわけです。

では、早速作っていきましょう。
下記の表があります。

まず、中心の柱になる年齢をセル参照させる数式をF3にクリックして作っていきます。
F3に、=A3
という数式をつくり、オートフィルハンドルを使ってコピーしていきましょう。

E列には、男性の数値を参照するようにしますので、
E3に、=B3
という数式をつくり、オートフィルハンドルを使ってコピーしたら、
女性の数値も同じようにしますので、

G3に、=C3

という数式をつくり、オートフィルハンドルを使ってコピーします。
すると、このような、表が作れましたね。

これが、ベースになります。

このあと、この数値を条件付き書式のデータバーを使って変えていきます。

では、E3:E9を範囲選択して、ホームタブの条件付き書式のデータバーから、
青色を選択しましょう。

すると、青色のデータバーが表示されましたね。

同じように今度は女性のG3:G9を範囲選択して、赤色のデータバーを設定しましょう。

ここからがポイントのはじまり。
まず女性の赤色のデータバーからアレンジしていきましょう。

まずは、G3:G9を範囲選択して、条件付き書式にある、ルールの管理をクリックします。

条件付き書式ルールの管理ダイアログボックスが表示されますので、

ルールの編集をクリックします。書式ルールの編集ダイアログボックスが表示されます。

棒のみ表示にチェックを入れます。これで数値が見えなくなります。
そして、最大値ですが、これは、男性と女性の中での最大値にしてあげる必要があります。
いちいち、手動で直していては、面倒くさいので、
種類を数式にして、値にはMAX関数を使います。数式は、

=MAX($E$3:$E$9,$G$3:$G$9)

不安な方は、どこかで数式を作って確認するといいでしょう。

あとは、OKボタンをクリックして、女性の方が完成しました。

今度は男性をアレンジしていきます。
E3:E9を範囲選択して、

書式ルールの編集ダイアログボックスを表示するところまで同じですので、
そこまでやったとします。

棒のみ表示と最大値を数式にしてMAX関数を使う所も同じです。

そして、追加する処理があります。
それが、棒の方向です。これを▼をクリックして、右から左にします。

そのあと、OKボタンをクリックしましょう。条件付き書式ルールの管理に戻りますので、
ここもOKボタンをクリックします。

これで、完成しました！
グラフにするよりも、同じものを作るようでしたら、こっちのほうが簡単な気もしますね。
条件付き書式は奥が深いので、色々試してみるといいかもしれませんね。

Excel。CHAR(10)。セル内で改行するにはAlt + Enterだけど沢山あるときはCHAR(10)がお勧め

Excel。セル内で改行するにはAlt + Enterだけど
沢山あるときはCHAR(10)がお勧め

CHAR関数+&

ちょっと前だったかな？セル内の改行を削除を削除する方法というのをご紹介しましたが、
今回はその逆で、セル内で改行をしたいのですが、一つ二つではなく、
大量なデータで指定の場所に改行を入れる場合をご紹介してきたいと思います。

やりたいことは、こんな感じです。

まずは、おさらいですが、セルの文字列同士を連結する方法から確認します。

CONCATENATE関数を使うというのが、MOS Excel2013などでは紹介されていますが、
別に関数を使わなくても結合は出来ます。

&（アンパサンド）を使えば、文字同士の結合は簡単にできますよね。

それでは、下記のデータを結合してみたいと思います。

E列とF列のデータをG列に結合していきます。
G2をクリックして、=E2&F2という数式を作りましょう。

文字列が結合されたのが確認されましたら、
オートフィルハンドルを使って数式を神奈川県座間市にまで数式をコピーしていきましょう。

まずは、結合できましたね。
列幅、行高を変更しても、一行のままですよね。
このG列に、セルの書式設定で、折り返して全体を表示するがONにしてみても、

文字の制御の折り返して全体を表示するにチェックをつけてONにしてOKボタンをクリックしても、何もかわりませんよね。

改行されないことを確認しておきましょう。

確認しましたら、チェックを外して、元に戻しておきましょう。

このG列は、数式ですので、最初にお見せしましたサンプルのようにセル内の上には都道府県、
下には市区町村を表示させることは出来ませんよね。

なので、実際にやろうとした場合には、コピーをして別のセルで値としてコピーしてから、
Alt + Enterを入れていく必要がありますよね。

これが大量のデータだと大変だよね。

ということなのです。
では、先程のサンプルはどうやっているのか？ということ、

CHAR関数を使っているのです。

CHAR関数は、コンピューターの文字セットから、そのコード番号に対応する文字を算出します。
そこで、CHAR(10)とすると、これがAlt + Enterと同じセル内の改行の文字コードなのです。

まずは、数式を作ってみたいと思います。

G2をクリックして、
=E2&CHAR(10)&F2
という数式を作り、オートフィルハンドルで数式をコピーしてみましょう。

見た目変わっていませんが、G2には、先程の数式で作られています。

これでは、サンプルのようになっていませんよね。
あとは、G2:G8を範囲選択して、セルの書式設定ダイアログボックスを表示します。

配置タブの文字の制御にある折り返して全体を表示するにチェックをいれて
OKボタンをクリックします。

するとサンプルと同じになりましたね。

もし、行の高さが変わらなかった時には、行の高さを変更してみましょう。

このような方法を使うと、セル内の改行を大量に入れることができます。

仮にマンション名は3行目にしたい場合は、またCHAR(10)をいれてあげればいいだけです。

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

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

ピラミッドグラフ

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

それを今回どうやって作ったらいいのかというご質問が以前ありましたので、ご紹介。

このような数値があって、

これを年齢階層別ピラミッドグラフにしてみると、

このようなグラフを作りたいというのが、今回の目標です。
グラフを作るときに、元のデータをグラフ用にアレンジすると、
イメージのグラフを作れることがよくありますが、今回もそのパターンです。

まずは、表をグラフ用の表にアレンジしていきます。

H列の女性の列には、単純にセル参照の数式が設定されていますので、
H3には=C3という数式が設定されています。

ところが、グラフの左側にあたる男性のデータはそう簡単にはいきません。

見た目は、セル参照に見えますが、マイナス方向にプロットしなければいけないわけです。
つまり、データの中身はマイナスになっていて、
それを表示形式のユーザー定義でマイナス表記を消しているのです。

例えば、F3には、=-B3という数式が設定されています。

イコールの後にマイナスを入力するとマイナスの参照値になります。
一時的に、表示形式を解除してみたいと思います。

F列は、このようにマイナスになっております。このようにしませんと、
グラフの真ん中に10代などの年齢を混ぜて左右均等になりません。ここがポイントですね。

そして、このマイナス表示をプラス表示にしますから、表示形式のユーザー定義を使って、
アレンジしていきます。

F3:F9を範囲選択して、セルの書式設定ダイアログボックスを表示します。

表示形式のユーザー定義をクリックして、種類には、
#,##0;#,##0
と入力します。;(セミコロン)の左側が、正の数の時の表示形式で、
右側が負の数の時の表示形式というルールになっております。
要するに右側も正の数と同じ表示形式にしたことにより、
マイナスが表示されなくなったわけです。

では、ここからグラフを作っていきますので、積み上げ横棒グラフを挿入していきます。

E2:H9を範囲選択して、挿入タブの横棒から積み上げ横棒を選択します。

すると、このようなグラフが挿入されてきます。

凡例を非表示にしますので、凡例をクリックしてDELキーで非表示にします。

そして、棒グラフを太くします。

棒グラフをクリックしておいて、データ系列の書式設定ダイアログボックスを表示します。

要素の間隔をなしにスライドします。そして、閉じるボタンをクリックします。

今回の横軸の値は、それぞれ150ずつの長さでまとまっておりますので、
何もしなくても左右均等ですが、もし、左右で最大値が異なっている場合には、
軸の書式設定ダイアログボックスを使って、左右均等になるように調整する必要があります。

縦（項目）軸の書式設定ダイアログボックスを表示します。

軸のオプションの”軸を反転する”のチェックボックスをクリックしてONにします。
これで、グラフが10代～が上になり反転されました。

ドンドングラフを作り込んでいきます。
つぎに、10代や20代と表示されている縦軸を削除しますので、縦軸をクリックして、
DELキーを押して削除します。

横軸も不要ですので、削除します。

さらに、レイアウトタブの目盛線の主縦軸目盛線をなしにします。

あと一息です。
男性と女性の塗りつぶしを変えていきます。

そして今赤になっているところに年齢を表示させていきます。

男性の青色をクリックします。

今回は、立体感のあるグラフにしたいと思います。同じように女性も変えましょう。

あとは、真ん中の赤色をクリックします。そして、塗りつぶしなしにします。

そして、ここがポイントになります。そのままレイアウトタブのデータラベルにある、

その他のデータラベルオプションをクリックします。

データラベルの書式設定ダイアログボックスが表示されますので、
ラベルの内容の分類名にチェックボタンをONにして、閉じるボタンをクリックします。

あとは、フォントサイズを変更したら、完成ですね。

このように、マイナス方向を表示するグラフの作り方を知っていると、
お客様の年齢層別ピラミッドグラフなんてものも作れるようになるんですよ。

ただ、今回はグラフでのピラミッドグラフの作り方でしたが、
実は条件付き書式でもこの年齢層別ピラミッドグラフを作ることが出来るんですよ。

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

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

条件付き書式

ちょっと前に、塗りつぶしたセルの数える方法をご紹介しましたところ、
午前・午後・夜間の3交代シフトを作れないかなぁ～といわれたもので、
こんなものを作ってみたところ、喜ばれましたので、それを今回はご紹介したいと思います。

下記のようなものを作ります。

午前が赤色で午後が緑色、
夜間が青色でスタッフが午前・午後・夜間・休日そして勤務日数がわかるシフト表です。

今回は、それぞれの日に午前・午後・夜間のスタッフが何名いるのか？
というのは外しております。

さて、フレームを作ります。

まずは、午前・午後・夜間という文字を入力するのに簡単に入力できるように、
C4:L8を範囲選択して、入力規則のリストを設定していきます。

データタブにある、データの入力規則をクリックすると、
データの入力規則ダイアログボックスが表示されます。

設定タブの入力値の種類からリストを選択して、元の値には、M3:O3を範囲選択しましょう。
すると、範囲選択したセルに▼が登場していますね。

クリックすると、午前・午後・夜間から文字を選択できるようになりました。

それでは、文字を入力してみます。

このようなデータをサンプルでいれております。
次に、午前・午後・夜間の数を数える関数を作っていきます。
M4をクリックして、COUNTIF関数のダイアログボックスを表示しましょう。

範囲には、$C4:$L4と入力します。複合参照にしておりますが、
これは、午後・夜間もいっぺんに算出できるようにするためです。

検索条件には、M$3を入力します。

複合参照は複雑ですがこのように何度も数式を作るには面倒な場合は、
複合参照を知っていると作成がスマートになります。

数式をコピーすると、いっぺんに算出されました。

次に、P列の休日数を数える関数を作ります。これは、空白の数を数えますので、
COUNTBLANK関数を使いますので、
COUNTBLANK関数のダイアログボックスを表示しましょう。

範囲には、C4:L4を選択します。あとはOKボタンをクリックしましょう。
そして、P列に数式をコピーしましょう。

Q列は勤務数を算出しますので、
ここは、SUM関数を使って、午前・午後・夜間のM列からO列までの合計数を算出します。

すると、このように算出されました。

そして、いよいよ条件付き書式を設定していきます。
C4:L8を範囲選択をして、
ホームタブの条件付き書式からセルの強調表示ルールから指定の値に等しいをクリックします。

すると、指定の値に等しいダイアログボックスが表示されますので、
M3をクリックして、書式には、ユーザー設定の書式を選択します。

セルの書式設定ダイアログボックスが表示されますので、赤色を選択します。

OKボタンをクリックすると、指定の値に等しいダイアログボックスに戻りますので、
OKボタンをクリックすると、午前に赤色が設定されました。

同じ方法で、午後と夜間も設定していきましょう。

最後に、午前・午後・夜間の文字を表示しないようにします。
間違えても文字を削除したらダメですよ。

それでは、C4:L8を範囲選択をしてセルの書式設定ダイアログボックスを表示します。

表示形式タブの種類に;;;（セミコロンが3個連続）にして、OKボタンをクリックします。

これで完成しました。

このように、あらゆるテクニックを積み重ねると、スゴイもができる。

これもExcelのスキルアップの近道ですので、
ビジネスシーンで必要なものを作っていきましょう。