tìm vị trí đầu và cuối cùng cuả cấu kiện (1 người xem)

[LOIKS]

chào mọi người mình muốn tìm số hàng đầu tiên củng như số hàng cuối cùng của làn lượt 2 cấu kiện là B210 và B211
trong file đính kèm thì cấu kiện B210 có hàng đầu tiên là hàng số 3 và hàng cuối cung là hàng số 7, tương tự như cấu kiện B211 cso hàng đầu tiên là hàng số 8 và hàng cuối cùng là hàng số 13,
ví du này chỉ có 2 cấu kiện thôi thực ra nó rất nhiều
cảm ơn mọi người

 

[lenkha]

chào mọi người mình muốn tìm số hàng đầu tiên củng như số hàng cuối cùng của làn lượt 2 cấu kiện là B210 và B211
trong file đính kèm thì cấu kiện B210 có hàng đầu tiên là hàng số 3 và hàng cuối cung là hàng số 7, tương tự như cấu kiện B211 cso hàng đầu tiên là hàng số 8 và hàng cuối cùng là hàng số 13,
ví du này chỉ có 2 cấu kiện thôi thực ra nó rất nhiều
cảm ơn mọi người

Dòng đầu tiên trong dữ liệu =ROW(INDEX($B$3:$B$13,MATCH("B210",$B$3:$B$13,0)))-2
Dòng cuối cùng trong dữ liệu =LOOKUP(2,1/($B$3:$B$13="B210"),ROW($B$3:$B$13))-2

 

[congnt92]

chào mọi người mình muốn tìm số hàng đầu tiên củng như số hàng cuối cùng của làn lượt 2 cấu kiện là B210 và B211
trong file đính kèm thì cấu kiện B210 có hàng đầu tiên là hàng số 3 và hàng cuối cung là hàng số 7, tương tự như cấu kiện B211 cso hàng đầu tiên là hàng số 8 và hàng cuối cùng là hàng số 13,
ví du này chỉ có 2 cấu kiện thôi thực ra nó rất nhiều
cảm ơn mọi người

Bắt đầu
E3=MIN(IFERROR(ROW($B$3:$B$13)/(($B$3:$B$13)=$F$3),""))
Kết thúc thì thay MIN thành MAX.
Với F3 là ô chứa tên kiện cần kiểm tra đầu cuối.
Bạn nhớ kết thúc bằng Ctrl Shift Enter

 

[Phan Thế Hiệp]

chào mọi người mình muốn tìm số hàng đầu tiên củng như số hàng cuối cùng của làn lượt 2 cấu kiện là B210 và B211
trong file đính kèm thì cấu kiện B210 có hàng đầu tiên là hàng số 3 và hàng cuối cung là hàng số 7, tương tự như cấu kiện B211 cso hàng đầu tiên là hàng số 8 và hàng cuối cùng là hàng số 13,
ví du này chỉ có 2 cấu kiện thôi thực ra nó rất nhiều
cảm ơn mọi người

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

 

[Phan Thế Hiệp]

Bắt đầu
E3=MIN(IFERROR(ROW($B$3:$B$13)/(($B$3:$B$13)=$F$3),""))
Kết thúc thì thay MIN thành MAX.
Với F3 là ô chứa tên kiện cần kiểm tra đầu cuối.
Bạn nhớ kết thúc bằng Ctrl Shift Enter

Nhìn công thức này của em làm anh nhớ lại khoảng thời gian mấy anh đang tìm hiểu công thức mảng, có vài bài toán hay liên quan đến phân khúc đoạn đầu và cuối của từng mã SP, và công thức của một người em làm "ngây ngất" anh em.

Nay, dựa trên dữ liệu của chủ thớt tại bài #1, anh bổ sung thêm 1 ít dẫn giải trong file đính kèm để nâng mức độ khó cho bài như sau: Tạo công thức theo hai yêu cầu sau, và đều không dùng thêm Name hoặc cột phụ hỗ trợ.

• Yêu cầu 1: Tính giá trị bình quân (cột D) giữa hai mốc đầu - cuối theo từng mã hàng, kết quả điền vào K16:K26. (Ưu tiên công thức ngắn nhất)
• Yêu cầu 2: Tính Tổng của các giá trị lớn nhất (cột D) của từng đoạn mã "CK"; và Tính Tổng của các giá trị nhỏ nhất (cột D) của từng đoạn mã "CK". (cốt yếu là tìm được giải thuật xử lý tốt yêu cầu)

Em và @hocexcel_1991 cứ từ từ nghiên cứu, tìm giải pháp và làm thử, sẽ suy nghĩ căng nhưng bù lại có nhiều điều rất thú vị.

Chúc hai em học tập vui.

 

[congnt92]

Nhìn công thức này của em làm anh nhớ lại khoảng thời gian mấy anh đang tìm hiểu công thức mảng, có vài bài toán hay liên quan đến phân khúc đoạn đầu và cuối của từng mã SP, và công thức của một người em làm "ngây ngất" anh em.

Nay, dựa trên dữ liệu của chủ thớt tại bài #1, anh bổ sung thêm 1 ít dẫn giải trong file đính kèm để nâng mức độ khó cho bài như sau: Tạo công thức theo hai yêu cầu sau, và đều không dùng thêm Name hoặc cột phụ hỗ trợ.

• Yêu cầu 1: Tính giá trị bình quân (cột D) giữa hai mốc đầu - cuối theo từng mã hàng, kết quả điền vào K16:K26. (Ưu tiên công thức ngắn nhất)
• Yêu cầu 2: Tính Tổng của các giá trị lớn nhất (cột D) của từng đoạn mã "CK"; và Tính Tổng của các giá trị nhỏ nhất (cột D) của từng đoạn mã "CK". (cốt yếu là tìm được giải thuật xử lý tốt yêu cầu)

Em và @hocexcel_1991 cứ từ từ nghiên cứu, tìm giải pháp và làm thử, sẽ suy nghĩ căng nhưng bù lại có nhiều điều rất thú vị.

Chúc hai em học tập vui.

- Tìm đầu cuối thì coi như xong rồi đúng không Anh. Biết dòng đầu, dòng cuối rồi thì mình index để lấy 2 giá trị đầu cuối, cộng lại chia đôi ra bình quân.
- Tìm max từng mã thì
F2=AGGREGATE(14,6,$D$3:$D$13/(($B$3:$B$13)=B3),1)*(COUNTIF($B$3:B3,B3)=1)
Cộng max toàn bộ thì
F14=Sum(F3:F13)
Với min, ta thay số 14 thành 15 ở hàm AGGREGATE.
Anh xem giúp em nha Anh.
Cảm ơn Anh.

Edit: Em hiểu nhầm ý câu 2, nếu Anh đang ra đề: cần 1 hàm ra tổng luôn thì em gửi lại hàm này
F14=SUMPRODUCT(LARGE($D$3:$D$13,ROW(INDIRECT(1&":"&SUMPRODUCT(1/COUNTIF($B$3:$B$13,$B$3:$B$13))))))

Ý tưởng của em là:
Nếu trong chuỗi có n mã thì lấy n giá trị lớn nhất và cộng lại
Trong ví dụ có 2 mã nên ta sẽ tìm 2 giá trị lớn nhất. Dùng hàm large với tham số k dạng mảng {1;2}
Để tạo mảng k này cơ động hơn ra dùng hàm row với indirect. row chạy từ 1 đến n. với n là số mã.

 

[hocexcel_1991]

- Tìm đầu cuối thì coi như xong rồi đúng không Anh. Biết dòng đầu, dòng cuối rồi thì mình index để lấy 2 giá trị đầu cuối, cộng lại chia đôi ra bình quân.
- Tìm max từng mã thì
F2=AGGREGATE(14,6,$D$3:$D$13/(($B$3:$B$13)=B3),1)*(COUNTIF($B$3:B3,B3)=1)
Cộng max toàn bộ thì
F14=Sum(F3:F13)
Với min, ta thay số 14 thành 15 ở hàm AGGREGATE.
Anh xem giúp em nha Anh.
Cảm ơn Anh.

Edit: Em hiểu nhầm ý câu 2, nếu Anh đang ra đề: cần 1 hàm ra tổng luôn thì em gửi lại hàm này
F14=SUMPRODUCT(LARGE($D$3:$D$13,ROW(INDIRECT(1&":"&SUMPRODUCT(1/COUNTIF($B$3:$B$13,$B$3:$B$13))))))

Ý tưởng của em là:
Nếu trong chuỗi có n mã thì lấy n giá trị lớn nhất và cộng lại
Trong ví dụ có 2 mã nên ta sẽ tìm 2 giá trị lớn nhất. Dùng hàm large với tham số k dạng mảng {1;2}
Để tạo mảng k này cơ động hơn ra dùng hàm row với indirect. row chạy từ 1 đến n. với n là số mã.

Nếu mà 1 mã này có 2 số lớn hơn so với số lớn nhất của mã kia thì công thức không đúng

 

[congnt92]

Nếu mà 1 mã này có 2 số lớn hơn so với số lớn nhất của mã kia thì công thức không đúng

Đúng là mình quên nghĩ đến trường hợp này. Để suy ngẫm thêm.
Bài tập hay mà để mình làm việc đã kẻo sa đà quá sếp đuổi việc bây giờ

 

[Phan Thế Hiệp]

cần 1 hàm ra tổng luôn thì em gửi lại hàm này
F14=SUMPRODUCT(LARGE($D$3:$D$13,ROW(INDIRECT(1&":"&SUMPRODUCT(1/COUNTIF($B$3:$B$13,$B$3:$B$13))))))

Đúng như @hocexcel_1991 nói:

Nếu mà 1 mã này có 2 số lớn hơn so với số lớn nhất của mã kia thì công thức không đúng

Nó phải trích xuất ra Max (hoặc Min) của từng mã, rồi mới cộng lại.
Đây anh chỉ mới đưa ra đơn giản là 2 mã, nếu nó nhiều hơn thì sao!?

- Tìm đầu cuối thì coi như xong rồi đúng không Anh. Biết dòng đầu, dòng cuối rồi thì mình index để lấy 2 giá trị đầu cuối, cộng lại chia đôi ra bình quân.

Do em vượt qua dễ dàng vậy anh đổi yêu cầu như sau:
Tạo công thức duy nhất theo hai yêu cầu sau, và đều không dùng thêm Name hoặc cột phụ hỗ trợ.

• Yêu cầu 1: Tính giá trị bình quân (cột D) giữa hai mốc đầu - cuối theo từng mã hàng. Sau đó, so sánh các giá trị bình quân vừa tìm được lấy ra giá trị Min/Max.
• Yêu cầu 2: Tính Tổng của các giá trị lớn nhất (cột D) của từng đoạn mã "CK"; và Tính Tổng của các giá trị nhỏ nhất (cột D) của từng đoạn mã "CK". (cốt yếu là tìm được giải thuật xử lý tốt yêu cầu)

Cứ từ từ nghiền ngẫm, không nên vội vàng, tìm hiểu kỹ đề bài, rồi hẵng đưa giải pháp, các em! và nhớ đừng làm ảnh hưởng đến "cần câu cơm" của mình.

Anh đính kèm bài gốc để mấy em tham khảo thêm.

Chúc anh em ngày vui.

 

[congnt92]

Tạo công thức duy nhất theo hai yêu cầu sau, và đều không dùng thêm Name hoặc cột phụ hỗ trợ.

Sáng ra vào form đã có bài tập mới của anh rồi
Thực sự là xem qua file và cố nghĩ hết thuật toán để giải nhưng vẫn bí anh ạ.
chỉ 1 công thức duy nhất, làm được các bước:
- tìm vị trí đầu, cuối không chỉ của 1 mã
- tính bình quân đầu cuối từng mã rồi lấy min.
mà chưa chắc mã đã được sort liên tục "đẹp" như vậy.
Chắc sang tuần nhờ anh gợi mở thêm tí về hướng đi

 

[hocexcel_1991]

Sáng ra vào form đã có bài tập mới của anh rồi
Thực sự là xem qua file và cố nghĩ hết thuật toán để giải nhưng vẫn bí anh ạ.
chỉ 1 công thức duy nhất, làm được các bước:
- tìm vị trí đầu, cuối không chỉ của 1 mã
- tính bình quân đầu cuối từng mã rồi lấy min.
mà chưa chắc mã đã được sort liên tục "đẹp" như vậy.
Chắc sang tuần nhờ anh gợi mở thêm tí về hướng đi

Mình chỉ mới nghĩ ddc công thức có 2 mã thôi, còn tổng quát thì cũng chưa, chắc nhờ a @Phan Thế Hiệp "Gợi ý"

 

[Phan Thế Hiệp]

Chào @congnt92 và @hocexcel_1991

Xem bài của chủ thớt trước nha:

Về "Yêu cầu 1":
Cách tổng quát, do có sự liệt kê các mã liền kề từ trên xuống, nên ta dựa vào sự biến đổi khác nhau giữa dòng trên và dòng dưới mà tìm ra được các mốc đầu cuối, ví dụ: ô B3="B210"<>ô B2=""; ô B7="B210" <> ô B8="B211"; ô B13="B211" <> ô B14="". Vậy dùng Mảng nào để gom lại các Mốc đó tạo ra một Mảng đánh dấu các vị trí Mốc {1;0;0;0;1;1;0;0;0;0;1}. Từ đó, phân làm hai hướng:

1. Nếu là Tính bình quân của các Mốc đầu cuối: thì cứ lấy Average() của 4 mốc đó (yêu cầu này dễ anh không hỏi thêm).
2. Do ta có các vị trí tuần tự xếp các Mốc từ trên xuống là 1, 2, 3, 4, dùng kỹ thuật Mảng sao cho nhóm 4 giá trị đó thành Mảng {1,2;3,4}. Lúc đó, ta cộng được Mảng 2 chiều này theo hàng ngang (bằng MMULT()). về thành Mảng 1 chiều có hai (hoặc nhiều giá trị nếu dữ liệu có thêm nhiều mã hàng).
3. Xong các việc trên thì lúc đó muốn cộng trừ nhân chia, so sánh Min Max thoải mái.

Chúc hai em ngày nghỉ cuối tuần vui vẻ cùng gia đình.

 

[congnt92]

lấy Average() của 4 mốc đó

Cảm ơn Anh đã gợi ý, để sang tuần rảnh rỗi sẽ thử sức
Nhưng mà chỗ em quote là do em chưa hiểu lắm.
Average 4 mốc tức là bình quân của 4 số rồi. Trong khi mình đang cần bình quân theo từng cặp đầu-cuối rồi so sánh lấy Min.
Hay do em đang hiểu "chưa tới" cái ý của Anh nhỉ

 

[Phan Thế Hiệp]

Cảm ơn Anh đã gợi ý, để sang tuần rảnh rỗi sẽ thử sức
Nhưng mà chỗ em quote là do em chưa hiểu lắm.
Average 4 mốc tức là bình quân của 4 số rồi. Trong khi mình đang cần bình quân theo từng cặp đầu-cuối rồi so sánh lấy Min.
Hay do em đang hiểu "chưa tới" cái ý của Anh nhỉ

Ý anh là ban đầu tính đố thêm yêu cầu 1", tức ngoài yêu cầu chính thức là tính bình quân theo từng mã hàng rồi tìm Min Max, thì thêm việc tính bình quân của 4 mốc đầu - cuối, nó cũng hàm nghĩa là tính bình quân của các mức bình quân của từng mã hàng. Nhưng do sau khi tìm ra được Mảng {1;0;0;0;1;1;0;0;0;0;1} thì chỉ cần nhân với cột Giá trị rồi tính bình quân thì dễ quá, nên không ghi thành yêu cầu chính thức.

Thân

 

[congnt92]

Ý anh là ban đầu tính đố thêm yêu cầu 1", tức ngoài yêu cầu chính thức là tính bình quân theo từng mã hàng rồi tìm Min Max, thì thêm việc tính bình quân của 4 mốc đầu - cuối, nó cũng hàm nghĩa là tính bình quân của các mức bình quân của từng mã hàng. Nhưng do sau khi tìm ra được Mảng {1;0;0;0;1;1;0;0;0;0;1} thì chỉ cần nhân với cột Giá trị rồi tính bình quân thì dễ quá, nên không ghi thành yêu cầu chính thức.

Thân

Tuần mới nhiều năng lượng nha anh.
Tạo mảng {1;0;0;0;1;1;0;0;0;0;1} thì em tạo xong nhưng cái kỹ thuật khó nhất ở đây là ngắt chuỗi ra làm nhiều phần, vị trí ngắt là ở chỗ 2 số 1 liền kề.
Đây chắc là bước ngoặt của vấn đề, xử được cái này thì kết hợp Min với MMULT là xong rồi anh à.

 

[Phan Thế Hiệp]

Tuần mới nhiều năng lượng nha anh.
Tạo mảng {1;0;0;0;1;1;0;0;0;0;1} thì em tạo xong nhưng cái kỹ thuật khó nhất ở đây là ngắt chuỗi ra làm nhiều phần, vị trí ngắt là ở chỗ 2 số 1 liền kề.
Đây chắc là bước ngoặt của vấn đề, xử được cái này thì kết hợp Min với MMULT là xong rồi anh à.

Tùy theo số lượng mã hàng, mà định ra được số lượng các Mốc có trong Mảng.

1. Giả sử như bài #1, ta có 2 mã hàng là "B210" và "B211", thì công thức Mảng sẽ phải đánh dấu mốc như sau: {1;5;6;11} và kỹ thuật ngắt từ Mảng 1 chiều thành 2 chiều:
   • =N(OFFSET($D$2,AGGREGATE(15,6, {1;5;6;11} , {1,2;3,4} ),))
2. Giả sử B13 là 1 mã khác nữa, Vd: "B213", thì công thức Mảng đánh dấu mốc như sau:
   • =N(OFFSET($D$2,AGGREGATE(15,6, {1;5;6;10;11;11} , {1,2;3,4;5,6} ),))

Tìm được 2 Mảng trên bằng công thức thì bài toán của Yêu cầu 1 được giải (Yêu cầu 2 giải thuật khác).

Xem file kèm.

Thân

 

[congnt92]

Tìm được 2 Mảng trên bằng công thức thì bài toán của Yêu cầu 1 được giải

Chính xác là vậy đấy anh, mảng số 2 là cái mà em đang bí.
Loay hoay sáng giờ cũng chỉ vì mỗi cái dấu "," trong {1,2,3,4} thành dấu ";" trong {1,2;3,4} anh à

 

[Phan Thế Hiệp]

Chính xác là vậy đấy anh, mảng số 2 là cái mà em đang bí.
Loay hoay sáng giờ cũng chỉ vì mỗi cái dấu "," trong {1,2,3,4} thành dấu ";" trong {1,2;3,4} anh à

Tại em nghĩ "phức tạp" thành ra đi theo hướng "phức tạp", nhưng đừng lo! bệnh này là phổ biến cho những ai đang đi tìm hiểu công thức mảng.

1. Nếu 2 mã hàng thì: =(ROW($1:$2)-1)*2+{1,2}
2. Nếu 3 mã hàng thì: =(ROW($1:$3)-1)*2+{1,2}
3. Nếu n mã hàng thì: =(ROW($1:$n)-1)*2+{1,2}
4. Tổng quát: =(ROW(INDIRECT("1:"&SUMPRODUCT(1/COUNTIF($B$3:$B$13,$B$3:$B$13))))-1)*2+{1,2})

Thân.

 

[congnt92]

Tại em nghĩ "phức tạp" thành ra đi theo hướng "phức tạp"

Chính xác vậy đấy anh.

Tại em nghĩ "phức tạp" thành ra đi theo hướng "phức tạp", nhưng đừng lo! bệnh này là phổ biến cho những ai đang đi tìm hiểu công thức mảng.

1. Nếu 2 mã hàng thì: =(ROW($1:$2)-1)*2+{1,2}
2. Nếu 3 mã hàng thì: =(ROW($1:$3)-1)*2+{1,2}
3. Nếu n mã hàng thì: =(ROW($1:$n)-1)*2+{1,2}
4. Tổng quát: =(ROW(INDIRECT("1:"&SUMPRODUCT(1/COUNTIF($B$3:$B$13,$B$3:$B$13))))-1)*2+{1,2})

Thân.

Vậy ra rồi anh ơi . Anh gợi ý hết sạch rồi
Em lại đi nhân 2 mảng thay vì đi cộng, nên nó cứ ra kiểu {1,2;2,4}
Xong giờ nghỉ trưa em sẽ gửi bài giải của em. Hiện đang nằm online bằng điện thoại

 

[Phan Thế Hiệp]

Chính xác vậy đấy anh.
Vậy ra rồi anh ơi . Anh gợi ý hết sạch rồi
Em lại đi nhân 2 mảng thay vì đi cộng, nên nó cứ ra kiểu {1,2;2,4}
Xong giờ nghỉ trưa em sẽ gửi bài giải của em. Hiện đang nằm online bằng điện thoại

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

 

[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.