Hàm, công thức là một trong những nội dung đặc biệt quan trọng khi sử dụng Excel. Làm thế nào để viết công thức nhanh, chính xác và hiệu quả hơn? Trong bài viết này, Gitiho sẽ hướng dẫn các bạn chi tiết cách viết hàm, công thức và những kỹ thuật để viết nhanh hơn, chính xác hơn.
Cách viết công thức trong Excel
Khi tìm hiểu về công thức, hàm trong Excel chúng ta sẽ thấy một đặc điểm chung là chúng không thay đổi trên hầu hết các phiên bản Excel. Ở một số phiên bản cao hơn chỉ có thêm các hàm, công thức mới mà thôi. Còn cách viết các hàm đã có sẵn thì không thay đổi.
Ví dụ: để cộng các giá trị trong các ô từ B1 tới B5, bạn có thể:
Bạn cũng có thể làm được kết quả tương tự như trên khi sử dụnghàm CONCATENATE.
Cách sử dụng các toán tử tham chiếu trong công thức Excel
Để giúp việc viết hàm trở nên đơn giản và thuận tiện hơn, chúng ta có thể sử dụng một số dấu toán tử cho mục đích tham chiếu, bao gồm:
Bạn cũng có thể dùng phương pháp này để tham chiếu trên cả 1 dòng hoặc cả 1 cột. Khi viết theo cách này bạn chỉ cần xác định tọa độ của 1 đối tượng (là dòng hoặc cột) mà thôi
= IF(A1>0, Đúng, Sai) nghĩa là xem xét giá trị trong ô A1 có lớn hơn số 0 hay không, nếu có lớn hơn 0 thì trả về kết quả là chữ Đúng, còn không phải thì trả về kết quả là chữ Sai.
Trong một số trường hợp bạn có thể bắt gặp dấu chấm phẩy (;) được sử dụng để làm việc này (chứ không phải là dấu phẩy). Đây là do thiết lập trong máy tính của bạn tại Control pannel > Region > Number setting. Bạn hoàn toàn có thể thay đổi dấu này nếu muốn.
Tham khảo: Cách đổi dấu ngăn cách các thành phần trong hàm Excel từ dấu chấm phẩy sang dấu phẩy và ngược lại.
=B3:D3 C2:D4
Kết quả thu được là số 180 tương ứng với vị trí ô C3 (vừa nằm ở dòng 3, vừa nằm ở cột C)
Các loại công thức Excel
Trong Excel có rất nhiều loại công thức khác nhau. bạn có thể viết:
Chúng ta hãy cùng tìm hiểu cụ thể các cách viết này nhé:
Cách viết các phép tính trong Excel
Việc sử dụng các phép tính trong Excel cần tuân thủ các nguyên tắc về toán học:
Như vậy khi viết công thức, các bạn phải hết sức chú ý đến thứ tự viết để đảm bảo nguyên tắc này.
Ví dụ: Chúng ta có bảng chứa các thông tin về xác định tiền bán hàng các sản phẩm và tính tiền hoa hồng, tiền thuế như sau:
Để xác định số thuế, ta có quy tắc tính:
Thuế = 10% của số tiền còn lại sau khi trừ đi tiền hoa hồng.
Khi viết biểu thức tính ta có:
Khi viết dạng sử dụng dấu ngoặc, ta có phép tính D2-E2 được thực hiện trước, sau đó ra kết quả rồi mới nhân với 10% thuế suất.
Cách viết hàm Excel lồng nhau
Nguyên nhân của việc cần phải viết hàm lồng ghép là vì một hàm đôi khi không đảm bảo được kết quả chính xác ngay, mà phải qua nhiều bước tính toán mới ra. Do đó lồng hàm là điều không tránh khỏi, bạn cần phải làm quen với việc này nếu muốn sử dụng Excel tốt hơn.
Ví dụ như trong tình huống tính tiền thuế ở trên, chúng ta thấy để tính ra tiền thuế phải qua 3 bước tương ứng với 3 cột:
Vậy chúng ta có thể chỉ cần 1 cột duy nhất để tính ra kết quả này không?
Câu trả lời là có. Hãy xem kết quả dưới đây:
tại ô D2 sử dụng công thức=(B2*C2*(1-5%))*10%
Kết quả vẫn đúng với cách tính ở trên. Trong công thức này chúng ta thấy thay vì tính riêng từng giá trị Thành tiền, Hoa hồng thì chúng ta sẽ tính gộp luôn trong cùng 1 biểu thức.
Nhận xét:
Xét tiếp một ví dụ khác: Tính tổng số tiền thuế nhưng kết quả cần làm tròn tới phần nghìn (làm tròn hết các số hàng trăm, hàng chục, hàng đơn vị lên phần nghìn)
Chúng ta có công thức như sau:
=ROUND(SUM(D2:D6),-3)
Kết quả nhận được là giá trị tổng các số từ D2 tới D6 mà đã được làm tròn lên tới phần nghìn.
Trong đó chúng ta có 2 hàm:
Bạn hoàn toàn có thể tách riêng các hàm này ra thành nhiều ô, nhiều cột. Mỗi ô, mỗi cột sẽ dùng 1 hàm đơn để tính toán theo 1 hàm. Sau đó bạn dùng chính kết quả của hàm trước làm tham số cho hàm sau. Đây chính là bản chất của việc lồng ghép hàm. Khi đã quen dần với việc này, bạn có thể rút gọn quá trình viết công thức bằng cách lồng ghép những hàm đơn giản vào chung với nhau. Khi đó khả năng đọc hiểu và viết các hàm lồng ghép phức tạp của bạn sẽ được cải thiện nhiều.
Tham khảo:
Cách dùng hàm ROUND để làm tròn số trong Excel
Hướng dẫn cách lồng ghép nhiều hàm IF trong một công thức duy nhất
Cách viết công thức mảng trong Excel
Công thức mảng trong Excel là một dạng đặc biệt khi viết công thức. Nó làm cho Excel thông minh hơn khi tính toán theo những điều kiện phức tạp hơn bình thường, nhưng đồng thời nó lại khiến bạn khó khăn hơn trong việc đọc hiểu công thức này.
Để giúp bạn có thể có một khái niệm cơ bản về công thức mảng và cách hoạt động của công thức này, chúng ta sẽ xem xét ví dụ sau đây:
Tính tổng số tiền các sản phẩm có số lượng bán được lớn hơn 6
{=SUM((B2:B6>6)*(D2:D6))}
Khi kết thúc công thức này, bạn không sử dụng phím Enter như thông thường, mà phải sử dụng tổ hợp phím Ctrl + Shift + Enter. Đây là tổ hợp phím đặc trưng cho dạng công thức mảng.
Dấu { } được tạo ra ở bên ngoài công thức chính là do sử dụng tổ hợp phím Ctrl + Shift + Enter, bạn không phải gõ trực tiếp ký hiệu này vào công thức.
Giải thích kết quả:
Trong công thức trên ta thấy:
- Đầu tiên hàm sẽ xét các giá trị số lượng trong vùng B2:B6 giá trị nào lớn hơn 6. Cụ thể là tại dòng 3, dòng 4 và dòng 6 thỏa mãn điều kiện này.
- Tiếp theo ta tính tổng thành tiền tại cột D trên các dòng 3, 4, 6. Khi đó ta có 218000 + 216000 + 105000 = 449000.
Chú ý: Ở phiên bản Office 365 thì Excel tự động hiểu những công thức nào được tính toán theo mảng, nên bạn có thể viết công thức mảng như công thức bình thường mà Excel vẫn hiểu đúng được và tính toán đúng.
Kết luận
Như vậy với bài viết này, Gitiho đã chia sẻ cho các bạn cách viết các hàm, công thức trong Excel. Hy vọng bài viết này sẽ hữu ích trong việc nâng cao khả năng viết công thức tính trong Excel của bạn. Nếu có bất kỳ thắc mắc gì bạn có thể góp ý, phàn hồi ngay dưới bài viết này để chúng tôi có thể giải quyết mọi thắc mắc của bạn một cách sớm nhất.
Những kiến thức bạn đang xem thuộc khóa học Excel từ cơ bản tới nâng cao của Gitiho.com. Khóa học này cung cấp cho bạn kiến thức một cách đầy đủ và có hệ thống về các hàm, các công cụ trong excel, ứng dụng excel trong công việc Hiện nay hệ thống đang có nhiều ưu đãi khi bạn đăng ký tham gia khóa học này.
Tải về file mẫu trong bài viết
Bạn có thể tải về file mẫu sử dụng trong bài viết tại địa chỉ bên dưới:
Tài liệu kèm theo bài viết