Преобразование CSV в строки, а не в столбцы

Содержание

Вопрос или проблема
Ответ или решение
Способ 1: Использование формул Excel
Способ 2: VBA Макрос
Как использовать макрос
Результат

Вопрос или проблема

У меня есть файл с уникальными ссылками в столбце A (например, названия компаний) и одним или несколькими значениями, связанными с этой уникальной ценностью, в столбце B (например, счета). Эти значения разделены запятыми, некоторые компании имеют только один счет, а некоторые – много. Используя ‘текст в столбцы’, я получаю все номера счетов в отдельных столбцах на одной строке с названием компании.

Что я хотел бы сделать вместо этого, так это получить каждый номер счета на новой строке, каждый с соответствующим названием компании, чтобы у меня был лист шириной всего в 2 столбца и возможность поиска на уровне отдельных счетов, ссылаясь на правильную компанию. Это возможно?

Вы можете использовать формулы Excel, чтобы “вручную” разделять ваши данные. Если вы используете эту формулу, она найдет первое вхождение запятой и затем обрежет до этого вхождения:

=IF(A1="","",IFERROR(MID(A1,1,FIND(",",A1)-1),A1))

Под ячейкой, где у нас находятся наши исходные данные (в нашем случае A1 – это наши исходные данные), нам нужно индексировать данные, чтобы не включать то, что мы только что удалили:

=MID(A1,LEN(B1)+2,LEN(A1))

Вот как все работает вместе:

@Jonnie,
Если вы используете TextToColumns, чтобы распределить номера счетов по различным столбцам.
Затем перейдите в меню ‘Экран’ и нажмите на иконку выше макросов справа. Введите имя для макроса и нажмите ‘создать’. Затем вставьте код ниже между двумя строками с sub.
Закройте окно VBA, чтобы вернуться к вашим данным. Щелкните где-нибудь в данных. Снова нажмите на иконку макросов, выберите ваше имя макроса и нажмите Запуск.

Dim data As Range
Dim i As Integer
Dim j As Integer

Set data = ActiveCell.CurrentRegion 'Выбор активного региона

For i = data.Rows.Count To 1 Step -1 'для всех строк (начиная с низа)
    For j = 3 To data.Columns.Count 'для всех столбцов (начиная с третьего столбца)
        If Not IsEmpty(data(i, j)) Then 'если есть значение
            data.Rows(i + 1).Insert 'вставить новую строку ниже
            data(i + 1, 1) = data(i, 1) 'скопировать компанию
            data(i + 1, 2) = data(i, j) 'скопировать номер счета
            data(i, j).Clear 'удалить оригинальное значение
        End If
    Next j
Next i

Это должно привести к списку

компания A; номер счета 1
компания A; номер счета 2
компания B; номер счета 3
компания C; номер счета 4

Ответ или решение

Конвертация данных из формата CSV, где значения представлены в строках, в формат со строками вместо столбцов является распространенной задачей в анализе данных. В вашем случае вы имеете уникальные ссылки (например, названия компаний) в первом столбце и связанные с ними значения (например, номера аккаунтов) во втором столбце, разделенные запятой. Чтобы структурировать данные в виде двух колонок, где каждый номер аккаунта будет на новой строке вместе с соответствующим названием компании, можно воспользоваться несколькими методами, включая встроенные функции Excel и написание макроса на VBA.

Способ 1: Использование формул Excel

Для того чтобы вручную разделить данные на отдельные строки, можно использовать следующие шаги:

Изначальная подготовка данных: Используйте функцию Text to Columns, чтобы разделить номера аккаунтов на разные столбцы, как вы уже делали.
Создание формулы для извлечения данных: В ячейке под вашей первой строкой введите следующую формулу:
```
=IF(A1="", "", IFERROR(MID(A1, 1, FIND(",", A1) - 1), A1))
```
Эта формула находит первое вхождение запятой и возвращает текст до нее.
Индексация следующего значения: Ниже в другой ячейке (например, B1) используйте формулу, чтобы удалить уже извлеченное значение:
```
=MID(A1, LEN(B1) + 2, LEN(A1))
```
Повторное применение формул: Копируйте и вставляйте формулы вниз по строкам, пока не извлечете все данные.

Способ 2: VBA Макрос

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

Sub TransformData()
    Dim data As Range
    Dim i As Integer
    Dim j As Integer

    Set data = ActiveCell.CurrentRegion ' Выбираем текущий регион данных

    For i = data.Rows.Count To 1 Step -1 ' Перебираем все строки с конца
        For j = 3 To data.Columns.Count ' Перебираем все столбцы начиная с третьего
            If Not IsEmpty(data(i, j)) Then ' Если значение не пустое
                data.Rows(i + 1).Insert ' Вставляем новую строку ниже
                data(i + 1, 1) = data(i, 1) ' Копируем название компании
                data(i + 1, 2) = data(i, j) ' Копируем номер аккаунта
                data(i, j).Clear ' Очищаем оригинальное значение
            End If
        Next j
    Next i
End Sub

Как использовать макрос

Откройте Excel и загрузите файл с вашими данными.
На вкладке Разработчик выберите Visual Basic.
Вставьте указанный код в новое модульное окно.
Вернитесь в Excel, выберите диапазон с данными и запустите макрос.

Результат

После выполнения одного из предложенных методов, вы получите чистый и структурированный список, где каждая строка будет представлять собой комбинацию названия компании и соответствующего номера аккаунта, что значительно упростит работу с данными:

Компания A; акк. 1
Компания A; акк. 2
Компания B; акк. 3
Компания C; акк. 4

Эти методы не только помогут вам организовать ваши данные, но и сделают их более доступными для дальнейшего анализа и поиска. Выбор метода зависит от объема вашего набора данных и предпочтений в работе с инструментами Excel.