Cách phân tích bảng tính do người khác lập (Không cần phân tích ngược)
Trước khi tin tưởng một con số trong một workbook được bàn giao lại, bạn cần ba điều. Thứ nhất là trang tính nào mới là nguồn thực sự. Thứ hai là những ô nào chứa giá trị nhập tay thay vì công thức. Thứ ba là tệp liên kết ra bên ngoài ở những đâu. Mọi thứ khác chỉ là chi tiết.
Hầu hết mọi người đều bỏ qua và đi thẳng đến tab tổng hợp rồi bắt đầu đọc. Đó là lý do tại sao một giá trị ghi đè được nhập cứng từ mười một tháng trước lại xuất hiện trong tài liệu họp hội đồng quản trị.
Hướng dẫn này sẽ giải thích lý do tại sao một workbook được bàn giao lại rất khó đọc, ba cách mọi người thường dùng để giải mã nó, và giới hạn của từng phương pháp.
Tại sao một bảng tính do người khác xây dựng lại khó đọc
Một workbook ghi lại các quyết định chứ không chỉ dữ liệu. Những quyết định đó vô hình, và người đưa ra chúng thường đã rời khỏi đội ngũ.
Vấn đề khó khăn nhất là một ô hiển thị 48,200 không đưa ra bất kỳ manh mối nào về nguồn gốc của nó. Đó có thể là một công thức, một giá trị được dán vào, hoặc một công thức đã bị ai đó ghi đè trực tiếp trong lúc vội chạy deadline. Cả ba trường hợp trông đều giống hệt nhau.
Cấu trúc cũng bị ẩn đi. Các trang tính có thể bị ẩn, các hàng có thể được nhóm và thu gọn, và một vùng đặt tên có thể trỏ đến một nơi hoàn toàn khác so với những gì tên của nó gợi ý. Các liên kết ngoài dẫn đến một tệp mà bạn không có sẽ tiếp tục hiển thị kết quả được lưu trong bộ nhớ đệm gần nhất mà không hề báo lỗi.
Tiếp theo là vấn đề về phiên bản. Khi một thư mục chứa các tệp model_v3, model_final và model_final_USE_THIS, tên tệp không phải là bằng chứng cho bất kỳ điều gì.
Những cái giá bạn phải trả
Mất cả ngày trước khi bạn có thể trả lời một câu hỏi. Yêu cầu đầu tiên thường đơn giản, chẳng hạn như tại sao một tổng số lại thay đổi. Để trả lời một cách chính xác, bạn phải lập bản đồ toàn bộ workbook trước, vì bạn không thể loại trừ khả năng có một giá trị ghi đè mà bạn chưa tìm thấy.
Sự tự tin không có cơ sở. Giải pháp thay thế cho việc lập bản đồ là tin tưởng vào tab tổng hợp. Điều đó giúp đưa ra câu trả lời nhanh chóng nhưng lại khiến bạn không có cách nào để bảo vệ nó khi có người chất vấn.
Lỗi đứt gãy xuất hiện muộn hơn. Việc chỉnh sửa một workbook chưa được lập bản đồ có thể vô tình làm đứt gãy một mối liên kết phụ thuộc một cách âm thầm. Con số vẫn được tính toán, vì vậy không có gì vẻ như bị lỗi cho đến khi người kiểm duyệt nhận ra số liệu đó đã ngừng cập nhật.
Những cái giá này đè nặng nhất lên người cuối cùng nắm giữ tệp. Khi một bảng tính do người khác xây dựng trải qua ba đời chủ sở hữu, mỗi người lại thêm vào một bản vá và không ai trong số họ ghi chép lại.
Những giải pháp tạm thời mà mọi người thường thử
Lựa chọn 1: Tách biệt các con số nhập tay khỏi các con số được tính toán
Trước khi đọc bất kỳ logic nào, hãy tìm xem những ô nào là dữ liệu đầu vào. ISFORMULA trả về TRUE cho bất kỳ ô nào chứa công thức, vì vậy một cột phụ trên trang tính sẽ ngay lập tức làm lộ ra các giá trị được nhập cứng.
Nếu bạn muốn xem logic thay vì chỉ đánh dấu nó, FORMULATEXT sẽ trả về công thức dưới dạng văn bản. Được đặt bên cạnh các giá trị, nó biến một khối dữ liệu mơ hồ thành một thứ dễ đọc.
Đây là bước đầu tiên có giá trị cao nhất và thực sự nhanh chóng. Giới hạn của nó là phạm vi bao phủ: bạn phải áp dụng nó cho từng trang tính, và một workbook lớn thường có nhiều trang tính hơn cả sự kiên nhẫn của bạn.
Lựa chọn 2: Theo dõi các mối liên kết phụ thuộc
Các công cụ Formula Auditing của Excel sẽ vẽ ra các mối quan hệ. Microsoft có tài liệu hướng dẫn về việc hiển thị mối quan hệ giữa các công thức và các ô, trong đó Trace Precedents hiển thị những gì cung cấp dữ liệu cho một ô và Trace Dependents hiển thị những gì nhận dữ liệu từ ô đó.
Màu sắc của các mũi tên mang thông tin. Mũi tên màu xanh lam biểu thị các ô không có lỗi, và mũi tên màu đỏ trỏ vào các ô gây ra lỗi. Một mũi tên màu đen trỏ đến biểu tượng trang tính có nghĩa là tham chiếu nằm trên một trang tính khác hoặc trong một workbook khác. Trường hợp cuối cùng đó chính là cách bạn phát hiện ra một mối liên kết phụ thuộc bên ngoài.
Đối với một công thức phức tạp duy nhất, việc đánh giá nó theo từng bước sẽ hiển thị từng kết quả trung gian. Cách này tuy chậm nhưng đáng tin cậy.
Giới hạn ở đây là khối lượng tính toán. Việc theo dõi là một thao tác trên từng ô, và một mô hình với bốn trăm công thức đồng nghĩa với bốn trăm thao tác.
Lựa chọn 3: Chạy một bản kiểm kê cấp độ workbook
Thay vì đọc từng ô, hãy lập danh mục cho tệp. Liệt kê mọi trang tính bao gồm cả những trang tính bị ẩn, mọi liên kết ngoài, mọi vùng đặt tên, và mọi vị trí mà cấu trúc công thức bị phá vỡ ở giữa chừng của một cột.
Microsoft có tài liệu hướng dẫn về một add-in dành riêng cho việc này, Spreadsheet Inquire, giúp phân tích cấu trúc và các mối quan hệ trong workbook. Khả năng khả dụng tùy thuộc vào phiên bản Office của bạn, vì vậy hãy kiểm tra trang trước khi lên kế hoạch sử dụng nó. Các tham chiếu vòng cần được xử lý riêng, và Microsoft có hướng dẫn riêng về việc tìm và xử lý chúng.
Việc kiểm kê là lựa chọn toàn diện nhất nhưng cũng tốn nhiều công sức nhất. Nó cũng trả lời một câu hỏi khác với câu hỏi mà bạn được yêu cầu giải quyết.
Giới hạn chung. Cả ba phương pháp đều giải thích cách workbook tính toán. Không phương pháp nào cho bạn biết liệu các con số có chính xác hay không, và không có công sức nào trong số đó còn nguyên vẹn khi phiên bản thứ tư xuất hiện.
Cách phân tích một workbook được bàn giao lại bằng Powerdrill Bloom
Bước 1: Tải workbook lên
Tải tệp lên đúng như trạng thái ban đầu khi bạn nhận được, không cần dọn dẹp trước. Powerdrill Bloom sẽ lập hồ sơ phân tích cho từng trang tính ngay khi tải lên. Số lượng trang tính, các loại cột, các khối trống và các loại giá trị không nhất quán đều hiển thị rõ ràng trước khi bạn đọc bất kỳ một công thức nào.
Bước 2: Đặt các câu hỏi về cấu trúc bằng ngôn ngữ tự nhiên
Hãy bắt đầu với bản đồ cấu trúc thay vì các con số. Hãy hỏi xem trang tính nào trông giống như dữ liệu đầu vào thô và trang tính nào trông giống như bảng tổng hợp được trích xuất, và trường dữ liệu giống nhau xuất hiện với các giá trị khác nhau ở những trang tính nào.
Sau đó, hãy hỏi thẳng vào câu hỏi về độ tin cậy. Hỏi xem những cột nào bị phá vỡ cấu trúc mẫu ở giữa chừng, và những tổng số nào không khớp với các hàng bên dưới chúng. Hai câu trả lời đó sẽ giúp xác định vị trí của hầu hết các giá trị ghi đè.
Bước 3: Xuất biểu đồ, báo cáo hoặc slide trình bày
Xuất bản tóm tắt cấu trúc của workbook, hoặc một biểu đồ từ trang tính mà bạn quyết định tin tưởng. Một ghi chú ngắn bằng văn bản ghi lại những gì bạn đã xác minh cũng rất hữu ích.
Tại sao cách này vượt trội hơn việc đọc công thức từng ô một
| Lộ trình thủ công | Powerdrill Bloom | |
|---|---|---|
| Tìm các giá trị nhập cứng | Cột phụ cho mỗi trang tính | Hỏi xem những giá trị nào phá vỡ cấu trúc mẫu |
| Hiểu các mối quan hệ | Theo dõi mũi tên, từng ô một | Hỏi xem trang tính nào cung cấp dữ liệu cho trang tính nào |
| Kiểm tra xem tổng số có chính xác không | Tự tính toán lại thủ công | Hỏi xem nó có khớp với các hàng của nó không |
| Khi phiên bản thứ tư xuất hiện | Lặp lại mọi thứ | Tải tệp mới lên |
Hàng cuối cùng chính là hàng làm thay đổi hành vi. Lập bản đồ cho một workbook một lần là một công việc vừa phải cho một buổi chiều. Nhưng việc phải lập bản đồ lại mỗi khi đồng nghiệp gửi một bản sửa đổi là lý do khiến mọi người ngừng kiểm tra.
Các sai lầm thường gặp
Tin tưởng vào tab tổng hợp. Đây là trang tính được chỉnh sửa nhiều nhất trong bất kỳ workbook nào và là nơi dễ chứa bản vá thủ công nhất. Hãy xác minh nó với phần chi tiết trước khi trích dẫn số liệu.
Chỉnh sửa trước khi lập bản đồ. Việc thay đổi một ô trong một cấu trúc mà bạn chưa hiểu rõ có thể làm đứt gãy một mối liên kết phụ thuộc một cách âm thầm. Hãy lập bản đồ trước, sau đó mới chỉnh sửa.
Giả định các cột có tính nhất quán. Một công thức chạy trơn tru trong hai trăm hàng có thể bị ghi đè ở hàng thứ 201. Hãy kiểm tra cấu trúc mẫu dọc theo toàn bộ cột, chứ không chỉ ở phần đầu.
Bỏ qua các trang tính bị ẩn. Một trang tính bị ẩn thường chứa bảng tra cứu mà mọi thứ khác đều phụ thuộc vào. Hãy hiện tất cả các trang tính trước khi đưa ra kết luận rằng tệp đó đơn giản.
Coi tên tệp là phiên bản. Một tệp có tên "final" không phải là bằng chứng. Hãy so sánh các con số thực tế giữa các tệp ứng viên trước khi chọn một tệp — hướng dẫn của chúng tôi về cách phân tích nhiều tệp Excel cùng lúc sẽ đề cập đến việc so sánh đó.
Xây dựng lại từ đầu. Việc này rất cám dỗ, nhưng thường là một sai lầm. Việc xây dựng lại sẽ làm mất đi các quy tắc không được ghi chép mà bản gốc đã thiết lập, và những quy tắc đó thường là lý do duy nhất giúp các con số khớp nhau.
Dọn dẹp trước khi hiểu rõ. Việc xóa các ô bị gộp và các hàng trống giúp tệp dễ đọc hơn nhưng lại phá hủy bằng chứng về cách nó được xây dựng. Hãy tạo một bản sao trước.
Kết luận
Một workbook được bàn giao lại là một bài toán về đọc hiểu trước khi nó là một bài toán về phân tích. Hãy tìm trang tính nguồn thực sự, tách biệt các giá trị nhập tay khỏi các giá trị được tính toán, lần theo các tham chiếu ra bên ngoài, và chỉ sau đó mới trả lời câu hỏi mà bạn được yêu cầu.
Không có điều nào trong số này là do không tin tưởng người đã viết ra nó. Một bảng tính do người khác xây dựng là một bản ghi lại các quyết định được đưa ra dưới áp lực deadline, và việc đọc nó một cách cẩn thận là cái giá phải trả để sử dụng nó.
Điều khiến nó trở nên tốn kém là việc phải làm lại điều đó cho mỗi bản sửa đổi. Nếu đó là nơi tiêu tốn thời gian trong tuần của bạn, hãy thử Powerdrill Bloom trên tệp chính xác như khi bạn nhận được. Xem thêm các hướng dẫn của chúng tôi về phân tích Excel bằng AI và dọn dẹp và loại bỏ dữ liệu trùng lặp, cùng với các trang trợ lý AI cho Excel và dọn dẹp dữ liệu bằng AI.
Câu hỏi thường gặp
Làm cách nào để tìm các giá trị nhập cứng trong một bảng tính do người khác xây dựng?
Thêm một cột phụ bằng cách sử dụng ISFORMULA, hàm này trả về TRUE cho các ô chứa công thức và FALSE cho các ô nhập tay. Mỗi giá trị FALSE bên trong một khối tính toán là một giá trị ghi đè thủ công đáng để điều tra.
Làm cách nào để xem công thức đằng sau một ô dưới dạng văn bản?
Sử dụng FORMULATEXT ở một ô bên cạnh. Nó trả về công thức dưới dạng một chuỗi văn bản dễ đọc, giúp bạn có thể quét qua một cột logic mà không cần nhấp vào từng ô.
Làm cách nào để tìm ra một ô phụ thuộc vào những gì?
Sử dụng Trace Precedents trên tab Formulas để xem những gì cung cấp dữ liệu cho ô đó, và Trace Dependents để xem những gì nhận dữ liệu từ nó. Một mũi tên màu đen trỏ đến biểu tượng trang tính có nghĩa là tham chiếu nằm ngoài trang tính hiện tại.
Tôi có nên dọn dẹp một workbook được bàn giao lại trước khi phân tích nó không?
Không nên làm vậy trước khi lập bản đồ cho nó. Việc dọn dẹp sẽ xóa đi bằng chứng về cách tệp được xây dựng, bao gồm cả các ô bị gộp và các khối trống dùng để đánh dấu cấu trúc. Dù thế nào đi nữa, hãy giữ một bản sao nguyên bản.
Cách nhanh nhất để kiểm tra xem một tổng số có đáng tin cậy hay không là gì?
Hãy tính toán lại nó từ các hàng bên dưới và so sánh. Nếu hai kết quả không khớp nhau, tổng số đó có chứa một giá trị ghi đè, một phạm vi được lọc, hoặc một tham chiếu đến một trang tính mà bạn chưa xem xét đến.