excel - 如何根据特定条件将行数据转换为列数据

Question

我有一个Excel，它具有按行排列的考试数据（理论，会话，实践等）。我必须将基于QPCODE的相同数据放在单行中。
我的数据是-

 +-------+--------+--------------------+--------------+---------------------------+--------------------+--------------------------+---------------------+-----------+-----------+
    |  id   | qpcode | subject_paper_code | subject_code |       subject_name        | subject_paper_name | subject_paper_short_code | subject_paper_group | min_marks | max_marks |
    +-------+--------+--------------------+--------------+---------------------------+--------------------+--------------------------+---------------------+-----------+-----------+
    | 37790 |  10032 |                  0 | A47          | GEOGRAPHY                 | THEORY             | GEOG1                    | A                   |        21 |        60 |
    | 37791 |        |                  1 | A47          | GEOGRAPHY                 | I.A.(THEORY)       | GE1IA                    | A                   |         0 |        10 |
    | 37792 |        |                  2 | A47          | GEOGRAPHY                 | PRACTICAL          | GE1PR                    | B                   |         9 |        20 |
    | 37793 |        |                  3 | A47          | GEOGRAPHY                 | RECORD             | GE1RC                    | B                   |         0 |        10 |
    | 37794 |  10033 |                  0 | A50          | HINDI (OPT)               | THEORY             | HINO1                    | A                   |        40 |        80 |
    | 37795 |        |                  1 | A50          | HINDI (OPT)               | I.A.(THEORY)       | HI1IA                    | A                   |         0 |        20 |
    | 37796 |  10034 |                  0 | A51          | HISTORY(PRIOR TO 2008-09) | THEORY             | HIST1                    | A                   |        40 |        80 |
    +-------+--------+--------------------+--------------+---------------------------+--------------------+--------------------------+---------------------+-----------+-----------+

我必须将相同的qpcode数据放在单行中，这样我才能为所有qpcodes在同一行中获得相同的qpcode数据。

+-------+--------+--------------------+--------------+--------------+--------------------+--------------------------+---------------------+-----------+-----------+---------------------------+------------+------------+---------------------------+------------+------------+---------------------------+------------+------------+
|  id   | qpcode | subject_paper_code | subject_code | subject_name | subject_paper_name | subject_paper_short_code | subject_paper_group | min_marks | max_marks | subject_paper_short_code2 | min_marks2 | max_marks2 | subject_paper_short_code3 | min_marks3 | max_marks3 | subject_paper_short_code4 | min_marks4 | max_marks4 |
+-------+--------+--------------------+--------------+--------------+--------------------+--------------------------+---------------------+-----------+-----------+---------------------------+------------+------------+---------------------------+------------+------------+---------------------------+------------+------------+
| 37790 |  10032 |                  0 | A47          | GEOGRAPHY    | THEORY             | GEOG1                    | A                   |        21 |        60 | GE1IA                     |          0 |         10 | GE1PR                     |          9 |         20 | GE1RC                     |          0 |         10 |
+-------+--------+--------------------+--------------+--------------+--------------------+--------------------------+---------------------+-----------+-----------+---------------------------+------------+------------+---------------------------+------------+------------+---------------------------+------------+------------+

Answer 1

使用VBA进行此操作更为简单。但我想看看是否可以使用数据透视表完成。所以这是我遵循的四个步骤。

步骤1：清除资料


将数据复制到新的工作表。
有一些不必要的列。删除它们：


ID
subject_paper_code
subject_code
subject_name
subject_paper_name
subject_paper_group



它们在摘要中没有任何作用，因为与特定记录有关，因此是不合适的。但是，如果确实需要这些，则可以使用简单的VLOOKUP来获取。

删除这些列后，您将剩下四个。假设这些包含在A:D列中。


您将需要一个帮助程序列来替换原始的qpcode列，以便试卷代码是连续的，并且它们之间没有任何空白单元格。


为此，您可以在A列的左侧插入一个列（包含qpcode），将此公式粘贴到A2并将其向下拖动：

=IF(ISBLANK(B2),A1,B2)

这将使qpcode对每个记录重复。


复制该列并将其粘贴为值。删除原始的 qpcode列（B列），其中每一记录集仅包含一个代码。


您的工作表应如下所示：

╔════════╦══════════════════════════╦═══════════╦═══════════╗
║ qpcode ║ subject_paper_short_code ║ min_marks ║ max_marks ║
╠════════╬══════════════════════════╬═══════════╬═══════════╣
║  10032 ║ GEOG1                    ║        21 ║        60 ║
║  10032 ║ GE1IA                    ║         0 ║        10 ║
║  10032 ║ GE1PR                    ║         9 ║        20 ║
║  10032 ║ GE1RC                    ║         0 ║        10 ║
║  10033 ║ HINO1                    ║        40 ║        80 ║
║  10033 ║ HI1IA                    ║         0 ║        20 ║
║  10034 ║ HIST1                    ║        40 ║        80 ║
╚════════╩══════════════════════════╩═══════════╩═══════════╝

步骤2：创建数据透视表


创建数据透视表
将 qpcode拖动到“行”区域
依次将这些字段一一拖到“列”区域中


subject_paper_short_code
min_marks
max_marks

在上一步中放置在“列”区域中的所有三个字段上使用以下设置


点击字段；选择“字段设置”
在 None选项卡中选择 Subtotals & Filters
转到 Layout & Print选项卡并检查 Repeat item labels选项



注意：仅对于“列”区域中的前两个项目，才需要应用上一步中的设置。将它们应用于第三个（ max_marks）没什么区别。


将它们也拖到“值”区域（这些与您在上一步中的“列”区域中放置的相同）：


subject_paper_short_code
min_marks
max_marks

在“汇总值字段依据”设置中选择“计数”选项。这样做仅是为了使清洁部分最后容易一些。


步骤3：处理结果


复制数据透视表，并在新工作表中，使用“选择性粘贴”选项中的“粘贴为值”将其粘贴
从数据透视表的副本中复制“列”和“行”标签，并将其粘贴在下面。这是一个截图，可以使它更清晰：




我已经加粗了列标题。


将此公式粘贴到 B10中，并将其复制到整个范围（在本例中为 B10:V12）：

=IF(NOT(ISBLANK(B5)),INDIRECT(ADDRESS(MOD(COLUMN()-1,3)+3*(MOD(COLUMN()-1,3)=0),COLUMN())))

保持此范围处于选中状态，以供后续说明使用。


再次复制该范围，并将其粘贴为值（在同一位置）。
在仍然选择范围的情况下，按Ctrl + H（“替换”对话框），然后将 FALSE替换为空白。
按F5，然后选择 Goto Special。然后，选择 Blank单元格选项，然后按Enter。现在，将仅选择该范围内的空白单元格。
按Ctrl +-，然后从弹出的对话框中选择 Shift cells left。


步骤4：清理


注意列标题​​前面有“ Count of”。选择该行，然后将 Count of（包括尾随空格）替换为空白。
将 Row Label替换为 qpcode。
最后还有几列。也删除它们。


而已。那应该为您留下所需的数据。

这是您提供的数据的输出：

╔════════╦══════════════════════════╦═══════════╦═══════════╦══════════════════════════╦═══════════╦═══════════╦══════════════════════════╦═══════════╦═══════════╦══════════════════════════╦═══════════╦═══════════╗
║ qpcode ║ subject_paper_short_code ║ min_marks ║ max_marks ║ subject_paper_short_code ║ min_marks ║ max_marks ║ subject_paper_short_code ║ min_marks ║ max_marks ║ subject_paper_short_code ║ min_marks ║ max_marks ║
╠════════╬══════════════════════════╬═══════════╬═══════════╬══════════════════════════╬═══════════╬═══════════╬══════════════════════════╬═══════════╬═══════════╬══════════════════════════╬═══════════╬═══════════╣
║  10032 ║ GE1IA                    ║         0 ║        10 ║ GE1PR                    ║         9 ║        20 ║ GE1RC                    ║         0 ║        10 ║ GEOG1                    ║        21 ║        60 ║
║  10033 ║ HI1IA                    ║         0 ║        20 ║ HINO1                    ║        40 ║        80 ║                          ║           ║           ║                          ║           ║           ║
║  10034 ║ HIST1                    ║        40 ║        80 ║                          ║           ║           ║                          ║           ║           ║                          ║           ║           ║
╚════════╩══════════════════════════╩═══════════╩═══════════╩══════════════════════════╩═══════════╩═══════════╩══════════════════════════╩═══════════╩═══════════╩══════════════════════════╩═══════════╩═══════════╝