İçerik
Excel'de yeniden boyutlandırılabilen veriler içeren tablolarınız var mı, yani çalışma sırasında satır (sütun) sayısı artabilir veya azalabilir mi? Tablo boyutları “yüzer” ise, bu anı sürekli izlemeniz ve düzeltmeniz gerekir:
- tablomuza başvuran rapor formüllerindeki bağlantılar
- tablomuza göre oluşturulmuş pivot tabloların başlangıç aralıkları
- tablomuza göre oluşturulmuş ilk grafik aralıkları
- tablomuzu veri kaynağı olarak kullanan açılır listeler için aralıklar
Bütün bunlar toplamda sıkılmanıza izin vermeyecek 😉
Boyut olarak gerçek satır ve sütun sayısına göre otomatik olarak ayarlanacak dinamik bir "kauçuk" aralık oluşturmak çok daha uygun ve doğru olacaktır. Bunu uygulamak için birkaç yol vardır.
Yöntem 1. Akıllı tablo
Hücre aralığınızı vurgulayın ve sekmeden seçin Ana Sayfa – Tablo Olarak Biçimlendir (Ana Sayfa – Tablo Olarak Biçimlendir):
Tabloya yan etki olarak eklenen çizgili tasarıma ihtiyacınız yoksa çıkan sekmeden kapatabilirsiniz. Yapıcı (Tasarım). Bu şekilde oluşturulan her tablo, sekmede aynı yerde daha uygun bir adla değiştirilebilecek bir ad alır. Yapıcı (Tasarım) alan içerisinde Tablo ismi (Tablo ismi).
Artık “akıllı tablomuza” dinamik bağlantılar kullanabiliriz:
- Tablo 1 – başlık satırı (A2:D5) hariç tüm tabloya bağlantı
- Tablo1[#Tümü] – tüm tabloya bağlantı (A1:D5)
- Tablo1[Peter] – ilk hücre başlığı (C2:C5) olmayan bir aralık sütununa başvuru
- Tablo1[#Başlıklar] – sütunların adlarını içeren “başlığa” bağlantı (A1:D1)
Bu tür referanslar formüllerde harika çalışır, örneğin:
= TOPLAM (Tablo1[Moskova]) – “Moskova” sütununun toplamının hesaplanması
or
=VPR(F5;Tablo 1;3;0) – tabloda F5 hücresinden ayı arayın ve bunun için St. Petersburg toplamını verin (DÜŞEYARA nedir?)
Bu tür bağlantılar, sekmede seçim yapılarak pivot tablolar oluşturulurken başarıyla kullanılabilir. Ekle – Özet Tablo (Ekle – Özet Tablo) ve veri kaynağı olarak akıllı tablonun adını girerek:
Böyle bir tablonun bir parçasını seçerseniz (örneğin, ilk iki sütun) ve herhangi bir türde bir diyagram oluşturursanız, yeni satırlar eklerken bunlar otomatik olarak diyagrama eklenir.
Açılır listeler oluştururken, akıllı tablo öğelerine doğrudan bağlantılar kullanılamaz, ancak bir taktik hilesi kullanarak bu sınırlamayı kolayca aşabilirsiniz - işlevi kullanın DOLAYLI (DOLAYLI)metni bir bağlantıya dönüştüren:
Şunlar. metin dizesi (tırnak işaretleri içinde!) biçiminde bir akıllı tabloya bağlantı, tam teşekküllü bir bağlantıya dönüşür ve açılır liste normal olarak bunu algılar.
Yöntem 2: Dinamik adlandırılmış aralık
Herhangi bir nedenle verilerinizi akıllı bir tabloya dönüştürmek istenmiyorsa, biraz daha karmaşık, ancak çok daha ince ve çok yönlü bir yöntem kullanabilirsiniz - Excel'de tablomuza atıfta bulunan dinamik bir adlandırılmış aralık oluşturun. Ardından, akıllı bir tabloda olduğu gibi, oluşturulan aralığın adını herhangi bir formülde, raporda, çizelgede vs. özgürce kullanabilirsiniz. Basit bir örnekle başlayalım:
Görev: bir şehir listesine atıfta bulunacak ve yeni şehirler eklerken veya silerken boyut olarak otomatik olarak esneyen ve küçülen dinamik bir adlandırılmış aralık yapın.
Herhangi bir sürümde bulunan iki yerleşik Excel işlevine ihtiyacımız olacak - POICPOZ (KİBRİT) aralığın son hücresini belirlemek için ve INDEX (İNDEKS) dinamik bir bağlantı oluşturmak için.
MATCH kullanarak son hücreyi bulma
KAÇINCI(arama_değeri, aralık, eşleme_türü) – bir aralıkta (satır veya sütun) belirli bir değeri arayan ve bulunduğu hücrenin sıra numarasını döndüren bir işlev. Örneğin, KAÇINCI(“Mart”;A1:A5;0) formülü, sonuç olarak 4 sayısını döndürür, çünkü “Mart” kelimesi A1:A5 sütunundaki dördüncü hücrede bulunur. Son işlev bağımsız değişkeni Match_Type = 0, tam bir eşleşme aradığımız anlamına gelir. Bu argüman belirtilmezse, fonksiyon en yakın en küçük değer için arama moduna geçecektir - bu tam olarak dizimizdeki son dolu hücreyi bulmak için başarıyla kullanılabilecek olan şeydir.
Hilenin özü basittir. KAÇINCI, yukarıdan aşağıya aralıktaki hücreleri arar ve teorik olarak, verilen değere en yakın en küçük değeri bulduğunda durmalıdır. İstenen değer olarak tabloda mevcut herhangi bir değerden açıkça daha büyük bir değer belirtirseniz, KAÇINCI tablonun en sonuna ulaşır, hiçbir şey bulamaz ve son doldurulan hücrenin sıra numarasını verir. Ve buna ihtiyacımız var!
Dizimizde yalnızca sayılar varsa, istenen değer olarak tablodaki herhangi birinden açıkça daha büyük olan bir sayı belirtebiliriz:
Garanti için, Excel'in prensipte çalışabileceği maksimum sayı olan 9E + 307 sayısını (9'nin kuvveti 10 üzeri, yani 307 sıfırlı 9) kullanabilirsiniz.
Sütunumuzda metin değerleri varsa, mümkün olan en büyük sayının eşdeğeri olarak, REPEAT(“i”, 255) yapısını ekleyebilirsiniz - 255 harften oluşan bir metin dizesi “i” - son harfi alfabe. Excel aslında arama yaparken karakter kodlarını karşılaştırdığından, tablomuzdaki herhangi bir metin teknik olarak böyle uzun bir "yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy" satırından "daha küçük" olacaktır:
INDEX kullanarak bir bağlantı oluşturun
Artık tablodaki son boş olmayan öğenin konumunu bildiğimize göre, geriye tüm aralığımızla bir bağlantı oluşturmaya devam ediyor. Bunun için şu işlevi kullanıyoruz:
İNDEKS(aralık; satır_sayısı; sütun_sayısı)
Satır ve sütun numarasına göre aralıktaki hücrenin içeriğini verir, örneğin, önceki yöntemden şehirler ve aylar içeren tablomuzdaki =INDEX(A1:D5;3;4) işlevi 1240'ı verir - içerik 3. satır ve 4. sütundan, yani D3 hücreleri. Yalnızca bir sütun varsa, numarası atlanabilir, yani formül INDEX(A2:A6;3) son ekran görüntüsünde “Samara” değerini verecektir.
Ve tamamen açık olmayan bir nüans vardır: INDEX, her zamanki gibi = işaretinden sonra hücreye girilmez, ancak iki nokta üst üste işaretinden sonraki aralığa yapılan referansın son kısmı olarak kullanılırsa, artık dışarı çıkmaz. hücrenin içeriği, ancak adresi! Böylece, $A$2:INDEX($A$2:$A$100;3) gibi bir formül çıktıda A2:A4 aralığına bir referans verecektir.
İşte burada, listenin sonunu dinamik olarak belirlemek için INDEX'in içine eklediğimiz KAÇINCI işlevi devreye girer:
=$A$2:DİZİN($A$2:$A$100; KAÇINCI(REP(“I”;255);A2:A100))
Adlandırılmış bir aralık oluşturun
Hepsini tek bir bütün halinde paketlemek için kalır. Bir sekme aç formül (Formüller) Ve tıklayın İsim Yöneticisi (İsim Yöneticisi). Açılan pencerede düğmesine tıklayın oluşturmak (yeni), alana aralık adımızı ve formülümüzü girin Aralık (Referans):
tıklamak kalıyor OK ve hazır ürün yelpazesi herhangi bir formülde, açılır listede veya çizelgede kullanılabilir.
- Tabloları ve arama değerlerini bağlamak için DÜŞEYARA işlevini kullanma
- Otomatik olarak doldurulan bir açılır liste nasıl oluşturulur
- Büyük miktarda veriyi analiz etmek için bir pivot tablo nasıl oluşturulur?