excel多條件專輯

06-01

excel多條件專輯

IF函數多條件判斷

多條件是我們在處理EXCEL數據時必須要面對的問題,多條件計數求和,多條件查找...為了方便大家學習,本文特對多條件的處理進行一個全面的總結,希望能對同學們有所幫助.本文由EXCEL精英培訓蘭色幻想編寫.轉截請註明作者和轉自EXCEL精英培訓.

IF函數可以單條件判斷,如:

=IF(A1<60,"不及格","不及格")

通過嵌套也可以實現多條件判斷,如

=IF(B21<60,"不及格",IF(B21<70,"及格",IF(B21<85,"良好","優秀")))

通過和OR或AND的配合可以實現混合判斷.如:

=IF(AND(B2<>"",C2<>""),C2/B2,"0%")

如果有更多的條件,甚至超過7個條件的判斷怎麼辦呢?我們可以用定義名稱其他他方式達到,不過這些都太過麻煩,這裡提供一個使用VLOOKUP函數替換IF完成多條件判斷的例子

在excel中函數最多只能嵌套七層，IF函數也不能例外，遇到需要進行多次判斷的怎麼辦呢？可以用VLOOKUP函數替代。

例如：下表中需要根據提供的銷售額判斷提成比率，這裡可能有很多，為了演示方便，只列中三種。這種情況下怎麼判斷呢？

公式1：=vlookup（C2，A$1：B$100，2，0）

如果區域不想放在單元格區域，可以直接寫成常量數組，即：

=VLOOKUP(C2,{"銷售額","提成比率";"電視",0.1;"洗衣機",0.05;"吸油煙機",0.06},2,0)

如果IF是進行的區間判斷，怎麼用VLOOKUP替換呢？答案是可以用vlookup的模糊查找功能。看下例：

公式為：=VLOOKUP(D2,A1:B11,2)

示例附件下載

excel多條件專輯

COUNTIF函數多條件計數

COUNTIF函數可以進行條件計數,但一般它只能有一個條件,如何實現多個條件呢

1 多項目條件: excel中countif能用來統計符合多個條件的單元格是可以實現的，不過要配合sum函數的使用，例：

=sum(countif(a:A,{"電視機","冰箱"}))

2 區間條件: 計算入庫金額大於10000且小於20000的入庫次數

3 更多條件我們有3種解決方案

1) 用SUMPRODUCT函數完成多條件計數,如

3月份A產品的銷售次數:=SUMPRODUCT((MONTH(A3:A9)=3)*(B3:B9="A"))

2) Excel2007後新添了多條件計數的函數COUNTIFS,它可以實現多條件計數

=COUNTIFS(A2:A11,"公司1",B2:B11,"人事部")

3) 資料庫函數DCOUNT 因為它需要有一個條件區域,用起來不方便,所以不再詳述.

excel多條件專輯

SUMIF多條件求和

SUMIF函數和COUNTIF函數用法差不多.多條件的處理方法如下:

1 多項目求和

=SUM(SUMIF(B31:B35,{"A","C"},C31:C35))

2 03版本可以用SUMPRODUCT函數替代.

=SUMPRODUCT((MONTH(A3:A9)=3)*(B3:B9="A")*C3:C9)

3 07版本可以用SUMIFS替換:

=SUMIFS(D2:D11,A2:A11,"公司1",B2:B11,"人事部")

excel多條件查找15種思路

示例

題目：如下圖所示，根據第9行的產品和型號，從上面表中查找「銷售數量」，結果如C10所示

excel多條件查找15種思路

SUM函數

公式{=SUM((A2:A6=A9)*(B2:B6=B9)*C2:C6)}

公式簡介：使用(條件)*（條件)因為每行符合條件的為0，不符合的為1，所以只有條件都符合的為非零數字。所以SUM求和後就是多條件查找的結果

SUMPRODUCT函數

公式：=SUMPRODUCT((A2:A6=A9)*(B2:B6=B9)*C2:C6)

公式簡介：和SUM函數用法差不多，只是SUMPRODUCT函數不需要數組運算

MAX函數

{=MAX((A2:A6=A9)*(B2:B6=B9)*C2:C6)}

SUM是通過求和把符合條件的提出來，這裡是使用MAX提取出最大值來完成符合條件的值提取。

lookup函數

公式1=LOOKUP(A9&B9,A2:A6&B2:B6,C2:C6)

公式簡介：LOOKUP函數可以直接進行數組運算。查找的連接起來，被查找區域也連接起來。

公式2：=LOOKUP(1,0/((A2:A6=A9)*(B2:B6=B9)),C2:C6)

公式3=LOOKUP(1,0/((A2:A6&B2:B6)=(A9&B9)),C2:C6)

公式4=LOOKUP(1,1/(((A2:A6=A9)+(B2:B6=B9))=2),C2:C6)

MIN+IF函數

公式=MIN(IF((A2:A6=A9)*(B2:B6=B9),C2:C6))

SUM+IF函數

公式

=SUM(IF(A2:A6=A9,IF(B2:B6=B9,C2:C6,0),0))

INDEX+MATCH函數組合

公式1：{=INDEX(C2:C6,MATCH(A9&B9,A2:A6&B2:B6,0))}

公式2：{=INDEX(C2:C6,MATCH(1,(A9=A2:A6)*(B9=B2:B6),0))}

OFFSET+MATCH函數

公式

=OFFSET(C1,MATCH(A9&B9,A2:A6&B2:B6,0),)

INDIRECT+MATCH函數

公式

=INDIRECT("C"&MATCH(A9&B9,A1:A6&B1:B6,0))

VLOOKUP+CHOOSE函數

公式：

=VLOOKUP(A9&B9,CHOOSE({1,2},A2:A6&B2:B6,B2:C6),2,0)

HLOOKUP+TRANSPOSE+CHOOSE函數

公式

=HLOOKUP(A9&B9,TRANSPOSE(CHOOSE({1,2},A2:A6&B2:B6,B2:C6)),2,0)

VLOOKUP+IF函數

公式1 =VLOOKUP(A9&B9,IF({1,0},A2:A6&B2:B6,B2:C6),2,0)

公式2=VLOOKUP(A9&"|"&B9,A31:D35,4,0)『添加輔助列

SUMIFS函數

excel2007中開始提供的函數SUMIFS

=SUMIFS(C1:C6,A1:A6,A9,B1:B6,B9)

資料庫函數

=DSUM(A1:C6,3,A8:B9)

=DGET(A1:C6,3,A8:B9)

=DAVERAGE(A1:C6,3,A8:B9)

=DMAX(A1:C6,3,A8:B9)

=DMIN(A1:C6,3,A8:B9)

=DPRODUCT(A1:C6,3,A8:B9)

excel多條件專輯

LOOKUP函數多條件

很多人會對=Lookup(1,0/(條件1*條件2*……),引用區域)或者=Lookup(2,1/(條件1*條件2*……),引用區域)這麼一個公式中的2、1產生疑問，到底是啥意思呢，我來說說：

2就是2，1就是1，0就是0，沒什麼意思。——呵呵。

但你應該問的是——為什麼要這樣寫公式，其實關鍵在於第2參數的分母。

比如Lookup(1,0/(條件1*條件2*……),引用區域)中：

條件——就是邏輯判斷，比如A1=B1、A1>B1、A1<>""等等，產生的是邏輯值True和False

條件1*條件2*……——就是邏輯值相乘，邏輯值運算的規則就相當於True=1、False=0，所以True*True=1、True*False=0，x/True=x本身、x/False=#DIV/0！錯誤值等等。

所以Lookup第2參數返回的是0/True=0和0/False=#DIV/0！，即都比第1參數「1」小。

Lookup函數的特性是要求第2參數按升序排列，即使不是按升序排列，它也是認為升序的情況下來執行——採用「二分法」按二分法原理，Lookup函數忽略錯誤值在第2參數中（即{0,=#DIV/0！,0,……}組成的數組）找1，肯定找不到，返回的是最後一個0出現的位置。

最後一個0意味著什麼——意味著最後一個0/True——既然是True就意味著最後一個條件成立的記錄的位置

找到了這個「位置」去對應第3參數引用區域中的位置——於是就返回了最後一個滿足條件的記錄了。

VBA多條件查找篩選

如何按多個條件用VBA進行查詢呢,下面這個示例可能會有一些啟示.

upload/2011_10/11101015221620.rar

excel多條件專輯

多條件模糊匹配求和

有網友問了下面的多條件求和問題：

我使用SUM進行多條件求和，但現在遇到的問題是如果我想對含有特定內容的單元格條件求和時卻無法使用通配符。

如：=SUM((A28:A41="5M")*(C28:C41="*MCD*"),J28:J41)

我得到的結果為0

實際在C28:C41單元格中可能有LKKMCD MCDOPI LKKMCDOPI 等多種情況存在。

而我現在想統計C28:C41中含有MCD得單元格，並對對應的J28:J41求和

A列為公司 C列為項目 J列為費用。

現在其實就是對5M公司MCD項目求費用總和。

答：正確的公式如下：

第1頁：IF函數多條件判斷

第2頁：COUNTIF函數多條件計數

第3頁：SUMIF多條件求和

第4頁：多條件查找

第5頁：LOOKUP函數多條件

第6頁：VBA多條件查找篩選

第7頁：多條件模糊匹配求和