Excelで横方向に並んだデータを縦方向へ、または縦方向の一覧を横方向へ入れ替えたい場面は少なくありません。
コピーして貼り付ける方法もありますが、元データが更新されるたびに転置作業を繰り返す必要があります。
TRANSPOSE関数を使えば、元の表を参照したまま行と列を自動で入れ替えられます。
売上表、名簿、月別集計、アンケート結果などを見やすく整えたい場合にも役立つ機能です。
行列を関数で入れ替える基本ポイントです。
・Excel 365やExcel 2021ではTRANSPOSE関数を通常どおり入力します。
・転置後の表示範囲は、元の行数と列数が反対になります。
・元のセルを更新すると、入れ替え先の表にも自動で反映されます。
この記事では、エクセルの行列を関数で入れ替える方法と、エラーを防ぐための確認ポイントを順番に解説します。
エクセルの行列を関数で入れ替える方法【TRANSPOSE関数】
それではまず、TRANSPOSE関数で行列を自動変換する基本操作について解説していきます。
| 元データ | A列 | B列 | C列 |
|---|---|---|---|
| 1行目 | 商品名 | 東京 | 大阪 |
| 2行目 | りんご | 120 | 98 |
| 3行目 | みかん | 85 | 110 |
TRANSPOSE関数の入力方法
TRANSPOSE関数は、指定したセル範囲の行と列を交換して表示する関数です。
たとえばA1からC3に元データがある場合、転置先の先頭セルへ数式を入力します。
=TRANSPOSE(A1:C3)
この数式をE1セルに入力すると、A1からC3の3行3列の表が、E1からG3へ行列を入れ替えた状態で表示されます。
TRANSPOSEの引数には、入れ替えたい元データの範囲だけを指定します。
見出しも一緒に向きを変えたい場合は、1行目のヘッダーを含めて範囲を選択することが大切です。
Excel 365とExcel 2021では、Enterキーを押すだけで数式の結果が必要な範囲へ自動展開されます。
古いバージョンのExcelでは配列数式として確定する必要があるため、後述する入力方法も確認しましょう。
転置先に必要なセル数の考え方
行列を入れ替える前に、転置先に空白のセルが十分にあるかを確認します。
元データが3行4列なら、転置後のデータは4行3列になります。
つまり、縦の長さと横の長さが反対になる仕組みです。
元データがm行n列の場合、TRANSPOSE関数の結果はn行m列です。
元の表が縦に長いほど、転置後の表は横に広がります。
転置先のセルに文字や数式が入っていると、スピルエラーが表示される場合があります。
数式を入力する前に、結果が広がる予定の範囲を空白にしておくことが安全です。
表の右側へ展開する場合は、既存の集計表やグラフと重ならない位置を選びましょう。
余白を数列分確保しておくと、あとから元データを追加した場合にも対応しやすくなります。
元データ更新時の自動反映
TRANSPOSE関数で作成した表は、元データと数式でつながっています。
たとえばB2セルの120を130へ変更すると、転置後でB2を参照しているセルも130へ変わります。
手作業でコピーした転置表と異なり、更新漏れを防げる点が関数利用の大きな利点です。
一方で、転置先のセルを直接編集して値だけを変えることはできません。
修正したい内容がある場合は、必ず元データ側の該当セルを編集します。
元表を入力用、TRANSPOSE関数の表を閲覧用として分けておくと、表の役割が明確になります。
【操作のポイント】転置先は計算結果の表として扱い、データの追加や修正は元データ側で行いましょう。
Excel 365でのスピルによる自動展開
続いては、Excel 365でTRANSPOSE関数を入力した後の自動展開について確認していきます。
| 入力場所 | 数式 | 表示結果 |
|---|---|---|
| E1 | =TRANSPOSE(A1:C3) | E1からG3へ自動展開 |
| 元の範囲 | A1:C3 | 3行3列 |
先頭セルだけに数式を入力する操作
Microsoft 365版のExcelでは、転置先の左上にある先頭セルだけを選択して数式を入力します。
たとえばE1セルをクリックし、数式バーまたはセル内へTRANSPOSE関数を入力します。
=TRANSPOSE(A1:C3)
入力後にEnterキーを押すと、Excelが必要なセル範囲を判断し、結果を自動で広げます。
数式をコピーして複数セルへ貼り付ける必要はありません。
自動展開された範囲は青い枠で囲まれて表示されることがあり、これをスピル範囲と呼びます。
数式本体は先頭セルにだけ存在し、ほかのセルはその計算結果として表示されます。
スピル範囲を編集するときの注意点
スピル範囲の途中にあるセルを選択して数値を打ち込もうとすると、変更できない旨のメッセージが出ます。
これは、結果全体が1つの数式から出力されているためです。
転置先の内容を変えたいときは、先頭セルの数式か、参照元の範囲を編集します。
スピル範囲内の一部だけを削除することもできないため、範囲全体を意識して操作する必要があります。
不要になった場合は、先頭セルを選択してDeleteキーを押すと、展開された結果をまとめて削除できます。
ただし、元データは削除されないため、再び数式を入力すれば同じ転置表を作成できます。
スピルエラーの原因と対処
数式を入力しても結果が表示されず、スピルを示すエラーになることがあります。
よくある原因は、転置先に見えない空白文字、結合セル、別の値、または別の数式が残っているケースです。
スピルエラーが出た場合は、エラーが表示されたセルをクリックします。
表示される警告アイコンから妨げになっているセルを確認し、不要な内容を削除します。
表の中に結合セルがある場合も自動展開を妨げるため、結合を解除するか、別の空白領域へ数式を移動します。
転置先をテーブル機能で作成している場合は、テーブルの外側にTRANSPOSE関数を置く方法が確実です。
【操作のポイント】スピルエラーは数式の誤りではなく、出力先に障害物があることを示す場合が多いため、周囲のセルを確認しましょう。
旧バージョンExcelでの配列数式入力
続いては、動的配列に対応していないExcelで行列を入れ替える入力方法を確認していきます。
| 元データの大きさ | 選択する転置先 | 確定方法 |
|---|---|---|
| 3行4列 | 4行3列 | Ctrl＋Shift＋Enter |
| 2行5列 | 5行2列 | Ctrl＋Shift＋Enter |
配列数式用の範囲選択
Excel 2019以前などの環境では、TRANSPOSE関数を入力する前に転置後のセル範囲を選択します。
元データがA1からD3の3行4列なら、転置先では4行3列の範囲を選択します。
たとえばF1からH4をドラッグして選択する考え方です。
先に選ぶ範囲が小さすぎると一部の値が表示されず、大きすぎると余分なエラー表示が出ることがあります。
元の行数と列数を数えてから、反対の形になるように選択しましょう。
CtrlとShiftとEnterによる確定
転置先の範囲を選択した状態で、数式バーへ次の数式を入力します。
=TRANSPOSE(A1:D3)
数式を入力したらEnterキーだけではなく、CtrlキーとShiftキーを押しながらEnterキーを押します。
この操作により、選択した範囲全体へ配列数式としてTRANSPOSE関数が設定されます。
正しく確定されると、数式バーでは数式の外側に波括弧が表示されることがあります。
数式そのものに波括弧を手入力するのではなく、必ずキー操作で確定する点が重要です。
配列数式の修正と削除
旧バージョンの配列数式は、結果の一部分だけを変更したり削除したりできません。
修正するときは、最初に設定した転置先の範囲全体を選択します。
その後、数式バーを編集してから、再びCtrlキーとShiftキーとEnterキーで確定します。
一部のセルだけを選んでDeleteキーを押すと、配列の一部を変更できないという警告が表示されます。
削除する場合も、転置された表の範囲全体を選択してからDeleteキーを押す流れです。
【操作のポイント】古いExcelでは、転置後の必要範囲を先に選択し、Ctrl＋Shift＋Enterで確定する操作を忘れないようにしましょう。
TRANSPOSE関数と貼り付け転置の使い分け
続いては、関数による自動変換と貼り付け転置の違いについて確認していきます。
| 方法 | 元データ更新 | 向いている用途 |
|---|---|---|
| TRANSPOSE関数 | 自動反映 | 継続して参照する表 |
| 形式を選択して貼り付け | 反映しない | 一度だけ形を変える表 |
関数で入れ替える場合のメリット
TRANSPOSE関数は、元データが頻繁に更新される表に向いています。
月ごとの売上実績や、日々追加される作業記録などでは、転置表を作り直す手間を減らせます。
参照先の表をレポートやグラフの元データにしている場合にも、自動反映の効果は大きくなります。
入力用の表を縦長に保ち、提出用の表だけを横長に変えるような使い方も可能です。
ただし、転置先のセルを個別に編集できないため、完成表を自由に加工したい場合には注意が必要です。
貼り付け転置を使う場面
値を一度だけ行列入れ替えしたい場合は、コピー後に形式を選択して貼り付けを使う方法が便利です。
元データをコピーし、貼り付け先で右クリックして転置のアイコンを選択すると、行と列を交換した値を貼り付けられます。
貼り付けた後の表は元データと連動しないため、自由にセルを編集できます。
元データの変更を追いかけたい場合はTRANSPOSE関数です。
別ファイルへ提出する完成表を作る場合は、貼り付け転置が使いやすいでしょう。
更新の必要性があるかどうかを基準に選ぶと、方法を判断しやすくなります。
値のみを残す変換方法
TRANSPOSE関数で作った表を、あとから値だけの独立した表へ変換することもできます。
転置結果の範囲をコピーし、同じ場所または別の場所へ値として貼り付けます。
値として貼り付けた後は数式の参照がなくなり、元データを変更しても結果は変わりません。
データを確定させて保存したいときや、他の人へ配布するブックで数式を残したくないときに便利です。
【操作のポイント】自動更新が必要な期間はTRANSPOSE関数を残し、確定後だけ値貼り付けへ切り替えると管理しやすくなります。
行列入れ替え時のエラーとデータ管理
続いては、行列を入れ替える際に起こりやすいエラーと、データを扱うときの注意点を確認していきます。
| 現象 | 主な原因 | 対処 |
|---|---|---|
| スピルエラー | 出力先に値がある | 範囲を空白にする |
| 一部だけ編集不可 | 配列数式の結果 | 元データか先頭数式を編集 |
空白セルと空文字の見え方
元データに空白セルがあると、転置後にも対応する位置へ空白が表示されます。
数式で空文字を返しているセルも、見た目は空欄のように見えることがあります。
ただし、空白セルと空文字は集計関数などで扱いが異なる場合があります。
転置後の表でCOUNTIFやFILTERなどを使う予定があるなら、元データの空欄ルールを統一しておくことが重要です。
不要なスペースが入っていないかも確認すると、検索や照合の不一致を防げます。
結合セルを含む表の注意点
結合セルを含む見出し表は、TRANSPOSE関数の参照元としては扱えても、見た目が期待どおりにならないことがあります。
結合という書式設定そのものは、関数で転置先へコピーされません。
転置結果には値だけが表示されるため、必要であれば転置後に罫線や配置を整えます。
集計や並べ替えを行うデータでは、結合セルを使わず、各セルへ値を入れる構造が適しています。
行列を入れ替える前に、見出しを含む範囲に結合セルがないか確認します。
結合セルがある場合は、可能であれば結合を解除してから作業しましょう。
テーブル範囲と参照範囲の拡張
TRANSPOSE関数でA1からC10のように固定範囲を指定すると、11行目以降へ追加したデータは自動で対象になりません。
将来の追加を見込む場合は、元データをテーブル化したり、参照範囲を見直したりする必要があります。
テーブル化した一覧を参照する場合は、列名を含む構造化参照を使う方法もあります。
ただし、転置先の広がり方が変わるため、周囲のセルに十分な空きスペースを用意しましょう。
データ量が増える表ほど、元データと転置表の配置を離して管理することが有効です。
【操作のポイント】転置は値の並びを変える機能なので、書式や結合状態まで同じ形で移す用途には貼り付け転置も検討しましょう。
まとめ エクセルの行列を関数で入れ替える方法
エクセルで行列を自動変換したいときは、TRANSPOSE関数を使うことで、元データと連動した転置表を作成できます。
Excel 365やExcel 2021では、転置先の先頭セルに=TRANSPOSE(元データ範囲)を入力してEnterキーを押すだけで完了します。
旧バージョンでは、転置後に必要な範囲を先に選択してから、CtrlキーとShiftキーとEnterキーで確定する流れです。
元の表が更新された際に転置先も変わるため、定期的に内容が変わる一覧表には特に便利です。
一度だけ形を変えて編集したい場合は、形式を選択して貼り付けから転置を選ぶ方法が適しています。
スピルエラーが出た場合は、転置先の範囲に入力済みの値、結合セル、テーブル範囲がないかを確認しましょう。
行と列を入れ替える目的に応じて関数と貼り付けを使い分け、見やすく更新しやすいExcel表を作っていきましょう。
