Excel – Hàm VSTACK: Cách sử dụng

Hàm VSTACK đặc biệt hữu ích khi làm việc với dữ liệu lớn cần được kết hợp thành một bộ dữ liệu hợp nhất.

Cú pháp

Cú pháp của hàm VSTACK như sau.

=VSTACK(array1, [array2], …)

Đối số

Hàm VSTACK chấp nhận các đối số sau.

• array1 – Đối số bắt buộc, là mảng hoặc vùng ô đầu tiên cần ghép.
• array2 – Đối số tùy chọn, là vùng ô hoặc mảng sẽ được ghép phía dưới array1.
• Các đối số tiếp theo đều là tùy chọn và cho phép nhập thêm các mảng hoặc vùng ô khác để ghép.

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

Một tính năng nổi bật của hàm VSTACK là khả năng tạo ra một mảng động. Nếu bất kỳ thay đổi nào được thực hiện trên các đối số mảng đầu vào, chúng sẽ tự động được phản ánh trong mảng kết quả. Các đặc điểm đáng chú ý khác bao gồm:

• Tổng số hàng của mảng kết quả bằng tổng số hàng của các mảng hoặc vùng ô đầu vào.
• Tổng số cột của mảng kết quả bằng số cột tối đa trong các mảng được chỉ định.
• Nếu mảng đầu vào chứa các ô trống, hàm VSTACK sẽ trả về ô trống (không phải số 0).
• Nếu array1 rỗng, VSTACK sẽ trả về lỗi #VALUE!.

Ví dụ về hàm VSTACK

Dưới đây là một số ví dụ minh họa cách VSTACK hoạt động với các giá trị đầu vào khác nhau.

Ví dụ đầu tiên là một trường hợp điển hình, trong đó chúng ta muốn xếp chồng hai vùng ô B2:B3 và C2:C3 theo chiều dọc. Vùng ô thứ hai sẽ được đặt dưới vùng ô thứ nhất.

Trong ví dụ thứ hai, chúng ta hợp nhất dữ liệu từ hai hàng và muốn xếp chồng chúng lên nhau. Ví dụ cuối cùng minh họa cách sử dụng hàm VSTACK để ghép mảng tĩnh với vùng ô. Vì mảng tĩnh là đối số đầu tiên, nó sẽ xuất hiện ở trên cùng, còn vùng ô sẽ được nối dưới.

Khi đã nắm được cách dùng VSTACK để kết hợp dữ liệu từ các nguồn khác nhau thành một mảng duy nhất, chúng ta sẽ khám phá các trường hợp thực tế để hiểu sâu hơn về hàm này.

Ví dụ 1 – Sử dụng VSTACK đơn giản

Giả sử bạn làm việc cho một công ty bán các sản phẩm thuộc nhiều danh mục như điện tử, thời trang và đồ gia dụng. Mỗi danh mục có bảng ghi doanh thu riêng, nhưng bạn muốn hợp nhất toàn bộ dữ liệu doanh thu để phân tích và báo cáo toàn diện.

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

=VSTACK(A2:D6, A10:D13, A17:D20)

Chúng ta lấy vùng ô A2:D6 từ bộ dữ liệu đầu tiên. Đối với hai bộ dữ liệu tiếp theo (A10:D13 và A17:D20) chúng ta bỏ qua hàng tiêu đề vì tiêu đề đã chung cho tất cả các bộ dữ liệu và không muốn lặp lại trong kết quả hợp nhất.

Đây là một trường hợp điển hình của VSTACK: dữ liệu đã được gộp lại thành một bảng duy nhất để tiếp tục phân tích. Đôi khi các bộ dữ liệu cần ghép có kích thước khác nhau.

Ví dụ 2 – Ghép các mảng có kích thước khác nhau bằng VSTACK

Trong trường hợp này, công ty của bạn thu thập thông tin khách hàng qua nhiều nguồn như biểu mẫu trực tuyến, email marketing và mạng xã hội. Để lọc và sử dụng dữ liệu một cách hiệu quả, việc hợp nhất chúng là rất cần thiết.

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

=VSTACK(A2:E6, A10:D12, A16:D19)

Vì kích thước các bộ dữ liệu khác nhau, mảng nhỏ hơn sẽ được lấp đầy bằng giá trị lỗi #N/A. Để loại bỏ lỗi này, chúng ta có thể dùng hàm IFERROR để thay thế bằng một giá trị phù hợp, ví dụ là chuỗi rỗng:

=IFERROR(VSTACK(A2:E6, A10:D12, A16:D19), "")

Ví dụ 3 – Kết hợp mảng duy nhất (Unique) bằng VSTACK

Giả sử bạn là nhà phân tích bán hàng và đã nhận được dữ liệu bán hàng từ website công ty và cửa hàng thực tế. Bạn cần hợp nhất và làm sạch dữ liệu để phân tích.

Dữ liệu thường lớn và có thể chứa các bản sao, vì vậy việc kết hợp hàm VSTACK với UNIQUE sẽ rất hữu ích.

Công thức ghép dữ liệu bán hàng:

=VSTACK(A2:D7, A11:D14)

Để loại bỏ các bản sao, chúng ta dùng hàm UNIQUE:

=UNIQUE(VSTACK(A2:D7, A11:D14))

Sau khi dữ liệu sạch, để sắp xếp từ doanh thu cao tới thấp, chúng ta có thể dùng hàm SORT:

=SORT(UNIQUE(VSTACK(A2:D7, A11:D14)), 4, -1)

Các đối số thêm vào cho hàm SORT là 4 và -1. Số 4 là cột trong mảng mà dữ liệu sẽ được sắp xếp theo, còn -1 chỉ sắp xếp giảm dần. Kết quả là dữ liệu đã được xếp chồng, loại bỏ bản sao và sắp xếp:

Ví dụ 4 – Ghép dữ liệu qua nhiều bảng tính bằng VSTACK

Trong ví dụ này, chúng ta có các bảng Excel riêng chứa thông tin nhân viên từ các phòng ban khác nhau (ID, tên, tuổi, chức vụ, lương, đánh giá). Bây giờ chúng ta cần tổng hợp toàn bộ dữ liệu vào một bảng để phân tích hiệu suất hàng năm.

Dữ liệu trong các bảng có cùng định dạng và có ba bảng tên ‘Dept 1 – Sales’, ‘Dept 2 – Marketing’, và ‘Dept 3 – Finance’.

Vì hàng tiêu đề (các tiêu đề cột) giống nhau ở mọi bảng, một cách là sao chép thủ công vào một bảng mới. Cách khác là đưa trực tiếp tiêu đề vào công thức, ví dụ:

{"Employee ID","Employee Name","Age","Job Title","Salary","Performance Evaluation"}

Bây giờ, với hàm VSTACK, chúng ta có thể ghép mảng tĩnh với dữ liệu từ các bảng khác nhau:

=VSTACK({"Employee ID","Employee Name","Age","Job Title","Salary","Performance Evaluation"},

'Dept 1 - Sales'!A2:F11,
'Dept 2 - Marketing'!A2:F7,
'Dept 3 - Finance'!A2:F7)

Kết quả là dữ liệu đã được tổng hợp trong một bảng. Nếu có tới 20 bảng, việc chọn từng bảng sẽ rất kém hiệu quả. Thay vào đó, chúng ta có thể dùng tham chiếu 3D – một cách động để chọn các vùng ô trải qua nhiều bảng tính.

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

=VSTACK('Dept 1 - Sales:Dept 3 - Finance'!A2:F15)

Công thức này ghép vùng ô A2:F15 từ tất cả các bảng tính từ “Dept 1 – Sales” đến “Dept 3 – Finance”. Vì kích thước dữ liệu ở mỗi bảng có thể khác nhau, những khoảng trống sẽ được thay bằng 0. Để loại bỏ các 0 này, chúng ta có thể dùng hàm FILTER kết hợp với TAKE để lọc ra các giá trị khác 0:

=FILTER(VSTACK('Dept 1 - Sales:Dept 3 - Finance'!A2:F15),

TAKE(VSTACK('Dept 1 - Sales:Dept 3 - Finance'!A2:F15), ,1)<>0)

Điểm mạnh của giải pháp này là nó tự động bao gồm bất kỳ bảng mới nào được thêm vào giữa các bảng trong tham chiếu 3D, tạo nên một cách tiếp cận rất linh hoạt để xếp chồng dữ liệu.

Hàm VSTACK vs HSTACK

Giờ chúng ta đã biết hàm VSTACK dùng để ghép dữ liệu theo chiều dọc, trong khi hàm HSTACK dùng để nối dữ liệu theo chiều ngang. Hai hàm này cho phép chúng ta kết hợp dữ liệu một cách chọn lọc; VSTACK hoạt động theo chiều dọc (vertical) và HSTACK hoạt động theo chiều ngang (horizontal).

Hãy xem một ví dụ đơn giản để hiểu rõ sự khác biệt:

Chúng ta có hai vùng ô nhỏ B1:B2 và C1:C2. Mục tiêu là ghép chúng cả theo chiều dọc và chiều ngang.

=VSTACK(B1:B2, C1:C2)

=HSTACK(B1:B2, C1:C2)

Như mong muốn, hàm VSTACK đã ghép hai vùng ô lại với nhau theo chiều dọc (đặt lên trên nhau), trong khi hàm HSTACK ghép chúng theo chiều ngang (đặt cạnh nhau).

Hãy thực hành và khám phá các ứng dụng mới của hàm VSTACK; nó là công cụ tuyệt vời để làm sạch dữ liệu và được sử dụng rộng rãi trong phân tích dữ liệu. Trong thời gian tới, chúng tôi sẽ trở lại với một hàm Excel khác để mở rộng bộ công cụ của bạn.