Phần 5: chương trình microsoft excel

a: Lý thuyết

 Bài 1:  Giới thiệu chương trình bảng tính excel.

I. Cách khởi động chương trình và thoát khỏi chương trình.

 1. Cách khởi động

Có ba cách để khởi động chương trình.

- Cách 1: Nhấn vào biểu tượng    nằm ở góc phải màn hình.

- Cách 2: Vào Start\ chọn Program\ chọn Micrsoft Excel nhấp chuột trái.

- Cách 3: Trên bàn phím ta nhấn tổ hợp phím Ctrl + Esc sử dụng các phím mũi tên di chuyển đến Program\ chọn Micrsoft Excel nhấn phím Enter.

2. Cách thoát khỏi chương trình.

Có 3 cách thoát khỏi chương trình.

- Cách 1: Nhấn chuột trái vào biểu tượng        nằm ở góc phải màn hình.

- Cách 2: Vào File\ Nhấp chuột trái chọn Exit.

- Cách 3: Trên bàn phím ta nhấn tổ hợp phím Alt + F4.

II. Màn hình excel:

Trong excel 1File được gọi là 1 book

1Book = 256 sheet

1Sheet: gồm 256 cột và 65536 hàng

1.Màn hình excel (Giao diện của bảng tính excel)

a.Thanh tiêu đề: (Title bar)

b.Thanh thực đơn : (Menu bar)

c. Thanh công cụ chuẩn: ( Standard)

d. Thanh định dạng: (Formatting)

e. Thanh công thức:

- Được chia làm hai cửa sổ:

Tương tự như bên Word

+ Cửa sổ trái: ghi địa chỉ ô con trỏ đang đứng.

+ Cửa sổ phải: ghi nội dung ô con trỏ đang đứng.

+ Giữa hai cửa sổ có ba nút

   : Huỷ công thức

  : Nhận công thức

       : Sửa công thức

2. Bảng tính Sheets:

- 1 Sheet =  256 cột và 65536 hàng.

- Các cột thường được ký hiệu bằng các chữ in A, B … Z, AA,AB     IV

- Các hàng được ký hiệu từ 1 đến 65536 dòng.

3. Các khái niệm cơ bản:

a. Khái niệm:

- Khái niệm về ô (Cell): Ô giao giữa cột và hàng

+ Địa  chỉ:  Mỗi một ô được  gọi có một địa  chỉ  nhất định,  địa  chỉ  của ô

được gọi theo tên: cột hàng mà ô đó chiếm giữ.

VD: C4, D5...

- Khái niệm về vùng: Vùng là tập hợp các ô liên tiếp được  sắp xếp với nhau

theo hình chữ nhật.

+ Địa chỉ vùng: Được gọi theo địa chỉ ô đầu và địa chỉ ô cuối

VD: D4:H11 b. Các loại địa chỉ:

- Địa chỉ tương đối: Là địa chỉ bị thay đổi trong quá trình sao chép copy

+ Cách viết: Viết theo ký hiệu cột và hàng

VD: B5:C5

- Địa chỉ tuyệt đối  là địa chỉ không bị thay đổi trong quá trình sao chép copy

+ Cách viết: Có 2 cách       

Nhập tuần tự: $cột $hàng.

Nhập địa chỉ tương đối sau đó nhấn phím F4

VD:$C$4

- Địa chỉ hỗn hợp: Có 2 loại.

+ Loại 1: Địa chỉ tương đối cột, tuyệt đối hàng

VD: C$4

+ Loại 2: Địa chỉ tuyệt đối cột, tương đối hàng

VD: $C5

c. Các kiểu dữ liệu trong Excel:

Trong excel có ba kiểu dữ liệu:

- Dữ liệu kiểu số

- Dữ liệu kiểu chữ

- Dữ liệu kiểu công thức.

Khi nhập dữ liệu máy tự động nhận dạng dữ liệu:

+ Nếu dữ liệu kiểu số luôn lệch bên phải.

+ Nếu dữ liệu kiểu chữ luôn lệch bên trái.

+ Nếu dữ liệu kiểu công thức khi nhập phải đánh dấu bằng.

 Kiểu ngày tháng trong excel được coi như dữ liệu kiểu số nên khi nhập phải lệch

phải.

- Máy tính có thể làm việc theo 2 chế độ: Anh - Mỹ, Pháp - Việt

- Nếu là chế độ Anh: Ta nhập Tháng... ngày... năm

- Nếu là chế độ Pháp : Ta nhập Ngày... tháng... năm

- Trước khi làm việc ta phải kiểm tra kiểu ngày tháng:

Có 2 cách để kiểm tra:

+ Cách 1: Nhập kiểu ngày tháng >12 theo 2 hệ Anh, Pháp kiểu  nào lệch phải ta  làm việc theo chế độ đó.

+ Cách 2:  Kiểm tra và sửa đổi trong hệ thống:

Vào Start/ Setting/ Control panel/ chọn Regionnal setting      xuất hiện bảng Regionnal setting.

- Date: Cho phép và sử đổi kiểu ngày tháng

- Number: Cho phép và sửa đổi kiểu số.

* Các toán tử:

- Dấu các phép toán.

Cộng (+)                                   Chia (/)

Trừ (-)                                       Luỹ thừa (^) Nhân (*)                                   Dấu bằng ( = )

- Các  toán tử về so sánh:

Dấu lớn hơn ( >)    Dấu lớn hơn hoặc bằng (=>, >=) Dấu nhỏ hơn ( <)  Dấu nhỏ hơn hoặc bằng (= <,<=) Dấu bằng ( = ) Dấu khác (><, <>)

- Toán tử lôgic: And, True, OR, False, Not.

- Toản tử ghép xâu: &. VD: Hà&Nội  HàNội.

III. Các thao tác

1. Đối với Sheet.

a. Quy định Font chữ, cỡ chữ, số Sheet ra ngoài màn hình (Mặc định khi bật máy)

- Vào Tool / Options, chọn thẻ bài Gerneral:

+Tại Sheet in new work book: Quy định số Sheet ra ngoài màn hình.

+ Tại Standard font: Quy định Font chữ ra ngoài màn hình.

+ Tại size: Quy định cỡ chữ ra ngoài màn hình.

Sau đó đóng chương trình lại và khởi động lại chương trình excel

b. Chèn, xoá, đổi tên sheet.

* C1: Chọn Sheet bất kỳ và nháy chuột phải.

- Insert: Chèn thêm Sheet

- Delete: Xoá Sheet

- Remane: Đổi tên Sheet

- Move or Copy: Đổi chỗ (hay di chuyển sheet): Đưa con trỏ vào sheet cần di

chuyển nhấn giữ rê chuột trái tới vị trí cần thiết  thả tay chuột.

- Select all sheet: Chọn toàn bộ các Sheet có trong file hiện hành.

* C2: Chọn Sheet/ Format/Sheet.

2. Nhập dữ liệu.

- Nhập bình thường đối với các trường hợp dữ liệu tràn ngoài cột ta để nguyên

và  nhập dữ liệu ở cột tiếp theo.

- Cách sửa dữ liệu có 3 cách:

+ Cách 1: Chọn ô dữ liệu muốn sửa, đưa trỏ chuột lên cửa sổ thanh công

thức nháy chuột trái.

+ Cách 2: Chọn ô dữ liệu muốn sửa, nháy đúp chuột trái.

+ Cách 3: Chọn ô dữ liệu muốn sửa và nhấn phím F2 trên bàn phím.

3. Cách đánh dấu bôi đen.

- Đưa trỏ chuột vào đến đầu cột hoặc hàng nhấp chuột trái

- Bôi đen toàn bộ Sheet đưa trỏ chuột vào ô trắng nằm phía trên cùng góc trái

màn hình nhấp chuột trái hoặc bấm tổ hợp phím Ctrl +A.

- Trong Excel ta có thể bôi đen được nhiều vùng: Cách làm như sau:

+ Chọn xong vùng đầu, bấm phím Ctrl và chọn các vùng tiếp theo.

4. Một số thao tác đối với ô, cột, hàng.

a. Cách chèn số ô, cột, hàng.

- Đặt con trỏ hoặc bôi đen số ô, cột, hàng cần chèn/ vào Insert.

+ Nếu chèn hàng chọn Insert Rows.

+ Nếu chèn cột chọn Insert Columns.

- Hoặc Vào Insert chọn Cell, xuất hiện bảng Insert cell

+ Shift Cell Right: Chèn số ô vào vị trí được bôi đen số ô bôi đen ban đầu

được đẩy sang phải.

+ Shift Cell Down: Chèn số ô vào vị trí được bôi đen số ô bôi đen ban đầu

được đẩy xuống dưới.

+ Entrire Row: Chèn toàn bộ hàng.

+ Entrire Columns: Chèn toàn bộ cột. b. Cách xoá số ô, cột, hàng.

- Bôi đen (chọn) số ô, cột, hàng, cần xoá vào Edit chọn Delete xuất hiện bảng Delete.

+ Shift cells left: Xoá số ô được bôi đen số ô còn lại được đẩy sang bên trái.

+ Shift ceels up: Xoá số ô ở vị trí được bôi đen số ô phía dưới được đẩy lên trên.

+ Entrire Row: Xóa số hàng được bôi đen.

+ Entrire Column: Xoá số cột được bôi đen

c. Cách hiệu chỉnh cột, hàng.

- Vào Format nếu hiệu chỉnh hàng chọn Rows, nếu hiệu chỉnh cột chọn Column.

- Xuất hiện bảng:

+ Width: Độ rộng cột (hàng)

+ Auto Fit Selection: Tự động co d•n cột (hàng) cho vừa với dữ liệu.

+ Hide: ẩn cột (hàng)

+ Unhide: Huỷ ẩn cột (hàng)

+ Standard Width: Quy định khoảng cách của các cột bằng nhau.

5. Cách đánh số thứ tự tự động.

* Có 3 cách đánh số thứ tự tự động.

- Cách 1: Nhập 2 số đầu d•y, chọn (bôi đen) 2 số, sau đó đưa trỏ chuột vào góc phải của ô thứ hai khi trỏ chuột tạo thành dấu cộng nhỏ nhấn giữ rê chuột trái đến vị trí cần thiết thả tay chuột.

* Chú ý với cách đánh số thứ tự này ta có thể nhập được d•y số thứ tứ toàn là số chẵn

hoặc toàn là số lẻ) (theo cấp số cộng).

- Cách 2: Nhập 1 số đầu d•y đưa trỏ chuột vào góc phải của ô đó khi trỏ chuột tạo

thành dấu cộng nhỏ nhấn giữ rê chuột trái đồng thời trên bàn phím ta nhấn giữ phím Ctrl

đến vị trí cần thiết thả tay chuột trước thả tay phím Ctrl sau (tuần tự).

- Cách  3: Nhập  một số đầu  d•y sau đó  vào Edit/ Fill/ Series xuất  hiện  bảng

Sries.

Tại Series in:

+ Row: Nhập số thứ tự theo hàng.

+ Columns: Nhập số thứ tự theo cột.

 Tại Type:

+ Linear: Đánh số thứ tự theo cấp số cộng (đường thẳng)

+ Growth: Đánh số thứ tự theo cấp số nhân

+ Date: Đánh số thứ tự theo thời gian.

+ Step value: Gán bước nhảy (Mặc định là 1).

+ Tại Stop value: Nhập số kết thúc của d•y số thứ tự.

6. Cách sao chép copy

-Tương tự như Word. Có thêm cách sao chép copy công thức. Cách sao chép như sau.

- Để sao chép copy công thức ta bôi đen vùng dữ liệu cần copy sau đó đưa trỏ

chuột tới nơi cần copy tới vào Edit chọn Paste Special. Xuất hiện bảng Paste Special.

+ All: Dán toàn bộ                      + Comment: Dán chú thích

+ Formulas: Dán công thức        + Validation: Dán logic

+ Values: Dán giá trị.                  + All except borders: Dán toàn bộ

+ Formats: Copy định dạng.           trừ đường lưới.

Opertion: (toán tử)

+ None: Không copy

+ Add: Copy cộng thêm giá trị (Đích + Nguồn).

+ Subtract: Copy trừ bớt giá trị (Đích – Nguồn).

+ Multiply: Copy nhân thêm giá trị (Đích  nguồn)

+ Divide: Copy chia giá trị (Đích / nguồn).

+ Transpose: Copy chuyển hướng (chuyển hướng dọc thành ngang, ngang

thanh dọc)

+ Paste link: Dán liên kết.

+ Skip blanks: Copy Bỏ qua ô trắng.

Bài 2:  Định dạng bảng tính excel

I. Chọn vùng dữ liệu cần định dạng.

Bấm tổ hợp phím Ctrl +1 hoặc vào Format/Cells. Xuất hiện bảng Cells.

1. Thẻ Number: Các kiểu dữ liệu.

- General: Kiểu máy tự nhận.

- Number: Dữ liệu kiểu số.

+  Decimal places            : Số các số phần thập phân.

+ Use 1000 Separator(,)  : Thêm dấu phân cách hàng nghìn.

- Currency                    : Kiểu tiền tệ.

- Accouting                      : Kiểu số trong kế toán.

- Date                               : Kiểu ngày tháng

- Time                              : Kiểu thời gian

- Percentage                   : Kiểu phần trăm.

- Fraction                      : Đổi số thập phân ra phân số.

- Scientific                       : Dạng số mũ.

- Text                               : Kiểu chuỗi ký tự.

- Special                           : Kiểu quy định đặc biệt

+ Zip code                        : M• quốc gia.

+ Phone number              : M• phone.

+ Secial Security number : Các m• số đặc biệt khác.

- Custom: Kiểu tự quy định.

VD: Để đánh số thứ tự tự động nằm trong ngoặc ( ) ta làm như sau:

+ Đánh 1 hoặc 2 số đầu d•y.

+ Vào Format/Cells. Xuất hiện bảng Number cell.

+ Chọn thẻ Number/Custom.

+ Tại cửa sổ Type xoá chữ General, thay bằng (#)/OK.

2. Thẻ Aligement: Căn chỉnh dữ liệu.

- Horizontal: Căn chỉnh dữ liệu theo cột.

+  General : Kiểu máy tự nhận.

+  Left (Indent) : Căn dữ liệu thẳng lề bên trái của cột.

+ Center : Căn dữ liệu vào giữa côt.

+ Right : Căn dữ liệu thẳng lề bên phải cột.

- Verical: Canh chỉnh dữ liệu theo hàng .

+  Top : Canh dữ liệu sát lề trên của hàng .

+ Center : Canh dữ liệu vào giữa hàng .

+ Bottom : Canh dữ liệu sát lề bên dưới của hàng.

+ Justify : Canh dữ liệu theo hai bên cột .

- Text control  : Điều khiển chữ.

+ Wrap text : Chữ nằm trọn trong ô (khi đó độ rộng cột không thay đổi mà

chỉ thay đổi độ  cao của hàng ).

+ Shrink to fit    : Dữ liệu tự động co lại cho vừa với ô.

+ Merge Cell    : Liên kết ô ( tương đương với      )

- Orientation   : Chọn hướng để chữ (nghiêng bao nhiêu độ).

- Indent             : Thụt đầu dòng.

3. Thẻ Font: Định dạng font chữ.

- Chọn font chữ, cỡ chữ, kiểu chữ.

- Taị Effects:

+ Strikethrought: Tạo đường kẻ gạch ngang qua chữ.

+ Supercript      : Chỉ số trên.

+ Subscript        : Chỉ số dưới.

4. Border: Đường kẻ trang trí bảng.

- Line: Chọn các kiểu đường kẻ.

- Color: Màu đường kẻ.

- Presets: Thiết lập.

+ None: Không có đường kẻ.

+ Outline: Đường bao quanh.

+ Inside: Đường bên trong.

5. Patterns: Màu nền.

- Color: Màu nền chính.

- Pattern: Đối tượng che màu nền.

Bài 3: tính toán đơn giản và cách gọi hàm trong excel

I - Tính toán đơn giản trong excel:

Sử dụng địa chỉ ô và các toán tử:

VD:

          A       B            C

1 Đơn giá Số lượng  Thành tiền

2 20.000   12            =A2*B2

II - Dạng tổng quát của các hàm trong excel:

= Tên hàm (danh sách đối số)

Đối số phải được đặt trong dấu ngoặc đơn, giữa các đối số phải có dấu phẩy(,)

hoặc dấu chấm phẩy (;)

Trong đó danh sách đối số có thể nhận các giá trị sau:

- Các giá trị số.

- Các giá trị chữ (phải để trong ngoặc kép)

- Danh sách địa chỉ các ô:

+ Các ô liên tiếp (vùng) ta khai báo (địa chỉ ô đầu vùng: địa chỉ ô cuối vùng).

VD: (A12:B14) ( từ ô A12 đến ôB14).

+ Nếu địa chỉ các ô không liên tiếp theo một vùng, ta dùng dấu phẩy (,) để ngăn cách.

VD: (A12, A14,B15)

- Tên các vùng.

- Các công thức.

- Các hàm.

III - Cách gọi  hàm mẫu:

Có 2 cách khai báo hàm:

+ Gõ tên hàm từ bàn phím rồi khai báo đối số

+ Gọi hàm mẫu.

- Đặt con trỏ vào ô cần đặt hàm mẫu

- Bấm biểu tượng (fx) trên thanh công cụ hoặt vào Insert/ Function.

- Chọn nhóm hàm trong khung Function Category.

- Chọn hàm cần dùng trong khung Function Name.

- Nhấn chuột vào nút Next hoặc OK . Xuất hiện hộp thoại.

- Đặt các đối số cần thiết vào các khung của hộp thoại

- ấn Finish hoặc Enter để kết thúc.

Bài 4: Một số hàm cơ bản.

1. Hàm SUM: Tính tổng của các đối số number1, number2,...

= SUM(number1, number2,...)

2. Hàm average: Tính trung bình cộng của các đối số number1, number2,...

= average( numbe r1, numbe r2,...)

3. Hàm max: Tìm giá trị lớn nhất trong dãy các đối số number1, number2,...

= max(numbe r1, numb e r2 ,...)

4. Hàm min: Tìm giá trị nhỏ nhất trong dãy các đối số number1, number2,...

= min(number1, number2,...)

5. Hàm rank: Xếp hạng số number trong dãy ref theo kiểu xếp hạng order.

= rank(number,ref,order)

- Nếu order = 0: Số lớn nhất xếp thứ nhất

- Nếu order =1: Số nhỏ nhất xếp thứ nhất

6. Hàm round: Làm tròn số number đến vị trí thứ n của phần thập phân

= round(number,n)

- Nếu n >0: làm tròn với n số thập phân (sau dấu chấm thập phân).

- Nếu n<0: làm tròn về bên trái dấu thập phân.

- Nếu n=0: Làm tròn đến phần nguyên (đơn vị)

7. Hàm ABS: Tính giá trị tuyệt đối của đối số number

= ABS(number)

8. Hàm SQRT: Tính căn bậc hai của number

=SQRT(number)

9. Hàm MOD: Tìm số dư của phép chia number cho số chia divisor.

=MOD(number,divisor)

10. Hàm INT: Tính phần nguyên của number

=INT(number)

11. Hàm COUNT:  Đếm  các ô DL kiểu  số  trong danh sách  đối  số  value1, value2,..

=COUNT (value1, value2,..)

12. Hàm  counta:  Đếm  các ô có  chứa dữ liệu  trong danh  sách  đối  số value1, value2,..

=COUNTA (value1, value2,..)

I- Hàm  điều kiện.

Bài 5: nhóm hàm logic

=IF(logical_test,value_if_true,  value_if_false)

Công dụng:   Cho ra giá trị value_if_true nếu logical_test là TRUE và cho ra giá trị

value_if_false nếu logical_test là FALSE.

Giải thích:   - logical_test: Biêủ thức logic.

- value_if_true: Giá trị đúng.

- value_if_false: Giá trị sai.

Chú ý: Trong trường hợp có nhiều điều kiện ta phải sử dụng các hàm IF lồng nhau

(không quá 8 vòng)

IF (logical_test,value_if_true, IF (logical_test,value_if_true,.... (n)

Trong đó (n) là số lần đóng ngoặc tương ứng với số hàm IF được sử dụng

II- Hàm  liên kết các điều kiện.

1. Hàm AND:

= AND (logical1,logical2,...)

Công dụng:  Cho ra giá trị  đúng  khi tất cả các điều  kiện  logical1,logical2,...đều  là

đúng và cho giá trị sai khi một trong các điêù kiện là sai.

2. Hàm (OR):

= OR(logical1,logical2,...)

Công dụng:  Cho ra giá trị đúng khi một trong các điều kiện logical1,logical2,... đúng và cho giá trị sai khi tất cả các điêù kiện là sai.

3. Hàm NOT:

=NOT(logical)

Công dụng:  Cho ra giá trị đúng  khi điều kiện logical sai và ngược lại.

III – Một số hàm điều kiện khác.

1. Hàm Countif: Đếm các ô trên vùngRange thoả m•n tiêu chuẩn Criteria

= COUNT IF(range ,c rite ri a)

2. Hàm SUMIF:  Cộng giá trị trong vùng (Sum_range) có các ô tương ứng (cùng một

dòng) trong vùng (Range) thoả m•n tiêu chuẩn (Criteria).

=SUMIF(range,criteria,sum_range)

- Range:Vùng chứa các ô cần ước lượng

- Criteria:Tiêu chuẩn đặt ra

- Sum_range: Vùng chứa các ô được cộng. Các ô trong vùng sum_range chỉ được cộng nếu các ô tương ứng trong vùng range phù hợp với tiêu chuẩn criteria. Nếu  bỏ qua sum_range thì  chính  các ô trong vùng  range

được cộng.

Bài 6: hàm chuỗi ký tự, hàm thời gian & ngày tháng

I - Nhóm hàm chuỗi ký tự.

1. Hàm LEFT:

= LEFT(text,num_char)

Công dụng: Trích ra đủ num_char ký tự  trong d•y text  tính từ trái sang.

2. Hàm RIGHT

= RIGHT(text,num_char)

Công dụng:  Trích ra đủ num_char ký tự  trong d•y text  tính từ phải sang.

3.Hàm MID

= MID(text,start_num,num_char)

Công dụng:  Trích ra từ vị trí start_num, đủ num_char ký tự trong d•y text.

4. Hàm LEN: Đếm độ dài của chuỗi text

= LEN(text)

5. Hàm LOWER: Chuyển chuỗi text thành chuỗi chữ thường

=LOWER(text)

6. Hàm Upper: Chuyển chuỗi text thành chuỗi chữ hoa

=UPPER(text)

7. Hàm PROPER: Chuyển các ký tự đầu từ của chuỗi text thành chữ hoa

=PROPER(text)

8. HàmTRIM:  Cắt  bỏ các ký tự trắng  thừa ở đầu  và cuối chuỗi text (đưa  về

chuỗi chuẩn)

= TRIM(text)

II. Hàm  thời gian.

1. Hàm SECOND: Cho ra số giây của đối số serial_number kiểu thời gian

=Second(serial_number)

2. Hàm MINUTE: Cho ra số phút của đối số serial_number kiểu thời gian

= Minute(se ri al_ numbe r)

3. Hàm HOUR: Cho ra số giờ của đối số  serial_number  kiểu thời gian

= Hour(serial_number)

4. Hàm NOW: Cho ra thời gian hiện tại của hệ thống.

= NOW()

III - Hàm  ngày tháng.

1. Hàm DAY: Cho ra ngày của tham số serial_number kiểu ngày tháng

= Day(serial_number)

2. Hàm MONTH: Cho ra tháng của tham số serial_number kiểu ngày tháng

= MONTH(serial_number)

3. Hàm YEAR: Cho ra năm của tham số serial_number kiểu ngày tháng

= yEAR (serial_number)

4. Hàm TODAY: Cho ra ngày tháng hiện tại

= TODAY()

5. Hàm DAYS360: Tính hiệu số ngày của end_date trừ đi star_date.

= Days360(star_date, end_date)

Bài 7: nhóm hàm tìm kiếm

1. Hàm tìm kiếm theo cột (VLOOKUP)

=VLOOKUP(lookup_value,table_array, col_index_num, range_lookup)

Giải thích tham số:

- Lookup_value: Giá trị tìm kiếm (trong bảng dữ liệu)

- Table_array: Bảng tham chiếu (luôn để ở dạng địa chỉ tuyệt đối)

- Col_index_num: Thứ tự cột trả về trong bảng tham chiếu được tính từ trái qua phải.

- Range_lookup: Chuẩn tìm kiếm (có 2 dạng):

*  Chuẩn 0 (False): Tìm kiếm chính xác.

 0 (0) False

# 0 (1) True

- Nếu giá trị tìm kiếm được tìm thấy trong cột đầu tiên của bảng tham chiếu thì máy sẽ in kết quả của cột trả về tương ứng.

- Nếu không tìm thấy máy đưa ra thông báo #N/A.

* Chuẩn #0 (=1, True): Tìm gần đúng.

Đ/k: Cột đầu tiên trong bảng tham chiếu phải được sắp xếp theo thứ tự tăng dần.

- Nếu giá trị tìm kiếm  < giá trị nhỏ nhất của cột đầu tiên trong bảng tham chiếu (ô đầu)

thì máy coi như không tìm thấy và đưa ra thông báo #N/A.

- Nếu giá trị tìm kiếm > = giá trị lớn nhất trong cột đầu tiên của bảng tham chiếu (ô cuối) thì máy coi như  tìm thấy ô giá trị đó và in kết quả của cột trả về tương ứng.

2. Hàm tìm kiếm theo dòng (HLOOKUP)

= HLOOKUP(lookup_value,table_array, row_index_num,range_lookup)

Cách khai báo giống trong hàm VlOOKUP

Bài 8: Các hàm cơ sở dữ liệu

I. Khái niệm

1. CSDL là tập hợp thông tin, dữ liệu được tổ chức theo cấu trúc dòng và cột để có thể liệt kê, truy tìm, xóa, rút trích những dòng dữ liệu thoả m•n một tiêu chuẩn nào đó nhanh chóng. Để thực hiện các thao tác này cần phải tạo ra các vùng CSDL, Criteria và Extract.

2. Vùng CSDL là vùng gồm ít nhất hai dòng. Dòng đầu tiên chứa các tiêu đề cột gọi là tên vùng tin (Field name) của CSDL. Tên các vùng tin phải là dữ liệu kiểu xâu ký tự và  không được trùng lặp. Các dòng còn lại chứa dữ liệu, mỗi dòng gọi là một mẫu tin CSDL(Record).

3. Vùng Criteria là vùng tiêu chuẩn chứa điều kiện tìm kiếm, xóa, rút trích... Vùng này gồm

ít nhất 2 dòng. Dòng đầu chứa tiêu đề, các dòng còn lại chứa điều kiện.

4. Vùng Extract là vùng dữ liệu trích ra, chứa các mẫu tin của vùng CSDL thoả mãn điều kiện của vùng tiêu chuẩn. Vùng Extract cũng có dòng đầu tiên chứa các tiêu đề  muốn rút trích.  Vùng này chỉ được dùng trong trường hợp  thực hiện thao tác rút trích còn các thao tác tìm kiếm, xoá …. Thì không cần dùng.

II - Các dạng vùng tiêu chuẩn:

1. Tiêu chuẩn số (ô điều kiện chứa dữ liệu kiểu số)

VD: Tiêu chuẩn Số con bằng 2

Số con

2

2. Tiêu chuẩn chuỗi ký tự (ô điều kiện chứa dữ liệu kiểu chuỗi ký tự )

VD: Tiêu chuẩn Chức vụ là “GĐ”

Chức vụ

GĐ

Trong đó điều kiện có thể chứa ký tự đại diện ? (thay cho một ký tự) hoặc * (thay cho một nhóm ký tự).

VD: Tiêu chuẩn Tên bắt đầu bằng “H” Tên H*

3. Tiêu chuẩn so sánh

ô điều kiện chứa toán tử so sánh kèm với giá trị so sánh. Các toán tử so sánh gồm: bằng (=), lớn hơn (>), nhỏ hơn (<), lớn hơn hoặc bằng (>=), nhỏ hơn hoặc bằng (<=), khác (<>).

VD. Tiêu chuẩn Số con lớn hơn 2

4. Tiêu chuẩn công thức

Số con >2 : ô điều kiện có kiểu công thức. Khi sử dụng tiêu chuẩn này cần lưu ý:

- ô điều kiện của vùng tiêu chuẩn phải là một tiêu đề khác với tất cả các tiêu đề

của vùng CSDL.

- Trong ô điều kiện phải lấy địa chỉ của ô trong mẫu tin đầu tiên để so sánh.

VD: Tiêu chuẩn ký tự đầu tiên của tên khác “H”

Công thức trong ô điều kiện là: =LEFT(C2,1)<>”H”

Tên-H TRUE

Xuất hiện TRUE/FALSE hay 1/0

5. Tiêu chuẩn liên kết:

Có thể tìm kiếm, xoá, hay rút trích các mẫu tin trong vùng CSDL bằng cách giao (AND) hay hội (OR) của nhiều điều kiện khác nhau. Nếu các ô điều kiện khác cột thì có tính chất giao. Nếu các ô điều kiện khác dòng thì có tính chất hội.

VD: Vùng tiêu chuẩn thể hiện điều kiện: Số con bằng 1và Chức vụ là “NV” hay

Số con lớn hơn 2 và Chức vụ là GĐ.

Số con Chức vụ

1            NV

>2         GĐ

III- Các hàm CSDL

1. Hàm DSUM:Tính  tổng  trên  một cột (field) của vùng  CSDL(database) thoả mãn điều kiện ghi trong miền tiêu chuẩn (criteria)

= DSUM(database,field,criteria)

2.  Hàm  DAVERAGE:   Tính   trung  bình   cộng  trên   một  cột  (field)  của  vùng CSDL(database) thoả mãn điều kiện ghi trong miền tiêu chuẩn (criteria)

= DAVERAGE(database,field,criteria)

3. Hàm DMAX:Tính giá trị  lớn nhất  trên  một cột (field) của vùng CSDL(database)

thoả m•n điều kiện ghi trong miền tiêu chuẩn (criteria)

= DMAX(database,field,criteria)

4. Hàm DMIN:Tính giá trị nhỏ nhất trên  một cột (field) của vùng CSDL (database)

thoả m•n điều kiện ghi trong miền tiêu chuẩn (criteria)

= DMIN(database,field,criteria)

5. Hàm DCOUNT: Đếm số bản ghi chứa dữ liệu kiểu số của vùng CSDL(database) trên cột

(field) thoả m•n điều kiện ghi trong miền tiêu chuẩn (criteria)

= DCOUNT(database,field,criteria)

6. Hàm DCOUNTA:  Đếm số bản ghi không rỗng của vùng CSDL(database) trên cột

(field) thoả m•n điều kiện ghi trong miền tiêu chuẩn (criteria)

= DCOUNTA(database,field,criteria)

Bài 9:  Sắp xếp và tổng hợp dữ liệu

1. Sort: Sắp xếp dữ liệu. Trong đó:

a. Sort by: Lựa chọn tên cột chứa DL cần sxếp chính. Sau đó chọn dạng:

- Ascending: Tăng dần

- Descending: Giảm dần

b. Then by: Lựa chọn tên cột chứa DL sắp xếp lượt 2 hoặc 3

- Ascending: Tăng dần

- Descending: Giảm dần

Chú ý: Trường hợp không xuất hiện tiêu đề cột của bảng ta phải chọn Header row .

2. Subtotals: Thống kê DL theo nhóm

B1. Sắp xếp cột chứa dữ liệu cần thống kê theo nhóm.

B2. Vào menu Data/ Subtotals sau đó khai báo các tham số để thống kê

- At each change in: Chọn tên cột đ• được sắp xếp (tổng hợp)

- Use function: chọn hàm cần thống kê (VD: sum)

- At subtotals to: Bấm chuột vào cột chứa dữ liệu cần thống kê (Để huỷ lệnh này ta chọn Subtotals/Remove all)

3.  Consolidate:  Tổng  hợp  dữ liệu  từ nhiều  bảng có cùng  dạng  và sao chép sang bảng mới.

B1. Đặt con trỏ ở ô bắt đầu tổng hợp

B2. Vào Data/ Consolidate.

- Tại Function: Chọn hàm tổng hợp

-   Bấm    Toprow:   Nếu   hàng   đầu   tiên    của   bảng   chi   tiết   là    tiêu   đề (Không tham gia vào tính toán)

- Bấm Left column: Nếu cột đầu tiên là tiêu đề không tham gia vào tính toán

B3. Bấm con trỏ vào mục Reference, bôi đen từng bảng chi tiết, bấm add để đưa vào sách bên dưới, lặp lại thao tác để đưa vào danh sách

- OK/ dữ liệu được tổng hợp.

4. Pivot table: (Bảng xoay)

B1. Đặt con trỏ vào ô bất kỳ trong CSDL cần tổng hợp

- Bấm Data/ Pivot table

- Chọn:   + Microsoft excel list or database: CSDL (hoặc danh sách bảng kê có dòng đầu chứa những tiêu đề cột)( ở đây ta chọn loại này)

+ External data source: Sử dụng CSDL của những chương trình khác

trong tính năng Microsoft Query.

+Multiple cosolidation ranges: Từ nhiều vùng CSDL kết hợp lại

+ Another Pivot Table: Từ bảng xoay khác.

- Bấm Next để chuyển sang bước 2

B2. Bôi đen lại toạ độ CSDL (nếu sai)/ Next chuyển sang bước 3.

B3. Bấm vào tên trường ở bên phải rồi kéo rê đến mục muốn tổng hợp. Cụ thể:

- Page: Tổng hợp theo trang (Trường làm đề mục chính)

- Row: Tổng hợp theo dòng

- Column:Tổng hợp theo cột

- Data: Các trường cần tính toán tổng hợp/ Next để chuyển sang bước 4

B4. Lựa chọn 1 trong 2 mục:

- New worksheet: Bảng tổng hợp sẽ nằm ở bảng tính mới

- Existing worksheet: Bảng tổng hợp nằm ở bảng tính hiện thời.

- Chọn Option... hộp thoại các lựa chọn bảng xoay xuất hiện

+ Name: Đặt tên cho bảng xoay.

+ Chọn Grand totals for columns: Hiển thị dòng tổng cộng của các cột dữ kiện ở

dưới bảng.

+ Chọn Grand totals for rows: Hiển thị cột tổng cộng của các dòng dữ kiện ở bên bảng.

+ Chọn Auto format table: Sử dụng tính năng AutoFormat.

+ Chọn Save data with table layout: Lưu dữ kiện với định dạng của bảng.

+ Chọn OK/ Finish để kết thúc.

Bài 10: Trích lọc dữ liệu:(Filter)

1. Autofilter: Lọc đơn giản.

- Đặt con trỏ vào ô bất kỳ của vùng dữ liệu/Data/Filter/Autofilter.

- Bấm chuột vào mũi tên chỉ xuống tại trường cần lọc, chọn giá trị cần lọc.

+ (All): Hiển thị toàn bộ bản ghi cho trường này(mục ngầm định).

+(Top 10...): Nếu muốn lọc ra 10 bản ghi đầu tiên hoặc cuối cùng.

+ (C u sto m) : Hiể n thị cửa sổ  Custom Auto Filter để bạn tạo tiêu chuẩn

AND hoặc OR.

+ ( Trị cụ thể ): Chỉ  hiển  thị  bản ghi có trị  bằ ng chính xác trị bạn chọn

cho trường này.

+ (Blanks): Hiển thị tất cả bản ghi trắng trong trường này.

+ (NonBlanks): Hiển thị tất cả các bản ghi có dữ liệu trên trường này.

 Bỏ lọc: Vào Data/Filter/ Bỏ dấu kiểm tại Autofilter.

2. Advance filter: Lọc cao cấp

Trước khi lọc thì ngoài vùng dữ liệu ra ta phải lập vùng tiêu chuẩn (Criteria)

- Đặt con trỏ vào ô bất kỳ trong vùng dữ liệu muốn đặt lọc.

- Vào Data/filter/Advance filter.

-Tại Action: + Đánh dấu mục Copy to Another Location. (Nếu muốn copy sang vị trí khác)

+ Filter the List In Place: Chọn mục này nếu muốn lọc tại chỗ

- Tại List range: Nhập địa chỉ của vùng DL (nếu đ• đúng thì thôi).

- Tại Criteria range: quét vùng tiêu chuẩn.

- Tại Copy to: Nhập vào địa ô đầu tiên sẽ sao chép ra bảng mới/ OK

- Unique Record Only: Nếu có nhiều bản ghi giống nhau thì chỉ giữ lại một bản.

Bài 11: vẽ biểu đồ

1- Vẽ biểu đồ:

Bước 1:

- Tạo lập dữ liệu cần vẽ biểu đồ.

- Đặt con trỏ vào ô đầu tiên bên góc trái của vùng dữ liệu muốn vẽ biểu đồ.

- Vào nút công cụ Chart Wizard trên thanh công cụ hoặc vào Insert/Chart. Xuất hiện hộp thoại Chart Wizard Step 1 of 4...

- Chọn loại biểu đồ và chọn kiểu chi tiết của loại đó.

- Bấm Press and Hold to Sample để xem qua biểu đồ sẽ được trình bày ra sao.

- Bấm Next để chuyển sang bước 2.

Bước 2:  Xuất hiện hộp  thoại Chart Wizard Step 2 of 4...

- Khai báo địa chỉ chứa vùng dữ liệu cần vẽ biểu đồ vào mục Data rage.

- Phân tích dữ liệu theo các hàng ngang (Rows) hay cột dọc (Columns).

- Chọn Next để chuyển sang bước 3

Bước 3: Xuất hiện hộp thoại Chart Wizard Step 3 of 4...

* Tại thẻ Titles:

- Chart tile: Đặt tên cho tiêu đề biểu đồ .

- Catagory (X) axis: Đặt tên cho trục hoành (trục X).

- Value (Y) axis: Đặt tên cho trục tung (trục Y).

* Tại thẻ Axis: ẩn/  hiện dữ liệu và giá trị trên trục (X) và (Y)

* Tại Gridlines: ẩn/hiện các đường lưới cho trục (X) và (Y).

* Tại thẻ Legend: ẩn/hiện và chọn vị trí cho bảng chú thích của biểu đồ.

* Tại Data Labels: ẩn/hiện các kiểu nh•n dữ liệu.

* Tại thẻ Data tabels: Chọn Show data table nêú muốn chèn cả bảng dữ liệu vào đáy biểu đồ.

- Bấm Next để chuyển sang bước cuối cùng.

Bước 4: Xuất hiện hộp thoại Chart Wizard Step 4 of 4:

- Chọn As new sheet: Nếu muốn chèn đồ thị vào một trang riêng (chỉ chứa biểu

đồ) như một sheet mới.

- Tại as object in: Chèn biểu đồ vào Sheet hiện hành hay một Sheet đối tượng bất

kỳ.

- Chọn Finish để kết thúc.

2 - Hiệu chỉnh  lại các thành phần của biểu đồ.

Muốn định dạng lại thành phần nào thì chọn thành phần đó, nháy chuột phải, chọn Format và tiến hành định dạng lại.

Bài 12: Trình bày trang và in ấn

1. Định dạng trang in

Vào Menu File/ Page Setup. Xuất hiện hộp thoại Page Setup.

a) Thẻ Page: Thiết lập giấy in.

- Khung Orientation:

+ Chọn Portrait        : Nếu in giấy dọc.

+ Chọn Lanscape     : Nếu in giấy ngang.

- Khung Scaling       : Chọn tỷ lệ in.

Chọn Fit to 1 page   : Nếu muốn co dữ liệu cho vừa 1 trang (trường hợp dữ liệu vượt quá 1 trang).

- Khung Page size    : Chọn khổ giấy (thường là khổ A4).

- Khung Print quality: Chọn chất lượng in (càng lớn càng đậm).

b) Thẻ Margins: Căn lề trang in.

- Top              : Lề trên.

- Bottom        : Lề dưới.

- Left              : Lề trái.

- Right           : Lề phải.

- Centet on page: Chỉnh dữ liệu vào giữa trang

+ Horiontally  : Chỉnh giữa trang theo chiều ngang.

+ Vertically    : Chỉnh giữa trang theo chiều dọc.

d) Thẻ Header/Footer: Tiêu đề đầu và cuối trang

- Chọn mục Custom Header để làm việc với dòng tiêu đề đầu.

- Chọn mục Custom Footer để làm việc với dòng tiêu đề cuối.

(Khi chọn 1 trong 2 mục này trên màn hình xuất hiện hộp thoại (như nhau)).

+ Chọn Left section, Center section hay Right section nếu muốn nhập nội dung tiêu đề đầu (cuối) trang để căn vào bên trái, giữa hay phải của trang giấy.

+ ý nghĩa của 7 nút công cụ bên trên đó tương ứng như sau: Chọn Font, Chèn số trang, chèn tổng số trang, chèn ngày tháng của hệ thống, chèn giờ, chèn tên tệp, chèn tên của bảng tính.

e) Thẻ Sheet:

- Print area      : Nhập địa chỉ vùng muốn in (trường hợp không muốn in cả trang).

- Print titles    : In các tiêu đề hàng (cột) giống nhau trên các trang in.

+ Rows to repeat at top        : Chọn hàng cần lặp lại trên các trang in.

+ Columns to repeat at left: Chọn cột cần lặp lại trên các trang in.

- Print:

+ Gridlines                 : In có đường lưới.

+ Black and white      : In đen trắng.

+ Draft quality : In nháp (không có đường lưới)

+ Row and column headings: Đề mục dòng và cột của excel.

+ Comments              : In cả các lời chú thích.

- Bấm  nút Print Preview: Nếu muốn xem trước trang in.

2. Chèn và xoá các đường ngắt trang:

a) Chèn các đường ngắt trang:

- Nhấn chuột vào ô muốn bắt đầu một trang mới.

- Vào Menu Insert/ Page Break.

b) Xoá đường ngắt trang.

- Đặt con trỏ vào ô nằm ngay phía dưới đường ngắt trang.

- Vào Menu Insert/ Remove Page Break.

3. Chọn một vùng in:

a) Chọn:

- Đánh dấu vùng cần in.

- Vào Menu File/ Print Area và chọn Set Print Area.

- Nhấp chuột vào ô bất ký để kết thúc

b) Bỏ chọn:

Vào Menu File/ Print Area/ Clear Print Area.

4. In ấn.

Sau khi định dạng trang in, ta vào Menu File/ Print. Xuất hiện hộp thoại Print

- Mục Printer (không phải chọn).

- Mục Print range:

+ Chọn All: Nếu muốn in toàn bộ Sheet hiện thời.

+ Page(s) From......To.......: In từ trang này đến trang kia.

- Mục Print what:

+ Selection: In toàn bộ vùng được chọn.

+ Selection Sheet: In những Sheet được chọn.

+ Entire Workbook: In toàn bộ bảng tính có chứa dữ liệu.

- Copies: Số bản in.