→ windknife18:恭喜 ^_^ 07/28 21:50
→ windknife18:Offset, indirect 是屬於 volatile 函數，也就是只要 07/28 22:05
→ windknife18:WB任何一個地方有改變，公式就會重算，所以當你資料 07/28 22:06
→ windknife18:很大的時候，速度上就會變慢，以前我也非常喜歡用 07/28 22:07
→ windknife18:現在則會盡量減少使用，提供給你參考！ 07/28 22:07
※ 引述《windknife18 (windknife18)》之銘言：
: 看看寫出來的和我是不是一樣 ^_^
: Sheet2!B2
: =INDEX(Sheet1!$A$1:$E$100,MATCH($A2,Sheet1!$A$1:$A$10,0),
: MATCH(B$1,INDIRECT("sheet1!" & MATCH($A2,Sheet1!$A$1:$A$10,0) & ":" &
: MATCH($A2,Sheet1!$A$1:$A$10,0)),0)+1)
嗯 差不多 不過我是用offset寫 所以沒有範圍限定的問題(好像INDEX也可以做到)
=IF(COUNTIF(INDIRECT("食譜!"&MATCH($F4,食譜!$A:$A,0)&":"&
MATCH($F4,食譜!$A:$A,0)),G$3)=0,"",COUNTIF(INDIRECT("食譜!"&
MATCH($F4,食譜!$A:$A,0)&":"&MATCH($F4,食譜!$A:$A,0)),G$3)*
OFFSET(食譜!$A$1,MATCH($F4,食譜!$A:$A,0)-1,MATCH(G$3,(INDIRECT
("食譜!"&MATCH($F4,食譜!$A:$A,0)&":"&MATCH($F4,食譜!$A:$A,0))),0)))
有點長 不過是把兩個資料合併起來後又美化過
(資料開端是F4 E和3是標題行)
IF這段是美化用的
=IF(COUNTIF(INDIRECT("食譜!"&MATCH($F4,食譜!$A:$A,0)&":"&
MATCH($F4,食譜!$A:$A,0)),G$3)=0
IF這段是用來檢驗要找的料理中有沒有該項原料
如果沒有會跑出0，這樣就可以利用他讓格子留空
COUNTIF(INDIRECT("食譜!"&MATCH($F4,食譜!$A:$A,0)&":"&
MATCH($F4,食譜!$A:$A,0)),G$3)*
OFFSET(食譜!$A$1,MATCH($F4,食譜!$A:$A,0)-1,MATCH(G$3,(INDIRECT
("食譜!"&MATCH($F4,食譜!$A:$A,0)&":"&MATCH($F4,食譜!$A:$A,0))),0)))
是精華所在
分成兩個部分，*號為分隔
1.確認有無用到該原料(有會輸出1)
COUNTIF(INDIRECT("食譜!"&MATCH($F4,食譜!$A:$A,0)&":"&
MATCH($F4,食譜!$A:$A,0)),G$3)*
首先先畫出範圍來
因為是變動範圍，所以用到INDIRECT函數
INDIRECT裡面的MATCH是來找出該料理在第幾列
輸出的數字結合INDIRECT就會變成一個範圍(例如5:5)
那COUNTIF就可以在這個地方搜尋，G$3就是要檢驗的值，
有的話就會顯示1，正好可以拿來相乘
2.找出原料所需的數量
OFFSET(食譜!$A$1,MATCH($F4,食譜!$A:$A,0)-1,MATCH(G$3,(INDIRECT
("食譜!"&MATCH($F4,食譜!$A:$A,0)&":"&MATCH($F4,食譜!$A:$A,0))),0)))
OFFSET裡面的函數分別為資料起始點，相對列數，相對欄數
起始點設哪都可以，不過最佳的設定法就是設定A1
那相對列數(Y)跟上面1.裡面的求法相同，只是不用作成範圍
-1的目的是因為原始點的關係，真正輸出的列數會變成(Y+1)列，所以要扣回來
相對欄數比較複雜
MATCH(G$3,(INDIRECT("食譜!"&MATCH($F4,食譜!$A:$A,0)&":"&
MATCH($F4,食譜!$A:$A,0))),0)))
最外層的MATCH是用來輸出列數的
搜尋的值是G3
範圍用INDIRECT來限定(會輸出5:5這樣的值)
那最後MATCH輸出的結果就是「那樣料理中某食材所登入位置的欄數」(X)
而我要求出的值是下一個欄位的值
結合OFFSET的特性，我應該要設定成(X-1+1)
因為材料那一欄的欄位在OFFSET裡面應該要輸入(X-1)欄
不過我是求他隔壁那一欄位 所以又要+1回來
最後就是(X)，不用作常數的加減
整體來說
重點分別是如下
1.用MATCH來抓取所在位置的欄數或列數
2.用INDIRECT來限定出一個浮動的範圍
3.用COUNTIF來找尋值的有無
4.用OFFSET(或INDEX)來找出相對應位置的值
層面很廣
有點複雜
不過弄懂了還滿開心的!
--
※ 發信站: 批踢踢實業坊(ptt.cc)
◆ From: 118.171.123.79