☰

根据模板向电子表格添加附加列

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2010 vba
根据模板向电子表格添加附加列

有没有办法将一些预定义的分析/评估单元添加到现有电子表格中？

我的用例是一个应用程序，它使用数据库中的员工数据生成 MS Excel 文件。我们需要以应用程序内部无法实现的方式评估这些数据，因此我们在 Excel 中添加了一些带有公式的列。

有没有一个好的方法可以将这些评估单元保存为模板并将其应用于新生成的文件？

从应用程序生成的示例文件/表：

+-------------+
| ColA | ColB |
+-------------+
|  foo | baz  |
|  bar | baz  |
|  foo |      |
|  bar |      |
+-------------+

带有应自动添加的附加公式（目前，我使用复制/粘贴从旧文件添加公式）

+-----------------------------+
| ColA | ColB | Eval1 | Eval2 |
+-----------------------------+
|  foo | baz  |   1   |   1   |
|  bar | baz  |   0   |   1   |
|  foo |      |   1   |   0   |
|  bar |      |   0   |   0   |
+-----------------------------+

编辑以澄清：我不需要任何明确的公式，而是一种更通用的方法来添加带有公式的任意列。我对 Excel 还很陌生（到目前为止还没有接触过 VBA），所以我不知道这是否可行，也不知道需要做多少工作。

这个问题不是关于任何计算的，而是关于向新生成的文件添加自定义预定义公式的过程.xlsx。我示例中的前后表仅用于演示应在何处添加列，并不旨在承载任何语义。

Month对于我的具体问题，我总是得到一个包含、Employee和列的文件WorkHours。然后，需要添加包含百分比的列，我通常手动完成并将它们拖到所有行。这不是一项复杂的任务，但我需要一种方法让我的同事可以访问它，而无需他们手动从某个文件中复制公式。

+-----------------------------------------------------------+
|Month    Employee   WorkHours    25%    50%    75%    100% |
|01.2016  PersonA       149,25    FormA  FormB  FormC  FormD|
|01.2016  PersonB        79,00    FormA  FormB  FormC  FormD|
+-----------------------------------------------------------+

公式如下：

FormA: =IF(AND(C2>0;C2<=40);0,25;"")
FormB: =IF(AND(C2>40;C2<=80);0,5;"")
FormC: =IF(AND(C2>80;C2<=120);0,75;"")
FormD: =IF(C2>120;1;"")

答案1

当然，您可以使用 VBA 例程来自动执行大多数可以手动完成的活动（并且可以客观描述）。我想到了一些广泛的方法：

  • 以当前的方法为基础，使用带有预定义公式的模板文件。将 VBA 添加到该文件以自动执行您现在正在执行的操作：将模板中的公式复制到生成的文件中。这可能是更好（更简单）的方法，特别是如果您希望更改分析/评估公式 — 并且尤其如果您需要允许您的同事修改模板。
  • 创建包含以下内容的新 Excel 文件仅有的VBA。将所需的 Excel 工作表公式作为数据嵌入到 VBA 中。这将自动执行打字将公式添加到新工作表中。这种方法可能有助于根据现有数据定制公式。

当然，这并不是非此即彼的；您可以设计一个结合上述各方面的混合解决方案。

你应该看到如何在 MS Office 中添加 VBA？了解一般信息。这描述了如何激活功能区上的“开发人员”选项卡。如果您不了解 VBA，一个好的开始方法是单击“开发人员”选项卡上的“录制宏”，选择一个名称并单击“确定”，浏览要录制/自动执行的操作，然后单击“停止录制”（与“录制宏”按钮相同）。我似乎记得，十年或二十年前，宏被记录并存储为按键序列/脚本；现在它们被转换成等效的 VBA。

您可能希望编辑代码以使其万无一失。教您 VBA 超出了本网站的范围，但我突然想到，您可能希望动态地“找到”要放置固定公式的工作表。以下是执行此操作的方法：

Set curw = ActiveWindow
Set curs = ActiveSheet
For Each w In Windows
    w.Activate
    For Each s In Sheets
        s.Activate
        If Range("A1") = "Month" And Range("B1") = "Employee" _
                                 And Range("C1") = "WorkHours" Then
            ' This is the sheet where I want to put the analysis/evaluation formulas.
            If Range("D1") = "" And Range("E1") = "" And Range("F1") = "" _
                                And Range("G1") = "" Then
                ' Put the formulas here.
            Else
                MsgBox "There’s already something here!"
            End If
        End If
    Next s
Next w
curw.Activate
curs.Activate

如果您搜索此网站和其他资源，您会发现大量有关如何确定目标表中已有多少数据的示例（以便您可以扩展新的列来匹配）。

您可以告诉您的同事打开模板文件并运行宏来安装公式。您可以创建一个按钮，以免他们需要浏览菜单。另请参阅如何存储 Office 2007 的 VBA 宏以使它们始终可用并与他人共享？ 了解有关如何让您的同事可以使用宏而无需打开模板文件的信息。

相关内容