检查单元格内部颜色与其值之间的性能差异 [英] Performance difference between checking a cell's interior colour vs. its value
问题描述
我正在Excel中运行资产管理项目.使用与SQL数据库的连接，我可以导入大量的维护计划.
I am running an asset management project in excel. Using a connection with an SQL database I am able to import large numbers of maintenance plans.
根据输入的内容，我用特定的短字符串填充了多个单元格，并另外用两种颜色之一绘制了各个单元格的内部.稍后由用户填充的单元将保持未绘制状态.
Based on what I import, I fill a number of cells with specific short strings, and I additionally paint the interior of the respective cells with one of two colours. Cells that later are populated by the user remain unpainted.
使用数据库数据填充的单元格始终为彩色
当我稍后运行再次填充大量单元格的命令时，其中一些单元格可能已经由用户或数据库填充.必须跳过这些特定的单元格，到目前为止，我知道确定是否必须跳过单元格的三种可能的方法:
When I later run a command that populates a large number of cells again, some of these cells may already be populated either by the user or the database. These specific cells must be skipped, and so far I am aware of three possible methods of determining if a cell must be skipped:
- 检查数据库(速度较慢):解决方案不完善，因为单元格已经用数据库填充，所以另一次运行会覆盖数据库
- 检查是否已填充单元格:
If not cell.Value = vbNullstring Then - 检查单元格是否着色:
If not cell.Interior.Color = vbRed Then
- Checking with the database (slow): a poor solution since the cells have already been populated with the database, another run would be overdoing it
- Checking wether the cell is populated:
If not cell.Value = vbNullstring Then - Checking whether a cell is coloured:
If not cell.Interior.Color = vbRed Then
现在，因为从理论上讲，填充单元的数量可能高达50万甚至更多，所以我想知道 second 和第三选项
Now, because in theory the amount of populated cells could be up to half a million or even more, I am wondering about the performance differences between the second and third option
检查单元格的值与单元格的内部颜色之间是否有明显的区别?
推荐答案
似乎确实存在性能差异.我刚刚检查了一个单元格的值和一个单元格的内部颜色一亿次，但有明显的区别:
There seems to be a performance difference indeed. I just checked a cell's value and a cell's interior colour for one hundred million times and there are clear differences:
Checking the value : 456 seconds
Checking the colour: 1281 seconds
换句话说:检查值的速度提高了±2.8倍(遵循这个简单的实验).
In other words: checking the values goes ±2.8 times faster (following this single simple experiment).
这篇关于检查单元格内部颜色与其值之间的性能差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
