2016年1月26日 星期二

請由『學不完.教不停.用不盡』繼續閱讀文章



親愛的讀者
本部落格為備份之用,請由另一個部落格閱讀文章。


http://isvincent.pixnet.net/blog


2016年1月24日 星期日

Excel-間隔列套用不同色彩(MOD,ROW)

網友問到:如何在 Excel 的工作表中設計間隔列使用不同色彩?如下圖,分別呈現間隔1~8列的不同色彩。一般都是使用『設定格式化的條件』來設定即可完成。
Excel-間隔列套用不同色彩(MOD,ROW)
在以下的示意圖中,假設原始儲存格底色為『淺綠色』,我們只要讓顯示 TRUE 的儲存格顯示不同色彩(例如『淺藍色』)即可。
Excel-間隔列套用不同色彩(MOD,ROW)
如果要間隔1列顯示不同儲存格底色,則參考以下步驟:
1. 選取儲存格B2:B24。
2. 在設定格式化的條件中編輯格式規則:
規則類型:使用公式來決定要格式化哪些儲存格。
規則:=MOD(ROW()-2,2)<1 p="">
儲存格B2的ROW()為2,儲存格B3的ROW()為3,依此類推。
ROW()-2:因為資料由第 2 列開始。使用 MOD 函數來求取列數減 2 後再除以 2 的餘數,如果小於 1 則傳回 TRUE;如果等於 1 則傳回 FALSE
設定格式:淺藍色。(上式傳回 TRUE 者,回套用格式『淺藍色』)
Excel-間隔列套用不同色彩(MOD,ROW)
如果要修改為間隔 2~8 列,則參考:
間隔1列,規則:=MOD(ROW()-2,2)<1 p="">
修改為:
間隔2列,規則:=MOD(ROW()-2,4)<2 p="">
間隔3列,規則:=MOD(ROW()-2,6)<3 p="">
間隔4列,規則:=MOD(ROW()-2,8)<4 p="">
間隔5列,規則:=MOD(ROW()-2,10)<5 p="">
間隔6列,規則:=MOD(ROW()-2,12)<6 p="">
間隔7列,規則:=MOD(ROW()-2,14)<7 p="">
間隔8列,規則:=MOD(ROW()-2,16)<8 p="">

2016年1月23日 星期六

Excel-模擬Word文件將文字置入稿紙中(MID,ROW,COLUMN)

最近孩子問到:在 Word 文件中如何將一篇文章排版成放在稿紙中的樣子?這是非常容易的,因為 Word 已內建有稿紙功能。你只要點選[版面配置/稿紙]選單中的「稿紙設定」功能。
Excel-模擬Word文件將文字置入稿紙中(MID,ROW,COLUMN)
在[稿紙設定]對話框中挑選格線的格式、列數x欄數、格線色彩等,再選取頁面方向和設定頁首/頁尾等。
Excel-模擬Word文件將文字置入稿紙中(MID,ROW,COLUMN)
Word 會自動將文字置入你設定的稿紙格式中。(本例有勾選「允許標點符號益出邊界」)
Excel-模擬Word文件將文字置入稿紙中(MID,ROW,COLUMN)
如果你在 Excel 的活頁簿中,也想要達到這種效果呢?
在下圖中,一段文字已經置於儲存格A1中,假設每列要顯示 20 個字。
儲存格A3:=MID($A$1,(ROW(1:1)-1)*20+COLUMN(A:A),1)
複製儲存格A3,貼至儲存格A3:T14。
如果要調整每列顯示的字數,只要改變『20』的數值。
Excel-模擬Word文件將文字置入稿紙中(MID,ROW,COLUMN)
在下圖中,一段文字已經置於儲存格A1中,假設每欄要顯示 20 個字。
儲存格L3:=MID($A$1,ROW(1:1)+(12-COLUMN(L:L))*20,1)
複製儲存格L3,貼至儲存格A3:L22。
其中L欄即為第12欄,所以如果要改變欄數,只要改變『12』和『L:L』數值。
Excel-模擬Word文件將文字置入稿紙中(MID,ROW,COLUMN)

Excel-利用INT函數模擬整數和小數的四捨五入(ROUND)

在 Excel 中通常會使用 ROUND 函數來調整小數和整數的四捨五入動作,在此,要以 INT 函數來模擬 ROUND 函數的動作,並分小數和整數二個部分來處理。
1. 小數部分
在下圖中,分別呈現取小數 1 位至小數 6 位的結果。
Excel-利用INT函數模擬整數和小數的四捨五入(ROUND)
(1) 取小數 3 位四捨五入
儲存格B4:=ROUND(B$1,3)
觀察函數中的參數『3』。
儲存格B4:=INT(B$1*10^3+0.5)/10^3
觀察函數中的參數『3』和運算符號『*、/』。
(2) 取小數 6 位四捨五入
儲存格B7:=ROUND(B$1,6)
儲存格B7:=INT(B$1*10^6+0.5)/10^6
觀察函數中的參數『6』和運算符號『*、/』。

2. 整數部分
Excel-利用INT函數模擬整數和小數的四捨五入(ROUND)
(1) 取整數 3 位四捨五入
儲存格B4:=ROUND(B$1,-3)
觀察函數中的參數『-3』。
儲存格B4:=INT(B$1/10^3+0.5)*10^3
觀察函數中的參數『3』和運算符號『*、/』。
(2) 取整數 6 位四捨五入
儲存格B7:=ROUND(B$1,-6)
儲存格B7:=INT(B$1/10^6+0.5)*10^6
觀察函數中的參數『6』和運算符號『*、/』。

【綜合比較】
小數:儲存格B7:=ROUND(B$1,6)
整數:儲存格B7:=ROUND(B$1,-6)
要取小數位數 6 位,則參數為『6』;要取整數位數 6 位,則參數為『-6』。
小數:儲存格B7:=INT(B$1*10^6+0.5)/10^6
整數:儲存格B7:=INT(B$1/10^6+0.5)*10^6
要取小數和整數 6 位,則參數都是『10^6』;要取小數位數時『先乘再除』、要取整數位數時『先除再乘』。
依此類推...。

2016年1月21日 星期四

Word-在文件中將文字置換成圖片

學校同仁問到:在 Word 文件中,如果想要將文件中的某些文字置換成一個圖示,該如何處理較為快速且方便。例如:在下圖中的文件中有許多個 Google Chrome 字樣,想要快速置換成 Chrome 的圖示(在文件第一列),該如何處理?
Word-在文件中將文字置換成圖片
參考以下步驟:
1. 將複製這個圖示。(在剪貼簿中可以看到這個圖示)
Word-在文件中將文字置換成圖片
2. 在[常用/編輯]功能表中選取「取代」功能。
3. 在[尋找與取代]對話框的[取代]標籤下,按一下[更多]按鈕。
4. 在[尋找目標]文字方塊中輸入:Google Chrome;在[取代為]文字方塊中輸入『^c』。
(或是將插入點移至[取代為]文字方塊中,按一下[指定方式]按鈕,再選取「[剪貼簿]內容」,也會自動產生『^c』。)
Word-在文件中將文字置換成圖片
5. 按下[全部取代]按鈕。
結果即會將所有的 Google Chrome 置換成指定的圖示。
Word-在文件中將文字置換成圖片

2016年1月19日 星期二

使用PowerPoint做為照片轉影片工具

同仁問:有時候想要利用手邊的照片,做一個自動播放照片的1分鐘的影片,並且希望加上音樂,很簡單的一個小動作,不知取用何種軟體來製作,才能省時又省事。我的做法是利用手邊辦公室電腦裡就有的軟體『PowerPoint』即可,不需再另外找任何軟體。
本例以 PowerPoint 2013 來介紹。
1. 先將照片和標題放在 12 張投影片中(預定每張播放5秒)。
使用PowerPoint做為照片轉影片工具
2. 在第一張投影片中插入一個音訊(例如:MP3檔)。
3. 選取這個音訊圖示,在[播放]功能表中,設定播放的動作:
(1) 按一下「音訊模式/在背景播放」。
(2) 在「音訊選項」中勾選:『放映時隱藏』。
(3) 在「音訊選項」中設定開始:自動。
(4) 在「音訊選項」中勾選:『跨投影片播放』。
(5) 在「音訊選項」中勾選:『循環播放,直到停止』。
使用PowerPoint做為照片轉影片工具
4. 切換至「投影片瀏覽」模式,並選取所有投影片。
5. 在[切換至此投影片]區中設定一個切換效果。
6. 在[切換]功能表的「預存時間」中設定:
(1) 勾選:每隔,輸入秒數(本例為:4.0秒)
(2) 在「期間」輸入秒數(本例為:1.5秒)
使用PowerPoint做為照片轉影片工具
7. 選取[檔案/匯出]功能表,點選「建立視訊」。
8. 選取投影片要用在何種媒體(本例:電腦與HD顯示器)及是否使用錄製的時間和旁白。
9. 設定秒數(本例為5秒)。
10. 按一下[建立視訊]按鈕。
使用PowerPoint做為照片轉影片工具
最後產生一個 MP4 影片檔。

2016年1月18日 星期一

Excel-4個用公式模擬樞紐分析表應用練習(SUMPRODUCT)

根據前一篇文章:Excel-14個樞紐分析表應用練習,本篇文章要以公式模擬這些樞紐分析的動作。
資料來源是在一個 Excel 工作作的銷售記錄的資料清單中,含有欄位:日期、店名、業務員、產品代碼、機型、單價、數量、銷售額(如下圖)。現在,取用這個資料清單來練習樞分析表的操作,以下使用 Excel 2013 為例,資料來源有 700 筆以上。
【準備工作】
選取資料表中所有資料的儲存格,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期、店名、業務員、產品、代碼、機型、單價、數量、銷售額。
Excel-4個用公式模擬樞紐分析表應用練習(SUMPRODUCT)
在另一個工作表中含有產品及業務員的基本資料。也是先定義名稱:代碼清單、產品清單、機型清單、單價清單、店名清單。
Excel-4個用公式模擬樞紐分析表應用練習(SUMPRODUCT)

1. 計算各店的銷售總額
儲存格E4:=SUMPRODUCT((店名=D4)*銷售額)
複製儲存格E4,貼至儲存格E4:E10。
Excel-4個用公式模擬樞紐分析表應用練習(SUMPRODUCT)

2. 計算各分店每個業務員的銷售總額
儲存格G3:=SUMPRODUCT((店名=E4)*(業務員=F4)*銷售額)
複製儲存格G3,往下各列貼上。
Excel-4個用公式模擬樞紐分析表應用練習(SUMPRODUCT)

3. 可篩選各店中各種機型的銷售總額
Excel-4個用公式模擬樞紐分析表應用練習(SUMPRODUCT)
儲存格E4:=SUMPRODUCT((機型=D4)*(店名=$E$1)*銷售額)
複製儲存格E4,貼至儲存格E4:E18。
在儲存格E1設定資料驗證,其中的準則為:
儲存格內允許:清單
來源:=店名清單(相當於『仁愛店,民生店,民族店,民權店,和平店,忠孝店,信義店』)
Excel-4個用公式模擬樞紐分析表應用練習(SUMPRODUCT)

4. 依各產品篩選每個分店各種機型的銷售總額(以矩陣表格顯示)
Excel-4個用公式模擬樞紐分析表應用練習(SUMPRODUCT)
在儲存格B14中設計產品清單的下拉選單,在資料驗證的準則中設定:
儲存格內允許:清單
來源:=產品清單
Excel-4個用公式模擬樞紐分析表應用練習(SUMPRODUCT)
儲存格B18:=IF(COLUMN(A:A)<=COUNTIF(基本資料!$B2:$B16,$B$14),OFFSET
(基本資料!$C$1,MATCH($B$14,基本資料!$B2:$B16,0)+COLUMN(A:A)-1,),"")
產品資料置於「基本資料!$B2:$B16」。當選取儲存格B14,則儲存格B17:E17的內容隨之變動。
Excel-4個用公式模擬樞紐分析表應用練習(SUMPRODUCT)
儲存格18:=SUMPRODUCT((店名=$A18)*(機型=B$17)*銷售額)
複製儲存格B18,貼至儲存格B18:E24。
Excel-4個用公式模擬樞紐分析表應用練習(SUMPRODUCT)

2016年1月17日 星期日

Excel-14個樞紐分析表應用練習

在一個 Excel 工作作的銷售記錄的資料清單中,含有欄位:日期、店名、業務員、產品代碼、機型、單價、數量、銷售額。取用這個資料清單來練習樞分析表的操作。
以下使用 Excel 2013 為例,資料來源有 700 筆以上。
Excel-14個樞紐分析表應用練習

1. 計算各店的銷售總額
在樞紐分析表欄位中,設定:
列:店名;值:銷售總額。並且更改儲存格A3和儲存格B3的標籤名稱。
Excel-14個樞紐分析表應用練習

2. 計算各分店每個業務員的銷售總額(大綱模式顯示)
列:店名(1)、業務員(2);值:銷售總額。
Excel-14個樞紐分析表應用練習

3. 計算各分店每個業務員的銷售總額(表格模式顯示)
列:店名(1)、業務員(2);值:銷售總額。
Excel-14個樞紐分析表應用練習
選取一個店名的儲存格,在『店名』欄位中設定。
Excel-14個樞紐分析表應用練習
在[小計與篩選]標籤下,『小計』設定:無。
Excel-14個樞紐分析表應用練習
在[版面配置與列印]標籤下,設定:
選取『以列表方式顯示項目標籤』;勾選『重覆項目標籤』。
Excel-14個樞紐分析表應用練習

4. 篩選銷售總額『前三名』店家
Excel-14個樞紐分析表應用練習
選取『店名』欄位,再選取[值篩選/前10項]選項:
Excel-14個樞紐分析表應用練習
設定:『最前』+『3』+『項』。
Excel-14個樞紐分析表應用練習

5. 依各店篩選業務員銷售前三名,並依銷售總額遞減排序(表格模式顯示)
Excel-14個樞紐分析表應用練習
選取『業務員』欄位,再選取[值篩選/前10項]選項,設定:『最前』+『3』+『項』。
然後,選取『業務員』欄位,選取「更多排序選項」選項。
Excel-14個樞紐分析表應用練習
選取:遞減,再選取「銷售總額」欄位。
Excel-14個樞紐分析表應用練習

6. 依各產品篩選業務員銷售前三名,並依銷售總額遞減排序(表格模式顯示)
選取『業務員』欄位,再選取[值篩選/前10項]選項,設定:『最前』+『3』+『項』。
選取『業務員』欄位,選取「更多排序選項」選項。
在[排序]對話框中,選取:遞減,再選取「銷售總額」欄位。
Excel-14個樞紐分析表應用練習

7. 依各產品的各種機型計算銷售總額,並依銷售總額遞增排序(表格模式顯示)
選取『產品』欄位,選取「更多排序選項」選項。
在[排序]對話框中,選取:遞增,再選取「銷售總額」欄位。
Excel-14個樞紐分析表應用練習

8. 可篩選各店中各種機型的銷售總額
在樞紐分析表欄位中,設定:
篩選:店名;列:機型;值:銷售總額。
Excel-14個樞紐分析表應用練習
只要選取某一家店,即可篩選該店的資料。(可單選/複選)
Excel-14個樞紐分析表應用練習

9. 依各產品篩選每個分店各種機型的銷售總額(表格模式顯示)
在樞紐分析表欄位中,設定:
篩選:產品;列:店名(1)、機型(2);值:銷售總額。
Excel-14個樞紐分析表應用練習
只要選取某一產品,即可篩選該產品的資料。(可單選/複選)
Excel-14個樞紐分析表應用練習

10. 依各產品篩選每個分店各種機型的銷售總額(以矩陣表格顯示)
在樞紐分析表欄位中,設定:
篩選:產品;列:店名;欄:機型;值:銷售總額。
Excel-14個樞紐分析表應用練習
Excel-14個樞紐分析表應用練習

11. 依各業務員列出每個產品的各種機型的銷售總額(以矩陣表格顯示)
(自行練習)
Excel-14個樞紐分析表應用練習

12. 取得一家店中的一種產品銷售清單
如果產生了一個商店和產品的銷售總額表(如下圖),如果想要知道一家店中的一種產品銷售清單,該如何處理?其實很簡單,例如,要知仁愛店的冰箱的銷售清單,則只要在儲存格B5上按二下即可。
Excel-14個樞紐分析表應用練習
Excel 會在一個新的工作表列出該銷售清單:
Excel-14個樞紐分析表應用練習

13. 使用交叉分析篩選器來輔助篩選
當使用樞紐分析表工具建立了店名、代碼和產品計算銷售總額的分析表,如下圖:
Excel-14個樞紐分析表應用練習
在[樞紐分析工具]功能表中選取「插入交叉分析篩選單」,選取「店名」。
Excel-14個樞紐分析表應用練習
使用這個篩選器也可以快速篩選想要的內容:
Excel-14個樞紐分析表應用練習
自行練習以下的樞紐分析結果:
Excel-14個樞紐分析表應用練習

14. 使用時間表來篩選日期
建立一個含有日期的樞紐分析表(如下圖),你可以在[樞紐分析工具]功能表中選取「插入時間表」,選取「日期」。
你可以在日期區間(年、季、月、天)中使用捲軸來指定要篩選的日期範圍。
Excel-14個樞紐分析表應用練習

好康東東