Расчет Суммеслимн из другой вкладки


enter image description here

Я хотел бы знать, если существует эффективный способ, чтобы переписать код, который я сделал. В основном он рассчитывает SumIfs из другой вкладки в зависимости от вкладки пользователь указывает в строке под номером 3, поэтому каждый раз что-то изменилось в строке Номер 3, то его следует пересчитать все снова.

Private Sub Worksheet_Change(ByVal Target As Range)

On Error Resume Next

Dim Amount As Range 'the range i want to sum
Dim ID As Range 'criteria range
Dim Arg3 As Variant 'the criteria
Dim TabToTake As String
Dim countAllActiveCell As Long
Dim CellsCnt As Range

Dim ActiveCol

If Not Intersect(Target, Range("A1:XFD3")) Is Nothing Then

ActiveCol = Left(ActiveCell.EntireColumn.Address(False, False), InStr(1, ActiveCell.EntireColumn.Address(False, False), ":") - 1)

TabToTake = ActiveCell.Value

Application.ScreenUpdating = False

  Set ID = Sheets(TabToTake).Range("A:A")
  Set Amount = Sheets(TabToTake).Range("Q:Q")

For Each CellsCnt In Range("E4:E2500").Cells
    If Not IsEmpty(CellsCnt) Then

        Cells(CellsCnt.Row, ActiveCol).Value = Application.WorksheetFunction.SumIfs(Amount, ID, Cells(CellsCnt.Row, "E").Value)

    End If
Next CellsCnt

Application.ScreenUpdating = True

    End If
End Sub


754
1
задан 1 марта 2018 в 04:03 Источник Поделиться
Комментарии
2 ответа

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


Ущербная структура. Надписьюworksheet_change

Здесь


ActiveCol = Left(ActiveCell.EntireColumn.Address(False, False), InStr(1, ActiveCell.EntireColumn.Address(False, False), ":") - 1)

Работает только на листе меняться, так что если вы не идете прямо к следующей ячейке (по вертикали), вы получаете неверный столбец. Попробовать это -

ActiveCol = Left(ActiveCell.EntireColumn.Address(False, False), InStr(1, ActiveCell.EntireColumn.Address(False, False), ":") - 1)
MsgBox ActiveCol

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

ActiveCol = Target.Column
MsgBox ActiveCol

Это работает на вашу цель, что изменилось, не там, где курсор оказался. Маленький, но важный.

Это также важно для вашего TabToTake используя и activecell, вы уверены, что хотите сделать следующий ячейку значение, которое в лучшем случае, появится в строке 4?

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

If Not Intersect(Target, Rows(3)) Is Nothing Then
targetSheet = Sheets(Target.Value)
activeColumn = Target.Column


Вы не накопили все -


For Each CellsCnt In Range("E4:E2500").Cells

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


Давайте разберем мясо из ваших суб

Set ID = Sheets(TabToTake).Range("A:A")
Set Amount = Sheets(TabToTake).Range("Q:Q")
For Each CellsCnt In Range("E4:E2500").Cells
If Not IsEmpty(CellsCnt) Then
Cells(CellsCnt.Row, ActiveCol).Value = Application.WorksheetFunction.SumIfs(Amount, ID, Cells(CellsCnt.Row, "E").Value)
End If
Next CellsCnt

Во-первых, для каждой ячейки в столбце E на targetSheet, если он не пуст, то

targetSheet, этой ячейки, в ActiveCol (что не target) =

Application.WorksheetFunction.SumIfs(targetSheet.Range("Q:Q"), targetSheet.Range("A:A"), ActiveSheet.Cells(CellsCnt.Row, 5)).Value

Так что мы здесь делаем, точно.


Суммируя все targetSheet.Столбцы("Г"), если все targetSheet.Колонок("а") = текущей ячейки. Для всего столбца E.

Предполагая, что многие из столбца е меняется, когда что-то в строке 3 изменений, вы по сути делаете сумму vlookups да? Тю, зачем так много? Не зная, как ваши клетки соединилась в свой лист, делает его более трудным, чтобы действительно оптимизировать это, но сейчас попробую

Const LOOKUP_COLUMN As Long = 5
Const LOOK_IN_COLUMN As Long = 1
Const SUM_COLUMN As Long = 17
Dim lookupVector As Variant
Dim lookinVector As Variant
Dim sumVector As Variant
Dim lastRow As Long

lastRow = ActiveSheet.Cells(Rows.Count, LOOKUP_COLUMN).End(xlUp).Row
lookupVector = ActiveSheet.Range(Cells(4, LOOKUP_COLUMN), Cells(lastRow, LOOKUP_COLUMN))

lastRow = targetSheet.Cells(Rows.Count, LOOK_IN_COLUMN).End(xlUp).Row
lookinVector = targetSheet.Range(Cells(1, LOOK_IN_COLUMN), Cells(lastRow, LOOK_IN_COLUMN))
sumVector = targetSheet.Range(Cells(1, SUM_COLUMN), Cells(lastRow, SUM_COLUMN))

Сейчас все в массивы и вы можете выполнять расчеты за кулисами, гораздо быстрее

Dim resultVector() As Double
Dim result As Double
ReDim resultVector(LBound(lookupVector) To UBound(lookupVector))
Dim index As Long
Dim targetIndex As Long
For index = LBound(lookupVector) To UBound(lookupVector)
result = 0
For targetIndex = LBound(lookinVector) To UBound(lookinVector)
If lookinVector(index) = lookupVector(index) Then result = result + sumVector(targetIndex)
Next
resultVector(index) = result
Next

Как только вы закончите, вы просто положить, что resultVector обратно в свой lookup_column.


Переменные

Давать переменным осмысленные имена. Когда я вижу Amount в коде, я не предполагаю, что спектр я предполагаю, что это значение. Ясно, легче на будущее вас. Вы можете увидеть мои примеры изменения в приведенный выше код.

Стандартные соглашения об именах в VBA есть camelCase для локальных переменных и PascalCase для других переменных и имена.


Dim ActiveCol

Вы не дали этой переменной типа поэтому он был объявлен как вариант. Вам не нужен вариант, просто long.


Отступы

Это хорошая практика для отступа кода этак Labels будет торчать как очевидное.


Обработка Ошибок

Это


On Error Resume Next

Какая ошибка обработки? Что может пойти не так? Например, когда вы используете выбор, а не цель, вы не видите, что это не делать то, что он должен был делать. Ручки ваши ошибки специально, чтобы вы знали, что может пойти не так, как оно идет не так, и как это предотвратить.


Работа на листе

Работа с диапазонами вместо массивов - это просто замедляет код по необходимости возиться с электронными таблицами, а делать все остальное за кадром. Есть хороший вопрос на StackOverflow решение этой. Такая же логика применяется здесь.


Комментарии

Комментарии - "код сказать вам, как, комментарии, скажу вам, почему". Код должен говорить сам за себя, если он нуждается в комментариях, его нужно сделать более четким. Если нет, то комментарий должен описывать , почему вы делаете что-то, а не как ты делаешь это. Вот несколько причин , чтобы избежать замечаний все вместе.


Dim Amount As Range 'the range i want to sum
Dim ID As Range 'criteria range
Dim Arg3 As Variant 'the criteria

Вы говорите мне в комментариях, какие диапазоны делать то, что было первым признаком того, что ваш именования можно улучшить, верно?


Способ

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

Честно, возможно, ваш лучший вариант на самом деле использует SUMIFS функция, как

 =IFERROR("",SUMIFS(Sheet1!A1:A31,Sheet1!Q1:Q25,Sheet2!E7))

1
ответ дан 1 марта 2018 в 10:03 Источник Поделиться

спасибо большое за такой длинный ответ,
что я в основном ищу что:
У меня есть несколько вкладок с данными, каждая вкладка в ту же структуру,
допустим, столбец a-это номер счета , столбец B столбец месяц (янв , фев и т. д.) c число.

теперь в главном разделе, Как вы можете видеть в столбце " пик " е содержать номера счетов и столбце G до R такие,строка под номером 1-это месяц для суммеслимн критерии количество строк 3 вкладка название для извлечения данных из.
теперь я хочу, чтобы когда пользователь изменить название вкладки в строке 3 будет суммеслимн на этой вкладке В все столбце также, когда пользователь изменить номер счета в столбце E будет меняться все строки в этот счет на вкладке в строке Номер 3.

что вы скажете?

main tab

0
ответ дан 7 марта 2018 в 10:03 Источник Поделиться