Adlandırılmış Aralıklar Excel'de Oluşturma (Adım Adım Bir Kılavuz)

Adın nedir?

Excel elektronik tablolarıyla çalışıyorsanız, bu çok zaman kazandıracak ve verimlilik sağlayabilir.

Bu öğreticide, Excel'de Adlandırılmış Aralıkların nasıl oluşturulacağını ve zaman kazanmak için nasıl kullanacağınızı öğreneceksiniz.

Excel'de Adlandırılan Aralıklar - Bir Giriş

Birisi beni aramak ya da bana danışmak zorundaysa, benim adımı kullanacaktır (erkek öylesine ve çok yüksekte ve kilo ile kaldığını söylemek yerine).

Sağ?

Benzer şekilde Excel'de bir hücreye veya bir hücre aralığına bir ad verebilirsiniz.

Şimdi, hücre referansını (A1 veya A1: A10 gibi) kullanmak yerine, ona atadığınız adı basitçe kullanabilirsiniz.

Örneğin, aşağıda gösterildiği gibi bir veri grubunuz olduğunu varsayalım:

Bu veri kümesinde, Tarih alanına bakmanız gerekiyorsa, formüllerde A2: A11 kullanmanız gerekecektir. Benzer şekilde, Satış Temsilcisi ve Satışlar için B2: B11 ve C2: C11'i kullanmanız gerekecektir.

Birkaç veri noktasına sahip olduğunuzda iyidir, ancak büyük karmaşık veri kümeleri durumunda, verilere atıf yapmak için hücre başvuruları kullanmak zaman alıcı olabilir.

Excel Named Ranges, Excel'deki veri kümelerine başvurmayı kolaylaştırır.

Her veri kategorisi için Excel'de adlandırılmış bir aralık oluşturabilir ve sonra hücre başvuruları yerine bu adı kullanabilirsiniz. Örneğin, tarihler 'Tarih' olarak adlandırılabilir, Satış Temsilcisi verileri 'SatışYolması' olarak adlandırılabilir ve satış verileri 'Satış' olarak adlandırılabilir.

Ayrıca tek bir hücre için bir ad oluşturabilirsiniz. Örneğin, bir hücrede satış komisyon yüzdesi varsa, o hücrenin adını 'Komisyon' olarak verebilirsiniz.

Excel'de Adlandırılmış Aralıkları Yaratmanın Faydaları

Excel'de adlandırılmış aralıkları kullanmanın yararları şunlardır:

Hücre Referansları yerine İsim Kullanın

Excel'de Adlandırılmış Aralıklar oluşturduğunuzda, hücre başvuruları yerine bu adları kullanabilirsiniz.

Örneğin, yukarıdaki veri kümesi için = SUM (C2: C11) yerine = SUM (SATIŞ) kullanabilirsiniz.

Aşağıda listelenen formüllerden bir göz atın. Hücre başvurularını kullanmak yerine, Named Ranges kullandım.

500'den fazla değeri olan satışların sayısı: = COUNTIF (Satış, "> 500")
Tom tarafından yapılan tüm satışların toplamı: = SUMIF (SalesRep, "Tom", Satışlar)
Komisyon, Joe tarafından kazanıldı (satışlar Joe tarafından komisyon yüzdesi ile çarpıldı):
= SUMIF (SalesRep, "Joe", Satışlar) * Komisyon

Bu formüllerin oluşturulması kolay ve anlaşılması kolay (özellikle başkasıyla paylaştığınızda veya kendiniz tekrar ziyaret ettiğinizde) kabul edersiniz.

Hücreleri Seçmek İçin Veri Kümesine Geri Dönmeye Gerek Yok

Excel'de Adlandırılmış Aralıkları kullanmanın bir diğer önemli yararı, geri dönüp hücre aralıklarını seçmenize gerek kalmamasıdır.

Adlandırılmış aralıktan birkaç alfabe yazmanız yeterlidir ve Excel, eşleşen adlandırılmış aralıkları (aşağıda gösterildiği gibi) gösterecektir:

Adlandırılmış Aralıklar Formülleri Dinamik Yapar

Excel'de Adlandırılmış Aralıkları kullanarak, Excel formülleri dinamik hale getirebilirsiniz .

Örneğin, satış komisyonu söz konusu olduğunda% 2,5 değerini kullanmak yerine Adlandırılmış Aralık'u kullanabilirsiniz.

Şimdi, şirketiniz daha sonra komisyonu% 3 artırmaya karar verirse, Sadece İsimlendirilmiş Aralık'ı güncelleyebilirsiniz ve tüm hesaplama yeni komisyonu yansıtacak şekilde otomatik olarak güncellenir.

Adlandırılmış Aralıklar Excel'de Nasıl Oluşturulur

Excel'de Adlandırılmış Aralıklar oluşturmanın üç yolu vardır:

Yöntem # 1 - Tanımlama Adını Kullanma

Adını Tanımla'yı kullanarak Excel'de Adlandırılmış Aralıklar oluşturmanın adımları şunlardır:

Excel'de bir Adlandırılmış Aralık oluşturmak istediğiniz aralığı seçin.
Formüllere gidin -> Adı Tanımla'ya gidin.
Yeni Adı iletişim kutusunda, seçili veri aralığına atamak istediğiniz Adı yazın. Kapsamı tüm çalışma kitabı veya belirli bir çalışma sayfası olarak belirleyebilirsiniz. Belirli bir sayfa seçerseniz, ad diğer sayfalarda kullanılamaz.
Tamam'ı tıklayın.

Bu, bir Adlandırılmış Aralık SALESREP yaratacaktır.

Yöntem # 2: Ad kutusunu kullanma

Bir ad oluşturmak istediğiniz aralığı seçin (başlıkları seçmeyin).
Formül çubuğunun solundaki Ad kutusuna gidin ve Adlandırılmış Aralık oluşturmak istediğinizin adını yazın.
Burada oluşturulan Adın tüm Çalışma Kitabında kullanılacağını unutmayın. Bir çalışma sayfasına kısıtlamak isterseniz, Yöntem 1'i kullanın.

Yöntem 3: Seçimden Oluşturma Seçeneği'ni kullanma

Verileri tablolaştırılmış formda olduğunda ve her bir sütun / satıra adlandırılmış aralık oluşturmak istiyorsanız, bu önerilen yoldur.

Örneğin, aşağıdaki veri setinde hızla üç adlandırılmış aralık (Date, Sales_Rep ve Sales) oluşturmak istiyorsanız, aşağıda gösterilen yöntemi kullanabilirsiniz.

Bir veri grubundan adlandırılmış aralıkları hızla oluşturmak için gerekli adımlar şunlardır:

Tüm veri setini seçin (başlıklar dahil).
Formüllere gidin -> Seçimden Yarat ( Klavye kısayolu - Kontrol + ÜstKrktr + F3 ). 'Seçimden İsim Oluştur' diyalog kutusu açılacaktır.
Seçimden İsim Oluştur diyalog kutusuna başlıkların bulunduğu seçenekleri kontrol edin. Bu durumda, üst sıra yalnızca üst satırda olduğu gibi seçilir. Hem üst hem de sol sütunda başlıklarınız varsa, her ikisini de seçebilirsiniz. Benzer şekilde, verileriniz yalnızca başlıklar sol sütunda olduğunda düzenlenirse, yalnızca Sol Sütun seçeneğini kontrol edin.

Bu, üç Adlandırılmış Aralık oluşturur: Date, Sales_Rep ve Sales.

Başlıkların adlarını otomatik olarak almasını unutmayın. Sözcükler arasında boşluk varsa, alt çizgi ekler (adlandırılmış aralıklarda boşluk olamazsınız).

Adlandırılmış Aralıklar için Excel'de Adlandırma Konvansiyonu

Excel'de Adlandırılmış Aralıklar oluştururken bilmeniz gereken bazı adlandırma kuralları vardır:

Adlandırılmış Aralıkın ilk karakteri bir harf ve alt çizgi karakteri (_) veya ters eğik çizgi (\) olmalıdır. Eğer başka bir şey varsa, bir hata gösterecektir. Kalan karakterler harfler, rakamlar, özel karakterler, nokta veya altçizgi olabilir.
Excel'de hücre başvurularını da temsil eden adları kullanamazsınız. Örneğin, AB1'i bir hücre referansı olduğu için kullanamazsınız.
Adlandırılmış aralıklar oluştururken boşluk kullanamazsınız. Örneğin, Satış Temsilcisini adlandırılmış bir aralık olarak alamazsınız. İki sözcüğü birleştirmek ve bir Adlandırılmış Aralığı oluşturmak istiyorsanız, oluşturmak için bir alt çizgi, nokta veya büyük harf kullanın. Örneğin, Sales_Rep, SalesRep veya SalesRep'e sahip olabilirsiniz.
- Adlandırılmış aralıklar oluştururken, Excel büyük ve küçük harfleri aynı şekilde değerlendirir. Örneğin, adlandırılmış bir SATIŞ aralığı oluşturursanız 'satış' veya 'Satış' gibi başka bir adlandırılmış aralık oluşturamazsınız.
Adlandırılmış Aralık 255 karaktere kadar olabilir.

Excel'de çok fazla adlandırılmış aralık? Endişelenme

Bazen büyük veri kümelerinde ve karmaşık modellerde, Excel'de bir çok Named Ranges oluşturabilirsiniz.

Oluşturduğunuz Named Range'in adını hatırlamazsanız ne olur?

Endişelenmeyin - İşte bazı yararlı ipuçları.

Adlandırılmış Alanların İsimlerinin Alınması

Oluşturduğunuz tüm adlandırılmış aralıkların bir listesini elde etmek için gerekli adımları aşağıda bulabilirsiniz:

Formüller sekmesine gidin.
Tanımlı Adlandırılmış grupta, Formülde Kullan'ı tıklayın.
'Adları Yapıştır'ı tıklayın.

Bu, bu çalışma kitabındaki tüm Adlandırılmış Aralıkların listesini verir. Adlandırılmış bir aralığı kullanmak için (formüllerde veya bir hücrede) çift tıklayın.

Eşleşen Adlandırılmış Aralıkların Görüntülenmesi

Ad hakkında bir fikriniz varsa birkaç başlangıç karakteri yazın ve Excel, eşleşen isimlerden birine damla gösterecektir.

Adlandırılmış Aralıkları Excel'de Düzenleme

Bir Named Range oluşturduysanız, aşağıdaki adımları izleyerek düzenleyebilirsiniz:

Formüller sekmesine gidin ve Ad Yöneticisi üzerine tıklayın.
Adı Yöneticisi iletişim kutusu, bu çalışma kitabındaki tüm Adlandırılmış Aralıkları listeler. Düzenlemek istediğiniz Named Range üzerine çift tıklayın.
Ad Düzenle iletişim kutusunda, değişiklikleri yapın.
Tamam'ı tıklayın.
Adı Yöneticisi iletişim kutusunu kapatın.

Yararlı Adlandırılmış Aralık Kısa Yolları (F3'in Gücü)

İşte, Excel'de Adlandırılmış Aralıklarla çalışırken kullanışlı bazı kullanışlı klavye kısayolları :

Tüm Adlandırılmış Aralıkların bir listesini almak ve Formül'e yapıştırmak için: F3
İsim Yöneticisi Diyalog Kutusu'nu kullanarak yeni bir isim oluşturmak için: Control + F3
Seçimden Adlandırılmış Aralıklar oluşturmak için: Control + Shift + F3

Excel'de Dinamik Adlandırılmış Aralıklar Oluşturma

Şimdiye dek bu öğreticide statik Adlandırılmış Aralıklar oluşturduk.

Bu, Adlandırılmış Aralıkların daima aynı veri kümesine başvurduğu anlamına gelir.

Örneğin, A1: A10 'Satış' olarak adlandırılmışsa, her zaman A1: A10'a bakar.

Daha fazla satış verisi eklerseniz, elle gidip belirtilen aralıktaki başvuruyu güncelleştirmeniz gerekir.

Sürekli genişleyen veri setleri dünyasında, bu işlem çok zaman alıyor olabilir. Her yeni veri aldığınızda, Excel'de İsimlendirilmiş Aralıkları güncelleştirmeniz gerekebilir.

Bu sorunu çözmek için Excel'de otomatik olarak ek verileri hesaba katan ve varolan Adlandırılmış Aralıkta bulunan Dinamik Adlandırılmış Aralıklar oluşturabiliriz.

Örneğin, Örneğin, iki ilave satış veri noktası eklersem, dinamik olarak adlandırılmış bir aralık otomatik olarak A1: A12'ye karşılık gelir.

Bu tür Dinamik Adlandırılmış Aralık Excel INDEX işlevi kullanılarak oluşturulabilir . Adlandırılmış Aralık oluştururken hücre başvurularını belirlemek yerine formülü belirtiriz. Formül, veri eklendiğinde veya silindiğinde otomatik olarak güncellenir.

Excel'de Dinamik Adlandırılmış Aralıkların nasıl oluşturulacağını görelim.

A2 hücresindeki satış verilerine sahip olduğumuzu varsayalım: A11.

Excel'de Dinamik Adlandırılmış Aralıklar oluşturma adımları şunlardır:

Formül sekmesine gidin ve Adı Tanımla'yı tıklayın.
Yeni Adı iletişim kutusuna aşağıdakini yazın:
- İsim: Satışlar
- Kapsam: Çalışma Kitabı
- Aşağıdakileri ifade eder: = $ A $ 2: INDEX ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100, "<>" & ""))
Tamam'ı tıklayın.

Bitti!

Artık, 'Satış' adı ile dinamik bir adlandırılmış aralığınız var. Bu, ona veri eklediğinizde veya veriyi veri tabanından kaldırdığınızda otomatik olarak güncellenir.

Dinamik Adlandırılmış Aralıklar Nasıl Çalışır?

Nasıl çalıştığını açıklamak için, Excel INDEX işlevi hakkında biraz daha bilgi sahibi olmalısınız.

Çoğu kişi, satır ve sütun sayısına dayalı olarak bir listeden bir değer döndürmek için INDEX kullanır.

Fakat INDEX fonksiyonunun başka bir yönü de var.

Etmek için kullanılabilir bir hücre başvurusu geri bir hücre referans bir parçası olarak kullanıldığı zaman.

Örneğin, dinamik bir adlandırılmış aralık oluşturmak için kullandığımız formül şu şekildedir:

= $ A $ 2: INDEX ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100, "<>" & ""))

INDEX ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100, "<>" & "") -> Formülün bu bölümünün bir değer döndürmesi bekleniyor (bu değer, Liste, on madde olduğu düşünülürse).

Bununla birlikte, bir referansın önünde (= $ A $ 2: INDEX ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100, "<>" & "")) kullanılırken, Değeri yerine hücre.

Dolayısıyla, burada = $ A $ 2: $ A $ 11

Satış sütununa iki ek değer eklersek, = $ A $ 2: $ A $ 13 değerini döndürür

Listeye yeni veri eklediğinizde , Excel COUNTIF işlevi boş olmayan hücrelerin sayısını veride döndürür. Bu sayı, listedeki son öğenin hücre başvurusunu almak için INDEX işlevi tarafından kullanılır.

Not:

Bu, yalnızca veride boş hücre yoksa çalışır.
Yukarıdaki örnekte, Adlandırılmış Aralık formülü için çok sayıda hücre (A2: A100) atadım. Bunu veri kümenize göre ayarlayabilirsiniz.

Ayrıca, OFFSET işlevini geçici olduğundan, Excel'de bir dinamik adlandırılmış aralığı oluşturmak için OFFSET işlevini kullanabilirsiniz , yavaş bir Excel çalışma kitabına neden olabilir . INDEX, diğer yandan, yarı geçici, bu Excel'de Dinamik Named Ranges oluşturmak için daha iyi bir seçim yapar.