Tìm hàm cho bài toán thống kê xuất nhập liên quan đến thời gian. (1 người xem)

[fibo8x]

Mình tìm mãi ko ra, nhưng chắc chắn là có cách giải quyết.
Các bác giúp với,
1 sheet là thống kê hàng xuất nhập theo ngày giờ,
1 sheet cần tổng hợp theo tiêu chí đơn giản là: buổi sáng 1 hôm nào đấy đã từng xuất, hay nhập thôi.
Chi tiết trong file ạ, cảm ơn mọi người.

 

[dazkangel]

Gửi bạn mình mới thử cũng không biết hợp ý bạn chưa, cân nhắc sử dụng Merge ở đây mình có sử dụng thủ thuật để nó hiểu ô bên dưới vẫn giữ giá trị bên trên.
Mình chỉ để vùng tìm kiếm đến dòng 15 bạn có thể tăng tùy ý và bên name(Ctrl+F3) bạn cũng tăng thêm nếu muốn.

 

[fibo8x]

Cảm ơn bạn nhé, mình áp dụng được rồi, hình như mỗi người đều cần có 1 cột phụ nữa như cột D của sheet A để có thông số phụ "a","p" cho hàm match thì phải.
à, mà thủ thuật để nó hiểu ô bên dưới vẫn giữ giá trị bên trên là như nào vậy bạn.

 

[dazkangel]

Thủ thuật thì mình mới tìm được bài mới bạn xem nhé:
http://hocexcelcoban.com/lọc-cac-vung-dũ-liẹu-co-trọn-merge-trong-excel/
Riêng cột phụ bạn có thể sử dụng công thức so sánh với thời gian nhập hàng cái đó mình chưa để ý nên chưa làm :v
Đây là hàm gọi ra chữ PM, AM luôn.
=TEXT(J15,"AM/PM")

 

[fibo8x]

Bạn thử xem bây giờ nếu giả dụ xóa bỏ cột B,C,D bên sheet A; và xóa cột B bên sheet B đi thì có phức tạp hơn nhiều không bạn?
Thực ra để như này với mình cũng là ok lắm rồi, tiện có ví dụ đây thì mình học hỏi bạn thêm thôi, excel vô biên thật, mình mù mờ lắm.

 

[dazkangel]

Đây viết lại cho bạn không cần bảng phụ, tuy nhiên có khi nào xảy ra cùng 1 buổi mà vừa xuất vừa nhập không ?
C2=IFERROR(IF(INDEX(INDIRECT($C$1),MATCH(A2&B2,A!$A$2:$A$15&TEXT(INDIRECT($C$1&"X"),"AM/PM"),0),1)<0,"XUAT","NHAP"),"")
Nếu bỏ hàm if thì những ô không nhập time, nó sẽ hiểu là buổi sáng hết.

 

[fibo8x]

Cùng 1 buổi mà vừa xuất vừa nhập : Chắc chắn có! Chỉ hiếm thôi ^^.
Trong trường hợp này thì nếu xuất>nhập: ta coi là xuất và ngược lại.
Cái này nếu phức tạp chắc mình phải tạo thêm cột phụ hiệu số xuất và nhập bên ngoài phải ko bạn?
Với hàm bạn viết này thì nếu cùng một buổi có cả xuất và nhập thì nó cho kết quả như nào vậy?

 

[dazkangel]

Nếu không dùng cột phụ cũng được chỉ là công thức thôi nhưng rất dài và lằng ngoằng ..

 

[dazkangel]

Cách mới nhưng vì lớn hơn 0 là nhập, thì nhừng ô rỗng (là 0) cũng hiểu là xuất nên phải làm công thức thêm lần nữa hơi dài không cột phụ.

 

[fibo8x]

Tuyệt vời bạn à.
Cách mới này đầy đủ hơn. Mình áp dụng vào rất hợp ý.
Cảm ơn bạn nhiều lắm. Thank you much, bro!

 

[fibo8x]

Bạn ơi, trường hợp nếu cùng một buổi mà xuất và nhập bằng nhau, mình muốn nó hiện lên chữ "E" thay vì để trống,
thì sửa lại công thức như nào nhỉ?

 

[fibo8x]

Liệu có phức tạp quá ko khi mình muốn giả dụ nếu cùng 1 buổi mà xuất>nhập thì nó hiển thị là "XUAT SIEU", ngược lại là "NHAP SIEU", còn bằng nhau thì là chữ "E".

 

[dazkangel]

Ý bạn có phải nếu chỉ nhập hoặc xuất trong 1 buổi thì là xuất nhập, còn nếu có cả 2 và xuất lớn hơn thì xuất siêu, xuất nhỏ hơn là nhập siêu và bằng nhau là E.
Nếu vậy công thức sẽ rất dài nếu không có cột phụ.
Và riêng về 1 buổi mà có từ 2 đến 3 lần nhập hoặc xuất thì phải tìm chính xác từng ô trong 1 bảng công thức cũng rất dài.

 

[eke_rula]

Liệu có phức tạp quá ko khi mình muốn giả dụ nếu cùng 1 buổi mà xuất>nhập thì nó hiển thị là "XUAT SIEU", ngược lại là "NHAP SIEU", còn bằng nhau thì là chữ "E".

Làm đại xem đúng ý bạn không nhe, bạn dùng CT mảng này ở C2:

C2=IFERROR(IF(1/SUM((LOOKUP(ROW($1:$13),MATCH(A!$A$3:$A$15,A!$A$3:$A$15,),A!$A$3:$A$15)=LOOKUP(10^6,$A$2:A2))*(LEFT(B2)=A!$D$3:$D$15)*A!$E$3:$E$15)>0,"Nhap sieu","Xuat sieu"),"E")

Ctrl+Shift+Enter rồi fill xuống!!!

 

[fibo8x]

Ý bạn có phải nếu chỉ nhập hoặc xuất trong 1 buổi thì là xuất nhập, còn nếu có cả 2 và xuất lớn hơn thì xuất siêu, xuất nhỏ hơn là nhập siêu và bằng nhau là E.
Nếu vậy công thức sẽ rất dài nếu không có cột phụ.
Và riêng về 1 buổi mà có từ 2 đến 3 lần nhập hoặc xuất thì phải tìm chính xác từng ô trong 1 bảng công thức cũng rất dài.

Đúng rồi, ý mình muốn vậy.
Thực ra cái này là mình phát triển ý thêm lên thôi, lúc đầu mình cũng không có nghĩ tới, chỉ cần như trước là được.
Nhưng bạn thử xem giúp mình với ý mới này nếu cần tạo thêm cột phụ nữa để thực hiện được thì nên làm như nào sẽ gọn nhất.

 

[fibo8x]

Làm đại xem đúng ý bạn không nhe, bạn dùng CT mảng này ở C2:

C2=IFERROR(IF(1/SUM((LOOKUP(ROW($1:$13),MATCH(A!$A$3:$A$15,A!$A$3:$A$15,),A!$A$3:$A$15)=LOOKUP(10^6,$A$2:A2))*(LEFT(B2)=A!$D$3:$D$15)*A!$E$3:$E$15)>0,"Nhap sieu","Xuat sieu"),"E")

Ctrl+Shift+Enter rồi fill xuống!!!

Cái này mình đưa vào, nó cho ra kết quả toàn là "E" thôi.
Với lại hình như mất cái tiêu chí đầu tiên là "XUAT" hoặc "NHAP" rồi.

 

[eke_rula]

Cái này mình đưa vào, nó cho ra kết quả toàn là "E" thôi.
Với lại hình như mất cái tiêu chí đầu tiên là "XUAT" hoặc "NHAP" rồi.

CT này là CT mảng nên bạn phải bấm CTrl+Shift+Enter mới ra kết quả đúng được.
Mình chỉ đoán thôi, bạn làm lại file rồi điền lại kết quả bạn mong muốn rồi đưa đưa lên xem !!!

 

[fibo8x]

Đây bạn, mình tạo lại file có nhiều dữ liệu của việc xuất nhập trong cùng một buổi hơn.
Kết quả mong muốn mình để nó ở cột E bên sheet1, mình đưa công thức trên vào vẫn chỉ hiện kết quả là "E" bạn à, bạn xem nó bị sao vậy?

 

[eke_rula]

Đây bạn, mình tạo lại file có nhiều dữ liệu của việc xuất nhập trong cùng một buổi hơn.
Kết quả mong muốn mình để nó ở cột E bên sheet1, mình đưa công thức trên vào vẫn chỉ hiện kết quả là "E" bạn à, bạn xem nó bị sao vậy?

Vậy đặt Name cho gọn, tại C2 bạn đặt Name như sau:

Name=(LOOKUP(ROW(tonghop!$1:$1001),MATCH(solieu!$A$3:$A$1003,solieu!$A$3:$A$1003,),solieu!$A$3:$A$1003)=LOOKUP(10^6,tonghop!$A$2:$A2))*(tonghop!$B2=TEXT(solieu!$D$3:$D$1003,"AM/PM"))*OFFSET(solieu!$A$3:$A$1003,,MATCH(tonghop!C$1,solieu!$B$1:$W$1,))

Sau đó dùng CT sau ở C2:

=CHOOSE(SIGN(SUM(name))+IF((SUM(--(name>0)))*(SUM(--(name<0))),3,)+2,"Xuat","","Nhap","Xuat sieu","E","Nhap sieu")

Ctrl+Shift+Enter rồi fill xuống, rồi fill sang phải!!!
P/s: do máy mình đang định dạng tiếng việt nên mốc thời gian sẽ là "SA/CH", nếu bạn nhận file vẫn không ra số liệu bạn sửa cột B lại là "SA/CH" nhe bạn!!!

 

[fibo8x]

Bạn ơi, file hiển thị ở máy mình vẫn ok.
Nhưng kết quả cho "HUNG" ở cột E sheet tổng hợp (ô E9) đáng lẽ phải cho ra kết quả là "E" thay vì là "Nhập siêu" chứ.
Bạn coi lại giúp mình xem tại sao vậy?

 

[fibo8x]

Bạn ơi, mình thấy khi mình thay thêm, bớt dữ liệu vào thì chỉ có duy nhất cột C cho ra kết quả luôn đúng thôi.
Còn các cột khác D,E,F có lúc đúng, nhưng vẫn có lúc sai.. (

 

[eke_rula]

Bạn ơi, file hiển thị ở máy mình vẫn ok.
Nhưng kết quả cho "HUNG" ở cột E sheet tổng hợp (ô E9) đáng lẽ phải cho ra kết quả là "E" thay vì là "Nhập siêu" chứ.
Bạn coi lại giúp mình xem tại sao vậy?

Ah, do cột thời gian của bạn thay đổi theo từng người, tại C2 bạn sửa CT ở Name như sau:

Name=(LOOKUP(ROW(tonghop!$1:$1001),MATCH(solieu!$A$3:$A$1003,solieu!$A$3:$A$1003,),solieu!$A$3:$A$1003)=LOOKUP(10^6,tonghop!$A$2:$A2))*(tonghop!$B2=TEXT(OFFSET(solieu!$A$3:$A$1003,,MATCH(tonghop!C$1,solieu!$B$1:$W$1,)+2),"AM/PM"))*OFFSET(solieu!$A$3:$A$1003,,MATCH(tonghop!C$1,solieu!$B$1:$W$1,))

 

[fibo8x]

Trời ơi, quá tuyệt vời.
Sao bạn có thể giỏi quá vậy, triệu like!
Cảm ơn bạn rất nhiều nhé. Thank you so much, bro man.