close

4002

Excel如何從表格查找資料()

如下圖的圖A中A欄~L欄是各個型號、單價、未稅價格表格,這種設計方式一般我們是不建議的,因為這在查找資料會變的複雜了,而會建議建成如圖B的表格。

image 圖A

image 圖B

但若公司其他同事傳來的表格就是圖A這樣,有什麼方法可以在O欄及P欄查詢到N欄型號的單價及未稅價格呢?

1.點取O2儲存格輸入公式「=OFFSET($A$1,MIN(IF($A$1:$L$4=N2,ROW($1:$4)))-1,MIN(IF ($A$1:$L$4=N2,COLUMN($A$1:$L$1))),1,1)」後,向下及向右複製到O2:P4儲存格。

image

2.點取P2儲存格輸入公式「=OFFSET($A$1,MIN(IF($A$1:$L$4=N2,ROW($1:$4)))-1,MIN(IF ($A$1:$L$4=N2,COLUMN($A$1:$L$1)))+1,1,1)」後,向下及向右複製到P3:P4儲存格。

image

公式說明

=OFFSET($A$1,MIN(IF($A$1:$L$4=N2,ROW($1:$4)))-1,MIN(IF($A$1:$L$4=N2,COLUMN($A$1: $L$1))),1,1)

公式一:COLUMN($A$1:$L$1) 傳回A欄到F欄的欄編號。

公式二:IF($A$1:$L$4=$N2,公式一) 如果A1:L4儲存格範圍中有符合N2儲存格內容的則傳回其欄編號。

公式三:MIN(公式二) 抓取公式二傳回結果中最小值,為該型號單價位於的欄。

image

公式四:ROW($1:$4) 傳回1~4列的列編號。

公式五:IF($A$1:$L$4=N2,公式四) 如果A1:L4儲存格範圍中有符合N2儲存格內容的則傳回其欄編號。

image

公式六:MIN(公式五) 抓取公式五傳回結果中最小值,為該型號單價位於的列。

公式二:OFFSET($A$1,公式六-1,公式三,1,1) 從A1儲存格移動N列減一(公式六結果),N欄(公式二結果)後,交叉的儲存格。

OFFSET(起始位置,移動列數,移動欄數,框選列數,框選欄數)

幫助~~有什麼問題歡迎留言,我會儘量及時的給大家答覆~~

更多相關影片教學:請點我

更多相關文章:請點我


arrow
arrow
    文章標籤
    IF OFFSET ROW MIN COLUMN
    全站熱搜
    創作者介紹

    錦子老師 發表在 痞客邦 留言(0) 人氣()