エクセル関数での集計

質問

営業マン毎に新規顧客営業管理表があります。
｛鈴木のリスト｝
　 　A　 　　　　Ｂ
１ ＜顧客名＞　＜対象性＞　・・・
２ 三菱商事　　　対象
３ 住友商事　　　対象
４ 双日　　　　　非対象
５ 清水建設　　　対象
６ ・・・

｛山田のリスト｝
　　　　Ａ　　　　　Ｂ
１　＜顧客名＞　＜対象性＞　・・・
２　鹿島建設　　　対象
３　大成建設　　　非対象
４　竹中工務店　　対象
５　・・・

上の二つのリストは、同一ファイルの別々のシートになっています。

これとは別に、顧客名簿一覧表があります。
　　　　Ａ　　　　　　Ｂ　　　　　 Ｃ
１　＜顧客名＞　＜新規対象客＞　＜住所＞
２　三菱商事
３　住友商事
４　双日
５　清水建設
６　・・・
ここで、上の新規顧客営業管理表の二つのシートにおける、＜顧客名＞と＜対象性＞の二つの条件を判断して、上の顧客名簿の＜新規対象客＞の欄に「新規対象客」、または、「新規非対象客」と表示させたいのです。
　例えば、顧客名簿一覧表の三菱商事について、鈴木のリストと山田のリストの二つを参照して、＜対象性＞に対象とあれば、「新規対象客」、そうでなければ「未対象客」と表示させたいのです。
　一つのシートで一つの条件で判断するのであれば、=IF(countif(範囲,A1),"取引顧客",”未取引顧客”)でできると以前教わりましたが、(1)顧客名と対象性の両方に合致すること、(2)参照先が２つの表（シート）になることから、まったく手に負えません。どうかご教示願います。

回答No.2

SAKURAMYLOVE · ベストアンサー

関数を利用したひとつの例です。
【前提条件】
(1)鈴木のリストは、シート名を「鈴木」とし、データは、A2:B10まであるものとする。
(2)山田のリストは、シート名を「鈴木」とし、データは、A2:B10まであるものとする。
(3)両方のリストには、ともに同じ顧客名があるものとする。
【手順】
(1)新規名簿一覧表のＤ，Ｅ列があいているとしてここを作業セルに使い、以下の式を入力します。
D2=VLOOKUP(A2,鈴木のリスト!$A$2:$B$10,2)
E2=VLOOKUP(A2,山田のリスト!$A$2:$B$10,2)
(2)B列を新規対象客の列とし、B2に以下の式を入力します。
B2=IF(AND(D2="対象",E2="対象"),"新規対象","新規非対象客")
(3)B2およびD2,E3を必要なセルまでコピー、貼り付けしていきます。

もし、いずれかのリストに対象客がない場合は、作業セルに#N/Aエラーがでるので、それをISNA関数などで回避することが必要です。
【例】
D2=IF(ISNA(VLOOKUP(A2,鈴木のリスト!$A$2:$B$10,2)),"非対象",VLOOKUP(A2,山田のリスト!$A$2:$B$10,2))

E2=IF(ISNA(VLOOKUP(A2,山田のリスト!$A$2:$B$10,2)),"非対象",VLOOKUP(A2,山田のリスト!$A$2:$B$10,2))

お礼

実際に活用させていただきました。どうもありがとうございました。

回答No.3

imogasi

質問とは結果が異なりますが、（住所がありませんが）、
「顧客名簿一覧表」を各「営業担当者シート」から、作ってしまうやり方です。
ーーーー
鈴木シート（シート名「鈴木」）
顧客名 対象
三菱商事 対象
住友商事 対象
双日 非対象
清水建設 対象
大林組 対象
山田シート（シート名山田）以下何シートあっても良い。
顧客名 対象
鹿島建設 対象
大成建設 非対象
竹中工務店 対象
清水建設 非対象
大林組 対象
－－－－－－
ＶＢＡコード
Sub test01()
'-----合体
Dim sh As Worksheet
k = 2
For Each sh In ActiveWorkbook.Worksheets
If sh.Name = "Sheet3" Then GoTo p01
d = sh.Range("A65536").End(xlUp).Row
For i = 2 To d
For j = 1 To 3
Worksheets("Sheet3").Cells(k, j) = sh.Cells(i, j)
Next j
Worksheets("sheet3").Cells(k, 3) = sh.Name
k = k + 1
Next i
Next
'-----ソート
p01:
Sheets("Sheet3").Range(Cells(2, "A"), Cells(k, "C")).Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _
:=xlPinYin, DataOption1:=xlSortNormal
'-----重複排除
p02:
Dim sh3 As Worksheet
Set sh3 = Worksheets("Sheet3")
n = 2
For i = 2 To k
If m = sh3.Cells(i, "A") Then
If sh3.Cells(i, "B") = "対象" Then
sh3.Cells(n - 1, "H") = sh3.Cells(n - 1, "H") & " " & sh3.Cells(i, "C")
End If
Else
For j = 1 To 3
sh3.Cells(n, j + 5) = sh3.Cells(i, j)
Next j
m = sh3.Cells(i, "A")
n = n + 1
End If
Next i
End Sub
結果
Ｓｈｅｅ３に（下記のＦ列より右側が、最終結果です。左は中間結果。）
三菱商事 対象 鈴木 三菱商事 対象 鈴木
鹿島建設 対象 山田 鹿島建設 対象 山田
住友商事 対象 鈴木 住友商事 対象 鈴木
清水建設 対象 鈴木 清水建設 対象 鈴木
清水建設 非対象 山田 双日 非対象 鈴木
双日 非対象 鈴木 大成建設 非対象 山田
大成建設 非対象 山田 大林組 対象 鈴木 山田
大林組 対象 鈴木 竹中工務店 対象 山田
大林組 対象 山田
竹中工務店 対象 山田
－－－－
改造できるなら使えるかも。
住所等法人付加情報は、ＶＬＯＯＫＵＰで引くとかできそう。

お礼

うまく動きました。どうもありがとうございました。いつも拝見していますが、とても高度な知識をお持ちですね。尊敬します。

回答No.1

imogasi

関数例が挙がっているので、あわよくば関数でできないかという質問だと思うが、エクセルの関数では難しい（極端に複雑になる）見込みです。エクセルＶＢＡならできると思う。各営業マンのリスト客合計が６５０００以下であるとして）
これなどＸＸシステムのある部分で出てくる場面であって、表計算では不得手なものと思う。アクセスならできそう。
ーー
別件ですか、なぜ「非対象」の顧客が上がっているのですか、各営業マンの鈴木と山田の名簿がちがうのでしょうか。今は対象でないが、そのうち対象にする候補なのか。
また顧客名簿一覧表や鈴木・山田の名簿の並べ順は何ですか。

補足

早速のご回答ありがとうございます。
非対象の顧客が上がっているのは、対象と非対象の割合を出すためです。また、おっしゃるとおり、将来対象となるまたはその逆がある場合も考えています。
それから、顧客名簿一覧表や名簿の並べ順は完全なランダムです。