Giả sử mình đang có 1 File dữ liệu gồm các Sheet cần tổng hợp dữ liệu là WIP_1, WIP_2, … WIP_5.

Giờ nhiệm vụ đầu tiên của chúng ta là tổng hợp dữ liệu về Sheet Tong_Hop.

Đầu tiên trước khi bắt đầu thì bạn cần biết là hàm VSTACK chỉ có trên Office 365 bản Insider và Office Online.

Nếu bạn không sở hữu hàm VSTACK để thực hành thì bạn cũng có thể thực hành trên Office Online.

Bạn có thể tham khảo hướng dẫn cài Office Online tại link trên.

Đầu tiên bạn cần biết lý thuyết về hàm VSTACK trước như sau.

Hàm VSTACK được sử dụng để nối các mảng theo chiều dọc và theo trình tự để trả về một mảng lớn hơn.

Cú pháp

=VSTACK(array1,[array2],...)

Cú pháp hàm VSTACK có đối số sau đây:

Array…: Các mảng cần nối thêm.

Giờ ta đang cần tham chiếu cùng 1 lúc tới tất cả các Sheet từ WIP_1 tới WIP_5.

Để làm được điều này thì bạn cần sử dụng kiến thức về công thức 3D trong Excel.

Ví dụ nếu bạn muốn tham chiếu tới vùng A2:U5000 trên Sheet WIP_1 thì bạn sẽ viết như thế này.

=WIP_1!A2:U5000

Giờ bạn muốn tham chiếu cùng 1 lúc tới tất cả các Sheet thì chúng ta sẽ viết như sau.

=WIP_1:WIP_5!A2:U5000

Bạn có thể thấy là khi viết công thức như trên thì sẽ báo lỗi #REF!

Tuy nhiên nếu bạn lồng thêm hàm VSTACK bên ngoài công thức trên thì tất cả dữ liệu trên các vùng dữ liệu WIP_1!A2:U5000, WIP_2!A2:U5000, WIP_3!A2:U5000, WIP_4!A2:U5000, WIP_5!A2:U5000 sẽ được kết hợp với nhau thành 1. Và đây là kết quả.

=<code class="erx-inline-code">VSTACK</code>(WIP_1:WIP_5!A2:U5000)

Tuy nhiên khi bạn làm như vậy thì bạn có thể thấy có rất nhiều dòng dữ liệu đang hiện như sau.

Lý do hiện ra các giá trị 0 như vậy đó là do vùng bạn chọn từ A2:U5000 chứa rất nhiều dòng dữ liệu trống. Và để công thức của các bạn trở nên hoàn hảo thì chúng ta cần lọc bỏ các dòng dữ liệu trên.

Và để làm điều đó thì chúng ta sẽ sử dụng hàm FILTER.

Hàm FILTER cho phép bạn lọc dải ô dữ liệu dựa trên tiêu chí do bạn xác định.

Cú pháp

Hàm FILTER lọc một mảng dựa trên mảng Boolean (True/False).

=FILTER(array,include,[if_empty])

Array: mảng hoặc dải ô cần lọc.

Include: Mảng Boolean có chiều cao hay chiều rộng giống với mảng đã chọn.

[if_empty]: Giá trị để trả về nếu mọi giá trị trong mảng được bao gồm đều trống (bộ lọc không trả về kết quả nào).

Giờ chúng ta sẽ áp dụng hàm FILTER vào công thức trên để lọc bỏ các dòng dữ liệu trống khi tổng hợp dữ liệu.

=<code class="erx-inline-code"><code class="erx-inline-code">FILTER</code></code>(<code class="erx-inline-code"><code class="erx-inline-code">VSTACK</code></code>(WIP_1:WIP_5!A2:U5000),<code class="erx-inline-code"><code class="erx-inline-code">VSTACK</code></code>(WIP_1:WIP_5!A2:A5000)<>"")

Giờ nếu chúng ta muốn thêm điều kiện lọc thì sao?

Ví dụ như bạn muốn lọc theo Ship Mode = Delivery Truck thì bạn sẽ làm thế nào.

Rất đơn giản, bạn hãy thêm điều kiện lọc vào hàm FILTER nữa là được.

=<code class="erx-inline-code"><code class="erx-inline-code">FILTER</code></code>(<code class="erx-inline-code"><code class="erx-inline-code">VSTACK</code></code>(WIP_1:WIP_5!A2:U5000),(<code class="erx-inline-code"><code class="erx-inline-code">VSTACK</code></code>(WIP_1:WIP_5!A2:A5000)<>"")*(<code class="erx-inline-code"><code class="erx-inline-code">VSTACK</code></code>(WIP_1:WIP_5!H2:H5000)="Delivery Truck"))

Lưu ý là bạn cần lọc dữ liệu cột nào thì khi viết điều kiện lọc của hàm FILTER bạn cần điều chỉnh cột bạn muốn lọc thành cột đó.

Ví dụ như trên là ta đang lọc cột H thì sẽ đổi cột muốn lọc từ A sang H chẳng hạn.

Nếu có phát sinh thêm 1 điều kiện nữa là Order Priority = “High” thì ta sẽ thêm vào dạng * (điều kiện lọc) như sau.

=<code class="erx-inline-code"><code class="erx-inline-code">FILTER</code></code>(<code class="erx-inline-code"><code class="erx-inline-code">VSTACK</code></code>(WIP_1:WIP_5!A2:U5000),(<code class="erx-inline-code"><code class="erx-inline-code">VSTACK</code></code>(WIP_1:WIP_5!A2:A5000)<>"")*(<code class="erx-inline-code"><code class="erx-inline-code">VSTACK</code></code>(WIP_1:WIP_5!H2:H5000)="Delivery Truck")*(<code class="erx-inline-code"><code class="erx-inline-code">VSTACK</code></code>(WIP_1:WIP_5!D2:D5000)="High"))

Các bạn có thể tham khảo File đã hoàn thành tại đây.

https://erx.vn/Media/trunghoang/ERX_VIETNAM_VSTACK.xlsx

Nếu bạn muốn tham khảo thêm ứng dụng của các hàm mới trong Excel 365 thì bạn có thể tham khảo Workshop của ERX tại địa chỉ dưới đây.

'' Kết hợp hàm mảng động lọc dữ liệu sạch, loại bỏ dòng trống và sắp xếp tự động:
=LET(
    data, FILTER(Table1, Table1[DoanhThu] > 0, ""),
    SORT(data, 1, 1)
)