Skapa ad-hoc-summor för en Excel-datauppsättning där du ska filtrera data. Också i den här videon: Använda SUBTOTAL (109,) för att summera data som har varit manuella dolda rader. Använd Ctrl + Ditto för att kopiera den exakta formeln nedåt en rad.
När du har applicerat ett filter skulle det vara fantastiskt att se summan av de synliga cellerna.
Välj den tomma cellen under var och en av dina numeriska kolumner. Klicka på AutoSum-knappen.
Istället för att infoga SUM-formler infogar Excel =SUBTOTAL(9,… )formler. Formeln nedan visar summan av endast de synliga cellerna.
Infoga några tomma rader ovanför dina data. Klipp ut formlerna nedanför data och klistra in till rad 1 med etiketten Total Visible.
Nu när du byter filter, även om data fyller mer än en helskärm med data, kommer du att se totalerna högst upp i kalkylbladet.
Tack till Sam Radakovitz i Excel-teamet för Filter by Selection - inte för att föreslå Filter by Selection, utan för att formalisera Filter by Selection!
Kolla på video
- Skapa ad-hoc-summor för en Excel-datauppsättning där du ska filtrera data. Också i den här videon: Använda SUBTOTAL (109,) för att summera data som har varit manuella dolda rader. Använd Ctrl + Ditto för att kopiera den exakta formeln nedåt en rad.
Videoutskrift
Podcasten sponsras av "MrExcel XL", en bok med 40+ Excel-tips, Excel-tecknade serier, cocktails, tweets och skämt.
Lär dig Excel från podcast, avsnitt 1976 - Total the Visible Cells!
Hej, välkommen tillbaka till netcast, jag heter Bill Jelen. Naturligtvis är alla, den allra första formeln, som de flesta skapar, AutoSum. Du är här nedanför en kolumn med siffror, du får AutoSum-knappen, och den kommer att använda summeringsfunktionen för att summera siffrorna ovanför den tills den möter antingen en formel eller text. Rätt? Eller Alt + = gör exakt samma sak. Eller, ännu bättre, välj flera celler och tryck sedan på AutoSum, så läggs alla dessa formler utan att du behöver bekräfta formlerna.
Men här är det coola tricket, okej, så låt oss sätta på AutoFilter. Så vi ska använda filtret efter urval, från gårdagens podcast. Uppgifterna filtreras. Jag ska gå till den allra första tomma raden under data och sedan trycka på AutoSum-knappen. Men något helt annat händer, förstår du. Istället för SUM-funktionen får vi = SUBTOTAL (9! Och vad det här är, detta är den totala av de synliga cellerna. Okej så, väldigt coolt att det fungerar.
Nu är det saken, jag gjorde ett live-kraft Excel-seminarium för nästan tio år sedan, i Fort Wayne, IN, för Institute of Internal Auditors. Och någon visade mig detta trick, och jag älskar verkligen tricket. Jag hade aldrig sett tricket förut, det var coolt, jag sa att det här är fantastiskt, jag ska lägga till detta på seminariet. Och nästa år var jag tillbaka i Fort Wayne, och den killens chef dyker upp, eller hur, han hade inte kommit första året, för han trodde att han visste allt om Excel. Men han kom tillbaka för att hävda detta trick och han säger: "Se, jag hör att min finansanalytiker sa till dig detta trick, men han sa inte till dig det bästa, gå upp över dina data och sätt in två rader där uppe. Och så när du har totalt synliga celler längst ner, klipp ut, CTRL + X och klistra in, CTRL + V, okej, vad det gör är,om du byter till en annan kund, ”Okej, så jag väljer några fler kunder här.” Och nu har jag fler kunder än vad som kommer att passa på skärmen! Och jag behöver inte rulla ner till botten för att se totalsummana, totalerna är alltid där uppe! "
Okej nu, det här tricket, med hjälp av AutoSum under filterdata, fungerar när du gömmer raderna med filtret. Vad händer om du gömmer raderna manuellt? Så här valde jag några rader, jag gick redan till Format, Hide & Unhide, Hide Rows! Så nu har vi dolda rader. Och jag kommer ner till botten och jag trycker på Alt + = eller AutoSum, och det ger mig inte SUBTOTAL-funktionen, det ger mig SUM-funktionen.
Just nu, vid den här tiden, ska jag göra en speciell sak, jag ska göra CTRL + '(ditto, apostrof). Det kommer att få ner exakt samma formel utan att ändra cellreferensen. Så det är E2: E8 ändras, eller ändras inte, vanligtvis om du skulle kopiera och klistra in till en ändring till E3: E9. Jag kommer att ändra detta till SUBTOTAL (9, och du kommer att se att det inte fungerar, det ger oss fortfarande alla celler. 1000 + 400 + 800 är inte 4500, det inkluderar de dolda raderna. Men här är vad du måste göra, komma in i CTRL + 'och ändra den 9, till en 109, och du får: Den sanna totala synliga baserat på manuellt dolda rader!
Okej, så går du, så ett par olika knep, AutoSum, Alt + = ger oss bara SUM-funktionen. Om du är under data och du trycker på AutoSum kommer du att få SUBTOTAL (9, som fungerar bra för filtrerade, eller så kan du bara skriva in SUBTOTAL manuellt (109, för att få det totala av det synliga när du har filtrerat manuellt.
Okej, här är en sammanfattning av begreppen i dagens video: Detta är förresten inte ens tips nr 3 i boken, detta är bonustips # 2b rätt, så mellan tip 2 och tip 3. Boken täcker alla dessa föremål. Tack till alla som föreslog detta, folket på Fort Wayne Indiana Chapter of The IIA, är där jag först hörde det. Det är en fantastisk bok, du kan köpa den i tryck eller e-bok, klicka på det "i" i det övre högra hörnet. Du skulle få mer information om boken.
Okej, jag vill tacka dig för att du kom förbi, vi ses nästa gång för en ny netcast från!
