標題: 求 比excel 更好的software [打印本頁]

---

作者: Turboaftershave    時間: 2015-5-14 19:49     標題: 求 比excel 更好的software

本帖最後由 Turboaftershave 於 2015-5-14 22:14 編輯

raw data 有 1.5M 個cells (6個column x 250000rows)

要做到 :
1) 按條件分類(年月份，排名，組別)
2) 細分後再整合再分析 (出chart)
3) 要全自動化 (dump 完 rawdata 自動出result)

now 用excel 要狂按update，計3小時先完 好無效率

想問有無代替品?

---

作者: TimmyL    時間: 2015-5-14 20:59

Just use another faster computer can solve yr problem.

---

作者: big_nature    時間: 2015-5-14 21:57

本帖最後由 big_nature 於 2015-5-14 21:58 編輯

用64bitExcel會否好D!?
加上用快速D電腦!

---

作者: Turboaftershave    時間: 2015-5-14 22:01

本帖最後由 Turboaftershave 於 2015-5-14 22:02 編輯


應該吾關事， 已經 i5-4440 了
excel 一到4核已經極限了，看過評測 再上i7 都系快得dd  (的確2核上4核很明顯用少左時間)
ssd 用緊 只能夠加快開worksheet 速度(差幾秒only)  無助加速運算

一定有d program 會好過excel (raw data cxells超過1千萬的統計 大有人在 但excel應該 handle吾到)

---

作者: Turboaftershave    時間: 2015-5-14 22:11



你指 window 64bit 嗎? 已經用緊

我估應該系excel 本來就吾系design for 大型統計分析
raw data 去到某個數應該 要用另一d software

因為吾可能次次用人手做filtering(而且人手會有機會出錯)，所以set灑 formula 自己run

---

作者: big_nature    時間: 2015-5-14 22:19



我意思是office 64 bit!
睇下有冇幫助!

---

作者: Turboaftershave    時間: 2015-5-14 22:29

本帖最後由 Turboaftershave 於 2015-5-14 22:33 編輯

回覆 6# big_nature

尼度 好似無提及會快左
https://technet.microsoft.com/en-us/library/ee681792.aspx

check 左我系32bit


圖片附件: 新點陣圖影像.jpg (2015-5-14 22:33, 68.66 KB) / 下載次數 122
https://h2.hkepc.com/forum/attachment.php?aid=1790875&k=5968e32f373c293ab3464a1e3cf558a1&t=1781949424&sid=28jExkyVmJ

---

作者: big_nature    時間: 2015-5-14 22:39

以前有聽過話64bit excel適合大量運算!
師兄條運算咁大量cell!
可以比較下64 vs 32的差異!

---

作者: Turboaftershave    時間: 2015-5-14 23:51

回覆 8# big_nature


無閒錢去做尼d實驗
你見我仲用緊學生版就知

---

作者: TimmyL    時間: 2015-5-15 01:00

or you may use the 2016 preview ver. (Trial Ver), meanwhile, i7 4770K - 4 cores, 8 threads which may help you .

---

作者: 狂蜂一號    時間: 2015-5-15 01:44

回覆 1# Turboaftershave

乜 Excel 咁差咩？
照計 1,500,000 個 Cell 唔算多喎！

---

作者: Turboaftershave    時間: 2015-5-15 07:39

本帖最後由 Turboaftershave 於 2015-5-15 22:02 編輯


主要系d formula
其中尼條常用(按條件抽取) 要好多時間計
{=(INDEX(D$1:D$70000,SMALL(IF($O$10000:$O$70000<>"正確","",ROW($O$10000:$O$70000)),ROW(D1)),)}

有無高手幫我簡化一下

---

作者: nissin    時間: 2015-5-15 07:50



Access + Excel + VBA

---

作者: Turboaftershave    時間: 2015-5-15 10:00

本帖最後由 Turboaftershave 於 2015-5-15 10:32 編輯

回覆 13# nissin

VBA 聽過，但吾似formula 甘 user-friendly

請問是否可以用 record 的方法 記綠我d step ?
我吾識寫VBA

BTW 請問 ACCESS 對 excel 會有哪種好處?

---

作者: happy_gemini    時間: 2015-5-15 12:16



可否清楚說明一下你本身想做什麼動作?
就這樣看Formula, 改完(簡化)都未必符合你原本意思.

---

作者: Turboaftershave    時間: 2015-5-15 21:10

本帖最後由 Turboaftershave 於 2015-5-15 22:05 編輯

如圖
左邊 A-G column 系 raw data
右邊L-W 系 if cause (月份條件)
e.g. : L3=IF(AND(H3>=DATE($H$2,1,1),H3<=DATE($H$2,1,31)),"正確",IF(H3>=DATE($H$2,2,1),"多",IF(H3<=DATE($I$2,12,31),"少")))

最後靠下面尼條公式 成功按月份分配A-G column 相應 data 到 每個月的worksheet內
{=(INDEX(D$1:D$70000,SMALL(IF($O$10000:$O$70000<>"正確","",ROW($O$10000:$O$70000)),ROW(D1)),)}

總站完成篩選後，再分配到15-18個分站 進行進一步篩選後分析，然後匯入一個大summary 表內


情形好似 :
國泰要統計每年 marco polo 會員 消費pattern
1) 按Tier Diamond金銀Green 每Group消費  Top spender ， demographics...
2) 飛行total miles, 常去地方
3) 買機票 peak season ，年內counter check-in 最高峰日子&時段 ，選擇check-in 時間，boarding時間，late show 人數...
4) Complaint 人數 & reason classification
5) Asian mile 消費種類
6) Inflight DFS 消費
7) Special event spending
8) CX Lounge 的 frequent user
9) First Class 享用 special meal 人數 ...

---

作者: Turboaftershave    時間: 2015-5-15 21:45

本帖最後由 Turboaftershave 於 2015-5-15 22:02 編輯

另外發覺有d公式 去到一定數量時 effeciency 強差人意

1) 抽取unique data(包含重複) (網上成日都只系搵到尼個方法)
{=IFERROR(INDEX($A$2:$A$14, MATCH(0,COUNTIF($Q$1:Q1, $A$2:$A$14), 0)),"")}
去到10000rows後已經好浪費時間 座底10mins以上

後來備我搵到另一個快好多的方法做
先幫我單純數重複次數
2) =COUNTIF($A$1:$A1,A1)
之後指示返所有 unique data
3) {=(INDEX(D$1:D$70000,SMALL(IF($O$10000:$O$70000<>"正確","",ROW($O$10000:$O$70000)),ROW(D1)),)}

不過2號公式去到90000rows時  excel已prompt我資源不足無法計算
要我逗個大圈 先可以過尼個bottleneck  但運算時間用多左幾倍

有無高手 可以教下我vba 啊

---

作者: patpatmok    時間: 2015-5-15 22:34

似用MYSQL 比較適合

---

作者: nissin    時間: 2015-5-15 22:36

本帖最後由 nissin 於 2015-5-15 22:48 編輯


如果formula太長太複雜
VBA隨時易過佢

Access可以將資料整合分析處理
VBA做資料交流既橋樑
Excel當做Interface出Graph用

只要識得寫VBA, 唔需要裝Access都可以經ADO & ADOX去建立同運行Access資料庫
https://msdn.microsoft.com/en-us ... 75532(v=vs.85).aspx

---

作者: Turboaftershave    時間: 2015-5-15 23:06

本帖最後由 Turboaftershave 於 2015-5-15 23:16 編輯

回覆 19# nissin

thx
請問有無VBA 教授網址
估你吾到 最後excel 只系淪為出graph的田地

我都未完全明灑 excel d formula(太多combinations) 只系網上/youtube 好多人一早寫好 對應某d用途
我就從中搵返d logic try & error 小規模test後證明work  就開始用  

某d位 人手做 5分鐘就搞點，但要自動化就要30分鐘
(無奈workload重 我連個5分鐘都吾想費神)

now 最urgent想要excel 內 advanced filter 尼個工能 自動化
如果vba 寫到 希望可以縮短1半運算時間

---

作者: Turboaftershave    時間: 2015-5-15 23:49

本帖最後由 Turboaftershave 於 2015-5-16 00:07 編輯

試試下 發現
原來 record marco 後
可以set button 一按即用 (即時抽取 A行 unique data 去 G行)
請問可以set 做一打開 file 就自動完成嗎?


圖片附件: 新點陣圖影像.jpg (2015-5-15 23:49, 52.5 KB) / 下載次數 86
https://h2.hkepc.com/forum/attachment.php?aid=1791181&k=b6b6f5c9ae4a4596f5fd322f251c4660&t=1781949424&sid=28jExkyVmJ

---

作者: 狂蜂一號    時間: 2015-5-16 00:25

回覆 21# Turboaftershave

Good start!

可以是可以，不過你這個恐怕並不適合。

---

作者: Turboaftershave    時間: 2015-5-16 00:32

本帖最後由 Turboaftershave 於 2015-5-16 00:43 編輯

回覆 22# 狂蜂一號

thx
的確youtube有人教左 可以open file autorun 灑所有marco
試左 彈message box + auto filter 系work的
看來VB裏頭都系 excel d 公式 不過寫法有d另類

請問吾合適的原因何在?
我唸近只要一次過record 灑 所有 steps 甘米ok 灑?
最多幾十個modules 一次過run (會晤會到頭來都系好浪費時間計?)


圖片附件: 新點陣圖影像.jpg (2015-5-16 00:31, 123.09 KB) / 下載次數 96
https://h2.hkepc.com/forum/attachment.php?aid=1791193&k=b9f94f501c90fc810ec5ff4bdd5d093e&t=1781949424&sid=28jExkyVmJ

---

作者: 狂蜂一號    時間: 2015-5-16 00:46

回覆 23# Turboaftershave

有啲嘢晤需要，或者晤想每次一開 Workbook 就 run 呀！

---

作者: nissin    時間: 2015-5-16 00:52

本帖最後由 nissin 於 2015-5-26 02:09 編輯


可以咁樣loop晒需要既d資料入database

---

作者: nissin    時間: 2015-5-16 00:56

講明先, 上圖係 WinXP x86用的, Win7 x64係行唔到架
照抄係唔會work的

---

作者: yorkchoi    時間: 2015-5-16 01:13

本帖最後由 yorkchoi 於 2015-5-16 01:16 編輯

呢d野我覺得交俾DB做會好d, 用excel嘅advanced pivot 抽返d野出來，純粹直覺，未必啱...

---

作者: Turboaftershave    時間: 2015-5-16 09:57

本帖最後由 Turboaftershave 於 2015-5-16 09:59 編輯

回覆 24# 狂蜂一號

應該吾難解決
要autorun的module 就全部用workbook open 來run

定期先update 的就 :
1) set button 自己click
2) 開獨立workbook 來存放

---

作者: 狂蜂一號    時間: 2015-5-16 21:47

回覆 28# Turboaftershave

每次開 Workbook 都重新 run 一次 (重新計算一次)？

你是怎樣輸入資料的？新加入?修改?

---

作者: Turboaftershave    時間: 2015-5-17 00:09

本帖最後由 Turboaftershave 於 2015-5-17 00:25 編輯


yes ar
一開workbook
名為 module 1 同 module 2 就自動run 幾多個都得

Private Sub Workbook_Open()
module 1
module 2
End Sub

https://www.youtube.com/watch?v=1CpE_amSD6o

真系好快
原本靠formula要10mins 的計算 (3個 uniqueness workbook 合供400000rows)
now 10秒搞定 (當然原本excel 內設 的 function 系好efficient既)
且200000rows 一次過同一行remove duplicate 吾使再逗大圈
無左d formula file size 得反原本1/4
一舉幾得

---

作者: 狂蜂一號    時間: 2015-5-17 01:10

回覆 30# Turboaftershave

值得恭喜！

---

作者: Turboaftershave    時間: 2015-5-17 17:50

本帖最後由 Turboaftershave 於 2015-5-17 18:06 編輯


thx 但系當我嘗試 用VBA 做月份篩選時  做吾到

因為網上教的方法只可以match不重複的conditions (John , Mary, Tom...)
要怎樣修改修先可以match重複的單一condition? (John 出現1000次)
(e.g. :Data內一月份出現左1000rows，將對應1000rows的A-G cells 全部抄過去Sheet1)
https://www.youtube.com/watch?v=AzhQ5KiNybk

Sub transfer()

Dim i As Long, j As Long, lastrow1 As Long, lastrow2 As Long
Dim myname As String
lastrow1 = Sheets("Data").Range("L" & Rows.Count).End(xlUp).Row

Sheets("Data").Activate
For i = 3 To lastrow1
myname = Sheets("Data").Cells(i, "L").Value

Sheets("Sheet1").Activate
lastrow2 = Sheets("Sheet1").Range("H" & Rows.Count).End(xlUp).Row

For j = 3 To lastrow2

If Sheets("Sheet1").Cells(j, "H").Value = myname Then

Sheets("Data").Activate
Sheets("Data").Range(Cells(i, "A"), Cells(i, "G")).Copy

Sheets("Sheet1").Activate
Sheets("Sheet1").Range(Cells(j, "A"), Cells(j, "G")).Select

ActiveSheet.Paste
End If
Next j
Application.CutCopyMode = False
Next i
Sheets("Data").Activate
Sheets("Data").Range("A1").Select

End Sub

---

作者: 狂蜂一號    時間: 2015-5-17 21:55

回覆 32# Turboaftershave

搬 For loop j 入 For loop i, 留意 row number!
(或者 Sort 咗先?)
用 VBA 要好小心, check 清楚啲資料和計算, 要自己輸入嘅 raw data 要 keep 好!!!!!!!!!!
人哋嘅 code 要俾啲心機去了解, 繼續努力!

---

作者: tragamer    時間: 2015-5-17 22:49

重新出發係痛苦事
我覺得ACCESS比較適合, 雖然我唔係好熟.
"Table A" → "Queries A" → "Table B"
"Table A" → "Queries B" → "Table C"
"Table B, Table C" → "Queries C" → "Table D"
"Macro A" run "Queries A, Queries B, Queries C, 匯出excel"
只作參考

---

作者: Turboaftershave    時間: 2015-5-18 00:55

本帖最後由 Turboaftershave 於 2015-5-18 01:00 編輯


一來未買 Access (公司亦都無)
2來 都大至上分析過 Excel 同 Access 之間的分別

Data之間 獨立的 Excel都夠做
Data之間有大關聯性而會影響之後分析的就 Access

Access 比較偏重 Storage 和 出表格
但分析篩選 都系excel強
加上VBA 都系靠向excel用途

所以我會繼續尼個無止境 學習
Btw Access普及率遠遠比吾上Excel，所以要逼自己用common既software都做到先得

VBA 越看越強大 根本就系開發工具 (高手 應該 可以用VB 由0 重新 寫一個新的Excel出來)

---

作者: Turboaftershave    時間: 2015-5-18 01:07

本帖最後由 Turboaftershave 於 2015-5-18 01:11 編輯

回覆 33# 狂蜂一號

thx
已經用另一種方法搞定 (一樣快過以前好多 30秒k.o.以前1hr   飛機vs步行)
仲要cpu load 由長時100%跌倒 30-60左右 hea run
不過都系用Macro record好 再修改一下 方便d
VBA 太深奧，我尼個階段無法隨心所欲 (吾識寫)


Sub IF_Jun()
   
    Application.ScreenUpdating = False
    Sheets("Jun").Range("A3:G5500").ClearContents
    Sheets("Data").Select
   
    If Sheets("Data").Range("Q1").Value = 0 Then
    Sheets("Data").Range("A1").Select
   
    Else:
   
    Cells.Select
    Selection.AutoFilter
    ActiveSheet.Range("$A:$W").AutoFilter Field:=17, Criteria1:="正確"
    Range("A2:G35000").Select
    Selection.Copy
    Sheets("Jun").Select
    Range("A3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("G3:G5500").Select
    Selection.NumberFormat = "m/d/yyyy h:mm"
    Sheets("Data").Select
    Application.CutCopyMode = False
    Selection.AutoFilter
   
    End If
   
End Sub

---

作者: nissin    時間: 2015-5-18 01:49

本帖最後由 nissin 於 2015-5-26 02:09 編輯


一早講左經VBA用Access係唔駛買的，有人唔聽書
只要執行呢段code，就會開到個新既access資料庫


出黎個資料庫格式係咁


圖片附件: result.png (2015-5-18 01:47, 4.84 KB) / 下載次數 98
https://h2.hkepc.com/forum/attachment.php?aid=1791845&k=1077223447e34a94c68393833fed7510&t=1781949424&sid=28jExkyVmJ

---

作者: nissin    時間: 2015-5-18 02:25

本帖最後由 nissin 於 2015-5-26 02:09 編輯

下面呢段係示範將資料庫內 DataOfBirth 等於6月既資料拎出黎放落Excel 入面第2張工作表

---

作者: nissin    時間: 2015-5-18 03:04

本帖最後由 nissin 於 2015-5-26 02:11 編輯

送上懶人包一個俾你試用



圖片附件: demo.png (2015-5-18 03:00, 59.02 KB) / 下載次數 75
https://h2.hkepc.com/forum/attachment.php?aid=1791853&k=dfe264e3383bb53e1db6ec635479aae4&t=1781949424&sid=28jExkyVmJ

---

作者: Turboaftershave    時間: 2015-5-18 09:43

回覆 39# nissin


thx
返去試下先

---

作者: mo9394    時間: 2015-5-18 09:56

又話要SOFTWARE 又唔肯俾錢, 好矛盾.

你做的野交俾做BI / STATISTIC的 SOFTWARE 做好快, 唔特別俾錢下EXCEL 算係易搞喇

---

作者: 狂蜂一號    時間: 2015-5-18 22:28

回覆 36# Turboaftershave

That's good.

---

作者: Strike_Zero    時間: 2015-5-18 22:30

本帖最後由 Strike_Zero 於 2022-6-18 13:56 編輯

Del......

---

作者: nivlacckw    時間: 2015-5-18 23:08



64bit excel is much much faster than you expect. Just try it

---

作者: toylet    時間: 2015-5-19 01:14

提示: 作者被禁止或刪除 內容自動屏蔽

---

作者: toylet    時間: 2015-5-19 01:26

提示: 作者被禁止或刪除 內容自動屏蔽

---

作者: 凌天羽v    時間: 2015-5-19 09:53

回覆 16# Turboaftershave

你呢堆野根本係己經唔係試算表既SCOPE, INPUT 入SQL 快得多.

---

作者: Turboaftershave    時間: 2015-5-19 11:38



Bottleneck 系 software 度

---

作者: Turboaftershave    時間: 2015-5-19 11:40



我之前reply有mentioned 過，已經加左啦
Sub IF_Jun()
   
    Application.ScreenUpdating = False

Ram 系8GB

---

作者: Turboaftershave    時間: 2015-5-19 11:53



SQL易入手嗎?

anyway 經我不斷用vba改良後
依家 total update 時間  已由3hrs 縮短到25mins (吾使再OT)

不過有d位仍然要依賴formula計，希望有朝一日能全部用vba搞定

---

作者: Turboaftershave    時間: 2015-5-19 12:04




請問快幾多先?
快多一倍

BTW 我見youtube 有人post excel run vba 速度 (2010 vs 2013)
2013 居然慢好多  真系...

---

作者: mkm00235    時間: 2015-5-19 16:20



證明新野未必一定快, 只係多左FUNCTION

---

作者: nivlacckw    時間: 2015-5-19 18:00

本帖最後由 nivlacckw 於 2015-5-19 19:42 編輯



    舊公司同一部電腦，同一個excel vba file. 行office 32bit 要計3分鐘， 行office 64bit 大約5 秒

大部分公司唔會裝office 64bit,因為3rd party plugin 兼容性問題

---

作者: toylet    時間: 2015-5-19 20:05

提示: 作者被禁止或刪除 內容自動屏蔽

---

作者: Strike_Zero    時間: 2015-5-19 20:44

本帖最後由 Strike_Zero 於 2022-6-18 13:55 編輯

Del......

---

作者: Turboaftershave    時間: 2015-5-19 20:57

本帖最後由 Turboaftershave 於 2015-5-19 21:03 編輯


兩個ramdisk 神人系到毒我 逼我打爆32GB

不過我對能再快多少存疑
1) 我已經用緊SSD (只系feel到 開file 較公司 hdd 快幾秒only)
2) 我個folder total size 雖然300mb 但最大file 不過30mb  而且多數套取data 都系單向1個file 分開存取 (非一次過同一時間load灑300mb)
3) 運算應該全權由cpu負責 (公司 i5 760 hdd  vs 家 i5 4440 ssd  : 比較過單一運算時間，其本上系一樣敢仔)
4) load workbook 時吾系已經 入左ram la 咩?  仲ram disk

---

作者: toylet    時間: 2015-5-19 20:59

提示: 作者被禁止或刪除 內容自動屏蔽

---

作者: Turboaftershave    時間: 2015-5-19 21:11

本帖最後由 Turboaftershave 於 2015-5-19 21:15 編輯


甘真系好快
出年加人工的話 會試下 xd

其實 電腦part 只系佔我 duty 4成
但真系要提promotion 或加人工 就要靠餘下6成

所以25分鐘我已ok，吾會用$再縮時間 (錦上添花弄得再好，也及不過討得上司歡心來得重要 )

Do the Right Things vs Do the Things Right  (職場感概...)

---

作者: toylet    時間: 2015-5-19 21:52     標題: 【少少ＯＴ】

提示: 作者被禁止或刪除 內容自動屏蔽

---

作者: Turboaftershave    時間: 2015-5-19 22:58



其實我電腦個part系公司都過得去
就系做事差d 和有時看吾透"他們"想甚麼
有時單憑夠醒，真系攤開手板等升職
(我情願電腦白痴 但有他心通 )

不過尼個錦上添花 努力下 都有作為既  我會繼續

---

作者: nissin    時間: 2015-5-20 07:55

本帖最後由 nissin 於 2015-5-20 08:01 編輯


試成點呢
CreateDatabase - 建立一個新既資料庫, 呢個sample會將資料庫放係 C:\database.mdb
InsertRecord - 將資料加入去資料庫
ReadData - 讀取資料, 呢個sample會顯示所有DateOfBirth等於6月既資料

你可以一次過將所有資料入晒落個database度
到需要搵資料時用SQL俾返d條件佢做query即可
從此以後就一個file搞掂晒

---

作者: XexeX    時間: 2015-5-20 11:56

我想問一問，有冇開個Task Manager/ Resource Monitor 睇睇CPU Loading同Disk IO？

如果D Data係要Share，不如搵台機裝個SQL Express再用Excel Query返D資料會唔會方便D？

---

作者: Cle32    時間: 2015-5-20 12:01

有無用過 Excel 的 Pivot Table ?
應該都做到的

---

作者: tunster    時間: 2015-5-20 12:07

自己電腦唔夠力, 開個 microsoft account 上 Office Online 掟上 OneDrive 試下舊雲夠唔夠力

---

作者: toylet    時間: 2015-5-21 19:29

提示: 作者被禁止或刪除 內容自動屏蔽

---

作者: barrysbarry    時間: 2015-5-22 22:33

WPS office, 好在免費

---

作者: jackyy01    時間: 2015-5-23 11:46

計得黎都只夠做兩轉 ...

SQL..啦