ブログ練習帳

Excelでヒストグラムを作る

ご無沙汰でございます。いまだに見習いの見習です。みなさま、いかがお過ごしでしょうか。

ところで最近、ウェブ解析に関わったりしているのですが、統計とか使うことになるので、Excelもがんばってみました。きっと、Excelできるッ。

いきなり、「時代はヒストグラムだ！」ということで、Excelでヒストグラムを作ることになりました（Excel2016使用）。

↑ヒストグラムって、こんなのです！
言われてみれば、見たことあるかも･･･。テストの点数とか、体重とか、身長とか、どのくらいの値の人がどんな感じにいるのかなー、って調べるときに使ったりするみたいです。（あと、アクセス解析では、日別の平均セッション数とか、モゴモゴモゴ……。）

この「ヒストグラム」、Excelで作ることができるんですって。あれまぁ、すごい。
ということで、やってみました。今回はExcel2016で作りましたが、それより前のバージョンでもいけまーす。

★Excelアドインの「分析ツール」を有効にする

ただ、このヒストグラム、Excelのアドオンの「分析ツール」を使うので、はじめて「分析ツール」を利用するときは、このアドインを有効にしておく必要があります。

Excel[ファイル]→[オプション]→[アドイン]→[分析ツール]を選んで、[OK]をクリックします。

これをすると、Excelの[データ]タブに[データ分析]が追加されます。

★ヒストグラムを作る

ではでは･･･。あとは動画でご説明です～。

0.A列にそれぞれのデータを入れておく

ここでは、A1:A100までデータが入っています。

1.B列に「データ区間」を入れる

データ区間とは、横軸の数字のことです。
とりあえず、最小値から最大値までを1刻みで刻んでいくヒストグラムを作ることにします。ここでは「0から始まって25までで1刻み」ことです。

1. A列の最小値と最大値を求め、横軸の最小の値と最大の値になる数を決めます
2. C列にMin関数とMax関数を使って、A列の最小値と最大値を求めます
3. 上記で求めた最小値の値(0)をB1セルに入力します
4. B1セルをアクティブにした状態で、[ホーム]→[連続データの作成]と進み、[連続データ]で「範囲」を「列」、「種類」を「加算」、「増分値」を「1」(1刻みにしたいから)、「停止値」を「25」(Max関数で出力されたのが25なので)として、「OK」をクリック

これで、B列に0から25まで、1ずつ増えていく連番が入力されました！

2.ヒストグラム作成画面で作業する

1. A1セルをアクティブにした状態で、[データ]タブの[データ分析]をクリック。[ヒストグラム]へと進みます
2. 「入力元」で[入力範囲]としてA1:A100、[データ区間]としてB1:B26を入力します
   それぞれ、[入力範囲]ではA1をクリックしてから、[データ区間]ではB1をクリックしてから、[Shift]＋[Ctrl]＋[↓]のキーを押下すると、最後の行まで選択できます。
3. [新規ワークシート]と[グラフ作成]にチェックをつけて、[OK]します。

新しいシートにヒストグラムができました!!!

3.ヒストグラムの見た目を整える

あとは、見た目を整えていきます。

1. 不要な「グラフ要素」を削除します
   凡例（はんれい：ここでは「頻度」）、「軸ラベル」（ここでは「頻度」と「データ区間」）を削除します
   • グラフ上でグラフ要素をクリックしてアクティブにした状態で[del]キーを押すだけで削除できます
2. 系列「頻度」の棒の幅を隣の棒とくっつくようにします
   1. ヒストグラムを選択した状態で[書式]タブの[現在の選択範囲]で[系列”頻度”]を選び、[選択範囲の書式設定]をクリック
   2. [データ系列の書式設定]で[系列のオプション]から[要素の間隔]を[0％]にします
3. 余分なデータ範囲を削除
   「次の級」というセル(A28:B28)が「グラフデータの範囲」に入っているので、範囲から除外します
   1. グラフをクリックし、アクティブにした状態にすると、データ範囲に青や紫などのカラーの囲み線が出ます。
   2. マウスカーソルが「黒い2方向の矢印」が出た状態で、この囲み線をドラッグすると、範囲を広げたり、狭めたりできます(「黒い4方向の矢印」ではないので注意。これだと範囲が移動してしまいます……)。

で、で、できた！（と思う･･･。）
やった～♥
でも、本当は、いい感じの「データ区間」をどんな感じに設定するか、とか、Excel以前の設定が大切なんですって･･･。

こりゃ、前途多難ですわ、がんばらなくっちゃ。

2018年7月29日1:29 PM| カテゴリー： Excel,見習い奮闘記| コメント (0)

Excelで予定表を作る

ご無沙汰してます。相変わらず、「明日できること、今日すな」の見習いです。

なんだか、やることいっぱいだから、とりあえず予定表を作ってみよう！とトライしました。

こんなんできました（Excel2016使用）。

毎月、きちっと予定表を作って管理すれば、できる見習い？てな感じです。
ささっと仕事ができるように、カレンダーを毎月作り直す必要がないように、ですです。時短です。
てなことで、2017年9月の予定表を作るときには、G1セルに「2017」、I1セルに「9」と入力するだけで、B3:C33まで、自動で日付と曜日が入ります。
そして、土曜日の行は青っぽく、日曜日の行は赤っぽく塗りつぶされることで、カレンダー感が出ます。そして、本日については、真っ赤に塗りつぶされることで、緊迫感が出てます。

これは、おもに「関数」と「条件付き書式」でできてます。
次にささっと作れるように、ビボー録です。

 

まずは、関数･･････

★年と月を入れるだけで、自動で表示される

入っている関数は、これだけ。まず、B3に「=DATE(G1,I1,1)」と入力。
これは、G1やI1を参照して、月初の1日目を取得します。
そして、B4に「=B3+1」が入ります。これは、B3の次の日ということ。Excelでは、1が1日となるので、これでOK。
あとは、オートフィルで式を下へコピーすれば大丈夫！

 

★一応、月末処理も･･･

でもでも、30日とか31日とか、月によって違うし、2月に至っては、どうなるのよ！って問題があります。

で、思いついたのが、B31からB33の式です。
B31には「=IF(B30=””,””,IF(MONTH(B30+1)=$I$1,B30+1,””))」としました。
これは、
「まず、直前の日のセルが空白だったらこのセルも空白よ！」ということからスタート。これは、本当はこのセルには入力する必要がないのだけど、その下2つのセルと同じにしておいたほうが管理上楽かなということと、オートフィルを使いたかったから。本来は、B32以降のセルに入れておけばよいものです。

で、上のセルが空白ではなかったら、次は「上のセルの日の次の日の月がI1で入力した月と同じであれば、上のセルの日に一日足した日を返しなさい。そうでなければ、空白よ！」という意味です。つまり、9月の予定表を作っているとき、単純に前の日を1日足していくと、10月1日の予定表が出てきてしまうことがあります。それを防ぐために、「もし、そのセルに表示される日が、次月ならば、空白にしておいてね♪」ということをしてます。

この3つのセルだけに、この式を入れたのは、31日分作った表の中で、一番短くなる可能性のある2月28日の次の日以降を処理するため。29日以降のセルで処理すればよいからです。

なお、C列については、B列をそのまま返しています。こんなことしないで「表示形式」でひとつのセルでやればいいじゃん！っていう突っ込みがあるかもですが、まあ、そこはご愛嬌ということで･･････。許してください。なんとなくです。なんとなく。

 

あとは、「条件付き書式」で曜日と本日

条件付き書式で一気にいけます。

（前にも、条件付き書式で、悩んだことがあるので、そのリンクはこちら。）

まず、条件付書式を設定したいセル「B3:E33」を選択します。

その状態で、「ホーム」→「条件付き書式」→「新しいルール」と進んで、「数式を使用して、書式設定するセルを決定」をクリックし、「次の数式を満たす場合に値を書式設定」に式を入れ、「書式」から「セルの書式設定」→「塗りつぶし」で塗りつぶしの色を設定していく流れです。

「B3:E33」に設定している書式はこんな風です。

上から、「本日なら、赤く塗りつぶしてね」です。なお、本日の塗りつぶしが行われた場合、曜日での色分けは無効にしたいので、「条件を満たす場合は停止」にチェックが入っています。

次が、土曜日の場合に青っぽく塗りつぶす条件付き書式

その次が、日曜日の場合に赤っぽく塗りつぶす条件付き書式です。

 

★「本日なら、赤く塗りつぶしてね」

の式は、「=$B3=TODAY()」です。「$B3」と複合参照になっているのは、この条件に当てはまる場合には、その行のC、D、E列にも書式を適用してほしいから。Bの前に$マークをつけて、B列を固定させるけど、条件付き書式を下にコピーしたときには、4行目5行目と条件を移動させたいので、3の前には$をつけていません。

★土曜日なら青っぽく塗りつぶす

の式は、「=WEEKDAY($B3)=7」です。B3をWeekday関数を使って、曜日を取得します。Weekday関数は、このように書くと、その曜日を日曜日を1として月曜日は2、火曜日は3というように、数字で返してくるので、土曜日の場合は、7と返します。だから「、7という数字が返ってきたときには、青っぽくしてね」となります。ここでも、「$B3」と複合参照で書いておきます。理由は、「本日なら、赤く塗りつぶしてね」と同じ。

★日曜日なら赤っぽく塗りつぶす

の式は、「=WEEKDAY($B3)=1」です。これは、「土曜日なら青っぽく塗りつぶす」を日曜日に変えただけなので、理由もまったく同じ。おほほほほ。

と、こんな感じで、予定表を作ってみました。どうでしょ。
おおお、気づいたら、もう、こんな遅い時間ではありませんか。予定表を作ったところで、本日も終了！　あれ？？？？？？

 

前途多難な見習いです。皆様、ごきげんよう･･････。（きゃー、どうしよう）

2017年9月9日6:34 PM| カテゴリー： Excel,見習い奮闘記| コメント (0)

Excelで金種計算（複数の人にお金を分けるとき）

まとめてお金を下ろしてきて、複数の人に分けなければならない！でも、それぞれの金種で何枚ずつ出金すればいいのかしら????

計算がめちゃくちゃ苦手な見習が出くわした課題。銀行の出金伝票には、10000円、5000円、2000円、1000円…、それぞれの金種の枚数を指定する欄があって、そこに枚数を記入するのみだけど、指折り計算すると間違いがありそう。
そんなときに、必要枚数を計算してくれるExcelブックを考えました。
(スクリーンショットはExcel2013でとりましたが、基本的な機能だけなので、どのバージョンでもだいたい同じです）

こんな風なのです。B3:B17に、それぞれ必要な金額を入力していきます。とりあえず、私の場合、15人くらいに分けるのがマックスっぽかったので、15行分です。

すると、O列に、それぞれ10000円から1円まで必要枚数を算出した結果が表示されるというもの。つまり、黄色いエリアに入力するだけで、あとは自動計算になります。

ここで使うものは、基本的な関数と機能ばかり。関数についてはいくつか別案もあるけれども、基本的なもので作成してみました。

あと、2000円札については、あえて割愛。2000円札で出金したくないので…。

まず、最初にですが、実はC:K列を非表示にしてありました。ここで途中計算させることで、簡単な関数のみで計算ができます。配列をつかったりすることで、途中計算を省くこともできますが…。

★ポイント　表示形式

表示形式を使って、「円」や「枚」を表示させることで、それぞれの数をExcelに「数値」として認識させ、計算できるようにしています。
C2:K2には、左から10000、5000、1000、500、100、50、10、5、1と入力、N4:N12は、上から、10000、5000、1000、500、100、50、10、5、1と入力しておきます。

「円」とついているセルB3:B18、C2:K2、N4:N12は、表示形式を「#,###”円”」と設定する(ユーザー定義)。

「枚」とついているセルC3:K18、O4:O12は、表示形式を「#,##0″枚”」と設定する(ユーザー定義)。

表示形式の設定は、設定したいセルを範囲選択した状態でCtrl+1[ぬ]キーを押すと、一発で「セルの書式設定」画面になるので、簡単！

★ポイント　C3:K17エリアの計算

このエリアは、それぞれ一人分に必要な金種を計算するエリアです。ここでは、B列に入力した金額をその行内でそれぞれの金種の枚数を計算します。

C3:C17の計算

ここはそれぞれ1万円札の枚数を計算します。C列では式を入力するのはC3のみで、あとはオートフィルします。

C3に入力するのは、「=ROUNDDOWN($B3/C$2,0)」です。この式の意味は、「B3の値をC2の値（つまり10000）で割り、その値を整数になるよう切り捨てする」という意味。

「$B3/C$2」とドルマークを使って複合参照にしたことで、C4:C17はオートフィルで簡単入力できます。(オートフィルの関係上「B3/$C$2」でもいける)

D3:K17の計算

このエリア、D3にのみ式を入力すれば、あとはオートフィルでいけます。

D3に入力するのは、「=ROUNDDOWN(MOD($B3,C$2)/D$2,0)」です。

この式の意味は、「B3の値(25637)をC2(10000)で割った余り、つまり5637を、D2(5000)で割って、その値を整数になるように切り捨てする」という意味です。

B3の値から10000円札で支払える金額を引いて、その数を5000円で割って整数で切り捨てすることで、5000円札の必要枚数を算出しています。

ここでポイントになるのは、それ以降の金種1000円札とか500円玉の扱いです。それぞれの金種の枚数を調べるときは、一つ前の金種で払えない端数の金額のみをその金種で払えるか調べるだけで大丈夫。(これが、2000円札が入ると、そうならないところがあるけれど…)

1000円札の枚数は、5000円で払えない端数のみ、500円の枚数は、1000円で払えない端数のみ、100円の枚数は500円で払えない端数のみ計算すればいい。

ということで、「MOD($B3,C$2)/D$2」というように複合参照を使ってあげることにより、D3:K17エリアは、D3にみに式を入力することで、あとはオートフィルでOKです。

★ポイント　B18:K18の計算

ここは単純に合計を出すエリアなのでB18に「=SUM(B3:B9)」と入力して、C18:K18へオートフィルすればOK。

★ポイント　O4:O12の式

C18:K18で、すでに答えは出ているのですが、C:K列は計算過程で、使う人には見えなくていい。だから非表示にします(C:Kを列選択後、右クリックから「非表示」)。で、C18:K18は、O4:O12に見やすく転記してあげます。

単なる転記なので、リンク貼り付けでOK。O4には「=C18」、O5には「=D18」、O6には「=E18」…というようにO12の「=K18」までリンク貼り付けを設定します。

★キー操作の短縮のため…データの入力規則

このブックでは、使用時には、B3:B17に半角で数値を入力するだけです。効率よく半角入力できるようにします。B3:B17を範囲選択後、「データ」⇒「データの入力規則」⇒「データの入力規則」⇒「日本語入力」⇒「日本語入力」を「オフ（英語モード）」にし、OKをクリックします。

★ミス予防に…　セルの保護

このブック、使うときに入力するのは、B3:B17のみで、あとは入力すべきところではないです。間違って数式を消してしまわないよう、セルに保護をかけます。

B3:B17の「セルのロック」をはずします。B3:B17を選択後、「ホーム」⇒「書式」⇒「セルのロック」をクリックします。

その後、「ホーム」⇒「書式」⇒「シートの保護」をクリックし、「ロックされていないセル範囲の選択」にのみチェックをつけて「OK」をします。単なるミス防止なので、パスワードは設定しなくてもOK。

 

これで必要な設定はおわり。あとは、セルに色をつけたり、罫線を引いたりして、「どこに入力すればいいのかしら」と直感的にわかるようにしてあげれば完成。

「明日の私は、もう他人」なくらい物忘れが激しい見習ですが、これなら明日から大丈夫かしらん????

2014年10月19日9:30 PM| カテゴリー： Excel,見習い奮闘記| コメント (0)