贊助廠商

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

搜尋本部落格文章資料

目前日期文章:201110 (65)

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

在數學中常見的運算式:

如果要以 Excel 來求得結果,要如何運算?之前文章曾提到以循環參照之反覆運算來求結果:

火星人的天空之城: Excel-使用循環參照來運算

 

【基本運算】

image

以公式代入2 為例:

儲存格B3:=B2^0.5

儲存格B4:=(B$2-B3)^0.5

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

你會發現運算結果愈來愈趨近於「1」,也就是在本例中可得:不須計算至無窮多個,即可得到運算結果。

 

【延伸運算】

同樣原理套用在當代入 6, 12, 20 時,也可以得到整數結果(分別為 2, 3, 4 )。

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

Google 又針對 Google 文件新增了非常多的簡報功能,目前你可以選擇試用。你可以選取頁面最右上角的「設定」(尺輪狀圖示),在「編輯」項下啟用「使用最新版的簡報編輯器建立新的簡報。」

進入新版的啟動畫面:(新的繪圖工具、製作動畫、協同合作)

可以挑選一個一主題樣式:

進入編輯畫面:

你可以看到一些新增的功能,例如插入文字藝術、插入動畫、新的排列功能表、新的格式功能表等。

當你在[投影片]功能表選擇變更轉場效果,可以設定投影片的轉場動畫。

當你選取了一個物件時,也可以為這個物件設定動畫效果。

試用看看,很快就可以上手的。

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

在 Excel 中取得一個學生考試成績表(如下圖),其中每個人的平均分數是一個加權平均。

試著建立一個可以藉由改變微調按鈕,可以顯示每個學生的成績和平均成績的對照表,和動態的圖表。

【處理成績表】

1. 計算加權平均

儲存格H3:=SUMPRODUCT($C$1:$G$1,C3:G3)/SUM($C$1:$G$1)

2. 計算名次

儲存格I3:=RANK(H3,$H$3:$H$22)

複製儲存格H3:I3,往下各列貼上。

 

【處理個人成績】

1. 在[開發人員/控制項]下,選取[表單控制項]的「微調按鈕」。

2. 在微調按鈕上按一下右鍵,選取「控制項格式」。

3. 設定最小值:1、最大值:20、遞增值1,儲存格連結:$A$26。

4. 由成績表查詢得到個人成績。

儲存格B26:=INDEX($A$3:$I$22,MATCH($A$26,$A$3:$A$22,0),COLUMN(B:B))

複製儲存格B26,貼至儲存格B26:I26。

MATCH($A$26,$A$3:$A$22,0)

以儲存格A26的內容找到資料在資料表中的第幾列。

INDEX($A$3:$I$22,MATCH($A$26,$A$3:$A$22,0),COLUMN(B:B))

利用查表方式求得指定列的每一欄資料,其中COLUMN(B:B)在往右複製時會變成COLUMN(C:C)、COLUMN(D:D)、…,可以求得第2欄、第3欄、第4欄、…的資料。

 

【處理個人圖表】

1. 選取儲存格C25:G27。

2. 選取[插入/圖表]區中的[直條圖/群組直條圖]。

3. 在[平均]數列上按一下右鍵,選取「變更數列圖表類型」選項。

4. 選取「折線圖」。

 

【進階處理個人圖表】

如果你不想透過個人成績表來動態查詢成績再製作圖表,則可以使用以下的方法:

1. 先定義一個名稱:DATA

參照到:=OFFSET(工作表1!$C$3,MATCH(工作表1!$A$26,工作表1!$A$3:$A$22,0)-1,,,5)

注意到範圍要選「工作表1」(指定給參照範圍在工作表1)

相關位址請使用絶對參照,例如:工作表1!$A$26、工作表1!$A$3:$A$22等。OFFSET函數會產生一個動態的位址,例如儲存格A26為1時,位址為C3:G3,而儲存格A26為2時,位址為C4:G4,…。

2. 選取儲存格C2:G3,按照 Ctrl 鍵,再選取儲存格C23:G23。

(為了便於說明,下圖中將部分列予以隱藏。)

3. 仿【進階處理個人圖表】說明之方式建立圖表。

4. 在圖表中點選個人成績數列(藍色)。

資料編輯列顯示:=SERIES(,工作表1!$C$2:$G$2,工作表1!$C$3:$G$3,1)

5. 在資料編輯列中,將「工作表1!$C$3:$G$3」改成「DATA」。

透過微調按鈕的變動,也可以在一個圖表中,顯示每一個人的成績圖表。

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

在 Excel 中取得一個日期數列,想要找出每個星期幾的數量,該如何處理?(參考下圖)

【準備工作】

為了方便說明,先定義各個資料名稱。

1. 選取儲存格A1:B21,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」選項。定義名稱:「日期、星期」。

【公式一:使用輔助欄位】

先找出每個日期的星期幾數值,以星期日到星期六為1~7。

儲存格B2:=WEEKDAY(A2,1)

利用 WEEKDAY 函數來求星期幾數值,其中的參數1,可以將星期日到星期六對應為1~7。

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

儲存格E2:=COUNTIF(星期,ROW(1:1))

利用 COUNITIF 函數統計在「星期」欄位中1~7的數量,即為星期幾的數量。

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

 

【公式二:使用陣列公式】

如果使用陣列公式,將可以不需要使用輔助欄位。

儲存格E2:={SUM(IF(WEEKDAY(日期,1)=ROW(1:1),1,0))}

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

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

公式的意義:

IF(WEEKDAY(日期,1)=ROW(1:1),1,0):判斷 WEEKDAY 函數取得的星期幾數值,如果為 1,則輸出1,否則輸出 0。

利用 SUM 函數將這些 0/1 的陣列加總即為數量。

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

有網友問到,在 Excel 中取得一個資料表(如下圖左),如果想要在一個儲存格中選取某一個類別,即能篩選出所有該類別的資料(如下圖右)。

你可以試著使用「自動篩選」即可方便篩選出想要類別的資料,但是如果想要以公式形式呈現,方便改變類別後,即能馬上得到結果,該如何處理?

 

【準備工作】

本例需要使用陣列公式,為了方便說明,先定義各個資料名稱。

1. 選取儲存格A1:D21,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」選項。定義名稱:「項目、類別、編號、數量」

2. 選取儲存格A1:D21,由[公式/已定義名稱/名稱管理員]中定義名稱為:「資料」。

3. 選取儲存格F2,在[資料/資料工具/資料驗證]中設定儲存格內允許:清單,來源:「A,B,C,D,E」。

 

【輸入公式】

儲存格G2:{=IFERROR(INDEX(資料,SMALL(IF(類別=$F$2,項目,FALSE),ROW(1:1))+1,3),"")}

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

儲存格H2:{=IFERROR(INDEX(資料,SMALL(IF(類別=$F$2,項目,FALSE),ROW(1:1))+1,4),"")}

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

複儲存格G2:H2,往下各列貼上即可。

 

【公式說明】

以儲存格G2為例。

儲存格G2:{=IFERROR(INDEX(資料,SMALL(IF(類別=$F$2,項目,FALSE),ROW(1:1))+1,3),"")}

IF(類別=$F$2,項目,FALSE)

將「類別」中符合儲存格F2內容者對應「項目」,否則給予Fasle。以本例可得 { Fasle, Fasle, 2, 3, Fasle, Fasle, 7, … } 陣列。

SMALL(IF(類別=$F$2,項目,FALSE),ROW(1:1))

取得前述陣列中的第1個、第2個、…最小值,本例為 2,3,7, … 。

INDEX(資料,SMALL(IF(類別=$F$2,項目,FALSE),ROW(1:1))+1,3)

前一項所得的各個最小值,以 INDEX 函數在「資料」陣列中求得第3欄的資料。公式中的「+1」乃因為「資料」中的欄名也占掉一列。

IFERROR(INDEX(資料,SMALL(IF(類別=$F$2,項目,FALSE),ROW(1:1))+1,3),"")

利用 IFERROR 函數將 INDEX 查表後所產生的錯誤值以空白顯示。

儲存格H2的公式原理相同,只要將查表 INDEX 函式中設定為查詢第4欄即可。

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

最近又有網友問到如何在 Excel 的工作表中設計二層的下拉式選單(參考下圖)?在第一層選單中可以選取類別名稱,根據第一層的名稱,在第二層選單中可以選取對應的項目名稱。

在設計上會用到「定義名稱、資料驗證、INDIRECT」等項目。

【準備工作】

1. 選取儲存格D1:F1,定義名稱為:TITLE。(由[公式/名稱管理員]中設定名稱)

image

2. 選取儲存格D1:F7。按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:「數字、英文、中文」。

【設計第一層選單】

3. 選取儲存格A2:A7,選取[資料/資料工具]中的「資料驗證」。

4. 在[資料驗證]對證框中,設定[儲存格內允許]為「清單」,[來源]方塊中輸入「=TITLE」。

TITLE 名稱所定義的資料範圍為儲存格D1:F1,用以擷取類別名稱。

如此,儲存格A2:A7都可以使用下拉式清單選取類別名稱。

【設計第二層選單】

5. 選取儲存格B2:B7,選取[資料/資料工具]中的「資料驗證」。

6. 在[資料驗證]對證框中,設定[儲存格內允許]為「清單」,[來源]方塊中輸入「=INDIRECT(A2)」。

INDIRECT(A2):將儲存格A2中的文字轉換為儲存格位址。

此處雖然只用到儲存格A2,實際上 Excel 會將往下各列,自動調整為儲存格A3、A4 …。

如此,即可使用第二層的選單了。

依此設計觀念,如果需要設計第三層、第四層也是可以的。

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

條列一些關於 SUBSTITUTE、REPLACE、FIND、SEARCH 函數應用的文章,當作練習的參考:

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

最近同事遇到一個問題,想要在電腦上打一些日文字,可是我們的鍵盤只有標示英文字,即使安裝日文輸入法,也不見得能打得出字來,該怎麼解決這個問題呢?

我找到了 sciweavers 網站(http://www.sciweavers.org/),它提供了許多好用的線上工具:

當你點選了「i2Type」工具,網頁上會呈現一個英數字組成的鍵盤:

點選網頁下方的日本國旗:

網頁上的鍵盤改顯示日文輸入的對照鍵盤,你可以試著輸入一些文字(直接對照鍵盤輸入或是以滑鼠點選按鍵圖示):

這個網頁還貼心的提供放大/縮小字型、改變字體等功能,當你輸入完成時,按一下 Downlaod 按鈕,網頁會自動將你所輸入的文字,產生一個 Word 檔。

這個系統可以讓你隨時切換至不同語系的輸入法:

這個網站也提供了瀏覽器的外掛:

 

如果,你在想打一些中文字,可是沒有中文鍵盤讓你對照,沒關係,請到這個網站:

http://www.i2bopomo.com/

這個網站讓你可以在英文鍵盤上打注音來輸入中文字:

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

接續前六篇文章:

火星人的天空之城- Excel-資料篩選-1

火星人的天空之城- Excel-資料篩選-2

火星人的天空之城- Excel-資料篩選-3

火星人的天空之城- Excel-資料篩選-4

火星人的天空之城- Excel-資料篩選-5

火星人的天空之城- Excel-資料篩選-6

如果要以「日期」欄位的條件來篩選,參考以下作法:

例如:輸入日期條件:「>=2011/7/1」,即篩選2011/7/1之後的資料。

如果你想要篩選 7 月份的資料該如何處理?你必須藉助 MONTH 函式才能達到。你在儲存條件的儲存格中輸入一個公式:

儲存格I2:=MONTH(A2)=7

結果顯示為 FALSE 的原因是因為A2儲存格為 2011/5/2,月份為5,所以結果為 FALSE。

注意:條件的欄位名稱不要使用原來的欄位名稱(日期),必須更換為不同的名稱(不可和原有的所有欄位名稱重覆)。

如此,便可以篩選出 7 月份的資料。

例如:你想要篩選日期為星期六或是星期日的銷售資料。

儲存格I2:=OR(WEEKDAY(A2,2)=6,WEEKDAY(A2,2)=7)

還可以增加條件來篩選:

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

接續前五篇文章:

火星人的天空之城- Excel-資料篩選-1

火星人的天空之城- Excel-資料篩選-2

火星人的天空之城- Excel-資料篩選-3

火星人的天空之城- Excel-資料篩選-4

火星人的天空之城- Excel-資料篩選-5

學會自動篩選之後,要來練習進階篩選。

image[3]

1. 首先,選取[資料/排序與篩選]項下的「進階」選項。

2. 在[進階篩選]對話框中設定:資料範圍、準則範圍和複製到何處。

image[7]

例如:(參考下圖)

  • 資料範圍:儲存格$A$1:$G$23
    指定要篩選的資料範圍。如果你已選取資料表中的一個儲存格,Excel 會自動選取資料範圍。
  • 準則範圍:儲存格$I$1:$J$2
    準則範圍必須包含欄位名稱和條件,而欄位名稱必須和資料範圍的欄位名稱一致,否則篩選不到任何資料。
  • 複製到:儲存格$I$5:$L$5
    顯示結果的欄位名稱,你可以指定篩選後要顯示的欄位名稱,順序可以調整,但欄位名稱必須和資料範圍的欄位名稱一致,否則篩選不到任何資料。

image[11]

例如:(參考下圖)

只要條件設在同一列中者,表示這些條件是要執行邏輯 AND 運算,必須同時符合條件者才會被篩選出來。

而「數量」欄位的條件可以使用關係運算子(>、<、=、>=、<=、<>)。

篩選條件:(車種=休旅車 AND 顏色=銀 AND 數量>2)

image17

image[15]

而「文字」欄位的條件,可以使用萬用字元(本例中「*E*」,表示篩選型號中有 E 字元者):

image[19]

例如:(參考下圖)

如果你把條件寫在不同列,則會執行邏輯 OR 運算。以下圖為例:

篩選條件:((型號=*E* AND 售價>1000000) OR 數量>3)

image[23]

同一列的 AND 條件,可以再增加;不同列的 OR 條件,可以再增加。(請自行練習)

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

接續前四篇文章:

火星人的天空之城- Excel-資料篩選-1

火星人的天空之城- Excel-資料篩選-2

火星人的天空之城- Excel-資料篩選-3

火星人的天空之城- Excel-資料篩選-4

如果你要篩選「日期」欄位,可以使用多種和日期相關的篩選條件。

如果你想自訂篩選的條件,則可以選取「等於、之前、之後、介於」的其中一個,然後輸入篩選條件。

例如:篩選介於2011/06/02 到 2011/07/09 之間的日期:

結果如下:

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

接續前三篇文章:

火星人的天空之城- Excel-資料篩選-1

火星人的天空之城- Excel-資料篩選-2

火星人的天空之城- Excel-資料篩選-3

如果你要篩選「文字」欄位,則可以使用「等於、不等於、開始於、結束於、包含、不包含」等項目。

例如:篩選型號中包含「E」字元者:

image

得到的結果:

在篩選條件中可以使用萬用字元「?、*」,例如:

  • 「*E*」代表篩選含有E字元者
  • 「?S*」代表篩選第2個字元是S者
  • 「M*」相當於「開始於M」
  • 「*A」相當於「結束於A」

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

接續前二篇文章:

火星人的天空之城- Excel-資料篩選-1

火星人的天空之城- Excel-資料篩選-2

如果要根據「數字」欄位(例如:售價)來進行篩選,則可以使用關係運算:等於、不等於、大於、大於或等於、小於、小於或等於、介於,和前10項、高於平均、低於平均等項目。

其中選取高於平均或低於平均,以「低於平均」為例:

如果選取「前10項」,則可以針對「最前、最後」和「項、%」來篩選。例如:最前5項、最後10%等。

image

例如:銷售額篩選「最前5項」,觀察下圖,為何會有6筆資料,因為第2列和第8列的銷售額相同。

如果要使用關係運算:等於、不等於、大於、大於或等於、小於、小於或等於,則可以自訂篩選條件。

例如:以[售價]欄位,篩選「大於1000000且小於或等於1300000」者:

得到以下的篩選結果:

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

接續上一篇文章:火星人的天空之城- Excel-資料篩選-1,繼續進行資料篩選練習。

1. 對[型號]欄位進行篩選。

2. 根據[顏色]欄位進行排序。

改變後的結果:注意到經過篩選和排序的二個欄位,其下拉式清單的符號有所改變。

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

在 Excel 中提供了方便的篩選工具可以篩選資料,以下要針對一個資料表進行篩選工作,參考以下的步驟。

1. 選取[常用/排序與篩選]下的「篩選」。

2. 在一個欄位中選取下拉式選單(以車種為例),勾選想要的項目(可以複選)。

觀察篩選後的結果,其中下拉式清單的符號改變了,而某些列號被隱藏了。

3. 可以在另一個欄位進行篩選(例如只勾選顏色為白色)。

參考以下結果:

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

在 Windows 7 中可以辨識 ZIP 格式的壓縮檔,有同事問到,如果想要將資料夾或一些檔案,直接壓縮成 ZIP 檔,該如何處理?

1. 只要選取想要壓縮的資料夾和檔案。

2. 在選取的檔案上按一下右鍵,選取[傳送到/壓縮的 (Zipped) 資料夾]選項。

Windows 7 會自動產生這個 ZIP 檔,並且停留在變更名稱的狀態。

ZIP 格式的壓縮檔,可以在 Windows 系統之下直接檢視其中的內容。

註:如果你想停用 ZIP 的支援功能,則按一下 WinKey + R 鍵,輸入「regsvr32 /u zipfldr.dll」,按一下[確定]按鈕即可。如果以後又想回復 ZIP 的支援功能,則按一下 WinKey + R 鍵,輸入「regsvr32  zipfldr.dll」。

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

現在上網常會可能接觸到釣魚(phishing)網站,所幸瀏覽器已提供篩選的功能,可以幫使用者自動偵測網路釣魚網站。根據微軟網站的說明,在瀏覽網頁時,IE的網路釣魚篩選器會在背景中執行,它會將所造訪的網址與已回報至 Microsoft 的合法網站清單做比對(此清單儲存在自己的電腦上)。然後,分析所造訪的網站,以查看其中是否含有網路釣魚網站常見的的特徵。最後,會徵求你的同意,網路釣魚篩選工具會傳送一些網址到 Microsoft,進一步將這些網址與經常更新的網路釣魚網站回報清單進行核對。

(參考:http://windows.microsoft.com/zh-TW/windows-vista/Phishing-Filter-frequently-asked-questions)

如果你想手動檢查網站是否可能是釣魚網站,可以選取[工具/安全性/檢查此網站]選項。

Internet Explorer 會啟動篩選工具:

回報篩選結果:

如果你想回報某網站可能是釣魚網站或是網站含有惡意軟體,可以選取[工具/安全性/報告不安全的網站]選項。Internet Explorer 會連到網站:https://feedback.smartscreen.microsoft.com/feedback.aspx,你可行進行相關的報告。

關於釣魚網站篩選工具的相關問題可以參考微軟網站:

https://feedback.smartscreen.microsoft.com/ie9faq.aspx

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

一般兩個較大的數值,要找出其最大公因數,可能不是一件容易的事,但是透過輾轉相除法,可以快速找到結果,如果要在 Excel 的工作表中輸入兩個數後,列出其輾轉相除法的計算過程,該如何處理?

參考下圖,在儲存格A2和儲存格B2中輸入兩個數值,例如:455064 和 8377674,經過輾轉相除法的運算,可以得到最大公因數為 6。

儲存格D2:=IFERROR(INT(C2/B2),"")

找出儲存格C2除以儲存格B2的最大的商,如果出錯(表示已運算至0),則顯示空白。

複製儲存格D2:D3,往下各列貼上。(其中儲存格D3沒有任何資料內容)

儲存格C3:=IF(D2<>"",B2*D2,"")

儲存格C4:=IF(C3<>"",C2-C3,"")

複製儲存格B3:C4,往下各列貼上。

儲存格A2:=IFERROR(INT(B2/C4),"")

找出儲存格B2除以儲存格C4的最大的商,如果出錯(表示已運算至0),則顯示空白。

複製儲存格A2:A3,往下各列貼上。(其中儲存格D3沒有任何資料內容)

儲存格B3:=IF(A2<>"",A2*C4,"")

儲存格B4:=IF(B3<>"",B2-B3,"")

如果要在運算過程中顯示分隔線,則在儲存格B3套用格式化條件「公式:=A2<>"",格式:設定底線」。

在儲存格C3套用格式化條件「公式:=D2<>"",格式:設定底線」。

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

條列一些關於「萬用字元」應用的文章,當作練習的參考:

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

在 Excel 中,如果要展現求取最大公因數和最小公倍數的過程,該如何設計呢?參考下圖,在儲存格B2和儲存格C2中輸入二個數值,然後A欄中自行輸入由小到大的質數,直到運算結果只剩質數。

儲存格B3:=IFERROR(B2/$A2,"")

儲存格C3:=IFERROR(C2/$A2,"")

複製儲存格B3:C3,往下各列貼上。

儲存格F2:=PRODUCT(A2:A11)

將儲存格A2到儲存格A11中的每一個數相乘,即為最大公因數。

儲存格F3:=PRODUCT(A2:A11)*SMALL(B2:C11,1)*SMALL(B2:C11,2)

將最大公因數,再乘以儲存格B2:C11中最小的兩個數(質數),即為最小公倍數。

接著,來設計儲存格自動產生底線和左框線。

設定儲存格B3的格式化條件為公式:「A3<>””」,格式設定為顯示左框線和底線。即只要A欄中有資料,對應的B欄儲存格,即顯示底線和左框線。

儲存格C3 ,則只要設定格式化條件為顯示底線。

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

1 234

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼