Board logo

標題: [發問] 計算不重複出現的日期 [打印本頁]
作者: PJChen    時間: 2013-4-12 17:24     標題: 計算不重複出現的日期

大大們,

B欄有很多重複的日期,如何在不加輔助公式的情形下,計算不重複出現的日期共有幾個?

[attach]14647[/attach]
作者: Bodhidharma    時間: 2013-4-12 17:32

回復 1# PJChen

既然你的日期是有排序的，那就用陣列公式
{=SUM(IF(B2:B26<>B3:B27,1,0))}
作者: Bodhidharma    時間: 2013-4-12 17:37

本帖最後由 Bodhidharma 於 2013-4-12 17:39 編輯

回復 2# Bodhidharma

沒排序的話陣列公式
{=SUM(IF(MATCH(B2:B26,B:B,0)=ROW(B2:B26),1,0))}

note: if(...,1,0)可以改成1*(...)
作者: PJChen    時間: 2013-4-12 20:07

回復 3# Bodhidharma

請問如果是資料會增加的情形下,也可用陣列,然後將公式直接下拉嗎?
作者: Bodhidharma    時間: 2013-4-12 23:03

本帖最後由 Bodhidharma 於 2013-4-12 23:05 編輯
回復  Bodhidharma

請問如果是資料會增加的情形下,也可用陣列,然後將公式直接下拉嗎?
PJChen 發表於 2013-4-12 20:07


{=SUM(IF(MATCH(OFFSET($B$2,,,COUNTA(B:B)-1),OFFSET($B$2,,,COUNTA(B:B)-1),0)=ROW(OFFSET($B$2,,,COUNTA(B:B)-1))-1,1,0))}
耶…這類公式你應該會常常用到，最好理解一下概念
offset($B$2,,,counta(B:B)-1,)就是從B2開始，高度為counta(B:B)-1的儲存格範圍
因此以你的資料就是B2:B26，因為counta(B:B)-1會隨著資料數增加，所以就達到動態範圍的效果，回傳所有你測資的欄位。
match(B2:B26,B2:B26,0)的陣列公式，會回傳{match(B2:B26,B2,0),match(B2:B26,B3,0),match(B2:B26,B4,0)...match(B2:B26,B26,0)}的陣列，因為match遇到重複值會傳回最前面的，因此這個函數就會傳回所有相同日期第一個出現的位置，你用憑估值公式就可以看出是{1,1,1,1,1,1,1,1,1,10,10,10,10,10,10,16,16,16,16,16,21,21,21,21,21}。要注意的是，因為match是從B2開始，因此"第一個符合"會是1而不是該行列數2。(如果從B1開始就會剛好對應第一個出現的列數)
把這個陣列對應於row(B2:B26)-1，也就是(1,2,3,4,5,6,7,8,...26}，如果相同就傳回1，不同就視為零。也就是match(...)=row(...)的時候，就代表是你要的，不重複出現的日期。把這些東西加總起來即是你要的答案。
作者: Bodhidharma    時間: 2013-4-12 23:07

回復 5# Bodhidharma

抱歉，row(B2:B26)-1傳回的陣列應該是{1,2,3,...,25}，沒有26
作者: PJChen    時間: 2013-4-12 23:38

回復 6# Bodhidharma

謝謝您解釋公式的意義.
我的疑惑正因為我對陣列很陌生,只會用TRANSPOSE的函數,因此不了解何時該將公式變成陣列?
因為有時套用陣列公式,資料增加時,將公式往下拉,好像可以用,但有時又完全不行,實在不了解原因?
有時我問問題時,別人給我一個陣列的公式,雖然套上去可以用,但每次我問到資料會增加的情形下,是否也可用陣列?總是得不到一個直接的答案!!

不過你若要我提出一個實例我又提不出,因為我搜集了好多年的函數,巨集的一些範例,因為前陣子硬碟壞了,幾年的資料一舜間都不見了...
作者: Bodhidharma    時間: 2013-4-13 00:11

本帖最後由 Bodhidharma 於 2013-4-13 00:13 編輯

回復 7# PJChen

唔…你似乎把「陣列公式」和「動態範圍」的概念混雜在一起了

資料增加時，公式仍然適用，這個是「動態範圍」，在你這個case中，我是用OFFSET($B$2,,,COUNTA(B:B)-1)來抓取所有B欄的資料範圍。
「陣列公式」是在處理省略輔助欄，以這個case來說，如果不用陣列公式，那就會像附件那樣，需要加三個輔助欄：
=MATCH(B2,OFFSET($B$2,,,COUNTA(B:B)-1),0)下拉，
=ROW(B2)-1下拉以及
=IF(C2=D2,1,0)下拉
一般來說，match函數就是match(儲存格位置,陣列,0)，row函數就是match(儲存格位置)
但是我現在使用match(儲存格位置陣列,陣列,0)，row(儲存格位置陣列)時，就必需使用陣列公式
MATCH(OFFSET($B$2,,,COUNTA(B:B)-1),OFFSET($B$2,,,COUNTA(B:B)-1),0)陣列公式，就相當於C2:C26 (假設B欄資料是到B26)
ROW(OFFSET($B$2,,,COUNTA(B:B)-1))-1陣列公式，就相當於D2:D26 (假設B欄資料是到B26)
IF(MATCH(OFFSET($B$2,,,COUNTA(B:B)-1),OFFSET($B$2,,,COUNTA(B:B)-1),0)=ROW(OFFSET($B$2,,,COUNTA(B:B)-1))-1,1,0)，就相當於E2:E26 (假設B欄資料是到B26)
原本是要三行輔助列，但是使用陣列公式，就可以直接達到效果
[attach]14650[/attach]
[attach]14651[/attach]
作者: Bodhidharma    時間: 2013-4-13 00:22

match(B2:B26,B2:B26,0)的陣列公式，會回傳{match(B2:B26,B2,0),match(B2:B26,B3,0),match(B2:B26,B4,0)...match(B2:B26,B26,0)}的陣列，因為match遇到重複值會傳回最前面的，因此這個函數就會傳回所有相同日期
Bodhidharma 發表於 2013-4-12 23:03


抱歉這邊寫反了，是回傳{match(B2,B2:B26,0),match(B3,B2:B26,0),match(B4,B2:B26,0),...,match(B26,B2:B26,0)}的陣列
作者: PJChen    時間: 2013-4-13 00:36

回復 9# Bodhidharma

我也不清楚我對陣列公式這樣的印象到底從何而來,看來陣列確實使公式變簡潔了,不過我無法清楚的知道何時該使用陣列,到底是什麼樣的概念時,需要用到它?
作者: Bodhidharma    時間: 2013-4-13 00:39

回復 10# PJChen

我覺得「動態範圍」和「陣列公式」都是excel非常重要的概念，我也是這一兩個月才稍微搞清楚原理
因此說明可能不是很到位
可以講以下我 #5和#8兩篇，有哪個部分是你看不懂的嗎？
作者: Bodhidharma    時間: 2013-4-13 00:44

回復 11# Bodhidharma

兩個我最近看過，比較完整的陣列公式說明網址：
http://gb.twbts.com/index.php/topic,1043.0.html
http://office.microsoft.com/zh-tw/excel-help/HA010228458.aspx#BM1
作者: PJChen    時間: 2013-4-13 01:41

回復 12# Bodhidharma

謝謝您提供的網址,我先自行了解,若有問題再請教您,不然可能也難提出我的問題在哪. . .
作者: Bodhidharma    時間: 2013-4-13 01:50

本帖最後由 Bodhidharma 於 2013-4-13 01:51 編輯

回復 13# PJChen

嗯，其實你這幾天很多問題的癥結點也都是「陣列公式」和 「動態範圍」的概念不清
那兩個網址理解之後，應該可以對陣列公式有基本概念
應該就比較能看懂我的說明(吧)

如此才能真的理解為什麼有時候下拉可以，有時候下拉不行、資料會增加要怎麼處理
或著是定義名稱到底要怎麼用(話說我最近也正在研究~)
作者: Hsieh    時間: 2013-4-14 10:35

本帖最後由 Hsieh 於 2013-4-14 15:32 編輯

回復 1# PJChen
陣列公式
=SUM(1/COUNTIF(OFFSET(B2,,,COUNT(B:B),),OFFSET(B2,,,COUNT(B:B),)))
     OFFSET(B2,,,COUNT(B:B),)是動態範圍隨著日期增加而擴大範圍

所謂陣列公式，則是運算過程會產生一個連續的資料，就是陣列公式
陣列公式通常用於取得資料為一個陣列
在本例中   OFFSET(B2,,,COUNT(B:B),)會得到B2:B26的範圍
COUNTIF(B2:B26,B2:B26)則會傳回25個計算個數的值
{9,9,9,9,9,9,9,9,9,6,6,6,6,6,6,5,5,5,5,5,5,5,5,5,5}
這些數值的倒數加總，就是不重複的數量


另一解法
=SUMPRODUCT((FREQUENCY(OFFSET(B2,,,COUNT(B:B),),OFFSET(B2,,,COUNT(B:B),))>0)*1)
FREQUENCY取得各日期的計數陣列，計算該陣列大於0的數量(因為計數只會出現在第一次日期出現位置，其餘會等於0)



歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)