Excel Forum

รบกวนอีกครั้งครับ เนื่องจากในตารางคำนวณที่แนบมานี้ ตั้งแต่ AK เป็นต้นไป จะเป็นตารางการคำนวณแบบวัน
ต้องการให้ตารางคำนวณเป็นวันเวลาที่เริ่ม และเวลาที่จบ โดยอ้างอิงจาก cell W1 ที่เป็นกำลังการผลิตต่อชั่วโมง
สูตร คือ จำนวนที่ผลิตได้จริง / (จำนวนที่ผลิตได้ต่อชั่วโมงx24) จะได้ผลลัพธ์ ที่เป็นวันออกมา แล้วให้ตารางสูตร
คำนวณไปข้างหน้า คือวันเวลาเริ่ม ที่ cell AK2 เป็นตัวตั้ง แล้วบวกด้วยผลลัพธ์ ที่เป็น วัน ก็จะได้วันเวลาที่จบ
ปัญหา คือจะให้แสดงผลอย่างไรดี ที่ให้เกิดความสันพันธ์กัน เพราะแผนจะมีการปรับเปลี่ยนได้เรื่อย ๆ

ลองเขียนตัวอย่างผลลัพธ์ที่ต้องการในรูปแบบเวลามาสัก 4-5 ตัวอย่างครับว่าผลลัพธ์ที่ถูกต้องนั้นเป็นอย่างไร ทั้งไปด้านขวาและลงด้านล่างเพื่อจะได้เทียบกับสูตรที่คำนวณได้ และเพื่อความเข้าใจถูกต้องตรงกัน

ตอนนี้ทำได้แล้วครับเรื่องการแสดงเวลา มีคำถามใหม่ คือต้องการใช้ condition formatting ในการเปลี่ยนสีตัวที่อยู่ล่างสุดในตารางคำนวณ
ตั้งแต่ column AK เป็นต้นไป เช่น ที่ cell AK8 เป็นสีเหลือง (ต้องการตัวล่างของแต่ละ column เป็นสีแยกออกมาจากสีเขียว)

ลองตามนี้ครับ

1. คลุม AK7:EU18
2. เข้าเมนู Format > Conditional Formatting แล้วกำหนดเงื่อนไขตามภาพ โดยเงื่อนไขแรกคือ

=AK7=LOOKUP(2,1/(AK$7:AK$18>0),AK$7:AK$18)

ส่วนเงื่อนไขที่สองคือเงื่อนไขเดิม

Note: ปรับสีพื้นสี Font ให้ตรงตามต้องการ

Ohh ไวจริงๆ post ปุ๊บ ตอบปั๊บ ยิ่งกว่าประกันซะอีก มาเร็ว เคลมเร็ว
รบกวนขอคำอธิบายอีกนิดครับ

ekkaluk_suk wrote:Ohh ไวจริงๆ post ปุ๊บ ตอบปั๊บ ยิ่งกว่าประกันซะอีก มาเร็ว เคลมเร็ว
รบกวนขอคำอธิบายอีกนิดครับ

เข้ามาแ้ล้วเจอคำถามพอดีครับ

snasui wrote: ลองตามนี้ครับ

1. คลุม AK7:EU18
2. เข้าเมนู Format > Conditional Formatting แล้วกำหนดเงื่อนไขตามภาพ โดยเงื่อนไขแรกคือ

=AK7=LOOKUP(2,1/(AK$7:AK$18>0),AK$7:AK$18)

ส่วนเงื่อนไขที่สองคือเงื่อนไขเดิม

Note: ปรับสีพื้นสี Font ให้ตรงตามต้องการ

จากสูตรด้านบนเป็นการหาค่าสุดท้ายที่ตรงตราเงื่อนไข ถ้าแปลตรง ๆ ก็คือหาค่าที่น้อยกว่าหรือเท่ากับ 2 จากช่วง 1/(AK$7:AK$18>0) แล้วนำค่าที่ตรงกันใน AK$7:AK$18 มาแสดง ซึ่งคำตอบคือค่าสุดท้ายที่ไม่ใช่ Div/0!

จากช่วง 1/(AK$7:AK$18>0) จะได้ผลลัพธ์เป็น 1 หรือ Div/0! แล้วแต่ว่าช่วง AK$7:AK$18>0 เป็นจริงหรือไม่ ถ้า AK$7:AK$18>0 เป็นจริงก็จะแสดงค่า True ไม่เป็นจริงก็แสดงค่า False เมื่อนำ 1/True ก็จะได้ 1 เมื่อนำ 1 มาหาร False นั่นคือหารด้วย 0 ก็จะเป็น Div/0!

ถ้าเราแกะสูตรด้วยการคลุม AK$7:AK$18>0 แล้วกดแป้น F9 จะได้เป็น

=LOOKUP(2,1/({TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}),{102000;60.0000000000728;0;0;0;0;0;0;0;0;0;0})

หากคลุมช่วง 1/({TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}) แล้วกดแป้น F9 จะได้เป็น

=LOOKUP(2,{1;1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!},{102000;60.0000000000728;0;0;0;0;0;0;0;0;0;0})

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

เข้าใจยากหน่อยครับ สามารถอ่าน Lookup ได้จากที่นี่น่าะจะทำให้เข้าใจได้ดีขึ้น http://office.microsoft.com/th-th/excel ... P010079186

จากปัญหาเดิมนะครับ
ต้องการให้แสดงเวลาใน cell แต่ละ cell ของตาราง AS-BF โดยให้เงื่อนไขว่า cell ไหนมีค่าก็ให้แสดงเวลาเริ่มและเวลาจบ โดยแต่ละวันใน cell จะจบที่ 19.00 และ 7.00 ตามเวลาที่ระบุอยู่ในแถวที่ 2 (ตอนนี้กำหนดให้มาแสดงที่ AL และ AM แต่ข้อมูลที่มาแสดงจะเป็นจำนวนรวมของแถว ต้องการให้แสดงของแต่ละ Cell ที่มีข้อมูล)
เนื่องจากในการทำงานจะเป็น 2 กะ เช้า 7.00-19.00 ดึก 19.00-7.00 ดังนั้นแต่ละ cell ต้องจบที่ตรงกะพอดี ถ้าเกินให้ขยับไปวันถัดไป

ขอตัวอย่างครับ เช่น

1. เซลล์ AS7 มีค่าเป็นอะไร
2. เซลล์ AT7 มีค่าเป็นอะไร
3. เซลล์ AT8 มีค่าเป็นอะไร
4. เซลล์ AU8 มี่ค่าเป็นอะไร

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

ขอเวลาเรียบเรียงก่อนนะครับ และจะเอามาให้ดูอีกครั้ง

ขอรบกวนอีกครั้งครับ

1.=AN10+(AV31/$W$1)/24 ที่มีอยู่ในสูตร ค่า AN ในสูตรจะมาจากการคีย์อิน แต่ต้องการสร้างให้เป็นสูตรอัตโนมัติที่รับค่ามาจากแถวที่ตัดคอลัมม์ก่อนค่าสุดท้ายในคอลัมม์นั้นๆ

2.ค่าที่อยู่ในแต่ละ cell ในตารางคำนวนของแต่ละช่อง ให้เอาค่าที่ Column AM มาทำการลดยอด โดยผลลัพธ์ที่ได้จะต้องไม่ไปกระทบที่ช่องถัดไป เช่น ที่ AU7 เป็นค่า 30,600 ถ้ามีค่าที่ช่อง AN7 0.13 = 30,600-(0.125day*5,400pcs/hr*24hrs)
ยอดที่ต้องแสดงคือ 30,600-16,200 = 14,400

ลองตามนี้ครับ

ที่เซลล์ AU2 คีย์

=INDEX($AN$7:$AN$19,MATCH(LOOKUP(2,1/(AU$7:AU$20>0),AU$7:AU$20),AU$7:AU$20,0)-1)+(AU31/$W$1)/24

Enter > Copy ไปทางขวา

สำหรับสูตรในตาราง ยังไม่เคลียร์ครับ ปกติถ้าเซลล์ไหนเปลี่ยนมันก็จะเปลี่ยนเป็นลูกโซ่ แต่นี่เหมือนกับว่าให้เป็นแค่เซลล์ที่มีค่า 0.13 อยู่ในคอลัมน์ AM ไม่ทราบว่าในบรรทัดเดียวกันกระทบด้วยหรือไม่

สูตรที่ AU2 OK แล้วครับ ตรงตามต้องการ
ข้อ 2 คือ ค่าที่อยู่ใน AM จะเป็นตัวลดยอดจำนวนที่ต้องผลิตได้ในแต่ละ cell ที่แสดง คือปกติแต่ละ column จะไปอ่านที่ target แถวที่ 29
แต่สำหรับตัวใหม่ที่ต้องการ คือ ให้สูตรในแต่ละ cell ลดจำนวนโดยไปอ่านที่ช่อง AM ของแต่ละ cell ว่า setup time หรือไม่( AM จะเป็น Setup time) เนื่องจากต้อง
การให้ค่าที่ได้หัก setup time ซึ่งผลที่ได้คือจะลดยอดที่ควรจะได้ลง บรรทัดเดียวกันจะกระทบด้วยคือ จำนวนที่ลด จะต้องไปเพิ่มที่ cell ต่อไป
เช่น A1 ค่าเดิม

เช่น A1 ค่าเดิม 30,000 ถ้าที่ cell AM1 มีค่า 0.13{(0.125*24)*5,400)} = 16,200
ดังนั้นที่ A1 30,000 -16,200 = 14,800 ยอดที่เหลือให้ไปลงที่ cell ขวามือถัดไป (B1)

Remark : A1 ,B1 สมมุติ

ลองตามนี้ครับ

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

ที่เซลล์ AU7 คีย์

=IF(AND(ROWS(AU$7:AU7)>1,LOOKUP(9.99999999999999E+307,AU$5:AU6)=0,COUNTIF(AU$6:AU6,">0")>0),0,IF(AV6<>0,0,MAX(0,IF($AK7-SUM($AT7:AT7)>=AU$29*$X7-SUM(AU$6:AU6),AU$29*$X7-SUM(AU$6:AU6),$AK7-SUM($AT7:AT7)))))-IF(AND(COLUMNS($AU7:AU7)=1,$AM7=3/24),16200,0)

Enter > Copy ไปทางขวาและลงด้านล่าง

ขอคำอธิบายชุดนี้หน่อยครับ ใกล้ความจริงแล้วครับ
-IF(AND(COLUMNS($AU7:AU7)=1,$AM7=3/24),16200,0)

ekkaluk_suk wrote:ขอคำอธิบายชุดนี้หน่อยครับ ใกล้ความจริงแล้วครับ
-IF(AND(COLUMNS($AU7:AU7)=1,$AM7=3/24),16200,0)

ถ้าใน column AU ใดเป็นจริง และช่อง AM7 มีค่าเท่ากับ 3/24 ให้ ชุดแรกทั้งหมดลบด้วย 16,200 ใช่มั้ยครับ

ถูกต้องครับ ชุดแรกหมายถึงคอลัมน์แรกนี้เท่านั้นครับ

ถ้าต้องการให้ที่ AM แสดงค่าอัตโนมัติ โดยคำนวณมาจากช่อง AR+AS ที่แถวของตัวเอง โดยถ้า AR =1 สูตรคือ (1*(0.25*3))/24
และที่ AS = 1 สูตรคือ (1*3)/24 ส่วนที่ AU นั้นก้อจะไปอ่านค่าที่ AM ว่ามีค่าหรือไม่ ถ้ามีค่าก็จะไปหักยอดออก แต่ถ้าไม่มีก็เป็นจำนวนปกติ (ซึ่งตอนนี้ AU จะไปอ่านค่าที่ AM ที่เป็น 3/24 หรือไม่ ถ้าใช่จะถูกลดยอดด้วย 16,200 ซึ่งยังไม่ถูกต้อง ที่ถูกคือ เมื่อได้ค่า AM แล้วให้ที่ AU ลบค่าที่ AM ออกโดย ถ้า AM ได้ค่าเท่าไหร่ ให้คูณด้วย 64,800)
--------
64,800 มาจาก 1 hrs=5,400 pcs
AM = ผลการคำนวณ AR + ผลการคำนวณ AS
AU = (ผลการคำนวณทั้งหมด)-AM

snasui wrote: ลองตามนี้ครับ

ที่เซลล์ AU2 คีย์

=INDEX($AN$7:$AN$19,MATCH(LOOKUP(2,1/(AU$7:AU$20>0),AU$7:AU$20),AU$7:AU$20,0)-1)+(AU31/$W$1)/24

Enter > Copy ไปทางขวา

สำหรับสูตรในตาราง ยังไม่เคลียร์ครับ ปกติถ้าเซลล์ไหนเปลี่ยนมันก็จะเปลี่ยนเป็นลูกโซ่ แต่นี่เหมือนกับว่าให้เป็นแค่เซลล์ที่มีค่า 0.13 อยู่ในคอลัมน์ AM ไม่ทราบว่าในบรรทัดเดียวกันกระทบด้วยหรือไม่

ในกรณีที่ au มีค่าเดียว จะส่งผลให้ที่ AU2 เป็น #value! ต้องแก้ไขอย่างไรครับ

[/img]

ekkaluk_suk wrote:

snasui wrote: ลองตามนี้ครับ

ที่เซลล์ AU2 คีย์

=INDEX($AN$7:$AN$19,MATCH(LOOKUP(2,1/(AU$7:AU$20>0),AU$7:AU$20),AU$7:AU$20,0)-1)+(AU31/$W$1)/24

Enter > Copy ไปทางขวา

สำหรับสูตรในตาราง ยังไม่เคลียร์ครับ ปกติถ้าเซลล์ไหนเปลี่ยนมันก็จะเปลี่ยนเป็นลูกโซ่ แต่นี่เหมือนกับว่าให้เป็นแค่เซลล์ที่มีค่า 0.13 อยู่ในคอลัมน์ AM ไม่ทราบว่าในบรรทัดเดียวกันกระทบด้วยหรือไม่

ในกรณีที่ au มีค่าเดียว จะส่งผลให้ที่ AU2 เป็น #value! ต้องแก้ไขอย่างไรครับ