Tämän viikon vinkin idea syntyi keskustelemalla tohtori M: n kanssa, joka kirjoitti suuren viikoittaisen Quicken tips -uutiskirjeen.
Rakastan Quickenia, mutta sillä on varmasti ärsytyksiä. Minulla on tietty muistissa oleva raportti Quickenissä, jossa luokat alaspäin ja kuukaudet ylittävät huipun. Quicken tarjoaa mahdollisuuden tulostaa tämä raportti, mutta tietenkin käytän aina vain Kopioi-komentoa kopioidaksesi raportin leikepöydälle ja sitten Muokkaa> Liitä Exceliin kopioidaksesi raportin leikepöydälle. Tämä ominaisuus on paljon nopeampi kuin vanhempi (ja edelleen käytettävissä oleva) .prn-tiedostoon tulostaminen.
Täällä ärsytykset nousevat esiin. Ensinnäkin Quicken ei vaivautunut kopioimaan sarakeotsikoita raportin kanssa. Joten minun on annettava kuukausien nimet manuaalisesti Excelissä. Tarpeeksi yksinkertainen. Toiseksi leikepöydälle kopioitavat luokat sisältävät ärsyttävän muotoilun kategorioille ja alaluokille.
Quickenin avulla voit luokitella kulut luokkiin ja alaluokkiin. Vasemmalla olevassa raportissa autokulut jaotellaan edelleen vakuutusten, bensiinin, korjausten ja rekisterikilpien mukaan. Ehkä tein huonosti työtä luokkien luomisessa, mutta huomaan, että minulla on joitain luokkia, joissa haluan nähdä alakategorian yksityiskohdat ja muut luokat, joissa haluaisin nähdä vain luokan kokonaismäärän.
Haluaisin myös pystyä lajittelemaan tämän raportin Excelissä. Olisi hyödyllistä lajitella se kokonaiskustannusten mukaan ja sitten lajitella takaisin luokittain. Toki voisin käyttää kumoa, mutta haluaisin luokat, jotka ovat luonteeltaan aakkosellisia. Lyhyesti sanottuna, en välitä Quickenin käyttämästä ääriviivamuodosta.
Pitkään lukijat muistavat halveksunani muotoilumuotoa, jota pivot-taulukot käyttävät Täytä pivot-taulukon tyhjät solut -toiminnolla Siirry Special -vinkkiin. Meillä on sama tilanne täällä. Jos Quicken-raportti on vain välivaihe ja haluat pystyä lajittelemaan luokan mukaan, jäsennysmuoto on kamala. Lajittelun jälkeen yhteensä ja sitten luokan mukaan Auto: Vakuutus-luokka lajitellaan väärin raportin I-osioon. Luokat, joissa pidän vain kokonaismäärän, ne lajitellaan virheellisesti raportin T-osioon.
Oli kaksi apuohjelmaa, joiden ajattelin helpottavan tätä tilannetta. Apuohjelmaa kutsutaan kutistumiseksi. Kun tämä makro kutsutaan, se kutistaa alaluokan yhdeksi riviksi, jolla on oikea luokan nimi. Yllä olevassa esimerkissä makron suorittaminen solun osoittimen ollessa missä tahansa rivillä 34-38 korvaa luokan A38 luokan "Tietokone" ja poistaa rivit 34-37.
Toinen apuohjelma on luokille, joissa haluaisin nähdä alaluokan yksityiskohdat, mutta en tarvitse otsikkoa, katkoviivoitettua väliriviä tai luokan kokonaismäärää. Tätä apuohjelmaa kutsutaan nimellä Täyttö. Se löytää oikean luokan nimen ja etuliitteen jokaiselle alaluokalle kateografialla. Yllä olevassa esimerkissä makron suorittaminen solun osoittimen ollessa missä tahansa rivillä 24-30 johtaa solujen A25: A28 muuttamiseen muotoon, kuten "Auto: Insurance". Rivit 24, 29 ja 30 poistetaan.
Oikealla on parannettu versioni mietinnöstä. Määrittämällä Kutista ja Täytä pikanäppäimille pystyin tekemään nämä muutokset vain muutamalla näppäimen painalluksella. Raportin lajitteleminen on nyt helppoa tietäen, että raportti voi palata alkuperäiseen järjestykseen lajittelemalla luokan.
Jos makrot ovat sinulle uusia, tutustu Excel VBA Editorin esittelyyn.
Kun kopioit makron, voit määrittää pikanäppäimen seuraavasti:
- Valitse Työkalut-valikosta Makrot ja sitten Makro
- Korosta Täytä-makro. Napsauta Asetukset. Kirjoita Pikakuvake-kenttään mikä tahansa kirjain. Käytän f täytettä varten. Napsauta OK
- Korosta kutistumakro. Napsauta Asetukset. Valitse pikakuvakkeen kirjain, mutta pysy kaukana c: stä, koska Ctrl + c on yleinen pikakuvake
- kohdasta Muokkaa> Kopioi. Napsauta OK
- Sulje makro-valintaikkuna valitsemalla Peruuta.
Anhtuan Do on luonut seuraavat makrot osana kesän harjoittelijana tehtävää suunnitella lisäosa päivässä.
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
