Aramalar, Grafikler, İstatistikler ve Özet Tablolar
Temel işlevleri, hücre referanslarını ve tarih ve saat işlevlerini inceledikten sonra, şimdi Microsoft Excel'in daha gelişmiş özelliklerinden bazılarına dalıyoruz. Finansta klasik sorunları çözme yöntemleri, satış raporları, nakliye masrafları ve istatistikler sunuyoruz..
OKUL GEÇİŞİ- Neden Formül ve İşlevlere İhtiyacınız Var??
- Formül Tanımlama ve Oluşturma
- Göreceli ve Mutlak Hücre Referansı ve Biçimlendirme
- Bilmeniz Gerekenler Faydalı İşlevler
- Aramalar, Grafikler, İstatistikler ve Özet Tablolar
Bu işlevler işletme, öğrenciler ve sadece daha fazla bilgi edinmek isteyenler için önemlidir..
VLOOKUP ve HLOOKUP
Dikey arama (VLOOKUP) ve yatay arama (HLOOKUP) fonksiyonlarını göstermek için bir örnek. Bu işlevler bir sayı veya başka bir değeri anlaşılır bir şeye çevirmek için kullanılır. Örneğin, VLOOKUP'ı bir parça numarası almak ve öğe açıklamasını geri dönmek için kullanabilirsiniz..
Bunu araştırmak için, Jane'in okula ne giyeceğine karar vermeye çalıştığı 4. Bölüm'deki “Karar Verici” elektronik tablomuza geri dönelim. Artık ne giydiği ile ilgilenmiyor, çünkü yeni bir erkek arkadaşına iniyor, bu yüzden şimdi rastgele kıyafetler ve ayakkabılar giyecek.
Jane'in elektronik tablosunda kıyafetleri dikey sütunlarda ve ayakkabılarda, yatay sütunlarda listeler..
Elektronik tabloyu açar ve RANDBETWEEN (1,3) işlevi, giyebileceği üç kıyafet türüne karşılık gelen bir ile üç arasında bir sayı oluşturur ya da ona eşit bir sayı oluşturur..
Beş ayakkabı türünden birini seçmek için RANDBETWEEN (1,5) işlevini kullanıyor.
Jane bir numara giyemediğinden, bunu bir isme dönüştürmemiz gerekiyor, bu yüzden arama işlevlerini kullanıyoruz..
Kıyafet numarasını kıyafet adına çevirmek için VLOOKUP işlevini kullanıyoruz. HLOOKUP ayakkabı numarasından satırdaki çeşitli ayakkabı türlerine çevirir.
Elektronik tablo, kıyafetler için böyle çalışır:
Üç kıyafet seçeneği olduğu için Excel bir ilâ üçte rastgele bir sayı seçiyor.
Daha sonra formül, sayıyı A2: B4 aralığına bakmak için B11'den gelen rasgele sayı kullanan = VLOOKUP (B11, A2: B4,2) kullanarak sayıya dönüştürür. Daha sonra ikinci sütunda listelenen verilerden sonuç (C11) verir.
Ayakkabı seçmek için aynı tekniği kullanıyoruz, ancak bu sefer HLOOKUP yerine VOOKUP kullanıyoruz..
Örnek: Temel İstatistikler
Neredeyse herkes istatistiklerden bir formül biliyor - ortalama - ancak iş için önemli olan başka bir istatistik var: standart sapma.
Örneğin, üniversiteye giden birçok kişi SAT puanlarından dolayı acı çekti. Diğer öğrencilere göre nasıl sıralandıklarını bilmek isteyebilirler. Üniversiteler de bunu bilmek istiyor, çünkü birçok üniversite, özellikle prestijli üniversiteler, düşük SAT puanına sahip öğrencileri geri çeviriyor.
Peki, ya da bir üniversite, SAT puanlarını nasıl ölçebilir ve yorumlayabiliriz? Aşağıda 1.870'den 2.230'a kadar değişen beş öğrenci için SAT puanları verilmiştir..
Anlamak için önemli sayılar:
Ortalama - Ortalama aynı zamanda “ortalama” olarak da adlandırılır.
Standart Sapma (STD veya σ) - Bu sayı, bir sayı kümesinin ne kadar geniş dağıldığını gösterir. Standart sapma büyükse, sayılar birbirinden uzaktır ve sıfır ise, tüm sayılar aynıdır. Standart sapmanın ortalama değer ile gözlenen değer arasındaki ortalama fark olduğunu söyleyebiliriz, yani 1,998 ve her SAT puanı. Lütfen dikkat: Yunanca sigma işareti σ.
Yüzdelik Sıra - Bir öğrenci yüksek bir puan aldığında, ilk yüzde 99 ya da öyle bir şeyle övünürler. “Yüzdelik sıra”, puanların yüzdesinin belirli bir puandan düşük olduğu anlamına gelir.
Standart sapma ve olasılık yakından bağlantılıdır. Her standart sapma için, bu sayının bu standart sapma sayısının içinde olma olasılığı veya olasılığı olduğunu söyleyebilirsiniz:
| STD | Puanların yüzdesi | SAT puan aralığı |
| 1 | % 68 | 1,854-2,142 |
| 2 | % 95 | 1,711-2,285 |
| 3 | 99.73% | 1,567-2,429 |
| 4 | 99,994% | 1,424-2,572 |
Görebildiğiniz gibi, herhangi bir SAT puanının 3 STD'nin dışında olma ihtimali neredeyse sıfırdır, çünkü puanların yüzde 99,73'ü 3 STD'nin içindedir..
Şimdi elektronik tabloya tekrar bakalım ve nasıl çalıştığını açıklayalım..
Şimdi formülleri açıklıyoruz:
= ORTALAMA (B2: B6)
B2: B6 aralığındaki tüm puanların ortalaması. Spesifik olarak, tüm puanların toplamının testi alan kişi sayısına bölünmesiyle.
= STDEV.P (B2: B6)
B2: B6 aralığında standart sapma. “.P”, STDEV.P'nin tüm puanlarda, yani tüm popülasyonda ve sadece bir altkümede kullanılmadığı anlamına gelir..
= PERCENTRANK.EXC (2 $ B: $ B $ 6, B2)
Bu, bu durumda B2'ye, SAT puanına dayalı olarak B2: B6 aralığında kümülatif yüzdeyi hesaplar. Örneğin, puanların yüzde 83'ü Walker'ın puanının altında.
Sonuçları Grafiklemek
Sonuçları bir grafiğe koymak, sonuçları daha kolay anlamanıza yardımcı olur; ayrıca, noktanızı daha net hale getirmek için sunumda gösterebilirsiniz..
Öğrenciler yatay eksendedir ve SAT puanları 1.600'den 2.300'e kadar bir ölçekte (dikey eksen) mavi bir çubuk grafiği olarak gösterilir..
Yüzdelik sıralaması yüzde 0 ila 90 arasında sağ dikey eksendir ve gri çizgiyle gösterilir.
Grafik Nasıl Oluşturulur
Bir grafik oluşturmak başlı başına bir konudur, ancak yukarıdaki grafiğin nasıl oluşturulduğunu kısaca açıklayacağız..
İlk önce, grafikte olacak hücre aralığını seçin. Bu durumda A2'den C6'ya kadar sayıların yanı sıra öğrencinin isimlerini de istiyoruz.
“Ekle” menüsünden “Grafikler” -> “Önerilen Grafikler” seçeneğini seçin:
Bilgisayar “Kümelenmiş Sütun, İkincil Eksen” çizelgesini önerir. “İkincil Eksen” kısmı iki dikey eksen çizdiği anlamına gelir. Bu durumda, istediğimiz grafik budur. Başka bir şey yapmak zorunda değiliz..
Grafiği hareket ettirmek için kullanabilirsiniz ve istediğiniz boyutta ve konumda olana kadar yeniden boyutlandırabilirsiniz. Memnun kaldığınızda, grafiği elektronik tabloya kaydedebilirsiniz..
Grafiği sağ tıklarsanız “Veri Seç”, o aralık için hangi verilerin seçildiğini gösterir..
“Önerilen Grafikler” özelliği, genellikle hangi verilerin dahil edileceğini, etiketlerin nasıl atanacağını ve sol ve sağ dikey eksenlerin nasıl atanacağını belirlemek gibi karmaşık ayrıntılarla uğraşmanıza gerek kalmaz.
“Veri Kaynağını Seç” iletişim kutusunda, “Efsane Girişleri (Seri)” altındaki “puan” ı tıklayın ve “Düzenle” ye basın ve “Puan” olarak değiştirin.
Ardından seri 2'yi (“yüzdelik”) “Yüzdelik” olarak değiştirin.
Grafiğinize geri dönün ve “Grafik Başlığı” nı tıklayın ve “SAT Puanları” olarak değiştirin. Şimdi tam bir tablomuz var. İki yatay eksene sahiptir: biri SAT puanı (mavi) ve biri kümülatif yüzde (turuncu) için.
Örnek: Ulaştırma Sorunu
Taşımacılık sorunu, “doğrusal programlama” olarak adlandırılan bir matematik türünün klasik bir örneğidir. Bu, belirli kısıtlamalara tabi olan bir değeri en üst düzeye çıkarmanızı veya en aza indirmenizi sağlar. Çok çeşitli iş sorunlarına yönelik birçok uygulaması vardır, bu yüzden nasıl çalıştığını öğrenmek faydalıdır..
Bu örneğe başlamadan önce “Excel Çözücü” nü etkinleştirmeliyiz.
Çözücü Eklentisini Etkinleştir
“Dosya” -> “Seçenekler” -> “Eklentiler” i seçin. Eklenti seçeneklerinin altında "Yönet: Excel Eklentileri" nin yanındaki "Git" düğmesini tıklayın.
Çıkan menüde, “Çözücü Eklentisi” seçeneğini etkinleştirmek için onay kutusunu tıklayın ve “Tamam” ı tıklayın.
Örnek: En Düşük iPad Nakliye Maliyetlerini Hesaplayın
Diyelim ki iPad'ler gönderiyoruz ve dağıtım merkezlerimizi mümkün olan en düşük nakliye maliyetlerini kullanarak doldurmaya çalışıyoruz. Şangay, Pekin ve Hong Kong'dan iPad'leri aşağıda gösterilen dağıtım merkezlerine göndermek için bir nakliye şirketi ve havayolu şirketi ile anlaştık..
Her iPad'in gönderilmesinin bedeli fabrikadan dağıtım merkezine 20.000 kilometre bölü tesise olan mesafedir. Örneğin, Şanghay'dan Melbourne'e 8.024 km, iPad için 8.024 / 20.000 veya .40'dır..
Sorun, tüm bu iPad'leri bu üç fabrikadan bu dört varış noktasına mümkün olan en düşük maliyetle nasıl gönderiyoruz??
Tahmin edebileceğiniz gibi, bazı formül ve araç olmadan bunu anlamak çok zor olabilir. Bu durumda toplam 462.000 (F12) iPad göndermemiz gerekiyor. Tesisler 500.250 (G12) ünite sınırlı kapasiteye sahiptir.
Elektronik tabloda, nasıl çalıştığını görebilmeniz için, 1 iPad'i Şangay'dan Melbourne'e göndermek istediğimiz anlamına gelen B10 hücresine 1 yazdık. Bu rotadaki nakliye masrafları iPad başına 0,40 dolar olduğundan, toplam maliyet (B17) 0,40 dolar.
Sayı = SUMPRODUCT işlevi kullanılarak hesaplandı (ücretler, gönderilen) “maliyetler” B3: E5 aralıklarıdır.
Ve “sevk” ise B9: E11 aralığıdır:
SUMPRODUCT, “gönderilenler” aralığındaki “maliyetleri” çarparak (B14) çarpmaktadır. Buna “matris çarpımı” denir.
SUMPRODUCT'ın düzgün çalışması için, iki matrisin (maliyetler ve gönderilenler) aynı boyutta olması gerekir. Ekstra maliyetler yaparak ve sıfır değere sahip sütunlar ve satırlar göndererek bu sınırlamayı aşabilirsiniz, böylece diziler aynı boyutta olur ve toplam maliyetler üzerinde bir etkisi olmaz.
Çözücüyü Kullanma
Tek yapmamız gereken, çok fazla karmaşık olmayacak olan “sevk” zamanlarının matrislerini çarpmaksa, ancak oradaki kısıtlamalarla da uğraşmak zorundayız..
Her dağıtım merkezinin ihtiyaç duyduğu şeyleri göndermeliyiz. Bu sabiti şu çözücüye koyduk: $ B $ 12: $ E $ 12> = $ B $ 13: $ E $ 13. Bu, gönderilenlerin toplamı anlamına gelir, yani, hücrelerin toplamı $ B $ 12: $ E $ 12, her dağıtım merkezinin gerektirdiğinden daha büyük veya eşit olmalıdır ($ B $ 13: $ E $ 13)..
Ürettiğimizden daha fazlasını gönderemiyoruz. Bu kısıtlamaları şöyle yazıyoruz: $ F $ 9: $ F $ 11 <= $G$9:$G$11. Put another way, what we ship from each plant $F$9:$F$11 cannot exceed (must be less than or equal to) the capacity of each plant: $G$9:$G$11.
Şimdi “Data” menüsüne gidin ve “Solver” düğmesine basın. “Çözücü” düğmesi orada değilse, Çözücü eklentisini etkinleştirmeniz gerekir.
Daha önce ayrıntılı olarak verilen iki kısıtlamayı yazın ve Excel'in hesaplamasını istediğimiz sayı aralığı olan “Gönderiler” aralığını seçin. Ayrıca “Simplex LP” varsayılan algoritmasını seçin ve “Set Objective” yazan B15 hücresini (“toplam nakliye masrafları”) “en aza indirmek” istediğimizi belirtin.
“Çöz” e basın ve Excel sonuçları istediğimiz şekilde elektronik tabloya kaydeder. Bunu ayrıca kaydedebilir, böylece diğer senaryolarla oynatabilirsiniz.
Bilgisayar bir çözüm bulamadığını söylerse, mantıklı olmayan bir şey yaptınız, örneğin, tesislerin üretebileceğinden daha fazla iPad talep etmiş olabilirsiniz..
İşte Excel bir çözüm bulduğunu söylüyor. Çözümü sürdürmek ve elektronik tabloya dönmek için “Tamam” düğmesine basın.
Örnek: Net Bugünkü Değer
Bir şirket yeni bir projeye yatırım yapıp yapmayacağına nasıl karar verir? Eğer “net bugünkü değer” (NBD) pozitif ise, buna yatırım yapacaklardır. Bu, çoğu finansal analist tarafından alınan standart bir yaklaşımdır..
Örneğin, Codelco maden şirketinin Andinas bakır madenini genişletmek istediğini varsayalım. Bir projeyle devam edip etmeyeceğinin belirlenmesinde standart yaklaşım net bugünkü değerin hesaplanmasıdır. Eğer NPV sıfırdan büyükse, iki girdi (1) zaman ve (2) sermaye maliyeti göz önüne alındığında, proje karlı olacaktır..
Basit İngilizce'de, sermaye maliyeti, bankada bıraktıklarında bu paranın ne kadar kazanacağı anlamına gelir. Nakit değerlerini bugünkü değere indirgemek için sermaye maliyetini kullanırsınız, diğer bir deyişle beş yılda 100 dolar bugün 80 dolar olabilir..
İlk yılda, projeyi finanse etmek için 45 milyon dolar sermaye ayrılmıştır. Muhasebeciler sermaye maliyetlerinin yüzde 6 olduğunu belirlediler..
Madenciliğe başladıklarında, şirket ürettikleri bakırı bulup satarken, nakit para girmeye başlar. Açıkçası, ne kadar çok benimselerse, o kadar çok para kazanıyorlar ve tahminleri, yılda 9 milyon dolara ulaşana kadar nakit akışlarının arttığını gösteriyor..
13 yıl sonra, NPV 3.945.074 USD'dir, bu nedenle proje karlı olacaktır. Finansal analistlere göre “geri ödeme süresi” 13 yıldır.
Pivot Tablo Oluşturma
Bir “pivot tablo” temel olarak bir rapordur. Bunlara pivot tabloları diyoruz, çünkü tamamen yeni bir rapor yapmak zorunda kalmadan bir rapor türünü diğerine kolayca geçebilirsiniz. Böylece pivot yerinde. Temel kavramları öğreten temel bir örnek gösterelim.
Örnek: Satış Raporları
Satış görevlileri çok rekabetçidir (bu bir satıcı olmanın bir parçası), bu nedenle doğal olarak, çeyrek sonunda ve yılın sonunda birbirleriyle nasıl karşılandıklarını ve ayrıca komisyonlarının ne kadar olacağını bilmek istiyorlar..
Diyelim ki üç satış elemanı var - Carlos, Fred ve Julie - hepsi petrol satıyor. 2014 yılı mali çeyrek başına dolar cinsinden satışları aşağıdaki elektronik tabloda gösterilmektedir..
Bu raporları oluşturmak için, bir pivot tablo oluşturuyoruz:
“Ekle -> Özet Tablo'yu seçin, araç çubuğunun sol tarafındadır:
Tüm satırları ve sütunları (satıcı adı dahil) aşağıda gösterildiği gibi seçin:
Özet tablo iletişim kutusu, elektronik tablonun sağ tarafında görünür..
Pivot tablo iletişim kutusundaki dört alanı da tıklatırsak (Çeyrek, Yıl, Satış ve Satış Görevlisi) Excel, elektronik tabloya mantıklı olmayan bir neden ekler??
Gördüğünüz gibi rapora eklemek için dört alanı da seçtik. Excel'in varsayılan davranışı, satırları metin alanlarına göre gruplandırmak ve ardından satırların geri kalanını toplamaktır.
Burada bize saçmalık olan 2014 + 2014 + 2014 + 2014 = 24,168 yılının toplamını verir. Ayrıca, 1 + 2 + 3 + 4 = 10 * 3 = 3 0 no'lu çeyreklerin toplamıdır. Bu bilgiye ihtiyacımız yok, bu yüzden bunları pivot tablomuzdan kaldırmak için bu alanların seçimini kaldırıyoruz.
Bununla birlikte, “Satışların Toplamı” (toplam satışlar) uygun, ancak düzelteceğiz.
Örnek: Satıcıdan Satış
Daha açık olan “Toplam Satış” demek için “Satışların Toplamını” düzenleyebilirsiniz. Ayrıca, hücreleri, diğer hücreleri de biçimlendirdiğiniz gibi para birimi olarak biçimlendirebilirsiniz. İlk önce “Toplam Satış” tıklayın ve “Değer Alanı Ayarları” nı seçin.
Çıkan iletişim kutusunda, adı “Toplam Satış” olarak değiştiririz, sonra “Sayı Biçimi” ni tıklayıp “Para Birimi” olarak değiştiririz.
El işinizi pivot masasında görebilirsiniz:
Örnek: Satıcı ve Çeyrek Satışları
Şimdi her çeyrek için alt toplamlar ekleyelim. Alt toplamları eklemek için sadece “Çeyrek” alanına sol tıklayın ve basılı tutun ve “satırlar” bölümüne sürükleyin. Sonucu aşağıdaki ekran görüntüsünde görebilirsiniz:
Biz buna devam ederken, “Çeyrek Toplamı” değerlerini kaldıralım. Basitçe oka tıklayın ve “Alan Kaldır” ı tıklayın. Ekran görüntüsünde, artık her bir satışçının satışlarını çeyreklik ayıran “Çeyrek” satırlarını eklediğimizi görebilirsiniz..
Bu yetenekler göz önünde bulundurularak, artık kendi verilerinizden pivot tablolar oluşturabilirsiniz!
Sonuç
Tamamlanırken, Microsoft Excel'in işinize, akademik ya da diğer gereksinimlerinize uygulayabileceğiniz Microsoft Excel'in formüllerinin ve işlevlerinin bazılarını gösterdik.
Gördüğünüz gibi Microsoft Excel, çoğu insanın, hatta ileri kullanıcıların bile, hepsini bilmediği pek çok özelliğe sahip muazzam bir üründür. Bazı insanlar bunu karmaşık hale getirdiğini söyleyebilir; daha kapsamlı olduğunu düşünüyoruz.
Umarım, size birçok gerçek yaşam örneği sunarak, yalnızca Microsoft Excel'deki işlevleri değil, size istatistikler, doğrusal programlama, grafikler oluşturma, rasgele sayıları kullanma ve şimdi benimseyeceğiniz diğer fikirler hakkında da bir şeyler öğrettik. okulunuzda veya çalıştığınız yerde kullanın.
Unutmayın, geri dönüp tekrar sınıfa geçmek istiyorsanız, Ders 1 ile yeni başlayabilirsiniz.!
