Giáo viên: Hoàng Thanh Hòa
hthoa@cofer.edu.vn
• Microsoft Excel là một phần mềm xử lý bảng tính,
một trong các ứng dụng của bộ phần mềm MS Office chạy trên hệ điều hành Windows. MS Excel chuyên du ng cho co ng ta c ke toa n, va n pho ng trong vie thie t ke ca c ba ng tí nh như ba ng lương, ba ng tho ng ke , ba ng dư toán,v.v...
c
2007, 2010, 2013
Trang 2
hthoa@cofer.edu.vn
• Phiên bản MS Excel đang sử dụng rộng rãi là Excel
• Khởi động Excel:
➢ Cách 1: Nhắp chuột vào nút Start →All
Programs → Microsoft Excel
➢ Cách 2: Nhắp đúp chuột vào biểu tượng Microsoft
Trang 3
hthoa@cofer.edu.vn
Excel có trên màn hình Desktop
Trang 4
hthoa@cofer.edu.vn
• Giao diện MS Excel:
Trên màn hình Excel 2010, ngoài những thành phần như thanh tiêu đề, các nút điều chỉnh kích thước,... còn có các thành phần như sau: • Thanh Ribbon: Có các nhóm Ribbon chính: Home,
Insert, Page Layout, Formulas, Data, Reviews, View,…
• Đe mơ mo
t Ribbon co the thư c hiện một trong hai cách
sau: ➢ Cách 1: Click chuột vào Ribbon đó. ➢ Cách 2: Nhấn tổ hợp phím Alt + Chữ cái đại diện cho
Ribbon đó.
Trang 5
hthoa@cofer.edu.vn
• Thanh công thức (Formula bar): là nơi chứa địa chỉ và
giá trị được nhập vào của ô hiện thời .
• Thanh Sheet Tab:
• Vùng làm việc (Work Space): la nơi chư a no
i dung cu a t đươ c đa nh thư tư theo ca c
ba ng tí nh, go m co 16.384 co chư ca i va 1.048.576 do ng đươ c đa nh theo so thư tư . • Nơi giao nhau của các dòng và cột hình thành nên các ô
(cell).
Trang 6
hthoa@cofer.edu.vn
• Workbook:
➢ Một File dữ liệu do Excel tạo ra tương ứng với một WorkBook. Một WorkBook có thể chứa nhiều bảng tính (WorkSheet), với số lượng phụ thuộc vào khả năng của bộ nhớ máy tính.
➢ Thông thường Excel sẽ mở sẵn 3 WorkSheet và được đặt tên là Sheet1, Sheet2, Sheet3. Ta có thể đổi tên lại các WorkSheet, bổ sung hoặc xóa bỏ các WorkSheet trong quá trình làm việc.
➢ Tên WorkBook được đặt theo quy ước đặt tên của Hệ
điều hành với phần mở rộng định sẵn là .XLSX.
Trang 7
hthoa@cofer.edu.vn
✓ Dư lie
u co mo
t ky tư na o đo kho ng pha i la so thí Excel hie u đo la kie u ky tư . Excel mặc nhiên canh trái ô.
✓ Ne u trong dư lie
u toa n la so thí Excel hie u la kie u so . Dư liệu kiểu số Excel mặc nhiên canh đều bên phải của ô.
Trang 8
hthoa@cofer.edu.vn
• Các kiểu dữ liệu trong Excel: Excel phân biệt hai loại dữ liệu cơ bản: Hằng (Constant value) và Công thức (Formula value): ➢ Ha ng bao go m kie u ky tư hoặc số.
• Các kiểu dữ liệu trong Excel:
➢ Kiểu công thức bao gồm biểu thức trong đó chứa địa chỉ hoặc tên của một số ô hoặc vùng khác của bảng tính. Để gõ công thức phải bắt đầu bằng dấu "=" hoặc dấu "+".
➢ Ngoa i hai kie u dư lie
u cơ ba n tre n, co n co kie u Logic c ca c ha m so
bao go m ca c gia trị TRUE và FALSE hoa mang gia trị logic.
➢ Excel co n co kie u nga y tha ng, kie u thơ i gian (đe lưu u na y Excel mặc nhiên canh đều
giơ ). Hai kie u dư lie bên phải của ô.
➢ Chú ý: Mỗi ô chỉ có thể chứa một loại kiểu dữ liệu.
Trang 9
hthoa@cofer.edu.vn
• Tạo file mới:
➢ Tạo một Workbook mới bằng lệnh [menu]
File/New
c click va o bie u tươ ng tre n
hoặc nhấn Ctrl-N hoa thanh co ng cu chua n ma n hí nh hie n thi ho hoa
c tư động tạo ra 1 file mới
Trang 10
hthoa@cofer.edu.vn
p ho i thoa i
• Mở file đã có sẵn:
➢ Thư c hiện lệnh [menu] File/Open hoặc nhấn
Ctrl-O hoặc click vào biểu tượng trên thanh công cụ chuẩn để mở một Workbook có sẵn trên đĩa xuất hiện hộp thoại sau:
➢ Chọn file cần mở
Trang 11
hthoa@cofer.edu.vn
• Lưu file: ➢ Le nh na y ra t hư u hie u pho ng khi ma t đie
n, hay treo ma y thì dữ liệu ta thay đổi vẫn được ghi lại đầy đủ.
Trang 12
hthoa@cofer.edu.vn
➢ Thư c hiện lệnh [menu] File/Save hoặc hoặc nhấn Ctrl-S hoặc click vào biểu tượng trên thanh công cụ chuẩn để ghi các thay đổi của dữ liệu trong file đang làm vào đĩa.
• Lưu file:
trình sẽ xuất hiện hộp hội thoại sau:
Trang 13
hthoa@cofer.edu.vn
➢ Khi thư c hiện lệnh lưu file lần đầu tiên thì chương
• Đóng file:
➢ Đe đo ng file đang đươ c mơ , thư c hiện lệnh [menu]
File/Close hoa
c click va o nút trên thanh tiều đề.
➢ Chu y : Ne u file chưa lưu va o đí a thí ma n hí nh xua t
Trang 14
hthoa@cofer.edu.vn
hie n tho ng ba o nhắc nhở:
• Di chuyển trong bảng tính:
➢ Sư du ng ca c phím mũi tên (→) đe di chuye n o n ha nh theo hươ ng ca c mũi tên, mỗi lần 1 ô.
hie
➢ Phím Page Up, Page Down đe di chuye n o hie
n ha nh
le n hay xuo ng mo
t trang ma n hình. ➢ Ctrl kết hợp với phím: Page Up hay Page Down đe di
n ha nh sang tra i hay phải một
chuye n ba ng tí nh hie bảng tính.
n ha nh về ô A1.
n ha nh về ô cuối cùng của
➢ Ctrl - Home: Di chuye n o hie ➢ Ctrl - End: Di chuye n o hie vùng dữ liệu trong sheet.
Trang 15
hthoa@cofer.edu.vn
• Nhập dữ liệu vào bảng tính:
p dư lie u va o ba ng tí nh ta co nhie u ca ch:
thông thường.
Đe nha ➢ Go ca c gia tri tư ba n phí m va o ô như soạn thảo
u bí nh thươ ng tư ba n
thanh co ng thư c, go dư lie phím.
Trang 16
hthoa@cofer.edu.vn
➢ Tro chuo t đe n o ca n nha p ro i đưa con tro le n
Excel cho phép sửa dữ liệu trong từng ô cũng bằng 2 cách. ➢ Click đôi chuột vào ô chứa giá trị cần sửa rồi mới
tiến hành sửa.
➢ Trỏ chuột đến ô cần sửa giá trị rồi nhấn phím F2. Lúc này con trỏ biến thành ( | ) nằm trong dòng dữ liệu, ta dùng phím mũi tên di chuyển con trỏ để sửa giá trị. Kết thúc sửa bằng cách nhấn phím Enter.
Trang 17
hthoa@cofer.edu.vn
• Sửa dữ liệu trong bảng tính:
• Chọn khối dữ liệu:
➢ Nếu sử dụng chuột, hãy nhấn và giữ nút trái chuột đồng thời rê chuột tới vị trí góc đối diện của khối. ➢ Đưa chuột trỏ tới góc trái trên của khối. Giữ phím Shift đồng thời click chuột tại ô đối diện của khối.
Trang 18
hthoa@cofer.edu.vn
➢ Nếu sử dụng bàn phím, hãy đưa hộp điều khiển tới ô là góc của khối. Giữ và nhấn phím Shift đồng thời sử dụng các phím mũi tên (→) đe di chuye n tơ i go c đo i die n cu a kho i ca n đa nh dấu.
t trong ca c thông số sau:
u va kiểu định dạng dữ liệu.
Trang 19
hthoa@cofer.edu.vn
• Xóa dữ liệu: Đa nh da u kho i o ca n xoa . Cách 1: Thư c hiện lệnh [menu] Home/Clear, Excel se cho cho n tie p mo ➢ All: Xoa ca dư lie ➢ Formats: Chí xoa kiểu định dạng dữ liệu ➢ Contents: Xóa dữ liệu ➢ Comments: Xoa đi ca c comments (ghi chú) ➢ Hyperlinks: Xo a bo lie n ke t tơ i ca c địa chỉ Web
xoa dữ liệu.
➢ Cách 3: Xóa dữ liệu sử dụng thanh công cụ Delete
Trang 20
hthoa@cofer.edu.vn
• Xóa dữ liệu: Đa nh da u kho i o ca n xoa . ➢ Cách 2: Nhấn phím Delete tương ư ng vơ i le nh chí
c thư c hie n ca c thao ta c sao che p, ca t da n dư lie u la
• Sao chép dữ liệu: Vie hoa n toa n tương tư như trong Word. Cách 1: ➢ Đa nh da u kho i o muo n sao che p va o [Menu]
Home/Copy hoặc nhấn đồng thời phím Ctrl-C hoa c click va o biểu tượng trên thanh công cụ chuẩn.
[Menu] Home/Paste hoặc nhấn đồng thời Ctrl-V
Trang 21
hthoa@cofer.edu.vn
➢ Di chuye n con tro đe n o ca n sao che p tơ i va o
t đe n bie n cu a
kho i o đa đươ c đa nh dấu (Lúc con trỏ xuất hiện thêm dấu mũi tên 4 hướng ở góc trên bên phải). ➢ Giữ phím Ctrl đồng thời bấm giữ trái chuột và rê tới vị trí ô cần được sao chép tới và thả tay (thả chuột trước thả phím sau).
Trang 22
hthoa@cofer.edu.vn
• Sao chép dữ liệu: Cách 2: ➢ Đa nh da u kho i o , ro i đưa con tro chuo
c
• Di chuyển dữ liệu: Cách 1: ➢ Đa nh da u kho i o muo n sao che p va o [Menu]
Home/Cut hoặc nhấn đồng thời phím Ctrl-X hoa click va o biểu tượng trên thanh công cụ chuẩn.
➢ Di chuye n con tro đe n o ca n sao che p tơ i va o
[Menu] Home/Paste hoặc nhấn đồng thời Ctrl-V hoa
Trang 23
hthoa@cofer.edu.vn
c click va o biểu tượng .
Trang 24
hthoa@cofer.edu.vn
• Di chuyển dữ liệu: Cách 2: ➢ Đa nh dấu khối. ➢ Đưa con tro tơ i bie n cu a kho i ba m giư nu t tra i t va rê tới vị trí đích rồi thả tay. chuo
nh thư c thi.
• Paste Special: Home/Paste Special, Excel sẽ hiển thị hộp thoại đe cho n le ➢ Đa nh dấu khối ô trên bảng tính, sau đo thư c hiện thao tác copy.
➢ Thư c hiện lệnh:
Home/Paste Special, ma n hình hiển thị hộp hội thoại:
Trang 25
hthoa@cofer.edu.vn
Trang 26
hthoa@cofer.edu.vn
• Paste Special: PASTE với các tùy chọn sau: ➢ All: sao chép tất cả ➢ Formulas: sao chép công thức ➢ Values: sao chép giá trị ➢ Formats: sao chép định dạng ➢ Comments: sao chép chú thích
➢ None: Không kèm theo việc tính toán nào trên dữ
liệu sắp dán vào
• Paste Special: OPERATION: sao chép có sử dụng toán tử
tương ứng ở vùng đích
Trang 27
hthoa@cofer.edu.vn
➢ Add: Cộng các giá trị của vùng nguồn vào các ô
• Dán đặc biệt:
đi các ô tương ứng của vùng nguồn.
➢ Multiply: Các ô mang giá trị của vùng đích sẽ nhân
➢ Subtract: Các ô mang giá trị của vùng đích sẽ trừ
➢ Divide: Các ô mang gia trị của vùng đích sẽ chia
cho các ô tương ứng của vùng nguồn
Trang 28
hthoa@cofer.edu.vn
với các ô tương ứng của vùng nguồn.
chuye n con tro đe n o ca n la m việc.
• Di chuyển trong bảng tính: ➢ Cách 1: Sư du ng ca c phím mũi tên (→) đe di
vie
c va click nu t tra i chuột.
➢ Cách 3: Đe di chuye n nhanh đe n mo
t o ba t ky , ta chí
➢ Cách 2: Sư du ng chuo t đe di chuye n đe n o ca n la m
c ba m phí m F5, Excel hie n ho p thoa i đe go va o
➢ Cách 4: Sư du ng ca c phím PgUp, PgDn để di
vie đi a chí o ca n chuye n tơ i va nhấn Enter.
Trang 29
hthoa@cofer.edu.vn
chuyển lên, xuo ng mo t trang ma n hình.
• Đổi tên bản tính: ➢Đe đo i te n ba ng tí nh hie
n thơ i ta thư c hiện
lệnh [menu] Home/Format/ Rename Sheet hoặc click phải chuột ở sheet muốn đổi tên sau đó chọn Rename.
Trang 30
hthoa@cofer.edu.vn
Trang 31
hthoa@cofer.edu.vn
• Chèn bảng tính mới: ➢ Thư c hiện lệnh [menu] Home/Insert/Insert Sheet ➢ Click phải chuột chọn Insert.
Trang 32
hthoa@cofer.edu.vn
• Xóa bảng tính: ➢ Thư c hiện lệnh [menu] Home/Delete Sheet hoặc click phải chuột trên sheet tương ứng chọn Delete
trang bảng tính sang vị trí mới
Trang 33
hthoa@cofer.edu.vn
• Sao chép bảng tính: ➢ Chọn tên trang bảng tính cần sao chép ➢ Giữ phím Ctrl + nhấn phím chuột trái và kéo – thả
• Sao chép bảng tính sang workbook khác: ➢ Chọn các trang bảng tính cần sao chép ➢ Trên thanh thực đơn chọn Edit-> Move or Copy
➢ Chọn bảng tính nhận các trang sao chép trong hộp
Sheet…
To book:
➢ Chọn vị trí đặt các trang bảng tính trong hộp
Before Sheet
➢ Nhấn chọn ô Create a copy ➢ Nhấn OK để hoàn tất
Trang 34
hthoa@cofer.edu.vn
• Di chuyển bảng tính: ➢ Nhắp chọn tên trang cần di chuyển ➢ Kéo – thả trang bảng tính sang vị trí mới
Trang 35
hthoa@cofer.edu.vn
• Di chuyển bảng tính sang workbook khác: ➢ Chọn các trang bảng tính cần sao chép ➢ Trên thanh thực đơn chọn Edit-> Move or Copy
➢ Chọn bảng tính nhận các trang sao chép trong hộp
Sheet…
To book:
➢ Chọn vị trí đặt các trang bảng tính trong hộp
Before Sheet
➢ Bỏ chọn ô Create a copy ➢ Nhấn OK để hoàn tất
Trang 36
hthoa@cofer.edu.vn
c click va o biểu tượng trên thanh tiêu
• Thoát khỏi Excel: ➢ Thư c hiện: [menu] Fle/Exit hoặc nhấn tổ hợp phím
Alt- F4 hoa đề.
thị thông báo
Trang 37
hthoa@cofer.edu.vn
➢ Ne u ba ng tí nh chưa lưu va o đí a thí ma n hình hiển
hộp hội thoại sau:
Trang 38
hthoa@cofer.edu.vn
• Chèn hàng/cột: ➢ Thư c hiện lệnh [menu] Home/Insert, xuất hiện
đa nh da u va dịch chuyển dữ liệu sang phải.
➢ Shift cells down: Che n ca c o tro ng va o ca c o đươ c
• Chèn hàng/cột: ➢ Shift cells right: Che n ca c o tro ng va o ca c o đươ c
đa nh da u va dịch chuyển dữ liệu xuống dưới. ➢ Entire Row: Che n ca c do ng tra ng va o ca c o đươ c đa nh da u va di ch chuye n ca c dòng xuống dưới.
➢ Entire Column: Che n ca c co t tra ng va o ca c o đươ c
Trang 39
hthoa@cofer.edu.vn
đa nh da u va di ch chuye n ca c cột sang bên phải.
• Xóa hàng/cột: ➢ [Menu] Home/Delete, Excel hie n ho
go m ca c le che n ha ng cột xuất hiện hộp thoại sau:
Trang 40
hthoa@cofer.edu.vn
p ho nh đe ta cho n lư a tương tư ho i thoa i p thoa i
chuye n va o đo dư lie
u tư ca c ô bên phải.
➢ Shift Cells Up: Xoa ca c o đươ c đa nh da u va dịch
• Xóa hàng/cột: ➢ Shift Cells Left: Xoa ca c o đươ c đa nh da u va di ch
➢ Entire Row: Xoa ca c do ng chư a ca c o đươ c đa nh
da u va di ch chuye n ca c dòng bên dưới lên.
chuyển va o đo dư lie u tư ca c ô bên dưới.
➢ Entire Column: Xoa ca c co t o đươ c đa nh da u va
Trang 41
hthoa@cofer.edu.vn
di ch chuye n ca c cột bên phải sang.
ng co
ro t, đo la : t ta i ca c đươ ng nga n ca ch
• Điều chỉnh độ rộng cột: Co hai ca ch đe đie u chí nh đo ➢ Du ng chuo t: Ke o re chuo
tương ứng trên cột tiêu đề của bảng tính. ➢ Dùng phím: [Menu] Home/Format/Column
Width, ro i cho n tie p ca c lệnh sau đây:
p chí nh xa c độ rộng cột theo mong
Trang 42
hthoa@cofer.edu.vn
+ Width: Nha muốn. + Auto Fit Column Width: Excel se tư động điều chỉnh độ rộng cột vừa khít với nội dung trong cột.
điền số tự động, ví dụ: nhập vào ô A1 số 1
• Tự động điền số thứ tự: ➢ Nhập số đầu tiên vào ô đầu tiên của vùng muốn
phải dưới của ô, con trỏ chuyển thành hình dấu “+” có mũ “+”
➢ Nhấn giữ phím Ctrl ➢ Chuyển con trỏ chuột vào hình vuông nhỏ ở góc
mong muốn
Trang 43
hthoa@cofer.edu.vn
➢ Nhấn và kéo chuột theo chiều dọc, ngang như
05TC0001, 05TC0002
• Điền tự động theo chuỗi dữ liệu: ➢ Nhập 2 chuỗi cho 2 ô đầu tiên theo quy luật, ví dụ:
của vùng vừa chọn
➢ Nhấn vào kéo chuột theo chiều dọc, ngang như
➢ Chọn 2 ô vừa nhập ➢ Đưa con trỏ chuột vào hình vuông ở góc phải dưới
Trang 44
hthoa@cofer.edu.vn
mong muốn
• Ẩn công thức trong ô: ➢ B1: chọn ô cần ẩn công thức ➢ B2: Format Cells -> Protection -> đánh dấu check
2 ô: Locked và Hidden
Trang 45
hthoa@cofer.edu.vn
➢ B3: Review -> Protect Sheet -> nhập pass ➢ B4: Nhập lại pass • Hiện công thức ➢ Chọn Review -> Unprotect Sheet -> nhập pass
• Đặt password cho bảng tính:
➢ B1: File → Info → Protect Workbook →
Encrypt with password ➢ B2: Nhập Password muốn đặt ➢ B3: Nhập lại Password • Gỡ password cho bảng tính:
➢ Chọn Review → Unprotect Sheet → nhập pass
Trang 46
hthoa@cofer.edu.vn
• Tạo công thức số học cơ bản
➢ Gõ dấu “=” trước công thức, ví dụ: =E1+F1 ➢ Nhấn Enter để kết thúc ➢ Nội dung công thức được hiển thị trên thanh
➢ Sử dụng cặp dấu “( )” để thay đổi độ ưu tiên của
các phép toán trong công thức
Trang 47
hthoa@cofer.edu.vn
Formula bar
• Các phép toán công thức số học
/
Phép chia
+
Phép cộng
^
Phép lũy thừa
-
Phép trừ
&
Phép nối chuỗi
*
Phép nhân
Trang 48
hthoa@cofer.edu.vn
Trang 49
hthoa@cofer.edu.vn
• Các phép toán so sánh
• Mức ưu tiên của các phép toán:
Trang 50
hthoa@cofer.edu.vn
➢ Phép toán trong ngoặc thực hiện trước ➢ Phép toán có độ ưu tiên cao thực hiện trước
công thức
• Nhận biết và sửa lỗi ➢ ####: không đủ độ rộng của ô để hiển thị, ➢ #VALUE!: dữ liệu không đúng theo yêu cầu của
thức
➢ #N/A: không có dữ liệu để tính toán ➢ #NUM!: dữ liệu không đúng kiểu số
Trang 51
hthoa@cofer.edu.vn
➢ #DIV/0!: chia cho giá trị 0 ➢ #NAME?: không xác định được ký tự trong công
• Địa chỉ tương đối
đích thay đổi tịnh tiến theo
➢ Là địa chỉ có dạng
• Địa chỉ tuyệt đối
ban đầu.
Trang 52
hthoa@cofer.edu.vn
➢ Là địa chỉ dạng <$cột $dòng>, VD: $A$2, $B$3… ➢ Khi sao chép đến vùng đich vẫn giữ nguyên địa chỉ
• Địa chỉ hỗn hợp
VD: $A2, B$3…
➢ Khi sao chép đến vùng đích thì chỉ có thành phần
➢ Là địa chỉ có dạng <$cột dòng> hoặc
➢ Nhấn phím F4 để thay đổi từ địa chỉ tương đối ->
tuyệt đối -> hỗn hợp.
Trang 53
hthoa@cofer.edu.vn
không có dấu “$” thay đổi.
copy công thức.
Trang 54
hthoa@cofer.edu.vn
• Địa chỉ tương đối: Là địa chỉ thay đổi trong thao tác
copy công thức.
Trang 55
hthoa@cofer.edu.vn
• Địa chỉ tuyệt đối: Là địa chỉ không bị thay đổi khi
Trang 56
hthoa@cofer.edu.vn
$
➢ Nhắp chọn ô chứa công thức muốn sao chép ➢ Đặt con trỏ chuột vào góc phải dưới của ô ➢ Biểu tượng chuột chuyển thành hình dấu thập mầu
đên nét đơn “+”
➢ Nhấn phím trái, kéo và di chuyển chuột theo dòng
Trang 57
hthoa@cofer.edu.vn
hoặc theo cột đến các ô cần sao chép
➢ Hàm (function) là tên của một thao tác đã được
định nghĩa sẵn trong Excel.
• Khái niệm:
hoặc không có đối số, ví dụ: – INT(12.5) – SUM(2,5,10) – NOW()
Trang 58
hthoa@cofer.edu.vn
➢ Tên hàm luôn đi kèm với một cặp dấu ngoặc đơn. ➢ Hàm có thể có một đối số (argument), nhiều đối số
• Cấu trúc hàm:
o Giá trị kiểu số, xâu,… o Địa chỉ ô hoặc vùng ô o Một hàm khác
Trang 59
hthoa@cofer.edu.vn
➢
• Nhập công thức vào ô:
➢ Cách 1: gõ trực tiếp vào ô theo dạng
=(đối số 1, đối số 2,…, đối số n)
➢ Cách 2:
o Chuyển con trỏ ô về ô muốn nhập công thức o Trên thanh thực đơn chọn Formulas →…. o Chọn loại hàm cần thiết o Nhấn nút OK o Nhập các đối số cần thiết o Nhấn nút OK để hoàn tất
Trang 60
hthoa@cofer.edu.vn
➢ Các đối số thường cách nhau bởi dấu “,” hoặc “;”
tùy theo thiết lập hệ thống của máy tính.
• Nhập công thức vào ô:
dạng: địa chỉ ô góc trái phía trên:địa chỉ ô góc phải phía dưới.
➢ Nếu đối số là một vùng địa chỉ thì cần ghi theo
phải có bấy nhiêu dấu “)”
Trang 61
hthoa@cofer.edu.vn
➢ Khi sử dụng dấu () thì mở bao nhiêu dấu “(“ thì
Trang 62
hthoa@cofer.edu.vn
• Nhóm hàm xử lý số • Nhóm hàm xử lý chuỗi dữ liệu • Nhóm hàm ngày giờ • Nhóm hàm logic • Nhóm hàm dò tìm • Nhóm hàm chuyển đổi kiểu • Nhóm hàm có điều kiện
1. Hàm giá trị tuyệt đối: • Cú pháp: =ABS(number) • Hàm trả về giá trị tuyệt đối của đối số • Number: là số cần trả về giá trị tuyệt đối • VD:
Trang 63
hthoa@cofer.edu.vn
➢ ABS(2) → 2 ➢ ABS(-5) → 5 ➢ ABS(A2) → 7 (A2 đang chứa công thức = 3.5 x -2)
2. Hàm tính tổng
• Cú pháp: =SUM (number 1, number 2,…) • Hàm trả về giá trị tổng của các đối số • Number: là các hằng hoặc địa chỉ tham chiếu ô,
• VD: Tổng lương ở ô E10 được tính theo công thức: =SUM(E4:E9) → 23
Trang 64
hthoa@cofer.edu.vn
miền.
3. Hàm trả về số nguyên
• Cú pháp: =INT (number) • Hàm trả về số nguyên nhỏ hơn gần nhất • Number: là các hằng hoặc địa chỉ tham chiếu ô,
• VD: INT(23.4) → 23
INT(-23.4) → -24
Trang 65
hthoa@cofer.edu.vn
miền.
4. Hàm lấy phần dư • Cú pháp: =MOD(number,divisor) • Hàm trả về số dư của phép chia • Với:
Trang 66
hthoa@cofer.edu.vn
➢ Number: số bị chia ➢ Divisor: số chia • VD: =MOD(23,10) → 3
5. Hàm tính lũy thừa • Cú pháp: =POWER(number,power) • Hàm trả về lũy thừa một số • Với:
• VD: POWER(5, 2) = 25
POWER(98.6, 3.2) = 2,401,077 POWER(4, 5/4) = 5.656854
Trang 67
hthoa@cofer.edu.vn
➢ Number: Số cần tính lũy thừa ➢ power: số mũ
Trang 68
hthoa@cofer.edu.vn
6. Hàm tính tích các số • Cú pháp: =PRODUCT(number 1; number 2;….) • Hàm trả về tích của các đối số • VD:
7. Hàm tính tổng các tích của các mảng dữ liệu • Cú pháp: =SUMPRODUCT(array1, array2, ...) • Hàm trả về tổng của tích các thành phần trong 2 mảng
cùng kích thước.
• VD:
SUMPRODUCT(A1:B3, C1:D3) = 156
• Cách tính:
156 = (3*2)+(4*7)+(8*6)+(6*7)+(1*5)+(9*3)
Trang 69
hthoa@cofer.edu.vn
8. Hàm làm tròn theo số chỉ định: • Cú pháp:=ROUND(number, num-digits) • Hàm trả về số đã được làm tròn theo chỉ định • Trong đó:
Trang 70
hthoa@cofer.edu.vn
➢ Number: Số cần làm tròn ➢ Num- digits: là số nguyên chỉ cách làm tròn với: >0: làm tròn đến số thập phân được chỉ định =0: làm tròn đến số nguyên gần nhất <0: làm tròn đến phần nguyên
9. Hàm làm tròn theo số chỉ định:
Trang 71
hthoa@cofer.edu.vn
➢ =ROUND(1234.5678, 3) → 1234.568 ➢ =ROUND(1234.5678, 2) →1234.57 ➢ =ROUND(1234.5678, 1) →1234.6 ➢ =ROUND(1234.5678, 0) →1235 ➢ =ROUND(1234.5678, -1) → 1230 ➢ =ROUND(1234.5678, -2) → 1200 ➢ =ROUND(1234.5678, -3) → 1000
10. Hàm tính giá trị trung bình
➢ Cú pháp: =AVERAGE(number1, number2…) ➢ Hàm trả về giá trị trung bình cộng của các đối số ➢ Number: là các hằng, địa chỉ tham chiếu đến ô,
Trang 72
hthoa@cofer.edu.vn
miền ➢ VD: =AVERAGE(4,10,7) → 7 =AVERAGE(E4:E9) →
11. Hàm tính số phần tử kiểu số
➢ VD:
Trang 73
hthoa@cofer.edu.vn
➢ Cú pháp: =COUNT(value1,value 2…) ➢ Hàm trả về số lượng ô có chứa các phần tử kiểu số ➢ Value: có thể chứa hay tham chiếu đến nhiều kiểu dữ liệu khác nhau, nhưng chỉ có những đối số mới được đếm
12. Hàm tính số ô có chứa giá trị
➢ VD:
Trang 74
hthoa@cofer.edu.vn
➢ Cú pháp: =COUNTA(value1,value 2…) ➢ Hàm trả về số lượng ô có chứa dữ liệu. ➢ Value: có thể chứa hay tham chiếu đến nhiều kiểu dữ liệu khác nhau, nhưng chỉ có những đối số mới được đếm
13. Hàm tìm giá trị lớn nhất
➢ Cú pháp: =MAX(number 1, number 2…) ➢ Hàm trả về giá trị lớn nhất trong các đối số ➢ Number:có thể là hằng hoặc địa chỉ tham chiếu ô,
MAX(9,23,11) → 23 MAX(F5:F14) → 10
Trang 75
hthoa@cofer.edu.vn
miền ➢ VD:
14. Hàm tìm giá trị nhỏ nhất
➢ Cú pháp: =MIN(number 1, number 2…) ➢ Hàm trả về giá trị nhỏ nhất trong các đối số ➢ Number: Có thể là hằng hay địa chỉ tham chiếu ô,
=MIN(9,23,11) → 9 =MIN(F5:F14) → 4.5
Trang 76
hthoa@cofer.edu.vn
miền ➢ VD:
15. Hàm xếp hạng
➢ Cú pháp: =RANK(number, ref, order) ➢ Hàm trả về thứ hạng của phần tử trong danh sách ➢ Trong đó:
+ number: là giá trị cần xếp hạng (VD: điểm 1 hs) + ref: bảng chứa các giá trị (VD: bảng điểm), có thể là mảng,
tham chiếu hoặc danh sách các số
+ order: Là 1 giá trị (0 or 1) cho biết cách thức sắp xếp: - Nếu order =0 thì số lớn có thứ hạng nhỏ - Nếu order =1 thì số lớn có thứ hạng lớn
Trang 77
hthoa@cofer.edu.vn
➢ VD hàm RANK:
Công thức tại ô E2:
Dựa vào cột tổng điểm, xếp hạng các thí sinh
Trang 78
hthoa@cofer.edu.vn
=RANK(D2,$D$2:$D$5,0) → 1
Trang 79
hthoa@cofer.edu.vn
16. Hàm chuyển đổi số đo góc sang radian: • Cú pháp: =RADIANS(angle) • Hàm trả về số đo radian của một góc • Với: angle là số đo của góc tính theo độ • VD: ➢ =RADIANS(270) → 4.71239 (3𝜋/2) ➢ =RADIANS(180) → 3.14159 (𝜋) ➢ =RADIANS(90) → 1.570796 (𝜋/2)
Trang 80
hthoa@cofer.edu.vn
17. Hàm chuyển đổi số đo góc từ radian sang độ: • Cú pháp: =DEGREES(angle) • Hàm trả về số đo độ của một góc • Với: angle là số đo của góc tính theo radian • VD: ➢ =DEGREES (4.71239) (3𝜋/2) → 270 ➢ =DEGREES (3.14159) (𝜋) → 180 ➢ =DEGREES (1.570796 (𝜋/2) →90
Trang 81
hthoa@cofer.edu.vn
18. Hàm tính Sin của một số: • Cú pháp: =SIN(number) • Hàm trả về giá trị sine của một số • Number: là số đo góc tính theo radian • VD: =SIN(4.71239) → -1 ( Sine của góc 270°) =SIN(1.570796) → 1 ( Sine của góc 90°)
Trang 82
hthoa@cofer.edu.vn
19. Hàm tính Cosin của một số: • Cú pháp: =COS(number) • Hàm trả về giá trị Cosin của một số • Number: là số đo góc tính theo radian • VD: =COS(3,14159) → -1 ( Cosin của góc 180°) =COS(0) → 1 ( Cosin của góc 0°)
20. Hàm tính Tang của một số: • Cú pháp: =TAN(number) • Hàm trả về giá trị Cosin của một số • Number: là số đo góc tính theo radian • VD:
=TAN(0.523598) → 0.577350 → 2 /2 ( Tang của góc 30°)
Trang 83
hthoa@cofer.edu.vn
=TAN(0.785398) → 1 ( Tang của góc 45°)
Trang 84
hthoa@cofer.edu.vn
21. Hàm tính radian của một góc từ giá trị Sine: • Cú pháp: =ASIN(number) • Hàm trả về giá trị radian 1 góc nằm trong khoảng từ -𝜋/2 đến 𝜋/2 (arcsine) • Number: là Sine một góc • VD: =ASIN(0.5) → 0.523598 (𝜋/6) =ASIN(1) → 1.570796 (𝜋/2)
22. Hàm tính radian của một góc từ giá trị Cosin: • Cú pháp: =ACOS(number) • Hàm trả về giá trị radian 1 góc nằm trong khoảng từ
0 đến 𝜋 (arccosine)
Trang 85
hthoa@cofer.edu.vn
• Number: là Cosin một góc • VD: =ACOS(-0.5) → 2.094395 (2𝜋/3) =ACOS(1) → 0
23. Hàm tính radian của một góc từ giá trị Tang: • Cú pháp: =ATAN(number) • Hàm trả về giá trị radian 1 góc nằm trong khoảng từ
Trang 86
hthoa@cofer.edu.vn
-𝜋/2 đến 𝜋/2 (arctang) • Number: là Tang một góc • VD: =ATAN(1.732050) → 1.0471975 (𝜋/3) =ATAN(1) → 0.7854 (𝜋/4)
Trang 87
hthoa@cofer.edu.vn
1. Hàm đếm ký tự • Cú pháp: =LEN(text) • Hàm trả về số ký tự trong một chuỗi văn bản • Text: chuỗi văn bản cần đếm số ký tự • VD: =LEN(“Việt Nam”) →8 =LEN(A2) →8 (ô A2 chứa chuỗi “Việt Nam”)
Trang 88
hthoa@cofer.edu.vn
2. Hàm LEFT • Cú pháp: =LEFT(text,[num-chars]) • Hàm trích xuất phần bên trái một chuỗi • Với: ➢ Text: chuỗi văn bản cần trích xuất ký tự ➢ Num-chars: số ký tự cần trích ra • VD: =LEFT(“Việt Nam”,4) → Việt =LEFT(A2,7) → Việt Na (ô A2 chứa chuỗi “Việt Nam”)
Trang 89
hthoa@cofer.edu.vn
3. Hàm RIGHT • Cú pháp: =RIGHT(text,[num-chars]) • Hàm trích xuất phần bên phải một chuỗi • Với: ➢ Text: chuỗi văn bản cần trích xuất ký tự ➢ Num-chars: số ký tự cần trích ra • VD: =RIGHT(“Việt Nam”,3) → Nam =RIGHT(A2,7) → iệt Nam (ô A2 chứa chuỗi “Việt Nam”)
4. Hàm MID • Cú pháp: =MID(text,start-num,[num-chars]) • Hàm trích xuất chuỗi con từ 1 chuỗi • Với: ➢ Text: chuỗi văn bản cần trích xuất ký tự ➢ Start: vị trí bắt đầu trích xuất từ bên trái ➢ Num-chars: số ký tự cần trích ra • VD: =MID(“Việt Nam vô địch”,6,3) → Nam =MID(A2,7,5) → am vô (ô A2 chứa chuỗi “Việt Nam vô địch”)
Trang 90
hthoa@cofer.edu.vn
Trang 91
hthoa@cofer.edu.vn
5. Hàm FIND • Cú pháp: =FIND(find-text,within-text,[start-num]) • Hàm trả về vị trí của 1 chuỗi con trong chuỗi văn bản • Với: ➢ Find-text: chuỗi con cần tìm vị trí ➢ Within-text: chuỗi văn bản chứa chuỗi con cần tìm ➢ Start-num: vị trí bắt đầu tìm • VD: =FIND(“Nam”,“Việt Nam vô địch”,1) → 6 =FIND(“vô địch”,A2,1) → 10 (ô A2 chứa chuỗi “Việt Nam vô địch”)
Trang 92
hthoa@cofer.edu.vn
Trang 93
hthoa@cofer.edu.vn
Trang 94
hthoa@cofer.edu.vn
6. Hàm TRIM • Cú pháp: =TRIM(text) • Hàm xóa những khoảng trắng vô ích trong văn bản • Text: chuỗi văn bản • VD: =TRIM(“ Việt Nam”) → Việt Nam =TRIM(A2) → Việt Nam
Trang 95
hthoa@cofer.edu.vn
7. Hàm LOWER • Cú pháp: =LOWER(text) • Hàm chuyển text thành chữ in thường • Text: chuỗi văn bản • VD: =LOWER(“Học Hành”) → học hành
Trang 96
hthoa@cofer.edu.vn
8. Hàm UPPER • Cú pháp: =UPPER(text) • Hàm chuyển text thành chữ in hoa • Text: chuỗi văn bản • VD: =UPPER(“Học Hành”) → HỌC HÀNH
9. Hàm PROPER • Cú pháp: =PROPER(text) • Hàm chuyển các chữ cái đầu thành in hoa, chữ khác
in thường
Trang 97
hthoa@cofer.edu.vn
• Text: chuỗi văn bản • VD: =PROPER(“họC hÀnh”) → Học Hành
[,instance_num])
10.Hàm SUBSTITUTE • Cú pháp: SUBSTITUTE(text, old_text, new_text
• Hàm thay thế một chuỗi bằng chuỗi khác ➢ text: chuỗi văn bản gốc, cần được xử lý ➢ old_text: chuỗi văn bản cần được thay thế ➢ new_text: chuỗi văn bản sẽ thay thế vào ➢ instance_num: số lần thay thế old_text bằng
Trang 98
hthoa@cofer.edu.vn
new_text
Hàm SUBSTITUTE • Ví dụ: đế thay số 2007 bằng 2008 trong câu
Expense Budget for 2007
• Dùng công thức như sau:
=SUBSTITUTE("Expense Budget for 2007", "2007", "2008") → Expense Budget for 2008
Trang 99
hthoa@cofer.edu.vn
Trang 100
hthoa@cofer.edu.vn
13.Hàm CONCATENATE • Cú pháp: CONCATENATE(text 1, text 2,….) • Nối nhiều chuỗi lại với nhau. • VD: CONCATENATE(“Giải pháp Excel”; “ ”;“-”;“Công cụ tuyệt vời của bạn” → Giải pháp Excel –Công cụ tuyệt vời của bạn
Trang 101
hthoa@cofer.edu.vn
• Những quy định khi nhập dữ liệu ngày tháng:
o Nếu <30 thì cộng vào 2000 o Nếu >= 30 và <100 thì cộng vào 1900
➢ Giá trị năm có giá trị từ 1900 đến 9999 ➢ Nếu nhập số năm với 2 con số thì:
vào có các dạng hiển thị khác nhau
Trang 102
hthoa@cofer.edu.vn
➢ Nhập không đúng sẽ chuyển thành dạng text ➢ Tùy theo định dạng hệ thống mà ngày tháng nhập
toán các giá trị ngày giờ
• Những quy định khi nhập dữ liệu ngày tháng: ➢ 24h ngày 31/12/1899 được chọn làm mốc tính
➢ Tất cả giá trị ngày giờ đều được quy đổi thành 1 giá
➢ Giá trị số là số ngày cách mốc quy ước 31/12/1899 ➢ VD: 06/12/1999 quy ra 36500 ngày 28/05/2005 quy ra 38500 ngày
Trang 103
hthoa@cofer.edu.vn
trị số để tính toán
Trang 104
hthoa@cofer.edu.vn
1. Hàm DATE • Cú pháp: =DATE(year,month,day) • Hàm trả về giá trị ngày tháng • VD: =DATE(2008,1,1) → 01/01/2008 =DATE(108,1,1) → 01/01/2008 =DATE(2008,12,32) → 01/01/2009 =DATE(08,14,01) → 01/02/2009
2. Hàm NOW • Cú pháp: =NOW() • Hàm trả về giá trị ngày tháng năm hiện hành • Tùy theo kiểu định dạng mà kết quả thể hiện khác
• VD: =NOW() → 09/16/14 với định dạng mm/dd/yy =NOW() → 16/09/2014 với định dạng dd/mm/yyyy
Trang 105
hthoa@cofer.edu.vn
nhau
Trang 106
hthoa@cofer.edu.vn
3. Hàm DAY • Cú pháp: =DAY(serial-number) • Hàm trả về giá trị ngày trong biểu thức ngày tháng • Serial-number: là biểu thức ngày tháng • VD: =DAY(“01/02/2008”) → 1 =DAY(A2) → 31 (ô A2 chứa biểu thức 31/01) =DAY(39000) → 10
Trang 107
hthoa@cofer.edu.vn
4. Hàm MONTH • Cú pháp: =MONTH(serial-number) • Hàm trả về giá trị tháng trong biểu thức ngày tháng • Serial-number: là biểu thức ngày tháng • VD: =MONTH(“01/02/2008”) → 2 =MONTH(A2) → 1 (ô A2 chứa biểu thức 31/01) =MONTH(39000) → 10
Trang 108
hthoa@cofer.edu.vn
5. Hàm YEAR • Cú pháp: =YEAR(serial-number) • Hàm trả về giá trị năm trong biểu thức ngày tháng • Serial-number: là biểu thức ngày tháng • VD: =YEAR(“01/02/2008”) → 2008 =YEAR(A2) → 2014 (ô A2 chứa biểu thức 31/01) =YEAR(39000) → 2006
6. Hàm HOUR • Cú pháp: =HOUR(serial-number) • Hàm trả về giá trị là số giờ lẻ chưa tròn 1 ngày (từ 0
• Serial-number: là biểu thức thời gian hoặc 1 số nào
đến 23)
đó chỉ số ngày (0 Trang 109 hthoa@cofer.edu.vn • VD:
=HOUR(“10:30 AM”) → 10
=HOUR(“10:30 PM”) → 22
=HOUR(0.5) →12 7. Hàm MINUTE
• Cú pháp: =MINUTE(serial-number)
• Hàm trả về giá trị là số phút chưa tròn 1 giờ (từ 0 đến 59) Trang 110 hthoa@cofer.edu.vn • Serial-number: là biểu thức thời gian
• VD:
=MINUTE(“10:30 AM”) → 30
=MINUTE(“10:45 PM”) → 45
=MINUTE(0.32) →40 8. Hàm SECOND
• Cú pháp: =SECOND(serial-number)
• Hàm trả về giá trị là số giây chưa tròn 1 phút (từ 0 đến 59) Trang 111 hthoa@cofer.edu.vn • Serial-number: là biểu thức thời gian
• VD:
=SECOND(0.32) →48 Trang 112 hthoa@cofer.edu.vn 9. Hàm TIME
• Cú pháp: =TIME(hour,minute,second)
• Hàm trả về giá trị biểu thức thời gian
• VD:
=TIME(13,40,48)→ 1:40 PM
=TIME(7,40,48) → 7:40 AM giữa hai khoảng thời gian theo tùy chọn. • Với: - firstdate: là Ngày bắt đầu của khoảng thời gian cần
tính toán
- Enddate: là Ngày kết thúc của khoảng thời gian cần
tính toán
- Option: là tùy chọn, xác định kết quả tính toán sẽ trả
về trong công thức: Trang 113 hthoa@cofer.edu.vn + "d": Hàm sẽ trả về số ngày giữa hai khoảng thời gian.
+ "m": Hàm sẽ trả về số tháng (chỉ lần phần nguyên) giữa hai khoảng thời gian. + "y": Hàm sẽ trả về số năm (chỉ lần phần nguyên) giữa hai khoảng thời gian. + "yd": Hàm sẽ trả về số ngày lẻ của năm (số ngày chưa tròn năm) giữa hai khoảng thời gian. + "ym": Hàm sẽ trả về số tháng lẻ của năm (số tháng chưa tròn năm) giữa hai khoảng thời gian. + "md": Hàm sẽ trả về số ngày lẻ của tháng (số ngày chưa tròn tháng) giữa hai khoảng thời gian. Trang 114 hthoa@cofer.edu.vn 11.Hàm WEEKDAY
• Cú pháp: =WEEKDAY(serial_number, return_type)
• Hàm trả về một giá trị, là số thứ tự của Ngày trong Trang 115 hthoa@cofer.edu.vn tuần.
• Với:
- Serial_number : là một biểu thức thời gian,
Serial_number có thể là Number hay Value_date
- return_type : là tùy chọn để xác định kiểu giá trị sẽ
trả về trong công thức 11.Hàm WEEKDAY
- return_type :
+ 1 hoặc để trống : Ngày chủ nhật được xem là 1, Ngày thứ bảy là 7. Trang 116 hthoa@cofer.edu.vn + 2 : Ngày thứ hai được xem là 1, Ngày chủ nhật là 7.
+ 3 : Ngày thứ hai được xem là 0, Ngày chủ nhật là 6. 1. Hàm AND
• Cú pháp: =AND(logical 1, logical 2…)
• Hàm trả về kết quả đúng (TRUE) nếu tất cả các đối số đều đúng sai. • Logical: là các biểu thức điều kiện
• VD: =AND(A2>=20,A2<26) Trang 117 hthoa@cofer.edu.vn • Hàm trả về kết quả sai (FALSE) nếu ít nhất 1 đối số 2. Hàm OR
• Cú pháp: =OR(logical 1, logical 2…)
• Hàm trả về kết quả sai (FALSE) nếu tất cả các đối số đều sai, đúng. • Logical là các biểu thức điều kiện
• VD: =OR(A2>=20,B2<10) Trang 118 hthoa@cofer.edu.vn • Trả về kết quả đúng (TRUE) nếu ít nhất 1 đối số 3. Hàm điều kiện
• Cú pháp: =IF(logical_test, value_if_true, value_if_false) có giá trị TRUE • Hàm trả về kết quả là value_if_true nếu logical_test • Hàm trả về kết quả là value_if_false nếu logical_test • VD:
Công thứ ô C2 là:
=IF(B2>=5,“đậu”,“rớt”) Trang 119 hthoa@cofer.edu.vn có giá tri FALSE • Hàm IF có thể được viết lồng vào nhau khi có nhiều kết • Công thức tại ô E5: quả trong phép tính Trang 120 hthoa@cofer.edu.vn =IF(D5>=5,”Đậu”,IF(D5<=3,”Học lại”,”Thi lại”)) Trang 121 hthoa@cofer.edu.vn kiện này bằng hàm AND hoặc hàm OR • Công thức tính kết quả tại ô F5:
=IF(AND(D5+E5>=10,D5<>0,E5<>0),"Đậu","Rớt")
=IF(OR(D5+E5<10,D5=0,E5=0),“Rớt”,“Đậu”) Trang 122 hthoa@cofer.edu.vn điều kiện dò tìm • Với:
➢ lookup_value: giá trị dò tìm
➢ Table_array: là bảng chứa giá trị cần để dò tìm
➢ Col_index: số thứ tự cột từ bên trái sang trong bảng
➢ Option_lookup: xác định kiểu dò tìm Trang 123 hthoa@cofer.edu.vn ➢ Sắp xếp vùng đối chiếu chỉ nhận giá trị logic 0 hoặc 1, nếu bỏ qua thì nhận giá trị 1 1. Hàm dò tìm theo cột
• Option_lookup: ➢ Nếu để giá trị 0: dò tìm chính xác, lấy giá trị đầu tiên ➢ Nếu để giá trị 1: kiểu dò tìm tương đối, nếu không tìm được, nếu không tìm thấy trả về #N/A Trang 124 hthoa@cofer.edu.vn tìm thấy trả về giá trị nhỏ hơn gần nhất với giá trị cần
dò tìm ➢ Tìm điểm LT của sinh viên Cúc: =Vlookup(“Cúc”,A1:C5,2,0) ➢ Tìm điểm TH của sinh viên Lan: =Vlookup(“Lan”,A1:C5,3,0) Trang 125 hthoa@cofer.edu.vn =HLOOKUP(lookup_value,table_array,row_index_num,optio
n_lookup) • Hàm trả về giá trị của ô nằm trên HÀNG nào đó thỏa mãn điều kiện dò tìm • Với: ➢ lookup_value: giá trị dò tìm
➢ Table_array: là bảng chứa giá trị cần để dò tìm
➢ row_index: số thứ tự hàng từ trên xuống trong bảng
➢ Option_lookup: xác định kiểu dò tìm Trang 126 hthoa@cofer.edu.vn ➢ Tìm điểm LT của sinh viên Cúc:
=Hlookup(“Điểm LT”,A1:C5,4,0)
➢ Tìm điểm TH của sinh viên Lan:
=Hlookup(“Điểm LT”,A1:C5,3,0) Trang 127 hthoa@cofer.edu.vn =MATCH(lookup_value,lookup_array,match_type)
• Hàm trả về vị trí của giá trị dò tìm trong cột (hàng)
• Với: ➢ Lookup_value: là giá trị dùng để dò tìm, có thể là một
số, một chuỗi, một giá trị logic hay một tham chiếu.
➢ Lookup_array: là vùng dò tìm, có thể là một cột hoặc một dòng, hoặc một mảng các giá trị. 3. Hàm MATCH
• Cú pháp: Trang 128 hthoa@cofer.edu.vn ➢ Match_type: là tùy chọn để xác định kiểu dò tìm. ➢ =0: dò tìm chính xác
➢ =1 (hoặc để trống): tìm vị trí của giá trị lớn nhất 3. Hàm MATCH
• Match-type: nhận 3 giá trị tương ứng nhỏ hơn giá trị cần tìm. Trang 129 hthoa@cofer.edu.vn ➢ =-1: Tìm giá trị nhỏ nhất lớn hơn giá trị cần tìm 4. Hàm INDEX
• Cú pháp: =INDEX(array,row_num,col_num)
• Hàm trả về giá trị trong một bảng (mảng)
• Với: trả về. ➢ Col_num: là số chỉ cột của giá trị trong mảng cần trả ➢ Array: là một dãy ô hay một hằng mảng.
➢ Row_num: là số chỉ dòng của giá trị trong mảng cần Trang 130 hthoa@cofer.edu.vn về. định dạng được chỉ định • Với: ➢ Number: là số cần chuyển đổi sang dạng text
➢ Format-text: kiểu định dạng số hoạc ngày tháng năm • Một số kiểu định dạng: ➢ Định dạng số: ###,000 ; #,##0.00…..
➢ Định dạng ngày tháng: dd/MM/yy; d/m/yyyy; dd/MM/yyyy hh:mm:ss…. Trang 131 hthoa@cofer.edu.vn 1. Hàm TEXT: 23/02/88 00:00 Trang 132 hthoa@cofer.edu.vn ➢ TEXT(123,“$#,##0.00”) → $123.00
➢ TEXT(1234,“#,##0.00”) → 1,234.00
➢ TEXT(12345,“###,000”) → 12,345
➢ TEXT(23/02/1988,“mm/dd/yy”) → 02/23/88
➢ TEXT(23/02/1988,“dd/mm/yy hh:mm:ss”) → kiểu số 2. Hàm VALUE:
• Cú pháp: VALUE(text)
• Hàm chuyển một chuỗi đại diện cho một số thành Trang 133 hthoa@cofer.edu.vn • Text: phải là dạng số hoặc ngày tháng
• VD:
=VALUE(“$123.00”) →123
=VALUE(“01/15/14”) → 41654 điều kiện cho trước 1. Hàm SUMIF:
• Cú pháp: SUMIF(range,criteria,sum-range)
• Hàm trả về tổng của các ô trong một vùng thỏa mãn Trang 134 hthoa@cofer.edu.vn • Với:
➢ Range: dãy các ô có chứa điều kiện
➢ Criteria: điều kiện để tính tổng
➢ Sum-range: vùng chứa các ô cần tính tổng kiện cho trước 2. Hàm COUNTIF:
• Cú pháp: COUNTIF(range,criteria)
• Hàm trả về số lượng các ô trong vùng thỏa mãn điều Trang 135 hthoa@cofer.edu.vn • Với:
➢ Range: dãy các ô để đếm
➢ Criteria: điều kiện để đếm thỏa mãn điều kiện cho trước 3. Hàm AVERAGEIF:
• Cú pháp: AVERAGEIF(range,criteria,average-range)
• Hàm trả về giá trị trung bình của các ô trong một vùng • Với:
➢ Range: dãy các ô có chứa điều kiện
➢ Criteria: điều kiện để tính giá trị trung bình
➢ Average-range: vùng chứa các ô cần tính giá trị trung Trang 136 hthoa@cofer.edu.vn bình điều kiện cho trước 4. Hàm SUMIFS:
• Cú pháp:
= SUMIFS(sum_range, criteria_range1, criteria1,
criteria_range2, criteria2, ...)
• Hàm trả về tổng các ô trong một vùng thỏa nhiều Trang 137 hthoa@cofer.edu.vn • Với:
➢ Sum-range: Vùng chứa các ô cần tính tổng
➢ Criteria_range1: Vùng chứa điều kiện 1
➢ Criteria1: Điều kiện 1 5. Hàm COUNTIFS:
• Cú pháp:
= COUNTIFS(range1, criteria1, range2, criteria2, ...)
• Hàm đếm các ô trong vùng thỏa nhiều điều kiện cho trước. Trang 138 hthoa@cofer.edu.vn • Với:
➢ Range 1: Vùng chứa điều kiện 1
➢ Criteria1: Điều kiện 1 nhiều điều kiện cho trước. 6. Hàm AVERAGEIFS:
• Cú pháp:
= AVERAGEIFS(average_range, criteria_range1,
criteria1, criteria_range2, criteria2, ...)
• Hàm trả về giá trị trung bình của các ô thỏa mãn Trang 139 hthoa@cofer.edu.vn • Với:
➢ Average_range : Vùng cần tính giá trị trung bình.
➢ Criteria_range 1: Vùng chứa điều kiện 1.
➢ Criteria 1: Điều kiện 1 Trang 140 hthoa@cofer.edu.vn • Sau khi đánh dấu khối ô bảng tính cần định dạng, ta
thực hiện lệnh [Menu] Home/Format/Format
Cells (hoặc tổ hợp phím Ctrl -1) để tạo định dạng
tổng thể. Excel xuất hiện hộp hội thoại bao gồm các
thẻ định dạng riêng biệt: Trang 141 hthoa@cofer.edu.vn • Định dạng số: Trong hộp Category chọn kiểu dữ
liệu cần trình bày và chọn tiếp các thông số tương
ứng với kiểu dữ liệu đã chọn:
➢ General: định dạng chung. Với kiểu định dạng này,
dữ liệu kiểu ký tự gõ vào sẽ được canh hàng bên
trái và dữ liệu kiểu số sau khi gõ vào bảng tính sẽ
được canh hàng bên phải ô. Chọn tiếp các thông số sau: Trang 142 hthoa@cofer.edu.vn ➢ Number: dùng để định dạng các số thông thường. • Định dạng số: chấm thập phân. ✓ Decimal places: khai báo số chữ số sau dấu ✓ Use 1000 Separators(,): click chọn khi muốn ✓ Negative numbers: chọn cách định dạng số âm. ➢ Currency: Chọn cách hiện ký hiệu tiền tệ.
➢ Date: Chọn cách hiện giá trị ngày tháng. Trang 143 hthoa@cofer.edu.vn dùng dấu phẩy để phân cách hàng nghìn hộp Type Trang 144 hthoa@cofer.edu.vn • Định dạng số:
➢ Percentage: Chọn cách hiện ký hiệu phần trăm.
➢ Fraction: Chọn cách hiện giá trị phân số.
➢ Scientific: Chọn cách hiện số dưới dạng khoa học.
➢ Text: định dạng số theo kiểu ký tự.
➢ Special: định dạng theo mã vùng và số điện thoại
➢ Custom: tuỳ biến theo khuôn dạng được gõ trong kiểu định dạng khác nhau. Ta có thể sử dụng những
mẫu định dạng đã có sẵn hoặc có thể tự định nghĩa
những kiểu định dạng mới theo ý thích.
➢ Sử dụng những mẫu định dạng có sẵn: Ở khung
Category chọn mục Date, và chọn kiểu hiển thị bên
khung Type. Excel thường hiển thị ngày tháng theo
dạng tổng quát m/d/yy (tháng/ngày/năm) Trang 145 hthoa@cofer.edu.vn • Excel cho phép ta hiển thị ngày tháng theo nhiều ➢ Sử dụng những định dạng tự định nghĩa: Nếu
không vừa ý với các dạng có sẵn, ta có thể tạo ra
một dạng hiển thị riêng bằng cách sau: Trang 146 hthoa@cofer.edu.vn Chọn mục Start/Control Panel xuất hiện hộp thoại
Control Panel chọn vào biểu tượng xuất hiện hộp thoại
sau: Trang 147 hthoa@cofer.edu.vn • Chọn vào nút Trang 148 hthoa@cofer.edu.vn Additional settings
xuất hiện hộp thoại
tiếp theo chọn vào tab
Date thì hộp thoại có
dạng sau: kiểu ngày rút gọn) ➢ Short date sample: ví dụ về cách thể hiện định • Trong khung Short date (định nghĩa kiểu thể hiện dạng ngày đã được định nghĩa. nghĩa, ta có thể click chọn vào đây để chọn kiểu
định nghĩa mới hoặc có thể gõ trực tiếp vào khung
này. ➢ Date separator: Dấu phân cách giữa ngày, tháng và ➢ Short date format: kiểu ngày đang được định Trang 149 hthoa@cofer.edu.vn năm (dấu - hoặc /) kiểu ngày chi tiết)
➢ Long date sample: ví dụ về cách thể hiện định • Trong khung Long date (định nghĩa kiểu thể hiện dạng ngày đã được định nghĩa. nghĩa, ta có thể click chọn vào đây để chọn kiểu
định nghĩa mới hoặc có thể gõ trực tiếp vào khung
này. Trang 150 hthoa@cofer.edu.vn ➢ Long date format: kiểu ngày đang được định Trang 151 hthoa@cofer.edu.vn • Mục Horizontal: Dùng để canh chỉnh theo chiều ngang ➢ General: Trở về cách sắp xếp ngầm định
➢ Left: Canh trái các ô.
➢ Center: Canh giữa các ô.
➢ Right: Canh phải các ô.
➢ Justify: Canh đều hai bên các ô. • Mục Vertical: Dùng để canh chỉnh theo chiều dọc ➢ Top: Ký tự hiện sát đỉnh ô
➢ Center: Ký tự hiện giữa ô.
➢ Bottom: Ký tự hiện ở đáy ô. Trang 152 hthoa@cofer.edu.vn được chọn
➢ Wrap text: Khi click chọn thì độ rộng cột sẽ cố
định, dữ liệu nhập vào tự động xuống hàng. ➢ Shrink to fit: khi click chọn vào mục này thì dữ liệu • Mục Text control:Điều kiển dữ liệu trong khối ô trọng ô sẽ co lại vừa bằng kích thước của ô. các ô đã chọn lại thành một ô. ➢ Merge cells: Khi click chọn thì Excel sẽ sát nhập liệu Trang 153 hthoa@cofer.edu.vn • Hộp Orientation: Dùng để chỉnh độ nghiêng của dữ Email: hthoa@cofer.edu.vn
Website: https://sites.google.com/site/thanhhoakhcb/
Di động: 039693516710. Hàm DATEDIF
• Cú pháp: =DATEDIF(firstdate,enddate,option)
• Hàm trả về một giá trị, là số ngày, số tháng hay số năm
10. Hàm DATEDIF
3. Hàm điều kiện
• Trong hàm IF, khi có nhiều điều kiện thì phải liên kết các điều
1. Hàm dò tìm theo cột
• Cú pháp:
=VLOOKUP(lookup_value,table_array,col_index_num,option_l
ookup)
• Hàm trả về giá trị của ô nằm trên cột nào đó thỏa mãn
1. Hàm dò tìm theo cột
• Ví dụ:
2. Hàm dò tìm theo hàng
• Cú pháp:
2. Hàm dò tìm theo hàng
• Ví dụ:
1. Hàm TEXT:
• Cú pháp: TEXT(number,format-text)
• Hàm chuyển đổi một số thành dạng văn bản (text) theo
Mọi chi tiết xin vui lòng liên hệ:
ThS. Hoàng Thanh Hòa
![Bài tập Tin học ứng dụng [nâng cao/cơ bản]](https://cdn.tailieu.vn/images/document/thumbnail/2025/20250824/shenkilltv@gmail.com/135x160/60111756087501.jpg)
