Nhờ hỗ trợ công thức gộp dòng có điều kiện và khoảng thời gian không trùng nhau

[Binbo2020]

Mình có bài toán lập kế hoạch sản xuất theo khoảng thời gian, có nhiều sản phẩm và nhiều máy chạy trong các khoảng thời gian khác nhau, muốn sắp các sản phẩm chạy cùng máy nếu không trùng khoảng thời gian thì điền dữ liệu liền nhau, nếu trùng thời gian thì điền xuống dòng dưới. Bài này diễn tả hơi khó hiểu chút, các bạn xem trong ảnh và file sẽ dễ hiểu hơn, phần bôi vàng là kết quả mong muốn. (Dữ liệu ban đầu là vùng A1:E12, còn các phần khác mình đang lập công thức để tư duy dần ra kết quả).
(Mới nhìn tưởng dễ mà viết công thức mới thấy ..... )

 

[Phan Thế Hiệp]

Mình có bài toán lập kế hoạch sản xuất theo khoảng thời gian, có nhiều sản phẩm và nhiều máy chạy trong các khoảng thời gian khác nhau, muốn sắp các sản phẩm chạy cùng máy nếu không trùng khoảng thời gian thì điền dữ liệu liền nhau, nếu trùng thời gian thì điền xuống dòng dưới. Bài này diễn tả hơi khó hiểu chút, các bạn xem trong ảnh và file sẽ dễ hiểu hơn, phần bôi vàng là kết quả mong muốn. (Dữ liệu ban đầu là vùng A1:E12, còn các phần khác mình đang lập công thức để tư duy dần ra kết quả).
(Mới nhìn tưởng dễ mà viết công thức mới thấy ..... )
View attachment 258806

Bài này nên thực hiện bằng VBA.
Miễn cưỡng làm công thức thì phải có:

1/ Bảng phụ để đếm (xem bảng B14: C16 file kèm)

C14=MAX(FREQUENCY(IFERROR(TEXT(ROW($1:$1)+$D$2:$D$12+COLUMN(A:AE)-2,"[<"&$E$2:$E$12&"]0;")/($B$2:$B$12=B14),""),ROW(INDIRECT(MIN($D$2:$D$12)&":"&MAX($E$2:$E$12)))))

Ctrl+Shift+Enter.

2/ Thực hiện liệt kê:

B26=T(IFERROR(IF(VLOOKUP(B25,B$14:C$16,2,)=COUNTIF(B$25:B25,B25),INDEX($B$14:$B$16,MATCH(B25,B$14:B$16,)+1),B25),B14))

Enter. Fill xuống.

F26=IFERROR(INDEX($A$2:$A$12&"-"&$C$2:$C$12,AGGREGATE(15,6,ROW($1:$100)/(1*TEXT(ROW($1:$1)+$D$2:$D$20+COLUMN($A:$AE)-2,"[<"&$E$2:$E$12&"]0;")=F$1)/($B$2:$B$12=$B26),COUNTIF($B$26:$B26,$B26))),"")

Enter. Fill xuống và 'sang ngang'.

Thân

 

[Binbo2020]

2/ Thực hiện liệt kê:

Công thức này giống công thức của mình rồi cứ dòng nào trống là bị nhảy lên trên
Công thức của mình ra
Của bạn
Còn kết quả mình mong muốn là
Mình đang bí ở chỗ nếu sản phẩm đã có ở dòng dưới rồi thì nó phải liền nhau chứ không phải cứ dòng trên trống là nhảy lên

 

[huuthang_bd]

Phải ăn gian xíu mới xong.

 

[Binbo2020]

Phải ăn gian xíu mới xong.

Công thức đọc xong ngất luôn, chỉ biết đúng không biết tại sao, thôi để dành đọc dần vậy.
Nhưng nhờ cái đoạn so sánh thời gian khi có ô ở trước của bạn mình ăn gian sửa lại điều kiện thời gian ở cái bảng phụ của mình cho dễ mò công thức.
Nhiều quá không biết còn sai trường hợp nào không.

 

[Phan Thế Hiệp]

Công thức này giống công thức của mình rồi cứ dòng nào trống là bị nhảy lên trên
Công thức của mình ra View attachment 258846
Của bạn View attachment 258847
Còn kết quả mình mong muốn là View attachment 258848
Mình đang bí ở chỗ nếu sản phẩm đã có ở dòng dưới rồi thì nó phải liền nhau chứ không phải cứ dòng trên trống là nhảy lên

Đâu có gì khó đâu bạn! Cũng như tìm quy luật của các dòng cần hiện của các mã: "16-11; 16-12; 16-13" là {2; 1; 4}, tương tự như vậy mà bạn tìm quy luật các ngày của dòng sản phẩm:
1/ Nếu xuất hiện ngày đầu (ngày SX), thì mình sẽ sắp thứ tự trên dưới của nó.
2/ Các ngày xuất hiện tiếp sau thì lấy giá trị là mã sản phẩm cột liền trước, cho đến ngày HT.

Điều chỉnh 1 ít trong công thức bài #2, như sau:

A14=IFERROR(INDEX($B$2:$B$12,MATCH(,COUNTIF(A$13:A13,$B$2:$B$12),)),"")
B14=MAX(FREQUENCY(IFERROR(TEXT($D$2:$D$12+COLUMN(A:AE)-1,"[<"&$E$2:$E$12&"]0;")/($B$2:$B$12=A14),""),ROW(INDIRECT(MIN($D$2:$D$12)&":"&MAX($E$2:$E$12)))))
B19=T(IFERROR(IF(VLOOKUP(B18,A$14:B$16,2,)=COUNTIF(B$18:B18,B18),INDEX($A$14:$A$16,MATCH(B18,A$14:A$16,)+1),B18),A14))
F19=IFERROR(IF(SUMPRODUCT((F$18=$D$2:$D$12)*($B$2:$B$12=$B19)),INDEX($A$2:$A$12&"-"&$C$2:$C$12,AGGREGATE(15,6,ROW($1:$100)/(F$18>=$D$2:$D$12)/(F$18<$E$2:$E$12)/($B$2:$B$12=$B19),COUNTIF($B$19:$B19,$B19))),IF(LOOKUP(2,1/(E19=$A$2:$A$12&"-"&$C$2:$C$12)/($B$2:$B$12=$B19),$E$2:$E$12)>F$18,E19,"")),"")

Xem file đính kèm

Thân

 

[Binbo2020]

Cám ơn bạn nhiều học được nhiều cái mới, tham gia thêm chút

A14=IFERROR(INDEX($B$2:$B$12,MATCH(,COUNTIF(A$13:A13,$B$2:$B$12),)),"")

Cái đoạn này có thể sửa chút để đỡ phải bấm ctrl shift enter (thấy mọi người hay dùng)

=IFERROR(INDEX($B$2:$B$12;MATCH(0;INDEX(COUNTIF(A$13:A13;$B$2:$B$12);0;0);0));"")

B14=MAX(FREQUENCY(IFERROR(TEXT($D$2:$D$12+COLUMN(A:AE)-1,"[<"&$E$2:$E$12&"]0;")/($B$2:$B$12=A14),""),ROW(INDIRECT(MIN($D$2:$D$12)&":"&MAX($E$2:$E$12)))))

Cái đoạn này mày mò thử cái hàm MMULT hôm nọ mới xem bài của bạn xong mượn tạm mà không biết còn sai sót gì không

=MAX(MMULT(--(COLUMN(($A$1:INDIRECT(ADDRESS(1;COUNT($E$2:$E$12)))))>0);($F$18:$W$18>=$D$2:$D$12)*($F$18:$W$18<$E$2:$E$12)*($B$2:$B$12=A14)))

 

[Phan Thế Hiệp]

Cái đoạn này mày mò thử cái hàm MMULT hôm nọ mới xem bài của bạn xong mượn tạm mà không biết còn sai sót gì không

=MAX(MMULT(--(COLUMN(($A$1:INDIRECT(ADDRESS(1;COUNT($E$2:$E$12)))))>0);($F$18:$W$18>=$D$2:$D$12)*($F$18:$W$18<$E$2:$E$12)*($B$2:$B$12=A14)))

Quá tốt! Hình dung được vấn đề rồi áp dụng thực tế, không máy móc, bạn học nhanh đó!

Thêm chút: thay vì hai dấu '--' có thể thay bằng N().

Còn cách chơi khác:

COLUMN(($A$1:INDIRECT(ADDRESS(1,COUNT($E$2:$E$12)))))^0

Thân