[PDF]Excel Formula

0 Office 2016





a-iiLijJ



+- EXCEL


Formulas


2.0 :d_C±bJI

■

2 0 16



:d_|L l 5g ^Ia_cI

UJ

j lti in jlloJI p


Al-Munthir Saffan


Fb.com/almfs

□ twitter.com/almunthir
[ffl linkedin.com/almunthir
behance.net/almunthir
youtube.com/almunthir.saffan
(3) almunthir.me@gmail.com






www.learn-barmaga.com


fOJ-3jJI Uj& 3JJI cdJI fTXlLU


alll. J kSo j fn i-i j 1 1 j ii joII j lop M jiiH q n \l k^I Iqi j jol l^lli ij ai)^_o_^JI
rin g m gjSjInft qNji.jv nQ IK 100(j_c^_jjj I P(?J jii |j q c \ nVii Mln ^ n^ g
H in j q q p g chop J s iii; poVii riS 9 p owio-yi pu .Microsoft Excel J mS j
Ij6_S2j- j- Q Wii"'i 1^1

.LqlI 11 111 i \ll p K*Vi in MIg OXJI^JI H I'i UlQn

0-LS2J S V \ll 5-0 m l J 'j M Hg a 1 or?>\ll 11 111 ^ vjlg^i_II n S rt\ u ji ji plJ

OXJhg CL4JL0JI9 a ; i I I9 QjjLllldJI CDXJIj^ _ll_±» vJlQ^_II 5 lg_Jlg CjLIqJI j l fir MI

5 _lj... Ci_6gJlg 5e_jjUJI

J_o-SJI I S nS .J ^?>.!9-l ip Ip LqJ l is?Qjjg
.. p_JjAJI CLdLpgJ I mi l's


www.learn-barmaga.com






























www.learn-barmaga.com


C5J«-QJI tfjjp:

i £ lj .1^6 qQ^J 1 <»»q*~> j ini .jK 6fu_ri}l_L±i ..HIa_lcU2_£j ljUAJI I S (ft £:iJ \J

.. oj^_jLqJI fo_ slU

IclLcIqA I vjJ-C JI^JLo


www.learn-barmaga.com






v5Q'i^oH

cLi_0-£i>lJI ii iii^ JI9AJI ls?nll (d"i jlol\ll»i I jij iynl 11 if vjJn i^>o IIi 1 iij ifn i

.oiJlAJI £ IgJIg olioJI old j_o £jj 1 q K -Vi m l ji^ilJIg


d-t6i^>ll (ix3)

dJI^JI


37

DMIN

25

36

DSUM

26

50

EVEN

27

58

EXACT

28

53

FACT

29

40

FIND

30

50

FLOOR

31

48

FV

32

54

GCD

33

24

HLOOKUP

34

45

HOUR

35

13

IF

36

30

IFERROR

37

32

IFNA

38

25

INDEX

39

41

INDIRECT

40

51

INT

41

33

ISBLANK

42

32

ISERR

43

32

ISERROR

44

33

ISEVEN

45

33

ISFORMULA

46

33

ISLOGICAL

47

32

ISNA

48


cL^AiqJI pxoj

dJI^JI


55

ABS

1

56

ACOS

2

15

AND

3

56

ASIN

4

56

ATAN

5

9

AVERAGE

6

21

AVERAGEIF

7

21

AVERAGEIFS

8

50

CEILING

9

27

CHOOSE

10

57

CONCATENATE

11

56

COS

12

12

COUNT

13

12

COUNTA

14

12

COUNTBLANK

15

16

COUNTIF

16

18

COUNTIFS

17

42

DATE

18

46

DATEDIF

19

37

DAVERAGE

20

44

DAY

21

37

DCOUNT

22

37

DCOUNTA

23

37

DMAX

24


www.learn-barmaga.com






























































Q ~>Ok-\ II (TXOJ


OJIaJI


d -N Qn I I fTX-OJ


OJIaJI


58

PROPER

77

48

PV

78

53

QUOTIENT

79

55

RAND

80

55

RANDBETWEEN

81

40

REPLACE

82

59

RIGHT

83

49

ROUND

84

49

ROUNDDOWN

85

49

ROUNDUP

86

45

SECOND

87

54

SIGN

88

56

SIN

89

11

SMALL

90

53

SQRT

91

39

SUBSTATUTE

92

34

SUBTOTAL

93

9

SUM

94

19

SUMIF

95

20

SUMIFS

96

56

TAN

97

42

TIME

98

43

TODAY

99

60

TRIM

100

58

UPPER

101

23

VLOOKUP

102

44

YEAR

103

57

&

104


33

ISNONTEXT

49

33

ISNUMBER

50

33

ISODD

51

32

ISREF

52

33

ISTEXT

53

11

LARGE

54

54

LCM

55

59

LEFT

56

56

LN

57

56

LOG

58

22

LOOKUP

59

58

LOWER

60

25

MATCH

61

10

MAX

62

60

MID

63

10

MIN

64

45

MINUTE

65

52

MOD

66

44

MONTH

67

51

MROUND

68

43

NOW

69

50

ODD

70

28

OFFSET

71

15

OR

72

56

PI

73

47

PMT

74

52

POWER

75

52

PRODUCT

76


www.learn-barmaga.com




































































www.learn-barmaga.com


9 Jojj^UI ^ ojojjLjjIJI cjUIaJI

:SUM - £mv^JI oJIj

=SUM ( numberl , [number2] ,....)

I r>\o j^lj_oJI ciULiiJI ,! vjjl .foijj (^>Ic oJJI aJLiiJI :number




=AVERAGE ( numberl , [number2] ,....)


:AVERAGE - J^oJI


.LdiJ oJluinJI JaiiigloJI O-bj-SLo ^Ij-oJI olilriJI £009 1 '.! |r i.'pj v5l i(txx»j o^JI Q-iL^JI :number



(jlojjj j.LloJI .fO :v_ij^_oJI


www.learn-barmaga.com






















































































































10


Jojj^UI

=MAX ( numberl , [number2] ,....)


:MAX - v>lciJI OolqJI


l(^iii Q nin .jtlrTanj?o ^IjplloUlriJI lijlr uQj q l px6j v>Lc.ji gWi gaJIcli 16-11 :number




=MIN ( numberl , [number2] ,....)


:MIN - ^ajaill a ; o I I


J_6I LuaJ jrftloi iii ( jJLULjg,px_6joJr k 5 q Wi ^>_DIa_JL±JI I nMn J_6 j fi ySi I ir>\ j_o\JI el ISS :number

I n\in a pin



(jlojjj ji-i-oJI .fO :v_jj^_oJI


www.learn-barmaga.com





















































































































































11


=LARGE ( array , k )


Jojj^UI <^£>IJjj
: LARGE - vSjxfiJI a »; a »


.LdLLD ^3J4±> a pin d-6j-S2-oJ l_lcjJ odJI CililiiJI ^Lku array
I S ‘Srft q (3 u'iSi ) gpin jjiif OJU gl (2 t. i i Si ) a?jn J_L±J oJll±> ‘n j rftln i Q| ngj ^.SJI i 11 ij ill ^jJJ j i in i :k















cz

T



f*

=LARGE[A2:A6,2)




V

A

A

B



c


D

E

F

G

H

1


1

Value








2

10.5


10.5J









3

7.2











4

200











5

5.4











6

8.1











7












8











-

►

Sheet 1



) i M


1

0



=SMALL ( array , k )


:SMALL - \Sysuai\ cLo. iiJI


. ln\iii ,jj 9 n doin odj^loj i.< c) i ij>'ill cjUL±JI 0 lku :array
I S Srft g (3 i/iSi ) tipiri j_ 2 jo| CjJLj g| (2 i J ’i M ) dp in j_s 2 .nl oJllh ‘<~> J nSkS i y I ^gj ^.SJI i i lij ill (jjj j j ill j :k



(jlojjj ji-i-oJI .fO :v_jj^_oJI


www.learn-barmaga.com




























































































































































12


Jojj^UI

:COUNT - COUNTA - COUNTBLANK - *stl\ Jlq^
=COUNT ( numberl , [number2] ,....)

=COUNTA ( numberl , [number2] ,....)

=COUNTBLANK ( numberl , [number2] ,....)

.LdiAi; fi xlLLu oJJI cjUL±JI :number

^_S 2 jfog_Qj CountA I oiij . ln on pi ojl ■_> I Wi o>_UI oULiiJI ^_s 2 jpg_QJ Count a; m il l;
.cLcjLqJI OUL6JI szj fog o'i 6 CountBlank Lol ^cLcjLo j_lc ^5l) a m i gl a iqq j a n m v>_Lc cUglnJI CiULiiJI





(jlQjjj ji-i-oJI .fO :v_jj^_oJI


www.learn-barmaga.com




















































































































































13 Jojj^UI ^ ojujjLjjIjI cjDUI

:IF - OjJajjiLlI lij

=IF ( logical_test , [value_if_true] , [value_if_false] )

.cu_o 0 0 ^'i J l j-oJI Joj-llUI k 3 \j l j'J -s \JI :logical_test
.ioI 0 Q">j Jlo \ j^d j cft h' i jjj ^>111 OoJ-QJI :value_if_true
.Jo i.j foJ Jlo vj ^-6 j n^innu ^jjjl Oqj-QJI :value_if_false

4JluLI 6I v>loiJ-C Qjj £uogj UoJ gl .Lo-6j gl Q,;!^ gl (dJ^L5^_o Q g Vi ijl U-Vo-o

:dUb iJjo Ji)l oils lb] Smallg *20 jjo jjSI cuimJI oils lb] Large aulS g*Sg : Jtiuo



!ji nlo gnJI djidiJ cujqJU 10g (Full Time) JjoIS (Olgjj JjQlQj ^joJ oXoJU 15 o^Lj jJI oils lb] ajaiJI oul jJI olm> :Jlijo



= IF(C2 = “Full Time”, D 2 * 1 5 % + D 2 , D 2 * 1 0 % + D 2 )


JgjjUI


JqjjUI icjQn'j


iflQn'j (D2 lC

Jo


qLq_LU JJUlqJI .fO


www.learn-barmaga.com










































































































14


Jojj^UI <^£>IJjj

:(o^^_2j_cJI IF) Jajjjj Q_o jiS ±ul •

:dUb jit oils lb] Smallg *20 Jjo jaSI cuimJI oils lb] Mediumg *30 jd jaSI cuunJI oils lb] Large cuUS g*Sg :JIajo



Inj li'i iol iJ JSSQ \j fn J Jl ^. jk 6 Lol , Large (^Ljg 30 u_o J jSTi"j ilS Jl o A1 Q il-SJIf jn ^o I.jk j'^qj
£LjoL o JS^ Qnij foJ IJJg Medium 20u_o J-lSI LriuhdoISJI CulS b| ( 2 ljap J_o ig QnJg ynn

.Small

.1 rft l i ^Vi 6 .jv'j Jl IF Jl ^A^ULulgi)! lLd>g


^>JLUl jjjjJ I


Jo j_jjjJI
uJQJI


0 6 "Vi (DJliZ

oJiiii Jo j_jjjji


= IF ( A1 > 3 0 , ’’Large” , IF (A1>20 , ’’Medium” , ’’Small” ))


JglJI Jo j_jjjJI


JoJ_JJjJI ^_QOJ
JgJl


lj)Qn J (D2 lC

JglJI Joj-luJI


Jjciflj 0-JQJ oJjqJU 10g (Full Time) JjoIS ^Igj^ Jj&£j (ycii oJuoJU 15 ^_£b ojLj jJI oJIS lb] a-ja^II oLiljJI oLuui> : JLao

j^-AdJogjall ojlqJ oJjqJU 5g (Short Time) ^JJ> pl9-M


E2

f* =IF(C2="Full Time",D2*15%+D2,IF(C2="Short Time",D2*10%+D2,D2*5%+D2)} v


A

B

c

D

E

F

G

H

I

Jk.

1

Name Gender Work Salary New Salary





2

Ahmad Male Full Time 1500

1725 J

1





3

4

5

6

7

8

Monaf

Saad

Mona

Nuha

sameer

Mustafa

Male

Full Time

1200

138o'






Male

Short Tim?

900

990







Female

Short Tim?

1000

1100






Female

free lance

700

735






Male

Full Time

1500

1725






-

Male

Short Tim?

1200

1320














shceti © : nn i m


qLq_LU JJUlqJI .fO ILJJA^JI


www.learn-barmaga.com











































































































15


Jojj^UI

:0R q AND Joqj-lllII ^ ^ sl


=AND ( logical 1 , [logical2] ,....)

=0R ( logical 1 , [logical2] ,....)

AND p v'i-Vi mi Ing j iii I Iy_oa r go-^om o-Vii Q| Lbjl JLo o_6 ,k>gj_LLiJI £^Lap ( 9 o^ 1 ' d-hLsz-o AND
.J_6\Jlo-Lc I i O 'i ~> lg ■ jj 6 "Vn p I ,JI ^ \ n Falseg I A9 inn I (-ftOO-Vi . Jl oTrued piOll I iiln

y fj 6^> i JI-3 o_6True kw ; ri j'p .Hgj III o^_c u_o J-DiJI o>-Lc ^ ~> lg Jaj_LU i n o~>i d_tal_2-o ■ ■ > rhd OR Lol

.£(jn_lu v5l i j 6 'Vi; pj JLd o_d Falseg . J_o\JI o-Lc I nMn ^ ~> lg


:(\julQullg culgi/l olibJuoi/l 50 Jl /lmjtohlc ojgbJ jjjJI ^bjui^l cvb jna : Jlio



.J-6\JI ole Jieilg Q l^i io l \j>J) 50 OIq\Lc Cljglnj 0_o lil llnr jip gdmi ORdJI^JI p h -S i m l; Lioi) gJ Ic* i j |



(jlAuj ji-i-oJI .fO :v_j


www.learn-barmaga.com

















































































































16


Jojj^UI ojojjLjjIJI oIIIaJI


:COUNTIF - oJojjilII ^szJI

=COUNTIF ( range , criteria )

•Oj-SZ-o Iqj in i noo'i Jin v>d LdiAi: ^IjjdJI CjlilnJI icjlku :range
c iiir ;iii -wii jfD qomi vjiiJI jlnz-oJI xriteria

(T v rhr' lq-' ij q S i J_j;iJ| | j 16 lo g? II r ldj_S 2 J 01 Lbjig .. (T V oTl 0 r Q $u i ki £_o Q i 6 lo g p £ Lo_luI LuaJ JL} gJ-6

.\J_olb L-olg^ u p \< jlj ^-SzJI jLlslo & b ■ (du i J_o-S2JI O-smJa v>lc \jj g 1 ^.\ vjb-ll ^g-o-xJI jUinj.. \LoL±>


:(Full Time) JjoIS ( olg.xj j glnoi QjjJI jjx oLu> :JtiuO


E2


=COUNTIFS(B2:B8,"Full Time"}


A

B

C

D

E

F

G

H | I

J

1

Name Work Salary







2

Ahmad Full Time 1500


3j

1





3

4

5

6

7

8

Monaf

Saad

Mona

Nuha

sameer

Mustafa

Full Time

1200








Short Time

900








Short Time

1000








free lance

700








Full Time

1500








Short Time

1200









Sheetl


© i HE


-


!□ 0


.culc.ji gWi (jJJI Q;I-sI I.FullTime QqI^ Jaj£jqJ Jl j-olJI i‘I is S Mi lSo j;












E2

T


X */ J*


:COUNTIFS(B2:B8,B2)


V


A

1

B

J

C


D

1 _E

F

G

H 1 1

J

Jk.

1

Name

Work


Salary









2

Ahmad

Full Time

1500


3







3

Monaf

Full Time

1200









4

Saad

Short Time

900









5

Mona

Short Time

1000









6

Nuha

free lance

700









7

sameer

Full Time

1500









8

Mustafa

Short Time

1200








-


Sheetl





©

hi

!□ 0



.Lds^szj (txx> B2 ail'SI I Qffti l m o Oil'S B2:B8<6 1 In i I I v_>_6 Oivng lo o^-o 'unoi g


(jlojjj j-Li-oJI .fO :v_ij^_oJI


www.learn-barmaga.com








































































































17


iJ LaiiUI ,?u>lj)j qjajjLujiJI cjUIaJI


jU_S2udJI £j-P £vJCU 01 1 '.! Ir IJ oq'ijn ,0_LLS2_o tin I Q Q-O J-XJCll gl J-L±>l 0 ^ 2 } oJJI fTX_LQj| A52J |X>g.ftJ Ql Lj^jl JlTi \j>J) LqT

.ijluLiIdIoJjLuj] i jpn \LoL±>


:20 (UUQJI OjO ,yJI ■ Jllo



I iiln I - 7 > qii Criteria jLlS2-oJI cj -^ Q nio ti ijlQnrijI ml ft kM QIL bjl I qijv IqQ iljq oll ftjln'i mi .jij -Slnjl i^iq

-Mil l i'in l.jv'ijl iiijp III LdLSZJCUul

<> [ j-S-nlJI . < jj-hiJI iJjd-lLuC iljldiiJIg


:Free lance j> Jnr Jjclqj qjo fluuuO Uujq> j iningni i jac oUu> :Jtio



(jlOjJJ J-Li-oJI .fO lUJJAoJI


www.learn-barmaga.com






































































































18 Jojj^UI ^ ojujjLjjIjI oUIaJI

:COUNTIFS - o^^-sl o Jaqjjijj istJ\
=COUNTIFS ( criteria_range1 , criterial , [criteria_range2] , [criteria2] , . )

.JgiJI jU_s2_oJI iU j liiiiLC ^lj 4 >JI JgiJI 0 UL 21 JI ^glku :criteria_range1

.cu_Lc r-liiii-SzJLj(D g-Qjju ^.iJI JgiJI jUs-oJI xriterial

•oJIjJI jIl2_oJI uJ-c ill) Ui^-c ^1 jxJI oJIlll oUL±JI ^Uaj :criteria_range2
c lilr ;ii) ■wlhfn gonn ^iJIyjllll jLpzxJI :criteria2

rilSnl il*SII SI kS i jf LbjIgJ bl_o

.cLllsz-o O-oJ- 6 u_o -Lcl v_jJj_oJI Qq Sj Jig J-oL 5 folg^


:1300 Jl j£ jjjj flrriiljjog Full Time JjoIS (Olgjj jgJLaoj jjjJI juilogoJI jac oLu> :JUo



: 1300Jlg 500 jju flm ulgj ^gljju jjjJI j in Fig ni l _>ac oUu> :JUo



(jlojjj jI .fO :v_ij a_oJI


www.learn-barmaga.com









































































19


Jojj^UI

:SUMIF - i^-bj-uUI &o-^JI

=SUMIF ( range , criteria , [sum_range] )

.Qjls-o kijjju ygo->'i JLs v>-D iJj 60? ' iJ 1 ■ ^ IdLil \Jj SUM QJI^JI JjLc 0 -o o^q

.dJjl&oJI Qj-O-LD 111 aoo^i JLn lndoU-SZ-o dJ ubJI cjUL±JI 0lbjg_dig.bj111H0lbj :range

( fiopl li pgil) doo^vi ^Ij-oJI jLis2-oJI :criteria
.jIlsz-oJI i jo^'i JL=>v_>_b Loi 52 -o-p ^Ij-oJIfoaiiJIo>!ccjjgbiJIciULS-ll^gLbJ g_d>g. fio 1 ? 1 1 ^glbJ :sum_range


:Full Tinne JjoL£d plgjj jgLcmj ujjJI j>nF>Qn)l oulgj ^qivno oUu> :Jlio



:1000JI (QXtulujjO jgbju QjjJI jinbgnll oulgj cgn-\n oUu> : Jlio



. ^glJcu cub g_ri} bg j iiinl l 10 Ib iI Iyl±> JingM 3 SUMIF d-bLsz-o U-o OJlill JnJI J.C ilixliiiiJI lii'So.i


ULojjj j-Li-oJI .fO :v_ij^_oJI


www.learn-barmaga.com





















































































20 Jojj^UI ^U>j ^ ojujjLjjIjI cjUIaJI

rSUMIFS - ozislLq Jaqj-i u £-o-^JI

=SUMIFS ( sum_range , criteria_range1 , criterial , [criteria_range2] , [criteria2] . . )

.Jajjjj y_o )is\ LLi^J ijlii JLa v>6 6-o-^JI fooj Q1

.Jog;in II rioo-Vi JLn^>6 Ujlszjd^ij fogimii v3iiJI cjUL±JI g_d>g^gLbJ :sum_range
.diilAoJI Q 04 oi l A 52 J fog_£j oJn Jo jjjj cU £j 6J LDgju JgiJI kigjjjloJI cjUL±JI 0lku :criteria_range 1

.JgiJI oULiiJI ^gLbJ ^>6 QQQ^'i j^Ij^oJI JgiJI jIlsloJI :criteria 1

.cUjliioJI cLcu-QJI ^-S2_i fDg_Q_i oJ^ oJU JajjjjcU LDgju vjjjiJI gjllll JagjjiLoJI oULiiJI ^gLlcJ :criteria_range 2

.gJlill oULiiJI iJlhJ gMi) aoo^i ^Ij-oJI oJllll jIlsloJI :criteria 2


:Full Tinne Jo IS plgjj j gino; jjjJl jgSjJI o>nF>gn)l oulgj £gn~\f> oUu> :Jlio



:HP ^gj (Jjo (\cUqJI oOglibUI ojuclS oluu> :Jlio



(jlojjj ji-i-oJI .fO :v_jj^_oJI


www.learn-barmaga.com









































































































21 Jojj^UI ^ ojojjLjjIJI cjUIaJI

•Q i Q Jojjju ^.Lc J "> y pJI ljLllld •

pg_ j L_Lrii Ldul UJ SUMIFS g SUMIF i_> i I ~>lr. I qA'mI^iAi fji ; liu AVERAGEIFS g AVERAGEIF OLUI^JI

(5 o7> 11 ij" ; lg J I I» <1 in -v i


:AVERAGEIF - ^_^lq Jqjjjjj J ^ ypJI ljLlll^ QJb
=AVERAGEIF ( range , criteria , [average_range] )

">lg Jcijjjj LuaJ ijlii JLa \^J> J^jg-pJI ljLlll^j fogiiJ

.Jo j_u±JI 0Lku :range
Qoo^'i ^lj_oJI jLlsloJI :criteria
(jjlku :average_range


:Full Time JjoIS plgjj j gino; (j*ntognll oulgj Ja&o olm> ijlio



:AVERAGEIFS - ^_^lg Jajjiij ljLlll^ Qjb

=AVERAGEIFS ( average_range , criteria_range1 , criterial , [criteria_range2] , [criteria2] . . )

.Jaj-LU 0 -o jliit LuaJ ylii JLd kjJ) J~WqJI mImi-ii pgijj

t.i i in "> 0 llaj :average_range
.JgiJI log j hi ni l olilriJI ^Itu :criteria_range1

.JglJI jU-sloJI :criteria1


•oJlill JcigjjiLoJI oULiiJI ^UcJ :criteria_range2

.gJlill jIlsloJI :criteria2



(jlojjj j-ii-oJI .fO :v_j


www.learn-barmaga.com
























































































22


Jojj^UI
:q i i slq cLo U-c Cl^jJI Jlq^ •

Ldllo UO-S2J (_nlj su ml; i~>; i m .. cLu-S-o Q p; b 0 _c Ci_ 3 jJU fogJiJ oj ^ Jlg:> J mMlI ^_oUj_lj ^-pgJ

l »Vi 6 a pg LdyLuLajJ

:LOOKUP Cjj^jJI dJb

=LOOKUP ( lookup_value , lookup_vector , [result_vector] )

Ifftir ciniJI ^1 j_cJI d oj 6 H :lookup_value
.Uidoja dp 16 I I (j_c clzuJLj fogAuii cjUL±JI (jlhJ g_d>g .cuzuJI ^gLbJ :lookup_vector

d_oJ-QJI o^_jpg JLn o—d l *ftip d oiii I I jrhlni in o-UI CiULiiJI iq ! in i g_dig . d ^liil l >>>