6.2 Makro ile İşlem Yapma (Gelişmiş Kullanım, Kayıt Formu, Filtreleme)

Örnek 3: Aşağıdaki Excel tablosunda bir şirketin günlük satış gelirleri ile günlük maliyetleri her bir gün için ayrı ayrı verilmiştir. Önce haftalık toplamları bulan bir makro kaydedelim, sonra ise haftalık satış gelirlerinde 60.000 TL ve üzeri olanları yeşile, 60.000 TL’nin altında kalanları kırmızıya boyayan bir makro kaydedelim. Son olarak ise haftalık maliyetlerde 30.000 TL’nin altında olanları maviye, 30.000 TL ve üzeri olanları ise turuncuya boyayan bir makro kaydedelim.

1. Yukarıdaki Excel verilerinin bulunduğu dosyayı indirin.
2. Geliştirici sekmesine tıklayın.
3. Toplamları hesaplayacağımız H3 hücresine tıkladıktan sonra Göreli Başvuruları Kullan düğmesine tıklayın.
4. Makro Kaydet düğmesine tıklayın. Açılan pencereden makronuza bir isim verin, örneğin Toplam_makrosu olsun. Kısayol olarak CTRL ifadesinin yanına x yazın, böylece kısayolunuz CTRL+x olur.
5. H3 hücresine =TOPLAM(B3:G3) formülünü girin ve H5 hücresine tıklayın.
6. Geliştirici sekmesinin altından veya durum çubuğundan Kaydı Durdur düğmesine tıklayın.
7. Bundan sonra H5 hücresine gelin CTRL+x tuşlarına basın, Excel toplamayı yapar ve H7 hücresine geçer.
8. CTRL+x tuşuna bir kez daha basın, Excel toplamayı yapar ve H9 hücresine geçer. CTRL+x tuşuna bir kez daha basın, Excel toplamayı yapar ve H11 hücresine geçer.
9. H11 hücresi toplama yapılacak bir hücre değil. Dolayısıyla fareyle H13 hücresine geçin ve CTRL+x tuşlarına basmaya devam edin.
10. Ocak ayı sayfasındaki toplamlar bitince Şubat ayı sayfasına geçin. H3 hücresinden başlayıp CTRL+x tuşlarına basarak bu sayfadaki toplamları da bulun. Makro işlemleri CTRL+Z ile geri alınamaz, dolayısıyla işlemleri dikkatli yapmalısınız.

11. Şimdi koşullu biçimlendirmeyi kullanarak haftalık satış gelirlerinde 60.000 TL ve üzeri olanları yeşile, 60.000 TL’nin altında kalanları kırmızıya boyayan bir makro kaydetmek için Ocak adlı çalışma sayfasında H3 hücresine gidin. Geliştirici sekmesinden önce Göreli Başvuruları Kullan düğmesine sonra Makro Kaydet düğmesine tıklayın.
12. Makronuzun adına Kosullu_bicim_1 adını verin ve kısayol tuşunuz CTRL+y olsun.
13. Tamam tuşuna bastıktan sonra Giriş sekmesinin altından Koşullu biçimlendirme açılır listesinden Yeni kural ögesine tıklayın.
14. Açılan listeden Yalnızca şunu içeren hücreleri biçimlendir seçeneğine tıklayın. Alt kısımda arasında yerine büyük ya da eşit seçeneğini seçip yandaki kutuya 60000 yazın.
15. Pencereyi kapatmadan Biçimlendir düğmesine tıklayın. Dolgu seçeneğinin altından yeşil rengi seçip Tamam’ tıklayın.
16. Yine pencereyi kapatmadan alttaki kutucuktaki büyük ya da eşit seçeneği yerine küçük seçeneğini işaretleyin. Tekrar Biçimlendir düğmesine tıklayın ve bu defa dolgu rengi olarak kırmızıyı seçin.
17. Tamam düğmelerine tıklayarak koşullu biçimlendirme penceresini kapatın.
18. H5 hücresine tıklayıp Geliştirici sekmesinden Kaydı Durdur düğmesine tıklayın.
19. Son olarak koşullu biçimlendirmeyi kullanarak haftalık maliyetlerde 30.000 TL’nin altında olanları maviye, 30.000 TL ve üzerinde olanları turuncuya boyayan bir makro kaydetmek için Ocak adlı çalışma sayfasında H3 hücresine gidin. Geliştirici sekmesinden önce Göreli Başvuruları Kullan düğmesine, sonra Makro Kaydet düğmesine tıklayın.
20. Makronuza Kosullu_bicim_2 adını verin ve kısayol tuşunuz CTRL+d olsun.
21. Bir önceki makronun hazırlanışı ile aynı yöntemleri kullanarak makroyu hazırlayın.
22. Makroları işleme alırken haftalık satış gelirleri toplamının koşullu biçimlendirilmesinde CTRL+y ve haftalık maliyetlerin koşullu biçimlendirilmesinde CTRL+d kısayolunu kullanın.

Örnek 4: Bir üniversitenin farklı bölümlerinde okuyan ve MATH101 adlı matematik dersini alan öğrencilerin sınav notlarını bu derse makro yardımıyla kaydeden bir Excel çalışma kitabı oluşturalım. Daha sonra bu tabloya öğrencilerin toplam puanını, geçme notunu, geçme/kalma durumunu, harf notunu ve aldığı rozeti gösteren birer sütun ekleyelim.

1. Math101 dersine ait notların kaydedileceği “Makro Çalışması” adında bir Excel çalışma kitabı açın.
2. “Sayfa1” adlı çalışma sayfasını “Not Girişi” olarak adlandırın.
3. “Sayfa2” adlı çalışma sayfası açıp “Not Tablosu” olarak adlandırın.
4. “Not Girişi” sayfasında aşağıdaki tabloyu B3 hücresinden başlayarak oluşturun.

5. “Not Tablosu” sayfasında aşağıdaki tabloyu başlangıçta 100 öğrenci için oluşturun.

6. “Sayfa 3” adlı bir çalışma sayfası açıp “Bölümler” olarak adlandırın. Math101 adlı matematik dersini üniversiteden hangi bölümlerdeki öğrencilerin aldığını gösteren aşağıdaki tabloyu oluşturun.

7. “Not Girişi” sayfasında kaydı girilecek öğrencilerin bölümlerini klavyeden yazmamak için veri doğrulamayı kullanarak bir açılır liste oluşturalım. “Not Girişi” sayfasında C5 hücresine gidin. “Veri” menüsünün altındaki “Veri doğrulama” açılır listesinden “Veri Doğrulama” seçeneğine tıklayın. “Ayarlar” sekmesinde yer alan “İzin Verilen” kısmındaki açılır listeden Liste seçeneğini işaretledikten sonra, “Kaynak” bölümüne “Bölümler” çalışma sayfasından B4:B13 aralığını seçin. Bu durumda “Kaynak” kısmında =Bölümler!$B$4:$B$13 ifadesi yazmalıdır. Tamam’a tıkladıktan sonra C5 hücresine giderseniz açılır listenin oluştuğunu göreceksiniz.
8. Şimdi sınav notları sırasıyla 6, 8, 70, 7, 9, 90 ve 95 olan Fizik bölümü öğrencisi “Suat Yanık”a ait verileri “Not Girişi” çalışma sayfasındaki tabloya girin. Öğrencinin bölümünü girerken açılır listeyi kullanın. Tablo aşağıdaki gibi olmalıdır.

9. Şimdi bu listedeki verileri “Not Tablosu”na ekleme işlemini bir makroya kaydedelim. “Geliştirici” menüsünün altından “Makro Kaydet” düğmesine tıklayın. (Şeridinizde Geliştirici menüsü yoksa “Dosya” menüsünün altından “Seçenekler”e tıklayın. Açılan pencereden “Şeridi Özelleştir” seçeneğine tıklayın. Geliştirici menüsünü etkin hâle getirip Tamam tuşuna tıklayın.)
10. Açılan iletişim kutusunda makronuza bir isim verin. Örneğin ismi aşağıdaki gibi Notkayıt olabilir. (Makronun adı birden fazla kelimeden oluşuyorsa aralarında boşluk bırakmadan yazmalısınız.)
    Makronun çalışması için iki yol kullanabilirsiniz:
    a. Kısayol: CTRL+ yanındaki boşluğa bir harf ya da SHIFT tuşuyla birlikte bir harf yazarak makro için bir kısa yol oluşturup makronuzu bu kısayol yardımıyla çalıştırabilirsiniz.
    b. Düğme/Tuş: Birazdan anlatacağımız gibi bir tuş oluşturup bu tuşa tıklayarak makronuzu çalıştırabilirsiniz.
11. Tamam’a tıklayın.

12. C3:C12 aralığını seçip kopyaladıktan sonra “Not Tablosu” sayfasına geçin.
13. C3 hücresine gidin ve “Geliştirici” menüsündeki “Göreli Başvuruları Kullan” seçeneğine tıklayın. (Bu işlemi girilen her yeni kaydın, bir önceki kayıttan hemen sonra gelen satırdan başlaması için yapıyoruz.)
14. “Veri girişi” kutusunun solunda yer alan “Ad Kutusu”na, “C500” yazıp ENTER tuşuna basın. (C500 hücresine gittiniz.) Buna göre tablonuza 500 kayıt girebilirsiniz. Dilerseniz bu sayıyı artırabilirsiniz.

15. CTRL ve yukarı ok ↑ tuşuna basarak C3 hücresine gidin.
16. Aşağı ok ↓ tuşuna basıp C4 hücresine gittikten sonra “Geliştirici” menüsündeki “Göreli Başvuruları Kullan” seçeneğine tıklayın.
17. C4 hücresine sağ tıklayıp “Özel Yapıştır” seçeneğine tıklayın. Açılan pencereden “Devrik Dönüşüm” seçeneğine tıkladıktan sonra Tamam’a tıklayın.

18. “Not Girişi” çalışma sayfasına geçin. Önce Esc tuşuna basıp kopyalama işleminden ayrılın, sonra Delete tuşuna basıp kayıt giriş sayfasını temizleyin.
19. C3 hücresine gittikten sonra gittikten sonra “Geliştirici” menüsünden “Kaydı Durdur” tuşuna tıklayın.
20. “Geliştirici” menüsünün altındaki “Ekle” açılır listesine tıklayın. “Form Denetimleri”nden “Düğme” (ilk dikdörtgen) tuşuna tıklayın. Bu durumda fare işaretçisi + şekline dönüşür. Düğme oluşturmak için ikinci bir yöntem olarak “Ekle” menüsünün altında yer alan “Şekiller” açılır listesinden, “Dikdörtgenler” bölümündeki bir dikdörtgen seçilebilir.

21. D2 hücresinin sağ alt köşesine sağ tıklayıp bırakmadan F3 hücresinin sağ alt köşesine taşıyıp bırakın. “Düğme 1” adlı bir düğme oluşacaktır. Düğmeye tıklayıp adını “Notları Kaydet” olarak değiştirin.

22. “Notları Kaydet” düğmesine sağ tıklayın. Açılan listeden “Makro Ata” seçeneğine tıklayın.
23. Açılan pencereden önce makronuzun ismine, sonra Tamam’a tıklayın. Bundan sonra “Not Girişi” bölümünden yeni kayıt girdikten sonra oluşturduğunuz bu düğmeye tıklarsanız kaydınız “Not Tablosu” sayfasındaki tabloya eklenecektir.
24. Birkaç öğrenciye ait notları “Not girişi” çalışma sayfasındaki tablodan girip makronun düzgün çalıştığını test ettikten sonra “Not Tablosu” sayfasının görünümü aşağıdaki gibi olmalıdır.

25. Bundan sonra 3. ünitedeki “Not Defteri ve Parklar” dosyasının “Notlar” sayfasında olduğu gibi “Not Tablosu” sayfasındaki tablomuza öğrencilerin “Toplam Puanı”nı, dersi “Geçme Puanı”nı, “Geçme/Kalma” durumunu, geçme puanına karşılık gelen “Harf Notu”nu ve yüksek başarı elde eden öğrencilerin aldığı nota karşılık gelen “Rozet” derecesini gösteren sütunlar ekleyelim.
26. M3 hücresine TOPLAM PUAN yazdıktan sonra, içerisinde not yazılı olan satırlardaki notların toplamını bulmak için M4 hücresine =EĞER(TOPLA(Tablo3[@[Q1PUANI]:[FİNALPUANI]])=0;””;TOPLA(Tablo3[@[Q1 PUANI]:[FİNAL PUANI]])) formülünü yazın. (Burada =EĞER(TOPLA yazdıktan sonra F4:L4 aralığını seçersek formüldeki Tablo bilgileri otomatik gelecektir. Yani formülü yazdığımız hücre tablo içinde olmasaydı formülümüz =EĞER(TOPLA(F4:L4)=0;””;TOPLA(F4:L4)) biçiminde olacaktı.)
27. N3 hücresine GEÇME PUANI yazdıktan sonra, içerisinde toplam puanın yazılı olduğu satırlarda öğrencilerin geçme puanını hesaplayacağız. Bu hesaplamayı yaparken yöntem olarak tüm öğrencilerin puanını öğrencilerin bu sınavlardan alabildiği maksimum puanlar toplamına bölüp 100 ile çarpacağız. Dolayısıyla önce F104 hücresine =MAK([Q1 PUANI]) formülünü yazın. (Formülü tablo dışında yazıyor olsaydık =MAK(F4:F104) olacaktı).
28. Ardından bu formülü G104:N104 aralığındaki hücrelere de kopyalayın. Sonra N4 hücresine =EĞER([@[TOPLAM PUAN]]=””;””;100*([@[TOPLAM PUAN]]/ Tablo3[[#Toplamlar]; [TOPLAM PUAN]])) formülünü yazın. Formülü N sütunundaki diğer hücrelere de kopyalayın.
29. Öğrencilerin Geçme/Kalma durumunu bulmak için O3 hücresine GEÇTİ/KALDI yazdıktan sonra O4 hücresine =EĞER([@[GEÇME PUANI]]<60;”KALDI”; EĞER([@[GEÇME PUANI]]=””;””;”GEÇTİ”)) formülünü yazın. Aynı sütundaki diğer hücreler kopyalayın. (Formüldeki [@[GEÇME PUANI]] ifadesini elde etmek için N4 hücresine tıkladık)
30. P3 hücresine HARF NOTU yazdıktan sonra P4 hücresine =EĞER([@[GEÇME PUANI]]<60;”F”;EĞER([@[GEÇME PUANI]]<70;”D”; EĞER([@[GEÇME PUANI]]<80;”C”;EĞER([@[GEÇME PUANI]]<90;”B”; EĞER([@[GEÇME PUANI]]=””;””;”A”))))) formülünü yazın. Bu formülü aynı sütundaki diğer hücrelere kopyalayın.
31. Q3 hücresine ALINAN ROZET yazdıktan sonra Q4 hücresine =EĞER([@[GEÇME PUANI]]<80;””;EĞER([@[GEÇME PUANI]]<90;”Onur”;EĞER([@[GEÇME PUANI]]=””;””;”Yüksek Onur”))) formülünü yazın. Bu formülü aynı sütundaki diğer hücrelere kopyalayın.
32. Kalan öğrencilerin tabloda kolayca görünmesi için “GEÇTİ/KALDI” sütununa koşullu biçimlendirme uygulayalım. Bunun için O5 hücresini seçtikten sonra “Giriş” menüsünden “Koşullu Biçimlendirme” açılır listesine tıklayın. “Hücre Vurgulama” seçeneğinin altından “İçerdiği Metin” seçeneğine tıklayın. Açılan pencerede “Şu Metni İçeren Hücreleri Biçimlendir” kısmına “KALDI” yazın, biçim kısmından “Koyu Kırmızı Metinle Açık Kırmızı Dolgu” seçeneğini seçip Tamam’a tıklayın. Biçimi aynı sütundaki diğer hücrelere kopyalayın. Aynı işlemi P5 hücresi için de tekrarlayın. Bu kez “Şu Metni İçeren Hücreleri Biçimlendir” kısmına “F” yazın. Not tablosunun son durumu aşağıdaki gibi olacaktır.

Örnek: Aşağıda bir giyim firmasının çalışanlarının farklı şehirlere yaptıkları farklı ürünlere ait satışları tarihlere göre gösteren bir Excel tablosu verilmiştir. Bu tabloda çalışan adı, şehir ve satış adedine bağlı bir gelişmiş filtreleme uygulayalım. Daha sonra bu filtrelemeyi pratik hâle getirmek için bir makro kaydedelim.

1. Önce yukarıda bir kısmı gösterilen Excel tablosunu bilgisayarınıza indirip açın.
2. Yeni bir çalışma sayfası açın ve bu sayfada K1:Q2 aralığına veri sayfasındaki başlıkları kopyaladıktan sonra çalışan adı, şehir ve adet kriterlerini aşağıdaki gibi girin.

3. A1 hücresini seçin ve Veri sekmesinin altından Sırala ve Filtre Uygula komut grubundaki Gelişmiş ögesine tıklayın. Aşağıdaki pencere açılacaktır.

Bu pencerede önce “Başka bir yere kopyala” ögesini onaylayın. Liste aralığı boşluğuna tıkladıktan sonra verilerin bulunduğu Sayfa1’e geçin ve A:G sütun aralığını seçin. Penceredeki Ölçüt aralığı boşluğuna tıklayın ve kutucuğu temizledikten sonra kriterleri yani K1:Q2 aralığını seçin. Hedef boşluğuna tıklayın ve içeriği temizleyip A1 hücresine tıklayın. Son olarak “Yalnızca benzersiz kayıtlar” seçeneğini onaylayıp Tamam’a tıklayın. A1:G7 aralığına aşağıdaki tablo gelecektir.

4. Şimdi bu filtreleme işlemini değiştirdiğimiz kriterlere göre hızlıca yapabilmek için bir makro kaydedin. A1 hücresine tıklayıp Geliştirici sekmesinin altından “Göreli Başvuruları Kullan” ögesinin etkin olmadığına dikkat ederek Makro Kaydet düğmesine tıklayın.
5. Makro Kaydet penceresi açılacaktır. Makronuza “Raporlama” adını verin ve kısayol olarak Ctrl+r kısayolunu seçip Tamam’a tıklayın.
6. Mevcut sayfadaki A:G sütun aralığını seçtikten sonra Giriş sekmesinin altından Temizle açılır listesinden Tümünü Temizle ögesine tıklayın.
7. Tekrar A1 hücresine tıklayın ve ardından Veri sekmesinin altından Sırala ve Filtre Uygula komut grubundaki Gelişmiş ögesine tıklayın. Gelişmiş Filtre penceresi açılacaktır. Bu pencerede önceki gibi “Başka bir yere kopyala” ögesini onaylayın. Liste aralığı boşluğuna tıkladıktan sonra verilerin bulunduğu Sayfa1’e geçin ve A:G sütun aralığını seçin. Penceredeki Ölçüt aralığı boşluğuna tıklayın ve kutucuğu temizledikten sonra kriterleri yani K1:Q2 aralığını seçin. Hedef boşluğuna tıklayın ve içeriği temizleyip A1 hücresine tıklayın. Son olarak “Yalnızca benzersiz kayıtlar” seçeneğini onaylayıp Tamam’a tıklayın. A1:G7 aralığına yine aşağıdaki tablo gelecektir. (Çünkü henüz kriterleri değiştirmedik)

8. Geliştirici sekmesinden “Kaydı Durdur” düğmesine tıklayın.
9. Çalışan Adı Soyadı olarak Suna Tüfekçi yerine veri listesinden Fatih Derin adını, şehir olarak İstanbul yerine Bursa girin. Makroyu çalıştırmak için Ctrl+r kısayolunu kullanın.
10. Gelişmiş filtreleme sonucu aşağıdaki gibi olacaktır. Böylece hem adı Fatih Derin hem satış şehri Bursa hem de satış adedi 6’dan büyük olanlar makro yardımıyla filtrelenmiş oldu.

11. Dilerseniz makronuzu sayfaya bir düğme ekledikten sonra bu düğmeye atayabilirsiniz.