กำหนด data validation ้วไม่เป็นไปตามเงื่อนไข

[March201711]

ค่ะ แล้ว ปี 2020 cell P คงมียอด 4000 และ ปี 2021 cell มียอด 4000 ค่ะ ใช้ copy มาแล้วไม่มียอดเลยค่ะ

[snasui]

Copy มาใช้ตรง ๆ ไม่ได้เพราะเงื่อนไขในการคำนวณไม่เหมือนกันครับ

ปี 2019 แยกเป็นรายเดือน ปี 2020 และ 2021 รวมทั้งปี จึงต้องปรับในส่วนของ Text(...) ในเซลล์ P2 เป็นด้านล่างส่วนอื่น ๆ ยังคงเดิมครับ

--(TEXT(Data!$K$8:$CI$8,"[$-]yyyy")=RIGHT(P$2,4))

[March201711]

ค่ะ ถ้าเราอยากจะสรุปยอดเป็นแต่ละประเภทสินทรัพย์ในแต่ละเดือนของปี 2019 ที่ cell b3 - m8 โดยดึงข้อมูลขาก sheet data ในแสดงใน sheet fully และยอดรวมทั้งปี 2020 กับ 2021 ต้องใช้สูตรเหมือนsheet FA ไหมคะ

[March201711]

แนบเอกสารไปแล้วไม่ผ่าน ลองใหม่ค่ะ

[snasui]

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

1. ที่ B3 คีย์
   =IFERROR(SUMPRODUCT(--(Data!$K$8:$CI$8=EOMONTH(B$2,-1)+1),INDEX(Data!$K$9:$CI$18,MATCH($A3,INDEX(Data!$B$9:$B$18,0),0),0)),0)
   Enter > Copy ไปด้านขวาถึง M3 > Copy ลงด้านล่าง
2. ที่ O3 คีย์
   =IFERROR(SUMPRODUCT(--(TEXT(Data!$K$8:$CI$8,"yyyy")=RIGHT(O$2,4)),INDEX(Data!$K$9:$CI$18,MATCH($A3,INDEX(Data!$B$9:$B$18,0),0),0)),0)
   Enter > Copy ไป P3 > Copy ลงด้านล่าง

[March201711]

อาจารย์คะ ตรง ประเภท office ในcell b4 ถึง m4 ยอดไม่ตรงกับยอดที่ได้ ดูได้จาก sheet data มี3 บรรทัด ที่ high light สีชมพูค่ะและ ประเภท renivation cell b8 ถึง m8 ไม่ตรงกับยอดที่ต้องการค่ะ ดูได้ จาก sheet data type renovation high light สีส้ม ค่ะ และ ปี 2020 กับ 2021 ด้วยค่ะ

[snasui]

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

1. ที่ B3 คีย์
   =IFERROR(SUMPRODUCT(--(Data!$B$9:$B$18=$A3),INDEX(Data!$K$9:$CI$18,0,MATCH(EOMONTH(B$2,-1)+1,Data!$K$8:$CI$8,0))),0)
   Enter > Copy ไปด้านขวาถึง M3 > ลงด้านล่าง
2. ที่ O3 คีย์
   =SUM(IF(Data!$B$9:$B$18=$A3,IF(TEXT(Data!$K$8:$CI$8,"yyyy")=RIGHT(O$2,4),IF(ISNUMBER(Data!$K$9:$CI$18),Data!$K$9:$CI$18))))
   Ctrl+Shift+Enter > Copy ไปด้านขวาถึง P3 > Copy ลงด้านล่าง

!

Note: Ctrl+Shift+Enter เป็นการสร้างสูตร Array โดยสามารถเลือกดำเนินการตามข้อ 1 หรือ 2 ด้านล่าง กรณีคีย์สูตรเอง เมื่อคีย์สูตรแล้ว แทนที่จะกด Enter ให้กดแป้น Ctrl และ Shift พร้อมกันค้างไว้แล้วกด Enter เพื่อสร้างเป็นสูตร Array กรณี Copy สูตรไปวางให้กดแป้น F2 เพื่อทำการ Edit Cell นั้นก่อน จากนั้นกดแป้น Ctrl และ Shift พร้อมกันค้างไว้แล้วตามด้วย Enter เพื่อสร้างเป็นสูตร Array หากกดแป้นถูกต้องสูตรนั้นจะมีเครื่องหมายปีกกาครอบ เช่น {=YourFormulas(...)} ปีกกานี้ไม่สามารถคีย์เข้าไปเอง จะต้องมาด้วยการกดแป้น Ctrl+Shift+Enter เท่านั้น ถ้ายังไม่เห็นปีกกาครอบสูตรแสดงว่ากดแป้นให้รับสูตรไม่ถูกต้อง การแก้ไขเปลี่ยนแปลงสูตร Array จะต้องกดแป้นให้รับสูตรด้วย Ctrl+Shift+Enter ทุกครั้ง

[March201711]

อาจารย์คะ ลองเช็คสูตรดูแล้ว สูตรไม่ใช่ยอดรวมคะ ดึงข้อมูลมาแค่รายการเดียว
เช่น แผนก VRES ประเภท hardware มี 2 รายการจาก sheet data cell f13 และ f14 แต่ดึงมารายการเดียว high light สีเขียว
และ แผนก VGENประเภท office มี 2 รายการจาก sheet data cell f15 และ f16 แต่ดึงมารายการเดียว high light สีส้มค่ะ

[snasui]

กรุณานำไฟล์ที่ได้ปรับสูตรล่าสุดตามที่ผมตอบไปแล้วในโพสต์ #27 มาถามกันต่อจะได้ตอบต่อไปจากนั้นครับ

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

[March201711]

ได้ตามที่อาจารย์ให้ค่ะ แต่ sheet Fully หนูผิดเอง คิดคำนวณผิดไปค่ะ ต้องคิดวันที่สินทรัพย์นั้นหมดอายุต้องดึงข้องมูลจาก cell J แล้วดูว่าหมดอายุเดือนไหนให้มาแสดงเดือนนั้น ปียอดรวมปี 2020 และ 2021 ค่ะ
ตามเอกสารแนบ ขอโทษด้วยค่ะอาจารย์

[snasui]

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

เซลล์ B3 คีย์

=SUMIFS(Data!$G$7:$G$13,Data!$D$7:$D$13,$A3,Data!$J$7:$J$13,EOMONTH(B$2,-1)+1)

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

[March201711]

ได้แล้วค่ะ แล้ว ยอดของปี 2020 กับ ปี 2021 ล่ะคะ

[snasui]

ช่วยอธิบายมาประกอบมาด้วยครับว่าเอาจากจากไหน ด้วยเงื่อนไขใดครับ

[March201711]

ดีงข้อมูลจาก sheet data ดูว่าสินทรัพย์หมดอายุปี 2020 กับ 2021 หรือไม่จาก cell j มาแสดงในsheet fully ค่ะ
เช่น ปี 2020
sheet data cellj10 j13 ถึง j16 หมดอายุปี 20 high light สีเขียว นำยอด cost cell g มาแสดงเป็นยอดรวมทั้งเดือน ใน sheet fully cell O3 ถึง O8 ในแต่ละประเภท
และ ปี 2021
sheet data cellj8 j9 j11 ถึง j12 หมดอายุปี 21 high light สีเหลือง นำยอด cost cell g มาแสดงเป็นยอดรวมทั้งเดือน ใน sheet fully cell P3 ถึง P8 ในแต่ละประเภทค่ะ

[snasui]

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

=SUMPRODUCT(--(TEXT(Data!$J$7:$J$16,"yyyy")=RIGHT(O$2,4)),--(Data!$D$7:$D$16=$A3),Data!$G$7:$G$16)

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

[March201711]

อาจารย์คะ ถ้าติด #N/A ปรับสูตรอย่างไรคะ

[snasui]

นำ Iferror เข้ามาครอบสูตรเดิมเพื่อไม่ให้แสดง Error ได้ครับ

[March201711]

ลองครอบแล้วค่ะ ขึ้น 0 ค่ะ

[snasui]

ให้นำ Iferror ไปครอบสูตร Vlookup ในคอลัมน์ D ของชีต Data ซึ่งเป็นต้นเหตุของการแสดงค่าผิดพลาด #N/A ครับ

[March201711]

ถ้าเราจะรวมวันที่เริ่มต้นใน cell h ในแต่ละปี และวันที่หมดอายุ ในcell j ของ sheet data ต้องปรับสูตรอย่างไรคะ ลองแล้วไม่ขึ้นยอดรวมเลยค่ะ
วันที่เริ่มต้น
เช่น ปี 2016 เริ่มมีcost รวมยอดได้ 170,000 ในcell
วันที่สิ้นสุด
เช่น ปี 2019 สิ้นสุด มียอดรวม 10,000 ใน cell g