Nội dung
Công thức của vấn đề
Hãy cùng xem xét một giải pháp tuyệt vời cho một trong những tình huống rất tiêu chuẩn mà hầu hết người dùng Excel sớm hay muộn phải đối mặt: bạn cần nhanh chóng và tự động thu thập dữ liệu từ một số lượng lớn tệp vào một bảng cuối cùng.
Giả sử chúng ta có thư mục sau, chứa một số tệp với dữ liệu từ các thành phố chi nhánh:
Số lượng tệp không quan trọng và có thể thay đổi trong tương lai. Mỗi tệp có một trang tính có tên Bán hàngnơi đặt bảng dữ liệu:
Tất nhiên, số lượng hàng (thứ tự) trong các bảng là khác nhau, nhưng tập hợp các cột là tiêu chuẩn ở mọi nơi.
Nhiệm vụ: thu thập dữ liệu từ tất cả các tệp vào một cuốn sách với việc cập nhật tự động sau đó khi thêm hoặc xóa các tệp hoặc hàng thành phố trong bảng. Theo bảng tổng hợp cuối cùng, sau đó sẽ có thể xây dựng bất kỳ báo cáo nào, bảng tổng hợp, dữ liệu sắp xếp bộ lọc, v.v. Điều chính là có thể thu thập.
Chúng tôi chọn vũ khí
Đối với giải pháp, chúng tôi cần phiên bản Excel 2016 mới nhất (chức năng cần thiết đã được tích hợp sẵn theo mặc định) hoặc các phiên bản Excel 2010-2013 trước đó có cài đặt bổ trợ miễn phí Truy vấn nguồn của Microsoft (tải về tại đây). Power Query là một công cụ siêu linh hoạt và siêu mạnh để tải dữ liệu vào Excel từ thế giới bên ngoài, sau đó tách và xử lý nó. Power Query hỗ trợ hầu hết các nguồn dữ liệu hiện có - từ tệp văn bản đến SQL và thậm chí cả Facebook 🙂
Nếu bạn không có Excel 2013 hoặc 2016, thì bạn không thể đọc thêm (đùa thôi). Trong các phiên bản Excel cũ hơn, tác vụ như vậy chỉ có thể được thực hiện bằng cách lập trình macro trong Visual Basic (điều này rất khó đối với người mới bắt đầu) hoặc bằng cách sao chép thủ công đơn điệu (mất nhiều thời gian và tạo ra lỗi).
Bước 1. Nhập một tệp làm mẫu
Đầu tiên, hãy nhập dữ liệu từ một sổ làm việc làm ví dụ, để Excel "chọn ra ý tưởng". Để thực hiện việc này, hãy tạo một sổ làm việc trống mới và…
- nếu bạn có Excel 2016, hãy mở tab Ngày và sau đó Tạo truy vấn - Từ tệp - Từ sách (Dữ liệu - Truy vấn mới- Từ tệp - Từ Excel)
- nếu bạn đã cài đặt Excel 2010-2013 với bổ trợ Power Query, hãy mở tab Truy vấn nguồn và chọn nó Từ tệp - Từ sách (Từ tệp - Từ Excel)
Sau đó, trong cửa sổ mở ra, hãy chuyển đến thư mục của chúng tôi với các báo cáo và chọn bất kỳ tệp thành phố nào (không quan trọng tệp nào, vì chúng đều là tệp điển hình). Sau một vài giây, cửa sổ Bộ điều hướng sẽ xuất hiện, nơi bạn cần chọn trang tính chúng tôi cần (Bán hàng) ở phía bên trái và nội dung của nó sẽ được hiển thị ở phía bên phải:
Nếu bạn nhấp vào nút ở góc dưới bên phải của cửa sổ này Tải về (Trọng tải), sau đó bảng sẽ được nhập ngay vào trang tính ở dạng ban đầu. Đối với một tệp duy nhất, điều này là tốt, nhưng chúng ta cần tải nhiều tệp như vậy, vì vậy chúng ta sẽ đi khác một chút và nhấp vào nút Correction (Chỉnh sửa). Sau đó, trình soạn thảo truy vấn Power Query sẽ được hiển thị trong một cửa sổ riêng biệt với dữ liệu của chúng tôi từ sách:
Đây là một công cụ rất mạnh mẽ cho phép bạn “hoàn thiện” bảng ở chế độ xem chúng ta cần. Ngay cả một mô tả bề ngoài về tất cả các chức năng của nó cũng sẽ mất khoảng trăm trang, nhưng, nếu rất ngắn gọn, bằng cách sử dụng cửa sổ này, bạn có thể:
- lọc ra những dữ liệu không cần thiết, dòng trống, dòng có lỗi
- sắp xếp dữ liệu theo một hoặc nhiều cột
- thoát khỏi sự lặp lại
- chia văn bản cố định theo các cột (theo dấu phân cách, số ký tự, v.v.)
- đặt văn bản theo thứ tự (xóa khoảng trắng thừa, viết hoa đúng, v.v.)
- chuyển đổi các kiểu dữ liệu theo mọi cách có thể (biến các số như văn bản thành số bình thường và ngược lại)
- chuyển vị (xoay) bảng và mở rộng bảng chéo hai chiều thành bảng phẳng
- thêm các cột bổ sung vào bảng và sử dụng các công thức và hàm trong đó bằng ngôn ngữ M được tích hợp trong Power Query.
- ...
Ví dụ: hãy thêm một cột có tên văn bản của tháng vào bảng của chúng tôi, để sau này, việc tạo báo cáo bảng tổng hợp sẽ dễ dàng hơn. Để làm điều này, hãy nhấp chuột phải vào tiêu đề cột ngàyvà chọn lệnh Cột trùng lặp (Cột trùng lặp), sau đó bấm chuột phải vào tiêu đề của cột trùng lặp xuất hiện và chọn Lệnh Chuyển đổi - Tháng - Tên tháng:
Một cột mới sẽ được tạo với tên văn bản của tháng cho mỗi hàng. Bằng cách nhấp đúp vào tiêu đề cột, bạn có thể đổi tên nó từ Sao chép Ngày thoải mái hơn tháng, ví dụ.
Nếu trong một số cột, chương trình không nhận dạng đúng kiểu dữ liệu, thì bạn có thể giúp bằng cách nhấp vào biểu tượng định dạng ở bên trái của mỗi cột:
Bạn có thể loại trừ các dòng có lỗi hoặc các dòng trống, cũng như người quản lý hoặc khách hàng không cần thiết, bằng cách sử dụng một bộ lọc đơn giản:
Hơn nữa, tất cả các phép biến đổi được thực hiện đều được cố định trong bảng điều khiển bên phải, nơi chúng luôn có thể được cuộn lại (chéo) hoặc thay đổi các thông số của chúng (bánh răng):
Nhẹ nhàng và thanh lịch phải không?
Bước 2. Hãy chuyển đổi yêu cầu của chúng ta thành một hàm
Để sau đó lặp lại tất cả các phép chuyển đổi dữ liệu được thực hiện cho mỗi cuốn sách đã nhập, chúng tôi cần chuyển đổi yêu cầu đã tạo của chúng tôi thành một hàm, sau đó sẽ được áp dụng lần lượt cho tất cả các tệp của chúng tôi. Để làm điều này thực sự rất đơn giản.
Trong Trình soạn thảo Truy vấn, hãy chuyển đến tab Xem và nhấp vào nút Trình chỉnh sửa nâng cao (Xem - Trình chỉnh sửa nâng cao). Một cửa sổ sẽ mở ra, nơi tất cả các hành động trước đây của chúng ta sẽ được viết dưới dạng mã bằng ngôn ngữ M. Xin lưu ý rằng đường dẫn đến tệp mà chúng tôi đã nhập cho ví dụ được mã hóa cứng trong mã:
Bây giờ chúng ta hãy thực hiện một vài điều chỉnh:
Ý nghĩa của chúng rất đơn giản: dòng đầu tiên (đường dẫn tệp) => biến thủ tục của chúng ta thành một hàm với một đối số đường dẫn tập tinvà bên dưới chúng tôi thay đổi đường dẫn cố định thành giá trị của biến này.
Tất cả các. Bấm vào Kết thúc và sẽ thấy điều này:
Đừng sợ rằng dữ liệu đã biến mất - trên thực tế, mọi thứ đều ổn, mọi thứ sẽ như thế này 🙂 Chúng tôi đã tạo thành công hàm tùy chỉnh của mình, trong đó toàn bộ thuật toán nhập và xử lý dữ liệu được ghi nhớ mà không bị ràng buộc vào một tệp cụ thể . Vẫn phải đặt cho nó một cái tên dễ hiểu hơn (ví dụ: lấy dữ liệu) trong bảng điều khiển bên phải trong trường tên riêng và bạn có thể gặt hái Trang chủ - Đóng và tải xuống (Trang chủ - Đóng và Tải). Xin lưu ý rằng đường dẫn đến tệp mà chúng tôi đã nhập cho ví dụ này được mã hóa cứng trong mã. Bạn sẽ trở lại cửa sổ Microsoft Excel chính, nhưng một bảng có kết nối đã tạo với hàm của chúng tôi sẽ xuất hiện ở bên phải:
Bước 3. Thu thập tất cả các tệp
Tất cả phần khó nhất ở phía sau, phần dễ chịu và dễ dàng vẫn còn. Đi tới tab Dữ liệu - Tạo Truy vấn - Từ Tệp - Từ Thư mục (Dữ liệu - Truy vấn mới - Từ tệp - Từ thư mục) hoặc, nếu bạn có Excel 2010-2013, tương tự với tab Truy vấn nguồn. Trong cửa sổ xuất hiện, chỉ định thư mục chứa tất cả các tệp thành phố nguồn của chúng tôi và nhấp vào OK. Bước tiếp theo sẽ mở một cửa sổ nơi tất cả các tệp Excel được tìm thấy trong thư mục này (và các thư mục con của nó) và chi tiết cho từng tệp sẽ được liệt kê:
Nhấp chuột Thay đổi (Chỉnh sửa) và một lần nữa chúng ta vào cửa sổ trình soạn thảo truy vấn quen thuộc.
Bây giờ chúng ta cần thêm một cột khác vào bảng bằng hàm đã tạo của chúng ta, hàm này sẽ "kéo" dữ liệu từ mỗi tệp. Để thực hiện việc này, hãy chuyển đến tab Thêm cột - Cột tùy chỉnh (Thêm cột - Thêm cột tùy chỉnh) và trong cửa sổ xuất hiện, hãy nhập hàm của chúng tôi lấy dữ liệu, chỉ định cho nó như một đối số, đường dẫn đầy đủ đến mỗi tệp:
Sau khi nhấp vào OK cột đã tạo sẽ được thêm vào bảng của chúng tôi ở bên phải.
Bây giờ chúng ta hãy xóa tất cả các cột không cần thiết (như trong Excel, bằng cách sử dụng nút chuột phải - Hủy bỏ), chỉ để lại cột đã thêm và cột có tên tệp, vì tên này (chính xác hơn là thành phố) sẽ hữu ích khi có trong tổng dữ liệu cho mỗi hàng.
Và bây giờ là “khoảnh khắc tuyệt vời” - nhấp vào biểu tượng có các mũi tên riêng ở góc trên bên phải của cột được thêm với chức năng của chúng tôi:
… Bỏ chọn Sử dụng tên cột ban đầu làm tiền tố (Sử dụng tên cột gốc làm tiền tố)và nhấp OK. Và chức năng của chúng tôi sẽ tải và xử lý dữ liệu từ mỗi tệp, theo thuật toán đã ghi và thu thập mọi thứ trong một bảng chung:
Để có vẻ đẹp hoàn chỉnh, bạn cũng có thể xóa phần mở rộng .xlsx khỏi cột đầu tiên có tên tệp - bằng cách thay thế tiêu chuẩn bằng "không có gì" (nhấp chuột phải vào tiêu đề cột - Thay thế) và đổi tên cột này thành City. Và cũng sửa định dạng dữ liệu trong cột có ngày tháng.
Tất cả các! Bấm vào Trang chủ - Đóng và Tải (Trang chủ - Đóng & Tải). Tất cả dữ liệu được thu thập bởi truy vấn cho tất cả các thành phố sẽ được tải lên trang tính Excel hiện tại ở định dạng "bảng thông minh":
Kết nối đã tạo và chức năng lắp ráp của chúng tôi không cần phải được lưu riêng biệt theo bất kỳ cách nào - chúng được lưu cùng với tệp hiện tại theo cách thông thường.
Trong tương lai, với bất kỳ thay đổi nào trong thư mục (thêm hoặc xóa thành phố) hoặc trong tệp (thay đổi số dòng), chỉ cần nhấp chuột phải trực tiếp vào bảng hoặc vào truy vấn trong bảng điều khiển bên phải và chọn yêu cầu Cập nhật & Lưu (Làm mới) - Power Query sẽ "xây dựng lại" tất cả dữ liệu sau vài giây.
PS
Bản sửa đổi. Sau bản cập nhật tháng 2017 năm XNUMX, Power Query đã học cách tự thu thập sổ làm việc Excel, tức là không cần tạo một hàm riêng nữa - nó tự động xảy ra. Do đó, bước thứ hai từ bài viết này không còn cần thiết nữa và toàn bộ quá trình trở nên đơn giản hơn đáng kể:
- Chọn Tạo yêu cầu - Từ tệp - Từ thư mục - Chọn thư mục - OK
- Sau khi danh sách các tệp xuất hiện, nhấn Thay đổi
- Trong cửa sổ Trình soạn thảo Truy vấn, mở rộng cột Nhị phân bằng một mũi tên kép và chọn tên trang tính sẽ được lấy từ mỗi tệp
Và đó là tất cả! Bài hát!
- Thiết kế lại bảng chữ cái thành một tấm phẳng phù hợp để xây dựng bảng tổng hợp
- Xây dựng biểu đồ bong bóng động trong Power View
- Macro để tập hợp các trang tính từ các tệp Excel khác nhau thành một