贊助廠商

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

搜尋本部落格文章資料

目前日期文章:201705 (26)

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

網友問到:如何將一個數字的『個位數』轉換為 0 和 5?其規則為「 0~4:0」和「5~9:5」。

Excel-依規則轉換數字的個位數(ROUNDDOWN,MOD)


【公式設計與解析】

儲存格C2:=ROUNDDOWN(A2,-1)+(MOD(A2,10)>4)*5

複製儲存格C2,貼至儲存格C2:C16。

(1) ROUNDDOWN(A2,-1)

利用 ROUNDDOWN 函數將儲存格A2的數字予以無條件捨去至個位數(個位數為 0)。(注意其中使用參數『-1』)

(2) (MOD(A2,10)>4)*5

利用 MOD(A2,10) 求得儲存各A2的個位數,則判斷是否大於 4,傳回 TRUE/FALSE。透過『*5』運算,可以將 TRUE/FALSE 轉換為 1/0,再將結果乘以 5。


【延伸學習】

關於 Excel 的進位問題,可參考:Excel-各種進位方式

, ,

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

網友想要根據 Excel 工作表中的基本資料(如下圖),列出含有指定字串的資料清單,該如何處理?

例如下圖中,要列出每一項目中含有「BBB」字串者,其中有可能某一項中有一個以上符合。為了方便說明,並且簡化公式,特別使用「輔助欄位」。

Excel-列出含有特字元的清單(SMALL,ROW,COLUMN,陣列公式)


【公式設計與解析】

1. 輔助欄位

儲存格I2:{=SUM(IFERROR(IF(FIND($B$16,B2:H2)>1,1,0),""))}

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

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

(1) IF(FIND($B$16,B2:H2)>1,1,0)

在陣列公式中利用 FIND 函數,找出是否同一列中有一個(含)以上包含指定字串的內容。若是(TRUE),則傳回 1,若否(FALSE),則傳回 0。(注意:FIND 函數若是儲存格中找不到含有指定字串,則會傳回錯誤訊息。)

(2) IFERROR(IF(FIND($B$16,B2:H2)>1,1,0),"")

利用 IFERROR 函數將傳回值是錯誤訊者,轉換為空字串。

(3) SUM(IFERROR(IF(FIND($B$16,B2:H2)>1,1,0),""))

在陣列公式中,以 SUM 函數將傳回的 1/0 予以相加。


2. 列出清單

你可以使用「篩選」功能將含有特定字串的項目列出,以下要以公式方式來產生。

儲存格A17:{=IFERROR(OFFSET($A$1,SMALL(IF($I$2:$I$13>0,
ROW($A$2:$A$13),""),ROW(1:1))-1,COLUMN(A:A)-1),"")}

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

複製儲存格A17,貼至儲存格A17:H27。

(1) IF($I$2:$I$13>0,ROW($A$2:$A$13),"")

判斷儲存格I2:I13中是否大於0(表示含有指定字串),若是,則傳回其列號;若否,則傳回空字串。

(2) SMALL(第(1)式,ROW(1:1))

利用 SMALL 函數由小至大找出符合者。(ROW(1:1)向下複製時,會產生ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→...。)

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

將第(2)式的結果代入 OFFSET 函數,找出以儲存格A1起始的對應儲存格內容。

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

因為 SMALL 函數可能傳回錯誤訊息,所以使用 IFERROR 函數將其顯示為空字串。

, , , ,

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

每隔一段時間就會有網友問到關於在 Excel 中如何製作多層的下拉式清單的問題。參考下圖,當選取了「類別」中的一個項目後,在項目1中顯示該項目所屬的清單。在項目2中跟著顯示項目所選的項目來顯示所選取的清單,該如何處理?

Excel-建立多層下拉式清單(資料驗證,INDIRECT) Excel-建立多層下拉式清單(資料驗證,INDIRECT) Excel-建立多層下拉式清單(資料驗證,INDIRECT)

本例先以三層的下拉式清單為例,讀者再自行擴充成你要的層數。本例的作法需要轉換表格,雖然比較費工,卻是對大多人較容易接受。參考以下的操作步驟:

1. 將表一轉換至表二、表三、表四。

Excel-建立多層下拉式清單(資料驗證,INDIRECT)

2. 選取儲存格H1:K14。

3. 按 Ctrl+G 鍵,選取[特殊]按鈕。

Excel-建立多層下拉式清單(資料驗證,INDIRECT)

3. 選取[常數]選項,按一下[確定]按鈕。(目的:選取有資料的儲存格)

Excel-建立多層下拉式清單(資料驗證,INDIRECT)

結果如下:

Excel-建立多層下拉式清單(資料驗證,INDIRECT)

4. 按 Ctrl+Shift+F3 鍵,勾選「最左欄」。(目的:以最左欄的儲存格內容定義名稱)

Excel-建立多層下拉式清單(資料驗證,INDIRECT)

結果如下:

Excel-建立多層下拉式清單(資料驗證,INDIRECT)

5. 選取儲存格B21,進入[資料驗證]對話框中,設定資料驗證準則:

儲存格內允許:清單;來源:=類別。

6. 選取儲存格C21,進入[資料驗證]對話框中,設定資料驗證準則:

儲存格內允許:清單;來源:=INDIRECT(B21)。

Excel-建立多層下拉式清單(資料驗證,INDIRECT)

7. 選取儲存格D21,進入[資料驗證]對話框中,設定資料驗證準則:

儲存格內允許:清單;來源:=INDIRECT(C21)。


【延伸練習】

依照以上的作法,如果要更多層相關的下拉式清單,則必須建立更多的表格,建立更多以資料驗證設定清單的儲存格。


【注意事項】

在本例中下拉式清單的內容含有中文字,如果你使用類似A1、C20等當為名稱會造成果擾。相同作法之下,定義的名稱會轉換如下的結果:

Excel-建立多層下拉式清單(資料驗證,INDIRECT)

, ,

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

網友提問:如果工作滿1年有7天假期,工作不滿3個月0天假期,工作滿3個月可以按比率得到7天的假期,該如何設計公式(參考下圖)? (比例計算公式=受僱天數/365天 * 7=得到的假期 (進位至整數))

Excel-計算休假天數


【公式設計與解析】

儲存格C2:=ROUND((B2>=90)*(IF(B2>365,365,B2)/365)*7,0)

複製儲存格C2,貼至儲存格C2:C11。

(B2>=90):條件一,判斷儲存格B2是否大於 90,傳回 TRUE/FALSE(經過『*』運算後,會轉換為 1/0。)

(IF(B2>365,365,B2):條件二,判斷儲存格B2是否大於 365,若是,則傳 365,若否,則傳回儲存格B2。

儲存格C2:=ROUND((B2>=90)*((B2>365)*365+(B2<365)*B2)/365*7,0)

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

如果你要使用 Excel 來抓取網頁上的表格資料,可以參考以下的文章:

Excel-從網頁上取得外部資料

Excel-在工作表中匯入網頁中的動態資料

Excel-取得部落格網頁的標題至工作表中

相同的動作,如果要在Google 試算表中執行,則可以 IMPROTHTML函數。

(參考:https://support.google.com/docs/answer/3093339?hl=zh-Hant

Google試算表-抓取網頁資料(IMPORTHTML)

例如,要抓取本部落格(http://isvincent.pixnet.net)的文章清單第 28頁:

http://isvincent.pixnet.net/blog/listall/28

Google試算表-抓取網頁資料(IMPORTHTML)

在儲存格A1輸入:

=IMPORTHTML(http://isvincent.pixnet.net/blog/listall/28,"table",3)

此處的參數 table 是指網頁中資料結構為表格者,參數 3 為第 3 個。

當你輸入完公式,按下 Enter 鍵後,速度很快的就會顯示匯入的結果:

(匯入的資料是靜態結果,並不會跟著網頁內容更新而變動。)

Google試算表-抓取網頁資料(IMPORTHTML)

如果你將公式中的參數 table 改成 list,則會得到以下的結果:

Google試算表-抓取網頁資料(IMPORTHTML)

對應至網頁中的:

Google試算表-抓取網頁資料(IMPORTHTML)

其他相關的函數還有:

IMPORTXML:匯入多種結構化資料類型的資料,包括 XML、HTML、CSV、TSV 和 RSS 以及 ATOM XML 資訊提供。

IMPORTRANGE:匯入指定試算表中特定儲存格的範圍。

IMPORTFEED:匯入 RSS 或 ATOM 資訊提供。

IMPORTDATA:將指定網址的資料匯入成 .csv (逗號分隔值) 或 .tsv (Tab 分隔值) 格式。

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

網友問到 Excel 的問題:在下圖中有二個清單包含了天和時的資料,如何將兩個數據予以加總?其中 1min = 0.1H(1分鐘=0.1小時)

以下圖中的第一列為例:1天14.41時+2天21.21時=4天12.02時。

Excel-天、時、分的運算(INT,MOD)


【公式設計與解析】

1. 計算天數

儲存格G2:=(A2+D2)+INT((B2+E2)/24)

複製儲存格G2,貼至儲存格G2:G15。

INT((B2+E2)/24):計算兩個儲存格中時的和是否大於24小時,若是,則進位 1 天。


2. 計算時數

儲存格H2:=MOD(INT(B2)+INT(E2),24)+INT((B2-INT(B2)+E2-INT(E2))/0.6)+
MOD(B2-INT(B2)+E2-INT(E2),0.6)

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

(1) MOD(INT(B2)+INT(E2),24)

計算小時數是否有超過24小時,若有則取其進位後的小時數。

(2) INT((B2-INT(B2)+E2-INT(E2))/0.6)

計算小時數是否有超過0.6時,若有則進位 1 小時。

(3) MOD(B2-INT(B2)+E2-INT(E2),0.6)

計算小時數是否有超過0.6時,若有,則取其進位後的小時數。

, ,

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

網友問到 Excel 的問題:如何將一個儲存格中裡數值中的每一個數字填入對應的儲存格?

例如:在下圖中有一個最多 9 位數的數字,如何將其每個位元的數字填填入對應的儲存格,其中包含負數的符號。

Excel-將數值中每一個位元的數字填入對應的儲存格(MID,LEN,COLUMN)


【公式設計與解析】

儲存格J2:=IFERROR(MID($L2,LEN($L2)-(10-COLUMN(J:J)),1),"")

複製儲存格J2,貼至儲存格A2:J14。

(1) LEN($L2)-(10-COLUMN(J:J))

因為要填入的儲存格數最多有 10 格,當公式向左複製/貼上時,10-COLUMN(J:J) 會依序產生 0,1,2,3,4,5,6,7,8,9。

利用 LEN 函數計算儲存格L2中的內容有多少個字元,而當公式向左複製/貼上時,在本例中 LEN($L2)-(10-COLUMN(J:J)) 依序傳回 10,9,8,7,6,5,4,3,2,1。

若是儲存格中的數值位元數(包含符號)少數 10 者,則公式 LEN($L2)-(10-COLUMN(J:J)) 會傳回負數。

(2) MID($L2,LEN($L2)-(10-COLUMN(J:J)),1)

利用 MID 函數依第(1)式的傳回值,取出由最後一個位元向左依序取出一個字。若第(1)式傳回負數,則會傳回錯誤訊息。

(3) IFERROR(MID($L2,LEN($L2)-(10-COLUMN(J:J)),1),"")

因為第(2)式可能傳回錯誤訊息,則利用 IFERROR 函數將其轉換為空字串。

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

網友問到 Excel 的問題:在工作表中的資料表有 9 個字元,分別對應數字 1~9(參考下圖右),如何將數個字元的字串轉換為數字?

在原始的欄位中的儲存格裡有多個不同數量的字元要轉換為數字,例如:在下圖左中 FCCG 要轉換為數字 6377,該如何處理?

Excel-將英文字元轉換為對應的數字(VLOOKUP,MID)


【公式設計與解析】

儲存格B2:
=IFERROR(VLOOKUP(MID(A2,1,1),$F$2:$G$10,2,TRUE),"")
&IFERROR(VLOOKUP(MID(A2,2,1),$F$2:$G$10,2,TRUE),"")
&IFERROR(VLOOKUP(MID(A2,3,1),$F$2:$G$10,2,TRUE),"")
&IFERROR(VLOOKUP(MID(A2,4,1),$F$2:$G$10,2,TRUE),"")
&IFERROR(VLOOKUP(MID(A2,5,1),$F$2:$G$10,2,TRUE),"")

複製儲存格B2,貼至儲存格B2:B20。

以 IFERROR(VLOOKUP(MID(A2,1,1),$F$2:$G$10,2,TRUE),"") 來說明:

(1) MID(A2,1,1)

利用 MID 函數,取出儲存格A2中的第 1 個字元。(改變第 2 個參數即可調整取出第幾個字元,但是如果要取出的字元超出儲存格的字元數,則會傳回錯誤訊息。)

(2) VLOOKUP(MID(A2,1,1),$F$2:$G$10,2,TRUE)

利用 VLOOKUP 函數將取出第(1)式取出的字元在對應表中查詢到對應的數字。

(3) IFERROR(VLOOKUP(MID(A2,1,1),$F$2:$G$10,2,TRUE),"")

由於第(1)式可能會傳回錯誤訊息,則以 IFERROR 函數將錯誤訊息轉換為空字串。

, ,

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

網友問到:在 Excel 的工作表中(如下圖),如何在D欄、E欄、F欄中,只能輸入同一列的A欄、B欄、C欄的內容?(共有1000列要套用同一規則)

參考下圖,例如在儲存格D1:F25範圍,利用下拉式清單,讓每儲存格只能輸入A1:C25中對應同一列中內容。

Excel-設定多個儲存格只能輸入同一列的內容(資料驗證)

【設計與解析】

基本上這是要利用「資料驗證」的方式來產生下拉式清單。如下操作:

1. 選取儲存格D1。

2, 設定儲存格內允許:清單;來源:=$A1:$C1。

注意:欄的部分要使用絶對參照,列的部分要使用相對參照。

Excel-設定多個儲存格只能輸入同一列的內容(資料驗證)

3. 複製儲存格D1,貼至儲存格D1:F25。

Excel-設定多個儲存格只能輸入同一列的內容(資料驗證)

以上無法防制使用者自行輸入清單以外的內容,如果你要限制只能輸入A欄、B欄、C欄中的其中一個,則必須將資料驗證改設定為:

設定儲存格內允許:自訂;來源:=COUNTIF($A1:$C1,D1)=1。

複製儲存格D1,貼至儲存格D1:F25。

Excel-設定多個儲存格只能輸入同一列的內容(資料驗證)

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

同事常會問到 Excel 真的那麼有用?很多公式我也不懂,學 Excel 要做什麼?我用以下的例子做了一部分的回應,重點是資料的處理。

例如:在圖書館的流通系統中匯出了一個200頁的文件,其中是學生的圖書借用資料。現在高三要畢業了,必須找出那些學生沒有還清圖書。如果系統有這個功能,那就不用傷腦筋了,問題是沒有。所以打算根據學生的借用資料來得到想要的結果,然而由系統匯出的資料並不是可以直接處理的資料(如下圖)。

Excel-資料處理(資料剖析、選取空白儲存格、大量輸入公式、排序)

其中的文字沒有排的很整齊,更不用說進一步的處理了。

Excel-資料處理(資料剖析、選取空白儲存格、大量輸入公式、排序)

這個時候,Excel 就派上用場了!

先把文字檔中的資料複製到 Excel 的工作表中:

Excel-資料處理(資料剖析、選取空白儲存格、大量輸入公式、排序)

接著,執行「資料剖析」:

Excel-資料處理(資料剖析、選取空白儲存格、大量輸入公式、排序)

資料變的整齊多了:

Excel-資料處理(資料剖析、選取空白儲存格、大量輸入公式、排序)

觀察上圖中的資料,其中A,B,C欄內的資料並不完整。先來補足其中的資料:

1. 選取A欄至C欄。

2. 按 Ctrl+G 鍵,開啟[到]對話框。

3. 按[特殊]按鈕。

Excel-資料處理(資料剖析、選取空白儲存格、大量輸入公式、排序)

4. 選取[空格]選項,按下[確定]按鈕。

Excel-資料處理(資料剖析、選取空白儲存格、大量輸入公式、排序)

5. 輸入公式「=A1」,按 Ctrl+Enter 鍵。

Excel-資料處理(資料剖析、選取空白儲存格、大量輸入公式、排序)

6. 結果如下:(空白的資料已經自動補上)

Excel-資料處理(資料剖析、選取空白儲存格、大量輸入公式、排序)

7. 複製A欄至C欄,在貼上時選取[選擇性貼上/值]選項,結果會把公式去除。

接著,對這個資料表執行「排序」:(利用「還書日期」欄位遞增排序)

Excel-資料處理(資料剖析、選取空白儲存格、大量輸入公式、排序)

排序結果的前面幾列就是我要的未還書清單:(還書日期空白者)

Excel-資料處理(資料剖析、選取空白儲存格、大量輸入公式、排序)

再看一次原始沒有規則性的資料排列:

Excel-資料處理(資料剖析、選取空白儲存格、大量輸入公式、排序)

在使用 Excel 的操作(資料剖析、選取空白儲存格、大量輸入公式、排序)之後,就可以得到你想要的資料。其中並沒有使用複雜的公式計算,都是常用的操作技巧。輕易的就成功的資料處理了!

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

在 Excel 中當我們在執行查詢時,有時會有錯誤的訊息產生,該如何避免出現這些訊息,而改以其他適當的方式來呈現?

在下圖中,當你使用 VLOOKUP 函數查詢時,若是查詢到空的儲存格,則會傳回 0,但是應該是顯示空白比較恰當。或是使用錯誤內容來查詢,正常會傳回 #N/A 這類的訊息,該如何以其他文字來回應呢?

Excel-錯誤訊息的處理(IFERROR,ISERROR)

【公式設計與解析】

(1) 儲存格E4:=VLOOKUP(D4,A2:B17,2,FALSE)

當使用 VLOOKUP 函數查詢,由於儲存格B7為空白,所以應該傳回空白,而非傳回 0。

(2) 儲存格E5:=VLOOKUP(D5,A2:B17,2,FALSE)&""

公式修正為將原公式,再串接一個空字串『""』即可解決。

(3) 儲存格E9:=VLOOKUP(D9,A2:B17,2,FALSE)

這是要查詢「王」這個項目所對應的數值。

(4) 儲存格E10:=VLOOKUP(D10,A2:B17,2,FALSE)

如果不小心以「壬」來查詢,則會傳回 #N/A 錯誤訊。

(5) 儲存格E11:=IFERROR(VLOOKUP(D10,A2:B17,2,FALSE),"查無此項")

利用 IFERROR 函數,將 #N/A 訊息,改以『查無此項』文字來顯示。

(6) 儲存格E11:=IF(ISERROR(VLOOKUP(D10,A2:B11,2,FALSE)),"查無此項",
VLOOKUP(D10,A2:B11,2,FALSE))

如果你使用的 Excel 試算表沒有 IFERROR 函數,則可以使用 ISERROR 來處理。

, ,

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

網友問到:在 Excel 中有一個資料表,如何計算出每個日期的配編數有幾類?

(請參考下圖)

image


【公式設計與解析】

為了簡化公式,特別利用二個輔助欄位。

1. 搜尋指定日期的起始列號

儲存格F2:{=MIN(IF(D2=INT($A$2:$A$25),ROW($A$2:$A$25),999))}

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

2. 搜尋指定日期的結束列號

儲存格E2:{=MAX(IF(D2=INT($A$2:$A$25),ROW($A$2:$A$25),0))}

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

3. 完整公式

儲存格G2:=SUMPRODUCT((1/COUNTIF(OFFSET($B$1,F2-1,0,G2-F2+1,1),
OFFSET($B$1,F2-1,0,G2-F2+1,1))))

請參考其他說明:

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

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

, ,

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

在 Excel 的資料表如下圖左,網友問到如何重組每三個文字插入一個數字,依此規則產生資料清單?

在下圖左之中,A欄由數字組成,B欄由文字組成,如何取出每三個文字插入一個數字?

Excel-資料交錯排列組合(OFFSET,INT,MOD)


【公式設計與解析】

(1) 處理數字部分

儲存格F1:=OFFSET($A$1,INT((ROW(1:1)-1)/4),0)

利用 INT 函數將除法結果再取不大於的最大整數。

公式:INT((ROW(1:1)-1)/4)

當公式向下複製時,產生:0,0,0,0,1,1,1,1,2,2,2,2,3,3,3,3,4,4, ...。

儲存格F1向下複製時,產生:1,1,1,1,2,2,2,2,3,3,3,3,4,4,4,4,5,5, ...。

(2)處理文字部分

儲存格G1:=OFFSET($B$1,ROW(1:1)-INT(ROW(1:1)/4)-1,0)

當公式向下複製時,產生:0,1,2,2,3,4,5,5,6,7,8,8,9,10,11,11,12,13, ...。

儲存格G1向下複製時,產生:甲,乙,丙,丙,丁,戊,己,己,庚,辛,壬,壬,癸,子,丑,丑,寅,卯, ...。

(3) 整合文字和數字

儲存格D1:=IF(MOD(ROW(1:1),4)=0,OFFSET($A$1,INT((ROW(1:1)-1)/4),0),
OFFSET($B$1,ROW(1:1)-INT(ROW(1:1)/4)-1,0))

MOD(ROW(1:1),4)=0:判斷儲存格的列號是否為 4,8,12,16, ...。

公式判斷,每隔四列時,會顯示數字,其餘顯示文字。

當公式向下複製時,會產生資料:甲,乙,丙,1,丁,戊,己,2,庚,辛,壬,3,癸,子,丑,4, ...。

, , ,

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

網友問到:在 Excel 的活頁簿中有 6 個工作表(A單位、B單位、C單位、D單位、E單位、F單位),如何計算這個 6 個工作表中相同位置儲存格的和?

這 6 個工作表的格式一致,其中「數值」欄位的內容不各有不同。

Excel-根據工作表名稱摘要各工作表的小計(INDIRECT)

在下圖中,分別計算A單位~F單位的甲乙丙、丁戊己庚、辛壬癸之小計,即根據工作表名稱,來摘要各工作表的小計,該如何處理?

Excel-根據工作表名稱摘要各工作表的小計(INDIRECT)


【公式設計與解析】

儲存格B2:=SUM(INDIRECT(A2&"!B2:B4"))

先將工作表名稱(儲存格A2)和字串『!B2:B4』加以串接,再利用 INDIRECT 函數將其轉換為儲存格位址。

儲存格C2:=SUM(INDIRECT(A2&"!B5:B8"))

儲存格D2:=SUM(INDIRECT(A2&"!B9:B11"))

複製儲存格B2:D2,貼至儲存格B2:D7。

,

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

網友問到:在 Excel 的工作表中有一個資料表(如下圖),如何計算資料中的的每個項目之小數的和?

如下圖,資料是由 A, B, C, D 再加上一個數字組合而成。如何分別依據各個項目來計算數字的小計。

Excel-取出第1個字元之後的數字計算總和(SUMPRODUCT,LEFT,MID)

【公式設計與解析】

選取資料欄位中有資料的所有儲存格(本例為儲存格A1:A22),按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:資料。

儲存格D2:=SUMPRODUCT((LEFT(資料,1)=C2)*MID(資料,2,999))

在 SUMPRODUCT 函數中利用二個陣列來運算:

LEFT(資料,1)=C2:在陣列公式中取出每個儲存格中的第一個字元,並判斷是否和儲存格 C2 的內容相同。傳回 TRUE/FALSE 陣列。

MID(資料,2,999):利用 MID 函數取出第一個字元以外的所有字元。(其中 999 只是一個較大的數,即使這個數字超過字串長度,仍可正常運作。)

公式中的『*』運算,可以將 TRUE/FALSE 陣列轉換為 1/0 陣列。

, , ,

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

以前的文章提過,如果在 Excel 的工作表中置入一張相片,如果放大到幾個A4版面時,列印出來的紙張就可以拼貼成一張大海報了。(例如下圖的相片跨兩張 A4 紙張)

如何將PDF檔列印在比A4還大的紙張

如果你將放有照片的工作表版面,直接放大到你要輸出的紙張(沒有要拼貼了)。下圖已將工作表調整為 A3 版面。

如何將PDF檔列印在比A4還大的紙張

當你把 Excel 另存新檔時,可以直接轉換為 PDF 檔。

如何將PDF檔列印在比A4還大的紙張

當你要列印這個 PDF 檔時,預設會遇到其自動切割成 A4 大小,該如何印在 A3 的紙上呢?

如何將PDF檔列印在比A4還大的紙張

如何將PDF檔列印在比A4還大的紙張

請你在列印時,在「大小」中勾選「依PDF頁面大小選擇紙張來源」。PDF 就不會縮放,也不會切割紙張了。

如何將PDF檔列印在比A4還大的紙張

或是在版面設定中先行改變紙張大小(例如:A3),就可以實際大小來列印了。

如何將PDF檔列印在比A4還大的紙張

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

問題從同事問到在 Google 注音輸入法之下,如何連續輸入大寫的英文字開始。

通常在輸入英文字時,Google 會貼心的為你執行首字大寫的動作,當第一個英文字輸入完成時,隨即會自動切換成小寫,該如何連續輸入大寫英文字呢?

(參考下圖)只要連續按二下『向上箭頭(控制大小寫)』,即可連續輸入大寫英文字了。因為這是虛擬鍵盤,所以不像實體鍵盤是硬體 switch,只要按下 CapsLock 鍵,即可維持大寫或小寫。

Google輸入法如何連續輸入大寫英文字 Google輸入法如何連續輸入大寫英文字

順便來看看 Google 鍵盤還有那些可以利用的輸入方式?

例如在下圖中,h 鍵之下還有一個『/』,當你在小寫狀態下,長按 h 鍵時,即可選取這個符號和大寫的 H 了。

Google輸入法如何連續輸入大寫英文字 Google輸入法如何連續輸入大寫英文字

當你在大寫狀態下,長按 H 鍵時,即可選取這個符號和大寫的 h。而你如果在注音狀態下,長按 h 鍵,還可切換選取注音或是英文大小寫字。

Google輸入法如何連續輸入大寫英文字 Google輸入法如何連續輸入大寫英文字

再來看看其他按鍵。例如長按 1 鍵,也會提供一些你可能用到的建議字元。

Google輸入法如何連續輸入大寫英文字 Google輸入法如何連續輸入大寫英文字

還可以選取一些特殊符號:

Google輸入法如何連續輸入大寫英文字 Google輸入法如何連續輸入大寫英文字

當你長按『,』號,即可選取輸入法的設定。而長按空白鍵時,可以變更鍵盤。

Google輸入法如何連續輸入大寫英文字 Google輸入法如何連續輸入大寫英文字

如果某些字元是你常用的,把它找出來,配合長按的動作,可以加快你的輸入速度。

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

網友根據這篇文章:Excel-計算含有部分字串的小計(SUMPRODUCT)

問到如果資料調整為下圖的代碼格式,如何計算各個代碼的加總小計?

Excel-計算含有部分字串的小計(SUMPRODUCT,LEFT,SUBSTITUTE)

【公式設計與解析】

方法一:

儲存格F2:=SUMPRODUCT((1*LEFT(代碼,3)=E2)*金額)

1*LEFT(代碼,3):利用 LEFT 函數將代碼欄位中的代碼取出左邊 3 碼(文字),利用『*1』將其轉換為數字。

1*LEFT(代碼,3)=E2:判斷取出的三碼數字是否和儲存格E2中的內容相同,傳回 TRUE/FALSE 陣列。

(1*LEFT(代碼,3)=E2)*金額:在 SUMPRODUCT 函數中,第 2 個『*』,相當於執行邏輯 AND 運算,會將 TRUE/FALSE 陣列轉換為 1/0 陣列。再計算『乘積和』。


方法二:

儲存格F2:=SUMPRODUCT((SUBSTITUTE(代碼,E2,"")<>代碼)*金額)

SUBSTITUTE(代碼,E2,"")<>代碼:利用 SUBSTITUTE 函數將代碼中的字串以空字串取代儲存格E2的內容,再和儲存格E2比對是否相同,傳回 TRUE/FALSE 陣列。(若是不相同,則傳回 TRUE,表示代碼中含有儲存格E2的內容;若是相同,則傳回 FALSE,表示代碼中沒有儲存格E2的內容。)

((SUBSTITUTE(代碼,E2,"")<>代碼)*金額:在 SUMPRODUCT 函數中,『*』運算過程中會將 TRUE/FALSE 陣列轉換為 1/0 陣列。再計算『乘積和』。

, , ,

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

網友提問:在 Excel 中(如下圖),如何求得在『配編』欄位中各個年月的配編數(幾種不一樣的類別)?

在下圖中,原始資料提供年月日的欄位,而統計表中是要以統計各年月的配編數,例如年月是10501者,有 1, 2, 3, 4 共四種配編類別,該如何處理?

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


【公式設計與解析】

儲存格G2:=SUMPRODUCT((1*LEFT($A$2:$A$26,5)=F2)*(1/COUNTIF
($C$2:$C$26,$C$2:$C$26)))

(1) 1*LEFT($A$2:$A$26,5)

取出儲存格A2:A26陣列儲格中,年月日的前 5 碼文字(年月)。其中『*1』,乃將前述的 5 碼文字轉換為 5 碼數字。

(2) 1*LEFT($A$2:$A$26,5)=F2

SUMPRODUCT 函數中設定條件,用以判斷儲存格A2:A26中前 5 碼和儲存格F2是否相同。

(3) 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』

(4) 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』。

, ,

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

在網頁或部落格文章中,常會需要用到其他各種資源。而當我們把檔案放在雲端時(例如:Google 雲端硬碟),如果能將檔案顯示在文章中來直接檢視。該如何處理?

以下就以和 Google 相關而常用的資源來介紹。

通常你會用「嵌入」的方式來處理,而各種資源也會提供「<iframe>...</iframe>」的程式碼供你使用。例如,你常會需要嵌入下列項目:

(1) Google 地圖、YouTube 影片、Google 日曆。

(2) Google 文件、Google 試算表、Google 簡報、Google 表單、Google 繪圖等。

(3) Google 雲端硬中的 PDF 檔、Google 雲端硬中的資料夾等。


1. Google 地圖

在 Google 地圖左側的選單中,點選「分享或嵌入地圖」。

在網頁中嵌入各種Google資源

你會看到分享連結(用以直接在瀏覽器中顯示地圖內容),切換至「嵌入地圖」,調整好地圖的大小後,複製其中的「<iframe>...</iframe>」程式碼,置於你的網頁或部落格中。

在網頁中嵌入各種Google資源


2. YouTube 影片

在 YouTube 影片的下方找到「分享」的圖示,再按「嵌入」。

在網頁中嵌入各種Google資源

複製其中的「<iframe>...</iframe>」程式碼,置於你的網頁或部落格中。

在網頁中嵌入各種Google資源


3. Google 文件

如果在 Google 雲端硬碟中新增了 Google 文件,你可以在選單中選取[檔案]功能表的「發佈到網路」:

在網頁中嵌入各種Google資源

(此時尚未啟用發佈)在[已發佈的內容與設定中]選取是否要「內容有變更時自動重新發佈」。按一下「發佈」。

image

此時才會產生一個連結,分享這個連結,他人即可看到文件內容。請按一下「內嵌」。

在網頁中嵌入各種Google資源

複製其中的「<iframe>...</iframe>」程式碼,貼至部落格文章或網頁中。即可在網頁中顯示該文件內容。(按一下「停止發佈」,即可取消分享。)

在網頁中嵌入各種Google資源


4. Goolge 試算表

仿 Google 文件的做法,你會看到「連結」和「嵌入」兩種分享方式。

在網頁中嵌入各種Google資源

若選取連結方式,Google 試算表可以整份文件分享,也可以只分享其中一個工作表。

在網頁中嵌入各種Google資源

若選取網頁方式,則可以有以下幾種分享方式。

在網頁中嵌入各種Google資源

若是切換至「嵌入」,Google 試算表可以整份文件分享或是只分享一個工作表。複製其中的「<iframe>...</iframe>」程式碼,貼至部落格文章或網頁中。

在網頁中嵌入各種Google資源

註:其中有些參數,可以自行調整。例:

(取自:https://support.google.com/docs/answer/37579?hl=zh-Hant

gid=1674242611 - 這是工作表 ID。

range=A1:B14 - 要顯示的範圍。

widget=false - 如果是 false,表示不在底部顯示工作表標籤。

headers=false - 不顯示列和欄的編號。

chrome=false - 不顯示標題和頁尾。


5. Google 簡報

若是分享的是 Google 簡報的連結,則可以設定自動播放的時間值。

在網頁中嵌入各種Google資源

若選取「內嵌」,則還可以選取投影片大小,複製其中的「<iframe>...</iframe>」程式碼,貼至部落格文章或網頁中。

在網頁中嵌入各種Google資源

其中可以選取的大小有:小、適中、大等,還可以自訂大小。

在網頁中嵌入各種Google資源

播放速度有以下幾種:

在網頁中嵌入各種Google資源


6. Google 表單

若是要在網頁中顯示 Google 表單,則在編輯表單時,先選取分享,再選取「嵌入HTML」,複製其中的「<iframe>...</iframe>」程式碼,貼至部落格文章或網頁中。你可以在此自訂長、寬的大小。

在網頁中嵌入各種Google資源


7. Google 繪圖

Google 繪圖提供的連結和內嵌的是一張圖片檔,你可以設定尺寸大小後,複製其中的「<img src=...」起始的程式碼,貼至部落格文章或網頁中。

在網頁中嵌入各種Google資源


8. Google 日曆

若要將 Google 日曆嵌入網頁中,則先開啟 Google 日曆,選取「設定」:

在網頁中嵌入各種Google資源

在[日曆]標籤下點選一個日曆:

在網頁中嵌入各種Google資源

在[嵌入此日曆]項下,選取<iframe>...</iframe>的程式碼,貼至部落格文章或網頁中。

在網頁中嵌入各種Google資源


9. PDF 檔案

如果要在網頁中嵌入一個 PDF 檔案,方便使用者直接檢視網頁內容。做法如下:

1. 先將檔案上傳至 Google 雲端硬碟,並且設定與他人共用(否則別人就看不到了)。

2. 點選這個 PDF 檔案,以檢視這個 PDF 檔案。

3. 點選「選項」中的「在新視窗中開啟」。

在網頁中嵌入各種Google資源

4. 再點選「選項」中的「嵌入項目」。

在網頁中嵌入各種Google資源

5. 取得嵌入項目中的<iframe>...</iframe>程式碼,貼至部落格文章或網頁中。

在網頁中嵌入各種Google資源


10. Google Drive 資料夾

如果想要將雲端硬碟的某個資料夾嵌入網頁或文章中,則先選取要嵌入的資料夾按右鍵,選取「共用」。

在網頁中嵌入各種Google資源

在共用設定中的共用連結取得代碼(參考下圖)

在網頁中嵌入各種Google資源

將代碼置入以下的<ifrme>...</iframe>中的目錄ID位置:

<iframe allowtransparency="false" height="300" src="https://drive.google.com/embeddedfolderview?authuser=0&hl=zh_TW&id=目錄ID#list" width="100%"></iframe>

其中#list為檔案以清單顯示,也可改用#grid。其他的allowtransparency、height、width等參數,也都可以自行變更。

複製「<iframe>...</iframe>」程式碼,貼至部落格文章或網頁中。

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

1 2

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼