Excel’de banka işlemlerinizi nasıl kategorilere ayırabilirsiniz?
Kişisel veya iş finanslarını yönetmek genellikle aylık banka işlemlerinizin ayrıntılı listesini incelemeyi gerektirir. Bu işlem kayıtları, restoranlardan mağazalara, faturalardan hizmetlere kadar çok çeşitli açıklamalar içerebilir. Her işlemi "Takeout", "Groceries", "Utilities" veya "Family fee" gibi net bir kategoriye yerleştirebildiğinizde harcamalarınızı izlemek ve analiz etmek çok daha kolay ve anlaşılır hale gelir. Excel’de işlem açıklamalarındaki belirli anahtar kelimelere göre sınıflandırma sürecini otomatikleştirerek, her ay maliyetleriniz ve harcama alışkanlıklarınız hakkında net içgörülere ulaşabilirsiniz.
Aşağıdaki ekran görüntüsünde gösterildiği gibi, B sütununda satıcı veya hizmet detaylarını içeren ham verilere sahip olduğunuzu ve basitleştirilmiş bir kategori kümesi istediğinizi varsayalım (örneğin ")Mc Donalds" içeren her işlem "Takeout" olarak tanımlanmalı, "Walmart" ise "Family fee" olarak sınıflandırılmalıdır). Bu adım adım eğitim, formül tabanlı çözümlerin yanı sıra gelişmiş otomasyon yöntemlerini de kapsayan birkaç pratik yaklaşımı ele almaktadır.
İçindekiler:
Excel’de formül kullanarak banka işlemlerini kategorilere ayırma
VBA Kodu – Önceden tanımlanmış bir listeye göre kategorilendirmeyi otomatikleştiren makro
Diğer Yerleşik Excel Yöntemleri – Koşullu sütun mantığıyla Power Query kullanımı
Excel’de formül kullanarak banka işlemlerini kategorilere ayırma
Excel’de banka işlemlerinizi kategorilere ayırmak için basit ve formül temelli bir yöntem arıyorsanız, aşağıdaki adımları izleyin. Bu yaklaşım, hızlı sonuç almak isteyen ve anahtar kelimelerle kategorilerden oluşan küçük bir arama tablosunu kolayca güncelleyebilen kullanıcılar için idealdir.
Öncelikle ana işlem listenizin dışında işlem açıklamalarındaki olası içeriklere karşılık gelen anahtar kelimeleri içeren bir sütun ve her anahtar kelimeye bağlanmasını istediğiniz kategoriyi içeren ikinci bir sütun olmak üzere iki "yardımcı sütun" oluşturun.
1. Bu örnekte, eşleşecek tüm anahtar kelimeler (örneğin "Mc Donalds", "Walmart" vb.) A sütununun 30 ila 41. satırları arasında; ilgili kategori adları ("Takeout", "Family fee" vb.) ise B sütununun 30 ila 41. satırlarındadır.
Anahtar kelime veya kategori listeniz daha kapsamlıysa ya da sık sık değişiyorsa, tüm ihtiyaçlarınızı karşılayacak şekilde aralıkları buna göre ayarlayın. Ekran görüntüsüne bakın:
2. Ardından, hedeflediğiniz çıktı sütunundaki ilk hücreye tıklayın (örneğin, verilerinizin Son Sütunu’nun yanındaki F3 hücresi), ardından aşağıdaki dizi formülünü girin. Bu bir dizi formülü olduğundan, onaylamak için yalnızca Enter’a değil, aynı zamanda Ctrl + Shift + Enter tuşlarına basmanız gerekir. Böylece açıklama içindeki ilk anahtar kelime bulunacak ve eşleşen kategori döndürülecektir.
=IFERROR(INDEX(B$30:B$41,MATCH(TRUE,ISNUMBER(SEARCH($A$30:$A$41,B3)),0)),"Other")
İlk satırda formül çalışır hale geldikten sonra, kategorilendirme formülünü kalan tüm işlem satırlarına uygulamak için sütunu aşağı doğru Otomatik Doldurma Sapı’nı sürükleyin.
Parametre açıklamaları:
Avantajlar ve dezavantajlar analizi:Bu formüle dayalı yaklaşım, kategorilendirme kurallarınız sık değişmiyorsa hızlı kurulum ve düşük bakım sunar. Ancak anahtar kelime listeniz daha karmaşık hale gelirse ya da kategorileri sık sık güncellemeniz gerekiyorsa, yardımcı sütunları ve formülleri yönetmek yorucu olabilir; böyle bir durumda otomasyon için VBA veya Power Query’i değerlendirmenizi öneririz.
Pratik ipucu: Bir açıklamada birden fazla anahtar kelime eşleşirse, yalnızca listenizde ilk sıradaki kelime kategoriyi belirler. Belirli bir anahtar kelimeye öncelik vermek için onu arama sütununda daha yukarı taşıyın.
Yaygın sorun giderme:Beklenmeyen sonuçlar alıyorsanız, arama aralığını tekrar kontrol edin ve anahtar kelimelerinizin tutarlı ve eksiksiz şekilde yazıldığından emin olun. Ayrıca işlem açıklamalarınızda fazladan boşluklar veya biçimlendirme farklılıkları olup olmadığını kontrol edin.
VBA Kodu – İşlem açıklamalarını önceden tanımlanmış bir listeye göre kategorilere eşleştiren makroyla kategorilendirmeyi otomatikleştirme
Bu çözüm, işlem-kategori eşleştirmesini daha kontrollü ve ölçeklenebilir şekilde otomatikleştirmek için bir VBA makrosu kullanır. Özellikle işlem hacmi yüksek olan kullanıcılar veya anahtar kelime-kategori referansını dinamik hale getirip manuel formül yönetimini en aza indirmek isteyenler için son derece faydalıdır.
Uygulanabilir senaryo: İşlem listesi uzunsa, güncellemeler sık sık yapılıyorsa veya manuel formüllerin bakımını önlemek istiyorsanız, VBA makroları her açıklamayı işleyip uygun kategoriyi çok daha verimli bir şekilde atayabilir; özelleştirilebilir mantık ve uyarılar sunar.
Uygulama adımları:
- Formül yönteminde kullanılan yardımcı sütunlara benzer şekilde (örneğin, A ve B sütunlarında 30. satırdan itibaren olduğu gibi) bir anahtar kelime-kategori listesi oluşturun.
- Alt + F11 tuşlarına basarak Visual Basic for Applications düzenleyicisini açın. VBA penceresinde Ekle > Modül seçeneğine tıklayarak yeni bir modül ekleyin.
Aşağıdaki kodu modüle kopyalayıp yapıştırın:
Sub CategorizeTransactions()
Dim lastRow As Long
Dim i As Long
Dim descCell As Range
Dim kwRow As Long
Dim kwRange As Range
Dim catRange As Range
Dim kwCount As Long
Dim catResult As String
Dim matched As Boolean
On Error Resume Next
xTitleId = "KutoolsforExcel"
kwCount = Cells(Rows.Count, "A").End(xlUp).Row - 29
Set kwRange = Range("A30:A" & 29 + kwCount)
Set catRange = Range("B30:B" & 29 + kwCount)
lastRow = Cells(Rows.Count, "B").End(xlUp).Row
For i = 3 To lastRow
Set descCell = Cells(i, "B")
catResult = "Other"
matched = False
For kwRow = 1 To kwCount
If InStr(1, descCell.Value, kwRange.Cells(kwRow, 1).Value, vbTextCompare) > 0 Then
catResult = catRange.Cells(kwRow, 1).Value
matched = True
Exit For
End If
Next kwRow
Cells(i, "F").Value = catResult
Next i
End SubNasıl kullanılır:
Çalıştır düğmesine tıklayın veya makroyu çalıştırmak için VBA düzenleyicide F5 tuşuna basın. Makro, B sütunundaki her işlem açıklamasını işleyecek ve anahtar kelime listesiyle karşılaştırarak eşleşen kategoriyi (eşleşme bulunamazsa "Other") aynı satırın F sütununa yazacaktır.- Makro içindeki satır sayısını ve aralıkları, veri düzeninize uygun şekilde özelleştirebilirsiniz—örneğin işlem açıklamalarının başladığı satırı veya yardımcı liste konumunu değiştirebilirsiniz. Anahtar kelime listenizin boş hücre içermemesine, kategorilerin ise açık, benzersiz ve kolayca başvurulabilir olmasına dikkat edin.
Çalıştır düğmesine tıklayın veya makroyu çalıştırmak için VBA düzenleyicide F5 tuşuna basın. Makro, B sütunundaki her işlem açıklamasını işleyecek ve anahtar kelime listesiyle karşılaştırarak eşleşen kategoriyi (eşleşme bulunamazsa "Other") aynı satırın F sütununa yazacaktır.Avantajlar ve dezavantajlar analizi:VBA çözümü, daha karmaşık kurallara kolayca uyarlanabilir; anahtar kelime veya kategori listenizi güncelledikten sonra istediğiniz zaman yeniden çalıştırılabilir ve dizi formüllerine olan gereksinimi tamamen ortadan kaldırır. Ancak bu makroların çalışabilmesi için Excel’de otomatik işlemlerin etkinleştirilmesi gerekir—ki bu durum her kullanıcı veya ortam için uygun olmayabilir.
Pratik ipucu: VBA makronuzu yeniden kullanılabilir bir dosyada saklayın ve yanlışlıkla üzerine yazılmasına karşı her zaman verilerinizin yedeklerini alın.
Sorun giderme önerileri:Kategoriler beklenildiği gibi güncellenmiyorsa, anahtar kelimelerinizin ve kategori listelerinizin uyumlu olduğundan ve hücrelerde gizli karakterler olmadığından emin olun. VBA, "vbTextCompare" kullanılırsa büyük/küçük harfe duyarlı değildir, ancak biçimlendirme nedeniyle eşleşmelerde hâlâ sorunlar oluşabilir.
Diğer Yerleşik Excel Yöntemleri – Koşullu sütun mantığıyla kural tabanlı kategorilendirmeyi ayarlamak için Power Query kullanma
Modern ve kod gerektirmeyen bir otomasyon yaklaşımı arıyorsanız, Power Query banka işlemlerinizi kategorilere ayırmanın güçlü bir yoludur. Bu yöntem, işlem verilerinizi CSV dosyalarından veya çevrimiçi kaynaklardan içe aktarırken ya da dinamik ve gelişen kategorilendirme kurallarını yönetirken idealdir; çünkü mantığı merkezileştirir ve karmaşık formüllerle ya da VBA betikleriyle uğraşmadan kolay güncellemeler sağlar.
Uygulama adımları:
- Öncelikle verilerinizin bir tablo olarak biçimlendirildiğinden veya net bir Veri Aralığı tanımlandığından emin olun. Tablonuzdaki herhangi bir hücreyi seçtikten sonra Veri > Tablodan/Aralıktan seçeneğine giderek verileri Power Query’ye yükleyin.
- Power Query Düzenleyici’nde kategoriler için yeni bir kural tabanlı sütun oluşturmak üzere Sütun Ekle > Koşullu Sütun seçeneğine tıklayın.
- Eşleştirme kurallarınızı tanımlayın; örneğin:
- Eğer Açıklama"Mc Donalds" içeriyorsa, "Takeout" çıktısını verin
- Eğer Açıklama"Walmart" içeriyorsa, "Family fee" çıktısını verin
- Aksi takdirde, "Other" çıktısını verin
- Koşullu sütunu oluşturmak için Tamam’a tıklayın, ardından kategorilendirilmiş sonuçlarınızı Excel’e geri döndürmek için Kapat ve Yükle seçeneğini belirleyin.
Avantajlar ve dezavantajlar analizi:Power Query, kural değişikliklerine esneklik sağlar, harici veri içe aktarımlarıyla sorunsuz entegre olur ve işlem verisi ya da kurallar güncellendiğinde sonuçları anında yeniler. Ayrıca çok büyük veri kümelerini, elle yazılmış formüllere kıyasla çok daha verimli şekilde işler. Ancak Power Query’nin temel kurulumu ve arayüzüne alışmak gerekir; bu da bazı kullanıcılar için yeni bir deneyim olabilir.
Pratik ipuçları:Kurallarınızı Koşullu Sütun iletişim kutusunda öncelik sırasına göre (üstten alta) düzenleyin; ilk eşleşen kural uygulanacaktır. Power Query’de kuralları kolayca düzenleyebilir, ekleyebilir veya silebilirsiniz; bunun için ana Excel çalışma sayfası formüllerinizi değiştirmenize gerek kalmaz.
Hata hatırlatıcıları: Yeni işlemleri içe aktarırsanız ancak sonuçlar beklenildiği gibi yenilenmezse, Excel’in Veri sekmesinden Yenile seçeneğine tıklayın. Power Query’de yazım hatalarına ve tam anahtar kelime eşleşmelerine dikkat edin; kısmi eşleşmeler doğru kategorinin atanmasını engelleyebilir.
Sorun giderme önerisi:Kategorileriniz doğru şekilde görünmüyorsa, çakışmalar veya eksik durumlar olup olmadığını görmek için koşullu sütun kurallarınızı gözden geçirin. Değişiklikleri tüm veri kümesine uygulamadan önce birkaç örnek girdiyle test etmeniz faydalı olur.
Özetle, dizi formüllerini mi, VBA makrolarının esnekliğini mi yoksa Power Query’nin akıcı otomasyonunu mu seçtiğiniz fark etmez; Excel, banka işlemlerinizi verimli bir şekilde kategorize etmek için çeşitli pratik yöntemler sunar. İşlem verileriniz veya kategorilendirme kriterleriniz değiştiğinde, kategorilendirme kurallarınızı ve sonuçların tutarlılığını mutlaka gözden geçirin.
En İyi Office Verimlilik Araçları
Kutools for Excel ile Excel becerilerinizi üst düzeye taşıyın ve hiç yaşanmamış bir verimlilik deneyimi yaşayın.Kutools for Excel, verimliliği artırmak ve Zamanı Kaydet için 300'den fazla gelişmiş özelliğe sahiptir.İhtiyacınız olan özelliği hemen alınmak için buraya tıklayın…
Office Tab, Office uygulamalarına sekmeli bir arayüz kazandırarak işlerinizi çok daha kolay hale getirir.
- Word, Excel ve PowerPoint içinde sekmeli düzenleme ve okumayı etkinleştirin, Publisher, Access, Visio ve Project.
- Birden fazla belgeyi yeni pencerelerde değil, aynı pencerenin yeni sekmelerinde açın ve oluşturun.
- Günlük verimliliğinizi %50 artırır ve her gün yüzlerce fare tıklamasından tasarruf etmenizi sağlar!
Tüm Kutools eklentileri, tek bir yükleyiciyle!
Kutools for Office paketi; Excel, Word, Outlook ve PowerPoint için eklentileriyle birlikte Office Tab Pro’yu da içerir ve Office uygulamalarında çalışan ekipler için idealdir.
- Hepsi bir arada paket— Excel, Word, Outlook ve PowerPoint eklentileri + Office Tab Pro
- Tek yükleyici, tek lisans— birkaç dakikada kurulum (MSI uyumlu)
- Birlikte daha iyi çalışır— Office uygulamaları arasında akıcı verimlilik
- 30 günlük tam özellikli deneme— kayıt gerekmez, kredi kartı gerekmez
- En iyi fiyat performans— tek tek eklenti satın alımına göre tasarruf edin