Excel2003での複数条件抽出について

いつもお世話になっております
Sheet1のA列にNo. B列には地名(例として東京 大阪 名古屋 福岡とします)C列に氏名 D列には数量が入ります Sheet2以降には東京 大阪 名古屋 福岡のシートがあります
今まではB列の地名と同じシート名に関数を使い各項目がそのまま一覧表示されるようにしていましたが 同一人物が複数回入力される場合 その数量を合計して重複データは一度だけという形で一覧表示されるようにするにはどんな関数を使えばいいでしょうか？
オートフィルタを使えばいいとは思いますが 依頼された先からそのまま表示されるように関数を使うよう指示がありました わかりにくいと思いますが ご教示宜しくお願い致します

No.1 回答者： Cupper-2 回答日時： 2013/01/02 23:20

関数ではなくピボットテーブルかな。

ヘルプを参考に試してみてください。

ピボットテーブルは簡単な集計から、ちょっと複雑な集計まで
少ない手順で簡単に行うことができる機能です。
覚えておいて損はないと思いますよ。

この回答へのお礼

お礼が遅れて申し訳ありません ピボットテーブルだと簡単ですよね 回答ありがとうございました

お礼日時：2013/01/03 20:08

No.2 回答者： mike_g 回答日時： 2013/01/03 00:33

》 わかりにくいと思いますが…

そのとおりなので、適切なアドバイスができません。ただ…

》 依頼された先から・・・関数を使うよう指示がありました
との由、察するに「依頼先」の担当者は Excelの関数の達人のようです。
此処は、貴方が恥を忍んで、素直に教えを乞うことをお奨めします。

この回答へのお礼

お礼が遅れ またわかりにくい質問で申し訳ありません 依頼者は達人という訳ではなく 下手に操作してデータがおかしくならないように はじめから関数で出してくれという人なんです 回答ありがとうございました

お礼日時：2013/01/03 20:12

No.3 回答者： kagakusuki 回答日時： 2013/01/03 04:10

　Sheet1のA列のNo.が、顧客ナンバーの様な、個人を区別するためのナンバーであれば話は楽だったのですが、そうと限っている訳ではないため、取り敢えず、B列が同じ地域にとなっている中には、同姓同名の氏名が現れる事は無い（例えば「佐藤一郎」という同姓同名の氏名がある場合には、「佐藤一郎 #1」、「佐藤一郎 #2」の様にして区別している）ものとして、話を進めさせて頂きます。

　今仮に、各地名のシートのB1セルに、各々の地名を表示し、A3セルには「No. 」、B3セルには「氏名」、D3セルには「数量」とそれぞれ入力されていて、実際の抽出したデータは4行目以下に表示するものとします。
　又、Sheet2のA列とB列を作業列とし使用するものとします。

　まず、Sheet2のA1セルに次の関数を入力して下さい。

=IF(OR(INDEX(Sheet1!$B:$B,ROW())="",INDEX(Sheet1!$C:$C,ROW())=""),"",INDEX(Sheet1!$C:$C,ROW())&"■"&INDEX(Sheet1!$B:$B,ROW()))

　次に、Sheet2のB1セルに次の関数を入力して下さい。

=IF(AND($A1<>"",COUNTIF($A$1:$A1,$A1)=1),INDEX(Sheet1!$B:$B,ROW())&"■"&COUNTIF($A$1:$A1,"*?■"&INDEX(Sheet1!$B:$B,ROW())),"")

　次に、Sheet2のA1～B1の範囲をコピーして、同じ列の2行目以下に貼り付けて下さい。

　次に、

東京

というシート名のシートを作成して下さい。
　次に、東京シートのB1セルに次の関数を入力して下さい。

=REPLACE(CELL("filename",B1),1,FIND("]",CELL("filename",B1),FIND(".xls",CELL("filename",B1))),)

　次に、東京シートのA4セルに次の関数を入力して下さい。

=IF(ROWS($4:4)>COUNTIF(Sheet2!$B:$B,$B$1&"■*?"),IF(ROWS($4:4)=COUNTIF(Sheet2!$B:$B,$B$1&"■*?")+2,"合計",""),ROWS($4:4))

　次に、東京シートのB4セルに次の関数を入力して下さい。

=IF(OR($A4="",$A4="合計"),"",INDEX(Sheet1!$C:$C,MATCH($B$1&"■"&ROWS($4:4),Sheet2!$B:$B,0)))


　次に、東京シートのC4セルに次の関数を入力して下さい。

=IF($A4="","",SUMIF(Sheet2!$A:$A,IF($A4="合計","*?",$B4)&"■"&$B$1,Sheet1!$D:$D))

　次に、東京シートのA4～C4の範囲をコピーして、同じ列の5行目以下に貼り付けて下さい。

　次に、東京シートのコピーシートを複数複製して、それぞれのシート名を大阪、名古屋、福岡、等の各地名に変更して下さい。

　これで、各地名のシートに抽出結果が自動的に表示されます。

この回答への補足

回答ありがとうございます 返信が遅れて申し訳ありません 質問なのですが 数式の■には何を入力すれば良いのでしょうか？

補足日時：2013/01/03 22:10

No.4 回答者： MackyNo1 回答日時： 2013/01/03 16:27

>今まではB列の地名と同じシート名に関数を使い各項目がそのまま一覧表示されるようにしていましたが 同一人物が複数回入力される場合 その数量を合計して重複データは一度だけという形で一覧表示されるようにするにはどんな関数を使えばいいでしょうか？

すでに地名ごとのデータが1つのシートに表示できているなら、同じシートに（例えばG2セルに）以下の関数を入力して下方向にオートフィルして重複のない氏名を表示させてみてはいかがでしょう。

=INDEX(C:C,SMALL(INDEX(((MATCH($C$2:$C$100&"",$C$2:$C$100&"",0)<>ROW($C$2:$C$100)-1)*1000+ROW($C$2:$C$100)),),ROW(A1)))&""

数量の合計（H2セル）はSUMIF関数で合計します。

=IF(G2="","",SUMIF(C:C,G2,D:D))

レイアウトが気になるなら、最後に元データの部分のA～D列は適宜非表示にします。

この回答へのお礼

回答ありがとうございます 非表示にする程度なら大丈夫そうです 早速試してみたいと思います

お礼日時：2013/01/03 22:14

No.5 回答者： KURUMITO 回答日時： 2013/01/03 17:27

次のようンしてはどうでしょう。

シート1に元の表が有ってＡ1セルにＮｏ，Ｂ1セルに地名、Ｃ1セルに氏名、Ｄ1セルに数量と項目名がそれぞれあって各データは下方にそれぞれ入力されているとします。
そこで作業列をＥおよびＦ列に設けます。
Ｅ2セルには次の式を入力して下方にドラッグコピーします。

=B2&C2

Ｆ2セルには次の式を入力して下方にドラッグコピーします。B列で入力されている行よりもかなり下の行までドラッグコピーします。

=IF(B2<>"",IF(COUNTIF(B$2:B2,B2)=1,ROUNDDOWN(MAX(F$1:F1),-4)+10000+ROW(),IF(COUNTIF(E$2:E2,E2)=1,ROUNDDOWN(INDEX(F$1:F1,MATCH(B2,B$1:B1,0)),-4)+ROW(),"")),IF(ROW(A1)<=COUNTA(B$2:B10000)+INT(MAX(F$1:F1)/10000),(ROW(A1)-COUNTA(B$2:B10000))*10000,""))

東京、大阪、名古屋、福岡などのシートがそれぞれに有るとしてシート見出しでＣｔｒｌキーを押しながらそれらの見出しをクリックします。これでそれらのシートは同じ作業グループとなります。
そこで表示されているシート、例えば東京のシートとします。Ａ1セルには地名と入力してＢ1セルには東京と入力します。
Ａ2セルからＤ2セルまでには、Ｎｏ，地名、氏名、数値と文字をそれぞれ入力します。
その後にＡ3セルには次の式を入力してＣ3セルまで横にドラッグコピーしたのちに下方にもドラッグコピーします。

=IF(OR(INDEX(Sheet1!$A:$D,MATCH(SMALL(Sheet1!$F:$F,RANK(ROUNDDOWN(INDEX(Sheet1!$F:$F,MATCH($B$1,Sheet1!$B:$B,0)),-4),Sheet1!$F:$F,1)+ROW(A1)),Sheet1!$F:$F,0),COLUMN(A1))=0,A2=""),"",INDEX(Sheet1!$A:$D,MATCH(SMALL(Sheet1!$F:$F,RANK(ROUNDDOWN(INDEX(Sheet1!$F:$F,MATCH($B$1,Sheet1!$B:$B,0)),-4),Sheet1!$F:$F,1)+ROW(A1)),Sheet1!$F:$F,0),COLUMN(A1)))

また、Ｄ3セルには次の式を入力して下方にドラッグコピーします。

=IF(B3="","",SUMIF(Sheet1!E:E,B3&C3,Sheet1!D:D))

最後にシート1を選択することで作業グループを解除します。
その後は各地名のシートについて、それぞれのシートのＢ1セルに該当の地名を入力することでお求めの表が表示されます。

この回答へのお礼

回答ありがとうございます 早速試してみます どうもありがとうございました

お礼日時：2013/01/03 22:18

No.6 回答者： tom04 回答日時： 2013/01/03 23:12

こんばんは！

お望みの関数ではありませんが・・・
VBAでの一例です。

Sheet1はSheet見出しの一番左側に配置してあり、Sheet2以降に「東京」「大阪」・・・
というSheet名のSheetがあるとします。

Alt+F11キー　→　メニュー　→　挿入　→　VBE画面に
↓のコードをコピー＆ペーストしてマクロを実行してみてください。
（Alt+F8キー　→　マクロ　→　マクロ実行です）

Sub 各Sheetに振り分け() 'この行から
Dim i As Long, k As Long, n As Long, str As String

Application.ScreenUpdating = False
For k = 2 To Worksheets.Count
str = Worksheets(k).Name
With Worksheets(1)
.Cells(1, 1).CurrentRegion.AutoFilter field:=2, Criteria1:=str
.Columns("C:D").Cells.Copy Worksheets(str).Cells(1, 1)
End With

With Worksheets(k)
For i = .Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
If WorksheetFunction.CountIf(.Columns(1), .Cells(i, 1)) > 1 Then
n = WorksheetFunction.Match(.Cells(i, 1), .Columns(1), False)
.Cells(n, 2) = .Cells(n, 2) + .Cells(i, 2)
.Rows(i).Delete
End If
Next i
End With
Next k
Worksheets(1).AutoFilterMode = False
Application.ScreenUpdating = True
MsgBox "処理完了"
End Sub 'この行まで

※　関数でないのでSheet1のデータ変更があるたびにマクロを実行する必要があります。
※　仮に各Sheetに関数が入っている場合は、一旦マクロを実行すると消えてしまいますので、
別Bookでマクロを試してみてください。

＞依頼された先からそのまま表示されるように関数を使うよう指示がありました

とありますがメンテナンス面からみると、関数で処理してもご希望通りに表示されない場合
関数の訂正などをご自身で出来ない場合はVBAでも同じだと思いますので、
敢えて別案を提示してみました。

参考にならなかったら無視してください。m(_ _)m

この回答へのお礼

お礼が遅くなって申し訳ありません VBAは全く知識がありませんがこれからじっくり試してみます ありがとうございました

お礼日時：2013/01/07 21:47

No.7 回答者： tom04 回答日時： 2013/01/04 01:05

No.6です！

たびたびごめんなさい。

あまり複雑でない関数でやってみました。
No.5さんとほとんど似たような感じになりますが、各Sheetに作業用の列を設けます。

↓の画像で左側がSheet1で右側が各Sheetの配置だとします。
（とりあえず「東京」Sheetで説明します）
各SheetのA1セルにはそのSheetのSheet名を入力しておきます。

画面左下のSheet見出し上の「東京」Sheetを選択Shiftキーを押しながら最後の「福岡」Sheetを選択
これで「東京」Sheet以降が作業グループ化されましたので、

A2セルに
=IF(AND(Sheet1!B2=$A$1,COUNTIF(Sheet1!$C$2:Sheet1!C2,Sheet1!C2)=1),ROW(),"")
B2セルに
=Sheet1!B2&Sheet1!C2
という数式を入れ、A2・B2セルを範囲指定　→　B2セルのフィルハンドルでずぃ～～～！っと下へコピーしておきます。
（Sheet1のデータ以上コピーしておきます。）

そして、C2セルに
=IF(COUNT(A:A)<ROW(A1),"",INDEX(Sheet1!C:C,SMALL(A:A,ROW(A1))))

D2セルに
=IF(C2="","",SUMIF(B:B,INDEX(B:B,SMALL(A:A,ROW(A1))),Sheet1!D:D))

という数式を入れ、C2・D2セルを範囲指定　→　D2セルのフィルハンドルで下へコピー！
これで画像のような感じになります。
最後にSheet見出し上で右クリック　→　作業グループ解除
これで完了です。

※　各Sheetの作業列（A・B列）が目障りであれば非表示にしておきます。
※　極力短い数式にしてみました。これであれば実状のレイアウトに合わせて
ご自身で何とかアレンジできないでしょうか？

何度も失礼しました。m(_ _)m

この回答へのお礼

何度も回答頂きありがとうございます またお礼が遅くなって申し訳ありません 短い数式にしていただけて助かります 何とかできそうです ありがとうございました

お礼日時：2013/01/07 21:50

No.8 回答者： kagakusuki 回答日時： 2013/01/04 09:42

　回答No.3です。

>質問なのですが 数式の■には何を入力すれば良いのでしょうか？


　ちょっと勘違いをされてしまったようですが、■は「サイトが使用しているフォントの中にはない文字」や、「何かの不明な文字」とか、「文字化けの類」等ではなく、そのまま、黒い四角形の（文字としての）記号です。
　「しかく」（sikaku）と入力してから、変換キーを押しますと、四角、◆、□、◇、死角、4画、等々の変換候補とともに現れる記号の1つです。
　Excelの適当なセルに

=CHAR(8739)

と入力した際に表示される文字です。

　何故その様な記号を使うのかと言いますと、Sheet2のA列に、氏名と地名を合わせたデータを表示させた際に、例えば、「そのまんま東」という氏名の方が、（宮崎県ではなく）京都に住んでいた場合、単純に氏名と住所を繋げただけでは、Sheet2のA列に表示される内容が「そのまんま東京都」となってしまい、東京のデータなのか、京都のデータなのか判らなくなってしまいます。
　又、例えば地名のシートに東京シートと京都シートがあった場合、「京都」の文字列が含まれているだけで、京都のデータであると判断する様な関数としたのでは、東京のデータも京都のデータの一部に含まれてしまう事になります。
　その様な弊害が生じる恐れを回避するために、Sheet2のA列に表示されるデータには、氏名と住所の区切りをつける目印として、通常の文章や名称では使用される事がまず無い■を間に入れる様にしておき、例えば京都のデータを集計する際にも、「京都」の文字列が含まれているか否かで判断するのではなく、、「■京都」の文字列が含まれているか否かで判断する関数としている訳です。
　ですから、必ずしも■でなければならないという訳ではなく、通常の文章や名称では使用される事がまず無い文字であれば、◇や▲、★、等々でも構いません。
　尚、今回は地名や人名ですが、これが何かの商品や機械等の名称の場合は、#、-、・、&、等々は、名称の中の文字として使用される可能性があるため、私は、区切りを表す記号として使用する事を避けており、その癖があったため、今回も■を使用した次第です。

この回答へのお礼

お礼が遅くなって申し訳ありません 詳しく説明して頂きよくわかりました ありがとうございました

お礼日時：2013/01/07 21:51