У Кэти есть рабочий лист, который включает информацию обо всех деталях на ее складе. На этом листе номера деталей показаны в столбце A в формате 12345 XXX, где XXX представляет собой код местоположения. Это означает, что у нее может быть несколько записей на листе с одними и теми же номерами деталей, но каждая запись представляет другое местоположение для этой части. Кэти нужна формула, которая суммирует значения, связанные с каждым номером детали, независимо от его кода местоположения. Таким образом, ей нужен способ суммирования столбца количества, относящегося к частям 12345 ABC, 12345 DEF, 123456 GHI и т. Д.

Ей нужен способ сделать это без разделения кода местоположения на другой столбец.

Есть несколько способов получить желаемый ответ. Для примеров в этом совете предположим, что номера деталей находятся в столбце A (как указала Кэти), а количества для каждой детали — в столбце B. Именно эти количества необходимо суммировать, основываясь только на часть того, что находится в каждой ячейке в столбце A. Кроме того, вы можете поместить желаемый номер детали (минус код местоположения) в ячейку D2.

Первое возможное решение — использовать функцию СУММПРОИЗВ следующим образом:

=SUMPRODUCT(--(VALUE(LEFT(A2:A49,FIND(" ",A2:A49)))=D2),B2:B49)

Эта формула проверяет значения в диапазоне A2: A49. Вы должны убедиться, что этот диапазон отражает диапазон ваших фактических данных. Если вы обобщите формулу так, чтобы она просматривала все столбцы A и B (как в A: A и B: B), вы получите ошибку #VALUE, поскольку она пытается применить формулу к пустым ячейкам в столбцах. .

Вы можете получить аналогичный результат, используя такую ​​формулу массива:

=SUM(B:B*(LEFT(A2:A49,5)=TEXT(D2,"@")))

Еще раз помните, что это формула массива, поэтому вам нужно ввести ее, нажав Shift + Ctrl + Enter. Также обратите внимание, что эта формула преобразует значение в D2 в текст для сравнения. Этого не было сделано в предыдущей формуле, потому что там подстрока, выбранная из столбца A, была преобразована в числовое значение с помощью функции VALUE.

Вы также можете использовать функцию DSUM для построения рабочей формулы. Предположим, что номера деталей (столбец A) имеют заголовок столбца в ячейке A1.

Скопируйте этот заголовок столбца (например, «Номер детали») в другую ячейку на листе, например в ячейку D1. В ячейке D2 введите номер детали без кода местоположения и поставьте звездочку. Например, вы можете ввести «12345 *» (без кавычек) в ячейку D2. После настройки этой спецификации вы можете использовать эту формулу:

=DSUM($A$1:$B$49,$B$1,D1:D2)

Эта формула использует спецификацию в ячейке D2 (символы 12345, за которыми следует что-либо) в качестве ключа, к которому следует суммировать значения из столбца B.

Наконец, если у вас есть та же спецификация в ячейке D2, что и при использовании подхода DSUM, вы можете использовать очень простую функцию СУММЕСЛИ, например:

=SUMIF(A:A,D2,B:B)

Обратите внимание, что этот подход позволяет использовать в формуле полные диапазоны столбцов (A: A и B: B).

Если номера ваших деталей (в столбце A) не так согласованы по своему формату, как вам хотелось бы, тогда вам может быть лучше создать определяемую пользователем функцию для определения ваших количеств. Например, если ваши номера деталей не всегда имеют одинаковую длину или если номера деталей могут содержать как цифры, так и буквы или тире, то UDF — это то, что вам нужно. Следующий пример отлично работает; он указывает наличие хотя бы одного пробела в значении. (Кэти указала, что пробел отделяет номер детали от кода местоположения.)

Function AddPrtQty(ByVal Parts As Range, PartsQty As Range, _   FindPart As Variant) As Long     Dim Pos As Integer     Dim Pos2 As Integer     Dim i As Long     Dim tmp As String     Dim tmpSum As Long     Dim PC As Long

PC = Parts.Count     If PartsQty.Count <> PC Then         MsgBox "Parts and PartsQty must be the same length", vbCritical         Exit Function     End If

For i = 1 To PC         Pos = InStr(1, Parts(i), " ")

Pos2 = InStr(Pos + 1, Parts(i), " ")



If Pos2 > Pos And Len(Parts(i)) > Pos + 1 Then             tmp = CStr(Trim(Left(Parts(i), Pos2 - 1)))

ElseIf Pos > 0 And Len(Parts(i)) > 0 Then             tmp = CStr(Trim(Left(Parts(i), Pos - 1)))

End If

If CStr(Trim(tmp)) = CStr(Trim(FindPart)) Then             tmpSum = tmpSum + PartStock(i)

End If     Next i

AddPrtQty = tmpSum End Function

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

=AddPrtQty(A2:A49,B2:B49,"GB7-QWY2")

_Примечание: _

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

link: / excelribbon-ExcelTipsMacros [Щелкните здесь, чтобы открыть эту специальную страницу в новой вкладке браузера].

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (11468) применим к Microsoft Excel 97, 2000, 2002 и 2003.

Вы можете найти версию этого совета для ленточного интерфейса Excel (Excel 2007 и новее) здесь:

link: / excelribbon-Summing_Based_on_Part_of_the_Information_in_a_Cell [Суммирование на основе части информации в ячейке].