Örnek çalışma kitabını indirin
OFFSET ve INDIRECT gibi geçici olan işlevlerin nasıl olduğu hakkında diğer makalelerde tartıştık. Bunların birçoğunu bir elektronik tabloda kullanmaya başlarsanız veya geçici işleve bağlı çok sayıda hücreniz varsa, bir hücreyi her değiştirmeye çalıştığınızda bilgisayarınızın yeniden hesaplamalar yapmak için gözle görülür bir zaman harcamasına neden olabilirsiniz. Bu makale, bilgisayarınızın yeterince hızlı olmamasından dolayı hayal kırıklığına uğramak yerine, insanların KAYDIRMA ve DOLAYLI olarak kullandığı yaygın durumları çözmenin alternatif yollarını keşfedecektir.
Dinamik bir liste oluşturmak için OFFSET'in değiştirilmesi
OFFSET işlevini öğrendikten sonra, son birkaç argümanı kullanarak dinamik boyutta bir sonuç döndürmenin tek yolunun bu olduğu yaygın bir yanlış anlamadır. Kullanıcımızın daha sonra ek öğeler eklemeye karar verebileceği A sütunundaki listeye bakalım.
C2 hücresinde bir açılır menü oluşturmak için, aşağıdaki gibi geçici bir formüle sahip bir Adlandırılmış Aralık tanımlayabilirsiniz:
=KAYDIR($A$2, 0, 0, COUNTA($A:$A)-1, 1)
Mevcut kurulumla, bu kesinlikle A2:A5 aralığına bir referans verecektir. Ancak, kalıcı INDEX'i kullanmanın başka bir yolu daha var. Bunu yapmak için, A2'den A5'e kadar olan aralığa bir referans yazdığımızı düşünün. “A2:A5” yazarken bunu tek bir veri parçası olarak değil, iki nokta üst üste ile ayrılmış bir “Başlangıç Noktası” ve “Bitiş Noktası” olarak düşünün (örn. Başlangıç Noktası:Bitiş Noktası). Bir formülde hem Başlangıç Noktası hem de Bitiş Noktası diğer işlevlerin sonuçları olabilir.
INDEX İşlevini kullanarak dinamik aralık oluşturmak için kullanacağımız formül:
=$A$2:INDEX($A:$A, COUNTA($A:$A))
Bu aralık için Başlangıç Noktasının her zaman A2 olacağını belirttiğimizi unutmayın. Kolonun diğer tarafında, EndingPoint'in nerede olacağını belirlemek için INDEX kullanıyoruz. COUNTA, A sütununda veri içeren 5 hücre olduğunu belirleyecektir ve bu nedenle INDEX'imiz A5'e bir başvuru oluşturacaktır. Formül böylece şöyle değerlendirilir:
=$A$2:INDEX($A:$A, COUNTA($A:$A)) =$A$2:INDEX($A:$A, 5) =$A$2:$A5
Bu tekniği kullanarak, INDEX işlevini kullanarak herhangi bir listeye veya hatta iki boyutlu bir tabloya dinamik olarak bir referans oluşturabilirsiniz. Çok sayıda OFFSET işlevine sahip bir elektronik tabloda OFFSET'leri INDEX ile değiştirmek, bilgisayarınızın çok daha hızlı çalışmaya başlamasını sağlayacaktır.
Sayfa adları için DOLAYLI değiştirme
DOLAYLI işlevi genellikle, çalışma kitapları birden çok çalışma sayfasına dağılmış verilerle tasarlandığında çağrılır. Tüm verileri tek bir sayfaya alamıyorsanız ancak geçici bir işlev kullanmak istemiyorsanız, SEÇME'yi kullanabilirsiniz.
3 farklı çalışma sayfasında Satış verilerimizin bulunduğu aşağıdaki düzeni göz önünde bulundurun. Özet sayfamızda, hangi çeyreğe ait verileri görüntülemek istediğimizi seçtik.
B3'teki formülümüz:
=SEÇ(KAÇINCI(B2, D2:D4, 0), Güz!A2, Kış!A2, Bahar!A2)
Bu formülde, MATCH işlevi hangi alanı döndürmek istediğimizi belirleyecektir. Bu daha sonra SEÇ işlevine aşağıdaki aralıklardan hangisinin sonuç olarak döndürüleceğini söyler.
Daha geniş bir aralık döndürmek için SEÇ işlevini de kullanabilirsiniz. Bu örnekte, üç çalışma sayfamızın her birine ilişkin bir satış verileri tablosuna sahibiz.
Sayfa adını oluşturmak için bir DOLAYLI işlevi yazmak yerine, aramanın hangi tabloda yapılacağını SEÇME'ye bırakabilirsiniz. Örneğimde, tbFall, tbWinter ve tbSpring adlı üç tabloyu zaten adlandırdım. B4'teki formül:
=DÜŞEYARA(B3, SEÇ(KAÇINCI(B2, D2:D4, 0), tbSonbahar, tbKış, tbBahar), 2, 0)
Bu formülde, MAÇ 2'yi istediğimizi belirleyecek.nd listemizdeki öğe. CHOOSE daha sonra bu 2'yi alacak ve referansı tbWinter'a döndürecektir. Son olarak, DÜŞEYARA'mız verilen tablodaki aramayı tamamlayabilecek ve Muz için kışın toplam satışlarının 6000$ olduğunu bulacaktır.
=DÜŞEYARA(B3, SEÇ(KAÇINCI(B2, D2:D4, 0), tbSonbahar, tbKış, tbBahar), 2, 0) =DÜŞEYARA(B3, SEÇ(2, tbSonbahar, tbKış, tbBahar), 2, 0) = DÜŞEYARA(B3, tbKış; 2, 0) =6000
Bu teknik, bir değer almak isteyebileceğiniz tüm alanlarla SEÇ işlevini doldurmanız gerektiği gerçeğiyle sınırlıdır, ancak size uçucu bir formülden kaçınma avantajı sağlar. Kaç tane hesaplama yapmanız gerektiğine bağlı olarak, bu yetenek oldukça değerli olabilir.
