Trong bài viết này, Học Excel Online sẽ hướng dẫn các bạn sử dụng công cụ Power Query để chuẩn hoá lại những kiểu bố trí dữ liệu khó, phức tạp trong Excel.

Chuẩn hoá kiểu dữ liệu “Khó đỡ” với công cụ Power Query trong Excel

Chuẩn hoá dữ liệu, hay nói cách khác là đưa dữ liệu về dạng chuẩn có cấu trúc đúng với các trường thông tin phù hợp, được bố trí một cách khoa học, hợp lý, nhằm giúp cho việc quản lý, kiểm soát cũng như đưa ra các báo cáo thống kê, phân tích một cách nhanh chóng, chính xác và hiệu quả,….

Cấu trúc của bảng dữ liệu chuẩn sẽ giúp cho bạn không phải đau đầu, mất quá nhiều thời gian suy nghĩ với những công thức Excel phức tạp, công thức mảng, những công thức lồng nhau dài cả cây số,….

Hướng dẫn sử dụng Power Query để chuẩn hoá dữ liệu

Đặt vấn đề

Hãy cùng Học Excel Online xem DATA ví dụ sau:

Bạn nghĩ như thế nào về kiểu bố trí dữ liệu như trên???. Rất khó để “Yêu” được phải không nào.

Các thông tin về giới tính, tuổi, trình độ nằm trong 1 cột. Các cột Mã nhân viên, SĐT thì chỗ có dữ liệu, chỗ không có. Không đỡ nổi phải không.

Phải chuyển đổi, những chuyển như nào cho nó đúng đây?, cần thiết kế lại với những trường thông tin nào đây?

Hướng xử lý

Câu trả lời cho vấn đề trên là:

Cột thông tin trong dữ liệu gốc chứa các thông tin về Giới tính, Tuổi, Trình độ của mỗi nhân viên. Vấn đề của ta nằm ở đây và cách giải quyết là ta nên tách các thông tin này ra làm tiêu đề cột với các cột là Giới tính, Tuổi, Trình độ.

Với các cột về Mã nhân viên, SĐT ta cũng phải xử lý như nào để bỏ hết các khoảng trống đó đi, chỉ giữ lại những dòng có dữ liệu.

Để giải quyết bài toán này hãy cùng Học Excel Online đến với phần cách thực hiện.

Cách thực hiện với Power Query

Trong phần này, Học Excel Online sẽ xử lý dữ liệu “Khó đỡ” và rất chi là “Oái oăm” trên với công cụ Power Query trong Excel. Ta sẽ có các bước sau:

Bước 1: Xử lý ban đầu

Sau khi đã tạo bảng, đặt một cái tên nào đó cho dữ liệu (mình đã đặt là DATA). Ta thực hiện Input bảng DATA này vào Power Query Editor. Xoá bỏ bước Changed Type và nó sẽ trông như sau:

Bạn ngắm kiểu dữ liệu “khó đỡ” này một chút rồi ta sẽ thực hiện tiếp:

– Trong ví dụ này không có. Tuy nhiên, trường hợp có giá trị null trong cột Thông tin thì bạn sẽ lọc và loại bỏ những giá trị null này đi (ở đây mình vẫn thực hiện để đảm bảo những lần sau nếu có dữ liệu trống trong bảng DATA gốc thì thao tác này sẽ loại bỏ những dòng trống đó cho chúng ta).

– Tiếp theo, chọn cột Mã nhân viên, vào Tab Tranform và Fill -> Down để đảm bảo không có giá trị null nào trong cột này.

Sau khi thực hiện như trên, dữ liệu ban đầu sẽ giống như sau (hình đã cắt bớt 1 phần):

Bước 2: Thêm cột

Tiếp theo, ta sẽ thêm cột Modulo

Tại Tab Add Column, bạn tìm tới nhóm From Number, trong danh sách sổ xuống của phần Standard bạn chọn Modulo, sẽ có 1 hộp thoại xuất hiện ra và bạn gõ vào số 3 như hình.

Vì sao phải làm việc đó?. Ta thấy thông tin của mỗi Mã nhân viên sẽ có 3 dòng đó là giới tính, tuổi, và trình độ (nhân viên nào cũng có 3 dòng này).

Việc đánh số Modulo này sẽ như sau:

• Số 0 sẽ tương ứng với giới tính
• Số 1 sẽ tương ứng với tuổi
• Số 2 sẽ tương ứng với trình độ

Lưu ý: Muốn thực hiện các thao tác trong nhóm From Number thì cột hiện tại mà bạn đang chọn phải là định dạng Number (nếu là Text thì bạn sẽ không chọn được mục Standard), bạn có thể chuyển đổi định dạng của 1 cột nào đó về dạng số rồi thực hiện bước trên (nếu có thể), hoặc không thì bạn sẽ chèn thêm 1 cột Index nữa chẳng hạn bằng chọn Index Column trong Tab Add Column (như mình đã thực hiện).

Sau khi thực hiện các bước trên thì bảng DATA của ta sẽ trông như này đây:

Bước 3: Thực hiện Pivot Column

Chọn cột Modulo, sau đó vào Tab Transform và chọn Pivot Column.

• Values Column bạn chọn là Thông tin
• Trong phần Advanced options bạn chọn Don’t Aggregate (Không tổng hợp)

Ta được kết quả tới bước này như sau:

Bước 4: FillDown và Lọc

Gần hoàn thành rồi đấy . Ở bước này, ta thực hiện các việc sau:

• Chọn cột có tiêu đề là 1 và 2, vào Tab Transform tìm tới Fill -> Down
• Sau đó, lọc cột có tiêu đề là 0, lọc bỏ cá giá trị null.

Kết quả sau bước này như sau, nhìn có vẻ khả quan hơn rồi phải không nào

Bước 5: Hoàn thành và Load trở lại Excel

Thực hiện xoá cột Index đi (nếu tại Bước 2 bạn đã thực hiện bằng cách thêm 1 cột Index như mình làm)

Đổi tên các cột:

• Cột có tên 0 thành cột Giới tính
• Cột có tên 1 thành Tuổi
• Cột có tên 2 thành Trình độ

Chính sửa định dạng cho các trường (cột) dữ liệu, cũng như di chuyển vị trí giữa các cột cho phù hợp,….

Và đây, ta sẽ Load bảng dữ liệu này vào Excel.

Thực hiện Close & Load To… rồi chọn cách xem DATA sau khi đã xử lý cũng như chọn vùng để đặt DATA này.

Qua các bước (cũng khá dài phải không ), nhưng không sao, thành quả mà ta có được xứng đáng phải không nào. Một bảng dữ liệu được cấu trúc lại một cách hợp lý, đã được chuẩn hoá qua các bước thực hiện mà Học Excel Online đã giới thiệu ở trên.

Sau khi đã thực hiện các thao tác trong Power Query, và tất nhiên là những thao tác mà bạn thực hiện này đã được M Code ghi lại. Khi bạn thêm dữ liệu vào DATA gốc ban đầu thì bạn chỉ cần Refresh, ngay lập tức bảng kết quả sẽ được cập nhật cho bạn một cách nhanh chóng, chính xác.

Kết luận

Học Excel Online hy vọng qua bài viết này bạn đã nắm được cách thực hiện chuyển đổi kiểu dữ liệu phức tạp như trên về dạng chuẩn, một bảng dữ liệu được biến đổi về đúng cấu trúc, bố trí một cách khoa học, thống nhất giữa các trường thông tin.

Bạn có thể thấy, việc tổ chức 1 bảng dữ liệu không khoa học, không tuân theo cấu trúc nào cả sẽ gây ra rất nhiều khó khăn trong quá trình tổng hợp, báo cáo cũng như lấy đi của bạn rất nhiều thời gian trong công việc,… Với công cụ rất mạnh là Power Query có thể sẽ giúp bạn xử lý được. Tuy nhiên, nếu như bạn tổ chức dữ liệu tốt ngay từ ban đầu thì mình vẫn nghĩ là tốt hơn.

Hướng dẫn sử dụng Power Query để chuẩn hoá dữ liệu

Cách gộp dữ liệu từ nhiều bảng vào 1 bảng sử dụng power query

Hàm DAX trong Power BI

PBI101 – Microsoft Power BI Desktop, Business Intelligence trong tầm tay