123
返回列表 發帖
lamsir

Rank: 2Rank: 2
11# 跳轉到 »
發表於 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鍵，公式將被視為普通的儲存格公式，僅會處理返回數字陣列的第一個元素。

TOP

sbg

Rank: 3Rank: 3Rank: 3
12#
發表於 2024-7-2 22:05 | 只看該作者
我嘗試逐步解釋這條公式同點解要用Array Formula：

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



冇得彈
逐個解釋非常清晰  

TOP

sbg

Rank: 3Rank: 3Rank: 3
13#
發表於 2024-7-2 22:06 | 只看該作者
這裡也有一些相關的formula
platinum 發表於 2024-7-2 17:03



Thx ching

TOP

sbg

Rank: 3Rank: 3Rank: 3
14#
發表於 2024-7-2 22:06 | 只看該作者
M$ Execl 我唔清楚﹐因為我一直都無用新版的M$ Excel。
但google sheet﹐我就一直有用。


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


thx ching

TOP

DennisTan

Rank: 2Rank: 2
15#
發表於 2024-7-4 22:43 | 只看該作者
我嘗試逐步解釋這條公式同點解要用Array Formula：

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


勁！！！

TOP

s20012797

Rank: 3Rank: 3Rank: 3
16#
發表於 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("資料已成功格式化,列寬已自動調整。")
複製代碼

TOP

sbg

Rank: 3Rank: 3Rank: 3
17#
發表於 2024-7-5 16:34 | 只看該作者
VBAPython
s20012797 發表於 2024-7-5 12:59




跟之前 lamsir ching做法已經成功
但都想學吓VBA
THANK ching  

TOP

ivanyung

Rank: 2Rank: 2
18#
發表於 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 也可以做到。試用公式做，非陣列的。

TOP

ivanyung

Rank: 2Rank: 2
19#
發表於 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 之新值

TOP

lamsir

Rank: 2Rank: 2
20#
發表於 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列進行插入/刪除時會有問題

TOP

123
返回列表