Dinamik veri aralıkları için toplamları bulmak amacıyla SUM ve OFFSET kullanın
Excel çalışma sayfanız, değişen bir hücre aralığına dayalı hesaplamaları içeriyorsa, SUM OFFSET formülünde SUM ve OFFSET işlevlerini birlikte kullanarak hesaplamaları güncel tutma görevini basitleştirir.
SUM ve OFFSET İşlevleriyle Dinamik Bir Aralık Oluşturun
Sürekli olarak değişen bir süre için hesaplamalar kullanırsanız (örneğin, ayın toplam satışları gibi) OFFSET işlevi, her gün satış rakamları eklendikçe değişmeyen dinamik bir aralık ayarlamanıza olanak tanır.
Kendi başına, SUM fonksiyonu genellikle toplanan aralığa yerleştirilen yeni veri hücrelerini barındırabilir.
Bir özel durum, veriler işlevin bulunduğu hücreye eklendiğinde oluşur.
Bu makaleye eşlik eden örnek görüntüde, her gün için yeni satış rakamları listenin altına eklenir; bu da, yeni veri eklendiğinde, bir hücreyi sürekli olarak bir hücreye kaydırmaya zorlar.
SUM fonksiyonu, verileri toplamlamak için tek başına kullanılmışsa, her yeni veri eklendiğinde fonksiyonun argümanı olarak kullanılan hücre aralığını değiştirmek gerekecektir.
Ancak, SUM ve OFFSET işlevlerini birlikte kullanarak, toplam aralığı dinamik olur. Başka bir deyişle, yeni veri hücrelerini barındırmak için değişir. Yeni hücrelerin eklenmesi sorun yaratmaz, çünkü her yeni hücre eklendikçe aralık devam eder.
Sözdizimi ve Argümanlar
Bu eğiticiyle birlikte takip etmek için bu makaleye eşlik eden resme bakın.
Bu formülde, SUM işlevi, argümanı olarak sağlanan veri aralığını toplamlamak için kullanılır. Bu aralığın başlangıç noktası statiktir ve formül tarafından toplanacak ilk numaraya hücre referansı olarak tanımlanır.
OFFSET işlevi SUM işlevinin içine yerleştirilir ve formül tarafından toplanan veri aralığına dinamik bir son nokta oluşturmak için kullanılır. Bu, aralığın son noktasının, formülün bulunduğu yerin üzerinde bir hücreye ayarlanmasıyla gerçekleştirilir.
Formülün sözdizimi :
= SUM (Aralık Başlangıcı: OFSET (Referans, Satır, Cols))Aralık Başlat - SUM işlevi tarafından toplamlanacak hücre aralığı için başlangıç noktası (gerekli). Örnek resimdeki bu, B2 hücresidir.
Referans - (gerekli) aralığın uç noktasını hesaplamak için kullanılan hücre referansı çok sayıda satır ve sütun bulundu. Örnek görüntüde, Referans argümanı formülün kendisi için hücre referansıdır, çünkü aralığın her zaman formülün üzerinde bir hücreyi sonlandırmasını istiyoruz.
Satırlar - (gerekli) ofsetin hesaplanmasında kullanılan Referans argümanının üstündeki veya altındaki satırların sayısı. Bu değer pozitif, negatif veya sıfıra ayarlanabilir.
Ofsetin konumu Referans argümanı üzerindeyse, bu değer negatiftir. Altında ise, Satırlar argümanı pozitiftir. Ofset aynı satırda bulunuyorsa, bu argüman sıfırdır. Bu örnekte, ofset, Reference argümanı üzerinde bir satır başlar, bu nedenle bu argümanın değeri bir negatiftir (-1).
Cols - (gerekli) ofsetin hesaplanmasında kullanılan Referans bağımsız değişkeninin solunda veya sağında bulunan sütun sayısı. Bu değer pozitif, negatif veya sıfır olarak ayarlanabilir
Ofsetin konumu Referans argümanının solundaysa, bu değer negatiftir. Sağa doğruysa, Cols argümanı pozitiftir. Bu örnekte, toplanan veriler formülle aynı sütundadır, bu nedenle bu argümanın değeri sıfırdır.
SUM OFFSET Formülünü Toplam Satış Verisine Kullanma
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 satış verileri dört gün boyunca toplandı.
Bir sonraki adım, SUM OFFSET formülünü beşinci gün satış toplamına yer açmak için bir satıra taşımaktır.
Bu, formülü 7 numaralı satıra taşıyan yeni bir satır 6 ekleyerek gerçekleştirilir.
Bu işlemin bir sonucu olarak, Excel otomatik olarak Reference argümanı B7 hücresine günceller ve B6 hücresini formül tarafından toplanan aralığa ekler.
SUM OFFSET Formula Girme
- Formülün sonuçlarının başlangıçta görüntüleneceği yer olan B6 hücresine tıklayın.
- Şerit menüsünün Formüller sekmesine tıklayın.
- Fonksiyon açılır listesini açmak için şeritten Math & Trig'i seçin.
- İşlev iletişim kutusunu açmak için listedeki SUM'a tıklayın.
- İletişim kutusunda, Number1 satırına tıklayın.
- Bu hücre referansını iletişim kutusuna girmek için B2 hücresini tıklayın. Bu konum, formül için statik son noktadır;
- İletişim kutusunda, Number2 satırına tıklayın.
- Aşağıdaki OFFSET fonksiyonunu girin: Formül için dinamik son noktayı oluşturmak için OFFSET (B6, -1,0) .
- İşlevi tamamlamak ve iletişim kutusunu kapatmak için Tamam'a tıklayın.
Toplam $ 5679.15 B7 hücresinde görünür.
B3 hücresini tıkladığınızda, çalışma sayfasının üstündeki formül çubuğunda = SUM (B2: OFFSET (B6, -1,0)) işlevi görüntülenir.
Sonraki Günün Satış Verilerini Ekleme
Bir sonraki günün satış verilerini eklemek için:
- Bağlam menüsünü açmak için satır 6 için satır üstbilgisine sağ tıklayın.
- Menüde, çalışma sayfasına yeni bir satır eklemek için Ekle'ye tıklayın.
- Sonuç olarak, SUM OFFSET formülü B7 hücresine ilerler ve sıra 6 şimdi boştur.
- A6 hücresini tıklayın.
- Beşinci gün için satış toplamının girildiğini belirtmek için 5 rakamını girin.
- B6 hücresine tıklayın.
- Numarayı 1458.25 $ yazın ve klavyede Enter tuşuna basın.
B7 hücresi yeni 7137,40 $ 'a güncellenir.
B7 hücresine tıkladığınızda, güncellenen formül = SUM (B2: OFFSET (B7, -1,0)) formül çubuğunda görünür.
Not : OFFSET fonksiyonunun iki isteğe bağlı argümanı vardır: Bu örnekte ihmal edilen Yükseklik ve Genişlik .
Bu argümanlar, OFFSET fonksiyonunun çıkış şeklini, çok fazla sayıda satır yüksekliğinde ve çok fazla sütun genişliği bakımından belirtmek için kullanılabilir.
Bu argümanları göz ardı ederek, işlev, varsayılan olarak, bu örnekte bir satır yüksek ve bir sütun genişliği olan, bunun yerine Referans bağımsız değişkeninin yüksekliğini ve genişliğini kullanır.