2012年1月31日 星期二

Excel-計算各月的平均(陣列公式)

在 Excel 的工作表中有個日期和數量的報表,如果想要摘要依月份計算數量的平均,該如何處理?(參考下圖)

選取儲存格A1:B26,按一下 Ctrl+Shift+F3 鍵,定義名稱:日期、數量。

【使用陣列公式】

儲存格E2:{=AVERAGE(IF(MONTH(日期)=ROW(1:1),數量,FALSE))}

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

複製儲存格E2,貼至儲存格E2:E13。

MONTH(日期)=ROW(1:1):判斷日期中的月份是否為1(ROW(1:1)=1),而ROW(1:1)往下複製會變為ROW(2:2)=2,…。

IF 函數中的參數 FALSE,請勿以0或空白取代,否則所有的0都會列入 AVERAGE 函數來平均。

 

【使用SUMPRODUCT函數】

儲存格E2:=SUMPRODUCT((MONTH(日期)=ROW(1:1))*數量)/SUMPRODUCT(--(MONTH(日期)=ROW(1:1)))

複製儲存格E2,貼至儲存格E2:E13。

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

 

【補充說明】

關於函數的詳細說明,可參考微軟網站: 

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

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

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

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

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

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

Excel-計算最近幾次成績的平均

在 Excel 的工作表中,在A欄中會不斷的輸入考試成績,如果只想計算最近幾次的平均,該如何處理?(參考下圖)

為了便於理解公式,先選取A欄,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」,將A欄定義名稱為「成績」。

要小心特別的狀況,如果輸入的成績數量少於要求的次數會出現錯誤訊息。

所以公式定為:

儲存格D2:=AVERAGE(OFFSET($A$2,COUNTA(成績)-MIN(COUNTA(成績),C2),0,MIN(COUNTA(成績),C2),1))

複製儲存格D2,往下各列複製。

因為成績會不斷的輸入,所以使用 OFFSET 函數來取這個動態的位址。

MIN(COUNTA(成績),C2):將要求的次數(儲存格C2)和成績欄中所輸入成績的個數取最小值,如此可以避免輸入的成績數量少於要求的次數而出現錯誤訊息。

COUNTA(成績)-MIN(COUNTA(成績),C2):取得不要計算平均的儲存格個數。

以上二式代入 OFFSET 函數,即可求得動態位置。

 

【補充說明】

關於函數的詳細說明,可參考微軟網站: 

OFFSEThttp://office.microsoft.com/zh-tw/excel-help/HP010342739.aspx

OFFSET 函數:傳回根據所指定的儲存格位址、列距及欄距而算出的參照位址。

語法:OFFSET(reference, rows, cols, [height], [width])

Reference:用以計算位移的起始參照位址。

Rows:左上角儲存格要往上或往下參照的列數。Rows可以是正數(表示在起始參照位址下方)或負數(表示在起始參照位址上方)

Cols:左上角儲存格要往左或往右參照的欄數。Cols 可以是正數(表示在起始參照位址右方)或負數(表示在起始參照位址左方)

Height:所傳回參照位址的高度 (以列數為單位)Height 必須是正數。

Width:所傳回參照位址的寬度 (以欄數為單位)Width 必須是正數。

 

Excel-以英文顯示日期中的月份

在 Excel 有非常多關於日期時間的格式設定,有人問到如果想要將日期中的月份以英文字來表示該如何處理?(參考下圖)

儲存格B2:=TEXT(A2,"mmmm")

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

透過 TEXT 函數,將參數設定為「"mmmm"」,即可顯示英文字的月份,例如一月為 January、二月為 February 等。

如果你是要在原日期儲存格中只想顯示月份,則可以在[儲存格格式]對話框中的「數值」標籤下,自訂格式為「"mmmm"」。

 

【補充說明】

關於函數的詳細說明,可參考微軟網站:

TEXThttp://office.microsoft.com/zh-tw/excel-help/HP010342952.aspx

TEXT:將數值轉換成文字,並使用特殊格式字串來指定顯示的格式。

語法:TEXT(valueformat_text)

vlue:可以是數值、一個會傳回數值的或者是一個參照到含有數值資料的儲存格位址。

format_text:一個以雙引號括住並格式化為文字字串的數值,例如"m/d/yyyy""#,##0.00"

 

2012年1月29日 星期日

自然人憑證-開始使用電子發票

如果你有自然人憑證,現在可以配合電子發票的推廣,將發票電子化,買東西時可以大聲說不要印紙本發票。你要先到財政部電子發票整合服務平台做一些設定,將來電子發票才能記錄到你的帳戶之下。

財政部電子發票整合服務平台https://www.einvoice.nat.gov.tw/wSite/mp?mp=1

要以自然人憑證登入(第一次使用要先註冊):

這個平台能做的事如下結構:

首先你要將你手邊的載具歸到你的帳戶,例如各種含有晶片的信用卡、會員卡、金融卡等,如果是悠遊卡則可以到 7-11 的 iBon 機器上進行歸戶(記得要帶自然人憑證去)。

你可以將自己和家人的各種「不具名」的載具(像別人的信用卡就不行)集合在你的帳戶中:

如果你有消費記錄了,就可以查詢各個載具的消費明細。在「查詢與捐贈」中,先選一個載具名稱:

你可以選取要查詢的發票月份,按一下「查詢」,即會列出所有發票:

挑選一張發票,按一下「查看明細」,還可以看你消費了那些內容:

這個平台會幫你對發票,如果中獎了還可以直接將獎金入到你指定的金融帳戶中。當然,你也可以在平台上設定捐贈給某些單位。現在主要的問題是提供電子發票的店家還不夠多,而且店家還是會列印紙本的消費明細給你(應該可以選擇是否列印吧?)。如果你沒有向店家指定「不列印紙本發票」,店家還是會列印發票給你(該發票資料不會進到你的平台帳戶中),所以消費時要特別注意!

自然人憑證-查詢個人健保資料

如果你已經有自然人憑證,你拿它來做什麼?如何能利用網路取代馬路,不臨櫃能做那些事?例如你想查詢個人健保相關資料、列印繳款單、繳費證明及申請無照片健保IC卡等,可以到這個網頁:

https://eservice.nhi.gov.tw/Personal1/System/Login.aspx

以自然人憑證登入系統,可使用下列功能:
1. 查詢個人未繳保險費情形與列印繳款單
2. 查詢及列印個人投、退保資料
3. 查詢及列印依附眷屬投、退保資料
4. 申請及查詢電子繳款單
5. 申請及列印繳納保險費證明
6. 申請無照片健保IC卡

系統會要求安裝 ActiveX 元件,裝好之後才能使用:

試著查詢個人相關資料,從一開始加入健保起的資料都有:

如果你想查詢個人就醫紀錄明細 ,可以到以下網頁:

https://eservice.nhi.gov.tw/PERSONALMED/System/Login.aspx

也是利用自然人憑證登入:

它會列出你最近三個月(指系統提供的最近三個月,例如我在1月底查,結果查到去年9月到11月的資料。)的就醫紀錄。

2012年1月28日 星期六

Excel-轉換含小數的二進位數為十進位數(陣列)

在 Excel 中如果想要將二進位數、八進位、十六進位數轉換為十進位數,可以利用以下的相關函數:

但是,如果要轉換含小數的二進位數為十進制數,則會產生「#NUM!」錯誤訊息。這次來練習如何達成轉換工作。

 

【模擬轉換過程】

(1) 儲存格B3:B14分別表示2^(-1)、2^(-2)、2^(-3)、2^(-4)、2^(-5)、…。

(2) 儲存格C3:=MID($B$1,ROW(3:3),1),複製儲存格C3,貼至儲存格C3:C14。取出小數點後的每一位數字(1或0)

(3) D欄為B欄和C欄的相乘積。

(4) 儲存格D15:{=SUM(IFERROR(D3:D14,0))},這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。

公式是要將非錯誤值的儲存格相加,透過 IFERROR 函數來判斷若為錯誤值則視為0。

 

【以陣列公式運算】

如果以陣列公式來處理,則只要一個公式即可做完上述所有的動作。

{=SUM(MID(B1,ROW(INDIRECT("3:"&LEN(B1))),1)*1/2^(ROW(INDIRECT("1:"&LEN(B1)-2))))}

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

LEN(B1):找出儲存格B1中二進位數的字元數(包含「0.」),公式自動判斷二進位數的長度。

ROW(INDIRECT("3:"&LEN(B1))):利用 INDIRECT 函數來轉換為位址,本例可得ROW(3:12),在陣列公式中代表3, 4, 5…, 12。

MID(B1,ROW(INDIRECT("3:"&LEN(B1))),1):取得儲存格B1中的第3個字至最後的每個字元(不包含「0.」),,本例為「1100110011」。

ROW(INDIRECT("1:"&LEN(B1)-2)):利用 INDIRECT 函數來轉換為位址,本例可得ROW(1:10),在陣列公式中代表1, 2, 3, …, 10。

1/2^(ROW(INDIRECT("1:"&LEN(B1)-2))):本例可得1/2, 1/4, 1/8, …, 1/(2)^10。

將上述二式相乘後加總即為答案。

2012年1月27日 星期五

有趣的Hello TAIWAN(臺灣)

有人給我看了一個有趣且富創意的LOGO,可能是中國大陸人設計的(不確定)。(我從別人相機翻拍下來的)

橫看是 「HELLO TAIWAN」:

直看是「臺灣」:

我有嘗試以 Google 的以圖找圖來看看出處為何,但是沒成功。

台南虎頭埤

過年期間那裡踏青好呢?今年選擇了去過好幾次的台南新化-虎頭埤風景區。

在其官方網頁中(http://htp.tainan.gov.tw/main.php)介紹到:台灣第一水庫虎頭埤水庫建於清道光二十六年(西元一八四六年),因山勢形狀如虎頭聳歭而得名,沿岸最高處為70公尺(位於青年活動中心北側山頭),埤面水最深約40公尺,背山面水,風景秀麗,西元1954年由台南縣文獻委員會選定「虎埤泛月」為「南瀛八大景」之一,亦曾創「台灣十二名勝」之一,有「小日月潭」之稱。

交通路線圖:(資料來源:虎頭埤風景區 http://htp.tainan.gov.tw/main.php)

在大門口外有租自行車的地方,可以騎自行車來環湖,也可以把車開進來,但我建議陡步是最好的。

園區內可住宿、露營、烤肉,可以釣魚、搭遊船(腳踏船、造型龍踏船、太陽能船),夏天還有滑水道可以玩。以下園區導覽圖取自虎頭埤風景區 http://htp.tainan.gov.tw/main.php

園區內有環湖步道:

有木板步道:

有石板步道:

有階梯:

一邊環湖,一邊拍下美麗湖光山色。園區美景渾然天成,人工整理的也是不錯:

天氣非常好,水中倒影非常漂亮:

湖中央有吊橋、涼亭:

在此漫步可以走上個一二小時,騎自行車也是不錯的,也適合野餐、烤肉活動。

2012年1月26日 星期四

台南成功大學隨意逛

過年在外閒晃,晃著晃著來到了台南的成功大學。成大的建築及設計,有現代的一面也有保留文化的一面,在亮麗的外表下,校園裡還有老火車、小東門的遺址等。

 

更有現代大師的作品:我指的是大大黑黑的那二個朱銘的作品(1995年,飛撲),而不是前面那二個小鬼頭。

 

 

校園內整裡的很好,功成湖和榕園有好多人在此散步,可見大家已把它當成公園了。而許多高大的榕樹讓人看了就覺得很不可思議,仔細看看,好像在電視廣告上常會看到。

 

校園中也有一些巧妙的設計:

 

這些也只是成大的一些角落,下次應再來走走。

台南都會公園

台南都會公園位於仁德往高雄一號省道上,目前尚未完全的完工,不過已可讓你走個大半天了。

相關旅遊說明可參閱:http://emmm.tw/L3_content.php?L3_id=73719

交通相當方便:國道1號高速公路或國道3號高速公路,銜接86東西向快速道路往西,於台南交流道下高架橋,往北接二仁路(台一線342K處),即可抵達台南都會公園。

 


檢視較大的地圖

由於傍晚才來這裡,所以只逛了一小部分而已。天氣好時,適合全家來走路、騎自行車。

 

 

 

 

 

註:參考以下網頁的說明

http://www.cpami.gov.tw/chinese/index.php?option=com_content&view=article&id=8189&Itemid=54

台南都會公園發展定位,係以博物館為核心的都會公園,提供多元化「文化藝術、休閒、遊憩、環境教育」之功能,並創造優質生活環境,有效引導周邊區域之發展。都會公園採國際競圖,規劃朝結合自然生態與人文藝術,適時提供都會區民眾休閒空間與結合都市自然生態的新景點。透過博物館的設置,使南瀛在地文化與世界文化接軌,經由自然與人文環境之陶冶涵養,當能符合當地居民對本園區成為南部藝文展演與休閒場所的殷切期盼。

一期園區中規劃內容包括迎賓廣場、表演廣場、體健舒活區、藝文迷宮區、活動草原區、兒童島親子區、滯洪景觀湖,園區步道及自行車道、人行景觀橋及複合式遊客服務中心等。另預留9.5公頃土地做為博物館興建用地,奇美公司表示博物館主體工程預定98年3月起動工興建,興建期程3年,完工後將成為自然生態與人文藝術結合的都會公園。

檢視其他文章

好康東東