[發問] 計算不重複出現的日期

PJChen

大大們,

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

計算不重複出現的日期.zip (6.78 KB)

下載次數: 6

2013-4-12 17:23

Bodhidharma

回復 1# PJChen

既然你的日期是有排序的，那就用陣列公式
{=SUM(IF(B2:B26<>B3:B27,1,0))}

Bodhidharma

回復 2# Bodhidharma

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

note: if(...,1,0)可以改成1*(...)

PJChen

回復 3# Bodhidharma

請問如果是資料會增加的情形下,也可用陣列,然後將公式直接下拉嗎?

Bodhidharma

回復  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

回復 5# Bodhidharma

抱歉，row(B2:B26)-1傳回的陣列應該是{1,2,3,...,25}，沒有26

PJChen

回復 6# Bodhidharma

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

不過你若要我提出一個實例我又提不出,因為我搜集了好多年的函數,巨集的一些範例,因為前陣子硬碟壞了,幾年的資料一舜間都不見了...

Bodhidharma

回復 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)
原本是要三行輔助列，但是使用陣列公式，就可以直接達到效果

下載 (139.3 KB)

2013-4-13 00:09

計算不重複出現的日期_說明.rar (7.45 KB)

下載次數: 2

2013-4-13 00:10

Bodhidharma

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

回復 9# Bodhidharma

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