4.1 Arama ve Başvuru Fonksiyonları

SATIR Fonksiyonu

Bir başvurunun satır numarasını verir. Sözdizimi =SATIR([başvuru]) şeklindedir. Aşağıdaki tabloda D8 hücresine =SATIR(B4) formülü girilirse Excel 4 sonucunu döndürür.

SÜTUN Fonksiyonu

Bir başvurunun sütun numarasını verir. Sözdizimi =SÜTUN([başvuru]) şeklindedir. Aşağıdaki tabloda C3 hücresine =SÜTUN(C3) formülü girilirse Excel 3 sonucunu döndürür.

SATIRSAY Fonksiyonu

Bir aralıkta kaç satır olduğunu döndürür. Sözdizimi =SATIRSAY(dizi) şeklindedir. Aşağıdaki tabloda C8 hücresine =SATIRSAY(A2:B6) formülü girilirse Excel 5 sonucunu döndürür.

SÜTUNSAY Fonksiyonu

Bir aralıkta kaç sütun olduğunu döndürür. Sözdizimi =SÜTUNSAY(dizi) şeklindedir. Aşağıdaki tabloda C8 hücresine =SÜTUNSAY(A2:D4) formülü girilirse Excel 4 sonucunu döndürür.

KAÇINCI Fonksiyonu

KAÇINCI fonksiyonu, bir dizide aranan terimin kaçıncı sırada olduğunu buldurur. Bu fonksiyonun söz dizimi =KAÇINCI(aranan_değer;aranan_dizi;[eşleştir_türü] biçimindedir. Bileşenlerinin anlamları aşağıda verilmiştir:

aranan_değer: Bulmak istediğiniz değer

aranan_dizi: Aranacak hücre dizisi (tek satır ya da tek sütun)

eşleştir_türü:

• 0 → Tam eşleşme
• 1 → Küçük veya eşit en büyük değer (artan sıralı liste)
• -1 → Büyük veya eşit en küçük değer (azalan sıralı liste)

Örnek: Aşağıdaki listede bazı ürünler ve fiyatları verilmiştir. Ürün listesinde cetvelin kaçıncı ürün olduğunu bulalım.

1. Önce Excel sayfanızda yukarıdaki tabloyu oluşturun.
2. D4 hücresinde ismi yazılı olan ürünün A2:A7 aralığında kaçıncı sırada olduğunu bulmak için F4 hücresine =KAÇINCI(D4;A2:A7;0) formülünü girin. Cetvel A2:A5 aralığında verilen listede 5.ürün olduğundan Excel 5 sonucunu döndürür.

İNDİS Fonksiyonu

Belirtilen satır ve sütun numarasına göre, bir aralıktan veri getirir. Sözdizimi =İNDİS(dizi; satır_sayısı; [sütun_sayısı]) şeklindedir. Bileşenlerinin anlamları aşağıda verilmiştir:

• dizi: Verilerin bulunduğu alan (tablo)
• satır_sayısı: Kaçıncı satırdan veri alınacak
• sütun_sayısı: Kaçıncı sütundan veri alınacak

Örnek: Aşağıda bazı eşyaların satış adetleri ile birim fiyatları verilmiştir. Bu tabloda ürün bilgilerinin yer aldığı A2:C8 aralığının 4.satırında ve 3.sütununda yer alan veriyi İNDİS fonksiyonunu kullanarak çağıralım.

1. Önce Excel sayfanızda yukarıdaki tabloyu oluşturun.
2. A2:C8 aralığında 4.satırda ve 3.sütundaki veriyi D10 hücresine yazdıralım. Bunun için D10 hücresine =İNDİS(A2:C8;4;3) formülünü girin. Excel 18000 sonucunu döndürecektir. Çünkü A2:C8 aralığının 4.satırı ile 3.sütununun kesiştiği yerde fırının birim fiyatı yani 18000 bulunmaktadır.

Örnek: Aşağıdaki tabloda bir iş yerindeki personelin boy, yaş ve kilo bilgileri verilmiştir. Önce E sütununda Vücut Kitle Endeksi değerlerini VKİ= Kilo / Boy uzunluğunun karesi formülünü kullanarak hesaplayalım. Sonra Veri doğrulama yöntemiyle A14 hücresine personel isim listesini getirelim ve listeden bir isim seçelim. Sonra da INDIS ve KAÇINCI formüllerini birlikte kullanarak B2, C2, D2 ve E2 hücrelerine tablodaki ilgili bilgileri çekelim.

1. Önce Excel sayfanızda yukarıdaki tabloyu oluşturun.
2. Vücut Kitle Endeksi değerlerini bulmak için E2 hücresine =D2/B2^2 formülünü girin.
3. E2 hücresinde formülü E3:E11 aralığına kopyalayın. E3:E11 aralığındaki sonuçları Sayı komut grubundan virgülden sonra iki rakam olacak şekilde düzenleyin.
4. B14 hücresine =İNDİS(A1:E11;KAÇINCI(A14;A1:A11;0);KAÇINCI(B13;A1:E1;0)) formülünü girin. Bu formülde KAÇINCI(A14;A1:A11;0) bileşeni, A14 hücresindeki ismin A1:A11 aralığında kaçıncı sırada olduğunu tam eşleşmeyle bulur, KAÇINCI(B13;A1:E1;0) bileşeni ise B13 hücresindeki Boy sözcüğünün A1:E1 aralığında kaçıncı sırada olduğunu tam eşleşmeyle bulur. Boy sözcüğünün 2.sütunda olduğu açıkça görüldüğü için KAÇINCI fonksiyonunu kullanmadan son bileşen yerine 2 sayısı da yazılabilir. Örneğin A14 hücresine listeden Fatih Derin ismini seçelim. Fatih Derin A1:A11 aralığında 5.sırada olduğundan KAÇINCI(A14;A1:A11;0) bileşeninin değeri 5 olur. Böylece A1:E11 aralığındaki tablonun tamamını tarayan İNDİS fonksiyonu 5.satırdaki Fatih Derin’in 2.sütundaki Boy uzunluğunu döndürür.
5. C14 hücresine =İNDİS(A1:E11;KAÇINCI(A14;A1:A11;0);KAÇINCI(C13;A1:E1;0)) formülünü girin. Böylece A14 hücresinde ismi olan personelin Yaşı bulunur.
6. D14 hücresine =İNDİS(A1:E11;KAÇINCI(A14;A1:A11;0);KAÇINCI(D13;A1:E1;0)) formülünü girin. A14 hücresinde ismi olan personelin Kilosu bulunmuş olur.
7. E14 hücresine =İNDİS(A1:E11;KAÇINCI(A14;A1:A11;0);KAÇINCI(E13;A1:E1;0)) formülünü girin. A14 hücresinde ismi olan personelin Vücut Kitle Endeksi bulunmuş olur.
8. Birbirine çok benzeyen bu formülleri tekrar tekrar yazmak yerine şöyle bir izleyebilirsiniz. İlk formülü yazdığınız B14 hücresine =İNDİS($A$1:$E$11;KAÇINCI($A$14;$A$1:$A$11;0);KAÇINCI(B13;$A$1:$E$1;0)) formülünü girin. Bu formülde B13 hücresi dışındaki tüm adreslerin sabitlendiğine dikkat edin. Formül C14:E14 aralığına kopyalanırken, formüldeki B13 ifadesi C13, D13, E13 olarak değişecek fakat diğer adresler sabit kalacaktır.
9. B14 hücresindeki formülü C14:E14 aralığına kopyalayın.

Tablonun son durumu aşağıdaki gibi olmalıdır.

ARA Fonksiyonu

ARA fonksiyonu, tek bir satır veya sütundaki bir değeri bulur ve bunu farklı bir satır veya sütunda aynı konumdaki bir değerle eşleştirir. Sözdizimi =ARA (aranan_değer;aranan_vektör;sonuç_vektör) biçimindedir.

Örnek: Aşağıdaki tabloda bir işyerindeki bazı çalışanların departmanları ve bu kişilerin yaşları verilmiştir. E2 hücresinde ismi yazılı olan Meryem’e ait yaş bilgisini tablodan bulalım.

Formül, E2 hücresindeki “Meryem” değerini kullanır ve arama vektöründe (A sütununda) “Meryem”i bulur. Formül daha sonra sonuç vektöründe (Sütun C) aynı satırdaki değeri eşleştirir. Ancak ARA fonksiyonunun düzgün çalışması için tablodaki verilerin C sütunundaki Yaş değerlerine göre sıralanmış olması gerekmektedir. Biz burada ilk olarak ARA formülünü aşağıdaki gibi sıralama yapmadan çalıştıralım.

1. Burada aranan değer “Meryem (E2)”, bu değeri aradığımız vektör isimlerin yer aldığı A2:A5 aralığı ve sonuç vektörü olarak yaş değerlerini aradığımız vektör C2:C5 aralığı olduğundan ARA fonksiyonunu F7 hücresine =ARA(E2;A2:A5;C2:C5) biçiminde yazın.
2. Tablodaki veriler yaşa göre sıralı olmadığından ARA formülü “Meryem”in yaşını yanlış göstermektedir. Burada formülün “Meryem”in yaşını yanlış döndürmesinin sebebi verilerin yaşa göre sıralı olmamasıdır.

Şimdi verileri yaşa göre sıralayıp ARA formülünü yeniden çalıştıralım.

1. A2:C5 aralığını işaretleyin.
2. Veri menüsünün altından “Sırala” tuşuna tıklayın.
3. Sırala iletişim kutusundan “Sıralama Ölçütü” açılır listesinden “Yaş” seçeneğini işaretleyip “Tamam”a tıklayın.
4. Bu durumda F7 hücresine yazmış olduğunuz  =ARA(E2;A2:A5;C2:C5) formülünün sonucu şekildeki gibi 28’den 22’ye dönüştü, yani formül “Meryem”in yaşını doğru buldu.

Son durumda tablonuz aşağıdaki gibi olmalıdır.

DÜŞEYARA Fonksiyonu

Arama fonksiyonlarından olan DÜŞEYARA; veriler, sütunlar hâlinde listelendiğinde kullanılır. Bu fonksiyon, en soldaki sütunda bir değer arar ve bu değeri aynı satırdaki ve belirli bir sütundaki veriyle eşleştirir. Sıralanmış veya sıralanmamış bir tablodaki verileri bulmak için DÜŞEYARA fonksiyonunu kullanabilirsiniz.

Bu fonksiyonun sözdizimi =DÜŞEYARA(aranan_değer;tablo_dizisi;sütun_indis_sayısı;[aralık_bak]) biçiminde olmalıdır. Sözdiziminden de görüleceği üzere, DÜŞEYARA fonksiyonunun dört bağımsız değişkeni vardır:

• Aramak istediğiniz değere aranan_değer denir.
• Tablo_dizisi, arama değerlerinin ve fonksiyon tarafından döndürülmesini istediğiniz değerlerin bulunduğu aralıktır. DÜŞEYARA fonksiyonunun doğru çalışması için arama değeri her zaman tablo dizisinin en solundaki ilk sütunda olmalıdır.
• Sütun_indis_sayısı, döndürülecek değeri içeren aralıktaki sütun numarasıdır.
• Aralık_bak yerine, yaklaşık bir eşleşme istiyorsanız isteğe bağlı olarak DOĞRU ya da 1, dönüşün tam bir eşleşmesini istiyorsanız YANLIŞ ya da 0 yazabilirsiniz. Bunu boş bırakırsanız varsayılan değer her zaman DOĞRU yani yaklaşık eşleşme olacaktır.

Örnek 1: Aşağıda bazı eşyaların satış adetleri ile birim fiyatları verilmiştir. Bu tablodan Tablet ürününe ait birim fiyatı bulduralım.

1. İNDİS fonksiyonunda oluşturduğunuz yukarıdaki Excel tablosunu açın.
2. E2 hücresine “Aranan ürün:” ve F2 hücresine “Birim fiyatı:” yazın.
3. E3 hücresinde Veri doğrulama yoluyla açılır ürün listesi oluşturun. Bunun için Veri menüsünün altındaki Veri doğrulama açılır listesinden Veri doğrulama seçeneğine tıklayın. İzin verilen açılır listesinden Liste seçeneğini seçin. Ardından Kaynak kutucuğuna tıklayıp çalışma sayfanızdan A2:A8 aralığını seçin. Tamam’ tıklayın.
4. E3 hücresinde oluşan açılır ürün listesinden Tablet’i işaretleyin.
5. F3 hücresine =DÜŞEYARA(E3;A1:C8;3;0) formülünü yazın. Bu formül; E3 hücresindeki ürünü A1:C8 tablosunun ilk sütununda arar ve 3.sütunda aynı ürüne ait birim fiyatı tam eşleşmeyle (0) getirir. Eğer biz ürünün birim fiyatı yerine satış adedi bilgisini çağırmış olsaydık, formüldeki 3 rakamı yerine 2 yazacaktık, çünkü satış adedi bilgileri A1:C8 aralığındaki tablonun 2.sütununda yer almaktadır.
6. Tablonuzun son durumu yukarıdaki gibi olmalıdır. Eğer E3 hücresindeki açılır listeden başka bir ürün seçerseniz, formül F3 hücresine o ürüne ait fiyatı getirecektir.
7. Tablodaki bir ürünün adının yazımında yazım yanlışı yapılmışsa DÜŞEYARA formülü bu ürünü nasıl bulur? E3 hücresinde veri doğrulama yöntemiyle liste oluşturmadığınızı, aradığınız ürünün adını klavyeden manuel olarak girdiğinizi düşünelim. Örneğin E3 hücresinde “Fırın” yerine “Fırınnn” yazılmış olsun. Bu durumda formülün Fırın’a ait birim fiyatı doğru getirmesi için tam eşleşme yerine yaklaşık eşleşme kullanılmalı, yani en sona yazılan 0 yerine 1 yazılmalıdır.
8. F3 hücresine yazılan formül =DÜŞEYARA(E3;A1:C8;3;1) olursa ismi yanlış yazılan ürünlerin fiyatları da aşağıdaki gibi düzgün gelebilir.

Örnek 2: Aşağıdaki tablonun solunda bazı temizlik ürünlerinin birim fiyatları ile bu ürünleri satın alan müşterilere uygulanan indirim oranları verilmiştir. Bu verilerdeki birim fiyatları ürün adlarını kullanarak sağdaki tabloda F sütunundaki hücrelere, indirim oranlarını ise müşteri adlarını kullanarak H sütunundaki hücrelere DÜŞEYARA fonksiyonunu kullanarak çağıralım. Sonrasında alınan ürünlerin tutarlarını ve indirimli fiyatlarını hesaplayalım.

1. Önce Excel sayfanızda yukarıdaki tabloyu oluşturun.
2. Birim fiyatları çağırmak için F2 hücresine =DÜŞEYARA(D2;$A$1:$B$8;2;0) formülünü girin. DÜŞEYARA fonksiyonunda aradığımız temizlik ürününün adını arama yaptığımız A1:B8 aralığından seçmeyiz. Dolayısıyla formülde aranan değer olarak A2 hücresini değil, D2 hücresini kullandık. Formüle göre D2 hücresinde ismi yazılı olan ürünü, A1:B8 aralığının birinci sütununda bulup 2.sütunda karşılığı olan fiyatı çağırdık. A1:B8 aralığını niçin $A$1:$B$8 biçiminde sabitledik. Çünkü, birim fiyatlar F sütunundaki diğer hücrelere kopyalanırken verilerin sadece A1:B8 aralığından okunması gerekir.
3. F2 hücresindeki formülü F3:F18 aralığına kopyalayın.
4. G2 hücresine =E2*F2 formülünü yazıp A firmasının aldığı 10 adet temizlik süngerinin tutarını hesaplayın.
5. G2 hücresindeki formülü G3:G18 aralığına kopyalayın.
6. İndirim oranlarını çağırmak için H2 hücresine =DÜŞEYARA(J2;$A$10:$B$14;2;0) formülünü girin. Formüle göre J2 hücresinde ismi yazılı olan müşteri, A10:B14 aralığının birinci sütununda bulup 2.sütunda karşılığı olan indirim oranını çağırdık.
7. H2 hücresindeki formülü H3:H18 aralığına kopyalayın. H sütunundaki veriler seçiliyken 10% biçiminde gelmesini istediğiniz indirim oranı 0,1 biçiminde gelmişse, Giriş menüsünün Sayı komut grubundaki % sitiline tıklayın.
8. İndirimli fiyatları bulmak için I2 hücresine =G2-G2*H2 formülünü girin.
9. I2 hücresindeki formülü I3:I18 aralığına kopyalayın.

Tablonuzun son durumu aşağıdaki gibi olmalıdır.

Örnek 3: Aşağıdaki tabloda bazı öğrencilerin bir dersten aldıkları notlar verilmiştir. Bu öğrencilerin notlarını yanda verilen not aralıklarından faydalanarak harf notuna çevirelim.

1. Önce Excel sayfanızda C sütunu boş olacak şekilde aşağıdaki tabloyu oluşturun. F sütunundaki değerlerin harf notu aralıklarının alt sınırı olduğuna dikkat edin.
2. C2 hücresine =DÜŞEYARA(B2;$F$2:$G$10;2;1) formülünü girin. Bu formül, B2 hücresindeki Kemal Tekin’e ait nottan küçük ya da bu nota eşit olan notu, F sütunundaki not değerleri içinde arar. F sütununda 79 sayısı yok. Dolayısıyla formül, yaklaşık eşleşme kullandığımız için 79’dan küçük olan en büyük notu yani 70’i bulur ve F2:G10 aralığının 2.sütunundaki karşılığı olan CB sonucunu döndürür.
3. C2 hücresindeki formülü C3:C15 aralığına kopyalayın.
4. Eğer formülde tam eşleşme yani DOĞRU (1) yerine YANLIŞ (0) kullansaydık sadece notu F sütununda bulunan öğrencilerin harf notları gelecekti.

Tablonun son durumu aşağıdaki gibi olmalıdır.

DOLAYLI Fonksiyonu

Excel’de DOLAYLI fonksiyonu, bir hücre adresini metin olarak alır ve bu metni gerçek bir başvuruya (hücre adresine veya aralığa) dönüştürür. Bu sayede dinamik başvurular yapabilirsiniz. Sabit hücre adresleri yerine, hücredeki değere göre başka hücrelere ulaşmak mümkün olur. Bu fonksiyonun sözdizimi =DOLAYLI(başv_metni; [a1]) biçimindedir.

Örnek 1: Diyelim ki A1 hücresine 5 yazdınız, B1 hücresine A1 yazdınız ve C1 hücresine de =DOLAYLI(B1) formülünü yazdınız. Böylece B1 hücresinden A1 hücresini çağırmış oldunuz. Excel C1 hücresine A1 hücresindeki 5 sayısını döndürür. C1 hücresine =A1 yazdığınızda da aynı sonuç gerçekleşir.

Örnek 2: Diyelim ki Ocak ve Şubat adında iki çalışma sayfanız var. Ocak çalışma sayfasında A1 hücresinde 500 yazılı olsun. Şubat çalışma sayfasında C1 hücresine =DOLAYLI(“Ocak!A1”) formülünü yazarsanız, Excel 500 sonucunu döndürür.

Örnek 3: Excelde ADRES fonksiyonu satır ve sütun numarası verilen bir hücrenin adresini döndürür. Örneğin A1 hücresine =ADRES(4;2) formülü yazılırsa 4.satırla 2.sütunun kesiştiği yer B4 hücresi olduğundan Excel hücrenin adresini $B$4 şeklinde döndürür. Eğer B4 hücresinde 12 sayısı yazılıysa ve bu durumda örneğin C2 hücresine =DOLAYLI(ADRES(4;2)) formülü yazılırsa Excel verilen adresteki sayıyı yani 12’yi döndürür.

Örnek 4: Aşağıdaki tabloda 14 öğrencinin Matematik, fizik, kimya ve biyoloji derslerinden aldıkları notlar verilmiştir. DOLAYLI fonksiyonunu kullanarak I2 hücresine herhangi bir öğrencinin herhangi bir dersteki notunu çağıralım.

1. G2 hücresinde veri doğrulama yöntemiyle A2:A15 aralığındaki öğrencilerin listesini ve H2 hücresinde B1:E1 aralığındaki derslerin listesini oluşturun. Öğrenci listesinden şekildeki gibi Fatih Derin adlı öğrenciyi ve ders listesinden Kimya dersini seçin.
2. I2 hücresine =DOLAYLI(ADRES(KAÇINCI(G2;A2:A15;0)+1;KAÇINCI(H2;B1:E1;0)+1)) formülünü girin. Bu formülde G2 deki öğrencinin A2:A15 aralığında kaçıncı sırada olduğu ve H2 deki dersin B1:E1 aralığında kaçıncı sırada olduğu bulunur. Notlar hem1.satırdan hem de 1.sütundan başlamıyor. Dolayısıyla sıra numaralarına 1 eklenip ADRES fonksiyonuyla ilgili notun adresi belirlenir. Son olarak DOLAYLI fonksiyonu ilgili adresteki notu döndürür.
3. Listeden öğrenci adlarını ve ders adlarını değiştirerek tüm öğrencilerin her dersine ait not çağırılabilir.

YATAYARA Fonksiyonu

YATAYARA fonksiyonu, verileri yatay olarak aramak için kullanılır. Yani, bir tablonun ilk satırında belirli bir değeri arar ve o değerin bulunduğu sütundan, belirli bir satır numarasındaki değeri getirir. Bu fonksiyonun sözdizimi =YATAYARA(aranan_değer; tablo_dizisi; satır_indis_sayısı; [aralık_bak]) şeklindedir. Burada; aranan_değer bileşeni ilk satırda aramak istediğiniz değerdir, tablo_dizisi bileşeni verilerin yer aldığı tablodur, arama yalnızca ilk satırda yapılır, satır_indis_sayısı aranan değerin bulunduğu sütundan hangi satırdan değer alınacağını belirtir ve son olarak aralık_bak bileşeni yerine ise yaklaşık eşleşme için DOĞRU yani 1, tam eşleşme için YANLIŞ yani 0 yazılır.

Örnek: Aşağıdaki tabloda, Kemal Tekin adlı öğrencinin girmiş olduğu bir sınavdaki farklı derslerden yapmış olduğu net sayıları verilmiştir. B5 hücresinde Veri doğrulama yöntemiyle ders listesini oluşturalım ve C5 hücresine B5 hücresindeki derse ait net sayısını YATAYARA fonksiyonunu kullanarak çağıralım.

1. Önce Excel sayfanızda yukarıdaki tabloyu oluşturun.
2. B5 hücresindeki derse ait net değerini çağırmak için C5 hücresine =YATAYARA(B5;B1:I2;2;0) formülünü girin. Bu formül B5 hücresinde yer alan dersi B1:I2 tablosunda arayıp kaçıncı sütunda olduğunu bulur ve o sütunun 2.satırında yer alan net değerini döndürür.
3. B5 hücresindeki açılır listeden farklı bir ders seçerseniz, C5 hücresinde o derse ait net değeri döndürülür.

Örnek: Aşağıdaki tabloda bazı ürünlerin farklı ödeme şekillerine göre fiyatları verilmiştir. B10 hücresinde A2:A8 aralığındaki ürünleri, B11 hücresinde ise B1:D1 aralığındaki ödeme şekillerini Veri doğrulama yöntemiyle listeleyelim. Sonra seçeceğiniz bir ürünün seçeceğiniz ödeme şekline göre fiyatını B14 hücresinde DÜŞEYARA, B15 hücresinde YATAYARA ve B16 hücresinde İNDİS fonksiyonuyla ayrı ayrı bulalım.

1. Önce Excel sayfanızda yukarıdaki tabloyu oluşturun.
2. B10 hücresindeki listeden çamaşır makinesini ve B11 hücresindeki listeden 5 taksit ödeme şeklini seçin.
3. Önce DÜŞEYARA fonksiyonunu kullanarak B10 hücresindeki ürünün B11 hücresindeki ödeme şekline uygun fiyatını tablodan bulduralım. Bunun için B14 hücresine =DÜŞEYARA(B10;A1:D8;KAÇINCI(B11;A1:D1;0);0) formülünü girin. Bu formüle göre B10 hücresindeki ürünün A1:D8 aralığındaki tablonun hangi satırında olduğu bulunur, sonra hangi sütundaki ödeme şekliyle kesiştiği KAÇINCI fonksiyonu yardımıyla elde edilir. KAÇINCI fonksiyonu B11 hücresindeki ödeme şeklinin A1:D1 aralığında kaçıncı sırada olduğunu bulur.
4. Şimdi aynı sonucu YATAYARA fonksiyonunu kullanarak elde edelim. Bunun için B15 hücresine =YATAYARA(B11;A1:D8;KAÇINCI(B10;A1:A8;0);0) formülünü girin. Bu formüle göre B11 hücresindeki ödeme şeklinin A1:D8 aralığındaki tablonun hangi sütununda olduğu bulunur, sonra hangi satırdaki ürünle kesiştiği KAÇINCI fonksiyonu yardımıyla elde edilir. KAÇINCI fonksiyonu B10 hücresindeki ürünün A1:A8 aralığında kaçıncı sırada olduğunu bulur.
5. Şimdi de aynı sonucu İNDİS fonksiyonunu kullanarak elde edelim. Bunun için B16 hücresine =İNDİS(A1:D8;KAÇINCI(B10;A1:A8;0);KAÇINCI(B11;A1:D1;0)) formülünü girin. Bu formülde A1:D8 ifadesi arama yaptığımız tabloyu, KAÇINCI(B10;A1:A8;0) bileşeni B10’daki ürünün satır numarasını, KAÇINCI(B11;A1:D1;0) bileşeni ise B11’deki ödeme şeklinin sütun numarasını buldurur.
6. B10 ve B11’de yer alan ürün ve ödeme şekli listelerinden farklı bir ürün ve farklı bir ödeme şekli seçin. Yazdığınız fonksiyonların düzgün çalışıp çalışmadığını kontrol edin.

Tablonuzun son durumu aşağıdaki gibi olmalıdır.

ÇAPRAZARA Fonksiyonu

Tablodaki veya aralıktaki öğeleri satır temelinde arayarak bulmak için ÇAPRAZARA işlevi kullanılır. ÇAPRAZARA ile dönüş sütunu hangi tarafta olursa olsun, bir arama verisini bir sütunda arayabilir ve aynı satırdan başka bir sütunda sonuç döndürebilirsiniz. DÜŞEYARA fonksiyonu bir veri tablosunun en solundaki bir hücrede bir değer arıyor, değeri bulunca o değerle aynı satırda ve belli bir sütunda yer alan başka bir değerle eşleşme sağlıyordu. ÇAPRAZARA fonksiyonu ise birden fazla sütunda aradığımız değerler aynı satırda kesiştiklerinde, bu değerleri aynı satırda ve belli bir sütunda yer alan başka bir değerle eşleştiren fonksiyondur. Örneğin aşağıdaki tabloda adı “Hülya” olan birden fazla kişi ve soyadı “Derman” olan birden fazla kişi bulunmaktadır. Biz ise “Hülya Derman” adlı kişinin yaptığı satış adedini bulmak istiyoruz. Bunun için ÇAPRAZARA fonksiyonu kullanılabilir.

ÇAPRAZARA fonksiyonunun söz dizimi =ÇAPRAZARA(aranan_değer; aranan_dizi; döndürülen_dizi; [bulunamadığında]; [eşleştirme_modu]; [arama_modu]) biçimindedir.

Burada ilk 3 değişkenin yazılması zorunlu, diğer 3 değişkenin yazılması ise isteğe bağlıdır. Yine burada aranan dizi, içerisinde aranan değerin bulunacağı dizidir, döndürülen dizi ise sonuç ekranına gelmesini istediğimiz değeri içerisinde barındıran dizidir.

Fonksiyon verilen şarta uygun bir satır bulamadığında ise #YOK değerini döndürür. Bu hata mesajını almak istemiyorsak söz dizimindeki [bulunamadığında] yerine istediğimiz bir mesajı yazabiliriz. Aranan değer ve aranan dizi birden fazla olduğunda aranan değerler arasına ve aranılan diziler arasına formülde & işareti yazılır.

Aşağıdaki veri tablosunda adı F5:G5 aralığında yazılı olan “Hülya Derman”ın yaptığı satış adedine ulaşmak için H5 hücresine =ÇAPRAZARA(F5&G5;B:B&C:C;D:D;”bulunamadı”;0) formülünü yazdık.

F5 hücresine “Hülya” yerine “Esma” yazılırsa bu durumda formül “Esma Derman”ın satış adedi olan 17’yi döndürür. Soyadı yerine “Tekin” yazarsak listede “Esma Tekin” adında kimse olmadığı için formül “bulunamadı” şeklinde bir sonuç döndürür.

ELEMAN Fonksiyonu

ELEMAN fonksiyonu, bir dizideki belirli konumdaki öğeyi döndürmek için kullanılır. Yani bir listedeki n. sıradaki elemanı almanızı sağlar. Çok kullanışlı ve basit bir fonksiyondur. Sözdizimi =ELEMAN(dizin_sayısı; değer1; [değer2]; …) şeklindedir. Burada dizin_sayısı bileşeni, bu sayıdan sonra gelen değer bileşenlerinden kaçıncısının döndürüleceğini gösterir. Örneğin bir hücreye =ELEMAN(3; “Elma”; “Armut”; “Nar”;”Muz”) formülü yazılırsa Excel üçüncü değeri yani Nar’ı döndürür.

Örnek: Aşağıdaki tabloda bazı ürünlere ait renk, kütle, fiyat ve stok adedi bilgileri verilmiştir. ELEMAN fonksiyonu yardımıyla sıra numarası verilen ürüne ait tüm ürün bilgilerini çağıralım.

1. Önce Excel sayfanızda yukarıdaki tabloyu oluşturun.
2. B8 hücresine bir ürünün sıra numarasını girin. Örneğin 4 olsun.
3. Ürünün adını çağırmak için A10 hücresine =ELEMAN($B$8;A2;A3;A4;A5;A6) formülünü girin. Formülü yana doğru kopyalarken ürün sıra numarasının sabit kalması için formüldeki B8 hücresini sabitledik.
4. A10 hücresindeki formülü B10:E10 aralığına kopyalayın. Tablonun son durumu aşağıdaki gibi olmalıdır.
5. B8 hücresindeki sıra numarasını değiştirirseniz farklı bir ürüne ait bilgiler gelecektir. Tabloda olmayan bir sıra numarası verilirse, bu durumda #DEĞER! hatasıyla karşılaşılır.

KAYDIR Fonksiyonu

Excel’de KAYDIR fonksiyonu, başlangıç hücresine göre belli sayıda satır ve sütun kayarak ulaşılan hücreyi veya hücre aralığını döndürür. Çok güçlü ve dinamik bir fonksiyondur; özellikle değişken veri alanları, grafik kaynakları ve ileri düzey formüller oluştururken sıkça kullanılır.

Sözdizimi =KAYDIR(başv; satırlar; sütunlar; [yükseklik]; [genişlik]) biçimindedir. Buradaki başv bileşeni kaymanın başlayacağı hücreyi, satırlar bileşeni yukarı/aşağı kaç satır kayılacağını (yukarı ise negatif), sütunlar bileşeni sola/sağa kaç sütun kayılacağını (sola ise negatif), [yükseklik] bileşeni sonuç aralığının kaç satır olacağını, [genişlik] bileşeni sonuç aralığının kaç sütun olacağını gösterir. Eğer yükseklik ve genişlik değerleri girilmezse varsayılan olarak 1 kabul edilir. Örneğin yükseklik 3 girilip, genişlik girilmezse Excel alt alta bulunan 3 hücredeki verileri döndürür.

Örnek: Aşağıdaki tabloda KAYDIR fonksiyonunu kullanarak E8 hücresine farklı hücrelerdeki verileri çağıralım.

1. KAYDIR fonksiyonu kullanılırken ilk bileşen yerine bir başlangıç hücresi seçilir. Örneğin başlangıç hücremiz, içerisinde Yeşil yazan B3 hücresi olsun. Boş bir hücreyi de başlangıç hücresi olarak seçebilirsiniz. Tabloda, B3 hücresinden 2 birim aşağıya ve 1 birim sola gidildiğinde A5 hücresine ulaşıldığı için E8 hücresine =KAYDIR(B3;2;-1) formülünü yazın. Excel A5’teki Kalemtraş kelimesini döndürecektir. B3 hücresinden 2 birim aşağı ve 1 birim sola gidildiği için formülde ;2;-1 yazıldı.
2. E8 hücresine =KAYDIR(A3;1;4) formülünü yazın. Excel “200” sonucunu döndürecektir. Çünkü A3 hücresinden 1 birim aşağı ve 4 birim sağa gidildiğinde E4 hücresine ulaşılır.
3. E8 hücresine =KAYDIR(D1;5;-2) formülünü yazın. Excel “Sarı” sonucunu döndürecektir. Çünkü D1 hücresinden 5 birim aşağı ve 2 birim sola gidildiğinde B6 hücresine ulaşılır.
4. E8 hücresine =KAYDIR(A1;3;0) formülünü yazın. Excel “Silgi” sonucunu döndürecektir. Çünkü A1 hücresinden 3 birim aşağı gidip sağa veya sola gidilmediğinde A4 hücresine ulaşılır.
5. E8 hücresine =KAYDIR(E1;3;2) formülünü yazın. Excel 0 sonucunu döndürecektir. Çünkü E1 hücresinden 3 birim aşağı ve 2 birim sağa gidildiğinde G4 hücresine ulaşılır. Bu hücre boş olduğundan Excel 0 sonucunu döndürür.
6. E8 hücresine =KAYDIR(A1;3;-2) formülünü yazın. Excel #BAŞV! hatası döndürecektir. Çünkü A1 hücresinden 3 birim aşağı ve 2 birim sola gidildiğinde herhangi bir hücre yoktur.
7. E8 hücresine =KAYDIR(C1;3;-2;3;2) formülünü yazın. Excel A4:B6 aralığındaki altı hücrede yer alan 6 tane veriyi E8:F10 aralığına döndürecektir. Çünkü C1 hücresinden 3 birim aşağı ve 2 birim sola gidildiğinde A4 hücresine ulaşılır. A4 hücresinden başlanarak yüksekliği 3 birim ve genişliği 2 birim olan bölge A4:B6 aralığı olduğundan bu aralıktaki verilerin tamamı E8:F10 aralığına gelir.

Örnek: Aşağıdaki tabloda bir işyerindeki personelin boy yaş ve kilo bilgileri verilmiştir.

1. B14, C14 ve D14 hücrelerine personel listesinden seçeceğimiz kişinin sırasıyla boy, yaş ve kilo değerlerini KAYDIR ve KAÇINCI fonksiyonlarını kullanarak çağıralım.
2. B17, C17 ve D17 hücrelerinde tüm personelin sırasıyla boy, yaş ve kilo ortalamalarını ORTALAMA, KAYDIR, KAÇINCI ve BAĞ_DEĞ_SAY fonksiyonlarını kullanarak hesaplayalım.
3. B17, C17 ve D17 hücrelerinde ise sırasıyla tüm personel içindeki en uzun boy, en büyük yaş ve en yüksek kilo değerlerini MAK, KAYDIR, KAÇINCI ve BAĞ_DEĞ_SAY fonksiyonlarını kullanarak hesaplayalım.

1. İNDİS fonksiyonunu anlatırken kullandığımız verileri kullanarak yukarıdaki tabloyu Excel sayfanızda oluşturun. Alt kısımdaki sayısal değerlerin bulunduğu hücreler boş kalsın.
2. A14 hücresinde Veri Doğrulama yöntemiyle personel açılır listesini oluşturun. Listeden örneğin Murat Öztürk adlı personeli seçin. Önce etkinliğin a seçeneğinden başlayın.
3. Seçilen kişinin boy uzunluğunu çağırmak için B14 hücresine =KAYDIR($A$1;KAÇINCI($A$14;$A$2:$A$11;0);KAÇINCI(B13;$B$1:$D$1;0)) formülünü girin. Bu formüle göre A1 hücresinden başlanarak; A14 hücresindeki personel A2:A11 aralığında kaçıncı sıradaysa o kadar satır aşağı, B13 hücresindeki Boy ifadesi B1:D1 aralığında kaçıncı sıradaysa o kadar sütun sağa gidilecektir. Örnekteki Murat Öztürk A2:A11 aralığında 3. sırada ve Boy kelimesi B1:D1 aralığında 1. sırada olduğundan A1 hücresinden başlanıp 3 birim aşağı ve 1 birim sağa gidildiğinde Murat Öztürk’ün boy uzunluğuna ulaşılır. KAYDIR fonksiyonunda başlangıç hücresi, KAÇINCI fonksiyonunda A14’teki aranan kişi ve aranılan A2:A11 aralığı değişmediği için bu hücreleri sabitledik. Ancak ikinci olarak kullandığımız KAÇINCI fonksiyonunda B13 hücresini sabitlemediğimize dikkat edin. Çünkü B14 hücresindeki formül; yaş ve kilo değerlerini gösteren C14 ve D14 hücrelerine kopyalanırken B13’teki Boy kelimesi sabit kalamaz. C13 ve D13’teki Yaş ve Kilo kelimelerine geçiş yapmalıdır.
4. Şimdi etkinliğin b seçeneğine geçin.
5. B17 hücresinde tüm personelin boy ortalamasını bulmak için bu hücreye =ORTALAMA(KAYDIR($A$1;1;KAÇINCI(B13;$B$1:$D$1;0);BAĞ_DEĞ_SAY(B2:B11))) formülünü girin.
6. Bu formülde, KAYDIR fonksiyonuyla A1 hücresi başlangıç alınıp 1 birim aşağı ve B13’teki Boy kelimesi B1:D1 aralığında 1.sırada olduğundan 1 birim sağa yani B2 hücresine gidildi. (Boy kelimesinin sırası KAÇINCI fonksiyonuyla belirlendi.) BAĞ_DEĞ_SAY fonksiyonuyla KAYDIR fonksiyonundaki Yükseklik bileşeni belirlendi. ORTALAMA fonksiyonuyla da Yükseklik içine giren 10 değerin ortalaması alındı.
7. B17 hücresindeki formülü C17:D17 aralığına kopyalayın. Böylece tüm personelin yaş ve kilo ortalamaları bulunmuş olur.
8. Şimdi etkinliğin c seçeneğine geçin. B20 hücresinde tüm personel içindeki en uzun boyu bulmak için bu hücreye =MAK(KAYDIR($A$1;1;KAÇINCI(B13;$B$1:$D$1;0);BAĞ_DEĞ_SAY(B2:B11))) formülünü girin. Bu formülün önceki formülden tek farkı ORTALAMA fonksiyonu yerine MAK fonksiyonunun kullanılmasıdır. Yine KAYDIR, KAÇINCI ve BAĞ_DEĞ_SAY fonksiyonları kullanılarak B2:B11 aralığındaki boy değerleri elde edildi. MAK fonksiyonuyla bu değerlerin en büyüğü bulundu.
9. B20 hücresindeki formülü C20:D20 aralığına kopyalayın. Böylece tüm personel içindeki en büyük yaş ve en yüksek kilo değerleri de bulunmuş olur.

Tablonuzun son durumu yukarıdaki gibi olmalıdır.

Kaydır Fonksiyonu ile Dinamik Grafik Oluşturma

Excel’de verileri analiz etmek kadar, bu verileri görsel hale getirmek de oldukça önemlidir. Ancak bazı durumlarda sabit grafikler yerine, kullanıcı seçimine göre değişebilen dinamik grafiklere ihtiyaç duyulur. İşte bu noktada, Excel’in güçlü araçlarından biri olan KAYDIR fonksiyonu kullanılır.

KAYDIR fonksiyonu, belirli bir başlangıç hücresinden itibaren satır ve sütun sayısına göre başka bir hücre ya da hücre aralığına ulaşmanızı sağlar. Bu esneklik, grafiklerde kullanılan veri kaynaklarını dinamik hâle getirmede büyük avantaj sunar. Kullanıcı farklı bir değer girdiğinde grafik, otomatik olarak yeni verileri gösterir.

Örnek: Aşağıdaki tabloda bazı büyük şehirlere ait yüzey alanı, nüfus ve yıllık ortalama sıcaklık değerleri yer almaktadır. Bu şehirlerin bu bilgilerini ayrı grafikler yerine tek grafik üzerinde dinamik olarak göstermek için KAYDIR fonksiyonunu kullanalım.

1. Önce Excel sayfanızda yukarıdaki tabloyu oluşturun. Veri Doğrulama aracındaki liste özelliğini kullanarak G2 hücresinde B1:D1 aralığındaki grafik içeriği özellikleri için bir liste oluşturun. Açılır listeden örneğin Yıllık Ortalama Sıcaklık (°C) özelliğini seçin.
2. Grafiğin yatay ekseninde yer alacak olan şehir adlarını çağırmak için F3 hücresine =KAYDIR($A$1;1;0;10) formülünü girin. Bu formüldeki $A$1 ifadesi, A1 hücresi başlangıç noktası kabul edildiği, 1 bileşeni şehir adlarının A1 hücresinin 1 hücre altından başladığını, 0 bileşeni sağa gidilmeyip A sütununda kalınacağını, 10 bileşeni ise veri yüksekliğini yani 10 şehir olduğunu göstermektedir.
3. Tablodaki değerlerden uygun olanları grafiğe alabilmek için G3 hücresine =KAYDIR($A$1;1;KAÇINCI($G$2;$B$1:$D$1;0);10) formülünü girin. Bu formüldeki $A$1 ifadesi, yine A1 hücresinin başlangıç noktası kabul edildiğini, 1 bileşeni A1 hücresinden 1 hücre aşağıya kayılacağını, KAÇINCI($G$2;$B$1:$D$1;0) fonksiyonu G2 hücresindeki özelliğin B1:D1 aralığında kaçıncı sırada olduğunu bulup sağa kaç birim kayılacağını gösterir. Örneğin G2 hücresinin içeriği Yüzey Alanı (km²) ise KAÇINCI fonksiyonunun sonucu 1, Yıllık Ortalama Sıcaklık (°C) ise KAÇINCI fonksiyonunun sonucu 3 olacaktır. KAYDIR fonksiyonundaki son bileşen olan 10 sayısı ise tablodan alınan veri yüksekliğinin 10 satır olacağını, yani tablodan 10 tane veri alınacağını gösterir.
4. Formüllerle bulunan değerleri grafiğe aktarmak için Formüller sekmesinin altında yer alan Ad Yöneticisi düğmesi kullanılır. Şehir adlarından oluşan veri grubunu aktarmak için F3 hücresine tıklayın, formül satırındaki formülü seçip CTRL+C ile kopyalayın. ESC ile çıkın ve Formüller sekmesinin altındaki Ad yöneticisine tıklayın. Açılan pencerede Yeni düğmesine tıklayın. Yine açılan pencerede Ad kutucuğuna Şehirler yazın. Başvuru yeri kutucuğuna ise mevcut yazıyı silip hafızaya aldığınız formülü CTRL+V ile yapıştırın. Aynı yöntemle grafik içeriği verilerini aktarmak için G3 hücresine tıklayın, formül satırındaki formülü seçip CTRL+C ile kopyalayın. ESC ile çıkın ve Formüller sekmesinin altındaki Ad yöneticisine tıklayın. Açılan pencerede Yeni düğmesine tıklayın. Yine açılan pencerede Ad kutucuğuna Değerler yazın. Başvuru yeri kutucuğuna ise mevcut yazıyı silip hafızaya aldığınız formülü CTRL+V ile yapıştırın. Tamam’ tıkladığınızda Ad Yöneticisi penceresi aşağıdaki gibi görünmelidir.

Hem şehir isimlerini hem de değerleri Şehirler ve Değerler’e yüklediğiniz için dilerseniz sayfanızdaki F3:G12 aralığındaki verileri silebilirsiniz.

1. Sayfanıza Ekle menüsünden bir sütun grafik ekleyin. Bu grafik alanını A13:G27 aralığına yayın. Grafik alanının üzerine sağ tıklayıp açılan listeden Veri Seç ögesine tıklayın. Açılan Serileri Düzenle penceresinde Seri adı kutucuğuna Değerler yazın. Seri değerleri kutucuğuna tıklayın ve içeriğini silin, sayfanın alt kısmından sayfa adına tıklayın, gelen ifadenin devamına Şehirler yazın ve Tamam’a tıklayın. (örneğin sayfanızın adı Sayfa72 ise, içerik =Sayfa72!Şehirler biçiminde olmalı).
2. Yatay (Kategori) Eksen Etiketleri kısmındaki Düzenle düğmesine tıklayın. Açılan penceredeki Eksen etiketi aralığı kutucuğu için yine sayfanın alt kısmından sayfa adına tıklayın ve bu defa devamına Değerler yazıp Tamam’a tıklayın. (içerik =Sayfa72!Değerler biçiminde olmalı). Tekrar Tamam’a tıklayıp pencereyi kapatın.
3. Bu durumda G2 hücresindeki Grafik İçeriği başlığını değiştirdiğinizde grafiğin dinamik olarak değiştiğini göreceksiniz.
4. Grafik üzerinde bazı biçimlendirme işlemleri yapmak için önce grafik alanına sonra grafiğin sağ üst kısmındaki + sembolüne sol tıklayın. Veri Etiketleri kutucuğunu aktifleştirin, kılavuz çizgilerini kaldırın. Grafiğin hem sol kısmında hem de sütunlar üzerinde sayılar olduğu için sol sütundaki dikey eksen verilerini silin. Şehir adlarının yazılı olduğu yatay eksen verilerine tıklayın, punto büyüklüğünü 1 artırıp, kalın yapın. Son olarak sütunların üzerinde yazan veri etiketlerinden birine tıklayın, iç dolgu rengini sarı yapın, punto büyüklüğünü 1 artırın.
5. Şimdi de grafik başlığını aktif hale getirin. Bunun için sayfadaki boş bir hücreye =”Şehirlerin “&G2&” Değerleri” formülünü yazın. Formülü yazacağınız boş hücreyi grafiğin altına gelen bir hücreden seçebilirsiniz. Örneğin B20 olsun. Bu formül sayesinde G2 hücresindeki veriler değiştirildiğinde başlık da dinamik olarak değişir.
6. Grafiğin üzerindeki grafik başlığına tıklayın. Bir önceki adımda formülü B20 hücresine yazdıysanız, sayfanın üst kısmındaki formül çubuğuna =Sayfa72!$B$20 formülünü girin. Grafik başlığını seçip sağdaki + sembolüne tıklayın ve başlığı düzenleyin. Başlığı bold yapın ve zemini yeşil dolgu ile renklendirin. Böylece dinamik hâle gelmiş olan grafiğin son durumu aşağıdaki gibi olmalıdır.

Örnek: Aşağıdaki tabloda bir mağazadaki temel ürünlerden aylara göre kaçar tane satıldığı verilmiştir. Seçilen bir üründen herhangi iki ay aralığında aylara göre kaçar tane satıldığını dinamik olarak gösteren bir grafik oluşturmak için KAYDIR fonksiyonunu kullanalım.

1. Önce Excel sayfanızda yukarıdaki tabloyu oluşturun. Veri Doğrulama aracındaki liste özelliğini kullanarak O14 hücresinde A2:A11 aralığındaki ürün adları, P14 ve Q14 hücrelerinde B1:M1 aralığındaki ay adları için birer açılır liste oluşturun. Açılır listelerden örneğin; ürün olarak pantolonu, başlangıç ayı olarak Ocak’ı ve bitiş ayı olarak Ağustos’u seçin.
2. Oluşturacağımız grafiğin yatay ekseni Aylar olsun. Başlangıç ve Bitiş ayları arasında yer alan tüm ayların listesini oluşturmak için B15 hücresine =KAYDIR($A$1;0;KAÇINCI($P$14;$B$1:$M$1;0);1;KAÇINCI($Q$14;$B$1:$M$1;0)-KAÇINCI ($P$14;$B$1:$M$1;0)+1) formülünü yazın. Burada $A$1, KAYDIR fonksiyonunun başlangıcının A1 hücresi olduğunu gösterir. Ay isimleri tabloda birinci satırda yer aldığı için A1 hücresinden aşağı kaymamıza gerek yok. Dolayısıyla satırlar bileşeninin yerine 0 yazılır. Başlangıç ayını belirlemek için P14 hücresinde yer alan başlangıç ayının B1:M1 aralığında kaçıncı sırada olduğu KAÇINCI formülüyle bulunur. KAYDIR fonksiyonunun dördüncü bileşeni olan [yükseklik] yerine 1 yazılır, çünkü ayların bulunduğu satır 1 satır olacak. Beşinci bileşen olan [genişlik] yerine, seçeceğimiz başlangıç ayı ile bitiş ayı arasında bu aylar dahil kaç ay olduğu yazılmalıdır. Örneğin yukarıdaki tabloda Ocak 1.ay, Ağustos 8.ay olduğundan grafikte ilk 8 aya ait veriler görünmeli. Formülde KAÇINCI fonksiyonu ile önce bitiş ayının sıra numarası sonra başlangıç ayının sıra numarası bulunuyor. Ağustos’un sıra numarasından Ocak’ın sıra numarasını çıkarırsak 8-1=7 bulunur. Ancak Ocak-Ağustos aralığı 8 aydan oluşuyor. Bu nedenle formülde bitiş ayının sıra numarası ile başlangıç ayının sıra numaraları farkına 1 eklenmiştir.

3. Oluşturacağımız grafiğin dikey ekseni satılan Adetler olsun. Tablodan önce O13 hücresinde yazılı olan ürünü bulup sonra bu ürüne ait başlangıç ve bitiş ayları arasında yapılan satış adetlerinin listesini oluşturmak için B17 hücresine =KAYDIR($A$1;KAÇINCI($O$14;$A$2:$A$11;0);KAÇINCI($P$14;$B$1:$M$1;0);1;KAÇINCI ($Q$14;$B$1:$M$1;0)-KAÇINCI($P$14;$B$1:$M$1;0)+1) formülünü yazın. Burada da $A$1, KAYDIR fonksiyonunun başlangıcının A1 hücresi olduğunu gösterir. Önce aşağıya kaç birim kayılacağı yani O13 hücresindeki ürünün bulunduğu satıra kaç birim inileceği KAÇINCI fonksiyonu yardımıyla bulunur. KAÇINCI($O$14;$A$2:$A$11;0) ifadesi O14’teki ürünün A2:A11 aralığında kaçıncı sırada olduğunu bulur. Sonra sağa kaç birim kayılacağı yani satış adetlerinin hangi aydan başlanarak yazılacağı yine KAÇINCI formülüyle bulunur. KAÇINCI($P$14;$B$1:$M$1;0) ifadesi P14’teki başlangıç ayının B1:M1 aralığında kaçıncı sırada olduğunu bulur. KAYDIR fonksiyonunun dördüncü bileşeni olan [yükseklik] yerine yine 1 yazılır, çünkü satış adetlerinin bulunduğu satır da 1 satırdır. Son olarak satış adetlerinin genişliği (kaç ayın verilerinin getirileceği) belirlenir. Bu işlem yine bir 2.maddede yer alan formüldeki ile aynı şekilde yapılır. Yani son olarak formüle

KAÇINCI ($Q$14;$B$1:$M$1;0)-KAÇINCI($P$14;$B$1:$M$1;0)+1 ifadesi eklenir. Formüller yazıldıktan sonra tablonun durumu aşağıdaki gibi olmalıdır.

4. Formüllerle bulunan değerleri grafiğe aktarmak için Formüller sekmesinin altında yer alan Ad Yöneticisi düğmesi kullanılır. Ay verilerini aktarmak için B15 hücresine tıklayın, formül satırındaki formülü seçip CTRL+C ile kopyalayın. ESC ile çıkın ve Formüller sekmesinin altındaki Ad yöneticisine tıklayın. Açılan pencerede Yeni düğmesine tıklayın. Yine açılan pencerede Ad kutucuğuna Aylar yazın. Başvuru yeri kutucuğuna ise mevcut yazıyı silip hafızaya aldığınız formülü CTRL+V ile yapıştırın. Aynı yöntemle satış adetleri verilerini aktarmak için B17 hücresine tıklayın, formül satırındaki formülü seçip CTRL+C ile kopyalayın. ESC ile çıkın ve Formüller sekmesinin altındaki Ad yöneticisine tıklayın. Açılan pencerede Yeni düğmesine tıklayın. Yine açılan pencerede Ad kutucuğuna Adetler yazın. Başvuru yeri kutucuğuna ise mevcut yazıyı silip hafızaya aldığınız formülü CTRL+V ile yapıştırın. Tamam’ tıkladığınızda Ad Yöneticisi penceresi aşağıdaki gibi görünmelidir.

Hem ay isimlerini hem de satış adetlerini Aylar ve Adetler’e yüklediğiniz için dilerseniz sayfanızdaki B15:I17 aralığındaki verileri silebilirsiniz.

1. Sayfanıza Ekle menüsünden bir sütun grafik ekleyin. Bu grafik alanını A12:M28 aralığına yayın. Grafik alanının üzerine sağ tıklayıp açılan listeden Veri Seç ögesine tıklayın. Açılan Serileri Düzenle penceresinde Seri adı kutucuğuna Dönemsel Satışlar yazın. Seri değerleri kutucuğuna tıklayın ve içeriğini silin, sayfanın alt kısmından sayfa adına tıklayın, gelen ifadenin devamına Adetler yazın ve Tamam’a tıklayın. (örneğin sayfanızın adı Sayfa71 ise, içerik =Sayfa71!Adetler biçiminde olmalı). Yatay (Kategori) Eksen Etiketleri kısmındaki Düzenle düğmesine tıklayın. Açılan penceredeki Eksen etiketi aralığı kutucuğu için yine sayfanın alt kısmından sayfa adına tıklayın ve bu defa devamına Aylar yazıp Tamam’a tıklayın. (içerik =Sayfa71!Aylar biçiminde olmalı). Tekrar Tamam’a tıklayıp pencereyi kapatın.
2. Bu durumda O14 hücresinden ürünü değiştirdiğinizde ya da P14 ve Q14 hücrelerinden başlangıç ve bitiş aralıklarını değiştirdiğinizde grafiğin dinamik olarak değiştiğini göreceksiniz.
3. Grafik üzerinde bazı biçimlendirme işlemleri yapmak için önce grafik alanına sonra grafiğin sağ üst kısmındaki + sembolüne sol tıklayın. Veri Etiketleri kutucuğunu aktifleştirin, kılavuz çizgilerini kaldırın. Grafiğin hem sol kısmında hem de sütunlar üzerinde sayılar olduğu için sol sütundaki dikey eksen verilerini silin. Ay adlarının yazılı olduğu yatay eksen verilerine tıklayın, punto büyüklüğünü 1 artırıp, kalın yapın. Son olarak sütunların üzerinde yazan veri etiketlerinden birine tıklayın, iç dolgu rengini sarı yapın, punto büyüklüğünü 1 artırın.
4. Şimdi de grafik başlığını aktif hale getirin. Bunun için sayfadaki boş bir hücreye =O14&” Ürününün “&P14&”-“&Q14&” Aylarına Ait Satış Adetleri” formülünü yazın. Formülü yazacağınız boş hücreyi grafiğin altına gelen bir hücreden seçebilirsiniz. Örneğin C28 olsun. Bu formül sayesinde O14, P14 ve Q14 hücrelerindeki veriler değiştirildiğinde başlık da dinamik olarak değişir.
5. Grafiğin üzerindeki grafik başlığına tıklayın. Bir önceki adımda formülü C28 hücresine yazdıysanız, sayfanın üst kısmındaki formül çubuğuna =Sayfa71!$C$28 formülünü girin. Grafik başlığını seçip sağdaki + sembolüne tıklayın ve başlığı düzenleyin. Başlığı bold yapın ve zemini pembe dolgu ile renklendirin. Böylece dinamik hâle gelmiş olan grafiğin son durumu aşağıdaki gibi olmalıdır.