Özet
Bir zaman birimindeki (yani günlük, haftalık, aylık vb.) Bir gideri diğer zaman birimlerine dönüştürmek için iki yönlü bir INDEX ve MATCH formülü kullanabilirsiniz. Gösterilen örnekte, E5'teki formül (çapraz ve aşağı kopyalanır):
=$C5*INDEX(data,MATCH($D5,vunits,0),MATCH(F$4,hunits,0))
burada veriler (O5: S9), vunitler (N5: N9) ve hunitler (O4: S4) aşağıda açıklandığı gibi adlandırılmış aralıklardır.
Açıklama
Bir zaman birimindeki (yani günlük, haftalık, aylık vb.) Bir gideri diğer zaman birimlerine dönüştürmek için iki yönlü bir INDEX ve MATCH formülü kullanabilirsiniz. Gösterilen örnekte, E5'teki formül (çapraz ve aşağı kopyalanır):
=$C5*INDEX(data,MATCH($D5,vunits,0),MATCH(F$4,hunits,0))
Bu formül, aşağıda gösterildiği gibi adlandırılmış aralıklara sahip bir arama tablosu kullanır:
Adlandırılan aralıklar: data (O5: S9), vunits (N5: N9) ve hunits (O4: S4).
Giriş
Amaç, bir zaman birimindeki bir gideri diğer zaman birimlerindeki eşdeğer bir harcama dönüştürmektir. Örneğin, aylık 30 ABD doları tutarında bir giderimiz varsa, 360 ABD doları tutarında bir yıllık gider, 7,50 ABD doları tutarında bir haftalık gider vb. Hesaplamak istiyoruz.
Excel'deki pek çok zorluk gibi, çoğu da soruna nasıl yaklaştığınıza bağlıdır. İlk önce iç içe geçmiş EĞER formülleri zincirini düşünmek isteyebilirsiniz. Bu yapılabilir, ancak sonunda uzun ve karmaşık bir formül elde edeceksiniz.
Daha temiz bir yaklaşım, tüm olası dönüşümler için dönüşüm faktörlerini içeren bir arama tablosu oluşturmak, ardından belirli bir dönüşüm için gerekli değeri almak için iki yönlü bir INDEX ve MATCH formülünü kullanmaktır. Değeri elde ettiğinizde, basitçe orijinal miktarla çarpabilirsiniz.
Dönüşüm tablosu
Dönüşüm tablosu hem dikey hem de yatay etiketler için aynı değerlere sahiptir: günlük, haftalık, iki haftada bir, aylık ve yıllık. "Kimden" birimleri dikey olarak ve "kime" birimleri yatay olarak listelenir. Bu örneğin amaçları doğrultusunda, önce satırı, sonra sütunu eşleştirmek istiyoruz. Öyleyse, bir aylık gideri yıllık gidere dönüştürmek istiyorsak, "aylık" satırı ve "yıllık" sütunu eşleştirip 12 döndürürüz.
Tablonun kendisini doldurmak için, basit formüllerin ve sabitlerin bir karışımını kullanıyoruz:
Not: Özel ihtiyaçlarınızı karşılamak için dönüşüm değerlerini özelleştirin. = 1/7 olarak bir değer girmek, uzun ondalık değerler girmekten kaçınmanın kolay bir yoludur.
Arama formülü
İki girdiye dayalı bir dönüşüm değeri bulmamız gerektiğinden, bir "başlangıç" zaman birimi ve bir "ila" zaman birimi, iki yönlü bir arama formülüne ihtiyacımız var. INDEX ve MATCH güzel bir çözüm sunar. Gösterilen örnekte, E5'teki formül şöyledir:
=$C5*INDEX(data,MATCH($D5,vunits,0),MATCH(F$4,hunits,0))
İçten dışa doğru çalışarak, ilk MATCH işlevi doğru satırı bulur:
MATCH($D5,vunits,0) // find row, returns 4
Adlandırılmış aralık vunitlerinde (N5: N9) doğru satırı bulmak için kullandığımız D sütunundan orijinal "başlangıç" zaman birimini çekeriz . Not $ D5, sütun kilitli karma bir referanstır, bu nedenle formül kopyalanabilir.
İkinci KAÇINCI işlevi sütunu bulur:
MATCH(F$4,hunits,0) // find column, returns 5
Burada, 4. satırdaki sütun başlığından arama değerini alırız ve bunu adlandırılmış aralık isabetleri (O4: S4) içinde doğru "-" sütununu bulmak için kullanırız . Yine, not F $ 4, satır kilitli karma bir referanstır, bu nedenle formül kopyalanabilir.
Her iki MATCH formülü sonuçları INDEX'e döndürdükten sonra, elimizde:
=$C5*INDEX(data,4,5)
INDEX'e sağlanan dizi, adlandırılmış aralık verileridir (O5: S9). 4'lük bir satır ve 5'lik bir sütun ile, INDEX 12'yi döndürür, böylece 12000'lik bir nihai sonuç şu şekilde elde ederiz:
=$C5*INDEX(data,4,5) =1000*12 =12000
