ブログの記事を書く際に集計したデータを使用したい時があるのですが、その際にスプレッドシートを活用しています。
現在、好きなミュージシャンがYouTubeにカヴァー曲をアップしているので、曲名・歌手名・曲のリリース年度などをまとめているスプレッドシートを更新してます。
そこに「発売年次」という列を作ってありまして西暦4桁の数字で管理してます。YYYY/MM/DD形式だったら年号に変換するのは楽なので表示形式で変更ができなかったので関数作ってクリアしていこうと思います。
※スプレッドシートに関しては無料で使えるエクセル『Googleスプレッドシート』をごらんください。
重複削除&昇順に並び替えする
現在、発売年次をP列に2019年なら『2019』というふうに入力してあります。
発売年次が同じもの、古いもの新しいものごっちゃになっています。
まずはこれを別シートで重複削除を行います。別シートにするのは、並び替えしたら関数が壊れてしまったりするので原本のデータと横並びにすることを避けます。
(1)抽出したいシートに移動します。今回は『抽出』という名称のシートで行います。
①重複なしを抜き出したい列に=UNIQUE(DB!P2:P)
を入力します。
②重複なしのデータが抜き出された一覧が表示されます。
③抽出用のシートです。現在はこのシートをイジっています。
④元データのシートです。このシートから情報を引っ張っています。
(2)(1)で重複を消した値を昇順(古い順)に並び替えます。
①並び替えた値を表示したい箇所に=SORT(C3:C,1,TRUE)
を入力します。(FALSEにすると降順(新しい順)になります。)
②昇順に並び替えられた一覧が表示されます。
(3)先ほど、(1)(2)の関数を一緒にすることも可能です。難しかったらここはスキップでもOK。
①重複なし&昇順で表示したい箇所に=SORT(UNIQUE(DB!P2:P),1,TRUE)
をいれます。
②重複なし&昇順に並び替えられた一覧が表示されます。
③(1)で入力した重複なし一覧の列が不要になりスッキリします。
西暦4桁を年号(和暦)にする
まずは、年号にする際にどこで区切るかを決めます。
令和は2019年5月1日からですが、リリース年度のみしか記載してないので年度のみで判断する関数とします。
下記のような振り分けを行うように関数を書いていきます。
(1)D列の年度(4桁の数字)を見て、②E列に年号を表示します。E3に③の関数を入力します。
=IF(D3>=2019,"令和",
IF(AND(D3>1897,D3<1911),"明治", IF(AND(D3>1912,D3<1925),"大正", IF(AND(D3>1926,D3<1988),"昭和", IF(AND(D3>1989,D3<=2018),"平成",""
)))))
(2)①D列の年度(4桁の数字)と、②E列の年号を見て、③その年号に合わせた年度を表示させます。
③の年度を表示させるために④の関数を入力します。
=IF(E3="明治",D3-1896,
IF(E3="大正",D3-1911,
IF(E3="昭和",D3-1925,
IF(E3="平成",D3-1988,
IF(E3="令和",D3-2018,
)))))
(3)先ほどの(1)(2)で表示した①の年号と年度を、②G列につなげて表示させます。③の関数を入力します。
=IF(D3="","",IF(D3,E3&F3&"年",""))
(4)西暦(数字4桁)を年号(和暦)に変更することができました。
おわりに
と、いうことで『スプレッドシートで西暦(数字4桁)を年号(和暦)に変換する』でした。
スプレッドシートには他にもたくさんの便利機能があるので、紹介していきたいと思います。
この記事のスクショ元のスプレッドシート『STGVのカヴァー曲まとめ(スプレッドシート関数サンプル)』、『STGVのカヴァー曲まとめ(スプレッドシート関数サンプル)_再生数定点』で公開してます。
他にもいろいろな関数を使用してますので、参考に見て行ってください。
このスプレッドシートで収集・集計しているのは、私の応援している大分県の仲良しミュージシャンSTGVのメンバーの方がYouTubeチャンネルにアップしているカヴァー曲の情報です。
データを使用して、カヴァー曲のまとめ記事を書いています。
『おすすめカヴァーが付いている記事一覧』でまとめています。好きな曲、アーティスト、ジャンルがあったらぜひぜひ聴いていってください。
コメント