Đọc truyện tong quat ve excel

♥ AzTruyen.Top - Trang đọc truyện hoàn toàn miễn phí!

tong quat ve excel

Trước Sau

Màu nền

Font chữ

Font size

Chiều cao dòng

1 - ?ޮh nghܡ

+> Ch??ng tr׮h b?ng tݮh l? h֠th讧 ch??ng tr׮h m? phᮧ m鴠b?ng c?ng t?c c񡠫ՠto?n vi?n tr?n m?n h׮h v? cho phа ng?ꩠdﮧ nh󮧠c?c c?ng th? v?o ?Ӡth?֮ c?c phа tݮh ?詠v�c?c d?ֵ nh׮ thʹ.

+> Microsoft Excel l? phǮ mҭ b?ng tݮh ?i֮ t??u biӵ do Microsoft ph?t triӮ, ??y l? phǮ mҭ chuy?n dﮧ cho c?ng t?c kՠto?n, v?n ph߮g ??triӮ khai tr?n m?i tr?ꮧ Windows.

+> B?ng tݮh

Trong c?c ch??ng tr׮h b?ng tݮh, ??y l? m鴠ma trˮ hai chiҵ g孠c?c h?ng v? c?c c鴠m? ng?ꩠdﮧ sϠnh˰ c?c ?ǵ ?Ҭ c?c con s謠v? c?c c?ng th?o ?㮍

2 - Tݮh n?ng v? ??c񡠥xcel

- Th?֮ ??nhiҵ phа tݮh t?n gi?n ?ծ ph?p.

- T栣h? l?u tr??ng tin d?�d?ng b?ng nh? b?ng l??ng, b?ng kՠto?n, b?ng thanh to?n, b?ng th讧 k?, b?ng d?n...

- Thao t?c tr?n b?ng tݮh c㠴hӠt?o ra c?c b?o c?o t殧 hhoƣ ph?n tݣh c㠫̭ theo c?c biӵ ?嬠h׮h vϠminh ho?...

3 - ?ƣ ?iӭ

- HiӮ thޠvﮧ l?m vi֣ d?�d?ng m?ng 2 chiҵ.

- T?g c˰ nh˴ khi d?ֵ ngu宠bޠthay ?橮

- ChuyӮ ?橠dԠd?ng t? liֵ sang h׮h vϮ

II - Gi�thiֵ m?n h׮h microsoft excel

1 - Kh멠?鮧 Excel

Th?ng th?ꮧ sau khi c?i ?ƴ b頣h??ng tr׮h Office 2000, shortcut c񡠍icrosoft Excel ???ƴ 렐rograms trong Start menu. C߮ 렴r?n ?ܡ ch??ng tr׮h n?y ???ƴ trong C:\Program Files\Microsoft Office\Office. Vנv˹ ?Ӡkh멠?鮧 Excel c㠴hӠth?֮ b?ng m鴠trong 2 c?ch sau :

C?ch 1 : Kݣh chu鴠v?o Start menu ch䮠Programs r婠ch䮠Microsoft Excel.

C?ch 2 (dﮧ khi kh?ng c㠢iӵ t? c񡠅xcel) : Kݣh chu鴠v?o Start menu ch䮠Run... r婠nh˰ d߮g l֮h :

"C:\Program Files\Microsoft Office\Office\ EXCEL.EXE"

2 - M?n h׮h Excel

VҠc? b?n c?栅xcel t??ng t? c?栣񡠰hǮ mҭ so?n th?o Microsoft Word, vנv˹ 렮?y chؠtr׮h b?y chi tiմ th?m c?c th?nh phǮ ?ƣ tr?ng nh? :

<+> Formula Bar (thanh c?ng th? hiӮ thޠto? ?頫 hi֮ h?nh, n󴠨u?p (Cancel), n󴠮h˰ (Enter), n󴠳o?n th?o c?ng th? n驠dung d?ֵ c񡠫 hi֮ h?nh.

<+> Workbook Window l? c?栣h?i dung tְ. T?n tְ (workbook) hi֮ tr?n thanh ti?u ?Ҡc?栶�phǮ m렲鮧 ?ޮh s?n l? XLS. T?n tְ Workbook mƣ nhi?n l? Book# ( # l? s蠴h?t??ng ?�nh?lǮ m렴ְ ). C?c th?nh phǮ c񡠗orkbook Window l? :

- ??ꮧ viҮ ngang (Column Border) ghi k? c鴠t??i sang ph?i theo ch?i A, B, C,..., Z, AA, AB,... , ZZ. C鴠(Column) l? m鴠t˰ hnh?? theo chiҵ d䣮 ?頲鮧 ngǭ ?ޮh l? 8 k?c㠴hӠthay ?橠gi? trޠn?y t??ծ 255). C㠴殧 c鮧 256 c鴮

- ??ꮧ viҮ d䣠(Row Border) ghi s蠴h?d߮g t??n xu讧 d?� D߮g (Row) l? m鴠t˰ hnh?? theo chiҵ ngang. Chiҵ cao mƣ nhi?n l? 12.75 chʭ ?iӭ (c㠴hӠthay ?橠gi? trޠn?y t??ծ 409). C㠴殧 c鮧 16384 d߮g.

- ? (Cell) l? giao c񡠭鴠d߮g v�m鴠c鴮 ?ޡ chؠc񡠭鴠? x?c ?ޮh b?ng t?n c鴠v? t?n h?ng. Vݠd?B6 l? ?ޡ chؠc񡠫 n?m tr?n c鴠B, h?ng th?

? hi֮ h?nh (Select cell) l? ? c㠫hung viҮ quanh.

- B?ng tݮh (Sheet) l? m鴠b?ng g孠c㠲56 c鴯16384 h?ng h׮h th?nh tr?n 4 triֵ ? d?ֵ. T?n b?ng tݮh mƣ nhi?n l? Sheet# (# l? s蠴h?. Theo chՠ?頮gǭ ?ޮh, m鴠tְ Workbook c㠱6 Sheet, ??liִ k? t?eet1 ?ծ Sheet16 (c㠴hӠn?ng gi? trޠn?y l?n ?ծ 255). Tr?n m?n h׮h Microsoft Excel ?ang ch䮠b?ng tݮh Sheet1 c񡠴ְ Book1.

<+> Status Bar (thanh tr?ng th?i) d߮g ch?ՠ?頬?m vi֣ hi֮ h?nh hay ?a l֮h hi֮ h?nh c񡠢?ng tݮh v? c?c t׮h tr?ng hi֮ h?nh c񡠨֠th讧 nh? NumLock, Capslock,...

C?c chՠ?頬?m vi֣ th?ng th?ꮧ g孺

- Ready : S?n s?ng nh˰ d?ֵ.

- Enter : ?ang nh˰ d?ֵ.

- Edit : ?ang ?iҵ chخh d?ֵ.

- Point : ?ang ?iҵ chخh d?ֵ hay c?ng th?ong ? hi֮ h?nh. Ch䮠chՠ?頮?y b?ng c?ch di chuyӮ ?ծ ? mu讠?iҵ chخh v? g⠰hݭ F2.

3 - Tho?t kh᩠excel

V?o Menu File, ch䮠Exit (hoƣ bʭ Alt+F4). Nյ c㠴h?֮ bʴ c? ?橠n?o trong c?c WorkBook ?ang m렭? ch?a l?u v?o ?ܡ (cho ?ծ thꩠ?iӭ tho?t) thנExcel sϠth?ng b?o nh?c nh렮g?ꩠdﮧ c㠭u讠ghi l?i nh?s?y ?橠?㠨ay kh?ng.

III - T?o b?ng tݮh

1 - C?ch di chuyӮ con trᠢ?ng tݮh

T?i m鴠thꩠ?iӭ lu?n lu?n t宠t?i m鴠con trᠫ trong b?ng tݮh. Khi nh˰ d?ֵ, v?o c?ng th?y thao t?c l֮h sϠt?c ?鮧 ?ծ ?ޡ chؠt?i vޠtrݠcon trᠢ?ng tݮh hi֮ h?nh. ?Ӡdi chuyӮ con trᠳ?ng c?c phݭ di chuyӮ hoƣ kݣh chu鴠t?i m鴠? ?Ӡdi chuyӮ con trᠮծ ? ?㮍

2 - Vﮧ, c?ch ch䮠vﮧ

Vﮧ l? m鴠hoƣ nhiҵ ? li?n t?Vﮧ ??x?c ?ޮh b멠to? ?頶ﮧ g孠?ޡ chؠ? ?ǵ vﮧ v? ?ޡ chؠ? cu詠vﮧ, d?ng : <? ?ǵ>:<? cu試gt;. Vݠd?:C8

Tr?�khi th?֮ m鴠thao t?c, ng?ꩠs?ng ph?i ch䮠ph?m vi l?m vi֣ (c㠴hӠl? m鴠vﮧ hoƣ nhiҵ vﮧ).

+> Ch䮠m鴠? : di chuyӮ con trᠮҮ ? ch䮬 hoƣ kݣh chu鴠t?i ? ch䮮

+> Ch䮠m鴠c鴠 : kݣh chu鴠t?i k? c鴮

+> Ch䮠m鴠d߮g : kݣh chu鴠t?i s蠴h?h?ng.

+> Ch䮠m鴠vﮧ, c㠣?c c?ch ch䮠:

- ?ƴ con trᠴ?i ? ?ǵ vﮧ, ʮ Shift, dﮧ c?c phݭ m򩠴?n dޣh chuyӮ ?ծ ? cu詠vﮧ.

- ?ƴ con trᠣhu鴠t?i ? ?ǵ vﮧ, ʮ v? gi?t tr?i chu鴬 kЯ ?ծ ? cu詠vﮧ

- ?ƴ con trᠣhu鴠t?i ? ?ǵ vﮧ, ʮ v? gi?ݭ Shift trong khi kݣh chu鴠t?i ? cu詠vﮧ.

+> Ch䮠nhiҵ vﮧ : ʮ v? gi?ݭ Ctrl trong khi dﮧ chu鴠th?֮ thao t?c ch䮠c?c vﮧ kh?c nhau.

3 - C?c kiӵ d?ֵ

Trong m鴠? chؠc㠴hӠch?t kiӵ d?ֵ. Kiӵ d?ֵ ph?u飠v?o k?ǵ ti?n ??nh˰.

a - Kiӵ s蠨Number)

K?ǵ ti?n g⠶?o l? c?c ch? t??ծ 9, c?c dʵ "+"; " -"; "."; ","; "()"; "$". M鴠s蠮?nh˰ v?o mƣ nhi?n l? d?ng General, sau ?㠣㠴hӠ?ޮh d?ng tr׮h b?y s蠬?i theo ? b?ng l֮h Format/Cells.

b - Kiӵ chu穠(Text)

K?ǵ ti?n g⠶?o l? c?c k???ծ Z, c?c k?anh bi?n nh? sau : ? ? ^ \

K? ?Ӡcanh c?c k??n trong ? vҠb?n tr?i. K? ?Ӡcanh c?c k?rong ? vҠb?n ph?i. K? ?Ӡcanh c?c k?rong ? v?o gi?K? ?Ӡlư l?i k?heo sau n㠣ho ?ծ hմ chiҵ r鮧 ?. C?c k?anh bi?n chؠc㠴?c d?khi ng?ꩠs?ng ?? ch䮠tu?n Navigation Keys trong Tools\Option thΠTransition.

c- Kiӵ thꩠgian (Date & Time)

D?ֵ kiӵ thꩠgian ph?i ??nh˰ v�khu?n mɵ ?? x?c ?ޮh khi ch䮠m? qu裠gia. Th?ng th?ꮧ ch󮧠??nh˰ theo d?ng mm/dd/yy ?詠v�d?ֵ kiӵ ng?y v? hh:mm:ss v�d?ֵ kiӵ giꮍ

d- Kiӵ c?ng th?ormular)

C?ng th?th?nh l˰ d?�d?ng m鴠biӵ th?ong ?㠫?ǵ ti?n nh˰ v?o b?t bu飠ph?i l? dʵ "=", "-" hoƣ "+". Kմ qu? tr׮h b?y trong ? ch?ng th??ng ph?i l? c?c k?⠶?o m? chݮh l? gi? trޠc񡠣?ng th?.

Vݠd?Khi nh˰ =4*5+2 thנkմ qu? tr׮h b?y trong ? l? 22.

Nh? th?ng th?ꮧ th?nh phǮ c񡠭鴠c?ng th?m c?c to?n h?ng v? to?n t?

M鴠to?n h?ng tr?n Excel c㠴hӠl? : h?ng s謠h?ng chu穠v?n b?n (ph?i ???ƴ trong cư kݣh kа), ?ޡ chؠ?, vﮧ hoƣ c?c lo?i h?m.

C?c to?n t? d?trong c?ng th?

- To?n t? h䣠: + (c鮧), - (tr?* (nh?n), / (chia), ^ (lu?), % (phǮ tr?m).

- To?n t?u穠: & (n詠chu穩

- To?n t?an h֠: = (b?ng), <> (kh?c), > (l�h?n), >= (l�h?n hoƣ b?ng), < (nhᠨ?n), <= ( nhᠨ?n hay b?ng).

?頭u ti?n c񡠣?c to?n t?ong c?ng th?c?c nh㭠biӵ th?ong (...), lu?, nh?n, chia, c鮧, tr?4 - C?ch nh˰ d?ֵ

+> Ch䮠? cǮ nh˰.

+> Nh˰ d?ֵ (theo quy ?�?詠v�t?lo?i d?ֵ).

+> Kմ th󣠮h˰ b?ng c?ch g⠰hݭ ENTER hay di chuyӮ con trᠫ hi֮ h?nh sang ? kh?c.

* Ch󠽠 :

+> D?ֵ ban ?ǵ ??nh˰ lu?n ???ޮh d?ng l? d?ֵ nguy?n thu?neral) do ?㠭u讠thay ?橠cǮ ph?i tiծ h?nh ?ޮh d?ng l?i.

+> Khi nh˰ d?ֵ g孠c? s蠶? v?n b?n thנExcel coi ?㠬? v?n b?n.

+> 렣hՠ?頮gǭ ?ޮh :

- D?ֵ kiӵ v?n b?n ??canh chخh t??i sang ph?i.

- D?ֵ kiӵ s蠮?canh chخh t??i sang tr?i.

+> Khi nh˰ d?ֵ hմ ?頲鮧 c񡠭鴠?, nյ l? :

- D?ֵ kiӵ v?n b?n n㠳Ϡt?g tr?n sang ? kՠtiհ nյ ? ?㠫h?ng c㠤?ֵ hoƣ bޠȮ ?i nյ ? ?㠣㠣h? liֵ.

- D?ֵ kiӵ s蠴hנ??hiӮ thޠd?�d?ng ######## hay d?�d?ng dʵ phȹ ?鮧 (n,mE?a), vݠd?5E+7.

5 - T?g ?iҮ d?ֵ

a - C?n t?n (Autofill handle)

C?n t?n l? ?iӭ g㣠d?�b?n ph?i ? hi֮ h?nh m? khi ?ƴ chu鴠?ծ ?㠮㠣huyӮ sang d?ng . C?n t?n ??dﮧ ?Ӡt?g ?iҮ d?ֵ cho c?c ? kՠtiհ b?ng c?ch ?ƴ chu鴠v?o ?㠶? kЯ ?ծ c?c kՠtiհ.

b - T?g ?iҮ c?c lo?i d?ֵ

+> Nh˰ m鴠d?y v?n b?n.

- Nh˰ d?ֵ kiӵ v?n b?n cǮ ?iҮ v?o ? ?ǵ ti?n.

- ?ƴ chu鴠v?o c?n t?n r婠kݣh v? kЯ ?Ӡt?g ?iҮ d?ֵ.

+> Nh˰ m鴠d?y s讍

- Nh˰ gi? trޠ?ǵ v? gi? trޠkՠtiհ v?o 2 ? kҠnhau (cﮧ h?ng hoƣ cﮧ c鴩.

- Ch䮠2 ? d?ֵ v?h˰.

- ?ƴ chu鴠v?o c?n t?n r婠kݣh v? kЯ ?Ӡt?g ?iҮ d?ֵ.

+> M鴠s蠣hu穠???iҮ t?ng.

Qtr1 ? Qtr2, Qtr2, Qtr4, Qtr1,...

Product1 ? Product2, Product3, Product4,...

Jan ? Feb, Mar, Apr, May, Jun, Jul, Aug,...

Jan 1998 ? Jan 1999, Jan 2000, Jan 2001, Jan 2002, Jan 2003,...

Mon ? Tue, Wed, Thu, Fri, Sat, Sun, Mon,...

IV - Thao t?c tr?n tְ

Tְ trong Excel c㠰hǮ m렲鮧 l? XLS v? c㠴hӠc㠮hiҵ b?ng tݮh, biӵ ?宮.

1 - L?u tr?p tr?n ?ܡ

- ?Ӡl?u tr?p th?֮ l֮h File\Save, ch䮠栮ܡ v? th? m?h?p r婠??a v?o t?n tְ cǮ l?u tr?Save.

- L?u tr?i t?n c򠢻ng c?ch ch䮠m鴠trong c?c c?ch sau: g⠃trl+S, hoƣ kݣh chu鴠v?o biӵ t? ? tr?n Standard Toolbar, hoƣ th?֮ l֮h: File/Save

2 - M렭鴠tְ ?? c㠴r?n ?ܡ

+> Th?֮ l֮h File/Open...

+> Ch䮠栮ܡ v? th? m?h?p cǮ m뮍

+> Ch䮠t?n tְ cǮ m렿 Open.

3 - T?o m鴠tְ m�

?Ӡm렭鴠tְ m�kݣh chu鴠v?o biӵ t? ? tr?n Standard Toolbar .

4 - ?㮧 m鴠tְ

M렴r׮h ??n File th?֮ l֮h Close.

V - Thao t?c tr?n b?ng tݮh

1 - Thao t?c tr?n vﮧ

a - Hiֵ chخh d?ֵ

+> Ch䮠vﮧ cǮ hiֵ chخh, g⠆2.

+> Tiծ h?nh c?c hiֵ chخh cǮ thiմ.

+> G⠰hݭ Enter ?Ӡkմ th󣠨iֵ chخh.

b - Hu?l֮h so?n th?o ?? th?֮

?Ӡhu?l֮h so?n th?o ?? th?֮ v?o tr׮h ??n Edit ch䮠l֮h Undo.

c - Lư l?i l֮h so?n th?o ?? th?֮

?Ӡl?m l?i l֮h so?n th?o ?? th?֮ v?o tr׮h ??n Edit ch䮠l֮h Redo.

d - Sao chа vﮧ d?ֵ

<+> Thao t?c sao chа.

+> Ch䮠vﮧ ngu宠(Source).

+> Th?֮ l֮h: Edit\Copy ( hoƣ g⠃trl + C )

+> Ch䮠vﮧ ?ݣh (Destination) c㠣ﮧ d?ng v�vﮧ ngu宠hay di chuyӮ con trᠫ ?ծ vޠtrݠc񡠫 g㣠tr?n b?n tr?i c񡠶ﮧ ?ݣh.

+> Th?֮ l֮h : Edit\Paste (hoƣ g⠃trl+ V ).

<+> ?ޡ chؠ?.

Khi chа gi?ֵ, nյ vﮧ ch? liֵ kiӵ c?ng th?մ qu? vﮧ ?ݣh sϠc㠴hay ?橠hay kh?ng tu?飠c?ng th?ong vﮧ ngu宠tham chiյ ?ޡ chؠt??ng ?詠hay tuyִ ?詮

+> Xд vݠd?Nh˰ b?ng tݮh v�n驠dung :

A B C D E F

1 30 80 110 110 110 140

2 40 30 70 110 120

3 90 60 150 110

4 60 80 140 110 170

- Nh˰ v?o ? C1 c?ng th?=A1+B1.

- Nh˰ v?o ? D1 c?ng th?=$A$1+$B$1.

- Nh˰ v?o ? E1 c?ng th?=$A1+B$1.

+> C?c lo?i ?ޡ chؠ?.

- ?ޡ chؠt??ng ?詠(Relative Address) l? lo?i ?ޡ chؠm? khi chа ?ծ vﮧ ?ݣh, ?ޡ chؠtham chiյ c񡠶ﮧ ?ݣh xϠthay ?橠theo ph??ng v? chiҵ v�to? ?頴?ng (gi?m) t??ng ?

?ޡ chؠtham chiյ t??ng ?詠c㠤?ng <c鴠><d߮g >.

Vݠd?D?ֵ t?i c?c ? nh? b?ng tݮh tr?n.

C?ng th?i ? C1 l? : =A1+B1 ? kմ qu? l? 110.

Khi chа c?ng th? v?o ? C2 thנc?ng th?C2 c㠮驠dung : =A2+B2 v? kմ qu? l? 70. T??ng t?i chа c?ng th? v?o ? C3 thנc?ng th?C3 c㠮驠dung : =A3+B3 v? kմ qu? l? 150; v?o ? C4 thנc?ng th?C4 c㠮驠dung : =A4+B4 v? kմ qu? l? 140.

?ޡ chؠA1, B1 trong c?ng th?a ? C1 l? ?ޡ chؠt??ng ?詮

- ?ޡ chؠtuyִ ?詠(Absolute Address ) l? lo?i ?ޡ chؠkhi chа ?ծ vﮧ ?ݣh ,?ޡ chؠtham chiյ c񡠶ﮧ ?ݣh sϠgi?uy?n gi讧 nh? vﮧ ngu宮

?ޡ chؠtham chiյ c㠤?ng : $< c鴠>$< d߮g >.

Vݠd?D?ֵ t?i c?c ? nh? b?ng tݮh tr?n.

C?ng th?i ? C1 l? : =$A$1+$B$1 ? kմ qu? l? 110

Khi chа c?ng th? v?o ? D2 thנc?ng th?D2 c㠮驠dung : =$A$2+$B$2 v? kմ qu? l? 110. T??ng t?i chа c?ng th? v?o ? D3 v? D4 thנc?ng th??㠶ɮ c㠮驠dung : =$A$2+$B$2 v? kմ qu? l? 110.

?ޡ chؠ$A$1, $B$1 ??g䩠l? ?ޡ chؠtuyִ ?詮

- ?ޡ chؠh箠h(Mixed Address)

?ޡ chؠtham chiյ c㠤?ng $<c鴦gt;<d߮g> (tuyִ ?詠c鴬 t??ng ?詠d߮g) hoƣ <c鴦gt;$<d߮g> (t??ng ?詠c鴬 tuyִ ?詠d߮g). Khi chа ?ծ vﮧ ?ݣh, ?ޡ chؠtham chiյ c񡠶ﮧ ?ݣh bޠthay ?橠m鴠c?ch t??ng ?oƣ theo c鴠hoƣ theo h?ng.

Vݠd?D?ֵ t?i c?c ? nh? b?ng tݮh tr?n.

C?ng th?i ? E1 l? : =$A1+B$1 ? kմ qu? l? 110

Khi chа c?ng th? v?o ? E2 thנc?ng th?E2 c㠮驠dung : =$A2+B$1v? kմ qu? l? 120. T??ng t?i chа c?ng th? v?o ? F1 thנc?ng th?F1 c㠮驠dung : =$A1+C$1 v? kմ qu? l? 140; v?o ? F4 thנc?ng th?F4 c㠮驠dung : =$A4+C$1v? kմ qu? vɮ l? 170

e - Di chuyӮ d?ֵ vﮧ

+> Ch䮠vﮧ ngu宠(suorce).

+> Th?֮ l֮h : Edit\ Paste.

+> Ch䮠vﮧ ?ݣh (destination area) c㠣ﮧ d?ng v�vﮧ ngu宠hay di chuyӮ con trᠫ ?ծ vޠtrݠc񡠫 g㣠tr?n b?n tr?i c񡠶ﮧ ?ݣh.

+> Th?֮ l֮h : Edit\Paste.

2 - Thao t?c v�?, h?ng, c鴍

a - Cḫ h?ng, c鴬 ?

+> Cḫ h?ng (Rows)

- Ch䮠c?c h?ng ?Ӡx?c ?ޮh vޠtrݠv? s蠬? h?ng mu讠cḫ th?m.

- Th?֮ l֮h Insert\Rows.

+> Cḫ c鴠(Columns)

- Ch䮠c?c c鴠?Ӡx?c ?ޮh vޠtrݠv? s蠬? c鴠mu讠cḫ th?m.

- Th?֮ l֮h Insert\Colunms.

+> Cḫ ? (Cells)

- Ch䮠vﮧ mu讠cḫ v?o ? tr讧.

- Th?֮ l֮h Insert\Cells...

- Ch䮠c?ch cḫ r婠nhʮ OK.

a - Xo? h?ng, c鴬 ?

+> Xo? h?ng (Rows)

- Ch䮠c?c h?ng cǮ xo?.

- Th?֮ l֮h Edit\Delete.

+> Xo? c鴠(Columns)

- Ch䮠c?c c鴠cǮ xo?.

- Th?֮ l֮h Edit\Delete.

+> Xo? ? (Cells)

- Ch䮠vﮧ mu讠xo?.

- Th?֮ l֮h Edit\Delete...

- Ch䮠c?ch xo? r婠nhʮ OK.

VI - ?ޮh d?ng d?ֵ b?ng tݮh

1 - Thay ?橠?頲鮧 c鴬 chiҵ cao d߮g

a - ?橠?頲鮧 c鴍

- Ch䮠c?c c鴠cǮ thay ?橠?頲鮧.

- Bʭ v? kЯ chu鴠tr?n ??ꮧ gianh gi�gi?i?u ?Ҡhai c鴠?Ӡthay ?橠kݣh th?�

b- Thay ?橠chiҵ cao m鴠d߮g

- Ch䮠c?c h?ng cǮ thay ?橠chiҵ cao.

- Bʭ v? kЯ chu鴠tr?n ??ꮧ gianh gi�gi?i?u ?Ҡhai h?ng ?Ӡthay ?橮

2 - ?ޮh d?ng d?ֵ s荊Sau khi nh˰ d?ֵ, tݮh to?n,... ta c߮ ph?i tr׮h bǹ b?ng tݮh sao cho thݣh hv�y?u cǵ c?ng vi֣. C?Ӡl? ch䮠kiӵ thӠhi֮ s蠬iֵ, ch䮠??n vޠtݮh thݣh h thay ?橠kiӵ ch?c젣h?hʮ m?nh n驠dung, s蠬iֵ quan tr䮧...

a - C?c kiӵ ?ޮh d?ng c㠳?n

+> Dﮧ biӵ t? tr?n Formatting Toolbar.

- $ : Biӵ t? ?Ӡbiӵ diԮ d?ֵ s蠫iӵ tiҮ t֮ Vݠd? $ 70,000,000.00

- % : Biӵ t? ?Ӡbiӵ diԮ d?ֵ s蠫iӵ phǮ tr?m. Vݠd?70%

- , : Biӵ t? ?Ӡbiӵ diԮ d?ֵ s蠫iӵ ph?n nh㭬 b?t ?ǵ t?n ph?i, 3 ch? m鴠nh㭠, dﮧ dʵ "," ph?n nh㭮 Dʵ chʭ "." t?ch phǮ nguy?n v? th˰ ph?n.

+> C?c b?�th?֮ ?ޮh d?ng hiӮ thޠs蠺

- Ch䮠vﮧ d?ֵ ?Ӡ?ޮh d?ng s讍

- Th?֮ l֮h Format\Cells... ch䮠thΠNumber.

- Ch䮠lo?i ?ޮh d?ng trong danh s?ch Category

- Ch䮠kiӵ hiӮ thi s蠩m 렎egative numbers.

- Ch䮠OK hay g⠅nter.

b - ?ޮh d?ng c񡠮g?ꩠdﮧ

+> C?c m? ?ޮh d?ng.

# : Dﮧ biӵ diԮ vޠtrݠcho 1 con s蠴h?, nյ gi? trޠnh˰ th? v?ngo?i mɵ ?ޮh d?ng thנgi? trޠ??biӵ diԮ b?ng # sϠ??l?m tr߮.

0 : Dﮧ th?m v?o gi? trޠth?c s蠰 v? nghܡ.

? : Th?m kho?ng tr讧 v?o gi? trޠ???ޮh d?ng.

+> T?o ?ޮh d?ng c񡠮g?ꩠdﮧ.

- Th?֮ l֮h Format\Cells... ch䮠thΠNumber.

- Ch䮠Custom trong danh s?ch Category.

- Nh˰ m? ?ޮh d?ng v?o h鰠Type, ?宧 thꩠtheo d⩠kմ qu? 렓ample.

3 - ?iҵ chخh d?ֵ

- Ch䮠vﮧ d?ֵ ?Ӡ?ޮh d?ng

-Th?֮ l֮h Format\Cells ch䮠thΠAlignment.

- Ch䮠m?orizontal ?Ӡl?䮠?iҵ chخh ngang d?ֵ trong t??.

- Ch䮠m?ertical ?Ӡl?䮠?iҵ chخh d䣠d?ֵ trong t??.

- Ch䮠m?rientation ?Ӡl?䮠h׮h th??i d?ֵ.

- C?c m?h䮠trong Text control :

? Wrap text : C蠮ޮh d?ֵ trong vﮧ kݣh th?�ngǭ ?ޮh c񡠫.

? Shrink to fit : Co d?n d?ֵ ?񠮓 ch?ong ?.

? Merge cells : Kմ hc?c ? th?nh m鴠?.

- Ch䮠OK hay g⠅nter.

4 - ?ޮh d?ng k?- Ch䮠vﮧ d?ֵ ?Ӡ?ޮh d?ng.

-Th?֮ l֮h Format\Cells ch䮠thΠFont.

- Th?֮ c?c thao t?c ?ޮh d?ng t??ng t?ord.

5 - T?o c?c ??ꮧ k΍

- Ch䮠vﮧ d?ֵ ?Ӡ?ޮh d?ng.

- Th?֮ l֮h Format\Cells ch䮠thΠBorder.

- Th?֮ c?c thao t?c ?ޮh d?ng t??ng t?ord.

VII - C?c h?m mɵ th?ng d?c񡠅xcel

1. Ph??ng ph?p bi?n t˰ h?m

C㠲 ph??ng ph?p nh˰ s?ng h?m : - Nh˰ tr?հ.

- G䩠s?ng b?ng Paste Function.

a - Nh˰ tr?հ

D?ng th?ng qu?t c񡠨?m: =<T?n h?m>(Danh s?ch ?詠s詍

- T?n h?m : t?n h?m mɵ do Excel quy ?ޮh. Vݠd?UM, AVERAGE, MAX,...

- ?詠s蠠 : c㠴hӠl? c?c h?ng, biӵ th?ޡ chؠ?, vﮧ ? hoƣ h?m.

* Ch󠽠: - T?n h?m v? c?c ?詠kh?ng ph?n biִ ch??ꮧ v? ch?a.

- ?詠s蠰h?i ???ƴ trong ngoƣ ??n ( ) v? ph?n c?ch nhau b멠dʵ phȹ.

- H?m c㠴hӠc㠴詠?a 30 ?詠nh?ng ?頤?i kh?ng v?qu? 255 kݠt? N驠dung c񡠨?m hay biӵ th??ng ??phа ch?u c?ch.

b - G䩠s?ng h?m b?ng Paste Function

- ?ƴ con trᠴ?i ? cǮ nh˰ h?m mɵ, r婠th?֮ l֮h Insert\ Function...

a - Nh˰ tr?հ

D?ng th?ng qu?t c񡠨?m: =<T?n h?m>(Danh s?ch ?詠s詍

- T?n h?m : t?n h?m mɵ do Excel quy ?ޮh. Vݠd?UM, AVERAGE, MAX,...

- ?詠s蠠 : c㠴hӠl? c?c h?ng, biӵ th?ޡ chؠ?, vﮧ ? hoƣ h?m.

* Ch󠽠: - T?n h?m v? c?c ?詠kh?ng ph?n biִ ch??ꮧ v? ch?a.

- ?詠s蠰h?i ???ƴ trong ngoƣ ??n ( ) v? ph?n c?ch nhau b멠dʵ phȹ.

- H?m c㠴hӠc㠴詠?a 30 ?詠nh?ng ?頤?i kh?ng v?qu? 255 kݠt? N驠dung c񡠨?m hay biӵ th??ng ??phа ch?u c?ch.

b - G䩠s?ng h?m b?ng Paste Function

- ?ƴ con trᠴ?i ? cǮ nh˰ h?m mɵ, r婠th?֮ l֮h Insert\ Function...

- Ch䮠nh㭠h?m cǮ th?֮ trong khung Function category.

- Ch䮠t?n h?m cǮ th?֮ trong khung Function name.

- Ch䮠n󴠏K. Excel ?㮧 h鰠?詠tho?i v? ??a t?n h?m l?n thanh c?ng th?ݠd?h䮠h?m tݮh trung b׮h c鮧: AVERAGE, sau khi ch䮠n󴠆inish, thanh c?ng th?Ӯ th޺ =AVERAGE(number 1, number 2,...)

- Nh˰ c?c ?詠s蠴heo quy ?ޮh c񡠴?h?m trong ngoƣ ??n.

- G⠰hݭ Enter (hoƣ kݣh OK), kմ qu? xuʴ hi֮ tr?n ? ?? ch䮮

2 - M鴠s蠨?m th?ng d?

a - H?m logic

a1. H?m AND(<Logic1>[,Logic2][,Logic3]...) : H?m cho kմ qu? ?󮧠khi v? chؠkhi tʴ c? c?c biӵ th?gic ?詠?󮧮 Ng?l?i m鴠trong c?c ?詠mang gi? trޠsai h?m tr? vҠkմ qu? sai.

Vݠd?=AND(-7<-1,6>3,3+1>3) ? TRUE

=AND(-7<-1,6>3,3+1>3+2) ? FALSE

a2. H?m OR(<Logic1>[,Logic2][,Logic3]...) : H?m cho kմ qu? ?󮧠khi c㠱 trong c?c biӵ th?gic ?󮧮 Ng?l?i h?m tr? vҠkմ qu? sai khi tʴ c? c?c ?詠mang gi? trޠsai

Vݠd?=OR(-7>-1,6<3,3+1>3) ? TRUE

=OR(-7>-1,6<3,3+1>3+2) ? FALSE

a3. H?m NOT(<Logic>) : H?m ph񠮞nh.

Vݠd?=NOT(FALSE) ? TRUE

=NOT(3+1<3+2) ? FALSE

a4. H?m IF (<BT logic>,<Gi? trޠ1>,<Gi? trޠ2>) : H?m tr? vҠ<Gi? trޠ1> nյ <BT logic> cho kմ qu? ?󮧬 ng?l?i h?m tr? vҠ<Gi? trޠ2> nյ <BT logic> cho kմ qu? sai.

Vݠd?=IF(-7>-1,"TRUE","FALSE") ? TRUE

=IF(-7>-1,6<3,3+1>3+2) ? FALSE

b - H?m s蠨䣠v? l? gi?c

b1. H?m ABS(<n>) : Tr? vҠgi? trޠtuyִ ?詠c񡠮.

Vݠd?=ABS(-7) ? 7

=ABS(6-140) ? 136

b2. H?m SQRT(<n>) : Tݮh c?n bˣ hai c񡠮 (n>0). Vݠd?=SQRT(9) ? 3

b3. H?m INT(n) : Cho phǮ nguy?n c񡠮. Vݠd?=INT(123.45) ? 123

b4. H?m EVEN(<n>) : L?m tr߮ ?ծ s蠮guy?n ch?n phݡ tr?n gǮ nhʴ c񡠮.

Vݠd?=EVEN(4.5) ? 6

b5. H?m ODD(<n>) : L?m tr߮ ?ծ s蠮guy?n lΠphݡ tr?n gǮ nhʴ c񡠮.

Vݠd?=ODD(4.5) ? 5

b6. H?m FACT(<n>) : H?m tݮh giai th?񡠮. Vݠd?=FACT(5) ? 120

b7. H?m LN(<n>) : Tr? vҠlogarit t??n c񡠮詠s蠮.

Vݠd?=LN(5) ? ~1.6

b8. H?m LOG10(<n>) : Tr? vҠlogarit theo c? s蠱0 c񡠮詠s蠮.

Vݠd?=LOG10(5) ? ~ 0.7

b9. H?m LOG(<n>,<c? s覧t;) : Tr? vҠlogarit theo c? s蠴?c񡠮詠s蠮

Vݠd?=LOG(4510) ? ~ 0.7

b10. H?m MOD(<n>,<d>) : Cho phǮ d? c񡠰hа chia nguy?n.

Trong ?㠺 - n l? s蠢ޠchia,

- d l? s蠣hia.

Vݠd?=MOD(10,3) ? 1

b11. H?m POWER(<n>,

) : Cho lu? m򠰠c񡠮.

Vݠd?=POWER(2,10) ? 1024

b12. H?m ROUND(n,d) : H?m l?m tr߮ s蠨n) ?ծ s蠴h?

Trong ?㠺 - n l? s蠣Ǯ l?m tr߮,

- d l? vޠtrݠcǮ l?m tr߮ ?ծ, nյ d > 0 h?m l?m tr߮ phǮ th˰ ph?n, nյ d = 0 h?m lʹ phǮ nguy?n, nյ d < 0 h?m l?m tr߮ phǮ nguy?n.

Vݠd?=ROUND(3.14159,2) ? 3.14

=ROUND(3.14159,0) ? 3

=ROUND(77777.1569,-3) ? 8000

b13. H?m SUM(D?y s詠: H?m tݮh t殧 c񡠤?y s?.

Vݠd?=SUM(3,5,4,7) ? 19

b14. H?m PRODUCT(D?y s詠: H?m tݮh tݣh c񡠤?y s讍

Vݠd?=PRODUCT(2,2,5) ? 20

b15. H?m SUMPRODUCT(D?y1, D?y2,...) : H?m tݮh t殧 c񡠴ݣh c?c ?宧 vޠth?nh phǮ trong c?c d?y s讍

Trong ?㠺 D?y1, D?y 2,... ph?i l? c?c d?y cﮧ chiҵ cﮧ s蠰hǮ t?Vݠd?=SUMPRODUCT({3;4;5},{2;5;3}) ? 41

b16. H?m SUMIF(Vﮧ ?iҵ ki֮,?iҵ ki֮ tݮh t殧,Vﮧ tݮh t殧) : H?m tݮh t殧 theo ?iҵ ki֮.

b17. H?m TRUNC(n,nd) : H?m c?t t??i ?詠n ?i nd s讠Nյ nd > 0 h?m c?t phǮ th˰ ph?n, nյ nd = 0 h?m c?t lʹ phǮ nguy?n, nյ nd < 0 h?m c?t phǮ nguy?n.

Vݠd?=TRUNC(12345.6789,2) ? 12345.67

=TRUNC(12345.6789,0) ? 12345

=TRUNC(12345.6789,-3) ? 12000

b18. H?m COMBIN(m,n) : H?m tݮh t栨ch˰ n c񡠭 phǮ t?Vݠd?=COMBIN(6,2) ? 15

b19. H?m MDETERM(M?ng) : Tݮh ?ޮh th?a ma trˮ.

Vݠd?= MDETERM({1,0,1;0,1,1;1,1,1}) ? -1

b20. H?m MINVERSE(M?ng) : Tݮh gi? trޠc񡠭a trˮ nghޣh ??o.

Vݠd?= MINVERSE({1,0;0,1}) ? 1

b21. H?m MMULT(M?ng1,M?ng2) : Tݮh tݣh c񡠲 ma trˮ.

Vݠd?= MMULT({1,0;0,1},{1,1;1,1}) ? -1

b22. H?m RADIANS(G㣩 : ?橠g㣠c㠳蠮o b?ng ?頳ang radians (G㣪PI()/180).

Vݠd?=RADIANS(90) ? 1.57

b23. H?m COS(r) : T׭ cos c񡠧㣠r c㠳蠮o b?ng radians.

Vݠd?=COS(Radians(60)) ? 0.5

b24. H?m SIN(r) : T׭ sin c񡠧㣠r c㠳蠮o b?ng radians.

Vݠd?=SIN(Radians(90)) ? 1

b25. H?m TAN(r) : T׭ tan c񡠧㣠r c㠳蠮o b?ng radians.

Vݠd?=TAN(Radians(45)) ? 1

c - H?m th讧 k?

c1. H?m MAX(<n1>[,n2][,n3]...) : H?m tr? vҠgi? trޠl�nhʴ c񡠤?y ?詮

Vݠd?D?ֵ trong c?c ? B1, B2, B3, B4 lǮ l?l? 4, 8, 6 v? 2. V˹ =MAX(B1:B4) cho gi? trޠ8

c2. H?m MIN(<n1>[,n2][,n3]...) : H?m tr? vҠgi? trޠnhᠮhʴ c񡠤?y ?詮

Vݠd??ֵ trong c?c ? B1, B2, B3, B4 lǮ l?l? 4, 8, 6 v? 2. V˹ =MIN(B1:B4) chogi? trޠ2

c3. H?m AVERAGE(<n1>[,n2][,n3]...) : H?m tr? vҠgi? trޠtrung b׮h c鮧 c񡠤?y ?詮

Vݠd?D?ֵ trong c?c ? B1, B2, B3, B4 lǮ l?l? 4, 8, 6 v? 2. C?ng th?? B6 l? : =AVERAGE(B1:B4,5). Gi? trޠtr? vҠtrong ? B6 l? 5.

c4. H?m RANK(<Gi? trޠxհ h?ng>,<Vﮧ tham kh?o>[,Kiӵ xհ h?ng]) : H?m t׭ th?g c񡠧i? trޠso v�vﮧ tham kh?o.

C?ng th?? C5 l? : =RANK(B5,$B$5:$B$9,1)

c5. H?m COUNT(<v1>[,v2][,v3]...) : ?խ s蠬? ?詠thu飠kiӵ d?ֵ s讍

Vݠd?=COUNT('BBC',12,'345',-2) ? 2

c6. H?m COUNTA(<v1>[,v2][,v3]...) : ?խ s蠬? ?詠c㠣h? liֵ.

Vݠd?=COUNT('BBC',12,'345',-2) ? 4

=COUNTA('BBC',12,'345',-2,' ') ? 4

d - H?m thꩠgian

d1. C?c h?m DAY, MONTH, YEAR(<"Date">) : H?m t?ch ng?y, th?ng hoƣ n?m t?u穠d?ֵ ng?y.

Vݠd?=DAY("6/12/2001") ? 12

=MONTH("6/12/2001") ? 6

=YEAR("6/12/2001") ? 2001

d2. H?m HOUR, MINUTE, SECOND(<"Time">) : H?m t?ch giꬠph󴠨oƣ gi?y t?u穠d?ֵ thꩠgian.

Vݠd?=HOUR("9:30:45") ? 9

=MINUTE("9:30:45") ? 30

=SECOND("9:30:45") ? 45

d3. H?m TODAY() : Lʹ ng?y hi֮ h?nh c񡠨֠th讧.

Vݠd?C?ng th?? B2 l? : =TODAY() ? "6/12/2001"

d4. H?m NOW() : H?m lʹ ng?y v? gi꠨i֮ h?nh c񡠨֠th讧.

Vݠd?C?ng th?? B3 l? : =NOW() ? "6/12/01 9:46"

d5. H?m DAYS360(<"Ng?y ?ǵ">,<"Ng?y cu試quot;>) : H?m tݮh s蠮g?y gi?ai m裠thꩠgian.

Vݠd?=DAYS360("4/15/1996","6/12/2001") ? 1857

=INT(DAYS360("4/15/1996","6/12/2001")/365) ? 5

e - H?m x? v?n b?n

e1. H?m CHAR(<Code>) : H?m tr? vҠkݠt?m? Code.

Vݠd?=CHAR(65) ? "A"

e2. H?m CODE(<Ch>) : H?m tr? vҠm? c񡠫ݠt?Ch.

Vݠd?=CODE("A") ? 65

e3. H?m LEFT(<X?u>,<n>) : H?m chа t?n tr?i <X?u> m鴠x?u con n kݠt?ݠd?=LEFT("Cao ??ng Ho? chʴ",7) ? "Cao ??ng"

e4. H?m RIGHT(<X?u>,<n>) : H?m chа t?n ph?i <X?u> x?u con n kݠt?ݠd?=RIGHT("Cao ??ng Ho? chʴ",9) ? "Ho? chʴ"

e5. H?m LOWER(<X?u>) : ?橠c?c kݠt? trong <X?u> th?nh kݠt?ꮧ.

Vݠd?=LOWER("Cao ??ng Ho? chʴ") ? "cao ??ng ho? chʴ"

e6. H?m UPPER(<X?u>) : ?橠c?c kݠt?ꮧ trong <X?u> th?nh kݠt?.

Vݠd?=UPPER("Cao dang Hoa chat") ? "CAO DANG HOA CHAT"

e7. H?m LEN(<X?u>) : H?m tr? vҠ?頤?i c񡠸?u kݠt?ݠd?=LEN("Cao ??ng Ho? chʴ") ? 16

e8. H?m TRIM(<X?u>) : H?m xo? c?c dʵ tr?ng v? nghܡ trong x?u.

Vݠd?=TRIM(" Cao ??ng Ho? chʴ ") ? "Cao ??ng Ho? chʴ"

e9. H?m PROPER(<X?u>) : H?m chuȮ ho? x?u kݠt?o khu?n d?ng h䠴?n.

Vݠd?=PROPER(" cAO ??ng hO? CHʴ ") ? "Cao ??ng Ho? Chʴ"

e10. H?m CONCATENATE(<X?u1>[,<X?u2>][,<X?u>]...) : H?m c鮧 x?u.

Vݠd?=CONCATENATE("Cao ??ng","Ho? chʴ") ? "Cao ??ngHo? chʴ"

e11. H?m MID(<X?u>,

,<n>) : H?m chа t?n ph?i <X?u> m鴠x?u con t? trݠth?�n kݠt?ݠd?=MID("L? Thanh T?m",4,5) ? "Thanh"

e12. H?m FIND(<X?u1>,<X?u2>[,

]) : H?m tr? vҠvޠtrݠb?t ?ǵ c񡠸?u1 ??t׭ thʹ trong x?u2 kӠt? trݠth?nյ ?詠p bޠbᠱua h?m t׭ ngǮ ?ޮh t?u x?u2).

Vݠd?=FIND("Ho? chʴ","Tr?ꮧ cao ??ng Ho? chʴ") ? 17

e13. H?m REPLACE(<X?u1>,

,<n>,<X?u2>) : H?m thay thՠn kݠt? x?u1 b?ng x?u2 t? trݠth?񡠸?u 1.

Vݠd?=REPLACE("Tr?ꮧ trung h䣠Ho? chʴ",8,9,"cao ??ng") ? " Tr?ꮧ cao ??ng Ho? chʴ"

f - H?m t׭ kiխ v? tra c?1. H?m t?o ?ޡ chؠ?

ADDRESS(<H?ng>,<C鴦gt;[,<Lo?i ?/c>][,<Kiӵ ?/c>][,<T?n b?ng tݮh>])

Trong ?㠺 - H?ng, c鴠l? to? ?頣񡠮ޡ chؠcǮ t?o.

- Lo?i ?ޡ chؠc㠴hӠl? 1 - (ngǭ ?ޮh) lo?i ?ޡ chؠtuyִ ?詻 2 - t??ng ?詠c鴬 tuyִ ?詠h?ng; 3 - tuyִ ?詠c鴬 t??ng ?詠h?ng; 4 - ?ޡ chؠt??ng ?詮

- Kiӵ ?ޡ chؠc㠴hӠl? 0 kiӵ ?ޡ chؠR[n]C[m]; 1 (ngǭ ?ޮh) kiӵ ?ޡ chؠl? <T?n c鴦gt;<T?n h?ng>;.

Vݠd?=ADDRESS(7,3,1) ? $C$7

= ADDRESS(7,3,2,0) ? R7C[3]

f2. H?m MATCH(<Gi? trޠt׭ kiխ>,<Vﮧ t׭ kiխ>[,<Kiӵ t׭ kiխ>]): H?m t׭ kiխ vޠtrݠc񡠦lt;Gi? trޠt׭ kiխ> trong <Vﮧ t׭ kiխ>.

Trong ?㠺 - Vﮧ t׭ kiխ ph?i l? m?ng 1 chiҵ.

- Kiӵ t׭ kiխ c㠴hӠl? 0 hoƣ 1. Nյ l? 1 thנvﮧ t׭ kiխ ph?i ??s?p xհ theo chiҵ t?ng dǮ, nյ kh?ng t׭ thʹ gi? trޠt׭ kiխ trong vﮧ t׭ kiխ thנh?m tr? vҠvޠtrݠc񡠰hǮ t? gi? trޠnhᠨ?n gi? trޠt׭ kiխ.

Nյ kiӵ t׭ l? 0 h?m chؠtr? vҠgi? trޠkhi t׭ ??chݮh x?c gi? trޠt׭ kiխ trong vﮧ t׭ kiխ, nյ kh?ng n㠴r? vҠgi? trޠ#N/A.

Vݠd?=Match(20,{10;15;20;30;40},1) ? 3

=Match(13,{10;15;20;30;40},1) ? 1

=Match(13,{10;15;20;30;40},0) ? #N/A

f3. H?m INDEX(<M?ng gi? trަgt;,<H?ng>,<C鴦gt;) : H?m t׭ kiխ gi? trޠtheo ?ޡ chؠtham chiյ.

Vݠd?=INDEX({10,15;20,30;40,30},1,2) ? 15

=INDEX({10;15;20;30;40},Match(15,{10;15;20;30;40},2) ? 15

f4. H?m

VLOOKUP(<G.trޠt׭ kiխ>,<Vﮧ g.trަgt;,<C鴠tham chiյ>[,<Kiӵ t׭>])

Trong ?㠺 - Vﮧ gi? trޠl? vﮧ ? trong ?㠣鴠?ǵ ????nh s蠬? 1 tr?n ?㠣h?nh s?ch gi? trޠt׭ kiխ, c?c c鴠kՠtiհ l? c?c c鴠ch?? trޠtham chiյ .

- C鴠tham chiյ l? s蠴h?c񡠣鴠tr?n vﮧ gi? trޠm? khi h?m t׭ thʹ, thנgi? trޠt??ng ?r?n h?ng ?㠳Ϡ??lʹ l?m kմ qu? tr? vҠc񡠨?m.

- Kiӵ t׭ c㠴hӠl? 0 hoƣ 1.

Nյ l? 0 (ngǭ ?ޮh) thנdanh s?ch gi? trޠt׭ kiխ kh?ng cǮ s?p xհ v? khi gi? trޠt׭ kiխ kh?ng kh�v�bʴ kנphǮ t?o trong danh s?ch gi? trޠt׭ kiխ thנh?m tr? vҠgi? trޠ#N/A.

Nյ l? 1 thנ:

- Danh s?ch gi? trޠt׭ kiխ ph?i ??s?p xհ theo chiҵ t?ng dǮ,

- Nյ gi? trޠt׭ kiխ nhᠨ?n phǮ t?u ti?n tr?n danh s?ch gi? trޠt׭ kiխ thנh?m tr? vҠgi? trޠ#N/A.

- Nյ gi? trޠt׭ kiխ l�h?n phǮ t?詠cﮧ tr?n danh s?ch gi? trޠt׭ kiխ thנcoi nh? ??t׭ thʹ 렰hǮ t?y.

- Nյ gi? trޠt׭ kiխ ??t׭ thʹ tr?n danh s?ch gi? trޠt׭ kiխ (kh?ng phǮ biִ ch?a ch??ꮧ) thנh?m tr? vҠgi? trޠ렣鴠tham chiҵ t??ng ?�h?ng ch?Ǯ t?ﮧ kh�v�gi? trޠt׭ kiխ.

Nh? v˹ cǮ d?o b?ng danh m?˴ t?, c?n c? m? v˴ t? ?iҮ d?ֵ t??ng ?ho c?c c鴠"T?n v˴ t?", "??n vޠtݮh","??n gi?".

Trong tr?ꮧ hn?y h?m VLOOKUP c㠴hӠgi?i quyմ ??y?u cǵ ?ƴ ra. C?Ӻ

Tr?n C13 nh˰ c?ng th?=VLOOKUP(C13,$A$4:$D$6,2,0)

Tr?n D13 nh˰ c?ng th?=VLOOKUP(C13,$A$4:$D$6,3,0)

Tr?n D13 nh˰ c?ng th?=VLOOKUP(C13,$A$4:$D$6,4,0)

f4. H?m HLOOKUP(<G.trޠt׭ kiխ>,<Vﮧ g.trަgt;,<H?ng tham chiյ>[,<Kiӵ t׭>])

H?m HLOOKUP c㠣󠰨?p t??ng t? h?m VLOOPKUP, nh?ng kh?c biִ 렣h砈LOOKUP l? h?m t׭ kiխ v? ?iҮ d?ֵ theo h?ng.

VIII - Qu?n l?hai th?c tְ Workbook

1 - Thao t?c v�b?ng tݮh

a - Khai b?o s蠢?ng tݮh trong tְ b?ng tݮh

+> V?o tr׮h ??n Tools ch䮠Options...

+> Khai b?o s蠢?ng tݮh trong m?heet in New Workbook.

+> Kh멠?鮧 l?i Excel.

b - Cḫ th?m b?ng tݮh m�v?o tְ b?ng tݮh

+> Ch䮠b?ng tݮh x?c ?ޮh vޠtrݠ(b?ng tݮh m�sϠcḫ v?o tr?�b?ng tݮh n?y).

+> M렴r׮h ??n Insert ch䮠l֮h Worksheet.

c - ?橠t?n b?ng tݮh

+> Ch䮠b?ng tݮh cǮ ?橠t?n.

+> M렴r׮h ??n Format ch䮠l֮h Sheet ? Rename.

+> Nh˰ t?n m�

d - Xo? b?ng tݮh

+> Ch䮠b?ng tݮh cǮ xo?

+> M렴r׮h ??n Edit ch䮠l֮h Delete Sheet.

e - Sao chа hoƣ di chuyӮ b?ng tݮh

+> Ch䮠b?ng tݮh cǮ sao chа hoƣ di chuyӮ.

+> M렴r׮h ??n Edit ch䮠l֮h Move or Copy Sheet...

+> ?Ӡdi chuyӮ ch䮠t?n tְ b?ng tݮh cǮ chа t�렔o book v? vޠtrݠcǮ chа t�tr?�b?ng tݮh n?o t?i Before sheet r婠nhʮ OK.

+> Mu讠sao chа l?m t??ng t? di chuyӮ v? ch䮠th?m m?reat a copy r婠nhʮ OK.

2 - Tְ kh?ng gian l?m vi֣

a - T?o tְ Workspace

+> M렣?c tְ Workbook mu讠??a v?o Workspace.

+> M렴r׮h ??n File ch䮠l֮h Save Workspace...

+> Nh˰ t?n tְ 렭?ile name r婠ch䮠Save (tְ Workspace c㠫iӵ XLW).

b - Hiֵ chخh

+> M렴ְ Workspace cǮ hiֵ chخh.

+> Th?֮ thao t?c ?㮧, m렣?c Workbook cǮ th?m hoƣ b�kh᩠Workspace.

+> Ghi tְ Workspace.

3 - Kմ n詠d?ֵ

<+> Kմ n詠d?ֵ gi??c b?ng tݮh.

?Ӡtham chiյ ?ޡ chؠt�b?ng tݮh kh?c cǮ ghi ?ޡ chؠtheo c󠰨?p :

<T?n b?ng tݮh>!<?ޡ chؠ?>

<+> Kմ n詠d?ֵ gi??c tְ b?ng tݮh.

?Ӡtham chiյ ?ޡ chؠt�b?ng tݮh trong tְ b?ng tݮh kh?c cǮ m렴ְ b?ng tݮh ?㠶? ghi ?ޡ chؠtheo c󠰨?p :

[Tְ b?ng tݮh]<T?n b?ng tݮh>!<?ޡ chؠ?>

IX - C? s려?ֵ tr?n b?ng tݮh

1 - Kh?i ni֭ CSDL tr?n excel

a. Kh?i ni֭ CSDL

Vﮧ CSDL l? vﮧ ? c㠣h? liֵ tr?n b?ng tݮh nh?ng ݴ nhʴ ph?i c㠨ai h?ng. H?ng ?ǵ ti?n ch??u ?Ҡc񡠣?c c鴠g䩠l? t?n vﮧ hay t?n tr?ꮧ (field name). T?n vﮧ ph?i c㠫iӵ d?ֵ chu穠v? kh?ng ??trﮧ nhau. C?c h?ng c߮ l?i ch?c d?ֵ v? ??g䩠l? b?n ghi (record).

b. C?c lo?i vﮧ

<+> Vﮧ CSDL.

<+> Vﮧ ti?u chuȮ l? vﮧ ch?ҵ ki֮ dﮧ thao t?c CSDL. Vﮧ ti?u chuȮ ??chia l?m hai lo?i :

+> Vﮧ ti?u chuȮ h?ng. Trong ?㠺

- T?n vﮧ ti?u chuȮ ph?i l? t?n tr?ꮧ ?? c㠴r?n vﮧ CSDL khai th?c ??dﮧ l?m ?iҵ ki֮ ?Ӡth?֮ c?c thao t?c v�vﮧ CSDL.

- ? ?iҵ ki֮ l? biӵ th? s?nh v�gi? trޠh?ng ti?u chuȮ. Ri?ng v�h?ng kiӵ chu穠Excel cho phа dﮧ kݠt? di֮ ?Ӡthiմ l˰ ?iҵ ki֮.

+> ?Ӡdi chuyӮ ch䮠t?n tְ b?ng tݮh cǮ chа t�렔o book v? vޠtrݠcǮ chа t�tr?�b?ng tݮh n?o t?i Before sheet r婠nhʮ OK.

+> Mu讠sao chа l?m t??ng t? di chuyӮ v? ch䮠th?m m?reat a copy r婠nhʮ OK.

2 - Tְ kh?ng gian l?m vi֣

a - T?o tְ Workspace

+> M렣?c tְ Workbook mu讠??a v?o Workspace.

+> M렴r׮h ??n File ch䮠l֮h Save Workspace...

+> Nh˰ t?n tְ 렭?ile name r婠ch䮠Save (tְ Workspace c㠫iӵ XLW).

b - Hiֵ chخh

+> M렴ְ Workspace cǮ hiֵ chخh.

+> Th?֮ thao t?c ?㮧, m렣?c Workbook cǮ th?m hoƣ b�kh᩠Workspace.

+> Ghi tְ Workspace.

3 - Kմ n詠d?ֵ

<+> Kմ n詠d?ֵ gi??c b?ng tݮh.

?Ӡtham chiյ ?ޡ chؠt�b?ng tݮh kh?c cǮ ghi ?ޡ chؠtheo c󠰨?p :

<T?n b?ng tݮh>!<?ޡ chؠ?>

<+> Kմ n詠d?ֵ gi??c tְ b?ng tݮh.

?Ӡtham chiյ ?ޡ chؠt�b?ng tݮh trong tְ b?ng tݮh kh?c cǮ m렴ְ b?ng tݮh ?㠶? ghi ?ޡ chؠtheo c󠰨?p :

[Tְ b?ng tݮh]<T?n b?ng tݮh>!<?ޡ chؠ?>

IX - C? s려?ֵ tr?n b?ng tݮh

1 - Kh?i ni֭ CSDL tr?n excel

a. Kh?i ni֭ CSDL

b. C?c lo?i vﮧ

<+> Vﮧ CSDL.

<+> Vﮧ ti?u chuȮ l? vﮧ ch?ҵ ki֮ dﮧ thao t?c CSDL. Vﮧ ti?u chuȮ ??chia l?m hai lo?i :

+> Vﮧ ti?u chuȮ h?ng. Trong ?㠺

- T?n vﮧ ti?u chuȮ ph?i l? t?n tr?ꮧ ?? c㠴r?n vﮧ CSDL khai th?c ??dﮧ l?m ?iҵ ki֮ ?Ӡth?֮ c?c thao t?c v�vﮧ CSDL.

- ? ?iҵ ki֮ l? biӵ th? s?nh v�gi? trޠh?ng ti?u chuȮ. Ri?ng v�h?ng kiӵ chu穠Excel cho phа dﮧ kݠt? di֮ ?Ӡthiմ l˰ ?iҵ ki֮.

+> ?Ӡdi chuyӮ ch䮠t?n tְ b?ng tݮh cǮ chа t�렔o book v? vޠtrݠcǮ chа t�tr?�b?ng tݮh n?o t?i Before sheet r婠nhʮ OK.

+> Mu讠sao chа l?m t??ng t? di chuyӮ v? ch䮠th?m m?reat a copy r婠nhʮ OK.

2 - Tְ kh?ng gian l?m vi֣

a - T?o tְ Workspace

+> M렣?c tְ Workbook mu讠??a v?o Workspace.

+> M렴r׮h ??n File ch䮠l֮h Save Workspace...

+> Nh˰ t?n tְ 렭?ile name r婠ch䮠Save (tְ Workspace c㠫iӵ XLW).

b - Hiֵ chخh

+> M렴ְ Workspace cǮ hiֵ chخh.

+> Th?֮ thao t?c ?㮧, m렣?c Workbook cǮ th?m hoƣ b�kh᩠Workspace.

+> Ghi tְ Workspace.

3 - Kմ n詠d?ֵ

<+> Kմ n詠d?ֵ gi??c b?ng tݮh.

?Ӡtham chiյ ?ޡ chؠt�b?ng tݮh kh?c cǮ ghi ?ޡ chؠtheo c󠰨?p :

<T?n b?ng tݮh>!<?ޡ chؠ?>

<+> Kմ n詠d?ֵ gi??c tְ b?ng tݮh.

?Ӡtham chiյ ?ޡ chؠt�b?ng tݮh trong tְ b?ng tݮh kh?c cǮ m렴ְ b?ng tݮh ?㠶? ghi ?ޡ chؠtheo c󠰨?p :

[Tְ b?ng tݮh]<T?n b?ng tݮh>!<?ޡ chؠ?>

IX - C? s려?ֵ tr?n b?ng tݮh

1 - Kh?i ni֭ CSDL tr?n excel

a. Kh?i ni֭ CSDL

b. C?c lo?i vﮧ

<+> Vﮧ CSDL.

<+> Vﮧ ti?u chuȮ l? vﮧ ch?ҵ ki֮ dﮧ thao t?c CSDL. Vﮧ ti?u chuȮ ??chia l?m hai lo?i :

+> Vﮧ ti?u chuȮ h?ng. Trong ?㠺

- T?n vﮧ ti?u chuȮ ph?i l? t?n tr?ꮧ ?? c㠴r?n vﮧ CSDL khai th?c ??dﮧ l?m ?iҵ ki֮ ?Ӡth?֮ c?c thao t?c v�vﮧ CSDL.

- ? ?iҵ ki֮ l? biӵ th? s?nh v�gi? trޠh?ng ti?u chuȮ. Ri?ng v�h?ng kiӵ chu穠Excel cho phа dﮧ kݠt? di֮ ?Ӡthiմ l˰ ?iҵ ki֮.

+> ?Ӡdi chuyӮ ch䮠t?n tְ b?ng tݮh cǮ chа t�렔o book v? vޠtrݠcǮ chа t�tr?�b?ng tݮh n?o t?i Before sheet r婠nhʮ OK.

+> Mu讠sao chа l?m t??ng t? di chuyӮ v? ch䮠th?m m?reat a copy r婠nhʮ OK.

2 - Tְ kh?ng gian l?m vi֣

a - T?o tְ Workspace

+> M렣?c tְ Workbook mu讠??a v?o Workspace.

+> M렴r׮h ??n File ch䮠l֮h Save Workspace...

+> Nh˰ t?n tְ 렭?ile name r婠ch䮠Save (tְ Workspace c㠫iӵ XLW).

b - Hiֵ chخh

+> M렴ְ Workspace cǮ hiֵ chخh.

+> Th?֮ thao t?c ?㮧, m렣?c Workbook cǮ th?m hoƣ b�kh᩠Workspace.

+> Ghi tְ Workspace.

3 - Kմ n詠d?ֵ

<+> Kմ n詠d?ֵ gi??c b?ng tݮh.

?Ӡtham chiյ ?ޡ chؠt�b?ng tݮh kh?c cǮ ghi ?ޡ chؠtheo c󠰨?p :

<T?n b?ng tݮh>!<?ޡ chؠ?>

<+> Kմ n詠d?ֵ gi??c tְ b?ng tݮh.

?Ӡtham chiյ ?ޡ chؠt�b?ng tݮh trong tְ b?ng tݮh kh?c cǮ m렴ְ b?ng tݮh ?㠶? ghi ?ޡ chؠtheo c󠰨?p :

[Tְ b?ng tݮh]<T?n b?ng tݮh>!<?ޡ chؠ?>

IX - C? s려?ֵ tr?n b?ng tݮh

1 - Kh?i ni֭ CSDL tr?n excel

a. Kh?i ni֭ CSDL

b. C?c lo?i vﮧ

<+> Vﮧ CSDL.

<+> Vﮧ ti?u chuȮ l? vﮧ ch?ҵ ki֮ dﮧ thao t?c CSDL. Vﮧ ti?u chuȮ ??chia l?m hai lo?i :

+> Vﮧ ti?u chuȮ h?ng. Trong ?㠺

- T?n vﮧ ti?u chuȮ ph?i l? t?n tr?ꮧ ?? c㠴r?n vﮧ CSDL khai th?c ??dﮧ l?m ?iҵ ki֮ ?Ӡth?֮ c?c thao t?c v�vﮧ CSDL.

- ? ?iҵ ki֮ l? biӵ th? s?nh v�gi? trޠh?ng ti?u chuȮ. Ri?ng v�h?ng kiӵ chu穠Excel cho phа dﮧ kݠt? di֮ ?Ӡthiմ l˰ ?iҵ ki֮.

+> ?Ӡdi chuyӮ ch䮠t?n tְ b?ng tݮh cǮ chа t�렔o book v? vޠtrݠcǮ chа t�tr?�b?ng tݮh n?o t?i Before sheet r婠nhʮ OK.

+> Mu讠sao chа l?m t??ng t? di chuyӮ v? ch䮠th?m m?reat a copy r婠nhʮ OK.

2 - Tְ kh?ng gian l?m vi֣

a - T?o tְ Workspace

+> M렣?c tְ Workbook mu讠??a v?o Workspace.

+> M렴r׮h ??n File ch䮠l֮h Save Workspace...

+> Nh˰ t?n tְ 렭?ile name r婠ch䮠Save (tְ Workspace c㠫iӵ XLW).

b - Hiֵ chخh

+> M렴ְ Workspace cǮ hiֵ chخh.

+> Th?֮ thao t?c ?㮧, m렣?c Workbook cǮ th?m hoƣ b�kh᩠Workspace.

+> Ghi tְ Workspace.

3 - Kմ n詠d?ֵ

<+> Kմ n詠d?ֵ gi??c b?ng tݮh.

?Ӡtham chiյ ?ޡ chؠt�b?ng tݮh kh?c cǮ ghi ?ޡ chؠtheo c󠰨?p :

<T?n b?ng tݮh>!<?ޡ chؠ?>

<+> Kմ n詠d?ֵ gi??c tְ b?ng tݮh.

?Ӡtham chiյ ?ޡ chؠt�b?ng tݮh trong tְ b?ng tݮh kh?c cǮ m렴ְ b?ng tݮh ?㠶? ghi ?ޡ chؠtheo c󠰨?p :

[Tְ b?ng tݮh]<T?n b?ng tݮh>!<?ޡ chؠ?>

IX - C? s려?ֵ tr?n b?ng tݮh

1 - Kh?i ni֭ CSDL tr?n excel

a. Kh?i ni֭ CSDL

b. C?c lo?i vﮧ

<+> Vﮧ CSDL.

<+> Vﮧ ti?u chuȮ l? vﮧ ch?ҵ ki֮ dﮧ thao t?c CSDL. Vﮧ ti?u chuȮ ??chia l?m hai lo?i :

+> Vﮧ ti?u chuȮ h?ng. Trong ?㠺

- T?n vﮧ ti?u chuȮ ph?i l? t?n tr?ꮧ ?? c㠴r?n vﮧ CSDL khai th?c ??dﮧ l?m ?iҵ ki֮ ?Ӡth?֮ c?c thao t?c v�vﮧ CSDL.

- ? ?iҵ ki֮ l? biӵ th? s?nh v�gi? trޠh?ng ti?u chuȮ. Ri?ng v�h?ng kiӵ chu穠Excel cho phа dﮧ kݠt? di֮ ?Ӡthiմ l˰ ?iҵ ki֮.

+> ?Ӡdi chuyӮ ch䮠t?n tְ b?ng tݮh cǮ chа t�렔o book v? vޠtrݠcǮ chа t�tr?�b?ng tݮh n?o t?i Before sheet r婠nhʮ OK.

+> Mu讠sao chа l?m t??ng t? di chuyӮ v? ch䮠th?m m?reat a copy r婠nhʮ OK.

2 - Tְ kh?ng gian l?m vi֣

a - T?o tְ Workspace

+> M렣?c tְ Workbook mu讠??a v?o Workspace.

+> M렴r׮h ??n File ch䮠l֮h Save Workspace...

+> Nh˰ t?n tְ 렭?ile name r婠ch䮠Save (tְ Workspace c㠫iӵ XLW).

b - Hiֵ chخh

+> M렴ְ Workspace cǮ hiֵ chخh.

+> Th?֮ thao t?c ?㮧, m렣?c Workbook cǮ th?m hoƣ b�kh᩠Workspace.

+> Ghi tְ Workspace.

3 - Kմ n詠d?ֵ

<+> Kմ n詠d?ֵ gi??c b?ng tݮh.

?Ӡtham chiյ ?ޡ chؠt�b?ng tݮh kh?c cǮ ghi ?ޡ chؠtheo c󠰨?p :

<T?n b?ng tݮh>!<?ޡ chؠ?>

<+> Kմ n詠d?ֵ gi??c tְ b?ng tݮh.

?Ӡtham chiյ ?ޡ chؠt�b?ng tݮh trong tְ b?ng tݮh kh?c cǮ m렴ְ b?ng tݮh ?㠶? ghi ?ޡ chؠtheo c󠰨?p :

[Tְ b?ng tݮh]<T?n b?ng tݮh>!<?ޡ chؠ?>

IX - C? s려?ֵ tr?n b?ng tݮh

1 - Kh?i ni֭ CSDL tr?n excel

a. Kh?i ni֭ CSDL

b. C?c lo?i vﮧ

<+> Vﮧ CSDL.

<+> Vﮧ ti?u chuȮ l? vﮧ ch?ҵ ki֮ dﮧ thao t?c CSDL. Vﮧ ti?u chuȮ ??chia l?m hai lo?i :

+> Vﮧ ti?u chuȮ h?ng. Trong ?㠺

- T?n vﮧ ti?u chuȮ ph?i l? t?n tr?ꮧ ?? c㠴r?n vﮧ CSDL khai th?c ??dﮧ l?m ?iҵ ki֮ ?Ӡth?֮ c?c thao t?c v�vﮧ CSDL.

- ? ?iҵ ki֮ l? biӵ th? s?nh v�gi? trޠh?ng ti?u chuȮ. Ri?ng v�h?ng kiӵ chu穠Excel cho phа dﮧ kݠt? di֮ ?Ӡthiմ l˰ ?iҵ ki֮.

+> ?Ӡdi chuyӮ ch䮠t?n tְ b?ng tݮh cǮ chа t�렔o book v? vޠtrݠcǮ chа t�tr?�b?ng tݮh n?o t?i Before sheet r婠nhʮ OK.

+> Mu讠sao chа l?m t??ng t? di chuyӮ v? ch䮠th?m m?reat a copy r婠nhʮ OK.

2 - Tְ kh?ng gian l?m vi֣

a - T?o tְ Workspace

+> M렣?c tְ Workbook mu讠??a v?o Workspace.

+> M렴r׮h ??n File ch䮠l֮h Save Workspace...

+> Nh˰ t?n tְ 렭?ile name r婠ch䮠Save (tְ Workspace c㠫iӵ XLW).

b - Hiֵ chخh

+> M렴ְ Workspace cǮ hiֵ chخh.

+> Th?֮ thao t?c ?㮧, m렣?c Workbook cǮ th?m hoƣ b�kh᩠Workspace.

+> Ghi tְ Workspace.

3 - Kմ n詠d?ֵ

<+> Kմ n詠d?ֵ gi??c b?ng tݮh.

?Ӡtham chiյ ?ޡ chؠt�b?ng tݮh kh?c cǮ ghi ?ޡ chؠtheo c󠰨?p :

<T?n b?ng tݮh>!<?ޡ chؠ?>

<+> Kմ n詠d?ֵ gi??c tְ b?ng tݮh.

?Ӡtham chiյ ?ޡ chؠt�b?ng tݮh trong tְ b?ng tݮh kh?c cǮ m렴ְ b?ng tݮh ?㠶? ghi ?ޡ chؠtheo c󠰨?p :

[Tְ b?ng tݮh]<T?n b?ng tݮh>!<?ޡ chؠ?>

IX - C? s려?ֵ tr?n b?ng tݮh

1 - Kh?i ni֭ CSDL tr?n excel

a. Kh?i ni֭ CSDL

b. C?c lo?i vﮧ

<+> Vﮧ CSDL.

<+> Vﮧ ti?u chuȮ l? vﮧ ch?ҵ ki֮ dﮧ thao t?c CSDL. Vﮧ ti?u chuȮ ??chia l?m hai lo?i :

+> Vﮧ ti?u chuȮ h?ng. Trong ?㠺

- T?n vﮧ ti?u chuȮ ph?i l? t?n tr?ꮧ ?? c㠴r?n vﮧ CSDL khai th?c ??dﮧ l?m ?iҵ ki֮ ?Ӡth?֮ c?c thao t?c v�vﮧ CSDL.

- ? ?iҵ ki֮ l? biӵ th? s?nh v�gi? trޠh?ng ti?u chuȮ. Ri?ng v�h?ng kiӵ chu穠Excel cho phа dﮧ kݠt? di֮ ?Ӡthiմ l˰ ?iҵ ki֮.

+> ?Ӡdi chuyӮ ch䮠t?n tְ b?ng tݮh cǮ chа t�렔o book v? vޠtrݠcǮ chа t�tr?�b?ng tݮh n?o t?i Before sheet r婠nhʮ OK.

+> Mu讠sao chа l?m t??ng t? di chuyӮ v? ch䮠th?m m?reat a copy r婠nhʮ OK.

2 - Tְ kh?ng gian l?m vi֣

a - T?o tְ Workspace

+> M렣?c tְ Workbook mu讠??a v?o Workspace.

+> M렴r׮h ??n File ch䮠l֮h Save Workspace...

+> Nh˰ t?n tְ 렭?ile name r婠ch䮠Save (tְ Workspace c㠫iӵ XLW).

b - Hiֵ chخh

+> M렴ְ Workspace cǮ hiֵ chخh.

+> Th?֮ thao t?c ?㮧, m렣?c Workbook cǮ th?m hoƣ b�kh᩠Workspace.

+> Ghi tְ Workspace.

3 - Kմ n詠d?ֵ

<+> Kմ n詠d?ֵ gi??c b?ng tݮh.

?Ӡtham chiյ ?ޡ chؠt�b?ng tݮh kh?c cǮ ghi ?ޡ chؠtheo c󠰨?p :

<T?n b?ng tݮh>!<?ޡ chؠ?>

<+> Kմ n詠d?ֵ gi??c tְ b?ng tݮh.

?Ӡtham chiյ ?ޡ chؠt�b?ng tݮh trong tְ b?ng tݮh kh?c cǮ m렴ְ b?ng tݮh ?㠶? ghi ?ޡ chؠtheo c󠰨?p :

[Tְ b?ng tݮh]<T?n b?ng tݮh>!<?ޡ chؠ?>

IX - C? s려?ֵ tr?n b?ng tݮh

1 - Kh?i ni֭ CSDL tr?n excel

a. Kh?i ni֭ CSDL

b. C?c lo?i vﮧ

<+> Vﮧ CSDL.

<+> Vﮧ ti?u chuȮ l? vﮧ ch?ҵ ki֮ dﮧ thao t?c CSDL. Vﮧ ti?u chuȮ ??chia l?m hai lo?i :

+> Vﮧ ti?u chuȮ h?ng. Trong ?㠺

- T?n vﮧ ti?u chuȮ ph?i l? t?n tr?ꮧ ?? c㠴r?n vﮧ CSDL khai th?c ??dﮧ l?m ?iҵ ki֮ ?Ӡth?֮ c?c thao t?c v�vﮧ CSDL.

- ? ?iҵ ki֮ l? biӵ th? s?nh v�gi? trޠh?ng ti?u chuȮ. Ri?ng v�h?ng kiӵ chu穠Excel cho phа dﮧ kݠt? di֮ ?Ӡthiմ l˰ ?iҵ ki֮.

+> ?Ӡdi chuyӮ ch䮠t?n tְ b?ng tݮh cǮ chа t�렔o book v? vޠtrݠcǮ chа t�tr?�b?ng tݮh n?o t?i Before sheet r婠nhʮ OK.

+> Mu讠sao chа l?m t??ng t? di chuyӮ v? ch䮠th?m m?reat a copy r婠nhʮ OK.

2 - Tְ kh?ng gian l?m vi֣

a - T?o tְ Workspace

+> M렣?c tְ Workbook mu讠??a v?o Workspace.

+> M렴r׮h ??n File ch䮠l֮h Save Workspace...

+> Nh˰ t?n tְ 렭?ile name r婠ch䮠Save (tְ Workspace c㠫iӵ XLW).

b - Hiֵ chخh

+> M렴ְ Workspace cǮ hiֵ chخh.

+> Th?֮ thao t?c ?㮧, m렣?c Workbook cǮ th?m hoƣ b�kh᩠Workspace.

+> Ghi tְ Workspace.

3 - Kմ n詠d?ֵ

<+> Kմ n詠d?ֵ gi??c b?ng tݮh.

?Ӡtham chiյ ?ޡ chؠt�b?ng tݮh kh?c cǮ ghi ?ޡ chؠtheo c󠰨?p :

<T?n b?ng tݮh>!<?ޡ chؠ?>

<+> Kմ n詠d?ֵ gi??c tְ b?ng tݮh.

?Ӡtham chiյ ?ޡ chؠt�b?ng tݮh trong tְ b?ng tݮh kh?c cǮ m렴ְ b?ng tݮh ?㠶? ghi ?ޡ chؠtheo c󠰨?p :

[Tְ b?ng tݮh]<T?n b?ng tݮh>!<?ޡ chؠ?>

IX - C? s려?ֵ tr?n b?ng tݮh

1 - Kh?i ni֭ CSDL tr?n excel

a. Kh?i ni֭ CSDL

b. C?c lo?i vﮧ

<+> Vﮧ CSDL.

<+> Vﮧ ti?u chuȮ l? vﮧ ch?ҵ ki֮ dﮧ thao t?c CSDL. Vﮧ ti?u chuȮ ??chia l?m hai lo?i :

+> Vﮧ ti?u chuȮ h?ng. Trong ?㠺

- T?n vﮧ ti?u chuȮ ph?i l? t?n tr?ꮧ ?? c㠴r?n vﮧ CSDL khai th?c ??dﮧ l?m ?iҵ ki֮ ?Ӡth?֮ c?c thao t?c v�vﮧ CSDL.

- ? ?iҵ ki֮ l? biӵ th? s?nh v�gi? trޠh?ng ti?u chuȮ. Ri?ng v�h?ng kiӵ chu穠Excel cho phа dﮧ kݠt? di֮ ?Ӡthiմ l˰ ?iҵ ki֮.

Vݠd?L˰ b?ng tݮh sau :

- Vﮧ ti?u chuȮ m? ng?nh CHC01

M? ng?nh

CHC01

- Vﮧ ti?u chuȮ t殧 ?iӭ b?ng 18.5 :

T殧 ?iӭ

18.5

- Vﮧ ti?u chuȮ t殧 ?iӭ l�h?n 15 :

T殧 ?iӭ

>15

- Vﮧ ti?u chuȮ m? ng?nh c㠳 kݠt? kh?c CHC :

M? ng?nh

<>CHC*

+> Vﮧ ti?u chuȮ c?ng th?l? vﮧ ti?u chuȮ m? ? ?iҵ ki֮ c㠤?ng c?ng th?o?i vﮧ ti?u chuȮ n?y ph?i thᡠm?n hai ?iҵ ki֮ sau :

- T?n vﮧ ph?i l? t?n kh?c v�tʴ c? c?c t?n tr?ꮧ trong vﮧ CSDL khai th?c.

- C?c ?ޡ chؠtham chiյ trong ? ?iҵ ki֮ nյ c㠰h?i l? lo?i ?ޡ chؠt??ng ?詠v? l? ? ?ǵ ti?n c񡠴r?ꮧ ??dﮧ l?m ti?u chuȮ.

Vݠd?L˰ vﮧ ti?u chuȮ n?m sinh 1983 thנ? ?iҵ ki֮ ch?ng th? =year(C4)=1983, kմ qu? tr? vҠc㠴hӠl? True hoƣ False.

N?m sinh 1983

True

+> Kմ hti?u chuȮ : L? vi֣ li?n kմ c?c ? ?iҵ ki֮, dﮧ trong tr?ꮧ hmu讠thao t?c CSDL v�nhiҵ ?iҵ ki֮ kh?c nhau.

- Nյ c?c ? ?iҵ ki֮ kh?c c鴠thנch󮧠c㠴ݮh chʴ giao (And),

- Nյ c?c ? ?iҵ ki֮ kh?c h?ng thנch󮧠c㠴ݮh chʴ h(Or).

Vݠd?- L˰ vﮧ ti?u chuȮ v�?iҵ ki֮ m? ng?nh l? CHC04 hoƣ CHC05 v? t宧 ?iӭ l�h?n 16 :

M? ng?nh T殧 ?iӭ

CHC04 >16

CHC05 >16

- L˰ vﮧ ti?u chuȮ v�?iҵ ki֮ ?iӭ c񡠳 m?n (DT, DL, DH) kh?c 0 v? t殧 ?iӭ l�h?n hoƣ b?ng 15 : ? ?iҵ ki֮ ?iӭ 3 m?n kh?c 0 c㠣?ng th? "=OR(E4=0,F4=0,G4=0)=FALSE"

?iӭ liִ T殧 ?iӭ

TRUE >=15

- L˰ vﮧ ti?u chuȮ v�?iҵ ki֮ DT hoƣ DH l�h?n 9

DT DH

2 - Thao t?c v�CSDL

a - S?p xհ d?ֵ

+> Ch䮠vﮧ CSDL cǮ s?p xհ.

+> M렴r׮h ??n Data ch䮠l֮h Sort...

+> Ch䮠Header row trong m?y list has ?ӠExcel nhˮ d?ng t?n c?c tr?ꮧ.

+> Ch䮠kho? s?p xհ chݮh tr?n h鰠Sort by v�chiҵ s?p xհ : Ascending - T?ng dǮ hoƣ Descending - Gi?m dǮ.

+> Ch䮠c?c kho? s?p xհ ph??n c?c h鰠Then by.

+> Ch䮠OK ?Ӡtiծ h?nh s?p xհ.

* L?u ?ong tr?ꮧ hmu讠hu?s?p xհ th?֮ l֮h Undo Sort trong tr׮h ??n Edit ngay sau khi th?֮ l֮h s?p xհ.

b - Trݣh l䣠d?ֵ

+> T?o vﮧ ti?u chuȮ.

+> M렴r׮h ??n Data ch䮠l֮h Filter ? Advanced Filter...

+> Ch䮠c?ch trݣh l䣠렭?ction, nյ ch䮠Filter the list, in-place c?c b?n ghi tho? m?n ?iҵ ki֮ trݣh l䣠sϠhiӮ thޠngay tr?n vﮧ CSDL khai th?c, nյ ch䮠Copy to another location Excel sϠchа c?c b?n ghi tho? m?n ?iҵ ki֮ trݣh l䣠sang ?ޡ chؠkhai b?o trong h鰠Copy to.

+> Trong h鰠List range nh˰ ?ޡ chؠc񡠶ﮧ CSDL khai th?c.

+> Trong h鰠Criteria range nh˰ ?ޡ chؠc񡠶ﮧ CSDL ti?u chuȮ.

+> Trong h鰠Copy to nh˰ ?ޡ chؠ?ǵ m? c?c b?n ghi tho? m?n ?iҵ ki֮ sϠ??chа ?ծ.

+> Nյ tu?n Unique records only ??kݣh ho?t thנtrong khi trݣh l䣠nh? b?n ghi tho? m?n ?iҵ ki֮ m? trﮧ nhau sϠbޠlo?i bᠶ? chؠch䮠lʹ 1.

+> Ch䮠OK ?Ӡth?֮ l֮h.

d - C?c h?m CSDL

d1. H?m DSUM(<Vﮧ CSDL khai th?c>,<Stt tr?ꮧ>,<Vﮧ ti?u chuȮ>).

H?m tݮh t殧 c?c gi? trޠtr?n tr?ꮧ v�STT ??chؠra thu飠vﮧ CSDL khai th?c c񡠣?c b?n ghi tho? m?n ?iҵ ki֮.

Vݠd?Tr?n B?ng k? chi tiմ h?ng ho? b?n ra (phǮ VII-2-f-f4) tݮh t殧 s蠴iҮ b?n h?ng c񡠭ƴ h?ng RAM 32MB c㠭? M002.

- Vﮧ ti?u chuȮ :

21 M? VT

22 M002

- H?m gi?i quyմ y?u cǵ : =DSUM($A$12:$G$16,7,$A$21:$A$22)

d2. H?m DMIN(<Vﮧ CSDL khai th?c>,<Stt tr?ꮧ>,<Vﮧ ti?u chuȮ>).

H?m tݭ c?c gi? trޠnhᠮhʴ tr?n tr?ꮧ v�s蠴h???chؠra thu飠vﮧ CSDL khai th?c c񡠣?c b?n ghi tho? m?n ?iҵ ki֮.

Vݠd?V�b?ng B?o c?o kմ qu? tuyӮ sinh n?m 2001 렴rang sau,

- T׭ t殧 ?iӭ thi thʰ nhʴ c񡠣?c thݠsinh ??ng k?g?nh CHC01 :

=DMIN($A$3:$I$8,9,$C$10:$C$11) ? 10.5

hoƣ =DMIN($D$3:$I$8,6,$C$10:$C$11) ? 10.5

d3. H?m DMAX(<Vﮧ CSDL khai th?c>,<Stt tr?ꮧ>,<Vﮧ ti?u chuȮ>).

H?m tݭ c?c gi? trޠl�nhʴ tr?n tr?ꮧ v�STT ??chؠra thu飠vﮧ CSDL khai th?c c񡠣?c b?n ghi tho? m?n ?iҵ ki֮.

Vݠd?

T׭ t殧 ?iӭ thi cao nhʴ c񡠣?c thݠsinh ??ng k?g?nh CHC01 :

=DMAX($A$3:$I$8,9,$C$10:$C$11) ? 18.5

d4. H?m DAVERAGE(<Vﮧ CSDL khai th?c>,<Stt tr?ꮧ>,<Vﮧ TC>).

H?m tݮh gi? trޠtrung b׮h tr?n tr?ꮧ v�STT ??chؠra thu飠vﮧ CSDL khai th?c c񡠣?c b?n ghi tho? m?n ?iҵ ki֮.

Vݠd?

T׮h t殧 ?iӭ thi trung b׮h c񡠣?c thݠsinh :

=DAVERAGE($A$3:$I$8,9,$I$10:$I$11) ? 15.5

d5. H?m DCOUNT(<Vﮧ CSDL khai th?c>,<Stt tr?ꮧ>,<Vﮧ TC>).

H?m ?խ s蠫 ch? liֵ kiӵ s蠴r?n tr?ꮧ v�STT ??chؠra thu飠vﮧ CSDL khai th?c c񡠣?c b?n ghi tho? m?n ?iҵ ki֮.

Vݠd?

T׭ t殧 s蠴hݠsinh c㠴殧 ?iӭ thi l�h?n hoƣ b?ng 16.5 :

=DCOUNT($A$3:$I$8,9,$B$10:$B$11) ? 3

hoƣ =DCOUNT($A$3:$I$8,7,$B$10:$B$11) ? 3

d6. H?m DCOUNTA(<Vﮧ CSDL khai th?c>,<Stt tr?ꮧ>,<Vﮧ TC>).

H?m ?խ s蠫 ch? liֵ tr?n tr?ꮧ v�STT ??chؠra thu飠vﮧ CSDL khai th?c c񡠣?c b?n ghi tho? m?n ?iҵ ki֮.

Vݠd?

T׭ t殧 s蠴hݠsinh c㠴殧 ?iӭ thi l�h?n hoƣ b?ng 16.5 v? m? ng?nh ??ng kݠCHCO1 :

=DCOUNT($A$3:$I$8,3,$B$10:$C$11) ? 1

hoƣ =DCOUNT($A$3:$I$8,7,$B$10:$C$11) ? 1

e - T殧 hd?ֵ theo nh㭍

+> Ch䮠vﮧ CSDL cǮ t殧 htheo nh㭮

+> S?p xհ l?i vﮧ CSDL cho tr?ꮧ ??ch䮠?Ӡt殧 h

+> M렴r׮h ??n Data chon l֮h Subtotals...

+> Ch䮠t?n tr?ꮧ cǮ t殧 htrong h鰠At each change in.

+> Ch䮠h?m tݮh to?n khi t殧 htrong h鰠Use function.

+> Ch䮠c?c tr?ꮧ cǮ t殧 htheo h?m ?? ch䮠tr?n h鰠Add subtotal.

+> Kݣh ho?t c?c tu?n Replace current subtotal (thay thӠb?ng t殧 hm�cho b?ng t殧 h?? c㩬 Page break between groups (t?o dʵ ng?t trang gi??c nh㭩, Summary below data (th?m d߮g t殧 kմ v?o b?n d?�vﮧ CSDL ??t殧 h.

+> Ch䮠OK ?Ӡth?֮ l֮h hoƣ ch䮠Remove All ?Ӡhu?t殧 htheo nh㭮

X - Biӵ ?半1 - T?o biӵ ?半+> T?o vﮧ CSDL.

+> Th?֮ l֮h Chart... trong tr׮h ??n Insert.

Ch䮠kiӵ biӵ ?場rong h鰠Chart type tr?n thΠStandard Types, sau ?㠫ݣh n󴠎ext.

+> Khai b?o vﮧ CSDL cǮ t?o biӵ ?場rong h鰠Data range, r婠ch䮠kiӵ ph?n tݣh d?ֵ theo c?c h?ng (Rows) hoƣ theo c?c c鴠(Columns) ? Next

+> Ch䮠thΠTitles ?Ӡth?m c?c ti?u ?Ҡcho biӵ ?堺 Chart title (t?n biӵ ?婬 Value (X) axis (t?n tr?), Value (Y) axis (t?n tr?) ? Next

+> Ch䮠n?i ?ƴ biӵ ?堣㠴hӠl? :

? As new sheet : ?ƴ biӵ ?場r?n m鴠Sheet m�v? lʹ t?n l? Chart1.

? As object in : Ch䮠b?ng tݮh tu?Ӡ?ƴ biӵ ?宍

Kݣh Finish ?Ӡkմ th󣠣?ng vi֣ t?o biӵ ?宍

2 - Hiֵ chخh biӵ ?半+> Ch䮠?詠t? cǮ hiֵ chخh (Chart area, Plot area, Chart title, Data series, Axis, Legend, Axis title).

+> M렴r׮h ??n Format ch䮠l֮h Select XXX... ?Ӡtiծ h?nh hiֵ chخh.

XI - Ho?n thi֮ v? in b?ng tݮh

1 - C?i ?ƴ trang in

Th?֮ l֮h Page Setup... trong tr׮h ??n File :

a - ?ƴ trang : Ch䮠thΠPage tr?n h鰠tho?i Page Setup.

+> Ch䮠h?� giʹ in : Portrait (in d䣩 hoƣ Landscape (in ngang) tr?n m?rientation.

+> Tr?n m?caling :

- Ch䮠Adjust to ?Ӡ?iҵ chخh l?i t?kݣh th?�trang cho ph

- Ch䮠Fit to nյ mu讠m렲鮧 kݣh th?�c񡠴rang th?nh nhiҵ trang tu?

+> Paper size dﮧ ch䮠kh栧iʹ.

+> Print quality : l?䮠chʴ l? b?n in .

b - ?ƴ kݣh th?�lҠ: Ch䮠thΠMargins v? nh˰ v?o gi? trޠc?c lҠv? ti?u ?Ү

c - Th?m ti?u ?Ҡv?o trang in

Ch䮠thΠHeader/Footer r婠ch䮠tiհ c?c ti?u ?Ҡ?? ??c?i ?ƴ s?n tr?n h鰠Header v? Footer

Trong tr?ꮧ hmu詠t?o c?c ti?u ?Ҡv�n驠dung tu?ch䮠n󴠃ustom Header (Custom Footer) :

Nh˰ n驠dung v?o h鰠?ޮh danh vﮧ t??ng ?

2 - Duyִ b?ng tݮh tr?�khi in

Th?֮ l֮h Print Preview trong tr׮h ??n File, Excel chuyӮ sang chՠ?頰review dﮧ hiֵ chخh, kiӭ duyִ l?i t殧 thӠb?ng tݮh tr?�khi quyմ ?ޮh in ra giʹ

3 - In b?ng tݮh

+> ChuyӮ tְ s栴ݮh cǮ in th?nh hi֮ h?nh,

+> Th?֮ l֮h Print... trong tr׮h ??n File

+> Kiӭ tra l?i c?c th?ng s蠶Ҡm?y in tr?n m?rinter,

+> Ch䮠vﮧ in 렭?rint range,

+> Ch䮠?詠t? in tr?n m?rint what, c㠴hӠl? m鴠trong c?c l?䮠sau :

- Selection : vﮧ b?ng tݮh ?? ??ch䮠tr?�

- Active sheet(s) : b?ng tݮh hi֮ h?nh,

- Entire worbook : to?n b頳栴ݮh.

+> ?ƴ s蠬? b?n in v? th?in tr?n m?opies.

+> Nhʮ n󴠏K ?Ӡb?t ?ǵ in b?ng tݮh. C㠴hӠnhʮ Cancel ?Ӡhu? t?c v?

Bạn đang đọc truyện trên: AzTruyen.Top

Trước Sau

Tags: #technology