Làm sao để lọc có điều kiện và sắp xếp theo ý mình muốn (1 người xem)

[loading860]

Em gặp vấn đề về lấy TK và MK như trong hình , bro giái giúp em với
TK sẽ đi liền với MK , vì có những cái bị thiếu thì không nhận , nếu TK típ theo TK không có MK thì ko lấy , MK cũng thế , nhìn hình sẽ hiểu

Em cám ơn nhìu lắm , làm ơn đừng xóa và giúp em giải câu này với

 

[Bảy Dzõ]

Em gặp vấn đề về lấy TK và MK như trong hình , bro giái giúp em với
TK sẽ đi liền với MK , vì có những cái bị thiếu thì không nhận , nếu TK típ theo TK không có MK thì ko lấy , MK cũng thế , nhìn hình sẽ hiểu

Em cám ơn nhìu lắm , làm ơn đừng xóa và giúp em giải câu này với

Theo tôi nghĩ bài này sẽ không bị xoá, nhưng với 3 bài (sai) kia thì có cái file còn bài đúng này thì hổng có cái file nào hết. Đưa file lên cho dễ nhé bạn. Thân

 

[loading860]

Dạ rồi , em thiếu cái file

 

[Bảy Dzõ]

Dạ rồi , em thiếu cái file

Theo kiến thức sơ đẳng của mình thì làm thủ công một chút luôn vậy. Bạn dùng Advance Filter theo dưới này cho dữ liệu "TK" và copy vào cột B nhé.



Đối với dữ liệu "MK" thì bạn đổi vùng điều kiện từ $B$1:$B$2 thành $C$1:$C$2 rồi cũng copy ra nhé...

 

[loading860]

Dạ cám ơn anh Bảy Dzõ , mình lấy đc dữ liệu TK còn MK thì em không lấy đc anh à , đổi vùng điều kiện từ $B$1:$B$2 thành $C$1:$C$2 bên cột A nó vẫn hiện TK , thêm 1 cái nữa là vì có những cái có TK nhưng không có dữ liệu MK ( hoặc ngược lại ) , vì vậy khi copy ra nói sẽ bị sai lệch , có cách nào giái quyết không ta

​

 

[Bảy Dzõ]

Dạ cám ơn anh Bảy Dzõ , mình lấy đc dữ liệu TK còn MK thì em không lấy đc anh à , đổi vùng điều kiện từ $B$1:$B$2 thành $C$1:$C$2 bên cột A nó vẫn hiện TK , thêm 1 cái nữa là vì có những cái có TK nhưng không có dữ liệu MK ( hoặc ngược lại ) , vì vậy khi copy ra nói sẽ bị sai lệch , có cách nào giái quyết không ta

​

Xin lỗi mình bị lỗi ở ô C2, bạn thay công thức ở C2 bằng: =LEFT(A3,2)="MK" nhé thì sẽ ok. Mình cũng đang cố gắng học để tìm ra cách đó cho bạn (vì kiến thức mình thuộc chập chững thôi)... Thân

 

[loading860]

Ngồi mò nãy giờ mà vẫn không đc , ví dụ em đặt ngay B3=IF(LEFT(A4,2)="MK",MID(A3,5,30),"") còn C3=IF(LEFT(A3,2)="TK",MID(A4,5,40)) , nó cũng ra nhưng mà làm sao cho nó chạy đều vậy xuống dưới , gõ nhìu cái cho nó hiểu mà vẫn không đc mà sao , xem thử dùm em

 

[loading860]

Mong thầy cô và các bạn trên GPE giúp em bài này với, hoặc trao đổi để giúp em tìm ra cách giải được không ạ !

 

[Bảy Dzõ]

Ngồi mò nãy giờ mà vẫn không đc , ví dụ em đặt ngay B3=IF(LEFT(A4,2)="MK",MID(A3,5,30),"") còn C3=IF(LEFT(A3,2)="TK",MID(A4,5,40)) , nó cũng ra nhưng mà làm sao cho nó chạy đều vậy xuống dưới , gõ nhìu cái cho nó hiểu mà vẫn không đc mà sao , xem thử dùm em

cái này bạn làm bằng thủ công vẫn được mà. Ô B2=IF(LEFT(A2,2)="TK",MID(A2,5,50),"") sau đó kéo fill xuống rồi dùng filter loại bỏ ô trắng ra và copy được mà bạn. Mình nghĩ nó chẳng tốn bao nhiêu thời gian của bạn đâu.

 

[loading860]

Dạ đúng rồi , nhưng điều kiện là TK và MK phải khớp nhau và vì có những cái thiếu nên khi kéo xuống làm sao nó chính xác ô mình cần , và có những cái TK là khoản trống thì mình vẫn nhận , cách của anh em mần ra đc nhưng khi copy phần MK thì nó lại sai lệch với phần TK anh ơi

 

[siwtom]

Dạ rồi , em thiếu cái file

Tôi chỉ xét tới dòng 1000. Bạn tự sửa cho thích hợp với dữ liệu của mình

Đặt name tmp

=IF((LEFT(Sheet1!$A$2:$A$1000;2)="TK")+(LEFT(Sheet1!$A$3:$A$1001;2)="MK")=2;ROW(Sheet1!$1:$999);"")

Công thức cho B2

=MID(INDEX($A$2:$A$1000;SMALL(tmp;ROWS($1:1)));5;50)

Công thức cho C2

=MID(INDEX($A$2:$A$1000;SMALL(tmp;ROWS($1:1))+1);5;50)

Kéo xuống dưới tới khi gặp #NUM!
------------------

Hoặc bạn dùng 1 công thức duy nhất cho ô B2

=MID(OFFSET(INDEX($A$2:$A$1000;SMALL(tmp;ROWS($1:1)));COLUMNS($A:A)-1;0);5;50)

Kéo sang phải và xuống dưới tới khi gặp #NUM!

 

[Bảy Dzõ]

Tôi chỉ xét tới dòng 1000. Bạn tự sửa cho thích hợp với dữ liệu của mình

Đặt name tmp

=IF((LEFT(Sheet1!$A$2:$A$1000;2)="TK")+(LEFT(Sheet1!$A$3:$A$1001;2)="MK")=2;ROW(Sheet1!$1:$999);"")

Công thức cho B2

=MID(INDEX($A$2:$A$1000;SMALL(tmp;ROWS($1:1)));5;50)

Công thức cho C2

=MID(INDEX($A$2:$A$1000;SMALL(tmp;ROWS($1:1))+1);5;50)

Kéo xuống dưới tới khi gặp #NUM!
------------------

Hoặc bạn dùng 1 công thức duy nhất cho ô B2

=MID(OFFSET(INDEX($A$2:$A$1000;SMALL(tmp;ROWS($1:1)));COLUMNS($A:A)-1;0);5;50)

Kéo sang phải và xuống dưới tới khi gặp #NUM!

Khi em đặt name hay dùng công thức Offset cho ô B2 thì excel báo lỗi thưa thầy (excel highlight phan $A$1000). Nhờ thầy coi giúp.

 

[siwtom]

Khi em đặt name hay dùng công thức Offset cho ô B2 thì excel báo lỗi thưa thầy (excel highlight phan $A$1000). Nhờ thầy coi giúp.

Tôi làm có thấy lỗi gì đâu.
Chú ý: Nếu trong công thức dấu phân cách các parametr là ";" (chấm phẩy) không được chấp nhận thì thay bằng "," (phẩy). Cái này tùy máy

 

[Bảy Dzõ]

Tôi làm có thấy lỗi gì đâu.
Chú ý: Nếu trong công thức dấu phân cách các parametr là ";" (chấm phẩy) không được chấp nhận thì thay bằng "," (phẩy). Cái này tùy máy

Dạ cám ơn thầy, đúng thế máy em chấp nhận dấu "," để phân cách các parameter.

 

[siwtom]

Dạ cám ơn thầy, đúng thế máy em chấp nhận dấu "," để phân cách các parameter.

Cái này nó có lôgic của nó. Không biết lôgic của Excel như thế nào nhưng của tôi là như sau:

Rõ ràng nếu trong CP thiết lập dấu "," là dấu thập phân thì chắc chắn Excel không chấp nhận "," là dấu phân cách các parameter. Vì sao? Vì nếu trong công thức có vd. 2 parameter Optional thì vd. 1,2 phải được hiểu như thế nào? Là chỉ có 1 parameter là 1,2 (cái kia optional) hay đó là 2 parameter 1 và 2? Như vậy dùng "," sẽ dẫn tới sự mập mờ, không rõ ràng.

Cũng máy của bạn nhưng nếu bạn sửa thiết lập trong CP thì bạn dùng được dấu ";" là dấu phân cách trong công thức

 

[loading860]

Cám ơn Thầy siwtom và anh Bảy Dzõ nhìu .
Em không hiểu rõ phần Đặt name tmp lắm
và khi em làm cách 2 thầy hướng dẫn thì excel báo lỗi thưa thầy SMALL(tmp;ROWS($1:1)). Nhờ thầy coi giúp.

 

[siwtom]

Cám ơn Thầy siwtom và anh Bảy Dzõ nhìu .
Em không hiểu rõ phần Đặt name tmp lắm
và khi em làm cách 2 thầy hướng dẫn thì excel báo lỗi thưa thầy SMALL(tmp;ROWS($1:1)). Nhờ thầy coi giúp.

Trong lời giải của tôi thì cách nào cũng cần đặt name. Nếu chưa biết cách đặt name thì phải học vì tương lai bạn sẽ cần. Tôi dùng Excel 2007 nên tôi hướng dẫn bằng 2007.

Mở thẻ Formulas --> nhấn vào mục Name Manager --> trong cửa sổ Name Manager nhấn vào New --> trong cửa sổ New Name ở trường Name nhập tmp, và ở trường Refers to thì nhập

=IF((LEFT(Sheet1!$A$2:$A$1000;2)="TK")+(LEFT(Sheet1!$A$3:$A$1001;2)="MK")=2;ROW(Sheet1!$1:$999);"")

--> nhấn OK để đóng cửa sổ New Name --> nhấn Close để đóng cửa sổ Name Manager
--------------

Nếu bạn vẫn chưa biết cách đặt name thì bạn học sau vậy. Trong khi chưa biết thì bạn dùng công thức cho B2

=MID(OFFSET(INDEX($A$2:$A$1000;SMALL(IF((LEFT($A$2:$A$1000;2)="TK")+(LEFT($A$3:$A$1001;2)="MK")=2;ROW($1:$999);"");
ROWS($1:1)));COLUMNS($A:A)-1;0);5;50)

Kết thúc bằng Ctrl+Shift+Enter
Kéo sang phải và xuống dưới

 

[loading860]

Dạ đc rồi , cám ơn thầy ạ , còn ô C2 mình viết như thế nào thầy , em ngố quá , cóp qua nó cũng báo lỗi

 

[siwtom]

Dạ đc rồi , cám ơn thầy ạ , còn ô C2 mình viết như thế nào thầy , em ngố quá , cóp qua nó cũng báo lỗi

Công thức trên là bạn tự bịa ra chứ tôi có cho bạn công thức như thế đâu?
---------------

Nếu bạn dùng cách 2 thì sau khi bạn nhập công thức cho B2 thì bạn để ý thấy ở góc dưới bên phài có một chấm to hình chữ nhật. Bạn di con trỏ vào đấy thì nó sẽ chuyển thành dấu "+". Lúc đó nhấn và giữ chuột rồi kéo sang ô C2. Sao đó lại "tóm" góc dưới bên phải rồi nhấn chuột và kéo xuống dưới cho tới khi gặp #NUM! thì dừng.

Những cái cơ bản mà cứ phải giảng cho bạn thì khổ tôi quá.

 

[loading860]

dạ đc rồi ạ , em cám ơn thầy

 

[loading860]

Thầy ơi , con không hiểu tại sao tới B501 thì nó báo lỗi mặt dù có nhiều hơn gấp đôi của B501 , hay chắc con không hiểu rõ công thức thầy viết nên ko biết sửa sao , khi con sửa =MID(OFFSET(INDEX($A$2:$A$1000,SMALL(IF((LEFT($A$2:$A$1000,2)="TK")+(LEFT($A$3:$A$1001,2)="MK")=2,ROW($1:$999),""),ROWS($1:1))),COLUMNS($A:A)-1,0),5,50) thành 10000 và 10001 thì nó báo lỗi

 

[loading860]

Thầy ơi , con không hiểu tại sao tới B501 thì nó báo lỗi mặt dù có nhiều hơn gấp đôi của B501 , hay chắc con không hiểu rõ công thức thầy viết nên ko biết sửa sao , khi con sửa B502=MID(OFFSET(INDEX($A$2:$A$1000,SMALL(IF((LEFT($A$2:$A$1000,2)="TK")+(LEFT($A$3:$A$1001,2)="MK")=2,ROW($1:$999),""),ROWS($1:1))),COLUMNS($A:A)-1,0),5,50) thành 10000 và 10001 thì nó báo lỗi

 

[Bảy Dzõ]

Thầy ơi , con không hiểu tại sao tới B501 thì nó báo lỗi mặt dù có nhiều hơn gấp đôi của B501 , hay chắc con không hiểu rõ công thức thầy viết nên ko biết sửa sao , khi con sửa =MID(OFFSET(INDEX($A$2:$A$1000,SMALL(IF((LEFT($A$2:$A$1000,2)="TK")+(LEFT($A$3:$A$1001,2)="MK")=2,ROW($1:$999),""),ROWS($1:1))),COLUMNS($A:A)-1,0),5,50) thành 10000 và 10001 thì nó báo lỗi

Bạn sửa công thức ở ngoài nhưng hình như bạn đã quên sửa theo trong phần name bạn ạ. Và bạn kiểm tra lại hàm theo như mình thấy đk trong ROW chỉ xét đến cell 501 thôi. Bạn xem và chỉnh lại xem sao vì mình lên bằng đt nên làm Gia Cát Dự và hy vọng đúng.

 

[loading860]

sửa phần name ở đâu anh ??? em mới gắn lại cái file r`

 

[siwtom]

Thầy ơi , con không hiểu tại sao tới B501 thì nó báo lỗi mặt dù có nhiều hơn gấp đôi của B501 , hay chắc con không hiểu rõ công thức thầy viết nên ko biết sửa sao , khi con sửa =MID(OFFSET(INDEX($A$2:$A$1000,SMALL(IF((LEFT($A$2:$A$1000,2)="TK")+(LEFT($A$3:$A$1001,2)="MK")=2,ROW($1:$999),""),ROWS($1:1))),COLUMNS($A:A)-1,0),5,50) thành 10000 và 10001 thì nó báo lỗi

Bạn sửa thành

=MID(OFFSET(INDEX($A$2:$A$[B][COLOR=#ff0000]10000[/COLOR][/B],SMALL(IF((LEFT($A$2:$A$[B][COLOR=#ff0000]10000[/COLOR][/B],2)="TK")+(LEFT($A$3:$A$[B][COLOR=#ff0000]10001[/COLOR][/B],2)="MK")=2,ROW($1:$[B][COLOR=#ff0000]9999[/COLOR][/B]),""),
ROWS($1:1))),COLUMNS($A:A )-1,0),5,50)

Những chỗ sửa tôi tô mầu đỏ
Nếu dữ liệu nhiều quá thì có lẽ nên dùng hàm tự tạo?
-----

Không nhất thiết phải sửa thành 10000. Nếu bạn chỉ có tới vd. A1020 thì nhập 1020, 1020, 1021, 1019.
Tôi không dùng code để xác định dòng cuối cho code đơn giản

 

[siwtom]

Bạn sửa công thức ở ngoài nhưng hình như bạn đã quên sửa theo trong phần name bạn ạ. Và bạn kiểm tra lại hàm theo như mình thấy đk trong ROW chỉ xét đến cell 501 thôi. Bạn xem và chỉnh lại xem sao vì mình lên bằng đt nên làm Gia Cát Dự và hy vọng đúng.

Công thức trong đoạn trích có dùng name đâu? Đó là phiên bản không dùng name đề phòng người hỏi chưa biết tạo name mà.

 

[Bảy Dzõ]

Bạn sửa thành

=MID(OFFSET(INDEX($A$2:$A$[B][COLOR=#ff0000]10000[/COLOR][/B],SMALL(IF((LEFT($A$2:$A$[B][COLOR=#ff0000]10000[/COLOR][/B],2)="TK")+(LEFT($A$3:$A$[B][COLOR=#ff0000]10001[/COLOR][/B],2)="MK")=2,ROW($1:$[B][COLOR=#ff0000]9999[/COLOR][/B]),""),ROWS($1:1))),COLUMNS($A:A )-1,0),5,50)

Những chỗ sửa tôi tô mầu đỏ
Nếu dữ liệu nhiều quá thì có lẽ nên dùng hàm tự tạo?

Thầy ơi cho em hỏi lý do tại sao bạn ấy kéo xuống đến cell 501 thì bị lỗi vậy thưa thầy trong khi mình set dữ liệu đến 1000. Nếu như thế thầy sửa thành 10000 vậy liệu nếu đến cell 5001 có thể bị tiếp ko thưa thầy? Mong thầy thông cảm em dốt nên hay hỏi ngu nếu sai mong bỏ qua nhưng thật lòng muốn tìm hiểu để rõ và ứng dụng nếu gặp. Cảm ơn thầy.

 

[siwtom]

Thầy ơi cho em hỏi lý do tại sao bạn ấy kéo xuống đến cell 501 thì bị lỗi vậy thưa thầy trong khi mình set dữ liệu đến 1000. Nếu như thế thầy sửa thành 10000 vậy liệu nếu đến cell 5001 có thể bị tiếp ko thưa thầy? Mong thầy thông cảm em dốt nên hay hỏi ngu nếu sai mong bỏ qua nhưng thật lòng muốn tìm hiểu để rõ và ứng dụng nếu gặp. Cảm ơn thầy.

Hôm nay ngày nghỉ nên tôi làm vài lon. Tôi không suy nghĩ gì cả. Lỗi là người hỏi nói thế chứ tôi và bạn có tập tin nào đâu để mà biết thực hư?

 

[nghiaphuc]

Mọi người bàn về công thức cũng khá nhiều rồi. Mình xin góp vui một tham khảo nữa cho chủ thớt bằng VBA (bữa nay đang học Array nên nhìn thứ gì cũng nghĩ đến Array cả!):
Mà bài này đúng ra là bài toán tách dữ liệu chứ đâu phải lọc và sắp xếp nhỉ?!
[GPECODE=vb]Sub TachDuLieu()
Dim Temp, Arr(), i As Long, n As Long
Temp = WorksheetFunction.Transpose(Sheet1.[A2:A5000])
ReDim Arr(1 To UBound(Temp), 1 To 2)
Do
Do
i = i + 1
Loop Until Left(Temp(i), 3) <> Left(Temp(i + 1), 3)
n = n + 1
Arr(n, 1) = LTrim(Replace(Temp(i), "TK:", ""))
Do
i = i + 1
Loop Until Left(Temp(i), 3) <> Left(Temp(i + 1), 3)
Arr(n, 2) = LTrim(Replace(Temp(i), "MK:", ""))
Loop Until Len(Temp(i + 1)) = 0
Sheet1.[B2:C5000].Clear
With Sheet1.[B2].Resize(n, 2)
.NumberFormat = "@"
.Value = Arr
End With
End Sub[/GPECODE]