在 Excel 中常見到使用滑鼠、捲軸、鍵盤等各種方式來移至某一列,不過當要前往的某一列距離目前較遠,則在操控上比較不容易,往往速度較慢或是要來來回回的反覆操作。
你可以試著這樣做:
1. 按一下 Ctrl+G 鍵,顯示「到」對話框。
2. 在[參照位址]文字方塊中,輸入約略的儲存格位址,本例為「A250」。
3. 按下[確定]按鈕後,會立即選取這個儲存格(移動到這個儲存格位置)。
在 Excel 中常見到使用滑鼠、捲軸、鍵盤等各種方式來移至某一列,不過當要前往的某一列距離目前較遠,則在操控上比較不容易,往往速度較慢或是要來來回回的反覆操作。
你可以試著這樣做:
1. 按一下 Ctrl+G 鍵,顯示「到」對話框。
2. 在[參照位址]文字方塊中,輸入約略的儲存格位址,本例為「A250」。
3. 按下[確定]按鈕後,會立即選取這個儲存格(移動到這個儲存格位置)。
網友問到:在 Excel 中有一個日期和數量的數列會不斷的增加,如果想要將前一月的某一天(例如:25日)之前的日期自動以不同色彩顯示,該如何處理?(參考下圖)
因為要自動設定為不同的色彩,最方便的做法還是要藉助「設定格式化的條件」來處理,參考以下的做法。
【例】將前一個月的25日(含)以前日期設為紅色
1. 選取儲存格A2:B18。
2. 按一下[樣式/設定格式化的條件]之下的「管理規則」。
網友問到:在 Excel 中有一個日期時間的數列中,如果要調整日期時間,例如增加 10 秒或是增加 10 分,該如何處理?
儲存格B2:=A2+1/24/60/60*10
儲存格C2:=A2+1/24/60*10
複製儲存格B2:C2,往下各列貼上。
因為在 Excel 的日期時間表示法中,以 1 代表 1 天,所以 1 小時為 1/24,1 分鐘為 1/24/60,1 秒鐘為 1/24/60/60。透過加上/減掉特定的數值,即可產生各種時間。
在 Excel 中製作一個圖表,其同一個數列的色彩都是預設為相同色彩(參考下圖),如果想要讓正負值以不同色彩表示,該如何處理?
1. 在圖表中點選一個數列。
2. 按一下右鍵,選取[數值資料格式]選項。
3. 在[填滿]標籤下勾選「負值以補色顯示」。
4. 如果不想要以補色表示負值,則自行在[填滿色彩]區中設定第二個色彩。(參考下圖)
在 Excel 2013 中設計一個統計圖表,可以輕鬆設定:圖表項目、圖表樣式、圖表篩選,當你在編輯一個圖表時,在圖表右上角的三個圖示,讓你可以快速切換及設定圖表。你可以使用 Ctrl 鍵來切換。
(1) 圖表項目
在此勾選圖表中要顯示/隱藏的項目。
(2) 圖表樣式
提供一些圖表的預設樣式,使用捲軸可以挑選各種樣式。
在 Excel 2013 中有一個新功能:快速填入,例如,你要將姓名中的「姓」和「名」分離,快速填入可以幫助你完成。(參考下圖)
1. 先在儲存格B2中輸入A欄中姓名的「姓」。
2. 選取儲存格B2:B12。
3. 按一下[資料/快速填入]指令。(或按一下 Ctrl + E 鍵。)
Excel 會仿照儲存格B2的內容,幫你將儲存格B3:B12填入「姓」的部分。你可以使用這個方法將「名」的部分填入。
在 Excel 中同時開啟多個活頁簿,將活頁簿並排顯示,或是將一個活頁簿新增視窗,再將這二個視窗並排檢視,在 Excel 2013 作了一些改變。
在 Excel 2010 中,多個活頁簿共同一個視窗:
在 Excel 2013 中,每個活頁簿有各自獨立的視窗。當你使用雙螢幕時,這樣更容易同時在兩個活頁簿上進行工作。
試著在 Excel 2010 的某一個活頁簿上新增視窗,結果如下:
在 Excel 中有許多的輸入輔助功能,幫助使用者能加速輸入的速度。在 Excel 2013 中可以用以下的方式讓 Excel 自動幫你完成輸入動作。
例如:在一個姓名清單中,要快速取出名字的部分,你可以這樣做。
1. 在儲存格B2中輸入A欄中姓名的名字部分,本例為:采燁。
2. 在儲存格B3中輸入A欄中姓名的名字部分,本例為:沅瑩。
當你輸入完「沅瑩」二字時,即會在B欄顯示預覽結果(每個儲存格都已取出名字的部分)。
3. 按一下 Enter 鍵,即可完成每個儲存格的名字輸入。
在 Excel 2013 中有一個新功能,當你選取了一個資料範圍,在右下角會顯示一個快速分析工具圖示(參考下圖)。它會列出你最常用的一些操作和分析時實用的相關工具,讓你減少操作步驟,提升使用效能。
只要將滑鼠停留在這個圖示上,即會顯示分析工具。或是在選取資料範圍後,按一下 Ctrl + Q 鍵,顯示這個分析工具。
快速分析工具有「格式設定、圖表、總計、表格、走勢圖」等五類,點選每一類的名稱,會顯示可用之工具。當滑鼠移至任一項工具上,資料範圍即可預覽該工具的結果。使用向右鍵或向左鍵,可以檢視更多的工具。
(下圖顯示當滑鼠停留在「列加總」時,資料範圍旁即顯示每列的加總預覽結果)
Office 2013 快要和大家見面了,先把 Excel 2013 的函數整理一下,以備不時之需。
以下資料源自微軟網站
http://office.microsoft.com/zh-tw/excel-help/HA102752955.aspx?CTT=3
統計函數 (參照)
BETADIST 函數 | 傳回 Beta 累加分配函數 |
BETAINV 函數 | 傳回指定 Beta 分配之累加分配函數的反函數值 |
BINOMDIST 函數 | 傳回在特定次數的二項分配實驗中,實驗成功的機率 |
CHIDIST 函數 | 傳回卡方分配的單尾機率 |
CHIINV 函數 | 傳回卡方分配之單尾機率的反函數值 |
CHITEST 函數 | 傳回獨立性檢定的結果 |
CONFIDENCE 函數 | 傳回母體平均值的信賴區間 |
COVAR 函數 | 傳回共變數,即成對誤差乘積的平均值 |
CRITBINOM 函數 | 傳回累加二項分配小於或等於臨界值的最小值 |
EXPONDIST 函數 | 傳回指數分配 |
FDIST 函數 | 傳回 F 機率分配 |
FINV 函數 | 傳回 F 機率分配的反函數值 |
FTEST 函數 | 傳回 F 檢定的結果 |
GAMMADIST 函數 | 傳回伽瑪分配 |
GAMMAINV 函數 | 傳回伽瑪累加分配的反函數值 |
HYPGEOMDIST 函數 | 傳回超幾何分配 |
LOGINV 函數 | 傳回對數常態累加分配函數的反函數值 |
LOGNORMDIST 函數 | 傳回累加對數常態分配 |
MODE 函數 | 傳回資料集中最常見的值 |
NEGBINOMDIST 函數 | 傳回負二項分配 |
NORMDIST 函數 | 傳回常態累加分配 |
NORMINV 函數 | 傳回常態累加分配的反函數值 |
NORMSDIST 函數 | 傳回標準常態累加分配 |
NORMSINV 函數 | 傳回標準常態累加分配的反函數值 |
PERCENTILE 函數 | 傳回範圍中第 K 個百分位數的值 |
PERCENTRANK 函數 | 傳回值在資料集中的百分比排名 |
POISSON 函數 | 傳回波氏分配 |
QUARTILE 函數 | 傳回資料集的四分位數 |
RANK 函數 | 傳回數字在一數列中的排名 |
STDEV 函數 | 根據樣本來估算標準差 |
STDEVP 函數 | 根據整個母體來計算標準差 |
TDIST 函數 | 傳回 Student T 分配 |
TINV 函數 | 傳回 Student T 分配的反函數值 |
TTEST 函數 | 傳回與 Student T 檢定相關的機率 |
VAR 函數 | 根據樣本來估算變異數 |
VARP 函數 | 根據整個母體來計算變異數 |
WEIBULL 函數 | 傳回 Weibull 分配 |
ZTEST 函數 | 傳回 Z 檢定的單尾機率值 |
Cube 函數
在 Excel 中的一個資料表(如下圖),如果要將 A1, A2, A3, A4 相乘,但不希望空白儲存格也併入相乘,該如何處理?
一般如果你輸入公式「儲存格E2:=A2*B2*C2*D2」,會將空白儲存格以 0 計算。
複製儲存格E2,往下各列貼上。
其實你只要使用 PRODUCT 函數即可解決這個問題,因為 PRODUCT 函數在執行乘積時會跳過空白的儲存格。這和 AVERAGE 函數一樣,能在計算平均時不會將空白儲存格併入計算。
儲存格E2:=PRODUCT(A2:D2)
同事問到:在 Excel 中,有一個資料表須依由左而右、由上而下輸入資料,並且在中間會跳過 2 欄,如何才能快速輸入資料呢?(參考下圖)
我提供了一種可行做法:
1. 選取 C 欄和 D 欄,按一下右鍵,選取「隱藏」指令。(隱藏C 欄和 D 欄)
2. 選取功能表的[檔案/選項]功能,開啟[Excel 選項]對話框。
在 Excel 中,如果要拿來記錄請假時數,並且統計各類假別的累計時數,該如何處理?
請假時以小時數登記,統計時以 8 小時為一天計。
(一)請假時數小計
儲存格F2:=INT(C2/8) & "天" & MOD(C2,8) & "時"
複製儲存格F2,往下各列貼上。
在 Excel 中常會有一些數列需要分組小計,以下列舉二種公式可以對固定間隔位置的數列加總小計,例如每5個數為一組。(參考下圖)
(1) 使用 INDIRECT
儲存格E1:=SUM(INDIRECT("B" & (ROW(B1)-1)*5+1 & ":B" & (ROW(B1)-1)*5+5))
複製儲存格E1,貼至儲存格E1:E5。
(ROW(B1)-1)*5+1:往下複製時會產生 1、6、11、…。
有同事問到:如果要產生許多個一組6個字的亂數字串,該如何處理可以比較快?
在 Excel 中可以運用亂數函數來產生亂的效果。例如:
儲存格A1:=MID("ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789",INT(RAND()*62+1),1)
複製儲存格A1,貼至儲存格A1:F1。
INT(RAND()*62+1):產生 1 ~ 62 的亂數。(大寫字母、小寫字母、數字等合計62個字)
藉由 MID 函數取出 62 個字中的某一個。
在 Excel 中,如果想要計算各月不含星期六和星期日的天數,該如何處理?(參考下圖)
【公式解析】
儲存格C3:=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT("A" & DATE(A3,B3,1) & ":" & "A" & DATE(A3,B3+1,1)-1)),2)<6))
複製儲存格C3,貼至儲存格C3:C14。
DATE(A3,B3,1):找出每月第1天所代表的數值
最近幫學校要設計一個可以用學生證記錄上學遲到學生學號,可以自動產生學生基本資料及入校時間的資料表(參考下圖):
進而產生每週遲到學校數量的統計分析表(參考下圖):
要如何來設計呢?參考以下的做法:
學生的基本資料如下圖,首先,選取資料表中所有的資料(例如儲存格B1:F1000),將其定義名稱:Data。
最近高速公路計程收費的問題吵得火熱,在高速公路局也有列出每個交流道的里程。(參考下圖左)
高速公路交流道一覽表:http://www.freeway.gov.tw/Publish.aspx?cnid=1288&p=215
現在,要依這個交流道和里程的對照表,要來求出每二個交流道之間的里程,用來試算收費金額。(參考下圖右)
1. 選取交流道和里程的整個資料表,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」選項,定義名稱:交流道、里程。
有網友問到:如果有 10 個數字的數列,要從中間挑出來最大的 5 個,並且標示不同色彩,該如何處理?(參考下圖)
一般這種問題,大多使用設定格式化條件來解決。參考以下的步驟:
1. 選取儲存格A1:A11,按一下 Ctrl+Shift+F3 鍵,定義名稱:數列。
2. 選取「常用/樣式」區中的「設定格式化的條件」選項。
3. 選取「新增規則」項目。
在下圖中有多列數字組成的數列,現在希望能在每一列中找出含有數字 2, 4, 7, 13, 19 的個數,該如何處理?
透過陣列的使用,可以用簡短的公式來完成:
儲存格M2:{=SUM(IF(A2:K2={2;4;7;13;19},1,0))}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。
要注意 {2;4;7;13;19} 陣列中是以「;」分隔。