贊助廠商

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

搜尋本部落格文章資料

目前日期文章:201201 (36)

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

在 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來處理。

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

在 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 必須是正數。

 

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

在 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"

 

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

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

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

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

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

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

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

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

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

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

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

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

如果你已經有自然人憑證,你拿它來做什麼?如何能利用網路取代馬路,不臨櫃能做那些事?例如你想查詢個人健保相關資料、列印繳款單、繳費證明及申請無照片健保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月的資料。)的就醫紀錄。

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

在 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。

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

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

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

橫看是 「HELLO TAIWAN」:

直看是「臺灣」:

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

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

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

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

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

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

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

園區內有環湖步道:

有木板步道:

有石板步道:

有階梯:

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

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

湖中央有吊橋、涼亭:

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

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

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

 

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

 

 

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

 

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

 

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

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

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

相關旅遊說明可參閱: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年,完工後將成為自然生態與人文藝術結合的都會公園。

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

2012 年的春節假期有九天,回台南過年時行經鹽水時正值中午,先休息一下再往前行。來到這裡一定要品嚐一下有名的鹽水意麵。在中山路上知名的阿桐意麵和阿三意麵,兩家都已品嚐過,都很好吃。


檢視較大的地圖

吃完午飯,四處走走,發現月津港橋邊的 7-11 外牆彩繪了滿有地方特色的圖案:

橋邊已裝點的很有意境,晚上看會更漂亮,而一旁的橋南老街可以逛逛:

 

老街上的第一戶是「打鐵老鋪」,這是百年以上的老店,老闆說已傳了三代 ,老闆親切的向我們解說他的老店,並且向小朋友們描述了打鐵的方式及使用的材料及機具等。隨便拿出一支鐵鎚說已有上百年歷史了,而門口的一根橫木已被等待打鐵的人,因架著手等待而將其表面壓得凹凸不平了:

 

 

老街上部分老建築被套上了新的畫面,而路旁水面上的一顆顆圓球,聽說在晚上會是一顆顆點亮的燈:

 

既然來到鹽水,也順便帶小朋友探訪附近的「台灣詩路」:


檢視較大的地圖

位於台南市鹽水區田寮里的台灣詩路,是台灣第一條以「詩」鋪成之路,一旁是一望無垠的田野。近百首烙印在陶片上的台灣詩,形成蜿蜒的一道雲牆。近百首經挑選的詩,內容都是代表人民心聲的詩作。這應該是社區營造下一個很好的典範。

關於台灣詩路的詳細介紹可參考:http://emmm.tw/L3_content.php?L3_id=59492

交通資訊:中山高速公路 → 麻豆交流道 → 縣道176 → 省道19 → 沿路標行駛

 

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

有網友根據上一篇文章:Excel-多條件的查詢(INDEX+MATCH+陣列) http://isvincent.blogspot.com/2012/01/excel-indexmatch.html

問到如果要查詢的資料不止一筆時,公式該如何寫?參考下圖,要由外形和尺寸這兩個條件來找符合的零件編號及其售價。

【準備工作】

1. 選取儲存格A1:D19,按一下 Ctrl+Shift+F3 鍵,建立名稱:零件編號、外形、尺寸。

2. 選取儲存格A2:D19,建立名稱:資料。

【公式說明】

儲存格F4:{=IFERROR(INDEX(資料,SMALL(IF((外形=$G$1)*(尺寸=$G$2),ROW($1:$18)),ROW(1:1)),1),"")}

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

(外形=$G$1)*(尺寸=$G$2):公式中的「*」乃將二個條件做 AND 運算,條件完全成立時會傳回 True ,反之傳回 False

IF((外形=$G$1)*(尺寸=$G$2),ROW($2:$19)):因為資料共有 18 筆,所以將上式合乎條件者,傳回對應的列號(1~18)。

SMALL(IF((外形=$G$1)*(尺寸=$G$2),ROW($1:$18)),ROW(1:1)),1):因為ROW(1:1)=1,所以可以取出合於條件者的最小的列號,當往下複製時產生ROW(2:2)=2,會取出合於條件者的第 2 小的列號,以此類推。

透過 INDEX 函數,將此式求得的列號來查詢第 1 欄中的資料。由於往下複製公式時,可能因為找不到資料而傳回錯誤值,所以利用 IFERROR 函數,將錯誤訊息轉換為空字串。

同理,

儲存格G4:{=IFERROR(INDEX(資料,SMALL(IF((外形=$G$1)*(尺寸=$G$2),ROW($1:$18)),ROW(1:1)),4),"")}

透過 INDEX 函數,將此式結果來查詢第 4 欄的結果。

複製儲存格F4:G4,往下各列貼上。

 

【補充資料】

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

INDEXhttp://office.microsoft.com/zh-tw/excel-help/HP010342608.aspx

INDEX:傳回表格或範圍內的某個值或值的參照。

語法:INDEX(array, row_num, [column_num])

Array:儲存格範圍或陣列常數。

Row_num:選取陣列中傳回值的列。

Column_num:選取陣列中傳回值的欄。

 

IFERRORhttp://office.microsoft.com/zh-tw/excel-help/HA010342587.aspx

IFERROR:如果公式計算錯誤,會傳回指定的值;否則,會傳回公式的結果。

語法:IFERROR(value, value_if_error)

value:檢查此引數是否有錯誤。

value_if_error:公式計算錯誤時要傳回的值。

使用 IFERROR 函數,可以捕捉並處理公式中的錯誤。會評估下列錯誤類型:#N/A#VALUE!#REF!#DIV/0!#NUM!#NAME? #NULL!

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

根據 Excel 中的資料表(參考下圖),想要根據三個條件(零件編號、外形、尺寸)來求得售價,利用 INDEX 函數應是不錯的選擇。由於條件有三個,所以得藉助陣列公式才能求得結果。

【準備工作】

1. 選取儲存格A1:D19,按一下 Ctrl+Shift+F3 鍵,建立名稱:零件編號、外形、尺寸。

2. 選取儲存格A2:D19,建立名稱:資料。

【公式說明】

儲存格D22:{=INDEX(資料,MATCH(1,(零件編號=A22)*(外形=B22)*(尺寸=C22),0),4)}

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

(零件編號=A22)*(外形=B22)*(尺寸=C22):公式中的「*」乃將三個條件做 AND 運算,條件完全成立時會傳回 True (運算時視為1),反之傳回 False (運算時視為0)。

MATCH(1,(零件編號=A22)*(外形=B22)*(尺寸=C22),0):找尋「1」(符合條件者)在第幾列。

透過 INDEX 函數查詢在第4欄中對應的結果。

如果查詢不到任何符合條件的資料會傳回錯誤值,稍加修改公式,利用 IFERROR 函式,讓其傳回「無此零件」訊息。修改如下:

儲存格D23:{=IFERROR(INDEX(資料,MATCH(1,(零件編號=A22)*(外形=B22)*(尺寸=C22),0),4),"無此零件")}

使用資料驗證方式設計下拉式清單,可以讓輸入資料時不會因為輸入錯誤而查詢不到資料。

 

【補充資料】

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

INDEXhttp://office.microsoft.com/zh-tw/excel-help/HP010342608.aspx

INDEX:傳回表格或範圍內的某個值或值的參照。

語法:INDEX(array, row_num, [column_num])

Array:儲存格範圍或陣列常數。

Row_num:選取陣列中傳回值的列。

Column_num:選取陣列中傳回值的欄。

 

MATCHhttp://office.microsoft.com/zh-tw/excel-help/HP010342679.aspx

MATCH:搜尋某儲存格範圍內的指定項目,然後再傳回該項目在範圍內的相對位置。

語法:MATCH(lookup_value, lookup_array, [match_type])

lookup_value:在 lookup_array 中尋找比對的值。

lookup_array:要搜尋的儲存格範圍。

match_type:這是一個數字,其值有三種可能:(預設值為 1)

1 或省略:找到等於或僅次於 lookup_value 的值。lookup_array 引數內的值必須以遞增次序排列。

0:找第一個完全等於 lookup_value 的比較值。lookup_array 引數內的值可以依任意次序排列。

-1:找到等於或大於 lookup_value 的最小值。lookup_array 引數內的值必須以遞減次序排序。

 

IFERRORhttp://office.microsoft.com/zh-tw/excel-help/HA010342587.aspx

IFERROR:如果公式計算錯誤,會傳回指定的值;否則,會傳回公式的結果。

語法:IFERROR(value, value_if_error)

value:檢查此引數是否有錯誤。

value_if_error:公式計算錯誤時要傳回的值。

使用 IFERROR 函數,可以捕捉並處理公式中的錯誤。會評估下列錯誤類型:#N/A#VALUE!#REF!#DIV/0!#NUM!#NAME? #NULL!

 

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

在 Excel 的一個處理成績的資料表中(如下圖左),如果要求取前三名分數的平均,即使已經知道每個分數的名次,也不見得是件容易的事。或許你可以試試「陣列公式」!不用先求名次也能計算。

【準備工作】

選取儲存格B1:B26,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」選項,建立「分數」之名稱。

【說明】

(1) 求取前3名的平均

儲存格E2:=AVERAGE(LARGE(分數,{1,2,3}))

先以 LARGE(分數,{1,2,3}) 找出最大的三個數,再以 AVERAGE 函數求平均。

(2) 求取後4名的平均

儲存格E3:=AVERAGE(SMALL(分數,{1,2,3,4}))

先以 SMALL(分數,{1,2,3,4}) 找出最小的四個數,再以 AVERAGE 函數求平均。

(3) 求取前10名的平均

儲存格E4:{=AVERAGE(LARGE(分數,ROW(1:10)))}

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

因為要求前十名,如果以(1)的方式,則公式中要輸入 {1,2,3,4,5,6,7,8,9,10},十分不方便,而且容易出錯。

所以改用陣列公式的方式,並且以 ROW(1:10) 來取代 {1,2,3,4,5,6,7,8,9,10}。

(4) 求取第11名至第15名的平均

儲存格E5:{=AVERAGE(LARGE(分數,ROW(11:15)))}

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

有了(3)的觀念,則可以套用 ROW(11:15) 來取代 {11,12,13,14,15}

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

在 Excel 中經常會放入由網頁中複製而來的資料,有一點麻煩的是,想要消掉資料中的空白字元時會遇到問題(參考如下圖在網頁中的文字有許多的空白字元):

將文字複製到儲存格A2:A4時,空白字元自動消失到「好像」剩下一個(觀察A欄),試著以 TRIM 函數來消掉空白字元,竟然完全沒有作用。

儲存格B2:=TRIM(A2)

這是因為網頁中的空白是以「 」來表示,這是一個非列印字元(non-printing characters),所以無法以 TRIM 函數來將空白字元取代為空字串。其實這個看起來像空白的非列印字元為:CHAR(160),而非空白字元:CHAR(32)。

可以改用:儲存格C2:=SUBSTITUTE(A2,CHAR(160),"")

如果使用:儲存格C2:=SUBSTITUTE(TRIM(A2),CHAR(160),"")

則一次將兩種看起來空白的字元都消掉。

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

在 Excel 中依成績判定等第的作法常見使用 VLOOKUP 函數,這次要使用其他函數來練習。在下圖右為設定好的分數區間對應評等,其定義為 100~85:A,84~70:B,69~60:C,59~40:D,39~0:E。

成績判定的公式如下:

儲存格D2:=CHOOSE(MATCH(C2,$F$2:$F$6,-1),$G$2,$G$3,$G$4,$G$5,$G$6)

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

MATCH(C2,$F$2:$F$6,-1):找出在C欄中的成績對應分數區間的第幾個分數,其中參數「-1」乃要找出大於且最接近的數值位在第幾個。

再利用 CHOOSE 函數根據 MATCH 函數所得數值,對應一個等第的儲存格。

 

【補充說明】

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

MATCHhttp://office.microsoft.com/zh-tw/excel-help/HP010342679.aspx

MATCH:搜尋某儲存格範圍內的指定項目,然後再傳回該項目在範圍內的相對位置。

語法:MATCH(lookup_value, lookup_array, [match_type])

lookup_value:在 lookup_array 中尋找比對的值。

lookup_array:要搜尋的儲存格範圍。

match_type:這是一個數字,其值有三種可能:(預設值為 1)

1 或省略:找到等於或僅次於 lookup_value 的值。lookup_array 引數內的值必須以遞增次序排列。

0:找第一個完全等於 lookup_value 的比較值。lookup_array 引數內的值可以依任意次序排列。

-1:找到等於或大於 lookup_value 的最小值。lookup_array 引數內的值必須以遞減次序排序。

 

CHOOSEhttp://office.microsoft.com/zh-tw/excel-help/HP010342269.aspx

CHOOSE:使用index_num從引數值清單中傳回值。

語法:CHOOSE(index_num,value1,[value2],...)

Index_num:指定所選取的數值引數。

如果index_num1CHOOSE會傳回value1;如果為2,則CHOOSE會傳回value2;依此類推。

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

快要過年了,整理書房時,挖出了收藏已久的古早的磁碟片,小朋友看到了嚇了一跳,怎麼會有這麼大的磁碟片呢?和一般的磁碟片比一比,大的是8吋,小的是3.5吋。(軟式磁碟片的大小以它的磁碟片直徑來區分)

下圖的白色區域是一張A4紙張,要讀這片軟碟的磁碟機,體積也是不小的。很多人連3.5吋的磁碟片都沒用過(或沒看過)呢!就把這一大一小的磁碟片收好,以後當作古懂囉!

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

在 Excel 中,如果輸入一個網址,想要自動取出機構名稱,並製成超連結(參考下圖),該如何處理?(本題假設機購名稱位於第一個「.」和第二個「.」之間)

【方法一】

儲存格B2:=HYPERLINK("http://"&A2,UPPER(MID(A2,SEARCH(".",A2)+1,SEARCH(".",A2,SEARCH(".",A2)+1)-SEARCH(".",A2)-1)))

SEARCH(".",A2)-1):找出第一個「.」的位置。

SEARCH(".",A2,SEARCH(".",A2)+1):找出第二個「.」的位置。

MID(A2,SEARCH(".",A2)+1,SEARCH(".",A2,SEARCH(".",A2)+1)-SEARCH(".",A2)-1):找出第一和第二個「.」之間的字元。

接著使用 UPPER 函數,將文字改為大寫。

HPERLINK 函數中的超連結參數必須加上「http://」。

 

【方法二】

儲存格B2:=HYPERLINK("http://"&A2,UPPER(SUBSTITUTE(MID(SUBSTITUTE($A2,".",REPT("*",30)),31,30),"*","")))

取出機關名稱的方法,可以參考另一篇文章的說明:
Excel-模擬資料剖析(http://isvincent.blogspot.com/2012/01/excel_11.html)

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

在 Excel 中要計算資料的個數方法有很多,這次來練習 EXACT 函數。EXACT 函數可以比較兩個儲存格中的字串是否相同。根據下圖左的資料數列,要找出各個項目的個數(參考下圖右)。

儲存格D2:=SUMPRODUCT(--EXACT($A$2:$A$20,C2))

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

EXACT($A$2:$A$20,C2):產生Fasle,False,True,False,Fasle,True,…的陣列。

--EXACT($A$2:$A$20,C2):產生0,0,1,0,0,1,…的陣列。

SUMPRODUCT 函數可以將上式中所有的 0 和 1 加總,即為個數。

【補充說明】

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

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

比較兩個文字字串,傳回 TRUE 代表兩個完全相同,傳回 FALSE 代表兩個不相同。EXACT 會區分大小寫,但忽略格式設定上的差異。使用 EXACT 可以測試文件中的文字。

【延伸學習】

上述是為了教學說明,其實以下的寫法即可算出答案:

儲存格D2:=SUMPRODUCT(--($A$2:$A$20=C2))

因為 $A$2:$A$20=C2 即可得到 True / False 陣列。

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

在 Excel 中有一個數列組成的資料表(參考下圖[上]),如果想要重新由小到大或由大到小排序其中的數列(參考下圖[下]),該如何處理?

(1) 由小到大排序

儲存格A12:=SMALL($A$1:$J$10,(ROW(1:1)-1)*10+COLUMN(A1))

複製儲存格A12,貼至儲存格A12:J21。

(ROW(1:1)-1)*10+COLUMN(A1):依序產生 1, 2, 3, …, 10, 11, 12, …, 99, 100 等數字。

第1列為1~10,第2列為11~20,第3列為21~30,…,第10列為91~100。

由上列數字透過 SMALL 函數找出其中第幾小的數值。

 

(2) 由大到小排序

儲存格A12:=LARGE($A$1:$J$10,(ROW(1:1)-1)*10+COLUMN(A1))

複製儲存格A12,貼至儲存格A12:J21。

原理同(1),由 (ROW(1:1)-1)*10+COLUMN(A1) 數字透過 LARGE 函數找出其中第幾小的數值。

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

1 2

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼