標題:
Excel的IF函數疑問
發問:
我想使用Excel的IF函數製作一各判斷式我的式子如下=IF(AND(F12>35001,OR(J12="T",J12="N")),500,IF(AND(F12>35001,J12="R"),200,IF(AND(F12>10001,OR(J12="T",J12="N")),315,IF(AND(F12>10001,J12="R"),125,IF(AND(F12>3201,OR(J12="T",J12="N")),200,IF(AND(F12>3201,J12="R"),80,IF(AND(F12>1201,OR(J12="T",J12="N")),125,IF(AN... 顯示更多 我想使用Excel的IF函數製作一各判斷式 我的式子如下 =IF(AND(F12>35001,OR(J12="T",J12="N")),500, IF(AND(F12>35001,J12="R"),200, IF(AND(F12>10001,OR(J12="T",J12="N")),315, IF(AND(F12>10001,J12="R"),125, IF(AND(F12>3201,OR(J12="T",J12="N")),200, IF(AND(F12>3201,J12="R"),80, IF(AND(F12>1201,OR(J12="T",J12="N")),125, IF(AND(F12>1201,J12="R"),50, IF(AND(F12>501,OR(J12="T",J12="N")),80, IF(AND(F12>501,J12="R"),32, IF(AND(F12>281,OR(J12="T",J12="N")),50, IF(AND(F12>281,J12="R"),20, IF(AND(F12>151,OR(J12="T",J12="N")),32, IF(AND(F12>151,J12="R"),13, IF(AND(F12>91,OR(J12="T",J12="N")),20, IF(AND(F12>91,J12="R"),8, IF(AND(F12>51,OR(J12="T",J12="N")),13, IF(AND(F12>51,J12="R"),5, IF(AND(F12>26,OR(J12="T",J12="N")),8, IF(AND(F12>26,J12="R"),3, IF(AND(F12>16,OR(J12="T",J12="N")),5, IF(AND(F12>16,J12="R"),2, IF(AND(F12>9,OR(J12="T",J12="N")),3, IF(AND(F12>9,J12="R"),2, IF(AND(F12>2,OR(J12="T",J12="N")),2, IF(AND(F12>2,J12="R"),2, IF(AND(F12>1,OR(J12="T",J12="N")),1, IF(AND(F12>1,J12="R"),1,"")))))))))))))))))))))))))))) 結果輸入好這些式子後 才發現Excel的IF函數只能到七層 不知道有沒有大大可以幫我解決這問題 因為我的這各判斷式就是一定要這麼多 所以在此想請問有沒有大大可以提供比較好的方式或是函數來取代 謝謝^^" 更新: 大大你可能誤會我的意思的 你給我的VLOOKUP 我剛剛去你的部落格看後 我覺得這函數可能不是我所需要 因為我需求的是 在一各表格中 如果我的目的格是K12 哪我可以在F12和J12這兩格中輸入數字和一各文字 然後從我上面IF函數式中得到一各數字 舉例 就是我可以在F12這各格子中輸入90 而在J12這各格子中輸入R 哪我的K12就能夠自動帶出5 不知道大大還有沒有別的方法可以來代替 因為我這函數是要應用在我工作上所要用到抽樣計畫表 更新 2: 可否給我你的Mail不然我不知道怎麼將檔案寄給你 謝謝^^" 另外下面兩位大大 你們所提供的陣列方式對我而言真的太過於深奧嚕 我實在是有看沒有懂 可否請你們在簡單化一點 謝謝^^" 更新 3: 三位大大我想在此再請教一各問題 就是Excel中的儲存格可以設定成輸入後就無法修改嗎?? 因為在我整各表格中有一格式用來讓檢驗者簽名的地方 我想將此格設定為只要檢驗者簽名之後 就無法再修改此儲存格 或是設定成如果今天A開啟的此工作表 輸入完一些相關資料後 檢驗者簽名的儲存格就會自動帶A的名字進去 如果是B開啟的就會帶B的名字 不知道Excel可以如此的設定嗎 或是能用其他方法代替呢?? 謝謝^^ 更新 4: 您會撰寫 VBA 嗎？ 關於您的補充問題，一個小小提示： 在還沒簽名之前，所有需要簽名的儲存格都可以輸入。 等簽名完之後，再去點選已簽名完之後的儲存格，都將作用中儲存格移到 A1 （可以除了可簽名之外的任意儲存格）即可 關於這點補充 VBA我個人是沒接觸過>< 至於你提到的小提示方法 我不太了解是什麼意思 因為試過移動它但還是移動不了>< 需不需要我將我所要表達的檔案寄給你讓你能更清楚了解我所要表達的意思呢?? 謝謝
最佳解答:
為了減少公式長度先定義名稱 NN={1;1;2;2;3;2;5;2;8;3;13;5;20;8;32;13;50;20;80;32;125;50;200;80;315;125;500;200} XX={1;1;2;2;9;9;16;16;26;26;51;51;91;91;151;151;281;281;501;501;1201;1201;3201;3201;10001;10001;35001;35001} YY={"T";"R";"T";"R";"T";"R";"T";"R";"T";"R";"T";"R";"T";"R";"T";"R";"T";"R";"T";"R";"T";"R";"T";"R";"T";"R";"T";"R"} ZZ={"N";"";"N";"";"N";"";"N";"";"N";"";"N";"";"N";"";"N";"";"N";"";"N";"";"N";"";"N";"";"N";"";"N";""} K12=MAX((XXXX),OR((YY=J12),(ZZ=J12)))*NN) 輸入完按Ctrl Shift Enter 2007-01-16 08:23:48 補充： 將原表格全選複製到Sheet3! 修改A欄的輸入法 1~1取身前面數字１ 2~8取身前面數字２ 公式修改如下 =SUMPRODUCT(MAX((Sheet3!$A$10:$A$22<=F12)*Sheet3!$B$10:$D$22*(Sheet3!$B$8:$D$8=Sheet1!J12))) 公式為取符合條件的最大數 2007-01-16 20:32:55 補充： Sheet3!$A$10:$A$22<=F12 如F2=90 從你的條件中51以下的數都成立,而51是最大的所以要最大的(MAX) 陣列公式是看面的A10:A22當他是Y軸13列,Y軸是51確定了 再看X軸R、T、N就三欄 Sheet3!$B$8:$D$8=Sheet1!J12 如J12="R"，$B$8:$D$8剛好三格看他=那一格,就可確定X軸 Sheet3!$B$10:$D$22 3欄13列 X軸跟Y軸相交點就是你要的 2007-01-16 20:41:42 補充： SUMPRODUCT傳回各[[陣列]]中所有對應元素乘積的總和。 最主要應用他的[[陣列]]功能而省去按Ctrl Shift Enter. 就看你要省SUMPRODUCT或省去按Ctrl Shift Enter解都是一樣 如MAX()函數得到是5 =SUMPRODUCT(5)還是=5
在 Sheet2 先建立你的對照關係, A 欄輸入數字, B 欄輸入文字, C 欄輸入當查詢數字大於等於 A 欄數字時, 要自動帶出的結果. A 欄要從小到大排序. 例如, 以你原來的一部份公式為例 IF(AND(F12>51,OR(J12="T",J12="N")),13,IF(AND(F12>51,J12="R"),5, 則要在 A 欄輸入 52, B 輸入 T, C 欄輸入 13 A 欄輸入 52, B 輸入 N, C 欄輸入 13 A 欄輸入 52, B 輸入 R, C 欄輸入 5 (記住, >51 就是 >=52, 所以 A 欄要填入 52) 假設 Sheet2 的對照表範圍在 A1:C20, 則在 Sheet1!F12 輸入下列公式 =INDEX(Sheet2!$C$1:$C$20,MATCH(F12,IF(Sheet2!$B$1:$B$20=J12,Sheet2!$A$1:$A$20,""),1),0) 最後以 CTRL+SHIFT+ENTER 完成輸入, 成為陣列公式, 完成. 公式可往上或往下複製. 有關陣列公式的說明, 請參考 [EXCEL] 淺談陣列公式－高標低標的計算 有關 INDEX() / MATCH() 的說明, 請參考 [EXCEL] 任意鍵值查表法 2007-01-16 00:54:24 補充： 建議先依回答操作, 確認是否合乎需求, 再來試著理解公式內容, 或進一步簡代公式.|||||使用 VLOOKUP 可以簡化您的需求 可參考筆者部落格： http://blog.yam.com/trump/category/609792 這類的解答～ 2007-01-15 20:27:22 補充： 檔案寄給我，幫你看看怎麼處理。 2007-01-15 22:57:49 補充： 請打開您的個人知識 收信功能，我會告知我的郵件地址。 知識 規定，不可公開個人信箱，請見諒。 2007-01-16 05:42:57 補充： 建議您弄個對照表，好方便整理出之間的相對應關係。 如果有了對應關係，那公式就簡單多了。 2007-01-16 06:38:13 補充： 愛上 Excel 總是令人難以釋懷好的題目 花了兩個小時，整理出您的問題之後，終於有了結果 先弄個對照表 若計算結果要出現在 K12 儲存格，則 K12 公式： =LOOKUP(F12,1/(($M$2:$M$43<=F12)*($N$2:$N$43=J12)),$O$2:$O$43) 此公式可以往下複製 筆者部落格有針對此問題的相關解答，請參閱： http://blog.yam.com/trump/article/7557009 2007-01-16 06:44:58 補充： 陣列公式，的確要開始入門有些不易 但是當您開始理解也會使用陣列公式之後 真的會令您抓狂！ 往往複雜、冗長的公式，只要使用陣列公式都便得相當簡潔。 不妨一起來研究、討論陣列公式。 最想說的是：騙你愛上陣列公式，永不後悔～ 2007-01-16 06:49:45 補充： 筆者手上整理不少關於陣列公式資訊，如有需要請至筆者部落格留言索取。 2007-01-16 23:57:38 補充： 您會撰寫 VBA 嗎？ 關於您的補充問題，一個小小提示： 在還沒簽名之前，所有需要簽名的儲存格都可以輸入。 等簽名完之後，再去點選已簽名完之後的儲存格，都將作用中儲存格移到 A1 （可以除了可簽名之外的任意儲存格）即可 2007-01-17 00:22:38 補充： 針對筆者所說得公式部份，意義如下： =LOOKUP(搜尋值,1/((條件一)*(條件二)),傳回值範圍) 當條件一或條件二不成立時，會傳回 False， 亦即 1/0 會傳回 ＃DIV/0! （數學中任何數除以零，等於無意義） 此時 LOOKUP 函數將省略 #DIV/0! 部份，不予搜尋。 2007-01-17 00:22:43 補充： 反之，當條件一與條件二成立時，會傳回 True， 亦即 1/True ，1 除以 True （True 類比數值 1 ) ，結果傳回 1， 此時 LOOKUP 函數會搜尋小於或等於 F12 儲存格中的數值，再依照 F12 搜尋得到的值，傳回該列相對應 「傳回值範圍」($O$2:$O$43)中的儲存格數值。 2007-01-17 00:24:15 補充： 修正最後一次補充： 反之，當條件一與條件二成立時，會傳回 True（True 類比數值 1 )， 亦即 1/1 ，1 除以 1 ，結果傳回 1， 此時 LOOKUP 函數會搜尋小於或等於 F12 儲存格中的數值，再依照 F12 搜尋得到的值，傳回該列相對應 「傳回值範圍」($O$2:$O$43)中的儲存格數值。 2007-01-17 00:26:51 補充： 如果仍不明白，可以多看幾次 Excel 說明檔中， LOOKUP 函數的說明。 2007-01-17 00:31:14 補充： 其實另外兩位高手所回答的公式，都相當經典。 公式看不懂，可以邊學邊理解。 有時，筆者也是如此，先套用別人的公式，等有時間再深入研究。 畢竟，Excel 公式變化萬千，其中理解陣列公式是一個邁向高階使用者重要的門檻。 2007-01-17 11:23:40 補充： 也就是說您的第二個問題，需要寫 VBA 才能辦到。FBEFE3C2E0474026
Excel的IF函數疑問
發問:
我想使用Excel的IF函數製作一各判斷式我的式子如下=IF(AND(F12>35001,OR(J12="T",J12="N")),500,IF(AND(F12>35001,J12="R"),200,IF(AND(F12>10001,OR(J12="T",J12="N")),315,IF(AND(F12>10001,J12="R"),125,IF(AND(F12>3201,OR(J12="T",J12="N")),200,IF(AND(F12>3201,J12="R"),80,IF(AND(F12>1201,OR(J12="T",J12="N")),125,IF(AN... 顯示更多 我想使用Excel的IF函數製作一各判斷式 我的式子如下 =IF(AND(F12>35001,OR(J12="T",J12="N")),500, IF(AND(F12>35001,J12="R"),200, IF(AND(F12>10001,OR(J12="T",J12="N")),315, IF(AND(F12>10001,J12="R"),125, IF(AND(F12>3201,OR(J12="T",J12="N")),200, IF(AND(F12>3201,J12="R"),80, IF(AND(F12>1201,OR(J12="T",J12="N")),125, IF(AND(F12>1201,J12="R"),50, IF(AND(F12>501,OR(J12="T",J12="N")),80, IF(AND(F12>501,J12="R"),32, IF(AND(F12>281,OR(J12="T",J12="N")),50, IF(AND(F12>281,J12="R"),20, IF(AND(F12>151,OR(J12="T",J12="N")),32, IF(AND(F12>151,J12="R"),13, IF(AND(F12>91,OR(J12="T",J12="N")),20, IF(AND(F12>91,J12="R"),8, IF(AND(F12>51,OR(J12="T",J12="N")),13, IF(AND(F12>51,J12="R"),5, IF(AND(F12>26,OR(J12="T",J12="N")),8, IF(AND(F12>26,J12="R"),3, IF(AND(F12>16,OR(J12="T",J12="N")),5, IF(AND(F12>16,J12="R"),2, IF(AND(F12>9,OR(J12="T",J12="N")),3, IF(AND(F12>9,J12="R"),2, IF(AND(F12>2,OR(J12="T",J12="N")),2, IF(AND(F12>2,J12="R"),2, IF(AND(F12>1,OR(J12="T",J12="N")),1, IF(AND(F12>1,J12="R"),1,"")))))))))))))))))))))))))))) 結果輸入好這些式子後 才發現Excel的IF函數只能到七層 不知道有沒有大大可以幫我解決這問題 因為我的這各判斷式就是一定要這麼多 所以在此想請問有沒有大大可以提供比較好的方式或是函數來取代 謝謝^^" 更新: 大大你可能誤會我的意思的 你給我的VLOOKUP 我剛剛去你的部落格看後 我覺得這函數可能不是我所需要 因為我需求的是 在一各表格中 如果我的目的格是K12 哪我可以在F12和J12這兩格中輸入數字和一各文字 然後從我上面IF函數式中得到一各數字 舉例 就是我可以在F12這各格子中輸入90 而在J12這各格子中輸入R 哪我的K12就能夠自動帶出5 不知道大大還有沒有別的方法可以來代替 因為我這函數是要應用在我工作上所要用到抽樣計畫表 更新 2: 可否給我你的Mail不然我不知道怎麼將檔案寄給你 謝謝^^" 另外下面兩位大大 你們所提供的陣列方式對我而言真的太過於深奧嚕 我實在是有看沒有懂 可否請你們在簡單化一點 謝謝^^" 更新 3: 三位大大我想在此再請教一各問題 就是Excel中的儲存格可以設定成輸入後就無法修改嗎?? 因為在我整各表格中有一格式用來讓檢驗者簽名的地方 我想將此格設定為只要檢驗者簽名之後 就無法再修改此儲存格 或是設定成如果今天A開啟的此工作表 輸入完一些相關資料後 檢驗者簽名的儲存格就會自動帶A的名字進去 如果是B開啟的就會帶B的名字 不知道Excel可以如此的設定嗎 或是能用其他方法代替呢?? 謝謝^^ 更新 4: 您會撰寫 VBA 嗎？ 關於您的補充問題，一個小小提示： 在還沒簽名之前，所有需要簽名的儲存格都可以輸入。 等簽名完之後，再去點選已簽名完之後的儲存格，都將作用中儲存格移到 A1 （可以除了可簽名之外的任意儲存格）即可 關於這點補充 VBA我個人是沒接觸過>< 至於你提到的小提示方法 我不太了解是什麼意思 因為試過移動它但還是移動不了>< 需不需要我將我所要表達的檔案寄給你讓你能更清楚了解我所要表達的意思呢?? 謝謝
最佳解答:
為了減少公式長度先定義名稱 NN={1;1;2;2;3;2;5;2;8;3;13;5;20;8;32;13;50;20;80;32;125;50;200;80;315;125;500;200} XX={1;1;2;2;9;9;16;16;26;26;51;51;91;91;151;151;281;281;501;501;1201;1201;3201;3201;10001;10001;35001;35001} YY={"T";"R";"T";"R";"T";"R";"T";"R";"T";"R";"T";"R";"T";"R";"T";"R";"T";"R";"T";"R";"T";"R";"T";"R";"T";"R";"T";"R"} ZZ={"N";"";"N";"";"N";"";"N";"";"N";"";"N";"";"N";"";"N";"";"N";"";"N";"";"N";"";"N";"";"N";"";"N";""} K12=MAX((XX
此文章來自奇摩知識+如有不便請留言告知
其他解答:在 Sheet2 先建立你的對照關係, A 欄輸入數字, B 欄輸入文字, C 欄輸入當查詢數字大於等於 A 欄數字時, 要自動帶出的結果. A 欄要從小到大排序. 例如, 以你原來的一部份公式為例 IF(AND(F12>51,OR(J12="T",J12="N")),13,IF(AND(F12>51,J12="R"),5, 則要在 A 欄輸入 52, B 輸入 T, C 欄輸入 13 A 欄輸入 52, B 輸入 N, C 欄輸入 13 A 欄輸入 52, B 輸入 R, C 欄輸入 5 (記住, >51 就是 >=52, 所以 A 欄要填入 52) 假設 Sheet2 的對照表範圍在 A1:C20, 則在 Sheet1!F12 輸入下列公式 =INDEX(Sheet2!$C$1:$C$20,MATCH(F12,IF(Sheet2!$B$1:$B$20=J12,Sheet2!$A$1:$A$20,""),1),0) 最後以 CTRL+SHIFT+ENTER 完成輸入, 成為陣列公式, 完成. 公式可往上或往下複製. 有關陣列公式的說明, 請參考 [EXCEL] 淺談陣列公式－高標低標的計算 有關 INDEX() / MATCH() 的說明, 請參考 [EXCEL] 任意鍵值查表法 2007-01-16 00:54:24 補充： 建議先依回答操作, 確認是否合乎需求, 再來試著理解公式內容, 或進一步簡代公式.|||||使用 VLOOKUP 可以簡化您的需求 可參考筆者部落格： http://blog.yam.com/trump/category/609792 這類的解答～ 2007-01-15 20:27:22 補充： 檔案寄給我，幫你看看怎麼處理。 2007-01-15 22:57:49 補充： 請打開您的個人知識 收信功能，我會告知我的郵件地址。 知識 規定，不可公開個人信箱，請見諒。 2007-01-16 05:42:57 補充： 建議您弄個對照表，好方便整理出之間的相對應關係。 如果有了對應關係，那公式就簡單多了。 2007-01-16 06:38:13 補充： 愛上 Excel 總是令人難以釋懷好的題目 花了兩個小時，整理出您的問題之後，終於有了結果 先弄個對照表 若計算結果要出現在 K12 儲存格，則 K12 公式： =LOOKUP(F12,1/(($M$2:$M$43<=F12)*($N$2:$N$43=J12)),$O$2:$O$43) 此公式可以往下複製 筆者部落格有針對此問題的相關解答，請參閱： http://blog.yam.com/trump/article/7557009 2007-01-16 06:44:58 補充： 陣列公式，的確要開始入門有些不易 但是當您開始理解也會使用陣列公式之後 真的會令您抓狂！ 往往複雜、冗長的公式，只要使用陣列公式都便得相當簡潔。 不妨一起來研究、討論陣列公式。 最想說的是：騙你愛上陣列公式，永不後悔～ 2007-01-16 06:49:45 補充： 筆者手上整理不少關於陣列公式資訊，如有需要請至筆者部落格留言索取。 2007-01-16 23:57:38 補充： 您會撰寫 VBA 嗎？ 關於您的補充問題，一個小小提示： 在還沒簽名之前，所有需要簽名的儲存格都可以輸入。 等簽名完之後，再去點選已簽名完之後的儲存格，都將作用中儲存格移到 A1 （可以除了可簽名之外的任意儲存格）即可 2007-01-17 00:22:38 補充： 針對筆者所說得公式部份，意義如下： =LOOKUP(搜尋值,1/((條件一)*(條件二)),傳回值範圍) 當條件一或條件二不成立時，會傳回 False， 亦即 1/0 會傳回 ＃DIV/0! （數學中任何數除以零，等於無意義） 此時 LOOKUP 函數將省略 #DIV/0! 部份，不予搜尋。 2007-01-17 00:22:43 補充： 反之，當條件一與條件二成立時，會傳回 True， 亦即 1/True ，1 除以 True （True 類比數值 1 ) ，結果傳回 1， 此時 LOOKUP 函數會搜尋小於或等於 F12 儲存格中的數值，再依照 F12 搜尋得到的值，傳回該列相對應 「傳回值範圍」($O$2:$O$43)中的儲存格數值。 2007-01-17 00:24:15 補充： 修正最後一次補充： 反之，當條件一與條件二成立時，會傳回 True（True 類比數值 1 )， 亦即 1/1 ，1 除以 1 ，結果傳回 1， 此時 LOOKUP 函數會搜尋小於或等於 F12 儲存格中的數值，再依照 F12 搜尋得到的值，傳回該列相對應 「傳回值範圍」($O$2:$O$43)中的儲存格數值。 2007-01-17 00:26:51 補充： 如果仍不明白，可以多看幾次 Excel 說明檔中， LOOKUP 函數的說明。 2007-01-17 00:31:14 補充： 其實另外兩位高手所回答的公式，都相當經典。 公式看不懂，可以邊學邊理解。 有時，筆者也是如此，先套用別人的公式，等有時間再深入研究。 畢竟，Excel 公式變化萬千，其中理解陣列公式是一個邁向高階使用者重要的門檻。 2007-01-17 11:23:40 補充： 也就是說您的第二個問題，需要寫 VBA 才能辦到。FBEFE3C2E0474026
文章標籤
全站熱搜
留言列表
