贊助廠商

學不完.教不停.用不盡文章列表

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

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

之前文章提過依分數發給獎金,是使用SUMPRODUCT函數,而且在一個儲存格中完成。參考:

http://isvincent.blogspot.com/2010/06/excel_13.html

但是這個方式擴充性比較小,這次來使用VLOOKUP函數以查表產生結果。

先建立分數和獎金的對照表:

分數 獎金
   0 ~ 449 $ -
450 ~ 459 $ 50
460 ~ 469 $ 100
470 ~ 479 $ 150
480 ~ 489 $ 200
490 ~ 499 $ 250
500 $ 350

 

儲存格H2:=VLOOKUP(G2,$J$2:$K$7,2,TRUE)

複製儲存格H2至儲存格H2:H21。

其中VLOOKUP()中第四個參數設定為TRUE,表示找尋大致符合的資料。

關於VLOOKUP函數的說明參考微軟網站:

http://office.microsoft.com/zh-tw/excel-help/HP010343011.aspx

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

在 Excel 的文件中如果要將列印出來的報表加上浮水印效果,該如何處理呢?

1. 開啟[版面配置/版面設定],切換至[頁首/頁尾]標籤。

2. 按一下[自訂頁首]按鈕。

3. 將插入點移至[左]、[中]、[右]的其中一個方塊中。(例如:中)

4. 按一下[插入圖片]按鈕。

5. 選取想要當為浮水印的圖片。本例在[中]的方塊中,會插入「&[圖片]」功能變數。

當你預覽或列印這份文件時,就可以看到浮水印文字了!(參考下圖)

 

 

 

如果你覺得這個浮水印的位置和大小不合宜,可以在插入頁首的對話框中,按一下[設定圖片格式]按鈕(位在[插入圖片]按鈕旁)。你可以重新設定圖片的大小、位置、色彩、亮度、對比等。

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

在 Excel 中能應用來查表的函數很多,但是寫公式畢竟是個煩人的事。其實使用簡單的定義名稱也可以來查表。

1. 首先,要來定義名稱,所以先選取整個資料表。

2. 然後選取名稱區塊中的[公式/從選取範圍建立]選項。

 

3. 勾選[頂端列]和[最左欄]二個選項。因為這個資料表的頂端列和最左欄分別都有一個名稱,現在要以其名稱直接來定義儲存格範圍的名稱。

其自動定義的名稱,可以由「名稱管理員」中檢視。

4. 在公式中的應用,例如:

儲存格G2:=丁 第三季,其中[丁]和[第三季]這二個名稱中間要有一個空格,意思是要顯示這兩個名稱的「交集」。(此例的結果為「$1,192」)

這個方法使用了簡單的方式達到查表的效果,雖然不用寫複雜的公式,但是名稱必須以key in方式自行輸入。

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

在 PowerPoint 中如果想要將多張相片(相簿)轉成投影片,該如何處理?以 PowerPoint 2010 為例:

1. 按一[插入]功能表中的[相簿]按鈕。

2. 在[相簿]對話框中,按一下[檔案/磁碟片]按鈕。

3. 選取想要轉換成投影片的照片。

4. 照片的檔案名稱列在清單中,你可以上移/下移來改變照片的順序。

5. 如果想要刪除某張照片,則選取照片名稱後,按一下[移除]按鈕。或是使用[向左翻轉]/[向右翻轉]、[增加對比]/[減少對比]和[增加亮度]/[減少亮度]等按鈕,來微調照片。

6. 選取一種[圖片配置]方式。

7. 按一下[建立]按鈕。

照片已自動轉換成一張張的投影片。

這是四張照片放在一張投影片中:

這是每張投影片放二張照片,並且設定標題在投影片上方,而且所有照片色彩設為黑白。

如果你的投影片每張放多個照片,還可以選取[外框形狀]。

設定外框形狀為[圓角矩形]和[柔邊矩形]。

在 PowerPoint 2010 中,這些照片都可以透過[快速樣式]、[校正]、[色彩]、[美術效果]等,將照片進一步美化。

 

 

輕鬆完成照片的各種效果:

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

在Excel中有一個員工基本資料表,為了慶生會,現在要製作一個以季別區分的生日摘要表。(如下圖)

輸入陣列公式(輸入完成,按Ctrl+Shift+Enter鍵):

(1) 找出人名

儲存格F2:{=IFERROR(VLOOKUP(SMALL(IF(MONTH($C$2:$C$25)={1,2,3},$A$2:$A$25,""),ROW(1:1)),$A$1:$D$25,2),"")}

公式中 IF(MONTH($C$2:$C$25)={1,2,3},$A$2:$A$25,"") 為求得生日月份為1,2,3月的序號儲存格。

公式中 SMALL(IF(MONTH($C$2:$C$25)={1,2,3},$A$2:$A$25,""),ROW(1:1)),利用ROW和SMALL函數來找出第1,2,3,4…個序號。

公式中 VLOOKUP(SMALL(IF(MONTH($C$2:$C$25)={1,2,3},$A$2:$A$25,""),ROW(1:1)),$A$1:$D$25,2),利用序號在VLOOKUP函數中查表求得姓名。

[注意:如果有人同名同姓,可能會查表錯誤!]

最後利用IFERROR函數,使發生錯誤的儲存格顯示空的內容(因為儲存格會往下複製)。

儲存格H2:{=IFERROR(VLOOKUP(SMALL(IF(MONTH($C$2:$C$25)={4,5,6},$A$2:$A$25,""),ROW(1:1)),$A$1:$D$25,2),"")}

儲存格J2:{=IFERROR(VLOOKUP(SMALL(IF(MONTH($C$2:$C$25)={7,8,9},$A$2:$A$25,""),ROW(1:1)),$A$1:$D$25,2),"")}

儲存格L2:{=IFERROR(VLOOKUP(SMALL(IF(MONTH($C$2:$C$25)={10,11,12},$A$2:$A$25,""),ROW(1:1)),$A$1:$D$25,2),"")}

各欄往下複製。

(2) 找出對應的生日

欄據人名於表格中以VLOOKUP函數來查表出生日:

儲存格G2:=IFERROR(VLOOKUP(F2,$B$2:$C$25,2,FALSE),"")

將儲存格G2複製到儲存格I2,K2,M2。再於各欄往下複製。

將生日欄位的數值格式自訂為「mm/dd」,則生日中的月份和日期都會以2碼顯示。

如果要以單位和月份對照來看各月份的生日人數,該如何計算?

儲存格P2:=SUMPRODUCT((MONTH($C$2:$C$25)=ROW(1:1))*($D$2:$D$25=P$1)*1)

將儲存格P2複製到儲存格P2:R13。

公式中:

以ROW(1:1)=1, ROW(2:2)=2,ROW(3:3)=3…

($D$2:$D$25=P$1)*1為使邏輯值(True,False)轉換成數值(1,0)。

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

在 Word 2010 中,你可以使用[文字效果]設定,來讓文字做出非常漂亮的特效。Word 2010在這方面的使用上非常的直覺化,而且可以在套上效果後,再藉由自行修改一些設定來進行微調。

這是做出的特效之一。

如果你在插入文字藝術師時,也可以套用文字效果,兩種效果都會呈現。你只要先插入文字藝術師的文字,再套用前述的[文字效果],接著更改各種[文字藝術師]的格式設定。

這是其中的一種特效,已包含了文字效果+文字藝術師效果。

但是,你可能已發現這些文字效果產生時,將段落標記也一併做在效果中了,造成了一些效果上的瑕疵。如果不想要顯示這些段落標記,則可以在[Word選項]對話框中的[顯示]頁面中,不要勾選[段落標記],或是注意是否勾選了[顯示所有格式化標記]等。或是在[常用]工具列上,找到[段落標記]按鈕,使其在沒有被按下的狀態亦可。

不過,你放心,這些段落標記在列印時,並不會被印出來,所以不會對文件本身造成影響。

沒有段落標記,看起來的效果比較完美了!

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

Office 2010中的提供了多種的圖片樣式可以套用,以Word 2010為例:

當插入一張圖片後,選取[圖片工具]中的[格式],將可以選取近30種的圖片樣式:

當你套用了一種樣式後,其實還可以進一步自行修改其色彩、框線等。例如本例中選取套用[無外框],則圖片修正為:

你可以自行試試其他效果。

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

如果你有機會教授 Word 操作時,或是要來練習 Word 的相關功能,你可能需要一些文字來練習。在Microsoft Word 中有一個方式可以讓你在文件中快速插入範例文字。例如:在Word 2003的文件中,在要顯示文字的地方輸入 =rand(),然後按 ENTER 鍵,會插入以下的範例文字:

機會稍縱即逝。機會稍縱即逝。機會稍縱即逝。機會稍縱即逝。機會稍縱即逝。

機會稍縱即逝。機會稍縱即逝。機會稍縱即逝。機會稍縱即逝。機會稍縱即逝。

機會稍縱即逝。機會稍縱即逝。機會稍縱即逝。機會稍縱即逝。機會稍縱即逝。

其預設顯示的範例文字包含三個段落,每個段落包含五個句子。如果想要增加顯示的文字數量,可以藉由增加括弧內的數字,來控制顯示的段落與句數。

=rand() 函數的語法為:=rand(p,s)

其中 p 為段落數,而 s 則為想要於每個段落中顯示的句數。

如果省略 s ,則預設值為五個句子的文字。括弧內可以使用的最大數字為 200。

例如:=rand(2) 會插入兩個包含五個句子的文字段落

例如:=rand(2,1) 會插入兩個包含一個句子的文字段落

例如:=rand(10) 會插入十個包含五個句子的文字段落

例如:=rand(10,10) 會插入十個包含十個句子的文字段落

資料來源:http://support.microsoft.com/kb/212251

在 Word 2010 文件中測試 =rand() 的結果,會顯示範例文字:

在 [插入] 索引標籤上的圖庫所包含的項目,是專為調整文件的整體外觀而設計。您可以利用這些圖庫插入表格、頁首、頁尾、清單、封面或其他文件建置組塊。當您建立圖片或圖表時,也會與目前的文件外觀協調一致。

在 [常用] 索引標籤上的 [快速樣式庫] 中,為選取文字選擇外觀,就能輕易地變更文件中選取文字的格式設定。您也可以使用 [常用] 索引標籤的其他控制項,來直接設定文字格式。多數控制項可以選擇使用目前佈景主題的外觀,或是使用您直接指定的格式。

若要變更文件的整體外觀,請在 [版面配置] 索引標籤上選擇新的 [佈景主題] 元素。若要變更快速樣式庫提供的外觀,請使用 [變更目前快速樣式集] 命令。佈景主題庫和快速樣式庫都提供重設命令,因此隨時可以將您的文件外觀還原成目前範本的初始狀態。

在 Word 2010 文件中,也適用=rand() 函數的語法為:=rand(p,s)

其實在英文版的 Word 中會出現以下的句子:

The quick brown fox jumps over the lazy dog.

這個句子包含了26個英文字母,方便做各種測試。

 

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

在Excel中來試試計算擲四顆骰子遊戲的點數,其規則必須要兩顆相同點數,再計算另外兩顆的和。

先試著計算每個點數有幾個骰子重覆(不包含自己,可能另有1,2,3個相同點數)。

儲存格B8:=COUNTIF($B$2:$E$2,B2)-1

複製儲存格B8到複製儲存格B8:E8。

接著檢驗儲存格B8是否大於0(如果為0表示該點數沒有重覆出現)。

儲存格B9:=IF(B8=1,TRUE,FALSE)

複製儲存格B8到複製儲存格B9:E9。

現在試著將儲存格B8:E9中的數字加總後除以2,儲存格C11:=SUM(B8:E8)/2。

可能的狀況分述如下:

狀況 說明 計算結果
case 0 4個數字完全不相同 0/2=0
case 1 2個相同數字,2個不同數字 2/2=1
case 2 2組相同數字 4/2=2
case 3 3個相同數字,1個不同數字 6/2=3
case 6 4個數字完全相同 12/2=6

 

(1) case 0:4個數字完全不相同

例:1,2,3,4;1,3,5,6。

儲存格C14:=0

以0計。

 

(2) case 1:2個相同數字,2個不同數字

例:1,2,5,5;2,4,5,2。

儲存格C15:=SUMPRODUCT(NOT(B9:E9)*B2:E2)

取未重覆者之點數相加。

 

(3) case 2:2組相同數字

例:2,2,4,4;1,1,6,6。

儲存格C16:=MAX(B2:E2)*2

取4個骰子點數的最大值乘以2。

 

(4) case 3:3個相同數字,1個不同數字

例:1,1,1,4;5,5,5,6。

儲存格C17:=0

以0計。

 

(5) case 6:4個數字完全相同

例:4,4,4,4;1,1,1,1。

儲存格C18:=SUM(B2:E2)/2

 

取4個骰子點數總和除以2。

儲存格C4:=CHOOSE(C11+1,C14,C15,C16,C17,0,0,C18)

利用CHOOSE函數,取C11的運算結果(C11+1是因為CHOOSE函數不接受0),找出對應的結果(CHOOSE函數找不到5,6項,所以補0)。

若儲存格B2:E2,以亂數(=INT(RAND()*6+1))產生,則只要按下F9,即可自動計算出一個值。

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

在Excel中的一個資料表,試著來練習將其摘要出相關統計報表。(如下圖)

(1) 學歷分析

使用SMUPRODUCT函數來計算人數(參考微軟網站:SUMPRODUCT 函數說明)

儲存格H4:=SUMPRODUCT(($E$2:$E$25=$G4)*($D$2:$D$25=$H$2)*($B$2:$B$25=H$3))

複製儲存格H4到儲存格H4:I6。

儲存格J4:=SUMPRODUCT(($E$2:$E$25=$G4)*($D$2:$D$25=$J$2)*($B$2:$B$25=J$3))

複製儲存格J4到儲存格J4:K6。

儲存格L4:=SUMPRODUCT(($E$2:$E$25=$G4)*($D$2:$D$25=$L$2)*($B$2:$B$25=L$3))

複製儲存格L4到儲存格L4:M6。

 

(2) 性別分析

使用陣列公式(輸入按Ctrl+Shift+Enter鍵),以SUM(IF())形式找出數量。

儲存格H11:

{=SUM(IF($B$2:$B$25=$H$9,IF($D$2:$D$25=H$10,IF($E$2:$E$25=$G11,1,0),0),0))}

複製儲存格H11到儲存格H11:J13。

儲存格K11:

{=SUM(IF($B$2:$B$25=$K$9,IF($D$2:$D$25=K$10,IF($E$2:$E$25=$G11,1,0),0),0))}

複製儲存格K11到儲存格K11:M13。

 

(3) 年齡分析

使用陣列公式(輸入按Ctrl+Shift+Enter鍵),以SUM(IF())形式找出數量。

儲存格H18:{=SUM(IF($C$2:$C$25>=20,IF($C$2:$C$25<=35,IF($E$2:$E$25=$G18,IF($B$2:$B$25=H$17,1,0),0),0),0))}

複製儲存格H18到儲存格H18:I20。

儲存格J18:{=SUM(IF($C$2:$C$25>=36,IF($C$2:$C$25<=50,IF($E$2:$E$25=$G18,IF($B$2:$B$25=J$17,1,0),0),0),0))}

複製儲存格J18到儲存格J18:K20。

儲存格L18:=SUM(IF($C$2:$C$25>=50,IF($C$2:$C$25<=65,IF($E$2:$E$25=$G18,IF($B$2:$B$25=L$17,1,0),0),0),0))

複製儲存格K11到儲存格L18:M20。

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

在Excel中建立一個成績表,如果只要顯示前三名,並且以不同色彩標示,該如何處理呢?

儲存格F2:=IF(E2=LARGE($E$2:$E$26,1),1,IF(E2=LARGE($E$2:$E$26,2),2,IF(E2=LARGE($E$2:$E$26,3),3,"")))

或是

儲存格F2:{=IF(RANK(E2,$E$2:$E$26)<=3,RANK(E2,$E$2:$E$26),"")}

這是陣列公式,輸入請按Ctrl+Shift+Enter。

複製儲存格F2至儲存格F2:F26。

(1)使用LARGE函數,判斷第1,2,3名,並標示出1,2,3。

(2)使用RANK函數+陣列公式,判斷第1,2,3名,並標示出1,2,3。

接下來要設定格式化條件來標示前三名。

選取儲存格A2:E2,設定以下的條件和格式:

再複製儲存格A2:E2的格式到儲存格A2:E26。

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

資料來源:http://office.microsoft.com

摘錄微軟網站提供的 Excel 2010 函數說明,超連結會連到 Microsoft 網站。 如果需要用到時,可以方便查詢。

數學與三角函數  

ABS 函數

會傳回數字的絕對值
ACOS 函數 會傳回數字的反餘弦值
ACOSH 函數 會傳回數字的反雙曲線餘弦值
AGGREGATE 函數 傳回清單或資料庫中的彙總
ASIN 函數 會傳回數字的反正弦值
ASINH 函數 會傳回數字的反雙曲線正弦值
ATAN 函數 會傳回數字的反正切值
ATAN2 函數 會從 X 與 Y 座標傳回反正切值
ATANH 函數 會傳回數字的反雙曲線正切值
CEILING 函數 會將數字四捨五入至最接近的整數或最接近的比較基數
CEILING.PRECISE 函數 會將數字四捨五入至最接近的整數或最接近的比較基數。無論數字的正負號,都會將數字進位。
COMBIN 函數 會傳回指定物件數的組合數
COS 函數 會傳回數字的餘弦值
COSH 函數 會傳回數字的雙曲線餘弦值
DEGREES 函數 會將弧度轉換成度數
EVEN 函數 會將數字無條件進位至最接近的偶數整數
EXP 函數 會傳回提升至指定數字之乘冪的 e
FACT 函數 會傳回數字的階乘
FACTDOUBLE 函數 會傳回數字的雙階乘
FLOOR 函數 將數值以趨近於零方式捨位
FLOOR.PRECISE 函數 會將數字捨位至最接近的整數值或最接近的比較基數。無論數字的正負號,都會將數字捨位。
GCD 函數 會傳回最大公因數
INT 函數 會將數字捨位至最接近的整數值
LCM 函數 會傳回最小公倍數
LN 函數 會傳回數字的自然對數
LOG 函數 會傳回指定底數之數字的對數
LOG10 函數 會傳回數字的以 10 為底對數
MDETERM 函數 會傳回陣列的矩陣行列式
MINVERSE 函數 會傳回陣列的反矩陣
MMULT 函數 會傳回兩個陣列的矩陣乘積
MOD 函數 會傳回相除後的餘數
MROUND 函數 傳回四捨五入為所需倍數的數字
MULTINOMIAL 函數 會傳回一組數字的多項式
ODD 函數 會將數字無條件進位至最接近的奇數整數
PI 函數 會傳回 Pi 的值
POWER 函數 會傳回提升至某個乘冪之數字的結果
PRODUCT 函數 會將引數相乘
QUOTIENT 函數 會傳回相除後的整數部分
RADIANS 函數 會將度數轉換成弧度
RAND 函數 會傳回 0 和 1 之間的亂數
RANDBETWEEN 函數 會傳回所指定數字之間的亂數
ROMAN 函數 會將阿拉伯數字轉換成文字格式的羅馬字
ROUND 函數 會將數字四捨五入至指定的位數
ROUNDDOWN 函數 將數值以趨近於零方式捨位
ROUNDUP 函數 會將數字四捨五入至指定的位數
SERIESSUM 函數 會傳回依據公式的冪級數總和
SIGN 函數 會傳回數字的正負號
SIN 函數 會傳回指定角度的正弦值
SINH 函數 會傳回數字的雙曲線正弦值
SQRT 函數 會傳回正平方根
SQRTPI 函數 會傳回 (number * pi) 的平方根
SUBTOTAL 函數 會傳回清單或資料庫的小計
SUM 函數 會將引數相加
SUMIF 函數 會將特定準則所指定的儲存格相加
SUMIFS 函數 加入範圍中符合多個準則的儲存格
SUMPRODUCT 函數 會傳回對應陣列元件之乘積的總和
SUMSQ 函數 會傳回引數之平方的總和
SUMX2MY2 函數 會傳回兩個陣列中對應值之平方差的總和
SUMX2PY2 函數 會傳回兩個陣列中對應值之平方和的總和
SUMXMY2 函數 會傳回兩個陣列中對應值之差的平方和
TAN 函數 會傳回數字的正切值
TANH 函數 會傳回數字的雙曲線正切值
TRUNC 函數 會將數值捨位為整數

 

文字函數  

ASC 函數

將字串中的全形 (雙位原組) 英文字母或片假名變更為半形 (單位元組) 字元
BAHTTEXT 函數 依照 ß (泰銖) 貨幣格式,將數值轉換成文字
CHAR 函數 傳回代碼所對應的字元
CLEAN 函數 移除文字中所有無法列印的字元
CODE 函數 文字字串中第一個字元的字碼
CONCATENATE 函數 將數個文字項目組合成一個文字項目
DOLLAR 函數 依照 $ (錢幣) 貨幣格式,將數值轉換成文字
EXACT 函數 檢查兩個文字值是否相同
FIND、FINDB 函數 在其他文字值中搜尋文字值 (大小寫視為相異)
FIXED 函數 以固定的小數位數將數值格式化為文字
JIS 函數 將字串中的半形 (單位元組) 英文字母或片假名變更為全形 (雙位原組) 字元
LEFT、LEFTB 函數 傳回文字值中最左邊的字元
LEN、LENB 函數 傳回文字字串中的字元個數
LOWER 函數 將文字轉換成小寫
MID、MIDB 函數 從文字字串中的指定位置開始傳回指定數目的字元
PHONETIC 函數 從文字字串中抽選注音 (假名註解) 字元
PROPER 函數 將文字值中每個單字的第一個字母設為大寫
REPLACE、REPLACEB 函數 取代文字中的字元
REPT 函數 依指定的次數重複顯示文字
RIGHT、RIGHTB 函數 傳回文字值中最右邊的字元
SEARCH 與 SEARCHB 函數 在其他文字值中搜尋文字值 (大小寫視為相異)
SUBSTITUTE 函數 使用新文字取代文字字串中的舊文字
T 函數 將引數轉換成文字
TEXT 函數 設定數字的格式並將其轉換成文字
TRIM 函數 移除文字中的空格
UPPER 函數 將文字轉換成大寫
VALUE 函數 將文字引數轉換成數值

 

工程函數  

BESSELI 函數

會傳回已修改的 Bessel 函數 In(x)
BESSELJ 函數 會傳回 Bessel 函數 Jn(x)
BESSELK 函數 會傳回已修改的 Bessel 函數 Kn(x)
BESSELY 函數 會傳回 Bessel 函數 Yn(x)
BIN2DEC 函數 會將二進位數字轉換成十進位
BIN2HEX 函數 會將二進位數字轉換成十六進位
BIN2OCT 函數 會將二進位數字轉換成八進位
COMPLEX 函數 會將實數係數與虛數係數轉換成複數
CONVERT 函數 會將數字從某個測量系統轉換成另一個測量系統
DEC2BIN 函數 會將十進位數字轉換成二進位
DEC2HEX 函數 會將十進位數字轉換成十六進位
DEC2OCT 函數 會將十進位數字轉換成八進位
DELTA 函數 會測試兩個值是否相等
ERF 函數 會傳回錯誤函數
ERF.PRECISE 函數 會傳回錯誤函數
ERFC 函數 會傳回互補錯誤函數
ERFC.PRECISE 函數 會傳回 x 和無限之間整合的補充 ERF 函數
GESTEP 函數 會測試數字是否大於閾值
HEX2BIN 函數 會將十六進位數字轉換成二進位
HEX2DEC 函數 會將十六進位數字轉換成十進位
HEX2OCT 函數 會將十六進位數字轉換成八進位
IMABS 函數 會傳回複數的絕對值 (模數)
IMAGINARY 函數 會傳回複數的虛係數
IMARGUMENT 函數 會傳回引數 theta,一個以弧度表示的角度
IMCONJUGATE 函數 會傳回複數的共軛複數
IMCOS 函數 會傳回複數的餘弦值
IMDIV 函數 會傳回兩個複數的商數
IMEXP 函數 會傳回複數的指數
IMLN 函數 會傳回複數的自然對數
IMLOG10 函數 會傳回複數底數為 10 的對數
IMLOG2 函數 會傳回複數底數為 2 的對數
IMPOWER 函數 會傳回提升至整數乘冪的複數
IMPRODUCT 函數 會傳回 2 到 255 個複數的乘積
IMREAL 函數 會傳回複數的實係數
IMSIN 函數 會傳回複數的正弦值
IMSQRT 函數 會傳回複數的平方根
IMSUB 函數 會傳回兩個複數之間的差異
IMSUM 函數 會傳回複數的總和
OCT2BIN 函數 會將八進位數字轉換成二進位
OCT2DEC 函數 會將八進位數字轉換成十進位
OCT2HEX 函數 會將八進位數字轉換成十六進位

 

邏輯函數  

AND 函數

如果所有引數為 TRUE,傳回 TRUE
FALSE 函數 傳回邏輯值 FALSE
IF 函數 指定要執行的邏輯檢定
IFERROR 函數 如果公式評估為錯誤,傳回您指定的值,否則傳回公式的結果
NOT 函數 將引數的邏輯值予以反轉
OR 函數 如果任一引數為 TRUE,傳回 TRUE
TRUE 函數 傳回邏輯值 TRUE

 

查閱與參照函數  

ADDRESS 函數

以文字格式傳回對工作表中單一儲存格的參照
AREAS 函數 傳回參照中區域的個數
CHOOSE 函數 從值的清單中選擇一個值
COLUMN 函數 傳回參照的欄號
COLUMNS 函數 傳回參照中的欄數
GETPIVOTDATA 函數 傳回樞紐分析表中儲存的資料
HLOOKUP 函數 尋找陣列的第一列並傳回指定儲存格的值
HYPERLINK 函數 建立捷徑,用以開啟在網路伺服器、內部網路或網際網路上儲存的文件
INDEX 函數 使用索引從參照或陣列中選擇值
INDIRECT 函數 傳回由文字值表示的參照
LOOKUP 函數 在向量或陣列中查閱值
MATCH 函數 在參照或陣列中查閱值
OFFSET 函數 傳回指定參照的參照位移
ROW 函數 傳回參照的列號
ROWS 函數 傳參照中的列數
RTD 函數 從支援 COM 自動化 (自動化:透過另一個應用程式或開發工具使用某應用程式的物件的方法。以前稱為 OLE Automation,Automation 是工業標準,並且是「元件物件模型 (COM)」的一項功能。)的程式中取出即時資料
TRANSPOSE 函數 傳回陣列的轉置
VLOOKUP 函數 尋找陣列的第一欄並移過列,然後傳回某個儲存格的值
ISERR 函數 如果值為除 #N/A 以外的任何錯誤值,傳回 TRUE
ISERROR 函數 如果值為任何錯誤值,傳回 TRUE
ISEVEN 函數 如果數值為偶數,傳回 TRUE
ISLOGICAL 函數 如果值為邏輯值,傳回 TRUE
ISNA 函數 如果值為錯誤值 #N/A,傳回 TRUE
ISNONTEXT 函數 如果值不是文字,傳回 TRUE
ISNUMBER 函數 如果值為數字,傳回 TRUE
ISODD 函數 如果數值為奇數,傳回 TRUE
ISREF 函數 如果值為參照,傳回 TRUE
ISTEXT 函數 如果值為文字,傳回 TRUE
N 函數 傳回轉換為數字的值
NA 函數 傳回錯誤值 #N/A
TYPE 函數 傳回表示值之資料類型的數字

 

日期及時間函數  

DATE 函數

傳回特定日期的序列值
DATEVALUE 函數 將文字形式的日期轉換為序列值
DAY 函數 將序列值轉換為月份中的日
DAYS360 函數 按每年 360 天計算兩個日期之間的天數
EDATE 函數 傳回日期的序列值,這是在開始日期之前或之後所指出的月份數
EOMONTH 函數 傳回所指定月份數之前或之後的月份最後一天的序列值
HOUR 函數 將序列值轉換為小時
MINUTE 函數 將序列值轉換為分鐘
MONTH 函數 將序列值轉換為月份
NETWORKDAYS 函數 傳回兩個日期之間所有工作日的數目
NETWORKDAYS.INTL 函數 使用參數指出哪幾天和多少天是週末,以計算兩個日期之間的所有工作日
NOW 函數 傳回目前日期及時間的序列值。
SECOND 函數 將序列值轉換為秒
TIME 函數 傳回特定時間的序列值
TIMEVALUE 函數 將文字形式的時間轉換為序列值
TODAY 函數 傳回今天日期的序列值
WEEKDAY 函數 將序列值轉換為星期
WEEKNUM 函數 將序列值轉換為表示某一週是一年當中第幾週的數字
WORKDAY 函數 傳回指定的工作日數目之前或之後,日期的數列值
WORKDAY.INTL 函數 使用參數指出哪幾天和多少天是週末,以傳回在指定的工作日數之前或之後的日期序列值
YEAR 函數 將序列值轉換為年
YEARFRAC 函數 傳回代表在 start_date 和 end_date 之間所有日期數字的年份分數

 

資料庫函數 (參照)  

DAVERAGE 函數

傳回所選資料庫記錄的平均值
DCOUNT 函數 計算資料庫中包含數字的儲存格數
DCOUNTA 函數 計算資料庫中非空白儲存格數
DGET 函數 從資料庫中抽選符合指定條件的一筆記錄
DMAX 函數 傳回所選資料庫記錄中的最大值
DMIN 函數 傳回所選資料庫記錄中的最小值
DPRODUCT 函數 乘以資料庫中符合條件之特定記錄欄位的值
DSTDEV 函數 計算所選資料庫記錄的樣本標準差
DSTDEVP 函數 計算所選資料庫記錄中整個母體的標準差
DSUM 函數 就符合條件的記錄中,計算指定欄位中數值資料的總和
DVAR 函數 傳回所選資料庫記錄的樣本變異數
DVARP 函數 計算所選資料庫記錄中整個母體的變異數

 

統計函數

 

AVEDEV 函數

會傳回各資料絕對平均差的平均值 (根據它們的平均)
AVERAGE 函數 會傳回引數的平均值
AVERAGEA 函數 會傳回引數的平均值,包含數字、文字和邏輯值
AVERAGEIF 函數 會傳回範圍中符合給定準則之所有儲存格的平均值 (算術平均值)。
AVERAGEIFS 函數 會傳回符合多個準則之所有儲存格的平均值 (算術平均值)
BETA.DIST 函數 會傳回 Beta 累加分配函數
BETA.INV 函數 會傳回指定 Beta 分配之累加分配函數的反函數
BINOM.DIST 函數 會傳回在特定次數的二項分配實驗中,實驗成功的機率
BINOM.INV 函數 會傳回累加二項分配小於或等於臨界值的最小值
CHISQ.DIST 函數 會傳回累加 beta 機率密度函數
CHISQ.DIST.RT 函數 會傳回單尾卡方分配的機率值
CHISQ.INV 函數 會傳回累加 beta 機率密度函數
CHISQ.INV.RT 函數 會傳回卡方分配之單尾機率的反函數
CHISQ.TEST 函數 會傳回獨立性檢定的結果
CONFIDENCE.NORM 函數 會傳回母體平均值的信賴區間
CONFIDENCE.T 函數 會傳回 Student 式 T 分配下,母體平均值的信賴區間
CORREL 函數 會傳回兩個資料集的相關係數
COUNT 函數 會計算引數清單中數字的個數
COUNTA 函數 會計算引數清單中值的個數
COUNTBLANK 函數 會計算範圍中空白儲存格的個數
COUNTIF 函數 會計算符合指定準則的範圍中之儲存格個數
COUNTIFS 函數 會計算符合多個準則的範圍中之儲存格個數
COVARIANCE.P 函數 會傳回共變數,即成對誤差乘積的平均值
COVARIANCE.S 函數 會傳回樣本共變數,也就是兩個資料集中每一個資料點對差距乘積的平均值。
DEVSQ 函數 會傳回差異平方和
EXPON.DIST 函數 會傳回指數分配函數
F.DIST 函數 會傳回 F 機率分配
F.DIST.RT 函數 會傳回 F 機率分配
F.INV 函數 會傳回 F 機率分配的反函數
F.INV.RT 函數 會傳回 F 機率分配的反函數
F.TEST 函數 會傳回 F 檢定的結果
FISHER 函數 會傳回費雪轉換
FISHERINV 函數 會傳回費雪轉換的反函數值
FORECAST 函數 會傳回等差趨勢上的值
FREQUENCY 函數 會以垂直陣列傳回頻率分配
GAMMA.DIST 函數 會傳回伽瑪分配
GAMMA.INV 函數 會傳回伽瑪累加分配的反函數
GAMMALN 函數 會傳回伽瑪函數的自然對數 Γ(x)
GAMMALN.PRECISE 函數 會傳回伽瑪函數的自然對數 Γ(x)
GEOMEAN 函數 會傳回幾何平均數
GROWTH 函數 會根據指數趨勢線傳回值
HARMEAN 函數 會傳回調和平均數
HYPGEOM.DIST 函數 會傳回超幾何分配
INTERCEPT 函數 會傳回直線迴歸線的截距
KURT 函數 會傳回資料集的峰度值
LARGE 函數 會傳回資料集中第 K 個最大值
LINEST 函數 會傳回等差趨勢的參數
LOGEST 函數 會傳回指數趨勢的參數
LOGNORM.DIST 函數 會傳回累加對數分配
LOGNORM.INV 函數 會傳回對數常態累加分配的反函數值
MAX 函數 會傳回引數清單中的最大值
MAXA 函數 會傳回引數清單中的最大值,包含數字、文字和邏輯值
MEDIAN 函數 會傳回指定數字的中間數
MIN 函數 會傳回引數清單中的最小值
MINA 函數 會傳回引數清單中的最小值,包含數字、文字和邏輯值
MODE.MULT 函數 會傳回在陣列或資料範圍中,最常出現或重複之值的垂直陣列
MODE.SNGL 函數 會傳回資料集中的最常見值
NEGBINOM.DIST 函數 會傳回負二項分配
NORM.DIST 函數 會傳回常態累加分配
NORM.INV 函數 會傳回常態累加分配的反函數
NORM.S.DIST 函數 會傳回標準常態累加分配
NORM.S.INV 函數 會傳回標準常態累加分配的反函數
PEARSON 函數 會傳回皮耳森積差相關係數
PERCENTILE.EXC 函數 會傳回範圍中位於第 k 個百分比的數值,其中 k 在範圍 0 到 1 之間 (不含 0 和 1)。
PERCENTILE.INC 函數 會傳回範圍中位於第 K 個百分比的值
PERCENTRANK.EXC 函數 會以資料集百分比 (0 到 1 之間,不含 0 和 1) 傳回數值在資料集中的等級
PERCENTRANK.INC 函數 會傳回資料集中值的百分比排位
PERMUT 函數 會傳回特定數量的物件所有可能排列方式的個數
POISSON.DIST 函數 會傳回波氏分配
PROB 函數 會傳回範圍中的值落在上下限之間的機率
QUARTILE.EXC 函數 會傳回資料集的四分位數,以 0 到 1 之間 (不含 0 和 1) 的百分比值為根據
QUARTILE.INC 函數 會傳回資料集的四分位數
RANK.AVG 函數 會傳回數字在數字清單中的排位
RANK.EQ 函數 會傳回數字在數字清單中的排位
RSQ 函數 會傳回皮耳森相關係數的平方
SKEW 函數 會傳回一個分配的偏態
SLOPE 函數 會傳回直線迴歸線的斜率
SMALL 函數 會傳回資料集中第 k 小的值
STANDARDIZE 函數 會傳回標準化的值
STDEV.P 函數 會根據整個母體來計算標準差
STDEV.S 函數 會根據樣本來估計標準差
STDEVA 函數 會根據樣本來估計標準差,包含數字、文字和邏輯值
STDEVPA 函數 會根據整個母體計算標準差,包含數字、文字及邏輯值
STEYX 函數 會傳回迴歸分析中為每個 X 所預測之 Y 值的標準誤差
T.DIST 函數 會傳回 Student 式 T 分配的百分比點 (機率)
T.DIST.2T 函數 會傳回 Student 式 T 分配的百分比點 (機率)
T.DIST.RT 函數 會傳回 Student 式 T 分配
T.INV 函數 會傳回機率函數和自由度之 Student 式 T 分配的 T 值
T.INV.2T 函數 會傳回 Student 式 T 分配的反函數
TREND 函數 會傳回等差趨勢上的值
TRIMMEAN 函數 會傳回資料集的內部平均值
T.TEST 函數 會傳回與 Student 式 T 檢定相關的機率
VAR.P 函數 會根據整個母體來計算變異數
VAR.S 函數 會根據樣本來估計變異數
VARA 函數 會根據樣本來估計變異數,包含數字、文字和邏輯值
VARPA 函數 會根據整個母體來計算變異數,包含數字、文字和邏輯值
WEIBULL.DIST 函數 會傳回 Weibull 分配
Z.TEST 函數 會傳回 z 檢定的單尾機率值

 

資訊函數  

CELL 函數

傳回儲存格的資訊,如格式、位置及內容等
ERROR.TYPE 函數 傳回與錯誤類型對應的數字
INFO 函數 傳回目前作業系統環境的相關資訊
ISBLANK 函數 如果值為空白,傳回 TRUE
ISERR 函數 如果值為除 #N/A 以外的任何錯誤值,傳回 TRUE
ISERROR 函數 如果值為任何錯誤值,傳回 TRUE
ISEVEN 函數 如果數值為偶數,傳回 TRUE
ISLOGICAL 函數 如果值為邏輯值,傳回 TRUE
ISNA 函數 如果值為錯誤值 #N/A,傳回 TRUE
ISNONTEXT 函數 如果值不是文字,傳回 TRUE
ISNUMBER 函數 如果值為數字,傳回 TRUE
ISODD 函數 如果數值為奇數,傳回 TRUE
ISREF 函數 如果值為參照,傳回 TRUE
ISTEXT 函數 如果值為文字,傳回 TRUE
N 函數 傳回轉換為數字的值
NA 函數 傳回錯誤值 #N/A
TYPE 函數 傳回表示值之資料類型的數字

 

財務函數  

ACCRINT 函數

傳回定期支付利息之證券的應計利息
ACCRINTM 函數 傳回證券在到期時支付利息的應計利息
AMORDEGRC 函數 使用折舊係數傳回每個會計週期的折舊
AMORLINC 函數 傳回每個會計週期的折舊
COUPDAYBS 函數 傳回從票息週期的開始到結帳日期之間的日數
COUPDAYS 函數 傳回包含結帳日期之票息週期的日數
COUPDAYSNC 函數 傳回從結帳日期到下一個票息日期之間的日數
COUPNCD 函數 傳回結帳日期之後下一個票息日期
COUPNUM 函數 傳回可在結帳日期和到期日期之間支付的票息數字
COUPPCD 函數 傳回結帳日期之前的前一個票息日期
CUMIPMT 函數 傳回在兩個週期之間所支付的累計利息
CUMPRINC 函數 傳回在兩個週期之間的貸款上所支付累計資金
DB 函數 傳回固定資產在指定期間按定率遞減法計算的折舊
DDB 函數 傳回固定資產在指定期間內按倍率遞減法,或者其他指定方法計算所得的折舊
DISC 函數 傳回證券的貼現率
DOLLARDE 函數 將以分數表示的美金價格,轉換成以十進位數字表示的美金價格
DOLLARFR 函數 將以十進位數字表示的美金價格,轉換成以分數表示的美金價格
DURATION 函數 傳回具有定期利息付款之證券的年度持續時間
EFFECT 函數 傳回實年度利率
FV 函數 傳回投資的未來值
FVSCHEDULE 函數 在套用一系列複利率之後,傳回初始資金的未來值
INTRATE 函數 傳回完整投資證券的利率
IPMT 函數 傳回某項投資某一期應付利息的金額
IRR 函數 傳回某一連續期間現金流量的內部報酬率
ISPMT 函數 計算指定投資期限內的直線式貸款利息
MDURATION 函數 傳回具有保障票面價值 $100 之證券的存續已修改期間
MIRR 函數 傳回以不同利率計算正負現金流量的內部報酬率
NOMINAL 函數 傳回年度名義利率
NPER 函數 傳回某項投資的期數
NPV 函數 在已知各期現金流量及貼現率的情況下,求出某項投資的淨現值
ODDFPRICE 函數 傳回具有奇數第一個週期的證券每 $100 面額的價格
ODDFYIELD 函數 傳回具有奇數第一個週期的證券收益
ODDLPRICE 函數 傳回具有奇數最後週期之證券每 $100 面額的價格
ODDLYIELD 函數 傳回具有奇數最後週期的證券收益
PMT 函數 傳回年金的每期付款額
PPMT 函數 傳回投資某一期的本金償還額
PRICE 函數 傳回證券每 $100 面額的價格,該證券會定期支付利息
PRICEDISC 函數 傳回貼現證券每 $100 面額的價格
PRICEMAT 函數 傳回證券每個 $100 面額的價格,該證券在到期時支付利息
PV 函數 傳回某項投資的年金現值
RATE 函數 傳回年金的各期利率
RECEIVED 函數 傳回完整投資證券在到期時收到的數量
SLN 函數 傳回某項固定資產按直線折舊法所計算的每期折舊金額
SYD 函數 按年數合計法計算,傳回某固定資產在某一指定期間的折舊金額
TBILLEQ 函數 傳回國庫券的債券約當收益率
TBILLPRICE 函數 傳回國庫卷每 $100 面額的價格
TBILLYIELD 函數 傳回國庫卷的收益
VDB 函數 傳回固定資產在指定期間或某一時間段按遞減餘額法計算的折舊
XIRR 函數 傳回現金流時程的內部報酬率,該現金流量不必是定期性的
XNPV 函數 傳回現金流時程的淨現值,該現金流量不必是定期性的
YIELD 函數 傳回定期支付利息的證券收益
YIELDDISC 函數 傳回貼現證券的年收益;例如,國庫債券
YIELDMAT 函數 傳回證券的年收益,該證券在到期時支付利息

 

Cube 函數

 

CUBEKPIMEMBER 函數

會傳回關鍵效能指標 (KPI) 屬性,並在儲存格中顯示 KPI 名稱。KPI 是可量化的度量 (如每月毛利或每季員工流動率),用於監視組織的效能。
CUBEMEMBER 函數 會傳回 Cube 中的成員或 Tuple。用於驗證成員或 Tuple 是否存在於 Cube 中。
CUBEMEMBERPROPERTY 函數 會傳回 Cube 中成員屬性的值。用於驗證成員名稱是否存在於 Cube 內,並傳回此成員的指定屬性。
CUBERANKEDMEMBER 函數 會傳回集合中第 n 個或已排序的成員。用於傳回集合中的一個或多個元素,如銷售業績最好的人員,或前 10 名的學生。
CUBESET 函數 會將集運算式傳送至伺服器上的 Cube 以定義成員或 Tuple 的導出集合,從而建立該集合,然後將該集合傳回到 Microsoft Excel。
CUBESETCOUNT 函數 會傳回集中項目的數目。
CUBEVALUE 函數 會從 Cube 傳回彙總值。

 

與增益集一起安裝的使用者定義函數  

CALL 函數

會呼叫動態連結程式庫或程式碼資源中的程序
EUROCONVERT 函數 會使用歐元做為中介,將數字轉換為歐元、將數字從歐元轉換為歐元的成員貨幣,或將數字從一種歐元成員貨幣轉換為另一種歐元成員貨幣 (三角測量)
REGISTER.ID 函數 會傳回所指定的已登錄之動態連結程式庫 (DLL) 或原始程式碼資源的登錄代號
SQL.REQUEST 函數 與外部資料來源連接,並在工作表中執行查詢,然後以陣列方式傳回結果,而不需要使用巨集程式

 

相容性函數 (參照)  

BETADIST 函數

傳回 beta 累加分配函數
BETAINV 函數 傳回指定 beta 分配之累加分配函數的反函數
BINOMDIST 函數 傳回在特定次數之二項分配實驗中,實驗成功的機率
CHIDIST 函數 傳回單尾卡方分配的機率值
CHIINV 函數 傳回卡方分配之單尾機率的反函數
CHITEST 函數 傳回獨立性檢定的結果
CONFIDENCE 函數 傳回母體平均值的信賴區間
COVAR 函數 傳回共變數,即成對差異乘積的平均值
CRITBINOM 函數 傳回累加二項分配小於或等於臨界值的最小值
EXPONDIST 函數 傳回指數分配
FDIST 函數 傳回 F 機率分配
FINV 函數 傳回 F 機率分配的反函數
FTEST 函數 傳回 F 檢定的結果
GAMMADIST 函數 傳回 Gamma 分配函數
GAMMAINV 函數 傳回 Gamma 累加分配的反函數
HYPGEOMDIST 函數 傳回超幾何分配
LOGINV 函數 傳回對數常態累加分配函數的反函數
LOGNORMDIST 函數 傳回累加對數分配
MODE 函數 傳回資料集中出現最多的值
NEGBINOMDIST 函數 傳回負二項分配
NORMDIST 函數 傳回常態累加分配
NORMINV 函數 傳回常態累加分配的反函數
NORMSDIST 函數 傳回標準常態累加分配
NORMSINV 函數 傳回標準常態累加分配的反函數
PERCENTILE 函數 傳回範圍中位於第 K 個百分比的值
PERCENTRANK 函數 傳回資料集中值的百分比等級
POISSON 函數 傳回波氏分配
QUARTILE 函數 傳回資料集的四分位數
RANK 函數 傳回某數字在數字清單中的等級
STDEV 函數 根據樣本來估計標準差
STDEVP 函數 根據整個母體來計算標準差
TDIST 函數 傳回 Student 氏 t-分配
TINV 函數 傳回 Student 氏 t-分配函數的反函數
TTEST 函數 傳回 Student 氏 t-檢定的機率值
VAR 函數 根據樣本來估計變異數
VARP 函數 根據整個母體來計算變異數
WEIBULL 函數 傳回 Weibull 分配
ZTEST 函數 傳回 z 檢定的單尾機率值

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

在Excel中建立一組隨機亂數資料,如何自動配置到另一個資料表中呢?例如依每間隔20為單位來配置。

儲存格G2:{=SMALL(IF($A$2:$E$11>=--LEFT(G$1,FIND("-",G$1,1)-1),IF($A$2:$E$11<=--RIGHT(G$1,LEN(G$1)-FIND("-",G$1,1)),$A$2:$E$11)),ROW(1:1))}

此公式為陣列公式,輸入時要按Ctrl+Shift+Enter。

將儲存格G2複製到儲存格G2:K20。

其中:

(1) --LEFT(G$1,FIND("-",G$1,1)-1)表示要找出第一列儲存格中「XX-YY」的XX值,--的用意為轉換成數字,因為LEFT()函數求得的結果為文字。

(2) --RIGHT(G$1,LEN(G$1)-FIND("-",G$1,1))表示要找出第一列儲存格中「XX-YY」的YY值。

(3) SMALL(大於XX且小於YY的儲存格, ROW(1,1))表示找出合於條件的第1個,當儲存格往下複製時,ROW(1,1)會變成ROW(2,2)、ROW(3,3)…。

接著:

因為如果往下複製儲存格G2,如果儲存格的結果是找不到這個數,會出現#NUM!的錯誤訊息。現在要使用設定格式化條件的方式,將錯誤訊息隱藏。例如將儲存格的錯誤訊息之色彩,設定成和底色一樣,就可以達到隱藏的作用。

在儲存格G2中設定當公式:=ISERROR(G2)為真時,顯示和底色相同的文字色彩。

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

在Excel中輸入一個資料表,如下圖左半部,班級是不規則順序輸入的。如果要自動轉換成右半部,變成依班級順序排列,而且只有班級名稱的第一筆才顯示,其餘班級名稱不顯示。

1.

儲存格G2:=SMALL($B$2:$B$24,ROW(1:1))

觀察因為班級全部由數字組成,所以利用SMALL函數,將其由小到大取出。

2.

儲存格H2:{=OFFSET($A$1,SMALL(IF($B$2:$B$24=G2,ROW(INDIRECT("$A$2:$A$24"))-1),COUNTIF($G$2:G2,G2)),,)}

這是陣列公式,輸入請按Ctrl+Shift+Enter鍵。目的是將SMALL函數所對應的班級來找出對應的編號。

其中「ROW(INDIRECT("$A$2:$A$24"))-1」,在陣列公式中會產生{1,2,3,…,23}的陣列內容。

其中「COUNTIF($G$2:G2,G2))」,找出某個班級名稱在「相同的」班級名稱上位於第幾個位置。

其中「SMALL(IF($B$2:$B$24=G2,ROW(INDIRECT("$A$2:$A$24"))-1),COUNTIF($G$2:G2,G2))」,在陣列公式中為找出指定的班級在第幾列。

透過「OFFSET($A$1,第幾列,,)」來找出編號號。

3.

儲存格I2:=VLOOKUP($H2,$A$1:$E$24,COLUMN(C2),FALSE)

複製儲存格I2到儲存格I2:K2。

使用VLOOKUP函數來根據編號找到姓名、檢定和級別欄位內容。

4.

然後將儲存格G2:K2複製到儲存格G2:K24。

5.

如何只顯示第一次出現的班級名稱,其餘則不顯示呢?可以利用設定格式化條件的方式來處理。

選取儲存格G3,設定公式的內容為:=COUNTIF($G$2:$G3,$G3)>1,並設定符合條件時,顯示和底色相同的文字色彩(當文字色彩和底色一樣時,將看不到文字。),這樣就可以把第一個以外的文字隱藏了。

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

在Excel中,如果要將一組依序排好的資料(例如:依序的班級編號)隨機重新排序,該如何處理呢?

因為要隨機排序,所以需要產生一組亂數:

儲存格A2:=INT(RAND()*100)+ROW()/1000

複製儲存格A2到儲存格A2:A17。

公式:INT(RAND()*100)產生1~99的整數亂數。

公式中加上ROW()/1000的用意,是因為之前的整數亂數可能重覆,而ROW()在每列中會產生不一樣的數字。

儲存格D2:=VLOOKUP(LARGE(A$2:A$17,ROW(1:1)),A$1:B$17,2,FALSE)

複製儲存格D2到儲存格DA2:D17。

此公式利用LARGE函數依序找出A欄中第1大、第2大、第3大…的亂數,再藉由VLOOKUP函數於儲存格A$1:B$17中,找尋對應的班級。

每按一次F9鍵,班級就會重新排列。

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

在Excel中如果要輸入一個報名表,希望同一欄中的資料是唯一值(不可重覆),該如何處理?

例如在運動會一百公尺報名表(每班一名),希望班級欄位不要重覆,可以藉助資料驗證來把關。

儲存格A3:=IF(B4<>"",A3+1,"")

將儲存格A3往下複製到多個儲存格。

該公式的作用是,如果B欄有輸入資料時,序號會自動加1。

 

接著,選取儲存格B3,設定其資料驗證:

儲存格內允許:自訂

公式:=COUNTIF($B:$B,B3)=1

注意:B3要使用相對位址,因為之後要複製到B欄的其他儲存格。

將錯誤提醒也一併設定,如下:

如果在B欄中輸入了同一欄重覆的內容,則會顯示以下的對話框。

使用這個方式,有助於減少輸入相同值。

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

我國的身分證號碼是基於某一種規則之下產生,如何使用Excel來驗證身份證號碼是否為有效的號碼呢?

身分證號碼的第一碼是英文字母,代表的出生後入籍的縣市的代號,如下表所示:

而第2到第10個碼為阿拉伯數字,其中第2碼代表性別:1為男性、2為女性,第3碼至第9碼為流水號。最後一碼為檢查碼,而它的檢查方法透過以下的公式換算而來:

image02

(1) 驗算是否正確(正確:V,不正確:X)

儲存格B2:=IF(VALUE(RIGHT(A2,1))=10-MOD(SUM(K6:T6),10),"V","X")

(2) 判定縣市別

儲存格C2:=VLOOKUP(LEFT(A2,1),$F$2:$H$25,2)

(3) 判定性別

儲存格D2:=IF(MID(A2,2,1)="1","男","女")

image03

 

 

 

 

試著使用以下的公式產生一個亂數的號碼:

=CHAR(INT(RAND()*26+65))&INT(RAND()*2+1)&INT(RAND()*8999999+1000000)&INT(RAND()*10)

如果不想透過上述表格換算,而想直接在儲存格中判定,試試以下的公式:(以儲存格B3為例)

=IF(VALUE(RIGHT(A3,1))=MOD(10-MOD(MID(VLOOKUP(LEFT(A3,1),$F$2:$H$25,3),1,1)+MID(VLOOKUP(LEFT(A3,1),$F$2:$H$25,3),2,1)*9+MID(A3,2,1)*8+MID(A3,3,1)*7+MID(A3,4,1)*6++MID(A3,5,1)*5+MID(A3,6,1)*4+MID(A3,7,1)*3+MID(A3,8,1)*2+MID(A3,9,1)*1,10),10),"V","X")

如果想要縮短一點公式的長度,可以試試陣列公式:(以儲存格B4為例)(輸入要按Ctrl+Shift+Enter)

{=IF(VALUE(RIGHT(A4,1))=MOD(10-MOD(MID(VLOOKUP(LEFT(A4,1),$F$2:$H$25,3),1,1)+MID(VLOOKUP(LEFT(A4,1),$F$2:$H$25,3),2,1)*9+SUM(MID(A4,ROW(INDIRECT("2:9")),1)*(10-ROW(INDIRECT("2:9")))),10),10),"V","X")}

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

在Excel中,如果要建立一個請假時數的資料表,如何來輸入最少資料而且避免輸入錯誤呢?練習以下的做法:

 

如果你在日期的儲存格欄位中使用資料驗證(如下圖),設定日期要小於「=TODAY()」,則可以避免輸入未來日期,但是必須準確的輸入日期格式。

如果你的工作是週期性的,例如每七天輸入一次,則可以利用一個工作表建立以下的資料:

儲存格A7:=TODAY()

儲存格A6:=A7-1,複製儲存格A6到儲存格A1:A6。(建立最近七天的日期清單)

然後選取儲存格A1:A7,定義名稱為:dayList。

在日期欄位中的驗證準則設定為儲存格允許:清單,來源:「=dayList」。

如此輸入日期時,不僅不會輸入未來日期,也可以由選取最近的日期來輸入。

假別欄位的驗證準則也設定為儲存格允許:清單,來源:「事假,病假,公假」。

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

在Excel的工作表中使用公式,難免會有錯誤運算訊息,所以提供了許多對公式除錯的工具。

在 E2 儲存格中出現了 #DIV/0! 的錯誤,只要按一下 Ctrl + ] ,就會顯示該儲存格中的公式引用了那些儲存格,並選取這些儲存格。而如果你選取一個儲存格,按一下 Ctrl + [ ,會顯示那些儲存格中的公式引用了這個儲存格,並選取那些儲存格。

如果你想知道公式中來源的儲存格,可以使用[追蹤前導參照]按鈕,即可以標示該儲存格公式中引用了那些儲存格。如果你想知道某個儲存格被其他儲存格引用,則按一下[追蹤從屬參照]按鈕。只要按一下[移除箭號]按鈕,即可以隱藏這些藍色的指標。

選取錯誤的儲存格,按一下[評估值公式]按鈕,可以逐步的檢驗問題在那裡。

每按一下[評估值]按鈕,即會進行一個運算動作。如果按一下[逐步執行]按鈕,可以進行更細部的運算做觀察。(如下圖,顯示D2+D3的結果為#VALUE!,即D2+D3出現錯誤。)

你還可以透過[監看視窗]來觀察各個儲存格中運算公式和內容的變化,只要按一下[新增監看式],再選取想要監看的儲存格即可。

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

在Excel中的資料編輯列中,當你正在輸入函數名稱時,可以即時看到該函數的說明(如下圖),這個說明區是可以自行拖曳至其他位置的。如果尚未輸入完函數名稱,而在其列出的函數名稱上按二下滑鼠左鍵,Excel會代為輸入這個函數名稱。

當你輸入函數名稱為,再輸入第一個右括號時,則會顯示完整的引數提示。其中以 「[ ]」表示者為選用引數,即可以視需要輸入。

如果點選函數名稱(顯示藍色的超連結),則會顯示該函數的相關說明。

面對一個已輸入完成的函數,在其引數上(藍色超連結)按一下,則資料編輯列上會將該函數的引數內容,以反白顯示。

在資料編輯列中所用到的儲存格會以不同色彩表示,並會和儲存格上的框線色彩互相對照。

如果你拖曳儲存格上的色彩框線,則資料編輯列上的儲存格名稱也會隨之調整。

如果按一下資料編輯列上的「fx」符號,則會提供該函數的對話框,可以針對個別引數輸入,並可以看到相關的指引文字。 每個引數的右邊也會提供運算結果供參考。

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

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼