Відстежуйте прогрес у напрямку до мети - Статті TechTV

З наближенням нового року, можливо, ви захочете відстежувати прогрес до якоїсь мети. Ця крута діаграма в Excel містить елементи, які вказують, чи йдете ви до досягнення мети. Скажіть, що ви хочете пробігти 250 миль у цьому кварталі.

Чорна лінія - мета
Блакитна лінія - це «на шляху» - для досягнення мети потрібно пробігати 2,7 милі на день
Зелена лінія - це фактичний пробіг
Червона лінія - це передбачення того, де ви закінчите чверть, залежно від вашого прогресу на даний момент. Червона лінія - це на сьогоднішній день найкрутіша частина діаграми.

Ось як створити діаграму.

Введіть ці заголовки у верхній частині аркуша. У комірку A5 введіть першу дату для діаграми.
Помістіть покажчик комірки в A5. Ви помітите, що у правому нижньому куті A5 є квадратна крапка. Це ручка заповнення. За допомогою миші візьміться за ручку заповнення та перетягніть вниз. Слідкуйте за підказкою, поки не побачите, що ви затягнули досить далеко, щоб включити останню дату кварталу.
Формула для B2 така =COUNT(A5:A200). Це буде враховувати кількість днів у вашому діапазоні.
Формула для B3 така =B1/B2. Це обчислює, скільки потрібно пробігати кожного дня, щоб досягти мети.
Використовуйте стовпець B, щоб ввести, наскільки далеко ви бігли щодня.
Хоча стовпець B містить ваші фактичні дані, він не підходить для створення діаграми. Ви фактично побудуєте абсолютно новий діапазон діаграм у стовпцях C: F, який буде містити елементи, необхідні для діаграми.
У колонці C буде вказано дати. Якщо одна вісь діапазону діаграми містить цифри або дати, слід залишити пусту верхню ліву комірку діапазону діаграм.
Це дивна помилка в Excel - якщо залишити цю клітинку порожньою, то Intellisense Excel може пізніше розпізнати діапазон діаграм. Отже, залиште клітинку С5 порожньою, а в D5: F5 введіть заголовки Відстань, Відстеження та Ціль.
Формула в С5 така =A5.
Виділіть комірку С5. У меню виберіть Форматувати комірки. На вкладці Цифровий виберіть категорію Дата, а потім формат 3/14.
Виділіть комірку С5. Перетягніть маркер заповнення вниз, щоб скопіювати цю формулу. Перетягніть вниз, наскільки є дані в стовпці А.
Для стовпців D & E формули в рядку 5 будуть відрізнятися від формул у рядках 6 і нижче. Формули в рядку 5 забезпечують числовий початок. Формули в рядку 6 і нижче збільшать ці числові значення у рядку 5. Формула для D5 така =B5- це просто скопіює відстань першого дня в діапазон діаграми.
На наступному кроці Excel додасть лінію тренду на основі значень у стовпці D. Якщо ви просто скопіювали D5 вниз, ви отримаєте нулі для майбутніх дат. Нулі призведуть до втрати лінії тренду. Фокус у тому, що вам потрібно заповнити майбутні дати значеннями помилок # N / A.
Тоді англійською мовою формула в основному говорить: "Якщо ми працювали сьогодні, то додайте сьогоднішнє значення до загальної суми вчорашнього дня. В іншому випадку, поставте в цю клітинку Н / Д.

Для досягнення цього результату введіть =IF(NOT(ISBLANK(B6)),D5+B6,NA())у клітинку D6.
Виділіть комірку D6. Двічі клацніть маркер заповнення, щоб скопіювати цю формулу вниз. Трюк подвійного клацання працює, оскільки стовпець ліворуч від цієї комірки містить значення, які вказуватимуть Excel на те, як далеко потрібно скопіювати формулу.
Формулу в E5 потрібно копіювати на день з B3. Введіть =B3в E5.
Формула для решти Е повинна додати В3 до попереднього дня. Формула для E6 може бути, =E5+B3але ця формула не буде працювати, коли ви копіюєте комірку в інші рядки. Ви завжди хочете, щоб формула додала B3 до клітинки вище.
Таким чином, використовуйте =E5+$B$3в E6. Знаки долара переконуються, що коли ви копіюєте формулу, вона завжди буде вказувати на B3.
Поверніть покажчик комірки назад до E6 і двічі клацніть маркер заповнення, щоб скопіювати формулу вниз.
Формула для всього стовпця F має =$B$1. Цей діапазон буде використаний для проведення чорної лінії воріт на графіку. Ось порада щодо введення цієї формули. Початок у F5. Введіть знак рівності. За допомогою миші торкніться B1. Потім введіть клавішу F4, щоб поставити знаки долара.
Натисніть Ctrl + Enter, щоб прийняти формулу та залишитися в поточній комірці. Тепер ви можете двічі клацнути маркер заповнення, щоб скопіювати формулу вниз.
Нарешті, ви готові створити діаграму. Виберіть діапазон C4: F94. Або скористайтеся меню «Вставити - Діаграма», або клацніть піктограму «Майстер діаграм» на стандартній панелі інструментів.
На кроці 1 виберіть Лінійну діаграму.
Діаграма потребує великих налаштувань, але нічого з цього не відбувається у майстрі, тому просто натисніть кнопку Готово, щоб створити цю жахливо виглядаючу діаграму.
Клацніть правою кнопкою миші на сірому фоні та виберіть Форматувати ділянку ділянки.
Виберіть Ефекти заливки.
Налаштуйте двоколірний градієнт, як показано тут.
Клацніть правою кнопкою миші жовтий рядок серії цілей. Виберіть Формат серії даних.
Виберіть товсту синю лінію для серії голів.
Повторіть кроки 23-24, щоб змінити лінію доріжки на тонку чорну лінію.
Форматувати серію Відстань складно, оскільки є лише 2 точки, і це майже неможливо побачити. Якщо важко клацнути правою кнопкою миші елемент діаграми, можна скористатися випадаючим списком на панелі інструментів "Діаграма".
Після вибору серії відстаней, використовуйте піктограму Властивості, щоб відкрити діалогове вікно Форматування для вибраної серії.
Додайте маркер до цього діалогового вікна. Змініть колір на темно-синій, а товщину лінії на середній.
Щоб додати лінію тренду, вам доведеться клацнути правою кнопкою миші на ряді відстані. На сьогодні це практично неможливо зробити. Отже - я пропоную вам "обдурити". Введіть підроблену точку даних 20 у клітинку B7. Це дозволить клацнути правою кнопкою миші на ряді відстані. Виберіть Додати лінію тренду.
У діалоговому вікні лінії тренду виберіть Лінійна лінія тренду.
Виділіть клітинку B7 і введіть клавішу Delete, щоб стерти там фальшиве значення. Тепер ви отримаєте лінію тренду, намальовану товстою чорною лінією.
Клацніть правою кнопкою миші лінію тренду та виберіть Формат лінії тренду.
Змініть лінію тренду на ламану червону лінію з тонкою вагою.
На вкладці Параметри Форматування лінії тренду змініть назву серії на Прогнозування

Тепер у вас є заповнена діаграма. Якщо ви пропустили день бігу, введіть нуль у стовпець B. Якщо ви пропустите пару днів, червона лінія прогнозу покаже, що ви не на шляху до досягнення мети.