Xu Hướng 2/2023 # Công Thức Trong Excel, Ví Dụ Và Mẹo Để Tiết Kiệm Thời Gian # Top 11 View | Hoisinhvienqnam.edu.vn

Xu Hướng 2/2023 # Công Thức Trong Excel, Ví Dụ Và Mẹo Để Tiết Kiệm Thời Gian # Top 11 View

Bạn đang xem bài viết Công Thức Trong Excel, Ví Dụ Và Mẹo Để Tiết Kiệm Thời Gian được cập nhật mới nhất trên website Hoisinhvienqnam.edu.vn. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất.

Trong MS Excel, công thức là các phương trình thực hiện các tính toán khác nhau trong các bảng tính. Mặc dù Microsoft giới thiệu thêm nhiều hàm mới qua các năm, khái niệm công thức bảng tính Excel vẫn giống nhau trong tất cả các phiên bản Excel 2016, Excel 2013, Excel 2010, Excel 2007 và thấp hơn.

Tất cả các công thức Excel bắt đầu với một dấu bằng (=).

Sau dấu bằng, bạn nhập hoặc là phép tính hoặc hàm. Ví dụ: để cộng các giá trị trong các ô từ B1 tới B5, bạn có thể:

Gõ toàn bộ phương trình: = B1 + B2 + B3 + B4 + B5

Sử dụng hàm SUM: = SUM (B1: B5)

Nhấn phím Enter để hoàn thành công thức. Làm xong!

Khi bạn tạo một công thức trong Excel, bạn có thể sử dụng các phần tử khác nhau để cung cấp dữ liệu nguồn cho công thức và chỉ ra các toán tử nên được thực hiện trên những dữ liệu đó. Tùy thuộc vào loại công thức mà bạn tạo ra, nó có thể bao gồm bất kỳ hoặc tất cả các phần sau: ·

Hằng số – số hoặc các giá trị văn bản mà bạn nhập trực tiếp vào một công thức, như = 2 * 3. ·

Tham chiếu ô – tham chiếu đến ô chứa giá trị bạn muốn sử dụng trong công thức Excel của bạn, ví dụ: = SUM (A1, A2, B5). Để tham khảo dữ liệu trong hai hoặc nhiều ô tiếp giáp, hãy sử dụng một tham chiếu phạm vi như A1: A5. Ví dụ, để tổng hợp giá trị trong tất cả các ô giữa A1 và A5, sử dụng công thức này: = SUM (A1: A5).·

Tên – được xác định cho một phạm vi ô, hằng, bảng, hoặc hàm, ví dụ = SUM (my_name). ·

Các hàm – các công thức được xác định trước trong Excel thực hiện tính toán sử dụng các giá trị được cung cấp trong các đối số của chúng. ·

Toán tử – các ký hiệu đặc biệt chỉ định loại hoạt động hoặc tính toán sẽ được thực hiện.

Để nói với Microsoft Excel loại công việc bạn muốn thực hiện trong một công thức, bạn sử dụng các ký hiệu đặc biệt được gọi là các toán tử. Có 4 loại toán tử trong Excel:

Số học – để thực hiện các phép toán cơ bản.

So sánh (hợp lý) – để so sánh các giá trị.

Kết nối – để kết hợp các giá trị văn bản vào một chuỗi duy nhất.

Tham chiếu – để tạo ra các cơn thịnh nộ và các đối số riêng biệt trong các hàm của Excel.

Sử dụng toán tử số học trong công thức Excel

Các toán tử này được sử dụng để thực hiện các phép toán cơ bản như cộng, trừ, nhân và chia.

Ví dụ: nếu bạn có giá cả trong ô A2 và VAT trong ô B2, bạn có thể tính số tiền thuế GTGT bằng cách sử dụng công thức tỷ lệ phần trăm sau: = A2 * B2

Trong các công thức Microsoft Excel, so sánh, hoặc hợp lý, toán tử được sử dụng để so sánh hai giá trị. Kết quả của việc so sánh luôn là một giá trị logic của TRUE hoặc FALSE. Các toán tử logic sau có sẵn trong Excel:

Ví dụ, công thức = A1 = B1 trả về TRUE nếu các ô A1 và B1 có cùng một giá trị (số, văn bản hoặc ngày tháng), FALSE nếu không.

Toán tử nối văn bản

Toán tử nối văn bản trong Excel là ký hiệu và (&). Bạn có thể sử dụng nó để kết hợp hai hoặc nhiều chuỗi văn bản trong một chuỗi duy nhất.

Ví dụ: nếu bạn có mã quốc gia trong cột A và số điện thoại trong cột B, bạn có thể sử dụng công thức sau để nhận số điện thoại kết hợp với mã quốc gia:

= A1 & “” & B1

Trong công thức trên, chúng ta thêm một khoảng trống vào giữa để làm cho các con số dễ đọc hơn:

Các toán tử tham chiếu trong các công thức Excel và các hàm

Để cung cấp vùng dữ liệu cho MS Excel công thức và đối số riêng biệt trong hàm Excel, các toán tử sau đây được sử dụng.

Dấu hai chấm (:) – nó là một toán tử phạm vi cho phép bạn tạo một tham chiếu cho nhiều ô nằm giữa 2 ô bạn chỉ định.

Ví dụ, phạm vi A1: A00 bao gồm 100 ô từ A1 đến A100. Để tìm trung bình của 100 ô này, bạn sử dụng công thức sau: = AVERAGE (A1: A00)

Bạn cũng có thể tham khảo toàn bộ cột (A: A) hoặc toàn bộ hàng (1: 1). Ví dụ: công thức sau tìm tổng số tất cả các số trong cột A: = SUM (A: A).

Các công thức Excel đơn giản chỉ thực hiện một thao tác toán học, ví dụ = 10 * 5 hoặc = SUM (A1: A10)

Các công thức Excel phức tạp (tiên tiến) bao gồm nhiều tính toán, ví dụ = 10 * 5 + 20 hoặc = SUM (A1: A10) / 2

Hơn nữa trong hướng dẫn này, bạn sẽ tìm thấy các bước chi tiết để tạo cả hai loại công thức bảng tính Excel.

Cách tạo các công thức trong Excel

Làm thế nào để tạo các công thức đơn giản trong Excel

Mặc dù các công thức Excel đơn giản chỉ thực hiện một phép tính, chúng có thể thực hiện theo nhiều cách khác nhau. Bạn có thể cung cấp dữ liệu nguồn dưới dạng hằng số, tham chiếu ô, hoặc các tên được xác định, và thực hiện tính toán bằng cách sử dụng các toán tử toán học hoặc các hàm Excel.

Cách tạo các công thức tiên tiến trong Excel

Khi bạn có một số kinh nghiệm với các công thức Excel đơn giản, bạn có thể muốn thực hiện một số tính toán trong một công thức. Và các ví dụ sau cho thấy cách bạn có thể làm điều này.

Tạo các công thức phức tạp với các hằng số và các toán tử toán học

Đối với một công thức phức tạp để chính xác, một số thao tác nhất định phải được thực hiện trước. Lệnh mặc định hoạt động của các công thức Excel là:

Phép tính trong dấu ngoặc đơn

Lũy thừa

Nhân và chia, tùy điều kiện nào đến trước trong một công thức

Cộng và trừ, tùy điều kiện nào đến trước trong một công thức

Ví dụ: bạn có thể sử dụng các công thức sau để tính tổng số và hoa hồng:

Công thức: = $B2*$D2 + $B2 * $D2 * $C2

Lần nhân thứ nhất: $B2 * $D2 (giá * số lượng. = Số tiền)

Phép nhân số thứ 2 và thứ 3: $B2*$D2*$C2 (giá * số lượng * VAT% = số VAT)

Phép cộng: Số tiền + Số tiền thuế VAT = tổng số

Công thức hoa hồng: = ($B2 * $D2 + $B2 * $D2 * $C2) * 10%

Để tính khoản hoa hồng 10%, bạn cần nhân tổng cộng với 10%, do đó bạn kèm theo tính toán trước đó trong ngoặc và nhận được kết quả mong muốn.

Tất nhiên, không có gì ngăn cản bạn nhân tổng số đã được tính trong cột E với 10%, trong trường hợp này công thức sẽ thành một phép tính đơn giản = E2 * 10%. Tuy nhiên, trong các bảng tính lớn, nên viết các công thức được tính toán độc lập một cách hợp lý.

Trong công thức Microsoft Excel, lồng hàm bên trong một hàm khác bằng cách sử dụng một hàm như một đối số của một hàm khác. Trong các phiên bản hiện tại của Excel 2016, 2013, 2010 và 2010, bạn có thể sử dụng tối đa 64 hàm lồng nhau. Trong các phiên bản cũ của Excel 2003 và phiên bản thấp hơn, chỉ được phép có 7 hàm.

Các công thức mảng trong Excel là một bước cải tiến. Một công thức mảng Excel đơn có thể thực hiện hàng ngàn phép tính và thay thế hàng trăm công thức thông thường. Nghiên cứu công thức mảng chắc chắn đòi hỏi thời gian và công sức, nhưng xứng đáng.

Vì hướng dẫn này dành cho người mới bắt đầu, tôi sẽ không hăm dọa bạn bởi các định nghĩa các hằng số mảng và các công thức nhiều dòng phức tạp. Tôi sẽ chỉ hiển thị một ví dụ rất đơn giản của một công thức mảng Excel thể hiện những gì nó có thể làm.

Giả sử bạn có 2 cột số, cột A và B. Và bạn muốn biết có bao nhiêu lần cột B lớn hơn hoặc bằng cột A khi giá trị trong cột B lớn hơn 0. Nhiệm vụ này đòi hỏi phải so sánh hai dãy và bạn có thể làm điều này bằng cách sử dụng công thức mảng sau:

Mặc dù Microsoft Excel có hàng trăm hàm được xây dựng, bạn vẫn có thể thấy mình phải đối mặt với một thách thức mà không có hàm Excel phù hợp. Trong trường hợp này, bạn có thể tạo ra hàm đó … hoặc có ai đó tạo ra nó cho bạn 🙂

Các hàm tùy chỉnh này được gọi là Các hàm do người dùng tạo (UDF), và chúng đặc biệt hữu ích cho các tính toán toán hoặc kỹ thuật cao cấp. Giống như macro, các hàm do người dùng tạo được viết bằng VBA (Visual Basic for Applications).

Các tham chiếu ô tuyệt đối, tương đối và hỗn hợp trong các công thức Excel

Có 3 loại tham chiếu ô trong Excel: tuyệt đối ($A$1), tương đối (A1) và hỗn hợp ($A1 hoặc A$1). Cả ba loại trên đề cập đến cùng một ô, và dấu đô la ($) chỉ được sử dụng cho một mục đích – nó nói với Microsoft Excel rằng có thay đổi hay không thay đổi các tham chiếu ô khi công thức được di chuyển hoặc sao chép sang các ô khác.

Tham chiếu ô tuyệt đối ($A$1) – dấu $ trước khi các tọa độ của hàng và cột làm cho tham chiếu tĩnh và cho phép bạn sao chép công thức mà không thay đổi tài liệu tham khảo.

Tham chiếu ô tương đối (A1) – một tham chiếu ô không có dấu hiệu thay đổi dựa trên vị trí tương đối của hàng và cột trong một bảng tính.

Tham chiếu ô hỗn hợp – có thể có 2 loại:

Cột tuyệt đối và hàng tương đối ($A1) – dấu hiệu $ ở phía trước của cột chữ cái khóa tham chiếu đến cột được chỉ định, vì vậy cột không bao giờ thay đổi. Tham chiếu hàng tương đối, không có dấu đô la, thay đổi tùy thuộc vào hàng mà công thức được sao chép.

Cột tương đối và hàng tuyệt đối (A$1) – tham chiếu của hàng được khóa bởi $ không thay đổi, và tham chiếu của cột đó thay đổi.

Các công thức trong Excel là một công cụ đa diện mạnh mẽ và chúng có thể giải quyết rất nhiều công việc trong bảng tính của bạn. Tất nhiên, học các khía cạnh khác nhau của hàm Excel và công thức sẽ tốn thời gian, vì vậy bạn có thể cảm thấy không có đủ thời gian để tìm hiểu tất cả mọi thứ. Vâng, một cách tốt để tìm thêm thời gian là để tiết kiệm một thời gian 🙂

Để chuyển đổi giữa các tham chiếu tuyệt đối, tương đối và hỗn hợp trong một công thức, sử dụng phím F4.

Để chỉnh sửa một công thức, nhấn F2, hoặc nhấp đúp vào một ô, hoặc nhấp vào thanh công thức.

Để gỡ lỗi công thức trong Excel, hãy chọn một phần công thức và nhấn F9. Điều này sẽ cho phép bạn xem các giá trị thực tế đằng sau các tham chiếu ô.

Để sao chép công thức tới tất cả các ô trong một cột, nhập công thức vào ô đầu tiên, chọn ô đó và di con trỏ qua hình vuông nhỏ ở góc dưới cùng bên phải cho đến khi nó chuyển thành chữ thập đỏ đen (được gọi là Fill handle). Nhấp đúp vào chữ thập đó và bạn sẽ nhận được công thức được sao chép qua toàn bộ cột.

Để chuyển đổi các công thức sang các giá trị, hãy chọn tất cả các ô với các công thức mà bạn muốn chuyển đổi, nhấn Ctrl + C để sao chép các công thức đó, sau đó nhấn Shift + F10, sau đó nhấn V, và sau đó nhấn Enter. Shift + F10 + V là phím tắt cho các giá trị đặc biệt Paste của Excel. Nếu bạn không chắc chắn sẽ nhớ lối tắt này, sau đó chỉ cần nhấn phím tắt thông thường để dán Ctrl + V, nhấp vào mũi tên nhỏ ở bên phải của nút Paste để mở danh sách thả xuống và chọn Paste Values.

Để có thể ứng dụng tốt Excel vào trong công việc, chúng ta không chỉ nắm vững được các hàm mà còn phải sử dụng tốt cả các công cụ của Excel. Những hàm nâng cao giúp áp dụng tốt vào công việc như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Những công cụ thường sử dụng là Data validation, Conditional formatting, Pivot table…

Toàn bộ những kiến thức này các bạn đều có thể học được trong khóa học Excel từ cơ bản đến chuyên gia cùng giảng viên Nguyễn Đức Thanh aka DT Nguyen

Công Thức Excel, Ví Dụ Và Mẹo Để Tiết Kiệm Thời Gian

Trong MS Excel, công thức là các phương trình thực hiện các tính toán khác nhau trong các bảng tính. Mặc dù Microsoft giới thiệu thêm nhiều hàm mới qua các năm, khái niệm công thức bảng tính Excel vẫn giống nhau trong tất cả các phiên bản Excel 2016, Excel 2013, Excel 2010, Excel 2007 và thấp hơn.

Tất cả các công thức Excel bắt đầu với một dấu bằng (=).

Sau dấu bằng, bạn nhập hoặc là phép tính hoặc hàm. Ví dụ: để cộng các giá trị trong các ô từ B1 tới B5, bạn có thể:

Gõ toàn bộ phương trình: = B1 + B2 + B3 + B4 + B5

Sử dụng hàm SUM: = SUM (B1: B5)

Nhấn phím Enter để hoàn thành công thức. Làm xong!

Các thành phần của công thức Microsoft Excel

Khi bạn tạo một công thức trong Excel, bạn có thể sử dụng các phần tử khác nhau để cung cấp dữ liệu nguồn cho công thức và chỉ ra các toán tử nên được thực hiện trên những dữ liệu đó. Tùy thuộc vào loại công thức mà bạn tạo ra, nó có thể bao gồm bất kỳ hoặc tất cả các phần sau: ·

Hằng số – số hoặc các giá trị văn bản mà bạn nhập trực tiếp vào một công thức, như = 2 * 3. ·

Tham chiếu ô – tham chiếu đến ô chứa giá trị bạn muốn sử dụng trong công thức Excel của bạn, ví dụ: = SUM (A1, A2, B5). Để tham khảo dữ liệu trong hai hoặc nhiều ô tiếp giáp, hãy sử dụng một tham chiếu phạm vi như A1: A5. Ví dụ, để tổng hợp giá trị trong tất cả các ô giữa A1 và A5, sử dụng công thức này: = SUM (A1: A5).·

Tên – được xác định cho một phạm vi ô, hằng, bảng, hoặc hàm, ví dụ = SUM (my_name). ·

Các hàm – các công thức được xác định trước trong Excel thực hiện tính toán sử dụng các giá trị được cung cấp trong các đối số của chúng. ·

Toán tử – các ký hiệu đặc biệt chỉ định loại hoạt động hoặc tính toán sẽ được thực hiện.

Các toán tử trong các bảng tính Excel

Để nói với Microsoft Excel loại công việc bạn muốn thực hiện trong một công thức, bạn sử dụng các ký hiệu đặc biệt được gọi là các toán tử. Có 4 loại toán tử trong Excel:

Số học – để thực hiện các phép toán cơ bản.

So sánh (hợp lý) – để so sánh các giá trị.

Kết nối – để kết hợp các giá trị văn bản vào một chuỗi duy nhất.

Tham chiếu – để tạo ra các cơn thịnh nộ và các đối số riêng biệt trong các hàm của Excel.

Sử dụng toán tử số học trong công thức Excel

Các toán tử này được sử dụng để thực hiện các phép toán cơ bản như cộng, trừ, nhân và chia.

Ví dụ: nếu bạn có giá cả trong ô A2 và VAT trong ô B2, bạn có thể tính số tiền thuế GTGT bằng cách sử dụng công thức tỷ lệ phần trăm sau: = A2 * B2

Các toán tử so sánh trong các công thức Excel

Trong các công thức Microsoft Excel, so sánh, hoặc hợp lý, toán tử được sử dụng để so sánh hai giá trị. Kết quả của việc so sánh luôn là một giá trị logic của TRUE hoặc FALSE. Các toán tử logic sau có sẵn trong Excel:

Ví dụ, công thức = A1 = B1 trả về TRUE nếu các ô A1 và B1 có cùng một giá trị (số, văn bản hoặc ngày tháng), FALSE nếu không.

Toán tử nối văn bản

Toán tử nối văn bản trong Excel là ký hiệu và (&). Bạn có thể sử dụng nó để kết hợp hai hoặc nhiều chuỗi văn bản trong một chuỗi duy nhất.

Ví dụ: nếu bạn có mã quốc gia trong cột A và số điện thoại trong cột B, bạn có thể sử dụng công thức sau để nhận số điện thoại kết hợp với mã quốc gia:

= A1 & “” & B1

Trong công thức trên, chúng ta thêm một khoảng trống vào giữa để làm cho các con số dễ đọc hơn:

Kết quả tương tự có thể đạt được bằng cách sử dụng chức năng CONCATENATE.

Các toán tử tham chiếu trong các công thức Excel và các hàm

Để cung cấp vùng dữ liệu cho MS Excel công thức và đối số riêng biệt trong hàm Excel, các toán tử sau đây được sử dụng.

Dấu hai chấm (:) – nó là một toán tử phạm vi cho phép bạn tạo một tham chiếu cho nhiều ô nằm giữa 2 ô bạn chỉ định.

Ví dụ, phạm vi A1: A00 bao gồm 100 ô từ A1 đến A100. Để tìm trung bình của 100 ô này, bạn sử dụng công thức sau: = AVERAGE (A1: A00)

Bạn cũng có thể tham khảo toàn bộ cột (A: A) hoặc toàn bộ hàng (1: 1). Ví dụ: công thức sau tìm tổng số tất cả các số trong cột A: = SUM (A: A).

Các loại công thức Excel

Công thức mà bạn tạo trong bảng tính Excel của bạn có thể đơn giản hoặc phức tạp:

Các công thức Excel đơn giản chỉ thực hiện một thao tác toán học, ví dụ = 10 * 5 hoặc = SUM (A1: A10)

Các công thức Excel phức tạp (tiên tiến) bao gồm nhiều tính toán, ví dụ = 10 * 5 + 20 hoặc = SUM (A1: A10) / 2

Hơn nữa trong hướng dẫn này, bạn sẽ tìm thấy các bước chi tiết để tạo cả hai loại công thức bảng tính Excel.

Cách tạo các công thức trong Excel

Làm thế nào để tạo các công thức đơn giản trong Excel

Mặc dù các công thức Excel đơn giản chỉ thực hiện một phép tính, chúng có thể thực hiện theo nhiều cách khác nhau. Bạn có thể cung cấp dữ liệu nguồn dưới dạng hằng số, tham chiếu ô, hoặc các tên được xác định, và thực hiện tính toán bằng cách sử dụng các toán tử toán học hoặc các hàm Excel.

Cách tạo các công thức tiên tiến trong Excel

Khi bạn có một số kinh nghiệm với các công thức Excel đơn giản, bạn có thể muốn thực hiện một số tính toán trong một công thức. Và các ví dụ sau cho thấy cách bạn có thể làm điều này.

Tạo các công thức phức tạp với các hằng số và các toán tử toán học

Đối với một công thức phức tạp để chính xác, một số thao tác nhất định phải được thực hiện trước. Lệnh mặc định hoạt động của các công thức Excel là:

Phép tính trong dấu ngoặc đơn

Lũy thừa

Nhân và chia, tùy điều kiện nào đến trước trong một công thức

Cộng và trừ, tùy điều kiện nào đến trước trong một công thức

Ví dụ: bạn có thể sử dụng các công thức sau để tính tổng số và hoa hồng:

Và bây giờ, hãy phân tích các công thức này để xem cách Microsoft Excel tính toán chúng như thế nào:

Công thức: = $B2*$D2 + $B2 * $D2 * $C2

Lần nhân thứ nhất: $B2 * $D2 (giá * số lượng. = Số tiền)

Phép nhân số thứ 2 và thứ 3: $B2*$D2*$C2 (giá * số lượng * VAT% = số VAT)

Phép cộng: Số tiền + Số tiền thuế VAT = tổng số

Công thức hoa hồng: = ($B2 * $D2 + $B2 * $D2 * $C2) * 10%

Để tính khoản hoa hồng 10%, bạn cần nhân tổng cộng với 10%, do đó bạn kèm theo tính toán trước đó trong ngoặc và nhận được kết quả mong muốn.

Tất nhiên, không có gì ngăn cản bạn nhân tổng số đã được tính trong cột E với 10%, trong trường hợp này công thức sẽ thành một phép tính đơn giản = E2 * 10%. Tuy nhiên, trong các bảng tính lớn, nên viết các công thức được tính toán độc lập một cách hợp lý.

Công thức Excel với hàm lồng nhau

Trong công thức Microsoft Excel, lồng hàm bên trong một hàm khác bằng cách sử dụng một hàm như một đối số của một hàm khác. Trong các phiên bản hiện tại của Excel 2016, 2013, 2010 và 2010, bạn có thể sử dụng tối đa 64 hàm lồng nhau. Trong các phiên bản cũ của Excel 2003 và phiên bản thấp hơn, chỉ được phép có 7 hàm.

Trong tất cả các hàm Excel, hàm IF lồng thường xuyên hơn tất cả các hàm khác. Như bạn có thể biết, hàm IF được sử dụng để đánh giá một điều kiện cụ thể và trả về một giá trị khi điều kiện được đáp ứng và một giá trị khác khi điều kiện không được đáp ứng. Tuy nhiên, thực tế bạn thường phải đối phó với các tình huống có nhiều hơn hai kết quả có thể xảy ra. Và nếu trường hợp này, bạn có thể viết một số hàm IF và lồng nó vào nhau:

Công thức mảng trong Excel

Các công thức mảng trong Excel là một bước cải tiến. Một công thức mảng Excel đơn có thể thực hiện hàng ngàn phép tính và thay thế hàng trăm công thức thông thường. Nghiên cứu công thức mảng chắc chắn đòi hỏi thời gian và công sức, nhưng xứng đáng.

Vì hướng dẫn này dành cho người mới bắt đầu, tôi sẽ không hăm dọa bạn bởi các định nghĩa các hằng số mảng và các công thức nhiều dòng phức tạp. Tôi sẽ chỉ hiển thị một ví dụ rất đơn giản của một công thức mảng Excel thể hiện những gì nó có thể làm.

Giả sử bạn có 2 cột số, cột A và B. Và bạn muốn biết có bao nhiêu lần cột B lớn hơn hoặc bằng cột A khi giá trị trong cột B lớn hơn 0. Nhiệm vụ này đòi hỏi phải so sánh hai dãy và bạn có thể làm điều này bằng cách sử dụng công thức mảng sau:

Chú thích. Để nhập công thức mảng Excel một cách chính xác, bạn phải nhấn Ctrl + Shift + Enter thay vì gõ Enter thông thường.

Hàm người dùng tự tạo

Mặc dù Microsoft Excel có hàng trăm hàm được xây dựng, bạn vẫn có thể thấy mình phải đối mặt với một thách thức mà không có hàm Excel phù hợp. Trong trường hợp này, bạn có thể tạo ra hàm đó … hoặc có ai đó tạo ra nó cho bạn 🙂

Các hàm tùy chỉnh này được gọi là Các hàm do người dùng tạo (UDF), và chúng đặc biệt hữu ích cho các tính toán toán hoặc kỹ thuật cao cấp. Giống như macro, các hàm do người dùng tạo được viết bằng VBA (Visual Basic for Applications).

Các tham chiếu ô tuyệt đối, tương đối và hỗn hợp trong các công thức Excel

Có 3 loại tham chiếu ô trong Excel: tuyệt đối ($A$1), tương đối (A1) và hỗn hợp ($A1 hoặc A$1). Cả ba loại trên đề cập đến cùng một ô, và dấu đô la ($) chỉ được sử dụng cho một mục đích – nó nói với Microsoft Excel rằng có thay đổi hay không thay đổi các tham chiếu ô khi công thức được di chuyển hoặc sao chép sang các ô khác.

Tham chiếu ô tuyệt đối ($A$1) – dấu $ trước khi các tọa độ của hàng và cột làm cho tham chiếu tĩnh và cho phép bạn sao chép công thức mà không thay đổi tài liệu tham khảo.

Tham chiếu ô tương đối (A1) – một tham chiếu ô không có dấu hiệu thay đổi dựa trên vị trí tương đối của hàng và cột trong một bảng tính.

Tham chiếu ô hỗn hợp – có thể có 2 loại:

Cột tuyệt đối và hàng tương đối ($A1) – dấu hiệu $ ở phía trước của cột chữ cái khóa tham chiếu đến cột được chỉ định, vì vậy cột không bao giờ thay đổi. Tham chiếu hàng tương đối, không có dấu đô la, thay đổi tùy thuộc vào hàng mà công thức được sao chép.

Cột tương đối và hàng tuyệt đối (A$1) – tham chiếu của hàng được khóa bởi $ không thay đổi, và tham chiếu của cột đó thay đổi.

Mẹo và các phím tắt tiết kiệm thời gian cho các công thức Excel

Các công thức trong Excel là một công cụ đa diện mạnh mẽ và chúng có thể giải quyết rất nhiều công việc trong bảng tính của bạn. Tất nhiên, học các khía cạnh khác nhau của hàm Excel và công thức sẽ tốn thời gian, vì vậy bạn có thể cảm thấy không có đủ thời gian để tìm hiểu tất cả mọi thứ. Vâng, một cách tốt để tìm thêm thời gian là để tiết kiệm một thời gian 🙂

Để chuyển đổi giữa các tham chiếu tuyệt đối, tương đối và hỗn hợp trong một công thức, sử dụng phím F4.

Để chỉnh sửa một công thức, nhấn F2, hoặc nhấp đúp vào một ô, hoặc nhấp vào thanh công thức.

Để gỡ lỗi công thức trong Excel, hãy chọn một phần công thức và nhấn F9. Điều này sẽ cho phép bạn xem các giá trị thực tế đằng sau các tham chiếu ô.

Để sao chép công thức tới tất cả các ô trong một cột, nhập công thức vào ô đầu tiên, chọn ô đó và di con trỏ qua hình vuông nhỏ ở góc dưới cùng bên phải cho đến khi nó chuyển thành chữ thập đỏ đen (được gọi là Fill handle). Nhấp đúp vào chữ thập đó và bạn sẽ nhận được công thức được sao chép qua toàn bộ cột.

Để chuyển đổi các công thức sang các giá trị, hãy chọn tất cả các ô với các công thức mà bạn muốn chuyển đổi, nhấn Ctrl + C để sao chép các công thức đó, sau đó nhấn Shift + F10, sau đó nhấn V, và sau đó nhấn Enter. Shift + F10 + V là phím tắt cho các giá trị đặc biệt Paste của Excel. Nếu bạn không chắc chắn sẽ nhớ lối tắt này, sau đó chỉ cần nhấn phím tắt thông thường để dán Ctrl + V, nhấp vào mũi tên nhỏ ở bên phải của nút Paste để mở danh sách thả xuống và chọn Paste Values.

Để có thể ứng dụng tốt Excel vào trong công việc, chúng ta không chỉ nắm vững được các hàm mà còn phải sử dụng tốt cả các công cụ của Excel. Những hàm nâng cao giúp áp dụng tốt vào công việc như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Những công cụ thường sử dụng là Data validation, Conditional formatting, Pivot table…

Toàn bộ những kiến thức này các bạn đều có thể học được trong khóa học Excel từ cơ bản đến chuyên gia cùng giảng viên Nguyễn Đức Thanh hay DT Nguyen

Các Công Thức Và Hàm Excel Cơ Bản Với Các Ví Dụ

Lượt Xem:8230

Các công thức và hàm Excel cơ bản với các ví dụ

Hướng dẫn này nhằm mục đích dạy cho bạn các yếu tố cần thiết của các hàm Excel và hiển thị cách sử dụng các công thức cơ bản trong Excel.

Khái niệm cơ bản về công thức Excel

Trước khi cung cấp danh sách công thức Excel cơ bản, hãy xác định các thuật ngữ chính chỉ để đảm bảo chúng ta đang ở trên cùng một trang. Vậy, chúng ta gọi công thức Excel và hàm Excel là gì?

Công thức là biểu thức tính toán giá trị của ô.

Ví dụ, =A2+A2+A3+A4là một công thức cho biết thêm các giá trị trong các ô từ A2 đến A4.

Hàm là một công thức được xác định trước đã có sẵn trong Excel. Các hàm thực hiện các phép tính cụ thể theo một thứ tự cụ thể dựa trên các giá trị được chỉ định, được gọi là các đối số hoặc các tham số.

Ví dụ: thay vì chỉ định từng giá trị được tổng hợp như trong công thức trên, bạn có thể sử dụng hàm SUM để thêm một dải ô: =SUM(A2:A4)

Bạn có thể tìm tất cả các hàm Excel có sẵn trong Thư viện hàm trên tab Công thức

Có tồn tại hơn 400 hàm trong Excel và số đang tăng theo phiên bản lên phiên bản. Tất nhiên, đó là bên cạnh không thể ghi nhớ tất cả chúng, và bạn thực sự không cần phải. Trình hướng dẫn chức năng sẽ giúp bạn tìm thấy hàm phù hợp nhất cho một nhiệm vụ cụ thể, trong khi Công thức Excel Intellisense sẽ nhắc cú pháp và đối số của hàm ngay sau khi bạn nhập tên của hàm trước bởi một dấu bằng trong ô:

Tiền boa. Bạn không nhất thiết phải gõ tên hàm trong tất cả các mũ, Microsoft Excel sẽ tự động viết hoa nó sau khi bạn nhập công thức và nhấn phím Enter để hoàn thành nó.

10 chức năng cơ bản của Excel bạn chắc chắn phải biết

SUM

Hàm Excel đầu tiên bạn cần làm quen thuộc là hàm thực hiện phép tính số học cơ bản của phép cộng:

SUM ( number1 , [number2], …)

Trong cú pháp của tất cả các hàm Excel, một đối số được đính kèm trong [dấu ngoặc vuông] là tùy chọn, các đối số khác được yêu cầu. Có nghĩa là, công thức Tổng của bạn nên bao gồm ít nhất 1 số, tham chiếu đến một ô hoặc một dải ô. Ví dụ:

=SUM(A2:A6) – thêm các giá trị trong các ô từ A2 đến A6.

=SUM(A2, A6) – thêm các giá trị vào các ô A2 và A6.

=SUM(A2:A6)/5 – thêm các giá trị trong các ô từ A2 đến A6, sau đó chia tổng cho 5.

Tiền boa. Cách nhanh nhất để tổng hợp một cột hoặc hàng số là chọn một ô bên cạnh các số bạn muốn tính tổng (ô ngay bên dưới giá trị cuối cùng trong cột hoặc bên phải của số cuối cùng trong hàng) và bấm các AutoSum nút trên Trang chủ tab, trong Biên tập nhóm. Excel sẽ tự động chèn một công thức SUM cho bạn.

Ví dụ về công thức Excel Sum – công thức tính tổng cột, hàng, chỉ các ô được lọc (hiển thị) hoặc tổng trên các trang tính.

Excel AutoSum – cách nhanh nhất để tổng hợp một cột hoặc một dãy số.

SUMIF trong Excel – ví dụ công thức để tổng hợp các ô có điều kiện.

Hàm SUMIFS trong Excel – ví dụ công thức để tổng hợp các ô dựa trên nhiều tiêu chí.

TRUNG BÌNH CỘNG

Hàm AVERAGE của Excel thực hiện chính xác tên của nó, nghĩa là tìm số trung bình hoặc số học của các số. Cú pháp của nó tương tự như cú pháp của SUM:

AVERAGE (số 1, [số2],…)

Xem xét kỹ hơn công thức cuối cùng từ phần trước ( =SUM(A2:A6)/5), nó thực sự làm gì? Tổng các giá trị trong các ô từ A2 đến A6 và sau đó chia kết quả cho 5. Và bạn gọi thêm một nhóm các số và sau đó chia tổng số cho các số đó? Yep, trung bình!

Vì vậy, thay vì nhập =SUM(A2:A6)/5, bạn có thể chỉ cần đặt=AVERAGE(A2:A6)

LỚN NHẤT NHỎ NHẤT

Công thức MAX và MIN trong Excel nhận được giá trị lớn nhất và nhỏ nhất trong một tập các số, tương ứng. Đối với tập dữ liệu mẫu của chúng tôi, các công thức sẽ đơn giản như sau:

IF (logical_test, [value_if_true], [value_if_false])

Ví dụ, câu lệnh IF sau đây chỉ thị cho Excel kiểm tra giá trị trong A2 và trả về “OK” nếu nó lớn hơn hoặc bằng 3, “Không OK” nếu nó nhỏ hơn 3:

TRIM

Nếu công thức Excel chính xác của bạn chỉ trả về một loạt lỗi, một trong những điều đầu tiên cần kiểm tra là khoảng trống trong ô được tham chiếu trong công thức của bạn (Bạn có thể ngạc nhiên khi biết có bao nhiêu hàng đầu, dấu và ở giữa không được chú ý trong tờ của bạn chỉ cho đến khi có điều gì đó sai!).

Có một số cách để loại bỏ không gian không mong muốn trong Excel, với hàm TRIM là hàm dễ nhất:

Ví dụ: để cắt thêm khoảng trắng trong cột A, hãy nhập công thức sau vào ô A1 và sau đó sao chép nó xuống cột:

Nó sẽ loại bỏ tất cả các khoảng trống thừa trong các ô nhưng một ký tự khoảng trắng giữa các từ:

LEN

Bất cứ khi nào bạn muốn biết số ký tự trong một ô nào đó, LEN là hàm để sử dụng:

Cần tìm ra số lượng ký tự trong ô A2? Chỉ cần gõ =LEN(A2)vào ô khác.

Xin lưu ý rằng hàm Excel LEN đếm hoàn toàn tất cả các ký tự bao gồm cả dấu cách :

Bạn muốn nhận tổng số ký tự trong một phạm vi hoặc ô hoặc chỉ đếm các ký tự cụ thể? Vui lòng xem các tài nguyên sau.

VÀ VÀ HOẶC

Đây là hai hàm logic phổ biến nhất để kiểm tra nhiều tiêu chí. Sự khác biệt là cách họ làm điều này:

VÀ trả về TRUE nếu tất cả các điều kiện được đáp ứng, FALSE nếu không.

HOẶC trả về TRUE nếu có bất kỳ điều kiện nào được đáp ứng, FALSE nếu không.

Trong khi hiếm khi được sử dụng trên riêng của họ, các chức năng này rất tiện dụng như là một phần của công thức lớn hơn.

Ví dụ: để kiểm tra số lượng trong 2 cột và trả về “Tốt” nếu cả hai giá trị lớn hơn 0, bạn sử dụng công thức IF sau đây với câu lệnh AND được nhúng:

Nếu bạn hài lòng với chỉ một giá trị lớn hơn 0 (A2 hoặc B2), thì hãy sử dụng câu lệnh OR:

CONCATENATE

Trong trường hợp bạn muốn lấy giá trị từ hai hoặc nhiều ô và kết hợp chúng thành một ô, hãy sử dụng toán tử nối (&) hoặc hàm CONCATENATE:

CONCATENATE (text1, [text2],…)

Ví dụ: để kết hợp các giá trị từ các ô A2 và B2, chỉ cần nhập công thức sau vào ô khác:

Để tách các giá trị được kết hợp với một dấu cách, hãy nhập ký tự dấu cách (“”) vào danh sách đối số:

=CONCATENATE(A2, ” “, B2)

TODAY & NGAY

Để xem ngày và giờ hiện tại bất cứ khi nào bạn mở trang tính của mình mà không phải cập nhật hàng ngày theo cách thủ công, hãy sử dụng:

=TODAY() để chèn ngày hôm nay vào ô.

=NOW() để chèn ngày giờ hiện tại vào ô.

Vẻ đẹp của các chức năng này là chúng không yêu cầu bất kỳ đối số nào cả, bạn nhập các công thức chính xác như được viết ở trên.

Sử dụng các hàm TODAY và NOW trong Excel

Cách chèn ngày hôm nay trong Excel – các cách khác nhau để nhập ngày giờ hiện tại trong Excel: dưới dạng dấu thời gian không thể thay đổi hoặc ngày và thời gian có thể cập nhật tự động.

Các hàm ngày tháng Excel với các ví dụ công thức – các công thức để chuyển đổi ngày thành văn bản và ngược lại, trích xuất một ngày, tháng hoặc năm từ một ngày, tính toán sự khác biệt giữa hai ngày và nhiều hơn nữa.

Mẹo công thức Excel và cách thực hiện

Bây giờ bạn đã quen thuộc với các công thức Excel cơ bản, các mẹo này sẽ cung cấp cho bạn một số hướng dẫn về cách sử dụng chúng hiệu quả nhất và tránh các lỗi công thức phổ biến.

Sao chép cùng một công thức vào các ô khác thay vì nhập lại nó

Một khi bạn đã gõ một công thức vào một ô, không cần phải gõ lại nó nhiều lần. Đơn giản chỉ cần sao chép công thức vào các ô liền kề bằng cách kéo tay cầm điền (một hình vuông nhỏ ở góc dưới bên phải của ô). Để sao chép công thức vào toàn bộ cột, hãy đặt con trỏ chuột vào thanh công cụ điền và nhấp đúp vào dấu cộng.

Chú thích. Sau khi sao chép công thức, hãy đảm bảo rằng tất cả các tham chiếu ô đều chính xác. Tham chiếu ô có thể thay đổi tùy thuộc vào việc chúng là tuyệt đối (không thay đổi) hoặc tương đối (thay đổi).

Để biết hướng dẫn từng bước chi tiết, vui lòng xem Cách sao chép công thức trong Excel .

Cách xóa công thức, nhưng giữ giá trị được tính

Chọn tất cả các ô có công thức của bạn.

Nhấn Ctrl + C để sao chép các ô đã chọn.

Để biết các bước chi tiết với ảnh chụp màn hình, vui lòng xem Cách thay thế công thức bằng các giá trị của chúng trong Excel .

Bao gồm các giá trị văn bản trong dấu ngoặc kép, nhưng không phải là số

Bất kỳ văn bản nào có trong công thức Excel của bạn đều phải được đính kèm trong “dấu ngoặc kép”. Tuy nhiên, bạn không bao giờ nên làm điều đó với các con số, trừ khi bạn muốn Excel coi chúng là các giá trị văn bản.

Ví dụ: để kiểm tra giá trị trong ô B2 và trả về 1 cho “Đã qua”, 0 nếu không, bạn đặt công thức sau, như trong C2:

Sao chép công thức xuống các ô khác và bạn sẽ có cột 1 và 0 có thể được tính mà không bị cản trở.

Bây giờ, hãy xem điều gì sẽ xảy ra nếu bạn báo giá gấp đôi các con số:

Ngay từ cái nhìn đầu tiên, đầu ra là bình thường – cùng một cột của 1 và 0. Tuy nhiên, khi xem xét kỹ hơn, bạn sẽ nhận thấy rằng các giá trị kết quả được căn chỉnh trái trong các ô theo mặc định, có nghĩa là các giá trị này là các chuỗi văn bản, chứ không phải các số! Nếu sau này ai đó sẽ cố gắng tính toán số 1 và 0, họ có thể sẽ kéo tóc ra để cố gắng tìm ra lý do tại sao công thức Sum hoặc Count chính xác 100% không trả về gì ngoài 0.

Không định dạng số trong công thức Excel

Hãy nhớ quy tắc đơn giản này: các số được cung cấp cho công thức Excel của bạn phải được nhập mà không có bất kỳ định dạng nào như dấu tách thập phân hoặc ký hiệu đô la. Ở Bắc Mỹ và một số quốc gia khác, dấu phẩy là dấu tách đối số mặc định và ký hiệu đô la ($) được sử dụng để tạo tham chiếu ô tuyệt đối. Sử dụng các ký tự đó trong số có thể chỉ làm cho Excel của bạn bị điên 🙂 Vì vậy, thay vì gõ $ 2,000, chỉ cần gõ 2000, và sau đó định dạng giá trị đầu ra theo ý thích của bạn bằng cách thiết lập một định dạng số Excel tùy chỉnh .

Khớp tất cả các dấu ngoặc mở và đóng trong công thức của bạn

Khi kết hợp một công thức Excel phức tạp với một hoặc nhiều hàm lồng nhau, bạn sẽ phải sử dụng nhiều hơn một bộ ngoặc đơn để xác định thứ tự các phép tính. Trong các công thức như vậy, hãy chắc chắn ghép các dấu ngoặc đơn đúng cách để có một dấu đóng ngoặc cho mỗi dấu ngoặc đơn mở. Để làm cho công việc dễ dàng hơn cho bạn, Excel làm tròn cặp ngoặc đơn bằng các màu khác nhau khi bạn nhập hoặc chỉnh sửa công thức.

Đảm bảo Tùy chọn tính toán được đặt thành Tự động

Nếu điều này không giúp được bạn, hãy xem các bước khắc phục sự cố sau: Công thức Excel không hoạt động: các bản sửa lỗi và giải pháp .

Các bài viết mới

Không có tin nào

Các tin cũ hơn

Hàm Lookup Và Công Thức Ví Dụ Trong Excel 2022 2013 2010 2007 2003

Với bài viết này, Học Excel Online sẽ hướng dẫn các bạn cách sử dụng hàm LOOKUP trong Excel, cú pháp của hàm LOOKUP cùng với các ví dụ của hàm LOOKUP từ cơ bản đến nâng cao trong Excel

Hàm LookUp trong Excel – cú pháp và cách sử dụng

Cách sử dụng đơn giản nhất của hàm Lookup trong Excel là tìm 1 giá trị trong 1 cột hoặc 1 hàng và trả về giá trị ở vị trí tương ứng trong hàng hoặc cột chứa giá trị cần trả về. Có 2 kiểu sử dụng hàm Lookup đó là kiểu Vector và kiểu mảng

Sử dụng hàm Lookup trong Excel – kiểu Vector

Khi sử dụng hàm Lookup, Vector được hiểu là 1 hàng hay 1 cột trong Excel. Cú pháp của hàm Lookup đơn giản như sau:

[ vector chứa kết quả trả về ] (không bắt buộc): 1 dòng hoặc 1 cột chứa giá trị trả về. Vector này cần có kích thước giống như kích thước của vector chứa giá trị cần tìm kiếm, nếu không có vector này, thì vector chứa giá trị cần tìm kiếm sẽ được dùng để thay thế.

2 Ví dụ sau đây sẽ giúp bạn hiểu rõ hơn về cú pháp phía trên của hàm Lookup trong Excel

Tra cứu dữ liệu theo cột với hàm LOOKUP trong Excel

=LOOKUP(B2,D2:D5,E2:E5)

Để hiểu rõ hơn, bạn có thể xem hình sau đây:

Tra cứu dữ liệu theo hàng với hàm LOOKUP trong Excel

Lưu ý khi tra cứu sử dụng hàm Lookup trong Excel

Vector chứa giá trị cần tra cứu cần được sắp xếp theo thứ tự từ điển (A đến Z) hoặc sắp xếp từ nhỏ tới lớn, nếu không công thức Lookup sẽ bị lỗi hoặc kết quả sẽ bị sai. Nếu trong trường hợp nào đó bạn không thể sắp xếp dữ liệu mà vẫn cần tra cứu, hãy sử dụng hàm Index kết hợp với Match

Vector chứa giá trị cần tra cứu và vector kết quả là hàng hoặc cột có kích thước giống nhau

Hàm Lookup trong Excel không phân biệt chữ hoa và chữ thường khi tra cứu

Hàm Lookup sử dụng chế độ tìm kiếm gần đúng, nếu không tìm thấy giá trị đúng 100% như giá trị đang cần tìm kiếm, nó sẽ tìm đến giá trị X lớn nhất trong vector chứa giá trị tìm kiếm với điều kiện X nhỏ hơn hoặc bằng giá trị cần tìm kiếm. Ví dụ: Nếu bạn tìm giá trị 5 mà 5 không được tìm thấy, Lookup sẽ tìm tiếp giá trị 4, nếu 4 không được tìm thấy, Lookup sẽ tìm kiếm các giá trị nhỏ hơn tiếp theo.

Nếu giá trị tìm kiếm nhỏ hơn giá trị nhỏ nhất trong vector chứa giá trị tìm kiếm, hàm Lookup sẽ trả về lỗi #N/A

Sử dụng hàm Lookup trong Excel – kiểu mảng

Hàm Lookup theo kiểu mảng sẽ tìm kiếm giá trị ở cột đầu tiên trong mảng và trả về giá trị ở vị trí tương ứng trong cột cuối cùng của mảng đó. Cú pháp:

Lưu ý:

Nếu mảng dùng để tra cứu có số dòng lớn hơn hoặc bằng số cột (như ảnh phía trên) thì hàm Lookup sẽ tra cứu theo cột

Nếu mảng dùng để tra cứu có số dòng nhỏ hơn số cột, thì hàm Lookup sẽ tra cứu theo dòng

Nếu giá trị cần tìm không được tìm thấy, Lookup sẽ dùng giá trị lớn nhất trong mảng mà nhỏ hơn hoặc bằng giá trị cần tìm

Nếu giá trị cần tìm nhỏ hơn giá trị nhỏ nhất trong hàng đầu tiên hoặc cột đầu tiên của mảng, lỗi #N/A sẽ được trả về

Học Excel Online không khuyên dùng hàm Lookup ở dạng mảng này. Bạn hãy sử dụng hàm VLOOKUP hoặc HLOOKUP

Tìm hiểu ngay: hướng dẫn học excel 2010

Ứng dụng của hàm LOOKUP trong Excel:

Tìm giá trị trong ô cuối cùng chứa dữ liệu của 1 cột

Nếu dữ liệu của bạn thay đổi thường xuyên và bạn cần tham chiếu tới giá trị cuối cùng trong 1 cột ở bảng dữ liệu đó, đây là trường hợp mà hàm LOOKUP dạng vector sẽ toả sáng. Ví dụ: Tìm giá trị trong ô cuối cùng có chứa dữ liệu ở cột A:

Diễn giải công thức:

Cũng với logic như trên, công thức để tìm kiếm giá trị ô cuối cùng chưa dữ liệu trong 1 hàng như sau:

Tháng có sales gần nhất của nhân viên sales

Với những kiến thức đã trình bày ở trên về Lookup, chúng ta có thể dễ dàng giải quyết được câu hỏi bằng công thức sau đây:

=LOOKUP(2,1/(B2:G2=”x”),$B$1:$G$1)

Thay thế hàm IF lồng nhau trong Excel

Một ứng dụng thú vị khác nữa của hàm LOOKUP đó chính là việc thay thế hàm IF lồng nhau trong trường hợp sau đây

Để có thể ứng dụng tốt Excel vào trong công việc, chúng ta không chỉ nắm vững được các hàm mà còn phải sử dụng tốt cả các công cụ của Excel. Những hàm nâng cao giúp áp dụng tốt vào công việc như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Những công cụ thường sử dụng là Data validation, Conditional formatting, Pivot table…

Toàn bộ những kiến thức này các bạn đều có thể học được trong khóa học

Hàm Ppmt Của Excel Với Các Ví Dụ Về Công Thức

Hướng dẫn cho thấy cách sử dụng hàm PPMT trong Excel để tính toán khoản thanh toán trên tiền gốc cho khoản vay hoặc đầu tư.

Khi bạn thực hiện thanh toán định kỳ cho khoản vay hoặc thế chấp, một phần nhất định của mỗi khoản thanh toán sẽ được tính theo lãi suất (phí phải trả khi vay) và phần còn lại của khoản thanh toán sẽ trả hết tiền gốc (số tiền bạn đã vay ban đầu). Mặc dù tổng số tiền thanh toán là không đổi cho tất cả các giai đoạn, phần gốc và phần lãi là khác nhau – với mỗi khoản thanh toán thành công ít hơn được áp dụng cho tiền lãi và nhiều hơn cho tiền gốc.

Microsoft Excel có các chức năng đặc biệt để tìm cả tổng số tiền thanh toán và các bộ phận của nó. Trong hướng dẫn này, chúng tôi sẽ xem xét cách sử dụng hàm PPMT để tính toán khoản thanh toán trên tiền gốc.

Hàm PPMT của Excel – cú pháp và cách sử dụng cơ bản

Hàm PPMT trong Excel tính toán phần chính của khoản thanh toán cho vay trong một khoảng thời gian nhất định dựa trên lãi suất không đổi và lịch thanh toán.

Cú pháp của hàm PPMT như sau:

PPMT (tỷ lệ, mỗi, nper, pv, [fv], [type])

Ở đâu:

Tỷ lệ (bắt buộc) – lãi suất không đổi cho khoản vay. Có thể được cung cấp dưới dạng phần trăm hoặc số thập phân.Ví dụ: nếu bạn thực hiện hàng năm thanh toán cho khoản vay hoặc đầu tư với lãi suất hàng năm là 7 phần trăm, cung cấp 7% hoặc 0,07. Nếu bạn thực hiện hàng tháng thanh toán cho cùng một khoản vay, sau đó cung cấp 7% / 12.

Mỗi (bắt buộc) – thời hạn thanh toán mục tiêu. Nó phải là một số nguyên giữa 1 và nper.

Thần (bắt buộc) – tổng số thanh toán cho khoản vay hoặc đầu tư.

Pv (bắt buộc) – giá trị hiện tại, tức là một loạt các khoản thanh toán trong tương lai có giá trị bao nhiêu. Giá trị hiện tại của khoản vay là số tiền bạn đã vay ban đầu.

Fv (tùy chọn) – giá trị tương lai, tức là số dư bạn muốn có sau lần thanh toán cuối cùng được thực hiện. Nếu bỏ qua, nó được coi là 0 (0).

Kiểu (tùy chọn) – cho biết khi đến hạn thanh toán:

0 hoặc bị bỏ qua – các khoản thanh toán đáo hạn vào cuối mỗi kỳ.

1 – các khoản thanh toán đến hạn vào đầu mỗi kỳ.

Ví dụ: nếu bạn vay 50.000 đô la trong 3 năm với lãi suất hàng năm là 8% và bạn thực hiện hàng năm thanh toán, công thức sau đây sẽ tính phần chính của khoản thanh toán khoản vay cho giai đoạn 1:

=PPMT(8%, 1, 3, 50000)

Nếu bạn sẽ làm hàng tháng thanh toán cho cùng một khoản vay, sau đó sử dụng công thức này:

=PPMT(8%/12, 1, 3*12, 50000)

Thay vì mã hóa các đối số trong công thức, bạn có thể nhập chúng vào các ô được xác định trước và tham khảo các ô như trong ảnh chụp màn hình này:

Nếu bạn muốn có kết quả như một số dương, sau đó đặt dấu trừ trước toàn bộ công thức PPMT hoặc pv đối số (số tiền cho vay). Ví dụ:

=-PPMT(8%, 1, 3, 50000)

hoặc là

=PPMT(8%, 1, 3, -50000)

3 điều bạn nên biết về hàm PPMT của Excel

Để sử dụng thành công các công thức PPMT trong trang tính của bạn, vui lòng ghi nhớ các sự kiện sau:

Hiệu trưởng được trả lại như một tiêu cực số bởi vì nó là một khoản thanh toán đi.

Theo mặc định, Tiền tệ định dạng được áp dụng cho kết quả, với các số âm được tô sáng màu đỏ và được đặt trong ngoặc đơn.

Khi tính toán số tiền gốc cho các tần số thanh toán khác nhau, hãy chắc chắn rằng bạn phù hợp với các đối số tỷ lệ và nper. Dành cho tỷ lệ, chia lãi suất hàng năm cho số lần thanh toán mỗi năm (giả sử nó bằng với số kỳ hạn gộp mỗi năm). Dành cho nper, nhân số năm với số lần thanh toán mỗi năm.

tuần: lãi suất – lãi suất hàng năm / 52; nper – năm * 52

tháng: lãi suất – lãi suất hàng năm / 12; nper – năm * 12

khu phố: lãi suất – lãi suất hàng năm / 4; nper – năm * 4

Ví dụ về sử dụng công thức PPMT trong Excel

Và bây giờ, hãy lấy một vài ví dụ về công thức chỉ ra cách sử dụng hàm PPMT trong Excel.

Ví dụ 1. Dạng ngắn của công thức PPMT

Giả sử, bạn muốn tính toán các khoản thanh toán trên tiền gốc cho khoản vay. Trong ví dụ này, đó sẽ là 12 khoản thanh toán hàng tháng, nhưng cùng một công thức sẽ hoạt động cho các tần suất thanh toán khác cũng như hàng tuần, hàng quý, nửa năm hoặc hàng năm.

Để tiết kiệm cho bạn những rắc rối khi viết một công thức khác nhau cho từng giai đoạn, hãy nhập số giai đoạn trong một số ô, giả sử A7: A18 và thiết lập các ô nhập sau:

B1 – lãi suất hàng năm

B2 – thời hạn cho vay (tính bằng năm)

B3 – số lượng thanh toán mỗi năm

B4 – số tiền vay

Dựa trên các ô nhập liệu, xác định các đối số cho công thức PPMT của bạn:

Tỷ lệ – lãi suất hàng năm / số lượng thanh toán mỗi năm ($ B $ 1 / $ B $ 3).

Mỗi – kỳ thanh toán đầu tiên (A7).

Thần – năm * số lượng thanh toán mỗi năm ($ B $ 2 * $ B $ 3).

Pv – số tiền cho vay ($ B $ 4)

Fv – bỏ qua, giả sử số dư bằng không sau lần thanh toán cuối cùng.

Kiểu – bỏ qua, giả sử các khoản thanh toán đến hạn tại kết thúc của từng thời kỳ.

Bây giờ, đặt tất cả các đối số lại với nhau và bạn sẽ nhận được công thức sau:

Hãy chú ý, chúng tôi sử dụng tài liệu tham khảo tế bào tuyệt đối trong tất cả các đối số ngoại trừ mỗi nơi một tham chiếu ô tương đối (A7) được sử dụng. Điều này là do tỷ lệ, nper và pv các đối số tham chiếu đến các ô đầu vào và sẽ không đổi cho dù công thức được sao chép ở đâu. Các mỗi đối số sẽ thay đổi dựa trên vị trí tương đối của một hàng.

=PPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4)

Nhập công thức trên vào C7, sau đó kéo nó xuống càng nhiều ô nếu cần và bạn sẽ nhận được kết quả sau:

Như bạn có thể thấy trong ảnh chụp màn hình ở trên, tổng thanh toán (được tính bằng Chức năng PMT) là giống nhau cho tất cả các giai đoạn trong khi phần gốc tăng theo từng giai đoạn liên tiếp vì ban đầu lãi nhiều hơn tiền gốc được trả.

Để xác minh kết quả của chức năng PPMT, bạn có thể thêm tất cả các khoản thanh toán chính bằng cách sử dụng Hàm SUM và xem số tiền bằng với số tiền vay ban đầu, là 20.000 đô la trong trường hợp của chúng tôi.

Ví dụ 2. Dạng đầy đủ của công thức PPMT

Trong ví dụ này, chúng tôi sẽ sử dụng hàm PPMT để tính toán các khoản thanh toán trên số tiền gốc cần có để tăng khoản đầu tư từ $ 0 đến số tiền bạn chỉ định.

Vì chúng ta sẽ sử dụng dạng đầy đủ của hàm PPMT, chúng tôi xác định thêm các ô nhập liệu:

B1 – lãi suất hàng năm

B2 – thời hạn đầu tư tính bằng năm

B3 – số lượng thanh toán mỗi năm

B4 – giá trị hiện tại (pv)

B5 – giá trị tương lai (fv)

B6 – khi đến hạn thanh toán (kiểu)

Như ví dụ trước, cho tỷ lệ, chúng tôi chia lãi suất hàng năm cho số lần thanh toán mỗi năm ($ B $ 1 / $ B $ 3). Dành cho nper, chúng tôi nhân số năm với số lần thanh toán mỗi năm ($ B $ 2 * $ B $ 3).

Với số kỳ thanh toán đầu tiên trong A10, công thức có dạng như sau:

Trong ví dụ này, các khoản thanh toán được thực hiện vào cuối mỗi quý trong khoảng thời gian 2 năm. Xin lưu ý rằng tổng của tất cả các khoản thanh toán gốc bằng với giá trị tương lai của khoản đầu tư:

=PPMT($B$1/$B$3, A10, $B$2*$B$3, $B$4, $B$5, $B$7)

Hàm PPMT của Excel không hoạt động

Nếu công thức PPMT không hoạt động chính xác trong bảng tính của bạn, các mẹo khắc phục sự cố này có thể giúp:

Các mỗi đối số phải lớn hơn 0 nhưng nhỏ hơn hoặc bằng nper, nếu không thì là #NUM! lỗi xảy ra.

Tất cả các đối số phải là số, nếu không thì là #VALUE! lỗi xảy ra.

Khi tính toán các khoản thanh toán hàng tuần, hàng tháng hoặc hàng quý, hãy đảm bảo chuyển đổi lãi suất hàng năm sang tỷ lệ thời gian tương ứng như trong các ví dụ trên, nếu không, kết quả của công thức PPMT của bạn sẽ sai.

Cập nhật thông tin chi tiết về Công Thức Trong Excel, Ví Dụ Và Mẹo Để Tiết Kiệm Thời Gian trên website Hoisinhvienqnam.edu.vn. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất. Chúc bạn một ngày tốt lành!