Управление данными Excel VBA

Question

Управление данными Excel VBA

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

Я не знаю, как написать таблицу здесь, поэтому я вставляю скриншот вместо этого.

Источник данных

Вывод данных

Я хотел бы написать код VBA для автоматического расчета количества определенных категорий (например, A минус C) в соответствии с их датой и страной и создать таблицу, подобную второму снимку экрана.

Я попытался написать точную ссылку для каждого столбца выходных данных, но это слишком неуклюже, и больше источников могут добавить к источнику данных, и точная ссылка будет недействительной.

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

Я все еще новичок на VBA.

Sub Test()
    Dim ShName As String
    Dim i As Integer
    Dim Number As Integer

    Number = Worksheets("MgrSummary").Range("D2").Value
    Worksheets("MgrSummary").Range("C3").Value = Worksheets("MgrFull").Range("Y1").Value    'transfer the necessary data from MgrFull
    Worksheets("MgrSummary").Range("D3").Value = Worksheets("MgrFull").Range("Y2").Value
    Worksheets("MgrSummary").Range("D2").Value = (Year(Worksheets("MgrSummary").Range("D3").Value) - 2011) * 12 + Month(Worksheets("MgrSummary").Range("D3").Value) + 5
    Number = Worksheets("MgrSummary").Range("D2").Value

    Cells(Number - 36, 4).Value = Number              'Month number
    Cells(Number - 37, 4).Select
    ActiveCell.FormulaR1C1 = "=IF(R[1]C="""","""",IF(R[1]C-1<=5,"""",R[1]C-1))"
    ActiveCell.AutoFill Range(ActiveCell.Address, Cells(5, 4))

    Cells(Number - 36, 5) = Cells(3, 4).Value         'month and year
    Cells(Number - 37, 5).Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-1]="""","""",EOMONTH(R[1]C,-1))"
    ActiveCell.AutoFill Range(ActiveCell.Address, Cells(5, 5))

    Cells(Number - 36, 2) = Cells(3, 3).Value         'Ticker
    Cells(Number - 37, 2).Select
    ActiveCell.FormulaR1C1 = "=R[1]C"
    ActiveCell.AutoFill Range(ActiveCell.Address, Cells(5, 2))

    ShName = ThisWorkbook.Worksheets("MgrSummary").Range("C3").Value

    For i = 0 To Number - 41                          'for loop
        Worksheets("MgrSummary").Cells(Number - 36 - i, 8).Formula = Worksheets(ShName).Cells(21, Number + 1 - i) - Worksheets(ShName).Cells(24, 1 + Number - i)    'position attribution
        Worksheets("MgrSummary").Cells(Number - 36 - i, 9).Formula = Worksheets(ShName).Cells(19, Number + 1 - i)    'No. of postion(L-S)
        Worksheets("MgrSummary").Cells(Number - 36 - i, 10).Formula = Worksheets(ShName).Cells(22, Number + 1 - i)
        Worksheets("MgrSummary").Cells(Number - 36 - i, 11).Formula = Worksheets(ShName).Cells(19, Number + 1 - i) + Worksheets(ShName).Cells(22, Number + 1 - i)
        Worksheets("MgrSummary").Cells(Number - 36 - i, 12).Formula = Worksheets(ShName).Cells(19, Number + 1 - i)
        Worksheets("MgrSummary").Cells(Number - 36 - i, 13).Formula = Worksheets(ShName).Cells(22, Number + 1 - i)
        Worksheets("MgrSummary").Cells(Number - 36 - i, 14).Formula = Worksheets(ShName).Cells(19, Number + 1 - i) + Worksheets(ShName).Cells(22, Number + 1 - i)


        Worksheets("MgrSummary").Cells(Number - 36 - i, 15).Formula = Worksheets(ShName).Cells(56, Number + 1 - i) - Worksheets(ShName).Cells(119, Number + 1 - i)    'Gross Country Exposure
        Worksheets("MgrSummary").Cells(Number - 36 - i, 16).Formula = Worksheets(ShName).Cells(57, Number + 1 - i) - Worksheets(ShName).Cells(120, Number + 1 - i)
        Worksheets("MgrSummary").Cells(Number - 36 - i, 17).Formula = Worksheets(ShName).Cells(58, Number + 1 - i) + Worksheets(ShName).Cells(59, Number + 1 - i) - Worksheets(ShName).Cells(121, Number + 1 - i) - Worksheets(ShName).Cells(122, Number + 1 - i)
        Worksheets("MgrSummary").Cells(Number - 36 - i, 18).Formula = Worksheets(ShName).Cells(60, Number + 1 - i) + Worksheets(ShName).Cells(66, Number + 1 - i) - Worksheets(ShName).Cells(123, Number + 1 - i) - Worksheets(ShName).Cells(129, Number + 1 - i)
        Worksheets("MgrSummary").Cells(Number - 36 - i, 19).Formula = Worksheets(ShName).Cells(61, Number + 1 - i) - Worksheets(ShName).Cells(124, Number + 1 - i)
        Worksheets("MgrSummary").Cells(Number - 36 - i, 20).Formula = Worksheets(ShName).Cells(63, Number + 1 - i) - Worksheets(ShName).Cells(126, Number + 1 - i)
        Worksheets("MgrSummary").Cells(Number - 36 - i, 21).Formula = Worksheets(ShName).Cells(64, Number + 1 - i) - Worksheets(ShName).Cells(127, Number + 1 - i)
        Worksheets("MgrSummary").Cells(Number - 36 - i, 22).Formula = Worksheets(ShName).Cells(70, Number + 1 - i) - Worksheets(ShName).Cells(133, Number + 1 - i)
        Worksheets("MgrSummary").Cells(Number - 36 - i, 23).Formula = Worksheets(ShName).Cells(62, Number + 1 - i) + Worksheets(ShName).Cells(68, Number + 1 - i) + Worksheets(ShName).Cells(67, Number + 1 - i) + Worksheets(ShName).Cells(68, Number + 1 - i) + Worksheets(ShName).Cells(71, Number + 1 - i) + Worksheets(ShName).Cells(72, Number + 1 - i) + Worksheets(ShName).Cells(69, Number + 1 - i) - Worksheets(ShName).Cells(125, Number + 1 - i) - Worksheets(ShName).Cells(131, Number + 1 - i) - Worksheets(ShName).Cells(132, Number + 1 - i) - Worksheets(ShName).Cells(130, Number + 1 - i) - Worksheets(ShName).Cells(128, Number + 1 - i) - Worksheets(ShName).Cells(134, Number + 1 - i) - Worksheets(ShName).Cells(135, Number + 1 - i) - Worksheets(ShName).Cells(128, Number + 1 - i)
        Worksheets("MgrSummary").Cells(Number - 36 - i, 24).Formula = Worksheets(ShName).Cells(73, Number + 1 - i) + Worksheets(ShName).Cells(74, Number + 1 - i) - Worksheets(ShName).Cells(136, Number + 1 - i) - Worksheets(ShName).Cells(137, Number + 1 - i)
        Worksheets("MgrSummary").Cells(Number - 36 - i, 25).Formula = Application.WorksheetFunction.Sum(Range(Cells(Number - 36 - i, 24), Cells(Number - 36 - i, 15)))

        Worksheets("MgrSummary").Cells(Number - 36 - i, 26).Formula = Worksheets(ShName).Cells(56, Number + 1 - i) + Worksheets(ShName).Cells(119, Number + 1 - i)    'Net country exposure
        Worksheets("MgrSummary").Cells(Number - 36 - i, 27).Formula = Worksheets(ShName).Cells(57, Number + 1 - i) + Worksheets(ShName).Cells(120, Number + 1 - i)
        Worksheets("MgrSummary").Cells(Number - 36 - i, 28).Formula = Worksheets(ShName).Cells(58, Number + 1 - i) + Worksheets(ShName).Cells(59, Number + 1 - i) + Worksheets(ShName).Cells(121, Number + 1 - i) + Worksheets(ShName).Cells(122, Number + 1 - i)
        Worksheets("MgrSummary").Cells(Number - 36 - i, 29).Formula = Worksheets(ShName).Cells(60, Number + 1 - i) + Worksheets(ShName).Cells(66, Number + 1 - i) + Worksheets(ShName).Cells(123, Number + 1 - i) + Worksheets(ShName).Cells(129, Number + 1 - i)
        Worksheets("MgrSummary").Cells(Number - 36 - i, 30).Formula = Worksheets(ShName).Cells(61, Number + 1 - i) + Worksheets(ShName).Cells(124, Number + 1 - i)
        Worksheets("MgrSummary").Cells(Number - 36 - i, 31).Formula = Worksheets(ShName).Cells(63, Number + 1 - i) + Worksheets(ShName).Cells(126, Number + 1 - i)
        Worksheets("MgrSummary").Cells(Number - 36 - i, 32).Formula = Worksheets(ShName).Cells(64, Number + 1 - i) + Worksheets(ShName).Cells(127, Number + 1 - i)
        Worksheets("MgrSummary").Cells(Number - 36 - i, 33).Formula = Worksheets(ShName).Cells(70, Number + 1 - i) + Worksheets(ShName).Cells(133, Number + 1 - i)
        Worksheets("MgrSummary").Cells(Number - 36 - i, 34).Formula = Worksheets(ShName).Cells(62, Number + 1 - i) + Worksheets(ShName).Cells(67, Number + 1 - i) + Worksheets(ShName).Cells(65, Number + 1 - i) + Worksheets(ShName).Cells(68, Number + 1 - i) + Worksheets(ShName).Cells(69, Number + 1 - i) + Worksheets(ShName).Cells(72, Number + 1 - i) + Worksheets(ShName).Cells(71, Number + 1 - i) + Worksheets(ShName).Cells(125, Number + 1 - i) + Worksheets(ShName).Cells(131, Number + 1 - i) + Worksheets(ShName).Cells(132, Number + 1 - i) + Worksheets(ShName).Cells(130, Number + 1 - i) + Worksheets(ShName).Cells(134, Number + 1 - i) + Worksheets(ShName).Cells(135, Number + 1 - i) + Worksheets(ShName).Cells(128, Number + 1 - i)
        Worksheets("MgrSummary").Cells(Number - 36 - i, 35).Formula = Worksheets(ShName).Cells(73, Number + 1 - i) + Worksheets(ShName).Cells(74, Number + 1 - i) + Worksheets(ShName).Cells(136, Number + 1 - i) + Worksheets(ShName).Cells(137, Number + 1 - i)
        Worksheets("MgrSummary").Cells(Number - 36 - i, 36).Formula = Application.WorksheetFunction.Sum(Range(Cells(Number - 36 - i, 26), Cells(Number - 36 - i, 36)))

        Worksheets("MgrSummary").Cells(Number - 36 - i, 37).Formula = Worksheets(ShName).Cells(77, Number + 1 - i) + Worksheets(ShName).Cells(140, Number + 1 - i)    'country exposure(attribution)
        Worksheets("MgrSummary").Cells(Number - 36 - i, 38).Formula = Worksheets(ShName).Cells(78, Number + 1 - i) + Worksheets(ShName).Cells(141, Number + 1 - i)
        Worksheets("MgrSummary").Cells(Number - 36 - i, 39).Formula = Worksheets(ShName).Cells(79, Number + 1 - i) + Worksheets(ShName).Cells(80, Number + 1 - i) + Worksheets(ShName).Cells(142, Number + 1 - i) + Worksheets(ShName).Cells(143, Number + 1 - i)
        Worksheets("MgrSummary").Cells(Number - 36 - i, 40).Formula = Worksheets(ShName).Cells(81, Number + 1 - i) + Worksheets(ShName).Cells(87, Number + 1 - i) + Worksheets(ShName).Cells(144, Number + 1 - i) + Worksheets(ShName).Cells(150, Number + 1 - i)
        Worksheets("MgrSummary").Cells(Number - 36 - i, 41).Formula = Worksheets(ShName).Cells(82, Number + 1 - i) + Worksheets(ShName).Cells(145, Number + 1 - i)
        Worksheets("MgrSummary").Cells(Number - 36 - i, 42).Formula = Worksheets(ShName).Cells(84, Number + 1 - i) + Worksheets(ShName).Cells(147, Number + 1 - i)
        Worksheets("MgrSummary").Cells(Number - 36 - i, 43).Formula = Worksheets(ShName).Cells(85, Number + 1 - i) + Worksheets(ShName).Cells(148, Number + 1 - i)
        Worksheets("MgrSummary").Cells(Number - 36 - i, 44).Formula = Worksheets(ShName).Cells(91, Number + 1 - i) + Worksheets(ShName).Cells(154, Number + 1 - i)
        Worksheets("MgrSummary").Cells(Number - 36 - i, 45).Formula = Worksheets(ShName).Cells(83, Number + 1 - i) + Worksheets(ShName).Cells(88, Number + 1 - i) + Worksheets(ShName).Cells(86, Number + 1 - i) + Worksheets(ShName).Cells(89, Number + 1 - i) + Worksheets(ShName).Cells(90, Number + 1 - i) + Worksheets(ShName).Cells(92, Number + 1 - i) + Worksheets(ShName).Cells(93, Number + 1 - i) + Worksheets(ShName).Cells(146, Number + 1 - i) + Worksheets(ShName).Cells(149, Number + 1 - i) + Worksheets(ShName).Cells(151, Number + 1 - i) + Worksheets(ShName).Cells(152, Number + 1 - i) + Worksheets(ShName).Cells(153, Number + 1 - i) + Worksheets(ShName).Cells(155, Number + 1 - i) + Worksheets(ShName).Cells(156, Number + 1 - i)
        Worksheets("MgrSummary").Cells(Number - 36 - i, 46).Formula = Worksheets(ShName).Cells(94, Number + 1 - i) + Worksheets(ShName).Cells(95, Number + 1 - i) + Worksheets(ShName).Cells(136, Number + 1 - i) + Worksheets(ShName).Cells(137, Number + 1 - i)
        Worksheets("MgrSummary").Cells(Number - 36 - i, 47).Formula = Application.WorksheetFunction.Sum(Range(Cells(Number - 36 - i, 37), Cells(Number - 36 - i, 47)))

    Next i
End Sub

1

excel vba excel-vba for-loop data-management

Источник

user9793284 21 май '18 в 01:56

2 ответа

Другие вопросы по тегам excel vba excel-vba for-loop data-management

user9633414 21 май '18 в 02:44 2018-05-21 02:44 · Answer 1 · 2018-05-21 02:44

Я думаю, что было бы намного проще использовать формулу, чтобы получить ожидаемый результат:

Лист1 это:

Лист 2 и формула:

=SUMIFS(OFFSET(Sheet1!$B$2:$B$18,0,MATCH(Sheet2!$A2,Sheet1!$C$1:$O$1,0)),Sheet1!$A$2:$A$18,"A",Sheet1!$B$2:$B$18,MID(Sheet2!B$1,FIND(" ",Sheet2!B$1)+1,LEN(Sheet2!B$1)))-SUMIFS(OFFSET(Sheet1!$B$2:$B$18,0,MATCH(Sheet2!$A2,Sheet1!$C$1:$O$1,0)),Sheet1!$A$2:$A$18,"C",Sheet1!$B$2:$B$18,MID(Sheet2!B$1,FIND(" ",Sheet2!B$1)+1,LEN(Sheet2!B$1)))

Редактировать для различных источников данных:

Лист1 это:

Лист2 это:

Лист 3 - это:

Формула это:

=SUMIFS(OFFSET(INDIRECT("Sheet"&$G$1&"!$B$2:$B$18"),0,MATCH(Sheet2!$A2,INDIRECT("Sheet"&$G$1&"!$C$1:$O$1"),0)),INDIRECT("Sheet"&$G$1&"!$A$2:$A$18"),"A",INDIRECT("Sheet"&$G$1&"!$B$2:$B$18"),MID(Sheet2!B$1,FIND(" ",Sheet2!B$1)+1,LEN(Sheet2!B$1)))-SUMIFS(OFFSET(INDIRECT("Sheet"&$G$1&"!$B$2:$B$18"),0,MATCH(Sheet2!$A2,INDIRECT("Sheet"&$G$1&"!$C$1:$O$1"),0)),INDIRECT("Sheet"&$G$1&"!$A$2:$A$18"),"C",INDIRECT("Sheet"&$G$1&"!$B$2:$B$18"),MID(Sheet2!B$1,FIND(" ",Sheet2!B$1)+1,LEN(Sheet2!B$1)))

Номер листа будет выбран в ячейке sheet2 G1. Вы можете слегка изменить формулу, если вы используете разные имена листов (например, INDIRECT("Лист"&$G$1&"!$A$2:$A$18") станет INDIRECT($G$1&"!$A$2:$A$18")).

Проблема с этой формулой в том, что она не легко масштабируется, и вы теряете относительную ссылку.

user6241235 21 май '18 в 10:07 2018-05-21 10:07 · Answer 2 · 2018-05-21 10:07

Я думаю, что вы можете легко сделать это с помощью PowerQuery (встроенная функция Get&Transform на вкладке данных 2016 г., бесплатная надстройка для 2013 г. на вкладке Microsoft > Powerquery).

Замечания:

Я добавил заголовки для столбца А из Category и столбец B Country
Я предполагаю, что вы имеете в виду A - C как вычитание, но легко изменить иначе, используя операции группировки и суммирования. Дай мне знать.

1) Выберите ячейку в вашем диапазоне данных и перейдите на вкладку PowerQuery или вкладку данных (2016) и выберите данные> из таблицы

2) Выберите Category столбец> выберите заголовок справа внизу стрелка вниз> удалить пустой

3) Выберите все столбцы месяца (выберите сначала, удерживайте нажатой кнопку Shift, выберите последний), затем щелкните правой кнопкой мыши> снять столбцы

4) Выберите Category столбец и перейдите к Transform > Pivot Column

Выбор значения в качестве значения столбца

5) Необязательно: выберите Атрибут, нажмите на значок слева и измените тип данных для столбца на "Дата". Затем вы можете отсортировать этот столбец по возрастанию.

[![Date][7]][7]

6) Перейти к Добавить столбец> И дать пример имени AminusC и формулу, например,

=[A]-[C]

Повторите для любых других расчетов, которые вы хотите.

7) Выберите столбцы A,B,C и щелкните правой кнопкой мыши удалить столбцы

8) Опционально выберите Country столбец и Преобразовать> столбец Сводка> столбец значений = AminusC

9) Главная> Закрыть и загрузить на лист

10) Наблюдайте за результатом (обратите внимание, что вы можете изменить имена столбцов между ними, чтобы они соответствовали желаемым заголовкам столбцов и чтобы различать вычисления. Это должно было показать вам метод.