iT邦幫忙

1

請問 Excel 有方法產生一千萬個介於0~1之間的不重複亂數嗎?

如題,我使用 Excel 2013版本,
目前嘗試的作法是開啟 excel的反覆運算功能,使用函數
=IF(COUNTIF($A$1:$J$1000000,A1)>1,RANDBETWEEN(0,10000000)/10000000,A1) ,填滿工作表欄位 A1:J1000000,共一千萬個儲存格範圍。

https://ithelp.ithome.com.tw/upload/images/20190702/20118578SigDOc1sag.jpg

如圖,用前述方式已經跑了超過12小時仍無結果,
想請問各位大神,excel有沒有其它比較有效率的方式,
例如:使用 Excel VBA?可以產生一千萬個介於0~1之間的不重複亂數?

感謝大家的幫忙!祝平安喜樂。

看更多先前的討論...收起先前的討論...
雷伊 iT邦研究生 5 級 ‧ 2019-07-02 17:19:28 檢舉
舊版的欄位限制沒記錯是65535個,新版的Excel列與欄工作表上的總數量
1,048,576 列乘以 16,384 欄,所以您有換行嗎?
戴芄蘭 iT邦新手 5 級 ‧ 2019-07-02 17:23:02 檢舉
有的,我有換行。
我使用 Excel 2013,把函數填滿 A1:J1000000儲存格範圍。
雷伊 iT邦研究生 5 級 ‧ 2019-07-02 18:52:00 檢舉
2013版本是過渡期品,所以很多公司都是2010直接跳2016,建議你拿最新版本的Excel2019測試,效率應該會提升很多。另外硬體效能也很吃緊,一般爛電腦開個10Mb的Excel就掛了,更別提你跑了12個多小時沒結果。
戴芄蘭 iT邦新手 5 級 ‧ 2019-07-02 19:26:00 檢舉
目前手邊沒有新版 Excel,可以比對測試新舊版效率有無顯著提升?所以,有機會再試試了。
12個小時還好耶,我用C#加EPPLUS輸出XLSX,跑10000組號碼(1000列)都要花掉30秒,現在程式還在跑,有結果再貼上回文
froce iT邦大師 4 級 ‧ 2019-07-03 10:48:42 檢舉
C#速度這麼慢?10000000組預估500分鐘!?
這效率比用公式好不了多少,也太誇張,又不是python...
分成兩種狀況,可重覆(隨機抽樣),跟不可重覆(樂透)
4
小魚
iT邦大師 1 級 ‧ 2019-07-02 17:26:08
最佳解答

有時間限制嗎?
Excel我猜可能沒辦法太快,

先產生1~10,000,000的數字、亂數排序,
然後除以10,000,001

看更多先前的回應...收起先前的回應...
dragonH iT邦大師 1 級 ‧ 2019-07-02 17:27:20 檢舉

我比較好奇excel不會一直崩潰嗎/images/emoticon/emoticon37.gif

戴芄蘭 iT邦新手 5 級 ‧ 2019-07-02 17:35:48 檢舉

小魚 感謝回答。
您提到的作法是不是就像我目前用的方式?=IF(COUNTIF($A$1:$J$1000000,A1)>1,RANDBETWEEN(0,10000000)/10000000,A1)

我是有預期應該不會太快,畢竟運算速度還是得看硬體效能。

但擺著超過12小時實在難耐,所以想問問有沒有方法?或者使用VBA的方式?可以更有效率產生需要的結果。

戴芄蘭 iT邦新手 5 級 ‧ 2019-07-02 17:41:51 檢舉

https://ithelp.ithome.com.tw/upload/images/20190702/20118578y0zPYpIfZ5.jpg

dragonH Excel是還沒崩潰,但鼠標就是一直轉圈圈超過12小時,轉到都快吐了還沒個結果...

小魚 iT邦大師 1 級 ‧ 2019-07-02 17:47:29 檢舉

1千萬筆本來就不小,
可以想像時間也要花不少,
不過如果不是用Excel VBA,
你要怎麼處理 不重複的資料 呢?

小魚 iT邦大師 1 級 ‧ 2019-07-02 17:55:02 檢舉

之前有人問的問題
亂數不重複
基本上是差不多的概念,
只要記憶體不爆掉,
總是跑得完的.

戴芄蘭 iT邦新手 5 級 ‧ 2019-07-02 18:04:14 檢舉

小魚 我不懂 excel vba的 code要如何寫?才能達成這目的。

我現在是開啟 excel的反覆運算功能,用 COUNTIF函數檢查每個儲存格內 RANDBETWEEN函數產生的亂數,在 A1:J1000000範圍內有沒有重複?

再搭配 IF函數,若亂數有重複就重新產生亂數,若無則保留原本的數值。在每個儲存格如此反覆運算最多100次。

小魚 iT邦大師 1 級 ‧ 2019-07-02 18:25:13 檢舉

我做了一個範例,
不會很難研究看看吧,

Private Sub test()
    Dim mybag As New Collection ' 宣告一個放球的袋子,類型是Collection集合
    
    totalRow = 9 '列數
    totalNum = 72 '總數
    For i = 1 To totalNum
        mybag.Add (i / (totalNum + 1)) ' 依序放入1到49的號碼球
    Next
    
    For i = 1 To totalNum
        R = Int(mybag.count * Rnd()) + 1 ' R 就是包包裡的球數*0~1之間的隨機數,集合跟陣列一樣,元素從1開始
        Row = ((i - 1) Mod totalRow) + 1
        Column = Int((i - 1) / totalRow) + 1
        Cells(Row, Column).Value = mybag.Item(R) ' 按c值在相對的列上填入球號
        mybag.Remove (R) ' 抽到的球就不要放回袋子(集合)裡了,直接拿掉
    Next

End Sub

這是執行結果
https://ithelp.ithome.com.tw/upload/images/20190702/20105694IA3GozlUQB.png

1千萬筆你就自己慢慢跑,
不奉陪了
/images/emoticon/emoticon39.gif

小魚 iT邦大師 1 級 ‧ 2019-07-02 18:25:57 檢舉

就是因為檢查所以會花很多時間,
用抽撲克牌的方式就不需要檢查.

戴芄蘭 iT邦新手 5 級 ‧ 2019-07-02 18:38:53 檢舉

小魚好的,那我先關掉那個到現在還沒結果的工作進度了...
/images/emoticon/emoticon02.gif

我試試看要怎麼改寫這段 vba的內容,感謝。

小魚 iT邦大師 1 級 ‧ 2019-07-02 18:41:22 檢舉

其實你只要改寫兩個地方,
不過你還是先研究清楚邏輯吧,
另外Excel VBA也可以 下中斷點(F9) 跟 逐步執行(F8) 來Debug.

雷伊 iT邦研究生 5 級 ‧ 2019-07-02 18:52:51 檢舉

小魚的方式好聰明啊!學起來

戴芄蘭 iT邦新手 5 級 ‧ 2019-07-02 19:20:28 檢舉

小魚我依照您提供的範例,改寫編碼如後,請問這樣正確嗎?

Private Sub test()
    Dim mybag As New Collection ' 宣告一個放球的袋子,類型是Collection集合
    
    totalRow = 1000000 '列數
    totalNum = 10000000 '總數
    For i = 1 To totalNum
        mybag.Add (i / (totalNum + 1)) ' 依序放入(1到10000000的號碼球/號碼+1)
    Next
    
    For i = 1 To totalNum
        R = Int(mybag.count * Rnd()) + 1 ' R 就是包包裡的球數*0~1之間的隨機數,集合跟陣列一樣,元素從1開始
        Row = ((i - 1) Mod totalRow) + 1
        Column = Int((i - 1) / totalRow) + 1
        Cells(Row, Column).Value = mybag.Item(R) ' 按c值在相對的列上填入球號
        mybag.Remove (R) ' 抽到的球就不要放回袋子(集合)裡了,直接拿掉
    Next

End Sub

另外,請問您提到的中斷點(F9)和逐步執行(F8),該如何寫入在 Excel vba的編碼內?

小魚 iT邦大師 1 級 ‧ 2019-07-02 19:29:20 檢舉

看起來應該是吧,
主要是改totalRow和totalNum,
其他就會自己處理了.

中斷點(F9)和逐步執行(F8)不是寫在Code裡面的,
是你在找問題的一些技巧,
你可以上網查查應該會有一些資料,

戴芄蘭 iT邦新手 5 級 ‧ 2019-07-02 20:49:41 檢舉

小魚好的,感謝回答。我正在嘗試用 Excel vab執行前述代碼,省去反覆檢查的功夫應該可以比較快完成,再來看看多久可以跑完...

小魚用抽撲克牌方式好聰明,不稱讚一下也不成。

paicheng0111 iT邦研究生 1 級 ‧ 2019-07-03 09:04:03 檢舉
小魚 iT邦大師 1 級 ‧ 2019-07-03 09:46:52 檢舉

其實這類問題看多了,
很容易聯想到撲克牌...
只是Excel要跑1千萬筆還是很吃力...

戴芄蘭 iT邦新手 5 級 ‧ 2019-07-03 11:41:08 檢舉

就算用 Excel Vba來跑,
也需要極漫長的等待,真的非常非常吃力.../images/emoticon/emoticon02.gif

paicheng0111 iT邦研究生 1 級 ‧ 2019-07-03 18:36:56 檢舉

VBA其實不會更快,很有可能更慢,頂多平手。

0
海綿寶寶
iT邦超人 1 級 ‧ 2019-07-02 17:50:57

10,000,000 = 250 x 40,000
A..IP = 250 Column
1..40000 = 40000 Row

A1..IP40000
公式只要寫 =RAND() 就好

看更多先前的回應...收起先前的回應...
戴芄蘭 iT邦新手 5 級 ‧ 2019-07-02 18:10:47 檢舉

海綿寶寶 抱歉,我看不懂您回應的內容,可以請您淺白點具體說明作法嗎?感謝。

有點看懂,我的理解是這樣。
1~100,抽4個幸運兒。
1~25抽一位,
26~50、51~75、76~100,
也各抽一位。

這樣每個集合各抽一位,就不會重複。

戴芄蘭 iT邦新手 5 級 ‧ 2019-07-02 20:45:31 檢舉

來杯拿鐵從你的回應來看,海綿寶寶提供的方法好像有點像分層抽樣的概念,先依等比例把10,000,000個樣本分成若干組集合,然後每次在每一組各抽出一個樣本,這樣就不會重複了。

雖然我還是不知道要怎麼Excel vba來實現這種作法,哈哈。但我這樣去理解作法是對的嗎?

對,不過用公式就可以。
https://ithelp.ithome.com.tw/upload/images/20190703/20091910l1kNZSeUkI.jpg

2
froce
iT邦大師 4 級 ‧ 2019-07-03 02:28:02

你原本的寫法不太可能會不重複吧...
然後這個用excel做也太沒效率了,10000000次連要我用python寫(不用numpy/pandas)我覺得都會花很久。

golang,應該不用10分鐘吧:

package main

import (
	. "fmt"
	"math/rand"
	"os"
	"path"
	"path/filepath"
	"strconv"
	"time"

	"github.com/360EntSecGroup-Skylar/excelize"
)

func main() {
	ex, err := os.Executable()
	if err != nil {
		panic(err)
	}

	exPath := filepath.Dir(ex)
	book := path.Join(exPath, "Book1.xlsx")
	f := excelize.NewFile()
	f.SetActiveSheet(1)
	columns := [10]string{
		"A",
		"B",
		"C",
		"D",
		"E",
		"F",
		"G",
		"H",
		"I",
		"J"}

	rand.Seed(time.Now().UnixNano())
	p := rand.Perm(10000000)

	for i := 1; i <= 1000000; i++ {
		_p := p[i*10-10 : i*10]
		for index, elm := range columns {
			cell := elm + strconv.Itoa(i)
			value := float64(_p[index]) / float64(10000000)
			Println(cell, value)
			f.SetCellValue("Sheet1", cell, value)

		}
	}
	Println(book)
	err = f.SaveAs(book)
	if err != nil {
		Println(err)
	}

}

https://drive.google.com/file/d/1iPOh_HqrH3Ysu2NzqE_1fwOhsDScg-wu/view?usp=sharing
我是順便試跨平台編譯,在mac上編的,能不能跑我不知道。

看更多先前的回應...收起先前的回應...
戴芄蘭 iT邦新手 5 級 ‧ 2019-07-03 08:11:58 檢舉

froce感謝回答!

我在 Win7 64bit + Excel 2013 64Bit的系統平台上執行程式,自 CMD視窗開啟到結束,大約需時 18分鐘,之後會產生一個大小為 0KB的 BOOK1.xlsx,且檔案無法由 Excel開啟,錯誤訊息如圖。

https://ithelp.ithome.com.tw/upload/images/20190703/20118578DvxbwNmNR2.jpg

更改副檔名為 .xls後可開啟檔案。可惜內容為空白表格,否則這速度真是神快啊.../images/emoticon/emoticon02.gif

froce iT邦大師 4 級 ‧ 2019-07-03 08:32:03 檢舉

有更新執行檔了,試試看。
原本的在mac上可以跑,在windows下則會因為路徑關係造成問題。
然後我的office 2013 32bit可以跑。

戴芄蘭 iT邦新手 5 級 ‧ 2019-07-03 11:25:33 檢舉

報告 froce,我使用更新後的執行檔試了 2次,最後停留狀態如圖:

https://ithelp.ithome.com.tw/upload/images/20190703/20118578cRaSJWSyNw.jpg

不曉得是不是因為記憶體不足,所以導致問題?

目前用另一台實體記憶體 16GB,使用 Office 2016 64bit的電腦在試;也找一台電腦重新安裝 Office 2013 32bit版本,再試試看。

戴芄蘭 iT邦新手 5 級 ‧ 2019-07-03 11:36:28 檢舉

froce有結果了!
前面的情況應該是硬體效能不足導致。

使用 Win7 64 bit + Office 2016 64 bit的電腦跑出結果如圖:

https://ithelp.ithome.com.tw/upload/images/20190703/20118578syoVrQzjeU.jpg

真的是一千萬個亂數啊!/images/emoticon/emoticon07.gif
這是目前得到最有效率的做法了!
真是太感謝您了!

froce iT邦大師 4 級 ‧ 2019-07-03 11:49:13 檢舉

我的記憶體都12G以上,不過因為這樣寫會在記憶體裡先建個xslx,然後都寫在記憶體裡,的確蠻吃記憶體的,在我的機器(win7 x64)吃到4.7G,難怪原本編32 bit的會有問題。

真的不行的話就找時間幫你改寫CSV版的好了。

戴芄蘭 iT邦新手 5 級 ‧ 2019-07-03 14:16:43 檢舉

froce 若您空檔時間有餘裕,也願意幫忙,改寫成 CSV版能減輕執行運算的硬體需求,當然再好不過了,感謝勞力!

froce iT邦大師 4 級 ‧ 2019-07-03 18:10:38 檢舉
package main

import (
	"encoding/csv"
	. "fmt"
	"math/rand"
	"os"
	"path"
	"path/filepath"
	"strconv"
	"time"
)

func main() {
	start := time.Now().UnixNano()
	ex, err1 := os.Executable()
	if err1 != nil {
		panic(err1)
	}

	exPath := filepath.Dir(ex)
	sheet := path.Join(exPath, "Book1.csv")
	csvFile, err2 := os.Create(sheet)
	if err2 != nil {
		panic(err2)
	}
	defer csvFile.Close()

	csvWriter := csv.NewWriter(csvFile)

	rand.Seed(time.Now().UnixNano())
	p := rand.Perm(10000000)
	rp := [][]string{}

	for i := 0; i < 1000000; i++ {
		_p := make([]string, 10, 10)

		for index, elm := range p[i*10 : (i+1)*10] {
			value := strconv.FormatFloat(float64(elm)/float64(10000000), 'f', -1, 64)
			_p[index] = value
		}

		rp = append(rp, _p)

	}

	err3 := csvWriter.WriteAll(rp)
	if err3 != nil {
		panic(err3)
	} else {
		csvWriter.Flush()
	}
	end := time.Now().UnixNano()
	Println("花費時間:", float64(end-start)/float64(1000000000), "秒")
}

https://drive.google.com/file/d/1as9vj6j3d-T4qL4B__CoP5WzVmgof6Og/view?usp=sharing

CSV版記憶體佔用不到1G...

froce iT邦大師 4 級 ‧ 2019-07-03 18:42:05 檢舉

python:

import csv
import random as rn
import time

start = time.time()

_ = [str(i/10000000) for i in range(10000000)]
rn.shuffle(_)

with open("./test.csv", "w", encoding="utf8") as csvfile:
    writer = csv.writer(csvfile)

    for i in range(1000000):
        writer.writerow(_[i*10:(i+1)*10])

end = time.time()
print("花費時間:", end-start, "秒")

我錯怪python了...還蠻快的。Orz
不輸出中間結果(print)大概18秒,golang 4秒。
記憶體倒是都吃差不多,700MB左右。

戴芄蘭 iT邦新手 5 級 ‧ 2019-07-03 21:36:16 檢舉

froce 我滿懷感激地收下 randomToCSV.exe檔案,感謝大力相助!/images/emoticon/emoticon41.gif

froce iT邦大師 4 級 ‧ 2019-07-04 08:18:35 檢舉

你是運氣好剛好遇到我最近想好好的來學GO,要不然我也懶得寫。XD

然後寫了python版才覺得python真的很好用,除了慢和沒有編譯後執行檔以外,寫code的限制少很多,開發更是快到不行。python版的我開始寫不到10分鐘就寫完了。
GO得一直轉型,限制也多,重點是沒有map、filter這類的語意式函式,讓歷遍只能寫迴圈去做。
不過GO的好處就是跨平台編譯簡單、不用顧lib的相依性、執行速度快、還有還沒學到的非同步編程。

1
japhenchen
iT邦新手 2 級 ‧ 2019-07-03 13:12:03

我用 C# 跑,用EPPLUS產生EXCEL檔
1、不考慮亂數重覆的前題,以下程式產生不到2分鐘
(i5-3470處理器、8GB DDR3,SSD、Windows10)

using OfficeOpenXml; //EPPLUS
using System;
using System.Diagnostics;
using System.IO;

namespace bigRandom
{
    class Program
    {
        static void Main(string[] args)
        {
            if (File.Exists("export.xlsx")) File.Delete("export.xlsx");
            ExcelPackage excel = new ExcelPackage(new System.IO.FileInfo("export.xlsx"));
            ExcelWorksheet ews = excel.Workbook.Worksheets.Add("輸出結果");

            int QRow = 1;
            int QCol = 1;
            int xx = 1;
            for(int i = 0;i<10000000;i++)
            {
                Random Rng = new Random(Guid.NewGuid().GetHashCode());
                int x = Rng.Next(0, 1000000000); //產生亂數
                ews.Cells[QRow, QCol].Value = (double)x / 1000000000;
                QCol++; //從A跑到J
                if (QCol > 10) //如果下一行超過 J
                {
                    QCol = 1;//回到A
                    QRow++;  //換行
                }
            }
            excel.Save();
            if (File.Exists("export.xlsx")) Process.Start("export.xlsx");
        }
    }
}

https://ithelp.ithome.com.tw/upload/images/20190703/20117954pFERI9woKl.jpg
2、考慮不可重覆.................
我用LINUX + PYTHON + LIST及C# + LIST作抽球實驗,效能真的都很差,每跑10000組隨機抽球就需時1分鐘以上,跑完1000萬組,一天一定跑不掉

看更多先前的回應...收起先前的回應...

如果有興趣實驗效能的話,還是留下程式給各位玩玩,多了一個LIST,此例效能實在不好,執行完需時3+小時

using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.IO;

namespace bigRandom
{
    class Program
    {
        static void Main(string[] args)
        {
            if (File.Exists("export.xlsx")) File.Delete("export.xlsx");
            ExcelPackage excel = new ExcelPackage(new System.IO.FileInfo("export.xlsx"));
            ExcelWorksheet ews = excel.Workbook.Worksheets.Add("輸出結果");

            List<int> ballbag = new List<int>();// 球袋
            for (int b = 1; b < 10000000; b++)
                ballbag.Add(b); //產生1000萬個號碼球並放入球袋裡

            int QRow = 1;
            int QCol = 1;
            while (ballbag.Count > 0) // 一直跑到球袋抽空為止
            {
                Random Rng = new Random(Guid.NewGuid().GetHashCode());
                int x = Rng.Next(0,ballbag.Count); //從袋中取出號碼球
                ews.Cells[QRow, QCol].Value = (double)ballbag[x] / 100000000; // 抽出的球所代表的數值除以1000萬01
                ballbag.RemoveAt(x);
                QCol++; //從A跑到J
                if (QCol > 10) //如果下一行超過J,則換行
                {
                    QCol = 1; //
                    QRow++;
                }
            }
            excel.Save();
            if (File.Exists("export.xlsx")) Process.Start("export.xlsx");
        }
    }
}

froce iT邦大師 4 級 ‧ 2019-07-03 14:45:24 檢舉

python慢是一定的,只是沒想到C#也要弄到3小時...
不過python用了numpy和pandas的話大概不一定會很久,因為底層是C。

都是慢在LIST的REMOVE操作上......

froce iT邦大師 4 級 ‧ 2019-07-04 11:33:43 檢舉

C#沒有直接對list做shuffle的函式嗎?

有耶,但不知道效能是不是一樣糟而已

private static Random rng = new Random();  

public static void Shuffle<T>(this IList<T> list)  
{  
    int n = list.Count;  
    while (n > 1) {  
        n--;  
        int k = rng.Next(n + 1);  
        T value = list[k];  
        list[k] = list[n];  
        list[n] = value;  
    }  
}

//用法:

List<Product> products = GetProducts();
products.Shuffle();
0
浩瀚星空
iT邦大師 1 級 ‧ 2019-07-03 16:35:55

我自已用php來寫。因為有卡到記憶體用量問題。(說真的,一千萬筆就算只有一個字元,也需要暫用到10m的用量。但字元數一組最多也需要約8個字元。少說要用到100mb的用量左右。

1.所以我用如下的先各自生成100萬筆的數據10組。每組的數值範圍不同。
如第一組是1~100萬。第二組是100萬零1起~200萬。每組產生的值先各自存到不同的文件上。共10個文件。

2.再產生資料前會先亂數處理後再儲存。

其實我的想法跟分層抽樣的做法也是一樣的。如果不是因為「Allowed memory size」的問題
其實是能直接用如下的方式就可以處理了

for ($i = 1; $i <= 10000000; $i++) {
          $a[] = $i;
    }
shuffle($a);

運行不需要1秒。我有試著做寫檔約在10秒有右。
不過這樣做很消耗記憶體。我是將變數容量一直開到2G才可以跑的。
我後續試著寫成CSV的資料。是可以在10秒內完成寫檔。

我後續有試著改成不調整記憶容量的做法。用50萬一組做一個分層寫入20個檔案。
感覺反而變快了。運行時間約5秒就可以完成。

只是在試著各自20個文件讀取後再插入CSV檔。又會報「Allowed memory size」的錯誤。
所以作罷

我要發表回答

立即登入回答