Excel OFFSET Fonksiyonu (Örnek + Video)
Excel OFFSET İşlevi ne zaman kullanılır?
Excel OFFSET işlevi, belirtilen sayıda satır ve sütunu başlangıç noktasından öteleyen bir referans almak istediğinizde kullanılabilir.
Ne Döndürür
OFFSET fonksiyonunun işaret ettiği referansı döndürür.
Sözdizimi
=OFFSET(referans, satırlar, sütunlar, [yükseklik], [genişlik])
Giriş Bağımsız Değişkenleri
- referans - Uzaklaştırmak istediğiniz referans. Bu, bir hücre referansı veya bir dizi bitişik hücre olabilir.
- satırlar - mahsup edilecek satır sayısı. Pozitif bir sayı kullanırsanız aşağıdaki satırlara, negatif bir sayı kullanılırsa yukarıdaki satırlara kaydırılır.
- sütunlar - dengelenecek sütun sayısı. Pozitif bir sayı kullanırsanız, sağdaki sütunlara kaydırılır ve negatif bir sayı kullanılırsa, soldaki sütunlara kaydırılır.
- [boy uzunluğu] - bu, döndürülen başvurudaki satır sayısını temsil eden bir sayıdır.
- [Genişlik] - bu, döndürülen başvurudaki sütun sayısını temsil eden bir sayıdır.
Excel OFFSET İşlevinin Temellerini Anlama
Excel OFFSET işlevi, muhtemelen Excel'deki en kafa karıştırıcı işlevlerden biridir.
Basit bir Satranç oyunu örneğini ele alalım. Satrançta Kale (kule, marki veya rektör olarak da bilinir) adı verilen bir parça vardır.
[Resim nezaket: Harika Wikipedia]
Şimdi, diğer tüm Satranç taşları gibi, bir Kalenin de tahta üzerinde hareket edebileceği sabit bir yolu vardır. Düz gidebilir (sağa/sola veya tahtada yukarı/aşağı), ancak çapraz gidemez.
Örneğin, Excel'de (aşağıda gösterildiği gibi) belirli bir kutuda (bu durumda D5) bir satranç tahtamız olduğunu varsayalım, Kale yalnızca vurgulanan dört yöne gidebilir.
Şimdi sizden Kaleyi mevcut konumundan sarı ile vurgulanmış olana götürmenizi istesem, kaleye ne söylersiniz?
İki adım aşağı ve iki adım sağa atmasını isteyeceksiniz… değil mi?
Ve bu, OFFSET işlevinin nasıl çalıştığının yakın bir örneğidir.
Şimdi bunun Excel'de ne anlama geldiğini görelim. D5 hücresiyle başlamak (ki bu Kalenin olduğu yer) ve sonra iki satır aşağı ve iki sütun sağa gidip oradaki hücreden değeri almak istiyorum.
Formül şöyle olurdu:
=OFFSET(nereden başlamalı, kaç satır aşağıda, kaç sütun sağda)
Gördüğünüz gibi yukarıdaki örnekte J1 hücresindeki formül =OFFSET(D5,2,2) şeklindedir.
D5'te başladı ve sonra iki sıra aşağı ve iki sütun sağa gitti ve F7 hücresine ulaştı. Daha sonra F7 hücresindeki değeri döndürdü.
Yukarıdaki örnekte OFFSET fonksiyonuna 3 argüman ile baktık. Ancak kullanılabilecek iki isteğe bağlı argüman daha var.
Burada basit bir örneğe bakalım:
A1 hücresine (sarı) başvuruyu kullanmak istediğinizi ve bir formülde mavi (C2:E4) ile vurgulanan tüm aralığa başvurmak istediğinizi varsayalım.
Bunu klavye kullanarak nasıl yapardınız? Önce C2 hücresine gidersiniz ve ardından C2:E4'teki tüm hücreleri seçersiniz.
Şimdi bunu OFFSET formülünü kullanarak nasıl yapacağımızı görelim:
=OFFSET(A1,1,2,3,3)
Bu formülü bir hücrede kullanırsanız, bir #DEĞER! ancak düzenleme moduna girip formülü seçip F9 tuşuna basarsanız, mavi ile vurgulanan tüm değerleri döndürdüğünü göreceksiniz.
Şimdi bu formülün nasıl çalıştığını görelim:
=OFFSET(A1,1,2,3,3)
- İlk argüman, başlaması gereken hücredir.
- İkinci argüman 1'dir ve Excel'e 1 satır OFFSET olan bir başvuru döndürmesini söyler.
- Üçüncü argüman 2'dir ve Excel'e 2 sütun kaydırılmış bir başvuru döndürmesini söyler.
- Dördüncü argüman 3'tür. Bu, başvurunun 3 satırı kapsaması gerektiğini belirtir. Buna yükseklik argümanı denir.
- Beşinci argüman 3'tür. Bu, başvurunun 3 sütunu kapsaması gerektiğini belirtir. Buna genişlik argümanı denir.
Artık bir hücre aralığına (C2:E4) referansa sahip olduğunuza göre, onu diğer işlevlerde (SUM, COUNT, MAX, ORTALAMA gibi) kullanabilirsiniz.
Umarım, Excel OFFSET işlevini kullanma konusunda iyi bir temel anlayışa sahipsinizdir. Şimdi OFFSET işlevini kullanmanın bazı pratik örneklerine bir göz atalım.
Excel OFFSET İşlevi - Örnekler
İşte Excel OFFSET işlevini kullanmanın iki örneği.
Örnek 1 - Sütundaki Son Doldurulan Hücreyi Bulma
Aşağıda gösterildiği gibi bir sütunda bazı verileriniz olduğunu varsayalım:
Sütundaki son hücreyi bulmak için aşağıdaki formülü kullanın:
=OFFSET(A1,COUNT(A:A)-1,0)
Bu formül, gösterilenler dışında hiçbir değer olmadığını ve bu hücrelerin içinde boş hücre bulunmadığını varsayar. Doldurulan toplam hücre sayısını sayarak çalışır ve A1 hücresini buna göre dengeler.
Örneğin, bu durumda 8 değer vardır, dolayısıyla SAYI(A:A) 8 değerini döndürür. Son değeri almak için A1 hücresini 7 ile kaydırırız.
Örnek 2 - Dinamik Açılır Aşağı Oluşturma
Dinamik adlandırılmış aralıklar oluşturmak için Örnek 1'deki konsept gösterilerini genişletebilirsiniz. Bunlar, formüllerde veya açılır listeler oluştururken adlandırılmış aralıkları kullanıyorsanız ve adlandırılmış aralığı oluşturan referanstan veri eklemeniz/silmeniz durumunda yararlı olabilir.
İşte bir örnek:
Yıl eklendiğinde veya kaldırıldığında açılır listenin otomatik olarak ayarlandığını unutmayın.
Bu, açılır menüyü oluşturmak için kullanılan formülün dinamik olması ve herhangi bir ekleme veya silme işlemini tanımlaması ve aralığı buna göre ayarlaması nedeniyle gerçekleşir.
Bunu nasıl yapacağınız aşağıda açıklanmıştır:
- Açılır menüyü eklemek istediğiniz hücreyi seçin.
- Veri -> Veri Araçları -> Veri Doğrulama'ya gidin.
- Veri Doğrulama iletişim kutusunda, Ayarlar sekmesinde açılır menüden Liste'yi seçin.
- Kaynağa şu formülü girin: =OFFSET(A1,0,0,COUNT(A:A),1)
- Tamam'ı tıklayın.
Bu formülün nasıl çalıştığını görelim:
- KAYDIR işlevinin ilk üç bağımsız değişkeni A1, 0 ve 0'dır. Bu, temelde aynı başvuru hücresini (A1 olan) döndüreceği anlamına gelir.
- Dördüncü argüman yükseklik içindir ve burada SAYI işlevi listedeki toplam öğe sayısını döndürür. Listede boşluk olmadığını varsayar.
- Beşinci argüman, sütun genişliğinin bir olması gerektiğini gösteren 1'dir.
Ek Notlar:
- OFFSET değişken bir işlevdir (dikkatli kullanın).
- Excel çalışma kitabı her açıldığında veya çalışma sayfasında bir hesaplama tetiklendiğinde yeniden hesaplar. Bu, işlem süresini artırabilir ve çalışma kitabınızı yavaşlatabilir.
- Yükseklik veya genişlik değeri atlanırsa, referansınki olarak alınır.
- Eğer satırlar ve cols negatif sayılar ise ofset yönü tersine çevrilir.
Excel OFFSET Fonksiyon Alternatifleri
Excel OFFSET işlevinin bazı sınırlamaları nedeniyle, birçoğunuz buna alternatifleri düşünmek istersiniz:
- INDEX İşlevi: INDEX işlevi, bir hücre başvurusunu döndürmek için de kullanılabilir. INDEX işlevini kullanarak dinamik adlandırılmış bir aralığın nasıl oluşturulacağına ilişkin bir örnek görmek için burayı tıklayın.
- Excel Tablosu: Excel Tablosunda yapılandırılmış referanslar kullanırsanız, eklenen yeni veriler ve formülleri ayarlama ihtiyacı konusunda endişelenmenize gerek yoktur.
Excel OFFSET İşlevi - Eğitim Videosu
- Excel DÜŞEYARA İşlevi.
- Excel HLOOKUP İşlevi.
- Excel İNDEKS İşlevi.
- Excel DOLAYLI İşlevi.
- Excel EŞLEŞTİRME İşlevi.
