ニュースで「物価高が進んでいる」と聞かされて、日々の暮らしに不安を感じる人もいるのではないでしょうか。
だって、収入はなかなか上がりづらいですもんね。
不安の多くは、対象ニュースなどに関する情報が不足していて、よく分からないことに起因します。不安を和らげるためには、自分で調べたり、分析したりすることが一番です。
できるだけお金や時間をかけず、手もちのネットやExcelをうまく活用して、自分なりの課題解決方法を見つけてみましょう。
今回は、Excelデータを分析に活用するため、1つの大きな表を、縦にも横にもながめられるTRANSPOSE関数をご紹介します。
物価の動向は、政府統計の総合窓口( e-Stat )で無料で調べられます
日本の物価動向を調査するには、まず、政府統計の総合窓口( e-Stat )へアクセスしてみましょう。代表的な調査資料として、消費者物価指数が毎月更新されています。
ためしに、Excel閲覧用としてアップされている「2020年基準消費者物価指数(月報)※」2022年5月分をダウンロードしてみましょう。
※2020年の平均を100として、調査対象月(年)の指数がどれだけ上下しているかを比較した指標のことです。
統計表のデータ表記方法・統一ルール(セル結合しない、非表示セルをつくらないetc.)が守られていない、汚れたデータではあるものの、ずいぶん昔から同じ書式で作られてきた資料のようですから、割り切って分析用に整形します。
“Excel閲覧用”ボタンからリンクされ、ダウンロードできる”am01-1.xlsx”というExcelブックの、一番左(トップ)のシート”am01-1”は、635行84列にもおよぶ、膨大な指数データの一覧表でした。
大まかに表の構造を図示すると、下図のようになります。
今回は、直近の消費者物価指数についてだけを調べたいので、セルに色をつけた2行79列のみを抽出して、その数字をながめてみることにします。
それにしても、横になが〜い表ですよね。Excelには、列を並び替える機能もありますから、そのまま横目で指数表をながめていってもよいのですが…。
視覚的に、あるいはランクづけのため、行と列を入れ替えた方が、わかりやすい資料となります。そこで、TRANSPOSE関数の登場です。
TRANSPOSE関数では、配列を使います
英単語で「入れ替える」という意味のTRANSPOSE関数は、文字通り(行と列などを)入れ替える時に使います。
構文
TRANSPOSE(配列)
構文自体はシンプルなのですが、引数のカッコ内に含まれる「配列」に注意する必要があります※※。
※※Office365、およびExcel2021で使用する場合には、スピル(動的配列数式)という便利な機能が使えますから、「配列」を意識しないでも、自動的に行列を入れ替えた表が複製されます。
配列(配列数式)とは、同種データの範囲を、ひとまとまりとして扱う計算方法のことをさします。今回の消費者物価指数の事例では、横80列以上の品目名などを、「ひとくくりの配列」情報として、一括して縦に置き換える操作となります。
関数式の前後に、波型カッコ{ }がついたセルを見たことはありませんか?
これを配列と知らずに、後から編集しようとすると、「配列の一部を変更することはできません。」という注意メッセージが出たりすることがあります。
また、配列は、関数や数式を最初に入力する時に定義する必要があるため、一度配列抜きで入力した計算式へ、後から前後に波型カッコ{ }をつけ足そうとしても、エラーとなるだけで無効です。
セルへの入力事項を確定する前に、Ctrl + Shiftを押しながらEnterキーをクリックすることから、3つのキーの頭文字(Ctrl + Shift+Enter)を取ってCSE関数(CSE数式)と言われたりもします。
では、具体的にこのTRANSPOSE(配列)の関数を使って、横長の表を縦長に変えてみましょう。
今回、行列を入れ替える必要がある表は2行79列である、ということをメモしておく必要がありますが、入れ替え先となるシートの空きスペースをあらかじめ選択します(関数の返り値を格納する79行2列を、ドラッグで選択します)。
上図では、セルA7で、
=TRANSPOSE(A1:CA2
と入力した後で、
CSE(Ctrl + Shift+Enter)
3つのキーを一斉にクリックします。
TRANSPOSE関数のカッコ()内引数が配列となり、2行79列の表が、同じ内容のまま、79行2列の表として複製されました。
横長の表を縦長に変換したら、あとは各指数をキーとする並べ替え(降順)をすることで、2022年5月の物価高主要因がわかります。
なんと!! エネルギー全体が2020年にくらべて2割増となっており、なかでも、電気代が17%↑、ガス代16%↑となっていることがわかりました。
ということは、Excel時短で、仕事場への滞在時間を2割短縮させられれば、電気代もガス代も、物価高の影響を受けずにすませられるワケですね!? (…って、もちろんそんなに単純なハナシではありませんが、ここでは、あくまでも注目すべき指数の優先順位がわかった、というところまでを解説しました)。
まとめ
TRANSPOSE関数で、同一データの見方を切り替えられます
CSEで、配列を作ってみましょう!
数字の羅列であるExcel表に関して、「見方をかえる」ということは、新たな気づきを得るために重要なことです。そして、専門的な分析ツールをひっぱり出す前に、単に縦と横とを入れ替えるだけでも、発見できることは多数あるはずです。
大きなExcel表を入手したら、まずはそれを小さな要素に分解して、TRANSPOSE関数などを使い、「縦のものを横に」「横のものを縦に」変えてから、ながめ直してみてください。それだけでも、多くの気づきを得られることがあるでしょう。
そして、TRANSPOSE関数で使われている「配列」は、最初の入力時点でちょっとしたテクニックが必要にはなりますが、使いこなせばTRANSPOSE関数以外の計算式にも応用できます。関数式の前後にあらわれた波型カッコ{ }の意味をよく理解して、ぜひご自身で一度、配列数式を作ってみてください。
これで、あなたもプチ・データサイエンティストに一歩近づけますよ。
