20 Công Thức Excel cần thiết cho mọi người, ví dụ và mẹo

Trong bài viết này, Học Excel Online sẽ hướng dẫn những điều cơ bản về tạo và sử dụng các công thức Excel. Và bởi vì một trong những cách hiệu quả nhất để học là thông qua thực hành, chúng tôi cũng sẽ thảo luận về một số công thức ví dụ để làm cho mọi việc trở nên dễ hiểu hơn.

Công thức Excel – những điều cơ bản

Trong MS Excel, công thức excel 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!

Microsoft Excel formulas

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 hoàn toàn có thể sử dụng các thành phần khác nhau để cung ứng tài liệu nguồn cho công thức và chỉ ra các toán tử nên được triển khai trên những tài liệu đó. Tùy thuộc vào loại công thức mà bạn tạo ra, nó hoàn toàn có thể gồm có bất kể hoặc toàn bộ 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.

Basic elements of Excel formulas

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 để triển khai các phép toán cơ bản như cộng, trừ, nhân và chia .

Toán tử Ý nghĩ Công thức ví dụ
+ Phép cộng =A2+B2
Phép trừ
Dấu âm
=A2-B2
=-A2 (đổi dấu giá trị của ô A2)
* Phép nhân =A2*B2
/ Phép chia =A2/B2
% Phần trăm =A2*10%
(trả về kết quả 10% của giá trị ô A2)
^ Lũy thừa =A2^3
(Lũy thừa 3 của ô A2)

Ví dụ : nếu bạn có Chi tiêu trong ô A2 và Hóa Đơn đỏ VAT trong ô B2, bạn hoàn toàn có thể tính số tiền thuế GTGT bằng cách sử dụng công thức tỷ suất Phần Trăm sau : = A2 * B2
Using mathematical operators in Excel formulas

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ài hòa và 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 :

Mọi Người Cũng Xem   Tính toán vốn FDI: “Cứ theo như nước ngoài thì rất khó!”
Toán tử so sánh Ý nghĩa Công thức ví dụ
= Bằng =A2=B2
<> Không bằng =A2<>B2
> Lớn hơn =A2>B2
< Nhỏ hơn =A2
>= Lớn hơn hoặc bằng =A2>=B2
<= Nhỏ hơn hoặc bằng =A2<=B2

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à ( và ). Bạn hoàn toàn có thể sử dụng nó để phối 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ã vương quốc trong cột A và số điện thoại cảm ứng trong cột B, bạn hoàn toàn có thể sử dụng công thức sau để nhận số điện thoại thông minh phối hợp với mã vương quốc :

= A1 & “” & B1

Trong công thức trên, tất cả chúng ta thêm một khoảng chừng trống vào giữa để làm cho các số lượng dễ đọc hơn :
Using the text concatenation operator in Excel formulas
Kết quả tựa như hoàn toàn có thể đạt được bằng cách sử dụng tính năng CONCATENATE .

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

Để phân phối vùng tài liệu cho MS Excel công thức và đối số riêng không liên quan gì đến nhau 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ụ, khoanh vùng phạm vi A1 : A00 gồm có 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 hoàn toàn có thể tìm hiểu thêm hàng loạt cột ( A : A ) hoặc hàng loạt hàng ( 1 : 1 ). Ví dụ : công thức sau tìm tổng số toàn bộ các số trong cột A : = SUM ( A : A ) .

Dấu phẩy (,) – được sử dụng để phân biệt đối số trong các công thức bảng tính Excel. Ví dụ, công thức = IF (A1> 0, “tốt”, “xấu”) đọc như sau: nếu A1 lớn hơn không, trả lại “tốt”, nếu không “xấu”.

Chú thích. Dấu phẩy là dấu ngăn cách mặc định ở Bắc Mỹ và 1 số ít nước khác. Tại các vương quốc Châu Âu, dấu phẩy được giữ như Biểu tượng thập phân và dấu phân làn thường được đặt thành dấu chấm phẩy ( ; ). Trong trường hợp này, bạn cần phải tách biệt đối số với các dấu chấm phẩy, ví dụ : = IF ( A1 > 0, “ tốt ”, “ xấu ” ) .
Vì vậy, nếu bạn đang nỗ lực tạo ra một công thức trong bảng tính của bạn, nhưng Excel không gật đầu nó và đưa ra lỗi “ formula invalid formula ”, hãy chuyển tới Regional Settings ( Control Panel > Region and Language > Additional Settings ) và kiểm tra ký hiệu nào được đặt là dấu phân làn ở đó. Đó là dấu mà bạn cần sử dụng để phân biệt đối số trong công thức Excel của bạn .

Khoảng trắng – nó là một toán tử giao lộ cho phép bạn lấy được các ô tương ứng với hai tham chiếu mà bạn chỉ định. Ví dụ: nếu bạn có một danh sách các mục trong cột A và một số dữ liệu có liên quan trong các cột khác, bạn có thể nhận được giá trị tại giao điểm của một cột và hàng cho trước bằng cách sử dụng công thức như sau: = B3: D3 C2: C4

Use the intersection operator to get a cell common to the two references that you specify.

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 hoàn toàn có thể đơn thuầ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 cụ thể để 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

Như đã đề cập, bất kể công thức Excel nào mở màn với dấu bằng ( = ). Vì vậy, bất kể công thức nào bạn sẽ viết, mở màn bằng cách gõ = trong ô chỉ định hoặc trong thanh công thức Excel. Và giờ đây, tất cả chúng ta hãy cùng xem xét cách bạn hoàn toàn có thể tạo các công thức khác nhau trong Excel .
Làm thế nào để tạo các công thức đơn thuần trong Excel
Mặc dù các công thức Excel đơn thuần chỉ triển khai một phép tính, chúng hoàn toàn có thể triển khai theo nhiều cách khác nhau. Bạn hoàn toàn có thể cung ứng tài liệu nguồn dưới dạng hằng số, tham chiếu ô, hoặc các tên được xác lập, và triển khai giám sát bằng cách sử dụng các toán tử toán học hoặc các hàm Excel .

Mọi Người Cũng Xem   Bỏ túi 3 cách tắt ứng dụng chạy ngầm trên máy tính Windows

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

Khi bạn có một số ít kinh nghiệm tay nghề với các công thức Excel đơn thuần, bạn hoàn toàn có thể muốn triển khai 1 số ít đo lường và thống kê trong một công thức. Và các ví dụ sau cho thấy cách bạn hoàn toà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 để đúng chuẩn, 1 số ít thao tác nhất định phải được thực thi trước. Lệnh mặc định hoạt động giải trí 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 hoàn toàn có thể sử dụng các công thức sau để tính tổng số và hoa hồng :
Complex Excel formulas with constants and mathematic operators
Và giờ đây, hãy nghiên cứu và phân tích các công thức này để xem cách Microsoft Excel thống kê giám sát 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 số với 10 %, do đó bạn kèm theo giám sát trước đó trong ngoặc và nhận được tác dụng mong ước .
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 thuầ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 giám sát độc lập một cách hài hòa và 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 năm nay, 2013, 2010 và 2010, bạn hoàn toà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 .
Dưới đây là một ví dụ rất đơn thuần về công thức Excel lồng nhau có chứa hàm SUM để tìm tổng số, và tính năng ROUND để tròn số đó đến số nguyên gần nhất ( 0 chữ số thập phân ) : = ROUND ( SUM ( B2 : B6 ), 0 )
An example of Excel formula with nested functions
Trong toàn bộ các hàm Excel, hàm IF lồng tiếp tục hơn tổng thể các hàm khác. Như bạn hoàn toàn có thể biết, hàm IF được sử dụng để nhìn nhận một điều kiện kèm theo đơn cử và trả về một giá trị khi điều kiện kèm theo được cung ứng và một giá trị khác khi điều kiện kèm theo không được phân phối. Tuy nhiên, trong thực tiễn bạn thường phải đối phó với các trường hợp có nhiều hơn hai hiệu quả hoàn toàn có thể xảy ra. Và nếu trường hợp này, bạn hoàn toàn có thể viết một số ít hàm IF và lồng nó vào nhau :
Using nested IF functions in Excel

Công thức mảng trong Excel

Các công thức mảng trong Excel là một bước nâng cấp cải tiến. Một công thức mảng Excel đơn hoàn toàn có thể thực thi hàng ngàn phép tính và sửa chữa thay thế hàng trăm công thức thường thì. Nghiên cứu công thức mảng chắc như đinh yên cầu thời hạn và công sức của con người, nhưng xứng danh .
Vì hướng dẫn này dành cho người mới khởi đầ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 thuần của một công thức mảng Excel bộc lộ những gì nó hoàn toà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 yên cầu phải so sánh hai dãy và bạn hoàn toàn có thể làm điều này bằng cách sử dụng công thức mảng sau :

= SUM ((B2: B10> = A2: A10) * (B2: B10> 0))

An example of an Excel array formula

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 kiến thiết xây dựng, bạn vẫn hoàn toàn có thể thấy mình phải đương đầu với một thử thách mà không có hàm Excel tương thích. Trong trường hợp này, bạn hoàn toà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 quan trọng hữu dụng cho các đo lường và thống kê toán hoặc kỹ thuật hạng sang. 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 ( USD ) chỉ được sử dụng cho một mục tiêu – nó nói với Microsoft Excel rằng có biến hóa hay không đổi khác các tham chiếu ô khi công thức được vận động và di chuyển hoặc sao chép sang các ô khác .

Mọi Người Cũng Xem   Cách Tính Lô Đề Theo Pascal Hiệu Quả Nhất Từ Chuyên Gia

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.

Hình dưới đây cho thấy các loại tham chiếu khác nhau hoạt động như thế nào trong thực tế.

Absolute, relative and mixed cell references in Excel formulas

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 can đảm và mạnh mẽ và chúng hoàn toàn có thể xử lý rất nhiều việc làm trong bảng tính của bạn. Tất nhiên, học các góc nhìn khác nhau của hàm Excel và công thức sẽ tốn thời hạn, vì thế bạn hoàn toàn có thể cảm thấy không có đủ thời hạn để tìm hiểu và khám phá toàn bộ mọi thứ. Vâng, một cách tốt để tìm thêm thời hạn là để tiết kiệm chi phí một thời hạn 🙂

  • Để 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.
  • Để xem tất cả công thức trên trang tính, nhấp vào nút Show Formulas trên tab Formulas> Nhóm Formulas Auditing hoặc nhấn phím tắt Ctrl + ~.
  • Để 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.

Để hoàn toàn có thể ứng dụng tốt Excel vào trong việc làm, tất 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 vận dụng tốt vào việc làm 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 và kỹ năng này các bạn đều hoàn toàn có thể học được trong khóa học Excel từ cơ bản đến chuyên viên cùng giảng viên Nguyễn Đức Thanh hay DT Nguyen .

Related Posts

About The Author

Add Comment