Транспонировать данные из строки / столбца, которые соответствуют определенным критериям

Question

Я провел опрос, в котором запрашивался балл и приоритет для каждого вопроса.

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

В настоящее время, если идентификатор вопроса xx1, это оценка, но если идентификатор xx2, это приоритет.

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

Есть идеи как туда добраться?

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

Answer 1

Решение: я использовал многошаговый метод, похожий на предложенный @gtwebb, за исключением чего-то менее элегантного.

1. Используйте Text-to-Columns для разделения последнего десятичного знака идентификатора вопроса (это действительно ключ к нему). Последнее десятичное число говорит мне, что это либо 1 = Оценка, либо 2 = Приоритет.
2. Используйте оператор if() чтобы найти значение "2" в моем новом столбце. Если есть, скопируйте соответствующее значение приоритета в новый столбец Приоритет . Если нет, оставьте это поле пустым. Скопируйте формулу вниз.
3. Затем отфильтруйте и удалите все строки, которые имели значение "2", потому что мои строки со значением "1" теперь имеют как Score, так и Priority в двух столбцах в одной строке.
4. Переименуйте столбцы по мере необходимости.
5. Pivot, ???, и прибыль!

Answer 2

Вы можете попробовать бесплатный Microsoft Power Add-In Query (из Excel 2010). Это довольно интуитивно понятно для загрузки и преобразования ваших данных, как показано во второй таблице. Up Excel 2016 полностью интегрирован в Excel (Get и Transform).

Выполните следующие действия в пользовательском интерфейсе Power Query:

1. Импортируйте данные в Power Query (Ribbon Power Query -> из таблицы, курсор должен находиться где-то в ваших данных)
2. (необязательно) измените тип столбца Вопрос на текст (в зависимости от настроек ОС для форматирования чисел PQ распознает 1.1.1 как дату)
3. Щелкните правой кнопкой мыши столбец Вопрос -> Разделить столбец по разделителю -> в самом правом разделителе, разделитель --Custom-- =.
4. Выберите новый столбец question.2, под лентой Transform, нажмите на Pivot Column, определите Score как значение
5. Переименуйте столбцы «Вопрос.1», "1", "2" в "Вопрос", "Оценка", "Приоритет" (двойным щелчком мыши на имени столбца)
6. Закройте и загрузите в Excel (Главная лента)

здесь скрипт Power Query, который создается пользовательским интерфейсом.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"FirstName", type text}, {"LastName", type text}, {"Question", type text}, {"Score", Int64.Type}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type","Question",Splitter.SplitTextByEachDelimiter({"."}, QuoteStyle.Csv, true),{"Question.1", "Question.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Question.1", type number}, {"Question.2", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type1", {{"Question.2", type text}}, "de-CH"), List.Distinct(Table.TransformColumnTypes(#"Changed Type1", {{"Question.2", type text}}, "de-CH")[Question.2]), "Question.2", "Score", List.Sum),
    #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"Question.1", "Question"}, {"1", "Score"}, {"2", "Priority"}})
in
    #"Renamed Columns"

Answer 3

Нечто подобное будет работать, в зависимости от ваших данных

Option Explicit
Sub ScorePriority()
    Dim lastRow As Long
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    Dim i As Long

    For i = lastRow To 2 Step -1
        If Cells(i, 2) = Cells(i - 1, 2) And _
        Cells(i, 3) = Cells(i - 1, 3) And _
        Left(Cells(i, 4), 4) = Left(Cells(i - 1, 4), 4) Then
            Cells(i - 1, 6) = Cells(i, 5)
            Cells(i).EntireRow.Delete shift:=xlUp
        End If
    Next

End Sub