Excel'de Yapılandırılmış Referanslar Nasıl Oluşturulur?
Yapılandırılmış Referanslar excel tabloları ile başlar. Excel'de oluşturulan tablolar 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 bağlantıyı B2 olarak göstermek yerine Tablo1 [@Satışlar] olarak göstermek yerine bir bağlantı verdim. Burada Table1 , tablonun adı ve @Sales , bahsettiğimiz 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 değişiklik 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
Bu Yapılandırılmış Referanslar Excel Şablonunu buradan indirebilirsiniz - Yapılandırılmış Referanslar Excel ŞablonuÖ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 tablosunun 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üncellenmiş 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 kopyalamaya veya sürüklemeye hepimiz aşinayız. Bu Yapılandırılmış Referanslarda aynı süreç değil, biraz farklı işliyor.
Şimdi aşağıdaki örneğe bakın. Normal aralık için Excel'de SUM formülünü uyguladım.
Fiyat ve Satış Değerini toplamak istersem, sadece mevcut formülü kopyalayıp yapıştıracağım veya diğer iki hücreye sürükleyeceğim ve bu bana Fiyat ve Satış Değerinin TOPLA değerini verecektir.
Ş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, yalnızca miktar sütununun toplamını göstermeye devam ediyor. 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
Artık sınırlamasını biliyoruz, artık yapılandırılmış referanslarla kopyala-yapıştır işini yapamayız. O halde bu sınırlamanın üstesinden nasıl gelebiliriz?
Çözüm çok basit, sadece formülü kopyalamak yerine sürüklememiz gerekiyor. 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 ama burada bir problemimiz daha var, formülü diğer hücrelere sürüklüyorsak bu çağrıyı mutlak referans olarak yapamayız.
Şimdi aşağıdaki örneğe bir göz atalım. Birden çok girdiye sahip bir satış tablom var ve Excel'de SUMIF işlevini kullanarak verileri 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, Feb & Mar referansını 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 Ölçüt Aralığı Sales_Table [Ürün] 'dür, çünkü formülü Satışlar _Tablosu [Ocak]' a sürüklediğimizden .
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ü 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.