贊助廠商

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

搜尋本部落格文章資料

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

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

網友提問:在 Excel 中會使用 Find 函數和 Search 函數來查詢儲存格中的內容是否包含指定的內容。參考下圖,當我要找尋「週邊裝置」時,會連「電腦-週邊裝置」、「週邊裝置-鍵盤」及「週邊裝置」等都會被找到。

如果只想找到完全符合相同字串者,該如何處理?

Excel-找尋完全相符字串的儲存格(FIND,SEARCH,SUBSTITUTE)

 

【公式設計與解析】

(1) 使用 SEARCH 函數

儲存格B1:=IF(ISERR(SEARCH(B$1,$A2)),"","V")

使用 SEARCH 函數會傳回相符字串的位置,所以只要儲存格內含有該字串,即會傳回一個數值。所以無法判定儲存格內容是否和要找的字串「完全相符」。

(2) 使用 FIND 函數

儲存格C1:=IF(ISERR(FIND(C$1,$A2)),"","V")

使用 FIND 函數會傳回相符字串的位置,所以只要儲存格內含有該字串,即會傳回一個數值。所以無法判定儲存格內容是否和要找的字串「完全相符」。

(3) 使用 SUBSTITUTE 函數

儲存格D1:=IF(SUBSTITUTE($A2,D$1,"")="","V","")

使用 SUBSTITUTE 函數將儲存格內容以搜尋的字串置換為空字串,若傳回空字串,則代表該儲存格內容完全相符要找尋的字串。

文章標籤

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

網友提問:

在 Excel 中如何將日期「2018/01/01」轉換為「01月01日(一)」,該如何處理?

Excel-在一個儲存格顯示多個日期格式(TEXT)

 

【公式設計與解析】

儲存格C2:

=TEXT(A2,"mm月dd日;@")&"("&RIGHT(TEXT(A2,"[$-zh-TW]aaaa;@"),1)&")"

(1) TEXT(A2,"mm月dd日;@")

將儲存格A2內容的日期轉換為月(2碼)和日(2碼)的格式。

例如:2018/1/1 轉為01月01日。

(2) TEXT(A2,"[$-zh-TW]aaaa;@")

儲存格A2內容的日期轉換為「星期三」格式。

(3) RIGHT(TEXT(A2,"[$-zh-TW]aaaa;@"),1)

利用 RIGHT 函數取出「星期三」格式的最右一個字元。

最後再以「&」串接字串,並加上「()」。

 

【延伸說明】

公式中「mm月dd日;@」和「[$-zh-TW]aaaa;@」等參數不用刻意去記憶。

如果想要取用,參考以下方式:

當你設定日期的「星期三」格式時:

Excel-在一個儲存格顯示多個日期格式(TEXT)

切換至自訂時,其會顯示對應的格式:

Excel-在一個儲存格顯示多個日期格式(TEXT)

複製自訂的內容,放至 TEXT 函數即可得到相同的格式結果。

文章標籤

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

引用網友提問的問題,在處理公式時要注意的細節:

原來公式:{=MAX(IF((買賣權=C3+到期月份=B3),未沖銷,))}

結果不是網友所要的正確答案,想要知道問題何在?

其中的「+」運算子,是要執行邏輯 OR 運算,但也是要執行加法運算。

其實公式不會回應公式錯誤,因為公式語法沒有錯誤!但是公式為何出現錯誤的結果?

修改公式:{=MAX(IF(((買賣權=C3)+(到期月份=B3)),未沖銷,))}

(注意公式中多加上了括號)

原因是,在Excel在執行公式時「算術運算」會優先於「關係運算」。

所以將公式的兩個判斷條件加上括號,以改變運算的優先順序。

檢視微軟提供的說明:計算運算子以及 Excel 中的運算順序

網址:https://support.office.com/zh-tw/article/計算運算子以及-excel-中的運算順序-48be406d-4975-4d31-b2b8-7af9e0e2878a

Excel-注意公式中運算符號的優先順序

文章標籤

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

網友問到:在 Excel 的工作表中(如下圖),如果有二個下拉式選單,希望第二選單不要重現第一個選單已選取的項目,該如何處理?

例如,在選單A中已選取「五月」,則選單B中沒有列出「五月」供選取。

Excel-第二個選單不出現第一個選單已被選的內容(OFFSET,ROW)

【公式設計與解析】

要製作選單效果可以透過「資料驗證」功能,例如選單A設定:

儲存格內允許:清單

來源:=$D$2:$D$13

image

如果要做到選單B不能包含選單A中已被選取的項目,則必須建立另一個選單的內容。

選取儲存格D1:D13,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:選單A。

儲存格E2:

{=OFFSET($D$1,SMALL(IF(選單A<>$A$2,ROW(選單A),""),ROW(1:1))-1,0)}

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

(1) IF(選單A<>$A$2,ROW(選單A),"")

在陣列公式中判斷儲存格A2內容是否和選單A陣列相同,若是則傳回儲存格列號,否則傳回空字串。

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

利用 SMALL 函數由小至大依序取出列號。

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

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

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

在儲存格B2中以儲存格E2:E12,建立下拉式清單。

 

 

文章標籤

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

網友提問:在 Excel 中,如何根據訂貨數量,加以分裝成箱、打、罐(1箱=12打、1打=12罐),該如何處理?

這個問題是初學者練習 INT 函數和 MOD 函數的好例子。

Excel-訂貨數量分裝箱/打/罐(INT,MOD)

 

【公式設計】

儲存格C2:=INT(A2/12/12)

儲存格D2:=INT((A2-C2*12*12)/12)

儲存格E2:=MOD(A2,12)

複製儲存格C2:E2,貼至儲存格C2:E16。

 

【函數說明】

★ INT 函數

作用:將數字無條件捨位至最接近的整數

語法:Int( number )

Number:無條件捨位至整數的實數

MOD 函數

作用:傳回兩數相除後的餘數

語法:MOD(number, divisor)

Number:被除數

Divisor:除數

註:MOD(n, d) = n - d*INT(n/d)

文章標籤

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

網友問到:在 Excel 中,如果有一個名字組成的清單,如何藉由輸入特定文字而列出含有該文字的清單?

參考下圖,在下拉式清單中選取不同的文字,即可列出含有該文字的姓名。

Excel-列出資料中含有指定文字的清單(SUBSTITUTE,OFFSET)

 

【公式設計與解析】

相關說明可以參考以往所寫的文章:

Excel-列出含有特定文字的清單(FIND,ROW,SMALL,陣列公式)

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

Excel-列出含有特定文字的清單(FIND,ROW,SMALL,陣列公式)2

Excel-列出含有某字的資料(陣列)

首先,選取儲存格A1:B200(名字清單的範圍),按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:編號、姓名。

Excel-列出資料中含有指定文字的清單(SUBSTITUTE,OFFSET)

接著,輸入公式:

儲存格F2:{=IFERROR(OFFSET($A$1,SMALL(IF(SUBSTITUTE(姓名,$D$2,"")<>
姓名,ROW(編號)-1,""),ROW(1:1)),0),"")}

儲存格G2:{=IFERROR(VLOOKUP(F2,資料,2,FALSE),"")}

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

複製儲存格F2:G2,貼至儲存格F2:G30。

其中公式:SUBSTITUTE(姓名,$D$2,"")<>姓名

乃在姓名陣列中,判斷如果將姓名以儲存格D2的內容置換為空字串(相當於刪掉該文字),如果結果和原來姓名不相同,則表示該姓名含有該文字。

如果選取不同的字元,清單會隨之改變列出的內容:

Excel-列出資料中含有指定文字的清單(SUBSTITUTE,OFFSET)

該公式不限查詢的字元數:

Excel-列出資料中含有指定文字的清單(SUBSTITUTE,OFFSET)

文章標籤

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

有老師問到在 Excel 中關於成績統計分析的問題,成績表如下圖左:

1. 由成績表中經由選取不同科目,可以顯示不同統計表。

2. 統計表中列出不重覆的成績。

3. 依個別不同成績列出該成績的人數。

4. 依不同成績由小至大列出座號。

Excel-動態學生成績表摘要(OFFSET,MATCH,INDIRECT)

 

【公式設計與解析】

如果要能動態選取科目而顯示不同的統計表,需要藉助下拉式選單和儲存格範圍的名稱定義。

Excel-動態學生成績表摘要(OFFSET,MATCH,INDIRECT)

Excel-動態學生成績表摘要(OFFSET,MATCH,INDIRECT)

首先,在儲存格K1中設定資料驗證:

資料驗證準則:清單

來源:=$C$2:$E$2

Excel-動態學生成績表摘要(OFFSET,MATCH,INDIRECT)

接著,選取儲存格A2:E12,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:座號、姓名、國文、數學、英文。

Excel-動態學生成績表摘要(OFFSET,MATCH,INDIRECT)

本例使用輔助欄位減少公式複雜度。新增國文、數學、英文三個輔助欄位(參考下圖)。

儲存格G3:

=IFERROR(OFFSET(C$3,IF(COUNTIF(C$3:C3,C3)>1,"",ROW(C3))-3,0),"")

複製儲存格G3,貼至儲存格G3:I12。

Excel-動態學生成績表摘要(OFFSET,MATCH,INDIRECT)

接著,輸入統計區的公式。

1. 列出不重覆的成績

儲存格K3:=IFERROR(LARGE(OFFSET($G$3,0,MATCH($K$1,$C$2:$E$2,0)-1,
COUNTA(座號),1),ROW(1:1)),"")

複製儲存格K3,貼至儲存格K3:K12。

 

2. 統計各個成績的人數

儲存格L3:=IF(K3<>"",COUNTIF(INDIRECT($K$1),K3),"")

複製儲存格L3,貼至儲存格L3:L12。

 

3. 由小至大列出各個成績的座號

儲存格M3:{=IFERROR(SMALL(IF(INDIRECT($K$1)=$K3,ROW(座號),""),
COLUMN(A:A))-2,"")}

複製儲存格M3,貼至儲存格M3:R12。

Excel-動態學生成績表摘要(OFFSET,MATCH,INDIRECT)

使用時將輔助欄位加以隱藏,只要選取科目即可動態顯示不同的統計表。

Excel-動態學生成績表摘要(OFFSET,MATCH,INDIRECT)

文章標籤

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

網友想要製作一個 Excel 的動態圖表(如下圖),圖表的內容是依指定的範圍來顯示內容。也就是要將一個靜態圖表轉換為動態圖表,該如何處理?

Excel-依指定範圍顯示圖表內容(建立動態圖表)

Excel-依指定範圍顯示圖表內容(建立動態圖表)

 

【公式設計與解析】

先觀察:

如果你將數據製成一個統計圖,當你選取圖表中的數列時,會看到其對應的公式如下圖。

Excel-依指定範圍顯示圖表內容(建立動態圖表)

本例中的公式:=SERIES(,'DATA '!$A$2:$B$8,'DATA '!$C$2:$C$8,1)

因此,如果要做一個動態圖表,則必須改變其中的參數,使其固定的位址改成動態位址。

所以,先建立二個動態範圍,利用 OFFSET 函數來建立名稱範圍。

view1:=OFFSET(DATA!$B$1,DATA!$F$1,0,DATA!$H$1-DATA!$F$1+1,1)

view2:=OFFSET(DATA!$C$1,DATA!$F$1,0,DATA!$H$1-DATA!$F$1+1,1)

此處要提醒,其中使用的位址必須要以完整的位址且要使用絶對參照方式。

例如:儲存格F1,要以 DATA!$F$1 表示,其中 DATA 是工作表名稱。

Excel-依指定範圍顯示圖表內容(建立動態圖表)

接著,點選圖表中的數列,在公式中將參數改成:(活頁簿1 是目前的活頁簿檔案名稱)

=SERIES(,活頁簿1.xlsx!view1,活頁簿1.xlsx!view2,1)

Excel-依指定範圍顯示圖表內容(建立動態圖表)

完成後,當你改變儲存格F1和儲存格H1的內容時,圖表也會隨之變動。

Excel-依指定範圍顯示圖表內容(建立動態圖表)

 

【延伸學習】

如果想要將儲存A1:C23的內容依儲存格F1和儲存格H1而標示成紫色,該如何處理?

先選儲存格$A$2:$C$23,然後設定格式化的條件:

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

編輯規則:=(ROW(A2)>=$F$1+1)*(ROW(A2)<=$H$1+1)

格式設定:字型色彩設為「紫色」

Excel-依指定範圍顯示圖表內容(建立動態圖表)

Excel-依指定範圍顯示圖表內容(建立動態圖表)

如果你覺得輸入數字太麻煩,你可以使用微調按鈕來輔助輸入數字。

在「開發人員」功能表中選取「表單控制項/微調按鈕」,並在工作表插入。

Excel-依指定範圍顯示圖表內容(建立動態圖表)

設定控制項格式:(指定儲存格連結位址)

Excel-依指定範圍顯示圖表內容(建立動態圖表)

如此,便可以使用微調按鈕來動態控制圖表顯示內容。

Excel-依指定範圍顯示圖表內容(建立動態圖表)

文章標籤

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

網友問到:(如下圖)如何標示兩個員工工作時間重疊區域?

在 Excel 的工作表中記錄了 A 和 B 員工的起迄工作時間,如何依據這個時間繪出二人重疊的時間區域?

Excel-根據兩個員工的起迄工作時間繪出兩人重疊的時間區域

 

【公式設計與解析】

1.產生每隔30分鐘的時間數列

儲存格A4:=TIME(8+INT((ROW(1:1)-1)/2),MOD((ROW(1:1)-1),2)*30,0)

複製儲存格A4,貼至儲存格A4:A40。

產生 08:00 AM→08:30 AM→09:00 AM→09:30 AM→10:00 AM→... 數列。

 

2. 產生 A 和 B 工作時間範圍圖示

儲存格B4:=($A4>=B$2)*($A4<B$3)

產生時間在工作時間內者產生「1」,否則產生「0」。

其中「*」運算子的作用相當於執行邏輯 AND 運算。

複製儲存格B4,貼至儲存格B4:B40。

再設定格式化的條件:將「>=1」者以綠燈圖示表示,其餘不顯示圖示。

Excel-根據兩個員工的起迄工作時間繪出兩人重疊的時間區域

接著,複製儲存格B4,貼至儲存格C4:C40。

其中,儲存格C4:=($A4>=C$2)*($A4<C$3),原理同上。

 

3. 產生 A 和 B 工作時間範圍重疊圖示

儲存格D4:=($A4>=B$2)*($A4<B$3)*($A4>=C$2)*($A4<C$3)

複製儲存格D4,貼至儲存格D4:D40。

再設定格式化的條件:將「>=1」者以紅燈圖示表示,其餘不顯示圖示。

Excel-根據兩個員工的起迄工作時間繪出兩人重疊的時間區域

 

文章標籤

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

網友問到:在 Excel 的工作表中記錄了每一天所出現的多個號碼(如下圖),如果想要計算星期一至星期日裡每個數字分別出現的次數,該如何處理?。

image

 

【公式設計與解析】

儲存格K2:=SUMPRODUCT((WEEKDAY($A$2:$A$365,2)=COLUMN(A:A))*($C$2:$H$365=$J2))

複製儲存格K2,貼至儲存格Q50。

在 SUMPRODUCT 函數,利用以下二個條件判斷完全相符者的個數。

(1) 條件一:WEEKDAY($A$2:$A$365,2)=COLUMN(A:A)

利用 WEEKDAY 函數配合參數「2」,判斷傳回值是否為「1 (=COLUMN(A:A))」。

(在此,傳回值 1 表示星期一。)

image

當公式向右複製,COLUMN(A:A)=1→COLUMN(B:B)=2→COLUMN(C:C)=3→…。

 

(2) 條件二:$C$2:$H$365=$J2

判斷儲存格陣列中和儲存格J2內容相同者。

利用以上二個條件,分別對應欄標題和列標題交叉的內容。

image

文章標籤

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

學生問到:利用 Google 表單讓人填答後,會自動產生回覆結果的統計圖表(如下圖),如何能取用這些已經製作好的統計圖?

做法很簡單,參考以下的做法:

先把滑鼠移至圖表上,視窗右側會出現「複製」圖示,點選這個圖示。(進行複製工作)

將Google表單產生的統計圖表放到Word文件中使用

接著,在網址列輸入:doc.new,用以產生一個新的 Google 文件。

在文件中按一下 Ctrl+V 鍵,會貼上剛剛複製的圖表。

將Google表單產生的統計圖表放到Word文件中使用

再選取「檔案/下載格式/Microsoft Word(.docx)」,以下載圖表所在的文件。

將Google表單產生的統計圖表放到Word文件中使用

打開下載好的 Word 文件,即可看到圖表置於 Word 中了。(不過,放到 Word 文件中的統計圖是一張一張的圖片,無法進一步進行編輯。)

將Google表單產生的統計圖表放到Word文件中使用

文章標籤

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

工作、教學時使用YouTube播放影片,有時會需要用到子母畫面的方式。例如播放影片時想要同時顯示講義、教材。目前 Google Chrome 70版已有內置了播放YouTube的子母畫面功能了。

當你播放一個YouTube影時:

利用Chrome顯示YouTube的子母畫面

在影片有一下右鍵,會顯示如下的選單:

利用Chrome顯示YouTube的子母畫面

連續再按一下右鍵,則會出現另一個選單,其中就有「子母畫面」:

利用Chrome顯示YouTube的子母畫面

使用子母畫面時,當你在做別的工作時,這個子畫面會置於視窗的最上層。子畫面可以改變大小和任意移動位置。

利用Chrome顯示YouTube的子母畫面

同時間,只能有一個YouTube影片被設為子畫面。而Chrome的標籤會顯示那個標籤正在播放子母畫面的內容。

利用Chrome顯示YouTube的子母畫面

文章標籤

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

在 Outlook 中收到一封郵件,裡頭有大量的收件人 Email 地址,如果想要將其備份至 Excel 的工作表中以方便保存,該如何處理?(參考下圖)

Excel-如何將Outlook收件者Email位址轉換至試算表

參考以下步驟操作:

1. 開啟 Outlook 的 Email 郵件,複製所有的收件人。

Excel-如何將Outlook收件者Email位址轉換至試算表

2. 將其貼至 Word 文件中。

Excel-如何將Outlook收件者Email位址轉換至試算表

3. 將「;」取代為「^p」,即將「;」符號置換為跳行(Enter)。

Excel-如何將Outlook收件者Email位址轉換至試算表

結果如下:

Excel-如何將Outlook收件者Email位址轉換至試算表

4. 將 Word 內容複製到 Excel 中。

5. 利用資料部析工具,將 Email 位址之前的文字置於一欄,Email 位址置於一欄。

Excel-如何將Outlook收件者Email位址轉換至試算表

6. 利用取代功能,將「>」置換為空字串。

Excel-如何將Outlook收件者Email位址轉換至試算表

結果如下圖,再利用公式:

儲存格C1:=HYPERLINK("mailto:"&B1,A1)

複製儲存格C1,往下各列貼上,產生一個郵件超連結清單。點選任一個超連結,可以直接進入預設的 Email 軟體進行郵件編輯。

Excel-如何將Outlook收件者Email位址轉換至試算表

文章標籤

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

網友問到:在 Excel 中設定數值格式時,如何顯示繁體中文的數值和簡體中文的國字數值?

Excel-顯示繁體中文和簡體中文的國字數值

在儲存格格式的數值設定中,自訂:

顯示繁體中文國字數值:

(1) [DBNum2][$-zh-TW]G/通用格式、(2) [DBNum2][$-404]G/通用格式

顯示簡體中文國字數值:

(1) [DBNum2][$-zh-CN]G/通用格式、(2)[DBNum2][$-804]G/通用格式

Excel-顯示繁體中文和簡體中文的國字數值

如果在數值之後要加上「元整」,則在儲存格格式的數值設定中,自訂:

顯示繁體中文國字數值:

[DBNum2][$-zh-TW]G/通用格式"元整"、[DBNum2][$-404]G/通用格式"元整"

顯示簡體中文國字數值:

[DBNum2][$-zh-CN]G/通用格式"元整"、[DBNum2][$-804]G/通用格式"元整"

Excel-顯示繁體中文和簡體中文的國字數值

文章標籤

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

最近在研究自己通訊錄上的連絡人時,好奇想要知道每個姓氏的人數份佈比率,所以利用樞紐分析表工具和公式分別處理一次,卻得到不同結果,進一步了解找出了自已在公式設計的錯誤之處。(參考下圖)

Excel-計算姓名清單中每個姓氏的人數(SUMPRODUCT)

在下圖中,A欄是所有連絡人的姓名,先利用公式取出其姓。

儲存格B2:=IF(LEN(A2)=4,LEFT(A2,2),LEFT(A2,1))

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

取用B欄內容執行樞紐分析表操作,得到以下的結果:

Excel-計算姓名清單中每個姓氏的人數(SUMPRODUCT)

選取A欄的全部資料,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:姓名。

可以取用樞紐分析表會產生的不重覆的姓,

設計公式,儲存格D2:

=IF(LEN(C2)=2,SUMPRODUCT((LEFT(姓名,2)=C2)*1),SUMPRODUCT((
LEFT(姓名,1)=C2)*1))

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

(1) SUMPRODUCT((LEFT(姓名,2)=C2)*1)

如果C欄中的「姓」的字數為2,則找出姓名陣列中前 2 個字和儲存格C2相同的數量。

(2) SUMPRODUCT((LEFT(姓名,1)=C2)*1)

如果C欄中的「姓」的字數為1,則找出姓名陣列中前 1 個字和儲存格C2相同的數量。

Excel-計算姓名清單中每個姓氏的人數(SUMPRODUCT)

以上結果初看之下好像是對的公式,經過思考之後發現我犯了錯:複姓者(例如:范姜、歐陽)的第 1 個恰好也有單姓者(例如:范、歐)相同的字,沒考慮到這個因素。

修改公式,儲存格E2:

=IF(LEN(C2)=2,SUMPRODUCT((LEFT(姓名,2)=C2)*1),SUMPRODUCT((
LEFT(姓名,1)=C2)*1)-SUMPRODUCT((LEFT(姓名,1)=C2)*(LEN(姓名)=4)))

將公式:

SUMPRODUCT((LEFT(姓名,1)=C2)*1)

改為:

SUMPRODUCT((LEFT(姓名,1)=C2)*(LEN(姓名)=4))

即在公式中多加了一個條件,姓名為 4 個字者:(LEN(姓名)=4)

如此,就不會重覆計算了。

文章標籤

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

今天同仁問了一個實務面上常會踫到的問題:當在 Word 中製作合併列印文件時,資料來源是放在 Excel 的工作表中(如下圖),當資料合併至 Word 文件中的時候,原先是文字的班別,卻顯示為 0,該如何解決?

Word+Excel-解決合併列印文件時文字變成0的問題

在 Excel 中先建立一個資料表當為資料來源:

Word+Excel-解決合併列印文件時文字變成0的問題

當你在 Word 中製作合併列印檔時使用這個資料檔:

Word+Excel-解決合併列印文件時文字變成0的問題

原先是「子一甲、資一甲、資一乙」等的文字卻都顯示為 0:

Word+Excel-解決合併列印文件時文字變成0的問題

這是因為資料表中的班別欄位,被認定為數字格式,所以文字全都變成 0 了。

處理方式:只要將所有欄位的數值格式全都設定為「文字」即可。

Word+Excel-解決合併列印文件時文字變成0的問題

重新製作這個合併列印文件檔,結果就會是正確的:

Word+Excel-解決合併列印文件時文字變成0的問題

文章標籤

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

(研習範例)參考下圖,同仁常問到的問題:在 Excel 中使用 VLOOKUP 函數,看起來公式是對的,但是查詢結果卻出錯,到底是那裡有問題?這個情形常發生在用學號、代號、座號等數字在做查詢時。

觀察下圖,在 Excel 中預設在儲存格中輸入文字會靠左對齊、輸入數值會對右對齊。在查表的資料裡雖然看來都是數字,但卻是文字格式。而查詢資料的數值是數字格式,所以查詢結果是錯誤訊息(因為查不到任何相符結果)。

為何這查表資料的數字會靠左對齊?通常是因為這些資料在系統的資料庫中的欄位屬性原始就是被設定為文字,所以匯出時就會是文字格式。

Excel-解決使用VLOOKUP函數查詢數字發生的錯誤

如何才能得到想要的結果呢?你可能會這樣做:

將儲存格F2的數值格式也修改為「文字」。

Excel-解決使用VLOOKUP函數查詢數字發生的錯誤

目前看來結果還是錯的(觀察儲存格數字已自動靠左對齊,所以已將格式設定為文字。)

Excel-解決使用VLOOKUP函數查詢數字發生的錯誤

此時,你只要在這個狀態下,重新輸入一次要查詢的內容(本例再key一次71),結果就會正確。

Excel-解決使用VLOOKUP函數查詢數字發生的錯誤

如果你有大量資料要查表,上述的動作將會過多操作,還是改用修正公式的方法:

修改公式→儲存格F4:=VLOOKUP(F2&"",A2:D19,4,FALSE)

關鍵在於將儲存格F2再串接一個空字串「""」,如此,儲存格F2的內容就會被轉成文字了。然後再進行複製,就能讓大量的查表公式得到正確的結果。(實現了用數值查詢文字)

Excel-解決使用VLOOKUP函數查詢數字發生的錯誤

文章標籤

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

(研習範例)參考下圖,學校老師時常建立一些試題,其中含有題目領域和題型等欄位,如何能快速得知各領域、各題型分配的題數?

Excel-試題領域和題型的數量分析(SUMPRODUCT)

首先,選取 Word 中已出好題目的領域和題型的欄位,並且複製到 Excel 中。再透過 Excel 公式來求取各領域、各題型分配的題數。

Excel-試題領域和題型的數量分析(SUMPRODUCT)

參考下圖,在 Excel 中選取領域和題型欄位有資料的儲存格,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:領域、題型。

Excel-試題領域和題型的數量分析(SUMPRODUCT)

輸入公式,儲存格E2:=SUMPRODUCT((領域=$D2)*(題型=E$1))

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

你也可以試著使用樞紐分析表工具,手動操作得到相同結果。

Excel-試題領域和題型的數量分析(SUMPRODUCT)

文章標籤

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

教師節為了給學校同仁一個祝福,想要用同仁姓名出現的字組成一個 QR Code,讓大家掃描後可以看到其中的文字(如下圖)。

利用文字做成QR Code

參考以下的做法:

首先,搜集了同仁的姓名,然後用 Excel 取出每一個字(利用公式或是資料剖析工具),然後用樞紐分析表工具,產生了不重覆的文字。

參考:Excel-手動產生不重覆項目(樞紐分析表)

再將每一個字產生一個圖檔:

參考這篇文章:PowerPoint+Word-將100個字製作為100個圖檔

接著,到網站產生這個 QR Code,並下載 QR Code 圖檔:

利用文字做成QR Code

接著,放大這個圖檔:

利用文字做成QR Code

並且,觀察 QR Code 中每一個小方塊的大小(例如:54X54)。

接著,將已經產生的文字圖檔,將每個圖檔改變大小至接近小方塊的大小。

例如:我用學生常用的 PhotoImpact 軟體,開啟 QR Code 圖檔,再手動將多個文字圖檔,隨機放至每一個小方塊相同位置。

利用文字做成QR Code

最後完成這個圖檔:(縮小一些比較好掃描)

利用文字做成QR Code

 

 

文章標籤

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

如果手邊有 100 個字,想要將每一個字變成一個圖檔(100個圖檔),該如何處理?

PowerPoint+Word-將100個字製作為100個圖檔

試試以下我的做法:

先在 Word 文件中準備好這 100 個字:

PowerPoint+Word-將100個字製作為100個圖檔

選取所有文字,並且在[段落]設定中將大網階層設定為:階層1。並且將此文件存檔。

PowerPoint+Word-將100個字製作為100個圖檔

新增一個 PowerPoint 簡報,並且設定投影片大小:相同的寬度和高度(例:4公分)。

PowerPoint+Word-將100個字製作為100個圖檔

接著,在新增投影片選項中選取:從大綱插入投影片。

PowerPoint+Word-將100個字製作為100個圖檔

選取前面所儲存的文件檔案,插入後如下圖:

PowerPoint+Word-將100個字製作為100個圖檔

切換至投影片母片,選取「標題及母片」的版面配置,再選取「標題」物件。

PowerPoint+Word-將100個字製作為100個圖檔

對標題樣式設定:字型、字型大小、水平置中對齊、垂直對齊、段落距離等,並且改變圖文框的大小,使其一個字正好是在投影片的正中央。(參考下圖)

PowerPoint+Word-將100個字製作為100個圖檔

如此,每張投影片恰好顯示一個字:

PowerPoint+Word-將100個字製作為100個圖檔

瀏覽投影片:

PowerPoint+Word-將100個字製作為100個圖檔

將這個簡報另存新檔為某一種圖片格式:(本例選取PNG)

PowerPoint+Word-將100個字製作為100個圖檔

再選取:所有投影片。

PowerPoint+Word-將100個字製作為100個圖檔

檢視檔案總管,100 個字的圖檔已經做好。

PowerPoint+Word-將100個字製作為100個圖檔

文章標籤

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

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼