Örnek çalışma kitabını indirin
Bu Öğretici, Excel OFSET İşlevi Excel'de bir ilk hücreden bir referans uzaklığı oluşturmak için.
OFSET İşlevine Genel Bakış
KAYDIRMA İşlevi, tanımlı bir hücre referansı ile başlar ve bir hücre referansını, orijinal referanstan belirli sayıda satır ve sütun ofsetiyle döndürür. Başvurular bir hücre veya bir hücre aralığı olabilir. Ofset, referansı belirli sayıda satır/sütun olarak yeniden boyutlandırmanıza da olanak tanır.
(Formül girişlerinin nasıl göründüğüne dikkat edin)
EĞERHATA İşlev Sözdizimi ve Girişler:
| 1 | =OFFSET(referans,satırlar,sütunlar,yükseklik,genişlik) |
referans - Mahsup etmek istediğiniz ilk hücre başvurusu.
satırlar - Mahsup edilecek satır sayısı.
cols - Mahsup edilecek sütun sayısı.
boy uzunluğu - İSTEĞE BAĞLI: Referanstaki satır sayısını ayarlayın.
Genişlik - İSTEĞE BAĞLI: Referanstaki sütun sayısını ayarlayın.
OFFSET işlevi nedir?
OFFSET işlevi, yarattığı şeyde oldukça çok yönlü olabileceğinden, daha güçlü elektronik tablo işlevlerinden biridir. Kullanıcıya çeşitli konum ve boyutlarda bir hücre veya aralık tanımlama yeteneği verir.
DİKKAT: OFFSET işlevi geçici işlevlerden biridir. E-tablonuzda çalışırken çoğu zaman, bilgisayar yalnızca girdilerin değerlerini değiştirmişse bir formülü yeniden hesaplar. Ancak uçucu bir işlev, yeniden hesaplar her Herhangi bir hücrede değişiklik yaptığınız zaman. Uçucu işlevin aşırı kullanımı veya uçucu bir işlevin sonucuna bağlı çok sayıda hücre olması nedeniyle büyük bir yeniden hesaplama süresine neden olmamak için dikkatli olunmalıdır.
Temel Satır Örnekleri
OFFSET işlevinin her kullanımında bir başlangıç noktası veya çapa vermeniz gerekir. Bunu anlamamıza yardımcı olması için bu tabloya bakalım:
Bağlantı noktamız olarak B3 hücresindeki "Bob"u kullanacağız. Hemen altındaki değeri (Charlie) almak isteseydik, satırı 1 ile kaydırmak istediğimizi söylerdik. Formülümüz şöyle görünürdü.
| 1 | =OFFSET(B3, 1) |
Yukarı kaydırmak isteseydik, bu olumsuz bir kayma olurdu. Bunu satır sayısı azalıyormuş gibi düşünebilirsiniz, bu yüzden çıkarmamız gerekiyor. Böylece yukarıdaki değeri (Adam) elde etmek için şunu yazardık:
| 1 | =OFFSET(B2, -1) |
Temel Sütun Örnekleri
Önceki örnekten devam ederek, tablomuza başka bir sütun ekleyeceğiz.
Bob için öğretmeni yakalamak isteseydik, formülü kullanabilirdik.
| 1 | =OFFSET(B2, 0, 1) |
Bu örnekte, sıfır satırları (yani aynı satırda kal) kaydırmak istediğimizi ancak 1 sütunu kaydırmak istediğimizi söyledik. Sütunlar için, pozitif bir sayı sağa kaydırmak anlamına gelir ve negatif sayılar sola kaydırmak anlamına gelir.
OFSET ve MAÇ
Birkaç veri sütununuz olduğunu ve kullanıcıya sonuçların hangi sütundan alınacağını seçme olanağı vermek istediğinizi varsayalım. INDEX işlevini kullanabilir veya OFFSET kullanabilirsiniz. KAÇINCI bir değerin göreli konumunu döndüreceğinden, bağlantı noktasının ilk olası değerimizin solunda olduğundan emin olmamız gerekir. Aşağıdaki düzeni göz önünde bulundurun:
B2'de şu formülü yazacağız:
| 1 | =KAYDIR(B2, 0, KAÇINCI(A2, $C$1:$F$1, 0)) |
MAÇ C1:F1 aralığında “Şubat” arayacak ve 2'de bulacak.nd hücre. OFFSET daha sonra 1 sütunu B2'nin sağına kaydırır ve istenen 9 değerini alır.
NOT: Bu teknik, arama aralığınızın solundan/yukarısından bir değer döndürmek istediğinizde DÜŞEYARA veya YATAYARA yerine kullanılabilir. Bunun nedeni, OFFSET'in negatif ofsetler yapabilmesidir.
Bir aralık elde etmek için OFFSET
4'ü kullanabilirsinizNS ve 5NS OFFSET işlevindeki bağımsız değişkenler, yalnızca tek bir hücre yerine bir aralık döndürmek için kullanılır. Bu tablodaki 3 sütunu toplamak istediğinizi varsayalım.
| 1 | =ORTALAMA(OFFSET(A1,KAÇINCI(F2,A2:A5,0),1,1,3)) |
F2'de, ortalama test puanlarını almak istediğimiz bir öğrencinin adını seçtik. Bunu yapmak için formülü kullanacağız
| 1 | =ORTALAMA(OFFSET(A1,KAÇINCI(F2,A2:A5,0),1,1,3)) |
MAÇ, adımızı A sütununda arayacak ve örneğimizde 3 olan göreli konumu döndürecektir. Bakalım bu nasıl değerlendirilecek. İlk olarak, OFFSET gidecek aşağı A1'den 3 satır ve 1 sütundan sağ A1'den. Bu bizi B3 hücresine yerleştirir.
| 1 | =ORTALAMA(OFFSET(A1, 3, 1, 1, 3)) |
Ardından, aralığı yeniden boyutlandıracağız. Yeni aralıkta sol üst hücre olarak B3 olacaktır. 1 satır yüksekliğinde ve 3 sütun yüksekliğinde olacak ve bize B4:D4 aralığını verecek.
| 1 | =ORTALAMA(OFFSET(A1,3, 1, 1, 3)) |
Uzaklık bağımsız değişkenlerine yasal olarak negatif değerler koyabilseniz de, boyutlandırma bağımsız değişkenlerinde yalnızca negatif olmayan değerleri kullanabileceğinizi unutmayın.
Sonunda, ORTALAMA işlevimiz şunu görür:
| 1 | =ORTALAMA(B4:D4) |
Böylece 86.67 çözümümüzü elde ederiz.
Dinamik SUM ile OFSET
OFFSET, doğrudan hücreye işaret etmek yerine bir başvuru bulmak için kullanıldığından, satır eklenmiş veya silinmiş verilerle uğraşırken en çok yardımcı olur. Altta bir Toplam bulunan aşağıdaki tabloyu göz önünde bulundurun
| 1 | =TOPLA(B2:B4) |
Burada “=TOPLA(B2:B4)” şeklinde temel bir SUM formülü kullansaydık ve ardından Bill için bir kayıt eklemek için yeni bir satır ekleseydik, yanlış cevaba sahip olurduk.
Bunun yerine, bunu Total'in bakış açısından nasıl çözeceğimizi düşünelim. B2 hücresinden hücreye kadar her şeyi gerçekten almak istiyoruz toplamımızın hemen üstünde. Bunu bir formülde yazabilmemizin yolu, -1'lik bir satır ofseti yapmaktır. Bu nedenle, bunu B5 hücresindeki toplamımız için formül olarak kullanırız:
| 1 | =TOPLA(B2:OFFSET(B5;-1,0)) |
Bu formül az önce tanımladığımız şeyi yapar: B2'den başlayın ve toplam hücremizin 1 hücresine gidin. Bill'in verilerini ekledikten sonra toplamımızın nasıl doğru şekilde güncellendiğini görebilirsiniz.
Son N öğeyi almak için OFFSET
Diyelim ki aylık satışlar kaydediyorsunuz ancak son 3 aya bakabilmek istiyorsunuz. Yeni veriler eklendikçe ayarlamaya devam etmek için formüllerinizi manuel olarak güncellemek zorunda kalmak yerine, COUNT ile KAYDIR işlevini kullanabilirsiniz.
Bir dizi hücreyi almak için OFFSET'i nasıl kullanabileceğinizi zaten gösterdik. Kaç hücreyi kaydırmamız gerektiğini belirlemek için, kaç tane hücreyi bulmak için COUNT kullanacağız. sayılar B sütunundadır. Örnek tablomuza bakalım.
| 1 | =TOPLA(OFFSET($B$1,COUNT(B:B)-$E$1+1,0,$E$1,1)) |
B1'den başlayıp 4 satırı kaydırırsak (B sütunundaki sayıların sayısı), B5 aralığımızın en altına düşeriz. Ancak, OFFSET negatif bir değerle yeniden boyutlandırılamayacağından, B3'e gelmemiz için bazı ayarlamalar yapmamız gerekiyor. Bunun için genel denklem yapmak olacak
| 1 | SAYI(… ) - N + 1 |
Tüm sütunun sayısını alırız, döndürmek istediğimiz kadarını çıkarırız (çünkü onları almak için yeniden boyutlandıracağız) ve sonra 1 ekleriz (çünkü esasen ofsetimize sıfır konumundan başlıyoruz).
Burada, son N ayın Toplamını, Ortalamasını ve Maks. değerini almak için bir aralık oluşturduğumuzu görebilirsiniz. E1'de 3 değerini girdik. E2'de formülümüz şudur:
| 1 | =TOPLA(OFFSET($B$1,COUNT(B:B)-$E$1+1,0,$E$1,1)) |
Vurgulanan bölüm, az önce tartıştığımız genel denklemimizdir. Herhangi bir sütunu dengelememize gerek yok. Ardından, aralığı 3 hücre uzunluğunda (E1'deki değer tarafından belirlenir) ve 1 sütun genişliğinde olacak şekilde yeniden boyutlandıracağız. SUM'umuz daha sonra bu aralığı alır ve bize 1,850 $ sonucunu verir. Ayrıca, dış işlevi SUM'dan durum ne gerektiriyorsa onu değiştirerek bu aynı aralığın maksimum ortalamasını hesaplayabileceğinizi de gösterdik.
OFFSET dinamik doğrulama listeleri
Son örnekte gösterilen tekniği kullanarak, Veri Doğrulama veya çizelgelerde kullanılabilecek Adlandırılmış Aralıklar da oluşturabiliriz. Bu, bir elektronik tablo oluşturmak istediğinizde ancak listelerimizin/verilerimizin boyutunun değişmesini beklediğinizde yardımcı olabilir. Diyelim ki mağazamız meyve satmaya başladı ve şu anda 3 seçeneğimiz var.
Başka bir yerde kullanabileceğimiz bir Veri Doğrulama açılır listesi yapmak için MyFruit adlı aralığı şöyle tanımlayacağız.
| 1 | =$A$2:OFFSET($A$1, COUNTA($A:$A)-1, 0) |
Metin değerleriyle uğraştığımız için COUNT yerine COUNTA kullanıyoruz. Bu nedenle, A1'deki başlık hücresini sayacağı ve 4 değerini vereceği için COUNTA'mız bir daha yüksek olacak. Yine de 4 satır kaydırırsak, sonunda boş olan A5 hücresine ulaşırız. Bunu ayarlamak için o zaman 1'i çıkarırız.
Artık Adlandırılmış Aralık kurulumumuzu elde ettiğimize göre, C4 hücresinde bir Liste türü kullanarak kaynakla birlikte bazı Veri Doğrulamaları ayarlayabiliriz:
| 1 | =MyFruit |
Açılır listenin yalnızca mevcut üç öğemizi gösterdiğini unutmayın. Daha sonra listemize daha fazla öğe ekler ve açılır listeye geri dönersek, liste, formüllerden herhangi birini değiştirmemize gerek kalmadan tüm yeni öğeleri gösterir.
OFFSET kullanımıyla ilgili uyarılar
Bu makalenin başında belirtildiği gibi, OFFSET geçici bir işlevdir. Sadece birkaç hücrede kullanıyorsanız bunu fark etmeyeceksiniz, ancak onu yüzlerce hesaplamaya dahil etmeye başlarsanız ve bilgisayarınızın her değişiklik yaptığınızda yeniden hesaplama yapmak için dikkate değer miktarda zaman harcadığını hemen fark edeceksiniz. .
Ek olarak, OFFSET baktığı hücrelere doğrudan isim vermediği için diğer kullanıcıların daha sonra gelip formüllerinizi gerektiğinde değiştirmesi daha zordur.
Bunun yerine, yapısal referanslara izin veren Tabloların (Office 2007'de sunulmuştur) kullanılması tavsiye edilir. Bunlar, kullanıcıların yeni veriler eklendikçe veya silindikçe boyutu otomatik olarak ayarlanan tek bir referans verebilmesine yardımcı oldu.
OFFSET yerine kullanılacak diğer seçenek, güçlü INDEX işlevidir. INDEX, bu makalede gördüğümüz tüm dinamik aralıkları geçici bir işlev olma sorunu olmadan oluşturmanıza olanak tanır.
ek Notlar
Bir başlangıç referansından belirli sayıda satır ve sütunu kaydırarak bir hücre değeri (veya bir hücre aralığı) döndürmek için KAYDIR İşlevini kullanın. Yalnızca tek bir hücre ararken, KAYDIRMA formülleri, biraz farklı bir teknik kullanarak INDEX Formülleriyle aynı amaca ulaşır. OFFSET İşlevinin gerçek gücü, başka bir formülde kullanılacak bir hücre aralığını seçme yeteneğinde yatmaktadır.
OFFSET İşlevini kullanırken, bir başlangıç başlangıç hücresi veya hücre aralığı tanımlarsınız. Ardından, bu ilk hücreden mahsup edilecek satır ve sütun sayısını belirtirsiniz. Ayrıca aralığı yeniden boyutlandırabilirsiniz; satır veya sütun ekleyin veya çıkarın.
Excel'deki Tüm İşlevler Listesine Dön
Google E-Tablolar'da OFFSET
OFFSET İşlevi, Google E-Tablolar'da Excel'dekiyle tamamen aynı şekilde çalışır:
