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

Интересно, можно ли транспонировать столбец с сгруппированными повторяющимися полями, например:

+---------+------+
| field_1 | 1    |
| field_2 | 2    |
| field_3 | 3    |
| field_4 | 4    |
| field_1 | 5    |
| field_2 | 6    |
| field_3 | 7    |
| field_4 | 8    |
| field_1 | 9    |
| field_2 | 10   |
| field_3 | 11   |
| field_4 | 12   |
+---------+------+

в уникальные столбцы, например:

+---------+---------+---------+---------+
| field_1 | field_2 | field_3 | field_4 |
+---------+---------+---------+---------+
| 1       | 2       | 3       | 4       |
| 5       | 6       | 7       | 8       |
| 9       | 10      | 11      | 12      |
+---------+---------+---------+---------+

Возможно ли это без скриптинга?


excel excel-formula
person vedar    schedule 25.05.2016    source источник
comment
Без сценариев (например, с использованием vba) ваш вопрос лучше задать на родственном сайте superuser.com.   -  person surfmuggle    schedule 26.05.2016
comment
@slackmuggle довольно часто делает что-то в Excel без скриптов. Для этого excel-formula есть целый тег. Что следует добавить.   -  person Forward Ed    schedule 26.05.2016
comment
@ForwardEd это вполне может быть, но я понимаю, что это лучше подходит для superuser.com.   -  person surfmuggle    schedule 26.05.2016

Ответы (3)


arrow_upward
5
arrow_downward

Введите приведенную ниже формулу в E3 (используя Ctrl+Shift+Enter), затем заполните вниз, а затем поперек.

=OFFSET($C$1,LARGE((ROW($B$2:$B$13)-1)*($B$2:$B$13=E$2),ROW()-2),0) & ""

введите здесь описание изображения

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

Попробую объяснить:

  • Все значения, которые мы хотим для каждого из отдельных значений «поля», смещаются на некоторое «x» количество строк из C1.

  • Взяв в качестве примера «поле_1», (ROW($B$2:$B$13)-1)*($B$2:$B$13=E$2) вернет массив номеров строк, умноженный либо на 1 (ИСТИНА), либо на ноль (ЛОЖЬ) в зависимости от того, соответствует ли значение ячейки «B» «полю_1» (из E2). -1 — это корректировка, потому что мы начинаем со строки 2. Возвращаемый массив выглядит так:

    [1,0,0,0,5,0,0,0,9,0,0,0]

  • Учитывая этот массив, нам нужно найти какой-то способ получать ненулевые значения одно за другим: мы можем использовать для этого функцию НАИБОЛЬШИЙ(), увеличивая второй аргумент до НАИБОЛЬШИЙ, используя ROW()-2 (-2, потому что мы хотим, чтобы второй аргумент начинаются с 1, а формула вводится в строке 3). Это дает нам упорядоченный массив значений:

    [9,5,1,0,0,0,0,0,0,0,0,0]

  • Мы передаем этот упорядоченный массив в качестве аргумента «смещения строки» функции OFFSET(), считая в обратном порядке от C2: все ненулевые значения дают нам значение, которое мы хотим получить из столбца C, тогда как все нулевые значения просто возвращают C2 (пустое, и будет отображаться как 0 без последнего & ""

  • Все ссылки на ячейки в формуле используют $ там, где это необходимо, поэтому при перетаскивании он корректируется по мере необходимости, чтобы заполнить вниз / поперек

person Tim Williams    schedule 26.05.2016
comment
Будь ты проклят, что ответил, пока я убирался! Хороший вариант! Я сделал то же самое, что и вы, с точки зрения порядка отображения при первом прогоне. Я думаю, что если вы используете МАЛЕНЬКИЙ вместо БОЛЬШОЙ, вы сохраните вертикальный порядок, в котором появляется информация. С большими вы просто работаете снизу вверх. - person Forward Ed; 26.05.2016
comment
DOH Я забыл, что у вас будут все эти ложные 0 с маленькими. Вчера столкнулся с этой проблемой! Не могли бы вы добавить COUNT($B$2:$B$13<>E$2) к ROW()-2, чтобы пропустить все 0, если вы выбрали МАЛЕНЬКУЮ версию формулы? - person Forward Ed; 26.05.2016
comment
@ForwardEd - я пытался заменить нули, но это только добавило сложности, без которой, как я думал, можно обойтись ... - person Tim Williams; 26.05.2016
comment
Я знал, что мне как-то нужно row, но не мог заставить его работать:/ Можете ли вы опубликовать формулу в виде текста вместо изображения, чтобы я мог скопировать/вставить, пожалуйста? - person findwindow; 26.05.2016
comment
Оооооооооооооооооооооооооооооооооооооооооооооооооооооооооооо это не нужно было ‹333 (я просто пошутил XD) - person findwindow; 26.05.2016
comment
Ахахахах приятно. Сейчас я на полпути к его изучению ^^ Редактировать: вы не объясняете, почему данные должны начинаться с B2, но я понял это =P Редактировать 2: Я знаю, что SO хочет объяснить ответы, но я этого не делаю. Лучше изучить его самостоятельно ^_^; - person findwindow; 26.05.2016
comment
Данные начинаются с B2, потому что именно там находятся данные? - person Tim Williams; 26.05.2016
comment
О, вы включаете границу XD На самом деле, я понял, что вы можете просто удалить/изменить математику в зависимости от местоположения ^_^; - person findwindow; 26.05.2016

arrow_upward
1
arrow_downward

Это только получает вас до сих пор. Вам все еще нужно удалить дубликаты. В 1_:

=INDEX($B1:$B$12,MATCH(D$1,$A1:$A$12,0))

введите здесь описание изображения

person findwindow    schedule 25.05.2016
comment
Не удалось заставить работать @scottcraner brilliance XD Редактировать: я думаю, что мне нужно sumproduct да ладно, пора домой. - person findwindow; 26.05.2016
comment
Я закончил, используя offset от B1 до B4: =OFFSET($B1;(ROW()-1)*3;0), затем автозаполнение вниз - person vedar; 26.05.2016
comment
@findwindow И вот я собирался поставить вам +1 за такую ​​компактную и эффективную формулу! - person Forward Ed; 26.05.2016
comment
@findwindow Поскольку на днях вы сказали, что вам трудно понять СУММПРОДУКТ, я подумал, что вы, возможно, захотите прочитать последняя половина ответа размещена здесь. - person Forward Ed; 26.05.2016
comment
Чувак, что мы говорили о недостаточном количестве скриншотов/слов XD - person findwindow; 26.05.2016

arrow_upward
0
arrow_downward

Мой ответ очень похож на ответ Тима, но он избегает части CSE и интернализует ее в функции AGGREGATE. Также поверьте, что он сохранит вертикальный порядок, в котором встречается элемент в B (хотя и не проверялся).

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

=INDEX($A$1:$A$12,MATCH(0,INDEX(COUNTIF($C1:C1,$A$1:$A$12),0,0),0))

Затем в D2 используйте следующее, чтобы извлечь информацию из столбца B. Ее можно скопировать вправо и вниз. Если элемент не найден, он будет отображаться как не найденный. вы можете изменить это на "", если хотите.

=IFERROR(INDEX($B$1:$B$12,AGGREGATE(15,6,ROW($A$1:$A$12)/($A$1:$A$12=D$1),ROW(1:1))),"Not found")

Доказательство концепции

Подтверждение концепции

Примечание о смещении

OFFSET — это изменчивая функция, означающая, что она будет пересчитывать каждый раз, когда что-либо изменяется на листе, а не только ячейки, с которыми она работает. Таким образом, если вы используете его всего пару раз, ничего страшного. Однако, если вы используете его неоднократно, вы можете столкнуться с замедлением работы вашего рабочего листа.

person Forward Ed    schedule 26.05.2016