2015年8月31日 星期一

在Google地圖上查詢座標更方便了

最近使用 Google地圖來查詢座標變的更方便了!
以往,你會在 Google 地圖上按右鍵,選取「這是那裡」,來取得地點的坐標。
image
現在,你只要在地圖的上某個位置點選一下,即會出現該位置的座標和大概地址,而且可以立即進入街景檢視,和直接規劃路線。
當你將滑鼠移至地址和座標上,則在 Google 地圖上也會標示出對應的位置。

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

開學了,老師同仁問到如何利用 Excel 在一次全年級的學生考試成績中找出:頂標、前標、均標、後標、底標的成績?
先來看看目前五標的定義為:
頂標:該科成績位於第88百分位數之考生級分
前標:該科成績位於第75百分位數之考生級分
均標:該科成績位於第50百分位數之考生級分
後標:該科成績位於第25百分位數之考生級分
底標:該科成績位於第12百分位數之考生級分
如下圖,本例中每個科目都有 434 個學生參考試,來試試分別求出各科及總分的五標。

【公式設計與解析】
底標/儲存格B436:=SMALL(B$2:B$435,COUNT(B$2:B$435)*12%)
後標/儲存格B437:=SMALL(B$2:B$435,COUNT(B$2:B$435)*25%)
均標/儲存格B438:=SMALL(B$2:B$435,COUNT(B$2:B$435)*50%)
前標/儲存格B439:=SMALL(B$2:B$435,COUNT(B$2:B$435)*75%)
頂標/儲存格B440:=SMALL(B$2:B$435,COUNT(B$2:B$435)*88%)
利用 COUNT 函數算出所有成績的數量,再乘以各標之百分位數,將結果放入 SMALL函數來求得該百分位數所對應的分數。
複製儲存格B436:B440,貼至儲存格B436:G440。

【思考題】
若將上述的 SMALL 函數改以 LARGE 函數來設計,該如何設計公式?

Excel-轉換文字日期格式為數值日期格式(DATE,MID,TEXT,數值格式設定)

網友們高頻率會詢問到在 Excel 中如何來轉換日期格式,例如:1040702 轉換為 104/07/02。這類的問題該如何處理?
在 Excel 中,日期是一個數值,1040702 並不是一個標準的日期數值,如果拿來轉換後,若是以顯示的角度來看(格式),轉換後可以是數字,也可以是文字。而數字可以用來執行日期的運算。

【公式設計與解析】
(1)轉換為數值格式
儲存格C2:=DATE(MID(A2,1,3)+1911,MID(A2,4,2),MID(A2,6,2))
MID(A2,1,3):利用 MID 函數,由儲存格A2中取出1~3碼當為日期的年,但是日期是西元年表示,所以要在加上 1911。
MID(A2,4,2):利用 MID 函數,由儲存格A2中取出4~5碼當為日期的月。
MID(A2,6,2):利用 MID 函數,由儲存格A2中取出6~7碼當為日期的日。
將以上的三個值利用 DATE 函數轉換為一個日期,但得到的是一個西元年格式。
接著,設定儲存格A2的儲存格數值格式,先選取「日期」類別,再於行事曆類型中選取「中華民國曆」,然後選取「101/3/14」項。目前的結果是將 1040702 轉換為 104/7/2。
接下來,再於類別中選取「自訂」,將 [$-404]e/m/d;@ 修改為 [$-404]e/mm/dd;@。(mm 表示月要以 2 碼表示,dd 表示日要以 2 碼表示。)
複製儲存格C2,貼至儲存格C2:C16。

(2) 轉換為文字格式
儲存格C2:=MID(A2,1,3) & "/" & MID(A2,4,2) & "/" & MID(A2,6,2)
轉換結果為文字格式,所以如果拿來運算,會出現 #VALUE! 錯誤訊息。

(3) 轉換為文字格式
儲存格C2:=TEXT(DATE(MID(A2,1,3)+1911,MID(A2,4,2),MID(A2,6,2)),
"[$-404]e/mm/dd;@")
綜合 (1) 的觀念結合到公式中,透過 TEXT 函數來設定格式,其結果仍為文字格式。

2015年8月30日 星期日

Excel-計算多條件的篩選的數值平均(陣列公式,SUMPRODUCT,MATCH)

在 Excel 中用來計算平均的方式很多,也有函數可以直接使用。但是,如果要依據多個條件來篩選出數值再計算平均,或許就會比較傷腦筋。
如下圖中的資料表,如果只想取出乙和丁的數值,並計算二者所有數值的平均,該如何處理?通常公式較長時,配合陣列來設計可以縮短公式,又該如何處理?

【公式設計與解析】
在儲存格D2中例舉以下幾種公式寫法:
(1) 使用 SUMPRODUCT 函數
=(SUMPRODUCT((人員="乙")*數值)+SUMPRODUCT((人員="丁")*數值))/
(SUMPRODUCT(--(人員="乙"))+SUMPRODUCT(--(人員="丁")))
(A) (SUMPRODUCT((人員="乙")*數值)+SUMPRODUCT((人員="丁")*數值))
用以計算人員是「乙」的數值總和+人員是「丁」的數值總和
(B) (SUMPRODUCT(--(人員="乙"))+SUMPRODUCT(--(人員="丁")))
用以計算人員是「乙」的個數+人員是「丁」的個數。其中,公式中的「--」,用以將判斷式傳回的 TRUE/FALSE 陣列轉換為 1/0 陣列。
將 (A)式/(B)式,即為乙和丁的數值平均值。

(2) 使用 SUMPRODUCT 函數+陣列表示法
=SUMPRODUCT((人員={"乙","丁"})*數值)/SUMPRODUCT(--(人員={"乙","丁"}))
使用陣列 {"乙","丁"},可以將兩個計算式變成一個。

(3) 使用陣列公式
{=AVERAGE(IF((人員="乙")+(人員="丁"),數值,FALSE))}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動產生「{}」。(以下做法一致)
其中的 FALSE 參數不可以留下空白或是以 0 表示,否則每一個數值都會併入平均計算。

(4) 使用陣列公式+陣列表示法
{=AVERAGE(IF((人員={"乙","丁"}),數值,FALSE))}
透過 {"乙","丁"} 將公式縮短,可以將公式中的兩個項目變成一個項目。
{=AVERAGE(IF(ISNUMBER(MATCH(人員,{"乙","丁"},0)),數值))}
透過 MATCH 函數比對人員中是否為乙或丁,若是則傳回對應的數值,若否則傳回錯誤訊息,二者組成一個陣列。再使用 ISNUMBER 函數取出是數值的內容,最後透過AVERAGE 函數來計算被取出數值的平均。
{=AVERAGE(IF(NOT(ISERROR(MATCH(人員,{"乙","丁"},0))),數值))}
原理同上式,改用 NOT 函數和 ISERROR 函數來找出非錯誤訊息的儲存格,相當於找出數值的儲存格。

2015年8月29日 星期六

Excel-動態選取列印範圍(名稱定義,CHOOSE)

在網路上看到有人在 Excel 中,利用選項按鈕來決定列印時的資料範圍,這個是很有用的工具。因為把多個資料做在同一個工作表中有其方便性,但是在列印時,不見得同時要印出每一個資料表,或是有時只想列印某一個資料表。
用以下一個工作表中所建立的三個資料表,來示範如何操作:
1. 利用[開發人員]功能表插入三個選項按鈕,分別修改標籤為:A表、B表、C表。
2.接著設定儲存格連結至一個儲存格(本例為儲存格K20)。
3. 選取一個資料表範圍(本例選取儲存格A2:B21)。
4. 選取[版面配置/列印範圍]功能表中的「設定列印範圍」。
5. 觀察名稱管理員,可以發現,Excel 已自動建立一個稱:Print_Area,而範圍即為步驟2所選範圍。
6. 分別根據圖中的A表、B表、C表的儲存格範圍,建立名稱為「A表、B表、C表」的名稱。結果如下圖:(儲存格位址使用絶對參照)
7. 修改名稱 Print_Area 的資料範圍。
參照到:=CHOOSE(公式!$K$20,A表,B表,C表)。
如此,只要利用選項按鈕,即可控制列印的範圍(不同資料表)。

使用微軟的Snip工具輕鬆製作教材和快樂分享使用微軟的Snip工具輕鬆製作教材和快樂分享

開學了,學校老師們又要忙著做講義、備課(其實大多數老師是不分學期中或是寒暑假的),要結合簡報和錄製成影片可以讓學生翻轉個夠,也能用來分享是現在的趨勢。而先前介紹的一篇文章:PowerPoint-利用 Office Mix 將簡報轉換為線上學習教材,提到了微軟透過一個 App 在 PowerPoint 中將投影片和簡報過程錄下,可以上傳到 Office Mix 網立站。
最近微軟推出了一個更簡易、更輕巧的工具:Snip,也可以用來製作教材或是在上課過程中錄製過程,可以提供學生補救教學。微軟的 Snip 目前還是 Beta 版,而且目前不用輸入帳號即可使用和上傳。Snip 是以螢幕上呈現的內容為主,讓你可以抓下畫面即可以透過其錄音、畫筆和白板工具來將一段過程錄製成影片,這個影片可以上傳至 Office MIX,也可以轉存成 MP4 影片。
Snip 有別於在 PowerPoint 中操作的地方是以一個畫面或是一張照片為基礎來操作,而在 PowerPoint 來操作則是可以多張投影片進行;而在 Snip 中不能像在 PowerPoint 中可以連人像(或影物)一起攝影下來。
下載 Snip(預覽版):https://mix.office.com/Home/SetupSnip
Snip 下載後要安裝才能使用,執行 Snip 時,你可以執行 螢幕截圖(Catpure)、白板(Whiteboard)和 Webcam 功能:
先利用 Catpure 功能抓取一個局部畫面、視窗或是整個螢幕的畫面,或是利用 Webcam 拍攝一張照片。
在你抓下螢幕畫面或是拍攝照片後,就可以使用其畫筆挑選不同粗細和色彩來寫字或畫圖,它所提供的錄音功能,可以讓你邊講邊寫(畫)。
完成的結果可以試播、回復、刪除、複製等。
透過 Link 功能可以立即上傳至 https://mix.office.com/ 網站(目前不用登入帳號),隨即可以在剪貼簿中取得這個作品的網址,因此你可以使用 Email 來分享,或是複製到其他應用程式使用。
例如,將複製的結果貼在 Word 文件中,則會看到一個預覽圖和超連節。
如果你使用 Embed 功能,則可以取得一個 的程式,讓你輕鬆插入網頁或是部落格中來展示。例如:
使用者在瀏覽器中透過你的分享(上述的網址),可以在線上操作你的作品。
另外,你也可以使用 Save 功能將你的作品儲存為一個 MP4 的影片,用於單機播放。而如果想要回顧自己做過那些作品,則可以按一下 Library 來檢視,在此你可以移除不想要放在 Library 中的作品。

2015年8月27日 星期四

Excel-輸入年月日來產生星期幾(DATE,TEXT,RIGHT)

網友問到:在 Excel 中希望能在輸入年、月、日後,自動顯示星期幾(如下圖),有那些公式可用?
要轉換年月日為一個日期數值,可以使用 DATE 函數;若要將一個日期顯示為星期幾,則可以使用儲存格格式設定或是使用 TEXT 函數來處理?
(1)
轉換日期為一個數值,儲存格D2:=DATE(A2,B2,C2)
再設定儲存格格式:日期/星期三
(2)
儲存格D5:=RIGHT(TEXT(DATE(A5,B5,C5),"[$-404]aaaa;@"),1)
儲存格D5:=RIGHT(TEXT(DATE(A5,B5,C5),"[$-404]aaa;@"),1)
TEXT(DATE(A5,B5,C5),"[$-404]aaaa;@"):產生「星期四」文字格式的結果。
TEXT(DATE(A5,B5,C5),"[$-404]aaa;@"):產生「週四」文字格式的結果。
再利用 RIGHT 函數取出最右邊一個字(本例為「四」)。

2015年8月26日 星期三

Excel-將資料表中的欄位名稱和資料內容互換(INDEX,MATCH,IFFERROR)

在 Excel 的工作表中取得一個如下圖左的資料表,其主要是在「甲、乙、丙」三個欄位中填入「A、B、C」。該如何轉換為如下圖右的資料表?其主要是在「A、B、C」三個欄位中填入「甲、乙、丙」。
【公式設計】
儲存格G1:=IFERROR(INDEX($C$1:$E$1,,MATCH(G$1,$C2:$E2,0)),"")
複製儲存格G1,貼至儲存格G1:I21。
MATCH(G$1,$C2:$E2,0):利用 MATCH 函數找出儲存格G1的內容在儲存格C2:E2中的位置(傳回一個順序的位置),如果儲存格內容不存在,則傳回錯誤訊息(#N/A)。
INDEX($C$1:$E$1,,MATCH(G$1,$C2:$E2,0)):利用 INDEX 函數以查表方式查詢上式中的位置順序數字在儲存格C1:E1中所對應的儲存格內容(甲、乙、丙)。
最後使用 IFFERROR 函數將錯誤訊息(#N/A)顯示為空白("")。

利用Google地圖的地形檢視模式查詢大約高度

  • 全家要出遊,有時要上山下海的,孩子們除了會問那裡有沒有 WiFi 之外,也會問到山有多高?當然就會透過 Google 查閱相關資料盡力查到山有多高。但是,其實手機上的 Google 地圖會也有相關的資料可看。
    當你在 Google 地圖上查詢到相關的位置後,在功能表上切換到「地形」。
     
    你試著縮放不同大小的層級:
     
    在某些大小之下,即可發現顯示了「等高線」,並且每間隔 100 公尺會有標示。我就用這個功能約略的回答孩子們的疑問。
    當然,使用電腦在 Google 地圖上檢視「地形」,也會顯示等高線和標示高度。

2015年8月25日 星期二

利用將照片上傳Google雲端硬碟就可以取出其中的文字

就要開學了,有網友和學校同仁同時問到:聽過人家說上傳圖片檔到 Google 雲端硬碟就可以自動辨識圖片中的文字,該如何處理呢?因為在學校中很多文件是以掃描形式存在的,而且老師備課是非常辛苦的,每次再重新打過,時間花的很不值得。更何況人手一隻的手機,隨手一拍就把文件數位化了,如果能快速取出文字,將非常有助於教學與備課。
沒錯,Google 雲端硬碟很早就推出的功能。目前版本的做法是:
先上傳圖片檔到 Google 雲端硬碟:
一般圖片檔你可以檢視、下載,但沒有取得文字的工具。
所以,你可以在檔案上按右鍵,選取[選擇開啟工具],再選取「Google 文件」。
當這個圖片送到新增的 Google 文件時,原始圖檔會被置入文件中,同時也會產生該圖檔中的文字。(順便觀察一下,其中會有些辨識不正確之處。)
為求方便,如果拿起手機順手拍了一張文件:
轉換成 Google 文件(如下圖),也是可以取出大部分是正確的字。
如果你上傳的 PDF 檔,然後用 Google 文件來開啟:
也會以一頁的 PDF 內容為單位,轉換出其中的文字和圖片,再接下一頁來轉換。

2015年8月24日 星期一

在手機上的Google地圖可以更方便使用街景檢視

最近更新的 Google 地圖在街景檢視功能有一些方便的調整。以往當你在頁面上釘選(放置圖釘)一個位置時,必須由下往上滑動後,即會顯示「街景服務」區塊,點選後即可顯示街景。
 
現在新的功能放在畫面左下角,當釘選一個位置時,只要點選出現的街景小視窗,即會立即進入街景檢視。
 

Excel-略過空白儲存格重整資料(多欄,陣列,INDEX)

有讀者根據這篇:Excel-略過空白儲存格重整資料(陣列,OFFSET)文章,想要將下圖左含有空白列的資料清單改成去除空白的資料清單(下圖右),該如何處理?
第(1)式
儲存格D2:{=SMALL(IF($A$1:$A$17<>"",ROW($A$1:$A$17),999),ROW(2:2))}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動產生「{}」
IF(廠商<>"",ROW(廠商),999):判斷廠商陣列中是否不為空白儲存格,若成立則取得列號陣列,不成立則給予一個很大的值(本例為999)。本例結果為{2,3,5,6,7,…}。
SMALL(IF(廠商<>"",ROW(廠商),999),ROW(1:1)):根據上式取得的列號陣列,取出最小的一個列號(ROW(2:2)=2),當向下複製公式時,可以依序取得較小的第 2、3、…之值。
第(2)式
儲存格D2:{=INDEX($A$1:$B$17,第(1)式,COLUMN(A:A))}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動產生「{}」
COLUMN(A:A)=1,當向右複製公式時,會變為 COLUMN(B:B)=2。
透過 INDEX 函數利用查表方式取得A欄中的第一筆資料「子」,而看到的「#REF!」是錯誤訊息,乃因位址參照錯誤產生查詢不到資料。
複製儲存格D2,貼至儲存格D2:E17。
第(3)式
儲存格D2:{=IFERROR(第(2)式,"")}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動產生「{}」
透過 IFERROR 函數將產生的錯誤訊息(#REF!)轉換為空白。
複製儲存格D2,貼至儲存格D2:E17。

檢視其他文章

好康東東