贊助廠商

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

搜尋本部落格文章資料

目前日期文章:201608 (32)

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

網友問到一個 Excel 的問題,是關於計算累進的乘積和。參考下圖,其中有一個數值區間和比重,如果在儲存格H2輸入一個數值,例如:750,而此數可以分解為:

750=100+100+200+200+100+50

再將每個區間的數量乘以比重:

100*6%+100*6%+200*3%+200*2%+100*5%+50*1%=27.5

輸入:750→輸出:27.5。

Excel-計算累進的乘積和(SUMPRODUCT,OFFSET)

【公式設計與解析】

1. 本例需要二個輔助欄位。

第一個欄位(F欄),內容是每個區間的範圍量。

第二個欄位(G欄),找出那些區間要被併入計算,給予「V」記號。(不包含最後一個區間)

儲存格G2:=IF($I$2>SUM($F$2:F2),"V","")

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

 

2. 計算輸出結果:

儲存格I6=SUMPRODUCT(F2:F13*D2:D13*(G2:G13="V"))+(I2-SUMPRODUCT
(F2:F13*(G2:G13="V")))*OFFSET(D2,COUNTIF(G2:G13,"V"),0)

(1) SUMPRODUCT(F2:F13*D2:D13*(G2:G13="V"))

計算有「記號」的範圍數值和比重的乘積和。

(2) I2-SUMPRODUCT(F2:F13*(G2:G13="V"))

計算最後一個區間的數值。(本例中為 700~800 之間的數值為 50)

(3) OFFSET(D2,COUNTIF(G2:G13,"V"),0)

找出最後一個區間的比重。

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

延續前一篇文章:Excel-郵遞區號查詢(VLOOKUP,OFFSET,SUMPRODUCT)

如下圖,若在儲存格E3中選取某一個縣市(例如:新北市),如何能自動列出該縣市的各區名稱?本篇要改良前一篇的公式。

Excel-郵遞區號查詢(VLOOKUP,OFFSET,SUMPRODUCT)2

選取儲存格A2:C63,按 Ctrl+Shfit+F3 鍵,勾選「頂端列」,定義名稱:

郵遞區號、縣市、區。

再來,要設定一個名稱:完整區名。並設定其參照到:

=OFFSET($C$2,MATCH($E$3,縣市,0),0,SUMPRODUCT(1*(縣市=$E$3)),1)

Excel-郵遞區號查詢(VLOOKUP,OFFSET,SUMPRODUCT)2

(1) SUMPRODUCT(1*(縣市=$E$3))

計算在縣市資料中和儲存格E3相同者的數量。

(2) MATCH($E$3,縣市,0)

找出儲存格E3在縣市資料中位於第幾個。

(3) OFFSET($C$2,第(2)式,0,第(1)式,1)

利用第(1)式和第(2)式找出,某一個縣市的各區儲存格範圍。

最後,在儲存格F3中利用資料驗證設定成下拉式清單:

儲存格內允許:清單

來源:=完整區名

Excel-郵遞區號查詢(VLOOKUP,OFFSET,SUMPRODUCT)2

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

網友問到如何在 Excel 中查詢郵遞區號的問題。首先下載郵局提供的郵遞區號對照表:

http://www.post.gov.tw/post/internet/Download/default.jsp?ID=22

稍加整理後,即可用於查詢。(如下圖的A,B,C欄)

Excel-郵遞區號查詢(VLOOKUP,OFFSET,SUMPRODUCT)

 

【公式設計與解析】

1. 由郵遞區號查詢:縣市和區

儲存格F3:=VLOOKUP(E3,A3:C370,2,FALSE)

儲存格G3:=VLOOKUP(E3,A3:C370,3,FALSE)

當在儲存格E3輸入一個郵遞區號時,即可對照顯示縣市和區。

 

2. 由縣市和區查詢:郵遞區號

這個範例,要使用下拉式清單來選取縣市:

Excel-郵遞區號查詢(VLOOKUP,OFFSET,SUMPRODUCT)

再根據縣市,在另一個下拉式清單中選取區:

Excel-郵遞區號查詢(VLOOKUP,OFFSET,SUMPRODUCT)

參考以下步驟:

1. 選取儲存格A2:C63,按 Ctrl+Shfit+F3 鍵,勾選「頂端列」,定義名稱:

郵遞區號、縣市、區。

2. 在儲存格I1:I24中置入縣市的清單。

3. 選取儲存格I1:I24,定義名稱:縣市名。

4. 設定儲存格E7的資料驗證。儲存格內允許:清單;來源:=縣市名。

image

5. 輸入公式,儲存格K2:

{=OFFSET($C$3,SMALL(IF(縣市=$E$7,ROW(區),999)-3,ROW(1:1)),0)}

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

複製儲存格K2,貼至儲存格K2:K34。

6. 將儲存格K2:K34定義名稱:區名。

7. 設定儲存格F7的資料驗證。

定義名稱:完整區名。

其參照公式:=OFFSET($K$2,0,0,COUNTA(區名)-COUNT(區名),1)

image

8. 設定儲存格E7的資料驗證:

儲存格內允許:清單;來源:=完整區名。

image

9. 輸入公式,儲存格G7:

=OFFSET(A3,SUMPRODUCT((縣市=E7)*(區=F7)*ROW(郵遞區號))-3,0)

大功告成!

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

如下圖的 Excel 資料表,其中的表格內容是散亂的資料分佈。要如何取出表格有資料的部分重新排列?

下圖左為原始資料,下圖右為重排後的結果。

Excel-取出表格有資料的部分重新排列(OFFSET,VLOOKUP,COUNTIF)

【公式設計與解析】

1.

先建立一個輔助欄位,用以計算資料表中每一列有內容儲存格的數量。

儲存格N2:1

儲存格N3:=COUNTA($A$3:L3)+1

計算由儲存格N2起始的累計結果。

複製儲存格N3,貼至儲存格N3:N12。

2.

儲存格P3:=MATCH(ROW(1:1),$N$2:$N$12,1)

藉由輔助欄位的內容,依序列出有資料內容的儲存格在第幾列。

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

3.

儲存格Q3:{=OFFSET($A$2,P3,SMALL(IF(OFFSET($A$2,P3,0,1,12)<>"",
COLUMN(A:L),99),COUNTIF($P$3:P3,P3))-1,1,1)}

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

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


(1) OFFSET($A$2,P3,0,1,12)

根據儲存格P3的內容,取得同列資料的範圍,本例中,儲存格P3=1,資料範圍為儲存格A3:L3。


(2) IF(第(1)式<>"",COLUMN(A:L),99)

在陣列公式中,根據第(1)式傳回的資料範圍,若儲存格有內容,則傳回第幾欄,否則傳回99。(99是一個很大的數值,只要大於一列中的資料數量最大值(本例為12)即可)

COLUMN(A:L)代表 1~12。

本例傳回:{ 99,2,99,99,99,99,99,99,9,10,99,99 }


(3) SMALL(第(2)式,COUNTIF($P$3:P3,P3))

COUNTIF($P$3:P3,P3)用以傳回儲存格P3是同一列中的『第幾個』。(本例中有3個1,傳回 1, 2, 3。)

再利用 SMALL 函數取出『第幾個』的欄號,依序取得欄位 2, 9, 10。


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

最後,將欄號代入 OFFSET 函數取得對應的儲存格內容,依序取得 I, C, H。

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

網友根據下圖的 Excel 資料表,如何才能求得表一、表二、表三的結果。

Excel-雙條件計算個數(SUMPRODUCT)

 

【公式設計與解析】

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

條件一、水果、條件二。

(1) 儲存格G2:=SUMPRODUCT((條件一=$F2)*(條件二=G$1))

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

(2) 儲存格G8:=SUMPRODUCT((水果=$F8)*(條件二=G$1))

複製儲存格G8,貼至儲存格G8:H13。

(3) 儲存格G17:=SUMPRODUCT((水果=$F17)*(條件一=G$16))

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

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

有網友問到如下圖的 Excel 資料表中,如何計算各科的加權平均?

下圖中,每個科目都有一個加權和一個分數,要找出各科的加權平權。

Excel-計算加權平均(SUMPRODUCT)

 

【公式設計與解析】

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

科目、加權、分數。

儲存格F2:

=SUMPRODUCT((科目=E2)*加權*分數)/SUMPRODUCT((科目=E2)*加權)

複製儲存格F2,貼至儲存格F2:F6。

(1) SUMPRODUCT((科目=E2)*加權*分數)

計算和儲存格E2內容相符的科目之「加權*分數」的總和。

(2) SUMPRODUCT((科目=E2)*加權)

計算和儲存格E2內容相符的科目之「加權」的總和。

(3) 加權平均=第(1)式/第(2)式

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

有老師問到:在整理資料時,如果想要將某一磁碟之下的資料夾(包含許多子資料夾),複製到另一個磁碟中,但是只要資料夾,而不要資料夾中的檔案。該如何處理?

參考下圖:在D磁碟中有一個 A 資料夾,其中包含了幾個檔案和資料夾,而資料夾之下可能還有資料夾和檔案。現在要複製 A 資料夾和子資料夾到另一個磁碟中,而不包含其中的任何一個檔案。

如何複製資料夾而不複製資料夾中的檔案

你可以藉助『命令提示字元』工具!其位於附屬應用程式之中:

如何複製資料夾而不複製資料夾中的檔案

你也可以按一下 WinKey+R,然後輸入 cmd,再按 Enter 鍵。同樣可以進入『命令提示字元』視窗。

接著,你要輸入以下格式的指令:

xcopy c:\來源資料夾 d:\目的資料夾\ /t/e

例如,將 D 磁碟的 temp 資料夾複製到 G 磁碟之下,輸入以下指令:

xcopy d:\temp g:\temp\ /t/e

其中的參數意義如下:

/E:複製每個目錄及子目錄,包含空目錄。

/T:建立目錄結構,但不複製其中的檔案。不包括空目錄或子目錄。

/T /E:建立目錄結構,並包含空目錄及子目錄。

如果要關閉這個視窗,只要輸入 exit,再按 Enter 鍵。

如何複製資料夾而不複製資料夾中的檔案

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

在 Excel 中如果你想要將資料清單中重覆者挑出來並移除,最方便的方式如下:

先選取資料範圍。(本例為儲存格A1:B20),再使用[資料/資料工具]功能表中的「移除範圍」指令。

Excel-使用進階篩選功能來移除重覆的資料

Excel 會詢問要列入檢查重覆的欄位:

Excel-使用進階篩選功能來移除重覆的資料

移除重覆後,會顯示找到幾個重覆,而保留了幾個唯一值。

Excel-使用進階篩選功能來移除重覆的資料

網友問到:如果使用的 Excel 版本並沒有如上的功能,該如何處理?你可以藉助「進階篩選」的操作。

1. 選取資料範圍。(本例:儲存格A1:B20)

2. 選取[資料/排序與篩選]功能表中的「進階」。

3. 在[進階篩選]對話框中,如下圖的設定:

勾選:將篩選結果複製到其他地方

資料範圍:$A$1:$B$20

準則範圍:$D$1:$E$1 (已先建立儲存格D1:E1和原標題儲存格A1:B1一致)

複製到:$D$1:$E$1

最重要的是要勾選:不選重覆的記錄

Excel-使用進階篩選功能來移除重覆的資料

被篩選出來的結果都不會重覆。

Excel-使用進階篩選功能來移除重覆的資料

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

有同仁問到:在 Excel 中如果開啟 Google 表單下載之填答記錄(如下圖),當在執行『排序』時,其中有一個條件要根據時間戳記來排序,卻遇到了錯誤結果。

關於Google表單填答記錄時間戳記排序的問題

如下圖,當使用「篩選」功能中的『從A到Z排序』:

關於Google表單填答記錄時間戳記排序的問題

卻發生了如下圖的錯誤結果。(其中11:31:38卻小於8:05:23)

關於Google表單填答記錄時間戳記排序的問題

以上是何種原因造成的?

因為 Google 表單下載的時間戳記是『文字』格式來表示,而非 Excel 預設的『數值』。該如何解決?

請你仔細觀察,Goolge 表單下載的報表本身即是以日期時間由小到大排序,如果你只是為了排序目的,而沒有要使用日期時間的內容,則只要將時間戳記欄位依預設的順序給予一個流水號(1,2,3,...),即可用來排序了,完全不用文字轉換為數字的程序。

但是,如果你是要運用時間戳記欄位的內容,則必須將這個欄位的內容,由文字轉換為數值,才能往下處理。

你可以將時間戳記欄位利用「資料剖析」工具,以分隔符號「空格」,將其分成日期、上下午、時間三個欄位。

關於Google表單填答記錄時間戳記排序的問題

儲存格D2:=IF(B2="上午",A2+C2,A2+C2+1/2)

如果B欄顯示為「上午」,則只要將日期+時間,如果B欄顯示為「下午」,則還要再加上 1/2。(因為 Excel 將一天視為數值 1,所以 12 小時為 1/2。)

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

關於Google表單填答記錄時間戳記排序的問題

透過數值格式設定,自訂:yyyy/mm/dd hh:mm:ss,可以顯示較適合人閱讀的格式。

關於Google表單填答記錄時間戳記排序的問題

結果如下:

關於Google表單填答記錄時間戳記排序的問題

此時,再將日期時間排序,即可得到正確的結果:

關於Google表單填答記錄時間戳記排序的問題

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

有網友問到:最近排班時會遇到七休一的問題,如何在 Excel 中如果連續排到 7 天時即給予警示?

以下圖中的排班格式為例(排班的形式可能很多種),『V』記號表示要排班,在連續 7 天以上被排班時,給予紅色粗體字來識別。

Excel-設定連續7天以上被排班時給予警示(設定格式化的條件)

通常這類問題,都只要透過「設定格式化的條件」來處理。假設,整個日期報表是由第 2 列開始,所以從第 8 列開始設定格式化的條件。

1. 選取B8:B27。

2. 選取[常用/樣式]功能表中的「設定格式化的條件/新增規則」選項。

3. 選取規則類型:使用公式來決定要格式化哪些儲存格。

4. 輸入編輯規則:=COUNTIF(B2:B8,"V")>6

5. 設定格式:紅色粗體字。

之後,如果增加日期時,只要將已設定好格式化的條件的儲存格格式,複製到新增日期的儲存格即可,不需要再重設。

Excel-設定連續7天以上被排班時給予警示(設定格式化的條件)

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

在日常生活中,有時會用到要在一個數值區間中計算含有某個數字的個數,該如何處理?

以下圖的 Excel 例子,要計算 1~500 的數值區間中,共有幾個數含有『4』?例如:4, 140, 403, ...,這些數都含有 4。

Excel-在一個數值區間中計算含有某個數字的個數(SUBSTITUTE,ROW)

 

【公式設計與解析】

儲存格C2:{=SUM(1*(SUBSTITUTE(ROW(1:500),"4","")<>ROW(1:500)&""))}

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

(1) SUBSTITUTE(ROW(1:500),"4","")

在陣列公式中對 1~500 的數值利用 SUBSTITUTE 函數將數值中的 4 以空白取代,其傳回 500 個新的數字組成的字串陣列。

(2) ROW(1:500)&""

在陣列公式中將 1~500 的數值轉換為字串。

(3) SUBSTITUTE(ROW(1:500),"4","")<>ROW(1:500)&"")

判斷第(1)式和第(2)是否不相等,若是,代表該數含有 4;若不是,代表該數含 4。其結果傳回 TRUE/FALSE 陣列。

(4) 1*(SUBSTITUTE(ROW(1:500),"4","")<>ROW(1:500)&"")

利用『1*』運算,將第(3)式傳回的 TRUE/FALSE 陣列轉換為 1/0 陣列。

(5) SUM(1*(SUBSTITUTE(ROW(1:500),"4","")<>ROW(1:500)&""))

將第(4)式傳回的 1/0 陣列加總,其總和即為含有 4 的數值個數。

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

網友問到一個 Excel 的問題:

根據前一篇文章:Excel-依據日期區間列出各月人員清單(OFFSET,ROW,COLUMN,陣列公式),如果日期有跨年時,該如何處理?

本例以2015年7月~12月、2016年1月~6為週期,來列出各月學員的清單。

Excel-依據日期區間列出各月(跨年)人員清單(OFFSET,ROW,COLUMN,陣列公式)

 

【公式設計與解析】

先選取儲存格A1:C23,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:學員、開始、結束。

儲存格E3:

{=IFERROR(OFFSET($A$1,SMALL(IF((DATE(YEAR(開始),MONTH(開始),1)<=
DATE(2015,COLUMN(G:G),1))*(DATE(YEAR(結束),MONTH(結束),1)>=
DATE(2015,COLUMN(G:G),1)),ROW(學員),""),ROW(1:1))-1,0,1,1),"")}

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

條件一:DATE(YEAR(開始),MONTH(開始),1)<=DATE(2015,COLUMN(G:G),1)

DATE(YEAR(開始),MONTH(開始),1):利用 DATE 函數找出開始的日期陣列中各月的第1天。

DATE(2015,COLUMN(G:G),1):利用 DATE 函數找出 2015 年7月的第1天。

COLUMN(G:G)=7,向右複製公式時,COLUMN(G:G)=7→COLUMN(H:H)=8→...COLUMN(L:L)=12。

條件二:DATE(YEAR(結束),MONTH(結束),1)>=DATE(2015,COLUMN(G:G),1)

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

 

儲存格K3:

{=IFERROR(OFFSET($A$1,SMALL(IF((DATE(YEAR(開始),MONTH(開始),1)<=
DATE(2016,COLUMN(A:A),1))*(DATE(YEAR(結束),MONTH(結束),1)>=
DATE(2016,COLUMN(A:A),1)),ROW(學員),""),ROW(1:1))-1,0,1,1),"")}

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

條件一:DATE(YEAR(開始),MONTH(開始),1)<=DATE(2016,COLUMN(A:A),1)

DATE(2016,COLUMN(A:A),1):利用 DATE 函數找出 2016 年1月的第1天。

COLUMN(A:A)=1,向右複製公式時,COLUMN(A:A)=1→COLUMN(B:B)=2→...COLUMN(F:F)=6。

條件二:DATE(YEAR(結束),MONTH(結束),1)>=DATE(2016,COLUMN(A:A),1)

複製儲存格E3,貼至儲存格K3:P24。

詳細說明,可參考前一篇文章:Excel-依據日期區間列出各月人員清單(OFFSET,ROW,COLUMN,陣列公式)

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

網友問到 Excel 的問題:如何將 2015/05/25 轉換為 2015年05月25日?

參考下圖,使用者在輸入日期時,可能會輸入數值格式的 2015/05/25,其數值置於D欄(本例為:42149),也可能輸入的是文字格式的 2015/05/25 字串。相同公式不一定都適用於數值和文字格式的日期格式。

Excel-將西元年日期加上年月日(TEXT,YEAR,MONTH,DAY)

【公式設計與解析】

儲存格B2:

=YEAR(A2)&"年"&TEXT(MONTH(A2),"00")&"月"&TEXT(DAY(A2),"00")&"日"

(1) YEAR(A2)&"年"

利用 YEAR 函數取出儲存格A2中 4 碼的年數。

(2) TEXT(MONTH(A2),"00")&"月"

利用 MONTH 函數取出儲存格A2中的月數,因為函數傳回的數值,所以 02 會轉換為 2,因此利用 TEXT 函數透過參數『"00"』,將其顯示為 2 碼的月數。

(3) TEXT(DAY(A2),"00")&"日"

利用 DAY 函數取出儲存格A2中的日數,再透過 TEXT 函數將其顯示為 2 碼的日數。

這個公式適用於數值格式的日期轉換,也適用於文字格式的日期轉換。

複製儲存格B2,貼至儲存格B2:B10,再貼至儲存格B13:B21。

 

如果,你採用以下的公式。

儲存格C2:=LEFT(A2,4)&"年"&MID(A2,6,2)&"月"&RIGHT(A2,2)&"日"

該公式利用 LEFT、MID、RIGHT 函數取出儲存格年、月、日三個位置的內容,該公式不適用於數值格式的日期格式,因為其會以一個數值(參考D欄)來拆解年、月、日三個位置的內容。(參考上圖)

如果是數值格式的日期格式,你可以使用設定儲存格格式的方式。

自訂數值格式:yyyy"年"mm"月"dd"日"

不用再透過公式設定。

Excel-將西元年日期加上年月日(TEXT,YEAR,MONTH,DAY)

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

網友問到:在 Excel 中有一個學員受訓的記錄表,如何根據下圖左的日期區間,轉換至下圖中各月的清單報表?

Excel-依據日期區間列出各月人員清單(OFFSET,ROW,COLUMN,陣列公式)

 

【公式設計與解析】

先選取儲存格A1:C23,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:學員、開始、結束。

儲存格E2:

{=IFERROR(OFFSET($A$1,SMALL(IF((MONTH(開始)<=COLUMN(A:A))*(MONTH
(結束)>=COLUMN(A:A)),ROW(學員),""),ROW(1:1))-1,0,1,1),"")}

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

(1) (MONTH(開始)<=COLUMN(A:A))*(MONTH(結束)>=COLUMN(A:A))

條件一:(MONTH(開始)<=COLUMN(A:A))

判斷開始日期的月份是否小於或等於1月,COLUMN(A:A)向右複製時,COLUMN(A:A)=1→COLUMN(B:B)=2→COLUMN(C:C)=3→ ... 。

修件二:(MONTH(結束)>=COLUMN(A:A))

判斷結束日期的月份是否大或等於1月。

兩個條件之間的運算子『*』,相當於執行邏輯 AND 運算,並且會將 TRUE/FALSE 陣列轉換為 1/0 陣列。

 

(2) IF(第(1)式,ROW(學員),"")

如果第(1)的條件成立,則傳回成員的列號陣列,否則傳回空白(空字串)

 

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

SMALL 函數中利用第(2)式,找出傳回的列號中最小值的第 1, 2, 3, ... 個。ROW(1:1)向下複製時,會產生 ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→ ... 。

 

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

根據第(3)傳回的列號代入 OFFSET 函數取得儲存格內容。(注意公式中的『-1』)

 

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

當公式傳回錯誤訊息時,利用 IFERROR 函數使其顯示空白(空字串)。

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

你有遇過這樣的問題?在 Excel 產生的統計圖表要貼至 Word 中,但是圖表是一個可以使用下拉式清單選取不同資料的表格所建立,即動態表格共用同一個圖表。

參考下圖,如果你想分別將2013年、2014年、2015年三個資料表建立的圖表複製到 Word 文件中,將會發生以下的問題。

如果你選取了2013年所產生的統計圖:

解決複製動態表格共用圖表至Word的問題

貼至 Word 文件中:(目前 Excel 文件在開啟狀態)

解決複製動態表格共用圖表至Word的問題

接著,在 Excel 中複製2014年的圖表,再貼至 Word 文件中。你會發現2013年的統計圖被更新為2014年了。下圖中變成了二個2014年統計圖!那是因為在更新 Excel 文件時,Word 文件的內容也跟著變更了。

解決複製動態表格共用圖表至Word的問題

如何解決這樣的問題?試著這樣操作:

1. 在 Excel 文件中複製2013年統計圖。

2. 貼至 Word 文件中。

3. 關閉 Excel 文件。

4. 再次開啟 Excel 文件。

5. 在 Excel 文件中複製2014年統計圖。

6. 貼至 Word 文件中。

7. 關閉 Excel 文件。

問題即可解決了!依此方法可以再增加2015年統計圖了。

解決複製動態表格共用圖表至Word的問題

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

繼上篇文章:Excel-將日期中月、日第1碼的0去除(LEFT,MID,RIGHT)

如果在 Excel 中,想要將民國年轉換為西元年,並將月、日以2碼表示,該如何處理。

Excel-民國年轉換為西元年並將月、日以2碼表示

 

【公式設計與解析】

儲存格C2:=TEXT((LEFT(A2,3)+1911)&MID(A2,4,9),"yyyy/mm/dd")

LEFT(A2,3))+1911:取出儲存格A2的前3碼為民國年,再加上1911即為西洋年。

MID(A2,4,9):取出儲存格A2的前3碼之後的所有文字(/月/日)。

在 TEXT 函數中使用參數『yyyy/mm/dd』,即可轉換為西洋年4碼、月2碼、日2碼。

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

網友問到:在 Excel 的工作表中如果日期欄位裡放的是民國年,且月和日都以2碼表示,該如何去除月、日第1碼的0?

參考下圖,因為A欄裡放的是『民國年』的資料,因此儲存格內容視為文字,而非 Excel 預設的數值日期格式。

Excel-將日期中月、日第1碼的0去除(LEFT,MID,RIGHT)

 

【公式設計與解析】

儲存格C2:=LEFT(A2,4)&(--MID(A2,5,2))&"/"&(--RIGHT(A2,2))

(1) LEFT(A2,4):取出儲存格A2前的1~4碼,得到『105/』。

(2) --MID(A2,5,2):取出儲存格A2的5~6碼。

其中『--』,用以將文字轉成數值,轉成數值時會自動省略第1碼的0。

(3) --RIGHT(A2,2):取出儲存格A2前8~9碼。

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

前二篇文章提到了在 Excel 中的排名問題:

Excel-使用RANK.EQ和RANK.AVG處理排名問題

Excel-重覆名次不跳過

如果現在要根據分組的人員分數,來求各組人員的名次,該如何處理?

Excel-計算分組的名次(SUMPRODUCT)

 

【公式設計與解析】

這個問題無法使用 RANK 函數來直接求取名次,但可以使用 SUMPRODUCT 函數來模擬名次。

儲存格D2:=SUMPRODUCT(($B$2:$B$21=B2)*($C$2:$C$21>C2))+1

公式中使用兩個條件:

$B$2:$B$21=B2:判斷在『組別』陣列中是否和儲存格B2相同,傳回 TRUE/FALSE 陣列。

$C$2:$C$21>C2:判斷在『分數』陣列中是否大於儲存格C2,傳回 TRUE/FALSE 陣列。

公式中的『*』運算,相當於執行兩個條件的邏輯 AND 運算。計算過程中會將 TRUE/FALSE 轉換為 1/0。

最後記得要將 SUMPRODUCT 函數傳回值再加 1,意思是例如大於自己的有 2 個,自己是第 3 名。該公式的結果相當於使用 RANK.EQ 函數的運算結果。

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

有同仁在 Excel 中使用「進階篩選」時,想要將[基本資料]工作表中的資料篩選至[篩選結果]工作表中,於操作過程中遇到一些的問題。(參考下圖)

其步驟為:

1. 目前被選取的是[基本資料]工作表的儲存格A1,選取「進階篩選」。

2. 在[進階篩選]對話框中填入「資料範圍」和「篩選範圍」。這兩個範圍都在[基本資料]工作表中。

3. 因為勾選了「將篩選結果複製到其他地方」,所以在「複製到」文字方塊中選取了另一個工作表的儲存格,例如:篩選結果!$A$1:$C$1。

Excel-如何解決將工作表A的內容篩選至工作表B發生的錯誤

4. 當按下[確定]按鈕時,出現了以下的錯誤訊息:只能在使用中的工作表上複製篩選範圍。

Excel-如何解決將工作表A的內容篩選至工作表B發生的錯誤

如何解決這個問題?以上的錯誤訊息是指:篩選的結果要放在作用中的工作表。

調整以下的步驟。(參考下圖)

1. 先調整作用中工作表為[篩選結果]工作表,再選取「進階篩選」。

2. 在[進階篩選]對話框中填入「資料範圍」和「篩選範圍」。這兩個範圍都在指定到[基本資料]工作表中的儲存格範圍。

3. 因為勾選了「將篩選結果複製到其他地方」,所以在「複製到」文字方塊中選取[篩選結果]工作表的儲存格,例如:篩選結果!$A$1:$C$1。

Excel-如何解決將工作表A的內容篩選至工作表B發生的錯誤

結果如下:

Excel-如何解決將工作表A的內容篩選至工作表B發生的錯誤

結論是:

如果你要將工作表A的內容篩選至工作表B,則應在工作表B中開始操作進階篩選。若在工作表A中開始進階篩選程序,則會發生錯誤。

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

畢業的學生傳訊息問到:在 Google 地圖中規劃路線所提供的交通工具有開車、大眾運輸、步行、騎單車、飛機等,但是學生想要騎機車啊!?該如何規劃路線?

也對啦!在台灣,怎麼可以少了機車這一項呢?況車機車是把妹的利器,也是很多年輕人賴以移動的重要工具。

例如:要從台北101騎機車到野柳地質公園,要如何找出機車能走的路線?

在 Google 地圖上規劃路線時,預設是以開車為主。如果想要找出機車路線,只能以變通的做法來處理,先點選下圖中的「選項」。

在Google地圖中如何規劃「機車」的路線

再勾選避開「高速公路」,即會以一般公路為主。

在Google地圖中如何規劃「機車」的路線

點選詳細資訊即可得完整的路線。

在Google地圖中如何規劃「機車」的路線

如果你要使用手機的 Google 地圖 App,也是沒問題的。

在 Google 地圖上輸入起迄地點後,也是先得到開車的路線。先點選「選項」,再選「路線選項」。

在Google地圖中如何規劃「機車」的路線 在Google地圖中如何規劃「機車」的路線

同樣是選取「避開高速公路」,按一下「完成」,即可得機車可以走的路線。

在Google地圖中如何規劃「機車」的路線 在Google地圖中如何規劃「機車」的路線

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

1 2
找更多相關文章與討論

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼