kabuさん、スプレッドシートで今の進捗状況が一目でわかる管理表を作りたいです！
この上部動画のような管理表はどうでしょう？
個々のタスクの状況によって、案件のステータスも変わっていく進捗管理リストです。
今回は
① タスクの状況によって案件のステータスが変わる進捗管理表
② ステータスの数式の説明
③ 値によってセルの書式が変わる「条件付き書式」
といった内容で話をしていきますね。
タスクの状況によって案件のステータスが変わる進捗管理表
進捗管理表の仕様
この管理表の仕様は以下の通りです。
- 個々のタスク（D4:Pセル）が「済」かどうかでセルの色が変わる
- 一つ先、二つ先のタスクが「済」かどうかでさらにセルの色が変わる
- どのタスクまで進んでいるかで「ステータス」が変わる
- 「ステータス」の色は進捗列の色に合わせて変化する
- タスクが全て「済」になると「ステータス」が「納品完了」になる
タスクを「済」にすると・・・
タスク「セルの色」と「ステータス」が変わります。
「ステータス」は次やるべきタスクを表示しています。
へぇー・・・
なんだか難しそうですね。
今回は「条件付き書式」の解説がメインになると思います。
数式は「ステータス」列に入れてある一つだけなのでそこまで難しくはないと思いますよ。
ステータスの数式の説明
C4セルの「ステータス」内の数式はこんな感じです。
=IFERROR(OFFSET($C$3,0,MATCH(TRUE,INDEX(D4:P4="",0),0)),"納品完了")階層で分けてみると・・・
=IFERROR(
OFFSET($C$3,0,
MATCH(TRUE,
INDEX(D4:P4="",0)
,0)
)
,"納品完了")一番深い「INDEX」関数から見ていきましょう。
INDEX(D4:P4="",0)これは何をしているかというと、指定範囲が「””」と一致するか判定しています。
試しにこの数式のみを別のセルに書き写してみるとこんな結果になります。
範囲のセル一つずつに対して「””」と一致していたらTRUE、そうでなければFALSEを返すって内容です。
セルの一つを「済」にしてみると、該当するINDEX関数のセルはFALSEになりました。
このINDEX関数の数式により、参照範囲の中の「空白のセル」を炙り出すことができるわけです。
=IFERROR(
OFFSET($C$3,0,
MATCH(TRUE,
INDEX(D4:P4="",0)
,0)
)
,"納品完了")その外側のMATCH関数は何をしているか見ていきます。
MATCH(TRUE,INDEX(D4:P4="",0),0)範囲の中で・・・
MATCH(TRUE,INDEX(D4:P4="",0),0)「TRUE」と・・・
MATCH(TRUE,INDEX(D4:P4="",0),0)最初に完全一致するもの
これが範囲内の中で何番目にあるかをMATCH関数は出力しています。
このスクリーンショットでは、MATCH関数の出力結果は「2」となります。
これを元にさらに上の階層の数式も見ていきます。
=IFERROR(
OFFSET($C$3,0,
MATCH(TRUE,
INDEX(D4:P4="",0)
,0)
)
,"納品完了")このMATCH関数の結果は「2」となるので、「2」に入れ替えます。
=IFERROR(
OFFSET($C$3,0,
2
)
,"納品完了")OFFSET関数は、軸になるセルから何行、何列移動したセルを出力するという関数です。
OFFSET($C$3,0,2)軸にしているのは、「$C$3」セル＝「進捗確認」とテキストの入っているセルですね。
OFFSET($C$3,0,2)ここから「0」行移動（縦に0移動）・・・
OFFSET($C$3,0,2)「2」列移動（横に2セル移動）したセルを出力しています。
「E3」＝「結果報告」のセルですね。
=IFERROR(
OFFSET($C$3,0,
MATCH(TRUE,
INDEX(D4:P4="",0)
,0)
)
,"納品完了")最後にIFERROR関数部分ですが、このOFFSET関数以内の結果がエラーになったら「納品完了」を出力するよう指定しています。
エラーになる場合っていうのは、（数式の間違いは別にして）INDEX関数がすべてFALSEになり、MATCH関数のTRUEと一致しなくなった時＝全てのタスクが「済」になった時ってことですね。
これで数式の説明は終了です。
作成した数式は、反映したいセルまでコピペしておいてください。
値によってセルの書式が変わる「条件付き書式」 〜ステータス〜
続いてこの「ステータス」が、タスクの「済」具合によって色が変わる仕様の作り方について説明していきます。
上部メニュー ＞ 表示形式 ＞ 条件付き書式 を選択します。
画面の右側に「条件付き書式設定ルール」メニューが表示されます。
適用範囲をステータス列全体「C4:C」を指定します。
「カスタム数式」に切り替えて「=C4=$D$3」と入力します。
塗り色はラベルの色と合わせています。
今回のように、同じセル範囲に複数の条件付き書式を設定する場合は、「条件を追加」を押します。
数式を「=C4=$E$3」に書き換えます。
「条件を追加」をクリックし
数式を「=C4=$F$3」として、塗り色を指定します。
これを色分けしたい分繰り返し行います。
数式は必ずラベルのセルに合わせて変えていってください。
※スクリーンショットでは適用範囲が「C4:P991」となっていますが、これは間違いです。
「C4:C」（または「C4:C〇〇」）としておけばOKです。
こうすることで、ステータスが変わった際に、ラベルの色も変わる仕様にすることができます。
進捗状況でグラデーションになる「条件付き書式」一つ先、二つ先のタスクが「済」かどうかでセルの色が変わる
古い「済」は濃い塗り色になり、新しい「済」は薄い塗り色になる。
行ったり来たりするタスクの不確定な部分を表現した「条件付き書式」です。
え？あ、はい。
良いですね！
古い「済」は濃い塗り色になり、新しい「済」は薄い塗り色になる。
グラデーションのような「条件付き書式」の作成方法を紹介します。
適用範囲はタスク全体。この場合は「D4:P」となります。
「空白ではない」を選択し、薄いグレーを塗り色に指定します。
「条件を追加」をクリックします。
「カスタム数式」を選択し、数式には次の内容を貼り付けます。
=AND(NOT(ISBLANK(D4)),E4="済")これは、このセル（D4セル）が・・・
=AND(NOT(ISBLANK(D4)),E4="済")空白じゃなく・・・
=AND(NOT(ISBLANK(D4)),E4="済")一つ先のセル（D4セルに対してE4セル）が「済」であれば指定した書式が反映されるということになります。
このセルも一つ先のセルも「済」だった際の書式ですね。
塗り色は、先ほどよりも少し濃いグレーを指定して、「条件を追加」を押します。
次は以下の数式を入力します。
=AND(NOT(ISBLANK(D4)),F4="済")先ほどと似ていますが
=AND(NOT(ISBLANK(D4)),F4="済")このセルが空白でなく・・・
=AND(NOT(ISBLANK(D4)),F4="済")2つ先のセルが「済」の場合の書式になります。
塗り色は、さらに濃いグレーを指定して、「条件を追加」を押します。
最後の数式は
=AND(NOT(ISBLANK(D4)),G4="済")この内容になります。
=AND(NOT(ISBLANK(D4)),G4="済")このセルが空白でなく・・・
=AND(NOT(ISBLANK(D4)),G4="済")3つ先のセルが「済」の場合の書式になります。
塗り色は、濃いグレーを指定して、文字の色を白にします。
「完了」を押します。
これでタスクの進み具合によってグラデーションのように色が変わる「条件付き書式」が完成しました。
今回の数式で使用した関数について
IFERROR
IFERROR関数は、式を評価してエラーが発生した場合に、指定した値を返します。これにより、エラーが発生してもスプレッドシートが読みにくくなるのを防げます。
IFERROR(値, エラー時に返す値)
OFFSET
OFFSET関数は、指定された参照点から特定の行数と列数だけ離れた位置にあるセルまたはセル範囲を返します。この関数は動的に範囲を調整したい場合に特に便利です。
cssCopy codeOFFSET(基準セル, 行のオフセット, 列のオフセット, [高さ], [幅])
- 基準セル: オフセットの出発点となるセルを指定します。
- 行のオフセット: 基準セルからの行の移動距離を指定します（下に移動する場合は正、上に移動する場合は負の数値）。
- 列のオフセット: 基準セルからの列の移動距離を指定します（右に移動する場合は正、左に移動する場合は負の数値）。
- 高さ と 幅 (オプション): 返す範囲の行数と列数を指定します。これらを指定しない場合は、単一のセルが返されます。
MATCH
MATCH関数は、指定した値が配列内で最初に現れる位置（インデックス）を返します。配列内の項目を検索し、その位置を知ることが目的です。この関数は、INDEX関数と組み合わせて使われることが多いです。
MATCH(検索値, 検索範囲, [検索タイプ])
- 検索値: 検索する値を指定します。
- 検索範囲: 検索を行う範囲を指定します。
- 検索タイプ (オプション): 検索の種類を指定します（1は昇順で最も近い値、0は完全一致、-1は降順で最も近い値）。
INDEX
INDEX関数は、指定された範囲または配列から特定の位置のデータを返します。特定の行や列の値を抽出するために使用します。
scssCopy codeINDEX(配列, 行番号, [列番号])
- 配列: データが含まれる範囲または配列を指定します。
- 行番号: 抽出する行の番号を指定します。
- 列番号 (オプション): 抽出する列の番号を指定します。この引数を省略した場合、全列が対象となります。
INDEXとMATCHはしばしば組み合わせて使用され、MATCHが見つけた位置情報をINDEXに渡して特定のデータを抽出するという強力なデータ操作を行うことができます。これにより、複雑な検索とデータの抽出が可能になります。
まとめ
今回は
① タスクの状況によって案件のステータスが変わる進捗管理表
② ステータスの数式の説明
③ 値によってセルの書式が変わる「条件付き書式」
といった内容で話をしてきました。
「条件付き書式」・・・
いままで使っていませんでしたが、数式を活用すれば色々なことができるんですね！！
一点、
スプレッドシートには枠線の色も条件付き書式で変えられるようにして欲しいです！
コメント