精通Excel公式,提升工作效率與數據分析能力
在日常工作當中,Excel是最常使用到的軟體之一,但是經常在要處理數據報告時,常因爲公式不夠熟練,導致產出報告慢了別人好幾步。
本篇文章,將帶你學習和掌握Excel公式,提高個人效率、進行數據分析。Excel公式能夠幫助使用者快速計算和分析數據,從簡單的日常任務到複雜的業務分析,都有相對應的使用情景。無論是財務報表、市場研究、項目規劃,還是庫存管理,Excel公式都扮演著不可或缺的角色。精通這些公式,能夠讓你事半功倍,不必再加班做報表。
本篇使用說明
▶ 備註1: 本篇文章可以善用ctrl+F 快速查找公式。
▶ 備註2: 語法解讀示例:
假設有一個公式的語法是:FUNCTION(必需參數, [可選參數])。
▶ 必需參數:這是在使用該函數時必須提供的資料。缺少這些資料,函數將無法正確執行。
▶ [可選參數]:這表示該參數是選擇性的。您可以根據需要添加這些參數來擴展或調整函數的功能。如果您選擇省略這些參數,函數仍然會按照預設的方式運行。
1. 計算統計函數
計算統計函數是分析數據集中趨勢、分散度及形態特徵的基礎。Excel提供了一系列強大的統計函數,今天幫讀者盤點最常使用到的幾個函數包括:
函數名稱 | 說明 | 語法 |
---|---|---|
COUNT | 計算範圍內含數字的單元格數量 | COUNT(選取計算的範圍) |
COUNTA | 計算範圍內非空單元格的數量 | COUNTA(選取計算的範圍) |
COUNTIF | 根據指定條件計算範圍內符合條件的單元格數量 | COUNTIF(選取計算的範圍, 判斷條件) |
COUNTIFS | 根據多個條件計算範圍內符合所有條件的單元格數量 | COUNTIFS(判斷範圍1, 判斷條件1, [判斷範圍2, 判斷條件2]…) |
MAX / MIN | 分別找出一組數據中的最大值和最小值 | MAX(選取計算的範圍) / MIN(選取計算的範圍) |
MAXIFS | 根據一個或多個條件查找範圍中的最大值 | MAXIFS(最大值範圍, 判斷範圍1, 判斷條件1, [判斷範圍2, 判斷條件2]…) |
SUM | 計算範圍內所有數值的總和 | SUM(選取計算的範圍) |
SUMIF | 根據指定條件對範圍內的數值進行求和 | SUMIF(選取計算的範圍, 判斷條件, [求和範圍]) |
SUMIFS | 根據多個條件對範圍內的數值進行求和 | SUMIFS(求和範圍, 判斷範圍1, 判斷條件1, [判斷範圍2, 判斷條件2]…) |
SUMPRODUCT | 計算範圍中對應元素乘積的總和 | SUMPRODUCT(陣列1, [陣列2], [陣列3], …) |
2. 邏輯判斷函數
邏輯判斷函數能夠對數據進行條件判斷,從而執行不同的計算或返回特定的值。以下是一些基本的邏輯判斷函數及其用法:
函數名稱 | 說明 | 語法 |
---|---|---|
IF | 根據給定條件的真偽來返回指定的值。如果條件為真,則返回一個值;如果為假,則返回另一個值。 | IF(條件, 值如果為真的回傳結果, 值如果為假的回傳結果) |
AND | 測試多個條件是否全部為真。只有當所有條件同時滿足時,函數才返回TRUE;否則返回FALSE。 | AND(條件1, 條件2, …) |
OR | 測試一組條件是否至少有一個為真。如果任何一個條件為TRUE,則函數返回TRUE;如果所有條件都為FALSE,則返回FALSE。 | OR(條件1, 條件2, …) |
NOT | 用於反轉條件的真偽值。如果條件為TRUE,則返回FALSE;如果條件為FALSE,則返回TRUE。 | NOT(條件) |
TRUE | 不需要任何參數,直接返回布爾值TRUE。 | TRUE() |
FALSE | 不需要任何參數,返回布爾值FALSE。 | FALSE() |
IFERROR | 檢查第一個參數的計算結果,如果不是錯誤,則返回該結果;如果是錯誤,則返回第二個參數指定的值。 | IFERROR(值, 值如果錯誤) |
SWITCH | 評估一個表達式,並根據該表達式的值返回不同的結果。能夠減少需要多個IF函數的複雜公式。 | SWITCH(表達式, 比較值1, 結果1, [比較值2, 結果2, …], [預設值]) |
3. 查閱參照函數
Excel的查閱參照函數是數據分析和處理中不可或缺的工具,它們提供了強大的能力來搜索、比較和引用數據。以下是一些基本的查閱參照函數及其用法:
函數名稱 | 說明 | 語法 |
---|---|---|
VLOOKUP | 在表格的第一列中查找某個鍵值,並返回同一行中的指定列的值。這是垂直查找。 | VLOOKUP(查找值, 範圍, 列索引號, [範圍查找]) |
HLOOKUP | 在表格的第一行中查找某個鍵值,並返回同一列中的指定行的值。這是水平查找。 | HLOOKUP(查找值, 範圍, 行索引號, [範圍查找]) |
INDEX | 根據行號和列號返回範圍中的特定值。 | INDEX(範圍, 行號, [列號]) |
MATCH | 在範圍內查找特定值,並返回該值在範圍中的相對位置。 | MATCH(查找值, 範圍, [匹配類型]) |
OFFSET | 根據指定的起點(參照點)、偏移量和大小返回一個範圍。 | OFFSET(參照, 行偏移, 列偏移, [高度], [寬度]) |
4. 日期時間函數
Excel的日期時間函數讓處理和分析基於時間的數據變得輕而易舉。無論是計算日期差異、提取特定時間單位的值,還是確定特定日期的星期數,這些函數都是非常實用的工具。以下是一些基本的日期時間函數及其用法:
函數名稱 | 說明 | 語法 |
---|---|---|
DATEDIF | 計算兩個日期之間的差異。 | DATEDIF(起始日期, 結束日期, “單位“) |
SECOND | 從時間值中提取秒。 | SECOND(時間) |
MINUTE | 從時間值中提取分鐘。 | MINUTE(時間) |
HOUR | 從時間值中提取小時。 | HOUR(時間) |
DAY | 從日期值中提取天。 | DAY(日期) |
WEEKDAY | 返回給定日期是星期幾。 | WEEKDAY(日期, [返回類型]) |
WEEKNUM | 返回給定日期屬於當年的第幾周。 | WEEKNUM(日期, [返回類型]) |
MONTH | 從日期值中提取月份。 | MONTH(日期) |
YEAR | 從日期值中提取年份。 | YEAR(日期) |
5. 其他精選函數功能
除了之前提及的計算、邏輯判斷、查閱參照、日期時間函數之外,Excel還提供了許多強大的公式,可以用於處理各種數據分析和處理任務。以下是一些精選的常用公式及其用法:
函數名稱 | 說明 | 語法 |
---|---|---|
CONCATENATE / CONCAT | 用於連接兩個或多個字符串。CONCAT是在較新版本的Excel中引入的,用於替代CONCATENATE。 | CONCATENATE(文字1, [文字2], …) / CONCAT(文字1, [文字2], …) |
LEFT | 從字符串的左側返回指定數量的字符。 | LEFT(文字, [數字]) |
RIGHT | 從字符串的右側返回指定數量的字符。 | RIGHT(文字, [數字]) |
MID | 從字符串的中間返回指定數量的字符。 | MID(文字, 開始位置, 數字) |
LEN | 返回字符串的字符數。 | LEN(文字) |
TRIM | 刪除文字前後的所有空格,並將文字中間的多個連續空格替換為單一空格。 | TRIM(文字) |
LOWER | 將文本轉換為全部小寫字母。 | LOWER(文字) |
UPPER | 將文本轉換為全部大寫字母。 | UPPER(文字) |
PROPER | 將文本轉換為首字母大寫的形式。 | PROPER(文字) |
TEXT | 根據指定的格式將數值轉換為文本。 | TEXT(值, 格式文本) |
VALUE | 將文本轉換為數字。 | VALUE(文字) |
SUBSTITUTE | 在文字中替換新文字舊有的某部分文字。 | SUBSTITUTE(文字, 舊文字, 新文字, [替換次數]) |
結語:
掌握上述介紹的Excel公式將對您的數據處理和分析能力大有裨益。更重要的是,通過將這些函數互相結合應用,您可以極大地拓展Excel的功能,從而實現更加複雜和動態的數據處理。此外,我們還鼓勵讀者在遇到陌生的公式或需要進一步優化公式時,積極探索AI工具的應用,例如ChatGPT,它可以幫助您快速獲得有效的公式解決方案(延伸閱讀:讓 ChatGPT 幫你寫 Excel 公式!3個應用範例提升工作效率)。
同時,若還能應用Excel的樞紐分析功能,面對未來任何報表挑戰都能無往不利(延伸閱讀:什麼是Excel樞紐分析?本篇5分鐘讓初學者快速上手!)
總而言之,若能持續學習和應用這些Excel公式和應用技巧,將為你提高工作效率,進而在職場中展現出更加出色的表現和價值,為你的職涯加分!
Excel 延伸系列文章:
Excel必學函數Vlookup用完就忘?讓GPT幫你2分鐘搞定公式
【Excel 基礎】學會這6個 Excel 小技巧,立刻提升工作效率
【Excel 基礎】別再花時間調整格式了!這些Excel技巧學起來,比同事早1小時下班!
【Excel圖表】「這樣」調整,就被誇專業了!3個小技巧讓你的數據表達更到位
【Excel樞紐分析】階層分析:怎麼從銷售數字、成本、時間看出更有料的分析?
【Excel樞紐分析】報表製作:1篇學會製作YoY、MoM等營收成長率報表
【Excel樞紐分析】顯示詳細資料:免用篩選,1鍵就能看出訂單組成的超強技巧!
【Excel樞紐分析】1招聚焦資料重點!善用欄標籤與列標籤立即變專業
【Excel樞紐分析】一鍵生成多張報表,節省9成以上工作時間
有沒有實際的操作步驟跟範例呢?更多教學在這裡!
針對商業圖表製作與設計,歡迎參考先行學院最新線上課程:解決90%工作情境的:Excel商業圖表製作與設計
先行智庫為台灣管理顧問公司,服務內容包含企業內訓、顧問諮詢以及領導管理,了解更多企業服務內容:https://kscthinktank.com.tw/custom-training/