Excel Forum

เรียน คุณคนควน
จาก File แนบ รบกวนถามดังนี้ครับ
1. หน้า monthly ทำไม column ที่ระบายสีไว้ถึงไม่โชว์ข้อมูลครับ
2. File นี้ถ้าผมใช้เก็บข้อมูลไปเรื่อยๆจนถึงอีก 30-40 ปีข้างหน้ามีตรงไหนที่ต้องเขียนสูตรเพิ่มหรือไม่ครับ
รบกวนตอบคำถามพร้อมกับแสดงวิธีทำให้ดูเหมือนที่ผ่านมาด้วยครับ
ขอบคุณมากครับ
ขอแสดงความนับถือ
ผดุงเกียรติ เมฆอรุณ

กระทู้นี้ผมย้ายมาจาก http://www.snasui.com/viewtopic.php?f=6&t=232 ซึ่งเป็นหน้าประกาศ เป็นการโพสต์ไม่ถูกหมวดครับ

สำหรับคำถามที่ถามนั้นผมเข้าใจว่า ต้องการให้แสดงคำตอบแทนค่าผิดพลาด ที่ไม่แสดงคำตอบเพราะว่า
1. ส่วนประกอบในสูตรเอามาจากพื้นที่ที่แสดงค่าผิดพลาด (ชีท Monthly ช่วง P51:BN83)
2. ในพื้นที่ที่มีค่าผิดพลาดนั้น อ้างอิงชื่อที่ให้ไว้กับเซลล์แต่ไม่มีชื่อนั้น ( คือชื่อ Received)

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

เรียน คุณคนควน
ขอบคุณมากครับสำหรับคำตอบ แต่ผมยังหาจุดผิดพลาดไม่เจอครับ
1. พื้นที่ที่แสดงค่าผิดพลาด (ชีท Monthly ช่วง P51:BN83) นี้ผมเห็นแล้วครับ แต่ไม่รู้ว่ามันแสดงค่าผิดพลาดได้ยังไง
2. เช่น Cell P51=VLOOKUP($O51,Received,(P$48)+1) อ้างอิงชื่อของ table_array ซึ่งผมก็เห็นว่ามีชื่ออยู่ที่ Cell P49 ซึ่งคงจะไปอ้างอิงกับชุดข้อมูลของ work sheet Received ใช่มั้ยครับ ผมก็เลยไม่รู้จะแก้ยังไง ให้พื้นที่ที่มีค่าผิดพลาดนั้นแสดงค่า ช่วยชี้แนะอีกครั้งครับ
3. ที่ผมถามว่าถ้าใช้เก็บข้อมูลไปเรื่อยๆจนถึงอีก 30-40 ปีข้างหน้ามีตรงไหนที่ต้องเขียนสูตรเพิ่มหรือไม่นั้นเพราะว่า ตั้งแต่วันที่ 1/1/2011 เป็นต้นมานี้ File ต้นฉบับที่เป็น File ใหญ่กว่านี้แต่สูตรอ้างอิงคล้ายๆกัน กรณีผมต้องการให้ work sheet Daily โชว์ข้อมูลปริมาณการใช้ (UsedF1) เมื่อวันที่ 4/1/2011 ของ Header!C2 ที่ป้อนยอดการใช้ไว้ที่ work sheet UsedF1 ไว้แล้ว แต่ work sheet Daily cell I11 กลับไปเอาข้อมูลของวันไหนก็ไม่รู้มาโชว์ครับ, ที่ work sheet Monthly ก็เหมือนกันครับ มันโชว์ข้อมูลคนละช่วงกับที่เราต้องการครับ
4. File งานนี้เป็นไฟล์ที่รุ่นพี่ที่ออกไปแล้วเขียนไว้ให้ใช้ ซึ่งผมก็ไม่เข้าใจในการใช้ table_array ผมควรแก้สูตรแล้วเปลี่ยมาใช้ VLOOKUP ที่คุณคนควนเคยสอนไว้จะดีหรือไม่ครับ
ขอบคุณมากครับ
ขอแสดงความนับถือ
ผดุงเกียรติ เมฆอรุณ

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

เท่าที่ตรวจพบนั้นเป็นการนำค่าว่างไปกระทำกับค่าว่าง เช่นบวก ลบ คูณ หาร จึงเกิดเป็นค่าผิดพลาดเป็น #Value! ค่าว่างที่พูดถึงนี้เดาว่าน่าจะมาจากสูตรอื่น ค่าว่างเหล่านั้นไม่ใช่ค่า Blank ครับ แต่เป็นอักขระที่แทนความว่างมีสภาพเป็น Text จะไม่สามารถนำมาบวกลบคูณหารเหมือนเซลล์ที่ว่างเปล่าจริง ๆ (ฺBlank) เนื่องจากเซลล์ว่างเปล่านั้นมีค่าเป็น 0 ซึ่งเป็น Number

ดังนั้น หากต้้องการนำผลลัพธ์จากสูตรไปคำนวณต่อควรหลีกเลี่ยงให้แสดงค่าว่างหากไม่เข้าตามเงื่อนไข แต่ให้แสดงเป็น 0 แทนครับ แล้วค่อยมาซ่อนเลข 0 ด้วยการกำหนด Format ทีหลัง

ยกตัวอย่างเช่นที่ B1 คีย์สูตร

=IF(A1="","",A1) เราควรเปลี่ยนเป็น

=IF(A1="",0,A1) แทน เป็นต้น

และเมื่อต้องการแสดงค่า 0 ให้เห็นเป็นค่าว่าง (่ซ่อนเลข 0) ให้ทำดังนี้ครับ

1. คลิกขวาลงในพื้นที่เป้าหมายแล้ว
2. Format Cells...
3. แถบ Number เลือก Custom
4. ช่อง Type: คีย์ #,##0.00;-#,##0.00;

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

ให้ผมเปลี่ยนชื่อไฟล์ แล้วให้เป็นประโยชน์ต่อผู้อื่นว่าอะไรดีครับ..
เอาเป็น การใช้ VLOOKUP ใน Report ดีมั้ยครับ
แล้วจะ Update ข้อมูลที่ต้องการในการตอบปัญหาให้ครับ
ขอบคุณครับ

เรียน คุณคนควน
รบกวนดูปัญหาที่เอกสารแนบครับ
1. sheet Daily
2. sheet Monthly
ขอบคุณมากครับ
ขอแสดงความนับถือ
ผดุงเกียรติ เมฆอรุณ

ปัญหาคือชื่อช่วงเซลล์ที่ชื่อ UsedF1 ไม่ครอบคลุมเซลล์ที่เพิ่มเข้าไป

สามารถเปลี่ยนช่วงเซลล์โดยใช้สูตรมาช่วย เพื่อให้ UsedF1 เปลี่ยนแปลงเพิ่ม-ลดตามปริมาณข้อมูล โดย

1. เข้าเมนู Insert > Name > Define
2. เลือกชื่อ UsedF1
3. ที่ช่อง Refer to: เปลี่ยนเป็นสูตรตามด้านล่างครับ

=OFFSET(UsedF1!$A$2,0,0,COUNTA(UsedF1!$A$2:$A$65536),COUNTA(UsedF1!$B$2:$IV$2))

เรียน คุณคนควน
ทำตามคำแนะนำแล้วครับ sheet Daily และ sheet Monthly สามารถโชว์ Data ของปี 2011 ได้แล้วและโชว์ Data ได้ถูกต้องเป็นวันๆไปด้วยครับ
ขอบคุณมากครับ
รบกวนถามเพิ่มเติมครับ
1. สูตร =OFFSET(UsedF1!$A$2,0,0,COUNTA(UsedF1!$A$2:$A$65536),COUNTA(UsedF1!$B$2:$IV$2)) มีหลักเกณฑ์การใช้ยังไงครับ ช่วยอธิบายด้วยครับ
ขอแสดงความนับถือ
ผดุงเกียรติ เมฆอรุณ
ปล. ผมได้แนบ File ที่แก้ไขแล้วมาด้วยครับ

ฟังก์ชั่น Offset ดังกล่าวเป็นการกำหนดช่วงข้อมูลให้เพิ่มลดตามปริมาณข้อมูลที่มีครับ

จากสูตร =OFFSET(UsedF1!$A$2,0,0,COUNTA(UsedF1!$A$2:$A$65536),COUNTA(UsedF1!$B$2:$IV$2))

ความหมายแปลจากซ้ายไปขวาตามด้านล่างครับ

1. เริ่มจาก UsedF1!$A$2
2. ถัดลงไปจากข้อ 1 จำนวน 0 บรรทัด (คืออยู่ที่เดิม)
3. ถัดไปด้านขวาข้อ 1 จำนวน 0 คอลัมน์ (คืออยู่ที่เดิม)
4. ความสูงของข้อมูลเท่ากับผลลัพธ์ของ COUNTA(UsedF1!$A$2:$A$65536)
5. ความกว้างของข้อมูลเท่ากับผลลัพธ์ของ COUNTA(UsedF1!$B$2:$IV$2)

เรียน คุณคนควน
ขอบคุณมากครับ แล้วจะมารบกวนใหม่นะครับอาจารย์
ขอแสดงความนับถือ
ผดุงเกียรติ เมฆอรุณ

ยินดีครับ

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