Электронная таблица De-Pivot Excel

Question

Использование Excel 2003 Можно ли ...

Имеется электронная таблица этого макета с разрешениями: чтение = R, запись = W и отсутствие разрешения = X

  Users     Perm1    Perm2   Perm3 ...    ..... PermX
  User1       R        W        X  ...    .....   R 
  User2       X        W        X  ...    .....   W
  User3       R        R        X  ...    .....   X
  ....
  ....

Это может быть сведено к этому

  Users     Perms    Value
  User1     Perm1      R
  User1     Perm2      W
  User1     Perm3      X
  ....
  ....
  User1     PermX      R
  User2     Perm1      X
  User2     Perm2      W
  User2     Perm3      X
  ....
  ....
  User2     PermX      W
  User3     Perm1      R
  User3     Perm2      R
  User3     Perm3      X
  ....
  ....
  User3     PermX      X

Есть простой способ сделать это?

Answer 1

Хорошо, я ответил на этот вопрос через 6 лет после того, как его спросили ... И я использую Excel 2013 сейчас. Насколько я понимаю, этот метод должен применяться к Excel 2007, 2010, 2013. Не уверен, что он работает в Excel 2016, хотя.

Сначала откройте мастер сводных таблиц и диаграмм с помощью Alt D P. Он скрыт с Excel 2007, и его можно открыть только с помощью сочетаний клавиш.

Выберите "Несколько диапазонов консолидации" и нажмите "Далее".

Выберите "Создать одно поле для меня" и нажмите "Далее".

Нажмите на Range и выберите диапазон, который нужно «разворачивать» (например, Sheet1!$ A $ 1:$ D $ 4). Нажмите Готово.

После закрытия мастера вы увидите сводную таблицу, созданную следующим образом:

Дважды щелкните ячейку "Общий итог" (т.е. E8 в моем примере), и вы получите таблицу «De-pivoted» на новом листе, например:

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

Answer 2

Я бы использовал функцию Index:

Допустим, ваш источник данных находится на листе 1 в A1:D20. Затем в листе 2 используйте эти формулы:

Column A: "Row": =1, =1, =1, =A1+1, drag down
Column B: "Column": =1, =2, =3, =A1, drag down
Column C: "User": =INDEX(Sheet1!$A$2:$A$20,A2)
Column D: "Perms": =INDEX(Sheet1!$B$1:$D$1,B2)
Column E: "Value": INDEX(Sheet1!$B$2:$D$20,A2,B2)

Что должно выглядеть так:

Row Column User  Perm  Value 
1   1      User1 Perm1 R     
1   2      User1 Perm2 W     
1   3      User1 Perm3 X     
2   1      User2 Perm1 X     
2   2      User2 Perm2 W     
2   3      User2 Perm3 X     
3   1      User3 Perm1 R     
3   2      User3 Perm2 R     
3   3      User3 Perm3 X     

Надеюсь это поможет.

РЕДАКТИРОВАТЬ:

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

Row Column
1   1
1   2
1   3
1   4
...
1   39
1   40
2   1
2   2
2   3
2   4
...
2   39
2   40
3   1
...
500 40

Если вы хотите использовать формулы, вы можете перетащить их вниз, чтобы сделать это:

Column A: "Count": =1, =A1+1
Column B: "Row": =INT((A1-1)/5)+1
Column C: "Column": =MOD(A1-1,40)+1  (where you change 40 to the number of permissions you have)
Column D: "User": =INDEX(Sheet1!$A$2:$A$20,B2)
Column E: "Perms": =INDEX(Sheet1!$B$1:$D$1,C2)
Column F: "Value": INDEX(Sheet1!$B$2:$D$20,B2,C2)

Answer 3

Я написал программу удаления таблиц в javascript. Смотрите здесь. Объясняя рабочий процесс:

1. Конвертируйте таблицу в словарь json, используя форк инструмента "Mr.Data Converter" от Shan Carter. (не разрешено размещать больше ссылок здесь)
2. Перейдите через JSON, чтобы вывести список таблицы линейно.

Смотрите этот рисунок для объяснения