Ý tưởng cho mẹo của tuần này đến từ cuộc trò chuyện với Tiến sĩ M, tác giả của bản tin mẹo Quicken hàng tuần tuyệt vời.
Tôi yêu Quicken, nhưng nó chắc chắn có những khó chịu. Tôi có một báo cáo được ghi nhớ nhất định trong Quicken với các danh mục ở bên cạnh và các tháng sẽ ở trên cùng. Quicken cung cấp cơ hội in báo cáo này, nhưng tất nhiên, tôi luôn chỉ sử dụng lệnh Sao chép để sao chép báo cáo vào khay nhớ tạm và sau đó sử dụng Chỉnh sửa> Dán trong Excel để sao chép báo cáo vào khay nhớ tạm. Tính năng này nhanh hơn nhiều so với tùy chọn in ra tệp .prn cũ hơn (và vẫn có sẵn).
Đây là nơi mà những khó chịu bật lên. Đầu tiên, Quicken không bận tâm sao chép các tiêu đề cột với báo cáo. Vì vậy, tôi phải nhập tên tháng trong Excel theo cách thủ công. Đủ đơn giản. Thứ hai, các danh mục được sao chép vào khay nhớ tạm bao gồm định dạng đường viền khó chịu cho các danh mục và tiểu mục.
Quicken cho phép bạn sử dụng các danh mục và tiểu mục để phân loại chi phí của mình. Trong báo cáo bên trái, chi phí ô tô được chia nhỏ hơn bao gồm bảo hiểm, xăng, sửa chữa và biển số. Có lẽ tôi đã thiết lập các danh mục của mình không tốt, nhưng tôi thấy rằng tôi có một số danh mục mà tôi muốn xem chi tiết danh mục phụ và các danh mục khác mà tôi muốn chỉ xem tổng danh mục.
Tôi cũng muốn có thể sắp xếp báo cáo này trong Excel. Sẽ rất hữu ích nếu bạn sắp xếp nó theo tổng chi phí, sau đó sắp xếp lại theo danh mục. Chắc chắn, tôi có thể sử dụng hoàn tác, nhưng tôi muốn các danh mục có bản chất là bảng chữ cái. Tóm lại, tôi không quan tâm đến định dạng phác thảo được Quicken sử dụng.
Người đọc lâu năm sẽ nhớ thái độ khinh thường của tôi đối với định dạng phác thảo được sử dụng bởi bảng tổng hợp trong Điền vào ô trống trong bảng tổng hợp bằng cách sử dụng mẹo đặc biệt. Chúng tôi có cùng một tình huống ở đây. Nếu báo cáo Quicken chỉ là một bước trung gian và bạn muốn có thể sắp xếp theo danh mục, thì định dạng phác thảo là rất kinh khủng. Sau khi sắp xếp theo tổng và sau đó theo danh mục, danh mục Tự động: Bảo hiểm sẽ bị phân loại sai thành phần "Tôi" của báo cáo. Đối với các danh mục mà tôi chỉ giữ lại tổng số, chúng sẽ được sắp xếp không chính xác vào phần "T" của báo cáo.
Có hai tiện ích mà tôi nghĩ sẽ giảm bớt tình trạng này. Tiện ích một được gọi là sụp đổ. Khi được gọi, macro này sẽ thu gọn một danh mục con thành một dòng với tên danh mục riêng. Trong ví dụ trên, chạy macro trong khi con trỏ ô ở bất kỳ đâu trong các hàng từ 34 đến 38 sẽ thay thế danh mục trong A38 bằng "Máy tính" và xóa các hàng từ 34 đến 37.
Tiện ích hai dành cho các danh mục mà tôi muốn xem chi tiết danh mục con, nhưng không cần tiêu đề, dòng tổng phụ gạch ngang, cũng như tổng danh mục. Tiện ích này được gọi là Fill. Nó sẽ tìm tên danh mục thích hợp và đặt tiền tố cho mỗi danh mục con bằng phân loại. Trong ví dụ trên, việc chạy macro trong khi con trỏ ô ở bất kỳ đâu trong các hàng từ 24 đến 30 sẽ dẫn đến các ô A25: A28 được thay đổi thành định dạng như "Tự động: Bảo hiểm". Các hàng 24, 29 và 30 sẽ bị xóa.
Ở bên phải, là phiên bản báo cáo cải tiến của tôi. Bằng cách gán Thu gọn và Điền cho các phím nóng, tôi có thể thực hiện những thay đổi này chỉ bằng một vài lần nhấn phím. Giờ đây, thật dễ dàng để sắp xếp báo cáo, biết rằng báo cáo có thể trở lại trình tự ban đầu bằng cách sắp xếp danh mục.
Nếu bạn chưa quen với macro, hãy xem lại Giới thiệu Trình soạn thảo Excel VBA.
Khi bạn sao chép macro, bạn có thể gán một phím nóng bằng cách làm theo các bước sau:
- Từ Trình đơn Công cụ, Chọn Macro rồi chọn Macro
- Đánh dấu macro Điền. Nhấp vào Tùy chọn. Trong trường Phím tắt, hãy nhập bất kỳ chữ cái nào. Tôi sử dụng f cho Điền. Bấm OK
- Đánh dấu macro thu gọn. Nhấp vào Tùy chọn. Chọn một chữ cái cho một phím tắt, nhưng tránh xa c, vì Ctrl + c là phím tắt phổ biến
- cho Chỉnh sửa> Sao chép. Bấm OK
- Đóng hộp thoại macro bằng Hủy.
Là một phần của nhiệm vụ thiết kế tiện ích bổ sung một ngày, thực tập sinh mùa hè của Anhtuan Do, Anhtuan Do đã tạo các macro sau.
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
