İşlem Tabloları Dersi 8. Ünite Özet

Giriş

Veri analizi karar vermeyi desteklemek, sonuç çıkarmak ve veriden anlamlı bilgileri türetmek için verinin kontrol edilmesi, temizlenmesi, dönüştürülmesi ve modellenmesi sürecidir. İşlem tabloları verileri hücreler şeklinde organize eden yapısı, işlev kütüphaneleri, dış veri kaynakları ile beraber çalışma olanaklarının yanı sıra durum çözümlemesi, istatistiksel analizler ve en iyi değerin belirlenmesine yönelik analiz araçları sunmaktadır. Excel’in verileri hücreler şeklinde organize ederek birbirleri arasında ilişki kurma, formül oluşturma gibi olanaklar kullanıcıların veri üzerinde istedikleri analiz hesaplamaları yapmalarına olanak vermektedir.

Excel ve Veri Analiz Araçları

Excel verilerin analiz edilmesi için birçok araç ve eklenti sunmaktadır. Bu araçlar istatistiksel analizler, veri madenciliği eklentileri, eniyilemeye yönelik matematiksel modeller farklı problemlerin çözülmesinde ya da verinin analiz edilmesinde kullanılabilmektedir.

Excel kullanıcıların karar vermelerine yönelik kullanılabilecek aşağıdaki işlev ve araçları içermektedir:

• Sıralama,
• Filtreleme,
• Koşullu biçimlendirme,
• Durum Çözümlemesi (What-if),
• Pivot Tablo ve Pivot Grafik,
• Çözücü (Solver),
• Çözümleyici Araç Takımı (Analysis ToolPak)

Listenin ilk üç elemanı bir önceki ünitede anlatılan verinin yönetilmesi ve anlaşılmasına yönelik temel araçlardır. Günlük hayatımızda ve iş süreçlerinde verilerin sıralanması, istenilen verilerin süzülmesi ve belirli koşullara uyan hücrelerin otomatik olarak şekillendirilmesi yaygın olarak kullanılan araçlardır.

Durum çözümlemesi varsayımsal durumların modellenerek farklı girdi değerleri ile sonucun ne olacağını hızlı bir şekilde hesaplama ve değerlendirme imkânı sunan analiz araçlarıdır.

Bilgi sistemlerinde üretilen ya da farklı şekillerde toplanan uzun veri listelerinin analiz edilmesinde Pivot Tablo ve Pivot Grafikler yaygın olarak kullanılmaktadır. Bu araçlar veri tablolarında sayısal ve metin verilerin gruplandırılması, özetlenmesi, eğilimlerin saptanması gibi farklı analiz ihtiyaçları için kullanışlı araçlardır.

Çözümleyici ve Çözümleyici Araç Takımı Excel’e eklenti olarak dahil edilebilen ve uygulanması bazı matematiksel ve istatistiksel konularda bilgi sahibi olunmasını gerektiren araçlardır.

Durum Çözümlemesi

Durum çözümlemesi (what if) analizleri varsayımsal durumların analiz edilmesi için en yaygın kullanılan veri analiz tekniğidir. İşlem tablolarının formül yapısı ile oluşturulan karar modellerinin en temel üstünlüğü veri ve parametrelerin değişmesi durumunda sonucun otomatik olarak güncellenebilmesidir. Bu sayede karar vericiler olası durumlarda sistemin davranışları konusunda fikir sahibi olabilmektedirler.

Durum Çözümlemesinde Veri Tablosu:

Bu işlem için aşağıdaki adımların izlenmesi gerekir:

• Adım 1: Modelde değişikliği izlenmek istenen parametrelerin satır ya da sütunda yer alacağı bir tablo hazırlanır.
• Adım 2: Hazırlanan tablonun sol üst köşesine hesaplanması istenen modeldeki ilgili sonuç hücresinin adresi girilir.
• Adım 3: Hesaplamanın yapılacağı tablo satır ve sütunları ile birlikte seçilir. •
• Adım 4: Komut şeridinde “veri” sekmesinde yer alan “Durum Çözümlemesi” düğmesindeki “veri tablosu” komutu seçilir.
• Adım 5: Açılan “veri tablosu” iletişim kutusunda tablodaki sütun ve satır değerlerinin modelde hangi hücrelere girilerek hesaplama yapacağını belirlemek için adresler tanımlanır.
• Adım 6: Tamam tuşuna basılarak tüm tablonun hücreleri için modelin hesaplanması sağlanır.

Durum Çözümlemesinde Hedef Arama: Bir modelde ulaşılmak istenen sonuç biliniyor ve girdi parametrelerin alması gereken değer hesaplanmak isteniyorsa “Hedef Arama” aracı kullanılır. Bunun için izlenmesi gereken adımlar aşağıdaki gibidir:

• Adım 1: Komut şeridindeki “Veri” sekmesinde yer alan “Durum Çözümlemesi” düğmesinde yer alan “Hedef Arama” komutu seçilir.
• Adım 2: “Hedef Ara” iletişim kutusunda “aranacak hücre” kısmına elde edilmek istenen sonuç, “Sonuç hücre” kısmına ulaşılmak istenen değer, “Değişecek hücre” kısmına hangi girdi parametresinin değiştirileceği işaret edilir.
• Adım 3: İstenilen hesaplama yapılarak eğer uygun bir çözüm varsa kullanıcıya görüntülenir. Kullanıcı sonucun tabloya yansıtılması için “Tamam”, iptal edilmesi için “İptal” tuşuna basması gerekir.
• Adım 4: Hesaplanan değer tabloya yansıtılır.

Durum Çözümlemesinde Senaryo Yöneticisi: Senaryo yöneticisi durum çözümlemesinde öngörülen durumların otomatik olarak hesaplanmasını sağlayan bir araçtır. Farklı girdi parametreleri ile hangi kâr oranlarının elde edilebileceğini “Senaryo Yöneticisi” ile gerçekleştirmek için aşağıdaki işlemler uygulanmalıdır:

• Adım 1: Komut şeridindeki “Veri” sekmesinde yer alan “Durum Çözümlemesi” düğmesinde yer alan “Senaryo Yönetimi” komutu seçilir.
• Adım 2: Açılan “Senaryo Yöneticisi” iletişim kutusundan “Ekle” komutuna basılarak bir senaryo ekleme talimatı verilir.
• Adım 3: “Senaryo Düzenleme” iletişim kutusundan “senaryo adı” ve modeldeki girdi parametrelerinin hücre adresleri yazılır (ya da fare yardımıyla seçilir) ve “Tamam” düğmesine basılır.
• Adım 4: Senaryo değerleri tanımlanır (Örnekte saatlik işçilik maliyeti ve hammadde maliyetleri ilgili senaryo için girilir).
• Adım 5: “Senaryo Yönetici” ekranına dönülerek eklenecek senaryo sayısı kadar Adım 3 ve 4 tekrar edilir.
• Adım 6: “Senaryo Yöneticisi” ekranında eklenen senaryolar seçilerek “Göster” tuşuna basılabilir. Bu şekilde kar analizi tablosunda ilgili senaryo sonucunda oluşan sonuçlar izlenebilir. Bir diğer seçenek ise tüm senaryoların değerlendirilebilmesine yönelik özet bir tablo hazırlanabilir.

Pivot Tablo ve Grafikler

PivotTable, işlem tabloları ve iş analitiği yazılımlarında verinin özetlenmesi için yaygın olarak kullanılan araçlara verilen isimdir. PivotTable bir veritabanından ya da veri tablosundan dinamik olarak üretilen özet raporlar olarak tanımlanabilir. PivotTable bir veritabanı yönetim sisteminden getirilen dış veri tablosunu ya da Excel çalışma sayfasında yer alan bir veri listesinin hızlı bir şekilde analiz edilmesini sağlayacak işlevlere sahiptir. Çok sayıda satır ve sütundan oluşan verilerin istenilen düzende görüntülenmesinin yanı sıra PivotTable’ın özetleme, gruplama ve filtreleme işlemlerinin interaktif bir şekilde uygulanması kullanıcıların bu aracı etkin olarak kullanılmasını sağlamaktadır. PivotTable aracı aşağıdaki analiz ihtiyaçlarına yanıt verebilir:

• Benzersiz Değerler: Büyük bir veri kümesi ile çalışırken alanların içerdiği değerler kümesinin (etki alanı) elde edilmesi gerekebilir.
• Özetler: PivotTable araçları uzun veri listelerinin özetlenmesi için etkin ve interaktif bir analiz ortamı sunar. Kullanıcılar ilgilendikleri alanların diğer alanlara göre kırılımlarını kolayca hesaplayabilir.
• lişkiler: Büyük veri kümeleri ile çalışırken alanlar arasındaki ilişkilerin belirlenmesi oldukça sık karşılaşılan durumlardır. Hangi meslekteki müşterilerin hangi ürünü tercih ettikleri, cinsiyete göre alışveriş alışkanlıkları gibi ilişkiler PivotTable ile araştırılabilir.
• Eğilimler: Veri kümelerinde zamana bağımlı olayların incelenmesinde PivotTable araçları kullanılabilmektedir. Tarih bilgisi yer alan bir alanı aylara, haftalara ve günlere göre otomatik olarak sınıflayabilmesi eğilimlerin belirlenmesinde kolaylık sağlar.

PivotTable Oluşturma: PivotTable oluşturmak için aşağıdaki adımlarla başlanmalı ve ardından alanları ilgili bölmelere taşıyarak istenilen rapora ulaşılmalıdır:

• Adım 1: PivotTable oluşturulacak veri tablosu sütun başlıklarını da kapsayacak şeklide seçilmelidir. Daha önce bir “Tablo” yapısı olarak belirlenmiş veri listelerinde ise etkin hücrenin ilgili tablo üzerinde bulunması bir sonraki adım için yeterli olmaktadır.
• Adım 2: Komut şeridinde “Ekle” sekmesinde yer alan “PivotTable” düğmesine tıklanır. (Aynı sekmedeki “PivotTable Önerileri” komutu da mevcut veri incelenerek kullanıcının tercih edebileceği hazır özet tablo seçenekleri sunar.)
• Adım 3: “PivotTable Oluştur” iletişim kutusunda analiz için kullanılacak veri tablosu önceden seçildiğinden yazılı olarak gelecektir. PivotTable raporunun oluşturulacağı adres de bu ekranda tanımlanır. En yaygın kullanım yeni bir çalışma sayfasında PivotTable raporunun oluşturulmasıdır. Kullanıcılar varsayılan rapor konumunu istedikleri bir veri sayfasındaki bir hücreye de tanımlayabilirler. PivotTable raporları oluşturma sürecinde tabloların boyutunun değişmesi ilgili veri sayfasındaki mevcut hücreleri kaplayabileceğinden genellikle boş bir sayfada raporun oluşturulması tercih edilir.

PivotTable Ögeleri ve İşlemleri: PivotTable raporunda satır ve sütunlara sürüklenen alanların değerleri tekil olarak görüntülerken tablo içerisinde görüntülenen sayılar, değerler bölmesine sürüklenen alandan hesaplanmaktadır. Böylece dikey kaynak veritablosunda Pivortable raporunun satır ve sütunlarına göre ilgili hücrelerin hesaplanması sağlanır. Excel varsayılan olarak sayısal veri türü için “Toplama” metin veri türü için “sayma” işlemini gerçekleştirir. Table aracı değerler üzerinde gerçekleştirilebilecek farklı hesaplama seçenekleri sunmaktadır.

Bu hesaplamalar kısaca şöyle açıklanabilir:

• Toplam: Sayısal veri türündeki alalar için kullanılabilir. Hücreler üzerinde toplama işlemi gerçekleştirilir.
• Say: Kaynak veri kümesindeki hücrelerin sayısını görüntüler.
• Ortalama: Sayısal değerlerin ortalaması hesaplanır.
• En Büyük: Sayısal alanlardaki en büyük değeri getirir.
• En Küçük: Sayısal alanlardaki en küçük değeri getirir.
• Çarpım: Sayısal değerlerin birbiri ile çarpılarak sonucu görüntülenir.
• Sayıları Say: Sayısal olan hücreler sayılarak görüntülenir.
• StdSapma: Standart sapmayı bir örneğe bağlı olarak tahmin eder (Standart sapma, değerlerin ortalama değerden (ortalama) ne kadar uzaklaştığının ölçümüdür).
• StdSapmaS: Bağımsız değişkenler olarak verilen tüm popülasyon temelinde standart sapmayı hesaplar.
• Değişken: Varyansı, bir örneğe bağlı olarak tahmin eder hesaplar.
• Vars: Varyansı, tüm popülasyon temelinde hesaplar.

PivotTable veri analizi aracının sınırlılıkları şöyle sıralanabilir:

• En fazla 1.048.576 adet veri satırlar halinde sıralanabilir (Bu limit bir Excel çalışma sayfasının en fazla satır sayısı olup Excel 2007 ve öncesinde 65.536 satır ile sınırlıydı).
• PivotTablo’da sütün olarak en fazla 16.384 adet sütun oluşturulabilir (Excel 2007 ve öncesinde 256 sütun ile sınırlıydı).
• En fazla Sayfa sayısı 256 ve en fazla 256 adet alan (veri listesi sütunu) PivotTable ile işlenebilir.
• Ayrıca kullanılan bilgisayarın hafızası da bu işlemler için bir sınır oluşturabilmektedir. Excel de bir veri tablosunda 2016 sürümü ile en fazla 1.048.576 adet satırı olan verilerin listesi oluşturulabilmekte bu da daha büyük veri tablolarının özetlerinin oluşturulmasına engel olabilmektedir. Fakat dış kaynaklardan sağlanan verilerin analiz edilmesi için PowerQuery, PowerPivot eklentileri milyonlarca satır verinin işletme veri tabanlarından sağlanarak özetlenmesine olanak sağlayacak yapıdadır.

PivotChart Oluşturma: PivotChart oluşturmak için kullanılan işlev ve ara-yüzler hemen hemen PivotTable ile aynıdır. Elde edilen PivotTable verilerinin grafiğe dönüştürülmesi için grafik işlemlerini kullanmak gerekir. Ünite örnek verisi için tarih bilgisine göre bayan ve erkek müşterilerin satın aldığı toplam tutarların grafiği çizilmek istenirse öncelikle grafik türüne karar verilmelidir. Bu örnekte satış tutarlarının zamana göre incelenebilmesi için grafik türü çizgi grafik olarak tercih edilmiştir.

Çözücü ve Çözümleyici Araç Takımı

“Çözümleyici Araç Takımı” karmaşık problemlerin çözümü için yöneylem araştırması yöntemlerini ve istatistiksel teknikleri içermektedir.

Çözücü aracının amacı belirlenen kısıtlara uyarak hedef hücrenin değerini en iyi olacak şekilde karar değişkenlerinin değerini belirlemektir.

Çözücü(Solver) eklentisi benzer yapıda birden fazla karar değişkeni olan en iyileme problemlerinde kullanılabilen gelişmiş bir araçtır. Çözücü(Solver) de tanımlanması gereken üç ana öge vardır:

• Değişkenler: Karar vericinin değerini belirleyebileceği değişkenlerdir. Çözücü en iyi sonucu bulununcaya kadar bu değişkenlerin değerini değiştirir.
• Kısıtlamalar: Her sistemde kullanılan kaynak ya da farklı çevresel etkiler nedeniyle sınırla mevcuttur. Çözücü problemdeki sınırlamaları hücrelerin alacağı değerleri sınırlayarak sağlar.
• Hedef: Kâr, maliyet, üretim miktarı gibi sistemde en iyi değerinin araştırıldığı değerdir. Karar değişkenlerinin de içinde yer aldığı bir formülden oluşmaktadır.

Doğa sporları için araç kiralayan bir firmanın üç farklı ürünün hangisinden kaçar adet alacağına ilişkin bir karar problemini olduğunu varsayalım. Firmanın satın alma işlemindeki kısıtları ürünler için ödenecek toplam tutar (250 bin TL), ürünlerin kiralanmadığı zaman konulacağı depo alanı (350 m ² ) ve satıcının elindeki ürün sayılarıdır. Karar verici her birimden elde edeceği kar miktarını da bildiğine göre hangi üründen kaç tane alacağına karar vermek durumundadır.

Bu problemi Çözücü ile çözmek için aşağıdaki adımların uygulanması gerekmektedir.

• Adım 1: Komut şeridinde “Veri” sekmesinde yer alan Çözücü eklentisi çalıştırılır. Problemin en iyi değeri aranan hedef hücresi seçilir. Bu problemde toplam kar değerini hesaplayan hücre seçilir.
• Adım 2: Problemde hedef hücrenin değerinin en büyük, en küçük ya da tanımlanan bir değere mi ulaşılacağı belirtilir.
• Adım 3: Karar değişkenlerinin yani modelin değerini belirleyeceği hücrelerin adresleri tanımlanır. Örnek problemde bisiklet, motosiklet ya da atv aracı alınacağı sayılarıdır.
• Adım 4: Karar değişkenlerinin alacağı değerleri kısıtlayan kısıtlayıcılar tek tek eklenir. Bu problemde beş adet kısıt tanımlanmıştır.
• Adım 5: “Kısıt Ekle” iletişim kutusunda her kısıtı teker teker eklenerek çözücü menüsüne geri dönülür. Örnek problemde beş adet kısıt tanımlanmıştır. Bunlardan üçü, alınacak ürün sayısı satıcının elindeki ürün sayısından büyük olamayacağıdır. Ürünler için harcanacak toplam tutarın 250 bin TL den küçük olması gerektiği kısıtı ve tüm araçları depolamak için toplam 350 metrekare alan sınırıdır.
• Adım 6: Çözümleme yöntemi olarak “Basit LP” seçeneği seçilir ve çözüm uygulanır.

Çözümleyici Araç Takımı: “Çözümleyici Araç Takımı” (Analysis ToolPak) istatistiksel ve mühendislik analizlerinin daha kolay ve hızlı yapılabilmesi için oluşturulmuş bir Excel eklentisidir. Kullanıcılar bu eklenti ile çok daha az işlem ve zamanda belirli analizleri gerçekleştirebilmektedir.

Verisi ve parametreleri kullanıcı tarafından tanımlanan “Çözümleyici Araç Takımı” analiz çıktılarını Excel çalışma sayfalarında tablolar, grafikler ve açıklamalar şeklinde raporlayabilmektedir.

Çözümleyici Araç Takımı analiz listesine eklenti yüklü olduğu takdirde komut şeridinin “Veri” sekmesinden ulaşılabilmektedir. Çözümleyici Araç Takımının sunduğu çözümleme araçları şunlardır:

• Anova,
• Anova: Tek Etmen,
• Anova: Yinelemeli Çift Etmen,
• Anova: Yinelemesiz Çift Etmen,
• Korelasyon,
• Kovaryans,
• Tanımlayıcı İstatistik,
• Üstel Düzeltme,
• F Sınaması: Varyanslar İçin İki Örnek,
• Fourier Çözümlemesi,
• Histogram,
• Hareketli Ortalama,
• Rastgele Sayı Üretimi,
• Derece ve Yüzdebirlik,
• Regresyon,
• Örnekleme,
• t Sınaması,
• t Sınaması: Ortalamalar için Eşli İki Örnek,
• t Sınaması: Varyansları Eşit Kabul Edilen İki Örnek,
• t Sınaması: Varyansları Eşit Kabul Edilmeyen İki Örnek,
• z Sınaması.