Biscuit'in en sık getirdiği formüller

Bir koşula uyan satırları say

A sütununda tam olarak "Denver" ile eşleşen hücre sayısını döndürür.

Joker karakterle sayma

Kısmi eşleşmeler için yıldız kullanır. Excel'de ve Google Sheets'te çalışır.

Birden çok koşulla sayma

Her koşul-aralık çifti VE ile bağlıdır. VEYA mantığı için iki COUNTIFS'i topla.

Sadece eşleşen satırları topla

A sütunu "Denver"a eşit olduğunda B sütunundaki değerleri toplar.

Birden çok koşulla toplama

SUMIFS'te önce değer aralığı, sonra koşul çiftleri gelir. Kazara karıştırması kolay.

Birikimli toplam

Aşağı sürükle. Çapa başlangıcı kilitler, göreli son ilerledikçe büyür.

Temel VLOOKUP

A2'yi Products A sütununda bulur, 3. sütunu döndürür. FALSE tam eşleşme demektir.

INDEX/MATCH (daha iyisi)

Biscuit bunu tercih eder. Sütun eklemelerine dayanır ve sola bakabilir.

XLOOKUP (yeni Excel)

Modern yerine geçer. Daha temiz, hataları daha iyi yönetir. Google Sheets'te henüz desteklenmiyor.

Yaklaşık eşleşme (sıralı veri)

TRUE, A2'den küçük ya da eşit en büyük değeri bul demektir. Vergi dilimleri için iyidir.

İki yönlü arama

Satır ve sütun ikisi de dinamik olarak eşleştirilir.

Boşlukları kırp

Baştaki, sondaki ve içerideki tekrarlanan boşlukları kaldırır.

Baş harf büyük

"hello world" "Hello World" olur.

Ayırıcıyla birleştir

TRUE boş hücreleri yok sayar.

E-postadan alan adını çıkar

FIND @ işaretini bulur, MID ondan sonraki her şeyi alır.

Regex extract (Google Sheets)

Her türlü karmaşanın içinden ilk e-posta adresini çeker.

Regex replace

Tüm boşlukları tek boşluğa indirger.

Bugünün tarihi

Sayfa her yeniden hesaplandığında güncellenir.

İki tarih arasındaki gün

Tarihler arka planda sayıdır. Çıkarma kendiliğinden çalışır.

Tarihten ay adı

"Ocak", "Şubat" gibi değerleri döndürür.

İçinde bulunulan ayın ilk günü

EOMONTH "ayın sonu" demektir. Bir ay geri git, bir gün ekle.

ISO haftasının başı (Pazartesi)

Tip 3 ile WEEKDAY Pazartesi için 0 döner, yani çıkarma seni Pazartesi'ye indirir.

Tarihler arasındaki iş günleri

Cumartesi, Pazar ve isteğe bağlı tatilleri dışlar.

Tüm bir sütuna formül uygula

Binlerce satırı aşağı sürüklemek tarih oldu.

SQL benzeri söz dizimiyle QUERY

SQL yazıyormuş gibi davran. Gerçekten çalışıyor.

FILTER

Eşleşen satırları döndürür. Çirkin dizi formülü hileleri gerekmez.

UNIQUE

A sütunundaki tüm farklı değerleri döndürür. Alfabetik değil, bulunuşa göre sıralar.

SORT

İkinci sütuna göre azalan düzende sıralar.

Aktif olan hariç tüm sayfaları gizle

Sub HideOthers()
  Dim s As Worksheet
  For Each s In ThisWorkbook.Worksheets
    If s.Name <> ActiveSheet.Name Then s.Visible = xlSheetHidden
  Next s
End Sub

Çalışma kitabını üzerinde gerçekten çalıştığın şeye hızlıca indir.

Kullanılan aralıktaki boş satırları sil

Sub KillEmpties()
  Dim r As Long
  For r = ActiveSheet.UsedRange.Rows.Count To 1 Step -1
    If Application.CountA(Rows(r)) = 0 Then Rows(r).Delete
  Next r
End Sub

Sildikçe satır numaraları kaymasın diye geriye doğru ilerler.

Bir klasördeki tüm dosyaları aç

Sub LoopFolder()
  Dim f As String
  f = Dir("C:\path\to\folder\*.xlsx")
  Do While f <> ""
    Workbooks.Open "C:\path\to\folder\" & f
    f = Dir()
  Loop
End Sub

Klasik Dir döngüsü. Aylık raporlarla dolu bir klasörün varsa mükemmel.