У меня есть электронная таблица со следующими данными:
Prod No Store 1 $ Store 1 Qty Store 2 Sale Store 2 Qty etc
A 4.00 1 7.50 2
B 0 0 15.00 1
C 4.00 2 -8 -1
D 5.00 1 5.00 1
Мне нужно получить все уникальные цены для каждого номера детали, а затем суммировать количество по каждой цене, например,
Prod No A has 4.00 1 unit and 7.50 2 units
Prod No B has 0.00 0 units and 15.00 1 unit
Prod No C has 4.00 2 units and -8 -1 unit
Prod No D has 5.00 2 units
Мне также нужен список только уникальных цен и общее количество по каждой цене.
например,
4.00 3 units
7.50 2 units
0.00 0 units
-8 -1 unit
15.00 1 unit
5.00 2 units
решение1
Во-первых, я считаю, что в долгосрочной перспективе вам повезет больше, если вы будете хранить свои данные в более горизонтальной форме, например:
Store Product Qty Sales
Store 1 A 1 4.00
Store 1 B 0 0.00
Store 1 C 2 4.00
Store 1 D 0 0.00
Гораздо проще выполнять поиск по одному столбцу, чем по парам столбцов.
(В зависимости от размера и масштаба база данных Access с отдельными таблицами Store, Product и Sales может оказаться даже лучше.)
Тем не менее, если вы ограничены тем, что у вас есть, и можете переварить макрос VBA в своей таблице, вы можете попробовать следующее:
Добавьте в свой проект VBA модуль класса под названием
Tuple
, содержащий:Private szKey As String Private nValue As Double Public Property Get Key() As String Key = szKey End Property Public Property Let Key(newKey As String) szKey = newKey End Property Public Property Get Value() As Double Value = nValue End Property Public Property Let Value(newValue As Double) nValue = newValue End Property
Добавьте обычный модуль, например,
Module 1
в свой проект, содержащий:Public Function Summarize(ByRef rng As Range) As String If rng.Cells.Count Mod 2 = 1 Then Err.Raise 100, "", "Expected range of even cells" Dim coll As New Collection On Error Resume Next Dim flag As Boolean: flag = False Dim prevCel As Range, cel As Range: For Each cel In rng.Cells If flag Then Dim Key As String: Key = "" & prevCel.Value2 coll(Key).Value = coll(Key).Value + cel.Value2 If Err.Number <> 0 Then Err.Clear Dim t1 As New Tuple t1.Key = "" & prevCel.Value2 t1.Value = cel.Value2 coll.Add t1, Key Set t1 = Nothing End If End If Set prevCel = cel flag = Not flag Next cel On Error GoTo 0 Dim t2 As Variant: For Each t2 In coll If Len(Summarize) Then Summarize = Summarize & ", " Summarize = Summarize & Format(t2.Key, "#0.00") & " @ " & t2.Value Next t2 End Function
Затем на рабочем листе вы можете ввести формулу, например:
="Product " & $A2 & " has " & Summarize($B2:$I2)
Убедитесь, что вы заменили диапазон $B2:$I2 на тот, который достаточно широк, чтобы охватить все возможные количества магазинов. Также убедитесь, что вы используете диапазон четного размера (потому что значения Sale/Qty парные), иначе вы получите ошибку
#VALUE
.