記賬

實(shí)操

會計(jì)實(shí)操文庫

記賬實(shí)操?會計(jì)報(bào)表核算的函數(shù)應(yīng)用

一、數(shù)字處理

1、取絕對值二ABS（數(shù)字）

2、取整=INT（數(shù)字）

3、四舍五入二心1^。（數(shù)字，小數(shù)位數(shù)）

二、判斷公式

1、把公式產(chǎn)生的錯(cuò)誤值顯示為空

公式:M,

C2=IFERROR（A2/B2f）

說明：如果是錯(cuò)誤值則顯示為空，否則正常顯示。

2、IF多條件判斷返回值

公式:C2=IF(AND(A2v500,B2="未到期)”補(bǔ)款

說明：兩個(gè)條件同時(shí)成立用AND,任一個(gè)成立用OR函

數(shù)。

三、統(tǒng)計(jì)公式

I、統(tǒng)計(jì)兩個(gè)表格重復(fù)的內(nèi)容

公式:B2=COUNTIF(Sheetl5!A:A,A2)

說明：如果返回值大于0說明在另一個(gè)表中存在，0則

不存在。

Sheet14/Sheetl5/

A

1姓名

2D

3c

4R

5Q

6V

7B

乂,/勺heetl4[Sheetl54’q7

2、統(tǒng)計(jì)不重復(fù)的總?cè)藬?shù)

公式：

C2=SUMPRODUCT(l/COUNTIF(A2:A8fA2:A8))

說明:用COUNTIF統(tǒng)計(jì)出每人的出現(xiàn)次數(shù)，用1除的

方式把出現(xiàn)次數(shù)變成分母，然后相加。

四、求和公式

L隔列求和

公式:H3

=SUMIF($A$2:$G$2fH$2fA3:G3)

或

=SUMPRODUCT((MOD(COLUMN(B3:G3)f2)=0)*B3:

G3)

說明：如果標(biāo)題行沒有規(guī)則用第2個(gè)公式

ABCDEFGHI

月2月3目合計(jì)

1戶口口L

2實(shí)際計(jì)劃實(shí)際計(jì)劃實(shí)際計(jì)劃實(shí)際計(jì)劃

3A16937417

4B9765237

5C610107652

6/\

耳公式:

=SUMIF($A$2:$G$2,H$2ZA3:G3)

11I「,匚二二二-二二!"力二七

12

2.單條件求和

公式：：

F2=SUMIF(A:AfE2fCC)

說明：SUMIF函數(shù)的基本用法

3、單條件模糊求和

公式：詳見下圖

說明：如果需要進(jìn)行模糊求和，就需要掌握通配符的使

用，其中星號是表示任意多個(gè)字符，如“*A*"就表示a前和

后有任意多個(gè)字符，即包含Ao

61.包含A的求和42

72.以A開始求和24

83.以A開始求和18

9,J________/-X；_____

10--------------------------------------/'-----------

11公式公SUMlF(A2:A4,"*A*n,C2:C4)

12公式2:SUMIF(A2:A4,”A*”,C2:C4)

13公式3=5UMIF(A2:A4,“，A”,C2:C4)

1G

4、多條件模糊求和

公式:C11

,,,,

=SUMIFS(C2:C7,A2:A7rAll&*/B2:B7fBll)

說明：在sumifs中可以使用通配符*

ABCD

1產(chǎn)品地區(qū)數(shù)量

2電視21寸死撲2

3洗衣機(jī)5公斤洛陽5

由宇

4電視21寸1

5洗衣機(jī)6公斤北京5

6電視21寸關(guān)處1、5

7電視29寸都外2

8

9

10產(chǎn)品地區(qū)數(shù)量

11電視勃州9

5、多表相同位置求和

公式：b2

=SUM(Sheetl:Sheetl9!B2)

說明：在表中間刪除或添加表后，公式結(jié)果會自動更新。

6、按日期和產(chǎn)品求和

公式：F2

=SUMPRODUCT((MONTH($A$2:$A$25)=F$1)*($B$

2:$B$25=$E2)*$C$2:$C$25)

說明：SUMPRODUCT可以完成多條件求和

ABCDEFGH

1日期/an銷量II產(chǎn)品123

22015-1-9A5A235026

32015-1-10A18B16200

42015-1-11B11C03520

52015-1-12B5D0161

62015-2-10A18S0110

72015-2-11C19E0200

82015-2-15D6F020

92015-2-15C15C0100

102015-2-15S11

112015-2-18A16

五、查找與引用公式

I單條件查找公式

公式1:C11

=VLOOKUP(BllfB3:F7f4fFALSE)

說明：查找是VLOOKUP最擅長的，基本用法

ABCDEF

1

3姓名工號性別籍貫出生年月

4張三豐KT001男北京1970年8月

5李四光KT002女天津1980年9月

6王麻子KT003里河北1975年3月

7趙六兒KT004女河南1985年12月

8

9

10姓名捐貫

11張三豐北京＜

2、雙向查找公式

公式：

=INDEX(C3:H7,MATCH(B10,B3:B7,0),MATCH(C10fC2:H2,0))

說明：利用MATCH函數(shù)查找位置，用INDEX函數(shù)取值

ABCDEFGH

1

2..…姓名…1月2月3月4月5月6月

3劉名1054254557

4吳號碼221215348

5張晴21544205455545

6李棟45235221

7吳風(fēng)1245212354754

8

9姓名月份銷售量

10張睛3月20-*用公式返回該值

11李棟4月

12

3、查找最后一條符合條件的記錄。

公式：詳見下圖

說明：0/（條件）可以把不符合條件的變成錯(cuò)誤值，而lookup可以

忽略錯(cuò)誤值

ABDE

Xc

眼■例1：查找A的最新單價(jià)

3

4入庫時(shí)間產(chǎn)品名稱入庫單價(jià)

52012-1-2A10

62012-1-3B34

72012-1-4A19

82012-1-5E25

92012-1-6A12

102012-1-7C25

11

13A12

_14j

15C13=LOOKUP(1,0/(C5：C10=B13),D5：D10)

4、多條件查找

公式:詳見下圖

說明:公式原理同上一個(gè)公式

ABCDEFG

22例2：多條件查找

24入庫時(shí)間產(chǎn)品名稱入庫單價(jià)

252012-1-2A10

262012-1-3B34

272012-1-4A19

282012-1-5E25

292012-1-6A12

302012-1-7C25

JI

33入庫時(shí)間2012-1-4

34產(chǎn)品名稱A

35入庫單價(jià)19

36

37C35=LOOKUP(1,0/((B25：B30=C33)*(C25：C30=C34)),D25：D30)

5.指定區(qū)域最后一個(gè)非空值查找

公式;詳見下圖

說明：略

ABCDE

'1JfN例3：查找最后一次還款日期

3姓名張?jiān)嚼蠲酚旰飩ペw大志

41月45

52月5

63月344

74月

85月66

g6月54

107月

ii8月

129月75

1310月5

1411月7

1512月

16最后一次還款日期10月6月9月11月

17

18B14=LOOKUP(1,0/(B2:B13<>"),$A2:$A13)

6.按數(shù)字區(qū)域間取對應(yīng)的值

公式：詳見下圖

公式說明：VLOOKUP和LOOKUP函數(shù)都可以按區(qū)間取值，一定

要注意，銷售量列的數(shù)字一定要升序排列。

ABCDE

1例3:根據(jù)提成表計(jì)算銷售提成

2

3銷售量單個(gè)提成B列表示區(qū)間

4050~49

550950~199

620015>=200

7

8姓名銷售數(shù)量單個(gè)提成提成額

9弓賅2510

10李玉蘭300154500

11張千順15575

12吳柳15091350

13孟良1009900

14

公式

15D9：=\TOOKU^(C9fB$4:C$6f2)

16用lookup=_00K/(C9,B$4:B$6,C$4:C$6)

17用IF區(qū)間多時(shí)公式太復(fù)雜，不寫了

六、字符串處理公式

1.多單元格字符串合并

公式：c2

=PHONETIC(A2:A7)

說明：Phonetic函數(shù)只能對字符型內(nèi)容合并，數(shù)字不可以。

2、截取除后3位之外的部分

公式：

=LEFT(Dl,LEN(Dl)-3)

說明：LEN計(jì)算出總長度,LEFT從左邊截總長度個(gè)

3、截取■前的部分

公式:B2

',

=Left(Al,FIND("-fAl)-l)

說明：用FIND函數(shù)查找位置，用LEFT截取。

AB

1科目一級科目

2銀行存款-建行銀行存款

3應(yīng)收賬款一張三應(yīng)收賬款

4其他應(yīng)收款-趙志東其他應(yīng)收款

5

6

r

4.截取字符串中任一段的公式

公式:B1

=TRIM(MID(SUBSTITUTE($A1,"';REPTC',,,20)),20,20))

說明:公式是利用強(qiáng)插N個(gè)空字符的方式進(jìn)行截取

ABCDEF

3256176122232561761222

1

2

5、字符串查找

公式：B2

=IF(COUNT(FIND(“酗7A2))=0,“否"是“)

說明：FIND查找成功，返回字符的位置