Блог

Главная - Вопросы по программированию - Автоматическое создание строк на основе ограничения значений в Microsoft Excel

Автоматическое создание строк на основе ограничения значений в Microsoft Excel

#excel #vba

#excel #vba

Вопрос:

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

Например.

 Item    Quantity    MaxQtyPerCarton
A          12            5
B           6            3

Это должно дать следующие результаты при создании строк в Excel

 Item    CartonQuantity
A          5
A          5
A          2
B          3
B          3

Вы можете увидеть, что количество товара было разделено на три строки на основе MaxQtyPerCarton.
Кроме того, элемент B был разделен на две строки на основе MaxQtyPerCarton.

Есть идеи по этому поводу?

Комментарии:

1. Извините, в моем поиске я не видел, чтобы этот вопрос задавался раньше, поэтому я разместил здесь. Это все еще актуально для получения ответа?

Ответ №1:

Подход VBA (просто программирование)

 Option Explicit

Sub Sub1()
  Dim iRow1amp;, iRow2amp;, zItem$, zQuanamp;, zMaxQamp;, zAmtamp;
  iRow2 = 10 ' ??
  For iRow1 = 2 To 3 ' ??
     zItem = Cells(iRow1, 1)
     zQuan = Cells(iRow1, 2)
     zMaxQ = Cells(iRow1, 3)
     Do While zQuan > 0
       zAmt = zQuan
       If zAmt > zMaxQ Then zAmt = zMaxQ
       Cells(iRow2, 1) = zItem
       Cells(iRow2, 2) = zAmt
       iRow2 = iRow2   1
       zQuan = zQuan - zAmt
     Loop
  Next iRow1
End Sub

Ответ №2:

Это не очень элегантно, но оно выполняет свою работу. Для этого требуется, чтобы ваша первая структура таблицы (включая заголовки) в вашем вопросе была range("A1:C3") включена. И он выводит в столбцы E и F .

 Option Explicit

Sub FillCartons()
    Dim cll As Range
    Dim rng_Items As Range
    Dim rng_Quantity As Range
    Dim rng_MaxQty As Range
    Dim l_CartonCount As Long
    Dim l_AlreadyInCartons As Long

    Set rng_Items = Range(Range("A2"), Range("A1000000").End(xlUp))
    l_CartonCount = 1
    Range("E:F").ClearContents

    For Each cll In rng_Items
        Set rng_Quantity = cll.Offset(, 1)
        Set rng_MaxQty = cll.Offset(, 2)
        l_AlreadyInCartons = Application.WorksheetFunction.SumIf(Range("E:E"), cll.Value, Range("F:F"))

        Do Until l_AlreadyInCartons = rng_Quantity.Value
            If rng_Quantity.Value - l_AlreadyInCartons > rng_MaxQty.Value Then
                Cells(l_CartonCount, 5).Value = cll.Value
                Cells(l_CartonCount, 6).Value = rng_MaxQty.Value
            Else
                Cells(l_CartonCount, 5).Value = cll.Value
                Cells(l_CartonCount, 6).Value = rng_Quantity.Value - l_AlreadyInCartons
            End If
            l_CartonCount = l_CartonCount   1
            l_AlreadyInCartons = Application.WorksheetFunction.SumIf(Range("E:E"), cll.Value, Range("F:F"))
        Loop
    Next cll
End Sub

Ответ №3:

Предполагается, что у вас есть следующие настройки, и вы хотите, чтобы вывод начинался с10

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

Я использовал комбинацию Mod и Division, Division для повторения и Mod для остатков

 Sub create_rows()

Dim arr()
arr = Range("A2:C5")
Range("A10").Select

Dim j
    For j = LBound(arr, 1) To UBound(arr, 1)               

          Dim looper, z
          looper = arr(j, 2) / arr(j, 3) 'No of times to print
          Modder = arr(j, 2) Mod arr(j, 3) 'Leftovers


           For z = 1 To looper
            ActiveCell = arr(j, 1) 'Name of the quantity
            ActiveCell.Offset(0, 1) = arr(j, 3) 'always the max per qtn number
            ActiveCell.Offset(1, 0).Select
           Next z

           If (Modder > 0) Then 'there is leftover quantity 
            ActiveCell = arr(j, 1)
            ActiveCell.Offset(0, 1).Value = arr(j, 2) - arr(j, 3) * Round(looper, 0)
            ActiveCell.Offset(1, 0).Select
           End If

        Next j        

End Sub

Ответ №4:

Предполагая, что приведенная вами таблица находится в формате A1: C3 (с заголовками в строке 1), введите эту формулу массива ** в E2:

 =IFERROR(INDEX($A$2:$A$3,MATCH(TRUE,MMULT(0 (ROW($A$2:$A$3)>=TRANSPOSE(ROW($A$2:$A$3))),CEILING($B$2:$B$3/$C$2:$C$3,1))>=ROWS($1:1),0)),"")

Копируйте вниз, пока не начнете получать пробелы для результатов.

Затем эта (не массивная) формула в F2:

 =IF(E2="","",MIN(INDEX($C$2:$C$3,MATCH(E2,$A$2:$A$3,0)),INDEX($B$2:$B$3,MATCH(E2,$A$2:$A$3,0))-INDEX($C$2:$C$3,MATCH(E2,$A$2:$A$3,0))*(COUNTIF($E$2:$E2,E2)-1)))

Скопируйте по мере необходимости.

** Формулы массива вводятся не так, как «стандартные» формулы. Вместо того, чтобы нажимать просто ENTER, сначала удерживайте нажатой клавиши CTRL и SHIFT и только затем нажимайте ENTER. Если вы все сделали правильно, вы заметите, что Excel заключает формулу в фигурные скобки {} (хотя не пытайтесь вставлять их вручную самостоятельно).

Метки: