การคัดแยกข้อมูลที่มีเงื่อนไข

[warapornboonmakong]

เรียนอาจารย์และผู้รู้ทุกท่านค่ะ

จากปัญหาดิฉันต้องการคัดแยกข้อมูลระหว่างข้อมูลที่มีน้ำหนักของสินค้ามากกว่า 200 kg และน้อยกว่า 200 kg ไว้อีกชีทหนึ่ง ดังนี้
-ที่ชีท DATA เป็นข้อมูลขาเข้าทั้งหมด โดยที่ C5, D5 (คอลัมน์) จะแสดง Customer และ Weight (kg) ตามลำดับ

C6 A D6 20
C7 B D7 10
C8 C D8 165
C9 D D9 203
C10 E D10 98
C11 F D11 56
C12 G D12 542
C13 H D13 10
C14 I D14 198
C15 J D15 340
C16 K D16 201
C17 L D17 96
C18 M D18 56
C19 N D19 83
C20 O D20 222
C21 P D21 78

-ต้องการแยกข้อมูลของลูกค้าที่มีน้ำหนักสินค้ามากกว่า 200 kg มาที่ชีท Upper than 200 (ดังตัวอย่าง)

C6 D D6 203
C7 G D7 542
C8 J D8 340
C9 K D9 201
C10 O D10 222

-และต้องการแยกข้อมูลของลูกค้าที่มีน้ำหนักสินค้าน้อยกว่า 200 kg มาที่ชีท Lower than 200 (ดังตัวอย่าง)

C6 A D6 20
C7 B D7 10
C8 C D8 165
C9 E D10 98
C10 F D11 56
C11 H D13 10
C12 I D14 198
C13 L D17 96
C14 M D18 56
C15 N D19 83
C16 P D21 78

ผิดพลาดประการใดขออภัยไว้ ณ ที่นี้ด้วยค่ะ

[PayomKanboon]

ที่ C6 Sheet Upper than 200 =INDEX(DATA!C$6:C$21,SMALL(IF(DATA!$D$6:$D$21>=200,ROW(DATA!$D$6:$D$21),""),ROW(A1))-5) เนื่องจากเป็นสูตร Arrray กด F2 แล้ว กด Ctrl+Shift+Enter พร้อมกันครับ
ที่ C6 Sheet Lower than 200 =INDEX(DATA!C$6:C$21,SMALL(IF(DATA!$D$6:$D$21<=200,ROW(DATA!$D$6:$D$21),""),ROW(A1))-5) เนื่องจากเป็นสูตร Arrray กด F2 แล้ว กด Ctrl+Shift+Enter พร้อมกันครับ

[PayomKanboon]

โทษทีครับ ลืมบอกครับให้ Copy ลงล่าง และไปทางขวามือตามต้องการครับ

[snasui]

ROW(A1))-5

มาช่วยเสริมครับ จากกรณีส่วนของสูตรด้านบน หากมีการลบบรรทัดหรือแทรกบรรทัดจะทำให้สูตรแสดงผลคลาดเคลื่อน ยกตัวอย่างเช่น แทรกบรรทัดที่ 1 จะทำให้สูตรแสดงผลคลาดเคลื่อนไปได้

สำหรับตัวอย่างสูตรที่มั่นคงต่อการแทรกบรรทัด ได้แก่สูตรตามด้านล่างครับ

=IFERROR(INDEX(DATA!C$6:C$21,SMALL(IF(DATA!$D$6:$D$21>=200,ROW(DATA!$C$6:$C$21)-ROW(DATA!$C$6)+1),ROWS(C$6:C6))),"")

[PayomKanboon]

ขอบคุณครับอาจารย์...คิดไม่รอบคอบครับ

[warapornboonmakong]

ขอขอบคุณอาจารย์ และคุณ PayomKanboon มากค่ะสำหรับคำตอบซึ่งตรงตามความต้องการที่จะนำไปใช้งานค่ะ

[warapornboonmakong]

เรียนอาจารย์และท่านผู้รู้ทุกท่านค่ะ
ต้องการสอบถามเพิ่มเติมจากเรื่องการคัดแยกข้อมูลค่ะ หลังจากที่ทำการคัดแยกข้อมูลเรียบร้อยแล้ว ต้องการจัดรถให้กับลูกค้าแต่ละรายค่ะ โดยมีข้อจำกัด คือรถแต่ละคันบรรได้ไม่เกิน 500 kg ดังนี้ค่ะ
-ที่ชีท Upper than 200 (จากสูตรของอาจารย์และคุณ PayomKanboon) จะทำการจัดรถ โดยที่คอลัมน์ C,D,E และ F จะเป็น Customer,Weight(kg),Cumulative (น้ำหนักสะสมของลูกค้าแต่ละราย) และ Truck No. (มีการใช้สูตร =ROUNDDOWN(E6/500,0)+1) ตามลำดับค่ะ

C6 D D6 203 E6 203 F6 1
C7 G D7 542 E7 745 F7 2
C8 J D8 340 E8 1085 F8 3
C9 K D9 201 E9 1286 F9 3
C10 O D10 222 E10 1508 F10 4
C11 Q D11 1000 E11 2508 F11 6
C12 R D12 1300 E12 3808 F12 8
C13 S D13 650 E13 4458 F13 9
C14 T D14 206 E14 4664 F14 10
C15 U D15 300 E15 4964 F15 10
C16 V D16 980 E16 5944 F16 12
C17 W D17 520 E17 6464 F17 13
C18 X D18 1250 E18 7714 F18 16
C19 Y D19 850 E19 8564 F19 18
C20 Z D20 260 E20 8824 F20 18

ที่ F6-F20 มีสูตร คือ ROUNDDOWN(E6/500,0)+1 ซึ่งพบปัญหาคือ
-ที่ C11 ลูกค้า Q ที่น้ำหนัก 1000 kg จะต้องใช้รถ 2 คัน คือคันที่ 5 และ 6 ซึ่งต้องการให้ F11 แสดงผลได้ว่าใช้คันที่ 5,6
-ที่ C12 ลูกค้า R มีน้ำหนัก 1300 kg แต่จากสูตรบอกว่าลูกค้าดังกล่าวใช้คันที่ 7 และ 8 ซึ่งตามความเป็นจริง ต้องใช้ 3 คันค่ะ

หากผิดพลาดประการใดขออภัยไว้ ณ ที่นี้ด้วยค่ะ

[DhitiBank]

สอบถามข้อมูลเพิ่มเติมครับ

1. รถบรรทุก 1 คันบรรทุกได้ประมาณ 500 กก. ใช่ไหมครับ

2. ลูกค้า Q ทำไมถึงเป็นรถบรรทุกคันที่ 5, 6 เลยครับ เพราะหากดูจากน้ำหนักรวม รถคันที่ 4 เพิ่งจะบรรทุกไปแค่ 8 กก. เท่านั้น ทำไมถึงไม่เป็นคันที่ 4 (492 กก.), 5 (500 กก.) และ 6 (8 กก.) ครับ

3. ลูกค้า R ที่บอกว่าต้องใส่รถ 3 คัน ไม่ทราบว่าเป็นรถคันที่ 6, 7 และ 8 หรือเป็น 7, 8 และ 9 ครับ เพราะหากดูจากน้ำหนักสะสม ลูกค้า Q มีน้ำหนักเกินมาคันที่ 6 แค่ 8 กก. ดังนั้น รถคันที่ 6 จึงยังน่าจะใส่ได้อีก ฉะนั้น ตามความเข้าใจของผมคือ 3 คันที่ว่านี้น่าจะเป็น 6-8 ใช่ไหมครับ

ที่ F6 ลองคีย์
=IF(D6<500,INT(E6/500)+1,INT(E5/500)+1&"-"&INT(E6/500)+1)
Enter แล้วคัดลอกลงล่าง ไม่แน่ใจว่าใช่แบบที่ต้องการหรือเปล่าครับ

[warapornboonmakong]

ตอบคำถามคุณ DhitiBank

1.รถบรรทุก 1 คัน บรรทุกได้ประมาณ 500 กิโลกรัมค่ะ
2-3. ถ้าจากสูตรเดิม ตามที่คิดว่าสูตรเป็น คือ ถ้ามีรายใดที่น้ำหนักรวมกับรายต่อไปแล้วเกิน 500 กิโลกรัม มันจะตัดไปอีกคันเลยค่ะ เช่น คันที่ 1 ถ้ารวมลูกค้า D และ G จะเกิน 500 กิโลกรัม ดังนั้นคันที่ 1 จึงขนเพียงลูกค้ารายเดียว แต่ถ้าเป็นไปตามที่คุณ DhitiBank บอก แสดงว่าดิฉันเข้าใจผิดค่ะ

สิ่งที่ต้องการให้รถจัดคือจัดกลุ่มของลูกค้าที่มีน้ำหนักเกิน 200 กิโลกรัมก่อน โดยที่ต้องการให้สินค้าของลูกค้าแต่ละรายอยู่ไปกับรถคันใดคันหนึ่งก่อนค่ะ แต่ถ้าน้ำหนักเกินที่จะบรรทุกได้จึงจะถูกแยกไป 2 คันค่ะ แล้วหลังจากนั้นจึงค่อยนำน้ำหนักของสินค้าที่มีน้อยกว่า 200 กิโลกรัมแบ่งใส่ภายหลังค่ะ ในอันดับแรกจึงต้องการทราบก่อนว่ารถแต่ละคันจะมีลูกค้ารายไหนที่ต้องขนส่งบ้างสำหรับกลุ่มที่มีน้ำหนักเกิน 200 กิโลกรัมค่ะ

ขอโทษด้วยนะคะที่อธิบายไม่ชัดเจน

[DhitiBank]

ถ้าเป็นไปตามที่คุณ DhitiBank บอก แสดงว่าดิฉันเข้าใจผิดค่ะ

โปรดอย่ากังวลครับ อย่างไรก็ตามก็ต้องยึดตามลักษณะงานที่คุณ warapornboonmakong ทำเป็นประจำอยู่แล้ว ผมเพียงแต่สงสัยเท่านั้นเองครับ

สิ่งที่ต้องการให้รถจัดคือจัดกลุ่มของลูกค้าที่มีน้ำหนักเกิน 200 กิโลกรัมก่อน โดยที่ต้องการให้สินค้าของลูกค้าแต่ละรายอยู่ไปกับรถคันใดคันหนึ่งก่อนค่ะ แต่ถ้าน้ำหนักเกินที่จะบรรทุกได้จึงจะถูกแยกไป 2 คันค่ะ แล้วหลังจากนั้นจึงค่อยนำน้ำหนักของสินค้าที่มีน้อยกว่า 200 กิโลกรัมแบ่งใส่ภายหลังค่ะ ในอันดับแรกจึงต้องการทราบก่อนว่ารถแต่ละคันจะมีลูกค้ารายไหนที่ต้องขนส่งบ้างสำหรับกลุ่มที่มีน้ำหนักเกิน 200 กิโลกรัมค่ะ

ถ้าอย่างนั้น รบกวนใส่คำตอบที่ถูกต้องลงมาในไฟล์ได้ไหมครับ จะได้ลองเขียนสูตรเทียบกันดูครับ อ่อ แล้วก็อยากให้ลองสูตรด้านบนดูด้วยครับ ว่าผิดพลาดอย่างไรหรือไม่ จะได้แก้ไขเพิ่มเติมหรืออาจต้องทำความเข้าใจใหม่ครับ

[warapornboonmakong]

ตอบคำถามคุณ DhitiBank

ขออนุญาติเปลี่ยนแปลงตัวอย่างและปรับเงื่อนไขปริมาณน้ำหนักที่รับได้เป็น 800 กิโลกรัมค่ะ และได้ลองใช้สูตรที่คุณ DhitiBank บอกแล้วค่ะ แต่ยังไม่เป็นไปตามต้องการค่ะ (ขอบคุณสำหรับสูตรด้วยค่ะ) และได้ใส่คำตอบที่ต้องการลงในไฟล์แนบแล้วค่ะ

หากผิดพลาดประการใดขออภัยไว้ ณ ที่นี้ด้วยค่ะ

[DhitiBank]

ลองแบบนี้ครับ
ที่ F19 คีย์
=IF(AND(D19<=800,800-SUMIF(G$18:G18,G18,D$18:D18)>=D19),MAX(INT(E19/800)+1,G18),IF(D19>800,G18+1&"-"&G18+ROUNDUP(D19/800,0),G18+1))
Enter

ที่ G19 คีย์
=IF(ISNUMBER(F19),F19,--RIGHT(F19,LEN(F19)-FIND("-",F19)))
Enter

แล้วคัดลอกทั้ง 2 สูตรลงล่างครับ

[warapornboonmakong]

ขอขอบคุณคุณ DhitiBank ค่ะ สูตรที่ได้เป็นไปตามที่จะใช้งานค่ะ