Coding Methodology

便利な数式でスプレッドシート二重管理の手間を減らす!

2021 / 11 / 01

制作の現場では、サイトマップやディレクトリマップの情報をまとめるのに、エクセル、スプレッドシートなどの表計算ソフトがよく使用されています。このような資料はひとつにまとめておきたいところですが、今回はスプレッドシートを二重管理せざるを得ない場合、データの不一致を防ぐためにできる限り項目を共通化する数式を紹介します。

便利な数式でスプレッドシート二重管理の手間を減らす!

使用する数式は「VLOOKUP」と「IMPORTRANGE」

1.VLOOKUP

指定した範囲の中から検索条件に一致するデータを抽出できる数式です。
図1は使用例です。左がサイトマップで右がサイトマップの情報を利用して進捗ステータスを管理する例になります。黄色のセルをVLOOKUPを使用して表示しています。
F4のセルは以下の数式を設定しています。

=VLOOKUP(E4,A$4:C$5,2)

A4からC5の範囲で、ページID「2」を検索し、検索に引っかかった2列目(B列)の情報を表示します。

【図1】

構文

=VLOOKUP(検索値,範囲,列番号,[検索の型])

検索値:検索する値を指定します。
範囲:検索するスプレッドシートのセルの範囲を指定します。(例:A4:C5)
列番号:検索値がヒットした情報の何列目のセルを抽出するかを指定します。
検索の型:TRUEかFALSEを指定します。TRUEは最大値、FALSEは完全一致になります。今回の記事では使用しないので詳しい説明は割愛します。

2.IMPORTRANGE

指定した範囲のデータを別のスプレッドシートに表示することができる数式です。
例えば、クライアントから提供されたスプレッドシートを編集せずに、自社で管理するスプレッドシートに指定したデータを表示することができます。
提供元のスプレッドシートが更新されると、自社管理のスプレッドシートの情報も自動で更新されます。

構文

=IMPORTRANGE(“スプレッドシートキー“,”シート名!範囲“)

スプレッドシートキー:スプレッドシートキーは、元のスプレッドシートURLの「d/」より後の部分で、以下の赤文字の部分です。
例:https://docs.google.com/spreadsheets/d/1FIiOMGbnrdb5VL1r0DZniIlN4/edit
シート名:参照したいシート名を指定します。
範囲:参照したいセルの範囲を指定します。(例:A4:C5)

2つの数式を組み合わせて二重管理の負担を軽減

図1でVLOOKUPの指定をした範囲にIMPORTRANGEをあわせて指定することで、同じスプレッドシートにあったサイトマップと進捗管理の表を別のスプレッドシートで管理することができます。例えば以下のように数式を構成することができます。

=VLOOKUP(E4,IMPORTRANGE("1FIiOMGbnrdb5VL1r0DZniIlN4","サイトマップ!A$4:C$5"),2)


検索値となるページIDは、ページごとに重複しないIDでしっかり管理してあげる必要はありますが、他の情報はスプレッドシートごとに自由に更新できますので、更新頻度が高くても、管理の手間を減らすことができます。スプレッドシートを二重管理にせざるを得ない場合は、この2つの数式を組み合わせて使用することを検討してみてください。

一覧に戻る