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

[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