วิธีใส่สูตรให้ค่าที่เกินเด้งไปเริ่มcellใหม่[สอบถาม]

[klingnarang]

เรียน พี่ๆ และอาจารย์ทุกๆท่าน
จากหัวข้อด้านบนคือว่าตอนนี้ผมต้องการคำนวนแผนเวลาการทำงานว่างานที่จะทำแต่ละชิ้นจะจบวันไหนครับ
โดยมีข้อมูลที่ว่า งาน1ชิ้นมี 12 process(บางชิ้นมี 16 process) แต่ละ process มีเวลาทำไม่เท่ากัน แต่1 วันเวลาทำงานอยู่ที่ 580 นาที
สิ่งที่ผมต้องการคำแนะนำจากทุกๆท่านคือ
A1=ชื่อชิ้นงาน B1=วันที่1 C1=วันที่2 D1=วันที่3 ไปเรื่อยๆจนครบ1เดือน
A2=ชื่อชิ้นงานที่1 B2=350 C2=580 D2=700 ไปเรื่อยๆจนครบprocess(หน่วยเป็นนาที)
A3=ชื่อชิ้นงานที่2 B3=500 C3=700 D3=450 ไปเรื่อยๆจนครบprocess(หน่วยเป็นนาที)
A4=ชื่อชิ้นงานที่3 B4=200 C4=300 D4=6000 ไปเรื่อยๆจนครบprocess(หน่วยเป็นนาที)

ผมอยากกำหนดว่าถ้าผลรวมของวันที่1มากกว่า580ห้ามลงข้อมูลใดๆเพิ่มหรือถ้ามากกว่า580ให้ค่าที่เกิน
580 ให้ค่าที่เด้งไปใส่ช่องของวันที่2แทนและช่องของวันที่2,3,4,5 ต่อไปเรื่อยๆให้เป็นสูตรเดียวกับช่องที่1
เช่นจากตัวอย่าง วันที่1ผลรวมจะเท่ากับ 1050ส่วนเกินจาก580 คือ 470 เพราะฉนั้นชิ้นงานที่3จะเริ่มในวันที่2 เนื่องจาก
วันที่2เหลือค่าอีก 580-470=110นาที(สามารถทำงานได้อีก110นาที)
ผมไม่ทราบว่าexcelสามารถทำได้ไหมเพราะผมยังอ่อนexcelอยู่มากครับผมใช้ได้แค่function พื้นฐาน
เช่น sum,sumif,if,countif,subtotal เท่านั้นครับ จึงอยากรบกวนพี่ๆทุกคนช่วยแนะนำสูตรให้ผมด้วยครับ
เพื่อให้ผมตอบโจทย์ได้ทั้งหมด ขอบคุณล่วงหน้าครับ

วิธีการวางแผนการทำงาน.xlsx

[DhitiBank]

สวัสดีครับ พอดีอ่านแล้วงงเพราะสิ่งที่อธิบายไม่มีในตัวอย่างไฟล์แนบ เลยขอข้อมูลเพิ่มเติมครับ

1. ฐานข้อมูลคือ Sheet1 และต้องการเขียนสูตรใน Sheet2 ตามเงื่อนไขที่กล่าวมาใช่หรือไม่ครับ

2. หากใช่ รบกวนอธิบายรายละเอียดในรูปหน่อยได้ไหมครับ เช่น งานชิ้นที่ 1 ในแถวที่ 5, 6 (ที่จริง อาจไม่ต้องผสานเซลล์ก็ได้ครับ เพราะเราสามารถปรับความสูงของแถวได้ ซึ่งหากชีทนี้เป็นฐานข้อมูลจริงการใส่ข้อมูลเรียงลงมาแบบไม่เว้นแถวจะทำให้เขียนสูตรอ้างอิงสะดวกกว่า และอ้างครอบคลุมเนื้อที่น้อยกว่าเมื่อเทียบกับปริมาณข้อมูลเท่าๆ กันครับ) ใส่ตัวเลขเวลาไว้หลายวัน หมายความว่าอย่างไร แล้วลักษณะการวางแผนงานเป็นอย่างไร เพราะหากทำงานวันละ 580 นาที แต่ชิ้นงานแต่ละชิ้นต้องทำหลายวัน เช่นชิ้นที่ 1 นี้ แล้วชิ้นที่ 2 จะทำตามแผนที่อยู่ในรูปได้อย่างไรครับ

2015-07-24_101106.png

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

[klingnarang]

1. ฐานข้อมูลคือ sheet1 ครับ sheet 2 พยายามทำอยู่ครับแต่ทำไม่ได้
2.sheet1 เป็นข้อมูลดิบของแต่ละชิ้นงานครับ ขอโทษด้วยครับที่ใส่ไม่ละเอียดรอบคอบ สำหรับเรื่องผสานเซลยกเลิกได้ครับพอดีลืมเช็คด้วยครับ
สำหรับรายละเอียดในตารางแก้ให้เรียบร้อยแล้วครับ
แก้ไฟล์แนบให้เรียบร้อยแล้วครับ
ขอโทษด้วยนะครับที่ไม่ตรวจสอบก่อนแนบไฟล์

[klingnarang]

วิธีการวางแผนการทำงาน.xlsx

[DhitiBank]

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

[klingnarang]

sheet2 ประมาณนี้ครับผมว่าจะทำsheet ละ1processครับ(หมายความว่าsheet2ในexcelที่แนบมาคือprocess1ครับ) เพราะผมนึกไม่ออกว่า จะสร้าง sheetมาแบบไหนถึงจะดูในsheetเดียวกันได้ทั้งหมด
**ขออธิบายเพิ่มเติมครับว่า1processที่ว่าใช้1คนทำงานครับ และ1 คนทำงานได้580นาทีต่อวันเพราะแบบนี้จึงจำเป็นต้องfix580นาทีครับ***
ถ้าพี่ๆมีวิธีแนะนำเพิ่มเติมถึงการจัดเซลใหม่ที่สามารถดูข้อมูลในsheetเดียวกันทั้งหมด รบกวนด้วยนะครับเพราะผมอ่อนexcel
มากๆๆๆ เจอโจทย์แบบนี้ไปไม่เป็นเลยครับ

Untitled.jpg

[DhitiBank]

ลองแบบนี้ครับ
ที่ Sheet3

1. เซลล์ A1 ยกเลิกการผสานเซลล์ จากนั้นเซลล์ A1 และ B1 คีย์ Process1 และ Part Name ตามลำดับ

2. A3 คีย์
=IF(Sheet1!B4="","",Sheet1!B4)
Enter --> คัดลอกสูตรลงด้านล่าง

3. B3 คีย์
=IF($A3="",0,IF(SUM(B$2:B2)-B$2>=580,0,MIN(580-SUM(B$2:B2)+B$2,INDEX(OFFSET(Sheet1!$B$3,1,MATCH($A$1,Sheet1!$C$3:$Y$3,0),COUNT(Sheet1!$A:$A)),MATCH($A3,Sheet1!$B$4:$B$13,0))-SUMPRODUCT(--ISNUMBER($A3:A3),$A3:A3)+IF(ISNUMBER($A3),$A3))))
Enter --> คัดลอกไปทางขวาและลงล่างครับ

4. คลุมช่วง B3:AFXXX (XXX คือแถวใดๆ ที่คัดลอกสูตรลงไปถึงครับ)
จากนั้นกด Ctrl+1 --> แท็ป Number เลือก Custom --> ช่อง Type คีย์
0;;


วิธีใช้ก็แค่ คีย์ที่เซลล์ A1 เป็น ProcessX (X แทนตัวเลขใดๆ ที่เหมือนกับหัวตารางแถวที่ 3 ใน Sheet1 หรืออาจทำเป็น Drop down list ให้เลือกก็จะสะดวกขึ้นครับ)

[DhitiBank]

อ่อ ผมลืมสังเกต

ในการทำงานจริง ชิ้นงานต่างๆ จะมีชื่อซ้ำกันไหมครับ หากไม่มีซ้ำผมจะได้ไม่ต้องปรับสูตรครับ

--แก้ไข--
จะซ้ำหรือไม่ซ้ำก็ไม่เป็นไรครับ ปรับสูตรไปเลยดีกว่า
Sheet2: B3 คีย์

=IF($A3="",0,IF(SUM(B$2:B2)-B$2>=580,0,MIN(580-SUM(B$2:B2)+B$2,INDEX(OFFSET(Sheet1!$B$3,1,MATCH($A$1,Sheet1!$C$3:$Y$3,0),COUNT(Sheet1!$A:$A)),MATCH(ROWS(A$3:A3)&$A3,Sheet1!$A$4:$A$13&Sheet1!$B$4:$B$13,0))-SUMIF($A3:A3,">0")+IF(ISNUMBER($A3),$A3))))

กด Ctrl+Shift ค้างไว้ แล้วกด Enter

(หากกดถูกจะเห็นเครื่องหมากปีกกา { } คร่อมสูตรอยู่)
แล้วคัดลอกสูตรไปทางขวาและลงล่างครับ

ในการเอาไปใช้จริง ก็ปรับช่วงทำไฮไลต์สีแดงกับชมพูให้ตรงกับฐานข้อมูลซึ่งอยู่ใน Sheet1 นะครับ

[klingnarang]

อ่อ ผมลืมสังเกต

ในการทำงานจริง ชิ้นงานต่างๆ จะมีชื่อซ้ำกันไหมครับ หากไม่มีซ้ำผมจะได้ไม่ต้องปรับสูตรครับ

--แก้ไข--
จะซ้ำหรือไม่ซ้ำก็ไม่เป็นไรครับ ปรับสูตรไปเลยดีกว่า
Sheet2: B3 คีย์

=IF($A3="",0,IF(SUM(B$2:B2)-B$2>=580,0,MIN(580-SUM(B$2:B2)+B$2,INDEX(OFFSET(Sheet1!$B$3,1,MATCH($A$1,Sheet1!$C$3:$Y$3,0),COUNT(Sheet1!$A:$A)),MATCH(ROWS(A$3:A3)&$A3,Sheet1!$A$4:$A$13&Sheet1!$B$4:$B$13,0))-SUMIF($A3:A3,">0")+IF(ISNUMBER($A3),$A3))))

กด Ctrl+Shift ค้างไว้ แล้วกด Enter

(หากกดถูกจะเห็นเครื่องหมากปีกกา { } คร่อมสูตรอยู่)
แล้วคัดลอกสูตรไปทางขวาและลงล่างครับ

ในการเอาไปใช้จริง ก็ปรับช่วงทำไฮไลต์สีแดงกับชมพูให้ตรงกับฐานข้อมูลซึ่งอยู่ใน Sheet1 นะครับ

ขอบคุณมากๆๆๆๆๆๆครับพี่จะลองใช้ดูครับ

[klingnarang]

อ่อ ผมลืมสังเกต

ในการทำงานจริง ชิ้นงานต่างๆ จะมีชื่อซ้ำกันไหมครับ หากไม่มีซ้ำผมจะได้ไม่ต้องปรับสูตรครับ

--แก้ไข--
จะซ้ำหรือไม่ซ้ำก็ไม่เป็นไรครับ ปรับสูตรไปเลยดีกว่า
Sheet2: B3 คีย์

=IF($A3="",0,IF(SUM(B$2:B2)-B$2>=580,0,MIN(580-SUM(B$2:B2)+B$2,INDEX(OFFSET(Sheet1!$B$3,1,MATCH($A$1,Sheet1!$C$3:$Y$3,0),COUNT(Sheet1!$A:$A)),MATCH(ROWS(A$3:A3)&$A3,Sheet1!$A$4:$A$13&Sheet1!$B$4:$B$13,0))-SUMIF($A3:A3,">0")+IF(ISNUMBER($A3),$A3))))

กด Ctrl+Shift ค้างไว้ แล้วกด Enter

(หากกดถูกจะเห็นเครื่องหมากปีกกา { } คร่อมสูตรอยู่)
แล้วคัดลอกสูตรไปทางขวาและลงล่างครับ

ในการเอาไปใช้จริง ก็ปรับช่วงทำไฮไลต์สีแดงกับชมพูให้ตรงกับฐานข้อมูลซึ่งอยู่ใน Sheet1 นะครับ

พี่ครับพอแยกprocess เป็นอีกsheet แล้วมันไม่ต่อเนื่องกันน่ะครับทำยังไงครับถึงต่อเนื่องครับ
ตัวอย่างในเซลครับ
process LL0 ใช้เวลาทำงานวันที่1 ไปแล้ว 450 นาที แต่วันนึงทำงานได้580นาที เพราะฉะนั้นวันที่1มันจะสามารถทำงานได้อีก 130 นาที
ซึ่ง130 นาทีนี้คือprocess LL1
ทำได้ไหมครับแบบนี้
***1วันมีเวลาทำงาน 580นาที 1processใช้คน1คน****

วิธีการวางแผนการทำงานแยกprocess.xlsx

[DhitiBank]

T_T
ทำไมถึงได้ยากขนาดนี้ครับ จะลองดูนะครับ คงนานหน่อย

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

[klingnarang]

T_T
ทำไมถึงได้ยากขนาดนี้ครับ จะลองดูนะครับ คงนานหน่อย

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

ไม่แยกชีทก็ได้ครับ (ที่ผมจะแยกเพราะผมคิดว่ามันจะง่ายน่ะครับ)
T_T
ขอโทษด้วยนะครับที่รบกวนพี่หลายต่อหลายครั้ง และขอบคุณมากๆๆๆครับ ที่ช่วยเหลือ
ถ้าต้องใช้ VBA จริงๆ ผมคงงานงอกอ่ะครับเพราะขนาด excel ยังอ่อนเลย T_T

[DhitiBank]

1. "อ่ะครับ" คำว่า อ่ะ ผิดกฎบอร์ดนะครับ ที่นี่ไม่ให้ใช้ภาษา chat ครับ (ดูกฎด้านบน)
2. งานอาจยังไม่งอกก็ได้ครับ เพราะอาจารย์และคนที่เก่ง excel มากๆๆๆ กว่าผม ยังมีอีกมาก และพร้อมจะช่วยอยู่แล้วครับ

[klingnarang]

ขอโทษด้วยครับพี่ ครั้งหน้าจะศึกษากฎให้ละเอียดก่อนพิมพ์ครับ
T_T

[DhitiBank]

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

[klingnarang]

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

ขอบคุณมากครับพี่

[snasui]

process LL0 ใช้เวลาทำงานวันที่1 ไปแล้ว 450 นาที แต่วันนึงทำงานได้580นาที เพราะฉะนั้นวันที่1มันจะสามารถทำงานได้อีก 130 นาที

ถ้าดูตามไฟล์แนบในวันที่ 1 ทำงานไปแล้ว 580 นาที ไม่ใช่ 450 นาทีครับ

วันที่ 1 อยู่ในคอลัมน์ B โดย B3 มีค่าเท่ากับ 450 และ B4 มีค่าเท่ากับ 130 รวมแล้ว 580

หรือว่าเงื่อนไขเป็นอย่างไร ลองอธิบายมาเพิ่มเติมครับ

[DhitiBank]

ผมขออธิบายเงื่อนไขให้นะครับอาจารย์

ดูแค่ Item 6 และ 5 และ Process LL0 กับ LL1 ครับ

** 1 คนจะทำแค่ 1 Process **

วันที่1: คนที่1 จะทำโปรเซส LL0 เริ่มทำ item 6 เสร็จใช้เวลา 450 นาที (เหลือเวลาอีก 130 นาที) จึงมาทำ item 5 ต่ออีก 130 นาที ดังนั้น item 5 จะต้องไปทำต่อในวันที่ 2 อีก 570 นาทีจึงจะเสร็จ

วันที่1: คนที่ 2 จะทำโปรเซส LL1 รับ item 6 จากคนที่ 1 มาทำต่อ (ตอนนั้นเวลาผ่านไปแล้ว 450 นาที) จึงเหลือเวลาทำงานอีก 130 นาที ก็หมดเวลาทำงาน (ยังไม่เสร็จ ต้องไปทำ item 6 ต่อในวันที่ 2)

วันที่2: คนที่1 ทำโปรเซส LL0 ต่อโดยเอา item 5 มาทำต่ออีก 570 นาที ต่อด้วย item 4 ได้อีก 10 นาที

วันที่2: คนที่2 ทำโปรเซส LL1 ต่อโดยเอา item 6 มาทำต่ออีก 20 นาที (เสร็จ item 6) เหลือเวลาทำงานอีก 560 นาทีเพราะเริ่มงานเลย ไม่ต้องรองานเหมือนวันที่1 จากนั้นก็ต้องรอ item 5 จากคนที่ 1 (ซึ่งเอามาทำในวันที่ 2) จนกว่าคนที่ 1 จะทำเสร็จก็เหลือเวลาทำงานในวันนี้อีกแค่ 10 นาที คนที่ 2 จึงรับ item 5 มาทำ LL1 ได้อีกแค่ 10 นาที

ไปแบบนี้เรื่อยๆ ครับ.... นั่งคิดแล้วงงแท้ กระผมนี่ปวดหัวเลย

[klingnarang]

ผมขออธิบายเงื่อนไขให้นะครับอาจารย์

ดูแค่ Item 6 และ 5 และ Process LL0 กับ LL1 ครับ

** 1 คนจะทำแค่ 1 Process **

วันที่1: คนที่1 จะทำโปรเซส LL0 เริ่มทำ item 6 เสร็จใช้เวลา 450 นาที (เหลือเวลาอีก 130 นาที) จึงมาทำ item 5 ต่ออีก 130 นาที ดังนั้น item 5 จะต้องไปทำต่อในวันที่ 2 อีก 570 นาทีจึงจะเสร็จ

วันที่1: คนที่ 2 จะทำโปรเซส LL1 รับ item 6 จากคนที่ 1 มาทำต่อ (ตอนนั้นเวลาผ่านไปแล้ว 450 นาที) จึงเหลือเวลาทำงานอีก 130 นาที ก็หมดเวลาทำงาน (ยังไม่เสร็จ ต้องไปทำ item 6 ต่อในวันที่ 2)

วันที่2: คนที่1 ทำโปรเซส LL0 ต่อโดยเอา item 5 มาทำต่ออีก 570 นาที ต่อด้วย item 4 ได้อีก 10 นาที

วันที่2: คนที่2 ทำโปรเซส LL1 ต่อโดยเอา item 6 มาทำต่ออีก 20 นาที (เสร็จ item 6) เหลือเวลาทำงานอีก 560 นาทีเพราะเริ่มงานเลย ไม่ต้องรองานเหมือนวันที่1 จากนั้นก็ต้องรอ item 5 จากคนที่ 1 (ซึ่งเอามาทำในวันที่ 2) จนกว่าคนที่ 1 จะทำเสร็จก็เหลือเวลาทำงานในวันนี้อีกแค่ 10 นาที คนที่ 2 จึงรับ item 5 มาทำ LL1 ได้อีกแค่ 10 นาที

ไปแบบนี้เรื่อยๆ ครับ.... นั่งคิดแล้วงงแท้ กระผมนี่ปวดหัวเลย

ขอบคุณครับพี่ที่ช่วยอธิบายให้ครับ

พี่snasuiครับ ที่พี่Dhitibank อธิบายถูกต้องครับ

[snasui]

ลองช่วยสรุปมาพอสังเขปอีกทีว่าเซลล์ AK8 ตามภาพจะมีลักษณะการวางข้อมูลอย่างไรเมื่อไม่มี Process อื่นมาต่อท้ายครับ