帖子: 36
主題: 16
精華: 0
積分: 134
點名: 0
作業系統: Xp
軟體版本: office 2007
閱讀權限: 20
性別: 女
註冊時間: 2011-1-7
最後登錄: 2019-1-6

1^# 跳轉到 » 倒序看帖

字體大小: tT

發表於 2011-1-19 11:22 | 只看該作者

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

我目前有兩個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

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 812
主題: 7
精華: 0
積分: 862
點名: 0
作業系統: XP
軟體版本: 2007
閱讀權限: 100
性別: 男
來自: 台中
註冊時間: 2010-5-13
最後登錄: 2019-10-10

論壇貢獻勳章

2^#

發表於 2011-1-19 14:35 | 只看該作者

本帖最後由 ANGELA 於 2011-1-25 12:30 編輯

目前我用的笨方法是增加索引工作表

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函數

TOP

kim595

中學生 Rank: 2

帖子: 36
主題: 16
精華: 0
積分: 134
點名: 0
作業系統: Xp
軟體版本: office 2007
閱讀權限: 20
性別: 女
註冊時間: 2011-1-7
最後登錄: 2019-1-6

3^#

發表於 2011-1-21 14:33 | 只看該作者

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

TOP

ANGELA

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 812
主題: 7
精華: 0
積分: 862
點名: 0
作業系統: XP
軟體版本: 2007
閱讀權限: 100
性別: 男
來自: 台中
註冊時間: 2010-5-13
最後登錄: 2019-10-10

論壇貢獻勳章

4^#

發表於 2011-1-23 12:00 | 只看該作者

[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,) 會被替換成東東乾糧

TOP

kim595

中學生 Rank: 2

帖子: 36
主題: 16
精華: 0
積分: 134
點名: 0
作業系統: Xp
軟體版本: office 2007
閱讀權限: 20
性別: 女
註冊時間: 2011-1-7
最後登錄: 2019-1-6

5^#

發表於 2011-1-25 10:26 | 只看該作者

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

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

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

TOP

ANGELA

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 812
主題: 7
精華: 0
積分: 862
點名: 0
作業系統: XP
軟體版本: 2007
閱讀權限: 100
性別: 男
來自: 台中
註冊時間: 2010-5-13
最後登錄: 2019-10-10

論壇貢獻勳章

6^#

發表於 2011-1-25 10:57 | 只看該作者

回復 5# kim595

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

TOP

gong

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 455
主題: 8
精華: 1
積分: 480
點名: 0
作業系統: WINS home
軟體版本: OFFICE XP
閱讀權限: 100
性別: 男
來自: 平鎮
註冊時間: 2010-5-1
最後登錄: 2015-1-22

論壇貢獻勳章

7^#

發表於 2011-1-25 14:45 | 只看該作者

本帖最後由 gong 於 2011-1-25 14:48 編輯

如果料號長短有規則
如
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))

知之為知之，不知為不知，誠實也！

TOP

kim595

中學生 Rank: 2

帖子: 36
主題: 16
精華: 0
積分: 134
點名: 0
作業系統: Xp
軟體版本: office 2007
閱讀權限: 20
性別: 女
註冊時間: 2011-1-7
最後登錄: 2019-1-6

8^#

發表於 2011-1-25 16:40 | 只看該作者

感謝兩位熱心回答 :D

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

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

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

TOP

gong

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 455
主題: 8
精華: 1
積分: 480
點名: 0
作業系統: WINS home
軟體版本: OFFICE XP
閱讀權限: 100
性別: 男
來自: 平鎮
註冊時間: 2010-5-1
最後登錄: 2015-1-22

論壇貢獻勳章

9^#

發表於 2011-1-27 11:09 | 只看該作者

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

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

知之為知之，不知為不知，誠實也！

TOP

Hsieh

超級版主 Rank: 8 Rank: 8

帖子: 4901
主題: 44
精華: 24
積分: 4916
點名: 255
作業系統: Windows 7
軟體版本: Office 20xx
閱讀權限: 150
性別: 男
來自: 台北
註冊時間: 2010-4-30
最後登錄: 2024-11-24

2023年全勤獎章 2022虎年全勤獎章 2021牛年全勤獎章 2020鼠年全勤獎章 2019 豬年全勤獎章 2018 狗年全勤獎章 2017 雞年全勤獎章 2016猴年全勤獎章 2015羊年全勤獎章 2014馬年全勤獎章 2013蛇年全勤獎章 2012龍年全勤獎章論壇貢獻勳章熱心公益勳章 2011兔年全勤獎章