Преобразование многострочных данных в строки - Советы по Excel

Содержание

lan Z прислал на этой неделе задачу Excel. Его отдел MIS дает ему файл, созданный на основе старого отчета COBOL. После открытия файла в Excel у него есть 2500 строк:

CustLastName | CustFirstName | CustMiddle | CustAddress | CustCity |CustState | CustZip Useless LINE1 Useless LINE2 CustLastName | CustFirstName | CustMiddle | CustAddress | CustCity |CustState | CustZip Useless LINE1 Useless LINE2

Алан хочет получить этот ASCII-отчет в удобном формате: по одной строке для каждого клиента, с добавлением состояния и почтового индекса к остальной информации. Он также хочет удалить две бесполезные строчки. Очевидно, мы не хотим делать все это вручную. Вот один из способов быстро разобраться с беспорядком.

С формулами

  • Вставьте два пустых столбца слева от данных.
  • Добавьте строку заголовка над данными.
  • Столбец А называется «Последовательность».
  • Столбец B называется RowType.
  • Столбец C называется «Данные».
  • Сделайте все заголовки жирными
  • Столбец A будет использоваться для присвоения номера каждой логической записи в отчете. Поскольку в этом отчете есть 4 физических строки для каждой логической записи, нам нужно, чтобы каждый набор из 4 строк имел одинаковый номер логической записи. Обычно я ввожу значения для первой записи, а затем разрабатываю формулы для второй записи, которые можно скопировать во весь отчет.
  • В ячейках A2: A5 введите 1. В ячейке A6 введите = A5 + 1. В ячейке A7 введите = A6. Скопируйте A7 в A8 и A9. Теперь у вас есть копируемый набор формул для второй логической записи отчета.
  • Выберите A6: A9 и нажмите Ctrl C, чтобы скопировать. Выберите A10: A2501 и нажмите Ctrl V, чтобы вставить.
  • Столбец B будет использоваться для определения того, является ли конкретная строка 1-м, 2-м, 3-м или 4-м сегментом логической записи.
  • В ячейках B2: B5 введите 1, 2, 3 и 4. В ячейке B6 введите = B2. Скопируйте ячейку B6 из B7: B2501.

После изменения значений

Теперь, когда у вас есть порядковые номера и типы строк для всех ваших данных, вам нужно изменить формулы на значения. Выберите A2: B2501. Правка> Копировать, Правка> Специальная вставка> Значения> ОК.

Теперь, когда всем строкам присвоены порядковые номера и типы строк, мы почти закончили. Отсортируйте данные по типу строки в качестве первичного ключа и последовательности в качестве вторичного ключа. Это приведет к тому, что 625 верхних строк каждой записи переместятся в ячейки C2: C626. Вторая строка каждой записи будет в C626: C1251. «Бесполезные» строки начнутся в C1252 и могут быть удалены. Переместите ячейки C626: C1251 в ячейку D2. В ячейке E2 введите формулу =C2&D2. Вы можете скопировать эту формулу из E2 в E626. Используйте тот же трюк со вставкой специального значения, чтобы перейти от формул к значениям, удалить столбцы AD, и вы получите результат.

Отсюда вы можете использовать мастер Text to Columns для дальнейшей обработки этих данных.

Вы можете легко настроить эту процедуру для работы с любым набором отчетов ASCII. Вам нужно выяснить, сколько физических напечатанных строк составляет одну логическую запись в отчете.

Интересные статьи...