Cách sử dụng hàm SEQUENCE trong Excel

Tác giả Starlink, T.M.Một 29, 2024, 04:10:20 CHIỀU

« Chủ đề trước - Chủ đề tiếp »

0 Thành viên và 1 Khách đang xem chủ đề.

Hàm SEQUENCE của Excel cho phép bạn tạo danh sách các số theo trình tự ngay lập tức. Nó cũng cho phép bạn xác định hình dạng, số lượng giá trị và các bước giữa mỗi số trong trình tự và bạn có thể sử dụng SEQUENCE kết hợp với các hàm Excel khác.

Hàm SEQUENCE chỉ được hỗ trợ trong Excel 365 và Excel 2021 trở lên.


1. Cú pháp SEQUENCE

Hàm SEQUENCE có bốn đối số:

Mã nguồn [Chọn]
=SEQUENCE(a,b,c,d)
Trong đó:

    a (bắt buộc) là số hàng mà chuỗi sẽ kéo dài (xuống dưới),
    b (tùy chọn) là số cột mà nó sẽ trải dài (trên),
    c (tùy chọn) là số bắt đầu trong chuỗi và
    d (tùy chọn) là sự gia tăng giữa mỗi giá trị trong chuỗi.

Đối số a và b (kích thước của mảng kết quả) phải là số nguyên (hoặc công thức xuất ra số nguyên), trong khi đối số c và d (số bắt đầu và số gia trong chuỗi) có thể là số nguyên hoặc số thập phân. Nếu đối số d là 0, kết quả sẽ lặp lại cùng một số, vì bạn đang yêu cầu Excel không thêm bất kỳ số gia nào giữa mỗi giá trị trong mảng.

Nếu bạn chọn bỏ qua bất kỳ đối số tùy chọn nào ( b, c hoặc d ), chúng sẽ mặc định là 1. Ví dụ, nhập

Mã nguồn [Chọn]
=SEQUENCE(2,,10,3)
sẽ trả về một chuỗi có chiều cao một cột, vì đối số b bị thiếu.

SEQUENCE là một công thức mảng động, nghĩa là nó có thể tạo ra một mảng tràn. Nói cách khác, mặc dù công thức được nhập vào một ô, nếu đối số a hoặc b lớn hơn 1, kết quả sẽ tràn sang nhiều ô.

2. Cách thức hoạt động của hàm SEQUENCE

Trước khi tôi chỉ cho bạn một số biến thể và cách sử dụng thực tế của SEQUENCE, đây là một ví dụ đơn giản minh họa cách thức hoạt động của nó.

Trong ô A1, tôi đã nhập

Mã nguồn [Chọn]
=SEQUENCE(3,5,10,5)
nghĩa là chuỗi có chiều cao ba hàng và chiều rộng năm cột. Chuỗi bắt đầu bằng số 10, và mỗi số tiếp theo tăng thêm năm so với số trước đó.


3. Điền xuống rồi ngang qua: TRANSPOSE

Trong ví dụ trên, bạn có thể thấy chuỗi đầu tiên được điền vào các cột, sau đó là các hàng. Tuy nhiên, bằng cách nhúng hàm SEQUENCE vào hàm TRANSPOSE, bạn có thể buộc Excel điền các số xuống các hàng, sau đó là các cột.

Ở đây, tôi đã nhập cùng một công thức như trong ví dụ trên, nhưng tôi cũng nhúng nó vào trong TRANSPOSE.

Mã nguồn [Chọn]
=TRANSPOSE(SEQUENCE(3,5,10,5))
Kết quả là, Excel đã đảo ngược các đối số a và b trong cú pháp, nghĩa là "3" bây giờ biểu thị số cột và "5" biểu thị số hàng. Bạn cũng có thể thấy các số được điền xuống và sau đó điền ngang.


4. Tạo một chuỗi số La Mã

Nếu bạn muốn tạo một chuỗi số La Mã (I, II, III, IV) thay vì các số Ả Rập (1, 2, 3, 4), bạn sẽ cần nhúng công thức SEQUENCE vào hàm ROMAN.

Sử dụng các tham số giống như trong ví dụ trên, tôi đã nhập

Mã nguồn [Chọn]
=ROMAN(SEQUENCE(3,5,10,5))
vào ô A1 để tạo ra kết quả này:


Tiến thêm một bước nữa, giả sử tôi muốn các chữ số La Mã là chữ thường. Trong trường hợp này, tôi sẽ nhúng toàn bộ công thức vào hàm LOWER.

Mã nguồn [Chọn]
=LOWER(ROMAN(SEQUENCE(3,5,10,5)))

5. Sử dụng SEQUENCE để tạo ngày

Một cách sử dụng thực tế hơn của hàm SEQUENCE là tạo một chuỗi ngày. Trong ví dụ bên dưới, tôi muốn tạo một báo cáo chứa lợi nhuận hàng tuần của mỗi người, bắt đầu từ thứ Sáu, ngày 1 tháng 3 và tiếp tục vào mỗi thứ Sáu trong 20 tuần.

Để làm điều này, tôi đã nhập

Mã nguồn [Chọn]
=SEQUENCE(1,20,DATE(2024,3,1),7)
vào B2, vì tôi muốn ngày tháng chạy trên hàng đầu tiên của 20 cột, bắt đầu từ thứ Sáu, ngày 1 tháng 3 và tăng dần bảy ngày từ giá trị này sang giá trị tiếp theo.

Trước khi thêm ngày vào ô, đặc biệt là nếu tạo bằng công thức, trước tiên bạn nên thay đổi định dạng số của ô thành "Ngày" trong nhóm Số của tab Trang chủ trên ruy-băng. Nếu không, Excel có thể trả về số sê-ri thay vì ngày.


6. Làm cho SEQUENCE phụ thuộc vào một tham số khác

Trong ví dụ này, tôi có một danh sách các tác vụ cần được đánh số. Tôi muốn Excel tự động thêm một số khác khi tôi thêm một tác vụ khác (hoặc tương tự, xóa một số khi tôi hoàn thành và xóa một tác vụ).


Để làm điều này, trong ô A2, tôi đã nhập

Mã nguồn [Chọn]
=SEQUENCE(COUNTA(B:B)-1)
Số hàng mà chuỗi điền vào bây giờ phụ thuộc vào số ô trong cột B có chứa văn bản (nhờ hàm COUNTA ) và tôi đã thêm "-1" vào cuối công thức để phép tính COUNTA loại bỏ hàng tiêu đề.

Bạn cũng sẽ nhận thấy rằng tôi chỉ chỉ định đối số a (số hàng) trong công thức SEQUENCE của tôi, vì việc bỏ qua tất cả các đối số khác sẽ mặc định chúng là 1, đó là điều tôi muốn trong ví dụ này. Nói cách khác, tôi muốn kết quả chỉ chiếm một cột, số bắt đầu từ 1 và chuỗi tăng thêm một mỗi lần.


Bây giờ, khi tôi thêm một mục vào danh sách trong cột B, số thứ tự trong cột A sẽ tự động cập nhật.


7. Những điều cần lưu ý khi sử dụng SEQUENCE

Có ba lưu ý cần lưu ý khi sử dụng hàm SEQUENCE trong Excel:

    Các công thức mảng động tạo ra các mảng tràn—bao gồm cả SEQUENCE—không thể được sử dụng trong các bảng Excel được định dạng. Cách khắc phục tốt nhất nếu bạn muốn sử dụng SEQUENCE trong dữ liệu hiện có là chuyển đổi bảng Excel được định dạng của bạn thành một phạm vi chưa định dạng bằng cách chọn một trong các ô trong bảng và nhấp vào "Chuyển đổi thành phạm vi" trong nhóm Công cụ của tab Thiết kế bảng.
    Nếu bạn tạo mảng động liên kết hai sổ làm việc, điều này sẽ chỉ hoạt động nếu cả hai sổ làm việc đều mở. Ngay khi bạn đóng sổ làm việc nguồn, công thức mảng động trong sổ làm việc đang hoạt động sẽ trả về lỗi #REF!.
    Việc ngắt một mảng bị tràn bằng cách đặt một giá trị khác vào ô bị ảnh hưởng sẽ làm hỏng hàm SEQUENCE của bạn và dẫn đến lỗi #SPILL !.

8. Tại sao nên sử dụng SEQUENCE thay vì Fill Handle?

Một giải pháp thay thế cho hàm SEQUENCE là trình xử lý điền của Excel, bạn có thể nhấp và kéo để tiếp tục chuỗi mà bạn đã bắt đầu:


Tuy nhiên, có một số lý do tại sao tôi thích sử dụng hàm SEQUENCE hơn:

    Nếu bạn muốn tạo một chuỗi dài, việc kéo có thể mất rất nhiều thời gian!
    Dễ dàng hơn để sửa đổi các tham số của chuỗi trong hàm SEQUENCE—chỉ cần điều chỉnh các đối số trong công thức của bạn. Khi bạn nhấp và kéo tay cầm điền, bạn phải nhớ chọn nhiều hơn một số trong mảng hiện có.
    Nếu bạn xóa các hàng hoặc cột tương tác với chuỗi của bạn, các số được tạo thông qua trình xử lý điền cũng sẽ bị xóa. Tuy nhiên, vì SEQUENCE tạo ra một mảng bị tràn, chúng vẫn giữ nguyên, ngay cả khi bạn cấu trúc lại bảng tính của mình.
    Fill handle của Excel được thiết kế để điền vào một chuỗi dọc theo một hàng hoặc một cột. Để tạo một chuỗi bằng fill handle bao phủ nhiều hàng và cột, bạn phải thực hiện nhiều bước hơn so với khi sử dụng hàm SEQUENCE, cho phép bạn chỉ định tất cả các tham số cùng một lúc.
    Hàm SEQUENCE loại bỏ mọi lỗi của con người có thể xảy ra khi sử dụng nút điền.

Nếu bạn sử dụng SEQUENCE với một hàm biến động, chẳng hạn như DATE, điều này có thể khiến sổ làm việc Excel của bạn chậm đi đáng kể, đặc biệt là nếu bạn đã có nhiều dữ liệu trong bảng tính của mình. Vì vậy, hãy cố gắng hạn chế số lượng hàm biến động mà bạn sử dụng để đảm bảo bảng tính Excel của bạn hoạt động nhanh chóng và hiệu quả.