Làm cách nào để trích xuất dữ liệu từ Excel?

Đây là vấn đề mà tôi đã phải đối mặt gần đây. Một khách hàng yêu cầu tôi xử lý dữ liệu từ hàng trăm tệp Excel. Thành thật mà nói, tôi không muốn dành hàng giờ để trích xuất dữ liệu này theo cách thủ công. Vì vậy, tôi đã tự động hóa quy trình với Anatella và sau đó trực quan hóa dữ liệu bằng Tableau. Dưới đây là hướng dẫn nhỏ về chuẩn bị dữ liệu và một cách tuyệt vời để xử lý dữ liệu của bạn hiệu quả hơn với một ETL xứng đáng với tên gọi

Nếu bạn chỉ có 30 giây

• sử dụng Anatella, tôi có thể tự động hóa quá trình trích xuất dữ liệu từ tệp Excel
• nó cho phép tôi tăng tốc và duy trì một quy trình trước đây tẻ nhạt và dễ bị lỗi do xử lý thủ công
• phương pháp được sử dụng dựa trên phương pháp khởi chạy vòng lặp trên các tệp Excel nằm trong một thư mục xác định
• một tệp hợp nhất được tạo ở đầu ra mà tôi có thể sử dụng trong Tableau

Một mô tả của vấn đề

Vấn đề mà tôi gặp phải là tương đối phổ biến. Là một phần của dự án do Quốc hội giao cho tôi, tôi được yêu cầu cung cấp số liệu thống kê về việc sử dụng người khuyết tật trong cơ quan hành chính công

Mỗi năm một lần, mỗi đô thị được yêu cầu điền vào một tệp Excel mẫu, ghi lại toàn bộ một loạt các chỉ số. số lượng FTE, giới tính, mức lương tham khảo, số lượng công việc được giao cho người khuyết tật, v.v. Tin tốt là các cơ quan quản lý được đề cập tôn trọng khuôn mẫu và ghi thông tin vào đúng chỗ. Vấn đề là các mẫu không phải lúc nào cũng đầy đủ, một số bị thiếu và có nhiều tệp. Do đó, cần phải xem lại nhiều lần trước khi có dữ liệu hợp nhất

Giải pháp đầu tiên là xử lý từng tệp riêng lẻ sau khi sửa lỗi trong đó. Điều này tốn thời gian và không bền vững. Tôi sẽ phải bắt đầu lại mỗi năm. Trên hết, nó sẽ liên quan đến nhiều chuyến đi qua lại với quản trị trung tâm để truy xuất từng tệp một khi chúng được gửi. Điều này sẽ làm tôi chậm lại đáng kể

 

Cách tiếp cận phù hợp là tạo ra một quy trình trích xuất dữ liệu và cấu trúc “đường ống. ” Đường ống này chạy tự động và cho phép tôi một mặt cập nhật tập dữ liệu của mình một cách dễ dàng và mặt khác là các hình ảnh trực quan của tôi bằng Tableau. Đây là cách tôi tiếp cận vấn đề với Anatella

---

Dung dịch

Giải pháp được phát triển được chia thành hai bước chính

• một đường dẫn để trích xuất dữ liệu từ mỗi tệp và cơ cấu lại chúng
• một đường dẫn để xử lý cùng lúc tất cả các tệp trong một thư mục nhất định

Bước 1. trích xuất dữ liệu từ tệp Excel và định dạng lại

Do đó, bước đầu tiên là trích xuất thông tin từ các ô cụ thể trong tệp Excel. Để làm điều này, tôi sử dụng một toán tử bậc ba. Sau đó, tính năng “unflatten” (rất tiện dụng) cho phép tôi cấu trúc tất cả dữ liệu trên một dòng, đặt nền tảng cho định dạng tệp cần thiết cho Tableau. Cuối cùng, tôi viết một. tệp gel trong cùng thư mục với tệp Excel

Đường ống trích xuất dữ liệu bằng Anatella. Các tập tin đầu vào là. xlsx và đầu ra là một. tập tin gel

Bước 2. vòng lặp xử lý tập tin

Bước 2 bao gồm tạo một vòng lặp sẽ gọi từng chuỗi xử lý của các tệp Excel và viết một loạt. tập tin gel.
Mẹo. bạn có thể chạy song song nhiều quy trình nếu CPU của bạn có nhiều lõi. Mình dùng CPU i7 8 nhân chạy 8 thao tác song song. Thời gian xử lý cho toàn bộ quá trình là 4. 31 giây thay vì khoảng 32 giây. Lợi nhuận là đáng kể. Đầu ra, tôi chỉ cần một tệp Excel (tôi cũng có thể chọn một. siêu tập tin).

Tập lệnh này chạy “Vòng lặp chính”, lấy từng tệp đóng băng một và tạo một tệp nén

Bạn có thể tùy chỉnh vòng lặp để tải tệp bằng cách xác định một số quy trình để chạy song song. Ở đây tôi đã chỉ ra 8, tương ứng với số lõi trong CPU của máy tôi

---

Phần kết luận

Nhờ có Anatella, tôi có thể tự động hóa quy trình trích xuất dữ liệu mà nếu không thì tôi sẽ mất hàng giờ đồng hồ. Một lợi thế đáng kể khác là quy trình tự động này là bằng chứng trong tương lai và sẽ cho phép tôi nhập các tệp mới và tự động cập nhật các hình ảnh trực quan của mình nếu các mẫu Excel không thay đổi trong tương lai

Microsoft Excel là một công cụ tuyệt vời, nhưng đôi khi các tệp bảng tính mà chúng tôi làm việc không lý tưởng. Một ví dụ là tệp có cột dữ liệu, có thể là địa chỉ đường phố mà bạn thực sự muốn tách ra. Trong hướng dẫn này, tôi sẽ chỉ cho bạn cách trích xuất văn bản từ một ô trong Excel bằng cách sử dụng một số hàm văn bản đơn giản nhưng mạnh mẽ. (Kèm sách bài tập thực hành. )

Nội dung hướng dẫn

Chuỗi con Excel là gì?

Trước khi chúng ta có Excel trích xuất văn bản từ chuỗi, chúng ta cần xác định một số điều. Một số ngôn ngữ lập trình có các hàm chuỗi con chuyên dụng. Excel làm một cái gì đó tương tự bằng cách sử dụng. Họ có thể loại riêng của họ

Khi chúng tôi nói về một chuỗi con, chúng tôi muốn nói đến một phần hoặc tập hợp con của nội dung ô Excel. Ví dụ: nếu ô chứa 1001 Drake Ave. , bất kỳ mục nào trong số này có thể là một chuỗi con

• 1001
• Đại lộ Drake
• 100
• cái cào

Một vấn đề phổ biến

Nhiều cơ sở dữ liệu thành viên hoặc danh sách gửi thư được thiết lập với các trường được xác định cho Tên, Họ, Đường phố, Thành phố, Bang và Zip. Định dạng này hoạt động tốt nếu bạn đang tạo nhãn gửi thư vì bưu điện dựa vào phân loại mã zip. Và đôi khi bạn có thể may mắn và phân tích tên và họ bằng Trình hướng dẫn Chuyển đổi Văn bản thành Cột của Excel

Nhưng nếu bạn cần đi vận động từng nhà để kiểm tra hàng xóm hoặc để thông báo cho mọi người về một dự luật bỏ phiếu sắp tới thì sao?

Nếu bạn mở loại danh sách này trong Excel và sắp xếp nó trên cột Đường phố, bạn sẽ nhận được một danh sách được sắp xếp theo số. Như bạn có thể thấy trong ví dụ bên dưới, các bản ghi Drake Ave không ở cùng nhau

Excel với trường Đường đơn

Lý tưởng nhất là bạn muốn sắp xếp danh sách sao cho Drake Ave. các mục được cùng nhau. Có một số cách để thực hiện việc này trong Excel, nhưng có một cách là tạo hai cột từ cột Phố

Cột đầu tiên phản ánh chuỗi con số đường và cột thứ hai là chuỗi con tên đường. Sau đó, bạn có thể sử dụng danh sách dựa trên tên đường và số đường

Xây dựng công thức lồng nhau một cách trực quan

Đối với ví dụ đầu tiên, chúng ta sẽ lồng một số hàm Excel như LEFT và FIND. Khi chúng tôi tiến bộ, chúng tôi sẽ thêm một số bộ dấu ngoặc đơn. Bằng cách lồng nhau, ý tôi là chúng ta sẽ sử dụng một hàm (TÌM) làm đối số cho một hàm khác, chẳng hạn như TRÁI hoặc PHẢI

Hãy bắt đầu với =FIND(" ",C2). Ở dạng văn bản thuần túy, cú pháp hàm của chúng tôi yêu cầu Excel tìm khoảng trống trong ô tham chiếu C2 để tìm khoảng trống được biểu thị bằng dấu " ". Trong hình bên dưới, tôi đã thêm vị trí bắt đầu là “1“, nhưng đây là tham số tùy chọn và Excel bắt đầu từ 1 theo mặc định. Excel tìm thấy khoảng trống ở vị trí 5 hiển thị trong ô D2

Để làm cho công thức dễ dàng hơn, tôi sẽ xóa tham số bắt đầu tùy chọn là 1 vì dù sao thì Excel cũng bắt đầu từ đó

Bây giờ, hãy thêm hàm LEFT để công thức của chúng ta đọc là =LEFT(C2,(FIND(" ",C2))). Trong trường hợp này, chúng ta lại sử dụng ô C2, nhưng hàm LEFT sẽ lấy nội dung ô trong C2 từ vị trí 1 đến vị trí 5 nơi hàm FIND của Excel tìm thấy khoảng trống

Tuy nhiên, có một vấn đề nhỏ. Trong khi bạn không thể nhìn thấy nó, có một dấu cách ở cuối trong D2. Dùng hàm LEN ta thấy ô D2 có 5 ký tự. Bạn có thể nhớ chức năng tiện dụng này từ hướng dẫn của chúng tôi về cách kiểm tra số lượng ký tự trong Excel

Giải pháp là trừ 1 hoặc sử dụng hàm TRIM mà mình đã tham khảo trong cách tách tên trong Excel. Để đơn giản, tôi sẽ sử dụng -1. Mặc dù kết quả trực quan giống nhau nhưng bạn có thể thấy số lượng ký tự giảm đi 1

Cách trích xuất số đường bằng hàm LEFT

1. Nhập dữ liệu của bạn vào Microsoft Excel hoặc sử dụng bảng tính mẫu trong phần Tài nguyên
2. Trong ô D1, gõ Nbr
3. Trong ô E1, nhập Tên đường
4. Trong ô D2, nhập công thức Excel sau =LEFT(C2,(FIND(" ",C2)-1))
5. nhấn nút Enter. Giá trị 1001 sẽ hiển thị trong D2

Phần tiếp theo liên quan đến việc sao chép công thức này vào phần còn lại của các mục. Tuy nhiên, chúng ta cần tham chiếu đúng ô đường phố và không sử dụng C2 cho các hàng còn lại

1. Nhấp vào ô D2 để chọn phần đầu của phạm vi của chúng tôi
2. Di chuột đến góc dưới bên phải

Sao chép công thức Excel xuống cột

1. Bấm đúp vào + con trỏ ở phía dưới bên phải. Điều này sẽ sao chép công thức của bạn xuống cột

Trong cột D, bạn sẽ thấy các số đường được trích xuất

Cách trích xuất tên đường bằng chức năng RIGHT

Bây giờ chúng ta sẽ tạo một công thức lồng nhau tương tự để nắm bắt địa chỉ đường phố bằng hàm RIGHT. Lần này, chúng tôi sẽ lấy nội dung ở bên phải của khoảng trống đầu tiên từ cột Đường phố

1. Trong ô E2, nhập công thức sau =RIGHT(C2,LEN(C2)-FIND(" ",C2))
2. nhấn nút Enter. E2 sẽ hiển thị là Drake Ave
3. Nhấp vào ô E2 để chọn phần đầu của phạm vi của chúng tôi
4. Di chuột đến góc dưới bên phải
5. Bấm đúp vào + con trỏ ở phía dưới bên phải. Điều này sẽ sao chép công thức của bạn xuống cột

Cột D và E phải chứa nội dung được phân tích cú pháp từ địa chỉ đường phố ban đầu của bạn

Bảng tính của bạn sẽ trông giống như bên dưới

Số đường và tên trong các cột mới

Dọn dẹp bảng tính và thay đổi định dạng ô

Bảng tính hiện có các trường phân tách của bạn, nhưng bạn nên xóa các công thức. Đề xuất của tôi là chuyển đổi các công thức TRÁI và PHẢI thành các giá trị tương ứng của chúng. Chúng tôi đã thực hiện một hướng dẫn trước đó về cách sao chép các giá trị công thức trong Excel sang các giá trị

Sau khi bạn chuyển đổi cột Nbr, bạn có thể muốn thay đổi loại định dạng thành một số

1. Nhấp vào cột D
2. Nhấp chuột phải và chọn Định dạng ô
3. Trên hộp thoại Format Cells, chọn Number
4. Đặt vị trí thập phân thành 0
5. Nhấp vào OK

Mặc dù ví dụ của chúng tôi đã trích xuất văn bản từ một ô Excel chứa thông tin đường phố, nhưng bạn có thể sử dụng quy trình tương tự để phân tích cú pháp các mục nhập khác. Ví dụ: Bước 1 ở trên thực sự đang phân tích cú pháp mọi thứ trừ từ đầu tiên vì nó đang tìm kiếm khoảng trống. Bạn có thể thay đổi công thức để tìm các giá trị khác, chẳng hạn như dấu phẩy hoặc dấu @