Håndtering Negative tall i en kompleks Custom Format

October 14  by Eliza

Douglas er å ha noen problemer med å få hodet rundt et tilpasset format han trenger. Han har skapt et egendefinert format som viser store tall slik han ønsker, slik at $ 1.000.000 vises som $ 1,0M og $ 1000 vises som $ 1.0k. Dette formatet er som følger: [> 1000000] $ # 0 ,, "M"; [> 1000] $ # 0, "K"; $ #, ## 0.0... Douglas ønsker å vite hvordan du justerer tilpasset format, slik at negative tall vises i denne samme måte, men i rødt med parentes rundt dem, som ($ 1.0k).

Dessverre, hva du ønsker å gjøre er ikke mulig med en enkelt tilpasset format. Grunnen er fordi en enkelt tilpasset format kan bare ha fire forhold, atskilt av semikolon. Dette er den generelle syntaksen til et egendefinert format:

positiv; negativ; null; tekst

Legg merke til at det første format brukes når verdien er positiv, den andre når det er negativt, det tredje når verdien er nøyaktig lik null, og den fjerde når verdien er tekst. Selv om dette er den generelle syntaksen for tilpassede formater, du kan "fudge" formatene et lite på den måten du gjør. Tenk det formatet du bruker:

[> 1000000] $ # 0 ,, "M";. [> 1000] $ # 0, "K";. $ #, ## 0.0

Legg merke til at i henhold til den generelle syntaksen, vil formatet før den første semikolon brukes til positive verdier, det neste format for negative verdier, og den tredje for nullverdier. Men dette er ikke måten Excel oversetter dette definert format. Det oversetter det som "dersom den er større enn en million, gjør dette, dersom den er større enn 1000 gjør dette, ellers gjøre dette". Det er ingen positiv eller negativ konnotasjon i formatet; faktisk blir eventuelle negative verdier behandlet i standardbehandling, som er den tredje format.

Hva du prøver å gjøre er å definere to positive forhold (en for millioner og én for tusenvis) og to negative forhold (igjen, for millioner og tusenvis). Dette kan ikke gjøres i en enkelt tilpasset format, uansett hvordan du prøver å sette det sammen. I stedet bør du bruke to egendefinerte formater, for eksempel disse:

[> = 1000000] $ # 0 ,, "M";. [> = 1000] $ # 0, "K";. $ #, ## 0.0
[Red] [<= - 1000000] ($ # 0 ,, "M".) [Red] [

Det første format som skal benyttes i tilfelle av positive verdier; Det er en variant av den opprinnelige format antydet ved den første av spissen. Det andre format som skal benyttes sammen med negative verdier. Disse tilpassede formater må manuelt påført, basert på verdien i cellen.

Dette kan virke som mye arbeid å gå gjennom for å få formateringen du ønsker. Det er mulig å lage en makro som gjelder formatene, men makroen ville ikke være en triviell oppgave. Det ville trenge for å sjekke hva verdien i cellen er, plukke riktig format, konstruere format, stappe den inn i tilpassede format for cellen, og deretter gå videre til neste celle.

Det er en ting du kan gjøre, men-du kan kombinere bruk av et tilpasset format med Excel betinget formatering evner. Sette opp følgende tre tilpassede formater i regnearket:

_ ($ # 0_K _.) [Red] ($ # 0_K.) ;; @
_ ($ # 0, "K" _.) [Red] (. $ # 0, "K") ;; @
_ ($ # 0 ,, "M" _.) [Red] (. $ # 0 ,, "M") ;; @

Deretter kan du bruke betinget formatering evner (Hjem-fanen på båndet | Betinget formatering | Manage Rules) for å definere seks forskjellige formateringsregler. Når du klikker på Ny regel knappen for å begynne å definere hver regel, vil du velge format celler som inneholder på toppen av New Formatering dialogboksen regel-boksen. Her er de seks reglene du vil definere:

Celle verdi <= -1000000
Celle verdi> = 1000000
Celle verdi mellom -999 999 og -1000
Celle verdi mellom 1000 og 999999
Celle verdi mellom -999 og -1
Celle verdi mellom 0 og 999

Som du definere hver av disse reglene, vil du klikker på knappen Format i New Formatering dialogboksen regel-boksen. Dette presenterer Formater celler dialogboks der bør du klikke kategorien Tall. Der kan du velge Custom kategorien og plukke en av de tre tilpassede formatene du definerte. Her er de du bør velge:

  • For de første to betingede formatreglene nevnt ovenfor, velge den tredje tilpasset format som er nevnt ovenfor.
  • For det tredje og fjerde betinget formateringsregler som er nevnt ovenfor, velger den andre egendefinerte formatet ovenfor.
  • For det femte og sjette betinget formateringsregler som er nevnt ovenfor, velge den første egendefinerte formatet ovenfor.

Det er det; de betingede formateringsregler gjøre testing for verdiområder og deretter bruke de riktige tilpassede formater for disse tallene.

ExcelTips er din kilde for kostnadseffektiv Microsoft Excel trening. Dette tipset (10227) gjelder for Microsoft Excel 2007 og 2010. Du kan finne en versjon av dette tipset for den eldre menygrensesnittet av Excel her: Håndtering Negative tall i en kompleks Custom Format.