Excel。roster。出勤時間を自動で塗りつぶす日勤シフト表を作ってみる。

Excel。出勤時間を自動で塗りつぶす日勤シフト表を作ってみる。

【シフト表】

以前に勤務日や午前・午後・夜勤の出勤によってセルが、
自動的に塗りつぶされるシフト表の作り方をご紹介したことがあったのですが、

出勤している時間が塗りつぶされている日勤のシフト表を作りたいんだけど、
どうしたらいいかなぁ～と、

ご質問がありましたので、今回はその作り方をご紹介してみたいと思います。

作りたい表はこんなのです。

出勤する時間帯。それも30分刻みですが、セルを塗りつぶしているというシフト表です。

今回はサンプルなので3名ですが、
多くの方がシフトや時間によって、
様々な勤務シフトで稼働している会社さん部署さんも多いと思います。

いちいち塗りつぶしているのは面倒ですよね。

そこで、例えばC3に出勤時間をD3に退勤時間を入力すると、
その勤務時間を自動的に塗りつぶし、

さらに、勤務時間も計算しちゃうシフト表を紹介していきたいと思います。

以前紹介したものは、プロジェクトでしたので、何日から何日までということだったのですが、
今回の時間は少し癖がありますので、注意するところもありますが、
ある癖も回避できるように作っていきます。

では、まずは、フレームを作りましょう。

まずポイントなのですが、E2に8:00と入力してF2に8:30と入力します。

この二つのセルを範囲選択をしてオートフィルハンドルを使ってコピーしていきましょう。
実は入力を気を付けないと、何日の何時ということで、あとで支障をきたしてしますので、
入力するよりもオートフィルハンドルを使うことをお勧めします。

うちの会社は、20:00までなんですが…という場合は列を増やしていただければ大丈夫です。

次に、C列とD列を作っていきますが、
ここの入力を今日以外で11:00と入力してもうまく反応してくれません、
ここもポイントで、日付が絡んでくるからです。

つまり、手で入力すると、その日の11:00になってしまい、
2行目の行タイトルの時間と異なってしまい、
この後に設定する条件付き書式がうまく稼働してくれません。

そこで、このC列とD列には、データの入力規則を使って、入力するようにした方が、
楽ですし、きちんと条件付き書式が反応してくれます。

では、C3:D5を範囲選択してデータタブのデータの入力規則をクリックして、
データの入力規則のダイアログボックスを表示しましょう。

入力値の種類を【リスト】にして、元の値には、=$E$2:$W$2　と入力します。
見出し行の時間のところを使っています。
あとは、OKボタンをクリックしましょう。
これで、出勤と退勤を入力しやすくなりました。

では、サンプルで、出勤を8:00　退勤を11:00としておきます。

今度は、勤務している時間に1を算出するように数式を作っていきます。
E3をクリックして、IF関数のダイアログボックスを表示します。

まず、出勤時間と見出し行の時間を比べて、出勤時間が大きいかを確認しますので、
論理式には、$C3>E$2

オートフィルハンドルを使って数式をコピーしますので、複合参照を設定しておきます。
真の場合には、””　空白

偽の場合には、今度は退勤と見出し行の時間を比べますので、IF関数を入力しますので、
再度IF関数のダイアログボックスを表示しましょう。

論理式には、$D3>E$2
真の場合には、1
偽の場合には、””　空白
あとは、OKボタンをクリックしましょう。

数式は、

=IF($C3>E$2,"",IF($D3>E$2,1,""))

となっています。

数式をオートフィルハンドルを使ってコピーします。

続いて、勤務時間の数式を作っていきます。
30分ごとに1が入っておりますので、合計数を2で割る必要があります。

x3の数式は、

=SUM(E3:W3)/2　

となります。
この数式もオートフィルハンドルを使ってコピーします。
今のところ、このようになっています。

この1が入力されているセルに塗りつぶしをしますので、
【条件付き書式】を使って数値の1が入力されていたらセルを塗りつぶすように、
設定していきましょう。

E3:W5までを範囲選択をして、ホームタブの【条件付き書式】をクリックします。

セルの強調表示ルールの指定の値に等しいをクリックします。

そうすると、指定の値に等しいダイアログボックスが表示されますので、

1と入力して、書式には、明るい赤の背景を選択してOKボタンをクリックします。
書式の塗りつぶしは何でもOKです。

これで、塗るつぶしはできましたが、数値の1が邪魔ですね。

これを削除しては何の意味もありませんし、塗りつぶしの色と同じにしても印刷した時に、
薄く見えてしまうこともありますので、表示形式を使って見えなくさせます。

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

表示形式のユーザー定義を選択して、

;;;（セミコロン×3）

と入力して、OKボタンをクリックしましょう。

これで完成しましたね。

様々なテクニックを盛り込みましたが、一部でも知っていると他にも流用できますので、
少しずつでもいいので、様々なテクニックを覚えていくと、
きっと仕事で使っている資料や表も使いやすくなると思いますので、
改善していってはいかがでしょうか？

Excel。trouble。色を付けた件数を数えたいけどどうやったらいいの？よくあるトラブル回避法

Excel。色を付けた件数を数えたいけどどうやったらいいの？よくあるトラブル回避法

フィルターと条件付き書式

先日も、新人君がせっかく表を作ってくれたんだけど、
Excelの使い方がイマイチわかってなくて困ったんだよねぇ～とお話されたので、
詳しく聞いてみると、出欠席の参加者がわかる表を作ってくれると頼んだそうなのですが、
もってきたのが、下記のような表だったそうです。

赤色で塗りつぶしてある人は、欠席の方だというリストだったそうです。

初心者さんが陥りがちな表ですよね。

確かに、色を付けるとわかりやすいのですが、これでは、何人が参加するのかがわからないし、
欠席される人も数えないといけませんよね。

Excelは、色がついている、ついてないを判断して数えることは出来ないという事を知っていれば、
もうちょっと＋αするだろうと思う訳ですね。

ただ、最初のうちはしょうがないとは思いますが、このようなケースの場合、
どのようにしたらいいでしょうか。

まずは、出欠席がわかる列を作るのがいいでしょうね。

出席・欠席という文字が入っている列を作ってあげれば、
出席という文字を数えてあげればいいでしょう。

では、修正してみますね。

D3に出欠席と入力して、書式も同じようにしましょう。また罫線も引いてみます。

すでに、この段階で、欠席者のところが赤色に塗りつぶされていないのがわかりますよね。

また塗りなおさないといけないわけです。
このあたりも、改善する必要がありそうですよね。

このD列に出席・欠席の文字を入力するわけですが、
この程度の大きさならばまとめとドンでもいいですが、
件数が多い場合には、フィルターを使うと効率的ですね。

では、3行目を選択して、データタブのフィルターボタンをクリックしましょう。

すると、フィルターが設定されて、各項目名に▼が表示されましたね。

Noの▼をクリックして、色フィルターから【塗りつぶしなし】を選択しましょう。

すると、塗りつぶしてないレコードだけが抽出されましたね。

あとは、出欠席の欄に出席と入力しましょう。

今後は、塗りつぶしてあるものだけを抽出してあげて、欠席と入力します。

では、フィルターをクリアします。
そうそう、間違っても、書式のコピーとか使って、欠席のセルに書式のコピーをしちゃダメですよ。
たいへんなことになりますからね。

あとは、出席の数を数えてあげればいいわけですから、
COUNTIF関数を使って、出席者を算出すればいいわけですね。

B22をクリックして、COUNTIF関数のダイアログボックスを表示します。

範囲には、D4:D20
検索条件には、”出席”
と入力してOKボタンをクリックしましょう。

参加者は12名と算出されましたね。

このように、色を数えることはできませんので、数えられるものを作っておくといいでしょう。

それと、塗りつぶしですが、できれたら、条件付き書式をお勧めしますが、
Excelが初心者の方ですと、ちょっと敷居が高いかもしれませんね。

Excel2013。horizontal_bar_graph。横棒グラフの並び順を表の順番にしたい。

Excel2013。横棒グラフの並び順を表の順番にしたい。

横棒グラフ

横棒グラフを作っているんだけど、
出来た横棒グラフの並び順が表と逆転していてどうにか、
表と同じように並べることはできないかなぁ～と、ご質問がありました。

以前私のBLOGでも書いたことがありますが、
改めて、そして、Excel2013でご紹介してみたいと思います。
http://infoyandssblog.blogspot.jp/2014/03/excel2010.html

グラフってちょっとしたことを知っているだけで、いろいろアレンジできる例の一つですね。

では、早速ご紹介していきます。
下記の表があります。

この表を使って、各年の合計を、横棒グラフにしてみます。

B2:B6とF2:F6を範囲選択をして、
挿入タブのグラフにある横棒から2-D横棒の集合縦棒を選択しましょう。

グラフが挿入されてきましたね。

横棒グラフとしては、問題はないのですが、今回のリクエストは、このグラフではダメですね。

よく見ると、年度の軸をみてみると、上が2013年で、下が2010年。

表は、一番上が2010年で一番下は2013年になっていますよね。これを逆にしたいのです。

今回は、下記のようなグラフにしたいわけです。

表と同じように表示したいわけです。

まずは、縦軸である2010年～2013年を反転させてみましょう。

縦（項目）軸をクリックして選択しましょう。
または、書式タブのグラフ要素を縦（項目）軸にして、選択対象の書式設定をクリックしましょう。

すると、右側に軸の書式設定作業ウインドウが表示されてきます。
軸を反転させますので、軸位置の【軸を反転する】にチェックをつけてみましょう。

グラフの縦軸が反転されたのが確認できますね。

軸が反転して、表と同じように、2010年～2013年と並んだことは並んだのですが、
今度は、数値軸もいっしょに、反転してしまって上に移動してしまいました。

このままでもOKといえばOKですが、やはり横（値）軸は、元の位置である、
下側にあるほうが望ましいこともあると思いますので、そこもあわせてご紹介をしていきます。

では、縦（項目）軸をクリック、
または、書式タブのグラフ要素を縦（項目）軸にして、選択対象の書式設定をクリックしましょう。

右側に軸の書式設定作業ウインドウが表示されてきます。

軸のオプションの【横軸の交点】が自動になっていますので、
最大項目にチェックをいれてみましょう。

これでしょうでしょうか？
表と同じように、2010年～2013年の順番で、並びましたね。

チェックを2か所つけるだけなのですが、このようにグラフは、
ちょこっとしたことを知っている・いないで、スキルが大幅にアップしちゃいます。

項目がわかりにくいので、いろいろ触ってみるといいですね。

Excel。Brackets with numbers。意外と難しい（1）というカッコ付数値を表示する方法。マイナスになっちゃう。

Excel。意外と難しい（1）というカッコ付数値を表示する方法。
マイナスになっちゃう。

表示形式

先日、カッコ付数値を連続でつくりたいんですが、うまくいかない。

マイナス表示になってしまうので、どうやったらいいのでしょうか？
とご質問をいただきまして、なるほど、確かにちょっと、難解というか、
面倒だよなぁ～と思いましたので、
今回はカッコ付数値をオートフィルハンドルを使って連続データの作り方をやっていきます。

何をいっているのか？というと、下記の表を作りたいわけです。

えっ、簡単なのに、何言っているの？とお思いになる方もいると思いますが、
では、C3に(1)と入力してみましょう。

Enterキーを押して完成…と思っていたら、なんと、マイナス１になってしましましたね。

アレレ、どうしてでしょうか？
実は、カッコ付の数値は、マイナスだと判断する表示形式が設定されているからなのです。

日付を3/15と入力すると分数ではなくて、3月15日と表示されるのと同じことなのですが、
今回は、カッコ付の数値を使いたいわけです。

そこで、このマイナスの数値の表示形式を変えてあげればいいと思いついて、
（）カッコ付に変えてみたとしましょう。

C3をクリックしておいて、セルの書式設定ダイアログボックスが表示しましょう。

Ctrl+1でもいいですね。

Ctrl+1はExcelではよく使うショートカットキーですので、覚えておくといいでしょうね。

表示形式タブの分類を数値にして、負の数の表示形式から、(1234)を選択しましたら、
OKボタンをクリックしましょう。

カッコ付数値になりましたね。

簡単じゃん。とお思いになると思いますが、問題はここから、一つだけならいいのですが、
A列のようにオートフィルハンドルを使って、連続コピーをしたら、どうなるのでしょうか？

では、C3をクリックして、オートフィルハンドルを使って下方向に連続コピーしてみましょう。

連続データを選択してみると、なんと、（1）0 1 2 3…どうしてしまったのでしょうか？

Excelがおかしくなったわけではありませんよ。なんでこうなってしまったのかというと、
(1)って表示形式で(1)となっていますが、元々マイナス１でしたよね。

つまりオートフィルハンドルを使って連続データを作ると、-1 0 1 2 3…となってしまうのです。

(1)(2)(3)とはならないのです。

では、どうやったらいいのでしょうか？

では、E3に1と入力します。

E3をクリックして、セルの書式設定ダイアログボックスを表示しましょう。

今回は、ユーザー定義をつかって修正しますので、
表示形式タブの分類はユーザー定義を選択して、種類には、(0)と入力して、
OKボタンをクリックします。

すると、E3には(1)と表示されたのが確認できますね。

では、下方向にオートフィルハンドルを使って連続コピーをしてみましょう。

今回は、綺麗に(1)～(12)まで表示できているのが確認できますね。
ですので、カッコ付数値を作りたい場合には、

表示形式のユーザー定義

を使うのがいいと思いますよ。

Excel2013。picture_graph。絵グラフをExcel2013でつくってみよう。

Excel2013。絵グラフをExcel2013でつくってみよう。

絵グラフ

プレゼンテーション用の資料をはじめとして、通常のグラフよりもわかりやく、
見栄えもいい、絵グラフを今回はExcel2013で作ってみようと思います。

Excel2013は、かつてのバージョンと比べて少し変わっていますね。

では、早速作っていきましょう。
絵グラフ？って、いう方もいると思うので、作りたいのは、こういうものです。

下記の表があります。これを、絵グラフにしていきます。

今回は合計数を使って絵グラフを作ろうと思いますので、
範囲選択は、A2:A5とE2:E5になりますので、範囲選択をしましたら、
挿入タブのおすすめグラフをクリックしましょう。

すると、グラフの挿入ダイアログボックスが表示されてきますので、

その中から、集合横棒を選択して、OKボタンをクリックします。

集合横棒グラフが完成しました。軸が600までの最大値になっていますので、
500に変更しておきます。

横（値）軸をクリックして、書式タブの選択対象の書式設定をクリックします。

右側に、軸の書式設定作業ウィンドウが表示されてきますので、
軸のオプションの最大値を600から500に変更します。

グラフの横軸の最大値が600から500に変わったことが確認できますね。

作業ウィンドウはそのまま表示しておきます。

それでは、いよいよ横棒グラフを絵に変更していきます。

事前に絵やイラストなどを用意しておくといいでしょう。
旧来のクリップアートはOffice2013から消えてしまいましたんで、
クリップアートのような方法を使う場合は、]
挿入タブのオンライン画像を使って検索していくことになります。

今回ご紹介するのは、すでに画像を用意しておきました。

では、ホットケーキの棒グラフをクリックします。

ホットケーキだけを選択したいのに、ドーナツもショートケーキも選択されてしまうので、
もう一度、ホットケーキの棒グラフをクリックしてみましょう。

ホットケーキのみが選択できましたね。
先ほど、軸の書式設定だった作業ウィンドウが、
データ要素の書式設定に変わっているのが確認できます。

塗りつぶしは、塗りつぶし（図またはテクスチャ）を選択して、
図の挿入元は、ファイルを選択します。旧クリップアートは、オンラインに該当します。

今回は、ファイルを用意してありますから、ファイルを選択します。

そして、ホットケーキの画像を選択して挿入すると、
横棒グラフがホットケーキの画像に置き換わりましたが、
太ったホットケーキが入ってきてしまっていますね。

このホットケーキを修正していきます。
データ要素の書式設定の塗りつぶしにある
【拡大縮小と積み重ね】にチェックをして、Units/Pictureを50で一つの大きさにしたいので、
50と入力します。

しかし、なんで、Units/Pictureって英語のままなの？

こうすることで、ホットケーキが50を一つの大きさとした横棒グラフになりましたね。

あとのドーナツとショートケーキも同じように処理をしましたら、

完成ですね。ちょこっと知っていると便利な絵グラフ。挑戦してみてはどうでしょうか？