Bạn đang xem bài viết Nhóm Hàm Dò Tìm Dữ Liệu Trong Excel – Cách Sử Dụng Và Ví Dụ Minh Hoạ (Phần 6) được cập nhật mới nhất trên website Hoisinhvienqnam.edu.vn. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất.
Trong nhóm hàm dò tìm dữ liệu, có thể nói hàm INDEX và hàm MATCH là hai hàm cũng khá là thông dụng. Các bạn có thể sử dụng kết hợp hai hàm này để thay thế hàm VLOOKUP, HLOOKUP, với chức năng lấy giá trị ở các hàng và cột thuộc các bảng khác nhau thậm chí còn mang lại kết quả hơn hẳn. Tuy nhiêu, nhiều bạn chưa biết cách sử dụng và vận dụng như thế nào trong bảng tính excel.
Trong bài viết sau đây, chúng tôi sẽ hướng dẫn các bạn cách sử dụng và ví dụ minh họa về hai hàm, INDEX, MATCH trong excel.
3. Hàm INDEX, MATCH
3.1. Hàm INDEX
Hàm INDEX trả về một giá trị hoặc tham chiếu tới một giá trị từ trong một bảng hoặc phạm vi.
+ Hàm INDEX dạng mảng:
Cú Pháp: Index (Array,Row_num,[Column_num])
Trong Đó:
- Array: Phạm vi ô hoặc một hằng số mảng, bắt buộc
- Row_num: Chọn hàng trong mảng mà từ đó trả về một giá trị
- Column_num: Chọn cột trong mảng mà từ đó trả về một giá trị.
Bắt buộc phải có ít nhất một trong hai đối số Row_num và Column_num.
+ Hàm INDEX dạng tham chiếu:
Cú Pháp: INDEX (Reference,Row_num,[Column_num],[Area_num])
Trong Đó:
- Reference: Vùng tham chiếu, bắt buộc
- Row_num: Chỉ số hàng từ đó trả về một tham chiếu, bắt buộc.
- Column_num: Chỉ số cột từ đó trả về một tham chiếu, tùy chọn.
– Area_num: Số của vùng ô sẽ trả về giá trị trong reference. Nếu Area_num được bỏ qua thì hàm index dùng vùng 1, tùy chọn.
+ Ghi chú:
– Nếu cả hai đối số Row_num và Column_num đều được dùng, thì hàm
index
trả về giá trị trong ô nằm ở giao điểm của Row_num và Column_num.
- Nếu bạn đặt Row_num hoặc Column_num là 0 (không), hàm index trả về mảng giá trị cho toàn bộ cột hoặc hàng tương ứng. Để dùng các giá trị được trả về làm mảng, hãy nhập hàm index như là một công thức mảng trong phạm vi ô ngang cho một hàng, và trong phạm vi ô dọc cho một cột. Để nhập một công thức mảng, hãy nhấn CTRL+SHIFT+ENTER.
Không tạo được công thức mảng trong Excel Web App.
Row_num và Column_num phải trỏ tới một ô trong mảng; nếu không, hàm INDEX trả về giá trị lỗi #REF! .
3.2. Hàm MATCH
Hàm MATCH tìm kiếm một mục đã xác định trong một phạm vi ô, rồi trả về vị trí tương đối của mục trong phạm vi đó.
Hãy dùng hàm MATCH thay cho một trong các hàm LOOKUP khi bạn cần biết vị trí của một mục trong một phạm vi thay vì chính mục đó. Ví dụ, bạn có thể dùng hàm MATCH để cung cấp giá trị cho đối số số hàng trong hàm INDEX.
Cú Pháp: MATCH (Lookup_value,Lookup_array,[Match_type])
Trong Đó:
– Lookup_value: Giá trị tìm kiếm (giá trị số, văn bản, giá trị logic, tham chiếu ô đến một số, văn bản hay giá trị logic)
– Lookup_array: Mảng để tìm kiếm, bắt buộc
– Match_type: Kiểu tìm kiếm. Không bắt buộc.
Có 3 kiểu tìm kiếm là:
1:Less than (Nhỏ hơn giá trị tìm kiếm)
0: Exact match (Chính xác giá trị tìm kiếm)
-1:Greater than ( Lớn hơn giá trị tìm kiếm)
Khi bỏ qua không nhập gì thì hàm MATCH mặc định là 1.
3.3. Ví dụ minh họa
Cho các bảng dữ liệu như hình dưới, yêu cầu sử dụng hàm INDEX và hàm MATCH để điền vào cột “Tên xe” cho mỗi mặt hàng dựa vào các bảng dữ liệu ở dưới.
Nhập công thức tại ô D3
D3=INDEX($B$16:$E$19;MATCH(B3;$B$16:$B$19;0);MATCH(C3;$B$16:$E$16;0))
Ta có kết quả như hình dưới
Bảng kết quả sau khi sử dụng công thức excel
Như vậy, bài viết trên chúng tôi đã giới thiệu đến các bạn về cách sử dụng của hàm INDEX và hàm MATCH trong excel. Trong bài viết tới, chúng tôi sẽ cập nhật tiếp đến các bạn các bài tập về hai hàm này trong Excel. Mong rằng bài viết trên sẽ mang lại tiện ích cho các bạn.
Hàm Dsum Trong Excel, Cách Sử Dụng Hàm Dsum Và Ví Dụ Minh Họa
Mô tả hàm DSUM
Hàm DSUM là hàm cộng các số trong một trường (cột) trong danh sách dữ liệu thỏa mãn với các điều kiện mà bạn xác định.
Cú pháp hàm DSUM
=DSUM(database; field; criteria)
Trong đó:
Database là đối số bắt buộc, đây chính là danh sách (cơ sở dữ liệu) được tạo thành từ một phạm vi ô. Danh sách dữ liệu này là danh sách chứa các dữ liệu là các bản ghi và các cột dữ liệu là các trường, có chứa trường để kiểm tra điều kiện và trường để tính tổng. Danh sách chứa hàng đầu tiên là tiêu đề cột.
Field là đối số bắt buộc, đối số này chỉ rõ tên cột dùng để tính tổng các số liệu. Các bạn có thể nhập tên tiêu đề cột trong dấu ngoặc kép hoặc là một số thể hiện vị trí cột trong danh sách không trong dấu ngoặc kép (ví dụ số 1 là cột đầu tiên, số 2 là cột thứ 2… trong database) hay một tham chiếu đến tiêu đề cột mà các bạn muốn tính tổng.
Criteria là đối số bắt buộc, đây là phạm vi ô có chứa điều kiện mà các bạn muốn hàm DSUM kiểm tra.
Lưu ý về phạm vi điều kiện criteria
Các bạn có thể dùng phạm vi bất kỳ nào cho đối số criteria nếu phạm vi đó có chứa ít nhất một nhãn cột và ít nhất một ô bên dưới tiêu đề cột đó mà nó sẽ xác định điều kiện cho cột đó.
Không nên đặt phạm vi điều kiện ở phía dưới danh sách, vì sẽ không có vị trí thêm các thông tin khác vào danh sách.
Ví dụ hàm DSUM
Giả sử các bạn có bảng dữ liệu sau:
Đầu tiên các bạn có thể tạo một phạm vi điều kiện cho hàm DSUM, vì các bạn cần tính các sản phẩm iPhone mà có rất nhiều dòng iPhone nên điều kiện các bạn nhập thêm ký tự đại diện là dấu * sau iPhone.
=DSUM(A9:E18;”Thành Tiền”;C4:C5)
Trong đó:
“Thành Tiền” là tiêu đề cột mà các bạn sẽ sử dụng giá trị trong cột đó để tính tổng.
C4:C5 là phạm vi điều kiện chứa tiêu đề cột và một giá trị điều kiện.
=DSUM(A9:E18;E9;C4:C5)
Trong đó:
A9:E18 là phạm vi cơ sở dữ liệu mà các bạn sẽ làm việc.
E9 là tham chiếu đến tiêu đề cột cần tính tổng.
Hàm Left Trong Excel, Cách Sử Dụng Hàm Left Và Ví Dụ Minh Họa
Mô tả hàm LEFT
Hàm LEFT trong Excel trả về một hoặc nhiều ký tự đầu tiên trong một chuỗi, dựa vào số ký tự mà bạn chỉ định, hàm này thường được gọi là hàm cắt chuỗi ký tự bên trái. Ngoài ra còn có hàm RIGHT là hàm cắt chuỗi ký tự bên phải, hàm MID là hàm lấy chuỗi ký tự ở giữa.
Cú pháp hàm LEFT
=LEFT(text;[num_chars])
Trong đó:
text là đối số bắt buộc, đây là chuỗi văn bản hoặc tham chiếu ô tới chuỗi văn bản có chứa các ký tự mà các bạn muốn trích xuất.
num_chars là đối số tùy chọn, đây là số ký tự mà các bạn muốn hàm LEFT trích xuất bắt đầu từ vị trí đầu tiên bên trái text.
Num_chars phải lớn hơn hoặc bằng không, nếu num_chars < 0 thì hàm sẽ trả về lỗi #VALUE!.
Num_chars lớn hơn độ dài của văn bản thì hàm LEFT sẽ trả về toàn bộ văn bản.
Nếu các bạn bỏ qua num_chars thì mặc định num_chars = 1.
Ví dụ hàm LEFT
Ví dụ 1: Sử dụng hàm LEFT để trích xuất 8 ký tự đầu tiên trong chuỗi “thuthuatphanmem.vn”
Các bạn có thể nhập trực tiếp chuỗi văn bản vào hàm LEFT.
Trong ô đầu tiên trong cột hàm LEFT (D7) các bạn nhập hàm
=LEFT(C7;SEARCH(“@”;C7)-1)
Hàm Search sẽ định vị ký tự đặc biệt @ trong chuỗi văn bản gmail đầy đủ (C7) và trả về số vị trí bắt đầu của @ trong chuỗi C7. Vị trí hàm Search trả về sẽ chính là vị trí của ký tự @ vậy nên các bạn cần trừ 1 để không trích xuất cả ký tự @. Như vậy hàm LEFT sẽ trả về chuỗi ký tự từ ký tự đầu tiên đến vị trí trước ký tự @ trong chuỗi văn bản trong ô C7.
Ví dụ 3: Sử dụng hàm VLOOKUP và hàm LEFT.
Giả sử các bạn có dữ liệu như sau:
Cách thực hiện:
Lấy 2 ký tự đầu trong mã nhân viên và dò tìm hai ký tự này trong bảng Công Việc để hiển thị công việc trong cột Công Việc của bảng Danh sách nhân viên.
Trong ô đầu tiên của cột Công Việc các bạn nhập hàm
=VLOOKUP(LEFT(C7;2);$C$19:$D$21;2;0)
Trong đó:
Hàm LEFT(C7;2) sẽ trả về 2 ký tự đầu tiên trong Mã Nhân Viên, đây sẽ là giá trị cần dò tìm trong hàm VLOOKUP.
$C$19:$D$21 là vùng dữ liệu trong bảng Công Việc, là vùng cần dò tìm và trả về kết quả của hàm VLOOKUP, vì vùng này là cố định nên các bạn chọn vùng C19:D21 sau đó nhấn phím F4 để cố định vùng thành $C$19:$D$21.
2 là cột trả về, chính là cột công việc trong bảng Công Việc.
0 là kiểu dò tìm chính xác.
Kết quả các bạn sẽ được như sau:
Cách Sử Dụng Hàm If Trong Excel Và Ví Dụ Thực Tế
Nói nôm na, “if” có nghĩa là “nếu”. Trong Excel, nó sẽ kiểm tra điều kiện và trả về một giá trị nếu điều kiện đó đúng, và trả về một giá trị khác nếu điều kiện đó sai. Các điều kiện và các giá trị trả về được chúng ta lập trình từ trước.
Cấu trúc của hàm IF
Một hàm IF sẽ có cấu trúc cơ bản như các hàm khác trong Excel như sau:
IF (logical_test, [value_if_true], [value_if_false]) Trong đó:
logical_test: Là một giá trị hay biểu thức logic có giá trị TRUE (đúng) hoặc FALSE (sai). Bắt buộc phải có. Đối với tham số này, bạn có thể chỉ rõ đó là ký tự, ngày tháng, con số hay bất cứ biểu thức so sánh nào.
Value_if_true: Là giá trị mà hàm sẽ trả về nếu biểu thức logic cho giá trị TRUE hay nói cách khác là điều kiện thỏa mãn. Không bắt buộc phải có.
Value_if_false: là giá trị mà hàm sẽ trả về nếu biểu thức logic cho giá trị FALSE hay nói cách khác là điều kiện không thỏa mãn. Không bắt buộc phải có.
Nghĩa là: nếu ở ô A1 có giá trị lớn hơn 5 thì giá trị trả về ô có chứa hàm if này là đạt, ngược lại là không đạt.
Tượng tự kéo công thức xuống các ô tiếp theo ta có:
Một số ví dụ minh họa về cách hoạt động của hàm IF
Cột tổng kết hiện ra hai giá trị là “thi lại” và “lên lớp”. Lên lớp khi điểm môn toán và môn văn phải từ 5 điểm trở lên, bất kỳ môn nào dưới 5 điểm đều phải thi lại.
Tương tự kéo công thức xuống các ô tiếp theo ta có:
Cột giá trị logic muốn trả về 2 giá trị là TRUE hoặc FALSE là các giá trị logic (Boolean). Nếu là “nam” thì là TRUE, nếu là “nữ” thì là FALSE.
Tương tự kéo công thức xuống các ô tiếp theo ta có:
Mẹo hay:
Nếu muốn giá trị trả về TRUE, FALSE là dạng văn bản thì cần để trong dấu nháy kép “TRUE”, “FALSE”.
Công thức trên không phân biệt giá trị so sánh là chữ hoa hay thường. Nếu muốn phân biệt hoa hay thường thì ta dùng thêm hàm EXACT. =IF(EXACT(A2,”nam”),TRUE,FALSE)
Nếu nhân viên nào có khoảng cách từ nhà đến cơ quan lớn hơn 5km thì lương hằng tháng sẽ bằng lương cơ bản cộng thêm phụ cấp xa nhà. Phụ cấp xa nhà được tính bằng khoảng cách (km) nhân với 1,000,000 đồng.
Nếu khách hàng thuê phòng từ ngày 10-10-2019 đến ngày 13-10-2019 sẽ được hiển thị ở cột ưu đãi là khuyến mãi, ngược lại thì bỏ trống.
Một số toán tử so sánh
Một số công thức hàm IF cho dữ liệu và ô trống
Nếu muốn đánh dấu ô dữ liệu hay ô trống nhất định thì bạn cần thực hiện một trong các cách sau:
Sử dụng kết hợp hàm IF với ISBLANK
Ô trống
=””
Được cho là nếu ô được chỉ định là ô trống, bao gồm cả các ô với độ dài xâu bằng 0. Ngược lại thì là
=IF(A1=””, 0, 1) Trả về 0 nếu A1 là ô trống. Ngược lại thì trả về 1 Nếu A1 là một chuỗi giá trị rỗng thì trả về 0
ISBLANK()
Được cho là nếu ô được chỉ định là ô rông hoàn toàn – không có công thức, không có cả chuỗi giá trị rỗng được trả về từ công thức khác. Ngược lại thì là
=IF(ISBLANK(A1), 0, 1) Trả lại kết quả giống với công thức trên nhưng xử lý các ô có độ dài chuỗi bằng 0 như các ô rỗng. Tức là, nếu A1 chứa một chuỗi giá trị rỗng, công thức sẽ trả về 1.
Ô có chứa dữ liệu
Được cho là nếu ô chỉ định có chứa dữ liệu. Ngược lại thì là Những ô với độ dài chuỗi bằng 0 thì là ô trống
ISBLANK()=FALSE
Được cho là nếu ô ấn định không phải ô rỗng. Ngược lại thì là Ô với độ dài chuỗi bằng o thì là ô không rỗng
=IF(ISBLANK(A1)=FALSE, 0, 1) Tương tự như các công thức trên, nhưng trả về 1 nếu A1 có bao gồm một chuỗi giá trị rỗng
Cập nhật thông tin chi tiết về Nhóm Hàm Dò Tìm Dữ Liệu Trong Excel – Cách Sử Dụng Và Ví Dụ Minh Hoạ (Phần 6) trên website Hoisinhvienqnam.edu.vn. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất. Chúc bạn một ngày tốt lành!