Что является лучшим способом сравнить два листа в рабочей книге Excel
Учитывая у меня есть следующее
<Sheet 1>
Item QTY
A 5
B 1
C 3
<Sheet 2>
Item QTY
A 15
B 4
C 1
D 8
Что лучший способ состоит в том, чтобы генерировать отчет, показывающий различие между листом 1 и 2?
Как
<Difference>
Item QTY
A 10
B 3
C -2
D 8
5 ответов
В Excel VBA используйте Словарь . Используйте элементы из одного из листов в качестве клавиш, QTY в качестве значений. Положите в словарь пары элемент/QTY листа 1, затем пропустите элементы листа 2 и обновите словарь соответствующим образом, чтобы получить разницу. Наконец, поместите результат на лист 3.
EDIT: вот полный пример в коде (нужно установить ссылку на время выполнения Microsoft Scripting, чтобы он работал таким образом):
Option Explicit
Sub CreateDiff()
Dim dict As New Dictionary
Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet
Dim i As Long, v As String
Set sh1 = ThisWorkbook.Sheets("Sheet1")
Set sh2 = ThisWorkbook.Sheets("Sheet2")
Set sh3 = ThisWorkbook.Sheets("Sheet3")
For i = 2 To sh1.Cells.SpecialCells(xlCellTypeLastCell).Row
v = Trim(sh1.Cells(i, 1).Value)
dict(v) = -sh1.Cells(i, 2).Value
Next
For i = 2 To sh2.Cells.SpecialCells(xlCellTypeLastCell).Row
v = Trim(sh2.Cells(i, 1).Value)
If dict.Exists(v) Then
dict(v) = dict(v) + sh2.Cells(i, 2).Value
Else
dict(v) = sh2.Cells(i, 2).Value
End If
Next
For i = 0 To dict.Count - 1
v = dict.Keys(i)
sh3.Cells(i + 2, 1) = v
sh3.Cells(i + 2, 2) = dict(v)
Next
End Sub
Вы можете объединить оба набора данных на один лист бок-бок (item1, qty, item2, qty), затем используют функцию vlookup () excel, чтобы найти данные с противоположного набор.
Зачем использовать VBA? На листе 3 листа сравнения в списке всех возможных элементов от листов 1 и 2 в столбце A Затем в столбце B используйте следующую формулу. Начиная с B2, то скопируйте вниз.
= Если (Iserror (vlookup (a2, лист2 '$ a 2: $ b $ 5,2, false), 0, vlookup (a2, лист2' $ a 2: $ b $ 5,2, false)) - если (Iserror (Vlookup (A2, Sheet1 '$ A $ 2: $ B $ 5,2, false), 0, vlookup (a2, лист1' $ a 2: $ B $ 5,2, false))
Измените диапазон таблицы по мере необходимости.
Одна из возможностей - использовать ADO
Dim cn As Object
Dim rs As Object
Dim strFile As String
Dim strCon As String
Dim strSQL As String
''http://support.microsoft.com/kb/246335
strFile = Workbooks("Book1.xls").FullName
''Note HDR=Yes, the names in the first row of the range
''can be used.
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
& ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cn.Open strCon
strSQL = "SELECT s2.Item, s2.Qty-IIf(s1.Qty Is Null,0,s1.Qty) FROM [Sheet2$] s2 " _
& "LEFT JOIN [Sheet1$] s1 ON s2.Item=s1.Item"
rs.Open strSQL, cn, 3, 3
Workbooks("Book1.xls").Worksheets("Sheet3").Cells(2, 1).CopyFromRecordset rs
Вам не нужно понадобиться VBA для этого.
Вот что вы делаете:
Создайте новый рабочий лист (лист3).
Установите это, чтобы выглядеть так:
ALT TEXT http://img16.imageshack.us/img16/2451/consolidationsheeT.jpg
hys не понадобится формула (вставьте каждый в правильность Cell):
Примечание: Первые два - «Формулы массива» - после вставки в формулу дважды щелкните ячейку и выполните Ctrl-Shift-Enter (Bracaces {}, должны появиться вокруг формулы)
------------------------------------------------------ ----------------------------------- Формула клеток -------------------------------------------------------- -------------------------------- B2 = SUM (если (Sheet1! A: A = "", 0,1)) <- Формула массива: используйте Ctrl-Shift-Enter вместо ввода B3 = сумма (если (лист2! A: a = "", 0,1)) <- Формула массива: используйте Ctrl-Shift-Enter вместо ввода D2 = if (d1 = d $ 1,2, если (или (d1 = b $ 2, d1 = ""), "", d1 + 1)) E2 = if (d2 = "", если (d1 = "", если (или (E1 = B $ 3, E1 = ""), "", E1 + 1), 2), "") G2 = if (d2 <> "", индекс (лист1! A: A, D2), если (E2 <> "", индекс (лист2! A: A, E2), "")) H2 = if (d2 <> "", - индекс (лист1! B: b, d2), если (e2 <> "", индекс (лист2! B: B, E2), ""))Перетащите формулы в D2: H2 вниз, насколько вам необходимо, чтобы охватить все данные для листов 1 и 2.
Выберите все данные в столбцах G & H (включая заголовки).
Сделайте вставку> PivOptable и нажмите OK.
Щелкните таблицу Pivot и перетащите
[] элементв коробку наклеиваний строки и[] QTYв поле значений.
Это так. Таблица Pivot будет содержать резюме для каждого элемента. Ни один элемент не будет повторяться, и ни один элемент не будет оставлен. Колонна «Sum of Qty» фактически содержит разницу (поскольку формула использует отрицательный для всех листов 1 количества).