Learn these eight commonly used and practical excel formulas, it's not a matter to get off work on time!
Han Yu dreams of flying 2021-02-22 23:59:20

One 、 Display the error value generated by the formula as null

The formula :=IFERROR(C2/D2,"")

 Learn these eight common and practical Excel The formula , It's no business getting off work on time !


Two 、 Fuzzy summation

contain A The summation formula of :=SUMIF(A2:A5,"*A*",C2:C5)

With A The sum formula at the beginning :=SUMIF(A2:A5,"A*",C2:C5)

With A The summation formula at the end :=SUMIF(A2:A5,"*A",C2:C5)

 Learn these eight common and practical Excel The formula , It's no business getting off work on time !


3、 ... and 、 Count the total number of people who don't repeat

The formula :=SUMPRODUCT(1/COUNTIF(A2:A11,A2:A11))

 Learn these eight common and practical Excel The formula , It's no business getting off work on time !


Four 、 String search

The formula :=IF(COUNT(FIND(" jiangsu ",A2))=0," no "," yes ")

 Learn these eight common and practical Excel The formula , It's no business getting off work on time !


5、 ... and 、 Summation of separated columns

The actual total formula :=SUMPRODUCT((MOD(COLUMN($C$3:$H$3),2)=1)*C3:H3)

The plan total formula :=SUMPRODUCT((MOD(COLUMN($C$3:$H$3),2)=0)*C3:H3)

Formula analysis :

First use Column Function to get the column number , Re pass Mod Module the column , Even columns divided by 2 The remainder is 0, Odd column divided by 2 The remainder is 1, Re pass Sumproduct Function for array operations , Sum after multiplication .

because mod The result is zero 1 and 0, So the formula is equal to 1 You can also omit , Optimize to :=SUMPRODUCT((MOD(COLUMN($C$3:$H$3),2)*C3:H3))

 Learn these eight common and practical Excel The formula , It's no business getting off work on time !


6、 ... and 、 Automatically select qualified data to sum

The formula :=SUMIF(A:A,E2,C:C)

 Learn these eight common and practical Excel The formula , It's no business getting off work on time !


7、 ... and 、 Multi cell string merging

The formula :=PHONETIC(A1:A5)

 Learn these eight common and practical Excel The formula , It's no business getting off work on time !


8、 ... and 、 After interception 4 The part outside the bit

The formula :=LEFT(C2,LEN(C2)-4)

 Learn these eight common and practical Excel The formula , It's no business getting off work on time !

版权声明
本文为[Han Yu dreams of flying]所创,转载请带上原文链接,感谢
https://fheadline.com/2021/02/20210222151905184s.html
相似文章

2021-08-09