贊助廠商

///本部落格所有文章列表///

搜尋本部落格文章資料

目前日期文章:201607 (22)

瀏覽方式: 標題列表 簡短摘要

網友根據這篇文章:Excel-表格的轉換(OFFSET,SUMPRODUCT,ROW),問到想要如下圖中由B表格查詢A表格,該如何處理?

Excel-表格的轉換(OFFSET,SMALL,COLUMN,ROW)


【公式設計與解析析】

儲存格E3:{=IFERROR(OFFSET($A$1,SMALL(IF($B$3:$B$18=$D3,ROW
($B$3:$B$18),""),COLUMN(A:A))-1,0),"")}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。

複製儲存格E3,貼至儲存格E3:H12。


(1) IF($B$3:$B$18=$D3,ROW($B$3:$B$18),"")

在陣列公式中,判斷儲存格B3:B18範圍和儲存格D3是否相同。若是,則傳回儲存格的『列號』;若否,則傳回空字串("")。


(2) SMALL(第(1)式,COLUMN(A:A))

SMALL 函數中代入第(1)式,傳回第(1)式所得『列號』的最小值。當公式向右複製時,COLUMN(A:A)=1→COLUMN(B:B)=2→ ... →COLUMN(D:D)=4,可取得第 1, 2, 3 ,4 的最小值 。


(3) OFFSET($A$1,第(2)式-1,0)

OFFSET 函數中代入第(2)式傳回的列號,即可取得A欄中的儲存格內容。公式中的「-1」是因為儲存格的列是由1起始(1,2,3,...),而 OFFSET 函數中的第 2 個參數要代入第幾列,是由 0 開始。


(4) IFERROR(第(3)式,"")

因為在第(1)式中如果是傳回空字串時,會導致公式出現錯誤訊息。因此,使用 IFERROR 函數將錯誤訊息以空字串顯示(看到空白儲存格)。

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

網友想要在 Excel 中做資料處理,參考下圖中的數值欄位,要依規則取後2碼來調整「進位」,該如何處理?

規則:

(1) 若後2碼<=50,則調整為:50

(2) 若後2碼>50,則調整為:100

Excel-取數值後2碼調整進位(INT,MOD)


【公式設計與解析】

儲存格B2:=INT(A2/100)*100+((MOD(A2,100)>=50)+1)*50

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

(1) INT(A2/100)*100

利用 INT 函數,將儲存格A2內容的後2碼變為『00』。

(2) ((MOD(A2,100)>=50)+1)*50

MOD(A2,100):利用 MOD 函數,取出儲存格A2內容的後2碼。

((MOD(A2,100)>=50)+1)*50:若儲存格A2內容的後2碼小於 50,則本式傳回 50;若若儲存格A2內容的後2碼大於或等於 50,則本式傳回 100。

vincent 發表在 痞客邦 PIXNET 留言(1) 人氣()

網友問到:如何在 Excel 中將一個矩陣資料內容轉換為一欄?參考下圖,儲存格A1:D5中的資料要轉換至儲存格A7:A26。

矩陣資料內容轉換為一欄(OFFSET,MOD,INT,ROW)


【公式設計與解析】

儲存格A7:=OFFSET($A$1,MOD(ROW(1:1)-1,5),INT((ROW(1:1)-1)/5),1,1)

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

(1) MOD(ROW(1:1)-1,5)

本例中矩陣資料每一欄有 5 筆資料,公式中的參數『5』,即為每一欄有幾筆資料。

ROW(1:1)=1,當公式向下複製時,ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→...。公式結果會產生 { 0,1,2,3,4,0,1,2,3,4, ... }。

(2) INT((ROW(1:1)-1)/5)

本例中矩陣資料每一欄有 5 筆資料,公式中的參數『5』,即為每一欄有幾筆資料。

當公式向下複製時,公式結果會產生 { 0,0,0,0,0,1,1,1,1,1, ... }。

矩陣資料內容轉換為一欄(OFFSET,MOD,INT,ROW)

將第(1)式和第(2)式代入 OFFSET 函數中,即可取得對應的儲存格內容。。


【延伸練習】

如何將矩陣資料內容轉換為一列?

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

參考下圖,在 Excel 中,如果要將儲存格內容「877/13/3214/6481/643/4486」的文字,依其分隔符號『/』取出『877、13、3214、6481、643、4486』,該如何處理?注意其分隔符號之間的內容的文字長度並不一致。

通常,我們會使用 Excel 中的「資料部析」工具來處理,手動操作也很方便,但是如果想要以公式來處理,或像下圖中要把取出的資料放在同一欄中。(資料剖析工具只能將資料部析結果放在同一列中)

Excel-手動資料剖析(MID,ROW,COLUMN,陣列公式)


【公式設計與解析】

參考上圖,假設:本例的資料中有五個分隔符號『/』,將資料分成六組。


1. 資料剖析結果置於同一列

計算分隔符號『/』的位置,儲存格C7:

{=SMALL(IF(MID($C2,ROW($1:$30),1)="/",ROW($1:$30),999),COLUMN(A:A))}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加入「{}」。

複製儲存格C7,貼至儲存格C7:G7。

(1) MID($C2,ROW($1:$30),1)

假設儲存格中的文字長度不超過 30 個字,在陣列公式中 ROW(1:30) 代表{1,2,3, ... 29, ,30} 陣列。

MID($C2,ROW($1:$30),1) 可以取出儲存格C2中第 1, 2, 3, ..., 29, 30 個字。

(2) IF(MID($C2,ROW($1:$30),1)="/",ROW($1:$30),999)

MID($C2,ROW($1:$30),1)="/" 用以判斷第(1)式取出的每個字是否為分隔符號『/』。

如果判斷結果為真則傳回所有位置(1~30),否則就傳回『999』,該數只是一個很大的數,且比儲存格中的文字長度還要大。

(3) SMALL(IF(MID($C2,ROW($1:$30),1)="/",ROW($1:$30),999),COLUMN(A:A))

利用 SMALL 函數取出第 1, 2, 3, 4, 5 個分隔符號(/)的位置。其中 COLUMN(A:A)=1,當公式向右複製時,COLUMN(A:A)=1→COLUMN(B:B)=2→ ... →COLUMN(E:E)=5。

(4) 取出第1組文字

儲存格C11:=MID(C2,1,C7-1)

(5) 取出第2~5組文字

儲存格D11:=MID($C2,C7+1,D7-C7-1)

複製儲存格D11,貼至儲存格D11:G11。

(6) 取出第6組文字(最後一組)

儲存格H11:=MID($C2,G7+1,99)

其中參數『99』,只是一個很大的數字,只要比儲存格內文字總數大即可。


2. 資料剖析結果置於同一欄

計算分隔符號『/』的位置,儲存格D16:

{=SMALL(IF(MID($C$2,ROW($1:$30),1)="/",ROW($1:$30),999),ROW(1:1))}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加入「{}」。

複製儲存格D16,貼至儲存格D16:D20。

儲存格G16:=MID(C$2,1,D16-1)

儲存格G17:=MID(C$2,D16+1,D17-D16-1)

複製儲存格G17,貼至儲存格G17:G20。

儲存格G21:=MID(C$2,D20+1,99)

公式的原理和「1. 資料剖析結果置於同一列」完全相同,其中 ROW(1:1)=1,當公式向右複製時,ROW(1:1)=1→ROW(2:2)=2→ ... →COLUMN(5:5)=5。

vincent 發表在 痞客邦 PIXNET 留言(2) 人氣()

最近 Pokémon GO 正火紅,學生問到:在電腦中 Pokémon GO 中的『é』字元要如何輸入?在手機中又要如何輸入?

因為 Pokémon GO 中的『é』字元是法文字,所以在電腦中除非你已安裝法文輸入法,否則無法直接來輸入。不過 Google 大神佛心來的,早在 Google 翻譯中提供了法文輸入的虛擬鍵盤讓你可以輸入法文字。

參考以下做法:

1. 先進入 Google 翻譯:https://translate.google.com.tw/

2. 將被翻譯文字挑選為『法文』。

3. 按下虛擬鍵盤的圖示,顯示法文鍵盤。

4. 直接按鍵盤的『2』鍵即會顯示『é』字元,或是使用滑鼠點選『é』圖示,即可輸入。

5. 複製輸入文字方塊中的『Pokémon Go』,即可在其他位置貼上來使用。

Pokémon Go的é如何輸入?

如果在手機中,也是可以藉助 Google Translate 應用程式來輸入『é』字元。

1. 開啟 Google Translate App。

2. 先將被翻譯文字『英文』改成『法文』。

3. 點選麥克風圖示,唸出『Pokémon』。

Pokémon Go的é如何輸入? Pokémon Go的é如何輸入?

4. Google Translate 已經幫你輸入了『Pokémon』。

5. 選取『Pokémon』,則複製到其他位置使用。

Pokémon Go的é如何輸入? Pokémon Go的é如何輸入?


你可以在Google搜尋介面下輸入:Google Translate,在此也會出現翻譯的介面。

先將被翻譯文字『英文』改成『法文』。然後選取點選麥克風圖示,並且唸出『Pokémon』。在此,Google 也會幫你輸入『Pokémon』,隨後即可取得『Pokémon』文字。

Pokémon Go的é如何輸入?


【網友補充】

網友提到:如果你的手機中裝有 Goolge 輸入法,在虛擬鍵盤上長按 e 時,即可選取其他語系的文字了。

Pokémon GO的é如何輸入? Pokémon GO的é如何輸入?

vincent 發表在 痞客邦 PIXNET 留言(1) 人氣()

網友想要在 Excel 的工作表中將大量資料的前幾碼刪除,該如何處理?

在下圖中的『資料』欄位中,欄位中的每筆資料都是一樣長度的,但是也可能會遇到資料的長度不一樣的狀況,所以使用的公式要有一點點彈性。

Excel-刪去資料前幾碼(MID,RIGHT,LEN)


【公式設計與解析】

(1) 省略第1個0

儲存格B2:=MID(A2,2,99)

公式中的『2』,是要刪除N個字再加1,即N+1。

公式中的『99』,只要資料總長度不超過99個字都適用。

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


(2) 省略前3個0

儲存格C2:=MID(A2,4,99)

如(1)所述,如果要省略前3個0,使用參數『4』。

如果不使用參數『99』,你也可以使用以下的做法。

儲存格C2:=MID(A2,4,LEN(A2)-3)

儲存格C2:=RIGHT(A2,LEN(A2)-3)

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

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

暑假開始,新的班級又要重頭教起了!和新同學認識,都是要先 教們怎麼唸書、如何做筆記等老生常談。配合 Google Chrome 瀏覽器,除了以前常介紹的 Evernote、Clearly 之外,微軟的 OneNote 也有不錯的網頁剪輯工具:OneNote Web Clipper。

首先,你必須先有 Microsoft 的帳號。(在此可以申請或登入:https://outlook.com)

接著,要安裝:Google Chrome OneNote Web Clipper 擴充程式

在Google Chroem中使用OneNote Web Clipper剪輯網頁資料做筆記

下載後安裝此擴充程式,在 Chrome 瀏覽器中會顯示其圖示:

在Google Chroem中使用OneNote Web Clipper剪輯網頁資料做筆記

當你想要剪輯某個網頁時(以udn.com的一個新聞網頁為例),你發現該網頁和主題內容無關的雜訊很多,例如含有一些網頁提供的其他功能、超連結、廣告等,這些雜訊會干擾我們搜集資料,造成做筆記的困擾。

你只要按一下 Google Chrome 上的 OneNote Web Clipper 擴充程式按鈕。

在Google Chroem中使用OneNote Web Clipper剪輯網頁資料做筆記

可以有三種方式來剪輯網頁的資料:

1. 整頁

如果你選取「整頁」選項,則會剪輯到網頁中較多的內容。除了文章主題之外,其他的圖片、廣告、超連結等,也都會一併剪輯。

在Google Chroem中使用OneNote Web Clipper剪輯網頁資料做筆記

2. 區域

當你選取「區域」選項時,你可以先拖曳網頁上的一個區域,這是你要剪輯的資料之一。

在Google Chroem中使用OneNote Web Clipper剪輯網頁資料做筆記

在Google Chroem中使用OneNote Web Clipper剪輯網頁資料做筆記

當你選取好的一個區域,可以刪除這個區域。也可以按一下「新增另一個區域」,來新增其他資料區域。也就是說可以剪輯多個區域的資料到同一個筆記中。

在Google Chroem中使用OneNote Web Clipper剪輯網頁資料做筆記

3. 文章

如果你選取「文章」選項,則會只會剪輯到文章主體的內容,其他雜訊不會被包含進來。而且你可以在剪輯時,設定文字的字形和文字的大小,並且在網頁上畫重點標記,也可以輕易的關閉該標記。

在Google Chroem中使用OneNote Web Clipper剪輯網頁資料做筆記

最後,你只要新增一個筆記本,或是選取一個已有的筆記本,來存放這個筆記。

在Google Chroem中使用OneNote Web Clipper剪輯網頁資料做筆記

在Google Chroem中使用OneNote Web Clipper剪輯網頁資料做筆記

最後,按一下「剪輯」,即可大功告成。

當你切換至你的筆記本中查看,網頁已照你的意思剪輯成一個新的筆記了,並且放在你指定的筆記本中。

在Google Chroem中使用OneNote Web Clipper剪輯網頁資料做筆記

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

網友問到:如下圖的 Excel 資料清單中,如何能找出不同料號的最低價廠商?

下圖中的基本資料有:廠商名稱、料號和單價,而單價有可能不同廠商相同單價。在本例中如有相同單價的廠商,則僅列出第一個廠商。

Excel-在資料清單中找出不同料號的最低價廠商(OFFSET,MIN,陣列公式)


【公式設計與解析】

(1) 為了便於說明,先設定儲存格範圍名稱:

選取儲存格A1:C24,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:

廠商、料號、單價。


(2) 找出各料號的最低價

儲存格F2:{=MIN(IF(料號=E2,單價,FALSE))}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動輸入「{}」。

判斷在料號陣列中和儲存格E2相同者,使用 MIN 函數得到其中單價的最小值。


(3) 找出各料號的最低價在第幾列

儲存格G2:{=MAX((料號=E2)*(單價=F2)*ROW(廠商))}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動輸入「{}」。

(料號=E2)*(單價=F2):利用雙條件判斷「料號和儲存格E2相同『且』單價和儲存格F2相同者」,傳回 TRUE/FALSE 陣列。

將上式傳回的陣列再和ROW(廠商)相乘,會傳回符合的列號。但是,因為可能會傳回一個以上的列號,所以透過 MAX 函數只取其中一個。


(4) 找出各料號的最低價廠商

儲存格H2:=OFFSET($A$1,G2-1,0,1,1)

最後使用 OFFSET 函數在廠商儲存格陣列中根據第(3)式傳回的列號取得對應的內容。

複製儲存格H2,貼至儲存格H2:H6。


(5)整合以上公式

儲存格H2:

{=OFFSET($A$1,SUM((料號=E2)*(單價=MIN(IF(料號=E2,單價,FALSE)))*ROW
(廠商))-1,0,1,1)}

複製儲存格H2,貼至儲存格H2:H6。

vincent 發表在 痞客邦 PIXNET 留言(1) 人氣()

網友問到一個關於用 Excel 來根據打卡清單計算工時的問題,該如何處理?

參考下圖,其條件如下:

8:00~8:14→8:00;8:15~8:30→8:30;8:30~8:44→8:30;8:45~9:00→9:00

Excel-根據上下班打卡清單計算工時(MROUND)


【公式設計與解析】

儲存格D2:=MROUND(C2,30/(24*60))-MROUND(B2,30/(24*60))

複製儲存格D2,貼至儲存格D2:D17。

MROUND(C2,30/(24*60)):將上班打卡時間捨去/進位至0/30。

MROUND(B2,30/(24*60)):將下班打卡時間捨去/進位至0/30。

因為在 Excel 中,一天(24小時)是以『1』表示,所以1小時=1/24。


關於 MROUND 函數也可以參考另一篇文章:

http://isvincent.pixnet.net/blog/post/46394263

vincent 發表在 痞客邦 PIXNET 留言(1) 人氣()

網友很常問的問題:在 Excel 中如何轉換日期格式,通常會有國曆和西曆的轉換,或是和格式相關的轉換等。

例如:下圖中要將一個國曆的年月日格式轉換為西曆的年/月/日格式。其中國曆年為三碼,西月年為四碼,月和日都是二碼。

Excel-日期格式轉換(MID,DATE,TEXT)


【公式設計與解析】

儲存格C2:=DATE(MID(A2,1,3)+1911,MID(A2,4,2),MID(A2,6,2))

MID(A2,1,3)+1911:利用 MID 函數取出儲存格A2的1~3碼,得到國曆年,再加上1911,轉換為西元年。

MID(A2,4,2):利用 MID 函數取出儲存格A2的4~5碼,得到月數。

MID(A2,6,2):利用 MID 函數取出儲存格A2的6~7碼,得到日數。

將西元年、月、日代入 DATE 函數即可轉換為西元日期格式。

再設定儲存格的數值格式,自訂格式為:yyyy/mm/dd,即可將月和日顯示為二碼。

Excel-日期格式轉換(MID,DATE,TEXT)


如果你不想設定儲存格的數值格式,則可以考慮使用 TEXT 函數:

儲存格C2:

=TEXT(DATE(MID(A2,1,3)+1911,MID(A2,4,2),MID(A2,6,2)),"yyyy/mm/dd")

vincent 發表在 痞客邦 PIXNET 留言(1) 人氣()

在 Excel 中的儲存格內容常會是數字和非數字混合,如何能找出某個儲存格中第一個非數字的位置呢?(參考下圖)

Excel-找出儲存格中第一個非數字的位置(ISNUMBER,MATCH,陣列公式)


【公式設計與解析】

先假設儲存格內容不會超過 20 個字元組成。

儲存格B2:{=MATCH(FALSE,ISNUMBER(VALUE(MID(A2,ROW($1:$20),1))),0)}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動產生「{}」。

(1) MID(A2,ROW($1:$20),1))

在陣列公式中,利用 ROW(1:20) 來獲得 {1,2,3,4,5,..,20},再使用 MID 函數取出儲存格中的每一個字元。

(2) VALUE(MID(A2,ROW($1:$20),1))

將第(1)式傳回的每一個字元利用 VALUE 函數轉換為數字,但若是字元非數字,則會產生錯誤訊息。本例傳回:{1,2,3,#VALUE!,#VALUE!,#VALUE!, … ,#VALUE!}。

(3) ISNUMBER(VALUE(MID(A2,ROW($1:$20),1)))

利用 ISNUMBER 函數判斷第(2)式的結果是否為數字,本例傳回:{TRUE,TRUE,TRUE,FALSE,FALSE,FALSE, … ,FALSE}。

最後利用 MATCH 函數找出第一個 FALSE 的位置,即為所求,本例傳回「4」。

你也可以使用以下的公式得到相同結果:

儲存格B2:{=MATCH(TRUE,ISERROR(VALUE(MID(A2,ROW($1:$20),1))),0)}


而如果你的儲存格內容,其字元長度不確定是否會低於20個字,則改用以下公式。

儲存格B2:{=MATCH(TRUE,ISERROR(VALUE(MID(A2,ROW(INDIRECT("1:"&
LEN(A2))),1))),0)}

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

ROW(INDIRECT("1:"&LEN(A2))):利用 LEN(A2) 取得儲存格的字元數,再透過 INDIRECT 函數將「"1:"&LEN(A2)」的結果轉換為實際位址。

本例傳回:ROW(INDIRECT("1:6"))→ROW(1:6),指第1列至第6列。

vincent 發表在 痞客邦 PIXNET 留言(1) 人氣()

有老師問到:如果成績中有些數字欄位呈現了文字或是錯誤訊息時,可否不要列入排序,公式可如何處理?

參考下圖,一個成績表中含有『轉學、休學、缺考』等文字,還有錯誤訊息,當在排序時要排除這三個,不要顯示排序結果。如果你使用 RANK 函數,會得到G欄的結果。

Excel-排序時跳過文字和錯誤訊息(ISNUMBER,RANK,IFERROR)


【公式設計與解析】

儲存格I2:=IFERROR(RANK(F2,IF(ISNUMBER($F$2:$F$26),F$2:F$26,FALSE)),"")

複製儲存格I2,貼至儲存格I2:I26。

(1) IF(ISNUMBER($F$2:$F$26),F$2:F$26,FALSE)

利用 ISNUMBER 函數判斷儲存格F2:F26中的內容是否為數值,如果是,傳回儲存格內容,如果不是,則傳回 FALSE 邏輯值。

(2) RANK(F2,IF(ISNUMBER($F$2:$F$26),F$2:F$26,FALSE))

利用第一式傳回是數值的儲存格來利用 RANK 函數進行排序。

最後,利用 IFERROR 函數將第(2)式若傳回錯誤訊息時,顯示為空字串。

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

在 Excel 的工作表中,有時會用到要在資料清單中反推數值所在的儲存格。如下圖,要如何找出儲存格L1的內容對應儲存格A1:J10中相符者的儲存格位址?

Excel-在資料清單中反推數值所在的儲存格(ADDRESS,ROW,COLUMN,陣列公式)


【公式設計與解析】

假設儲存格A1:J10的內容具唯一性,不會重覆。

儲存格L4:{=ADDRESS(SUM((A1:J10=L1)*ROW(1:10)),SUM((A1:J10=L1)*
COLUMN(A:J)),1)}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 自動輸入「{}」。


(1) SUM((A1:J10=L1)*ROW(1:10))

在陣列公式中判斷儲存格A1:J10中和儲存格L1內容相同者,傳回 TRUE/FALSE 陣列。

其中 100 個傳回值中只有一個 TRUE,有 99 個 FALSE

在陣列公式中 ROW(1:10)={1,2,3,4,5,6,7,8,9,10}。

以上兩式相乘時,TRUE/FALSE 會變為 1/0,其結果會有一個不是0,而有99個0。

最後,再以 SUM 加總這 100 個數字,結果即為符合者的『列號』。


(2) SUM((A1:J10=L1)*COLUMN(A:J))

在陣列公式中判斷儲存格A1:J10中和儲存格L1內容相同者,傳回 TRUE/FALSE 陣列。

其中 100 個傳回值中只有一個 TRUE,有 99 個 FALSE

在陣列公式中 COLUMN(A:J)={1,2,3,4,5,6,7,8,9,10}。

以上兩式相乘時,TRUE/FALSE 會變為 1/0,其結果會有一個不是0,而有99個0。

最後,再以 SUM 加總這 100 個數字,結果即為符合者的『欄號』。


將第(1)式和第(2)式代入 ADDRESS 函數即可顯示儲存格位址。

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

如果在 Excel 的工作表中有一個資料清單,其中每一個儲存格是由數值和單位所組成,如何能將數值和單位分離出來?

參考下圖,資料清單中的數值和單位的字元數都不相同,如何分離其中的數值和單位?

Excel-分離數值和單位(MID,SUBSTITUTE,陣列公式)


【公試設計與解析】

(1) 取出數值

假設:資料棈單中數值部分的位元數不超過 9(包含小數點)。

儲存格C2:{=MAX(IFERROR(1*LEFT(A2,ROW($1:$9)),""))}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動產生「{}」。

ROW($1:$9):在陣列公式中可傳回 1, 2, 3, ... , 8, 9。

LEFT(A2,ROW($1:$9)):在陣列公式中由左邊取出儲存格A2內容 1, 2, 3, ..., 8, 9 個字。

1*LEFT(A2,ROW($1:$9)):由於 LEFT 函數傳回的是字串,透過「1*」,將其轉換為數值。若傳回的不是數字組成的字串,則會傳回「錯誤訊息」。

IFERROR(1*LEFT(A2,ROW($1:$9)),""):利用 IFFERROR 函數,將上式中的錯誤訊息轉換為空字串。

最後,利用 MAX 函數將 1 ~ 9 個數值取其最大值。

(2) 取出單位

儲存格D2:=SUBSTITUTE(A2,C2,"")

利用 SUBSTITUTE 函數將儲存格A2中的內容,去除儲存格C2的內容(數值),得到的結果即為「單位」。

最後,複製儲存格C2:D2,貼至儲存格C2:D19。

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

根據前一篇文章:Excel-計算一年中13號星期五的數量(WEEKDAY,ROW,陣列公式)

如果要進一步找出每年第一個13號星期出現的月份,該如何處理?

Excel-找出一年中第一個13號星期出現的月份(WEEKDAY,SMALL,陣列公式)


【公式設計與解析】

儲存格C2:

{=SMALL(IF(WEEKDAY(DATE(A2,ROW($1:$12),13),2)=5,ROW($1:$12),99),1)}

公式輸入完成,要按 Ctrl+Shift+Enter 鍵,Excel 會自動產生「{}」。

第(1)式 IF(WEEKDAY(DATE(A2,ROW($1:$12),13),2)=5,ROW($1:$12),99)

若各月的13號是星期五,則傳回對應的月份,否則就傳回 99(隨意設定比12大的數)。

再利用 SMALL 函數找出這些月份的最小值,即為所求。

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

在 Excel 的工作表中,如果你想要找出一年中有幾個13號星期五,該如何處理?以本例來練習『陣列公式』。

Excel-計算一年中13號星期五的數量(WEEKDAY,ROW,陣列公式)


【公式設計與解析】

儲存格B2:{=SUM(1*(WEEKDAY(DATE(A2,ROW($1:$12),13),2)=5))}

公式輸入完成,要按 Ctrl+Shift+Enter 鍵,Excel 會自動產生「{}」。

ROW($1:$12):在陣列公式中產生:1, 2, 3, ..., 11, 12。

DATE(A2,ROW($1:$12),13):在陣列公式中產生各年度的 1/13 ~ 12/13。

WEEKDAY(DATE(A2,ROW($1:$12),13),2)=5:判斷各年度的 1/13 ~ 12/13 是否為為星期五,傳回 TRUE/FALSE 陣列。

Excel-計算一年中13號星期五的數量(WEEKDAY,ROW,陣列公式)

1*(WEEKDAY(DATE(A2,ROW($1:$12),13),2)=5):將上式傳回的 TRUE/FALSE 陣列,轉換為 1/0 陣列。

最後透過 SUM 函數將上式加總,即為所求。


【補充說明】

利用『評估值公式』,可以觀察到陣列公式:

(1) 產生各年度每月的13日

Excel-計算一年中13號星期五的數量(WEEKDAY,ROW,陣列公式)

(2)找出各年度每月的13日是否為星期五

Excel-計算一年中13號星期五的數量(WEEKDAY,ROW,陣列公式)

(3) 將0和1加總即為所求

Excel-計算一年中13號星期五的數量(WEEKDAY,ROW,陣列公式)

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

網友問到:在 Excel 的工作表中如何限制儲存格只能輸入某一時間區間的數值?

參考下圖,想要限制儲存格只能輸入 10:00 至 14:00 之間的數值,該如何處理?

Excel-限制儲存格只能輸入指定時間區間的數值


【公式設計與解析】

1. 選取儲存格A2:A21。

2. 選取[資料/資料工具]功能表中的「資料驗證」。

3. 在[資料驗證]對話框的「設定」標籤下,設定資料驗證準則:

儲存格內允許:自訂。

公式:=(A14>=10*1/24)*(A14<=14*1/24)

因為在 Excel 中是以 1 為一天的時間量,因此每一小時為 1/24,所以 10:00 為 10*1/24,而 14:00 為 14*1/24。

公式中兩個條件之間的「*」,相當於執行邏輯 AND 運算。

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

有網友問到:在 Excel 中有一個項目清單(參考下圖左),若要依照項目的數量列出(如下圖右),該如何處理?

Excel-將項目依清單數量列出(OFFSET,SUMIF,INDEX)


【公式設計與解析】

我參考了網路其他網友提供的公式,覺得是解題的好方式。

在本例中,項目個數有「5個」,要分別依其個數列出項目。

儲存格D2:{=IFERROR(INDEX($A$2:$A$6,SUM(1*(ROW(1:1)>SUMIF(OFFSET
($B$2,0,0,ROW($1:$5),1),">0")))+1),"")}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動輸入「{}」

(1) OFFSET($B$2,0,0,ROW($1:$5),1)

因為項目個數有5個,使用 ROW($1:$5),若項目個數有6個,則使用ROW($1:$6)。此 OFFSET 函數在陣列公式中分別取得儲存格範圍:B2:B2、B2:B3、B2:B4、B2:B5、B2:B6)。

(2) SUMIF(OFFSET($B$2,0,0,ROW($1:$5),1),">0")))

利用 SUMIF 函數找出儲存格範圍B2:B2、B2:B3、B2:B4、B2:B5、B2:B6中,大於0的數值總和,分別傳回:3, 8, 12, 14, 17。

(3) SUM(1*(ROW(1:1)>SUMIF(OFFSET($B$2,0,0,ROW($1:$5),1),">0")))+1

利用 ROW(1:1)=1 在第 1 個儲存中判斷是否大於 {3,8,12,14,17},結果傳回{FALSE,FALSE,FALSE,FALSE,FALSE},透過「1*」運算,將 TRUE/FALSE 轉換為 1/0

例如:在儲存格B10中,為 ROW(9:9) 判斷是否大於 {3,8,12,14,17},結果傳回{TRUE,TRUE,FALSE,FALSE,FALSE},最後 SUM 運算會加總 1,1,0,0,0,再加 1,最後結果為「3」。

將上式的傳回傳代入 INDEX 函數,顯示儲存格A2:A6中對應的儲存格,例如上式傳回值為「3」,再傳回「丙」。

再利用 IFFERROR 函數將公式向下複製時若傳回錯誤訊息時,顯示為空字串。

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

老師或是一般使用者,在教育現場或是工作環境,常常會對學生或特定人士進行問卷調查、搜集資料或是對某些訊息取得回應。大家可能對 Google 表單已有使用經驗,所以較為熟悉。而微軟為 Office 365 用戶提供了 Microsoft Forms 功能,可以讓你方便的製作表單,我們就來初體驗一下這個預覽版的功能。

網址:https://forms.office.com/

(你必須要有能登入 Office 365 的 Education subscription 帳號)

使用Microsoft Forms製作表單,簡單好用!

登入後,你可以檢視選單,已設為你的應用程式之一(目前還在設定中)。

使用Microsoft Forms製作表單,簡單好用!

我們來建立第一個表單。按一下「新增」:

使用Microsoft Forms製作表單,簡單好用!

為表單命名,並且說明表單內容,再按一下「新增問題」,開始輸入問題。

使用Microsoft Forms製作表單,簡單好用!

可以使用的問題有五類型式:選項、測驗、文字、評分、日期。

使用Microsoft Forms製作表單,簡單好用!

首先,選取:選項,輸入你的題目和選項內容,按一下「新增選項」或按一下「刪除」圖示,可以增減選項。

使用Microsoft Forms製作表單,簡單好用!

你也可以按一下:新增「其他」選項,讓使用者自填內容。按一下「需要答案」,讓這個題目變為「必答」。

使用Microsoft Forms製作表單,簡單好用!

接著新增問,選取:文字,輸入題目,這可以讓使用者自行輸入答案。如果你也選取了「開放性回答」,則使用者不只可以在一行內輸入答案,可以自行按 Enter 鍵,輸入多行的答案。

使用Microsoft Forms製作表單,簡單好用!

到此,按一下「預覽」圖示,檢視一下目前的表單。按一下「Back」,可以回到題目繼續編輯。而當你在「選項」這類題目中使用單選或複選時,會有不同的選取方式。

單選/非開放性回答:

使用Microsoft Forms製作表單,簡單好用!

複選/開放性回答:

使用Microsoft Forms製作表單,簡單好用!

而如果你所要的答案想要限制在某一區間中,你可以在省略符號(...)上按一下,選取「限制」,即可將想要的答案限制在你要的範圍。如果你也勾選了「子標題」,則可以為題目增加其他的說明。

使用Microsoft Forms製作表單,簡單好用!

再來,新增一個「評分」問題,你可以使用二種層級:5、10及二種符號:星號、數字,來做為評分強度。

使用Microsoft Forms製作表單,簡單好用!

接著,練習新增一個「日期」的題目,可以讓使用者可以自行挑選一個日期:

使用Microsoft Forms製作表單,簡單好用!

最後,還有一個「測驗」的類別,這是個老師的好幫手。你可以為題目設定正確答案,並且為某些答案設定要回饋給使用者的訊息。參考下圖,要挑選那些是質數,你先勾選那些是正確答案,並且為使用者選到錯誤答案時,給予一些訊息。

使用Microsoft Forms製作表單,簡單好用!

在製作表單過程中,你可以複製一個已完成的問題,上移/下移題目順序,或是刪掉某個題。

使用Microsoft Forms製作表單,簡單好用!

當你在預覽表單時,也可以預覽在行動裝置上的效果:

使用Microsoft Forms製作表單,簡單好用!

在完成表單前,你可以為這個表單設定一個主題:

使用Microsoft Forms製作表單,簡單好用!

完成的表單,要讓使用者填寫,按一下「傳送表單」,你可以取得這個表單的超連結,除了可以使用電子郵件傳送給他人填寫之外,你也可以產生這個超連結的 QR Code。點選「內嵌網頁」,還可以取得程式碼放在網頁或部落格中讓人填寫。

使用Microsoft Forms製作表單,簡單好用!

在上圖中,你可以選取任何取得連結的人,不需要登人即可填寫表單,或是要登入之後才能填寫表單。

當你讓使用者填寫表單後,可以在「標籤」下,看到填寫的狀況,及每一小題的分析圖。

使用Microsoft Forms製作表單,簡單好用!

也可以下載這個表單的結果成一個 Excel 檔。

使用Microsoft Forms製作表單,簡單好用!

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

同仁問到:一般在 Word 文件中都是由上而下/由左而右的輸入資料,如何才能讓所有資料靠下對齊,由下而上/由左而右的輸入資料?

有別於一般的文件,翻轉參考下圖,資料(包含圖文)輸入是先出現於版面的最下方,輸入的文字會往上推高,該如何處理?

Word-讓文件內容每一頁都能靠下對齊或是垂直置中

很簡單的步驟即可設定完成:

選取[版面配置/版面設定]功能表中右下角的完整設定按鈕,在開啟的[版面設定]對話框中,進入[版面配置]標籤,在[頁面]區中的[垂直對齊方式]下拉式清單中選取「靠下」選項。

Word-讓文件內容每一頁都能靠下對齊或是垂直置中

因此,如果要你要所有內容都要在版面中垂直置中對齊,則在在[頁面]區中的[垂直對齊方式]下拉式清單中選取「置中」選項。參考下圖,這樣就不會參考垂直尺規來手動定位圖片的位置了。

Word-讓文件內容每一頁都能靠下對齊或是垂直置中

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

1 2

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼