Excel。ガントチャートで期間の「始点～終点」を見出しから抽出してつくりたい。
＜ガントチャート：INDEX+MATCH関数＞
スケジュール期間をわかりやすく管理するガントチャートという表があります。
作業Aは、6月1日に最初の○があるので、B列は、6/1～と表示され、6月3日に最後の○があるので、6/3と判断した結果、B2には、「6/1～6/3」と自動的に算出し表示しています。
今回は、この期間の作り方を紹介していきます。
どうしたら、最初の「○」がある日付を抽出することができるのかを考えていきましょう。
C1:G2までの表で考えるとわかりやすくなります。
日付がある行は、1行だけなので、行数は1で、列は、5列ある表。
つまり、1行5列の表の中から、最初の「○」がある6月1日を抽出させるには、6月1日は、1行1列のデータを抽出すればいいわけです。
このような、行と列から抽出する時には、INDEX関数を使うことで算出することができます。
問題は、最初の「○」が1列目で、最後の「○」は3列目にありますが、この1とか3をどうやって計算させればいいのでしょうか？
何番目に「○」があるのか？
このような時には、MATCH関数を使うことで算出することができます。
B2をクリックして、数式をつくっていきます。
INDEX関数は、2種類選べる関数になっているので、手入力の方がわかりやすいかと思いますので、手入力でつくることをお勧めします。
B2の数式は、
=INDEX($C$1:$G$1,1,MATCH("○",C2:G2,0))
2020/6/1という算出結果がでました。結果がシリアル値の場合は、日付型に変更しましょう。
この計算式について説明します。
INDEX関数は、INDEX(配列,行番号,列番号)という引数をもっていますので、
配列には、$C$1:$G$1 を設定します。
オートフィルで数式をコピーすることを考慮して、絶対参照も合わせて設定しておきます。
行番号は、1行なので、1。
列番号は、最初の「○」がある場所の数値ですが、この数値をMATCH関数で算出します。
MATCH関数を確認します。
MATCH関数は、MATCH(検索値,検索範囲[,照合の種類])という引数を持っています。
検索値には、「○」。
検索範囲には、C2:G2。
照合の種類ですが、0を設定します。0は、完全一致する値を算出します。
また、最初に登場する値を算出することができます。
よって、0を設定することで、「1」を得ることができるというわけです。
なお、「0」の代わりに「-1」にすると、検索値の中で最小値を算出してくれるので、「-1」でもかまいません。
このように、INDEX関数とMATCH関数を組み合わせることで、抽出することができます。
この数式を、「～」で挟む数式に修正していきます。
=INDEX($C$1:$G$1,1,MATCH("○",C2:G2,0))&"～"&INDEX($C$1:$G$1,1,MATCH("○",C2:G2,1))
ちょっと、長くなりましたが、「&"～"&」で始点の見出しの日付から終点の見出しの日付までという形で表示することができます。
ただ、表示形式が解けてしまうので、シリアル値で表示されてしまっています。
日付に表示形式を変更したいのですが、文字結合していることもあって、通常の表示形式の変更ではかわりませんので、TEXT関数をつかってあげる必要があります。
よって、最終的な数式は、
=TEXT(INDEX($C$1:$G$1,1,MATCH("○",C2:G2,0)),"m/d")&"～"&TEXT(INDEX($C$1:$G$1,1,MATCH("○",C2:G2,1)),"m/d")
とさらに長くなってしまいましたが、オートフィルで数式をコピーして完成です。
ちょっと長い数式ではありますが、作業工程に変化があった場合でも、この表の場合だと、「○」を追加削除するだけで、期間がオートマチックに連動して変更することができるので、ミスが抑制できるかもしれませんね。
