Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 掲示板への投稿には会員登録(無料)が必要です。会員登録がまだの方はこちら
  • 掲示板ご利用上のお願い」に反するご記入はご遠慮ください。
  • Q&A掲示板の使い方はこちらをご覧ください
トピックに返信
質問

 
(指定なし : 指定なし)
「Vlookupのような検索を可能とする」について質問されたdisさんへ
投稿日時: 19/04/23 22:25:11
投稿者: xls-hashimoto

Vlookupのような検索を可能とする
https://www.moug.net/faq/viewtopic.php?t=78181&sid=b4b7bd6667c690dae740e2d9653433fc
 

引用:
やはり関数では無理っぽいですか。
 受注番号のみを入力して、別シートから同じ番号だけのデータを抽出といった対応だったんですが、
 範囲が一意でない為、VLOOKUPでは対応できないとまではわかったんですがね。

データの個数にもよりますが、関数でやる方法もあります。
 
Aシート
   A     B    C    D    E
 6 受注番号    担当者    品番    品名    販売数量
 7 12345                
 8 12346                
 9 12345                
10 12346                
11 12347                
12 12345                
13 12346                
14 12347                
15 12345                
16 12346                
17 12347                
18 12345                
19 12346                
20 12347                
21 12345                
22 12346                
23 12347                
24 12345                
25 12346                
26 12347



1048576 12345                
 
Bシート
  A   B    C 
 1    受注番号    12345
 2        
 3        
 4        
 5        
 6        受注番号
 7     7    12345
 8     9    12345
 9    12    12345
10    15    12345
11    18    12345
12    21    12345
13    24    12345
14    1048576    12345
15    -    
16    -    
17    -    
 
Aシートでの存在行を順番に計算させます。
B7
=IF(B6="-","-",IF(ISERROR(MATCH(C$1,OFFSET(Aシート!A$1,B6,,ROWS(C:C)-B6,1),0)),"-",MATCH(C$1,OFFSET(Aシート!A$1,B6,,ROWS(C:C)-B6,1),0)+B6))
計算結果が「-」になるまで下にコピー
 
C7
=IF(B7="-","",INDEX(Aシート!A:A,B7))
B列と同じ数だけ下にコピー
 
これで同じ受注番号が並びます。
1048576行目にあるデータも並んでます。
 
個数を数えるCOUNTIFはスピードが遅くて使い物になりませんが、MATCHなら見つけた最初の行で計算が終わるのである程度のデータ数までは使えます。
 
Bシートは受注番号を並び替えたいとのことなので、この計算を作業シートで行い、そこに並び替えデータも表示し並び替え計算をすると、Bシートに並び替えた受注番号を表示することが出来ます。
 
日々のルーチン業務の中で手作業を省くために普通に使っている手法です。

投稿日時: 19/04/24 06:35:23
投稿者: xls-hashimoto

Excel2013ならIFERRORが使えるので
 
B7
=IF(B6="-","-",IFERROR(MATCH(C$1,OFFSET(Aシート!A$1,B6,,ROWS(C:C)-B6,1),0)+B6,"-"))
計算結果が「-」になるまで下にコピー
 
とします。
 
B7セルでMATCHを使って存在行が見つかったら、次のB8セルでの検索範囲は
OFFSET(Aシート!A$1,B7,,ROWS(C:C)-B7,1),0)
となります。そこで見つかったらそれにB7セルの値を足して
OFFSET(Aシート!A$1,B7,,ROWS(C:C)-B7,1),0)+B7
が2つ目の存在行となります。
以降この計算を繰り返し、見つからなくなると「-」が表示されコピーは必要なくなります。

回答
投稿日時: 19/04/26 10:09:17
投稿者: dis

あれから別回答していただいていたのですね。
 
今直ぐに検証確認できない為、今はお礼のみとさせていただきます。
ありがとうございました。

トピックに返信