Nếu ô A1 chứa giá trị số -4 ô B1 chứa 2 tại ô C1 chứa 7 kết quả của công thức A1, B1, C1 2 là

Xem 4,158

Bạn đang xem bài viết Hướng Dẫn Chọn Nội Dung Xuất Hiện Nhiều Nhất Trong Danh Sách Và Sử Dụng Vòng Tính Lặp Trong Excel được cập nhật mới nhất ngày 21/03/2022 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. Cho đến thời điểm hiện tại, bài viết này đã đạt được 4,158 lượt xem.

--- Bài mới hơn ---

1. Tìm hiểu về công thức vòng tính lặp trong Excel và phương pháp xử lý

Trong khi sử dụng Excel, đôi khi chúng ta mở 1 file excel lên và gặp phải thông báo như sau:

Thế nào là vòng tính lặp [vòng lặp / tham chiếu vòng tròn / tham chiếu vòng lặp]

Trong ví dụ ở hình trên, khi chúng ta bấm vào nút OK trong thông báo, nếu tại sheet đang mở chứa vòng lặp, sẽ ngay lập tức hiện lên cho chúng ta nội dung sau:

Tại vị trí có công thức, có nút mũi tên màu xanh nối hai dấu chấm xanh tại 2 ô, mà tại các ô đó đang có chứa công thức lặp lại kết quả của nhau.

Và ở phía dưới Sheet Tab [danh sách các Sheet], chúng ta thấy có dòng chữ thông báo: Circular References: C1, tức là thông báo vòng lặp đang có vấn đề tại vị trí ô C1.

Công thức tại ô A1 =SUM[C1:C5]-B1

Công thức tại ô C1 =A1-B1

C1 là đối tượng trong hàm SUM tại A1, vì vậy kết quả của A1 sẽ được tính thông qua dữ liệu của C1

Nhưng tại ô C1 lại tham chiếu tới A1 và B1, dùng kết quả của A1 để làm đối tượng tính toán.

Như vậy chúng ta có thể thấy giữa 2 ô A1 và C1 đang có sự lặp lại lẫn nhau, dùng chính kết quả của nhau để tính kết quả cho mình, và nó tạo thành vòng tròn không biết điểm đầu, điểm cuối.

Vòng lặp trong tính toán sẽ khiến Excel tiêu tốn bộ nhớ, tài nguyên khi phải thực hiện phép tính liên tục theo vòng. Bởi vậy sẽ luôn có thông báo cho người dùng biết khi có vòng lặp trong bảng tính để cảnh báo người dùng.

Cách tìm vị trí bị vòng lặp khi không rõ vị trí đó ở đâu?

Chọn Tab Formulas, tìm đến mục Error Checking, chọn Circular References

Trong mục này Excel sẽ chỉ cho chúng ta biết vòng lặp xuất hiện ở vị trí nào [Sheet nào, ô nào], đâu là trọng tâm của vòng lặp này [vị trí được đánh dấu tích V]

Bấm vào đối tượng được thông báo trong Circular References thì Excel sẽ đưa chúng ta đến ngay vị trí đó để kiểm tra lại thông tin.

Cách xử lý khi gặp vòng lặp:

Thường việc tính toán bị vòng lặp có 2 nguyên nhân chính:

• Nguyên nhân do cố ý và muốn tính toán theo vòng lặp: Bạn biết rõ rằng sẽ xuất hiện vòng lặp và chấp nhận điều đó để khai thác tính năng này.

• Nguyên nhân do vô ý: bạn không muốn hoặc không biết rằng đã tạo ra vòng lặp, cần xử lý để loại bỏ vòng lặp.

Trường hợp 1: sử dụng tham chiếu vòng [vòng tính lặp]

Đánh dấu chọn vào mục Enable iterative calculation [Cho phép tính toán lặp]

Maximum Iterations : Số lần lặp tối đa [giới hạn số lần lặp để kiểm soát giới hạn cho vòng lặp]

Việc tính toán theo vòng lặp có thể cho ra 3 kết quả [theo Support.office.com]

• Đáp án phân kỳ: Qua các lần lặp có sự khác biệt về kết quả, lần sau sẽ khác so với trước đó

• Đáp án chuyển đổi giữa hai giá trị: Thường có một vài kết quả nhất định và sẽ lặp lại sau một vài lần lặp

Trường hợp 2: Loại bỏ vòng lặp

• Bỏ đánh dấu chọn ở mục Enable iterative calculation ở bước trên để không tính toán theo vòng lặp

2. Cách chọn nội dung xuất hiện nhiều nhất trong danh sách

Đề bài:

Cách làm thứ 1: Countif + Max + Index[Match]

Bước 1: Đếm số lần xuất hiện với hàm COUNTIF

Tại ô C2 chúng ta đặt công thức như sau: =COUNTIF[$B$2:B2,B2]

Vùng chứa giá trị tìm kiếm sẽ xét từ vùng B2 trở đi. Cố định điểm đầu, còn điểm cuối sẽ mở rộng dần cho những dòng tiếp theo

Giá trị tìm kiếm tại chính vị trí B2. Khi filldown cho những giá trị tiếp theo phía dưới, giá trị tìm kiếm sẽ thay đổi theo vị trí tương ứng.

Bước 2: Tìm giá trị xuất hiện lớn nhất trong cột Đếm số lần xuất hiện với hàm MAX

Sử dụng hàm tìm giá trị lớn nhất là hàm MAX, xét tới kết quả tại cột Đếm số lần xuất hiện

G1 = MAX[C2:C17]

Bước 3: Tìm tên mặt hàng tương ứng với số thứ tự lớn nhất với hàm Index+Match

Với hàm Match, chúng ta có thể tìm được giá trị xuất hiện nhiều nhất nằm ở dòng nào trong bảng

Với hàm Index, chúng ta có thể tìm được giá trị xuất hiện nhiều nhất tại cột Tên mặt hàng, dòng được xác định bởi hàm Match

Công thức tại ô G2 = INDEX[B1:B17,MATCH[G1,C1:C17,0]]

Cách làm thứ 2: Công thức mảng Index – Mode – Match

Trong cách làm thứ 1, chúng ta đã tìm hiểu cách sử dụng hàm Index kết hợp với hàm Match. Ở cách làm thứ 2 này chúng ta cũng sử dụng hàm Index + Match nhưng kết hợp với 1 hàm khác là hàm Mode

Hàm Mode là hàm xác định số ký tự được lặp lại nhiều lần nhất, nhưng chỉ áp dụng với ký tự dạng số [Number]

Cấu trúc hàm Mode[Number1, Number2, …]

Đối số của hàm Mode là Number, vì vậy chúng ta phải đưa giá trị dạng số thì hàm Mode mới cho ra kết quả.

Chúng ta thấy hàm Match trả về kết quả là vị trí dòng của 1 đối tượng trong một danh sách, vì vậy có thể kết hợp hàm Mode với hàm Match được.

Tại ô F2 chúng ta đặt công thức như sau:

F2 =MODE[MATCH[B2:B17,B1:B17,0]] Kết thúc công thức chúng ta sử dụng tổ hợp phím Ctrl + Shift + Enter để có thể đưa toàn bộ công thức này vào trong dấu { … } để thể hiện đây là công thức mảng.

Kết quả của hàm trả về 2, là dòng chứa nội dung xuất hiện nhiều nhất: Dòng 2, ứng với nội dung là “Cam”

Bây giờ chúng ta kết hợp thêm hàm Index để lấy nội dung như sau:

--- Bài cũ hơn ---

Cập nhật thông tin chi tiết về Hướng Dẫn Chọn Nội Dung Xuất Hiện Nhiều Nhất Trong Danh Sách Và Sử Dụng Vòng Tính Lặp Trong Excel 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!

Trình duyệt của bạn không hỗ trợ nghe audio.

Có thể bạn chưa biết trong Excel có rất nhiều hàm có thể  giúp bạn tìm kiếm một giá trị trong 1 mảng và hàm Match là một trong những hàm đó. Nói một cách đơn giản hơn hàm Match sẽ trả lại kết quả là vị trí tương đối của giá trị bạn cần tìm trong 1 vùng. Tuy nhiên, hàm Match còn có thể làm nhiều hơn như vậy. Trong bài học này chúng ta cùng nhau tìm hiểu về hàm Match và cách dùng Match trong Excel bạn nhé

Cú pháp hàm Match

Hàm MATCH tìm một mục được chỉ định trong phạm vi của ô, sau đó trả về vị trí tương đối của mục đó trong phạm vi này. Ví dụ, nếu phạm vi A1:A3 chứa giá trị 5, 25 và 38 thì công thức =MATCH[25,A1:A3,0] sẽ trả về số 2, vì 25 là mục thứ hai trong phạm vi.

Cú pháp của hàm Match như sau:

Trong công thức trên:

•  [là tham số bắt buộc]: là giá trị bạn muốn tìm vị trí tương đối trong mảng, có thể thuộc loại dữ liệu số, text, logic hoặc là 1 tham chiếu đến 1 địa chỉ ô
•  [cũng là tham số bắt buộc]: vùng muốn tìm giá trị
•  [ 1 trong 3 số : 1 hoặc 0 hoặc -1]: Là tham số không bắt buộc]: định nghĩa cách tìm kiếm
  • Nếu bạn điền số 1 hoặc bỏ trống: hàm Match sẽ tìm giá trị lớn nhất trong vùng tìm kiếm nhưng nhỏ hơn hoặc bằng giá trị đang được tìm kiếm. Chú ý: vùng tìm kiếm cần được sắp xếp theo chiều tăng dần, nghĩa là, từ giá trị nhỏ nhất đến giá trị lớn nhất hoặc từ A tới Z
  • Nếu bạn điền số 0: hàm Match sẽ tìm chính xác giá trị cần tìm trong vùng và trả lại vị trí đầu tiên mà nó tìm thấy
  • Nếu bạn điền số -1: hàm Match sẽ tìm giá trị nhỏ nhất trong vùng tìm kiếm nhưng lơn hơn hoặc bằng giá trị đang được tìm kiếm. Chú ý: vùng tìm kiếm cần được sắp xếp theo thứ tự giảm dần, nghĩa là, từ giá trị lớn nhất đến giá trị nhỏ nhất hoặc từ Z tới A

Hơi lan mam khó hiểu phải không nào. Chúng ta sẽ xét thử 1 ví dụ để giải tỏa đầu óc nhé. Hãy cùng nhau làm một ví dụ để hiểu rõ hơn:

Copy và dán  danh sách sau vào Excel 

Gõ vào ô muốn hiển thị kết quả công thức sau

=MATCH[E3,A2:A8,0]

như các bạn đã thấy, tên truyện Vô địch kiếm vực ở vị trí tương đối số 1 trong vùng A2:A11, nên kết quả hàm Match của chúng ta trả về kết quả là  1.

Các lưu ý khi sử dụng hàm Match:

• Hàm MATCH trả về vị trí của giá trị cần tìm trong lookup_array, chứ không trả về chính giá trị đó. Ví dụ, MATCH["b","a","b","c"},0] trả về 2, là vị trí tương đối của "b" trong mảng "a","b","c"}.
• Hàm MATCH không phân biệt chữ hoa và chữ thường khi so khớp các giá trị văn bản.
• Nếu hàm MATCH không tìm thấy giá trị khớp nào, nó trả về giá trị lỗi #N/A.
• Nếu kiểu khớp là 0 và giá trị tìm kiếm là chuỗi văn bản, thì bạn có thể dùng ký tự đại diện  dấu hỏi [?] và dấu sao [*] trong đối số giá trị tìm kiếm. Một dấu chấm hỏi khớp bất kỳ ký tự đơn nào; một dấu sao phù hợp với bất kỳ chuỗi ký tự nào. Nếu bạn muốn tìm một dấu chấm hỏi hay dấu sao thực, hãy gõ một dấu ngã [~] trước ký tự đó.
• Nếu vùng tìm kiếm có một vài giá trị trùng nhau, hàm Match sẽ trả về giá trị đầu tiên mà nó gặp

Cách dùng hàm MATCH trong Excel

Ví dụ dùng MATCH đơn giản

1. Cũng xét ví dụ trên chúng ta Data validation để kiểm tra từng tên truyện 
2. Chúng ta sẽ tạo Data validation là List trong ô E3. Chọn ô E3 rồi vào Data => Data validation 
3. Cửa sổ Data validation hiện lên chọn như sau với =$A$2:$A$8 là vùng chứa dữ liệu để tạo List. 
4. Nhấn OK List của ta sẽ được tạo
5. Nhập công thức sau vào ô E4 để sử dụng hàm Match
6. =MATCH[E3,A2:A8,0]
7. Nhấn Enter trên bàn phím kết quả sẽ thu được như sau
8. Sử dụng mũi tên ở ô E3 để kiểm tra các tên truyện khác

Sử dụng MATCH với ký tự đại diện

Ở phần trước, các bạn đã học cách sử dụng hàm Match một cách đơn giản. Giống như một số hàm khác, hàm Match của chúng ta cũng có khả năng ăn chơi với các kí tự đại diện:

• Dấu hỏi chấm [?] – đại diện cho 1 kí tự bất kì
• Dấu sao [*] – đại diện cho bất kì số lượng kí tự nào. [Chỉ sử dụng với Match khi tham số thứ 3 của hàm Match có giá trị bằng 0]

Chúng ta có ví dụ sau: Tìm vị trí tương đối của truyện đầu bằng “Đấu”

Với hàm match sử dụng cùng kí tự đại diện dấu “*”

=MATCH[E3&"*",A2:A9,0]

Ta được kết quả là 2:

Một ví dụ nữa với kí tự đại diện dấu hỏi chấm [?] với dữ liệu ở bảng phía trên:

=MATCH["Ki? long bất diệt thể",A2:A9,0]

Ta được kết quả là 5, là vị trí tương đối của tên truyện "Kim long bất diệt thể"

Sử dụng MATCH kết hợp hàm EXACT phân biệt chữ hoa chữ thường

Như đã nói ở phần trước, hàm Match khi tìm kiếm sẽ không phân biệt được chữ hoa và chữ thường. Để giúp hàm Match phân biệt chữ hoa, chữ thường, ta sẽ kết hợp hàm Match với hàm EXACT như sau:

=MATCH[TRUE, EXACT[,< giá trị tìm kiếm>],0]

Chúng ta có thể hiểu công thức này như sau:

• Hàm EXACT sẽ giúp chúng ta so sánh giá trị cần tìm kiếm với mỗi giá trị trong vùng tìm kiếm. Nếu ô được so sánh trùng khít về nội dung 100% với ô đem đi so sánh thì hàm EXACT sẽ trả về giá trị TRUE, nếu không hàm EXACT sẽ trả về giá trị FALSE
• Sau đó hàm Match sẽ tìm kiếm giá trị TRUE được trả về từ hàm EXACT này, kết quả là chúng ta có thể sử dụng hàm MATCH có phân biệt được chữ hoa và chữ thường.

Chú ý: đây là 1 công thức mảng, sau khi nhập xong công thức, bạn sử dụng phím tắt CTRL + SHIFT + ENTER để nhập, bạn không cần thêm dấu ngoặc nhọn vào công thức.

Ví dụ chúng ta cần tìm vị trí chính xác của truyện bắt đầu bằng vô trong bảng dưới

Ta sử dụng hàm MATCH kết hợp với EXACT như sau

=MATCH[TRUE,[EXACT[A2:A9,"vô*"]]] .

Vì là công thức mảng nên sau khi gõ công thức xong chúng ta nhấn Ctrl + Shift + Enter trên bàn phím để thấy kết quả

Kết quả trả về vị trí thứ 8, vị trí số 1 do chữ Vô viết hoa nên không chính xác.

Hàm MATCH so sánh 2 cột tìm sự khác biệt

Một trong những công việc gây tốn rất nhiều thời gian đó là so sánh 2 cột trong Excel để biết thành phần nào ở cột 1 chưa có trong cột 2 hoặc ngược lại. Có nhiều cách để giải quyết vấn đề này, trong đó có cách sử dụng Match kết hợp với hàm ISNA:

=IF[ISNA[MATCH[, ,0]],"Không có trong cột 2",""]

Phân tích công thức

• Hàm Match sẽ tìm giá trị ở cột 1 trong cột 2, nếu giá trị này được tìm thấy, hàm Match sẽ trả về vị trí tương đối của giá trị đó, nếu không tìm thấy, lỗi #N/A sẽ được trả về.
• Hàm ISNA kiểm tra xem kết quả trả về từ hàm Match có phải là #N/A hay không. Nếu hàm ISNA trả về giá trị đúng, nghĩa là giá trị không được tìm thấy, điều này dẫn đến việc tham số đầu tiên của hàm IF có giá trị TRUE, do vậy kết quả của hàm IF sẽ là “Không có trong cột 2”

Để minh hoạ cho đoạn giải thích rất là dài phía trên, không có gì tốt hơn 1 ví dụ:

Trong ví dụ này, Tuyệt thế võ thần có trong cột A  mà không có trong cột B, vậy nên kết quả thông báo như ở cột C.

Hàm MATCH kết hợp VLOOKUP

Bạn có chắc là bạn đã biết sử dụng hàm vlookup rồi chứ? nếu chưa, bạn có thể bấm vào chữ Vlookup để tìm hiểu về hàm này trước khi tiếp tục.

Một vấn đề rất hay gặp phải của hàm VLOOKUP đó là: Lỗi khi bảng tra cứu thay đổi cấu trúc, cách giải quyết đầu tiên là tổ chức dữ liệu tốt hơn trong Excel để tránh lỗi này. Thế nhưng trong bài này, chúng ta sẽ đi tìm hiểu 1 kĩ thuật nữa để hạn chế được lỗi này khi sử dụng VLOOKUP

Chúng ta có 1 ví dụ về hàm VLOOKUP như sau: 

Trong ví dụ chúng ta sử dụng hàm VLOOKUP để trả về Đơn giá của truyện Vô địch kiêm vực. Chúng ta sử dụng hàm VLOOKUP sau:

=VLOOKUP[F3,$A$2:$C$9,3,0]

Bây giờ chúng ta sẽ xoá cột Số lượng đi . Ngay lập tức hàm VLOOKUP của chúng ta lỗi  #REF!

 Để hạn chế lỗi này, chúng ta sẽ kết hợp hàm Match và Vlookup như sau:

=VLOOKUP[F3,$A$2:$C$9,MATCH[C1,A1:C1,0],0]

Và bây giờ, chúng ta có thể thử xoá cột Đơn giá đi, kết quả là, hàm VLOOKUP không còn bị lỗi nữa. Và Excel cũng sửa luôn công thức cho chúng ta.

MATCH[C1,A1:C1,0] công thức này sẽ dò tìm từ "Đơn giá" tức là tiêu đề cột Đơn giá xem nó nằm ở vị trí thứ mấy trong Dải A1 B1 C1. Lúc đầu 3 cột thì cho vị trí số 3, 2 cột thì cho vị trí số 2 nên dù thêm hay xóa cột đều không làm công thức vlookup của chúng ta bị lỗi.

Trên đây là toàn bộ cách sử dụng hàm MATCH trong Excel cũng như cách kết hợp hàm MATCH với nhiều hàm khác như VLOOKUP, EXACT ... Chúc các bạn học tốt.