☰

열에 하나의 회사가 있고 다른 열에 여러 이메일 주소가 있는 행 분할

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에서는 이 작업을 직접 수행할 수 없습니다. 최선의 선택은 작은 프로그램/스크립트가 변환을 수행하도록 하는 것입니다. 나는 당신에게 다음을 사용하는 답변을 제안합니다.파이썬.

  1. 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

관련 정보