Bill menanyakan pertanyaan minggu ini tentang data Excel yang berlebihan.
Saya membuat daftar transaksi bulanan di Excel. Di akhir bulan, saya perlu menghilangkan data yang tidak perlu dan menghasilkan total berdasarkan kode akun. Setiap kode akun dapat terjadi beberapa kali. Bill kemudian menjelaskan metodologi Excel-nya saat ini yang mirip dengan metode 1 di bawah ini untuk menghasilkan daftar kode akun yang unik, dengan rencana untuk menggunakan matriks rumus CSE untuk mendapatkan totalnya. Dia bertanya, apakah ada cara yang lebih mudah untuk sampai pada daftar unik kode akun dengan total untuk setiap akun?
Ini adalah pertanyaan liburan yang sempurna. Menjadi pengguna Lotus selama 15 tahun, saya mengenali metode Bill sebagai metode klasik untuk manipulasi data "cepat dan kotor" dari masa lalu yang baik dari Lotus rilis 2.1. Ini adalah musim untuk menghitung berkat kita. Ketika Anda memikirkan pertanyaan ini, Anda menyadari bahwa orang-orang di Microsoft telah memberikan kami sejumlah alat selama bertahun-tahun. Jika Anda menggunakan Excel 97, setidaknya ada lima metode untuk melakukan tugas ini, semuanya jauh lebih mudah daripada metode klasik yang dijelaskan oleh Bill. Saya akan menawarkan tutorial tentang lima metode minggu ini.
Kumpulan data saya yang disederhanakan memiliki nomor akun di kolom A dan jumlah di kolom B. Data dijalankan dari A2: B100. Itu tidak diurutkan di awal.
Metode 1
Gunakan pernyataan kreatif If dalam hubungannya dengan Tempel Nilai Khusus untuk menemukan jawabannya.
Mengingat alat baru yang ditawarkan oleh Excel, saya tidak lagi merekomendasikan metode ini. Saya dulu sering menggunakan ini sebelum hal-hal yang lebih baik datang dan masih ada situasi di mana itu berguna. Nama alternatif saya untuk ini adalah metode "The-Lotus-123-When-You-Were-Not-In-The-Mood-To-Use- @ DSUM". Berikut langkah-langkahnya.
- Urutkan data berdasarkan kolom A.
- Ciptakan rumus di kolom C yang akan menjaga jumlah berjalan menurut akun. Sel C2 adalah
=IF(A2=A1,C1+B2,B2). - Ciptakan formula di D yang akan mengidentifikasi entri terakhir untuk akun tertentu. Sel D2 adalah
=IF(A2=A3,FALSE,TRUE). - Salin C2: D2 ke semua baris Anda.
- Salin C2: D100. Lakukan Edit - PasteSpecial - Values kembali ke C2: D100 untuk mengubah rumus menjadi nilai.
- Urutkan berdasarkan kolom D menurun.
- Untuk baris yang memiliki BENAR di kolom D, Anda memiliki daftar unik nomor akun di A, dan total berjalan terakhir di C.
Kelebihan: Cepat. Yang Anda butuhkan hanyalah rasa yang tajam dalam menulis pernyataan IF.
Kekurangan: Ada cara yang lebih baik.
Metode 2
Gunakan Filter Data - Filter Lanjutan untuk mendapatkan daftar akun unik.
Pertanyaan Bill sebenarnya bagaimana cara mendapatkan daftar unik nomor rekening sehingga dia bisa menggunakan Rumus CSE untuk mendapatkan totalnya. Ini adalah metode untuk mendapatkan daftar nomor akun unik.
- Sorot A1: A100
- Dari Menu, pilih Data, Filter, Filter Lanjutan
- Klik tombol radio untuk "Salin ke lokasi lain".
- Klik kotak centang untuk "Unique Records Only".
- Pilih bagian kosong dari lembar kerja di mana Anda ingin daftar unik muncul. Masukkan ini di bidang "Salin ke:". (Perhatikan bidang ini berwarna abu-abu sampai Anda memilih "Salin ke lokasi lain".
- Klik OK. Nomor akun unik akan muncul di F1.
- Masukkan manipulasi downline, rumus array, dll. Untuk mendapatkan hasil Anda.
Kelebihan: Lebih Cepat dari Metode 1. Tidak perlu penyortiran.
Cons: Rumus CSE yang diperlukan setelah ini akan membuat kepala Anda pusing.
Metode 3
Gunakan Konsolidasi Data.
Kualitas hidup saya meningkat ketika Excel menawarkan Konsolidasi Data. Ini BESAR! Diperlukan waktu 30 detik untuk menyiapkannya, tetapi dieja mati untuk DSUM dan metode lain. Nomor akun Anda harus di sebelah kiri bidang numerik yang ingin Anda jumlahkan. Anda harus memiliki judul di atas setiap kolom. Anda perlu menetapkan nama rentang ke blok sel persegi panjang yang menyertakan nomor akun di sepanjang kolom kiri dan judul di bagian atas. Dalam hal ini, kisaran tersebut adalah A1: B100.
- Sorot A1: B100
- Tetapkan nama rentang ke area ini dengan mengklik di kotak nama (di sebelah kiri bilah rumus) dan ketik nama seperti "TotalMe". (Bergantian, gunakan Sisipkan - Nama).
- Letakkan penunjuk sel di bagian kosong di lembar kerja.
- Pilih Data - Konsolidasi
- Di bidang referensi, ketikkan nama rentang (TotalMe).
- Di bagian Gunakan Label Di, centang Baris Atas dan Kolom Kiri.
- Klik OK
Kelebihan: Ini adalah metode favorit saya. Tidak perlu penyortiran. Pintasannya adalah alt-D N (rangename) alt-T alt-L enter. Ini mudah diskalakan. Jika rentang Anda mencakup 12 kolom bulanan, jawabannya akan memiliki total untuk setiap bulan.
Kekurangan: Jika Anda melakukan Konsolidasi Data lain di lembar yang sama, Anda perlu menghapus nama rentang lama dari bidang Semua Referensi menggunakan tombol Hapus. Nomor rekening harus di sebelah kiri data numerik Anda. Ini sedikit lebih lambat dari tabel pivot yang terlihat untuk kumpulan data dengan 10.000+ catatan.
Metode 4
Gunakan Subtotal Data.
Ini adalah fitur yang keren. Karena data yang dihasilkan aneh untuk digunakan, saya lebih jarang menggunakannya daripada Data Consolidate.
- Urutkan berdasarkan kolom A menaik.
- Pilih sel mana saja dalam rentang data.
- Pilih Data - Subtotal dari menu.
- Secara default, Excel menawarkan subtotal kolom terakhir data Anda. Ini berfungsi dalam contoh ini, tetapi Anda sering harus menggulir daftar "Tambahkan Subtotal Ke:" untuk memilih bidang yang benar.
- Klik OK. Excel akan memasukkan baris baru pada setiap perubahan nomor rekening dengan total.
Setelah Anda memasukkan subtotal, Anda akan melihat 123 kecil muncul di bawah kotak nama. Klik 2 untuk melihat hanya satu baris per akun dengan total. Baca Salin Subtotal Excel untuk penjelasan tentang langkah-langkah khusus yang diperlukan untuk menyalinnya ke lokasi baru. Klik 3 untuk melihat semua baris. Kelebihan: Fitur Keren. Bagus untuk mencetak laporan dengan total dan pagebreak setelah setiap bagian.
Kekurangan: Data harus diurutkan terlebih dahulu. Lambat untuk banyak data. Anda harus menggunakan Goto-Special-VisbileCellsOnly untuk mendapatkan totalnya di tempat lain. Anda harus menggunakan Data-Subtotals-RemoveAll untuk kembali ke data asli Anda.
Metode 5
Gunakan Tabel Pivot.
Tabel pivot adalah yang paling serbaguna dari semuanya. Data Anda tidak harus diurutkan. Kolom numerik bisa berada di kiri atau kanan nomor rekening. Anda dapat dengan mudah menurunkan nomor akun atau melintasi halaman.
- Pilih sel mana saja dalam rentang data.
- Pilih Data - PivotTable dari menu.
- Terima default pada Langkah 1
- Pastikan rentang data di langkah 2 sudah benar (biasanya)
- Jika Anda menggunakan Excel 2000, klik tombol Layout pada langkah 3. Pengguna Excel 95 & 97 secara otomatis masuk ke layout sebagai langkah 3.
- Dalam dialog tata letak, seret tombol Akun dari sisi kanan dialog dan lepaskan di area Baris.
- Seret tombol Jumlah dari sisi kanan dialog dan lepaskan di area Data.
- Pengguna Excel 2000 klik OK, pengguna Excel 95/97 klik Next.
- Tentukan jika Anda menginginkan hasil di lembar baru atau di bagian tertentu dari lembar yang sudah ada. Baca selengkapnya tentang tabel pivot di Trik Tingkat Lanjut Tabel Pivot Excel.
- Tabel pivot menawarkan fungsionalitas luar biasa dan memudahkan tugas ini. Untuk menyalin hasil tabel pivot, Anda perlu melakukan Edit-PasteSpecial-Values, jika tidak Excel tidak akan membiarkan Anda memasukkan baris, dll.
Kelebihan: Cepat, Fleksibel, Kuat. Cepat, bahkan untuk banyak data.
Cons: Agak mengintimidasi.
Bill sekarang memiliki empat metode baru untuk menghilangkan data yang berlebihan. Meskipun metode ini belum tersedia sejak awal waktu, baik Lotus dan Excel telah menjadi inovator hebat yang memberikan cara yang lebih cepat kepada kita untuk menyelesaikan tugas biasa ini.
