Board logo

標題: Excel vloopup 問題 [打印本頁]
作者: edenho    時間: 2023-11-4 00:13     標題: Excel vloopup 問題

有六十萬列 data, 行 vlookup 行到 hang 機 ，請問有冇好方法
作者: javacomhk    時間: 2023-11-4 00:52

呢個係你部機唔夠RAM，SSD 唔夠大唔夠快嘅問題
作者: s20012797    時間: 2023-11-4 01:11

本帖最後由 s20012797 於 2023-11-4 09:03 編輯

Excel的VLOOKUP函數在處理大量資料時可能會導致效能問題，特別是當你有60萬列資料時。 一些可能的解決方案，包括使Python腳本，將自動計算更改為手動，或使用雙重VLOOKUP（需要先排序）。 這些都是可行的方法，但是它們可能需要一些技術知識和時間來實施。(https://github.com/MiCigo/pandas ... ic-train/basic-4.py)

另外，你也可以考慮以下方法來提升效能：

使用INDEX和MATCH函數組合：這種方法通常比VLOOKUP更快，更靈活。 MATCH函數可以找到尋找值在陣列中的位置，然後INDEX函數可以使用這個位置來傳回對應的值。

使用Excel的資料模型：如果你的Excel版本支持，你可以使用Power Pivot加入資料模型。 數據模型允許你在記憶體中載入數據，這樣可以大大提高處理大量數據的速度。

優化你的數據：確保你的數據盡可能簡潔清晰。 刪除不必要的列，避免使用複雜的公式，盡可能使用數值而不是文字。

將資料分割成更小的區塊：如果可能，你可以嘗試將你的資料分割成幾個較小的部分，然後在每個部分上分別執行VLOOKUP。
作者: bongbong3481    時間: 2023-11-4 06:51

本帖最後由 bongbong3481 於 2023-11-4 07:28 編輯

回覆 1# edenho

1. 將自動autocalculation 轉左manual 先
2. 用double-vlookup （佢既方法要先sortting ，再用兩個vlookup）
你google 下double-vlookup

我可以保證，以上方法有坐火箭既感覺，快到你吾信。
作者: eddy    時間: 2023-11-4 20:07

回覆  edenho

1. 將自動autocalculation 轉左manual 先
2. 用double-vlookup （佢既方法要先sortting ， ...
bongbong3481 發表於 2023-11-4 06:51

試過用double vlookup 一輪，但由於每次update DB table 後都要重新sorting ，我個case 大約每日都會有update ,十幾萬條data , 所以都幾繁，最後都係轉返index match 算數
作者: bongbong3481    時間: 2023-11-4 20:58

回覆 5# eddy


我印象中index match 好似吾是快好多，好似是快少少，另外應該沒double vlokup 咁快。

如果只是sorting問題 ，我覺得可以用vba sorting解決。
作者: jimking332    時間: 2023-11-4 22:22

試過用double vlookup 一輪，但由於每次update DB table 後都要重新sorting ，我個case 大約每日都會有up ...
eddy 發表於 2023-11-4 20:07


十幾萬條data根本唔意該放係Excel度, 放係Access度運作起黎已經爽好多.
作者: eddy    時間: 2023-11-6 09:25

回覆  eddy


我印象中index match 好似吾是快好多，好似是快少少，另外應該沒double vlokup 咁快。

如果 ...
bongbong3481 發表於 2023-11-4 20:58

實測index match 係有感地快過vlookup, 而sorting double vlookup 就差唔多

不過index match 唔洗每人做sorting 先用得, 比double vlookup 少一個step
作者: eddy    時間: 2023-11-6 09:30

十幾萬條data根本唔意該放係Excel度, 放係Access度運作起黎已經爽好多.
jimking332 發表於 2023-11-4 22:22

本身都係俾連excel formula 都未識嘅人用，而且公司機無access ….
作者: s20012797    時間: 2023-11-6 10:06

本身都係俾連excel formula 都未識嘅人用，而且公司機無access ….
eddy 發表於 2023/11/6 09:30


excel 下一版都吾知仲有冇得VBA,你同我講要記formula
作者: bongbong3481    時間: 2023-11-6 12:07

回覆 10# s20012797


我覺得vba 好難被淘汰，好多銀行證券行或其它行業都用緊，積累左N 年，如果要轉，工程極大。
作者: bongbong3481    時間: 2023-11-6 12:21

回覆 8# eddy

請問你用既數據量有幾多？ 你的結果同我的認知有少少出入，估吾到index match 快過double vlookup
作者: jimking332    時間: 2023-11-6 13:04

回覆  s20012797


我覺得vba 好難被淘汰，好多銀行證券行或其它行業都用緊，積累左N 年，如果要轉，工程 ...
bongbong3481 發表於 2023-11-6 12:07


條友唔識係度亂吹, 唔洗咁認真.
作者: eddy    時間: 2023-11-6 14:29

回覆  eddy

請問你用既數據量有幾多？ 你的結果同我的認知有少少出入，估吾到index match 快過double vlo ...
bongbong3481 發表於 2023-11-6 12:21

Index match 同double vlookup 差唔多，只係後者要先做一次sorting 就麻煩少少
作者: eddy    時間: 2023-11-6 15:14

excel 下一版都吾知仲有冇得VBA,你同我講要記formula
s20012797 發表於 2023-11-6 10:06

未必咁多公司跟得咁貼用新版office , 而excel formula 係excel 一個重要核心，唔預期會消失吧…..
作者: s20012797    時間: 2023-11-7 09:15

回覆  s20012797

我覺得vba 好難被淘汰，好多銀行證券行或其它行業都用緊，積累左N 年，如果要轉，工程 ...
bongbong3481 發表於 2023/11/6 12:07
未必咁多公司跟得咁貼用新版office , 而excel formula 係excel 一個重要核心，唔預期會消失吧….. ...
eddy 發表於 2023/11/6 15:14


無聊寫兩個code玩下


python

  1. from math import sin, acos, degrees, tan, pi
  2. from datetime import datetime, timedelta

  4. # Define a class to calculate sunrise and sunset times
  5. class SunriseSunsetCalculator:
  6.     # Earth's tilt angle
  7.     EARTH_TILT = 23.44
  8.     # Conversion factor from degrees to radians
  9.     TO_RADIANS = pi / 180

  11.     # Method to calculate solar declination
  12.     def calculate_declination(self, day_of_year):
  13.         return self.EARTH_TILT * sin(self.TO_RADIANS * ((360 / 365.0) * (day_of_year - 81)))

  15.     # Method to calculate hour angle
  16.     def calculate_hour_angle(self, latitude, declination):
  17.         return degrees(acos(-tan(latitude * self.TO_RADIANS) * tan(declination * self.TO_RADIANS)))

  19.     # Method to calculate sunrise and sunset times
  20.     def calculate(self, latitude, longitude):
  21.         # Check if latitude and longitude are within valid range
  22.         if not (-90 <= latitude <= 90) or not (-180 <= longitude <= 180):
  23.             raise ValueError("Invalid latitude or longitude value.")

  25.         # Get the current time
  26.         now = datetime.now()
  27.         # Get the current day of the year
  28.         day_of_year = (now - datetime(now.year, 1, 1)).days + 1

  30.         # Approximate calculation of solar declination
  31.         declination = self.calculate_declination(day_of_year)

  33.         # Calculate the hour angle
  34.         hour_angle = self.calculate_hour_angle(latitude, declination)

  36.         # Calculate sunrise and sunset times
  37.         solar_noon = datetime.combine(now.date(), datetime.min.time()) + timedelta(hours=12)
  38.         sunrise = solar_noon - timedelta(minutes=8 * hour_angle)
  39.         sunset = solar_noon + timedelta(minutes=8 * hour_angle)

  41.         return sunrise, sunset


  44. # Define a class to calculate the length of day and night in the Edo period
  45. class EdoPeriodClock:
  46.     # A day and a night in the Edo period each have 6 hours
  47.     HOURS_IN_EDO_DAY = 6
  48.     HOURS_IN_EDO_NIGHT = 6

  50.     # Method to calculate the length of day and night in the Edo period
  51.     def calculate(self, latitude, longitude):
  52.         # Calculate sunrise and sunset times
  53.         calculator = SunriseSunsetCalculator()
  54.         sunrise, sunset = calculator.calculate(latitude, longitude)

  56.         # Calculate the length of day and night
  57.         day_length = (sunset - sunrise).total_seconds() / 3600
  58.         night_length = 24 - day_length

  60.         # Calculate the length of day and night in the Edo period
  61.         edo_day_hour_length = day_length / self.HOURS_IN_EDO_DAY
  62.         edo_night_hour_length = night_length / self.HOURS_IN_EDO_NIGHT

  64.         return edo_day_hour_length, edo_night_hour_length


  67. # Example usage
  68. latitude = 37.7749  # Latitude of a location
  69. longitude = -122.4194  # Longitude of a location

  71. edo_clock = EdoPeriodClock()
  72. edo_day_hour_length, edo_night_hour_length = edo_clock.calculate(latitude, longitude)

  74. print(f"Length of Edo day hour: {edo_day_hour_length} hours")
  75. print(f"Length of Edo night hour: {edo_night_hour_length} hours")
複製代碼



VBA
  1. Option Explicit

  3. ' Define a class to calculate sunrise and sunset times
  4. Class SunriseSunsetCalculator
  5.     ' Earth's tilt angle
  6.     Private Const EARTH_TILT As Double = 23.44
  7.     ' Conversion factor from degrees to radians
  8.     Private Const TO_RADIANS As Double = 3.14159265358979 / 180

  10.     ' Method to calculate solar declination
  11.     Private Function CalculateDeclination(day_of_year As Integer) As Double
  12.         CalculateDeclination = EARTH_TILT * Sin(TO_RADIANS * ((360 / 365.0) * (day_of_year - 81)))
  13.     End Function

  15.     ' Method to calculate hour angle
  16.     Private Function CalculateHourAngle(latitude As Double, declination As Double) As Double
  17.         CalculateHourAngle = WorksheetFunction.Degrees(Acos(-Tan(latitude * TO_RADIANS) * Tan(declination * TO_RADIANS)))
  18.     End Function

  20.     ' Method to calculate sunrise and sunset times
  21.     Public Function Calculate(latitude As Double, longitude As Double) As Variant
  22.         ' Check if latitude and longitude are within valid range
  23.         If latitude < -90 Or latitude > 90 Or longitude < -180 Or longitude > 180 Then
  24.             Err.Raise Number:=5, Description:="Invalid latitude or longitude value."
  25.             Exit Function
  26.         End If

  28.         ' Get the current time
  29.         Dim now As Date
  30.         now = Now()
  31.         ' Get the current day of the year
  32.         Dim day_of_year As Integer
  33.         day_of_year = DateDiff("d", DateSerial(Year(now), 1, 0), now)

  35.         ' Approximate calculation of solar declination
  36.         Dim declination As Double
  37.         declination = CalculateDeclination(day_of_year)

  39.         ' Calculate the hour angle
  40.         Dim hour_angle As Double
  41.         On Error GoTo ErrorHandler
  42.         hour_angle = CalculateHourAngle(latitude, declination)
  43.         On Error GoTo 0

  45.         ' Calculate sunrise and sunset times
  46.         Dim solar_noon As Date
  47.         solar_noon = Date + TimeSerial(12, 0, 0)
  48.         Dim sunrise As Date
  49.         sunrise = solar_noon - TimeSerial(8 * hour_angle / 60, 0, 0)
  50.         Dim sunset As Date
  51.         sunset = solar_noon + TimeSerial(8 * hour_angle / 60, 0, 0)

  53.         Calculate = Array(sunrise, sunset)
  54.         Exit Function

  56. ErrorHandler:
  57.         Err.Raise Number:=Err.Number, Description:="Error calculating hour angle. " & Err.Description
  58.     End Function
  59. End Class

  61. ' Define a class to calculate the length of day and night in the Edo period
  62. Class EdoPeriodClock
  63.     ' A day and a night in the Edo period each have 6 hours
  64.     Private Const HOURS_IN_EDO_DAY As Integer = 6
  65.     Private Const HOURS_IN_EDO_NIGHT As Integer = 6

  67.     ' Method to calculate the length of day and night in the Edo period
  68.     Public Function Calculate(latitude As Double, longitude As Double) As Variant
  69.         ' Calculate sunrise and sunset times
  70.         Dim calculator As New SunriseSunsetCalculator
  71.         Dim sunrise_sunset As Variant
  72.         sunrise_sunset = calculator.Calculate(latitude, longitude)
  73.         Dim sunrise As Date
  74.         sunrise = sunrise_sunset(0)
  75.         Dim sunset As Date
  76.         sunset = sunrise_sunset(1)

  78.         ' Calculate the length of day and night
  79.         Dim day_length As Double
  80.         day_length = (sunset - sunrise) * 24
  81.         Dim night_length As Double
  82.         night_length = 24 - day_length

  84.         ' Calculate the length of day and night in the Edo period
  85.         Dim edo_day_hour_length As Double
  86.         edo_day_hour_length = day_length / HOURS_IN_EDO_DAY
  87.         Dim edo_night_hour_length As Double
  88.         edo_night_hour_length = night_length / HOURS_IN_EDO_NIGHT

  90.         Calculate = Array(edo_day_hour_length, edo_night_hour_length)
  91.     End Function
  92. End Class
複製代碼



純睇code&寫code而言,至少我吾覺得vba"仲適合學生(或初學)去學",至於"舊腳本"問題,買個Ai來重寫&捉蟲花吾上多少$,咁都比吾起學咩人做生意,就係咁
作者: 波風水門    時間: 2023-11-7 11:45

提示: 作者被禁止或刪除 內容自動屏蔽
作者: s20012797    時間: 2023-11-7 12:09

有 excel 的 office，不是包埋 access ?

via HKEPC IR 5.1.14 - Android(5.1.2F)
波風水門 發表於 2023/11/7 11:45


冇記錯最平個Set得word,exceI,同PPT,
BTW,2023仍為非M$不可的,
系咪M$的office待別好打x機wwW
作者: dominicyu    時間: 2023-11-7 13:55

用Power Query
作者: platinum    時間: 2023-11-8 08:58

舊po 講double vlookup, 同你個case 差唔多
https://www.hkepc.com/forum/view ... =vlookup&page=1
作者: 波風水門    時間: 2023-11-8 22:27

提示: 作者被禁止或刪除 內容自動屏蔽
作者: bongbong3481    時間: 2023-11-9 06:55

回覆 21# 波風水門

我都以為是，不過從來吾買office，用免費下載版
作者: 波風水門    時間: 2023-11-9 10:23

提示: 作者被禁止或刪除 內容自動屏蔽
作者: bongbong3481    時間: 2023-11-9 19:08

回覆 23# 波風水門

我用舊版，新版就沒試過。

如果家用，吾會買office 了，用代用。
作者: jimking332    時間: 2023-11-9 22:13

以前就得姐, 而家仲得?
波風水門 發表於 2023-11-9 10:23


商用梗係唔得, 但個人用點解唔得?




歡迎光臨 電腦領域 HKEPC Hardware (https://h2.hkepc.com/forum/) Powered by Discuz! 7.2