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

ブログの記事タイトル、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のセル
このように出力されます↓
親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」をそのまま表示させます。

たとえば「A2」と書くと「A2のセルの中身」が表示されますが、
「”A2”」と書けば、文字「A2」が表示されます。

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”・・・コンマで区切って、表示形式を指定します。ここでは欲しい日付の形式です。特別なことがなければ、書かなくて大丈夫です。(コンマだけ打っておきます)

エクセルにおいて日付は、1900年だかを基準とした通し番号で扱われており、そのままだと、意味不明な5桁くらいの数字が出てきてしまいます。
本来は、日付や時刻などの数値を、形式を整えて文字列化するための関数みたいです。

文字をそのまま表示させる:”(ダブルクォーテーション)

=親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の式が出来ました。
記事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””>

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

リンクタグ(全体)の数式

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

★日付なしの記事タイトルリンク。
リンクタグ全体の数式1
出力↓
<a href=”親URL/1900-03-03-5″ target=”_blank”>・記事タイトル</a>

★日付ありの記事タイトルリンク。
リンクタグ全体の数式2
出力↓
<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年程度ですが、案外覚えていないものだなあ、と思いました。

スポンサーリンク
関連記事
関連記事

フォローする