Bạn đang xem bài viết Cấu Trúc Hàm Sumif Trong Excel đượ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.
1. Ý nghĩa của hàm sumif
Hàm sumif để tính tổng các giá trị thỏa mãn cùng điều kiện nào đó.
2. Công thức tính hàm sumif
Công thức: SUMIF(range, criteria,sum_range)
Range: Là vùng chứa ô điều kiện tính tổng (là cột chứa điều kiện) (Bạn có thể ấn F4 một lần để cố định vùng điều kiện)
Criteria: Là điều kiện tính tổng (là biểu thức, số hoặc chữ)
Sum_range: Vùng cần tính tổng. (ấn F4 một lần để cố định vùng cần tính tổng)
3. Các bước áp dụng hàm sumif
Bước 1: Xác định cột chứa điều kiện
Bước 2: Xác định điều kiện tính tổng
Bước 3: Xác định vùng tính tổng
Bước 4: Nhập công thức vào Excel
Ví dụ: Yêu cầu tính tổng lương phòng hành chính và tổng lương phòng kế toán như trong bảng sau:
Tại ô C9 (tổng lương phòng hành chính) bạn nhập: =sumif($B$4:$B$8,$B$4,$C$4:$C$8)
Tại ô C10 (Tổng lương phòng kế toán) bạn nhập: =sumif($B$4:$B$8,$B$5,$C$4:$C$8)
Ở đây: điều kiện tính tổng là “hành chính” hoặc “kế toán”,
Vùng chứa ô điều kiện là cột phòng ban (Từ B4 đến B8) ấn F4 một lần để cố định vùng
Vùng cần tính tổng là cột lương (từ C4 đến C8), ấn F4 một lần để cố định vùng. Học kế toán ở đâu tốt
Khi sử dụng công thức tính tổng có điều kiện Sumif trong excel, kèm theo các toán tử so sánh là một số hay chữ dạng text nhớ luân được đóng trong dấu ngoặc kép (“”)
Sử dụng hàm SUMIF với điều kiện thuộc dạng Text
Khi sử dụng hàm SUMIF bạn có thể thêm các giá trị tùy thuộc vào ô tương ứng trong các cột chứa text hoặc không
= SUMIF(A2:A8,”apple”, C2:C8) Tổng giá trị trong các ô C2:C8 nếu một ô tương ứng trong cột A chứa chính xác từ “apple” và không chứa thêm các ký tự khác. Các ô có chứa “red apple” hay “apple!” không được tính
Tags: Hàm sumif nâng cao, hàm tìm kiếm và tính tổng trong excel, hàm sumif và hàm sumifs, các hàm tính tổng trong excel, hàm sumif kết hợp vlookup, lỗi hàm sumif bằng 0, các hàm sum trong excel, hàm sumif 2 điều kiện,…
KẾ TOÁN LÊ ÁNH
Chuyên đào tạo các khóa học excel kế toán và làm dịch vụ kế toán thuế trọn gói tốt nhất thị trường
(Được giảng dạy và thực hiện bởi 100% các kế toán trưởng từ 13 năm đến 20 năm kinh nghiệm)
Trung tâm Lê Ánh hiện có đào tạo các khoá học kế toán và khoá học xuất nhập khẩu cho người mới bắt đầu và khoá học chuyên sâu, để biết thông tin chi tiết, bạn vui lòng liên hệ với chúng tôi theo số hotline: 0904.84.88.55 để được tư vấn trực tiếp về các khoá học này.
Hàm Date Trong Excel, Cách Sử Dụng, Cấu Trúc Hàm
Định nghĩa hàm Date, hàm ngày tháng trong Excel
Hàm DATE được tích hợp sẵn trên Excel cho phép người dùng tạo một ngày với đầy đủ các thành phần năm, tháng và ngày. Đặc biệt hàm DATE cực kỳ hữu ích trong trường hợp cung cấp ngày làm đầu vào cho các hàm khác như hàm SUMIFS hay hàm COUNTIFS, vì người dùng có thể dễ dàng tập hợp các giá trị năm, tháng và ngày từ một ô tham chiếu hoặc kết quả công thức.
Mục đích
Tạo các ngày hợp lệ từ năm, tháng và ngày.
Giá trị trả về
Trả về kết quả là giá trị số của một ngày cụ thể trong Excel.
Cú pháp
= DATE (năm, tháng, ngày)
Các tham số
Năm – Năm sử dụng khi tạo ngày.
Tháng – Tháng để sử dụng khi tạo ngày.
Ngày – Ngày để sử dụng khi tạo ngày.
Lưu ý
– Hàm DATE trong Excel trả về kết quả là giá trị số của một ngày cụ thể. Định dạng kết quả một ngày để hiển thị là một ngày (gồm năm, tháng, ngày).
– Nếu năm nằm trong khoảng giữa 0 và 1900, Excel sẽ thêm 1900 vào năm.
– Tháng có thể lớn hơn 12 và nhỏ hơn 0. Nếu tháng lớn hơn 12, Excel sẽ thêm tháng vào tháng đầu tiên trong năm. Nếu tháng nhỏ hơn hoặc bằng 0, Excel sẽ trừ đi giá trị tuyệt đối của tháng cộng thêm 1 (ví dụ ABS (tháng) + 1) tính từ tháng đầu tiên của năm.
– Ngày có thể tương đối hoặc tuyệt đối. Nếu ngày lớn hơn các ngày trong tháng cụ thể, Excel sẽ thêm ngày vào ngày đầu tiên của tháng được chỉ định. Nếu ngày nhỏ hơn hoặc bằng 0, Excel sẽ trừ giá trị tuyệt đối của ngày và cộng thêm 1 (ví dụ ABS (ngày) + 1) tính từ ngày đầu tiên của tháng được chỉ định.
Giải thích:
Nếu muốn đánh dấu các ngày lớn hơn hoặc nhỏ hơn một ngày cụ thể với định dạng có điều kiện, bạn có thể sử dụng công thức đơn giản dựa trên hàm DATE. Ví dụ, nếu bạn có ngày trong các ô B4: G11 và làm nổi bật các ô chứa ngày lớn hơn ngày 1/8/2015, chọn phạm vi và tạo quy tắc CF mới sử dụng công thức này:
Sau khi lưu rule, bạn sẽ nhìn thấy các ngày lớn hơn ngày 8/1/2015 được đánh dấu.
Ý nghĩa công thức:
Hàm DATE tạo một ngày hợp lệ trên Excel với các giá trị năm, tháng, và ngày. Sau đó, chỉ đơn giản là vấn đề so sánh mỗi ngày trong phạm vi với ngày được tạo bằng hàm DATE. Tham chiếu B4 hoàn toàn tương đối, vì vậy sẽ cập nhật theo rule được áp dụng cho mỗi ô trong vùng và bất kỳ ngày nào lớn hơn 8/1/2015 sẽ được đánh dấu.
So sánh lớn hơn hoặc bằng:
Bạn có thể sử dụng tất cả các toán tử tiêu chuẩn trong công thức này để điều chỉnh hành vi nếu cần. Ví dụ, để làm nổi bật tất cả các ngày lớn hơn hoặc bằng ngày 8/1/2015, sử dụng công thức:
Sử dụng một ô khác để làm đầu vào:
Không cần mã hóa ngày tháng vào rule. Để tạo ra một rule tương tác linh hoạt hơn, sử dụng một ô khác làm biến trong công thức. Ví dụ, nếu muốn sử dụng ô C2 làm ô đầu vào, đặt tên cho ô C2 “input”, nhập ngày và sử dụng công thức này:
Sau đó thay đổi ngày trong ô C2 thành bất kỳ thứ gì bạn muốn và rule định dạng có điều kiện sẽ phản hồi ngay lập tức.
– Ví dụ 2: Lấy ngày cuối cùng của tháng Công thức chung:
=EOMONTH(date,0)
Giải thích:
Cách dễ nhất để tính ngày cuối cùng của tháng là sử dụng hàm EOMONTH.
Trong ví dụ trên, công thức trong ô B5 là:
=EOMONTH(B5,0)
Ý nghĩa công thức:
Đối số thứ 2 (tháng) của hàm EOMONTH cho phép bạn lấy được ngày cuối cùng trong những tháng tiếp theo hoặc trong tháng trước. Khi bạn sử dụng số 0 trong nhiều tháng, EOMONTH sẽ trả lại kết quả vào ngày cuối cùng trong tháng đó.
Để lấy ngày cuối cùng của tháng trước, sử dụng công thức:
=EOMONTH(date,-1)
Để lấy ngày cuối cùng của tháng tiếp theo, sử dụng công thức:
=EOMONTH(date,1)
Giải pháp thay thế thông minh:
Nếu bạn là người thích xây dựng các công thức thông minh để phân loại, bạn cũng có thể sử dụng hàm DATE trong Excel để lấy ngày cuối cùng của tháng:
=DATE(YEAR(date),MONTH(date)+1,0)
Thủ thuật với công thức này là cung cấp số 0 làm đối số ngày. Khi bạn cung cấp số 0 làm đối số ngày trong hàm DATE, hàm date sẽ quay trở lại ngày đầu tiên của tháng trước cho đến ngày cuối cùng của tháng đó. Vì vậy, bằng cách cộng thêm 1 vào đối số tháng, và sử dụng 0 cho đối số ngày, hàm DATE cuộn lại cho ngày cuối cùng của tháng “ban đầu”.
– Ví dụ 3: Bôi đen các ngày có cùng tháng và năm Công thức chung:
=TEXT(A1,”myyyy”)=TEXT(date,”myyyy”)
Giải thích:
Nếu muốn sử dụng định dạng có điều kiện để đánh dấu các ngày cùng tháng cùng năm với nhau, bạn có thể sử dụng công thức đơn giản dựa trên hàm TEXT.
=TEXT(B4,”myyyy”)=TEXT(DATE(2015,6,1),”myyyy”)
Sau khi lưu rule, bạn sẽ nhìn thấy tất cả các ngày cùng tháng 6 năm 2015 được đánh dấu.
Ý nghĩa công thức:
Công thức này sử dụng hàm TEXT để ghép tháng và năm của mỗi ngày. Sau đó, 2 ngày được kiểm tra sự bình đẳng. TEXT là một hàm hữu ích cho phép bạn chuyển đổi một số thành văn bản ở định dạng văn bản mà bạn chọn. Trong trường hợp này định dạng là định dạng ngày tùy chỉnh “myyyy”, có nghĩa là đối số tháng không có số 0 ở đầu và đối số năm có 4 chữ số. Ví dụ, nếu A1 chứa ngày 9/6/2015, TEXT (A1, “myyyy”) sẽ tạo chuỗi văn bản “62016”.
Sử dụng các ô khác cho đầu vào:
Bạn không cần mã hoá một ngày vào rule. Để tạo rule linh hoạt hơn, bạn có thể sử dụng các ô khác làm biến. Ví dụ, nếu bạn đặt tên cho ô E2 là “date”, bạn có thể viết lại công thức như sau:
=TEXT(B4,”myyyy”)=TEXT(date,”myyyy”)
Và bất cứ khi nào bạn thay đổi ngày trong E2, rule định dạng có điều kiện sẽ cập nhật ngay lập tức. Điều này đơn giản hóa công thức và giúp bạn đọc dễ hơn.
Giải thích:
Để tính tổng các giá trị giữa 2 ngày, bạn có thể sử dụng hàm SUMIFS. Trong ví dụ trên, ô H5 sử dụng công thức này:
Công thức này tính tổng số tiền trong cột D khi một ngày trong cột C nằm giữa một ngày trong cột H5 và một ngày trong cột H6. Trong ví dụ, H5 chứa ngày 15/9/2015 và H6 chứa ngày 15/10 /2015.
Để ghép khớp ngày giữa hai giá trị, chúng ta phải sử dụng hai tiêu chí. SUMIF yêu cầu mỗi tiêu chí phải được nhập dưới dạng một vùng tiêu chuẩn:
“
Lưu ý rằng phải bao gồm các toán tử logic trong ngoặc kép (“”) sau đó nối ghép các tham chiếu ô sử dụng dấu và (&).
– Ví dụ 5: Tính số ngày trong năm Công thức chung:
=date-DATE(YEAR(date),1,0)
Giải thích:
Nếu muốn tính số ngày từ một định dạng ngày cụ thể (tức là ngày thứ bao nhiêu trong năm), bạn có thể sử dụng công thức sử dụng kết hợp các hàm DATE và YEAR.
Ví dụ: với ngày 1/6/2016 trong ô B4, sử dụng công thức sau sẽ trả lại kết quả 153:
=B4-DATE(YEAR(B4),1,0)
Và ngày 1/6/2016 là ngày thứ 153 trong năm 2016.
Ý nghĩa của công thức:
Công thức này tận dụng lợi thế đó là thực tế ngày tháng chỉ là các số liên tiếp trong Excel. Nó xác định ngày cuối cùng của năm trước và trừ đi kết quả ngày ban đầu trong ô B4. Kết quả trả lại là ngày thứ bao nhiêu trong năm.
Lưu ý đối số ngày trong hàm DATE được cung cấp bằng 0. Hàm DATE còn có chức năng là có thể xử lý các giá trị DAY nằm ngoài phạm vi và điều chỉnh kết quả phù hợp.
Tính số ngày đến ngày hiện tại trong năm
Để điều chỉnh công thức trả lại kết quả tính số ngày kể từ ngày thứ n trong năm cho ngày hiện tại, chỉ cần sử dụng hàm TODAY:
=TODAY()-DATE(YEAR(TODAY()),1,0)
– Ví dụ 6: Chuyển đổi định dạng dd/mm/yy (ngày/tháng/năm) thành mm/dd/yy (tháng/ngày/năm) Công thức chung:
=DATE(RIGHT(A1,2)+2000,MID(A1,4,2),LEFT(A1,2))
Giải thích:
Để chuyển đổi định dạng ngày tháng từ định dạng dd/mm/yy (ngày/tháng/năm) sang mm/dd/yy (tháng/ngày/năm), bạn có thể sử dụng công thức dựa trên hàm DATE. Trong ví dụ trên, công thức trong ô C5 là:
=DATE(RIGHT(B5,2)+2000,MID(B5,4,2),LEFT(B5,2))
Chuyển đổi các giá trị văn bản trong B5 29/02/16 thành một định dạng ngày thích hợp trên Excel.
Ý nghĩa của công thức:
Giá trị cốt lõi của công thức này là hàm DATE, được sử dụng để ghép một giá trị ngày thích hợp trên Excel. Hàm DATE yêu cầu giá trị năm, tháng và ngày hợp lệ, vì vậy chúng được phân tích cú pháp từ chuỗi văn bản ban đầu như sau:
Giá trị năm được trích bằng hàm RIGHT:
RIGHT(B5,2)+2000
Hàm RIGHT nhận được nhiều nhất 2 ký tự từ giá trị gốc. Số 2000 được thêm vào kết quả để tạo ra năm hợp lệ. Số này làm đối số năm trên hàm DATE.
Giá trị tháng được trích bằng:
MID (B5,4,2)
MID lấy 4-5 ký tự. Kết quả được đưa vào hàm DATE làm đối số tháng.
Giá trị ngày được trích bằng:
LEFT (B5,2)
LEFT lấy 2 ký tự cuối cùng của giá trị văn bản gốc, đưa vào hàm DATE làm đối số ngày.
Ba giá trị được trích ở trên được đưa vào hàm DATE như sau:
= DATE (2016, “02”, “29”)
Mặc dù tháng và ngày được cung cấp dưới dạng văn bản, hàm DATE sẽ tự động chuyển đổi sang số và trả về kết quả ngày hợp lệ.
Lưu ý: giá trị năm 2016 đã được tự động chuyển đổi thành số khi thêm 2000 vào.
Thêm vấn đề về dấu cách:
Nếu giá trị văn bản gốc ban đầu chứa thêm dấu cách ở đầu hoặc cuối, bạn có thể sử dụng thêm hàm TRIM để xóa:
=DATE(RIGHT(TRIM(A1),2)+2000,MID(TRIM(A1),4,2),LEFT(TRIM(A1),2))
– Ví dụ 7: Tạo hàng loạt ngày giống nhau, chỉ khác năm Công thức chung:
=DATE(YEAR(date)+1,MONTH(date),DAY(date))
Giải thích:
Nếu cần tạo một loạt ngày tháng giống nhau, có số năm tăng dần, bạn có thể sử dụng công thức có các hàm DAY, MONTH, YEAR, và DATE.
Ý nghĩa công thức:
Trong ví dụ trên, ngày trong ô B6 là ngày bắt đầu và công thức trong ô B7 là:
= DATE (YEAR (B6) + 1, MONTH (B6), DAY (B6))
Để giải quyết công thức này, Excel sẽ trích giá trị năm, tháng và ngày từ ngày bắt đầu trong ô B6, sau đó cộng thêm 1 vào giá trị năm. Tiếp theo, ngày kế tiếp được lặp lại bằng cách sử dụng DATE, sử dụng cùng một ngày và tháng, và và công thêm 1 vào năm.
= DATE (YEAR (B6) + 1, MONTH (B6), DAY (B6))
= DATE (2010 + 1,1,15)
= DATE (2011,1,15)
= 1/15/2011
Công thức đầu tiên sẽ trả về kết quả một định dạng ngày mới 1/15/2011, số năm nhiều hơn 1 năm từ định dạng ban đầu .
Sau khi nhập công thức đầu tiên, bạn có thể sao chép công thức xuống các ô tiếp theo. Mỗi công thức tiếp theo tạo ra một định dạng ngày mới có số năm tăng dần.
=DATE(YEAR(date)+1,1,1)
– Ví dụ 8: Tạo hàng loạt ngày và năm giống nhau, chỉ khác tháng Công thức chung:
=DATE(YEAR(date),MONTH(date)+1,DAY(date))
Giải thích:
Nếu muốn tạo một loạt ngày có công thức tăng dần các tháng, nhưng ngày và năm giống nhau bạn có thể sử dụng các hàm DAY, MONTH, YEAR và DATE.
Ý nghĩa công thức:
Trong ví dụ trên, định dạng ngày trong ô B6 là ngày bắt đầu và công thức sử dụng trong ô B7 là:
=DATE(YEAR(B6),MONTH(B6)+1,DAY(B6))
Để giải quyết công thức này, Excel sẽ trích giá trị năm, tháng và ngày từ ngày bắt đầu trong ô B6, sau đó cộng thêm 1 vào giá trị tháng. Tiếp theo, ngày kế tiếp được lặp lại bằng cách sử dụng DATE, sử dụng cùng một ngày và năm, và và cộng thêm 1 vào giá trị tháng.
=DATE(YEAR(B6),MONTH(B6)+1,DAY(B6))
=DATE(2010,1+1,15)
=DATE(2010,2,15)
=2/15/2010
Công thức đầu tiên sẽ trả về kết quả một định dạng ngày mới 2/15/2010, số tháng nhiều hơn 1 lần số tháng từ định dạng ban đầu.
Sau khi nhập công thức đầu tiên, bạn có thể sao chép công thức ở các ô tiếp theo. Mỗi công thức tiếp theo tạo ra một định dạng ngày mới có giá trị tháng tăng dần.
Lưu ý: nếu định dạng ngày bắt đầu từ 31/1, công thức trên sẽ bỏ qua tháng 2 và chuyển tiếp sang tháng 3. Lý do là vì không có ngày 2/31/2010, do đó Excel sử dụng giá trị ngày để chuyển sang ngày 3/3/2010.
= EOMONTH (B6,1)
Giải thích:
Nếu muốn tính tổng theo năm, bạn có thể sử dụng hàm SUMIFS với 2 tiêu chí.
Trong ví dụ trên, công thức trong ô C6 là:
Kết quả là tổng số tiền trong năm 2011. Bạn có thể sao chép công thức để tính tổng số tiền cho năm 2012 và 2013.
Ý nghĩa công thức:
Đối số đầu tiên của SUMIFs là phạm vi tổng (“sum_range”), và các tiêu chí được cung cấp dưới dạng một hoặc nhiều cặp vùng tiêu chuẩn.
Trong ví dụ này, phạm vi tổng là một vùng có tên gọi “amount” (E3:E2931), và các tiêu chí được cung cấp theo hai cặp, cả hai đều sử dụng một vùng có tên gọi là “date” (B3: B2931).
Trong mỗi trường hợp, hàm DATE được sử dụng trong các tiêu chí để xây dựng 2 định dạng ngày hợp lệ, cả hai đều sử dụng cùng một năm:
1. Ngày đầu tiên của năm 2011
2. Ngày cuối cùng của năm 2011
Kết quả công thức trả về là tổng số tiền tất cả các khoản chỉ trong năm 2011.
Vì đang sử dụng một tham chiếu ô để cung cấp kết quả cho một năm, bạn cùng có thể sao chép công thức để tính tổng giá trị cho các năm 2012 và 2013.
Giải thích:
Để tính tổng giá trị các ngày khác nhau, bạn có thể sử dụng hàm SUMIF.
Trong ví dị trên, công thức trong ô H5 là:
Công thức này tính tổng số tiền trong cột D khi ngày trong cột C lớn hơn ngày 1/10/2015.
Lưu ý rằng phải bao gồm các toán tử logic trong ngoặc kép (“”) sau đó nối ghép các tham chiếu ô sử dụng dấu và (&).
Ngày là tham chiếu ô
Nếu bạn muốn hiển thị ngày trên bảng tính để có thể dễ dàng thay đổi, bạn sử dụng công thức này:
Trong đó A1 là tham chiếu đến ô có chứa ngày hợp lệ.
Hàm SUMIFS thay thế:
Ngoài ra bạn cũng có thể sử dụng hàm SUMIFS. Hàm SUMIFS có thể xử lý nhiều tiêu chí, và thứ tự của các đối số khác với SUMIF. Công thức hàm SUMIFS tương đương là:
Lưu ý rằng phạm vi tổng hợp luôn đứng đầu tiên trong hàm SUMIFS.
– Ví dụ 11: Chuyển đổi văn bản thành ngày Công thức chung:
=DATE(LEFT(text,4),MID(text,5,2),RIGHT(text,2))
Giải thích:
Để chuyển đổi văn bản ở định dạng ngày không phù hợp thành định dạng ngày phù hợp trên Excel, bạn có thể phân tích cú pháp văn bản và ghép thành một ngày phù hợp với công thức dựa trên một số hàm như: DATE, LEFT, MID và hàm RIGHT.
Lưu ý: Trước khi bạn sử dụng công thức, tham khảo một số cách khác bên dưới để kiểm tra Excel phát hiện ra các văn bản và ngày không có công thức.
Trong ví dụ được trên, công thức trong ô C6 là:
=DATE(LEFT(B6,4),MID(B6,5,2),RIGHT(B6,2))
Công thức này trích các giá trị năm, tháng và ngày tách biệt, và sử dụng hàm DATE để tập hợp cvào ngày 24 /10/2000.
Excel sẽ không nhận ra các giá trị văn bản này là định đạng ngày, do đó, để tạo một ngày hợp lệ, bạn cần phải phân tích cú pháp văn bản thành các thành phần (năm, tháng, ngày) và sử dụng các thành phần này để tạo một ngày bằng hàm DATE.
Ý nghĩa công thức:
Hàm DATE lấy 3 đối số: năm, tháng và ngày. Hàm LEFT trích 4 ký tự còn lại và cung cấp làm đối số năm cho hàm DATE. Hàm MID trích 5-6 ký tự và lấy các ký tự này là đối số tháng, ngày cho hàm DATE, và hàm RIGHT trích 2 ký tự bên phải và cung cấp đối số ngày cho hàm DATE. Kết quả cuối cùng là một định dạng ngày hợp lệ trên Excel, có thể được định dạng bằng bất kỳ định dạng nào mà bạn muốn.
Ví dụ khác:
Trong hàng số 8, định dạng ngày là chúng tôi (ngày. tháng. năm) không được Excel công nhận, và công thức trong C8 là:
=DATE(RIGHT(B8,4),MID(B8,4,2),LEFT(B8,2))
Thêm số 0 để chuyển đổi định dạng văn bản thành định dạng ngày:
Trong một số trường hợp bạn sẽ nhận thấy rằng có định dạng văn bản mà Excel sẽ nhận ra. Trong trường hợp nàu bạn có thể buộc Excel chuyển đổi các giá trị văn bản thành định dạng ngày tháng bằng cách thêm số 0 vào giá trị. Khi bạn thêm số 0, Excel sẽ cố gắng buộc các giá trị văn bản thành các con số.
Để chuyển đổi định dạng văn bản thành định dạng ngày bằng cách thêm số 0, bạn sử dụng Paste Special:
Bước 1: Thêm số 0 vào ô không sử dụng và sao chép vào clipboard.
Bước 2: Chọn ngày bị lỗi.
Để chuyển đổi các ngày bằng cách thêm số 0 bằng cách sử dụng công thức:
=A1+0
Trong đó A1 có chứa định dạng ngày không hợp lệ.
Chuyển đổi Text thành Column:
Một cách khác để Excel nhận biết định dạng ngày tháng là sử dụng text thành các tính năng column:
Đôi khi bạn có thể sử dụng giải pháp này để sửa mọi thứ cùng một lúc.
Giải thích:
Lưu ý: Excel được tích hợp một số quy tắc xác nhận dữ liệu hợp lệ cho định dạng ngày tháng.
Để cho phép người dùng chỉ nhập đối số ngày giữa hai ngày, bạn có thể xác nhận dữ liệu bằng một công thức tùy chỉnh dựa trên hàm AND.
Trong ví dụ trên, việc xác nhận dữ liệu được áp dụng cho C5: C9 là:
Ý nghĩa công thức:
Các quy tắc xác nhận dữ liệu được kích hoạt khi người dùng thêm hoặc thay đổi giá trị trong ô.
Hàm AND lấy nhiều đối số (logic) và trả về kết quả TRUE chỉ khi tất cả các đối số trở về TRUE. Hàm DATE tạo ra một ngày hợp lệ trên Excel với các giá trị năm, tháng, và năm.
Nếu chỉ muốn cho phép định dạng ngày tháng trong tháng 6 năm 2016, bạn sử dụng hàm AND với 2 đối số.
Đối số đầu tiên kiểm tra đầu vào C5 lớn hơn hoặc bằng ngày 1/6/2016:
Đối số thứ 2 kiểm tra tính hợp lý đầu vào C5 nhỏ hơn hoặc bằng ngày 30/6/2016:
Nếu cả hai điều kiện là TRUE, hàm AND trả về kết quả TRUE và đầu vào xác nhận hợp lệ. Nếu một trong hai điều kiện là FALSE, và kết quả trả về FALSE và đầu vào dữ liệu không thành công.
– Ví dụ 13: Thêm năm vào ngày Công thức chung:
=DATE(YEAR(date)+years,MONTH(date),DAY(date))
Giải thích:
Để thêm năm cho một ngày cụ thể, bạn có thể sử dụng công thức dựa trên hàm DATE kết hợp với các hàm YEAR, MONTH, DAY.
Trong ví dụ trên, công thức trong ô D5 là:
=DATE(YEAR(B5)+C5,MONTH(B5),DAY(B5))
Ý nghĩa công thức:
Làm việc từ trong ra ngoài, các hàm YEAR, MONTH, DAY trích các thành phần ngày tương ứng:
=YEAR(B5)
=MONTH(B5)
=DAY(B5)
Xét về mặt bề ngoài hàm DATE chỉ đơn giản gộp các giá trị của thành phần thành một định dạng ngày Excel hợp lệ. Để thêm năm cho một ngày cụ thể, bạn chỉ cần thêm giá trị trong C5 vào thành phần năm trước khi gộp:
=DATE(YEAR(B5)+C5,MONTH(B5),DAY(B5))
Công thức có dạng như sau:
=DATE(1960+10,3,8)
=DATE(1970,3,8)
=8-Mar-1970
https://thuthuat.taimienphi.vn/ham-date-trong-excel-cach-su-dung-cau-truc-ham-23781n.aspx
Hàm Sumif Trong Excel Là Gì? Cách Sử Dụng Hàm Sumifs
Hàm SUMIF là gì? Sự khác nhau giữa hàm SUMIFS trong Excel với hàm SUMIF là như thế nào? Bài viết này sẽ hướng dẫn cho các bạn cú pháp, ý nghĩa, cách sử dụng và một số cách kết hợp giữa hàm SUMIF, SUMIFS với một số hàm thông dụng khác.
#1. Tổng quan về hàm SUMIFS
Trong công việc hàng ngày có sử dụng đến phần mềm Excel, để cộng các giá trị cùng một điều kiện cho trước với nhau thì chúng ta thường nghĩ ngay tới hàm SUMIF. Vậy Hàm SUMIF là gì?
Hàm SUMIF là gì?
Hàm SUMIFS trong Excel là một trong những hàm Excel cơ bản, hàm tính toán thường được dùng trong Excel. Để tính tổng trong Excel chúng ta sẽ dùng đến hàm SUM, nếu muốn thêm 1 điều kiện nhất định cho hàm tính tổng đó sẽ dùng hàm SUMIF.
#2. Cách sử dụng hàm SUMIFS
#2.1. Cú pháp và cách sử dụng hàm SUMIF
Cú pháp hàm này như sau:
=SUMIF(range,criteria,[sum_range])
Cú pháp hàm SUMIF có các đối số sau đây:
– Range (Bắt buộc): Phạm vi ô bạn muốn được đánh giá theo tiêu chí. Các ô trong mỗi phạm vi phải là số hoặc tên, mảng hay tham chiếu chứa số. Giá trị trống và giá trị văn bản bị bỏ qua. Phạm vi được chọn có thể chứa các ngày ở định dạng Excel tiêu chuẩn (ví dụ bên dưới).
– criteria (Bắt buộc): Tiêu chí ở dạng số, biểu thức, tham chiếu ô, văn bản hoặc hàm xác định sẽ cộng các ô nào. Ký tự đại diện có thể được bao gồm-dấu chấm hỏi (?) để khớp với bất kỳ ký tự đơn nào, dấu hoa thị (*) để khớp với bất kỳ chuỗi ký tự nào. Nếu bạn muốn tìm một dấu chấm hỏi hay dấu sao thực sự, hãy gõ dấu ngã (~) trước ký tự.
– sum_range Tùy chọn. Các ô thực tế để cộng nếu bạn muốn cộng các ô không phải là các ô đã xác định trong đối số range. Nếu đối số sum_range bị bỏ qua, Excel cộng các ô được xác định trong đối số range (chính các ô đã được áp dụng tiêu chí).
Sum_range phải có cùng kích cỡ và hình dạng theo phạm vi. Nếu không, hiệu suất có thể bị, và công thức sẽ tính tổng phạm vi ô bắt đầu với ô đầu tiên trong sum_range nhưng có cùng kích thước như phạm vi.
Bước 1: Tại ô G8, chúng ta viết công thức sau: =SUMIF(E8:E22;”nam”;F8:F22).
Trong công thức này, vùng phạm vi đánh giá tiêu chí là từ ô E8 đến ô E22, tiêu chí là giới tính nam, vùng tính tổng là từ ô F8 đến F22.
Bước 2: Bấm phím Enter, kết quả sẽ là 87.860.000 đồng. Tức tổng số tiền lương của các nhân viên nam là 87,86 triệu đồng.
#2.2. Cú pháp và cách sử dụng hàm SUMIFS
Hàm này có cú pháp tổng quát như sau:
=SUMIFS(sum_range,criteria_range1,criteria1,[criter_range2, criteria2], …)
Bước 1: Tại ô G7, chúng ta viết công thức sau:
=SUMIFS(F7:F21;E7:E21;”Nam”;D7:D21;”Văn phòng”)
Trong công thức này, vùng tính tổng là từ ô F7 đến ô F21, vùng tiêu chí đánh giá thứ nhất là từ ô E7 đến ô F21, tiêu chí thứ nhất là giới tính nam, cùng tiêu chí đánh giá thứ 2 là từ ô D7 đến ô D21, tiêu chí thứ 2 là văn phòng.
Bước 2: Bấm phím Enter, kết quả của phép tính sẽ là: 15.910.000.
Vậy, tổng tiền lương của các nhân viên nam ở bộ phận văn phòng là 15.910.000 đồng.
#2.3. Hàm SUMIFS nhiều điều kiện
Ví dụ #3, Tính tổng tiền lương của nhân viên thỏa mãn các điều kiện sau:
– Giới tính nam;
– Thuộc bộ phận văn phòng;
– Sinh năm 1992
Để giải quyết bài toàn này, chúng ta thực hiện như sau:
Bước 1: Tại ô H7, chúng ta viết công thức:
=SUMIFS(G7:G21;F7:F21;”nam”;E7:E21;”văn phòng”;D7:D21;1992)
Bước 2: Bấm phím Enter, chúng ta sẽ ra kết quả là: 8.110.000 đồng.
Hỏi: Sự khác nhau giữa hàm SUMIF và SUMIFS như thế nào?
Trả lời: Hàm SUMIF dùng để tính tổng các phần tử theo 1 điều kiện cho trước, còn SUMIFS dùng để tính tổng các phần tử thỏa mãn từ hai điều kiện trở lên.
Hỏi: Tối đa có thể dùng bao nhiêu điều kiện trong hàm SUMIFS?
Hỏi: Cách sử dụng các phím tắt trong Excel?
Hỏi: Tải file hướng dẫn hàm SUMIFS tại đâu?
Sử Dụng Tham Chiếu Có Cấu Trúc Với Bảng Excel
Khi bạn tạo bảng Excel, Excel gán một tên bảng và mỗi tiêu đề cột trong bảng. Khi bạn thêm công thức vào bảng Excel, những tên có thể xuất hiện tự động khi bạn nhập công thức và chọn tham chiếu ô trong bảng thay vì nhập thủ công chúng. Đây là ví dụ về điều Excel:
Tổ hợp tên cột và bảng đó được gọi là tham chiếu có cấu trúc. Các tên trong tham chiếu có cấu trúc sẽ điều chỉnh bất kỳ lúc nào bạn thêm hoặc loại bỏ dữ liệu khỏi bảng.
Tham chiếu có cấu trúc cũng xuất hiện khi bạn tạo công thức bên ngoài bảng Excel tham chiếu dữ liệu bảng. Tham chiếu có thể giúp định vị bảng dễ dàng hơn trong sổ làm việc lớn.
Để bao gồm các tham chiếu có cấu trúc trong công thức của bạn, hãy bấm ô bảng bạn muốn tham chiếu thay vì nhập tham chiếu ô của chúng trong công thức. Hãy dùng dữ liệu ví dụ sau đây để nhập một công thức tự động sử dụng các tham chiếu có cấu trúc để tính toán lượng tiền hoa hồng bán hàng.
Sao chép dữ liệu mẫu trong bảng ở trên, bao gồm các đầu đề cột và dán nó vào ô A1 của trang tính Excel mới.
Để tạo bảng, chọn bất kỳ ô nào trong phạm vi dữ liệu, rồi nhấn Ctrl + T.
Bấm ô E2, gõ dấu bằng (=) và bấm ô C2.
Trong thanh công thức, các tham chiếu có cấu trúc [@ [Doanh Số]] xuất hiện sau dấu bằng.
Gõ dấu hoa thị (*) ngay sau dấu ngoặc đóng, rồi bấm ô D2.
Trong thanh công thức, các tham chiếu có cấu trúc [@ [% Tiền hoa hồng]] xuất hiện sau dấu hoa thị.
Nhấn Enter.
Excel sẽ tự động sẽ tạo một cột được tính toán và sao chép công thức xuống toàn bộ cột cho bạn, điều chỉnh công thức cho mỗi hàng.
Điều gì xảy ra khi tôi dùng tham chiếu ô rõ ràng?
Nếu bạn nhập tham chiếu ô rõ ràng trong cột tính toán thì có thể khó xem những gì công thức đang tính toán hơn.
Trong trang tính mẫu của bạn, hãy bấm ô E2
Lưu ý là trong khi Excel sao chép công thức của bạn xuống cột, nó không dùng tham chiếu có cấu trúc. Ví dụ: nếu bạn thêm một cột vào giữa cột C và D hiện có, bạn phải xem lại công thức của mình.
Làm thế nào để tôi thay đổi tên bảng?
Bất kỳ khi nào bạn tạo bảng Excel, Excel sẽ tạo tên bảng mặc định (Table1, Table2, v.v.), nhưng bạn có thể thay đổi tên bảng để giúp nó có ý nghĩa hơn.
Trong dữ liệu ví dụ của chúng tôi, chúng ta đã dùng tên DeptSales.
Sử dụng các quy tắc sau đây cho tên bảng:
Sử dụng ký tự hợp lệ Luôn bắt đầu một tên bằng chữ cái, một ký tự dấu gạch dưới (_) hoặc dấu xuyệt ngược (). Dùng chữ cái, số, dấu chấm, và các ký tự cho phần còn lại của tên gạch dưới. Bạn không thể sử dụng “C”, “c”, “R” hoặc “r” đối với tên, vì chúng đang đã chỉ định làm một lối tắt để chọn các cột hoặc hàng cho ô hiện hoạt khi bạn nhập chúng trong hộp tên hoặc Đi tới .
Không sử dụng tham chiếu ô Tên không là giống như tham chiếu ô, chẳng hạn như Z$ 100 hoặc R1C1.
Không sử dụng hơn 255 ký tự Một tên bảng có thể có tối đa 255 ký tự.
Sử dụng tên bảng duy nhất Không được phép dùng tên giống nhau. Excel không phân biệt ký tự in hoa và chữ thường trong tên vì vậy nếu bạn nhập “Doanh thu” nhưng đã có một tên khác được gọi là “DOANH THU” trong cùng một sổ làm việc, bạn sẽ được nhắc để chọn một tên duy nhất.
Sử dụng bộ nhận diện một đối tượng Nếu bạn có kế hoạch gặp một tập hợp bảng, Pivottable và biểu đồ, đó là nên thêm tiền tố vào tên của bạn với kiểu đối tượng. Ví dụ: tbl_Sales cho một bảng doanh số, pt_Sales doanh số bán hàng PivotTable và chrt_Sales cho biểu đồ bán hàng, hoặc ptchrt_Sales cho một PivotChart bán hàng. Điều này sẽ liên tục tất cả các tên của bạn trong một danh sách sắp xếp trong Trình quản lý tên.
Quy tắc cú pháp tham chiếu có cấu trúc
Bạn cũng có thể nhập hoặc thay đổi các tham chiếu có cấu trúc theo cách thủ công trong công thức nhưng để thực hiện điều này, nó sẽ giúp để hiểu cú pháp tham chiếu có cấu trúc. Chúng ta hãy xem qua ví dụ công thức sau đây:
=SUM(DeptSales[ [ #Tổng] , [Số tiền doanh thu]] ,DeptSales[[#Dữ liệu] , [Số Tiền Hoa hồng]])
Công thức này có các thành phần tham chiếu có cấu trúc sau đây:
Mã xác định cột: [Doanh số]và[Số tiền hoa hồng] là các mã xác định cột sử dụng tên của cột mà họ đại diện cho. Họ tham chiếu dữ liệu cột, không có bất kỳ cột tiêu đề hoặc tổng dòng. Luôn Hãy mã xác định trong dấu ngoặc như minh họa.
Mã xác định mục: [#Totals] và [#Data] là các mã xác định mục đặc biệt có tham chiếu đến các phần cụ thể của bảng, chẳng hạn như hàng tổng.
Mã xác định bảng: [[#Tổng] , [Doanh Số]] và [[#Dữ liệu] , [Số tiền Hoa hồng]] là các mã xác định bảng đại diện cho các phần bên ngoài của tham chiếu có cấu trúc. Tham chiếu bên ngoài sẽ theo sau tên bảng và bạn đặt chúng trong dấu ngoặc vuông.
Tham chiếu có cấu trúc: (DeptSales [[#Totals], [doanh số]] và DeptSales [[#Data], [số tiền hoa hồng]] là tham chiếu có cấu trúc, được đại diện bởi một chuỗi bắt đầu với tên bảng và kết thúc với mã xác định cột.
Khi bạn tạo hay sửa tham chiếu có cấu trúc theo cách thủ công, hãy dùng quy tắc cú pháp sau:
Sử dụng ngoặc vuông bao quanh tiêu đề cột với các ký tự đặc biệt Nếu có ký tự đặc biệt, toàn bộ tiêu đề cột cần được đặt trong dấu ngoặc vuông, điều này có nghĩa là dấu ngoặc vuông kép được yêu cầu trong một mã xác định cột. Ví dụ: =DeptSalesFYSummary[[Tổng Số tiền $]]
Đây là danh sách các ký tự đặc biệt cần dấu ngoặc bổ sung trong công thức:
Dùng ký tự dấu cách để nâng cao khả năng đọc trong tham chiếu có cấu trúc Bạn có thể dùng ký tự dấu cách để cải thiện khả năng đọc tham chiếu có cấu trúc. Ví dụ: =DeptSales[ [Người Bán hàng]:[Khu vực] ] hoặc =DeptSales[[#Tiêu đề], [#Dữ liệu], [% Tiền hoa hồng]]
Tôi khuyên bạn sử dụng một dấu cách:
Mã xác định mục đặc biệt
Tham chiếu tới một phần cụ thể của bảng chẳng hạn như chỉ cần hàng tổng cộng, bạn có thể sử dụng bất kỳ mã xác định mục đặc biệt sau đây trong tham chiếu có cấu trúc của bạn.
Toàn bộ bảng, bao gồm đề mục cột, dữ liệu và tổng (nếu có).
#Tổng cộng
Chỉ hàng tổng. Nếu không có giá trị nào thì sẽ trả về giá trị null.
Chỉ cần các ô trong cùng một hàng làm công thức. Những mã xác định này không thể kết hợp với bất kỳ mã xác định mục đặc biệt nào khác. Hãy dùng chúng để áp đặt hành vi giao cắt ngầm định dành cho tham chiếu hoặc để ghi đè hành vi giao cắt ngầm định và tham chiếu đến các giá trị đơn từ cột.
Excel sẽ tự động thay đổi mã xác định #Hàng Này vào các mã xác định @ ngắn hơn trong các bảng có nhiều hơn một hàng dữ liệu. Nhưng nếu bảng của bạn chỉ có một hàng, Excel không thay thế các mã xác định #Hàng Này, điều này có thể gây ra kết quả tính toán bất ngờ khi bạn thêm nhiều hàng. Để tránh các vấn đề về tính toán, hãy đảm bảo bạn nhập nhiều hàng trong bảng của bạn trước khi bạn nhập bất kỳ công thức tham chiếu có cấu trúc nào.
Xác thực đủ điều kiện tham chiếu có cấu trúc trong cột tính toán
Khi tạo cột tính toán, bạn thường dùng tham chiếu có cấu trúc để tạo công thức. Tham chiếu có cấu trúc này có thể không được xác thực đủ điều kiện hoặc được xác thực hoàn toàn đủ điều kiện. Ví dụ: để tạo cột tính toán gọi là Số tiền Hoa hồng, tính số tiền hoa hồng theo tiền đô, bạn có thể dùng công thức sau:
Quy tắc chung để làm theo như sau: Nếu bạn đang dùng tham chiếu có cấu trúc trong bảng, ví dụ như khi tạo cột tính toán, bạn có thể dùng tham chiếu có cấu trúc không đủ điều kiện, nhưng nếu bạn dùng tham chiếu có cấu trúc ngoài bảng, bạn cần dùng tham chiếu có cấu trúc hoàn toàn đủ điều kiện.
Ví dụ về dùng tham chiếu có cấu trúc
=DeptSales[[#Hàng Này], [Số tiền Hoa hồng]]
hoặc
=DeptSales[@Số tiền Hoa hồng]
Ô tại giao điểm của hàng hiện tại và cột số tiền hoa hồng. Nếu sử dụng trong cùng hàng là đầu trang hoặc hàng tổng, điều này sẽ trả về một #VALUE! lỗi.
Nếu bạn nhập biểu mẫu dài hơn của tham chiếu có cấu trúc này (#Hàng Này) trong một bảng có nhiều hàng dữ liệu, Excel sẽ tự động thay thế nó bằng các biểu mẫu ngắn hơn (@). Cả hai bảng đều hoạt động như nhau.
E5 (Nếu hàng hiện tại là 5)
Chiến lược để làm việc với các tham chiếu có cấu trúc
Cân nhắc những vấn đề sau khi bạn làm việc với tham chiếu có cấu trúc.
Dùng Tự động Điền Công thức Bạn có thể thấy rằng dùng Tự động Điền Công thức rất hữu ích khi nhập tham chiếu có cấu trúc và để bảo đảm sử dụng đúng cú pháp. Để biết thêm chi tiết, hãy xem Dùng Tự động Điền Công thức.
Sử dụng sổ làm việc với các nối kết bên ngoài đến bảng Excel trong sổ làm việc khác Nếu sổ làm việc chứa một nối kết bên ngoài vào bảng Excel trong sổ làm việc khác, sổ làm việc nguồn được nối kết đó phải được mở trong Excel để tránh #REF! lỗi trong sổ làm việc đích có chứa các nối kết. Nếu bạn mở sổ làm việc đích đầu tiên và #REF! lỗi xuất hiện, họ sẽ được giải quyết nếu bạn sau đó mở sổ làm việc nguồn. Nếu bạn mở sổ làm việc nguồn trước tiên, bạn sẽ thấy mã lỗi không.
Chuyển đổi phạm vi thành bảng và bảng thành phạm vi Khi bạn chuyển đổi bảng thành phạm vi, tất cả các tham chiếu ô thay đổi của họ tương đương tuyệt đối A1 kiểu tham chiếu. Khi bạn chuyển đổi phạm vi thành bảng, Excel không tự động thay đổi bất kỳ tham chiếu ô của phạm vi này để tham chiếu có cấu trúc tương đương của họ.
Thêm hoặc xóa cột và hàng trong bảng Vì bảng dữ liệu phạm vi mức độ thường xuyên thay đổi, tham chiếu ô cho tham chiếu có cấu trúc điều chỉnh tự động. Ví dụ, nếu bạn dùng một tên bảng trong công thức để đếm tất cả các ô dữ liệu trong bảng, nhưng bạn rồi thêm một hàng dữ liệu, tham chiếu ô tự động điều chỉnh.
Đổi tên bảng hoặc cột Nếu bạn đổi tên cột hay bảng, Excel sẽ tự động đổi việc sử dụng đề mục cột và bảng đó trong tất cả các tham chiếu có cấu trúc dùng trong sổ làm việc.
Di chuyển, sao chép, và điền tham chiếu có cấu trúc Tất cả các tham chiếu có cấu trúc vẫn giữ nguyên khi bạn sao chép hoặc di chuyển công thức sử dụng một tham chiếu có cấu trúc.
Lưu ý: Sao chép một tham chiếu có cấu trúc và cách thực hiện một màu tô của một tham chiếu có cấu trúc sẽ không tương tự. Khi bạn sao chép, tất cả các tham chiếu có cấu trúc vẫn giữ nguyên, trong khi khi bạn điền công thức, tham chiếu có cấu trúc đủ điều chỉnh mã xác định cột chuỗi như tóm tắt trong bảng sau đây.
Bạn cần thêm trợ giúp?
Bạn luôn có thể nhờ chuyên gia trong Cộng đồng Kỹ thuật Excel, tìm sự hỗ trợ trong Cộng đồng Giải pháp hoặc đề xuất tính năng hay cải tiến mới trên Excel User Voice.
Tổng quan về bảng ExcelVideo: tạo và định dạng bảng Exceltổng dữ liệu trong bảng Excelđịnh dạng bảng Excelđổi cỡ bảng bằng cách thêm hoặc loại bỏ hàng và cộtLọc dữ liệu trong một phạm vi hoặc bảngchuyển đổi bảng thành phạm vivấn đề tương thích bảng Excelxuất bảng Excel sang SharePointtab Tổng quan về công thức trong Excel
Cập nhật thông tin chi tiết về Cấu Trúc Hàm Sumif Trong Excel 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!