2011年12月28日 星期三

Excel-資料表摘要(INDIRECT+ADDRESS)

假設在 XXX、YYY、ZZZ 三個工作表中(如下三個圖),都有 AAA、BBB、CCC、DDD、EEE 五個人的資料,如果想要將每個工作表的小計(位於第8列中)列,整理成一個「小計」的摘要表,該如何處理?其中摘要表要以 XXX、YYY、ZZZ 為欄標題,AAA、BBB、CCC、DDD、EEE 為列標題。

XXX 工作表:

YYY 工作表:

ZZZ 工作表:

【分析】

觀察下圖與以上三圖:

由於三個工作表的結構完全一樣,所以我們特別將工作表名稱定摘要表的欄標題,將每個工作表的欄標題(AAA、BBB、CCC、DDD、EEE)當為列標題。

 

【解法】

儲存格B2:=INDIRECT(B$1&"!"&ADDRESS(8,1+ROW(1:1)))

複製儲存格B2,貼至儲存格B2:D6。

ADDRESS(8,1+ROW(1:1)):結果為$B$8。

INDIRECT(B$1&"!"&ADDRESS(8,1+ROW(1:1))):相當於位址「XXX!$B$8」

向右複製時,即可產生XXX!$B$8、YYY!$B$8、ZZZ!$B$8。

向下複製時,即可產生XXX!$C$8、YYY!$C$8、ZZZ!$C$8。

再向下複製時,可產生XXX!$D$8、YYY!$D$8、ZZZ!$D$8。

此方式,不管有幾個工作表或工作表中有幾個人的資料,都可以一個公式通用之。

 

【延伸思考】

1. 如果原來的題目,將摘要表要以 AAA、BBB、CCC、DDD、EEE 為欄標題,為 XXX、YYY、ZZZ 列標題,公式該如何設計。

2. 如果想要在摘要表中,僅列出想要的人名(例如:AAA、CCC、EEE),公式該如何處理?

 

【補充資料】

詳細函數說明請參閱微軟網站:

ADDRESShttp://office.microsoft.com/zh-tw/excel-help/HP010342163.aspx

在已知指定列和欄號下,取得工作表中儲存格的位址。

語法:ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])

row_num:指定要用在儲存格參照中之列號的數值。

column_num:指定要用在儲存格參照中之欄號的數值。

abs_num:可省略。指定要傳回之參照類型的數值。傳回此參照類型如下:

1或省略:絕對儲存格參照;2:列:絕對;欄:相對;3:列:相對;欄:絕對;4:相對參照

 

INDIRECThttp://office.microsoft.com/zh-tw/excel-help/HP010342609.aspx

INDIRECT:傳回文字串所指定的參照位址。

語法:INDIRECT(ref_text,[a1])

ref_text:單一儲存格的參照位址,其中包含A1欄名列號表示法、R1C1欄名列號表示法、定義為參照位址的名稱,或定義為字串的儲存格參照位址。

a1:指定ref_text儲存格中所包含參照位址類型的邏輯值。

Excel-同時檢視活頁簿中的兩個工作表

在 Excel 中,大家比較熟悉同時檢視兩個活頁簿中的不同工作表,但有時會想要同時檢視「同一活頁簿」中的兩個不同工作表,可以做比較或是方便複製等動作。該如何操作呢?

1. 開啟要檢視的活頁簿,切換至第一個工作表。

2. 按一下[檢視/視窗]中的「開新視窗」按鈕。

3. 按一下[檢視/視窗]中的「並排顯示」按鈕。

4. 選取一種排列方式,例如:水平並排。

5. 在第二個視窗切換至第二個工作表。

如此便能輕鬆做資料對照或執行複製、搬移等工作。如果你有勾選「同步捲動」,則改變第一個工作表的檢視位置時,第二個工作表也會同步的改變位置。

2011年12月27日 星期二

Excel-計算小數部分的總和(陣列公式)

來練習陣列公式。有人想要將一群數列中的小數部分取出並計算其總和,該如何處理?你可能會先將每一個數的小數找出來,再求其總和。

儲存格C2:=B2-INT(B2)

複製儲存格C2,往下各列貼上。再求這些小數的總和。

而你如果使用陣列公式,將可以不用這麼多步驟,只要一個公式即可完成:

儲存格E2:{=SUM(B2:B21-INT(B2:B21))}

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

2011年12月26日 星期一

免費線上工程計算機(可繪圖)

最近看到一個免費的線上工程計算機,還可以幫你繪圖。使用上非常的方便實用:

Desmos Graphing Calculatorβ (https://www.desmos.com/calculator/)

當你使用晝面上的按鍵輸入運算式,可以立即得到運算結果:

它也可以根據變數來計算公式的值:

按一下 Examples,會列出一些範例,你可以試著操做一下。按一下 Graph Settings,可以針對圖形的顯示進行設定:

如果你想分享或儲存這個公式的結果,則按一下 save & share,將超連結網址寄給他人或在瀏覽器上直接打開,則會看到一樣的運算結果,如果想要保存這個公式的繪圖結果,則按一下 Save graph as image (opens in a new a window),這個圖片會顯示在另一個視窗中。

這個網站,應該會是某些科目老師的好幫手。也是個人自學、自用的好工具。

2011年12月25日 星期日

Google 搜尋透視

Google 的搜尋透視(http://www.google.com/insights/search/?hl=zh-TW)可以讓你看到某個關鍵字在不同時間點、不同地理位置、特定類別和資源等的熱門程度變化。

例如:查詢王建明和曾雅妮,可見曾雅妮這一二年爆紅,王建明就開始走下坡。

再來看看查詢:iPhone,關察其熱門的地理位置,想不到在柬埔寨、土庫曼、寮國等地區這麼熱門。色彩深度可以看出熱門程度。

如果你搜尋:Steve Jobs,則會發現和其相關的熱門及人氣竄升的搜尋關鍵字詞。

如果你不知道要搜尋什麼,試試網站提供的範例:

台北信義華納威秀附近的聖誔夜

12/24 接近 11:30,帶著小孩到台北信義華納威秀附近看看裝飾著五彩繽紛、閃閃發亮的聖誕夜。運氣還不錯,路邊還留一個車位給我,這裡只要能塞得進車子的地方都已塞了車。

四處人山人海、男男女女、大大小小,擠的人行道上滿滿都是人,連拍照都不容易取得好位置。今天晚上外面溫度大概 14 度左右,看起來大家還都滿有熱情的。

不管是地上的,還是樹上的;不管是遠看,還是近觀,這些燈飾都有一種美感。而且有這麼多人,一起走過來走過去的,好像都不會累似的。想想看,如果只有10個人在這裡看這些聖誔燈,你一定會覺得這些沒什麼好看的?!總之,數大就是美!

 

 

 

看看已經午夜十二點整了,車子還擠成這樣呢!數大就是美!?

註:技術太差,晚上拿傻瓜相機亂拍,破壞了美感。

2011年12月24日 星期六

只列印網頁上有用的內容-printfriendly

一般的網站和部落格都會放上一些廣告或外掛,如果在列印時只想列印文章部分,該如何處理呢?有一些網站,例如 udn.com,會在某篇新聞中提供「列印」的功能,如果你的網站沒有提供這些功能,該如何處理呢?

printfriendly 網站(http://www.printfriendly.com/) 可以讓你輸入網址後,幫你建立列印用的頁面,免費又好用。

http://isvincent.blogspot.com/2011/12/e.html 文章為例,原始畫面如下:

輸入網址後,進行轉換:(你可以選取文字的大小)

按一下 print ,其輸出的結果如下:(不相干的資訊已消除)

如果按一下 PDF,則會把處理結果轉換至一個 PDF 檔案中。(不過,試了幾次並不理想)

網站也提供可以將其提供的外掛程式碼,直接放在你的網站上。以後你的讀者即可以直接按鈕後列印文章了。(複製程式碼後,置入你的部落格所提供的外掛 HTML 程式區)

觀察一下本網站是否有加入這個外掛!

Google 提供的免費線上字型

在網頁上常會需要用到一些字型,Google 提供了許多免費字型(都是英文字型),可以免費下載及線上使用。

網址:http://www.google.com/webfonts#ChoosePlace:select

首先,挑選你想要的字型。(慢慢挑,字型很多)

按一下 Review,預覽一下這些字型:

image

如果你想下載字型,則按一下 Download your Collection

字型會放在一個 fonts.zip 檔案中,解壓縮後將檔案放入 Windows 中的字型資料夾即可。而系統提醒你,其實不用下載字型也可以使用。

如果想要在你的網站上線上使用這些字型,則按一下 Use,依照指引取得程式碼。

取得步驟 3 和步驟 4 中的程式碼:

在網頁編輯軟體上實測一下:

預覽一下效果:

2011年12月23日 星期五

Excel-BMI值計算(格式化的條件規則)

大家都知道身體質量指數(BMI)的計算公式是「體重(kg)/(身高(M))^2」,這次要設計一個簡單的工具來找合理的體重範圍。

動作是希望輸入身高值和體重值後,能自動顯示一組身高範圍和體重範圍組成的 BMI 值。並且設計以微調按鈕來控制不同體重值,而能自動標示這個身高和體重所在的 BMI 值,並且將合於標準範圍(18.5~24)的部分以淺綠色表示(落在淺紅色區域即為不標準)。(參考下圖)

【表格數值製作】

1. 產生身高數列

儲存格B2:=$C$1-5;儲存格C2:=B2+1

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

2. 產生體重數列

儲存格A3:=F1-15;儲存格A4:=A3+1

複製儲存格A4,貼至儲存格A4:A32。

3. 產生 BMI 值

儲存格B3:=$A3/(B$2/100)^2

複製儲存格B3,貼至儲存格B3:L32。

 

【製作體重微調按鈕】

1. 選取[開發人員/插入]中的「微調按鈕」工具,在工作表中建立適當大小。

image

2. 設定最小值和最大值,本例之目前值設為67,並將儲存格連結至儲存格F1。

如此即可以微調按鈕來調整儲存格F1中的體重值。

 

【設定儲存格格式】

先將儲存格B3:L32設定為淺紅色底色,接著要設定儲存格格式化的條件規則。

1. 將目前體重和身高所得之 BMI 以白色底+紅色外框+紅色字來表示。

2. 選取儲存格B3:L32,設定格式化條件規則。

3. 選取[使用公式來決定格式化哪些儲存格]項目,輸入公式「=AND($A3=$F$1,B$2=$C$1)」。

4. 設定格式為白色底+紅色外框+紅色字。

你觀察一下這個紅色方塊,它會固定在表格的中央位置!

4. 新增一個規則,選取[只格式化包含下列的儲存格]選項。

5. 設定儲存格值介於 18.5 至 24 之間,設定底色為淺綠色。(參考下圖)

隨著使用微調按鈕來改變體重值,淺綠色的標準區也會跟著改變位置。

2011年12月20日 星期二

Microsoft下載管理員

有時你要下載的檔案比較大或是比較多時,可能要藉助一些下載管理員以方便操作。微軟自己也有推出下載管理員,下載頁面:

http://www.microsoft.com/downloads/zh-tw/details.aspx?familyid=39fbaa4c-0295-4844-8c2a-14f5e9ca149a&displaylang=zh-tw

根據其網站的說明,這個Microsoft 下載管理員可以讓大型檔案(例如應用程式)和多媒體檔案的下載作業快速且穩定地執行。 它設計的主要目的是協助使用者管理在支援的 Microsoft 網站上進行的檔案下載作業。 開始下載後,Microsoft 下載管理員容易使用的介面會顯示下載作業的狀態,並讓您暫停作用中的下載作業或繼續失敗的下載作業。

下載並執行這個 msi 檔,啟動後按一下「新的下載」,輸入要下載檔案的 URL,其有提醒要是 http:// 開頭才行。

透過這個管理員,你可以暫停/接續下載:

下載檔案的預設存放位置是:C:\Users\你的帳號\Downloads(本例為 C:\Users\Administrator\Downloads),如果你要更改,則按一下「設定」,自行修改目的地資料夾。其他像自動重試的次數和間隔秒數、同時可下載的數量及 Proxy 的設定等也在此設定。

註:如果你要移除這個程式,只要再一次執行原來的 msi 檔即可。

好康東東