Metode 1. Rumus
Mari kita mulai, untuk pemanasan, dengan opsi paling sederhana – formula. Jika kita memiliki tabel kecil yang diurutkan berdasarkan tanggal sebagai input, maka untuk menghitung total berjalan di kolom terpisah, kita memerlukan rumus dasar:
Fitur utama di sini adalah pengaturan rentang yang rumit di dalam fungsi SUM – referensi ke awal rentang dibuat absolut (dengan tanda dolar), dan hingga akhir – relatif (tanpa dolar). Dengan demikian, saat menyalin rumus ke seluruh kolom, kami mendapatkan rentang yang diperluas, jumlah yang kami hitung.
Kerugian dari pendekatan ini jelas:
- Tabel harus diurutkan berdasarkan tanggal.
- Saat menambahkan baris baru dengan data, rumus harus diperpanjang secara manual.
Metode 2. Tabel pivot
Metode ini sedikit lebih rumit, tetapi jauh lebih menyenangkan. Dan untuk memperburuk, mari kita pertimbangkan masalah yang lebih serius – tabel 2000 baris data, di mana tidak ada penyortiran berdasarkan kolom tanggal, tetapi ada pengulangan (yaitu kita dapat menjual beberapa kali pada hari yang sama):
Kami mengubah tabel asli kami menjadi pintasan keyboard "pintar" (dinamis) Ctrl+T atau tim Beranda – Format sebagai tabel (Beranda — Format sebagai Tabel), dan kemudian kita membuat tabel pivot di atasnya dengan perintah Sisipkan – PivotTable (Sisipkan — Tabel Pivot). Kami menempatkan tanggal di area baris dalam ringkasan, dan jumlah barang yang terjual di area nilai:
Harap dicatat bahwa jika Anda memiliki versi Excel yang tidak terlalu lama, maka tanggal secara otomatis dikelompokkan berdasarkan tahun, kuartal, dan bulan. Jika Anda memerlukan pengelompokan yang berbeda (atau tidak membutuhkannya sama sekali), maka Anda dapat memperbaikinya dengan mengklik kanan pada tanggal apa pun dan memilih perintah Grup / Pisahkan grup (Grup / Pisahkan).
Jika Anda ingin melihat total yang dihasilkan berdasarkan titik dan total berjalan di kolom terpisah, maka masuk akal untuk membuang bidang ke area nilai Terjual lagi untuk mendapatkan duplikat bidang – di dalamnya kita akan mengaktifkan tampilan total berjalan. Untuk melakukan ini, klik kanan pada bidang dan pilih perintah Perhitungan Tambahan – Total Kumulatif (Tampilkan Nilai sebagai — Total Berjalan):
Di sana Anda juga dapat memilih opsi untuk meningkatkan total sebagai persentase, dan di jendela berikutnya Anda harus memilih bidang yang akan diakumulasikan – dalam kasus kami, ini adalah bidang tanggal:
Keuntungan dari pendekatan ini:
- Sejumlah besar data dibaca dengan cepat.
- Tidak ada rumus yang perlu dimasukkan secara manual.
- Saat mengubah data sumber, cukup memperbarui ringkasan dengan tombol kanan mouse atau dengan perintah Data – Segarkan Semua.
Kerugiannya mengikuti dari fakta bahwa ini adalah ringkasan, yang berarti bahwa Anda tidak dapat melakukan apa pun yang Anda inginkan di dalamnya (menyisipkan baris, menulis rumus, membuat diagram, dll.) tidak akan berfungsi lagi.
Metode 3: Power Query
Mari muat tabel "pintar" kami dengan data sumber ke editor kueri Power Query menggunakan perintah Data – Dari Tabel/Rentang (Data — Dari Tabel/Rentang). Omong-omong, di versi Excel terbaru, namanya diubah - sekarang disebut Dengan daun (Dari Lembar):
Kemudian kita akan melakukan langkah-langkah berikut:
1. Urutkan tabel dalam urutan menaik berdasarkan kolom tanggal dengan perintah Urutkan naik dalam daftar drop-down filter di header tabel.
2. Beberapa saat kemudian, untuk menghitung total berjalan, kita memerlukan kolom tambahan dengan nomor baris ordinal. Mari tambahkan dengan perintah Tambahkan Kolom – Kolom Indeks – Dari 1 (Tambahkan kolom — Kolom indeks — Dari 1).
3. Juga, untuk menghitung total berjalan, kita memerlukan referensi ke kolom Terjual, tempat data ringkasan kami berada. Di Power Query, kolom juga disebut daftar (daftar) dan untuk mendapatkan tautan ke sana, klik kanan pada header kolom dan pilih perintah Merinci (Tampilkan detail). Ekspresi yang kita butuhkan akan muncul di bilah rumus, yang terdiri dari nama langkah sebelumnya #"Indeks ditambahkan", dari mana kita mengambil tabel dan nama kolom [Penjualan] dari tabel ini dalam tanda kurung siku:
Salin ekspresi ini ke clipboard untuk digunakan lebih lanjut.
4. Hapus langkah terakhir yang tidak perlu Terjual dan tambahkan kolom terhitung untuk menghitung total berjalan dengan perintah Menambahkan Kolom – Kolom Kustom (Tambahkan kolom — Kolom khusus). Rumus yang kita butuhkan akan terlihat seperti ini:
Disini fungsinya Daftar.Rentang mengambil daftar asli (kolom [Penjualan]) dan mengekstrak elemen darinya, mulai dari yang pertama (dalam rumus, ini adalah 0, karena penomoran di Power Query dimulai dari nol). Jumlah elemen yang akan diambil adalah nomor baris yang kita ambil dari kolom [Indeks]. Jadi fungsi ini untuk baris pertama hanya mengembalikan satu sel pertama dari kolom Terjual. Untuk baris kedua - sudah dua sel pertama, untuk yang ketiga - tiga yang pertama, dll.
Nah, lalu fungsinya Daftar.Jumlah jumlahkan nilai yang diekstraksi dan kami mendapatkan di setiap baris jumlah semua elemen sebelumnya, yaitu total kumulatif:
Tetap menghapus kolom Indeks yang tidak lagi kita perlukan dan mengunggah hasilnya kembali ke Excel dengan perintah Beranda – Tutup & Muat ke.
Masalah terpecahkan.
Cepat dan penuh energi
Pada prinsipnya, ini bisa dihentikan, tetapi ada lalat kecil di salep – permintaan yang kami buat bekerja dengan kecepatan kura-kura. Misalnya, pada PC saya yang bukan yang terlemah, tabel yang hanya berisi 2000 baris diproses dalam 17 detik. Bagaimana jika ada lebih banyak data?
Untuk mempercepat, Anda dapat menggunakan buffering menggunakan fungsi List.Buffer khusus, yang memuat daftar (daftar) yang diberikan kepadanya sebagai argumen ke dalam RAM, yang sangat mempercepat akses ke sana di masa mendatang. Dalam kasus kami, masuk akal untuk menyangga daftar #”Added index”[Sold], yang harus diakses oleh Power Query saat menghitung total yang berjalan di setiap baris tabel 2000 baris kami.
Untuk melakukannya, di editor Power Query pada tab Utama, klik tombol Editor Tingkat Lanjut (Beranda – Editor Tingkat Lanjut) untuk membuka kode sumber kueri kami dalam bahasa M yang ada di dalam Power Query:
Dan kemudian tambahkan baris dengan variabel di sana Daftarku, yang nilainya dikembalikan oleh fungsi buffering, dan pada langkah berikutnya kita mengganti panggilan ke daftar dengan variabel ini:
Setelah melakukan perubahan ini, kueri kami akan menjadi jauh lebih cepat dan akan mengatasi tabel 2000 baris hanya dalam 0.3 detik!
Hal lain, kan?
- Bagan Pareto (80/20) dan cara membuatnya di Excel
- Pencarian kata kunci dalam teks dan buffering kueri di Power Query