Excel：使用工作表作为函数？ [英] Excel: using a worksheet as a function?

问题描述

我有一个excel工作表，需要两个输入并生成一个输出。

我可以打开工作表，将这两个输入到单元格A1和A2中，结果显示在A3中。

有没有办法让我们进入一个函数或子程序，这样我可以在另一个工作表中使用它来填写一个值表？

解决方案

数据表可以为您工作，但不能从不同的表中引用单元格。至少这是Excel 2003的情况，您可以在2007年或2010年尝试，看看它是否有效。

---

其他那样：

是的，你可以做一个子程序。

你不能做一个工作表函数，因为它们是不允许的要更改工作表。

在该工作表上，创建一个函数：

  public function GetWhatever（byval p1 as variant，byval p2 as variant）as variant 
'确保你处于AutoCalculation模式，否则使用me.calculate 
 me.range（A1） value = p2 
 GetWhatever = me.range（A3）。value 
 end function 
  

然后，您可以从第二个工作表上的另一个过程重复调用此函数，并将新获得的结果复制到下一个可用行在您的工作表上：

  for i = 1 to 10 
 me.cells（i，3）.value = SheetWithCalculations .GetWhatever（ me.cells（i，1）.value，me.cells（i，2）.value）
 next 
  

I've got an excel worksheet that takes two inputs and generates an output.

I can currently open the worksheet, type the two into cells A1 and A2, and the result shows up in A3.

Is there a way I can make this into a function or subroutine, such that I can can use it in another worksheet to fill in a table of values?

解决方案

Data tables could work for you, but you can't refer to a cell from a different sheet. At least, that's the case for Excel 2003, you can try it out in 2007 or 2010 to see if it works.

---

Other that that:

Yes, you can make a subroutine.
You can't make a worksheet function though, as they are not allowed to change sheets.

On that worksheet, create a function that goes:

public function GetWhatever(byval p1 as variant, byval p2 as variant) as variant
  'Make sure you're in AutoCalculation mode, otherwise use me.calculate
  me.range("A1").value = p1
  me.range("A2").value = p2
  GetWhatever = me.range("A3").value
end function

You then can repeatedly call this function from another procedure, which is on the second worksheet, and copy the newly aquired result into the next available row on your worksheet:

for i = 1 to 10
  me.cells(i,3).value = SheetWithCalculations.GetWhatever(me.cells(i,1).value, me.cells(i,2).value)
next

这篇关于Excel：使用工作表作为函数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！