Genel Bakış
Genişleyen Başvuru (Expanding Reference), başlangıç hücresi mutlak, bitiş hücresi göreli yazılan ve formül aşağı ya da yana kopyalandıkça satır satır büyüyen aralık başvurusudur. Birikimli toplam, kümülatif ortalama, o ana kadarki en yüksek değer ve bir değerin kaçıncı kez geçtiği gibi hesaplarda kullanılır. Ayrı bir komut ya da işlev değildir; mutlak ve göreli başvuruyu aynı aralıkta birleştirerek elde edilir.
Aylık satışların birikimli toplamı için C2 hücresine şu formülü yazıp aşağı kopyalayın:
C sütunundaki aralık her satırda bir hücre büyür. C9 hücresi sekiz ayın tamamını toplar ve 126.800,00 sonucunu verir. D sütununda ise aralık her satırda bir hücre küçülür. D2 hücresi tüm listeyi toplayarak 126.800,00 sonucunu verir, D9 hücresi yalnızca Ağustos satırını kapsar ve 20.400,00 değerini gösterir.
C sütunu, şehrin listenin başından o satıra kadar kaç kez geçtiğini gösterir. A8 hücresindeki İstanbul için 3 bulunur; bu değer 1 olan satırlar şehrin ilk kaydıdır. D sütunu aynı şehrin o satıra kadarki siparişlerini toplar; son İstanbul satırında 7.200,00 değerine ulaşır. İki formülde de ölçüt aralığı $A$2'den başlayıp geçerli satırda biter.
Her satır yalnızca kendisine kadar olan haftaları değerlendirir. İlk satırda ortalama, rekor ve en düşük değer aynı haftanın puanıdır. Sekizinci satırda ortalama 82,1, rekor 95 ve en düşük puan 66 olur. Rekor sütunu yalnızca yükselir ya da aynı kalır, en düşük sütunu ise yalnızca düşer ya da aynı kalır; çünkü aralık hiçbir zaman küçülmez.
Çalışma mantığı
Genişleyen başvuruda aralığın ilk hücresi dolar işaretleriyle sabitlenir, son hücresi sabitlenmez. Örneğin $B$2:B2 başvurusu ikinci satırda yalnızca B2 hücresini kapsar. Formülü bir satır aşağı kopyaladığınızda başvuru $B$2:B3 olur, sonraki satırda $B$2:B4 diye devam eder. İlk hücre yerinde kalır, son hücre satırla birlikte kayar. Böylece her satır, listenin başından kendi satırına kadar olan bölümü hesaplar.Aylık satışların birikimli toplamı için C2 hücresine şu formülü yazıp aşağı kopyalayın:
Nasıl kurulur
Genişleyen başvuruyu kurmak için şu adımları izleyin:- Formülü yazın: İlk satırdaki hücreye işlevi yazın ve aralığın ilk hücresini seçin, örneğin B2.
- İlk hücreyi sabitleyin: İmleç başvurunun üzerindeyken F4 tuşuna basın. Başvuru $B$2 olur. F4'e art arda basmak sırasıyla $B$2, B$2, $B2 ve B2 biçimlerini döndürür.
- Aralığı tamamlayın: İki nokta üst üste yazıp son hücre olarak yine B2'yi seçin. Bu kez F4'e basmayın, göreli kalsın. Sonuç $B$2:B2 olur.
- Aşağı kopyalayın: Hücrenin sağ alt köşesindeki Doldurma Tutamacı'na (Fill Handle) çift tıklayın ya da aralığı seçip Giriş > Düzenleme > Doldur > Aşağı (Home > Editing > Fill > Down) komutunu veya Ctrl+D kısayolunu kullanın.
Ters yönlü ve yatay kullanım
Aynı mantık farklı yönlerde de çalışır:- Kalan toplam: B2:$B$9 gibi, ilk hücresi göreli ve son hücresi mutlak bir aralık aşağı kopyalandıkça küçülür. Bu, o satırdan listenin sonuna kadar olan değerleri verir.
- Yatay genişleme: Aylar sütunlara yazılmışsa $B2:B2 gibi, sütun harfi sabit, satır göreli bir aralık yana kopyalandıkça sağa doğru büyür.
- Yalnızca ilk hücre sabit: Başlangıç noktası dolar işaretiyle korunmazsa kopyalanan formülde aralığın iki ucu da kayar ve sonuç genişleyen değil, kayan bir aralık olur.
Hangi işlevlerle kullanılır
Aralık bekleyen hemen her işlevle kullanılabilir:- TOPLA (SUM): Birikimli toplam.
- ORTALAMA (AVERAGE): O ana kadarki ortalama.
- MAK (MAX) ve MİN (MIN): O ana kadarki en yüksek ve en düşük değer.
- EĞERSAY (COUNTIF): Bir değerin listede kaçıncı kez geçtiğini bulur; sıra numarası ve tekrar denetimi için kullanılır.
- ETOPLA (SUMIF): Her grubun o ana kadarki toplamını verir.
- ALTTOPLAM (SUBTOTAL): Filtrelenmiş listelerde yalnızca görünen satırların birikimini hesaplar.
1. Birikimli toplam ve kalan toplam
Sekiz aylık satış listesinde C sütununda yılbaşından o aya kadar olan birikimli toplamı, D sütununda o aydan yıl sonuna kadar kalan toplamı hesaplayın. C2 hücresine ilk hücresi sabit bir aralık, D2 hücresine son hücresi sabit bir aralık yazın, ardından ikisini de 9. satıra kadar aşağı kopyalayın.Genişleyen Başvuru
Formülünü incelemek için bir hücre seçin.| A | B | C | D | |
|---|---|---|---|---|
| 1 | Ay | Satış | Birikimli Toplam | Kalan Toplam |
| 2 | Ocak | 12.500,00 | 12.500,00 | 126.800,00 |
| 3 | Şubat | 14.200,00 | 26.700,00 | 114.300,00 |
| 4 | Mart | 11.800,00 | 38.500,00 | 100.100,00 |
| 5 | Nisan | 15.600,00 | 54.100,00 | 88.300,00 |
| 6 | Mayıs | 17.300,00 | 71.400,00 | 72.700,00 |
| 7 | Haziran | 16.100,00 | 87.500,00 | 55.400,00 |
| 8 | Temmuz | 18.900,00 | 106.400,00 | 39.300,00 |
| 9 | Ağustos | 20.400,00 | 126.800,00 | 20.400,00 |
Aylık SatışExcelDepo v1.0.0
2. Kaçıncı sipariş ve şehir bazında birikim
Sipariş listesinde her şehrin kaçıncı kez geçtiğini C sütununda, o şehrin o satıra kadarki sipariş toplamını D sütununda bulun. C2 hücresine EĞERSAY, D2 hücresine ETOPLA formülünü yazın ve ikisini de aşağı kopyalayın. Her iki formülde de ölçüt aralığının ilk hücresi sabittir.Genişleyen Başvuru
Formülünü incelemek için bir hücre seçin.| A | B | C | D | |
|---|---|---|---|---|
| 1 | Şehir | Sipariş Tutarı | Kaçıncı Sipariş | Şehir Birikimi |
| 2 | İstanbul | 2.400,00 | 1 | 2.400,00 |
| 3 | Ankara | 1.800,00 | 1 | 1.800,00 |
| 4 | İzmir | 950,00 | 1 | 950,00 |
| 5 | İstanbul | 3.100,00 | 2 | 5.500,00 |
| 6 | Bursa | 1.250,00 | 1 | 1.250,00 |
| 7 | Ankara | 2.200,00 | 2 | 4.000,00 |
| 8 | İstanbul | 1.700,00 | 3 | 7.200,00 |
| 9 | İzmir | 1.400,00 | 2 | 2.350,00 |
Şehir SiparişleriExcelDepo v1.0.0
3. Ortalama, en yüksek ve en düşük puan
Bir satış ekibinin sekiz haftalık puanlarını izliyorsunuz. C sütununda o haftaya kadarki ortalamayı, D sütununda o haftaya kadarki rekoru, E sütununda o haftaya kadarki en düşük puanı hesaplayın. Üç formülde de aralık $B$2 ile başlar ve geçerli satırda biter.Genişleyen Başvuru
Formülünü incelemek için bir hücre seçin.| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Hafta | Puan | Ortalama | Rekor | En Düşük |
| 2 | 1. Hafta | 72 | 72,0 | 72 | 72 |
| 3 | 2. Hafta | 85 | 78,5 | 85 | 72 |
| 4 | 3. Hafta | 78 | 78,3 | 85 | 72 |
| 5 | 4. Hafta | 91 | 81,5 | 91 | 72 |
| 6 | 5. Hafta | 66 | 78,4 | 91 | 66 |
| 7 | 6. Hafta | 88 | 80,0 | 91 | 66 |
| 8 | 7. Hafta | 95 | 82,1 | 95 | 66 |
| 9 | 8. Hafta | 82 | 82,1 | 95 | 66 |
Haftalık PuanExcelDepo v1.0.0
Kullanırken dikkat edin
- Başlangıcı sabitlemeyi unutmayın: Dolar işaretleri yazılmazsa B2:B2 aşağı kopyalandığında B3:B3 olur ve aralık büyümek yerine tek hücrede kalır. Sonuç, birikimli toplam yerine her satırın kendi değeri çıkar. Başvuruyu yazarken ilk hücrede F4'e basmayı alışkanlık edinin.
- Başlık satırını aralığa katmayın: Aralığı başlık hücresinden başlatırsanız metin değerler TOPLA, ORTALAMA gibi işlevlerce yok sayılır, ancak EĞERSAY gibi işlevlerde başlığın da sayıldığı hatalı sonuçlar çıkabilir. Aralığı her zaman ilk veri satırından başlatın.
- Satır ekleme ve sıralama: Aralığın ilk satırının üstüne satır eklerseniz mutlak başlangıç aşağı kayar ve yeni satır hesaba girmez. Veriyi, ilk veri satırının üstüne değil, ilk ve son veri satırlarının arasına ekleyin. Birikimli sütunlar sıraya bağlı olduğundan listeyi sıraladığınızda sonuçların anlamı değişir; hesabı sıralamadan sonra yeniden kurun ya da formüllerin sıralanan aralıkla birlikte hareket ettiğinden emin olun.
- Filtrelenmiş listelerde: TOPLA gizli ve filtrelenmiş satırları da toplar. Yalnızca görünen satırların birikimini istiyorsanız ALTTOPLAM işlevini 9 işlev numarasıyla ve genişleyen aralıkla birlikte kullanın.
- Büyük listelerde hız: Her satırdaki formül başa kadar tüm aralığı yeniden tarar. Onbinlerce satırda bu hesaplamayı yavaşlatabilir. Yalnızca birikimli toplam gerekiyorsa, bir üst satırdaki sonuca geçerli satırın değerini ekleyen basit bir formül (önceki toplam artı geçerli değer) daha hızlı çalışır.
- İlgili konular: Mutlak ve göreli başvuru, karma başvuru, F4 kısayolu, Doldurma Tutamacı, Excel Tablosu ve yapılandırılmış başvurular, TOPLA, ORTALAMA, MAK, MİN, EĞERSAY, ETOPLA, ALTTOPLAM.
