Excel Forum

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

Conditional formatting สำหรับ I7:I25
=(I7<$J$4)*(I7>0)

K7:K25
=DATEDIF(E7,J$4,"ym")

แนบไฟล์ใหม่เพื่อตรวจคำตอบนะครับ

K7
=DATEDIF(E7,J$4,"ym")+(DAY(E7)<16)


CF formula

=(I7<--$J$4)*(I7<>"")

J4 เป็น Text ต้องใส่ -- เพื่อเปลี่ยนเป็นตัวเลข

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

เมื่อใช้สูตรนี้
=DATEDIF(F8,DATE(YEAR(F8),9,30),"m")+(DAY(F8)<=15)

ในคอมลัมน์ L ถ้าสินทรัพย์ที่ได้มาระหว่างเดือน ต.ค. - ธ.ค. จะมีค่า #NUM! เกิดขึ้น

จะมีวิธีการแก้ไขอย่างไรครับ

(การคำนวณค่าเสื่อมราคา จะใช้ปีงบประมาณ คือ 1 ต.ค. ปีนี้ - 30 ก.ย. ปีหน้า)

แนะนำให้สูตรโดยตรงทางการเงิน ในExcel จะมีฟังก์ชันนี้อยู่ เช่น DB,DDB,SLN,SYD,VDB คนที่เรียนด้านการเงินจะรู้วิธธีค่าเสื่อมราคา
DB= หาค่าเสื่อมราคาของสินทรัพย์สำหรับคาบเวลาที่ระบุโดยใช้ช่วงเวลาลดลงแน่นอน
DDB = หาค่าเสื่อมราคาของสินทรัพย์สำหรับช่วงเวลาที่ระบุเอง
SLN =หาค่าเสื่อมราคาแบบเส้นตรงของสินทรัพย์
SYD = หาค่าเสื่อมราคาแบบผลรวมจำนวนปีของสินทรัพย์สำหรับคาบเวลาที่ระบุ
VDB =หาค่าเสื่อมราคาของสินทรัพย์สำหรับคาบเวลาใดๆที่ระบุบางส่วน โดยใช้วิธีดุลลดลงสองเท่า

สูตร DB(Cost,Salvage,life,period,month)
Cost = ต้นทุนเริ่มแรกของสินทรัพย์
Salvage = มูลค่าที่เหลืออยู่หลังจากหักค่าเสื่อมราคาแล้ว
life = อายุการใช้งานของสินทรัพย์
period = เวลาที่ที่ต้องการคำนวณค่าเสื่อม
month = จำนวนเดือนในปีแรกถ้าไม่ระบุ ให้ค่า 12

ลืม https://support.office.com/th-th/articl ... 4ee549d9d7

เพราะ =DATEDIF(start_date,end_date,unit) start date ต้องน้อยกว่า end_date

แก้ไขโดยใช้สูตรให้ไป
L8
=DATEDIF(F8,K$4,"ym")+(DAY(F8)<16)
จริงๆ แค่ 11 เดือนแต่ +1 จากวันที่ ที่ไม่ถึง 16
ถ้าต้องการให้แสดงเป็น 0 ก็
=MOD(DATEDIF(F8,K$4,"ym")+(DAY(F8)<16),12)

ส่วนปีต้องเพิ่มอีก 1 ถ้า L2 เป็น 12
K8
=DATEDIF(F8,K$4,"y")+INT((DATEDIF(F8,K$4,"ym")+(DAY(F8)<16))/12)


ถ้าไม่ต้องการให้คำนวนในแถวที่ยังไม่ใส่วันที่ก็
K8
=IF(F8,DATEDIF(F8,K$4,"y")+INT((DATEDIF(F8,K$4,"ym")+(DAY(F8)<16))/12),"")
L8
=IF(F8,MOD(DATEDIF(F8,K$4,"ym")+(DAY(F8)<16),12),"")

วิธีนับอายุทรัพย์สิน ให้นับเป็นปีงบประมาณ ดังนี้
การนับอายุสินทรัพย์ จากคอลัมน์ K จะนับเป็นปีงบประมาณ
(ช่วงวันที่ 1 -15 ของเดือนนับเต็มเดือน ช่วงวันที่ 16 - สิ้นเดือนตัดทิ้ง)
วันที่ได้มา F8 เช่น 10/10/1991 นับถึง 30/09/2018 ได้เท่ากับ K8 = 27 ปี L8 = 0 เดือน
(การนับ นับจาก 10/1991 – 09/1992 เป็น 1 ปี)
(การนับ นับจาก 10/1992 – 09/1993 เป็น 1 ปี) นับไปเรื่อย ๆ จนถึง 09/2018
วันที่ได้มา F9 เช่น 10/11/2016 นับถึง 30/09/2018 ได้เท่ากับ K9 = 1 ปี L9 = 11 เดือน
(การนับ นับจาก 10/2016 – 09/2017 เป็น 11 เดือน)
(การนับ นับจาก 10/2017 – 09/2018 เป็น 1 ปี)
วันที่ได้มา F10 เช่น 16/12/2016 นับถึง 30/09/2018 ได้เท่ากับ K10 = 1 ปี L10 = 9 เดือน
(การนับ นับจาก 01/2017 – 09/2017 เป็น 9 เดือน)
(การนับ นับจาก 10/2017 – 09/2018 เป็น 1 ปี)

จากสูตรที่ท่านแนะนำมา ทำได้แล้วครับ ขอบพระคุณเป็นอย่างสูงครับ

เห็นข้อผิดพลาดอีกแล้วครับผม รบกวนท่านอาจารย์ และท่านผู้รู้โปรดเมตตาผมด้วยนะครับ

การนับเดือนตามเงื่อนไขไม่ถูกต้อง
ในคอลัมน์ M (ปี) คอลัมน์ N (เดือน)
ตาม คอลัมน์ H ถ้าวันที่ได้มา 1-15 ให้นับเป็น 1 เดือน
ถ้าได้มาวันที่ 16 - 31 ให้ตัดทิ้งไม่ต้องนับเดือนนั้น

ปัญหาคือถ้าเป็นวันที่ 31 ของเดือน โปรแกรมจะตัดทิ้งเป็น 2 เดือน

คำตอบ N8 = 8 เดือน
N9 = 6 เดือน
N10 = 4 เดือน
N11 = 2 เดือน
N12 = 1 เดือน

ปีในExcel ต้องใส่เป็น คศ.
30/9/2561 คือวันที่ 30 ก.ย. คศ 2561
จะมีปัญหาเรื่องวันจันทร์ - อาทิตย์ ไม่ตรง และปีที่เดือน ก.พ. มี 29 วัน
เช่นวันนี้วันพุธ 26/6/2019 Format เป็น long Date Wednesday, 26 June 2019
แต่ถ้าใส่ 26/6/2562 Format เป็น long Date Saturday, 26 June 2562 กลายเป็นวันเสาร์

ถ้าต้องการให้แสดงเป็น ปี พ.ศ. ให้ เลือก format:Date > Locale : Thai > Thai Buddhist M8
=IF(H8,DATEDIF(H8,M$4,"y")+(MONTH($M$4)-MONTH(H8)+(DAY(H8)<16)=12),"")
N8
=IF(H8,MOD(MONTH($M$4)-MONTH(H8)+(DAY(H8)<16),12),"")

เป็นพระคุณอย่างสูงครับ ที่ให้ความกรุณาช่วยเหลือ ขอบคุณมาก ๆ ครับผม

สอบถามปัญหาเพิ่มเติมครับ
จากคอลัมน์ H วันเดือนปีที่ได้มา ถ้าเป็นวันที่ 1 - 15 ต.ค. ของทุกปีจะมีปัญหา ในการนับอายุการใช้งานในคอลัมน์ M และคอมลัมน์ N ครับ จะขาดไป 1 ปี
จากตัวอย่างคำตอบที่ถูกต้อง m8 = 3 ปี m9 = 2 ปี m10 = 1 ปี

เพิ่มเติมข้อมูลให้มูลให้หลากหลายมากขึ้น ครับ
จากคอลัมน์ H วันเดือนปีที่ได้มา ถ้าเป็นวันที่ 1 - 15 ต.ค. ของทุกปีจะมีปัญหา ในการนับอายุการใช้งานในคอลัมน์ M
และคอมลัมน์ N ครับ จะขาดไป 1 ปี

จากตัวอย่างคำตอบที่ถูกต้อง m8 = 3 ปี m12 = 2 ปี m16 = 1 ปี

M8
=IF(H8,DATEDIF(H8,M$4,"y")+(MOD(MONTH($M$4)-MONTH(H8),12)+(DAY(H8)<16)=12),"")

ขอบคุณมาก ๆ อีกครั้งครับผม