Snasui Excel Forum

เรียนอาจารย์ทุกท่านครับ

ผมเจออาการ ปัญหาการเกิด #REF! ขึ้นมาเอง ต้องแก้ไขอย่างไรบ้างครับ
ซึ่งสูตรผมใส่ถูกต้องแล้วพอใช้ไปๆ ทั้งๆที่ Lock สูตรด้วยแล้ว ก็เกิดขึ้นมาอีกครับ ไฟล์นี้ใช้ร่วมกันใน DROPBOX โดยแชร์ให้สาขาใช้ จึงมีคนใช้งานร่วมกันประมาณ7-8 คน ครับ
ตามตัวอย่างไฟล์แนบ ชื่อตารางส่งของ เดือนกุมภาพันธุ์ วันที่ 11 บรรทัดที่ 70
สูตรที่ใช้เริ่ม
บรรทัดที่ 68 =IF(กุมภาพันธ์!$B$112=0,"",กุมภาพันธ์!$B$112)
บรรทัดที่ 69 =IF(กุมภาพันธ์!$B$113=0,"",กุมภาพันธ์!$B$113)
บรรทัดที่ 70 =IF(กุมภาพันธ์!$B$114=0,"",กุมภาพันธ์!$B$114) เปลี่ยนเป็น =IF(กุมภาพันธ์!#REF!=0,"",กุมภาพันธ์!#REF!) เปลี่ยนเอง
บรรทัดที่ 71 =IF(กุมภาพันธ์!$B$115=0,"",กุมภาพันธ์!$B$115)
บรรทัดที่ 72 =IF(กุมภาพันธ์!$B$116=0,"",กุมภาพันธ์!$B$116)
บรรทัดที่ 73 =IF(กุมภาพันธ์!$B$117=0,"",กุมภาพันธ์!$B$117)
บรรทัดที่ 74 =IF(กุมภาพันธ์!$B$118=0,"",กุมภาพันธ์!$B$118)
บรรทัดที่ 75 =IF(กุมภาพันธ์!$B$112=0,"",กุมภาพันธ์!$B$112)

และยังเกิดขึ้นกับอีกหลายเดือน โดยส่วนใหญ่จะเกิดการเปลี่ยนแปลงเองครับ
ผมจะเป็น Admin ในการ Key ข้อมูลลงไป และได้ทำการ Lock เซล์ Lock สูตร ทุกครั้งครับ
จึงมั่นใจได้ว่าไม่น่าจะมีรใครเข้ารหัสไปแก้ไขเองได้ครับ

ปล.ข้อมูลนี้มีคนเปิดใช้งานทั้งคอมพิวเตอร์ และมือถือครับ
รบกวนอาจารย์ทุกท่านด้วยครับ

#REF เกิดจากการที่มีคนลบ ข้อมูลต้นทางทิ้ง ในลักษณะDelete
(คลิ๊กขวาแล้ว Delete หรือเลือกเมนู Delete เท่านั้น หากกดปุ่ม Delete บนคีย์บอร์ดจะเป็นการ ClearContent ไม่ใช่ Delete)
ซึ่งการที่เรา Lock Sheet ไว้ไม่ได้ช่วยเสมอไปในกรณี เครื่อง User ปลายทางมีการ Default เครื่องที่ไม่เหมือนทาง Admin
(ซึ่งผมเจอกรณีนี้บ่อยๆ ซึ่งทำให้ Code Macro สั่งให้ปลดLock Sheet ไม่สามารทำงานได้ เพราะ Default เครื่องมันปลด Lockไปแล้ว)
อีกทั้งการนำไฟล์ไปเปิดในมือถือก็ทำให้เกิดปัญหามากมาย เพราะ App ในมือถือ อาจทำให้การทำงานของไฟล์เราผิดเพี้ยนได้ง่ายๆ
ลองเปลี่ยนไปใช้วิธี สร้าง Array ครอบพื้นที่ดูครับ อาจพอช่วยได้
คลุมพื้นที่ รอบๆตารางทีละด้านครับ
เช่น Database อยู่ที่ B4 ถึง C8
คลุม A3 ถึง A9 กด F2 พิมพ์ ="" กด Ctrl+Enter
ทำแบบนี้เช่นเดียวกันกับ B3:D2 D3:D9 และ B9:C9
ไม่แน่ใจว่าป้องกันได้ 100% หรือไม่ ยังไงลองฟังคำแนะนำจากท่านอื่นๆเพิ่มเติมดูครับ

การใช้ Indirect อาจจะช่วยแก้ไขปัญหานี้ได้ครับ
เพราะ Indirect ไม่สนใจ Relation ของ Cell
เรียกง่าย ๆ ว่า ต่อให้ Delete Cell (หรือแถว/คอลัมน์)
ตราบใดที่มันมองเห็นเซล Ref ตัวนั้นอยู่ มันจะเปลี่ยน
ไปอ้างอิงกับสิ่งที่เป็นปัจจุบันแทน

=IF(กุมภาพันธ์!$B$114=0,"",กุมภาพันธ์!$B$114)
เปลี่ยนเป็น
=IF(Indirect("'กุมภาพันธ์'!$B$114")=0,"",Indirect("'กุมภาพันธ์'!$B$114"))

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

ขอบคุณทุกท่านมากครับ จะลองนำไปใช้งานดูครับ