Noen ganger, hvis du faktisk vet navnet på en funksjon, er det enkelt å finne ut hvordan du styrer denne funksjonen. Men med dagens tema har de fleste som blir stukket av denne "bug" ingen anelse om hvorfor det skjer. De vil rapportere et symptom i retning av: "Av en eller annen grunn vises to av våre ansatte stadig øverst i pivottabellene våre". Jeg vil ofte forutsi at den ansatte heter juni eller mai eller kanskje til og med fredag.
Her er historien: Hver kopi av Excel starter med fire innebygde tilpassede lister. Disse listene inneholder tolvmånedersnavn, syv ukedagsnavn, tolvmåneders forkortelser og syv forkortelser på hverdager. Egendefinerte lister brukes ofte i forbindelse med Fill Handle for raskt å legge til overskrifter over et regneark.
Men tilpassede lister har andre bruksområder. Du kan sortere etter en tilpasset liste. I sorteringsdialogen tilbyr rullegardinmenyen Sorter etter stigende, synkende og egendefinert liste. Også - du kan bruke en egendefinert liste for å kontrollere rekkefølgen av elementer i en pivottabell.
Fordi egendefinerte lister er nyttige, vil mange opprette nye tilpassede lister. En liste kan være så kort som to eller så lang som 254 varer. Du kan legge til egendefinerte lister for produktlinjene eller kostnadsstedene eller anleggsstedene.
For eksempel har jeg en tilpasset liste med tre regionnavn: Øst, Sentral, Vest. Listen har elementene i den rekkefølgen. Når jeg oppretter en pivottabell, vil pivottabellen automatisk vise regionene i øst, sentral, vest-sekvens, selv om den normale sekvensen ville være sentral, øst, vest.
Hva skjer når selskapet ditt introduserte en ny sørregion, og du ikke oppdaterer den tilpassede listen? Excel bruker øst, sentral, vest fra den egendefinerte listen og viser deretter elementene som ikke er i den egendefinerte listen:
Men la oss late som du ikke har opprettet noen tilpassede lister. Din versjon av Excel har bare de fire innebygde lister. Det er trettiåtte ord som tilfeldigvis er i disse tilpassede listene. Hvis du har et datasett med fornavn, og noen av disse fornavnene tilfeldigvis er i listen over 38 ord, vil disse navnene sorteres til toppen av pivottabellen. Jeg kjenner ikke mange som heter ti eller tor. Men jeg kjenner mange mennesker som heter Jan. Problemet vil oppstå hvis dataene dine inneholder noen av disse navnene: Apr, April, Aug, August, Dec, December, Feb, February, Fri, Friday, Jan, January, Jul, July, Juni, juni, mars, mars, mai, mai, mandag, nov, november, oktober, oktober, lør, lørdag, sep, september, søn, søndag, torsdag, torsdag, tirsdag, onsdag og onsdag.
Merk
Under innspillingen av videoen nedenfor oppdaget jeg ved et uhell at sorteringen av pivottabellen kunne komme fra forskjellige lister. Jan fra månedens forkortelser og april fra månedens navn vil begge sorteres før et annet navn som Andy. Det er imidlertid ingen klar regel for hvilke tilpassede lister som skal sorteres først. Jeg sjekket til og med med Sam Rad i Excel-teamet, og det er ingenting definert for å håndtere blanding av elementer fra flere lister. Sam foreslår at hvis du vil kontrollere sorteringen av en pivottabell, beholder du alle elementene i samme tilpassede liste.
Så - hvordan får du Jan til å slutte å sortere til toppen av et pivottabell? Tross alt kan du ikke slette de innebygde egendefinerte lister.
- Høyreklikk på pivottabellen og velg Alternativer.
- Gå til kategorien Totaler og filtre i Pivottabellalternativer.
- Fjern merket for Bruk egendefinerte lister når du sorterer.
Merk: Dette løser ikke den allerede eksisterende pivottabellen. Du må gjøre trinn 4 for å sortere en pivottabell som allerede eksisterer. - Åpne rullegardinlisten Region i pivottabellen. Velg Sorter stigende
Hvis du har Office 365 eller Excel 2019, kan du endre standardinnstillingene for alle fremtidige pivottabeller for å slå av denne funksjonen. Gå til Fil, Alternativer, Data, Rediger standardoppsett. I neste dialog, klikk pivottabellalternativer og slå av funksjonen vist ovenfor.
Se på video
Videoutskrift
Lær Excel fra Podcast, Episode 2211: Pivot Table, No Custom Sort.
Hei, velkommen tilbake til netcast, jeg er Bill Jelen. Dagens spørsmål: Hvorfor sorterer pivottabellene mine i en rar sekvens?
Ok, så vi har en liste over ansatte her, og de er alle på fornavnsbasis - Adam, April, Claire, Della - vi setter inn en pivottabell: Sett inn, pivottabell, OK - legg navn ned i venstre side, og Salg, av en eller annen grunn, kommer januar og april til toppen, deretter fulgt av Gary, Otto, Paul, mai, juni, og deretter alle andre, alfabetisk. Hva foregår her?
Vel, jeg vet nøyaktig hva som skjer her, og om mange ganger bruker jeg dette med vilje. La meg legge inn Region, så merker du at Region dukker opp: Øst, Sentral, Vest. Hvorfor dukker det opp, øst, sentral, vest? Fordi jeg setter opp en egendefinert liste: Fil, Alternativer, Avansert, blar helt til bunnen, Rediger egendefinerte lister og ser? Jeg har en liste - Øst, Sentral, Vest. Og denne listen lar meg bruke Fill Handle til å fylle denne listen, det lar meg sortere dataene i denne listen - du må gå inn i Data, Sort, og velg deretter den tredje rullegardinlisten Custom, og automatisk, Pivottabeller vil sorteres i denne listen. Det er vakkert, ikke sant? Jeg vil at det skal skje.
Men i tilfeller der vi har ansatt i stedet for region - og vi tilfeldigvis har noen ansatte som heter januar, april, mai, juni eller onsdag - vil de sveve til toppen av listen. Nå, hvorfor Gary, Otto og Paul? Fordi jeg lager falske data hele tiden, og så har jeg en tilpasset liste som starter med Andy og har 26 navn der inne, og det hender at Gary, Otto og Paul tilfeldigvis er på listen min. Hvis vi hadde hatt en ansatt ved navn onsdag eller tirsdag, hadde de også sortert til toppen av listen.
Selv om den ene tingen jeg virkelig ikke kan forklare her - og jeg prøver å forstå hvordan Excel fungerer hele tiden - men i dette ene tilfellet kan jeg ikke helt finne ut av mønsteret fordi januar og april er i dette liste her-- den fjerde listen fra toppen-- og hvis jeg bare hadde skrevet i januar og grep i Fill Handle og dratt-- eller, la oss si at jeg skrev inn Jan og grep i Fill Handle og dro - det kommer til å bruk den nærmeste bunnen, ikke sant? Så denne med det ekstra ordet "Total" kommer til å bli brukt i stedet for denne nær toppen av listen. Greit, så januar og april er på denne listen, og så - her, hvor er Andy, akkurat her-- så, her er Gary, og Otto, og Paul på listen. Men mai og juni - vel, mai er i denne listen, og juni er i denne listen. Ikke sant. Så hvorfor dets blande elementer fra denne listen, denne listen og denne listen-- Jeg har ingen forklaring på det. Jeg hadde forventet at det ville ha valgt en liste og brukt alt fra den listen, vet du, eller noe, ikke sant? Hvorfor januar og april-- og deretter juni helt her nede? Det gir ingen mening for meg i det hele tatt.
Men uansett, det er ikke poenget med episoden. Du var innstilt for å finne ut: "Hvordan får vi pivottabeller til å skje uten en tilpasset sortering?" Så her er hva du må gjøre: For å endre bare denne pivottabellen, høyreklikk, gå inn i pivottabellalternativer, gå til den andre kategorien kalt Totaler og filtre, og fjern avmerkingen i denne boksen "Bruk egendefinerte lister når du sorterer." Nå løser det ikke det. Og til og med å fjerne ansatt og sette tilbake ansatt løser det ikke. Du må gå her til denne rullegardinmenyen og si Sorter A-Å-Å, og det vil fikse det.
Men hva om du aldri vil at dette skal skje, ikke sant? Du har noen som heter Jan, og de sorterer alltid til toppen av listen. Du kan slå den av permanent. Gå til File, Options, Data-- nå, dette er helt nytt i Office 365, kom sammen i 2017 - Edit Standard Layout. Gå til Pivottabellalternativer, gå til Totaler og filtre, og fjern merket for "Bruk egendefinerte lister når du sorterer." Hvis du ikke har dette valget, er det på tide å oppgradere til Office 365. Det er egentlig ingen grunn til å ha Excel 2016 eller Excel 2013 eller Excel 2010. Du vil få den nyeste og beste versjonen - nye funksjoner hver måned, og de gir oss virkelig mange flotte nye funksjoner hver måned. Det er faktisk billigere å gå med Office 365 enn å betale $ 400 en gang hvert tredje år. Så du vet, det er ingen god grunn til det i det hele tatt.
Pivottabeller, egendefinerte lister, alt dekket i boken min LIVe, The 54 Greatest Excel Tips of All Time. Klikk på "Jeg" øverst til høyre for å lære mer om boken.
Greit, avslutning i dag: Hvorfor fortsetter May eller Jan å sortere til toppen av pivottabellen? Det er fordi pivottabeller følger sorteringsmønsteret for alle egendefinerte lister, og de månedene eller ukedagsnavnene er i de tilpassede listene. Så hvis du ansetter folk som heter juni eller mai eller onsdag, kommer de til å sortere til toppen av pivottabellen. Hvor kan du se dine tilpassede lister? Gå til Fil; Alternativer; Avansert; bla helt ned; Rediger egendefinerte lister. Men hei, de første fire seriene, de med månedsnavn og ukedagsnavn? De kan ikke fjernes. Du har ikke lov til å redigere dem; De er innebygd. Greit, så løsningen din er bare å si opp alle personene som heter april eller juni, eller du kan høyreklikke på pivottabellen og velge Alternativer; gå til Totals and Filters; fjern merket for "Bruk egendefinerte lister når du sorterer." Eller,hvis du har Office 365, er det bare å slå den av for alle fremtidige pivottabeller-- Fil; Alternativer; Data; Standardverdier for pivottabell; gå inn i Pivot Table Options og slå den av en gang. Det vil være av for alle fremtidige lister.
Vel, hei, for å prøve dette - for å laste ned arbeidsboken fra dagens video - besøk URL-en i YouTube-beskrivelsen.
Jeg vil takke deg for at du var innom; Vi sees neste gang for en ny netcast fra.
Last ned Excel-fil
For å laste ned Excel-filen: pivot-table-no-custom-sort.xlsx
Excel-tanken om dagen
Jeg har bedt mine Excel Master-venner om deres råd om Excel. Dagens tanke å tenke på:
"Lev et Excel-lånt liv"
Areef Ali
