[發問] 如何自動搜尋某個xls檔內的多個分頁內之數值?

kim595

我目前有兩個xls檔

(1) 查詢表.xls
   B      C      D
料號 │品名│ 價格
1

(輸入正確料號帶出品名規格)

C1的公式為:

=IF(B1=0," ",VLOOKUP(B1,'C:\Documents and Settings\fuj\My Documents\[資料庫.xls]開心食品'!$A$1:$D$65536,2,FALSE))

※紅字的部分只能選一個分頁?


(2) 資料庫.xls (內有分頁 Sheet1,Sheet2,Sheet3,Sheet4 各為不同品牌)

Sheet1 (開心食品)

料號 │品名│ 價格

as01 哈蜜瓜糖果 $15
as02 西瓜甜筒   $50

===================

Sheet2 (東東乾糧)

料號 │品名│ 價格

kksk0123 雜糧餅乾    $75
kksk0124 炸雞洋竽片  $90

===================

因為各廠牌資料庫比數眾多,所以不希望將多個分頁合併成一個大的工作表,
有沒有什麼方法讓我只要輸入對的料號,他就能自動搜尋所有分頁,找出符合條件的欄位資訊?

ANGELA

目前我用的笨方法是增加索引工作表                       
                       
        A                 B                     C
1        as01         開心食品       
2        as02         開心食品       
3        kksk0123 東東乾糧       
4        kksk0124 東東乾糧       
5                       
=IF(B1=0,"",VLOOKUP(B1,INDIRECT("[資料庫.XLS]"&VLOOKUP(B1,[資料庫.xls]索引!$A:$C,2,)&"!A:B"),2,))
工作簿資料庫要開敫才能用INDIRECT函數

kim595

感謝大大回答,
不過我excel不是很強,
可以詳細說明一下嗎?有點看不懂,感恩

ANGELA

[i=s] 本帖最後由 ANGELA 於 2011-1-25 11:36 編輯 [/i]

資料庫工作簿增加一個工作表,名稱定為索引
1  as01            開心食品
2 as02             開心食品
3 kksk0123     東東乾糧
4 kksk0124     東東乾糧

如上 A欄記錄料號    B欄記錄廠牌
把所有的料號及對應的廠牌全打上
以上假設兩個檔案在同一個資料夾中,並同時開敫
C1=IF(B1=0,"",VLOOKUP(B1,INDIRECT("[資料庫.xls]"&VLOOKUP(B1,[資料庫.xls]索引!$A:$C,2,)&"!A:B"),2,))
假設查的是as01
VLOOKUP(B1,[資料庫.xls]索引!$A:$C,2,) 會被替換成 開心食品
假設查的是kksk0124
VLOOKUP(B1,[資料庫.xls]索引!$A:$C,2,) 會被替換成 東東乾糧

kim595

樓上大大的方法對我可能不適用,

1.料號 總筆數超過萬筆,再多增加一個索引工作表將品牌分類可能太繁瑣,且這個表每一年都必須更新一次價格,可能工程太過浩大
2.目前比較希望做到的是key入料號,自動搜索 資料庫xls. 的所有工作表內的資訊,品牌倒是其次

這兩天試過access發現他也只能搜尋單一工作表內資訊,
想請問如果excel的函數無法做到我的需求,那vba可以做到嗎? tks!!

ANGELA

回復 5# kim595


    我說過了他是一個笨方法要增加工作表,是麻煩點,但管用,這公式會挑正確的工作表來做vlookup不用去查每個工作表.上萬種項目應該不是問題,
    產品編號原來就有了copy 一下很快的.

gong

如果料號長短有規則
如
sheet1!b1=as01  >>len(b1)=4
sheet2!b1=kksk0123  >>len(b1)=8
sheet3!b1=aaaa123  >>len(b1)=7
sheet4!b1=ac123f  >>len(b1)=6

公式
1) 查詢表.xls
   B      C      D
料號 │品名│ 價格

(輸入正確料號帶出品名規格)
C1的公式為:
=IF(B1="","",VLOOKUP(B1,indirect("C:\Documents and Settings\fuj\
My Documents\[資料庫.xls]"&index({"","","","開心食品","5碼","6碼","7碼","東東乾糧"},,len(b1))&"!$A:$D"),2,0))

kim595

感謝兩位熱心回答 :D

回版主:
希望可以集結大家的意見做出一個漂亮好用的公式...
料號很冗長也無一定規則,隨意挑個幾個給版主參考~

LBMK7115-ISO
SV1-N4-24VDC
0121A2D
0700K3
14LU316MM
2-RSG-316B
6320G2B
HQC4-D-6M0-316

以上是由不同廠牌隨意挑選幾樣實際的料號

gong

總有個基本規則
如開心食品為"AS"開頭
東東為"KK"開頭

如果完全沒有規則則放在同一個工作表方便處理

Hsieh

回復 3# kim595

跟4#的想法類似
不過只要將所有資料庫工作表名稱列出
將資料庫活頁簿同時開啟

下載 (19.95 KB)

2011-4-9 23:14

多表查詢.rar (10.29 KB)

下載次數: 22

2011-4-9 23:14