贊助廠商

///本部落格所有文章列表///

搜尋本部落格文章資料

目前日期文章:201309 (33)

瀏覽方式: 標題列表 簡短摘要

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 立即上傳(同步)螢幕截圖至雲端。

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

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

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

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

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

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

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

vincent 發表在 痞客邦 PIXNET 留言(1) 人氣()

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

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

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

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

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

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

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

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

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

有網友問到:在 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。

vincent 發表在 痞客邦 PIXNET 留言(10) 人氣()

有人有這樣的需求:想要將 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 內容都放在投影片中了。

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

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

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

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

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

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

儲存格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。

vincent 發表在 痞客邦 PIXNET 留言(1) 人氣()

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

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

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

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

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

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

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

vincent 發表在 痞客邦 PIXNET 留言(5) 人氣()

有人有這樣的需求,想要取出 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。再往下複製到各列中。

vincent 發表在 痞客邦 PIXNET 留言(2) 人氣()

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)

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

在 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)

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

格式:紅色粗體字型。

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

有讀者問到:在 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-列出週六日的日期

vincent 發表在 痞客邦 PIXNET 留言(3) 人氣()

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

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

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

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

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

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

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

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

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

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

vincent 發表在 痞客邦 PIXNET 留言(2) 人氣()

繼上篇文章:讓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 網站上觀賞了!

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

學校取得某次考試的成績,如果想要自行計算各科成績的五標(底標, 後標, 均標, 前標, 頂標),並標示學生的程度,該如何處理?以下以 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%)

vincent 發表在 痞客邦 PIXNET 留言(1) 人氣()

有人想要使用 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。

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

在 Excel 中常使用自動篩選來做為挑選資料的工具,而且提供使用「萬用字元」來自訂篩選,並且可以使用二個條件的邏輯運算。以下圖的資料為例,來練習自訂篩選。

(2) 篩選文字欄位

如果以「文字」欄位來自訂篩選,你可以看到「等於、不等於、開始於、結束於、包含、不包含」等篩選項目:

image5

這些篩選工具相當於在自訂篩選中使用萬用字元「?、*」的那些用法呢?其中「?」代表任何單一字,「*」代表任何連續字串。

例如:

開始於「電腦」 = 等於「電腦*」

結束於「電子」 = 等於「*電子」

包含「體」=「*體*」

不包含「腦」=不等於「*腦*」

 

(2) 篩選數字欄位

以「分數」這個數字欄位來篩選:

因為是數字欄位,所以可以進行關於「等於、不等於、大於、大於或等於、小於、小於或等於、介於」等算術運算。

當你自訂篩選時,可以使用雙條件透過 AND(且)、OR(或) 的邏輯運算。 例如:

要篩選:條件大於40 「且」 條件小於 80 者,會篩選出 40 ~ 80 者。

要篩選:條件小於40 「或」 條件大於 80 者,會篩選出 0 ~ 40 或 80 ~ 100 者。

要特別小心:

如果設為:條件大於40 「或」 條件小於 80 者,將篩選到每一個數字,篩選無作用。

如果設為:條件小於40 「且」 條件大於 80 者,將篩選不到任一個數字,篩選無作用。

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

有老師想要知道計算每次考試後前三名的平均成績,在 Excel 中,該用什麼公式?以下的例子就來找尋前三名的平均成績,並且算出多個名次為一組的各組平均成績。

參考下圖,要用人工方式找出名次分組再計算分組,是有點不容易!不過藉由陣列觀念可以輕鬆達到。

【準備工作】

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

【輸入公式】

(1) 計算前三名的平均

儲存格F1:=AVERAGE(LARGE(分數,{1,2,3}))

LARGE(分數,{1,2,3}):在 LARGE 函數中使用 {1,2,3} 陣列,表示要取成績最大的前三名。再透過 AVERAGE 函數求得此三者的平均。

其中使用常數陣列 {1,2,3},所以輸入完公式不需要按 Ctrl+Shift+Enter 鍵。

 

(2) 計算分次分組的各組平均

本例以 5 個名次為一組來分組。

儲存格F3:{=AVERAGE(LARGE(分數,ROW($1:$5)+(ROW(1:1)-1)*5))}

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

複製儲存格F3,貼至儲存格F3:F6。

ROW($1:$5)+(ROW(1:1)-1)*5:取得 1~5、6~10、11~15、16~20 的數字陣列。

透過 LARGE 函數取得各名次分組的分數陣列,再由 AVERAGE 函數將各組分數加以平均。

 

【延伸練習】

你也來練習求前 4 名的平均分數和以 4 個名次為一組,分組的平均分數之公式該如何處理?

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

在 Excel 2013 版已開始提供 XOR 函數,但是在 Excel 2013 之前的版本都沒有 XOR 函數。如果你想要執行 XOR 的邏輯運算效果,只能使用 NOT、AND、OR 函數來組成。

在你還沒有使用 Excel 2013 之前,先來做做邏輯訓練吧!先看一下 XOR 的真值表(參考下圖),以二個輸入 A, B,一個輸出 Y 來觀察,可視為二個輸入相同,則輸出為 FALSE,二個輸入不同,則輸出為 TRUE。在運算式中 TRUE 視為 1,False 視為 0。

【輸入公式】

以下五種做法,都可以達到 XOR 功能:

(1) 儲存格C2:=OR(AND(A2,NOT(B2)),AND(NOT(A2),B2))

(2) 儲存格C2:=NOT(OR(NOT(OR(A2,B2)),AND(A2,B2)))

(3) 儲存格C2:=OR(A2*NOT(B2),NOT(A2)*B2)

(4) 儲存格C2:=OR(A2*(1-B2),(1-A2)*B2)

(5) 儲存格C2:=(A2+B2)=1

複製儲存格C2,貼至儲存格C2:C5。

 

【延伸學習】

如果需要用到二個以上的輸入時,該如何建立函數呢?參考以下的文章找尋解答:

Excel-邏輯XOR運算

Excel-多條件的AND邏輯運算

Excel-陣列中的邏輯運算

線上邏輯閘模擬器

Excel-AND、OR和XOR的真值表

Excel-基本邏輯運算練習

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

先前一篇文章:將學生名條亂數重排是在 Excel 中,利用手動方式將學生名條亂數加以重新排列,有人又問到如何利用公式來執行亂數重排呢?(參考下圖)

【準備工作】

先選取儲存格C1:C29,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:亂數。

【輸入公式】

以下提供三種公式供練習:

公式一:

儲存格E2:=OFFSET($A$1,MATCH(LARGE(亂數,ROW(1:1)),亂數,0),COLUMN(A:A)-1,,)

LARGE(亂數,ROW(1:1)):在亂數陣列中取出最大的一個數。其中ROW(1:1)=1,往下複製時會產生2, 3, 4, …。

MATCH(LARGE(亂數,ROW(1:1)),亂數,0):將上式中取得的亂數藉由 MATCH 函數找出其位於亂數中的第幾個。

將上式(MATCH)代入 OFFSET 函數中,找出對應的儲存格,並顯示其內容。

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

 

公式二:

儲存格E2:=INDIRECT(ADDRESS(MATCH(LARGE(亂數,ROW(1:1)),亂數,0)+1,COLUMN(A:A)))

參考公式一之說明,將 MATCH 函數找到的儲存格位置,置入 ADDRESS 函數,轉成儲存格位址,再藉由 INDIRECT 函數,取出該儲存格的內容。

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

 

公式三:

儲存格E2:=INDEX(CHOOSE({1,2,3},亂數,座號,姓名),MATCH(LARGE(亂數,ROW(1:1)),亂數,0),COLUMN(B1))

參考公式一之說明,再加上此公式利用陣列觀念配合 CHOOSE 函數來做查表的動作,讀者可以參考另一篇文章:利用CHOOSE函數來輔助查表(VLOOKUP,INDEX,MATCH)中有詳細的說明。

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

 

只要每按一下 F9 鍵,即可產生一個亂數排序的結果。

vincent 發表在 痞客邦 PIXNET 留言(4) 人氣()

1 2

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼