รวมตัวเลขเฉพาะที่เป็นตัวเลข 3 คอลัมน์

[janyad]

อยากให้ คอลัมน์ P เป็นผลรวมเฉพาะที่เป็นตัวเลข ตั้งแต่คอลัมน์ D:N จำนวน 3 คอลัมน์

[snasui]

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

เซลล์ P7 คีย์

=IFERROR(SUM(INDEX($D7:$N7,MATCH(1,($D7:$N7>0)+0,0)):INDEX($D7:$N7,SMALL(IF($D7:$N7<>0,COLUMN($D7:$N7)-COLUMN($D7)+1),3))),0)

Ctrl+Shift+Enter > Copy ลงด้านล่าง

[janyad]

ขอบคุณค่ะอาจารย์รบกวนขอทราบความหมายด้วยค่ะ

[snasui]

ความหมายคือรวมเฉพาะ 3 ตัวแรกที่มีข้อมูลตามโจทย์ครับ

จากสูตร IFERROR(SUM(INDEX($D7:$N7,MATCH(1,($D7:$N7>0)+0,0)):INDEX($D7:$N7,SMALL(IF($D7:$N7<>0,COLUMN($D7:$N7)-COLUMN($D7)+1),3))),0)

หมายถึง หาก SUM(INDEX($D7:$N7,MATCH(1,($D7:$N7>0)+0,0)):INDEX($D7:$N7,SMALL(IF($D7:$N7<>0,COLUMN($D7:$N7)-COLUMN($D7)+1),3))) เกิดค่าผิดลาด ให้แสดงค่าเป็น 0

จากสูตร SUM(INDEX($D7:$N7,MATCH(1,($D7:$N7>0)+0,0)):INDEX($D7:$N7,SMALL(IF($D7:$N7<>0,COLUMN($D7:$N7)-COLUMN($D7)+1),3)))

หมายถึงให้รวมยอดจากผลลัพธ์ของสูตร INDEX($D7:$N7,MATCH(1,($D7:$N7>0)+0,0)):INDEX($D7:$N7,SMALL(IF($D7:$N7<>0,COLUMN($D7:$N7)-COLUMN($D7)+1),3))

จากสูตร INDEX($D7:$N7,MATCH(1,($D7:$N7>0)+0,0)):INDEX($D7:$N7,SMALL(IF($D7:$N7<>0,COLUMN($D7:$N7)-COLUMN($D7)+1),3))

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

โดย INDEX($D7:$N7,MATCH(1,($D7:$N7>0)+0,0)) เป็นการหาว่าค่าตัวเลขที่พบตัวแรกในช่วง $D7:$N7 คือค่าใด ให้นำตำแหน่งนั้นมาใช้

และ INDEX($D7:$N7,SMALL(IF($D7:$N7<>0,COLUMN($D7:$N7)-COLUMN($D7)+1),3)) เป็นการหาว่าค่าตัวเลขตัวที่ 3 เป็นตำแหน่งใด ให้นำตำแหน่งนั้นมาใช้

จากสูตร INDEX($D7:$N7,MATCH(1,($D7:$N7>0)+0,0))

หมายถึง จากช่วง $D7:$N7 ให้นำลำดับที่ที่เกิดจากผลลัพธ์ของ MATCH(1,($D7:$N7>0)+0,0) มาแสดง เช่น หาก MATCH(1,($D7:$N7>0)+0,0) ได้ผลลัพธ์เป็น 2 หมายถึงจาก $D7:$N7 ให้นำลำดับที่ 2 มาแสดง หรือนั่นคือ นำ E2 มาแสดง

จากสูตร MATCH(1,($D7:$N7>0)+0,0) เป็นการหาว่าเลข 1 อยู่ในลำดับที่เท่าใดของผลลัพธ์ ($D7:$N7>0)+0 ซึ่งเป็นการเทียบว่า D7:N7 มากกว่า 0 หรือไม่ หากใช่จะเป็น True หากไม่ใช่จะเป็น False เป็นการเทียบไปตัวต่อตัว คือ

D7>0,
E7>0,
...,
N7>0

ซึ่งจะได้ผลลัพธ์เป็นเช่น {True,False,True,....}+0

การบวกด้วย 0 เป็นการแปลง True ให้เป็น 1 และแปลง False ให้เป็น 0 ก็จะได้เป็นเช่น {1,0,1,...} เช่นนี้เป็นต้น

จากสูตร NDEX($D7:$N7,SMALL(IF($D7:$N7<>0,COLUMN($D7:$N7)-COLUMN($D7)+1),3))

หมายถึง จากช่วง $D7:$N7 ให้นำลำดับที่ที่เป็นผลลัพธ์ของ SMALL(IF($D7:$N7<>0,COLUMN($D7:$N7)-COLUMN($D7)+1),3) มาแสดง

จากสูตร SMALL(IF($D7:$N7<>0,COLUMN($D7:$N7)-COLUMN($D7)+1),3)

หมายถึง ให้หาค่าที่น้อยที่สุดเป็นลำดับที่ 3 จากผลลัพธ์ของ IF($D7:$N7<>0,COLUMN($D7:$N7)-COLUMN($D7)+1)

จากสูตร IF($D7:$N7<>0,COLUMN($D7:$N7)-COLUMN($D7)+1)

หมายถึง หาก $D7:$N7<>0 เป็นจริง ให้แสดงผลลัพธ์ของ COLUMN($D7:$N7)-COLUMN($D7)+1 หากไม่เป็นจริงให้แสดงค่า False

จากสูตร COLUMN($D7:$N7)-COLUMN($D7)+1 เป็นการแสดงค่าลำดับคอลัมน์ เพื่อให้เริ่มจากเลข 1 เสมอ โดย COLUMN($D7:$N7) คือหมายเลขคอลัมน์ตั้งแต่คอลัมน์ D:N ซึ่งจะให้ผลลัพธ์เป็น {4,5,6...} เป็นต้น จากนั้นนำมาหักด้วย Columns($D7) หรือนั่นคือ {4}

สูตรโดยรวมส่วนนี้จะกลายเป็น {4,5,6...}-{4}+1 และจะกลายเป็น {0,1,2...}+1 และจะกลายเป็น {1,2,3...} เช่นนี้เป็นต้น

สูตรเหล่านี้เข้าใจยากมาก การแปลสูตรให้แปลจากด้านนอกเข้าด้านใน ส่วนการแทนค่าให้แทนค่าจากด้านในออกด้านนอกครับ

[janyad]

ขอบคุณค่ะ แต่ถ้าคอลัมน์มีตัวเลขไม่ถึง 3 คอลัมน์ อาจมี1หรือ2 คอลัมน์ผลรวมของตัวเลขก็จะไม่มาไช่ไหมค่ะ แล้วจะแก้สูตรแบบไหนดีค่ะ

[snasui]

ปรับสูตรที่ P7 เป็นด้านล่างครับ

=IFERROR(SUM(INDEX($D7:$N7,MATCH(1,($D7:$N7>0)+0,0)):INDEX($D7:$N7,SMALL(IF($D7:$N7<>0,COLUMN($D7:$N7)-COLUMN($D7)+1),MIN(3,COUNTIF($D7:$N7,">0"))))),0)

Ctrl+Shift+Enter > Copy ลงด้านล่าง

[janyad]

ขอบคุณค่ะ จะพยายามศึกษาให้ชำนาญ เพื่อนนำไปใช้ต่อไปค่ะ