2020/3/27 19:20:16
1、分離(lí)字母/數字和(hé)漢字
漢字在前
=LEFT(單元格,LENB(單元格)-LEN(單元格))
示例:
漢字在後
=Right(單元格,LENB(單元格)-LEN(單元格))
示例:
漢字在中間
=MID(單元格,MATCH(2,LENB(MID(單元格,ROW(INDIRECT("1:"&LEN(單元格))),1)),),LENB(單元格)-LEN(單元格)),此函數為(wèi)數組函數,因此在公式輸入完成後,按Ctrl+Shift+Enter三鍵結束
示例:
注:怕麻煩可(kě)通過填寫例子(zǐ),然後ctrl+E快捷鍵智能填充。
2、提取任意位置字符串的(de)數字
=LOOKUP(9^9,MID(數字,MATCH(1,MID(數字,ROW(1:99),1)^0,0),ROW(1:99))*1)}
(注:數組公式,需要按ctrl+shift+enter三鍵輸入)
示例:如(rú)下圖所示,提示A列中字符串中的(de)數字
=LOOKUP(9^9,MID(A2,MATCH(1,MID(A2,ROW(1:99),1)^0,0),ROW(1:99))*1)
注:如(rú)果字符串超過100位,就把99調大
3、金額大寫轉換公式
=TEXT(LEFT(RMB(單元格),LEN(RMB(單元格))-3),"[>0][dbnum2]G/通用格式元;[<0]負[dbnum2]G/通用格式元;;") & TEXT(RIGHT(RMB(單元格),2),"[dbnum2]0角0分;;整")
示例:
4、多條件查找
=Lookup(1,0/((條件1*條件2*條件3...)),返回值區域)
示例:如(rú)下圖所示要求,根據菜名和(hé)産地(dì)查找價格
=LOOKUP(1,0/(A2:A8=A12)*(B2:B8=B12),C2:C8)
5、計算不重複值個數的(de)公式
=SUMPRODUCT(1/COUNTIF(區域,區域))
示例:統計A列的(de)菜的(de)種類有(yǒu)多少
=SUMPRODUCT(1/COUNTIF(A2:A15,A2:A15))
6、多條件判斷公式
=IF(AND(條件1,條件2...條件n),同時滿足條件返回的(de)值,不滿足條件返回的(de)值)
=IF(OR(條件1,條件2...條件n),同時滿足任一(yī)條件返回的(de)值,不滿足條件返回的(de)值)
示例:滿足菜名是芹菜,價格小于5元條件的(de),返回低(dī)價
7、多條件求和(hé)、計數公式
=Sumproduct(條件1*條件2*條件3...數據區域)
示例:統計産地(dì)廳內(nèi)無智慧家庭體驗區中牟的(de)芹菜産量
=SUMPRODUCT((A18=A2:A15)*(B2:B15=B18)*D2:D15)
注:Sumproduct可(kě)以對數組進行(xíng)處理(lǐ)後再設置條件,同時也可(kě)以對文本型數字進行(xíng)計算,而Sumifs函數則不可(kě)。
8、多表統計公式
=Sum(sheetM:sheetN!單元格地(dì)址)
=AVERAGE(sheetM:sheetN!單元格地(dì)址)
……
可(kě)以彙總、均值工作表名1到工作表名2之間所有(yǒu)sheet表區域,包含sheetM和(hé)sheetN
示例: 如(rú)下圖所示,彙總Sheet1到Sheet7表D2單元格。=SUM(Sheet1:Sheet7!D2)
以上,多學(xué)多看多用,你也會成為(wèi)Excel達人。