店舗名が2行にまたがり、性別が縦並びの集計結果シートである場合の集計方法

質問

各店舗ごとに、現在までの性別・年代別の登録者数をカウントするため、添付ファイルのように集計したいと考えています。最初はピボットテーブルで簡単に出来ると思っていたのですが、店舗名がある決められた順番（例では、わかりやすくA店、B店というような順になってますが…）で並んだ形でリスト化する必要があるのです。しかし、その決められた順番というものには規則性はないため、ピボットテーブルでやろうとすると店舗がE店、B店、D店…といった順番で並んでしまい、うまくA店、B店、C店といった順番にすることが出来ません。

かといって、別シートに店舗名だけ決まった店舗順にリストを用意し、これをピボットテーブルのデータと組み合わせてVLOOKUPを使おうとすると、今度は集計結果のシートに店舗名が2つの行にまたがっていて、その中に性別が男、女、男、女という順番で縦並びになっているため、うまく集計できず、いくつかの別シートにまたがって集計しなければなりません。

添付ファイルのように集計するためには、簡潔に集計する方法はないでしょうか？　うまく伝えられていないと思うので、補足があればお伝えします。m(__)m

回答No.4

tom04 · ベストアンサー

こんにちは！
本来であれば、セルの結合がない方が関数を使いやすいのですが・・・

すでに、結合がしてある場合は、
無理矢理って感じになりますが、
↓の画像で説明させていただきます。

G2セルに
=SUMPRODUCT(($A$2:$A$1000=$F2)*($B$2:$B$1000=G$1)*($C$2:$C$1000=INDEX($E$2:$E$500,2*INT(ROW(A2)/2)-1)))

という数式を入れ、K2セルまでオートフィルでコピーします。
最後にG2～K2セルを範囲指定し、K2セルのオートフィルハンドルで
下へコピーすると画像のような表になります。

とりあえず、数式は1000行まで対応できるようにしていますが、
データ量によって範囲指定の領域はアレンジしてみてください。
尚、エラー処理はしていません。

以上、参考になれば幸いですが、
他に良い方法があれば読み流してくださいね。m(__)m

お礼

画像例を載せていただき有難うございます。若干手間がありますが、ほぼ希望通りで集計できます。お手数をおかけしました。m(__)m

回答No.3

popuplt

>ピボットテーブルで店舗が順番にすることが出来ません。

行レベルを「移動」で並べ替えてはいかがでしょう。

お礼

有難うございます。ピボットテーブルをもうちょっと見てみたいと思います。m(__)m

回答No.2

yaasan

ここは単純に元データにあらたな列を足してみてはどうでしょうか。

店舗番号とでも銘打って、並んで欲しい順に通し番号を打ちます（Ａ店は１、Ｂ店は２）。
実際の打ち方は関数を利用し、後で数値で貼付け直す。または登録店舗を店舗番号の列と別のシートにコピペ、Ａ店→１という風に置換、最後に登録店舗をコピペし直す。これのどちらかでいけると思います。

店舗番号を加えてピボットテーブルを作り、店舗番号でソート、完成後に店舗番号の列の幅を０にして、見えなくすればＯＫかな。

うまくいかなければ補足お願いします。

お礼

有難うございます。m(__)m　教えていただいた方法はありかと思ったのですが、ピボットテーブルですと1つの行に「登録店舗」「男性」「女性」で収まってしまうため、セルが結合した集計結果シートにうまくハマらないのです…（集計結果シートはフォーマットの変更をしてはいけないことになっているので）。しかし、他の作業で応用がきく方法であると感じたので、参考になりました。

回答No.1

cistronezk

掲載していただいた画像の左上がA1とすると以下が一例です。

=SUMPRODUCT(($C$4:$C$10000=IF($E4<>"",$E4,$E3))*($A$4:$A$10000=$F4)*($B$4:$B$10000=G$3))

これを集計範囲を選択して、Ctrl+Enterで入力してください。

お礼

有難うございます。参考にさせていただきました。m(__)m