Kaedah 1. Formula
Mari mulakan, untuk memanaskan badan, dengan pilihan paling mudah – formula. Jika kita mempunyai jadual kecil yang diisih mengikut tarikh sebagai input, kemudian untuk mengira jumlah berjalan dalam lajur yang berasingan, kita memerlukan formula asas:
Ciri utama di sini ialah penetapan julat yang rumit di dalam fungsi SUM - rujukan kepada permulaan julat dibuat mutlak (dengan tanda dolar), dan hingga akhir - relatif (tanpa dolar). Sehubungan itu, apabila menyalin formula ke seluruh lajur, kami mendapat julat yang berkembang, yang jumlahnya kami kira.
Kelemahan pendekatan ini adalah jelas:
- Jadual mesti diisih mengikut tarikh.
- Apabila menambah baris baharu dengan data, formula perlu dilanjutkan secara manual.
Kaedah 2. Jadual pangsi
Kaedah ini sedikit lebih rumit, tetapi lebih menyenangkan. Dan untuk memburukkan lagi, mari kita pertimbangkan masalah yang lebih serius – jadual 2000 baris data, di mana tiada pengisihan mengikut lajur tarikh, tetapi terdapat pengulangan (iaitu kita boleh menjual beberapa kali pada hari yang sama):
Kami menukar jadual asal kami kepada pintasan papan kekunci "pintar" (dinamik). Ctrl+T atau pasukan Laman Utama – Format sebagai jadual (Laman Utama — Format sebagai Jadual), dan kemudian kami membina jadual pangsi di atasnya dengan arahan Sisipkan – Jadual Pangsi (Sisipkan — Jadual Pangsi). Kami meletakkan tarikh dalam kawasan baris dalam ringkasan, dan bilangan barang yang dijual dalam kawasan nilai:
Sila ambil perhatian bahawa jika anda mempunyai versi Excel yang tidak lama, maka tarikh dikumpulkan secara automatik mengikut tahun, suku tahun dan bulan. Jika anda memerlukan kumpulan yang berbeza (atau tidak memerlukannya langsung), maka anda boleh membetulkannya dengan mengklik kanan pada mana-mana tarikh dan memilih arahan Kumpulan / Nyahkumpulan (Kumpulkan / Nyahkumpulan).
Jika anda ingin melihat kedua-dua jumlah yang terhasil mengikut noktah dan jumlah berjalan dalam lajur yang berasingan, maka masuk akal untuk membuang medan ke dalam kawasan nilai Jual sekali lagi untuk mendapatkan pendua medan – di dalamnya kami akan menghidupkan paparan jumlah berjalan. Untuk melakukan ini, klik kanan pada medan dan pilih arahan Pengiraan Tambahan – Jumlah Kumulatif (Tunjukkan Nilai sebagai — Jumlah Berjalan):
Di sana anda juga boleh memilih pilihan untuk menambah jumlah sebagai peratusan, dan dalam tetingkap seterusnya anda perlu memilih medan yang akan digunakan pengumpulan - dalam kes kami, ini ialah medan tarikh:
Kelebihan pendekatan ini:
- Sebilangan besar data dibaca dengan cepat.
- Tiada formula perlu dimasukkan secara manual.
- Apabila menukar dalam data sumber, cukup untuk mengemas kini ringkasan dengan butang kanan tetikus atau dengan perintah Data – Refresh All.
Kelemahan berikutan daripada fakta bahawa ini adalah ringkasan, yang bermaksud bahawa anda tidak boleh melakukan apa sahaja yang anda mahu di dalamnya (memasukkan baris, menulis formula, membina sebarang rajah, dll.) tidak akan berfungsi lagi.
Kaedah 3: Power Query
Mari muatkan jadual "pintar" kami dengan data sumber ke dalam editor pertanyaan Power Query menggunakan arahan Data – Daripada Jadual/Julat (Data — Daripada Jadual/Julat). Dalam versi terkini Excel, dengan cara itu, ia telah dinamakan semula - kini ia dipanggil Dengan daun (Dari Helaian):
Kemudian kami akan melakukan langkah-langkah berikut:
1. Isih jadual dalam tertib menaik mengikut lajur tarikh dengan arahan Isih menaik dalam senarai juntai bawah penapis dalam pengepala jadual.
2. Tidak lama kemudian, untuk mengira jumlah berjalan, kita memerlukan lajur tambahan dengan nombor baris ordinal. Mari tambahkannya dengan arahan Tambah Lajur – Lajur Indeks – Daripada 1 (Tambah lajur — Lajur indeks — Daripada 1).
3. Juga, untuk mengira jumlah berjalan, kita memerlukan rujukan kepada lajur Jual, di mana data ringkasan kami terletak. Dalam Power Query, lajur juga dipanggil senarai (senarai) dan untuk mendapatkan pautan kepadanya, klik kanan pada pengepala lajur dan pilih arahan Perincian (Tunjukkan butiran). Ungkapan yang kami perlukan akan muncul dalam bar formula, yang terdiri daripada nama langkah sebelumnya #”Indeks ditambah”, dari mana kita mengambil jadual dan nama lajur [Jualan] daripada jadual ini dalam kurungan segi empat sama:
Salin ungkapan ini ke papan keratan untuk kegunaan selanjutnya.
4. Padamkan lagi langkah terakhir yang tidak perlu Jual dan sebaliknya tambah lajur yang dikira untuk mengira jumlah berjalan dengan arahan Menambah Lajur – Lajur Tersuai (Tambah lajur — Lajur tersuai). Formula yang kita perlukan akan kelihatan seperti ini:
Di sini fungsinya Senarai.Julat mengambil senarai asal (lajur [Jualan]) dan mengekstrak elemen daripadanya, bermula dari yang pertama (dalam formula, ini adalah 0, kerana penomboran dalam Power Query bermula dari sifar). Bilangan elemen untuk diambil ialah nombor baris yang kami ambil daripada lajur [Indeks]. Jadi fungsi ini untuk baris pertama hanya mengembalikan satu sel pertama lajur Jual. Untuk baris kedua - sudah menjadi dua sel pertama, untuk baris ketiga - tiga pertama, dsb.
Nah, kemudian fungsinya Senarai.Jumlah menjumlahkan nilai yang diekstrak dan kami mendapat dalam setiap baris jumlah semua elemen sebelumnya, iaitu jumlah kumulatif:
Ia kekal untuk memadamkan lajur Indeks yang kami tidak perlukan lagi dan memuat naik hasil kembali ke Excel dengan arahan Laman Utama - Tutup & Muatkan.
Masalahnya diselesaikan.
Cepat dan marah
Pada dasarnya, ini boleh dihentikan, tetapi terdapat lalat kecil dalam salap - permintaan yang kami buat berfungsi pada kelajuan penyu. Sebagai contoh, pada PC saya yang bukan yang paling lemah, jadual hanya 2000 baris diproses dalam 17 saat. Bagaimana jika terdapat lebih banyak data?
Untuk mempercepatkan, anda boleh menggunakan penimbalan menggunakan fungsi List.Buffer khas, yang memuatkan senarai (senarai) yang diberikan kepadanya sebagai hujah ke dalam RAM, yang sangat mempercepatkan akses kepadanya pada masa hadapan. Dalam kes kami, adalah wajar untuk menimbal senarai #”Indeks Ditambah”[Dijual], yang Power Query perlu akses semasa mengira jumlah berjalan dalam setiap baris jadual 2000 baris kami.
Untuk melakukan ini, dalam editor Power Query pada tab Utama, klik butang Editor Lanjutan (Laman Utama – Editor Lanjutan) untuk membuka kod sumber pertanyaan kami dalam bahasa M yang terbina dalam Power Query:
Dan kemudian tambah satu baris dengan pembolehubah di sana Senarai Saya, nilai yang dikembalikan oleh fungsi penimbalan, dan pada langkah seterusnya kita menggantikan panggilan ke senarai dengan pembolehubah ini:
Selepas membuat perubahan ini, pertanyaan kami akan menjadi lebih pantas dan akan mengatasi jadual 2000 baris dalam masa 0.3 saat sahaja!
Perkara lain, bukan? 🙂
- Carta pareto (80/20) dan cara membinanya dalam Excel
- Carian kata kunci dalam teks dan penimbalan pertanyaan dalam Power Query