Wednesday, 08 February, 2023

Vietnam's Got Talent - vietnamgottalent.vn

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


excel cell references

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 những thành phần khác nhau để phân phối tài liệu nguồn cho công thức và chỉ ra những 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 tổng thể những 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 để thực thi những 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ó giá thành 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 Tỷ Lệ 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 những 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 :

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 những ô 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 thông minh 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 tích 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 những 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 công dụ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, những 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ể những 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à một số ít nước khác. Tại những 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 ngăn cách 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 những 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 những 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, khởi đầu 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 những công thức khác nhau trong Excel .
Làm thế nào để tạo những công thức đơn thuần trong Excel
Mặc dù những 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 những tên được xác lập, và triển khai giám sát bằng cách sử dụng những toán tử toán học hoặc những 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 những công thức Excel đơn thuần, bạn hoàn toàn có thể muốn triển khai 1 số ít thống kê giám sát trong một công thức. Và những 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 những 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 những 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 những 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 giám sát trước đó trong ngoặc và nhận được hiệu quả 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 những bảng tính lớn, nên viết những 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 những 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 những 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 )
An example of Excel formula with nested functions
Trong toàn bộ những hàm Excel, hàm IF lồng liên tục hơn toàn bộ những 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 phân phối 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 những trường hợp có nhiều hơn hai tác dụng 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ể 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 mở màn, tôi sẽ không hăm dọa bạn bởi những định nghĩa những hằng số mảng và những 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 có ích cho những thống kê giám sát toán hoặc kỹ thuật hạng sang. Giống như macro, những 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 những 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ó đổi khác hay không biến hóa những tham chiếu ô khi công thức được vận động và di chuyển hoặc sao chép sang những ô 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ế.

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 những 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 để 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.

Để 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 những hàm mà còn phải sử dụng tốt cả những 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 kỹ năng và kiến thức này những 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 .

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

Trả lời

[Review] 72 tư thế quan hệ tình dục phê không tưởng có hình ảnh sống động
[Review] 72 tư thế quan hệ tình dục phê không tưởng có hình ảnh sống động

Social