Вставить строки в лист из другой книги?

Question

У меня есть две книги, скажем: workbook_a.xlsx и workbook_b.xlsx, и обе содержат разные таблицы. Два из этих рабочих листов уже связаны парой ВПР формул с помощью уникального ключа , который является столбец CustID. С помощью этой настройки я могу позволить пользователям вводить данные в workbook_a, а затем я могу запустить все свои BI на workbook_b. Теперь, одна из самых утомительных вещей, которые я делаю, - это поиск новых клиентов с высоким риском в workbook_a, чтобы я мог вручную добавить их в workbook_b. Я хотел бы сделать следующее:

1. Из workbook_a поиска CustID в workbook_b.
2. Если она существует, ничего не делать (я уже добавил клиента и формулу ВПР будет заботиться об обновлении значения cust_risk клеток).
3. Если он не существует, скопируйте строку из workbook_a в workbook_b (возможно, даже не всю строку, а только одну ячейку?).

Это возможно? Я использую Excel 2007.

Answer 1

Если вам интересно, другой подход заключается в том, чтобы сначала настроить динамический именованный диапазон в workbook_a, который охватывает область, в которой вы разрешаете пользователям вводить данные. Затем используйте этот динамический именованный диапазон в качестве источника для сводной таблицы в workbook_b.

Любые изменения, которые вы вносите в свои данные в workbook_a, включая новых клиентов, обнаруживаются при обновлении сводной таблицы в workbook_b.

Обратите внимание, что обе рабочие книги должны быть открыты.

В частности, когда я поиграл с этим, я назвал свой динамический диапазон в workbook_a как "AllCustomerData", с данными, начинающимися в ячейке A1 на sheet1, и определил формулу для "AllCustomerData" как

 =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))

Далее, для источника сводной таблицы в workbook_b я использовал

 workbook_a.xlsx!AllCustomerData

Затем, когда я построил сводную таблицу, я выбрал "Классический макет сводной таблицы". Я разместил все свои поля в разделе "RowLabels" в поле "Список полей" сводной таблицы в порядке своих полей в оригинальной рабочей книге_a. Вы можете, конечно, заказать их по своему желанию или выбрать только те поля, которые вам нужны.

Когда я проверил это, я увидел, что если я удаляю клиента, обновляю сводную таблицу, затем повторно использую CUSTID этого клиента в нижней части исходного диапазона и снова обновляю сводную таблицу, то повторно использованный CUSTID будет отображаются в сводной таблице не внизу, а в исходном положении перед удалением. (Надеюсь, это было ясно!) Я не знаю, разрешают ли ваши бизнес-правила повторное использование CUSTID, но помните об этом, если вы это сделаете.

Надеюсь это поможет. Подход VBA @Lance Roberts тоже очень хорош. Преимущество этого подхода в сводной таблице заключается в том, что он не несет все эти "накладные расходы сводной таблицы".

Answer 2

Да, вы можете сделать макрос VBA для этого.

Я бы создал кнопку, и в событии click сначала убедитесь, что workbook_b открыт, а затем используйте следующий код, чтобы скопировать строки, где custid находится в workbook_a, но не в workbook_b (обратите внимание, что в итоге вы просто получаете копия workbook_a в workbook_b, так что было бы намного проще просто скопировать весь диапазон данных).

Dim i As Integer, workingCol1 As Integer, workingCol2 As Integer
Dim workingRange1 As Range, workingRange2 As Range

workingCol1 = WorksheetFunction.Match("custid", Sheets("Sheet1").UsedRange.Rows(1), 0)
Set workingRange1 = Sheets("Sheet1").Range("AllCustomers").Columns(workingCol1)

workingCol2 = WorksheetFunction.Match("custid", Sheets("Sheet2").UsedRange.Rows(1), 0)
Set workingRange2 = Sheets("Sheet2").Range("CriticalCustomers").Columns(workingCol2)

For i = 2 To workingRange1.Rows.Count
   If Not IsError(Application.Match(workingRange1.Cells(i, 1), workingRange2, 0)) Then
    workingRange1.Rows(i).EntireRow.Copy
    Sheets("Sheet2").UsedRange.Rows(Sheets("Sheet2").UsedRange.Rows.Count).Offset(1, 0).EntireRow.PasteSpecial (xlPasteValues)
  End If
Next i

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