Excel Forum

จากเดือน สค52 - ตค54 ค่าเช่าเดือนละ 1000 บาท
ที่ F2 รบกวนแนะนำสูตรด้วยค่ะ

อย่าพยายามกรอกข้อมูลวันที่เป็นปี พ.ศ เนื่องจากเอ็กเซลล์ไม่รู้จักชื่อปี พ.ศ

ที่ F2 นั้น ผลลัพธ์ได้ 27000 ผมคิดว่าน่าจะยังไม่ถูกครับ เพราะ Aug-52 ถึง Oct-54 เท่ากับ 2 ปี กับ 2 เดือน เพราะฉะนั้นคำตอบควรจะเป็น 26000 บาทครับ
ลองตามนี้ครับ
ที่ F2 คีย์ =SUM(DATEDIF(B2,D2,"Y")*12,DATEDIF(B2,D2,"YM"))*1000

นับเดือน สค 52 ด้วยนะคะ
จะได้ สค-ธค 52 = 5 เดือน เป็น 5000 บาท
มค-ธค 53 = 12 เดือน เป็น 12000 บาท
มค-ตค 54 = 10 เดือน เป็น 10000 บาท
รวมแล้วจะได้ 27,000 บาทค่ะ

งั้นก็บวกเข้าไปอีกครับ เนื่องจากสูตรมันนับได้ 2 ปี 2เดือน

แต่ที่ผมนับเองจะได้ประมาณนี้
1 สค 52 -1สค 53 = 1 ปี
1 สค 53 -1สค 54 = 1 ปี
1 สค 54 -1ตค 54 = 2 เดือน
รวมแล้ว 2 ปี 2 เดือน

พอแก้เป็น
ที่ F2 คีย์ =SUM(DATEDIF(B2,D2,"Y")*12+1,DATEDIF(B2,D2,"YM"))*1000
ตรงที่ต้องการเลยค่ะ เบื้องต้นรบกวนเพียงเท่านี้ก่อน ขอบพระคุณมากๆค่ะ

ให้แก้เป็น

=SUM(DATEDIF(B2,D2,"Y")*12,DATEDIF(B2,D2,"YM")+1)*1000

เพราะเราบวกเพิ่มแค่เดือน ไม่ใช่ปี ให้ปีเป็นค่า 12 เดือนไว้แบบนั้นจะดีที่สุดครับ ได้แยกแยะสูตรได้ถูก

มาแถมให้ครับ

สำหรับการคิดค่าเช่าเป็นเดือนสามารถใช้ Datedif ตามด้านล่างได้ครับ

=(DATEDIF(B2,D2,"m")+1)*1000

Enter หรือ

=(12*(YEAR(D2)-YEAR(B2))+MONTH(D2)-MONTH(B2)+1)*1000

Enter

เซลล์ B2 และ D2 ควรจะคีย์ให้เป็นปีค.ศ.และควรคีย์ปีให้ครบ 4 หลักเพื่อป้องกันค่าปีผิดพลาดครับ

ขอบคุณค่ะ

ค.ศ. เกรงว่าชาวบ้านอาจจะำไม่คุ้นชินเท่าไหร่ ...

ควรแจ้งให้ทราบว่าจำเป็นต้องคีย์ให้เป็นค.ศ.ครับ หากให้คีย์เป็นพ.ศ.ก็ต้องหาวิธีปรับมาให้เป็นค.ศ.เพื่อให้ผลการคำนวณถูกต้อง แต่ถ้าไม่เน้นความถูกต้องก็ไม่เป็นไรครับ

ยกตัวอย่างคีย์ 19/1/55 ซึ่งมีการคีย์ปีแค่ 2 หลักและคีย์เป็นพ.ศ. เช่นนี้ Excel จะเข้าใจว่าเป็น วันที่ 19 เดือน มกราคม ค.ศ. 1955

และหากคีย์ 19/1/2555 ซึ่งมีการคีย์ปีครบ 4 หลักและคีย์เป็นพ.ศ. เช่นนี้ Excel จะเข้าใจว่าเป็น วันที่ 19 เดือน มกราคม ค.ศ. 2555

ก็คียเป็น ค.ศ. ไปก่อนครับ แล้วก็เปลี่ยน format โดยคลิ๊กขวา > Format cell
ช่อง Catagory เลือก Date
ช่อง Locale(location) เปลี่ยนเป็น Thai
คราวนี้มันก็จะมี รูปแบบ พ.ศ. ให้เลือก
คราวนี้เวลาเราพิมพ์เป็นปี ค.ศ. กด enter ปั๊ก มันก็จะโชว์เป็นปี พ.ศ. ให้อัตโนมัติครับ
แต่คนคีย์อาจจะงงเอง เพราะคีย์ ค.ศ. แต่โชว์ ค่าเป็น พ.ศ.

รบกวนเพิ่มเติมค่ะ
จาก F3
=IF(B3>D3,0,SUM(DATEDIF(B3,D3,"Y")*12,DATEDIF(B3,D3,"YM")+1)*1000)
ถ้าไม่ีมีหนี้ค้าง จะไม่ปรากฎค่าใดๆ

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

จากคำถาม ช่อง F3 ควรจะเป็น 1000 ค่ะ

ขอบพระคุณค่ะ

กรณีให้แสดงเลข 1000 สามารถปรับสูตรที่ F2 เป็น

=ABS((12*(YEAR(D2)-YEAR(B2))+MONTH(D2)-MONTH(B2)+(B2<=D2))*1000)

Enter > Copy ลงด้านล่าง หากต้องการทราบว่ารายการนี้เป็นจ่ายล่วงหน้าหรือค้างชำระที่เซลล์ I2 คีย์

=IF(B2<=D2,"ค้างชำระ","จ่ายล่วงหน้า")

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

ขอบพระคุณอีกครั้งค่ะ

อาจารย์ครับ ผมไม่เคยใช้สูตรแบบนี้เลย

=ABS((12*(YEAR(D2)-YEAR(B2))+MONTH(D2)-MONTH(B2)+(B2<=D2))*1000)

มันคืออะไรเหรอครับ ใช้งานยังไงครับ

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

ลองดูคำอธิบายแต่ละฟังก์ชั่นตาม Link นี้ครับ http://office.microsoft.com/th-th/excel ... 04211.aspx