☰

1 つの列に 1 つの会社、他の列に複数の電子メール アドレスを入力して行を分割する

tag-icon • tag-icon microsoft-excel
1 つの列に 1 つの会社、他の列に複数の電子メール アドレスを入力して行を分割する

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ファイルを 1 行ずつ読み込み、各行をカンマ ( -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 リリース 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

関連情報