Excel'de Yapılandırılmış Referanslar Nasıl Oluşturulur?
Yapılandırılmış Referanslar excel tabloları ile başlar. Excel'de tablolar oluşturulur oluşturulmaz sizin için otomatik olarak yapılandırılmış referanslar oluşturur.
Şimdi aşağıdaki resme bir göz atın.
- Adım 1: B3 hücresine bir bağlantı vermiştim. Bağlantıyı B2 olarak göstermek yerine Table1 (@Sales) olarak gösteriliyor. Burada Table1 tablonun adıdır ve @Sales atıfta bulunduğumuz sütundur. Bu sütundaki tüm hücrelere bir Tablo adı ve ardından sütun başlığı adı verilir.
- Adım 2: Şimdi, tablo adını Data_Table olarak değiştireceğim ve sütun başlığını Amount olarak değiştireceğim .
- Adım 3: Tablo adını değiştirmek için tablonun içine bir imleç yerleştirin> Tasarım> Tablo Adı'na gidin.
- Adım 4: Tablo adını Data_Table olarak belirtin.
- Adım 5: Şimdi, B3 hücresine bir referans verin.
Dolayısıyla, yapılandırılmış referansın iki bölümü Tablo Adı ve Sütun Adı olduğunu anladık .
Örnekler
Örnek 1
Yapılandırılmış referansları kullanarak formülünüzü dinamik hale getirebilirsiniz. Normal hücre referanslarından farklı olarak, veri aralığında ekleme ve silme olması durumunda formülün canlı olmasına izin verir.
SUM formülünü hem normal aralık hem de excel tablosu için uygulamama izin verin.
Normal Aralık için TOPLA Formülü.
Excel Tablosu için TOPLA Formülü.
Hem normal hem de excel tablolarının verilerine birkaç satır ekleyeyim. Verilere 2 satır öğesi ekledim, şimdi farkı görün.
Excel tablosundaki yapılandırılmış referans güncellenen değeri gösterir, ancak formülde manuel olarak bazı değişiklikler yapmadığınız sürece normal veri aralığı güncellenen değerleri göstermez.
Örnek 2
Şimdi, bir örneğe daha bakın. Ürün Adı, Miktar ve Fiyat bilgilerim var. Bu bilgileri kullanarak Satış Değerine ulaşmam gerekiyor.
Satış değerini elde etmek için formül Miktar * Fiyat şeklindedir . Bu formülü tabloya uygulayalım.
Formül (@QTY) * (@PRICE) diyor . Bu, B2 * C2'nin normal referansından daha anlaşılırdır . Formülü tablonun içine koyarsak tablo adını alamayız.
Excel Yapılandırılmış Referanslarla İlgili Sorunlar
Yapılandırılmış referansları kullanırken, aşağıda listelenen bazı sorunlarla karşılaşıyoruz.
Sorun 1
Yapılandırılmış referansların da kendi sorunları vardır. Excel formülünü uygulamaya ve diğer hücrelere kopyalayıp sürüklemeye hepimiz aşinayız. Bu, Yapılandırılmış Referanslarda aynı süreç değildir. Biraz farklı çalışıyor.
Şimdi aşağıdaki örneğe bakın. SUM formülünü normal aralık için excel'de uyguladım.
Fiyat ve Satış Değerini toplamak istersem, sadece mevcut formülü kopyalayıp yapıştırırım veya diğer iki hücreye sürüklerim ve bu bana Fiyat ve Satış Değerinin TOPLA değerini verir.
Şimdi aynı formülü Qty sütunu için excel tablosu için uygulayın.
Şimdi Qty sütununun toplamını aldık. Normal aralık gibi, formül mevcut formülü kopyalar ve toplam Fiyat'ı elde etmek için Fiyat sütununa yapıştırır.
Aman Tanrım!!! Fiyat sütununun toplamını göstermiyor; bunun yerine, hala yalnızca Miktar sütununun toplamını gösteriyor. Bu nedenle, göreli sütun veya satıra başvurmak için bu formülü bitişik hücreye veya başka bir hücreye kopyalayıp yapıştıramayız.
Referansı Değiştirmek için Formülü Sürükleyin
Şimdi sınırlamasını biliyoruz. Yapılandırılmış referanslar ile artık kopyala-yapıştır işini yapamıyoruz. O halde bu sınırlamanın üstesinden nasıl gelebiliriz?
Çözüm çok basit. Kopyalamak yerine formülü sürüklememiz yeterli. Formül hücresini seçin ve doldurma tutamacını kullanın ve sütun referansını Fiyat ve Satış Değeri olarak değiştirmek için kalan iki hücreye sürükleyin.
Şimdi, ilgili toplamları almak için formülleri güncelledik.
Sorun 2
Yapı referanslarında bir problem gördük ve çözümü de bulduk, ancak burada bir problemimiz daha var, formülü diğer hücrelere sürüklüyorsak mutlak referans olarak çağrıyı yapamayız.
Şimdi aşağıdaki örneğe bir göz atalım. Birden çok giriş içeren bir satış tablom var ve verileri Excel'de SUMIF işlevini kullanarak konsolide etmek istiyorum.
Şimdi her ürün için konsolide satış değerlerini elde etmek için SUMIF işlevini uygulayacağım.
Formülü Ocak ayı için uyguladım. Yapılandırılmış bir referans olduğu için, formülü kalan iki sütuna kopyalayıp yapıştıramayız. Başvuruyu Feb & Mar'a değiştirmeyecek, bu yüzden formülü sürükleyeceğim.
Oh !! Feb & Mar sütununda herhangi bir değer almadım. Sorun ne olurdu ??? Formüle yakından bakın.
Formülü Ocak ayından itibaren sürükledik. SUMIF işlevinde ilk bağımsız değişken, formülü sürüklediğimizden beri Ölçüt Aralığı Sales_Table (Ürün) 'dür . Sales _Table (Oca) olarak değiştirildi.
Peki bununla nasıl başa çıkacağız? İlk argümanı, yani Ürün sütununu mutlak ve diğer sütunları göreceli referans olarak yapmalıyız. Normal referansın aksine, referans türünü değiştirmek için F4 tuşunu kullanma lüksüne sahip değiliz.
Çözüm, aşağıdaki resimde gösterildiği gibi referans sütununu çoğaltmamız gerektiğidir.
Şimdi formülü diğer iki sütuna sürükleyebiliriz. Ölçüt Aralığı sabit olacak ve diğer sütun referansları buna göre değişecektir.
Profesyonel İpucu: SATIR'ı mutlak referans yapmak için çift SATIR girişi yapmamız gerekir, ancak SATIR adının önüne @ sembolünü koymamız gerekir.
= Sales_Table (@ (Ürün) :( Ürün))
Excel'de Yapılandırılmış Başvuru Nasıl Kapatılır?
Yapılandırılmış referansların hayranı değilseniz, aşağıdaki adımları izleyerek kapatabilirsiniz.
- Adım 1: DOSYA> Seçenekler'e gidin.
- Adım 2: Formüller> Formüllerde tablo adlarını kullan seçeneğinin işaretini kaldırın .
Hatırlanacak şeyler
- Yapılandırılmış referansta mutlak referansı yapmak için sütun adını ikiye katlamamız gerekir.
- Yapılandırılmış referans formülünü kopyalayamayız; bunun yerine formülü sürüklememiz gerekir.
- Yapılandırılmış referanslarda tam olarak hangi hücreye atıfta bulunduğumuzu göremiyoruz.
- Yapılandırılmış referanslarla ilgilenmiyorsanız, bunları kapatabilirsiniz.
