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

rosebud

大家好，請教一個問題：

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

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

下載次數: 4

2014-8-29 15:35

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

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

ML089

直接取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")

Hsieh

回復 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筆資料可看出來

下載 (37.8 KB)

2014-8-31 23:44

rosebud

謝謝兩位版主指點～

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

下載次數: 1

2014-9-5 15:48

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

ML089

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

ML089

有意見詢問時，請按意見下方的回覆，這樣才能收到通知。

rosebud

回復 6# ML089


    好的~謝謝版主熱心指導^^