Alt Üst Soy Belgesi ile Soy Ağacı Oluşturma

e-Devlet Alt Üst Soy Bilgilerinden Excel Üzerinde Aile Ağacı Çizen Makrolu Dosya​

Bu çalışma kitabı, e-Devlet Kapısı'ndaki Alt-Üst Soy Bilgisi Sorgulama ekranında görüntülenen akrabalık tablosunu Excel'e aktarmak ve bu tablodan kuşaklara ayrılmış bir soy ağacı çizmek için hazırlanmış makrolu bir dosyadır. Dosyanın içinde hazır veri, formül veya örnek aile bulunmaz; bütün işlem tek bir düğmeyle başlatılan VBA makrosu tarafından yapılır. Makro, kullanıcının kendi bilgisayarında açık olan e-Devlet sorgu sayfasındaki tabloyu okur, satırları bir çalışma sayfasına yazar ve ardından ayrı bir sayfada kişiyi, anne ve babasını, büyükanne ve büyükbabalarını ve daha önceki kuşakları renkli kutular halinde yerleştirir.
Dosya .xlsm biçimindedir ve dört çalışma sayfasından oluşur. Çalışma mantığı tamamen Internet Explorer türü bir tarayıcı penceresine bağlıdır. Bu nedenle aşağıda, dosyanın neyi nasıl yaptığı kadar hangi koşullarda çalışmayabileceği de açıkça anlatılmıştır.

Dosya Ne İşe Yarar​

Alt üst soy belgesi, bir kişinin nüfus kayıtlarındaki anne, baba, büyükanne, büyükbaba ve daha önceki kuşaklarına, alt soy kısmında ise çocuklarına ve torunlarına ait bilgileri tablo halinde gösterir. Belge kuşak sayısı arttıkça uzun bir listeye dönüşür ve kimin kimin annesi veya babası olduğunu bu listeden izlemek zorlaşır. Bu dosya, aynı listeyi görsel bir aile ağacına çevirmeyi amaçlar.
Makro, tablodaki Yakınlık sütununda yazan ifadeleri çözümleyerek her kişinin hangi kuşakta ve hangi dalda bulunduğunu belirler. Örneğin Babası birinci kuşakta baba dalına, Annesinin Babası ikinci kuşakta anne dalının baba tarafına yerleştirilir. Sonuçta kişinin kendisi en solda, her yeni kuşak bir sağdaki sütunda yer alır; anne ve baba kutuları kalın siyah çizgilerle birbirine bağlanır.
Bilgiler e-Devlet sayfasından okunur ve yalnız açık çalışma kitabına yazılır. Makro kodunda verileri internete veya başka bir dosyaya gönderen bir bölüm bulunmaz. Çalışma kitabı kaydedildiğinde aile bireylerinin kişisel bilgileri de dosyada saklanacağı için kaydedilen kopyanın güvenli bir yerde tutulması uygundur.

Sayfa Sayfa İçerik​

anasayfa: Makro Başlatma Ekranı​

Dosya açıldığında görünen ilk sayfadır. B1:C7 aralığında bir aile simgesi bulunur ve bu simgeye SoyAgaciAktarim makrosu atanmıştır; simgeye tıklandığında aktarım başlar. D8 hücresinde exceldepo.com yazılı bir bağlantı yer alır. Sayfa korumalıdır, yalnız A-D sütunları ve ilk sekiz satır görünür; geri kalan satır ve sütunlar gizlenmiştir.

SoyBilgileri: Aktarılan Tablo​

Teslim edildiğinde boştur. Makro çalıştığında önce bu sayfanın tamamı temizlenir, ardından e-Devlet tablosunun başlıkları birinci satıra, kişi satırları ikinci satırdan itibaren yazılır. Başlık metinleri dosyada sabit değildir; tarayıcıdaki tabloda ne yazıyorsa aynen kopyalanır. Satır başına on iki hücre okunur. Satırın ilk hücresi sayı değilse tablo bitmiş kabul edilir. Onuncu sütun, Excel'in tarih veya sayıya çevirmemesi için metin olarak yazılır. Aktarımdan sonra H ve L sütunlarındaki satır sonları tire işaretine dönüştürülür, I sütunundaki satır sonları kaldırılır, başlıklar kalın ve küçük puntoyla biçimlendirilir, sütun genişlikleri içeriğe göre ayarlanır.

s1: Gizli Ara Sayfa​

Gizli bir çalışma sayfasıdır ve teslim edildiğinde boştur. Makro, SoyBilgileri tablosunun bir kopyasını buraya alır ve ağaç hesaplamasını bu kopya üzerinde yapar. Kuşak kodu, kuşak numarası, satır sırası ve eş eşleştirmesi için geçici sütunlar ekler. Ağaç çizildikten sonra bu sayfanın içeriği yeniden silinir. Sayfada ayrıca makro atanmış aile simgesinin iki eski kopyası bulunur; kullanıcı tarafından açılması gerekmez.

SoyAgaci: Çizilen Aile Ağacı​

Teslim edildiğinde boştur ve ağaç her başarılı aktarımda baştan çizilir. Her kişi ayrı bir hücrede ad ve soyadıyla, baş harfleri büyük olacak biçimde yazılır. Cinsiyet sütununda E yazan kişiler açık turkuaz zemin ve mavi kenarlıkla, K yazan kişiler pembe zemin ve mercan rengi kenarlıkla gösterilir. Her kutuya gizli bir açıklama notu eklenir; fare kutunun üzerine getirildiğinde notta adın yanında aktarılan tablonun H, L ve I sütunlarındaki bilgiler görünür. İşlem bitince bu sayfa otomatik olarak seçilir.

Makronun Çalışma Mantığı​

Dosyada formül bulunmaz; hesaplamanın tamamı iki VBA modülündedir. Birinci modüldeki SoyAgaciAktarim yordamı, Windows'ta açık olan Internet Explorer ve Dosya Gezgini pencerelerini listeleyen Microsoft Internet Controls kitaplığını kullanır ve başlığı Alt-Üst Soy Bilgisi Sorgulama ile biten pencereyi arar. Pencere bulunamazsa HATA başlıklı bir uyarı gösterilir ve makro hiçbir değişiklik yapmadan durur. Uyarıda, e-Devlet sistemine T.C. kimlik numarası ve şifre ile giriş yapılıp Alt Üst Soy Belgesi ekranı açıldıktan sonra işlem yapılması istenir. Pencere bulunursa sayfadaki tablo hücreleri sırayla okunur.
İkinci modüldeki AgacYap yordamı makro listesinde görünmez ve yalnız aktarımın sonunda çağrılır. Bu yordam her kişinin Yakınlık ifadesindeki kelimelerin baş harflerini birleştirerek bir kod üretir: Babası için B, Annesi için A, Babasının Annesi için BA gibi. Kendisi satırının kodu sıfırdır. Kodun harf sayısı kişinin kuşağını, harflerin sırası ise ağaçtaki dalını belirler. Yakınlığı Oğlu veya Kızı olan satırlar ile yakınlığı boş satırlar ağaçtan çıkarılır; bu nedenle çizilen ağaç bir üst soy ağacıdır ve çocuklar gösterilmez.
Makro ayrıca tabloda bulunan en eski kişilerin anne ve baba adlarından bir kuşak daha üretir. Bir kişinin baba veya ana adı tabloda yazılıysa ve o ebeveyn listede ayrı bir satır olarak yoksa, yalnız adıyla yeni bir kutu eklenir. Üçüncü ve daha eski kuşaklarda eklenen bu kişilerin durum bilgisine Ölüm-? yazılır. Satırlar koda ve kuşağa göre sıralandıktan sonra her kişinin ağaçtaki satırı hesaplanır, anne ve baba çiftleri eşleştirilir ve ağaç SoyAgaci sayfasına çizilir.

Öne Çıkan Özellikler​

• Tek tıkla aktarım: anasayfadaki simgeye tıklanması, tablo aktarımını ve ağaç çizimini birlikte başlatır.
• Kuşak düzeni: kişi en solda, her yeni kuşak bir sağdaki sütunda yer alır; anne ve baba kutuları çizgiyle bağlanır.
• Renkli cinsiyet ayrımı: erkek ve kadın bireyler farklı dolgu ve kenarlık renkleriyle gösterilir.
• Kutu notları: tablodaki ek bilgiler, ağacı kalabalıklaştırmadan her kutunun açıklama notunda saklanır.
• Bir kuşak ileri tamamlama: listede satırı olmayan en eski ebeveynler baba ve ana adı sütunlarından eklenir.
• Yerel çalışma: veriler tarayıcı penceresinden okunur ve yalnız açık çalışma kitabına yazılır.

Çalışma Koşulları ve Bilinen Sınırlar​

Dosya yalnız Windows üzerinde çalışan masaüstü Excel ile kullanılabilir ve makroların etkinleştirilmesi gerekir. Mac için Excel'de makronun kullandığı Windows pencere bileşeni bulunmaz; Excel Online ve mobil uygulamalarda ise VBA makroları çalışmaz.
En önemli koşul tarayıcıdır. Makro, e-Devlet sayfasını yalnız Internet Explorer türü bir pencere içinde bulabilir; Edge, Chrome veya Firefox'ta açılmış bir sayfayı göremez. Internet Explorer masaüstü uygulaması Microsoft tarafından kullanımdan kaldırılmıştır ve Windows 11 içinde yer almaz; güncel Windows 10 sistemlerinde de açılmak istendiğinde Edge'e yönlendirilir. Edge'in Internet Explorer modunda açılan bir e-Devlet sayfasının makro tarafından bulunup bulunmadığı doğrulanmamıştır. Bu nedenle dosya güncel Windows ve tarayıcı yapılandırmalarında aktarım yapamayabilir.
Makro, pencere başlığının Alt-Üst Soy Bilgisi Sorgulama ile bitmesini ve tablonun satır başına on iki hücreden oluşmasını bekler. e-Devlet sayfasının başlığı veya tablo düzeni değişmişse aktarım yapılamaz ya da sütunlar kayabilir. Dosyada tablonun elle girilmesi veya kaydedilmiş bir sayfadan okunması için ayrı bir yol bulunmaz.
Kod incelendiğinde bazı sınırlar daha görülür. Oğlunun Kızı gibi torun satırları ağaçtan çıkarılmaz ve ata satırı gibi işlendiği için yanlış yere yerleşebilir. VBA projesi parola ile kilitlidir, anasayfa ve çalışma kitabı yapısı korumalıdır; bu nedenle kodda veya sayfa düzeninde değişiklik yapılamaz. Yayın öncesinde dosyanın açıldığı ve e-Devlet penceresi yokken beklenen uyarıyı verip hiçbir sayfayı değiştirmeden durduğu kontrol edilmiştir. Gerçek bir e-Devlet oturumuyla uçtan uca aktarım ve ağaç çizimi, gerçek kimlik bilgisi ve gerçek aile verisi gerektirdiği için denenmemiştir.

Nasıl Kullanılır​

1. Dosya Windows'taki masaüstü Excel ile açılır ve güvenlik uyarısında makrolar etkinleştirilir.
2. Internet Explorer türü bir tarayıcı penceresinde e-Devlet Kapısı'na kişinin kendi T.C. kimlik numarası ve şifresiyle giriş yapılır. Kimlik bilgileri yalnız e-Devlet sayfasına girilir; Excel dosyası bu bilgileri istemez.
3. Alt-Üst Soy Bilgisi Sorgulama hizmeti açılır ve sonuç tablosunun ekranda görüntülendiği doğrulanır. Tarayıcı penceresi açık bırakılır.
4. Excel'de anasayfadaki aile simgesine tıklanır.
5. Pencere bulunursa tablo SoyBilgileri sayfasına aktarılır ve SoyAgaci sayfası açılarak ağaç gösterilir. Pencere bulunamazsa HATA uyarısı çıkar ve dosyada değişiklik yapılmaz.
6. Ağaçtaki kutuların üzerine gelinerek açıklama notlarındaki ek bilgiler okunabilir. Sonuç saklanacaksa çalışma kitabı yeni bir adla kaydedilir.

e-Devlet penceresi bulunduğunda makro SoyBilgileri ve SoyAgaci sayfalarını baştan temizler. Önceki bir ağacın korunması gerekiyorsa yeni sorgudan önce dosyanın ayrı bir kopyası kaydedilmelidir.

Kimler İçin Uygun​

Dosya, kendi alt üst soy belgesindeki kuşakları görsel bir aile ağacı halinde görmek isteyen kişiler, aile geçmişini araştıranlar ve VBA ile bir web sayfasındaki tablonun Excel'e nasıl aktarılabileceğini incelemek isteyen kullanıcılar için uygundur. Dosya, Internet Explorer erişimi bulunan eski Windows ortamları için tasarlanmıştır; güncel sistemlerde ise öncelikle tarayıcı koşulunun sağlanıp sağlanamadığının değerlendirilmesi gerekir.
Sonuç olarak bu çalışma kitabı, e-Devlet alt üst soy tablosunu tek tıkla Excel'e alıp kuşaklara ayrılmış bir soy ağacına dönüştüren sade bir makro çözümüdür; kullanım öncesinde tarayıcı bağımlılığının ve yukarıda belirtilen sınırların dikkate alınması önerilir.