Bu fonksiyonları yakın zamanda Excel'de keşfettim ve artık onlarsız yaşayamam.

Excel'de verilerle çalışırken bazı görevler gereksiz yere sıkıcı gelebilir. Belki de tam adların bulunduğu bir sütunu ad ve soyad için ayrı sütunlara ayırmanız veya birden fazla hücredeki metni belirli virgüllerle birleştirmeniz gerekebilir. Bunlar karmaşık analitik zorluklar değil; düzenli olarak karşılaşılan temel veri işleme görevleridir.

İyi haber şu ki, Excel'in bu durumlar için özel olarak tasarlanmış yerleşik işlevleri var. Ancak bunlar genellikle gözden kaçıyor çünkü bunlar Çoğu insanın öğrendiği standart Excel araç seti, ben de dahil. Burada ele alacağım fonksiyonlar ileri düzey hesaplamalarla ilgili değil, ancak tekrarlayan veri işleriyle uğraşıyorsanız, bu fonksiyonlar size biraz zaman kazandırabilir.

5. METİN BÖLÜMÜ

Birbirine yapışmış metinleri ayırır

Birisinin adını, soyadını ve hatta belki de göbek adının baş harflerini tek bir hücreye sıkıştırdığı bir elektronik tablo aldıysanız, bu verileri ayırmaya çalışmanın ne kadar zor olduğunu bilirsiniz. TextSplit tam da bu sorunu çözer: Tek bir hücredeki metni alır ve belirttiğiniz bir ayırıcıya göre birden fazla sütuna böler.

Örnek bir satış tablosu üzerinde çalışalım. Satış temsilcilerinin adlarını tek bir sütunda "Sarah Chen", "Mike Johnson" ve "Lisa Park" olarak göreceksiniz. Her bir adı ayrı sütunlara manuel olarak yeniden yazmak yerine, TextSplit bu işi otomatik olarak yapabilir.

Formül şu şekildedir:

=TEXTSPLIT(metin, sütun ayırıcı, satır ayırıcı, boşları yok say, eşleştirme modu, doldurma alanı)

Her öğretmenin yaptığı şey şudur:

• metin: Bölmek istediğiniz metnin bulunduğu hücre.
• sütun_ayırıcı: Verilerinizi ayıran karakter (boşluk, virgül veya noktalı virgül gibi).
• satır_ayırıcı (isteğe bağlı): Hem satırlara hem de sütunlara ayırmada kullanılır.
• ignore_empty (isteğe bağlı): TRUE boş değerleri yok sayar, FALSE ise onları tutar (varsayılan FALSE'dur).
• eşleşme_modu (isteğe bağlı): Büyük/küçük harf duyarlılığını kontrol eder (büyük/küçük harf duyarlılığı için 0, büyük/küçük harf duyarlılığı için 1).
• pad_with (isteğe bağlı): Sonuçlar eşit uzunlukta olmadığında boş hücreleri neyle doldurursunuz?

Örneğin, satış temsilcisi adları için adları ayrı sütunlara bölmek üzere aşağıdaki formülü kullanırdım:

=METİNBÖLÜN(A2, " ")

İşlev, verilerinize göre gerekli sayıda sütunu otomatik olarak oluşturur. Bu temel yaklaşım çoğu durumda işe yarasa da, size ayrıntılı kontrol sağlayan ek parametreler mevcuttur. Excel'de TEXTSPLIT İşlevi.

4. METİN BİRLEŞTİRME

Birden fazla hücreyi tek bir hücrede birleştir

TEXTJOIN, TEXTSPLIT'in tam tersini yapar. Birden fazla hücreden metin alır ve seçtiğiniz ayırıcıyı kullanarak bunları tek bir hücrede birleştirir. Bu, tam adresler, ürün açıklamaları veya e-posta listeleri gibi sıralı değerler oluşturmanız gerektiğinde kullanışlıdır.

Formül şu şekildedir:

=TEXTJOIN(ayırıcı, boş olanları yok say, metin1, [metin2], ...)

Her parametrenin kontrol ettiği şeyler şunlardır:

• ayırıcı: Gömülü değerleri ayıran karakter veya metin (virgül, boşluk, tire vb.).
• boş_bırak: Boş hücreleri yok saymak için TRUE, sonuçlara dahil etmek için FALSE.
• text1, text2, vb.: Birleştirmek istediğiniz hücreler veya aralıklar (tek tek hücreleri veya tüm aralıkları belirtebilirsiniz).

Satış tablosuna baktığımda, ad ve bölge için ayrı sütunlarım varsa, ancak bunları birleştiren tek bir sütuna ihtiyacım varsa, TEXTJOIN kullanırım. görmezden_boş TRUE, boş hücrelerin otomatik olarak atlanacağı anlamına gelir.

=TEXTJOIN(" - ", TRUE, B2, D2)

Farklı metin bütünleştirme yöntemleri arasında seçim yaparken, CONCAT ve TEXTJOIN fonksiyonları arasındaki farklar Belirli veri bütünleştirme ihtiyaçlarınız için doğru aracı seçmenize yardımcı olabilir.

3. SEÇENEKLER

Verilerinizin belirli sütunlarını belirtin.

CHOOSECOLS, kopyalayıp yapıştırmadan veya referans oluşturmadan bir aralıktan belirli sütunları çıkarmanıza olanak tanır. Büyük bir veri kümeniz varsa ancak analiziniz için yalnızca 2, 5 ve 8. sütunlara ihtiyacınız varsa, bu işlev ihtiyacınız olanı alır ve geri kalanını atar.

Satış verilerine dayanarak, sipariş tarihlerini, ürün kategorilerini ve diğer ayrıntıları göz ardı ederek yalnızca satış elemanı ve satış elemanı adlarını çıkarmak isteyebilirim. Sütunları manuel olarak seçip kopyalamak yerine, CHOOSECOLS işlevi kaynak veriler değiştiğinde otomatik olarak güncellenen dinamik bir referans oluşturur.

Fonksiyon aşağıdaki formülü takip eder:

=SÜTUN SEÇ (dizi, sütun_numarası1, [sütun_numarası2], ...)

Her parametrenin çalışma şekli şöyledir:

• dizi: Kaynak verilerinizi içeren aralık veya tablo (A1:F100 gibi bir hücre aralığı veya bir tablo başvurusu olabilir).
• sütun_num1: Çıkarmak istediğiniz ilk sütunun numarası (ilk sütun için 1, ikinci sütun için 2 vb.).
• sütun_num2, vb.: Eklemek istediğiniz ek sütun numaraları (isteğe bağlı – istediğiniz kadarını belirtebilirsiniz).

Örneğin, 2. sütundan satış temsilcilerinin adlarını ve 9. sütundan da durumlarını çıkarmak isteseydim, şunu kullanırdım:

=CHOOSECOLS(A1:I23, 2, 9)

İşlev, her iki sütunu da verilere uyacak şekilde otomatik olarak yeniden boyutlandırılmış bir akış dizisi olarak döndürür. Bu nedenle CHOOSECOLS, Size çok zaman kazandırabilecek Excel işlevleriBüyük veri kümeleriyle çalışırken birden fazla VLOOKUP formülü kullanma veya sütunları elle kopyalama ihtiyacını ortadan kaldırır.

2. AL ve BIRAK

Verilerinizin bölümlerini çıkarın

TAKE ve DROP, veri aralığınızın belirli bölümlerini yakalamak için bir çift olarak çalışır. TAKE, veri kümenizin başından veya sonundan belirli sayıda satır veya sütun çıkarırken, DROP başlangıçtan veya sondan satır veya sütunları kaldırarak size kalanı bırakır.

Bu işlevler, veri örneklemesi için hassas araçlar görevi görür. İster hızlı analiz için yalnızca ilk on veri satırına ihtiyacınız olsun, ister hesaplamalarınızı tıkayan başlık satırlarını kaldırmak isteyin, bu işlevler görevi kusursuz bir şekilde yerine getirir.

TAKE şu formülü kullanır:

=TAKE(dizi, satırlar, [sütunlar])

DROP da benzer bir örüntüyü takip ediyor:

=DROP(dizi, satırlar, [sütunlar])

Parametrelerin her iki fonksiyon için nasıl çalıştığına bakalım:

• dizi: Çıkarmak veya değiştirmek istediğiniz kaynak veri aralığı.
• satırlar: Alınacak/çıkarılacak satır sayısı (pozitif sayılar yukarıdan, negatif sayılar aşağıdan işlenir).
• sütunlar (isteğe bağlı):
  Almak veya çıkarmak istediğiniz sütun sayısı (soldan pozitif, sağdan negatif).

Satış verilerinin ilk beş satırını almak için aşağıdaki formülü kullanın:

=TAKE(A1:C100, 5)

İlk 20 satırı kaldırmak ve temiz verilerle çalışmak için şunu deneyin:

=DROP(A1:C23, 20)

Satır ve sütun işlemlerini birleştirebilirsiniz. Örneğin, aşağıdaki formül size ilk on satırı ve ilk üç sütunu verir:

=TAKE(A1:F23, 10, 3)

Bu işlevler, özellikle otomatik olarak uyarlanan dinamik veri alt kümelerine ihtiyaç duyduğunuzda çok faydalı hale gelir. Excel'de TAKE ve DROP Fonksiyonları Nasıl Kullanılır? Değişen veri seti boyutlarına uyum sağlayabilen esnek raporlar oluşturmanıza olanak tanır.

1. AGREGA

Dağınık verileri işleyen güçlü hesaplamalar

AGGREGATE, 19 farklı istatistiksel fonksiyonun işlevselliğini tek bir esnek formülde birleştirir. Onu farklı kılan şey, hataları, gizli satırları veya filtrelenmiş verileri göz ardı edebilmesidir; SUM veya AVERAGE gibi standart fonksiyonların güvenilir bir şekilde yapamadığı bir şey.

Verileriniz bazı #N/A hataları içeriyorsa veya yalnızca belirli bölgeleri gösterecek şekilde filtreliyorsanız, AGGREGATE, bu sorunlar sonuçlarınızı etkilemeden toplamları, ortalamaları veya diğer istatistikleri hesaplayabilir. Görünürlüğün ve veri kalitesinin sıklıkla değiştiği dinamik veri kümeleriyle çalışırken bunu faydalı buluyorum.

Cümle yapısı birkaç bileşenden oluşur:

=AGGREGATE(fonksiyon_numarası, seçenekler, dizi, [k])

Her kriter hesaplamanın farklı yönlerini kontrol eder:

• fonksiyon_num: Kullanılacak işlevi belirten 1'den 19'a kadar bir sayı (1=ORTALAMA, 4=MAKS, 9=TOPLA, 12=MEDYAN, vb.).
• seçenekleri: Hesaplama sırasında neyin göz ardı edileceğini kontrol eder (0=hiçbiri, 1=gizli satırlar, 2=hata değerleri, 3=gizli satırlar ve hatalar, 5=sadece hata değerleri, 6=gizli satırlar ve hata değerleri).
• dizi: Hesaplanacak hücre aralığı.
• k (isteğe bağlı):
  • Sadece LARGE, SMALL veya PERCENTILE gibi belirli işlevlerle kullanılır.

  Herhangi bir hatayı göz ardı ederek gösterilen satış tutarlarını özetlemek için şunu kullanabilirim:

  =AGGREGATE(9, 6, D2:D23)

  9 sayısı TOPLAM'ı belirtir ve 6 sayısı fonksiyona hem gizli satırları hem de hata değerlerini yoksaymasını söyler.

  AGGREGATE'in dahil edilmesinin nedeni tam olarak bu güçlü hesaplama yeteneğidir. Her ofis çalışanının bilmesi gereken Excel fonksiyonlarının listesi—Daha basit fonksiyonların etkili bir şekilde yönetemediği gerçek dünya verilerinin kaosuyla başa çıkar.

  Kullanmaya değer yerleşik araçlar

  En önemli Excel işlevleri genellikle insanların ilk öğrendikleri işlevler değildir. Ancak, karmaşık metin verileriyle başa çıkma, büyük veri kümelerinden belirli bölümleri çıkarma ve eksik veriler üzerinde hesaplamalar yapma gibi gerçek elektronik tablo çalışmalarında ortaya çıkan ince sorunları ele alırlar. Bahsettiğimiz işlevlerin hiçbiri gelişmiş Excel becerileri gerektirmez. Ancak, TEXTSPLIT, CHOOSECOLS, TAKE ve DROP işlevleri yalnızca Microsoft 365 ve Excel for Web'de mevcuttur.

  Bir dahaki sefere kendinizi verileri tekrar tekrar temizlerken veya sütunları manuel olarak kopyalarken bulduğunuzda, bu işlevlerin mevcut olduğunu unutmayın. Bunlar, sıkıcı işleri halletmek için Excel'e zaten entegre edilmiştir, böylece verilerin size gerçekten ne anlattığına odaklanabilirsiniz.