はじめに
今PM(プロジェクトマネージャー)をやってまして、スプレッドシートでプロジェクトを管理することがままあります。
以前はExcelを使っていましたが、リアルタイムに相互利用しやすいということでスプレッドシートに移行してきております。ですが、まだまだスプレッドシートに関する情報が少ない！
「スプレッドシート」「Googleスプレッドシート」「SpreadSheet」などで検索してもExcelに関する情報が出てきてしまったりして、なかなかうまく情報がとれません。。。うぬぬ。あとちょっと古かったり。
じゃあ、自分で整理しておくか、ということで、この記事です。あくまで"My"ベストなのでお間違いなく^^;
チュートリアル的に書いたので、上から順番にやっていけば、理解がかなり深まると思います。2018年2月最新版、詳細図付きです！
Myベスト1 条件付き書式を使って背景色を行ごと変更する
タスク管理や課題管理をするときに、ステータスを変更したら背景色を行ごと変えるというテクニックです。「済」とか「完了」なら黒くして、「処理中」なら緑、それ以外は白(そのまま)みたいな感じです。
これはほぼ使ってます。ただ、いつもすでに条件付き書式が入っているシートからコピーしてきたりするので、改めてやろうとするとやり方がわからなかったりします。。。
やり方まとめ
データ範囲を選択→条件付き書式→カスタム数式→「=\$D4="済"」→色選択→別の条件設定
以上！！
では、以下詳しいやり方です。
やり方
超簡単なタスク管理表です。
これでステータスに連動して行の色を変えられるようにしてみます。
下は何もしていない状態のデータです。
ステップ1 データ範囲の設定
まずは、ステータスが「済」になっている行の色を変えていきます。その次に別のステータスも変えていきます。
では、まずはタイトルを省くデータ部分を選択しましょう。タイトルは入れちゃダメ。(入れてもいいけどやり方が少し変わっちゃいます)
この例では、B4～D10までとなります。
タスクを追加していくことを考えて、下は10行目まで範囲指定しています。
ステップ2 「条件付き書式」の呼び出し
上のメニューから「表示形式」→「条件付き書式…」を選びましょう。範囲指定はそのままで。
すると、スプレッドシートの右側にこんな「条件付き書式設定ルール」というBOXが出てきます。
「範囲に適用」という部分にさきほど選んだ範囲の「B4:D10」が入ってることを確認しましょう。
これがずれてると上手くいかないので要チェック！間違ってたら直接編集しても大丈夫。
ステップ3 「セルの書式設定の条件」を「カスタム数式」に設定
さきほどのBOXの上から2つめ「セルの書式設定の条件」を選択しましょう。そしてその一番下に「カスタム数式」というものがあるので、これを選択。一番下にあってスクロールしないと出てこないので見落としがち！
選択したらこんな感じになります。
ステップ4 「カスタム数式」の「値または数式」に数式設定
ここが一番悩ましいところ。分からなくなるところです。原理を理解するのに時間かかります。。。
とりあえず「=\$D4="済"」と入れてみましょう。
すると表がこんな感じに。おお、これでだいたいOKですね。上手くいかない人は次の解説をよく見ましょう。
解説
この表とまったく同じB4:D10状態じゃないセルでやった人は上手くいかなかったかも。
「=\$D4="済"」がくせもので、意味を理解すれば、自分のスプレッドシートでも上手くいくはず。
まず、右の"済"は分かりますね？？変更させたい文字をダブルクォーテーションでくくります。文字は""でくくりましょう。
「\$D4」の「4」の部分ですが、これは範囲指定の「B4:D10」の「4」と同じにする必要があります。
範囲の一番上の行ってことです。指定範囲の一番上の行に対する条件式を設定していると考えればOKで、それ以外の行は自動的に同じ条件で設定されます。
「\$D4」の「D」の部分ですが、これは「"済"」が書かれている列を指定しましょう。例ではD列にステータスがあるので、「D」を指定することになります。範囲指定の「B4:D10」の「D」とは関係ありませんよ。
「\$D4」の「\$」に関してですが、これは実際に試してみると分かるのですが、ためしに「=D4="済"」としてみましょう。すると、一番左のB列だけ色が変わるようになります。\$がないと、「B列に対してD列」すなわち2つ右のセルの状態を見ることになります。\$を付けることで、常にD列の状態と比べることができるようになります。
なので、Dにだけ\$をつけることになります。要するに、これらの式は範囲指定の始めのB4に対する条件式と考えれば理解しやすいかと思います。その条件式が他の行と列にもコピーされているという感じです。
範囲指定の一番初めのセルに対して上手くいく条件を指定してやればいいということです。
「=\$D4="済"」の始めの「=」なんですが、これは、「これから式を書きますよ」という合図です。条件の「=」とはまた違うのですよね。これも混乱の元ですが、初めには「=」をつけて書き始めると覚えればよいでしょう。
「=\$D4="済"」の途中にある「=」は条件式の「=」で\$D4と"済"が等しい時という、よくあるやつです。プログラミングが出来る人は始めの「=」に混乱すると思いますが、これはルールと思ってしまえばいいでしょう。
はい。解説は以上です。長かった。でもこれが全てなんですが、これを理解してない人は結構いるんじゃないかと。自分も理解するまで時間がかかりましたので。。。
ステップ5 「書式設定のスタイル」を変える
今回は色だけ変えてみようと思います。"済"なので暗めのグレーにしてみます。
「書式設定のスタイル」の右下の「塗りつぶし」を選んでみましょう。
選んだら、「暗めのグレー3」左から三つ目あたりを選んでみましょう。なんでも大丈夫です。
するとこんな感じになりました。"済"については完成です。文字色とかその他も変えられるので、自分好みにどうぞ。
とりあえず、ここで一旦「完了」ボタンを押しておきましょう。消えちゃうと悲しいので保存はお早めに。
ステップ6 条件を追加する
次は他のステータスにも同じように色を付けましょう。次は「作業中」がターゲットです。
条件を追加する方法ですが、まず前に設定した範囲の上のセルを選択しておきましょう。セルは1つだけで大丈夫です。
その状態で、ステップ2の「表示形式」の「条件付き書式」を選択しましょう。
すると、右の方にこんな「条件付き書式設定ルール」BOXが表示されます。
なんかちょっと違うという人は、正しく範囲内のセルを選択した状態になっているか要チェック。違うセルを選択しているとちょっと違う表示になっちゃいます。
そしてこの状態で、一番上の『「=\$D4="済"」であるカスタムの数式』を選択。
「新しい条件を追加」を選んでもいいけど、範囲指定し直さないといけないので、上のを選択するのがオススメ。範囲指定し直すと微妙にずれたりするので、条件追加のときは、すでに作った数式を一度選びましょう。
すると、さっきの条件式を呼び出せました。
条件式の呼び出し方は大事なので、あえてこの方法にしてみました。
ここで下の方にある「条件を追加」を選択しましょう。
(ステップ5で完了を押さずに、そのまま条件を追加してもOKです)
すると、ぬぬぬ、右にスクロールして同じものが出てきましたよ。これ、実は条件付き書式をコピーしてるのです。ということで、
"済"になっているところを"作業中"に、
色を緑っぽい色にしてみましょう。
はい。完成です。条件追加は簡単でした。ただし、範囲を間違えないようにしましょう。
ちなみに、完成した条件はこんな感じになります。他にも追加したい場合は同じようにどうぞ。
ステップ7 行や項目の追加
ここからは運用ですが、実際にはタスクが増えていったり、期限だけじゃなくて開始日を追加したくなったりします。
そんな時は、初めに設定したB4:D10の範囲内で、行か列の挿入をしましょう。そうすれば、数式を変更することなく、自動で上手いことやってくれます。
でも、共同作業をしているとそんなこと聞いてないよってことになるので、自分で作るスプレッドシートはこんな風に「ここより上の行に挿入してください」とか書くようにしてます。これで範囲外に何かを追加されることはないはず！！
Myベスト2 期限が過ぎたアイテムに色を付ける
タスク管理なので、期限が過ぎちゃったら注目できるようにしておきたいところ。期限が過ぎたらC列の日付を太字の赤にするようにしてみます。
「Myベスト1：条件付き書式を使って背景色を行ごと変更する」で基本は説明してあるので、全く同じところは少し省略して説明します。
やり方まとめ
データ範囲を選択→条件付き書式→次より前の日付→色選択→条件の順番変更
以上！！
では、以下詳しいやり方です。
やり方
「Myベスト1：条件付き書式を使って背景色を行ごと変更する」で作ったスプレッドシートをそのまま使ってみます。この状態で文字色を変えてみましょう！基本的なやり方は条件付き書式です。日付の扱いとか、すでにある条件との整合性とかを注意しましょう。
日付は、ステータスが「未」のものを今日より後の日付、それ以外が過去の日付にしておいてください。その前提で話を進めています。(そうじゃなくても大丈夫です)
ステップ1 条件付き書式設定ルールの呼び出し
まずは、範囲指定。今回色を付けるのは日付だけなので、日付の列C4:C10の範囲を選びましょう。(前回と同じように、タイトルは選ばないようにしましょう)
そして、「表示形式」→「条件付き書式…」を選びましょう。
Myベスト1でやっているので途中の図は省略。こんなのが右に出てればOKです。
ステップ2 新しい条件を追加
今回は「新しい条件を追加」でやります。範囲が違うからですね。条件も違うのでこちらを選んでおきましょう。
こんな感じになればOKです。範囲がC4:C10になってます。
ステップ3 「セルの書式設定の条件」を設定
「セルの書式設定の条件」を選択して、「次より前の日付」を選びましょう。期限が過ぎているので、「今日より前の日付だったら色を変える」っていう考えですね。
「次より前の日付」を選択すると、この画面になるはずです。「本日」というのが新たに増えています。これを選択すれば1週間前とか細かい調整はできるのですが、今回は期限切れの色を変えるということで、デフォルトの「本日」のままにしておきます。これで条件は完了です。
ぬぬぬ、左の表を見てもまだ何も変わってないよ、という人、それで合ってます。というかまだ足りていない設定があるので、もうちょっと我慢しましょう。。。
ステップ4 「書式設定のスタイル」を設定
期限切れのものを赤の太字にしましょう。背景色はいらないので「なし」にしておきます。こんな感じです。
左のBのところを選択。右から2番目のAを選んで赤を選択。右の塗りつぶしを選んで「なし」を選択です。
ぬぬぬ、まだ実際の表に反映されてないという人、焦らないで。
とりあえず、「完了」ボタンを押して確定させておきましょう。
ステップ5 「条件付き書式設定ルール」の順番を入れ替え
「条件付き書式設定」には優先順位があります。今作ったルールは3番目のルールになってしまうので、他のルールの方が優先度が高くなってしまっています。文字を赤くするよりも、背景色を変えるルールの方が強くなっているということです。
ちなみに、今このように右のBOXに出てないよという人は、C列のセルを選択すると全部の条件が出てきます。選択されているセルにある設定ルールが出てくるというわけです。
順番の入れ替えですが、条件の左の方にカーソルを当てるとカーソルが十字になります。その状態で、ドラッグ＆ドロップすると入れ替えができます。左の方じゃないと動かないので要注意です。カーソルが十字になるとドラッグ可能です。
こんな感じで、真ん中に入れてみましょう。
すると、作業中のところの色が赤に変わりました！期限に書かれてる日付は上手く調整してみてください。これを書いているのが2018年2月28日なので、こうなってます。
ちなみに、順番を一番上にしてしまうと、"済"の状態のものも赤くなってしまいます。"済"のステータスのものは赤くする必要がないので、順番は"済"のものの次に配置することになります。3つ以上の色を条件で分けている場合は要確認です。
むむむ、ちょっと格好悪い？？背景色が緑じゃない、って気になる人いますか？？
そんな人のために、もう1ステップ追加です。
ステップ6 複合条件を設定する
複数のスタイルを結合することはできないので、「"作業中"で期限が過ぎていたら、背景を緑にして赤で太字にする」っていうことをしないといけません。
そのためには、まず右の「条件付き書式設定ルール」の「今日より前の日付」を選択しましょう。
この条件はそのまま使いたいので、「条件を追加」を選びます。
そして、「次より前の日付」の代わりに「カスタム数式」を選びます。条件式には次のように入れてみましょう。
書式設定の背景色は緑にしましょう。こんな感じになります。
条件式については難しいので解説です。
解説
「=AND(D4="作業中", C4<Today())」が条件式ですが、意味としては、「D4が作業中で、かつ、C4が今日より小さい数字」の場合ということになります。
「=AND(A, B)」というのが、AかつBという式になります。
Aの部分は、Myベスト1で書いたように、D4が作業中という文字だったらと言う意味です。
「Today()」は今日の日付を意味しているので、C4が今日より前の日付だったらということになります。
条件を結合するときは、こんな感じで書くと上手く行きます。
で、これでもまだダメで、順番の入れ替えが必要です。順番はこのようにしましょう。
強い条件の方を上の方に置くってことですね。文字色を赤にして背景を緑にする方を優先順位を高くします。単に緑にするだけのものを上に置いてしまうと、赤くなりません。
4番目にも赤にする条件があるのは、"作業中"以外のステータスのときに赤にする必要があるからです。3色以上使っている人は、赤にしたい背景色だけ追加が必要になります。
結構大変ですね。
結果はこうなります。いい感じです。
条件式が増えてしまうので、それが嫌な人は、B列とC列の間に「期限切れ」の列を追加してその列だけ色が変わるようにするとか、期限列の背景が変わっても変にならないように、ステータスの右に持っていって背景は常に白にしておくとか色々方法はあります。
テクニックを見せるということで、今回はこの方法を紹介してみました。
Myベスト3 日付を英語表記にする
仕事では海外の人と一緒にやることも多いので、日付表記は誤解のないようにしておきたいところ。
2018/06/05は日本だと6月5日とすぐわかりますが、5月6日なのか6月5日なのかわからくなることがよくあります。
なので、表記は誤解のないように、「5 Jun. 2018」というような「日＋月(英語表記)＋年(4桁)」にしています。
これなら誰が見ても間違えないはず。
やり方まとめ
ファイル→スプレッドシートの設定→アメリカ合衆国→表示形式→数字→表示形式の詳細設定→dd mmm, yyyy
以上！！
では、以下詳しいやり方です。
やり方
簡単なのですが、罠にはまるとやっかいです。
表示形式の数字を使うのですが、日本語設定のままやろうとすると「05 8月, 2018」とかにしかならない。8月の部分を英語にできない！！
しかも、google全体の設定を英語にする方法と、今見ているスプレッドシートだけ英語にする方法の2種類があって、後者を使わないといけません。いつも間違えてしまいます。。。
ステップ1 「言語と地域」設定をアメリカ合衆国に
日付の表記を変えられるので、まずはその設定にたどり着きましょう。
左上の「ファイル」から「スプレッドシートの設定」を選びましょう。
するとこんな画面が出てくるので、一番上の「言語と地域」で「日本」となっているところを、「アメリカ合衆国」に変更します。(別にアメリカじゃなくても英語表記ができる国にすれば大丈夫)
アメリカ合衆国は上の方にあるので、選択したら、「設定を保存」ボタンで戻りましょう。
これで下準備は完了です。
これは、今使っているスプレッドシートに関してだけの設定です。なので、変更したい場合はスプレッドシートごとにこの設定を変更しましょう。
ステップ2 日付表記を変更
まず、日付全体を選択します。今回の場合はC4:C10を選択範囲とします。
選択された状態で「表示形式」→「数字」→「表示形式の詳細設定」→「カスタム数値形式…」を選びましょう
そこで「カスタム数値形式」が出てくるので、
「dd mmm, yyyy」と入力して、適用ボタンを押しましょう。
ddが日、mmmが月、yyyyが年となっています。mmのように2つだと数字で月を表現しますが、mmmのように3つだと英語表記になります。年はyyyyと4桁がオススメです。yyのように2桁もできますが、日と年の違いが分からなくなるので、4桁がオススメ。
出来上がりはこのようになります。これでどの国の人が見ても間違えないですね。
設定を日本に戻してしまうと、表記も戻ってしまうので、アメリカ合衆国のままにしておきましょう。
さいごに
よく使うテクニックのまとめでした。よかったら参考にしてみてください。
条件付き書式は少しずつ応用していけば、色々できて表が見やすくなりますよ！
Excelでやる場合は、みたいなことも書きたかったのですが、それはまた次回で。
特に英語表記にするのは、Excelとはやり方が違うので、最初は見つけるまでに時間がかかりました。条件付き書式はそんなに変わらないはずです。