Excel 2013 宏表到列(用于文本分析)

Question

我正在尝试移动一些数据，以便更轻松地进行一些基本的文本挖掘。我有一个表格，每个句子都有一行，第一列作为标识符，后面的“N”列是单词。
例子:

Record  Word1   Word2   Word3   Word N
1       The     quick   brown   fox
2       jumps   over    the 
3       lazy    white       
4       dog         

我需要将数据从该表格式移动到一个列表，每行一个单词，以及该单词所在的记录。

例子:

Record  Word
1       the
1       quick
1       brown
1       fox
2       jumps
2       over
2       the
3       lazy
3       white
4       dog

我找到了将整个表格放在一列中的宏，但不是以我需要识别该单词出现在哪个记录中的方式。( Excel Macros: From Table to Column)

我还在这里找到了以下代码: http://community.spiceworks.com/scripts/show/1169-excel-table-to-single-column

Option Explicit

Public Sub DoCopies()
Dim lRowIdx As Long
Dim lColIdx As Long
Dim lRowStart As Long
Dim lRowOut As Long

Dim s1 As Worksheet
Dim s2 As Worksheet

Dim oBook As Workbook

Dim r As Range
Dim lRows As Long
Dim lCols As Long

  On Error GoTo errorExit

  Application.DisplayAlerts = False
  Set oBook = ThisWorkbook
  Set s1 = Worksheets(1)

  ' remove other tabs
  While (oBook.Sheets.Count > 1)
    oBook.Sheets(oBook.Sheets.Count).Delete
  Wend

  ' create the new tab
  Set s2 = oBook.Worksheets.Add(After:=oBook.Worksheets(oBook.Worksheets.Count))
  s2.Name = "Result"

  Set r = s1.UsedRange
  lCols = r.Columns.Count
  lRows = r.Rows.Count

  'skip header
  lRowStart = 1
  While (Trim$(s1.Cells(lRowStart, 1) = ""))
    lRowStart = lRowStart + 1
  Wend

  lRowStart = lRowStart + 1

  ' Take each row, put on tab 2
  For lRowIdx = lRowStart To lRows

    If (Trim$(s1.Cells(lRowIdx, 1)) <> "") Then

      For lColIdx = 1 To lCols
        lRowOut = lRowOut + 1
        s2.Cells(lRowOut, 1) = s1.Cells(lRowIdx, lColIdx)
      Next lColIdx

    End If

  Next lRowIdx

  s2.Activate

  Application.DisplayAlerts = True
  Exit Sub

errorExit:
  Application.DisplayAlerts = True
  Call MsgBox(CStr(Err.Number) & ": " & Err.Description, vbCritical Or vbOKOnly, "Unexpected Error")

End Sub

但是那个宏会返回这样的数据:

1
The
quick
brown
fox
2
jumps
over
the
<null>
3
lazy
white
<null>
<null>
4
dog
<null>
<null>
<null>

我试过玩代码，但无法弄清楚。

任何帮助，将不胜感激。谢谢!

Answer 1

Microsoft 已经为您有效地编写了大部分代码。所缺少的只是过滤列 Value选择(Blanks)然后删除这些行 - 并更改列标签，删除一列。详情 here .