Các hàm Excel 365

Ngày tạo 17/05/2023

- 225 Lượt xem

1. Hàm EXPAND trong Excel

Hàm EXPAND trong Excel được thiết kế để biến một mảng có giá trị do bạn chọn vào kích thước hàng và cột mà bạn đã chỉ định.

Cú pháp bao gồm các đối số sau:

=EXPAND(array, rows, [columns], [pad_with])

array (bắt buộc) - mảng ban đầu.

rows (tùy chọn) - số hàng trong mảng được trả về. Nếu bỏ qua, các hàng mới sẽ không được thêm vào và đối số cột phải được đặt.

columns (tùy chọn) - số cột trong mảng được trả về. Nếu bị bỏ qua, các cột mới sẽ không được thêm vào và đối số hàng phải được đặt.

Pad_with - giá trị để điền vào các ô mới. Nếu bỏ qua, mặc định là #N/A.

2. Tính khả dụng của hàm EXPAND

Hiện tại, hàm EXPAND sẵn dùng trong Excel for Microsoft 365 (Windows và Mac) và Excel dành cho web.

3. Cách sử dụng hàm EXPAND trong Excel

Để tăng một mảng đến kích thước mong muốn, hãy xây dựng công thức EXPAND theo cách sau:

Đối với array, hãy cung cấp một dải ô hoặc một mảng giá trị được trả về bởi một công thức khác.

Đối với các rows và columns, hãy cung cấp các số dương lớn hơn số hàng và cột trong mảng ban đầu. Lưu ý rằng các đối số này xác định kích thước của mảng kết quả, không phải số lượng hàng hoặc cột cần thêm. Trong mỗi công thức cụ thể, phải đặt hàng hoặc cột. Đối số bị bỏ qua mặc định là kích thước tương ứng (hàng hoặc cột) của mảng nguồn.

Đối với pad_with, hãy chỉ định giá trị để điền vào các ô mới được thêm vào mảng. Nếu đó là một giá trị văn bản, hãy đặt nó trong dấu ngoặc kép. Nếu đó là một giá trị số, hãy sử dụng nó mà không có dấu ngoặc kép. Nếu không được cung cấp, các ô mới sẽ được đệm với lỗi #N/A.

Là một hàm mảng động, EXPAND chỉ cần được nhập vào một ô và nó sẽ tự động đưa kết quả vào nhiều ô như đã chỉ định trong các đối số hàng và cột.

Ví dụ: để mở rộng mảng B4:C11 thành 12 hàng và 3 cột, công thức là:

Ta có thể thử với việc bỏ qua tham số rows và columns và xem kết quả trả về.

Khi bạn bỏ qua tham số rows thì mảng kết quả trả về sẽ lấy rows = số dòng của array bạn đưa vào.

Tương tự với việc bỏ qua tham số columns thì mảng kết quả trả về sẽ lấy columns = số cột của array bạn đưa vào.

Trong trường hợp bạn bỏ qua cả tham số rows và columns thì mảng kết quả trả về sẽ bằng chính array ban đầu bạn nhập vào.

Dưới đây, bạn sẽ tìm thấy thêm một số ví dụ về cách sử dụng hàm EXPAND trong Excel để phát triển một mảng theo một hướng cụ thể.

4. Mở rộng mảng đến một số hàng nhất định

Để mở rộng một mảng xuống số lượng hàng mong muốn, hãy xác định đối số hàng và bỏ qua các cột.

Chẳng hạn, để tăng mảng ban đầu lên tổng cộng 12 hàng, hãy sử dụng công thức sau:

=EXPAND(B4:C11,12,,"-")

Vì các cột không được kéo dài nên chúng tôi nhập dấu phẩy sau các hàng và sau dấu phẩy nhập giá trị đệm (pad_with)

5. Mở rộng mảng đến một số cột nhất định

Để thêm nhiều cột hơn vào một mảng, hãy xác định đối số cột và bỏ qua các hàng.

Ví dụ: đây là cách bạn có thể mở rộng mảng nguồn sang bên phải để mảng có tổng cộng 4 cột:

6. Cách mở rộng một mảng sang trái và lên trên

Hàm EXPAND được thiết kế để mở rộng một mảng sang phải và xuống dưới. Để thêm các ô vào bên trái và bên trên của mảng nguồn, bạn sẽ phải tìm ra giải pháp của riêng mình.

Giả sử bạn có một mảng giá trị trong B4:C15. Bạn nhắm thêm 3 hàng phía trên và 1 cột bên trái mảng ban đầu. Để hiểu rõ hơn về cơ chế, trước tiên chúng ta sẽ thực hiện từng nhiệm vụ riêng lẻ, sau đó ghép tất cả các phần lại với nhau.

6.1. Thêm cột vào bên trái

Để mở rộng mảng theo chiều ngang, bạn có thể sử dụng hàm HSTACK để nối thêm một số cột trống nhất định vào bên trái mảng của bạn. Trong ví dụ này, chúng tôi thêm 1 cột trống bằng công thức sau:

Lưu ý rằng một mảng trống mà bạn xếp chồng (D4:D15) phải có cùng số hàng với mảng nguồn và phải được cung cấp trước trong công thức. Kết quả là, một cột có giá trị 0 được thêm vào bên trái của mảng ban đầu.

Bạn có thể tham khảo công thức ở hình dưới đây.

6.2. Thêm hàng ở trên

Để mở rộng mảng theo chiều dọc, hãy tận dụng hàm VSTACK để đặt một số hàng trống nhất định phía trên hàng đầu tiên của mảng ban đầu của bạn.

Trong trường hợp của chúng tôi, hai phạm vi sau có cùng số lượng cột được xếp chồng lên nhau, một mảng trống xuất hiện trước:

Kết quả là 3 hàng có giá trị bằng 0 được thêm vào đầu mảng ban đầu.

6.3. Thêm các hàng ở trên và các cột ở bên trái

Để mở rộng một mảng theo chiều dọc và chiều ngang, hãy lồng hàm VSTACK và HSTACK vào nhau. Trong trường hợp của chúng tôi, công thức có dạng này:

6.4. Đệm một mảng lên và sang trái với giá trị tùy chỉnh

Theo mặc định, hàm VSTACK và HSTACK trả về số không thay vì ô trống. Để đệm các ô mới bằng một giá trị tùy chỉnh, hãy lồng tổ hợp IF và ISBLANK trong mỗi hàm ngăn xếp, hàm này sẽ thay thế các khoảng trống bằng giá trị bạn chỉ định.

Ví dụ: để các ô mới được đệm bằng dấu gạch nối, công thức là:

Để mở rộng một mảng với các ô trống, hãy sử dụng công thức sau:

7. Tách chuỗi và mở rộng mảng

Ví dụ này cho thấy cách sử dụng các hàm EXPAND và TEXTSPLIT cùng nhau để phân tách một chuỗi văn bản bằng một dấu phân cách cụ thể trong các cột riêng lẻ, sau đó phát triển mảng cuối cùng thành kích thước mong muốn.

Giả sử bạn có một cột gồm các chuỗi như cột B trong hình bên dưới. Mỗi chuỗi có một số giá trị được phân tách bằng dấu phân cách cụ thể (dấu phẩy và dấu cách trong trường hợp của chúng tôi). Mục tiêu của bạn là chia từng chuỗi thành các ô riêng biệt để tất cả các mảng thu được có cùng số cột.

Để hoàn thành nhiệm vụ, hãy nhập công thức này vào ô E4:

Sau đó bạn hãy kéo công thức xuống nhé.

8. Hàm EXPAND không hoạt động

Nếu hàm EXPAND hoạt động không chính xác trong Excel của bạn hoặc gây ra lỗi, các mẹo sau đây sẽ giúp bạn xác định nguyên nhân và khắc phục nó.

8.1. Lỗi #VALUE!

Xảy ra nếu giá trị được sử dụng cho đối số hàng hoặc cột nhỏ hơn tổng số hàng hoặc cột của mảng nguồn.

8.2. Lỗi #N/A

Xuất hiện trong các ô được đệm nếu đối số pad_with không được đặt.

8.3. Lỗi #NAME?

Xảy ra nếu tên của hàm bị viết sai chính tả hoặc ham EXPAND không có sẵn trong phiên bản Excel của bạn. Hiện tại, nó chỉ được hỗ trợ trong Excel 365 và Excel dành cho web.