excel中,如何搜尋一個範圍內的值後,再把符合值的列欄..

Question

excel 工作表如下：
................名稱1.....名稱2.....名稱3 ...........名稱N
位置1......數值D....................數值G
位置2.....................數值D.....數值H............數值X
位置3.......數值Y....數值Y.....數值Y
....
位置N.....................數值D.......數值P............數值X

請問：
如何使用搜尋，當我輸入"數值D"時，則自動列出數值D的名稱與位置:
數值D:
1. 名稱1...位置1
2. 名稱2...位置2
3. 名稱2...位置N
輸入"數值X":
1. 名稱N...位置2
2. 名稱N...位置N

(名稱)可能高達100個，而(位置)可能高達2000個，當我輸入數值時，可以把對應的”名稱”及”位置”分別列出來。
附註：我的資料庫已完成（名稱與位置皆固定，但數值是隨時會變的），且資料量多，請大大們儘可能依現在的排列方式來為我解答，謝謝！（或是改變格式？）

再次請教，我以前有GWBasic & C 的經驗，知道迴圈的概念，但這個程式修正後一直無法執行，是否要分行或那裡有誤呢？
謝謝您！

已經出現雛形了，但數值裡如有 1, 12, 16, 21, 51....等有1的數值也會一起列出，應該需修改那裡呢？

知識的寄信功能，好像不能附加檔案？
問題點主要在，如果找"1"的話，數值內有"1"的會全部列出，如果找"4"的話，那數值內有"4"的也會全部列出...
如果直接使用函數功能，您建議使用那些函數呢？謝謝您

請問 Applerot 大大:
您在名稱->定義中的＂名稱＂公式後面好像有點錯誤，是否應改為
=OFFSET(............COUNTA(Sheet1!$1:$1)　才對？

請問所搜尋到的值(Sheet2!A2 & B2)要使用下拉式功能，複製到更多的儲存格，還是需使用圈選儲存格，按F2，按 ctrl+Shift+Enter 的陣列公式呢?
我試了第一種才能達到要求，但其判斷式中的部份儲存格也跟著變換？？

Answer 1

陣列公式可解決問題,只是資料太多運算起來會很慢.

2006-09-29 20:42:24 補充：
Sheet1!　　Ａ　　Ｂ　　　Ｃ　　　Ｄ　　　Ｅ　１ 　　　名稱１　名稱２　名稱３　名稱Ｎ２位置１　數值Ｄ　　　　　數值G３位置２　　　　　數值Ｄ　數值H　數值X４位置３　數值Ｙ　數值Ｙ　數值Ｙ５位置Ｎ　　　　　數值Ｄ　數值P　數值X Sheet2!　　Ａ　　Ｂ　　１數值D２名稱1　位置1３名稱2　位置2４名稱2　位置N〔插入〕〔名稱〕〔定義〕現有名稱輸入　資料　參照到(R)=OFFSET(Sheet1!$A$1,1,1,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))〔插入〕〔名稱〕〔定義〕現有名稱輸入　名稱　參照到(R)=OFFSET(Sheet1!$B$1,,,,COUNTA(Sheet1!$A$1:$F$1))〔插入〕〔名稱〕〔定義〕現有名稱輸入　位置　參照到(R)=OFFSET(Sheet1!$A$2,,,COUNTA(Sheet1!$A:$A))Sheet2!A1為輸入的儲存格Sheet2!A2=IF(COUNTIF(資料,$A$1)>=ROW(Sheet1!A1),INDEX(Sheet1!$1:$1,SMALL(IF(資料=$A$1,COLUMN(名稱),""),ROW(A1))),"")Sheet2!B2=IF(COUNTIF(資料,$A$1)>=ROW(Sheet1!A1),INDEX(Sheet1!$A:$A,SMALL(IF(資料=$A$1,ROW(位置),""),ROW(Sheet1!A1))),"")A2、B2為陣列公式輸入完公式不以ENTER來完成,而是以Ctrl+Shift+Enter來完成公式你可從資料編輯列看到以下型式{=IF(COUNTIF(資料,$A$1)>=ROW(Sheet1!A1),INDEX(Sheet1!$A:$A,SMALL(IF(資料=$A$1,ROW(位置),""),ROW(Sheet1!A1))),"")}OFFSET()可自動計算儲存格欄列數量如你的檔案欄列位子同上表格,不管多少欄列,都可搜尋到Sheet2!A1的資料

2006-09-30 22:36:37 補充：
先選A2再按F2，按 ctrl+Shift+Enter 再選B2再按F2，按 ctrl+Shift+Enter 後再下拉即可

2006-09-30 22:44:37 補充：
ROW()要隨列而變沒錯,如此才知第幾個SMALL定義中的＂名稱＂如你說的是我沒改到

Answer 2

假設您的資料位置(含位置及名稱)是A1~DY2000
建議您先在A欄插入一欄.
那您的資料位置就變成是B1~DZ2000
在A2輸入您要查詢的數值
建立一巨集按鈕:
檢視/工具/表單->選長方形按鈕
在A1的位置拖曳產生一按鈕 ->名稱改為TEST ->按新增
再將以下程式內容COPY入.
Sub TEST()
N = Range("A2")
M = 3
With Range("C2:DZ2000")
Set c = .Find(N, LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
R1 = c.Row()
C1 = c.Column()
Range("A" & M) = Cells(1, C1) & "/" & Range("B" & R1)
Set c = .FindNext(c)
M = M + 1
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
End Sub

**注意
1.以上程式是設定根據輸入在A2的值來尋找.並將位置列在A3~A...
2.以上程式中"C2:DZ2000"是指資料不含名稱及位置的部份,
請自行依你的資料內容做修正.
3.以上程式中N = Range("A2")中的A2即為你所說的輸入"數值D"的位置.
4.以上程式中M = 3,表示從A3開始顯示"數值D"的所在的位置.
請依您的資料做修正.

以上試試,有問題再討論!!

2006-09-28 23:56:05 補充：
我試過,不會像你說的一樣數值裡如有 1, 12, 16, 21, 51....等有1的數值也會一起列出,不知道問題在那裡?我的版本是 office2003.還是mail資料給我幫你看看?

2006-09-29 00:02:13 補充：
在第一行加入Range("a3:a10000").ClearContents如下:Sub TEST()Range("a3:a10000").ClearContentsN = Range("A2")......

2006-09-29 13:44:13 補充：
是不是你的資料內容不是數值,而是文字格式?試試將整個工作表以值的方式copy到另一個工作表先選取整個範圍/複製到新工作表A1編輯 /選擇性貼上 / 勾[值]那一格 /確定用函數很困難,還想不到可用的.