iT邦幫忙

0

需要各位IT前輩指教 excel 找相同的儲存格 並列出對應的值

有一個excel的問題想請教各位前輩
下面兩張圖裡的
購買人需求的價格欄 需要對應到服裝工作表的值
請問公式該如何打呢?
困擾好幾天了
小弟在此領教

https://ithelp.ithome.com.tw/upload/images/20190220/20112826WTPu6tfh5h.jpg

https://ithelp.ithome.com.tw/upload/images/20190220/20112826n7zhShaZw6.jpg

看更多先前的討論...收起先前的討論...
小k iT邦新手 4 級 ‧ 2019-02-20 17:52:43 檢舉
搜尋VLOOKUP
mis339 iT邦新手 2 級 ‧ 2019-02-21 03:13:36 檢舉
把購買人需求的代號改成1到6就好

在D2輸入=Vlookup(B2,'服裝'!A:I,C2+2,FALSE)
然後往下拉就可以了
試試吧
不可能只靠vlookup做到這件事啦,vlookup的【v】意思是【垂直(vertical)】,換句話說vlookup比對的只有垂直欄位(品項部分),那橫向列位(代號部分)的比對就無法解決啦~
順便一提,相較於vlookup的另外一個公式是hlookup,取自【水平(horizontal)】,比對的就是橫向的列位(代號部分)。也有同時運用v和h的方法,但要理解很燒腦,不如直接改資料定義。
感謝各位大神協助
小弟還是excel初學者
以上所提到的公式我都會再嘗試看看的
謝謝

2 個回答

1
張小馬~
iT邦新手 4 級 ‧ 2019-02-21 09:37:45
最佳解答

如下圖:
https://ithelp.ithome.com.tw/upload/images/20190221/20111566vyfcUb5mdF.png

=INDEX(資料,MATCH(J3,品項,0),MATCH(K3,代號,0))

寫完往下拉就可以~

如同公式內容,你必須先定義:資料品項代號

步驟一:定義【資料】
https://ithelp.ithome.com.tw/upload/images/20190221/2011156620ZWRBQZsL.png

步驟二&三:定義【代號】和【品項】
同理~
https://ithelp.ithome.com.tw/upload/images/20190221/201115667Y1Rwsfluh.png
https://ithelp.ithome.com.tw/upload/images/20190221/20111566KP5kP4AIZ2.png

步驟四:確認【頂端列】二次
框這個範圍~
https://ithelp.ithome.com.tw/upload/images/20190221/20111566h0y73yyJSo.png
框這個範圍~
https://ithelp.ithome.com.tw/upload/images/20190221/20111566adKI7gZBzx.png
上面這張圖的預設應該是【最左欄】,改成去勾【頂端列】。

步驟五:下公式,往下拉,完成。
https://ithelp.ithome.com.tw/upload/images/20190221/20111566JlhpKwUbkU.png

能做到這件事的方法很多,
但相較於公式下得亂七八糟也不懂得原理的其他方法,
上面是我認為最好記最好理解,熟練後也最快的方法。

感謝大神協助
您提供的方法非常受用

不過在將最左欄更改成頂端列時
會跳出一個提醒視窗https://ithelp.ithome.com.tw/upload/images/20190221/201128268ASx4Sf6Bu.jpg
選擇"是"後再重新框範圍
會發現又會跑回預設https://ithelp.ithome.com.tw/upload/images/20190221/20112826FGjJzUWWjb.jpg
更改不了
請問這個小問題該如何排除呢
有嘗試過在名稱管理員的地方刪除
但刪除後再重新操作還是會跳出來https://ithelp.ithome.com.tw/upload/images/20190221/20112826vSdQw0mfaZ.jpg/images/emoticon/emoticon06.gif

發問中文章所列的工作表格式是我在練習時自己創建的練習題
套用您說給的公式後有成功解答到問題https://ithelp.ithome.com.tw/upload/images/20190221/20112826t6OTdpKBAM.jpg
但是在實際操作工作的excel會跳出上述問題

刪除掉存完檔重新開,還是一樣的話,看來那份檔案有卡權限,這我就不知道了~/images/emoticon/emoticon17.gif
對了,另外預設會跳回去這個不用擔心,只要公式下完數字跑出來之後,之後再開都會是對的,其實系統背後是已經被調成【頂端列】的概念,只是每次點開來都還是【最左欄】(某種bug的感覺)。

0
mis339
iT邦新手 2 級 ‧ 2019-02-21 15:02:39

資料,為了方便我也是把它設定命名範圍為ITEM
https://ithelp.ithome.com.tw/upload/images/20190221/20000351RwEzOO086H.png

結果,就是用VLOOKUP就行了
https://ithelp.ithome.com.tw/upload/images/20190221/20000351t3cLRF4N1b.png

看更多先前的回應...收起先前的回應...

無意冒犯,vlookup第三個欄位抓的是數字,你這招要能用,必須建立在你橫的那列滿足2個條件:
1.數字
2.遞增
原po的問題明顯是要做【欄列位準確交叉比對】,若要用非正統的招去解,還有很多比你這更偷吃步的招。

正統的做法中,我的A1~A6可以不用照順序,你的1~5必須照順序,而且還不能改成A1~A5,這在實務作業上,列位上面變成還必須有對應的數字才能解,要填答的位置也必須把項目對應成數字才能做。

舉例如果現在不是1~5,而是紅橙黃綠藍,你豈不是得另外標12345,填答處也得再寫個vlookup把12345對出來,才可以用你這招,這豈不是更麻煩嗎?

mis339 iT邦新手 2 級 ‧ 2019-02-21 17:50:29 檢舉

只要有規則,就一定能做出來。
只是想盡量讓它簡單一點。
因為感覺樓主也不是很熟Excel,所以與其讓他有問題,不如讓他簡單化!至少這樣他應該比較好懂。
而且只要一個簡單的VLOOKUP通,要複雜一點也可以用巢狀的VLOOKUP或是改用INDEX。
只要能解決問題就好 ^ ^

公式存在的意義是為了去配合資料,若為了能使用公式而去改動原始資料,不是很本末倒置嗎?
交流一下,我認為這不是一句【能解決問題就好】,下圖也能解決問題啊~ 用眼睛看 XDD
https://ithelp.ithome.com.tw/upload/images/20190221/20111566BtbTtDemjq.png
這樣下最簡單了~

話說回來,你說的簡單其實並不簡單。
所謂【簡單】指的是公式使用上的觀念,而不是那條公式下了多長多短,例如我的方法原理就是,將一份資料範圍比對兩次(品項&代號),兩次都比對到則帶出資料中的交叉位置。
但你要如何說明vlookup這種作法的觀念原理呢?B2+2在資料比對上實際的意思是什麼呢?真要講解起來你就會發現【很難解釋】。

多說幾句,不是針對你,純分享。職場經驗上,確實很常看到這種狀況,當下處理的人覺得反正當下數字看起來對了就好,而不管之後資料的變化是不是還適用,那苦的就是後面接手的人,或是這人的徒弟,只學到了這招而沒學到正統方法,一旦資料狀況變了,就不會應付了。徒弟想理解為什麼要寫B2+2,難道回他說:【BJ4,反正照著這樣下就對了】這樣嗎?

我是認為大家都是資料處理這領域的人,正確的觀念還是要有,要教人當然要教最正確的。

mis339 iT邦新手 2 級 ‧ 2019-02-23 15:23:33 檢舉

在回答問題前…先分享一個影片,什麼叫正宗?
https://youtu.be/Jod2TyUOnXs

我不是資料處理這領域的人,我對Excel也沒有很熟,只會點皮毛,所以覺得只要能解決問題的方法都是好方法!

以前客戶的財務需要比對資料,但他不熟Excel。教他用VBA到INDEX到VLOOKUP…教的時候都會,但實際上手只要條件變,就出錯了!因為觀念不清楚,解釋了半天,他還是一知半解且愈來愈混亂!最後他用的方法是巢狀式IF函數 XD

可能會用Excel的人會覺得怎麼會這樣用,這樣用也太笨了吧!但對他而言,能解決問題且他能理解、能除錯、能修改的方法就是好方法!

鬼扯完了,回到正題。這一題,還是用VLOOKUP解。對於我一開始的解法,高手覺得有兩個問題:
1、顏色變成只能數字,不能自訂!
2、B2+2,為何要加2?
好,那就改一下

資料放在同一頁比較好說明,範例如下:
https://ithelp.ithome.com.tw/upload/images/20190223/20000351hcoAslVZlA.png

=vlookup(I6,ITEM,match(J6,$1:$1),FALSE)

VLOOKUP(搜尋準則,範圍,索引,已排序)
搜尋準則就是衣服或鞋子…等
範圍就是A欄到G欄
索引就是找到後要顯示第幾欄,在這邊用上match函數就可以顯示第幾欄,範圍是從第一列中去尋找,所以你要用數字、中英文都行。

另外,這邊加上一個功能叫驗證資料,可以避免使用者在輸入資料時打錯而出現錯誤!
https://ithelp.ithome.com.tw/upload/images/20190223/20000351z3ljClEMw6.png

我要發表回答

立即登入回答