1. Hàm VSTACK

Hàm VSTACK trong Excel kết hợp nhiều mảng hoặc phạm vi theo chiều dọc thành một mảng duy nhất. Mỗi mảng tiếp theo được thêm vào ô trống đầu tiên ở dưới cùng của mảng trước đó.

Cú pháp:

Hàm VSTACK nhận các đối số sau:

=VSTACK(array1, [array2],…)

Trong đó mảng là phạm vi ô hoặc mảng để nối. array1 là bắt buộc; array2 và những mảng tiếp theo là tùy chọn.

Bạn có thể tham khảo ví dụ nối các vùng lại với nhau bằng cách sử dụng hàm VSTACK như sau.

2. Hàm HSTACK

Hàm HSTACK trong Excel kết hợp nhiều mảng hoặc phạm vi theo chiều ngang thành một mảng duy nhất. Mỗi mảng tiếp theo được thêm bên phải của mảng trước đó.

Cú pháp:

Hàm HSTACK nhận các đối số sau:

=HSTACK(array1, [array2],…)

Trong đó mảng là phạm vi ô hoặc mảng để nối. array1 là bắt buộc; array2 và những mảng tiếp theo là tùy chọn.

Bạn có thể tham khảo ví dụ nối các vùng lại với nhau bằng cách sử dụng hàm HSTACK như sau.

3. Tính khả dụng của hàm VSTACK và HSTACK

Cả 2 hàm VSTACK và HSTACK hiện tại chỉ có thể sử dụng được trên Office 365 phiên bản Personal và Office Online.

Tại thời điểm mình viết bài trên thì 2 hàm trên vẫn chưa sẵn có trên Office 365 phiên bản Business và Enterprise.

4. Những điều cần phải nhớ về hàm VSTACK và HSTACK

Để sử dụng hiệu quả các hàm VSTACK và HSTACK trong trang tính của bạn, đây là một số thông tin chính cần lưu ý:

- Các hàm có thể hợp nhất các phạm vi ô, phạm vi đã đặt tên, hằng số mảng và mảng động được trả về bởi các công thức khác.

- Công thức chỉ được nhập vào một ô - ô ngoài cùng bên trái của phạm vi đích. Mảng kết quả sẽ tự động tràn vào các ô có thể nếu cần.

- Đầu ra là hoàn toàn động. Ngay khi xảy ra bất kỳ thay đổi nào trong mảng ban đầu, mảng được trả về sẽ được cập nhật ngay lập tức.

- Để tự động đưa dữ liệu mới vào mảng kết quả mà không cần cập nhật công thức, bạn có thể sử dụng mảng nguồn là dữ liệu ở dạng Table hoặc sử dụng mảng động.

5. Làm thế nào để hợp nhất các mảng trong Excel và ví dụ minh hoạ

Với các hàm VSTACK và HSTACK, việc kết hợp các phạm vi trong Excel thực sự dễ dàng và đơn giản. Các ví dụ dưới đây sẽ cung cấp cho bạn một số nguồn cảm hứng để bắt đầu và hiển thị các phương pháp tiếp cận thực tiễn tốt nhất.

5.1. Hợp nhất các mảng theo chiều dọc và chiều ngang

Tùy thuộc vào cấu trúc của các mảng ban đầu và bố cục của trang tính, bạn có thể muốn kết hợp các phạm vi theo chiều dọc hoặc chiều ngang.

Để hợp nhất nhiều phạm vi thành một ngăn xếp ngang duy nhất, hãy sử dụng hàm HSTACK. Trong trường hợp này, các phạm vi được xếp chồng lên nhau. Ví dụ:

Để kết hợp nhiều phạm vi thành một ngăn xếp dọc duy nhất, hãy sử dụng chức năng VSTACK. Trong trường hợp này, các phạm vi được đặt bên dưới nhau. Ví dụ:

5.2. Hợp nhất các phạm vi và hằng số mảng

Trong các trường hợp khi tiêu đề cột hoặc một số giá trị bị thiếu trong phạm vi ban đầu, bạn có thể cung cấp dữ liệu bị thiếu trong một hằng số mảng trực tiếp cho hàm VSTACK hoặc HSTACK. Ví dụ:

5.3. Kết hợp nhiều bảng Excel thành 1

Ngoài các mảng và phạm vi, các hàm VSTACK và HSTACK cũng có thể hợp nhất nhiều bảng Excel thành một. Cho rằng các bảng Excel tự động mở rộng để bao gồm dữ liệu mới, đây là một giải pháp hoàn hảo cho các tập dữ liệu có kích thước thay đổi. Chỉ cần cung cấp các tham chiếu bảng tới VSTACK và HSTACK, và bạn sẽ không phải lo lắng về việc cập nhật công thức của mình cho dù có bao nhiêu hàng/cột dữ liệu mới được thêm vào bảng nguồn.

=VSTACK(Table1, Table2)

5.4. Hợp nhất các Dynamic Array

Nếu các mảng bạn đang kết hợp là mảng động, tức là được trả về bởi công thức mảng động, thì bạn có thể nối các mảng đó bằng cách sử dụng toán tử phạm vi tràn, là một thẻ băm (#) được thêm vào địa chỉ của ô phía trên bên trái trong phạm vi tràn.

Ví dụ: để kết hợp hai mảng được tạo bởi hàm UNIQUE trong ảnh chụp màn hình bên dưới, bạn có thể sử dụng công thức sau:

=VSTACK(E3#, E12#)

Trong đó E3 là ô trên cùng của mảng đầu tiên và E12 là ô trên cùng của mảng thứ hai.

Lợi ích của phương pháp này là với bất kỳ sự thay đổi nào trong dữ liệu nguồn, cả 3 mảng sẽ được Excel tính toán lại và cập nhật tự động.

5.5. Xử lý các ô trống khi sử dụng VSTACK

Trong trường hợp phạm vi nguồn chứa các ô trống, mảng được trả về sẽ có các số 0 ở vị trí của các ô trống. Để khắc phục điều này, bạn có thể lồng hàm VSTACK hoặc HSTACK vào đối số đầu tiên của SUBSTITUTE và yêu cầu hàm này thay thế các số 0 bằng các chuỗi rỗng (""). Ví dụ:

Nếu các mảng ban đầu của bạn chứa các giá trị bằng không, thì hãy thay thế các chuỗi trống thay vì các số không để tránh thay thế hoàn toàn tất cả các số không trong mảng được trả về. Ví dụ:

Thay thế một chuỗi trống bằng một chuỗi trống có vẻ phi logic, nhưng nó hoạt động. Trong một mảng xếp chồng lên nhau, Excel chỉ hiển thị các số 0 thay vì khoảng trống để hiển thị rõ hơn.

5.6. Kết hợp các phạm vi và loại bỏ các hàng trống

Khi có bất kỳ hàng hoặc cột trống nào trong phạm vi nguồn, mảng kết quả sẽ có các hàng hoặc cột không được điền vào vị trí của chúng. Để loại bỏ các hàng và/hoặc cột như vậy, bạn có thể lọc ra các khoảng trống với sự trợ giúp của hàm FILTER.

Ý tưởng chung là cung cấp đầu ra VSTACK hoặc HSTACK cho đối số mảng của hàm FILTER và sử dụng lệnh để lọc các mục nhập không trống (<>"").

Về cơ bản có thể có 3 trường hợp như sau.

5.6.1. Trường hợp 1: Lọc ra các khoảng trống dựa trên cột chính

5.6.2. Xoá các hàng hoàn toàn trống

5.6.3. Xoá bỏ các hàng có chứa ô trống

5.7. Kết hợp các vùng lại và bỏ qua các giá trị trùng lặp

Để chỉ kết hợp các bản ghi duy nhất từ ​​hai phạm vi trở lên, hãy sử dụng hàm UNIQUE.

Giá trị logic TRUE trong đối số thứ 2 của UNIQUE cho biết hàm so sánh dữ liệu giữa các cột, không phải hàng.

5.8. Kết hợp các mảng và sắp xếp mảng cùng lúc

Khi hợp nhất các mảng, bạn hoàn toàn có thể sắp xếp kết quả này theo ý mà bạn muốn. Điều này khá dễ thực hiện bằng cách lồng HSTACK hoặc VSTACK vào một hàm mảng động khác - SORT.

Bạn có thể tham khảo ví dụ sau.

5.9. Cách kết hợp các mảng có kích thước khác nhau mà không bị lỗi

Khi hợp nhất hai hoặc nhiều mảng có kích thước khác nhau, Excel "mở rộng" (các) mảng nhỏ hơn để khớp với kích thước của mảng lớn nhất và trả về lỗi #N/A thay cho các giá trị bị thiếu. Để nắm bắt và xử lý các lỗi đó, bạn có thể sử dụng hàm IFNA trong Excel 2013 trở lên hoặc hàm IFERROR trong các phiên bản cũ hơn.

Trong công thức bên dưới, IFNA được định cấu hình để thay thế lỗi #N/A bằng các chuỗi trống (""), trông giống như các ô trống đối với mắt người.

6. Hàm VSTACK và HSTACK không hoạt động

Nếu các chức năng không hoạt động như mong đợi hoặc không có sẵn trong Excel của bạn, bạn có thể gặp phải một trong các lỗi này.

6.1. Lỗi #NAME?

Hàm DROP hiện chỉ có sẵn trên Office 365 (phiên bản Window và MacOS – phiên bản Personal dành cho cá nhân) và Office Online trên Web.

Hiện ở thời điểm bài viết thì hàm DROP chưa được cập nhật trên phiên bản Office 365 Business và Enterprise.

Khi đó nếu bạn sử dụng hàm DROP thì sẽ trả về kết quả là lỗi #NAME.

6.2. Lỗi #SPILL!

Trong trường hợp không có đủ ô trống bên dưới hoặc/và bên phải công thức, sẽ xảy ra lỗi #SPILL. Để khắc phục, chỉ cần xóa phạm vi tràn đi là được.

6.3. Lỗi #N/A

Nếu một trong các mảng nguồn có ít cột hơn mảng lớn nhất, VSTACK trả về lỗi #N/A trong các cột thiếu giá trị.

Nếu một trong các mảng nguồn có ít hàng hơn mảng lớn nhất, HSTACK trả về lỗi #N/A trong các hàng bổ sung.

Giải pháp là sử dụng hàm IFNA hoặc IFERROR để thay thế các lỗi bằng giá trị bạn muốn.

Các bạn có thể download File đính kèm bằng cách click vào hình dưới đây.