火星人的天空之城

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

2012-01-28T13:02:00.001+08:00

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

BIN2DEC 函數：會將二進位數字轉換成十進位
BIN2HEX 函數：會將二進位數字轉換成十六進位
BIN2OCT 函數：會將二進位數字轉換成八進位
DEC2BIN 函數：會將十進位數字轉換成二進位
DEC2HEX 函數：會將十進位數字轉換成十六進位
DEC2OCT 函數：會將十進位數字轉換成八進位
HEX2BIN 函數：會將十六進位數字轉換成二進位
HEX2DEC 函數：會將十六進位數字轉換成十進位
HEX2OCT 函數：會將十六進位數字轉換成八進位

但是，如果要轉換含小數的二進位數為十進制數，則會產生「#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。

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

有趣的Hello TAIWAN(臺灣)

2012-01-27T11:14:00.001+08:00

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

橫看是「HELLO TAIWAN」：

直看是「臺灣」：

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

台南虎頭埤

2012-01-27T00:55:00.001+08:00

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

在其官方網頁中(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-01-26T22:46:00.001+08:00

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

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

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

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

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

台南都會公園

2012-01-26T22:21:00.001+08:00

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

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

返鄉過年順道遊台南鹽水

2012-01-26T21:57:00.001+08:00

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

檢視較大的地圖

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

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

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

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

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

檢視較大的地圖

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

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

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

Excel-多條件的查詢(INDEX+SMALL+陣列)

2012-01-19T11:28:00.001+08:00

有網友根據上一篇文章：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，往下各列貼上。

【補充資料】

關於詳細函數說明，請參考微軟網站：

INDEX：http://office.microsoft.com/zh-tw/excel-help/HP010342608.aspx

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

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

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

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

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

IFERROR：http://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!。

Excel-多條件的查詢(INDEX+MATCH+陣列)

2012-01-18T23:51:00.001+08:00

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

【準備工作】

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

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

【公式說明】

儲存格D23：{=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 函式，讓其傳回「無此零件」訊息。修改如下：

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

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

【補充資料】

關於詳細函數說明，請參考微軟網站：

INDEX：http://office.microsoft.com/zh-tw/excel-help/HP010342608.aspx

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

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

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

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

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

MATCH：http://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 引數內的值必須以遞減次序排序。

IFERROR：http://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!。

Excel-計算部分名次的平均(陣列+LARGE+ROW)

2012-01-18T15:10:00.001+08:00

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

Excel-消去資料中的空白字元(TRIM+SUBSTITUTE)

2012-01-18T00:01:00.001+08:00

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

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

Excel-判定成績等第(MATCH+CHOOSE)

2012-01-16T23:40:00.001+08:00

在 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 函數所得數值，對應一個等第的儲存格。

【補充說明】

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 引數內的值必須以遞減次序排序。

CHOOSE：http://office.microsoft.com/zh-tw/excel-help/HP010342269.aspx

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

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

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

如果index_num為1，CHOOSE會傳回value1；如果為2，則CHOOSE會傳回value2；依此類推。

古老的磁碟片

2012-01-16T23:00:00.001+08:00

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

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

Excel-由網址中取出名稱並製作超連結

2012-01-15T21:59:00.001+08:00

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

Excel-計算資料個數(SUMPRODUCT+EXACT)

2012-01-15T21:07:00.001+08:00

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

Excel-重新依序排列表格中的數列

2012-01-15T13:38:00.001+08:00

在 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 函數找出其中第幾小的數值。

Excel-找出沒有連續遞增的數值

2012-01-14T15:31:00.001+08:00

在 Excel 的工作表中有一個資料，大部分的資料都是連續的遞增數值(參考下圖左)，如何找出不是連續遞增的資料(參考下圖右)？

【解法】

如果單從一些儲存格數值要找不出不連續的儲存格並不容易，所以需要一些輔助儲存格，例如「項次」欄位(其是一些遞增1的數值)。先找到「項次」欄位的儲存格內容，再由這個儲存格內容查表找到對應的數值。

(1) 找出非連續遞增數值的項次

儲存格D2：{=IFERROR(SMALL(IF($B$3:$B$26-$B$2:$B$25>1,$A$3:$A$26,FALSE),ROW(1:1)),"")}

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

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

$B$3:$B$26-$B$2:$B$25>1：找出下一個儲存格大於上一個儲存格超過1的儲存格(即數值不連續的儲存格)。

IF($B$3:$B$26-$B$2:$B$25>1,$A$3:$A$26,FALSE)：將找到的上述儲存格對應「項次」儲存格。

SMALL(IF($B$3:$B$26-$B$2:$B$25>1,$A$3:$A$26,FALSE),ROW(1:1))：指出上述儲存格對應「項次」儲存格中第1,2,3,…小的項次號碼。

透過 IFERROR 函數，將產生錯誤訊息的儲存格顯示空白字串。

(2) 找出非連續遞增數值的項次所對應的儲存格資料

儲存格E2：=IFERROR(VLOOKUP(D2,$A$1:$B$26,2,FALSE),"")

利用 VLOOKUP 函數以查表方式，由項次號碼找出對應的資料。

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

Excel-取用不同工作表中相同儲存格的值

2012-01-14T13:39:00.001+08:00

在 Excel 中有許多工作表，通常會有相同的架構，如果想要取用相同工作表的同一儲存格時，該如何下公式？

如果你輸入的公式是：

儲存格D1：=工作表1!A2

則當你複製儲存格D1，往下各列貼上時會產生：

儲存格D2：=工作表1!A3

儲存格D3：=工作表1!A4

…

儲存格D1：=INDIRECT("工作表"&ROW(1:1)&"!A2")

複製儲存格D1，往下各列貼上。

利用 INDIRECT 函數將字串「"工作表"&ROW(1:1)&"!A2"」轉換成位址。

ROW(1:1)=1，往下各列貼上時，會變為ROW(2:2)=2，ROW(3:3)=3，…。

以本例來看，公式可簡化為：

儲存格D1：=INDIRECT(C1&"!A2")

【補充資料】

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

INDIRECT：http://office.microsoft.com/zh-tw/excel-help/HP010342609.aspx

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

語法：INDIRECT(ref_text,[a1])

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

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

Excel-不複製被隱藏的儲存格

2012-01-12T23:20:00.001+08:00

在 Excel 的工作表中有些時候我們可能會隱藏某些欄(列)，如果我們複製的儲存格區域包含了隱藏的儲存格(如下圖中的B欄被隱藏了)：

在貼上時會將被隱藏的資料也一併複製貼上了：

如果想要在貼上時不要包含被隱藏的資料該如何處理呢？其實只要多一個動作即可。

1. 先選取要複製的儲存格區域(其中包含了被隱藏的儲存格(B欄))。

2. 按一下 Alt+; 鍵。(觀察下圖，其實選取區被分為儲存格A1:A9和儲存格C1:D9。)

3. 複製後貼上，並不會貼上複隱藏的部分。

Excel-模擬資料剖析

2012-01-11T16:04:00.001+08:00

在網路上發現有人用公式來處理資料部析的方式不錯，很有創意，可以來介紹一下。參考下圖，在A欄中有一些字串，其中的文字以「/」做為分隔，現在希望將每段分隔的文字，分別放在右側的儲存格中，該如何處理？

假設字串中不含「*」字元。

儲存格B2：=SUBSTITUTE(MID(SUBSTITUTE($A2,"/",REPT("*",30)),30*(COLUMN(A2)-1)+1,30),"*","")

複製儲存格B2，貼至儲存格B2:D5。

公式中的「30」是假設在A欄中的字串不會超過30個字元。

REPT("*",30)：產生一個30個「*」的字串。

SUBSTITUTE($A2,"/",REPT("*",30))：將「/」以前述之30個「*」字串取代。

以ABC/DEF/OP字串為例，會產生：

「ABC******************************DEF******************************OPQ」字串。

30*(COLUMN(A2)-1)+1：在B欄的結果為1，在C欄的結果為31，在D欄的結果為61，…

MID(SUBSTITUTE($A2,"/",REPT("*",30)),30*(COLUMN(A2)-1)+1,30)：在上式的運算結果做為取字串的起點，每次取30個字。

如果在B欄，則結果為「ABC******************************」。(由第1個字取30個字)

如果在C欄，則結果為「***DEF***************************」。(由第31個字取30個字)

如果在D欄，則結果為「******DEF」。(由第61個字取30個字，字元數不足30字)

最後利用 SUBSTITUTE 函數，將上述字串中的「*」全部去除，即可得想要的字串。

Excel-輸入網址後快速移除超連結

2012-01-10T23:35:00.001+08:00

當你在 Excel 的工作表中輸一個網址或 Email 位址，在按下 Enter 鍵時，其預設會自動建立超連結。可是，有時你並不想要這個超連結，要如何不要這個超連結呢？方法很簡單：你只要在輸入好網址後，隨即按一下 Ctrl+Z 鍵，可以馬上移除超連結。

在 Word 中相同方式也可行。

Word-將段落依每段第一個字排序

2012-01-10T23:23:00.001+08:00

在 Word 中如果你想將段落依每段的第一個字來排序，可以使用［常用/段落］中的「排序」功能。

1. 選取要排序的段落。

2. 按一下［常用/段落］中的「排序」按鈕。

3. 選取要排序的類型，例如「筆劃、數字、日期、注意」等，並選取「遞增/遞減」。

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

段落即會依設定重排：

如果你的資料含有欄位名稱列，則可以選取欄位名稱來排序：

本例為選擇「處室」欄位，以遞減方式排序：

微軟照片接合軟體-Image Composite Editor

2012-01-09T23:58:00.001+08:00

介紹一個由微軟提供的免費照片接合軟體(Image Composite Editor)，可以幫助你建立類似全景圖照片。你只要將多張不同角度拍得的照片集合在一起，即可自動拼接這些照片，比使用任何的廣角鏡頭拍得還要廣角。

軟體網頁：http://research.microsoft.com/en-us/um/redmond/groups/ivm/ice/

下載 32 位元版：

http://research.microsoft.com/en-us/downloads/730cd6bb-6450-4e66-8101-a94e71cb0779/default.aspx

下載 64 位元版：

http://research.microsoft.com/en-us/downloads/69699e5a-5c91-4b01-898c-ef012cbb07f7/default.aspx

下載安裝後，找到你要接合的照片(可以拿起相片，儘量維持一樣的水平角度，由左至右隨意按下快門)：

將這些照片拖曳至視窗中，系統立即執行接合動作：

因為接合照片後，在邊緣通常會有一些不規則的部分，你可以拖曳白色的框線，來定義所要的接合內容：

當你調整白色框線時，在 Crop 區中的數值會跟著調整，也可以按一下 Automatic crop 按鈕來自動切割：

(你可以調 Camera motion 項目，來改變接合的位置。)

當你調整好了之後，接著來設定輸出的部分，你可以在 Format 中選取輸出格式，也可以在 Quality 中設定品質。並且可以調整輸出照片的大小(比例或實際大小)：

按一下 Export to disk 按鈕，即可輸出這張接合好的照片。(參考下圖)

Google-以圖搜尋

2012-01-09T20:28:00.001+08:00

Google 已推出以圖搜尋功能有好一陣子了，感覺上要找相似圖片還真方便。

如果你在 Google 圖片搜尋中，將找到的圖片，直接拖曳到搜尋框中：

則可以用所選擇的圖片來搜尋：

你也可以將某個網頁上的圖片，取得其網址：

然後在 Google 圖片搜尋框中，按一下照相機圖示(以圖搜尋)：

將複製的網址貼入方塊中，按一下[搜尋]按鈕：

也可以進行以圖找圖：

或許你也可以用來找看看由你原創的圖，被那些網頁給「借用」了。

Excel-邏輯XOR運算

2012-01-08T22:57:00.001+08:00

在 Excel 的函數提供了 NOT、AND、OR 等函數，但是沒有提供 XOR 運算(互斥或運算)的相關函數，該如何執行這個邏輯函數的運算呢？

XOR 邏輯函數的基本概念是輸入 X, Y ，兩者相同時，輸出為 False；兩者不同時，輸出為 True。我們要利用 NOT、AND、OR 三個函數來執行 XOR 運算。

XOR(X,Y) = OR(AND(X, NOT Y), AND(Y, NOT X))

【不處理空白】

儲存格C3：=OR(AND(A3,NOT(B3)),AND(B3,NOT(A3)))

複製儲存格C3，往下各列貼上。

因為不考慮儲存格為空白的狀況，所以輸入 X 或 Y，只要有一個或二個為空白，都會造成輸出結果為 True。

【不接受空白】

儲存格G3：=IF(OR(E3="",F3=""),NA(),OR(AND(E3,NOT(F3)),AND(F3,NOT(E3))))

複製儲存格G3，往下各列貼上。

如果我們不接受輸入為空白，透過這個公式，可以將輸出顯示為 #N/A。

Excel-查詢後修正部分資料

2012-01-08T20:19:00.001+08:00

在 Excel 中有一個價格資料表(如下圖左)，因為有了新的價目表，其中只有部分的價格做了調整(如下圖右)，如何製作新的價目表呢？其中沒有調價的項目維持原價，而有調價項目以新價格取代。

【準備工作】

選取儲存格G1:H12，按一下 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：調價項目和新價格。接著，定義名稱：資料，範圍為儲存格G1:H12。

【輸入公式】

儲存格D2：=IFERROR(INDEX(資料,MATCH(B2,調價項目,0)+1,2),C2)

MATCH(B2,調價項目,0)：查出B欄中的項目位在新價目中的「調價項目」欄位中的第幾列，如果查不到資料，則會傳回錯誤值。

INDEX(資料,MATCH(B2,調價項目,0)+1,2)：將 MATCH 函數查得的數字代入 INDEX 函數，查詢在「資料」中的第 2 個欄位(新價格)的值，其中「+1」是因為在 INDEX 函數中的查詢欄位必須要具有欄位名稱。

IFERROR 函數用以判斷如果傳回錯誤訊息，則表示在新價目表中沒有該項目，即沒有更新價目，所以用原價格表示。

儲存格E2：=IF(ISERROR(MATCH(B2,調價項目,0)),"","新調價")

參考上述說明，在備註欄中如果傳回錯誤訊息，則顯示空白，否則顯示「新調價」。

複製儲存格D2:E2，往下各列複製。

【補充資料】

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 引數內的值必須以遞減次序排序。

INDEX：http://office.microsoft.com/zh-tw/excel-help/HP010342608.aspx

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

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

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

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

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