Bu bölüm, değişen toplamları yapmanın üç yolunu gösterir.
Değişen toplam, sayısal değerlerin bir listesi için, ilk satırdan değişen toplamın satırına kadar olan değerlerin toplamıdır. Değişen toplamın yaygın kullanımları bir çek defteri sicilinde veya bir muhasebe sayfasında bulunur. Toplam iki tanesi aşağıda açıklanan bir dizi oluşturmanın birçok yolu vardır.
En basit teknik, her satırda, yukarıdaki satırdan değişen toplamı satırdaki değere eklemektir. Yani 2. sıradaki ilk formül:
=SUM(D1,C2)
SUM işlevini kullanmamızın nedeni, ilk satırda yukarıdaki satırdaki başlığa bakmamızdır. Daha basit, daha sezgisel formülünü kullanırsak, =D1+C2başlık değeri metne karşı sayısal olduğundan bir hata üretilecektir. İşin büyüsü, TOPLA işlevinin sıfır değerleri olarak eklenen metin değerlerini yok saymasıdır. Formül, değişen toplamın istendiği tüm satırlara kopyalandığında, hücre referansları buna göre ayarlanır:
Diğer teknik de TOPLA işlevini kullanır, ancak her formül, ilk satırdan değişen toplamı görüntüleyen satıra kadar tüm değerleri toplar. Bu durumda, referanstaki ilk hücreyi mutlak bir referans yapmak için bir dolar işareti ($) kullanırız; bu, kopyalandığında ayarlanmadığı anlamına gelir:
Her iki teknik de satırların sıralanması ve silinmesinden etkilenmez, ancak satır eklerken formülün yeni satırlara kopyalanması gerekir.
Excel 2007, Listenin Excel 2003'te yeniden uygulanması olan Tabloyu tanıttı. Tablolar, biçimlendirme, sıralama ve filtreleme gibi veri tabloları için çok yararlı bir dizi özellik tanıttı. Tabloların tanıtılmasıyla, bir Tablonun bölümlerine atıfta bulunmanın yeni bir yolu da sağlandı. Bu yeni referans stiline yapılandırılmış referanslama denir.
Yukarıdaki örneği bir Tabloya dönüştürmek için, Tabloya dahil etmek istediğimiz verileri seçip Ctrl + T tuşlarına basarız. Tablonun aralığını ve mevcut başlıklar olup olmadığını onaylamamızı isteyen bir uyarı görüntüledikten sonra, Excel verileri dönüştürür. biçimlendirilmiş bir Tabloya:
Daha önce girdiğimiz formüllerin aynı kaldığını unutmayın.
Tabloların sunduğu kullanışlı özelliklerden biri, satırlar eklenirken, kaldırılırken, sıralanır ve filtrelenirken otomatik biçimlendirme ve formül bakımıdır. Özellikle odaklanacağımız ve sorunlu olabilecek formül bakımıdır. Tablolar işlenirken çalışır durumda tutmak için Excel, yukarıdaki örnekte D sütunu gibi formüllere sahip sütunlar olan hesaplanmış sütunları kullanır. En alta yeni satırlar eklendiğinde, Excel yeni satırları otomatik olarak o sütun için "varsayılan" formülle doldurur. Yukarıdaki örnekteki sorun, Excel'in standart formüllerle karıştırılması ve bunları her zaman doğru şekilde işlememesidir. Bu, Tablonun altına yeni satırlar eklendiğinde (Tabloda sağ alt hücre seçilip TAB tuşuna basarak) açıkça görülür:
Bu eksiklik, daha yeni yapılandırılmış referans kullanılarak giderilir. Yapılandırılmış referans, A1 veya R1C1 referans stilini kullanarak belirli hücrelere başvurma ihtiyacını ortadan kaldırır ve bunun yerine bir Tablonun bölümlerini tanımlamak ve referans vermek için sütun adlarını ve diğer anahtar kelimeleri kullanır. Örneğin, yukarıda kullanılanla aynı değişen toplam formülü oluşturmak için, ancak yapılandırılmış referanslama kullanarak aşağıdakilere sahibiz:
=SUM(INDEX((Sales),1):(@Sales))
Bu örnekte, mevcut satır olarak da bilinen formülün bulunduğu sütundaki satıra başvurmak için at işareti (@) ile birlikte "Satış" sütun adına bir referansımız var.
Önceki satırdaki cari toplam değeri geçerli satırdaki satış tutarına eklediğimiz yukarıdaki ilk örneği uygulamak için OFFSET işlevini kullanabilirsiniz:
=SUM(OFFSET((@(Running Total)),-1,0),(@Sales))
Değişen toplamı hesaplamak için kullanılan tutarlar iki sütunda ise, örneğin "Borçlar" ve biri "Krediler" için, formül şu şekildedir:
=SUM(INDEX( (Credit),1):(@Credit))- SUM(INDEX( (Debit),1):(@Debit))
Burada, ilk satırın Alacak ve Borç hücrelerini bulmak için INDEX işlevini kullanıyoruz ve mevcut satırın değerleri dahil olmak üzere tüm sütunu toplamıyoruz. Cari toplam, geçerli satıra kadar olan ve mevcut satır dahil tüm kredilerin toplamından mevcut satıra kadar ve dahil tüm borçların toplamından çıkarılır.
Özellikle yapılandırılmış referanslar ve genel olarak Tablolar hakkında daha fazla bilgi için, Zack Barresse ve Kevin Jones tarafından yazılan Excel Tabloları: Listeleri ve Tabloları Oluşturma, Kullanma ve Otomatikleştirme için Eksiksiz Kılavuz kitabını öneriyoruz.
Okuyuculardan en sevdikleri ipuçlarına oy vermelerini istediğimde tablolar popülerdi. Bu özelliği önerdikleri için Peter Albert, Snorre Eikeland, Nancy Federice, Colin Michael, James E. Moede, Keyur Patel ve Paul Peton'a teşekkürler. Peter Albert, Okunabilir Referanslar bonus İpucu'nu yazdı. Zack Barresse Koşu Toplamları bonus ipucunu yazdı. Dört okuyucu dinamik grafikler için genişleyen aralıklar oluşturmak için OFFSET'i kullanmayı önerdi: Charley Baak, Don Knowles, Francis Logan ve Cecelia Rieb. Tablolar artık çoğu durumda aynı şeyi yapıyor.
Video izle
- Bu bölüm, değişen toplamları yapmanın üç yolunu gösterir
- İlk yöntemin 2. satırda diğer tüm satırlardan farklı bir formülü var
- İlk yöntem = Sol 2. satırda ve = Sol + Yukarı 3. satırlarda N
- Aynı formülü kullanmaya çalışırsanız, = Toplam + Sayı ile # Değer hatası alırsınız
- Yöntem 2 kullanır
=SUM(Up,Left)veya=SUM(Previous Total,This Row Amount) - SUM, Metni yoksayar, böylece bir VALUE hatası almazsınız
- Yöntem 3, genişleyen bir aralık kullanır:
=SUM(B$2:B2) - Genişleyen aralıklar harika ama yavaşlar
- Excel Formula Speed hakkındaki Charles Williams teknik incelemesini okuyun
- Üçüncü yöntem, Ctrl + T'yi kullandığınızda ve yeni satırlar eklediğinizde bir sorundur.
- Excel formülün nasıl yazılacağını çözemiyor
- Geçici çözümler, Tablolarda bazı yapılandırılmış referans bilgileri gerektirir
- Geçici çözüm 1, yavaş
=SUM(INDEX((Qty),1):(@Qty)) - Geçici çözüm 2 uçucudur
=SUM(OFFSET((@Total),-1,0),(@Qty)) - (@Qty) bu satırdaki Qty anlamına gelir
- (Qty) tüm Qty değerlerini ifade eder
Video Metni
Podcast için Excel'i Öğrenin, Bölüm 2004 - Toplamları Çalıştırma
Bu kitabın tamamını podcast yayınlayacağım. Abone olmak için sağ üst köşedeki I tıklayın.
Hey, mistik hücre ağına tekrar hoş geldiniz. Ben Bill Jelen. Şimdi kitapta bu konuya arkadaşım Zach Parise tarafından katkıda bulundum. Excel tabloları hakkında konuşalım, Zach, Excel tabloları konusunda dünyanın uzmanıdır. Excel tabloları hakkında bir kitap yazdı, ancak önce tablolarda değil toplamları çalıştırmaktan bahsedelim.
Dolayısıyla, toplamları çalıştırmayı düşündüğümde, değişen toplamları yapmanın üç farklı yolu var ve her zaman başladığım yol, ilk satırda, sadece değeri getir diyorsunuz. Benim solumda ne varsa o kadar eşit. Pekala, buradaki biçim sadece = B2. Bunların hepsi sağ köşede bulunan formül metinleridir, bu yüzden ne kullandığımızı görüyorsunuz ve buradan aşağıya, önceki değere eşit basit bir küçük formül artı mevcut değer sağa ve aşağıya kopyalayın , ama şimdi biliyorsunuz, iki farklı formül gerektirdiğine dair bir problemimiz var ve biliyorsunuz ki mükemmel bir durumda, tamamen aynı formüle sahipsiniz ve ilk satırda farklı bir formüle sahip olmamızın nedeni şu: eşittir 7 artı toplam kelimesini eklemeye çalıştığınızda, bunun bir değer hatası olduğunu,ama buradaki havalı çalışan, sadece sol artı yukarı kullanmak değil, aynı zamanda önceki değerin = (TOPLA) artı bu satırdaki miktarı kullanmak ve bazılarının metinleri görmezden gelmek için yeterince uzak olduğunu görmek. Doğru, bu aynı formüle izin verir. tamamen aşağı.
Pekala, Excel kullanmaya başladığımda bunu kullanıyordum ve sonra genişleyen aralığı keşfettim, genişleyen aralık L $ 2: L2 yapacağımızı söylüyor ve bu her zaman 2. satırdan başlıyor, ama sonra mevcut satıra iniyor. Yani kopyalandığında nasıl çalıştığına baktığınızda, her zaman 2. satıra başladık, ancak şu anki satıra gidiyoruz ve bu benim favori yöntemim oldu. Ben, oh, bu çok daha sofistike gibiydim ve Excel Seçenekleri'ne gittiğimizde, Formüller Sekmesine gidin ve Referans Stilinde R1C1'i seçin. Pekala bakın, R1C1, tüm bu formüller tamamen aynı. R1C1'i anlıyor musunuz bilmiyorum, tamamen aynı R1C1 formüllerine sahip olduğumuzu bilmek güzel.
Hadi geri dönelim. Yani buradaki bu yöntem benim sevdiğim yöntemdi, ta ki İngiltere'den bir Excel MBP'si olan Charles Williams, formül hızı, Excel formül hızı üzerine harika bir makalesi olan ve bu yöntemi tamamen çürütene kadar. Bu yöntemde 10.000 satırınız olduğunu varsayalım, her formül iki referansa bakıyor. Yani 20.000 referansa bakıyorsunuz, ama bu, bu ikiye bakıyor, bu üçe bakıyor, bu dörde bakıyor, bu beşe bakıyor ve sonuncusu 10.000 referansa bakıyor ve bu çok daha yavaş ve bu yüzden bu yöntemi kullanmayı bıraktım.
Sonra Kevin Jones'un Excel tabloları hakkındaki kitabındaki Zack'i okumaya devam ediyorum ve bu yöntemle ilgili başka bir sorun daha keşfediyorum. Dolayısıyla, tabloların sunduğu kullanışlı özelliklerden biri, 'otomatik biçimlendirme ve formül bakımı satırları eklenir, kaldırılır, sıralanır ve filtrelenir'. Pekala, bu onun kitabından bir alıntı. Ve bir tabloya satır eklemek için tablodaki en son hücreye gidip sekme tuşuna basmanız yeterlidir. Yani burada her şey çalışıyor. 70'e düştük, bu harika ve sonra A104 ve buraya 100 koyacağım. Pekala, 70'in 170'e değişmesi gerekiyor ve öyle, ama bu 70'in hiç değişmemesi gerekiyordu. Pekala 68 + 2, 170 değil. Tekrar yapacağım. A 104 ve sonuncusuna başka bir yüz koy doğru. Bu ikisi doğru değil. Pekala, tuhaf bir durumumuz var.Bu formülü kullanarak yeniden tabloya dönüştürürseniz satır eklemeye başlarsınız, değişen toplam çalışmaz. Bu ne kadar kötü?
Pekala, Zack iki çözüm öneriyor ve ikisi de yapı referanslarının nasıl çalıştığı konusunda biraz bilgi gerektiriyor. Burada yeni bir sütunumuz olacak ve miktar, eşit miktar, doğru yapmak istersem, bu = (@ Qty) bu satırda miktar yazıyor. Harika, Qty'yi @ olmadan kullandığımız başka bir tür referans var. Şuna bak. Yani = TOPLA (INDEX ((Qty), 1: (@ Qty)) tüm miktarlar anlamına gelir ve ilk miktardan TOPLA yapmak istediğimizi söyleyeceğiz, bu nedenle (INDEX ((Qty), 1 diyor ki Buradaki ilk değer, geçerli satır miktarına kadar ve bu gerçekten özel bir dizin sürümü kullanıyor, dizinin ardından iki nokta üst üste geldiğinde, aslında bir hücre başvurusuna dönüşüyor. Pekala, bu geçici çözüm maalesef Charles Williams kuralını ihlal ediyor biz 'Her bir referansa bakmanız gerekecek ve bu yüzden 10.000 satır aldığınızda bunun gerçekten çok yavaş ilerleyeceği.
Zach'in Charles Williams problemini ihlal etmeyen başka bir çözümü var, ancak bu korkunç OFFSET'i kullanıyor. OFFSET değişken bir işlevdir, bu nedenle bir şeyi her hesapladığınızda, OFFSET yeniden hesaplayacak ve OFFSET'ten sonraki her şey yeniden hesaplanacaktır. Formüllerinizi tamamen, tamamen alt üst etmenin harika bir yolu ve bunun yaptığı şey, diyor ki, bu satırdan toplamı alıyoruz, bir satır yukarı, sıfır sütunun üzerine gidiyoruz ve bu da şunu söylüyor: önceki satırdaki toplamı al ve sonra bu satırdaki miktarı buna ekliyoruz. Pekala, şimdi hepsi her seferinde iki referansa bakıyor, ancak ne yazık ki OFFSET değişken fonksiyonlar sunuyor.
İşte Koşu Toplamları hakkında bilmek istediğinden çok daha fazlasına sahipsin. Sanırım buradaki son fikrim bu yöntemi kullanmak, çünkü sadece ikiye benziyor. Tamamen aynı formül ve yapılandırılmış tablo referanslarınız işe yarayacaktır.
Bu keşif ve diğer gerçekten iyi ipuçları için, tüm zamanların en büyük 40 Excel ipucu olan XL kitabına göz atın.
Bu bölümün özetini, hareketli toplamları yapmanın üç yolu hakkında konuştuk. İlk yöntemin, diğer tüm satırlardan farklı bir formülü vardır: 2. satır. Satır 2'de sola eşittir ve sonra 3'ten N'ye kadar olan satırlarda sol artı yukarı eşittir, ancak aynı formülü kullanmaya çalışırsanız, eşit sol artı yukarı, tamamen aşağı, nasıl # Değer Hatası elde edersiniz? . Yani = TOPLA (Yukarı, Sol), önceki toplam, artı harika çalışan bu yol haritası, Değer Hataları yok ve sonra sevdiğim genişleyen aralık. Havalılar, ancak Charles Williams'ın Excel hız formu hakkındaki teknik incelemesini okuyana kadar. Sonra bu genişleyen referanslardan nefret etmeye başladım. Ayrıca CTRL T kullandığınızda ve yeni satırlar eklediğinizde de bir sorun vardır. Excel, bu formülün nasıl genişletileceğini, yeni satırların nasıl ekleneceğini çözemez. Bu ipucunu seviyorum, tablodaki son hücreye gidin ve Tab tuşuna basın,bu yeni bir satır ekleyecek ve sonra bu satırda miktarı ve ardından tüm miktarları kullandığımız bazı yapılandırılmış referanslardan bahsettik. = TOPLA (OFSET ((@ Toplam), - 1,00, (@ Miktar)).
Tamam, bu ipucuna katkıda bulunduğunuz için Zach'e teşekkür etmek istiyorum. Uğradığın için teşekkür ederim. Bir dahaki sefere başka bir internet yayını için görüşürüz.
Dosyayı indir
Örnek dosyayı buradan indirin: Podcast2004.xlsx
