Excel là một trong những công cụ phần mềm kế toán mạnh mẽ nhất hiện có và phần lớn sức mạnh đó đến từ khả năng sử dụng các công thức Excel để tự động hóa công việc của bạn. Khi bạn lần đầu tiên học cách sử dụng chúng, bạn sẽ dễ bị choáng ngợp một chút - may mắn thay, bảng cheat công thức Excel này ở đây để giúp bạn hiểu được tất cả
Chúng ta hãy xem những kiến thức cơ bản về công thức, một số chức năng phổ biến mà bạn sẽ sử dụng với chúng và cách sắp xếp chúng ngăn nắp cho dù chúng phức tạp đến mức nào
Sự khác biệt giữa hàm và công thức là gì?
Rất nhiều người sử dụng thuật ngữ “hàm số” và “công thức” thay thế cho nhau, nhưng chúng thực sự là hai thứ khác nhau. Một công thức bắt đầu bằng một dấu bằng theo sau là một biểu thức, có thể bao gồm một hoặc nhiều hàm. Một hàm bắt đầu bằng một tên và thường được theo sau bởi các đối số được đặt trong dấu ngoặc đơn
Đây là một ví dụ về công thức sử dụng hàm COUNT
=COUNT[A1:A15]
Bạn có thể xem cách công thức sử dụng hàm COUNT để tính toán kết quả, đó là số lượng các số trong phạm vi ô từ A1 đến A15
Danh sách bên dưới hiển thị một ví dụ nhỏ về các hàm Excel mà bạn có thể thấy mình thường xuyên sử dụng.
SUMTính tổng của các giá trị đã xác địnhSUMIFSTính tổng của một tập hợp các giá trị trong đó đáp ứng một hoặc nhiều điều kiệnAVERAGETìm giá trị trung bình trong một nhóm giá trịMEDIANTìm giá trị trung bình trong một nhóm giá trịMINTính số nhỏ nhất trong một dảiMAXTính số cao nhất trong một dải COUNTĐếmKhi làm việc với tên, địa chỉ, danh sách khách hàng hoặc bất kỳ dữ liệu dựa trên văn bản nào khác, các hàm văn bản của Excel sẽ rất hữu ích.
Một số chức năng văn bản bạn sẽ sử dụng thường xuyên bao gồm
Các chức năng tra cứu rất hữu ích cho việc tìm kiếm thông qua một lượng lớn dữ liệu. Mặc dù hầu hết người dùng Excel đều có hàm Tra cứu [hoặc cặp hàm] yêu thích của riêng họ, nhưng mỗi hàm đều có các đặc quyền hoạt động tốt nhất trong các trường hợp khác nhau.
VLOOKUPTìm kiếm một giá trị trong bảng theo chiều dọcHLOOKUPTìm kiếm một giá trị trong bảng theo chiều ngangHàm tra cứuINDEXA tìm kiếm một bảng theo cả chiều dọc và chiều ngangMATCHTrả về vị trí của một giá trị trong một chuỗiXLOOKUPMột phiên bản cập nhật của VLOOKUP có sẵn trong các phiên bản Excel mới hơnOFFSETDi chuyển tham chiếu ô theo số lượng đã choMặc dù đó là một số hàm bạn sẽ thường sử dụng trong công thức của mình, nhưng nó không phải là toàn bộ danh sách. Nếu bạn cần danh sách đầy đủ các chức năng của Excel, Microsoft sẽ sắp xếp chúng theo danh mục một cách thuận tiện
Bây giờ bạn đã biết các hàm phổ biến, công thức nào sử dụng chúng?
Phần lớn, các công thức hoàn toàn có thể tùy chỉnh dựa trên nhu cầu của người dùng. Những cái bạn sử dụng thường xuyên nhất sẽ thay đổi dựa trên loại công việc bạn cần làm.
Tuy nhiên, bạn vẫn có thể thực hành bằng cách làm việc với một số công thức đơn giản. Khi bạn nắm vững kiến thức cơ bản, việc tạo các công thức phức tạp, tùy chỉnh sẽ dễ dàng hơn rất nhiều – và thậm chí còn thú vị nữa
Công thức có thể chứa
- Toán tử – chẳng hạn như +, -, *, /
- Hàm - chẳng hạn như SUM
- Các hàm lồng nhau – chẳng hạn như INDEX[MATCH[]]
- Tên – chẳng hạn như Tên công ty [được đặt tên bằng Trình quản lý tên]
- Hằng số – chẳng hạn như 10 hoặc 20
- Tham chiếu ô – chẳng hạn như A1
Để biết một số cách thực hành tốt với các công thức Excel cơ bản, hãy xem các bài đăng này
- Tính tổng N cột cuối cùng – Hướng dẫn này bắt đầu bằng một công thức đơn giản sử dụng hàm SUM và sau đó kết hợp hàm INDEX, vì vậy bạn sẽ thực hành với cả hai
- Danh sách tháng Excel – Công thức trong bài đăng này sử dụng các hàm ngày và giờ phổ biến để tạo lịch đáp ứng
- Đếm giữa các ngày – Bài đăng này sẽ giúp bạn làm quen với các công thức chứa hàm COUNTIFS
Khi bạn đã cảm thấy thoải mái với việc tạo các công thức đơn giản, bạn sẽ muốn bắt đầu sử dụng hai công cụ hữu ích khi chúng trở nên nâng cao hơn. phạm vi tham chiếu và chức năng LET
Bảng cheat công thức Excel - tham chiếu phạm vi
Bạn có thể tham chiếu dữ liệu trong các phần khác của trang tính bằng cách cung cấp tham chiếu phạm vi trong công thức hoặc đối số hàm. Bạn có thể tham chiếu các ô [hoặc một dải ô] khi tạo công thức cơ bản hoặc công thức bao gồm một hàm. Ví dụ: giả sử bạn chọn tham chiếu ô B2 – công thức sẽ tính toán kết quả bằng cách sử dụng giá trị của ô đó. Công thức sẽ tự động tính toán lại kết quả của nó khi giá trị trong ô đó thay đổi
Trong Excel, bạn có thể sử dụng hai loại tham chiếu ô
- Tương đối – Tham chiếu ô tự điều chỉnh khi ô được sao chép hoặc điền
- Tuyệt đối – Tham chiếu ô không thay đổi khi được sao chép hoặc điền
Khi viết công thức, bạn có thể kết hợp các tham chiếu tương đối và tuyệt đối để hàng thay đổi nhưng cột thì không hoặc ngược lại.
Để gán tham chiếu tuyệt đối, bạn sẽ sử dụng ký hiệu đô la [$]. Vị trí bạn chèn nó vào tham chiếu ô sẽ xác định xem cột, hàng hay cả hai là tuyệt đối. Nghe có vẻ hơi khó hiểu, nhưng không quá khó để học khi bạn xem một vài ví dụ
Giả sử bạn cần tham chiếu lại ô B2. Đây là cách bạn chỉ định loại tham chiếu
=B2 – Tham chiếu hoàn toàn tương đối
=$B2 – Cột [B] là tuyệt đối, nhưng hàng [2] là tương đối
=B$2 – Cột [B] là tương đối, hàng [2] là tuyệt đối
=$B$2 – Tham chiếu hoàn toàn tuyệt đối
Khi bạn cần lặp lại cùng một phép tính trên nhiều hàng hoặc cột, tham chiếu tương đối sẽ hữu ích. Tham chiếu tuyệt đối hữu ích khi bạn cần giữ một tham chiếu không đổi, chẳng hạn như nếu bạn cần tính một tỷ lệ phần trăm thuế bán hàng duy nhất trên một dải ô.
Sử dụng LET để giữ cho các công thức dài hơn không nằm ngoài tầm tay
Không có bảng cheat công thức Excel nào sẽ hoàn chỉnh nếu không đề cập đến hàm LET hữu ích. Các công thức Excel thường khá ngắn gọn và hấp dẫn. Do đó, chúng rất dễ hiểu và duy trì theo thời gian. Một ví dụ về một công thức ngắn trông giống như thế này
=SUM[B2:B8]
Công thức này sử dụng hàm SUM để cộng các giá trị từ ô B2 đến ô B8. Khá đơn giản, phải không?
Khi các bảng tính trở nên phức tạp hơn, các công thức cũng có xu hướng phức tạp hơn. Bạn có thể kết thúc với các công thức rườm rà, khó đọc và khó duy trì, thường là do trùng lặp. Ví dụ: một công thức có nhiều lần trùng lặp có thể trông như thế này
=IF[VLOOKUP[B8,Table1,2,0]>100, VLOOKUP[B8,Table1,2,0], VLOOKUP[B8,Table1,2,0]*C8]
Bạn có thể thấy rằng hàm VLOOKUP[B8, Table1, 2, 0] được sử dụng 3 lần khiến cho công thức trở nên dài dòng, lặp đi lặp lại và hơi khó hiểu. Hàm LET cho phép bạn gán tên cho các giá trị trong công thức của mình, sau đó sử dụng tên nếu cần trong suốt phần còn lại của công thức
Nó có thể sử dụng công thức khó hiểu ở trên và biến nó thành một thứ dễ đọc hơn nhiều
=LET[Rate, VLOOKUP[B8,Table1,2,0], IF[Rate>100,Rate,Rate*C8]]
Sử dụng hàm LET được coi là một kỹ năng nâng cao hơn, nhưng người mới bắt đầu có thể bắt đầu sử dụng hàm này trong các hàm đơn giản hơn để làm quen với cách thức hoạt động của hàm
Trong Excel, các công thức có thể được sử dụng để tính toán bất cứ thứ gì bạn có thể nghĩ ra – bạn không cần phải sợ phải sáng tạo một chút. Mặc dù bảng cheat công thức Excel này bao gồm các yếu tố cần thiết của công thức Excel, nhưng cách tốt nhất để thực sự quen thuộc với chúng là thực hành bằng cách yêu cầu Excel tính toán càng nhiều giá trị càng tốt. Nói cách khác, khi bạn thấy mình đang nhập thủ công một giá trị vào trang tính của mình, hãy xem liệu bạn có thể chuyển đổi giá trị đó thành công thức không. Thói quen này không chỉ cho phép bạn thấy những gì có thể, mà còn loại bỏ bước nhập thủ công [nâng cao hiệu quả và giảm lỗi thủ công]