スポンサーリンク
はじめに
※今回の記事で扱うExcelファイルは、こちらに保存してあります。
下図の表は、売上・原価・粗利という数値項目のタイトルが上3行にゴチャゴチャ書いてある。
部門名も4行目とか10行目とかに繰り返し横に書かれているが、こういうのは縦に1回だけまとめて書くべきところだ。
まあとにかく、色々とよろしくない表なので、たとえば下図のように、縦長のデータベース形式にしたいところだ。
こういう風に、表の縦横を組み替えたりするのは、PowerQueryでやるのが最近の流行りみたいなものではある。
しかしPowerQueryは、イマイチな感じの表を加工する機能も充実してはいるのだが、それでも元のデータがある程度は規則的に整ってくれているのが望ましい。
元データの図を再掲するが、たとえば
- B11:B13セルなんかは数値が入るべきところなのに「不明」などと文字列を入れてあるし、F15:G18セルは空欄になっている。
- 1月と2月の間は2行空いているが、2月と3月の間は1行しか空いていなくて、不規則。
- 横軸の部門など、どう増えていったり、途中に空白が挟まれたりするか今後不明。
など、色々と滅茶苦茶で規則性がない。
こうなると、この表が今後どうなっていくか予想しきれず、もうPowerQueryで整形するのにはあまり適さない。
行列を入れ替えて貼り付け
ちなみに、単に元データの値だけを縦横入れ替えて貼り付けて終わりで良いなら、
セル範囲をコピー(Ctrl + C)
→形式を選択して貼り付け(Ctrl + Alt + V)
で出てくるダイアログで、「行/列の入れ替え」にチェックを入れれば良い。
他には、主に左上の「すべて」「数式」「値」のどれかにチェックを入れることになるだろう。
今回の本題はこれではないので、あまり詳しくは触れないけど。
TRANSPOSE関数を入れる
ただ、この「形式を選択して貼り付け」は基本的には、ただ貼り付けるだけなので、元データが変化した時に貼り付け後のデータも自動的に変化させるようにするのにはあまり適さない。
そこでこういう場合、セル範囲の縦横を入れ替えて参照する関数としてTRANSPOSE関数がある。
そう多用すべき関数でもないが、これが今回の記事の本題だ。
では今回の「元データ」シートの数値データを、縦横を入れ替えて「加工」シートに転記してみたい。
まずは「元データ」シートのB5:H7セルの数値からいく。
まず「加工」シートのC2セルに、「=TR」くらいまで打てば入力予測が表示されるはずなので、それを元に
=TRANSPOSE(
と打とう。
次に「元データ」シートを選択してB5:H7セルをドラッグして、TRANSPOSE関数の中に入れる。
F4キーを押して、セル「B5:H7」の参照方法を絶対参照「$B$5:$H$7」に変更しておこう。
そもそも絶対参照とは何か分からないのでは、TRANSPOSE関数とか以前にスタート地点に立てないので、必ず絶対参照・相対参照はマスターしよう。
そして、数式
=TRANSPOSE(元データ!$B$5:$H$7)
を入れた結果として、「元データ」シートの数値データを縦横入れ替えたものができあがる。
TRANSPOSE関数とスピル、配列数式
さて今回、TRANSPOSE関数を入れたのは「加工」シートのC2セル1個だけなのだが、その結果が自動的にE8セルまで埋められている。
たとえば「加工」シートで、直接に数式が入れられていないC4セルを見ると、数式の文字がグレーになっている。
同じように、1個のセルに数式を入れただけでも他のシートまで結果が埋められてくれる機能としては、以前に扱ったスピルがある。
TRANSPOSE関数はスピルの登場以前からずっと存在していた関数だが、スピルもTRANSPOSE関数も、本質としては共通してExcelの配列数式というものになる。
配列数式というのは、複数セル範囲をいっぺんに1つの数式でまとめて扱うというものだ。
それを圧倒的にとっつきやすくしたのがスピルで、配列数式そのものをまともに学習するのはよほど熟練してからで良い。
配列数式などというものは、スピルとTRANSPOSE関数だけ覚えておけば、ひとまず大丈夫だ。
ついでに、「加工」シートのC9セル、C16セルにもTRANSPOSE関数を入れて完成させたものが、下図のようになる。
今回サンプルとして保存したExcelファイルには、更にOFFSET関数とかまで使った例を示している。
今回のような場合は、元データの表がいつ、どの行・列が削除されたりしてズレるか分からないので、そういう場合はOFFSET関数を使って備えるのが一つの手法だからだ。
それについて今回は詳説はしないが、参考にならばと思います。
スポンサーリンク
