Có bao nhiêu hàng là quá nhiều cho Excel

Với các giới hạn của Excel và để tệp của bạn không trở nên quá lớn, tôi khuyên bạn nên chia đầu ra thành nhiều tệp thay vì chỉ nhiều trang tính.  

Trong cấu hình của Dữ liệu đầu ra, có một tùy chọn để đặt các bản ghi tối đa cho mỗi tệp. Bạn có thể đặt giá trị này là 1.000.000 để mỗi tệp sẽ chứa tối đa 1.000.000 bản ghi

Một lầm tưởng phổ biến mà tôi nghe rất thường xuyên là bạn không thể làm việc với hơn 1 triệu bản ghi trong Excel. Trên thực tế, lầm tưởng đúng là bạn không thể sử dụng nhiều hơn 1.048.576 hàng, vì đây là số hàng trên mỗi trang tính;

Trong bài đăng này, tôi sẽ lật tẩy huyền thoại này bằng cách tạo PivotTable từ 50 triệu bản ghi trong Excel

Để làm cho mọi thứ thú vị hơn, tôi sẽ nhập dữ liệu từ 20 tệp văn bản khác nhau (. csv) với 2. 5 triệu bản ghi mỗi

Để thực hiện điều này, tôi sẽ sử dụng hai công cụ Excel. Power Pivot và Power Query. Power Query còn được gọi là 'Nhận và Chuyển đổi' trong Excel 2016

Đối với bài đăng này, tôi sẽ sử dụng hồ sơ bán hàng với các trường sau. Khu vực, Sản phẩm, Ngày và Bán hàng. Mục tiêu mong muốn là có thể phân tích hiệu suất bán hàng theo năm và khu vực

Nếu bạn muốn làm theo, vui lòng tải xuống các tệp từ liên kết này

Nếu bạn không có Power Query trên máy tính của mình, bạn có thể tải xuống từ đây. Tải xuống Power Query

Quá trình tôi sẽ làm theo là

Nhập và làm sạch dữ liệu

Như đã đề cập trước đây, dữ liệu được chứa trong 20 tệp văn bản. Do đó, bước đầu tiên là nhập và nối thông tin từ các tệp này

Ghi chú. Tôi sẽ sử dụng Excel 2016, tuy nhiên, các bước giống như trên các phiên bản Excel trước. Nếu bạn có Excel 2010/2013, hãy chuyển đến tab Power Query thay vì tab Dữ liệu

Bước 1. Nhập dữ liệu vào Excel bằng Power Query

Chuyển đến dữ liệu Truy vấn mới từ tệp từ thư mục

Nhấp vào 'Duyệt' và duyệt tìm thư mục chứa các tệp, sau đó nhấp vào OK

Một tùy chọn khác (tùy chọn tôi thường sử dụng) là sao chép đường dẫn của thư mục và dán vào hộp đường dẫn thư mục

Khi bạn nhấp vào OK, nhấn Chỉnh sửa trên cửa sổ tiếp theo

Sau đó mở rộng nội dung bằng cách nhấp vào nút mũi tên kép

Sau khi nhập dữ liệu sẽ như thế này

Bước 2. Xóa các tiêu đề khỏi mỗi tệp

Các tệp sẽ được nhập có tiêu đề, vì vậy bạn phải xóa chúng. Đối với điều này, bạn có thể đi tới bất kỳ cột nào và xóa tên cột khỏi các tùy chọn. Ví dụ: chuyển đến cột 'Khu vực' và thiết lập bộ lọc để loại trừ từ 'Khu vực'

Bước 3. Tải dữ liệu vào Mô hình Dữ liệu Power Pivot

Sau khi loại bỏ các tiêu đề, bạn chỉ cần tải dữ liệu vào Mô hình Dữ liệu Power Pivot. Để thực hiện việc này, hãy vào File Close and Load To…

Trên hộp thoại 'Tải vào', chọn 'Chỉ tạo kết nối', sau đó nhấp vào hộp kiểm 'Thêm dữ liệu này vào Mô hình dữ liệu' và nhấp vào Tải

Sau khi bấm Tải, bạn sẽ có thể sử dụng dữ liệu trong Power Pivot

Sửa đổi Mô hình Dữ liệu Power Pivot

Để thực hiện các sửa đổi đối với Mô hình Dữ liệu, chẳng hạn như thêm các cột khác, bạn có thể mở cửa sổ Power Pivot

Để mở Power Pivot, hãy chuyển đến tab Power Pivot và bấm vào Quản lý

Nếu tab Power Pivot không hiển thị, hãy làm theo hướng dẫn trên liên kết này để bật nó

Đối với ví dụ này, tôi sẽ thêm một cột có tên là Năm để tính năm của cột ngày. Để thêm một cột, hãy chuyển đến cột ngoài cùng bên phải và bấm đúp vào tiêu đề, sau đó nhập tên mong muốn

Sau đó, trên hàng đầu tiên của cột mới, hãy nhập công thức '=YEAR([Date])' và nhấn enter. Số năm sẽ được tính sau khi nhấn Enter

Quan trọng. Một cách khác để thêm cột Năm là thực hiện trong Power Query. Bằng cách này, bạn không phải mở cửa sổ Power Pivot để sửa đổi Mô hình Dữ liệu vì Năm đã là một phần của dữ liệu nguồn

Tạo PivotTable

Khi Mô hình Dữ liệu đã sẵn sàng, bạn có thể tạo PivotTable bằng cách bấm vào nút PivotTable trên Tab Trang chủ của Cửa sổ Power Pivot

Sau đó chọn vị trí của PivotTable (New worksheet hoặc Existing worksheet) và nhấn OK

Khi bạn bấm OK, Danh sách trường PivotTable sẽ xuất hiện. Trong ví dụ này, hãy kéo trường Khu vực vào Khu vực Cột, trường Năm vào khu vực Hàng và trường Doanh số vào khu vực Giá trị

Sau các bước này, bạn sẽ nhận được PivotTable sau với Doanh số bán hàng theo Khu vực và Năm từ 50 TRIỆU bản ghi

Bạn có thể tiến xa hơn nữa và tạo PivotChart từ PivotTable hiện có

Bấm vào bất kỳ ô nào trong PivotTable và đi tới Chèn PivotChart

Sau đó vào Line Line with Markers

Bạn sẽ nhận được một biểu đồ như thế này (Sau một vài chỉnh sửa về định dạng, chẳng hạn như thêm Nhãn trục, Tiêu đề, …)

Cuối cùng, bạn có thể thêm các bộ lọc trực quan (Slicers và timelines). Slicers cho phép bạn lọc theo các trường phân loại;

Thêm một Slicer

Để thêm Bộ cắt cho PivotChart, hãy chọn PivotChart và trong tab Phân tích, nhấp vào 'Chèn Bộ cắt'

Chọn trường cần lọc (e. g. Vùng) và nhấp vào 'OK'

Thêm dòng thời gian

Để thêm Dòng thời gian cho PivotChart, hãy chọn PivotChart và trong tab Phân tích, nhấp vào 'Chèn Dòng thời gian'

Chọn trường ngày cần lọc (e. g. Ngày) và nhấp vào 'OK'

thì đấy

Kết quả cuối cùng phải là một biểu đồ động với khả năng lọc như hình bên dưới. Một lần nữa, bạn đang trực quan hóa hàng triệu bản ghi một cách linh hoạt

Bạn có thể tải xuống tệp cuối cùng từ liên kết này

Hãy chia sẻ bài đăng này với những người khác để họ cũng được hưởng lợi

Nếu bạn muốn nhận thông báo khi có bài viết mới. Đăng ký miễn phí Master Data Analysis

Excel có thể xử lý 10 triệu hàng không?

Bạn có thể có nhiều hơn 1048576 hàng trong Excel không?

Kích thước bao nhiêu là quá lớn đối với tệp Excel?

Bạn có thể có hơn 1000 hàng trong một trang tính không?