エクセルで旧ブログの記事一覧を作った～方法と使った関数、豆知識の覚え書き

ブログの記事タイトル、URLの末尾（スラッグ）、投稿日、を拾い合わせてリンクタグを生成する表を、Excelで作りました。これを使って旧ブログの記事一覧を作った覚え書きです。

ちなみに、旧ブログのスラッグが投稿年月日だったおかげで、今回の作戦は大幅時短できました。スラッグが全ての記事や画像の通し番号だったり、記事の内容を反映した文字列だったりしたら、もっと面倒だったと思います。

というわけで、本題に入ります。

計画

旧ブログのトップページのURLはコレです。→　http://diary-sky-garden.blog.so-net.ne.jp/
基本的には、このURLの末尾に投稿日が付くことで、その記事のURLになります。（うちの旧ブログの場合）

これを踏まえて、以下のような表を作ろう、と考えました。横に長いので半分に分けます。

A～Dのカラムは、手入力する予定の項目です。
実際は、「B：URL用日付」は「A：投稿日」のカラムからオートフィルします。また、カテゴリーで記事をソートして書き写すので、「D:カテゴリー」も、縦にオートフィルで埋められます。
ごりごり入力するのは「A：投稿日」「C:記事タイトル」だけ、ということです。

そして、入力したデータを元に、記事タイトルリンクを生成する計画です。

実行（手入力の部）

手入力部分の最終形は、こうなりました。（データはサンプルです）
解説します。

計画からの変更点

• 同じ日に2記事アップロードすると、日付の末尾に「-1」「-2」と番号が振られていきます。そういう番号が付いていたら入力するカラム「C:URL末尾（あれば）」を追加しました。
• サブカテゴリーも設定してたので、入力欄「E:サブカテ（あれば）」を作りました。

作業内容

全部で300記事と少しあります。管理画面を見ながら、カテゴリーごとに書き写していきます。100記事近くある【うさぎ】カテゴリーでも、30分～1時間ほどで入力できました。

手順は以下のような感じです。

1. 「F:カテゴリー」「E：サブカテ（あれば）」を入力 → 記事数分の行だけオートフィル
2. 「A：投稿日」をテンキーで入力（ずれないように物差しなどで差しながら）
3. 「D:記事タイトル」を入力（昔の記事はだいたい短いので、横目で見ながら打つのが速かった。最近の記事はコピペ中心）
4. 「A：投稿日」を丸ごと選択 → 「B：URL用日付」へオートフィル
5. 「B：URL用日付」と「A：投稿日」が異なる、「C:URL末尾（あれば）」がある、といったイレギュラーを知っている記事は個別に手直し

日付や数字が入ったカラムの表示形式は、次のように設定します。

• 「投稿日」・・・日付 「○○○○年△△月□□日」
• 「URL用日付」・・・ユーザー定義「yyyy”-“mm”-“dd」（画像参照）
• 「URL末尾（あれば）」・・・文字列

実際の画面はこんな感じです。

実行（数式入力の部）

最終形はこのようになりました。
左半分

右半分

ひとつのセルに入る数式が長くなりすぎて、私の頭の限界を超えたので、リンクタグを2段階で生成するように変えました。
以下、関数と数式の解説をしていきます。（たくさん解説するのは最初のひとつだけです）

記事URLの数式

完成形はこんなのです↓

このように出力されます↓
親URL/1900-03-03-5

行番号を表示させる：ROW関数

=”親URL/”&TEXT(INDIRECT(“B”&ROW()),”yyyy-mm-dd”)&INDIRECT(“C”&ROW())

ROWの後の（）に何も記入しないことで、ROWが書かれているセル自身の行番号が返されます。
同じ行から投稿日や記事タイトルを取得するために、ぜひ必要な関数です。行をソートしても、安定して同じ行の番号を返してくれるのがステキです。

特定のセルの中身を引っ張ってくる：INDIRECT関数

=”親URL/”&TEXT(INDIRECT(“B”&ROW()),”yyyy-mm-dd”)&INDIRECT(“C”&ROW())

後半部分を例にとります。この部分では、同じ行の「C:URL末尾（あれば）」のセルに書かれている文字を引っ張ってきます↓
INDIRECT(“C”&ROW())

★以下、（）の中身 “C”&ROW() について。

“C”・・・文字「C」を「”」で囲むことで、「C」をそのまま表示させます。

ROW()・・・上で出てきたROW関数です。行番号を取得します。

&・・・二つ以上の文字列を、ひとかたまりとして表示したいとき、「＆」で繋ぎます。

以上で、INDIRECT の中に C3　C16　などと表示され、 C3やC16に書いてある「URL末尾（あれば）」を引っ張ってこれます。
ちなみに、Cのセルに何も書いてなければ、何も引っ張ってこれないので、何も表示されません。

特定のセルの中身を文字列として表示する：TEXT関数

=”親URL/”&TEXT(INDIRECT(“B”&ROW()),“yyyy-mm-dd”)&INDIRECT(“C”&ROW())

★以下、（）の中身 INDIRECT(“B”&ROW()),“yyyy-mm-dd” について。

INDIRECT(“B”&ROW())・・・欲しいセルの番地。ここでは「B」列（カラム）の、同じ行のセルです。

“yyyy-mm-dd”・・・コンマで区切って、表示形式を指定します。ここでは欲しい日付の形式です。特別なことがなければ、書かなくて大丈夫です。（コンマだけ打っておきます）

文字をそのまま表示させる：”（ダブルクォーテーション）

=“親URL/“&TEXT(INDIRECT(“B”&ROW()),”yyyy-mm-dd”)&INDIRECT(“C”&ROW())

”（ダブルクォーテーション）でくくることで、親URL/の部分は数式として計算されることなく、そのままセルに表示されます。

＆の働き

=”親URL/”&TEXT(INDIRECT(“B”&ROW()),”yyyy-mm-dd”)&INDIRECT(“C”&ROW())

文字列同士をつなげて、ひとかたまりで表示させるときに、「＆」で繋ぎます。上のINDIRECT関数の説明でも登場しました。

以上で、記事URLの式が出来ました。

リンクタグ（前半）の数式

完成形はこんなのです↓

出力↓
＜a href=”親URL/1900-03-03-5″ target=”_blank”＞
※＜＞を全角で打ってあるので、参考にしたい方は注意してください

記事URLの数式の応用で作っていきますが、つまずくのがリンクタグ内の”（ダブルクォーテーション）です。
普通にひとつだけ打つと、数式の一部とみなされるようで、表示が変になります。

”（ダブルクォーテーション）を表示させるには？

=”＜a href=“”“&TEXT(INDIRECT(“G”&ROW()),)&”“” target=“”_blank“”＞”

このように「””」とふたつ続けて打つと、ひとつの「”」が表示されます。「”””」だらけになって目がチカチカしますが、システムはちゃんと認識してくれます。

あとは応用です

★「G:記事URL」を、同じ行から取ってくるために、ROW関数とINDIRECT関数が入ります。
=”＜a href=”””&TEXT(INDIRECT(“G”&ROW()),)&””” target=””_blank””＞”

★「G:記事URL」を文字列として表示するために、TEXT関数でくくります。表示形式は、特別なことがないので指定してません。
=”＜a href=”””&TEXT(INDIRECT(“G”&ROW()),)&””” target=””_blank””＞”

★各リンクタグに共通の文字列を書いて、”（ダブルクォーテーション）でくくります。各ブロックを＆で繋ぎます。
=“＜a href=””“&TEXT(INDIRECT(“G”&ROW()),)&”“” target=””_blank””＞“

以上で、リンクタグ前半部分の式が出来ました。

リンクタグ（全体）の数式

あとは応用するのみです。

★日付なしの記事タイトルリンク。

出力↓
＜a href=”親URL/1900-03-03-5″ target=”_blank”＞・記事タイトル＜/a＞

★日付ありの記事タイトルリンク。

出力↓
＜a href=”親URL/1900-03-03-5″ target=”_blank”＞1900.3.20　記事タイトル＜/a＞

レッツ！　オートフィル！！

数式が出来たら、すべての行にオートフィルします。リンク一覧の完成！

カテゴリーと投稿日でソートして、旧ブログの記事一覧を作ることができました。
また、旧ブログの管理ページを開かなくても簡単にリンクを張れるようになり、とても便利になりました。

おまけ：間違っていたリンクの取りこぼしの程度は？

最後は、エクセルで作ったリンクを記事に貼って「公開」すれば、ワードプレスに入れたリンク切れチェッカーのプラグインが、間違い探しをしてくれます。
ここで見つかる「間違い」は、投稿日とURLの日付が異なる記事です。たとえば、下書きとしてアップロードし、後日公開した場合です。

1. 事前に日付の違いを知ってて直したリンク・・・15個
2. チェッカーが見つけてくれたリンク間違い・・・13個
3. それを直す過程でさらに見つけた間違い・・・0個

しかし、チェッカーのチェックでは、「URLの日付が間違っているのに、間違った日付の日にも記事がある場合」は取りこぼしてしまいます。
というわけで、後からきっかけがあって拾えた間違いが、これだけありました。

• 同じ日付に2記事投稿されていて、チェッカーも見つけられなかった間違い・・・12個

旧ブログの更新終了間際には、基本的に1日1記事と自分ルールを決めて更新していたので、最初からそうだったと思い込んでいました。しかし、初期の頃には、けっこう1日2記事投稿していたと分かりました。
旧ブログの運営期間は、たかだか3年程度ですが、案外覚えていないものだなあ、と思いました。