[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『重複データありのVLOOKUPもしくはINDEX&MATCH』（すぬらば）
A B C D E F G H I J 1 山田 太郎 株式会社A JR 摂津富田 ～ 京橋 390 ○ 2 山田 太郎 株式会社A 京阪 京橋 ～ 大和田 270 ○ 3 山田 太郎 株式会社B JR 摂津富田 ～ 志紀 640 ○ 4 山田 太郎 有限会社C 阪急 富田 ～ 北千里 280 ○ 5 山田 太郎 有限会社C 阪急バス 北千里 ～ 今宮 220 ○ 6 山田 太郎 株式会社D JR 摂津富田 ～ 尼崎 420 ○ 7 山田 太郎 株式会社D 阪神バス JR尼崎 ～ 下坂部 210 ○
＜ 使用 Excel：Excel2013、使用 OS：Windows10 ＞
下記のような各会社に行くための経路表がSheet1（以下、経路表）にあります。
A B C D E F G H I J 1 山田 太郎 株式会社A JR 摂津富田 ～ 京橋 390 ○ 2 山田 太郎 株式会社A 京阪 京橋 ～ 大和田 270 ○ 3 山田 太郎 株式会社B JR 摂津富田 ～ 志紀 640 ○ 4 山田 太郎 有限会社C 阪急 富田 ～ 北千里 280 ○ 5 山田 太郎 有限会社C 阪急バス北千里 ～ 今宮 220 ○ 6 山田 太郎 株式会社D JR 摂津富田 ～ 尼崎 420 ○ 7 山田 太郎 株式会社D 阪神バスJR尼崎 ～ 下坂部 210 ○
同じくSheet1に下記のようなリスト（L列に日付、M列に会社名。以下リスト）があります。
L M
2018/4/1 株式会社A
2018/4/7 株式会社B
2018/4/8 株式会社B
2018/4/14 有限会社C
2018/4/15 有限会社C
2018/4/21 株式会社D
2018/4/22 株式会社D
2018/4/28 有限会社C
2018/4/29 有限会社C
上記のリストを元に経路表からデータを引っ張って
別シートに下記のように表示させたいです。
A B C D E F G H 1 2018/4/1 山田 太郎 JR 摂津富田 ～ 京橋 390 ○ 2 2018/4/1 山田 太郎 京阪 京橋 ～ 大和田 270 ○ 3 2018/4/7 山田 太郎 JR 摂津富田 ～ 志紀 640 ○ 4 2018/4/8 山田 太郎 JR 摂津富田 ～ 志紀 640 ○ 5 2018/4/14 山田 太郎 阪急 富田 ～ 北千里 280 ○ 6 2018/4/14 山田 太郎 阪急バス北千里 ～ 今宮 220 ○ 7 2018/4/15 山田 太郎 阪急 富田 ～ 北千里 280 ○ 8 2018/4/15 山田 太郎 阪急バス北千里 ～ 今宮 220 ○ 9 2018/4/21 山田 太郎 JR 摂津富田 ～ 尼崎 420 ○ 10 2018/4/21 山田 太郎 阪神バスJR尼崎 ～ 下坂部 210 ○ 11 2018/4/22 山田 太郎 JR 摂津富田 ～ 尼崎 420 ○ 12 2018/4/22 山田 太郎 阪神バスJR尼崎 ～ 下坂部 210 ○ 13 2018/4/28 山田 太郎 阪急 富田 ～ 北千里 280 ○ 14 2018/4/28 山田 太郎 阪急バス北千里 ～ 今宮 220 ○ 15 2018/4/29 山田 太郎 阪急 富田 ～ 北千里 280 ○ 16 2018/4/29 山田 太郎 阪急バス北千里 ～ 今宮 220 ○
要は
株式会社Aに行くとA1:J2
株式会社Bに行くとA3:J3
株式会社Cに行くとA4:J5
株式会社Dに行くとA6:J7
それぞれの会社に行く経路を会社ごとに１つのデータとして
VLOOKUP等の関数またはVBAで表示させることは可能でしょうか？
作業列を使った重複データありのVLOOKUPは同じ会社が複数出てくる為、上手くいかず。
解決方法をご教示いただけるとありがたいです。
（すぬらば） 2018/05/15(火) 22:01
Sheet1はO列を作業列として使います。
Sheet1 O1 1を入力 O2 =O1+COUNTIF(B:B,M1) 最大データ数以上の行まで下にフィルコピー
Sheet2はJ列を作業列として使います。
Sheet2 A1 =INDEX(Sheet1!L:L,MATCH(ROW(A1),Sheet1!O:O)) B1 =IF(A1,Sheet1!A$1,"") J1 =INDEX(Sheet1!M:M,MATCH(ROW(A1),Sheet1!O:O)) 必要数下にフィルコピー A列の書式設定をユーザー設定で yyyy/m/d;; にする
C1 =IF($A1,INDEX(Sheet1!E:E,MATCH($J1,Sheet1!$B:$B,0)+COUNTIFS($J$1:$J1,$J1,$A$1:$A1,$A1)-1),"") H列までと、必要数下にフィルコピー
無駄な作業列使ってたので修正します。(23:37)
（sy） 2018/05/15(火) 23:29
'L列１行目からデータがあるとして
Dim r As Range, rr As Range, c As Range
Sheets("Sheet2").Cells.Clear
Set r = Sheets("Sheet1").Range("L1")
Set rr = Sheets("Sheet2").Range("A1")
Do While Trim(r.Value) <> ""
For Each c In Sheets("Sheet1").Range("A:A").SpecialCells(2)
If c.Offset(, 1).Value = r.Offset(, 1).Value Then
rr.Resize(, 8).Value = Array(r.Value, c.Value, c.Offset(, 4).Value, c.Offset(, 5).Value, c.Offset(, 6).Value, c.Offset(, 7).Value, c.Offset(, 8).Value, c.Offset(, 9).Value)
Set rr = rr.Offset(1)
End If
Next c
Set r = r.Offset(1)
Loop
End Sub
（mm） 2018/05/16(水) 10:21
できました！
すごいです！
ただ、後出しで誠に申し訳ございません。
下記の状態（経路表に複数人の経路があり、検索用のリストに人物名が追加）だと、
どうなりますでしょうか？
A B C D E F G H I J 1 山田 太郎 株式会社A JR 摂津富田 ～ 京橋 390 ○ 2 山田 太郎 株式会社A 京阪 京橋 ～ 大和田 270 ○ 3 山田 太郎 株式会社B JR 摂津富田 ～ 志紀 640 ○ 4 山田 太郎 有限会社C 阪急 富田 ～ 北千里 280 ○ 5 山田 太郎 有限会社C 阪急バス北千里 ～ 今宮 220 ○ 6 山田 太郎 株式会社D JR 摂津富田 ～ 尼崎 420 ○ 7 山田 太郎 株式会社D 阪神バスJR尼崎 ～ 下坂部 210 ○ 8 佐藤 花子 事務所A 奈良交通宮の森町 ～ 奈良駅 230 ○ 9 佐藤 花子 事務所A 近鉄 近鉄奈良 ～ 竹田 620 ○ 10 佐藤 花子 事務所A 地下鉄 竹田 ～ 四条 260 ○ 11 佐藤 花子 事務所B 奈良交通宮の森町 ～ 奈良駅 230 ○ 12 佐藤 花子 事務所B JR 奈良 ～ 桜井 320 ○ 13 佐藤 花子 事務所B 近鉄 桜井 ～ 耳成 150 ○
L M N
2018/4/1 山田 太郎 株式会社A
2018/4/7 山田 太郎 株式会社B
2018/4/8 山田 太郎 株式会社B
2018/4/14 山田 太郎 有限会社C
2018/4/15 山田 太郎 有限会社C
2018/4/21 佐藤 花子 事務所A
2018/4/22 佐藤 花子 事務所A
2018/4/28 佐藤 花子 事務所B
2018/4/29 佐藤 花子 事務所B
（すぬらば） 2018/05/16(水) 11:25
数式の方でしたら、以下の式を書き換えて下さい。 フィルコピー等の文面が少し変わってますので注意して下さい。 無いとは思いましたけど、同じ日に、同じ人が、同じ場所に、2回以上行った場合も対応できるようにしたので その分重くなるので、Sheet1のデータ数は1万行までにしています。 もっとデータ数が多い場合は、C1の式のCOUNTIFS(Sheet1!$A$1:$A$10000,$B1,Sheet1!$B$1:$B$10000,$J1) の1万を変更して下さい。
Sheet1 O2 =O1+COUNTIFS(A:A,M1,B:B,N1) 最大データ数より1行以上下にフィルコピー(多い分には何行多くてもOK)
Sheet2 B1 =INDEX(Sheet1!M:M,MATCH(ROW(A1),Sheet1!O:O)) J1 =INDEX(Sheet1!N:N,MATCH(ROW(A1),Sheet1!O:O)) 必要数下にフィルコピー A・B列の書式設定をユーザー設定で yyyy/m/d;; にする
C1 =IF($A1,INDEX(Sheet1!E:E,MATCH($J1,Sheet1!$B:$B,0)+MOD(COUNTIFS($B$1:$B1,$B1,$J$1:$J1,$J1,$A$1:$A1,$A1),COUNTIFS(Sheet1!$A$1:$A$10000,$B1,Sheet1!$B$1:$B$10000,$J1))-1),"") H列までと、必要数下にフィルコピー
（sy） 2018/05/16(水) 22:39
ただ、私のやり方がよくないのか上手く行かず、
残念ながらC列～H列が思うように表示されずでした…
会社Bは会社Aの2行目を。
会社Cは会社Bを。ただ、会社Bは経路が1行なのでそのままそれを表示。
と言った感じで各経路の最後の行は
sheet1の一つ前の経路の最後の行を見に行っているようです。
excelがおかしいのかと思い、修復してみましたが変わらずでした。
L M N O
2018/4/1 山田 太郎 株式会社A 1
2018/4/7 山田 太郎 株式会社B 3
2018/4/8 山田 太郎 株式会社B 4
2018/4/14 山田 太郎 有限会社C 5
2018/4/15 山田 太郎 株式会社D 7
2018/4/21 山田 太郎 株式会社D 9
2018/4/22 佐藤 花子 事務所A 11
2018/4/28 佐藤 花子 事務所B 14
2018/4/29 佐藤 花子 事務所B 17
2018/4/30 山田 太郎 有限会社C 20
2018/4/30 山田 太郎 株式会社D 22
24
A B C D E F G H I J
2018/4/1 山田 太郎 JR 摂津富田 ～ 京橋 390 ○ 株式会社A
2018/4/1 山田 太郎 京阪 京橋 ～ 大和田 270 ○ 株式会社A
2018/4/7 山田 太郎 京阪 京橋 ～ 大和田 270 ○ 株式会社B
2018/4/8 山田 太郎 京阪 京橋 ～ 大和田 270 ○ 株式会社B
2018/4/14 山田 太郎 阪急 富田 ～ 北千里 280 ○ 有限会社C
2018/4/14 山田 太郎 JR 摂津富田 ～ 志紀 640 ○ 有限会社C
2018/4/15 山田 太郎 JR 摂津富田 ～ 尼崎 420 ○ 株式会社D
2018/4/15 山田 太郎 阪急バス 北千里 ～ 今宮 220 ○ 株式会社D
2018/4/21 山田 太郎 JR 摂津富田 ～ 尼崎 420 ○ 株式会社D
2018/4/21 山田 太郎 阪急バス 北千里 ～ 今宮 220 ○ 株式会社D
2018/4/22 佐藤 花子 奈良交通 宮の森町 ～ 近鉄奈良駅 230 ○ 事務所A
2018/4/22 佐藤 花子 近鉄 近鉄奈良 ～ 竹田 620 ○ 事務所A
2018/4/22 佐藤 花子 阪神バス JR尼崎 ～ 下坂部 210 ○ 事務所A
2018/4/28 佐藤 花子 奈良交通 宮の森町 ～ JR奈良駅 230 ○ 事務所B
2018/4/28 佐藤 花子 JR 奈良 ～ 桜井 320 ○ 事務所B
2018/4/28 佐藤 花子 地下鉄 竹田 ～ 四条 260 ○ 事務所B
2018/4/29 佐藤 花子 奈良交通 宮の森町 ～ JR奈良駅 230 ○ 事務所B
2018/4/29 佐藤 花子 JR 奈良 ～ 桜井 320 ○ 事務所B
2018/4/29 佐藤 花子 地下鉄 竹田 ～ 四条 260 ○ 事務所B
2018/4/30 山田 太郎 阪急 富田 ～ 北千里 280 ○ 有限会社C
2018/4/30 山田 太郎 JR 摂津富田 ～ 志紀 640 ○ 有限会社C
2018/4/30 山田 太郎 JR 摂津富田 ～ 尼崎 420 ○ 株式会社D
2018/4/30 山田 太郎 阪急バス 北千里 ～ 今宮 220 ○ 株式会社D
（すぬらば） 2018/05/17(木) 12:24
C1 =IF($A1,INDEX(Sheet1!E:E,MATCH($J1,Sheet1!$B:$B,0)+MOD(COUNTIFS($B$1:$B1,$B1,$J$1:$J1,$J1,$A$1:$A1,$A1)-1,COUNTIFS(Sheet1!$A$1:$A$10000,$B1,Sheet1!$B$1:$B$10000,$J1))),"") （sy） 2018/05/17(木) 12:38
希望通りの動きになりました！
いやー、本当にすごいですね…
「数式の検証」で順番に処理内容を見ていますが、
私には全然思いつけなかったので、勉強になります。
本当にありがとうございました！
（すぬらば） 2018/05/17(木) 14:30
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.