Hướng dẫn chỉ ra cách bạn có thể tận dụng hàm SEQUENCE mới để nhanh chóng tạo danh sách ngày trong Excel và sử dụng tính năng Tự động điền để điền vào một cột có ngày, ngày làm việc, tháng hoặc năm.
Cho đến gần đây, chỉ có một cách dễ dàng để tạo ngày tháng trong Excel – Tự động điền đặc tính. Sự ra đời của mảng động mới Hàm SEQUENCE đã giúp bạn có thể tạo một chuỗi ngày bằng công thức. Hướng dẫn này sẽ xem xét sâu hơn cả hai phương pháp để bạn có thể chọn phương pháp phù hợp nhất với mình.
Cách điền chuỗi ngày trong Excel
Khi bạn cần điền vào một cột có ngày tháng trong Excel, cách nhanh nhất là sử dụng Tính năng Tự động điền.
Tự động điền chuỗi ngày tháng trong Excel
Việc điền vào một cột hoặc hàng với các ngày tăng lên một ngày rất dễ dàng:
- Nhập ngày đầu tiên vào ô đầu tiên.
- Chọn ô có ngày ban đầu và kéo chốt điền (một hình vuông nhỏ màu xanh lục ở góc dưới bên phải) xuống hoặc sang phải.
Excel sẽ ngay lập tức tạo ra một chuỗi ngày có cùng định dạng với ngày đầu tiên bạn nhập theo cách thủ công.
Điền vào một cột với các ngày trong tuần, tháng hoặc năm
Để tạo một chuỗi ngày làm việc, tháng hoặc năm, hãy thực hiện một trong các thao tác sau:
- Điền vào một cột với các ngày tuần tự như được mô tả ở trên. Sau đó, nhấp vào Tùy chọn Tự động điềnvà chọn tùy chọn mong muốn, nói Lấp đầy tháng:
- Hoặc bạn có thể nhập ngày đầu tiên của mình, nhấp chuột phải vào ô điều khiển điền, giữ và kéo qua nhiều ô nếu cần. Khi bạn thả nút chuột, một menu ngữ cảnh sẽ bật lên cho phép bạn chọn tùy chọn cần thiết, Điền năm trong trường hợp của chúng ta:
Điền vào một chuỗi ngày tăng dần N ngày
Để tự động tạo một chuỗi ngày, ngày trong tuần, tháng hoặc năm với bước cụ thể, đây là những gì bạn cần làm:
- Nhập ngày đầu tiên vào ô đầu tiên.
- Chọn ô đó, bấm chuột phải vào ô điều khiển điền, kéo nó qua nhiều ô nếu cần, sau đó thả ra.
- Trong menu bật lên, hãy chọn Loạt (mục cuối cùng).
- bên trong Loạt hộp thoại, chọn Đơn vị ngày tháng quan tâm và đặt Giá trị bước.
- Bấm OK.
Cách tạo chuỗi ngày trong Excel bằng hàm Sequence
Trong một trong các hướng dẫn trước, chúng ta đã xem xét cách sử dụng hàm SEQUENCE mảng động mới để tạo một dãy số. Bởi vì bên trong Excel, ngày tháng được lưu trữ dưới dạng số sê-ri, nên hàm cũng có thể dễ dàng tạo ra một chuỗi ngày tháng. Tất cả những gì bạn phải làm là định cấu hình chính xác các đối số như được giải thích trong các ví dụ sau.
Tạo một chuỗi ngày trong Excel
Để tạo chuỗi ngày trong Excel, hãy thiết lập các đối số sau của hàm SEQUENCE:
SEQUENCE (hàng, [columns], [start], [step])
- Hàng – số hàng để điền ngày tháng.
- Cột – số cột để điền ngày tháng.
- Khởi đầu – ngày bắt đầu ở định dạng mà Excel có thể hiểu được, như “8/1/2020” hoặc “1-8-2020”. Để tránh nhầm lẫn, bạn có thể cung cấp ngày bằng cách sử dụng NGÀY chẳng hạn như DATE (2020, 8, 1).
- Bươc – số gia cho mỗi ngày tiếp theo trong một trình tự.
Ví dụ: để tạo danh sách 10 ngày bắt đầu từ ngày 1 tháng 8 năm 2020 và tăng thêm 1 ngày, công thức là:
=SEQUENCE(10, 1, "8/1/2020", 1)
hoặc là
=SEQUENCE(10, 1, DATE(2020, 8, 1), 1)
Ngoài ra, bạn có thể nhập số ngày (B1), ngày bắt đầu (B2) và bước (B3) trong các ô được xác định trước và tham chiếu các ô đó trong công thức của bạn. Vì chúng tôi đang tạo một danh sách nên số cột (1) được mã hóa cứng:
=SEQUENCE(B1, 1, B2, B3)
Nhập công thức dưới đây vào ô trên cùng (trong trường hợp của chúng tôi là A6), nhấn Đi vào và kết quả sẽ tự động tràn qua số hàng và cột được chỉ định.
Tạo một chuỗi ngày làm việc trong Excel
Để chỉ nhận một chuỗi ngày làm việc, hãy đặt SEQUENCE trong hàm WORKDAY hoặc WORKDAY.INTL theo cách này:
WORKDAY (start_date -1, SEQUENCE (no_of_days))
Khi hàm WORKDAY thêm số ngày được chỉ định trong đối số thứ hai vào ngày bắt đầu, chúng tôi lấy nó trừ đi 1 để có chính ngày bắt đầu trong kết quả.
Ví dụ: để tạo một chuỗi các ngày làm việc bắt đầu từ ngày trong B2, công thức là:
=WORKDAY(B2-1, SEQUENCE(B1))
Trong đó B1 là kích thước trình tự.
Mẹo và lưu ý:
- Nếu ngày bắt đầu là Thứ Bảy hoặc Chủ Nhật, loạt phim sẽ bắt đầu vào ngày làm việc tiếp theo.
- Excel WORKDAY hàm giả định thứ bảy và chủ nhật là cuối tuần. Để định cấu hình các ngày cuối tuần và ngày lễ tùy chỉnh, hãy sử dụng WORKDAY.INTL chức năng thay thế.
Tạo chuỗi tháng trong Excel
Để tạo một chuỗi ngày tăng dần theo một tháng, bạn có thể sử dụng công thức chung sau:
NGÀY(năm, SEQUENCE (12), ngày)
Trong trường hợp này, bạn đặt năm mục tiêu vào 1st đối số và ngày trong 3rd tranh luận. Đối với 2nd đối số, hàm SEQUENCE trả về các số liên tiếp từ 1 đến 12. Dựa trên các tham số trên, hàm DATE tạo ra một chuỗi ngày như được hiển thị trong phần bên trái của ảnh chụp màn hình bên dưới:
=DATE(2020, SEQUENCE(12), 1)
Để chỉ hiển thị tên tháng, đặt một trong những điều dưới đây định dạng ngày tùy chỉnh cho phạm vi tràn:
- mmm – dạng ngắn như tháng một, Tháng hai, Mar, Vân vân.
- mmmm – dạng đầy đủ như tháng Giêng, tháng 2, tháng Ba, Vân vân.
Do đó, chỉ tên tháng sẽ xuất hiện trong các ô, nhưng các giá trị cơ bản vẫn sẽ là ngày đầy đủ. Trong cả hai chuỗi trong ảnh chụp màn hình bên dưới, vui lòng lưu ý căn chỉnh bên phải mặc định điển hình cho các số và ngày tháng trong Excel:
Để tạo chuỗi ngày tăng dần một tháng và bắt đầu với một ngày cụ thể, sử dụng hàm SEQUENCE cùng với EDATE:
CHỈNH SỬA (start_date, SEQUENCE (12, 1, 0))
Các CHỈNH SỬA hàm trả về một ngày là số tháng được chỉ định trước hoặc sau ngày bắt đầu. Và hàm SEQUENCE tạo ra một mảng gồm 12 số (hoặc nhiều như bạn chỉ định) để buộc EDATE tiến lên theo gia số một tháng. Xin lưu ý rằng khởi đầu đối số được đặt thành 0, để ngày bắt đầu được đưa vào kết quả.
Với ngày bắt đầu trong B1, công thức có dạng như sau:
=EDATE(B1, SEQUENCE(12, 1, 0))
Tạo chuỗi năm trong Excel
Để tạo chuỗi ngày tăng dần theo năm, hãy sử dụng công thức chung sau:
DATE (SEQUENCE (n, 1 NĂM(start_date)), THÁNG(start_date), NGÀY(start_date))
Ở đâu n là số ngày bạn muốn tạo.
Trong trường hợp này, hàm DATE (năm, tháng, ngày) tạo ngày theo cách này:
- Năm được trả về bởi hàm SEQUENCE được cấu hình để tạo n các hàng theo 1 mảng số cột, bắt đầu từ giá trị năm từ start_date.
- tháng và ngày giá trị được lấy trực tiếp từ ngày bắt đầu.
Ví dụ: nếu bạn nhập ngày bắt đầu vào B1, công thức sau sẽ xuất ra một chuỗi 10 ngày với gia số một năm:
=DATE(SEQUENCE(10, 1, YEAR(B1)), MONTH(B1), DAY(B1))
Sau khi bị được định dạng là ngày tháng, kết quả sẽ như sau:
Tạo chuỗi thời gian trong Excel
Bởi vì thời gian được lưu trữ trong Excel dưới dạng số thập phân đại diện cho một phần nhỏ trong ngày, hàm SEQUENCE có thể hoạt động trực tiếp với thời gian.
Giả sử thời gian bắt đầu là B1, bạn có thể sử dụng một trong các công thức sau để tạo ra một chuỗi 10 lần. Sự khác biệt chỉ là ở bươc tranh luận. Vì có 24 giờ trong một ngày, hãy sử dụng 1/24 để tăng thêm một giờ, 1/48 để tăng thêm 30 phút, v.v.
Cách nhau 30 phút:
=SEQUENCE(10, 1, B1, 1/48)
Cách nhau 1 giờ:
=SEQUENCE(10, 1, B1, 1/24)
Cách nhau 2 giờ:
=SEQUENCE(10, 1, B1, 1/12)
Ảnh chụp màn hình bên dưới cho thấy kết quả:
Nếu bạn không muốn tính toán bước theo cách thủ công, bạn có thể xác định nó bằng cách sử dụng Hàm TIME:
SEQUENCE (hàng, cột, bắt đầu, TIME (giờ, phút, thứ hai))
Đối với ví dụ này, chúng tôi sẽ nhập tất cả các biến trong các ô riêng biệt như được hiển thị trong ảnh chụp màn hình bên dưới. Và sau đó, bạn có thể sử dụng công thức dưới đây để tạo chuỗi thời gian với bất kỳ kích thước bước tăng dần nào mà bạn chỉ định trong các ô E2 (giờ), E3 (phút) và E4 (giây):
=SEQUENCE(B2, B3, B4, TIME(E2, E3, E4))
Cách tạo lịch hàng tháng trong Excel
Trong ví dụ cuối cùng này, chúng ta sẽ sử dụng hàm SEQUENCE cùng với DATEVALUE và TUẦN để tạo lịch hàng tháng sẽ tự động cập nhật dựa trên năm và tháng mà bạn chỉ định.
Công thức trong A5 như sau:
=SEQUENCE(6, 7, DATEVALUE("1/"&B2&"https://www.ablebits.com/"&B1) - WEEKDAY(DATEVALUE("1/"&B2&"https://www.ablebits.com/"&B1)) + 1, 1)
Công thức này hoạt động như thế nào:
Bạn sử dụng hàm SEQUENCE để tạo 6 hàng (số tuần tối đa có thể trong một tháng) bằng 7 cột (số ngày trong một tuần) mảng ngày tăng lên 1 ngày. Vì thế hàng, cột và bươc lập luận không đưa ra câu hỏi.
Phần khó nhất trong khởi đầu tranh luận. Chúng tôi không thể bắt đầu lịch của mình với 1st ngày của tháng mục tiêu vì chúng tôi không biết đó là ngày nào trong tuần. Vì vậy, chúng tôi sử dụng công thức sau để tìm Chủ nhật đầu tiên trước ngày 1st ngày của tháng và năm được chỉ định:
DATEVALUE("1/"&B2&"https://www.ablebits.com/"&B1) - WEEKDAY(DATEVALUE("1/"&B2&"https://www.ablebits.com/"&B1)) + 1
Hàm DATEVALUE đầu tiên trả về một số sê-ri, trong hệ thống Excel nội bộ, đại diện cho 1st ngày trong tháng ở B2 và năm ở B1. Trong trường hợp của chúng tôi, đó là 44044 tương ứng với ngày 1 tháng 8 năm 2020. Tại thời điểm này, chúng tôi có:
44044 - WEEKDAY(DATEVALUE("1/"&B2&"https://www.ablebits.com/"&B1)) + 1
Hàm WEEKDAY trả về ngày trong tuần tương ứng với 1st ngày của tháng mục tiêu dưới dạng một số từ 1 (Chủ nhật) đến 7 (Thứ bảy). Trong trường hợp của chúng tôi, đó là 7 vì ngày 1 tháng 8 năm 2020 là thứ Bảy. Và công thức của chúng tôi giảm thành:
44044 - 7 + 1
44044 – 7 là 4403, tương ứng với Thứ Bảy, ngày 25 tháng 7 năm 2020. Vì chúng tôi cần Chủ nhật, chúng tôi thêm chỉnh sửa +1.
Bằng cách này, chúng tôi nhận được một công thức đơn giản xuất ra một mảng các số sê-ri bắt đầu bằng 4404:
=SEQUENCE(6, 7, 4404, 1)
Định dạng kết quả dưới dạng ngày tháng và bạn sẽ nhận được lịch hiển thị trong ảnh chụp màn hình ở trên. Ví dụ: bạn có thể sử dụng một trong các cách sau định dạng ngày tháng:
- d-mmm-yy để hiển thị các ngày như 1-8-20
- mmm d để hiển thị tháng và ngày như 20 tháng 8
- d chỉ hiển thị ngày
Chờ đã, nhưng chúng tôi muốn tạo lịch hàng tháng. Tại sao một số ngày của tháng trước và tháng sau lại hiển thị? Để ẩn những ngày không liên quan đó, hãy thiết lập quy tắc định dạng có điều kiện với công thức dưới đây và áp dụng phông chữ trắng màu sắc:
=MONTH(A5)<>MONTH(DATEVALUE($B$2 & "1"))
Trong đó A5 là ô ngoài cùng bên trái của lịch và B2 là tháng mục tiêu.
Để biết các bước chi tiết, vui lòng xem Cách tạo quy tắc định dạng có điều kiện dựa trên công thức trong Excel.
Đó là cách bạn có thể tạo chuỗi ngày trong Excel. Tôi cảm ơn bạn đã đọc và hy vọng sẽ gặp bạn trên blog của chúng tôi vào tuần tới!
The post Tạo chuỗi ngày tự động điền trong Excel với hàm Sequence appeared first on Kế Toán MVB | Dịch Vụ kế toán tại Hà Nội | Uy tín - Chất lượng.
source https://ketoanmvb.com/tao-chuoi-ngay-tu-dong-dien-trong-excel-voi-ham-sequence.html
Không có nhận xét nào:
Đăng nhận xét