Bài giảng Tin học đại cương: Chương 3

07/06/2021

(cid:151) Hàm toán học: SUM,

PRODUCT, ROUND, INT, MOD

(cid:151) Hàm thống kê: MAX, MIN,

AVERAGE, COUNT

(cid:151) Hàm xử lý chuỗi: LEFT, RIGHT,

Nội dung Chương III (cid:151) Cửa sổ giao diện MS Excel (cid:151) Quản lý tập tin Excel (cid:151) Cấu trúc cơ bản của bảng tính (cid:151) Các thao tác trên bảng tính (cid:151) Các kiểu dữ liệu

MID, VALUE, LEN

(cid:151) Định dạng ngày tháng

BẢNG TÍNH ĐIỆN TỬ MICROSOFT EXCEL

(cid:151) Định dạng kiểu dữ liệu (cid:151) Công thức, sao chép công thức

(cid:151) Địa chỉ tương đối (cid:151) Địa chỉ tuyệt đối (cid:151) Địa chỉ hỗn hợp

(cid:151) Hàm logic & điều kiện: AND, OR, IF, IF lồng nhau, IF + AND/OR, COUNTIF, SUMIF (cid:151) Hàm tìm kiếm: VLOOKUP, HLOOKUP, MATCH, INDEX (cid:151) Hàm ngày tháng, thời gian

(cid:151) Định dạng, in ấn bảng tính (cid:151) Hàm trên bảng tính

(cid:151) Biểu đồ, đồ thị (cid:151) CSDL trên bảng tính: Sắp xếp, Lọc 2

Giới thiệu bảng tính

(cid:151) Microsoft Excel là phần mềm bảng tính điện tử trong bộ sản phẩm Microsoft Office của hãng Microsoft (Mỹ)

(cid:151) Được sử dụng rộng rãi trong quản lý và xử lý số liệu, công

˜ Lập công thức, hàm tính toán số liệu

(cid:151) Các tính năng cơ bản:

(cid:151) Tổ chức lưu trữ và xử lý thông tin dưới dạng bảng (cid:151) Tự động tính toán theo biểu thức được xây dựng (cid:151) Thư viện các hàm nhằm hỗ trợ tính toán chuyên biệt (cid:151) Sắp xếp, trích lọc dữ liệu (cid:151) Vẽ biểu đồ thống kê số liệu (cid:151) …

tác kế toán, tài chính, thương mại, v.v…

07/06/2021

Giới thiệu bảng tính

˜ Vẽ biểu đồ thống kê số liệu

(cid:151) Vào menu Start ﬁ All Programs ﬁ Microsoft Office ﬁ

Khởi động và thoát Microsoft Excel (cid:151) Khởi động

(cid:151) Thoát:

(cid:151) Cách 1: Vào tab File ﬁ Exit. (cid:151) Cách 2: Nhấp chuột vào biểu tượng Close trên cửa sổ

Microsoft Office Excel 2010.

(cid:151) Cách 3: Dùng tổ hợp phím Alt + F4

Thanh tiêu đề (Title bar)

giao diện chương trình

Cửa sổ giao diện Microsoft Excel

Thanh Ribbon

07/06/2021

Cửa sổ giao diện Microsoft Excel

Hệ thống thanh Ribbon

(cid:151) Tab File: Các lệnh quản lý tập tin văn bản

(cid:151) Tab Home: Định dạng ký tự (Font), định dạng canh lề

Thanh công thức (Formular Bar)

Thanh Sheet tabs (thanh chọn bảng tính)

(Alignment), định dạng kiểu dữ liệu trong ô, hiệu chỉnh (di chuyển, sao chép), công cụ tìm kiếm và thay thế (Find, Replace)

Hệ thống thanh Ribbon

(cid:151) Tab Insert: Chèn các đối tượng vào bảng tính như hình

(cid:151) Tab Data: Gồm các công cụ sắp xếp, trích lọc dữ liệu (Sort

& Filter) trong bảng tính

ảnh (Picture), Clip Art, hình vẽ (Shapes), biểu đồ (Charts), lưu đồ (SmartArt), chữ nghệ thuật (Word Art), ký hiệu/công thức toán học (Equation), biểu tượng đặc biệt (Symbol)…

07/06/2021

Quản lý tập tin Excel

˜ Mở tập tin Excel có sẵn trên ổ đĩa (cid:151) Cách 1: Chọn tab File ﬁ Open... (cid:151) Cách 2: Nhấn tổ hợp phím Ctrl+O.

Xác định đường dẫn

Chọn tập tin Excel

Chọn Open

˜ Tạo tập tin bảng tính (Excel) mới (cid:151) Cách 1: Chọn tab File ﬁ New... (cid:151) Cách 2: Nhấn tổ hợp phím Ctrl+N.

Quản lý tập tin Excel

˜ Lưu tập tin Excel vào ổ đĩa (cid:151) Cách 1: Chọn tab File ﬁ Save. (cid:151) Cách 2: Nhấn tổ hợp phím Ctrl+S.

Xác định đường dẫn

Chọn nơi lưu tập tin

Nhập tên tập tin

˜ Lưu tập tin Excel với tên khác (cid:151) Chọn tab File (cid:240) Save As... để mở hộp thoại Save As. (cid:151) Xác định thư mục chứa tập tin, đặt tên khác cho tập tin

Chọn Save

(cid:240) chọn Save. (cid:151) Phím tắt F12 ˜ Đóng tập tin Excel hiện hành (cid:151) Chọn tab File (cid:240) Close. (cid:151) Tập tin bảng tính hiện hành sẽ được tắt đi, giao diện cửa sổ chương trình MS Excel vẫn còn.

07/06/2021

Cấu trúc cơ bản của bảng tính

Bảng tính (Sheet/Worksheet)

Ô (Cells) A1 ≠ 1A

Dòng/hàng (Rows)

Cột (Columns)

Cấu trúc cơ bản của bảng tính

Vùng ô A1:C1

Vùng ô A:A

Vùng ô 1:1

Vùng ô 1:2

Vùng ô A1:C3

Vùng ô A1:A3

Vùng ô A:B

07/06/2021

Các thao tác trên bảng tính

(cid:151) Chọn một Sheet: Nhấp chuột vào Sheet cần chuyển đến

(cid:151) Di chuyển ô hiện hành:

(cid:151) Đổi tên Sheet: Nhấp đúp chuột vào tên Sheet cũ trên

trên thanh chọn bảng tính

(cid:151) Các phím mũi tên (cid:239) (cid:240) æ (cid:242): Qua trái, phải, lên, xuống. (cid:151) Ctrl + Home: Trở về ô đầu tiên bảng tính (A1). (cid:151) PageDown + PageUp: Lên, xuống một trang màn hình. (cid:151) Alt + PageDown: Sang phải một trang màn hình. (cid:151) Alt + PageUp: Sang trái một trang màn hình.

(cid:151) Chèn thêm Sheet: Click chuột phải tại thanh chọn bảng

tính (cid:240) Insert (cid:240) Worksheet

(cid:151) Xóa Sheet: Click chuột phải tại Sheet trên thanh chọn

bảng tính (cid:240) Delete.

thanh chọn bảng tính rồi nhập tên mới vào.

Chọn vùng trong bảng tính

(cid:151) Cách 2: Nhấn giữ phím Shift và dùng các phím mũi tên

a. Chọn cả bảng tính: Có 2 cách thực hiện (cid:151) Cách 1: Nhấn tổ hợp phím Ctrl + A (cid:151) Cách 2: Nhấp chuột tại ô giao giữa tiêu đề cột và tiêu đề a. Chọn cả bảng tính: Có 2 cách thực hiện b. Chọn một cột/dòng: Có 2 cách thực hiện (cid:151) Cách 1: Nhấp chuột tại trên ô tiêu đề cột, hoặc phía trái dòng. tiêu đề dòng.

(cid:239) (cid:240) æ (cid:242).

07/06/2021

Chọn vùng trong bảng tính

Nhập và chỉnh sửa dữ liệu

(cid:151) Nhập dữ liệu:

(cid:151) Đặt con trỏ ở ô đầu tiên hoặc ô cuối cùng của vùng

c. Chọn một vùng: Có 2 cách (cid:151) Cách 1: (Dùng bàn phím)

(cid:151) Nhấn giữ phím Shift và dùng phím mũi tên (cid:239) (cid:240) æ (cid:242)

định chọn.

(cid:151) Nhấp chuột tại ô muốn nhập dữ liệu (cid:151) Nhập dữ liệu bằng bàn phím (cid:151) Nhấn phím ESC để hủy việc nhập (cid:151) Nhấn tổ hợp phím Alt + Enter để xuống dòng trong ô (cid:151) Nhấn phím Enter để hoàn tất

(cid:151) Cách 2: (Dùng chuột)

(cid:151) Chuyển con trỏ ở ô đầu tiên hoặc ô cuối cùng của vùng

để xác định vùng chọn.

(cid:151) Rê chuột để xác định vùng chọn.

định chọn.

Nhập và chỉnh sửa dữ liệu

Phím tắt trong Microsoft Excel

(cid:151) Chỉnh sửa dữ liệu:

(cid:151) Home: Di chuyển đến ô đầu của một hàng trong một

Điều hướng trong bảng tính (cid:151) Page Down / Page Up: Di chuyển xuống cuối bảng tính/ lên đầu của bảng tính.

(cid:151) Ctrl + Home: Di chuyển đến ô đầu tiên của một bảng tính. (cid:151) Ctrl + End: Di chuyển đến ô cuối cùng chứa nội dung trên

bảng tính.

(cid:151) Nhấp chuột tại ô muốn chỉnh sửa dữ liệu (cid:151) Nhấn phím F2 và bắt đầu chỉnh sửa (cid:151) Nhấn phím ESC để hủy việc chỉnh sửa (cid:151) Nhấn phím Enter để hoàn tất Hoặc: (cid:151) Nhấp đúp chuột tại ô muốn chỉnh sửa dữ liệu (cid:151) Chỉnh sửa và nhấn phím Enter để hoàn tất

một bảng tính.

07/06/2021

Phím tắt trong Microsoft Excel

Điều hướng trong bảng tính (cid:151) Ctrl + Page Down / Page Up: chuyển qua lại giữa các Sheet với nhau Điều hướng trong bảng tính (cid:151) Ctrl + phím mũi tên æ (cid:242) (cid:239) (cid:240): di chuyển tới dòng trên cùng, dưới cùng, cạnh trái, cạnh phải trong bảng tính

Phím tắt trong Microsoft Excel

Điều hướng trong bảng tính (cid:151) Ctrl + Shift + phím mũi tên æ (cid:242) (cid:239) (cid:240) : khoanh chọn vùng dữ liệu tới cuối bảng thay vì chỉ di chuyển ô chọn xuống cuối bảng

Chọn các ô (cid:151) Shift + Space (Phím cách): Chọn toàn bộ dòng. (cid:151) Ctrl + Space (Phím cách): Chọn toàn bộ cột. (cid:151) Ctrl + A: Chọn toàn bộ bảng tính (khu vực chứa dữ liệu). Xóa cột/dòng (cid:151) Chọn toàn bộ cột/dòng, nhấn tổ hợp phím Ctrl – (dấu trừ) Chèn thêm cột/dòng (cid:151) Chọn toàn bộ cột/dòng tại vị trí cần chèn, nhấn tổ hợp phím Ctrl + (dấu cộng)

07/06/2021

Phím tắt trong Microsoft Excel

Các kiểu dữ liệu

˜ Dữ liệu kiểu số (Number) (cid:151) Tất cả ký tự nhập vào là số. Dữ liệu hiển thị lề phải ô.

(cid:151) Ctrl + ; (chấm phẩy): điền nhanh ngày tháng hiện tại.

˜ Dữ liệu kiểu chuỗi (Text) (cid:151) Tất cả ký tự nhập vào là chữ cái. Dữ liệu canh lề trái ô. F Khi sử dụng trong hàm/công thức phải đặt trong cặp " " (cid:151) Ví dụ: ="ABC"&"def" =LEFT("XYZ", 2) Chỉnh sửa nội dung bên trong ô (cid:151) F2: Chỉnh sửa ô đang chọn. (cid:151) Alt + Enter: Xuống một dòng mới trong cùng một ô. (cid:151) Esc: Hủy bỏ việc sửa trong một ô. (cid:151) Ctrl + Delete: Xóa văn bản đến cuối dòng. (cid:151) Ctrl + D: Copy nội dung /công thức ở ô bên trên. (cid:151) Ctrl + R: Copy nội dung/công thức ô bên trái. (cid:151) Ctrl + Shift + ; (chấm phẩy): điền nhanh thời gian hiện tại vào ô trong bảng tính

˜ Dữ liệu kiểu luận lý (logic) (cid:151) Có giá trị TRUE (» 1) hoặc FALSE (» 0)

Các kiểu dữ liệu

˜ Tùy chỉnh định dạng dữ liệu kiểu ngày tháng (Date) (cid:151) Khởi động Control Panel: Vào menu Start (cid:240) Control Panel (cid:151) Chọn mục Clock, Language and Region (cid:151) Chọn Region and Language

˜ Dữ liệu kiểu ngày tháng (Date) (cid:151) Cú pháp: dd/mm/yyyy (yy) hoặc dd-mm-yyyy (yy) (cid:151) Nếu nhập đúng ngày/tháng, dữ liệu hiển thị lề phải ô. (cid:151) Ví dụ: 25/12/2014 hoặc 25/12/14 hoặc 25-12-2014 F Khi sử dụng trong hàm/công thức phải đặt trong cặp " "

07/06/2021

(cid:151) Chọn Additional settings… (cid:151) Trong hộp thoại tiếp theo, chọn thẻ Date

Xóa nội dung và nhập: (cid:151) dd/mm/yyyy hoặc (cid:151) dd-mm-yyyy

(cid:151) Chọn Apply (cid:151) Chọn OK…

˜ Tùy chỉnh định dạng dữ liệu kiểu ngày tháng (Date) (cid:151) Mục Short date

Các kiểu dữ liệu

˜ Dữ liệu kiểu thời gian (cid:151) Cú pháp: hh:mm:ss (giờ:phút:giây) hoặc

(cid:151) Ví dụ: 12:30:45 (12 giờ: 30 phút: 45 giây); 12:30 F Khi sử dụng trong hàm/công thức phải đặt trong cặp " "

(cid:151) Một hằng số (số hoặc chuỗi ký tự). (cid:151) Một tham chiếu tới địa chỉ ô/vùng ô. (cid:151) Một hàm của Excel.

(cid:151) Kết thúc nhập công thức, nhấn phím Enter ¿ (cid:151) Kết quả trình bày trong ô là giá trị của công thức đó. (cid:151) Ví dụ: Nhập =(2*3+4)/2

hh:mm (giờ:phút) ˜ Dữ liệu kiểu công thức (Formula) (cid:151) Bắt đầu công thức là dấu bằng (=) (cid:151) Toán hạng có thể là:

(cid:240) Kết quả: 5

07/06/2021

Các toán tử dùng trong công thức

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

Ý nghĩa

Ví dụ

Toán tử

(cid:151) B1: Chọn ô/vùng ô cần định dạng (cid:151) B2: Chọn tab Home (cid:240) Format (cid:240)

Dấu ngoặc đơn

( )

(cid:240) Thay đổi độ ưu tiên của các phép toán

Format Cells

^

Lũy thừa

= 2 ^ 3

(cid:240) 8

-

Dấu âm

= - 3

(cid:240) -3

* / Nhân, chia

= (3 * 4) / 5

(cid:240) 2.4

+ -

Cộng, trừ

= 8 + (3 - 5)

(cid:240) 6

= <> Bằng, khác nhau

= 2 = 3 (cid:240) FALSE

= 2 <> 3 (cid:240) TRUE

> >= Lớn hơn, hoặc bằng

= 3 > 3 (cid:240) FALSE

= 3 >= 3 (cid:240) TRUE

< <= Nhỏ hơn, hoặc bằng

= 4 < 3 (cid:240) FALSE

= 2 <= 3 (cid:240) TRUE

&

Ghép chuỗi

= "ABC" & 123 (cid:240) ABC123

Định dạng kiểu dữ liệu

(cid:220) Thẻ Number: Định dạng các kiểu dữ liệu ˜ Mục Currency: Định dạng dữ liệu kiểu tiền tệ

(cid:151) Decimal places: Làm tròn số chữ số phần thập phân

(cid:151) Symbol: Chọn đơn vị

˜ Mục Number: Định dạng dữ liệu kiểu số

(cid:151) Decimal places: Làm tròn số chữ số phần thập phân.

(cid:151) Use 1000 separator: Dùng dấu phân cách hàng nghìn (,)

tiền tệ

07/06/2021

Định dạng kiểu dữ liệu

(cid:151) Decimal places: Quy định làm tròn số chữ số phần thập phân.

(cid:151) Type: Tùy chọn mẫu hiển thị dữ liệu kiểu ngày tháng trong danh sách

˜ Mục Date: Định dạng dữ liệu kiểu ngày tháng ˜ Mục Percentage: Định dạng hiển thị số %

Định dạng kiểu dữ liệu

(cid:151) Type: Nhập mẫu định dạng kiểu dữ liệu…

˜ Mục Custom: Tùy chỉnh dạng kiểu dữ liệu ˜ Mục Custom: Tùy chỉnh dạng kiểu dữ liệu

Ví dụ: (cid:151) Kiểu ngày tháng: dd-mm-yyyy yyyy-mm-dd … Ví dụ: (cid:151) Kiểu số: 000 0.00 …

07/06/2021

Định dạng kiểu dữ liệu

(cid:151) Ctrl + Shift + 1 (!): định dạng ô dạng số thập phân với 2 số

˜ Mục Custom: Tùy chỉnh dạng kiểu dữ liệu sau dấu phẩy.

Mẫu định dạng

Dữ liệu nhập

Dữ liệu hiển thị

000

001

(cid:151) Ctrl + Shift + 4 ($): định dạng ô tiền tệ $. (cid:151) Ctrl + Shift + 5 (%): định dạng ô là số %.

0.00

3.2

3.20

dd-mm-yyyy

12/2/12

12-02-2012

#,### [$VND]

2500

2,500 VND

Định dạng Font chữ

Size: Chọn cỡ chữ (từ 8 - 72)

(cid:220) Thẻ Font: (cid:151) Chọn các thông số cần (cid:220) Thẻ Font: (cid:151) Chọn các thông số cần thiết… thiết…

Font: Chọn mẫu ký tự (Font chữ)

Effects: Thêm hiệu ứng… (cid:151) Strikethrought: Chữ có

đường gạch giữa

(cid:151) Superscript: Chỉ số trên (cid:151) Subscript: Chỉ số dưới

Font style: Kiểu chữ (cid:151) Regular (bình thường) (cid:151) Bold (chữ in đậm) (cid:151) Italic (chữ in nghiêng)

07/06/2021

Canh lề vị trí dữ liệu trong ô

(cid:220) Thẻ Alignment: (cid:151) Chọn các thông số cần (cid:220) Thẻ Alignment: (cid:151) Chọn các thông số cần thiết… thiết…

Wrap text: Canh dữ liệu tự động xuống dòng cho vừa chiều ngang của ô.

Merge cells: Trộn các ô đã chọn thành một ô

Text alignment: Canh lề trong ô theo hướng chiều ngang (Horizontal) và chiều dọc (Vertical)

Shrink to fit: Thu nhỏ kích thước dữ liệu cho vừa chiều ngang của ô.

Orientation: Canh dữ liệu các chiều theo độ nghiêng tương ứng

Tạo khung viền

Tô màu nền

(cid:220) Thẻ Border: (cid:151) Chọn các thông số cần (cid:220) Thẻ Fill: (cid:151) Chọn các thông số cần thiết… thiết…

Background Color: Màu tô nền

B1: Mục Line (cid:151) Style: Kiểu đường viền (cid:151) Color: Màu đường viền

Pattern Style: Mẫu tô nền

B2: Mục Presets và Border Tùy chọn các đường viền cần kẻ khung

07/06/2021

Sao chép công thức

Click đúp chuột trái tại ô chứa công thức: đặt chuột vào vị trí dấu cộng, click đúp chuột trái và công thức sẽ được copy xuống dưới. Sử dụng nút Fill handle: (cid:151) B1: Nhấp chuột chọn ô chứa công thức cần sao chép. (cid:151) B2: Đặt trỏ chuột vào góc dưới bên phải ô, thấy xuất hiện

(cid:151) Sao chép công thức ô D1 theo cột

nút hình dấu thập đơn (Fill handle) Nhấp giữ chuột trái kéo theo dòng hoặc theo cột những ô cần sao chép công thức.

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

(cid:151) Địa chỉ tương đối trong công thức khi được sao chép đến vùng đích, địa chỉ tham chiếu trong vùng đích sẽ thay đổi theo hàng hay theo cột.

(cid:151) Ví dụ: Ta có bảng dữ liệu sau

(cid:151) Ta có kết quả:

(cid:151) Tại ô D1 nhập công thức: =A1+B1 (cid:151) Kết quả trong ô là: 15

Công thức tại các ô… (cid:151) D1 là: =A1+B1 (cid:151) D2 là: =A2+B2 (cid:151) D3 là: =A3+B3 (cid:151) D4 là: =A4+B4

07/06/2021

(cid:151) Sao chép công thức ô D1 theo hàng

Địa chỉ tuyệt đối

(cid:151) Địa chỉ tuyệt đối tham chiếu có dạng: $$

(Ví dụ: $A$1). Khi sao chép đến vùng đích vẫn giữ nguyên giá trị như ở vùng nguồn. (cid:151) Ví dụ: Ta có bảng dữ liệu sau

Ta có kết quả:

(cid:151) Tại ô D1 nhập công thức: =$A$1+$B$1 (cid:151) Kết quả trong ô là: 15

(cid:151) Sao chép công thức ô D1 theo cột, dòng

Công thức tại: (cid:151) D1 là: =A1+B1 (cid:151) E1 là: =B1+C1 (cid:151) F1 là: =C1+D1

Địa chỉ hỗn hợp

(cid:151) Địa chỉ hỗn hợp tham chiếu có dạng:

(cid:151) $ (tuyệt đối cột, tương đối dòng) hoặc (cid:151) $ (tương đối cột, tuyệt đối dòng) (cid:151) Ví dụ: $A3, B$5

(cid:151) Khi sao chép công thức giá trị tuyệt đối được giữ nguyên,

(cid:151) Các công thức vẫn giữ nguyên =$A$1+$B$1

giá trị tương đối thay đổi tương ứng. Ta có kết quả:

07/06/2021

(cid:151) VD: Sao chép công thức ô D1=$A1+B$1 theo cột:

(cid:151) VD: Sao chép công thức ô D1=$A1+B$1 theo hàng:

Ta có kết quả: Ta có kết quả:

Công thức tại… (cid:151) E1 =$A1+C$1 (cid:151) F1 =$A1+D$1 (cid:151) … Công thức tại các ô… (cid:151) D1=$A1+B$1 (cid:151) D2 =$A2+B$1 (cid:151) D3 =$A3+B$1 (cid:151) D4 =$A4+B$1 …

@ Bài tập sao chép công thức

(cid:151) Đổi tiền tệ, đổi đơn vị:

Phím F4: biến địa chỉ một ô thành địa chỉ tuyệt đối/tương đối.

Tỉ giá USD/VND

20,800

VND

USD

1.5

120

1000

…

07/06/2021

Hàm trên bảng tính

@ Bài tập sao chép công thức

(cid:151) Bảng Cửu chương:

(cid:151) Cách 1 (cid:151) Cách 2

(cid:151) Tính tổng lương tháng? =D3+D4+D5+D6+D7+D8+D9+D10+D11+D12

Công thức tại ô B2: = B$1*$A2

Hàm trên bảng tính

(cid:151) Tính trung bình cộng lương tháng? =(D3+D4+D5+D6+D7+D8+D9+D10+D11+D12)/10

(cid:151) Tính tổng lương tháng (cid:240) =SUM(D3:D12)

07/06/2021

Hàm trên bảng tính

Khái niệm hàm

(cid:151) Là công thức phức tạp được định nghĩa sẵn để thực hiện

một yêu cầu tính toán chuyên biệt nào đó. (cid:151) Mỗi hàm giải quyết một công việc nhất định.

(cid:151) Kết quả của một hàm có thể là một giá trị cụ thể, một

(cid:240) Giải quyết những vấn đề về quản lý và kỹ thuật do con người đặt ra.

(cid:151) Tính trung bình cộng lương tháng (cid:240) =AVERAGE(D3:D12)

chuỗi hoặc một thông báo lỗi.

Cú pháp chung của các hàm

˜ Thay đổi dấu phân cách đối số: (;) hoặc (,) (cid:151) Khởi động Control Panel: Vào menu Start (cid:240) Control Panel (cid:151) Chọn mục Clock, Language and Region (cid:151) Chọn Region and Language

(cid:151) Cú pháp: = (Đối số 1, Đối số 2, …, Đối số n) Tên hàm: Gõ chữ in hoa hay chữ thường đều được nhưng không được gõ tắt. (cid:151) Ví dụ: =SUM(…, …) =sum(…, …) =Sum(…, …)

Dấu ngoặc đơn: Có hay không có đối số, hàm phải có () Danh sách đối số: (cid:151) Dấu phân cách đối số: (,) hoặc (;) F Khai báo riêng (cid:151) Số lượng đối số tùy từng hàm quy định. (cid:151) Excel 2003: Một hàm nhiều nhất 30 đối số.

07/06/2021

(cid:151) Chọn Addional settings. Trong hộp thoại tiếp theo,

trong thẻ Numbers, đổi dấu trong mục List separator

Cú pháp chung của các hàm

(cid:151) Cú pháp: = (Đối số 1, Đối số 2, …, Đối số n) (cid:151) Đối số trong hàm có thể là:

Decimal symbol: . (dấu chấm) Digit grouping symbol: , (dấu phẩy) List separator: , (dấu phẩy)

(cid:151) Số: (cid:151) Giá trị logic: (cid:151) Chuỗi ký tự: (cid:151) Địa chỉ ô/Vùng ô: =INT(B3) =MAX(A1:C5) (cid:151) Biểu thức: (cid:151) Hàm khác: (cid:151) Rỗng:

=MOD(13, 5) =SUM(TRUE, FALSE) =LEFT("ABC_EF", 3)

=ROUND(13/3, 2) =SUM(D1, MAX(A1:C5)) =NOW() =TODAY()

Một số hàm thông dụng

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

(cid:151) Nhóm hàm toán học: SUM, PRODUCT, ROUND, INT, MOD (cid:151) Nhóm hàm thống kê: MAX, MIN, AVERAGE, COUNT,

COUNTA ˜ Hàm SUM: Tính tổng các số trong danh sách (cid:151) Cú pháp: =SUM(Danh sách các số hoặc vùng) (cid:151) Danh sách các số trong hàm SUM có thể là:

(cid:151) Nhóm hàm xử lý chuỗi: LEFT, RIGHT, MID, VALUE (cid:151) Nhóm hàm logic và điều kiện: AND, OR, IF, COUNTIF,

=SUM(13, 5)

SUMIF

(cid:151) Nhóm hàm tìm kiếm: VLOOKUP, HLOOKUP, MATCH, INDEX (cid:151) Nhóm hàm ngày tháng, thời gian: DAY, MONTH, YEAR,

=SUM(B3, B4, B5) =SUM(B3:B5); =SUM(A1:C5)

(cid:151) Số: (cid:151) Giá trị logic: =SUM(TRUE, FALSE) (cid:151) Địa chỉ ô: (cid:151) Vùng ô: (cid:151) Biểu thức: =SUM(13/3, 2) (cid:151) Hàm khác:

HOUR, MINUTE, SECOND =SUM(D1, SUM(A1:C5))

07/06/2021

Tính tổng nhanh

˜ Hàm SUM

=SUM(A1:C3)

=SUM(1:1)

=SUM(A:B)

Alt + =: tính tổng nhanh. Bôi đen vùng cần tính và bôi thêm 1 ô trống bên dưới sau đó nhấn tổ hợp 2 phím Alt + =

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

˜ Hàm PRODUCT: Tính tích các số trong danh sách. (cid:151) Cú pháp: =PRODUCT(Danh sách các số hoặc vùng) (cid:151) Ví dụ bảng dữ liệu: ˜ Hàm ROUND: Làm tròn số thập phân đến n vị trí chỉ định (cid:151) Cú pháp: =ROUND(Số cần làm tròn, Số chữ số làm tròn) (cid:151) Nếu Số chữ số > 0

D

H

I

(cid:151) =ROUND(21.5462, 2) (cid:240) 21.55 (cid:151) =ROUND(1234.5678, 1) (cid:240) 1234.6

A 15 1

B 14 4

C 50 5

80 8

E 16 1

F 13 3

G 23 2

6 6

15 1

J 232 5760

1 2

(cid:151) Nếu Số chữ số = 0

(cid:151) =ROUND(21.5462, 0) (cid:240) 22

(cid:151) Nếu Số chữ số < 0

(cid:151) =ROUND(21.5462, -1) (cid:240) 20 (cid:151) =ROUND(1234.567, -2) (cid:240) 1200

=PRODUCT(A2:I2) (cid:240) Kết quả: 5760

07/06/2021

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

Bài tập

(cid:151) Biết số ngày thuê phòng. Tính số tuần, số ngày lẻ.

˜ Hàm INT: Lấy giá trị phần nguyên của số thập phân (bỏ phần lẻ). (cid:151) Cú pháp: =INT(Số) (cid:151) Ví dụ: =INT(10.5) (cid:151) Ví dụ: =INT(10/7) (cid:240) Kết quả: 10 (cid:240) Kết quả: 1

˜ Hàm MOD: Lấy số dư của phép chia. (cid:151) Cú pháp: =MOD(Số bị chia, Số chia) (cid:151) Ví dụ: =MOD(14, 3) (cid:240) Kết quả: 2

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

Nhóm hàm thống kê

˜ Hàm MAX: Lấy giá trị số lớn nhất của các số có trong danh sách hoặc vùng. (cid:151) Cú pháp: =MAX(Danh sách các số hoặc vùng) (cid:151) Ví dụ: =MAX(2, 5, 7) (cid:240) Kết quả: 7

G

H

I

(cid:151) SUM(Danh sách các số hoặc vùng) (cid:151) PRODUCT(Danh sách các số hoặc vùng) (cid:151) ROUND(Số, Số chữ số làm tròn) (cid:151) INT(Số) (cid:151) MOD(Số bị chia, Số chia) F Đối số là kiểu số trong các hàm có thể là:

A 15 1

B 14 4

C 50 5

D 80 8

E 16 1

F 13 3

23 2

6 6

15 1

J 232 5760

1 2

(cid:151) Ví dụ: =MAX(A1:I2) (cid:240) Kết quả: 80

(cid:151) Một số (cid:151) Một địa chỉ ô chứa giá trị số (cid:151) Biểu thức toán (cid:151) Kết quả của một hàm khác trả về giá trị số

07/06/2021

Nhóm hàm thống kê

˜ Hàm MIN: Lấy giá trị số nhỏ nhất của các số có trong danh sách hoặc vùng. (cid:151) Cú pháp: =MIN(Danh sách các số hoặc vùng) (cid:151) Ví dụ: =MIN(2, 5, 7) (cid:240) Kết quả: 2 ˜ Hàm AVERAGE: Lấy giá trị trung bình cộng của các số trong danh sách hoặc vùng. (cid:151) Cú pháp: =AVERAGE(Danh sách các số hoặc vùng) (cid:151) Ví dụ:

D

H

I

G

H

I

A 15 1

B 14 4

C 50 5

80 8

E 16 1

F 13 3

G 23 2

6 6

15 1

J 232 5760

1 2

A 15 1

B 14 4

C 50 5

D 80 8

E 16 1

F 13 3

23 2

6 6

15 1

J 232 5760

1 2

(cid:151) Ví dụ: =MIN(A1:I2) (cid:240) Kết quả: 1

(cid:151) Ví dụ: =AVERAGE(A2:C2) (cid:240) (A2+B2+C2)/3 (cid:151) Ví dụ: =AVERAGE(A2, B2, B2, C2)

(cid:240) Kết quả: 3.33

(cid:240) (A2+B2+B2+C2)/4 (cid:240) Kết quả: 3.5

Nhóm hàm thống kê

@ Bài tập hàm thống kê

˜ Hàm COUNT: Đếm số ô có chứa dữ liệu số trong danh sách, vùng (cid:151) Cú pháp: =COUNT(Danh sách các vùng) (cid:151) Ví dụ bảng dữ liệu:

B

D

G

H

I

(cid:151) =ROUND((E4+F4+G4*2)/4, 1) (cid:151) =ROUND(AVERAGE(E4, F4, G4, G4), 1) (cid:151) =MAX(H4:H…) (cid:151) =MIN(H4:H…)

A 15 A

B 4

C 50 5

D 8

E 16 1

F 13 F

23 2

H 6

I 1

J 232 5760

1 2

(cid:151) Tính Điểm trung bình của các thí sinh theo yêu cầu:

(cid:151) Ví dụ: =COUNT(A1:I1) (cid:240) Kết quả: 5

(cid:151) Điểm Word & Excel hệ số 1, Windows hệ số 2; (cid:151) Điểm trung bình lấy 1 số lẻ.

(cid:151) Tìm Điểm trung bình cao nhất, thấp nhất.

07/06/2021

Nhóm hàm thống kê

@ Bài tập hàm thống kê

˜ Hàm COUNTA: Đếm số ô có chứa dữ liệu trong danh sách, vùng. (cid:151) Cú pháp: =COUNTA(Danh sách các vùng) (cid:151) Ví dụ bảng dữ liệu:

B

C

D

F

G

H

I

Mỗi ô tương ứng 1 cây (cid:240) Tổng số cây? Số cây từng nhóm?

A 15 A

1 2

C 5

E 16 1

G 2

J 232 5760

(cid:151) Ví dụ: =COUNTA(A1:I1) (cid:240) Kết quả: 4

Nhóm hàm xử lý chuỗi

@ Bài tập hàm thống kê

(cid:151) Tổng số HS trong lớp? (cid:151) Bao nhiêu HS chưa có điểm

(cid:151) Bao nhiêu HS có điểm kiểm

kiểm tra hệ số 1 (L1)? (cid:240) Kết quả: ABC

tra hệ số 1 (L1) trên 5?

(cid:240) #VALUE!

˜ Hàm LEFT: Trích xuất ra một số ký tự từ bên trái chuỗi cho trước. (cid:151) Cú pháp: =LEFT(Chuỗi kí tự, Số ký tự cần lấy) (cid:151) Ví dụ: =LEFT("ABC123", 3) Chú ý: (cid:151) Giá trị chuỗi kí tự trong hàm phải đặt trong cặp " " (cid:151) Nếu ô A1 chứa chuỗi "ABC123": =LEFT(A1, 3) (cid:240) ABC (cid:151) Số ký tự cần lấy > 0 (cid:151) =LEFT("ABC123", -2) (cid:151) =LEFT(A1, 1) =LEFT(A1) (cid:240) Kết quả: A (cid:151) =LEFT("ABC123", 7) (cid:240) Kết quả: ABC123 (6 ký tự)

07/06/2021

Nhóm hàm xử lý chuỗi

(cid:240) #VALUE!

˜ Hàm RIGHT: Trích xuất ra một số ký tự từ bên phải chuỗi cho trước. (cid:151) Cú pháp: =RIGHT(Chuỗi kí tự, Số ký tự cần lấy) (cid:151) Ví dụ: =RIGHT("ABC123", 3) (cid:240) Kết quả: 123 Chú ý: (cid:151) Nếu ô A1 chứa chuỗi "ABC123": =RIGHT(A1, 3) (cid:240) 123 (cid:151) Số ký tự cần lấy > 0 (cid:151) =RIGHT("ABC123", -2) (cid:151) =RIGHT(A1, 1) =RIGHT(A1) (cid:240) Kết quả: 3 (cid:151) =RIGHT("ABC123", 7) (cid:240) Kết quả: ABC123 (6 ký tự) ˜ Hàm MID: Trích xuất ra một chuỗi con từ vị trí bắt đầu với số ký tự cần lấy được chỉ định (cid:151) Cú pháp: =MID(Chuỗi k.tự, Vị trí bắt đầu, Số k.tự cần lấy) (cid:151) Ví dụ: =MID("ABC123", 3, 2) (cid:240) Kết quả: C1 Chú ý: (cid:151) Ký tự đầu tiên bên trái của chuỗi có vị trí là 1, 2, … (cid:151) Khoảng trắng được tính 1 ký tự. (cid:151) =MID("ABC123", 0, 2) (cid:240) #VALUE! (cid:151) =MID("ABC123", 7, 2) (cid:240) Chuỗi rỗng (cid:151) =MID("ABC123", 5, 3) (cid:240) Kết quả: 23 (2 ký tự)

Nhóm hàm xử lý chuỗi

ABC123 123 (cid:254) Tính toán +-*/ (cid:253) So sánh | Hàm

˜ Hàm LEN: Trả về số lượng ký tự trong chuỗi (cid:151) Cú pháp: =LEN(Chuỗi ký tự/Địa chỉ ô) (cid:151) Ví dụ:

123 123 ˜ Hàm VALUE: Chuyển đổi văn bản gồm các ký số sang kiểu số (cid:151) Cú pháp: =VALUE(Chuỗi ký tự dạng số) (cid:151) Ví dụ: =VALUE( ) RIGHT("ABC123", 3) (cid:240) =LEN("ABC123") (cid:240) Kết quả: 6 Nếu A1="ABC123" =LEN(A1) (cid:240) Kết quả: 6

100

Chú ý: (cid:151) Chuỗi ký tự cần đổi phải chứa toàn bộ các ký số (cid:151) =VALUE(RIGHT("ABC123", 4)) (cid:240) #VALUE!

07/06/2021

Nhóm hàm logic & điều kiện

@ Bài tập hàm xử lý chuỗi

˜ Hàm AND: (cid:151) Cú pháp: =AND(Biểu thức đ.kiện 1, Biểu thức đ.kiện 2, …) ˜ Hàm OR: (cid:151) Cú pháp: =OR(Biểu thức đ.kiện 1, Biểu thức đ.kiện 2, …)

(cid:151) Điền Tên hàng: Nhóm 3 hoặc 4 ký tự đầu của Mã phiếu xuất (cid:151) Điền Số lượng: 3 ký tự số nằm cuối của Mã phiếu xuất (cid:151) Tính tiền Thuế: = Số lượng * Đơn giá * 10% (cid:151) Tính Tổng số lượng nhiên liệu đã xuất.

101

102

F Chức năng chung: Xét các Biểu thức đ.kiện và trả về kết quả chung TRUE (đúng) hoặc FALSE (sai)

Nhóm hàm logic & điều kiện

˜ Hàm AND

TRUE

TRUE FALSE

(cid:151) Biểu thức điều kiện:

(cid:151) Các toán hạng (số, chuỗi, địa chỉ ô,…) so sánh với nhau

(cid:151) Biểu thức điều kiện cho kết quả là TRUE (đúng) hoặc

Đ.kiện 1 Đ.kiện 2 … Đ.kiện n sử dụng các toán tử so sánh (=, <>, >, >=, <, <=)

AND(…, …) FALSE (sai)

(cid:151) Ví dụ: =AND(2*3=6, 7>5, 2<>3) (cid:240) Kết quả: TRUE (cid:151) Ví dụ: =AND(2*3=6, 7>5, 2<>2) (cid:240) Kết quả: FALSE

(cid:151) Ví dụ: 3>2 3<>5 A1="ABC" B2=100 …

103

104

Tất cả biểu thức là đúng (cid:240) TRUE Có ít nhất một điều kiện sai (cid:240) FALSE

07/06/2021

˜ Hàm OR

˜ Hàm IF

TRUE FALSE

FALSE

Đ.kiện 1 Đ.kiện 2 … Đ.kiện n

(cid:151) Xét Kết quả dựa trên Điểm TB:

OR(…, …)

(cid:151) Nếu Điểm TB ‡ 5 thì Kết quả là “Đậu” (cid:151) Ngược lại (Điểm TB < 5) thì Kết quả là “Rớt”.

(cid:151) Ví dụ: =OR(6+9=15, 3>5, 8<3) (cid:240) Kết quả: TRUE (cid:151) Ví dụ: =OR(6+9<>15, 3>5, 8<3) (cid:240) Kết quả: FALSE

105

106

Tất cả biểu thức là sai (cid:240) FALSE Có ít nhất một điều kiện đúng (cid:240) TRUE

˜ Hàm IF: Ví dụ bảng tính

˜ Hàm IF

(cid:151) Cú pháp: =IF(Biểu thức điều kiện, Giá trị khi đ.kiện đúng,

(cid:151) Chức năng: Nếu “Biểu thức điều kiện” đúng (TRUE) thì hàm IF trả về “Giá trị khi đ.kiện đúng”, nếu Biểu thức đ.kiện sai (FALSE) hàm IF sẽ trả về “Giá trị khi đ.kiện sai”

Đúng

Giá trị khi đ.kiện sai)

IF

(cid:151) 1) Xét Kết quả dựa trên Điểm TB:

Biểu thức điều kiện

Giá trị khi điều kiện đúng

Sai

(cid:151) Nếu Điểm TB ‡ 5 thì Kết quả là “Đậu” (cid:151) Ngược lại (Điểm TB < 5) thì Kết quả là “Rớt”.

Giá trị khi điều kiện sai

107

108

07/06/2021

˜ Hàm IF

(cid:151) Ví dụ: Tại ô I4 nhập công thức =IF(H4>=5, "Đậu", "Rớt")

Đúng

IF

H4>=5

"Đậu"

IF

H4>=5

"Đậu"

Sai

"Rớt"

H

I

H

I

5 Đậu

4 Rớt

109

110

˜ Hàm IF: Ví dụ bảng tính

˜ Hàm IF

Đúng

F Đảo điều kiện:

IF

H4>=5

"Đậu"

Sai

"Rớt"

(cid:151) I4 =IF(H4<5, "Rớt", "Đậu")

(cid:151) Cách 1: tại G3 ghi =IF(E3="Giám đốc", F3*10%, 0) (cid:151) Cách 2: =IF(E3<>"Giám đốc", 0, F3*10%)

Đúng

IF

H4<5

"Rớt"

(cid:151) Xét tiền Thưởng dựa trên điều kiện Chức vụ:

Sai

(cid:151) Nếu chức vụ Giám đốc thì thưởng 10% Lương tháng. (cid:151) Ngược lại, không được thưởng (=0)

"Đậu"

111

112

07/06/2021

˜ Hàm IF lồng nhau

˜ Hàm IF

(cid:151) Biểu thức điều kiện:

(cid:151) Các toán hạng (số, chuỗi, địa chỉ ô,…) so sánh với nhau

(cid:151) Kết quả là TRUE (đúng) hay FALSE (sai) (cid:151) Có thể sử dụng thêm hàm AND, OR để kết hợp nhiều

sử dụng các toán tử so sánh (=, <>, >, >=, <, <=)

(cid:151) Giá trị trả về có thể là:

(cid:151) 2) Xét Xếp loại dựa trên Điểm TB:

biểu thức điều kiện khác

Điểm TB

< 5

‡ 5 (< 7)

‡ 7

Xếp loại

Yếu

TB

Khá

(cid:151) Giá trị số / Biểu thức toán (cid:240) Giá trị số (cid:151) Chuỗi ký tự (phải đặt trong cặp dấu nháy kép “…”) (cid:151) Địa chỉ ô chứa giá trị (cid:151) Một hàm khác (cid:240) Hàm IF lồng nhau

113

114

˜ Hàm IF lồng nhau

(cid:151) Ví dụ: Tại ô J4 nhập công thức xét Xếp loại

(cid:151) Cú pháp:

Đúng

=IF(Đk1, Kq1, IF(Đk2, Kq2, ... IF(Đkn, Kqn, S)...))

IF

H4>=7

"Khá"

(cid:151) Xét nếu điều kiện 1 (ĐK1) đúng thì thực hiện kết quả đúng

Sai

Điều kiện?

1 (Kq1) và dừng.

IF

"TB"? "Yếu"?

(cid:151) Nếu sai thì xét tiếp điều kiện 2, nếu Đk2 đúng thì thực

(cid:151) Nếu sai thì xét tiếp đến điều kiện n, nếu Đkn đúng thì

(cid:151) Giá trị trả về được nhận thông qua kết quả một hàm khác (cid:240)Hàm IF lồng nhau

hiện kết quả đúng 2 (Kq2) và dừng…

thực hiện kết quả đúng n (Kqn) nếu sai thì thực hiện kết quả sai (S).

Điểm TB

< 5

Xếp loại

Yếu

‡ 5 (< 7) TB

‡ 7 Khá

115

116

07/06/2021

˜ Hàm IF: Ví dụ bảng tính

˜ Hàm IF lồng nhau

Đúng

IF

Điều kiện 1

Kết quả đúng 1

Sai

Đúng

IF

Điều kiện 2

Kết quả đúng 2

Sai

Đúng

IF

Điều kiện n

Kết quả đúng n

(cid:151) 2) Xét Xếp loại dựa trên Điểm TB:

Sai

Điểm TB

< 5

‡ 5 (< 7)

‡ 7

Kết quả sai

Số lượng hàm IF phù hợp?

Xếp loại

Yếu

TB

Khá

117

118

Đúng

˜ Hàm IF: Hàm IF lồng nhau ˜ Hàm IF: Hàm IF lồng nhau #2

IF

H4>=7

IF

H4>=7

"Khá"

Đúng

H4<7

Sai

IF

H4>=5

"TB"

IF

H4<5

"Yếu"

H4<5

H4‡5

Sai

"Yếu"

"TB"

5

7

5

7

Yếu

Khá

>=7

(cid:151) Tại J4 nhập =IF(H4>=7, "Khá", IF(H4>=5, "TB", "Yếu"))

(cid:151) Tại J4 nhập =IF(H4>=7, "Khá", IF(H4<5, "Yếu", "TB"))

119

120

07/06/2021

Đúng

˜ Hàm IF: Hàm IF lồng nhau (Đảo điều kiện) ˜ Bài tập hàm IF lồng nhau

IF

H4<5

"Yếu"

(cid:151) Xét Xếp loại tại ô J4:

Đúng

H4‡5

Sai

(cid:151) =IF(H4<5, "Yếu", IF(H4<7, "TB", IF(H4<8, "Khá", IF(H4<10,

IF

H4<7

"TB"

H4‡7

Sai

(cid:151) =IF(H4>=10, "Xuất sắc", IF(H4>=8, "Giỏi", IF(H4>=7, "Khá",

"Khá"

"Giỏi", "Xuất sắc"))))

(cid:151) Xét Xếp loại dựa trên Điểm TB:

IF(H4>=5, "TB", "Yếu"))))

5

7

Khá

Yếu

Điểm TB

< 5

‡ 7 (< 8) ‡ 8 (< 10)

<5

Xếp loại

Yếu

‡ 5 (< 7) TB

Khá

Giỏi

Xuất sắc

(cid:151) Tại J4 nhập =IF(H4<5, "Yếu", IF(H4<7, "TB", "Khá"))

122

121

F Xét thêm trường hợp đảo điều kiện

Đúng

˜ Bài tập hàm IF lồng nhau ˜ Bài tập hàm IF lồng nhau (Đảo)

IF

H4<5

"Yếu"

IF

H4>=10

"Xuất sắc"

Đúng

Sai

IF

H4<7

"TB"

IF

H4>=8

"Giỏi"

Đúng

Sai

IF

H4<8

"Khá"

IF

H4>=7

"Khá"

Đúng

Sai

IF

H4<10

"Giỏi"

IF

H4>=5

"TB"

Sai

"Xuất sắc"

"Yếu"

8

5

7

8

10

5

7

10

Yếu

Khá

Giỏi

Xuất sắc

Yếu

Khá

Giỏi

Xuất sắc

=IF(H4<5, "Yếu", IF(H4<7, "TB", IF(H4<8, "Khá", IF(H4<10, "Giỏi", "Xuất sắc"))))

123

124

=IF(H4>=10, "Xuất sắc", IF(H4>=8, "Giỏi", IF(H4>=7, "Khá", IF(H4>=5, "TB", "Yếu"))))

07/06/2021

˜ Bài tập hàm IF lồng nhau

˜ Hàm IF lồng nhau

(cid:151) Excel sử dụng tối đa bao nhiêu hàm IF lồng nhau?

Điểm TB

Xếp loại

A

‡9

B

‡8

C

(cid:151) =IF(E3="Giám đốc", F3*10%, IF(E3="Phó Giám đốc",

‡7

D

‡6

F3*5%, 0))

E

‡5

F

‡4

G

(cid:151) Xét tiền Thưởng dựa trên điều kiện Chức vụ:

‡3

H

‡2

I

‡1

J

(cid:151) Nếu chức vụ Giám đốc thì thưởng 10% Lương tháng (cid:240) (cid:151) Nếu chức vụ Phó Giám đốc thì thưởng 5% Lương tháng (cid:240) (cid:151) Ngược lại, không được thưởng

K

125

126

Điểm TB

Xếp loại

˜ Hàm IF lồng nhau

˜ Hàm IF nhiều điều kiện

A

‡9

B

F Excel 2003 sử dụng tối đa 8 hàm IF

‡8

C

‡7

lồng nhau

D

F Excel 2010 sử dụng tối đa 64 hàm IF

‡6

E

‡5

lồng nhau

F

‡4

G

Sử dụng hàm tìm kiếm

‡3

H

‡2

I

(cid:151) Xét Kết quả dựa trên điều kiện:

‡1

J

K

(cid:151) Nếu Điểm TB ‡ 5 thì và Điểm Windows ‡ 5 thì Kết quả mới

được “Đậu”

(cid:151) =IF(D3>=10, "A", IF(D3>=9, "B", IF(D3>=8, "C", IF(D3>=7, "D",

(cid:151) Ngược lại thì Kết quả “Rớt” (không thỏa 1 trong 2 điều kiện

IF(D3>=6, "E", IF(D3>=5, "F", IF(D3>=4, "G", IF(D3>=3, "H", "I"))))))))

hoặc không thỏa cả 2 điều kiện trên).

127

128

07/06/2021

˜ Hàm IF nhiều điều kiện

Điểm TB

Đúng

˜ Kết hợp biểu thức điều kiện (cid:151) Xét Kết quả: ˜ Kết hợp biểu thức điều kiện (cid:151) Cách sử dụng:

IF

AND(

AND(Đ.kiện 1, Đ.kiện 2, …) OR(Đ.kiện 1, Đ.kiện 2, …)

Giá trị khi điều kiện đúng

IF

"Đậu"

H4>=5 G4>=5

, , … )

Sai

Điểm Windows

Sai

"Rớt"

Giá trị khi điều kiện sai

(cid:151) =IF(AND(H4>=5, G4>=5, …), "Đậu", "Rớt") (cid:151) =IF(OR(H4>=5, G4>=5, …), "Đậu", "Rớt") = ?

129

130

(cid:151) Xét tiền Thưởng tại ô G4:

(cid:151) Xét Kết quả tại ô I4:

Bài tập hàm IF kết hợp biểu thức điều kiện Bài tập hàm IF kết hợp biểu thức điều kiện

(cid:151) =IF(AND(H4>=5, E4>=5, F4>=5, G4>=5), "Đậu", "Rớt") (cid:151) =IF(AND(H4>=5, MIN(E4:G4)>=5), "Đậu", "Rớt")

(cid:151) Xét Kết quả dựa trên các điều kiện:

(cid:151) Nếu Điểm TB ‡ 5 thì và không có môn nào điểm < 5 thì Kết

quả mới được “Đậu”

(cid:151) Ngược lại thì Kết quả “Rớt” (không thỏa cả 2 điều kiện trên).

(cid:151) Xét tiền Thưởng dựa trên điều kiện Chức vụ và Phòng ban: (cid:151) Trưởng phòng Kinh doanh được thưởng 10% Lương tháng (cid:151) Trưởng phòng Kế toán được thưởng 5% Lương tháng (cid:151) Ngược lại, không được thưởng (0).

131

132

=IF(AND(E3="Trưởng phòng", D3="Phòng Kinh doanh"), F3*10%, IF(AND(E3="Trưởng phòng", D3="Phòng Kế toán"), F3*5%, 0))

07/06/2021

Bài tập hàm IF kết hợp biểu thức điều kiện

Nhóm hàm logic & điều kiện

(cid:151) Xét tiền Thưởng tại ô G4:

(cid:151) Xét tiền Thưởng dựa trên điều kiện Chức vụ:

(cid:151) COUNTIF (cid:151) SUMIF

(cid:151) Tổng số nhân viên Phòng Kế toán? (cid:240) (cid:151) Tổng Lương tháng của NV Phòng Kế toán? (cid:240)

(cid:151) Giám đốc và Trưởng phòng được thưởng 10% Lương tháng (cid:151) Phó Giám đốc và Phó phòng được thưởng 5% Lương tháng (cid:151) Ngược lại, không được thưởng (0).

133

134

=IF(OR(E3="Giám đốc", E3="Trưởng phòng"), F3*10%, IF(OR(E3="Phó Giám đốc", E3="Phó phòng"), F3*5%, 0))

˜ Hàm COUNTIF

˜ Hàm COUNTIF: Ví dụ

A

B

C

D

E

F

G

H

I

5 ABC

4 BC

8 Abc

3 A

1 4 a ABC_

3 b

5 C

6 ab

1 2

(cid:151) Cú pháp: =COUNTIF(Vùng so sánh, Tiêu chuẩn so sánh) (cid:151) Chức năng: Đếm số ô trong Vùng so sánh thoả Tiêu chẩn

(cid:151) Tiêu chẩn so sánh có thể là:

(cid:151) =COUNTIF(A1:I2, 5)

(cid:151) Tiêu chẩn so sánh có thể là: Giá trị số (cid:240) 2

Không phân biệt in hoa/thường

(cid:151) Tiêu chẩn so sánh có thể là: Chuỗi ký tự

(cid:151) =COUNTIF(A1:I2, "ABC")

(cid:240) 2

(cid:151) Tiêu chẩn so sánh có thể là: Địa chỉ ô chứa giá trị

(cid:151) Giá trị số (cid:151) Chuỗi ký tự (không phân biệt chữ in hoa/thường) (cid:151) Địa chỉ ô chứa giá trị số/chuỗi (cid:151) Biểu thức

(cid:151) =COUNTIF(A1:I2, A2)

(cid:240) 2

(cid:151) Tiêu chẩn so sánh có thể là: Biểu thức

(cid:151) =COUNTIF(A1:I2, ">=5")

(cid:240) 4

135

136

so sánh.

07/06/2021

Dùng hàm TRIM để loại bỏ khoảng trắng ở đầu hoặc cuối ô

(cid:151) Tổng số nhân viên Phòng Kế toán?

(cid:151) Số người có Lương ngày bằng 40000?

(cid:240) 1 (Lỗi?)

(cid:151) =COUNTIF(D3:D10, "Phòng Kế toán") (cid:240) 2 (cid:240) 2 (cid:151) =COUNTIF(D3:D10, D4)

(cid:151) =COUNTIF(E3:E10, 40000) (cid:151) =COUNTIF(E3:E10, E6)

(cid:240) 2 (cid:240) 2

137

138

˜ Hàm COUNTIF: Ví dụ bảng tính ˜ Hàm COUNTIF: Ví dụ bảng tính

Ký tự đại diện: ? Ký tự đơn lẻ bất kỳ. * Chuỗi ký tự bất kỳ. Muốn tìm dấu ? hay dấu * thực sự, nhập dấu ~ trước ký tự

(cid:151) Bao nhiêu người có tên bắt đầu bằng "H"?

(cid:151) Số người có Lương ngày dưới 40000? (cid:151) =COUNTIF(E3:E10, "<40000") (cid:240) 5 (cid:240) ? (cid:151) =COUNTIF(E3:E10, "

(cid:151) =COUNTIF(C3:C10, "H*") (cid:151) =COUNTIF(C3:C10, "H?")

(cid:240) 5 (cid:240) 1

139

140

07/06/2021

Bài tập

Số cây tràm: =COUNTIF(A2:L9, “Tràm”)

Mỗi ô tương ứng 1 cây (cid:240) Tổng số cây? Số cây từng nhóm?

141

142

˜ Hàm COUNTIFS: Ví dụ bảng tính

˜ Hàm COUNTIFS

(cid:151) Cú pháp: =COUNTIFS(Vùng SS 1, Tiêu chuẩn SS 1,

Vùng SS 2, Tiêu chuẩn SS 2, …) (cid:151) Chức năng: Đếm số ô thoả mãn đồng thời các Tiêu chẩn

(cid:151) Tổng số nhân viên Phòng Kế toán xếp loại A?

(cid:151) Giá trị số (cid:151) Chuỗi ký tự (cid:151) Địa chỉ ô chứa giá trị số/chuỗi (cid:151) Biểu thức

(cid:151) =COUNTIFS(D3:D10, "Phòng Kế toán", G3:G10, "A")

143

144

so sánh trong các Vùng so sánh tương ứng. (cid:151) COUNTIFS hỗ trợ 127 vùng so sánh điều kiện. (cid:151) Tiêu chẩn so sánh có thể là:

07/06/2021

˜ Ví dụ bảng tính

˜ Hàm SUMIF

(cid:151) Cú pháp: =SUMIF(Vùng so sánh, Tiêu chuẩn so sánh, Vùng

(cid:151) Chức năng: Tính tổng các số trong Vùng tính tổng mà số hạng tương ứng ở Vùng so sánh đã thỏa Tiêu chuẩn so sánh.

(cid:151) Tiêu chẩn so sánh có thể là:

(cid:151) Tổng Lương tháng của nhân viên Phòng Kế toán?...

Tương tự trong COUNTIF

(cid:151) Giá trị số (cid:151) Chuỗi ký tự (cid:151) Địa chỉ ô chứa giá trị số/chuỗi (cid:151) Biểu thức

145

146

tính tổng)

(cid:151) Tổng Lương tháng của nhân viên Phòng Kế toán?

(cid:151) Tổng Lương tháng của những người có lương ngày >40000?

(cid:151) =SUMIF(D3:D10, "Phòng Kế toán", F3:F10) (cid:151) =SUMIF(D3:D10, D4, F3:F10)

(cid:151) =SUMIF(E3:E10, ">40000", F3:F10) (cid:151) =SUMIF(E3:E10, ">E6", F3:F10) ???

147

148

˜ Hàm SUMIF: Ví dụ bảng tính ˜ Hàm SUMIF: Ví dụ bảng tính

07/06/2021

(cid:151) Tổng Lương tháng của các nhân viên ở các Phòng?

(cid:151) Tổng các Lương tháng có giá trị >1000000?

(cid:151) =SUMIF(D3:D10, "Phòng*", F3:F10)

(cid:151) =SUMIF(D3:D10, ">1000000")

149

150

˜ Hàm SUMIF: Ví dụ bảng tính ˜ Hàm SUMIF: Ví dụ bảng tính

˜ Hàm SUMIFS: Ví dụ bảng tính

˜ Hàm SUMIFS

(cid:151) Cú pháp: =SUMIFS(Vùng tính tổng, Vùng SS 1,

(cid:151) Chức năng: Tính tổng các số trong Vùng tính tổng mà số hạng tương ứng ở các Vùng so sánh đã thỏa mãn đồng thời các Tiêu chuẩn so sánh tương ứng. (cid:151) SUMIFS hỗ trợ 127 vùng so sánh điều kiện (cid:151) Tiêu chẩn so sánh có thể là:

Tương tự trong COUNTIF

(cid:151) Giá trị số (cid:151) Chuỗi ký tự (cid:151) Địa chỉ ô chứa giá trị số/chuỗi (cid:151) Biểu thức

(cid:151) Tổng lương ngày của nhân viên Phòng Kế toán xếp loại A? (cid:151) =SUMIFS(F3:F10, D3:D10, "Phòng Kế toán", G3:G10, "A")

151

152

Tiêu chuẩn SS 1, Vùng SS 2, Tiêu chuẩn SS 2, …)

07/06/2021

Nhóm hàm tìm kiếm

(cid:151) VD: Tìm % Thưởng dựa theo Xếp loại ABC… trong Bảng tra

Tìm kiếm theo chiều dọc (cột) VLOOKUP

Tìm kiếm theo chiều ngang HLOOKUP

20%

153

154

(cid:151) =VLOOKUP(Trị dò, Bảng dò, Cột lấy giá trị, Cách dò)

Nhóm hàm tìm kiếm

(cid:151) =VLOOKUP(E3, $H$5:$I$8, 2, 0)

(4) Cách dò

(3) Số thứ tự cột lấy giá trị

(1) Trị dò

(cid:151) Chức năng: Dò tìm “Trị dò” trong cột đầu tiên bên trái của “Bảng dò”. Khi tìm được giá trị dò, chuyển đến “Cột lấy giá trị” để lấy giá trị của ô tương ứng theo hàng mang về ô chứa hàm.

(2) Bảng dò

Vertical

155

156

˜ Hàm VLOOKUP: Hàm dò tìm theo cột / chiều dọc (cid:151) Cú pháp: =VLOOKUP(Trị dò, Bảng dò, Cột lấy giá trị, Cách dò)

07/06/2021

(cid:151) Trị dò và cột đầu tiên của Bảng dò phải cùng kiểu dữ liệu, cùng miền giá trị, không phân biệt chữ in hoa/thường.

Thường dùng

(cid:151) Trị dò có thể là: (cid:151) Một giá trị số (cid:151) Một giá trị chuỗi (cid:151) Tham chiếu đến một ô nào đó (cid:151) Một kết quả của một hàm khác

157

158

˜ Hàm VLOOKUP: Lưu ý ˜ Hàm VLOOKUP: Lưu ý

(cid:151) Bảng dò trong hàm phải mang địa chỉ tuyệt đối =VLOOKUP(E3, $H$5:$I$8, 2, 0)

(cid:151) Bảng dò phải có cột đầu tiên chứa các trị dò (cid:151) Bảng dò phải có các cột lấy giá trị (cột thứ 2, 3…)

Bảng dò H5:I8

159

160

˜ Hàm VLOOKUP: Lưu ý ˜ Hàm VLOOKUP: Lưu ý

07/06/2021

˜ Hàm VLOOKUP: Lưu ý

Nhóm hàm tìm kiếm

(cid:151) Bảng dò có thể đặt trong cùng hoặc khác trang bảng tính. =VLOOKUP(E3, Sheet2!$A$3:$B$6, 2, 0)

(cid:151) Chức năng: Dò tìm “Trị dò” trong hàng trên cùng của

Bảng dò A3:B6

˜ Hàm HLOOKUP: Hàm dò tìm theo hàng / chiều ngang (cid:151) Cú pháp: =HLOOKUP(Trị dò, Bảng dò, Hàng lấy giá trị, Cách dò)

Horizontal

“Bảng dò”. Khi tìm được giá trị dò, chuyển đến “Hàng lấy giá trị” để lấy giá trị của ô tương ứng theo cột mang về ô chứa hàm

Sheet1

Sheet2

161

162

(cid:151) =HLOOKUP(Trị dò, Bảng dò, Hàng lấy giá trị, Cách dò)

(cid:151) =HLOOKUP(E3, $I$11:$L$12, 2, 0)

(cid:151) Trị dò và hàng đầu tiên của Bảng dò phải cùng kiểu dữ liệu, cùng miền giá trị, không phân biệt in hoa/thường.

(4) Cách dò

(1) Trị dò

(2) Bảng dò

(3) Số thứ tự hàng lấy giá trị

163

164

˜ Hàm HLOOKUP: Lưu ý

07/06/2021

(cid:151) Bảng dò phải có hàng đầu tiên chứa các trị dò (cid:151) Bảng dò phải có các hàng lấy giá trị (hàng thứ 2, 3…)

Thường dùng

(cid:151) Cách dùng trị dò trong HLOOKUP tương tự VLOOKUP

Bảng dò I11:L12

165

166

˜ Hàm HLOOKUP: Lưu ý ˜ Hàm HLOOKUP: Lưu ý

(cid:151) Bảng dò trong hàm phải mang địa chỉ tuyệt đối =HLOOKUP(E3, $I$11:$L$12, 2, 0)

(cid:151) Bảng dò có thể đặt trong cùng hoặc khác trang bảng tính =HLOOKUP(E3, Sheet2!$B$9:$E$10, 2, 0)

Bảng dò B9:E10

Bảng dò I11:L12

˜ Hàm HLOOKUP: Lưu ý ˜ Hàm HLOOKUP: Lưu ý

Sheet1

Sheet2

167

168

07/06/2021

(cid:151) Cách dò là 1 khi Bảng dò có cột/hàng đầu tiên được sắp xếp theo thứ tự số 1, 2, 3… hoặc theo ký tự A, B, C… (cid:151) =VLOOKUP(E3, $H$5:$I$8, 2, 1)

(cid:240) =HLOOKUP(…)

(cid:151) B1: Chọn và Copy vùng bảng tính (Ctrl+C) (cid:151) B2: Click chuột phải tại vị trí cần chèn (cid:240) Paste Special (cid:151) B3: Check tùy chọn “Transpose” (cid:240) OK

Sắp xếp theo thứ tự

Copy Ctrl + C

169

170

˜ Đảo chiều bảng tính ˜ Sử dụng cách dò trong VLOOKUP, HLOOKUP

(cid:151) Cách dò là 0 khi Bảng dò có cột/hàng đầu tiên không

(cid:151) Cách dò là 1 khi Bảng dò có cột/hàng đầu tiên được sắp xếp theo thứ tự số 1, 2, 3… hoặc theo ký tự A, B, C… (cid:151) =VLOOKUP(E3, $H$5:$I$8, 2, 1)

(cid:240) =HLOOKUP(…)

˜ Sử dụng cách dò trong VLOOKUP, HLOOKUP ˜ Sử dụng cách dò trong VLOOKUP, HLOOKUP

(cid:240) =HLOOKUP(…)

Sắp xếp không theo thứ tự

được sắp xếp theo thứ tự (cid:151) =VLOOKUP(E3, $H$5:$I$8, 2, 0)

(cid:252)

171

172

07/06/2021

(cid:151) Ví dụ: Dò tìm và điền giá trị Xếp loại dựa theo Điểm TB

˜ Sử dụng cách dò trong VLOOKUP, HLOOKUP ˜ Sử dụng cách dò trong VLOOKUP, HLOOKUP

(cid:151) =VLOOKUP(D3, $G$3:$H$7, 2, 1)

(cid:151) Cách dò là 0 dùng cho tìm kiếm với trị dò chính xác (cid:151) Cách dò là 1 dùng cho tìm kiếm với trị dò xấp xỉ, gần đúng (cid:151) Ví dụ: Dò tìm và điền giá trị Xếp loại dựa theo Điểm TB

trong Bảng tra

Tìm giá trị lớn nhất £ Trị dò

173

174

trong Bảng tra

(cid:151) Tìm Tên hàng dựa theo kí tự đầu Mã hàng trong Bảng tra 1 (cid:151) =VLOOKUP(LEFT(B3), $B$12:$E$14, 2, 0)

(cid:151) Dò tìm Đơn giá trong Bảng tra 1 (Cách 1): (cid:151) =VLOOKUP(LEFT(B3), $B$12:$E$14, 3, 0)

Trị dò: LEFT(B3)

Số thứ tự Cột trả về giá trị

175

176

˜ Hàm VLOOKUP, HLOOKUP: Ví dụ ˜ Hàm VLOOKUP, HLOOKUP: Ví dụ

07/06/2021

(cid:151) Tìm % Thưởng dựa theo Xếp loại ABC và chức vụ

(cid:151) Dò tìm Đơn giá trong Bảng tra 1 (Cách 2): (cid:151) =VLOOKUP(C3, $C$12:$E$14, 2, 0)

(cid:151) =VLOOKUP(F3, $I$7:$K$10, 2, 0)? (cid:151) =VLOOKUP(F3, $I$7:$K$10, 3, 0)?

Trị dò: C3

Trị dò: Xếp loại

Số thứ tự Cột trả về giá trị

Cột trả về giá trị

177

178

˜ Hàm VLOOKUP, HLOOKUP: Ví dụ ˜ Hàm VLOOKUP, HLOOKUP: Ví dụ

(cid:151) Tìm % Thưởng dựa theo Xếp loại ABC và chức vụ (Cách 1): (cid:151) =IF(D3="Trưởng phòng", VLOOKUP(F3, $I$7:$K$10, 2, 0),

(cid:151) Tìm % Thưởng dựa theo Xếp loại ABC và chức vụ (Cách 2): (cid:151) =VLOOKUP(F3, $I$7:$K$10, IF(D3="Trưởng phòng", 2, 3), 0)

˜ Hàm VLOOKUP, HLOOKUP: Ví dụ ˜ Hàm VLOOKUP, HLOOKUP: Ví dụ

VLOOKUP(F3, $I$7:$K$10, 3, 0))

179

180

07/06/2021

˜ Hàm VLOOKUP, HLOOKUP: Ví dụ

Nhóm hàm tìm kiếm

(cid:151) Tìm % Thưởng dựa theo Xếp loại ABC và chức vụ

(cid:151) =HLOOKUP()? (cid:151) Kết hợp hàm MATCH & hàm INDEX?

Trị dò: Chức vụ

tìm kiếm.

181

182

˜ Hàm MATCH: Hàm dò tìm, trả về vị trí của giá trị dò tìm trong vùng dò tìm (cid:151) Cú pháp: =MATCH(Trị dò, Vùng dò tìm, Cách dò) Chú ý: (cid:151) Trị dò: Giá trị cần tìm vị trí trong “Vùng dò tìm” (cid:151) Vùng dò tìm: Là một dãy ô liên tục trên 1 cột hoặc dòng (cid:151) Cách dò: Có 3 tùy chọn là -1, 0 hoặc 1 được chỉ định làm kiểu

˜ Hàm MATCH

(cid:151) Cách dò = 1 (hoặc để trống): Dò tìm vị trí giá trị lớn nhất

(cid:151) Cách dò = 0: Dò tìm chính xác vị trí đầu tiên của “Trị dò” trong “Vùng dò tìm”. Nếu không tìm thấy, hàm sẽ báo lỗi #N/A.

(cid:151) Chú ý: Bắt buộc “Vùng dò tìm” phải được sắp xếp theo

(cid:151) “Vùng dò tìm” không cần sắp xếp

trong “Vùng dò tìm” mà £ “Trị dò”.

thứ tự tăng dần 1, 2, 3… A, B, C…

A

D

H

I

H

I

15 1

B 14 4

C 50 5

80 4

E 16 1

F 12 3

G 23 2

6 6

15 1

1 2

A 14 2

B 16 4

C 15 5

D 19 8

E 21 11

F 22 12

G 23 16

26 17

31 21

1 2

(cid:240) Kết quả: #N/A

183

184

Ví dụ: (cid:151) =MATCH(4, A2:I2, 0) (cid:240) Kết quả: 2 (cid:151) =MATCH(7, A2:I2, 0) (cid:240) Kết quả: #N/A (cid:151) =MATCH(4, A1:I2, 0) (cid:240) Kết quả: #N/A Ví dụ: (cid:151) =MATCH(7.9, A2:I2, 1) (cid:240) Kết quả: 3 (cid:151) =MATCH(1, A2:I2) (cid:151) =MATCH(15, A1:I1, 1) (cid:240) Kết quả: …

07/06/2021

Nhóm hàm tìm kiếm

˜ Hàm MATCH

(cid:151) Cách dò = -1: Dò tìm vị trí giá trị nhỏ nhất trong “Vùng dò

(cid:151) Chú ý: Bắt buộc “Vùng dò tìm” phải được sắp xếp theo

tìm” mà ‡ “Trị dò”.

thứ tự giảm dần.

A

I

14 21

˜ Hàm INDEX: Trả về một giá trị hay tham chiếu đến một giá trị trong một bảng. (cid:151) Cú pháp: =INDEX(Bảng dò, Chỉ số dòng, Chỉ số cột) Chú ý: (cid:151) Bảng dò là một dãy ô (cid:151) Hàm trả về giá trị của ô là giao điểm của “Chỉ số dòng” và

B 16 17

C 15 16

D 19 12

E 21 11

F 22 8

G 23 5

H 26 4

31 2

1 2

(cid:151) “Chỉ số dòng” và “Chỉ số cột” phải chỉ tới một ô trong

“Chỉ số cột”

185

186

mảng, nếu không hàm sẽ báo lỗi #REF! Ví dụ: (cid:151) =MATCH(5.5, A2:I2, -1) (cid:240) Kết quả: 6 (cid:151) =MATCH(19, A1:I1, -1) (cid:240) Kết quả: #N/A

(cid:151) Tìm % Thưởng dựa theo Xếp loại ABC và chức vụ

(cid:151) G3 =INDEX(J7:K10, 2, 1)

=MATCH(F3, I7:I10, 0)

=MATCH(D3, J5:K5, 0)

20%

187

188

˜ Hàm INDEX: Ví dụ ˜ Hàm INDEX: Ví dụ

07/06/2021

˜ Hàm INDEX: Ví dụ

Nhóm hàm ngày tháng, thời gian

(cid:151) Tìm % Thưởng dựa theo Xếp loại ABC và chức vụ

(cid:151) G3 =INDEX($J$7:$K$10, MATCH(F3, $I$7:$I$10, 0),

MATCH(D3, $J$5:$K$5, 0))

(cid:240) Kết quả: 25/02/2014 21:46

(cid:151) F Hàm NOW() không tự động cập nhật ngày, giờ được. Chỉ thay đổi giá trị khi trang bảng tính được tính toán lại.

20%

˜ Hàm NOW: Hiển thị giá trị ngày/tháng/năm, giờ/phút hệ thống hiện hành của máy. (cid:151) Cú pháp: =NOW() (cid:151) Ví dụ: =NOW()

(cid:240) Kết quả: 25/02/2014

189

190

˜ Hàm TODAY: Hiển thị giá trị ngày/tháng/năm hệ thống hiện hành của máy. (cid:151) Cú pháp: =TODAY() (cid:151) Ví dụ: =TODAY()

Nhóm hàm ngày tháng, thời gian

(cid:151) Ví dụ: =DAY("20/09/2009")

(cid:240) Kết quả: 20

(cid:151) Ví dụ: Nếu ô B1 chứa giá trị ngày tháng: 20/10/2009

Công thức =MONTH(B1)

(cid:240) Kết quả: 10

˜ Hàm DAY: Cho giá trị ngày trong biểu thức ngày tháng (cid:151) Cú pháp: =DAY(Biểu thức kiểu ngày tháng) ˜ Hàm MONTH: Hiển thị giá trị tháng của biểu thức dạng ngày tháng. (cid:151) Cú pháp: =MONTH(Biểu thức kiểu ngày tháng)

Chú ý: (cid:151) Dữ liệu kiểu ngày tháng: dd/mm/yyyy hoặc dd-mm-yyyy (cid:151) Dữ liệu kiểu ngày tháng sử dụng trong hàm/công thức phải đặt trong cặp " "

(cid:151) Nếu ô B1 chứa giá trị ngày tháng: 20/10/2009 (cid:240) Kết quả: 20

(cid:151) Công thức =DAY(B1)

(cid:151) Ví dụ: Nếu ô B1 chứa giá trị ngày tháng: 20/10/2009

(cid:151) Nếu ô B2 chứa giá trị sai kiểu ngày tháng: 10/20/2009

Công thức =YEAR(B1)

(cid:240) Kết quả: 2009

(cid:151) Công thức =DAY(B2)

(cid:240) Kết quả: #VALUE!

191

192

˜ Hàm YEAR: Hiển thị giá trị năm của biểu thức dạng ngày tháng. (cid:151) Cú pháp: =YEAR(Biểu thức kiểu ngày tháng)

07/06/2021

Nhóm hàm ngày tháng, thời gian

(cid:151) Ví dụ: =HOUR("20:09:35")

(cid:240) Kết quả: 20

(cid:151) Kết quả trả về:

˜ Hàm HOUR: Hiển thị giá trị giờ của biểu thức dữ liệu dạng thời gian. (cid:151) Cú pháp: =HOUR(Biểu thức kiểu thời gian) ˜ Hàm WEEKDAY: Trả về số thứ tự của Ngày trong tuần. (cid:151) Cú pháp: =WEEKDAY(Biểu thức kiểu ngày tháng) (cid:151) Biểu thức kiểu ngày tháng có thể là địa chỉ ô chứa giá trị ngày tháng, hoặc từ kết quả của hàm TODAY

(cid:151) Ngày Chủ nhật được xem là 1 (cid:151) Ngày Thứ hai là 2, …, Thứ bảy là 7

(cid:151) hh:mm:ss (giờ:phút:giây) hoặc hh:mm (giờ:phút)

(cid:151) Ví dụ:

(cid:151) Dữ liệu kiểu thời gian sử dụng trong hàm/công thức phải

Chú ý: (cid:151) Cú pháp dữ liệu kiểu thời gian:

(cid:151) Nếu ô C1 chứa giá trị thời gian 20:09:35

(cid:151) =WEEKDAY("02/03/2014") (cid:151) =WEEKDAY("01/03/2014")

(cid:240) Kết quả: 1 (Chủ nhật) (cid:240) Kết quả: 7 (Thứ 7)

(cid:151) Công thức =HOUR(C1)

(cid:240) Kết quả: 20

193

194

đặt trong cặp " "

Nhóm hàm ngày tháng, thời gian

Lỗi nhập hàm/công thức

(cid:151) ######## : Không đủ độ rộng của ô để hiển thị dữ liệu (cid:151) #NAME? : Không xác định được ký tự trong công thức,

sai tên hàm

Công thức =MINUTE(C1)

(cid:151) #VALUE! (cid:151) #REF (cid:151) #N/A (cid:151) #DIV/0! (cid:151) #NUM!

˜ Hàm MINUTE: Hiển thị giá trị phút của biểu thức dạng thời gian. (cid:151) Cú pháp: =MINUTE(Biểu thức kiểu thời gian) (cid:151) Ví dụ: Nếu ô C1 chứa giá trị thời gian 20:09:35 (cid:240) Kết quả: 9

: Dữ liệu không đúng theo yêu cầu của hàm : Không xác định được cột/dòng trả về dữ liệu : Không có dữ liệu để tính toán : Chia cho giá trị 0 : Dữ liệu không đúng kiểu số

Công thức =SECOND(C1)

195

196

˜ Hàm SECOND: Hiển thị giá trị giây của biểu thức dạng thời gian. (cid:151) Cú pháp: =SECOND(Biểu thức kiểu thời gian) (cid:151) Ví dụ: Nếu ô C1 chứa giá trị thời gian 20:09:35 (cid:240) Kết quả: 35

07/06/2021

Biểu đồ, đồ thị

(cid:151) Ví dụ: Vẽ biểu đồ thống kê kết quả thi ˜ Biểu đồ Column

Điểm 4 điểm 5 điểm 6 điểm 7 điểm 8 điểm 9 điểm

Số lượng 10 11 26 24 9 2

197

198

Biểu đồ, đồ thị

(cid:151) Ví dụ: Vẽ biểu đồ thống kê kết quả thi ˜ Biểu đồ Bar

(cid:151) Ví dụ: Vẽ biểu đồ thống kê kết quả thi ˜ Biểu đồ Pie

Điểm 4 điểm 5 điểm 6 điểm 7 điểm 8 điểm 9 điểm

Số lượng 10 11 26 24 9 2

Điểm 4 điểm 5 điểm 6 điểm 7 điểm 8 điểm 9 điểm

Số lượng 10 11 26 24 9 2

199

200

07/06/2021

Biểu đồ, đồ thị

(cid:151) Ví dụ: Vẽ biểu đồ thống kê kết quả thi ˜ Biểu đồ Doughnut

(cid:151) Ví dụ: Vẽ đồ thị PT bậc 1: y1 = 2x + 3 và y2 = -3x + 1 ˜ Biểu đồ XY (Scatter)

Điểm 4 điểm 5 điểm 6 điểm 7 điểm 8 điểm 9 điểm

Số lượng 10 11 26 24 9 2

x -5 5

y1 -7 13

y2 16 -14

201

202

Biểu đồ, đồ thị

(cid:151) Ví dụ: Vẽ đồ thị PT bậc 2: y1 = x2 - 1 và y2 = x2 + 3 ˜ Biểu đồ XY (Scatter)

(cid:151) Ví dụ: Vẽ đồ thị PT bậc 3: y = x3 ˜ Biểu đồ XY (Scatter)

Đồ thị bậc 2

x -6 -3 0 3 6

y1 35 8 -1 8 35

y2 39 12 3 12 39

x -9 -6 -3 0 3 6 9

y -729 -216 -27 0 27 216 729

203

204

07/06/2021

Các bước dựng biểu đồ

˜ Tạo biểu đồ

˜ Xây dựng vùng dữ liệu cho biểu đồ (cid:151) Tạo bảng tính có chứa các dữ liệu cần thiết

Bước 1: (cid:151) Chọn vùng dữ liệu sử dụng để vẽ biểu đồ. (cid:151) Tab Insert (cid:240) Other Charts Ví dụ: Vẽ biểu đồ thống kê kết quả thi (cid:151) Lập bảng số liệu thống kê kết quả thi của 4 sinh viên: