Phần 3: Nhóm hàm dò tìm dữ liệu
I.
NHÓM
HÀM DÒ TÌM DỮ LIỆU
1.Hàm LOOKUP dạng mảng
Cú pháp: =LOOKUP(Giá trị tìm kiếm, Mảng tìm kiếm)
Trả về một
giá trị từ một vùng dữ liệu là 1 cột, 1 hàng hoặc một mảng.
Giá trị tìm kiếm: Giá trị cần lấy ra để tìm kiếm
Mảng tìm kiếm: Vùng tìm kiếm, có thể là 1 cột, 1
hàng hoặc một mảng.
Ví dụ:
=LOOKUP(1,A1:B9) giá trị trả về là Trung
Quốc
2.Hàm VLOOKUP
Cú pháp: =VLOOKUP(Giá trị tìm kiếm, Vùng dữ liệu, Số
thứ tự cột lấy giá trị, Kiểu tìm kiếm)
Dò tìm một
giá trị ở cột đầu tiên bên trái của một bảng dữ liệu. Nếu tìm thấy sẽ trả về
giá trị ở cùng trên một dòng với giá trị tìm thấy trên cột mà các bạn chỉ định.
Giá trị tìm kiếm: Giá trị cần lấy ra để so sánh.
Bảng tìm kiếm: Bảng chứa dữ liệu cần so sánh và
tìm kiếm. Vùng dữ liệu này phải là giá trị tham chiếu tuyệt đối.
Số thứ tự cột lấy giá trị: Số chỉ của cột muốn lấy dữ liệu.
Kiểu tìm kiếm: là 0 hoặc 1. Nếu giá trị này bằng 0
thì kiểu tìm kiếm sẽ là chính xác 100%, ngược lại, giá trị này bằng 1 thì giá
trị gần đúng được trả về. 99% nhu cầu tìm kiếm khi sử dụng hàm này là sử dụng
giá trị kiểu tìm kiếm bằng 0.
Ví dụ: =VLOOKUP(C12,$D$20:$F$25,2,0) Tìm một
giá trị bằng một giá trị ở ô C12 trong bảng từ ô D20 đến F25, và lấy giá trị
tương ứng ở cột thứ 2.
Viết hàm Vlookup cho ô I4 để lấy PCCV (phụ cấp chức vụ), với từng Chức vụ sẽ lấy PCCV
tương ứng: =VLOOKUP(G4,$B$17:$C$22,2,0)
3.Hàm HLOOKUP
Cú pháp: =HLOOKUP(Giá trị tìm kiếm, Vùng dữ liệu, Số
thứ tự hàng lấy giá trị, Kiểu tìm kiếm)
Tương tự như
hàm VLOOKUP. Nhưng thay vì tìm kiếm
bằng cách so sánh với các giá trị ở cột đầu tiên thì hàm HLOOKUP sẽ so sánh với các giá trị trong hàng đầu tiên của bảng nhập
vào và lấy giá trị theo số thứ tự hàng.
Ví dụ: Như
trong BẢNG LƯƠNG THÁNG ở trên, viết
hàm Hlookup cho ô H4 để lấy Tên phòng, với từng MANV sẽ lấy
Tên phòng tương ứng: =HLOOKUP(B4,$F$17:$I$18,2,0)
4.Hàm INDEX
Cú pháp: =INDEX(Vùng dữ liệu, Số thứ tự hàng, Số thứ
tự cột)
Trả về dữ liệu
của một ô trong Vùng dữ liệu dựa vào
Số thứ tự hàng và Số thứ tự cột.
Ví dụ: Theo
bảng phía trên: =INDEX(A1:D10,5,3) kết
quả trả về sẽ là Moscow
=INDEX(B1:B10,2) kết quả sẽ trả về là Ấn Độ
5.Hàm MATCH
Cú pháp: =MATCH(Giá trị cần tìm, Mảng tìm kiếm, Kiểu
tìm kiếm)
Trả về vị
trí tương đối của Giá trị cần tìm trong Mảng tìm kiếm.
Kiểu tìm kiếm = 1: Tìm kiếm giá trị lớn nhất trong những
giá trị nhỏ hơn Giá trị tìm kiếm. Giá trị trong mảng tìm kiếm cần được sắp xếp
tăng dần.
Kiểu tìm kiếm = 0: Tìm kiếm giá trị đầu tiên bằng với
Giá trị cần tìm. Kiểu tìm kiếm này sẽ được sử dụng nhiều nhất đối với hàm MATCH
Kiểu tìm kiếm = -1: Tìm giá trị nhỏ nhất trong những
giá trị lớn hơn Giá trị tìm kiếm.
Ví dụ: Trả về
vị trí tương đối của Quốc gia = Nga:
=MATCH(“Nga”, B1:B7,0)
Kết hợp hàm INDEX và
hàm MATCH
Hàm VLOOKUP và HLOOKUP là kiểu tìm kiếm dữ liệu một chiều nên sẽ gặp nhiều hạn chế.
Nếu biết cách kết hợp hàm INDEX và
hàm MATCH lại với nhau, chúng ta sẽ
có một cách tìm kiếm hiệu quả hơn nhiều so với việc sử dụng hàm VLOOKUP hay HLOOKUP.
Ví dụ:
Lấy ra Số
dân của Ấn Độ vào năm 2000:
=INDEX(A1:F10,MATCH("Ấn Độ",B1:B10,0),MATCH("Năm
2000",A1:F1,0))
Lấy ra Số
dân của nước có Thủ Đô là Tokyo năm 2002:
=INDEX(A1:F10,MATCH("Tokyo",C1:C10,0),MATCH("Năm
2002",A1:F1,0))
II.
MỘT
SỐ HÀM KHÁC
1. Hàm OFFSET
Cú pháp: =OFFSET(Ô xuất phát, Số dòng, Số cột, Độ
cao , Độ rộng)
Ô xuất phát: là điểm xuất phát để tạo vùng tham
chiếu mới.
Số dòng: là số dòng bên trên hoặc bên dưới Ô xuất phát
Số cột: là số cột bên trái hoặc bên phải Ô xuất phát
Độ cao: là số dòng của vùng tham chiếu cần
trả về
Độ rộng: là số cột của vùng tham chiếu cần
trả về.
2. Hàm CHOOSE
Cú pháp: =CHOOSE(Giá trị cần tìm, Giá trị 1, Giá trị
2,…)
Tìm kiếm 1
giá trị trong chuỗi giá trị đầu vào. Thường được kết hợp với hàm SUM
3. Hàm COLUMN
Cú pháp: =COLUMN(Ô tham chiếu)
Dùng để tìm
ra số thứ tự cột của một ô trong excel tình từ cột đầu tiên (cột A)
4. Hàm COLUMNS
Cú pháp: =COLUMNS(Vùng tham chiếu)
Dùng để tìm
ra số cột có trong Vùng tham chiếu
5. Hàm ROW
Cú pháp: =ROW(Ô tham chiếu)
Dùng để tìm
ra số thứ tự dòng của một ô trong excel tình từ dòng đầu tiên (dòng 1)
6. Hàm ROWS
Cú pháp: =ROWS(Vùng tham chiếu)
Dùng để tìm
ra số dòng có trong Vùng tham chiếu
Chú ý: Hàm COLUMN, COLUMNS, ROW và ROWS thường được sử dụng kết hợp với
hàm VLOOKUP và HLOOKUP
7. Hàm RAND
Cú pháp: =RAND()
Trả về một số
thực ngẫu nhiên, trong khoảng từ 0 đến 1.
Muốn lấy một
số ngẫu nhiên lớn hơn hoặc bằng 0 và nhỏ hơn n:
=RAND() * n
Muốn lấy một
số ngẫu nhiên lớn hơn hoặc bằng m và nhỏ hơn n:
=RAND() * (n - m) + m
Phần
này là phần kết thúc của bộ Giới thiệu và hướng dẫn sử dụng của một
số hàm thường dùng trong Excel.
Các hàm này đều hay gặp trong suốt quá
trình làm việc của mọi người, nếu biết kết hợp và sử dụng linh hoạt các hàm này
thì trình độ sử dụng Excel của mọi người sẽ nâng cao đáng kể.
Sign up here with your email
ConversionConversion EmoticonEmoticon