仕事で頼まれたデータ更新の作業で、驚くべき非効率な方法で作成されていました。
離れた場所にあるエクセルのデータを行と列を入れ替えて表示している表でした。このデータは定期的にデータの追加がされますが、「＝」を使って一つずつ参照する方法で管理されています。以下の例はデータが少しですが、実際は大量なデータでした。
この方法では、式をコピーできず、毎回個別に参照を設定しなければならないため、非常に不便です。
式表示
もっと簡単な方法はいくつかあります。以下のうち、上記２つの説明をします。
- TRANSPOSE関数
- 関数組み合わせ
- Power Query
- VBA
行列入れ替えるデータの例
同じシート上での行列（縦横）を入れ替える例です。
混乱しないように少しのデータにしました。
TRANSPOSE関数を使って縦横入れ替え
Excelでデータの行と列を入れ替えるために使用されます。縦に並んでいるデータを横に並べたり、その逆を行ったりすることができます。
空白のセルに関数を設定します。
この例では、セルF1に「TRANSPOSE(」と入力後、セルA1からD4を選択し、Enterキーをおします。
=TRANSPOSE(A1:D4)
設定後
- 元のデータの範囲と同じ大きさの範囲に結果が表示されます。選択した範囲が元のデータのサイズに合っていないと、エラーが表示されることがあります。
- 元のデータを変更した場合、範囲も自動的に更新されます。
縦横を入れ替えるためのINDEX関数の使用
３つの関数を使う方法です。
- INDEX関数
- COLUMNS関数
- ROWS関数
INDEX関数で指定した範囲から特定の行と列にある値を返します。今回の例では、行と列の指定を式のコピーで対応できるようにします。
設定手順
はじめに回答から。セルF1に以下の式を設定します。
=INDEX($A$1:$D$4, COLUMNS($A$1:A1), ROWS($A$1:A1))
式を行列にコピーします。
結果
INDEX関数
指定した範囲から特定の行と列にある値を返します。この関数は、データの中から特定の値を取り出すのに便利です。
INDEX(配列, 行番号, [列番号])
- 配列：値を取得したい範囲や配列
- 行番号：取り出したい値がある行番号
- 列番号：（省略可能）取り出したい値がある列番号。
COLUMNS関数
COLUMNS(配列)
指定した範囲内の列数を返します。
ROWS関数
ROWS(配列)
指定した範囲内の行数を返します。
COLUMNS関数とROWS関数
COLUMNS関数とROWS関数関数で行列表示した結果です。
これにINDEX関数を加えると、上記の行列の位置の値を表示します。
参考・Power Query（パワークエリ）
パワークエリの方法は、定期的にデータの追加がある場合には向かないです。
（1）データ範囲をアクティブにします。例えばセルA1を選択した状態で、[データ]＞[テーブまたは範囲から]を選択します。
→範囲を自動で選択するので、テーブルの作成で「OK」をクリックします。
→PowerQueryが開きます。
（2）[変換]タブ＞[行列反転]をクリックします。
→ 行列入れ替わります。
（3）[変換]タブ＞[1行目をヘッダーとして使用]をクリックします。
（4）[ファイル]＞[閉じて読み込む]をクリックします。
完成
自動でスタイルの設定がされます。
