Excel Ung Dung

Bé Gi¸o dôc vµ §µo t¹o Tr­êng §¹i häc Má- §Þa chÊt

Tin häc øng dông trong kinh tÕ – Qu¶n trÞ doanh nghiÖp

Biªn säan: Ngäc

NguyÔn ThÞ BÝch

Môc tiªu, ®èi t­îng vµ ph­¬ng ph¸p nghiªn cøu m«n häc Muc tiªu: Cung cÊp cho häc viªn c¬ së lý luËn vµ c«ng cô tin häc ®Ó xö lý c¸c vÊn ®Ò n¶y sinh trong qu¸ tr×nh qu¶n trÞ häat ®éng s¶n xuÊt kinh doanh cña doanh nghiÖp. §èi t­îng nghiªn cøu:  C¸c phÇn mÒm øng dông trong lÜnh vùc qu¶n trÞ kinh doanh  §i s©u nghiªn cøu c¸c øng dông cña phÇn mÒm Microsoft Excel  C¸c bµi tãan lý thuyÕt vµ c¸c vÊn ®Ò thùc tiÔn n¶y sinh trong H§SXKD Ph­¬ng ph¸p nghiªn cøu

Néi dung nghiªn cøu m«n häc

Bµi 1: Tæng quan vÒ M.E Bµi 2: NhËp d÷ liÖu Bµi 3: TÝnh tãan d÷ liÖu Bµi 4: §Þnh d¹ng b¶ng tÝnh trong M.E. Bµi 5: VÏ ®å thÞ trong M.E. Bµi 7: C¬ së d÷ liÖu vµ kÕt xuÊt th«ng tin tõ CSDL Bµi 6: C¸c c«ng cô ph©n tÝch d÷ liÖu thèng kª Bµi 8: Cµi ®Æt m«i tr­êng lµm viÖc cña M.E.

Bµi 1: Tæng quan vÒ M.E Giíi thiÖu vÒ M.E C¸c yªu cÇu vÒ phÇn cøng Cµi ®Æt M.E. Chøc n¨ng cña M.E. – – – – – –

NhËp, l­u tr÷ vµ xö lý d÷ liÖu TÝnh tãan d÷ liÖu LËp c¸c b¸o c¸o kÕt xuÊt th«ng tin VÏ ®å thÞ Ph©n tÝch d÷ liÖu xö lý thèng kª Tù ®éng hãa c¸c c«ng viÖc th­êng nµy

Vµo – ra, më, ghi file d÷ liÖu CÊu tróc giao diÖn cña M.E. 4

Vµo – ra, më, ghi file d÷ liÖu Khëi ®éng Excel: – – –

Menu: Start/Program/Microsoft Excel KÝch chuét vµo biÓu t­îng Excel trªn desktop ...

KÕt thóc c«ng viÖc víi Excel – – –

Menu: File/Exit KÝch chuét vµo nót “Close” Dïng tæ hîp phÝm: Ctrl+F4

Më file míi: – – –

Menu: File/New KÝch chuét vµo nót lÖnh: “Open” Dïng tæ hîp phÝm: Ctrl+N

Ghi file vµo bé nhí ngoµi: Ph©n biÖt 2 kiÓu ghi – –

■

LÖnh Save – dïng ®Ó ghi lÇn ®Çu tiªn sau khi t¹o lËp file hoÆc ghi lÇn 2 trë ®i nh­ng kh«ng thay ®æi c¸c thuéc tÝnh cña file LÖnh Save As... – dïng ®Ó lùa chän nh÷ng thuéc tÝnh míi cho file khi ghi lÇn thø 2 trë ®i

§ãng file: File/Close 5

C¸c thuéc tÝnh cña file d÷ liÖu N¬i l­u tr÷ file Save in: Tªn file – File name KiÓu file – Type of file File ®­îc b¶o vÖ hay kh«ng – Protection... 6

bµi 2: NhËp d÷ liÖu 1. KiÓu d÷ liÖu 2. X¸c ®Þnh « tÝnh, miÒn « tÝnh, d÷ liÖu 3. NhËp d÷ liÖu 4. §iÒn ®Çy d÷ liÖu cho miÒn « 5. Copy – Cut (c¾t) vµ Paste (d¸n) d÷ liÖu and Paste Special 6. Xãa d÷ liÖu 7. T×m kiÕm, thay thÕ d÷ liÖu 7

d¹ng d÷ liÖu D÷ liÖu d¹ng v¨n b¶n (Text)

■ – –

VÝ dô: “NguyÔn V¨n An” L­u ý víi d÷ liÖu d¹ng v¨n b¶n: ■

■

■

- Khi c¸c tãan h¹ng trong c«ng thøc hoÆc tham sè trong c¸c hµm sè lµ d÷ liÖu d¹ng v¨n b¶n th× chóng ph¶i ®­îc ®Æt trong dÊu nh¸y (‘’). - Muèn sö dông ch÷ sè ®Ó biÓu diÕn d÷ liÖu d¹ng v¨n b¶n th× ph¸i ®Æt dÊu (‘) tr­íc khi nhËp d÷ liÖu vµo « tÝnh - Theo ngÇm ®Þnh d÷ liÖu d¹ng v¨n b¶n c¨n th¼ng tr¸i trong « tÝnh. §©y lµ mét dÊu hiÖu ®Ó nhËn biÕt d¹ng d÷ liÖu

8

d¹ng d÷ liÖu (tiÕp) D÷ liÖu d¹ng sè (Number)

■ – – –

D÷ liÖu d¹ng sè häc (Numeric) D÷ liÖu d¹ng ngµy-th¸ng (Date): L­u ý: ■

■

■

DÊu ph©n c¸ch gi÷a phÇn nguyªn vµ phÇn thËp ph©n cña 1 sè. C¸ch thÓ hiÖn d÷ liÖu d¹ng ngµy th¸ng (th¸ng/ngµy/n¨m) Theo ngÇm ®Þnh d÷ liÖu d¹ng sè sÏ ®­îc c¨n ph¶i khi nhËp d÷ liÖu vµo « tÝnh.

9

d¹ng d÷ liÖu (tiÕp) D÷ liÖu d¹ng c«ng thøc (Formular)

■ – –

D÷ liÖu d¹ng c«ng thøc ®­îc b¾t ®Çu bëi dÊu (=) Trong c«ng thøc bao gåm: ■ ■

C¸c tãan tö: + ; - ; * ; / ; ^ C¸c tãan h¹ng, cã thÓ lµ: – –

–

–

H»ng sè: VÝ dô: =2*3 Tham chiÕu, bao gåm: • Tham chiÕu lµ ®Þa chØ «: VÝ dô: =B3+B4+8 • Tham chiÕu lµ tªn miÒn, vÝ dô: =6%*Thu_nhap Hµm sè, vÝ dô: =6%*Sum(B2:B20)

L­u ý: ■

C«ng thøc ®­îc thÓ hiÖn trªn thanh c«ng thøc, cßn kÕt qu¶ cña c«ng thøc thÓ hiÖn trong « tÝnh

10

X¸c ®Þnh miÒn « X¸c ®Þnh miÒn « liÒn kÒ

■ – –

–

§­a chuét lªn gãc tr¸i trªn miÒn « cÇn x¸c ®Þnh, gi÷ vµ rª tíi gãc ph¶i d­íi cña miÒn « §­a con trá « tíi « gãc tr¸i trªn cña miÒn cÇn x¸c ®Þnh, gi÷ phÝm Shift+mòi tªn lªn, xuèng, tr¸i ph¶i ®Ò di chuyÓn con trá « tíi gãc ph¶i cña miÒn cÇn x¸c ®Þnh §­a con trá « (nh¾p chuét) ë « gãc tr¸i trªn cña miÒn cÇn x¸c ®Þnh, gi÷ phÝm Shift+nh¾p chuét vµo « gãc ph¶i d­íi cña miÒn cÇn x¸c ®Þnh.

X¸c ®Þnh miÒn « kh«ng liÒn kÒ

■ –

T­¬ng tù nh­ trªn nh­ng gi÷ phÝm Ctrl ®ång thêi khi thao t¸c.

11

X¸c ®Þnh dßng, cét, trang tÝnh ■

■

■

■

■

X¸c ®Þnh dßng liÒn kÒ – Nh¾p chuét t¹i tiªu ®Ò dßng, gi÷ vµ rª ®Ó x¸c ®Þnh nhiÒu dßng liÒn kÒ X¸c ®Þnh dßng kh«ng liÒn kÒ – T­¬ng tù nh­ trªn nh­ng gi÷ phÝm Ctrl ®ång thêi khi thao t¸c. X¸c ®Þnh cét liÒn kÒ – Nh¾p chuét t¹i tiªu ®Ò cét, gi÷ vµ rª ®Ó x¸c ®Þnh nhiÒu dßng liÒn kÒ X¸c ®Þnh cét kh«ng liÒn kÒ – T­¬ng tù nh­ trªn nh­ng gi÷ phÝm Ctrl ®ång thêi khi thao t¸c. X¸c ®Þnh trang tÝnh – Nh¾p chuét vµo « lµ giao cña tiªu ®Ò dßng vµ tiªu ®Ò cét

12

NhËp d÷ liÖu ■

■

NhËp d÷ liÖu cho tõng « tÝnh – §­a con trá « vÒ n¬i cÇn nhËp, gâ d÷ liÖu, x¸c ®Þnh d÷ liÖu nhËp NhËp cïng d÷ liÖu cho miÒn nhiÒu « ■ ■ ■

–

HoÆc ■ ■

■

X¸c ®Þnh miÒn « cÇn nhËp d÷ liÖu Gâ d÷ liÖu NhÊn tæ hîp phÝm Ctrl+Enter Gâ d÷ liÖu cÇn nhËp vµo « ®Çu Menu: Edit/Fill Down (Left ; Right; Up)

NhËp d·y d÷ liÖu (serie d÷ liÖu) – Gâ gi¸ trÞ ®Çu tiªn cña d·y d÷ liÖu – Menu: Edit/Fill/Serie 13

Copy-cut-Paste d÷ liÖu 1.

X¸c ®Þnh miÒn d÷ liÖu cÇn Copy hoÆc Cut 1. Chän 1 trong c¸c c¸ch sau ®Ó thùc hiÖn lÖnh Copy ■ NhÊn tæ hîp phÝm: Ctrl+C ■ Menu: Edit/Copy ■ Nh¾p chuét vµo nót lÖnh “Copy” trªn thanh c«ng cô chuÈn ■ KÝch chuét ph¶i ë vÞ trÝ d÷ liÖu ®· x¸c ®Þnh ®Ó kÝch häat Menu t¾t, chän lÖnh Copy 2. Chän 1 trong c¸c c¸ch sau ®Ó thùc hiÖn lÖnh Cut ■ NhÊn tæ hîp phÝm: Ctrl+X ■ Menu: Edit/Cut ■ Nh¾p chuét vµo nót lÖnh “Cut” trªn thanh c«ng cô chuÈn ■ KÝch chuét ph¶i ë vÞ trÝ d÷ liÖu ®· x¸c ®Þnh ®Ó kÝch häat Menu t¾t, chän lÖnh Cut 3. §­a con trá « vÒ vÞ trÝ cÇn d¸n, chän 1 trong c¸c c¸ch sau ®Ó thùc hiÖn lÖnh Paste ■ NhÊn tæ hîp phÝm: Ctrl+V ■ Menu: Edit/Paste ■ Nh¾p chuét vµo nót lÖnh “Paste” trªn thanh c«ng cô chuÈn ■ KÝch chuét ph¶i ë vÞ trÝ cÇn d¸n ®Ó kÝch häat Menu t¾t, chän lÖnh Paste 4. LÖnh Paste Special 1. §­a con trá tíi vïng cÇn d¸n 14 2. Chän: Menu: Edit/Paste Special

Sao chÐp hoÆc di chuyÓn trang tÝnh ■ ■

3.

§Æt con trá ë « bÊt kú trong trang tÝnh nguån Menu: Edit/Move or Copy sheet 1. Chän Create a Copy ®Ó t¹o b¶n Copy ■ Kh«ng chän: Di chuyÓn 2. To Book: chän file (book) n¬i b¹n muèn di chuyÓn hoÆc Copy (Chän n¬i ®Ých) – NÕu ®Ých lµ mét File kh¸c th× File Êy ph¶i ®ang ®­îc më hoÆc b¹n cã thÓ chän ®Ých lµ mét File míi 3. Before sheet: Chän vÞ trÝ ®øng cña b¶n Copy hoÆc b¶n ®­îc di chuyÓn tíi

C¸ch kh¸c: Gi÷ vµ rª chuét vµo tªn trang tÝnh ®ång thêi gi÷ phÝm Ctrl

15

xãa d÷ liÖu Xãa néi dung trong « tÝnh

■ – – –

X¸c ®Þnh miÒn d÷ liÖu cÇn xãa NhÊn phÝm Delete Víi lÖnh nµy chØ d÷ liÖu trong miÒn « bÞ xãa

Xãa «, miÒn «, cét, hoÆc xãa dßng

■ – –

X¸c ®Þnh miÒn « cÇn xãa Menu: Edit/Delete hoÆc KÝch häat Menu t¾t, chän Delete ■ ■ ■ ■

Shift Cells Left: Xãa khèi « vµ dÞch nh÷ng « cßn l¹i sang tr¸i Shift Cells Upt: Xãa khèi « vµ dÞch nh÷ng « cßn l¹i lªn Entire Row: Xãa toµn bé nh÷ng dßng cã chøa khèi « ®ang x¸c ®Þnh Entire Column: Xãa toµn bé nh÷ng cét cã chøa khèi « ®ang x¸c ®Þnh

Clear

■ – –

X¸c ®Þnh miÒn « cÇn xãa Chän menu: Edit/Clear vµ lùa chän: ■ ■ ■

All: Xãa tÊt tÇn tËt Formats: Xãa nh÷ng thuéc tÝnh vÒ ®Þnh d¹ng Contents: Xãa néi dung cña miÒn « (t­¬ng ®­¬ng víi viÖc

16

Bµi 4: §Þnh d¹ng b¶ng tÝnh

1. 2. 3. 4. 5.

§Þnh d¹ng « tÝnh §Þnh d¹ng dßng §Þnh d¹ng cét §Þnh d¹ng trang tÝnh Sö dông mÉu thiÕt kÕ s½n ®Ó ®Þnh d¹ng 6. §Þnh d¹ng b»ng thÎ kiÓu 17

§Þnh d¹ng (format) ■

■

§Þnh d¹ng « tÝnh – §Þnh d¹ng d÷ liÖu d¹ng sè (number) – C¨n d÷ liÖu trong « (Alignment) – §Þnh d¹ng font ch÷ (Font) – §Þnh d¹ng ®­êng kÎ khung (Border) – §Þnh d¹ng nÒn cña « tÝnh (Parttent) – B¶o vÖ d÷ liÖu cho « (Protection) §Þnh d¹ng dßng (Format/Row) ■ ■

X¸c ®Þnh dßng (nhiÒu dßng) cÇn nhËp d÷ liÖu Format/Row – – – –

■

ChiÒu cao dßng (Height) Tù ®éng thay ®æi chiÒu cao phï hîp víi d÷ liÖu trong dßng (Autofit) Èn dßng (Hide) T¸i hiÖn nh÷ng dßng ®ang bÞ Èn: Unhide

§Þnh d¹ng cét (t­¬ng tù) 18

§Þnh d¹ng (format) ■

(tiÕp)

§Þnh d¹ng trang tÝnh – §æi tªn trang tÝnh (Rename sheet) – ¢n trang tÝnh: Menu: Format/Sheet/Hide – T¸i hiÖn trang tÝnh: Menu: Format/Sheet/Unhideh

Sö dông c«ng cô ®Þnh d¹ng cã s½n

■ – –

X¸c ®Þnh miÒn cÇn ®Þnh d¹ng Menu: Format/Autoformat

19

§Þnh d¹ng trang in Menu: File/Page Setup

■ –

Cµi ®Æt trang in (Page): ■ ■

Chän h­íng in (Orientation): Portrait: in däc trang giÊy; Lanscape: In ngang trang giÊy §iÒu chØnh mËt ®é in (Scalling): Ajust To: 100% ®Ó thu nhá hoÆc phãng to khi in –

■ ■ ■

–

Lùa chän kÝch cì giÊy in: Paper size ChÊt l­îng in (Print Quality): §¸nh sè trang in ®Çu tiªn (First Page Number)

Cµi ®Æt lÒ trang in: ■ ■

–

Top (lÒ trªn; Bottom (lÒ d­íi); Left (lÒ tr¸i); Right (lÒ ph¶i) Lùa chän néi dung in cã ®­îc in ë gi÷a trang (theo chiÒu däc hoÆc theo chiÒu ngang hay kh«ng? Center on Page

Cµi ®Æt tiªu ®Ò trªn (d­íi) cho mçi trang in – –

–

Tù ®éng ®iÒu chØnh cho võa sè trang in: Fit to…. wide and …. tall

Header Footer

Cµi ®Æt thuéc tÝnh cho trang tÝnh khi in ■ ■

X¸c ®Þnh miÒn in (Print Area) In nh¾c l¹i dßng ®Çu ®Ò trªn ®Çu mçi trang in, hoÆc cét ®Çu tiªn bªn tr¸i mçi trang in – –

Row to Repeat at top Column to Repeat at Left

20

Cµi ®Æt in Menu: File/Print

■ –

MiÒn in (Print Range) ■ ■

–

X¸c ®Þnh sè b¶n in (Copies) ■

–

In tÊt c¶ c¸c trang in (All) Lùa chän in mét sè trang nhÊt: Page(s) From…. To …. Lùa chän tr×nh tù in khi in nhiÒu b¶n (Collate)

Lùa chän ph¹m in ■ ■ ■

In phÇn trang tÝnh ®· ®­îc x¸c ®Þnh (Selection) – Ph¶i x¸c ®Þnh miÒn cÇn in tr­íc khi chän lÖnh File/Print In trang tÝnh hiÖn thêi (Active Sheet) In tÊt c¶ c¸c trang tÝnh trong Workbook (file): Entire Workbook

21

Bµi 3: TÝnh tãan víi b¶ng tÝnh C¸c tãan tö vµ tãan h¹ng trong c«ng thøc

1. – –

C«ng thøc ®­îc b¾t ®Çu bëi dÊu (=) Trong c«ng thøc bao gåm: ■ ■

C¸c tãan tö: + ; - ; * ; / ; ^ C¸c tãan h¹ng, cã thÓ lµ: – –

–

–

H»ng sè: VÝ dô: =2*3 Tham chiÕu, bao gåm: • Tham chiÕu lµ ®Þa chØ «: VÝ dô: =B3+B4+8 • Tham chiÕu lµ tªn miÒn, vÝ dô: =6%*Thu_nhap Hµm sè, vÝ dô: =6%*Sum(B2:B20)

L­u ý: ■

C«ng thøc ®­îc thÓ hiÖn trªn thanh c«ng thøc, cßn kÕt qu¶ cña c«ng thøc thÓ hiÖn trong « tÝnh

22

C¸c d¹ng tham chiÕu trong c«ng thøc 1.

Theo tÝnh chÊt cè ®Þnh khi tham chiÕu ■ ■ ■ ■

2.

Tham chiÕu lµ tªn miÒn, VÝ dô: ■

3.

Tham chiÕu t­¬ng ®èi: VÝ dô: =B2*B3 Tham chiÕu tuyÖt ®èi: VÝ dô: =$C$12*F4 Tham chiÕu hçn hîp: =$C12*F14 L­u ý: §Ó chuyÓn tõ ®Þa chØ t­¬ng ®èi thµnh ®Þa chØ tuyÖt ®èi sö dông phÝm F4 khi nhËp tham chiÕu =Ngµy_c«ng*Tæng_hÖ_sè

Theo tÇm xa cña tham chiÕu ■ ■ ■

Tham chiÕu 1 chiÒu: C24 Tham chiÕu 2 chiÒu: “Sheet1!C14” Tham chiÕu 3 chiÒu: VÝ dô: [VD_luong_ht]Lg_thg_1!C17” 23

G¸n tªn cho miÒn « ■ ■

X¸c ®Þnh miÒn « cÇn g¸n tªn Menu: Insert/Name/Define ■ ■

■

Names in workbook: X¸c nhËn tªn miÒn Tªn miÒn tham chiÕu tíi ®©u (Refers To:)

Insert/Name/Create - ®Ó g¸n tªn cho miÒn bëi nh÷ng nh·n (labels) cã s½n trªn trang tÝnh 1. 2.

X¸c ®Þnh miÒn cÇn g¸n tªn (Trong miÒn ®ã ph¶i cã chøa c¸c nh·n) Menu: Insert/Name/Create

24

NhËp c«ng thøc ■ ■ ■

4.

5.

X¸c ®Þnh « hä¨c miÒn « cÇn cÇn nhËp c«ng thøc Gâ dÊu (=); ngo¹i trõ c«ng thøc ®­îc b¾t ®Çu bëi dÊu (+) hoÆc dÊu (-) hoÆc chÌn hµm sè NhËp c¸c tham chiÕu vµ c¸c tãan tö ■ NhËp tham chiÕu: ■ NÕu tham chiÕu lµ ®Þa chØ «:§­a chuét ®Ó x¸c ®Þnh miÒn tham chiÕu ■ NÕu tham chiÕu lµ tªn miÒn: ■ NhËp c¸c tãan tö: Sö dông nhãm phÝm NumLock ®Ó nhËp c¸c tãan tö NÕu nhËp cïng mét c«ng thøc cho miÒn cã nhiÒu « th×… ph¶i nhÊn tæ hîp phÝm Ctrl+Enter. NÕu chØ nhËp gi÷ liÖu cho 1 «: Enter Cã thÓ sö dông thao t¸c Copy- Paste ®Ó sao chÐp c«ng thøc cña « tíi nh÷ng « kh¸c ■ HÕt søc l­u ý tham chiÕu tuyÖt ®èi vµ tham chiÕu t­¬ng ®èi trong c«ng thøc ■ Tham chiÕu lµ tªn miÒn lu«n lu«n lµ tham chiÕu tuyÖt ®èi 25

hµm sè (function) Có ph¸p chung:[Tªn hµm]([c¸c ®èi sè cña hµm])

Trong ®ã: 1.

2.

Tªn hµm: Do M.E ®Æt, ng­êi sö dông kh«ng ®­îc tù ý thay ®æi, viÕt tªn hµm kh«ng cã dÊu c¸ch, trong tªn hµm kh«ng ph©n biÖt ch÷ viÕt th­êng vµ ch÷ viÕt hoa. C¸c ®èi sè cña hµm: 1. Lu«n ®­îc ®Æt trong dÊu () 2. C¸c ®èi sè cña hµm ®­îc ph©n c¸ch nhau bëi dÊu ph©n c¸ch, gi÷a c¸c ®èi sè kh«ng cã dÊu c¸ch. 3. C¸c ®èi sè cã thÓ lµ: ■ H»ng sè: VÝ dô ■ Tham chiÕu, vÝ dô: =Sum(Thu_nhap) ■ Hµm sè (Nested Function) ■ C¸c ®èi sè cã thÓ tïy chän hoÆc b¾t buéc ■ Mét hµm sè cã thÓ kh«ng cã ®èi sè, hoÆc cã thÓ cã tíi 30 ®èi sè hoÆc kh«ng cã ®èi sè 26

Mét sè hµm cô thÓ C¸c hµm tãan häc (Math and Trig)

I. ■

Hµm tÝnh tæng: Sum(Number1,Number2,…) VÝ dô: =Sum(2;3;4)  9 =sum(ngµy_c«ng;89;1)340

■

Hµm tÝnh tæng theo ®iÒu kiÖn: Sumif(Range,Criteria,SumRange) VÝ dô: =SumIf(Ten_hang;”Than c¸m5”;Sè_l­îng)- hµm tÝnh tæng sè l­îng than c¸m

■

Hµm tÝnh gi¸ trÞ tuyÖt ®èi cña 1 sè: ABS(Number) VÝ dô: =ABS(-5)  5

■

Hµm lÊy phÇn nguyªn cña mét sè: INT(Number) VÝ dô: =INT(5.78) 5

■

Hµm lµm trßn sè: ROUND(Number,Num_digits) Trong ®ã: Number lµ sè cÇn lµm trßn; Num_digits lµ sè ch÷ sè lµm trßn. NÕu Num_digits lµ sè ©m, hµm sÏ lµm trßn c¸c ch÷ sè phÇn nguyªn; nÕu lµm trßn phÇn thËp ph©n, nÕu Num_digits b»ng 0 hµm cho ra phÇn nguyªn cña sè ®ã. VÝ dô: =Round(4.5354,1) 4.5

■

Hµm tÝnh tÝch: PRODUCT(Number1, Number2,…) VÝ dô: =PRODUCT(2,3,4)24 27

Mét sè hµm cô thÓ (tiÕp) C¸c hµm tãan häc (Math and Trig) (tiÕp)

I. ■

Hµm tÝnh tæng cña c¸c tÝch: SUMPRODUCT(Array1,Array2,…) KÕt qu¶ cña hµm lµ tæng cña tÝch c¸c phÇn tö t­¬ng øng cña c¸c m¶ng aray1; aray2;…) VÝ dô: =SUMPRODUCT(Don_gia;So_luong) sÏ cho ra kÕt qu¶ lµtæng doanh sè b¸n hµng víi 2 miÒn chøa th«ng tin vÒ gi¸ b¸n vµ sè l­îng hµng b¸n cña mçi ®ît giao hµng.

■

Hµm lµm trßn: EVEN(Number) Hµm lµm trßn lªn nÕu ®èi sè lµ sè d­¬ng vµ lµm trßn xuèng nÕu ®èi sè lµ sè ©m tíi sè nguyªn ch½n gÇn nhÊt. VÝ dô: =EVEN(2.4) 4 vµ =EVEN(-3,2)-4

■

Hµm tÝnh b×nh ph­¬ng cña 1 sè: EXP(Number) VÝ dô: =ABS(-5)  25

■

Hµm tÝnh c¨n bËc 2 cña mét sè: SQRT(Number) VÝ dô: =SQRT(9) 3

■

Hµm tÝnh phÇn d­ cña phÐp chia: MOD(Number,Divisor) VÝ dô: =MOD(7,2) 1

Hµm tÝnh ®Þnh thøc cña ma trËn: MDETERM(Array) Hµm tÝnh ma trËn nghÞch ®¶o cña mét ma trËn: MINVERSE(Array) ■ Hµm tÝnh ma trËn tÝch cña 2 ma trËn: MMULT(Array1,Array2) L­u ý: C¸c hµm lµm viÖc víi ma trËn chØ cho kÕt qu¶ khi c¸c ®èi sè cña hµm ph¶i tháa m·n ®iÒu kiÖn tÝnh ®­îc. C¸c hµm lµm viÖc víi ma trËn lµ nh÷ng c«ng thøc m¶ng nªn khi nhËp c«ng thøc (hµm) vµo « tÝnh ph¶i gi÷ ®ång thêi tæ hîp phÝm: Crtl+Shift+Enter ■ ■

28

Mét sè hµm cô thÓ (tiÕp) C¸c hµm tãan häc (Math and Trig) (tiÕp)

I. ■

Hµm n©ng mét sè lªn lòy thõa: POWER(Number,Power) VÝ dô: =POWER(2,3)  8

■

Hµm tÝnh b×nh ph­¬ng cña 1 sè: EXP(Number) VÝ dô: =ABS(-5)  25

■

Hµm tÝnh c¨n bËc 2 cña mét sè: SQRT(Number) VÝ dô: =SQRT(9) 3

■

Hµm tÝnh phÇn d­ cña phÐp chia: MOD(Number,Divisor) VÝ dô: =MOD(7,2) 1

■

Hµm tÝnh tæng b×nh ph­¬ng cña c¸c sè trong danh s¸ch ®èi sè cña hµm: SUMSQ(Number1, Number2, …) VÝ dô: =SUMSQ(6,9, 10) 217

■

Hµm tÝnh giai thõa cña mét sè: FACT(Number)

29

Mét sè hµm cô thÓ (tiÕp) C¸c hµm logic

I.

Hµm xÐt ®iÒu kiÖn: IF(Logical_test,Value_if_true,Value_if_False ) Trong ®ã: Logical_Test: biÓu thøc logic (biÓu thøc chØ cho ra gi¸ trÞ ®óng (True or False) ®Ó ®­a ra ®iÒu kiÖn kiÓm tra. Value_if_true: Gi¸ trÞ mµ hµm IF ®­a ra nÕu biÓu thøc logic lµ True (®óng) Value_if_False: Gi¸ trÞ mµ hµm IF ®­a ra nÕu biÓu thøc logic lµ False (sai) ■

30

Mét sè hµm cô thÓ (tiÕp) I.

C¸c hµm logic (tiÕp) Hµm xÐt ®iÒu kiÖn ®ång thêi: AND(Logical1, Logical2, Logical3,…) Trong ®ã: Logical1, Logical2, Logical3 là nh÷ng biÓu thøc logic. NÕu tÊt c¶ c¸c biÓu thøc logic ®Òu ®óng (True) th× hµm AND sÏ cho kÕt qu¶ lµ ®óng (True) NÕu mét trong sè c¸c biÓu thøc logic lµ sai (False) th× hµm AND sÏ cho kÕt qu¶ lµ sai (False) 1.

31

Mét sè hµm cô thÓ (tiÕp) I.

C¸c hµm logic (tiÕp) Hµm xÐt mét trong c¸c ®iÒu kiÖn: OR(Logical1, Logical2, Logical3,…) Trong ®ã: Logical1, Logical2, Logical3,… là nh÷ng biÓu thøc logic. NÕu mét trong c¸c biÓu thøc logic ®Òu ®óng (True) th× hµm OR sÏ cho kÕt qu¶ lµ ®óng (True) NÕu tÊt c¶ c¸c biÓu thøc logic lµ sai (False) th× hµm OR sÏ cho kÕt qu¶ lµ sai (False) ■ Hµm xÐt mét trong c¸c ®iÒu kiÖn: NOT(Logical) 1.

Hµm ®­a ra gi¸ trÞ nghÞch ®¶o cña biÓu thøc logic VÝ dô: =NOT(3>1)False

32

Mét sè hµm cô thÓ (tiÕp) Hµm t×m kiÕm vµ tham chiÕu (Lookup and Reference)

I.

Hµm MATCH: MATCH(lookup_value,Lookup_aray,Match_type) Hµm ®­a ra vÞ trÝ cña phÇn tö Trong ®ã: Lookup_Value là gi¸ trÞ mµ hµm MATCH t×m kiÕm trong miÒn Lookup_aray. Lookup_aray lµ miÒn t×m kiÕm cña hµm MATCH Match_type: KiÓu t×m kiÕm. M.E quy ®Þnh kiÓu t×m kiÕmbëi c¸c gi¸ trÞ. 1.

NÕu Match_type =1 th× hµm MATCH sÏ t×m ®Õn vÞ trÝ cña phÇn tö cã gi¸ trÞ lín nhÊt nh­ng kh«ng v­ît qu¸ gi¸ trÞ t×m kiÕm. Khi Êy c¸c phÇn tö trong miÒn t×m kiÕm ph¶i ®­îc s¾p xÕp theo thø tù t¨ng dÇn. NÕu Match_type =-1 th× hµm MATCH sÏ t×m ®Õn vÞ trÝ cña phÇn tö cã gi¸ trÞ nhá nhÊt nh­ng kh«ng nhá h¬n gi¸ trÞ t×m kiÕm. Khi Êy c¸c phÇn tö trong miÒn t×m kiÕm ph¶i ®­îc s¾p xÕp theo thø tù gi¶m dÇn. NÕu Match_type = 0 th× hµm MATCH sÏ t×m ®Õn vÞ trÝ ®Çu tiÒn cña phÇn tö cã gi¸ trÞ chÝnh x¸c b»ng gi¸ t×m kiÕm. NÕu kh«ng t×m thÊy th× hµm MACH sÏ

33

Mét sè hµm cô thÓ (tiÕp) Hµm t×m kiÕm vµ tham chiÕu (Lookup and Reference) (tiÕp

I. 1.

Hµm Hlookup:

HLOOKUP(Lookup_value,Table_aray,Row_index_num,Ran ge_lookup) Hµm ®­a ra vÞ trÝ cña phÇn tö Trong ®ã: Lookup_Value là gi¸ trÞ mµ hµm HLOOKUP t×m kiÕm trong miÒn Table_aray. Table_aray lµ b¶ng t×m kiÕm cña hµm HLOOKUP. B¶ng nµy ph¶i cã Ýt nhÊt lµ 2 dßng. Hµm HLOOKUP sÏ tiÕn hµnh t×m kiÕm ë dßng ®Çu tiªn phÇn tö cã gi¸ trÞ tháa m·n Lookup_Value Row_index_num chØ sè dßng mµ hµm HLOOKUP sÏ lÊy kÕt qu¶ nÕu nã t×m thÊy phÇn tö t­¬ng øng trong dßng ®Çu tiÒn cña miÒn b¶ng t×m kiÕm tháa m·n gi¸ trÞ t×m kiÕm Range_Lookup: lµ mét gi¸ logic quy ®Þnh kiÓu t×m kiÕm NÕu Range_lookup =True (hoÆc bá qua) th× hµm Hlookup sÏ t×m ®Õn vÞ trÝ cña phÇn tö cã gi¸ trÞ lín nhÊt nh­ng kh«ng v­ît qu¸ gi¸ trÞ t×m kiÕm. Khi Êy c¸c phÇn tö trong dßng ®Çu tiªn cña b¶ng t×m kiÕm ph¶i ®­îc s¾p xÕp theo thø tù t¨ng dÇn.

34

Mét sè hµm cô thÓ (tiÕp) Hµm t×m kiÕm vµ tham chiÕu (Lookup and Reference) (tiÕp)

I. 1.

Hµm Vlookup:

VLOOKUP(Lookup_value,Table_aray,Col_index_num,Rang e_lookup) Hµm ®­a ra vÞ trÝ cña phÇn tö Trong ®ã: Lookup_Value là gi¸ trÞ mµ hµm VLOOKUP t×m kiÕm trong cét ®Çu tiªn miÒn Table_aray. Table_aray lµ b¶ng t×m kiÕm cña hµm VLOOKUP. B¶ng nµy ph¶i cã Ýt nhÊt lµ 2 cét. Hµm VLOOKUP sÏ tiÕn hµnh t×m kiÕm ë cét ®Çu tiªn phÇn tö cã gi¸ trÞ tháa m·n Lookup_Value Col_index_num chØ sè dßng mµ hµm VLOOKUP sÏ lÊy kÕt qu¶ nÕu nã t×m thÊy phÇn tö t­¬ng øng trong cét ®Çu tiÒn cña miÒn b¶ng t×m kiÕm tháa m·n gi¸ trÞ t×m kiÕm Range_Lookup: lµ mét gi¸ logic, quy ®Þnh kiÓu t×m kiÕm NÕu Range_lookup =True (hoÆc bá qua) th× hµm Hlookup sÏ t×m ®Õn vÞ trÝ cña phÇn tö cã gi¸ trÞ lín nhÊt nh­ng kh«ng v­ît qu¸ gi¸ trÞ t×m kiÕm. Khi Êy c¸c phÇn tö trong cét ®Çu tiªn cña b¶ng t×m kiÕm ph¶i ®­îc s¾p xÕp theo thø tù t¨ng dÇn. NÕu Range_lookup =False (0) th× hµm HLOOKUP sÏ t×m ®Õn vÞ

35

Mét sè hµm cô thÓ (tiÕp) I.

Hµm t×m kiÕm vµ tham chiÕu (Lookup and Reference) (tiÕp) 1.

Hµm INDEX:

INDEX(Array,Row_index_num,Col_index_nu m) Hµm ®­a ra gÝa trÞ cña phÇn tö lµ giao cña chØ sè dßng vµ chØ sè cét trong miÒn Array Trong ®ã: Array lµ m¶ng c¸c phÇn tö. M¶ng nµy cã thÓ lµ m¶ng 1 chiÒu hoÆc m¶ng 2 chiÒu. Row_index_num chØ sè dßng mµ hµm INDEX sÏ tham chiÕu tíi ®Ó lÊy kÕt qu¶. Col_index_num chØ sè cét mµ hµm INDEX sÏ

36

Mét sè hµm cô thÓ (tiÕp) I.

Hµm víi d÷ liÖu v¨n v¶n (Text) 1.Hµm x¸c ®Þnh ®é dµi x©u ký tù: LEN(Text) 2. Hµm c¾t mét sè ký tù tõ bªn tr¸i LEFT(Text,Num_chars) Hµm cho ra mét x©u ký tù b»ng c¸ch c¾t lÊy Num_Chars ký tù tõ bªn tr¸i cña chuçi Text 3. Hµm c¾t mét sè ký tù tõ bªn ph¶i RIGHT(Text,Num_chars) Hµm cho ra mét x©u ký tù b»ng c¸ch c¾t lÊy Num_Chars ký tù tõ bªn ph¶i cña chuçi Text 4. Hµm c¾t mét sè ký tù tõ gi÷a mét x©u ký tù kh¸c MID(Text,Start_Num,Num_chars) Hµm cho ra mét x©u ký tù b»ng c¸ch c¾t lÊy Num_Chars ký tù kÓ tõ vÞ trÝ Start_Num cña chuçi Text 37

Mét sè hµm cô thÓ (tiÕp) I.

Hµm víi d÷ liÖu v¨n v¶n (Text) 5. Hµm chuyÓn x©u ký tù thµnh ch÷ in th­ êng: LOWER(Text) Start_Num cña chuçi Text 6. Hµm chuyÓn x©u ký tù thµnh ch÷ in hoa: UPPER(Text) 7. Hµm chuyÓn d÷ liÖu d¹ng Text sang d÷ liÖu d¹ng sè: VALUE(Text) VÝ dô: Value(“04”)4 38

Mét sè hµm cô thÓ (tiÕp) I.

Hµm víi d÷ liÖu thêi gian (Date and Time) 1. Hµm x¸c ®Þnh ngµy th¸ng: DATE(Year,Month,Day) Hµm ®­a ra ngµy th¸ng hoÆc sè ®¹i diÖn cho m· ngµy th¸ng trong M.E. 2. Hµm x¸c ®Þnh ngµy (th¸ng, N¨m) cña d÷ liÖu d¹ng ngµy th¸ng DAY(Serial_Num); MONTH(Serial_Num); YEAR(Serial_Num); Trong ®ã: Serial_Num- lµ ngµy th¸ng mµ b¹n muèn t×m xem ®ã lµ ngµy nµo trong th¸ng (th¸ng nµo trong n¨m hoÆc n¨m nµo). C¸c d÷ liÖu ngµy th¸ng ph¶i ®­îc nhËp bëi hµm Date hoÆc lµ kÕt qu¶ cña c«ng thøc hoÆc hµm sè 3. Hµm giê (phót, gi©y) cña d÷ liÖu thêi gian

■

HOUR(Serial_Num); MINUTE(Serial_Num); SECOND(Serial_Num); Trong ®ã: Serial_Num- lµ thêi gian mµ b¹n muèn t×m xem ®ã lµ giê nµo trong ngµy (phót, gi©y). C¸c d÷ liÖu thêi gian cã thÓ ®­îc nhËp nh­ mét x©u ký tù víi dÊu “”hoÆc bëi ch÷ sè thËp ph©n hoÆc lµ kÕt qu¶ cña hµm hoÆc c«ng thøc.

39

Mét sè hµm cô thÓ (tiÕp) I.

Hµm víi d÷ liÖu thêi gian (Date and Time) 4. Hµm x¸c ®Þnh ngµy th¸ng, thêi gian hiÖn thêi theo hÖ thèng lÞch cña m¸y tÝnh: TODAY(); NOW() Hµm kh«ng cã ®èi sè. 5. Hµm x¸c ®Þnh thø trong tuÇn: WEEKDAY(Serial_Num,Return_Type) Trong ®ã: Serial_Num- lµ ngµy th¸ng mµ b¹n muèn t×m xem ®ã lµ ngµy thø mÊy trong tuÇn . C¸c d÷ liÖu ngµy th¸ng ph¶i ®­îc nhËp bëi hµm Date hoÆc lµ kÕt qu¶ cña c«ng thøc hoÆc hµm sè Return_Type - C¸c gi¸ trÞ dïng ®Ó quy ®Þnh kiÓu kÕt qu¶ ®­a ra NÕu Return_Type =1 hoÆc bá qua hµm sÏ cho kÕt qu¶ tõ 1 ®Õn 7, trong ®ã sè 1 t­¬ng ®­¬ng víi chñ nhËt vµ… sè 7 lµ ngµy thø 7 NÕu Return_Type =2 hµm sÏ cho kÕt qu¶ tõ 1 ®Õn 7, trong ®ã sè 1 t­¬ng ®­¬ng víi thø 2 vµ… sè 7 lµ

40

Mét sè hµm cô thÓ (tiÕp) I.

Hµm th«ng tin (Information Functions) C¸c hµm th«ng tin cho biÕt th«ng tin vÒ « tÝnh, th«ng tin vÒ d¹ng d÷ liÖu trong « tÝnh; th«ng tin vÒ d¹ng cña lçi c«ng thøc trong « tÝnh, gåm c¸c nhãm hµm sau: 1. Hµm x¸c nhËn d¹ng lçi cña c«ng thøc trong «: ISERR(Value) Hµm x¸c nhËn c«ng thøc trong « cã ph¶i lµ mét trong c¸c lçi sau: #VALUE!: Lçi gi¸ trÞ tÝnh tãan trong c«ng thøc #REF!: Lçi kh«ng t×m thÊy tham chiÕu cho c«ng thøc trong « #DIV/0!: Lçi kh«ng thÓ chia mét sè cho 0 #NUM!: Sè qu¸ lín ®Ó M.E. kh«ng thÓ tÝnh næi #NAME!: Lçi kh«ng nhËn biÕt ®­îc tªn miÒn hoÆc tªn c«ng thøc #NULL!: Th­êng gÆp trong phÐp lÊy giao cña 2 miÒn, nh­ng 2 miÒn nµy l¹i kh«ng giao nhau.

NÕu Value lµ mét trong sè c¸c lçi trªn, ngo¹i trõ lçi #N/A,

41

Mét sè hµm cô thÓ (tiÕp) I.

Hµm th«ng tin (Information Functions) ISERROR(Value) Hµm x¸c nhËn Value cã ph¶i lµ mét trong c¸c lçi sau: #VALUE!: Lçi gi¸ trÞ tÝnh tãan trong c«ng thøc #REF!: Lçi kh«ng t×m thÊy tham chiÕu cho c«ng thøc trong « #DIV/0!: Lçi kh«ng thÓ chia mét sè cho 0 #NUM!: Sè qu¸ lín ®Ó M.E. kh«ng thÓ tÝnh næi #NAME?: Lçi kh«ng nhËn biÕt ®­îc tªn miÒn hoÆc tªn c«ng thøc #NULL!: Th­êng gÆp trong phÐp lÊy giao cña 2 miÒn, nh­ng 2 miÒn nµy l¹i kh«ng giao nhau. #N/A!: Lçi kh«ng t×m thÊy trong hµm t×m kiÕm vµ tham chiÕu

NÕu Value lµ mét trong sè c¸c lçi trªn, hµm sÏ cho gi¸ trÞ logic True, ng­îc l¹i lµ False.

ISNA(Value) Hµm x¸c nhËn gi¸ trÞ (Value) cã ph¶i lµ lçi #N/A hay kh«ng. NÕu Value lµ lçi #N/A, hµm sÏ cho gi¸ trÞ logic

42

Mét sè hµm cô thÓ (tiÕp) I. ■

Hµm th«ng tin (Information Functions) 3. Hµm x¸c nhËn d¹ng gi¸ trÞ hoÆc tham chiÕu (Hµm IS): ISBLANK(value); ISLOGICAL(value); ISNA(value); ISNONTEXT(value); ISNUMBER(value); ISREF(value); ISTEXT(value) C¸c hµm trªn cho kÕt qu¶ lµ True hoÆc False tïy thuéc vµo ®èi sè cña hµm (Value) lµ trèng, gi¸ trÞ lo gic, lçi N/A; kh«ng ph¶i lµ d÷ liÖu d¹ng Text; d÷ liÖu d¹ng sè; lµ d÷ liÖu d¹ng Text…

43

Mét sè hµm cô thÓ (tiÕp) I.

Hµm th«ng tin (Information Functions) 4. Hµm chuyÓn gi¸ trÞ kh«ng ph¶i d¹ng sè vÒ d÷ liÖu d¹ng sè N(value) C¸c gi¸ trÞ ngµy th¸ng chuyÓn vÒ sè seri ®¹i diÖn cho ngµy th¸ng, gi¸ trÞ logic True chuyÓn thµnh 1; cßn nh÷ng d¹ng kh¸c chuyÓn vÒ 0.

5. Hµm x¸c ®Þnh d¹ng d÷ liÖu TYPE(value) Hµm cho ra sè nguyªn ®¹i diÖn cho d¹ng d÷ liÖu: 1 = d÷ liÖu d¹ng sè; 2 = d÷ liÖu d¹ng Text; 4 = gi¸ trÞ d¹ng logic; 16= d÷ liÖu lçi vµ 64= d÷ liÖu d¹ng m¶ng 44

Mét sè hµm cô thÓ (tiÕp) I.

Hµm c¬ së d÷ liÖu (Database Functions) Có ph¸p chung: DFUNCTION(Database;Field;Criteria) Trong ®ã: Database: MiÒn « chøa danh s¸ch hoÆc c¬ së d÷ liÖu Field: Tr­êng ®­îc sö dông ®Ó tÝnh tãan trong hµm sè. §èi sè “Field” cã ®­îc nhËp ë d¹ng nh·n lµ tªn tr­êng (®­îc ®Æt trong dÊu nh¸y “”) hoÆc chØ sè thø tù cña tr­êng trong CSDL. Criteria: MiÒn chøa ®iÒu kiÖn tÝnh tãan cña hµm

45

Mét sè hµm cô thÓ (tiÕp) I.

Hµm c¬ së d÷ liÖu (Database Functions) Mét sè hµm CSDL:

DAVERAGE   Hµm tÝnh gi¸ trÞ trung b×nh cña nh÷ng phÇn tö ®· x¸c ®Þnh trong CSDL DCOUNT   Hµm ®Õm sè « chøa gi¸ trÞ sè trong CSDL DCOUNTA   Hµm ®Õm c¸c « kh«ng trèng trong c¬ së d÷ liÖu DGET   Hµm t¸ch mét b¶n ghi tõ CSDL mµ tháa m·n ®iÒu kiÖn x¸c ®Þnh DMAX    Hµm ®­a ra gi¸ trÞ lín nhÊt cña nh÷ng phÇn tö ®· x¸c ®Þnh trong CSDL DMIN    Hµm ®­a ra gi¸ trÞ nhá nhÊt cña nh÷ng phÇn tö ®· x¸c ®Þnh trong CSDL DPRODUCT   Hµm tÝnh tÝch cña c¸c gi¸ trÞ cña c¸c b¶n ghi trong mét tr­êng cô thÓ mµ tháa m·n ®iÒu kiÖn trong CSDL. DSUM    Hµm tÝnh tæng cña c¸c gi¸ trÞ cña c¸c b¶n ghi trong mét tr­êng cô thÓ mµ tháa m·n ®iÒu kiÖn trong

46

Hµm tµi chÝnh (Financial Functions)

1. Hµm PMT – Hµm tÝnh kho¶n tiÒn ph¶i chi tr¶ ®Òu nhau mçi kú bao gåm c¶ gèc lÉn l·i cho mét kho¶n vay. PMT(Rate;Nper;PV;FV;Type)

Rate – L·i suÊt ph¶i tr¶ cho kho¶n vay tÝnh cho 1 kú Nper – Sè kú chi tr¶ PV – Gi¸ trÞ hiÖn t¹i cña kho¶n vay FV – gi¸ trÞ cßn l¹i trong t­¬ng lai cña kho¶n vay Type – KiÓu chi tr¶. NÕu viÖc chi tr¶ ®­îc thùc hiÖn vµo cuèi mçi kú th× Type =

47

Hµm tµi chÝnh (Financial Functions)

2. Hµm PPMT – Hµm tÝnh kho¶n tiÒn ph¶i chi tr¶ gèc trong kú ®èi víi mét kho¶n vay dùa trªn dßng tiÒn chi tr¶ vµ l·i suÊt cè ®Þnh . PPMT(Rate;Per;Nper;PV;FV;Type) Rate – L·i suÊt ph¶i tr¶ cho kho¶n vay tÝnh cho 1 kú Per – Kú tÝnh kho¶n tiÒn tr¶ gèc Nper – Sè kú chi tr¶ PV – Gi¸ trÞ hiÖn t¹i cña kho¶n vay FV – gi¸ trÞ t­¬ng lai hoÆc c©n ®èi tiÒn mÆt mµ b¹n muèn gi÷ l¹i sau lÇn tr¶ cña cuèi cïng (§èi sè tïy chän – ngÇm ®Þnh lµ 0)

48

Hµm tµi chÝnh (Financial Functions) 3. Hµm IPMT – Hµm tÝnh l·i ph¶i tr¶ trong kú cho mét kho¶n ®Çu t­ víi sè kú chi tr¶, kho¶n tiÒn chi tr¶ vµ l·i suÊt kh«ng ®æi ë c¸c kú. IPMT(Rate;Per;Nper;Pv;Fv) Rate – L·i suÊt tÝnh cho 1 kú cña kho¶n ®Çu t­ Per – Kú tÝnh l·i Nper – Sè kú chi tr¶ ®èi víi kho¶n ®Çu t­ PV- Gi¸ trÞ hiÖn t¹i cña kháan ®Çu t­. NÕu lµ ®i vay th× PV chÝnh lµ kháan tiÒn ®i vay FV – Gi¸ trÞ t­¬ng lai hoÆc c©n ®èi tiÒn mÆt mµ b¹n muèn gi÷ l¹i sau lÇn chi tr¶ cuèi cïng (®èi sè tïy chän, ngÇm ®Þnh lµ 0) Type – KiÓu chi tr¶. NÕu viÖc chi tr¶ ®­îc thùc hiÖn vµo cuèi mçi kú th× Type = 0 (ngÇm ®Þnh); nÕu kh«ng Type = 1. 49 Nh­ vËy, cã thÓ viÕt: PMT = IPMT+PPMT

Hµm tµi chÝnh (Financial Functions) 4. Hµm NPER – Hµm tÝnh tæng sè kú chi tr¶ cho mét kho¶n ®Çu t­ víi l·i suÊt, sè tiÒn chi tr¶ mçi kú kh«ng ®æi. NPER(Rate;Pmt;Pv;Fv;Type) Rate – L·i suÊt tÝnh cho 1 kú PMT – Kho¶n chi tr¶ ë mçi kú vµ kh«ng thay ®æi trong suèt ®êi cña dù ¸n. Cô thÓ Pmt bao gåm c¶ kho¶n tr¶ gèc lÉn l·i. Pv – Gi¸ trÞ hiÖn t¹i hoÆc tæng céng dån gi¸ trÞ hiÖn t¹i cña dßng tiÒn trong t­¬ng lai. Fv – Gi¸ trÞ t­¬ng lai hoÆc c©n ®èi tiÒn mÆt mµ b¹n muèn gi÷ l¹i sau lÇn chi tr¶ cuèi cïng (®èi sè tïy chän, ngÇm ®Þnh lµ 0) Type – KiÓu chi tr¶. NÕu viÖc chi tr¶ ®­îc thùc hiÖn vµo cuèi mçi kú th× Type = 0 (ngÇm ®Þnh); nÕu kh«ng 50 Type = 1.

Hµm tµi chÝnh (Financial Functions) 5. Hµm RATE – Hµm tÝnh l·i suÊt ph¶i tr¶ cho mét kho¶n vay hoÆc kho¶n ®Çu t­ víi sè tiÒn chi tr¶ mçi kú kh«ng ®æi. RATE(Nper;Pmt;Pv;Fv;Type) Nper – Tæng sè kú chi tr¶ PMT – Kho¶n chi tr¶ ë mçi kú vµ kh«ng thay ®æi trong suèt ®êi cña dù ¸n. Cô thÓ Pmt bao gåm c¶ kho¶n tr¶ gèc lÉn l·i. Pv – Gi¸ trÞ hiÖn t¹i hoÆc tæng céng dån gi¸ trÞ hiÖn t¹i cña dßng tiÒn trong t­¬ng lai. Fv – Gi¸ trÞ t­¬ng lai hoÆc c©n ®èi tiÒn mÆt mµ b¹n muèn gi÷ l¹i sau lÇn chi tr¶ cuèi cïng (®èi sè tïy chän, ngÇm ®Þnh lµ 0) Type – KiÓu chi tr¶. NÕu viÖc chi tr¶ ®­îc thùc hiÖn vµo cuèi mçi kú th× Type = 0 (ngÇm ®Þnh); nÕu kh«ng 51 Type = 1.

Hµm tµi chÝnh (Financial Functions) 6. Hµm PV – Hµm tÝnh gi¸ trÞ hiÖn t¹i cña dßng tiÒn ®Çu t­. §ã chÝnh lµ tæng gi¸ trÞ hiÖn t¹i cña dßng tiÒn chi trong t­¬ng lai. PV(Rate;Nper;PMT;FV;Type) Rate – L·i suÊt tÝnh cho 1 kú Nper – Sè kú chi tr¶ PMT – Kho¶n chi tr¶ ë mçi kú vµ kh«ng thay ®æi trong suèt ®êi cña dù ¸n. FV – Gi¸ trÞ t­¬ng lai hoÆc c©n ®èi tiÒn mÆt mµ b¹n muèn gi÷ l¹i sau lÇn chi tr¶ cuèi cïng (®èi sè tïy chän, ngÇm ®Þnh lµ 0) Type – KiÓu chi tr¶. NÕu viÖc chi tr¶ ®­îc thùc hiÖn vµo cuèi mçi kú th× Type = 0 (ngÇm ®Þnh); nÕu kh«ng Type = 1. 52

Hµm tµi chÝnh (Financial Functions) 7. Hµm FV – Hµm tÝnh gi¸ trÞ t­¬ng lai cña dßng tiÒn ®Çu t­ dùa trªn kho¶n chi tr¶ vµ l·i suÊt chi tr¶ cè ®Þnh trong c¸c kú chi tr¶. FV(Rate;Nper;PMT;FV;Type) Rate – L·i suÊt tÝnh cho 1 kú Nper – Sè kú chi tr¶ PMT – Kho¶n chi tr¶ ë mçi kú vµ kh«ng thay ®æi trong suèt ®êi cña dù ¸n. PV – Gi¸ trÞ hiÖn t¹i hoÆc tæng c¸c gi¸ trÞ hiÖn t¹i cña c¸c dßng tiÒn trong t­¬ng lai. (®èi sè tïy chän, ngÇm ®Þnh lµ 0) Type – KiÓu chi tr¶. NÕu viÖc chi tr¶ ®­îc thùc hiÖn vµo cuèi mçi kú th× Type = 0 (ngÇm ®Þnh); nÕu kh«ng Type = 1. 53

Hµm tµi chÝnh (Financial Functions) 8. Hµm NPV – Hµm tÝnh gi¸ trÞ hiÖn t¹i rßng cña dßng tiÒn ®Çu t­ dùa trªn l·i suÊt chiÕt khÊu vµ c¸c kháan chi tr¶ (gi¸ trÞ ©m) vµ c¸c kho¶n thu nhËp (gi¸ trÞ d­¬ng) cña dù ¸n. NPV(Rate;Value1; Value2; …) Rate – Tû lÖ chiÕt khÊu tÝnh cho 1 kú Value1; Value2; … vµ cã thÓ cã tíi 29 Value, chÝnh lµ c¸c kho¶n chi vµ kho¶n thu cña dù ¸n ®Çu t­ (NCF). C¸c gi¸ trÞ nµy ph¶i ®­îc s¾p xÕp theo tr×nh tù thêi gian víi kháang c¸ch c¸c kú ®Òu nhau trong suèt ®êi dù ¸n. L­u ý: Hµm NPV chiÕt khÊu dßng tiÒn theo thêi gian tõ n¨m 1, n¨m 0 (th­êng lµ n¨m ®Çu t­ ban ®Çu) kh«ng tham gia vµo qu¸ tr×nh tÝnh tãan (Xem Help ®Ó biÕt thªm chi tiÕt) 54

Hµm tµi chÝnh (Financial Functions)

9. Hµm IRR – Hµm tÝnh tû lÖ thu håi néi t¹i cña dù ¸n ®Çu t­ IRR(Values; Guess) Values lµ mét m¶ng hoÆc tham chiÕu tíi c¸c « cã chøa c¸c gi¸ trÞ sè mµ b¹n muèn tÝnh tû lÖ thu håi néi t¹i cña chóng (dßng NCF). Dßng gi¸ trÞ nµy ph¶i cã chøa Ýt nhÊt 1 gi¸ trÞ ©m hoÆc 1 gi¸ trÞ d­¬ng. NÕu kh«ng, hµm IRR sÏ b¸o lçi. NÕu trong m¶ng hoÆc tham chiÕu cã chøa gi¸ trÞ kh«ng ph¶i d¹ng sè sÏ bÞ bá qua. Guess - §èi sè tïy chän, lµ tû lÖ mµ b¹n dù ®ãan gÇn ®óng víi gi¸ trÞ IRR L­u ý: Hµm IRR sö dông sè thø tù cña c¸c phÇn tö trong m¶ng hoÆc tham chiÕu ®Ó tÝnh tãan chiÕt khÊu v× vËy b¹n ph¶i ch¾c ch¾n r»ng c¸c gi¸ trÞ dßng tiÒn trong m¶ng ®ã ph¶i ®­îc ®Æt theo ®óng tr×nh tù. Hµm IRR cã liªn hÖ víi hµm NPV, ®ã chÝnh lµ tû lÖ chiÕt khÊu t­¬ng øng víi NPV = 0. 55

Hµm tµi chÝnh (Financial Functions)

10. Hµm SLN – Hµm tÝnh møc khÊu hao TSC§ cho 1 kú theo ph­¬ng ph¸p khÊu hao ®Òu. SLN(Cost;Salvage;Life) Cost – Nguyªn gi¸ TSC§ Salvage – Gi¸ trÞ thanh lý ë thêi ®iÓm cuèi ®êi sö dông cña TSC§ Life – Tæng sè c¸c kú trÝch khÊu hao TSC§ (®«i khi cßn gäi lµ thêi gian h÷u Ých cña TSC§)

11. Hµm SYD – Hµm tÝnh møc khÊu hao TSC§ cho 1 kú theo ph­¬ng ph¸p khÊu hao tæng sè n¨m sö dông. SLN(Cost;Salvage;Life;Per) Cost – Nguyªn gi¸ TSC§ Salvage – Gi¸ trÞ thanh lý ë thêi ®iÓm cuèi ®êi sö dông cña TSC§ Life – Tæng sè c¸c kú trÝch khÊu hao TSC§ (®«i khi cßn gäi lµ thêi gian h÷u Ých cña TSC§) Per – Kú trÝch khÊu hao 56

C¬ së d÷ liÖu (Database) Kh¸i niÖm vÒ CSDL

I.

Kh¸i niÖm vÒ “Tr­êng CSDL” – Field. Trong M.E. c¸c tr­êng ®­îc l­u theo cét, trong ®ã dßng ®Çu tiªn cña cét l­u tªn tr­êng (Field name). Kh¸i niÖm vÒ “B¶n ghi” – Record.

–

–

C¸c c«ng cô xö lý CSLD

2.

S¾p xÕp c¬ së d÷ liÖu

1. ■ ■

§Æt con trá vµo mét « bÊt kú trong CSDL Menu: Data/Sort –

Chän tªn tr­êng cÇn s¾p xÕp (cïng lóc b¹n cã thÓ s¾p xÕp 3 tr­êng.NÕu muèn s¾p xÕp >3 tr­êng th× b¹n l¹i lÆp l¹i thao t¸c trªn lÇn n÷a) 57

C¬ së d÷ liÖu (Database) C¸c c«ng cô xö lý CSLD

1.

LÖnh tÝnh tæng con cña CSDL TÝnh tæng con lµ viÖc tæng hîp d÷ liÖu cña c¸c b¶n ghi cã cïng thuéc tÝnh qu¶n lý. (L­u ý: NghÜa cña “tæng” kh«ng ®¬n thuÇn chØ lµ “Céng” mµ cã nghÜa lµ Tæng hîp) Tr­íc khi chän lÖnh cÇn s¾p xÕp CSDL thµnh c¸c nhãm phï hîp víi môc tiªu tæng hîp th«ng tin. Menu: Data/Subtotals víi c¸c lùa chän sau:

■ ■

■

■ ■ ■ ■

At each change in (Tæng hîp d÷ liÖu theo tr­êng nµo) Use function: Lùa chän hµm ®Ó tæng hîp d÷ liÖu Add subtotals to: Tæng hîp

58

C¬ së d÷ liÖu (Database) C¸c c«ng cô xö lý CSLD (tiÕp)

1.

LÖnh t¹o b¶ng b¸o c¸o “trô” tõ CSDL (Pivot Table)

1. ■ ■ ■

§Æt con trá vµo « ®¬n bÊt kú trong CSDL Menu: Data/Pivot Table and PivotChart Report Chän c¸c th«ng sè trong hép tho¹i, víi c¸c lùa chän sau: 1. 2. 3.

Chän nguån d÷ liÖu ®Ó t¹o b¶ng b¸o c¸o trô X¸c ®Þnh miÒn c¬ së d÷ liÖu ®Ó t¹o b¶ng trô X¸c ®Þnh n¬i l­u tr÷ b¶ng trô ■ L­u tr÷ trong cïng trang tÝnh víi CSDL: Existing Worksheet ■ L­u tr÷ ë trang tÝnh míi: New Worksheet ■ Lùa chän d¹ng b¶ng b¸o c¸o (Nót lÖnh: Layout) ■ Lùa chän c¸c thuéc tÝnh cña b¶ng trô (Nót lÖnh: Option)

59

C¬ së d÷ liÖu (Database) C¸c c«ng cô xö lý CSLD (tiÕp)

1.

LÖnh kÕt xuÊt th«ng tin tõ CSDL (Consolidate)

1. ■

■ ■

§Æt con trá vµo « ®¬n bÊt kú trong vïng sÏ l­u tr÷ kÕt qu¶ Menu: Data/Consolidate Chän c¸c th«ng sè trong hép tho¹i, víi c¸c lùa chän sau: ■

■

■

■

Chän hµm sö dông ®Ó kÕt xuÊt th«ng tin (Sum;Count:Average;…) X¸c ®Þnh miÒn tham chiÕu ®Ó lÊy d÷ liÖu (Reference) Chän Add ®Ó ®­a tham chiÕu ®· lùa chän vµo danh s¸ch c¸c tham chiÕu (All References) – LÆp l¹i thao t¸c 2 ®Ó x¸c ®Þnh tÊt c¶ c¸c tham chiÕu cÇn lÊy d÷ liÖu. X¸c nhËn tiªu ®Ò dßng vµ cét cña tham chiÕu: ■ 60 Top Row: NÕu trong miÒn tham chiÕu cã tiªu ®Ò

C¬ së d÷ liÖu (Database) C¸c c«ng cô xö lý CSLD (tiÕp)

1.

LÖnh läc th«ng tin tõ CSDL (Filter)

1. ■ ■ ■

■

■

■ ■

■

Läc tù ®éng (Autofilter) §Æt con trá vµo « ®¬n bÊt kú trong miÒn CSDL Menu: Data/Filter/Autofilter (®Ó chuyÓn c¬ së d÷ liÖu vÒ chÕ ®é läc) Chän tr­êng cÇn läc, chän ®iÒu kiÖn läc b»ng c¸ch nhÊn mòi tªn sæ xuèng tõ tr­êng ®ã. KÕt qu¶ lµ: Nh÷ng b¶n ghi tháa m·n ®iÒu kiÖn läc ®­îc gi÷ l¹i, nh÷ng b¶n ghi kh¸c bÞ Èn ®i. Ng­êi sö dông cã thÓ thùc hiÖn c¸c lÖnh cÇn thiÕt víi nh÷ng b¶n ghi ®ang ®­îc hiÖn. Muèn cho hiÖn tÊt c¶ chän Show all. Thãat khái chÕ ®é läc tù ®éng, chän Data/Filter/Autofilter mét lÇn n÷a L­u ý víi ®iÒu kiÖn läc ®Æc biÖt: Chän Custom tõ danh s¸ch do khi lùa chän mòi tªn sæ xuèng cã ®­îc. 61

C¬ së d÷ liÖu (Database) Läc tù ®éng (Autofilter) (tiÕp)

1. 1.

Hép tho¹i Custom 1. 2. 3.

4.

Läc nh÷ng b¶n ghi theo tr­êng nµo §iÒu kiÖn läc (=; >=;<;<=;…) Khi cã nhiÒu ®iÒu kiÖn, sö dông And vµ ®­a ®iÒu kiÖn thø 2 nÕu c¸c ®iÒu kiÖn ph¶i tháa m·n ®ång thêi. Sö dông Or nÕu chØ cÇn tháa m·n 1 trong sè c¸c ®iÒu kiÖn. L­u ý: Cã thÓ sö dông ký tõ ? hoÆc ký tù * ®Ó ®¹i diÖn cho 1 ký tù ®¬n hoÆc mét sè ký tù bÊt kú nµo ®ã.

62

C¬ së d÷ liÖu (Database) C¸c c«ng cô xö lý CSLD (tiÕp)

1.

LÖnh läc th«ng tin tõ CSDL (Filter)

1. ■ ■

Ngày tháng nhập 9/15/200 5

Läc theo miÒn ®iÒu kiÖn (Advanced Filter) T¹o miÒn ®iÒu kiÖn (Criteria Range): §ã lµ mét miÒn « trong M.E., ë ®ã chøa c¸c ®iÒu kiÖn ®Ó läc CSDL. MiÒn « nµy ph¶i cã 2 dßng trë lªn, sè cét tïy thuéc vµo sè tr­êng cÇn läc.

Tên hàng 1.

Than Cám 4

2.

Trong ®ã dßng ®Çu tiªn chøa tªn tr­êng cÇn läc. Tªn tr­êng ®iÒu kiÖn läc ph¶i gièng y chang tªn tr­êng cña CSDL nÕu ®iÒu kiÖn läc lµ ®iÒu kiÖn so s¸nh (VD: “>23”) vµ ph¶i kh¸c víi tªn tr­êng cña CSDL nÕu ®iÒu kiÖn läc lµ ®iÒu kiÖn tÝnh tãan (VD: “>Average(A1:A12)” C¸c dßng sau chøa c¸c ®iÒu kiÖn läc. NÕu lµ ®iÒu kiÖn ®ång thêi ph¶i tháa m·n, c¸c ®iÒu kiÖn ®ã ph¶i n»m trªn cïng dßng.Ng­îc l¹i, khi c¸c ®iÒu kiÖn n»m kh¸c dßng, M.E. hiÓu ®ã lµ ®iÒu kiÖn cÇn tháa m·n 1 63

C¬ së d÷ liÖu (Database) Läc theo miÒn ®iÒu kiÖn (Advanced Filter) (tiÕp)

1.

Menu: Data/Filter/Advanced Filter , víi c¸c lùa chän sau:

■

■

■

■

■

■

Läc t¹i chç nh÷ng b¶n ghi tháa m·n ®iÒu kiÖn läc. Filter the list, in-place Läc vµ copy nh÷ng b¶n ghi tháa m·n ®iÒu kiÖn läc tíi ®Þa chØ ®­îc chØ ra bëi Copy to (lùa chän: Copy to another location) MiÒn chøa CSDL hoÆc danh s¸ch cÇn läc – List range MiÒn chøa ®iÒu kiÖn läc – Criteria range Lùa chän Unique records only nÕu b¹n chØ läc 1 b¶n ghi duy nhÊt tháa m·n ®iÒu kiÖn läc

64

bµi 7: C«ng cô tiÖn Ých cña M.E. 1. C«ng cô ph©n tÝch gi¶ sö (What-if analysis) ■ C«ng cô Goal seek C«ng cô Goal seek ®«i khi cßn ®­îc coi lµ mét phÇn cña c¸c c«ng cô ph©n tÝch gi¶ sö (Whatif analysis), ®­îc sö dông ®Ó x¸c ®Þnh gi¸ trÞ ®Çu vµo cho mét c«ng thøc khi Ên ®Þnh kÕt qu¶ cña c«ng thøc. Khi t×m kiÕm, M.E. thay thÕ gi¸ trÞ cña mét « x¸c ®Þnh cã mÆt trong c«ng thøc cho ®Õn khi nµo c«ng thøc ®¹t ®­ Vay 2.000.000 Lãi suất 7% îc gi¸ trÞ mong muèn. Số kỳ chi trả VÝ dô: Hµm Goal seek t×m l·i suÊt ®Ó c¸c kho¶n 10 Số tiền chi trả =PMT(D27;D28;D26) chi tr¶ hµng kú ®¹t møc 100.000® 65

C«ng cô goal seek (tiÕp) Vay

Chän lÖnh: Tools/Goal seek… Cã c¸c lùa chän sau:

2.000.000

Lãi suất

7%

Số kỳ chi trả Số tiền chi trả

10 =PMT(D27;D28;D26)

Set cell: « chøa c«ng thøc cã tham chiÕu ®Õn « cÇn thay ®æi gi¸ trÞ ®Çu vµo. To Value: Gi¸ trÞ cÇn ®¹t tíi cña c«ng thøc. By changing cell: « ®Çu vµo mµ gi¸ trÞ cña nã cÇn thay ®æi ®Ó c«ng thøc ®¹t ®­îc kÕt qu¶ mong muèn.

Goal seek sÏ ®­a ra gi¸ trÞ cña « “Changing cell” ®Ó c«ng thøc ®¹t kÕt qu¶ mong muèn.

66

C«ng cô data table C«ng cô ph©n tÝch gi¶ sö víi b¶ng d÷ liÖu lµ miÒn « ph¶n ¸nh sù thay ®æi gi¸ trÞ cña chóng ¶nh h­ëng nh­ thÕ nµo ®Õn kÕt qu¶ cña c«ng thøc. B¶ng d÷ liÖu ®­a ra c¸ch trùc tiÕp ®Ó tÝnh tãan vµ so s¸nh nhiÒu ph­ ¬ng ¸n kh¸c nhau –

–

B¶ng d÷ liÖu 1 biÕn ®Çu vµo (Onevariable data tables). VÝ dô nh­ sö dông b¶ng d÷ liÖu 1 biÕn ®Çu vµo ®Ó kh¶o s¸t kho¶n tiÒn chi tr¶ cho mét kháan vay víi c¸c møc l·i suÊt kh¸c nhau. B¶ng d÷ liÖu 2 biÕn ®Çu vµo (Onevariable data tables). VÝ dô nh­ sö dông

67

C«ng cô Data tables (tiÕp) T¹o Tables 1. T¹o b¶ng 1-biÕn ®Çu vµo: - Gi¸ trÞ ®Çu vµo thay ®æi chøa theo dßng hoÆc cét - C«ng thøc cÇn kh¶o s¸t sù thay ®æi kÕt qu¶ cña nã ®­îc ®Æt ë « liÒn kÒ cét hoÆc liÒn kÒ dßng 2. T¹o b¶ng 2-biÕn ®Çu vµo: - Gi¸ trÞ ®Çu vµo thay ®æi cña 1 biÕn chøa theo dßng vµ gi¸ trÞ ®Çu vµo thay ®æi cña biÕn kia chøa theo cét - C«ng thøc cÇn kh¶o s¸t sù thay ®æi kÕt qu¶ cña nã ®­îc ®Æt ë « lµ giao cña cét

68

C«ng cô senarios… C«ng cô Senarios.. dïng ®Ó kh¶o sù t¸c ®éng cña viÖc thay ®æi gi¸ trÞ cña mét hoÆc nhiÒu biÕn ®Çu vµo tíi gi¸ trÞ cña 1 hoÆc nhiÒu c«ng thøc. VÝ dô: B¹n cã thÓ xem xÐt sù thay ®æi vÒ gi¸ b¸n, s¶n l­îng, l·i suÊt… xem nh÷ng thay ®æi ®ã t¸c ®éng tíi NPV, IRR, Thêi h¹n hoµn vèn, … cña dù ¸n nh­ thÕ nµo

69

C«ng cô Data tables (tiÕp) TÝnh Data Tables 1. X¸c ®Þnh b¶ng d÷ liÖu (bao gåm c¶ cét hoÆc dßng vµ « cã chøa c«ng thøc). 2. Menu: Data/Tables… víi c¸c lùa chän sau: NhËp ®Þa chØ cña « d÷ liÖu ®Çu vµo t¹i Row Input cell chob d÷ liÖu ®Çu vµo chøa theo dßng. NhËp ®Þa chØ cña « d÷ liÖu ®Çu vµo t¹i Column Input cell cho d÷ liÖu ®Çu vµo chøa theo cét.

70

71