Excel'in OFSET işlevi, çalışma kitabınızın hesaplamasını yavaşlatacaktır. Daha iyi bir alternatif var: INDEX'in alışılmadık bir sözdizimi.
Bu niş bir ipucu. OFFSET adında inanılmaz derecede esnek bir işlev var. Esnektir çünkü anında hesaplanan farklı büyüklükteki bir aralığa işaret edebilir. Aşağıdaki resimde, birisi H1'deki # Qtrs açılır menüsünü 3'ten 4'e değiştirirse, KAYDIR'ın dördüncü argümanı aralığın dört sütunu içerecek şekilde genişlemesini sağlayacaktır.
Elektronik tablo uzmanları, geçici bir işlev olduğu için OFFSET'ten nefret eder. Tamamen ilgisiz bir hücreye gidip bir sayı girerseniz, tüm OFSET fonksiyonları hesaplayacaktır. Bu hücrenin H1 veya B2 ile ilgisi olmasa bile. Çoğu zaman, Excel yalnızca hesaplaması gereken hücreleri hesaplayarak zaman kaybetmeye çok dikkat eder. Ancak OFSET'i sunduğunuzda, tüm OFSET hücreleri artı OFSET'ten gelen her şey, çalışma sayfasındaki her değişiklikten sonra hesaplamaya başlar.
Avustralya'dan birkaç arkadaşım tuhaf bir geçici çözüme işaret ettiğinde, New York City'deki 2013 ModelOff finallerini değerlendiriyordum. Aşağıdaki formülde, INDEX işlevinden önce bir iki nokta üst üste vardır. Normalde, aşağıda gösterilen INDEX işlevi, 1403'ü D2 hücresinden döndürür. Ancak INDEX işlevinin her iki tarafına iki nokta üst üste koyduğunuzda, D2'nin içeriği yerine D2 hücre adresini döndürmeye başlar. Bu, işe yaraması vahşi.
Bu neden önemli? INDEX uçucu değil. OFFSET'in tüm esnek güzelliklerini defalarca zaman emen yeniden hesaplamalar yapmadan elde edersiniz.
Bu ipucunu ilk olarak Fintega'daki Dan Mayoh'dan öğrendim. Bu özelliği önerdiği için Access Analytic'e teşekkür ederiz.
Video izle
Video Metni
Podcast, bölüm 2048'den Excel öğrenin -: INDEX Geçici Bir OFSET'in yerini alacak!
Hey, bu kitaptaki tüm ipuçlarımı podcast yayınlıyorum, oynatma listesine ulaşmak için sağ üst köşedeki "i" yi tıklayın!
Pekala, OFFSET harika bir fonksiyon! KAYDIR, bir sol üst köşe hücresi belirlememize ve ardından değişkenleri kullanarak buradan kaç satır aşağı, kaç satır üstünü tanımlamamıza ve ardından bir şekil tanımlamamıza izin verir. Yani burada, toplamak istersem veya ortalama yapmak istersem, 3/4 diyelim, buradaki formül formüle bir göz atacak. OFFSET, Q1'den başlayarak B2'den başladığımızı söylüyor, 0'dan aşağıya, 0'ın üzerine gidiyoruz, şekil 1 satır yüksekliğinde olacak ve H1, yani 3 hücre genişliğinde olacak. Yani bu durumda, gerçekten yaptığımız tek şey, kaç tane hücre topladığımızı değiştirmek, her zaman tekrar B2 veya B3 veya B4'te kopyalıyorum ve sonra kaç hücre genişliğine karar veriyor. Pekala, OFFSET bu harika şey, her türlü harika işlevi gerçekleştiriyor, ama işte güçlük, uçucu!Bu, hesaplama zincirinde bir şey olmasa bile, Excel'in onu yeniden hesaplamak için zaman alacağı ve bu da işleri yavaşlatacağı anlamına gelir.
INDEX'in bu harika versiyonu, evet, normalde bu 4 hücrenin INDEX'ini sorarsam, bu 1403 sayısını döndürecektir. Ancak, INDEX'in önüne veya arkasına iki nokta koyduğumda, çok farklı bir şey olur, burada bu formüle bir göz atacağız. Yani 4 hücrenin indeksi, hangisini istiyorum, üçüncü olanı istiyorum, ama burada bir: olduğunu görüyorsunuz. Yani her zaman B2: E2'den başlayacağız ve size Formüller, Formülü Değerlendir'e gidersek göstereceğim, pekala, işte burada 3 sayısını hesaplayacak. Ve burada, INDEX ile: sonraki ona, bize 1403'ü, INDEX'in normalde nasıl hesaplayacağını söylemek yerine, D2 $ 'ı döndürecek ve sonra ORTALAMA bu 3'ün ortalamasını yapacak. Kesinlikle şaşırtıcı, bunun çalışma şekli ve ek fayda, uçucu değil, pekalave bu inanılmaz derecede karmaşık bir OFSET'in yerini almak için bile kullanılabilir.
Yani burada bu OFSET ile bu değerleri temel alıyoruz. Q2 ve Central'ı seçersem, tamam, bu formüller kaç satır aşağı, kaç sütun fazla, ne kadar uzun, ne kadar geniş olduğunu bulmak için MATCH ve COUNTIF kullanıyor. Ve sonra buradaki OFFSET, medyanı bulmak için tüm bu değerleri kullanıyor. Çalıştığından emin olmak için sadece bir test yapacağız, yani = oradaki mavi aralığın ORTANCA, 71 olsa iyi olur ve burada başka bir şey seçeceğiz, Q3 ve Batı, yani. F2'ye basın, sadece bunu sürükleyip bu formülü yeniden yazmak için yeniden boyutlandıracağım, Enter tuşuna basın ve OFFSET ile çalışıyor.
Burada, bir INDEX kullanarak, ortada sol tarafta bir INDEX ve sağ tarafta bir INDEX olan bir kolon var, bu şeyin Hesaplama Formülünde hesaplanmasını izleyin. Böylece başlar, Değerlendirecek, Değerlendirecek ve tam burada INDEX onu bir hücre adresine dönüştürmek üzere. Yani H16 1., Batı, 3. Çeyrek ve sağ taraf daha değerlendirecek, daha çift olacak ve sonra onu düzeltip I20'ye dönüşecek. Bu nedenle, INDEX işlevini kullanarak bir OFSET'in yerini alacak soğuk, soğuk uçucu olmayan. Pekala, bu ipucu ve bu kitaptaki daha pek çok şey, kitabı satın almak için sağ üst köşedeki "i" yi tıklayın.
Pekala özet: OFFSET, harika, esnek işlev, bir kez ustalaştığınızda her türlü şeyi yapabilirsiniz. Sol üstteki değişken bir hücrenin üzerine gelin, değişken bir şekle sahip olan ancak uçucu bir aralığa işaret edin ve bu nedenle her hesaplamada hesaplarlar. Excel genellikle akıllı bir hesaplama yapar veya hesaplanması gereken hücreleri yeniden hesaplar, ancak OFFSET ile her zaman hesaplanır. OFFSET yerine INDEX: veya: INDEX veya INDEX: INDEX kullanabilirsiniz, INDEX'in yanında iki nokta üst üste bulunduğunda, o hücrenin değeri yerine bir hücre adresi döndürecektir. Ve faydası INDEX'in uçucu olmamasıdır!
Tamam, uğradığınız için teşekkür etmek istiyorum, bir dahaki sefere başka bir internet yayını için görüşürüz!
Dosyayı indir
Örnek dosyayı buradan indirin: Podcast2048.xlsm
