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

「エクセル評論家」でもある当ウェブサイトの著者は、『エク達』というウェブサイトを運営していました。本稿はそのコンテンツ『「セル番地」を特定し、簡単にタテヨコ変換する超絶技』の転載版です。基本的に用語、言葉遣い等については原文どおりに転載しています。

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

タテヨコ変換

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

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

たとえば、日銀が公表する『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セルを変更した場合の事例

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

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

読者コメント一覧

  1. しきしま より:

    VBA使いましょう。

    1. エク達管理人 より:

      コメントありがとうございます。
      下手な人が組んだマクロなどを使うと業務にかなりの支障が出ます(経験者談)。
      『エク達』の趣旨はマクロを極力排除して、誰にでもわかりやすい関数をフル活用するという点にあります。
      これは、いわば「仕事の哲学」のようなものでしょう。

    2. ななっしー より:

      調べてみたら Range.PasteSpecial で行列入れ替えできるのですね。

  2. 七味 より:

    ・・・「転置して貼り付け」じゃだめですか?

      ∧_∧   / ̄ ̄ ̄ ̄
     ( ´・ω・) < お茶が美味しいのです♪
     ( つ旦と) \____
     と_)_)

    1. 匿名29号 より:

      転置して貼り付けだと元のsのシートを修正しても、数式によるリンクがないので貼り付け先のシートには修正が反映されません。

  3. Hotaka より:

    タテヨコ返還時に私がよく使うショートカットは

    コピー;Ctrl-C
    貼り付け時;Alt-E-S-E (-V:値貼り付、-T:形式 などを追加)) [Enter]

    とか、使います。これ、旧バージョンのやつですが、体が覚えてしまってるので、、、
    今のショートカットだと、、、
    貼り付け時;Alt-V-S (以下同様) ですか。。。

    行列形式になってしまい使いづらいため賛否ありますが、
    Transpose ワークシート関数なども使ったりします。

    ちなみに私は列をABC…で表現するとExcelがほとんど使えない人です。
    設定時に必ずR1C1形式を選択してから使うようにしています。

    たいていの場合 A1形式は「慣れている」というだけで選定されがちですが。
    R1C1形式は「これでないときわめて不便」「論理的整合性に優れる」というメリット、、、というか、A1形式はここに致命的欠陥あり、と、考えています
    ・・・Index関数とか、Vlookup関数とか、
    貼り付け時に違うものが張り付けられてるように見えるとか、・・・

    1. ななっしー より:

      TRANSPOSEなんて便利なものがあるとは知りませんでした。
      しかもペーストと違って範囲外の参照まで維持してくれるなんて賢すぎる^^

  4. べち より:

    indirect関数もよいですが、このケースではindex関数でもいけるのでは?

    indirectだと、数式内に「s!」というシート参照を”文字列”として埋め込むため、もしシート名を変更すると、とたんに動かなくなる危険性があるのが難点ではないでしょうか。

    indirectは、使いこなせばかなり高度なこともできますが、ほかの代替手段がない場合の最後の選択肢かと個人的には思っています。

    1. 匿名 より:

      INDIRECTは揮発性関数という分類のもので、
      数値入力のたびに再計算が発生するため、
      数が多くなってくると動作が重くなりがちです。
      出来れば利用を避けて、普通の関数であるindex関数に置き換えた方が良いですね。

  5. ななっしー より:

    これはたしかに超絶技!
    ただ個人的には…

    ・串刺し以外の他シート参照
    ・引数の値を入れるためだけのセル
    ・引数のセル範囲に名前
    ・R1C1参照形式

    …などを始めたらスクリプト言語を学ぶタイミングだと思っているけど、今回三つ当てはまったり。
    特にROWインデックスとCOLUMNインデックス用の値の専用セルを配置→番地文字列作成→番地参照とか、もう普通の二次元配列操作で良いのでは^^

※【重要】ご注意:他サイトの文章の転載は可能な限りお控えください。

やむを得ず他サイトの文章を引用する場合、引用率(引用する文字数の元サイトの文字数に対する比率)は10%以下にしてください。著作権侵害コメントにつきましては、発見次第、削除します。

※【重要】ご注意:人格攻撃等に関するコメントは禁止です。

当ウェブサイトのポリシーのページなどに再三示していますが、基本的に第三者の人格等を攻撃するようなコメントについては書き込まないでください。今後は警告なしに削除します。なお、コメントにつきましては、これらの注意点を踏まえたうえで、ご自由になさってください。また、コメントにあたって、メールアドレス、URLの入力は必要ありません(メールアドレスは開示されません)。ブログ、ツイッターアカウントなどをお持ちの方は、該当するURLを記載するなど、宣伝にもご活用ください。なお、原則として頂いたコメントには個別に返信いたしませんが、必ず目を通しておりますし、本文で取り上げることもございます。是非、お気軽なコメントを賜りますと幸いです。

コメントを残す

メールアドレスが公開されることはありません。