Kathy có một bảng tính bao gồm thông tin cho tất cả các bộ phận trong nhà kho của cô ấy. Trong trang tính này, số bộ phận được hiển thị trong cột A sử dụng định dạng 12345 XXX, trong đó XXX đại diện cho mã vị trí. Điều này có nghĩa là cô ấy có thể có nhiều mục nhập trên trang tính cho các số phần giống nhau, nhưng mỗi mục nhập đại diện cho một vị trí khác nhau cho phần đó. Kathy cần một công thức tính tổng các giá trị được liên kết với mỗi số bộ phận, bất kể mã vị trí của nó là gì. Do đó, cô ấy cần một cách để tính tổng cột số lượng liên quan đến các phần 12345 ABC, 12345 DEF, 123456 GHI, v.v.

Cô ấy cần một cách để thực hiện việc này mà không cần tách mã vị trí sang một cột khác.

Có nhiều cách để có được câu trả lời mong muốn. Vì lợi ích của các ví dụ trong mẹo này, hãy giả sử rằng số bộ phận nằm trong cột A (như Kathy đã chỉ ra) và số lượng cho mỗi bộ phận nằm trong cột B. Chính những đại lượng này cần được tính tổng, chỉ dựa trên một phần của nội dung trong mỗi ô trong cột A. Ngoài ra, bạn có thể đặt số phần (trừ mã vị trí) mong muốn vào ô D2.

Giải pháp tiềm năng đầu tiên là sử dụng hàm SUMPRODUCT, theo cách này:

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

Công thức này kiểm tra các giá trị trong phạm vi A2: A49. Bạn nên đảm bảo rằng phạm vi này phản ánh phạm vi dữ liệu thực tế của bạn. Nếu bạn tổng quát hóa công thức để nó xem xét tất cả các cột A và B (như trong A: A và B: B), bạn sẽ gặp lỗi #VALUE, vì nó cố gắng áp dụng công thức cho các ô trống trong cột .

Bạn có thể nhận được kết quả tương tự bằng cách sử dụng công thức mảng như sau:

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

Một lần nữa, hãy nhớ rằng đây là một công thức mảng, vì vậy bạn cần nhập nó bằng cách nhấn Shift + Ctrl + Enter. Cũng lưu ý rằng công thức này chuyển đổi giá trị trong D2 thành văn bản để so sánh. Điều này không được thực hiện trong công thức trước vì ở đó chuỗi con được chọn từ cột A đã được chuyển đổi thành giá trị số bằng cách sử dụng hàm VALUE.

Bạn cũng có thể sử dụng hàm DSUM để xây dựng một công thức hoạt động. Giả sử rằng các số bộ phận (cột A) có tiêu đề cột trong ô A1.

Sao chép tiêu đề cột này (chẳng hạn như “Part Num”) sang một ô khác trong trang tính, chẳng hạn như ô D1. Trong ô D2, nhập số bộ phận, không có mã vị trí của nó, theo sau là dấu hoa thị. Ví dụ: bạn có thể nhập “12345 *” (không có dấu ngoặc kép) vào ô D2. Với thông số kỹ thuật được thiết lập, bạn có thể sử dụng công thức này:

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

Công thức này sử dụng đặc tả trong ô D2 (các ký tự 12345 theo sau là bất kỳ thứ gì) làm khóa để tính các giá trị từ cột B.

Cuối cùng, nếu bạn có cùng đặc điểm kỹ thuật trong ô D2 như bạn đã sử dụng với cách tiếp cận DSUM, bạn có thể sử dụng một hàm SUMIF rất đơn giản, theo cách sau:

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

Lưu ý rằng cách tiếp cận này cho phép bạn sử dụng phạm vi cột đầy đủ (A: A và B: B) trong công thức.

Nếu số bộ phận của bạn (trong cột A) không nhất quán trong định dạng của chúng như bạn có thể muốn, thì tốt hơn bạn nên tạo một hàm do người dùng xác định để tìm số lượng của bạn. Ví dụ: nếu số bộ phận của bạn không phải lúc nào cũng có cùng độ dài hoặc nếu số bộ phận có thể chứa cả chữ số và chữ cái hoặc dấu gạch ngang, thì UDF là cách để thực hiện. Ví dụ sau hoạt động tuyệt vời; nó đánh dấu sự hiện diện của ít nhất một khoảng trắng trong giá trị. (Kathy chỉ ra rằng một khoảng trắng ngăn cách số bộ phận với mã vị trí.)

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

Để sử dụng hàm, trong trang tính của bạn, hãy gọi hàm bằng hai phạm vi và số phần bạn muốn:

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

_Lưu ý: _

Nếu bạn muốn biết cách sử dụng các macro được mô tả trên trang này (hoặc trên bất kỳ trang nào khác trên trang ExcelTips), tôi đã chuẩn bị một trang đặc biệt bao gồm thông tin hữu ích.

ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.

Mẹo này (11468) áp dụng cho Microsoft Excel 97, 2000, 2002 và 2003.

Bạn có thể tìm thấy phiên bản của mẹo này cho giao diện ribbon của Excel (Excel 2007 trở lên) tại đây: