贊助廠商

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

搜尋本部落格文章資料

目前分類:講義資料 (2023)

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

(網友提問)在 Excel 中如果要使用公式以起始字串來篩選清單中的項目,該如何處理?

參考下圖,要以起始字串(本例:A135790)在一個號碼的清單中篩選。

Excel-使用公式以起始字串來篩選(OFFSET,ROW,LEN)

 

【手動篩選】

如果你使用篩選工具,就可以使用「文字篩選」下的「開始於」功能來篩選。

Excel-使用公式以起始字串來篩選(OFFSET,ROW,LEN)

輸入開始的字串:(本例為A135790)

Excel-使用公式以起始字串來篩選(OFFSET,ROW,LEN)

隨之得到篩選結果:

Excel-使用公式以起始字串來篩選(OFFSET,ROW,LEN)

 

【公式設計與解析】

如何以公式來完成 ?

先選取A1:A35,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:號碼。

儲存格D2:{=IFERROR(OFFSET($A$1,SMALL(IF(LEFT(號碼,LEN($C$2))=$C$2,
ROW(號碼),""),ROW(1:1))-1,0),"")}

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

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

Excel-使用公式以起始字串來篩選(OFFSET,ROW,LEN)

(1) IF(LEFT(號碼,LEN($C$2))=$C$2,ROW(號碼),"")

先以LEN 函數計算儲存格C2的文字長度,然後在陣列公式中利用 LEFT 函數判斷所有號碼清單由左邊取和儲存格C2相同的的文字長度,如果和儲存格C2相同者傳回其列號(利用 ROW 函數),否則傳回空字串。

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

利用 SMALL 函數於第(1)式的傳回值由小至大取出列號。公式向下複製時,ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→…。

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

將第(2)式傳回的列號代入 OFFSET 函數取得對應的儲存格內容。

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

公式可能傳回錯誤訊息,藉由 IFERROR 函數將錯誤訊息置換為空字串。

文章標籤

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

(網友提問)參考下圖,如何在 Excel 中根據分組表將成員名單顯示每位成員所分的隊伍?

下圖中,右邊是分組表,左邊是成員名單,有些成員沒有被分到組別,要將有分到組別者,顯示其組名。

Excel-查詢分組組別(SUMPRODUCT,COLUMN,OFFSET)

 

【公式設計與解析】

儲存格C3:=IFERROR(OFFSET($E$2,0,SUMPRODUCT(($E$3:$H$13=A3)*
COLUMN($E$3:$H$13))-5),"")

(1) COLUMN($E$3:$H$13)

找出儲存格E3:H13中,每個儲存格的欄號(傳回一個數字)。COLUMN(E3)=5、COLUMN(F3)=6、COLUMN(G3)=7、COLUMN(H3)=8。

(2) SUMPRODUCT(($E$3:$H$13=A3)*COLUMN($E$3:$H$13))

由於每位隊員僅會被分在一組中,所以利用 SUMPRODUCT 函數找出儲存格A3內容對應組別的欄號。

(3) OFFSET($E$2,0,第(2)式-5)

將第(2)式傳回的欄號置入 OFFSET 函數,取得對應的儲存格內容(組名)。其中『-5』是因為分組表由E欄開始。

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

若成員沒有被分到組別,可能傳回錯誤訊息,利用 IFFERROR 函數將錯誤訊息置換為空字串。

文章標籤

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

在 Excel 中有時會用到多表格的查詢(如下圖),你可以使用各種查詢的公式,例如:INDEX、VLOOKUP、OFFSET等,本例以3種不同的做法來練習在多個表格中查詢,也要看看如何簡化公式。前提是多個表格的格式要是一致的。

Excel-多表格查詢(INDEX,MATCH,INDIRECT,OFFSET)

若要在儲存格H2:J2中使用了3 個條件來搜尋(概念如下圖),可以看成是 3D 的搜尋。

Excel-多表格查詢(INDEX,MATCH,INDIRECT,OFFSET)


【公式設計與解析】

1. 使用 INDEX 函數的參照形式

儲存格K2:

公式:=INDEX(($B$2:$F$4,$B$7:$F$9,$B$12:$F$14,$B$17:$F$19,$B$22:$F$24),
MATCH($I2,$A$2:$A$4,0),MATCH(J$2,$B$1:$F$1,0),MATCH(H2,{"甲班","乙班",
"丙班","丁班","戊班"},0))

INDEX 函數有兩種查表形式:

(1) array 之陣列形式(傳回指定儲存格或儲存格陣列的值)

(2) reference之參照形式(傳回指定儲存格的參照)。

本例使用 reference之參照形式,語法如下:

INDEX(reference, row_num, [column_num], [area_num])

Reference:一個或多個儲存格範圍的參照。(若是非相鄰的選取範圍做為 reference,則必須使用括號括住 reference)

Row_num:參照中要傳回參照的列數。

Column_num:參照中要傳回參照的欄數。

Area_num:在參照中選取範圍以傳回 Row_num 與 Column_num 的交集。

Excel-多表格查詢(INDEX,MATCH,INDIRECT,OFFSET)

公式中利用 MATCH 函數來查詢位置,例如:

(1) MATCH($I2,$A$2:$A$4,0)

查詢儲存格I2在儲存格A2:A4中的位置,傳回一個數值(本例傳回3)。

(2) MATCH(J$2,$B$1:$F$1,0)

查詢儲存格J2在儲存格B1:F1中的位置,傳回一個數值(本例傳回2)。

(3) MATCH(H2,{"甲班","乙班","丙班","丁班","戊班"},0)

查詢儲存格H2(丙班)在{"甲班","乙班","丙班","丁班","戊班"}陣列中的位置,傳回一個數值(本例傳回3)。


2. 使用 OFFSET 函數

儲存格K2:

公式:=OFFSET($A$1,MATCH($I2,$A$2:$A$4,0)+5*(MATCH(H2,{"甲班","乙班",
"丙班","丁班","戊班"},0)-1),MATCH(J$2,$B$1:$F$1,0))

(1) MATCH($I2,$A$2:$A$4,0)

查詢儲存格I2在儲存格A2:A4中的位置,傳回一個數值(本例傳回3)。

(2) 5*(MATCH(H2,{"甲班","乙班","丙班","丁班","戊班"},0)-1)

查詢儲存格H2(丙班)在{"甲班","乙班","丙班","丁班","戊班"}陣列中的位置,傳回一個數值(本例傳回3)。其中『5*』的用意是因為每個表格的位置相差 5 列。

(3) MATCH(J$2,$B$1:$F$1,0)

查詢儲存格J2在儲存格B1:F1中的位置,傳回一個數值(本例傳回2)。

Excel-多表格查詢(INDEX,MATCH,INDIRECT,OFFSET)


3. 使用 INDEX+INDIRECT 函數配合定義名稱

先定義儲存格名稱。

(1) 選取儲存格A1:F4,按 Ctrl+F3 鍵,開啟「名稱管理員」對話框。

(2) 按下「新增」按鈕,名稱已自動帶入「甲班」,參照到已自動帶入「$A$1:$F$4」。

Excel-多表格查詢(INDEX,MATCH,INDIRECT,OFFSET)

(3) 按下「確定」按鈕,即新增一個名稱定義:甲班。

Excel-多表格查詢(INDEX,MATCH,INDIRECT,OFFSET)

(4)重覆步驟(1)至步驟(3),定義名稱:乙班、丙班、丁班、戊班。


儲存格K2:

公式:=INDEX(INDIRECT(H2),MATCH($I2,$A$2:$A$4,0)+1,MATCH
(J$2,$B$1:$F$1,0)+1)

(1) INDIRECT(H2)

利用 INDIRECT 函數將儲存格H2的內容轉換為一個儲存格範圍。(先前已定義名稱)

(2) MATCH($I2,$A$2:$A$4,0)+1

查詢儲存格I2在儲存格A2:A4中的位置,傳回一個數值(本例傳回3)。『+1』是因為定義名稱時是以儲存格A1為起始,多了這個儲存格,所以相對位置要多 1。

(3) MATCH(J$2,$B$1:$F$1,0)+1

查詢儲存格J2在儲存格B1:F1中的位置,傳回一個數值(本例傳回2)。『+1』是因為定義名稱時是以儲存格A1為起始,多了這個儲存格,所以相對位置要多 1。

Excel-多表格查詢(INDEX,MATCH,INDIRECT,OFFSET)

文章標籤

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

觀察下圖!圖裡的 Excel 工作表有個資料清單(本例中的儲存格有部分是以亂數產生數值),完全無法辨識儲存格中是否有為含有:公式、註解、設定格式化的條件、常數、資料驗證等。如果想要對這些項目進行操作時,還得先找出各個項目所屬的儲存格才能處理。

Excel-搜尋特殊的目標

還好,Excel 已內建搜尋這些項目的功能,可以用搜尋方式找到:公式、註解、設定格式化的條件、常數、資料驗證等

Excel-搜尋特殊的目標

(1) 搜尋含有「公式」的儲存格(和搜尋含有「常數」的儲存格互為相反)

Excel-搜尋特殊的目標

(2) 搜尋含有「註解」的儲存格

Excel-搜尋特殊的目標

(3) 搜尋含有「設定格式化的條件」的儲存格

Excel-搜尋特殊的目標

(4) 搜尋含有「常數」的儲存格(和搜尋含有「公式」的儲存格互為相反)

Excel-搜尋特殊的目標

(5) 搜尋含有「資料驗證」的儲存格

Excel-搜尋特殊的目標

以上這些都可以使用搜尋「特殊目標」來達到相同結果,而且可以搜尋空格、目前範圍、目前陣列、物件等。

Excel-搜尋特殊的目標

你也有可以來搜尋不同的內容的儲存格。例如:選取儲存格E1:F15,並選取儲存格E3。

Excel-搜尋特殊的目標

當搜尋「欄差異」,會選取以下四個和其他儲存格不同的儲存格。

Excel-搜尋特殊的目標

例如:選取儲存格A14:F15,並選取儲存格A15。

當搜尋「列差異」,會選取以下二個和其他儲存格不同的儲存格。

Excel-搜尋特殊的目標

若選取儲存格F1:F15,再搜尋「從屬參照」,則會找到公式中有參照其他儲存格者。

Excel-搜尋特殊的目標

若選取儲存格F1:F15,再搜尋「前導參照」,則會找有被公式參照的儲存格。

Excel-搜尋特殊的目標

文章標籤

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

在 Excel 中己提供了將儲存格範圍複製作圖片的功能,但是沒有提供將該圖片儲存成圖片檔的功能,只能以間接的方式存檔了。

以下圖為例,選取一個儲存格範圍(不接受不連續的儲存格範圍),再選取「剪貼簿/複製/複製成圖片」功能。

將Excel中儲存格內容儲存成圖片

設定選項:

將Excel中儲存格內容儲存成圖片

然後,開啟一個 Word 文件,貼上剛剛複製的圖片。

再於圖片上按右鍵,選取「另存成圖片」選項。

將Excel中儲存格內容儲存成圖片

有多種圖片格式供選取。

將Excel中儲存格內容儲存成圖片

 

文章標籤

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

在 Excel 中以欄名列號當為儲存格名稱,如果想要根據欄的名稱轉換為欄的數值,該如何處理?(參考下圖)

Excel-欄號和欄名互轉(CODE,MID,ADDRESS,SUBSTITUTE)

 

【公式設計與解析】

參考下圖,在 Excel 中是以A~Z、AA~AZ、BA~BZ、...為欄名的順序。欄的數值為A=1、B=2、C=3、...、AA=27欄。

Excel-欄號和欄名互轉(CODE,MID,ADDRESS,SUBSTITUTE)

所以,XEZ:第 16380 欄;XFD:第 16384 欄

Excel-欄號和欄名互轉(CODE,MID,ADDRESS,SUBSTITUTE)

1. 計算欄名對應的數值

儲存格C2:=SUM((CODE(MID(A2,{1,2,3},1))-64)*26^{2,1,0})

(1)MID(A2,{1,2,3},1)

本例使陣列來簡化公式,利用 MID 函數找出儲存格A2中的第 1, 2, 3 個字。

(2) CODE(MID(A2,{1,2,3},1))

找出儲存格A2中每個字的ASCII碼。

(3) CODE(MID(A2,{1,2,3},1))-64

將上式減64,可得A=1、B=2、C=3、...。

(4) (CODE(MID(A2,{1,2,3},1))-64)*26^{2,1,0}

將第1碼X26^2,將第2碼X26^1,將第3碼X26^0。

最後利用 SUM 函數予以加總,即為所求。

 

2. 計算數值對應的欄名

儲存格G2:=SUBSTITUTE(ADDRESS(1,E2,4),1,"")

(1) ADDRESS(1,E2,4)

ADDRESS 函數可以自動轉換數值為欄名,本例參數 1 會傳回列號為 1 的儲存格位址。

(2) SUBSTITUTE(ADDRESS(1,E2,4),1,"")

利用 SUBSTITUTE 函數將列號 1 置換為空字串("")。

Excel-欄號和欄名互轉(CODE,MID,ADDRESS,SUBSTITUTE)

文章標籤

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

(網友提問)在 Excel 中常會用到要執行查詢的工作,或許你會用 VLOOKUP 函數或是 INDEX 函數,以欄、列交會之處查詢。但是如果要列出相同者的清單,就無法直接使用這兩個函數。這次來試試 OFFSET 函數。

Excel-列出指定項目的清單(MATCH,COUNTIF,OFFSET)


【公式設計與解析】

本題已假設同機種的物料是連續排列。

儲存格E2:=IF(ROW(1:1)<=COUNTIF($A$2:$A$24,$D$2),OFFSET($B$1,
MATCH($D$2,$A$2:$A$24,0)+ROW(1:1)-1,0),"")

複製儲存格E2,貼至儲存格E2:E9。

(1) COUNTIF($A$2:$A$24,$D$2)

計算儲存格D2(機種)在儲存格A2:A24中共有幾個(傳回數值,本例傳回 7)。

(2) MATCH($D$2,$A$2:$A$24,0)

找出儲存格D2(機種)在儲存格A2:A24中第一個的位置(傳回數值,本例傳回 11)。

(3) OFFSET($B$1,第(2)式+ROW(1:1)-1,0)

當公式向下複製時,ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→...。

第(2)式+ROW(1:1)-1:本例依序傳回 11、12、13、...。

將上式代入 OFFSET 函數求得對應的儲存格內容。

(4) IF(ROW(1:1)<=第(1)式,第(3)式,"")

當公式向下複製時,ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→...。

本例中若 ROW 函數的傳回值小於或等於 7,則執行第(3)式,否則顯示空字串。

文章標籤

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

許多網友對於 Excel 中的 VLOOKUP 函數或許不陌生,但如果要在公式中使用雙條件或多條件,可能就會產生一些困擾,甚至不知如何下手。

因為 VLOOKUP 函數是藉由將要查詢的內容和資料陣列的第一欄比對,查到第幾列時,再由公式中指定的欄數,以欄列交會查出對應的儲存格內容。在此,若要使用雙條件,的確很不方便。該如何解決?

Excel-在VLOOUP函數中使用雙條件查詢

 

【公式設計與解析】

我的部落格上有許多應用雙條件的查詢公式可參考:

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

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

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

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

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

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

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

但是,這些公式對於很多讀者是很大的門檻,不容易一看就懂。所以,試著將程序稍微複雜化,或許公式的理解和使用也會變簡單。

本例中,如果要使用 VLOOKUP 函數來查詢雙條件的結果,使用者可以先建立一個輔助欄位(A欄),其內容是 AA 和 BB 的串接,例如:儲存格A2:=B2&C2,複製儲存格A2,貼至儲存格A2:A17。

再使用公式:

儲存格F6:=VLOOKUP(F3&G3,A2:D17,4,FALSE)

如此,便可以由雙條件查詢對應的結果。

文章標籤

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

網友問到關於 Excel 中 WEEKDAY 函數的使用:

在公式中使用 WEEKDAY 函數,可以依傳回的數值來判斷為星期幾。

WEEKDAY的參數與傳回值:

1:適用星期日為每週第一天者(傳回值以 1 為起始)

2:適用星期六為每週第一天者(傳回值以 2 為起始)

3:適用星期一為每週第一天者(傳回值以 0 為起始)

11~17:分別對應以星期一至星期日為第一天者(對應傳回值以 1~7 為起始)

其中參數 1 和 17 為相同結果;參數 2 和 11 為相同結果。

Excel-關於WEEKDAY函數

參數 11~17 方便在公式中使用連續的數列產生傳回值。

例如公式:=WEEKDAY(A2,10+ROW(1:1))

Excel-關於WEEKDAY函數

文章標籤

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

在Excel中執行排序動作時,通常習慣使用由上而下的排序方式,如果想要由左至右排序時,該如何處理?而中文字的排序預設是依筆劃多寡來排序(還可以調整為依注音排序),如果想要依自己定義的順序排序,該如何處理?

image

 

1. 由上而下遞減自訂清單排序(癸→壬→辛→...→丙→乙→甲)

Excel 預設為「由上而下」排序,如果要自訂依癸→壬→辛→...→丙→乙→甲,必須要先在排序的順序中選取「自訂清單」。

image

再選取「子,丑,寅,卯,辰,巳,午,未,申,酉,戌,亥」。(此為預設的自訂清單,所以如果你有自己的清單,應先在此自訂。)

image

然後在順序中選取:亥,戌,酉,申,未,午,巳,辰,卯,寅,丑,子。(遞減排序)

image

結果如下:

image

 

2. 由左而右遞減自訂清單排序(亥→戌→酉→...→寅→丑→子)

如果在Excel中要由左而右排序,則要先選取「選項」,然後選取「循列排序」。

image

在排序的順序中選取「自訂清單」,再選取「甲,乙,丙,丁,戊,己,庚,辛,壬,癸」。

然後在順序中選取:癸,壬,辛,庚,己,戊,丁,丙,乙,甲。(遞減排序)

image

結果如下:

image

文章標籤

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

在 Excel 中使用 VLOOKUP 函數和 HLOOKUP 函數都可以用來做為查詢的工具,這兩者的查詢在概念上有何差別?

Excel-VLOOKUP查詢和HLOOKUP查詢

 

【公式設計與解析】

1. 使用VLOOKUP函數

儲存格N4:=VLOOKUP(N3,B3:K22,MATCH(N2,B2:K2,0),FALSE)

(1) 找出儲存格N3在資料陣列儲存格B3:K22中的第一欄(B欄)的位置(第n列)。

(2) 以MATCH(N2,B2:K2,0)找出儲存格N2在儲存格B2:K2中的位置(第m欄)。

(3) 第(1)式和第(2)式欄(m)、列(n)交會之處,即為查詢結果。

Excel-VLOOKUP查詢和HLOOKUP查詢

 

2. 使用HLOOKUP函數

儲存格N4:=HLOOKUP(N2,C2:K22,MATCH(N3,B2:B22,0),FALSE)

(1) 找出儲存格N2在資料陣列儲存格C2:K22中的第一列(2列)的位置(第m欄)。

(2) 以MATCH(N3,B2:B22,0)找出儲存格N3在儲存格B2:B22中的位置(第n列)。

(3) 第(1)式和第(2)式欄(m)、列(n)交會之處,即為查詢結果。

Excel-VLOOKUP查詢和HLOOKUP查詢

文章標籤

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

當在 Excel 中的公式運算結果出現小數,如果想要轉換為特定分母的分數,該如何處理?以下圖例,將1~24 除以 24,會得到以小數顯示的數值。如何轉換為以 24 為分母的分數?

Excel-設定小數轉換為指定分母的分數

選取儲存格A15:D20,設定數值格式,自訂:# ??/24。

即 # 後接空格,再接 ??,再接 /,再接 24(指定的分母)。

Excel-設定小數轉換為指定分母的分數

這是設定數值格式:# ??/48 的結果:

Excel-設定小數轉換為指定分母的分數

這是設定數值格式:# ??/12 的結果:

Excel-設定小數轉換為指定分母的分數

文章標籤

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

在 Excel 中,日期是以數值來儲存,其格式為「年/月/日」和「數值/數值/數值」很相像,學生常會搞不清楚。試著比較在儲存格中輸入以下的內容顯示的結果:

(1) =2016/2/4:顯示252,其執行公式運算2016除以2、再除以4的結果。

(2) 2016/2/4:顯示2016/2/4,其執行輸入日期2016年2月4日。

(3) '2016/2/4:顯示2016/2/4,其執行輸入2016/2/4字串。

(4) ="2016/2/4":顯示2016/2/4,其執行公式運算顯示2016/2/4字串。

Excel-比較「年/月/日」和「數值/數值/數值」

 

文章標籤

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

(回答網友提問)網友根據下圖,想要利用 Excel 來根據指定條件來計算次數,該如何處理?

例如:根據「編號」來計算「遲到、未帶卡、傳遞物品」等的次數。

Excel-根據條件計算次數(SUMPRODUCT,ISBLANK,NOT)

 

【公式設計與解析】

選取儲存格A1:G8,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:編號、日期、排班、姓名、遲到、未帶卡、傳遞物品。

1. 計算遲到的次數。

儲存格D11:=SUMPRODUCT((編號=A11)*NOT(ISBLANK(遲到)))

複製儲存格D11:F11,貼至儲存格D11:F12。

SUMPRODUCT 函數,根據:

條件一:編號=A11,編號儲存格範圍中和儲存格A11相同者。

條件二:NOT(ISBLANK(遲到)),遲到儲存格範圍中不是空白者。

ISBLANK 函數:判斷儲存格是否為空白,傳回 TRUE/FALSE

NOT 函數: 將 TRUE/FALSE 轉換為 FALSE/TRUE

2. 計算未帶卡的次數。

儲存格E11:=SUMPRODUCT((編號=A11)*NOT(ISBLANK(未帶卡)))

3. 計算傳遞物品的次數。

儲存格F11:=SUMPRODUCT((編號=A11)*NOT(ISBLANK(傳遞物品)))

文章標籤

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

如何利用 Excel 來計算多重選擇的總得分?如下圖,假設每個題目的答案由 A, B, C, D 所組成,答案可能是其中的 1 ~ 4 個所組成。

Excel-比對答案自動計算分數(多重選擇)(SUMPRODUCT,SUBSTITUTE)

其得分的標準:

●該選的選項也有選:+1分

●該選的選項沒有選:+0分

●不該選的選項沒有選:+1分

●不該選的選項卻有選:+0分

 

【公式設計與解析】

1. 計算每個題目題分

儲存格D2:=SUMPRODUCT(((SUBSTITUTE(B2,{"A","B","C","D"},"")=B2)=
(SUBSTITUTE(C2,{"A","B","C","D"},"")=C2))*1)

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

SUMPRODUCT 函數中,公式可以陣列形式來運算。{"A","B","C","D"} 表示由 A、B、C、D 字元組成的陣列。

(1) SUBSTITUTE(B2,{"A","B","C","D"},"")=B2

利用 SUBSTITUTE 函數將儲存格B2中的 A、B、C、D 分別置換成空字串(共會運算 4 筆)。再分別判斷 4 個傳回值是否和儲存格B2相同。例如,置換 A 後若傳回相同,代表儲存格B2中沒有 A 字元。

 

(2) SUBSTITUTE(C2,{"A","B","C","D"},"")=C2

利用 SUBSTITUTE 函數將儲存格C2中的 A、B、C、D 分別置換成空字串(共會運算 4 筆)。再分別判斷 4 個傳回值是否和儲存格C2相同。

 

(3) (第(1)式)=(第(2)式))*1

SUMPRODUCT 函數中,判斷第(1)式和第(2)式的結果是否相等,傳回 TRUE/FALSE 陣列。因為 TURE 代表得分(+1),FALSE 代表沒有得分(+0),所以利用公式中的『*1』運算,將 TRUE/FALSE 陣列轉換為 1/0 陣列。

最後經由 SUMPRODUCT 函數予以加總,即為該題得分。

 

2. 計算所有題目總得分

儲存格G2:=SUMPRODUCT(((SUBSTITUTE(B2:B26,{"A","B","C","D"},"")=B2:B26)
=(SUBSTITUTE(C2:C26,{"A","B","C","D"},"")=C2:C26))*1)

有了「1. 計算每個題目題分」的運算經驗,只要將公式中的儲存格B2置換成儲存格B2:B26,和將儲存格C2置換成儲存格C2:C26,即為所求。

由本例來看,善用 SUMPRODUCT 函數來處理陣列形式的運算,可以縮短公式的總量。或許對很多人而言,其實看不懂這樣的公式,建議由其他較簡單的 SUMPRODUCT 函數運算先理解。

文章標籤

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

在 Excel 的工作表中如果能好好利用設定格式化的條件來處理多儲存格的格式,可以不怕儲存格新增/刪除所帶來又要重設的困擾。

例如:(參考下圖)如何在一個資料表中,自動為間隔欄、間隔列、間隔欄列交會產生不同的背景色彩。

Excel-自動產生間隔欄、間隔列不同背景色彩(MOD,ROW,COLUMN)

這是原始表格:

Excel-自動產生間隔欄、間隔列不同背景色彩(MOD,ROW,COLUMN)

1. 產生間隔欄不同背景色彩

Excel-自動產生間隔欄、間隔列不同背景色彩(MOD,ROW,COLUMN)

選取儲存格B2:K13,設定格式化的條件:

規則類型:使用公式來決定要格式化哪些儲存格

規則:=MOD(COLUMN(B2),2)=1

格式:設定淺綠色的背景色彩

Excel-自動產生間隔欄、間隔列不同背景色彩(MOD,ROW,COLUMN)

利用 COLUMN 函數取得儲存格的欄數,利用 MOD 函數判斷欄數是否奇數欄。

 

2. 產生間隔列不同背景色彩

Excel-自動產生間隔欄、間隔列不同背景色彩(MOD,ROW,COLUMN)

選取儲存格B2:K13,設定格式化的條件:

規則類型:使用公式來決定要格式化哪些儲存格

規則:=MOD(ROW(B2),2)=1

格式:設定淺綠色的背景色彩

利用 ROW 函數取得儲存格的列數,利用 MOD 函數判斷列數是否奇數列。

 

3. 產生間隔欄列不同背景色彩

Excel-自動產生間隔欄、間隔列不同背景色彩(MOD,ROW,COLUMN)

選取儲存格B2:K13,設定格式化的條件:

規則類型:使用公式來決定要格式化哪些儲存格

規則:=(MOD(COLUMN(B2),2)=1)+(MOD(ROW(B2),2)=1)

格式:設定淺綠色的背景色彩

公式中的『+』運算子,相當於執行邏輯 OR 運算。

4. 產生間隔欄列交會不同背景色彩

Excel-自動產生間隔欄、間隔列不同背景色彩(MOD,ROW,COLUMN)

選取儲存格B2:K13,設定格式化的條件:

規則類型:使用公式來決定要格式化哪些儲存格

規則:=(MOD(COLUMN(B2),2)=1)*(MOD(ROW(B2),2)=1)

格式:設定淺綠色的背景色彩

公式中的『*』運算子,相當於執行邏輯 AND 運算。

文章標籤

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

在 Excel 的工作表中有一個數值構成的資料清單,如何由些數值中找出出現次數最多者,並依由大至小排列?

在下圖中,出現最多的數字是 81,而其出數次數是 9。排列順序依次數由大至小排列。

Excel-由大至小列出數值清單中出現次數最多者(MODE)

 

【公式設計與解析】

選取儲存格A2:J18,按 Ctrl+F3 鍵,在名稱管理員中定義名稱:DATA。

儲存格L2:{=MODE(IF(COUNTIF(L$1:L1,DATA),"",DATA))}

儲存格M2:=COUNTIF(DATA,L2)

複製儲存格L2:M2,貼至儲存格L2:M18。

在公式中使用 MODE 函數,乃是因為 MODE 函數會傳回陣列或資料範圍中最常出現,或重複的值。

(1) COUNTIF(L$1:L1,DATA)

在陣列公式中計算在DATA儲存格範圍中和儲存格L1內容相符者的個數。

(2) IF(COUNTIF(L$1:L1,DATA),"",DATA)

COUNTIF(L$1:L1,DATA)如果傳回大於 0 的數,表示儲存格L1裡的數已計算過,所以傳回空字串;否則傳回 DATA 儲存格範圍的內容 .

(3) MODE(IF(COUNTIF(L$1:L1,DATA),"",DATA))

將第(2)式的結果代入 MODE 函數,就會找出個數最多者。

文章標籤

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

(網友提問)根據下圖左的 Excel 工作表中的資料清單,來找出物料碼以 EIM 為首者的各月訂單數量總和,該如何處理?

Excel-依多條件取出清單中符合的項目加總(SUMPRODUCT)

 

【公式設計與解析】

假設資料來源位於儲存格A1:C115。

選取儲存格A1:C115,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:交貨日期、物料、訂單數量。

儲存格G2:

=SUMPRODUCT((MONTH(交貨日期)=F3)*(LEFT(物料,3)="EIM")*訂單數量)

(1) 條件一:MONTH(交貨日期)=F3

SUMPRODUCT 函數中判斷「交貨日期」儲存格陣列中的月份(利用 MONTH 函數)是否和儲存格F3相同,傳回 TRUE/FALSE 陣列。

(2) 條件二:(LEFT(物料,3)="EIM"

SUMPRODUCT 函數中判斷「物料」儲存格陣列中的前 3 碼(利用 LEFT 函數)是否為「EIM」,傳回 TRUE/FALSE 陣列。

(3) (MONTH(交貨日期)=F3)*(LEFT(物料,3)="EIM")*訂單數量

SUMPRODUCT 函數中執行乘積和的運算,其中『*』運算子,相當於執行邏輯 AND 運算,運算過程會將 TRUE/FALSE 陣列轉換為 1/0陣列。

文章標籤

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

(網友提問)在 Excel 的工作表中有一組資料清單(參考下圖),如何列出清單中含有指定字元的資料?

例如:要找出資料中含有 T、N、R 的清單。

Excel-根據清單中列出含有指定字元的資料(OFFSET,SUBSUTITUTE)

 

【公式設計與解析】

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

儲存格C2:{=IFERROR(OFFSET($A$1,SMALL(IF(SUBSTITUTE(資料,C$1,"")<>
資料,ROW(資料),""),ROW(1:1))-1,0),"")}

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

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

(1) SUBSTITUTE(資料,C$1,"")

利用 SUBSTITUTE 函數將資料陣列中每個儲存格都以儲存格C1的內容用空白取代。

(2) IF(第(1)式<>資料,ROW(資料),"")

對資料陣列中的每個儲存格判斷第(1)式的傳回結果,如果取代後的結果和原來的內容不一樣,表示含有儲存格C1內容,則傳該儲存格的列號;否則傳回空字串(空白)。(ROW 函數可傳儲存格的列號)

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

當公式向下各列複製時,將第(2)式傳回的列號利用 SMALL 函數由小至大取出。

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

將第(3)式傳回的列號代入 OFFSET 函數取得由儲存格A1起始所對應的儲存格內容。

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

利用 IFERROR 函數將第(3)可能傳回的錯誤訊轉換為空字串(空白)。

文章標籤

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

當多人編輯一份文件進行協作時,除了輸入資料這個基本工作,過程中的對話機制是很重要的。如何能快速而迶效的傳遞訊息,讓編輯工作不會陷於反覆不斷的修正,而且能正確讓團隊成員知悉各自的看法,相對更是重要的歷程。

以下用Google文件的編輯為例,來稍微描述Google在共享文件編輯時的對話機制。本例假設這個文件有二個使用者共用:A使用者:文件原始擁有者/B使用者:文件共用者。

1. 使用建議操作

B使用者在編輯文件時啟用「建議操作」,並輸入一些建議。

使用Google文件的對話功能進行團隊共筆

B使用者所輸入的文字會以不同色彩標示之外,視窗右側會顯示編輯的內容和編輯的時間。

使用Google文件的對話功能進行團隊共筆

A使用者會看到B使用者的操作建議,並且可以針對B使用者的建議文字加以回覆。(二個使用者不一定都要在線,何時回覆都可以。)

使用Google文件的對話功能進行團隊共筆

A使用者可以可以選取「工具/查看修訂建議」:

使用Google文件的對話功能進行團隊共筆

可以選取要接受或是拒絶相關的建議,除了全部一次處理之外,也可以使用視窗右側的每一個編輯建議,逐一給予接受建議(V)或是拒絶建議(X)。

使用Google文件的對話功能進行團隊共筆

 

2. 使用註解功能

先前所提的「建議操作」也是一種註解,使用者也可以以註解方式來對和共用的使用者對話。先將插入點移至要加上註解的位置,點選右上角的「註解」,再點選「+註解」以新增註解。

使用Google文件的對話功能進行團隊共筆

例如:A使用者輸入註解內容後,點選「留言」。

使用Google文件的對話功能進行團隊共筆

B使用者看到這個註解後,可以加以回覆或是點選「解決」,若點選了「解決」,則會將討論標示為已解決並予以隱藏。

使用Google文件的對話功能進行團隊共筆

B使用者也可以使用插入註解的方式,和A使用者對話。

使用Google文件的對話功能進行團隊共筆

共用文件的每位使用者也可以將一個註解取得超連結,並傳送給其他使用者,指定該使用者來處理這個註解。以分派工作的方式讓團隊運作。

使用Google文件的對話功能進行團隊共筆

每個使用者可以在編輯註解時,使用「@」再加上使用者的ID來指定給某使用者,輸入註解後勾選「指派給…」,再點選「指派」,該使用者會收到一個通訊訊息。

使用Google文件的對話功能進行團隊共筆

使用Google文件的對話功能進行團隊共筆

 

3. 使用即時通訊

如果共用文件的使用者同時已在線上(看是否出線頭貼來判斷),可以透過「訊息」功能來即時傳遞即時訊息,互相對話討論。但是所有的討論過程訊息並不會儲存在文件中,一旦離線或是關閉文件,訊息也會隨之消失。

使用Google文件的對話功能進行團隊共筆

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

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼