Bài Giảng Microsoft Excel 2000

.Có 4 kiểu xoá: • All: Xoá toàn bộ • Formats: Chỉ xoá phần định dạng của ô • Contents: Chỉ xoá phần nội dung dữ liệu trong ô • Comments: Chỉ xoá phần chú thích của ô • Di chuyển hoặc sao

Microsoft Excel 2000

Giới thiệu chung

Chương 1: Các khái niệm cơ bản

2 Môi trường làm việc của Excel

Thanh menu

Thanh công cụ Thanh định dạng

Tên Sheet

Vùng làm việc

3 Cửa sổ Workbook:

Một Workbook tối đa 255 sheets.

- Đổi tên Sheets:

- Kích chuột lên tên sheet

- Chọn Format/Rename hoặc Kích chuột phải lên tên Sheets chọn Rename

Cấu tạo một sheet:

+ Một sheet có 256 cột (Column) được đánh số bằng các kí tự

chữ cái: A, B, AA,AB IV gọi là địa chỉ cột

+ Một sheet co 65536 hàng (Rows) được đánh số từ 1, 2,

65536 gọi là địa chỉ hàng

+ Giao của hàng và cột là ô (cell) Một ô được thông qua <địa chỉ cột> và <địa chỉ hàng> Ví dụ: A1 (cột A dòng 1)

4 Cách nhập dữ liệu:

theo hoặc Tab để sang ô bên cạnh

5 Các kiểu dữ liệu:

• Nhãn hoặc chuỗi (Label): Là một hoặc nhiều kí tự chữ và số Không dùng giá trị là Label để tính toán Một Label có thể dài 32000 kí tự nhưng chỉ có 1020 kí tự được in ra

• Kiểu số (Number): Số có thể chứa các kí tự bao gồm: 0, 1, 2, 3, 4, 5,

6, 7, 8, 9 +, -, (), /, %, E, e Excel xem ngày và thời gian như là số do đó nếu chúng ta nhập thời gian và ngày tháng không hợp lệ nó sẽ biến thành nhãn Chúng ta có thể thay đổi kiều ngày tháng mặc định theo ý mình bằng cách:

- Chọn Start/Settings/Control Panel/ Chọn Regional Settings/Date và khai báo lại kiểu ngày tháng

.

• Công thức: Nếu một ô là kết quả của một công thức thì nó có dạng:

=công thức

- Ví dụ: =A8*B7

- Công thức có thể có các hàm tính toán

- Các toán tử và ý nghĩa của chúng:

Địa chỉ tương đối và địa chỉ tuyệt đối:

• Địa chỉ tương đối:

– Khi sao chép công thức có địa chỉ tương đối thì địa chỉ ô trong công thức sẽ được cập nhật và thay đổi thành địa chỉ tương đối khác

=<địa chỉ cột><địa chỉ hàng>  =B5

• Địa chỉ tuyệt đối:

– Khi sao chép công thức có địa chỉ tuyệt đối thì địa chỉ ô trong công thức không thay đổi

=$<địa chỉ cột>$<địa chỉ hàng>  =$B5; =B$5; =$B$5

• Chèn chú thích vào ô:

thích và chọn Insert comment Gõ chú thích vào hộp Kết thúc bằng kích chuột ra khỏi ô muốn chỉnh sửa lại chú thích chọn Edit comment

5 Xử lý dữ liệu:

• Xoá dữ liệu:

• Chọn ô chứa dữ liệu cần xóa Chọn Edit/Clear .Có 4 kiểu xoá:

• All: Xoá toàn bộ

• Formats: Chỉ xoá phần định dạng của ô

• Contents: Chỉ xoá phần nội dung dữ liệu trong ô

• Comments: Chỉ xoá phần chú thích của ô

• Di chuyển hoặc sao chép dữ liệu:

• Chọn ô hoặc vùng cần di chuyển (sao chép)

+ Di chuột nằm trên khung viền ngoài của khối ô chọn, giữ chuột

và kéo đến vị trí cần di chuyển đến (nếu sao chép vừa di chuột vừa giữ Ctrl)

+ Hoặc chọn Edit/Cut (hoặc Copy) xác định ô đén chọn Edit/Paste

Đánh dãy số tự động cho một cột

dãy số thì chúng ta có thể đánh tự động tăng dần bằng cách như sau:

– Gõ vào ô đầu tiên giá trị đầu tiên của dãy

– Gõ vào ô thứ hai giá trị thứ hai của dãy

– Chọn cả hai ô

– Kích chuột vào dấu hình vuông ở góc ô

– Giữ và kéo chuột đến hết dãy

Ví dụ :

2 Chèn ô, chèn dòng, chèn cột

• Chèn ô:

• Chọn Insert/cells/ Hộp thoại chèn ô, hàng, cột hiện ra.

– Chọn Shift cells right (Chèn khối ô và đẩy dữ liệu sang phải bắt đầu

từ cột đầu tiên khối ô chọn), – Shift cells down (Chèn khối ô và đẩy dữ liệu xuống dưới bắt đầu từ hàng đầu tiên trong khối ô chọn)

3 Đặt Password

• Vào Tools/Protection

• Protect Sheets: Đặt mật khẩu cho Sheet

• Protect Workbook: Đặt cho tệp (hay Work)

• Protect and Sharing WorkBook: Đặt mật khẩu và chia sẻ Work

Chèn thêm Sheet mới

Vào Insert/WorkSheet

4 Định dạng cho bảng tính

a, Font

– Đặt Font mặc định:

đánh dấu Normal font

– Đặt font thay đổi

– Chọn Format/Cells/Font Sau đó chọn kiểu font, màu font (color), kích cỡ font (font size) .Ok

• Tắt tất cả đường lưới: Tools/Option/View

c Thay đổi thuộc tính hàng

d Thay đổi thuộc tính cột

• Đánh dấu cột

• Chọn Format/Column/Width – ẩn cột

• Chọn Format/ Column /Hide

– Hiện lại cột

• Chọn Format/ Column /Unhide

e Quy định dữ liệu hiển thị trong ô

• Chọn cột hoặc ô cần

quy định kiểu hiển thị

• Chọn Format/Cells

• Chọn Alignment

• Left: Trái sang

• Right: Phải sang

• Center: Giữa

• Muốn viết trên nhiều ô

• Chọn Meger cell

f Quy định kiểu dữ liệu trong ô

• Ví dụ:

- Hình bên: Sắp xếp Stt,

Ho va ten, LCB

5 Tính năng AutoFormat

Tự động định dạng theo một số khuôn mẫu sẵn

• Đánh dấu vùng cần định dạng

• Vào Format/AutoFormat

6 Định dạng theo yêu cầu

• Vào Format/Conditional Formating

Ví dụ

Bước 1: Chọn kiểu cho biểu đồ

Bước 2: Chọn vùng dữ liệu nguồn

Bước 3: Đặt tên và các thuộc tính khác

Bước 4: Chọn Sheet để đặt biểu đồ

Ví dụ

Chương 3: In ấn bảng tính

• 1 Định dạng trang in

• Page: Định dạng trang

• Margins: Căn lề trang

• Heeder/Footer: Thêm tiêu

đề đầu và cuối trang

• Sheet: Chọn in Sheet nào, trong khoảng nào

Tab Sheet

Tab Margin

Xem trước khi in

Loại hàm Nội dung

Math&Trig Các hàm toán học,l ợng giác

Date&Time Các hàm ngày tháng và giờ

Text Các hàm về đến v n b n ăn bản ản

Statistical Các hàm trung binh, tính

tổng Look up

&Reference Các hàm tim kiếm

4 C¸c hµm th«ng dông

- Hµm Sum(d·y sè): tÝnh tæng cho mét d·y sè VÝ dô

Hµm Average( d·y sè): TÝnh trung binh céng VÝ dô

- Hµm Max(d·y sè): Cho ra sè lín nhÊt cña d·y sè

- Hµm Min(d·y sè): Cho sè nhá nhÊt trong d·y sè

5.ưHàmưLogicư(ưLogical) -ưHàmưAnd(dk1,ưdk2,ưdk3 dkn):ưHàmưtr ưvềưkếtưqu ưlàưtrueư ải cuối , con trỏ biến dấu ải cuối , con trỏ biến dấu khiưtấtưc ưcácưđiềuưkiệnưlàưtrue.ưCònưlạiưlàưhàmưtr ưvềưFalse.ư ải cuối , con trỏ biến dấu ải cuối , con trỏ biến dấu -ưHàmưOr(ưđk1,ưđk2 đkn):ưhàmưtr ưvềưkếtưqu ưlàưTrueưkhiưchỉư ải cuối , con trỏ biến dấu ải cuối , con trỏ biến dấu cầnưmộtưtrongưcácưđiềuưkiệnưlàưtrue.ưCònưlạiưhàmưtr ưvềưfalse.ư ải cuối , con trỏ biến dấu -ưHàmưNot(ưđk):ưChoưkếtưqu ưlàưđúngưkhiưđkưlàưsaiưvàưngượcưlại.ư ải cuối , con trỏ biến dấu

- HàmưIf(<đk>,ưgiáưtrịưđúng,ưgiáưtrịưsai):ưHàmư

kiểmưtraưbiểuưthứcưđkưnếuưđkưđúngưthỡưkếtư

qu ưcủaưhàmưsẽưđượcưtr ưvềưtrongưgiáưtrịư ải cuối , con trỏ biến dấu ải cuối , con trỏ biến dấu

đúng.ưNgượcưlạiưhàmưtr ưkqưlàưgíaưtrịưsai ải cuối , con trỏ biến dấu

*ưChúưýư:ưtrongưđkưnếuưph iưkiểmưtraưtừưhaiư ải cuối , con trỏ biến dấu

đkưtrởưlênưthỡưtaưph iưđặtưcácưđkưđóưtrongư ải cuối , con trỏ biến dấu

hàmưand(),ưhoặcưor()

víưdụ

6 Các hàm v n b n(text) ăn bản ản

- Hàm Left(<btvanban>,n): Cho kết qu là n kí tự ản lấy từ bên trái Ví dụ

- Hàm Right( <btký tự>,n): Cho kết qu là n ký tự ản lấy từ bên ph i ản

- Hàm Mid( <btkí tự>,m,n): lấy ra n kí tự từ kí tự

thứ m của <btkí tự> Ví dụ

- Hàm Len( <btkí tự>): Cho kết qu là một số ản

biểu thị độ dài của <btkí tự> Ví dụ

- Hàm Lower(<btkí tự>): Chuyển đổi <btkítự>

thành ch th ờng Ví dụ ữ thường Ví dụ

- Hàm Upper(<btkí tự>) : chuyển đổi <btkí tự >

thành ch hoa ví dụ ữ thường Ví dụ

- Hàm Proper(<btkítự>):Chuyển đổi <btkt> thành

một chuỗi mà các kí tự đầu của mỗi từ là ch hoa ữ thường Ví dụ

ví dụ

- Hàm Replace( <btkítự1>,m,n, <btkítự2>): từ vị trí

thứ m và lây ra n kí tự của <btkítự1> Thay vào đó

là <btkí tự 2> ví dụ =replace(“lê thu na”,

4,3,”Hoa”): kq là lê hoa na

- Hàm Rept(<btkítự>,n): Cho kết qu là <btkítự> đ ản

ợc lặp lại n lần ví dụ = rept(“Long”,3): kq là

LongLongLong

-Hàm Search(<btkítự1>,<btkítự2>): Cho biết vị trí xuất hiện <btkítự1> trong <btkítự2> ví dụ =

search(“Tuấn”,”nguyễn Tuấn anh”): kq là 8

- Hàm Trim(<btkí tự>): cho kết qu là <btkí tự> nh ng đã ản

bỏ đi kí tự trắng ở đầu và cuối

Ví dụ:=Trim(“ Nga “) “Nga”

- HàmConcatenate(<chuỗi1>;<chuỗi2>;<chuỗi3> ):

dùng để nối các chuỗi là <chuỗi1><chuỗi2><chuỗi3> lại với nhau

Hoặc dùng toán tử & để ghép các chuỗi

ví dụ: ô C3:Lan, ô C4:Anh

=C3&“ ”&C4Lan Anh

hoặc =C3&C4LanAnh

- Hàm Value(<chuỗi số>): hàm đổi chuỗi dạng số thành

số

VD: ô C1: a1 (nằm bên trái ô)

Nếu ghi: =Right(C1,1)1 (nằm bên trái ô)

còn nếu ghi: =value(Right(C1,1))1 (nằm bên phai ô)

­­­NÕu­Order=0:­xÕp­theo­chiÓu­giam­dÇn

­­­NÕu­order=1­:­xÕp­theo­chiÒu­tang­dÇn

<điều kiện> có thể nhận các giá trị là số, ký tự hoặc biểu

thức logic nh ng ph i đặt trong dấu nháy kép và cùng ảnkiểu d liệu với DL trong <cột1>ữ thường Ví dụ

- Hàm Countif(cột chứa đk, đk): dùng đếm các

ô chứa dl tho mãn 1 điều kiện ản

ví dụ : countif(F11:F15;”>=102”): kq là 3

– Ln(n): LÊy log nepe cña sè n

– Log(n,m): Logarit c¬ sè m cña n

– Lg(n): Logarit thËp ph©n cña n

• Mod(n,m): LÊy phÇn d cña phÐp chia n cho m

vÝ dô: =mod(8,3):kq lµ 2 cung dau so chia

• SQRT(n): LÊy c n bËc 2 cña sè n ăn bản

vÝ dô =sqrt(25): kq lµ 5

11 Hàm tham chiếu và tỡm kiếm

a Hàm tỡm kiếm trong cột

=Vlookup(giáưtrịưdòưtỡm,ưbảngưthamưchiếu,ưcộtưtrảưvề,ư

kiểuưdòưtỡm)

Trongưđó:ư

mà chứa trong bảng tham chiếu

tỡm và kết quả cần trả về cho hàm bảng này luôn để trong địa chỉ tuyệt đối

- Cộtưtrảưvề:ưl cột chứa kết quà cột chứa kết qu ả đ ợc nằm trong bảng

tham chiếu, và cột này đ ợc tính là số thứ tự trên bảng tham chiếu

chính xác,tức giá trị tỡm phải có mặt trong cột đầu của bảng tham chiếu, nếu không sẽ xuất hiện lỗi Nếu là 1:thỡ cột đầu phải đ ợc sx tăng(ngầm định là 1)

b Hµm tìm kiÕm trong hµng

= hlookup(gi¸ trÞ dß tìm, bảng tham chiÕu, hµng trả vÒ, kiÓu dß tìm)

• Chó ý : nÕu bảng tham chiÕu ® îc nhËp theo chiÒu cét ta

sö dông hµm vlookup ng îc l¹i bảng ® îc nhËp theo

chiÒu hµng ta sö dông hlookup

• vÝ dô

c hàm address(<dòng>,

<cột>,<btlogic1>,<btlogic2>,<tênbảngtính>):

Dùng để tham chiếu tới một địa chỉ theo

<dòng>,<cột> là các số chỉ thứ tự của dòng và cột của ô cần tham chiếu

- Nếu <btlogic1> và <btlogic2> là true thỡ ô tham

chiếu sử dụng theo địa chỉ tuyệt đối Ví dụ

=address(3,5,true,true,”sheet1”): kq là Sheet1!$E$3

- nếu <btlogic1> là true và <btlogic2> là false thỡ

địa chỉ cần tham chiếu theo địa chỉ R1C1

- NÕu <d¹ng>=0, thì vïng cÇn tìm kh«ng cÇn s¾p xÕp tr

íc

f Hàm Index(): có hai dạng

- dạng 1: = Index(<vùng cần tỡm>,<dòng>,<cột>): kq là giá trị

của ô ứng với dòng và cột của vùng cần tỡm, trong đó<dòng> và cột : là dòng , cột trong vùng cần tỡm

- d¹ng 2: = index((<c¸c vïng cÇn tìm>),<dßng>,<cét>,<sè

thø tù vïng>)

12 Các hàm tài chính

• PV(Rate, Nper, Pmt, Fv, Type): Tớnh số tiền phải bỏ ra để

đầu tư vào một việc nào đú

– Rate : lói suất trong một khoảng thời gian

– Nper : Tổng số cỏc khoảng thời gian đầu tư

– Pmt : Số tiền lói (hoặc phải trả) trong một khoảng thời gian

– Fv : Số tiền cú được trong tương lai sau tổng số khoảng thời gian Nper

– Type : Giỏ trị logic, xỏc định khoản thanh toỏn xuất hiện đầu kỳ (=1)

hay cuối kỳ (= 0 hoặc bỏ trống)

Ví dụ: Bài toán nh sau:

Tính tổng số tiền t ơng đ ơng bạn phải tr lúc đầu kỳ (thay vì tr góp), ản ản

để mua tr góp chiếc xe với khoản chi th ờng kỳ là $220 mỗi tháng và ản trong 4 năm (48 tháng) với lãi suất 9%?

- FV(Rate, Nper, Pmt, PV, Type): Tính số tiền có được sau thời

gian Nper, với lãi suất Rate (tính theo năm), khi đầu tư một số tiền Pmt

– Rate : Lãi suất trong một khoảng thời gian

– Nper : Tổng số các khoảng thời gian đầu tư

– Pmt : Số tiền lãi (hoặc phải trả) trong một khoảng thời gian

– Pv : Số tiền phải bỏ ra để đầu tư vào việc nào đó.

– Type : Giá trị logic, xác định khoản thanh toán xuất hiện đầu kỳ

(=1) hay cuối kỳ (= 0 hoặc bỏ trống)

• VÝ dô: B¹n ®Çu t lóc ®Çu lµ: $2000(Pv) Vµ chi thªm mçi th¸ng lµ: $100

trong 5 n¨m (60 th¸ng) Víi l·i suÊt 8% mét n¨m

TÝnh gi¸ trÞ ® îc h ëng ë cuèi kú?

 Hµm­PMT(Rate, Nper, PV,FV, Type)­:­T×m sè tiÒn ph¶i tr trong mçi th¸ng khi vay ng©n hµng (Tr v ản ản ề kho n chi tr thản ản ường kỳ cho một niªn khoản)ng k cho m t niªn kho n)ỳ cho một niªn khoản) ột niªn khoản) ản

 HàmưNPER(Rate, Pmt, PV,FV, Type)ư:Tìm số tháng

phải tr một khoản tiền cố định khi vay ngân hàng (tr về ản ản

số kỳ khoản của một đầu t )

1 Giới thiệu về cơ sở d liệu trên b ng tính ữ thường Ví dụ ản

- Cơ sở d liệu(database) là tập hợp các ữ thường Ví dụ

thông tin về một vấn đề cần qu n lý đ ợc ản

tổ chức có cấu trúc theo dòng và cột

*ư ểưthựcưhiệnưcácưthaoưtácưtrênưmộtưcơưsởưd ư Để thực hiện các thao tác trên một cơ sở dữ ữ hoa , thường

liệu,ưcầnưtạoưraưbaưvùngưkhácưnhau

a Vùng d liệu(database range) ữ thường Ví dụ

ây là vùng đ a ra d liệu cần thiết và ít nhất

Đây là vùng đưa ra dữ liệu cần thiết và ít nhất ữ thường Ví dụ

ph i có hai dòng: ản

+ Dòng đầu tiên chứa tiêu đề cột gọi là tên

vùng hay tên tr ờng(field name)

+ Các dòng còn lại chứa các d liệu Mỗi ữ thường Ví dụ

dòng đ ợc gọi là một b n ghi(record) ản

b Vùng tiêu chuẩn(criteria range)

Chứa điều kiện để tỡm kiếm, xoá , rút trích

- Vùng này có ít nhất hai dòng:

Dòng đầu chứa các tiêu đề mà mỗi tiêu đề này ph i ản

trùng với tiêu đề của vùng d liệu, các dòng còn lại chứa ữ thường Ví dụtiêu chuẩn

- Vùng tiêu chuẩn có thể chứa nhiều cột

c Vùng trích d liệu(extract)ữ thường Ví dụ

Là vùng dữ liệu chứa các mẩu tin của dữ liệu

(Database) thoả mãn điều kiện vùng tiêu chuẩn

b ng ản

– New: T¹o b n ghi míi ản

– Delete: Xãa b n ghi hiÖn ản

thêi

– Restore: Phôc håi b n ghi ản

võa xãa:

– Find Prev : HiÓn thÞ b n ản

ghi tr íc b n ghi hiÖn thêi ản

– Find Next: HiÓn thÞ b n ản

ghi sau b n ghi hiÖn thêi ản

– Criteria: iÒu kiÖn điÒu kiÖn

– Close: ãng Form l¹i điÒu kiÖn

3.ưQuyưđịnhưd ưliệuưkhiưnhậpư ữ hoa , thường

– Allow: Loại d liệu ữ thường Ví dụ

– Data: Phép toán sử dụng

• Đặt thuộc tớnh hiện thị

thụng bỏo trong quỏ trỡnh

nhập liệu trong Tab Input

Mesage

• Đưa ra cỏc thụng bỏo

nhập sai dữ liệu trong

Tab Error Alert

• Đây là vùng đưa ra dữ liệu cần thiết và ít nhất ánh dấu vùng nguồn Chọn Edit/Copy

• Đây là vùng đưa ra dữ liệu cần thiết và ít nhất a đến vùng đích Chọn Edit/Paste

3.ư ặtưlọcưd ưliệu Để thực hiện các thao tác trên một cơ sở dữ ữ hoa , thường

a Đây là vùng đưa ra dữ liệu cần thiết và ít nhất ặt lọc tự động

ánh dấu vùng d liệu cần đặt lọc

Đây là vùng đưa ra dữ liệu cần thiết và ít nhất ữ thường Ví dụ

Chọn menu Data/Filter/AutoFilter

b ặt lọc theo điều kiệnĐây là vùng đưa ra dữ liệu cần thiết và ít nhất

ánh dấu vùng d liệu cần đặt lọc

Đây là vùng đưa ra dữ liệu cần thiết và ít nhất ữ thường Ví dụ

Chọn menu Data/Filter/Advanced Filter

Vùng danh sách lọc

Vùng điều kịên lọc

• Nếu có từ 2 điều kiện lọc trở lên

• Nếu các điều kiện đồng thời x y ra: Viết trên một dòng (là phép And)ản

• Nếu các điều kiện hoặc x y ra: Viết trên nhiều dòng (là phép Or)ản – Ví dụ:

• TH1 : Khu vuc=1 And DHoa=7

• TH2: DToan>=7 Or DLi>=8 Or DHoa>=8

4 Ph ơng pháp trích rút

B1: Xác định vùng dữ liệu(Database) và tạo tr ớc vùng tiêu chuẩn(Criteria) và vùng rút trích(Extract)

B2:Đ a trỏ chuột vào ô bất kỳ trong vùng dữ liệu Chon Data\Filter\Advanced filter Kiểm chọn vào Copy to another location

B3:Nhập địa chỉ vùng dữ liệu cần trích vào khung List range Nhập vùng điều kiện trong khung Criteria

range Nhập địa chỉ ô đầu của vung rút trích trong khung Copy to

B4: Click OK

DSum(V, C, K) Đ©y lµ vïng ®­a ra dữ liÖu cÇn thiÕt vµ Ýt nhÊt Tính t ng cổng c ó i u ki nđiÒu kiÖn ề ện

DCountA(V,C, K) Đ©y lµ vïng ®­a ra dữ liÖu cÇn thiÕt vµ Ýt nhÊt Đ©y lµ vïng ®­a ra dữ liÖu cÇn thiÕt vµ Ýt nhÊt ếm cm các b n ghi khản ác r ng cỗng c ó i u ki nđiÒu kiÖn ề ệnDCount(V,C, K) Đ©y lµ vïng ®­a ra dữ liÖu cÇn thiÕt vµ Ýt nhÊt Đ©y lµ vïng ®­a ra dữ liÖu cÇn thiÕt vµ Ýt nhÊt ếm cm các b n ghi lản à s cố c ó i u ki nđiÒu kiÖn ề ện

DMax(V,C, K) Đ©y lµ vïng ®­a ra dữ liÖu cÇn thiÕt vµ Ýt nhÊt Tìm Max có i u ki nđiÒu kiÖn ề ện

DMin(V,C, K) Đ©y lµ vïng ®­a ra dữ liÖu cÇn thiÕt vµ Ýt nhÊt Tìm Min có i u ki nđiÒu kiÖn ề ện

DAverage(V,C, K) Đ©y lµ vïng ®­a ra dữ liÖu cÇn thiÕt vµ Ýt nhÊt Tính trung bình có i u ki nđiÒu kiÖn ề ện