エクセルでのデータ分析・集計について

質問

Excel2000での質問です。

あるブックの中にシートが10枚ほど含まれています。（ただしこのシートは今後も増え続けていきます）
それぞれのシートの中には、１列のみデータが入っており、そこには10ケタ程度のランダムな製品番号がズラーッと1000行くらい入っています。
それぞれのシートは日付ごとに分けられていて、シート名＝日付です。

このような時、たとえばある製品番号（番号Ａとします）が、
各シートに含まれている可能性があるのですが、
番号Ａが含まれている回数を調べるにはどうしたらよいのでしょうか？

他にも、番号Ｂ、番号Ｃ・・・と、含まれる全ての番号について、全シートにおける出現回数を集計したいのです。
番号Ａは全シートの中で６回出現、番号Ｂは３回、番号Ｃは10回・・・といったように。

ちょっと質問の書き方が分かりづらいかとは思いますが、
何か良いお知恵がありましたら教えてください。

回答No.5

tinu 2000（@tinu2000） · ベストアンサー

補足要求にお答えて下さりありがとうございます。
> （１）いいえ
となると、
ANo2さんの書かれているように、手間隙かけてコピー張り付けなどして、１シートに集める
などをして、製品番号をユニークにした一覧表を作らないといけません。

１ブックに１年分として考えます。（３６シートまで追加されるとします）

Key作成用のシートを追加します。
(1)　Key作成用のシートのA列に、それぞれのシートの製品番号データを下へ下へとコピーして貼り付けて行きます。
(2)　A列の製品番号の全体を、データ→フィルタ→フィルタオプションの設定→範囲を設定して→重複するレコードは無視する。
で製品番号をユニークにする。
http://pc.nikkeibp.co.jp/article/NPC/20060214/229243/
ここを参考に

この作業はデータシートが追加される毎に全部を対象に製品番号をユニークにする。

出現回数を集計するシートを追加します。
(3)　A列にシート１のデータを貼り付ける。B列にシート２のデータを貼り付ける・・・J列にシート１０のデータを貼り付ける（現在１０シートならここまで）
(4)　(2)で作ったユニークになった製品番号をAK列に貼り付けます。
(5)　AL列1行目に「=COUNTIF($A$1:$AJ$2000,AK1)」と入力して、下へコピー
$AJ$2000の2000は１シートに2000もデータは無いだろうの数字です。

これで完成です。
AL列が出現回数です。

タイトル行を入れる、色付けする、シート枚数、Key作成用のシートと出現回数を集計するシートを１シートにする、等は実情に合わせて下さい。

関数は COUNTIF だけでしたね。
手間暇がかかるけど、考え方がわかれば単純です（笑）

ではでは。

お礼

出来ました！！　まさにこれが求めていた結果です。
詳しい手順までご解説いただきまして、本当にありがとうございました！

なるほど、考え方を聞けば確かにシンプルですが、
でもそれがなかなか思いつかないので、私は凡人ですね・・・（＾＾；
tinu2000さんはスゴイです。とても助かりました。

回答No.4

tinu 2000（@tinu2000）

番号Ａ、番号Ｂ・・・
(1)　と製品番号は予め全部分かっていますか？
(2)　それは何種類ありますか？
> シート内での番号の重複はない、１シート１０００行くらい
ですと、少なくとも１０００以上はありますね。

> シートは今後も増え続けていきます、シート名＝日付です
(3)　１ブックに最大何日分のデータを蓄えるのでしょうか？
　　　（１ヶ月分なら３１シートで済む）

(1)が「いいえ」、なら考え方が大きく変わる
(2)６５５３６種類以上なら、考え方が大きく変わる
(3)２５６日以上なら、考え方が大きく変わる

(1)が「はい」で、(2)(3)は範囲内であれば理論的にできるが、上限に近いと恐ろしく処理時間が掛かるが、マクロを使わずに関数だけでできる。

以上、補足要求です。

補足

ありがとうございます。

（１）いいえ、番号はどんなものが含まれるのか予測はつきません。完全ランダムです。製品番号というよりシリアル番号と考えたほうが近いかもしれません。だいたい10ケタ～12ケタくらいで、数字のみです。

（２）仮に最大12ケタだとすると、可能性としては12の10乗種類・・・ということになりますでしょうか？？

（３）未定ですが、おそらく毎月１～２シートずつ増えていくと思います。ただ、全シートを対象にした計算に膨大な時間がかかるようでしたら、連続する３シート程度ごとに区切って集計するのでもかまいません。

回答No.3

neconama

１です。
CONUTIF関数はＣＯＵＮＴＩＦ関数の間違いでした。

ほんとうはこのＣＯＵＮＴＩＦ関数で串刺し集計ができれば、
一発で出現回数が出たのですが、仕様上できないようですね。

そこで各シートごとにＣＯＵＮＴＩＦ関数で出現回数を求め、
各シートごとの結果をＳＵＭ関数で串刺し集計することになるのです。
マクロは使わなくてもできると思います。

集計元になる製品番号は、Sheet1からSheet99（間はとびとびでもよい）までの
Ａ列にあるものとし、集計結果はSheet100に出すと決めます。

Sheet1からSheet99までのＢ２セルに　=CountIF(A:A,Sheet100!B1)
Sheet100のＢ２セルに　=Sum(Sheet1:Sheet99!B2)
として、Sheet100のＢ１セルに知りたい製品番号を入力すればいいわけです。

参考　３D集計（串刺し集計）
http://www.takenet.or.jp/~hayakawa/excel0/u-tanexcel50.htm

参考URL：

http://www.takenet.or.jp/~hayakawa/excel0/u-tanexcel50.htm

補足

ありがとうございます。いただいた情報を元にちょっと試行錯誤してみます。

回答No.2

imogasi

手間隙かけてコピー張り付けなどして、１シートに集めるなどしてやれば出来るかもしれない・
シートをまたいでやれるのは
統合
集計
ぐらいでしょうか。非常に限られて、難しい。
ーーー
課題丸投げではあるが、今回限りということで、VBAでの件数集計を挙げておきましょうか。
（参考　合計に変えるのは微修正でよい。）
例データ　今回は件数なので、B列データは使わない。
Sheet1　A１:B５
商品コード　金額
a 1
a 2
b 3
c 4
Sheet２A2:B７
a 1
a 2
c 3
b 4
a 5
c 6
Sheet3　A2:B5
c 1
a 2
a 3
b 4
--
Sheet4 件数合計シート
A2:A4 製品番号リスト　重複の無いものをなんらかの方法で作成するものとする
a
b
c
ーー
VBAコード
標準モジュール
Sub test01()
Dim gsh As Worksheet
Set gsh = Worksheets("Sheet4")
For Each sh In ActiveWorkbook.Sheets
'MsgBox Sh.Name
If sh.Name <> "Sheet4" Then
For j = 2 To 4
t = WorksheetFunction.CountIf(sh.Range("A1:A100"), gsh.Cells(j, "A"))
gsh.Cells(j, "B") = gsh.Cells(j, "B") + t
Next j
End If
Next
End Sub
ややこしくならないよう単純化してます。
例
For j = 2 To 4の４－－＞目視で変える。
CountIf(sh.Range("A1:A100"), のA1:A100。シートの中で最大（最下行）の行数で書き換え実行
など
ーー
結果
Sheet4 A2:B4
a 7
b 3
c 4

補足

詳しい解説ありがとうございます。
VBAは全くやったことがないので、コードの内容を地道に解読してみたいと思います。

回答No.1

neconama

まず、各シートごとに出現回数を求めるようにし、
それを串刺し集計するシートを一つ作ればできます。

使うのは、CONUTIF関数とSUM関数（串刺し集計）かな。

補足

ありがとうございます。
複数の関数を使うということは、マクロを書かないといけないのでしょうか？

また、ちょっと書き忘れてしまったのですが、ある任意の番号Ａは、一つのシートにつき１回しか出現しません。
つまり、シート内での番号の重複はない、ということです。

こうした場合でも、教えていただいた方法で出来るのでしょうか？

素人の推測として、全シートをマージして一つにまとめ、番号の重複を整理し、元となった各シートに存在していた同じ番号の出現回数を数えられるような方法があるのかな？と考えております。