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

Làm thế nào để 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 một cách nhanh chóng nhất. Hãy đọc bài viết dưới đây do eLib biên soạn để tìm cho mình được câu trả lời chính xác nhất nhé.

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

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ông báo lỗi trong Excel

Đây chính là thông báo trong file có chứa phép tính bị lặp theo vòng (Circular references). Chúng ta cùng tìm hiểu xem vòng lặp là thế nào và cách xử lý nhé:

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.

Chúng ta cùng xem công thức tại các ô này thế nào nhé:

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.

=> Đây chính là khái niệm về vòng tính lặp trong Excel và cách Excel thông báo tới chúng ta về vòng lặp.

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)

Chọn tab File > Option > Formulas

Đá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)

Maximum change: Thay đổi tối đa (khi tính toán theo vòng lặp thì cứ có sự thay đổi kết quả sau mỗi vòng lặp thì sẽ được tính tiếp) => Tùy vào độ phức tạp trong vòng lặp mà cần giới hạn điều này, để đảm bảo kết quả như mong muốn. Giá trị càng nhỏ thì kết quả vòng lặp càng chính xác, nhưng Excel sẽ cần tính toán nhiều hơn và tốn tài nguyên để xử lý hơn.

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 hội tụ: Tính đến khi đạt được kết quả cuối cùng, ổn định => Thường dùng vòng lặp để đạt được điều này

  • Đá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

  • Dựa theo vị trí mà chức năng Error checking > Circular References chỉ dẫn để kiểm tra lại công thức, loại bỏ các tham chiếu gây lặp trong công thức đó (xóa, chuyển công thức thành ghi chú hoặc sửa lại tham chiếu trong công thức)

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

Hôm nay chúng ta cùng tìm hiểu về chủ đề “Tìm nội dung xuất hiện nhiều nhất trong danh sách”. Chắc hẳn với nhiều bạn khi chưa thành thạo Excel thì ngay cả tìm ra 1 cách làm cũng khó rồi. Ở bài viết này mình xin giới thiệu với các bạn 02 cách khác nhau để giải quyết yêu cầu trên.

Đề bài:

Tìm tên mặt hàng được bán chạy nhất trong danh sách dưới đây

Cách chọn nội dung xuất hiện nhiều nhất

Cách chọn nội dung xuất hiện nhiều nhất

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.

=> Như vậy kết quả sẽ cho chúng ta số lần xuất hiện tăng dần

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))

Như vậy chúng ta đã tìm được kết quả là mặt hàng Cam

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.

Tại sao lại là công thức mảng? Vì ở đây chúng ta sẽ xét hàm Mode trên từng giá trị tạo được bởi hàm Match, xét từ giá trị ở B2 tới B17, trong vùng từ B1 tới B17 (lookup_array trong hàm Match phải xét từ dòng đầu tiên trong sheet). Vì vậy công thức MATCH(B2:B17,B1:B17,0) chính là dạng công thức mảng của hàm Match, trong đó lookup_value chính là mảng B2:B17

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:

Như vậy bài viết đã hướng dẫn cho bạn cách 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. Chỉ cần một số thao tác cơ bản, bạn đã có thể sử dụng vòng tính lặp và chọn danh sách một cách hợp lý. Chúc các bạn thực hiện thao tác thành công!

Ngày:14/08/2020 Chia sẻ bởi:

CÓ THỂ BẠN QUAN TÂM