News Thủ thuật

Tạo bảng kê – phiếu thu chi – xuất nhập kho tự động với Power Query Excel

Viết bởi Tiến Đức

I. Xác định và khởi tạo dữ liệu 

1.1 Xác định dữ liệu nguồn

Đặc biệt là dân kế toán thì thường sẽ thiết kế và sử dụng theo mẫu có sẵn như trong những luật liên quan, ví dụ phiếu thu, phiếu chi, phiếu xuất nhập kho,…. Bạn có thể tạo luôn hoặc không nhưng điều quan trọng bạn phải xác định được mục nào sẽ cần trong form in.

Mục đính: Để xác định những trường cần thiết khi in số liệu.

Từ đây ta xác định được những trường phải có cần thiết để tạo data ban đầu cho chuẩn ngay từ đầu, một số trường như: Ngày tháng, mã phiếu, Tên sản phẩm, số lượng, đơn giá, tên người mua/bán, tên nhà cung cấp, địa chỉ,… Việc xác định trường nào sẽ phụ thuộc vào nhu cầu công việc của bạn.
Trong ví dụ này tôi sẽ phải cần những trường sau: TT, Tên loại hàng, Đvt, nhóm hàng, số lượng và ghi chú.

1.2 Thiết kế chuẩn dữ liệu nguồn

Dữ liệu nguồn sẽ là sheet chứa tất cả dữ liệu nhập đầu vào của bạn, ở đây chắc chắn ta phải có những trường đã được xác định ở mục 1.1. Nếu bạn đang có một bảng nhập liệu sẵn rồi thì thôi, có thể bỏ qua phần I này.

II. Cách khởi tạo Power Query và danh mục điều kiện

2.1 Khởi tạo Power Query từ dữ liệu nguồn.

Đối với office 2010,2013: chọn tab Power Query > From Table/Range.
Đối với office 2016 trở lên: chọn tab DATA > From Table/Range.
Từ đây bạn quét chọn toàn bộ bảng dữ liệu của mình, lưu ý chọn cả phần tên cột nhé. (Tích như hình chọn: My table has headers).

Khởi tạo Power Query với Office 2016 – 2019
Khởi tạo Power Query với Office 2010 – 2013

2.2 Thao tác dữ liệu nguồn với Power Query

Bước này ta sẽ tạo Query

Sau khi khởi tạo Power Query bạn cần loại bỏ (Remove) những trường không cần thiết chỉ để lại những trường cần lấy số liệu.

Như phần 1 tôi sẽ cần giữ lại những trường sau: Tên loại hàng, Đvt, nhóm hàng, số lượng và ghi chú. Và thêm 1 trường Index – mục đích để tạo cột thứ tự.

Chọn cột cần xóa – kick chuột phải chọn Remove, hoặc giữ phím ctrl để chọn nhiều cột cùng lúc để remove.

Chỉnh sửa và load Query ra sheet

Lưu ý bạn có thể làm ngược lại, tức là chọn những cột cần lấy số liệu sau đó kick chọn “Remove other columns” để xóa những cột còn lại.

Việc xóa những cột không cần thiết trong cửa sổ Power Query sẽ giúp bạn tạo được bảng dữ liệu mới chỉ bao gồm những cột có số liệu cần thiết.

Đến đây là ok rồi nhưng để phục vụ trích xuất sau này mình sẽ làm luôn đó là lọc theo số phiếu và tạo thêm cột số thứ tự (thêm column Index), phần dưới mình sẽ nói rõ hơn.

2.3 Tạo danh mục điều kiện số phiếu

Mình hay dùng thủ thuật này để tạo số phiếu tự động, tạo đến đâu cập nhật đến đó, đỡ mất thời gian tạo thủ công. Để tạo số phiếu tự động, từ bảng dữ liệu nguồn bạn khởi tạo Power Query như bình thường.

Xóa hết những trường còn lại chỉ dữ lại trường “Số phiếu” > sau đó chọn tab Transform chọn Group by
Cửa sổ Group by hiện ra bạn chọn tính năng Count rows/ hoặc Count Distinct Rows đều được.

Lưu ý thêm: Với Count Rows kết quả sẽ trả về số phiếu và đếm số lần xuất hiện số phiếu đó, thích hợp với việc bạn muốn xem xem có bao nhiêu mặt hàng xuất hiện trong cùng một số phiếu.
Với Count Distinct Rows thì chỉ đơn giản là trả về số phiếu có tên trong bảng.

Số phiếu được cập nhật tự động từ bảng dữ liệu nguồn

Ngoài ra mình sẽ hướng dẫn thêm cách để cập nhật số phiếu tự động, việc này khá đơn giản:

Cập nhật số phiếu tự động – Power Query

2.4 Tạo và cấu hình mục chọn. (Quan trọng).

Số phiếu chính là điều kiện để chúng ta trích xuất dữ liệu, bạn có thể tạo list chọn hoặc gõ tay, tuy nhiên bạn phải xác định được nơi đặt mục chọn list phiếu đó.
Ở file mình đang để ở ô K6, từ đây bạn quét chọn và tạo 1 bảng bao gồm 2 ô K5 và K6, trong đó K5 là header đặt tên là chon_phieu, bảng này có tên là Table5 (bạn có thể đổi tên bảng trong tab Design)
Từ bảng này chọn From Table/Range để tạo Query mới.
Click xóa Changed Type trong cửa sổ Query Settings
Chuyển qua tab View chọn Advanced Editor, cửa sổ soạn thảo hiện ra bạn gõ như sau:

Với chon_phieu là tên header của bảng (ô K5)

Với SoPhieu là trường tùy chọn, bạn có thể đặt tên khác rồi chọn Done để hoàn thành.

Sau đó click File > Close & load to…  Cửa sổ hiện ra click chọn Only Create Connection.

Ok Xong phần tạo và cấu hình mục chọn.

Giờ chúng ta cấu hình kết nối Query vừa tạo ở mục 2.2 và mục chọn điều kiện ở mục 2.4
Mở lại query Table3 đã tạo ở mục 2.2 > tại tab View > chọn Advanced Editor, tìm đúng step #”Filtered Rows” …… thay cụm “PN002” bằng tên của query điều kiện là Table5. Chọn Done để hoàn thành và chọn Close & load… kết thúc quá trình cấu hình.
Vậy là xong, bạn có thể căn chỉnh lại form biểu mẫu của mình và theo dõi chi tiết trong video:

Kết nối query và data điều kiện – Power Query

Kết

Vậy là mình vừa giới thiệu xong tới các bạn ứng dụng của Power Query trong việc trích xuất dữ liệu, cụ thể ở đây là chúng ta trích xuất để tạo một bảng kê hoặc phiếu thu chi hoặc phiếu xuất nhập kho hoàn toàn tự động dựa trên tập hợp số liệu đầu vào. Hy vọng bài hướng dẫn này sẽ giúp ích trong công việc của bạn.

Sending
User Review
5 (1 vote)

Về tác giả

Tiến Đức

Gửi phản hồi