帖子: 12
主題: 4
精華: 0
積分: 25
點名: 0
作業系統: WIN7
軟體版本: office 2010
閱讀權限: 10
性別: 男
來自: 台北
註冊時間: 2012-6-7
最後登錄: 2017-6-29

1^# 跳轉到 » 倒序看帖

字體大小: tT

發表於 2014-12-15 18:22 | 只看該作者

[發問] EXCEL 抽樣

大家好

我需要抽樣

我的資料格式如下(每個工作表為958欄*1226列，共16個工作表，命名為工作表1~工作表16)

現在我需要在每個工作表抽出500筆數據(抽出的位置需相同，有個限制條件不能抽到數值為-9999)

將每個工作表抽出的500筆按不同工作表名稱來排在不同欄位並統一放在工作表17(工作表1排在欄位A、工作表2排在欄位B、依此類推...)

不知道該如何寫呢

感謝

樓主熱帖

peter800725

小學生 Rank: 1

帖子: 12
主題: 4
精華: 0
積分: 25
點名: 0
作業系統: WIN7
軟體版本: office 2010
閱讀權限: 10
性別: 男
來自: 台北
註冊時間: 2012-6-7
最後登錄: 2017-6-29

2^#

發表於 2014-12-16 11:00 | 只看該作者

有沒有人可以幫我解答

TOP

ashan0418

中學生 Rank: 2

帖子: 59
主題: 5
精華: 0
積分: 75
點名: 278
作業系統: xp
軟體版本: 2003
閱讀權限: 20
性別: 男
來自: 基隆
註冊時間: 2012-7-5
最後登錄: 2024-11-29

2023年全勤獎章 2022虎年全勤獎章 2021牛年全勤獎章 2020鼠年全勤獎章 2019 豬年全勤獎章 2018 狗年全勤獎章 2017 雞年全勤獎章 2016猴年全勤獎章 2015羊年全勤獎章

3^#

發表於 2014-12-19 09:22 | 只看該作者

回復 1# peter800725

您每個工作表有958欄，是那一欄要抽出500筆，而這一欄的資料型態是什麼??

TOP

peter800725

小學生 Rank: 1

帖子: 12
主題: 4
精華: 0
積分: 25
點名: 0
作業系統: WIN7
軟體版本: office 2010
閱讀權限: 10
性別: 男
來自: 台北
註冊時間: 2012-6-7
最後登錄: 2017-6-29

4^#

發表於 2014-12-29 12:22 | 只看該作者

回復 3# ashan0418

是一個工作表總共要抽出500筆資料
沒有限定是哪一欄
應該就是空間隨機抽樣

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-12-29 14:54 | 只看該作者

每個工作表為958欄*1226列，共16個工作表
這資料量很大，使用公式會跑很久

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

TOP

ashan0418

中學生 Rank: 2

帖子: 59
主題: 5
精華: 0
積分: 75
點名: 278
作業系統: xp
軟體版本: 2003
閱讀權限: 20
性別: 男
來自: 基隆
註冊時間: 2012-7-5
最後登錄: 2024-11-29

2023年全勤獎章 2022虎年全勤獎章 2021牛年全勤獎章 2020鼠年全勤獎章 2019 豬年全勤獎章 2018 狗年全勤獎章 2017 雞年全勤獎章 2016猴年全勤獎章 2015羊年全勤獎章

6^#

發表於 2014-12-29 15:22 | 只看該作者

本帖最後由 ashan0418 於 2014-12-29 15:28 編輯

回復 4# peter800725

跟您確認一下以下的問題

1. 16個分頁皆有958欄*1226列的數據，要隨機抽出500筆
2. 抽出的位置需相同是指，若隨機抽到的儲存格為A1，那16個分頁A1的值不能為-9999，才是要的資料嗎??

若可以，請將檔案上傳!!

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猴年全勤獎章

7^#

發表於 2014-12-29 17:47 | 只看該作者

16個分頁皆有958欄1226列的數據，要隨機抽出500筆.rar (247.34 KB)
資料指模擬3個工作簿，公式只放3欄，請參考
資料及公式設好後，按F9重新計算(目前設為手動計算)，然後去喝杯咖啡看個影片。

1. 工作表A，是找出Sheet1~16同位置沒有 -9999者，填上 RAND()
A1 =IF(OR(N(INDIRECT("sheet"&ROW($1:$16)&"!rc",))=-9999),"",RAND())
陣列公式，下拉右拉

2. 工作表B，由工作表A找出前500大，並轉為行列位置(列*10^4+行)
A1 =IF(A!A1="","",IF(RANK(A!A1,INDIRECT("A!R1C1:R1226C958",))>500,"",ROW()*10^4+COLUMN()))
下拉右拉

3. 工作表C，A欄將工作表B中以SMALL依順序取出，B欄~Q欄就為Sheet1~Sheet16的資料
A2 =SMALL(INDIRECT("B!R1C1:R1226C958",),ROW()-1)
下拉
B2 =INDIRECT("Sheet"&B$1&"!"&TEXT($A2,"!R0!C0000"),)
下拉右拉

注意! 公式計算改為手動，資料修改好按F9重新計算

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

TOP

peter800725