Excel'de çalışmak genellikle çeşitli veri noktaları arasındaki bağlantıları bulmakla ilgilidir. Ancak karmaşık formüller eklerken, tekrar tekrar bağıl ve mutlak açık hücre başvuruları ("B7" veya varyasyonları gibi) kullanmak, formül çubuğu okunamaz bir karmaşaya dönüşmeden önce sizi yalnızca belirli bir yere kadar götürebilir.
Excel'deki yapılandırılmış başvurular, tablolara ve başlıklarına adlar atayarak bu işi kolaylaştırmanıza olanak tanır. Bu adlar daha sonra örtük hücre başvuruları olarak kullanılabilir, böylece Excel yapılandırılmış verileri otomatik olarak alabilir ve hesaplayabilir.
Excel'de yapılandırılmış referansları kullanmanın en yaygın yollarından bazıları şunlardır.
1. İç Tabloların Hesaplanması
Yapılandırılmış referanslar yalnızca tablolarda çalıştığı için, bunları kullanmanın en iyi yolu aynı tabloların içindedir.
Örneğin, bir mağazanın satış verileriyle B2'den F8'e kadar basit bir tablo oluşturacağız. Tabloya "Satışlar" adını verdiğimizi unutmayın (sol üstteki "Tablo Adı"na bakın).
Her satış için toplamı hesaplayalım:
Adım 1: F2'ye tıklayın (ancak açılır simgeye değil). "Ana Sayfa"ya gidin, ardından "Ekle"ye gidin ve "Tablo Sütunlarını Sağa Ekle"yi seçin. Bu, tabloya otomatik olarak yeni bir sütun ekleyecektir.
Adım 2: G sütununun başlığına “Toplam” adını verin.
Adım 3: G3'te, ekleyin =[@PricePerUnit]*[@Quantity]ve Enter'a basın. Hücre çıktısını gerektiği gibi biçimlendirin.
“[@PricePerUnit]” ve “[@Quantity]” bu sütunlardaki karşılık gelen alanlara referanslardır. Sütun adlarından önceki “@” argümanı her sonuç hücresinin aynı tablo satırındaki referansları kullanacağı anlamına gelir.
Çeviri yapmak gerekirse, G3'teki formül =[@PricePerUnit]*[@Quantity]temelde şunu yazmakla aynıdır =$C3*$D3.
2. Tablonun Dışındaki Bir Aralığı Getirme
Tablonun dışındaki bir hücrede yapılandırılmış bir başvuru kullanmak istediğinizde, başvuruya TableName ile önsöz eklemeniz gerekir. Önceki örneğimizde, “Sales[Total]” kullanmak “Sales” tablosunun “Total” başlığı altındaki tüm aralığı getirecektir. Bu, işleyebileceğiniz bir dizide birden fazla değer elde edeceğiniz anlamına gelir.Aralığın aşağıya doğru taşması için yeterli alan bıraktığınız takdirde, Excel'in I3 hücresinde bu şekilde görünür.
3. Bir Sütunu Toplama ve Kısmen Toplama
Tüm bir sütunu hızlıca toplamak için, “Tablo Tasarımı” seçeneklerindeki (“Tablo Stili Seçenekleri” altında) “Toplam Satır” onay işaretini kullanabilirsiniz. İşte “Miktar” ve “Toplam” sütunları için toplamları alma örneği.
"Toplam" satırı kendi başına taşınamaz ve tablonun sonuna yerleştirilir (eklemelere izin verir), ancak sonucunu başka bir yerde çoğaltabilirsiniz:
- "Toplam" sütunundaki tüm satırların toplamını almak için kullanın =SUM(Sales[Total]).
- Tabloyu filtreledikten sonra olduğu gibi yalnızca görünür sütunların toplamını almak istiyorsanız =SUBTOTAL(109,Sales[Total]), . Bu formül, Tablo Biçimi'ndeki "Toplam Satır" seçeneğinin satırında gerçekte yaptığı şeydir.
- Mike'ın tüm satışlarının toplamını almak için kullanabilirsiniz =SUMIF(Sales[Seller],”Mike”,Sales[Total]). Formülde, "Mike" elle girilen bir dizedir.
- "41230" ID'li tüm ürünlerin toplamını almak için aşağıdakini kullanın =SUMIF(Sales[ProductID],41230,Sales[Total]). ProductID sütununun "Genel" biçimine sahip olduğunu unutmayın, bu nedenle sayıyı doğrudan girebilirsiniz.
4. INDIRECT Yoluyla Tablodan Veri Doğrulaması
Daha önce kullanılmış Satışlar tablonuz olduğunu varsayalım. Tabloda aramayı kolaylaştırmak için özel Veri Doğrulama seçenekleri oluşturabilirsiniz. Ürün Kimlikleri, Tarihler veya Satıcılar arasında seçim yapmanıza ve ardından alt toplamı görüntülemek için bu alt kümelerden herhangi bir tek öğeyi seçmenize olanak tanıyan daha küçük bir tablo oluşturalım.
Adım 1: B13 hücresinde Veri Doğrulaması'nı oluşturun (Veri sekmesi > Veri araçları > Veri Doğrulaması).
Adım 2: Açılan pencerede, “İzin Ver” seçeneklerinden “Liste”yi seçin, ardından sütunların değerlerini virgülle ayırarak “Kaynak” kutusuna manuel olarak girin. Bu durumda, “ÜrünKimliği, Satıcı, Tarih” ekledik.
Adım 3: C13 hücresinde başka bir Veri Doğrulaması oluşturun. Tekrar “Liste”yi seçin. “Kaynak” için şu formülü girin: =INDIRECT("Sales["&B13&"]").
Adım 4: D13 hücresinde aşağıdaki formülü kullanın: =SUMIF(INDIRECT("Sales["&B13&"]"),B14,Sales[Total]).
Artık iki veri doğrulama listesinden seçenekleri seçebilirsiniz ve ara toplam D13'te gösterilecektir.
