Nyligen har jag varit ute och gjort flera Excel Power Seminars. När du får 150 revisorer i ett rum för en skrattfylld morgon med Excel-tips och tricks, lär jag mig alltid något nytt. Någon i publiken kan dela ett coolt trick med resten av rummet.
I dagens avsnitt har jag en samling nya knep. Detta är faktiskt knep som är bättre eller annorlunda än motsvarande metod som diskuteras i boken. De kommer definitivt att vara i nästa version av boken.
Förresten, jag skulle gärna vilja komma till din stad för att göra ett Power Excel-seminarium. Om du tillhör en yrkesgrupp som det lokala kapitlet vid Institute of Managerial Accountants, Institute of Internal Auditors, AICPA, SME, etc., varför inte föreslå att de bokar mig för en av deras kommande CPE-dagar? Skicka din kapitelprogramordförande till den här sidan för mer information.
Hitta skillnaden mellan två datum
Jag brukar tala om metoder för att använda =YEAR(), =MONTH(), =DAY()funktioner, men det är en cool gamla funktionen gömmer sig i Excel.
DATEDIF-funktionen är kvar från Lotus. Medan Excel-hjälp inte pratar om den här funktionen är det ett bra sätt att hitta skillnaden mellan två datum.
Syntaksen är =DATEDIF(EarlierDate,LaterDate,Code)
Här är de giltiga värdena som du kan använda för kod.
- Y - berättar antalet kompletta år mellan de två datumen.
- YM - berättar antalet fullständiga månader, exklusive åren, mellan de två datumen.
- MD - berättar antalet fullständiga dagar, exklusive hela månader, mellan de två datumen.
- M - berättar antalet kompletta månader. Till exempel har jag levt i 495 månader
- D - kommer att berätta antalet dagar. Till exempel har jag levt i 15 115 dagar. Detta är en trivial användning, eftersom du bara kan dra ett datum från ett annat och formatera som ett nummer för att duplicera den här koden.
De användbara koder är de tre första koder. På showen demonstrerade jag detta kalkylblad. Identiska formler i kolumnerna D, E och F beräknar DATEDIF i år, månader och dagar.
Formeln i kolumn G strängar ihop detta för att skapa text med tiden i år, månader och dagar.
Du kan kombinera detta till en enda formel. Om cell A2 innehåller anslutningsdatum, använd följande formel i B2:
=DATEDIF($A2,TODAY(),"Y")&" years, "&DATEDIF($A2,TODAY(),"YM")&" months & "&DATEDIF($A2,TODAY(),"MD")&" days"
Summan av synliga celler
Lägg till en SUM-funktion under en databas och använd sedan AutoFilter för att filtrera databasen. Excel kommer irriterande att inkludera de dolda raderna i summan!
Följ istället dessa steg:
- Använd Data - Filter - AutoFilter för att lägga till rullgardinsmenyn AutoFilter.
- Välj ett filter för ett fält
- Gå till den tomma cellen under en av de numeriska kolumnerna i databasen.
- Klicka på den grekiska bokstaven E (Sigma) i standardverktygsfältet. I stället för att ange
=SUM()kommer Excel att ange=SUBTOTAL()och använda koder för att förhindra att dolda rader inkluderas.
Genväg för att upprepa det senaste kommandot
F4-tangenten upprepar det senaste kommandot du utförde.
Välj till exempel en cell och klicka på B-ikonen för att göra cellen fet.
Välj nu en annan cell och tryck på F4. Excel kommer att göra den cellen fet.
F4 kommer ihåg det senaste kommandot. Så du kan göra en cell i kursiv stil och sedan använda F4 för att göra många celler kursiv.
Förvälja det cellintervall som ska anges
I boken visar jag dig hur du använder Verktyg - Alternativ - Redigera - Flytta markering efter ange riktning - Höger för att tvinga Excel att flytta till höger när du trycker på Enter-tangenten. Det här är bra när du måste ange data som går över en rad.
Det är särskilt användbart om du anger siffror på det numeriska tangentbordet. Tricket låter dig skriva 123 Enter och hamna i nästa cell. Genom att hålla händerna på det numeriska tangentbordet kan du ange siffrorna snabbare.
Någon föreslog en förbättring av denna teknik. Förvälja det intervall där du ska mata in data. Fördelen är att när du kommer till den sista kolumnen och trycker på Enter kommer Excel att hoppa till början av nästa rad.
På bilden nedan flyttar du dig till cell B6 genom att trycka på Enter.
Ctrl + Dra i fyllningshandtaget
Jag har visat Fill Handle-tricket många gånger på showen. Ange måndag i A1. Om du väljer cell A1 finns det en kvadratisk punkt i det nedre högra hörnet av cellen. Denna punkt är Fill Handle. Klicka på fyllningshandtaget och dra antingen nedåt eller åt höger. Excel fyller i tisdag, onsdag, torsdag, fredag, lördag, söndag. Om du drar mer än sju celler börjar Excel igen på måndagen.
Excel är riktigt bra. Det kan förlänga alla dessa serier automatiskt:
- Måndag - tisdag, onsdag, torsdag, fredag etc.
- Jan - Feb, Mar, Apr, etc.
- Januari - februari, mars etc.
- Q1 - Q2, Q3, Q4 etc.
- Qtr 1 - Qtr 2, Qtr 3, Qtr 4, Qtr 1, etc.
- 1: a perioden - 2: a perioden, 3: e perioden, 4: e perioden osv.
- 23 okt 2006 - 24 oktober 2006, 25 oktober 2006, etc.
Eftersom Excel kan göra ALLA dessa fantastiska serier, vad kan du förvänta dig om du anger 1 och drar fyllningshandtaget?
Du kan förvänta dig att du skulle få 1, 2, 3, …
Men du får verkligen 1, 1, 1, 1, 1, …
Boken talar om en invecklad metod. Ange 1 i A1. Ange 2 i A2. Välj A1: A2. Dra påfyllningshandtaget. Det finns ett bättre sätt.
Ange bara 1 i A1. Ctrl + Dra i fyllningshandtaget. Excel fyller i 1, 2, 3. Att hålla ner Ctrl verkar åsidosätta det normala beteendet hos fyllningshandtaget.
Någon i ett seminarium sa att de skulle vilja ange ett datum, dra datumet och få Excel att hålla samma datum. Om du håller Ctrl intryckt medan du drar fyllningshandtaget kommer Excel att åsidosätta det normala beteendet (öka datumet) och ge dig samma datum i alla celler.
