iT邦幫忙

1

excel 公式問題

excel
神威 2018-02-22 14:49:00 ‧ 9242 瀏覽

各位大大好:
想請教一下excel的問題
https://ithelp.ithome.com.tw/upload/images/20180222/20102983eSek3rRHhR.jpg
以上圖片
我希望我可以有2個輸入二個數字B和S
如果這2個數字有對應到以上標準就會顯示那格的總工時
我只會用程式表達，但不會用excel....
想問該怎麼做....
我現在寫的完全無法跑...

=IF(AND(L14<C2:C10,M14<D2:D10),I2:I10,false)
//若2個條件同時達到即可顯示那列的I
paicheng0111 iT邦大師 5 級 ‧ 2018-02-22 16:59:47 檢舉
我不清楚你的需求。
在你寫的程式中， `L14<C2:C10` 只能做到B < 50這種單向的比較，無法做到 50 < B < 100 這種雙向的比較。
但，你的圖片中又有出現雙向比較的字句。
paicheng0111 iT邦大師 5 級 ‧ 2018-02-22 17:23:54 檢舉
另外，圖片中又出現 S < 500 以及S < 500 < 1000。
那接下來是否為S < 500 < 1000 < 2000？那不就跟S < 500 是相同的嗎？
登入發表討論
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

3 個回答

0
paicheng0111
iT邦大師 5 級 ‧ 2018-02-22 15:31:12
最佳解答

我在K2儲存格寫下

=IF(AND($L$14<C2,$M$14<D2),I2,0)

然後，往下拉。

看更多先前的回應...收起先前的回應...
神威 iT邦研究生 4 級 ‧ 2018-02-22 15:38:22 檢舉

你好:
這個我會，我要的是可以把答案顯示在某個固定格子而非某個格子
我想要L14和M14輸入後，答案自動顯示在N14，(其他格都不要有多餘的顯示)可以這樣嗎?
謝謝

paicheng0111 iT邦大師 5 級 ‧ 2018-02-22 17:30:27 檢舉

先設定二個輔助欄位，分別代表B的下界與C的下界，然後再在N14用SUMIFS函數。

神威 iT邦研究生 4 級 ‧ 2018-02-22 17:37:26 檢舉

pcw，打錯了
是500<S<1000才對

神威 iT邦研究生 4 級 ‧ 2018-02-22 17:44:09 檢舉

https://ithelp.ithome.com.tw/upload/images/20180222/20102983zxAId8FVFF.png
以上為圖片，
我有找到一種解法，
https://www.deskbright.com/excel/index-match-multiple-criteria/
但我發現照著做還是無法顯示，

=INDEX(C2:D10,MATCH(L13,{0,51},1),MATCH(M13,{0,501,1001,2001,3001,4001,5001},1))

想問一下這語法該如何改善
謝謝

paicheng0111 iT邦大師 5 級 ‧ 2018-02-22 17:52:06 檢舉

是500 < S < 1000？
我可以用500 < S <= 1000嗎？

神威 iT邦研究生 4 級 ‧ 2018-02-22 17:59:48 檢舉

PCW 可以
500 < S <= 1000

paicheng0111 iT邦大師 5 級 ‧ 2018-02-22 18:04:07 檢舉

這樣試試看。

神威 iT邦研究生 4 級 ‧ 2018-02-23 10:13:42 檢舉

pwc 可以了，謝謝

登入發表回應
1
做工仔人!
iT邦大師 1 級 ‧ 2018-02-22 16:28:06

基本上樓主要在一個SHEET中用函數方式來達到需求:"很難" !
原因:EXCEL函數中用來比對資料而傳回接近值的函數為:VLOOKUP
但是VLOOKUP 只能用一個值下去比對,無法用二個欄位值下去比對.

可行的解決方案:
1.寫VBA 方式去撈資料
2.將表格拆成多個SHEET , 再用VLOOKUP 去撈資料.
例:將欄位B(規格B)相同的拆成多個SHEEL ,如規格B=50,就拆成一個SHEET 為B50, 規格B=100 :就拆成B100...)
在新的SHEET中, A欄放規格B , B欄放規格S
用VLOOKUP 的指令:
=IF(A1 <=50,VLOOKUP(B1,B50!$D:$J,7,1),
IF(A1<=100,VLOOKUP(B1,B100!$D:$J,7,1),'ERR')
)

神威 iT邦研究生 4 級 ‧ 2018-02-23 10:16:19 檢舉

謝謝做工仔人!回復
但我這東西寫完後就要直接丟給業務維護
所以我盡可能弄簡單一點
想不到搞個表格這麼難.....

登入發表回應
0
gogobyby
iT邦新手 3 級 ‧ 2018-02-26 11:19:44

看起來好像來晚了...

其實VLOOKUP是可以多條件比對的,要用到數組而已
因為上面提到大於小於等於,所以函數用區間取值,先用IF去判斷對錯再用LOOKUP+VLOOKUP組合函數去做比對

=IF(OR(I12>100,J12>6000),"ER",VLOOKUP((LOOKUP(I12,{0,51,101},{50,100,"ER"}))&(LOOKUP(J12,{0,501,1001,2001,3001,4001,5001,6001},{500,1000,2000,3000,4000,5000,6000,"ER"})),IF({1,0},A:A&B:B,H:H),2,0))

函數輸入完後記得"CTRL+SHIFT+ENTER"一起按
https://ithelp.ithome.com.tw/upload/images/20180226/20098877s3Z5VEV1co.jpg

gogobyby iT邦新手 3 級 ‧ 2018-02-26 11:47:29 檢舉

後來才想到沒判斷到,50<B<100且S>1000的情況...

神威 iT邦研究生 4 級 ‧ 2018-02-26 13:30:24 檢舉

gogobyby你好:
還是謝謝你提供意見
想請教一下VLOOKUP是不是要把所有條件都列出來啊?
其實我的B是0-600(0-50,51-100,101-150,....)
s是0-6000(0-500,501-1000,1001-2000....)
這樣是不是就要很多個if阿?
如果是的話有辦法省略嗎?

gogobyby iT邦新手 3 級 ‧ 2018-02-26 15:14:52 檢舉

如果不介意表格裡出現"#N/A"的話一開始的IF判斷可以拿掉沒關係,
函數可以直接從VLOOKUP開始

另外只是判斷區間增加的話不用增加IF,
只要把LOOKUP裡面的對照數值修改就可以了

=LOOKUP(輸入數值,{界限1,界限2,界限3,界限4},{數值1,數值2,數值3,數值4})

"界限1<=輸入數值<界限2"則回傳"數值1"
"界限2<=輸入數值<界限3"則回傳"數值2"
"界限3<=輸入數值<界限4"則回傳"數值3"
"界限4<=輸入數值"則回傳"數值4"

但是要是你的對照數值不只B和S兩樣的話,
VLOOKUP裡面還要增加相對應的函數要求

{=VLOOKUP(條件1&條件2&條件3,IF({1,0},搜尋區域1&搜尋區域2&搜尋區域3,返回值區域),2,0)}

在等號和函數前後的{}表示這個函數是數組公式輸入完後不能只按ENTER,要"CTRL+SHIFT+ENTER"三鍵一起按

假如覺得整串函數太冗長可以使用輔助欄位拆分,
先用輔助欄位先把LOOKUP回傳數值做好,
然後再用VLOOKUP去抓取輔助欄位
不過缺點就是會多出一些使用欄位畫面上比較沒那麼簡潔

在下才疏學淺,要是各位先進前輩有好的的方式可以簡略公式請不吝賜教讓大家一起學習

登入發表回應

我要發表回答

立即登入回答
iThome鐵人賽
參賽組數
1123 組
團體組數
52 組
累計文章數
23096 篇
完賽人數
656 人
iT+看影片追技術 看影片追技術 看更多
熱門tag 看更多
15th鐵人賽 13th鐵人賽 14th鐵人賽 12th鐵人賽 11th鐵人賽 鐵人賽 2019鐵人賽 2018鐵人賽 javascript 2017鐵人賽 python windows php c# windows server linux css react 程式設計 vue.js
熱門問題
熱門回答
熱門文章
IT邦幫忙