Snasui Excel Forum

ทำไมกำหนด data validation เป็นปี 2017 แล้ว เครื่องแสดงให้เป็น ปี 1905 คะ

กรุณาแนบไฟล์ที่ทำเช่นนั้นมาด้วยจะได้ช่วยตรวจสอบให้ได้ครับ

ขอโทษค่ะ ลืมแนบไป ตามนี้ค่ะ

เปลี่ยน Number Format เสียใหม่ เดิมกำหนดเป็น yyyy ซึ่งไม่ถูกต้อง การกำหนดให้เป็น yyyy นั้นจะต้องคำนึงถึงว่า ค่าในเซลล์ต้องเป็นวันที่และต้องการให้แสดงแค่ปีเช่นนี้เป็นต้น แต่หากเป็นตัวเลขทั่วไปให้กำหนดเป็น General ครับ

คลิกขวา > Format Cells > Tab Number เลือก General > OK

ถามต่อค่ะ ถ้ากำหนดสูตรให้คิดค่าเสื่อมวันเริ่มต้น และแสดงวันที่ สิ้นสุด ใน cell I9 โดยดูประเภทของสินทรัพย์ค่ะ
เช่น วันที่เริ่ม วันที่ 1/5/17 คิดอายุเป็นเดือน 36 เดือน ให้แสดงวันที่หมดอายุค่ะ
เช่น วันที่เริ่ม วันที่ 1/6/17 คิดอายุเป็นเดือน 60 เดือน ให้แสดงวันที่หมดอายุค่ะ

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

sheet Data ค่ะ ตรงcolumn I9 ถึงI18

ตัวอย่างสูตรในเซลล์ I9 ครับ

=DATE(YEAR(G9),MONTH(G9)+H9,DAY(G9))

ช่วยอธิบายสูตรได้ไหมคะ เหมือนขหายไป 1 เดือนค่ะ ถามต่อค่ะ ตรงcolumn K9 ถึง BV18 เป็นช่วงที่คิด ค่าเสื่อมณ วันเริ่มต้น ถึงวันที่สินทรัพย์หมดอายุค่ะ เช่น เริ่มวันที่ 1/05/16 สิ้นสุดสันที่ 1/05/19 ตรง column K9 หรือ column K12 เริ่มคิดวันที่ 1/06/17 ถึงสิ้นสุดวันที่ 1/06/20 ตรง column BY12 เหลือ 1 เดือนไม่ได้คิดค่ะ

การบวกไปทีละเดือนไม่ใช่บวกด้วย 31 ครับ

สูตรใน L8 ควรเป็นด้านล่าง

=DATE(YEAR($K8),MONTH($K8)+COLUMNS($K8:K8),DAY($K8)) หรือ
=EOMONTH(K8,1)

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

ตรงcolumn K9 ถึง BV18 เป็นช่วงที่คิด ค่าเสื่อม ณ วันเริ่มต้น ถึงวันที่สินทรัพย์หมดอายุค่ะ เช่น เริ่มวันที่ 1/05/16 สิ้นสุดสันที่ 1/05/19 ตรง column K9 (high light สีเหลือง) ให้ดึงค่า column J9 มาแสดงในวันที่เริ่มต้นไปเรื่อยๆ จนถึงวันหมดอายุค่ะ
อีกตัวอย่าง
หรือ column K12 เริ่มคิดวันที่ 1/06/17 ถึงสิ้นสุดวันที่ 1/06/20 ((high light สีแดง) ให้ดึงค่า column J12 มาแสดงในวันที่เริ่มต้นไปเรื่อยๆ จนถึงวันหมดอายุค่ะ

แก้ตามที่ผมบอกแล้วแนบไฟล์นั้นมาใหม่จะได้ดูต่อไปจากนั้นครับ

เพื่อการสื่อสารที่กระชับ สำหรับการอ้างอิง เช่น A9 เขียนว่า เซลล์ A9 หรือ A9 ได้เลย ไม่เรียกเป็น Column A9 หรือคอลัมน์ A9 และไม่เรียกเป็น Row A9 หรือ บรรทัด A9 ครับ

ค่ะ ตรง K9 ถึง CI18 (พื้นทาสีเทา)เป็นช่วงที่คิด ค่าเสื่อม ณ วันเริ่มต้น ถึงวันที่สินทรัพย์หมดอายุค่ะ เช่น เริ่มวันที่ 1/05/16 สิ้นสุดสันที่ 1/05/19 (high light สีเหลือง) ให้ดึงค่า J9 มาแสดงในวันที่เริ่มต้นไปเรื่อยๆ จนถึงวันหมดอายุ
อีกตัวอย่าง
หรือ K12 เริ่มคิดวันที่ 1/06/17 ถึงสิ้นสุดวันที่ 1/06/20 (high light สีแดง) ให้ดึงค่า J12 มาแสดงในวันที่เริ่มต้นไปเรื่อยๆ จนถึงวันหมดอายุค่ะ

เนื่องจากวันที่ในบรรทัดที่ 8 เริ่มวันที่ 1 ให้ปรับสูตรใน L8 ใหม่เป็นด้านล่างครับ

=EOMONTH(K8,0)+1

Enter > Copy ไปด้านขวา

เซลล์ K9 คีย์สูตร

=IF(AND($G9<=K$8,$I9>=K$8),$J9,"")

Enter > Copy ไปเซลล์ที่เกี่ยวข้อง

ติดปัญหาค่ะ ทำไม cell cl9 ถึง cl18 high light สีฟ้า หายไป 1 เดือน ค่ะ

การคิดค่าเสื่อมราคาตามที่แนบมานี้รวมเดือนแรกเข้าไปด้วย

หากเริ่มเดือน Jan สิ้นสุด Dec
หากเริ่มเดือน Feb สิ้นสุดเดือน Jan
ฯลฯ

ไม่ใช่เริ่ม Jan และสิ้นสุด Jan

เปรียบเทียบกับไฟล์ที่แนบมา เซลล์ I9 ควรเป็น Arp-19 ไม่ใช่ May-19

ดังนั้นควรปรับสูตรใหม่เป็นด้านล่างครับ

=DATE(YEAR(G9),MONTH(G9)+H9-1,DAY(G9))

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

ค่ะ ได้แล้วค่ะ ถ้าอยากจะสรุปยอดแต่ละประเภท แต่ละแผนก และแต่ละเดือน โดยดึงข้อมูลจาก sheet Data มาแสดงที่ sheet FA
เช่น แผนก vitg ประเภท hardware มีค่าเสื่อมแต่ละเดือน jan-dec 2019 cell c12 ถึง n12 โดยดึงจากsheet dataแต่ละเดือนมาแสดงใน sheet FA ส่วนปี 2020 และ 2021 ให้ดึงยอดรวมที่ cell P และ O ตามลำดับค่ะ

ตัวอย่างสูตรในเซลล์ C3 ครับ

=IFERROR(SUMPRODUCT(--(TEXT(Data!$K$8:$CI$8,"[$- ]mmmyyyy")=C$2&RIGHT($O$2,4)),INDEX(Data!$K$9:$CI$18,MATCH($A3&$B3,INDEX(Data!$E$9:$E$18&Data!$B$9:$B$18,0),0),0)),0)

Enter > Copy ไปด้านขวาและลงด้านล่างเฉพาะเซลล์ที่เกี่ยวข้อง

key สูตรแล้วไปผ่านค่ะ ต้องปรับอย่างไรคะ

ให้คัดลอกสูตรไปวางแทนคีย์เข้าไปเอง นอกจากนี้ควรจะตรวจสอบข้อมูลในชีต Data ในคอลัมน์ B และ E ว่าเหมือนกับข้อความในชีต FA คอลัมน์ B และ A ทุกประการ หากวรรคก็ต้องมีวรรคเหมือนกัน ไม่เช่นนั้นจะไม่ได้คำตอบครับ