贊助廠商

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

搜尋本部落格文章資料

目前日期文章:201111 (33)

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

在 Excel 中如果你在工作表中按日輸入一些資料,做為簡單的行事曆(如下圖左)。現在,想要從這個行事曆資料中摘要出某一週的行事曆,及每天開啟資料夾時自動顯示當週的行事曆,該如何處理?

【準備工作】

將行事曆資料範圍設定名稱為:資料。

【指定週別】

(1) 首先,要求某一週的第一天(星期一):

儲存格E4:=DATE($F$1,1,1)-WEEKDAY(DATE($F$1,1,1),1)+1+($F$2-1)*7

DATE($F$1,1,1):取得某一年的1月1日之日期序列。

WEEKDAY(DATE($F$1,1,1),1):取得某一年的1月1日是星期幾。

DATE($F$1,1,1)-WEEKDAY(DATE($F$1,1,1),1)+1:取得某一年第一週的第一天的日期。

將上式加上($F$2-1)*7,即可取得任一週的第一天。

儲存格E5:=E4+1

複製儲存格E4,貼至儲存格E4:E10。

 

(2) 找出星期幾

儲存格F4:=E4

將儲存格E4的數值格式設定為星期幾格式

 

(3) 查詢工作項目

儲存格G4:=IFERROR(VLOOKUP(E4,資料,3,FALSE),"")

利用 VLOOKUP 函數查詢對應日期的第3欄(工作項目)的資料。

其中的 IFERROR 乃是為了某些日期沒有在行事曆上,可以避免產生錯誤訊息。

如果你使用較早版本的 Excel,沒有提供 IFERROR 函數,可以改用 ISERROR 函數:

儲存格G4:=IF(ISERROR(VLOOKUP(E4,資料,3,FALSE)),"",VLOOKUP(E4,資料,3,FALSE))

最後,複製儲存格F4:G4,貼至儲存格F4:G10。

 

【顯示當週】

如果你對指定週別的算法已有瞭解,則變通一下來求今日當週的資料。

儲存格F11:=TODAY()

儲存格G12:="第"&WEEKNUM(TODAY())&"週"

WEEKNUM 函數可以求得某一日期在當年度是第幾週。

儲存格E14:=DATE($F$1,1,1)-WEEKDAY(DATE($F$1,1,1),1)+1+(WEEKNUM(TODAY())-1)*7

複製儲存格E14,貼至儲存格E14:E20。

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

最近需要掃描網段中有那些 IP 被使用了,找了一下相關軟體,找到了 IP Seizer 軟體。這是一個免安裝的綠色軟體,功能陽春但簡單實用。

下載頁面:http://www.alexnolan.net/software/ip_address_scanner.htm

下載軟體:http://www.alexnolan.net/software/IPSeizer.exe

下載後免安裝,可以直接執行該程式。輸入起始IP和終止IP,按一下[Start]按鈕,即可開始掃描工作。

掃描過程中會顯示 IP 位址、主機名稱、MAC 位址、群組/網域名稱等。按一下[Save]按鈕,你可以將掃描後的資料儲存成文字檔。

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

推薦一個網站!

國家圖書館的遠距學園網站(https://cu.ncl.edu.tw/)提供有許多的免費課程,而有些課程也適用於公務人員的認證課程。使用這些課程前必須先行註冊才能使用,註冊時要使用一個可以驗證的郵件地址。

註冊網頁:http://www.ncl.edu.tw/sp.asp?xdurl=member/userRegisterLaw.asp

(註冊後的帳號/密碼,可以在圖家圖書館的相關網站中通用。)

可先檢視其課程介紹(https://cu.ncl.edu.tw/inner.php?course),課程由舊到新,有數百個課程可選:

要先選課後才能上課:

選課後立刻就能上課:

課程內容有很多和圖書館專業、公務人員相關和資訊電腦相關的課程,即然是免費課程,如果有適合自己的內容,就不要客氣好好利用一下。

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

在 Excel 的工作表中,如果指定二個起迄日期,要求出這兩個日期之間,每個星期幾的天數有幾天,該如何處理(參考下圖)?以下要分別以陣列公式和 SUMPRODUCT 函數來計算。

 

【方法一:使用陣列公式】

儲存格D2:{=SUM(IF(WEEKDAY(ROW(INDIRECT($A$2 & ":" & $A$5)),2)=ROW(1:1),1,0))}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。

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

本公式要利用陣列來運算,所以先取得兩個日期間的日期數字(每個日期都有一個數字代表)的陣列。

INDIRECT($A$2 & ":" & $A$5):將兩個日期數字轉換成 XXX:XXX 形式。

ROW(INDIRECT($A$2 & ":" & $A$5)):利用 ROW 函數取得兩個日期間的所有數字(所有日期)。

利用 WEEKDAY 函數判斷是否為 1 (是否等於ROW(1:1)),如果成立則給予 1,否則給予 0。

加總這些數字,即可得有幾個星期一的日數。

將儲存格D2住下複製時,ROW(1:1)=1 → ROW(2:2)=2→ … → ROW(7:7)=7,可求得每個星期幾的天數。

 

【方法二:使用SUMPRODUCT 函數】

儲存格D2:=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT($A$2 & ":" & $A$5)),2)=ROW(1:1)))

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

SUMPRODUCT 函數中使用「--」,是為了將 True/False 陣列轉換為 1/0 陣列,才能以數值計算。

原理和「方法一」一樣,因為 SUMPRODUCT 函數本來就是以陣列形式來運算。

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

Google 的翻譯(http://translate.google.com/?hl=zh-TW&tab=wT)功能是日常生活中常用的工具。要執行翻譯前必須先輸入文字,現在你可以使用語音輸入和虛擬鍵盤兩種方式來輸入文字。

(1) 使用語音輸入

如果你要輸入英文來翻譯,則可以使用語音輸入。當你已安裝好麥克風,按一下[麥克風]圖示,即可以開始說話。

如果你唸的夠標準,Google 會自動轉換成英文字幫你輸入。

(2) 使用虛擬鍵盤

有些國家的文字無法用你的鍵盤來輸入,沒關係,Google 提供了虛擬鍵盤方便你輸入文字。選取[原文是英文]的下拉式清單,選取一種文字。(不是每種文字都有虛擬鍵盤,例如「日文」沒有提供虛擬鍵盤)

使用虛擬鍵盤可以直接敲打鍵盤上的按鍵,即可對照虛擬鍵盤上的文字來輸入。

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

在 Excel 中有一個每天會增加三筆資料的報表(如下圖左),如何根據不同產品,產生依星期和月份統計的報表(累計下載和平均檢閱)(參考下圖右)?

因為每天都會增加三筆資料,所以資料的範圍並不固定。

 

【準備工作】

首先要定四個名稱,以便簡化公式的複雜度。由於每天都有三筆資料加入,所以資料的範圍不固定,因此以OFFSET函數來定義資料範圍。在名稱管理員中做以下的定義:

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

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

(3) 檢閱數:=OFFSET(工作表1!$C$2,,,COUNTA(工作表1!$A:$A)-1,)

(4) 下載數:=OFFSET(工作表1!$D$2,,,COUNTA(工作表1!$A:$A)-1,)

公式中以 COUNTA 函數來抓取目前在 A 欄中有多少筆資料。

 

【計算累計下載 - 使用 SUMPRODUCT 函數】

儲存格G2:=SUMPRODUCT(--(產品=G$10),--(WEEKDAY(日期,2)=ROW(1:1)),下載數)

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

其中 WEEKDAY 函數中的參數 2,乃定義數字 1(星期一) 至 7(星期日):

ROW(1:1)=1,往下複製時會自動變為ROW(2:2)=2 –> ROW(3:3)=3 –> … –> ROW(7:7)=7。

利用 SUMPRODUCT 函數取得「符合產品名稱的 True/False陣列、符合星期幾的 True/False陣列、下載數」來運算乘積和。

SUMPRODUCT 函數中使用「--」,是為了將 True/False 陣列轉換為 1/0 陣列,才能以數值計算。

SUMPRODUCT(--(產品=G$10),--(WEEKDAY(日期,2)=ROW(1:1)),下載數) 也可以寫成:

SUMPRODUCT((產品=G$10)*(WEEKDAY(日期,2)=ROW(1:1))*下載數)

 

【計算累計下載 - 使用陣列公式】

儲存格G2:{=SUM(IF(產品=G$10,IF(WEEKDAY(日期,2)=ROW(1:1),下載數,FALSE),FALSE))}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。

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

IF 函數中的 FALSE 參數在此例中使用「0」或「空白」所得的結果是一樣的。

 

由以上「計算累計下載」的例子,來自行練習「計算平均檢閱」。

【計算平均檢閱 - 使用 SUMPRODUCT 函數】

儲存格G11:=SUMPRODUCT((產品=G$10)*(MONTH(日期)=ROW(9:9))*檢閱數)/SUMPRODUCT((產品=G$10)*(MONTH(日期)=ROW(9:9)))

複製儲存格G11,貼至儲存格G11:I13。

公式中透過 MONTH 函數來求得日期代表的月份,因為題目要求9,10,11月,所以判斷其等於ROW(9:9)=9。往下複製時可以產生10,11。

 

【計算平均檢閱 - 使用陣列公式】

儲存格G11:{=AVERAGE(IF(產品=G$10,IF(MONTH(日期)=ROW(9:9),檢閱數,FALSE),FALSE))}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。

複製儲存格G11,貼至儲存格G11:I13。

 

【補充說明】

相關函數說明,請參閱微軟網站。

SUMPRODUCThttp://office.microsoft.com/zh-tw/excel-help/HP010342935.aspx

SUMPRODUCT:傳回各陣列中所有對應元素乘積的總和。

語法:SUMPRODUCT(array1, [array2], [array3], ...)

array1:要求對應元素乘積和的第一個陣列引數。

array2, array3,...:要求對應元素乘積和的第 2 個到第 255 個陣列引數。

註:各陣列必須有相同的維度(相同的列數,相同的欄數)。否則會傳回錯誤值#VALUE!。並且會將所有非數值資料的陣列元素當成0來處理。

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

Google 的swiffy 網站(http://www.google.com/doubleclick/studio/swiffy/) 提供了將Flash SWF檔轉換為 HTML5 格式檔案的功能。

你可以上傳一個檔案,在線上執行轉換。頁面上可以比較轉換前後的結果:

點選其提供的超連結或是QRCode,可以線上瀏覽這個效果。或是另存其提供的超連結,即可以取得這 HTML5 的檔案。但是使用時要注意,其只能在支援 HTML5 的瀏覽器上使用。

在這個頁面上也提供一個擴充程式,這是由 ADOBE 提供的 mxp 檔,下載安裝後可以由 Adobe Extension Manager 中執行轉換。

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

這次要來練習 COUNTIF 和 SUMIF 公式。

【準備工作】

將各欄位資料設定名稱以方便在公中使用。

選取資料範圍(本例為儲存格A1:A25),按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」選項。定義四個名稱:「序號、姓名、日期、數量」。

image[3]

選取儲存格F6,選取[資料/資料工具]中的「資料驗證」按鈕。在[儲存格允許]中選取「清單」,在來源中輸入:「=INDIRECT(“日期")」。

儲存格F6即可以下拉式清單方式選取日期:

image[7]

 

【公式輸入】

(1) 儲存格F2:=COUNT(D2:D10)

計算儲存格D2:D10中的數字個數。

(2) 儲存格F3:=COUNTIF(數量,">200")

計算「數量」欄位中大於200的個數。

(3) 儲存格F4:=COUNTIF(姓名,"陳*")

計算所有姓「陳」者的數量。

COUNTIF 和 SUMIF 函數中可以使用「?、*」萬用字元。

(4) 儲存格F5:=COUNTIF(日期,">2011/3/1")

計算「大於 2011/3/1」 的日期個數。將一個固定日期置於「" "」中來運算。

(5) 儲存格F7:=COUNTIF(日期,">"&F6)

將日期置於一個儲存格中再取用,必須以「&」來串接條件運算符號和儲存格位址。

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

儲存格F6:{=COUNT(IF(日期>F6,數量,Fasle))}

(6) 儲存格F8:=SUMIF(數量,">250")

計算「數量」欄位中超過 250 者的總和。

(7) 儲存格F9:=SUMIF(日期,">"&F6,數量)

計算大於某個日期之後的數量總和。

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

儲存格F6:{=SUM(IF(日期>F6,數量,FALSE))}

 

【補充說明】

COUNTIFhttp://office.microsoft.com/zh-tw/excel-help/HP010342346.aspx

COUNTIF:計算範圍內符合您所指定單一條件準則的儲存格總數。

語法:COUNTIF(range, criteria)

range:要列入計算的一個或多個儲存格,包括數字或包含數字的名稱、陣列或參照;空白或文字值會忽略。

criteria:定義要將哪些儲存格列入計算的準則,可以是數字、表示式、儲存格參照或文字字串。

可以在準則中使用萬用字元:問號 (?) 及星號 (*);問號可以替代任何單一字元;星號可以替代任何一系列的字元。如果確實要尋找實際的問號或星號,請在該字元前輸入波狀符號 (~)

 

SUMIFhttp://office.microsoft.com/zh-tw/excel-help/HP010342932.aspx

SUMIF:將準則套用到將進行加總的相同值上。

Range:依據準則進行評估的儲存格範圍。每個範圍中的儲存格都必須是數字,或包含數字的名稱、陣列或參照位址。空白或文字值會被忽略。

Criteria:用以定義要加總之儲存格的準則,可以是數字、表示式、儲存格參照、文字或函數。例如,criteria 可以由 32">32"B532"32""apples" TODAY() 來表示。

重要事項:任何文字準則或包含邏輯符號或數學符號的準則都必須使用雙引號 (") 括住。如果準則為數值,則不需要使用雙引號。

sum_range:想要加總 range 引數內指定範圍之外的儲存格,這個引數可指定實際要加總的儲存格。如果省略 sum_range 引數,則 Excel 會加總 range 引數內指定的儲存格 (即與套用準則相同的儲存格)

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

使用 Excel 來做數學運算是很方便的,例如以下的六個例子以SUMPRODUCT函數配合ROW函數來運算。

SUMPRODUCT函數可以將多組陣列中的數字相乘後加總,但是其中的參數如果只使用一組陣列,則只會將其加總。在此例中以ROW(1:20)來表示 1,2,3, … , 20 組成的陣列。

儲存格B2:=SUMPRODUCT(ROW(1:20))

儲存格B3:=SUMPRODUCT(1/ROW(1:20))

儲存格B4:=SUMPRODUCT((ROW(1:20))^2)

儲存格B5:=SUMPRODUCT(1/(ROW(1:20)^2))

儲存格B6:=SUMPRODUCT(ROW(1:20)^0.5)

儲存格B7:=SUMPRODUCT(1/(ROW(1:20)^0.5))

【延伸學習】

以上這些運算式,如果改以陣列公式來運算,該如何處理?

以下所有陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。

儲存格B2:{=SUM(ROW(1:20))}

儲存格B3:{=SUM(1/ROW(1:20))}

儲存格B4:{=SUM(ROW(1:20)^2)}

儲存格B5:{=SUM(1/ROW(1:20)^2)}

儲存格B6:{=SUM(ROW(1:20)^0.5)}

儲存格B7:{=SUM(1/ROW(1:20)^0.5)}

 

【補充說明】

相關函數說明,請參閱微軟網站。

SUMPRODUCThttp://office.microsoft.com/zh-tw/excel-help/HP010342935.aspx

SUMPRODUCT:傳回各陣列中所有對應元素乘積的總和。

語法:SUMPRODUCT(array1, [array2], [array3], ...)

array1:要求對應元素乘積和的第一個陣列引數。

array2, array3,...:要求對應元素乘積和的第 2 個到第 255 個陣列引數。

註:各陣列必須有相同的維度(相同的列數,相同的欄數)。否則會傳回錯誤值#VALUE!。並且會將所有非數值資料的陣列元素當成0來處理。

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

在 Excel 中根據一個日期和項目所構成的銷售額資料表(參考下圖左),現在要依月份和星期幾來摘要統計表(參考下圖右),該如何處理?這個題目要使用 SUMPRODUCT、MONTH、WEEKDAY、ROW 等函數。

 

【準備工作】

首先,建立名稱,讓後續的公式更簡捷。

選取資料範圍(本例為儲存格A1:C26),按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」,建立三個名稱:日期、項目、銷售額。

 

【依月份統計】

儲存格F2:=SUMPRODUCT((MONTH(日期)=ROW(1:1))*(項目=F$15)*銷售額)

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

ROW(1:1) 往下複製時,ROW(1:1)=1 → ROW(2:2)=2 → … → ROW(12:12)=12。

配合 MONTH 函數求得的月份:1~12。

 

【依星期幾統計】

儲存格F16:=SUMPRODUCT((WEEKDAY(日期,1)=ROW(1:1))*(項目=F$15)*銷售額)

複製儲存格F16,貼至儲存格F16:J22。

WEEKDAY(日期,1) 中的參數1,表示數字 1 (星期一) 至數字 7 (星期日)。其他參數參考下圖:

 

【延伸學習】

(1) 如果上述之兩個報表,只要統計次數而非總和,該如何處理?

儲存格F2:=SUMPRODUCT((MONTH(日期)=ROW(1:1))*(項目=F$15))

儲存格F16:=SUMPRODUCT((WEEKDAY(日期,1)=ROW(1:1))*(項目=F$15))

 

(2) 如果想要以陣列公式來統計這些資料,該如何處理?

儲存格F2:{=SUM(IF(MONTH(日期)=ROW(1:1),IF(項目=F$15,銷售額,FALSE),FALSE))}

儲存格F16:{=SUM(IF(WEEKDAY(日期,1)=ROW(1:1),IF(項目=F$15,銷售額,FALSE),FALSE))}

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

 

【補充資料】

相關函數說明,請參考微軟網站:

SUMPRODUCThttp://office.microsoft.com/zh-tw/excel-help/HP010342935.aspx

SUMPRODUCT:傳回各陣列中所有對應元素乘積的總和。

語法:SUMPRODUCT(array1, [array2], [array3], ...)

array1:要求對應元素乘積和的第一個陣列引數。

array2, array3,...:要求對應元素乘積和的第 2 個到第 255 個陣列引數。

註:各陣列必須有相同的維度(相同的列數,相同的欄數)。否則會傳回錯誤值#VALUE!。並且會將所有非數值資料的陣列元素當成0來處理。

 

WEEKDAYhttp://office.microsoft.com/zh-tw/excel-help/HP010343015.aspx

WEEKDAY:傳回符合日期的星期。給定的日預設為介於1(星期日)7(星期六)之間的整數。

語法:WEEKDAY(serial_number,[return_type])

serial_number:要找的日期的代表序列值。

return_type:決定傳回值類型的數字。

 

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

在 Excel 的工作表中,如果想產生一組不重覆的隨機亂數,例如:1~36,分別置於 6X6 儲存格中,該如何處理?(參考下圖)。

【準備工作】

1. 在儲存格H1中輸入公式「=RAND()」,按下 Enter 鍵後,即會產生一個亂數值。

2. 複製儲存格H1,貼至儲存格H1:H36,共計 36 個亂數。(每按一下 F9 鍵,即可重新產生一組亂數。)

 

【產生亂數】

提供二組公式來產生不重覆的 1~36 的亂數值:

(1) 儲存格A1:=RANK(INDIRECT("H"&((ROW(1:1)-1)*6)+COLUMN()),$H$1:$H$36):

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

公式的原理為:

將亂數值來拿排序(RANK函數),產生 1~36 的數值(基不上名次重覆的機率應該是很低的)。

INDIRECT("H"&((ROW(1:1)-1)*6)+COLUMN()):將 6X6 的儲存格對照到儲存格H1:H26。

(2) 儲存格A1:=RANK(OFFSET($H$1,(ROW(1:1)-1)*6+COLUMN(A:A)-1,,,),$H$1:$H$36)

本式的原理和 (1) 很接近,只是將 INDIRECT 函數以 OFFSET 函數來取代,以取得位址。

 

【延伸練習】

試著產生 1~64 的不重覆亂數,置於 8X8 的儲存格中。假設亂數置於J欄中。

 

……

 

參考答案:

儲存格A1:=RANK(INDIRECT("J"&((ROW(1:1)-1)*8+COLUMN())),$J$1:$J$64)

儲存格A1:=RANK(OFFSET($J$1,(ROW(1:1)-1)*8+COLUMN(A:A)-1,,,),$J$1:$J$64)

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

微軟提供了一套免費、免安裝的安全掃描工具:Microsoft Safety Scanner。

下載頁面:http://www.microsoft.com/security/scanner/zh-tw/default.aspx

在其頁面上提到:

Microsoft Safety Scanner 是可免費下載的安全性工具,可隨時用來掃描電腦並移除病毒、間諜軟體和其他惡意軟體。 並可以和現有的防毒軟體搭配運作。

特別要注意到: Microsoft Safety Scanner 將於下載後的第 10 天到期。 若要使用最新的反惡意程式碼定義檔重新執行掃描,必須重新下載並執行 Microsoft Safety Scanner。且Microsoft Safety Scanner 不可取代提供持續防護的防毒軟體程式。

下載時需選取你的電腦作業系統(Windows)是 32 位元版還是 64 位元版。

這個程式(msert.exe)是一個免裝軟體,依其步驟選取掃描的類型進行掃描。選取不同的類型,所花費的時間也不相同。

掃描過程需要一段時間的運作:

執行完會產生一份報告,並列出偵測到並且已移除的軟體。如果看到以下的畫面,表示你的電腦是健康的。(但誰能保證呢?)

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

每一本書的背面都有大家熟悉的 ISBN(國際標準書碼),利用這個 ISBN,我們要在 Excel 中練習檢核輸入的 ISBN 是否正確。首先,介紹檢核碼的計算方法。

【檢核碼的計算方法】

以下資料取自:http://zh.wikipedia.org/wiki/國際標準書號

(1) 10碼

假設某國際標準書號號碼前 9 位是:7-309-04547

計算加權和S:S = 7×10 + 3×9 + 0×8 + 9×7 + 0×6 + 4×5 + 5×4 + 4×3 + 7×2 = 226

計算 S ÷ 11 的餘數M:M = 226 mod 11 = 6

計算11 - M 的差N:N = 11 − 6 = 5

如果N = 10,校驗碼是字母「X」

如果N = 11,校驗碼是數字「0」

如果N為其他數字,校驗碼是數字N。

所以,本書的校驗碼是5,故該國際標準書號為 ISBN 7-309-04547-5。

(2) 13位

假設某國際標準書號號碼前 12 位是:978-986-181-728

計算加權和S:S = (9x1)+(7x3)+(8x1)+(9x3)+(8x1)+(6x3)+(1x1)+(8x3)+(1x1)+(7x3)+(2x1)+(8x3) = 164

計算 S÷10 的餘數M:M = 164 mod 10 = 4

計算10 - M 的差N:N = 10 − 4 = 6

如果N = 10,校驗碼是數字「0」

如果N為其他數字,校驗碼是數字N。

所以,本書的校驗碼是6。完整的國際標準書號號碼為 ISBN 978-986-181-728-6

 

【實作練習一:10碼檢核】

根據上述的檢核方式,設計以下的做法:

儲存格C2:=MID($A$2,ROW(1:1),1)

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

儲存格F2:=SUM(E2:E11)

儲存格F4:=MOD(F2,11)

儲存格F6:=IF(F4=0,"正確","錯誤")

如果不想這麼複雜的過程來處理,而想以一個儲存格來檢核,該如何處理?需要用到陣列公式!

儲存格F6:{=IF(MOD(SUM(MID(A2,ROW(1:10),1)*(11-ROW(1:10))),11)=0,"正確","錯誤")}

陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。

公式解釋:

MID(A2,ROW(1:10),1):取出儲存格A2中的每一位數。

11-ROW(1:10):產生 10, 9, 8, … , 2, 1 的數列。

SUM(MID(A2,ROW(1:10),1)*(11-ROW(1:10))):將以上二式的每個數字兩兩相乘,再全部加總。

利用 MOD 函數求得除以 11 的餘數。如果餘數為 0,則顯示「正確」,如果餘數不為 0,則顯示「錯誤」。

 

【實作練習二:13碼檢核】

儲存格C14:=MID($A$14,ROW(1:1),1)

複製儲存格C14,貼至儲存格C14:C26。

儲存格F14:=SUM(E14:E26)

儲存格F16:=MOD(F14,10)

儲存格F18:=IF(F16=0,"正確","錯誤")

如果不想這麼複雜的過程來處理,而想以一個儲存格來檢核,該如何處理?需要用到陣列公式!

儲存格F18:{=IF(MOD(SUM(MID(A14,ROW(1:13),1)*(MOD(MOD(ROW(1:13),2)+2,3)+1)),10)=0,"正確","錯誤")}

陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。

公式解釋:

公式原理參考實作練習一。其中:

MOD(MOD(ROW(1:13),2)+2,3)+1:可以產生 1, 3, 1, 3, …, 3, 1 的數列。

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

在 Excel 中在輸入資料時,如果想要根據起迄的日期,在下拉式清單中選取這段範圍的日期,該如何處理(參考下圖左 )?

 

【動作一:產生日期數列】

為了在儲存格可以選取起迄日期範圍內的日期,我們必須先產生這段範圍的日期(參考上圖右)。參考以下的做法:

儲存格E2:=D2

儲存格E3:=IFERROR(IF(E2+1<=$D$4,E2+1,#VALUE!),"")

複製儲存格E3,貼至儲存格E3:E101。(本例限定日期區間在100天內)

其原理為如果超過結束日期時,即給予錯誤訊息「#VALUE!」,根據這個錯誤訊息,顯示空白。

 

【動作二:產生下拉式清單】

要產生下拉式清單,參考以下的步驟:

1. 選取儲存格A2。

2. 選取[資料/資料工具]區中的「資料驗證」選項。

3. 在[儲存格內允許]下拉式清中選取「清單」。

4. 在[來源]文字方塊中輸入「=OFFSET($E$2,,,COUNT($E$2:$E$100),)」

(特別注意相關的儲存格位址要使用絶對參照方式)

說明:

使用 OFFSET($E$2,,,COUNT($E$2:$E$100),) 的目的是為了產生一個動態的位址,其中「COUNT($E$2:$E$100)」可求得在儲存格E2:E100中數字的個數(每個日期代表一個數字,之前已設定非起迄日期範圍內產生空白-非數字)。

 

【步驟三:在儲存格中使用】

1. 將儲存格A1,往下各列貼上。

2. 使用下拉式清單來選取一個日期。

 

【思考】

你可以試試:

1. 在下拉式清單中只要顯示起迄日期間星期一(或其它)的日期。

2. 在下拉式清單中只要顯示起迄日期間非假日或星期六日的日期。

3. 在下拉式清單中只要顯示起迄日期間日期尾數為 5 的日期。

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

在 Windows 7 中使用「傳送到」功能,是一個方便有用的功能。例如你在檔案上按一下右鍵,選取[傳送到]選項,即可選取想要傳送的目的地。例如:虛擬印表機、壓縮的資料夾、郵件收件者等。

這個好用的功能,如果想要自訂想要的傳送標的,該如何處理?

 

【開啟[傳送到]資料夾】

首先,來認識[傳送到]資料夾。

1. 在[開始]功能表的[搜尋程式及檔案]方塊中輸入:shell:sendto

2. 點選找到的這個資料夾,即為「傳送到」的資料夾(參考下圖)。這個資料夾的真實位址為:

C:\Users\XXXXX\AppData\Roaming\Microsoft\Windows\SendTo

(其中 XXXXX 是你登入Windows 7 的帳號)

觀察這個[傳送到]資料夾,你可以發現其中大多是一些捷徑。

 

【自訂[傳送到]資料夾】

如果你想新增一個自己的傳送標的,則可以:

1. 在[傳送到]資料夾中,在空白處按一下右鍵,選取新增一個捷徑。

例如:新增一個資料夾位置的捷徑,本例為:E:\Google Downloads。

完成後,在某個檔案上按一下右鍵來測試,已可以傳送到自訂的位置了。

除了指定傳送到某個資料夾,你也可以自行練習傳送到某個應用程式等。

 

【特殊用法】

如果你在某個檔案上,按著 Shift 鍵,再按滑鼠右鍵,你會發現到傳送到的內容比原先多了一些選項。

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

在 Excel 中有一個資料表含有項目A和項目B的數列(參考下圖左),若要將A欄和B欄相乘的結果加總,該如何處理(參考下圖右)?

你可以仿照C欄,將A欄和B欄相乘,然後將C欄的資料加總。但是我們要練習的是以一個儲存格即要完成這個工作。參考以下的作法:

 

【準備工作】

選取儲存格A1:B22,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:項目A、項目B。

 

【計算:所有 AXB 的總和】

(1) 使用 SUMPRODUCT 函數可以方便的計算乘積:

儲存格G2:=SUMPRODUCT(項目A,項目B)

(2) 使用陣列公式(輸入完成,要按 Ctrl+Shift+Enter 鍵。)

儲存格G2:{=SUM(項目A*項目B)}

將 (1) 和 (2) 對照比較一下,很容易可以理解其用法。

 

【計算:所有 A/B 的總和】

如果要計算 A/B 的總和,就沒有像 SUMPRODUCT 這類的函數可以使用,不過,你可以使用陣列公式(輸入完成要按 Ctrl+Shift+Enter 鍵。)

儲存格G3:{=SUM(項目A/項目B)}

 

【計算:A>50 且 B>5 的個數】

(1) 儲存格G4:=SUMPRODUCT(--(項目A>50),--(項目B>5))

在 SUMPRODUCT 函數中加入條件判斷,其中的「--」,乃是將 True / False 陣列轉換成 1 / 0 的陣列。

(2) 儲存格G4:{=SUM((項目A>50)*(項目B>5))}

使用陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。

將 (1) 和 (2) 對照比較一下,很容易可以理解其用法。

 

【計算:A>50 且 B>5 的 AXB 總和】

儲存格G5:{=SUM(IF(項目A>50,項目A)*IF(項目B>5,項目B))}

使用陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。

 

【註記】

希望藉由這四個連續的不同運算,能對陣列公式的理解有一點幫助。如果真的無法使用陣列公式,也是要多一些輔助欄位,多一些運算式也是能完成的。能正確運算的方法都是好方法!並非一定要使用陣列公式才是比較好的。

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

在 Excel 中取得一個資料表(如下圖左),如果要將項目區分成偶數和奇數分列(如下圖中和下圖右),該如何處理?本題可以使用陣列公式來完成。

【準備工作】

選取儲存格A1:B26,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」,建立名稱:序號、項目。

 

【方法一:依數字大小順序排列】

儲存格D2:{=IFERROR(SMALL(IF(MOD(項目,2)=0,項目),ROW(1:1)),"")}

儲存格E2:{=IFERROR(SMALL(IF(MOD(項目,2)=1,項目),ROW(1:1)),"")}

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

IF(MOD(項目,2)=0,項目):取出項目欄位中除以2的餘數為0者(偶數)的項目陣列。

利用 SMALL 函數由小到大取出符合的數字。

利用 IFERROR 函數將查不到資料的錯誤訊息以空白顯示。

此結果即可以將數列以由小到大的順序來呈現。

 

【方法二:依原來數列順序排列】

儲存格G2:{=IFERROR(INDEX(項目,SMALL(IF(MOD(項目,2)=0,序號),ROW(1:1))),"")}

儲存格H2:{=IFERROR(INDEX(項目,SMALL(IF(MOD(項目,2)=1,序號),ROW(1:1))),"")}

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

原理同方法一。

IF(MOD(項目,2)=0,序號):取出項目欄位中除以2的餘數為0者(偶數)的序號陣列。

再利用 INDEX 函數,並使用序號陣列,來索引項目陣列中的值。

此結果即可以原先的數列順序來呈現。

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

在你的 Excel 工作表中,如果有多人填了相同表格想要彙整在一起,可能不是件容易的事。試試 Excel 中的「合併彙算」功能。

【例舉】

以下以二個工作表代表多個工作表,其格式完全一樣,參考下圖:

 

【執行】

1. 選取工作表3的儲存格B1。

2. 選取[資料/資料工具]之中的「合併彙算」按鈕。

3. 在[合併彙算]對話框中,選取一個你要執行的函數,本例選取「平均」。

其中可執行:加總、項目個數、平均值、最大值、最小值、乘積、數字項個數、標準差、母體標準差、變異值、母體變異值等。

4. 在[參照位址]方塊中選取一個工作表1中的儲存格範圍。

5. 按一下[新增]按鈕。重覆步驟3和4,將工作表2中的資料代入。

6. 在[標籤名稱來自]區中勾選「頂端列」選項。

7. 按一下[確定]按鈕。

 

【結果】

二個工作表資料的平均值,會自動填入儲存格B2:C21,並會自動產生標題(儲存格B1:C1)。

如果你在[合併彙算]對話框中,勾選了「建立資料來源的連結」選項,則 Excel 會幫你建構此資料表的群組,並立公式(本例為 AVERAGE 函數)。

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

Google 設計了 Analytics 分析工具,讓網站可以分析訪客的種行為與資訊,這可能也會產生一些資料搜集上的問題。所以 Google 也針對這方面的問題,開發了「Google Analytics (分析) 不透露資訊瀏覽器外掛程式」,可以阻止你使用的瀏覽器將網站造訪資訊傳遞給 Google Analytics。

此瀏覽器外掛程式適用於 Internet Explorer、Google 瀏覽器、Mozilla Firefox、Apple Safari 和 Opera。

網址:http://tools.google.com/dlpage/gaoptout?hl=zh-TW

按下按鈕以取得程式:

你會被轉到 Google 的 websotre,在此下載安裝:

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

【觀察】

在 Excel 中的運算結果,其有效位數可達 15 位,大部分在儲存格上會以四捨五入方式來顯示,但是取用這個儲存格來運算時,並不是以「顯示值」來運算,而是以「實際值」來運算。

以下圖為例,在C欄中是A欄/B欄的運算結果,再取小數點3位。如果以C欄的結果來運算,在D欄中顯示C欄X2的結果。

 

【改變】

如果想要以顯示值來運算時,該如何處理呢?你可以這樣做:

1. 選取[檔案/選項]。

2. 在[計算此活頁簿時]區域中,勾選「以顯示值為準」。

Excel 會提醒你,資料將會永遠失去其精準度。

參考下圖,D欄中的運算結果已是以C欄「顯示值」來運算。(上第1圖比較一下)

 

【重要註記】

「以顯示值為準」的設定是以活頁簿為單位,所以啟用此功能時,所有活頁簿中的運算都會以當時的顯示值來運算。而新增一個活頁簿時,該活頁簿的預設值又會回到沒有勾選「以顯示值為準」。

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

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

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼