Excel2003 データの集計

質問

お世話になります。

表題の件で 質問が御座います。

今、添付画像の左の表から 右の表に集計している所です。

・セルＡ1の「田中さん/毛布」は１つのセルに入力されています。
・金額は「数値」で入力されています。
・名前と一緒に入力されている品名は何が入ってるか分かりません。（色々な物が想定されます）
・Ａ列には おそらく 千数百人の方々がいらっしゃいます。

この左の表を元に右のように「その人の合計金額」を集計したいのですが
何か便利な方法はないでしょうか？

現状ではフィルタを掛け「田中さんを含む」で抽出し、別シートに「田中さん」と入力して
その隣に「=SUM」で合計地道にを入れております。

まだ半分も終わってないようなので気が遠くなってまいりました。。。

１つのセルに２種類の情報が入っている為「=SUMIF」も使えません。。
どうにかして簡単に集計する方法は無いでしょうか？

どこか別のセルに「大久保さん」とか入れたら その隣に「大久保さんを含むＢ列の合計が出る」ような？

どなたか良いお知恵が御座いましたら宜しくお願い致します。
説明が不十分であれば補足致しますので何なりとお申し付けください。
何卒宜しくお願い致します。

回答No.5

hiro-ss16 · ベストアンサー

A列から名前を抽出さえ出来れば、名前と金額の表を作って、SUMIF関数で名前をキーにすれば、算出できますが、問題はA列から名前だけを抽出する方法ですね。名前が先に有ったり、最後にあったり、”さん”が付いていたり、ついていなかったりだと、これはもう手作業で分離するしかないでしょう。
分離の方法として例えば画像のように、”さん”付けで名前が先頭のものはC列に”１”、名前が先頭で”さん”の無いものは”２”、名前が最後で”さん”のあるものは”３”、同じく”さん”の無いものは”４”と手入力します。そして「データ」「フィル」「オートフィル」でオートフィルを設定し”C”列のたとえば”１”を選びます。そして、表示された表をドラッグしエクセルの例えば別シートに貼り付けます。
後は”FIND”関数で =FIND("さん",A13)+1 を１行目の空列（Ｃ１）に入れれば”４”と出ます。別の空列（Ｄ１）に =MID(A1,1,C1) の関数を入れれば、”田中さん”と出ます。各式を下にコピーします。
最後に名前があるものは、”RIGHT”関数で切り出す文字数を指定するしかないかと思います。
最後に切り出した名前と金額の表をコピ＆ペで纏めた表を作ります。参考になれば幸いです。

お礼

ご回答ありがとう御座います。

やはり手作業になりますよね。。。

なので締め切りの時間を延長してもらいました。。。

ご対応ありがとう御座いました！！

回答No.8

082

以下の方式でできそうです。

添付画像で説明すると・・・
(1)D1に集計したい名前を入力

(2)画像の表のE1に以下の数式入力
　=SUMPRODUCT(NOT(ISERROR((FIND(D1,$A$1:$A$8))))*($B$1:$B$8))

(3)集計値がE1に表示される

あとは、必要な数だけ数式をコピペしてください。
千数百通りの名前は手入力が必要ですけど・・・

お礼

ご回答ありがとう御座います。

やはり手作業になりますよね。。。

なので もう 諦めて締め切りを延長してもらいました。

ありがとう御座いました。

回答No.7

imogasi

例データA列：B列　　C,D列は結果で出す部分。
A2:B8
A列　　　　B列　　　　　　　　C列　　D列
田中さんあ 1 田中さん 8
田中さんい 2 大中さん 3
大中さんあ 3 北中さん 15
北中さんい 4
田中さんう 5
北中さんいい 5
北中さんいう 6
C列にA列から重複したデータは排除したデータを作成
D２は　=SUMIF($A$2:$A$10,C2&"*",$B$2:$B$10)
を入れて下方向に式を複写。
メインで無いのでC列をA列から出す部分は手作業と仮定したが、関数では一旦/までを別列に取り出し、データーフィルターフィルタオプションの設定ー重複するレコードは無視するでリストを出す。

お礼

ご回答ありがとう御座います。

imogasiさんから説教ではなく回答をいただけただけで光栄ですｗ

手作業を覚悟して締め切りを延長してもらいました。

ご回答ありがとう御座いました。

回答No.6

hiro-ss16

NO5です。先程の関数が間違っていました。

　誤　=FIND("さん",A13)+1
　正　=FIND("さん",A1)+1

回答No.4

mu2011

>ご提示いただいた数式には無理があります。。。
⇒ご提示の条件でご提案しただけですので「無理がある」という事ならば、何が無理なのかをご回答いただけると無理のないアドバイスができるかも知れません。
　例えば、Ｄ列を文字列置換で「さん」を削除すれば、=SUMIF(A:A,"*"&D1&"*",B:B)とすれば、名前/商品や区切り文字を意識しませが如何でしょう。

回答No.3

CaveatEmptor

では、VBAを使ってみてはどうでしょうか？

合計を求めたい名前がD1:D30に入っているとします。
その横のE列に合計を出します

Sub test()
Dim MyName(30) As String
Dim Goukei(30) As Variant
For i = 1 To 30
　　MyName(i) = Cells(i, 4)
Next
i = 1
Do While Not (IsEmpty(Cells(i, 1)))
　　For j = 1 To 30
　　　　If InStr(Cells(i, 1), MyName(j)) > 0 And MyName(j) <> "" Then
　　　　　　Goukei(j) = Goukei(j) + Cells(i, 2)
　　　　End If
　　Next
i = i + 1
Loop
For i = 1 To 30
　　Cells(i, 5) = Goukei(i)
Next
End Sub

適当に書いていますので参考程度お願いします。

お礼

再登場ありがとう御座います。

＞合計を求めたい名前がD1:D30に入っているとします。
ここの部分でＤ列に名前を抽出するのを手作業でやっていて果てしなく長く続きそうだったので質問致しました。

ありがとう御座いました。

回答No.2

CaveatEmptor

こんにちは。

まずはA列をデータ区切りで名前と品名に分け、その上でA列から重複するデータを省いたものの(画像のD列のような)リストを作り、そのリストをもとに集計していけばいいと思います。

もしA列をいじれないのなら、作業列にコピーする、または、VBAでinstrを使って集計することもできます。

お礼

ご回答ありがとう御座います！

今 確認をしましたら Ａ列は必ずしも「名前/商品」の並び順でないという事が判明いたしました。。。

「商品名/名前」の場合もあり、しかも名前も「さんづけ」「呼び捨て」両方存在しています。。。

さらにさらに 大部分は「/」で区切られているのですが時折「（ ）」で区切ってあったり「・」だったりスペースだったりと
区切り方が統一されていませんでした。。。
（こういう入力したヤツに「お前がやれよ」と言いたいですが、どんなモンを出して来るかと考えたらとてもとても。。。）

ご提示いただいた内容ですと だいぶ楽になりそうな予感ではありますが
どちらにせよ後から確認しなくてはいけなくなりそうです。。。

他に何か良いお知恵が御座いましたら再度お願い致します。
（半ば諦め気味ですが。。。）

回答No.1

mu2011

一例です。
E1に=SUMIF(A:A,D1&"*",B:B)として下方向にコピー

お礼

ご回答ありがとう御座います。

Ｄ列の名前は「抽出」もしくは「入力」しているので
ご提示いただいた数式には無理があります。。。

他に 何か良いお知恵が御座いましたら再度お願い致します。