エクセルで日付を指定するだけで自動的にタテヨコを入れ替えたりできないか――。そういう悩みに答えるテクニックがあります。先週の『タテヨコ変換の基本形②配列数式を利用した自動的入替』のさらに続編として、かなりマニアックな「タテヨコ変換テクニック」を紹介します。
エクタツは、「エクセル評論家」でもある新宿会計士が、エクセルなどの「オフィス系の汎用ソフト」を使い、誰でも気軽に、かつ安価に、とても迅速かつ正確に仕事をこなす、ビジネスマンとしての基本テクニックを研究するサイトである。 |
『タテヨコ変換の基本形①「行列入れ替え+値貼り付け」』では、実務上極めて頻繁に出てくる「タテヨコ変換」の基本形のうち「行列を入れ替えてえの値貼り付け」を紹介した。ところが、これだといちいち手作業が介在してしまう。本稿では「タテヨコ変換」をさらに簡単にやる「極意」を紹介したい。
目次
タテヨコ変換、手作業?数式?
先々週の『タテヨコ変換の基本形①「行列入れ替え+値貼り付け」』や先週の『タテヨコ変換の基本形②配列数式を利用した自動的入替』では、「行列を入れ替える基本テクニック」を2つほど紹介した。
エクセルで「タテヨコ変換」をするマニアックな方法として、「配列数式」などを使用する方法を紹介します。それが「TRANSPOSE関数」を使ったテクニックです。いったいどうやればよいのでしょうか。エクタツは、「エクセル評論家」でもある新宿会計士が、エクセルなどの「オフィス系の汎用ソフト」を使い、誰でも気軽に、かつ安価に、とても迅速かつ正確に仕事をこなす、ビジネスマンとしての基本テクニックを研究するサイトである。先週の『タテヨコ変換の基本形①「行列入れ替え+値貼り付け」』では、実務上極めて頻繁に出てくる「タ... タテヨコ変換の基本形②配列数式を利用した自動的入替 - 新宿会計士の政治経済評論 |
まず、基本形の①は、「選択してコピー」→「値貼り付け」の際に行列を入れ替えるというテクニックであり、もっとも「お手軽なワザ」だが、その反面、いちいち範囲を選択して値貼り付けをしなければならないこと、大変にミスを誘発しやすいことなどの欠点がある。
そこで、当ウェブサイトがおすすめする方法は、何といっても②のやり方だ。
これだと最初から関数を入力してあるので、そもそも「範囲を間違える」ということはあり得ないからだ。
ただ、ここでもう一歩踏み込んで、「日々変化するデータを効率的に取り込む方法」がないかを検討してみたい。
やはりいちばんやりやすいのは、「MATCH」関数と「INDIRECT」関数の組み合わせだ。
ミスを誘発しやすい「値貼り付け」
たとえば、こんなデータがあったとしよう(図表1)。
図表1 サンプルデータ(シート名は「Sheet1」)
【出所】東京都『新型コロナウイルス陽性患者発表詳細』より当ウェブサイト作成)
これは、新型コロナウィルス感染症の陽性者数を年代別・日付別に集計したものだ(数値は実際に東京都が発表したものを使用している)。
この図表は、タテ、すなわち行を日付、ヨコ、すなわち列を年代別に区切っている。「ある日の新規陽性者の年齢別構成」やその推移を追っていく際には、わりと見やすい図表ではあるが、いかんせん加工し辛い。
たとえば、「8月31日」だけを抜き出して、その日の新規陽性者数と全体の構成割合を調べたければ、6行目を選択し(図表2)、それを別シートなどに「タテヨコ変換」して「値貼り付け」(図表3)して転記する(図表4)のが現実的だろう。
図表2 6行目を選択
図表3 タテヨコ変換のうえ値貼り付け
図表4 転記完了
いおちおう、これで転記自体は可能だ。
しかし、このやり方だと、どうにも面倒である。
8月31日の値を張り付けたつもりが、うっかり選択する行を間違ってしまい、前日の8月30日のものを張り付けてしまうこともあるからだ。
まずは転記用シートを作ろう
では、どうすれば良いか。
ここでは、「8月31日」と入力したら、自動的に元シートから「8月31日に相当するデータを引っ張ってくれる」という関数を貼ってみよう。
やり方は簡単。
まずは、こんなシートを用意する(図表5)。
図表5 転記用シートの作成
さきほどの図表1のサンプルデータだと、タテに日付、ヨコに年代が入っていたが、こちらの図表5の方では、逆にタテに年代、ヨコに日付を入れてみよう。その際、一番上の行はわざと1つ空け、B2セルは書式を「日付」に設定しよう(日付の書式設定については『日付をさまざまな値に変換する「TEXT関数」の威力』等参照)。
本稿は、著者自身がかつて運営していた『エク達』というウェブサイトに掲載した、「TEXT関数」を使った文字列操作の基本形について触れた記事を転載したものです。昨日の『日付データからさまざまな情報を抽出するTEXT関数』ともあわせてぜひともご参照ください。エク達は、「エクセル評論家」でもある新宿会計士が、エクセルなどの「オフィス系の汎用ソフト」を使い、誰でも気軽に、かつ安価に、とても迅速かつ正確に仕事をこなす、ビジネスマンとしての基本テクニックを研究するサイトである。エクセルを使いこなすには、基本的な関... 日付をさまざまな値に変換する「TEXT関数」の威力 - 新宿会計士の政治経済評論 |
次に、転載元シートでいう「8月31日」は、6行目だったことを思い出してほしい。
「転載元シートの①6行目の②B列からM列までを、転載先シートのB列の3行目から14行目までに転載すること」を、まずは目指したい。
MATCH関数で行数を特定する
ここで威力を発揮するのがMATCH関数だ。
書式は次のとおり、
MATCH(検査値,検査範囲,[照合の種類])
「照合の種類」の箇所には、とりあえず「0(ゼロ)」を入力しよう。その理由は、VLOOKUP関数と同じく、「0」を入力しないと、わけのわからない値を引っ張ってしまい、エラーとなることがあるからだ。
そして、図表5の転記用シートのB1セルに、こんな数式を入力する。
=MATCH(B2,Sheet1!A:A,0)
図表6 数式の入力
この数式の意味は、「『B2の値』、すなわち『2021/08/31』に相当するものが、『Sheet1!A:A』の範囲の何番目にあるかを返しなさい」、というものだ。サンプルの例だと、「6」と表示されるはずである(図表7)。
図表7 計算の結果
これで、最初の目標である「①6行目」を抽出することができた。
TRANSPOSE関数の基本を思い出す
次の目標は「②Sheet1のB列からM列」という指定だが、ここで昨日のTRANSPOSE関数を使う。
TRANSPOSE関数は、あらかじめ入力する範囲を選択したうえで、数式(※以下)を入力し、「Ctrl+Shift」を押しながらエンターで入力するというのがルールだったことを思い出してほしい(『タテヨコ変換の基本形②配列数式を利用した自動的入替』参照)。
=TRANSPOSE(Sheet1!B6:M6)
ただし、本日はこの計算式ではなく、「B6:M6」の部分を「INDIRECT関数」で置き換えてやろう、という試みだ。
INDIRECT関数を組み込む
具体的には、日付によって変わるのが「6行目」であり、「B列からM列まで」については日付によって変わることはないため、可変部分を「INDIRECT」関数でこう置き換えてやる。
=TRANSPOSE(INDIRECT(“Sheet1!B”&B1&”:M”&B1))
この意味するところは、「Sheet1!B●:M●」を「タテヨコ変換せよ」、という意味だが、この「●」の部分が先ほど求めた「6」という数値を引用する形となっている。
図表8 計算式の分解
実際、B3セルからB14セルを選択した状態で、この数式を入力して「Ctrl+Shift」を押しながらEnterキーを入力すると、8月31日の新規陽性者数の年齢別データがうまく転載されるのが確認できるだろう(図表9)。
図表9 計算結果
つまり、INDIRECT関数を使うことで、B1セル「=MATCH(B2,Sheet1!A:A,0)」の計算結果である「6」を配列関数の中にうまく組み込めた格好だ。
あとは、たとえばこの日付を、たとえば「8月30日」に変更してやれば、自動でB1セルの計算結果も「5」に変わり、参照先も自動的に変更される、というわけだ(図表10)。
図表10 日付を変更した場合の計算結果
最初に計算式を組むのは大変かもしれないが、あとは日付さえ入力してやれば、各日にその日のデータを引っ張って来ることができる。これが、「エクセルの達人」による「正確で簡単なタテヨコ変換の極意」である。
エクタツは、「エクセル評論家」でもある新宿会計士が、エクセルなどの「オフィス系の汎用ソフト」を使い、誰でも気軽に、かつ安価に、とても迅速かつ正確に仕事をこなす、ビジネスマンとしての基本テクニックを研究するサイトである。 |
View Comments (1)
別解で
=TRANSPOSE(FILTER(Sheet1!B2:M6,Sheet1!A2:A6=B2))
もありますねー。