スプレッドシート 複数シートの自動集計リスト作成をしたい

Question

１つのスプレッドシートに部毎の5つのシートがあり、
それぞれのシートで未対応がある部署の一覧リストを作成したいのですが、
ググったりまわりの方と知恵を出し合っても、行き詰ってしまいましたので
質問させていただいております。
ここで質問するべき内容ではない可能性も感じてはおりますが
他にどうしたらよいのかもわからずでして、どうかよろしくお願いいたします。

部毎に５つのシートがあり、各部で入力管理しています。
（だいぶはしょっていますが、このようなデータです↓）

A部～E部をまとめる結合用のシートを１つ作成し、
そこからピボットテーブルで未対応をカウントした場合、
ひとつの項目づつ以下のようにしかできませんでした。
（データの整合性とれていないですが、そこはスルーしてください）

最終的に、以下ようなリストを作成し、
元データの入力があるたびに自動更新されるようにしたいのですが、
上記のピボット結果をひとつにまとめる方法、
または、そもそももっと別の方法で行ったほうが良いなどありましたら、
ご教授いただきたくお願いいたします。

【追記】
実際のA部～E部のシートは、上記キャプチャのE～Gの項目欄が『済／未対応』表記ではなく
以下のように『入力があるか、ブランク』となっています。
また、E列○○の箇所が、数と文字の入力が混合するため、
I列に、何かしら入力がある場合＝1、ない場合はブランクとする式 =if(E5="","",1) を入れています。

結合用のシート↓　

部毎のシートを下記の式で表示（全セルに数式が入っています）、
J～Lでブランク箇所に未対応と表示させて、
こちらのI~Lを使ってピボットで部毎に未対応の個数をカウントしました。
（部署名がない箇所はフィルタで非表示）

AGの式　111行目　='A部'!A4　　12～21行目　='B部'!A5　　22～31行目　='C部'!A5　・・・

J～Lの式 =if(isblank(E2),"未対応",E2)

よろしくお願いいたします。

Comments

[sawa]

関数で集計するのが一番簡単だと思います。個々のシートから直接でもいいですが、せっかく「A部～E部をまとめる結合用のシート」を作られてるなら、それをベースにした方が簡単なんで、どういう式で統合シートを作成されているかと、統合シートがどういうデータの並びか（画面キャプチャ）を追記いただけますか。

[merci]

sawa様
ご連絡いただき、ありがとうございます。
追記させていただきました。どうぞよろしくお願いいたします。

Answer 1

結合用のシートの情報を追記いただいたので、それを使った回答を記載させていただきます。
数式での処理を考えてましたが、統合用シートを少し修正してピボットを使う方法が簡単そうです。

J～Lの式 =if(isblank(E2),"未対応",E2)の部分を
=if(isblank(E2),"未対応",)

と変更して J2に貼り付け、再度該当のセルにコピペかオートフィルしてください。
こうすると、対応済が空欄、未対応が「未対応」という状態になります。

あとはこの範囲をピボット集計してください。

その際、列は選択なしで、値を〇〇、△△、□□ それぞれでCOUNTAを選択してください。
行は部署名で、フィルタで部署名を空白以外としてください。

これで希望する表になるかと思います。

ちなみにデータ量が多いと式の数が多いほど重くなるので、以下のように式を集約することも可能です。

A～G列 A1に以下の式を(先にA～G列の式を全て削除)
={'A部'!A4:G14;'B部'!A5:G14;'C部'!A5:G14;'D部'!A5:G14;'E部'!A5:G14}

J2
=ARRAYFORMULA(if(isblank(E2:G51),"未対応",))

集計表なしでやりたい場合は、COUNTIFSという関数を使う方法があります。興味があったらお試しください。

---

追記

集計表を経由せずに直接集計をする場合は、COUNTIFSじゃなくQury関数で直接いけました。
部署名も自動で集計します。参考までに。

=Query({'A部'!C5:G;'B部'!C5:G;'C部'!C5:G;'D部'!C5:G;'E部'!C5:G},"select Col1,COUNT(Col1)-COUNT(Col3),COUNT(Col1)-COUNT(Col4),COUNT(Col1)-COUNT(Col5) where Col1 is not null group by Col1 label Col1 '部署名',COUNT(Col1)-COUNT(Col3) '〇〇',COUNT(Col1)-COUNT(Col4) '△△',COUNT(Col1)-COUNT(Col5) '□□'")

Comments

[merci]

無事に希望のリストを作成することができました！
ご丁寧に教えていただき、ありがとうございました。

対応済＝空欄、未対応＝「未対応」と表示させて、未対応をピボットで集計する。
レベルが低くてお恥ずかしいですが、
この発想が出てこなかったねとメンバーと感動しております。
今後の参考にもなりますので本当に感謝しております。

ご教示いただいた式、また集約した式でもさくさくとうまくいったのですが。
いざ実際のデータを使用してやろうとしたところ、うまくいかず・・
少しのことも応用が利かず、四苦八苦してしまいましたが、
空白のある項目に式が入っているためだということに気づき、
『isblank』を『CountBlank』変えてみたところ、できました。

ARRAYFORMULAを使いたかったのですが、
　=ARRAYFORMULA(if(CountBlank(E2:G51),"未対応",))
これだと、E2しかカントされなかったので、
　=if(CountBlank(B2),"未対応",)　
のコピペで対応しました。

今後勉強して、集計表なしの『COUNTIFS関数』にも挑戦してみたいと思います。
本当に本当にありがとうございました。
感謝です！！

[sawa]

ARRAYFORMULAの提示した式がうまく動かなかったようで失礼しました。私の環境で試したら問題ないんですけど、表の構成が違ったのかな。。

ややこしい式ですが、Query関数で集計シートなしでまとめる方法を追記しときました。
Query関数は集計の際に役立ちますので、興味があれば検索してみてください。

[merci]

ARRAYFORMULA の件、問い合わせ投稿したデータでは、バッチリ動いております！
うまく動かなかったのは、本番のデータに置き換える際に私の応用がきかなかったためです。。
言葉足らずで大変失礼いたしました。

追加でご教示いただいた『Query関数』の式で、希望のリストを完成することができました！
集計表なしですっきりと、集計時にセルが増えてしまった場合・・などの心配もいらず、
もう魔法のようです！
今後応用していろいろな場面で活用できるよう、検索等頑張りたいと思います。
本当にありがとうございました。