Я собираюсь предположить, что у вас есть диаграмма, исходные данные которой имеют все годы, и либо значение для этого года, либо # N/A - возможно, созданная с помощью функции NA() .
В этом случае годы все еще отображаются на вашем графике, потому что они все еще в ваших данных. Вы должны изменить это так, чтобы годы в исходных данных были полностью удалены.
Вы можете сделать это с помощью сводной диаграммы, хотя вам нужно будет написать небольшой VBA для обновления сводного кэша, когда пользователь выбирает разные годы.
Вы также можете сделать это с некоторыми формулами и динамическими диапазонами. Осуществимость этого зависит от того, насколько большими и динамичными будут данные вашей диаграммы. Если вы показываете сводку за каждый год, это может быть легко. Если вы показываете сотни точек данных, это может быть громоздким.
Можете ли вы добавить пример желаемого результата, чтобы мы могли выработать ответ? (Я обновлю этот ответ на основе вашего ответа.)
ОБНОВЛЕНИЕ НА ОСНОВАНИИ ОБНОВЛЕНИЯ ОП
Excel не сбрасывает значения NA() потому что он все еще может их видеть, даже если нет ничего для графика. Это то же самое, что текст там. Ваша ось по-прежнему видит все периоды, поэтому она имеет метки для размещения на оси, а затем просто пропускает данные, которые она не может обработать. Мы должны изменить это так, чтобы Excel смотрел только те данные, которые нам нужны.
- Проблема 1) Не наметить определенные годы
- Проблема 2) По-прежнему наметить, что будет после этих лет. (График EG только на 2014 и 2016 годы)
Если бы это была только проблема 1, мы могли бы установить несколько динамических именованных диапазонов и установить их в качестве источника диаграммы. Однако из-за проблемы 2 мы должны сначала установить промежуточную таблицу, а затем создать именованные диапазоны.
Во-первых, я прошу прощения, если это становится немного плотным. Меня часто обвиняют в том, что я слишком многословен. Я действительно хотел бы просто загрузить файл здесь ...
ШАГ 1
Вот CSV-версия моей отправной точки. Мои данные находятся в диапазоне A1:D35 (обратите внимание, что я добавил в начало столбец с именем Order - это поможет позже - и расширил данные до 2016 года.)
Заказ, Срок, заказы, отгрузки
1, Р6/2014,281,74
2, Р7/2014.498.126
3, Р8/2014.589.132
4, Р9/2014.655.158
5, Р10/2014.679.161
6, Р11/2014.489.115
7, Р12/2014.574.129
8, Р13/2014,340,80
9, Р1/2015.615.225
10, Р2/2015,0,0
11, Р3/2015,0,0
12, Р4/2015,0,0
13, Р5/2015,0,0
14, Р6/2015,0,0
15, Р7/2015,0,0
16, Р8/2015,0,0
17, Р9/2015,0,0
18, Р10/2015,0,0
19, Р11/2015,0,0
20, Р12/2015,0,0
21, Р13/2015,0,0
22, Р1/2016,0,0
23, Р2/2016,0,0
24, Р3/2016,0,0
25, Р4/2016,0,0
26, Р5/2016,0,0
27, Р6/2016,0,0
28, Р7/2016,0,0
29, P8/2016,0,0
30, Р9/2016,0,0
31, Р10/2016,0,0
32, Р11/2016,0,0
33, Р12/2016,0,0
34, Р13/2016,0,0
ШАГ 2
Добавьте промежуточную таблицу к NA() что не нужно. Для вас это может быть таким же, как Шаг 1. Для меня я использовал Шаг 1 в качестве необработанных данных, поэтому мне нужен этот другой шаг. Мои данные находятся в диапазоне F1:I35 . В итоге я выглядел как CSV, когда я выбрал 2015 год.
Заказ, Срок, заказы, отгрузки
1, Р6/2014,281,74
2, Р7/2014.498.126
3, Р8/2014.589.132
4, Р9/2014.655.158
5, Р10/2014.679.161
6, Р11/2014.489.115
7, Р12/2014.574.129
8, Р13/2014,340,80
9, Р1/2015, # N/A, # N/A
10, Р2/2015, # N/A, # N/A
11, Р3/2015, # N/A, # N/A
12, Р4/2015, # N/A, # N/A
13, Р5/2015, # N/A, # N/A
14, Р6/2015, # N/A, # N/A
15, Р7/2015, # N/A, # N/A
16, Р8/2015, # N/A, # N/A
17, Р9/2015, # N/A, # N/A
18, Р10/2015, # N/A, # N/A
19, Р11/2015, # N/A, # N/A
20, Р12/2015, # N/A, # N/A
21, Р13/2015, # N/A, # N/A
22, Р1/2016,0,0
23, Р2/2016,0,0
24, Р3/2016,0,0
25, Р4/2016,0,0
26, Р5/2016,0,0
27, Р6/2016,0,0
28, Р7/2016,0,0
29, P8/2016,0,0
30, Р9/2016,0,0
31, Р10/2016,0,0
32, Р11/2016,0,0
33, Р12/2016,0,0
34, Р13/2016,0,0
ШАГ 3
Добавьте третью таблицу, которая извлекает только значения, которые не являются #N/A во второй таблице. Это достигается с помощью формул массива. Помните, как мы добавили столбец Order ? Мы собираемся найти все те значения, которые не совпадают с #N/A в столбце « Orders а затем использовать то, что осталось, чтобы получить остальные данные. Мой третий стол находится в диапазоне K1:N35 .
Формула для первого значения в столбце « Order : (Для всех этих формул важно ввести это с помощью SHIFT+ENTER чтобы превратить их в формулы массива, как указано в скобках { } на обоих концах.)
{=MIN(IF(NOT(ISERROR($H$2:$H$35)),$F$2:$F$35))}
Это находит первое значение в столбце Order второй таблицы (F2:F35), где столбец Orders (H2:H35) не является ошибкой.
Формула для остатка второго значения в столбце Order :
=IF(OR($K2=0,$K2=MAX($F$2:$F$35)),0,MIN(IF(NOT(ISERROR($H$2:$H$35)),IF($F$2:$F$35>$K2,$F$2:$F$35))))
Сначала проверяется, является ли предыдущее значение (в данном случае K2 ) 0 или максимум значений из 2-й таблицы (что означает, что мы закончили), и возвращает 0 в этом случае. В противном случае он находит первое значение в столбце « Order второй таблицы, которое больше, чем предыдущее найденное значение, и где столбец « Orders не является ошибкой.
Эту формулу можно перетащить до остальной части столбца. Результат выглядит как этот CSV.
порядок
1
2
3
4
5
6
7
8
22
23
24
25
26
27
28
29
30
31
32
33
34
0
0
0
0
0
0
0
0
0
0
0
0
0
ШАГ 4
Используйте комбинацию INDEX и MATCH чтобы найти значения для любых оставшихся значений Order Шаг 3. Формула Период, Заказы и Отгрузки приведены в следующем порядке:
=INDEX(B$2:B$35,MATCH($K2,$A$2:$A$35,0))
=INDEX(C$2:C$35,MATCH($K2,$A$2:$A$35,0))
=INDEX(D$2:D$35,MATCH($K2,$A$2:$A$35,0))
Введите их в качестве первой записи и скопируйте / вставьте или перетащите их вниз для остальных. Обратите внимание, что они относятся к первой таблице, и единственным отличием является первый параметр. Вот как выглядит CSV, когда вы решите скрыть 2015 год.
Заказ, Срок, заказы, отгрузки
1, Р6/2014,281,74
2, Р7/2014.498.126
3, Р8/2014.589.132
4, Р9/2014.655.158
5, Р10/2014.679.161
6, Р11/2014.489.115
7, Р12/2014.574.129
8, Р13/2014,340,80
22, Р1/2016,0,0
23, Р2/2016,0,0
24, Р3/2016,0,0
25, Р4/2016,0,0
26, Р5/2016,0,0
27, Р6/2016,0,0
28, Р7/2016,0,0
29, P8/2016,0,0
30, Р9/2016,0,0
31, Р10/2016,0,0
32, Р11/2016,0,0
33, Р12/2016,0,0
34, Р13/2016,0,0
0, # N/A, # N/A, # N/A
0, # N/A, # N/A, # N/A
0, # N/A, # N/A, # N/A
0, # N/A, # N/A, # N/A
0, # N/A, # N/A, # N/A
0, # N/A, # N/A, # N/A
0, # N/A, # N/A, # N/A
0, # N/A, # N/A, # N/A
0, # N/A, # N/A, # N/A
0, # N/A, # N/A, # N/A
0, # N/A, # N/A, # N/A
0, # N/A, # N/A, # N/A
0, # N/A, # N/A, # N/A
ШАГ 5
Создайте динамический именованный диапазон для меток оси X, Series 1 и Series 2. Я использую Excel 2010, и он не позволяет вам установить весь источник данных диаграммы сразу, если это динамический именованный диапазон; он продолжает возвращаться к жестко заданному диапазону, который не обновляется. Это означает, что мы должны создать один для каждой серии и оси отдельно. Чтобы создать именованный диапазон, перейдите на ленту « Formulas и нажмите « Name Manager где-то посередине. В диалоговом окне нажмите кнопку « New в левом верхнем углу. Дайте ему имя и диапазон, к которому можно обратиться. Хитрость в том, что некоторые функции, такие как OFFSET возвращают объекты диапазона, поэтому вы можете использовать их здесь вместо чего-то вроде =A1:D35 . Вот 3 названных диапазона, которые я сделал, и их формула.
Имя: rngPeriod
Формула:
=OFFSET(Sheet1!$L$2,0,0,IFERROR(MATCH(0,Sheet1!$K:$K,0)-2,MATCH(MAX(Sheet1!$A:$A),Sheet1!$K:$K,0)-1))
Имя: rngOrders
Формула:
=OFFSET(Sheet1!$M$2,0,0,IFERROR(MATCH(0,Sheet1!$K:$K,0)-2,MATCH(MAX(Sheet1!$A:$A),Sheet1!$K:$K,0)-1))
Имя: rngShipments
Формула:
=OFFSET(Sheet1!$N$2,0,0,IFERROR(MATCH(0,Sheet1!$K:$K,0)-2,MATCH(MAX(Sheet1!$A:$A),Sheet1!$K:$K,0)-1))
Каждая формула работает одинаково. Начните с верхней части данных, которые мы хотим. Спустись один. Найдите первый 0 в столбце « Order или найдите максимальное значение, которое указывает конец данных, которые мы хотим, и сделайте диапазон достаточно высоким, чтобы охватить все от первой строки до последней строки, которую мы хотим. Ключевой момент: это вызовет ошибку, и ваш график не будет отображаться, если вы решите скрывать каждый год, поэтому каждое значение равно #N/A а самое первое значение в таблице Order равно 0 потому что тогда функция MATCH возвращает 1 и 1-2=-1 и вы не можете иметь диапазон, который равен -1 строке.
ШАГ 6
Настройте диаграмму для использования этих динамических именованных диапазонов. Используйте любое значение для Series name: но Series values: должны быть именами динамических диапазонов. Очень раздражающий ключевой момент в том, что вы должны включить имя файла. Сделайте то же самое для надписей горизонтальной (категории) оси, и все будет готово. Моя диаграмма в конечном итоге использовалась для серий 1, 2 и метки оси:
=sample.xlsx!rngOrders
=sample.xlsx!rngShipments
=sample.xlsx!rngPeriod
