使用公式更新单元格，但将其作为值 [英] Use formula to update cells, but as values

问题描述

我一直在处理Excel中的大量数据，这些数据使用宏从两个不同的源电子表格中填充公式.问题在于，公式太多会导致电子表格的爬网速度变慢.互联网搜索使我相信我需要将数据设置为值.我已经测试了一种吸引我眼球的方法，但无法使其按我需要的方式发挥作用.

I've been working with a large set of data in Excel that I use macros to populate formulas in from two different source spreadsheets. The problem is that having so many formulas is causing the spreadsheet to slow to a crawl. Internet searches have led me to believe that I need to set my data as values. I've tested one method that caught my eye, but can't get it to work the way I need it to.

Range("D1:D10").Formula = "=SUM(A1:A10)"
Range("E1:E10").Formula = "=SUM($A$1:$A$10)"
Range("F1:F10").Formula = Evaluate("=SUM(A1:A10)")
Range("G1:G10").Formula = Evaluate("=SUM($A$1:$A$10)")
Range("H1:H10").Value = Evaluate("=SUM(A1:A10)")
Range("I1:I10").Value = Evaluate("=SUM($A$1:$A$10)")
Range("J1:J10").Value = "=SUM(A1:A10)"
Range("K1:K10").Value = "=SUM($A$1:$A$10)"

我刚刚在A1到A10中输入了随机数.通常，我将使用类似将根据我需要如何使用公式来更新我的D和E范围的代码.在公式中使用Evaluate()可以实现在每个单元格而不是公式中添加值的功能，但是它不执行的操作是像D范围公式那样更改要从中提取的范围.如果我使用Evaluate()，它将仅使用其包含的确切公式，基本上使其与绝对引用没有区别.我需要在输入值时灵活地做这两种事情.

I just entered random numbers in A1 through A10. Normally, I'll use code like the one that would update my D and E range depending on how I need to use the formula. Using Evaluate() around my formula does what I want with regards to putting a value in each cell rather than the formula, but what it doesn't do is change the range I'm wanting to pull from like the D range formula will. If I use Evaluate(), it will only use the exact formula it contains, basically making it no different than an absolute reference. I need the flexibility to do either while putting in values.

我知道我可以实现目标的一种方法是简单地使用更新的公式复制我的范围，然后粘贴到它们上，但是我希望保持这一步.

I know a way I could achieve my goal is to simply copy my range with the updated formulas and then paste over them, but I was hoping to keep this a single step.

我看到的另一个选项是 Range("C1").Value = Range("A1").Value ，但是我不确定如何将其与VLOOKUP或INDEX一起使用&比赛.我将来自两个不同工作表的一组参考数据放在主工作表上，然后删除重复项.从那里开始，我正在使用公式在其他工作表上查找不同的信息.某些信息可能是两个表中的一个，但不是同时存在的，所以我必须使用IFERROR.

One other option I saw was Range("C1").Value = Range("A1").Value, but I'm not sure how to use this with a VLOOKUP or an INDEX & MATCH. I'm putting one set of reference data from two different sheets on a master sheet and then removing the duplicates. From there, I'm using formulas to find the different pieces of information on the other sheets. Some of the information might be one both sheets, but not at the same time, so I have to use IFERROR.

我想我正在尝试找到一种通用的方法来使用.如果您认为有一个更好的选择不能解决我的特定问题，但是解决了避免冻结如此大量数据的最终问题，那么我欢迎您提出建议.

I guess I'm trying to find a versatile method to use. If you think you have a better option that doesn't answer my specific question, but resolves my ultimate issue of avoiding freezing with this large amount of data, I'm open to suggestions.

推荐答案

将相对寻址的公式放入范围内，然后恢复为从公式返回的值.

Put the relative addressed formulas into the range then revert to the values returned from the formulas.

with Range("D1:D10")
    .Formula = "=SUM(A1:A10)"
    .value = .value
end with

尽管目前我没有在线白皮书可链接到以证明以下主张是成立的，但我将限制使用Evaluate.我从来没有发现它是一种有效的编码方法，而且我个人仅将其用作最后的手段.

While I have no online whitepaper to link to at this moment to justify the following assertion, I would restrict the use of Evaluate. I've never found it to be an efficient method of coding and personally only use it as a last resort.

这篇关于使用公式更新单元格，但将其作为值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！