複数シートのセルの集計について

質問

複数のシートに同じ様式の表があり、それにはいくつかの項目があって該当すれば○を記載する、という形になっています。40名ほどに記載してもらったのでシートは40あります。

40シートの表のうち、たとえば「B20のセルに○を入力している人は全部で●●名」という風に別シートで集計したいのですが、セルをどう設定したらいいでしょうか。

調べたところ、複数シートだとcountifは使えないようで…。
色々試したのですがうまくいきませんでした。どなたかご教授ください。

回答No.4

cj_mover · ベストアンサー

急場凌ぎ、ということは、質問者さんもお分かりのことだとは思います。
まだ間に合っていればいいのですが、、、。

=SUM(--(CHOOSE(ROW($1:$40),Sheet1!B20,Sheet2!B20,Sheet3!B20,Sheet4!B20,Sheet5!B20,Sheet6!B20,Sheet7!B20,Sheet8!B20,Sheet9!B20,Sheet10!B20,Sheet11!B20,Sheet12!B20,Sheet13!B20,Sheet14!B20,Sheet15!B20,Sheet16!B20,Sheet17!B20,Sheet18!B20,Sheet19!B20,Sheet20!B20,Sheet21!B20,Sheet22!B20,Sheet23!B20,Sheet24!B20,Sheet25!B20,Sheet26!B20,Sheet27!B20,Sheet28!B20,Sheet29!B20,Sheet30!B20,Sheet31!B20,Sheet32!B20,Sheet33!B20,Sheet34!B20,Sheet35!B20,Sheet36!B20,Sheet37!B20,Sheet38!B20,Sheet39!B20,Sheet40!B20)="○"))

以上を数式バーに直接貼り付けたら、Enterの代わりに
Ctrl + Shift + Enter で確定して配列数式にします。
数式バーを視認して、確定後の上記数式の両端に {} が表示されていれば、正しく配列数式になっています。

　ROW($1:$40)
の 40 の部分はシート数に合わせて修正が必要です。

　,Sheet1!B20　...　,Sheet40!B20
類似の参照式を40回繰り返しているだけです。
この内、
　B20
の部分を修正する場合は置換機能を使います。
または、通常の数式での相対参照の扱いと同様、フィルコピーしたりすれば、
参照先のセルを修正出来るようにもになっています。

複数シートという設問で、シート名に言及がないのは、
シート名が普通ということだと判断しましたので、
Sheet# パターンのシート名で指定しています。
もし、シート名の命名規則がイレギュラーだった場合、
命名規則がある程度パターン化されていれば、置換機能で対応できるかも知れません。
こちらでは、そもそもVBAマクロの力を借りて数式を書き上げていますから、
そちらでシート名を書き換えるのが大変そうな状況でしたら、
補足貰えれば、できるだけ対応するつもりはあります。

Excelのバージョンが不明ですが、Excel2007よりも前のものをお使いでしたら、
=SUM(--(CHOOSE(ROW($1:$20),Sheet1!B20,Sheet2!B20,Sheet3!B20,Sheet4!B20,Sheet5!B20,Sheet6!B20,Sheet7!B20,Sheet8!B20,Sheet9!B20,Sheet10!B20,Sheet11!B20,Sheet12!B20,Sheet13!B20,Sheet14!B20,Sheet15!B20,Sheet16!B20,Sheet17!B20,Sheet18!B20,Sheet19!B20,Sheet20!B20)="○"),--(CHOOSE(ROW($1:$20),Sheet21!B20,Sheet22!B20,Sheet23!B20,Sheet24!B20,Sheet25!B20,Sheet26!B20,Sheet27!B20,Sheet28!B20,Sheet29!B20,Sheet30!B20,Sheet31!B20,Sheet32!B20,Sheet33!B20,Sheet34!B20,Sheet35!B20,Sheet36!B20,Sheet37!B20,Sheet38!B20,Sheet39!B20,Sheet40!B20)="○"))

やはり、Ctrl + Shift + Enter で確定して配列数式にします。

またいつか同じような調査？集計をする機会があったら、
最初の段階で集計し易い設計に挑戦してみて下さい。

お礼

回答誠にありがとうございました。

回答No.3

MSZ006

ユーザー定義関数を作るという裏技？もありますが、他の回答者さまのおっしゃるように、各シートごとに○の数を数えたセルをつくって、それを集計する、という方法が簡単かと思います。

お礼

回答誠にありがとうございました。

回答No.2

bunjii

>調べたところ、複数シートだとcountifは使えないようで…。
シート毎に○印の数を集計して集計用のシートで串刺し集計をすれば良いと思います。

各シートのカウント項目をCOUNTIF関数で数値化したセル（C列等）を用意します。
40枚のアンケート用紙（シート）と同じフォーマットのシート（Sheet41）を追加します。
集計用シートの集計用セルに =Σ(Sheet1:Sheet40!C20) のように関数式を入力すれば目的に合うと思います。

お礼

回答誠にありがとうございました。

回答No.1

chonami

各シートごとに、対象セルに○が入っているかどうかを集計する表をもう一つ作る。
その表を串刺しで集計する。

レイアウトなどがわからないので概要だけ。
今回の内容ではあまり関係ないかも知れませんが手順などを記載される方もいらっしゃいますのでバージョンは必ず入れてください。

それから、後々集計する事がわかっている表なら○とかでなくて1を入れていく方が便利だと思いますよ。
ま、見た目の事もありますので表示形式で1と入れたら○と表示されるとかにして。入力もテンキーでできるので楽ですし。

お礼

回答誠にありがとうございました。