Office application
【Excel教學】入門篇：理解Excel的基本公式、函數！
【Excel教學】入門篇：5分鐘理解Excel的基本公式、函數！
Excel has functions for creating worksheets, editing data (including modifying, copying, and deleting), performing calculations (such as using formulas and functions), managing file access, and printing worksheets. It can be used in many places, such as creating lists with personal information or creating purchase orders with different data. Most importantly, it's very easy to use! If you haven't learned how to use this simple computer software yet, keep reading! Here's a guide to some basic Excel functions and formulas.
1. 儲存格
First, each cell has a name, which consists of a letter and a number. In spreadsheets, the vertical rows are called "rows" and are represented by letters; the horizontal columns are called "columns" and are represented by numbers. The letters and numbers are sequentially numbered, so the top left corner cell is A1, the cell directly to the right of A1 is B1, and the cell directly below A1 is A2. The cell directly below B1 is B2. "A1, A2, B1, B2" are the names of the cells. To refer to A1, you would type "A1," and to refer to more than one cell, you would type:
- To indicate A1 to A3, type "A1:A3," where ":" means "to."
- 如果要表示A1、A2、B1、B2四格，它們是一個正方形（長方形都一樣），所以要表示的句子是「由最右上至最左下的儲存格」，即可以打「A1:B2」
- 如果要表示A1和A3，就打「A1,A3」，「,」就是「和」的意思。（如果要表示A1、A3和B1，就打「A1,A3,B1」。）
- To refer to the entire A row, use "A:A," which means A row to A row.
- 同樣，如果要表示1全列，就打「1:1」
To indicate A1 to A3, type "A1:A3," where ":" means "to."
如果要表示A1、A2、B1、B2四格，它們是一個正方形（長方形都一樣），所以要表示的句子是「由最右上至最左下的儲存格」，即可以打「A1:B2」
To refer to the entire A row, use "A:A," which means A row to A row.
如果要表示1全列，就打「1:1」
如果要把數字相加，公式是「=SUM(儲存格)」
減數功能（MINUS）就是：「=儲存格1-儲存格2」
想把數字相乘，公式就是「=MULTIPLY(儲存格1,儲存格2)」
如要儲存格1除以儲存格2，公式是：「=儲存格1/儲存格2」
如果要一個儲存格的數字除以一個指定數字（以「2」為例），公式是：「=儲存格/2」
2. Addition, subtraction, multiplication, and division formulas
2.1 加數
所有公式都是從「=」開始的。如果要把數字相加，公式是「=SUM(儲存格)」，其中儲存格就是以前面提到的方式來表示要包括的儲存格。打完公式後按ENTER，有公式的格字就會變成答案。
2.2 減數
- The MINUS function in Microsoft Office Excel is "=cell1-cell2," and the technique is the same as above.
- If you are using Google Excel and want to subtract numbers, the formula is "=MINUS(Cell1, Cell2)", enter the formula and press Enter to get the value of Cell1 minus Cell2.
2.3 multiplier
- 和減數一樣，如要儲存格1乘儲存格2，公式是：「=儲存格1儲存格2」；如果要一個儲存格的數字乘一個指定數字（以「3」為例），公式是：「=儲存格3」。
- If you're using Google Sheets and want to multiply numbers, the formula is "=MULTIPLY(CELL1,CELL2)," and pressing Enter after typing the formula will give you the value of CELL1 multiplied by CELL2. Similarly, you can choose to multiply by a specific number.
2.4 除數
- As with the multiplication mode, to divide cell 1 by cell 2, the formula is: "=cell 1 / cell 2"; if you want to divide a cell's number by a specified number (3 in this case), the formula is: "=cell / 3".
- 如果你是用Google Excel，想把數字相乘，公式就是「=DIVIDE(儲存格1,儲存格2)」，打完公式後按ENTER，就會得出儲存格1除以儲存格2的數值。同樣，你也可以選擇除數或被除數是一個指定數字。
3. Time adjustment
[常用]>[儲存格]>[格式]>[儲存格格式]（Format Cells…）其實，時間加減和普通加減大同小異。例如儲存格A1為「6:45」（表示6小時45分鐘），儲存格A2為「9:30」，計算時間總和，可輸入公式「=A1+A2」，會顯示答案「16:15」。
However, if the sum exceeds 24, such as "12:45 + 15:30," the answer will be 04:15, which is the result of 12:45 AM plus 15 hours and 30 minutes, or 4:15 AM. If you want to know the answer for "12 hours 45 minutes plus 15 hours 30 minutes," it would be:
- 按寫著公式的儲存格
- 在[常用] 索引標籤的 [儲存格] 群組中，選擇 [格式]（Format），然後選擇 [儲存格格式]。
- In the Format Cells… box, select Custom from the Category list.
- In the [Type] box at the top of the format list, enter [h]: mm; @, and then select [OK].
The result is 28 hours and 15 minutes. The next time you need it, this format will be in the [Type] list.
When subtracting time, the formula is the same ("=A1-A2") but you need to select [h:mm] from the [Type] list. If you want to subtract more than 24 hours, use the following formula:
- 在格式清單頂端的 [類型] 方塊中，輸入「m/d/yyyy h:mm AM/PM」
- 在要加的儲存格中，輸入日期，包括月/日/年與時間 (使用 “a” 代表 AM 或 “p” 代表 PM)。
- Use the formula = (cell2 - cell1) * 24.
4. IF function
接下來一跳就到了IF函數的使用了，IF函數的能做的東西是回答「真（是）」和「假（否）」，例如，我要知道「A1是不是大於10」，公式是「=IF(A1>10,TRUE,FALSE)」，其中：
- "A1>10" means "A1 is greater than 10."
- "TRUE" means "true" if true, and "FALSE" if false
- "FALSE" means "FALSE" if false
所以，假如A1是10或以下，答案會是FALSE；假如A1是11或以上，答案會是TRUE。
然後，如個A1不是數字呢？如果A1可能是Red, Blue或者Green，而我要知道A1是不是Red，打「=IF(A1=”Red”,TRUE,FALSE)」，其中：「A1=”Red”」表示「A1是Red這個字」（要有”引號”），即假如A1是Red，答案會是TRUE；假如A1是Red以外的字眼，答案會是FALSE。
5. 其他函數
AND函數：「=IF(AND(條件1,條件2),TRUE,FALSE)」，要同時滿足條件1和2才會TRUE
OR函數：「=IF(OR(條件1,條件2),TRUE,FALSE)」，只要同時滿足條件1或2就會TRUE
NOT函數：「=IF(OR(條件1),TRUE,FALSE)」，如果滿足了條件就會FALSE
For example, A1 cell is 5, and condition 1 is "A1 > 0" and condition 2 is "A1 > 10":
- 使用以上的AND函數，答案是FALSE，因為只滿足了條件1
- Using the OR function above, the answer is TRUE because condition 1 is met.
- 使用以上的NOT函數，答案是FALSE，因為滿足了條件1
6. Other simple and commonly used formulas
- 由儲存格1至儲存格n的平均值：「=AVERAGE(格子1:格子n)」
- 由儲存格1至儲存格n的最大值：「=MAX(格子1:格子n)」
- 由格子1至格子n的最小值：「=MIN(格子1:格子n)」
7. 常見問題
7.2. Why is #NAME? How can I fix the error? 3. Version 1.4.7 4. Regular updates: Continuous feature improvements 5. Iteration and fixes (optional): After the developer reviews the results, further modification requests may be made, such as "Please add an email field for user objects," and the process above is then repeated for iterative development.
公式中之所以出現 #NAME? 錯誤出現在公式中是因為公式名稱有錯字，例如串錯字，把「COUNTIF」串成「CONTIF」，如果仍然顯示錯誤，可以按此查看 #NAME? 錯誤的其他可能原因。
7.3. 為何會顯示 #### ？如何修正錯誤？
當Excel 的欄寬不足，無辦法顯示整個儲存格的內容時，就會顯示 #####。如果想增加欄寬，完整地顯示儲存格內容，可以按兩下欄標題的右邊緣，或者將儲存格拖曳至您想要的寬度。
另外，如果日期和時間為負值的公式時，也會顯示為 #####。
Summarize
希望大家看完之後會對簡單的Excel公式和基本函數有些理解，其實Excel的公式也是十分簡單，只要知道格式能輕易理解公式的用法，一理通百理明，希望Excel都能幫助到大家工作！