Excel Forum

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

1. AV2 ปรับสูตรใหม่ไม่ให้แสดงค่าผิดพลาดกรณีที่มีค่าเดียว

=INDEX($AN$7:$AN$19,MAX(1,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 ไปด้านขวา

2. AM7 เขียนสูตรให้สัมพันธ์กับ AR+AS

=(AR7=1)*0.25*3/24+(AS7=1)*3/24

Enter > Copy ลงด้านล่าง

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

=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<>0),64800*$AM$7,0)))))

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

ขอเพิ่มเงื่อนไขใน cell คำนวณ อีกครั้งครับ หลังจากที่ - ด้วย if แล้วให้ไปเช็คกับแถวที่ 29 อีกครั้ง
=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<>0),129600*$AM7,0)))))

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

เข้าใจว่าผมวางสูตรไปลบผิดตำแหน่ง

ลองสูตรตามด้านล่างที่เซลล์ 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)-IF(AND(COLUMNS($AU7:AU7)=1,$AM7<>0),129600*$AM$7,0),$AK7-SUM($AT7:AT7)))))

Enter

ถ้ายังไม่ถูกต้องช่วยส่งไฟล์มาดูครับ

snasui wrote: เข้าใจว่าผมวางสูตรไปลบผิดตำแหน่ง

ลองสูตรตามด้านล่างที่เซลล์ 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)-IF(AND(COLUMNS($AU7:AU7)=1,$AM7<>0),129600*$AM$7,0),$AK7-SUM($AT7:AT7)))))

Enter

-IF(AND(COLUMNS($AU7:AU7)=1,$AM7<>0),129600*$AM$7,0
ตรงสีแดงต้องแก้เป็น $AM7 ครับ เพราะถ้าลากลงแล้วมันจะเพี้ยน
สูตรที่ส่งมาล่าสุด มันไม่ลดยอดให้น่ะครับ คือถ้า AM เปลี่ยนไป ผลในตารางคำนวณต้องเปลี่ยนแปลงตาม แต่ตอนนี้มันไม่เปลี่ยนเลยครับ

คือการเปลี่ยนแปลง ต้องคล้ายกับการที่เราใส่ค่าในช่อง AU4 ครับ หลักการเหมือนกัน

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

อ้าว...เพิ่งเห็นไฟล์...ขอดูไฟล์ก่อนครับ

นำส่วนเงื่อนไข If ไปไว้ที่ช่อง AT ครับ

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

ปรับปรุง เพิ่มเติมครับ
1.ต้องการให้ แถวที่ 3 และแถวที่ 4 auto โดยรับค่ามาจากผลรวมของ cell AR แสดงแถวที่ 3 และผลรวมของ AS แสดงแถวที่ 4 โดยมีเงื่อนไขว่า ให้สูตรอ่านจำนวนที่อยู่ในตาราง AU ก่อนแล้วค่อยไปมองผลรวมที่ช่อง AR และ AS เป็นชุด ๆ ของ column (จะไม่เข้าเงื่อนไข เมื่อใน ตาราง AU มีค่าน้อยกว่า 2 cell)
2.ที่แถวที่ 3 และ 4 จะลบด้วย 1 เสมอ หากในตาราง AU ด้านซ้ายของชุดข้อมูล มีค่ามากกว่า 0

ผมทดลองเขียนสูตรด้วยเงือนไขที่ให้มา (ตามภาพด้านล่าง) พบว่าเกิดการคำนวณเป็นวงกลม หรือก็คือการคำนวณ "วน" อ่านเพิ่มเติมที่ http://snasui.blogspot.com/2010/06/excel.html

แสดงว่ามีการนำเซลล์นั้นไปใช้คำนวณประกอบในช่วงข้อมูลด้านล่าง ถ้าต้องการให้แสดงผลถูกต้อง คงต้องหาวิธีไม่ใหเกิดการคำนวณวนก่อนครับ

=$G$1*60*(AU5-(((AU3*3*0.25)+AU4*3))*(AU5/(AU5+0.0000000000001)))

แถวที่ 29 ผมเอาสูตรที่เป็นสีแดงออก จะไม่เป็นวงกลมแล้วครับ แล้วต้องสร้างอย่างไรต่อครับ

ลองนำสูตรข้างบนไปใช้ดูแล้วยังครับ ลองปรับใช้ดูกับ AU4 ได้ผลเป็นอย่างไรนำมาโพสต์อีกรอบครับ

ได้นำค่าที่ต้องการให้ระบบแสดงให้ ใน sheet "ค่าที่ต้องการ" ส่วนคำอธิบายวิธีการคิดจะอยู่ใน Sheet "แนวคิด" เพื่อจะได้เข้าใจยิ่งขึ้นครับ
ส่วนในตารางคำนวณนั้น ผมใส่ค่าที่ต้องการที่ถูกต้องไว้นะครับ

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

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

ขอโทษด้วยครับ ข้อมูลที่ส่งให้ผิดพลาด ส่งให้ใหม่แล้วครับ ส่วน sheet แนวคิด
เป็นกระบวนการของที่มา ในสูตรน่ะครับ เพื่อจะได้เข้าใจยิ่งขึ้น ว่าช่วงเวลา setup จะ
อยู่ระหว่างงานแต่ละงานน่ะครับ

ผมลองคิดตามความเข้าใจว่าให้เอาเวลาเข้ามาเกี่ยวข้องกับการแสดงค่า 30600 หรือ 0 โดยใช้เซลล์ AU2 มาหาเวลา พบว่าเกิดกรคำนวณเป็นวงกลมตามภาพครับ

ช่วยส่ง file ที่แก้ไขกลับให้หน่อยครับ ผมลองพิมพ์ตามแล้วสูตรมัน error ส่วนตรงที่เป็นวงกลม
น่าจะเป็นแถวที่แถวที่ 27 ครับ ต้องลองเอาตัวแปรที่เป็นของ แถวที่ 3 และ 4 ออก ดู (เหมือนครั้งที่แล้ว)

ดูสูตรตามไฟล์แนบครับ