今回は、スプレッドシートのマクロ実行で大活躍のTRANSPOSE関数をご紹介します。マクロを使わない人でもちゃちゃっと表を作りたいときに便利です。

TRANSPOSE関数は「転置して貼り付け」をしてくれる関数です。使い方は簡単で、関数のカッコの中に転置して貼り付けたいデータの範囲を入力するだけです。

=TRANSPOSE(転置して貼り付けたいデータの範囲)

実際どんなときに使っているかというと、製品の出荷数や販売数が週単位で集計されている明細表を推移表に変換するときに使っています。

明細表は、

7/1週：商品A　数量 3
7/1週：商品B　数量 5
7/8週：商品A　数量 1
7/8週：商品B　数量 2
7/8週：商品C　数量 3
7/15週：….
7/22週：….
7/29週：….

と、データが縦に並んでいます。このデータを

7/1週　　　7/8週　　　7/15週  ….
商品A　　3　　　　　1
商品B　　5　　　　　2
商品C　　0　　　　　3

というように、週別商品別の推移表に集計します。

ピボットテーブルで集計するのが一番シンプルですが、実際に使っているデータは 17,098行×7列 のスプレッドシートでなかなか集計が終わりませんでした。さらに、この作業をマクロを使って実施したところ、処理時間が6分を超えてしまうのか、うまくいくときとうまくいかないときがあり不便を感じていました。

そこで、ピボットテーブルをあえて使わず、前回ご紹介したUNIQUE関数と今回ご紹介するTRANSPOSE関数を使って、処理時間が6分を超えない単位のマクロをいくつか作って集計しています。

実務で使っている表をブログに使っていいとお達しが出たのですが、集計している内容が出荷実績や実売実績なだけに、みなさんに見せられるような形にうまく加工することができませんでした。そのため、今回も絵本みたいなたとえになりますが、みかんとりんごとメロンの仕入を例に説明していきます。

今回は「週別仕入明細表」を集計して、「週別商品別仕入推移表」を作ります。そして、マクロで集計できるようにします。

「週別仕入明細表」

完成イメージ「週別商品別仕入推移表」

「週別仕入明細表」から「週別商品別仕入推移表」の1行目に記載されている「2019/07/01」「2019/07/08」「2019/07/15」… の日付を入力する仕組みを作るのが今回の肝です。

集計の仕方

早速、「週別商品別仕入推移表」の1行目に入力する毎週月曜日の日付を抽出していきます。

「週別仕入明細表」のA列：日付から抽出します。同じ日付が複数個入力されているので、UNIQUE関数を使って、すべての日付を1つずつ抽出します。シートを新規作成して、セル：A1に関数を入れます。わかりやすいように、シート名は「集計用データ」に変えておきます。

=UNIQUE(‘201907_週別仕入明細表’!A2:A32)

次に、「週別商品別仕入推移表」を作っていきます。シートを新規作成して、見出し「商品名」と商品の名前（みかん・りんご・メロン）は入力します（みかん・りんご・メロンなどは、UNIQUE関数を使って入力してもいいと思います。

「=UNIQUE(‘201907_週別仕入明細表’!B2:B32)」）そして、セル：B1にTRANSPOSE関数をいれます。
=TRANSPOSE(‘集計用データ’!A1:A5)

TRANSPOSE関数のカッコの中に、縦に並んでる日付の範囲（「集計用データ」シートのセル：A1からA5）を入力するだけです。無事、縦に並んでいた日付が横に並ぶようになりました。

次に、商品別・週別に集計した仕入数を入力してきます。ある条件に合致した数の合計を出したいときはSUMIF関数をよく使いますが、今回は条件が2つあるのでSUMIFS関数を使いました。

セル：B2に SUMIFS 関数を入れます。
=SUMIFS(‘201907_週別仕入明細表’!$D:$D,’201907_週別仕入明細表’!$B:$B,$A2,’201907_週別仕入明細表’!$A:$A,B$1)

（関数の意味）

• 「週別仕入明細表」の D 列（仕入数）を集計します。
• 下記2つの条件を両方とも満たす数量の合計を計算します。

1. 「週別仕入明細表」の B 列（商品名）から、「週別商品別仕入推移表」のセル：A2（商品名：みかん）
2. 「週別仕入明細表」の A 列（日付）から、「週別商品別仕入推移表」のセル：B1（2019/07/01）

※関数の中に含まれる「$」マークは絶対参照の記号です。今回のトピックではないので、思い切って説明を省略します。

簡単に言うと、関数の入ったセルを上下左右のセルにコピペしたときに、関数の中のアルファベットや列を移動させずに固定することができます。「$」マークがアルファベットの前についているか、後ろについているかで、どこを固定するかを決めることができます。

アルファベットの前と後ろに「$」マークを付けると、関数を上下左右のセルにコピペしても、そのセルから絶対に動かないようになります。

いったんは前述の「=SUMIFS(‘201907_週別仕入明細表’!$D:$D,’201907_週別仕入明細表’!$B:$B,$A2,’201907_週別仕入明細表’!$A:$A,B$1)」を使っていきます。関数を入れたセル：B2をコピーして、セル：B2からF4にペーストします。そうすると、各条件を満たした数量の合計が集計されます。

合計欄もつけておきます。関数はSUM関数を入れました。

書式を整えたら、完成です。

毎週月曜日の日付を抽出するために「集計用データ」というシートを作りましたが、「集計用データ」シートを作らずに直接「週別商品別仕入推移表」シートに抽出することもできます。

・「集計用データ」シートで使った関数：
=UNIQUE(‘201907_週別仕入明細表’!A2:A32)

・「集計用データ」シートのデータを元に「週別商品別仕入推移表」シートのセル：B1 に入れた関数：
=TRANSPOSE(‘集計用データ’!A1:A5)

⇒上記2つの関数を統合させて「週別商品別仕入推移表」シートのセル：B1に入れる：
=TRANSPOSE(UNIQUE(‘201907_週別仕入明細表’!A2:A32))

TRANSPOSE関数の中に、UNIQUE関数を入れるだけです。

マクロの使い方

ここまで理解できれば、マクロを作るだけです。私はスクリプトを書くような高度なことはできないので、「マクロの記録」というのを使ってマクロを作ります。作り方は簡単で、

• 「マクロを記録」をクリックする
• 表を作る（実際に作業をして、作業手順をスプレッドシートに覚えてもらう）
• マクロの「保存」ボタンをクリックする

の3ステップです。操作は簡単なので、スクリーンショットを参考にしてみてください。

作ったマクロを実行してみます。

一番はじめは、承認メッセージが表示されますので承認します。2回目以降は、マクロを実行しても承認メッセージは表示されません。

マクロを実行すると、数秒もかからず「週別仕入明細表」から「週別商品別仕入推移表」が完成しました。

ちなみに、行データを列に並べ替えるとき（縦に並んでるデータを横に並べたいとき）、データをコピー > 貼り付けたいセルを選択 > 右クリック > 特殊貼り付け > 転置して貼り付けをクリックすると、行データを列データにすることができます。

転置して貼り付けは簡単な作業なので、わざわざ関数を使うこともないと思っていましたが、マクロに記録したところ、実際にマクロを実行してうまく表が完成するときと、なぜか失敗してしまうときがありました。関数はセルに直接打ち込むので成功も失敗もわかりやすいと思いTRANSPOSE関数を使うようになりました。

最後に、ここまで書いてなんですが、これくらいの小さな表でしたら、マクロ記録開始 → ピボットテーブルに集計 → マクロ保存をすれば問題なく、マクロで集計できると思います。扱うデータが多すぎて集計に時間がかかって処理が止まってしまうときなどは、TRANSPOSE関数を使った方法を検討してみるといいと思います。

★

「あさすけ先生の、超役に立つGoogleスプレッドシート解説」関連記事

 
▶ 【スプレッドシート時短術】19,767個の空白セルを3秒で「0」にする小技
▶ 計算式のコピペ漏れを防ぐ！　気難しそうに見えて案外シンプルないいヤツ 〜ARRAYFORMULA関数〜
▶ VLOOKUP関数のエラー原因を探すのに2時間もかかっていた私が、いろいろな関数を使いこなせるようになったコツを考えてみた。
▶ Googleスプレッドシートに週ごとにタスクをまとめて、その週の未完了タスク・完了タスク・タスクの達成率がひと目でわかるようにする
▶ IMPORTRANGE関数で休日設定シートを転記すればすべてが丸く収まった
▶ 値ペーストと書式ペーストを使いこなすと作業が格段に速くなる – エクセルやGoogleスプレッドシート
▶ Googleスプレッドシートでデータの結合・分割の幅を広げたい。改行するなら CHAR(10) を使おう！
▶ 「$」マークを使いこなして表をパパッと作る 〜相対参照と絶対参照の復習〜
▶ わざわざ使いたい、転置して貼り付けができるGoogleスプレッドシートの「TRANSPOSE関数」（本記事）
▶ 地味にはまっている、GoogleスプレッドシートでUNIQUE関数とSORT関数で入力規則（プルダウン）用のデータリストをつくる方法
▶ Googleスプレッドシートのフィルタ表示機能が便利
▶ Googleスプレッドシートでチェックリストを5分で作ってみる
▶ 営業日の異なる日本と中国のスケジュール作成ツールをGoogleスプレッドシートで作ってみた