「セル番地」を特定し、簡単にタテヨコ変換する超絶技
「エクセル評論家」でもある当ウェブサイトの著者は、『エク達』というウェブサイトを運営していました。本稿はそのコンテンツ『「セル番地」を特定し、簡単にタテヨコ変換する超絶技』の転載版です。基本的に用語、言葉遣い等については原文どおりに転載しています。
当ウェブサイトのひとつの「ウリ」は、誰でも簡単に使える関数を工夫することで、信じられないほど高度な仕事を正確・迅速に実施することにある。こうしたなか、当ウェブサイトで「イチオシ」している関数をいくつか組み合わせるだけで、驚くほど簡単にデータの入れ替えが出来てしまう。
タテヨコ変換
エクセルを使った業務効率化という視点において、何より重要なのは、「難しいことをする」のではなく、「できるだけ最短ルートで目的を達成すること」にある。
こうしたなか、当ウェブサイトが関心を持って研究しているテーマのひとつが、「タテヨコ変換」だ。
たとえば、日銀が公表する『BIS国際与信統計（最終リスクベース）』という資料がある（図表１）。
図表１ BIS国際与信統計（最終リスクベース）（※シート名は「s」としておく）
（【出所】日銀ウェブサイト）
便宜上、シート名を「s」とでもしておく。
これは、タテ（行）に各地域・国の名称、ヨコ（列）に四半期（３ヶ月毎）のデータが格納されたものだ。
しかし、これについてはこのままだと加工し辛い。
そこで、このデータをじっくり眺めてみると、ひとつの法則に気付く。
まずは３ヶ月毎の日付を入力
たとえば、データの始点はD列に格納された2004年12月で、E列が2005年3月、F列に2005年6月、といった具合に、３ヶ月毎にデータが格納されている。こうしたデータの構成は、非常にわかりやすい。
ここで、空白シートを立ち上げ、A2セルに「2004/12/31」、A3セルに次の計算式を入力してみよう。
=DATE(YEAR(A2),MONTH(A2)+4,1)-1
そのうえで、これをA68セルまで「Ctrl+D」などを使ってそのままコピーすれば、ちょうどA68が日本銀行データの最新版である「2021/06/30」に到達する（図表２、図表３）。
図表２ DATE関数の入力
図表３ A68セルまでCtrl+Dなどでコピー
そのうえで、B2セルに次の計算式を入力し、B68セルまでコピーする。
=TEXT(A2,”yyyy年m月末”)
その結果が、図表４だ。
図表４ B2～B68まで、TEXT関数を入力
このTEXT関数の意味は、『日付から曜日を抽出する：「地味に役立つ」テクニック』などでご確認いただきたい（※）。
（※）転載にあたっての注：『日付から曜日を抽出する：「地味に役立つ」テクニック』については、転載が完了していません。いずれ当ウェブサイト『新宿会計士の政治経済評論』側に転載したいと思います。転載順序が前後して申し訳ありません。
データの行列番号を特定する
ここまで下準備が出来上がれば、あとは簡単だ。
C2セルに次の計算式を入力してC68セルまでコピーする。
=MATCH(B2,s!$4:$4,0)
すると、図表５のとおり、C列に数値が表示される。これこそ、その年月に対応する、元データの列番号である。
図表５ C2～C68まで、MATCH関数を入力
そのうえで、C1セルあたりに「米国」と入力し、隣のD1セルあたりに次の計算式を入力してみよう。
=MATCH(C1,s!$C:$C,0)
すると、今度はD1セルに、元シート上の「米国」に対応する行数である「45」が表示された（図表６）。
図表６ D1セルにMATCH関数を入力
あとは、D2セルに次の計算式を入力し、D68セルまでコピーしよう。
=INDIRECT(“s!R”&D$1&”C”&$C2,0)
最後の「0」は、「R1C1形式で参照する場合」のINDIRECT関数の引数である（図表７）。
図表７ D2～D68まで、INDIRECT関数を入力
すると、こんな具合に、数値が表示された（図表８）。
図表８ 入力結果
国名を入れ替えるだけで数字が切り替わる！
じつはこのシート、とってもパワフルである。
C1セルの「米国」を、たとえば「英国」、「フランス」、「中国」などと置き換えると、自動でその国のデータを引っ張ってくれるからだ。ためしに「英国」と入力した例が、図表９である。
図表９ C1セルを変更した場合の事例
この計算式だと、ワークシートも非常に軽く、手元で国名を変えるだけで、すぐにデータを入れ替えることができる。大変便利だ。
是非、こうしたテクニックを活用してみてほしい。
本文は以上です。
日韓関係が特殊なのではなく、韓国が特殊なのだ―――。
— 新宿会計士 (@shinjukuacc) September 22, 2024
そんな日韓関係論を巡って、素晴らしい書籍が出てきた。鈴置高史氏著『韓国消滅』（https://t.co/PKOiMb9a7T）。
日韓関係問題に関心がある人だけでなく、日本人全てに読んでほしい良著。
読者コメント欄はこのあとに続きます。当ウェブサイトは読者コメントも読みごたえがありますので、ぜひ、ご一読ください。なお、現在、「ランキング」に参加しています。「知的好奇心を刺激される記事だ」と思った方はランキングバナーをクリックしてください。
ツイート @新宿会計士をフォロー読者コメント一覧
※【重要】ご注意：他サイトの文章の転載は可能な限りお控えください。
やむを得ず他サイトの文章を引用する場合、引用率（引用する文字数の元サイトの文字数に対する比率）は10％以下にしてください。著作権侵害コメントにつきましては、発見次第、削除します。
※現在、ロシア語、中国語、韓国語などによる、ウィルスサイト・ポルノサイトなどへの誘導目的のスパムコメントが激増しており、その関係で、通常の読者コメントも誤って「スパム」に判定される事例が増えています。そのようなコメントは後刻、極力手作業で修正しています。コメントを入力後、反映されない場合でも、少し待ち頂けると幸いです。
※【重要】ご注意：人格攻撃等に関するコメントは禁止です。
当ウェブサイトのポリシーのページなどに再三示していますが、基本的に第三者の人格等を攻撃するようなコメントについては書き込まないでください。今後は警告なしに削除します。なお、コメントにつきましては、これらの注意点を踏まえたうえで、ご自由になさってください。また、コメントにあたって、メールアドレス、ＵＲＬの入力は必要ありません（メールアドレスは開示されません）。ブログ、ツイッターアカウントなどをお持ちの方は、該当するＵＲＬを記載するなど、宣伝にもご活用ください。なお、原則として頂いたコメントには個別に返信いたしませんが、必ず目を通しておりますし、本文で取り上げることもございます。是非、お気軽なコメントを賜りますと幸いです。
コメントを残す
【おしらせ】人生で10冊目の出版をしました
  | 自称元徴用工問題、自称元慰安婦問題、火器管制レーダー照射、天皇陛下侮辱、旭日旗侮辱…。韓国によるわが国に対する不法行為は留まるところを知りませんが、こうしたなか、「韓国の不法行為に基づく責任を、法的・経済的・政治的に追及する手段」を真面目に考察してみました。類書のない議論をお楽しみください。 |
【おしらせ】人生で９冊目の出版をしました
 | 日本経済の姿について、客観的な数字で読んでみました。結論からいえば、日本は財政危機の状況にはありません。むしろ日本が必要としているのは大幅な減税と財政出動、そして国債の大幅な増発です。日本経済復活を考えるうえでの議論のたたき台として、ぜひとも本書をご活用賜りますと幸いです。 |
VBA使いましょう。
コメントありがとうございます。
下手な人が組んだマクロなどを使うと業務にかなりの支障が出ます（経験者談）。
『エク達』の趣旨はマクロを極力排除して、誰にでもわかりやすい関数をフル活用するという点にあります。
これは、いわば「仕事の哲学」のようなものでしょう。
調べてみたら Range.PasteSpecial で行列入れ替えできるのですね。
・・・「転置して貼り付け」じゃだめですか？
∧＿∧ ／￣￣￣￣
（ ´･ω･) ＜ お茶が美味しいのです♪
（ つ旦と） ＼＿＿＿＿
と＿）＿）
転置して貼り付けだと元のｓのシートを修正しても、数式によるリンクがないので貼り付け先のシートには修正が反映されません。
タテヨコ返還時に私がよく使うショートカットは
コピー；Ctrl-C
貼り付け時；Alt-E-S-E (-V:値貼り付、-T:形式 などを追加)) [Enter]
とか、使います。これ、旧バージョンのやつですが、体が覚えてしまってるので、、、
今のショートカットだと、、、
貼り付け時；Alt-V-S （以下同様） ですか。。。
行列形式になってしまい使いづらいため賛否ありますが、
Transpose ワークシート関数なども使ったりします。
ちなみに私は列をABC…で表現するとExcelがほとんど使えない人です。
設定時に必ずR1C1形式を選択してから使うようにしています。
たいていの場合 A1形式は「慣れている」というだけで選定されがちですが。
R1C1形式は「これでないときわめて不便」「論理的整合性に優れる」というメリット、、、というか、Ａ１形式はここに致命的欠陥あり、と、考えています
・・・Index関数とか、Vlookup関数とか、
貼り付け時に違うものが張り付けられてるように見えるとか、・・・
TRANSPOSEなんて便利なものがあるとは知りませんでした。
しかもペーストと違って範囲外の参照まで維持してくれるなんて賢すぎる^^
INDIRECTは揮発性関数という分類のもので、
数値入力のたびに再計算が発生するため、
数が多くなってくると動作が重くなりがちです。
出来れば利用を避けて、普通の関数であるindex関数に置き換えた方が良いですね。
これはたしかに超絶技！
ただ個人的には…
・串刺し以外の他シート参照
・引数の値を入れるためだけのセル
・引数のセル範囲に名前
・R1C1参照形式
…などを始めたらスクリプト言語を学ぶタイミングだと思っているけど、今回三つ当てはまったり。
特にROWインデックスとCOLUMNインデックス用の値の専用セルを配置→番地文字列作成→番地参照とか、もう普通の二次元配列操作で良いのでは^^