贊助廠商

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

搜尋本部落格文章資料

網友問到一個 Excel 的問題:參考下圖,如果將姓名欄位整併在一起並且扣除空白後,每個姓名一列。

在下圖中,姓名欄位中每幾個姓名即有一個空白儲存格,想要將這些姓名集合在一個圖文框中,並且每一列一個姓名。

Excel-串接儲存格內容(CONCATENATE,PHONETIC)

 

【公式設計與解析】

你可能會使用 CONCATENATEPHONETIC 函數,但是要注意:

若是使用 CONCATENATE 函數來串接儲存格,例如公式:=CONCATENATE(C2,C3, … , C27),其中的參數若是過於冗長,則輸入公式及公式維護都較不容易。

若是使用 PHONETIC 函數來串接儲存格,例如公式:=PHONETIC(C2:C27),其中儲存格C2:C27必須全為文字,若有數字或是公式,則儲存格內容不會被串接。

所以只好使用一個輔助欄位,如上圖的E欄。

儲存格E2:=IF(C2="",E1&C2,E1&C2&CHAR(10))

其中 CHAR(10) 用以在字串中加入『換行』指令。

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

選取文字方塊,在公式編輯列中輸入:=E27,按下 Enter 鍵。

image

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

在下圖中,網友想要根據一個基本表(由員工、地點、時薪組成),要在日期清單中依地點和員工查詢時薪,再根據輸入的時數求得薪資,該如何處理?

依題意,地點和員工是輸入的資料,時薪是要以公式求得,薪資=時薪X時數。

Excel-根據兩個條件來查詢(SUMPRODUCT,LOOKUP)

 

【公式設計與解析】

(1) 使用 SUMPRODUCT 函數

儲存格D2:=SUMPRODUCT((B2=$I$2:$I$7)*(C2=$H$2:$H$7)*$J$2:$J$7)

(B2=$I$2:$I$7)*(C2=$H$2:$H$7):兩個條件之間的『*』,相當於執行邏輯 AND 運算,傳回 TRUE/FALSE 陣列。第二個『*』表示乘法運算,可將 TRUE/FALSE 陣列轉換為 1/0 陣列。

最後在 SUMPRODUCT 函數中,將上述結果和儲存格J2:J7陣列執行乘積和,結果即為所求。

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

 

(2) 使用 LOOKUP 函數

儲存格D2:=LOOKUP(1,1/((B2=$I$2:$I$7)*(C2=$H$2:$H$7)),$J$2:$J$7)

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

詳細說明可參考:http://isvincent.pixnet.net/blog/post/43985656

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

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼