Orta Ders 7: Excel Tabloları ve Yapılandırılmış Başvuru

Aralık ile tablo arasındaki fark

Excel tablosu, sıradan bir hücre aralığına ad, biçim ve otomatik genişleme kazandıran bir yapıdır. Görünüşte yalnız renkli bir liste gibi dursa da formüller, filtreler ve grafikler tabloyu tek bir nesne olarak tanır. Yeni bir satır eklediğinizde tablo kendiliğinden büyür ve ona bağlı her şey bu satırı da hesaba katar.

Sıradan bir aralıkta ise =TOPLA(E2:E6) yazdıysanız 7. satıra eklenen kayıt toplamın dışında kalır. Bu hata fark edilmeden aylarca sürebilir. Tablo kullanmak bu riski büyük ölçüde ortadan kaldırır.

Tablo oluşturmak ve ad vermek

Tablo oluşturmak için verinizin içindeki herhangi bir hücreyi seçip Ctrl+T'ye basarsınız ya da Ekle sekmesinden Tablo'yu seçersiniz. Açılan pencerede aralığın doğru algılandığını ve başlık satırının tablonun parçası olarak işaretli olduğunu kontrol edin. Başlıklar sütun adları olacağı için kısa ve benzersiz olmalıdır.

Excel tabloya Tablo1 gibi bir ad verir. Tablo seçiliyken şeritte açılan Tablo Tasarımı sekmesinin solundaki ad kutusuna Satışlar gibi anlamlı bir ad yazın. Formüller bu adı kullanacağı için birkaç ay sonra dosyayı açtığınızda neyin hesaplandığını hemen anlarsınız.

Yapılandırılmış başvuru nasıl okunur?

Yapılandırılmış başvuru, hücre adresi yerine tablo ve sütun adı kullanan formül yazımıdır. Satışlar[Tutar] Satışlar tablosunun Tutar sütununun tamamını, [@Adet] ise formülün bulunduğu satırdaki Adet değerini gösterir. Sütun adında boşluk varsa ad ayrıca köşeli paranteze alınır: [@[Birim fiyat]].

Bu derste örnekler, tablo nesnesi olmayan bir aralıkta da çalışsın diye hücre adresleriyle verilmiştir. Her örneğin açıklamasında aynı formülün tablo içindeki karşılığını da bulacaksınız.

=ETOPLA(B2:B6;"Kırtasiye";E2:E6)

=SUMIF(B2:B6,"Kırtasiye",E2:E6)

Kırtasiye kategorisindeki üç ürünün tutarı 500 + 750 + 240 = 1.490 eder. Satışlar tablosunda aynı formül =ETOPLA(Satışlar[Kategori];"Kırtasiye";Satışlar[Tutar]) olarak yazılır ve tabloya yeni bir kırtasiye satırı eklendiğinde onu da toplar.

Hesaplanan sütun

Tablonun yanındaki ilk boş sütuna bir formül yazdığınızda Excel bunu hesaplanan sütuna çevirir ve formülü bütün satırlara kendisi uygular. Tutar sütununun ilk satırına =[@Adet]*[@[Birim fiyat]] yazıp Enter'a basmanız yeterlidir. Doldurma tutamacını sürüklemeniz gerekmez.

Yeni satır eklediğinizde hesaplanan sütun o satıra da yayılır. Bir satırdaki formülü elle değiştirirseniz Excel hücrenin köşesinde küçük bir uyarı gösterir. Sütunda tutarsız formül bırakmamak için değişikliği her zaman ilk satırda yapıp bütün sütuna yaydırın.

Toplam satırı ve ALTTOPLAM

Toplam satırı, tablonun en altına toplam, ortalama, sayım gibi özetleri tek tıkla eklemenizi sağlar. Tablo Tasarımı sekmesindeki toplam satırı onay kutusunu işaretlediğinizde Excel son sütunun altına bir formül yazar. Bu formül TOPLA değil ALTTOPLAM'dır.

=ALTTOPLAM(109;E2:E6)

=SUBTOTAL(109,E2:E6)

İlk argümandaki 109, "gizli satırları yok sayarak topla" anlamına gelir. Tabloyu Elektronik kategorisine filtrelediğinizde toplam satırı 4.910 yerine yalnız görünen iki satırın toplamını, yani 3.420'yi gösterir. Filtreli listelerde doğru ara toplamı görmenin en kısa yolu budur.

Sık karşılaşılan sorunlar

Tablo içindeki birleştirilmiş hücreler ve boş başlıklar sorun çıkarır. Excel boş başlığa Sütun1 gibi bir ad verir ve formülleriniz bu adla yazılır. Tabloyu oluşturmadan önce başlıkları doldurmak ve birleştirilmiş hücreleri çözmek daha temiz bir sonuç verir.

Başka bir dosyadan yapılandırılmış başvuruyla veri çekiyorsanız kaynak dosya kapalıyken bazı formüller #BAŞV! verebilir. Böyle bir bağlantıyı kalıcı kullanacaksanız veriyi Power Query ile almak daha sağlam bir yoldur.

Büyüyen bir gider listesi

Küçük bir ofisin gider listesini her hafta yeni satırlarla güncellediğini düşünün. Liste sıradan bir aralıkken özet sayfasındaki =TOPLA(D2:D120) formülü 121. satırdan itibaren eklenen giderleri görmez. Ay sonunda toplam eksik çıkar ve hatanın kaynağını bulmak zaman alır.

Aynı liste Giderler adında bir tabloya çevrildiğinde özet formülü =TOPLA(Giderler[Tutar]) olur. Tablonun hemen altındaki boş satıra yazdığınız her yeni gider tabloya katılır ve toplam kendiliğinden güncellenir. Kategori bazlı özetler, grafikler ve pivot tablolar da aynı genişlemeden yararlanır.

Tablonun bir başka faydası filtre düğmeleridir. Başlık satırındaki oklar tablo oluşturulduğu anda gelir; Veri sekmesinden ayrıca filtre açmanız gerekmez.

Tabloya yeni satır eklemenin en kolay yolu, son satırın son hücresindeyken Tab tuşuna basmaktır. Excel yeni bir satır açar ve hesaplanan sütunları bu satıra da uygular.

Sıradaki adım

Alıştırmada ürün tablosuna indirimli tutar sütunu ekleyeceksiniz. Formülü hücre adresleriyle yazdıktan sonra aynı veriyi kendi dosyanızda Ctrl+T ile tabloya çevirin ve formülü yapılandırılmış başvuruyla yeniden yazmayı deneyin.