Hàm tìm kiếm và tính tổng trong mảng (1 người xem)

[Mai Mèo 122]

Gửi các anh chị, e có file như bên dưới gồm chuỗi text. Trong đó các phần trong text có thể tham chiếu đến 1 giá trị trong bảng cho trước. Yêu cầu tính tổng giá trị của chuỗi này.
Ví dụ: ô A9 là chuỗi V1/V2/V4 trong đó có bảng tham chiếu cho sẵn V1=100, V2=200, V3=300, V4=400. Giá trị trả về là 700 cho chuỗi V1/V2/V4. Làm cách nào viết công thức tính tổng ở ô màu vàng?
Em cảm ơn ạ

 

[Phan Thế Hiệp]

Gửi các anh chị, e có file như bên dưới gồm chuỗi text. Trong đó các phần trong text có thể tham chiếu đến 1 giá trị trong bảng cho trước. Yêu cầu tính tổng giá trị của chuỗi này.
Ví dụ: ô A9 là chuỗi V1/V2/V4 trong đó có bảng tham chiếu cho sẵn V1=100, V2=200, V3=300, V4=400. Giá trị trả về là 700 cho chuỗi V1/V2/V4. Làm cách nào viết công thức tính tổng ở ô màu vàng?
Em cảm ơn ạ

Thử:

B9=SUMPRODUCT(ISNUMBER(SEARCH($A$3:$A$6,A9))*$B$3:$B$6)

Enter, fill xuống

Thân

 

[Loc Uyen]

Gửi các anh chị, e có file như bên dưới gồm chuỗi text. Trong đó các phần trong text có thể tham chiếu đến 1 giá trị trong bảng cho trước. Yêu cầu tính tổng giá trị của chuỗi này.
Ví dụ: ô A9 là chuỗi V1/V2/V4 trong đó có bảng tham chiếu cho sẵn V1=100, V2=200, V3=300, V4=400. Giá trị trả về là 700 cho chuỗi V1/V2/V4. Làm cách nào viết công thức tính tổng ở ô màu vàng?
Em cảm ơn ạ

anh/Chị xem thử nhé!

Bài đã được tự động gộp: 11/10/18

Thử:

B9=SUMPRODUCT(ISNUMBER(SEARCH($A$3:$A$6,A9))*$B$3:$B$6)

Enter, fill xuống

Thân

Wow, của anh gọn quá. . "Xí hổ"

 

[Mai Mèo 122]

SUMPRODUCT(ISNUMBER(SEARCH($A$3:$A$6,A9))*$B$3:$B$6)

Mình cảm ơn nhé

 

[Phan Thế Hiệp]

anh/Chị xem thử nhé!
Wow, của anh gọn quá. . "Xí hổ"

Không có gì phải "xí hổ" đâu em!
"Ngọc bất trác, bất thành khí"
Ngọc mà không mài giũa thì làm sao làm đẹp cho đời.

Trong tương lai, em sẽ còn vượt trội gấp 100 lần, cứ dũng cảm và bạo gan lên như vậy em!

Chúc em ngày vui.

 

[Loc Uyen]

Không có gì phải "xí hổ" đâu em!
"Ngọc bất trác, bất thành khí"
Ngọc mà không mài giũa thì làm sao làm đẹp cho đời.

Trong tương lai, em sẽ còn vượt trội gấp 100 lần, cứ dũng cảm và bạo gan lên như vậy em!

Chúc em ngày vui.

Cám ơn anh động viên, hihi, học hỏi được nhiều từ anh và mọi người.

 

[hml89]

Thử:

B9=SUMPRODUCT(ISNUMBER(SEARCH($A$3:$A$6,A9))*$B$3:$B$6)

Enter, fill xuống

Thân

Hi @Phan Thế Hiệp , khi tôi F9 đoạn Search($A$3:$A$6,A9) thì nó ra là {1;4;#VALUE!;7}, nhưng tôi không hiểu giá trị bên trong đó (1;4;7 ) lấy như thế nào . Bạn có thể giải thích giùm tôi được không?

 

[Phan Thế Hiệp]

Hi @Phan Thế Hiệp , khi tôi F9 đoạn Search($A$3:$A$6,A9) thì nó ra là {1;4;#VALUE!;7}, nhưng tôi không hiểu giá trị bên trong đó (1;4;7 ) lấy như thế nào . Bạn có thể giải thích giùm tôi được không?

Search() hay Find() cùng công năng là:
"Tìm kiếm ký tự (hoặc chuỗi ký tự) bất kỳ trong vùng chỉ định, và trả về vị trí của ký tự đó nếu tìm thấy, nếu không tìm thấy se báo lỗi #VALUE!"

Khi đem Mảng {"V1";"V2";"V3";"V4} so với ô A9= "V1/V2/V4" thì nó tìm thấy:

• "V1" có tại vị trí số 1 của ô A9= "V1/V2/V4"
• "V2" có tại vị trí số 4 của ô A9= "V1/V2/V4"
• "V3" không tìm thấy báo lỗi
• "V4" có tại vị trí số 7 của ô A9= "V1/V2/V4"

Dù rằng nó tìm hai ký tự, nhưng khi tìm thấy thì nó trả về số vị trí của ký tự đầu tiên.
Thật ra, để làm kỹ hơn thì công thức phải là:

=SUMPRODUCT(ISNUMBER(SEARCH("/"&$A$3:$A$6&"/","/"&A9&"/"))*$B$3:$B$6)

Nhằm tránh trường hợp có mã gần giống, Vd: V10, V11 hay V252....

Thân

 

[hml89]

Search() hay Find() cùng công năng là:
"Tìm kiếm ký tự (hoặc chuỗi ký tự) bất kỳ trong vùng chỉ định, và trả về vị trí của ký tự đó nếu tìm thấy, nếu không tìm thấy se báo lỗi #VALUE!"

Khi đem Mảng {"V1";"V2";"V3";"V4} so với ô A9= "V1/V2/V4" thì nó tìm thấy:

• "V1" có tại vị trí số 1 của ô A9= "V1/V2/V4"
• "V2" có tại vị trí số 4 của ô A9= "V1/V2/V4"
• "V3" không tìm thấy báo lỗi
• "V4" có tại vị trí số 7 của ô A9= "V1/V2/V4"

Dù rằng nó tìm hai ký tự, nhưng khi tìm thấy thì nó trả về số vị trí của ký tự đầu tiên.
Thật ra, để làm kỹ hơn thì công thức phải là:

=SUMPRODUCT(ISNUMBER(SEARCH("/"&$A$3:$A$6&"/","/"&A9&"/"))*$B$3:$B$6)

Nhằm tránh trường hợp có mã gần giống, Vd: V10, V11 hay V252....

Thân

Công thức hay quá bạn ạ, cám ơn bạn đã chỉ dẫn, tôi đã hiểu rồi. thật là bổ ích quá!

 

[cdtam]

Không có gì phải "xí hổ" đâu em!
"Ngọc bất trác, bất thành khí"

Ngọc này chắc là "Ngọc bích họ Hòa"

 

[befaint]

Thử:

B9=SUMPRODUCT(ISNUMBER(SEARCH($A$3:$A$6,A9))*$B$3:$B$6)

Enter, fill xuống

Thân

Ngoài Find, Search còn cách nào không anh?

 

[Phan Thế Hiệp]

Ngoài Find, Search còn cách nào không anh?

Sợ nhất là cái ông em "Thanh tra" này của tôi lắắắmmm!!
Mỗi lần "ổng" vặn vẹo là có vấn đề!?

Dạ! đây 'anh'

=SUMPRODUCT(COUNTIF(A9,"*"&$A$3:$A$6&"*")*$B$3:$B$6)

Chúc em ngày vui.
Khà khà khà.

 

[HieuCD]

Sợ nhất là cái ông em "Thanh tra" này của tôi lắắắmmm!!
Mỗi lần "ổng" vặn vẹo là có vấn đề!?

Dạ! đây 'anh'

=SUMPRODUCT(COUNTIF(A9,"*"&$A$3:$A$6&"*")*$B$3:$B$6)

Chúc em ngày vui.
Khà khà khà.

Hình như thiếu ký tự dấu...

 

[Phan Thế Hiệp]

Hình như thiếu ký tự dấu...

Thêm ông anh chung băng 'Tứ quái' có mặt, gần đủ mặt rồi vui quá!
Nhưng giờ em đang gõ từng chữ bằng xì mát phôn, làm sao mần được công thức đây anh?
Hổng ấy, anh ra tay giúp em.

Chúc anh ngày vui.

 

[HieuCD]

Thêm ông anh chung băng 'Tứ quái' có mặt, gần đủ mặt rồi vui quá!
Nhưng giờ em đang gõ từng chữ bằng xì mát phôn, làm sao mần được công thức đây anh?
Hổng ấy, anh ra tay giúp em.

Chúc anh ngày vui.

Giống bài #8 đó mờ, thêm dấu "/", nhưng Countif nó không chịu

 

[huonglien1901]

Gửi các anh chị, e có file như bên dưới gồm chuỗi text. Trong đó các phần trong text có thể tham chiếu đến 1 giá trị trong bảng cho trước. Yêu cầu tính tổng giá trị của chuỗi này.
Ví dụ: ô A9 là chuỗi V1/V2/V4 trong đó có bảng tham chiếu cho sẵn V1=100, V2=200, V3=300, V4=400. Giá trị trả về là 700 cho chuỗi V1/V2/V4. Làm cách nào viết công thức tính tổng ở ô màu vàng?
Em cảm ơn ạ

Chơi cột phụ!

 

[befaint]

Chơi cột phụ!

. Thử hàm len và substitute xem.

 

[Phan Thế Hiệp]

. Thử hàm len và substitute xem.

Ý hay!
Dùng Substitute() để làm giảm ký tự cần tìm, lấy chiều dài của mảng đó rồi so với chiều dài của chuỗi gốc, lồi ra em nào nhỏ hơn. Sau đó nhân với giá trị, và cộng được.

Chúc anh em ngày vui.

 

[Lê Duy Thương]

Search() hay Find() cùng công năng là:
"Tìm kiếm ký tự (hoặc chuỗi ký tự) bất kỳ trong vùng chỉ định, và trả về vị trí của ký tự đó nếu tìm thấy, nếu không tìm thấy se báo lỗi #VALUE!"

Khi đem Mảng {"V1";"V2";"V3";"V4} so với ô A9= "V1/V2/V4" thì nó tìm thấy:

• "V1" có tại vị trí số 1 của ô A9= "V1/V2/V4"
• "V2" có tại vị trí số 4 của ô A9= "V1/V2/V4"
• "V3" không tìm thấy báo lỗi
• "V4" có tại vị trí số 7 của ô A9= "V1/V2/V4"

Dù rằng nó tìm hai ký tự, nhưng khi tìm thấy thì nó trả về số vị trí của ký tự đầu tiên.
Thật ra, để làm kỹ hơn thì công thức phải là:

=SUMPRODUCT(ISNUMBER(SEARCH("/"&$A$3:$A$6&"/","/"&A9&"/"))*$B$3:$B$6)

Nhằm tránh trường hợp có mã gần giống, Vd: V10, V11 hay V252....

Thân

search(....) sẽ tìm cả chữ hoa lẫn thường. find(..) chỉ tìm được 1 thứ chữ hoa hoặc chữ thường thôi anh bạn hiền .

 

[Phan Thế Hiệp]

search(....) sẽ tìm cả chữ hoa lẫn thường. find(..) chỉ tìm được 1 thứ chữ hoa hoặc chữ thường thôi anh bạn hiền .

Khà khà khà!
Bởi vậy đã nói là cùng "công năng", chứ đã bảo là giống "đặc tính" đâu nè!

Cũng như, cùng làm việc nghĩa chung mái nhà GPE (->công năng), nhưng mà đặc tính của hai anh chàng: @Lê Duy Thương và @Phan Thế Hiệp thì khác nhau: một ông thì "khó chịu", còn một ông "dễ ghét".

Khà khà khà