「セル番地」を特定し、簡単にタテヨコ変換する超絶技

当ウェブサイトのひとつの「ウリ」は、誰でも簡単に使える関数を工夫することで、信じられないほど高度な仕事を正確・迅速に実施することにある。こうしたなか、当ウェブサイトで「イチオシ」している関数をいくつか組み合わせるだけで、驚くほど簡単にデータの入れ替えが出来てしまう。

タテヨコ変換

エクセルを使った業務効率化という視点において、何より重要なのは、「難しいことをする」のではなく、「できるだけ最短ルートで目的を達成すること」にある。

こうしたなか、当ウェブサイトが関心を持って研究しているテーマのひとつが、「タテヨコ変換」だ。

たとえば、日銀が公表する『BIS国際与信統計(最終リスクベース)』という資料がある(図表1)。

図表1 BIS国際与信統計(最終リスクベース)(※シート名は「s」としておく)

(【出所】日銀ウェブサイト

便宜上、シート名を「s」とでもしておく。

これは、タテ(行)に各地域・国の名称、ヨコ(列)に四半期(3ヶ月毎)のデータが格納されたものだ。

しかし、これについてはこのままだと加工し辛い。

そこで、このデータをじっくり眺めてみると、ひとつの法則に気付く。

まずは3ヶ月毎の日付を入力

たとえば、データの始点はD列に格納された2004年12月で、E列が2005年3月、F列に2005年6月、といった具合に、3ヶ月毎にデータが格納されている。こうしたデータの構成は、非常にわかりやすい。

ここで、空白シートを立ち上げ、A2セルに「2004/12/31」、A3セルに次の計算式を入力してみよう。

=DATE(YEAR(A2),MONTH(A2)+4,1)-1

そのうえで、これをA68セルまで「Ctrl+D」などを使ってそのままコピーすれば、ちょうどA68が日本銀行データの最新版である「2021/06/30」に到達する(図表2図表3)。

図表2 DATE関数の入力

図表3 A68セルまでCtrl+Dなどでコピー

そのうえで、B2セルに次の計算式を入力し、B68セルまでコピーする。

=TEXT(A2,"yyyy年m月末")

その結果が、図表4だ。

図表4 B2~B68まで、TEXT関数を入力

このTEXT関数の意味は、以前の『日付から曜日を抽出する:「地味に役立つ」テクニック』などでご確認いただきたい。

データの行列番号を特定する

ここまで下準備が出来上がれば、あとは簡単だ。

C2セルに次の計算式を入力してC68セルまでコピーする。

=MATCH(B2,s!$4:$4,0)

すると、図表5のとおり、C列に数値が表示される。これこそ、その年月に対応する、元データの列番号である。

図表5 C2~C68まで、MATCH関数を入力

そのうえで、C1セルあたりに「米国」と入力し、隣のD1セルあたりに次の計算式を入力してみよう。

=MATCH(C1,s!$C:$C,0)

すると、今度はD1セルに、元シート上の「米国」に対応する行数である「45」が表示された(図表6)。

図表6 D1セルにMATCH関数を入力

あとは、D2セルに次の計算式を入力し、D68セルまでコピーしよう。

=INDIRECT("s!R"&D$1&"C"&$C2,0)

最後の「0」は、「R1C1形式で参照する場合」のINDIRECT関数の引数である(図表7)。

図表7 D2~D68まで、INDIRECT関数を入力

すると、こんな具合に、数値が表示された(図表8)。

図表8 入力結果

国名を入れ替えるだけで数字が切り替わる!

じつはこのシート、とってもパワフルである。

C1セルの「米国」を、たとえば「英国」、「フランス」、「中国」などと置き換えると、自動でその国のデータを引っ張ってくれるからだ。ためしに「英国」と入力した例が、図表9である。

図表9 C1セルを変更した場合の事例

この計算式だと、ワークシートも非常に軽く、手元で国名を変えるだけで、すぐにデータを入れ替えることができる。大変便利だ。

是非、こうしたテクニックを活用してみてほしい。