在 Excel 中大家都很熟悉如果要做到動態色彩效果可以使用「設定格式化條件」功能。如下圖,有一個含有日期的資料表,雖然月份都是由小到大,但是可能有些月份有,有些月份沒有,如何設定才能讓不同月份有所區隔。
(一) 月份必須連續,且每一月份皆有,利用儲存格底色分隔月份。(可以用在跨年份)
1. 選取儲存格A3:B27。設定儲存格底色:橙色。
2. 設定格式化條件為:使用公式來決定要格式化哪些儲存格。
在 Excel 中大家都很熟悉如果要做到動態色彩效果可以使用「設定格式化條件」功能。如下圖,有一個含有日期的資料表,雖然月份都是由小到大,但是可能有些月份有,有些月份沒有,如何設定才能讓不同月份有所區隔。
(一) 月份必須連續,且每一月份皆有,利用儲存格底色分隔月份。(可以用在跨年份)
1. 選取儲存格A3:B27。設定儲存格底色:橙色。
2. 設定格式化條件為:使用公式來決定要格式化哪些儲存格。
同仁問到一個有趣的問題:在 Excel 中有一個折線圖,如果想要改變其中的線條層次(上層/下層),該如何處理?
例如:在下列的統計圖中想要將最底層的紅色折線放到最上層。
參考以下的步驟:
1. 選取這個圖表。
2. 選取[圖表工具/設計]功能表中的「選取資料」。
同事問到:在 Excel 中以一個資料表製成一個統計圖,如果只想列印資料表,不想列印統計圖,該如何處理?(參考下圖)
提供二種方式來操作:
一、將統計圖隱藏
1. 選取統計圖後,選取 [常用/編輯]功能表中的「選取窗格」。
2. 在[選取窗格]中點選圖表,該圖表會被隱藏起來。
在 Excel 中常見到在一堆資料中要找出數個符合的項目,參考下圖。將檢核結果符合者,給予「V」記號,該如何處理?
為了讓學生多多練習,提供四種方式來操作,複製儲存格B2,往下各列貼上:
(1) 儲存格B2:=IF(ISNA(VLOOKUP(A2,$D$2:$D$9,1,FALSE)),"","V")
VLOOKUP(A2,$D$2:$D$9,1,FALSE):查詢儲存格A2的內容是否為符合的項目,若不是會得到一個錯誤訊息「#N/A」。
在 Excel 中輸入資料,預設值為按下 Enter 鍵後移至同欄的下一列儲存格。同事問到,如何能向下輸入、向上輸入、向右輸入、向左輸入呢?
有一些簡單的技巧,可能常被忽略了。你可以選取一段儲存格範圍,參考以下有四種做法:
(1) 輸入資料後按 Enter 鍵,作用儲存格的移動方向為:由上往下,由左至右。
(2) 輸入資料後按 Shift + Enter 鍵,作用儲存格的移動方向為:由下往上,由右至左。
在 Excel 中常會使用到「日期/時間」的表示,所以日期/時間的格式設定相當重要。在儲存格的格式設定之「數值/自訂」中,可以輸入自訂的格式:
整理了一些常用的格式如下:
【日期格式】
格式 | 功能 |
m | 將月份顯示為數字,前面不補零。 |
mm | 將月份顯示為數字,適當時前面補零。 |
mmm | 將月份顯示為縮寫 (Jan 至 Dec)。 |
mmmm | 顯示完整月份名稱 (January 至 December)。 |
mmmmm | 將月份顯示為單一字母 (J 至 D)。 |
d | 將日期顯示為數字,前面不補零。 |
dd | 將日期顯示為數字,適當時前面補零。 |
ddd | 將星期顯示為縮寫 (Sun 至 Sat)。 |
dddd | 顯示完整星期名稱 (Sunday 至 Saturday)。 |
yy | 將年份顯示為兩位數字。 |
yyyy | 將年份顯示為四位數字。 |
gg | 顯示民國 |
ggg | 顯示中華民國 |
aaa | 以週日~週六表示 |
aaaa | 以星期日~星期六表示 |
最近個人資料法的問題吵的沸沸揚揚,很多要公告的文件都必須要把名字做適當的隱藏。參考下圖,如何將第 2 字隱藏或是將倒數第 2 個字隱藏呢?
因為人名可能有 2, 3, 4, 5, …個字,所以可以隱藏第 2 個字或是倒數第 2 個字,或其他隱藏方式。
利用 REPLACE 函數,可以置換文字。
(1) 隱藏第 2 個字
儲存格C2:=REPLACE(B2,2,1,"O")
(2) 隱藏倒數第 2 個字
網友問到:如何計算某個日期為當年第幾週?如果不使用 WEEKNUM 函數,如何做到相同結果?(參考下圖)
(1) 使用 WEEKNUM 函數
在 Excel 2010 中,有一個 WEEKNUM 函數,可以根據某個日期傳回該日期為本年度的第幾週。
儲存格B2:=WEEKNUM(A2,1)
複製儲存格B2,往下各列貼上。
有同事問到:如果拿到一個 Word 檔,很想知道其中文字的格式設定為何,該如何操作呢?
以 Word 2010 為例:
開啟檔案後,按一下 Shift + F1 鍵,可以開啟/隱藏[顯示格式]窗格。
只要點選想要的文字,在[顯示格式]窗格中,會立即顯示該文字的格式。
點選設定的連結,例如:「字型」,會開啟[字型]對話框,你可以直接重新設定,並會套用在文字上。
在 Excel 中常見的資料表如下圖左,如果想要轉換為下圖右的呈現方式,你會如何下公式?
為了讓學生能靈活運用公式,所以用以下三種方式來練習:
(1) 儲存格F2:=IF($B2=F$1,$C2,)
(2) 儲存格F2:=SUMPRODUCT(($B2=F$1)*$C2)
(3)儲存格F2:=$C2*($B2=F$1)
複製儲存格F2,貼至儲存格F2:I22。