Giúp đỡ lọc dữ liệu bằng hàm

[devil.hunter]

Chào các ace trong forums!

Em có dữ liệu như trong file. Sheet 1 là dữ liệu thô, em muốn lọc theo mã nhân viên các dữ liệu ngày, giờ vào giờ ra sang các cột như bên sheet2.

Cảm ơn ace đã giúp đỡ.

 

[CHAOQUAY]

Chào các ace trong forums!

Em có dữ liệu như trong file. Sheet 1 là dữ liệu thô, em muốn lọc theo mã nhân viên các dữ liệu ngày, giờ vào giờ ra sang các cột như bên sheet2.

Cảm ơn ace đã giúp đỡ.

Dùng cột phụ:
Tại I4, công thức mảng, kết thúc = ctrl+shift+enter

=IF(ROWS($I$4:I4)>COUNTIF(Sheet1!$A$2:$A$190,$B$2),"",SMALL(IF(Sheet1!$A$2:$A$190=$B$2,ROW(Sheet1!$A$2:$A$190),""),ROWS($I$4:I4)))

Tại A4, công thức thường

=IF($I4="","",INDEX(Sheet1!$E$2:$E$190,$I4))

Tại B4: công thức thường

=IF($I4="","",IF(INDEX(Sheet1!$H$2:$H$190,$I4)=B$3,B$3,""))

copy B4 sang C4

 

[devil.hunter]

Dùng cột phụ:
Tại I4, công thức mảng, kết thúc = ctrl+shift+enter

=IF(ROWS($I$4:I4)>COUNTIF(Sheet1!$A$2:$A$190,$B$2),"",SMALL(IF(Sheet1!$A$2:$A$190=$B$2,ROW(Sheet1!$A$2:$A$190),""),ROWS($I$4:I4)))

Tại A4, công thức thường

=IF($I4="","",INDEX(Sheet1!$E$2:$E$190,$I4))

Tại B4: công thức thường

=IF($I4="","",IF(INDEX(Sheet1!$H$2:$H$190,$I4)=B$3,B$3,""))

copy B4 sang C4

Cảm ơn bác!Cột A thì OK rồi nhưng cột B,C chưa đc ạ. Cụ thể em muốn lấy cái thời gian vào, ra cơ ạ chứ ko phải chữ vào vào ra. Thời gian nó nằm ờ cột F sheet 1 ạ.

 

[CHAOQUAY]

Cảm ơn bác!Cột A thì OK rồi nhưng cột B,C chưa đc ạ. Cụ thể em muốn lấy cái thời gian vào, ra cơ ạ chứ ko phải chữ vào vào ra. Thời gian nó nằm ờ cột F sheet 1 ạ.

Bạn sửa B4 như bên dưới, copy sang C4

=IF($I4="","",IF(INDEX(Sheet1!$H$2:$H$190,$I4)=B$3,INDEX(Sheet1!$F$2:$F$190,$I4),""))

 

[batman1]

Cảm ơn bác!Cột A thì OK rồi

Cột A cũng vẫn sai. Hiện thời A6 = 02/03/2022 12:00:00 AM. Kết quả này là sai.

Trong Sheet1 dòng 3 và 7 tương ứng với Vào - Ra ngày 01/03, dòng 9, 13 tương ứng với Vào - Ra ngày 02/03. Tức A4 = 01/03/2022 và A5 = 02/03/2022. Do vậy phải có A6 = 03/03/2022 - tương ứng với dòng 16 và 18.

Các A7, A8, ... cũng đều lệch 1 ngày. Lý do là cột I trả về chỉ số dòng (tính từ 1) trên sheet1 trong khi đó hàm INDEX trong cột A lấy từ vùng Sheet1!$E$2:$E$190, tức lệch 1 dòng.

Bạn sửa B4 như bên dưới, copy sang C4

=IF($I4="","",IF(INDEX(Sheet1!$H$2:$H$190,$I4)=B$3,INDEX(Sheet1!$F$2:$F$190,$I4),""))

Nếu cứ 2 dòng là 1 ngày Vào - Ra thì kết quả vẫn kỳ lạ. Trước hết dễ thấy là ngày 01/03/2022 chỉ có vào mà không có ra. Sau đó ngày 02/03/2022 có 2 lần vào mà không ra lần nào



Tôi đề nghị các công thức như sau, tất cả kết thúc bằng Enter.
I4

=IFERROR(AGGREGATE(15,6,ROW($A$1:$A$190)/(Sheet1!$A$1:$A$190=$B$2),ROWS($I$4:I4)),"")

A4

=IFERROR(INDEX(Sheet1!$E$1:$E$190,INDEX(I$4:I$1000,ROWS(A$1:A1)*2)),"")

B4

=IF($A4="","",INDEX(Sheet1!$F$1:$F$190,INDEX(I$4:I$1000,(ROWS(A$1:A1)-1)*2+1)))

C4

=IF($A4="","",INDEX(Sheet1!$F$1:$F$190,INDEX(I$4:I$1000,ROWS(A$1:A1)*2)))

 

[CHAOQUAY]

@devil.hunter Công thức bài 2, 4 bì sai vì chưa trừ số dòng ngoài vùng dữ liệu như bài 5 đã viết, bạn dùng công thức bài 5 nhé.

---
@batman1 : Công thức của bác thì chuẩn rồi, nhưng đọc thấy vào vào ra ra nhiều quá chóng hết cả mặt.

Nhưng mà hình như vào - ra không lúc nào cũng là 1 cặp thì phải, như ngày 15/03/2022, "Trần Thị Loan" vào - ra - vào, là vào 2 lần mà ra có 1 phát thôi, chắc là "ra" xong lại liều chui vào rồi chắc mệt ra không nổi nữa

 

[devil.hunter]

Cột A cũng vẫn sai. Hiện thời A6 = 02/03/2022 12:00:00 AM. Kết quả này là sai.

Trong Sheet1 dòng 3 và 7 tương ứng với Vào - Ra ngày 01/03, dòng 9, 13 tương ứng với Vào - Ra ngày 02/03. Tức A4 = 01/03/2022 và A5 = 02/03/2022. Do vậy phải có A6 = 03/03/2022 - tương ứng với dòng 16 và 18.

Các A7, A8, ... cũng đều lệch 1 ngày. Lý do là cột I trả về chỉ số dòng (tính từ 1) trên sheet1 trong khi đó hàm INDEX trong cột A lấy từ vùng Sheet1!$E$2:$E$190, tức lệch 1 dòng.


Nếu cứ 2 dòng là 1 ngày Vào - Ra thì kết quả vẫn kỳ lạ. Trước hết dễ thấy là ngày 01/03/2022 chỉ có vào mà không có ra. Sau đó ngày 02/03/2022 có 2 lần vào mà không ra lần nào

View attachment 273990

Tôi đề nghị các công thức như sau, tất cả kết thúc bằng Enter.
I4

=IFERROR(AGGREGATE(15,6,ROW($A$1:$A$190)/(Sheet1!$A$1:$A$190=$B$2),ROWS($I$4:I4)),"")

A4

=IFERROR(INDEX(Sheet1!$E$1:$E$190,INDEX(I$4:I$1000,ROWS(A$1:A1)*2)),"")

B4

=IF($A4="","",INDEX(Sheet1!$F$1:$F$190,INDEX(I$4:I$1000,(ROWS(A$1:A1)-1)*2+1)))

C4

=IF($A4="","",INDEX(Sheet1!$F$1:$F$190,INDEX(I$4:I$1000,ROWS(A$1:A1)*2)))

cảm ơn các ae nhiều ạ!

 

[HieuCD]

Cột A cũng vẫn sai. Hiện thời A6 = 02/03/2022 12:00:00 AM. Kết quả này là sai.

Trong Sheet1 dòng 3 và 7 tương ứng với Vào - Ra ngày 01/03, dòng 9, 13 tương ứng với Vào - Ra ngày 02/03. Tức A4 = 01/03/2022 và A5 = 02/03/2022. Do vậy phải có A6 = 03/03/2022 - tương ứng với dòng 16 và 18.

Các A7, A8, ... cũng đều lệch 1 ngày. Lý do là cột I trả về chỉ số dòng (tính từ 1) trên sheet1 trong khi đó hàm INDEX trong cột A lấy từ vùng Sheet1!$E$2:$E$190, tức lệch 1 dòng.


Nếu cứ 2 dòng là 1 ngày Vào - Ra thì kết quả vẫn kỳ lạ. Trước hết dễ thấy là ngày 01/03/2022 chỉ có vào mà không có ra. Sau đó ngày 02/03/2022 có 2 lần vào mà không ra lần nào



Tôi đề nghị các công thức như sau, tất cả kết thúc bằng Enter.
I4

=IFERROR(AGGREGATE(15,6,ROW($A$1:$A$190)/(Sheet1!$A$1:$A$190=$B$2),ROWS($I$4:I4)),"")

A4

=IFERROR(INDEX(Sheet1!$E$1:$E$190,INDEX(I$4:I$1000,ROWS(A$1:A1)*2)),"")

B4

=IF($A4="","",INDEX(Sheet1!$F$1:$F$190,INDEX(I$4:I$1000,(ROWS(A$1:A1)-1)*2+1)))

C4

=IF($A4="","",INDEX(Sheet1!$F$1:$F$190,INDEX(I$4:I$1000,ROWS(A$1:A1)*2)))

Dữ liệu không chuẩn có vào nhưng không chịu ra như ngày '05/03/2022 dòng 29 nên công tèo, gộp vào và ra thành 1 dòng đòi hỏi phải dùng nhiều cột phụ, công thức cột phụ lấy số thứ tự dòng dùng hàm Row nếu dữ liệu nhiều sẽ khá nặng
Dung thuật toán điều chỉnh vùng lấy dữ liệu để lấy thứ tự dòng công thức sẽ nhẹ rất nhiều. Xem các công thức trong file

 

[batman1]

Dữ liệu không chuẩn có vào nhưng không chịu ra như ngày '05/03/2022 dòng 29 nên công tèo, gộp vào và ra thành 1 dòng đòi hỏi phải dùng nhiều cột phụ, công thức cột phụ lấy số thứ tự dòng dùng hàm Row nếu dữ liệu nhiều sẽ khá nặng
Dung thuật toán điều chỉnh vùng lấy dữ liệu để lấy thứ tự dòng công thức sẽ nhẹ rất nhiều. Xem các công thức trong file

Bạn ạ, bạn thích cứu hỏa thì là chuyện của bạn. Bạn có phải là sếp của tôi đâu mà bạn phán tôi phải làm thế này thế nọ.

Tôi chỉ viết công thức cho dữ liệu chuẩn thôi. Bạn muốn phục vụ cả̉ dữ liệu không chuẩn thì bạn cứ làm vì bạn có quyền đó. Nhưng đừng phán là tôi cũng phải có nhiệm vụ đó.

Nếu lần sau mà tôi không sai, chỉ là làm không theo ý bạn thì bạn không nên phán nữa. Vì tôi không thích những kiểu đeo bám của bạn. Bạn có quyền góp ý khi tôi sai, ai cũng có quyền đó. Nhưng đừng góp ý khi tôi làm khác bạn. Bạn cứ đề xuất giải pháp của mình thôi chứ sao lại trích bài của tôi. Tôi không có nhiệm vụ làm theo ý thích của bạn. Tôi đã cố tình tránh bạn nhưng bạn bám như con đỉa. Nói thế chắc bạn hiểu.

@batman1 : Công thức của bác thì chuẩn rồi, nhưng đọc thấy vào vào ra ra nhiều quá chóng hết cả mặt.
Nhưng mà hình như vào - ra không lúc nào cũng là 1 cặp thì phải, như ngày 15/03/2022, "Trần Thị Loan" vào - ra - vào, là vào 2 lần mà ra có 1 phát thôi, chắc là "ra" xong lại liều chui vào rồi chắc mệt ra không nổi nữa

Bạn cứ cười thải mái. Một nụ cười bằng mười thang thuốc bổ.

Công thức là một chuyện nhưng dữ liệu gốc không chuẩn thì vứt hết. Tôi viết công thức chỉ cho dữ liệu chuẩn.

Bạn kiểm tra nhé.

Với Đỗ Thị Loan có:

- 05/03/2022, 06/03/2022, 06/03/2022 (dòng 29, 30, 31) tương ứng với Vào, Vào, Ra. Như vậy sau khi Vào ở 05/03/2022 thì không có Ra và lại có Vào tiếp ở 06/03/2022

- 10/03/2022, 11/03/2022 (dòng 51, 54) tương ứng với Vào, Vào. Như vậy sau khi Vào ở 10/03/2022 thì không có Ra và lại có Vào tiếp ở 11/03/2022

- 12/03/2022, 13/03/2022 (dòng 62, 68) tương ứng với Vào, Vào. Như vậy sau khi Vào ở 12/03/2022 thì không có Ra và lại có Vào tiếp ở 13/03/2022

- 15/03/2022 có Vào và Ra ở dòng 83, 84. Sau đó cũng ngày 15/03/2022 lại Vào tiếp ở dòng 88, và tiếp theo Vào ở ngày 16/03/2022 ở dòng 92. Như thế sau khi Vào lần 2 ở ngày 15/03/2022 thì không có Ra mà lại có Vào tiếp ở ngày 16/03/2022.

...

 

[HieuCD]

Bạn ạ, bạn thích cứu hỏa thì là chuyện của bạn. Bạn có phải là sếp của tôi đâu mà bạn phán tôi phải làm thế này thế nọ.

Tôi chỉ viết công thức cho dữ liệu chuẩn thôi. Bạn muốn phục vụ cả̉ dữ liệu không chuẩn thì bạn cứ làm vì bạn có quyền đó. Nhưng đừng phán là tôi cũng phải có nhiệm vụ đó.

Nếu lần sau mà tôi không sai, chỉ là làm không theo ý bạn thì bạn không nên phán nữa. Vì tôi không thích những kiểu đeo bám của bạn. Bạn có quyền góp ý khi tôi sai, ai cũng có quyền đó. Nhưng đừng góp ý khi tôi làm khác bạn. Bạn cứ đề xuất giải pháp của mình thôi chứ sao lại trích bài của tôi. Tôi không có nhiệm vụ làm theo ý thích của bạn. Tôi đã cố tình tránh bạn nhưng bạn bám như con đỉa. Nói thế chắc bạn hiểu.


Bạn cứ cười thải mái. Một nụ cười bằng mười thang thuốc bổ.

Công thức là một chuyện nhưng dữ liệu gốc không chuẩn thì vứt hết. Tôi viết công thức chỉ cho dữ liệu chuẩn.

Bạn kiểm tra nhé.

Với Đỗ Thị Loan có:

- 05/03/2022, 06/03/2022, 06/03/2022 (dòng 29, 30, 31) tương ứng với Vào, Vào, Ra. Như vậy sau khi Vào ở 05/03/2022 thì không có Ra và lại có Vào tiếp ở 06/03/2022

- 10/03/2022, 11/03/2022 (dòng 51, 54) tương ứng với Vào, Vào. Như vậy sau khi Vào ở 10/03/2022 thì không có Ra và lại có Vào tiếp ở 11/03/2022

- 12/03/2022, 13/03/2022 (dòng 62, 68) tương ứng với Vào, Vào. Như vậy sau khi Vào ở 12/03/2022 thì không có Ra và lại có Vào tiếp ở 13/03/2022

- 15/03/2022 có Vào và Ra ở dòng 83, 84. Sau đó cũng ngày 15/03/2022 lại Vào tiếp ở dòng 88, và tiếp theo Vào ở ngày 16/03/2022 ở dòng 92. Như thế sau khi Vào lần 2 ở ngày 15/03/2022 thì không có Ra mà lại có Vào tiếp ở ngày 16/03/2022.

...

Mình góp ý công thức không đúng với dữ liệu thực tế "Dữ liệu không chuẩn có vào nhưng không chịu ra như ngày '05/03/2022 dòng 29" nếu không thích được góp ý thì dừng ở đây

 

[batman1]

Mình góp ý công thức không đúng với dữ liệu thực tế "Dữ liệu không chuẩn có vào nhưng không chịu ra như ngày '05/03/2022 dòng 29"

Thì tôi đã nói rõ là công thức tôi viết chỉ dùng cho dữ liệu chuẩn. Tôi không dọn vệ sinh khi người ta "ị" ra một đống.

Từ xưa tới nay tôi luôn yêu sách là dữ liệu gốc phải chuẩn. Tôi rất nhiều lần nói rõ là tôi chỉ chấp nhận dữ liệu gốc chuẩn, không bao giờ chấp nhận "xoay xở" với dữ liệu hổ lốn. Điển hình là không bao giờ tôi chấp nhận ngày tháng nhái, text giả bộ ngày tháng, để rồi mỗi khi dùng công thức lại phải "xoay xở" để công thức không bị lỗi. Chỉ cần đọc những bài tôi viết là biết không bao giờ tôi chấp nhận dữ liệu giả bộ chuẩn và "xoay xở".

nếu không thích được góp ý thì dừng ở đây

Vấn đề là góp ý khi người ta sai chứ không góp ý khi người ta làm khác mình, khi người ta có tiêu chí của người ta, có đòi hỏi của người ta, khác với tiêu chí và đòi hỏi của mình.

Mà tốt nhất tôi nên tránh bạn và bạn nên tránh tôi. Chúng ta không có gì để nói với nhau đâu.

Tốt nhất là "nước sông không phạm nước giếng"