Phil skrev i morges og spurte om å lage diagram i Excel.
Er det noen måte å ta to kolonner som inneholder start- og stoppdatoer for individuelle hendelser og lage et Gantt-typediagram uten å måtte forlate Excel?
Dette emnet ble dekket i tipset om Opprett tidslinjediagram. Det tipset fra sommeren 2001 nevnte at du også kunne lage et Gantt-diagram på et regneark ved bruk av betinget formatering. Denne typen diagram vil løse Phils spørsmål.
Jeg forestiller meg at dataene til Phil ser ut som tabellen til venstre. Det er en hendelse, deretter startdatoer i kolonne B og sluttdatoer i kolonne C. Jeg bruker år for mitt eksempel, men du kan enkelt bruke vanlige Excel-datoer.
Det neste trinnet kan lett innlemmes i en makro, men det virkelige fokuset med denne teknikken er å sette opp betinget formatering. Jeg skannet gjennom dataene mine og la merke til at datoene varierer fra 1901 til 1919. Fra og med kolonne D kom jeg inn det første året 1901. I E1 skrev jeg inn 1902. Du kan deretter velge D1: E1, klikk på fyllhåndtaket i nederste høyre hjørne av utvalget med musen og dra ut til kolonne W for å fylle ut alle årene fra 1901 til 1920.
For å få årene til å ta mindre plass, velg D1: W1, og bruk deretter Format - Celler - Justering, velg alternativet for vertikal tekst. Velg deretter Format - Kolonne - Autowidth og du vil kunne se alle 23 kolonnene på skjermen.
Velg cellen øverst til venstre i Gantt-kartområdet, eller D2 i dette eksemplet. Velg Format - Betinget formatering fra menyen. Dialogboksen har i utgangspunktet en rullegardin på venstre side som standard er "Cell Value Is". Endre denne rullegardinmenyen til "Formel er", og høyre side av dialogboksen endres til en stor tekstboks for å skrive inn en formel.
Målet er å legge inn en formel som sjekker for å se om året i rad 1 over denne cellen faller innenfor årene i kolonnene B & C i denne raden. Det er viktig å bruke riktig kombinasjon av relative og absolutte adresser slik at formelen vi skriver inn i D2 kan kopieres til alle cellene i området.
Det vil være to forhold å sjekke, og begge må være sanne. Dette betyr at vi skal begynne med =AND()funksjonen.
Den første tilstanden vil sjekke om året i rad 1 er større enn eller lik året i kolonne B. Siden jeg alltid vil at denne formelen skal referere til rad 1, er den første delen av formelen D $ 1> = $ B2 . Merk at dollartegnet før 1 i D $ 1 vil sikre at formelen vår alltid peker mot rad 1, og at dollartegnet før B i $ B2 vil sikre at den alltid sammenlignes med kolonne B.
Den andre tilstanden vil sjekke om året i rad 1 er mindre enn eller lik datoen i kolonne C. Vi må fortsatt bruke samme relative og absolutte adressering, så dette vil være D $ 1 <= $ C2
Vi må kombinere begge disse forholdene ved hjelp av AND () -funksjonen. Dette ville være=AND(D$1>=$B2,D$1<=$C2)
I formelboksen i dialogboksen Formasjonsformatering, skriv inn denne formelen. Forsikre deg om å starte med likhetstegn, ellers vil den betingede formateringen ikke fungere.
Velg deretter en lys farge som skal brukes når tilstanden er oppfylt. Klikk på Format… -knappen. Velg en farge i kategorien Mønstre. Klikk OK for å lukke dialogboksen Formater celler, og du må ha en dialog med betinget formatering som ser ut som denne
Klikk OK for å avvise Betinget formatering-boksen. Hvis den øverste venstre cellen din i D2 tilfeldigvis faller om et år, blir den cellen gul.
Enten cellen ble gul eller ikke, klikk på D2 og bruk Ctrl + C eller Edit - Copy for å kopiere den cellen.
Fremhev D2: W6 og velg Rediger - Lim inn spesial - Formater - OK fra menyen. Det betingede formatet kopieres til hele Gantt-diagrammet, og du vil ende opp med et diagram som ser ut som dette.
Betinget formatering er et flott verktøy og lar deg enkelt lage Gantt-type diagrammer rett på regnearket. Husk at du er begrenset til bare tre forhold for en hvilken som helst celle. Du kan eksperimentere med forskjellige kombinasjoner av forhold. For å lage grenser rundt hver stolpe i Gantt-diagrammet brukte jeg tre kondisjoner som vist nedenfor og brukte forskjellige grenser for hver tilstand.
