Bu haftanın ipucu fikri, harika haftalık Quicken ipuçları bülteninin yazarı Dr. M ile sohbet etmekten geldi.
Quicken'ı seviyorum ama kesinlikle sıkıntıları var. Quicken'da kategoriler ve aylar en üstte olan ezberlenmiş bir raporum var. Quicken bu raporu yazdırma fırsatı sunuyor, ancak elbette raporu panoya kopyalamak için her zaman Kopyala komutunu kullanıyorum ve ardından raporu panoya kopyalamak için Excel'de Düzenle> Yapıştır'ı kullanıyorum. Bu özellik, eski (ve hala mevcut) bir .prn dosyasına yazdırma seçeneğinden çok daha hızlıdır.
Rahatsızlıkların ortaya çıktığı yer burasıdır. Birincisi, Quicken, sütun başlıklarını raporla birlikte kopyalama zahmetine girmedi. Bu yüzden, Excel'de ay adlarını manuel olarak girmeliyim. Yeterince basit. İkincisi, panoya kopyalanan kategoriler, kategoriler ve alt kategoriler için can sıkıcı bir taslak biçimi içerir.
Quicken, harcamalarınızı sınıflandırmak için kategorileri ve alt kategorileri kullanmanıza olanak tanır. Soldaki raporda, otomotiv giderleri sigorta, benzin, onarımlar ve plakalara göre daha da ayrılmıştır. Belki de kategorilerimi oluşturmakta kötü bir iş çıkardım, ancak alt kategori ayrıntısını görmek istediğim bazı kategoriler ve sadece kategori toplamını görmeyi tercih ettiğim diğer kategoriler olduğunu fark ettim.
Bu raporu Excel'de de sıralayabilmek istiyorum. Toplam gidere göre sıralamak ve ardından kategoriye göre sıralamak faydalı olacaktır. Elbette, geri almayı kullanabilirim, ancak doğası gereği alfabetik olan kategorileri istiyorum. Kısacası, Quicken tarafından kullanılan anahat formatını umursamıyorum.
Uzun zamandır okuyucular, Pivot Tabloyu Boş Hücrelerde Doldurma Özelliğine Git ipucunu kullanarak pivot tablolar tarafından kullanılan anahat biçimine yönelik küçümsememi hatırlayacaklar. Burada da aynı durum var. Quicken raporu yalnızca bir ara adımsa ve kategoriye göre sıralayabilmek istiyorsanız, anahat biçimi korkunçtur. Toplamlara ve ardından kategoriye göre sıraladıktan sonra, Otomatik: Sigorta kategorisi, raporun "I" bölümünde yanlış sıralanacaktır. Yalnızca toplamı sakladığım kategoriler için, raporun "T" bölümüne yanlış bir şekilde sıralanacaklar.
Bu durumu kolaylaştıracağını düşündüğüm iki araç vardı. Yardımcı programa daraltma denir. Bu makro çağrıldığında, bir alt kategoriyi uygun bir kategori adıyla tek bir satıra daraltır. Yukarıdaki örnekte, hücre işaretçisi 34 ila 38 arasındaki satırlarda herhangi bir yerdeyken makroyu çalıştırmak, A38'deki kategoriyi "Bilgisayar" ile değiştirecek ve 34 ila 37 arasındaki satırları silecektir.
İkinci yardımcı program, alt kategori ayrıntısını görmek istediğim, ancak başlığa, kesikli ara toplam satırına veya kategori toplamına ihtiyaç duymadığım kategoriler içindir. Bu yardımcı programa Fill adı verilir. Uygun kategori adını bulacak ve her alt kategorinin önüne kategori koyacaktır. Yukarıdaki örnekte, hücre işaretçisi 24 ile 30 arasındaki satırlar arasında herhangi bir yerdeyken makroyu çalıştırmak, A25: A28 hücrelerinin "Otomatik: Sigorta" gibi bir biçime değiştirilmesine neden olacaktır. 24, 29 ve 30. satırlar silinecek.
Sağda, raporun geliştirilmiş versiyonu var. Daralt ve Doldur'u kısayol tuşlarına atayarak, bu değişiklikleri sadece birkaç tuşa basarak yapabildim. Raporun kategoriyi sıralayarak orijinal sırasına dönebileceğini bilerek raporu sıralamak artık çok kolay.
Makrolarda yeniyseniz, Excel VBA Düzenleyicisine Giriş bölümünü inceleyin.
Makroyu kopyaladıktan sonra, aşağıdaki adımları izleyerek bir kısayol tuşu atayabilirsiniz:
- Araçlar Menüsünden Makroları, ardından Makroları Seçin
- Makroyu doldurun. Seçenekler'i tıklayın. Kısayol alanına herhangi bir harf girin. Fill için f kullanıyorum. Tamam'ı tıklayın
- Daraltma makrosunu vurgulayın. Seçenekler'i tıklayın. Kısayol için bir harf seçin, ancak ortak kısayol Ctrl + c olduğundan c'den uzak durun
- Düzenle> Kopyala için. Tamam'ı tıklayın
- Makro iletişim kutusunu İptal ile kapatın.
Günde bir eklenti tasarlama arayışının bir parçası olarak, yaz stajyerinin Anhtuan Do aşağıdaki makroları yarattı.
Option Explicit Dim Flag, Flag2 As Boolean 'Flags to keep the Find Procedures running Dim HeaderRow, TotalRow As Integer 'Number of the Header and Total Rows Dim Counter As Integer 'Counter to ensure searches are relative to activecell Dim TempString, TempTest As String 'Strings that are used as temporary holders to compare Dim CategoryName As String 'Name of the category currently in Sub Collapse() 'To collapse the rows, run this macro Flag = False Flag2 = False Counter = -1 'Continue looping until HeaderRow is found Do Until Flag = True Counter = Counter + 1 Call FindHeader Loop 'Create CategoryName CategoryName = Left(CategoryName, Len(CategoryName) - 1) CategoryName = Trim(CategoryName) 'Assign HeaderRow HeaderRow = ActiveCell.Row - Counter Counter = 0 'Continue looping until TotalRow is found Do Until Flag2 = True Counter = Counter + 1 Call FindTotal Loop 'Assign TotalRow TotalRow = ActiveCell.Row + Counter 'Replace "TOTAL Category" with "Category" Cells(TotalRow, 1).Value = " " & CategoryName 'Deleting sub-category rows Rows(HeaderRow & ":" & TotalRow - 1).Delete Shift:=xlUp End Sub Sub Fill() 'To add the CategoryName to each of the types, run this macro Dim i As Integer Flag = False Flag2 = False Counter = -1 'Continue looping until HeaderRow is found Do Until Flag = True Counter = Counter + 1 Call FindHeader Loop 'Create CategoryName CategoryName = Left(CategoryName, Len(CategoryName) - 1) CategoryName = Trim(CategoryName) 'Assign HeaderRow HeaderRow = ActiveCell.Row - Counter Counter = 0 'Continue looping until TotalRow is found Do Until Flag2 = True Counter = Counter + 1 Call FindTotal Loop 'Assign TotalRow TotalRow = ActiveCell.Row + Counter 'Adding CategoryName and colon to the start of each sub category For i = HeaderRow + 1 To TotalRow - 2 TempString = Trim(Cells(i, 1).Value) Cells(i, 1).Value = " " & CategoryName & ": " & TempString Next i 'Deleting Header and Total Row Rows(TotalRow & ":" & TotalRow - 1).Delete Shift:=xlUp Rows(HeaderRow).Delete Shift:=xlUp End Sub Sub FindHeader() Dim i As Integer TempString = Cells(ActiveCell.Row - Counter, 1).Value 'Checking to see if in first row If (ActiveCell.Row - Counter) = 1 Then MsgBox "You are not in a collapsable row" End If 'Checking to see if in a valid row 'If it reaches a TOTAL before reaching a colon, then error If Left(Trim(TempString), 5) = "TOTAL" Then MsgBox "You are not in a collapsable row" End If 'Checking the String for a colon For i = 1 To Len(TempString) TempTest = Mid(TempString, i, 1) If TempTest = ":" Then CategoryName = TempString Flag = True Exit For End If Next i End Sub Sub FindTotal() Dim i As Integer 'Finding the TOTAL Row TempString = Cells(ActiveCell.Row + Counter, 1).Value TempString = Trim(TempString) If TempString = "TOTAL " & CategoryName Then Flag2 = True End If End Sub
