Проследяване на напредъка към цел - Статии на TechTV

С наближаването на новата година може да искате да проследите напредъка към някаква цел. Тази страхотна диаграма в Excel съдържа елементи, които показват дали сте на път да постигнете целта. Кажете, че искате да избягате 250 мили през това тримесечие.

Черната линия е целта
Синята линия е линията „на път“ - трябва да бягате 2,7 мили на ден, за да постигнете целта
Зелената линия е действителният пробег на мили
Червената линия е прогнозата къде ще завършите тримесечието, въз основа на напредъка ви до момента. Червената линия е най-яката част на графиката.

Ето как да създадете диаграмата.

Въведете тези заглавия в горната част на работния лист. В клетка A5 въведете първата дата за диаграмата.
Поставете клетката в A5. Ще забележите, че в долния десен ъгъл на A5 има квадратна точка. Това е дръжката за пълнене. С мишката хванете дръжката за пълнене и плъзнете надолу. Гледайте подсказката, докато не видите, че сте влачили достатъчно далеч, за да включите последната дата на тримесечието.
Формулата за B2 е =COUNT(A5:A200). Това ще отчита броя на дните във вашия диапазон.
Формулата за B3 е =B1/B2. Това изчислява колко трябва да бягате всеки ден, за да постигнете целта.
Използвайте колона Б, за да въведете докъде сте стигнали всеки ден.
Въпреки че колона Б съдържа вашите действителни данни, тя не е подходяща за създаване на диаграмата. Всъщност ще изградите напълно нов диапазон на диаграмата в колони C: F, който ще съдържа елементите, необходими за диаграмата.
Колона C ще съдържа дати. Ако една ос от диапазона на диаграмата ви съдържа числа или дати, трябва да оставите горната лява клетка на диапазона на диаграмата празна.
Това е странна грешка в Excel - оставянето на тази клетка празна ще позволи на Intellisense на Excel да разпознае обхвата на диаграмата по-късно. И така, оставете клетката C5 празна и въведете заглавия за Разстояние, Проследяване и Цел в D5: F5.
Формулата в C5 е =A5.
Изберете клетка C5. От менюто изберете Форматиране на клетки. В раздела Numeric изберете категория Date и след това формат 3/14.
Изберете клетка C5. Плъзнете дръжката за попълване надолу, за да копирате тази формула. Плъзнете надолу, доколкото имате данни в колона А.
За колони D и E формулите в ред 5 ще бъдат различни от тези в редове 6 и по-долу. Формулите в ред 5 ще осигурят цифрово начало. Формулите в ред 6 и по-долу ще увеличат тези числови стойности в ред 5. Формулата за D5 е =B5- това просто ще копира разстоянието за първия ден в диапазона на диаграмата.
В по-късна стъпка Excel ще добави линия на тенденция въз основа на стойностите в колона D. Ако просто сте копирали D5 надолу, ще получите нули за бъдещи дати. Нулите ще обезсилят линията на тенденция. Номерът е, че трябва да попълните бъдещите дати със стойности за грешка # N / A.
Тогава на английски формулата основно казва: "Ако бяхме днес, добавете днешната стойност към общата сума от вчера. В противен случай поставете N / A в тази клетка.

За да постигнете този резултат, въведете =IF(NOT(ISBLANK(B6)),D5+B6,NA())в клетка D6.
Изберете клетка D6. Щракнете двукратно дръжката за попълване, за да копирате тази формула надолу. Трикът с двойно щракване работи, тъй като колоната вляво от тази клетка съдържа стойности, които ще насочат Excel колко далеч надолу формулата трябва да бъде копирана.
Формулата в E5 трябва да копира на ден от B3. Въведете =B3в E5.
Формулата за останалата част от E трябва да добави B3 към предходния ден. Формулата за E6 може да бъде, =E5+B3но тази формула няма да работи, когато копирате клетката надолу към останалите редове. Винаги искате формулата да добави B3 към клетката по-горе.
По този начин използвайте =E5+$B$3в E6. Доларовите знаци се уверете, че когато копирате формулата, тя винаги ще сочи към B3.
Преместете показалеца на клетката обратно на E6 и щракнете двукратно върху манипулатора за попълване, за да копирате формулата надолу.
Формулата за цялата колона F е =$B$1. Този диапазон ще се използва за изтегляне в черната голова линия на графиката. Ето съвет за въвеждане на тази формула. Започнете във F5. Въведете знака за равенство. С помощта на мишката докоснете B1. След това въведете клавиша F4, за да поставите знаците за долар.
Натиснете Ctrl + Enter, за да приемете формулата и да останете в текущата клетка. Вече можете да щракнете двукратно върху дръжката за попълване, за да копирате формулата надолу.
И накрая, вие сте готови да създадете диаграмата. Изберете обхвата на C4: F94. Или използвайте Insert - Chart от менюто, или щракнете върху иконата Chart Wizard в стандартната лента с инструменти.
В стъпка 1 изберете Линейна диаграма.
Графиката ще изисква много персонализиране, но нищо от това не се случва в съветника, така че просто щракнете върху Finish, за да създадете тази ужасно изглеждаща диаграма.
Щракнете с десния бутон върху сивия фон и изберете Форматиране на участъка.
Изберете Ефекти за запълване.
Настройте двуцветен градиент, както е показано тук.
Щракнете с десния бутон на мишката върху жълтия ред на целта. Изберете Форматиране на поредица от данни.
Изберете дебела синя линия за серията от голове.
Повторете стъпки 23-24, за да промените линията на пистата на тънка черна линия.
Трудно е да форматирате серията Разстояние, тъй като има само 2 точки и е почти невъзможно да се види. Ако е трудно да щракнете с десния бутон върху елемент от диаграмата, можете да използвате падащото меню в лентата с инструменти Charting.
След като изберете серията от разстояния, използвайте иконата Properties, за да отворите диалоговия прозорец Format за избраната серия.
Добавете маркер към този диалогов прозорец. Променете цвета на тъмно син и дебелината на линията на средна.
За да добавите линията на тенденция, ще трябва да можете да щракнете с десния бутон на мишката върху поредицата от разстояния. В този момент е почти невъзможно да се направи. И така - предлагам ви да "изневерите". Въведете фалшива точка от данни 20 в клетка B7. Това ще ви позволи да щракнете с десния бутон върху поредицата от разстояния. Изберете Добавяне на линия на тенденция.
В диалоговия прозорец Trendline изберете Linear Trendline.
Изберете клетка B7 и въведете клавиша Delete, за да изтриете фалшивата стойност там. Сега линията на тренда ще бъде изчертана като дебела черна линия.
Щракнете с десния бутон върху линията на тенденцията и изберете Форматиране на линия на тенденция.
Променете Trendline на счупена червена линия с тънка тежест.
В раздела „Опции“ на „Форматиране на линия на тенденция“ променете името на поредицата на „Прогнозиране“

Вече имате попълнена диаграма. Ако пропуснете ден бягане, въведете нула в колона Б. Ако пропуснете няколко дни, червената линия за прогнозиране ще покаже, че не сте на път да постигнете целта.