2025年8月6日 星期三

Excel教學:打造自動更新的行事曆!用VLOOKUP輕鬆管理你的待辦事項

你是否常常覺得需要一個簡單、直觀的方式來追蹤待辦事項?雖然市面上有許多行事曆軟體,但有時候,我們只想要一個在熟悉的 Excel 環境中就能運作的解決方案。

今天,我們將分享一個非常實用的 Excel 技巧,教你如何利用 VLOOKUP 函數,打造一個可以顯示單月、三個月甚至一整年待辦事項的動態行事曆。你只需要在一個清單中輸入日期和任務,這些事項就會自動同步到你的行事曆上,一目了然!

核心概念

這個行事曆的運作原理相當簡單:

  1. 待辦事項清單:建立一個獨立的工作表,專門用來記錄所有任務的「日期」和「待辦事項」。

  2. 行事曆版面:設計出你喜歡的月曆或年曆版面。

  3. VLOOKUP 查詢:在行事曆的每一天儲存格中,使用 VLOOKUP 函數去查詢「待辦事項清單」,如果當天有任務,就將其顯示出來。

我們將介紹兩種版本:「半自動更新版」和更進階的「全自動更新版」。

方法一:半自動更新版 (手動調整起始日期)

這個版本非常適合需要固定查看特定時期(例如:專案週期、學期行事曆)的使用者。你只需要手動修改一個儲存格的日期,整個行事曆就會跟著變動。

操作步驟:

  1. 建立「待辦事項」工作表:
    在一個新的工作表中,建立兩欄:「日期」和「待辦事項」。將你所有的任務和對應的日期一一填入。

    日期待辦事項
    2024/10/25提交專案報告
    2024/11/11團隊會議
    ......
  2. 設計行事曆版面:
    你可以設計單月、三個月或一整年的版面。最重要的是,在每個日期格子旁,預留一個空白的儲存格用來顯示任務。

  3. 設定日期與 VLOOKUP 公式:

    • 起始日期:在行事曆的第一個日期儲存格(例如年曆的 1 月 1 日)手動輸入你想要的起始日期,例如 2024/1/1。

    • 後續日期:後面的日期儲存格,公式可以設為 =前一個日期儲存格+1。

    • 顯示任務:在預留的任務儲存格中,輸入以下公式(假設日期在 C10,待辦事項清單在 待辦事項 工作表的 A、B 欄):

      Excel
      =IFERROR(VLOOKUP(C10, 待辦事項!$A:$B, 2, FALSE), "")

      這個公式的意思是:去 待辦事項 工作表查詢 C10 的日期,如果找到了,就傳回第 2 欄的內容(待辦事項);如果沒找到(IFERROR 的作用),就顯示空白。

  4. 更新行事曆:
    當你需要更換年份或月份時,只需要修改那個手動輸入的起始日期儲存格,例如將 2024/1/1 改為 2025/1/1,整個行事曆就會自動刷新,非常方便!

方法二:全自動更新版 (永遠顯示當前日期)

如果你希望行事曆能「永遠」自動跟上現在的日期,不用每年手動調整,那麼這個版本就是為你設計的!

這個版本的核心差異在於起始日期的公式。我們不再手動輸入,而是改用 Excel 的日期函數。

關鍵公式修改:

  • 年曆:將起始日期的公式改為:

    Excel
    =DATE(YEAR(TODAY()), 1, 1)

    這個公式會自動抓取「今年的年份」,並產生 1 月 1 日的日期。這樣一來,每年打開檔案,它都會自動顯示當年度的行事曆。

  • 月曆:若要讓月曆永遠顯示「當前月份」,起始日期公式可以設為:

    Excel
    =DATE(YEAR(TODAY()), MONTH(TODAY()), 1)

    這個公式會自動抓取今年、今月,並產生 1 號的日期。

透過這個方法,你完全不需要去動行事曆的任何設定,只要專心維護你的「待辦事項」清單即可。

範本檔案下載

為了讓你更快上手,我們提供了兩種版本的範本檔案,你可以直接下載使用或參考其公式設計。

總結

利用 Excel 搭配簡單的 VLOOKUP 和日期函數,就能打造出一個符合個人需求的客製化行事曆。無論是需要手動調整的彈性,還是全自動的便利,這個方法都能大大提升你的工作效率。現在就動手試試看吧!




Google Sheets 教學:打造多筆訂單批次更新系統(日期區間查詢與狀態更新)

在管理網路商店或處理大量訂單時,最耗時的工作之一莫過於逐筆更新訂單狀態。無論是確認款項、標示出貨,或是處理退貨,重複的操作不僅效率低落,也容易出錯。最近有網友提到,希望能透過核取方塊(Checkbox)一次選取多筆訂單進行更新,為了解決這個常見的需求,我們將分享如何利用 Google Sheets 搭配 Apps Script 與簡單的 HTML 介面,打造一個實用的「訂單批次更新系統」。

這個系統能讓你先依據日期區間篩選出特定時段的訂單,再勾選需要變更的項目,最後一鍵完成狀態更新。讓我們一起來看看如何實現吧!

功能亮點

  • 日期區間查詢: 可自訂起始與結束日期,精準撈出特定時間範圍內的訂單資料。

  • 多筆批次選取: 使用核取方塊(Checkbox),自由勾選多筆需要更新的訂單。

  • 一鍵批次更新: 輸入新的訂單狀態(例如:已付款、已出貨),點擊按鈕即可一次更新所有已選取的訂單。

  • 直觀網頁介面: 透過簡單的 HTML 頁面操作,無需直接在 Google 試算表中手動修改。

系統成品展示

操作流程非常簡單:

  1. 在網頁介面上選擇想查詢的「起始訂購日期」與「結束訂購日期」。

  2. 點擊「查詢/更新」按鈕,系統會列出該區間內的所有訂單。

  3. 勾選您想更新狀態的訂單。

  4. 在「訂單狀況」欄位輸入新的狀態,例如「已付款」。

  5. 再次點擊「查詢/更新」按鈕,系統就會將更新寫回 Google 試算表。

教學步驟

首先,你需要一份儲存訂單資料的 Google 試算表。我們已經準備好一份範本,你可以直接複製使用。

  1. 點擊下方連結前往範本試算表:

  2. 建立副本: 點選左上角 檔案 > 建立副本,將這份試算表複製到你自己的雲端硬碟。這樣你才能對它進行編輯與操作。

    這份範本包含訂單編號、日期、品名、訂單狀態等欄位,你可以根據自己的需求修改。請注意,此範例中,「訂單編號」(A欄) 是唯一值,而**「日期」(B欄) 是查詢的依據**。

接下來,我們要為試算表加入後端處理邏輯。

  1. 在你剛剛建立的試算表副本中,點選 擴充功能 > Apps Script。

  2. 將編輯器中原有的程式碼清空,並貼上下方的 Code.gs 程式碼。

  3. 點擊右上角的 部署 > 新增部署作業。

  4. 在彈出的視窗中,點擊齒輪圖示,類型選擇 網頁應用程式。

  5. 在「誰可以存取」的選項中,選擇 任何人。

  6. 點擊 部署,並在接下來的權限審查中點擊 授予存取權,選擇你的 Google 帳戶並允許所有權限。

  7. 完成後,複製產生的「網頁應用程式網址」,這個網址非常重要,下一步會用到。

Code.gs 程式碼:

JavaScript
// 腳本發佈為網路應用程式後,處理GET請求
function doGet(e) {
  // 取得從網頁傳來的參數
  var params = e.parameter;
  var BeginDate = Date.parse(params.BeginDate); // 開始日期
  var EndDate = Date.parse(params.EndDate);     // 結束日期
  var OrderId_list = params.OrderID_List;       // 訂單ID列表 (字串)
  var OrderStatus = params.OrderStatus;         // 訂單狀態

  // 將訂單ID字串用逗號分割成陣列
  var OrderList = OrderId_list ? OrderId_list.split(',') : [];

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("工作表1"); // 請確認你的工作表名稱

  var lastRow = sheet.getLastRow();
  var data = []; // 用於回傳給前端的資料

  // 取得所有資料
  var listAll = sheet.getRange(1, 1, lastRow, sheet.getLastColumn()).getValues();

  // 將標題列先放入回傳資料中
  data.push(listAll[0]);

  // 遍歷所有資料列 (從第二列開始,索引為1)
  for (var i = 1; i < listAll.length; i++) {
    var row = listAll[i];
    var listDate = new Date(row[1]).getTime(); // B欄位的日期
    var listOrderID = row[0].toString();       // A欄位的訂單編號

    // 判斷日期是否在查詢區間內
    if (listDate >= BeginDate && listDate <= EndDate) {
      
      // 如果有傳入訂單狀態,且此筆訂單ID在要更新的列表中,就進行更新
      if (OrderStatus && OrderList.indexOf(listOrderID) !== -1) {
        sheet.getRange(i + 1, 4).setValue(OrderStatus); // 更新D欄 (訂單狀態)
        sheet.getRange(i + 1, 7).setValue(new Date());  // 更新G欄 (確認日期)
        // 更新後,重新讀取該列的值以確保回傳的是最新資料
        row = sheet.getRange(i + 1, 1, 1, sheet.getLastColumn()).getValues()[0];
      }
      
      data.push(row); // 將符合條件的資料列加入回傳陣列
    }
  }

  // 將結果轉換為JSON格式並回傳
  return ContentService.createTextOutput(JSON.stringify(data))
    .setMimeType(ContentService.MimeType.JSON);
}

這個 HTML 檔案就是我們的操作介面。

  1. 在你的電腦上新增一個文字檔案,將其命名為 ordering.html。

  2. 用記事本或任何程式碼編輯器打開它,貼上下方的 HTML 程式碼。

  3. 最重要的一步: 找到程式碼中的 var url = '...' 這一行,將引號內的網址替換成你在步驟二取得的網頁應用程式網址。

  4. 儲存檔案。

ordering.html 程式碼:

Html
<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <title>訂單查詢與批次更新</title>
</head>
<body>

    輸入起始訂購日期: <input type="date" id="BeginDate" value="2020-02-01"><br>
    輸入結束訂購日期: <input type="date" id="EndDate" value="2020-02-17"><br>
    訂單狀況: <input type="text" id="OrderStatus" /><br>
    <input type="button" value="查詢/更新" onclick="searchOrders()">

    <div id="order_status"></div>

    <script>
        function searchOrders() {
            // 替換成你自己的 Apps Script 網頁應用程式網址
            var url = 'https://script.google.com/macros/s/AKfycbyo.../exec'; 

            var beginDate = document.getElementById("BeginDate").value;
            var endDate = document.getElementById("EndDate").value;
            var orderStatus = document.getElementById("OrderStatus").value;

            // 收集所有被勾選的訂單ID
            var checkboxes = document.getElementsByName("OrderID");
            var orderID_List = "";
            for (var i = 0; i < checkboxes.length; i++) {
                if (checkboxes[i].checked) {
                    orderID_List += checkboxes[i].value + ",";
                }
            }
            // 移除最後一個多餘的逗號
            if (orderID_List.length > 0) {
                orderID_List = orderID_List.substring(0, orderID_List.length - 1);
            }

            // 組合查詢URL
            var finalUrl = url + "?BeginDate=" + beginDate + "&EndDate=" + endDate + "&OrderID_List=" + orderID_List + "&OrderStatus=" + orderStatus;
            
            console.log("Request URL:", finalUrl); // 在開發者工具中查看送出的網址,方便除錯

            var xhr = new XMLHttpRequest();
            xhr.open('GET', finalUrl, true);
            xhr.onreadystatechange = function() {
                if (xhr.readyState == 4 && xhr.status == 200) {
                    var result = JSON.parse(xhr.responseText);
                    displayResults(result);
                }
            };
            xhr.send();
        }

        function displayResults(data) {
            var html = '<table border="1" width="100%">';
            if (data.length > 0) {
                // 處理標題列
                html += '<tr><th>選取</th>';
                for(var j=0; j<data[0].length; j++){
                    html += '<th>' + data[0][j] + '</th>';
                }
                html += '</tr>';

                // 處理資料列
                for (var i = 1; i < data.length; i++) {
                    html += '<tr>';
                    // 第一欄加上 Checkbox
                    html += '<td><input type="checkbox" name="OrderID" value="' + data[i][0] + '"></td>';
                    // 其餘資料欄
                    for(var k=0; k<data[i].length; k++){
                       var cellData = data[i][k];
                       // 處理日期格式
                       if (k === 1 || k === 6) { // 假設第2和第7欄是日期
                           cellData = cellData ? new Date(cellData).toLocaleString() : '';
                       }
                       html += '<td>' + cellData + '</td>';
                    }
                    html += '</tr>';
                }
            } else {
                html += '<tr><td colspan="8">查無資料</td></tr>';
            }
            html += '</table>';
            document.getElementById("order_status").innerHTML = html;
        }
    </script>
</body>
</html>

將 ordering.html 檔案用你的瀏覽器(例如 Chrome、Edge)打開,你就可以開始操作這個系統了! 



2025年8月4日 星期一

【AppSheet 教學】如何取消數字欄位的千分位逗號?(適用於手機號碼、訂單編號)

在 AppSheet 中,當我們將欄位類型設定為 Number 時,系統為了提升數字的可讀性,會自動為超過三位數的數字加上千分位逗號(例如,1000 會顯示為 1,000)。這個功能在處理金額或數量時非常實用。

然而,在某些特定情境下,例如輸入手機號碼、訂單編號或產品代碼時,我們需要的是一串純數字,不希望中間出現任何符號。這時,自動加入的逗號反而會造成困擾。

本篇教學將引導您如何簡單地取消這個預設功能。

問題情境:自動出現的千分位逗號

當您在 App 中建立一個資料欄位,並將其類型(TYPE)設定為 Number 時,一旦輸入的數值大於或等於 1000,AppSheet 就會自動加上千分位符號。

操作範例:
如下圖所示,當我們在「容量(cc)」欄位中輸入 1000 並儲存後,應用程式的預覽畫面會將其顯示為 1,000。

如果這個欄位是用來記錄手機號碼或不應被格式化的編號,這樣的顯示方式就不符合我們的需求。

解決步驟:關閉千分位分隔符號

要移除這個逗號,我們只需要調整該欄位的設定即可。

  1. 進入欄位設定
    在 AppSheet 編輯器中,前往左側的 Data 頁籤,並選擇 Columns。找到您想修改的那個 Number 類型欄位(如此範例中的「容量(cc)」),點擊欄位名稱左邊的鉛筆圖示進入編輯模式。

  2. 取消勾選「Show thousands separator」
    在彈出的設定視窗中,找到 Type Details 區塊。您會看到一個名為 Show thousands separator 的選項,系統預設是勾選的。

    請直接取消勾選這個選項。

  3. 完成並儲存 App
    取消勾選後,點擊右下角的 Done 按鈕關閉設定視窗。最後,務必點擊 AppSheet 編輯器右上角的 Save 按鈕,讓變更正式生效。

結果確認

儲存並等待 App 同步完成後,回到應用程式的預覽畫面查看。您會發現,原本顯示為 1,000 的數字,現在已經變回純數字的 1000,成功移除了千分位逗號。



MSSQL 公用暫時表格CTE(Common Table Expression)

在 SQL Server(MSSQL)中,CTE 是「Common Table Expression」的縮寫,中文常翻作「共用表達式」或「公用暫時表格」。它是一種在查詢中定義暫時結果集的方式,讓你可以寫出更清晰、模組化的 SQL。

使用時機:

 

📘 CTE 的用途與優點

功能說明
✅ 結構清晰把複雜查詢拆成多段,讓邏輯更容易閱讀與維護
✅ 可重複使用可以定義多個 CTE,彼此串接使用
✅ 支援遞迴可用於階層資料(如組織樹、分類樹)的遞迴查詢
✅ 可搭配 INSERT/UPDATE/DELETEMSSQL 支援在 DML 操作中使用 CTE

🧪 基本語法範例

sql
WITH cte_name AS (
  SELECT ...
  FROM ...
  WHERE ...
)
SELECT *
FROM cte_name
WHERE ...

你也可以定義多個 CTE:

sql
WITH cte1 AS (...),
     cte2 AS (...)
SELECT ...
FROM cte1
JOIN cte2 ON ...

🧠 與暫存表的差異

類型是否寫入磁碟是否可重複使用是否支援索引
CTE❌(記憶體中)✅(僅限一次查詢)❌
暫存表(#table)✅✅✅(可加索引)

🕰️ 使用 CTE 的最佳時機

1. 簡化複雜查詢邏輯

  • 當查詢包含多層子查詢或 JOIN,使用 CTE 可以將每一層邏輯分段處理。

  • 例如:先計算銷售總額,再根據總額篩選 VIP 客戶。

2. 遞迴查詢(階層資料)

  • 查詢樹狀結構如部門上下層、分類目錄、員工主管關係。

  • CTE 是 SQL 中唯一支援遞迴的語法(在 MSSQL、PostgreSQL、Oracle 中皆支援)。

sql
WITH RecursiveCTE AS (
  SELECT id, parent_id, name
  FROM categories
  WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.parent_id, c.name
  FROM categories c
  JOIN RecursiveCTE r ON c.parent_id = r.id
)
SELECT * FROM RecursiveCTE;

3. 重複使用中間結果

  • 當某個查詢結果需要在後續多次使用時,CTE 可以避免重複撰寫 SQL。

4. 改善可讀性與維護性

  • 對於多人協作或日後維護的 SQL,CTE 能讓邏輯更清楚,減少錯誤。

5. 搭配 INSERT/UPDATE/DELETE 使用

  • MSSQL 支援在 DML 操作中使用 CTE,讓你可以先篩選資料再進行更新或刪除。

sql
WITH TargetRows AS (
  SELECT *
  FROM orders
  WHERE status = 'pending'
)
DELETE FROM TargetRows;

【教學】如何利用免費 Cloudflare Workers 連接 Google 試算表?透過 Google AI Studio 快速搞定前後端串接!

大家好!在開發輕量級應用程式或個人專案時,許多人喜歡直接將 Google 試算表(Google Sheets) 當作資料庫使用。但過去若直接透過 Google Cloud Platform (GCP) 串接,經常會遇到需要綁定信用卡、權限設定繁瑣或突然調用失敗的問題。 最近有網...