Отслеживание прогресса в достижении цели - статьи TechTV

С приближением нового года вы можете отслеживать прогресс в достижении какой-то цели. Эта классная диаграмма в Excel содержит элементы, которые показывают, приближаетесь ли вы к цели. Скажите, что вы хотите пробежать 250 миль в этом квартале.

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

Вот как создать диаграмму.

1. Введите эти заголовки в верхней части листа. В ячейке A5 введите первую дату для диаграммы.

   

2. Поместите указатель в ячейку A5. Вы заметите квадратную точку в правом нижнем углу A5. Это маркер заполнения. С помощью мыши возьмитесь за маркер заливки и потяните вниз. Следите за всплывающей подсказкой, пока не увидите, что вы перетащили достаточно далеко, чтобы включить последнюю дату квартала.

   

3. Формула для B2 есть =COUNT(A5:A200). Это посчитает количество дней в вашем диапазоне.

   

4. Формула для B3 есть =B1/B2. Это подсчитывает, сколько вам нужно бегать каждый день, чтобы достичь цели.

   

5. В столбце B укажите, сколько вы пробежали каждый день.

   

6. Хотя столбец B содержит ваши фактические данные, он не подходит для создания диаграммы. Фактически вы создадите совершенно новый диапазон диаграммы в столбцах C: F, который будет содержать элементы, необходимые для диаграммы.
7. Столбец C будет содержать даты. Если одна ось диапазона диаграммы содержит числа или даты, вы должны оставить верхнюю левую ячейку диапазона диаграммы пустой.

   Это странная ошибка в Excel - если оставить эту ячейку пустой, программа Excel Intellisense сможет распознать диапазон диаграммы позже. Итак, оставьте ячейку C5 пустой и введите заголовки Distance, Track и Goal в D5: F5.

   

8. Формула в C5 есть =A5.

   

9. Выберите ячейку C5. В меню выберите Формат ячеек. На вкладке "Числа" выберите категорию "Дата", а затем формат 3/14.

   

10. Выберите ячейку C5. Перетащите маркер заполнения вниз, чтобы скопировать эту формулу. Перетащите вниз, пока у вас есть данные в столбце A.
11. Для столбцов D и E формулы в строке 5 будут отличаться от формул в строках 6 и ниже. Формулы в строке 5 обеспечат числовое начало. Формулы в строке 6 и ниже увеличивают эти числовые значения в строке 5. Формула для D5 =B5- это просто копирует расстояние первого дня в диапазон диаграммы.

    

12. На более позднем этапе Excel добавит линию тренда на основе значений в столбце D. Если вы просто скопировали D5 вниз, вы получите нули для будущих дат. Нули аннулируют линию тренда. Хитрость в том, что вам нужно заполнить будущие даты ошибочными значениями # N / A.

    Таким образом, на английском языке формула в основном гласит: «Если мы запустили сегодня, добавьте сегодняшнее значение к вчерашней сумме». В противном случае укажите в этой ячейке «Н / Д».

    Чтобы добиться этого результата, введите =IF(NOT(ISBLANK(B6)),D5+B6,NA())в ячейку D6.

    

13. Выберите ячейку D6. Дважды щелкните маркер заполнения, чтобы скопировать эту формулу вниз. Трюк с двойным щелчком работает, потому что столбец слева от этой ячейки содержит значения, которые укажут Excel, как далеко вниз необходимо скопировать формулу.

    

14. Формула в E5 должна скопировать день из B3. Войдите =B3в E5.
15. Формула для оставшейся части E должна добавить B3 к предыдущему дню. Формула для E6 может быть =E5+B3такой, но эта формула не будет работать, когда вы копируете ячейку в другие строки. Вы всегда хотите, чтобы формула добавляла B3 в ячейку выше.

    Таким образом, используйте =E5+$B$3в E6. Знаки доллара гарантируют, что при копировании формулы она всегда будет указывать на B3.

    

16. Переместите указатель ячейки обратно на E6 и дважды щелкните маркер заполнения, чтобы скопировать формулу вниз.
17. Формула для всего столбца F есть =$B$1. Этот диапазон будет использован для рисования черной линии ворот на графике. Вот совет по вводу этой формулы. Начать с F5. Введите знак равенства. С помощью мыши коснитесь B1. Затем нажмите клавишу F4, чтобы вставить знаки доллара.

    Нажмите Ctrl + Enter, чтобы принять формулу и остаться в текущей ячейке. Теперь вы можете дважды щелкнуть маркер заполнения, чтобы скопировать формулу вниз.

    

18. Наконец, вы готовы создать диаграмму. Выберите диапазон C4: F94. Либо используйте Insert - Chart из меню, либо щелкните значок Chart Wizard на стандартной панели инструментов.

    

19. На шаге 1 выберите линейный график.

    

20. Для диаграммы потребуется множество настроек, но в мастере этого не происходит, поэтому просто нажмите «Готово», чтобы создать эту ужасно выглядящую диаграмму.

    

21. Щелкните правой кнопкой мыши серый фон и выберите «Форматировать область построения».

    

22. Выберите «Эффекты заливки».

    

23. Настройте двухцветный градиент, как показано здесь.

    

24. Щелкните правой кнопкой мыши желтую строку серии целей. Выберите Форматировать ряд данных.

    

25. Выберите толстую синюю линию для серии целей.

    

26. Повторите шаги 23-24, чтобы изменить линию трека на тонкую черную линию.
27. Серию Distance сложно отформатировать, так как там всего 2 точки и их практически невозможно увидеть. Если сложно щелкнуть правой кнопкой мыши элемент диаграммы, можно использовать раскрывающийся список на панели инструментов диаграммы.

    

28. После выбора серии расстояний используйте значок «Свойства», чтобы открыть диалоговое окно «Формат» для выбранной серии.

    

29. Добавьте маркер в это диалоговое окно. Измените цвет на темно-синий, а толщину линии на среднюю.

    

30. Чтобы добавить линию тренда, вам нужно будет щелкнуть правой кнопкой мыши ряд расстояний. На данный момент это сделать практически невозможно. Итак - предлагаю "схитрить". Введите фальшивую точку данных 20 в ячейку B7. Это позволит вам щелкнуть правой кнопкой мыши серию расстояний. Выберите Добавить линию тренда.

    

31. В диалоговом окне «Линия тренда» выберите «Линия тренда».

    

32. Выберите ячейку B7 и нажмите клавишу Delete, чтобы стереть там фальшивое значение. Теперь у вас будет линия тренда в виде толстой черной линии.

    

33. Щелкните линию тренда правой кнопкой мыши и выберите «Форматировать линию тренда».

    

34. Измените линию тренда на прерывистую красную линию с тонкой гири.

    

35. На вкладке Параметры линии тренда формата измените название серии на Прогноз.

    

Теперь у вас есть готовая диаграмма. Если вы пропустите день бега, введите ноль в столбце B. Если вы пропустите пару дней, красная линия прогноза покажет, что вы не на правильном пути к цели.

Вы можете скачать готовый график по этой ссылке.