Hàm EXPAND của Excel – Cách sử dụng

Hàm EXPAND cũng cho phép “đệm” (pad) các ô mới bằng một giá trị xác định.

Syntax

Cú pháp của hàm EXPAND như sau:

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

Arguments:

Hàm EXPAND nhận 4 đối số, mỗi đối số đóng một vai trò cụ thể trong việc mở rộng kích thước của một mảng.

• array – Đối số bắt buộc, đại diện cho mảng gốc hoặc phạm vi ô cần mở rộng. Nó có thể là một tham chiếu ô tới mảng hoặc một hàm Excel trả về một mảng.
• rows – Đối số tùy chọn, chỉ định tổng số hàng mà mảng mở rộng phải có. Giá trị của đối số rows (số dòng) phải là số nguyên dương. Nếu để trống, hàm sẽ giữ nguyên số hàng của mảng đầu vào.
• columns – Đối số tùy chọn, chỉ định tổng số cột mà mảng kết quả nên có. Giá trị của đối số columns (số cột) phải là số nguyên dương. Khi để trống, số cột sẽ giữ nguyên như mảng đầu vào.
• pad_with – Đối số tùy chọn, định nghĩa giá trị sẽ được dùng để lấp các ô mới tạo ra do việc mở rộng. Nếu không chỉ định, các ô mới sẽ được lấp bằng lỗi #N/A. Khi giá trị là chuỗi văn bản, cần đặt trong dấu ngoặc kép.

Đặc điểm quan trọng của hàm EXPAND

• Hàm EXPAND trả về một mảng mới và tràn vào các ô liền kề tùy theo kích thước.
• Nếu mảng kết quả quá lớn so với khả năng hiển thị của bảng tính, hàm sẽ trả về lỗi #NUM!.
• Nếu giá trị của rows hoặc columns là số âm hoặc nhỏ hơn số hàng/cột của mảng đầu vào, hàm sẽ trả về lỗi #VALUE!.

Ví dụ về hàm EXPAND

Vì hàm EXPAND chấp nhận bốn đối số khác nhau, chúng ta sẽ dùng các giá trị đầu vào đa dạng cho mỗi đối số để hiểu rõ hơn về cách hoạt động của nó.

Dưới đây là một bộ dữ liệu mẫu gồm hai hàng và hai cột.

Trường hợp 1: Chỉ nhập giá trị cho đối số array và để các đối số còn lại trống. Khi đó, hàm EXPAND trả về mảng gốc nguyên vẹn.

Trường hợp 2: Đặt giá trị rows là 3, để columns trống. Hàm EXPAND mở rộng mảng gốc lên ba hàng, giữ nguyên số cột. Vì pad_with cũng để trống, các ô mới được lấp bằng lỗi #N/A.

Trường hợp 3: Mở rộng mảng theo chiều ngang bằng cách cung cấp giá trị cho columns trong khi rows để trống. Ngoài ra, giá trị pad_with được đặt là dấu đô la ($). Kết quả là mảng có 3 cột và tất cả các ô bổ sung chứa dấu đô la.

Trường hợp 4: Sử dụng tất cả các đối số. Hàm mở rộng mảng 2×2 trong các ô B1:C2 thành mảng 3×3, và các ô mới được điền giá trị “X”.

Ví dụ 1 – Sử dụng đơn giản hàm EXPAND

Giả sử chúng ta có dữ liệu bán hàng hàng tháng của một cửa hàng cho các năm 2020 và 2021. Trong vài tháng cuối năm 2021, cửa hàng phải tạm ngưng hoạt động vì đang sửa chữa. Cửa hàng sẽ mở lại từ tháng 1/2024, nhưng vì lý do thuế, dữ liệu bán hàng cần được cập nhật.

Một cách để cập nhật dữ liệu là thủ công thêm 0 vào các ô tháng cho đến tháng 12/2023. Cách hiệu quả hơn là dùng hàm EXPAND để mở rộng phạm vi mảng gốc A2:C13 thành một mảng 12×5 (một hàng cho mỗi tháng và một cột cho mỗi năm) và đệm các ô mới bằng 0 để biểu thị không có doanh thu.

Công thức sẽ như sau:

=EXPAND(A2:C13,12,5,0)

Bây giờ chúng ta có thể dùng dữ liệu bán hàng đã cập nhật cho mục đích khai thuế.

Ví dụ 2 – Tách chuỗi thành mảng mở rộng

Giả sử chúng ta có một bộ dữ liệu chứa tên dự án và các thành viên trong nhóm được giao cho mỗi dự án trong tuần tới. Trong cột “Project Team”, tên các thành viên được ngăn cách bằng dấu phẩy kèm theo dấu cách.

Mục tiêu là mở rộng bảng để tạo các cột riêng cho mỗi ngày trong kế hoạch dự án, giúp dễ nhìn thấy những ngày không có ai được giao việc.

Bước đầu tiên là tách tên các thành viên ra thành các ô riêng bằng dấu phân cách là dấu phẩy kèm theo dấu cách. Công thức:

=TEXTSPLIT(B2,", ")

Sau khi dữ liệu đã ở các cột riêng nhờ hàm TEXTSPLIT, chúng ta có thể mở rộng mảng theo chiều ngang để tạo một cột cho mỗi ngày bằng hàm EXPAND và lấp các ô trống bằng dấu gạch ngang (-) để biểu thị không có ai được giao việc.

Số cột có thể thay đổi tùy vào số ngày dự án kéo dài; trong ví dụ này chúng ta tạo cho 5 ngày.

=EXPAND(TEXTSPLIT(B2,", "),,5,"-")

Kết quả sẽ cho chúng ta cái nhìn rõ hơn về ai đang làm việc trên mỗi dự án vào mỗi ngày, hỗ trợ quản lý và lập kế hoạch dự án.

Ví dụ 3 – Tạo Planner (Lịch công việc) bằng hàm EXPAND

Trong ví dụ này, chúng ta sẽ minh họa cách tạo một Planner (lịch công việc) hàng ngày linh hoạt, tự động điều chỉnh dựa trên số ngày của dự án. Người dùng nhập tên dự án, ngày bắt đầu và ngày kết thúc vào các ô riêng biệt. Sau đó dùng hàm EXPAND để tạo một Planner theo ngày dựa trên số ngày của dự án.

Người dùng nhập thông tin vào các ô A3, B3 và C3. Dùng hàm DAYS để tính số ngày giữa ngày bắt đầu và ngày kết thúc:

=DAYS(C3,B3)

Sau đó, dùng hàm EXPAND để mở rộng mảng hiện có thành một Planner bằng cách thêm các cột tương đương với độ dài dự án. Để bao gồm các chi tiết do người dùng nhập, chúng ta cộng thêm 3 cột. Cuối cùng, thêm một tiêu đề nhắc người dùng nhập nhiệm vụ hàng ngày cho mỗi cột.

=EXPAND(A3:C3,,DAYS(C3,B3)+3,"Enter Daily Task")

Bây giờ chúng ta có một Planner động giúp lập kế hoạch dự án một cách chi tiết hơn ở cấp độ người dùng.

Hàm EXPAND là một công cụ đa năng trong Excel; có hàm EXPAND trong bộ công cụ của bạn có thể nâng cao đáng kể năng suất làm việc. Hãy thực hành và khám phá các cách sử dụng mới, đồng thời chờ đón những hàm Excel hữu ích tiếp theo.