Temel Excel işlevleri basit hesaplamalar için iyi çalışır, ancak karmaşık veri analizleriyle uğraşırken hızla karmaşıklaşırlar. Okunması zor iç içe geçmiş formüller, elektronik tablonuzu dolduran birden fazla yardımcı sütun ve verileriniz değiştiğinde bozulabilen formüllerle karşılaşırsınız. İşte tam da bu noktada Excel'deki matris formülleri devreye girer.
Dizi formülleri, tek bir formülde tüm veri aralıklarında hesaplamalar yapmanıza olanak tanır. Bu nedenle, şunları yapabilirsiniz: Yıldırım hızında aramalar gerçekleştirinHer satır veya sütun için ayrı formüller yazmak yerine, tek bir güçlü ifadeyle filtreleme yapın ve sıralayın. Bu Excel için yeni bir şey değil, ancak bazı insanlar bu işlevler işlerini daha basit ve daha verimli hale getirebildiğinde eski iş yapma yöntemlerine bağlı kalıyorlar.
Hızlı Linkler
5. XLOOKUP
Her zaman VLOOKUP'tan daha iyi performans gösterir.
XLOOKUP, başlangıçtan beri var olması gereken arama işlevidir. Sütunları saymanızı ve yalnızca sağa doğru arama yapmanızı gerektiren VLOOKUP'un aksine, XLOOKUP herhangi bir yönde çalışır ve gerçek sütun referanslarını kullanır. Sözdizimi aşağıdaki gibidir:
=XLOOKUP(aranan_değer, aranan_dizi, dönüş_dizisi, [bulunamadıysa], [eşleşme_modu], [arama_modu])
Her parametrenin anlamı şu şekildedir:
- aranan_değer: Aradığınız belirli değer. Bu, bir parça numarası, ürün kodu veya veri kümenizdeki herhangi bir tanımlayıcı olabilir.
- arama_dizisi: Excel'in arama yaptığı aralık lookup_value Sizin. Bu genellikle arama kriterlerinizi içeren tek bir sütun veya satırdır.
- dönüş_dizisi: Almak istediğiniz değerleri içeren aralık. Bu, tek bir sütun, birden fazla sütun veya hatta tüm bir tablo bölümü olabilir.
- if_not_found (isteğe bağlı): Eşleşme bulunamadığında görüntülenecek özel metin veya değer. Can sıkıcı #N/A hatalarını ortadan kaldırır ve bunun yerine "Bulunamadı" veya "Parça Numarasını Kontrol Edin" mesajını görüntülemenizi sağlar.
- eşleşme_modu (isteğe bağlı): Eşleşme türünü kontrol eder. Tam eşleşme için 0 (varsayılan), bir sonraki tam veya daha küçük eşleşme için -1, bir sonraki tam veya daha büyük eşleşme için 1 ve joker eşleşme için 2 kullanın.
- arama_modu (isteğe bağlı): Arama yönünü belirtir. İlk-son arama için 1 (varsayılan), son-ilk arama için -1 ve sıralı verilerde ikili arama için 2 değerini kullanın.
Bir mekanik envanter elektronik tablosu örneğini ele alalım. Aşağıdaki formül, bir dizi parça kimliği içinde "BRG-002" parça numarasını arar ve ilgili verileri döndürür. Parça mevcut değilse, hata mesajı yerine "Parça Bulunamadı" mesajı görüntülenir.
=XLOOKUP("BRG-002", A:A, A:H, "Parça bulunamadı")
XLOOKUP, VLOOKUP'ta bulunan zahmetli sütun hesaplamaları olmadan farklı sütunlardan veri çıkarmanıza olanak tanır ve bu da onu en önemli araçlardan biri yapar Excel'in verileri hızlı bir şekilde bulma işlevleri.
4. SUMPRODUCT
Koşullu hesaplamalar için güç üretim istasyonu
SUMPRODUCT yalnızca sayıları toplamakla kalmaz, aynı zamanda matrisleri çarpar ve sonuçları toplar. Bu, birden fazla yardımcı sütun gerektiren karmaşık koşullu hesaplamalar için kullanışlıdır.
Formülü şu şekildedir:
=SUMPRODUCT(array1, [array2], [array3], ...)
Burada, dizi1 Çarpılacak ilk değer aralığıdır - genellikle miktarlar veya maliyetler gibi birincil veri sütununuzdur. dizi2 Çarpma işlemi için isteğe bağlı ikinci bir aralıktır ve çoğunlukla karşılaştırma operatörleri kullanılarak ölçüt veya koşullu mantık içerir.
Dizilerde mantıksal operatörler kullandığımızda daha kullanışlı hale gelirler. Örneğin, (tedarikçi="Siemens") gibi koşullar yazdığımızda, Excel TRUE/FALSE sonuçlarını 1/0'a dönüştürerek hesaplamalara olanak tanır.
Örneğin, aşağıdaki formül yalnızca Siemens tarafından tedarik edilen parçalar için toplam envanter değerini hesaplar. Formül, miktarları birim maliyetlerle çarpar, ancak yalnızca tedarikçinin kriterleri karşıladığı satırlar için.
=SUMPRODUCT(D2:D100*H2:H100*(G2:G100="Siemens"))
Benzer şekilde, aşağıdaki formül iyi tedarikli bir rulman stoğunun toplam maliyetini bulur:
=SUMPRODUCT((C2:C100="Bearings")*(D2:D100>=15)*H2:H100)
Aynı anda iki koşul geçerlidir: Kategori "Rulmanlar" olmalı ve envanter seviyeleri 15 birim veya daha yüksek olmalıdır; bu, yeterli envanter kapsamına sahip rulman kategorilerini belirlememize yardımcı olur.
Birden fazla ölçütü olan geleneksel SUM işlevlerinin aksine, SUMPRODUCT tek ve okunabilir bir formülde birden fazla koşulu ele aldığı için karmaşık iç içe yapılara ihtiyaç duymaz. Excel'de TOPLA fonksiyonları, SUMIF ve SUMIFS gibi bunlar da basit koşullu toplamalar için mükemmeldir, ancak SUMPRODUCT fonksiyonu toplamadan önce değerleri çarpmanız gerektiğinde veya daha karmaşık mantıksal işlemleri halletmeniz gerektiğinde öne çıkar.
3. FİLTRE
Dinamik veri çıkarmayı basitleştirir
FILTER, belirttiğiniz koşullara göre veri kümenizden satırları çıkarır. Manuel filtrelemenin aksine, bu işlev kaynak veriler değiştiğinde otomatik olarak güncellenen dinamik sonuçlar üretir. FILTER sözdizimi aşağıdaki gibidir:
=FILTER(dizi, dahil et, [if_empty])
Her girdinin kontrol ettiği şeyler şunlardır:
- dizi (aralık): Filtrelemek istediğiniz verilerin tam aralığı. Bu, yalnızca ölçüt sütununu değil, sonuçlarınızda görmek istediğiniz tüm sütunları içerir.
- katmak: Hangi satırların döndürüleceğini belirten mantıksal koşul – her satır için TRUE/FALSE dizileri oluşturmak üzere karşılaştırma operatörlerini kullanır.
- if_empty (isteğe bağlı): Kriterlerinizi karşılayan satır bulunmadığında özel bir mesaj görüntüler. #CALC! hatalarını önler ve "Eşleşen sonuç bulunamadı" gibi anlamlı metinler görüntüler.
Fonksiyon, koşulunuzu aralıktaki her satıra göre değerlendirerek çalışır. Koşul TRUE değerini döndürdüğünde, söz konusu satırın tamamı filtrelenmiş sonuçlarda görünür. İşte mekanik envanter elektronik tablosundan bir örnek:
=FILTER(A2:H101, (C2:C101="Bearings")*(G2:G101="Timken"))
Bu formül, kaynağın "Timken" ve kategorinin "Rulmanlar" olduğu tüm satırları çıkarır. Yıldız işareti (*), mantıksal dizileri çarparak bir VE koşulu oluşturur.
Kaynak aralığınıza yeni veriler eklediğinizde, Excel'de FİLTRE işlevini kullanma Manuel sıralama ve geçici tablolardan daha mantıklıdır çünkü filtrelenmiş sonuçlar otomatik olarak güncellenir. Bu, canlı panolar ve raporlar oluşturmak için kullanışlıdır.
2. EŞSİZ
Yinelenenler olmadan benzersiz değerleri çıkarın
UNIQUE, veri aralığınızdan benzersiz değerler çeker ve yinelemeleri otomatik olarak önler. Bu işlev, açılır listeler oluşturmak, veri kategorilerini analiz etmek ve özet raporlar oluşturmak istiyorsanız önemlidir. Formül şöyledir:
=BENZERSİZ(dizi, [sütun bazında], [tam olarak bir kez])
Her girdinin çalışma şekli şöyledir:
- dizi (aralık): Yinelenenleri kaldırmak istediğiniz verileri içeren aralık; tek bir sütun, birden çok sütun veya tablonun tüm bir bölümü olabilir.
- by_col (isteğe bağlı): FALSE, benzersizliği belirlemek için satırları karşılaştırır (varsayılan), TRUE ise sütunları karşılaştırır. Ancak çoğu senaryoda varsayılan satır karşılaştırması kullanılır.
- exactly_once (isteğe bağlı): FALSE, birden fazla kez ortaya çıkanlar (varsayılan) dahil olmak üzere tüm benzersiz değerleri döndürür ve TRUE, veri kümesinde yalnızca tam olarak bir kez ortaya çıkan değerleri döndürür.
UNIQUE işlevi, dizinizdeki her satırı veya değeri değerlendirir ve her benzersiz öğenin yalnızca ilk örneğini döndürür. Sıralama, orijinal veri dizisiyle eşleşir. İşte bir örnek:
=BENZERSİZ(G2:G22)
Bu formül, Tedarikçi sütunu G'den tüm benzersiz tedarikçi adlarını çıkarır ve temiz, yinelenen bir liste oluşturur. Bunu, tedarikçi açılır listeleri veya özet raporları oluşturmak için kullanıyorum.
Aşağıda gösterildiği gibi bunu tüm tabloda da kullanabilirsiniz:
=BENZERSİZ(A2:F100)
Tüm sütunlarda (A'dan F'ye kadar) benzersiz kombinasyonlar döndürür ve farklı envanter kayıtlarını görüntüler. İki parçanın her sütunda aynı değerlere sahip olması durumunda, sonuçlarda yalnızca biri görünür.
Büyük veri kümeleriyle çalışırken, UNIQUE, yinelenenleri manuel olarak kaldırma zahmetini ortadan kaldırır. Dinamik sonuçlar yeni veriler geldikçe güncellenir ve UNIQUE taşma matrisleri oluşturduğundan, bu yaklaşım tüm benzersiz değerleri barındıracak şekilde otomatik olarak ölçeklendirerek tabloları yeniden boyutlandırma zahmetini ortadan kaldırır. Temiz referans listeleri tutmak ve güvenilir veri doğrulama aralıkları oluşturmak için kullanıyorum.
1. SIRALA ve SIRALA
Orijinalinden ödün vermeden verilerinizi düzenleyin
SORT ve SORTBY işlevleri, kaynağı bozulmadan korurken verileri dinamik olarak düzenler. SORT, sütun konumuna göre temel sıralamayı gerçekleştirirken, SORTBY farklı sütunlardaki değerlere göre sıralama yapar; bu da karmaşık sıralama için size daha fazla esneklik sağlar.
SORT şu yapıyı kullanır:
=SIRALA(dizi, [sıralama_dizin], [sıralama_sıralama], [tarafına göre])
Her parametrenin kontrol ettiği şeyler şunlardır:
- dizi: Sıralamak istediğiniz veri aralığı—sıralanmış sonuçlarda görünmesi gereken tüm sütunları içerir.
- sort_index (isteğe bağlı): Sıralama için dizi içindeki sütun numarası. İlk sütun için 1, ikinci sütun için 2 vb. kullanın (varsayılan değer 1'dir).
- sort_order (isteğe bağlı): Artan sıralama için 1'i (varsayılan) ve azalan sıralama için -1'i kullanın.
- by_col (isteğe bağlı): Satırlara göre sıralamak için FALSE (varsayılan), sütunlara göre sıralamak için TRUE—çoğu senaryoda satır sıralaması kullanılır.
SORTBY fonksiyonu aşağıdaki formu alır:
=SORTBY(dizi, diziye göre1, [sıralama_düzeni1], [diziye göre2], [sıralama_sıralaması2], ...)
İşlemleri şunlardır:
- dizi: Sıralanacak veri aralığı—SORT işlevine benzer şekilde, sonuçlarda bulunmasını istediğiniz tüm sütunları içerir.
- by_array1: Sıralama düzenini belirleyen değerleri içeren aralık, ana dizinin aralığının dışında bile herhangi bir sütun olabilir.
- sort_order1 (isteğe bağlı): 1 artan sıralama için (varsayılan), -1 azalan sıralama için.
- by_array2, sort_order2 (isteğe bağlı): Çok seviyeli sıralama için ek sıralama ölçütleri.
Mekanik envanter tablosu örneğine bakıldığında, bu işlevler gerçek dünya sıralama senaryolarını ele alır:
=SORT(A2:H22, 4, -1)
Bu, tüm envanteri stok seviyelerine göre azalan sırada sıralar ve en yüksek stok seviyesine sahip ürünler ilk sırada gösterilir. Formül, satırlar arasındaki tüm ilişkileri koruyarak 4. sütuna (stok seviyeleri) göre sıralar.
SORTBY fonksiyonunu kullanıyorum. SORT yerine, sıralama ölçütleri ve birden fazla sıralama düzeyi üzerinde daha iyi kontrol sağlamak için kullanabilirsiniz. Örneğin, aşağıdaki formül önce kategoriye göre alfabetik olarak, ardından her kategorideki envanter düzeylerine göre en yüksekten en düşüğe doğru sıralar.
=SIRAMAYA GÖRE(A2:H22, C2:C22, 1, D2:D22, -1)
Düzenli elektronik tablolar, daha akıllı sonuçlar
Dizi formülleri, elektronik tabloların bakımını zorlaştıran yardımcı sütunların ve iç içe geçmiş işlevlerin yarattığı karmaşayı ortadan kaldırır. Birden fazla işlemi işleyen tek formüller elde ederek çalışma kitaplarınızı daha temiz ve profesyonel hale getirirsiniz.
Dikkat çekici avantajlarından biri, kaynak veriler değiştiğinde sonuçların otomatik olarak güncellendiği dinamik işlevlerdir. Bu, manuel güncellemeleri veya bozuk formül dizelerini ortadan kaldırarak, elektronik tablolarınızı devam eden analizler için daha güvenilir hale getirir.
Excel'in dizi fonksiyonu kütüphanesi bu temel araçların ötesine geçerek genişlemeye devam ediyor. Birden fazla kaynaktan veri birleştirmem gerektiğinde, aralıkları birleştirmek için VSTACK ve HSTACK fonksiyonlarını kullanıyorum. Bu fonksiyonlar bir araya geldiğinde, geleneksel formüllerle mümkün olmayacak güçlü veri işleme iş akışları yaratıyor.
