Truy vấn bảng chéo - Xuất sang bảng điều khiển Excel

Chào các bạn,

Tôi quay lại với một câu hỏi khác --- lần này là về việc kết hợp các truy vấn bảng chéo để tạo báo cáo bảng điều khiển.

Lý lịch:

Tôi liên kết đến một bảng SQL với hàng triệu bản ghi. Từ bảng này, tôi truy xuất dữ liệu 12 tháng gần nhất, thu hẹp số lượng bản ghi xuống còn hơn một triệu. Do thiếu kiến ​​thức về SQL, tôi không sử dụng các truy vấn truyền trực tiếp. Tuy nhiên, tôi không gặp vấn đề về thời gian xử lý --- truy vấn bảng đơn của tôi với một triệu bản ghi chỉ mất chưa đến 3 giây để chạy. Một truy vấn bảng chéo trung bình với cùng số lượng bản ghi lại mất nhiều thời gian hơn. Từ bảng lớn này, tôi có khoảng 80 truy vấn bảng chéo phân tích các thông tin khác nhau theo công ty, chi nhánh, đại diện và khách hàng --- theo ngày, tháng, quý và năm. Các truy vấn bảng chéo riêng lẻ hoạt động hoàn hảo, cung cấp cho tôi chính xác những gì cần thiết. Thông tin từ mỗi truy vấn bảng chéo được sử dụng để tạo ra các chỉ số điền vào báo cáo bảng điều khiển. Vì tôi thích sử dụng Excel hơn Access để phân tích thêm, nên kết quả của mỗi truy vấn bảng chéo được xuất sang Excel. Đây là quy trình chạy hàng ngày lúc 5:00 sáng:

  • Một tác vụ theo lịch trình được chạy và gọi một thủ tục lưu trữ.
  • Thủ tục lưu trữ gọi một hàm công khai chạy từng truy vấn bảng chéo và xuất dữ liệu sang Excel. Mỗi truy vấn sẽ có một tab riêng.
  • Để giảm thiểu việc cuộn ngang (ít tab hơn), thông tin được xuất sang 4 bảng tính Excel khác nhau --- ngày, tháng, quý và năm.
  • Các truy vấn/bảng tính đã xuất được lưu trữ trên ổ đĩa dùng chung.
  • Dữ liệu đã lưu trữ được ánh xạ vào một bảng tính "dashboard" duy nhất và được sử dụng để phân tích thêm.
  • Bảng tính điều khiển được định dạng để tạo ra một báo cáo hấp dẫn, không chỉ tóm tắt dữ liệu từ mỗi truy vấn bảng chéo (với phân tích phụ) mà còn bao gồm cả biểu đồ.
  • Bảng điều khiển Excel được lưu trữ dưới dạng báo cáo PDF và được phân phát mỗi sáng.

Do số lượng truy vấn và dữ liệu lớn, quy trình nêu trên mất khoảng 20 phút để chạy. Vì đây là sự kiện được kích hoạt theo thời gian, nên thời gian xử lý không thành vấn đề vì nó diễn ra một giờ trước khi tôi đến văn phòng.

Câu hỏi của tôi, thực ra không phải là vấn đề (phương pháp trên hoạt động hoàn hảo), là: liệu có cách nào thông minh hơn để đạt được kết quả tương tự không? Phương pháp trên có lỗi thời không --- một cách làm rõ ràng của người mới bắt đầu khi giải quyết một công việc lớn? Báo cáo trên bảng điều khiển rất ấn tượng, nhưng tôi tò mò bạn sẽ xử lý kết quả tương tự như thế nào?

Cảm ơn rất nhiều,

Ken mới vào nghề




Trả lời:

Bạn đang phân tích dữ liệu của mình theo nhiều chiều khác nhau. Đó là trường hợp hoàn hảo để sử dụng bảng tổng hợp (pivot table) trong Excel. Điều này sẽ giúp người dùng tự khám phá ra kết quả của riêng mình.

Tiến thêm một bước nữa và sử dụng PowerPivot trực tiếp với cơ sở dữ liệu SQL; không cần đến Access ở giữa.

Chắc chắn rồi, việc này sẽ đòi hỏi bạn phải tìm hiểu thêm các khái niệm mới, nhưng trường hợp của bạn là một ví dụ hoàn hảo để sử dụng bảng tổng hợp (pivot table).



Trả lời:

Cảm ơn Tom. Tôi sẽ nghiên cứu PowerPivot so với các bảng SQL của mình. Tôi cho rằng Excel không phù hợp vì số lượng bản ghi quá nhiều, lên đến hàng triệu. Vì vậy, tôi đã sử dụng Access để tóm tắt dữ liệu (truy vấn bảng chéo) trước khi sử dụng đến sức mạnh của Excel.

Ken



Trả lời:

Bạn đang phân tích dữ liệu của mình theo nhiều chiều khác nhau. Đó là trường hợp hoàn hảo để sử dụng bảng tổng hợp (pivot table) trong Excel. Điều này sẽ giúp người dùng tự khám phá ra kết quả của riêng mình.

Tiến thêm một bước nữa và sử dụng PowerPivot trực tiếp với cơ sở dữ liệu SQL; không cần đến Access ở giữa.

Chắc chắn rồi, việc này sẽ đòi hỏi bạn phải tìm hiểu thêm các khái niệm mới, nhưng trường hợp của bạn là một ví dụ hoàn hảo để sử dụng bảng tổng hợp (pivot table).

Nếu một nửa số truy vấn bảng chéo dựa trên các bảng được kết hợp, liệu PowerPivot vẫn là lựa chọn tốt nhất? Và tôi đang sử dụng phiên bản Excel 2010 32 bit --- liệu nó có thể xử lý hơn một triệu bản ghi không? Tôi đoán rằng trong vòng một năm, số lượng bản ghi sẽ tăng lên 1,5 triệu.

Tin tốt là, những gì tôi đã xây dựng đang hoạt động tốt, vì vậy tôi có thời gian để xem xét các lựa chọn khác.

Ken

Comments

Post a Comment

Popular posts from this blog

Điều tôi muốn làm trong Excel 2010 là tạo một nút tùy chỉnh và gắn nó vào thanh công cụ Truy nhập nhanh và chạy một macro cụ thể.

Image
Điều tôi muốn làm trong Excel 2010 là tạo một nút tùy chỉnh và gắn nó vào thanh công cụ Truy nhập nhanh và chạy một macro cụ thể. Trong excel 2003, trong mục tùy chỉnh thanh công cụ, người ta có thể lấy một nút và chỉnh sửa hình ảnh của nút đó. Điều tôi muốn làm trong excel 2010 là tạo một nút tùy chỉnh và gắn nó vào thanh công cụ Truy cập nhanh và để nút đó chạy một macro cụ thể. Ví dụ, tạo nút có chữ G lớn và khi nhấp vào, nút sẽ thực hiện truy vấn web và nhập giá vàng từ kitco.com. Có cách nào để thực hiện việc này trong Excel 2010 không? Trả lời: Xin chào, Để bật tab Nhà phát triển 1. Trên tab Tệp , chọn Tùy chọn để mở hộp thoại Tùy chọn . 2. Nhấp vào Tùy chỉnh Ribbon ở phía bên trái của hộp thoại. 3. Trong mục Chọn lệnh từ ở phía bên trái hộp thoại, chọn Lệnh phổ biến . 4. Trong mục Tùy chỉnh Dải băng ở phía bên phải hộp thoại, chọn Tab chính trong hộp danh sách thả xuống, sau đó chọn hộp kiểm Nhà phát triển . 5. Nhấp vào OK . Để biết thêm thông tin về cách tạo ...
Read more

Mở tài liệu Excel và Word từ Outlook Lỗi - Không đủ bộ nhớ

Image
Mở tài liệu Excel và Word từ Outlook Lỗi - Không đủ bộ nhớ Chào mọi người, Tôi biết có nhiều bài viết liên quan đến vấn đề này nhưng câu trả lời không thỏa đáng với môi trường của tôi. Chúng tôi xử lý số lượng tài liệu mỗi ngày và lưu vào máy tính để bàn -> việc bỏ chặn không cắt được tài liệu đó cũng như vô hiệu hóa chế độ xem được bảo vệ bằng phần mềm ransomware gần đây mà NHS nhận được (bảo mật cần được giữ nguyên). Thông báo có nội dung: 'Không thể mở hoặc lưu thêm bất kỳ tài liệu nào vì không có đủ bộ nhớ hoặc dung lượng ổ đĩa' -> mặc dù việc thực hiện 2 giải pháp trên sẽ khắc phục được điều này (đó không phải là bộ nhớ, chỉ là một tin nhắn ngu ngốc nếu tôi thành thật) . Tuy nhiên, tôi chỉ cần có thể mở tệp ở chế độ xem được bảo vệ, không gặp lỗi. Có ai có thể sửa lỗi này 'đúng cách' chưa? Nếu vậy xin vui lòng cho tôi biết để tôi có thể triển khai rộng rãi hoạt động kinh doanh này. Tôi đã tìm thấy cài đặt gpe trong cấu hình người dùng -> thành phần windo...
Read more

Excel 2016 - mở tất cả các tệp trong MỘT phiên bản

Image
Excel 2016 - mở tất cả các tệp trong MỘT phiên bản Xin chào, Tôi gặp sự cố với Excel 2016 (16.0.10228.20051 32-bit): Tôi có một phiên bản Excel đang chạy. (NB - hộp kiểm " Bỏ qua các ứng dụng khác sử dụng Trao đổi dữ liệu động (DDE) " trong Tùy chọn nâng cao KHÔNG được chọn.) Điều tôi cần là - đối với tất cả các tệp Excel mà tôi sắp mở để mở trong phiên bản Excel đầu tiên và duy nhất của mình e, luôn luôn và bất kể: - tôi mở bao nhiêu tập tin cùng một lúc, - nơi chứa các tập tin (tức là trên ổ đĩa cục bộ hoặc trên OneDrive nên hệ thống cần tải chúng xuống trước khi mở) và kể cả khi mở tệp Excel từ các ứng dụng Office khác, tức là tệp đính kèm Outlook. Hành vi mà tôi thấy trong Excel 2016 tốt nhất là không nhất quán: 1. Khi mở nhiều tệp bổ sung cùng một lúc từ đĩa (chọn nhiều tệp/nhấp chuột phải/Mở) - nó sẽ mở n tệp đầu tiên trong phiên bản Excel đầu tiên, nhưng sau đó từ tệp n +1, nó bắt đầu mở chúng trong phiên bản mới . 2. Một biến thể của trường hợp trên là k...
Read more