Ithy Logo

總務工作的Excel函數公式

提升效率與精準管理的必備工具和技巧

office excel tasks

關鍵重點

  • 數據查詢與匹配功能完善資料管理
  • 條件判斷與邏輯運算提高決策效率
  • 日期管理與預算計算有效規劃資源

數據統計與匯總

SUM、COUNT、AVERAGE

總務工作中經常需要對各類數據進行統計與匯總,Excel提供的SUM、COUNT、AVERAGE等函數可以大大簡化這些任務。

SUM 函數

用途:計算一組數值的總和。

公式:=SUM(範圍)

示例:計算辦公用品的總費用:=SUM(B2:B10)

COUNT 函數

用途:計算範圍內數值的個數(僅計算數字單元格)。

公式:=COUNT(範圍)

示例:計算提交物品申請單的人數:=COUNT(A2:A10)

AVERAGE 函數

用途:計算範圍內數值的平均值。

公式:=AVERAGE(範圍)

示例:計算每月平均支出:=AVERAGE(B2:B10)


資料查詢與匹配

VLOOKUP、HLOOKUP、INDEX + MATCH

在總務管理中,查找和匹配資料是常見的需求。Excel提供多種查找函數來滿足這些需求。

VLOOKUP 函數

用途:在垂直方向上查找特定值並返回相應的數據。

公式:=VLOOKUP(查找值, 查找範圍, 返回列數, [精確匹配/模糊匹配])

示例:根據物品編號查詢物品名稱:=VLOOKUP(A2, B2:D10, 2, FALSE)

HLOOKUP 函數

用途:在水平方向上查找特定值並返回相應的數據。

公式:=HLOOKUP(查找值, 查找範圍, 返回行數, [精確匹配/模糊匹配])

示例:查找某項目的具體費用:=HLOOKUP("場地租賃", A1:E5, 3, FALSE)

INDEX 和 MATCH 函數組合

用途:使用更靈活的方式查找和返回數據,比VLOOKUP更強大。

公式:=INDEX(返回範圍, MATCH(查找值, 查找範圍, 0))

示例:根據商品名稱查找價格:=INDEX(D2:D10, MATCH("筆記本", C2:C10, 0))


行政日期管理

TODAY、DATEDIF、WORKDAY、TEXT

有效的日期管理對於合同、會議和其他行政事務的安排至關重要。Excel的日期函數能夠幫助總務人員高效管理時間相關的任務。

TODAY 函數

用途:返回當天的日期,動態更新。

公式:=TODAY()

示例:顯示當前日期:=TODAY()

DATEDIF 函數

用途:計算兩個日期之間的差異,可以以天數、月數或年數表示。

公式:=DATEDIF(開始日期, 結束日期, "d/m/y")

示例:計算合同到期還剩幾天:=DATEDIF(TODAY(), B1, "d")

WORKDAY 函數

用途:計算從指定日期開始的若干工作日後的日期,排除週末和指定假日。

公式:=WORKDAY(開始日期, 工作日數, [假日範圍])

示例:計算十個工作日後的日期,假日定義在H1:H5:=WORKDAY(TODAY(), 10, H1:H5)

TEXT 函數

用途:將日期以特定格式顯示。

公式:=TEXT(日期, "格式")

示例:將日期顯示為"2025-01-23"格式:=TEXT(A1, "yyyy-mm-dd")


資訊整理與文字處理

CONCATENATE、TEXTJOIN、LEFT/RIGHT/MID、TRIM

總務工作中常涉及到資料的整理和文本的處理,Excel提供的多種文字函數可協助完成這些任務。

CONCATENATE 函數

用途:將多個文本字符串合併為一個字符串。

公式:=CONCATENATE(文本1, 文本2, ...)

示例:合併供應商名稱與聯絡人:=CONCATENATE(I2, " - ", J2)

TEXTJOIN 函數

用途:合併多個單元格內容,並可指定分隔符。

公式:=TEXTJOIN(分隔符, 忽略空白, 範圍)

示例:將申請人清單合併為一個文本串:=TEXTJOIN(", ", TRUE, A1:A5)

LEFT、RIGHT、MID 函數

用途:提取文本中的特定部分。

公式: =LEFT(文本, 類字符數)
=RIGHT(文本, 類字符數)
=MID(文本, 起始位置, 類字符數)

示例:從物品編號中提取前五個字元:=LEFT(A1, 5)

TRIM 函數

用途:去除文本中的多餘空格。

公式:=TRIM(文本)

示例:清理資料中的額外空格:=TRIM(A1)


邏輯運算與決策

IF、COUNTIF、SUMIF、IFERROR、AND/OR

在進行數據分析和決策時,邏輯函數能夠根據條件自動進行判斷和計算,提升工作效率。

IF 函數

用途:根據條件返回不同的值。

公式:=IF(條件, 值1, 值2)

示例:判斷部門申請經費是否超支:=IF(B1>100, "超支", "正常")

COUNTIF 函數

用途:根據條件統計範圍內符合條件的單元格數量。

公式:=COUNTIF(範圍, 條件)

示例:統計已核准的申請表數量:=COUNTIF(B1:B10, "已核准")

SUMIF 函數

用途:對滿足特定條件的數值進行加總。

公式:=SUMIF(範圍, 條件, 加總範圍)

示例:計算分類為「耗材」的商品費用總和:=SUMIF(C1:C10, "耗材", D1:D10)

IFERROR 函數

用途:處理錯誤信息,避免顯示錯誤數據。

公式:=IFERROR(公式, "替代值")

示例:避免除數為零的錯誤:=IFERROR(A1/B1, "錯誤")

AND/OR 函數

用途:實現多條件的篩選和判斷。

公式: =AND(條件1, 條件2)
=OR(條件1, 條件2)

示例:判斷是否同時符合兩個條件:=IF(AND(A1>0, B1<100), "合格", "不合格")


預算與費用計算

PMT、ROUND

預算的制定與費用的計算是總務工作的重要部分,Excel中的相關函數可協助進行精確的財務計算。

PMT 函數

用途:計算貸款或分期付款的金額。

公式:=PMT(利率/期數, 期數*年數, -本金)

示例:計算貸款10萬元,利率5%,分5年每月需還款金額:=PMT(5%/12, 12*5, -100000)

ROUND 函數

用途:將數值四捨五入到指定的小數位數。

公式:=ROUND(數值, 小數位數)

示例:將費用金額保留至小數第二位:=ROUND(A1, 2)


資產管理與折舊計算

SLN 函數

對於固定資產的管理,折舊計算是必要的工作,Excel的SLN函數可以幫助計算直線折舊。

SLN 函數

用途:計算固定資產的直線折舊。

公式:=SLN(原值, 殘值, 使用年限)

示例:計算原值為50,000元,殘值為5,000元,使用年限為10年的年折舊額:=SLN(50000, 5000, 10)


會議室與空間管理

時段重疊檢查、使用率計算

有效管理會議室的使用情況,確保資源的合理分配。

時段重疊檢查

用途:檢查會議時段是否有重疊,避免預訂衝突。

公式:=COUNTIFS(開始時間範圍, "<=" & 結束時間, 結束時間範圍, ">=" & 開始時間)

示例:檢查某時段的重複預訂情況:=COUNTIFS(StartTimes, "<=" & EndTime, EndTimes, ">=" & StartTime)

使用率計算

用途:計算會議室的使用率,評估資源利用效率。

公式:=COUNTIF(使用狀態範圍, "已預約") / 工作天數 * 100

示例:計算會議室的月使用率:=COUNTIF(StatusRange, "已預約") / TotalWorkdays * 100


數據整理與分析

資料排序、重複資料檢查、條件統計

數據的整理與分析是總務工作中不可或缺的一部分,Excel提供了多種函數來協助完成這些任務。

資料排序

用途:對數據進行排序,如排名、優先級等。

公式:=RANK(當前數值, 數值範圍)

示例:對員工績效進行排名:=RANK(B2, B$2:B$10)

重複資料檢查

用途:檢查數據中是否存在重複項目,以維持數據的唯一性。

公式:=COUNTIF(範圍, 查詢值) > 1

示例:檢查員工編號是否重複:=COUNTIF(A:A, A2) > 1

條件統計

用途:根據多個條件對數據進行統計分析。

公式:=COUNTIFS(範圍1, 條件1, 範圍2, 條件2)

示例:統計特定部門在特定月份的費用:=COUNTIFS(DepartmentRange, "財務部", MonthRange, "2025-01")


預算控制與費用分攤

預算達成率、月份費用累計

有效的預算控制和費用分攤對於資源的合理利用至關重要,Excel提供的相關函數能協助實現精確的預算管理。

預算達成率

用途:計算實際支出相對於預算的達成百分比。

公式:=ROUND((實際支出 / 預算金額) * 100, 2) & "%%"

示例:計算預算達成率:=ROUND((B2 / C2) * 100, 2) & "%%"

月份費用累計

用途:計算特定月份內的費用總額。

公式:=SUMIFS(金額範圍, 日期範圍, ">=" & 月初, 日期範圍, "<=" & 月底)

示例:計算2025年1月的費用累計:=SUMIFS(B2:B100, A2:A100, ">=2025-01-01", A2:A100, "<=2025-01-31")


進階函數應用

庫存管理與預警系統

透過進階函數,總務可以更有效地管理庫存,並及時發出補貨預警。

庫存量查詢

公式:=SUMIFS(數量欄位, 品名欄位, "品項名稱")

示例:計算某品項的庫存量:=SUMIFS(D2:D100, C2:C100, "筆記本")

低於安全庫存警示

公式:=IF(庫存量 < 安全存量, "需要補貨", "庫存正常")

示例:判斷筆記本是否需要補貨:=IF(D2 < 50, "需要補貨", "庫存正常")


信息整理與清理

文件編號自動產生、逾期提醒

資訊整理與清理是保持資料整潔和高效運作的關鍵,透過自動化公式可以減少手動操作並降低錯誤率。

文件編號自動產生

公式:=TEXT(ROW(), "000") & "-" & TEXT(TODAY(), "yyyymmdd")

示例:自動生成文件編號:=TEXT(ROW(), "000") & "-" & TEXT(TODAY(), "yyyymmdd")

逾期提醒

公式:=IF(TODAY() > 截止日期, "已逾期", IF(截止日期 - TODAY() <= 7, "即將到期", "正常"))

示例:判斷合同是否逾期:=IF(TODAY() > B2, "已逾期", IF(B2 - TODAY() <= 7, "即將到期", "正常"))


預算與費用管理

各部門費用統計

透過費用統計,總務能夠清晰掌握各部門的經費使用情況,並進行合理調配。

各部門費用統計

公式:=SUMIFS(金額欄位, 部門欄位, "部門名稱")

示例:計算財務部的總費用:=SUMIFS(D2:D100, C2:C100, "財務部")


預算與費用計算總覽

函數名稱 用途 示例公式
SUM 計算數值總和 =SUM(B2:B10)
COUNTIF 統計符合條件的數量 =COUNTIF(B1:B10, "已核准")
VLOOKUP 垂直查找並返回對應數據 =VLOOKUP(A2, B2:D10, 2, FALSE)
IF 條件判斷 =IF(B1>100, "超支", "正常")
PMT 計算分期付款金額 =PMT(5%/12, 12*5, -100000)

結論

在總務工作中,熟練運用Excel函數公式能夠顯著提升工作效率和數據管理的精確性。無論是數據統計、資料查詢、日期管理,還是預算控制與費用計算,合理選用和組合Excel函數都是實現高效總務管理的關鍵。建議總務人員根據實際工作需求,不斷學習和應用各類函數,以達到最佳的工作效果。


參考資料


Last updated January 23, 2025
Ask me more