みんなの「教えて(疑問・質問)」にみんなで「答える」Q&Aコミュニティ

こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

エクセル関数での集計

営業マン毎に新規顧客営業管理表があります。
{鈴木のリスト}
   A      B
1 <顧客名> <対象性> ・・・
2 三菱商事   対象
3 住友商事   対象
4 双日     非対象
5 清水建設   対象
6 ・・・

{山田のリスト}
    A     B
1 <顧客名> <対象性> ・・・
2 鹿島建設   対象
3 大成建設   非対象
4 竹中工務店  対象
5 ・・・

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

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

投稿日時 - 2006-01-20 20:56:13

QNo.1909674

暇なときに回答ください

質問者が選んだベストアンサー

関数を利用したひとつの例です。
【前提条件】
(1)鈴木のリストは、シート名を「鈴木」とし、データは、A2:B10まであるものとする。
(2)山田のリストは、シート名を「鈴木」とし、データは、A2:B10まであるものとする。
(3)両方のリストには、ともに同じ顧客名があるものとする。
【手順】
(1)新規名簿一覧表のD,E列があいているとしてここを作業セルに使い、以下の式を入力します。
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))

投稿日時 - 2006-01-20 22:52:19

お礼

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

投稿日時 - 2006-02-18 11:42:06

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(3)

ANo.3

質問とは結果が異なりますが、(住所がありませんが)、
「顧客名簿一覧表」を各「営業担当者シート」から、作ってしまうやり方です。
ーーーー
鈴木シート(シート名「鈴木」)
顧客名対象
三菱商事対象
住友商事対象
双日非対象
清水建設対象
大林組対象
山田シート(シート名山田)以下何シートあっても良い。
顧客名対象
鹿島建設対象
大成建設非対象
竹中工務店対象
清水建設非対象
大林組対象
------
VBAコード
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
結果
Shee3に(下記のF列より右側が、最終結果です。左は中間結果。)
三菱商事対象鈴木三菱商事対象鈴木
鹿島建設対象山田鹿島建設対象山田
住友商事対象鈴木住友商事対象鈴木
清水建設対象鈴木清水建設対象鈴木
清水建設非対象山田双日非対象鈴木
双日非対象鈴木大成建設非対象山田
大成建設非対象山田大林組対象鈴木 山田
大林組対象鈴木竹中工務店対象山田
大林組対象山田
竹中工務店対象山田
----
改造できるなら使えるかも。
住所等法人付加情報は、VLOOKUPで引くとかできそう。

投稿日時 - 2006-01-21 16:03:35

お礼

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

投稿日時 - 2006-02-18 11:44:18

ANo.1

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

投稿日時 - 2006-01-20 21:59:13

補足

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

投稿日時 - 2006-01-20 23:16:05

あなたにオススメの質問