vba 在 excel 2007 中显示、排序和过滤带有多个小数的数字
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16336302/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Display, sort and filter numbers with multiple decimal in excel 2007
提问by Kapil Sharma
I'm using excel 2007.
我正在使用 excel 2007。
I've a list of tasks (200-500) that I need to group in different category/section etc (multiple filters). Whole data is in excel table so I can apply Excel's build-in table filters to display exact data that I need.
我有一个任务列表（200-500），我需要在不同的类别/部分等（多个过滤器）中进行分组。整个数据都在 excel 表格中，所以我可以应用 Excel 的内置表格过滤器来显示我需要的确切数据。
However it is always difficult to apply multiple filter to display expected data, specially as I need to do it very frequently. To make things simple I'm planning to number each record like
然而，应用多个过滤器来显示预期数据总是很困难，特别是因为我需要非常频繁地这样做。为了简单起见，我打算给每条记录编号，例如
a.b.c.d.e.f
Where a, b, c, d, e, f are simple numbers. List looks like:
1
1.1
1.2
1.2.1
1.2.1.1
1.2.2
1.3
& so on.
Problem is, Excel take it as number with single decimal but as soon as I add second decimal, excel treat it as text, which is obvious in general behavior.
问题是，Excel 将其作为带有单个小数的数字，但是一旦我添加第二个小数，excel 将其视为文本，这在一般行为中很明显。
However, as special case, I need excel treat both as number or text. Number is preferable as I want to sort them, which might be difficult as a text.
但是，作为特殊情况，我需要 excel 将两者都视为数字或文本。Number 更可取，因为我想对它们进行排序，这对于文本来说可能很困难。
To make the things little more complex, while filtering in table, I require if I can add some formula to filter results like 1.*should display all numbers starts with 1.
为了使事情变得更复杂，在表格中过滤时，我需要是否可以添加一些公式来过滤结果，例如1.*应该显示所有数字开头1.
Is it possible with excel's default behavior, without VBA?
在没有 VBA 的情况下，是否可以使用 excel 的默认行为？
If no, is it possible with VBA? If yes, any clue is appreciated. I don't need whole program as I can write basic VBA program, just a clue how it can be done?
如果没有，是否可以使用 VBA？如果是，任何线索表示赞赏。我不需要整个程序，因为我可以编写基本的 VBA 程序，只是一个线索如何完成？
采纳答案by glh
I sort mine by adding a helper column that adds a letter to the front and sort on that. E.g. 1 becomes f1, 1.1 becomes f1.1 etc. Then all are sorted as text.
我通过添加一个辅助列来对我的进行排序，该列在前面添加一个字母并对其进行排序。例如 1 变成 f1，1.1 变成 f1.1 等等。然后所有都被排序为文本。
You can use the formula ="f" & A1.
您可以使用公式="f" & A1。
My sample:
我的样本：
Then the data sorted:
然后数据排序：
And the filter:
和过滤器：
回答by B-Rell
If I were to try this without VBA, my first step would be to use the sort to columns function on the data tab.
如果我在没有 VBA 的情况下尝试此操作，我的第一步将是使用数据选项卡上的列排序功能。
Next make sure all empty spaces in your data are filled with zeros.
接下来确保数据中的所有空格都用零填充。
Then sort the data by column
然后按列对数据进行排序
as long as you left your original data in the same row as the sorted data (I didn't in the images posted to focus on the process), your items should now be in order.
只要您将原始数据与排序数据放在同一行（我没有在发布的图像中关注该过程），您的项目现在应该是有序的。
