รบกวนช่วยตรวจสอบข้อผิดพลาดสูตรให้หน่อยครับ

[cartoonnet]

ในหน้าแถบ "ชำระ" นะครับ
คอลั่มที่ G ไม่แน่ใจว่าเขียนผิดตรงไหนครับ ผลลัพธ์ที่ได้มันถูกต้องครับ
แต่ผลตามมาคือทำให้ excel ช้าลง มากๆ
เวลาจะเพิ่มงานในหน้า "บันทึกงาน" บรรทัดใหม่
กว่าจะขึ้นบรรทัดเสร็จ กินเวลาไป 15 -30 วินาที (ปกติ 1 วิ)

ความต้องการของหน้า "ชำระ" คอลั่ม G นะครับ

1.ถ้าเป็นเงินผ่อน ให้ตรวจสอบว่า QM นี้ งวดนี้ ต้องจ่ายกี่บาท
2.ถ้าเป็นชำระ ปกติ ให้ตรวจสอบว่า คงเหลือยอดชำระกี่บาท
มีไว้สำหรับดูยอดเงินเฉยๆครับป้องกันการเก้บเงินพลาด
***ที่ขึ้น Value เพราะลบทิ้งไปหลายแถบครับมันเกิน 500kb
ขอบคุณมากครับครับ

[Xcelvba]

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

[cartoonnet]

ของผมมีราวๆ 13 แถบงานครับ
แต่ละแถบก็สูตรหนักๆทั้งนั้นครับแต่ ไม่มีหน่วงเครื่อง
เฉพาะ สูตรนี้สูตรเดียวครับ ถ้าเอาออก จากรอ 15 - 30 วิ เหลือ 1 วิครับ

ไม่แน่ใจไปอ้างอิงวงกลมหรือแบบไหน ทำไมมันดึงข้อมูลหนักมากครับ

ขอบคุณครับ

[snasui]

คอลั่มที่ G ไม่แน่ใจว่าเขียนผิดตรงไหนครับ ผลลัพธ์ที่ได้มันถูกต้องครับ
แต่ผลตามมาคือทำให้ excel ช้าลง มากๆ

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

=IFERROR(IFS([@สถานะ]="ผ่อน",XLOOKUP([@[เลข QM]]&[@งวดที่],ห้ามแก้ไข!D:D&ห้ามแก้ไข!E:E,ห้ามแก้ไข!J:J,"")-IF([@งวดที่]<>"",-SUM(FILTER([ชำระ],[เลข QM]&[งวดที่]=[@[เลข QM]]&[@งวดที่],0)),0),[@สถานะ]="ปกติ",XLOOKUP([@เลขที่บิล]&[@ตัวแทน],Table1[เลขที่บิล]&Table1[ตัวแทน],Table1[รวม],"")-SUM(FILTER([ชำระ],[ตัวแทน]&[เลขที่บิล]=[@ตัวแทน]&[@เลขที่บิล],

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

[cartoonnet]

คอลั่มที่ G ไม่แน่ใจว่าเขียนผิดตรงไหนครับ ผลลัพธ์ที่ได้มันถูกต้องครับ
แต่ผลตามมาคือทำให้ excel ช้าลง มากๆ

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

=IFERROR(IFS([@สถานะ]="ผ่อน",XLOOKUP([@[เลข QM]]&[@งวดที่],ห้ามแก้ไข!D:D&ห้ามแก้ไข!E:E,ห้ามแก้ไข!J:J,"")-IF([@งวดที่]<>"",-SUM(FILTER([ชำระ],[เลข QM]&[งวดที่]=[@[เลข QM]]&[@งวดที่],0)),0),[@สถานะ]="ปกติ",XLOOKUP([@เลขที่บิล]&[@ตัวแทน],Table1[เลขที่บิล]&Table1[ตัวแทน],Table1[รวม],"")-SUM(FILTER([ชำระ],[ตัวแทน]&[เลขที่บิล]=[@ตัวแทน]&[@เลขที่บิล],

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

ขอบคุณมากครับ
ลองแก้ไขแล้ว กลับมาเป็นปกติแล้วครับบบบบ

[cartoonnet]

รบกวนสอบถามเพิ่มเติมครับ
จากที่ผมใช้ D:D เป็น D2:D200
E:E เป็น E2:E200
J:J เป็น J2:J200

เราพอมีวิธีตั้งค่าขอบเขต เช่น จาก J2:(ข้อความแถวสุดท้าย)
จะต้องเขียนประมาณไหนครับ เผื่ออนาคตข้อมูลมันเพิ่มขึ้นจะได้ไม่ต้องมาแก้สูตรขยายขอบเขต ครับ

ขอบคุณครับ

[snasui]

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

=IFERROR(IFS([@สถานะ]="ผ่อน",XLOOKUP([@[เลข QM]]&[@งวดที่],OFFSET(Table13[งวดที่],0,-1)&Table13[งวดที่],Table13[ยอดชำระ],"")-IF([@งวดที่]<>"",-SUM(FILTER([ชำระ],[เลข QM]&[งวดที่]=[@[เลข QM]]&[@งวดที่],0)),0),[@สถานะ]="ปกติ",XLOOKUP([@เลขที่บิล]&[@ตัวแทน],Table1[เลขที่บิล]&Table1[ตัวแทน],Table1[รวม],"")-SUM(FILTER([ชำระ],[ตัวแทน]&[เลขที่บิล]=[@ตัวแทน]&[@เลขที่บิล],0))),0)

สังเกตการระบายสีในสูตร ที่ต้องเขียนอ้างอิงเช่นนั้นเนื่องจากคอลัมน์ D ของชีต ห้ามแก้ไข ไม่รวมอยู่ใน Table13

หากต้องการรวมคอลัมน์ D เข้าไปใน Table ด้วยสามารถปรับปรุงดังนี้

1. ที่ชีต ห้ามแก้ไขเซลล์ D2 ปรับสูตรเป็น
   =IFERROR(INDEX(TEXTSPLIT(TEXTJOIN("",TRUE,REPT(A$2#&",",B$2#)),,","),ROWS(D$2:D2)),"")
   Enter > Copy ลงด้านล่าง
2. ปรับขอบเขนตอง Table ให้รวมคอลัมน์ D เข้าไปด้วย
3. ที่ชีต 1.ชำระ เซลล์ G2 ปรับสูตรเป็น
   =IFERROR(IFS([@สถานะ]="ผ่อน",XLOOKUP([@[เลข QM]]&[@งวดที่],Table13[[เลข QM ]]&Table13[งวดที่],Table13[ยอดชำระ],"")-IF([@งวดที่]<>"",-SUM(FILTER([ชำระ],[เลข QM]&[งวดที่]=[@[เลข QM]]&[@งวดที่],0)),0),[@สถานะ]="ปกติ",XLOOKUP([@เลขที่บิล]&[@ตัวแทน],Table1[เลขที่บิล]&Table1[ตัวแทน],Table1[รวม],"")-SUM(FILTER([ชำระ],[ตัวแทน]&[เลขที่บิล]=[@ตัวแทน]&[@เลขที่บิล],0))),0)
   Enter

[cartoonnet]

ขอบพระคุณอย่างสูงเลยครับ