Как реорганизовать большой файл Excel

Question

Мой друг "унаследовал" лист Excel, который использовался в качестве файла адреса.

К сожалению, человек, который создал это, имел ужасную идею сделать это так:

Name   |Lorem               |Surname  |Ipsum               |ZipCode | 10139
Street |Lorem Street        |City     |Ipumvillw           |Tel     | 01020302
       |                    |         |                    |        |
Name   |Lorem               |Surname  |Ipsum               |ZipCode | 10139
Street |Lorem Street        |City     |Ipumvillw           |Tel     | 01020302
       |                    |         |                    |        |
Name   |Lorem               |Surname  |Ipsum               |ZipCode | 10139
Street |Lorem Street        |City     |Ipumvillw           |Tel     | 01020302
       |                    |         |                    |        |
Name   |Lorem               |Surname  |Ipsum               |ZipCode | 10139
Street |Lorem Street        |City     |Ipumvillw           |Tel     | 01020302

И, конечно, мой друг хотел бы переформатировать это как:

|Name         | Surname      |Tel       |City         |Street    |ZipCode |
|Lorem        |Ipsum         |01010101  |Ipsumville   |Lorem St. |10139   |
|    ...      | ...          | ...      | ...         | ...      |  ...   |
|Lorem        |Ipsum         |01010101  |Ipsumville   |Lorem St. |10139   |
|Lorem        |Ipsum         |01010101  |Ipsumville   |Lorem St. |10139   |

Я мог бы, вероятно, сохранить его как csv, собрать воедино скрипт Perl для синтаксического анализа csv и реорганизовать его более разумным способом, но я занят, поэтому, если у кого-то есть какое-то непрограммное решение, я бы посмотрел, сможет ли он им управлять сам.

---

Плохие новости Таблицы не являются регулярными. Т.е. целый адрес может занимать более 2 строк, и это более или менее случайно. По-видимому, это своего рода отчет от какой-то маленькой бухгалтерской программы, они печатают в файл, а затем отбрасывают его в Excel. Я не буду видеть, может ли parse-o-matic помочь.

(Я смотрю на Parse-O-Matic, потому что я использовал его для аналогичной проблемы много лет назад, существует бесплатная базовая версия, и я надеюсь, что она может пригодиться моему другу в качестве инструмента общего назначения в будущем).

Answer 1

Это просто. Допустим, у вас есть данные, начиная с ячейки A1 :

| A1 | B1 | C1 | ...

| A2 | B2 | С2 | ...

В 1-й пустой ячейке верхней строки введите формула =A2 и перетащите / заполните ячейку вправо для того же числа столбцов, чтобы ваш лист выглядел следующим образом:

| A1 | B1 | C1 | ... | = A2 | = B2 | = C2 | ...

| A2 | B2 | С2 | ...

1. выделите все ячейки с формулами в этом верхнем ряду и перетащите / заполните его для остальных строк
2. выделите весь лист, затем скопируйте и вставьте специальные (только значения) на другой лист
3. в этом новом листе отфильтруйте все, кроме строк с именем в первом столбце
4. сделать заголовки для столбцов с актуальными данными
5. удалить столбцы со старыми заголовками

Answer 2

Я не гарантирую, что это гораздо меньше усилий, чем другие идеи, которые были предложены, но вот мое решение; я перевел

               

в

               

(на Sheet2). Формулы:

• A2: =OFFSET(Sheet1!$B$1, ROW()*3-6, 0, 1, 1)
• B2: =OFFSET(Sheet1!$D$1, ROW()*3-6, 0, 1, 1)
• C2: =OFFSET(Sheet1!$F$2, ROW()*3-6, 0, 1, 1)
• D2: =OFFSET(Sheet1!$D$2, ROW()*3-6, 0, 1, 1)
• E2: =OFFSET(Sheet1!$B$2, ROW()*3-6, 0, 1, 1)
• F2: =OFFSET(Sheet1!$F$2, ROW()*3-6, 0, 1, 1)

и конечно вы перетаскиваете / заполняете.  Первый параметр в каждом вызове OFFSET - это местоположение первого появления нужного поля в первом листе; например, вы хотите Tel в столбце C , а первый Tel в Sheet1 находится в ячейке F2 .  Вы умножаете номер текущей строки на 3, потому что каждая «запись» на Sheet2 - это всего лишь одна строка, а записи на Sheet1 - по три строки в каждой.  -6 просто фактор выдумки , чтобы заставить его выйти право, при условии , что у вас есть заголовки на Sheet2 и их нет на Sheet1 - первая запись в строке 2, и 2 * 3 : 6, но, чтобы получить доступ к Sheet1!B1 по смещению от Sheet1!B1 , вам нужно смещение 0 (следовательно, вычтите 6).

Вы можете сохранять связь между Sheet2 и Sheet1 бесконечно, либо вы можете копировать и вставлять значения и отбрасывать их.

Answer 3

Я подумал о возможном решении:

1. Создайте новый пустой лист.
2. На исходном листе создайте автоматический фильтр и выберите фильтр "Имя" в первом столбце. Это "выберет" все нечетные строки. Выберите и скопируйте в новый лист.
3. Создайте другой фильтр, используя другое содержимое ячейки (улица). Это выберет все четные строки. Скопируйте их на новый лист, разместив строки справа от первого выделения.
4. Тот же подход может сработать, если ваши данные распределены по 3 или более строкам (в моем примере только две, но это может работать для шаблонов с 3 и более строками).
5. Создать строку вверху, добавить имя поля
6. Удалите столбцы, где были метки полей.