帖子: 18
主題: 9
精華: 0
積分: 77
點名: 0
作業系統: Win7
軟體版本: 2013
閱讀權限: 20
性別: 男
註冊時間: 2010-5-4
最後登錄: 2017-8-15

1^# 跳轉到 » 倒序看帖

字體大小: tT

發表於 2014-8-29 15:52 | 只看該作者

[發問] 取出固定時間間隔內符合條件的資料

大家好，請教一個問題：

假設我的的數據資料是每秒一筆，且連續收集一整天。
目標：篩選出每10分鐘資料中的最低值的前百分之二十。

我的想法是先將資料分成10分鐘一組，然後再找出每組的最低值前20％
因為我不會寫VBA，所以就去找內建函數拼湊如附件。

noisetest.zip (306.68 KB)

說明如下：
1. 原始數據只有兩欄位－TIME和VALUE（都是模擬的不是真實數據）
2. 用Quotient()函數將資料分成600秒一組（10分鐘）
3. 用Percentile()函數找出前20％；或先將資料排序再取出前20％資料。
4. 用樞紐分析或進階篩選方式取出欲求得之資料。
請大家幫忙確認一下作法是否正確？感謝。

另外，這種作法的前提是每秒一筆的資料沒有重覆也沒有缺少，才能固定每600筆分成一組。
但實際收資料時一定不會這麼理想。。。那函數要怎麼修改才能處理這種狀況呢？
還有，要求得資料的前百分之幾的數據是用percentile()這個函數嗎？
（我excel是2013版，用percentile()結果和用用percentile.exe()個有差距耶。）

樓主熱帖

ML089

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 2025
主題: 13
精華: 0
積分: 2053
點名: 0
作業系統: WIN7
軟體版本: Office2007
閱讀權限: 100
性別: 男
來自: 台北市
註冊時間: 2011-3-2
最後登錄: 2024-3-14

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

2^#

發表於 2014-8-31 22:19 | 只看該作者

直接取600筆為一組比較簡單
=IF(B6<PERCENTILE(OFFSET($B$6,INT((ROW()-6)/600),,600),20%),"Y","N")

以每10分鐘內資料為一組，組數約600比上下，公式中需找出範圍的起迄未置
=IF(B6<PERCENTILE(INDIRECT("B"&MATCH(FLOOR(A6+1/86400,10/1440),A:A)&":B"&MATCH(CEILING(A6+1/86400,10/1440),A:A)-1),20%),"Y","N")

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

Hsieh

超級版主 Rank: 8 Rank: 8

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

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

3^#

發表於 2014-8-31 23:44 | 只看該作者

回復 1# rosebud

若有重覆則區間筆數會超過600筆
以600秒分組取得同區間的數值計算其百分比是否在0.2
G6陣列公式
=IF(PERCENTRANK.INC(IF(INT((ROUND(MOD($A$6:$A$6005,1)*86400,0))/600)=INT((ROUND(MOD(A6,1)*86400,0))/600),$B$6:$B$6005,""),B6,2)<0.2,"Y","N")
向下複製
這樣算出來則是00:00:00~00:09:59為區間
最容易看出與你的方式不同在最後2筆資料可看出來

學海無涯_不恥下問

TOP

rosebud

中學生 Rank: 2

帖子: 18
主題: 9
精華: 0
積分: 77
點名: 0
作業系統: Win7
軟體版本: 2013
閱讀權限: 20
性別: 男
註冊時間: 2010-5-4
最後登錄: 2017-8-15

4^#

發表於 2014-9-5 15:49 | 只看該作者

謝謝兩位版主指點～

我將原始工作表複製一份（test2）再把兩位版主寫的公式分別填入H欄和I欄，並且把資料量從6000筆砍到3000筆左右。
為測試若資料有缺漏或重複的狀況下兩公式的反應，故意刪除3筆數據以及添加2筆重複的數據，結果如附件所示，兩公式的結果並不全然一樣。

noisetest.zip (755.72 KB)
是不是有麼細節我沒注意到呢？
ML890版主的公式是，找出範圍的起末，再判斷該區間的資料是否屬於前20％；
Hsieh版主的公式是，找出00:00:00~00:09:59區間，再判斷該區間的資料是否屬於前20％
我是否有誤解呢？

TOP

ML089

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 2025
主題: 13
精華: 0
積分: 2053
點名: 0
作業系統: WIN7
軟體版本: Office2007
閱讀權限: 100
性別: 男
來自: 台北市
註冊時間: 2011-3-2
最後登錄: 2024-3-14

論壇榮譽勳章論壇贊助勳章熱烈參與勳章 2014馬年全勤獎章 2015羊年全勤獎章 2016猴年全勤獎章

5^#

發表於 2014-9-7 00:19 | 只看該作者

回復 4# rosebud

公式修改一下，和你F欄的一樣
=IF(B6<PERCENTILE(INDIRECT("B"&MATCH(FLOOR(A6+1/864000,10/1440),A:A)&":B"&MATCH(CEILING(A6+1/864000,10/1440)-1/864000,A:A)),20%),"Y","N")

{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

ML089