Onlangs heb ik verschillende Excel Power Seminars gedaan. Als je 150 accountants in een kamer krijgt voor een lachwekkende ochtend vol Excel-tips en -trucs, leer ik altijd iets nieuws. Iemand in het publiek kan een coole truc delen met de rest van de kamer.
In de aflevering van vandaag heb ik een verzameling nieuwe trucs. Dit zijn eigenlijk trucs die beter of anders zijn dan de equivalente methode die in het boek wordt besproken. Ze zullen zeker in de volgende herziening van het boek voorkomen.
Ik zou trouwens heel graag naar jouw stad komen om een Power Excel Seminar te doen. Als u tot een professionele groep behoort, zoals de plaatselijke afdeling van het Institute of Managerial Accountants, het Institute of Internal Auditors, de AICPA, de KMO, enz., Waarom zou u dan niet voorstellen dat ze mij boeken voor een van hun komende CPE-dagen? Stuur de programmavoorzitter van je hoofdstuk naar deze pagina voor details.
Zoek het verschil tussen twee datums
Ik praat vaak over de methoden voor het gebruik van =YEAR(), =MONTH(), =DAY()functies, maar er is een koele oude functie verbergen in Excel.
De DATEDIF-functie is overgebleven van Lotus. Hoewel Excel-help niet over deze functie praat, is het een geweldige manier om het verschil tussen twee datums te vinden.
De syntaxis is =DATEDIF(EarlierDate,LaterDate,Code)
Dit zijn de geldige waarden die u voor code kunt gebruiken.
- Y - geeft het aantal volledige jaren tussen de twee datums weer.
- YM - vertelt u het aantal volledige maanden, exclusief de jaren, tussen de twee datums.
- MD - vertelt u het aantal volledige dagen, exclusief volledige maanden, tussen de twee datums.
- M - vertelt u het aantal volledige maanden. Ik leef bijvoorbeeld al 495 maanden
- D - zal u het aantal dagen vertellen. Ik leef bijvoorbeeld al 15.115 dagen. Dit is een triviaal gebruik, omdat je gewoon de ene datum van de andere kunt aftrekken en als een getal kunt opmaken om deze code te dupliceren.
De nuttige codes zijn de eerste drie codes. Op de show heb ik dit werkblad gedemonstreerd. Identieke formules in de kolommen D, E en F berekenen de DATEDIF in jaren, maanden en dagen.
De formule in kolom G verbindt dit samen om tekst te maken met de tijdsduur in jaren, maanden en dagen.
Je zou dit kunnen combineren tot een enkele formule. Als cel A2 de toetredingsdatum bevat, gebruikt u de volgende formule in B2:
=DATEDIF($A2,TODAY(),"Y")&" years, "&DATEDIF($A2,TODAY(),"YM")&" months & "&DATEDIF($A2,TODAY(),"MD")&" days"
Som van zichtbare cellen
Voeg een SOM-functie toe onder een database en gebruik vervolgens AutoFilter om de database te filteren. Excel zal irritant de verborgen rijen in de som opnemen!
Volg in plaats daarvan deze stappen:
- Gebruik Gegevens - Filter - AutoFilter om de vervolgkeuzelijsten AutoFilter toe te voegen.
- Kies een filter voor één veld
- Ga naar de lege cel onder een van de numerieke kolommen in de database.
- Klik op de Griekse letter E (Sigma) in de standaardwerkbalk. In plaats van in te voeren
=SUM(), zal Excel=SUBTOTAL()de codes invoeren en gebruiken om te voorkomen dat verborgen rijen worden opgenomen.
Sneltoets om de laatste opdracht te herhalen
De F4-toets herhaalt de laatste opdracht die u hebt uitgevoerd.
Selecteer bijvoorbeeld een cel en klik op het pictogram B om de cel vetgedrukt te maken.
Selecteer nu een andere cel en druk op F4. Excel maakt die cel vetgedrukt.
F4 onthoudt het laatste commando. U kunt dus een cel cursief maken en vervolgens F4 gebruiken om veel cellen cursief te maken.
Selecteer vooraf het celbereik dat u wilt invoeren
In het boek laat ik je zien hoe je Extra - Opties - Bewerken - Selectie verplaatsen na richting invoeren - Rechts gebruikt om Excel naar rechts te dwingen wanneer je op de enter-toets drukt. Dit is handig als u gegevens over een rij moet invoeren.
Dit is vooral handig als u cijfers invoert met het numerieke toetsenbord. Met de truc kun je 123 Enter typen en in de volgende cel terechtkomen. Door uw handen op het numerieke toetsenbord te houden, kunt u de cijfers sneller invoeren.
Iemand stelde een verbetering van deze techniek voor. Selecteer vooraf het bereik waarin u de gegevens gaat invoeren. Het voordeel is dat wanneer u bij de laatste kolom komt en op Enter drukt, Excel naar het begin van de volgende rij springt.
Als u in de onderstaande afbeelding op Enter drukt, gaat u naar cel B6.
Ctrl + sleep de vulgreep
Ik heb de Fill Handle-truc vaak in de show laten zien. Voer maandag in A1 in. Als u cel A1 selecteert, staat er een vierkante punt in de rechter benedenhoek van de cel. Deze stip is de vulhendel. Klik op de vulgreep en sleep naar beneden of naar rechts. Excel vult dinsdag, woensdag, donderdag, vrijdag, zaterdag, zondag in. Als u meer dan 7 cellen sleept, begint Excel op maandag opnieuw.
Excel is echt goed. Het kan al deze series automatisch uitbreiden:
- Maandag - dinsdag, woensdag, donderdag, vrijdag, etc.
- Jan - feb, mrt, apr, etc.
- Januari - februari, maart, etc.
- Q1 - Q2, Q3, Q4 enz.
- Qtr 1 - Qtr 2, Qtr 3, Qtr 4, Qtr 1, enz.
- 1e periode - 2e periode, 3e periode, 4e periode etc.
- 23 oktober 2006 - 24 oktober 2006, 25 oktober 2006, enz.
Aangezien Excel AL deze geweldige series kan doen, wat zou u verwachten als u 1 invoert en de vulgreep sleept?
Je zou verwachten dat je 1, 2, 3, …
Maar je krijgt echt 1, 1, 1, 1, 1, …
Het boek spreekt over een ingewikkelde methode. Voer 1 in bij A1. Voer 2 in A2 in. Selecteer A1: A2. Sleep de vulgreep. Er is een betere manier.
Voer gewoon 1 in bij A1. Ctrl + sleep de vulgreep. Excel vult 1, 2, 3 in. Het ingedrukt houden van de Ctrl lijkt het normale gedrag van de vulgreep te overschrijven.
Iemand in een seminar zei dat ze een datum wilden invoeren, de datum wilden verslepen en Excel de datum hetzelfde wilden laten houden. Als u Ctrl ingedrukt houdt terwijl u de vulgreep sleept, zal Excel het normale gedrag overschrijven (de datum verhogen) en u dezelfde datum in alle cellen geven.
