excelperfect

本文將綜合使用前面系列中學(xué)習(xí)到的技術(shù)，包括布爾邏輯,、動(dòng)態(tài)單元格區(qū)域,、提取滿足條件的數(shù)據(jù)、統(tǒng)計(jì)唯一值等,，創(chuàng)建出一個(gè)解決問(wèn)題的大型公式,。當(dāng)然，如果你不需要自動(dòng)動(dòng)態(tài)更新數(shù)據(jù),，完全可以使用Excel內(nèi)置的高級(jí)篩選或數(shù)據(jù)透視表功能,。

使用輔助列：從單列中提取唯一值列表

借助于輔助列來(lái)提取唯一值比使用數(shù)組公式更容易。下圖1展示用于從單元格區(qū)域B2:B9中提取唯一的賽道名的公式,。

圖1

數(shù)組公式：從單列中提取唯一值列表,，使用SMALL函數(shù)

下圖2展示了使用數(shù)組公式統(tǒng)計(jì)唯一值數(shù)量。FREQUENCY函數(shù)作為IF函數(shù)的參數(shù)logical_test值,，傳遞一組數(shù)字?jǐn)?shù)組,，即：

{=SUM(IF({2;3;1;0;0;0;0;1;0},1))}

其中，非零數(shù)值等價(jià)于TRUE,，零等價(jià)于FALSE,。而非零數(shù)值即為每個(gè)唯一值在列表中的數(shù)量。IF函數(shù)的參數(shù)value_if_true的值為1,，從而將唯一值轉(zhuǎn)化為1,，而1的個(gè)數(shù)即為唯一值的個(gè)數(shù)。

圖2

下面,，需要考慮如何提取唯一值列表,。下圖3中顯示了需要在SMALL函數(shù)的參數(shù)array中指定的相對(duì)位置數(shù)組。

圖3

可以將上圖2公式中IF函數(shù)的參數(shù)value_if_true的值1替換為ROW($B$2:$B$9)-ROW($B$2)+1,，從而得到上圖3中的相對(duì)位置數(shù)組,。即，將：

IF(FREQUENCY(IF($B$2:$B$9<>'',MATCH($B$2:$B$9,$B$2:$B$9,0)),ROW($B$2:$B$9)-ROW($B$2)+1),1)

修改為：

IF(FREQUENCY(IF($B$2:$B$9<>'',MATCH($B$2:$B$9,$B$2:$B$9,0)),ROW($B$2:$B$9)-ROW($B$2)+1),ROW($B$2:$B$9)-ROW($B$2)+1)

得到作為SMALL函數(shù)的參數(shù)array的數(shù)組：

{1;2;3;FALSE;FALSE;FALSE;FALSE;8;FALSE}

現(xiàn)在,，提取唯一值的數(shù)組公式為：

=IF(ROWS(E$5:E5)>$E$2,'',INDEX($B$2:$B$9,SMALL(IF(FREQUENCY(IF($B$2:$B$9<>'',MATCH($B$2:$B$9,$B$2:$B$9,0)),ROW($B$2:$B$9)-ROW($B$2)+1),ROW($B$2:$B$9)-ROW($B$2)+1),ROWS(E$5:E5))))

可以看出，除SMALL函數(shù)部分外,，公式其余部分與之前講過(guò)的公式相同,。結(jié)果如下圖4所示。

圖4

如果將上圖4中的空單元格填充值,，則結(jié)果會(huì)自動(dòng)更新,。

數(shù)組公式：使用動(dòng)態(tài)單元格區(qū)域并從單列中提取唯一值列表

如下圖5所示,，在公式中使用了定義的名稱來(lái)統(tǒng)計(jì)唯一值的個(gè)數(shù)。

圖5

公式中的名稱為：RaceTrack

定義該名稱的公式為：

=$B$2:INDEX($B$2:$B$51,MATCH('Ω',$B$2:$B$51))

公式假設(shè)賽道名稱不會(huì)超過(guò)51個(gè),。

注意到,，在統(tǒng)計(jì)唯一值個(gè)數(shù)的公式中，使用了ISTEXT函數(shù),，這樣可以將數(shù)字,、非文本的單元格排除。

下圖6展示了提取唯一值列表的公式,，忽略了區(qū)域中的數(shù)字單元格,。

圖6

創(chuàng)建唯一值列表作為數(shù)據(jù)有效性下拉列表項(xiàng)

繼續(xù)使用前一個(gè)示例，將得到的唯一值列表作為數(shù)據(jù)有效性下拉列表項(xiàng),。從單元格E5:E14中提取文本項(xiàng),，在定義的名稱RaceTrackDynamicDataV中，MATCH函數(shù)的參數(shù)lookup_value值使用了通配符*?,，意味著查找的值至少有一個(gè)字符,，指定參數(shù)match_type的值為-1，查找指定區(qū)域中長(zhǎng)度至少為1的最后一個(gè)文本項(xiàng),。然后,，將定義的名稱運(yùn)用到數(shù)據(jù)有效性列表中，如下圖7所示,。這樣,，當(dāng)在列E中有新的項(xiàng)目添加或刪除時(shí)，列表值會(huì)自動(dòng)更新,。

圖7

將通配符作為字符

前面我們講過(guò),，如果要將通配符視為字符，需要在前面加上波浪號(hào)（~）,。如下圖8所示,，對(duì)于MATCH函數(shù)來(lái)說(shuō)，在參數(shù)lookup_value指定的值前面加上波浪號(hào),，在參數(shù)lookup_array指定的值后加上空文本字符串,。

圖8

使用輔助列或數(shù)組公式提取滿足條件的唯一值

如下圖9所示，借助于輔助列提取公司ABC不重復(fù)的記錄,。

圖9

也可以使用數(shù)組公式,，如下圖10所示。

圖10

在單元格B12中輸入數(shù)組公式：

=SUM(IF(FREQUENCY(IF(C2:C10=F2,MATCH(A2:A10&B2:B10&C2:C10&D2:D10,A2:A10&B2:B10&C2:C10&D2:D10,0)),ROW(A2:A10)-ROW(A2)+1),1))

在單元格A15中輸入數(shù)組公式：

=IF(ROWS(A$15:A15)>$B$12,'',INDEX(A$2:A$10,SMALL(IF(FREQUENCY(IF($C$2:$C$10=$F$2,MATCH($A$2:$A$10&$B$2:$B$10&$C$2:$C$10&$D$2:$D$10,$A$2:$A$10&$B$2:$B$10&$C$2:$C$10&$D$2:$D$10,0)),ROW($A$2:$A$10)-ROW($A$2)+1),ROW($A$2:$A$10)-ROW($A$2)+1),ROWS(A$15:A15))))

復(fù)制公式到單元格區(qū)域A15:D18,。

從交易數(shù)據(jù)集中提取客戶名和小計(jì)的動(dòng)態(tài)公式

如下圖11所示,，根據(jù)交易數(shù)據(jù)集，自動(dòng)列出所有的客戶名和銷售額小計(jì)。注意,，SUMIF函數(shù)的參數(shù)sum_range指定值為單個(gè)單元格,，此時(shí)該函數(shù)基于其用于在range參數(shù)中添加的客戶區(qū)域的高度的單元格數(shù)。

圖11

將單元格E15和F15中的公式分別向下復(fù)制,。

注：本文為電子書(shū)《精通Excel數(shù)組公式（學(xué)習(xí)筆記版）》中的一部分內(nèi)容節(jié)選,。你可以到知識(shí)星球App的完美Excel社群下載這本電子書(shū)的完整中文版。