贊助廠商

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

搜尋本部落格文章資料

在 Excel 的工作表中(如下圖),如果想要根據日期清單列出不重覆的日期,該如何處理?

手動方式可以參考以下二篇文章:

Excel-手動產生不重覆項目(樞紐分析表)

Excel-使用篩選工具列出不重覆的資料

如果你想要使用公式,則參考以下的做法。

Excel-列出不重覆的清單(OFFSET,SMALL,ROW)


【公式設計與解析】

本例使用一個輔助欄位C欄。

1. 計算同一個日期由第一個儲存格起算累計有幾個。

儲存格C2:=COUNTIF($A$2:A2,A2)

儲存格C2,貼至儲存格C2:C25。

2. )

儲存格F2:{=IFERROR(OFFSET($A$1,SMALL(IF($C$2:$C$25=1,
ROW($C$2:$C$25),""),ROW(1:1))-1,0),"")}

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

(1) IF($C$2:$C$25=1,ROW($C$2:$C$25),"")

在陣列公式中由C欄中找出標示為『1』的列號。

(2) SMALL(第(1)式,ROW(1:1))

利用 SMALL 函數,根據第(1)式中傳回的列號由小到大,找出第 1,2,3,… 最小者。ROW(1:1)函數,向下複製公式後,會產生 ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→…。

(3) OFFSET($A$1,第(2)式-1,0)

將第(2)式的傳回值代入 OFFSET 函數,得到對儲存格內容(一個日期)。

(4) IFERROR(第(3)式,"")

由於公式向下複製後,可能因為查詢不到對應的儲存格而產生錯誤訊息,利用 IFERROR 函數將錯誤訊息轉換為空字串(顯示為空白)。

, , ,

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

網友問到 Excel 的問題:在工作表中的一個日期和金額清單,網友想要將合於某個日期區間中的金額予以加總,該如何處理?

如下圖,以「01月03日」為例,觀察起日和迄日的日期區間,有 7 個區間中含有「01月03日」,要將這些的金額予以加總。

Excel-位於日期範圍者加總(SUMPRODUCT)


【公式設計與解析】

選取儲存格A1:C21,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:起日、迄日、金額。

儲存格F2:=SUMPRODUCT((起日<=E2)*(迄日>=E2)*金額)

SUMPRODUCT 函數中,利用二個條件來判斷E欄的日期是否位於某個日期區間中,其中 (起日<=E2)*(迄日>=E2) 的『*』,相當於執行邏輯 AND 運算,會將條件判斷的傳回值 TRUE/FALSE 轉換為 1/0,再和金額陣列執行「乘積和」運算。


【延伸學習】

如何將金額欄位中含有指定日期者顯示為紅色?(以儲存格E4為例)

1. 選取儲存格C1:C21。

2. 進入設定格式化的條件的對話框,並設定:

類型:使用公式來決定要格式化哪些儲存格。

規則:=(A2<=$E$4)*(B2>=$E$4)

Excel-位於日期範圍者加總(SUMPRODUCT)

,

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

網友問到:在 Excel 中,如果想要將數字的小數點後第3位,調整為不大於該位數的最大偶數,該如何處理?

(參考下圖)例:7.46545→7.464,7.55870→7.558

Excel-調整小數點後特定位數為偶數/奇數(INT,MOD)

【公式設計與解析】

儲存格C2:=INT(A2*1000)/1000-(MOD(INT(A2*1000),2)=1)*0.001

(1) INT(A2*1000)/1000

將儲存格A2內容,截取至小數點後第 3 位。

(2) MOD(INT(A2*1000),2)=1

判斷小數點後第3位是否為奇數,若是,傳回 TRUE;若否,傳回 FALSE

(3) INT(A2*1000)/1000-(MOD(INT(A2*1000),2)=1)*0.001

若小數點後第3位為奇數,則將截取至小數點後第 3 位的數,再減 0.001。

如果你想要改變調整第 n 位數,則只要變動公式中的 1000 和 0.001 即可。

2→100 和 0.01,即 10^2 和 10^-2

3→1000 和 0.001,即 10^3 和 10^-3

4→10000 和 0.0001,即 10^4 和 10^-4

......

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

網友問到:在 Excel 中有一個資料表(如下圖左),由日期和數值欄位組成。當繪製其統計圖表時,卻出現了原本日期清單中沒有的日期(星期六日),該如何取消顯示這些日期?

在新增圖表時,Excel 判斷了A欄是日期時,即自動以連續的日期來顯示圖表,所以必須要在圖表的座標軸格式設定中處理。

Excel-在圖表中不顯示日期清單沒有的日期

選取座標軸後,進入座標軸格式設定,在座標軸選項中的「座標軸類型」選取「文字座標軸」,即可隱藏這些原本日期清單中沒有的日期。

Excel-在圖表中不顯示日期清單沒有的日期

結果如下:

Excel-在圖表中不顯示日期清單沒有的日期

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

在 Excel 中的「篩選」功能是很好用的資料處理工具。當你篩選資料後,如果想要得知篩選出來的數量,並不需要再做任何處理即可得知,只是這些資訊常被忽略而已。

當你執行了篩選的動作:

Excel-使用篩選功能時得知篩選結果有幾筆資料

當顯示篩選結果時,狀態列上即可得知本次篩選出來的資料數量。

Excel-使用篩選功能時得知篩選結果有幾筆資料

不管你執行幾層篩選,都會顯示篩選出的數量。

Excel-使用篩選功能時得知篩選結果有幾筆資料

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

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼