贊助廠商

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

搜尋本部落格文章資料

在下圖中是一個 Excel 的資料表,如果要計算各個日期中不含空白的不重覆資料個數,該如何處理?

 

【設計公式】

選取儲存格A1:A23,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期。

思考之後,發現得設計一個輔助欄位才能順利設計公式。

儲存格C2:=IF(ISBLANK(B2),"???",B2)

若B欄的儲存格內容為空白,則顯示「???」,這個內容不可以和B欄中的項目內容有所重覆。

(特別說明:原先公式由於在COUNTIF函數使用了「???」來表示空白,這是不好的做法,因為『?』會被視為萬用字元,因此如果項目欄位中使用的項目名稱是三個字元者,都會被看成是空白。要特別注意!建議讀者可以改用例如『///』等字元。)

儲存格F2:
=SUMPRODUCT(1/COUNTIF(OFFSET($A$1,MATCH(E2,日期,0),2,COUNTIF(
日期,E2),),OFFSET($A$1,MATCH(E2,日期,0),2,COUNTIF(日期,E2),)))-IF(COUNTIF(
OFFSET($A$1,MATCH(E2,日期,0),2,COUNTIF(日期,E2),),"???")>=1,1,0)

公式這麼長是要嚇死人嗎?稍微來分解一下:

OFFSET($A$1,MATCH(E2,日期,0),2,COUNTIF(日期,E2),):找出合於儲存格E2的日期儲存格範圍。(別名:日期E2)

其中 MATCH 函數找出符合儲存格E2的第一個儲存格位置,COUNTIF 函數則找出和儲存格E2內容相同的儲存格有幾個。

原公式中變化為二個部分:

(1) SUMPRODUCT(1/COUNTIF(日期E2,日期E2))

求出符合儲存格E2內容的儲存格範圍中包含「???」的不重覆個數。

(若要進一步了解可參考:Excel-計算不重覆的數值個數

(2) IF(COUNTIF(日期E2,"???")>=1,1,0)

求出符合儲存格E2內容的儲存格範圍中是否包含「???」,若是傳回 1,若否傳回 0。

將公式 (1)-(2),即為所求。

創作者介紹

學不完.教不停.用不盡

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


留言列表 (3)

發表留言
  • Emily Huang
  • 老師您好,有個excel的問題想請教,
    就是我一個檔案裡有工作表1、工作表2、工作表3,
    當我刪除工作表3之後,又想再新增一個工作表,
    但它會自動命名為工作表4,若我再刪工作表4,
    再新增會自動命名為工作表5,
    但我希望是從工作表3開始自動編排的~
    請問這是要怎麼去設定才可以,謝謝
  • 我也試了一下,若按一下「+」來自動產生工作表,其產生過的名稱就不會再重覆,即使刪除也是一樣。應讓沒有自動的方法達到你的要求。

    vincent 於 2015/02/02 21:05 回覆

  • Nuno Chan
  • 老師!
    以上公式好像有錯
    項目改為AAA則出錯
    (不知道這是公式的錯還是EXCEL的漏洞)
  • Nuno Chan
  • 如將以上公式由
    儲存格F2:
    =SUMPRODUCT(1/COUNTIF(OFFSET($A$1,MATCH(E2,日期,0),2,COUNTIF(
    日期,E2),),OFFSET($A$1,MATCH(E2,日期,0),2,COUNTIF(日期,E2),)))-IF(COUNTIF(
    OFFSET($A$1,MATCH(E2,日期,0),2,COUNTIF(日期,E2),),"???")>=1,1,0)
    改為
    儲存格F2:
    =SUMPRODUCT(1/COUNTIF(OFFSET($A$1,MATCH(E2,日期,0),1,COUNTIF(日期,E2),),OFFSET($A$1,MATCH(E2,日期,0),1,COUNTIF(日期,E2),)&""))-IF(COUNTIF(OFFSET($A$1,MATCH(E2,日期,0),1,COUNTIF(日期,E2),),"")>=1,1,0)
    則就算項目改為"AAA"(任何三個連續相同字符)都不會算錯,並且不用加輔助欄,
    同時想問問老師
    如果想計算1月內(而非1月1日或1月3日等等)的所有不重覆項目數時,該如何修改以上公式,希望有空指教教
  • 您好,謝謝您發現了這個BUG,原先公式由於在COUNTIF函數使用了「???」來表示空白,這是不好的做法,因為『?』會被視為萬用字元,因此如果項目欄位中使用的項目名稱是三個字元者,都會被看成是空白,可以改用例如『///』等字元。而你想出了解決方案也是不錯。
    如果你想在多個月份中計算一月份的不重覆數量,我再來寫另一篇文章,請再等一下。

    vincent 於 2016/02/27 10:52 回覆

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼