.st0{fill:#FFFFFF;}

Cách sử dụng các hàm tìm kiếm trên mọi phiên bản của Excel 

 Tháng Chín 19, 2021

By  Lê Đình Chi

Với lượng dữ liệu thống kê lớn, sử dụng các hàm tìm kiếm cơ bản trên Excel càng trở nên cần thiết.

Để giải quyết bài đoán đó trong bài viết này zik.vn sẽ hướng dẫn cách bạn phối hợp cách hàm với nhau để tìm được kết quả như ý nhé: Cùng theo dỗi nhé. 

Trước tiên chúng ta cần xem dữ liệu của chúng ta thế nào nhé.

1. Hàm tìm kiếm giá trị lớn nhất và nhỏ nhất trên Excel 

Mục đích tìm kiếm ở đây là lấy ra giá trị lớn nhất trong vùng trong excel hỗ trợ cho chúng ta những hàm sau:

Với hàm Max, Large, Aggegate để tìm được giá trị lớn nhất bạn cần gõ hàm và chọn vùng cần so sánh, kết quả lượng tiêu thụ lớn nhất lần lượt theo cách hàm như sau

  1. =MAX($B$2:$B$10)
  2. =LARGE($B$2:$B$10,1)
  3. =AGGREGATE(14,0,$B$2:$B$10,1)

Tương tự lượng tồn kho lớn nhất ta cũng là tương tự chỉ cần đổi đối số tìm kiếm là được.

Tương tự vơi cách tìm kiếm nhỏ nhất chúng ta sử dụng hàm sau:

2.  Hàm tìm kiếm giá trị tương ứng

Hàm tìm kiếm giá trị tương ứng.

  1. Vlookup + choose
  2. Offset
  3. Index + Match

Và có rất nhiều hàm khác.

Cú pháp =VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

nhưng hàm vlookup không cho dò tìm từ phải qua trái nên ta cần thêm hàm CHOOSE nữa

Công thức sẽ như sau:

=VLOOKUP(MAX($B$2:$B$10),CHOOSE({1,2},$B$2:$B$10,$A$2:$A$10),2,0)

Giải thích công thức.

MAX($B$2:$B$10) sẽ tính được giá trị lớn nhất của lượng tiêu thụ.

CHOOSE({1,2},$B$2:$B$10,$A$2:$A$10) sẽ đổi số cột của biến cột A2:A10 ra sau và cột B2: B10 lên trước.

Sau khi tìm được các đối số cần thiết việc còn lại là vlookup theo từng đối số và lấy ra kết quả.

Cú pháp hàm =OFFSET(reference,rows,cols,height,width)

Xác định yếu tố cần tìm.

  1. reference là điểm đầu tiên ở đây thường là bắt đầu của bảng tính($A$2)
  2. rows là vị trí hàng cầm tìm ở đây ta kết hợp hàm match và hàm max để tìm tương tự như ở trên như lưu ý là phải trừ đi số dòng tiêu đề (=MATCH( MAX($B$2:$B$10),$B$2:$B$10,0)-1)
  3. cols ở đây do cột bắt đầu là  cột kết quả lên ta không di chuyển(0)
  4. Hai đối số Height và width chúng ta không bắt buộc đưa vào trong trường hợp này nên không cần chọn.

Công thức như sau:

=OFFSET($A$2,MATCH( MAX($B$2:$B$10),$B$2:$B$10,0)-1,0)

Định nghĩa: Bằng vị trí giao nhau giữa dòng và cột được xác định, thông qua hàm INDEX, Excel sẽ đưa ra giá trị tương ứng với quy chiếu.

Cú pháp của hàm Index: =INDEX(array,row_num,column_num)

Cú pháp hàm Match: =MATCH(lookup_value,lookup_array,match_type)

xác định điều kiện 

  1. Array: bảng dò tìm đã có rồi
  2. Row_num chúng ta tìm ra khi sử dụng hàm match kết hợp với hàm Max để tính ra

Công thức viết gộp lại sẽ như sau.

=INDEX($A$2:$A$10,MATCH(MAX($B$2:$B$10),$B$2:$B$10,0))

Trong đó 

  • $A$2:$A$10 là Array bảng dò tìm
  • MATCH(MAX($B$2:$B$10),$B$2:$B$10,0) là vị trí hàng trả về Row_num

ở đây do bảng dò tìm là một cột nên ta không cần tìm hàm cột cần lấy dữ liệu.

Trong các hoàn cảnh tương thích, các bạn tùy ý sử dụng một trong hai phương thức trên. Ví dụ sau đây, chúng ta sẽ sử dụng cách 1 để xử lý dữ liệu.

01

01

Bước 1: Sử dụng hàm MAX để tìm ra lượng tiêu thụ lớn nhất và lượng tồn kho lớn nhất (hướng dẫn sử dụng hàm tìm kiếm giá trị lớn nhất tại đây) Trong ví dụ, hàm MAX được thể hiện như sau:

  • Lượng tiêu thu lớn nhất: =MAX(B3:B11)
  • Lượng tồn kho lớn nhất: =MAX(C3:C11) 

Dõi theo hình động này để hiểu rõ cách thực hiện của hàm tìm kiếm MAX.

02

02

Bước 2: Sử dụng hàm INDEX để tìm mã hàng tương ứng với mã hàng nào có lượng tiêu thụ lớn nhất và mã hàng nào có lượng tồn kho lớn nhất:

  • Mã hàng có lượng tiêu thụ lớn nhất: =INDEX(A3:A11, MATCH(H2,B3:B11,0))
  • Mã hàng có lượng tồn kho lớn nhất: =INDEX(A3:A11, MATCH(H4,C3:C11,0))

Trong đó:

  • A3:A11 xác định vùng tìm mã hàng
  • MATCH(vị trí giá trị lượng tiêu thụ lớn nhất vừa tìm được, giá trị lượng tiêu thụ lớn nhất tìm thấy trong vùng B3:B11, 0) — tương tự với lượng tồn kho lớn nhất.

Quan sát hình động sau để hiểu rõ cách thực hiện của hàm tìm kiếm INDEX này.

03

Để dò tìm ra số lớn nhất các bạn hoàn toàn có thể sử dụng hàm LARGE hay AGGREGATE để thay thế nhé.

Để có thể ứng dụng tốt Excel vào trong công việc, chúng ta không chỉ nắm vững được các hàm mà còn phải sử dụng tốt cả các công cụ của Excel. Những hàm nâng cao giúp áp dụng tốt vào công việc như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Những công cụ thường sử dụng là Data validation, Conditional formatting, Pivot table…

Toàn bộ những kiến thức này các bạn đều có thể học được trong khóa học

Cảm ơn các bạn đã theo dõi bài viết nếu thấy hay và hữu ích hãy chia sẻ cho nhiều người biết và đừng quên coment ý kiến của mình để đội ngũ phát triển Zik.vn đưa ra các bài viết chất lượng hơn nữa.


Đánh giá bài viết này

Tài liệu kèm theo bài viết

zik.vn – Nền tảng học online duy nhất tại Việt Nam tập trung vào phát triển kỹ năng làm việc dành cho người đi làm

Với sứ mệnh: Mang cơ hội phát triển kỹ năng, phát triển nghề nghiệp tới hàng triệu người”, đội ngũ phát triển zik.vn đã và đang làm việc với những học viện, trung tâm đào tạo, các chuyên gia đầu ngành để nghiên cứu và xây dựng lên các chương trình đào tạo từ cơ bản đến chuyên sâu xung quanh các lĩnh vực: Tin học văn phòng, Phân tích dữ liệu, Thiết kế, Công nghệ thông tin, Kinh doanh, Marketing, Quản lý dự án…

Zik.vn tự hào khi được đồng hành cùng:

  • 50+ khách hàng doanh nghiệp lớn trong nhiều lĩnh vực như: Vietinbank, Vietcombank, BIDV, VP Bank, TH True Milk, VNPT, FPT Software, Samsung SDIV, Ajinomoto Việt Nam, Messer,…
  • 100.000+ học viên trên khắp Việt Nam

Tìm hiểu ngay các khóa học của Zik.vn TẠI ĐÂY



Lê Đình Chi


Your Signature

Leave a Reply

Your email address will not be published. Required fields are marked

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Subscribe to our newsletter now!

DMCA.com Protection Status
>