Creating your Spreadsheet with MS Excel 2007 is just quite simple. Whether you are already a power user or just a beginner starting using of Excel spreadsheet for your office task, you can find out what you need in hundreds of practical examples, popular techniques, and friendly tips in this comprehensive resource. You could master the new Office's Ribbon user interface, the smart way to create formulas. You could also know how to make the enhancement of your worksheet with graphic charts and smart art tools, how to summarize and consolidate your data with specific commands, how to analyze your data using pivot tables, or how to seek a properly best answer for problems using analysis solver . . . and much more. If you want to really understand what and how an Excel spreadsheet can serve you, this book is the right place for you to practice.

Easy MS Excel 2007

By Thong Ong

AuthorHouse

Copyright © 2009 Thong Ong
All right reserved.
ISBN: 978-1-4389-9224-2

Chapter One

Computer for EveryBody TIN HOC CHO MOI NGU''I

Gi'i thieu:

VE MICROSOFT EXCEL 2007

Thu'a cc ban,

Microsoft Excel l phan mem chuyn dng cho bang tnh dien tu' (Worksheet). Ban cung dong y rang d l mot phan mem hau nhu' khng th' thieu tai van phng lm viec cua mnh. T' phin ban thuo' so' khai l Excel 4.0, hng Microsoft d lin tuc nng cap thnh cc phin ban Excel 5.0, Excel 7.0, Excel 97, Excel 2000, Excel XP, v gan dy Excel 2003. Tat ca cc phin ban nay deu c mot giao dien hao hao giong nhau, nn ngu'i su' dung de dng tiep thu phin ban m'i du'a trn kinh nghiem phin ban tru''c.

The nhu'ng v'i phin ban Microsoft Office Excel 2007 (Excel Version 12), bang mot giao dien rat la lam, c the gy nh'c dau cho ngu'oi da t'ng su' dung Excel ! Chnh ban thn chng ti cung ho ang hon v ngan ngo' khi tiep can lan dau v'i Excel 2007, do vay chng ti thay can phai viet lai tap gio trnh nay, nhu' mot cch gip ban doc nhanh chng lm chu du'o'c Excel, neu my cua ban da ci bo Microsoft Office 2007.

Tru''ng hop ban dang ci bo Office XP hoac Office 2003, chng ti khuyn ban c' de vay m dng, v cc bo Office nay, theo chng ti, l rat thn thien v hau nhu' da dat den dinh cua su' hoan hao.

1. BO CHU'O'NG TRNH EXCEL 2007 :

De dng du'o'c Microsoft Excel 2007, ban phai c mot my vi tnh v'i cau hnh tu'o'ng doi manh, v'i he giao dien Windows XP hoac Windows Vista, c ci dat Microsoft Excel 2007 (trong bo Office 2007) dng cch. Ngu''i su' dung di nhin da quen thao tc trn giao dien Windows. Neu c nhu cau hien thi van ban tieng Viet, can ci dat thm bo go de go tieng Viet, v du phan mem VietKey.

2. KHO'I DONG EXCEL 2007 :

(1) Mo my, he Windows (cc Version Windows XP hoac Vista) du'o'c nap.

(2) Kch hoat Excel bang cch:

Thng thuong, ban goi Excel qua con duo'ng chnh ngach bang cach nhan Start, chon All Programs, chon Microsoft Office, roi chon Excel 2007 theo hnh du''i tri.

Neu Excel v'a du'oc su' dung tru''c d, th khi goi Start, trong Start Menu thu''ng c san bieu tu'o'ng Excel trong do nhu' hinh trn phai, ban nhap vo de kch hoat.

* Viec Excel nam trong nhnh no cua Start Menu th phai ty vo thu'c te cua he Windows trn my m ban dang dng.

Hoac neu da c tao mot Shortcut cho Excel 2007 trn mn hnh Desktop nhu' hnh sau thi bn nhap dup vo bieu tu'o'ng nay hoac nhap chon n roi Enter.

* Ve cch lm the no de tao Shortcut trn Desktop cho mot 'ng dung, xin xem cc ti lieu Giao dien do hoa Windows.

* Neu ban chu'a tien xem cc ti lieu Windows, hay nhan nt Start, chon All Programs, chon Microsoft Office, r den Microsoft Excel, roi nhan nt phai chuot (right click) de goi Menu di dong, trong d chon Send To -> Desktop nhu' hnh sau.

3. MN HNH EXCEL 2007 KHI V'A KHO'I DONG :

Sau khi du'o'c nap, mn hnh Excel 2007 c dng dap gan nhu' trn, l cua bo Excel 2007, du''i mi tru''ng Windows Vista, du'o'c dng de soan sch nay, thu'c te c the khc di cht, nhu'ng khng anh hu'o'ng nhieu den viec lap v xu' ly bang tnh cua ban.

Bang tnh Excel du'o'c to ch'c nhu' mot quyen tap (workbook) gom nhieu trang (goi l worksheet), ngam dinh c 3 sheets (c the thm b't). Moi Sheet gom nhieu hang (Row) dnh so 1, 2, 3 ..., moi hng c nhieu cot (Column) ky hieu A, B, C ... Khu vu'c giao nhau giu'a hng v cot to thnh mot goi l Cell. Trong Cell c the ch'a tri so, van ban, hoac tri cng th'c.

V'i mn hnh trn dy:

1. Dng trn cng goi l Title Bar (thanh tiu d) d d c tn ti liu (Book1) v tn ca 'ng dung (Microsoft Excel), bn tri c bieu tu'o'ng Office Button hnh trn, m khi nhap chuot vo d se by ra Menu (hnh sau) v'i cc linh nhu' New, Open, Save, Save As, Print ... den Close, neu nhap vo nt Exit Excel, ban dng cc bang tnh dang xu' ly dong th'i ket thc Excel.

2. Bn tri Title bar l thanh Quick access toolbar, ch'a cc cng cu thu''ng su' dung nhu' Save, Undo ... Ban c the gan thm hoac bo bt cc bieu tu'o'ng ty thch. Bn phai Title bar l cc nt Minimize (thu nh), Maximize (phng l'n) v Close (dng).

3. Dng th' hai l mot Ribbon (dai ru bang) gom nhieu Tab (t' Home den View), Ribbon du'o'c thiet ke nham gip ngu''i su' dung nhanh chng tm cc linh can thiet de hon thnh cng viec. Trong moi Tab, cc linh du'o'c ket thnh t'ng nhm goi l cc Group. V du trong Tab Home c cc Group Clipboard, Font v.v ... Ribbon c tnh thun gian cnh cup cnh xo, khi cu'a so Excel no' rong th cc Group by ra du cc linh con, neu thu hep cu'a so th cc Group co cum lai, nhap vo m'i xo ra.

De do roi mat, mot so Tab c th du'o'c Excel giau b't di, chi by ra trong tnh huong thch ho'p. V du Tab Format ca Picture Tools hoac Tab Format cua Drawing Tools chi xuat hien khi doi tu'o'ng lin he l Picture hay AutoShape dang du'o'c chon.

Neu ban chon mot Ribbon Tab bat ky (v du Tab Home) roi nhan nt phai chuot de mo Menu di dong, xong chon Minimize the Ribbon th phan Ribbon du'o'c thu gon lai chi cn tn cc Tab m thi, cc Group deu bi che nhu'o'ng cho cho bang tnh.

Khi dng Ribbon dang du'o'c thu gon, ban thao tc tu'o'ng tu' se bung ra nhu' cu.

Thao tc nhap dp vo mot Tab bat ky cua Ribbon (hoac bam Ctrl-F1) cung thu gon Ribbon neu dang hien cc Group, v ngu'o'c lai, by ra cc Group neu dang thu gon. Viec thu gon dai Ribbon khng anh hu'o'ng den viec xu' ly bang tnh, v'i Ribbon dang thu gon, ban c the nhap vo mot Tab no d, cc Group trong Tab s hien ra, ban su' dung ty y, sau d Ribbon tu' dong thu lai. Khng thay cch no de xa bo hoac thay Ribbon bang cc Menu v Toolbar nhu' trong Excel cua cc phin ban tru'o'c.

Ghi ch : Ribbon du'o'c to ch'c theo he thong gom 3 cap :

1. Tabs: Cc go Tab giang ngang pha trn. Moi Tab ch'a bieu tu'o'ng cc linh lin quan den mot linh vu'c thao tc.

2. Groups: Trong Tab c nhieu Group (nhm), mot Group tap ho'p cc cng viec lin he v'i nhau. Trong Group c nhieu Commands (linh), v du Group Font cua Tab Home ch'a bieu tu'o'ng dng vo muc dch trnh by nhu' dng khung, chon Font chu', co' chu', mu chu', dng chu' ... Tu'o'ng tu', Group Alignment th gom cc bieu tu'o'ng canh bin, ch'a le v.v.... Neu gc du'o'i phai cua mot Group co bieu tu'o'ng Dialog Box Launcher [??] th khi nhap ch uot vo d se mo hop thoai tu'o'ng 'ng, v du hop thoai Format Cells - Font de dinh dang Font chu, hop thoai Format Cell - Alignment de dinh dang doan v.v....

3. Commands: Mot Command c the du'o'c bieu hien du'o'i dang mot nt (button), neu c thm nt [down arrow] bn canh th dng mo' hop thoai de chon, hoac mo menu tiep theo.

Khi nhap bieu tu'o'ng Office Button (hnh trn), Excel se by ra Menu v'i cc linh t' New den Close nhu' trn.

Nt Excel Options dng quy dinh cc ty chon khi su' dung Excel v cch thao tc khi nhap so lieu. Excel 2007 thu'o'ng dinh san kh nhieu ch'c nang dieu chinh tu' dong, tuy kh thng minh nhu'ng di khi lm ban bu'c mnh v qu lm linh. Nt Excel Options se gip ban chi dinh cc ty chon theo y mnh.

Nt Exit Excel th dng cc bang tnh hien hnh dong th'i ket thc Excel.

4. Thanh doc bn phai khu vu'c bang tnh goi la Vertical Scroll bar (thanh tru'o't doc) dng de tru'o't ln hoac xuong theo chieu doc bang cch nhap vo mui tn ln hay mui tn xuong cua thanh nay, hoac r khoi vung trong thanh tru'o't. Thanh ngang pha du''i khu vu'c bang tnh l Horizontal Scroll bar (thanh tru'o't ngang) dng tru'o't qua hay lai theo chieu ngang.

5. Bn tri gan thanh tru'o't ngang l cc g' Sheet Tab, thao tc trn Sheet no th nhap chuot vo g' Sheet d. Bieu tu'o'ng Insert Worksheet dng de chn thm mot Sheet m'i. Dn nt Sheet navigator bn tri dng de cuon cc g' Sheet Tab qua lai, trong tru''ng ho'p bang tnh c nhieu Sheet khong nhn thay het trong vng Sheet Tab.

6. Dng pha du''i cng l Status bar (thanh tnh trang) trn d cho biet mot so chi tiet ve bang tnh dang thu'c hien.

* Bn tri cho biet tnh trang hien hnh cua bang tnh l Ready (san sng) hoac Edit (dang hieu dnh).

* Vng ke tiep hien thi cc so tm lu'o'c cua khoi Cell dang chon nhu' so trung binh, so dem, v tong.

* Khoang giu'a l dn nt chon che do hien thi cho ti lieu: (1) Normal l cch nhn bnh thu'o'ng v quen thuoc, (2) Page Layout nhn ti lieu gan giong nhu' khi in ra giay, v (3) Page Break Preview cho php chi dinh phai ngat trang o' vi tr no. Thng thu'o'ng, ban nn chon che do Normal v de thao tc.

* St bn phai l thanh tru'o't Zoom, dng dieu chinh l'n nho cho kch co nhn cua ti lieu, nhap vo cc nt se tang hoac giam 10%.

4. KHI NIEM VE BANG TNH EXCEL

4.1 VAI TR BANG TNH

Microsoft Excel l mot phan mem cu'c manh dng de to ch'c, phn tch, v trnh by so lieu. Microsoft Excel gom c 5 ch'c nang chnh :

(1) Worksheet (Bang tnh) Nhap, lu'u tru', phn tch, tnh ton trn so lieu.

(2) Chart (Do thi) Bieu dien so lieu du'o'i dang do thi.

(3) Database (Co' so du' lieu) Quan ly so lieu c khoi lu'o'ng lon.

(4) Macro (Linh vi m) Tu' dong ha cc cng viec can lap lai nhieu lan

(5) Advanced Formatting Graphics Tao lap cc bo bieu chuyn nghiep (Dinh dang v do hoa cao cap)

Bang tnh Excel l mot loai ti lieu c kha nang bien doi nhu'ng noi dung thng tin hon don tro thnh mot tap hop so lieu c to ch'c. Bang tnh du'o'c bo tr trong mot tap sch (Workbook) gom nhieu trang (Worksheet), moi trang l mot bang phn chia thnh cc cot (Columns) v hng (Rows) lap thnh nhu'ng do'n vi xu' ly du'o'c goi l (Cells), ngu'o'i ta c the nhap vo Cell cc dang so lieu khc nhau nhu' van ban (text), tri so (number), cng th'c (formula) hay ngy gi' (date and time). Chng ta c the sao chep (Copy) hay di chuyen (Move) so lieu t' Cell nay sang Cell khc bang cch ban hnh cc linh thch hop qua bn phm hoac r chng t' noi nay den tha xuong noi khc bang thiet bi chuot. The manh cua bang tnh dien t' l cc cng th'c (Formula). fl c th l nh~ng biu th'c ton hqc (s hqc hay di s) hoc biu th'c x' ly chui (ghp ni, ct, trch dgan ...) hay biu th'c luan ly (Logic hay Boolean). Bieu th'c trong cng th'c bao gi' cung cho mot tri v d l tri linh hoat lun phan anh tnh hnh thu'c tai cua bang tnh. Khi mot thnh phan lien quan den cng th'c du'o'c (hay bi) thay doi, thi tri cua cng th'c lap t'c tu' dong diu chinh theo. Chng ta c the sap xep so lieu trong bang tnh theo trt tu' can khai thc, thu'c hien cc php phn tch hay tong hop. Neu can, c the bo cuc thnh dn bi phn ra tu'ng dang cap cho cc dng trong bang tnh. Ngoi ra cn c the lin ket so lieu giu'a cc bang tnh voi nhau, tao thnh mot the thong nhat giu'a hai hoac nhieu bang tnh trong cng mt co' so du' lieu, de cho khi thay doi so lieu trong bang tnh nay, th bang tnh khc cung dong thoi bien chuyen theo. Trong tru'ong hop can thiet, thng qua ch'c nang Visual Basic Editor, ngu'oi ta c the lap trnh bang ngn ngu' Excel Basic de xu' ly tu' dong thay cho nhu'ng loat thao tc thu cng, hoac can thiep su ho'n vo qu trnh tnh ton o hau tru'ong.

4.2 MOT SO MINH HOA

Du'oi dy l minh hoa cho mot vi bang tnh don gian :

Bang tnh thu'ong can du'o'c in an ra phuc vu ngu'oi su' dung. Viec trnh by v dinh dang cho bang tnh cung nhu' xem thu' tru''c khi in l mot thu'c te can thiet nhu'ng rat de dng. So lieu trong bang tnh c the dng de tao ra cc do thi, mt dang thng tin de tiep nhan v t nh'c dau.

5. CO' CAU CUA MOT BANG TNH :

Bang tnh Excel 2007 du'o'c xem nhu' mot quyen tap (Workbook) gom tu' 1 den toi da bao nhiu to (Worksheet) chu'a xc dinh du'o'c, ve ly thuyet th toi da 255 to, nhu'ng ti da thu' toi 4.096 to van khong thay cu' nu' g, khng dm thu' thm v so my diec (ch' Excel chu'a diec). Moi Sheet xem nhu' mot trang giay ke gom 16.384 cot (t'c 128^2 Columns) v 1.048.576 hng (t'c 1.024^2 Rows). Khu vu'c giao nhau giu'a mot cot v mot hng th goi l Cell (), nhu' vay moi Sheet c tat ca:

1.048.576 Hng x 16.384 cot = 17.179.869.184 Cells

cn trong mot Workbook neu c 4.096 Sheet th c

17.179.869.184 Cells x 4.096 to = 70.368.744.177.664 Cells

Trong Cell th ch'a so lieu v Cell l don vi co so de xu' ly cua bang tnh. Moi Cell c the ch'a du'o'c mot chuoi den 32.767 ky tu' (tu'o'ng du'o'ng 7 trang giay dnh my). Neu dn so nu'oc ta cho l 80 trieu, so lieu moi ngu'oi ghi trong mot Cell th chi can 1 Workbook trong d c 1 Worksheet l du' s'c qua cau.

Cc Worksheet du'o'c dat tn ngam dinh bat dau l Sheet1, den Sheet2, Sheet3, v.v ... Ngu'oi dng c the su'a lai tn cho cc Sheet neu muon.

Cc cot du'oc dat nhan tu' tri sang phai, bat dau l cot A tiep theo l cot B, C, ... cho den Z. Lien sau cot Z, la cc cot AA, AB, ... den AZ, roi BA, BB, ... den BZ, ... roi ZA, ZB, ... den ZZ, tiep theo l AAA, AAB, ... den AAZ, roi ABA, ABB, ... den ABZ, c' vay cho den XEA, XEB, ... XEZ v nhm cuoi cng l XFA, XFB, XFC v XFD l du 16.384 cot.

Cc hng du'o'c dnh so lien tuc tu' trn xuong, t' hng 1 den hng 1.048.576.

Neu ngu'oi su' dung khng quen voi cch dat nhan A, B, C, ... cho cot, th c the chon so th' tu', lin tuc tu' 1 den 16384 tnh t' bn tri tro di. Tuy nhin cch nay se gap kh nhieu bat tien khi tham chieu den dia chi cc Cell nn chng ti khng khuyen khch dng trong gio trnh nay.

De phn biet Cell nay voi Cell khc trn cng mot Sheet (trong so hon 17 ty Cells cua mot Sheet), moi Cell deu co mot dia chi (Cell Address). Dia chi Cell du'o'c lap thnh bang cch :

* Neu chon cch goi cot bang cc nhan A, B, C.... : Dia chi Cell gom ky hieu cot v so hieu hng, v du Cell A130, B747, Z27, X30, v.v... V Cell cuoi cng trong mot Sheet l Cell XFD1048576.

* Neu goi cot bang so th' tu' t' 1 den 16384 : Dia chi Cell c dang RnCm (t'c Row no, Column no), v du R1C1, R5C7 v.v... v Cell cuoi cng cua mot Sheet l R16384C1048576.

* Ca 2 tru'o'ng hop, dia chi Cell khng phn biet chu hoa hay chu' thu'o'ng.

De phn biet Cell nay voi Cell khc giu'a cc Sheet (trong so ho'n 4.096 Sheet cua mot Workbook), phai ghi dia chi Cell lien sau tn Sheet noi voi nhau bang dau cham than. V du Sheet3!B12 l Cell nam trn cot B hng 12 cua Sheet3.

Trong gio trnh nay chng ti khng p dung kieu dia chi RnCm. Neu ban gap phai mot bo Excel no m khi ln mn hnh, cc cot deu mang so thay v chu, th ban hay nhan Office Button, chon Excel Options, Formulas, du'i muc Working with Formulas ban nhap tat muc [x] R1C1 Reference Style (xem hnh sau), xong OK.

6. SO LIEU TRONG BANG TNH :

Ban du'a so lieu hay cng th'c vo bang tnh bang cch nhp vo cc Cell nhu'ng noi dung m ban can. Noi dung Cell c the l van ban (Text), tri so (Numbers), hay cng th'c (Formulas). Excel tu' dong tnh lai gi tri cho cc cng th'c moi khi bn thm hay su'a doi so lieu trn bang tnh. Hnh th'c bieu dien thng tin bn trong cc Cell cung de dng dieu chinh ve kch co (Size), kieu dng (Style), hay mu sac (Color). Bn cung c the gan vo bang tnh cc doi tu'o'ng do hoa (Graphic Objects) nham t diem thm cho bang tnh cua ban. Ban cn c the lin ket (Link) cc bang tnh lai voi nhau, v du nhu' lin ket so lieu cua cc bo co hng thng de san xuat ra bo co cuoi quy hay cuoi nam. V trong tru''g hop nay, nhu'ng su'a doi thu'c hien trn cc bang tnh hang thng se su'a doi lun ca so lieu tu'o'ng 'ng lin he trong bang tnh cua quy hay cua ca nam.

(Continues...)

Excerpted from Easy MS Excel 2007by Thong Ong Copyright © 2009 by Thong Ong. Excerpted by permission.
All rights reserved. No part of this excerpt may be reproduced or reprinted without permission in writing from the publisher.
Excerpts are provided by Dial-A-Book Inc. solely for the personal use of visitors to this web site.