|
各位朋友，元旦快樂(lè)！ 2018年，不可阻擋地來(lái)了，你準(zhǔn)備好了嗎？ 在新年的第1天，搶個(gè)早給大家發(fā)今年的第1篇文章，希望這是一個(gè)新的起點(diǎn)，我們一起從這里全新開始。 下面的30個(gè)例子是前面講解過(guò)的Excel公式練習(xí)中的示例，初步展示了Excel公式與函數(shù)的美妙與強(qiáng)大。在這里，只給出了具體的公式，不作過(guò)多的說(shuō)明，其運(yùn)算原理可點(diǎn)擊每個(gè)示例后面的鏈接來(lái)查看。
示例1：將列數(shù)據(jù)轉(zhuǎn)換為行數(shù)據(jù) 使用公式將工作表內(nèi)一列中的重復(fù)項(xiàng)依次移到一行中。具體如下圖1所示，要將工作表：
轉(zhuǎn)換成圖2所示的工作表：
在單元格B2中輸入數(shù)組公式： =IFERROR(INDEX(數(shù)據(jù)!$B$2:$B$7,SMALL(IF(數(shù)據(jù)!$A$2:$A$7=$A2,ROW(數(shù)據(jù)!$A$2:$A$7)-ROW(數(shù)據(jù)!$A$2) 1),COLUMNS($B2:B2))),'') 然后向下和向右拖動(dòng)至單元格區(qū)域B2:E4。 一鍵直達(dá) -> Excel公式練習(xí)1：將列數(shù)據(jù)轉(zhuǎn)換為行數(shù)據(jù)
示例2：將矩形數(shù)據(jù)區(qū)域轉(zhuǎn)換成一行或者一列 將工作表中矩形數(shù)據(jù)區(qū)域轉(zhuǎn)換成一行或者一列。例如將下圖3所示的名為Data的區(qū)域：
轉(zhuǎn)換成如圖4所示單獨(dú)的一列：
或者如圖5所示單獨(dú)的一行：
按行順序?qū)^(qū)域中的數(shù)據(jù)轉(zhuǎn)換成單獨(dú)的一列 在單元格B2中輸入公式，然后下拉至單元格B17，如下圖6所示。
按列順序?qū)^(qū)域中的數(shù)據(jù)轉(zhuǎn)換成單獨(dú)的一列 在單元格B2中的公式如下，然后拖動(dòng)下拉至單元格B17。
按行順序?qū)^(qū)域中的數(shù)據(jù)轉(zhuǎn)換成單獨(dú)的一行 在單元格B2中的公式如下，然后向右拖動(dòng)拉至單元格Q2。
按列順序?qū)^(qū)域中的數(shù)據(jù)轉(zhuǎn)換成單獨(dú)的一行 在單元格B2中的公式如下，然后向右拖動(dòng)拉至單元格Q2。
一鍵直達(dá) -> Excel公式練習(xí)4：將矩形數(shù)據(jù)區(qū)域轉(zhuǎn)換成一行或者一列 示例3：獲取列中的非空單元格 嘗試使用一個(gè)公式，來(lái)消除指定單元格區(qū)域中的空單元格，即獲得的值中不包括空單元格，如下圖10所示。
選擇單元格區(qū)域C1:C7，輸入數(shù)組公式： =IFERROR(INDEX(A1:A7,SMALL(IF(A1:A7<>'',ROW(A1:A7)),ROW(A1:A7))),'') 一鍵直達(dá) -> Excel公式練習(xí)2：獲取非空單元格
示例4：求連續(xù)數(shù)據(jù)之和的最大值 求連續(xù)N個(gè)數(shù)據(jù)中所有連續(xù)M個(gè)數(shù)據(jù)之和的最大值。如圖11所示，在單元格B5中，給出了一個(gè)求連續(xù)幾年薪水之和的最大值的公式。示例中是每連續(xù)4年（由單元格A5指定）薪水之和的最大值。 工作表的單元格A10中是2008年至2011年的薪水之和、B10是2009年至2012年的薪水之和，……，依此類推。其中單元格B10中的值就是所有連續(xù)4連薪水之和的最大值。 在工作表中，將單元格A5命名為“Number”。我們可以修改單元格A5中的年數(shù)，從而求出指定年數(shù)的薪水之和的最大值。
單元格B5中的數(shù)組公式為： =MAX(MMULT(A8:J8,--(ABS(TRANSPOSE(COLUMN(A8:J8))-COLUMN(OFFSET(A8:J8,0,0,1,COLUMNS(A8:J8)-Number 1))-(Number-1)/2)<Number/2))) 一鍵直達(dá) -> Excel公式練習(xí)3：求連續(xù)數(shù)據(jù)之和的最大值
示例5：找出符合多列條件的值 工作表中有多列，其中有些列的值是相同的，我們要找到同時(shí)符合幾列條件的值。例如下圖12所示的工作表，列A中是學(xué)生姓名，有重復(fù)，列B中是科目，列C中是考試成績(jī)。現(xiàn)在，我們需要找出某學(xué)生的某科目的成績(jī)。例如，張三的物理成績(jī)。
在單元格H4中輸入數(shù)組公式： =INDEX(C:C,MATCH(H2&I2,$A$1:$A$15&$B$1:$B$15,0)) 結(jié)果如下圖13所示：
一鍵直達(dá) -> Excel公式練習(xí)5：找出符合多列條件的值
示例6：判斷單元格區(qū)域中是否有重復(fù)值 如下圖14所示的工作表單元格區(qū)域A1:A9，我們將其命名為Data。
如果區(qū)域Data中有重復(fù)值，則返回False；如果區(qū)域Data中的值都不一樣，則返回True。上圖14所示的區(qū)域Data中明顯有多個(gè)重復(fù)值，因此應(yīng)該返回False。
使用下面的數(shù)組公式來(lái)判斷單元格區(qū)域Data中是否有重復(fù)值： =MAX(COUNTIF(Data,Data))=1 一鍵直達(dá) -> Excel公式練習(xí)6：判斷單元格區(qū)域中是否有重復(fù)值
示例7：統(tǒng)計(jì)單元格區(qū)域中不重復(fù)值的數(shù)量 統(tǒng)計(jì)單元格區(qū)域中有多少個(gè)不重復(fù)的值。如下圖15所示的工作表：
將單元格區(qū)域A1:A6命名為Data，要使用公式求出區(qū)域Data中有多少個(gè)不重復(fù)的值。
在某單元格中輸入下面的數(shù)組公式： =SUM(1/COUNTIF(Data,Data)) 一鍵直達(dá) -> Excel公式練習(xí)7：統(tǒng)計(jì)單元格區(qū)域中不重復(fù)值的數(shù)量
示例8：獲取單元格區(qū)域中的不重復(fù)值 在一個(gè)單元格區(qū)域中含有重復(fù)值，使用公式來(lái)獲取該區(qū)域中的不重復(fù)值。 例如，下圖16所示的工作表單元格區(qū)域A1:A13，將其命名為Data。在該區(qū)域中，含有很多重復(fù)值?，F(xiàn)在要獲取該區(qū)域中的不重復(fù)值。
在單元格區(qū)域C1:C13中輸入下面的數(shù)組公式： =INDEX(Data,SMALL(IF(MATCH(Data,Data,0)=ROW(INDIRECT('1:'&ROWS(Data))),MATCH(Data,Data,0),''),ROW(INDIRECT('1:'&ROWS(Data))))) 一鍵直達(dá) -> Excel公式練習(xí)8：獲取單元格區(qū)域中的不重復(fù)值
示例9：獲取當(dāng)前單元格所在列的列字符 使用一個(gè)公式來(lái)獲取當(dāng)前單元格所在列的列字符，例如當(dāng)前單元格為B2，其所在列為列B。
獲取當(dāng)前單元格所在列的列字符的公式如下： =LEFT(ADDRESS(ROW(),COLUMN(),2),FIND('$',ADDRESS(ROW(),COLUMN(),2))-1) 一鍵直達(dá) -> Excel公式練習(xí)9：獲取當(dāng)前單元格所在列的列字符
示例10：判斷兩個(gè)單元格區(qū)域是否有重復(fù)值 如下圖18所示的工作表，使用公式來(lái)判斷單元格區(qū)域A1:A3與C1:C3中是否有重復(fù)值。
從工作表中可以明顯看出，這兩個(gè)區(qū)域中都含有“Excel”，因此有重復(fù)值。
在單元格E1中輸入下面的數(shù)組公式： =OR(TRANSPOSE(A1:A3)=C1:C3) 其值為TRUE，表明兩個(gè)區(qū)域中存在重復(fù)值。 一鍵直達(dá) -> Excel公式練習(xí)10：判斷兩個(gè)單元格區(qū)域是否有重復(fù)值
示例11：顛倒單元格區(qū)域中的數(shù)據(jù) 使用公式將單元格區(qū)域中的數(shù)據(jù)顛倒過(guò)來(lái)。例如，下圖19所示工作表中的單元格區(qū)域Data（即A1:A7），使用公式將原來(lái)處于區(qū)域Data中第一個(gè)單元格A1中的數(shù)據(jù)放置到最后一個(gè)單元格，本例中為單元格C7，將區(qū)域Data中第二個(gè)單元格A2中的數(shù)據(jù)放置到倒數(shù)第二個(gè)單元格C6，……，依此類推，直至將區(qū)域Data中最后一個(gè)單元格A7中的數(shù)據(jù)放置到第一個(gè)單元格C1。
在單元格C1輸入公式： =INDEX(Data,ROWS(Data)-ROW(A1) 1,1) 然后，下拉至單元格C7。 一鍵直達(dá) -> Excel公式練習(xí)11：顛倒單元格區(qū)域中的數(shù)據(jù)
示例12：獲取單元格中字符串的最后一個(gè)單詞 使用公式來(lái)獲取字符串的最后一個(gè)單詞。如下圖20所示，提取列A單元格中字符串的最后一個(gè)單詞，將其放置到列C相應(yīng)的單元格中。
在單元格C1中輸入數(shù)組公式： =RIGHT(A1,MATCH('',MID(A1,LEN(A1)-ROW(INDIRECT('1:' & LEN(A1))) 1,1),0)-1) 向下拖動(dòng)至單元格C2。 一鍵直達(dá) -> Excel公式練習(xí)12：獲取單元格中字符串的最后一個(gè)單詞
示例13：統(tǒng)計(jì)單元格區(qū)域中無(wú)效數(shù)據(jù)數(shù)量 如下圖21所示的工作表，使用公式來(lái)統(tǒng)計(jì)單元格區(qū)域C2:C6中沒(méi)有出現(xiàn)在單元格區(qū)域A2:A9中的數(shù)據(jù)的數(shù)量。
也就是說(shuō)，單元格區(qū)域C2:C6中凡是沒(méi)有出現(xiàn)在單元格區(qū)域A2:A9中的數(shù)據(jù)，都是無(wú)效數(shù)據(jù)。從工作表中可以明顯看出，單元格區(qū)域C2:C6中的“SX006”沒(méi)有出現(xiàn)在單元格區(qū)域A2:A9中，因此“SX006”為無(wú)效數(shù)據(jù)，即單元格區(qū)域C2:C6中的無(wú)效數(shù)據(jù)為1。
在單元格E1中輸入下面的數(shù)組公式： =SUM(1*ISNA(MATCH(C2:C6,A2:A9,0))) 其值為1，表明單元格區(qū)域C2:C6中沒(méi)有出現(xiàn)在單元格區(qū)域A2:A9中的數(shù)據(jù)數(shù)為1。 一鍵直達(dá) -> Excel公式練習(xí)13：統(tǒng)計(jì)單元格區(qū)域中無(wú)效數(shù)據(jù)數(shù)量
示例14：使用數(shù)組公式創(chuàng)建日歷 如何使用數(shù)組公式創(chuàng)建如下圖22所示的日歷？
在單元格B1中輸入日期時(shí)，顯示該日期所在月的日歷，如下圖23所示。
獲取當(dāng)月日歷的數(shù)組公式： =IF(DAY(calendar)>15,IF(ROW()=6,'',calendar),IF(DAY(calendar)<15,IF(ROW()>9,'',calendar),calendar)) 其中，calendar是一個(gè)命名公式，即： =week*7 weekday B3-B2 其中，week和weekday都是命名數(shù)組，即： week：={0;1;2;3;4;5} weekday：={1,2,3,4,5,6,7} 一鍵直達(dá) -> Excel公式練習(xí)14：使用數(shù)組公式創(chuàng)建日歷
示例15：求2018年母親節(jié)的日期 使用公式求出2018年母親節(jié)的日期。
公式為： =DATE(2018,5,1) IF(1<WEEKDAY(DATE(2018,5,1)),7-WEEKDAY(DATE(2018,5,1)) 1,1-WEEKDAY(DATE(2018,5,1))) (2-1)*7 一鍵直達(dá) -> Excel公式練習(xí)15：求2018年母親節(jié)的日期
示例16：求包含文本內(nèi)容的單元格中的數(shù)字之和 單元格中的數(shù)據(jù)包含文本和數(shù)字（如圖24），如何使用公式求出該單元格中的數(shù)字之和？
數(shù)組公式如下： =SUM(IFERROR(1*MID(A1,ROW(1:10),1),0)) 一鍵直達(dá) -> Excel公式練習(xí)16：求包含文本內(nèi)容的單元格中的數(shù)字之和
示例17：提取字符串中的數(shù)字 單元格中的數(shù)據(jù)包含文本和數(shù)字（如圖25），如何使用公式提取出該單元格中的數(shù)字？
數(shù)組公式如下： =1*MID(A1,MATCH(FALSE,ISERROR(1*MID(A1,ROW(1:10),1)),0),255) 一鍵直達(dá) -> Excel公式練習(xí)17：提取字符串中的數(shù)字
示例18：獲取每行中第一個(gè)非空單元格 如何使用公式獲取每行中第一個(gè)非空單元格？例如下圖26所示工作表，要求使用公式根據(jù)上半部分的表格求各項(xiàng)目的開工日期。
也就是說(shuō)，要求出單元格區(qū)域B2:G6的每行中第一個(gè)非空單元格對(duì)應(yīng)的B1:G1中的日期。
在單元格B9中的數(shù)組公式如下： =IF(COUNTA(B2:G2),INDEX($B$1:$G$1,MATCH(TRUE,B2:G2<>'',0)),'') 下拉至單元格B13。 一鍵直達(dá) -> Excel公式練習(xí)18：獲取每行中第一個(gè)非空單元格
示例19：獲取單元格區(qū)域中最長(zhǎng)內(nèi)容的單元格數(shù)據(jù) 如下圖27所示的工作表，在單元格區(qū)域A1:A7中有一組數(shù)據(jù)，如何使用公式獲取該區(qū)域中最長(zhǎng)內(nèi)容的單元格數(shù)據(jù)？即單元格A2包含的文本“excelperfect”。
在單元格C3中的數(shù)組公式如下： =INDEX(A1:A7,MATCH(MAX(LEN(A1:A7)),LEN(A1:A7),0)) 一鍵直達(dá) -> Excel公式練習(xí)19：獲取單元格區(qū)域中最長(zhǎng)內(nèi)容的單元格數(shù)據(jù)
示例20：對(duì)不同的班級(jí)分別排序 如下圖28所示的工作表，在單元格區(qū)域A1:C9中有一組學(xué)生成績(jī)數(shù)據(jù)，如何使用公式分別對(duì)不同班級(jí)的學(xué)生根據(jù)成績(jī)高低排序？
在單元格D2中的公式如下： =SUMPRODUCT(--($B$2:$B$9=B2),--($C$2:$C$9>C2)) 1 下拉至單元格D9。 一鍵直達(dá) -> Excel公式練習(xí)20：對(duì)不同的班級(jí)分別排序
示例21：在單元格列區(qū)域中輸入連續(xù)的數(shù)字 使用公式在工作表任意單元格列區(qū)域中輸入連續(xù)的數(shù)字，如下圖29所示，在單元格區(qū)域A2:A7中輸入1～6。要求：不能單獨(dú)刪除或修改該單元格區(qū)域中的任何一個(gè)數(shù)字；不能在該單元格區(qū)域中添加或刪除行；在該區(qū)域最開始的單元格A2之前插入一行時(shí)，數(shù)字序號(hào)不變；將該區(qū)域復(fù)制到其它地方時(shí)，數(shù)字序號(hào)不變。
選擇單元格區(qū)域A2:A7，輸入數(shù)組公式： =ROW(A2:A7)-ROW(A2) 1 一鍵直達(dá) -> Excel公式練習(xí)21：在單元格列區(qū)域中輸入連續(xù)的數(shù)字
示例22：將表轉(zhuǎn)換成單列 使用公式將如圖30所示的工作表中的表區(qū)域A2:C5轉(zhuǎn)換到列E中，如單元格區(qū)域E1:E12。
選擇單元格E1中，輸入公式： =INDEX($A$2:$C$5,INT((ROWS(E$1:E1)-1)/3) 1,MOD((ROWS(E$1:E1)-1),3) 1) 然后，拖動(dòng)至單元格E12，即可得到正確的結(jié)果。 一鍵直達(dá) -> Excel公式練習(xí)22：將表轉(zhuǎn)換成單列
示例23：求指定日期在哪個(gè)季度 如圖31所示的工作表，在單元格區(qū)域A2:A5中輸入了日期，要求在列B相應(yīng)的單元格中得出該日期所在的季度。例如，2017年8月9日為第3季度，在單元格B2中的數(shù)字就為3。
選擇單元格區(qū)域B2:B5，輸入數(shù)組公式： =CEILING(MONTH(A2:A5),3)/3 一鍵直達(dá) -> Excel公式練習(xí)23：求指定日期在哪個(gè)季度
示例24：統(tǒng)計(jì)兩個(gè)日期之間有多少個(gè)星期一在1日 使用公式求兩個(gè)指定日期之間日期為1日的星期一數(shù)，如圖32所示。
在單元格C2中的公式： =SUMPRODUCT(--(TEXT(ROW(INDIRECT(C2& ':' & C3)),'aaaa d')='星期一 1')) 一鍵直達(dá) -> Excel公式練習(xí)24：統(tǒng)計(jì)兩個(gè)日期之間有多少個(gè)星期一在1日
示例25：生成在1至10之間且沒(méi)有重復(fù)值的隨機(jī)數(shù) 使用公式生成位于兩個(gè)值之間且沒(méi)有重復(fù)值的隨機(jī)數(shù)。如下圖33所示，生成在1至10之間且沒(méi)有重復(fù)值的隨機(jī)數(shù)。
在單元格B3中的數(shù)組公式： =SMALL(IF(COUNTIF(B$2:B2,ROW($1:$10))<>1,ROW($1:$10)),1 INT(RAND()*(10-ROW() ROW(B$3)))) 拖至單元格B12。 一鍵直達(dá) -> Excel公式練習(xí)25：生成在1至10之間且沒(méi)有重復(fù)值的隨機(jī)數(shù)
示例26：比較兩列的值并提取不相同的數(shù)據(jù) 如下圖34所示，使用公式提取列C中西區(qū)超市有而列A中東區(qū)超市沒(méi)有的水果。
在單元格A10中的數(shù)組公式： =IFERROR(INDEX($C$2:$C$6,SMALL(IF(ISNA(MATCH($C$2:$C$6,$A$2:$A$5,0)),ROW($C$2:$C$6)-ROW($C$2) 1),ROWS(A$10:A10))),'') 向下拖至單元格中沒(méi)有數(shù)據(jù)為止。 一鍵直達(dá) -> Excel公式練習(xí)26：比較兩列的值并提取不相同的數(shù)據(jù)
示例27：從數(shù)據(jù)區(qū)域中提取滿足多條件的值 如下圖35所示，單元格區(qū)域A8:D18中是銷售數(shù)據(jù)，使用公式提取張三在2017年5月1日至2017年12月1日之間的銷售數(shù)據(jù)。
在單元格F9中的數(shù)組公式： =IFERROR(INDEX(A$9:A$18,SMALL(IF($A$9:$A$18>=$B$3,IF($A$9:$A$18<=$C$3,IF($C$9:$C$18=$D$3,ROW($A$9:$A$18)-ROW($A$9) 1))),ROWS(F$9:F9))),'') 向右與向下拖至單元格中沒(méi)有數(shù)據(jù)為止。 一鍵直達(dá) -> Excel公式練習(xí)27：從數(shù)據(jù)區(qū)域中提取滿足多條件的值
示例28：獲取指定班級(jí)的學(xué)生姓名 如下圖36所示，在單元格D2中指定班級(jí)名稱，要獲取數(shù)據(jù)區(qū)域A1:B10中該班級(jí)學(xué)生姓名，如何編寫公式？
在單元格D5中的數(shù)組公式： =IFERROR(INDEX($A$2:$A$10,SMALL(IF($B$2:$B$10=D$2,ROW($A$2:$A$10)-ROW(A$2) 1),ROWS(G$4:G4))),'') 向下拖至單元格中沒(méi)有數(shù)據(jù)為止。 一鍵直達(dá) -> Excel公式練習(xí)28：獲取指定班級(jí)的學(xué)生姓名
示例29：總是獲取某列數(shù)值中的最后5個(gè)數(shù)值之和 如下圖37所示，在單元格區(qū)域A1:A10中有一系列數(shù)值，但有些單元格為空，使用公式求該區(qū)域最后5個(gè)數(shù)值之和，不計(jì)空格。
在單元格C1中的數(shù)組公式： =SUM(INDEX($A$1:$A$10,LARGE(IF(ISNUMBER($A$1:$A$10),ROW($A$1:$A$10)-ROW($A$1) 1),5)):INDEX($A$1:$A$10,MATCH(9.99E 307,$A$1:$A$10))) 當(dāng)單元格區(qū)域A1:A10中的數(shù)值改變時(shí)，單元格C1中的值隨之更新。 一鍵直達(dá) -> Excel公式練習(xí)29：總是獲取某列數(shù)值中最后5個(gè)數(shù)值之和
示例30：分別求不同班級(jí)大于90分的學(xué)生數(shù) 如下圖38所示，在列A中是班級(jí)，列B中是各班級(jí)的成績(jī)，要求各班級(jí)大于90分的人數(shù)，即列C中得出的數(shù)字。
在單元格C4中的公式： =IF(A5<>'',COUNTIF(INDEX($B$3:B4,MATCH('做',$A$3:A4)):B4,'>'&$C$1),'') 向下拖至單元格C19。 一鍵直達(dá) -> Excel公式練習(xí)30：分別求不同班級(jí)大于90分的學(xué)生數(shù) 接下來(lái)，我將與大家一起來(lái)發(fā)現(xiàn)Excel公式與函數(shù)之美。 本文為原創(chuàng)文章，轉(zhuǎn)載請(qǐng)聯(lián)系我（xhdsxfjy@163.com）或者注明出處。 歡迎在下面留言，完善本文內(nèi)容，讓更多的人學(xué)到更完美的知識(shí)。 |
|
|
