Phân tích dữ liệu Excel - Kiểm tra công thức

Bạn có thể muốn kiểm tra độ chính xác của các công thức hoặc tìm nguồn gốc của lỗi. Các lệnh Kiểm tra Công thức trong Excel cung cấp cho bạn một cách dễ dàng để tìm thấy

Những ô nào đang đóng góp trong việc tính toán một công thức trong ô hiện hoạt.
Công thức nào đang tham chiếu đến ô hiện hoạt.

Những phát hiện này được thể hiện bằng đồ thị bằng các đường mũi tên giúp dễ hình dung. Bạn có thể hiển thị tất cả các công thức trong trang tính đang hoạt động bằng một lệnh duy nhất. Nếu công thức của bạn tham chiếu đến các ô trong một sổ làm việc khác, hãy mở cả sổ làm việc đó. Excel không thể đi đến một ô trong sổ làm việc không mở.

Đặt các tùy chọn hiển thị

Bạn cần kiểm tra xem các tùy chọn hiển thị cho sổ làm việc bạn đang sử dụng có được đặt chính xác hay không.

Nhấp chuột FILE > Options.
Trong hộp thoại Tùy chọn Excel, bấm Nâng cao.
Trong Tùy chọn hiển thị cho sổ làm việc -
- Chọn sổ làm việc.
- Kiểm tra xem trong Đối với đối tượng, hiển thị, Tất cả đã được chọn.
Lặp lại bước này cho tất cả các sổ làm việc bạn đang kiểm tra.

Truy tìm tiền lệ

Các ô trước là những ô được tham chiếu bởi một công thức trong ô hiện hoạt.

Trong ví dụ sau, ô hiện hoạt là C2. Trong C2, bạn có công thức=B2*C4.

B2 và C4 là các ô trước cho C2.

Để theo dõi các tiền lệ của ô C2,

Bấm vào ô C2.
Bấm vào tab Công thức.
Bấm Theo dõi Tiền lệ trong nhóm Kiểm tra Công thức.

Hai mũi tên, một từ B2 đến C2 và một từ C4 đến C2 sẽ được hiển thị, theo dõi các tiền lệ.

Lưu ý rằng để theo dõi tiền lệ của một ô, ô phải có một công thức với các tham chiếu hợp lệ. Nếu không, bạn sẽ nhận được thông báo lỗi.

Bấm vào ô không chứa công thức hoặc bấm vào ô trống.
Bấm Theo dõi Tiền lệ trong nhóm Kiểm tra Công thức.

Bạn sẽ nhận được một tin nhắn.

Loại bỏ Mũi tên

Bấm Loại bỏ Mũi tên trong nhóm Kiểm tra Công thức.

Tất cả các mũi tên trong trang tính sẽ biến mất.

Truy tìm người phụ thuộc

Các ô phụ thuộc chứa các công thức tham chiếu đến các ô khác. Điều đó có nghĩa là, nếu ô hiện hoạt đóng góp vào một công thức trong ô khác, thì ô kia là ô phụ thuộc vào ô hiện hoạt.

Trong ví dụ dưới đây, C2 có công thức =B2*C4. Do đó, C2 là ô phụ thuộc vào các ô B2 và C4

Để theo dõi những người phụ thuộc của ô B2,

Bấm vào ô B2.
Bấm vào tab Công thức.
Bấm Theo dõi Người phụ thuộc trong nhóm Kiểm tra Công thức.

Một mũi tên xuất hiện từ B2 đến C2, cho thấy C2 phụ thuộc vào B2.

Để theo dõi những người phụ thuộc của ô C4 -

Bấm vào ô C4.
Bấm vào tab Công thức> Theo dõi Người phụ thuộc trong nhóm Kiểm tra Công thức.

Một mũi tên khác xuất hiện từ C4 đến C2, cho thấy C2 cũng phụ thuộc vào C4.

Nhấp chuột Remove Arrowstrong nhóm Kiểm tra Công thức. Tất cả các mũi tên trong trang tính sẽ biến mất.

Note- Để truy tìm các thành phần phụ thuộc của một ô, ô phải được tham chiếu bởi một công thức trong một ô khác. Nếu không, bạn sẽ nhận được thông báo lỗi.

Nhấp vào ô B6 không được tham chiếu bởi bất kỳ công thức nào hoặc nhấp vào bất kỳ ô trống nào.
Bấm Theo dõi Người phụ thuộc trong nhóm Kiểm tra Công thức. Bạn sẽ nhận được một tin nhắn.

Làm việc với công thức

Bạn đã hiểu khái niệm Tiền thân và Người phụ thuộc. Bây giờ, hãy xem xét một trang tính có một số công thức.

Bấm vào một ô bên dưới Vượt qua Hạng mục trong bảng Kết quả Kỳ thi.
Nhấp vào Theo dõi tiền lệ. Ô bên trái (Dấu) và phạm vi E4: F8 sẽ được ánh xạ như các ô trước.
Lặp lại cho tất cả các ô trong Vượt qua Hạng mục trong bảng Kết quả Kiểm tra.

Bấm vào một ô bên dưới Loại Vượt qua trong bảng Xếp loại Học sinh.
Nhấp vào Theo dõi người phụ thuộc. Tất cả các ô trong Loại Vượt qua trong bảng Kết quả Kỳ thi sẽ được ánh xạ dưới dạng các ô phụ thuộc.

Hiển thị Công thức

Bảng tính dưới đây chứa tóm tắt doanh số của các nhân viên bán hàng ở các khu vực Đông, Bắc, Nam và Tây.

Bấm vào tab CÔNG THỨC trên Ruy-băng.
Bấm Hiển thị Công thức trong nhóm Kiểm tra Công thức. Công thức trong trang tính sẽ xuất hiện để bạn biết ô nào chứa công thức và công thức là gì.

Bấm vào một ô bên dưới TotalSales.
Nhấp vào Theo dõi tiền lệ. Biểu tượng trang tính xuất hiện ở cuối mũi tên. Biểu tượng trang tính cho biết rằng các tiền lệ nằm trong một trang tính khác.

Bấm đúp vào mũi tên. AGo TO hộp thoại xuất hiện, hiển thị các tiền lệ.

Như bạn quan sát, có bốn tiền lệ, trên bốn bảng tính khác nhau.

Nhấp vào tham chiếu của một trong những tiền lệ.
Tham chiếu xuất hiện trong hộp Tham chiếu.
Bấm OK. Trang tính có chứa tiền lệ đó xuất hiện.

Đánh giá một công thức

Để tìm cách hoạt động của một công thức phức tạp trong ô theo từng bước, bạn có thể sử dụng lệnh Đánh giá Công thức.

Hãy xem xét công thức NPV (Năm giữa) trong ô C14. Công thức là

=SQRT (1 + C2)*C10

Bấm vào ô C14.
Bấm vào tab CÔNG THỨC trên Ruy-băng.
Bấm Đánh giá Công thức trong nhóm Kiểm tra Công thức. Hộp thoại Đánh giá Công thức xuất hiện.

bên trong Evaluate Formulahộp thoại, công thức được hiển thị trong hộp bên dưới Đánh giá. Bằng cách nhấp vàoEvaluatenhiều lần, công thức được đánh giá theo từng bước. Biểu thức có gạch dưới sẽ luôn được thực hiện tiếp theo.

Ở đây, C2 được gạch dưới trong công thức. Vì vậy, nó được đánh giá trong bước tiếp theo. Nhấp chuộtEvaluate.

Ô C2 có giá trị 0,2. Do đó, C2 sẽ được đánh giá là 0,2.1+0.2được gạch chân hiển thị nó như là bước tiếp theo. Nhấp chuộtEvaluate.

1 + 0,2 sẽ được đánh giá là 1,2. SQRT(1.2)được gạch chân hiển thị nó như là bước tiếp theo. Nhấp chuộtEvaluate.

SQRT (1.2) sẽ được đánh giá là 1.09544511501033. C10được gạch chân hiển thị nó như là bước tiếp theo. Nhấp chuộtEvaluate.

C10 sẽ được đánh giá là 4976,8518518515.

1.09544511501033 * 4976.8518518515 được gạch chân hiển thị ở bước tiếp theo. Nhấp chuộtEvaluate.

1,09544511501033 * 4976,8518518515 sẽ được đánh giá là 5,451,87.

Không có biểu hiện nào nữa để đánh giá và đây là câu trả lời. CácEvaluate nút sẽ được đổi thành Restart , cho biết đã hoàn thành đánh giá.

Kiểm tra lỗi

Một phương pháp hay là thực hiện kiểm tra lỗi sau khi trang tính và / hoặc sổ làm việc của bạn đã sẵn sàng với các phép tính.

Hãy xem xét các phép tính đơn giản sau đây.

Việc tính toán trong ô đã dẫn đến lỗi # DIV / 0 !.

Bấm vào ô C5.
Bấm vào tab CÔNG THỨC trên Ruy-băng.
Bấm vào mũi tên bên cạnh Kiểm tra Lỗi trong nhóm Kiểm tra Công thức. Trong danh sách thả xuống, bạn sẽ thấy rằngCircular References bị hủy kích hoạt, cho biết rằng trang tính của bạn không có tham chiếu vòng tròn.
Lựa chọn Trace Error từ danh sách thả xuống.

Các ô cần thiết để tính ô hoạt động được chỉ định bằng các mũi tên màu xanh lam.

Nhấp vào Xóa mũi tên.
Nhấp vào mũi tên bên cạnh Kiểm tra Lỗi.
Chọn Kiểm tra lỗi từ danh sách thả xuống.

Các Error Checking hộp thoại xuất hiện.

Quan sát những điều sau -

Nếu bạn nhấp vào Help on this error, Excel trợ giúp về lỗi sẽ được hiển thị.
Nếu bạn nhấp vào Show Calculation Steps, Hộp thoại Đánh giá Công thức xuất hiện.
Nếu bạn nhấp vào Ignore Error, hộp thoại Kiểm tra Lỗi đóng lại và nếu bạn nhấp vào Error Checking lệnh một lần nữa, nó bỏ qua lỗi này.
Nếu bạn nhấp vào Edit in Formula Bar, bạn sẽ được đưa đến công thức trong thanh công thức để bạn có thể chỉnh sửa công thức trong ô.