category

SpreadSheetでVLOOKUP関数をつかって作業ファイルリストの一覧化と関連付けが見通せるようにする

2019-05-10

Excel を普段から使いこなしてる人とかは当たり前なんだろうけど、今更ながら VLOOKUP を知って、もっと早く知ってれば、、、、ということで忘れる前にメモ。
調べててついでに知ったのが  IMPORTRANGE なので、これはこれでやりたかったことがやれそうなのでまた今度やってみる。

サイトのURLリストを作成

普段はこんな感じでURLリスト的なページ一覧を作ったりするんですが。

それぞれのマスターの管理をどこかでしたいなー、というのと、
CMSのテンプレートを実装する人にとってはテンプレート数がどのくらいあるのか？が必要だし、
HTML実装する人もHTMLを何ページ作らないといけないのか？というのはそれぞれ管理する必要があるな、と。
実際の作業はBacklogとかそういうのでチケット化されるのでそこをみればいいんだけど。

厳密には SpreadSheet 管理と Backlog 管理がわかれてるのでダサいのですがそこは現状は目をつぶるとして・・・
流石に Backlog でURLリスト作成するのは難しい気もするし。。。。

HTML作成ページをリスト化

とりあえず、HTMLの作成ページをリスト化。

実装したHTMLのURLは最近だとHTML開発で自動でリストを作っちゃったりするので SpreadSheet で管理しなかったりもするんだけど、今回の処理を考えると SpreadSheet に入れておいた方が使い回しはしやすい。

作成するテンプレートのリストアップ

次に作成するテンプレートをリストアップ。

この辺はテンプレート数をどのタイミングで出せるのか？ってのが肝かな、、、というところ。

あとはこんなかんじで想定してたけど、作る人によって作りや構成は変わるかもしれないのでエンジニアと相談しながらになることもあるかも。

どのページがどのHTMLを使うのか？ということで、B列のHTMLはHTMLシートの「ページ名」を選択できるようにバリデーションルールを利用する。

HTMLのページ名を対象にする。

これでテンプレートリストのHTML列はプルダウンからHTML選択ができるので問題ない。
ついでに選択した時に開発したHTMLのURLも出てた方もいいだろう、ってことで VLOOKUP を利用する。

テンプレートリストのURL列については

=VLOOKUP(B2,'HTMLリスト'!A$2:B$4,2,FALSE)

のような感じで。

検索キーはHTML列で選択したページ名（=B2）
HTMLリストのシートを検索対象にする（='HTMLリスト'!A$2:B$4）
表示するのは検索対象にしたHTMLリストのURL（2番目）の列（=2）
並べ替え済みかどうか？（=FALSE）

最後のはFALSEと覚えておくととりあえずは良い様子。

並べ替え済み - [既定値は TRUE] - 検索対象の列（指定した範囲の先頭列）が並べ替え済みであるかどうかを指定します。通常は FALSE をおすすめします。
https://support.google.com/docs/answer/3093318?hl=ja

これでテンプレートリストにURLも表示される。

URLリストで各ページがどこから出力されるかを設定

ここまでやったらURLリストで各ページがどのテンプレートを利用するか？とかを設定していく。

どのブログ、どのテンプレートとかもう少し詳細に選択はしていくことになるのだけどそこは割愛。

作ってみて

全体を見たければページリストのシートを観れば良いでしょうし、作業するタイミングによって参照するシートを変えればいいのかなぁ、と。

他にはワイヤーとデザインが分かれてばそれらも追加したうえで、HTMLとの連携までかければよさそう。
ステータスもマスターに掲載しておけばページリストで全体の進捗が見えてくることになりますね。

ただ、進捗・ステータスとかはたぶん作業リストとしてタスク管理ツール側で管理することになるので、そっちを見た方が早いかもしれない。
もしくはAPIを使う形でタスク管理ツールから自動で引っ張ってくるとかそういう作りになっていてもいいのかもしれない。

その辺はおいおい考えていくとして、プロジェクト内での成果物の関連付けとかちゃんとしておかないと探すに探せないし、こういうふうに用意しておけばわかりやすいのかな、、、ということで。

Excelは共有しづらいのでSpreadSheet一択

普段は Excel は共有しづらかったり、最新版がわからなくなるようなこともあるので、 Spreadsheet 一択ですね。
SpreadSheet は機能が足りないのかなぁとか思ったりもしてるけど、そもそも自分が Excel とかの基本的な使い方を知らなすぎた、ということを知りました。

VLOOKUP って言葉はなんとなく聞いたことがあったけど、こんな感じのことをやりたい、って書いたら @tinybeans が教えてくれて調べ直したらドンピシャだった、と。
言葉とか知ってれば調べられるだろうし、もっと検索スキルがあれば、、、、というところなんだけど。

今度つくったシートの感想を聞いてみたいと思います。

参考