%3F.png)
Я использую Excel 2010.
Я пытаюсь построить график фактических данных по расходам (сплошная линия) и расширить этот график на будущее (пунктирная линия).
У меня есть лист из 5 столбцов. Первый столбец — это столбец даты, который отображает начало месяца; 1 января, 1 февраля, 1 марта и т. д. Это ось x.
Второй столбец — прогнозируемый бюджет. Это довольно статичные данные; создаются в начале программы и обновляются лишь изредка (ежегодно).
Третий столбец — это фактические данные о расходах с начала программы и до конца этого месяца. Эти данные связаны с листом, где в электронную таблицу вводятся новые данные многих типов. Мне нравится ограничивать количество мест, где в электронную таблицу вводятся новые данные, и вводить фрагмент данных только один раз.
Четвертый столбец — это промежуточный итог по месяцам фактических данных, который заканчивается в этом месяце. Это функция, которая управляет высотой столбца и в основном говорит: «если в столбце фактических данных есть число, то добавьте его к предыдущему итогу и отобразите результат, в противном случае отобразите пустую ячейку». Таким образом, данные начинаются с месяца после запуска программы и заканчиваются после ввода данных о расходах в этом месяце.
Пятый столбец начинается со следующего месяца и добавляет прогнозируемые данные о расходах за этот месяц к фактическим данным о расходах за этот месяц. Это продолжается до прогнозируемого конца программы.
Итак, типичный набор данных может выглядеть следующим образом:
Месяц Бюджет Фактический Акт Всего Проект Всего Янв 100 99 99 Фев 95 98 197 Мар 98 295 Апрель 99 394 120 мая 514
Сейчас февраль, и на сегодняшний день мы фактически потратили 197. Исходя из бюджета, мы планируем потратить 514 к концу программы. Я пытаюсь представить эту идею на графике.
Я хочу нанести на график фактические данные расходов на текущий месяц, а затем продолжить с бюджетными данными. Таким образом, на графике будет сплошная линия от прошлого до сегодняшнего дня, а затем продолжиться пунктирной линией, чтобы нанести на график прогнозируемые данные бюджета, добавленные к фактическим данным с сегодняшнего дня до конца программы. Я получаю фактические данные, нанесенные на график на текущую дату, затем линию через середину моего графика вниз до нуля. Линия прогнозируемых расходов идет от нуля до текущей даты, затем линию через мой график вверх до текущих расходов и продолжается в будущее.
Я могу заставить его делать именно то, что мне нужно, удалив формулу в столбце 4 со следующего месяца до конца программы (и прогнозируемый столбец с начала программы до этого месяца), но тогда я потеряю свои автоматические обновления и мне придется «возиться» с моей таблицей каждый месяц. Поскольку я делаю такие виды прогнозов по всей этой рабочей книге, это много «возни» каждый месяц.
Если щелкнуть правой кнопкой мыши по полю графика и выбрать «Выбрать данные», а затем «Скрытые и пустые ячейки» в левом нижнем углу диалогового окна, вы «вроде бы» можете указать Excel отображать пустые ячейки как пробелы, но это, похоже, работает только в том случае, если я стираю формулы.
Есть ли способ заставить это работать?
решение1
Формулы рядов столбцов должны возвращать #N/A (например, =IF(isblank(Actual),na(),prior_actual_total+actual)) вместо пустого значения или нуля. Excel будет игнорировать эти точки при построении диаграммы, и ваши линии будут начинаться/останавливаться, а не падать до нуля (или расти от него).
решение2
Если вы используете опцию #NA, вам нужно убедиться, что в окне «Выбор источника данных» вы нажали кнопку «Скрытые и пустые ячейки» и отметили «Пробелы», а не «Ноль». Тогда он не упадет до нуля