Snasui Excel Forum

ที่ชีต GS บรรทัดที่ 73 เป็นนักเรียนห้อง 1 ที่เพิ่งย้ายเข้ามา
เลยได้อยู่บรรทัดสุดท้าย แต่พอบันทึก ข ป ล น ที่ชีต R1
ของเลขที่ 17 บรรทัดที่ 19 ข้อมูลที่บันทึก ข ป ล น ไม่แสดงที่ชีต GR
แต่กลับเอาข้อมูลของเลขที่ 1 ของ R1 มาแสดงแทน
ต้องปรับแก้สูตรอย่างไร ตามไฟล์แนบครับ
ปล. หากจะยึดเอาเลขประจำตัวเลขห้องและเลขที่ ของชีต R1 ถึง R4 มาจับกับเลขประจำตัวเลขห้องและเลขที่ในคอลัมน์ B ในชีต GS
เพื่อให้แสดงข้อมูลที่บันทึก ข ป ล น ของแต่ละคนที่ IN:LO ของชีต GS จะเป็นไปได้ไหมครับ

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

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

โดยมีการแทรก IN:IO คอลัมน์ช่วยขึ้นมา 2 คอลัมน์เพื่อให้เขียนสูตรเดียวแล้วใช้ได้ทั้งตาราง ตามด้านล่างครับ

1. ที่ IN1 คีย์ Header
2. ที่ IO1 คีย์ Row
3. ที่ IN2 เป็นคอลัมน์ช่วย คีย์
   ="'R"&D2&"'!N1:CO1"
   Enter > Copy ลงด้านล่าง
4. ที่ IO2 เป็นคอลัมน์ช่วย คีย์
   ="'R"&D2&"'!N"&E2+2&":CO"&E2+2
   Enter > Copy ลงด้านล่าง
5. ที่ IP2 คีย์
   =IFERROR(INDEX(TEXT(INDIRECT($IN2),"\""dd/mm/yyyy\""=")&INDIRECT($IO2),AGGREGATE(15,6,COLUMN($N$3:$CO$3)/(INDIRECT($IO2)>99)-COLUMN($N3)+1,COLUMNS($N3:N3))),"")
   Enter > Copy ไปด้านขวาและลงด้านล่าง

โดยปกติ หากบันทึกวันที่ไม่เข้าเรียนของนักเรียนแต่ละห้อง (ชีต R1 คือ ห้อง 1, R2 คือ ห้อง 2, R3 คือ ห้อง 3 และ R4 คือ ห้อง 4) ด้วยการคีย์ ตัวอักษร ข (ขาด), ป(ป่วย), ล (ลากิจ), หรือ น(หนีเรียน) ลงในวันที่ใด ๆ ของนักเรียนคนใด ในชีต R1 ถึง R4 ข้อมูลที่คีย์ จะไปแสดงที่ชีต GS ปัญหาเจอตอนนี้คือ หากมีนักเรียนย้ายเข้ามาใหม่ ไม่ว่าจะย้ายเข้ามาอยู่ห้องใด (R1 ถึง R4) ชื่อนักเรียนคนดังกล่าวอยู่เลขที่สุดท้ายและที่บรรทัดสุดท้ายของห้องนั้น ๆ หากมีการคีย์วันที่ไม่เข้าเรียนของนักเรียนที่ย้ายเข้ามาใหม่ โดยการใส่ ข, ล, ป, หรือ น ลงในชีตของห้อง (R1-R4) ของนักเรียนที่ย้ายเข้า ข้อมูลที่คีย์จะไม่แสดงที่ชีต GS แต่กลับจะแสดงข้อมูลของเลขที่ 1 ของห้องนั้น ๆ แทน "ทำอย่างไรให้จะให้ข้อมูลที่คีย์ของนักเรียนที่ย้ายเข้าใหม่นั้น ไปแสดงที่ชีต GS" ตามไฟล์แนบ ครับ

ไม่พบไฟล์แนบล่าสุดครับ

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

หากจะใช้ไฟล์เดิมชี้ให้เห็นว่าเซลล์ที่ผิดพลาดจะต้องได้ค่าเท่าใด ด้วยเงื่อนไขใดบ้าง

ค่อย ๆ ถามตอบกันไปครับ

snasui wrote: Sun Sep 07, 2025 8:11 am ยังไม่ค่อยเข้าใจสิ่งที่อธิบาย แต่จากการดูสูตรในชีต GS พบว่าเป็นสูตรที่จะมีปัญหาเมื่อมีการแทรกหรือลบบรรทัด เพราะเป็นการเขียนให้ทำงานเฉพาะช่วงของชีตแบบ Fixed ค่า

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

โดยมีการแทรก IN:IO คอลัมน์ช่วยขึ้นมา 2 คอลัมน์เพื่อให้เขียนสูตรเดียวแล้วใช้ได้ทั้งตาราง ตามด้านล่างครับ

1. ที่ IN1 คีย์ Header
2. ที่ IO1 คีย์ Row
3. ที่ IN2 เป็นคอลัมน์ช่วย คีย์
   ="'R"&D2&"'!N1:CO1"
   Enter > Copy ลงด้านล่าง
4. ที่ IO2 เป็นคอลัมน์ช่วย คีย์
   ="'R"&D2&"'!N"&E2+2&":CO"&E2+2
   Enter > Copy ลงด้านล่าง
5. ที่ IP2 คีย์
   =IFERROR(INDEX(TEXT(INDIRECT($IN2),"\""dd/mm/yyyy\""=")&INDIRECT($IO2),AGGREGATE(15,6,COLUMN($N$3:$CO$3)/(INDIRECT($IO2)>99)-COLUMN($N3)+1,COLUMNS($N3:N3))),"")
   Enter > Copy ไปด้านขวาและลงด้านล่าง

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

ajsudkedt wrote: Sun Sep 07, 2025 11:41 am หลังใส่สูตรทำให้ไฟล์ใหญ่ขึ้นมาก

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

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

snasui wrote: Sun Sep 07, 2025 12:29 pm

ajsudkedt wrote: Sun Sep 07, 2025 11:41 am หลังใส่สูตรทำให้ไฟล์ใหญ่ขึ้นมาก

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

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

รับทราบครับผม ขอบคุณครับ
สูตรนี้สามารถใช้กับ excel ได้ทุก version ไหมครับ

ajsudkedt wrote: Sun Sep 07, 2025 12:44 pm ใช้กับ excel ได้ทุก version ไหมครับ

เนื่องจากมีฟังก์ชัน Aggregate จึงใช้ได้ตั้งแต่ Version 2013 เป็นต้นไปครับ

snasui wrote: Sun Sep 07, 2025 1:32 pm

ajsudkedt wrote: Sun Sep 07, 2025 12:44 pm ใช้กับ excel ได้ทุก version ไหมครับ

เนื่องจากมีฟังก์ชัน Aggregate จึงใช้ได้ตั้งแต่ Version 2013 เป็นต้นไปครับ

ครับผม ขอบคุณครับ