2014年5月31日 星期六

Excel-模擬分數的計算式

小學生在剛遇見分數的運算時,實在是令人頭大的一件事,而父母親在檢查小孩功課時,即使使用計算機也無法顯示分數的結果(參考下圖)。心血來潮,設計一下可以顯示分數的運算式,可以套用在小孩的作業檢查上。

(1) 計算整數

儲存格G2:=INT(C3/C4+E3/E4)

(2) 計算分子

儲存格H2:=LEFT(TEXT((C3/C4+E3/E4)-G3,"???/???"),3)*1

(C3/C4+E3/E4)-G3:計算分數運算後的小數部分。

TEXT 函數中使用參數「???/???」,可得分子和分母都是三個位數的分數。

使用 LEFT 函數取出 TEXT 函數結果的最左邊三碼。

(3) 計算分母

儲存格H3:=RIGHT(TEXT((C3/C4+E3/E4)-G3,"???/???"),3)*1

使用 RIGHT 函數取出 TEXT 函數結果的最右邊三碼。

2014年5月28日 星期三

Excel-計算儲存格範圍中含有多個特定字元的儲存格個數

有網友根據這篇:計算儲存格範圍中含有特定字元的儲存格個數(ISERROR,SEARCH),來搜尋儲存格中是否含有某一特定字,網友想要知道如可查詢含有兩個字元的數量。(參考下圖)

以下的例子,要計算含有「民」含有「中時」的儲存格數量:

儲存格D2:{=SUM(NOT(ISERROR(SEARCH("民",$A$2:$A$30)))*1)}

儲存格D3:{=SUM(NOT(ISERROR(SEARCH("中時",$A$2:$A$30)))*1)}

儲存格D4:

{=SUM(NOT(ISERROR(SEARCH("民",$A$2:$A$30)*SEARCH("中時",$A$2:$A$30)))*1)}

其中兩個 SEARCH 函數之間的「*」,在觀念上是執行 AND 的邏輯運算。

如果要計算含有「民」含有「中時」的儲存格數量,公式如何下?

其結果為:=D2+D3-D4

Excel-將欄位中的資料重組為資料為欄位的表格(陣列公式)

前一篇文章:提到將欄位中的資料重組為資料為欄位的表格,網友還想要知道左邊表格如何轉換為右邊表格。(參考下圖)

方法應有很多,例舉使用陣列公式的做法:

儲存格H2:

{=OFFSET($A$1,0,SMALL(IF($B2:$E2="彈班",COLUMN($A:$D),99),COLUMN(A1)))}

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

IF($B2:$E2="彈班",COLUMN($B:$E),99):找出儲存格B2:E2中含有「彈班」的儲存格,對應至欄A:D,可傳回 1,2,3,4。若不含「彈班」者,傳回 99。(99的用意是要給一個較大的數值)

將上式置入 SMALL 函數,再利用 COLUMN(A1)=1 來取出第 1 小的值,當向右複製公式時會產生 COLUMN(B1)=2、COLUMN(C1)=3、COLUMN(D1)=4。

最後藉由 OFFSET 函數,根據儲存格A1的相對位址得到對應的「甲、乙、丙、丁」。

試試將 OFFSET 函數改為 INDIRECTADDRESS 函數:

儲存格H2:{=INDIRECT(ADDRESS(1,SMALL(IF($B2:$E2="彈班",COLUMN($B:$E),99),COLUMN(A1))))}

2014年5月27日 星期二

Excel-將欄位中的資料重組為資料為欄位的表格(COUNTIF,SUMPRODUCT)

有網友問到一個好像也蠻常會遇到的例子,在以下的 Excel 資料表中(參考下圖),如何將右側表格中欄位的資料重組為左側資料為欄位的表格?

在右側表格的每列中的資料不會重覆,而且每一列出的資料內容沒有特定的順序。

儲存格B2:=IF(COUNTIF($H2:$J2,B$1),"彈班","")

複製儲存格B2,貼至儲存格B2:E19。

COUNTIF($H2:$J2,B$1):先計算儲存格B1(甲)在儲存格H2:J2中出現的次數(1/0)。

IF(COUNTIF($H2:$J2,B$1),"彈班",""):如果上式結果為「1」,表示有出現過,則顯示「彈班」,否則顯示空白。

以下的公式寫法可以得到相同結果:(試試看!)

儲存格B2=IF(SUMPRODUCT(--($H2:$J2=B$1)),"彈班","")

2014年5月22日 星期四

Excel-將資料清單改放至合併儲存格中顯示(OFFSET)

有網友問到:在 Excel 的資料表已有一個資料清單,如何改放到合併儲存格中(例如:每三個儲存格合併為一格)?(參考下圖)

這種問題使用 OFFSET 函數最方便處理了。在下圖中儲存格C2是由三個儲存格合併而來。

儲存格C2:=OFFSET($A$2,INT(ROW(1:1)/3),,,)

複製儲存格C2(即儲存格C2:C4),往下各列貼上。

INT(ROW(1:1)/3):往下複製時會產生 INT(ROW(1:1)/3)=0、INT(ROW(4:4)/3)=1、INT(ROW(7:7)/3)=2、INT(ROW(10:10)/3)=3、…。

如果每 4 個儲存格合併成一個儲存格,則公式改為 INT(ROW(1:1)/4),依此類推。

2014年5月20日 星期二

Google地圖-景點提供資料速覽

大家使用 Google 來搜尋資料時,你應該有注意到在搜尋特定項目時,在視窗右側會出現「知識圖譜」提供的相關資訊:

現在這項功能,也部分在 Google 地圖上出現了,當你搜尋的景點是著名的景點/地點,也會出現「資訊速覽」,其提供的資料和知識圖譜內容一致。

當你輸入一個景點名稱時,如果看到「資訊速覽」時,點一下「資訊速覽」:

展開資訊可以對這個景點/地點有些初步的了解,對於在地圖上發現的景點,可以馬上獲得一些基本資訊,不用再切換至 Google 搜尋。

不止著名景點,特定的地點也會提供資訊速覽:

台大醫院:

Excel-計算一段大小區間內的平均值

有網友問到:如何取一個數列的一段數值範圍來計算平均值?參考下圖:

【使用陣列公式】

儲存格C2:{=AVERAGE(IF((A2:A20>400)*(A2:A20<600),A2:A20,FALSE))}

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

(A2:A20>400)*(A2:A20<600):其中「*」執行 AND 邏輯函數運算,表示取儲存格A2:A20中大於 400 且小於 600 者。

IF((A2:A20>400)*(A2:A20<600),A2:A20,FALSE):取得合於條件的儲存格範圍陣列。

將上式透過 AVERAGE 函數來計算平均。

 

【使用SUMPRODUCT函數】

儲存格C2:=SUMPRODUCT((A2:A20>400)*(A2:A20<600)*A2:A20)/SUMPRODUCT((A2:A20>400)*(A2:A20<600))

SUMPRODUCT((A2:A20>400)*(A2:A20<600)*A2:A20):計算合於條件的儲存格總和。

SUMPRODUCT((A2:A20>400)*(A2:A20<600)):計算合於條件的儲存格個數。

將上二式相除即可求得平均值。

Evernote-更換已設定的文字加密的密碼

先前的文章中提到在 Evernote 的記事中可以將局部文字予以加密,可以參考:
http://isvincent.pixnet.net/blog/post/38225911

有網友問到當第一次設定加密文字的密碼,在下一次使用時,會預設以這個密碼做為密碼,要如何更換密碼呢?對於每次都使用相同密碼有其方便性,不然在許多不同記事中使用了各種密碼,很有可能會忘記那篇記事使用了那個密碼。

image

當你輸入正確密碼後,就可以看到原始文字,下圖中有加框的文字即為加密後被解開的文字,下次使用時,還是得輸入相同密碼才能解開來。如果選取永久解密文字,才能真正的去除文字加密。

image

如果你要更換和上次不同的密碼,則在你再次加密文字時,故意打和上一次不同的密碼即可。

Evernote 會顯示讓你選取要再次輸入密碼,還是要建立新密碼。

image

當你選取「建立新的複雜密碼」,即可進入重新建立密碼的程序了。

image

你可能會遇到以下的狀況:

1. 原先已設定好加密文字的密碼,要使用當時設定的密碼才能解開。

2. 如果使用當時設定的密碼解開,要再次加密時,會預設使用最新設定的密碼。

3. 設定過多的不同密碼,可能會導致自己也記不得密碼,千萬小心使用。

2014年5月19日 星期一

Excel-模擬MROUND函數(MROUND,MOD)

有網友問到在 Excel 中,如果要取一個數值最近某一數的倍數,可以使用 MROUND 函數,但是在稍早的 Excel 版本,該如何處理呢?

參考下圖,在儲存格B3中輸入公式「=MROUND(A3,$B$1)」,即可取得儲存格A3中數值最接 30 的倍數的數值。

MROUND 函數為:傳回四捨五入為所需倍數的數字。
其語法為:MROUND(number, multiple)

如果你使用的 Excel 版本並沒有提供 MROUND 函數,則只好利用公式自行運算了!

例如以下的公式可以參考使用:

儲存格B3:=IF(MOD(A3,$B$1)>=$B$1/2,A3-MOD(A3,$B$1)+$B$1,A3-MOD(A3,$B$1))

  • MOD(A3,$B$1)>=$B$1/2:判斷儲存格A3除以儲存格B1的餘數是否大於儲存格B1的一半。
  • 如果上式為真,則表示要進位。結果為儲存格A3+儲存格B1-儲存格A3除以儲存格B1的餘數。
  • 如果上式為假,則表示要捨去。結果為儲存格A3-儲存格A3除以儲存格B1的餘數。

如果想要縮短上述的公式,可以試試以下的寫法:

儲存格B3:=A3-MOD(A3,$B$1)+(MOD(A3,$B$1)>=$B$1/2)*$B$1

2014年5月15日 星期四

Prezi-將簡報儲存成PDF格式來保存

有老師問到使用雲端簡報工具 Prezi 來製作簡報效果很好,但是如何像 PowerPoint 一樣可以印出來或是加以儲存成檔案來保存呢?現在教師評鑑、教學檔案製作有時要用到書面資料。

Prezi 雲端簡報的動畫播放效果十分吸引人,要保存成一頁一頁的簡報內容其實是可以的,Prezi 提供了儲存格 PDF 的功能。

當完成簡報的設計之後,選取「Share」之下的「Download as PDF」指令:

系統開始會一頁一頁的儲存成 PDF 檔,等待出現 Finished all pages,即可按「Save PDF」來儲存。

Prezi 會將檔案儲存成 yourprezi.pdf,一頁一頁的簡報其實就是 Prezi 視窗左側相同的內容。

Evernote-直接取用記事來做簡報

教學上,我常使用 Evernote 來做研習課程的講義,通常只要透過共用指令,取得一個URL,即可將一個記事轉換為網頁,並且使用瀏覽器來檢視這個記事。

這是 Evernote 上的記事,必須透過 Evernote 軟體來檢視:

這是網頁上的記事,可以用瀏覽器在任何地點檢視:

現在 Evernote 的更新版本,已可以在 Evernote 上直接來播放簡報了。只要開啟這個記事後,點選工具列上的「簡報」:

Evernote 的視窗會轉為全螢幕,滑鼠游標會變成彩帶般,在此模式下來播放簡報:

也可以點選一張圖片,進入圖片檢視模式,:

將滑鼠移至螢幕左右兩側,即可切換上一張/下一張圖片:

如此一來,即可透過 Evernote 軟體直接將記事當成簡報來播放了,只是目前播放功能還很陽春,期待播放效果會愈來愈好。

2014年5月13日 星期二

Excel-列出每年母親節的日期(WEEKDAY)

母親節剛過,有人問到如何在 Excel 的工作表中列出每年母親節的日期?

因為母親節是固定在5月的第2個星期日,它是個明確且固定的規則,所以只要使用公式,即可輕鬆取得每年的母親節日期。(參考下圖)

儲存格B2:=DATE(A2,5,1)+14-WEEKDAY(DATE(A2,5,1),2)

DATE(A2,5,1):取得儲存格A2所代表年份的5月1日的數值,例如:2014/5/1的數值為41760。

WEEKDAY(DATE(A2,5,1),2):取出該年5月1日為星期幾的數值,在此使用參數「2」,代表傳回值和星期幾的對照關係為傳回1表示星期一、…、傳回7表示星期日。

14-WEEKDAY(DATE(A2,5,1),2):計算第二個星期日距5月1日的天數。本例為:10

將上面二個式子的結果相加即為所求,例如: 41760+10 = 41770,即為 2014/5/11。

複製儲存格B2,往下各列貼上。

 

【同場加映】

你也來練習把某一年每個月第二個星期日的日期通通找出來?

儲存格B3:=DATE($B$1,ROW(1:1),1)+14-WEEKDAY(DATE($B$1,ROW(1:1),1),2)

2014年5月8日 星期四

Excel-在日期清單中分年分月計算小計(SUMPRODUCT)

有網友問到,在一個 Excel 的資料表中含有日期和數量的清單,如何分年分月的統計加總結果?(參考下圖)

【準備工作】

選取A欄和B欄中含有資料的儲存格,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期、數量。

【輸入公式】

本例可使用 SUMPRODUCT 函數執行多條件 AND 結果的加總運算。

儲存格E2:=SUMPRODUCT((YEAR(日期)=E$1)*(MONTH(日期)=ROW(1:1))*數量)

YEAR(日期)=E$1:條件一,判斷日期陣列中每個日期的「年份」是否和儲存格E1相同,結果傳回 TRUE/FALSE 陣列。

MONTH(日期)=ROW(1:1):條件二,判斷日期陣列中每個日期的「月份」是否和ROW(1:1)(=1)相同,即為一月。如果公式往下複製時,ROW(1:1)=1 → ROW(2:2)=2 → ROW(2:3)=3 …,如此可以判斷每一個月。

將上述二個條件的 TRUE/FALSE 的結果相乘,再乘以「數量」的陣量,然後加總結果,即為所求。

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

2014年5月7日 星期三

Excel-產生數個固定星期幾順序的數列(WEEKDAY)

有網友問到,在 Excel 中如何產生數個固定星期幾順序的數列,例如星期二三五日。(參考下圖)

首先在儲存格A2中,先輸入第一個日期(必須為星期二三五日其中之一)。

接著在儲存格A3中輸入公式:

儲存格A3:=A2+VLOOKUP(WEEKDAY(A2,2),{2,1;3,2;5,2;7,2},2,FALSE)

複製儲存格A3,往下各列貼上。

WEEKDAY(A2,2):參數2乃指定傳回傳星期一到星期日對應為1到7。

image

{2,1;3,2;5,2;7,2}:在 VLOOUP 函數中使用二維陣列(注意陣列中的逗號和分號的使用),其中星期二(2)對應至數值1、星期三(3)對應至數值2、星期五(5)對應至數值2、星期日(7)對應至數值2。

例如:若儲存格A2為星期三(3)即加2,傳回星期五。儲存格A2為星期日(7)即加2,傳回星期二。

檢視其他文章

好康東東