エクセルでの表内の検索について

質問

エクセルで下記のような2つの表があり、表2の「表1の数字」の部分の数字が表1のどこにあるかを自動的に検索し、その数字のところを色分けもしくは、表2のNoとリンクさせるためにはどのように
したらよろしいでしょうか。

-表1-
1 2 3 4 5 6 …10
11 12 ……20
21…………30
……………
…………
491…………500

-表2-
No. 表1の数字 内容
1 100 *****
2 250 ++++++

回答No.8

KURUMITO · ベストアンサー

回答No3,7です。その後いかがでしたか？
追加ご質問の件ですが表1がシート1に、表2がシート2にあるとしてシート1のA1セルには次の式を入力してF1セルまでオートフィルドラッグしたのちに17行目まで下方にオートフィルドラッグします。
これでシート2のB列で指定したセルにA列の値が表示されます。

=IF(COUNTIF(Sheet2!$B:$B,IF(MOD(COLUMN(A1),2)=1,17*(7-COLUMN(A1))-ROW(A1)+1,IF(MOD(COLUMN(A1),2)=0,17*(7-COLUMN(A1))-17+ROW(A1),0)))=0,"",INDEX(Sheet2!$A:$A,MATCH(IF(MOD(COLUMN(A1),2)=1,17*(7-COLUMN(A1))-ROW(A1)+1,IF(MOD(COLUMN(A1),2)=0,17*(7-COLUMN(A1))-17+ROW(A1),0)),Sheet2!$B:$B,0)))

色付けするためにはシート1のA1セルからF17セルの範囲を選択したのちに「条件付き書式の設定」で数式の窓には次の式を入力してから書式で塗りつぶしの色を指定します。

=COUNTIF(Sheet2!$B:$B,IF(MOD(COLUMN(A1),2)=1,17*(7-COLUMN(A1))-ROW(A1)+1,IF(MOD(COLUMN(A1),2)=0,17*(7-COLUMN(A1))-17+ROW(A1),0)))>0

お礼

回答ありがとうございます。その後いろいろと調べていましたがわかりませんでした。最後にもうひとつ質問があります。表１の値が増えた場合はオートドラックで表２を増やしていけばよろしいでしょうか？

回答No.7

KURUMITO

回答No3です。一定の規則を持って並んでいる数値ですから関数を使って対応することは可能でしょう。しかし実用上はあまり例のないことですね。エクセルの練習問題を解くような話でしたらご自分で解決策を勉強されてはどうでしょう。

お礼

回答ありがとうございます。エクセルの練習問題というわけではないのですが、解決策を勉強してみます。

回答No.6

kmetu

もし、先の私の回答がしたいことだとしたら

表１が質問の状態で10単位で行が増えてるのでしたら

=HYPERLINK("#表2!A" & MATCH(COLUMN(A1)+((ROW(A1)-1)*10),表2!$A:$A,0),COLUMN(A1)+((ROW(A1)-1)*10))

としてフィルしてください。

回答No.5

kmetu

> 表2のNoとリンクさせるためにはどのように

=HYPERLINK("#表2!A" & MATCH(COLUMN(A1),表2!$A:$A,0),COLUMN(A1))

たとえば表1の一列目だけ考えたとしてこういうことでしょうか？
10の列までフィルしたら10までリンクします。

回答No.4

MackyNo1

>表2の「表1の数字」の部分の数字が表1のどこにあるかを自動的に検索し、その数字のところを色分けもしくは、表2のNoとリンクさせるためにはどのようにしたらよろしいでしょうか

表２に入力した数字だけ、表１の数字のセルの背景を赤くして表示したいという意味なら、以下のような条件付き書式を利用します。

まず準備として、表２の数字入力範囲を選択して、画面左上の名前ボックス（A1などと選択したセル番地が表示されている部分）に、例えば「データ範囲」と入力しEnter２回で確定し、その範囲に名前を付けておきます。

次に、表１の数字データ範囲を選択し（A1セルから選択した場合）、「書式」「条件付き書式」で「数式が」にして（Excel2007以降ならホームタブの条件付き書式、「新しいルール」「数式を使用して・・・」を選択）、「=COUNTIF(データ範囲,A1)」と入力して「書式」ボタンからセルの塗りつぶしの色を赤などに設定します。

これで表にに入力した数字は、表１で赤く表示されます。

ところで、「あるいは表２のNoとリンクさせる」とは具体的にどのようなことがしたいのでしょうか？

＃エクセルなどのOfficeソフトはバージョンによって使用できる機能や操作方法が大きく異なりますので、質問の際には必ずバージョンを明記するようにしましょう。

回答No.3

KURUMITO

表1がシート1のＡ1セルからＪ50セルの範囲にあるとします。
また、表2はシート2にあってＡ1セルから下方には番号がＢ1セルから下方には100とか250とかの数値を入力するとします。そこでシート2のＤ列には作業列を設けＤ1セルには次の式を入力して下方にオートフィルドラッグします。

=IF(B1="","",ROUNDUP(B1/10,0)&"/"&MOD(B1-1,10)+1)

その後にシート1に戻ってＡ1セルには次の式を入力し、Ｊ1セルまで横にオートフィルドラッグしたのちに50行まで下方にオートフィルドラッグします。

=IF(COUNTIF(Sheet7!$D:$D,ROW(A1)&"/"&COLUMN(A1))>0,INDEX(Sheet7!$A:$A,MATCH(ROW(A1)&"/"&COLUMN(A1),Sheet7!$D:$D,0)),"")

これでシート2のＢ列で指定された場所にシート2のＡ列での番号が表示されます。
なお、Ｂ列で指定されたセルに色を付けるとしたら次のようにします。
初めにシート1のＡ1セルからＪ50セルまでを範囲として選択します。
その後にエクセル2010でしたら「ホーム」タブから「条件付き書式」を選択し、「新しいルール」をクリックします。
表示の画面で「数式を使用して…」を選択して、数式の窓には次の式を入力します。

=COUNTIF(Sheet2!$D:$D,ROW(A1)&"/"&COLUMN(A1))>0

同じ画面の「書式」で「塗りつぶし」のタブから好みの色を指定してＯＫします。

補足

回答ありがとうございます。解決できました。
表1が下記のような数字の配列の場合でも=IF(B1="","",ROUNDUP(B1/10,0)&"/"&MOD(B1-1,10)+1)を変えてやればできるのでしょうか。

102 69 68 35 34 1
101 70 67 36 33 2
100 71 66 37 32 3
99 72 65 38 31 4
98 73 64 39 30 5
97 74 63 40 29 6
96 75 62 41 28 7
95 76 61 42 27 8
94 77 60 43 26 9
93 78 59 44 25 10
92 79 58 45 24 11
91 80 57 46 23 12
90 81 56 47 22 13
89 82 55 48 21 14
88 83 54 49 20 15
87 84 53 50 19 16
86 85 52 51 18 17

あまりエクセルの関数とかを使った式を使わないのでわかりません。
申し訳ありませんが、よろしくお願いします。

回答No.2

imogasi

>表2の「表1の数字」の部分の数字が表1のどこにあるかを
基本的にエクセル関数では、VLOOKUP関数（FALSE型)やMATCH関数しかないことをご存知か？
これらの関数を見てみると検索される範囲を１列しか指定できない。（HLOOKUPの場合は１行）
だから表１で検索するする列が多数列にあるようだが、そうだと使えない。
胃列になっておれば存在を調べることは出来る。
これをやるなら
検索操作か
VBAを勉強すること。マクロの記録を取ればコードの骨格はわかり、修正すれば良いのだが
初めての者には難しいだろう。
＞表2のNoとリンクさせるためにはどのように
リンクという言葉を初心者はよく使うが、どういう意味なのか？

回答No.1

SakuraiMisato

VLOOKUP関数のことでしょうか？