[[20120801153701]]　『HYPERLINK等で別シート全体を検索し目的の場所へ�x（324）　ページの最後に飛ぶ

『HYPERLINK等で別シート全体を検索し目的の場所へ移動する』（324）

 Excel2003,Windows7

 HYPERLINKで可能か分かりませんが、よろしくお願いします。

 <Sheet1>
 Sheet1の至る所に漢字と数字の混ざった文字列が分布

 <Sheet2>
 B列に漢字と数字の混ざった文字列が入っている

 この状態で例えば、Sheet2のE列の1行目をクリックしたら、
 Sheet2のB列1行目のセルの文字列と同じセルをSheet1から探して、
 そのセルを選択した状態にしたいです。
 今はE列に =HYPERLINK("＃Sheet1!A1","aaa")
 というようにSheet1の対象セルを自分で指定していますが、
 数が多いのと、Sheet1に入っているデータを
 同じシート内の別のセルへ移動することがあるからです。

 また逆にSheet1の文字列の例えば横のセル(場所は任意でいいです)を
 クリックするとSheet2のB列を検索して同じ文字列が入っている行の
 A列を選択した状態にしたいです。

 Sheet2のE列に入れる関数と
 Sheet1の文字列の横のセルに入れる関数を教えて下さい。

 判りづらい点がありましたら、追記致します。
 よろしくお願いします。

 =HYPERLINK("#Sheet1!"&ADDRESS(MATCH(B1,Sheet1!A:A,0),1),"aaa")

 =HYPERLINK("#Sheet2!"&ADDRESS(MATCH(A1,Sheet2!B:B,0),1),"aaa")

 (GobGob)

 お返事遅くなりました
 GobGobさんありがとうございます。

 2番目のSheet1からSheet2へのリンクはうまくいきましたが、
 1番目のSheet2からSheet1へのリンクがうまくいきませんでした。

 説明が判りにくくて申し訳ありません。
 Sheet1の至る所に文字列が分布というのはA列だけに分布しているのではなく、
 Sheet全体(又はA～Z列まで等の制限も可)です。
 従って、以下のようにA:AをA:Zと置き換えて実行してみましたが#N/Aエラーが出ます。
 =HYPERLINK("#Sheet1!"&ADDRESS(MATCH(B1,Sheet1!A:Z,0),1),"aaa")
                                                     ↑
 また、↑の"1"は列指定だと思いますが、
 こちらも検索して一致した場所の列を指定したいです。

 ご無理を言って申し訳ありませんが、お知恵をお貸し下さい。

 (324)

 =HYPERLINK("#Sheet1!"&ADDRESS(SUMPRODUCT((Sheet1!A1:Z100=B1)*ROW(A1:Z100)),SUMPRODUCT((Sheet1!A1:Z100=B1)*COLUMN(A1:Z100))),"aaa")

 ※範囲に検索値ダブリなきこと。
 ※範囲 A1:Z100で設定。

 (GobGob)

 GobGob様、ありがとうございます。
 無事できました。

 が、式のSUMPRODUCTの中身が理解できていません。
 SUMPRODUCT((Sheet1!A1:Z100=B1)*ROW(A1:Z100))
 ヘルプを見ると、配列の引数は2～30個で指定可と書いてありますが、
 こちらの式は配列1つ？でしょうか。
 A1からZ100までの配列？にどのような操作をしているかが、
 分からない状況です。

 よろしければ、勉強のために教えて頂けないでしょうか。

 (324)

 ＞(Sheet1!A1:Z100=B1)*ROW(A1:Z100)

 ①(Sheet1!A1:Z100=B1) → Sheet1のA1:Z100の範囲を見て
                          B1と等しいデータがあればTRUE。でなければFALSE
 ②ROW(A1:Z100) → ROW関数で1～100の連続データ（配列）を作成
 ③ ①と②を掛け算 (Excel数式では TRUE→1、FALSE→0で処理）

 ・・・配列の結果はB1と等しいところだけが行番号として残り、その他は0となる。
       これを合計すれば欲しい行番号が返ってくる。

 ってな処理です。

 んで合計処理にはSUMPRODUCTを使って処理。

 (Sheet1!A1:Z100=B1)*ROW(A1:Z100) の計算結果 → 配列。
 SUMPRODUCTの引数が1つしかないので、その配列だけを合計。

 って意味ですな。

 SUMPRODUCT的（？・・・配列×配列の要素間の積を配列にし、最後に足し算。）な表記にするなら

 SUMPRODUCT((Sheet1!A1:Z100=B1)*1,ROW(A1:Z100))
 →(Sheet1!A1:Z100=B1)*1 の配列と ROW(A1:Z100)の配列 、引数2つ。
    *1するのはデータを論理値から数値化するため)

 ※別にSUM関数でもいいんだけど CTRL+SHIFT+ENTER数式確定しなければいけないんで
   敢えてSUMPRODUCT使ってます。

 (GobGob)

 GobGob様

 お返事遅くなりました。
 丁寧なご説明ありがとうございました。
 こういう使い方もあるのかと非常に勉強になりました。
 ありがとうございます！

 (324)

コメント返信：

[ 一覧(最新更新順) ]

[[20120801153701]] 『HYPERLINK等で別シート全体を検索し目的の場所へ�x（324） ページの最後に飛ぶ

[[20120801153701]]　『HYPERLINK等で別シート全体を検索し目的の場所へ�x（324）　ページの最後に飛ぶ