2018年6月30日 星期六

Excel-根據年和月產生月曆並標示星期六日(DATE,RIGHT,TEXT)

如何在 Excel 的工作表中指定年和月,即可產生該月的月曆表?
參考下圖:
1. 其中日期儲存為完整日期(年/月/日),但是只顯示「日」的數值。
2. 星期六和星期日的儲存格顯示不同色彩。
根據年和月產生月曆並標示星期六日(DATE,RIGHT,TEXT)

【公式設計與解析】
儲存格B3:=DATE($A$2,$B$2,COLUMN(A:A))
根據設定的年和月,藉由 DATE 函數轉換為日期格式。公式向右複製時,COLUMN(A:A)=1→COLUMN(B:B)=2→COLUMN(C:C)=3→ …..。
儲存格B4:=RIGHT(TEXT(B3,"[$-zh-TW]aaaa;@"),1)
利用 TEXT 函數設定日期格式:星期一。再以 RIGHT 函數取出最後一個字元。
複製儲存格B3:B4,貼至儲存格B3:AF4。
根據年和月產生月曆並標示星期六日(DATE,RIGHT,TEXT)
如果要考量不同月份會有不同的日數,例如:28、30、31。修改公式:
儲存格B3:=IF(DATE($A$2,$B$2,COLUMN(A:A))>=DATE($A$2,$B$2+1,1),"",
DATE($A$2,$B$2,COLUMN(A:A)))
若要設定星期六和星期日顯示不同的儲存格色彩,則:
首先,選取儲存格B5:AF14。
接著,設定儲存格的格式化條件。
規則類型:使用公式來決定要格式化哪些儲存格。
編輯規則:=WEEKDAY(B$3,2)>5
設定格式:儲存格底色為粉紅色
根據年和月產生月曆並標示星期六日(DATE,RIGHT,TEXT)

2018年6月28日 星期四

Excel-根據數值最後一碼調整(CHOOSE,MOD)

在 Excel 的工作表中有一個數值清單,要根據每個數值的最後一碼做不同的調整。
條件:(參考下圖)
最後一碼是奇數時,進位為偶數。
最後一碼為7、8、9時,進位為10。
Excel-根據數值最後一碼調整(CHOOSE,MOD)

【公式設計與解析】
儲存格C2:=A2+CHOOSE(MOD(A2,10)+1,0,1,0,1,0,1,0,3,2,1)
複製儲存格C2,貼至儲存格C2:C16。
MOD(A2,10):找出儲存格A2的個位數(0, 1, 2, .... ,9)
利用 CHOOSE 函數對 0~9 要加上的數值,即為所求。
如果你想運用 Excel 提供的進位函數,可以參考這篇:Excel-各種進位方式

2018年6月25日 星期一

Goolge-在手機Google地圖上顯示經度和緯度、plus codes和量測距離

大家在使用的 Google 地圖可以使用經度和緯度來查詢位置,並且進行導航。但是如果你想要知道某個地點的經度和緯,該如何取得?
在電腦版的 Google 地圖上,只要點選地圖上任意一個地點,就會得到一個標記,視窗下方就會顯示該地點的經度和緯度。
Goolge-在手機Google地圖上顯示經度和緯度、plus codes和量測距離
如果是使用手機的 Google 地圖 App 呢?你只要長按地圖上的某個位置,Google地圖除了標記這個位置,並會在搜尋框中顯示該地點的經度和緯度資料(以十制制表示)。
Goolge-在手機Google地圖上顯示經度和緯度、plus codes和量測距離
以上二個實例都是以十進制顯示經度和緯度的資料,如果想要轉換經度和緯度為「度分秒」的格式,可以參考:http://isvincent.pixnet.net/blog/post/46197634
其轉換的公式:
X度Y分Z秒 =X+Y/60+Z/3600 度
除了使用經度和緯度來定位之外,還可以使用 plus codes 來表示。
Goolge-在手機Google地圖上顯示經度和緯度、plus codes和量測距離 Goolge-在手機Google地圖上顯示經度和緯度、plus codes和量測距離
點選 plus codes 就會複製這個 plus codes,將它貼至 Google 地圖上,即可顯示點選的位置。
Goolge-在手機Google地圖上顯示經度和緯度、plus codes和量測距離
plus codes 的原理,請參考:https://plus.codes/,這個碼是一個 global code,由 area code 和 local code 所組成。
Goolge-在手機Google地圖上顯示經度和緯度、plus codes和量測距離
除了經度和緯度的資訊,在手機 Google 地圖 App 也和網頁版一樣,可以讓你量測距離了。先點選「量測距離」。拖曳手機畫面,就會即時顯示距離。若按一下「+」,則可以產生新的一段量測距離。
Goolge-在手機Google地圖上顯示經度和緯度、plus codes和量測距離 Goolge-在手機Google地圖上顯示經度和緯度、plus codes和量測距離

2018年6月22日 星期五

Excel-對含有單位的儲存格加總數值(SUMPRODUCT,SUBSTITUTE)

例如,當你在 Excel 的工作表中取得一個金額的清單,其中每個金額都附上了單位,該如何計算其總計金額?
如下圖,原始資料中的金額後都有單位:元,以這個清單如何直接計算加總?
Excel-對含有單位的儲存格加總數值(SUMPRODUCT,SUBSTITUTE)

【公式設計與解析】
儲存格C2:=SUMPRODUCT(SUBSTITUTE(A2:A20,"元","")*1)&"元"
首先,在 SUMPRODUCT 函數中,利用 SUBSTITUTE 函數將儲存格A2:A20裡的「元」,全部置換為空白字元。
因為,SUBSTITUTE 函數傳回的結果是文字,所以再以「*1」運算,將文字數字轉換為數值數字。
最後,以 SUMPRODUCT 函數加總,並且串接(& )字串「元」。

2018年6月21日 星期四

Workflowy-有效利用有限的項目容量

最近讓學生在自主學習方面,練習使用 Workflowy 來做為規劃的工具,學生說預設100條項目/每月,有可能會一下就用完這些容量,而又不想花錢申請 Pro 版,該如何有效運用這些項目容量?
Workflowy-有效利用有限的項目容量
當你申請 Workflowy 的帳號時,就可以免費獲得每個月 100 條項目的基本容量,你可以透過介紹他人(或是自己的另一個帳號)申請,而雙方都會獲得增加每個月 100 條項目的容量,此時每個月你就有 200 條項目的容量了。
Workflowy-有效利用有限的項目容量
當這一個月新增了 200 條項目時,必須等下個月才能再新增 200 條項目,但是前一個月已輸入的內容仍會存在,因此,一年(12個月)後,你最多可以有 2400 條項目的容量(一年內你會用的完 2400 條項目?)。所以申請初期,如果每個月的 200 條容量沒用完,有點可惜!
學生嘛!總是克難一點。或許,你可以將這些未使用的項目輸入特定的文字先占著,下個月如果用量較大時,可以取用這些備用的項目。例如下圖中,在未用完的項目,先輸入「待用」文字。
Workflowy-有效利用有限的項目容量
下個月要使用前面留下的項目容量時,可以使用快速鍵來移動位置,取出來用。
Alt+Shift+向上鍵:項目上移
Alt+Shift+向下鍵:項目下移
Tab:移至下一層
Shift+Tab:移至上一層
Workflowy-有效利用有限的項目容量

2018年6月19日 星期二

Excel-計算編號為N的倍數且數值大於M的個數(SUMPRODUCT,MOD,ROW)

在 Excel 的工作表中,如下圖的數值清單裡,要如何計算編號為 3 的倍數者,其大於50的個數有幾個,該如何處理?
Excel-計算編號為N的倍數且數值大於M的個數(SUMPRODUCT,MOD,ROW)

【公式設計與解析】
儲存格D2:=SUMPRODUCT((MOD(ROW(B2:B26),3)=1)*(B2:B26>50))
在 SUMPRODUCT 函數裡,使用兩個條件式來運算。
(1) 條件一:MOD(ROW(B2:B26),3)=1
判斷 MOD(ROW(B2:B26),3)= 1 是否成立,傳回 TRUE/FALSE 陣列。
因為 ROW 函數會傳回儲存格的列號,所以 ROW(B2)=2、ROW(B3)=3、ROW(B4)=4、……。
MOD(ROW(B2),3)=2、MOD(ROW(B3),3)=0、MOD(ROW(B4),3)=1、......。
因為編號 A003 在儲存格B4,所以利用 MOD(ROW(B2:B26),3)=1 來判斷編號是否為 3 的倍數。
(2) 條件二:B2:B26>50
判斷儲存格 B2:B26 的內容是否大於 50,傳回 TRUE/FALSE 陣列。
(3) ((MOD(ROW(B2:B26),3)=1)*(B2:B26>50)
其中的「*」運算會將 TRUE/FALSE 陣列轉換為 1/0 陣列。
最後於 SUMPRODUCT 函數中將 1/0 陣列相加,結果即會符合二個條件的個數。
文章標籤

2018年6月17日 星期日

Windows 10-調整桌面圖示大小與利用時間軸開啟過去使用的文件

在 Windows 10 裡的桌面圖示,預設有三種大小:大圖示、中圖示、小圖示。
Windows 10-調整桌面圖示大小與利用時間軸開啟過去使用的文件
其實,你可以自訂想要的圖示大小。按著 Ctrl 鍵,再滾動滑鼠滾輪,就可以自訂調整喜歡的圖示大小。
Windows 10-調整桌面圖示大小與利用時間軸開啟過去使用的文件
如果你按下 Alt+Tab 鍵可以切換目前桌面開啟的應用程式,而當你按下工作列上的「工作檢視按鈕」,或是按 Ctrl+Tab 鍵,可以切換至「時間軸」模式。
Windows 10-調整桌面圖示大小與利用時間軸開啟過去使用的文件
在時間軸模式下,滾動滑鼠滾輪或是拖曳時間軸中的圓點,可以切換至最近30日的某一天。會列出當日所開啟的文件,點選該工作,即可以開啟應用程式與文件。
Windows 10-調整桌面圖示大小與利用時間軸開啟過去使用的文件

Excel-執行儲存格中的運算式(SUBSTITUTE,EXT,EVALUATE)

在 Excel 中如果想要執行儲存格中的運算式可以利用 EVALUATE 函數,但是要把檔案儲存為巨集活頁簿(*.xlsm)。
以下圖為例,輸入字串: $9,861-$6,296,要直接得到結果:$3,565,該如何處理?
Excel-執行儲存格中的運算式(SUBSTITUTE,EXT,EVALUATE)

【公式設計與解析】
先將儲存格中的字串加以轉換。(將「X」取代為「*」、將「$」和「,」取代為空白)
儲存格B2:=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"X","*"),"$",""),",","")
複製儲存格B2,貼至儲存格B2:B9。
接著,定義名稱。
名稱:Cal
參照到:=EVALUATE(工作表1!B2)
Excel-執行儲存格中的運算式(SUBSTITUTE,EXT,EVALUATE)
儲存格C2:=TEXT(Cal,"$#,##0")
將計算結果套用格式:$#,##0。
複製儲存格C2,貼至儲存格C2:C9。

Excel-字串重組(MID,LEFT,FIND)

在 Excel 中有一個資料清單(如下圖左),如果想要重組其中的文字(如下圖右),該如何處理?
在下圖中,要重組其中的文字,主要是要把英文後的數字移到英文字之前,再把「-」去除。其中兩組數字,可能為一碼,也可能為二碼。
Excel-字串重組(MID,LEFT,FIND)

【公式設計與解析】
儲存格C2:
=MID(A2,2,FIND("-",A2)-2)&LEFT(A2,1)&MID(A2,FIND("-",A2)+1,99)
複製儲存格C2,貼至儲存格C2:C24。
(1) FIND("-",A2)
找尋儲存格A2的內容裡,「-」的位置。(本例為:3)
(2) MID(A2,2,FIND("-",A2)-2)
取出英文字之後,「-」之前的數字。(本例為:1)
(3) LEFT(A2,1)
取出英文字。(本例為:A)
(4) MID(A2,FIND("-",A2)+1,99)
取出「-」之後的數字。(本例為:12)

2018年6月16日 星期六

使用Google雲端硬碟檔案串流工具建立虛磁碟在檔案總管中操作檔案

Google 提供的雲端硬碟檔案串流工具,可以讓使用者在電腦上透過檔案總管來檢視和搜尋 Google 雲端硬碟檔案,而不必再透過瀏覽器連線雲端硬碟來操作。除了操作的便利性之外,並且可以離線儲存特定檔案與資料夾。如果你使用 Backup and Sync from Google 無法備份與同步處理小組硬碟,而且只能以資料夾為單位來備份至電腦。Google 的雲端硬碟串流工具則可以處理小組硬碟的檔案,而且可以處理單一檔案。
不過 Google 提供的雲端硬碟檔案串流工具目前只適用於 G Suite 帳號。
點選 Windwos 版或 Mac 版:
使用Google雲端硬碟檔案串流工具建立虛磁碟在檔案總管中操作檔案
選擇並下載你要的版本後,執行安裝,完成後在 Windwos 的工作列會多一個雲端硬碟串流的圖示。
使用Google雲端硬碟檔案串流工具建立虛磁碟在檔案總管中操作檔案
在檔案總管中,可以看到多了一個虛擬磁碟(名稱預設:Google Drive File Stream),內容包含了雲端硬碟和小組硬碟的內容。
使用Google雲端硬碟檔案串流工具建立虛磁碟在檔案總管中操作檔案
檢視各個資料夾的內容,會和雲端碟中的檢視結果一致。檔案總管內容:
使用Google雲端硬碟檔案串流工具建立虛磁碟在檔案總管中操作檔案
雲端硬碟內容:
使用Google雲端硬碟檔案串流工具建立虛磁碟在檔案總管中操作檔案
你可以將資料夾或檔案由 Google 雲端硬碟開啟,也可以在此設定共用選項。並可以切「僅限線上存取」或是「可離線使用」。
特別注意:例如,假設你點選了 Word 檔文件,會以 Word 程式來開啟這個檔案;但是,如果你點選了 Google 文件(在檔案總管中以 gdoc 為檔案格式),將會開啟瀏覽器於線上編修。
使用Google雲端硬碟檔案串流工具建立虛磁碟在檔案總管中操作檔案
可以方便的使用檔案總管提供的各種工具:
使用Google雲端硬碟檔案串流工具建立虛磁碟在檔案總管中操作檔案
當你在此新增/移除/編輯的任何資料夾或檔案,都會即時同步到雲端硬碟中。你的所有動作都是在虛擬磁碟上動作的,一旦你登出了 Google 雲端硬碟串流,原來的虛擬磁碟會被移除,雲端硬碟上的資料夾和檔案不會再出現於檔案總管上。或是切換了不同的帳號,虛擬硬碟內容也會隨帳號的不同而改變。(也包含已設定為離線存取的檔案)
當你有了這個虛擬磁碟,你也可以使用原來就在操作的同步/備份軟體,其結果可以和使用 Backup and Sync from Google 有相同的結果。
使用Google雲端硬碟檔案串流工具建立虛磁碟在檔案總管中操作檔案

Excel-不同時段顯示不同格式(MOD,SUMPRODUCT,FREQUENCY)

在 Excel 中如何讓一個時間清單,能依不同時段自動以不同格式區格?
以下圖為例,若以每個小時為分界,可以使用分隔線或是不同色彩來顯示區隔。
Excel-不同時段顯示不同格式(MOD,SUMPRODUCT,FREQUENCY)

【公式設計與解析】
利用設定格式化的條件可以來達到結果。
1. 使用不同色彩的分隔線
首先,選取儲存格A3:B27。設定其格式化的條件:
規則類型:使用公式來決定要格式化哪些儲存格。
編輯規則:=HOUR($A3)<>HOUR($A2)
設定格式:上框線為紅色
Excel-不同時段顯示不同格式(MOD,SUMPRODUCT,FREQUENCY)

2. 設定不同色彩來區隔
首先,選取儲存格A2:B27。設定其格式化的條件:
規則類型:使用公式來決定要格式化哪些儲存格。
編輯規則:=MOD(SUMPRODUCT((FREQUENCY(HOUR($A$2:$A2),
HOUR($A$2:$A2))>0)*1),2)=0
設定格式:儲存格色彩為淺藍色
Excel-不同時段顯示不同格式(MOD,SUMPRODUCT,FREQUENCY)
其中:
FREQUENCY(HOUR($A$2:$A2))
用以找出由資料第一列往下的每個時數出現的次數。
觀察下圖,對應公式的數值:
儲存格C2:
=SUMPRODUCT((FREQUENCY(HOUR($A$2:$A2),HOUR($A$2:$A2))>0)*1)
儲存格D2:
=MOD(SUMPRODUCT((FREQUENCY(HOUR($A$2:$A2),HOUR($A$2:$A2))>0)*
1),2)
儲存格E2:
=MOD(SUMPRODUCT((FREQUENCY(HOUR($A$2:$A2),HOUR($A$2:$A2))>0)*
1),2)=0
Excel-不同時段顯示不同格式(MOD,SUMPRODUCT,FREQUENCY)

利用Google查詢中學生網站中的小論文

中學生網站(http://www.shs.edu.tw/)是我國高中職學生參加小論文比賽和閱讀心得寫作比賽投稿的平台,透過此平台參賽。這個平台至目前為止已累積超過20萬篇小論文,是個很重要的資料庫。
該網站有提供查詢得獎作品的功能,參考下圖,查詢得獎作品時,被限定要指定作品性質、參賽梯次、分區等條件,再配合關鍵字才能查詢。
利用Google查詢中學生網站中的小論文
這樣的設計看來是為了給各個學校的行政、教師和學生方便查詢自已或是自己學校的資訊為主。而且,限定只能查詢得獎的作品。但是,學生有時是為了查詢他人的作品,以觀摩別人的撰寫方式,及有那些題材已被研究或撰寫過。該如何能有效的來搜尋作品?
可以藉助 Google!利用 Google 搜尋來強化這個網站的搜尋功能。
例如,要找尋「人工智慧」相關的投稿作品,可以輸入:
人工智慧 site:http://www.shs.edu.tw
其中「site:http://www.shs.edu.tw」指限定在中學生網站內搜尋。
結果如下:(查到的作品不限是得獎作品,也不限是小論文或是閱讀心得,而且關鍵字也不一定是出現在標題名稱,也可能是出現在內文。)
利用Google查詢中學生網站中的小論文
如果輸入的關鍵字,加上「得獎作品」
例如:AR 得獎作品 site:http://www.shs.edu.tw
則會搜尋到有得獎作品的小論文或閱讀心得文章。
利用Google查詢中學生網站中的小論文
如果你只想要查詢小論文投稿作品,則使用「filetype:pdf」參數。(因為全國小論文比賽限定要上傳PDF檔)
例如:AR site:http://www.shs.edu.tw filetype:pdf
利用Google查詢中學生網站中的小論文
如果你想查詢某個老師指導的作品,因為小論文的格式規定要有指導老師姓名,所以可以用老師姓名當關鍵。(同理,可以搜尋某個學校的作品等。)
例如:簡○桐 site:http://www.shs.edu.tw filetype:pdf
利用Google查詢中學生網站中的小論文
當然,Google還提供了指定日期範圍的查詢,像是:查詢去年的投稿作品。而且可以指定依日期排序。
利用Google查詢中學生網站中的小論文
藉由Google協助,可以更快速、有效的找到別人的作品,觀看他人作品,也是一種學習。

2018年6月15日 星期五

Google-使用Backup and Sync from Google進行電腦和雲端的差異性備份



現在大家對雲端硬碟的應用與日俱增,當你要將個人電腦上的檔案備份到Google雲端硬碟,或是要將Google雲端硬碟的檔案備份回電腦,如果依靠在瀏覽器以拖曳方式來上傳或下載檔案,將是一件辛苦的差事。建議你使用 Google 提供的 Backup and Sync from Google 程式,這是一個免費的差異性備份的檔案同步工具,並且支援多帳號的同步(同步至不同的雲端硬碟)。
Backup and Sync from Google 下載網頁:
Google-使用Backup and Sync from Google進行電腦和雲端的差異性備份
下載安裝後,必須登入帳號。
在設定頁面中,先設定電腦要備份到雲端的檔案,選取要備份資料夾,並決定上傳的影片和相片是要以原始畫質或高畫質上傳,是否要同步上傳至Google相簿。
Google-使用Backup and Sync from Google進行電腦和雲端的差異性備份
接著設定雲端硬碟的檔案同步至電腦,你可以勾選想要同步的資料夾。
Google-使用Backup and Sync from Google進行電腦和雲端的差異性備份
同步上去雲端的檔案會被放在「電腦」項下,你可能會用相同帳號在不同電腦中同步檔案,所以可以為這些不同電腦的同步檔案給予不同的名稱來辨識。(本例:home 和 sclool)
Google-使用Backup and Sync from Google進行電腦和雲端的差異性備份
而從雲端硬碟同步下來電腦的檔案,會放在檔案總管的「Google雲端硬碟」資料夾中:
Google-使用Backup and Sync from Google進行電腦和雲端的差異性備份
在電腦同步過程中,如果發現檔案或資料夾有綠色打勾圖示,表示該項目已同步完成。(正在同步也會有不同圖示)
Google-使用Backup and Sync from Google進行電腦和雲端的差異性備份
你還可以在設定中指定是否在開機時即啟動備份與同步。
Google-使用Backup and Sync from Google進行電腦和雲端的差異性備份

檢視其他文章

好康東東