質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
Google スプレッドシート

Google スプレッドシートは、フリーで利用できる表計算ソフト。Webアプリのためインターネットに接続することで利用できます。チャートやグラフの作成のほか、シートを他のユーザーと共有したり、同時に作業を進めることも可能です。

Q&A

解決済

1回答

9853閲覧

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

merci

総合スコア1

Google スプレッドシート

Google スプレッドシートは、フリーで利用できる表計算ソフト。Webアプリのためインターネットに接続することで利用できます。チャートやグラフの作成のほか、シートを他のユーザーと共有したり、同時に作業を進めることも可能です。

0グッド

2クリップ

投稿2020/08/21 11:09

編集2020/08/22 16:46

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

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

イメージ説明

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

イメージ説明

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

イメージ説明

【追記】
実際の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)

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

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

sawa

2020/08/22 00:02

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

2020/08/22 16:47

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

回答1

0

ベストアンサー

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

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) '□□'")

投稿2020/08/23 00:27

編集2020/08/24 08:19
sawa

総合スコア3002

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

merci

2020/08/24 03:18

無事に希望のリストを作成することができました! ご丁寧に教えていただき、ありがとうございました。 対応済=空欄、未対応=「未対応」と表示させて、未対応をピボットで集計する。 レベルが低くてお恥ずかしいですが、 この発想が出てこなかったねとメンバーと感動しております。 今後の参考にもなりますので本当に感謝しております。 ご教示いただいた式、また集約した式でもさくさくとうまくいったのですが。 いざ実際のデータを使用してやろうとしたところ、うまくいかず・・ 少しのことも応用が利かず、四苦八苦してしまいましたが、 空白のある項目に式が入っているためだということに気づき、 『isblank』を『CountBlank』変えてみたところ、できました。 ARRAYFORMULAを使いたかったのですが、  =ARRAYFORMULA(if(CountBlank(E2:G51),"未対応",)) これだと、E2しかカントされなかったので、  =if(CountBlank(B2),"未対応",)  のコピペで対応しました。 今後勉強して、集計表なしの『COUNTIFS関数』にも挑戦してみたいと思います。 本当に本当にありがとうございました。 感謝です!!
sawa

2020/08/24 08:34

ARRAYFORMULAの提示した式がうまく動かなかったようで失礼しました。私の環境で試したら問題ないんですけど、表の構成が違ったのかな。。 ややこしい式ですが、Query関数で集計シートなしでまとめる方法を追記しときました。 Query関数は集計の際に役立ちますので、興味があれば検索してみてください。
merci

2020/08/25 09:28

ARRAYFORMULA の件、問い合わせ投稿したデータでは、バッチリ動いております! うまく動かなかったのは、本番のデータに置き換える際に私の応用がきかなかったためです。。 言葉足らずで大変失礼いたしました。 追加でご教示いただいた『Query関数』の式で、希望のリストを完成することができました! 集計表なしですっきりと、集計時にセルが増えてしまった場合・・などの心配もいらず、 もう魔法のようです! 今後応用していろいろな場面で活用できるよう、検索等頑張りたいと思います。 本当にありがとうございました。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問