AppSheet 教學：利用試算表公式 (VLOOKUP) 打造彈性的會議簽到系統

在今天的 AppSheet 教學中，我們將探討一個非常實用且強大的功能：如何在 App 中直接使用 Google Sheets (或 Excel) 的試算表公式。我們將透過一個「會議簽到系統」的範例，來展示如何利用 VLOOKUP 函數，實現一個更具彈性的資料連動應用。

應用情境

想像一個常見的會議簽到情境：

1. 公司有多場會議，每場會議都需要記錄出席人員。

2. 員工擁有自己的員工編號，簽到時只需輸入或掃描員工編號即可。

3. 系統需要根據輸入的員工編號，自動帶出員工的姓名。

最大的挑戰是：如果一位新進員工或臨時與會者，他的資料還沒被預先建立在「人員資料表」中，該怎麼辦？我們希望他仍然可以先用編號簽到，等到事後補上資料時，簽到記錄上的姓名能自動更新。

這就是今天要介紹的 Spreadsheet formula 功能的絕佳應用場景。

基礎設定：三張資料表

首先，我們在 Google Sheets 中建立三張基礎資料表：

1. 會議 (Meetings): 用於建立不同的會議場次。

   • 會議編號 (Key)

   • 會議名稱

   • 會議修改時間

2. 人員 (Personnel): 存放所有員工的基本資料。

   • 人員編號 (Key)

   • 人員名稱

3. 簽到表 (Sign-in List): 記錄每場會議的簽到狀況。

   • 簽到表編號 (Key)

   • 會議編號 (Ref 類型，關聯至「會議」表)

   • 人員編號 (簽到時手動輸入的欄位)

   • 人員名稱 (我們希望此欄位自動帶出)

   • 簽到時間

核心步驟：設定 Spreadsheet Formula

我們的目標是在「簽到表」中，當輸入 人員編號 後，人員名稱 欄位能自動從「人員」表中查找並填入對應的姓名。

1. 在 AppSheet 編輯器中，進入 Data > Columns，並選擇 簽到表。

2. 找到 人員名稱 這個欄位，點擊左側的鉛筆圖示進入編輯模式。

3. 展開 Auto Compute 區塊，你會看到幾個選項。我們要使用的是 Spreadsheet formula。

4. 在這個欄位中，輸入你在 Google Sheets 中會使用的 VLOOKUP 公式。假設在試算表中，「人員編號」在 C 欄，「人員名稱」在 D 欄，而「人員」資料表的編號和姓名分別在 B 欄和 C 欄，你的公式會像這樣：

   Generated excel

   =VLOOKUP(C2, '人員'!B:C, 2, 0)

   • C2: 要查找的值 (當前這一筆簽到記錄的 人員編號)。

   • '人員'!B:C: 要查找的範圍 (在「人員」工作表的 B 到 C 欄)。

   • 2: 找到後，回傳範圍中的第 2 個欄位的值 (也就是 人員名稱)。

   • 0: 表示需要精確匹配。

   將這個公式貼到 Spreadsheet formula 欄位中。

5. 儲存設定。AppSheet 會自動將這個公式轉換為它內部使用的 R1C1 格式，例如：vlookup(RC[-1],'人員'!C[-2]:C[-1],2,0)。這表示它會根據欄位的相對位置來計算，更加穩健。

實際操作與問題排解

現在讓我們來看看實際操作時會發生什麼，以及如何解決可能遇到的問題。

情境一：簽到時，人員已存在

如果我們簽到時輸入的 人員編號 (例如 A001) 已經存在於「人員」表中，AppSheet 會在同步後，觸發試算表公式，人員名稱 ("張三") 會被正確地帶出來。

情境二：簽到時，人員不存在 (核心展示)

1. 臨時簽到: 我們在簽到 App 中新增一筆記錄，會議選擇「臨時會」，人員編號 輸入一個不存在的 A005。儲存後，你會發現 人員名稱 欄位是空的。

2. 事後補登資料: 我們到「人員」管理介面，新增一筆資料：人員編號 為 A005，人員名稱 為「路人甲」。

3. 同步與更新: 儲存後，再次查看「臨時會」的簽到記錄。神奇的事情發生了！A005 旁邊的 人員名稱 自動更新為「路人甲」。

這就是 Spreadsheet formula 的威力。它不是在 AppSheet 端計算，而是在資料寫回 Google Sheets 時，將公式一併寫入儲存格。因此，只要試算表端的源頭資料（「人員」表）有變動，所有引用到它的公式（「簽到表」中的 VLOOKUP）都會自動重新計算。

常見問題與解決方案：公式沒有生效？

在影片中，我們也遇到了一個常見的狀況：當我們修改了資料表的結構（例如新增或刪除欄位），有時會導致 Spreadsheet formula 失效或抓到錯誤的欄位。

當你發現公式沒有如預期般運作時，可以這麼做：

1. 檢查 Google Sheet: 直接打開後端的 Google Sheet，看看公式是否正確寫入，或者是否出現了 #N/A 或 #REF! 等錯誤。這能幫助你判斷問題是出在公式本身還是 AppSheet 的設定。

2. 重新產生結構 (Regenerate Structure): 這是最重要的一步。當你變更了試算表的欄位結構後，必須告訴 AppSheet 重新讀取一次。

   • 回到 AppSheet 編輯器 Data > Columns。

   • 選擇你修改過的資料表（例如 簽到表）。

   • 點擊右上方的 Regenerate Structure 按鈕。

   • AppSheet 會重新掃描你的試算表結構，並修正欄位對應關係。

   這個動作通常能解決因為欄位變動而導致的公式錯誤。

總結

今天我們學習了如何在 AppSheet 中利用 Spreadsheet formula 來執行試算表原生公式，它帶來了幾個好處：

• 彈性極高: 可以在資料輸入後，根據其他資料表的變化動態更新內容。

• 功能強大: 能運用 Google Sheets 中所有你熟悉的複雜公式，而不僅限於 AppSheet 內建的表達式。

• 除錯直觀:可以直接在 Google Sheets 中看到公式的計算結果與錯誤訊息。

下次當你需要類似「先記錄，後更新」的應用場景時，不妨試試這個強大的功能！