Sử dụng Excel để trích xuất dữ liệu từ các tệp riêng lẻ vào một bảng tổng hợp.
Chào bạn. Tôi đang gặp một vấn đề với Excel khiến tôi bối rối và rất mong nhận được lời khuyên. Trong một thư mục dùng chung, tôi có 24 tệp riêng lẻ (các cơ sở) có cấu trúc và bố cục giống hệt nhau. Tôi đã tạo một bảng tổng hợp, nhằm mục đích lấy dữ liệu từ cùng một ô trong mỗi thư mục riêng lẻ. Vấn đề là bảng tổng hợp mất vài phút để cập nhật, khi lấy dữ liệu từ mười ô trong mỗi tệp trong số 24 tệp. Đây là hàm tra cứu tôi đang sử dụng trong bảng tổng hợp cho một trong các ô đó:
='U:\Ngân sách tài chính 2020\Tệp FNS chuẩn 2020\[Pasadena 2020.xlsx]Tổng số kỳ trả lương'!F$44
Ngoài việc chuyển sang Access, liệu còn cách nào khác không? Trước đây, tôi đã thiết lập các tab riêng biệt cho từng cơ sở trên cùng một tệp, được tóm tắt trên một tab tổng hợp. Điều này tỏ ra khá rắc rối, và tôi muốn mỗi địa điểm đăng dữ liệu kỳ thanh toán của họ vào tệp riêng trong thư mục chia sẻ. Mọi người có ý kiến gì không? -Bill
Trả lời:
Bạn có thể bắt đầu sử dụng PowerPivot...
Nhìn thấy
(Bạn không cần phải xóa tiêu đề... sau khi kết hợp các tệp và chọn hàng đầu tiên làm tiêu đề từ tệp đầu tiên, hãy lọc ra các hàng có tiêu đề.)
và/hoặc
https://www.contextures.com/PowerPivot-Identical-Excel-Files.html
và/hoặc
http://www.ashishmathur.com/create-a-pivot-table-from-multiple-worksheets-in-the-same-workbook/
Trả lời:
Tôi cần dành thời gian để hiểu cách Power Pivot hoạt động... cảm ơn lời khuyên của bạn. Về cơ bản, trên mỗi sổ làm việc nguồn, tôi muốn sao chép dữ liệu từ hàng 44 trên tab thứ hai sang trang tính tóm tắt. Tôi đã thiết lập trang tính tóm tắt với tên sổ làm việc ở cột 1. Lưu ý rằng hàng 44 là các ô được tính toán trên các sổ làm việc nguồn. Power Pivot có còn là công cụ phù hợp để sử dụng không? Cách tôi đang làm hiện tại mất khoảng 5 giây để làm mới Tài liệu Tóm tắt. Ý định của tôi là khi các cơ sở nhập dữ liệu, nó sẽ tự động cập nhật Tài liệu Tóm tắt, hoặc hiệu quả hơn là khi tôi mở nó. Tôi có tất cả các sổ làm việc trong cùng một thư mục chia sẻ. Cảm ơn một lần nữa, và xin lỗi vì đã trả lời muộn.
Trả lời:
Có lẽ sẽ đơn giản hơn nếu sử dụng PowerQuery để lấy dữ liệu từ tất cả các tệp riêng lẻ.
Bạn thiết lập một truy vấn để thực hiện nhập dữ liệu ban đầu, sau đó bạn có thể áp dụng các quy tắc nhập tương tự cho các tệp khác. Và bạn có thể thiết lập PowerQuery để nhập các tệp mới khi chúng được chuyển vào. Mặc dù các bài viết tôi sẽ cung cấp sẽ đề cập đến PivotTables, nhưng bạn không nhất thiết phải làm vậy. Tuy nhiên, tôi cho rằng nếu bạn làm quen với PivotTables, bạn sẽ thấy chúng nhanh hơn và dễ sử dụng hơn so với công thức.
Hướng dẫn bắt đầu sử dụng “PowerQuery” / “Lấy & Chuyển đổi” > Bảng > Bảng tổng hợp > Biểu đồ tổng hợp > Bảng điều khiển…
WIKI: https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_win10-mso_o365b/get-started-with-get-transform-tables-pivottables/fea2f31d-7835-4f77-ad34-6fe3e6d07620?tm=1537369871055
Cũng giống như bất kỳ công cụ văn phòng nào khác, Excel có rất nhiều tính năng "ẩn". Tôi đã và đang tìm hiểu một số tính năng đó. Một trong những điểm thực sự tuyệt vời của các tính năng này là khả năng tự động hóa được tích hợp sẵn. Một số tính năng này đã có từ năm 2007, nhưng hiếm khi được nhắc đến. Tôi sẽ cung cấp các liên kết đến các hội thảo trực tuyến, video và bài viết miễn phí nói về các tính năng này:
PowerQuery > PowerPivot > Bảng > Bảng tổng hợp > Biểu đồ tổng hợp > Bảng điều khiển ...
Ở cuối trang wiki có các liên kết để tải xuống miễn phí sách điện tử PDF về các tính năng này.
PowerQuery (Get&Transform) được sử dụng để nhập dữ liệu từ hơn 80 nguồn dữ liệu khác nhau bên trong và bên ngoài Excel. Sau đó, nó cho phép bạn "làm sạch" và định dạng dữ liệu. PowerQuery ghi lại các thay đổi của bạn để bạn có thể nhập dữ liệu mới sau này và nó sẽ tự động áp dụng các thao tác tương tự.
PowerPivot / Mô hình hóa dữ liệu
Power Pivot là một công nghệ mô hình hóa dữ liệu cho phép bạn tạo mô hình dữ liệu, thiết lập mối quan hệ và thực hiện các phép tính. Với Power Pivot, bạn có thể làm việc với các tập dữ liệu lớn, xây dựng các mối quan hệ phức tạp và tạo các phép tính phức tạp (hoặc đơn giản), tất cả trong một môi trường hiệu suất cao và trong trải nghiệm quen thuộc của Excel. Tóm lại, nó cho phép bạn sử dụng nhiều nguồn dữ liệu. Sau đó, bạn có thể nhập, hợp nhất và thực hiện phân tích trên dữ liệu kết quả. Excel chỉ có thể lưu trữ tối đa 1 triệu hàng dữ liệu. Trước khi đạt đến giới hạn 1 triệu hàng, Excel sẽ bắt đầu hoạt động rất kém, đặc biệt nếu bạn có nhiều hàm VLOOKUP, v.v. Power Pivot nén dữ liệu nguồn và lưu trữ nó dưới dạng cơ sở dữ liệu trong sổ làm việc Excel. Điều này có nghĩa là bạn có thể lưu trữ hàng chục (thậm chí hàng trăm) triệu hàng dữ liệu trực tiếp trong sổ làm việc Excel theo cách được nén cao và hiệu suất cao. Bạn có thể phân phối kết quả cho người khác mà không cần họ phải truy cập vào dữ liệu nguồn gốc.
Bảng này lấy dữ liệu được nhập bởi PowerQuery, sau đó áp dụng định dạng đơn giản và cho phép bạn thực hiện một số phép tính đơn giản trên dữ liệu. Dữ liệu bảng đã được định dạng sau đó được chuyển đến Bảng tổng hợp (Pivot Table).
PivotTable thực hiện các phép tính tự động phức tạp hơn. Các tùy chọn thực sự tuyệt vời.
Biểu đồ PivotChart có thể được tạo từ bảng PivotTable để cung cấp cách diễn giải đồ họa động cho dữ liệu được tạo từ bảng PivotTable.
Bảng điều khiển (Dashboard) đơn giản là tập hợp các Bảng tổng hợp (PivotTable) và Biểu đồ tổng hợp (PivotChart) cung cấp cho người dùng khả năng kiểm soát linh hoạt nhiều dữ liệu trong một màn hình hiển thị nhỏ gọn duy nhất. Nhiều Bảng tổng hợp và Biểu đồ tổng hợp liên quan có thể được kết nối với cùng một Bộ lọc (Slicer) để tất cả đều hiển thị dữ liệu cụ thể theo cùng một tiêu chí lọc.
Trả lời:
CHÀO,
Power Query sẽ là công cụ phù hợp cho công việc này.
Comments
Post a Comment