Bu yazımızda Excel'de OFFSET fonksiyonunun nasıl kullanılacağını öğreneceğiz.
Nedir OFSET Fonksiyonu?
Ofset işlevi, bir bilgi alma işlevidir. Belirli verileri içeren büyük bir tablonuz varsa ve ondan veri almak istiyorsanız, bunu yapmanın en iyi yolu, o çalışma sayfasındaki tablo için veya aynı çalışma kitabındaki başka bir sayfa için bir ofset formülü eklemektir. Böylece, ofset alım tablosunu oluşturduktan sonra, örneğin 'ay' gibi verileri girmeniz yeterlidir ve o ayın 'satışlarını' veya 'gelirini' o tablodan alırsınız.
Tabloyu gözden geçirmek başka bir seçenektir, ancak tümü 1000 sütun veri tablosu içeren sayfalarınız ve veri sayfalarınız varsa seçeneği düşünür müsünüz? Ofsetin diğer işlevlerle birleştirildiği yer burasıdır.
Pivot tablo kavramı ofsetten gelir. Bir pivot tablo, genel bir tablodur ve gerektiğinde ve gerektiğinde bu belirli veri ve grafik kombinasyonundan elde edilir. Aynı şekilde dinamik tablolar da oluşturulabilir. Bu tablolar için excel ofset formülünü oluşturmanız gerekecek.
OFFSET sözdizimi
| =OFFSET(referans,satırlar, sütunlar, [yükseklik], [genişlik]) |
satırlar - Excel'den bilgi almak için satır sayısını taşımasını istersiniz. Bu durumda, masanın her yerinde hareket etmesi ve virgül (,) ile gösterilen bir boşluk bırakması gerekir. Aksi takdirde, bir sütunu ileriye taşımak için 1 değerini ve bir sütunu öne taşımak için -1 değerini verin.
Sütunlar - bu, işlevi sütun sayısına yönlendirmek içindir. Değer sistemi satır için olanla aynıdır.
Boy uzunluğu - tablonun yüksekliği, bu durumda A11.
Genişlik - tablonun genişliği, bu durumda D11.
Ofset işlevini ayarladıktan sonra, herhangi bir hatayı ayıklamak için bir deneme yapmak önemlidir.
Ad, e-posta kimliği, doğum tarihi ve yaşı içeren bir tablo alalım. Tablo, 'Ad' başlığını içeren A1 hücresinden başlar. Veriler, örneğin A11 için çalışır. Ve satırlar D1'e kadar devam eder. Tablonun tamamı A1:D11'i çalıştırır. E-posta kimliğini girdiğinizde adı veya diğer ayrıntılarla birlikte adı girdiğinizde e-posta kimliğini size veren bir alma sistemi istiyorsunuz. 5 bağımsız değişkeni karşılayarak bir formül oluşturursunuz.
Referans noktası, aramanın başlaması gereken hücredir. Bu durumda A2 olmalıdır. Genel standart budur.
Örnek:
Örneğin, B4 ve ofset satırı 0 ve ofset sütunu 1 referansını verirseniz, C4'ten gelen değer döndürülür. Kafa karıştırıcı? Biraz daha örnek verelim. OFFSET fonksiyonunun son örneği, dinamik olarak nasıl SUM yapılacağını anlatır.
Bir Hücrenin Sağından ve Solundan Değer Almak İçin OFFSET Fonksiyonu
Bu tablo bizde.
Şimdi 2 hücreden B2'ye (yani D2'ye) değer almak istersem. Bu OFFSET formülünü yazacağım:
| =OFFSET(B2,0,2) |
OFFSET işlevi, B4 hücresinin 2 hücresini sağa kaydıracak ve değerini döndürecektir. Aşağıdaki resme bakın
Soldaki 1 hücreden B2'ye (bu A2 anlamına gelir) değer almak istersem. Bu OFFSET formülünü yazacağım:
| =OFFSET(B2,0,-1) |
Eksi (-) işareti, ters yönde hareket etmek için ofseti gösterir.
Bir Hücrenin Altından ve Üstünden Değer Almak İçin OFFSET Fonksiyonu
Yani yine yukarıdaki tabloda, 2 hücreden B3'e (yani B5'e) değer almak istersem. Bu OFFSET formülünü yazacağım:
| =OFFSET(B2,2,0) |
Soldaki 1 hücreden B2'ye (bu A2 anlamına gelir) değer almak istersem. Bu OFFSET formülünü yazacağım:
| =OFFSET(B2,-2,0) |
- Profesyonel İpucu: Bir OFSET'i, Referans'ın orijin ve satır ve sütunun x ve y ekseni olduğu grafik işaretçisi olarak ele alın.
OFFSET Fonksiyonu Aralığı Dinamik Olarak Toplama
Bu örneği görelim.
Yukarıdaki tabloda, tablonun sonundaki toplam satır. Zamanla bu tabloya satırlar ekleyeceksiniz. O zaman formüldeki toplam aralığını değiştirmeniz gerekir. Burada dinamik olarak toplamak için OFFSET fonksiyonunun yardımını alabiliriz. Şu anda C11 hücresinde =TOPLA(C2:C10). Bunu aşağıdaki formülle değiştirin.
| =TOPLA(C2:OFFSET(C11;-1,0)) |
Bu formül yalnızca ilk veri satırını alır ve ardından toplam satırın üzerindeki aralığı toplar.
Dizi Almak İçin Ofset
OFFSET işlevinin iki isteğe bağlı argümanı vardır, [height] ve [width]. Onları hafife almayın. KAYDIR işlevi [yükseklik] ve [genişlik] bağımsız değişkenleri sağlandığında, iki boyutlu bir dizi döndürür. Bunlardan yalnızca biri argüman olarak iletilirse, boyutlu değerler dizisine döner.
Bu formülü herhangi bir hücreye yazarsanız:
| =TOPLA(OFFSET(C1,1,0,9,3)) |
Aşağıdaki C2'den 9 satıra ve 3 sütun sağdan başlayarak aralıktaki değerleri toplayacaktır.
OFSET(C1,1,0,9,3) : Bu {8,8,7;6,1,2;8,5,8;5,1,5;8,3,5;8,3,9;8,9,2;3,5 olarak çevrilecektir. ,4;6,6,5}.
Sonuç olarak, ofset, birçok boğulmuş ipliği çözmenize yardımcı olabilecek excel'in çok kullanışlı bir işlevidir. OFFSET işlevini formülünüzde nasıl kullandığınızı ve size en çok nerede yardımcı olduğunu bana bildirin.
Arama işlevi ofset ile aynıdır ancak dinamik bir tabloyu çalıştırmak için Match, Counta ve IF gibi işlevleri kullanır.
Excel'de ofseti kullanmak, Excel'in işlevleri ve birlikte nasıl çalıştıkları konusunda beceri ve iyi bilgi gerektirir. Herhangi bir hata yapmadan çekmek en büyük engeldir.
Formülün kullanımıyla ilgili tüm gözlem notları burada.
Notlar:
- Bu formül hem metin hem de sayılarla çalışır.
- Hatasız bir bilgi almak için karşılanması gereken ofsetin beş argümanı vardır. Yanlış bir formül yazarsanız, bir Ref hatası oluşur.
Umarım Excel Ofset İşlevi Nedir ve Excel'de Nasıl Kullanılır anlamışsınızdır. Excel'de arama hakkında daha fazla makale keşfedin ve buradaki formülleri kullanarak değerleri eşleştirin. Bloglarımızı beğendiyseniz, Facebook'ta arkadaşlarınızla paylaşın. Ayrıca bizi Twitter ve Facebook'ta da takip edebilirsiniz. Sizden haber almayı çok isteriz, işimizi nasıl iyileştirebileceğimizi, tamamlayabileceğimizi veya yenileyebileceğimizi ve sizin için daha iyi hale getirebileceğimizi bize bildirin. E-posta sitesinde bize yazın.
Excel'de notu hesaplamak için DÜŞEYARA işlevi : Notları hesaplamak için IF ve IFS kullanabileceğiniz tek işlevler değildir. DÜŞEYARA bu tür koşullu hesaplamalar için daha verimli ve dinamiktir. DÜŞEYARA kullanarak notları hesaplamak için bu formülü kullanabiliriz.
Excel DÜŞEYARA Hakkında 17 Şey : DÜŞEYARA en yaygın olarak eşleşen değerleri almak için kullanılır, ancak DÜŞEYARA bundan çok daha fazlasını yapabilir. İşte DÜŞEYARA hakkında etkili bir şekilde kullanmak için bilmeniz gereken 17 şey.
Excel'deki Bir Listeden İlk Metni ARA : DÜŞEYARA işlevi, joker karakterlerle sorunsuz çalışır. Bunu, Excel'deki belirli bir listeden ilk metin değerini çıkarmak için kullanabiliriz. İşte genel formül.
Listedeki son değere sahip ARAMA tarihi : Son değeri içeren tarihi almak için ARA işlevini kullanırız. Bu işlev, bir vektördeki son değeri içeren hücreyi kontrol eder ve ardından tarihi döndürmek için bu başvuruyu kullanır.
Excel'de En Son Fiyat Nasıl Alınır : Herhangi bir işletmede fiyatları güncellemek yaygındır ve herhangi bir satın alma veya satış için en son fiyatları kullanmak zorunludur. Excel'deki bir listeden en son fiyatı almak için ARA işlevini kullanırız. ARA işlevi en son fiyatı getirir.
Popüler Makaleler:
Verimliliğinizi Arttıracak 50 Excel Kısayolu : Görevinizde daha hızlı olun. Bu 50 kısayol, Excel'de daha da hızlı çalışmanızı sağlayacaktır.
Excel'de EĞER İşlevi nasıl kullanılır? : Excel'deki EĞER ifadesi koşulu kontrol eder ve koşul DOĞRU ise belirli bir değer veya YANLIŞ ise başka bir belirli değer döndürür.
Excel'de DÜŞEYARA İşlevi nasıl kullanılır? : Bu, farklı aralık ve sayfalardan değer aramak için kullanılan excel'in en çok kullanılan ve popüler işlevlerinden biridir.
Excel'de COUNTIF İşlevi nasıl kullanılır? : Bu şaşırtıcı işlevi kullanarak değerleri koşullarla sayın. Belirli değerleri saymak için verilerinizi filtrelemeniz gerekmez. Gösterge tablonuzu hazırlamak için Countif işlevi gereklidir.
Excel'de SUMIF İşlevi nasıl kullanılır? : Bu, başka bir gösterge panosu temel işlevidir. Bu, belirli koşullardaki değerleri özetlemenize yardımcı olur.