İller Arası Mesafe Matrisi Oluşturma

İl Çiftlerine Ait Mesafeleri Excel Üzerinde Üçgen Çizelgeye Dönüştürün​

İki şehir arasındaki mesafeler uzun bir kayıt listesinde tutulduğunda, farklı il çiftlerini karşılaştırmak için satırların tek tek aranması gerekebilir. Bu çalışma kitabı, şehir çiftleri ve mesafe değerlerinden oluşan kaynak listeyi tek bir dinamik dizi formülüyle düzenli bir çizelgeye dönüştürür. Kaynak veriler ayrı sayfada korunur; sonuç sayfasında şehir adları köşegen üzerinde, mesafeler alt üçgen bölümünde gösterilir. Dosyanın yaptığı işlem güncel bir harita hizmetinden rota hesaplamak değildir. Mevcut çalışma kitabında bulunan sayısal mesafeler, ilgili il çiftlerine yerleştirilir. Bu ayrım, dosyanın sonuçlarının doğru yorumlanması için temel noktadır.

İki Sayfanın Görevleri​

Çalışma kitabında Veriler ve Çizelge adlı iki görünür çalışma sayfası bulunmaktadır. Veriler sayfasında A1, B1 ve C1 hücrelerinde sırasıyla Şehir 1, Şehir 2 ve Mesafe başlıkları yer alır. Kayıtlar ikinci satırdan başlayıp 3241'inci satıra kadar devam eder. Böylece kaynak listede 3240 il çifti ve her çift için bir sayısal mesafe değeri bulunmaktadır. Bir satırın ilk iki sütunu, mesafenin hangi şehir çifti için saklandığını belirler; üçüncü sütun o çiftin değerini içerir. Dosyada bu veriler Excel tablosu nesnesi olarak tanımlanmamıştır; formül doğrudan hücre aralıklarına başvurur.
Çizelge sayfasında hesaplamanın başlangıcı A1 hücresidir. Bu hücredeki LET tabanlı formül, sonuçlarını 81 satır ve 81 sütundan oluşan alana yayar. Mevcut veri kümesindeki şehir adları, iki kaynak şehir sütununun birlikte değerlendirilmesiyle elde edilir. Tekrarlanan şehir adları kaldırılır ve sıralı bir liste oluşturulur. Çizelgenin köşegenindeki etiketler bu listeyi temsil eder. Örneğin A1 hücresinde ADANA, B2 hücresinde ADIYAMAN ve C3 hücresinde AFYON adları bulunur. Aynı alanın üst üçgen bölümünün boş bırakılması, formülün tasarlanmış görüntüleme tercihidir; eksik hesaplama olarak değerlendirilmemelidir.

Üçgen Matrisin Okunması​

Bir şehir çiftinin değeri, ilgili iki köşegen etiketinin satır ve sütun kesişiminden okunur. Çizelgede ADANA ile ADIYAMAN çifti için A2 hücresinde 339 değeri bulunmaktadır. ADANA ile AFYON çifti A3 hücresinde 578 olarak gösterilir. ADIYAMAN ile AFYON çifti B3 hücresinde 917 değerini verir. Bu örnekler, kaynak satırların alt üçgen alana nasıl yerleştirildiğini açıklamak içindir. Başlıklardaki şehir adlarının köşegen hücrelerinde bulunması nedeniyle, normal bir tablodaki ayrı üst başlık satırı veya ayrı sol etiket sütunu yapısından farklı bir düzen görülür.
Alt üçgen bölümünde 3240 şehir çifti bulunmaktadır. Bu sayı, 81 farklı şehir arasında iki şehirden oluşan bütün farklı çiftlerin sayısıyla uyumludur. Aynı şehir çiftinin üst bölümde yeniden yazılmaması, çizelgenin tekrar eden değerlerden arındırılmış bir görünüm sunmasını sağlar. Köşegen hücreleri sayısal sıfır değil şehir adıdır. Dolayısıyla bu çizelge, köşegeni sıfır olan tamamen sayısal ve iki yönde doldurulmuş simetrik bir uzaklık matrisi değildir. Başka bir analiz bu tür sayısal matris istiyorsa, dosyanın mevcut gösterimi doğrudan aynı biçimi sağlamaz. Mevcut çıktı, şehir çiftlerinin tek yönlü yerleşimini okunur biçimde sunar.

Şehir Listesinin Oluşturulması​

Formül, Veriler sayfasındaki A2:A3241 aralığını ilk şehirler, B2:B3241 aralığını ikinci şehirler ve C2:C3241 aralığını mesafeler olarak kullanır. LET işlevi bu aralıkları hesaplama içinde kısa adlarla tanımlar. VSTACK, iki şehir listesini alt alta birleştirir. UNIQUE, birleşik listedeki tekrarlanan şehir adlarını kaldırır. SORT ise sonuç listesini sıralar. Böylece bir şehir yalnızca kaynak listenin ikinci sütununda yer alsa bile sonuç listesinden dışlanmaz. Şehir listesi yalnız ilk sütun üzerinden oluşturulmadığı için kaynak çiftlerin hangi yönde yazıldığına bağlı olarak şehirlerin kaybolması önlenir.
TOROW, elde edilen sıralı şehir listesini yatay bir diziye çevirir. Dikey ve yatay şehir dizilerinin birleştirilmesi, olası şehir eşleşmelerinin arama anahtarlarını oluşturur. Mevcut dosyada şehir adları büyük harflerle tutulmaktadır. AFYON, KOCAELİ (İZMİT) ve SAKARYA (ADAPAZARI) gibi kaynak yazımları, veri kümesinin kendi etiketleridir. Bunların güncel idari adlandırmalarla otomatik olarak değiştirildiği veya internetten doğrulandığı varsayılmamalıdır. Aynı şehrin farklı yazımlarının birleştirilmesine yönelik ayrı bir temizlik sözlüğü bulunmaz. Kaynak şehir adlarında değişiklik yapılırken tutarlı yazım kullanılması, benzersiz şehir listesinin doğru oluşması açısından önemlidir.

Mesafelerin İki Yönde Aranması​

XLOOKUP işlevi, oluşturulan şehir eşleşmelerini kaynak çiftlerde arar ve ilgili mesafe sütunundan sonuç alır. İlk arama, kaynak listedeki Şehir 1 ve Şehir 2 sırasını kullanır. Eşleşme bu sırayla bulunmazsa IFNA üzerinden ikinci arama yapılır; bu kez kaynak şehir sütunlarının sırası ters çevrilir. Örneğin ADANA ile ADIYAMAN çifti, kaynak satırda ADIYAMAN ve ADANA olarak yer alsa da aynı sayısal mesafe ilgili sonuç hücresine getirilebilir. Bu özellik, kaynak kayıtların iki şehir adını tek bir yönde saklamasıyla birlikte çalışır. Kaynakta aynı çiftin iki kez bulunması için ayrı bir uyuşmazlık çözüm kuralı mevcut değildir.
Ters yönlü aramada da kayıt bulunamazsa formül sıfır değerini kullanır. Kaynak mesafe hücresinin boş bırakılması da test edilen örnekte sonuçta sıfır olarak gözlenmiştir. Bu nedenle çizelgedeki sıfır değeri her durumda gerçekten sıfır mesafe anlamına gelmez; eksik bir kayıt veya boş kaynak değerinin sonucu olabilir. Dosyada eksik mesafeleri ayrı renkle gösteren bir denetim alanı, hata listesi veya veri tamlık raporu bulunmamaktadır. Kaynak liste tamamlanmadan sonuçların kullanılacağı durumlarda bu davranış dikkate alınmalıdır. Formül mesafe değerini üretmez; var olan değeri arar ve bulunamadığında tanımlı varsayılanı uygular.

Sonuç Alanının Kurulması​

MAKEARRAY işlevi, mesafe arama sonucunun satır ve sütun sayısına göre sonuç alanını kurar. İçindeki LAMBDA yordamı, oluşturulan her hücrenin satır ve sütun konumunu değerlendirir. IFS ile üç farklı yerleşim seçilir. Satır ve sütun numarası eşitse ilgili şehir adı yazılır. Sütun numarası satır numarasından büyükse hücre boş bırakılır. Diğer konumlarda, arama sonucundan ilgili mesafe INDEX ile alınır. Bu yapı, yalnızca değerlerin toplanması veya ayrı ayrı hücre formüllerinin aşağıya kopyalanması şeklinde çalışmaz; A1 hücresindeki tek formül bütün sonuç görünümünü üretir.
Çizelgenin yayılan alanında bulunan hücrelerin üzerine elle veri yazılması, dinamik dizi sonucunu engelleyebilir. Hesaplama formülünün bulunduğu A1 hücresi korunmalıdır. Kaynak mesafeler değiştirilecekse bu değişiklikler Veriler sayfasında yapılmalıdır. Mevcut formülün kaynak sınırı 3241'inci satırdır. Yeni kayıtların bu sınırın dışına eklenmesi, onların kendiliğinden hesaba katılmasını sağlamaz; hücre başvurularının da uyarlanması gerekir. Dosyanın mevcut 81 şehir ve 3240 çiftlik örneği korunarak çalışılması, formülün mantığını incelemek için yeterlidir. Rastgele boş satırlar, farklı şehir yazımları ve yinelenen çiftler için otomatik veri temizliği vaat edilmez.

Verinin Güncelliği ve Kullanım Amacı​

Çalışma kitabının mevcut sürümü, içinde saklanan tarihsel mesafe verilerini kullanır. Güncel yol durumu, trafik yoğunluğu, ücretli geçiş, alternatif güzergâh veya yol kapanması gibi bilgiler alınmaz. Kaynak sayfada verilerin son kontrol tarihini veren ayrı bir alan veya dış veri yenileme bağlantısı bulunmamaktadır. Bu nedenle dosya, güncel bir navigasyon uygulaması yerine geçmez. Şehir çiftlerini karşılaştıran bir Excel örneği, mevcut verilerin düzenlenmesi veya dinamik matris formülünün incelenmesi için kullanılabilir. Gerçek yolculuk ve sevkiyat kararlarında mesafe kaynağının güncelliği ayrıca değerlendirilmelidir; çizelgenin kendisi bu doğrulamayı yapmaz.
Dosyada VBA makrosu, Power Query sorgusu, dış bağlantı, veri doğrulama listesi veya grafik bulunmaz. Hesaplama gelişmiş dinamik dizi işlevleriyle oluşturulmuştur. LET, VSTACK, TOROW, MAKEARRAY, LAMBDA ve XLOOKUP işlevlerini destekleyen güncel bir Excel sürümü gerekir. xlsx uzantısının varlığı, eski Excel sürümlerinin bütün bu işlevleri çalıştırabildiği anlamına gelmez. Standart bir hücre tablosu gibi görünse de sonuç alanının dinamik olması, eski sürümlerde uyumluluk değerlendirmesini özellikle önemli hale getirir. Mevcut dosyaya çalışması gerekmeyen bir makro eklenmemiş veya yeni bir çevrim içi servis bağlanmamıştır.

Kullanım ve İşlev Kontrolü​

Kullanımda önce Veriler sayfasındaki üç sütunun ilişkisi incelenir. Bir şehir çiftinin mesafesi değiştirilecekse ilgili satırın C sütunundaki değeri düzenlenir. Çizelge sayfasındaki sonuç, Excel yeniden hesaplaması sonrasında kontrol edilir. Şehir etiketlerinin köşegen üzerinde olduğu ve yalnız alt üçgen bölümde mesafe bulunduğu dikkate alınır. Sonuç alanına elle yazmak yerine kaynak kaydın güncellenmesi gerekir. Farklı bir veri kümesi hazırlanacaksa kaynak aralıkları, şehir adlarının tutarlılığı ve eksik mesafelerin sıfır dönüşü birlikte değerlendirilmelidir. Değişiklik öncesinde özgün çalışma kitabının bir kopyasının saklanması, aynı örnekle karşılaştırma yapılmasını kolaylaştırır.
Gerçek Excel kontrolünde bütün 6561 sonuç hücresi incelenmiş; şehir etiketleri, boş üst üçgen ve alt üçgendeki 3240 mesafe, kaynak çiftlerle karşılaştırılmıştır. ADANA ile ADIYAMAN örneğinin 339 değeri doğrulanmıştır. Geçici test kopyasında kaynak mesafe 987 olarak değiştirildiğinde sonucun değiştiği kontrol edilmiş, aynı kaynak çiftin şehir sırası ters çevrildiğinde de doğru değerin getirildiği görülmüştür. Kaydetme, yeniden açma ve yeniden hesaplama kontrolü yapılmıştır. Özgün çalışma kitabı çalışır durumda olduğundan gereksiz bir formül revizyonu uygulanmamıştır. Galeride iki görünür sayfanın gerçek Excel görüntüleri ile matrisin genel görünümü ve okunur ayrıntı kesiti bulunmaktadır.