開始使用公式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

顯示簡單的值Show a simple value

在 Excel 中,您可以透過在儲存格內輸入內容來輸入特定資料,如數值 42 或片語 Hello WorldIn 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. 在公式列中輸入 "Hello World" (以雙引號括住),以將 Text 屬性的值設定為該字串: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 屬性 (如標籤) 設定為 42Sum(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. 此範例顯示上一個程序的標籤,以及名為 TextInput1TextInput2 的兩個文字輸入控制項。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. 例如,若要指定 TextBox1Text 屬性,請輸入 TextBox1.TextFor 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. 排列滑桿以避免它們重疊,新增三個標籤並將其設定為顯示紅色綠色藍色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:

    變更畫面之背景填滿色彩的公式 (尚未完成)

    當您輸入右括弧時,畫面的背景會依據每個滑桿的預設值 (50) 變更為深灰色。As 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.

諸如 NavigateCollect 等某些函式只能用於行為公式中。You 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.

語法醒目提示