Bạn đang xem bài viết Các Ví Dụ Về Hàm Sum Trong Excel được cập nhật mới nhất tháng 9 năm 2023 trên website Phauthuatthankinh.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 bài viết này Học Excel Online sẽ hướng dẫn các bạn cách tính tổng trong Excel bằng cách dùng tính năng AutoSum, và cách tạo công thức Sum để tính tổng các cột, hàng hoặc một dải ô đã được chọn. Ngoài ra, các bạn cũng sẽ học được cách tính tổng riêng những ô được hiển thị, tổng của các trang tính, đồng thời giúp bạn tìm hiểu thêm về những lý do khiến hàm Sum không hoạt động.
Cách tính tổng trong Excel dùng phép tính số học đơn giảnNếu bạn cần tính nhanh tổng của một số ô, bạn có thể sử dụng Microsoft Excel như một chiếc máy tính mini. Chỉ cần dùng dấu (+) giống như trong phép toán số học thông thường. Ví dụ:
Tìm hiểu ngay: Địa chỉ học Excel tại Hà Nội
Cách sử dụng hàm Sum trong ExcelExcel SUM là một hàm toán học dùng để tính tổng. Cú pháp của hàm Sum như sau:
Sum (số thứ nhất, [số thứ 2] ,…) Đối số đầu tiên là bắt buộc, các cố khác là tùy chọn, bạn có thể áp dụng hàm Sum cho tối đa 225 số trong một công thức.
Trong công thức Sum của Excel, mỗi đối số có thể là một giá trị số dương hoặc số âm, một hàng hoặc một chuỗi các ô. Ví dụ như:
Hàm SUM trong Excel thực sự là lựa chọn hữu ích khi bạn cần cộng các giá trị nằm ở cách dải khác nhau hoặc tính tổng các giá trị số, các ô tham chiếu hay các dãy. Ví dụ: =SUM(A2:A4, A8:A9)
=SUM(A2:A6, A9, 10)
Ví dụ, bạn có thể kết hợp hàm SUM với đối số value_if_true thuộc hàm IF để cộng các giá trị trong cột B, C và D nếu tất cả các ô trong cùng hàng có cả ô chứa giá trị và ô trống:
Cách dùng AutoSum trong Excel:Nếu bạn cần tính tổng một dãy các số hoặc tổng của vài hàng, cột liền kề thì có thể sử dụng công thức SUM được Microsoft Excel thiết kế sẵn.
Mẹo: Cách nhanh hơn để sử dụng AutoSum trong Excel là dùng phím tắt Alt + =. Bạn chỉ cần giữ phím Alt, nhấn phím Equal Sign, rồi Enter là hoàn thành việc chèn công thức Sum.
Ngoài tính tổng, bạn có thể sử dụng AutoSum để tự động nhập các hàm AVERAGE, COUNT, MAX, hay MIN.
Cách tính tổng một cột trong ExcelĐể tính tổng các số liệu trong một cột cụ thể, bạn có thể sử dụng hàm SUM thông thường hoặc tính năng AutoSum. Ví dụ, để tính tổng các giá trị trong cột B, từ B2 đến B8, bạn nhập công thức SUM như sau:
Cách tính tổng toàn bộ cột với số hàng vô hạn không xác định đượcNếu cột mà bạn muốn tính tổng có số lượng dòng thay đổi (nghĩa là các ô mới có thể được thêm vào và những giá trị cõ sẵn có thể bị xóa đi), bạn có thể tính tổng toàn bộ cột bằng cách thêm tham chiếu cột, không xác định giới hạn trên và dưới. Ví dụ:
Tính tổng cột nhưng bỏ hàng tiêu đề hoặc một vài hàng đầu tiênKích thước bảng tính có giới hạn nhất định nên bạn có thể chỉ định giới hạn trên của công thức SUM dựa trên số hàng tối đa trong phiên bản Excel mà bạn dùng. Ví dụ: để tính tổng cột B mà không bao gồm hàng tiêu đề (loại trừ ô B1) bạn có thể sử dụng các công thức sau:
Trong Excel 2007, Excel 2010, Excel 2013 và Excel 2023:
= SUM (B2: B1048576)
Trong Excel 2003 và phiên bản thấp hơn:
= SUM (B2: B655366)
Cách tính tổng các hàng trong ExcelTương tự như tính tổng một cột, bạn có thể tính tổng một hàng trong Excel bằng cách sử dụng hàm SUM, hoặc AutoSum để chèn công thức tính.
Ví dụ, để tính tổng các giá trị trong ô B2 đến D2, ta sử dụng công thức sau:
Cách tính tổng nhiều hàng trong Excel= SUM (B2: D6) – tổng các giá trị từ hàng 2 đến hàng 6.
= SUM (B2: D3, B5: D6) – tổng các giá trị trong các hàng 2, 3, 5 và 6.
Cách tính tổng toàn bộ một hàngĐể tính tổng toàn bộ hàng với một số cột không xác định, ta cần cung cấp tham chiếu toàn bộ hàng cho công thức Excel Sum, ví dụ:
= SUM (2: 2) -SUM (A2: B2)
Sử dụng Excel Total Row để tính tổng dữ liệu trong một bảng:Nếu dữ liệu của bạn được nằm trong một bảng tính của Excel, bạn có thể sử dụng tính năng Total Row để nhanh chóng tính tổng dữ liệu trong bảng và tổng số hiển thị trong hàng cuối cùng.
Một lợi thế lớn của việc sử dụng các bảng Excel là chúng tự động mở rộng để thêm được các hàng mới, do đó, bất kỳ dữ liệu mới nào bạn nhập vào trong một bảng sẽ được tự động thêm luôn vào công thức đã có.
Để chuyển đổi một dải ô bình thường thành một bảng, hãy nhấn chọn và ấn phím tắt Ctrl + T (hoặc nhấp vào Table trên tab Insert).
Cách thêm hàng total trong bảng ExcelKhi dữ liệu của bạn được sắp xếp trong một bảng, bạn có thể chèn thêm hàng total theo cách sau:
Khóa học lập trình VBA trong Excel
Cách tính tổng dữ liệu trong bảngKhi tổng số hàng xuất hiện ở cuối bảng, Excel sẽ tự xác định cách tính toán dữ liệu trong bảng.
Nếu bạn muốn thực hiện một số phép tính khác, hãy chọn chức năng tương ứng từ danh sách thả xuống như Average, Count, Max, Min, …
Nếu hàng total tự động hiển thị tổng cho một cột mà bạn không cần dùng đến tổng đó thì mở danh sách thả xuống của cột đó và chọn None.
Chú thích. Khi sử dụng tính năng Excel Total Row để tính tổng một cột, tổng số Excel chỉ có giá trị trong hàng hiển thị bằng cách chèn hàm SUBTOTAL với đối số đầu tiên là 109.
Cách tính tổng các ô được (có thể nhìn thấy được) trong ExcelĐôi khi, để việc phân tích dữ liệu hiệu quả hơn, bạn cần phải lọc hoặc ẩn một số dữ liệu trong bảng tính. Một công thức tổng hợp thông thường sẽ không hoạt động trong trường hợp này bởi vì hàm Excel SUM sẽ cộng tất cả các giá trị trong phạm vi được chỉ định bao gồm các hàng ẩn (đã lọc ra).
Nếu bạn muốn tính tổng các ô hiển thị trong một danh sách được lọc, cách nhanh nhất là đưa chúng vào một bảng Excel, và sau đó bật tính năng Excel Total Row. Như đã trình bày trong ví dụ trước, chọn Sum ở hàng total của bảng rồi chèn chức năng SUBTOTAL bỏ qua các ô ẩn.
Một cách khác để tính tổng các ô đã lọc trong Excel là áp dụng AutoFilter cho dữ liệu của bạn theo cách thủ công bằng cách nhấp vào nút Filter trên tab Data. Và sau đó, tự viết một công thức Subtotal.
Hàm SUBTOTAL có cú pháp sau:SUBTOTAL (function_num, ref1, [ref2], …) Trong đó, Function_num – một số từ 1 đến 11 hoặc từ 101 đến 111 dùng để xác định chức năng sẽ được dùng cho việc tính tổng phụ.
Bạn có thể tìm thấy danh sách đầy đủ các chức năng trên chúng tôi Bây giờ, chúng ta chỉ quan tâm đến chức năng SUM, được định nghĩa bởi các số 9 và 109. Cả hai số này loại trừ các hàng được lọc ra. Sự khác biệt là 9 bao gồm các ô ẩn thủ công (tức là bạn chuột phải rồi chọn Hide), trong khi đó 109 không bao gồm các ô đó.
Vì vậy, nếu bạn muốn tính tổng cho riêng những hàng được hiển thị, không tính những hàng đã bị ẩn thì có thể dụng 109 làm đối số cho công thức Subtotal của bạn.
Ref1, Ref2, … – ô hoặc dãy mà bạn muốn tính tổng phụ. Đối số Ref đầu tiên là bắt buộc, những đối số tiếp theo (lên đến 254) là tùy chọn. Trong ví dụ này, ta sẽ tính tổng các ô có thể nhìn thấy trong khoảng B2: B14 bằng cách sử dụng công thức sau:
Cách sử dụng running total (tính tổng lũy tích) trong ExcelĐể tính tổng lũy tích trong Excel, bạn viết công thức SUM như bình thường và sử dụng thông minh các tham chiếu của các ô một các tuyệt đối và tương đối.
Ví dụ, muốn hiển thị sô lũy tích của các số trong cột B, ta nhập công thức sau và ô B2 rồi sao chép sang các ô khác:
Cách tính tổng cho nhiều trang tính trên cùng bảng tínhNếu bạn có một vài bảng tính với bố cũng và dữ liệu tương tự nhau, bạn có thể cộng tổng các dữ liệu trong cùng một ô hoặc dải ô ở các trang tính khác nhau với cùng một công thức SUM.
Chúng ta có một công thức gọi là tham chiếu 3-D:=SUM(Jan:Apr!B6)
=SUM(Jan:Apr!B2:B5)
Tính tổng có điều kiện trong ExcelNếu công việc của bạn yêu cầu tính tổng các ô đáp ứng điều kiện nhất định nào đó thì bạn có thể sử dụng hàm SUMIF hoặc SUMIFS tương ứng.
Ví dụ: công thức SUMIF sau chỉ cộng tổng các giá trị trong cột B có trạng thái “Completed” (đã hoàn thành) trong cột C, ta có:
Excel SUM không hoạt động – lý do và giải phápKhi bạn tính tổng của một vài giá trị hoặc một số cột trong bảng tính Excel nhưng công thức SUM đơn giản không hoạt động thì có thể là do những lý do sau đây:
1. #Name error xuất hiện thay vì kết quả mong đợiĐó là lỗi dễ sửa chữa nhất. 99 trên 100 trường hợp, lỗi #Name error chỉ ra rằng hàm SUM bị sai chính tả.
2. Một số con số không được cộng vào tổng chungMột lý do phổ biến khác khiến một công thức Sum (hoặc Excel AutoSum) không hoạt động là các con số được định dạng dưới dạng các giá trị văn bản. Thoạt nhìn, chúng trông giống như các con số bình thường, nhưng Microsoft Excel nhận dạng chúng như chuỗi văn bản nên không đưa chúng vào tính toán.
3. Hàm Excel SUM trả về kết quả bằng 0 4. Công thức Excel Sum trả về một con số cao hơn dự kiếnNếu công thức Sum của bạn sẽ trả về một số lớn hơn dự kiến, bạn cần nhớ rằng hàm SUM trong Excel sẽ thêm cả các ô ẩn nà ta không nhìn thấy được. Trong trường hợp này, bạn có thể sử dụng chức năng Subtotal để thay thế công thức SUM.
5. Công thức Excel SUM không cập nhậtKhi một công thức SUM trong Excel tiếp tục hiển thị tổng số cũ ngay cả sau khi bạn đã cập nhật các giá trị cần tính tổng mới, nhiều khả năng Calculation Mode được đặt thành Manual. Để khắc phục sự cố này, hãy chuyển đến tab Formulas, nhấp vào mũi tên thả xuống bên cạnh Calculate Options, rồi nhấn Automatic.
Ngoài ra để ứng dụng Excel vào công việc một cách hiệu quả thì bạn còn phải sử dụng tốt các hàm, các công cụ khác của Excel.
Một số hàm cơ bản thường gặp như:
SUMIF, SUMIFS để tính tổng theo 1 điều kiện, nhiều điều kiện
COUNTIF, COUNTIFS để thống kê, đếm theo một điều kiện, nhiều điều kiện
Các hàm xử lý dữ liệu dạng chuỗi, dạng ngày tháng, dạng số…
Các hàm dò tìm tham chiếu Index+Match, hàm SUMPRODUCT…
Một số công cụ hay sử dụng như:
Định dạng theo điều kiện với Conditional formatting
Thiết lập điều kiện nhập dữ liệu với Data Validation
Cách đặt Name và sử dụng Name trong công thức
Lập báo cáo với Pivot Table…
Rất nhiều kiến thức phải không nào? 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 dành cho người đi làm
Hàm Sum Trong Excel – Cách Sử Dụng Hàm Tính Tổng Qua Ví Dụ
4.4
/
5
(
17
bình chọn
)
Hàm SUM trong Excel là hàm tính tổng và cũng là hàm thông dụng nhất trong Excel mà bất kỳ ai khi mới dùng Excel đều phải biết. Cách sử dụng hàm SUM này cũng khá đơn giản và dễ hiểu.
Trong bài viết này, Nguyễn Hùng sẽ hướng dẫn các bạn sử dụng hàm SUM thông qua các ví dụ trực quan để bạn có thể hiểu được và làm được.
Hướng dẫn sử dụng hàm SUM trong Excel
Chức năng: Hàm SUM trong Excel là một trong các hàm toán học và lượng giác, giúp tính tổng cộng tất cả các đối số của hàm.
Cú pháp lệnh: =SUM(number 1,[number 2],…)
Giải thích:
=SUM(……): Cú pháp lệnh cần phải có.
Number 1 (bắt buộc): Số (ô) đầu tiên muốn thêm vào. Số đó có thể là số 3, 4, 5,…, hoặc ô tham chiếu như D7, D8,…, hoặc ô phạm vi như D7:D11.
Number 2 (tùy chọn): Đây là số thứ 2 bạn có thể thêm hoặc không tùy chọn. Bạn có thể bổ sung đến 255 số bổ sung thêm.
Ví dụ sử dụng hàm SUMVí dụ 1:
=SUM(2,3,4): Thêm vào các số 2,3,4 tính được tổng bằng 9.
=SUM(3,4,TRUE): Thêm vào giá trị số 3,4 và giá trị logic TRUE đầu tiên được dịch là 1. Kết quả là bằng 8.
=SUM(“3”,4,TRUE): Thêm vào giá trị văn bản “3” đầu tiên được dịch thành chữ số, và thêm số 4, thêm tiếp vào giá trị logic TRUE được dịch thành số 1. Tổng là 8.
Ví dụ 2:
Giả sử chúng ta có bảng dữ liệu như sau:
Ta có các công thức tính như sau:
Tại ô D13 sử dụng công thức tính tổng: =SUM(D7:D11) để lấy giá trị từ ô D7 đến ô D11 ta có kết quả bằng 630.
Lời kết
Hàm Sum Trong Excel, Ví Dụ Và Cách Dùng Hàm Tính Tổng Theo Hàng Dọc
Nội dung hữu ích * Sửa lỗi hàm sum bằng 0 trong Excel* Sửa lỗi hàm Sum không cộng được trong Excel* Bài mẫu hàm SUM (Kết hợp các hàm khác, có lời giải) * Làm quen hàm SUMIF, hàm tính tổng có điều kiện trong Excel Hướng dẫn dùng hàm SUM trong Excel – Ví dụ minh họa
1. Cú pháp hàm SUM trong EXCEL
– Cú pháp: =SUM (number 1, number 2, …) – Chức năng: Tính tổng number 1, number 2, …– Trong đó: number 1, number 2, … là các đối số mà bạn muốn tính tổng.
Chú ý:– Ô tính có giá trị logic TRUE được xem là 1, FALSE được xem là 0.– Nếu đối số là mảng hay tham chiếu thì chỉ các giá trị số trong mảng hay tham chiếu đó mới được tính. Các giá trị khác trong mảng hoặc tham chiếu bị bỏ qua.
2. Ví dụ cụ thể dùng hàm SUM trong EXCE
Ví dụ 1:=SUM (1, 2, 3) có giá trị bằng 6.=SUM (2, 3, TRUE) có giá trị bằng 6.=SUM (“2″,”3”,1) có giá trị bằng 6.
Ví dụ 2: Ta có bảng tính lương của nhân viên như sau
– Áp dụng hàm SUM để tính tổng tiền của nhân viên trong tháng ở cột thực lĩnh– Công thức sử dụng: F10=SUM (F5:F9) để chọn tất cả các giá trị từ ô F5 tới F9. Ta có kết quả như hình dưới
Vậy là các bạn đã có kết quả tổng tiền lương phải chi trả cho nhân viên
3. Các lỗi khi sử dụng hàm SUM trong EXCEL
Nếu gặp phải lỗi hàm Excel Sum, có thể là lỗi #VALUE!:
Lỗi phổ biến:#VALUE!: Lỗi này xảy ra nếu bất kỳ đối số nào được cung cấp trực tiếp cho hàm SUM không thể diễn giải là các giá trị số.Lưu ý: nếu một vùng dữ liệu ô được cung cấp chứa các giá trị không thể diễn giải thành các giá trị số, hàm SUM sẽ bỏ qua nó.
Các giá trị nào được đưa vào hàm SUM để tính toán?
Các giá trị số và ngày tháng được coi là giá trị số trong hàm Excel SUM. Tuy nhiên các giá trị văn bản và giá trị logic được xử lý khác nhau, tùy thuộc vào giá trị đó là giá trị được lưu trữ trong các ô của bảng tính hoặc được cung cấp trực tiếp cho hàm SUM.
4. Khắc phục lỗi khi sử dụng hàm SUM
Nếu hàm SUM tham chiếu ô có chứa lỗi #VALUE!, công thức sẽ trả về lỗi #VALUE!.
Để khắc phục lỗi #VALUE!, cách đơn giản là xây dựng một công thức bỏ qua vùng tham chiếu chứa lỗi để tính trung bình các giá trị “bình thường” còn lại.
Để thực hiện theo kịch bản này, bạn sử dụng kết hợp hàm SUM với hàm IF và hàm ISERROR để xác định xem có lỗi trong vùng tham chiếu cụ thể hay không. Kịch bản này yêu cầu công thức mảng:
=SUM(IF(ISERROR(B2:D2),””,B2:D2))
Lưu ý: vì đây là công thức mảng (array) nên bạn sẽ phải nhập bằng cách nhấn các phím CTRL + SHIFT + ENTER. Excel sẽ tự động bọc công thức trong dấu ngoặc {}. Nếu thử nhập các công thức này tự động, Excel sẽ hiển thị công thức dưới dạng văn bản.
Ngoài ra có thể sử dụng hàm trên để khắc phục lỗi #VALUE!, lỗi #N/A, #NULL, #p/0!, và một số lỗi hàm SUM khác.
Các bạn vừa tìm hiểu hàm SUM trong excel, thao tác của nó cực kỳ đơn giản. Ngoài ra bạn hãy tìm hiểu thêm một số hàm tính tổng với điều kiện cho trước như hàm SUMIF trong Exel để thực hiện tính toán trên bảng tính với các bài toán kết hợp nhiều điều kiện mới có kết quả mong muốn.
https://thuthuat.taimienphi.vn/ham-sum-trong-excel-1295n.aspx
Hàm And Trong Excel – Cách Dùng, Cú Pháp Và Ví Dụ Về Hàm And
4
/
5
(
2
bình chọn
)
Hàm AND trong Excel là một hàm logic được sử dụng rất nhiều để kiểm tra xem liệu tất cả các điều kiện có đúng hay không. Nó sẽ trả về các kết quả với giá trị TRUE hoặc FALSE giúp bạn kiểm nghiệm tính logic. Trong thực tế, bạn sẽ thường xuyên phải kết hợp hàm AND và hàm IF để có thể kiểm tra với nhiều điều kiện thay vì một điều kiện để giúp tăng năng suất công việc.
Hướng dẫn cách dùng hàm AND trong ExcelHàm AND là hàm Excel thông dụng nên nó sử dụng được trong mọi phiên bản Exel bao gồm: Excel 365, Excel 2023, Excel 2013, Excel 2010, Excel 2007, Excel cho Mac, Excel mobile. Vậy nên bạn hãy cài đặt Office 2010 hoặc Office 2013 để có thể thực hành Excel ngay trên máy của bạn. Trong bài này mình sử dụng hàm AND trên Excel 2023, nếu bạn dùng phiên bản khác cũng thực hiện tương tự.
Cú pháp của hàm AND=AND(logical1,[logical2], …)
Các giá trị trong hàm AND
logical1: Là điều kiện thứ nhất bạn muốn kiểm nghiệm. Đây là đối số bắt buộc.
logical2: Là những điều kiện khác mà bạn muốn kiểm nghiệm. Đây là đối số tùy chọn. Tối đa bạn có thể thêm là 255 đối số.
Các lưu ý khi dùng hàm AND
Các đối số (điều kiện) phải chỉ định về các giá trị logic hay các mảng hoặc tham chiếu có chứa giá trị logic.
Nếu một đối số mảng hoặc tham chiếu có chứa văn bản hoặc ô trống thì những đối số đó sẽ được bỏ qua.
Nếu dải ô được chỉ định không chứa giá trị logic thì hàm AND trả về lỗi #VALUE!.
Ví dụ về hàm AND trong Excel Ví dụ hàm IF kết hợp hàm ANDCũng trong bảng dữ liệu như ví dụ trên, chúng ta sẽ so sánh kết quả bán hàng bằng cách kết hợp hàm IF với hàm AND.
Download ví dụ về hàm ANDHàm Match Trong Excel Và Tổng Hợp Các Ví Dụ Hàm Match Trong Excel
Trong bài này, Blog Học Excel Online sẽ cùng các bạn tìm hiểu hàm Match ở trong Excel và chỉ ra cách các bạn có thể kết hợp hàm Match và Vlookup để tạo ra những công thức linh hoạt và hiệu quả hơn.
Trong Excel, có rất nhiều hàm giúp bạn có thể tìm kiếm một giá trị trong 1 mảng, hàm Match là một trong những hàm đó. Nói một cách đơn giản, hàm Match sẽ trả lại kết quả là vị trí tương đối của giá trị cần tìm trong 1 vùng. Tuy nhiên, hàm Match còn có thể làm nhiều hơn như vậy.
Hướng dẫn hàm Match – cú pháp và cách sử dụng
Làm thế nào để sử dụng hàm Match trong Excel
Sử dụng hàm match có phần biệt chữ hoa, chữ thường
So sánh 2 cột, tìm sự khác biệt
Kết hợp Vlookup và Match
Hướng dẫn hàm Match – cú pháp và cách sử dụngCú pháp của hàm Match như sau:
[ 1 trong 3 số : 1 hoặc 0 hoặc -1] (Tham số không bắt buộc): định nghĩa cách tìm kiếm
Nếu bạn điền số 1 hoặc bỏ trống: hàm Match sẽ tìm giá trị lớn nhất trong vùng tìm kiếm nhưng nhỏ hơn hoặc bằng giá trị đang được tìm kiếm. Chú ý: vùng tìm kiếm cần được sắp xếp theo chiều tăng dần, nghĩa là, từ giá trị nhỏ nhất đến giá trị lớn nhất hoặc từ A tới Z
Nếu bạn điền số 0: hàm Match sẽ tìm chính xác giá trị cần tìm trong vùng và trả lại vị trí đầu tiên mà nó tìm thấy
Nếu bạn điền số -1: hàm Match sẽ tìm giá trị nhỏ nhất trong vùng tìm kiếm nhưng lơn hơn hoặc bằng giá trị đang được tìm kiếm. Chú ý: vùng tìm kiếm cần được sắp xếp theo thứ tự giảm dần, nghĩa là, từ giá trị lớn nhất đến giá trị nhỏ nhất hoặc từ Z tới A
Một số lưu ý khi sử dụng hàm Match:
Hàm Match trả về vị trí tương đối của giá trị cần tìm, không trả về giá trị đó
Hàm Match không phân biệt chữ hoa, chữ thường khi làm việc với dữ liệu chữ
Nếu vùng tìm kiếm có một vài giá trị trùng nhau, hàm Match sẽ trả về giá trị đầu tiên mà nó gặp
Nếu giá trị cần tìm không có trong vùng tìm kiếm, lỗi #N/A sẽ được trả về.
Làm thế nào để sử dụng hàm Match trong ExcelỞ phần trước, các bạn đã học cách sử dụng hàm Match một cách đơn giản. Giống như một số hàm khác, hàm Match của chúng ta cũng chơi với các kí tự đại diện:
Dấu hỏi chấm (?) – đại diện cho 1 kí tự bất kì
Dấu sao (*) – đại diện cho bất kì số lượng kí tự nào. (Chỉ sử dụng với Match khi tham số thứ 3 của hàm Match có giá trị bằng 0)
Chúng ta có ví dụ sau: Tìm vị trí tương đối của tên người bắt đầu bằng “Car”
=MATCH( E1 & “*” , A2:A11 , 0 )
Một ví dụ nữa với kí tự đại diện dấu hỏi chấm (?) với dữ liệu ở bảng phía trên:
=MATCH( “ba?er” , A2:A11 , 0 )
Ta được kết quả là 5, là vị trí tương đối của tên “Baker”
Sử dụng hàm Match có phân biệt chữ hoa, chữ thườngNhư đã nói ở phần trước, hàm Match khi tìm kiếm sẽ không phân biệt được chữ hoa và chữ thường. Để giúp hàm Match phân biệt chữ hoa, chữ thường, ta sẽ kết hợp hàm Match với hàm EXACT như sau:
Hàm EXACT sẽ giúp chúng ta so sánh giá trị cần tìm kiếm với mỗi giá trị trong vùng tìm kiếm. Nếu ô được so sánh trùng khít về nội dung 100% với ô đem đi so sánh thì hàm EXACT sẽ trả về giá trị TRUE, nếu không hàm EXACT sẽ trả về giá trị FALSE
Sau đó hàm Match sẽ tìm kiếm giá trị TRUE được trả về từ hàm EXACT này, kết quả là chúng ta có thể sử dụng hàm MATCH có phân biệt được chữ hoa và chữ thường.
Chú ý: đây là 1 công thức mảng, sau khi nhập xong công thức, bạn sử dụng phím tắt CTRL + SHIFT + ENTER để nhập, bạn không cần thêm dấu ngoặc nhọn vào công thức.
So sánh 2 cột tìm sự khác biệtMột trong những công việc mất rất nhiều thời gian đó là so sánh 2 cột trong Excel để biết thành phần nào ở cột 1 chưa có trong cột 2 hoặc ngược lại. Có nhiều cách để giải quyết vấn đề này, trong đó có cách sử dụng Match kết hợp với hàm ISNA:
Hàm Match sẽ tìm từng giá trị ở cột 1 trong cột 2, nếu giá trị này được tìm thấy, hàm Match sẽ trả về vị trí tương đối của giá trị đó, nếu không tìm thấy, lỗi #N/A sẽ được trả về.
Hàm ISNA kiểm tra xem kết quả trả về từ hàm Match có phải là #N/A hay không. Nếu hàm ISNA trả về giá trị đúng, nghĩa là giá trị không được tìm thấy, điều này dẫn đến việc tham số đầu tiên của hàm IF có giá trị TRUE, do vậy kết quả của hàm IF sẽ là “Không có trong cột 1”
Để minh hoạ cho đoạn giải thích rất là dài phía trên, không có gì tốt hơn 1 ví dụ:
Kết hợp Vlookup và MatchĐến đây, mình tin là bạn đã biết sử dụng hàm vlookup rồi, nếu chưa, bạn có thể bấm vào chữ Vlookup để tìm hiểu về hàm này trước khi tiếp tục.
Một vấn đề rất hay gặp phải của hàm Vlookup đó là: Lỗi khi bảng tra cứu thay đổi cấu trúc, cách giải quyết đầu tiên là tổ chức dữ liệu tốt hơn trong Excel để tránh lỗi này. Trong bài này, chúng ta sẽ đi tìm hiểu 1 kĩ thuật nữa để hạn chế được lỗi này.
Chúng ta có 1 ví dụ về hàm VLookup như sau:
Học Excel tại nhà với các chuyên gia hàng đầu tại Việt Nam
Các Hàm Trong Excel Và Các Ví Dụ Minh Họa
Excel cung cấp một số lượng lớn các chức năng để phân tích, kiểm toán và tính toán dữ liệu. Một trong số những chức năng này được sử dụng bởi nhiều người sử dụng Excel hàng ngày và cũng có một số người chỉ sử dụng những phép toán này trong trường hợp cụ thể.
Trong hướng dẫn này, chúng ta sẽ không khám phá từng chức năng trong Microsoft Excel mà chỉ đưa ra cho bạn cái nhìn tổng quan về những chức năng hữu ích trong Excel được sử dụng nhiều nhất.
Chức năng văn bản trong Excel (các chức năng chuỗi)TEXT(value, format_text) được sử dụng để chuyển đổi một số hoặc một ngày thành một chuỗi kiểm tra ở định dạng được chỉ định, trong đó:
value là một giá trị số bạn muốn chuyển đổi sang văn bản. Format_text là định dạng mong muốn. Các công thức sau đây thể hiện hoạt động của chức năng Excel TEXT:
=TEXT(A1,”mm/dd/yyyy”) – chuyển ngày tháng trong ô A1 sang một chuỗi văn bản theo định dạng ngày truyền thống của Hoa Kỳ, chẳng hạn như “01/01/2023” (tháng / ngày / năm).
SUBSTITUTE(text, old_text, new_text, [instance_num]) sẽ thay thế một bộ ký tự bằng một ký tự khác trong một ô được chỉ định hoặc một chuỗi văn bản. Cú pháp của hàm SUBSTITUTE như sau:
Text – chuỗi văn bản ban đầu hoặc tham chiếu đến ô mà bạn muốn thay thế một số ký tự nhất định. Old_text – các ký tự bạn muốn thay thế. New_text – các ký tự mà bạn mới bạn muốn dùng để thế chỗ văn bản cũ. Nth_appearance – một tham số tùy chọn cho old_text mà bạn muốn thay thế bằng new_text. Sau đó, mỗi lần xuất hiện của văn bản cũ sẽ được thay thế bằng văn bản mới. Ví dụ: công thức SUBSTITUTE sau thay thế tất cả các dấu phẩy trong ô A1 bằng dấu chấm phẩy:
VALUE(text) – chuyển đổi một chuỗi văn bản thành một số.
Hàm này thực sự hữu ích khi chuyển đổi các giá trị định dạng văn bản đại diện cho các con số thành các con số có thể được sử dụng trong các công thức Excel khác và tính toán.
Chuyển đổi chữ số định dạng văn bản thành số Hàm VALUE dùng để chuyển đổi văn bản thành ngày Hàm EXACT EXACT(text1, text2) so sánh hai chuỗi văn bản và trả về giá trị TRUE nếu cả hai giá trị đều giống nhau, và trả về FALSE nếu không giống nhau.
Ví dụ: nếu A2 là “apples” và B2 là “Apples”, công thức =EXACT(A2, B2) sẽ trả về FALSE vì chúng không khớp chính xác.
Hàm EXACT hiếm khi được sử dụng riêng, nhưng lại hữu ích khi kết hợp với những hàm khác để tạo nên tác vụ phức tạp hơn, ví dụ như kết hợp với Vlookup dùng thao tác với văn bản trong Excel.
Các hàm sử dụng để chuyển đổi văn bản (UPPER, LOWER, PROPER)Microsoft Excel cung cấp 3 hàm văn bản để chuyển đổi giữa UPPER, LOWER, PROPER.
UPPER(text) – chuyển đổi tất cả các ký tự trong một chuỗi ký tự được chỉ định sang chữ hoa.
LOWER(text) – thay đổi tất cả các chữ in hoa trong một chuỗi văn bản thành chữ thường.
PROPER(text) – viết hoa chữ cái đầu tiên của mỗi từ và chuyển đổi tất cả các chữ cái khác sang chữ thường.
Nếu bạn cần một công thức để trả về một số ký tự nhất định từ một chuỗi văn bản, sử dụng một trong các hàm Excel sau.
LEFT(text, [num_chars]) – trả về một số ký tự nhất định bắt đầu từ đầu chuỗi văn bản.
RIGHT(text,[num_chars]) – trả về một số ký tự được chỉ định bắt đầu từ cuối chuỗi văn bản.
MID(text, start_num, num_chars) – trả về một số ký tự nhất định từ một chuỗi văn bản, bắt đầu ở bất kỳ vị trí nào bạn chỉ định.
Trong các hàm này, bạn cung cấp các đối số sau:
Microsoft Excel cung cấp một số ít các chức năng logic đánh giá một điều kiện xác định và trả về giá trị tương ứng.
OR(logical1, [logical2], …) – trả về TRUE nếu ít nhất một trong các đối số là TRUE.
NOT(logical) – đảo ngược giá trị đối số của nó, nghĩa là nếu đánh giá lôgic thành FALSE, hàm NOT sẽ trả về TRUE và ngược lại.
Ví dụ, cả hai công thức sau đây sẽ trả về FALSE:
Hàm IF trong Excel đôi khi được gọi là “hàm điều kiện” vì nó trả về một giá trị dựa trên điều kiện mà bạn chỉ định. Cú pháp của hàm IF như sau:
Và đây là một ví dụ về công thức hàm IF kết hợp thể hiện điểm thi trong ô A2:
Cả hai hàm đều được sử dụng để kiểm tra lỗi trong một công thức tính nhất định, và nếu có lỗi xảy ra, các hàm MS Excel trả lại một giá trị được chỉ định thay thế.
IFERROR(value, value_if_error) – kiểm tra lỗi của công thức hoặc biểu thức đánh giá. Nếu có lỗi, công thức trả về giá trị được cung cấp trong đối số value_if_error, nếu không, kết quả của công thức sẽ được trả về. Hàm này xử lý tất cả các lỗi Excel có thể xảy ra, bao gồm VALUE, N/A, NAME, REF, NUM và những lỗi khác. Nó có sẵn trong Excel 2007 và những phiên bản cao hơn.
IFNA(value, value_if_na) – được giới thiệu trong Excel 2013, nó hoạt động tương tự như IFERROR, nhưng chỉ xử lý lỗi #N/A.
Các công thức toán học trong ExcelSUM(number1,[number2],…) trả về tổng các đối số của nó. Các đối số có thể là các số, các tham chiếu ô hoặc các giá trị số bằng công thức.
Ví dụ, công thức toán học đơn giản =SUM(A1:A3, 1) cộng các giá trị trong ô A1, A2 và A3, và thêm 1 vào kết quả. Các hàm SUMIF và SUMIFS (tổng điều kiện) Cả hai hàm này dùng để cộng các ô trong một phạm vi xác định đáp ứng một điều kiện nhất định. Sự khác biệt là SUMIF có thể đánh giá chỉ một tiêu chuẩn, trong khi SUMIFS, được giới thiệu trong Excel 2007, cho phép sử dụng nhiều tiêu chí. Hãy lưu ý rằng thứ tự của các đối số là khác nhau trong mỗi chức năng:
SUMPRODUCT(array1,array2, …) là một trong số ít các chức năng của Microsoft Excel dùng để xử lý mảng. Nó kết hợp với các thành phần mảng được cung cấp và trả về tổng sản phẩm.
Bản chất của hàm SUMPRODUCT có thể khó nắm bắt vì vậy các bạn có thể theo dõi các ví dụ cụ thể của hàm này trong bài viết dành riêng cho hàm SUMPRODUCT để có được hiểu biết đầy đủ.
RAND() – trả về một số thực (số thập phân) ngẫu nhiên giữa 0 và 1.
RANDBETWEEN(bottom, top) – trả về một số nguyên ngẫu nhiên giữa số dưới cùng và số trên cùng mà bạn chỉ định.
Có một số chức năng để làm tròn số trong Excel, và Hướng dẫn làm tròn của Excel của chúng tôi đã giúp bạn giải thích cách sử dụng những chức năng đó dựa trên tiêu chí của bạn. Vui lòng nhấp vào tên của chức năng để tìm hiểu cú pháp và ví dụ về sử dụng.
ROUND – làm tròn số đến số chữ số được chỉ định.
ROUNDUP – làm tròn lên, đến số ký tự được chỉ định.
ROUNDDOWN – làm tròn xuống, đến số ký tự được chỉ định.
MROUND – làm tròn số đến một bội số được chỉ định.
FLOOR – làm tròn số xuống, đến bội số được chỉ định.
CEILING – tròn số lên, đến bội số được chỉ định.
INT – làm tròn số xuống số nguyên gần nhất.
TRUNC – cắt bớt số số đến số nguyên gần nhất.
EVEN – làm tròn số đến số nguyên chẵn gần nhất.
ODD – làm tròn số lên đến số nguyên lẻ gần nhất.
Trả về số dư sau khi chia (hàm MOD)MOD(number, divisor) trả về số sư sau khi chia.
Hàm này thực sự rất hữu ích trong nhiều trường hợp khác nhau, ví dụ như:
Các hàm thống kê trong ExcelTrong số nhiều hàm thống kê của Excel, có một số hàm có thể áp dụng để sử dụng cho những công việc mang tính chuyên nghiệp cao.
Tìm các giá trị lớn nhất, nhỏ nhất và trung bìnhMIN(number1, [number2], …) – trả về giá trị nhỏ nhất từ danh sách các đối số.
MAX(number1, [number2], …) – trả về giá trị lớn nhất từ danh sách các đối số
AVERAGE(number1, [number2], …) – trả giá trị trung bình của các đối số.
SMALL(array, k) – trả về giá trị nhỏ nhất thứ k trong mảng.
LARGE (mảng, k) – trả về giá trị lớn nhất thứ k trong mảng.
COUNT(value1, [value2], …) – trả về số lượng các giá trị số (số và ngày) trong danh sách các đối số.
COUNTA(value1, [value2], …) – trả về số ô có chứa dữ liệu trong danh sách các đối số. Nó đếm các ô có chứa bất kỳ thông tin, bao gồm các giá trị lỗi và các chuỗi văn bản rỗng (“”) được trả về bởi các công thức khác.
COUNTBLANK(range) – đếm số ô trống trong một phạm vi xác định. Các ô có chuỗi văn bản trống (“”) cũng được tính là các ô trống.
COUNTIF(range, criteria) – tính số ô trong phạm vi đáp ứng các tiêu chí được chỉ định.
COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…) – đếm số ô đáp ứng được tất cả các tiêu chí được chỉ định.
Các hàm dạng này của MS Excel có ích khi bạn cần tìm thông tin nhất định trong một bảng dựa trên một giá trị trong một cột hoặc trả về một tham chiếu đến một ô nhất định.
Hàm VLOOKUP tìm một giá trị được chỉ định trong cột đầu tiên và kéo dữ liệu phù hợp từ cùng một hàng trong một cột khác.
VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup]) Lookup_value – giá trị cần tìm kiếm. Table_array – hai hoặc nhiều cột dữ liệu. Col_index_num – số cột để kéo dữ liệu. Range_lookup – xác định tìm kiếm là hoàn toàn chính xác (FALSE) hay chỉ tương đối chính xác (TRUE hoặc bỏ qua). Ví dụ: công thức =VLOOKUP(“apples”, A2:C10, 3) tìm kiếm “apples” trong ô A2 đến A10 và trả về giá trị phù hợp từ cột C:
INDEX(array, row_num, [column_num]) – trả về một tham chiếu đến một ô bên trong mảng dựa trên số hàng và cột mà bạn chỉ định.
Đây là một công thức INDEX đơn giản: =INDEX(A1:C10, 3 ,4) hàm này tìm trong ô A1 đến C10 và trả về giá trị tại giao điểm của hàng thứ 3 và cột 4, đó là ô D3.
MATCH(lookup_value, lookup_array, [match_type]) – tìm kiếm lookup_value trong lookup_array, và sau đó trả về vị trí tương đối của mục đó trong dãy.
Sự kết hợp giữa các hàm MATCH và INDEX có thể được sử dụng như là một thay thế mạnh mẽ và linh hoạt hơn cho Vlookup của Excel.
INDIRECT(ref_text, [a1]) – trả về một tham chiếu ô hoặc dải ô được chỉ định bởi một chuỗi văn bản.
OFFSET(reference, rows, cols, [height], [width]) – trả về một tham chiếu đến một dãy ô được tính từ một ô bắt đầu hoặc một dãy các ô theo số hàng và cột được chỉ định.
Ví dụ, =OFFSET(A1, 1, 2) trả về giá trị trong ô C2 vì nó là 1 hàng xuống và 2 cột bên trái tính từ A1.
TRANSPOSE(array) – biến đổi một khoảng nằm ngang của các ô thành một dải đứng và ngược lại, tức là chuyển hàng thành các cột và cột thành hàng.
HYPERLINK(link_location, [friendly_name]) – Tạo một siêu liên kết đến một tài liệu được lưu trữ trên mạng nội bộ hoặc Internet.
Các hàm tài chính trong ExcelFV(rate, nper ,pmt ,[pv], [type]) – tính giá trị tương lai của một khoản đầu tư dựa trên lãi suất cố định.
Hàm ngày tháng trong ExcelĐối với những người sử dụng Excel thường xuyên, các hàm ngày tháng ngày càng trở nên quen thuộc hơn và được sử dụng trong rất nhiều trường hợp khác nhau.
Trích ra ngày tháng và các thành phần ngày thángDAY – trả về ngày trong tháng. MONTH – trả về tháng của một ngày được chỉ định. YEAR – trả về năm của một ngày nhất định. EOMONTH – trả về ngày cuối cùng của tháng. WEEKDAY – trả về ngày trong tuần. WEEKNUM – trả về số tuần của một ngày.
DATEDIF – trả về sự khác biệt giữa hai ngày. EDATE – trả về một ngày nằm trong tháng đã định trước, có thể đứng trước hoặc sau ngày bắt đầu. YEARFRAC – trả về tỷ lệ của một khoảng thời gian trong một năm.
WORKDAY – Trả về một số tuần tự thể hiện số ngày làm việc, có thể là trước hay sau ngày bắt đầu làm việc và trừ đi những ngày cuối tuần và ngày nghỉ (nếu có) trong khoảng thời gian đó. chúng tôi – tính ngày tháng là số ngày trong tuần được chỉ định trước hoặc sau ngày bắt đầu, với thông số cuối tuần tùy chỉnh. NETWORKDAYS – trả về số ngày làm việc giữa hai ngày được chỉ định. chúng tôi – trả về số ngày làm việc giữa hai ngày được chỉ định với ngày cuối tuần tùy chỉnh.
Các hàm thời gian trong ExcelTIME(hour, minute, second) – trả về thời gian dưới dạng số sê ri.
TIMEVALUE(time_text) – chuyển đổi một thời gian nhập dưới dạng một chuỗi văn bản thành một số thể hiện thời gian dưới dạng số sê ri.
NOW() – trả về số tương ứng với ngày và thời gian hiện tại dạng số sê ri.
HOUR(serial_number) – chuyển đổi một số thành một giờ dạng số sê ri.
MINUTE(serial_number) – chuyển đổi một số thành phút dưới dạng số sê ri.
SECOND(serial_number) – chuyển đổi một số thành giây dưới dạng số sê ri.
Đếm và tính tổng các ô theo màu sắc (chức năng do người dùng xác định)GetCellColor(cell) – trả về mã màu của màu nền thuộc một ô xác định. GetCellFontColor(cell) – trả về mã màu của màu phông chữ thuộc một ô xác định. CountCellsByColor(range, color code) – đếm các ô có màu nền được chỉ định. CountCellsByFontColor(range, color code) – đếm các ô có màu phông chữ được chỉ định. SumCellsByColor(range, color code) – tính tổng của các ô có màu nền nhất định. SumCellsByFontColor(range, color code)) – trả về tổng của các ô với một màu chữ nhất định. WbkCountCellsByColor(cell) – tính các ô có màu nền được chỉ định trong toàn bộ bảng tính. WbkSumCellsByColor(cell) – tính tổng các ô với màu nền được chỉ định trong toàn bộ bảng tính.
Tất nhiên, Microsoft Excel có nhiều hàm hơn những hàm được liệt kê ở đây rất nhiều, tuy nhiên đây là những hàm vô cùng thiết yếu và phục vụ nhiều cho công việc của bạn.
Để 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…
Cập nhật thông tin chi tiết về Các Ví Dụ Về Hàm Sum Trong Excel trên website Phauthuatthankinh.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!