在Excel中排序行？ [英] Sort Rows in Excel?

问题描述

我有一个excel电子表格的问题我想看看，如果我可以做没有VBA只是因为它似乎更容易实现这种方式。基本上，我想排序的表中有很多列。但是，我只想查看三列：标题列，数据列和状态列。

I have an issue with an excel spreadsheet I want to see if I can do without VBA just because it seems easier to implement that way. Basically, there are many columns in the sheet I want to sort. However, I merely want to look at three columns: the title column, the data column and the status column.

在一个新的电子表格中，将有四个部分。每个部分对应于一年的3个月（即1月，2月，3月将映射到新电子表格的第一列，4月，5月，6月将映射到新电子表格的第二列）。

In a new spreadsheet, there will be four sections. Each section corresponds to 3 months of the year (ie Jan, Feb, Mar. will map to the first column on the new spreadsheet, April, May, June will map to the second column on the new spreadsheet).

根据日期，如果状态列中的单词已完成（在原始电子表格中），我想将标题映射到新电子表格下的某列，基于日期标准，如前一段所述。例如，如果原始电子表格有以下内容：

Based on the date, and if the status column has the word "Finished" (in the original spreadsheet), I want to map the title to a certain column under the new spreadsheet based on the date criteria as described in the previous paragraph. So for example, if the original spreadsheet has following:

Title Date Status

Doc1 1/12/13 Finished
Doc2 2/10/13 UnFinished
Doc3 4/1/13  Finished
Doc4 3/31/13 Finished

将映射到新的电子表格：

Would map to, on the new spreadsheet:

1st Column | 2nd Column
Doc1         Doc3 
Doc4

我已经看到了很多数据透视表但我不能像我想要的那样自动化。我已经把它归结为可以将数据透视表更改为基于日期的过滤，但是我希望它更自动化。我也试过excel公式，但这是没有用的。感谢您的帮助，我真的很感激！

I have looked a lot into pivot tables but I can't "automate it" as much as I want to. I have gotten it down to the point where I can change the pivot tables into filtering based on date, but I want it even more automated than that. I've also tried excel formulas but that has been to no avail. Thanks for the help, I really appreciate it!

推荐答案

使用数据透视表，似乎很容易自动化 2 如下：

With a PivotTable it seems fairly easy to 'automate' as far as Sheet 2 as below:

但是从那里到所要求的结果是相对手动没有VBA，所以可能不适合。

but from there to the result requested is relatively 'manual' without VBA, so may not suit.

为了方便起见，我已经更改了日期格式。数据透视表按通常/指示构建，不显示行或列（数据透视表选项，总计和过滤器）的总计。列标签为日期，具有适当的分组，开始于：（组）和折叠整个字段（展开/折叠）。

For my convenience I have changed the date formats. The PivotTable is constructed as usual/indicated without showing grand totals for rows or for columns (PivotTable Options, Totals & Filters). The Column Labels are Date with Grouping By Quarters with appropriate Starting at: and Ending at: (Group) and Collapse Entire Field (Expand/Collapse).

I6 中的公式是将文档计数（始终为1）转换为文档名称：

The formula in I6 is to convert the document count (always 1) to document name:

= IF（F6 = 1，$ E6，）

=IF(F6=1,$E6,"")

但是，为了在数据透视表中允许额外的空间，公式应该移动到右边。公式需要根据需要进行复制。

However, to allow room for additional quarters in the PivotTable the formula should be moved to the right. The formula would need to be copied across and down as necessary.

该过程变得更加手动，复制这些公式的结果，将它们（使用特殊/值）粘贴到新位置（在示例2！A1中）如果需要，删除空白。

The process becomes more ‘manual’ with copying the results of these formulae, pasting them (with Special / Values) into a new location (in the example 2!A1) and, if required, deleting blanks.

这篇关于在Excel中排序行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！