Board logo

標題: 再請教excel問題 [打印本頁]
作者: sbg    時間: 2024-6-28 23:08     標題: 再請教excel問題

原始 Data好亂下
長度不一 首幾個喺Product ID打後就喺名稱

現想要統一番格式
總長度30
首六位喺數字唔夠要在前補0   
其餘喺產品名稱  尾補space

先行謝謝
excel-30length.png

圖片附件: excel-30length.png (2024-6-28 23:02, 44.66 KB) / 下載次數 140
https://h2.hkepc.com/forum/attachment.php?aid=2429465&k=e57652853a70bee00dd86cd06b803fb1&t=1781622090&sid=38c5GmSjla

作者: jimking332    時間: 2024-6-29 18:46

回覆 1# sbg

呢個應該要VBA先搞得掂
作者: sbg    時間: 2024-6-30 23:09

回覆  sbg

呢個應該要VBA先搞得掂
jimking332 發表於 2024-6-29 18:46



Thanks ching
咁樣有無辦法可以取下左邊的數值
由0至6位長度沒有特定的
作者: jimking332    時間: 2024-6-30 23:16

Thanks ching
咁樣有無辦法可以取下左邊的數值
由0至6位長度沒有特定的
sbg 發表於 2024-6-30 23:09


用vba咪得囉
作者: sbg    時間: 2024-6-30 23:18

用vba咪得囉
jimking332 發表於 2024-6-30 23:16


小弟才疏學淺唔曉
作者: lamsir    時間: 2024-7-1 15:10

本帖最後由 lamsir 於 2024-7-1 15:54 編輯

唔駛用 VBA，一條 Formula已搞掂
  1. =LEFT(TEXT(LEFT(A2,MIN(IF(ISNUMBER(--MID(A2,ROW(INDIRECT("1:6")),1)),999,ROW(INDIRECT("1:6"))))-1),"000000") & MID(A2,MIN(IF(ISNUMBER(--MID(A2,ROW(INDIRECT("1:6")),1)),999,ROW(INDIRECT("1:6")))),LEN(A2)) & REPT(" ", 30), 30)
複製代碼
這是Array Formula，輸入完記得要按Ctrl + Shift + Enter
作者: bongbong3481    時間: 2024-7-2 07:04

留名學野
作者: sbg    時間: 2024-7-2 10:48

唔駛用 VBA，一條 Formula已搞掂這是Array Formula，輸入完記得要按Ctrl + Shift + Enter ...
lamsir 發表於 2024-7-1 15:10



Very NICE
Thanks ching

想請教一下Ctrl + Shift + Enter有乜唔同
直接enter結果與原本無分別
用咗Ctrl + Shift + Enter就能顯示正確結果
作者: peter_chan    時間: 2024-7-2 14:27

本帖最後由 peter_chan 於 2024-7-2 14:28 編輯
Very NICE
Thanks ching

想請教一下Ctrl + Shift + Enter有乜唔同
直接enter結果與原本無分別
用咗Ctrl ...
sbg 發表於 2024-7-2 10:48



    M$ Execl 我唔清楚﹐因為我一直都無用新版的M$ Excel。
但google sheet﹐我就一直有用。


你想C欄得出 A+B欄的結果﹐傳統做法﹐你需要不停打....
=A1+B1
=A2+B2
=A3+B3
=A4+B4
一直打﹐按你需要一直打落去/拉落去。

但係﹐Array Formula 就只需在第一格打就可以。
你在第一格打 :
=A1:A+B1:B
再按 Ctrl+Shift+Enter
佢就會自動轉成 :
=ArrayFormula(A1:A+B1:B)

成個C欄﹐都會自動係A欄+B欄的結果。
唔再需要C欄每一格都輸入formula。

=ArrayFormula(A1:A+B1:B)
當中紅字位置﹐就係你需要的結尾位置。
Google 唔打結尾的位置﹐即係代表成欄都填滿。
M$ Excel 好似一定要講明結尾位置﹐例如做10行(如下)。
=ArrayFormula(A1:A10+B1:B10)
作者: platinum    時間: 2024-7-2 17:03

本帖最後由 platinum 於 2024-7-2 17:07 編輯

這裡也有一些相關的formula
https://www.ablebits.com/office- ... number-from-string/
作者: lamsir    時間: 2024-7-2 17:45

Very NICE
Thanks ching

想請教一下Ctrl + Shift + Enter有乜唔同
直接enter結果與原本無分別
用咗Ctrl ...
sbg 發表於 2024-7-2 10:48



我嘗試逐步解釋這條公式同點解要用Array Formula：

1. MID(A2,ROW(INDIRECT("1:6")),1)
這部分的目的是從儲存格A2中提取出前6個字符。ROW(INDIRECT("1:6"))會生成一個包含數字1到6的數字陣列，而MID(A2,ROW(INDIRECT("1:6")),1)則會從A2中逐個提取這6個位置的字符。

2. ISNUMBER(--MID(A2,ROW(INDIRECT("1:6")),1))
這部分是檢查上一步提取的每個字符是否為數字。使用--將字符轉換為數字，然後使用ISNUMBER函數檢查是否為數字。

3. IF(ISNUMBER(--MID(A2,ROW(INDIRECT("1:6")),1)),999,ROW(INDIRECT("1:6")))
這部分是創建一個數字陣列，該陣列的元素對應到前6個字符是否為數字。如果字符是數字，則對應的元素設為999，否則將對應的元素設為該字符所在的行數。

4. MIN(IF(ISNUMBER(--MID(A2,ROW(INDIRECT("1:6")),1)),999,ROW(INDIRECT("1:6"))))-1
這部分是找到上一步所創建的數字陣列中的最小值（除去999）。然後減去1以獲得最小值的前一個位置。

5. LEFT(A2,MIN(...)-1)
這部分是從儲存格A2中提取出從開頭到上一步找到的位置之前的字符。

6. TEXT(LEFT(...), "000000")
這部分是將上一步提取的字符轉換為固定長度的文字，該文字的長度為6位，不足6位的部分在前面補 0。

7. MID(A2,MIN(...)-1,LEN(A2))
這部分是從儲存格A2中提取出上一步找到的位置之後的所有字符。

8. REPT(" ", 30)
這部分是創建30個空格。

9. 最前的LEFT(... & ... & ..., 30)
這部分將前面提到的三個部分連接在一起，並截取前30個字符。如果連接後的文字長度不足30，則在後面補上空格。

由於第 1, 3 ,4部份都會返回數字陣列(Array)，如公式中其他函數要逐個處理陣列中的數值，就必需輸入完後按下 Ctrl+Shift+Enter來告訴 Excel這是一個陣列公式。相反，如果按下普通的 Enter鍵，公式將被視為普通的儲存格公式，僅會處理返回數字陣列的第一個元素。
作者: sbg    時間: 2024-7-2 22:05

我嘗試逐步解釋這條公式同點解要用Array Formula：

1. MID(A2,ROW(INDIRECT("1:6")),1)
這部分的目的是 ...
lamsir 發表於 2024-7-2 17:45



冇得彈
逐個解釋非常清晰  
作者: sbg    時間: 2024-7-2 22:06

這裡也有一些相關的formula
platinum 發表於 2024-7-2 17:03



Thx ching
作者: sbg    時間: 2024-7-2 22:06

M$ Execl 我唔清楚﹐因為我一直都無用新版的M$ Excel。
但google sheet﹐我就一直有用。


你想C欄 ...
peter_chan 發表於 2024-7-2 14:27


thx ching
作者: DennisTan    時間: 2024-7-4 22:43

我嘗試逐步解釋這條公式同點解要用Array Formula：

1. MID(A2,ROW(INDIRECT("1:6")),1)
這部分的目的是 ...
lamsir 發表於 2024-7-2 17:45


勁！！！
作者: s20012797    時間: 2024-7-5 12:59

本帖最後由 s20012797 於 2024-7-5 13:02 編輯

VBA
  1. Sub FormatData()
  2.     Dim lastRow As Long
  3.     Dim i As Long
  4.    
  5.     ' 找到最後一行數據
  6.     lastRow = Cells(Rows.Count, "A").End(xlUp).Row
  7.    
  8.     ' 遍歷每一行數據
  9.     For i = 1 To lastRow
  10.         ' 提取產品 ID 的前 6 位數字,並補零到 6 位
  11.         Cells(i, "B").Formula = "=LEFT(TEXT(LEFT(A" & i & ",MIN(IF(ISNUMBER(--MID(A" & i & ",ROW(INDIRECT(""1:6"")),1)),999,ROW(INDIRECT(""1:6"")))})-1),""000000"")&MID(A" & i & ",MIN(IF(ISNUMBER(--MID(A" & i & ",ROW(INDIRECT(""1:6"")),1)),999,ROW(INDIRECT(""1:6"")})),LEN(A" & i & ")),LEN(A" & i & ")-MIN(IF(ISNUMBER(--MID(A" & i & ",ROW(INDIRECT(""1:6"")),1)),999,ROW(INDIRECT(""1:6"")})))+1)&REPT("" "", 30-LEN(Cells(i,""B"").Formula))"
  12.         ' 將結果填入 B 列
  13.         Cells(i, "B").Value = Cells(i, "B").Formula
  14.     Next i
  15.    
  16.     ' 調整列寬
  17.     Range("A:B").EntireColumn.AutoFit
  18. End Sub
複製代碼
Python
  1. import re
  2. # 讀取資料
  3. data = []with open('input_file.txt', 'r') as file:   
  4. for line in file:        
  5. data.append(line.strip())
  6. # 格式化數據
  7. for i, row in enumerate(data):   
  8. # 提取產品 ID 的前 6 位數字,並補零到 6 位   
  9. product_id = row[:6].zfill(6) + row[6:]   
  10. data[i] = product_id.ljust(30)
  11. # 寫入輸出檔案
  12. with open('output_file.txt', 'w') as file:   
  13. file.writelines([line + '\n' for line in data])
  14. # 調整列寬
  15. print("資料已成功格式化,列寬已自動調整。")
複製代碼
作者: sbg    時間: 2024-7-5 16:34

VBAPython
s20012797 發表於 2024-7-5 12:59




跟之前 lamsir ching做法已經成功
但都想學吓VBA
THANK ching  
作者: ivanyung    時間: 2024-7-6 22:33

本帖最後由 ivanyung 於 2024-7-7 08:11 編輯
  1. =LEFT(REPT("0",6-LEN(-LOOKUP(,-LEFT(A2,ROW($1:$6)))))&A2&REPT(" ",30),30)
複製代碼
無諗過不用 vba 也可以做到。試用公式做，非陣列的。
作者: ivanyung    時間: 2024-7-7 11:03

還是習慣性用 VBA
工具，引用 .... Microsoft VBscript regular expression
  1. Function AddZero(MyStr As String) As String
  2.    
  3.     Dim MyRegEx As New RegExp
  4.     MyRegEx.Pattern = "^[0-9]{1,6}"
  5.    
  6.     Dim NewLen As Integer
  7.     NewLen = Len(MyRegEx.Replace(MyStr, ""))
  8.    
  9.     MyStr = String(6 - Len(MyStr) + NewLen, "0") & MyStr & String(30, " ")
  10.    
  11.     AddZero = Left(MyStr, 30)
  12.    
  13. End Function
複製代碼
在任意一個cell 中打入
  1. =Addzero(a2)
複製代碼
即可回傳 A2 之新值
作者: lamsir    時間: 2024-7-7 18:19

本帖最後由 lamsir 於 2024-7-7 18:31 編輯
無諗過不用 vba 也可以做到。試用公式做，非陣列的。
ivanyung 發表於 2024-7-6 22:33


厲害

呢條公式比起我之前寫的簡潔高雅，我冇諗過唔用Array formula都可以解決此問題

此式最巧妙地方係
1) 通過 -LEFT(A2,ROW($1:$6)) 中的-將非數字的字串設為#VALUE錯誤值，返回一個6元素的負數及錯誤值陣列
2) 然後用 LOOKUP函數省略look_value技巧，找出返回陣列中最後一個非錯誤值，再通過LOOKUP前的-號令負數變回正數，從而確定數字有幾多個位

Ching真乃 Excel達人，佩服佩服

不過公式還有一個地方可修正如下：
  1. =LEFT(REPT("0",6-LEN(-LOOKUP(,-LEFT(A2,ROW(INDIRECT("1:6"))))))&A2&REPT(" ",30),30)
複製代碼
最好用 INDIRECT函數鎖定1-6，否則當係1-6列進行插入/刪除時會有問題

作者: ivanyung    時間: 2024-7-8 13:28

回覆 20# lamsir

最初是看到師兄在不使用VBA便能完成。所以便胆粗粗試下，我並不是師兄所說的那麼利害。
難得有師兄欣賞，那再來一個吧！
  1. =LEFT(REPT("0",6-SUMPRODUCT(1*ISNUMBER(--MID(A2,ROW($1:$6),1)),1*ISNUMBER(--LEFT(A2,ROW($1:$6)))))&A2&REPT(" ",30),30)
複製代碼
作者: sbg    時間: 2024-7-8 22:08

無諗過不用 vba 也可以做到。試用公式做，非陣列的。
ivanyung 發表於 2024-7-6 22:33


多謝ching另一個寫法
真係只要動腦筋就得

不過小弟真係才疏學淺
淨係識d最基本功能
依照ching方程要逐個功能去查下點用
作者: sbg    時間: 2024-7-8 22:10

厲害

呢條公式比起我之前寫的簡潔高雅，我冇諗過唔用Array formula都可以解決此問題

此 ...
lamsir 發表於 2024-7-7 18:19


再次多謝 lamsir ching
給我解釋 ivanyung ching的方程
使我理解得更深入




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