Phân tách dấu phân cách thành hàng (và cột) với Power Query

12/1/2020

Nội dung chính: Tìm hiểu cách sử dụng các tính năng tách theo dấu phân cách thành hàng và cột của Power Query. Ngoài ra, hãy xem cách cắt các khoảng trống và thay thế các giá trị.

Cấp độ kỹ năng: Trung bình

Chuyển đổi đoạn địa chỉ email thành một danh sách có tổ chức

Giả sử bạn vừa tham gia vào một nhóm mới (danh sách phân phối) và bạn muốn tạo danh sách hoặc thư mục thông tin liên hệ của các thành viên. Dữ liệu mà bạn có để bắt đầu danh sách của mình là từ một email nhóm mà bạn đã nhận được.

Bạn có thể lấy chuỗi tên và email trong trường “To” của email và nhanh chóng biến chúng thành một danh sách có tổ chức bằng Power Query.

Sử dụng Power Query để tạo bảng

Bước đầu tiên trong việc chuyển đổi dữ liệu chỉ là sao chép toàn bộ khối hoặc chuỗi tên/email. Sau đó, dán chúng vào một ô trên trang tính Excel.

Với một ô được chọn, hãy mở Power Query Editor. Bạn có thể làm điều đó bằng cách nhấp vào From Table/Range trên tab Data của Ribbon.

Điều đó sẽ hiển thị cửa sổ Create Table. Xác minh ô mà bạn đang lấy dữ liệu từ đó và hộp kiểm cho “My table has headers” KHÔNG được chọn. Khi bạn bấm OK, Power Query Editor sẽ mở ra.

1. Tách dấu phân cách thành hàng

Điều đầu tiên chúng ta sẽ thực hiện trong trình chỉnh sửa là chia cột. Trên tab Home của Ribbon, hãy chuyển đến menu Split Column và chọn By Delimiter.

Dấu phân cách là một ký tự, ký hiệu hoặc dấu cách cho biết phần đầu hoặc phần cuối của một mục dữ liệu. Trong trường hợp này, các mục email của được phân tách với nhau bằng dấu chấm phẩy. Vì vậy, khi cửa sổ Split Column by Delimiter xuất hiện, hãy chọn Semicolon (dấu chấm phẩy) từ menu thả xuống của các dấu phân cách.

Trong cùng một cửa sổ, bên dưới Advanced Options, hãy chọn tùy chọn Rows để tách thành hàng. Nếu không, mỗi mục email sẽ được liệt kê trong cột riêng của nó, và điều này thì không hữu ích.

Khi bạn OK, chuỗi dữ liệu sẽ được tách để mỗi hàng chứa dữ liệu trước đây nằm giữa các dấu chấm phẩy.

Đây là một tính năng tuyệt vời và có phần ẩn của Power Query! Và nó vẫn hoạt động tương tự nếu bảng dữ liệu có nhiều cột nhưng bạn chỉ đang tách một cột duy nhất.

2. Tách dấu phân cách thành các cột

Bước tiếp theo là tách tên khỏi địa chỉ email. Ký hiệu nào tách các phần dữ liệu này? Ký hiệu nhỏ hơn (<). Vì vậy, chúng ta sẽ lặp lại quá trình phân tách bằng dấu phân cách, nhưng lần này sẽ chọn ký hiệu nhỏ hơn, có thể được nhập khi bạn chọn tùy chọn Custom cho dấu phân cách của mình. Lần này không chọn Advanced Option cho Rows.

Đây sẽ là dữ liệu sau khi thực hiện.

Dọn dẹp dữ liệu

Tiếp theo, chúng ta có một số công việc dọn dẹp dữ liệu phải làm.

1. Loại bỏ khoảng trống bằng Trim

Bạn sẽ nhận thấy rằng trong cột đầu tiên, có thêm khoảng trắng trước tên. Để loại bỏ khoảng trắng đó, chỉ cần nhấp chuột phải vào tiêu đề cột và chọn Transform, sau đó Trim.

2. Loại bỏ các ký tự phụ bằng giá trị thay thế

Trong cột thứ hai, muốn loại bỏ các ký hiệu lớn hơn (>) theo sau mỗi địa chỉ email, hãy bấm chuột phải vào tiêu đề cột, chọn Replace Values, rồi thay thế dấu > bằng không gì cả. Thao tác này sẽ xóa tất cả các dấu đó.

Và cuối cùng, muốn thêm tiêu đề cột, hãy bấm đúp vào tiêu đề cho mỗi cột và nhập tên mới. (Trong trường hợp này, Column 1 trở thành Name và Column 2 trở thành Email.)

3. Đổi tên các cột

Cuối cùng, chúng ta có thể bấm đúp vào từng tiêu đề cột và đặt tên mô tả cho các cột.

Xuất Truy vấn vào Bảng Excel

Bây giờ hãy nhấp vào nửa trên của nút Close & Load. Thao tác này xuất dữ liệu ra khỏi Power Query thành trang tính mới của riêng nó.

Trang tính mới của bạn liệt kê từng tên và địa chỉ email trong các ô riêng của một bảng được tổ chức.

Sử dụng Power Query, chúng ta đã chia dữ liệu bằng cách sử dụng dấu phân cách, đầu tiên thành các hàng để mỗi email/tên có mục nhập riêng, sau đó theo cột để tên được tách biệt với địa chỉ email.

Một quy trình hoàn toàn tự động

Vì chúng ta đã xây dựng quy trình này trong Power Query nên có thể sử dụng lại. Mọi danh sách email mới đến đều có thể được dọn dẹp nhanh chóng ngay bây giờ khi đã hoàn thành công việc thiết lập.

Để làm điều đó, bạn chỉ có thể dán danh sách mới vào ô có danh sách ban đầu và chạy lại truy vấn, việc này thực sự đơn giản. Chỉ cần nhấp chuột phải vào bất kỳ đâu trên bảng đầu ra và chọn Refresh.

Hoặc nếu bạn đang tìm cách thêm các mục nhập mới vào bảng đầu ra hiện có của mình, bạn có thể chỉ cần dán dữ liệu vào ô trống tiếp theo trong cột nguồn. Trong hình ảnh bên dưới đã dán danh sách email đầu tiên vào A2 và danh sách thứ hai vào A3.

Thiết lập này sẽ nối danh sách thứ hai vào danh sách đầu tiên khi bạn làm mới bảng đầu ra. Nối có nghĩa là xếp chồng dữ liệu; danh sách thứ hai sẽ được đặt bên dưới danh sách đầu tiên trong bảng đầu ra.

Nguồn: excelcampus.com