Превратить одну строку в несколько строк в Excel

Question

У меня есть несколько предметов, которые доступны в нескольких распределительных центрах (т. Е. Отношение многих ко многим). В настоящее время существует одна строка для каждого элемента с одним столбцом для каждого центра распространения. Ячейка в строке для элемента X и столбца для центра распределения Y помечается кодом для центра распределения Y, если там имеется элемент X , и не указывается в противном случае. Элемент с несколькими центрами распространения будет иметь несколько кодов центров распределения (в соответствующих столбцах). Таким образом, текущий лист выглядит так:

    |   A    |         B         |*|                      S-AJ                      |
1   |  ID #  |   Description     |…|              Distribution Centers              |
2   |   17   |   Ginkgo Biloba   |…|      |      |      |      |      |      |  SE  |
3   |   42   |   Ginseng         |…|      |  MP  |  MS  |      |  NW  |      |      |
                     ︙

Столбцы с C по R содержат другие атрибуты предметов, такие как код UPC, стоимость и цена, которые не относятся к этому вопросу. У моего фактического листа есть 18 распределительных центров, охватывающих столбцы от S до AJ ; Я уменьшил это, чтобы пример вписался в окно Stack Exchange.

Мне нужно иметь один столбец центра распространения, с одним кодом распределения на строку, а затем дублировать строки по мере необходимости для элементов, которые в настоящее время содержат несколько кодов. Результат должен выглядеть так:

    |   A    |         B         |*|   S  |
1   |  ID #  |   Description     |…|  DC  |
2   |   17   |   Ginkgo Biloba   |…|  SE  |
3   |   42   |   Ginseng         |…|  MP  |
4   |   42   |   Ginseng         |…|  MS  |
5   |   42   |   Ginseng         |…|  NW  |
                     ︙

где ячейки A3:R3 , A4:R4 и A5:R5 содержат одинаковую информацию.

Единственный способ сделать это, что потребовало бы много времени, - это скопировать номер элемента в несколько строк; и в столбце, который имеет код распространения, я бы изменил код для элемента, доступного в каждом центре распространения. Я буду делать это для 900 предметов. Есть ли более простой способ сделать это?

Answer 1

1. Создайте новый лист.  Скопируйте строки заголовка, ширину столбцов и форматы, кроме столбцов T AJ . Проще всего скопировать весь лист, затем удалить все строки, кроме 1, и удалить столбцы S - AJ .

2. Во- первых , мы хотим повторить каждый пункт строки из Sheet1 Sheet2 на 18 раз - один раз для каждого распределительного центра.  Тип =INDEX(Sheet1!A:A, INT((ROW()-2)/18)+2, 1) & "" в Sheet2!A2 .  INT((ROW()-2)/18)+2 отображает строки 2-19 на Sheet2 2 в строку 2 на Sheet1 , строки 20-37 на Sheet2 в строку 3 на Sheet1 и т.д. Символ " & "" заставляет Excel отображать пусто при ссылке на пустую ячейку в Sheet1 .  Если у вас нет бланков в Sheet1 , вы можете оставить это.  Если вам не нравится это конкретное решение, вы можете использовать одно из других решений из Display Blank при ссылке на пустую ячейку в Excel .

   Перетащите / заполните это право, к ячейке R2 .

3. Введите =INDEX(Sheet1!$S:$AJ, INT((ROW()-2)/18)+2, MOD(ROW()-2, 18)+1) в Sheet2!S2 .  Это ссылается на ту же строку в Sheet1 что и приведенная выше формула, но Sheet2!S2 получает значение из Sheet1!S2 , Sheet2!S3 получает значение из Sheet1!T2 , Sheet2!S4 получает значение из Sheet1!U2 и т.д. Для пробелов будет отображаться 0 с.
4. Выберите всю строку A2:S2 и перетащите / заполните вниз, чтобы получить все ваши данные.  Это должно быть в 18 раз больше строк, чем у вас на Sheet1 ; т.е. 18 × 900 = 16200.
5. Скопируйте все Sheet2 и вставьте значения.
6. Фильтр Колонка S Отображать только нули.  Удалить все строки (кроме строки 1).  Снимите фильтр.

Готово.