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

>=
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 cảm ứng 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 :

Kết quả tương tự 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

Để cung ứng 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ố tổng thể 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 phân làn 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 cố gắng 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 đồng ý 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 ngăn cách ở đó. Đó 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

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 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 khởi đầu 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ể phân phối 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 .

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 một 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 triển khai 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 :

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 đo lường và thống kê 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 đo lường và thống kê 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 thống kê 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à công dụ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 )

Trong tổng thể các hàm Excel, hàm IF lồng liên tục hơn toàn bộ 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, 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 :

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ể triển khai 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à sức lực lao động, 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))

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 giám sát 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 ( USD A $ 1 ), tương đối ( A1 ) và hỗn hợp ( USD 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ó đổi khác hay không đổi khác các tham chiếu ô khi công thức được chuyển dời 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.

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ế.

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, thế cho nên 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 ngân sách và 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.