2017年11月16日 星期四

Excel-將符合條件的多個內容集合在一個儲存格(MATCH,OFFSET,SUBSTIITUE,COUNTIF)

網友問到:如何在 Excel 的工作表中,將符合條件的多個儲存格內容集合在一個儲存格中?(如下圖)
例如:將項目『甲』的編號集合成『W355, O237, N493』,該如何處理?
Excel-將符合條件的多個內容集合在一個儲存格(MATCH,OFFSET,SUBSTIITUE,COUNTIF)

【公式設計與解析】
上圖中的項目和編號是相鄰的兩欄,且相同項目的編號是連續排列在一起。
選取儲存格A1:A25,按 Ctrl+Shfit+F3 鍵,定義名稱:項目。
儲存格E2:=MID(SUBSTITUTE(PHONETIC(OFFSET($A$1,MATCH(D2,項目,0),0,COUNTIF(項目,D2),2)),D2,", "),3,9999)
複製儲存格E2,貼至儲存格E2:E9。
(1) MATCH(D2,項目,0)
利用 MATCH 函數找出儲存格D2在項目儲存格陣列中的第幾個位置。
(2) COUNTIF(項目,D2)
利用 COUNTIF 函數找出儲存格D2在項目儲存格陣列中共有幾個。
(3) OFFSET($A$1,第(1)式,0,第(2)式,2)
將第(1)式和第(2)式代入 OFFSET 函數找出符合條件的儲存格範圍。
Excel-將符合條件的多個內容集合在一個儲存格(MATCH,OFFSET,SUBSTIITUE,COUNTIF)
(4) PHONETIC(第(3)式)
將第(3)傳回的儲存格範圍內的儲存格內容,利用 PHONETIC 函數串接在一起。
本例得到:甲W355甲O237甲N493
(5) SUBSTITUTE(第(4)式,D2,", ")
利用 SUBSTITUTE 函數將上式的結果中的儲存格D2,全部置換『, 』(逗號+空格)
本例得到:, W355, O237, N493
(6) MID(第(5)式,3,9999)
利用 MID 函數將上式中的第一個置換『, 』(逗號+空格)
本例得到:W355, O237, N493

2017年11月15日 星期三

Word-多層次清單設定

最近在上研習的教學時,老師和學生對於 Word 文件的多層次清單,在操作上發生些問題。在 Word 中使用多層次清單可以快速完成一個完整結構的清單設定,透過設定每個段落的「大綱階層」,可以設定其結構。
(文件的多層次清單設定可以參考:Word-建立長文件之多層次清單
Word-多層次清單設定
Word-多層次清單設定
透過樣式設定,可以快速設定每個結構上各層次的大綱:
Word-多層次清單設定
將每個標題都設定了樣式:
Word-多層次清單設定
當你為文件新增了一個多層次清單:
Word-多層次清單設定
設定完成之後即可選用:
Word-多層次清單設定
觀察以下的例子,如果現在你分2次選取多個標題分別設定多層次清單,則會發生編號不連續的現象。(同一份文件,有可能會需要多組同一個多層次清單設定。)
Word-多層次清單設定
這是一次選取設定的結果,所有編號是連續的:
Word-多層次清單設定
逐一設定也沒問題:
Word-多層次清單設定
你可以多做一個動作:將設定好的多層次清單,再更新樣式。
Word-多層次清單設定
如此,當設定段落樣式時,會自動產生多層清單的編號,並且號碼都是連續的。
可是,如果要使用多組的多層次清單,就無法套用相同的樣式了。
Word-多層次清單設定

2017年11月12日 星期日

在Google表單中使用公式運算式

教師們常用 Google 表單來給學生填問卷和寫作業,有時也用來考試,實在相當的方便和實用。
但是,如何在 Google 表單中使用數學的公式?(參考下圖)
在Google表單中使用公式運算式
因為 Google 表單的問題中只能輸入文字,而無法輸入公式。但是,可以插入圖片。所以,只要將公式儲存成圖片,即可在 Google 表單中使用了!做法如下:
1. 開啟 PowerPoint。(為何不選 Word?因為其無法將公式儲存為圖片檔!)
2. 在空白投影片中插入公式。
在Google表單中使用公式運算式
3. 複製公式,然後選取貼為圖片。
在Google表單中使用公式運算式
4. 在圖片化的公式上按一下右鍵,再選取「另存成圖片」。
在Google表單中使用公式運算式
5. 接者,來到 Google 表單中。
6. 在如下的 Google 表單中的選項中,選取新增圖片。
在Google表單中使用公式運算式
7. 上傳先前已儲存的公式圖片檔。
在Google表單中使用公式運算式
8. 完整的輸入四個選項的公式圖片。
在Google表單中使用公式運算式
9. 學生看到的題目如下。
在Google表單中使用公式運算式

2017年11月11日 星期六

將多個座標加入Google地圖中(轉換報紙訊息至我的地圖)

今天看到 UDN 新聞的一篇文章:台鐵絕美祕境 攝影師蹲點經緯度大公開,有位鐵道攝影師佛心來的,提供了多個拍攝地點的經緯度分享給讀者。(如下圖)
將多個座標加入Google地圖中(轉換報紙訊息至我的地圖中)
要如何來擷取這類的資訊呢?又如何將這些資訊轉換成地圖上可以用的資訊呢?
將多個座標加入Google地圖中(轉換報紙訊息至我的地圖中)
我個人的做法如下:
1. 在 Chrome 瀏覽器中使用 Evernote 擴充功能,按下儲存加以收藏。
將多個座標加入Google地圖中(轉換報紙訊息至我的地圖中)
2. 全選資料至記事本中,留下地點和座標,並且以 Tab 鍵加以隔開。
將多個座標加入Google地圖中(轉換報紙訊息至我的地圖中)
3. 將記事本的資料放在 Excel 中來處理。
將多個座標加入Google地圖中(轉換報紙訊息至我的地圖中)
因為報紙給的座標無法直接提供給 Google 地點使用,所要來做些調整。
主要是要消除「"N、"E」,並將「°」換成「.」,透過以下公式來處理:
儲存格C2:=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"°","."),
"N",""),"E",""),CHAR(34),"")
複製儲存格C2,貼至儲存格C2:C15。
公式中 CHAR(34) 是用來置換「"」字元為空白。
結果如下,存檔待用:
將多個座標加入Google地圖中(轉換報紙訊息至我的地圖中)
4. 開啟 Google 地圖後,新增一個地圖。
按一下「匯入」,將先前儲存的 Excel 檔開啟。
將多個座標加入Google地圖中(轉換報紙訊息至我的地圖中)
Google 會問你當為座標的欄位(本例:座標),並且選取是「經度/緯度」或是「緯度/經度」。(本例選取:緯度/經度)
將多個座標加入Google地圖中(轉換報紙訊息至我的地圖中)
接著選取要當為地標的標題。(本例為:地點)
將多個座標加入Google地圖中(轉換報紙訊息至我的地圖中)
匯入完成後,便完成了「火車攝影蹲點秘境」地圖了!
將多個座標加入Google地圖中(轉換報紙訊息至我的地圖中)
點選地點即可使用:
將多個座標加入Google地圖中(轉換報紙訊息至我的地圖中)

2017年11月9日 星期四

Excel+Word-產生多個QR Code圖片於合併列印中使用

如下圖,網友問到想要將一些在 Excel 工作表中的財產資料,產生 QR Code 圖片後,再經由 Word 合併列印成標籤,該如何處理呢?
Excel+Word-產生多個QR Code圖片於合併列印中使用
我也不知道是否有特定軟體可以直接完成,以下是一些免費的做法。
參考以下步驟:
先將所有資料串接成一個資料,其中刻意用「*」將不同欄位的資料隔開。
儲存格E2:=A2&"*"&B2&"*"&C2
Excel+Word-產生多個QR Code圖片於合併列印中使用
將 Excel 中的資料複製到 Google 雲端硬碟的試算表中,或是利用上傳匯入的方式產生 Google 試算表。(參考下圖)
然後,在B欄中輸入以下公式以產生 QR Code 圖片:
儲存格B2:=image("https://chart.googleapis.com/chart?chs=200x200&cht=qr&chl=" &A2)
將公式複製到B欄的其他儲存格,並且調整每一列的列高。
Excel+Word-產生多個QR Code圖片於合併列印中使用
接著,由 Google 試算表中匯出為一個 PDF 檔。(選取「檔案/下載格式/PDF文件」)(如下圖)
Excel+Word-產生多個QR Code圖片於合併列印中使用
再來,開啟 Word,在 Word 中以開啟舊檔方式來開啟這個 PDF 檔。如此,在 Word 文件中即可取得所有的 QR Code 圖片。如此,便能整理成合併列印要用的資料檔了。
Excel+Word-產生多個QR Code圖片於合併列印中使用
如果你想將資料三成三個欄位,則依以下步驟操作:
將表格欄位中的『*』全部置換為『^t』(^t 為定位字元)。
Excel+Word-產生多個QR Code圖片於合併列印中使用
然後,在第 1 欄之右邊,新增 2 欄。
接著,複製第 1 欄的內容,然後選取 1 ~ 3 欄,再於選取區中按右鍵,選取「只保留文字」項目。
Excel+Word-產生多個QR Code圖片於合併列印中使用
結果如下,一欄資料已分配至三欄中了。(已可當為合併列印的資料檔了!)
Excel+Word-產生多個QR Code圖片於合併列印中使用

2017年11月8日 星期三

使用PowerPoint環境來繪圖

最近對校內教職員的研習課程中有提到 PowerPoint 的使用,有時不是用來做簡報投影片,而是拿它來繪圖(參考下圖)!因為其每張投影片都是繪圖介面,用來繪製各種圖案,既輕鬆又簡單!
所以,我鼓勵大家用隨手可得的工具,並用最簡單的方式來繪製複雜的圖案。
使用PowerPoint環境來繪圖
先來觀察 Google 設計的 Blockly Games 的 Maze:
使用PowerPoint環境來繪圖
其中,只用了「循序、判斷、重覆」三種 Block,就可以排列組合多個 Block,進而寫出所有的程式。
如果以繪製下圖為例,先觀察其中的物件,除了文字之外,也是只包含了三種物件:線條、矩形、圓形。是否也能用這三種物件,排列組合出大多數你想表達的圖案。
使用PowerPoint環境來繪圖
如何繪製這樣的複製圖案?其實只要善用複製功能,再輔以使用 Ctrl 鍵和 Shift 鍵。
首先,如何繪製正圓?
當你在使用「橢圓」物件時,若配合 Ctrl 鍵和 Shift 鍵,會有不同的結果:
拖曳:預設以第一個點往拖曳方向繪製一個橢圓。
按著 Ctrl 鍵+拖曳:以第一個點為中心點繪製一個橢圓。
按著 Shift 鍵+拖曳:繪製長寬相同的橢圓,即繪製一個正圓。
按著 Ctrl+Shift 鍵+拖曳:以第一個點為中心點繪製一個正圓。
使用PowerPoint環境來繪圖
接著,以複製方式將一個圓擴建多個圖。先選取上圖中的圖形圖案後:
按著 Ctrl 鍵+拖曳:可以複製一個該物件。
按著 Shift 鍵+拖曳:可以平行移動該物件。
按著 Ctrl+Shift 鍵+拖曳:可以在平移位置上複製該物件。(位置隨意置放)
使用PowerPoint環境來繪圖
接著,選取上圖中的多個圖形物件,並點選「排列/對齊/水平均分」。
接著,按著 Ctrl+Shift 鍵+拖曳這些圓形物件,複製成二份。
使用PowerPoint環境來繪圖
當你繪製好一個如下的圖案,選取所有的物件:
使用PowerPoint環境來繪圖
接著,按著 Ctrl+Shift 鍵+拖曳這些圓形物件,複製成五份。
使用PowerPoint環境來繪圖
再將文字做適當的修改:
使用PowerPoint環境來繪圖
真正繪製一個如上圖的圖案,其實所花的時間是很少的。
你還可以將多個圓形、矩形等物件組合成不同的形狀。
使用PowerPoint環境來繪圖
例如:選取「合併圖案/合併」,結果如下:
(合併圖案有:聯集、合併、分割、交集、減法等合併功能,選取時可預覽。)
使用PowerPoint環境來繪圖
例如:選取「合併圖案/減法」,結果如下:
使用PowerPoint環境來繪圖
我再利上二個上圖中的物件,再組合出如下的圖案。創意將可無限延伸,圖案也可以無限創造。
使用PowerPoint環境來繪圖
因此,你也可以三兩下就產生了以下的圖案:
使用PowerPoint環境來繪圖

檢視其他文章

好康東東