Excelで作成した表を見ていると、項目を横方向に並べたほうが見やすい場合や、月別データを縦方向に配置し直したい場合があります。
表を手作業でコピーし直すこともできますが、数式や書式が多い表では入力ミスが起こりやすく、元データとの整合性も崩れやすくなります。
そこで役立つのが、Excelの転置機能です。
転置とは、表の行と列を入れ替え、縦軸と横軸を交換する操作を指します。
縦軸と横軸を入れ替える主な方法は、コピーして形式を選択して貼り付ける方法、TRANSPOSE関数を使う方法、Power Queryで整形する方法です。
元の表を残して別の位置に表を作るのか、元データの変更を反映させたいのかによって、選ぶ方法は変わります。
この記事では、エクセルの表で縦軸と横軸を入れ替える具体的な手順、数式を使った連動方法、失敗しやすいポイントまで確認していきましょう。
エクセルの表をコピーして縦軸と横軸を入れ替える方法
それではまず、コピーと形式を選択して貼り付けを使い、表の縦軸と横軸を入れ替える方法について解説していきます。
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 商品 | 4月 | 5月 | 6月 |
| 2 | りんご | 120 | 135 | 128 |
| 3 | みかん | 98 | 110 | 105 |
この例では、1行目にヘッダーがあり、A列には商品名、B列からD列には月別の売上数が入っているものとします。
コピーして転置する方法は、元表との連動が不要で、完成済みの表を一度だけ見やすく組み替えたいときに向いています。
コピー範囲の選択
まず、入れ替えたい表全体を選択します。
見出しだけを除外すると、転置後の表で何のデータなのか判断しにくくなるため、基本的には1行目のヘッダーと1列目の見出しを含めて選択することが大切です。
表内のセルを1つ選択してから、CtrlキーとAキーを押す方法もありますが、周辺に別のデータがある場合は範囲を目視で確認しましょう。
数式が含まれている表でも、値だけではなく数式や表示形式をまとめてコピーできます。
範囲を選択したら、CtrlキーとCキーを押すか、ホームタブのコピーをクリックします。
選択範囲の周囲に点線が動く状態になれば、コピーの準備は完了です。
形式を選択して貼り付けの転置
続いては、貼り付け先を指定して転置を実行する画面を確認していきます。
貼り付け先には、元の表と重ならない空白セルを選びます。
たとえば元表がA1からD3にある場合は、A6やF1など、転置後の表が十分に広がる場所を選ぶと安心です。
貼り付け先の先頭セルを右クリックし、表示されたメニューから形式を選択して貼り付けを開きます。
貼り付けオプションの中にある、縦横のセルが入れ替わった矢印のアイコンを選ぶと転置できます。
ダイアログを使う場合は、形式を選択して貼り付けの画面で転置にチェックを入れ、OKをクリックしましょう。
転置の貼り付けは、選択した貼り付け先セルを左上として、新しい表を展開します。
転置後の表と元表の関係
コピーして貼り付けた転置表は、通常の貼り付けでは元の表と連動しません。
たとえば元表のB2にある120を150へ書き換えても、転置後に表示された120は自動では変わらない仕組みです。
これは固定した報告資料を作るときには便利ですが、毎月更新する集計表には注意が必要かもしれません。
更新が多い表では、後で紹介するTRANSPOSE関数を使うと、元表の修正を反映できます。
【操作のポイント】貼り付け先には、転置後の行数と列数を考えて十分な空白範囲を確保します。元表が3行4列なら、転置後は4行3列になります。
貼り付けオプションから転置を素早く実行する操作
続いては、右クリックメニューやリボンの貼り付けオプションを使い、転置を素早く実行する操作を確認していきます。
| A | B | C | |
|---|---|---|---|
| 1 | 部署 | 営業部 | 経理部 |
| 2 | 人数 | 12 | 6 |
| 3 | 予算 | 500 | 300 |
少ない操作で表を組み替えたい場合は、転置専用の貼り付けアイコンを使うと便利です。
コピー後に貼り付け先を右クリックし、貼り付けオプションの転置アイコンを選べば、ダイアログを開かずに縦横を交換できます。
右クリックメニューの転置アイコン
コピーした後に貼り付け先セルを右クリックすると、メニュー上部に複数の貼り付けアイコンが表示されます。
その中から、セルの向きが縦横に切り替わる形のアイコンを選択します。
アイコンにマウスポインターを重ねると、貼り付け先に転置後のプレビューが表示されるExcelのバージョンもあります。
プレビューで表が周囲のデータに重ならないことを確認してから確定すると、意図しない上書きを防げます。
右クリックメニューは操作が速い反面、アイコンの意味を見誤りやすいため、初めて使うときはツールチップも確認するとよいでしょう。
ホームタブの貼り付けメニュー
マウス操作をリボンに統一したい場合は、ホームタブの貼り付けボタン下にある下向き矢印をクリックします。
メニューの貼り付けオプションから転置を選択すれば、右クリックした場合と同じ結果になります。
作業マニュアルで操作を案内するときは、ホーム、貼り付け、転置という流れで伝えると、利用者が探しやすくなります。
Excelの画面幅が狭いと、貼り付けの名称ではなくアイコンだけが表示されることがあります。
その場合も、貼り付けボタンのメニュー内に転置機能があります。
ショートカット操作の注意点
転置だけを一発で実行する標準ショートカットは環境によって覚えにくいため、まずはコピー後に右クリックする手順が実務的です。
キーボード操作では、Altキーを使ってリボンのキー操作を順に実行することもできます。
ただし、Excelのバージョンや表示言語によって割り当てが異なることがあるため、固定のキー列だけに頼る必要はありません。
コピー範囲が点線で囲まれている間に貼り付けを実行すればよく、操作後はEscキーでコピー状態を解除できます。
【操作のポイント】右クリックの転置は素早い方法ですが、貼り付け先に既存データがないかを先に確認します。
TRANSPOSE関数で縦軸と横軸を連動させる方法
続いては、元表の変更を転置後の表にも反映したい場合に使う、TRANSPOSE関数を確認していきます。
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 担当者 | 1月 | 2月 | 3月 |
| 2 | 田中 | 80 | 92 | 88 |
| 3 | 佐藤 | 75 | 86 | 90 |
TRANSPOSE関数は、指定したセル範囲の行と列を入れ替えて表示する関数です。
転置後の表を元表に連動させたい場合は、コピー貼り付けではなくTRANSPOSE関数が適しています。
TRANSPOSE関数の基本形
=TRANSPOSE(配列)
配列には、転置したい元表の範囲を指定します。今回の例ではA1:D3です。
数式の入力位置
まず、転置後の表を表示したい左上のセルを選びます。
元表がA1:D3の3行4列なら、転置後は4行3列になります。
たとえばF1を選択して数式を入力すると、F1からH4の範囲に結果が自動で広がります。
Microsoft 365やExcel 2021以降では、動的配列機能により、先頭セルへ数式を入れるだけで結果が展開されます。
入力する数式
=TRANSPOSE(A1:D3)
数式を入力してEnterキーを押すと、元表の1行目が転置表の1列目として表示されます。
このとき、転置結果が広がる予定のセルに値が入っていると、スピルエラーになる場合があります。
TRANSPOSE関数の操作画面
ここでは、F1セルを起点としてTRANSPOSE関数を入力するイメージを確認していきます。
赤枠で示した数式バーへ数式を入力し、Enterキーを押します。
結果が複数セルへ自動で表示されるため、スピルした範囲の途中に手入力を加えないことが重要です。
旧バージョンでの配列数式
Excel 2019以前など、動的配列に対応していない環境では、転置後に必要な範囲を先に選択します。
元表が3行4列なら、貼り付け先では4行3列の範囲を選択します。
範囲を選んだ状態で=TRANSPOSE(A1:D3)と入力し、Ctrlキー、Shiftキー、Enterキーを同時に押します。
成功すると、数式バーには波かっこで囲まれた配列数式として扱われます。
一部のセルだけを削除したり書き換えたりするとエラーになるため、修正するときは結果範囲全体を選択しましょう。
【操作のポイント】TRANSPOSE関数は元表と連動します。更新用の集計表には便利ですが、転置結果のセルを個別編集する用途には向きません。
数式と書式を転置するときの注意点
続いては、数式、セル参照、書式を含む表を入れ替えるときの注意点を確認していきます。
| A | B | C | |
|---|---|---|---|
| 1 | 月 | 売上 | 前年差 |
| 2 | 4月 | 120000 | =B2-100000 |
| 3 | 5月 | 135000 | =B3-110000 |
表の見た目だけでなく数式や書式も扱う場合は、貼り付けの種類を意識する必要があります。
コピーして転置する場合、通常の転置貼り付けでは値、数式、書式をまとめて移せます。値だけを固定したい場合は、値の貼り付けと転置を組み合わせます。
相対参照と絶対参照の変化
数式を含む表をコピーして転置すると、数式内のセル参照は貼り付け位置に応じて変化します。
これはExcelの相対参照の仕組みによるものです。
たとえばB2を参照する数式は、移動先との位置関係を保つように調整されます。
常に同じ基準セルを参照したいときは、$A$1のような絶対参照を使います。
行だけ固定したい場合はA$1、列だけ固定したい場合は$A1という混合参照も利用できます。
転置前に参照方法を見直しておくと、転置後に計算結果がずれる問題を減らせます。
値のみを貼り付ける方法
計算式は不要で、表示されている数値だけを縦横交換したい場合もあります。
この場合は、コピー後に形式を選択して貼り付けを開き、値を選んだうえで転置を指定します。
値として貼り付けたセルには数式が残らないため、元データを変更しても結果は変わりません。
報告書の確定値を別シートへ配置する場合は、値の転置が扱いやすい方法です。
ただし、日付が連続番号で表示されたり、通貨記号が消えたりした場合は、貼り付け後に表示形式を調整しましょう。
結合セルとテーブル機能の制限
結合セルを含む範囲は、転置時にエラーになったり、期待した形で貼り付けられなかったりすることがあります。
結合セルは表の並べ替えやフィルターにも影響しやすいため、可能であれば結合を解除してから操作するのがおすすめです。
Excelのテーブルとして設定された範囲では、構造化参照や集計行が含まれる場合があります。
テーブル全体を転置する前に通常の範囲へコピーするか、転置後にテーブルとして設定し直すと管理しやすくなります。
【操作のポイント】数式入りの表は、転置後の計算結果を必ず確認します。特に相対参照、絶対参照、日付表示、結合セルには注意が必要です。
グラフとピボットテーブルの縦軸と横軸を切り替える操作
続いては、通常のセル範囲ではなく、グラフやピボットテーブルで縦軸と横軸の見せ方を切り替える操作を確認していきます。
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 商品 | 4月 | 5月 | 6月 |
| 2 | A商品 | 120 | 135 | 128 |
| 3 | B商品 | 98 | 110 | 105 |
グラフでは、表そのものを転置しなくても、系列と分類軸を切り替えるだけで比較対象を変更できます。
セル範囲の転置は表の構造を変える操作です。グラフの行列の切り替えは、グラフ上の系列と横軸ラベルの見え方を変える操作です。
グラフの行列切り替え
グラフをクリックすると、グラフのデザインタブが表示されます。
データグループにある行列の切り替えを選ぶと、横軸に表示する項目と系列の関係を入れ替えられます。
たとえば月を横軸にして商品別の棒を比較していたグラフを、商品を横軸にして月別の棒を比較する形へ変更できます。
グラフの行列切り替えは、元の表のセル配置を変更しないため、資料の見せ方だけを調整したいときに便利です。
横棒グラフと縦棒グラフの違い
グラフの縦軸と横軸を入れ替えたいという場合、表の転置ではなく、縦棒グラフを横棒グラフへ変更する意味で使われることもあります。
縦棒グラフでは項目が横軸、数値が縦軸に配置されます。
横棒グラフでは項目が縦軸、数値が横軸に配置されるため、長い商品名や部署名を読みやすくできます。
グラフを選択し、グラフのデザインからグラフの種類の変更を選ぶと、棒グラフの向きを変更できます。
ピボットテーブルの行と列の移動
ピボットテーブルでは、フィールド一覧の行エリアと列エリアを入れ替えることで、集計表の縦軸と横軸を変更できます。
たとえば行に商品、列に月を配置している場合、商品フィールドを列へ、月フィールドを行へドラッグします。
この操作は元のデータベースを変更せず、集計の視点だけを切り替える方法です。
大量のデータを複数の切り口で比較したいときは、表の転置よりピボットテーブルのフィールド移動が効率的です。
【操作のポイント】グラフは行列の切り替え、ピボットテーブルは行エリアと列エリアの移動を使います。通常の表の転置とは目的が異なります。
まとめ エクセルの表で横軸と縦軸を入れ替える方法
エクセルの表で縦軸と横軸を入れ替えるには、コピーして転置貼り付けを行う方法がもっとも手軽です。
完成済みの表を別の向きで使いたい場合は、コピー後に形式を選択して貼り付けから転置を選びましょう。
元表の修正を転置後の表へ反映させたい場合は、TRANSPOSE関数を使うことが重要です。
サンプルデータがA1:D3なら、転置後の表は4行3列になります。
TRANSPOSE関数では、先頭セルに=TRANSPOSE(A1:D3)を入力します。
古いExcelでは転置先の範囲を選択し、Ctrlキー、Shiftキー、Enterキーで配列数式として確定する必要があります。
数式を含む表では、相対参照と絶対参照の違い、値だけを貼り付ける必要性、結合セルの影響も確認しておくと安心です。
また、グラフの見え方を変えたいだけなら行列の切り替え、ピボットテーブルの集計方向を変えたいならフィールドの移動が適しています。
表の構造を変えるなら転置貼り付け、更新に連動させるならTRANSPOSE関数、分析の見せ方を変えるならグラフやピボットテーブルの設定を選びましょう。
目的に合う方法を選べば、データを作り直さなくても読みやすいExcel表へ整えられます。
