Cách dùng hàm Excel để tìm kiếm với nhiều biến (1 người xem)

[nmhanh]

Chào các anh chị em,

Tôi đang gặp phải tình huống chưa tìm được giải pháp nên nhờ các anh chị em giúp cách sử dụng hàm Excel để tìm kiếm theo nhiều biến số và trả về giá trị tương ứng như minh hoạ trong file gửi kèm.

Xin cảm ơn các anh chị em.

 

[hoangminhtien]

Tại E16 bạn nhập công thức sau

=OFFSET(E$3,MATCH($A16&$B16&$C16&$D16,$A$4:$A$9&$B$4:$B$9&$C$4:$C$9&$D$4:$D$9,0),0)

Sau đó nhấn Ctr+Shift+Enter

 

[nmhanh]

Tại E16 bạn nhập công thức sau

Sau đó nhấn Ctr+Shift+Enter

Sau đó có thể dùng cách kéo ô E16 sang các ô khác đển copy công thức này không bạn?

Nhân tiện, nhờ bạn giải thích hộ dòng công thức trên đây có ý nghĩa như thế nào để mình áp dụng cho những trường hợp khác.

Cảm ơn bạn.

 

[hoangminhtien]

Sau đó có thể dùng cách kéo ô E16 sang các ô khác đển copy công thức này không bạn?

Bạn thử kéo xem nó cho ra kết quả là gì nha!

Nhân tiện, nhờ bạn giải thích hộ dòng công thức trên đây có ý nghĩa như thế nào để mình áp dụng cho những trường hợp khác.

Trước hết bạn hiểu $A$4:$A$9&$B $4:$B$9&$C$4:$C$9&$D$4:$D$9 là 1 mảng gồm các phần tử {A4&B4&C4&D4;A5&B5&C5&D5;...;A9&B9&C9&D9}
vì vậy MATCH($A16&$B16&$C16&$D16,$A$4:$A$9&$B $4:$B$9&$C$4:$C$9&$D$4:$D$9,0) sẽ trả về vị trí của phần tử $A16&$B16&$C16&$D16 trong mảng $A$4:$A$9&$B $4:$B$9&$C$4:$C$9&$D$4:$D$9.
rồi đến hàm OFFSET chắc bạn đã hiểu

 

[Hieu07061984]

Bạn tham khảo thử xem có đúng như ý của bạn không?
Nếu có gì thì cho mình ý kiến nhe

 

[hoangminhtien]

Bạn tham khảo thử xem có đúng như ý của bạn không?
Nếu có gì thì cho mình ý kiến nhe

Cách của bạn nếu có ai đó trùng tên nhau thì sẽ không tìm được tất cả dữ liệu của những người đó.
Theo mình cách tốt nhất là gán cho mỗi người một mã số, khi đó thì làm theo cách của bạn là hợp lý nhất

 

[nmhanh]

Mình có làm theo cách của bạn hoangminhtien nhưng mình đang gặp phải một vấn đề chưa xử lý được, đó là nếu như trong mảng dữ liệu xuất hiện nhiều lần một dữ liệu nào đó thì trong phần kết quả tìm kiếm và xuất ra dữ liệu tổng hợp lại không thực thi như mong muốn. Mình không hiểu nguyên nhân tại sao? Cụ thể các bạn xem file gửi kèm và giúp đỡ mình nhé.

Xin cảm ơn nhiều.

 

[MinhCong]

Mình có làm theo cách của bạn hoangminhtien nhưng mình đang gặp phải một vấn đề chưa xử lý được, đó là nếu như trong mảng dữ liệu xuất hiện nhiều lần một dữ liệu nào đó thì trong phần kết quả tìm kiếm và xuất ra dữ liệu tổng hợp lại không thực thi như mong muốn. Mình không hiểu nguyên nhân tại sao? Cụ thể các bạn xem file gửi kèm và giúp đỡ mình nhé.

Xin cảm ơn nhiều.

Bạn dùng hàm SUMPRODUCT xem sao nhé!

E16 =SUMPRODUCT(($A$4:$A$9&$B$4:$B$9&$C$4:$C$9&$D$4:$D$9=$A16&$B16&$C16&$D16)*(E$4:E$9))

 

[nmhanh]

Bạn dùng hàm SUMPRODUCT xem sao nhé!

E16 =SUMPRODUCT(($A$4:$A$9&$B$4:$B$9&$C$4:$C$9&$D$4:$D$9=$A16&$B16&$C16&$D16)*(E$4:E$9))

Cảm ơn bạn MinhCong. Mình còn một băn khoăn nhỏ nữa là nếu dữ liệu ở một Sheet khác với Sheet chứa kết quả tìm kiếm thì hàm SUMPRODUCT lại cho giá trị khác hoặc mình viết sai câu lệnh của hàm. Nhờ bạn chỉ giáo tiếp nhé. Mình gửi kèm file để bạn xem giúp.

Xin cảm ơn.

 

[hoangminhtien]

Cảm ơn bạn MinhCong. Mình còn một băn khoăn nhỏ nữa là nếu dữ liệu ở một Sheet khác với Sheet chứa kết quả tìm kiếm thì hàm SUMPRODUCT lại cho giá trị khác hoặc mình viết sai câu lệnh của hàm. Nhờ bạn chỉ giáo tiếp nhé. Mình gửi kèm file để bạn xem giúp.

Xin cảm ơn.

Công thức E3 của bạn

=SUMPRODUCT((Sheet1!$A$4:$A$9&Sheet1!$B$4:$B$9&Sheet1!$C$4:$C$9&Sheet1!$D$4:$D$9=Sheet2!$A3&Sheet2!$B3&Sheet2!$C3&Sheet2!$D3)*(Sheet1!E$4:G$9))

phải chỉnh thành

=SUMPRODUCT((Sheet1!$A$4:$A$9&Sheet1!$B$4:$B$9&Sheet1!$C$4:$C$9&Sheet1!$D$4:$D$9=Sheet2!$A3&Sheet2!$B3&Sheet2!$C3&Sheet2!$D3)*(Sheet1!E$4:E$9))

 

[nmhanh]

Chào các bạn,

Mình thấy cách dùng công thức {=OFFSET(E$3,MATCH($A16&$B16&$C16&$D16,$A$4:$A$9&$B $4:$B$9&$C$4:$C$9&$D$4:$D$9,0),0)} của bạn hoangminhtien xử lý được vấn đề không cần phải xắp xếp các cột kết quả dữ liệu ở Sheet kết quả theo thứ tự giống với Sheet dữ liệu đầu vào, nhưng lại không thực thi được khi Sheet dữ liệu đầu vào có nhiều bản ghi trùng lặp về trường đặc tính bản ghi (Tên, địa chỉ, nghề nghiệp, chức danh, ...). Cách dùng hàm SUMPRODUCT của bạn MinhCong thì bắt buộc phải xắp xếp các cột kết quả dữ liệu theo thứ tự giống với Sheet dữ liệu đầu vào, nhưng lại có thể tìm kiếm nếu như có nhiều bản ghi trùng lặp về trường đặc tính. Vậy có cách nào làm được cả hai không các bạn. Ý mình là:

- Dữ liệu đầu vào có nhiều bản ghi trùng lặp ở các trường đặc tính: Họ và tên, Lớp, Quê quán

- Các cột dữ liệu ở Sheet kết quả không xắp xếp theo thứ tự giống như ở trong Sheet dữ liệu đầu vào

Mình gửi kèm file minh họa nhé.

Xin cảm ơn các bạn giúp đỡ.

 

[MinhCong]

Chào các bạn,

Mình thấy cách dùng công thức {=OFFSET(E$3,MATCH($A16&$B16&$C16&$D16,$A$4:$A$9&$B $4:$B$9&$C$4:$C$9&$D$4:$D$9,0),0)} của bạn hoangminhtien xử lý được vấn đề không cần phải xắp xếp các cột kết quả dữ liệu ở Sheet kết quả theo thứ tự giống với Sheet dữ liệu đầu vào, nhưng lại không thực thi được khi Sheet dữ liệu đầu vào có nhiều bản ghi trùng lặp về trường đặc tính bản ghi (Tên, địa chỉ, nghề nghiệp, chức danh, ...). Cách dùng hàm SUMPRODUCT của bạn MinhCong thì bắt buộc phải xắp xếp các cột kết quả dữ liệu theo thứ tự giống với Sheet dữ liệu đầu vào, nhưng lại có thể tìm kiếm nếu như có nhiều bản ghi trùng lặp về trường đặc tính. Vậy có cách nào làm được cả hai không các bạn. Ý mình là:

- Dữ liệu đầu vào có nhiều bản ghi trùng lặp ở các trường đặc tính: Họ và tên, Lớp, Quê quán

- Các cột dữ liệu ở Sheet kết quả không xắp xếp theo thứ tự giống như ở trong Sheet dữ liệu đầu vào

Mình gửi kèm file minh họa nhé.

Xin cảm ơn các bạn giúp đỡ.

Tại ô E4 của sheet2 Bạn nhập công thức sau:

SUMPRODUCT((Sheet1!$A$4:$A$100&Sheet1!$B$4:$B$100&Sheet1!$C$4:$C$100&Sheet1!$D$4:$D$100=$A4&$B4&$C4&$D4)*(INDIRECT(ADDRESS([COLOR=Red]4[/COLOR];MATCH(E$3;Sheet1!$A$3:$J$3;0);4;1;"Sheet1")&":"&ADDRESS([COLOR=Red]100[/COLOR];MATCH(E$3;Sheet1!$A$3:$J$3;0);4;1))))

Để ý số 4 và 100 trong công thức ám chỉ danh sách học sinh bắt đầu từ dòng thứ 4 và kết thúc ở dòng 100

 

[duongdanang]

chào mọi người!
sao mình dùng công thức của anh hoangminhtien thì lại cho ra giá trị value.

 

[MinhCong]

chào mọi người!
sao mình dùng công thức của anh hoangminhtien thì lại cho ra giá trị value.

Do công thức đó là công thức mảng, khi nhập xong công thức Bạn phải kết thúc bằng tổ hợp phím Ctrl+Shift+Enter

 

[nmhanh]

Cảm ơn các anh chị em đã nhiệt tình giúp đỡ mình học hỏi rất nhiều.

Thêm một điều mình muốn học hỏi nữa, đó là hàm SUMPRODUCT chỉ trả về kết quả là dữ liệu số, nếu là dữ liệu text thì nó sẽ báo lỗi #VALUE (mình gửi kèm file minh họa).

Vậy có hàm nào để lấy dữ liệu là text không các bạn?

Xin cảm ơn nhiều.

 

[nmhanh]

Tại ô E4 của sheet2 Bạn nhập công thức sau:

SUMPRODUCT((Sheet1!$A$4:$A$100&Sheet1!$B$4:$B$100&Sheet1!$C$4:$C$100&Sheet1!$D$4:$D$100=$A4&$B4&$C4&$D4)*(INDIRECT(ADDRESS([COLOR=red]4[/COLOR];MATCH(E$3;Sheet1!$A$3:$J$3;0);4;1;"Sheet1")&":"&ADDRESS([COLOR=red]100[/COLOR];MATCH(E$3;Sheet1!$A$3:$J$3;0);4;1))))

Để ý số 4 và 100 trong công thức ám chỉ danh sách học sinh bắt đầu từ dòng thứ 4 và kết thúc ở dòng 100

Xin cho mình hỏi thêm:

- "Số 4 và 100 trong công thức ám chỉ danh sách học sinh bắt đầu từ dòng thứ 4 và kết thúc ở dòng 100" là trong danh sách tổng số học sinh đúng không? Hay là trong danh sách kết quả tìm kiếm.

- Trong công thức mình cũng thấy các số 4 khác nữa thì không hiểu các số 4 này có ý nghĩa gì và có phải là những số 4 kia không?

Xin cảm ơn.

 

[MinhCong]

Xin cho mình hỏi thêm:

- "Số 4 và 100 trong công thức ám chỉ danh sách học sinh bắt đầu từ dòng thứ 4 và kết thúc ở dòng 100" là trong danh sách tổng số học sinh đúng không? Hay là trong danh sách kết quả tìm kiếm.

- Trong công thức mình cũng thấy các số 4 khác nữa thì không hiểu các số 4 này có ý nghĩa gì và có phải là những số 4 kia không?

Xin cảm ơn.

Đúng rồi đấy Bạn. (Những số 4 và 100 kia cũng vậy)

 

[nmhanh]

Đúng rồi đấy Bạn. (Những số 4 và 100 kia cũng vậy)

Cảm ơn bạn.

Bạn giúp mình câu hỏi trước đó với:

"Thêm một điều mình muốn học hỏi nữa, đó là hàm SUMPRODUCT chỉ trả về kết quả là dữ liệu số, nếu là dữ liệu text thì nó sẽ báo lỗi #VALUE (mình gửi kèm file minh họa).

Vậy có hàm nào để lấy dữ liệu là text không các bạn?"

Ngoài ra, khi mình áp dụng vào công việc thì bị lỗi như sau. Mình loay hoay chưa biết làm thế nào. Nhờ các anh chị em giúp đỡ với. Mình gửi lèm file ở đây.

Cảm ơn các anh chị em nhiều.

 

[MinhCong]

Cảm ơn bạn.

Bạn giúp mình câu hỏi trước đó với:

"Thêm một điều mình muốn học hỏi nữa, đó là hàm SUMPRODUCT chỉ trả về kết quả là dữ liệu số, nếu là dữ liệu text thì nó sẽ báo lỗi #VALUE (mình gửi kèm file minh họa).

Vậy có hàm nào để lấy dữ liệu là text không các bạn?"

Ngoài ra, khi mình áp dụng vào công việc thì bị lỗi như sau. Mình loay hoay chưa biết làm thế nào. Nhờ các anh chị em giúp đỡ với. Mình gửi lèm file ở đây.

Cảm ơn các anh chị em nhiều.

Công thức của Bạn:

SUMPRODUCT((Sheet1!$B$2:$B$270&Sheet1!$C$2:$C$270=$B3&$C3)*(INDIRECT(ADDRESS(2;MATCH(D$2;Sheet1![COLOR=Red]$D$1[/COLOR]:$AU$1;0);2;1;"Sheet1")&":"&ADDRESS(270;MATCH(D$2;Sheet1![COLOR=Red]$D$1[/COLOR]:$AU$1;0);2;1))))

Sửa lại như sau:

SUMPRODUCT((Sheet1!$B$2:$B$270&Sheet1!$C$2:$C$270=$B3&$C3)*(INDIRECT(ADDRESS(2;MATCH(D$2;Sheet1![COLOR=Red]$A$1[/COLOR]:$AU$1;0);2;1;"Sheet1")&":"&ADDRESS(270;MATCH(D$2;Sheet1![COLOR=Red]$A$1[/COLOR]:$AU$1;0);2;1))))

 

[nmhanh]

Bạn ơi,

Làm ơn nghiên cứu giúp trường hợp dữ liệu là dạng text và hỗ trợ mình nhé.

Có hàm nào thay thế cho SUMPRODUCT hoặc cách dùng hàm SUMPRODUCT trong trường hợp này không?

Cảm ơn bạn.