Агрегировать данные Excel

Question

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

Вот что у меня сейчас:

 
ID      course   grade
1       geo        90
2       math       80
3       geo        75
4       math       90
4       sci        90
4       geo        50
5       sci        85

Это то, что я хочу:

ID     geo     math     sci     gradegeo     grademath     gradesci
1     1        0        0        90        
2     0        1        0                          80
3     1        0        0        75
4     0        1        1        50                90        90
5     0        0        1                                    85

Надеюсь, кто-то может помочь :)

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

Answer 1

Используйте сводную таблицу. Это не "транспонирование", скажем так, а "агрегация" - различие в терминологии, которое должно помочь вам в поиске Google :)

• Введите данные

• Выберите (выделите) все соответствующие данные и создайте сводную таблицу с помощью команды "Вставка" -> "Сводная таблица" в "Новой рабочей таблице".

• Настройте сводную таблицу в соответствии с вашими потребностями. В вашем конкретном случае перетащите поле курса на метку столбца, поле идентификатора на метку строки и дважды перетащите значение оценки в область "Значения": один раз оставьте все как есть, чтобы получить "сумму" оценки (это будет суммировать только одно значение, если только один студент не пойдет один и тот же курс дважды в течение одного семестра), а для другой "Суммы оценок" щелкните левой кнопкой мыши стрелку раскрывающегося списка и посмотрите "Настройки поля значений". ». Измените его с "Sum" на "Count".

  • Выделите всю сводную таблицу, нажмите Ctrl+C, чтобы скопировать в буфер обмена, перейти на новый лист и в параметрах вставки (раскрывающийся список под значком буфера обмена на вкладке "Главная" ленты) выполнить "Вставить значения с исходным форматированием". ,

  • Переставьте столбцы и изменяйте форматирование, пока не получите то, что вам нужно. Вот что я сделал, чтобы он выглядел как ваш желаемый результат; у меня ушло около 30 секунд после генерации сводной таблицы:

Answer 2

Используйте две сводные таблицы (для первой: выберите диапазон, «Вставить», «Таблицы», «Сводная таблица», «Сводная таблица», «ОК», щелкните в отображаемом поле, перетащите идентификатор в метки строк, «Курс» в метки столбцов и оценку в значения Σ.)

Скопируйте эту сводную таблицу направо, и для левой стороны убедитесь, что значения Σ имеют число, для правой - одну сумму. Скройте верхнюю строку и столбец Метки строк для правой таблицы. Переименуйте заголовки столбцов по мере необходимости.

Чтобы убедиться, что в левой таблице указано 0, а не пусто, щелкните по нему правой кнопкой мыши, выберите Параметры сводной таблицы, Макет и формат, установите флажок «Пустые ячейки» и введите 0. При необходимости можно изменить формат чисел оценок, щелкнув левой кнопкой мыши на содержании значений Σ, настройках поля значений (также где изменить сумму на счет и т.д.) И формате чисел. (Если форматирование настроено непосредственно в сводной таблице, оно будет отменено при следующем обновлении).

Перезаписать метки строк идентификатором, и, надеюсь, вы получите результат, который вы ищете.

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