jumlah berjalan (juga dikenali sebagaijumlah terkumpul) sangat biasa digunakan dalam banyak kes.Ini ialah metrik yang memberitahu anda jumlah nilai setakat ini.
Sebagai contoh, jika anda mempunyai data jualan bulanan, jumlah berjalan akan memberitahu anda jumlah jualan yang dibuat dari hari pertama bulan hingga tarikh tertentu.
Terdapat situasi lain di mana jumlah sering digunakan, seperti mengira baki tunai dalam penyata bank/lejar, mengira kalori dalam pelan makan, dsb.
Dalam Microsoft Excel, terdapat beberapa cara berbeza untuk mengira jumlah berjalan.
Kaedah yang anda pilih juga bergantung pada struktur data.
Sebagai contoh, jika anda mempunyai data jadual ringkas, anda boleh menggunakan formula SUM mudah, tetapi jika anda mempunyai jadual Excel, lebih baik menggunakan rujukan berstruktur.Anda juga boleh menggunakan Power Query untuk melakukan ini.
Dalam tutorial ini, saya akan membincangkan kesemuanyaKira jumlah berjalan dalam Excelkaedah yang berbeza.
Jadi mari kita mulakan!
Contents [show]
Kira jumlah berjalan menggunakan data jadual
Jika anda mempunyai data jadual (iaitu, jadual dalam Excel yang tidak ditukar kepada jadual Excel), anda boleh menggunakan beberapa formula mudah untuk mengira jumlah berjalan.
Gunakan operator penambahan
Katakan anda mempunyai data jualan mengikut tarikh dan anda mahu在 dalam lajur CmengiraJumlah berjalan.
Berikut adalah langkah-langkah untuk melakukan ini.
Langkah 1 – Dalam sel C2, sel pertama yang anda mahu jalankan jumlahnya, masukkan
=B2
Ini hanya mendapat nilai jualan yang sama dalam sel B2.
Langkah 2– Dalam sel C3, masukkan formula berikut:
= C2 + B3
Langkah 3– Gunakan formula pada keseluruhan lajur.Anda boleh menggunakan pemegang isian untuk memilih dan menyeretnya, atau hanya salin-tampal sel C3 ke semua sel yang tinggal (ini akan melaraskan rujukan secara automatik dan memberikan hasil yang betul).
Ini akan memberikan anda hasil yang ditunjukkan di bawah.
Ini adalah kaedah yang sangat mudah yang berfungsi dengan baik dalam kebanyakan kes.
Logiknya mudah - setiap sel mengambil nilai padanya (iaitu jumlah kumulatif sehingga hari sebelumnya) dan menambah nilai dalam sel bersebelahan (iaitu nilai jualan hari semasa).
Terdapat hanya satu kelemahan - jika anda memadamkan mana-mana baris sedia ada dalam set data ini, semua sel di bawah akan mengembalikan ralat rujukan (#REF!)
Jika set data anda mempunyai kemungkinan ini, gunakan kaedah seterusnya menggunakan formula SUM
Gunakan SUM dengan rujukan sel separa terkunci
Katakan anda mempunyai data jualan mengikut tarikh dan anda ingin mengira jumlah berjalan dalam lajur C.
Di bawah ialah formula SUM yang akan memberi anda jumlah berjalan.
=SUM($B$2:B2)
Biar saya terangkan bagaimana formula ini berfungsi.
Dalam formula SUM di atas, saya menggunakan rujukan untuk menambah sebagai $B$2:B2
- $B$2 - Ini ialah rujukan mutlak, yang bermaksud rujukan ini tidak berubah apabila saya menyalin formula yang sama dalam sel di bawah.Jadi apabila anda menyalin formula dalam sel di bawah, formula akan bertukar kepada SUM($B$2:B3)
- B2 - Ini adalah bahagian kedua rujukan, ia adalah rujukan relatif, yang bermaksud apabila saya menyalin formula ke bawah atau ke kanan, ini melaraskan.Jadi apabila menyalin formula dalam sel di bawah, nilainya menjadi B3
Perkara yang menarik tentang pendekatan ini ialah jika anda memadamkan sebarang baris dalam set data, formula ini melaraskan dan masih memberikan anda jumlah larian yang betul.
Kira jumlah larian dalam helaian Excel
Apabila bekerja dengan data jadual dalam Excel, adalah lebih baik untuk menukarnya kepada jadual Excel.Ia menjadikannya lebih mudah untuk mengurus data, dan ia juga memudahkan untuk menggunakan alatan seperti Power Query dan Power Pivot.
Menggunakan jadual Excel memberikan beberapa faedah, seperti rujukan berstruktur (yang menjadikannya sangat mudah untuk merujuk data dalam jadual dan menggunakannya dalam formula), dan pelarasan automatik rujukan semasa anda menambah atau mengalih keluar data daripada jadual.
Walaupun anda masih boleh menggunakan formula di atas yang saya tunjukkan dalam helaian Excel, izinkan saya menunjukkan kepada anda beberapa cara yang lebih baik untuk melakukannya.
Katakan anda mempunyai jadual Excel seperti di bawah, dan anda ingin mengira jumlah berjalan dalam lajur C.
Berikut ialah formula untuk melakukan ini:
=SUM(Data Jualan[[#Headers],[Jualan]]:[@Jualan])
Formula di atas mungkin kelihatan agak panjang, tetapi anda tidak perlu menulisnya sendiri.Perkara yang anda lihat dalam formula penjumlahan dipanggil rujukan berstruktur dan ia merupakan cara yang cekap untuk Excel merujuk kepada titik data tertentu dalam jadual Excel.
Contohnya, SalesData[[#Headers],[Sale]] merujuk kepada pengepala Jualan dalam jadual SalesData (SalesData ialah nama jadual Excel yang saya berikan semasa saya mencipta jadual)
Manakala [@Sale] merujuk kepada nilai dalam sel dalam baris yang sama lajur Jualan.
Saya hanya menerangkan perkara ini di sini untuk pemahaman anda, tetapi walaupun anda tidak tahu apa-apa tentang rujukan berstruktur, anda masih boleh mencipta formula ini dengan mudah.
Berikut adalah langkah-langkah untuk melakukan ini:
- Dalam sel C2, masukkan =SUM(
- Pilih sel B1, iaitu pengepala lajur dengan nilai jualan.Anda boleh menggunakan tetikus atau menggunakan kekunci anak panah.Anda akan melihat bahawa Excel secara automatik memasukkan rujukan berstruktur kepada sel
- Tambah: (titik bertitik)
- Pilih sel B2.Excel akan secara automatik memasukkan rujukan berstruktur pada sel sekali lagi
- Tutup kurungan dan tekan Enter
Anda juga akan mendapati bahawa anda tidak perlu menyalin keseluruhan lajur formula, helaian Excel melakukannya untuk anda secara automatik.
Satu lagi faedah pendekatan ini ialah jika anda menambah rekod baharu pada set data ini, helaian Excel secara automatik mengira jumlah berjalan untuk semua rekod baharu.
Walaupun kami memasukkan pengepala lajur dalam formula, perlu diingat bahawa formula mengabaikan teks pengepala dan hanya mempertimbangkan data dalam lajur
Kira jumlah larian dengan Power Query
Power Query ialah alat yang menakjubkan apabila ia berkaitan dengan menyambung ke pangkalan data, mengekstrak data daripada berbilang sumber dan mengubahnya sebelum memasukkannya ke dalam Excel.
Jika anda sudah menggunakan Power Query, adalah lebih cekap untuk menambah jumlah larian apabila mengubah data dalam Power Query Editor itu sendiri (daripada mula-mula mendapatkan data dalam Excel dan kemudian menambah jumlah larian menggunakan mana-mana kaedah di atas).
Walaupun tiada fungsi terbina dalam dalam Power Query untuk menambah jumlah berjalan (saya harap ada), anda masih boleh melakukannya dengan formula mudah.
Katakan anda mempunyai jadual Excel yang kelihatan seperti ini dan anda ingin menambah jumlah berjalan pada data ini:
Berikut adalah langkah-langkah untuk melakukan ini:
- Pilih mana-mana sel dalam jadual Excel
- klik data
- Dalam tab Dapatkan & Ubah, klik ikon daripada Jadual/Julat.Ini akan membuka jadual dalam Power Query Editor
- [Pilihan] Jika lajur tarikh anda belum diisih, klik ikon penapis dalam lajur tarikh, kemudian klik Isih Menaik
- Klik tab Tambah Lajur dalam Editor Pertanyaan Kuasa
- Dalam kumpulan Umum, klik menu lungsur turun Lajur Indeks (jangan klik ikon Lajur Indeks, tetapi anak panah serong hitam kecil di sebelahnya untuk mendedahkan lebih banyak pilihan)
- Klik pilihan "Dari 1".Melakukan ini akan menambah lajur indeks baharu yang akan bermula pada 1 dan memasukkan nombor yang ditambah 1 di seluruh lajur
- Klik ikon Lajur Tersuai (juga dalam tab Tambah Lajur)
- Dalam dialog Lajur Tersuai yang terbuka, masukkan nama untuk lajur baharu.Dalam contoh ini saya akan menggunakan nama "Running Total"
- Dalam medan Rumus lajur tersuai, masukkan formula berikut:List.Sum(List.Julat(#"Indeks Ditambah"[Jualan],0,[Indeks]))
- Pastikan terdapat kotak pilihan di bahagian bawah dialog yang menyatakan "Tiada ralat sintaks dikesan"
- Klik OK.Ini akan menambah jumlah lajur berjalan baharu
- lajur indeks jatuh
- Klik tab Fail, kemudian klik Tutup dan Muatkan
Langkah di atas akan memasukkan lembaran kerja baharu dalam buku kerja anda dengan jadual dengan jumlah yang sedang berjalan.
Sekarang, jika anda fikir ini adalah terlalu banyak langkah berbanding pendekatan sebelumnya dengan formula mudah, anda betul.
Jika anda sudah mempunyai set data dan anda hanya perlu menambah jumlah yang sedang berjalan, maka sebaiknya jangan gunakan Power Query.
Menggunakan Power Query masuk akal dalam situasi di mana anda perlu mengekstrak data daripada pangkalan data atau menggabungkan data daripada beberapa buku kerja yang berbeza dan juga menambah jumlah larian kepada mereka dalam proses.
Selain itu, sebaik sahaja anda mengautomasikannya dengan Power Query, apabila set data anda berubah seterusnya, anda tidak perlu melakukannya lagi, anda hanya boleh memuat semula pertanyaan dan ia akan memberikan anda hasil berdasarkan set data baharu.
Bagaimana ianya berfungsi?
Sekarang izinkan saya menerangkan dengan cepat apa yang berlaku dengan pendekatan ini.
Perkara pertama yang kita lakukan dalam Editor Pertanyaan Kuasa ialah memasukkan lajur indeks yang bermula pada 1 dan meningkat apabila ia turun ke dalam sel.
Kami melakukan ini kerana kami perlu menggunakan lajur ini apabila mengira jumlah berjalan dalam lajur lain yang disisipkan dalam langkah seterusnya.
Kemudian kami memasukkan lajur tersuai dan menggunakan formula berikut
List.Sum(List.Julat(#"Indeks Ditambah"[Jualan],0,[Indeks]))
Ini ialah formula List.Sum yang akan memberikan anda jumlah julat yang dinyatakan di dalamnya.
Julat ditentukan menggunakan fungsi Senarai.Julat.
Fungsi List.Range mengambil sebagai output julat yang ditentukan dalam lajur jualan dan julat berbeza mengikut nilai Indeks.Sebagai contoh, untuk rekod pertama, julat ialah nilai jualan pertama.Julat ini berkembang apabila anda menuruni sel.
Jadi, untuk sel pertama.List.Sum hanya akan memberikan anda jumlah nilai jualan pertama, untuk sel kedua ia akan memberikan anda jumlah dua nilai jualan pertama, dan seterusnya.
Walaupun pendekatan ini berfungsi dengan baik, pendekatan ini boleh menjadi sangat perlahan untuk set data yang besar (ribuan baris).
Kira jumlah larian berdasarkan kriteria
Setakat ini, kami telah melihat contoh pengiraan jumlah larian semua nilai dalam lajur.
Tetapi dalam beberapa kes, anda mungkin mahu mengira jumlah berjalan untuk rekod tertentu.
Sebagai contoh, di bawah saya mempunyai set data di mana saya ingin mengira jumlah pencetak dan pengimbas yang sedang berjalan dalam dua lajur berbeza.
Ini boleh dilakukan menggunakan formula SUMIF, yang mengira jumlah berjalan sambil memastikan syarat tertentu dipenuhi.
Berikut ialah formula untuk melakukan ini untuk lajur pencetak:
=SUMIF($C$2:C2,$D$1,$B$2:B2)
Begitu juga, untuk mengira jumlah berjalan pengimbas, gunakan formula berikut:
=SUMIF($C$2:C2,$E$1,$B$2:B2)
Dalam formula di atas, saya telah menggunakan SUMIF yang akan memberi saya jumlah dalam julat apabila syarat tertentu dipenuhi.
Formula mengambil tiga parameter:
- pelbagai: Ini ialah julat syarat yang akan disemak terhadap syarat yang ditentukan
- kriteria: ini adalah kriteria yang hanya akan disemak jika kriteria ini dipenuhi, maka nilai dalam parameter ketiga, julat jumlah akan ditambah
- [julat_jumlah]: ini ialah julat jumlah nilai untuk ditambah apabila syarat dipenuhi
Di samping itu, dipelbagai和jumlah_julatparameter, saya telah mengunci bahagian kedua rujukan supaya apabila kita bergerak ke bawah sel, julat terus berkembang.Ini membolehkan kami hanya mempertimbangkan dan menambah nilai sehingga julat itu (oleh itu jumlah yang sedang berjalan).
Dalam formula ini, saya menggunakan lajur pengepala (Pencetak dan Pengimbas) sebagai kriteria.Anda juga boleh hardcode standard jika tajuk lajur anda tidak betul-betul sama dengan teks standard.
Jika anda perlu menyemak berbilang syarat, anda boleh menggunakan formula SUMIFS.
Menjalankan jumlah dalam jadual pangsi
Jika anda ingin menambah jumlah berjalan pada hasil Jadual Pangsi anda, anda boleh melakukannya dengan mudah menggunakan fungsi terbina dalam dalam Jadual Pangsi.
Katakan anda mempunyai jadual pangsi seperti di bawah dengan tarikh dalam satu lajur dan nilai jualan di lajur yang lain.
Berikut ialah langkah untuk menambah lajur tambahan yang akan menunjukkan jualan terkumpul mengikut tarikh:
- Seret medan Jualan dan lepaskannya dalam kawasan Nilai.
- Ini akan menambah satu lagi lajur dengan nilai jualan
- Klik pilihan Jumlah Jualan 2 dalam kawasan Nilai
- Klik pada pilihan Tetapan Medan Nilai
- Dalam dialog Tetapan Medan Nilai, tukar Nama Tersuai kepada Jumlah Berjalan
- Klik tab "Nilai Paparan Sebagai".
- Dalam menu lungsur turun Nilai Paparan sebagai, pilih pilihan "Jumlah Masuk".
- Dalam pilihan Medan Asas, pastikan Tarikh dipilih
- Klik OK
Langkah di atas akan menukar lajur jualan kedua kepada lajur "jumlah berjalan".
Jadi, ini adalah beberapa kaedah yang boleh anda gunakan untuk mengira jumlah berjalan dalam Excel.Jika anda mempunyai data dalam format jadual, anda boleh menggunakan formula ringkas dan jika anda mempunyai jadual Excel, anda boleh menggunakan formula yang menggunakan rujukan berstruktur.
Saya juga membincangkan cara mengira jumlah larian menggunakan Power Query dan PivotTable.
Saya harap anda mendapati tutorial ini berguna.
