贊助廠商

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

搜尋本部落格文章資料

在 Excel 中當我們在執行查詢時,有時會有錯誤的訊息產生,該如何避免出現這些訊息,而改以其他適當的方式來呈現?

在下圖中,當你使用 VLOOKUP 函數查詢時,若是查詢到空的儲存格,則會傳回 0,但是應該是顯示空白比較恰當。或是使用錯誤內容來查詢,正常會傳回 #N/A 這類的訊息,該如何以其他文字來回應呢?

Excel-錯誤訊息的處理(IFERROR,ISERROR)

【公式設計與解析】

(1) 儲存格E4:=VLOOKUP(D4,A2:B17,2,FALSE)

當使用 VLOOKUP 函數查詢,由於儲存格B7為空白,所以應該傳回空白,而非傳回 0。

(2) 儲存格E5:=VLOOKUP(D5,A2:B17,2,FALSE)&""

公式修正為將原公式,再串接一個空字串『""』即可解決。

(3) 儲存格E9:=VLOOKUP(D9,A2:B17,2,FALSE)

這是要查詢「王」這個項目所對應的數值。

(4) 儲存格E10:=VLOOKUP(D10,A2:B17,2,FALSE)

如果不小心以「壬」來查詢,則會傳回 #N/A 錯誤訊。

(5) 儲存格E11:=IFERROR(VLOOKUP(D10,A2:B17,2,FALSE),"查無此項")

利用 IFERROR 函數,將 #N/A 訊息,改以『查無此項』文字來顯示。

(6) 儲存格E11:=IF(ISERROR(VLOOKUP(D10,A2:B11,2,FALSE)),"查無此項",
VLOOKUP(D10,A2:B11,2,FALSE))

如果你使用的 Excel 試算表沒有 IFERROR 函數,則可以使用 ISERROR 來處理。

, ,

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

網友問到:在 Excel 中有一個資料表,如何計算出每個日期的配編數有幾類?

(請參考下圖)

image


【公式設計與解析】

為了簡化公式,特別利用二個輔助欄位。

1. 搜尋指定日期的起始列號

儲存格F2:{=MIN(IF(D2=INT($A$2:$A$25),ROW($A$2:$A$25),999))}

這是陣列公式,公式輸入完畢,要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。

2. 搜尋指定日期的結束列號

儲存格E2:{=MAX(IF(D2=INT($A$2:$A$25),ROW($A$2:$A$25),0))}

這是陣列公式,公式輸入完畢,要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。

3. 完整公式

儲存格G2:=SUMPRODUCT((1/COUNTIF(OFFSET($B$1,F2-1,0,G2-F2+1,1),
OFFSET($B$1,F2-1,0,G2-F2+1,1))))

請參考其他說明:

http://isvincent.pixnet.net/blog/post/46855459

http://isvincent.pixnet.net/blog/post/47236374

, ,

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

在 Excel 的資料表如下圖左,網友問到如何重組每三個文字插入一個數字,依此規則產生資料清單?

在下圖左之中,A欄由數字組成,B欄由文字組成,如何取出每三個文字插入一個數字?

Excel-資料交錯排列組合(OFFSET,INT,MOD)


【公式設計與解析】

(1) 處理數字部分

儲存格F1:=OFFSET($A$1,INT((ROW(1:1)-1)/4),0)

利用 INT 函數將除法結果再取不大於的最大整數。

公式:INT((ROW(1:1)-1)/4)

當公式向下複製時,產生:0,0,0,0,1,1,1,1,2,2,2,2,3,3,3,3,4,4, ...。

儲存格F1向下複製時,產生:1,1,1,1,2,2,2,2,3,3,3,3,4,4,4,4,5,5, ...。

(2)處理文字部分

儲存格G1:=OFFSET($B$1,ROW(1:1)-INT(ROW(1:1)/4)-1,0)

當公式向下複製時,產生:0,1,2,2,3,4,5,5,6,7,8,8,9,10,11,11,12,13, ...。

儲存格G1向下複製時,產生:甲,乙,丙,丙,丁,戊,己,己,庚,辛,壬,壬,癸,子,丑,丑,寅,卯, ...。

(3) 整合文字和數字

儲存格D1:=IF(MOD(ROW(1:1),4)=0,OFFSET($A$1,INT((ROW(1:1)-1)/4),0),
OFFSET($B$1,ROW(1:1)-INT(ROW(1:1)/4)-1,0))

MOD(ROW(1:1),4)=0:判斷儲存格的列號是否為 4,8,12,16, ...。

公式判斷,每隔四列時,會顯示數字,其餘顯示文字。

當公式向下複製時,會產生資料:甲,乙,丙,1,丁,戊,己,2,庚,辛,壬,3,癸,子,丑,4, ...。

, , ,

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

網友問到:在 Excel 的活頁簿中有 6 個工作表(A單位、B單位、C單位、D單位、E單位、F單位),如何計算這個 6 個工作表中相同位置儲存格的和?

這 6 個工作表的格式一致,其中「數值」欄位的內容不各有不同。

Excel-根據工作表名稱摘要各工作表的小計(INDIRECT)

在下圖中,分別計算A單位~F單位的甲乙丙、丁戊己庚、辛壬癸之小計,即根據工作表名稱,來摘要各工作表的小計,該如何處理?

Excel-根據工作表名稱摘要各工作表的小計(INDIRECT)


【公式設計與解析】

儲存格B2:=SUM(INDIRECT(A2&"!B2:B4"))

先將工作表名稱(儲存格A2)和字串『!B2:B4』加以串接,再利用 INDIRECT 函數將其轉換為儲存格位址。

儲存格C2:=SUM(INDIRECT(A2&"!B5:B8"))

儲存格D2:=SUM(INDIRECT(A2&"!B9:B11"))

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

,

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

網友問到:在 Excel 的工作表中有一個資料表(如下圖),如何計算資料中的的每個項目之小數的和?

如下圖,資料是由 A, B, C, D 再加上一個數字組合而成。如何分別依據各個項目來計算數字的小計。

Excel-取出第1個字元之後的數字計算總和(SUMPRODUCT,LEFT,MID)

【公式設計與解析】

選取資料欄位中有資料的所有儲存格(本例為儲存格A1:A22),按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:資料。

儲存格D2:=SUMPRODUCT((LEFT(資料,1)=C2)*MID(資料,2,999))

在 SUMPRODUCT 函數中利用二個陣列來運算:

LEFT(資料,1)=C2:在陣列公式中取出每個儲存格中的第一個字元,並判斷是否和儲存格 C2 的內容相同。傳回 TRUE/FALSE 陣列。

MID(資料,2,999):利用 MID 函數取出第一個字元以外的所有字元。(其中 999 只是一個較大的數,即使這個數字超過字串長度,仍可正常運作。)

公式中的『*』運算,可以將 TRUE/FALSE 陣列轉換為 1/0 陣列。

, , ,

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

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼