PJChen

　暱稱: PJ

帖子: 913
主題: 150
精華: 0
積分: 1089
點名: 0
作業系統: win10
軟體版本: office 2019
閱讀權限: 50
性別: 女
註冊時間: 2011-8-28
最後登錄: 2023-7-19

論壇榮譽勳章論壇贊助勳章

1^# 跳轉到 » 正序看帖

打印

字體大小: tT

發表於 2013-4-12 17:24 | 只看該作者

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

大大們,

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

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

樓主熱帖

Hsieh

超級版主 Rank: 8 Rank: 8

帖子: 4901
主題: 44
精華: 24
積分: 4916
點名: 254
作業系統: Windows 7
軟體版本: Office 20xx
閱讀權限: 150
性別: 男
來自: 台北
註冊時間: 2010-4-30
最後登錄: 2024-11-23

2023年全勤獎章 2022虎年全勤獎章 2021牛年全勤獎章 2020鼠年全勤獎章 2019 豬年全勤獎章 2018 狗年全勤獎章 2017 雞年全勤獎章 2016猴年全勤獎章 2015羊年全勤獎章 2014馬年全勤獎章 2013蛇年全勤獎章 2012龍年全勤獎章論壇貢獻勳章熱心公益勳章 2011兔年全勤獎章

15^#

發表於 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)

學海無涯_不恥下問

TOP

Bodhidharma

中學生 Rank: 2

帖子: 186
主題: 6
精華: 0
積分: 218
點名: 0
作業系統: win7
軟體版本: office 2007
閱讀權限: 20
性別: 男
註冊時間: 2012-11-12
最後登錄: 2014-4-15

14^#

發表於 2013-4-13 01:50 | 只看該作者

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

回復 13# PJChen

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

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

TOP

PJChen

　暱稱: PJ

大學生 Rank: 4

帖子: 913
主題: 150
精華: 0
積分: 1089
點名: 0
作業系統: win10
軟體版本: office 2019
閱讀權限: 50
性別: 女
註冊時間: 2011-8-28
最後登錄: 2023-7-19

論壇榮譽勳章論壇贊助勳章

13^#

發表於 2013-4-13 01:41 | 只看該作者

回復 12# Bodhidharma

謝謝您提供的網址,我先自行了解,若有問題再請教您,不然可能也難提出我的問題在哪. . .

TOP

Bodhidharma

中學生 Rank: 2

帖子: 186
主題: 6
精華: 0
積分: 218
點名: 0
作業系統: win7
軟體版本: office 2007
閱讀權限: 20
性別: 男
註冊時間: 2012-11-12
最後登錄: 2014-4-15

12^#

發表於 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

TOP

Bodhidharma

中學生 Rank: 2

帖子: 186
主題: 6
精華: 0
積分: 218
點名: 0
作業系統: win7
軟體版本: office 2007
閱讀權限: 20
性別: 男
註冊時間: 2012-11-12
最後登錄: 2014-4-15

11^#

發表於 2013-4-13 00:39 | 只看該作者

回復 10# PJChen

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

TOP

PJChen

　暱稱: PJ

大學生 Rank: 4

帖子: 913
主題: 150
精華: 0
積分: 1089
點名: 0
作業系統: win10
軟體版本: office 2019
閱讀權限: 50
性別: 女
註冊時間: 2011-8-28
最後登錄: 2023-7-19

論壇榮譽勳章論壇贊助勳章

10^#

發表於 2013-4-13 00:36 | 只看該作者

回復 9# Bodhidharma

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

TOP

Bodhidharma

中學生 Rank: 2

帖子: 186
主題: 6
精華: 0
積分: 218
點名: 0
作業系統: win7
軟體版本: office 2007
閱讀權限: 20
性別: 男
註冊時間: 2012-11-12
最後登錄: 2014-4-15

9^#

發表於 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)}的陣列

TOP

Bodhidharma

中學生 Rank: 2

帖子: 186
主題: 6
精華: 0
積分: 218
點名: 0
作業系統: win7
軟體版本: office 2007
閱讀權限: 20
性別: 男
註冊時間: 2012-11-12
最後登錄: 2014-4-15

8^#

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

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

TOP

PJChen

　暱稱: PJ

大學生 Rank: 4

帖子: 913
主題: 150
精華: 0
積分: 1089
點名: 0
作業系統: win10
軟體版本: office 2019
閱讀權限: 50
性別: 女
註冊時間: 2011-8-28
最後登錄: 2023-7-19

論壇榮譽勳章論壇贊助勳章

7^#

發表於 2013-4-12 23:38 | 只看該作者

回復 6# Bodhidharma

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

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

TOP