Idea untuk hujung minggu ini datang dari perbincangan dengan Dr. M, pengarang buletin petua Quicken mingguan yang hebat.
Saya suka Quicken, tetapi ia pasti menjengkelkan. Saya mempunyai laporan hafalan tertentu di Quicken dengan kategori di samping dan bulan-bulan berlalu. Quicken menawarkan peluang untuk mencetak laporan ini, tetapi tentu saja, saya selalu menggunakan perintah Salin untuk menyalin laporan ke papan keratan dan kemudian menggunakan Edit> Tampal di Excel untuk menyalin laporan ke papan keratan. Ciri ini jauh lebih pantas daripada cetakan yang lebih lama (dan masih ada) ke pilihan fail .prn.
Di sinilah kegusaran muncul. Pertama, Quicken tidak peduli untuk menyalin tajuk lajur dengan laporan. Oleh itu, saya secara manual mesti memasukkan nama bulan di Excel. Cukup sederhana. Kedua, kategori yang disalin ke papan keratan merangkumi format garis besar yang menjengkelkan untuk kategori dan subkategori.
Quicken membolehkan anda menggunakan kategori dan subkategori untuk mengelaskan perbelanjaan anda. Dalam laporan di sebelah kiri, perbelanjaan automotif dibahagikan dengan insurans, petrol, pembaikan dan plat kenderaan. Mungkin saya melakukan tugas yang sukar untuk menetapkan kategori saya, tetapi saya mendapati bahawa saya mempunyai beberapa kategori di mana saya ingin melihat perincian subkategori dan kategori lain di mana saya lebih suka melihat keseluruhan kategori.
Saya juga ingin menyusun laporan ini di Excel. Adalah berguna untuk menyusunnya mengikut jumlah perbelanjaan, kemudian menyusunnya mengikut kategori. Pasti, saya boleh membuat asal, tetapi saya mahu kategori yang mengikut abjad. Ringkasnya, saya tidak peduli dengan format garis besar yang digunakan oleh Quicken.
Pembaca lama akan mengingati penghinaan saya terhadap format garis besar yang digunakan oleh jadual pangsi di Isi Pivot Table Blank Cells Menggunakan Go to Special tip. Kami mempunyai keadaan yang sama di sini. Sekiranya laporan Quicken hanyalah langkah pertengahan dan anda mahu menyusun mengikut kategori, format garis besarnya mengerikan. Setelah diisih mengikut jumlah dan kemudian mengikut kategori, kategori Auto: Insurans akan disusun salah ke bahagian "I" dalam laporan. Untuk kategori di mana saya hanya menyimpan jumlahnya, kategori tersebut akan disusun secara salah ke bahagian "T" dalam laporan.
Terdapat dua utiliti yang saya fikir dapat meredakan keadaan ini. Utiliti satu dipanggil runtuh. Apabila dipanggil, makro ini akan meruntuhkan sub-kategori menjadi satu baris dengan nama kategori yang betul. Dalam contoh di atas, menjalankan makro sementara penunjuk sel berada di mana saja dalam baris 34 hingga 38 akan menggantikan kategori di A38 dengan "Komputer" dan menghapus baris 34 hingga 37.
Utiliti dua adalah untuk kategori di mana saya ingin melihat perincian subkategori, tetapi tidak memerlukan tajuk, garis subtotal putus-putus, atau jumlah kategori. Utiliti ini dipanggil Fill. Ia akan mencari nama kategori yang tepat, dan awalan setiap subkategori dengan katate. Dalam contoh di atas, menjalankan makro sementara penunjuk sel berada di mana saja dalam baris 24 hingga 30 akan menghasilkan sel A25: A28 diubah menjadi format seperti "Auto: Insurans". Baris 24, 29 dan 30 akan dihapuskan.
Di sebelah kanan, adakah versi laporan saya lebih baik. Dengan menetapkan Kekunci dan Isi pada butang panas, saya dapat membuat perubahan ini hanya dengan beberapa ketukan kekunci. Kini mudah untuk menyusun laporan, dengan mengetahui bahawa laporan dapat kembali ke urutan asalnya dengan menyusun kategori.
Sekiranya anda baru menggunakan makro, tinjau Memperkenalkan Editor VBA Excel.
Sebaik sahaja anda menyalin makro, anda boleh menetapkan kunci panas dengan mengikuti langkah-langkah berikut:
- Dari Menu Alat, Pilih Makro kemudian Makro
- Serlahkan makro Isi. Klik Pilihan. Di medan Pintasan, masukkan sebarang huruf. Saya menggunakan f untuk Isi. Klik OK
- Serlahkan makro keruntuhan. Klik Pilihan. Pilih huruf untuk jalan pintas, tetapi jauhkan dari c, kerana Ctrl + c adalah jalan pintas yang biasa
- untuk Edit> Salin. Klik OK
- Tutup dialog makro dengan Batal.
Sebagai sebahagian daripada usahanya untuk merancang tambahan dalam sehari, pelatih musim panas, Anhtuan Do membuat makro berikut.
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
