Episod ini menunjukkan tiga cara untuk melakukan jumlah keseluruhan.
Jumlah berjalan adalah, untuk senarai nilai numerik, jumlah nilai dari baris pertama hingga baris jumlah berjalan. Kegunaan umum dari jumlah berjalan adalah dalam daftar buku cek atau lembaran perakaunan. Terdapat banyak cara untuk membuat jumlah keseluruhan-dua yang diterangkan di bawah.
Teknik paling mudah adalah, pada setiap baris, menambahkan jumlah berjalan dari baris di atas ke nilai dalam baris. Jadi formula pertama dalam baris 2 adalah:
=SUM(D1,C2)
Sebab kami menggunakan fungsi SUM adalah kerana, pada baris pertama, kami melihat tajuk pada baris di atas. Sekiranya kita menggunakan formula yang lebih sederhana dan lebih intuitif =D1+C2maka ralat akan dihasilkan kerana nilai tajuk adalah teks berbanding angka. Keajaibannya ialah fungsi SUM mengabaikan nilai teks, yang ditambahkan sebagai nilai sifar. Apabila formula disalin ke semua baris di mana jumlah berjalan diinginkan, rujukan sel disesuaikan dengan sewajarnya:
Teknik lain juga menggunakan fungsi SUM tetapi setiap formula menjumlahkan semua nilai dari baris pertama hingga baris yang menunjukkan jumlah berjalan. Dalam kes ini, kita menggunakan tanda dolar ($) untuk menjadikan sel pertama dalam rujukan sebagai rujukan mutlak yang bermaksud ia tidak disesuaikan ketika disalin:
Kedua-dua teknik tidak terpengaruh dengan menyusun dan menghapus baris tetapi, ketika memasukkan baris, rumus harus disalin ke baris baru.
Excel 2007 memperkenalkan Jadual yang merupakan pelaksanaan semula dari Daftar di Excel 2003. Jadual memperkenalkan sejumlah ciri yang sangat berguna untuk jadual data seperti memformat, menyusun, dan menyaring. Dengan pengenalan Jadual, kami juga diberikan cara baru untuk merujuk bahagian-bahagian Jadual. Gaya rujukan baru ini disebut rujukan berstruktur.
Untuk menukar contoh di atas menjadi Jadual, kami memilih data yang ingin kami sertakan dalam Jadual dan tekan Ctrl + T. Setelah memaparkan permintaan yang meminta kami mengesahkan julat Jadual dan sama ada atau tidak ada tajuk yang ada, Excel menukar data ke dalam Jadual berformat:
Perhatikan bahawa formula yang kami masukkan sebelumnya tetap sama.
Salah satu ciri berguna yang ditawarkan oleh Tabel adalah pemformatan dan penyelenggaraan formula secara automatik ketika baris ditambahkan, dikeluarkan, disusun, dan disaring. Ini adalah penyelenggaraan formula yang akan kita fokuskan dan boleh menjadi masalah. Untuk memastikan Jadual berfungsi semasa dimanipulasi, Excel menggunakan lajur yang dikira yang merupakan lajur dengan formula seperti lajur D dalam contoh di atas. Apabila baris baru dimasukkan ditambahkan ke bahagian bawah, Excel secara automatik mengisi baris baru dengan formula "lalai" untuk lajur itu. Masalah dengan contoh di atas adalah bahawa Excel keliru dengan formula standard dan tidak selalu mengatasinya dengan betul. Ini menjadi jelas apabila baris baru ditambahkan ke bahagian bawah Jadual (dengan memilih sel kanan bawah dalam Jadual dan menekan TAB):
Kekurangan ini diselesaikan dengan menggunakan rujukan berstruktur yang lebih baru. Rujukan berstruktur menghilangkan keperluan untuk merujuk sel tertentu menggunakan gaya rujukan A1 atau R1C1 dan sebaliknya menggunakan nama lajur dan kata kunci lain untuk mengenal pasti dan merujuk bahagian-bahagian Jadual. Sebagai contoh, untuk membuat formula keseluruhan berjalan yang sama yang digunakan di atas tetapi menggunakan rujukan berstruktur, kita mempunyai:
=SUM(INDEX((Sales),1):(@Sales))
Dalam contoh ini kita memiliki referensi ke nama lajur, "Penjualan", bersama dengan tanda at (@) untuk merujuk baris dalam lajur di mana formula itu terletak yang juga dikenal sebagai baris saat ini.
Untuk melaksanakan contoh pertama di atas di mana kita menambahkan nilai total berjalan pada baris sebelumnya ke jumlah penjualan pada baris semasa, anda boleh menggunakan fungsi OFFSET:
=SUM(OFFSET((@(Running Total)),-1,0),(@Sales))
Sekiranya jumlah yang digunakan untuk menghitung jumlah berjalan ada dalam dua lajur, misalnya satu untuk "Debit" dan satu untuk "Kredit", maka rumusnya adalah:
=SUM(INDEX( (Credit),1):(@Credit))- SUM(INDEX( (Debit),1):(@Debit))
Di sini kita menggunakan fungsi INDEX untuk mencari sel Kredit dan Debit baris pertama, dan menjumlahkan keseluruhan lajur hingga dan termasuk nilai baris semasa. Jumlah berjalan adalah jumlah semua kredit hingga dan termasuk baris semasa ditolak jumlah semua debit hingga dan termasuk baris semasa.
Untuk maklumat lebih lanjut mengenai rujukan berstruktur khususnya dan Jadual secara umum, kami mengesyorkan buku Excel Tables: Panduan Lengkap untuk Membuat, Menggunakan dan Mengautomasikan Daftar dan Jadual oleh Zack Barresse dan Kevin Jones.
Semasa saya meminta pembaca memilih petua kegemaran mereka, jadualnya sangat popular. Terima kasih kepada Peter Albert, Snorre Eikeland, Nancy Federice, Colin Michael, James E. Moede, Keyur Patel, dan Paul Peton kerana mencadangkan ciri ini. Peter Albert menulis Petua bonus Rujukan Boleh Dibaca. Zack Barresse menulis tip bonus Running Totals. Empat pembaca mencadangkan menggunakan OFFSET untuk membuat julat pengembangan untuk carta dinamik: Charley Baak, Don Knowles, Francis Logan, dan Cecelia Rieb. Jadual sekarang melakukan perkara yang sama dalam kebanyakan kes.
Tonton Video
- Episod ini menunjukkan tiga cara untuk melakukan jumlah keseluruhan
- Kaedah pertama mempunyai formula yang berbeza dalam Baris 2 daripada semua baris lain
- Kaedah pertama ialah = Kiri di baris 2 dan = Kiri + Atas di baris 3 hingga N
- Sekiranya anda cuba menggunakan formula yang sama, anda mendapat ralat #Value dengan = Total + Number
- Kaedah 2 menggunakan
=SUM(Up,Left)atau=SUM(Previous Total,This Row Amount) - SUM mengabaikan Teks sehingga anda tidak mendapat NILAI kesalahan
- Kaedah 3 menggunakan julat yang berkembang:
=SUM(B$2:B2) - Julat pengembangannya sejuk tetapi perlahan
- Baca buku putih Charles Williams mengenai Excel Formula Speed
- Kaedah ketiga adalah masalah semasa anda menggunakan Ctrl + T dan menambahkan baris baru
- Excel tidak dapat mengetahui cara menulis formula
- Penyelesaiannya memerlukan sedikit pengetahuan mengenai rujukan berstruktur dalam Jadual
- Penyelesaian 1 adalah perlahan
=SUM(INDEX((Qty),1):(@Qty)) - Penyelesaian 2 adalah tidak menentu
=SUM(OFFSET((@Total),-1,0),(@Qty)) - (@Qty) merujuk kepada Qty pada baris ini
- (Qty) merujuk kepada semua nilai Qty
Transkrip Video
Belajar Excel untuk Podcast, Episode 2004 - Menjalankan Jumlah
Saya akan menyiarkan keseluruhan buku ini. Klik pada saya di sudut kanan atas untuk melanggan.
Hai selamat datang kembali ke netcast sel mistik. Saya Bill Jelen. Sekarang topik ini dalam buku, saya disumbangkan oleh rakan saya Zach Parise. Bercakap tentang jadual Excel, Zach adalah pakar dunia dalam jadual Excel. Dia telah menulis sebuah buku mengenai jadual Excel, tetapi pertama-tama mari kita bincangkan mengenai jumlah keseluruhan yang tidak terdapat dalam jadual.
Oleh itu, ketika saya berfikir tentang jumlah keseluruhan, ada tiga cara yang berbeza untuk melakukan jumlah berjalan, dan cara yang selalu saya mulakan adalah pada baris pertama yang anda katakan, tambah nilai. Jadi sama dengan apa sahaja di sebelah kiri saya. Baiklah, format ini di sini hanya = B2. Ini semua teks formula di sini di sudut kanan sehingga anda melihat apa yang kami gunakan, dan kemudian dari sana ke bawah, ini adalah formula kecil sederhana yang sama dengan nilai sebelumnya, ditambah nilai semasa dengan betul dan salin ke bawah , tetapi anda tahu sekarang, kita menghadapi masalah ini bahawa ia memerlukan dua formula yang berbeza dan anda tahu dalam keadaan yang sempurna anda mempunyai formula yang sama sepanjang jalan, dan alasan kita harus mempunyai formula yang berbeza di baris pertama adalah bahawa apabila anda mencuba dan menambah sama 7 ditambah jumlah perkataan itu adalah kesalahan nilai,tetapi pekerja yang hebat di sini, bukan hanya menggunakan tambah kiri, tetapi menggunakan = (SUM) dari nilai sebelumnya ditambah kuantiti dalam baris ini, dan melihat beberapa cukup jauh untuk mengabaikan teks. Betul sehingga membenarkan formula yang sama. sepanjang jalan ke bawah.
Baiklah, ketika saya mula menggunakan Excel, saya menggunakannya dan kemudian saya menjumpai julat yang berkembang, julat yang berkembang mengatakan kita akan melakukan L $ 2: L2 dan apa yang berlaku ialah ini selalu bermula pada baris 2, tetapi kemudian turun ke baris semasa. Oleh itu, apabila anda melihat bagaimana ini berfungsi apabila disalin, kami selalu memulakan baris 2, tetapi kami turun ke baris semasa dan ini menjadi kaedah kegemaran saya. Saya seperti, oh, ini jauh lebih canggih dan apabila kita memasuki Pilihan Excel, pergi ke Rumus Tab dan pilih R1C1 dalam Gaya Rujukan. Baiklah, R1C1, semua formula ini sama persis di bawah. Saya tidak tahu sama ada anda memahami R1C1, ada baiknya kita mengetahui bahawa kita mempunyai formula R1C1 yang sama.
Mari kembali. Jadi kaedah ini di sini adalah kaedah yang saya suka, hingga Charles Williams, seorang MBP Excel dari England, yang mempunyai kertas yang hebat mengenai kelajuan formula, kelajuan formula Excel, sepenuhnya mematikan kaedah ini. Kaedah ini, katakan anda mempunyai 10,000 baris ini, setiap formula melihat dua rujukan. Oleh itu, anda melihat 20,000 rujukan, tetapi yang satu ini, ini melihat dua, ini melihat tiga, ini melihat empat, ini melihat lima dan yang terakhir melihat 10,000 rujukan, dan ini lebih perlahan dan jadi saya berhenti menggunakan kaedah ini.
Kemudian saya terus membaca Zack dalam buku Kevin Jones mengenai jadual Excel dan saya menemui satu lagi masalah dengan kaedah ini. Jadi salah satu ciri berguna yang ditawarkan oleh jadual, adalah 'format automatik dan pemeliharaan formula baris ditambahkan, dikeluarkan, disusun dan ditapis'. Baiklah itu petikan dari bukunya. Dan untuk menambah baris ke meja, anda hanya pergi ke sel terakhir di atas meja dan tekan tab. Jadi semuanya berfungsi di sini. Kami berada di bawah 70 kanan yang hebat dan kemudian A104 dan saya akan memasukkan 100 di sini. Baiklah, sehingga 70 harus berubah menjadi 170 dan ia berlaku, tetapi 70 ini tidak seharusnya berubah sama sekali. Baiklah 68 + 2 bukan 170. Saya akan melakukannya lagi. A 104 dan meletakkan seratus lagi dalam yang terakhir betul. Kedua-dua ini tidak betul. Baiklah, jadi kami mempunyai situasi yang pelik jika anda 'menggunakan formula ini dan anda menukar ke jadual anda mula menambah baris, jumlah berjalan tidak akan berfungsi. Betapa teruknya itu?
Baiklah, jadi Zack menawarkan dua kerja dan kedua-duanya memerlukan sedikit pengetahuan tentang bagaimana rujukan struktur berfungsi. Kami akan mempunyai ruangan baru di sini dan jika saya mahu melakukan kuantiti, kuantiti yang sama, betul, sehingga = (@ Qty) mengatakan kuantiti dalam baris ini. Bagus, ada rujukan lain di mana kita menggunakan Qty tanpa @. Lihat ini. Jadi = SUM (INDEX ((Qty), 1: (@ Qty)) bermaksud semua kuantiti dan kita akan mengatakan bahawa kita mahu SUM dari kuantiti pertama, jadi (INDEX ((Qty), 1 mengatakan nilai pertama di sini, hingga ke kuantiti baris semasa, dan ini menggunakan versi indeks yang benar-benar istimewa, apabila indeks diikuti oleh titik dua, ia benar-benar berubah menjadi rujukan sel. Baiklah, penyelesaian ini sayangnya melanggar peraturan Charles Williams dari, kita 'saya perlu melihat setiap rujukan, dan apabila anda mendapat 10,000 baris ini akan menjadi sangat perlahan.
Zach mempunyai jalan penyelesaian lain yang tidak melanggar masalah Charles Williams, tetapi menggunakan OFFSET yang ditakuti. OFFSET adalah fungsi yang tidak menentu sehingga setiap kali anda mengira sesuatu, OFFSET akan dikira semula dan segala-galanya dari OFFSET akan dikira semula. Ini hanya kaedah yang baik untuk menyusun formula anda sepenuhnya, dan apa yang dilakukannya, katanya, kami mengambil jumlah keseluruhan dari baris ini, naik satu baris, melebihi lajur sifar dan apa yang dilakukannya adalah: ambil jumlah dari baris sebelumnya dan kemudian kita menambah kuantiti dari baris ini. Baiklah, jadi, sekarang semuanya melihat dua rujukan setiap kali, tetapi sayangnya OFFSET memperkenalkan fungsi tidak menentu.
Nah, di sana anda memilikinya, lebih daripada yang anda ingin tahu mengenai Running Totals. Saya rasa pendapat terakhir saya di sini adalah menggunakan kaedah ini, kerana ia hanya kelihatan dua. Rumus yang sama sepanjang jalan dan rujukan jadual tersusun anda akan berfungsi.
Untuk penerokaan ini dan 39 petua lain yang sangat baik, lihat buku ini XL, 40 petua Excel terbaik sepanjang masa.
Recap for this episode we talked about three ways to do running totals. The first method has a different formula, row 2, than all the other rows. It's equal left in row 2 and then equal left plus up in rows 3 through N, but if you try and just use that same formula, equal left plus up, all the way down, how you're going to get a #Value Error. So =SUM(Up,Left), which is previous total, plus this roadmap, that works great, no Value Errors and then the expanding range which I use to love. They're cool, but until I read Charles Williams white paper on Excel form of speed. Then I started to hate these expanding references. It also has a problem when you use CTRL T and add new rows. Excel can't figure out how to expand that formula, how to add new rows. I love this tip go to the very last cell in the table and press Tab, that will add a new row and then we talked about some structured referencing, where we're using quantity in this row and then all quantities. =SUM(OFFSET((@Total),-1,00,(@Qty)).
Baiklah, saya ingin mengucapkan terima kasih kepada Zach kerana menyumbang tip itu. Saya ingin mengucapkan terima kasih kerana berhenti. Kami akan berjumpa anda di lain kali untuk siaran net lain dari.
Muat turun fail
Muat turun fail contoh di sini: Podcast2004.xlsx
