Bagaimana cara membuka jutaan baris di excel?

Jika Anda biasanya memuat file CSV (comma-separated values) yang sangat besar atau file teks ke Excel, Anda mungkin mengalami pesan "File tidak dimuat sepenuhnya" yang ditakuti

Seperti yang dijelaskan pesan tersebut, file yang Anda coba muat terlalu besar untuk ditangani oleh Excel. Bagi saya, ini biasanya terjadi dengan file log besar dengan lebih dari 1 juta baris (secara teknis lebih dari 1.048.576 baris). Solusi yang diusulkan melibatkan memecah file menjadi potongan yang lebih kecil atau menggunakan aplikasi lain untuk memproses data (Access atau Power BI dapat menangani hal semacam ini). Saya menemukan ini di Excel berkali-kali sehingga saya akhirnya memposting blog tentang cara memecah file-file ini. Saya menyebut pos "Memisahkan log dengan PowerShell". Itu masih merepotkan dan saya tidak pernah bisa membuat ringkasan yang bagus dari seluruh kumpulan data dalam satu PivotTable

2. Solusinya

Nah, ternyata ada cara untuk menangani situasi ini hanya dengan menggunakan Excel, jika yang Anda coba lakukan pada akhirnya adalah menggunakan Tabel Pivot untuk memproses informasi. Excel memiliki cara untuk mengimpor data dari file teks tanpa benar-benar memuat file ke dalam lembaran (yang masih tidak membutuhkan lebih dari satu juta baris)

Anda pada dasarnya memuat data ke dalam apa yang disebut Excel sebagai Model Data, hanya menyimpan tautan ke file CSV asli. Setelah itu, Anda dapat membuat Tabel Pivot langsung dari Model Data. Dengan metode tersebut, Anda akan dapat memuat jutaan baris. Sejauh ini saya telah menggunakan ini hingga 8. 5 juta baris tanpa masalah sama sekali

Anda mungkin berpikir bahwa seluruh urusan membuat Model Data ini sulit, tetapi ternyata sangat sederhana

3. Langkah langkah

Mari kita bahas proses memuat CSV ke dalam Model Data. Tangkapan layar saya menggunakan Excel 2016 dan hanya itu yang saya uji sendiri. Saya memang mendengar bahwa fungsi ini juga tersedia di Excel 2013 dan Excel 2010, tetapi Anda harus mengujinya sendiri. Jika berhasil untuk Anda dengan versi yang lebih lama ini, silakan kirim komentar

Untuk memulai, Anda ingin membuka Excel dengan spreadsheet kosong dan melihat tab "Data". Inilah tampilannya bagi saya

Perintah yang akan kita gunakan adalah yang kedua pada tab itu, yang disebut "Kueri Baru". Di beberapa versi pra-rilis terbaru Excel yang mungkin muncul sebagai "Dapatkan Data"

Seperti yang ditunjukkan di atas, Anda ingin memilih "Kueri Baru", lalu "Dari File", lalu "Dari CSV". Setelah itu, Anda akan dimintai file dalam dialog biasa

Setelah file dibuka, Anda akan melihat pratinjau file, seperti yang ditunjukkan di bawah ini

Ada opsi "Edit" yang memungkinkan Anda melakukan beberapa pemfilteran dan pengeditan sebelum memuat data, tetapi kami akan mengambil rute paling sederhana dan menggunakan opsi "Muat Ke ..."
PENTING. Opsi "Muat" yang lebih sederhana tidak akan berfungsi di sini. Anda harus mengklik panah bawah kecil di sebelah memuat dan memilih "Muat Ke ..."

Sekarang inilah langkah kunci dalam keseluruhan proses. Dalam dialog "Muat Ke", Anda harus memilih "Tambahkan data ini ke Model Data", yang memungkinkan Anda untuk memilih opsi "Hanya Buat Koneksi". Ini berarti kami tidak memuat data ke lembar/tabel Excel. Ini penting, karena sheet memiliki batas 1 juta baris, tetapi Model Data tidak. Setelah itu, klik "Muat"

Dan dengan itu, Anda akan mulai memuat seluruh file besar. Dalam kasus saya, saya memiliki 2 juta baris. Ini mungkin memakan waktu cukup lama, jadi harap bersabar saat Excel memuat data

Satu hal yang akan Anda perhatikan adalah data yang baru Anda muat tidak muncul di mana pun di spreadsheet itu sendiri. Anda harus ingat bahwa data Anda berada di Model Data, yang terpisah dari lembar data biasa. Namun, jika Anda menyimpan file XLSX, Anda akan melihat bahwa file tersebut akan berukuran besar, jadi Anda tahu bahwa ada sesuatu di sana.

Nah, bagaimana cara melihat datanya? . Anda melihatnya sebagai opsi pertama di tab Power Pivot. Lihat di bawah

Ketika Anda mengklik Kelola, Anda akan dibawa ke Model Data seperti yang ditunjukkan di bawah ini

Di jendela khusus ini, yang disebut "Power Pivot untuk Excel", Anda akan melihat data di Model Data. Anda juga dapat menambahkan kolom terhitung, memfilter data, memformat kolom, dan melakukan semua jenis aktivitas manajemen. Ini bukan lembar data Excel biasa, jadi Anda tidak bisa membuat rumus Excel begitu saja di sini. Namun, jutaan baris Anda semua akan ada di sini, seperti yang Anda lihat di bawah. Itu adalah sesuatu yang biasanya tidak Anda lihat di Excel...

OKE. Tapi kami memuat jutaan baris untuk membuat PivotTable, bukan? . Anda hanya perlu mengkliknya

Opsi PivotTable dari Model Data bahkan tidak meminta sumber data. Diasumsikan dengan tepat bahwa Model Data adalah sumbernya dan yang harus Anda lakukan hanyalah menyediakan lokasi tempat Anda ingin membuat PivotTable. Anda dapat menggunakan Sheet1 kosong yang disertakan dengan spreadsheet kosong Anda

Pada titik ini, jika Anda menggunakan Tabel Pivot sebelumnya, Anda seharusnya sudah terbiasa. Kolom yang berasal dari Model Data akan tersedia untuk digunakan sebagai Kolom, Baris, Nilai, atau Filter di Tabel Pivot. Ini contohnya

Saya harap Anda menikmati tur Model Data dan Excel Power Pivot. Lain kali Anda terkena pesan "File tidak dimuat sepenuhnya", Anda akan memiliki cara baru untuk mengatasinya

Perhatikan bahwa ini adalah mekanisme yang sama yang digunakan Excel untuk memuat data dari database seperti SQL Server atau sumber data lain seperti Active Directory. Jadi, Anda memiliki lebih banyak untuk dijelajahi ...

P. S. Jika Anda memerlukan file CSV uji dengan lebih dari 1 juta baris untuk bereksperimen dengan ini, Anda dapat membaca posting blog lain ini tentang Menggunakan PowerShell untuk menghasilkan file CSV uji besar dengan data acak

P. P. S. Posting ini diperbarui pada 21-10-2022 untuk memperbaiki beberapa tautan yang rusak. Posting asli dari 2017-02-12

Bagaimana cara membuka lebih dari 1048576 baris di Excel?

Bagaimana cara membuka 10 juta baris di Excel?

Bagaimana cara membuka file Excel yang melebihi batas baris?

Bagaimana cara membuka file dengan lebih dari 1048576 baris?