excel，給2個數值後抓取表格內的相對應資料

frank.michael.cheng

frank.michael.cheng
個人積分：971分
文章編號：79069101

971分

樓主

2020-09-14 23:28

標題打得很爛，因為不知道要怎麼描述XDD
以下如圖：
excel，給2個數值後抓取表格內的相對應資料

上方是ppm，左方是水量，中間表格是溶質質量

今天我需要的功能是：
輸入二數值，目前使用的ppm(固定)，還有溶質質量(不固定)，要如何把左邊的合適水量抓出來?

舉三個例子:
1. 200ppm、溶質300g，那就要在200ppm那一列抓到800這個數值
200ppm下
700水量對應 295g
800水量對應 338g
295<300<338，所以就抓800水量

2. 300ppm、溶質900g，那就要在300ppm那一列抓到1500這個數值
300ppm下
1400水量對應 886g
1500水量對應 950g
886<900<950，所以就抓1500水量

3. 如果溶質小於最上方水量300那一行的數值的話，那就抓最低300
例如今天用500ppm，溶質200g，那低於500ppm那列的最低值317，所以就抓300出來

想了很久想不出來怎麼寫公式，所以上來問各位大神
如果方便的話順便教我一下抓取的原理，學起來以後類似的就不用再問人了

謝謝大家！！

2020-09-14 23:28 發佈

文章關鍵字 excel 資料表格數值

Mystique Hsiao

Mystique Hsiao
個人積分：7785分
文章編號：79070036

7785分

2樓

2020-09-15 2:36

frank.michael.cheng wrote:
想了很久想不出來怎麼寫公式

My Interior Knowledge is Extraordinaire

frank.michael.cheng

frank.michael.cheng
個人積分：971分
文章編號：79071342

971分

樓主

2020-09-15 9:05

Mystique Hsiao wrote:
(恕刪)

謝謝，我來慢慢研究，excel新手從來沒用過offset

misser

misser
個人積分：395分
文章編號：79083588

395分

4樓

2020-09-16 5:00

frank.michael.cheng wrote:
謝謝，我來慢慢研究，excel...(恕刪)

或許是樓主提式(應該是「提示」)的小小忽略？......

如果溶質小於最上方水量300那一行的數值的話，那就抓最低300

那相反，如果大於最下方水量1500那一行的數值，是否要抓最高1500？還是傳回0？

Mystique Hsiao 大提供的，看來是傳回0？.......但我猜，樓主會不會是想要傳回1500？

excel新手從來沒用過offset

offset這個函數其實很好用，Mystique Hsiao 大已經提供給樓主參考，樓主趕快學起來吧。

Excel裡面還有不少「奇奇怪怪」的「基本」函數，其實也很常用，樓主可以舉一反三囉。比如index , indirect.....之類。

試試不用offset，也是可以：(借用一下Mystique Hsiao表格的位置)，在K4輸入：
=INDIRECT("A" & MIN(3+COUNTIF(INDEX(B3:H15,0,K1/50-3),"<" & K2),15))

「勉強」不用offset，改用indirect。不過用offset真的沒啥不好。
在K5輸入：
=OFFSET(A1,MIN(3+COUNTIF(INDEX(B3:H15,0,K1/50-3),"<" & K2),15)-1,0)

以上這2種，都可以解決「超過最下方列」的問題(傳回1500)，提供樓主參考。
~~個人提供的方法應該是「粗略」一點，只是發揮一下「創意」而已(比如MIN)，對樓主來說應該沒啥問題，應該不需要特別解釋吧？

大家一起加油囉。

Der，misser1

Mystique Hsiao

Mystique Hsiao
個人積分：7785分
文章編號：79083661

7785分

5樓

2020-09-16 5:48

misser wrote:
那相反，如果大於最下方水量1500那一行的數值，是否要抓最高1500？還是傳回0？

因為樓主未提及這部份的條件情況，
因此未將該情況列入公式設計內，
若有更多說明或範例，再將公式稍作修正即可！

ps.
其實一開始也有想到可能出現此狀況，
但若僅以最大值 (1500) 顯示結果，
實際上並無法完全將溶質物溶解，
該答案亦非正確解答，
因此最多只能顯示為資料錯誤！

misser wrote:
INDEX(B3:H15,0,K1/50-3)

用此方式可簡化公式長度，
以此例來說不失為一個好方法，
但必須在 ppm 為固定間隔的情況下，
若間隔不固定，或數值有修改，
還是得再多一層函數或修改公式才行！

My Interior Knowledge is Extraordinaire

misser

misser
個人積分：395分
文章編號：79083679

395分

6樓

2020-09-16 6:05

Mystique Hsiao wrote:
因為樓主未提及這部份的條件情況

是的，Mystique Hsiao說的完全正確，搞不好樓主待會就出來回應：我就是希望出現0，而不是1500.........所以我也只是大膽「猜測」而已。

就算樓主真的要1500也沒差，Mystique Hsiao大的公式就是微調而已，完全沒問題。

Mystique Hsiao wrote:
實際上並無法完全將溶質物溶解

所以Mystique Hsiao大不但是單純回答Excel問題而已，還能考慮到實際應用的知識，這就真的是太厲害了 100分

。

尤其是Mystique Hsiao大提供的offset，對還沒接觸過的新手來說，絕對是非常好的舉例應用真心不騙

~~

所以還是希望樓主真的好好研究一下Mystique Hsiao大的範例，絕對是收穫良多喔(不只offset，還有match也很好用)。

Der，misser1

misser

misser
個人積分：395分
文章編號：79083699

395分

7樓

2020-09-16 6:16

Mystique Hsiao wrote:
但必須在 ppm 為固定間隔的情況下

有喔，我有看到樓主有說，呵。

frank.michael.cheng wrote:
目前使用的ppm(固定)

...........(所以照樓主的意思，當然「簡化」一下囉~~呃，好吧，其實我是「偷懶」了~~哈。)

Mystique Hsiao wrote:
若間隔不固定，或數值有修改

是的，這確實是「風險」......如果樓主希望「萬無一失」的話，的確要考慮Mystique Hsiao大的說法。

個人經驗，也曾遇過幫同事完成類似需求，也確認過關鍵引數「不會更動」，所以就直接用「快」的方法(就不會動嘛，幹嘛多幾層判斷....？直接取值來用囉)~~~

後來，同事又來求救了！說資料結果有問題.......只好再去幫忙，一看，呵，當然就是，那不會更動的數值「被動了」(儲存格保護也沒用，沒設密碼，他居然也能自己解來改，然後給你一句：我什麼也沒動啊........哈。)

以上，提供樓主參考囉。

Der，misser1

snare

snare
個人積分：3107分
文章編號：79083734

3107分

8樓

2020-09-16 6:33

misser wrote:
也確認過關鍵引數「不會更動」，所以就直接用「快」的方法(就不會動嘛，幹嘛多幾層判斷....？直接取值來用囉)~~~

如果確定不會更動，可用"資料驗證"，做一個下拉式選單
因為防呆不防蠢，一定有人會打錯

k1=資料驗證，下拉式選單，範圍b2:h2
另一種用除法的偷懶寫法請參考，超過1500會輸出1600，請無視
k3
=IFERROR(LOOKUP(1,0/(OFFSET(B3:B15,,K1/50-4)<K2),A3:A15)+100,300)

L3=IF(K3>1500,"超出上限","")

Mystique Hsiao

Mystique Hsiao
個人積分：7785分
文章編號：79083912

7785分

9樓

2020-09-16 7:25

如果是自己要用的公式，
我有時也會偷懶，甚至以偷吃步的方式來精簡公式，
反正自己知道公式原理及結構，日後修改時並不難！

但幫別人設計公式時，
我會考慮到公式的容錯性及擴充性，
因為我無法得知日後誰會使用此公式，
該使用者的程度也不清楚，
在精簡公式之餘，也要考慮到未來可能出現的狀況，
雖然無法百分之百包含所有可能性，
至少要讓自己或使用者，
在不用大幅變更結構的情況下，
輕鬆修改公式以滿足未來的使用需求！

My Interior Knowledge is Extraordinaire

misser

misser
個人積分：395分
文章編號：79084263

395分

10樓

2020-09-16 8:22

感謝Mystique Hsiao提供的經驗囉。

個人目前的作法，主要是：找我幫忙可以，但不接收「沒時間，簡單交代」......要幫忙的，請空時間出來，大家當面好好溝通了解一下，我才能明確知道對方需求，可以好好規劃設計，甚至還可以提出改進的作法。......不過本來我是說：過來當面談......結果弄到最後，都嘛是我過去「堵人」(因為會發現其他問題，得確認清楚).......暈。

Mystique Hsiao wrote:
因為我無法得知日後誰會使用此公式，

這點我倒是不大有這方面的困擾，因為同事職務換人後，幾乎都不大會跟後面的人交代這些檔案是幹嘛的，怎麼用，都只交代一句：這是xxx做的.........然後，後面的人就會「自動」跑來問我(不只是有問題要改之類，連基本的操作步驟都........可是我都是「遵照」前面的人的需求做的啊，義務幫忙的結果，連這種後續的「收尾」居然都要我來.....)~~~

所以，我現在的做法，是「盡量」把注意事項、操作步驟，交代在檔案內(找空白的格子位置，甚至做說明表單...)。「防呆」的部分，有工作表保護(但不設密碼，畢竟別人也不是「非我不可」，使用者還是有權利自己來或另找他人幫忙修改之類)、多用參數化(保留使用者調整彈性)、驗證(下拉選單)、VBA表單執行+「自我檢查資料完整性」......(應該還有)......總之盡量啦。不過當然還是偶有「疏漏」，被「鑽了空子」。

最近的一個「偷懶出包」(沒做防止措施)的例子是：我都交代使用者：資料表名稱都不可以修改喔！(也加註使用說明....)，對方也說瞭解沒問題。~~我「承認」，我應該是「高估」了使用者的「能力」，可想而知，果然還是出包了........後來我一看：嗯，她是沒改工作表名稱啦...........

但是她把某些工作表直接刪了..........

好吧，別再多說了，再說下去，版上的一堆前輩高手們可能都要跳出來「痛罵」我了.........不過最後我都有「負起責任」來，乖乖做好「收拾殘局」的工作啦。.......至於「抓狂」完之後，之後我還是會問同事們：有沒有新的需求需要幫忙的？(唉，自己總是沒得到「教訓」，好了傷疤忘了痛.....哈。.....本來無一事，卻總是自動惹塵埃~)

最後小小感想：其實問題出越多，處理過程中學到的經驗也越多，感覺對個人的經驗累積是有幫助的。嗯，就是這樣。呵

Der，misser1