☰

將一行拆分為一列中的一家公司和其他欄位中的多個電子郵件地址

tag-icon • tag-icon microsoft-excel
將一行拆分為一列中的一家公司和其他欄位中的多個電子郵件地址

我的 Excel 中的數據如下所示：

column1 column2 column3, column4, column5
Company1 email1 email2 email3
Company2 email1 email2
Company3 email1 email2 email3 email4 email5

我有大約 25,000 行此類數據，其中可能有 25,000 個公司和 40,000 個電子郵件地址。我想讓數據看起來像這樣：

Company1 email1
Company1 email2
Company1 email3
Company2 email1
etc.

答案1

儲存檔案csv然後使用awk（linux或cygwin工具）：

awk -F, '{if (NR>1) {if (NF==1) {print $1} else {for (f=2; f<=NF; f++) print $1","$f}}}' ./myfile.csv >./mynewfile.csv

解釋：

awk逐行讀入文件，它使用逗號作為分隔符號（-F，）將每一行分成「字段」（即列），每個字段都被$1調用。它跳過第一行（標題），然後為每一行建立一系列行，其中每個欄位位於第一個欄位前面的單獨行上。輸出被寫回新文件中。您可以在 Excel 中開啟這個新檔案。

答案2

這個巨集將完成以下工作：

Public Sub createrows()
    Application.ScreenUpdating = False
    Dim wks As Worksheet
    Set wks = ActiveSheet
    firstrow = 2
    thecolumn = 3
    searchingrow = True
    therow = firstrow
    While searchingrow
        totalcolumns = wks.Cells(therow, Columns.Count).End(xlToLeft).Column
        For j = totalcolumns To thecolumn Step -1
            a = wks.Cells(therow, j)
            Rows(therow + 1).Insert shift:=xlShiftDown
            wks.Cells(therow + 1, 1) = wks.Cells(therow, 1)
            wks.Cells(therow + 1, 2) = wks.Cells(therow, j)
        Next j
        therow = therow + 1
        If wks.Cells(therow, 1) = "" Then searchingrow = False
    Wend
    wks.Range(Cells(1, thecolumn), Cells(therow, 1000)).Delete
    Application.ScreenUpdating = True
    themessage = MsgBox("Finished", vbInformation)
End Sub

使用 ALT+F11 開啟 VBA/宏，在下方插入一個新模組本練習冊並將程式碼貼到右側。執行巨集。

答案3

您無法直接在 Excel 中執行此操作。您最好的選擇是讓一個小程式/腳本為您轉換。我向你提出一個使用的答案Python。

  1. 如果您的電腦上尚未安裝 python，請下載並安裝它。

    Python 發布 Python 2.7.10 | Python.org （直接連結）

  2. 在 Excel 中，將檔案另存為 CSV。
    注意：另存為對話方塊中可能有多個 CSV 選項。請務必選擇CSV（逗號分隔）。
  3. 將下面的程式碼複製到記事本中並另存為convert.py.你必須選擇全部文件以便記事本以正確的檔案副檔名儲存。請記住分別將和
    替換為正確的輸入和輸出檔名。此外，您應該將所有反斜線 ( ) 變更為正斜線 ( )。"c:/users/user/desktop/book1.csv""c:/users/user/desktop/book2.csv"\/
infile = open("c:/users/user/desktop/book1.csv", "rb")
outfile = open("c:/users/user/desktop/book2.csv", "wb")
import csv
reader = csv.reader(infile)
writer = csv.writer(outfile)
reader.next() # skip header
writer.writerow(["Company", "Email"])
writer.writerows(((row[0], email) for row in reader \
                                  for email in row[1:] if email != ""))
outfile.close()
infile.close()
  1. 雙擊 python 檔案運行它並執行轉換。

答案4

這是另一個執行速度相對較快的 VBA 巨集，因為它在 VBA 數組中而不是在工作表中工作。

假設來源資料從A1或A2開始；資料區域是連續的，並且每個公司的電子郵件是連續的（因此一行中的第一個空白儲存格位於最後一個電子郵件地址之後）。如果這些假設中的任何一個不成立，則需要對程式碼進行少量修改。

還有一個假設是沒有列標籤，程式碼註解中有關於如何補償的說明。

Option Explicit
Sub RowsToColumns()
    Dim vSrc As Variant
    Dim COL As Collection
    Dim wsSrc As Worksheet, wsRes As Worksheet, rRes As Range
    Dim S(0 To 1) As String
    Dim I As Long, J As Long

'Define source and result worksheets and ranges
'Alter as necessary
Set wsSrc = Worksheets("sheet3")
Set wsRes = Worksheets("sheet4")
    Set rRes = wsRes.Cells(1, 1)

'Read source data into array
' This method assumes data starts in A2, and is
'  contained in a contiguous array.
'But other methods could be used
vSrc = wsSrc.Cells(2, 1).CurrentRegion

'Collect the results into Collection object
'Assumes no header row, if there is, then start
'  with for I = 2 to ...
Set COL = New Collection
For I = 1 To UBound(vSrc, 1) 'the rows
    For J = 2 To UBound(vSrc, 2) 'the columns
        S(0) = vSrc(I, 1) 'company name
        S(1) = vSrc(I, J) 'email
        If S(1) <> "" Then
            COL.Add S
        Else
            Exit For 'assumes first blank in email list is end of list
        End If
    Next J
Next I

'Create results array
ReDim vres(1 To COL.Count, 1 To 2)
For I = 1 To COL.Count
    With COL(I)
        vres(I, 1) = COL(I)(0)
        vres(I, 2) = COL(I)(1)
    End With
Next I

'Write the results to worksheet
Set rRes = rRes.Resize(rowsize:=UBound(vres, 1), columnsize:=UBound(vres, 2))
With rRes
    .EntireColumn.Clear
    .Value = vres
    .EntireColumn.AutoFit
End With

End Sub

相關內容