Dette er det 19. ugentlige Excel-tip på.com. Mange af Excel-tipene involverer en slags makrotrick. I denne uge tilbyder jeg for de Excel-brugere, der aldrig har skrevet en makro, en primer til, hvordan man optager og derefter tilpasser en nyttig Excel-makro.
Lad os sige, at du har 400 rækker med adressedata som vist i øverste figur til venstre. Navnefeltet er i kolonne A, gadenavnet i kolonne B og byen i kolonne C.
Dit mål er at konvertere dataene til en enkelt kolonne som vist i anden figur.
Dette enkle problem vil blive brugt til at illustrere, hvordan man optager, ændrer og derefter kører en simpel makro.
For brugere af Excel 95: Efter optagelse af makroen placerer Excel din makro på et ark kaldet Module1 i din projektmappe. Du kan bare klikke på arket for at få adgang til makroen.
Selvom der er 400 poster i dette regneark, vil jeg optage en lille smule af makroen, der kun tager sig af den første adresse. Makroen antager, at cellepegeren er på fornavnet. Det vil indsætte tre tomme rækker. Det kopierer cellen til højre for den oprindelige celle til cellen under den oprindelige celle. Det kopierer bycellen til cellen 2 rækker under den oprindelige celle. Det skal derefter flytte cellemarkøren ned, så den er på det næste navn.
Nøglen er at tænke over denne proces, før du registrerer den. Du ønsker ikke at lave mange fejl, når du optager makroen.
Så sæt din cellepeger i celle A1. Gå til menuen, og vælg Værktøjer> Makro> Optag ny makro. Dialogen Record Macro foreslår et navn på Macro1. Dette er fint, så tryk OK.
Excel makrooptageren har en meget dum standardindstilling, som du absolut skal ændre for at få denne makro til at fungere. I Excel 95 skal du gå til Værktøjer> Makro> Brug relative referencer I Excel 97-2003 skal du klikke på det andet ikon på værktøjslinjen Stop optagelse. Ikonet ligner et lille regneark. En rød celle i C3 peger på en anden rød celle i A3. Ikonet kaldes relativ reference. Når dette ikon er 'tændt', er der en eller anden farve omkring ikonet. Ikonet husker den sidste indstilling fra den aktuelle Excel-session, så du bliver muligvis nødt til at klikke på det et par gange for at finde ud af, hvilken metode der er aktiveret eller ej. I Excel 2007 skal du bruge Vis - Makroer - Brug relative referencer.
OK, vi er klar til at gå. Følg disse trin:
- Tryk på pil ned én gang for at flytte til celle B1.
- Hold shift-tasten nede, og tryk på pil ned to gange for at vælge række 2, 3 og 4
- Fra menuen skal du vælge Indsæt og derefter vælge Rækker for at indsætte tre tomme rækker.
- Tryk på pil op og derefter højre pil for at flytte til celle B2.
- Tryk på Ctrl X for at klippe celle B2.
- Tryk på pil ned, venstre pil og derefter Ctrl V for at indsætte i celle A2.
- Hit pil op, højre pil, højre pil, Ctrl X, venstre pil, venstre pil, pil ned, pil ned, Ctrl V for at flytte C1 til A3.
- Tryk på pil ned to gange, så cellemarkøren nu er på det næste navn i række A5.
- Klik på ikonet "Stop optagelse" på værktøjslinjen for at stoppe optagelsen af makroen.
Nå, du har optaget din første makro. Lad os se. Gå til Værktøjer> Makro> Makroer. Fra listen skal du markere Makro1 og trykke på knappen Rediger. Du skal se noget, der ser sådan ud.
Sub Macro1() ' ' Macro1 Macro ' Macro recorded 4/18/99 by Reader ' ' ActiveCell.Offset(1, 0).Range("A1:A3").Select Selection.EntireRow.Insert ActiveCell.Offset(-1, 1).Range("A1").Select Selection.Cut ActiveCell.Offset(1, -1).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(-1, 2).Range("A1").Select Selection.Cut ActiveCell.Offset(2, -2).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(2, 0).Range("A1").Select End SubHej, hvis du ikke er programmør, ser det sandsynligvis ret skræmmende ud. Lad det ikke være. Hvis der er noget, du ikke forstår, er der fremragende hjælp. Klik på din markør et eller andet sted i nøgleordet Offset, og tryk på F1. Forudsat at du installerede VBA-hjælpefilen, vil du se hjælpemnet til Offset-nøgleordet. Hjælpen fortæller dig syntaksen for udsagnet. Det siger, at det er forskudt (RowOffset, ColumnOffset). Stadig ikke særlig klar? Se efter det grønne understregede ord "eksempel" nær toppen af hjælpen. Excels VBA-eksempler giver dig mulighed for at lære, hvad der foregår. I eksemplet med forskydning står det at aktivere cellen to rækker nedenfor og tre rækker til højre for den aktuelle celle, du vil bruge:
ActiveCell.Offset(3, 2).ActivateOK, så det er en anelse. Offset-funktionen er en måde at bevæge sig rundt i Excel-regnearket. I betragtning af denne smule information kan du slags se, hvad makroen laver. Den første forskydning (1, 0) er, hvor vi flyttede cellemarkøren ned til A2. Den næste forskydning er, hvor vi flyttede op en række (-1 rækker) og over 1 kolonne. Du forstår muligvis ikke andet i makroen, men det er stadig nyttigt.
Gå tilbage til Excel-regnearket. Sæt din cellepeger i celle A5. Vælg Værktøjer> Makro> Makroer> Makro1> Kør. Makroen kører, og din anden adresse formateres.
Du siger måske at vælge denne hele lange store række af kommandoer er sværere end bare at formatere manuelt. OK, gør derefter Værktøjer> Makro> Makroer> Indstillinger. I genvejsfeltet skal du sige, at Ctrl + w er genvejstasten til denne makro. Klik på OK, og afvis derefter makrodialogen med Annuller. Nu, når du trykker på Ctrl w, kører makroen. Du kan formatere en adresse i et enkelt tastetryk.
Er du klar til den store tid? Hvor mange adresser har du tilbage? Jeg ramte Ctrl wa et par gange, så jeg har 395 tilbage. Gå tilbage til din makro. Vi vil sætte hele makrokoden i en løkke. Indsæt en ny linje, der siger "Gør indtil aktivecell.value =" "" før den første linje med makrokode. Indsæt en linje, der siger "Loop" før End Sub-linjen. Do-sløjfen udfører alt mellem Do og Loop-linjen, indtil den løber ind i en tom linje. Makroen ser nu sådan ud:
Sub Macro1() ' ' Macro1 Macro ' Macro recorded 4/18/99 by Reader ' ' Do Until ActiveCell.Value = "" ActiveCell.Offset(1, 0).Range("A1:A3").Select Selection.EntireRow.Insert ActiveCell.Offset(-1, 1).Range("A1").Select Selection.Cut ActiveCell.Offset(1, -1).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(-1, 2).Range("A1").Select Selection.Cut ActiveCell.Offset(2, -2).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(2, 0).Range("A1").Select Loop End SubGå tilbage til dit Excel-ark. Sæt cellemarkøren på det næste navn. Tryk på Ctrl w, og makroen formaterer alle dine poster på få sekunder.
Forfatterne af Excel-bøger siger, at du ikke kan gøre noget nyttigt ved at optage en makro. Ikke sandt! For den person, der skulle klippe og indsætte 800 gange, er denne makro meget nyttig. Det tog et par minutter at optage og tilpasse. Ja, professionelle programmører vil påpege, at koden er forfærdeligt ineffektiv. Excel lægger en hel masse ting derinde, som den ikke behøver at lægge derinde. Ja, hvis du vidste, hvad du laver, kan du udføre den samme opgave med halvdelen af linjerne, der kører på 1,2 sekunder i stedet for 3 sekunder. OG HVAD SÅ? 3 sekunder er langt hurtigere end de 30 minutter, opgaven ville have taget.
Nogle flere tip til begyndende makrooptagere:
- Den anvendte apostrof angiver en kommentar. Alt efter apostrofen ignoreres af VBA
- Dette er objektorienteret programmering. Den grundlæggende syntaks er object.action. Hvis en objektorienteret kompilator spillede fodbold, ville det sige "ball.kick" for at sparke bolden. Så "Selection.Cut" siger at lave en "edit> cut" på det aktuelle valg.
- I ovenstående eksempel er områdemodifikatorerne i forhold til den aktive celle. Hvis den aktive celle er i B2, og du siger "ActiveCell.Range (" A1: C3 "). Vælg", så vælg området 3 række med 3, der starter i celle B2. Med andre ord vælger du B2: D4. At sige "ActiveCell.Range (" A1 ")" siger at vælge 1 x 1 celleområde begyndende med den aktive celle. Dette er utroligt overflødigt. Det svarer til at sige "ActiveCell.Select".
- Gem din projektmappe, før du kører en makro for første gang. På denne måde, hvis den har en fejl og gør noget uventet, kan du lukke uden at gemme og vende tilbage til den gemte version.
Forhåbentlig giver dette enkle eksempel dig uerfarne makrooptagere modet til at optage en simpel makro næste gang du har en tilbagevendende opgave at udføre i Excel.
