Xu Hướng 6/2023 # Hướng Dẫn Cách Dùng Hàm Sumifs Đơn Giản Qua Bài Tập # Top 6 View

Bạn đang xem bài viết Hướng Dẫn Cách Dùng Hàm Sumifs Đơn Giản Qua Bài Tập được cập nhật mới nhất trên website Hoisinhvienqnam.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.

Mô tả hàm SUMIFS

Hàm SUMIFS() là hàm tính tổng các ô trong vùng, phạm vi lựa chọn thỏa mãn với nhiều điều kiện khác nhau.

Cú pháp của hàm SUMIFS

=SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2,…)

Trong đó:

– sum_range: bắt buộc Là các ô cần tính tổng bao gồm giá trị số. Các ô chứa giá trị trống, giá trị là chữ, văn bản sẽ được bỏ qua.

– criteria_range1: bắt buộc Là phạm vi cần tính được kiểm tra điều kiện criteria1.

– criteria1: bắt buộc Điều kiện dưới dạng một số, biểu thức, tham chiếu ô được áp dụng cho criteria_range1.

– criteria_range2, criteria2, … : tùy chọn Là các phạm vi và điều kiện bổ sung. Hàm SUMIFS cho phép tối đa 127 cặp criteria_range, criteria.

Những điều cần chú ý khi dùng hàm SUMIFS

Các criteria_range phải có cùng kích thước vùng chọn giống với đối số sum_range, tức là criteria_range và sum_range phải có số hàng và số cột giống nhau.

Các ô trong sum_range nếu có giá trị là TRUE sẽ được định nghĩa giá trị là 1 và nếu ô có giá trị là FALSE sẽ được định nghĩa giá trị là 0 (không).

Điều kiện criteria có thể chứa các ký tự đại diện như sau: dấu hỏi chấm (?) sẽ thay cho ký tự đơn, dấu sao (*) sẽ thay cho một chuỗi. Nếu trong điều kiện criteria muốn đặt là dấu hỏi chấm (?) hoặc dấu sao (*) thì hãy nhập thêm dấu ~ ở trước.

Nếu các giá trị của điều kiện (criteria) ở dạng văn bản thì cần để trong dấu ngoặc kép ” “.

Ví dụ về hàm SUMIFS trong Excel số 1

Để nắm rõ hơn về cách dùng hàm SUMIFS trong Excel chúng ta sẽ cùng thực hiện ví dụ thực tế như bên dưới. Chúng ta có bảng Excel như sau:

Yêu cầu tính toán

Yêu cầu tính toán số 1: Tính tổng số lượng sản phẩm từ nhà cung cấp Adayroi với giá bán trên 6.000.000đ

Yêu cầu tính toán số 2. Tính tổng số lượng sản phẩm từ nhà cung cấp Lazada trừ sản phẩm Samsung S8

Giải yêu cầu số 1

Vùng cần tính tổng là cột Số lượng: D4:D13

Phạm vi cần kiểm tra điều kiện 1 là cột Nhà cung cấp: B4:B13

Điều kiện 1 là nhà cung cấp Adayroi ở dạng văn bản: “Adayroi”

Phạm vi cần kiểm tra điều kiện 2 là cột Giá bán: E4:E13

Đây là kết quả:

Giải yêu cầu 2

Vùng cần tính tổng là cột Số lượng: D4:D13

Phạm vi cần kiểm tra điều kiện 1 là cột Nhà cung cấp: B4:B13

Điều kiện 1 là nhà cung cấp Lazada ở dạng văn bản: “Lazada”;

Phạm vi cần kiểm tra điều kiện 2 là cột Tên sản phẩm: C4:C13

Đây là kết quả

Ví dụ về hàm SUMIFS trong Excel số 2

Cho bảng dữ liệu sau đây:

Yêu cầu 1. Tính tổng số lượng sản phẩm đã được bán bởi nhân viên tên Nga với STT < 5.

Yêu cầu 2. Tính tổng số lượng sản phẩm đã được bán bởi nhân viên tên Hương với tất cả các sản phẩm trừ “Na”.

Tương tự cách sử dụng hàm SUMIFS ở ví dụ 1,Kết quả sau khi áp dụng hàm SUMIFS() ở ví dụ này như sau:

Với sứ mệnh: ” Mang cơ hội phát triển kỹ năng, phát triển nghề nghiệp tới hàng triệu người “, đội ngũ phát triển đã và đang làm việc với những học viện, trung tâm đào tạo, các chuyên gia đầu ngành để nghiên cứu và xây dựng lên các chương trình đào tạo từ cơ bản đến chuyên sâu xung quanh các lĩnh vực: Tin học văn phòng, Phân tích dữ liệu, Thiết kế, Công nghệ thông tin, Kinh doanh, Marketing, Quản lý dự án…

Gitiho tự hào khi được đồng hành cùng:

50+ khách hàng doanh nghiệp lớn trong nhiều lĩnh vực như: Vietinbank, Vietcombank, BIDV, VP Bank, TH True Milk, VNPT, FPT Software, Samsung SDIV, Ajinomoto Việt Nam, Messer,…

Cách Dùng Hàm Sum, Sumif, Sumifs Qua Ví Dụ Hay Nhất

Có một điều chắc chắn rằng, hàm SUM, SUMIF, SUMIFS là những hàm tính tổng trong Excel được sử dụng rất phổ biến trong học tập, công việc. Đây đều là các hàm cơ bản trong Excel. Do vậy, việc hiểu được công thức, cách dùng của những hàm này sẽ rất có ích cho bạn sau này. Nếu bạn mới làm quen với phần mềm Excel, hoặc đang tìm hiểu về các hàm Excel cơ bản, thì đây là một bài viết rất hữu ích với bạn.

Trong bài viết này, Đỗ Bảo Nam Blog chia sẻ chi tiết với bạn về công thức, cách sử dụng của những hàm này qua ví dụ minh họa cực hay và dễ hiểu. File bài tập thực hành bạn có thể download ở phía cuối bài viết này. Những ví dụ về hàm SUMIF, hàm SUMIFS và hàm SUM được Đỗ Bảo Nam Blog thiết kế rất khoa học, dễ hiểu. Các ví dụ này được thiết kế dựa trên bảng thống kê bán hàng (cơ bản). Đây là dạng bảng bạn sẽ gặp thường xuyên trong quá trình học tập và làm việc.

Video hướng dẫn cách dùng hàm SUM, SUMIF, SUMIFS qua ví dụ Video rất hay chia sẻ về các hàm tính tổng trong Excel. File bài tập về các hàm này bạn có thể download ở phía dưới bài viết. Ví dụ về cách sử dụng hàm SUMIF, SUMIFS và hàm SUM Cách dùng hàm SUM trong Excel

Hàm SUM là một trong những hàm cơ bản nhất trong Excel. Đây là hàm toán học dùng để tính tổng trong Excel. Bạn có thể dùng hàm này khi muốn tính tổng của các số, dãy số khi cần thiết…

Công thức hàm SUM trong ExcelCông thức: SUM(số thứ nhất, [số thứ hai],…)Trong đó, số thứ nhất, số thứ hai… là các giá trị cần tính tổng. Và chắc chắn khi đã dùng hàm, thì đối số thứ nhất, tức là số thứ nhất phải là bắt buộc. Các giá trị khác của hàm này là tùy chọn. Và khi dùng hàm SUM, bạn chỉ cần xác định tính tổng của những số nào hay dãy số nào.

Tất cả các hàm gắn với chữ “if” là đều có điều kiện kèm theo. Và hàm SUMIF cũng vậy. Nói một cách dễ hiểu nhất, hàm SUMIF là hàm tính tổng có điều kiện trong Excel. Hàm này được dùng khi bạn muốn tính tổng của các số khi thỏa mãn một điều kiện nào đó.

Hàm cuối cùng trong bài viết này mà Đỗ Bảo Nam Blog chia sẻ là hàm SUMIFS. Khi gắn với chữ “S” ở đằng sau, thì hàm này trở thành hàm tính tổng có nhiều điều kiện. Về cơ bản, đây là hàm tính tổng có điều kiện, và bạn có thể sử dụng nhiều điều kiện trong hàm này. Và trong thực tế, hàm SUMIFS trong Excel cũng được sử dụng rất rộng rãi trong thực tế. Vì vậy ngay bây giờ, bạn hãy trang bị cho mình những kiến thức về các hàm tính tổng để sử dụng trong công việc, học tập.

Công thức hàm SUMIFS trong Excel

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)Hay viết một cách dễ hiểu hơn, công thức của hàm tính tổng có điều kiện SUMIFS sẽ có dạng như sau:SUMIFS(vùng cần tính tổng, vùng điều kiện 1, điều kiện 1,[vùng điều kiện 2, điều kiện 2], …)Trong công thức này, vùng cần tính tổng là vùng chứa các giá trị số cần để tính tổng. Vùng điều kiện 1 là vùng chứa điều kiện 1. Điều kiện 1 là điều kiện để tính tổng theo một tiêu chí nào đó. Vùng điều kiện 2, điều kiện 2 (tùy chọn) là các phạm vi bổ sung và các tiêu chí liên kết với chúng. Bạn có thể nhập tối đa 127 cặp phạm vi/tiêu chí.

Cách dùng hàm SUMIFS trong Excel qua ví dụ minh họa

Download bài tập hàm SUMIF, SUMIFS

Hướng Dẫn Cách Sử Dụng Hàm Npv Trong Excel Qua Bài Tập Ví Dụ

Ứng dụng hàm NPV tính giá trị hiện tại ròng

Ví dụ chúng ta có dự án X yêu cầu vốn đầu tư ban đầu là 100 đô la (ô B5). Tiếp theo chúng ta thực hiện các bước tính toán như sau:

Bước 1. Ta kỳ vọng lợi nhuận là 0 đô la cuối kỳ đầu tiên, cuối kỳ thứ 2 lợi nhuận là 50 đô la và cuối kỳ thứ 3 lợi nhuận là 150 đô la.

Bước 2. Lãi suất chiết khấu bằng 15%.

Giải thích: Đây là tỷ suất sinh lợi của khoản đầu tư thay thế tốt nhất. Ví dụ bạn cũng có thể gửi tiền tiết kiệm của mình với lãi suất cao 15%.

=NPV(B2,B6:B8)+B5

Giải thích:

Giá trị hiện tại ròng dương cho biết tỷ suất sinh lợi của dự án vượt qua lãi suất chiết khấu. Hay nói cách khác là tốt hơn bạn nên đầu tư tiền của mình vào dự án X hơn là bỏ tiền vào tài khoản tiết kiệm lãi suất cao 15%.

Giải thích: Giá trị hiện tại ròng của dự án Y cao hơn so với giá trị hiện tại ròng của dự án X. Do đó dự án Y là dự án đầu tư tốt hơn.

Giải thích ý nghĩa của hàm NPV trong Excel

Hàm NPV đơn thuần tính cho ta giá trị hiện tại ròng của một loạt các dòng tiền tương lai. Và nó không phải vạn năng.

Bước 1. Ví dụ như ta có dự án A yêu cầu vốn đầu tư là 100 đô la (ô B5). Ta kỳ vòng lợi nhuận khi hết kỳ đầu tiên là 0 đô la, lợi nhuận khi hết kỳ thứ hai là 0 đô la. Và lợi nhuận khi hết kỳ thứ ba là 152.09 đô la.

Giải thích: Giá trị hiện tại ròng bằng 0 cho biết dự án tạo ra tỷ suất sinh lời bằng lãi suất chiết khấu. Hay nói cách khác cả hai dự án, dù bạn đầu tư vào dự án A hay cho tiền vào tài khoản tiết kiệm lãi suất cao 15% đều mang lại lợi nhuận như nhau.

Bước 2. Ta có thể kiểm tra lại điều này. Ta viết 100 đô la vào ô trống đầu tiên. Khoản đầu tư của bạn sẽ có giá trị bao nhiêu sau 3 năm với lãi suất hàng năm là 15%. Đáp án là 152.09 đô la.

Bước 3. Hàm NPV đơn thuần tính giá trị hiện tại của một loại các dòng tiền trong tương lai.

Bước 4. Ta có thể kiểm tra kết quả. Đầu tiên ta tính giá trị hiện tại của mỗi dòng tiền (PV). Tiếp theo ta tính tổng tất cả chúng.

Giải thích:

152.09 đô la trong 3 năm tới có giá trị ở thời điểm hiện tại là 100 đô la.

50 đô la trong 2 năm tới có giá trị ở thời điểm hiện tại là 37.81 đô la.

Tất nhiên bạn có muốn lấy 159.55 đô la thay cho 100 đô la bây giờ. Do đó dự án B là một khoản đầu tư tốt.

Hướng dẫn cách sử dụng hàm PMT trong Excel Hướng dẫn ứng dụng hàm PV trong ExcelHướng dẫn cách dùng và ý nghĩa của hàm IRR trong Excel

50+ khách hàng doanh nghiệp lớn trong nhiều lĩnh vực như: Vietinbank, Vietcombank, BIDV, VP Bank, TH True Milk, VNPT, FPT Software, Samsung SDIV, Ajinomoto Việt Nam, Messer,…

Gitiho tự hào khi được đồng hành cùng:

Hướng Dẫn Giải Bài Tập Dùng Hàm Index Kết Hợp Hàm Match Có File Mẫu

Bạn có biết hàm INDEX và MATCH là những hàm tìm kiếm rất hiệu quả và hữu ích, tuy nhiên do phải kết hợp 2 hàm này nên nhiều người thấy khó. Để hiểu rõ hơn cách sử dụng hai hàm này, Gitiho sẽ hướng dẫn các bạn cách sử dụng hàm INDEX, hàm MATCH qua một số bài tập ứng dụng. Các bạn có thể tải file bài tập này ở cuối bài viết.

Cách dùng hàm MATCH để dò tìm trong Excel

Ví dụ chúng ta có bảng tính sau:

Trong bảng này chúng ta có thông tin về họ tên, số điện thoại, Email, khu vực làm việc của 6 nhân viên. Nếu muốn biết thông tin của nhân viên “Lê Thị Thủy” thì bạn phải làm thế nào? Chắc hẳn việc đầu tiên bạn cần xác định xem người đó nằm ở vị trí thứ mấy trong bảng tính này.

Khi đó bạn có thể sử dụng hàm MATCH như sau:

Cú pháp hàm MATCH gồm 3 phần:

lookup_value: giá trị làm căn cứ tìm kiếm (tìm theo cái gì); ở đây là tên Lê Thị Thủy

lookup_array: là nơi dò tìm (bạn sẽ tìm ở đâu); ở đây là cột Họ tên

match_type: (Tham số này không bắt buộc): phương pháp tìm kiếm (tìm đúng theo giá trị hay tìm theo khoảng giá trị); ở đây tìm chính xác theo tên.

Do đó chúng ta có thể viết hàm MATCH như sau:

=MATCH(Tên cần tìm, cột Họ tên, tìm chính xác theo tên)

Tại ô D12 nhập tên Lê Thị Thủy

cột Họ tên là vùng C3:C8

Như vậy hàm MATCH cho kết quả là số 3, tương ứng với dòng thứ 3 trong bảng dữ liệu trên.

Hàm INDEX kết hợp với MATCH để tìm kiếm trong Excel

Khi bạn đã có vị trí là dòng thứ 3 trong bảng, bạn có thể lấy được bất kỳ thông tin nào ở dòng này nếu xác định thêm được cột chứa nội dung cần tìm. Khi đó bạn có thể kết hợp hàm INDEX với MATCH theo cách:

Hàm INDEX chỉ định cột chứa kết quả cần tìm

Hàm MATCH chỉ định dòng cần tìm

Khi đó giao điểm giữa cột và dòng này chính là vị trí kết quả cần tìm

Tìm Email của nhân viên Lê Thị Thủy

Trong công thức tại ô D15 tìm Email, chúng ta thấy:

=INDEX(E3:E8,MATCH(D12,C3:C8,0))

Công thức này, hàm INDEX có tác dụng:

Chỉ ra cột chứa giá trị cần tìm là cột E, vùng E3:E8

Chỉ ra dòng chứa giá trị cần tìm, dòng này đã xác định bởi hàm MATCH trong ví dụ trước.

Kết quả là giao điểm của cột E với dòng 3, cho ra vị trí ô E3 là ô chứa giá trị cần tìm.

Phương pháp tìm kiếm từ phải qua trái với hàm INDEX+MATCH

Thông thường để tìm kiếm trong Excel chúng ta thường dùng hàm VLOOKUP. Nhưng hàm này có nhược điểm là không thể tìm được theo chiều từ phải qua trái. Nhưng bạn có thể thực hiện điều này rất dễ dàng với hàm INDEX kết hợp hàm MATCH. Hãy tìm hiểu ví dụ sau: Tìm tên dựa vào thông tin Email, biết cột Email ở bên phải cột Họ tên.

Cách làm như sau:

Viết hàm MATCH để tìm vị trí dòng chứa thông tin Email

=MATCH(tên Email, vùng chứa Email, 0)

Kết hợp với hàm INDEX trong việc chỉ ra vị trí cột chứa kết quả cần tìm, là cột Họ tên (cột C), vùng C3:C8

=INDEX(C3:C8,MATCH(D12,E3:E8,0))

Như vậy về cách viết hàm không có gì thay đổi, nhưng phương pháp tìm kiếm có thể cho phép tìm từ phải qua trái, từ dưới lên trên một cách dễ dàng.

Bài tập tìm đơn giá của mặt hàng theo nhiều điều kiện

Trong trường hợp giá trị tìm kiếm của bạn thay đổi trên cả dòng và cột trong 1 bảng dữ liệu, bạn vẫn có thể sử dụng hàm INDEX MATCH để tìm kiếm được. Hãy xem ví dụ về tìm kiếm đơn giá của 1 sản phẩm khi thay đổi cả về mã sản phẩm và mã công ty:

Trong ví dụ này, chúng ta có Mã hàng và Mã công ty là các điều kiện cần tìm. Mã này có thể thay đổi:

Mã hàng thay đổi trong vùng B3:E3

Mã công ty có thể thay đổi trong vùng A4:A7

Với bất kỳ mã nào, bạn cũng phải xác định được đơn giá phù hợp.

Các bước thực hiện như sau:

Bước 1: Xác định vị trí Mã hàng nằm trên cột thứ mấy trong bảng

Bạn sẽ dùng hàm MATCH để tìm Mã hàng (ô H2) trong vùng chứa tên mã hàng (A3:E3) để xem mã hàng cần tìm nằm ở cột thứ mấy trong bảng

=MATCH(H2,A3:E3,0)

Chúng ta thấy hàm MATCH cho kết quả là số 2, tương ứng mã hàng này ở cột thứ 2 trong bảng.

Bước 2: Xác định vị trí Mã công ty nằm trên dòng thứ mấy trong bảng

Bạn sẽ dùng hàm MATCH để tìm Mã công ty (ô H3) trong vùng chứa tên công ty (A3:A7) để xem mã công ty cần tìm nằm ở dòng thứ mấy trong bảng:

=MATCH(H3,A3:A7,0)

Kết quả hàm MATCH trong trường hợp này cho là số 3, tương ứng với vị trí dòng thứ 3 trong bảng.

Như vậy bạn đã có cả 2 vị trí: số dòng và số cột trong bảng đơn giá, chỉ cần xác định giao điểm của dòng và cột này là có thể cho ra kết quả đơn giá cần tìm. Để làm việc này chúng ta sẽ sử dụng hàm INDEX.

Bước 3: Xác định kết quả đơn giá cần tìm với hàm INDEX

Khi viết hàm INDEX, bạn cần chỉ ra 3 vị trí:

Bảng cần tìm: bảng A3:E7

Dòng cần tìm: hàm MATCH tìm theo mã công ty

Cột cần tìm: hàm MATCH tìm theo mã hàng

Khi đó chúng ta có hàm INDEX như sau:

=INDEX(A3:E7,I3,I2)

Trong đó:

ô I3 là ô chứa kết quả hàm MATCH tìm theo mã công ty

ô I2 là ô chứa kết quả hàm MATCH tìm theo mã hàng

Nếu viết 1 cách tổng quát, chúng ta có:

=INDEX(A3:E7, MATCH(H3,A3:A7,0), MATCH(H2,A3:E3,0))

Trong trường hợp này chúng ta viết hàm INDEX gồm 3 tham số, khác với 2 ví dụ trước chỉ có 2 tham số. Bởi vì:

Khi vùng tìm kiếm của hàm INDEX chỉ có duy nhất 1 cột thì bạn không cần viết tham số thứ 3 (là cột cần tìm).

Khi vùng tìm kiếm của hàm INDEX có nhiều hơn 1 cột thì bạn bắt buộc phải viết tham số thứ 3 để chỉ có tìm kết quả ở cột nào.

Như vậy chúng ta đã có thể hình dung được cách sử dụng hàm INDEX kết hợp hàm MATCH để thực hiện các yêu cầu dò tìm, tham chiếu, tìm kiếm trong Excel thông qua 4 ví dụ tiêu biểu rồi. Một số kết luận có thể rút ra là:

Hàm MATCH dùng để tìm ra vị trí số dòng, số cột

Hàm INDEX có 2 cách viết: Viết rút gọn nếu chỉ xác định trên 1 cột (hoặc 1 hàng), viết đầy đủ nếu tìm trên 1 vùng gồm nhiều dòng, nhiều cột. Khi viết đầy đủ thì cần tới 2 hàm MATCH

Hàm INDEX+MATCH thay thế được cho hàm VLOOKUP, HLOOKUP trong mọi trường hợp tìm kiếm, và phương pháp tìm kiếm không bị hạn chế.

Những kiến thức bạn đang xem thuộc khóa học Excel từ cơ bản tới nâng cao của chúng tôi Khóa học này cung cấp cho bạn kiến thức một cách đầy đủ và có hệ thống về các hàm, các công cụ trong excel, ứng dụng excel trong công việc… Hiện nay hệ thống đang có nhiều ưu đãi khi bạn đăng ký tham gia khóa học này.

Vì sao dùng INDEX và MATCH tốt hơn dùng VLOOKUP trong Excel Phân biệt hàm VLOOKUP với HLOOKUP và bí quyết sử dụng hàm Hàm vlookup trong Excel và các ứng dụng nâng cao thường gặp

Tải về file mẫu trong bài viết

Bạ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

Gitiho tự hào khi được đồng hành cùng:

50+ khách hàng doanh nghiệp lớn trong nhiều lĩnh vực như: Vietinbank, Vietcombank, BIDV, VP Bank, TH True Milk, VNPT, FPT Software, Samsung SDIV, Ajinomoto Việt Nam, Messer,…

Cập nhật thông tin chi tiết về Hướng Dẫn Cách Dùng Hàm Sumifs Đơn Giản Qua Bài Tập trên website Hoisinhvienqnam.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!