2011年6月18日 星期六

Excel-對文字排序

在 Excel 中取得一串資料(如下圖A欄),因為每筆資料中包含了「"」字元,所以當你使用 Excel 的排序工具時,將會變成2" → 21" → 3" → ‥‥,而非2" → 3" → ‥‥。如何解決這樣的問題呢?

這是因為 Excel 將 2" 視為文字而非數字來排序所造成的問題。

先將儲存格A2:A21定義名稱為「資料」

儲存格C2:{=SMALL(VALUE(SUBSTITUTE(資料,"""","")),ROW(1:1))&""""}

這是陣列公式,輸入完成時要按 Ctrl+Shift6+Enter 鍵。複製儲存格C2,貼至儲存格C2:C21。

SUBSTITUTE(資料,"""",""):將資料中的「"」字元消除。

VALUE(SUBSTITUTE(資料,"""","")):將消除「"」字元的文字轉成數字。

利用ROW(1:1)=1、ROW(2:2)=2、‥‥,透過SMALL函數來取得第1,2,3,‥‥的數值,由上而下即為排序的結果。

相關函數的詳細說明,請參閱微軟網站:

SUBSTITUTEhttp://office.microsoft.com/zh-tw/excel-help/HP010342927.aspx

SUBSTITUTE:將文字字串中的 old_text 部分以新字串 new_text 取代。

語法:SUBSTITUTE(text, old_text, new_text, [instance_num])

text:要取代字元的文字,或含有該文字之儲存格的參照。

old_text:這是要取代的文字。

new_text:是要用來取代 old_text 的文字。

instance_num:指定要將第幾個 old_text 取代為 new_text

沒有留言:

張貼留言

好康東東