Giáo trình Bảng tính Excel - Chu Thị Hoa Hồng (Phần 2)
BÀI 6: SỬ DỤNG HÀM TÌM KIẾM VÀ THAM CHIẾU
MÃ BÀI: MĐ 08_06
Giới thiệu:
Để trả về một giá trị hay một tham chiếu đến một giá trị trong phạm vi bảng
hay vùng dữ liệu phù hợp yêu cầu tính toán, chúng ta sẽ tìm hiểu một số hàm tìm
kiếm và tham chiếu.
Mục tiêu:
- Trình bày đƣợc ý nghĩa, cú pháp và cách sử dụng hàm tìm kiếm và tham
chiếu
- Thực hiện đƣợc lồng ghép các hàm với nhau để giải bài toán
- Áp dụng các hàm tìm kiếm và tham chiếu để giải bài toán
- Chấp hành tốt nội quy xƣởng thực hành, đảm bảo an toàn lao động và tác
phong công nghiệp.
- Rèn luyện tính kiên trì, cẩn thận, chính xác.
1. SỬ DỤNG HÀM VLOOKUP
a. Ý nghĩa
Hàm vlookup thƣờng dùng để điền giá trị tự động vào bảng dữ liệu từ bảng tham
chiếu (nếu có).
b. Cú pháp:
VLOOKUP(Lookup Value, Table array, Col idx num, [range lookup])
hoặc
= VLOOKUP (Giá trị dò tìm, Bảng tham chiếu, Cột tham chiếu, Cách dò tìm)
Trong đó:
- Giá trị dò tìm: Là giá trị nằm trong bảng dữ liệu, dùng để dò tìm trong cột đầu
tiên bên trái của bảng tham chiếu. Giá trị dò tìm có thể là một số, một chuỗi, một công
thức trả về giá trị hay tham chiếu đến một ô nào đó dùng làm giá trị dò tìm.
- Bảng tham chiếu: Là một bảng dùng để dò tìm giá trị đƣợc tổ chức theo cột.
Trong đó cột đầu tiên bên trái đƣợc dùng để dò tìm và không chứa các giá trị trùng
nhau, các cột tiếp theo chứa những giá trị tƣơng ứng để tham chiếu. Bảng tham chiếu
có thể là tham chiếu đến một vùng nào đó hay tên trả về vùng dò tìm. Bảng tham
chiếu có địa chỉ tuyệt đối.
- Cột tham chiếu: Là số thứ tự của cột (tính từ trái qua phải) trong bảng tham
chiếu chứa giá trị mà ta muốn trả về, là một số trong khoảng từ 1 đến số cột tối đa của
bảng tham chiếu.
- Cách dò tìm: Nhận một trong hai giá trị 0 hoặc 1(mặc định)
Nếu Cách dò tìm = 0 thì Giá trị dò tìm đƣợc so sánh tuyệt đối (Giống y hệt
nhau) giữa bảng dữ liệu và bảng tham chiếu
83
Nếu Cách dò tìm = 1 thì Giá trị dò tìm đƣợc so sánh tƣơng đối (Chỉ cần
giống ký tự đầu tiên) giữa bảng dữ liệu và bảng tham chiếu. Đồng thời cột đầu tiên
bên trái bảng tham chiếu phải đƣợc sắp xếp theo thứ tự tăng dần.
Chú ý:
- Nếu giá trị Lookup value nhỏ hơn giá trị nhỏ nhất trong cột đầu tiên của bảng Table
array, nó sẽ thông báo lỗi #N/A.
Ví dụ: =VLOOKUP(F11,$C$20:$D$22,2,0)
Tìm một giá trị bằng giá trị ở ô F11 trong cột thứ nhất, và lấy giá trị tƣơng ứng ở
cột thứ 2.
2. SỬ DỤNG HÀM HLOOKUP
a. Ý nghĩa
Hàm hlookup thƣờng dùng để điền giá trị tự động vào bảng dữ liệu từ bảng tham
chiếu (nếu có).
b. Cú pháp:
HLOOKUP(Lookup Value, Table array, Row idx num, [range lookup])
hoặc
= VLOOKUP (Giá trị dò tìm, Bảng tham chiếu, Hàng tham chiếu, Cách dò tìm)
Trong đó:
- Giá trị dò tìm: Là giá trị nằm trong bảng dữ liệu, dùng để dò tìm trong hàng
đầu tiên bên trái của bảng tham chiếu. Giá trị dò tìm có thể là một số, một chuỗi, một
công thức trả về giá trị hay tham chiếu đến một ô nào đó dùng làm giá trị dò tìm.
- Bảng tham chiếu: Là một bảng dùng để dò tìm giá trị đƣợc tổ chức theo hàng.
Trong đó hàng đầu tiên bên trái đƣợc dùng để dò tìm và không chứa các giá trị trùng
nhau, các hàng tiếp theo chứa những giá trị tƣơng ứng để tham chiếu. Bảng tham
chiếu có thể là tham chiếu đến một vùng nào đó hay tên trả về vùng dò tìm. Bảng
tham chiếu có địa chỉ tuyệt đối.
- Hàng tham chiếu: Là số thứ tự của hàng (tính từ trên xuống dƣới) trong bảng
tham chiếu chứa giá trị mà ta muốn trả về, là một số trong khoảng từ 1 đến số hàng
tối đa của bảng tham chiếu.
- Cách dò tìm: Nhận một trong hai giá trị 0 hoặc 1(mặc định)
Nếu Cách dò tìm = 0 thì Giá trị dò tìm đƣợc so sánh tuyệt đối (Giống y hệt
nhau) giữa bảng dữ liệu và bảng tham chiếu
Nếu Cách dò tìm = 1 thì Giá trị dò tìm đƣợc so sánh tƣơng đối (Chỉ cần giống
ký tự đầu tiên) giữa bảng dữ liệu và bảng tham chiếu. Đồng thời hàng đầu tiên bên
trái bảng tham chiếu phải đƣợc sắp xếp theo thứ tự tăng dần.
3. SỬ DỤNG HÀM MATCH
a. Ý nghĩa
Hàm Match cho kết quả là vị trí tƣơng đối của giá trị tìm (lookup_value) trong
một mảng (lookup_array) kết với một giá trị chỉ định theo thứ tự đặc biệt
84
(match_type).
b. Cú pháp:
MATCH(lookup_value,lookup_array,match_type)
Các tham số của hàm:
- Lookup_value: Là giá trị mà ta dùng để tìm giá trị mà ta mong muốn trong một
mảng (lookup_array). Lookup_value có thể là giá trị (số, text, hoặc giá trị logic) hoặc
là một tham chiếu đến một số, text, hoặc giá trị logic.
- Lookup_array: Mảng chứa các giá trị tìm kiếm. Lookup_array có thể là mảng
hoặc tham chiếu mảng.
- Match_type: Là một con số xác định cách dò tìm: -1 hoặc 0 (false) hoặc 1
(true). Nếu match_type đƣợc bỏ qua thì mặc nhiên đƣợc hiểu là 1.
+ Match_type = -1: Tìm giá trị nhỏ nhất trong lookup_array, lớn hơn hay bằng
giá trị dò. Lookup_array phải đƣợc sắp xếp theo thứ tự giảm dần.
+ Match_type = 1 (true): Tìm giá trị lớn nhất trong lookup_array, nhỏ hơn hay
bằng giá trị dò. Lookup_array phải đƣợc sắp xếp theo thứ tự tăng dần.
+ Match_type = 0 (false): Tìm giá trị đầu tiên bằng giá trị tìm trong
lookup_array. Lookup_array không cần sắp theo thứ tự nào cả.
• Nếu tìm không thấy, hàm cho giá trị là #N/A
• Không phân biệt giữa chữ hoa và chữ thƣờng.
• Nếu match_type = 0 và lookup_value là text thì trong lookup_value có thể chứa
các ký tự đại diện nhƣ:
+ Dấu ?: Thay cho một ký tự bất kỳ.
+ Dấu *: Thay cho tổ hợp (nhiều) ký tự bất kỳ.
Ví dụ:
Hàm MATCH: cho vị trí xuất hiện của Cell đƣợc tìm thấy trong mảng, chứ
không phải là giá trị của Cell đó.
4. SỬ DỤNG HÀM INDEX
a. Ý nghĩa
Trả về một giá trị hay một tham chiếu đến một giá trị trong phạm vi bảng hay
vùng dữ liệu.
b. Cú pháp:
INDEX(Array,Row_num,Col_num)
Các tham số:
- Array: Là một vùng chứa các ô hoặc một mảng bất biến.
Nếu Array chỉ chứa một hàng và một cột, tham số Row_num hoặc Col_num
tƣơng ứng là tùy ý.
Nếu Array có nhiều hơn một hàng hoặc một cột thì chỉ một Row_num hoặc
Col_num đƣợc sử dụng.
- Row_num: Chọn lựa hàng trong Array. Nếu Row_num đƣợc bỏ qua thì
Col_num là bắt buộc.
85
- Col_num: Chọn lựa cột trong Array. Nếu Col_num đƣợc bỏ qua thì Row_num là
bắt buộc.
Bài tập thực hành của học viên:
Kiến thức:
Câu 1: Trình bày ý nghĩa, cú pháp các.
Câu 2: Trình bày các sai sót, nguyên nhân và cách khắc phục khi sử dụng các hàm tìm
kiếm và tham chiếu.
BÀI THỰC HÀNH SỐ 1
Cho bảng dữ liệu sau:
TÌNH HÌNH XUẤT HÀNG NÔNG SẢN QUÝ 1 - 2018
Mã Hàng
Tên Hàng
Số Lƣợng Thành Tiền Tiền Giảm Phải Trả
GTD-1 Gạo trắng dài
GTH-2 Gạo thơm
GNH-3 Gạo Nàng Hƣơng
100
245
278
189
256
289
275
GTT-2
Gạo trắng tròn
GNH-3 Gạo Nàng Hƣơng
GTD-2 Gạo trắng dài
GTH-3 Gạo thơm
Bảng Tra
Loại
Mã Hàng
Tên Hàng
1
2
3
GTD
GTH
GNH
Gạo trắng dài
Gạo thơm
Gạo Nàng Hƣơng
Gạo trắng tròn
20
15
10
30
40
45
20
40
60
75
30
50
GTT
Yêu Cầu:
Câu 1: Dựa vào 3 ký tự bên trái của Mã Hàng và tra trong Bảng Tra, hãy điền tên cho
các mặt hàng
Câu 2: Hãy tính Thành Tiền, biết rằng Thành Tiền = Số Lƣợng * Đơn Giá, trong đó:
Đơn Giá của mỗi mặt hàng thì tra trong Bảng Tra dựa vào Mã Hàng
Câu 3: Tính Tiền Giảm cho mỗi loại hàng biết rằng Tiêfn Giảm = Thành Tiền *
%Giảm, trong đó: % Giảm cho mỗi mặt hàng dựa vào ký tự đầu tiên bên phải, nếu:
+ Bằng 1: thì được giảm 3%
+ Bằng 2: thì được giảm 5%
86
+ Bằng 3: thì được giảm 7%
Câu 4: Tính Phải Trả = Thành Tiền - Tiền Giảm
Câu 5: Tính Tổng Cộng cho các cột Số Lƣợng, Thành Tiền, Tiền Giảm và Phải Trả
BÀI THỰC HÀNH SỐ 2
Cho các bảng tính sau:
Bảng đơn giá nhập (Đơn vị: $)
JAP
KOR
CHI
USA
Nơi
Mã hàng
SX
CPU
RAM
MAI
120,5
24
100
23
53,5
80,5
19,5
49
120
23
52
56
Bảng mô tả nơi sản xuất
Mã nƣớc
Tên nƣớc
JAP
KOR
CHI
Trung
Quốc
USA
Nhật
Hàn Quốc
Mỹ
Bảng mô tả tên hàng
Tên hàng
Chíp vi xử lý (P4-2Ghz)
RAM (Kingston-128MB)
Mainboard (Intel-845)
Mã hàng
CPU
RAM
MAI
Đơn
giá
xuất
Tên Nơi
hàng SX
Số
lƣợng
Thành Thanh
TT
Mã hàng
Ngày xuất
tiền
toán
1
2
3
4
5
6
7
8
CHI-CPU
KOR-CPU
JAP-RAM
CHI-MAI
USA-RAM
CHI-RAM
JAP-RAM
JAP-CPU
02/01/2003
10/10/2003
31/12/2004
20/11/2004
12/12/2003
15/01/2004
13/10/2003
20/10/2003
25
10
50
40
30
15
24
26
87
9
10
JAP-MAI
CHI-MAI
10/10/2004
30/10/2004
25
45
Bảng thống kê
Số lần bán
Mã hàng
CPU
Số tiền
MAI
RAM
Yêu cầu:
1. Điền tên hàng vào cột Tên hàng dựa vào 3 kí tự cuối của mã hàng và đƣợc tra ở
bảng mô tả tên hàng.
2. Điền dữ liệu vào cột Nơi sản xuất dựa vào 3 kí tự đầu của mã hàng và đƣợc tra ở
bảng mô tả nơi sản xuất.
3. Tính đơn giá xuất, biết rằng: Đơn giá xuất tăng 10% so với đơn giá nhập. (đơn
giá nhập đƣợc tra ở bảng đơn giá nhập).
4. Tính Thành tiền = Đơn giá xuất * Số lƣợng.
5. Tính Thanh toán = Thành tiền + Thuế. Trong đó thuế đƣợc tính nhƣ sau:
Nếu ngày xuất thuộc năm 2003 thì thuế là 3% Thành tiền.
Nếu ngày xuất thuộc năm 2004 thì thuế là 5% Thành tiền
BÀI THỰC HÀNH SỐ 3
BẢNG KÊ TÌNH HÌNH MUA BÁN NGUYÊN LIỆU
Mã
Tên
Nghiệp
Vụ
Hình
Số
Thành
Đại Lý
Hàng
Hàng
Thức
Lƣợng
Tiền
Ánh Sáng
Hoàng Hôn
Bình Minh
Sức Sống
N
X
D
X
N
Mua
Mua
Bán
Mua
Bán
L
S
S
L
S
245
321
157
134
423
Vƣơn Lên
Tổng Cộng
Mua
Bán
Mã Hàng Tên Hàng
S
L
S
L
88
X
D
N
Xăng
Dầu
1.200
1.700
2.000
1.500
2.000
2.500
1.800
2.000
2.300
2.000
2.400
2.600
Nhớt
Yêu cầu:
1. Điền số liệu cho cột Tên Hàng dựa vào Mã Hàng ở Bảng 1 và tra ở Bảng 2
2. Tính Thành Tiền = Số Lƣợng * Đơn Giá, trong đó Đơn Giá đƣợc tra ở Bảng 2
dựa vào Mã Hàng, Nghiệp Vụ (Mua hoặc Bán) và Hình Thức (Sĩ hoặc Lẽ) ở Bảng
1
3. Hãy hoàn thành Bảng Thống Kê sau:
BẢNG THỐNG KÊ
Tên Hàng
Xăng
Tổng Số Lƣợng
Tổng Số Tiền
Dầu
Nhớt
Tổng Cộng
BÀI THỰC HÀNH SỐ 4
Cho các bảng tính sau:
Tổ SX
Loại Xe
Số Lƣợng
Ngày Giao
Kết Quả Tiền Thƣởng
1
2
3
DREAM
WAVE
150
500
300
10/03/2003
15/03/2003
25/03/2003
FUTURE
BẢNG TRA THÔNG TIN
Loại Xe
Chỉ Tiêu
600
Kế Hoạch Hoàn Thành
WAVE
FUTURE
DREAM
02/03/2003
20/03/2003
30/03/2003
200
120
89
Yêu cầu
1. Điền giá trị cho cột Kết Quả với yêu cầu sau:
- Nếu tổ sản xuất nào có Số Lƣợng sản phẩm lắp ráp > Chỉ Tiêu đƣợc giao và ngày
giao sản phẩm trƣớc ngày Hoàn Thành Kế Hoạch đã đề ra thì ghi ―Vƣợt Kế
Hoạch‖.
- Ngƣợc lại thì để trống
Trong đó: Chỉ tiêu Số Lƣợng sản phẩm lắp ráp phải hoàn thành và thời gian hoàn
thành kế hoạch của mỗi tổ sản xuất thì dựa vào ký hiệu loại xe ở Bảng 1 và tra ở
Bảng
2. Tính giá trị cho cột Tiền Thƣởng biết rằng mức Tiền Thƣởng sẽ là 1000000 cho
tổ sản xuất nào lắp ráp vƣợt mức kế hoạch đƣợc giao.
3. Thao tác định dạng và kẻ khung cho bảng tính
BÀI THỰC HÀNH SỐ 5
BẢNG THỐNG KÊ TÌNH HÌNH CÁC CHUYẾN BAY
Hành
Quốc
Tịch
Giá
Vé
Bảo
Phụ
Ngày Bay Tuyến Bay
Phải Trả
Khách
Hiểm
Thu
Ngọc
Jean
VN
15/12/2002
Hà Nội
ANH
05/06/2002 Hồng Kông
Rooney
NHẬT 01/04/2002 Hàn Quốc
Hồ Chí
Thanh
Jenifer
VN
09/08/2002
07/02/2002
Minh
MỸ
Thái Lan
Tổng Cộng
Giá vé (ĐVT:USD)
(Tính từ ngày đến ngày)
Tuyến Bay
Ngày 1 đến
Ngày 11 đến
Ngày 21 đến
10
20
31
Hà Nội
Hồ Chí Minh
Thái Lan
22
27
25
29
28
41
199
289
210
268
237
272
Hồng Kông
90
Hàn Quốc
256
241
261
Yêu cầu:
1. Tính Giá Vé cho mỗi chuyến bay, biết rằng Giá Vé đƣợc tra trong Bảng 2 dựa vào
Tuyến Bay, Ngày Bay ở Bảng 1
2. Tính tiền Bảo Hiểm cho mỗi hành khách biết rằng số tiền Bảo Hiểm đƣợc tính
bằng công thức:
Giá Vé * %Bảo Hiểm, trong đó %Bảo Hiểm đƣợc quy định theo Quốc Tịch nhƣ
sau: - Quốc Tịch là VN thì mức Bảo Hiểm là 3%.
- Còn các Quốc Tịch khác là 5%.
3. Tính giá trị cho cột Phụ Thu biết ràng:
- Nếu Ngày Bay trúng ngày Chủ Nhật hoặc Thứ Bảy thì tính 2%Giá Vé
- Nếu Ngày Bay trúng vào các ngày khác thì không tính Phụ Thu
4. Tính số tiền Phải Trả cho mỗi hành khách theo công thức:
Phải Trả = Giá Vé + Bảo Hiểm + Phụ Thu
5. Thực hiện thao tác định dạng và kẻ khung cho bảng tính
BÀI THỰC HÀNH SỐ 6
BẢNG THEO DÕI TIỀN BẢO HIỂM
Mã Số
Mức
Bệnh Viện
Điều Trị
Tiền
Ghi
Kết Quả
Nhân Viên
Lƣơng
Bảo Hiểm
Chú
Tham Khảo
Q1-001
Q2-002
Q3-003
Q1-004
Q2-005
Q3-006
400.000
500.000
600.000
550.000
450.000
700.000
Tổng Cộng
Đa khoa
Quận 3
Đa khoa
C17
Trúng tuyến
Trái tuyến
Trái tuyến
Trái tuyến
Trúng tuyến
Trúng tuyến
C17
Quận 3
BẢNG TRA
Mức
Mã
Bệnh Viện
Bảo Hiểm
Tuyến
Bảo Hiểm
Q1
Q2
5%
Đa khoa
10%
C17
91
Q3
15%
Quận 3
Yêu cầu:
1. Tính Tiền Bảo Hiểm cho mỗi bệnh nhân biết rằng Tiền Bảo Hiểm = Mức Lƣơng
* Mức Bảo Hiểm, trong đó:
Mức Bảo Hiểm của từng ngƣời bệnh đƣợc quy định bởi hai ký tự đầu tiên bên trái
của Mã Số Nhân Viên và tra theo Bảng Tra
2. Tính Tổng Tiền Bảo Hiểm
3. Tạo giá trị cho cột Ghi Chú: Nếu bệnh nhân nào mua Bảo hiểm và điều trị tại cùng
một Bệnh viện thì ghi là"Trúng tuyến", ngƣợc lại là "Trái tuyến"
Trong đó: Bệnh viện mà Bệnh nhân mua Bảo hiểm thì dựa vào hai ký tự đầu tiên bên
trái của Mã Số Nhân Viên và tra theo Bảng Tra
4. Định dạng và kẻ khung cho bảng tính
BÀI THỰC HÀNH SỐ 7
KẾT QUẢ THI TUYỂN SINH
Số
Diện
Chính
Sách
Tên
Mã
Điểm
Tổng
Kết
Báo
Toán Lý Hoá
Thí Sinh Trƣờng
ƣu tiên Điểm
Quả
Danh
A01
B01
B02
C01
D01
D02
Thanh
Le
A
C
B
C
C
A
K
CLS
CTB
CTB
K
Viet
Hoang
Thy
Thai
MN
Bảng Tra Điểm Thi
Bảng Điểm Chuẩn
Số Báo Danh
Toán
Lý
Hoá
Mã Trƣờng
Điểm Chuẩn
A
B
C
D01
B01
D02
C01
A01
10
10
9
9
8
7
4
5
1
4
16 17,5 19
10
0
7
3
6
92
B02
8
6
4
Yêu cầu
1. Dựa vào Số Báo Danh của mỗi thí sinh và tra ở Bảng Tra Điểm Thi hãy xác định
giá trị cho các cột điểm Toán, Lý và Hoá
2. Xác định Điểm ƣu tiên cho mỗi thí sinh, biết rằng:
- Nếu thuộc diện chính sách là CLS (Con liệt sỹ) thì đƣợc 1,5 điểm
- Nếu thuộc diện chính sách là CTB (Con thƣơng binh) thì đƣợc 1
điểm
- Nếu thuộc diện chính sách là MN (Miền núi) thì đƣợc 0,5 điểm
- Còn ngoài ra không thuộc các diện thì 0 điểm
3. Tính Tổng Điểm biết rằng Tổng Điểm = Điểm Toán + Điểm Hoá + Điểm Lý +
Điểm ƣu tiên
4. Xác định giá trị cho cột Kết Quả, biết rằng: Nếu: Tổng Điểm >= Điểm Chuẩn
thì ghi là "Đậu", còn ngƣợc lại thì ghi là "Rớt", trong đó Điểm Chuẩn của mỗi
trƣờng thì tra theo Bảng Điểm Chuẩn dựa vào giá trị ở cột Mã Trƣờng
Yêu cầu về đánh giá kết quả học tập:
- Sử dụng hàm tìm kiếm và tham chiếu đúng công thức, đúng quy trình.
- Vận dụng đƣợc hàm tìm kiếm và tham chiếu để tính toán đƣợc yêu cầu cho
bảng tính phù hợp.
- Có tính sáng tạo, tỷ mỉ, cẩn thận trong công việc.
Ghi nhớ:
- Ý nghĩa, cú pháp và lƣu ý khi sử dụng hàm tìm kiếm và tham chiếu.
93
BÀI 7: SẮP XẾP VÀ TRÍCH LỌC DỮ LIỆU
MÃ BÀI: MĐ 08_07
Giới thiệu:
Với 1 vùng dữ liệu đã có, chúng ta có thể sắp xếp dữ liệu, lọc dữ liệu hay trích
1 vùng dữ liệu thỏa mãn điều kiện cho trƣớc.
Mục tiêu:
- Trình bày đƣợc các khái niệm về cơ sở dữ liệu trong bảng tính.
- Trình bày đƣợc các phƣơng pháp để sắp xếp, trích lọc dữ liệu.
- Sắp xếp đƣợc dữ liệu thỏa mãn điều kiện để giải bài toán.
- Lọc, rút trích đƣợc dữ liệu thỏa mãn điều kiện để giải bài toán.
- Chấp hành tốt nội quy xƣởng thực hành, đảm bảo an toàn lao động và tác
phong công nghiệp.
- Rèn luyện tính kiên trì, cẩn thận, chính xác.
1. SẮP XẾP DỮ LIỆU
Giả sử chúng ta có bảng số liệu dƣới đây:
Bảng 7- 1. Ví dụ sắp xếp
Yêu cầu cần sắp xếp theo Họ tên
Để sắp xếp dữ liệu thực hiện theo các bƣớc sau đây:
Bước 1: Nhấp chọn vào cột cần sắp xếp trong vùng dữ liệu.
nhấp chọn vào ô bất kỳ trong cột Họ tên này.
Bước 2: Từ thanh công cụ Ribbon nhấp chọn Tab Data sau đó tìm tới nhóm
Sort & Filter.
94
Trong nhóm này có thể chọn biểu tƣợng Sort A to Z để sắp xếp dữ liệu giảm
dần, hoặc chọn biểu tƣợng Sort Z to A để sắp xếp dữ liệu tăng dần. Ngoài ra cũng có
thể chọn nút Custum Sort để có nhiều tùy chọn sắp xếp hơn.
Hình 7- 1. Hộp thoại sort
Để bổ xung các điều kiện sắp xếp nhấp chọn nút Add Level sau đó chọn trƣờng
cần lọc trong ô Sort By tiếp theo là kiểu sắp xếp trong ô Order.
Hình 7- 2. Nút lệnh Add Level của hộp thoại sort
2. TRÍCH LỌC TỰ ĐỘNG
Lọc là cách dễ dàng và nhanh chóng để tìm và làm việc với dữ liệu trong vùng
dữ liệu đƣợc chọn. Vùng dữ liệu đƣợc lọc hiển thị các mẫu tin đáp ứng tiêu chuẩn mà
chỉ định cho một trƣờng. Excel cung cấp hai lệnh: AutoFilter dùng để lọc tự động với
tiêu chuẩn đơn giản và Advanced Filter dùng để lọc cho tiêu chuẩn phức tạp hơn.
Việc lọc không giống việc sắp xếp, lọc mẫu tin có tác dụng che tạm thời các mẫu
tin mà không muốn hiển thị và nó không sắp xếp trật tự các mẫu tin đó. Khi đã lọc các
mẫu tin, có thể chỉnh sửa, định dạng các mẫu tin này trong cơ sở dữ liệu mà không ảnh
hƣởng đến các mẫu tin khác.
Lọc tự động bằng AutoFilter
95
Lọc tự động là cách lọc đơn giản và nhanh nhất. Trong đó có thể chọn những
tiêu chuẩn do Excel phát hiện thấy có trong trƣờng dữ liệu hoặc cũng có thể sử dụng
các câu lệnh đi theo nó. Các bƣớc thực hiện nhƣ sau:
- Chọn tới vùng dữ liệu cần lọc
- Từ thanh công cụ Ribbon nhấp chọn Tab Data tìm tới nhóm lệnh Sort &
Filter. Khi đó trên vùng dữ liệu ở dòng đầu tiên mỗi ô sẽ xuất hiện một biểu tƣợng
hình tam giác nhỏ phía góc phải.
Bảng 7- 2. Ví dụ trích lọc tự động
- Bây giờ muốn lọc theo cột nào nhấp chọn biểu tƣợng hình tam giác ở cột đó.
Một danh sách xuất hiện trong menu nhanh nhƣ hình dƣới đây:
Hình 7- 3. Minh họa lệnh Text Filters
96
+ Sort A to Z: Sắp xếp dữ liệu tăng dần
+ Sort Z to A: Sắp xếp dữ liệu giảm dần
+ Sort by Color: Sắp xếp dữ liệu theo mầu nền của Cell.
Hình 7- 4. Minh họa sắp xếp theo màu
Khi di chuyển chuột đến mục này một menu nhanh xuất hiện bên trái liệt kê các
mầu nền của Cell có trong cột hiện thời có thể chọn một mầu bất kỳ nào đó để sắp xếp
dữ liệu theo mầu đó.
+ Filter by Color: Lọc dữ liệu theo mầu nền của Cell
Hình 7- 5. Minh họa lọc dữ liệu theo màu
Khi di chuyển chuột đến mục này một menu nhanh xuất hiện bên trái liệt kê các
mầu nền của Cell có trong cột hiện thời có thể chọn một mầu bất kỳ nào đó để lọc dữ
97
liệu theo mầu đó.
Giả sử chúng ta chọn mầu vàng nhƣ hình trên kết quả sẽ đƣợc nhƣ hình dƣới
đây:
Bảng 7- 3. Bảng kết quả lọc dữ liệu theo màu
Lưu ý:
+ Trong trƣờng hợp muốn lọc ra những Cell không có mầu nền chọn mục No
Fill.
+ Nếu muốn hủy giá trị lọc nhấp chọn lại mục đã đƣợc đánh dấu lọc trƣớc đó.
- Text Filters: Lọc dữ liệu dạng Text
Hình 7- 6. Minh họa lọc dữ liệu dạng text
Khi di chuyển tới mục này một danh sách xuất hiện bên tay trái có thể chọn:
+ Equals…: Lọc những giá trị bằng… Sau khi nhấp chọn mục này một hộp thoại
xuất hiện nhƣ hình dƣới đây:
98
Hình 7- 7. Hộp thoại Custom AutoFilter
Nhập giá trị cần tìm ở hộp thoại đầu tiên bên tay trái, giả sử chúng ta muốn tìm
nhân viên có họ tên là ―Đặng Minh Tuấn‖.
Cuối cùng nhấp nút Ok để hoàn tất, kết quả sẽ đƣợc nhƣ hình dƣới đây:
Bảng 7- 4. Ví dụ kết quả lọc dữ liệu dạng text
Lưu ý: Ngoài việc gõ trực tiếp giá trị cần lọc cũng có thể nhấp vào ô hình tam
giác cuối ô nhập này để chọn tính xác giá trị cần lọc.
+ Does not equal…: Lọc những giá trị không bằng… Sau khi nhấp chọn mục này
hộp thoại Custom AutoFilter xuất hiện nhƣ hình dƣới đây:
Vẫn với danh sách trƣớc chúng ta muốn lọc ra những nhân viên có họ tên bắt đầu
là ―Nguyễn‖ thiết lập thông số nhƣ sau:
Cuối cùng nhấp Ok để hoàn tất, kết quả sẽ đƣợc nhƣ hình dƣới đây:
+ Begins With…: Lọc dữ liệu bắt đầu với… Sau khi nhấp chọn mục này hộp
thoại Custom AutoFilter xuất hiện nhƣ hình dƣới đây:
Vẫn với danh sách trƣớc chúng ta muốn lọc ra những nhân viên có họ tên khác
99
―Nguyễn Mạnh Hùng‖ thiết lập thông số nhƣ sau:
Cuối cùng nhấp Ok để hoàn tất, kết quả trong danh sách không còn Nguyễn
Mạnh Hùng nhƣ hình dƣới đây:
Bảng 7- 5. Ví dụ lọc kiểu Begins with
+ End With…: Lọc dữ liệu kết thúc với… Sau khi nhấp chọn mục này hộp thoại
Custom AutoFilter xuất hiện nhƣ hình dƣới đây:
Vẫn với danh sách trƣớc chúng ta muốn lọc ra những nhân viên có họ tên kết
thúc bằng ―Hằng‖ thiết lập thông số nhƣ sau:
Cuối cùng nhấp Ok để hoàn tất, kết quả sẽ đƣợc nhƣ hình dƣới đây:
+ Contains…: Lọc dữ liệu chứa…
+ Does not contains…: Lọc dữ liệu không chứa…
+ Custom Filter…: Tùy biến điều kiện lọc.
100
Thực chất tất cả các lựa chọn trên đều nằm trong hai hộp chọn đầu tiền bên tay
trái.
Đây còn gọi là các điều kiện lọc, tuy nhiên với những gì đã hƣớng dẫn mỗi lần
lọc chúng ta chỉ có thể lấy dữ liệu ra với một điều kiện (bằng, không bằng, chứa,
không chứa,…). Trong phần này chúng ta sẽ học cách lọc dữ liệu với hai điều kiện kết
hợp nhau giả sử nhƣ tên chứa từ ―Thị‖ và kết thúc bằng ―Thanh‖, hay tên không chƣa
từ ―Tâm‖ hoặc chứa từ ―Tiến‖. Để làm đƣợc điều này chúng ta quy lại với của sổ
Custom AutoFilter và thiết lập nhƣ hình dƣới đây:
Giả sử chúng ta lọc những nhân viên có họ là ―Nguyễn‖ nhƣng tên khác ―Lâm‖.
Trong hai mục And và Or tích chọn And vì đây là hai điều kiện sảy ra đồng thời.
Kết quả cuối cùng sẽ nhƣ hình dƣới đây:
- Trong phần tiếp theo: Microsoft Excel 2013 cung cấp cho chúng ta các tùy
chọn khác.
Đầu tiên là hộp nhập tìm kiếm có thể gõ giá trị cần tìm kiếm tại đây để thu gọn
điều kiện loc. Sau đó tích chọn các kết quả cần lọc.
101
Hoặc có thể nhấp chọn (Select All) để chọn tất cả. Cuối cùng nhấp Ok để hoàn
tất.
3. LỌC NÂNG CAO
Lọc dữ liệu với cơ chế lọc tự động bằng AutoFilter chỉ có thể tiến hành trên một
trƣờng dữ liệu nào đó nên còn rất nhiều hạn chế. Cơ chế lọc cao cấp bằng Advanced
Filter sẽ cho phép lọc trên nhiều trƣờng khác nhau gọi là vùng tiêu chuẩn lọc (Criteria
Range), nó tạo điều kiện cho việc lọc dữ liệu đa dạng hơn. Để tiện theo dõi phần này
chúng ta sẽ làm việc với bảng dữ liệu dƣới đây:
102
Tải về để xem bản đầy đủ
67 trang
23500
Free
Bạn đang xem 20 trang mẫu của tài liệu "Giáo trình Bảng tính Excel - Chu Thị Hoa Hồng (Phần 2)", để tải tài liệu gốc về máy hãy click vào nút Download ở trên
File đính kèm:
- giao_trinh_bang_tinh_excel_chu_thi_hoa_hong_phan_2.pdf
