ขอรบกวนค่ะ สูตรExcel ขึ้น #NUM ต้องแก้ไขแบบไหนค่ะ
Posted: Thu Jan 03, 2019 8:21 am
รบกวนสอบถามค่ะ
ในสูตรถ้าเพิ่มบรรทัดข้อมูล เช่น จากเดิม 7669 เป็น 10,000 ผลลัพธ์เป็น #NUM ต้องแก้ไขแบบไหนค่ะ
ขอบคุณพระคุณค่ะ
=IF((A14>COUNTIF(INDEX(Stock!$H$9:$BO$7669,,MATCH($G$6,Stock!$H$7:$BO$7,)),">0"))+(A14=""),"",INDEX(Stock!C:C,SMALL(IF(INDEX(Stock!$H$9:$BO$7669,,MATCH($G$6,Stock!$H$7:$BO$7,))>0,ROW(Stock!$B$9:$H$7669)),A14)))
=IF($B14="","",INDEX(INDEX(Stock!$A:$BO,,CHOOSE(COLUMNS($E14:E14),MATCH($G$6,Stock!$A$7:$BO$7,)-1,7,4,MATCH($G$6,Stock!$A$7:$BO$7,))),SMALL(IF(INDEX(Stock!$H$9:$BO$7669,,MATCH($G$6,Stock!$H$7:$BO$7,))>0,ROW(Stock!$B$9:$H$7669)),$A14)))
=IF($B14="","",INDEX(INDEX(Stock!$A:$BO,,CHOOSE(COLUMNS($E14:F14),MATCH($G$6,Stock!$A$7:$BO$7,)-1,7,4,MATCH($G$6,Stock!$A$7:$BO$7,))),SMALL(IF(INDEX(Stock!$H$9:$BO$7669,,MATCH($G$6,Stock!$H$7:$BO$7,))>0,ROW(Stock!$B$9:$H$7669)),$A14)))
=IF($B14="","",INDEX(INDEX(Stock!$A:$BO,,CHOOSE(COLUMNS($E14:G14),MATCH($G$6,Stock!$A$7:$BO$7,)-1,7,4,MATCH($G$6,Stock!$A$7:$BO$7,))),SMALL(IF(INDEX(Stock!$H$9:$BO$7669,,MATCH($G$6,Stock!$H$7:$BO$7,))>0,ROW(Stock!$B$9:$H$7669)),$A14)))
=IF($B14="","",INDEX(INDEX(Stock!$A:$BO,,CHOOSE(COLUMNS($E14:H14),MATCH($G$6,Stock!$A$7:$BO$7,)-1,7,4,MATCH($G$6,Stock!$A$7:$BO$7,))),SMALL(IF(INDEX(Stock!$H$9:$BO$7669,,MATCH($G$6,Stock!$H$7:$BO$7,))>0,ROW(Stock!$B$9:$H$7669)),$A14)))
ในสูตรถ้าเพิ่มบรรทัดข้อมูล เช่น จากเดิม 7669 เป็น 10,000 ผลลัพธ์เป็น #NUM ต้องแก้ไขแบบไหนค่ะ
ขอบคุณพระคุณค่ะ
=IF((A14>COUNTIF(INDEX(Stock!$H$9:$BO$7669,,MATCH($G$6,Stock!$H$7:$BO$7,)),">0"))+(A14=""),"",INDEX(Stock!C:C,SMALL(IF(INDEX(Stock!$H$9:$BO$7669,,MATCH($G$6,Stock!$H$7:$BO$7,))>0,ROW(Stock!$B$9:$H$7669)),A14)))
=IF($B14="","",INDEX(INDEX(Stock!$A:$BO,,CHOOSE(COLUMNS($E14:E14),MATCH($G$6,Stock!$A$7:$BO$7,)-1,7,4,MATCH($G$6,Stock!$A$7:$BO$7,))),SMALL(IF(INDEX(Stock!$H$9:$BO$7669,,MATCH($G$6,Stock!$H$7:$BO$7,))>0,ROW(Stock!$B$9:$H$7669)),$A14)))
=IF($B14="","",INDEX(INDEX(Stock!$A:$BO,,CHOOSE(COLUMNS($E14:F14),MATCH($G$6,Stock!$A$7:$BO$7,)-1,7,4,MATCH($G$6,Stock!$A$7:$BO$7,))),SMALL(IF(INDEX(Stock!$H$9:$BO$7669,,MATCH($G$6,Stock!$H$7:$BO$7,))>0,ROW(Stock!$B$9:$H$7669)),$A14)))
=IF($B14="","",INDEX(INDEX(Stock!$A:$BO,,CHOOSE(COLUMNS($E14:G14),MATCH($G$6,Stock!$A$7:$BO$7,)-1,7,4,MATCH($G$6,Stock!$A$7:$BO$7,))),SMALL(IF(INDEX(Stock!$H$9:$BO$7669,,MATCH($G$6,Stock!$H$7:$BO$7,))>0,ROW(Stock!$B$9:$H$7669)),$A14)))
=IF($B14="","",INDEX(INDEX(Stock!$A:$BO,,CHOOSE(COLUMNS($E14:H14),MATCH($G$6,Stock!$A$7:$BO$7,)-1,7,4,MATCH($G$6,Stock!$A$7:$BO$7,))),SMALL(IF(INDEX(Stock!$H$9:$BO$7669,,MATCH($G$6,Stock!$H$7:$BO$7,))>0,ROW(Stock!$B$9:$H$7669)),$A14)))
