I am facing some difficulties with plotting grouped data (by index) in one graph (scatter plot with lines) in Excel, and I will appreciate a lot your help.
我在Excel中绘制分组数据(通过索引)绘制一个图表(带有线条的散点图)时遇到了一些困难,我将非常感谢您的帮助。
My data are in three columns: The first column is the index of the data or the group (i.e. a unique number for every set of data) the second column is the time and the third column is the data
我的数据分为三列:第一列是数据或组的索引(即每组数据的唯一编号),第二列是时间,第三列是数据
Group, Time, Data
1 1 12
1 3 12
1 4 28
1 8 56
1 12 37
1 24 40
1 48 34
2 0 7
2 1 14
2 4 6
2 8 63
2 12 4
2 24 35
2 48 3
und so on.
等等。
and I want to plot the data vs. time for each index i.e. data group alone, but on the same graph.
我想绘制每个索引的数据与时间,即单独的数据组,但在同一图表上。
Until now, I was always doing it manually by adding each data set separately to the graph. But I think there should be a more clever and easier way to do it, especially that sometimes I have a lot of data (index number can reach 70 or 80).
到目前为止,我总是通过将每个数据集分别添加到图表中来手动完成。但我认为应该有一种更聪明,更简单的方法,尤其是有时候我会有很多数据(索引号可以达到70或80)。
Thanks a lot in advance.
非常感谢提前。
1 个解决方案
#1
1
You can create a pivot table on all your data. Use 'Group' as column headers and 'Time' as row headers. The resulting pivot table will have all time points from all groups as rows and your groups as columns. Each columns of course has entries only at these time points which are included in its group. The other cells are empty. If you just select the data range of this pivot table without column headers, you can get charts from the data as a plot chart omits empty cells.
您可以在所有数据上创建数据透视表。使用“组”作为列标题,使用“时间”作为行标题。生成的数据透视表将包含所有组中的所有时间点作为行,将您的组作为列。当然,每列只有在这些时间点才有条目,这些时间点都包含在其组中。其他单元格是空的。如果您只选择此数据透视表的数据范围而没有列标题,则可以从数据中获取图表,因为绘图图表省略了空单元格。
Update
That is the result pivot table of your test data. The sorted data are in the red frame. (Forget the total results)
这是测试数据的结果数据透视表。已排序的数据位于红框中。 (忘记总结果)
#1
1
You can create a pivot table on all your data. Use 'Group' as column headers and 'Time' as row headers. The resulting pivot table will have all time points from all groups as rows and your groups as columns. Each columns of course has entries only at these time points which are included in its group. The other cells are empty. If you just select the data range of this pivot table without column headers, you can get charts from the data as a plot chart omits empty cells.
您可以在所有数据上创建数据透视表。使用“组”作为列标题,使用“时间”作为行标题。生成的数据透视表将包含所有组中的所有时间点作为行,将您的组作为列。当然,每列只有在这些时间点才有条目,这些时间点都包含在其组中。其他单元格是空的。如果您只选择此数据透视表的数据范围而没有列标题,则可以从数据中获取图表,因为绘图图表省略了空单元格。
Update
That is the result pivot table of your test data. The sorted data are in the red frame. (Forget the total results)
这是测试数据的结果数据透视表。已排序的数据位于红框中。 (忘记总结果)