Tip 1 uit het XL-boek - snelle manieren om een formule in een kolom te kopiëren.
Je hebt duizenden rijen met gegevens. U hebt een nieuwe formule toegevoegd in de bovenste rij van uw dataset, zoiets als deze =PROPER(A2&" "&B2), zoals hieronder weergegeven. U moet de formule nu naar alle rijen van uw gegevensset kopiëren.
Veel mensen pakken de vulhendel en beginnen naar beneden te slepen. Maar terwijl u naar beneden sleept, begint Excel steeds sneller te gaan. Vanaf Excel 2010 is er een pauze van 200 microseconde bij de laatste rij met gegevens. 200 microseconden is lang genoeg om de pauze op te merken, maar niet lang genoeg om te reageren en de muisknop los te laten. Voordat je het weet, heb je de vulhendel veel te ver gesleept.
De oplossing is om te dubbelklikken op de vulhendel! Ga naar exact dezelfde plek waar u de vulhendel begint te slepen. De muisaanwijzer verandert in een zwart plusteken. Dubbelklik.
Excel kijkt naar de omringende gegevens, zoekt de laatste rij met gegevens van vandaag en kopieert de formule naar de laatste rij van de gegevensset.
In het verleden zorgden lege cellen in de kolom aan de linkerkant ervoor dat de "dubbelklik op de vulhendel" -truc stopte met werken net voor de lege cel. Maar zoals je hieronder kunt zien, zullen namen als Madonna, Cher of Pele geen problemen veroorzaken. Mits er minimaal een diagonaal pad is (bijvoorbeeld via B76-A77-B78), zal Excel de ware onderkant van de dataset vinden.
In mijn live Power Excel-seminars lokt deze truc altijd een zucht uit van de helft van de mensen in de kamer. Het is mijn nummer één tijdbesparende truc.
Alternatieven voor dubbelklikken op de vulhendel
Deze truc is een geweldige truc als je tot nu toe alleen de vulhendel naar de onderkant van de gegevensset sleept. Maar er zijn nog snellere manieren om dit probleem op te lossen:
- Gebruik tabellen. Als u één cel in A1: B112 zou selecteren en op Ctrl + T zou drukken, zal Excel het bereik opmaken als een tabel. Zodra u een tabel heeft, voert u eenvoudig de formule in C2 in. Als u op Enter drukt, wordt het naar beneden gekopieerd.
- Gebruik een complexe maar effectieve sneltoets. Deze snelkoppeling vereist dat de aangrenzende kolom geen lege cellen bevat. Hoewel het ingewikkeld lijkt om uit te leggen, kunnen de mensen die me over deze snelkoppeling vertellen het hele ding in een oogwenk doen.
Hier zijn de stappen:
- Druk vanuit uw nieuw ingevoerde formule in C2 op de pijl naar links om naar cel B2 te gaan.
- Druk op Ctrl + pijl-omlaag om naar de laatste rij met gegevens te gaan. In dit geval B112.
-
Druk op de pijl naar rechts om terug te keren naar de onderkant van de grotendeels lege kolom C.
Sneltoetsen gebruiken - Druk in cel C112 op Ctrl + Shift + Pijl-omhoog. Hiermee worden alle lege cellen naast uw gegevens geselecteerd, plus de formule in C2.
-
Druk op Ctrl + D om de formule in C2 tot alle lege plekken in de selectie te vullen. (Merk op dat Ctrl + R rechts vult, wat in andere situaties handig kan zijn.)
Ctrl + D om naar beneden te vullen
Als alternatief kunt u dezelfde resultaten krijgen door op Ctrl + C te drukken vóór stap 1 en stap 5 te vervangen door Ctrl + V.
Dank aan de volgende mensen die deze tip hebben voorgesteld: D. Carmichael, Shelley Fishel, Dawn Gilbert, @Knutsford_admi, Francis Logan, Michael Ortenberg, Jon Paterson, Mike Sullivan en Greg Lambert Lane suggereerden Ctrl + D. Bill Hazlett, auteur van Excel voor de Math Classroom, wees op Ctrl + R. Het was de cartoon van Em DeMarco die op Twitter werd gepost en die de inspiratie vormde voor het inhuren van illustratoren voor dit boek.
Bekijk video
- Tip 1 uit het XL-boek - snelle manieren om een formule in een kolom te kopiëren. Ook in deze video:
- Ctrl + pijl-omlaag om naar het einde van een gegevensblok te gaan
- Ctrl + Shift + Pijl-omhoog om een gegevensblok bovenaan te selecteren
- Ctrl + D om de formule vanaf de eerste rij van een selectie omlaag te vullen
- Dubbelklik op de vulgreep
- JUISTE functie
- & teken voor aaneenschakeling
- ROMAN functie
- ARABIC-functie
Videotranscriptie
De podcast wordt gesponsord door "MrExcel XL", een boek met 40+ Excel-tips, plus Excel-cartoons, cocktails, tweets en grappen.
Leer Excel van podcast, aflevering 1974 - Snelle formule-kopie!
Oké, nou ja, dit hele boek bevat veel illustraties, en degene die me verslaafd maakte, was deze illustratie van Emily DeMarco. Emily was in een soort Excel-seminar, waar ze lieten zien hoe ze gegevens konden combineren met behulp van het aaneenschakelteken, en ze krabbelde hier doorheen, en iemand in het publiek zei: "Maar is dit niet hetzelfde als = CONCATENATE?" Aan degene die les geeft, ja. Maar wie kan conCATenate spellen? Ik zag dat, ik vond dat hilarisch.
Dus de vraag is vandaag: hoe nemen we deze formule, ik ga het niet hebben over deze formule, maar hoe nemen we deze formule, of welke formule dan ook, en kopiëren we deze naar honderden of duizenden rijen met gegevens? En voordat ik op weg ga, moeten we eerst de Excel Olympics-methode erkennen! Er zijn mensen die deze vijf stappen kunnen uitvoeren als een enkele toetsaanslag, het duurt minder dan een seconde, en als jij dat bent, gefeliciteerd, een pluim voor je, als er ooit een Olympische Spelen in Excel zijn, nomineer ik je voor het team. Dus als je een sneltoets bent, gaat de linkerpijl naar B2, CTRL + pijl-omlaag, rechterpijl, CTRL + Shift, pijl-omhoog selecteert naar boven en in mijn favoriete, degene die ik nooit echt heb gedaan gebruik eerder was CTRL + D. CTRL + D neemt de formule in de bovenste rij van de selectie en kopieert deze naar beneden.Dus als je van sneltoetsen houdt, is dit in elk geval de juiste keuze.
Maar de manier waarop de meeste mensen dit probleem oplossen, is dat ze die eerste formule daarboven in rij 2 maken, en ze pakken de vulgreep en ze beginnen te slepen, rechts, en het begint steeds sneller en sneller en sneller en sneller te gaan. Voordat u het weet, staan honderden rijen onder de gegevens. En als u opmerkt, vertragen ze daar eigenlijk. Ze zijn niet lang genoeg om daadwerkelijk te reageren, net lang genoeg om de vertraging te zien. Dus als je merkt dat je te ver naar beneden gaat en weer omhoog gaat, noem ik dat de 'fill handle dance', en je zult deze volgende truc geweldig vinden.
Dus hier is de snellere manier, begin precies op dezelfde manier, je hebt daar het witte kruis, daar is de muiscursor, als je dichtbij komt, verandert het in een zwarte +, precies daar als je die zwarte + hebt, dubbele -Klik! Excel kijkt naar links, berekent hoeveel rijen met gegevens we vandaag hebben, en kopieert de formule naar beneden! Of het nu 112 rijen zijn in dit geval, of 1,1 miljoen rijen, hoe dan ook, het komt tot op de bodem.
Oké, voor degenen onder u die al wisten dat u dubbelklikt op de vulgreep, u weet het al lang: wanneer werkt dubbelklikken op de vulgreep niet? Dat klopt, het is wanneer iemand geen achternaam heeft! De kolom aan de linkerkant heeft een spatie. In plaats van Cher, laten we Prince erbij doen. Dubbelklik dus normaal op de vulgreep, deze kijkt naar links en gaat alleen naar rij 8 kopiëren. Juist, vanwege deze blanco in rij 9. En natuurlijk staat het nooit op het eerste scherm, het zijn altijd honderden rijen naar beneden in de gegevens. Nou, dat was vroeger het probleem, maar in Excel 2010 kijken ze nu stilletjes en zonder enige kennisgeving in alle kolommen aan de linkerkant om erachter te komen hoever ze de formule naar beneden moeten kopiëren. Het laat zich dus niet misleiden door de lege cel in de kolom aan de linkerkant!Dit is verreweg mijn favoriete tip in heel Excel: als ik de Power Excel-seminars doe, die live-seminars, en ik krijg dit ongeveer 45 minuten per dag, dan krijg ik bijna altijd een zucht van een paar mensen in de kamer, als ik dubbelklik op de vulgreep.
Nou, hey gewoon een leuk feit hier, het "MrExcel XL" -boek, je weet dat ik vroeger opgroeide, in de jaren 70 toen er allerlei soorten films waren, die uitkwamen met Romeinse cijfers, dus the Godfather deel II was de eerste in. In de moderne geschiedenis, Back to the Future III, Star Wars IV, Rocky V, Star Trek VI, zelfs Final Fantasy VII voor 7. Weet je, en het is grappig, Excel heeft eigenlijk een ROMAN-functie! Oké, dus je kunt om de ROMAN van ROW vragen, en dat geeft je de Romeinse cijfers. Wat echt raar is, want in Excel 2013, als je een Romeins cijfer XL invoert, en je vraagt naar het ARABIC, zal het dat ROMAN cijfer terug naar een normaal cijfer converteren. Dus toen ik klaar was met mijn 39e boek, en ik wist dat het 40e boek eraan kwam, net als een puntje van de hoed terug naar al deze films die Romeinse cijfers gebruiken, wist ik dat,het zou gewoon "MrExcel XL" worden, want het is mijn 40e boek, hoewel ik de slogan daar heb gezet, = ROMAN (40), zodat mensen de grap zouden begrijpen!
Oké, even een korte samenvatting van alle dingen waar we het in deze aflevering over hadden: en DIT is slechts tip # 1 uit het boek. Nou, hey bedankt, aan iedereen die schreef om deze tip voor te stellen. Dit zijn allemaal mensen die me hebben getweet. Een die ik hier niet eens heb besproken, Bill Hazlett, wijs erop dat CTRL + R vergelijkbaar is met CTRL + D, als je een cel selecteert en naar rechts sleept, of naar rechts, CTRL + R zal naar rechts kopiëren. Ga je gang en klik nu op die infoknop in de rechterbovenhoek om te lezen hoe je dit boek kunt kopen.
Bedankt voor het langskomen, we zien je de volgende keer voor een nieuwe netcast van!
