贊助廠商

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

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

在 Microsoft Office 的文件中繪圖,如果需要連續繪製幾個相同圖案,如何做比較快?

你可以:

1.選取[插入/圖案]指令。

2.在想要使用的圖案上按一下右鍵。

3. 選取「鎖定繪圖模式」選項。

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

在 Excel 的工作表中有一欄的英、數字項目,如何找出最後一個數字呢?

儲存格C2:=INDEX(A2:A16,MATCH(9.99999999999999E+307,A2:A16,1))

MATCH(9.99999999999999E+307,A2:A16,1) = 12

MATCH函數中的參數1(也可以省略),會找尋等於或最接近要搜尋的的值,而且要搜尋的範圍必須先排序好。因為9.99999999999999E+307是Excel能接受的最大值,而A欄也沒有排序過,所以會傳回最後一個數字。

再透過 =INDEX(A2:A16,12),找到儲存格中的內容。

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

如果在 Excel 中的資料表會不斷的輸入資料,而只想要計算最近幾天的數據該如何處理呢?請標示資料取用了那些列的資料。

因為資料會不斷的變動所以位址無法以固定位址來計算,所以得藉助OFFSET和COUNTA函數來運算。

儲存格E2:=MAX(OFFSET($A$2,COUNTA($A:$A)-1-$E$1,1,$E$1,1))

COUNTA($A:$A)-1-$E$1為計算A欄中有資料的儲存格有幾格,然後減掉1(第一列),再減掉儲存格E1。這就是要取用資料的第一列,而儲存格範圍的高度是儲存格E1的內容。

同理:

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

在 Excel 中輸入多個班級(C301~C308)的成績表,其對應的科目都相同,每個科目最下方有該科目的平均值(如下圖),如何將這個8個資料表的平均成績整合在一個表(下下圖)中呢?

假設各班各科的平均置於第22列。

儲存格B2:=INDIRECT($A2&"!"&CHAR(65+COLUMN(A:A))&"22")

COLUMN(A:A)=1,往右複製變為COLUMN(B:B)=2,…

CHAR(65+COLUMN(A:A)),往右複製可以得到A,B,C,D,E,…。

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

在 Excel 中有一個資料表是由星期一至星期五組成的A,B,C,D五組數據資料,如何根據星期幾統計超過某值的個數有多少?資料會不斷的增加,而量測的某值要能變動。

儲存格B2:=A2,並設定數值格式顯示「星期X」。

由資料會不斷的增加,所以無法取得實際的位址,因此要以定義名稱的方式來取得不固定的位址。

DATA:=OFFSET(工作表1!$A$2,,,COUNTA(工作表1!$B:$B)-1,)

利用COUNTA取得B欄中有幾筆資料,COUNTA(工作表1!$B:$B)-1是因為第一列不是資料。

同理,設定以下四個名稱。

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

在 Excel 中取得一個資料表,其依日期填入數量(如下圖左)。現在,希望建立一個摘要表,其中只要最近12個月的小計,該如何處理?

(1) 取得年和月的數列

儲存格E2:=YEAR(EDATE(TODAY(),ROW()-13))

因為第13列要放本月的資料,所以透過EDATE函數,以ROW()-13求取之前的月份,再取出年的部分。

儲存格F2:=MONTH(EDATE(TODAY(),ROW()-13))

同上原理,取出月的部分。

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

在 Excel 的工作表中有一個資料表(如下圖上),希望利用下拉式選單來挑選姓名,並依不同人員顯示不同的統計圖表內容(如下圖下),並且將被選取人員的資料以較深的底色標示,該如何處理呢?

1. 選取儲存格B14,設定資料驗證為「資料驗證準則:清單,來源:=$A$2:$A$12」。

2. 選取儲存格A1:G2,製作一個直條圖的統計圖表。

3. 選取儲存格B2:G12,設定格式化規則為「=$A2=$B$14」,將底色改為較深的綠色和文字色彩為紅色。

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

在 Excel 中有一個資料(如下圖左),要由這個資料表中查詢對應的數值,並標示其位址,該如何處理?這次要在LOOKUP函數中使用兩個件來進行查詢。

首先定義三個資料範圍:DA:儲存格A3:A26,DB:儲存格B3:B26,DC:儲存格C3:C26。

(1) 查詢資料

儲存格G3:=IFERROR(LOOKUP(1,1/((A3:A26=E3)*(B3:B26=F3)),C3:C26),"X")

LOOKUP(1,1/((A3:A26=E3)*(B3:B26=F3)),C3:C26)

=LOOKUP(1,1/{True,False,True,False,False,…}*{Fasle,False,True,True,False,…},{1,2,3,4,5,…})

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

在 Excel 中取得一個資料表(如下圖),其中已經按生日欄位排序,如何讓 Excel 依間隔5歲自動畫出分隔線?即以31-35,36-40,41-45,…為間隔。

只要藉助設定格式化的條件規則,即可自動完成。

(1) 選取儲存格A3:E25。注意:不選取儲存格A2:E2。

(2) 設定條件格式化規則為:使用公式來決定要格式化哪些儲存格,並設定公式為「=INT(($D3-1)/5)<>INT(($D2-1)/5)」。

因為要以1-5為一組,6-10為一組,所以INT(($D3-1)/5),可以取得(年齡+1)除以5的商。用來判斷自已和上一個儲存格是否不相同。

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

在 Excel 中有一個單位轉換的函數:CONVERT,可以將多種類型的度量單位做轉換,例如:

=CONVERT(100,"F","C"),可以將100度的華氏溫度轉換為攝氏溫度。

參考微軟網站:http://office.microsoft.com/zh-tw/excel-help/HP010342306.aspx

CONVERT:將數字換算成不同的度量單位。

語法:CONVERT(number, from_unit, to_unit)

numberfrom_units 中要轉換的值。

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

在 Excel 的儲存格中輸入了多個字元的字串,如何來計算這些字元的數量呢?(參考下圖)

儲存格C2:=LEN($A2)-LEN(SUBSTITUTE($A2,C$1,""))

原理是:將儲存格C2的字串總長度(字元數),減掉去除某個字元的字串總長度(字元數)。

複製儲存格C2至儲存格C2:L12。

當你有了以上的每個字元的數量,如果要計算總和(參考13列)是很容的事。如果你不想透過先將每一列中每個字元的數量算出來,直接想得到總和,該如何做呢?

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

有人問到 Excel 2010 的最大欄數是XFD,這個數字代表第幾欄呢?我們可以把Excel的欄名順序看成是一種26進制的表示法,A, ~ , Z, AA ~ AZ, BA ~ ZZ, AAA ~ XFD。

以3個位元字母的第XFD欄(最後一欄)為例:

XFD1:=SUBSTITUTE(ADDRESS(ROW(),COLUMN(),4),ROW(),"")

=SUBSTITUTE(ADDRESS(1,COLUMN(),4),ROW(),"") 

=SUBSTITUTE(ADDRESS(1,16384,4),ROW(),"") [取得列數和欄數]

=SUBSTITUTE("XFD1",ROW(),"")  [將列數和欄數轉成欄名列號字串]

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

在 Excel 中如果要將時間轉為十進制表示,該如何處理呢?以下先以「分」來列舉,1-60分鐘如果要轉換成小時,而以十進制表示(請參考下表),則每1分鐘代表1/60小時。

如果要將一個時間(時:分)轉成十進制表示,則:

儲存格B2:=TEXT(HOUR(A2),"00") & "." &SUBSTITUTE(MINUTE(A2)/6,".","")

HOUR(A2)為取出儲存格A2中的時間之「時」的部分,TEXT(HOUR(A2),"00") 則將格式設定為以二位數表示,不足二位數者要補0。

MINUTE(A2)/6乃將儲存格A2中的時間之「分」的部分除以6,而SUBSTITUTE(MINUTE(A2)/6,".","")乃將MINUTE(A2)/6所得數值的部分之小數點去除(即左移一位,例:0.16666666變為.01666666),相當於除以10。所以整個公式為將儲存格A2中的時間之「分」的部分除以60。請觀察下圖較容易理解:

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

在 Excel 中如果想要有規律的改變間隔列的底色(參考下圖)該如何處理呢?假設第1列的數字做為標記間隔列數,並依先深色間隔白色的規律。

儲存格B2:=LEFT(MOD(ROW()-2,B$1*2)<B$1,1),複製儲存格B2至儲存格B2:G25。

公式中的MOD(ROW()-2,B$1*2)會得到如下圖的數字,MOD(ROW()-2,B$1*2)<B$1會得到TURE/FALSE的數列,透過LEFT函數取出第一個字母。

因此,只要將儲存格B2:G25的格式化條件設定為=MOD(ROW()-2,B$1*2)<B$1,成立時改變其底色即可。

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

在 Excel 中取得一個學生考試成績記錄表(參考下圖),現在有部分成績將不予採計,該如何計算平均呢?

採用的方式是在各個成績欄位上標記「X」記號,表示該成績不予計算,而且該欄位的成績會予以淡化。

(1) 計算平均

儲存格M3:{=AVERAGE(IF(UPPER($C$1:$L$1)<>"X",C3:L3,FALSE))}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。複製儲存格M3,往下各列貼上。

透過UPPER函數可以接受「X、x」的標示,公式中將不符合條件者,給予FALSE,該數將不會列入平均。

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

在 Excel 中取得學生成績表(如下圖),因為學生成績是依座號順序輸入,而學生是以亂數分組而成。共有40位學生,每4位學生一組,共分為10組。現在要根據這個成績表,來計算A成績的最高分、B成績的不及格數和三個科目的加權平均成績。

請注意:以下均為這是陣列公式,輸入完成應按 Ctrl+Shift+Enter 鍵。

(1) A成績的最高分

{=MAX(($C$3:$C$42=$H3)*D$3:D$42)}

=MAX((符合組別1的True/False陣列)*A成績陣列)

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

當你在 Excel 取得一個資料表(如下圖左),想要以符號字元(例如:「■、□」字元)來繪製達成率的統計圖表,該如何做呢?

特別注意圖表是以月份為中心,左邊為未達目標(達成率為負數),右邊為超過目標(達成率為正數)。而且未達目標者是以中心往左填入「■」字元,而超過目標者是以中心往右填入「■」字元。

儲存格E2:=IF(D2<0,REPT("□",10+ROUND(D2*100,0))&REPT("■",-ROUND(D2*100,0)),REPT("□",10))

利用ROUND函數將達成率乘以100後四捨五入,再以REPT函數重覆「■」字元。因為全為要填滿10個字元,所以其餘以「□」字元呈現。而且要先判斷達成率如果是負數才填入,如果不是負數,則填入10個「□」字元。

儲存格G2:=IF(D2>0,REPT("■",ROUND(D2*100,0))&REPT("□",10-ROUND(D2*100,0)),REPT("□",10))

同上原理,先判斷達成率如果是正數才填入,如果不是正數則,填入10個「■」字元。

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

如果在 Excel 中的取得一個時間的數列(含分:秒),如何計算排名與總和呢?

儲存格C2:=RANK(B2,$B$2:$B$16,1)

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

RANK函數中的參數1,代表要遞增排序。(參數0,代表要遞減排序)

(2) 計算總和

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

在 Excel 中如果取得一個Email地址的清單,如果想要將Email中的帳號和伺服器名稱分離出來,該如何處理呢?

儲存格B2:=LEFT(A2,FIND("@",A2)-1)

儲存格C2:=RIGHT(A2,LEN(A2)-FIND("@",A2)-1)

複製儲存格B2:C2,往下貼上即可。

FIND:在某個文字字串內找到另一個文字字串,並傳回該文字字串在第一個文字字串中的起始位置。

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

在 Excel 中取得一個數列(如下圖),想要標示出數列中不重覆的數、重覆的數、重覆的數但不包第1個、排除重覆的數(圖中深色底、紅色字的儲存格為標示)。這次要以設定格式化條件來處理,而且只要使用COUNTIF函數即可。

(1) 重覆的數

選取儲存格B2:B20,設定公式條件:=COUNTIF($B$2:$B$20,B2)=1

image

(2) 重覆的數

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

Close

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

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

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

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

reload

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼