Aさん たまに横方向にデータを蓄積していくタイプの表がありますが、縦方向の表にするために毎回コピーし、「形式を選択して貼り付け」の「行/列の入れ替え」で対応しています。
元の表が更新される度にこの対応をするのが面倒ですが、何か良い方法はありますかね？
森田 その場合は、関数の「TRANSPOSE」を活用すると良いですよ！
では、TRANSPOSEの使い方について解説していきますね。
はじめに
この記事は関数の概要を把握していることが前提です。
関数の概要については以下の記事をご参照ください。
はじめに 本題に入る前に、この記事がおすすめな方を挙げてみます。 実務でExcelを用いる事務作業が多い方 E …
表の行列の入れ替えを自動化したい場合は「TRANSPOSE」が有効
実務では、たまに横方向にデータを蓄積していくタイプの表に遭遇することがあります。
しかし、横方向にデータを蓄積していく表はデータベースとして使い勝手が良いものではありませんので、縦方向にレイアウト変更する方が無難です。
よって、表の行（縦軸）と列（横軸）を入れ替えると効果的です。
この行列の入れ替えが1回限りであれば、コピペ（コピー→「形式を選択して貼り付け」→「行/列の入れ替え」のチェックON）で十分ですが、元の表データが定期的に更新される場合、いちいち手作業で一連のコピペを繰り返すこととなり面倒です。
こんな場合、関数の「TRANSPOSE」を使いましょう。
TRANSPOSEは「トランスポーズ」と読む。
TRANSPOSEを使うことで、表の行列の入れ替えを自動化することが可能です。
もちろん、関数なので一旦数式をセットすれば、元の表データが更新されても、その内容がTRANSPOSEの戻り値側（行列入れ替え後の表）へ自動反映されます。
TRANSPOSEの構文
TRANSPOSEの構文は以下の通りです。
=TRANSPOSE(配列)
配列の縦方向と横方向のセル範囲の変換を行います。
| 引数名 | 必須 | データ型 | 説明 |
|---|---|---|---|
| 配列 | ○ | すべて | 行列を入れ替えたいセル範囲を指定します。 |
【参考】TRANSPOSEは「検索/行列関数」
あくまで参考情報となりますが、TRANSPOSEはリボン「数式」タブの関数ライブラリの「検索/行列」に分類されています。
実際にTRANSPOSEを活用する際は、以下で解説しているように直接入力で挿入していきましょう。
TRANSPOSEの使用結果イメージ
TRANSPOSEを使い、行列の入れ替えを行うイメージは以下の通りです。
今回は「ローデータ」シートのA1~J3セルの表を対象に、行列を入れ替えた結果を「商品マスタ」シートのA1~C10セルへ返しました。
ちなみに、TRANSPOSE をセットした「商品マスタ」シートのA1~C10セルには、すべて同じ数式を一括でセットする「配列数式」という数式で使うことが一般的です。
複数セルに一括で同じ数式がセットされるため、絶対参照等を留意する必要がありません。
この配列数式はテーブル内で使用できませんので、ご注意ください。
よって、TRANSPOSEをセットする場所は普通のセル範囲にしましょう。
その他、TRANSPOSEを配列数式で使用した場合、元の表のセル範囲が広がったら、都度TRANSPOSEの数式を修正しなくてはなりませんので、ご注意ください。
TRANSPOSEの数式の挿入手順
上記の結果を得るための手順は以下の通りです。
- 関数を挿入するセル範囲を選択
※今回はA1~C10セル - 「=tr」等と入力
- サジェストから「TRANSPOSE」を選択し、「Tab」キーで確定
- 行列を入れ替えたいセル範囲を選択
※今回は「ローデータ」シートのA1~J3セル - 「Ctrl」+「Shift」+「Enter」キーで確定
手順①は入れ替え前の表の行列を入れ替えたセル範囲を選択すること。
※例：入れ替え前が「3行×10列」の場合、「10行×3列」のセル範囲を選択
手順①のセル範囲で手順④のセル範囲外を指定した場合、該当セルはエラー値「#N/A」が表示。
手順②の際にIMEを半角英数モードにすること。
手順⑤の後、数式の前後の中カッコ（{}）が自動で付加される。
配列数式を修正する際、修正後は手順⑤が毎回必要となる。
【応用】Excel2021以降またはMicrosoft365の場合は「スピル」でより楽に数式をセット可能
TRANSPOSEを配列数式で使用する場合、次の2点がデメリットです。
- 手順①で事前に入れ替え後のセル範囲を選択しなければならない
- 手順④で参照した表の範囲が変更した都度、数式の修正も必要になる
ただし、Excel2021以降またはMicrosoft365で使用できる「スピル（動的配列数式）」という数式の新機能でTRANSPOSEを使用すれば、2点のデメリットを解消することが可能です。
スピルでのTRANSPOSEは次の手順で使用可能です。
- 関数を挿入するセルを選択
※今回はA1セル - 「=tr」等と入力
- サジェストから「TRANSPOSE」を選択し、「Tab」キーで確定
- 行列を入れ替えたいセル範囲を選択
※今回は「ローデータ」シートのA1~J3セル - 「Enter」キーで確定
スピルの場合、手順⑤は「Ctrl」+「Shift」+「Enter」でなく、通常の「Enter」キーでの確定でOK。
これで、A1セルにしか数式は入っていないにも関わらず、手順④で選択したセル範囲の行列を入れ替えたデータがTRANSPOSEの戻り値となりました。
数式はA1セルにしか入っていませんが、その他のデータはコピーや書式変更等も可能です。
デメリット1はこれで解消ですが、デメリット2を解消するには、事前に手順④の表をテーブルにしておく必要があります。
それにより、テーブルの行列の拡張/縮小に合わせ、TRANSPOSEの戻り値の範囲も連動します。
なお、スピルも配列数式の一種のため、テーブル上にTRANSPOSEをセットできませんので、ご注意ください。
スピルとテーブルの詳細については以下の記事をご参照ください。
はじめに 本題に入る前に、この記事がおすすめな方を挙げてみます。 Excelでデータの集計/分析作業を行うこと …
サンプルファイルで練習しよう！
可能であれば、以下のサンプルファイルをダウンロードして、実際に操作練習をしてみてください。
サンプルファイル_ワークシート関数_TRANSPOSE.xlsx
※サンプルファイルのダウンロードには無料メルマガに登録いただく必要があります。
（上記リンクから登録フォームへ遷移します）
ブックを開いたら、次の手順を実施してください。（今までの解説のまとめです）
- 関数を挿入するセル範囲を選択
※今回はA1~C10セル - 「=tr」等と入力
- サジェストから「TRANSPOSE」を選択し、「Tab」キーで確定
- 行列を入れ替えたいセル範囲を選択
※今回は「ローデータ」シートのA1~J3セル - 「Ctrl」+「Shift」+「Enter」キーで確定
本記事の解説と同じ結果になればOKです！
さいごに
いかがでしたでしょうか？
TRANSPOSEは行列の入り替えの自動化に役立つ関数の一つです。
定期的に行列の入れ替えを行う機会があれば、覚えて活用してみてください。
なお、TRANSPOSE以外にもExcelでのデータ整形の各種テクニックを拙著で解説していますので、こちらも参考にしてみてください。
ご参考になれば幸いですm(_ _)m
森田 行列の入れ替えは、TRANSPOSE以外にもINDEX+ROW+COLUMNやINDEX+MATCH等の複数関数を組み合わせて行うもあります。
こちらは配列数式ではなく普通の数式なので、テーブルの見出し行以外にはセットできるため、テーブル内へ部分的に別表の行列を入れ替えたデータを組み込みたい場合等で活用できると良いですね。
