Creați o linie țintă dinamică în diagrama de bare Excel

Dacă vă aflați în departamentul de vânzări și viața dvs. ține de ținte, această tehnică de graficare este pentru dvs. Și dacă nu sunteți, citiți-l oricum pentru a învăța câteva trucuri excelente de diagramă Excel.

În această postare de blog, vă voi arăta o super modalitate de a crea o linie țintă dinamică într-o diagramă Excel, care vă poate ajuta să vă urmăriți performanța de-a lungul lunilor. Ceva așa cum se arată mai jos:

Linia țintă este controlată de bara de derulare și, ca și cum ținta este atinsă (sau depășită) în oricare dintre luni, ea este evidențiată în verde.

Crearea unei linii țintă dinamice în diagrama de bare Excel

Acest grafic are 3 părți:

1. Diagrama cu bare
2. Linia țintă (linie punctată orizontală)
3. Bara de derulare (pentru a controla valoarea țintă)

Diagrama cu bare

Am date așa cum se arată mai jos:

Celulele B2: B13 are toate valorile, în timp ce C2: C13 afișează o valoare numai dacă depășește valoarea țintă (în celula F2). Dacă valoarea este mai mică decât valoarea țintă, se afișează # N / A. Acum trebuie să trasăm aceste valori într-o diagramă cluster

1. Selectați întregul set de date (A1: C13)
2. Accesați Inserare -> Diagrame -> Diagramă coloană grupată
   
3. Selectați oricare dintre bare pentru valorile „Deasupra țintei” și faceți clic dreapta și selectați Formatare serie de date
   
4. În secțiunea Opțiune serie, modificați valoarea Suprapunerea seriei la 100%
   
5. Aceasta creează o diagramă, în care toate valorile care depășesc ținta sunt evidențiate într-o altă culoare (puteți verifica acest lucru modificând valoarea țintă)
   

Linia țintă

Aici permiteți-mi să vă arăt un mod inteligent de a crea o linie țintă folosind bare de eroare

1. Selectați graficul și accesați Design -> Select Data
   
2. În caseta de dialog Select Data Source, faceți clic pe Add
3. În caseta Editați seria, tastați numele seriei ca „Linie țintă” și în Valoare serie selectați celula Valoare țintă
   
4. Aceasta va insera o diagramă cu bare numai pentru primul punct de date (ianuarie)
   
5. Selectați această bară de date și faceți clic dreapta și selectați Modificare tip grafic serie
   
6. Schimbați tipul diagramei în diagrama Scatter. Aceasta va schimba bara într-un singur punct în ianuarie
   
7. Selectați punctul de date și accesați Proiectare -> Aspecte diagrame -> Adăugare element diagramă -> Bare de eroare -> Mai multe opțiuni pentru bare de eroare
   • Pentru Excel 2010, selectați punctul de date și accesați Aspect -> Analiză -> Bare de eroare -> Mai multe opțiuni pentru bare de eroare
     
8. Veți observa linii orizontale ale barei de eroare pe ambele părți ale punctului de împrăștiere. Selectați bara de eroare orizontală și apoi în secțiunea Opțiuni pentru bara de erori, selectați Personalizat și faceți clic pe Specificați valoarea
   
9. Dați o valoare pozitivă la 11 și o valoare negativă la 0 (puteți folosi accesarea și încercarea pentru a vedea ce valoare arată bine pentru graficul dvs.)
   
10. Selectați punctul de date Scatter și faceți clic dreapta și selectați Format Series Data. Mergeți la Opțiuni marcator și selectați Tip marcator ca niciunul. Aceasta elimină punctul de date și aveți doar bara de eroare (care este linia țintă)
11. Rețineți că bara de erori se va schimba ori de câte ori modificați valoarea țintă. Doar formatați-l pentru a face o linie punctată și schimbați culoarea pentru a face să arate mai bine

Bara de derulare

1. Creați o bară de derulare și aliniați-o cu graficul. Montați bara de defilare împreună cu graficul. Faceți clic aici pentru a afla cum să creați o bară de derulare în Excel.
2. Faceți ca valoarea maximă a barei de defilare să fie egală cu valoarea maximă din diagramă și conectați valoarea barei de defilare la orice celulă (am folosit G2)
3. În celula care are valoarea țintă, utilizați formula = 500-G2 (500 este valoarea maximă din grafic)
4. Aceasta este pentru a vă asigura că valoarea țintă se deplasează acum cu bara de derulare

Asta e!! Acum, când mutați bara de defilare și modificați valorile țintă, barele care îndeplinesc ținta se vor evidenția automat într-o altă culoare.

Încercați singur … Descărcați fișierul