エクセルでこんなジャンプ出来ますか

質問

Sheet2
　　　A　　　　 B　　　C　　D　　　　E　　　F　　　G　　　 　H
3　　no.　　　氏名　　　　 no.　　　氏名　　　　　no.　　　氏名
4　100001　山本A　　　102001　　　　　　　　104001　伊藤F
5　100002　　　　　　　　102002　佐藤D　　　104002　
6　100003　鈴木B　　　102003　　　　　　　　104003　中村G
7　100004　高橋C　　　102004　山田E　　　104004
：　　　：　　　　：　　　　　　　：　　　　 ：　　　　　　　：　　　　 ：

Sheet1
　　　A　　　　 B　　　　C　　
3　　no.　　　氏名　　回数　　
4　104003　中村G　　 3
5　100004　高橋C　　 5
6　102003
：　　　：　　　　：　　　　：
Sheet2の表を基にSheet1のような表を作成したい。
Sheet2には約２万件の番号が登録されています。
Sheet1のA列のno.は番号順に入力されるわけではない。

Sheet1のA列にno.を入力するとSheet2より該当の氏名をB列に表示、
該当氏名が空白の場合は、Sheet2の該当セルにジャンプし、氏名入力後Sheet1のC列にジャンプして戻る。

また、Sheet2において例えば、B1に104001を入力するとH4にジャンプしEnterを押すとB1に戻り、100002を入力するとB5にジャンプ氏名入力後Enterを押しB1に戻る。・・・ということもしたいのですがマクロではなく関数で出来るでしょうか？

回答No.4

某HN クロメート(Chromate)（@CoalTar）

＃2です

MATCH(1*MID(A4,3,1),{0,2,4})
A,D,G列の違いは左から3番目の数字 MID(A4,3,1)
が違うので、どの列に属するかを判断した。
数字だと{0,2,4}にヒットしないので、数値にするために1*
0,2,4→A,D,G列としたい。
しかし、MATCH関数から返る値が1,2,3なので、A列は1列、D列は4列、G列は7列
1,4,7と返ってほしい。
1,2,3を1,4,7にするためには *3-2

もちろん、そのような判断せずにmt2008さんのように
A列で無いならD列を検索。D列で無いなら、G列検索とする方法も考えられる

ちなみに「回数」の説明が全くありませんでしたので手入力したと判断しています

回答No.3

imogasi

ＶＢＡが経験がないからといって、ＶＢＡ向きの問題を、複雑な関数で回答者に答えさせて何の意味がある？
エクセルの関数も含めて、適当な（エクセルが得意な）分野とか、関数が使いやすいように表を設計するものだ。
名前を取ってくるのも３列の中からしいが、１列にしたものを手作業で作るとかしないと、回答者が書いている数式を理解する時間より遙かに早くできるだろう。
回答者を振り回してｂ、苦労させるより、問題を限定でもして、質問者がＶＢＡを勉強したら。
エクセルの関数に、又何でもエクセルに押し付けないこと。
＞ジャンプしEnterを押・・
ジャンプなんて動作は関数で実現しない。限定された場合でＨＹＰＥＲＬＩＮＬが使えるかな。
それより該当をＦｉｎｄしてＡｃｔｉｖａｔｅするボタンでも作ったらと思う。

回答No.2

某HN クロメート(Chromate)（@CoalTar）

Sheet2!B4セルに
=HYPERLINK("#"&ADDRESS(MATCH(A4,CHOOSE(MATCH(1*MID(A4,3,1),{0,2,4}),Sheet2!A:A,Sheet2!D:D,Sheet2!G:G),0),
MATCH(1*MID(A4,3,1),{0,2,4})*3-1,,,"Sheet2"),
VLOOKUP(A4,CHOOSE(MATCH(1*MID(A4,3,1),{0,2,4}),Sheet2!A:B,Sheet2!D:E,Sheet2!G:H),2,FALSE)&"")
下へオートフィル

必要に応じて 書式 － スタイル [ハイパーリンク] [削除]

B6セルをクリック データを入力後 [Alt]+[←]

数式ではこのあたりが限界と思われる

また、データベース(リスト)として使うならSheet2は複数の列に分けないこと
その分け方も明確になっていないので、適当に判断した。

お礼

回答ありがとうございます。
少し、質問があります。
最初の”＃”＆部分また、行、列番号を求める箇所におけるMATCH関数｛0,2,4｝とは？なぜ*1？また、*3-1とは何でしょうか？

回答No.1

mt2008

関数でも無理やりやれば近い物は出来ます。

Sheet1のB4に
=IF(COUNTIF(Sheet2!A:A,A4)>0,VLOOKUP(A4,Sheet2!A:B,2,FALSE),IF(COUNTIF(Sheet2!D:D,A4)>0,VLOOKUP(A4,Sheet2!D:E,2,FALSE),IF(COUNTIF(Sheet2!G:G,A4)>0,VLOOKUP(A4,Sheet2!G:H,2,FALSE),"")))

C4に
=COUNTIF(Sheet2!A:A,A4)+COUNTIF(Sheet2!D:D,A4)+COUNTIF(Sheet2!G:G,A4)

D4に
=IF((C4>0) *(B4=0),HYPERLINK("#Sheet2!"&IF(COUNTIF(Sheet2!A:A,A4)>0,"B"&MATCH(A4,Sheet2!A:A,0),IF(COUNTIF(Sheet2!D:D,A4)>0,"E"&MATCH(A4,Sheet2!D:D,0),"H"&MATCH(A4,Sheet2!G:G,0))),"LINK"),"")

と、入れてB4:D4を下にコピーします。
さらに、B列の書式をユーザ書式で「#;-#;[白]#」と設定します。
＃氏名無しの場合、B列に0と表示されても良いならユーザ書式の設定不要

これで、A列の番号に合った氏名と登場回数が表示され、さらにNo.はあるけど氏名が登録されて居ない場合は、D列に「LINK」と表示されます。
このLINKをクリックするとSheet2の該当箇所（の、一番最初に見つかった所）にジャンプします。
Sheet1に戻るのは手動です。

でも、こんな長ったらしい式はメンテナンスがやっかいですので実用に向かないと思います。
やりたい事や元データを整理してもっとシンプルにしましょう。

お礼

回答ありがとうございます。
そうですね。もっとシンプルにできないか考えてみます。