I have a dataset of over 10,000 and I was wondering how to suppress the file by replacing values that are less than 10 with <10?
我有一个超过10,000的数据集,我想知道如何通过用<10替换小于10的值来压缩文件?
I tried using the IF function: =IF(G4:I22726<10, "<10")
我尝试使用IF函数:= IF(G4:I22726 <10,“<10”)
but it gave me an error.
但它给了我一个错误。
The range G4:I22726 are the columns of the the table that contains numbers.
范围G4:I22726是包含数字的表的列。
I would appreciate any help, thanks.
我要感谢任何帮助,谢谢。
3 个解决方案
#1
3
You can do this in one of two ways.
您可以通过以下两种方式之一完成此操作。
1) create a new range with a formula that computes the value you want; then copy the range, do a "paste-special-values" on top of the old data, and finally delete your temporary range; or
2) Use a small VBA macro:
1)使用计算所需值的公式创建一个新范围;然后复制范围,在旧数据之上执行“粘贴特殊值”,最后删除临时范围;或2)使用小型VBA宏:
Sub lessThan()
On Error Resume Next
For Each c in Range("G4:I22726").Cells
if c.Value < 10 Then c.Text = "<10"
Next
End Sub
Run this macro just once and you're done.
只运行一次这个宏,你就完成了。
The reason your original attempt generated an error is that your formula was referencing itself - that is what Excel calls a circular reference. A formula cannot depend on its own result, as it might never finish calculating (although in this case it would).
您的原始尝试生成错误的原因是您的公式引用了自己 - 这就是Excel调用循环引用。公式不能取决于它自己的结果,因为它可能永远不会完成计算(尽管在这种情况下它会)。
UPDATE
Showing how using a formula would work:
更新显示如何使用公式:
The formula shown in the formula bar was entered in cell C1, then dragged to D1. Finally I double-clicked the little box in the bottom right hand corner which automatically copied the formula all the way down to the bottom of the list (in this case, row 5; in your case it would copy down 22k rows).
公式栏中显示的公式输入单元格C1,然后拖动到D1。最后,我双击右下角的小方框,自动将公式一直复制到列表的底部(在这种情况下,第5行;在你的情况下,它会复制22k行)。
Then you copy the selected range, and paste-special-values it on top of cell A1 (it will overwrite everything else). After that, delete columns C and D.
然后复制选定的范围,并在单元格A1的顶部粘贴特殊值(它将覆盖其他所有内容)。之后,删除列C和D.
#2
5
Without altering the actual cell contents, the appearance of "<10" can be achieved for values under 10 with custom formatting, such as:
在不改变实际单元格内容的情况下,对于具有自定义格式的10以下的值,可以实现“<10”的外观,例如:
[<10]"<10";General
#3
2
Enter the following formula in J4:
在J4中输入以下公式:
=IF(G4<10,"<10",G4)
Copy this to the range J4:L22726 so that it mirrors the entire range you are referencing. This will create another range of cells with the result you are looking for.
将其复制到J4:L22726范围内,使其镜像您引用的整个范围。这将创建另一个单元格范围,其结果是您正在寻找的结果。
#1
3
You can do this in one of two ways.
您可以通过以下两种方式之一完成此操作。
1) create a new range with a formula that computes the value you want; then copy the range, do a "paste-special-values" on top of the old data, and finally delete your temporary range; or
2) Use a small VBA macro:
1)使用计算所需值的公式创建一个新范围;然后复制范围,在旧数据之上执行“粘贴特殊值”,最后删除临时范围;或2)使用小型VBA宏:
Sub lessThan()
On Error Resume Next
For Each c in Range("G4:I22726").Cells
if c.Value < 10 Then c.Text = "<10"
Next
End Sub
Run this macro just once and you're done.
只运行一次这个宏,你就完成了。
The reason your original attempt generated an error is that your formula was referencing itself - that is what Excel calls a circular reference. A formula cannot depend on its own result, as it might never finish calculating (although in this case it would).
您的原始尝试生成错误的原因是您的公式引用了自己 - 这就是Excel调用循环引用。公式不能取决于它自己的结果,因为它可能永远不会完成计算(尽管在这种情况下它会)。
UPDATE
Showing how using a formula would work:
更新显示如何使用公式:
The formula shown in the formula bar was entered in cell C1, then dragged to D1. Finally I double-clicked the little box in the bottom right hand corner which automatically copied the formula all the way down to the bottom of the list (in this case, row 5; in your case it would copy down 22k rows).
公式栏中显示的公式输入单元格C1,然后拖动到D1。最后,我双击右下角的小方框,自动将公式一直复制到列表的底部(在这种情况下,第5行;在你的情况下,它会复制22k行)。
Then you copy the selected range, and paste-special-values it on top of cell A1 (it will overwrite everything else). After that, delete columns C and D.
然后复制选定的范围,并在单元格A1的顶部粘贴特殊值(它将覆盖其他所有内容)。之后,删除列C和D.
#2
5
Without altering the actual cell contents, the appearance of "<10" can be achieved for values under 10 with custom formatting, such as:
在不改变实际单元格内容的情况下,对于具有自定义格式的10以下的值,可以实现“<10”的外观,例如:
[<10]"<10";General
#3
2
Enter the following formula in J4:
在J4中输入以下公式:
=IF(G4<10,"<10",G4)
Copy this to the range J4:L22726 so that it mirrors the entire range you are referencing. This will create another range of cells with the result you are looking for.
将其复制到J4:L22726范围内,使其镜像您引用的整个范围。这将创建另一个单元格范围,其结果是您正在寻找的结果。