仕事がススむ関数(10)同一データを、縦にも横にも分析できる TRANSPOSE関数を使ってみましょう

仕事がススむ関数(10)同一データを、縦にも横にも分析できる TRANSPOSE関数を使ってみましょう

仕事がススむ関数(10)

同一データを、縦にも横にも分析できる

TRANSPOSE関数を使ってみましょう

ニュースで「物価高が進んでいる」と聞かされて、日々の暮らしに不安を感じる人もいるのではないでしょうか。

だって、収入はなかなか上がりづらいですもんね。

不安の多くは、対象ニュースなどに関する情報が不足していて、よく分からないことに起因します。不安を和らげるためには、自分で調べたり、分析したりすることが一番です。

できるだけお金や時間をかけず、手もちのネットやExcelをうまく活用して、自分なりの課題解決方法を見つけてみましょう。

今回は、Excelデータを分析に活用するため、1つの大きな表を、縦にも横にもながめられるTRANSPOSE関数をご紹介します。

物価の動向は、政府統計の総合窓口( e-Stat )で無料で調べられます

日本の物価動向を調査するには、まず、政府統計の総合窓口( e-Stat )へアクセスしてみましょう。代表的な調査資料として、消費者物価指数が毎月更新されています。

ためしに、Excel閲覧用としてアップされている「2020年基準消費者物価指数(月報)※」2022年5月分をダウンロードしてみましょう。

※2020年の平均を100として、調査対象月(年)の指数がどれだけ上下しているかを比較した指標のことです。

仕事がススむ関数(10)同一データを、縦にも横にも分析できる TRANSPOSE関数を使ってみましょう

統計表のデータ表記方法・統一ルール(セル結合しない、非表示セルをつくらないetc.)が守られていない、汚れたデータではあるものの、ずいぶん昔から同じ書式で作られてきた資料のようですから、割り切って分析用に整形します。

“Excel閲覧用”ボタンからリンクされ、ダウンロードできる”am01-1.xlsx”というExcelブックの、一番左(トップ)のシート”am01-1”は、635行84列にもおよぶ、膨大な指数データの一覧表でした。

大まかに表の構造を図示すると、下図のようになります。

仕事がススむ関数(10)同一データを、縦にも横にも分析できる TRANSPOSE関数を使ってみましょう

今回は、直近の消費者物価指数についてだけを調べたいので、セルに色をつけた2行79列のみを抽出して、その数字をながめてみることにします。

それにしても、横になが〜い表ですよね。Excelには、列を並び替える機能もありますから、そのまま横目で指数表をながめていってもよいのですが...。

視覚的に、あるいはランクづけのため、行と列を入れ替えた方が、わかりやすい資料となります。そこで、TRANSPOSE関数の登場です。

TRANSPOSE関数では、配列を使います

英単語で「入れ替える」という意味のTRANSPOSE関数は、文字通り(行と列などを)入れ替える時に使います。

構文
TRANSPOSE(配列)

構文自体はシンプルなのですが、引数のカッコ内に含まれる「配列」に注意する必要があります※※。

※※Office365、およびExcel2021で使用する場合には、スピル(動的配列数式)という便利な機能が使えますから、「配列」を意識しないでも、自動的に行列を入れ替えた表が複製されます。

配列(配列数式)とは、同種データの範囲を、ひとまとまりとして扱う計算方法のことをさします。今回の消費者物価指数の事例では、横80列以上の品目名などを、「ひとくくりの配列」情報として、一括して縦に置き換える操作となります。

関数式の前後に、波型カッコ{ }がついたセルを見たことはありませんか?

仕事がススむ関数(10)同一データを、縦にも横にも分析できる TRANSPOSE関数を使ってみましょう

これを配列と知らずに、後から編集しようとすると、「配列の一部を変更することはできません。」という注意メッセージが出たりすることがあります。

また、配列は、関数や数式を最初に入力する時に定義する必要があるため、一度配列抜きで入力した計算式へ、後から前後に波型カッコ{ }をつけ足そうとしても、エラーとなるだけで無効です。

セルへの入力事項を確定する前に、Ctrl + Shiftを押しながらEnterキーをクリックすることから、3つのキーの頭文字(Ctrl + Shift+Enter)を取ってCSE関数(CSE数式)と言われたりもします。

では、具体的にこのTRANSPOSE(配列)の関数を使って、横長の表を縦長に変えてみましょう。

仕事がススむ関数(10)同一データを、縦にも横にも分析できる TRANSPOSE関数を使ってみましょう

今回、行列を入れ替える必要がある表は2行79列である、ということをメモしておく必要がありますが、入れ替え先となるシートの空きスペースをあらかじめ選択します(関数の返り値を格納する79行2列を、ドラッグで選択します)。

上図では、セルA7で、

=TRANSPOSE(A1:CA2

と入力した後で、

CSE(Ctrl + Shift+Enter)

3つのキーを一斉にクリックします。

仕事がススむ関数(10)同一データを、縦にも横にも分析できる TRANSPOSE関数を使ってみましょう

TRANSPOSE関数のカッコ()内引数が配列となり、2行79列の表が、同じ内容のまま、79行2列の表として複製されました。

横長の表を縦長に変換したら、あとは各指数をキーとする並べ替え(降順)をすることで、2022年5月の物価高主要因がわかります。

仕事がススむ関数(10)同一データを、縦にも横にも分析できる TRANSPOSE関数を使ってみましょう

なんと!! エネルギー全体が2020年にくらべて2割増となっており、なかでも、電気代が17%↑、ガス代16%↑となっていることがわかりました。

ということは、Excel時短で、仕事場への滞在時間を2割短縮させられれば、電気代もガス代も、物価高の影響を受けずにすませられるワケですね!? (...って、もちろんそんなに単純なハナシではありませんが、ここでは、あくまでも注目すべき指数の優先順位がわかった、というところまでを解説しました)。

まとめ:TRANSPOSE関数で、同一データの見方を切り替えられます
CSEで、配列を作ってみましょう!

数字の羅列であるExcel表に関して、「見方をかえる」ということは、新たな気づきを得るために重要なことです。そして、専門的な分析ツールをひっぱり出す前に、単に縦と横とを入れ替えるだけでも、発見できることは多数あるはずです。

大きなExcel表を入手したら、まずはそれを小さな要素に分解して、TRANSPOSE関数などを使い、「縦のものを横に」「横のものを縦に」変えてから、ながめ直してみてください。それだけでも、多くの気づきを得られることがあるでしょう。

そして、TRANSPOSE関数で使われている「配列」は、最初の入力時点でちょっとしたテクニックが必要にはなりますが、使いこなせばTRANSPOSE関数以外の計算式にも応用できます。関数式の前後にあらわれた波型カッコ{ }の意味をよく理解して、ぜひご自身で一度、配列数式を作ってみてください。

これで、あなたもプチ・データサイエンティストに一歩近づけますよ。

小さなお悩みでも、
お気軽にご相談ください!

お急ぎの方はお電話にてお問い合わせください

050-6867-2130
せるワザロゴ

セルワザでは、役に立つEXCELワザをご紹介しております!