Cách viết công thức trong Excel
Khi khám phá về công thức, hàm trong Excel tất cả chúng ta sẽ thấy một đặc thù chung là chúng không biến hóa trên hầu hết các phiên bản Excel. Ở một số ít 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 đổi khác .
- Để bắt đầu viết công thức, hàm, chúng ta sẽ bắt đầu với 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ộ biểu thức: =B1+B2+B3+B4+B5
- hoặc sử dụng hàm SUM như sau =SUM(B1: B5)
- Kết thúc thao tác này bằng cách sử dụng phím Enter trên bàn phím.
Các thành phần của công thức Excel
Khi viết một công thức trong Excel, bạn sẽ gặp nhiều thành phần khác nhau. Sau đây tất cả chúng ta cùng khám phá 1 số ít thành phần hay gặp :
- Tên hàm: Khi sử dụng 1 công thức, bạn có thể sử dụng các phép tính trực tiếp như cộng, trừ, nhân, chia… hoặc thông qua các hàm. Các hàm là những phương pháp tính toán đã được lập trình từ trước. Bạn sẽ chỉ cần gọi tên hàm, khai báo các thành phần bên trong hàm là hàm sẽ tự động tính toán để cho ra kết quả.
- Hằng số: là một giá trị số hoặc văn bản mà có nội dung cụ thể, giá trị cụ thể và không thay đổi, được viết trực tiếp trong công thức. Ví dụ: =3*5 thì số 3 và số 5 là hằng số.
- Tham chiếu: là tọa độ của ô chứa các giá trị được đưa vào công thức tính. Có thể là 1 ô lẻ hoặc 1 vùng gồm nhiều ô nối tiếp nhau, hoặc hỗn hợp nhiều vùng ô
Ví dụ : = SUM ( A1, A2, B5 )Trong công thức này sẽ tính tổng các giá trị có trong 3 ô là ô A1, A2 và ô B5
- Tên vùng ô: thay vì sử dụng tọa độ trong công thức, chúng ta có thể đặt tên cho các vùng ô trước, sau đó gọi tên các vùng này trong công thức. Như vậy cái bạn nhìn thấy sẽ là một cái tên, không phải hằng số cũng không phải tọa độ. Nhưng về bản chất đó chính là tọa độ. Tên vùng ô được sử dụng giống như các vùng ô:
Ví dụ : = SUM ( cot_SoLuong )
Chúng ta tính tổng trên vùng ô đã được đặt tên là cot_Soluong
Tham khảo : Cách đặt tên vùng trong Excel
- Toán tử: các ký hiệu đại diện cho các phép tính, hoặc phép so sánh trong công thức.
Ví dụ :
Các toán tử trong Excel rất phong phú, hãy cùng tìm hiểu và khám phá cụ thể hơn theo nội dung bên dưới :
Các ký hiệu toán tử trong công thức Excel
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: cộng, trừ, nhân, chia
- So sánh (hợp lý): để so sánh các giá trị: lớn hơn, nhỏ hơn…
- Kết nối: để nối các giá trị văn bản vào một chuỗi duy nhất, hoặc các ký tự đại diện cho 1 hoặc nhiều ký tự text
- Tham chiếu: để tạo ra các tham chiếu dạng cố định tọa độ
Sau đây tất cả chúng ta cùng tìm hiểu và khám phá chi tiết cụ thể hơn về các toán tử này
Cách sử dụng toán tử số học trong công thức Excel
Các toán tử số học đại diện thay mặt cho các phép tính cơ bản :
- dấu + là phép cộng
- dấu – là phép trừ
- dấu * là dấu nhân
- dấu / là phép chia
- dấu % là tính theo giá trị dạng phần trăm, nghĩa là số đó đã chia cho 100 (thường đi kèm với 1 hằng số)
- dấu ^ là đại diện cho số mũ (lũy thừa)
Một số ví dụ :
- =A2+B2 nghĩa là giá trị trong ô A2 cộng với giá trị ô B2
- =A2*10% nghĩa là giá trị trong ô A2 sẽ nhân với giá trị là 10% (tương đương =A2*0,1)
Cách sử dụng toán tử so sánh trong các công thức Excel
Khi bạn cần sử dụng tới các phép so sánh trong một công thức tính để xét 1 biểu thức logic, hoặc khi muốn đo lường và thống kê theo 1 khoảng chừng, bạn sẽ cần sử dụng tới các toán tử so sánh .Các toán tử này gồm :
- Dấu = có nghĩa là bằng nhau
- Dấu > có nghĩa là lớn hơn
- Dấu >= có nghĩa là lớn hơn hoặc bằng
- Dấu < có nghĩa là nhỏ hơn
- Dấu <= có nghĩa là nhỏ hơn hoặc bằng
- Dấu <> có nghĩa là khác nhau, không bằng
Ví dụ :
- =IF(A1>B1, “Đúng”, “Sai”) có nghĩa là xét trường hợp nếu (dùng hàm IF để xét) giá trị trong ô A1 lớn hơn giá trị trong ô B1 thì công thức sẽ cho kết quả là chữ Đúng, còn nếu giá trị trong ô A1 không lớn hơn B1 thì cho kết quả là chữ Sai.
- =COUNTIF(B1:B10, “>=5”) có nghĩa là đếm trong vùng B1:B10 có bao nhiêu giá trị lớn hơn hoặc bằng 5 (đếm theo điều kiện được thực hiện thông qua hàm COUNTIF)
Cách sử dụng toán tửnối văn bản trong Excel
Để nối các đoạn text lại với nhau, bạn có thể sử dụng dấu & (giữ phím shift và nhấn số 7 trên bàn phím).
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 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 dấu cách vào giữa 2 thành phần sau khi nối để làm cho các số lượng dễ đọc hơn :
Bạn cũng hoàn toàn có thể làm được tác dụng tương tự như như trên khi sử dụng hà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 thuần và thuận tiện hơn, tất cả chúng ta hoàn toàn có thể sử dụng một số ít dấu toán tử cho mục tiêu tham chiếu, gồm có :
- Dấu hai chấm (:) cho phép bạn tham chiếu tới 1 vùng ô liên tiếp nhau, thay vì việc phải chỉ định riêng từng ô
- Ví dụ: bạn muốn tính tổng trong 1 vùng liên tiếp nhau là các ô B1, B2, B3, B4, B5. Thay vì viết =SUM(B1, B2, B3, B4, B5) thì bạn có thể viết là =SUM(B1:B5). Cách viết này khiến công thức ngắn gọn và dễ viết hơn nhiều phải không nào.
Bạn cũng hoàn toàn có thể dùng chiêu thức 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 lập tọa độ của 1 đối tượng người tiêu dùng ( là dòng hoặc cột ) mà thôi
- Ví dụ: Tính tổng toàn bộ các số trong cột A: =SUM(A:A)
- Dấu phẩy (,) dùng để phân biệt các thành phần (tham số) trong một hàm.
- Ví dụ: hàm IF trong Excel gồm 3 thành phần, khi viết ta có:
= 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ề tác dụng là chữ Đúng, còn không phải thì trả về tác dụng là chữ Sai .
Trong 1 số ít trường hợp bạn hoàn toàn có thể phát hiện dấu chấm phẩy ( ; ) được sử dụng để thao tá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 trọn vẹn hoàn toàn có thể đổi khác 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 .
- Khoảng trắng (dấu cách) Dùng để xác định giá trị là giao điểm giữa 2 vùng ô.
- Ví dụ: Bạn có 2 vùng ô là B3:D3 (dòng 3) và C2:C4 (cột C). Để xác định giá trị là giao điểm giữa 2 vùng này bạn có thể viết như sau:
= 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 hoàn toàn có thể viết :
- Công thức đơn: chỉ có duy nhất 1 công thức hoặc 1 hàm.
- Công thức phức tạp: Gồm nhiều hàm, nhiều biểu thức tính toán trên cùng 1 công thức. Các hàm có thể lồng vào nhau trong cùng 1 công thức… Điều này khiến bạn gặp khó khi viết công thức.
Chúng ta hãy cùng tìm hiểu và khám phá đơn cử 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 :
- Các phép tính được thực hiện theo chiều từ trái qua phải, tính từ vị trí dấu bằng (để bắt đầu công thức tính)
- Phép tính nhân chia sẽ được thực hiện trước, các phép tính cộng trừ sẽ được thực hiện sau
- Các tham số đặt trong dấu ngoặc đơn sẽ được tính toán trước, sau đó mới tính toán ở bên ngoài dấu ngoặc đơn.
- Nếu bắt đầu với dấu trừ (ví dụ =-A5+B6-…) thì tham số đầu tiên kèm theo dấu trừ được hiểu là số âm.
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.
Xem thêm: ✅ CÔNG THỨC TOÁN 12 ⭐️⭐️⭐️⭐️⭐️
Ví dụ : Chúng ta có bảng chứa các thông tin về xác lập tiền bán hàng các loại sản phẩm và tính tiền hoa hồng, tiền thuế như sau :
Để xác lập 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ó :
- Với sản phẩm Cam, thành tiền ở ô D2, Hoa hồng ở ô E2, như vậy tiền thuế = (D2 – E2) * 10%
- Nhưng nếu viết dạng không có dấu ngoặc đơn thì D2-E2*10% thì kết quả sẽ khác. Biểu thức này sẽ tính E2*10% trước, sau đó mới lấy giá trị trong ô D2 trừ đi kết quả này. Điều này không đúng.
Khi viết dạng sử dụng dấu ngoặc, ta có phép tính D2-E2 được thực thi trước, sau đó ra tác dụng 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 bảo vệ được tác dụng đúng chuẩn ngay, mà phải qua nhiều bước giám sát 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 trường hợp tính tiền thuế ở trên, tất cả 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 :
- Cột D để tính thành tiền = Số lượng * Đơn giá
- Cột E để tính Hoa hồng
- Cột F để tính tiền thuế.
Vậy tất cả chúng ta hoàn toàn có thể chỉ cần 1 cột duy nhất để tính ra hiệu quả này không ?Câu vấn đáp là có. Hãy xem tác dụng 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 tất cả chúng ta thấy thay vì tính riêng từng giá trị Thành tiền, Hoa hồng thì tất cả chúng ta sẽ tính gộp luôn trong cùng 1 biểu thức .Nhận xét :
- Ưu điểm: giúp bảng tính đơn giản hơn, ít cột hơn
- Nhược điểm: công thức phức tạp hơn
Xét tiếp một ví dụ khác : Tính tổng số tiền thuế nhưng tác dụng 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ị chức năng 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 đó tất cả chúng ta có 2 hàm :
- Hàm SUM(D2:D6) để tính tổng các số trong vùng D2:D6
- Hàm ROUND(…, -3) để làm tròn số đó (là kết quả của hàm SUM) lên tới phần nghìn (với tham số là -3)
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 quan trọng khi viết công thức. Nó làm cho Excel mưu trí hơn khi đo lường và thống kê theo những điều kiện kèm theo phức tạp hơn thông thường, nhưng đồng thời nó lại khiến bạn khó khăn vất vả hơn trong việc đọc hiểu công thức này .Để giúp bạn hoàn toà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 giải trí của công thức này, tất cả chúng ta sẽ xem xét ví dụ sau đây :Tính tổng số tiền các mẫu 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ì, mà phải sử dụng tổng hợp phím Ctrl + Shift + Enter. Đây là tổng 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ổng 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 tác dụng :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 nhu cầu điều kiện kèm theo 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 đã san 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 dụng trong việc nâng cao năng lực viết công thức tính trong Excel của bạn. Nếu có bất kể vướng mắc gì bạn hoàn toàn có thể góp ý, phàn hồi ngay dưới bài viết này để chúng tôi hoàn toàn có thể xử lý mọi vướng mắc của bạn một cách sớm nhất .Những kiến thức và kỹ năng 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 phân phối cho bạn kiến thức và kỹ năng một cách rất đầy đủ và có mạng lưới hệ thống về các hàm, các công cụ trong excel, ứng dụng excel trong việc làm … Hiện nay mạng lưới hệ thống đang có nhiều khuyễn mãi thêm khi bạn ĐK tham gia khóa học này .
Tải về file mẫu trong bài viết
Bạn hoàn toà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
Cach-viet-cong-thuc-ham-trong-excel.xlsxTải xuống
Source: https://camnangbep.com
Category: Học tập