日付を入れるだけで簡単にタテヨコ変換できる強力ワザ

エクセルで日付を指定するだけで自動的にタテヨコを入れ替えたりできないか――。そういう悩みに答えるテクニックがあります。先週の『タテヨコ変換の基本形②配列数式を利用した自動的入替』のさらに続編として、かなりマニアックな「タテヨコ変換テクニック」を紹介します。

『タテヨコ変換の基本形①「行列入れ替え＋値貼り付け」』では、実務上極めて頻繁に出てくる「タテヨコ変換」の基本形のうち「行列を入れ替えてえの値貼り付け」を紹介した。ところが、これだといちいち手作業が介在してしまう。本稿では「タテヨコ変換」をさらに簡単にやる「極意」を紹介したい。

タテヨコ変換、手作業？数式？

先々週の『タテヨコ変換の基本形①「行列入れ替え＋値貼り付け」』や先週の『タテヨコ変換の基本形②配列数式を利用した自動的入替』では、「行列を入れ替える基本テクニック」を２つほど紹介した。

新宿会計士の政治経済評論

 

1 Pocket

2022.01.29

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

https://shinjukuacc.com/20220129-99

エクセルで「タテヨコ変換」をするマニアックな方法として、「配列数式」などを使用する方法を紹介します。それが「TRANSPOSE関数」を使ったテクニックです。いったいどうやればよいのでしょうか。エクタツは、「エクセル評論家」でもある新宿会計士が、エクセルなどの「オフィス系の汎用ソフト」を使い、誰でも気軽に、かつ安価に、とても迅速かつ正確に仕事をこなす、ビジネスマンとしての基本テクニックを研究するサイトである。先週の『タテヨコ変換の基本形①「行列入れ替え＋値貼り付け」』では、実務上極めて頻繁に出てくる「タ...

まず、基本形の①は、「選択してコピー」→「値貼り付け」の際に行列を入れ替えるというテクニックであり、もっとも「お手軽なワザ」だが、その反面、いちいち範囲を選択して値貼り付けをしなければならないこと、大変にミスを誘発しやすいことなどの欠点がある。

そこで、当ウェブサイトがおすすめする方法は、何といっても②のやり方だ。

これだと最初から関数を入力してあるので、そもそも「範囲を間違える」ということはあり得ないからだ。

ただ、ここでもう一歩踏み込んで、「日々変化するデータを効率的に取り込む方法」がないかを検討してみたい。

やはりいちばんやりやすいのは、「MATCH」関数と「INDIRECT」関数の組み合わせだ。

ミスを誘発しやすい「値貼り付け」

たとえば、こんなデータがあったとしよう（図表１）。

図表１　サンプルデータ（シート名は「Sheet1」）

【出所】東京都『新型コロナウイルス陽性患者発表詳細』より当ウェブサイト作成）

これは、新型コロナウィルス感染症の陽性者数を年代別・日付別に集計したものだ（数値は実際に東京都が発表したものを使用している）。

この図表は、タテ、すなわち行を日付、ヨコ、すなわち列を年代別に区切っている。「ある日の新規陽性者の年齢別構成」やその推移を追っていく際には、わりと見やすい図表ではあるが、いかんせん加工し辛い。

たとえば、「８月31日」だけを抜き出して、その日の新規陽性者数と全体の構成割合を調べたければ、６行目を選択し（図表２）、それを別シートなどに「タテヨコ変換」して「値貼り付け」（図表３）して転記する（図表４）のが現実的だろう。

図表２　６行目を選択

図表３　タテヨコ変換のうえ値貼り付け

図表４　転記完了

いおちおう、これで転記自体は可能だ。

しかし、このやり方だと、どうにも面倒である。

８月31日の値を張り付けたつもりが、うっかり選択する行を間違ってしまい、前日の８月30日のものを張り付けてしまうこともあるからだ。

まずは転記用シートを作ろう

では、どうすれば良いか。

ここでは、「８月31日」と入力したら、自動的に元シートから「８月31日に相当するデータを引っ張ってくれる」という関数を貼ってみよう。

やり方は簡単。

まずは、こんなシートを用意する（図表５）。

図表５　転記用シートの作成

さきほどの図表１のサンプルデータだと、タテに日付、ヨコに年代が入っていたが、こちらの図表５の方では、逆にタテに年代、ヨコに日付を入れてみよう。その際、一番上の行はわざと１つ空け、B2セルは書式を「日付」に設定しよう（日付の書式設定については『日付をさまざまな値に変換する「TEXT関数」の威力』等参照）。

新宿会計士の政治経済評論

2021.11.28

日付をさまざまな値に変換する「TEXT関数」の威力

https://shinjukuacc.com/20211128-99

本稿は、著者自身がかつて運営していた『エク達』というウェブサイトに掲載した、「TEXT関数」を使った文字列操作の基本形について触れた記事を転載したものです。昨日の『日付データからさまざまな情報を抽出するTEXT関数』ともあわせてぜひともご参照ください。エク達は、「エクセル評論家」でもある新宿会計士が、エクセルなどの「オフィス系の汎用ソフト」を使い、誰でも気軽に、かつ安価に、とても迅速かつ正確に仕事をこなす、ビジネスマンとしての基本テクニックを研究するサイトである。エクセルを使いこなすには、基本的な関...

次に、転載元シートでいう「８月31日」は、６行目だったことを思い出してほしい。

「転載元シートの①６行目の②B列からM列までを、転載先シートのB列の３行目から14行目までに転載すること」を、まずは目指したい。

MATCH関数で行数を特定する

ここで威力を発揮するのがMATCH関数だ。

書式は次のとおり、

MATCH(検査値,検査範囲,[照合の種類])

「照合の種類」の箇所には、とりあえず「０（ゼロ）」を入力しよう。その理由は、VLOOKUP関数と同じく、「０」を入力しないと、わけのわからない値を引っ張ってしまい、エラーとなることがあるからだ。

そして、図表５の転記用シートのB1セルに、こんな数式を入力する。

=MATCH(B2,Sheet1!A:A,0)

図表６　数式の入力

この数式の意味は、「『B2の値』、すなわち『2021/08/31』に相当するものが、『Sheet1!A:A』の範囲の何番目にあるかを返しなさい」、というものだ。サンプルの例だと、「６」と表示されるはずである（図表７）。

図表７　計算の結果

これで、最初の目標である「①６行目」を抽出することができた。

TRANSPOSE関数の基本を思い出す

次の目標は「②Sheet1のB列からM列」という指定だが、ここで昨日のTRANSPOSE関数を使う。

TRANSPOSE関数は、あらかじめ入力する範囲を選択したうえで、数式（※以下）を入力し、「Ctrl+Shift」を押しながらエンターで入力するというのがルールだったことを思い出してほしい（『タテヨコ変換の基本形②配列数式を利用した自動的入替』参照）。

=TRANSPOSE(Sheet1!B6:M6)

ただし、本日はこの計算式ではなく、「B6:M6」の部分を「INDIRECT関数」で置き換えてやろう、という試みだ。

INDIRECT関数を組み込む

具体的には、日付によって変わるのが「６行目」であり、「B列からM列まで」については日付によって変わることはないため、可変部分を「INDIRECT」関数でこう置き換えてやる。

=TRANSPOSE(INDIRECT(“Sheet1!B”&B1&”:M”&B1))

この意味するところは、「Sheet1!B●:M●」を「タテヨコ変換せよ」、という意味だが、この「●」の部分が先ほど求めた「６」という数値を引用する形となっている。

図表８　計算式の分解

実際、B3セルからB14セルを選択した状態で、この数式を入力して「Ctrl+Shift」を押しながらEnterキーを入力すると、８月31日の新規陽性者数の年齢別データがうまく転載されるのが確認できるだろう（図表９）。

図表９　計算結果

つまり、INDIRECT関数を使うことで、B1セル「=MATCH(B2,Sheet1!A:A,0)」の計算結果である「６」を配列関数の中にうまく組み込めた格好だ。

あとは、たとえばこの日付を、たとえば「８月30日」に変更してやれば、自動でB1セルの計算結果も「５」に変わり、参照先も自動的に変更される、というわけだ（図表10）。

図表10　日付を変更した場合の計算結果

最初に計算式を組むのは大変かもしれないが、あとは日付さえ入力してやれば、各日にその日のデータを引っ張って来ることができる。これが、「エクセルの達人」による「正確で簡単なタテヨコ変換の極意」である。