4. Dinamik Dizi (Sequence) ve Tarih (Date) İşlemleri

Bu anlatımı yeni bir Sheet üzerinde yapalım. Çalışma dosyamızda sheetlerin bulunduğu yerde “+” ’ya basarak yeni bir sheet açtık.

Özellikle ders programı, nöbet çizelgesi veya proje takvimi hazırlarken verileri elde girmek yerine fonksiyonlara yazdırmak, hataları önlemek adına elzemdir.

SEQUENCE Fonksiyonu

=SEQUENCE(satır_sayısı, sütun_sayısı, başlangıç_değeri, artış_miktarı)

Örnek: 24 haftalık liste için satır kısmına 24 yazmak yeterlidir.

=SEQUENCE(24,,1,1) # sütun kısmını doldurmadık o şu anda boş olarak ,, arasında

Bu komut çalıştığında aşağıdaki görünüm elde edilir.

Tarih Atama (DATE)

SEQUENCE formülü içine başlangıç değeri olarak tarih konulabilir. 7’şer gün artan bir ders programı için artış miktarına 7 yazılır.

SEQUENCE formülü ile beraber aşağıdaki gibi yazmamız gerekir.

Date içine yıl , ay , gün şeklinde yazılır. Son olarak da kaçar kaçar tarihin ilerleyeceği eklenir.

Formül aşağıdaki gibidir.

=SEQUENCE(24,,DATE(2025,10,8),7)

Bu formül çalıştığnda tek seferde 24 haftalık tarihler karşımıza gelir.

DİKKAT Excel’de Tarih Mantığı DİKKAT

Excel, tarihleri kendi arka planında 1 Ocak 1900’den başlayan sayısal değerler (seri numaraları) olarak tutar. SEQUENCE sonucunda anlamsız sayılar çıkarsa, hücre formatını General’den Date (Tarih) formatına çevirmek yeterlidir.

Bu işlemlerden sorna gerekli düzenlemeleri yaparak aşağıdaki görünümü elde ederiz.

5. Hücre Sabitleme ve Dinamik İsimlendirme

Bu anlatım “Satışlar” Sheeti ile yapılacaktır.

Dolar İşareti ile Sabitleme (Absolute Reference)

İlk önce çalışma sayfamızın bir yerinde Prim değeri olsun. Aşağıdaki gibi

Formül içine dahil edilen referans hücresini kilitlemek için $ işareti kullanılır: $Q$1. Yani formül aşağıdaki gibi olur.

Hızlı yöntem: Formülü yazarke hücre seçiliyken F4 tuşuna basarak yine $Q$1 ’ü kolayca elde ederiz.

=J2*$Q$1

Sonra bunu her zaman yaptığımız gibi çıkan sonucun sağ alt köşesine çift tıklıyarak tüm sütun doldurulur. Son durum aşağıdaki gibidir.

Bir başka Sheet daha alsun ve orada da VERGİ ORANI ismi ve DEĞERİ var diyelim yani aşağıdaki gibi

O zaman formülümüzü aşağıaki gibi yazarız. F4’ü kullanacağız. Yani formülü yazarken vergi oranının bulunduğu hücreyi seçip F4’e basacağız.

=K2*Sheet3!$C$2

Sonra bunu her zaman yaptığımız gibi çıkan sonucun sağ alt köşesine çift tıklıyarak tüm sütun doldurulur. Son durum aşağıdaki gibidir. (Düzenlemeler yapılmıştır.)

Name Box (İsim Kutusu) Kullanımı

Daha zarif yol: Örneğin bizim örneğimizde bir başka sheetde bulunan prim vergisi değerinin üzerine gelip tıklayıp sol üstteki isim kutusuna özel bir ad verin (örneğin “Vergi”). Artık formülde doğrudan =K2 * Vergi yazabilirsiniz.

=K2*Vergi

Bu sayede formülleri metin halinde yazmış oluruz.

6. Gelişmiş Arama Fonksiyonu: XLOOKUP

DİKKAT Bu anlatım “ögrenci” sheeti ile yapılacaktır. Anlatım olsun diye eklenen sütunlar silinmiştir.DİKKAT

İlk önce Ad ve Soyadın yanında bir sütun ekleyerek

=B2&" "&C2

ile bunu oluşturalım.

Sonra bunu her zaman yaptığımız gibi çıkan sonucun sağ alt köşesine çift tıklıyarak tüm sütun doldurulur. Son durum aşağıdaki gibidir. (Düzenlemeler yapılmıştır.)

XLOOKUP eskiden kullanılan VLOOKUP ve HLOOKUP’un kısıtlarını ortadan kaldırır. T.C. Kimlik No veya Öğrenci No gibi benzersiz tanımlayıcılarla sorgu ekranları oluşturmak için idealdir.

=XLOOKUP(aranan_değer, aranacak_sütun, döndürülecek_sütun)

örnekte aşağıdaki gibi bir yerleşim yaparak formülü şöyle yazarım.

=XLOOKUP(X2,D2:D31,I2:I31)

Görünüm aşağıdaki gibi olur.

Burada X2’nin bulunduğu yere bir başka isim yazdığımızda o isme ait GPA notu artık gelmektedir.

Şimdi ise bu sefer GPA’nın yanına Burs Miktarını ekleyerek yine XLOOKUP ile formülü oluşturalım.Burada burs almayanların burs miktar kısmının boş olduğunu hatırlatmak isterim.O kısımlar sıfır gelecektir Formülü aşağıdaki gibidir.

=XLOOKUP(X2,D2:D31,K2:K31)

Örneğimiz ile ilgili son görünüm aşağıdaki gibidir.

7. Tablo ve Pivot Tablolar (Özet Tablolar)

Bu anlatım “Satışlar” Sheeti ile yapılmıştır. Az önceki XLOOKUP kısmını anlatım kolaylığı için kaldırıyoruz.

Büyük ve hiyerarşik verilerde özet analizler için en güçlü araçtır.

Veriyi Tabloya Dönüştürme

Tüm veri seçilip Ctrl + T (Table) ile tablo haline getirilmelidir. Excel ancak bunun bir tablo olduğunu bu sayede anlar.

Menuden Table seçilebilir veya Ctrl + T yapılabilir. (Mac’de Commmand + T)

Burada başlıkları da alsın diye My table has headers tikli olması lazım. Ok dediğimizde tablomuz karşımıza gelir.

Pivot Table Oluşturma

Diyelim ki tablomuzu yaptık ama şimdi ise pivot Table ile daha estetik ve kullanışlı tablo oluşturabiliriz.

Insert → Pivot Table seçeneği ile oluşturulur. New Worksheet (Yeni Çalışma Sayfası) kullanılması önerilir.

New Worksheet (Yeni Çalışma Sayfası) kullanılması önerilir. Seçim yapılır ve OK tıklanir

DİKKAT ÖNEMLİ NOT: PIVOT TABLE’ı oluşturmadan önce biz en baştaki hücrede olmalıyız

Karşımıza aşağıdaki gibi bir görünüm gelir.

Pivot Paneli

İki kısımdır ilk kısımda değişkenlerimiz yani kolonlarımız var.

ikinci kısım ise aşağıdaki gibidir.

  • Rows → Satırlar
  • Columns → Sütunlar
  • Values → Değerler (Sum, Average, Count…)
  • Filters → Genel filtreler

Kolonların bulunduğu yerden Şehir’ı sürükle bırak mantığında Rows kısmına sürüklersek Şehir eklenmiş olur. Values kısmına ise Toplam Satış’ı sürükleyip bırakalım.

Burada bunu yaptıktan sonra ekranın sağ kısmında ilgili değerler gelir. Burada Sum of Toplam Satış üzerine gelip sağ tıkladığımızda Summary Values by ın üzerine geldiğimzde isteğimize göre toplam miktar,satış miktarı,ortalama,min ve maksimum değerleri işaretleyerek bu değeri elde edebiliriz.

Şimdi Values kısmına Masaüstü ve Laptop değişken isimlerini sürükleyip bırakalım. Görünüm aşağıdaki gibi olur.

Eğer vazgeçersek ya yine geri sürükleyip bırakırız ya da hangisi ise onun üzerine gelip sağ tıklayıp Remove Field diyebiliriz. Masaüstü ve Laptop’ı kaldıralım.

Şimdi ise her bir ilin aylara göre satışlarına bakalım Ay değişkenini Columns kısmına sürükleyip bırakalım. Aylar alfabatik sırayla gelir. Bunu yorumlama kolaylığı olsun diye Hocamız tarafından Ay2 oluşturulmuş olup Ay2’yi Columns’a sürükleyelim.Artık aylar sırasıyla gelecektir.

Şimdi ilgili yere tarayıp yani iller ve aylık satışları seçip insert’ten çizgi grafiğini seçip grafik oluşturalım.

İlgili grafik seçildiğinde hemen eklenir.

Görüldüğü üzere X ekseninde şehirler var ben bunu düzeltmek istiyorum.

Satır ve Sütun Değişimi

Design → Switch Rows and Columns ile satırlar ve sütunlar yer değiştirebilir. Bu tıklandığında hem grafik hem datamızın görüntüsü değişir yani X eksenine aylar gelir.

DİKKAT LÜTFEN AYLARI NUMARA HALİNE GETİREN Ay2 SEÇEREK GRAFİĞİ OLUŞTURUNUZ. GRAFİĞİ DAHA İYİ YORUMLAMAK İÇİN BU ŞARTTIR.

Şimdi Cinsiyetlere göre satışa bakalım.

Cinsiyeti Filters’a sürükleseydim orada bir filter sekmesi oluşacaktı buradan kadın ve erkek tek tek seçilerek ona ait bilgileri gelebilirdi.

İstersek burada ikinci bir filters kriteri koyabilirdik. örneğin ödeme türüne göre o zaman durum aşağıdaki gibi olurdu. Ödeme türünü de filters kısmına sürüklerdik.Bu sayede kadın veya erkeğin ödeme türlerine göre istersek tek tek satışlarını görebildik.

8. Veri Görselleştirme (Grafikler)

Grafik Oluşturma

Pivot tablosundan özet veriler seçilip Insert menüsünden grafik (Pie, Bar, Line vb.) eklenir.

Örnek olarak şehirlerdeki satış miktarlarının cinsiyetlere göre dağılımını pivot tablodan oluşturduk ve görsellerilmeesini yapmak istiyoruz.

Görselleştirme yapmadan önceki durum aşağıdaki gibidir.

Sonra bunu tarayıp copy paste diyerek aynı yerde düzenleyelim. Aşağıdaki gibi görünsün.

Şimdi önce erkeklerin yaptığı satışların grafiğini yapalım. Pie yani pasta grafik yapalım.

Bu seçildiğinde;

Grafik üzerinde iki defa tıklarsak ekranda sağ tarafta grafiği düzenlemek için bir menü çıkar.

Grafik Tasarımı ve Formatlama

  • Grafiğe çift tıklayarak Format menüsü açılır.
  • Shape Fill → Arka plan rengi
  • Shape Outline → Kenarlık
  • Grafi üzerine sağ tıklayıp Add Data Labels → Veri etiketleri
  • Dilimlerin veri etiklerinin tıklayarak ana menüden boyutlarını ve yazı rengini değiştirebilirim.
  • Dilimleri çift tıklayarak dilimleri Format menüsünden Shape Fill ile o dilimin rengini değişterebilirim.
  • Grafiği tıklayıp Chart Design i tıklarsak alternatif fikirler verebilir.

Grafikleri Kaydetme (Önemli!)

Grafiğe sağ tıklayıp Save as Picture menüsünden SVG formatı seçin (vektörel, pikselleşmez). Kalitesi iyi olur.

Bu dosyalarımızı bir klasörde saklayabiliriz.

Grafik üzerinden kopyala yapıştır derseniz kalitesi bozulur. Örneğin boş bir word dosyasına grafik eklenecek ise Word’de Insert Pictures This Device şeklinde klasörden getirelim. Bu sayede grafik küçülse de büyüse de kalitesi düşmez.

Şablon Olarak Kaydetme

Save as Template seçeneği ile sonraki grafiklerde aynı tasarımı anında uygulayabilirsiniz.

Örneğin az önceki erkek pasta grafiğini şablon olarak save edip sonra onu kadın için yapılacak pasta grafiğinde şablon olarak kullanabiliriz.

Erkek pasta grafiğinin üzerine gelip Save as Template yaparak şablonu saklayalım. Örneğin pie1 olarak kayıt ettik. Sonra bu sefer Kadın pasta grafiğinin üzerine geldik Change Chart Type üzerine gelince Templates kısmından pie1 seçilir. Şablon artık Kadın pasta grafiğine de uygulanmış olur.

Derinlik ve Katmanlar

Üst üste grafikler için grafik üzerinde sağ tuş yapıp Bring to Front / Send to Back kullanılabilir.

Dostlar,

Üçüncü haftayı bitirmiş olduk. Artık Excel’i sadece veri girişi yapmak için değil, veriyi hızlıca düzenlemek, temizlemek, analiz etmek ve etkileyici görsellerle sunmak için güçlü bir araç olarak kullanabiliyoruz. Hocamızın anlatımdan derlenerek bu çalışma oluşturulmuştur.

Üçüncü hafta ile veriye ilk bakıştan başlayarak temel kısayolları, filtreleme ve transpose tekniklerini, metin fonksiyonlarını, dinamik dizi oluşturmayı, hücre sabitleme yöntemlerini, XLOOKUP’u, Pivot Tabloları ve profesyonel grafik hazırlama tekniklerini adım adım öğrendik. Özellikle tarihleri gün-ay-yıl olarak bölme, veriyi istenilen yöne çevirme ve büyük veri setlerinden anlamlı özetler çıkarma becerilerimiz artık çok daha güçlü.

Üçüncü hafta burada sona eriyor.