Excel çalışma sayfanız değişen hücre aralığına dayalı hesaplamalar içeriyorsa, hesaplamaları güncel tutma görevini basitleştirmek için SUM OFFSET formülünde TOPLA ve KAYDIR işlevlerini birlikte kullanın.
Bu makaledeki talimatlar Microsoft 365 için Excel, Excel 2019, Excel 2016, Excel 2013 ve Excel 2010 için geçerlidir.
SUM ve OFFSET İşlevleriyle Dinamik Aralık Oluşturun
Hesaplamaları sürekli değişen bir süre için kullanıyorsanız (örneğin, aylık satışları belirlemek gibi) her günün satış rakamları eklendikçe değişen dinamik bir aralık ayarlamak için Excel'deki KAYDIR işlevini kullanın.
Kendi başına, SUM işlevi genellikle toplanan aralığa yeni veri hücrelerinin eklenmesini sağlayabilir. Veriler, işlevin bulunduğu hücreye eklendiğinde bir istisna oluşur.
Aşağıdaki örnekte, her gün için yeni satış rakamları listenin en altına eklenir ve her yeni veri eklendikçe toplamı sürekli olarak bir hücre aşağı kaydırmaya zorlar.
Bu öğreticiyi takip etmek için boş bir Excel çalışma sayfası açın ve örnek verileri girin. Çalışma sayfanızın örnekteki gibi biçimlendirilmesi gerekmez, ancak verileri aynı hücrelere girdiğinizden emin olun.
Verileri toplamak için yalnızca SUM işlevi kullanılıyorsa, işlev argümanı olarak kullanılan hücre aralığının her yeni veri eklendiğinde değiştirilmesi gerekir.
TOPLA ve OFFSET işlevlerini birlikte kullanarak, toplanan aralık dinamik hale gelir ve yeni veri hücrelerini barındıracak şekilde değişir. Her yeni hücre eklendikçe aralık ayarlanmaya devam ettiğinden, yeni veri hücrelerinin eklenmesi sorunlara neden olmaz.
Sözdizimi ve Argümanlar
Bu formülde, SUM işlevi, argüman olarak sağlanan veri aralığını toplamak için kullanılır. Bu aralığın başlangıç noktası statiktir ve formül tarafından toplanacak ilk sayının hücre başvurusu olarak tanımlanır.
OFFSET işlevi, SUM işlevinin içinde yuvalanmıştır ve formülün topladığı veri aralığı için dinamik bir bitiş noktası oluşturur. Bu, aralığın bitiş noktası formül konumunun bir hücre üzerinde olacak şekilde ayarlanarak gerçekleştirilir.
Formül sözdizimi:
=SUM(Aralık Başlangıcı:OFFSET(Referans, Satırlar, Sütunlar))
Argümanlar:
- Range Start: SUM işlevi tarafından toplanacak hücre aralığının başlangıç noktası. Bu örnekte, başlangıç noktası B2 hücresidir.
- Reference: Aralık bitiş noktasını hesaplamak için kullanılan gerekli hücre başvurusu. Örnekte, Aralık, formülün bir hücre üzerinde bittiği için Başvuru bağımsız değişkeni formülün hücre başvurusudur.
- Rows: Ofseti hesaplamada kullanılan Referans bağımsız değişkeninin üstündeki veya altındaki satır sayısı gereklidir. Bu değer pozitif, negatif veya sıfıra ayarlanmış olabilir. Ofset konumu, Referans bağımsız değişkeninin üzerindeyse, değer negatiftir. Ofset aşağıdaysa, Satırlar bağımsız değişkeni pozitiftir. Uzaklık aynı satırda bulunuyorsa, bağımsız değişken sıfırdır. Bu örnekte, uzaklık, Referans bağımsız değişkeninin bir satır yukarısında başlar, bu nedenle bağımsız değişkenin değeri negatif birdir (-1).
- Sütun: Uzaklığı hesaplamak için kullanılan Referans bağımsız değişkeninin solundaki veya sağındaki sütunların sayısı. Bu değer pozitif, negatif veya sıfıra ayarlanmış olabilir. Ofset konumu, Referans bağımsız değişkeninin solundaysa, bu değer negatiftir. Ofset sağa ise, Cols argümanı pozitiftir. Bu örnekte, toplanan veriler formülle aynı sütundadır, dolayısıyla bu bağımsız değişkenin değeri sıfırdır.
Toplam Satış Verileri için SUM OFSET Formülünü kullanın
Bu örnek, çalışma sayfasının B sütununda listelenen günlük satış rakamlarının toplamını döndürmek için SUM OFFSET formülü kullanır. Başlangıçta formül B6 hücresine girildi ve dört gün boyunca satış verilerinin toplamı alındı.
Bir sonraki adım, beşinci günün satış toplamına yer açmak için SUM OFFSET formülünü bir satır aşağı taşımaktır. Bu, formülü 7. satıra taşıyan yeni bir 6. satır eklenerek gerçekleştirilir.
Taşımanın bir sonucu olarak, Excel Başvuru bağımsız değişkenini otomatik olarak B7 hücresine günceller ve B6 hücresini formülün topladığı aralığa ekler.
- Hücreyi seçin B6, formül sonuçlarının başlangıçta görüntüleneceği konumdur.
-
Şeridin Formüller sekmesini seçin.
Image -
Seç Matematik ve Trig.
Image -
Seç SUM.
Image - Function Arguments iletişim kutusunda, imleci Number1 metin kutusuna yerleştirin.
-
Çalışma sayfasında, iletişim kutusuna bu hücre referansını girmek için B2 hücresini seçin. Bu konum, formülün statik uç noktasıdır.
Image - Function Arguments iletişim kutusunda, imleci Number2 metin kutusuna yerleştirin.
-
Enter OFFSET(B6, -1, 0). Bu KAYDIR işlevi, formülün dinamik bitiş noktasını oluşturur.
Image -
İşlevi tamamlamak ve iletişim kutusunu kapatmak için OK öğesini seçin. Toplam, B6 hücresinde görünür.
Image
Ertesi Günün Satış Verilerini Ekle
Ertesi günün satış verilerini eklemek için:
- 6. satır için satır başlığına sağ tıklayın
-
Çalışma sayfasına yeni bir satır eklemek için Ekle'yi seçin. SUM OFFSET formülü bir satır aşağı B7 hücresine gider ve 6. satır artık boştur.
Image - Hücreyi seçin A6 ve beşinci günün satış toplamının girildiğini belirtmek için 5 numarasını girin.
-
Hücreyi seçin B6, $1458.25 girin, ardından Enter tuşlarına basın.
Image - Cell B7, 7137,40$'lık yeni toplamı günceller.
B7 hücresini seçtiğinizde, formül çubuğunda güncellenmiş formül görünür.
=TOPLA(B2:OFFSET(B7, -1, 0))
OFFSET işlevinin iki isteğe bağlı bağımsız değişkeni vardır: Bu örnekte kullanılmayan Yükseklik ve Genişlik. Bu argümanlar OFFSET işlevine çıktının şeklini satır ve sütun sayısı cinsinden söyler.
Bu bağımsız değişkenleri atlayarak, işlev, bunun yerine, bu örnekte bir satır yüksekliğinde ve bir sütun genişliğinde olan Referans bağımsız değişkeninin yüksekliğini ve genişliğini kullanır.
