2016年11月29日 星期二

Excel-計算符合條件者的不重覆數量(SUMPRDUCT,COUNTIF)

網友問到一個 Excel 公式運算的問題:如下圖,如何求得在『配編』欄位中各個年月的配編數(幾種不一樣的類別)?
在下圖中,年月是10501者,有 1, 2, 3, 4 共四種配編類別,該如何求得?
Excel-計算符合條件者的不重覆數量(SUMPRDUCT,COUNTIF)

【公式設計與解析】
儲存格G2:
=SUMPRODUCT(($A$2:$A$26=F2)*(1/COUNTIF($C$2:$C$26,$C$2:$C$26)))
(1) $A$2:$A$26=F2
在 SUMPRODUCT 函數中設定條件判斷儲存格A2:A26中和儲存格F2相同者。
(2) COUNTIF($C$2:$C$26,$C$2:$C$26)
在 SUMPRODUCT 函數中,計算儲存格C2:C26中每個數的重覆個數。例如:『1, 1, 1, 2, 2, 3, 3, 4, 4』傳回『3, 3, 3, 2, 2, 2, 2, 2, 2』
(3) 1/COUNTIF($C$2:$C$26,$C$2:$C$26)
將第(2)的傳回值取倒數,例如『3, 3, 3, 2, 2, 2, 2, 2, 2』傳回『1/3, 1/3, 1/3, 1/2, 1/2, 1/2, 1/2, 1/2, 1/2』,當在 SUMPRODUCT 函數加總時會得到結果『4』。
可另外參考以下二篇的說明:

2016年11月27日 星期日

用Evernote編寫講義並轉換為網頁

最近和學校同仁們討論到我自己在準備教材呈現上的做法。如果要將做好的講義放在網頁上,可以達到環保效果(不要列印),並且隨時、隨地都可以閱讀,教師可以隨時開講。然而每個老師們都有自己慣用的方式及最有效的法寶,不過使用上大多還是和個人專長及手邊工具為主。
這篇文章是要描述自己平常給老師們上研習課的時候,個人的教材呈現方式。
我會先準備的工具:
●個人部落格(學不完.教不停.用不盡)(http://isvincent.pixnet.net/blog)
●Evernote 記事本
●Google 雲端硬碟
●Dropbox 雲端硬碟
●Microsoft 雲端硬碟
●其他
我使用 Evernote 的筆記來編寫研習講義,除了平常就有使用 Evernote 搜集資料、記錄事情的習慣之外,因為其提供了『輕鬆轉換為網頁』及『方便使用者搜集』的二大好處。
以本篇文章用 Evernote 網頁呈現:https://goo.gl/t4Fa1m,你可以試著和你現在看到的網頁比較一下。或是使用 QR Code 掃描後在行動裝置上觀看。
在 Evernote 中編寫的講義,會一直保留在我的記事本中,方便管理和日後再利用。而編輯講義時也很方便從平常準備好的記事中,擷取資料加以組織成講義內容。透過電腦上的 Evernote 應用程式或是在瀏覽器中連線編輯記事都是可以的。
用Evernote編寫講義並轉換為網頁
將其轉換為網頁,使用者若是也有使用 Evernote,可以儲存講義網頁為自己的 Evernote 記事:
用Evernote編寫講義並轉換為網頁
在 Evernote 記事轉換成的網頁,對於圖片在呈現上會有特別的處理:
用Evernote編寫講義並轉換為網頁
如何將一個記事轉換為網頁?只要在 Evernote 的記事上按右鍵,選取「共用/複製URL」,該 URL 為網址,可於瀏覽器中看到該網頁的內容:
用Evernote編寫講義並轉換為網頁
該 URL 是一個很長的網址,可以先用 Google短網址(https://goo.gl/) 做轉換:
用Evernote編寫講義並轉換為網頁
除了獲取短網址以方便他人輸入網址,也可以產生 QR Code 的圖片檔方便手機和平板的掃描:
用Evernote編寫講義並轉換為網頁
日後你也可以根據短網址的分析報表(Analytics Data),得知該網頁被使用的狀況:
用Evernote編寫講義並轉換為網頁
而講義的網頁內容主要的呈現方式是一些文字搭配超連結,超連結主要有以下幾種:
●連結至自己的部落格:學不完.教不停.用不盡 http://isvincent.pixnet.net/blog
●連結至自己編寫的 Google 文件
●連結至自己放在 Docs.com 的文件(https://docs.com)
●連結至他人編寫的內容(網站、部落格)
●連結影片(YouTube或其他)
●連結投影片(Prezi、Microsoft Sway)
若有需要讓使用者下載檔案時,會先將一些檔案先行壓縮成一個檔案或是整個資料夾放至雲端硬碟,再開放下載(下載網址會先利用 Google 短網址轉換):
●Google 雲端硬碟
●Dropbox 雲端硬碟
●Microsoft OneDrive 雲端硬碟
有需要檔案上傳時,會利用 Dropbox 雲端硬碟的『檔案請求』功能:
用Evernote編寫講義並轉換為網頁
用Evernote編寫講義並轉換為網頁
如果有問卷要填寫,則會利用 Google 表單:
用Evernote編寫講義並轉換為網頁
對一個老師而言,製做教材和講義,其實都是無時、無刻都在進行的,所以我取用自己每天都會使用的工具(Evernote)來搭配,操作上就會方便且簡單。我的講義不講求過度美工,如果你強度要美化的網頁,Evernote 其實是做不到的。
講義和教材不只要製作方便,更要能易於分享和能讓使用者取得,這些因素也都是我使用 Evernote 來製作研習教材的主要考量因素。

2016年11月20日 星期日

在Google雲端硬碟的試算表中建立圖表並且分享

有些人開始使用 Google 雲端硬碟的 Google 試算表來取代 Excel,或是你想要發佈一個試算表文件時,透過 Google 試算表也很方便。本文要來看看如何在 Google 試算表中製作一個圖表。
在 Google 試算表中,當你要建立一個圖表時,不妨可以由 Google 提供的「探索」來開始。以下圖在 Excel 中的一個資料表為例:
在Google雲端硬碟的試算表中建立圖表並且分享
將這個檔案上傳至 Google 雲端硬碟並開啟(如下圖),按一下視窗右下角的「探索」。
在Google雲端硬碟的試算表中建立圖表並且分享
在[探索]窗格中,由「格式設定」區中點選一種格式設定,資料表隨即會套用這個格式:
在Google雲端硬碟的試算表中建立圖表並且分享
按一下[格式設定]區中的「編輯」,即可自訂要套用至那些儲存格範圍及樣式的設定:
在Google雲端硬碟的試算表中建立圖表並且分享
設定成功後,按一下「完成」,隨即套用這個自訂的格式:
在Google雲端硬碟的試算表中建立圖表並且分享
挑一個想要的圖表格式,按一下「插入圖表」:
在Google雲端硬碟的試算表中建立圖表並且分享
工作表中會插入你選取的圖表,按一下「下拉式清單」圖示,有其他的處理工具:
在Google雲端硬碟的試算表中建立圖表並且分享
如果你想要自行插入一個圖表,則可以按一下工具列上的「插入圖表」圖示,則會開啟「圖表編輯器」,在此可以選取和設定圖表類型。在此也可以修改圖表的資料範圍。
在Google雲端硬碟的試算表中建立圖表並且分享
在[自訂圖表]標籤下,你可以自訂各種格式:
在Google雲端硬碟的試算表中建立圖表並且分享
按下[插入]即會將圖表放入工作表中,使用「進階編輯」,即可開啟「圖表編輯器」來設定:
在Google雲端硬碟的試算表中建立圖表並且分享
你可以選取圖表的「儲存圖片」功能,將這個圖表下載成一個圖片檔。
在Google雲端硬碟的試算表中建立圖表並且分享
你可以發佈這個文件和圖表:
在Google雲端硬碟的試算表中建立圖表並且分享
在Google雲端硬碟的試算表中建立圖表並且分享
發佈後如下範例:(試著移動滑鼠至圖表的曲線上)
你可以隨時停止這個圖表的分設:
在Google雲端硬碟的試算表中建立圖表並且分享
在 Google 試算表中提供的圖表工具或許和 Excel 比較顯的陽春,但是它是免費的工具,有些人也僅需簡單的呈現圖表,Google 試算表中的圖表工具也是一個不錯的選項。

2016年11月19日 星期六

Excel-清單資料轉換為表格資料(OFFSET,陣列公式)

有網友問到:在 Excel 中有一個資料清單(如下圖左),如何轉換為表格形式(如下圖右)?
在下圖左的資料清單是由類別和項目組成,在下圖右的表格中將相同的類別的項目集合在一起,該如何設計公式?
Excel-清單資料轉換為表格資料(OFFSET,陣列公式)

【公式設計與解析】
為了幫助公式理解,請先選取儲存格A1:B25,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:類別、項目。
儲存格E2:{=IFERROR(OFFSET($B$1,SMALL(IF(類別=$D2,ROW(類別),""),
COLUMN(A:A))-1,0),"")}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 自動加上「{}」。
(1) IF(類別=$D2,ROW(類別),"")
在陣列公式中,如果類別陣列的內容和儲存格D2相同者,傳回其列號,若不相同,則傳回空字串(『""』)(其目的是為了製造一個錯誤訊息)。
(2) SMALL(IF(類別=$D2,ROW(類別),""),COLUMN(A:A))
利用 SMALL 函數傳回第(1)式中的列號最小值(COLUMN(A:A)=1),當公式向右複製時,COLUMN(A:A)=1→COLUMN(B:B)=2→COLUMN(C:C)=3→...,可以分別取出第1, 2, 3, ... 的最小值。
(3) OFFSET($B$1,SMALL(IF(類別=$D2,ROW(類別),""),COLUMN(A:A))-1,0)
將第(2)式傳回的列號代入 OFFSET 函數可以找到對應的『項目』陣列內容。若是代入 OFFSET 函數的是空字串,則會傳回一個錯誤訊息。
(4) IFERROR(第(3)式,"")
利用 IFERROR 函數將錯誤訊息轉換為空白。

在下圖中,如果類別是不規則的排列(試和上圖比較),原公式仍可以得到想要的結果(項目內容的排序不同而已)。
Excel-清單資料轉換為表格資料(OFFSET,陣列公式)

2016年11月17日 星期四

Excel-如何避免在含有合併儲存格中搜尋資料引起的當機現象

在 Excel 中,如果你的工作表裡含有一些合併的儲存格,當你在這些儲存格中進行搜尋動作時,有時會遇到當機現象。你碰到過嗎?
如下圖,要在某些已合併的儲存格中搜尋含『Data』的儲存格,目前有四個儲存格含有『Data』。『Data』資料雖然分佈各欄中,但是列號分別有重疊。
Excel-如何避免在含有合併儲存格中搜尋資料引起的當機現象
當你開始搜尋動作時,只看到在名稱方塊中有不斷跳動的儲存格名稱,現在已經進入當機的狀態了。雖然你可以按 Esc 鍵,來終止尋找動作,但無法跳出當機狀態。
Excel-如何避免在含有合併儲存格中搜尋資料引起的當機現象
是否有方法來避免在含有合併儲存格中搜尋資料引起的當機現象?我的做法是在搜尋對話框中的進階選項,將『循列』改成『循欄』,即可解決這個問題。
Excel-如何避免在含有合併儲存格中搜尋資料引起的當機現象
可得正確的搜尋結果:
Excel-如何避免在含有合併儲存格中搜尋資料引起的當機現象
如果『Data』所在的列號沒有重疊(例如在同一欄或同不欄),則在搜尋時選取『循列』或『循欄』都不會出錯。
Excel-如何避免在含有合併儲存格中搜尋資料引起的當機現象

Excel-取出固定間隔列的數值予以加總(SUMPRODUCT,MOD,ROW)

有網友問到:在 Excel 的工作表中有一個數值清單,如何取出固定間隔列的數值予以加總?
參考下圖,如何取出間隔 1, 2, 3, 4, 5, 6, 7, 8, 9 列的數值來加總?
Excel-取出固定間隔列的數值予以加總(SUMPRODUCT,MOD,ROW)
【公式設計與解析】
儲存格E2:=$B$2+SUMPRODUCT((MOD(ROW($A$3:$A$25)-2,ROW(2:2))=0)*$B$3:$B$25)
複製儲存格E2,貼至儲存格E2:E10。
(1) ROW($A$3:$A$25)
在 SUMPRODUCT 函數中取得儲存格A3:A25的列號,傳回 3, 4, 5, ..., 25。
(2) MOD(ROW($A$3:$A$25)-2,ROW(2:2))
將第(1)式的傳回值減2後再除以2,傳回 1, 0, 1, 0, ..., 0, 1。
(3) MOD(ROW($A$3:$A$25)-2,ROW(2:2))=0
判斷第(2)式的傳回值是否為 0,傳回 TRUE/FALSE 陣列。
(4) (MOD(ROW($A$3:$A$25)-2,ROW(2:2))=0)*$B$3:$B$25
式子中的『*』運算子相當於執行 AND 邏輯運算,執行時 TRUE/FALSE 陣列會轉換為 1/0 陣列。

公式結果相當於:
儲存格E2:=B2+SUMPRODUCT((MOD(ROW(A3:A25)-2,2)=0)*B3:B25)
儲存格E3:=B2+SUMPRODUCT((MOD(ROW(A3:A25)-2,3)=0)*B3:B25)
儲存格E4:=B2+SUMPRODUCT((MOD(ROW(A3:A25)-2,4)=0)*B3:B25)
儲存格E5:=B2+SUMPRODUCT((MOD(ROW(A3:A25)-2,5)=0)*B3:B25)

2016年11月15日 星期二

Excel-找尋儲存內的文字(SUBSTITUTE,FIND,SEARCH)

網友問到:如下圖,有一個 Excel 工作表的資料清單,如何找出儲存格中含有『助理』二個字?
Excel-找尋儲存內的文字(SUBSTITUTE,FIND,SEARCH)

1. 使用 SUBSTITUTE 函數
儲存格B2:=IF(SUBSTITUTE(A2,"助理","")<>A2,"助理","")
複製儲存格B2,貼至儲存格B2:B15。
SUBSTITUTE(A2,"助理",""):將儲存格A2中的「助理」字串用空字串取代。
假如取代後的結果和原字串不相同,則表示原字串中含有「助理」二字。

2. 使用 FIND 函數
儲存格B2:=IFERROR(IF(FIND("助理",A2),"助理",""),"")
複製儲存格B2,貼至儲存格B2:B15。
FIND("助理",A2):在儲存格A2中尋找是有「助理」二字,如果有,傳回一個大於0的數;如果沒有,會傳回一個錯誤訊息。
利用 IFERROR 函數將錯誤訊息轉換為空白。

3. 使用 SEARCH 函數
儲存格B2:=IFERROR(IF(SEARCH("助理",A2),"助理",""),"")
複製儲存格B2,貼至儲存格B2:B15。
SEARCH("助理",A2):在儲存格A2中尋找是有「助理」二字,如果有,傳回一個大於0的數;如果沒有,會傳回一個錯誤訊息。
利用 IFERROR 函數將錯誤訊息轉換為空白。

2016年11月13日 星期日

Excel-由資料陣列中反推對應的列標題和欄標題(OFFSET,SUMPRODUCT)

網友問到:在 Excel 中有一個資料表(參考下圖),如何由數值內容反推欄/列的標題?
例如:在儲存格J2中指定一個數值,要找出其人員為:『戊』,月份為:『三月』。.
【公式設計與解析】
1. 使用 SUMPRODUCT 函數
Excel-由資料陣列中反推對應的列標題和欄標題(OFFSET,SUMPRODUCT)
找出列標題:
儲存格J2:=OFFSET(A1,SUMPRODUCT((J1=B2:G11)*ROW(B2:G11))-1,0)
(1) SUMPRODUCT((J1=B2:G11)*ROW(B2:G11))
利用條件:J1=B2:G11,將其乘以ROW(B2:G11),可以傳回符合條件的列號。
(2) OFFSET(A1,SUMPRODUCT((J1=B2:G11)*ROW(B2:G11))-1,0)
將第(1)傳回的列號代入 OFFSET 函數,即可找出在A欄中對應的標題名稱。
找出欄標題:
儲存格J3:=OFFSET(A1,0,SUMPRODUCT((J1=B2:G11)*COLUMN(B2:G11))-1)
(1) SUMPRODUCT((J1=B2:G11)*COLUMN(B2:G11))
利用條件:J1=B2:G11,將其乘以COLUMN(B2:G11),可以傳回符合條件的欄號。
(2) OFFSET(A1,0,SUMPRODUCT((J1=B2:G11)*COLUMN(B2:G11))-1)
將第(1)傳回的列號代入 OFFSET 函數,即可找出在1列中對應的標題名稱。

2. 使用 SUM 函數+陣列公式
Excel-由資料陣列中反推對應的列標題和欄標題(OFFSET,SUMPRODUCT)
如果你想使用陣列公式,可以試試以下的公式:
儲存格J2:{=OFFSET(A1,SUM((J1=B2:G11)*ROW(B2:G11))-1,0)}
儲存格J3:{=OFFSET(A1,0,SUM((J1=B2:G11)*COLUMN(B2:G11))-1)}
陣列公式輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動輸入「{}」。

2016年11月9日 星期三

Excel-資料重組(OFFSET,INT,MOD,ROW,COLUMN)

網友問到在 Excel 中如何重新排列資料,例如下圖由六欄轉換為一欄,其中會使用的函數有:OFFSET、INT、MOD、ROW、COLUMN等。
以下例舉五種資料重組的樣式來練習:
(1) 六欄轉換為一欄
Excel-資料重組(OFFSET,INT,MOD,ROW,COLUMN)
儲存格A6:=OFFSET($A$1,MOD(ROW(1:1)-1,2),INT((ROW(1:1)-1)/2))

(2) 欄列轉置
Excel-資料重組(OFFSET,INT,MOD,ROW,COLUMN)
儲存格A10:=OFFSET($A$1,COLUMN(A:A)-1,ROW(1:1)-1)

(3) 取出間隔列
Excel-資料重組(OFFSET,INT,MOD,ROW,COLUMN)
儲存格A10:=OFFSET($A$1,(ROW(1:1)-1)*2,COLUMN(A:A)-1)

(4) 三列轉一欄
Excel-資料重組(OFFSET,INT,MOD,ROW,COLUMN)
儲存格A5:=OFFSET($A$1,INT((ROW(1:1)-1)/6),MOD(ROW(1:1)-1,6))

(5) 一欄轉三列
Excel-資料重組(OFFSET,INT,MOD,ROW,COLUMN)
儲存格A1:=OFFSET($A$5,(ROW(1:1)-1)*6+COLUMN(A:A)-1,0)

2016年11月7日 星期一

Excel-查表時依條件顯示是否合於標準(VLOOKUP)

網友提問:在下圖中的 Excel 資料表,如何根據下圖右的『程度/標準』對照表,在下圖左中依據『耗時』欄位判斷是否合於標準?
如下圖,當耗時小於標準值時,以『V』標示。
Excel-查表時依條件顯示是否合於標準(VLOOKUP)

Google-利用各種色彩模式查詢色彩對應的數值

在電腦中無論使用 Word 來執行文書處理或是使用軟體來製作網頁,或是使用影像編修軟體來編輯照片,對於色彩的選取,你可能都看過以下的畫面:
Google-利用各種色彩模式查詢色彩對應的數值
Google-利用各種色彩模式查詢色彩對應的數值
Google-利用各種色彩模式查詢色彩對應的數值
Google-利用各種色彩模式查詢色彩對應的數值
Google-利用各種色彩模式查詢色彩對應的數值
其中對於色彩的資訊,不外乎是使用 RGB 模式(紅、綠、藍)或是 HSL模式(色調、飽和度、亮度)來表示色彩,除此之外,你也可能因應列印需求而用到 CMYK 模式(C-Cyan青色、M-Magenta洋紅色、Y-Yellow黃色、 K-Black黑色)。其中數值的表示,有的使用十進制,有的使用十六進制。
不管你熟不熟悉這些色彩之間的關係,或是如何轉換,有時你是憑直覺來選取色彩。但是如果問你某個色彩的數值時,或許會難倒你。亦或如果有人要你講出某個色彩時,你就會有到用到以上這些資訊了。
在 Google 的搜尋引擎中也提供了方便你查詢色彩的功能,你可以利用多種方式來查詢同一色彩。例如以下的例子:
HEX:#649AFF
RGB:RGB(100, 154, 255)
HSV:HSV(219º, 61%, 100%)
HSL:HSL(219º, 100%, 70%)
CMYK:CMYK(61%, 40%, 0%, 0%)
Google-利用各種色彩模式查詢色彩對應的數值
你可以藉由調整兩個圓點的位置,即可查詢各種色彩的數值。
Google-利用各種色彩模式查詢色彩對應的數值

檢視其他文章

好康東東