Cách loại bỏ tham chiếu vòng 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ụngExcel,đôi khi chúng ta mở 1 fileexcel lên và gặp phải thông báo như sau: Show Đâ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 tronghàm SUMtạ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 trongExcel và cáchExcel 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ếnExcel 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 TabFormulas, tìmđến mụcError Checking, chọnCircular References Trong mục nàyExcel 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:
Trường hợp 1: sử dụng tham chiếu vòng (vòng tính lặp) Chọn tabFile>Option>Formulas Đánh dấu chọn vào mụcEnable 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ưngExcel 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)
Trường hợp 2: Loại bỏ vòng lặp
2. Cách chọn nội dung xuất hiện nhiều nhất trong danh sáchHô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ạoExcel 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ấttrong danh sách dướiđây 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ớihà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ớihàm Match, chúng ta có thể tìmđược giá trị xuất hiện nhiềunhấ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àngCam 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 Modelà 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ímCtrl + 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_arraytrong hàm Match phải xét từ dòng đầu tiên trong sheet). Vì vậy công thứcMATCH(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áchchọ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! |
