Spreadsheet Formulas Cheat Sheet: The 20 You Actually Need — csv-x.com

Tôi vẫn nhớ khoảnh khắc mà tôi nhận ra mình đã làm bảng tính sai trong bảy năm. Đó là năm 2019, và tôi đang ngồi trong một phòng hội nghị cùng đội của mình tại một công ty logistics vừa và nhỏ, quan sát Giám đốc Tài chính của chúng tôi sao chép và dán dữ liệu thủ công giữa ba tệp Excel khác nhau để tạo báo cáo quý của chúng tôi. Quá trình này mất của cô ấy bốn giờ. Bốn giờ của thời gian $150/giờ của cô, mỗi quý một lần, vì không ai chịu học VLOOKUP.

Ngày hôm đó đã thay đổi mọi thứ đối với tôi. Tôi là Sarah Chen, và tôi đã dành 11 năm qua như một tư vấn viên vận hành tài chính, làm việc với hơn 200 công ty để tối ưu hóa quy trình dữ liệu của họ. Tôi đã thấy các doanh nghiệp lãng phí hàng nghìn giờ trên những công việc bảng tính có thể được tự động hóa chỉ với một vài công thức. Trớ trêu thay? Hầu hết mọi người nghĩ rằng họ cần phải nắm vững hàng trăm hàm để "giỏi Excel." Họ không cần. Theo kinh nghiệm của tôi, 20 công thức xử lý khoảng 95% tình huống doanh nghiệp trong thực tế.

Đây không phải là một cuốn bách khoa toàn thư toàn diện về mọi hàm bảng tính từng được tạo ra. Đây là bộ công cụ đã được kiểm chứng mà tôi đã sử dụng để tiết kiệm cho khách hàng của mình ước tính hơn 12,000 giờ công việc thủ công. Đây là những công thức thực sự có ý nghĩa khi bạn đang cố gắng đóng sổ, phân tích dữ liệu bán hàng, hoặc tìm ra lý do tại sao số lượng hàng tồn kho của bạn không khớp.

Nền Tảng: Các Phép Tính Cơ Bản Điều Khiển Mọi Thứ

Trước khi đi vào chi tiết, hãy nói về ba công thức hình thành nên xương sống của hầu như mọi mô hình tài chính mà tôi từng xây dựng. Những điều này có thể có vẻ hiển nhiên, nhưng tôi đã chứng kiến các nhà phân tích cấp cao làm sai theo cách mà khiến các công ty mất tiền thật.

SUM là công thức chủ lực. Cú pháp rất đơn giản: =SUM(A1:A10) cộng tất cả trong phạm vi đó lại. Nhưng đây là điều mà hầu hết mọi người không biết: bạn có thể sử dụng SUM với nhiều phạm vi không liên tiếp. =SUM(A1:A10, C1:C10, E5) hoạt động hoàn hảo và sạch hơn việc viết ra từng tham chiếu ô. Tôi từng kiểm tra một mô hình tài chính mà ai đó đã viết ra =A1+A2+A3+A4+A5+A6+A7+A8+A9+A10 thay vì sử dụng SUM. Khi họ cần chèn một hàng, công thức đã bị hỏng. Đừng trở thành người đó.

AVERAGE cũng đơn giản nhưng lại mạnh mẽ đến bất ngờ. =AVERAGE(B2:B50) cho bạn giá trị trung bình của tập dữ liệu của bạn. Điểm chính ở đây là hiểu khi nào nên sử dụng AVERAGE so với AVERAGEIF (cái mà chúng tôi sẽ đề cập sau). Trong công việc tư vấn của tôi, tôi nhận thấy khoảng 60% thời gian mà mọi người sử dụng AVERAGE, họ thực sự cần một giá trị trung bình có điều kiện. Nếu bạn đang tính toán doanh thu trung bình nhưng muốn loại trừ số không hoặc các giá trị ngoại lai, AVERAGE thông thường sẽ khiến bạn hiểu sai.

COUNT và COUNTA là hai nàng sinh đôi có tính cách khác nhau. COUNT chỉ đếm các ô có số, trong khi COUNTA đếm mọi ô không trống. Sự phân biệt này quan trọng hơn bạn nghĩ. Khi tôi đánh giá chất lượng dữ liệu, tôi sử dụng =COUNTA(A:A)-COUNT(A:A) để ngay lập tức tìm ra bao nhiêu ô trong cột A chứa văn bản thay vì số. Nếu con số đó cao hơn mong đợi, tôi biết có vấn đề trong việc nhập dữ liệu. Kiểm tra đơn giản này đã phát hiện hàng ngàn đô la trong lỗi kế toán cho khách hàng của tôi.

Hàm Tra Cứu: Các hàm

Nếu tôi chỉ có thể dạy ai đó ba công thức, hai trong số đó sẽ là hàm tra cứu. Đây là sự khác biệt giữa việc tốn 30 phút để khớp dữ liệu thủ công và chỉ mất 30 giây để viết một công thức làm việc đó ngay lập tức.

"Theo kinh nghiệm của tôi khi làm việc với hơn 200 công ty, 20 công thức xử lý khoảng 95% tình huống doanh nghiệp trong thực tế. Hầu hết mọi người lãng phí thời gian cố gắng nắm vững hàng trăm hàm mà họ sẽ không bao giờ sử dụng."

VLOOKUP là cái cổ điển, và mặc dù có các lựa chọn thay thế mới hơn, nó vẫn cực kỳ hữu ích. Cú pháp là =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). Dưới đây là một ví dụ thực tế từ tháng trước: Tôi có một khách hàng với 5,000 hồ sơ khách hàng trong một tệp và 5,000 hồ sơ giao dịch trong một tệp khác. Họ cần khớp tên khách hàng với ID giao dịch. =VLOOKUP(A2, Customers!A:D, 3, FALSE) đã làm trong ba giây điều mà sẽ mất hàng giờ nếu làm thủ công.

Điều quan trọng mà mọi người đều mắc sai lầm: tham số thứ tư đó. FALSE có nghĩa là khớp chính xác, TRUE có nghĩa là khớp gần đúng. Đối với 99% các trường hợp sử dụng trong doanh nghiệp, bạn muốn FALSE. Tôi đã thấy toàn bộ báo cáo tài chính bị hỏng vì ai đó để nó là TRUE (hoặc bỏ qua nó, điều này mặc định là TRUE) và nhận được dữ liệu khớp gần đúng thay vì chính xác.

XLOOKUP là sự thay thế hiện đại, và thật lòng mà nói, nó tốt hơn gần như mọi cách. =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) nghe có vẻ phức tạp, nhưng trong thực tế thì nó sạch sẽ hơn. Tính năng nổi bật? Nó có thể nhìn sang trái, điều mà VLOOKUP không thể. Gần đây tôi đã chuyển toàn bộ hệ thống báo cáo của một khách hàng từ VLOOKUP sang XLOOKUP và giảm lỗi công thức của họ xuống khoảng 40% vì XLOOKUP xử lý dữ liệu thiếu một cách mượt mà hơn.

INDEX-MATCH là sự chọn lựa của người dùng quyền lực. Thực tế, đây là hai công thức kết hợp: =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)). Bộ đôi này linh hoạt hơn VLOOKUP và nhanh hơn với các tập dữ liệu lớn. Khi tôi làm việc với các bảng tính trên 50,000 hàng, INDEX-MATCH hoạt động tốt hơn đáng kể. Đường cong học tập steeper hơn, nhưng một khi bạn đã hiểu, bạn sẽ không quay lại. Tôi sử dụng nó cho các mô hình tài chính phức tạp mà tôi cần tìm giá trị dựa trên hai tiêu chí đồng thời.

Logic Điều Kiện: Biến Bảng Tính Thành Công Cụ Suy Nghĩ

Đây là nơi bảng tính chuyển mình từ máy tính thành công cụ ra quyết định. Các công thức điều kiện cho phép bạn xây dựng logic vào dữ liệu của mình, và chúng rất cần thiết cho bất kỳ loại báo cáo tự động nào.

IF là loại thuốc khởi đầu cho tự động hóa bảng tính. =IF(logical_test, value_if_true, value_if_false) cho phép bạn tạo logic phân nhánh. Một ví dụ đơn giản: =IF(B2>1000, "Giá Trị Cao", "Chuẩn") phân loại khách hàng dựa trên số tiền mua. Nhưng sức mạnh thực sự đến từ việc lồng ghép IF. Tôi đã xây dựng máy tính hoa hồng với năm câu lệnh IF lồng ghép tự động xác định tiền thưởng của nhân viên bán hàng dựa trên nhiều tiêu chí. Chìa khóa là giữ cho chúng dễ đọc — sử dụng thụt lề phù hợp và ghi chú.

IFS là lựa chọn sạch hơn cho IF lồng ghép, có sẵn trong các phiên bản bảng tính mới hơn. Thay vì viết =IF(A1>90, "A", IF(A1>80, "B", IF(A1>70, "C", "F"))), bạn viết =IFS(A1>90, "A", A1>80, "B", A1>70, "C", TRUE, "F"). Nó dễ đọc hơn và ít có khả năng mắc lỗi ngoặc hơn. Tôi đã chuyển sang IFS khoảng ba năm trước và thời gian gỡ lỗi công thức của tôi đã giảm một nửa.

SUMIF và SUMIFS là những cỗ máy tổng có điều kiện. =SUMIF(range, criteria, [sum_range]) cộng tổng các giá trị thỏa mãn một điều kiện. =SUMIFS mở rộng điều này sang nhiều điều kiện. Ví dụ thực tế: Tôi đã có một khách hàng bán lẻ cần tính tổng doanh số theo khu vực và loại sản phẩm. =SUMIFS(Sales!C:C, Sales!A:A, "Tây", Sales!B:B, "Điện Tử") đã cung cấp cho họ đúng điều đó. Trước khi học SUMIFS, họ đã sử dụng bảng xoay cho mọi thứ, điều đó quá phức tạp và chậm hơn để cập nhật.

COUNTIF và COUNTIFS hoạt động giống nhau nhưng đếm thay vì tổng. Tôi sử dụng chúng liên tục để kiểm tra chất lượng dữ liệu. =COUNTIF(A:A, "Lỗi") cho biết có bao nhiêu ô trong cột A chứa từ "Lỗi." =COUNTIFS(A:A, ">100", B:B, "<50") đếm các hàng mà cột A lớn hơn 100 VÀ cột B nhỏ hơn 50. Những công thức này đã tiết kiệm cho tôi hàng triệu giờ kiểm tra dữ liệu thủ công.