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:

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