Microsoft Office/建立大學費用分析工作簿
#REF! - 一條錯誤訊息，表明公式存在錯誤，如果公式中包含已刪除的單元格，則可能出現此錯誤訊息。
格式符號 - 與數字一起輸入的符號，例如 $, %，或逗號。在數字中輸入這些符號時，Excel 會自動假設相應的格式。
凍結標題 - 固定一行或一列，以便無論向下或向右滾動多遠，它們始終在螢幕上顯示。
日期戳 - 顯示檔案建立日期。這在商業財務報表中非常重要。
NOW 函式 - 此函式始終顯示今天的日期，或計算機儲存的系統日期。日期實際上儲存為一個數字，表示從 1900 年 1 月 1 日開始的天數。
絕對單元格引用 - 在複製單元格時保持單元格不變。透過新增 $ 符號來實現。$C$15 是一個絕對單元格引用，當您複製包含它的公式（例如，=$C$15 +C16）向下移動一列時，$C$15 會保持不變，而 C16 會隨行號變化。
相對單元格引用 - 當公式被複制時，單元格引用會發生變化。在公式 =$C$15 + C16 中，C16 是相對單元格引用。
混合單元格引用 - 可以告訴 Excel 只保留單元格引用的一部分不變，只需使用一個 $ 符號即可。C$15 和 $C15 都是混合單元格引用。在 C$15 中，行號將保持不變，但列字母可能會發生變化。在 $C15 中，列字母將保持不變，但行號可能會發生變化。
IF 函式 - 一種條件，允許根據測試結果將值分配給單元格。=IF(B7>$B$2, $C$2, 0) 這意味著如果單元格 B7 中的值大於單元格 B2 中的值，則保留單元格 C2 中的值。如果它等於或小於 B2 中的值，則保留值 0。
邏輯測試 - IF 語句中的測試，例如 B7>$B$2。
如果為真，則返回值 - 如果測試的條件為真，則顯示的值。=IF(B7>$B$2, $C$2, 0) 在此示例中，它是 $C$2。
如果為假，則返回值 - 如果測試的條件為假，則顯示的值。=IF(B7>$B$2, $C$2, 0) 在此示例中，它是 0。
比較運算子 - 可以執行六種測試
- < 第一個值小於第二個值
- > 第一個值大於第二個值
- >= 第一個值大於或等於第二個值
- <= 第一個值小於或等於第二個值
- = 兩個值相等
- <> 兩個值不相等
巢狀 IF 函式 - 可以將一個 IF 語句巢狀在另一個 IF 語句中。第二個 IF 語句將位於如果為真或如果為假的區域中。
圖表工作表 - Excel 中只包含圖表的 工作表。
餅圖 - 對整體部分的圖形表示。僅當您要顯示某事物的百分比組成時使用餅圖。每個餅圖切片表示整體的百分比。
爆炸餅圖 - 一個或多個切片從整體中拉出的餅圖。
偏移 - 餅圖中拉出的部分稱為偏移。有時您只想拉出一個切片來顯示您正在討論的區域。
假設分析 - 這被稱為敏感性分析，這只是意味著對於您更改資料的任何單元格，Excel 將重新計算所有公式並重新繪製所有圖表。
目標求解 - 如果您想找到在單元格中獲得所需結果需要發生什麼情況，您可以使用此功能來確定公式所依賴的單元格的值。
EX3 - 費用分析
將以下表格輸入 Excel
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Pizza R Us | |||||||
| 2 | 半年預計毛利、費用和營業利潤 | |||||||
| 3 | 一月 | 總計 | ||||||
| 4 | 銷售額 | $2,212,105.98 | $4,845,562.56 | $6,721,100.32 | $3,025,430.35 | $3,001,272.68 | $5,987,384.14 | |
| 5 | 銷貨成本 | |||||||
| 6 | 毛利 | |||||||
| 7 | ||||||||
| 8 | 費用 | |||||||
| 9 | 獎金 | |||||||
| 10 | 佣金 | |||||||
| 11 | 營銷 | |||||||
| 12 | 研發 | |||||||
| 13 | 支援、一般和行政管理 | |||||||
| 14 | 總費用 | |||||||
| 15 | ||||||||
| 16 | 營業利潤 | |||||||
| 17 | ||||||||
| 18 | 假設資料 | |||||||
單擊 Office 按鈕以儲存工作簿
單擊 Office 按鈕 - 準備為您的姓名和其他相關資訊新增文件屬性
為工作表選擇一個主題
旋轉月份名稱，使其呈 45° 角
單擊行標題 3 以選擇整行。
單擊“主頁”選項卡以使其處於活動狀態
單擊“對齊”組對話方塊啟動器
將方向更改為 45°
單擊“確定”
單擊單元格 B3，即月份 JANUARY
單擊並拖動填充柄到右側，到單元格 G3
注意，Excel 會自動將月份更新到系列中的下一個月份。
還要注意新出現的選項按鈕 - 自動填充選項選單
- 複製單元格
- 填充系列
- 僅填充格式
- 不帶格式填充
- 填充月份
在 Excel 中，有許多不同的複製和貼上方法。
- “主頁”選項卡“剪貼簿”組中的複製和貼上按鈕
- 右鍵單擊並複製和貼上
- CTRL+C 複製，CTRL+V 貼上
- 另一種方法是剪下和貼上，如果您想移動資料。
使用其中一種方法將範圍 A9:A13 複製到 A19:A23。
與填充選項選單一樣，還有一個貼上選項選單，提供相同的選項。
您可以插入單個單元格、單元格區域或整行和整列。不同的方法是
- 右鍵單擊行標題，然後單擊“插入”以插入一行
- 右鍵單擊列標題，然後單擊“插入”以插入一列
- 單擊行標題，然後單擊“開始”選項卡中的“單元格”組中的“插入”。
- 單擊列標題，然後單擊“開始”選項卡中的“單元格”組中的“插入”。
- 您也可以對單個單元格執行相同的操作。
您還可以刪除單元格、單元格區域或整行和整列。當您刪除單元格時，系統會詢問您是否要將單元格向上或向右移動。
插入單元格 A21 並將文字“利潤率”放在新單元格 A21 中。
插入單元格 A24 並將文字“獎金收入”放在新單元格 A24 中。
注意：如果刪除了在公式中使用的單元格，則公式結果將顯示為“#REF!”，您需要修復公式。
輸入“假設資料”
[edit | edit source]B19 = 150,000.00
B20 = 4.5%
B21 = 58%
B22 = 8%
B23 = 6.25%
B24 = 5,250,000.00
B25 = 16.5%
凍結單元格
[edit | edit source]有時，當您處理大型電子表格時，您可能希望看到列和行標題。Excel 中有一個選項允許您在螢幕上凍結行或列。這樣一來，您仍然可以檢視標題，並且可以滾動檢視其餘資料。
要凍結單元格，請執行以下操作：
- 單擊要凍結區域的邊框單元格。在本例中，該單元格為 B4。
- 單擊“檢視”選項卡。
- 單擊“凍結窗格”按鈕。
- 單擊“凍結窗格”，這將凍結列 A 和行 1-3。
顯示系統日期
[edit | edit source]處理資料時，瞭解資料的最新程度非常重要。為此，我們將日期放入我們的表格中。
- 轉到單元格 H2。
- 單擊公式欄中的“插入函式”框。
- 選擇“日期和時間”。
- 在“選擇函式”框中單擊“NOW”。
- 單擊“確定”
- 右鍵單擊單元格 H2。
- 單擊“設定單元格格式”。
- 單擊“數字”選項卡。
- 單擊“日期”。
- 單擊您喜歡的格式（我將選擇 10/27/09）。
- 單擊“確定”
此日期實際上儲存為自 1899 年 12 月 31 日以來的天數。
絕對引用和相對引用單元格
[edit | edit source]在以下公式中，請確保在使用填充柄之前確定哪些引用是絕對引用，哪些引用是相對引用。
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 單元格 | 行標題 | 公式 | 註釋 |
| 2 | B5 | 銷售成本 | =B4*(1-B21) | 這是銷售的所有商品的成本。 |
| 3 | B6 | 毛利 | =B4-B5 | 銷售利潤（不包括任何其他費用）。 |
| 4 | B9 | 獎金 | =IF(B4>=B24, B19, 0) | 如果您的銷售額（B4）大於或等於獎金收入（B24）金額，則發放獎金（B19），否則不發放任何獎金。 |
| 5 | B10 | 佣金 | =B4*B20 | 計算銷售額（B4）的佣金百分比（B20）。 |
| 6 | B11 | 營銷 | =B4*B22 | 計算銷售額（B4）的營銷支出百分比（B22）。 |
| 7 | B12 | 研發 | =B4*B23 | 計算銷售額（B4）的研發支出百分比（B23）。 |
| 8 | B13 | 支援、一般和管理 | =B4*B25 | 計算銷售額（B4）的支援、一般和管理支出百分比（B25）。 |
| 9 | B24 | 總費用 | =SUM(B9:B13) | 1 月份所有支出的總額 |
| 10 | B16 | 營業利潤 | =B6-B14 | 這是您在扣除所有費用後的實際利潤。 |
當您輸入公式時，您有三種選擇，可以決定公式將如何與您引用的單元格互動
- 相對單元格引用 - 這是您一直使用的方法。當您使用填充柄時，單元格引用會發生變化。如果在包含公式“=A3+A5”的單元格上使用填充柄並向右拖動，則新的公式將為“=B3+B5”。如果使用填充柄並向下拖動，則新的公式將為“=A4+A6”。根據拖動方向的不同，行號或列字母會發生變化。
- 絕對單元格引用 - 有時您可能希望公式中引用的單元格在拖動填充柄時不發生變化。（就像上面的公式一樣。）如果以單元格 B5 的公式“=B4*(1-B21)”為例。B4 是該月的銷售額。當您向右拖動時，您希望它發生變化，以便每列都引用其所在月份的銷售額。但是，單元格 B21 旁邊沒有任何內容。因此，您希望該單元格為絕對引用。為此，在 B 和 21 前面放置一個“$”，因此它將變為“$B$21”，或者公式將變為“=B4*(1-$B$21)”
- 混合單元格引用 - 如果您不需要阻止單元格在列和行方向上都更改引用，就像在本例中一樣，您只需將“$”放在您不希望更改的部分。$B21，因此公式將變為“=B4*(1-$B21)”然後引用將始終為 B 列，並且當您向右拖動時，21 不會發生變化。
建立條件 IF 函式語句
[edit | edit source]IF 語句的格式
= if (B4>=$B$24,$B$19,0)
這樣解釋：如果 B4 的內容大於或等於 B24 的內容，則使用 B19 中的值，否則使用 0。
或者
IF(logical_test, Value_if_True, Value_if_False)
您可以測試以下條件：
- = 等於
- < 小於
- <= 小於或等於
- > 大於
- >= 大於或等於
- <> 不等於
對非相鄰單元格求和
[edit | edit source]選擇 H 列中應該求和的單元格。如果單元格不相鄰，則使用 CTRL 鍵。
單擊“自動求和”按鈕以獲取總計。
使用格式刷
[edit | edit source]格式刷的工作原理與在 Microsoft Word 中相同。單擊要複製格式的單元格，然後單擊格式刷（畫筆工具），然後單擊要設定相同格式的單元格。
建立餅圖
[edit | edit source]選擇 B3:G3 和 B16:G16。
插入選項卡。
餅圖 - 三維餅圖
單擊“圖表工具” - “設計”選項卡中的“移動圖表”按鈕。
單擊“新建工作表”單選按鈕。
鍵入“半年期財務預測器”。
單擊“確定”
注意視窗底部的新的圖表選項卡。
新增圖表標題 - “半年期財務預測器”。
下劃線。
關閉圖例 - 從“佈局”選項卡中單擊“圖例”，然後單擊“無”。
新增資料標籤 - 從“佈局”選項卡中單擊“資料標籤” - 選中“類別名稱”和“百分比”。
旋轉圖表 - 從“佈局”選項卡中單擊三維旋轉按鈕 - 選擇角度。
更改圖表的格式 - 右鍵單擊餅圖，然後轉到“設定資料系列格式” - 探索並確保您訪問了“三維格式”以新增輪廓、表面或材質。
嘗試從“設定資料系列格式”視窗中手動更改餅圖扇區的顏色。
右鍵單擊工作表選項卡，然後單擊“重新命名”，將名稱從“SHEET 1”更改為“半年財務預測”。
右鍵單擊工作表選項卡，然後單擊“選項卡顏色”，更改選項卡的顏色，切換活動選項卡以檢視顏色變化。
單擊並拖動工作表選項卡到右側或左側，以更改它們在底部列出的順序。
在列印前始終進行列印預覽！
Office 按鈕 - 列印 - 列印預覽
我使用“頁面設定”按鈕進行格式化，然後從預覽中列印，以確保我得到想要的結果。
在“頁面”選項卡中，您可以從橫向更改為縱向，並將文件調整為一頁。
在“頁邊距”選項卡中，您可以更改頁面的頁邊距，以及水平和垂直居中。
在“頁首/頁尾”選項卡中，您可以在頁面中新增頁首或頁尾。
在“工作表”選項卡中，您可以設定列印區域，設定要在每張紙張的頂部或側面列印的行或列，啟用用於列印的網格線，僅以黑白列印，或以較低的質量列印。
如果您有多個工作表的工作簿，可以一次列印整個工作簿。選擇包含資料或圖表的所有工作表選項卡，然後進行列印預覽和正常列印。在列印預覽中，您需要使用捲軸檢視其他頁面。
由於所有資料都與“假設分析”部分相關聯，您可以更改假設，並在整個工作簿（包括圖表）中觀察變化。
縮放工作表，以便您可以看到整個資料表。
在單元格 B19 中輸入 72,000 - 這會造成什麼變化？為什麼？這意味著什麼？
在單元格 B20 中輸入 3.0 - 這會造成什麼變化？為什麼？這意味著什麼？
在單元格 B25 中輸入 15.25 - 這會造成什麼變化？為什麼？這意味著什麼？
單元格 H16 中發生了什麼？為什麼？
如果您知道想要的結果，但不知道如何獲得，這將非常有用。Excel 的此功能允許您輸入最終結果，它將幫助您計算因變數。
單擊“垂直拆分”框，並在列 E 後拆分螢幕。
調整右側以顯示列 H。
單擊單元格 H16。
單擊“資料”選項卡。
單擊“假設分析”按鈕。
單擊“目標求解”。
按如下方式填寫框：
- 設定單元格 - H16
- 目標值 - 11,000,000
- 透過更改單元格 - $B$25
單擊“確定”
What happened?
玩弄這些值。
完成後，單擊“取消”。
大學費用預測 在當今經濟條件下上大學很困難。提前規劃是緩解尋找這筆鉅額費用所需資金的壓力的關鍵。建立以下表格
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | 科羅拉多州立大學北部 | |||||
| 2 | 費用預測 | |||||
| 3 | 費用 | 大一 | 大二 | 大三 | 大四 | 總計 |
| 4 | 學費 | 公式 #1 | 公式 #1 | 公式 #1 | ||
| 5 | 一般費用 | 公式 #1 | 公式 #1 | 公式 #1 | ||
| 6 | 醫療 | 公式 #1 | 公式 #1 | 公式 #1 | ||
| 7 | 書籍津貼 | 公式 #1 | 公式 #1 | 公式 #1 | ||
| 8 | 食宿 | 公式 #1 | 公式 #1 | 公式 #1 | ||
| 9 | 個人/雜項 | 公式 #1 | 公式 #1 | 公式 #1 | ||
| 10 | 交通 | 公式 #1 | 公式 #1 | 公式 #1 | ||
| 11 | 總費用 | |||||
| 12 | ||||||
| 13 | 資源 | 大一 | 大二 | 大三 | 大四 | 總計 |
| 14 | 儲蓄 | 公式 #2 | 公式 #2 | 公式 #2 | 公式 #2 | |
| 15 | 父母 | 公式 #2 | 公式 #2 | 公式 #2 | 公式 #2 | |
| 16 | 工作 | 公式 #2 | 公式 #2 | 公式 #2 | 公式 #2 | |
| 17 | 貸款 | 公式 #2 | 公式 #2 | 公式 #2 | 公式 #2 | |
| 18 | 獎學金 | 公式 #2 | 公式 #2 | 公式 #2 | 公式 #2 | |
| 19 | 總資源 | |||||
| 20 | ||||||
| 21 | 假設 | |||||
| 22 | 儲蓄 | |||||
| 23 | 父母 | |||||
| 24 | 工作 | |||||
| 25 | 貸款 | |||||
| 26 | 獎學金 | |||||
| 27 | 年度增長率 | 5.3% | ||||
公式 #1 = 上一年費用 * (1 + 年度增長率)
公式 #2 = 年總費用 * 相應的假設
這些假設是你對從每個類別中需要多少百分比來支付大學費用的最佳猜測。
建立總費用的餅圖。
建立總資源的餅圖。
額外學分 - 使用 if 語句，如果總儲蓄高於你已知的儲蓄額，則通知你。
| 標準 | 1 分 | 2 分 | 3 分 | 4 分 | 5 分 |
|---|---|---|---|---|---|
| 準確建立大學費用表 | 缺少 4 個專案 | 缺少 3 個專案 | 缺少 2 個專案 | 缺少 1 個專案 | 100% 準確 - 沒有任何遺漏 |
| 使用邏輯猜測填寫假設 | 缺少 4 個專案 | 缺少 3 個專案 | 缺少 2 個專案 | 缺少 1 個專案 | 100% 準確 - 沒有任何遺漏 |
| 準確使用公式 #1 | 4 個錯誤 | 3 個錯誤 | 2 個錯誤 | 1 個錯誤 | 沒有錯誤 |
| 準確使用公式 #2 | 4 個錯誤 | 3 個錯誤 | 2 個錯誤 | 1 個錯誤 | 沒有錯誤 |
| 準確填寫總計 | 4 個錯誤 | 3 個錯誤 | 2 個錯誤 | 1 個錯誤 | 沒有錯誤 |
| 建立總費用的餅圖 | 4 個錯誤 | 3 個錯誤 | 2 個錯誤 | 1 個錯誤 | 沒有錯誤 - 包括資料標籤和標題 |
| 建立總資源的餅圖 | 4 個錯誤 | 3 個錯誤 | 2 個錯誤 | 1 個錯誤 | 沒有錯誤 - 包括資料標籤和標題 |