Excel Forum

สงสัยค่ะอาจารย์ ทำไมต้องเติมคอลัมน์ A ในชีต BaseAlloc ให้เต็ม เซลล์ว่างต้องเติมค่าให้เหมือนค่าด้านบน เพราะอะไรค่ะ

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

ค่ะ ถ้าเปลี่นยนเป็นแผนก pwm แล้วต้องได้ยอดรวมเป็น 24 แต่ข้อมูลดึงมาเป็น ค่า 4 ต้องทำอย่างไรคะ

ใช้ Sumifs ได้ครับ

ลองเขียนมาเองก่อน ติดแล้วค่อยถามกันต่อ ฟังก์ชั่นนี้มีการถามตอบกันมาหลายครั้ง จำเป็นจะต้องฝึกใช้ให้เป็นครับ

ค่ะ จะลองปรับดูค่ะ
อาจารย์คะ Q : มีปัญหาการใช้สูตร array ค่ะ
คำตอบที่ได้ถูกต้องทุกอย่างนะคะ แต่ทำให้การคำนวณช้ามากๆ จนบางครั้งทำให้เครื่องแฮงค์ไปเลยค่ะ
มีวิธีการใช้สูตรแบบ formula ธรรมดาไหมคะ อยากหลีกเลี่ยงการใช้สูตร array เพราะข้อมูลดิบเยอะมากๆ
เวลาจะ copy ข้อมูลดิบแล้วมาแปะที่ data หรือทำอะไร เครื่องจะคำนวณตลอดเวลาต้องรอให้
เครื่องคำนวณเสร็จก่อน ถีงค่อยทำได้ค่ะ

ยกตัวอย่างเช่น
ในปี 2019 เดือน Jan Type Vehicle Dept VGEN จะดึงข้อมูลจาก sheet Data_Currently high light สีเหลือง ยอดรวมกันได้ 7,113.10
ในปี 2020 ยอดรวม Type Vehicle Dept VGEN จะดึงข้อมูลจาก sheet Data_Currently high light สีแดง ยอดรวมกันได้ 74,668.84
ในปี 2021 ยอดรวม Type Vehicle Dept VGEN จะดึงข้อมูลจาก sheet Data_Currently high light สีน้ำเงิน ยอดรวมกันได้ 29,343.10

ขอขอบคุณค่ะ

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

1. ที่ C5 คีย์
   =SUMIFS(INDEX(Data_Currently!$L$7:$BK$1000,0,MATCH((1&"/"&C$4&"/"&$P$4-1)+0,Data_Currently!$L$6:$BK$6,0)),Data_Currently!$F$7:$F$1000,$S5,Data_Currently!$D$7:$D$1000,$B5)
   Enter > Copy ไปด้านขวาถึง N5 และลงด้านล่าง
2. ที่ P5 คีย์
   =SUMIFS(INDEX(Data_Currently!$L$7:$BK$1000,0,MATCH("FY"&P$4,Data_Currently!$L$6:$BK$6,0)),Data_Currently!$F$7:$F$1000,$S5,Data_Currently!$D$7:$D$1000,$B5)
   Enter > Copy ไป Q5 และลงด้านล่าง

ขอบคุณมากๆเลยค่ะอาจารย์ ช่วยประหยัดเวลาการทำงานได้เยอะมากเลย อาจารย์เก่งมากเลยค่ะ:D

อาจารย์ช่วยอธิบายการทำงานของสูตรได้ไหมค่ะ ไม่เข้าใจตรงช่วงนี้ค่ะ MATCH((1&"/"&C$4&"/"&$P$4-1)+0 สูตร sumifs+index+match ได้ด้วยหรอคะ ซับซ้อนจนงงเลยค่ะ
แล้วอาจารย์มี logic คิดอย่างไรว่าต้องใช้สูตรนี้มาประยุกต์คะ ทั้งๆที่มีสูตรมากมายคะ

สูตรนั้นเป็นการหาว่าคอลัมน์ของปี ก่อนหน้า P4 อยู่ในลำดับที่เท่าไรของ Data_Currently!$L$6:$BK$6 และจะนำลำดับนั้นไปใช้กับ Index อีกที สังเกตว่าใช้ P4-1 เพื่อให้ลดลง 1 ลำดับ

1&"/"&C$4&"/"&$P$4-1 คือการเชื่อมข้อความเพื่อทำให้เป็น วันเดือนปี

(1&"/"&C$4&"/"&$P$4-1)+0 เป็นการแปลงกลับมาให้เป็นตัวเลขเพื่อที่จะใช้ Match กันได้

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

ปกติแต่ละส่วนประกอบของฟังก์ชั่นเราสามารถซ้อนฟังก์ชั่นอื่น ๆ เข้าไปได้ตามต้องการจึงเห็นว่าเป็นความซับซ้อนครับ

ขอบคุณอาจารย์มากค่ะ ทำให้เข้าใจมากยิ่งขึ้น จะพยายามศึกษาต่อไปคะ

มีปัญหาอีกนิดนึงค่ะ
โดยมีเงื่อนไขว่าถ้าเป็น VGEN หรือ VONL_C ให้คำนวณสูตร นี้ =-SUMIFS(Data!N$7:N$32,Data!$A$7:$A$32,$B$1,Data!$D$7:$D$32,$B5) column C2
ซึ่งจะรู้ได้โดยดูที่ Column C2
ยกตัวอย่าง
เช่น ถ้า column cell C2 เป็น VGEN ให้แสดงที่ Column C5 : N10
ถ้า column cell C2 เป็น VONL_C ให้แสดงที่ Column C33 : N38
=IF($C$2=S33,(-SUMIFS(Data!N$7:N$32,Data!$A$7:$A$32,$B$1,Data!$D$7:$D$32,$B33)),0)
ซึ่งลองใช้ if แล้วมีเงื่อนไข ข้อมูลไม่มาค่ะ
ยอดที่ถูกต้องจะแสดงที่เดือน Sep : Dec แต่ละเดือน = 35,666.67 โดยดึงข้อมูลจาก sheet Data ค่ะ

การอ้างอิงตำแหน่งเซลล์ควรเป็นด้านล่างครับ

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

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

1. ที่ C5 คีย์
   =IF(OR($C$2="VGEN",$C$2="VONL_C"),-SUMIFS(Data!N$7:N$32,Data!$A$7:$A$32,$B$1,Data!$D$7:$D$32,$B5),0)
   Enter > Copy ลงด้านล่าง
2. ที่ C33 คีย์
   =IF($C$2="VONL_C",-SUMIFS(Data!N$7:N$32,Data!$A$7:$A$32,$B$1,Data!$D$7:$D$32,$B5),0)
   Enter > Copy ลงด้านล่าง

ได้แล้วค่ะอาจารย์ ขอบคุณมากค่ะ

อาจารย์ค่ะ สงสัยคะ ทำไม อ้างอิงระบุ cell แล้ว
เช่น S33 ก็ =VONL_C เหมือนที่อาจารย์ระบุชื่อ
IF($C$2=S33,(-SUMIFS(Data!N$7:N$32,Data!$A$7:$A$32,$B$1,Data!$D$7:$D$32,$B33)),0)

ของอาจารย์

ที่ C33 คีย์
=IF($C$2="VONL_C",-SUMIFS(Data!N$7:N$32,Data!$A$7:$A$32,$B$1,Data!$D$7:$D$32,$B5),0)

ทำไม ไม่ได้คะ ทั้งๆที่เมื่อก่อนทำได้คะ

ปัจจุบัน S33 คือค่าใด ทราบได้อย่างไรว่าเป็นค่านั้นครับ

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

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

ดูถี่ถ้วนแล้วค่ะอาจารย์ อักขระเท่ากันทุกตัวอักษร ทุกประการ ไม่มีการเว้นวรรคแต่อย่างใดเลยค่ะ

แนบไฟล์นั้นมาด้วยจะได้ช่วยดูให้ได้ครับ

ตามเอกสารที่แนบค่ะ

สูตรจะต้องเป็นตามด้านล่างครับ

=IF($C$2=$S33,...)

จะต้อง Lock หรือตรึงตำแหน่งด้วย เรื่องนี้เป็นเรื่องสำคัญอันดับแรก ๆ ที่ต้องทำความเข้าใจครับ

ดู Link นี้ประกอบครับ Lock Cell, wordpress/beginner-knowledge/

อ๋อเข้าใจแล้วค่ะอาจารย์ ขอบคุณมากค่ะ