公式入门Get started with formulas

为应用配置公式,不仅用于计算值和执行其他任务(与在 Excel 中的操作一样),还能(根据应用需求)响应用户输入。Configure your app with formulas that not only calculate values and perform other tasks (as they do in Excel) but also respond to user input (as an app requires).

  • 在 Excel 中生成公式,例如,填充单元格并创建表格和图表。In Excel, you build formulas that, for example, populate cells and create tables and charts.
  • 在 PowerApps 中,生成与配置控件(而不是单元格)时类似的公式。In PowerApps, you build similar formulas as you configure controls instead of cells. 此外,生成专门适用于应用(而非电子表格)的公式。In addition, you build formulas that apply specifically to apps instead of spreadsheets.

例如,生成公式以确定当用户选择按钮、调整滑块或提供其他输入时应用的响应方式。For example, you build a formula to determine how your app responds when users select a button, adjust a slider, or provide other input. 这些公式可能会显示不同的屏幕、更新应用外部的数据源、或创建包含现有表格中数据子集的表格。These formulas might show a different screen, update a data source that's external to the app, or create a table that contains a subset of the data in an existing table.

可将公式用于各种方案。You can use formulas for a wide variety of scenarios. 例如,可以使用设备的 GPS、地图控件以及使用 Location.LatitudeLocation.Longitude 的公式,从而显示当前位置。For example, you can use your device's GPS, a map control, and a formula that uses Location.Latitude and Location.Longitude to display your current location. 移动时,地图会自动跟踪你的位置。As you move, the map automatically tracks your location.

本主题仅提供使用公式的概述。This topic provides only an overview of working with formulas. 浏览 公式引用 了解详细信息以及可以使用的函数、运算符和其他构建基块的完整列表。Browse the formula reference for more details and the complete list of functions, operators, and other building blocks you can use.

先决条件Prerequisites

  • 注册安装 PowerApps,然后打开该程序,并提供注册所用的同一凭据进行登录。Sign up for PowerApps, install it, open it, and then sign in by providing the same credentials that you used to sign up.
  • 了解如何在 PowerApps 中 配置控件Learn how to configure a control in PowerApps.

显示简单值Show a simple value

在 Excel 中,可以输入数据的特定片段,例如编号 42 或短语 Hello World,方法是将这些内容键入到单元格。In Excel, you can enter a specific piece of data, such as the number 42 or the phrase Hello World, by typing it into a cell. 该单元格将始终显示所键入的片段所属的数据。That cell will always show that data exactly as you typed it. 同样,在 PowerApps 中,只需将标签的 Text 属性设置为想要使用的字符的精确序列(由双引号括住),即可指定数据的某个片段。In PowerApps, you can similarly specify a piece of data that doesn't change by setting the Text property of a label to the exact sequence of characters that you want, surrounded by double quotation marks.

  1. 选择“文件”菜单上的“新建”(屏幕左边缘附近)。Select New on the File menu (near the left edge of the screen).
  2. 在“创建应用”下,选择“空白应用”磁贴上的“电话布局”。Under Create an app, select Phone layout on the Blank app tile.

    编辑栏位于屏幕顶部。The formula bar sits at the top of the screen.

    编辑栏

    此栏包括两个部分:This bar has two parts:

    • 属性列表:每个控件和屏幕均有一个 属性集Property list: Each control and screen has a set of properties. 使用此列表选择特定属性。Use this list to select a specific property.
    • 公式:要针对此属性计算的公式,由 值、运算符和函数 组成。Formula: The formula to be calculated for this property, made up of values, operators, and functions.

      在编辑栏中,可以查看并编辑所选控件的属性或屏幕的属性(若未选择任何控件)。In the formula bar, you can see and edit properties for the selected control or for the screen if no controls are selected. 可以在“内容”选项卡上查看所选控件的名称:You can see the name of the selected control on the Content tab:

      内容栏显示当前所选的控件

      可以通过单击名称更改“内容”选项卡中所选控件的名称。You can change the name of the selected control in the Content tab by clicking the name.

  3. 在屏幕中添加“标签”控件。Add a Label control to the screen.

    添加了 TextBox 控件

    添加标签时,属性列表会自动显示用于控制控件显示内容的“Text”属性。When you add a label, the property list automatically shows the Text property, which drives what the control shows. 默认情况下,该属性的值是 "Text"By default, the value of this property is "Text".

  4. Text 属性的值设置为 "Hello World",方法是将双引号括住的字符串键入编辑栏:Set the value of the Text property to "Hello World" by typing that string, surrounded by double quotes, into the formula bar:

    使用标签 "Hello World"

    在键入过程中,标签会反映此新值。The label reflects this new value as you type it. 键入时,屏幕可能会显示黄色感叹号图标。The screen may show yellow exclamation-point icons while you type. 这些图标表示存在错误,但在输入有效值后,这些图标将消失。These icons indicate errors, but they'll go away when you finish entering a valid value. 例如,如果字符串首尾没有双引号,则无效。For example, a string without double quotation marks on both ends isn't valid.

    在 Excel 中,可以显示一个数字,如 42,方法是将其键入一个单元格或键入用于解析到该数字的公式,如 =SUM(30,12)In Excel, you can show a number, such as 42, by typing it into a cell or by typing a formula that resolves to that number, such as =SUM(30,12). 在 PowerApps 中,可以将标签等控件的“Text”属性设置为“42”或“Sum(30,12)”,从而达到同样的效果。In PowerApps, you can achieve the same effect by setting the Text property of a control, such as a label, to 42 or Sum(30,12). 无论在工作表或应用中进行了其他何种更改,单元格和标签始终都会显示此数字。The cell and the label will always show that number regardless of what else changes in the worksheet or the app.

    注意:在 PowerApps 中,并不在公式前使用等号或加号,这一点与在 Excel 中不同。Note: In PowerApps, you don't precede a formula with an equals sign or a plus sign as you do in Excel. 默认情况下,编辑栏将在该处键入的任何内容均视为一个公式。The formula bar treats anything you type there as a formula by default. 此外,也不使用双引号 (") 将公式括起来(在之前的操作中使用它指定文本的字符串)。You also don't surround a formula with double quotation marks ("), as you did earlier to specify a string of text.

  5. 在标签的“Text”属性中,将“"Hello World"”替换成“Sum(1,2,3)”。In the Text property of the label, replace "Hello World" with Sum(1,2,3).

    键入部分函数 Sum(1,2,3(不加右括号)显示错误

    键入时,编辑栏会显示此函数的描述和预期参数,为你提供帮助。While you type, the formula bar helps you by showing the description and the expected arguments for this function. 对于 "Hello World" 中的右双引号,屏幕显示黄色感叹号表示出现错误,直至键入了此公式的最后一个括号:As with the final double quotation mark in "Hello World", the screen shows yellow exclamation points to indicate an error until you type the final parenthesis of this formula:

    使用完整的公式 Sum(1,2,3)

基于输入更改值Change a value based on input

在 Excel 中,将 =SUM(A1:A2) 键入到单元格,显示单元格 A1 和 A2 所包含的任何值的总和。In Excel, you type =SUM(A1:A2) into a cell to show the sum of whatever values cells A1 and A2 contain. 如果任一值或全部值发生更改,则包含公式的单元格将自动显示更新结果。If either or both of those values change, the cell that contains the formula automatically shows the updated result.

Excel 重新计算两个数字相加的图示

在 PowerApps 中,可通过添加控件并设置其属性实现类似结果。In PowerApps, you can achieve a similar result by adding controls and setting their properties. 此示例展示了上述过程中的标签,以及两个“文本输入”控件(分别名为“TextInput1”和“TextInput2”)。This example shows the label from the previous procedure and two Text input controls, named TextInput1 and TextInput2.

PowerApps 重新计算两个数字相加的图示

无论在文本输入控件中键入什么数字,标签始终都会显示这些数字的总和,因为它的“Text”属性设置为以下公式:Regardless of what numbers you type in the text-input controls, the label always shows the sum of those numbers because its Text property is set to this formula:
TextInput1 + TextInput2TextInput1 + TextInput2

PowerApps 重新计算两个数字相加的图示

在 Excel 中,可以使用条件公式进行显示,例如,用红色显示负值。In Excel, you can use conditional formatting to show, for example, negative values in red. 在 PowerApps 中,可以使用包含 If 函数的公式,其行为与在 Excel 中类似。In PowerApps, you use a formula that contains the If function, which behaves similarly to how it behaves in Excel.

  1. 将标签的“Color”属性设置为以下公式:Set the Color property of the label to this formula:
    If( Value(TextBox1.Text) < 0, Red, Black )If( Value(TextBox1.Text) < 0, Red, Black )

    注意:在公式中,通过提供控件的名称(后跟句点、属性名称)指定控件的属性。Note: In a formula, specify the property of a control by providing the name of the control, followed by a period, followed by the name of the property. 例如,通过键入 TextBox1.Text 指定 TextBox1Text 属性。For example, specify the Text property of TextBox1 by typing TextBox1.Text.

    PowerApps 基于值重新计算更改标签颜色的图示

  2. TextInput1TextInput2 中,指定两个数字(这两个数字在相加后的结果为负数)。In TextInput1 and TextInput2, specify two numbers that, when added together, result in a negative number.

    PowerApps 基于值重新计算更改标签颜色的图示

    标签中的值显示为红色。The value in the label appears in red.

基于用户输入更改颜色Change a color based on user input

可以通过公式配置应用,使用户能够更改应用的外观或行为。You can configure your app with formulas so that users can change your app's appearance or behavior. 例如,可以创建一个筛选器,仅显示包含用户指定的文本字符串的数据,或让用户基于数据集中的某列对一组数据进行排序。For example, you can create a filter to show only data that contains a string of text that the user specifies, or you can let users sort a set of data based on a certain column in the data set. 在此过程中,将通过调整一个或多个滑块让用户更改屏幕颜色。In this procedure, you'll let users change the color of the screen by adjusting one or more sliders.

  1. 删除之前步骤中的控件,或创建空白应用(与之前步骤中的操作相同),并向其添加三个滑块控件:Remove the controls from the previous procedures, or create a blank app as you did previously, and add three slider controls to it:

    插入滑块控件

  2. 排列滑块以避免重叠,添加三个标签,然后将它们配置为显示“Red”、“Green”和“Blue”:Arrange the sliders so they don't overlap, add three labels, and configure them to show Red, Green, and Blue:

    排列滑块并为每个颜色组件添加标签

  3. 将每个滑块的 Max 属性设置为 255,该值是 RGBA 函数颜色组件的最大值。Set the Max property of each slider to 255, which is the maximum value of a color component for the RGBA function.

    可通过在“内容”选项卡进行选择以指定 Max 属性,或在属性列表中:You can specify the Max property by selecting it on the Content tab or in the property list:

    更改每个滑块的最大值

  4. 通过从任何控件外单击选择屏幕,然后将屏幕的 Fill 属性设置为以下公式:Select the screen by clicking away from any control, and then set the screen's Fill property to this formula:
    RGBA( Slider1.Value, Slider2.Value, Slider3.Value, 1 )RGBA( Slider1.Value, Slider2.Value, Slider3.Value, 1 )

    如上所述,可以使用 运算符访问控件属性As already described, you access control properties by using the . operator. Slider1.Value 表示滑块的 Value 属性,该属性反映了用户在 MinMax 值间放置滑块的位置。Slider1.Value refers to the slider's Value property, which reflects where the user has placed the slider between the Min and Max values. 键入此公式时,其包含的每个控件均在屏幕和编辑栏间使用颜色编码:As you type this formula, each control that it contains is color coded between the screen and the formula bar:

    更改屏幕的背景填充颜色的公式,但尚未完成

    键入右括号时,屏幕的背景将基于每个滑块的默认值更改为深灰色,默认值为 50As you type the closing parenthesis, the screen's background will change to dark gray based on the default value of each slider, which is 50. 键入公式完成后,将对其进行计算并用作背景填充颜色的值。At the moment when you finish typing the formula, it's calculated and used as the value of the background fill color. 可以在默认工作区中与应用交互,无需打开预览:You can interact with your app while in the default workspace without needing to open Preview:

    更改每个滑块的最大值

  5. 调整滑块,并查看所做的更改如何影响背景颜色。Adjust the sliders, and see how your changes affect the background color.

    更改每个滑块时,包含 RGBA 函数的公式将重新计算,并立即更改屏幕显示的效果。As each slider changes, the formula that contains the RGBA function is recalculated, which immediately changes how the screen appears.

    更改屏幕的背景填充颜色公式,现在完成

管理应用行为Manage app behavior

使用公式不仅可以执行计算和更改外观,还可以执行操作。You can use formulas not only to perform calculations and change appearance but also to take action. 例如,可以将某个按钮的 OnSelect 属性设置为包含 Navigate 函数的公式。For example, you can set the OnSelect property of a button to a formula that includes the Navigate function. 当用户选择该按钮时,将显示在该公式中指定的屏幕。When a user selects that button, the screen that you specify in the formula appears.

可以仅在行为公式中使用某些函数,例如 NavigateCollectYou can use some functions, such as Navigate and Collect, only in behavior formulas. 如果只能在此上下文中使用某个函数,则公式引用将进行调用。The formula reference calls out if you can use a function only in this context.

如果使用分号 (;) 分隔函数,则可以在一个行为公式中执行多个操作。You can take more than one action in a behavior formula if you separate functions with a semi-colon (;). 例如,你可能想要更新上下文变量、将数据推送到数据源,并最终导航到其他屏幕。For example, you might want to update a context variable, push data to a data source, and finally navigate to another screen.

按类别查看属性列表View a list of properties by category

属性列表按字母顺序显示属性,但如果选择“视图”选项卡上的“高级”选项,则还可以查看按类别安排的控件的所有属性:The properties list shows properties alphabetically, but you can also view all the properties of a control, organized by category, if you select the Advanced option on the View tab:

高级视图

可以在此视图内直接编辑公式。You can edit formulas directly within this view. 通过窗格顶部的控件选择器可以快速查找要使用的控件。With the control selector at the top of the pane, you can quickly find a control to work with. 此外,借助属性搜索可以快速查找该控件的属性。And with the property search, you can quickly find a property of that control.

此视图默认显示最重要的属性。Initially, this view shows the most important properties. 若要显示所有属性,请单击窗格底部的向下箭头。To reveal all the properties, click the down arrow at the bottom of the pane. 每个控件均具有属性的长列表,该列表控制控件的行为和外观涵盖的所有方面。Each control has a long list of properties that govern all aspects of the control's behavior and appearance. 可以滚动列表进行浏览,也可以在窗格顶部的框中键入内容以搜索某个属性。You can scroll through the list or search for a property by typing in the box at the top of the pane.

公式语法Formula syntax

在公示栏中键入公式时,不同的语法元素以不同的颜色显示,让用户更加轻松地进行查看并帮助理解长公式。As you type a formula in the formula bar, different syntax elements appear in different colors to improve readability and help you understand long formulas. 下面是 PowerApps 中的颜色代码列表。Here is the color code list in PowerApps.

突出显示语法