Snasui Excel Forum

ตรงช่อง F3 และ G3 จะต้องใช้สูตรแบบไหนในการแจ้งสถานะ และคำเตือนคะ
โดยมีเงื่อนไขดังนี้

- หากอีก 1 เดือนจะครบกำหนดสัญญาเช่า ในช่องสถานะ (คอลัมภ์F) ต้องการให้ขึ้นข้อความแสดงว่า อีก 1 เดือนครบกำหนดสัญญาเช่าโดยตัวหนังสือสีแดงพื้นหลังสีเหลืองโดยขึ้นค้างไว้ตลอดจนว่าจะถึงวันที่ครบกำหนดสัญญา โดยช่องคำเตือน(คอลัมภ์ G) ให้ขึ้นวันที่คงเหลือว่าเหลือกี่วันจะครบสัญญาเช่าแล้ววันตรงช่องคำเตือนค่อยๆลดไปตามวันที่ปัจจุบัน
- หากครบกำหนดสัญญาตรงกับวันที่ปัจจุบัน ให้แสดงว่า ครบกำหนดสัญญาเช่า (ในคอลัมภ์ F) โดยมีตัวหนังสื่อสีแดง หนา โดยช่องคำเตือนไม่ต้องปรากฎข้อความใดๆ
- หากเลยวันที่ปัจจุบันเลยครบกำหนดสัญญาเช่าแล้ว ในช่องสถานะ (คอลัมภ์ F) ให้ขึ้นว่า เลยสัญญาเช่ามาแล้ว โดยช่องคำเตือน(G) ให้ขึ้นตัวเลขที่เลยกำหนดว่ามากี่วัน โดยทั้งคอลัมภ์ F และ G ตัวหนังสือสีเหลือง พื้นหลังสีแดง

ตัวอย่างสูตรตามด้านล่างครับ

1. ที่ F3 คีย์
   =IF(E3-TODAY()<0,"เลยกำหนดสัญญาเช่ามาแล้ว",IF(E3-TODAY()=0,"ครบกำหนดสัญญาเช่า",IF(INT((E3-TODAY())/12)+1=1,"อีก 1 เดือน จะครบกำหนดสัญญาเช่า","")))
   Enter > Copy ลงด้านล่าง
2. ที่ G3 คีย์
   =IF(OR(H3="",H3="ครบกำหนดสัญญาเช่า"),"",IF(H3="อีก 1 เดือน จะครบกำหนดสัญญาเช่า","เหลืออีก "&E3-TODAY()&" วันจะครบกำหนดสัญญา",IF(H3="เลยกำหนดสัญญาเช่ามาแล้ว","เลยกำหนดสัญญามาแล้ว "&ABS(E3-TODAY())&" วัน","")))
   Enter > Copy ลงด้านล่าง

ส่วนเรื่องการแสดงสีต่าง ๆ ให้ลองพยายามดูเองก่อน ติดปัญหาแล้วค่อยถามกันต่อ ดู Link นี้เป็นตัวอย่างครับ viewtopic.php?t=6155

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

F8= จะต้องใช้สุตรอะไรให้นับจำนวนเดือนจาก D8 โดยบวกเดือนเพิ่ม 1 เดือนไปเรื่อยๆ หากครบปีให้โชว์จำนวนปีในช่อง E8 เนื่องจากลูกค้าเช่าไปเรื่อยๆ อยากให้มีการ Update วันที่ครบกำหนดออกไปล่วงหน้า 1 เดือน แล้วสถานะการเช่า (I8) ต้องการให้ขึ้นแจ้งว่า ลูกค้าเช่าเดือน/เดือน
G8 = อ้างอิงจาก F8 ต้องการหาสูตรยังไงให้ระยะเวลาจำนวนเดือน บวกเพิ่มไปล่วงหน้า 1 เดือน
H8= ต้องการให้บวกเดือนเพิ่มล่วงหน้า 1 เดือน อย่างเช่น ปัจจุบันเดือน 3 จะครบกำหนดสัญญา คือ 7/4/2561 ปรากฎตรงนี้
I8 = ต้องการให้ขึ้นสถานะการเช่าแจ้งว่าลูกค้าเช่าเดือน/เดือน
หรือจะต้องปรับคอลัมภ์ให้เป็นแบบไหนคะที่จะสามารถคำนวนตรงจุดนี้ได้

2. ตรงช่อง H9,I9,J9 และ H10,I10,J10 หากช่อง B9 และ B10 ขึ้นเป็นรถสแปร์ใช้ และรถยืมใช้ ตรง H9,I9,J9 และ H10,I10,J10 ไม่ต้องขึ้นข้อความใดๆ

ตามไฟล์แนบ F8 ต้องมีค่าเป็นเท่าไร คิดอย่างไรจึงได้ค่าเท่านั้นช่วยอธิบายมาด้วยครับ

ส่วน H9:J10 สามารถใช้สูตร If เข้ามาช่วยได้ ยกตัวอย่าง H9 คีย์สูตร

=If(Or($B9="รถสแปร์ใช้",$B9="ยืมใช้"),"",DATE(YEAR(D9),MONTH(D9)+G9,DAY(D9)-1))

Enter > Copy ลงด้านล่าง นำไปปรับใช้กับเซลล์ที่เหลือ สังเกตว่าตรงไหนคือสูตรเดิมของคอลัมน์ H วางไว้ตรงไหนของสูตรนี้ ในคอลัมน์อื่นก็ทำลักษณะเดียวกันครับ

ถ้าหากเปลี่ยน
E8 = 2
F8 = 3
F8 = 6/4/2561
หากวันที่ปัจจุบัน ทุกวันที่ 7 ของทุกๆเดือน
ตรงช่อง F8 = ต้องการให้บวกเพิ่ม 1 เดือน จากปัจจุบันคือ 3 ตรงช่อง F8 ให้เปลี่ยนเป็น 4
ตามรายละเอียดด้านล่าง
E8 = 2
F8 = 4
G8 = 6/5/2561
วนแบบนี้ไปเรื่อยๆค่ะ และตรงช่อง
H8 = สถานะการเช่าให้ขึ้นแจ้งว่า เช่าเดือน/เดือน
เพราะว่าถ้าลูกค้าเช่าเดือนต่อเดือน พอครบกำหนดเช่าต่อไปเรื่อยๆ
หรือพอจะมีฟอร์มไหนที่สามารถช่วยให้สามารถทำงานเได้เร็วขึ้นบ้างคะ

การเพิ่มค่า F8 อัตโนมัติได้จะต้องมีค่าคงที่ให้เปรียบเทียบ หากจะเทียบกับ G2 เซลล์ G2 จะต้องเป็นค่าคงที่ไม่ใช่เป็นสูตร =Today() ที่ผันแปรได้ครับ

สูตรใน G9:G10 เขียนได้ตรงกับที่ผมตอบไปแล้ว แต่ H9:I10 ยังไม่ถูกต้อง สูตรเดิมของมันเป็นอย่างไร ให้นำมาใช้ตามนั้น อ่านคำอธิบายในโพสต์ #8 ซ้ำอีกครั้ง สูตรเดิมของคอลัมน์นั้น ๆ เป็นอย่างไร ให้นำไปประยุกต์ใช้กับสูตรที่ผมตอบครับ

snasui wrote: Wed Mar 28, 2018 8:07 pm ตามไฟล์แนบ F8 ต้องมีค่าเป็นเท่าไร คิดอย่างไรจึงได้ค่าเท่านั้นช่วยอธิบายมาด้วยครับ

ส่วน H9:J10 สามารถใช้สูตร If เข้ามาช่วยได้ ยกตัวอย่าง H9 คีย์สูตร

=If(Or($B9="รถสแปร์ใช้",$B9="ยืมใช้"),"",DATE(YEAR(D9),MONTH(D9)+G9,DAY(D9)-1))

Enter > Copy ลงด้านล่าง นำไปปรับใช้กับเซลล์ที่เหลือ สังเกตว่าตรงไหนคือสูตรเดิมของคอลัมน์ H วางไว้ตรงไหนของสูตรนี้ ในคอลัมน์อื่นก็ทำลักษณะเดียวกันครับ

H9:I10 ได้ทำการแก้ไขแล้วค่ะ ส่วน F8

F8= ให้ตรวจสอบว่า
1. วันที่ปัจจุบัน อยู่ระหว่างวันที่ขอคอลัมภ์ M และ N หรือไม่
ตามตัวอย่างปัจจุบัน วันที่ 29/3/61 ดังนั้นอยู่ระหว่าง M5 และ N5 คือ (7/3/61-6/4/61)

2. และหากวันที่ปัจจุบันอยู่ระหว่างคอลัมภ์ M และ N แล้ว ตรงกับเลขอะไรในคอลัมภ์ L (คือจำนวนเดือนใน 1 ปี) ตามตัวอย่าง คือ ตรงกับเลข 3 ให้ดึงเลข 3 มาปรากฎในคอลัมภ์ F8 และให้คำนวนแบบนนี้ไปเรื่อยๆค่ะ ตามตัวอย่างด้านล่างค่ะ

ตัวอย่างสูตรที่ F8 ครับ

=LOOKUP(TODAY(),$M$3:$M$25,$L$3:$L$25)

Enter

เป็นการนำวันที่ปัจจุบันไปหาว่ามีค่ามากกว่าหรือเท่ากับค่าใดในคอลัมน์ M แล้วนำค่าที่ตรงกันในคอลัมน์ L มาแสดง