Как разделить одну строку на несколько строк в Excel?

Question

У меня есть база данных продуктов в Excel с несколькими сотнями записей, каждая из которых имеет от 1 до 3 "уровней" ценообразования: Standard, Deluxe и Premium. Каждый уровень имеет свой собственный SKU (A, B или C добавляется в конец базового SKU) и цену. Мои данные таковы:

Name, Description, Price A, Price B, Price C, SKU A, SKU B, SKU C
name1,      desc1,   14.95,   19.95,        , sku1A, sku1B, 
name2,      desc2,    4.95,    9.95,   12.95, sku2A, sku2B, sku2C
name3,      desc3,   49.95,        ,        , sku3A,      ,

Как мне сделать так, чтобы данные выглядели так:

Name, Description,   SKU, Price
name1,      desc1, sku1A, 14.95
name1,      desc1, sku1B, 19.95
name2,      desc2, sku2A,  4.95
name2,      desc2, sku2B,  9.95
name2,      desc2, sku2C, 12.95
name3,      desc3, sku3A, 49.95

Если это поможет, я собираюсь импортировать эти продукты в установку Magento.

Заранее благодарю.

Answer 1

Эти задачи обычно быстрее с VBA. На самом деле это заняло у меня ~ 10 минут.
Я предполагаю, что ваши данные от столбца A до столбца H.

Перейдите в Excel » Developer » Visual Basic »На левой панели откройте sheet1 (или) лист с вашими данными» Вставьте код в правильное окно »Запустите код

Код VBA

1 |Sub NewLayout()
2 |    For i = 2 To Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
3 |        For j = 0 To 2
4 |        If Cells(i, 3 + j) <> vbNullString Then
5 |            intCount = intCount + 1
6 |            Cells(i, 1).Copy Destination:=Cells(intCount, 10)
7 |            Cells(i, 2).Copy Destination:=Cells(intCount, 11)
8 |            Cells(i, 3 + j).Copy Destination:=Cells(intCount, 12)
9 |            Cells(i, 6 + j).Copy Destination:=Cells(intCount, 13)
10|        End If
11|        Next j
12|    Next i
13|End Sub

объяснение

Я хотел, чтобы код был как можно короче, чтобы объяснить его лучше. В основном мы используем две петли. Внешний цикл (i) предназначен для строк, а внутренний цикл (j) - для столбцов цен.

Мы интенсивно используем cells(rowNumber,columnNumber) для чтения / записи ячеек.

• Линия 2 | Начните цикл со строки 2 до вашего последнего ряда. Перебираем каждую использованную строку

• Линия 3 | Начните второй цикл с 0 до 2 (это фактически 3 цикла, по одному на каждый столбец Price)

• Линия 4 | Мы используем этот внутренний цикл для проверки значений в нашей текущей строке и столбце Price A, затем Price B и в последнем цикле Price C. Если мы находим значение в столбце Price, мы продолжаем и копируем ячейки. Если цена не указана, мы ничего не делаем и переходим к следующему столбцу

• Строка 5 | Подсчитайте счетчик, чтобы узнать, сколько строк мы уже скопировали,
  поэтому мы знаем, после какой строки мы можем скопировать нашу текущую строку

• Строка 6 | Скопируйте имя столбца

• Линия 7 | Скопируйте описание столбца

• Линия 8 | Скопируйте столбец Цена A или B или C в зависимости от того, какой внутренний цикл мы в данный момент

• Линия 9 | Скопируйте SKU столбец A или B или C в зависимости от того, какой внутренний цикл мы в настоящее время

Скриншот результата

Answer 2

Вот решение функции листа. Формулы немного плотные, так что будьте осторожны, но это даст то, что вы хотите.

шаги:

1. В первой строке вашей новой таблицы в столбце Name введите прямую ссылку на первое Name в ваших данных. В вашем примере вы должны ввести =A2 где A2 - имя, указанное в ваших данных. На приведенном ниже примере снимка экрана эта формула представлена в A8 . Все следующие формулы будут соответствовать макету, использованному на скриншоте. Вам, конечно, придется обновить все ссылки на диапазоны, чтобы они соответствовали вашим листам.
2. В ячейку под этим введите следующую формулу:

   = ЕСЛИ (СЧЕТЕСЛИ ($ A $ 9: А9, А9)= СЧЕТЗ (OFFSET ($ C $ 1: $ E $ 1, MATCH (А9, $ A $ 2: $ A $ 5,0), 0)), ИНДЕКС ($ A $ 2: $ A $ 5, MATCH (А9, $ A $ 2: $ A $ 5,0)+1), А9)
   

   Это в основном проверяет, сколько строк должно быть для имени, указанного выше (в A9), и, если количество строк в вашей новой таблице соответствует этому, то оно переходит к следующему имени. Если нет, будет добавлена еще одна строка для указанного выше имени.
   Заполните эту формулу настолько, насколько вам нужно (пока она не вернет 0 вместо имени).
3. В первой строке под Description введите следующую формулу и заполните.

   = ИНДЕКС ($ B $ 2: $ B $ 5, MATCH (А9, $ A $ 2: $ A $ 5,0))

4. В первой строке под SKU вставьте следующую формулу в строку формул и нажмите Ctrl+Shift+Enter .

   = ИНДЕКС (OFFSET ($ A $ 1: $ H $ 1, MATCH (A9, $ A $ 2: $ A $ 5,0), 0), малый (ЕСЛИ (OFFSET ($ F $ 1: $ H $ 1, MATCH (A9, $ A $ 2: $ A $ 5,0), 0)<> "", Column ($ F $ 1: $ H $ 1)), СЧЕТЕСЛИ ($ A $ 9: $ А9, $ А9)))

   Это формула массива; если введено правильно, формула появится в строке формул в фигурных скобках. Заполните эту формулу в таблице (каждый экземпляр должен также появляться в фигурных скобках).
5. Аналогично, в первой строке в разделе « Price вставьте следующую формулу в строку формул и введите ее в виде формулы массива (нажав клавиши Ctrl+Shift+Enter).

   = ИНДЕКС (OFFSET ($ A $ 1: $ H $ 1, MATCH ($ А9, $ A $ 2: $ A $ 5,0), 0), малый (ЕСЛИ (OFFSET ($ C $ 1: $ E $ 1, ПОИСКПОЗ ($ A9 $ A $ 2: $ A $ 5,0), 0)<> "", Column ($ C $ 1: $ E $ 1)), СЧЕТЕСЛИ ($ A $ 9: $ А9, $ А9)))

   Заполните, и это должно заполнить вашу таблицу.