Tips nr 1 från XL-boken - snabba sätt att kopiera en formel ner i en kolumn.
Du har tusentals rader med data. Du har lagt till en ny formel i den översta raden i din datamängd, något liknande detta =PROPER(A2&" "&B2), som visas nedan. Du måste nu kopiera formeln till alla raderna i din datamängd.
Många kommer att ta tag i Fill Handle och börja dra. Men när du drar ner börjar Excel gå snabbare och snabbare. Från och med Excel 2010 finns det en paus på 200 mikrosekunder vid den sista raden med data. 200 mikrosekunder är tillräckligt lång för att du ska märka pausen men inte tillräckligt länge för att du ska kunna reagera och släppa musknappen. Innan du vet ordet av det har du dragit Fill Handle för långt.
Lösningen är att dubbelklicka på Fill Handle! Gå till exakt samma plats där du börjar dra Fill Handle. Muspekaren ändras till ett svart plustecken. Dubbelklicka.
Excel tittar på omgivande data, hittar den sista raden med data idag och kopierar formeln ner till den sista raden i datamängden.
Tidigare skulle tomma celler i kolumnen till vänster orsaka att "dubbelklicka på Fill Handle"-tricket slutar fungera strax före den tomma cellen. Men som du kan se nedan kommer namn som Madonna, Cher eller Pele inte att orsaka problem. Förutsatt att det finns åtminstone en diagonal väg (till exempel via B76-A77-B78), kommer Excel att hitta den verkliga botten av datamängden.
I mina Power Excel-seminarier framkallar det här tricket alltid en gasning från hälften av människorna i rummet. Det är mitt nummer ett tidsbesparande trick.
Alternativ till att dubbelklicka på fyllningshandtaget
Detta trick är ett fantastiskt trick om allt du har gjort till den här punkten är att dra Fill Handle till botten av datamängden. Men det finns ännu snabbare sätt att lösa detta problem:
- Använd tabeller. Om du väljer en cell i A1: B112 och trycker på Ctrl + T kommer Excel att formatera intervallet som en tabell. När du har en tabell anger du helt enkelt formeln i C2. När du trycker på Enter kommer den att kopieras till botten.
- Använd en komplex men effektiv kortkommando. Den här genvägen kräver att den intilliggande kolumnen inte har några tomma celler. Även om det verkar komplicerat att förklara, kan de som berättar om den här genvägen göra det hela på ett ögonblick.
Här är stegen:
- Från din nyinmatade formel i C2, tryck på vänsterpil för att flytta till cell B2.
- Tryck på Ctrl + nedåtpilen för att flytta till den sista raden med data. I det här fallet, B112.
-
Tryck på högerpiltangenten för att återgå till botten av den mest tomma kolumnen C.
Använda kortkommandon - Från cell C112 trycker du på Ctrl + Skift + Uppåtpil. Detta markerar alla tomma celler bredvid dina data, plus formeln i C2.
-
Tryck på Ctrl + D för att fylla formeln i C2 till alla tomma ämnen i markeringen. (Observera att Ctrl + R fyller rätt, vilket kan vara användbart i andra situationer.)
Ctrl + D för att fylla ner
Som ett alternativ kan du få samma resultat genom att trycka på Ctrl + C före steg 1 och ersätta steg 5 med Ctrl + V.
Tack till följande personer som föreslog detta tips: D. Carmichael, Shelley Fishel, Dawn Gilbert, @Knutsford_admi, Francis Logan, Michael Ortenberg, Jon Paterson, Mike Sullivan och Greg Lambert Lane föreslog Ctrl + D. Bill Hazlett, författare till Excel för Math Classroom, påpekade Ctrl + R. Det var Em DeMarcos tecknad film som publicerades på Twitter som var inspiration för att anställa illustratörer för den här boken.
Kolla på video
- Tips nr 1 från XL-boken - snabba sätt att kopiera en formel ner i en kolumn. Även i den här videon:
- Ctrl + Pil ned för att flytta till slutet av ett datablock
- Ctrl + Skift + Uppåtpil för att välja överst i ett datablock
- Ctrl + D för att fylla formeln från den första raden i ett val nedåt
- Dubbelklicka på fyllningshandtaget
- PROPER-funktion
- & karaktär för sammankoppling
- ROMAN-funktion
- ARABISK funktion
Videoutskrift
Podcasten sponsras av "MrExcel XL", en bok med 40+ Excel-tips, plus Excel-tecknade serier, cocktails-tweets och skämt.
Lär dig Excel från podcast, avsnitt 1974 - Fast Formula Copy!
Okej, hej, hela den här boken har en hel del illustrationer i sig, och den som fick mig hakad var denna illustration av Emily DeMarco. Emily var i någon form av ett Excel-seminarium, där de visade hur man kombinerar data med hjälp av sammankopplingstecknet, och hon klottrade igenom detta, och någon i publiken sa: "Men är det inte samma sak som = CONCATENATE?" Till den som undervisar klasser, ja. Men vem kan stava conCATenate? Jag såg det, jag tyckte det var roligt.
Så frågan idag är hur vi tar den här formeln, jag ska inte prata om den här formeln, men hur tar vi den här formeln eller någon formel och kopierar den till hundratals eller tusentals rader med data? Och innan jag går till min väg bör vi först erkänna Excel Olympics Method! Det finns människor som kan göra dessa fem steg som som en enda tangenttryckning, det tar mindre än en sekund, och om det är du, grattis, kudos till dig, om det någonsin finns ett Excel-OS, nominerar jag dig att vara med på laget. Så om du är en person med kortkommandon flyttar vänsterpil till B2, CTRL + nedåtpil, högerpil, CTRL + Skift, uppåtpilen väljer till toppen, och i min favorit, den som jag egentligen aldrig användning före, var CTRL + D. CTRL + D tar formeln i den översta raden i urvalet och kopierar ner den.Så om du älskar tangentbordsgenvägar är det här vägen att gå.
Men det sätt som de flesta människor löser detta problem är att de skapar den första formeln där uppe i rad 2, och de tar tag i fyllningshandtaget och de börjar dra, höger, och det börjar gå snabbare och snabbare och snabbare och snabbare. Innan du vet är det hundratals rader under botten av data. Och om du märker, saktar de faktiskt ner där. De är inte tillräckligt långa för att du faktiskt ska kunna reagera, bara tillräckligt länge för att du ska se avmattningen. Så om du befinner dig att gå ner för långt och säkerhetskopiera, så kallar jag att fyllhandtaget dansar, och du kommer att älska det här nästa tricket.
Så här är det snabbare sättet, do börjar på exakt samma sätt, du har det vita korset där, det finns muspekaren, när du kommer nära ändras den till svart +, precis där när du har den svarta +, dubbel -klick! Excel ser till vänster, räknar ut hur många datarader vi har idag och kopierar formeln till botten! Oavsett om det är 112 rader i det här fallet eller 1,1 miljoner rader, på något sätt, kommer det till botten.
Okej nu, för de av er, som redan visste att dubbelklicka på fyllningshandtaget, har ni känt det länge: När fungerar dubbelklick inte på fyllningshandtaget? Det stämmer, det är när någon inte har ett efternamn! Kolumnen till vänster har en tom. Istället för Cher, låt oss lägga in Prince. Dubbelklicka på fyllningshandtaget normalt, det ser till vänster och det kommer bara att kopieras ner till rad 8. Höger, på grund av det här tomma i rad 9. Och det är naturligtvis aldrig på första skärmen, det är alltid hundratals av rader ner i data. Tja, det brukade vara problemet, men tyst utan meddelande i Excel 2010 tittar de nu i alla kolumner till vänster för att ta reda på hur långt man ska kopiera formeln. Så det luras inte av den tomma cellen i kolumnen till vänster!Det här är överlägset mitt favorit tips i hela Excel. När jag gör Excel-seminarierna, dessa live-seminarier, och jag kommer till detta cirka 45 minuter in i dagen, får jag nästan alltid en gasp från några få personer till rummet när jag dubbelklickar på fyllningshandtaget.
Nu hej bara ett roligt faktum här, "MrExcel XL" -boken, du vet att jag växte upp på dagen, tillbaka på 70-talet när det fanns alla möjliga filmer, som kom ut med romerska siffror, så Gudfadern del II var den första i. I modern historia, Back to the Future III, Star Wars IV, Rocky V, Star Trek VI, till och med Final Fantasy VII för 7. Du vet, och det är roligt, Excel har faktiskt en ROMAN-funktion! Okej, så du kan be om ROMAN of ROW, och det ger dig de romerska siffrorna. Vad som är riktigt konstigt, för i Excel 2013, om du lägger in en romersk siffra XL, och du ber om ARABIC, kommer den att konvertera den romerska siffran till vanlig siffra. Så när jag var klar med min 39: e bok och jag visste att den 40: e boken skulle komma, precis som ett spets på hatten tillbaka till alla dessa filmer som använder romerska siffror, visste jag attdet skulle bara bli "MrExcel XL", för det är min 40: e bok, även om jag lägger tagline där, = ROMAN (40), så folk skulle få skämtet!
Okej, bara en kort sammanfattning av alla saker vi pratade om i det här avsnittet: Och DETTA är bara tips nr 1 från boken. Tja hej tack, till alla som skrev och föreslog detta tips. Det här är alla människor som twittrade mig. En jag täckte inte ens här, Bill Hazlett, påpekade att CTRL + R är som CTRL + D, om du väljer en cell och drar åt höger eller väljer till höger, CTRL + R kopieras till höger. Fortsätt och klicka på den informationsknappen i det övre högra hörnet just nu för att läsa om hur du kan köpa den här boken.
Tack för att du stannade, vi ses nästa gång för en ny netcast från!
