I'm wanting to display a pivot table and for it to show me the actual values, one on each row, rather than a sum of the values. E.g.
我想要显示一个pivot表，并让它显示每个行的实际值，而不是值的和。如。
Name Jan Feb Mar Apr
Bob 12 10 4
3 5
James 2 6 8 1
15
etc.
等。
My starting point is having three columns: Name, Value and Month.
我的出发点是有三列:名称、值和月。
Is this possible without having to do something completely different?
如果不做完全不同的事情，这可能吗?
2 个解决方案
#1
11
I fear this might turn out to BE the long way round but could depend on how big your data set is – presumably more than four months for example.
我担心这可能会是一个漫长的过程，但这可能取决于你的数据集有多大——比方说，可能超过四个月。
Assuming your data is in ColumnA:C and has column labels in Row 1, also that Month is formatted mmm(this last for ease of sorting):
假设您的数据在ColumnA:C中，并且在第1行中有列标签，那么这个月的格式也是mmm(这是为了便于排序):
- Sort the data by Name then Month
- 按名称和月份对数据进行排序
- Enter in
D2=IF(AND(A2=A1,C2=C1),D1+1,1)(One way to deal with what is the tricky issue of multiple entries for the same person for the same month). - 输入D2 =IF(和(A2=A1,C2=C1)，D1+1,1)(处理同一个人在同一月内多个条目的棘手问题的一种方法)。
- Create a pivot table from
A1:D(last occupied row no.) - 从A1:D(最后占用的行号)创建一个pivot表。
- Say insert in
F1. - 说插入在F1。
- Layout as in screenshot.
- 布局如截图。
I’m hoping this would be adequate for your needs because pivot table should automatically update (provided range is appropriate) in response to additional data with refresh. If not (you hard taskmaster), continue but beware that the following steps would need to be repeated each time the source data changes.
我希望这能满足您的需求，因为pivot表应该自动更新(提供的范围是适当的)，以响应使用refresh的附加数据。如果没有(您的硬任务管理器)，请继续，但请注意，在每次源数据更改时，需要重复以下步骤。
- Copy pivot table and Paste Special/Values to, say,
L1. - 复制透视表并粘贴特殊/值到，比如说，L1。
- Delete top row of copied range with shift cells up.
- 删除顶部行复制范围与移位单元格向上。
- Insert new cell at
L1and shift down. - 在L1位置插入新的单元格并向下移动。
- Key 'Name' into
L1. - 关键的“名字”到L1。
- Filter copied range and for
ColumnL, selectRow Labelsand numeric values. - 筛选复制的范围，对于ColumnL，选择行标签和数值。
- Delete contents of
L2:L(last selected cell) - 删除L2:L(最后选中的单元格)的内容
- Delete blank rows in copied range with shift cells up (may best via adding a column that counts all 12 months). Hopefully result should be as highlighted in yellow.
- 删除复制范围内的空白行并向上移动单元格(最好添加一个12个月的列)。希望结果用黄色突出显示。
Happy to explain further/try again (I've not really tested this) if does not suit.
如果不适合的话，我很乐意进一步解释/再试一次(我还没有真正测试过这个)。
EDIT (To avoid second block of steps above and facilitate updating for source data changes)
编辑(避免以上步骤的第二个块，并促进源数据更改的更新)
.0. Before first step 2. add a blank row at the very top and move A2:D2 up.
.2. Adjust cell references accordingly (in D3 =IF(AND(A3=A2,C3=C2),D2+1,1).
.3. Create pivot table from A:D
0。前2的第一步。在顶部添加一个空行，然后移动A2:D2。相应地调整单元格引用(在D3 =IF(和(A3=A2,C3=C2)，D2+1,1)。从A:D创建数据透视表
.6. Overwrite Row Labels with Name.
.7. PivotTable Tools, Design, Report Layout, Show in Tabular Form and sort rows and columns A>Z.
.8. Hide Row1, ColumnG and rows and columns that show (blank).
。6。用名称覆盖行标签。数据透视表工具，设计，报表布局，以表格形式显示，对行和列进行排序。隐藏行1、列和显示(空白)的行和列。
Steps .0. and .2. in the edit are not required if the pivot table is in a different sheet from the source data (recommended).
步骤0。和2。如果pivot表与源数据(推荐)位于不同的表中，则不需要进行编辑。
Step .3. in the edit is a change to simplify the consequences of expanding the source data set. However introduces (blank) into pivot table that if to be hidden may need adjustment on refresh. So may be better to adjust source data range each time that changes instead: PivotTable Tools, Options, Change Data Source, Change Data Source, Select a table or range). In which case copy rather than move in .0.
一步。3。在编辑过程中，更改是为了简化源数据集的扩展结果。但是，将(空白)引入到pivot表中，如果要隐藏，可能需要在刷新上进行调整。因此，最好每次更改时都调整源数据范围:数据透视表工具、选项、更改数据源、更改数据源、选择表或范围)。在这种情况下，复制而不是移动。0。
#2
0
Another easier way to do it is to upload your file to google sheets, then add a pivot, for the columns and rows select the same as you would with Excel, however, for values select Calculated Field and then in the formula type in =
另一种更简单的方法是将文件上载到谷歌表中，然后添加一个pivot，以便列和行可以选择与Excel相同的值，然后选择计算字段，然后使用公式类型in =
#1
11
I fear this might turn out to BE the long way round but could depend on how big your data set is – presumably more than four months for example.
我担心这可能会是一个漫长的过程，但这可能取决于你的数据集有多大——比方说，可能超过四个月。
Assuming your data is in ColumnA:C and has column labels in Row 1, also that Month is formatted mmm(this last for ease of sorting):
假设您的数据在ColumnA:C中，并且在第1行中有列标签，那么这个月的格式也是mmm(这是为了便于排序):
- Sort the data by Name then Month
- 按名称和月份对数据进行排序
- Enter in
D2=IF(AND(A2=A1,C2=C1),D1+1,1)(One way to deal with what is the tricky issue of multiple entries for the same person for the same month). - 输入D2 =IF(和(A2=A1,C2=C1)，D1+1,1)(处理同一个人在同一月内多个条目的棘手问题的一种方法)。
- Create a pivot table from
A1:D(last occupied row no.) - 从A1:D(最后占用的行号)创建一个pivot表。
- Say insert in
F1. - 说插入在F1。
- Layout as in screenshot.
- 布局如截图。
I’m hoping this would be adequate for your needs because pivot table should automatically update (provided range is appropriate) in response to additional data with refresh. If not (you hard taskmaster), continue but beware that the following steps would need to be repeated each time the source data changes.
我希望这能满足您的需求，因为pivot表应该自动更新(提供的范围是适当的)，以响应使用refresh的附加数据。如果没有(您的硬任务管理器)，请继续，但请注意，在每次源数据更改时，需要重复以下步骤。
- Copy pivot table and Paste Special/Values to, say,
L1. - 复制透视表并粘贴特殊/值到，比如说，L1。
- Delete top row of copied range with shift cells up.
- 删除顶部行复制范围与移位单元格向上。
- Insert new cell at
L1and shift down. - 在L1位置插入新的单元格并向下移动。
- Key 'Name' into
L1. - 关键的“名字”到L1。
- Filter copied range and for
ColumnL, selectRow Labelsand numeric values. - 筛选复制的范围，对于ColumnL，选择行标签和数值。
- Delete contents of
L2:L(last selected cell) - 删除L2:L(最后选中的单元格)的内容
- Delete blank rows in copied range with shift cells up (may best via adding a column that counts all 12 months). Hopefully result should be as highlighted in yellow.
- 删除复制范围内的空白行并向上移动单元格(最好添加一个12个月的列)。希望结果用黄色突出显示。
Happy to explain further/try again (I've not really tested this) if does not suit.
如果不适合的话，我很乐意进一步解释/再试一次(我还没有真正测试过这个)。
EDIT (To avoid second block of steps above and facilitate updating for source data changes)
编辑(避免以上步骤的第二个块，并促进源数据更改的更新)
.0. Before first step 2. add a blank row at the very top and move A2:D2 up.
.2. Adjust cell references accordingly (in D3 =IF(AND(A3=A2,C3=C2),D2+1,1).
.3. Create pivot table from A:D
0。前2的第一步。在顶部添加一个空行，然后移动A2:D2。相应地调整单元格引用(在D3 =IF(和(A3=A2,C3=C2)，D2+1,1)。从A:D创建数据透视表
.6. Overwrite Row Labels with Name.
.7. PivotTable Tools, Design, Report Layout, Show in Tabular Form and sort rows and columns A>Z.
.8. Hide Row1, ColumnG and rows and columns that show (blank).
。6。用名称覆盖行标签。数据透视表工具，设计，报表布局，以表格形式显示，对行和列进行排序。隐藏行1、列和显示(空白)的行和列。
Steps .0. and .2. in the edit are not required if the pivot table is in a different sheet from the source data (recommended).
步骤0。和2。如果pivot表与源数据(推荐)位于不同的表中，则不需要进行编辑。
Step .3. in the edit is a change to simplify the consequences of expanding the source data set. However introduces (blank) into pivot table that if to be hidden may need adjustment on refresh. So may be better to adjust source data range each time that changes instead: PivotTable Tools, Options, Change Data Source, Change Data Source, Select a table or range). In which case copy rather than move in .0.
一步。3。在编辑过程中，更改是为了简化源数据集的扩展结果。但是，将(空白)引入到pivot表中，如果要隐藏，可能需要在刷新上进行调整。因此，最好每次更改时都调整源数据范围:数据透视表工具、选项、更改数据源、更改数据源、选择表或范围)。在这种情况下，复制而不是移动。0。
#2
0
Another easier way to do it is to upload your file to google sheets, then add a pivot, for the columns and rows select the same as you would with Excel, however, for values select Calculated Field and then in the formula type in =
另一种更简单的方法是将文件上载到谷歌表中，然后添加一个pivot，以便列和行可以选择与Excel相同的值，然后选择计算字段，然后使用公式类型in =