タテヨコ変換の基本形②配列数式を利用した自動的入替

エクセルで「タテヨコ変換」をするマニアックな方法として、「配列数式」などを使用する方法を紹介します。それが「TRANSPOSE関数」を使ったテクニックです。いったいどうやればよいのでしょうか。

エクタツは、「エクセル評論家」でもある新宿会計士が、エクセルなどの「オフィス系の汎用ソフト」を使い、誰でも気軽に、かつ安価に、とても迅速かつ正確に仕事をこなす、ビジネスマンとしての基本テクニックを研究するサイトである。

先週の『タテヨコ変換の基本形①「行列入れ替え+値貼り付け」』では、実務上極めて頻繁に出てくる「タテヨコ変換」の基本形のうち「行列を入れ替えてえの値貼り付け」を紹介した。ところが、これだといちいち手作業で変換する必要があり、実用的ではない。そこで本稿ではTRANSPOSE関数を紹介する。

値貼り付けの問題点

実務上、エクセルの「タテヨコ」を変換したいと思うことがしばしば発生する。

先週の『タテヨコ変換の基本形①「行列入れ替え+値貼り付け」』では、「値貼り付け」+「行列入れ替え」という機能を使用した、「タテヨコ変換」のいちばん基本的な技を紹介した。ただ、これだといちいち手作業で「コピー」、「形式を選択して貼り付け」などの機能を使わなければならない。

この点、「キーボードなどを使ったエクセルの操作が得意だ」という方ならば、都度このような作業をすることしても、問題はないのかもしれない。

しかし、実務上は、そう単純ではない。

とくにWindows7以降は、エクセルやワードに「リボン」という大変使い辛いシステムが導入され、「Alt」キーを使用したショートカットの多くが廃止され、使えなくなってしまった。ということは、エクセル等の操作の多くは、いちいちマウスを使わなければならないのである。

そして、WindowsXP時代を知っている者からすれば、現在のエクセルはマウスを使った操作が大変に面倒くさくなってしまっており、その分、ミスをしやすくなってしまっている。『タテヨコ変換の基本形①「行列入れ替え+値貼り付け」』のような作業がルーティンで発生することは、できれば避けたい。

TRANSPOSE関数は便利だ

そこで、本稿で紹介したいのがTRANSPOSE関数だ。

書式はとても簡単で、次の計算式を入力してやれば良い。

=TRANSPOSE(範囲)

ただし、このTRANSPOSE関数、若干エキセントリックなので、注意が必要だ。

まず、これは「行列式」だ。このため、キーボード上での入力がポイントである。

ここで、こんな事例を使ってみよう(図表1)。

図表1 サンプルシート

これは、当ウェブサイトでいうところの、いわゆる「ヨコ展開」型のシートだが、これを「タテ展開」型に変換するのがTRANSPOSE関数だ。

サンプルシートに収録されている表は、タテ2行×ヨコ4列のシートで、セル数は全部で8個ある。

そこで、「タテ4行×ヨコ2列」の範囲を選択し、その範囲内で次の計算式を入力してみよう。

=TRANSPOSE(A1:D2)

これが、図表2の状態だ。

図表2 計算式

そして、この計算式を、「Shift」を押しながら「Enter」で入力してみよう(図表3)。

図表3 Shift+Enter

押す「Shift」「Ctrl」キーは、左右のどちらでも構わない。

すると、どうなるか。

その結果が、図表4だ。

図表4 タテヨコ入替が完成する

すなわち、この計算式は、「A1~D4」にある「2行×4列」を、「A4~B7」までの「4行2列」にタテヨコ転換するためのものである。

注意点:配列の変更は「できない」

ただし、この計算式には注意点がいくつかある。

まず、その最たるものは、いったん配列として入力した場合、一部分だけを編集・変更したりできない点だ。配列の範囲内の任意のセルを選び、ためしに何か値を入力して欲しい。すると、こんなエラーメッセージが出てくる。

配列の一部を変更することはできません。

図表5 エラーメッセージ

つまり、いったん配列を作ってしまうと、そこに新たに行や列を挿入することはできない。

もし配列の参照範囲(この場合はA1:D2)の行列数を変更する場合は、TRANPSOSE関数も入力し直し、である。

このあたりは、注意が必要な点だろう。

※ ※ ※ ※ ※ ※ ※

次回以降では、デイリーで変わるデータをタテヨコ変換するするテクニックについても紹介していきたい。

エクセルで「タテヨコ変換」をする一番簡単

エクタツは、「エクセル評論家」でもある新宿会計士が、エクセルなどの「オフィス系の汎用ソフト」を使い、誰でも気軽に、かつ安価に、とても迅速かつ正確に仕事をこなす、ビジネスマンとしての基本テクニックを研究するサイトである。

読者コメント一覧

  1. ななっしー より:

    >「配列の一部を変更することはできません。」

    このほうがむしろ自分や他人がうっかりセルの内容を書き変えてしまう事故も減る、ということで^^
    そのセルだけ気にしていればいいので可読性/保守性の面からも配列数式はオススメ。

    >もし配列の参照範囲(この場合はA1:D2)の行列数を変更する場合は、TRANPSOSE関数も入力し直し、である。

    付け加えますとEXCEL2021あるいはMS365版の場合は…
    ・Ctrl+Shift+Enter で入力すると {中カッコ} でくくられて(静的)配列数式になるので出力先は固定。
    ・フツーに入力すると(動的)配列数式の出力はスピルする(あふれる)ので参照範囲も行列挿入削除が可能。

※【重要】ご注意:他サイトの文章の転載は可能な限りお控えください。

やむを得ず他サイトの文章を引用する場合、引用率(引用する文字数の元サイトの文字数に対する比率)は10%以下にしてください。著作権侵害コメントにつきましては、発見次第、削除します。

※現在、ロシア語、中国語、韓国語などによる、ウィルスサイト・ポルノサイトなどへの誘導目的のスパムコメントが激増しており、その関係で、通常の読者コメントも誤って「スパム」に判定される事例が増えています。そのようなコメントは後刻、極力手作業で修正しています。コメントを入力後、反映されない場合でも、少し待ち頂けると幸いです。

※【重要】ご注意:人格攻撃等に関するコメントは禁止です。

当ウェブサイトのポリシーのページなどに再三示していますが、基本的に第三者の人格等を攻撃するようなコメントについては書き込まないでください。今後は警告なしに削除します。なお、コメントにつきましては、これらの注意点を踏まえたうえで、ご自由になさってください。また、コメントにあたって、メールアドレス、URLの入力は必要ありません(メールアドレスは開示されません)。ブログ、ツイッターアカウントなどをお持ちの方は、該当するURLを記載するなど、宣伝にもご活用ください。なお、原則として頂いたコメントには個別に返信いたしませんが、必ず目を通しておりますし、本文で取り上げることもございます。是非、お気軽なコメントを賜りますと幸いです。

コメントを残す

メールアドレスが公開されることはありません。