Đề Xuất 6/2023 # Hàm Date Trong Excel, Cách Dùng, Cấu Trúc Hàm # Top 10 Like

Cập nhật nội dung chi tiết về Hàm Date Trong Excel, Cách Dùng, Cấu Trúc Hàm mới nhất trên website Beiqthatgioi.com. Hy vọng thông tin trong bài viết sẽ đáp ứng được nhu cầu ngoài mong đợi của bạn, chúng tôi sẽ làm việc thường xuyên để cập nhật nội dung mới nhằm giúp bạn nhận được thông tin nhanh chóng và chính xác nhất.

Đị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.

Một số ví dụ về hàm Date – Ví dụ 1: Đánh dấu ngày lớn hơn Công thức chung:

=A1gt;DATE(year,month,day)

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:

=B4gt;DATE(2015,8,1)

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:

=B4gt;=DATE(2015,8,1)

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:

=B4gt;input

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.

– Ví dụ 4: Tính tổng giá trị theo ngày Công thức chung:

=SUMIFS(amount,start_date,”gt;”amp;A1,end_date,”lt;gt;

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:

=SUMIFS(amount,date,”gt;”amp;H5,date,”lt;gt;

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.

Ý nghĩa của công thức

Hàm SUMIFS hỗ trợ toán tử logic ( logical operator) của Excel (tức là “=”, “gt;”, “gt; =”,…) và nhiều tiêu chí khác.

Để 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:

“gt;”amp;H5,date

“lt;”amp;h6,date criteria=””gt;

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à (amp;).

Cần chú trọng khi sử dụng dấu lớn hơn hoặc bằng (“gt; =”) và ít hơn hoặc bằng (“lt;=”) nếu=”” muốn=”” bao=”” gồm=”” (ghép)=”” ngày=”” bắt=”” đầu=”” hoặc=”” ngày=”” kết=”” thúc=”” cũng=”” như=”” ngày=”” giữa=””gt;

– 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)

– Ví dụ 9: Tính tổng theo năm Công thức chung:

=SUMIFS(sum_range,date,”gt;=”amp;DATE(year),date,”lt;gt;

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à:

=SUMIFS(amount,date,”gt;=”amp;DATE(G6,1,1),date,”lt;gt;

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

date, “gt;=”amp;DATE(G6,1,1)

date, “lt;=”amp;date(g6,12,31) date=”” is=””gt;lt;=gt;

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.

– Ví dụ 10: Tính tổng các ngày khác nhau Công thức chung:

=SUMIF(range,”gt;”amp;DATE(year,month,day),sum_range)

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à:

=SUMIF(date,”gt;”amp;DATE(2015,10,1),amount)

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.

Ý nghĩa công thức:

Hàm SUMIF hỗ trợ toán tử logic của Excel (tức là “=”, “gt;”, “gt; =”, …), do đó bạn có thể sử dụng hàm theo ý muốn của mình.

Trong ví dụ này để khớp các ngày lớn hơn ngày 1/10/2015, bạn sử dụng toán tử lớn hơn (gt;) với hàm DATE để tạo ngày:

“gt;”amp;DATE(2015,10,1)

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à (amp;).

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:

=SUMIF(date,”gt;”amp;A1,amount)

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à:

=SUMIFS(amount, date,”gt;”amp;DATE(2015,10,1))

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.

Bước 3: Chọn Paste Special =gt; Values =gt; Add .

Để 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:

Chọn cột ngày tháng, sau đó thử Data =gt; Text to columns =gt; Fixed =gt; Finish.

Đô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.

– Ví dụ 12: Xác nhận giá trị hợp lệ giữa các ngày Công thức chung:

=AND(A1gt;=date1),A1lt;gt;

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à:

=AND(C5gt;=DATE(2016,6,1),C5lt;gt;

Ý 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:

C5gt;=DATE(2016,6,1)

Đố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:

Cấu Trúc Hàm Sumif Trong Excel

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 If And, If Or, Left, Date Lồng Nhau Trong Excel

1. CÁCH DÙNG HÀM IF VỚI HÀM AND VÀ HÀM OR TRONG EXCEL

1,1 HÀM IF

Hàm IF là một trong những hàm logic giúp người dùng kiểm tra một điều kiện nhất định và trả về giá trị mà bạn chỉ định nếu điều kiện là TRUE hoặc trả về một giá trị khác nếu điều kiện là FALSE.

Cú pháp:

=IF( logical_test, value_if_true, value_if_false)

Trong đó:

1,2 HÀM AND

Hàm AND ít được dùng độc lập mà thường được dùng để mở rộng tính hữu dụng của các hàm thực hiện các kiểm nghiệm logic như hàm IF chẳng hạn.

Hàm AND trong excel là hàm sẽ trả về kết quả là TRUE nếu tất cả các đối số của hàm định trị là TRUE và trả về kết quả là FALSE nếu có ít nhất một đối số của hàm định trị là FALSE.

Cú pháp:

= AND(logical1, [logical2], ...)

Trong đó:

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ố.

logical1 và logical2 là các mệnh đề logic. Hàm này cho phép sử dụng nhiều hơn 1 mệnh đề logic.

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!.

Các lưu ý khi dùng hàm AND

1,3 HÀM OR

Cú pháp:

Hàm OR cũng giống như hàm AND khi nó có nhiệm vụ kiểm tra điều kiện của các giá trị nhưng khác ở chỗ chỉ cần một trong hai hoặc nhiều điều kiện là đúng thì giá trị hiển nhiên là True. Còn tất cả sai thì hiển nhiên là False rồi.

= OR (logical1, [logical2], ...)

logical1: bắt buộc. Là một giá trị logic.

logical2 : tùy chọn. Là một giá trị logic. Có thể lên tới 255 điều kiện khác nhau.

Trong đó của hàm OR rất giống với AND:

Chức Năng: Trả về TRUE nếu bất kỳ đối số nào là TRUE, trả về FALSE nếu tất cả các đối số là FALSE.

2. CÁCH SỬ DỤNG HÀM IF KẾT HỢP NHIỀU ĐIỀU KIỆN:

Hàm AND: Nếu điều kiện kiểm tra chứa hàm AND, Excel sẽ trả về TRUE (Đúng) nếu tất cả các điều kiện được đáp ứng; nếu không sẽ trả về FALSE (Sai).

Hàm OR: Trong trường hợp sử dụng hàm OR trong kiểm tra, Excel sẽ trả về TRUE nếu bất kỳ điều kiện nào được đáp ứng; nếu khác sẽ trả về FALSE (Sai).

Nói tóm lại, có thể có 2 loại điều kiện cơ bản – với hàm logic AND và OR. Do đó, hàm IF của bạn nên nhúng một hàm AND và OR để làm phép thử logic tương ứng.

Để minh họa rõ hơn, chúng ta hãy xem xét một vài ví dụ của hàm IF với nhiều điều kiện.

Giả sử, bạn có một bảng với kết quả của hai điểm thi:

Ví dụ: Hãy đưa ra kết quả từ bảng danh sách học sinh gồm Họ và Tên, Điểm 1 phải lớn hơn hoặc bằng 60 và Điểm 2 phải lớn hơn hoặc bằng 90. Chỉ khi đáp ứng cả hai điều kiện trên, học sinh mới vượt qua kỳ thi.

Cách dễ nhất để tạo một công thức thích hợp là viết ra điều kiện trước, sau đó kết hợp nó vào đối số kiểm tra hàm IF của bạn:

Trong đó:

Công thức IF/AND:

Sau khi nhập công thức cho ô E5, nhấn Enter được kết quả trả về như hình phía bên dưới:

Ví dụ: Vẫn là ví dụ về bảng học sinh gồm Họ và Tên, Điểm 1 phải lớn hơn hoặc bằng 60 và Điểm 2 phải lớn hơn hoặc bằng 90. Hãy đưa ra kết quả nếu ít nhất một trong các điều kiện được chỉ định được đáp ứng cả hai điều kiện trên, học sinh mới vượt qua kỳ thi.

Cách dễ nhất để tạo một công thức thích hợp là viết ra điều kiện trước, sau đó kết hợp nó vào đối số kiểm tra hàm IF của bạn:

Trong đó:

Công thức IF/OR:

Sau khi nhập công thức cho ô E5, nhấn Enter được kết quả trả về như hình phía bên dưới:

Trong bảng trên, giả sử bạn có các tiêu chí sau để đánh giá điều kiện đạt của học sinh:

Nếu một trong các điều kiện trên được đáp ứng thì bạn đó được coi là đã vượt qua, nếu không thì trượt.

Công thức có vẻ phức tạp, nhưng thực ra cũng không quá khó lắm. Bạn chỉ cần biểu thị hai điều kiện là các câu lệnh AND và đặt chúng trong hàm OR vì không yêu cầu cả hai điều kiện được đáp ứng, chỉ cần một trong hai điều kiện đáp ứng là đủ:

Trong đó:

Cuối cùng, sử dụng hàm OR ở trên làm điều kiện kiểm tra logic trong hàm IF và cung cấp các đối số TRUE (Đúng) và FALSE (Sai).

Kết quả là bạn sẽ nhận được công thức IF sau với nhiều điều kiện AND/OR:

a) CÔNG THỨC HÀM IF CHO NGÀY THÁNG VỚI HÀM DATEVALUE

Để hàm IF có thể nhận dạng được ngày tháng trong một biểu thức logic, bạn phải đặt nó trong hàm DATEVALUE như thế này: DATEVALUE(“11/15/2020”). Công thức hoàn chỉnh có dạng như sau:

=IF(D4<DATEVALUE("11/15/2020"),"completed","Coming soon")

Như minh họa của hình bên dưới, công thức hàm IF này đáng giá ngày tháng trong cột D và trả về giá trị “Completed” nếu như trò chơi này diễn tra trước ngày 15 tháng 11, còn ngược lại thì công thức sẽ trả về giá trị “Coming soon”.

Để chỉ ra những ngày đã diễn ra cách đây hơn 30 ngày, bạn có thể dùng công thức sau:

VIDEO HƯỚNG DẪN CƠ BẢN HÀM IF AND OR MONTH DATE:

Cấu Trúc Dữ Liệu Trong Java

Các cấu trúc dữ liệu cung cấp bởi các package tiện ích của Java rất mạnh mẽ và thực hiện các tính năng rộng rãi. Những cấu trúc dữ liệu này bao gồm những interface và class.

Để hiểu sâu hơn các khái niệm được trình bày trong chương này, mời bạn tham khảo loạt bài: .

Lớp Enumeration trong Java

Interface Enumeration bản thân nó không phải là cấu trúc dữ liệu, nhưng rất quan trong bên trong ngữ cảnh sử dụng các cấu trúc dữ liệu khác. Interface Enumeration định nghĩa để nhận các thành phần kế tiếp từ cấu trúc dữ liệu.

Ví dụ, Enumeration định nghĩa phương thức gọi là nextElement được sử dụng để lấy các thành phần tiếp theo trong cấu trúc dữ liệu chứa nhiều thành phần.

Để tìm hiểu chi tiết về interface này, bạn truy cập link sau: .

Lớp BitSet trong Java

Lớp BitSet trong Java triển khai một nhóm các bit hoặc flag mà có thể được thiết lập và xóa một cách riêng rẽ.

Class này rất hữu dụng trong trường hợp bạn muốn lưu trữ một tập các giá trị Boolean và chỉ muốn gắn từng bit các giá trị và thiết lập hoặc xóa nó thích hợp.