tìm vị trí đầu và cuối cùng cuả cấu kiện

[congnt92]

Chớ vội mừng, em trai!
Vì:

1. Đó chỉ mới là yêu cầu 1, mà trong đó còn phải xem công thức như thế nào nữa: có gọn và bao quát hết các trường hợp thay đổi (như thêm bớt mã hàng...), hoặc công thức tạo 1 lần rồi dán xuống (tức trên là Max dưới là Min), hay có cần nhấn CSE hay không..v.v...
2. Yêu cầu 2 giải thuật khác hẵn.

Chúc em ngày vui

Em gửi trước công thức tìm min, max đầu cuối. Công thức dài và rườm rà và yêu cầu dũ liệu được sort theo mã CK để đảm bảo tính liên tục. Nhờ anh chỉ thêm chỉ cách tối ưu hơn.

=MAX(MMULT(N(OFFSET($D$2,AGGREGATE(15,6,MATCH($B$3:$B$13,$B$3:$B$13,{0,1})/($B$4:$B$14<>$B$3:$B$13),(ROW(INDIRECT("1:"&SUMPRODUCT(1/COUNTIF($B$3:$B$13,$B$3:$B$13))))-1)*2+{1,2}),)),{1;1})/2)

Min tương tự như trên, ta thay hàm max thành hàm min.

Yêu cầu 2 em đang tiếp tục tìm hiểu

 

[Phan Thế Hiệp]

Em gửi trước công thức tìm min, max đầu cuối. Công thức dài và rườm rà và yêu cầu dũ liệu được sort theo mã CK để đảm bảo tính liên tục. Nhờ anh chỉ thêm chỉ cách tối ưu hơn.

=MAX(MMULT(N(OFFSET($D$2,AGGREGATE(15,6,AGGREGATE(15,6,MATCH($B$3:$B$13,$B$3:$B$13,{0,1})/($B$4:$B$14<>$B$3:$B$13),ROW(INDIRECT("1:"&SUMPRODUCT(1/COUNTIF($B$3:$B$13,$B$3:$B$13))*2))),(ROW(INDIRECT("1:"&SUMPRODUCT(1/COUNTIF($B$3:$B$13,$B$3:$B$13))))-1)*2+{1,2}),)),{1;1})/2)

Min tương tự như trên, ta thay hàm max thành hàm min.
Yêu cầu 2 em đang tiếp tục tìm hiểu

Sử dụng hàm MATCH($B$3:$B$13,$B$3:$B$13,{0,1}) rất hay và phù hợp. Chỉ cần giữ lại Match() bỏ bớt khúc đầu của nó là Aggregate(15,6, 'Giữ lại Match()/ĐK', Row() ), là công thức sẽ bớt được nhiều lắm, vì ..'Mảng kết quả từ Match()'.. bỏ vào Aggregate(15,6, ..'Mảng kết quả từ Match()'.. , {1,2;3,4;...} ) thì không quan trọng là Mảng 1 hay 2 chiều, cỡ nào nó cũng đưa về theo {1,2;3,4;...}.

Gợi ý cho "Yêu cầu 2":

• "Các Vùng phân khúc" (Mảng 1 chiều dọc) Ngắt khoảng Vùng theo chiều dọc: bắt đầu từ dòng nào, và có chiều dài phần tử thuộc chung nhóm.
• Dùng kỹ thuật xét Max (giống kỹ thuật cộng dồn theo phân khúc) theo "Các Vùng phân khúc".

Thân.

 

[congnt92]

bỏ bớt khúc đầu của nó là Aggregate(15,6,

Cảm ơn Anh.
Lại 1 lần nữa em quên mất option 6 của AGGREGATE là bỏ lỗi rồi. Không cần lồng 2 AGGREGATE vào nhau.
Yêu cầu 2 em cũng đã có ý tưởng như anh nói. Nghĩa là lấy max/min từng offset động rồi dùng sum để tính tổng.
Tuy nhiên em vẫn chưa thành công với offset có tham số mảng.

 

[Phan Thế Hiệp]

Cảm ơn Anh.
Lại 1 lần nữa em quên mất option 6 của AGGREGATE là bỏ lỗi rồi. Không cần lồng 2 AGGREGATE vào nhau.
Yêu cầu 2 em cũng đã có ý tưởng như anh nói. Nghĩa là lấy max/min từng offset động rồi dùng sum để tính tổng.
Tuy nhiên em vẫn chưa thành công với offset có tham số mảng.

Kỹ thuật xét Max/Min này "ngộ" lắm, thường nó là "cặp bài trùng" với Filter, nhưng ở đây mặc dù mình không có filter nhưng lại "cần phải" dùng đến nó, thì kết quả trả về thật là "hữu hiệu" vô cùng.

Đó cũng là yếu tố quyết định cho bài mà anh gửi "Tìm chi phí thấp nhất, cao nhất giữa các Phòng". Cách chọn điều kiện để lấy Mốc giữa hai bài sẽ khác nhau hoàn toàn, cho nên em không thể dùng Match('Vùng','Vùng',{0,1}) như trên được nữa.

Thân

 

[congnt92]

Kỹ thuật xét Max/Min này "ngộ" lắm, thường nó là "cặp bài trùng" với Filter, nhưng ở đây mặc dù mình không có filter nhưng lại "cần phải" dùng đến nó, thì kết quả trả về thật là "hữu hiệu" vô cùng.

Đó cũng là yếu tố quyết định cho bài mà anh gửi "Tìm chi phí thấp nhất, cao nhất giữa các Phòng". Cách chọn điều kiện để lấy Mốc giữa hai bài sẽ khác nhau hoàn toàn, cho nên em không thể dùng Match('Vùng','Vùng',{0,1}) như trên được nữa.

Thân

Em có thêm hàm này. Anh xem thử
=MIN(MMULT(N(OFFSET($D$2,MATCH($B$3:$B$13,$B$3:$B$13,{0,1}),)),{1;1})/2)
Dĩ nhiên yêu cầu dữ liệu vẫn phải được sort A-Z theo mã CK.
Tương tự cho max.

P/S: Em cũng đang nghiên cứu thêm vlookup nhưng sẽ hơi dài hơn xíu.

 

[Phan Thế Hiệp]

Em có thêm hàm này. Anh xem thử
=MIN(MMULT(N(OFFSET($D$2,MATCH($B$3:$B$13,$B$3:$B$13,{0,1}),)),{1;1})/2)
Dĩ nhiên yêu cầu dữ liệu vẫn phải được sort A-Z theo mã CK.
Tương tự cho max.
P/S: Em cũng đang nghiên cứu thêm vlookup nhưng sẽ hơi dài hơn xíu.

Xuất sắc!

Em đã hình dung được hết Mảng rồi đó. Nhưng chỉ cho đúng yêu cầu Min Max, chứ nếu tính bình quân của các bình quân "đầu - cuối" theo từng mã thì công thức này phải chế biến thêm điều kiện.

Phần "Yêu cầu 2" cũng vậy đó, phần Offset() tạo ra Mảng toàn cục, gần từa tựa như N(OFFSET($D$2,MATCH($B$3:$B$13,$B$3:$B$13,{0,1}),)) em đã tạo trên.

Chúc mừng em.

 

[fabikumi]

Mốc đầu:

=MATCH(H16,$B$1:$B$500,)​

Mốc cuối:

=LOOKUP(2,1/(H16=$B$1:$B$500)/($B$1:$B$500<>""),ROW($1:$500))​

Thân

anh ơi, cho em hỏi với là, nếu mình sử dụng hàm MATCH cho cả tìm kiếm mốc đầu và mốc cuối được ko ạ.?
Mốc đầu : nếu hàm = MATCH(H16,$B$1:$B$500,) theo kiểu tìm kiếm chính xác thì nó sẽ ra là 3
còn mốc cuối =MATCH(H16,$B$1:$B$500,1) thì nó sẽ là 7.
Đều đúng yêu cầu đề bài.

 

[congnt92]

Phần "Yêu cầu 2" cũng vậy đó, phần Offset() tạo ra Mảng toàn cục

Thực sự là em vẫn chưa thành công với Offset()
Ý định của em là 1 hàm Offset kiểu
Offset(điểm đầu,{a;b;c;d ....},,{e;f;g;h...},)
Tùy mỗi mã CK sẽ "nhảy cóc" tương ứng với a,b,c,d có độ cao e,f,g,h tương ứng.
Từ Offset động này thì Max/Min cũng "động" theo và tiến hành Sum.

Bài đã được tự động gộp: 28/8/18

anh ơi, cho em hỏi với là, nếu mình sử dụng hàm MATCH cho cả tìm kiếm mốc đầu và mốc cuối được ko ạ.?
Mốc đầu : nếu hàm = MATCH(H16,$B$1:$B$500,) theo kiểu tìm kiếm chính xác thì nó sẽ ra là 3
còn mốc cuối =MATCH(H16,$B$1:$B$500,1) thì nó sẽ là 7.
Đều đúng yêu cầu đề bài.

Match_type của hàm match là
0: tìm chính xác và sẽ trả kết quả đầu tiên tìm thấy.
1: nếu sort A-Z
-1: nếu sort Z-A.

 

[Phan Thế Hiệp]

anh ơi, cho em hỏi với là, nếu mình sử dụng hàm MATCH cho cả tìm kiếm mốc đầu và mốc cuối được ko ạ.?
Mốc đầu : nếu hàm = MATCH(H16,$B$1:$B$500,) theo kiểu tìm kiếm chính xác thì nó sẽ ra là 3
còn mốc cuối =MATCH(H16,$B$1:$B$500,1) thì nó sẽ là 7.
Đều đúng yêu cầu đề bài.

Tìm mốc đầu với công thức: = MATCH(H16,$B$1:$B$500,0) do không cần sort nên sẽ trả về kết quả là "giá trị khớp đúng" cho mọi trường hợp .
Tìm mốc cuối với công thức: = MATCH(H16,$B$1:$B$500,1), chức năng của hàm này là tìm "giá trị trong khoảng", thì yêu cầu dữ liệu tại B1: B500 phải được sort từ nhỏ đến lớn (A->Z hay 0->9...), nếu không sẽ ra kết quả sai. Việc tìm mốc cuối bằng hàm LOOKUP(2,1/(đk.1)/.../(đkn...), 'Vùng chứa stt Dòng' ) cũng là tìm giá trị khớp đúng mà không yêu cầu dữ liệu sort trước. Bạn cứ thử thay đổi giá trị ô B3= "B213" là sẽ biết ngay.
Chẳng qua, là do đề bài đã cho sẵn giá trị đã được sắp xếp từ nhỏ đến lớn nên bạn sử dụng MATCH(,1) phù hợp, nếu 1 ngày đẹp trời nào đó, dữ liệu bị biến động không sắp xếp như vậy nữa thì công thức sẽ sai ngay. Cho nên, khi tạo công thức cần phải hiểu hết những ưu khuyết của hàm để không bị ảnh hưởng về sau.

Thân.
(xem thêm giải thích tại: https://www.giaiphapexcel.com/diendan/threads/nhờ-các-anh-chị-lập-công-thức-tính-số-ngày-nhập-hàng-chân-thành-cám-ơn.123588/#post-876563)
----------------------------------------------------------------------------------------------------------------------------------------------

Thực sự là em vẫn chưa thành công với Offset()
Ý định của em là 1 hàm Offset kiểu
Offset(điểm đầu,{a;b;c;d ....},,{e;f;g;h...},)
Tùy mỗi mã CK sẽ "nhảy cóc" tương ứng với a,b,c,d có độ cao e,f,g,h tương ứng.
Từ Offset động này thì Max/Min cũng "động" theo và tiến hành Sum.

Đúng đường lối rồi đó, chỉ còn yếu tố hàm then chốt làm nhiệm vụ tính MAX/MIN cho từng đoạn thôi.
Anh có gợi ý tại bài #24 rồi đó.

Chúc em ngày vui.

 

[fabikumi]

Tìm mốc đầu với công thức: = MATCH(H16,$B$1:$B$500,0) do không cần sort nên sẽ trả về kết quả là "giá trị khớp đúng" cho mọi trường hợp .
Tìm mốc cuối với công thức: = MATCH(H16,$B$1:$B$500,1), chức năng của hàm này là tìm "giá trị trong khoảng", thì yêu cầu dữ liệu tại B1: B500 phải được sort từ nhỏ đến lớn (A->Z hay 0->9...), nếu không sẽ ra kết quả sai. Việc tìm mốc cuối bằng hàm LOOKUP(2,1/(đk.1)/.../(đkn...), 'Vùng chứa stt Dòng' ) cũng là tìm giá trị khớp đúng mà không yêu cầu dữ liệu sort trước. Bạn cứ thử thay đổi giá trị ô B3= "B213" là sẽ biết ngay.
Chẳng qua, là do đề bài đã cho sẵn giá trị đã được sắp xếp từ nhỏ đến lớn nên bạn sử dụng MATCH(,1) phù hợp, nếu 1 ngày đẹp trời nào đó, dữ liệu bị biến động không sắp xếp như vậy nữa thì công thức sẽ sai ngay. Cho nên, khi tạo công thức cần phải hiểu hết những ưu khuyết của hàm để không bị ảnh hưởng về sau.

Thân.
(xem thêm giải thích tại: https://www.giaiphapexcel.com/diendan/threads/nhờ-các-anh-chị-lập-công-thức-tính-số-ngày-nhập-hàng-chân-thành-cám-ơn.123588/#post-876563)
----------------------------------------------------------------------------------------------------------------------------------------------

rất hay anh ạ, em đã hiểu, nhưng công thức =LOOKUP(2,1/(H16=$B$1:$B$500)/($B$1:$B$500<>""),ROW($1:$500)) của anh thì đâu cần ($B$1:$B$500<>"") anh nhỉ.kết quả vẫn đúng.

 

[Phan Thế Hiệp]

rất hay anh ạ, em đã hiểu, nhưng công thức =LOOKUP(2,1/(H16=$B$1:$B$500)/($B$1:$B$500<>""),ROW($1:$500)) của anh thì đâu cần ($B$1:$B$500<>"") anh nhỉ.kết quả vẫn đúng.

Đúng vậy! tôi "lo bò trắng răng"
Cảm ơn bạn.

Thân

 

[congnt92]

khi tạo công thức cần phải hiểu hết những ưu khuyết của hàm để không bị ảnh hưởng về sau.

Chính xác vậy đấy anh.
Nếu bài toán của chủ thớt chỉ yêu cầu tìm đầu cuối thì lookup tìm điểm cuối là hợp lý nhất.
Còn nếu là bài toán mở rộng mà anh em mình đang trao đổi thì em nghĩ nhiều khi xử lí sơ chút xíu dữ liệu rồi kết hợp thêm hàm sẽ giúp mình rất nhiều.
Chẳng hạn em vẫn đang có 1 "băn khoăn" trong phương pháp mà mình đang sử dụng là hàm Aggregate(15,)
Vì nó sẽ sắp xếp các vị trí đầu cuối theo thứ tự tăng dần. Do đó dữ liệu cần phải được sắp xếp liền khối (không cần A-Z hoặc Z-A, nhưng bắt buộc phải liền khối).
Ví dụ B210 xuất hiện đầu cuối tại 1 và 5, B211 xuất hiện đầu cuối tại 3 và 8
Lúc đó Aggregate(15,) cho ra {1,3;5,8} (trong khi mình đang cần là {1,5;3,8}) và vì vậy kết quả sẽ sai.
Bài học rút ra ở đây là, dù là sử dụng hàm, đôi khi trong thực tế mình nên ứng dụng thêm các công cụ thanh ribbon để xử lý sơ các dữ liệu, lúc đó cả "người viết hàm" lẫn "hàm" đều đỡ vất vả .
P/S: Không biết anh có hướng nào cho bài toán này nếu dữ liệu sắp xếp ngẫu nhiên (không liền khối) không anh.
P/S2: yêu cầu 2 em vẫn đang theo dõi, chỉ là yêu cầu 1 còn có chút "day dứt" xem có thể tổng quát hóa hơn không

 

[Phan Thế Hiệp]

Không biết anh có hướng nào cho bài toán này nếu dữ liệu sắp xếp ngẫu nhiên (không liền khối) không anh.

Thích thì cũng ráng chìu, chứ làm xong không biết có xài ở đâu không!?

=MAX(MMULT(IFERROR(N(OFFSET($D$2,CHOOSE({1,2},ROW($1:$100),MOD(LOOKUP(COUNTIF($B$3:$B$100,">="&$B$3:$B$100)*10^3+100,
AGGREGATE(15,6,COUNTIF($B$3:$B$100,">="&$B$3:$B$100)*10^3+ROW($1:$100),ROW($1:$100))),10^3)),))/
(COUNTIF(OFFSET($B$3,,,ROW($1:$100)),$B$3:$B$100)=1),),{1;1})/2)

Ctrl+Shift+Enter. Đổi MAX thành MIN kiếm nhỏ nhất.

Xem file kèm.
Thân

 

[congnt92]

Thích thì cũng ráng chìu, chứ làm xong không biết có xài ở đâu không!?

Tuyệt quá anh.
Thực ra thực tế thì chả ai lại đi đánh đố mình như thế, sort dữ liệu 1 cái thì yêu cầu nó trở nên nhẹ nhàng hơn và viết hàm nó cũng nhàn hạ đi nhiều
Chỉ là thử khám phá thêm để có thêm kiến thức.
May mà anh chia ra từng cột phụ chứ thực sự quăng cái hàm tổng thì ngồi mổ xẻ ngâm cứu cả ngày chứ chả chơi
"Thỏa mãn" rồi, giờ để em thử tiếp cái yêu cầu 2

 

[Phan Thế Hiệp]

Chỉ là thử khám phá thêm để có thêm kiến thức.

Anh hiểu mà! Các câu đặt thêm ngoài yêu cầu của chủ thớt, chủ yếu để anh em giải bài có cái nhìn bao quát hơn, và cũng là dịp chia sẻ điều hay hoặc kiểm điểm xem mình còn thiếu sót chỗ nào để điều chỉnh.

Chúc em ngày vui.