Excel Formülleri Hile Sayfası: Gelişmiş Kılavuz

Yani temelleri aştınız, ara konuları hallettiniz ve artık büyük silahlarla, Excel'in gelişmiş formülleriyle güreşmeye hazırsınız! Excel, karmaşık hesaplamalar için birçok gelişmiş işleve ve formüle sahiptir, bu nedenle ihtiyacınız olanların yerini belirleyen bir kılavuza sahip olmak yararlıdır.

Bu Excel formülleri hile sayfası, gelişmiş tahmin formüllerini, istatistiksel analizi, veri işleme işlevlerini, hata işlemeyi ve daha fazlasını kapsar.

Bu başvuru, sizi bu gelişmiş işlevlerin nasıl kullanılacağına ilişkin bilgiyle donatacaktır. Orta düzey Excel kullanıcılarının ileri düzey uzman kullanıcılar haline gelmesine yardımcı olmak için her formüle net açıklamalar, sözdizimi ve pratik örnekler eşlik eder.

Lütfen hile sayfasını indirip yazdırın ve elinizin altında bulundurun.

Tamam, başlayalım.

Excel-formüller-hile sayfası-gelişmiş-1 İndir

Öncelikle Array formüllerine geçelim.

İçindekiler

• Dizi Formülleri
  • 1. BENZERSİZ İşlev
  • 2. SIRALAMA İşlevi
  • 3. FİLTRE İşlevi
• Excel İşlevlerini Rastgele Hale Getirme
  • 1. RANDBETWEEN İşlevi
  • 2. RANDARAY İşlevi
• Microsoft Excel'de Gelişmiş Tahmin Formülleri
  • 1. TAHMİN.ETS İşlevi
  • 2. TREND İşlevi
• Gelişmiş İstatistiksel Formüller
  • 1. YÜZDELİK İşlevi
  • 2. DÖRT KISMI İşlevi
• Gelişmiş Veri Analizi ve Manipülasyon Formülleri
  • 1. TRANSPOSE İşlevi
  • 2. FREKANS İşlevi
  • 3. GEPIVOTDATA İşlevi
• Gelişmiş Hata İşleme
• Gelişmiş Arama Formülleri
  • 1. XLOOKUP İşlevi
  • 2. XMATCH İşlevi
• Son düşünceler

Dizi Formülleri

Acemi hile sayfamız, verilerinizi manuel olarak nasıl sıralayacağınızı ve filtreleyeceğinizi gösterir. İleri düzey kullanıcılar bunu dizi formülleriyle programlı olarak yapar.

Dizi formülleri, aynı anda birden çok hücrede hesaplamalar yapmanıza olanak tanır. Bunlar üç temel işlevdir:

1. EŞSİZ

2. DÜZENLEMEK

3. FİLTRE

Bu işlevlerden bazıları yalnızca Microsoft Excel'in en son sürümlerinde mevcuttur.

1. BENZERSİZ İşlev

UNIQUE işlevi bir aralığı kabul eder ve benzersiz değerlerin bir listesini döndürür.

Giyim ürünleri için satış verileriniz olduğunu varsayalım. A sütunundaki benzersiz öğeleri bulmak için şu formülü kullanın:

=ÖZEL(A2:A6)

2. SIRALAMA İşlevi

SORT işlevi, bir aralığın içeriğini sıralar. sözdizimi şöyledir:

SIRALAMA(dizi, [sıralama_index], [sıralama_sıralama], [sütun_a göre])

• dizi : sıralanacak değer aralığı.

• sort_index : sıralanacak sütun (varsayılan olarak 1)

• sort_order : Artan için 1 (varsayılan) veya azalan için 2).

• by_col : Sütuna göre sıralamak için TRUE (varsayılan) veya satıra göre sıralamak için FALSE.

Son üç bağımsız değişken isteğe bağlıdır ve varsayılanlar genellikle istediğiniz şeydir.

Örnek verileri ilk sütuna göre sıralamak için şu formülü kullanın:

=SIRALAMA(A2:C6)

3. FİLTRE İşlevi

FİLTRE işlevi, bir aralığı belirli bir koşulda filtrelemenizi sağlar. Bu sözdizimi:

=FILTER(dizi, dahil et, [eğer_boşsa])

• dizi : filtrelenecek aralık.

• include : hangi değerlerin filtreleneceğini belirleyen koşul.

• if_empty : filtreleme ölçütlerini karşılayan hiçbir değer yoksa ne döndürüleceğini belirtir (varsayılan "" dır).

Örnek verilerdeki satırları yalnızca satış değerinin 15 ABD Dolarından büyük olduğunu gösterecek şekilde filtrelemek istediğinizi varsayalım. Bu formülü kullanın:

=FİLTRE(A2:C6, C2:C6>15)

Excel İşlevlerini Rastgele Hale Getirme

Ara hilemiz, 0 ile 1 arasında rastgele bir sayı üreten RAND işlevinin nasıl kullanılacağını gösterir.

Gelişmiş Excel kullanıcıları, hızlı bir şekilde örnek veriler oluşturmak için rastgeleleştirme işlevlerini nasıl kullanacaklarını bilirler.

1. RANDBETWEEN İşlevi

RANDBETWEEN işlevi RAND işlevinden daha esnektir çünkü alt ve üst sayıları 0 ve 1'den farklı olarak belirtebilirsiniz.

1 ile 100 arasında sayılarla veri oluşturmak için A1 hücresine şu formülü girin:

=RANDBETWEEN(1, 100)

Ardından, hücreyi istediğiniz kadar satır ve sütuna kopyalayın. Rastgele sayılar tablosu oluşturmak saniyeler sürer:

2. RANDARAY İşlevi

RANDBETWEEN işlevinin manuel kopyasından kaçınmanın iyi olacağını düşünüyor olabilirsiniz. Süper gelişmiş olmak için Microsoft Excel'in en son sürümündeki yeni RANDARRAY işlevini kullanabilirsiniz.

sözdizimi şöyledir:

RANDARRAY([satırlar], [sütunlar], [min], [maks], [tam sayı])

• satırlar : satır sayısı

• sütunlar : sütun sayısı

• min : en düşük sayı

• maks : en yüksek sayı

• tamsayı : varsayılan olarak DOĞRU'dur, aksi takdirde ondalık sayılar kullanılır.

Altı satır ve dokuz sütunda 1 ile 100 arasında rasgele sayılar oluşturmak için şu formülü kullanın:

=RANDARRAY(6, 9, 1, 100, DOĞRU)

Microsoft Excel'de Gelişmiş Tahmin Formülleri

Excel'in tahmin işlevleri, mevcut veri eğilimlerine dayalı olarak gelecekteki değerleri tahmin etmek için kullanılır. Bu işlevler, verilerinize dayalı kalıpları ve proje trendlerini belirlemeye yardımcı olur.

1. TAHMİN.ETS İşlevi

Eski TAHMİN işlevi, Excel 2016'da bir dizi yeni işlevle değiştirildi.

İstediğiniz belirli tahmin modeline göre işlevi seçersiniz. Örneğin TAHMİN.ETS işlevi, Üstel Düzeltme algoritmasını kullanır.

sözdizimi şöyledir:

TAHMİN.ETS(hedef_tarihi, değerler, zaman çizelgesi)

• hedef_tarihi : için hesaplanmış bir değer istediğiniz tarih.

• değerler : tarihsel veriler.

• zaman çizelgesi : bir tarih aralığı

A sütununda 1 Ocak'tan 5 Ocak'a kadar olan tarihlerin ve B sütununda satış tutarlarının olduğunu varsayalım. Bu formül bir sonraki satış tutarını tahmin edecektir:

=TAHMİ.ETS(“6 Ocak 2023”, B2:B6, A2:A6)

2. TREND İşlevi

TREND işlevi, en küçük kareler yöntemine dayalı olarak bir dizi değer yansıtır. Bir dizi döndürür. sözdizimi şöyledir:

TREND(bilinen_y, [bilinen_x], [yeni_x], [sabit])

• bilinen_y : y değerleri aralığı

• bilinen_x : x değerleri aralığı

• new_x : hesaplanan değer aralığı

Genellikle, bilinen_y veri noktalarıdır, bilinen_x ise tarihlerdir.

Önceki örnektekiyle aynı verileri kullanarak, aşağıdaki formülü C2 hücresine girebilirsiniz. Bir dizi değer üretilecektir.

=EĞİLİM(B2:B6, A2:A6)

Gelişmiş İstatistiksel Formüller

Gelişmiş istatistiksel işlevler, yüzdeliklerin ve çeyrekliklerin hesaplanmasını içerir. Bazı matematik işlevleri geriye dönük uyumluluk için kullanılabilir, ancak en güncel sürümlerin kullanılması önerilir.

1. YÜZDELİK İşlevi

Bu işlev, belirli bir değerin altına düşen veri noktalarının yüzdesini hesaplar. sözdizimi şöyledir:

YÜZDEBİRLİK.DAH.(dizi; k)

• dizi : hücre aralığı

• k : 0'dan 1'e yüzdelik dilim

Diyelim ki B sütunundaki verilerin 70. yüzdelik dilimini hesaplamak istiyorsunuz. Şu formülü kullanın:

=YÜZDEBİRLİK.DAH.(B2:B6, 0,7)

2. DÖRT KISMI İşlevi

Bu işlev, yüzdelik işlevinin bir çeşididir ancak verileri bölmek için çeyrekleri kullanır. Bu sözdizimi:

QUARTILE.INC(dizi; çeyrek)

• dizi : veri aralığı

• quart : 25. persentil için 1, 50. için 2, 75. için 3 ve maksimum için 4.

Aşağıdaki formül, A sütunundaki verilerin ilk dörtte birlik dilimini hesaplayacaktır.

=ÇEYREKTİR.DAH.(A2:A5, 1)

Gelişmiş Veri Analizi ve Manipülasyon Formülleri

Çeşitli gelişmiş işlevler, verilerin biçimini değiştirmenize, frekans dağılımlarını analiz etmenize ve pivot tablolardan veri çıkarmanıza olanak tanır.

1. TRANSPOZ

2. SIKLIK

3. GETPIVOTDATA

1. TRANSPOSE İşlevi

Bazen satırlarınızdaki verileri sütunlara taşımak istersiniz ve bunun tersi de geçerlidir. Bunu manuel olarak yapabilir veya bunun yerine TRANSPOSE işlevini kullanabilirsiniz.

A2, A3 ve A4 hücrelerinde "Tişört", "Kapşonlu" ve "Kot pantolon" öğeleriniz olduğunu varsayalım. Bunları sütun başlıkları haline getirmek istiyorsunuz. Bu işlev, değerleri tek bir satırda döndürür:

=YER DÖNÜŞÜM(A2:A4)

2. FREKANS İşlevi

Bu işlev, bir veri kümesi içindeki değerlerin frekans dağılımını hesaplar. Bu sözdizimi:

FREKANS(veri_dizisi; bins_dizisi)

• data_array : değer aralığı.

• bins_array : kullanılacak aralıklar.

Diyelim ki B sütununda satış verileriniz var ve değerlerin sıklık dağılımını aşağıdaki miktarlara göre analiz etmek istiyorsunuz:

• 20'nin altında.

• 20'den 80'e.

• 80'in üzerinde.

Bu, üç bölmeyi temsil eder ve şu formülle hesaplanabilir:

=FREKANS(A2:A6, {20,80})

Excel'deki frekans dağılımları hakkında daha fazla bilgi için şu videoyu izleyin:

3. GEPIVOTDATA İşlevi

Bu işlev, pivot tablolardan özetlenmiş bilgileri çıkarmanıza olanak tanır. Bu sözdizimi:

GEPIVOTDATA(veri_alanı, özet_tablo, [alan1, öğe1], [alan2, öğe2], …)

• data_field : pivot tablodan almak istediğiniz veri alanı veya değer.

• pivot_table : pivot tabloya bir başvuru.

• field1, item1, etc. : filtrelenecek alan/öğe çiftleri.

Satılan ürünlerin rengine dayalı bir pivot tablonuz olduğunu varsayalım. Kırmızı ürünler için satışları çıkarmak için şu formülü kullanın:

=GETPIVOTDATA(“Satış”, A1, “Renkli”, “Kırmızı”)

Gelişmiş Hata İşleme

En temel Excel formülleri bile hata üretebilir. Orta düzey kullanıcılar, hataları işlemek için ISERROR'u nasıl kullanacaklarını bilmelidir. İleri düzey kullanıcılar, hata tanımlama için ERROR.TYPE işlevine de aşina olmalıdır.

HATA.TİPİ işlevi, bir hücre veya formül içindeki belirli hata türünü belirlemeye yardımcı olur.

#YOK, #DEĞER!, #BAŞV! ve daha fazlası gibi farklı hata türlerine karşılık gelen sayısal bir değer döndürür.

A1 hücresinde bir hatanız olduğunu ve hata türünü belirlemek istediğinizi varsayalım. Aşağıdaki formül, belirli hataya karşılık gelen sayıyı döndürür:

=HATA.TİPİ(A1)

Hatanın türüne bağlı olarak farklı yanıtlar vermek için bunu birden çok işlevle birleştirebilirsiniz. Bunlar en yaygın hatalar ve değerleridir:

1. #HÜKÜMSÜZ! (bir aralıkta ortak hücre bulunamadı)

2. #BÖL/0! ( sıfıra bölme veya boş bir hücre)

3. #DEĞER! (bir formülde uygunsuz veri türü veya bağımsız değişken)

4. #REF! (referans verilen bir hücre silinmiş veya döngüsel bir referans var )

5. #İSİM? (Excel işlevi veya aralığı tanımıyor)

6. #SAYI! (geçersiz sayısal değer)

7. #YOK (değer bulunamadı)

Üç belirli hata tipini ele almak istediğinizi varsayalım. Türe göre belirli bir hata mesajı görüntülemek için bu formülü kullanın:

=IF(ISERROR(C2), IF(ERROR.TYPE(C2)=2, "Sıfıra bölme hatası", IF(ERROR.TYPE(C2)=3, "Geçersiz değer hatası", IF(ERROR.TYPE(C2) )=7, “Değer bulunamadı hatası”, “Diğer hata”)), C2)

Gelişmiş Arama Formülleri

Başlangıç ​​ve orta düzey hile sayfalarımız, çeşitli arama işlevlerini kapsıyordu . İşte bazı gelişmiş seçenekler:

1. XLOOKUP

2. XMATCH

1. XLOOKUP İşlevi

Bu arama işlevi, bir aralıkta bir değer aramanıza ve başka bir sütun veya aralıktan karşılık gelen bir değer döndürmenize olanak tanır.

DÜŞEYARA gibi daha basit arama işlevlerinden daha fazla çok yönlülük sunar . Bu sözdizimi:

XLOOKUP(aranan_değer, aranan_dizi, dönüş_dizisi, [eşleşme_modu], [arama_modu], [bulunamadıysa])

• aranan_değer : aramak istediğiniz değer.

• aranan_dizi : arama aralığı.

• dönüş_dizisi : karşılık gelen değeri gösterecek aralık.

• match_mode : tam eşleşme (0), sonraki küçük (1), sonraki büyük (-1) veya joker karakter eşleşmesi (2).

• search_mode : -1 yukarıdan aşağıya, 1 aşağıdan yukarıya veya 2 ikili arama için.

• if_not_found : eşleşme bulunmazsa döndürülecek değeri ayarlar.

Bir giysi öğesinin ilk kez geçtiği bir veri aralığını aramak ve satış miktarlarını döndürmek istediğinizi varsayalım. Bu formül "Hoodie" metnini arayacak ve bulunursa bitişik hücredeki değeri döndürecektir:

=XLOOKUP(“Kapşonlu”, A2:A6, B2:B6)

2. XMATCH İşlevi

Bu işlev, bir aralık veya dizi içinde belirtilen bir değerin konumunu bulmanızı sağlar. Bu sözdizimi:

XMATCH(aranan_değer, aranan_dizi, [eşleşme_türü], [arama_modu])

• aranan_değer : bulmak istediğiniz değer.

• aranan_dizi : Aramak istediğiniz aralık.

• match_type : tam eşleşme (0), sonraki en küçük (-1), sonraki en büyük (1).

• search_mode : ikili arama (1) veya doğrusal arama (2).

B sütunundaki bir aralıkta sarı bir öğenin ilk geçtiği yeri bulmak istediğinizi varsayalım. Şu formülü kullanın:

=XMATCH(“Sarı”, B2:B6, 0)

Son düşünceler

Bu hile sayfası, istatistiksel analiz, arama formülleri, veri işleme teknikleri ve hata işleme stratejileri gibi çok çeşitli işlevleri kapsamaktadır.

Sağlanan örnekler ve açıklamalar, bu gelişmiş formüllerin gizemini çözmeye yardımcı olarak, sınırlı deneyime sahip olanlar için bile erişilebilir hale getirir.

Bunları Excel görevlerinize dahil etmeye başladığınızda, Excel becerilerinizi ileri bir düzeye çıkarma yolundasınız.

Ama unutma, bu kopya kağıdı buzdağının sadece görünen kısmı. Yaratıcı olduğunuzda, bu formülleri karıştırıp eşleştirdiğinizde ve bunları benzersiz zorluklarınızı çözmek için uyarladığınızda gerçekten harika şeyler olur. Excel bir tuval gibidir ve bu formüller sizin paletinizdir — öyleyse devam edin, şaheserinizi boyayın!