Anaconda 代码：通过 Python 创建和使用 Excel 的用户定义函数

我们很高兴地宣布 Anaconda 代码中的一项强大的新功能：Python 用户定义函数！借助 UDF，您可以编写 Python 函数并像本机 Excel 函数一样使用它们，将 Python 丰富的数据科学生态系统的全部功能直接带入您的电子表格公式中。让我们看一个真实的示例，展示这种集成的强大功能。

在一个函数调用中从交叉表到长格式

考虑一个常见的数据重塑挑战：将交叉表（宽）数据转换为长格式。您可能会想，“好吧，我可以使用 Power Query 做到这一点，没有问题。”是的，确实如此。但生成的表不是动态的。每次输入数据发生变化时，都需要刷新查询。并且该查询与该工作簿相关联。使用 Anaconda Code 和 Anaconda Toolbox，您可以创建 Python UDF，将其另存为代码片段，并在您打开的任何工作簿中使用它。另外，快速逆透视宽格式表所需的代码非常简单。以下是创建自定义 UNPIVOT 函数的方法，该函数的工作方式与任何内置 Excel 函数一样：

Python

# decorate the function with the UDF decorator

@UDF(name='UNPIVOT', nested=False)

def unpivot_data(

    data: UDF.Range, # the input data from the Excel sheet

    id_vars, # the column(s) to keep fixed in the output

    var_name='variable', # the column name to hold the unpivoted column headers

    value_name='value' # the column name to hold the unpivoted values

    ) -> UDF.Range: # output the result as an Excel spilled array

    # convert the input data into a Pandas DataFrame 

    df = to_df(data)

    # pass the arguments through to the melt function and return the result

    return df.melt(id_vars=id_vars, var_name=var_name, value_name=value_name)

定义后，您可以直接在 Excel 中使用该函数。例如，销售数据分布在各个年份：

Anaconda 代码：通过 Python 创建和使用 Excel 的用户定义函数插图

图 1：直接从 Excel 网格调用 UNPIVOT 用户定义函数

如果您想遵循此示例，这里有一个公式可以为您提供输入数据。只需将其放入工作表的单元格 B2 中即可。

={"Category",1996,1997,1998;"Confections",29685.549981647,82657.7504128219,55013.9244483569;"Dairy Products",40980.4499421209,115387.639834883,78139.194964755;"Meat/Poultry",28813.6599777699,80975.1079431361,53233.591471141}

设置输入数据后，打开 Anaconda Code 并加载环境。这将确保 Python 环境在加载项中运行：

Anaconda 代码：通过 Python 创建和使用 Excel 的用户定义函数插图1

图2：要在Anaconda代码中使用Python，首先需要加载环境

加载环境后，转到Anaconda Code 的Imports and Definitions选项卡，并将代码片段中的 UDF 代码粘贴到脚本中。作为参考，这里再次说明：

# decorate the function with the UDF decorator

@UDF(name='UNPIVOT', nested=False)

def unpivot_data(

    data: UDF.Range, # the input data from the Excel sheet

    id_vars, # the column(s) to keep fixed in the output

    var_name='variable', # the column name to hold the unpivoted column headers

    value_name='value' # the column name to hold the unpivoted values

    ) -> UDF.Range: # output the result as an Excel spilled array

    # convert the input data into a Pandas DataFrame 

    df = to_df(data)

    # pass the arguments through to the melt function and return the result

    return df.melt(id_vars=id_vars, var_name=var_name, value_name=value_name)

将其粘贴到“导入和定义”脚本中后，单击“保存并运行”按钮以在 Excel 中注册 UNPIVOT 函数。

完成后，绿色的“保存并运行”按钮将更改为“运行”。这意味着您的函数现已在 Excel 中注册。！继续使用它来逆透视销售数据！

=UNPIVOT(B2:E5,"Category", "Year", "Revenue")

现在您已经了解了如何在 Anaconda 代码中创建 Python 用户定义函数，这里有一个快速视频，展示了一个更复杂的示例。希望这会激发您对使用此新功能可以做的事情的一些想法。

为什么 Anaconda 代码中的 UDF 会改变游戏规则

原生 Excel 集成：您的 Python 函数就像内置 Excel 函数一样工作

范围支持：使用 UDF.Range 类型提示自然地处理 Excel 范围

灵活的参数：支持可变参数和带有默认值的可选参数

内置文档：您的 Python 文档字符串成为 Excel 函数帮助文本

本地执行：函数在安全的 Anaconda 代码环境中本地运行

UDF 入门

创建您自己的 UDF 就像使用 @UDF 修饰 Python 函数一样简单。以下是它们的特别之处：

Excel 范围的类型提示：使用 UDF.Range 处理单元格范围

可选参数：添加默认值以使函数参数在 Excel 中可选

有用的文档：添加文档字符串为 Excel 用户提供功能帮助

变量参数：支持 *args 接受多个输入

立即开始创建您自己的函数

可能性是无限的 – 从简单的数据转换到复杂的统计分析，您现在可以将 Python 的功能直接引入 Excel 公式中。