Office application
Excel Top 10 Common Formulas Tutorial
Excel Top 10 Common Formulas Tutorial
Excel is one of the most commonly used tools in the workplace, and mastering these commonly used formulas can significantly improve work efficiency. This article will introduce 10 of the most useful Excel formulas and provide practical application examples.
1. SUM – 加總
Basic summation functions that quickly calculate the sum of multiple values.
Calculate the sum of all values in cells A1 to A10.
2. AVERAGE – 平均值
計算一組數值的平均值。
計算B2到B20儲存格中所有數值的平均值。
3. IF – 條件判斷
根據條件返回不同的值。
If C1 is greater than 60, display "Pass", otherwise display "Fail".
4. VLOOKUP – 垂直查詢
Vertical lookup of specific values in a table.
在A1到B100範圍內查找D1的值，並返回第二列對應的值。
5. CONCATENATE / & – 合併文字
將多個文字串合併為一個。
或
將E1和F1的內容合併，中間加一個空格。
6. COUNTIF – 條件計數
Calculate the number of cells that meet specific conditions.
計算G1到G100中值大於50的儲存格數量。
7. SUMIF – 條件加總
Summarize values that meet specific criteria.
When the values in H1 to H100 are "Apple", add up the corresponding values in I1 to I100.
8. INDEX + MATCH – 進階查詢
比VLOOKUP更靈活的查詢組合。
Find the value of J1 in A1 to A100 and return the value in the corresponding position in B1 to B100.
9. TEXT – 格式化文字
將數值轉換為特定格式的文字。
Format the date in K1 as "year-month-day" format.
10. ROUND – 四捨五入
Round up the value to the specified decimal places.
將L1中的數值四捨五入到小數點後兩位。
Tips
Use ChatGPT to quickly generate Excel formulas! Simply describe the function you want, and AI will provide appropriate formula suggestions.)
Excel can also be used to create barcodes for easy inventory management.
結語
掌握這10個常用Excel公式，你就能應付大多數的數據處理需求。記得多加練習，這些公式會成為你職場上的得力助手！