Hàm trả về vùng không liền mạch trong excel?

[mrhackgm]

Kính chào các bác.

vấn đề của em thế này: Em muốn tìm kiếm 1 hàm trả về vùng (array) không liền mạch.
VD:
a1:b10 là một vùng liền mạch
a1:b4 cộng với a6:b10 là vùng không liền mạch

Chính xác mục đích của em là Chọn vùng theo điều kiện, loay hoay define cho nó bằng hàm offset kết hợp với counta hay if mà mãi không xong cả tuần nay rồi... giờ phút này em thực sự bế cả tắc ý tưởng không bay đc nữa rồi.

Các bác ra tay vẽ ông mặt trời cho em với.


Em chân thành cảm ơn ạ!

 

[ptm0412]

trước tiên là dùng 1 name trung gian:
tmp =if($A$1:$A$10= điều kiện, row($1:$10), "")

Kết quả có được từ công thức:

= index($A$1:$A$10, small(tmp, rows($1:1)), 1)

fill xuống

 

[mrhackgm]

ôi sao lại sơ xuất thế đc nhỉ

Em muốn define sao cho chỉ trả về các vùng có chưa UV, BO, GA hay AX....
mà nhìn vào trong file cũng thấy các BO là không gần nhau, nên nó sẽ là các mảng đứt đoạn ạ!

http://www.mediafire.com/?rc67jwoo8hj4ga5

Em chân thành cảm ơn


PS: em xin lỗi vì sơ xuất không attach file

 

[ptm0412]

PS: em xin lỗi vì sơ xuất không attach file

Có những bài hỏi không cần file.

 

[mrhackgm]

bác cả ah, xem giúp em có lỗi gì tỏng công thức không với.
em nhập theo logic đó, nó bảo:
You've entered too many arguments for this function???

Cảm ơn bác!

 

[ptm0412]

Sửa lại công thức bài 6:

= index($A$1:$A$10, small(tmp, rows($1:1)), 1)

(thiếu 1 dấu ngoặc)

 

[bebo021999]

Điều kiện tạo vùng với điều kiện "UV" có thể như sau:

=IF(ROW(B2:E31)=IF(C2:C31="UV",ROW(C2:C31),0),B2:E31,"")

Tức là những dòng nào không chứa "UV" sẽ cho bằng rỗng.
Nhấn F9 để xem kết quả:

{"12870K","UV",21,14;"","","","";"12871A","UV",10,26;"12871F","UV",3,11;"","","","";"","","","";"","","","";"12482C","UV",28,22;"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"12866B","UV",22,11;"12869B","UV",27,24;"12869D","UV",18,28;"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","","";"","","",""}

 

[ptm0412]

Điều kiện tạo vùng với điều kiện "UV" có thể như sau:

=IF(ROW(B2:E31)=IF(C2:C31="UV",ROW(C2:C31),0),B2:E31,"")

Tức là những dòng nào không chứa "UV" sẽ cho bằng rỗng.
Nhấn F9 để xem kết quả:

Công thức này tạo ra mảng 4 cột thì OK rồi, nhưng đưa xuống sheet loại bỏ dòng trống thì làm thế nào? Có lẽ cũng dùng index. Vậy Nếu đã index và small thì chỉ cần mảng 1 cột làm mảng tạm thôi.

Trong file dưới đây, chọn UV, BO trong ô H1 và xem kết quả. Tất nhiên là 1 name duy nhất và name tạo mảng 1 cột.

 

[mrhackgm]

Thực sự hiệu quả ngoài sự mong đợi của em.

Một lần nữa chân thành cảm ơn các bác đã nhiệt tình giúp đỡ!

 

[nguyenviethungson1107]

em cũng giống trường hợp như trên muốn dùng hàm counif để đếm nhưng mảng ko liền mạch ví dụ mảng là B1:b4 và f2:f4.kí tự cần đếm có tên là "sơn" Các bạn có thể viết hàm cho mình được ko.thanks

 

[Hong.Van]

em cũng giống trường hợp như trên muốn dùng hàm counif để đếm nhưng mảng ko liền mạch ví dụ mảng là B1:b4 và f2:f4.kí tự cần đếm có tên là "sơn" Các bạn có thể viết hàm cho mình được ko.thanks

Bạn thử dùng Countif đếm B1:b4 rồi CỘNG Countif đếm f2:f4