Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10全般 : Excel 2016)
該当するデータの抽出方法
投稿日時: 19/01/06 11:52:03
投稿者: あきらか

sheet1『全予約』から sheet2『割引の対象となる予約番号一覧』に当てはまるものだけを抽出したい。
 
 
sheet1『全予約』(宿泊施設の利用履歴)
 
  予約番号   利用日   人数・・・・
@ aa123456   20181012   3
A aa546213   20180512   1
B aa857462   20181110   2
C aa123456   20181013   3
D aa123456   20181014   3
E aa154368   20180110   2
F aa442579   20180629   8
.
.
.
並び順に法則はありません。
連泊利用の場合、すべての利用日に対して同じ予約番号が振られています(例:sheet1の@CD)
(連泊の最大回数は6)
このようなデータが2万件前後あります。
 
 
sheet2『割引の対象となる予約番号一覧』 
 
 予約番号
@ aa123456  
A aa123457  
B aa588712  
C aa462138  
D aa123456  
E aa745982  
F aa442579
.
.
.
 
sheet2に記載されている【割引の対象となる予約番号】と一致する予約のみをsheet1から取り出したいのですが
 
・sheet1 予約番号列にcountif関数を使用し、同一予約番号に対して連番を振る
・予約番号と連番を結合し、検索値とする
・sheet2のすべての予約番号に対して1〜6の連番を結合させた列を作成
・sheet1 予約番号列と↑のデータでVLOOKUPを使用し抽出
 
という方法を考えたのですが、
sheet1のデータが膨大なため、うまく進めることができませんでした。
 
なにかいい方法はありますでしょうか?
つたない説明で大変恐縮ですが、お力お貸し頂けますと幸いです。よろしくお願い致します。

回答
投稿日時: 19/01/06 12:51:40
投稿者: WinArrow
投稿者のウェブサイトに移動

Sheet1の件数が膨大は
関係ありませんが・・・・
  
VLOOKUP関数を使用するには、
 「検索キーが参照範囲の最左側列に存在する」という大前提が成立していません。
  
説明では、
 検索キーは「予約番号+連番」に対して、参照範囲の最左列は「予約番号」だけです。
  
 >sheet2のすべての予約番号に対して1〜6の連番を結合させた列を作成
とありますが、
この説明の具体的な例を示してください。
 (でも、Sheet1側に連番がないから・・・意味があるかな?)
  
VLOOKUP関数で、何をどこに取得するのですか?

投稿日時: 19/01/06 15:08:51
投稿者: あきらか

コメント頂きありがとうございます。
わかりづらく大変失礼いたしました。順を追って再度記載させていただきます。
 
 
sheet1『全予約』(宿泊施設の利用履歴)
 
予約番号    利用日    人数
aa123456    20181012     3
aa546213    20180512     1
aa857462    20181110     2
aa123456    20181013     3
aa123456    20181014     3
aa154368    20180110     2
aa442579    20180629     8
.
.
 
 
=COUNTIF($A$2:A2,A2) で重複する予約番号に連番を振る
 
予約番号    連番    利用日    人数
aa123456    1    20181012     3
aa546213    1    20180512     1
aa857462    1    20181110     2
aa123456    2    20181013     3
aa123456    3    20181014     3
aa154368    1    20180110     2
aa442579    1    20180629     8
.
.
 
=A2&B2 予約番号と連番を結合してそれぞれのデータに固有の番号をもたせる
 
予約番号    連番    結合     利用日      人数
aa123456    1    aa1234561     20181012  3
aa546213    1    aa5462131     20180512  1
aa857462    1    aa8574621     20181110  2
aa123456    2    aa1234562     20181013  3
aa123456    3    aa1234563     20181014  3
aa154368    1    aa1543681     20180110  2
aa442579    1    aa4425791     20180629  8
.
.
 
 
 
 
sheet2『割引の対象となる予約番号一覧』
 
予約番号のセルを↓に6回コピペして連番を振る
連番    予約番号
1    aa123456    
2    aa123457    
3    aa588712    
4    aa462138    
5    aa745982    
6    aa442579    
7    aa123456    
.
.
 
 
フィルタで予約番号の昇順に並べ替え
連番    予約番号
1    aa123456
7    aa123456
13    aa123456
19    aa123456
25    aa123456
31    aa123456
2    aa123457
8    aa123457
.
.
 
 
並べ替えた状態で1〜6の連番を振る
連番    予約番号    連番2 =COUNTIF($B$2:B2,B2) ↓
 1    aa123456     1
 7    aa123456     2
13    aa123456     3
19    aa123456     4
25    aa123456     5
31    aa123456     6
 2    aa123457     1
 8    aa123457     2
14    aa123457     3
20    aa123457     4
.
.
 
 
予約番号と連番を結合する
連番    予約番号    連番2     結合 =B2&C2 ↓
 1    aa123456     1      aa1234561
 7    aa123456     2      aa1234562
13    aa123456     3      aa1234563
19    aa123456     4      aa1234564
25    aa123456     5      aa1234565
31    aa123456     6      aa1234566
 2    aa123457     1      aa1234571
 8    aa123457     2      aa1234572
14    aa123457     3      aa1234573
20    aa123457     4      aa1234574
.
.
 
 
sheet2のC列(連番を結合した予約番号)を検索値として
sheet1から
利用日 =VLOOKUP(D2,Sheet1!C1:E8,2,0)
人数  =VLOOKUP(E2,Sheet1!D1:F8,2,0)
...で、該当するものだけ表示できると考えました。
 
連番    予約番号    連番2  結合     抽出 利用日  抽出 人数
 1    aa123456     1   aa1234561    20181012       3
 7    aa123456     2   aa1234562    20181013       3
13    aa123456     3   aa1234563    20181014       3
19    aa123456     4   aa1234564    #N/A         #N/A
25    aa123456     5   aa1234565    #N/A         #N/A
 

回答
投稿日時: 19/01/06 16:08:47
投稿者: んなっと

     A     B   C D   E F G H     I     J   K
1 予約番号  利用日 人数          予約番号  利用日 人数
2 aa123456 20181012   3   TRUE      aa123456 20181012   3
3 aa546213 20180512   1          aa123456 20181013   3
4 aa857462 20181110   2          aa123456 20181014   3
5 aa123456 20181013   3          aa442579 20180629   8
6 aa123456 20181014   3                      
7 aa154368 20180110   2                      
8 aa442579 20180629   8                      
 
1.
E1は空欄
E2
=ISNUMBER(MATCH(A2,Sheet2!A:A,0))
 
2.
 データ
→詳細設定
→指定した範囲
 リスト範囲:$A:$C
 検索条件範囲:$E$1:$E$2
 抽出範囲:$I$1
→OK
 
     I     J   K
1 予約番号  利用日 人数
2 aa123456 20181012   3
3 aa123456 20181013   3
4 aa123456 20181014   3
5 aa442579 20180629   8
 
 
ほかに、
 データ
→テーブルまたは範囲から
として「クエリ」作成
→マージ
などとする方法も。
上の方法と違って、元データに変更があっても「更新」ワンクリックで済みます。
ただし、Sheet2のテーブルは重複削除が必要かもしれません。

回答
投稿日時: 19/01/06 16:24:54
投稿者: WinArrow
投稿者のウェブサイトに移動

数式が少し変?
 
>利用日 =VLOOKUP(D2,Sheet1!C1:E8,2,0)
>人数  =VLOOKUP(E2,Sheet1!D1:F8,2,0)
 
利用日の数式は、まあ、いいと思います(問題あり:下記*1)が、
人数は
=VLOOKUP(D2,Sheet1!C1:F8,3,0)
 ではないかと思います。
 
*1の問題
Sheet1の参照範囲は、絶対参照にしましょう。
 
Sheet2は、無条件に各々6つの検索データをつくっているんですね?
無駄が多いと思いますが、今は・・・案が出てこないので・・・割愛

回答
投稿日時: 19/01/06 17:02:45
投稿者: んなっと

関数だけの方法。
 
     A     B C D E F G H     I     J   K
1 予約番号 利用日 人数         予約番号  利用日 人数
2 aa123456 20181012 3   1      aa123456 20181012   3
3 aa546213 20180512 1   1      aa123456 20181013   3
4 aa857462 20181110 2   1      aa123456 20181014   3
5 aa123456 20181013 3   2      aa442579 20180629   8
6 aa123456 20181014 3   3                  
7 aa154368 20180110 2   3                  
8 aa442579 20180629 8   4                  
 
E2
=E1+ISNUMBER(MATCH(A2,Sheet2!A:A,0))
下方向・↓
I2
=IFERROR(INDEX(A:A,MATCH(ROW(I1),$E:$E,0)),"")
右方向・→下方向・↓

投稿日時: 19/01/06 17:14:27
投稿者: あきらか

>んなっと様
 
コメント頂きありがとうございます。
ご丁寧に説明頂きよく理解出来ました、ありがとうございます…!
 
MATCH関数もISNUMBER関数も、勉強した際には具体的な使用法がピンときていなかったのですが
とても納得できました、、こう使うのですね…勉強になります。
 
いま手元に実際のブックがないので、すぐに結果が得られないのですが
ご提案頂いたクエリ作成の方法も併せて試してみたいと思います。
 
ご教示頂きありがとうございました!

投稿日時: 19/01/06 17:21:58
投稿者: あきらか

>WinArrow様
 
ありがとうございます。
ご指摘通り数式に誤りがありました、、ありがとうございます!
 
>Sheet2は、無条件に各々6つの検索データをつくっているんですね?
>無駄が多いと思いますが、今は・・・案が出てこないので・・・割愛
 
コメント頂いたとおり、無駄が多いと感じたためご相談させて頂きました。。
お考え頂きありがとうございます…mm

投稿日時: 19/01/06 17:35:51
投稿者: あきらか

>んなっと様
 
関数のみの方法もありがとうございます…!
大変勉強になります、本当に奥が深いです…
 
また不明点あれば伺わせて頂ければ幸いです。よろしくお願い致します。

投稿日時: 19/01/06 17:37:19
投稿者: あきらか

ご教示くださったお二方、大変ありがとうございます。
ご丁寧にお教え頂き大変助かりました。
実際のブックで作業したのちクローズさせて頂ければと存じます。

投稿日時: 19/01/12 17:06:48
投稿者: あきらか

お教え頂いた方法で希望の結果が得られました!
いろいろとご教示頂きありがとうございました。