Bài giảng Microsoft excel - Khoa Công nghệ thông tin

TRƯỜNG ĐẠI HỌC CÔNG NGHỆ THÔNG TIN & TRUYỀN THÔNG

KHOA CÔNG NGHỆ THÔNG TIN

MICROSOFT EXCEL

CONGNGHETHONGTIN@ICTU.EDU.VN http://fit.ictu.edu.vn

Tổng quan về MS Excel

Làm quen với Excel

Thao tác với workbook

Thao tác với worksheet

Thao tác với ô và vùng

Sử dụng các phím tắt

“Làm quen với Excel 2010” tôi giới thiệu tới các bạn về Excel 2010 với những đặc trưng mới. Giới thiệu cách khởi động và vận hành Excel 2010 trong môi trường Windows 7. Kèm theo đó là chi tiết về màn hình làm việc chính, hệ thống phím tắt, các Menu chính.

Nội dung phần này:

Microsoft Excel 2

1. Làm quen với Excel Thanh công cụ Ribbon

Home: Chứa các nút lệnh: cắt, dán, sao chép, định dạng tài liệu, kiểu mẫu có sẵn,..

Insert: Chứa các công cụ dùng để chèn bảng biểu, sơ đồ, đồ thị, ký hiệu, v.v…, vào bảng tính.

Page Layout: Chứa các lệnh sử dụng trong việc hiển thị bảng tính và thiết lập in ấn.

Formulas: Cung cấp công cụ cho công việc chèn công thức, đặt tên vùng (range), theo dõi công thức, điều khiển cách tính toán của Excel.

Data: Các nút lệnh trao đổi với dữ liệu

Review: Chứa các lệnh kiểm tra lỗi chính tả, thêm chú thích,...

View: Chứa các lệnh thiết lập chế độ hiển thị của bảng tính (phóng to, thu nhỏ, chia màn hình,…).

Microsoft Excel 3

1. Làm quen với Excel Làm việc với nhiều bảng tính

Bạn tạo ra nhiều bảng tính với các chủ đề khác nhau để phù hợp với nhu cầu riêng, thỉnh thoảng bạn cần tìm kiếm dữ liệu từ nhiều hơn một bảng tính bằng cách chọn Swith Windows trong Menu View. Bạn cũng có thể sắp xếp các bảng tính theo cách khác nhau.

1 Click Menu View.

2 Click Switch Windows.

3 Chọn bảng tính muốn hiển thị.

Chuyển đổi giữa các bảng tính

1 Click Menu View.

2 Click Arrange All.

3 Chọn kiểu sắp xếp tương ứng.

4 Click OK.

Sắp xếp các bảng tính

Microsoft Excel 4

1. Làm quen với Excel Phóng to, thu nhỏ bảng tính

1 Click Zoom In để tăng 10% kích thước cửa sổ.

2 Điều chỉnh Zoom Slider để đạt kích thước phù hợp.

3 Click Zoom Out để thu nhỏ 10% kích thước cửa sổ.

Phóng to, thu nhỏ

4 Click Menu View.

5 Click Zoom.

6 Chọn kích thước phù hợp.

7 Click OK.

Thuật ngữ cần nhớ

Phóng to, thu nhỏ theo kích thước định sẵn

Một workbook có thể chứa nhiều sheet, do vậy có thể tổ chức, lưu trữ nhiều loại thông tin có liên quan với nhau chỉ trong một tập tin.

Mỗi workbook chứa rất nhiều worksheet hay chart sheet, tùy thuộc vào bộ nhớ máy tính.

Workbook:

Có khi được gọi là “sheet” hay “bảng tính”, là nơi lưu trữ và làm việc với dữ liệu.

Worksheet:

Microsoft Excel 5 Một worksheet có nhiều ô và các ô có chứa các cột và dòng. Worksheet được lưu trong workbook.

Tạo file Excel 2010

2. Thao tác với Workbook Mở file Excel đã có sẵn

1 Click File

2 Click New

2 Click Open.

3 Xem các mẫu dựng sẵn

3 Tìm đường dẫn đến thư mục chứa file

4 Chọn mẫu dựng sẵn

4 Chọn file muốn mở

5 Click nút Create

5 Click nút Open

Mở file Excel

Sử dụng phím tắt: Ctrl + N

Microsoft Excel 6

2. Thao tác với Workbook

Lưu trữ bảng tính Excel

Đóng bảng tính và thoát khỏi chương trình Excel

1 Click trên thanh Quick Access.

1 Click Office Button

2 Click Office Button

2 Click Close

3 Click Save.

Lưu bảng tính Đóng bảng tính

3 Click nút Close.

Thoát khỏi Excel:

2 Click Office Button

4 Click Save As.

5 Chọn nơi lưu trữ file

6 Đặt tên cho file mới

7 Click Save để lưu file

Nếu file của bạn chưa được lưu trữ trước khi thoát hoặc có cập nhật chưa lưu, Excel sẽ hỏi bạn có muốn lưu trữ

những thay đổi này không? A: Đồng ý lưu trữ cập nhật sau cùng B: Không đồng ý (giữ nguyên như lần lưu trữ trước)

C: Tiếp tục làm việc với bảng tính hiên thời

Lưu bảng tính với tên mới

Microsoft Excel 7

3. Thao tác với Worksheet

Sao chép, thêm mới, xóa Worksheet

1 Click chuột phải vào Worksheet muốn sao chép.

2 Chọn nhãn Move or Copy..

3 Chọn vị trí muốn đặt Worksheet

4 Tick vào ô Create a copy.

5 Click OK.

Sao chép Worksheet

1 Click chuột phải vào Worksheet muốn xóa

6 Chọn Delete.

Xóa Worksheet

1 Double Click vào tiêu đề một Worksheet bất kỳ

2 Chọn Insert…

3 Chọn Worksheet.

4 Click OK.

Tạo Worksheet mới

Microsoft Excel 8

3. Thao tác với Worksheet

Ẩn/Hiện Worksheet

1 Double Click vào tiêu đề một Worksheet muốn ẩn.

2 Chọn Hide.

Ẩn Worksheet

1 Double Click vào tiêu đề một Worksheet bất kỳ.

2 Chọn Unhide.

3 Chọn Worksheet muốn hiển thị lại.

4 Click OK.

Hiển thị lại Worksheet đã bị ẩn

Microsoft Excel 9

3. Thao tác với Worksheet

Các thao tác với Worksheet

Trong phần này, chúng tôi giới thiệu tới các bạn cách quản lý các trang bảng tính (sheet) của mình. Các thao tác cơ bản như: tạo mới, di chuyển, đổi tên, chọn bảng tính sẽ được giới thiệu bên dưới.

1 Click vào tên của sheet muốn hiển thị

Xem và lựa chọn các Worksheet

Đổi tên Worksheet

1 Double Click vào sheet muốn đổi tên và gõ tên mới

Di chuyển vị trí các sheet

1 Double Click vào sheet muốn di chuyển và kéo sang phải hoặc sang trái

2 Dừng lại theo vị trí chỉ dẫn như mong muốn

Microsoft Excel 10

4. Thao tác với ô và vùng

Lựa chọn vùng dữ liệu

Việc lựa chọn một nhóm các ô dữ liệu – vùng dữ liệu, được thực hiện khi bạn cần thiết phải copy, định dạng, thay đổi cỡ chữ …v.v. của vùng dữ liệu đó.

1

1 Đưa trỏ chuột đến ô đầu tiên trên cùng của vùng lựa chọn.

2 Click và kéo đến ô dưới cùng bên phải của vùng lựa chọn.

Chọn vùng dữ liệu liên tục

Chọn vùng dữ liệu không liên tục

2

1 Đưa trỏ chuột đến ô đầu tiên trên cùng của vùng lựa chọn.

1

2

2 Click và kéo đến ô dưới cùng bên phải của vùng lựa chọn giữ phím Ctrl.

3 Đưa trỏ chuột đến ô đầu tiên trên cùng của vùng tiếp theo.

4 Click và kéo đến ô dưới cùng bên phải của vùng này.

Chọn vùng dung bàn phím: Shift + các mũi tên, Ctrl+A

3

4

Microsoft Excel 11

4. Thao tác với ô và vùng Chọn nhiều dòng/cột liên tục

Tiêu đề cột

1 Đưa trỏ chuột đến tiêu đề của dòng/cột đầu tiên.

2 Click và kéo đến tiêu đề của dòng/cột cuối cùng.

Bạn có thể giữ phím Shift, rồi click vào tiêu đề

dòng/cột đầu tiên và tiêu đề dòng/cột cuối cùng để

chọn nhiều dòng/cột liên tục.

Tiêu đề dòng

Giữ phím Ctrl

Click chuột vào tiêu đề của dòng/cột đầu tiên kéo đến tiêu đề của dòng/cột cuối cùng của vùng đầu tiên, Nhả chuột

Click vào tiêu đề dòng/cột đầu tiên của vùng tiếp theo kéo đến tiêu đề của dòng/cột cuối cùng của vùng tiếp theo.

Chọn nhiều dòng/cột không liên tục

Microsoft Excel 12

4. Thao tác với ô và vùng Cắt, sao chép, dán dữ liệu

v Cắt dữ liệu

Trong quá trình làm việc với Excel, những chức năng được chúng ta sử dụng nhiều là: cắt, sao chép, dán dữ liệu.

1 Lựa chọn vùng dữ liệu cần cắt.

2 Click menu Home.

3 Click nút Cut (hoặc Ctrl + X).

v Sao chép dữ liệu

1 Lựa chọn vùng dữ liệu cần sao chép.

2 Click menu Home.

4 Click nút Copy (hoặc Ctrl + C).

v Dán dữ liệu dữ liệu

1 Lựa chọn vùng dữ liệu cần sao chép.

2 Click menu Home.

5 Chọn vùng dữ liệu cần dán dữ liệu.

6 Click nút Paste để dán dữ liệu (hoặc Ctrl + V).

Microsoft Excel 13

4. Thao tác với ô và vùng

Thao tác với ô dữ liệu, hàng, cột

1 Click chuột tại vị trí cần thêm.

2 Click Office Button.

3 Chọn nút Insert

4 Chọn Insert Cell

5 Chọn cách chuyển vị chí ô dữ liệu hiện thời

6 Click OK

Phần này chúng tôi sẽ giới thiệu tới các bạn các thao tác với ô dữ liệu, hàng, cột: Thêm, sửa, xóa một ô dữ liệu; thêm, bớt hàng/cột. Thêm một ô dữ liệu vào Worksheet

Di chuyển vùng dữ liệu

1 Chọn vùng dữ liệu cần di chuyển.

2 Đưa trỏ chuột ra bên rìa của vùng dữ liệu đã chọn.

3 Kéo vùng dữ liệu được chọn đến vị trí mới

Xóa ô dữ liệu

1 Chọn vùng dữ liệu cần xóa.

4 Chọn Delete Cell.

2 Click Office Button.

5 Chọn cách dồn dữ liệu sau khi xóa ô hiện

3 Chọn nút Delete

thời.

6 Click OK.

Microsoft Excel 14

5. Sử dụng phím tắt Các phím tắt thông dụng

1. Một số phím tắt ngầm định của Excel, để thực thi bạn cần ấn đồng thời tổ hợp 2 phím (để copy ấn Ctrl+C).

Các phím tắt tương ứng sẽ hiện lên trên Robbin, bạn ấn các phím tương ứng để thực hiện các chức năng tương ứng .

VD: để copy: chọn Home > click ; sử dụng phím tắt: ấn Alt nhả > ấn H nhả > ấn C.

Để quay trở lại ấn phím ESC.

2. Các phím tắt thời gian thực, bắt đầu bằng cách ấn phím Alt trên bàn phím (bấm xong nhả).

Microsoft Excel 15

Bài tập thực hành 1: Kỹ năng định dạng dữ liệu trong Excel Tạo một file đặt tên là Baitap_Excel_1

Soạn thảo 3 sheet: TT_Chung, TT_Nhansu, TT_Hocsinh. Các ô nhập liệu (Tên trường, Mã trường,..) đặt màu nền xanh nhạt. Sau khi soạn thảo xong hãy bỏ chế độ hiển thị lưới (Gridlines). Nội dung các sheet được mô tả như sau:

Sheet "TT_chung

Microsoft Excel 16

Bài tập thực hành 1: Kỹ năng định dạng dữ liệu trong Excel

Sheet "TT_Nhansu"

Microsoft Excel 17

Bài tập thực hành 1: Kỹ năng định dạng dữ liệu trong Excel

Sheet "TT_Hocsinh": Đổi màu nền các khu vực để dễ nhìn

Microsoft Excel 18

Làm việc với dữ liệu trong Excel

Nhập liệu và hiệu chỉnh

Định dạng

Tìm kiếm và thay thế dữ liệu

Sắp xếp và lọc dữ liệu

Trong bài học này, bạn sẽ học cách nhập và sửa đổi dữ liệu Excel, di chuyển dữ liệu trong một bảng tính, tìm và thay thế dữ liệu hiện có, sắp xếp và lọc dữ liệu.

Nội dung phần này:

Microsoft Excel 19

1. Nhập liệu Nhập dữ liệu

1 Click vào ô muốn nhập dữ liệu.

2

1

2 Gõ nội dung, kết thúc bằng ấn phím Enter.

Nhập dữ liệu với ngắt dòng

1 Click vào ô muốn nhập dữ liệu.

2 Gõ nội dung muốn hiển thị trên dòng đầu tiên, ấn Alt + Enter để thêm dòng mới

3 Gõ nội dung dòng tiếp theo, kết thúc bằng ấn phím Enter.

Dữ liệu số nhập dấu chấm (.) thay dấu phẩy (,) ngăn cách phần thập phân.

1

2

Để Excel hiểu một dữ liệu dạng khác là dữ liệu dạng chữ thì nhập dấu ’ trước dữ liệu đó.

3

Microsoft Excel 20

2

1

3 1. Nhập liệu Chèn ký hiệu đặc biệt 1 Chọn Menu Insert.

2 Click ký hiệu Symbol.

3 Chọn tab Symbols.

4 Chọn Font chữ.

5

5 Chọn ký tự đặc biệt cần chèn.

6 Click Insert

7 Kết thúc bấm Cancel.

4

7

6

Microsoft Excel 21

1. Nhập liệu Điền dữ liệu tự động

v Điền dữ liệu tự động

Khi bạn cần lập danh sách mặt hàng gồm hàng trăm mặt hàng khác nhau với cột số thứ tự tăng dần từ 1 đến hết. Nếu chúng ta nhập từng số thứ tự, quả thật tốn rất nhiều thời gian.

1 1 Nhập dữ liệu vào ô đầu tiên muốn điền vào các dòng.

2 Đưa trỏ chuột vào phía dưới cùng bên phải ô đầu

tiên, sau đó kéo xuống ô dưới cùng của vùng muốn

điền.

2

v Điền dữ liệu tự động một chuỗi giá trị

1 Nhập dữ liệu vào ô đầu tiên.

3

1 2 Nhập dữ liệu vào ô thứ 2.

2 3 Chọn vùng dữ liệu gồm ô thứ nhất và ô thứ 2.

4 Đưa trỏ chuột vào phía dưới cùng bên phải của vùng

dữ liệu, sau đó kéo xuống ô dưới cùng của vùng muốn

điền.

4

Microsoft Excel 22

1. Nhập liệu Các kiểu dữ liệu

Khi nhập dữ liệu cho các ô trong bảng tính, chúng ta cần chú ý đến kiểu dữ liệu để việc tính toán sau này được chính xác. Các kiểu dữ liệu chính mà Excel hỗ trợ: Dữ liệu văn bản, kiểu số, ngày tháng, thời gian,… v.v.

Dữ liệu văn bản

Dữ liệu số

Dữ liệu ngày tháng

vDữ liệu dạng số (số, tiền tệ), thời gian thường được căn phía bên phải.

vDữ liệu văn bản, ngày tháng thường được căn về phía trái.

Microsoft Excel 23

2. Định dạng

C chỡ ữ

ẻ

K khung

Định dạng chung: Các nút định dạng thông dụng của Excel được bố trí rất thuận lợi truy cập trong nhóm Home của thanh Ribbon

Khi cần các định dạng phức tạp hơn ta sử dụng hộp thoại Format Cells được trình bày ở tiếp theo

Microsoft Excel 24

Ki u ể ị ể hi n th số

Khung xem cướ tr

2. Định dạng Chọn Home / nhóm Cells / Format / Format Cells… Tab Number: định cách hiển thị số

ố ữ S ch ố ậ s th p phân

Tab Alignment: định cách chỉnh vị trí dữ liệu

Tab Font: định font chữ

ử ụ S d ng ký ệ hi u ngăn cách hàng nghìn

Tab Border: định đường kẻ viền các ô

Tab Fill: Đặt màu nền

ể

ị Cách hi n th s âmố

Protection: Bảo mật dữ liệu

Microsoft Excel 25

2. Định dạng Format/Cells… Tab Alignment

Format/Cells… Tab Alignment

Chọn font chữ

Chọn kiểu chữ

Căn dữ liệu chiều ngang ô

Định hướng văn bản

Căn dữ liệu chiều dọc ô

Màu chữ

Xuống dòng vừa độ rộng ô

Gạch chân chữ

Cơ chữ

Thu nhỏ chữ vừa kích thước ô

Nhập các ô liền kề thành 1 ô

Microsoft Excel 26

2. Định dạng Format/Cells… Tab Border

Không kẻ khung

Khung bên trong

Khung bao ngoài

Chọn kiểu đường kẻ

Màu đường kẻ

Microsoft Excel 27

2. Định dạng Định dạng bảng: Excel thiết lập sẵn rất nhiều biểu mẫu định dạng bảng và còn hỗ trợ tạo thêm các biểu mẫu mới Bước 1. Chọn vùng cần định dạng bảng

Bước 2. Chọn Home / nhóm Styles / chọn Format As Table

Bước 3. Cửa sổ Style liệt kê rất nhiều biểu mẫu định dạng bảng, chọn một trong các biểu mẫu. Ví dụ chọn mẫu Light số 9

Bước 4. Cửa sổ Format As Table hiện lên nhấn OK để xác nhận.

Microsoft Excel 28

3. Tìm kiếm và thay thế

Tìm kiếm, thay thế dữ liệu: Sau khi nhập dữ liệu vào bảng tính, bạn cần tìm kiếm xem một nội dung nào đó đã có hay chưa, hay đơn gian là thay thế 1 từ bằng một cụm từ mới. Chức năng tìm kiếm, thay thế dữ liệu của Excel sẽ giúp bạn làm công việc này một cách nhanh chóng và dễ dàng.

1 Click Office Button.

2 Click nút Find & Select.

3 Click Find...

4 Nhập nội dung tìm kiếm vào ô Find what.

5 Click Find Next (tìm vì trí trùng tiếp theo); Find All (hiện tất cả các ô tìm thấy); Close (Thoát).

Tìm kiếm dữ liệu

Tìm kiếm, thay thế dữ liệu

1 Click Office Button.

2 Click nút Find & Select. 6 Click Replace...

7 Nhập nội dung tìm kiếm vào ô Find what.

8 Nhập nội dung cần thay thế khi tìm thấy.

9 Click Find Next (tìm vị trí trùng tiếp theo); Replace (thay thế nội dung cho vị trí tìm thấy hiện thời); Replace All (thay thế tất cả dữ liệu tìm thấy bằng nội dung mới); Close (thoát);

Microsoft Excel 29

4. Sắp xếp và lọc dữ liệu

Tùy chọn sắp xếp

Để sắp xếp nhiều hơn một cột:

Kích nút Sort & Filter trên tab Home

Chọn cột mà bạn muốn sắp xếp đầu tiên

Kích Add Level

Sort (sắp xếp) và Filter (lọc): là những tính năng cho phép bạn thao tác dữ liệu trong một bảng tính được thiết lập dựa trên các tiêu chuẩn nào đó.

Chọn cột tiếp theo bạn muốn sắp xếp

Kích OK

Đánh dấu các ô muốn được sắp xếp

Kích nút Sort & Filter trên tab Home

Kích nút Sort A to Z (tăng dần) hay Sort Z to A (giảm dần)

Sắp xếp: Để thực hiện một sắp xếp theo chiều tăng dần hay giảm dần trên một cột:

4. Sắp xếp và lọc dữ liệu Lọc dữ liệu

Bộ lọc cho phép bạn chỉ hiển thị dữ liệu mà đáp ứng các tiêu chuẩn nhất định.

Kích vào cột hoặc chọn các cột chứa dữ liệu mà bạn muốn lọc

Trên tab Home, kích Sort & Filter

Kích nút Filter

Kích vào mũi tên phía dưới ô đầu tiên

Kích Text Filter

Kích Words bạn muốn lọc

Để sử dụng bộ lọc:

Kích nút Sort & Filter

Kích Clear

Để hủy bỏ lọc đã thiết lập

Bài tập thực hành 2

Bài 1: Thực hành định dạng dữ liệu trong Excel với các yêu cầu tương tự bài 1. Nội dung các sheet như sau

Microsoft Excel 32

Bài tập thực hành 2

Sheet "Lop_2"

Microsoft Excel 33

Bài tập thực hành 2

S l

Mã hàng

Ngày bán (dd/MM/yy)

Bài 2: Sử dụng Format cells (định dạng kiểu ngày, số, đơn vị tiền tệ, bảng tính,...) thực hiện chức năng thay đổi độ rộng của cột, chiều cao của hàng, chức năng freeze panels, sắp xếp bảng tính.

ố ượ (T n)ấ

ơ Đ n giá (USD)

Thành ti nề (USD)

Thành ti n ề (VNĐ)

22/8/2014

X001

100

2/8/2014

T001

200

400

12/8/2014

N001

250

500

3/8/2014

N002

300

1800

10/8/2014

X003

100

300

Thưc hiện các yêu cầu sau:

TT H đ mọ ệ

Tên Mã ng chạ

Tên đ n vơ ị

Tên ng chạ

L ng CB

Phòng hành chính

1 Nguy n Thễ

Chuyên viên

ị Bình

01003

1200000

Chuyên viên chính

Phòng hành chính

2 Đào Anh

01002

Công

1850000

3 Hoàng Tu nấ Dũng

Chuyên viên cao c pấ

Phòng QLKH

01001

1600000

Phòng Kinh doanh

Chuyên viên

4 Đ Thỗ ị

01003

Hoa

950000

Chuyên viên cao c pấ

Phòng Kinh doanh

5 Lê T nấ

01001

Nam

1000000

Bài 3: Sử dụng Format cells (định dạng kiểu ngày, số, đơn vị tiền tệ, bảng tính,...) thực a) Sắp xếp danh sách theo thứ tự ưu hiện chức năng thay đổi độ rộng của cột, chiều cao của hàng, chức năng freeze panels, ươ tiên Tên, họ đệm. sắp xếp bảng tính. b) Định dạng cột lương theo tiền VNĐ c) Sử dụng chức năng freeze panel cố định 3 3 cột bên trái và dòng đầu tiên d) Đặt lọc danh sách để người dùng có thể lọc danh sách theo bất kỳ cột nào. e) Trích danh sách cán bộ là chuyên viên

Microsoft Excel 34

Công thức và hàm

Công thức

Một số thao tác với công thức

Toán tử, toán hạng

Độ ưu tiên của các phép toán

Hàm

Cách nhập hàm trong Excel

Hàm lồng nhau

Tham chiếu

Tham chiếu tương đối/tuyệt đối

Một số lỗi thường gặp

Công thức (formula) và hàm (function) là những công cụ mạnh nhất của Excel dùng để thực hiện tính toán trên các ô dữ liệu. Tuy nhiên, việc sử dụng linh hoạt công thức và hàm của Excel không phải là một nhiệm vụ đơn giản do công thức và hàm của Excel là một dạng của lập trình xử lý dữ liệu.

Nội dung phần này:

Để có thể nắm bắt và sử dụng công thức/hàm cần nắm vững những khái niệm cơ bản, các hàm do excel cung cấp, cần tư duy logic tốt để kết hợp linh động các hàm sẵn có của Excel vào công việc. Bài này sẽ giới thiệu những khái niệm cần biết để có thể Microsoft Excel nắm bắt được việc sử dụng công thức và

hàm trong Excel.

Công thức và hàm

Công thức

• Kết hợp các toán tử, toán hạng,

Công thức trong Excel là một loại dữ liệu đặc biệt:

hàm, tham chiếu

Công thức

Giá trị tĩnh (hằng số)

Giá trị tính ra bằng công thức

• Bắt đầu bằng dấu bằng (=) • Tự động cập nhật khi các thành

• Luôn trả về giá trị (do đó có thể 1. Hàm: Hàm PI() trả về giá trị của pi: 3,142... xuất hiện trong ô như các giá trị 2. Tham chiếu: A2 trả về giá trị trong ô A2. 3. Hằng số: Các số hoặc giá trị văn bản được nhập trực tĩnh bình thường) tiếp vào một công thức, như 2. 4. Toán tử: Toán tử ^ (mũ) nâng một số lên theo một lũy thừa và toán tử (dấu sao) * nhân các số.

Để hiển thị bản thân công thức trong ô dữ liệu: thêm ký tự nháy đơn (‘) trước dấu bằng.

phần thay đổi

Microsoft Excel 36

Công thức và hàm

Một số thủ thuật với công thức Ø Kéo thả công thức với hàng/cột

Ø Copy công thức cho một loạt ô

Ø Chuyển đổi kết quả công thức thành giá trị tĩnh (hằng số)

Ø Tổ hợp phím Ctrl + `

Ø Tổ hợp phím Ctrl + D

Microsoft Excel 37

Công thức và hàm

Toán tử • là những phép toán cơ bản dùng để xây dựng nên các công thức • có nhiệm vụ kết nối các toán hạng và các hàm thành những công thức phức tạp hơn

Ý nghĩa

Ví dụ

Kết quả

STT 1 2 3 4

Toán tử + - * /

Phép cộng Phép trừ, số âm Phép nhân Phép chia

3+3 45-4 150*.05 3/3

3 cộng 3 là 6 45 trừ 4 còn 41 150 nhân 0.50 thành 7.5 3 chia 3 là 1

Lũy thừa

2^4, 16^(1/4)

2 lũy thừa 4 thành 16, Lấy căn bậc 4 của 16 thành 2

• toán tử số học • toán tử ghép xâu • toán tử logic • toán tử tham chiếu

Ghép xâu

“Lê” & “Thanh”

Nối chuỗi “Lê” và “Thanh” lại thành “Lê Thanh”

A1=B1 A1>B1

Ví dụ ô A1=3, ô B1=6 Kết quả: FALSE Ví dụ ô A1=3, ô B1=6 Kết quả: FALSE

7 8

= >

A1>=B1

Ví dụ ô A1=3, ô B1=6 Kết quả: FALSE

Ví dụ ô A1=3, ô B1=6 Kết quả: TRUE

A1<=B1

Ví dụ ô A1=3, ô B1=6 Kết quả: TRUE

So sánh bằng So sánh lớn hơn So sánh lớn hơn hoặc bằng So sánh kém So sánh nhỏ hơn hoặc bằng So sánh khác

A1<>B1

Ví dụ ô A1=3, ô B1=6 Kết quả: TRUE Ví dụ ô A1=3, ô B1=6 Kết quả: 9 Ví dụ ô A1=3, ô B1=6 Kết quả: 9

, hoặc ; Dấu tách các tham số Sum(A1, B1) Sum(A1: B1)

Tham chiếu mảng

12 13 14

B1: B6 A3: D3

Trả về giá trị của ô

Trả về các ô giao nhau giữa hai vùng

: Khoảng trắng (space)

Dấu tách tham số là dấu phảy (,) hoặc dấu chấm phảy (;) phụ thuộc vào thiết lập ngôn ngữ hệ thống

Phân loại:

Microsoft Excel 38

Công thức và hàm

Độ ưu tiên của các phép toán • Các toán tử trong một công thức được thực hiện theo thứ tự, phụ thuộc vào độ ưu

• Độ ưu tiên quyết định phép toán nào thực hiện trước (vd: nhân chia trước, cộng trừ

tiên và tính kết hợp của toán tử.

• Tính kết hợp quyết định xem khi có nhiều phép toán cùng độ ưu tiên thì thực hiện các phép toán theo thứ tự từ trái sang phải (tính kết hợp trái) hay từ phải sang trái (tính kết hợp phải).

sau)

Ưu tiên giảm dần

Toán tử : (hai chấm) (1 khoảng trắng) , (dấu phẩy) – % ^ * và / + và – & = < > <= >= <>

Mô tả Toán tử tham chiếu Số âm Phần trăm Lũy thừa Nhân và chia Cộng và trừ Nối chuỗi So sánh

Ưu tiên 1 2 3 4 5 6 7 8

Để thay đổi trật tự tính toán, tương tự như trong các phép toán số học, có thể sử dụng cặp dấu ngoặc đơn. Các phép toán trong dấu ngoặc đơn sẽ được ưu tiên thực hiện trước. Nếu có nhiều cặp dấu ngoặc đơn lồng nhau thì các phép toán ở cặp ngoặc bên trong sẽ được thực hiện trước.

Microsoft Excel 39

Công thức và hàm

Hàm

Hàm là công thức được định sẵn thực hiện tính toán bằng cách sử dụng các giá trị cụ thể, gọi là các đối số theo một thứ tự hoặc cấu trúc cụ thể. Hàm có thể được sử dụng để thực hiện các tính toán đơn giản hoặc phức tạp.

Cấu trúc hàm

=tên_hàm(danh_sách_đối_số)

• Đối số là các giá trị tĩnh, là tham chiếu tới một ô dữ liệu hoặc là tham chiếu tới một vùng dữ liệu.

Cấu trúc của một hàm 1. Cấu trúc. Cấu trúc của một hàm bắt đầu với dấu bằng (=), theo sau bởi tên hàm, một dấu ngoặc đơn mở, các đối số cho hàm được phân tách bởi dấu phẩy và dấu ngoặc đơn đóng. 2. Tên hàm. Đối với danh sách các hàm sẵn có của Excel, chọn một ô và bấm SHIFT+F3. 3. Đối số. Đối số có thể là số, văn bản, giá trị lô-gic như TRUE hoặc FALSE, mảng, giá trị lỗi như #N/A, hoặc tham chiếu ô. Đối số mà bạn chỉ định phải tạo giá trị hợp lệ cho đối số đó. Đối số cũng có thể là hằng, công thức hoặc hàm khác. 4. Hỗ trợ. Excel hiển thị thông tin về cú pháp và đối số khi nhập hàm. Ví dụ, nhập =ROUND( và hộp thông tin hỗ trợ xuất hiện. Hộp thông tin hỗ trợ chỉ xuất hiện khi dùng các hàm dựng sẵn.

• Hàm có thể làm đối số cho các hàm khác (nghĩa là một hàm cũng có thể chứa các hàm khác).

• Cần kết hợp linh hoạt các hàm để tạo thành biểu thức tính toán phức tạp hơn theo nhu cầu sử dụng.

Trong đó:

Microsoft Excel 40

Các hóm hàm thông dụng

• Nhóm hàm toán học

• Nhóm hàm thời gian

• Nhóm hàm chuỗi

• Nhóm hàm logic

• Nhóm hàm thống kê

• Hàm dò tìm dữ liệu

(sẽ nghiên cứu kỹ từng loại)

Công thức và hàm

Cách nhập hàm

Cách 2 – dùng nút Insert Function B1. Chọn một ô

Cách 3 – dùng nút Insert Function B1. Chọn một ô B2. Chọn tab Formulas trên thanh Ribbon

B2. Ấn nút Insert Function

Cách 1 – dùng bàn phím

B3. Chọn hàm trong hộp thoại

B1. Chọn một ô

B3. Chọn hàm trong hộp thoại và ấn OK

B2. Nhập dấu =

B4. Nhập đối số trong hộp thoại (như cách

2) và ấn OK

B4. Nhập đối số trong hộp thoại và ấn OK

B3. Gõ một số ký tự bắt đầu của hàm

(2) Dùng chuột trái khoanh vùng các ô cần chọn

(1) Ấn nút này để chọn ô trong bảng thay vì nhập số trực tiếp

(3) Ấn nút này sau khi hoàn tất lựa chọn

B4. Chọn hàm trong danh sách hỗ trợ

41 Microsoft Excel B5. Gõ dấu ( và xem

thông tin hỗ trợ về danh

sách đối số

B6. Nhập đối số

B7. Gõ dấu ) và ấn Enter

Công thức và hàm

Hàm lồng nhau

Một hàm có thể được sử dụng như một trong các tham đối của hàm khác.

Ví dụ, công thức sau đây sử dụng một hàm SUM và AVERAGE lồng trong hàm IF.

Để sử dụng hàm lồng nhau phải đáp ứng các yêu cầu sau:

Giá trị trả về hợp lệ

Khi một hàm lồng được sử dụng như một đối số, hàm lồng phải trả về dạng giá trị giống với giá trị mà đối số sử dụng.

Ví dụ, nếu một hàm trả về giá trị TRUE hoặc FALSE, hàm được lồng vào phải trả về giá trị TRUE hoặc FALSE. Nếu một hàm không trả về, Excel sẽ hiển thị giá trị lỗi #VALUE! .

Giới hạn mức độ lồng

Một công thức có thể chứa đến bảy mức độ hàm lồng.

Microsoft Excel 42 Khi một hàm (chúng ta sẽ gọi là Hàm B) được sử dụng như một đối số trong một hàm khác (chúng ta sẽ gọi là Hàm A), Hàm B đóng vai trò một hàm mức hai. Ví dụ, cả hàm AVERAGE và hàm SUM đều là hai hàm mức hai nếu chúng được sử dụng như

các đối số của hàm IF. Một hàm được lồng vào trong hàm AVERAGE đã được lồng

khi đó sẽ là hàm mức ba và v.v…

Công thức và hàm

Tham chiếu • là địa chỉ một ô (tham chiếu ô) hoặc một nhóm ô dữ liệu (tham chiếu vùng) cho Excel biết nơi cần tìm giá trị hoặc dữ liệu

Tham chiếu ô

• được sử dụng trong công thức, hàm, đồ thị và

Tham chiếu vùng

• sử dụng tham chiếu trong công thức và hàm

một số lệnh khác

Tham chiếu đến địa chỉ ở worksheet khác trong cùng workbook: Tên_sheet!Địa_chỉ_tham_chiếu. Ví dụ: =A2*Sheet2!A2 =A2*’Thong so’!B4

cho phép tiết kiệm thời gian sửa chữa các công thức khi các giá trị tính toán có sự thay đổi

Tham chiếu ô:

• phần ký tự chỉ vị trí cột, giá trị từ A đến XFD Để tham chiếu đến

Cách viết

A10

Tham chiếu đến địa chỉ trong workbook khác: [Tên_Workbook]Tên_sheet!Địa_chỉ_ô. Ví dụ: =A2*[Bai2.xlsx]Sheet3!A4 =A2*‘[Bai tap 2.xlsx]Sheet3’!A4

A10:A20

(16.384 cột) Ô trong cột A và hàng 10 • phần chữ số chỉ vị trí hàng, giá trị từ 1 đến Phạm vi ô giữa cột A và các hàng từ 10 đến 20

B15:E15

Phạm vi ô giữa hàng 15 và các cột từ B đến E • địa chỉ cột viết trước địa chỉ hàng 5:5 Tất cả các ô trong hàng 5

Khi tên sheet hay workbook có chứa khoản trắng: cần để tên trong cặp nháy đơn ‘ ’. Ví dụ: =A2*‘C: \Tai lieu\[Bai tap 2.xlsx]Sheet3’!A4

Tất cả các ô trong hàng 5 đến 10

5:10

Tất cả các ô trong cột H

H:H

H:J

A10:E20

Khi sao chép/ di chuyển công thức chứa tham chiếu, địa chỉ tham chiếu trong công thức ở vị trí mới sẽ tự thay đổi cho phù hợp với số lượng ô đã di chuyển.

1.048.576

Tham chiếu vùng: Tất cả các ô trong cột từ H đến J • chứa địa chỉ của ô nằm ở góc trên bên trái và Phạm vi ô trong cột A đến E và các hàng từ 10 đến 20 góc dưới bên phải của vùng • viết tách nhau bởi dấu hai chấm Microsoft Excel 43

Công thức và hàm

Tham chiếu: tương đối tuyệt đối hỗn hợp

Tham chiếu tương đối, tham chiếu tuyệt đối • Tham chiếu tương đối: Các dòng và cột tham chiếu sẽ thay đổi khi sao chép hoặc di dời công thức đến vị trí khác một lượng tương ứng với số dòng và số cột mà ta di dời. Đây là loại tham chiếu mặc định.

• Tham chiếu tuyệt đối: Các dòng và cột tham chiếu không

Địa chỉ tương đối thay đổi khi copy công thức

Địa chỉ tương đối không thay đổi khi copy công thức

Công thức với địa chỉ tuyệt đối

• Tham chiếu hỗn hợp: Phối hợp tham chiếu địa chỉ tương đối Công thức và tuyệt đối, trong đó địa chỉ nào cần giữ cố định thì thêm ký với địa chỉ tự $ vào trước. tương đối

- Dấu $ trước thứ tự cột là cố định cột và trước thứ tự dòng là cố định dòng; - Nhấn phím F4 nhiều lần để (tuyệt đối) cố định/ bỏ cố định dòng hoặc cột; - Khi nhập công thức có thể click chọn ô tham chiếu hoặc rê chuột để chọn vùng tham chiếu trên worksheet;

thay đổi khi ta di dời hay sao chép công thức. Địa chỉ tuyệt đối của hàng và cột được viết sau ký tự Dollar ($).

Microsoft Excel 44

Công thức và hàm

Một số lỗi thường gặp

Lỗi chia cho 0

Xem thông tin chi tiết về lỗi

• Lỗi #DIV/0!: Trong công thức có chứa phép

Trong quá trình làm việc với các hàm Excel có thể phát sinh các lỗi. Việc đọc hiểu các lỗi cho phép nhanh chóng tìm được hướng khắc phục. Dưới đây là một số lỗi thường gặp nhất:

Danh sách giải pháp

• Lỗi #NAME?: Do dánh sai tên hàm hay tham

chia cho 0 (zero) hoặc chia cho ô rỗng

• Lỗi #N/A: Công thức tham chiếu đến ô mà có dùng hàm NA để kiểm tra sự tồn tại của dữ liệu hoặc hàm không có kết quả

• Lỗi #NULL!: Hàm sử dụng dữ liệu giao nhau của 2 vùng mà 2 vùng này không có phần chung nên phần giao rỗng

• Lỗi #NUM!: Vấn đề đối với giá trị, ví dụ như dùng nhầm số âm trong khi đúng phải là số dương

• Lỗi #REF!: Tham chiếu bị lỗi, thường là do ô

chiếu hoặc đánh thiếu dấu nháy

tham chiếu trong hàm bị xóa

Hộp thoại báo có lỗi trong công thức đang nhập

• Lỗi #VALUE!: Công thức tính toán có chứa

kiểu dữ liệu không đúng. Microsoft Excel 45

Các hàm toán học và logic

Các kiểu dữ liệu

Nhóm hàm logic

Hàm điều kiện - IF

Nhóm hàm toán học

Hàm tính tổng SUM, SUMIF

Các làm logic và toán học là những hàm cơ bản và đơn giản nhất trong Excel. Các hàm này cho phép thực hiện những tính toán thường gặp nhất trong thực tế.

Phần này giới thiệu chi tiết về nhóm các hàm logic và một số hàm toán học thông dụng, cũng như trình bày một số vấn đề liên quan đến kiểu dữ liệu – một khái niệm quan trọng khi làm việc với công thức và hàm trong Excel.

Nội dung phần này

Microsoft Excel 46

Các hàm toán học và logic

Kiểu dữ liệu trong Excel

Vùng giá trị

Tên kiểu

Số nguyên (Integer)

-263 tới 263-1

Số thực (Decimal)

-1,79E+308 tới -2,23E-308, số 0, số thực dương trong phạm vi từ 2,23E-308 tới 1,79E+308. Độ chính xác tới 15 chữ số phần thập phân

Logic (Boolean)

TRUE hoặc FALSE

268.435.456 ký tự Unicode

Chuỗi ký tự (TEXT/STRING)

Ngày tháng (Date/Datetime)

Chứa ngày, tháng, năm, giờ, phút, giây. Ngày nhỏ nhất được chấp nhận là ngày 01 tháng 01 năm 1900

Tiền tệ (Curency)

-922.337.203.685.477,5808 tới 922.337.203.685.477,5807 với 4 chữ số thập phân

Kiểu N/A (blank)

Ô không chứa dữ liệu

Kiểm tra kiểu dữ liệu với hàm TYPE: =TYPE(value)

Trong đó, value là giá trị (hoặc ô dữ liệu) cần kiểm tra kiểu. Hàm TYPE trả về kết quả là một số tương ứng với kiểu của dữ liệu được kiểm tra. Cụ thể như sau: - 1 cho các kiểu số;

- 2 cho kiểu chuỗi; - 4 cho kiểu logic (TRUE or FALSE);

- 16 cho lỗi dữ liệu.

Kiểu dữ liệu là tập hợp của những dữ liệu có cùng những thuộc tính được định nghĩa trước. Kiểu dữ liệu được Excel tự động xác định phụ thuộc ký tự đầu tiên gõ vào hoặc người dùng định dạng.

Microsoft Excel 47

Các hàm toán học và logic

Nhóm hàm logic

Là loại hàm trả về một trong 2 giá trị TRUE (đúng) hoặc FALSE (sai). Các hàm logic cho phép kết hợp các biểu thức logic cơ bản (các phép so sánh) thành một biểu thức logic phức tạp hơn. Kết quả của hàm logic thường dùng làm đối số trong các hàm có sử dụng điều kiện (SUMIF, COUNTIF, v.v…).

Hàm AND Trả về kết quả TRUE nếu tất cả điều kiện đều TRUE, trả về FALSE nếu một trong các điều kiện là FALSE.

Cú pháp: AND(logical_1,logical_2,...) Trong đó, logical_1, logical_2,... là các đều kiện cần kiểm tra. Các điều kiện này có thể là biểu thức, vùng tham chiếu hoặc mảng giá trị. Các điều kiện phải có giá trị TRUE hoặc FALSE.

Nếu 1 trong các điều kiện có giá trị không phải Logic, hàm AND trả về lỗi #VALUE!

=AND(A2>9,A3>9)

Nếu ô A2 và A3 cùng lớn hơn 9 thì hàm trả về giá trị TRUE. Ngược lại, chỉ cần A2<=9 hoặc A3<=9 thì hàm trả về giá trị FALSE

Microsoft Excel 48

Các hàm toán học và logic

Nhóm hàm logic

Hàm OR

Trả về TRUE nếu một trong các điều kiện là TRUE. Trả về FALSE nếu tất cả các điều kiện là FALSE. Cú pháp: OR(logical_1,logical_2,...) Trong đó, logical_1, logical_2,… là các điều kiện cần kiểm tra. Các điều kiện này có thể là biểu thức, vùng tham chiếu hoặc mảng

giá trị. Các điều kiện phải có giá trị là TRUE hoặc FALSE.

Nếu 1 trong các điều kiện có giá trị không phải Logic, hàm AND trả về lỗi #VALUE!

=OR(A2>9,A3>9)

Nếu giá trị ở ô A2 hoặc A3 lớn hơn 9 thì hàm trả về giá trị TRUE. Trong trường hợp, cả A2<=9 và A3<=9 thì hàm trả về giá trị FALSE

Hàm NOT Trả về giá trị phủ định của đối số.

Cú pháp: NOT (logical) Trong đó, logical là một biểu thức logic hay một giá trị có giá trị TRUE hoặc FALSE.

Microsoft Excel 49

Các hàm toán học và logic

Ví dụ tổng hợp với các hàm logic

• B1. Chọn ô D3, nhập =A3>B3 • B2. Chọn ô E3, nhập =B3>C3 • B3. Chọn ô F3, nhập =AND(D3;E3) • B4. Chọn ô G3, nhập =OR(D3;E3) • B5. Chọn ô H3, nhập =AND(D3;G3) hoặc =AND(D3;OR(D3;E3)) • Dùng tổ hợp phím Ctrl + D hoặc copy công thức tới các ô còn lại trong cột

Microsoft Excel 50

Các hàm toán học và logic

Hàm điều kiện IF

Mô tả

logical_test (Bắt buộc)

Điều kiện muốn kiểm tra.

Hàm IF là một trong những hàm được sử dụng rất rộng rãi trong Excel, cho phép một ô nhận giá trị phụ thuộc vào các điều kiện quy định trong đối số. Tên đối số

Hàm IF Trả về giá trị phụ thuộc vào một biểu

value_if_true (Bắt buộc)

Giá trị muốn trả về nếu kết quả của logical_test là ĐÚNG.

value_if_false (Tùy chọn)

thức logic. Cú pháp: IF(logical_test,value_if_true,value_if_f

Giá trị muốn trả về nếu kết quả của logical_test là SAI.

Sự cố

Chuyện gì không ổn

alse) Trong đó:

O (không) trong ô

logical_test: là bất kỳ giá trị hoặc biểu thức logic nào có thể nhận giá trị

Không có đối số nào cho đối sốvalue_if_true hoặc value_if_False. Để thấy giá trị trả về đúng, thêm văn bản đối số vào hai đối số đó, hoặc thêm ĐÚNG hoặc SAI vào đối số.

Điều này thường có nghĩa là công thức đó viết sai chính tả.

#NAME? Trong ô

TRUE hoặc FALSE. Đối số này có thể sử dụng bất kỳ toán tử tính toán so sánh nào hoặc có thể sử dụng các

hàm logic ở trên. value_if_true: là giá trị được trả về

Cách thực hành

Tại sao

nếu logical_test có giá trị TRUE. value_if_false: là giá trị được trả về

Bằng cách dùng một hàm IF trong một hàm IF khác.

Tối đa 64 hàm IF có thể được lồng vào nhau cho đối số value_if_true vàvalue_if_false để tạo thêm nhiều kiểm tra phức tạp.

nếu logical_test có giá trị FALSE.

Sử dụng IF với mảng

Nếu bất kỳ đối số nào của hàm IF là mảng, thì mọi thành phần của mảng sẽ được đánh giá khi thực hiện câu lệnh IF.

Microsoft Excel 51

Các hàm toán học và logic

Ví dụ đơn giản với hàm If Giả sử có bảng điểm thi tốt nghiệp như sau. Nếu điểm trung bình trên 5.0 thì tính là đậu, nếu dưới 5.0 thì

tính là trượt. B1. Chọn ô D4, nhập công thức =IF(C4>=5; “ĐẬU”; “TRƯỢT”) B2. Ấn tổ hợp Ctrl + D hoặc copy công thức sang các ô còn

lại trong cột

Giả sử có bảng thống kê chi phí như sau.

Nếu chi phí thực tế lớn hơn chi phí dự toán thì ghi “Vượt dự toán”, nếu không thì ghi OK. B1. Chọn ô C2, nhập công thức =IF(A2>B2;"Vượt dự

toán";"OK") B2. Ấn tổ hợp Ctrl + D hoặc copy công thức sang các ô còn

lại trong cột

Microsoft Excel 52

Các hàm toán học và logic

Ví dụ phức tạp với nhiều hàm IF lồng nhau (tự nghiên cứu) Cần điền số liệu cho cột “Thưởng theo ngày công” trong bảng kê ở bên. Theo quy định của công ty, nếu số ngày công >= 27 thì được hưởng 500000. Nếu số ngày công < 27 và >= 25 được thưởng 300000. Nếu số ngày công <25 và >=20 thì hưởng 100000. Còn lại không được thưởng.

Microsoft Excel 53

Các hàm toán học và logic

Các hàm toán học

Hàm ABS: Lấy trị tuyệt đối của một số. Cú pháp ABS(number) - number: số cần lấy trị tuyệt đối.

Hàm MOD: Hàm trả về giá trị là phần dư của phép chia a/b. Cú pháp MOD(number,divisor) - number: số bị chia - divisor: số chia

Hàm INT: Làm tròn một số về số nguyên gần nhất. Cú pháp INT(number) - number: số cần làm tròn

Cho bảng số liệu như hình bên. Dùng các hàm tương ứng để tính giá trị cho các ô để trống của mỗi cột. Đây là một bài tập để làm quen với các hàm toán học cơ bản của Excel. Ngoài một số hàm đã trình bày ở phần lý thuyết trên, trong bài tập này gặp thêm một số hàm mới: • SQRT(number): tính căn bậc hai của

number;

Hàm ROUND: Làm tròn một số đến một độ chính xác theo yêu cầu. Cú pháp ROUND(number,num_digits) - number: giá trị cần làm tròn. - num_digits: xác định số lượng chữ số thập phân sau dấu phẩy.

• POWER(number, power): tính lũy thừa

numberpower;

• PRODUCT(number1, number2, …): tính tích

các số.

Hàng số 3, các ô C, D, E, F, G, H lần lượt nhập vào các công thức sau: =MOD(A3;B3) =INT(A3/B3) =SQRT(A3+B3) =ROUND(A3/B3;2) =POWER(A3;4) =PRODUCT(A3;B3) Sử dụng phím tắt Ctrl+D hoặc copy công thức để điền cho các ô còn lại trong cột tương ứng.

Microsoft Excel 54

Các hàm toán học và logic

Các hàm tính tổng

Hàm SUM: Tính tổng tất cả các số trong dãy số. Cú pháp SUM(number1,number2,....) - number1, number2,...: danh sách các giá trị để lấy tổng. Exceo cho phép có 1 đến 255 số trong danh sách này.

Lưu ý • Số, giá trị logic và chữ số gõ trực tiếp vào công thức thì

được tính toán;

• Nếu một đối số là một mảng hoặc tham chiếu, thì chỉ các số

trong mảng hoặc tham chiếu được tính toán;

• Các ô trống, các giá trị logic hoặc chuỗi ký tự trong mảng

hoặc tham chiếu sẽ được bỏ qua;

• Các đối số là giá trị lỗi hoặc chuỗi ký tự không thể chuyển

đổi thành số sẽ gây ra lỗi tính toán.

Sự cố

Đã xảy ra lỗi gì

Một vài số không được thêm vào.

Nếu đối số là một phạm vi ô hoặc tham chiếu, chỉ các giá trị là số trong tham chiếu hoặc phạm vi sẽ được đếm. Ô rỗng, giá trị lô-gic như TRUE hoặc văn bản sẽ được bỏ qua.

Lỗi này thường có nghĩa là công thức viết sai chính tả .

Giá trị lỗi #NAME? xuất hiện thay vì kết quả mong đợi.

Microsoft Excel 55

Các hàm toán học và logic

Các hàm tính tổng

Hàm SUMIF là một hàm được sử dụng đặc biệt phổ biến, dùng để tính tổng giá trị (số) của các cô theo một điều kiện đặt ra.

Hàm SUMIF: Tính tổng các ô theo điều kiện.

Cú pháp SUMIF(range,criteria,sum_range)

•

range Bắt buộc. Phạm vi ô bạn muốn đánh giá theo tiêu chí. Các ô trong mỗi phạm vi phải là số hoặc tên, mảng hay tham chiếu chứa số. Giá trị trống và giá trị văn bản bị bỏ qua.

Lưu ý

• criteria Bắt buộc. Tiêu chí ở dạng số, biểu thức, tham chiếu ô, văn bản hoặc hàm xác định sẽ cộng các ô nào. Ví dụ, tiêu chí có thể được biểu thị là 32, ">32", B5, "32", "táo" hoặc TODAY().

• Hàm SUMIF trả về kết quả sai khi bạn dùng nó để khớp các chuỗi dài hơn 255 ký tự hoặc với chuỗi #VALUE!.

• Đối số sum_range không nhất thiết phải có

QUAN TRỌNG Mọi tiêu chí văn bản hoặc mọi tiêu chí bao gồm biểu tượng lô-gic hoặc toán học đều phải được đặt trong dấu ngoặc kép ("). Nếu tiêu chí ở dạng số, không cần dấu ngoặc kép.

cùng kích cỡ và hình dạng với đối số range. Các ô thực tế sẽ cộng được xác định bằng

cách dùng ô ở ngoài cùng phía trên bên trái trong đối số sum_range làm ô bắt đầu, sau đó bao gồm các ô tương ứng về kích cỡ và

• sum_range Tùy chọn. Các ô thực tế để cộng nếu bạn muốn cộng các ô không phải là các ô đã xác định trong đối số range. Nếu đối số sum_range bị bỏ qua, Excel cộng các ô được xác định trong đối số range (chính các ô đã được áp dụng tiêu chí).

hình dạng với đối số range.

• Khi các đối số range và sum_range trong hàm SUMIF không chứa cùng số ô, thì việc

Có thể dùng ký tự đại diện—dấu chấm hỏi (?) và dấu sao (*)—làm đối số criteria. Một dấu chấm hỏi khớp bất kỳ ký tự đơn nào; một dấu sao phù hợp với bất kỳ chuỗi ký tự nào. Nếu bạn muốn tìm một dấu chấm hỏi hay dấu sao thực sự, hãy gõ dấu ngã (~) trước ký tự.

tính toán có thể mất nhiều thời gian hơn.

Microsoft Excel 56

Các hàm toán học và logic

Sử dụng hàm SUMIF

Sử dụng hàm SumIF tính tổng phụ cấp cho Nhân Viên trong bảng số liệu bên:

Công thức cho ô D15:

=SUMIF(C5:C14,"Nhân Viên",D5:D14)

Ta thu được kết quả như hình dưới:

Microsoft Excel 57

Các hàm toán học và logic

Bài tập tổng hợp

Hướng dẫn: Cột I: =D3*H3; Cột J: =(G3-

A. TRỊ GIÁ = SỐ LƯỢNG * ĐƠN GIÁ

F3)*10+D3*4,25%; Cột K: =IF(I3<=2500000;5%*I3;5,75%*I3)

B. TIỀN LƯU KHO= (NGÀY XUẤT - NGÀY NHẬP) * 10 + SỐ LƯỢNG * 4,25%

Cột L: =IF(OR(E3=$E$3;E3=$E$4);D3*1500;IF( OR(E3=$E$6;E3=$E$5);1350*D3;0))

C. THUẾ = 5%* TRỊ GIÁ, VỚI TRỊ GIÁ <=2500000

Cột M: =J3+K3+L3; Cột N: =IF(LEFT(A3;1)="G";3%*M3;IF(LEFT(A3;

THUẾ =5.75% * TRỊ GIÁ, VỚI TRỊ GIÁ >2500000

1)="B";2,5%*M3;1,75%*M3))

D. CHUYÊN CHỞ = SỐ LƯỢNG *1500, đối với kho NBÈ hoặc Bchánh

Chuyên chở =Số lượng* 1350, đối với kho TĐỨC hoặc HMÔN

E. TỔNG CỘNG= TIỀN LƯU KHO+THUẾ + CHUYÊN CHỞ

F. Chèn thêm cột Tiền Giảm và tính theo tiêu chuẩn

Nếu kí tự đầu của Mã số là "G": giảm 3% của tổng cộng

Nếu kí tự đầu của Mã số là "B": giảm 2.5% của tổng cộng Microsoft Excel 58 Nếu kí tự đầu của Mã số là "N": giảm 1.75% của

tổng cộng

Xử lý dữ liệu ngày tháng và văn bản

Nhóm hàm datetime

Nhóm hàm chuỗi

Nhóm hàm thống kê

Xử lý thông tin ngày tháng/thời gian, xử lý chuỗi văn bản và thống kê là những công việc thường xuyên gặp phải. Nếu có kỹ năng tốt về xử lý dữ liệu thời gian, văn bản và thống kê sẽ giúp giảm thiểu thời gian thực hiện.

Bài này sẽ giới thiệu một số hàm xử lý thời gian thường gặp, một số hàm xử lý xâu ký tự phổ biến và những hàm thống kê quan trọng nhất mà người dùng Excel trong công việc cần biết.

Nội dung phần này:

Microsoft Excel 59

Xử lý dữ liệu ngày tháng và văn bản

Nhóm hàm thời gian

Hàm DATE

Hàm DATE: Trả về một dãy số liên tiếp biểu diễn một ngày. Nếu trước khi sử dụng hàm DATE định dạng của ô là General thì kết quả sẽ được định dạng là kiểu ngày.

• year: đối số này có thể từ 1 đến 4 ký tự. Excel biên dịch đối số năm tùy thuộc vào hệ thống ngày tháng trên máy tính. Mặc định, Excel sử dụng hệ thống ngày tháng 1900. Nếu year nằm trong khoảng (0,1899], Excel cộng thêm 1900 để tính toán. Ví dụ, DATE(108,1,2) sẽ cho kết quả January 2, 2008 (1900+108). Nếu year nằm trong khoảng [1900, 9999], thì số đó chính là năm. Nếu year < 0 hoặc year>=10,000, Excel trả về lỗi #NUM!.

• month: số nguyên biểu thị tháng. Nếu month>12 thì hàm DATE tự quy đổi 12 tháng =

Cú pháp DATE(year, month, day)

giá trị 1, do đó ngày 1/1/2008 có giá trị 39448 bởi vì nó là 39,448 ngày sau này 1/1/1900.

§ Để xem kết quả hàm ở định dạng dãy số liên tiếp, hãy vào Format - Cell. Chọn tab Number, chọn General trong mục

Category.

1 năm và cộng vào year số năm tăng lên do số tháng. Ví dụ, DATE(2008,16,2) month là 16, thì hàm chuyển đổi 16 tháng = 1 năm + 4 tháng, vậy year là 2009, month là 4. Tương tự như vậy, nếu month<1 thì hàm sẽ trừ số năm § Excel lưu trữ ngày như là một dãy số tuần tự bởi vậy nó có thể sử dụng để tính toán. Mặc định ngày 1/1/1900 tương đương và quy đổi tháng.Ví dụ, DATE(2008,-3,2) sẽ trả về ngày 2 tháng 9 năm 2007.

• day: số nguyên biểu thị ngày. Nếu số ngày lớn hơn số ngày của tháng thì hàm tự quy đổi và tăng số tháng (tương tự năm). Vì số ngày của mỗi tháng khác nhau 28,29,30 § Thông thường định dạng ngày mặc định theo kiểu US “month /day / year”. Việt Nam dùng định dạng “day / month / year”. hoặc 31 tùy thuộc vào tháng và năm, nên tùy theo chúng mà hàm hàm sẽ quy đổi cho phù hợp.

Microsoft Excel 60

Xử lý dữ liệu ngày tháng và văn bản

Nhóm hàm thời gian

Hàm TIME

Trả về số thập phân thể hiện đầy đủ về thời gian. Nếu định dạng ô là General trước khi nhập hàm thì kết quả trả về là một thời gian. Số thập phân trả về bởi hàm TIME là một giá trị nằm trong khoảng từ 0 đến 0,99999999, biểu diễn thời gian từ 0: 00: 00 (12: 00: 00 AM) đến 23: 59: 59 (11: 59: 59 P.M.).

§ hour là số từ 0 đến 32767 đại diện cho số giờ. Nếu hour>23 nó sẽ được chia cho 24,

Cú pháp TIME(hour, minute, second)

§ minute số từ 0 đến 32767 đại diện cho sô phút. Nếu minute>59 nó sẽ được chia cho

phần dư được hiểu là hour. Ví dụ TIME(24,0,0) = TIME(1,0,0)

§ second số từ 0 đến 32767 đại diện cho số giây. Nếu second>59 nó sẽ được chia cho

60, phần dư là minute.

Hàm NOW

60, phần dư là second.

Trả về ngày giờ hiện tại của hệ thống. Nếu định dạng ô là General trước khi hàm nhập công thức, kết quả trả về ở định dạng ngày tháng.

Cú pháp NOW()

61 Microsoft Excel Hàm TODAY

Trả về ngày hiện tại của hệ thống. Nếu định dạng ô là General trước khi hàm nhập

công thức, kết quả trả về ở định dạng ngày tháng.

Cú pháp TODAY()

Xử lý dữ liệu ngày tháng và văn bản

Nhóm hàm thời gian

Công thức

Kết quả

Hàm DAY

DATE(2008,5,23)

ngày 23 tháng 5 năm 2008

DAY(DATE(2008,5, 23))

23 (là giá trị ngày trong “23 tháng 5 năm 2008)

Trả về giá trị ngày (day) của một dữ liệu kiểu ngày tháng (date).

MONTH(DATE(200 8,5,23))

5 (là giá trị tháng trong “23 tháng 5 năm 2008)

YEAR(DATE(2008, 5,23))

2008 (là giá trị năm trong “23 tháng 5 năm 2008”)

Cú pháp DAY(serial_number)

Hàm MONTH

Tương tự như hàm DAY, MONTH, YEAR, Excel cũng cung cấp các hàm HOUR, MINUTE, SECOND là các hàm trả về giờ, phút, giây của một thời gian và có cách sử dụng tương tự.

serial_number là ngày tháng (date) nhập vào bằng hàm DATE hoặc là kết quả của công thức hoặc hàm khác.

Trả về giá trị tháng (month) của một dữ liệu kiểu ngày tháng (date).

Cú pháp MONTH(serial_number)

serial_number là ngày tháng (date) của tháng nhập vào bằng hàm DATE hoặc là kết quả của công thức hoặc hàm khác.

Microsoft Excel Hàm YEAR

Trả về giá trị năm (year) của một

dữ liệu kiểu ngày tháng (date).

Cú pháp YEAR(serial_number)

serial_number là ngày tháng của

năm được nhập vào bằng hàm

DATE hoặc là kết quả của công

thức hoặc hàm khác.

Xử lý dữ liệu ngày tháng và văn bản

Nhóm hàm thời gian

Sử dụng các hàm thời gian

Để điền giá trị vào các cột B, C, D, E cần dùng các hàm DAY, MONTH, YEAR, WEEKDAY tương ứng với tham số là giá trị serial ở cột A. Để điền giá trị vào cột F cần dùng hàm DATE với tham số lấy từ các cột D, C, B. Để điền vào cột B, C, D (từ dòng dố 14) cần dùng các hàm SECOND, MINUTE, HOUR với tham số là số serial ở cột A.

Về bản chất, Excel lưu trữ thông tin về ngày dưới dạng số, là số ngày tính từ thời điểm 1/1/1900. Có nghĩa là, ngày 1/1/1900 có giá trị số tương ứng (số serial) là 1, ngày 2/1/1990 có giá trị serial là 2. Việc lưu trữ ngày tháng dưới dạng số như vậy cho phép thực hiện các phép toán số học với kiểu ngày tháng. Trong ví dụ này, số serial ở cột A bằng 36500 tương đương với ngày thứ 36500 tính từ thời điểm 1/1/1990, cũng tương đương với ngày 6/12/1999 theo cách viết ngày tháng bình thường.

Đối với thời gian (giờ, phút, giấy), Excel lưu trữ dưới dạng số thực, tính bằng tỉ lệ đối với tổng thời gian của một ngày. Ví dụ, ô A12 giá trị serial bằng 0,32 tương ứng với 0,32 * 24h * 60m * 60s = 27648 giây, cũng tương đương với 460,8 phút hoặc 7,68 giờ đã qua, hoặc theo cách viết thời gian bình thường là 7h40 sáng.

Cho bảng dữ liệu như trong hình. Yêu cầu điền thông tin vào các ô trống

Microsoft Excel 63

Xử lý dữ liệu ngày tháng và văn bản

Nhóm hàm chuỗi

Hàm LEFT:

Ví dụ: =LEFT(“Ngành toán tin ứng dụng”;5) sẽ cho kết quả là “Ngành”.

- num_chars không nhận giá trị âm - num_chars nếu lớn hơn độ dài của chuỗi thì sẽ trả về toàn bộ chuỗi text. - Nếu không nhập giá trị num_chars thì mặc định là 1.

Trả về một ký tự hoặc một nhóm ký tự tính từ trái sang phải của một chuỗi ký tự đã có.

Cú pháp LEFT(text,num_chars)

Ví dụ: =RIGHT(“Trường Đại học Hà Nội”;6) sẽ cho kết quả là “Hà Nội”.

- text: chuỗi ký tự ban đầu.

Hàm RIGHT:

Ví dụ: =MID(“Trường Đại học Hà Nội”;7,3) sẽ cho kết quả là “Đại”.

start_num nếu nhỏ hơn 1 hàm trả về lỗi #VALUE! num_chars nếu lớn hơn chiều dài chuỗi ban đầu text thì hàm trả về toàn bộ chuỗi. num_chars âm MID trả về lỗi #VALUE!

- num_chars: số ký tự mà bạn định trích ra từ chuỗi ban đầu.

Trả về một ký tự hoặc một nhóm ký tự tính từ phải sang trái của một chuỗi ký tự đã có.

64 Cú pháp RIGHT(text, num_chars) Microsoft Excel

- text là chuỗi ký tự ban đầu.

- num_chars là số ký tự mà

bạn định trích ra từ chuỗi ban

Hàm MID:

đầu.

Trích ra một chuỗi con từ

chuỗi ban đầu.

Cú pháp

MID(text,start_num,num_char

- text là chuỗi ký tự ban đầu.

- start_num vị trí bắt đầu trích

ra từ chuỗi ban đầu text.

- num_chars số ký tự của

chuỗi cần trích ra từ chuỗi

ban đầu text.

Xử lý dữ liệu ngày tháng và văn bản

Nhóm hàm chuỗi

Hàm LEN:

Ví dụ: =LEN(“Hà Nội”) sẽ trả về 6 =LEN(“ Hà Nội ”) sẽ trả về 8

Trả về độ dài (số ký tự, bao gồm cả khoảng trống) của một chuỗi ký tự.

Ví dụ: Giả sử có chuỗi text=“ Hà Nội là thủ đô của Việt Nam ”. = TRIM(text) sẽ trả về “Hà Nội là thủ đô của Việt Nam”

Cú pháp LEN(text)

Hàm TRIM:

Ví dụ: = UPPER(“hà nội”) sẽ trả về “HÀ NỘI”.

Ví dụ: = LOWER(“Hà Nội”) sẽ trả về “hà nội”.

- text: chuỗi ký tự cần xác định độ dài.

Chuẩn hóa xâu ký tự bằng cách xóa bỏ các khoảng trắng ở đầu, cuối và những khoảng trắng liên tục bên trong chuỗi chỉ để lại các từ và khoảng trắng đơn phân cách giữa các từ.

Cú pháp TRIM(text)

Ví dụ: =VALUE(RIGHT(“HH108”,3)) sẽ trả về số 108.

- text: chuỗi cần xóa các ký tự trắng.

Lưu ý: text là định dạng hằng số, ngày tháng, hoặc thời gian bất kỳ được Microsoft Excel công nhận. Nếu không phải là các định dạng trên sẽ trả về lỗi #VALUE!

Hàm UPPER:

65 Chuyển tất cả các ký tự trong chuỗi thành Microsoft Excel ký tự in hoa.

Cú pháp UPPER(text)

- text là chuỗi văn bản cần chuyển định

Hàm LOWER:

dạng

Chuyển tất cả các ký tự trong chuỗi thành

ký tự thường.

Cú pháp LOWER(text)

- text là chuỗi văn bản cần chuyển định

Hàm VALUE:

dạng

Chuyển đổi một xâu ký tự (chỉ chứa các

chữ số) thành dữ liệu kiểu số.

Cú pháp VALUE(text)

- text là chuỗi ký tự đại diện cho số (nằm

trong dấu nháy kép hoặc là tham chiếu).

Xử lý dữ liệu ngày tháng và văn bản

Nhóm hàm chuỗi

§ Cột C: =RIGHT(B3;1) § Cột D: =MID(B3;2;3) § Cột G:

Theo quy tắc đánh mã mặt hàng thì ký tự cuối cùng là mã loại. Cần dùng hàm RIGHT để lấy ký tự này. Đối với mã hợp đồng cần dùng hàm MID. Đối với cột G cần dùng hàm IF để xác định mức giảm giá. Cụ thể các hàm như sau:

§ Cột H: =E3*F3-G3 § Sử dụng phím tắt Ctrl + D hoặc copy công thức để điền giá trị cho các cột tương ứng.

=IF(C3="2";30/100;50/100 )*E3*F3

Microsoft Excel 66

Xử lý dữ liệu ngày tháng và văn bản

Nhóm hàm thống kê

Hàm COUNT:

Đếm số ô dữ liệu trong vùng tham chiếu.

- value1,value2,...: các vùng giá trị cần đếm số ô chứa dữ liệu (có thể có tới 255 vùng).

- Các đối số này có thể là các số, ngày

Công ty cần thống kế số lượng nhân viên có bằng tiến sỹ và thạc sỹ. § Thống kê số lượng tiến sỹ: =COUNTIF($D$2:

Cú pháp COUNT(value1,value2,...)

$D$9;"TS")

§ Thống kê số lượng thạc sỹ: =COUNTIF($D$2:

$D$9;"ThS")

Hàm COUNTIF:

tháng, địa chỉ ô, địa chỉ miền.

Đếm số ô trong một phạm vi đáp ứng yêu cầu nào đó.

Cú pháp COUNTIF(range,criteria)

- criteria: các tiêu chuẩn để Excel so sánh và

- range: là một hoặc nhiều ô để đếm, bao gồm các số, tên, mảng hoặc tham chiếu Trong công thức này, vùng $D$2: $D$9 chứa dữ liệu cần thống kê chứa các số; Chuỗi ký tự và ký tự trắng bị (dữ liệu về bằng cấp của nhân sự). Chuỗi “TS” và “ThS” là tiêu chí tìm kiếm. Excel sẽ tìm trong vùng $D$2: $D$9 những ô chứa xâu bỏ qua. “TS” và tính tổng số ô như vậy. Tương tự đối với xâu “ThS”.

đếm. 67 Microsoft Excel - Ví dụ: criteria có thể biểu diễn là 32, "32",

">32", "apples", hoặc B4.

Xử lý dữ liệu ngày tháng và văn bản

Nhóm hàm thống kê

Hàm AVERAGE:

Trả về giá trị trung bình (trung bình toán học) của các đối số.

§ Các đối số có thể là các số hoặc tên, mảng hoặc tham chiếu chứa số. § Các giá trị logic và chữ số gõ trực tiếp vào danh sách các đối số được tính. § Nếu đối số là một mảng hoặc tham chiếu chứa ký tự, giá trị logic hoặc các ô trống thì

Cú pháp AVERAGE(number1,number2,...)

§ Các đối số là các giá trị lỗi hoặc chuỗi ký tự không thể chuyển thành số gây ra lỗi.

Hàm MIN:

chúng được bỏ qua khi tính toán.

Trả về giá trị nhỏ nhất trong tập hợp các giá trị.

• number1,number2,...: danh sách các số cần tìm số nhỏ nhất, có thể có tối đa 255 số.

Cú pháp MIN(number1,number2,...)

Lưu ý: Nếu 2 số cùng thứ hạng (cùng giá trị) thì vị thứ tiếp theo được bỏ qua. Ví dụ: Có 2 giá trị có thứ hạng 3 thì sẽ không có thứ Hàm RANK: hạng 4 mà chỉ có thứ hạng số 5.

Trả về thứ hạng của một số trong một dãy số.

• number là số cần stìm thứ hạng.

• ref là mảng hoặc vùng tham chiếu đến một danh sách kiểu số. Những giá trị không

Microsoft Excel 68 Cú pháp RANK(number,ref, order)

• order là một số xác định cách xếp thứ hạng. Nếu order = 0, Excel xếp thứ hạng số

phải là số được bỏ qua.

• Nếu order khác 0, Excel xếp thứ hạng số theo danh sách tăng dần.

theo danh sách được sắp xếp giảm dần.

Xử lý dữ liệu ngày tháng và văn bản

Ví dụ sử dụng hàm thống kê

Cho bảng thanh toán lương của đơn vị như hình bên. Yêu cầu:

1. Tính cột Lương Tháng = Lương ngày*số ngày công

• Nếu số ngày công >=25: Thưởng

2. Lập công thức cho cột thưởng, với mức thưởng được ấn định như sau:

• Nếu số ngày công >=22: Thưởng

20%* Lương Tháng

• Nếu số ngày công <22: Không

10%* Lương Tháng

Hướng dẫn 1. Cột lương tháng tính theo công thức: =D3*E3 2. Cột thưởng tính theo công thức sau: =IF(E3>=25;20%*F3;IF(E3>=22;10%*F3;0)) 3. Phụ cấp chức vụ tính theo công thức; =IF(G3=$G$3;250000; IF(G3=$G$4;200000; IF(G3=$G$6;180000;150000))) 4. Thực lãnh tính theo công thức: =F3+H3+I3 5. Lương cao nhất: =MAX(J3: J12) Lương trung bình: =AVERAGE(J3: J12) Lương thấp nhất: =MIN(J3: J12) Số người đi làm hơn 22 ngày: =COUNTIF(E3: E12;">22") Số người có lương trên 1 triệu: =COUNTIF(J3: J12;">1000000")

• Nếu chức vụ là GĐ: 250,000 • PGĐ: 200,000 • TP: 180,000 • NV: 150,000

Thưởng 3. Lập công thức cho cột phụ cấp chức vụ, trong đó:

Microsoft Excel 69 4. Tính cột Thực Lãnh= Lương tháng +Thưởng + Phụ cấp chức

vụ

5. Lập công thức lấy số liệu cho

các ô tổng cộng, Lương cao nhất,

thấp nhất, trung bình

6. Lập công thức lấy số liệu cho

các người làm hơn 22 ngày, số

người lương trên 1 triệu.

Hàm dò tìm dữ liệu

Hàm VLOOKUP

Sử dụng VLOOKUP

Hàm HLOOKUP

Hàm dò tìm dữ liệu là hàm mạnh mẽ và được sử dụng rộng rãi bậc nhất trong Excel. Nếu thiếu các hàm dò tìm dữ liệu, việc xử lý dữ liệu trên nhiều bảng dữ liệu không thể thực hiện được hoặc sẽ đòi hỏi rất nhiều thời gian để hoàn thành. Khi học làm việc với Excel bắt buộc phải nắm bắt được cách sử dụng các hàm dò tìm dữ liệu.

Trong phần này sẽ giới thiệu hai hàm dò tìm dữ liệu do Excel cung cấp: HLookUp và VLookUp.

Nội dung phần này:

Microsoft Excel 70

Hàm dò tìm dữ liệu

Vấn đề

§ Nếu 2 danh sách trên dài sẽ phải

Giả sử có hai bảng danh sách như ở Hình 3.28. Yêu cầu điền tên ngạch công chức vào cột Tên ngạch. Phương pháp giải quyết thủ công nhất là nhìn vào bảng chứa mã ngạch và tên ngạch rồi điền thông tin vào cột tên ngạch. Tuy nhiên, phương pháp này có một loạt nhược điểm:

§ Nếu bảng mã ngạch có thay đổi sẽ phải quay lại danh sách bên trên sửa đổi tất cả các mã ngạch;

nhớ mã để nhập (rất khó);

§ Nếu 2 bảng danh sách đều rất lớn, việc nhập thủ công sẽ rất mất thời gian; Excel cung cấp hai hàm – VLOOKUP và HLOOKUP – giúp giải quyết bài toán trên một cách đơn giản. § Có thể nhầm lẫn khi nhập dữ liệu § Hai hàm này có cách sử dụng tương tự nhau. vào cột tên ngạch, dẫn đến sai sót § Quyết định sử dụng hàm nào phụ thuộc vào cách bố trí dữ liệu của bảng phụ (bảng lookup). § Nếu bảng lookup bố trí theo chiều dọc (như trong ví dụ trên) thì dùng hàm VLOOKUP; khi lọc dữ liệu. § Nếu bảng lookup bố trí theo chiều ngang thì dùng hàm HLOOKUP. Xuất phát từ bài toán này chúng ta thấy cần thiết phải có một công cụ để thực hiện điền tự động cho cột Tên ngạch, bằng cách lấy mã

Microsoft Excel 71

ngạch của một người đem so (dò)

ở cột đầu tiên của bảng mã, khi tìm

thấy thì sẽ lấy giá trị cùng dòng và

ở cột thứ 2.

Hàm dò tìm dữ liệu

Hàm VLOOKUP:

Tìm kiếm một giá trị trong cột đầu tiên của một bảng dữ liệu và trả về một giá trị trong cùng một dòng từ một cột khác của bảng dữ liệu. Có thể hiểu VLOOKUP là hàm dò tìm dữ liệu theo cột và trả về giá trị theo hàng.

Cú pháp

§ lookup_value là một giá trị để tìm kiếm trong cột đầu tiên của bảng dữ liệu. Nếu

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

§ table_array là 2 hoặc nhiều cột dữ liệu (bảng dữ liệu). Những giá trị trong cột đầu tiên của table_array là các giá trị được tìm kiếm bằng lookup_value. Những giá trị này có thể là chuỗi văn bản, số hoặc giá trị logic. Không phân biệt chữ hoa, chữ thường. § col_index_num là chỉ số cột trong bảng dữ liệu mà giá trị tại đó sẽ được trả về. Cột

lookup_value nhỏ hơn giá trị nhỏ nhất trong cột đầu tiên của bảng dữ liệu, VLOOKUP trả về lỗi #N/A.

Lưu ý đầu tiên trong bảng dữ liệu (table_array) có chỉ số là 1, các cột tiếp theo là 2, 3, 4, 5, § Các giá trị ở cột đầu tiên không chứa các khoảng cách ở đầu, cuối, khoảng v.v… Nếu Col_index_num<1, VLOOKUP trả về giá trị lỗi #VALUE!. Nếu cách dài và các ký tự đặt biệt khác. Trong trường hợp này VLOOKUP có thể Col_index_num > số cột của bảng dữ liệu (table_array), VLOOKUP trả về giá trị lỗi trả lại giá trị không chính xác. Ví dụ: “Hà Nội” và “ Hà Nội ” là khác nhau. § Khi tìm kiếm giá trị kiểu số (number), ngày (date) thì cột đầu tiên của vùng #REF!

dữ liệu không được chứa giá trị kiểu chuỗi (text).

§ range_lookup là một giá trị logic xác định cách tìm kiếm chính xác hay tìm kiếm tương

đối. Nếu Range_lookup là TRUE hoặc bỏ qua là tìm kiếm tương đối. Nếu Range_lookup là FALSE, hàm VLOOKUP sẽ tìm kiếm chính xác giá trị bạn cần tìm.

Microsoft Excel 72

Hàm dò tìm dữ liệu

Sử dụng hàm VLOOKUP Điền tên ngạch vào cột E, dựa vào bảng mã ngạch dùng hàm VLOOKUP.

§ B1. Thiết lập công thức tại ô

§ B2. Copy công thức (kéo) để áp dụng cho các ô còn lại.

Giải thích: Tìm giá trị D3 (lookup_value) trong cột đầu tiên (D14: D17) của bảng dữ liệu D14: E17 (table_array), nếu tìm thấy thì dừng lại và trả về giá trị tại cột thứ 2 (col_index_num) cùng dòng với nó ở trong bảng dữ liệu, hãy tìm chính xác giá trị D3 (FALSE).

Lưu ý: Vì vùng dữ liệu này là không thay đổi khi áp dụng cho công thức ở các ô trong cột Tên ngạch nên chúng ta dùng địa chỉ cố định (bấm F4). Trong công thức trên vùng dữ liệu là $D$14: $E$17.

E3 như sau: =VLOOKUP(D3,$D$14: $E$17,2,FALSE)

Microsoft Excel 73

Hàm dò tìm dữ liệu

Hàm HLOOKUP

Tìm kiếm một giá trị trong hàng đầu tiên của một bảng dữ liệu (hoặc mảng dữ liệu) và trả về một giá trị trong cùng một cột từ một dòng khác của bảng dữ liệu. Có thể hiểu HLOOKUP là hàm dò tìm dữ liệu theo hàng và trả về giá trị theo cột.

Cú pháp

HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)

Quay trở lại ví dụ trên. Giả sử bảng phụ lookup được bố trí theo chiều ngang. Trong trường hợp này không thể dùng hàm VLOOKUP mà cần dùng hàm HLOOKUP.

B1. Nhập công thức sau đây vào ô D2 (lưu ý cách lấy địa chỉ tuyệt đối cho vùng lookup để khi kéo thả công thức, địa chỉ vùng này không bị di chuyển): =HLOOKUP(C2;$H$1: $K$2;2; FALSE)

B2. Kéo công thức qua các ô còn lại.

Các biến và cách dùng tương tự như hàm VLOOKUP

Microsoft Excel 74

Hàm dò tìm dữ liệu

Sử dụng hàm tìm kiếm – bài toán Cho bảng số liệu khách hàng của khách sạn. Yêu cầu:

1. Lập công thức tính số liệu cho cột tiền ăn theo công thức: Tiền ăn = số ngày ở * đơn giá khẩu phần ăn.

Hai ký tự cuối của Mã số là Mã Phần ăn (Dùng hàm Hlookup, với trị dò là Mã Phần ăn, bảng dò là biểu giá khẩu phần ăn).

2. Thêm cột Số Tuần vào bên trái cột ĐGT. Lập công thức cho cột số tuần, biết số tuần là số ngày ở được đổi ra tuần lễ tuần lễ (không tính các ngày lẻ) (Dùng hàm Int hoặc Trunc để bỏ phần lẻ sau khi chia số ngày ở cho 7)

3. Lập công thức cho cột ĐGT (Đơn Giá Tuần) (Dùng hàm VLOOKUP với trị dò là 3 ký tự đầu bên trái của Mã số, Bảng dò là Biểu giá phòng)

Microsoft Excel 75

4. Thêm cột số ngày lẻ vào bên trái cột ĐGN. Lập công thức cho cột số

ngày lẻ biết số ngày lẻ là số ngày ở

còn lại sau khi đã đổi ra tuần (Dùng

hàm MOD lấy về số dư của phép

chia số ngày ở cho 7)

5. Lập công thức cho cột ĐGN (Đơn

Giá Ngày)

6. Chèn thêm cột tiền phòng vào bên

trái cột tổng cộng

Tính Tiền phòng = ĐGT* Số Tuần +

ĐGN * Số ngày lẻ

7. Thêm cột giảm giá vào bên trái cột

tổng cộng. Tính Cột giảm giá biết

rằng, nếu số ngày ở từ 15 ngày trở

lên thì giảm giá 5% Tiền phòng

8. Tính cột tổng cộng bằng = Tiền ăn

+ Tiền Phòng - Giảm Giá

9. Lập công thức tính doanh thu theo

từng phòng ở Bảng Thống kê (Dùng

hàm SUMIF)

Hàm dò tìm dữ liệu

Giải quyết Ø Cột tiền ăn tính theo công

Ø Cột đơn giá tuần (ĐGT) tính

thức: =HLOOKUP(RIGHT(C3;2); $G$16:$I$17;2;0)*(E3-D3)

Ø Cột đơn giá ngày (ĐGN) tính

theo công thức: =VLOOKUP(LEFT(C3;3); $B$17:$D$20;2;0)

Ø Thêm cột số tuần (cột H) và

theo công thức: =VLOOKUP(LEFT(C3;3); $B$17:$D$20;3;0)

Ø Thêm cột số ngày lẻ (cột J) và

tính theo công thức: =INT((E3-D3)/7)

Ø Thêm cột tiền phòng (cột K) và Microsoft Excel

tính theo công thức: =MOD(E3-D3;7)

Ø Thêm cột tiền giảm giá (cột L)

tính theo công thức: =G3*H3+I3*J3

và tính theo công thức:

Ø Thêm cột tổng cộng (cột M) và

=IF((E3-D3)>15;5%*K3;0)

tính theo công thức:

Ø Trong bảng thống kê dùng

=F3+K3-L3

công thức:

=SUMIF($C$3:$C$12;"L1A*";

$M$3:$M$12)

Hàm dò tìm dữ liệu

Thử sức với bài tập sau

Điền thông tin còn thiếu vào bảng thống kê sau đây theo các yêu cầu:

1. Tìm tên hàng trong kí tự đầu của mã hàng trong bảng tra 1

2. Hãng sản xuất theo hai kí tự cuối của mã hàng, nếu là "TN" thì là "Trong Nước" nếu khác thì tìm ở bảng tra 2

3. Tính đơn giá theo kí tự đầu của mã hàng trong bảng 1

4. Nếu hãng sản xuất là trong nước thì thuế bằng 0, nếu không thì dựa vào bảng tra 1 tính thuế =số lượng* đơn giá* thuế xuất

5. Thành tiền = số lượng * đơn giá - thuế

Microsoft Excel 77

7 + 8

Nội dung phần này:

Định dạng có điều kiện với công thức

Định dạng có điều kiện

Bài tập tổng hợp

Định dạng có điều kiện là một công cụ mạnh trong việc xây dựng các báo cáo. Với việc áp dụng công thức, định dạng có điều kiện sẽ phát huy được khả năng tùy biến cao của mình.

Bài này sẽ giới thiệu phương pháp định dạng có điều kiện sử dụng công thức và một số bài tập tổng hợp.

Microsoft Excel 78

Định dạng có điều kiện với công thức

Bài toán

Phòng Hành chính – Tổ chức có một bảng dữ liệu nhân sự như dưới đây. Yêu cầu định dạng sao cho những dòng chứa nhân sự có trình độ thạc sỹ và đại học có màu sắc khác để quản lý dễ theo dõi.

1. Định dạng sao cho những người có trình độ thạc sỹ được đổ màu xanh, những người

Phương án:

2. Định dạng sao cho tất cả những người có trình độ thạc sỹ hoặc đại học đều được đổ

có trình độ đại học được đổ màu vàng;

màu xanh.

Microsoft Excel 79

Định dạng có điều kiện với công thức

Giải quyết theo phương án 1

Bước 1. Chọn vùng dữ liệu cần định dạng ($A$3: $M$18) và chọn Home  Conditional Formatting  Manage Rules.

Bước 2. Trong hộp thoại Conditional Formatting Rules Manager chọn nút New Rule

Microsoft Excel 80

Định dạng có điều kiện với công thức

Bước 3. Trong hộp thoại New Formatting Rule chọn Use a formula to determine which cells to format.

Microsoft Excel 81 Ở dòng Format values where this formula is true cần nhập vào điều kiện để kiểm tra. Ở dòng này

có thể nhập bất kỳ biểu thức logic nào. Excel sẽ

chỉ định dạng những dòng nào mà điều kiện trả

về giá trị TRUE.

Nhập vào dòng điều kiện: =$J3="x" (những

người có trình độ đại học). Điều kiện này quy

định rằng, nếu cột J (chú ý cột J là tham chiếu

tuyệt đối) trong vùng lựa chọn có giá trị “x” thì

trả về giá trị TRUE, và Excel sẽ chỉ định dạng

cho những dòng mà cột J có giá trị “x”.

Ấn nút Format để mở hộp thoại chọn định dạng

Định dạng có điều kiện với công thức

Bước 4. Sau khi chọn được định dạng theo nhu cầu, ấn OK để

quay về hộp thoại New Formatting Rule. Ấn tiếp OK để đóng cửa sổ này và quay về cửa sổ Conditional Formatting Rules

Manager chọn nút New Rule. Lúc này trong hộp thoại đã xuất hiện điều kiện định dạng vừa thiết lập. Ấn nút Apply để thực hiện định dạng

Bước 5. Lặp lại các bước từ 2 đến 4, thay điều kiện bằng =$I3="x", thay màu sắc định dạng khác. Kết quả cuối cùng như dưới đây.

Microsoft Excel 82

Định dạng có điều kiện với công thức

Giải quyết theo phương án 2

Excel cũng cho phép thay đổi vùng chịu định dạng. Trong cửa sổ Conditional Formatting Rules Manager có thể chọn giá trị ở trường Applies to. Khi đó, định dạng chỉ áp dụng cho vùng được lựa chọn

Lặp lại tất cả các bước từ 1 đến 5 như đã thực hiện trong phương án 1. Thay đổi điều kiện định dạng thành =(OR($I3="x";$J3="x")). Chúng ta có thể thấy, đây là một điều kiện phức hợp bao gồm hai điều kiện con và được kết hợp bởi hàm OR.

Microsoft Excel 83

Định dạng có điều kiện với công thức

Bài tập tổng hợp

Cho bảng thông tin chi tiết mua xe máy và các bảng phụ (bảng phân khối, bảng loại xe, bảng nhãn hiệu).

Mã hóa đơn được viết theo quy tắc sau:

Ký tự đầu tiên chỉ mã loại xe (bảng loại xe);

Hai ký tự tiếp theo chỉ mã phân khối (bảng phân khối);

Ký tự cuối cùng chỉ hình thức mua (M) hay bán (B).

Yêu cầu điền thông tin vào các cột còn để trống (hãng sản xuất, phân khối, nhãn hiệu, thời gian bảo hành. Cột hết hạn cần điền “Còn hạn” hoặc “Hết hạn” theo thời gian bảo hành và ngày mua.

Microsoft Excel 84 Thực hiện thống kê trong các bảng “Thống kê số lượng xe”

và “Tình hình mua bán xe

theo loại phân khối”.

Định dạng có điều kiện với công thức

Hướng dẫn ü Điền thông tin hãng sản xuất:

ü Thông tin về phân khối:

=VLOOKUP(LEFT(B4;1); $H$28:$I$31;2;0)

ü Cột Nhãn hiệu:

=HLOOKUP(VALUE(MID(B4;2; 2));$C$27:$F$28;2;0)

ü Cột thời gian bảo hành:

=INDEX($B$35:$E$38;MATCH( C4;$A$35:$A$38;0);MATCH(D 4;$B$33:$E$33;0))

ü Cột hết hạn:

=HLOOKUP(D4;$C$28:$F$29; 2;0)

ü Bảng thống kê số lượng xe: § Mua:

=IF(NOW()>=EDATE(F4;H4);"H ết hạn";"Còn hạn")

=SUMIF($B$4:$B$23;"H*M"; $G$4:$G$23) § Bán: Microsoft Excel 85

=SUMIF($B$4:$B$23;"H*B";

ü Bảng thống kê theo loại phân

$G$4:$G$23)

khối:

=COUNTIF($D$4:$D$23;F41)

Bài tập tổng hợp về hàm

Cho bảng số liệu điện

Mã Hộ: Trong đó 2 ký tự đầu chỉ loại hộ, ký tự cuối chỉ khu vực đăng ký

1. Số trong định mức = Số mới - Số cũ, nếu (số mới - Số cũ) < Định mức, ngược lại thì lấy định mức tra trong bảng định mức

2. Nếu không vượt định mức SỐ NGOÀI ĐỊNH MỨC =0, ngược lại SỐ NGOÀI ĐỊNH MỨC = (SỐ MỚI -SỐ CŨ) - ĐỊNH MỨC

3. Tiền Trong Định Mức = Số Trong Định Mức * Đơn Giá. Giá Tra trong bảng Đơn Giá (Hàm INDEX)

4. Tiền Ngoài Định Mức = Số Ngoài Định Mức*Đơn Giá *1.5 . Giá Tra trong bảng Đơn Giá (Hàm INDEX)

5. Thành Tiền = Tiền Trong Định Mức + Tiền Ngoài Định Mức

7. Trích rút dữ liệu sang ô khác những hộ Hoặc là NN hoặc là CB

8. Tính toán bảng thông kê sau bằng hàm SumIf(), CountIf()

Microsoft Excel 86

9. Tính toán bảng thống kê sau:

Bài tập tổng hợp về hàm

Gợi ý: ü Cột Số trong định mức có thể tính theo công thức:

ü Cột Số ngoài định mức tính theo công thức:

=IF((F3-E3)

ü Cột Tiền trong định mức:

=IF((F3-E3)>INDEX($I$19:$M$22;MATCH(RIGHT(D3;1); $H$19:$H$22;0);MATCH(LEFT(D3;2);$I$18:$M$18;0));(F3-E3)- INDEX($I$19:$M$22;MATCH(RIGHT(D3;1);$H$19:$H$22;0);MATCH(LEFT(D3;2); $I$18:$M$18;0));0)

ü Cột Tiền ngoài định mức:

=G3*INDEX($C$19:$F$23;MATCH(LEFT(D3;2); $B$19:$B$23;0);MATCH(RIGHT(D3;1);$C$18:$F$18;0))

=H3*INDEX($C$19:$F$23;MATCH(LEFT(D3;2); $B$19:$B$23;0);MATCH(RIGHT(D3;1);$C$18:$F$18;0))*1,5

Microsoft Excel 87

7 + 8

Nội dung phần này:

Định dạng có điều kiện với công thức

Định dạng có điều kiện

Bài tập tổng hợp

Bài này sẽ giới thiệu phương pháp định dạng có điều kiện sử dụng công thức và một số bài tập tổng hợp.

Microsoft Excel 88

Định dạng có điều kiện với công thức

Bài toán

1. Định dạng sao cho những người có trình độ thạc sỹ được đổ màu xanh, những người

Phương án:

2. Định dạng sao cho tất cả những người có trình độ thạc sỹ hoặc đại học đều được đổ

có trình độ đại học được đổ màu vàng;

màu xanh.

Microsoft Excel 89

Định dạng có điều kiện với công thức

Giải quyết theo phương án 1

Bước 1. Chọn vùng dữ liệu cần định dạng ($A$3: $M$18) và chọn Home  Conditional Formatting  Manage Rules.

Bước 2. Trong hộp thoại Conditional Formatting Rules Manager chọn nút New Rule

Microsoft Excel 90

Định dạng có điều kiện với công thức

Bước 3. Trong hộp thoại New Formatting Rule chọn Use a formula to determine which cells to format.

Microsoft Excel 91 Ở dòng Format values where this formula is true cần nhập vào điều kiện để kiểm tra. Ở dòng này

có thể nhập bất kỳ biểu thức logic nào. Excel sẽ

chỉ định dạng những dòng nào mà điều kiện trả

về giá trị TRUE.

Nhập vào dòng điều kiện: =$J3="x" (những

người có trình độ đại học). Điều kiện này quy

định rằng, nếu cột J (chú ý cột J là tham chiếu

tuyệt đối) trong vùng lựa chọn có giá trị “x” thì

trả về giá trị TRUE, và Excel sẽ chỉ định dạng

cho những dòng mà cột J có giá trị “x”.

Ấn nút Format để mở hộp thoại chọn định dạng

Định dạng có điều kiện với công thức

Bước 4. Sau khi chọn được định dạng theo nhu cầu, ấn OK để

quay về hộp thoại New Formatting Rule. Ấn tiếp OK để đóng cửa sổ này và quay về cửa sổ Conditional Formatting Rules

Manager chọn nút New Rule. Lúc này trong hộp thoại đã xuất hiện điều kiện định dạng vừa thiết lập. Ấn nút Apply để thực hiện định dạng

Bước 5. Lặp lại các bước từ 2 đến 4, thay điều kiện bằng =$I3="x", thay màu sắc định dạng khác. Kết quả cuối cùng như dưới đây.

Microsoft Excel 92

Định dạng có điều kiện với công thức

Giải quyết theo phương án 2

Microsoft Excel 93

Định dạng có điều kiện với công thức

Bài tập tổng hợp

Cho bảng thông tin chi tiết mua xe máy và các bảng phụ (bảng phân khối, bảng loại xe, bảng nhãn hiệu).

Mã hóa đơn được viết theo quy tắc sau:

Ký tự đầu tiên chỉ mã loại xe (bảng loại xe);

Hai ký tự tiếp theo chỉ mã phân khối (bảng phân khối);

Ký tự cuối cùng chỉ hình thức mua (M) hay bán (B).

Microsoft Excel 94 Thực hiện thống kê trong các bảng “Thống kê số lượng xe”

và “Tình hình mua bán xe

theo loại phân khối”.

Định dạng có điều kiện với công thức

Hướng dẫn ü Điền thông tin hãng sản xuất:

ü Thông tin về phân khối:

=VLOOKUP(LEFT(B4;1); $H$28:$I$31;2;0)

ü Cột Nhãn hiệu:

=HLOOKUP(VALUE(MID(B4;2; 2));$C$27:$F$28;2;0)

ü Cột thời gian bảo hành:

=INDEX($B$35:$E$38;MATCH( C4;$A$35:$A$38;0);MATCH(D 4;$B$33:$E$33;0))

ü Cột hết hạn:

=HLOOKUP(D4;$C$28:$F$29; 2;0)

ü Bảng thống kê số lượng xe: § Mua:

=IF(NOW()>=EDATE(F4;H4);"H ết hạn";"Còn hạn")

=SUMIF($B$4:$B$23;"H*M"; $G$4:$G$23) § Bán: Microsoft Excel 95

=SUMIF($B$4:$B$23;"H*B";

ü Bảng thống kê theo loại phân

$G$4:$G$23)

khối:

=COUNTIF($D$4:$D$23;F41)

Bài tập tổng hợp về hàm

Cho bảng số liệu điện

Mã Hộ: Trong đó 2 ký tự đầu chỉ loại hộ, ký tự cuối chỉ khu vực đăng ký

1. Số trong định mức = Số mới - Số cũ, nếu (số mới - Số cũ) < Định mức, ngược lại thì lấy định mức tra trong bảng định mức

2. Nếu không vượt định mức SỐ NGOÀI ĐỊNH MỨC =0, ngược lại SỐ NGOÀI ĐỊNH MỨC = (SỐ MỚI -SỐ CŨ) - ĐỊNH MỨC

3. Tiền Trong Định Mức = Số Trong Định Mức * Đơn Giá. Giá Tra trong bảng Đơn Giá (Hàm INDEX)

4. Tiền Ngoài Định Mức = Số Ngoài Định Mức*Đơn Giá *1.5 . Giá Tra trong bảng Đơn Giá (Hàm INDEX)

5. Thành Tiền = Tiền Trong Định Mức + Tiền Ngoài Định Mức

7. Trích rút dữ liệu sang ô khác những hộ Hoặc là NN hoặc là CB

8. Tính toán bảng thông kê sau bằng hàm SumIf(), CountIf()

Microsoft Excel 96

9. Tính toán bảng thống kê sau:

Bài tập tổng hợp về hàm

Gợi ý: ü Cột Số trong định mức có thể tính theo công thức:

ü Cột Số ngoài định mức tính theo công thức:

=IF((F3-E3)

ü Cột Tiền trong định mức:

=IF((F3-E3)>INDEX($I$19:$M$22;MATCH(RIGHT(D3;1); $H$19:$H$22;0);MATCH(LEFT(D3;2);$I$18:$M$18;0));(F3-E3)- INDEX($I$19:$M$22;MATCH(RIGHT(D3;1);$H$19:$H$22;0);MATCH(LEFT(D3;2); $I$18:$M$18;0));0)

ü Cột Tiền ngoài định mức:

=G3*INDEX($C$19:$F$23;MATCH(LEFT(D3;2); $B$19:$B$23;0);MATCH(RIGHT(D3;1);$C$18:$F$18;0))

=H3*INDEX($C$19:$F$23;MATCH(LEFT(D3;2); $B$19:$B$23;0);MATCH(RIGHT(D3;1);$C$18:$F$18;0))*1,5

Microsoft Excel 97

9 + 10

Nội dung phần này:

Tạo báo cáo với Pivotable và Biểu đồ

Tạo biểu đồ

Pivottable reports

Lọc các dữ liệu trong một pivottable

Tính toán dữ liệu trong những báo cáo pivottable

Excel còn hỗ trợ người dùng vẽ các biểu đồ thể hiện các dữ liệu nhằm truyền đạt thông tin đến người sử dụng, người xem một cách sinh động và dễ hiểu hơn.

Bài này sẽ hướng dẫn chi tiết về cách sử dụng pivot table trong excel, pivot table là một tiện ích tự động của excel nhằm giúp người sử dụng thực hiện nhanh cách thao tác như trích lọc dữ liệu, tạo báo cáo, thống kê…. Có thể nói một cách khác pivot table là một công cụ giúp cho chúng Microsoft Excel ta phân tích dữ liệu theo một yêu cầu cụ thể nào đó.

1. Tạo biểu đồ Cách tạo biểu đồ trong Excel

1. Chọn dữ liệu mà bạn muốn tạo biểu đồ, bao gồm các cột tiêu đề.

2. Sau đó, click vào tab Insert, và trong nhóm Charts, click vào nút Column. Chọn một loại biểu đồ phù hợp

3. Sau khi bạn click vào Column, bạn sẽ thấy một số loại biểu đồ cột để lựa chọn

Tạo một biểu đồ bằng cách nhấp chuột vào một trong các loại biểu đồ nhóm Charts, trên tab Insert

1. Tạo biểu đồ

Tìm hiểu về biểu đồ

Nếu bạn muốn thay đổi các loại biểu đồ sau khi bạn tạo biểu đồ của bạn, nhấp chuột vào bên trong các biểu đồ.

Data Table

Khi bạn tạo một biểu đồ, Chart Tools xuất hiện trên Ribbon, trong đó bao gồm các tab Design, Layout và tab Format.

Trên tab Design, dưới Chart Tools, trong nhóm Type, bấm vào Change Chart Type và chọn một loại biểu đồ khác.

Y-Axis

Legend

X-Axis

Microsoft Excel 100

1. Tạo biểu đồ

Sau khi tạo xong một biểu đồ, bạn có thể thay đổi kiểu biểu đồ, thay đổi chiều dữ liệu, thay đổi vùng dữ liệu và thay đổi các thông số khác của biểu đồ.

Sử dụng Chart Tool – tab ngữ cảnh Design để thay đổi

Microsoft Excel 101

1. Tạo biểu đồ Tiêu đề biểu đồ mô tả thông tin về biểu đồ của bạn

Để thêm tiêu đề biểu đồ một cách nhanh chóng bằng cách click chuột vào biểu đồ, sau đó đến nhóm Chart Layouts trên tab Design, click vào nút More để xem tất cả các layout (trình bày).

Mỗi sự lựa chọn hiện thị mỗi layout khác nhau có thể làm thay đổi những yếu tố của biểu đồ. Trong biểu đồ trên:

1. Tiêu đề cho biểu đồ này là Northwind Traders Tea, tên của một sản phẩm.

2. Tiêu đề cho các trục dọc bên trái là Cases Sold.

3. Tiêu đề cho các trục ngang ở dưới cùng là First Quarter Sales.

Một cách khác để nhập tiêu đề là trên tab Layout, trong nhóm Labels, bạn có thể thêm tiêu đề bằng cách click chuột vào Chart Titles và Axis Titles.

Microsoft Excel 102

2. Báo cáo dạng Pivottable

Hãy tưởng tượng một worksheet Excel của những con số doanh thu với hàng trăm ngàn dòng dữ liệu. Những worksheet trình bày tất cả những dữ liệu về nhân viên kinh doanh ở hai quốc gia và họ bán bao nhiêu hàng hóa trong ngày, có rất nhiều dữ liệu để giải quyết theo từng hàng chia thành nhiều cột khác.

Làm thế nào bạn có thể có được thông tin trong worksheet? Làm thế nào có thể hiểu về tất cả những dữ liệu này? Tính tổng thể thì ai được bán được nhiều nhất?

Ai bán nhiều nhất theo mỗi quý hay theo mỗi năm? Quốc gia nào có doanh số bán hàng cao nhất? Bạn có thể có câu trả lời bằng các báo cáo PivotTable.

Một báo cáo PivotTable chuyển tất cả dữ liệu thành các báo cáo ngắn gọn cho bạn biết chính xác những gì bạn cần phải biết.

Microsoft Excel 103

2. Báo cáo dạng Pivottable Dưới đây là một bài tập thực tế giúp chúng ta hiểu rõ hơn về cách sử dụng pivot table trong excelđể tiện cho việc thực hành pivot table.

Một dòng thể hiện thông tin bán một sản phẩm, ví dụ Banana (chuối), sản phẩm banana thuộc một loại hàng hóa nhất định đó là vegettable (đồ rau củ quả), có một số tiền bán được nhất định (amount) cho đơn hàng này và bán vào ngày (date) ở một đất nước (country).

Dữ liệu trên thể hiện danh sách chi tiết bán hàng của một công ty bán về sản phẩm hàng rau củ quả, thông tin chi tiết của đơn hàng được mô tả ngắn ngọn như sau:

Microsoft Excel 104

2. Báo cáo dạng Pivottable

q Bài tập thực hành số 1 về pivot table sẽ tiến hành chèn pivot table vào một sheet

khác từ dữ liệu có sẵn.

Khi các dữ liệu đã có, đặt con trỏ bất cứ nơi nào trong phần dữ liệu bao gồm tất cả các dữ liệu worksheet trong báo cáo hoặc chọn dữ liệu bạn muốn sử dụng trong các báo cáo, sau đó trên tab Insert, trong nhóm Tables, bấm vào PivotTable và sau đó nhấp vào PivotTable lần nữa

Hộp thoại Create PivotTable sẽ mở ra, bạn nhấn chọn OK để thiết lập tạo một pivot table theo chế độ mặc định của excel.

Microsoft Excel 105 Chọn Select a table or range, hộp Table/Range cho thấy phạm vi của các dữ liệu đã

được chọn, chọn New Worksheet hoặc Existing Worksheet, nếu bạn không muốn báo

cáo được đặt trong một worksheet mới.

2. Báo cáo dạng Pivottable

Bảng pivottable hiện ra.

PivotTable Field List. Danh sách này hiển thị các cột tiêu đề từ dữ liệu nguồn.

Kéo cột Product vào vùng Row Labels

Kéo cột Amount vào vùng Values area

Kéo cột Country vào vùng Report

Đầu tiên để tính tổng số tiền bán được của mỗi một sản phẩm, thì bạn làm theo các bước

Kết quả sau khi hoàn thành các bước trên.

Microsoft Excel 106

2. Báo cáo dạng Pivottable

Thay đổi cách tính toán theo yêu cầu ở tab Summarize Value by

Đếm số lượng hóa đơn có chứa theo mỗi sản phẩm

Nhấn phải chuột chọn vào giá trị đang được tính tổng Sum of Amout chọn Value Field Setting

Ngoài chức năng là tính tổng của một dòng theo một nhóm nào đó, như kết quả ở trên là tính tổng số tiền theo mỗi một sản phẩm, thì bạn có thể tùy chỉnh thành đếm số lượng hóa đơn có bán sản phẩm đó như bên dưới.

Sau đó nhấn OK, kết quả hiện ra bên dưới sản phẩm Apple (táo) có 16 hóa đơn trên tổng số 28 hóa đơn

Microsoft Excel 107

3. Lọc các dữ liệu trong một pivottable q Lọc để xem ra một trong nhiều sản phẩm

Để lọc thông tin bạn bỏ mục Select All, sau đó chọn Road Bikes cuối cùng nhấp Ok, báo cáo PivotTable sẽ cho thấy dữ liệu của phần road bikes. Các dữ liệu khác sẽ không thay đổi, nhưng chúng không xuất hiện. Để lọc các báo cáo, bấm vào mũi tên bên cạnh Row Labels, chọn mục Road bikes (xe đạp) trong khu vực của báo cáo.

Microsoft Excel 108 Khi bạn bấm vào mũi tên đó, một trình đơn xuất hiện với hộp Select field để bạn chọn lựa và lọc thông tin.

3. Lọc các dữ liệu trong một pivottable q Tìm loại sản phẩm có doanh thu cao

q Lọc thông tin theo thời gian

Giả sử bạn muốn xem xe đạp bán như thế trong một khoảng thời gian xác định. Bằng cách sử dụng bộ lọc, bạn có thể xem những thông tin trong khoảng thời gian bạn thích và các thông tin khác tạm thời bị ẩn.

Giả sử bạn muốn xem loại xe đạp nào có doanh số hơn $100.000, bạn có thể sử dụng Excel để xem thông tin trên, còn các không tin khác thì tạm thời ẩn. để thực hiện việc này click vào mũi tên trên biểu tượng filter nằm sát bên hộp Row Labels, chọn Product Name, tiếp theo chỉ vào ValueFilters.

Để lọc thông tin theo từng năm chẳng hạn bạn muốn xem dữ liệu trong năm 2011 thôi thì bấm vào mũi tên bên cạnh Column Labels, sau đó click vào hộp Select All và cuối cùng chọn hộp 2011. Excel ẩn các số liệu của 2009 và 2010, chỉ còn lại số liệu năm 2011.

Bộ lọc đọc dữ liệu và chọn những dòng có những ô phù hợp với yêu cầu của bạn, chọn Greater Than Or Equal To và chọn hộp thoại Value Filter, nhập 100,000 trong hộp rỗng. Kết quả bao gồm 13 trong tổng số 38 loại xe đạp có doanh thu hơn $100,000.

Nếu bạn muốn xem một thời gian cụ thể thì bạn hãy làm cách sau: chọn mũi tên bên cạnh Column Labels, sau đó chỉ vào Date Filters, click Between, trong hộp thoại Date Filter nhập dữ liệu ngày tháng vào chẳng hạn 11/8/2011 và 12/8/2011 trong hai hộp rỗng, thì báo cáo sẽ hiện thông tin từ ngày 8 tháng 11 năm 2011 đến ngày 8 tháng 12 năm 2011.

Microsoft Excel 109

3. Lọc các dữ liệu trong một pivottable

1. Xóa bỏ một bộ lọc trong báo cáo PivotTable bằng cách nhấp chuột vào biểu tượng bộ lọc , và sau đó nhấp vào Clear Filter From "Product Category".

2. Xoá bỏ một bộ lọc trong PivotTable Field List bằng cách di chuyển con trỏ qua biểu tượng bộ lọc bên cạnh tên field, nhấp chuột vào mũi têr xuất hiện và sau đó nhấp vào Clear Filter From "Product Category".

Hủy bỏ bộ lọc

Microsoft Excel 110

3. Lọc các dữ liệu trong một pivottable q Hủy bỏ một filter trong báo cáo PivotTable

Để loại bỏ một bộ lọc từ một field xác định, click vào biểu tượng ở bất cứ nơi nào field bộ lọc xuất hiện trong báo cáo, ngay cả trên Row Labels hoặc trên Column Labels. Sau đó click vào Clear Filter From < Field Name >. Hoặc lựa chọn các hộp kiểm tra bên cạnh (Select All) để làm cho tất cả các dữ liệu trong field nhìn thấy được.

Nếu bạn không xem thấy lệnh Clear Filter From cho field lọc:

Hãy chắc chắn rằng bạn đã chọn đúng khu vực của báo cáo để xóa bộ lọc: hoặc là hàng hoặc là cột.

Phải chọn đúng tên field trong hộp Select field. Bạn thấy rằng hộp khi bạn nhấp chuột vào biểu tượng bộ lọc, tên field trong hộp phải khớp với tên của field mà bạn xóa bộ lọc từ đó. Nếu các tên field trong hộp không đúng, chọn đúng field từ danh sách xuất hiện khi bạn bấm vào mũi tên bên cạnh hộp.

Hủy bỏ một bộ lọc trong danh sách PivotTable Field: Di chuyển con trỏ qua các biểu tượng bộ lọc bên cạnh các tên field mà bạn muốn xóa bỏ bộ lọc từ đó. Click vào mũi tên xuất hiện và sau đó nhấp vào Clear Filter From < Field Name>.

Xóa bỏ tất cả bộ lọc ngay tức khắc: Trên Ribbon, click vào tab Options dưới PivotTable Tools. Trong nhóm Actions, click vào Clear và sau đó nhấp vào Clear Filters.

Microsoft Excel 111

Bài tập thực hành 10

Bài 1: Bài thực hành về pivot table sẽ tiến hành chèn pivot table vào một sheet khác từ dữ liệu có sẵn.

Dữ liệu trên thể hiện danh sách chi tiết bán hàng của một công ty bán về sản phẩm hàng rau củ quả, thông tin chi tiết của đơn hàng được mô tả ngắn ngọn như sau: một dòng thể hiện thông tin bán một sản phẩm, ví dụ Banana (chuối), sản phẩm banana thuộc một loại hàng hóa nhất định đó là vegettable (đồ rau củ quả), có một số tiền bán được nhất định (amount) cho đơn hàng này và bán vào ngày (date) ở một đất nước (country).

Nhấn vào biểu tượng pivot table ở menu Insert

Microsoft Excel 112

Bài tập thực hành 10

Một cửa sổ mới hiện lên, bạn nhấn chọn OK để thiết lập tạo một pivot table theo chế độ mặc định của excel.

Kéo cột Product vào vùng Row Labels

Kéo cột Amount vào vùng Values area

Kéo cột Country vào vùng Report

Bảng pivottable hiện ra. Đầu tiên để tính tổng số tiền bán được của mỗi một sản phẩm, thì bạn làm theo các bước

Và đây là kết quả sau khi hoàn thành các bước trên.

Microsoft Excel 113

Bài tập thực hành 10

Đếm số lượng hóa đơn có chứa theo mỗi sản phẩm: Nhấn phải chuột chọn vào giá trị đang được tính tổng Sum of Amout chọn Value Field Setting

Thay đổi cách tính toán theo yêu cầu ở tab Summarize Value by

114 Microsoft Excel

Sau đó nhấn OK, kết quả hiện ra bên dưới sản phẩm Apple (táo) có 16 hóa đơn trên tổng

số 28 hóa đơn

Bài tập thực hành 10

Quý 1 250

Quý 2 300

Quý 3 380

Quý 4 640

Cho bảng dữ liệu sau: Tổng hợp doanh số bán hàng của 3 chi nhánh Công ty ABC năm 2014

350

280

400

560

Chi nhánh Sài Gòn

420

480

350

500

Vẽ đồ thị như sau: Chi nhánh Chi nhánh Hà N iộ Chi nhánh Đà N ngẵ

Thay đổi kiểu tô nền cho Series dữ liệu

Thêm tiêu đề cho đồ thị "Tổng hợp doanh số bán hàng của ba chi nhánh công ty ABC năm 2014".

Hiển thị giá trị cho mỗi cột

Chỉnh sửa đồ thị theo yêu cầu sau:

Xoay chiều biểu diễn của đồ thị. Gợi ý: Vào Design/Data/Switch Row/Column.

Microsoft Excel 115

In ấn và một số thủ thuật

In ấn

Một số thủ thuật

Bảo mật dữ liệu

Nội dung phần này:

Microsoft Excel 116

1. In ấn

Các chế độ hiển thị trang trong Excel

Có 3 chế độ hiển thị là Nornal View, Page Layout View và Page Break Preview.

Page Layout View

Đây là chế độ bạn sử dụng thường xuyên trong quá trình nhập liệu, tính toán,… trên bảng tính và là chế độ mặc định của Excel.

Normal View:

Là chế độ xem trước khi in, trong chế độ này bạn vẫn có thể tính toán và nhập liệu.

Page Layout View:

Hiển thị bảng tính Excel với các dấu phân trang, tại đây bạn có thể chia lại trang bằng cách kéo thả các đường chia cách trang.

Normal view

Page Break Preview

Page Break Preview:

Microsoft Excel 117

1. In ấn

Thiết lập các thông số cho trang in

Tất cả các tùy chọn thiết lập thông số trang in có tại nhóm Page Layout / nhóm Page Setup.

Chiều trang in dọc

Chiều trang in ngang

Lề trên

Khổ giấy

Lề trái

Lề phải

Lề dưới

Để vào hộp thoại Page Setup bạn vào nhóm Page Layout / đến nhóm Page Setup / nhấn vào nút

Microsoft Excel 118

1. In ấn

Thiết lập các thông số cho trang in

Chọn vùng cần in

ị ố

ể

ệ

&[Page]

Hi n th s trang tài li u

Dòng tiêu đề cần lặp lại ở các trang

ị ổ

ể

ố

ượ

&[Pages]

Hi n th t ng s trang đ

c in

ệ ạ

ể

ị

&[Date]

Hi n th ngày hi n t

ể

&[Time]

Hi n th gi

ị ờ ệ ạ i

hi n t

ị ườ

ể

ầ ủ ế

ơ ư ậ

ẫ

Hi n th đ

ng d n đ y đ đ n n i l u t p

&[Path]&[File]

ậ

tin và Tên t p tin

Tiêu đề cuối trang

Tiêu đề đầu trang

ể

ậ

ị

&[File]

Hi n th tên t p tin

ể

&[Tab]

ị Hi n th tên sheet

ự

ế

ạ

ả

ọ

Chèn hình l a ch n vào. B n ph i tìm đ n

ạ

ầ

ộ

hình c n chèn vào trong h p tho i Insert

Không có

ấ

ọ

Picture, ch n hình và nh n nút Insert.

Tiêu đề trang chẵn khác với trang lẻ

ế ậ

ố

Không có

Thi

t l p các thông c cho hình chèn vào.

Tiêu đề trang đầu khác với trang còn lại

Các mã lệnh và nút lệnh cho Header và Footer

Microsoft Excel 119

1. In ấn

Thiết lập các thông số cho trang in

Selection: Chỉ in vùng đang chọn.

Active sheet(s): Chỉ in sheet hiện hành hay các sheet đang chọn.

Entire workbook: In toàn bộ workbook

Chọn máy in

Table: Chỉ có tác dụng khi ô hiện hành đang trong một bảng, nếu chọn thì chỉ in bảng này.

Ignore print areas: Khi chọn, Excel sẽ bỏ qua tất cả các thiết lập vùng in đã thực hiện.

Mỗi trang in bao nhiêu bản

Thiết lập thông số hộp thoại Print: Bạn chọn nút Office Button sau đó chọn Print ( hoặc bấm tổ hợp phím )

Microsoft Excel 120

2. Một số thủ thuật

Giấu bảng tính Excel

Bấm chuột phải vào sheet

Chọn Hide

Chon sheet1

Ẩn sheet:

Bấm chuột phải vào sheet bất kỳ

Bấm chuột phải chọn UnHide

Chọn sheet cần hiển thị

Cách 2:

Hiển thị sheet ẩn:

1. Trong bảng tính hiện tại, bấm Alt + F11

2. Chọn Project - VBA Project

3. Chọn Microsoft Excel Objects

4. Chọn bảng tính muốn giấu, bấm phím F4

Tuy chọn Visible: Ẩn sheet: 2-xlSheetVeryHidden, Hiển thị sheet: 1-xlSheetVisible

Chọn thuộc tính ẩn/Hiện sheet

Ẩn/Hiện sheet nâng cao

Microsoft Excel 121

2. Một số thủ thuật Khóa các ô chứa công thức

Theo mặc định, tất cả các ô trong bảng tính đều được khóa (locked), tuy nhiên, nó chẳng có tác dụng gì trừ phi bạn áp dụng lệnh Protect bảng tính. Đây là cách dễ nhất để áp dụng lệnh Protect cho bảng tính, nhưng chỉ những ô chứa công thức thì mới bị khóa, và được bảo vệ:

Chọn toàn bộ bảng tính, bằng cách nhấn Ctrl+A, hoặc nhấn vào cái ô vuông nằm ở giao điểm của cột A và hàng 1. Rồi nhấn nút phải chuột và chọn Format Cells, rồi trong tab Protection, bỏ đánh dấu ở tùy chọn Locked, rồi nhấn OK:

Microsoft Excel 122

2. Một số thủ thuật

Find & Select Go To ➝ ➝

Sau đó, bạn mở lại hộp thoại Format Cells đã nói ở trên, nhưng lần này thì bạn đánh dấu vào tùy chọn Locked, và nếu bạn thích ẩn luôn công thức (không cho thấy) thì đánh dấu vào tùy chọn Hidden, nhấn OK.

Sau đó, bạn chọn đại một ô nào đó, chọn Home Special [E2003: Edit | Go To… | Special]; hoặc nhấn Ctrl+G hay F5 rồi nhấn vào nút Special.... Hộp thoại sau đây sẽ mở ra:

Việc cuối cùng là Protect bảng tính: Chọn Home trên Ribbon, nhấn vào Format trong nhóm Cells, rồi nhấn vào Protect Sheet...; hoặc chọn Review trên Ribbon, rồi nhấn vào Protect Sheet [E2003: Tools | Protection | Protect Worksheet]:

Microsoft Excel 123

Bạn nhấn vào tùy chọn Formulas, và

nếu cần thiết thì chọn hoặc không chọn

thêm 4 ô nhỏ ở dưới, và nhấn OK.

2. Một số thủ thuật

Trong hộp thoại Protect Sheet, bỏ đánh dấu ở tùy chọn Select locked cells, chỉ cho phép Select unlocked cells (chọn những ô không khóa), và nhập vào một password, nếu cần thiết:

Microsoft Excel 124

3. Sử dụng chức năng thay thế (Replace) để gỡ bỏ các ký tự không mong muốn

Tại ô khác, nhập công thức sau: =CODE($A$1)

Chức năng thay thế (replace) trong Excel có thể giúp bạn gỡ bỏ các ký tự không mong muốn trong bảng tính, nhưng phải qua một vài bước phụ. Công thức này trả về mã của ký tự không mong muốn.

Editing Find & Select Replace, ở ➝ ➝

Chọn toàn bộ dữ liệu của bạn, chọn Home ➝ khung Find what: nhấn phim Alt và gõ số 0 kèm theo code đã đưa ra bởi công thức trên. Nếu mã số là 163 thì nhấn Alt và nhấn 0163. Ví dụ như, bạn có thể thay thế những ký tự không mong muốn bằng chuỗi rỗng tựa như nó chưa hề tồn tại. Muốn vậy bạn cần biết mã của từng ký tự mà bạn muốn gỡ bỏ. Tất cả các ký tự đều mang một mã riêng và Excel sẽ cho bạn biết nó là gì khi bạn sử dụng hàm CODE.

Bỏ trống khung Replace With và nhấn Replace all.

Hàm CODE sẽ trả về một mã số cho ký tự đầu tiên trong một chuỗi. Mã này tương đương ký tự mà máy tính của bạn đã thiết lập.

Việc làm này sẽ xóa bỏ tất cả những ký tự không mong muốn rất nhanh qua việc dò tìm mã ký tự.

Lặp lại các bước ở trên cho mỗi ký tự không mong muốn tiếp theo.

Để thực hiện điều này, chọn một trong các ô có chứa những ký tự không mong muốn. Từ thanh công thức, bôi đen ký tự và sao chép ký tự đó. Tiếp theo chọn ô trống bất kỳ (A1 chẳng hạn) và dán ký tự đó vào ô đã chọn (A1).

Microsoft Excel 125

Bài tập thực hành tổng hợp

Chủ hộ

Tiền điện

Tổng số tiền

Hình thức sử dụng

Chỉ số trước

Chỉ số sau

Điện tiêu thụ

Tiền công tơ

Vân

Sản xuất

500

Bình

Kinh doanh

200

Khánh

Tiêu dùng

150

Doanh

Sản xuất

600

Lan

Tiêu dùng

101

Thu

Tiêu dùng

Quảng

Kinh doanh

300

Tiền

Loại Sản xuất Kinh doanh Tiêu dùng

Số hộ ? ? ?

? ? ?

Dùng công thức điền giá trị vào các ô có dấu? trong bảng sau (chú ý trình bày đẹp):

Hộ Sản xuất: 20000 đ/tháng

Hộ Kinh doanh: 10000 đ/tháng

Hộ Tiêu dùng: 5000 đ/tháng

1/ Tiền công tơ tính như sau: 126 Microsoft Excel

Hộ Sản xuất: 2000 đ/số

Hộ Kinh doanh: 800 đ/số

Hộ Tiêu dùng: 500 đ/số

2/ Giá điện:

3/ Tổng số tiền = Tiền điện+Tiền công tơ

Bài tập thực hành tổng hợp

Tuổi

Toán

Tin

Trung bình

Xếp thứ

Xếp loại

Học bổng

Tên

Ngày sinh

Hùng

1/30/1978

Nam

Bình

8/21/1974

Nữ

Vân

Nữ

8/21/1975

Bình

8/16/1977

Nam

Doanh

12/5/1976

Nam

Loan

9/18/1977

Nữ

Anh

4/23/1968

Nam

Thu

5/1/1973

Nữ

Khánh

2/26/1971

Nam

Loại

Số lượng

? Điểm TB

Ngân

5/12/1975

Nữ

Kém TB Khá Giỏi

? ? ? ?

Dùng công thức điền giá trị vào các ô có dấu? trong bảng sau (chú ý trình bày đẹp):

Xuất sắc

1/ Tuổi tính đến ngày Hôm nay (đủ 365 ngày mới tính là 1 tuổi)

2/ Điểm trung bình tính như sau: Toán hệ số 2, Tin hệ số 3

3/ Xếp loại và Học bổng theo thang điểm sau:

Microsoft Excel 127

Bài giảng Microsoft excel - Khoa Công nghệ thông tin

Bài giảng Microsoft excel được biên soạn ngắn gọn, dễ hiểu giúp bạn nắm bắt được các hàm cơ bản trong excel. Từ đó, thực hành hiệu quả và thành thạo hơn, ôn thi tốt trong kì thi cuối kì. Mời các bạn tham khảo!

TRƯỜNG ĐẠI HỌC CÔNG NGHỆ THÔNG TIN & TRUYỀN THÔNG

KHOA CÔNG NGHỆ THÔNG TIN

MICROSOFT EXCEL

Tổng quan về MS Excel

1. Làm quen với Excel Thanh công cụ Ribbon

1. Làm quen với Excel Làm việc với nhiều bảng tính

1. Làm quen với Excel Phóng to, thu nhỏ bảng tính

2. Thao tác với Workbook Mở file Excel đã có sẵn

2. Thao tác với Workbook

3. Thao tác với Worksheet

3. Thao tác với Worksheet

3. Thao tác với Worksheet

1 Double Click vào sheet muốn đổi tên và gõ tên mới

1 Double Click vào sheet muốn di chuyển và kéo sang phải hoặc sang trái

2 Dừng lại theo vị trí chỉ dẫn như mong muốn

4. Thao tác với ô và vùng

1

2

1

2

3

4

4. Thao tác với ô và vùng Chọn nhiều dòng/cột liên tục

Tiêu đề cột

Tiêu đề dòng

4. Thao tác với ô và vùng Cắt, sao chép, dán dữ liệu

1 Lựa chọn vùng dữ liệu cần cắt.

2 Click menu Home.

3 Click nút Cut (hoặc Ctrl + X).

1 Lựa chọn vùng dữ liệu cần sao chép.

2 Click menu Home.

4 Click nút Copy (hoặc Ctrl + C).

1 Lựa chọn vùng dữ liệu cần sao chép.

2 Click menu Home.

5 Chọn vùng dữ liệu cần dán dữ liệu.

6 Click nút Paste để dán dữ liệu (hoặc Ctrl + V).

4. Thao tác với ô và vùng

Di chuyển vùng dữ liệu

Xóa ô dữ liệu

5. Sử dụng phím tắt Các phím tắt thông dụng

Bài tập thực hành 1: Kỹ năng định dạng dữ liệu trong Excel Tạo một file đặt tên là Baitap_Excel_1

Bài tập thực hành 1: Kỹ năng định dạng dữ liệu trong Excel

Bài tập thực hành 1: Kỹ năng định dạng dữ liệu trong Excel

Làm việc với dữ liệu trong Excel

1. Nhập liệu Nhập dữ liệu

1 Click vào ô muốn nhập dữ liệu.

2

1

2 Gõ nội dung, kết thúc bằng ấn phím Enter.

1 Click vào ô muốn nhập dữ liệu.

2 Gõ nội dung muốn hiển thị trên dòng đầu tiên, ấn Alt + Enter để thêm dòng mới

3 Gõ nội dung dòng tiếp theo, kết thúc bằng ấn phím Enter.

1

2

Để Excel hiểu một dữ liệu dạng khác là dữ liệu dạng chữ thì nhập dấu ’ trước dữ liệu đó.

3

2

1

3

1. Nhập liệu Chèn ký hiệu đặc biệt 1 Chọn Menu Insert.

5

4

7

6

1. Nhập liệu Điền dữ liệu tự động

1

1 Nhập dữ liệu vào ô đầu tiên muốn điền vào các dòng.

2 Đưa trỏ chuột vào phía dưới cùng bên phải ô đầu

2

1 Nhập dữ liệu vào ô đầu tiên.

3

1

2 Nhập dữ liệu vào ô thứ 2.

2

3 Chọn vùng dữ liệu gồm ô thứ nhất và ô thứ 2.

4 Đưa trỏ chuột vào phía dưới cùng bên phải của vùng

4

1. Nhập liệu Các kiểu dữ liệu