In Excel 2013, I have a series of data that looks like this...
在Excel 2013中,我有一系列看起来像这样的数据......
1, #N/A, 2, 3, 4
1,#N / A,2,3,4
(N.B. The #N/A was generated using =NA() ).
(N.B.使用= NA()生成#N / A)。
I wish to make a line graph of this data, and have the #N/A show as a gap in the graph.
我希望制作这些数据的折线图,并将#N / A显示为图中的间隙。
The standard advice for this is to use the 'Hidden and Empty Cells' options (right click on graph > Select Data... > Hidden and Empty Cells) to show empty cells as Gaps. However, this has no impact for me, the line graph is displayed in the same was as if the 'Connect data points with line' option was selected.
对此的标准建议是使用“隐藏和空单元格”选项(右键单击图形>选择数据...>隐藏和空单元格)将空单元格显示为间隙。但是,这对我没有影响,线图显示的方式与选择“使用线连接数据点”选项相同。
I've uploaded the example I'm referring to here to make this easy to see: http://www.filedropper.com/linegraphchartexample-gapsissue
我已经上传了我在这里提到的示例,以便于查看:http://www.filedropper.com/linegraphchartexample-gapsissue
I know that these gaps can be displayed, as I've seen it working on other line graphs in Excel (including graphs where #N/A was the value to be skipped over), I just haven't been able to replicate it. Can anyone help me?
我知道可以显示这些间隙,因为我已经看到它在Excel中的其他线图上工作(包括#N / A是要跳过的值的图形),我只是无法复制它。谁能帮我?
2 个解决方案
#1
0
If anyone reads this question, I found a solution which relies on using formulas to split the data into multiple data series, it's a pain to setup but it appears to be the least problematic way that Excel offers:
如果有人读到这个问题,我找到了一个依赖于使用公式将数据拆分成多个数据系列的解决方案,这很难设置,但它似乎是Excel提供的问题最少的方式:
The simple summary is that you use formulas to create a new data series every time there is an #N/A value in the source data, then plot all the resulting data series, and format them so that they look the same.
简单的总结是,每当源数据中存在#N / A值时,您使用公式创建新数据系列,然后绘制所有结果数据系列,并对它们进行格式化以使它们看起来相同。
#2
0
Another option is to cheat by adding a second data series:
另一个选择是通过添加第二个数据系列作弊:
I have added a row to your data table which duplicates the data on row 2 if the cells to the left or the right contain #N/A.
我在数据表中添加了一行,如果左侧或右侧的单元格包含#N / A,则会复制第2行的数据。
B3=IF(OR(ISNA(A2),ISNA(B2),ISNA(C2)),B2,"")
The second data series is formatted with the same markers as the original line, but with a white line which obscures the blue one (as long as you've got your data series in the correct order).
第二个数据系列使用与原始行相同的标记进行格式化,但使用白线遮盖蓝色数据(只要您的数据系列按正确顺序排列)。
#1
0
If anyone reads this question, I found a solution which relies on using formulas to split the data into multiple data series, it's a pain to setup but it appears to be the least problematic way that Excel offers:
如果有人读到这个问题,我找到了一个依赖于使用公式将数据拆分成多个数据系列的解决方案,这很难设置,但它似乎是Excel提供的问题最少的方式:
The simple summary is that you use formulas to create a new data series every time there is an #N/A value in the source data, then plot all the resulting data series, and format them so that they look the same.
简单的总结是,每当源数据中存在#N / A值时,您使用公式创建新数据系列,然后绘制所有结果数据系列,并对它们进行格式化以使它们看起来相同。
#2
0
Another option is to cheat by adding a second data series:
另一个选择是通过添加第二个数据系列作弊:
I have added a row to your data table which duplicates the data on row 2 if the cells to the left or the right contain #N/A.
我在数据表中添加了一行,如果左侧或右侧的单元格包含#N / A,则会复制第2行的数据。
B3=IF(OR(ISNA(A2),ISNA(B2),ISNA(C2)),B2,"")
The second data series is formatted with the same markers as the original line, but with a white line which obscures the blue one (as long as you've got your data series in the correct order).
第二个数据系列使用与原始行相同的标记进行格式化,但使用白线遮盖蓝色数据(只要您的数据系列按正确顺序排列)。