Episode ini menunjukkan tiga cara untuk melakukan total lari.
Total berjalan adalah, untuk daftar nilai numerik, jumlah nilai dari baris pertama hingga baris dari total berjalan. Penggunaan umum dari total berjalan ada di daftar buku cek atau lembar akuntansi. Ada banyak cara untuk membuat total-dua berjalan yang dijelaskan di bawah ini.
Teknik paling sederhana adalah, pada setiap baris, menambahkan total berjalan dari baris di atas ke nilai di baris. Jadi rumus pertama di baris 2 adalah:
=SUM(D1,C2)
Alasan kami menggunakan fungsi SUM karena, di baris pertama, kami melihat header di baris di atas. Jika kita menggunakan rumus yang lebih sederhana dan lebih intuitif =D1+C2maka kesalahan akan dihasilkan karena nilai header adalah teks versus numerik. Ajaibnya adalah bahwa fungsi SUM mengabaikan nilai teks, yang ditambahkan sebagai nilai nol. Saat rumus disalin ke semua baris yang diinginkan total berjalan, referensi sel disesuaikan:
Teknik lainnya juga menggunakan fungsi SUM tetapi setiap rumus menjumlahkan semua nilai dari baris pertama hingga baris yang menampilkan total berjalan. Dalam hal ini kami menggunakan tanda dolar ($) untuk membuat sel pertama dalam referensi sebagai referensi absolut yang artinya tidak disesuaikan saat disalin:
Kedua teknik tersebut tidak terpengaruh oleh pengurutan dan penghapusan baris, tetapi saat menyisipkan baris, rumus harus disalin ke baris baru.
Excel 2007 memperkenalkan Tabel yang merupakan implementasi ulang Daftar di Excel 2003. Tabel memperkenalkan sejumlah fitur yang sangat berguna untuk tabel data seperti pemformatan, pengurutan, dan pemfilteran. Dengan pengenalan Tabel, kami juga diberikan cara baru untuk mereferensikan bagian-bagian Tabel. Gaya referensi baru ini disebut referensi terstruktur.
Untuk mengubah contoh di atas menjadi Tabel, kami memilih data yang ingin kami sertakan dalam Tabel dan tekan Ctrl + T.Setelah menampilkan prompt yang meminta kami untuk mengonfirmasi rentang Tabel dan apakah ada tajuk atau tidak, Excel mengubah datanya ke dalam Tabel yang diformat:
Perhatikan bahwa rumus yang kita masukkan sebelumnya tetap sama.
Salah satu fitur berguna yang ditawarkan Tabel adalah pemformatan otomatis dan pemeliharaan formula saat baris ditambahkan, dihapus, diurutkan, dan difilter. Ini adalah pemeliharaan formula khususnya yang akan kami fokuskan dan yang dapat menjadi masalah. Agar Tabel tetap berfungsi saat dimanipulasi, Excel menggunakan kolom terhitung yang merupakan kolom dengan rumus seperti kolom D pada contoh di atas. Saat baris baru dimasukkan ditambahkan ke bawah, Excel secara otomatis mengisi baris baru dengan rumus "default" untuk kolom itu. Masalah dengan contoh di atas adalah Excel menjadi bingung dengan rumus standar dan tidak selalu menanganinya dengan benar. Ini menjadi jelas ketika baris baru ditambahkan ke bagian bawah Tabel (dengan memilih sel kanan bawah dalam Tabel dan menekan TAB):
Kekurangan ini diatasi dengan menggunakan referensi terstruktur yang lebih baru. Referensi terstruktur menghilangkan kebutuhan untuk mereferensikan sel tertentu menggunakan gaya referensi A1 atau R1C1 dan sebaliknya menggunakan nama kolom dan kata kunci lain untuk mengidentifikasi dan mereferensikan bagian-bagian dari Tabel. Misalnya, untuk membuat rumus total berjalan yang sama seperti yang digunakan di atas tetapi menggunakan referensi terstruktur, kami memiliki:
=SUM(INDEX((Sales),1):(@Sales))
Dalam contoh ini kita memiliki referensi ke nama kolom, "Penjualan", bersama dengan tanda di (@) untuk mereferensikan baris di kolom tempat rumus berada yang juga dikenal sebagai baris saat ini.
Untuk menerapkan contoh pertama di atas di mana kami menambahkan nilai total yang berjalan di baris sebelumnya ke jumlah penjualan di baris saat ini, Anda dapat menggunakan fungsi OFFSET:
=SUM(OFFSET((@(Running Total)),-1,0),(@Sales))
Jika jumlah yang digunakan untuk menghitung total berjalan ada dalam dua kolom, 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 menemukan sel Kredit dan Debit baris pertama, dan menjumlahkan seluruh kolom hingga dan termasuk nilai baris saat ini. Total berjalan adalah jumlah semua kredit hingga dan termasuk baris saat ini dikurangi jumlah semua debet hingga dan termasuk baris saat ini.
Untuk informasi lebih lanjut tentang referensi terstruktur pada khususnya dan Tabel pada umumnya, kami merekomendasikan buku Tabel Excel: Panduan Lengkap untuk Membuat, Menggunakan dan Mengotomatiskan Daftar dan Tabel oleh Zack Barresse dan Kevin Jones.
Ketika saya meminta pembaca untuk memilih tip favorit mereka, tabel menjadi populer. Terima kasih kepada Peter Albert, Snorre Eikeland, Nancy Federice, Colin Michael, James E. Moede, Keyur Patel, dan Paul Peton karena telah menyarankan fitur ini. Peter Albert menulis Tip bonus Referensi yang Dapat Dibaca. Zack Barresse menulis tip bonus Running Totals. Empat pembaca menyarankan penggunaan OFFSET untuk membuat rentang yang diperluas untuk bagan dinamis: Charley Baak, Don Knowles, Francis Logan, dan Cecelia Rieb. Tabel sekarang melakukan hal yang sama dalam banyak kasus.
Menonton video
- Episode ini menunjukkan tiga cara untuk melakukan total lari
- Metode pertama memiliki rumus yang berbeda di Baris 2 dari semua baris lainnya
- Metode pertama adalah = Kiri di baris 2 dan = Kiri + Atas di baris 3 sampai N
- Jika Anda mencoba menggunakan rumus yang sama, Anda mendapatkan kesalahan #Value dengan = Total + Number
- Metode 2 menggunakan
=SUM(Up,Left)atau=SUM(Previous Total,This Row Amount) - SUM mengabaikan Teks sehingga Anda tidak mendapatkan kesalahan NILAI
- Metode 3 menggunakan rentang yang meluas:
=SUM(B$2:B2) - Memperluas rentang itu keren tapi lambat
- Baca whitepaper Charles Williams tentang Kecepatan Formula Excel
- Metode ketiga adalah masalah saat Anda menggunakan Ctrl + T dan menambahkan baris baru
- Excel tidak tahu cara menulis rumus
- Solusi tersebut memerlukan pengetahuan tentang referensi terstruktur di Tabel
- Solusi 1 adalah lambat
=SUM(INDEX((Qty),1):(@Qty)) - Solusi 2 adalah volatile
=SUM(OFFSET((@Total),-1,0),(@Qty)) - (@Qty) mengacu pada Qty di baris ini
- (Qty) mengacu pada semua nilai Qty
Transkrip Video
Pelajari Excel untuk Podcast, Episode 2004 - Running Totals
Saya akan membuat podcasting seluruh buku ini. Klik saya di pojok kanan atas untuk berlangganan.
Halo, selamat datang kembali di jaringan seluler mistik. Saya Bill Jelen. Sekarang topik dalam buku ini, saya dikontribusikan oleh teman saya Zach Parise. Berbicara tentang tabel Excel, Zach adalah pakar dunia dalam tabel Excel. Dia menulis buku tentang tabel Excel, tapi pertama-tama mari kita bicara tentang menjalankan total bukan dalam tabel.
Jadi ketika saya berpikir tentang menjalankan total, ada tiga cara berbeda untuk melakukan total berjalan, dan cara yang selalu saya mulai adalah di baris pertama yang Anda katakan, bawa nilainya. Jadi sama dengan apapun yang ada di sebelah kiri saya. Baiklah jadi format ini di sini hanya = B2. Ini semua adalah teks rumus di sini di sudut kanan sehingga Anda melihat apa yang kami gunakan, dan dari sana ke bawah, itu adalah rumus kecil sederhana yang sama dengan nilai sebelumnya, ditambah nilai saat ini ke kanan dan salin ke bawah , tetapi Anda tahu sekarang, kami memiliki masalah ini yang memerlukan dua rumus berbeda dan Anda tahu dalam situasi yang sempurna Anda memiliki rumus yang sama persis di bagian bawah, dan alasan kami harus memiliki rumus berbeda di baris pertama adalah bahwa ketika Anda mencoba dan menambahkan sama dengan 7 ditambah kata total itu adalah kesalahan nilai,tetapi pekerja keren di sini, adalah tidak hanya menggunakan kiri plus atas, tetapi menggunakan = (SUM) dari nilai sebelumnya ditambah kuantitas di baris ini, dan melihat beberapa cukup jauh untuk mengabaikan teks. Benar sehingga memungkinkan rumus yang sama. sampai ke bawah.
Baiklah, saat itulah saya mulai menggunakan Excel, saya menggunakannya dan kemudian saya menemukan rentang yang meluas, rentang yang diperluas mengatakan kami akan melakukan L $ 2: L2 dan yang terjadi adalah ini selalu dimulai dari baris 2, tapi kemudian turun ke baris saat ini. Jadi ketika Anda melihat bagaimana ini bekerja ketika disalin, kami selalu memulai baris 2, tetapi kami turun ke baris saat ini dan ini menjadi metode favorit saya. Saya merasa, oh, ini jauh lebih canggih dan saat kita masuk ke Opsi Excel, buka Tab Rumus dan pilih R1C1 dalam Gaya Referensi. Baiklah lihat, R1C1, semua rumus ini sama persis di bagian bawah. Saya tidak tahu apakah Anda memahami R1C1, senang mengetahui bahwa kami memiliki rumus R1C1 yang identik sepenuhnya.
Ayo kembali. Jadi metode di sini adalah metode yang saya suka, sampai Charles Williams, seorang MBP Excel dari Inggris, yang memiliki makalah luar biasa tentang kecepatan rumus, kecepatan rumus Excel, benar-benar membantah metode ini. Metode ini, katakanlah Anda memiliki 10.000 baris ini, setiap rumus melihat dua referensi. Jadi Anda melihat 20.000 referensi, tapi yang ini, ini, ini dua, ini melihat tiga, ini melihat empat, ini melihat lima dan yang terakhir melihat 10.000 referensi, dan itu sangat lambat. jadi saya berhenti menggunakan metode ini.
Lalu saya melanjutkan membaca Zack dalam buku Kevin Jones tentang tabel Excel dan saya menemukan masalah lain dengan metode ini. Jadi salah satu fitur berguna yang ditawarkan tabel adalah 'pemformatan otomatis dan baris pemeliharaan rumus ditambahkan, dihapus, diurutkan, dan difilter'. Baiklah itu kutipan dari bukunya. Dan untuk menambahkan baris ke tabel Anda hanya pergi ke sel terakhir pada tabel dan tekan tab. Jadi semuanya bekerja di sini. Kami turun ke 70, itu luar biasa dan kemudian A104 dan saya akan memasukkan 100 di sini. Baiklah, jadi 70 harus berubah menjadi 170 dan itu benar, tapi 70 ini seharusnya tidak berubah sama sekali. Baiklah 68 + 2 bukan 170. Saya akan melakukannya lagi. A 104 dan menempatkan seratus lagi di yang terakhir benar. Keduanya tidak benar. Baiklah, jadi kami punya situasi aneh yang jika Anda 'kembali menggunakan rumus ini dan Anda mengonversi ke tabel Anda mulai menambahkan baris, total berjalan tidak akan berfungsi. Seberapa buruk itu?
Baiklah, jadi Zack menawarkan dua solusi dan keduanya membutuhkan sedikit pengetahuan, tentang cara kerja referensi struktur. Kami hanya akan memiliki kolom baru di sini dan jika saya ingin melakukan kuantitas, kuantitas yang sama, benar, sehingga = (@ Qty) mengatakan kuantitas di baris ini. Oh keren, ada jenis referensi lain di mana kita menggunakan Qty tanpa @. Lihat ini. Jadi = SUM (INDEX ((Qty), 1: (@ Qty)) berarti semua kuantitas dan kita akan mengatakan bahwa kita ingin menjumlahkan dari kuantitas pertama, jadi (INDEX ((Qty), 1 mengatakan nilai pertama di sini, turun ke kuantitas baris saat ini, dan ini menggunakan versi indeks yang sangat khusus, jika indeks diikuti oleh titik dua, sebenarnya akan berubah menjadi referensi sel. Baiklah, solusi ini sayangnya melanggar aturan Charles Williams dari, kami 'kembali harus melihat setiap referensi, dan ketika Anda mendapatkan 10.000 baris ini akan berjalan sangat, sangat lambat.
Zach memiliki solusi lain yang tidak melanggar masalah Charles Williams, tetapi menggunakan OFFSET yang ditakuti. OFFSET adalah fungsi yang mudah menguap sehingga setiap kali Anda menghitung sesuatu, OFFSET akan menghitung ulang dan semua yang ada di bawah dari OFFSET akan dihitung ulang. Ini hanya cara yang bagus untuk benar-benar, mengacaukan rumus Anda sepenuhnya, dan apa yang dilakukannya, katanya, kami mengambil total dari baris ini, naik satu baris, lebih dari nol kolom dan jadi yang dilakukannya adalah mengatakan: ambil total dari baris sebelumnya dan kemudian kami menambahkan jumlah dari baris ini. Baiklah, jadi, sekarang semuanya melihat dua referensi setiap kali, tapi sayangnya OFFSET memperkenalkan fungsi volatile.
Nah, begitulah, lebih dari yang pernah ingin Anda ketahui tentang Running Total. Saya kira pendapat terakhir saya di sini adalah menggunakan metode ini, karena hanya terlihat dua. Rumus yang sama sepenuhnya dan referensi tabel terstruktur Anda akan berfungsi.
Untuk eksplorasi ini dan 39 tip bagus lainnya, lihat buku XL ini, 40 tip Excel terhebat sepanjang masa.
Rekap untuk episode ini kami berbicara tentang tiga cara untuk melakukan total lari. Metode pertama memiliki rumus yang berbeda, baris 2, dari semua baris lainnya. Itu sama dengan kiri di baris 2 dan kemudian kiri sama dengan atas di baris 3 hingga N, tetapi jika Anda mencoba dan hanya menggunakan rumus yang sama, kiri sama dengan atas, ke bawah, bagaimana Anda akan mendapatkan #Nilai Kesalahan . Jadi = SUM (Atas, Kiri), yang merupakan total sebelumnya, ditambah peta jalan ini, yang berfungsi dengan baik, tidak ada Kesalahan Nilai dan kemudian rentang yang semakin luas yang saya gunakan untuk mencintai. Mereka keren, tapi sampai saya membaca kertas putih Charles Williams pada bentuk kecepatan Excel. Kemudian saya mulai membenci referensi yang berkembang ini. Ini juga memiliki masalah saat Anda menggunakan CTRL T dan menambahkan baris baru. Excel tidak tahu cara memperluas rumus itu, cara menambahkan baris baru. Saya suka tip ini pergi ke sel terakhir dalam tabel dan tekan Tab,yang akan menambahkan baris baru dan kemudian kita berbicara tentang beberapa referensi terstruktur, di mana kita menggunakan kuantitas di baris ini dan kemudian semua kuantitas. = SUM (OFFSET ((@ Total), - 1,00, (@ Qty)).
Oke, saya ingin berterima kasih kepada Zach karena telah menyumbangkan tip itu. Saya ingin mengucapkan terima kasih telah mampir. Sampai jumpa di lain waktu untuk netcast lain dari.
Unduh berkas
Unduh file contoh di sini: Podcast2004.xlsx
