2013年9月30日 星期一

Dropbox雲端硬碟新增螢幕截圖功能

Dropbox 雲端硬碟工具在其最新版(2.4)中,新增了螢幕截圖功能,可以藉由按下 PrintScreen 或是 Alt+PrintScreen 來抓取整面整面或是作用中視窗的截圖,直接同步至雲端。

Dropbox 版本說明網頁:https://www.dropbox.com/release_notes

點選 2.40 版,下載各種版本:

安裝好 2.4 版後,可以勾選「使用 Dropbox 分享螢幕截圖」,當抓下螢幕截圖時,可以立即上傳至 Dropbox 雲端硬碟。

截圖會被放進 Dropbox 資料夾的「螢幕截圖」子資料夾中:

並且會同步上傳至 Dropbox 的「螢幕截圖」子資料夾中:

隨著 Office 2010 以上版本可以抓取螢幕截圖,Windows 8 也內建可以將螢幕截圖自動存檔,現在雲端硬碟工具也可以提供管理螢幕截圖的工具,螢幕截圖的使用越來越方便了。其實如果你在平版或是手機上抓取螢幕截圖,也可以設定 Dropbox 立即上傳(同步)螢幕截圖至雲端。

2013年9月27日 星期五

Evernote-寄一封電子郵件做為提醒的記事

先前 Evernote 新增了提醒功能,讓這個記事本更像私人秘書了。而你除了在 Evernote 上新增一個含有提醒功能的記事之外,你也可以很輕鬆的寄一封電子郵件就能新增一個含有提醒功能的記事。

做法很簡單:只要在 Email 的標題上加上「!日期」

例如:繳交信用卡費用 !2013/9/28,「!」之前要記得插入一個空格。你的 Evernote 會新增一個日期為 2013/9/28 的提醒記事。如下圖,提醒事項自動新增了這個記事,記得在電子郵件提醒中按一下「Yes」進行確認。

在你自己的信箱中也會收到一封已寄出的通知信:

所以你可以用 Email 來提醒自己繳交費用、提醒老公出差時順便要買的美食、提醒同仁處理公務、提醒學生交作業、…,一封電子郵件就可以輕鬆解決!

你還可以在 Email 中將主旨設定為:Topic @notebook #tag,自動將記事歸類到 notebook 記事本,並且加上 tag 標籤。參考另一篇文章:Evernote-將郵件變為記事有詳細說明。

Google的Android裝置管理員新增手機鎖定功能

之前介紹過:啟用Android Device Manager來定位追蹤手機,讓你在遺失手機或是一時找不到手機時,讓使用者可以利用網路,在遠端定位追蹤手機位置,並且可以在不得已時清除手機的內容。當你遺失手機時務必第一時間要採取行動,並且手機也要在能上網的環境中相關設定才會生效。

現在 Google 在 Android 裝置管理員介面中又新增了「鎖定」功能。

https://www.google.com/android/devicemanager

當你按下「鎖定」,你可以輸入一個新的密碼來鎖定裝置(螢幕),Google 也建議你,如果找到了裝置,務必要重設 Google 帳戶的密碼,以確保安全。設定密碼後按下「鎖定」,手機就會以這個密碼上鎖,任何人無法進一步看到手機內容。當然只有你能解開來囉!

2013年9月26日 星期四

YouTube開放免費下載背景音樂(mp3)

在做網頁時常會缺乏合法使用的音樂來做為背景音樂,現在YouTube 在網站上新增了「創作工作」,其中的「音軌庫」開放可以免費下載其精心挑選的音樂,集合在音軌庫中,你可以下載這些音樂來做為背景音樂,可以儲存成 mp3 檔,最重要的是完全免費,這是可以合法使用的音樂素材。

網址:https://www.youtube.com/audiolibrary

你可以在類型、氛圍、樂器、播放時間等類別中挑選各類的曲目,你可以直接線上播放(按一下播放符號),其中的藍色橫條表示其熱門程度,你可以按一下「下載」圖示,即可以下載這首樂曲的 mp3 檔,按一下「★」符號,即可加入收藏。

Excel-清單資料和矩陣表格資料互換

有網友問到:在 Excel 中如果有資料是以「清單」形式列表(如下圖左),想要改以「矩陣」表格列表,該如何轉換?反之,又該如何轉換?

(一) 清單資料轉換為矩陣表格資料(下圖左→下圖右)

儲存格E2:{=IFERROR(INDIRECT(ADDRESS(SMALL(IF(組別=$D2,ROW(組別),FALSE),COLUMN(A:A)),2)),"")}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。

SMALL(IF(組別=$D2,ROW(組別),FALSE),COLUMN(A:A)):在陣列公式中找出和儲存格D2相同組別的第 1 個「列號」,例如:第 1 個「A」在第 5 列。

INDIRECT(ADDRESS(SMALL(IF(組別=$D2,ROW(組別),FALSE),COLUMN(A:A)),2)):透過 INDIRECT 和 ADDRESS 函數查表得到姓名。

因為公式查表可能會產生錯誤訊息,以 IFERROR 函數將錯誤的結果改以空白顯示。

複製儲存格E2,貼至儲存格E2:J7。

 

(二) 矩陣表格資料轉換為清單資料(下圖右→下圖左)-1

儲存格A2:=OFFSET($D$1,MOD(ROW(A1)-1,6)+1,0,,)

MOD(ROW(A1)-1,6)+1:往下複製公式時,可以產生 1, 2, 3, 4, 5, 6 (列)。

透過 OFFSET 函數,將上式之1, 2, 3, 4, 5, 6 代入得到 A, B, C, D, E, F。

儲存格B2:=OFFSET($D$1,MOD(ROW(A1)-1,6)+1,INT((ROW(A1)-1)/6)+1,,)

INT((ROW(A1)-1)/6)+1:往下複製公式時,可以產生 1, 2, 3, 4, 5 (欄)。

透過 OFFSET 函數,,將上式之1, 2, 3, 4, 5 代入得到每一列之各欄對應的姓名。

複製儲存格A2:B2,貼至儲存格A2:B30。

 

(三) 矩陣表格資料轉換為清單資料(下圖右→下圖左)-2

儲存格A2:=OFFSET($D$1,INT((ROW(A1)-1)/5)+1,0,,)

INT((ROW(A1)-1)/5)+1:當向下複製公式時,產生 1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 3, ….。

將上式代入 OFFSET 函數得到 A, A, A, A, A, B, B, B, B, B, C, …。

儲存格B2:=OFFSET($D$1,INT((ROW(A1)-1)/5)+1,MOD((ROW(A1)-1),5)+1,,)

MOD((ROW(A1)-1),5)+1:向下複製公式時產生 1, 2, 3, 4, 5 (欄)。

透過 OFFSET 函數,將上式之1, 2, 3, 4, 5 代入得到每一列之各欄對應的姓名。

複製儲存格A2:B2,貼至儲存格A2:B30。

2013年9月25日 星期三

將Word文件的每一頁轉換為PowrePoint的一張投影片

有人有這樣的需求:想要將 Word 文件檔中的每一頁轉換為 PowrePoint 的一張張投影片。以下圖的 Word 檔中有三頁內容為例,想要將其所有內容轉換至 PowerPoint 中成為三張投影片。

這有別於利用 Word 中的大網文字來轉換為 PowerPoint 投影片大綱,也有人會將 Word 的每一頁轉成圖片檔,再放入 PowerPoint 中。有沒有其他更適合或更好的方法呢?

我其實也沒有特別的方法,不過,曾經藉助 Wondershare PDF Converter Pro 這套軟體,倒是可以輕鬆做到。這是一套可以將 PDF 檔轉換為 Word、Excel、PowerPoint、EPUB、圖像、HTML等格式的軟體。

Wondershare PDF Converter Pro:http://www.wondershare.com.tw/

下載中文版:http://www.wondershare.com.tw/pdf-converter/

1. 先將 Word 文件另存新檔為 PDF 格式檔案。(以 Word 2010 為例)

2. 將這個 PDF 檔拖曳到 Wondershare PDF Converter Pro 中。

3. 按下 PowerPoint 圖示。(表示要轉換成 PowerPoint 格式)

4. 按一下[轉換]按鈕。

5. 開啟這個 已轉換好的 PowerPoint 檔案(以 PowerPoint 2010 為例),整份 Word 內容都放在投影片中了。

你還可以進一步進行編輯:

不知你是否也有可行好用的方法?其實,能解決問題的方法都是好方法!善用手邊的工具來解決各式各樣的問題,讓事情能順利的進行下去,什麼方法都值得一試。

2013年9月24日 星期二

Excel-依日期統計數量(SUMPRODUCT,LARGE)

在使用的資料中,有一個常見的例子(參考下圖),其中有二個欄位,一是日期,一是內容。其中日期是由最新到最舊排列,有些日期有重覆,有些日期不在清單上。

根據這個資料表,要來求下圖中的四種統計數量:

(一) 根據距今天的日數來統計累計的筆數

儲存格E2:=SUMPRODUCT((日期>TODAY()-100*ROW(A1))*1)

ROW(A1):向下複製後,可以產生ROW(A1)=1、ROW(A2)=2、…、ROW(A10)=10。

TODAY()-100*ROW(A1):距離今天的指定日數(100、200、300、…)。

日期>TODAY()-100*ROW(A1):產生日期大於距離今天的指定日數的日期陣列。

透過 SUMPRODUCT 函數統計上式陣列的日期個數,透過「*1」,將其轉換為 1/0 陣列。

複製儲存格E2,貼至儲存格E2:E12。

將兩個相鄰日期的累積筆數相減,即是兩個日期區間的筆數。

 

(二) 根據最近的筆數來找出對應的日期

儲存格E15=LARGE(日期,D15)

因為日期已經由大到小排序,所以可以運用 LARGE 函數即可找出指定日數(100、200、300、…)的日期。

複製儲存格E15,貼至儲存格E15:E26。

將兩個相鄰日期相減,即是兩個日期區間的筆數。

 

(三) 依年度統計筆數

儲存格I2:=SUMPRODUCT((YEAR(日期)=H2)*1)

透過 YEAR 函數,找出日期中合於指定年度的日期的條件陣列(TRUE/FALSE),透過「*1」,將其轉換為 1/0 陣列。

透過 SUMPRODUCT 函數合計上式之 1/0 陣列。

複製儲存格I2,貼至儲存格I2:I7。

 

(四) 依星期統計筆數

儲存格I10:=SUMPRODUCT((WEEKDAY(日期,1)=ROW(A1))*1)

透過 WEEKDAY 函數,找出日期中合於指定星期的條件陣列(TRUE/FALSE),透過「*1」,將其轉換為 1/0 陣列。

其中 WEEKDAY 函數中參數的意義如下:

透過 SUMPRODUCT 函數合計上式之 1/0 陣列。

複製儲存格I10,貼至儲存格I10:I6。

Word-合併多個Word文件

有網友提到要將多個 Word 文件檔整併成一個,不知道要如何操作?

參考以下的做法(以 Word 2010 版本為例):

1. 先開啟一個全新的 Word 文件檔。

2. 在檔案總中選取想要合併的 Word 檔。

3. 將所選取的 Word 檔以拖曳的方式拉進已開啟的 Word 文件中。(參考下圖)

當你放開滑鼠左鍵時,這些 Word 檔就會被開啟,並依序插入這個新開啟的 Word 文件中。

如此就完成了 Word 文件的合併工作了。

2013年9月23日 星期一

Excel-取出數值中的每一位數

有人有這樣的需求,想要取出 Excel 工作表儲存格中數字的每一個位數,該如何處理呢?

(一) 整數數值

參考下圖的數字為 9 位數的數值,要取出其中的每個位數。

儲存格B2:=MID($A2,COLUMN(A2),1)

將數值視為文字來處理,以 MID 函數來取出文字。

複製儲存格B2,貼至儲存格B2:J2。再往下複製到各列中。

如果數值被設定了其他格式(千分位符號、百分比符號、增加小數點等),都不會影響其取得數值的各個位數。

(二) 含小數數值

也有人以運算方式來取得每個位數,下圖中的數字是整數為 5 位數,小數部分也是 5 位數。

其中整數部分:(假設數值置於儲存格A2)

  •  個位數 =MOD(INT(A2/1),10)
  •  十位數 =MOD(INT(A2/10),10)
  •  百位數 =MOD(INT(A2/100),10)
  •  千位數 =MOD(INT(A2/1000),10)
  •  萬位數 =MOD(INT(A2/10000),10)
  • 十萬位數 =MOD(INT(A2/100000),10)

儲存格B2:=MOD(INT($A2/10^(5-COLUMN(A1))),10)

複製儲存格B2,貼至儲存格B2:F2。再往下複製到各列中。

小數位數部分:(假設數值置於儲存格A2)

  •  十分位數 =MOD(INT(A2/.1),10)
  •  百分位數 =MOD(INT(A2/.01),10)
  •  千分位數 =MOD(INT(A2/.001),10)
  •  萬分位數 =MOD(INT(A2/.0001),10)
  • 十萬分位數 =MOD(INT(A2/.00001),10)
  • 百萬分位數 =MOD(INT(A2/.000001),10)

儲存格H2:=MOD(INT($A2/10^(-COLUMN(A1))),10)

複製儲存格H2,貼至儲存格H2:L2。再往下複製到各列中。

2013年9月22日 星期日

Google-在網頁,部落格中嵌入雲端硬碟中的各種檔案

Google Drvie 已經是很多人經常使用的雲端硬碟之一了,有老師問到如果要將雲端硬碟中的檔案放在自己的網站或是部落格中使用,該如何處理呢?

Google Drvie 已經很貼心的提供了檔案的「嵌入」功能,讓你可以將某些類型的檔案以嵌入的方式放在網頁或是部落格中。

例如:以下的 Google Drive 中已經放入了數個各種類型的檔案:

因為你要在網頁上使用,所以必須將這些檔案的存取權,設定為:公開在網路上。



然後,選取一個檔案按一下右鍵,選取「選擇開啟工具」,再選取「Google 雲端硬碟檢視器」。(先以一個 PDF 檔為例)

在 Google 雲端檢視器中已呈現了這個 PDF 的內容,選取「檔案」功能表中的「嵌入這個 PDF 檔案」:

複製這個要嵌入網站中的 HTML 程式碼:



貼至你的網頁或部落格中,以下即為嵌入的 PDF 檔:



各種檔案的嵌入方式類似,你還可以嵌入其他類型的檔案:

例如:影片檔(影片取自 Windows 7 系統中的視訊檔案)



例如:Excel 檔



如果你要使用 Google Drive 上的圖片,則無法使用嵌入的方式,要來試試別的方式。

你先在 Google 雲端檢視器中開啟要使用的圖片,觀察其網址,例如:

https://docs.google.com/file/d/0ByGVVaMLQtOMRUtkcGRXckxUa3c/edit

複製其中的一段代碼:0ByGVVaMLQtOMRUtkcGRXckxUa3c

組合新的網址:https://googledrive.com/host/ + 0ByGVVaMLQtOMRUtkcGRXckxUa3c

這就是以下圖片的網址(https://googledrive.com/host/0ByGVVaMLQtOMRUtkcGRXckxUa3c)

Excel-偶數,奇數之運用

在 Excel 中要判斷一個數是偶數或是奇數,常用的方式不外以下數種:(以判斷儲存格A1為例)

(1) 公式 =IF(ISEVEN(A1),"偶數","奇數")

ISEVEN 函數判斷是否為偶數(傳回 True/False),ISODD 函數判斷是否為奇數(傳回 True/False)。

(2) 公式 =IF(MOD(A1,2)=0,"偶數","奇數")

使用 MOD 函數來求儲存格A1除以 2 的餘數,結果為 0,傳回「偶數」,否則,傳回「奇數」。

(3) 公式 =IF(INT(A1/2)=A1/2,"偶數","奇數")

使用 IND 函數來求儲存格A1除以 2 的商取不大於的最大整數,是否和儲存格A1除以 2 的結果相等,結果為 0,傳回「偶數」,否則,傳回「奇數」。

(4) 公式 =IF(QUOTIENT(A1,2)=A1/2,"偶數","奇數")

使用 QUOTIENT 函數來求儲存格A1除以 2 的整數商,是否和儲存格A1除以 2 的結果相等,結果為 0,傳回「偶數」,否則,傳回「奇數」。

利用偶數/奇數的判斷,可以用在那些地方呢?

參考下圖來做些練習:

選取儲存格A1:B21,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:序號、數值。

藉由 SUMPRODUCT 函數來求以下四項:

(1) 偶數列數值和

儲存格E2:=SUMPRODUCT((MOD(序號,2)=0)*數值)

(2) 奇數列數值和

儲存格E3:=SUMPRODUCT((MOD(序號,2)=1)*數值)

(3) 偶數數值之和

儲存格E4:=SUMPRODUCT((MOD(數值,2)=0)*數值)

(4) 奇數數值之和

儲存格E5:=SUMPRODUCT((MOD(數值,2)=1)*數值)

接著來設定序號欄位和數值欄位的格式:

(1) 數值欄位偶數者,字型顯示藍色粗體

選取儲存格B2:B21,設定格化式的條件,使用公式來決定要格式化哪些儲存格。

規則:=MOD(B2,2)=0,格式:字型為藍色粗體。

(2) 序號為奇數者,整列改為粉紅色底色

選取儲存格A2:B21,設定格化式的條件,使用公式來決定要格式化哪些儲存格。

規則:=MOD($A2,2)=1,格式:填滿粉紅色底色。

 

接著來練習進一步的應用。

原始格式:

設定格式:

選取儲存格A1:I10,設定格式條件,使用公式來決定要格式化哪些儲存格。

(1) 公式:=(MOD(ROW(A1),2)=0)*(MOD(COLUMN(A1),2)=1)

公式中的「*」乃將二個條件作「AND」運算。找出偶數列和奇數欄的交集。

(2) 公式:=(MOD(ROW(A1),2)=1)*(MOD(COLUMN(A1),2)=0)

找出奇數列和偶數欄的交集。

格式:較淺粉紅色底色。

選取儲存格A1:I10,設定格式條件,使用公式來決定要格式化哪些儲存格。

(3) 公式:=(MOD(ROW(A1),2)=1)*(MOD(COLUMN(A1),2)=0)

找出奇數列和偶數欄的交集。

(4) 公式:=(MOD(ROW(A1),2)=0)*(MOD(COLUMN(A1),2)=1)

找出偶數列和奇數欄的交集。

格式:紅色粗體字型。

Excel-資料驗證中使用跨工作表的來源資料

有讀者問到:在 Excel 中是否可以在資料驗證中的來源資料,使用跨工作表的資料?答案是可以的。

例如:在下圖中的BBB工作表建立一些資料清單(儲存格A1:B31)。

選取儲存格B1:B31,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」,建立名稱:姓名。

當你為一個儲存格建立資料驗證時,在資料驗證準中的儲存格內允許選取:清單,在來源中選取儲存格B2:B31,Excel 會自動更換為「=姓名」,它已認得資料範圍被定義為:姓名。

在AAA工作表的儲存格A2中已可使用清單:

如果你沒有先定義名稱,也是可以使用「工作表!儲存格範圍」來作為清單來源。

本例:=BBB!$B$2:$B$31

 

【延伸學習】

參考其他關於資料驗證使用的學習文章…

Excel-在公式中善用名稱和資料驗證清單(VLOOKUP,INDIRECT)
Excel-使用表單下拉式方塊篩選資料
Excel-固定資料驗證提示訊息的位置
Excel-保護下拉式清單中的內容不變動
Excel-選取儲存格顯示公式說明
Excel-不允許輸入某些字串(資料驗證)
Excel-限定儲存格中只能輸入大寫字母(資料驗證)
Excel-限定儲存格輸入內容(設定格式化的條件)
Excel-讓圖案中的文字可變
Excel-分組報告成績輸入統計表
Excel-設計二層的下拉式選單
Excel-資料驗證使用多個條件
Excel-加總下拉式清單的內容
Excel-使用高速公路里程表來計費
Excel-四則運算練習
Excel-各種資料驗證的應用
Excel-在一個儲存格中參照不同工作表的內容(INDIRECT)
Excel-以資料驗證來輸入資料
Excel-限制儲存格中輸入的字數和首字為英文
Excel-輸入資料時提示輸入重覆
Excel-使用下拉式清單設定格式
Excel-為不同組別計算平均分數(OFFSET)
Excel-使用多層下拉式清單結構輸入資料(基礎)
Excel-限制同一欄中輸入唯一值
Excel-設定格式化條件區隔男女生資料
Excel-限制不可輸入未來日期
Excel-限制輸入的小數點位數
Excel-查表練習(INDEX,MATCH,OFFSET)
Excel-在下拉式選單中選取日期
Excel-顯示四則運算的計算過程
Excel-依選單內容顯示圖表
Excel-用公式篩選資料(陣列公式)
Excel-查表練習(INDEX,陣列)
Excel-製作單位轉換工具(多層選單)
Excel-Index+Match練習
Excel-研習人員統計報表(清單、INDIRECT)
Excel-避免同一欄中輸入重覆值
Excel-使用多層下拉式清單結構輸入資料(進階)
Excel-多條件的查詢(INDEX+MATCH+陣列)
Excel-用公式取代樞紐分析(二)
Excel-計算每月各日及星期各天的平均
Excel-顯示動態圖片(OFFSET+資料驗證+定義名稱)
Excel-COUNTIF+SUMIF練習
Excel-研習人員統計報表(SUMPRODUCT,INDEX,MATCH,VLOOKUP)
Excel-列出週六日的日期

2013年9月21日 星期六

Windows-為ZIP壓縮檔加密

同事問到:因為所有的 Windows 系統都認得 ZIP 壓縮檔,為了方便移轉及使用(使用者不需安裝其他壓縮/解壓縮軟體),想要將資料檔製成 ZIP 壓縮檔並且加密,該如何處理呢?

在 Windows 系統要將資料檔案壓縮成 ZIP 檔,是一件很容易的事。只要在選取的資料夾和檔案上按一下右鍵,並選取「傳送到/壓縮的 (zipped) 資料夾」,即會產生一個 ZIP 壓縮檔。

但是,這個 ZIP 壓縮檔無法加密,所以,要藉助其他的壓縮軟體才能達到加密的效果。而 WinRAR 是個不錯的選擇!

官網:http://www.rar.com.tw/

下載安裝試用版,雖然宣稱只能完整使用 30 天,但是到期後仍可繼續使用,只是會常跳出要求註冊的畫面,不過,仍可持續使用沒問題。

1. 在選取的資料夾和檔案上按一下右鍵,並選取「加到壓縮檔」。

2. 在[壓縮檔名稱及參數]對話框中的壓縮格式點選「ZIP」。

3. 按一下[進階設定]標籤,再按一下「設定密碼」按鈕。

4. 輸入二次相同密碼,按一下[確定]按鈕,即完成加密壓縮檔設定。

如果你在檔案總管的資料夾中按一下右鍵,即會出現「移除密碼」,使用者可以輸入密碼來移除密碼。

讓YoutTube影片可以隨著視窗縮放大小

繼上篇文章:讓YoutTube影片可以隨著視窗縮放大小,稍加修改網址即可將 YouTube 的影片跟著視窗調整而調整,符合一些老師上課或是做簡報時使用。可以兩種視窗並例,並且沒有其他YouTube 影片的干擾。做法很簡單,例如:

http://www.youtube.com/watch?v=ImlTUol7-vU

在網址中的「watch」之後加入「_popup」:

http://www.youtube.com/watch_popup?v=ImlTUol7-vU

現在你還可以利用第二種方法做到相同效果:

將原來網址:

http://www.youtube.com/watch?v=ImlTUol7-vU

修改「watch?v=」為「embed/」

http://www.youtube.com/embed/ImlTUol7-vU

影片就可以跟著視窗調整而調整:

當你把改變後的網址郵寄給他人或是掛在網路上時,使用者就可以享受這清爽且可縮放的功能了。使用者只要按下影片右下角的YouTube字樣,即可切換回 YouTube 網站上觀賞了!

2013年9月20日 星期五

Excel-計算成績的五標(底標,後標,均標,前標,頂標)

學校取得某次考試的成績,如果想要自行計算各科成績的五標(底標, 後標, 均標, 前標, 頂標),並標示學生的程度,該如何處理?以下以 320 個學生,五個科目的成績為例。

首先,選取儲存格C1:H321,按一下 Ctrl+Shift+F3 鍵,定義名稱:國文、英文、數學、社會、自然、總分。參考下圖,來計算五標及標示學生成績的程度。

(一) 若考試成績的五標如下定義:

  • 底標:該科成績位於第12百分位數之考生分數
  • 後標:該科成績位於第25百分位數之考生分數
  • 均標:該科成績位於第50百分位數之考生分數
  • 前標:該科成績位於第75百分位數之考生分數
  • 頂標:該科成績位於第88百分位數之考生分數

【輸入公式】

(1) 底標(第12百分位) 

儲存格C323:=SMALL(INDIRECT(C1),320*12%)

儲存格C323:=LARGE(INDIRECT(C1),320*(1-12%))

INDIRECT(C1):將儲存格C1的內容「國文」轉成已定義的名稱(位址)。

320*12%:320 個學生的12%人數。

(2) 後標(第25百分位) 

儲存格C324:=SMALL(INDIRECT(C1),320*25%)

儲存格C324:=LARGE(INDIRECT(C1),320*(1-25%))

(3) 均標(第50百分位) 

儲存格C325:=SMALL(INDIRECT(C1),320*50%)

儲存格C325:=LARGE(INDIRECT(C1),320*(1-50%))

(4) 前標(第75百分位) 

儲存格C326:=SMALL(INDIRECT(C1),320*75%)

儲存格C326:=LARGE(INDIRECT(C1),320*(1-75%))

(5) 頂標(第88百分位) 

儲存格C327:=SMALL(INDIRECT(C1),320*88%)

儲存格C327:=LARGE(INDIRECT(C1),320*(1-88%))

複製儲存格C323:C327,貼至儲存格C323:H727。

接著,在列323建立一個輔助列資料。

儲存格I2:=VLOOKUP(H2,CHOOSE({1,2},$H$322:$H$327,$A$322:$A$327),2,TRUE)

使用查表方式找出學生總分對照的五標程度。(請參閱:利用CHOOSE函數來輔助查表)

 

(二) 若考試成績的五標如下定義:

  • 底標:該學科後25%考生成績的平均分數
  • 低標:該學科後50%考生成績的平均分數
  • 均標:該學科全體考生成績的平均分數
  • 高標:該學科前50%考生成績的平均分數
  • 頂標:該學科前25%考生成績的平均分數

【輸入公式】

(1) 底標(後25%平均) 

儲存格C323:{=AVERAGE(LARGE(INDIRECT(C1),ROW(241:320)))}

儲存格C323:{=AVERAGE(SMALL(INDIRECT(C1),ROW(1:80)))}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。

ROW(241:320):產生 241~320 的數字陣列。

(2) 低標(後50%平均) 

儲存格C324:{=AVERAGE(LARGE(INDIRECT(C1),ROW(161:320)))}

儲存格C324:{=AVERAGE(SMALL(INDIRECT(C1),ROW(1:160)))}

(3) 均標(全體平均) 

儲存格C325:{=AVERAGE(LARGE(INDIRECT(C1),ROW(1:320)))}

儲存格C325:{=AVERAGE(SMALL(INDIRECT(C1),ROW(1:320)))}

(4) 高標(前50%平均) 

儲存格C326:{=AVERAGE(LARGE(INDIRECT(C1),ROW(1:160)))}

儲存格C326:{=AVERAGE(SMALL(INDIRECT(C1),ROW(161:320)))}

(5) 頂標(前25%平均) 

儲存格C327:{=AVERAGE(LARGE(INDIRECT(C1),ROW(1:80)))}

儲存格C327:{=AVERAGE(SMALL(INDIRECT(C1),ROW(241:320)))}

複製儲存格C323:C327,貼至儲存格C323:H727。

 

【延伸學習】

你也可以使用 PERCENTILE 函數來找出第幾百分位上的分數:

(1) 底標(第12百分位) 

儲存格C323:=PERCENTILE(INDIRECT(C1),12%)

(2) 後標(第25百分位) 

儲存格C324:=PERCENTILE(INDIRECT(C1),25%)

(3) 均標(第50百分位) 

儲存格C325:=PERCENTILE(INDIRECT(C1),50%)

(4) 前標(第75百分位) 

儲存格C326:=PERCENTILE(INDIRECT(C1),75%)

(5) 頂標(第88百分位) 

儲存格C327:=PERCENTILE(INDIRECT(C1),88%)

2013年9月19日 星期四

Excel-找出一年中指定週別的起迄日期

有人想要使用 Excel 來找出一年中指定週別的起始日期(星期日)和結束日期(星期六),該如何處理?

以下圖為例,今年(2013)第一週的起始日期是 2012/12/30,結束日期是 2013/1/5。

現在,首要任務是要先找出每一年的第一週的第一個日期。再設計微調按鈕來控制週別,即可查出所有週別的起迄日期。

第一週起始日期:=DATE(C1,1,1)-WEEKDAY(DATE(C1,1,1),1)+1

DATE(C1,1,1):該年度的第一天。

WEEKDAY(DATE(C1,1,1),1):求該年度的第一天是星期幾,星期日為 1,…,星期六為 7。

第 n 週起始日期:=(DATE(C1,1,1)-WEEKDAY(DATE(C1,1,1),1)+1)+7*(C2-1)

第 n 週結束日期:=C3+6

如果想要以微調按鈕控制項來改變週別,則新增一個微調按鈕後,設定控制項格式:

最小值:1,最大值:53,儲存格連結:$C$2。

好康東東