Snasui Excel Forum

ไม่เข้าใจหลักการของการหาผลรวมโดยเงื่อนไขครับ
ตัวอย่างครับ มีชุดข้อมูลดังนี้
คอลัมน์ A = 1-10 แต่ละตัวเลขมีค่า (B) 100 เทียบกับ C ที่มีชุดข้อมูล 1-5

สอบถามดังนี้ครับ

1.เมื่อผมใช้ =(A1:A10)=(C1:C5) ค่าที่ได้มาจะเป็น True / False (Array)
- เมื่อผมเพิ่มสูตร =((A1:A10)=(C1:C5)*(B1:B10) ค่าได้ได้จะเป็น Array 100 / #N/A (Array)
- แต่พอผมเพิ่มสูตร SUM(.......) หรือ SUMPRODUCT(......) ค่าที่ได้กลับเป็น #N/A ไม่เป็นผลรวมของตัวเลข ครับ

2.เมื่อผมใช้ =SUMIF(A1:A10,C1:C5,B1:B10 ) ได้ Arrayที่มีค่า 100
- เมื่อผมใช้ SUM / SUMPRODUCT ครอบ จะได้ค่าที่ต้องการ

3.ผมต้องการ สูตรในช่อง D15:D21 ครับ ซึ่งน่าจะต้องใช้ SUMPRODUCT ที่ B1 เป็น Dropsownlist เป็นช่วงของวันที่ครับ
เพื่อเพิ่มความเข้าใจผมจึงลองใช้สูตรดังนี้
ที่ D15=SUMPRODUCT((A4:A11=C15)*(B4:AE11)) ได้ค่ามาเป็นตัวเลข เมื่อเอาผมรวมทั้งหมดในตาราง ที่มีเงื่อนไข = C15

แต่เมื่อผมลองเพื่อสูตร =SUMPRODUCT((A4:A11=C15)*(B4:AE11)*(วันที่ เทียบวันที่ )) ผลที่ได้คือ #N/A ครับ


(วันที่ เทียบวันที่ ) =ROW(INDIRECT(LEFT(B1,FIND("-",B1)-1)&":"&RIGHT(B1,LEN(B1)-(FIND("-",B1)))))
เพื่อหาช่วงของวันที่ แล้วมาเทียบกับ B3:AE3 ครับ
=SUMPRODUCT(...............*(ROW(INDIRECT(LEFT(B1,FIND("-",B1)-1)&":"&RIGHT(B1,LEN(B1)-(FIND("-",B1))))))*(B3:AE3))

ดังนั้นผมจึงไม่แน่ใจว่าทำไมค่าที่ได้เป็น #N/A ครับ

Xcelvba wrote: Mon Dec 26, 2022 10:38 am 1.เมื่อผมใช้ =(A1:A10)=(C1:C5)

เทียบกันเช่นนี้ไม่ได้ครับ

หลักการเทียบคือเอาค่าเดี่ยวไปเทียบกับช่วง หากเป็นช่วงเทียบกับช่วงจะต้องใช้ช่วงที่เท่ากัน ถ้าไม่เท่ากันจะต้องใช้สูตรเข้าไปช่วย เช่น =Match(A1:A10,C1:C5,0) จะได้ 10 รายการ (เท่ากับจำนวนของ A1:A10)

Xcelvba wrote: Mon Dec 26, 2022 10:38 am 2.เมื่อผมใช้ =SUMIF(A1:A10,C1:C5,B1:B10 ) ได้ Arrayที่มีค่า 100
- เมื่อผมใช้ SUM / SUMPRODUCT ครอบ จะได้ค่าที่ต้องการ

สูตรเช่นนี้ทำงานได้จึงไม่แปลกที่จะได้คำตอบครับ

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

=SUMPRODUCT(($A$4:$A$11=C15)*($B$4:$AE$11)*ISNUMBER(MATCH($B$3:$AE$3,ROW(INDIRECT(SUBSTITUTE(D$13,"-",":"))),0)))

Enter > Copy ลงด้านล่าง

snasui wrote: Mon Dec 26, 2022 10:55 am

Xcelvba wrote: Mon Dec 26, 2022 10:38 am 1.เมื่อผมใช้ =(A1:A10)=(C1:C5)

เทียบกันเช่นนี้ไม่ได้ครับ

หลักการเทียบคือเอาค่าเดี่ยวไปเทียบกับช่วง หากเป็นช่วงเทียบกับช่วงจะต้องใช้ช่วงที่เท่ากัน ถ้าไม่เท่ากันจะต้องใช้สูตรเข้าไปช่วย เช่น =Match(A1:A10,C1:C5,0) จะได้ 10 รายการ (เท่ากับจำนวนของ A1:A10)

Xcelvba wrote: Mon Dec 26, 2022 10:38 am 2.เมื่อผมใช้ =SUMIF(A1:A10,C1:C5,B1:B10 ) ได้ Arrayที่มีค่า 100
- เมื่อผมใช้ SUM / SUMPRODUCT ครอบ จะได้ค่าที่ต้องการ

สูตรเช่นนี้ทำงานได้จึงไม่แปลกที่จะได้คำตอบครับ

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

=SUMPRODUCT(($A$4:$A$11=C15)*($B$4:$AE$11)*ISNUMBER(MATCH($B$3:$AE$3,ROW(INDIRECT(SUBSTITUTE(D$13,"-",":"))),0)))

Enter > Copy ลงด้านล่าง

ขอบคุณครับ อ. สอบถามเพิ่มเติมครับ
เมื่อจะเอาค่าแต่ละ ROW เหตุใดจึงใช้สูตรนี้ไม่ได้ครับ =INDEX(A1:A10,MATCH(A1:A10,E1:E5,0)+Rows(H5:H5))
ทำไมจึงต้องใช้ Aggregate เข้ามาช่วยครับ =INDEX(A:A,AGGREGATE(15,6,ROW(A1:A10)/ISNUMBER(MATCH(A1:A10,E1:E5,0)),ROWS(I$5:I5)))

Xcelvba wrote: Mon Dec 26, 2022 12:04 pm AGGREGATE(15,6,ROW(A1:A10)/ISNUMBER(MATCH(A1:A10,E1:E5,0)),ROWS(I$5:I5))

หมายถึงให้หาค่าจากช่วง ROW(A1:A10)/ISNUMBER(MATCH(A1:A10,E1:E5,0)) ที่น้อยที่สุดในลำดับที่เป็นผลลัพธ์ของ ROWS(I$5:I5) และ Aggregate จะไม่นำค่า Error มาพิจารณา

ส่วน

Xcelvba wrote: Mon Dec 26, 2022 12:04 pm =INDEX(A1:A10,MATCH(A1:A10,E1:E5,0)+Rows(H5:H5))

ไม่ได้มีความหมายว่าให้นำค่าที่น้อยที่สุดในลำดับที่เท่านั้นเท่านี้มาแสดง แต่เป็นการนำค่าในลำดับที่เป็นผลลัพธ์ของ MATCH(A1:A10,E1:E5,0)+Rows(H5:H5) มาแสดงพร้อมกันในคราวเดียว สูตรแบบนี้ทำงานได้ใน O365 แต่จะมีค่าผิดพลาดออกมาด้วยเนื่องจากไม่ได้กันค่าผิดพลาดออกไปเหมือนกับ Aggregate

การที่สูตรใดสามารถให้ผลลัพธ์มาเป็นค่าใดจะทราบได้จากการศึกษาความหมายและคุณสมบัติของฟังก์ชันต่าง ๆ ที่ประกอบเข้ามาเป็นสูตรนั้น ๆ ครับ

snasui wrote: Mon Dec 26, 2022 12:31 pm

Xcelvba wrote: Mon Dec 26, 2022 12:04 pm AGGREGATE(15,6,ROW(A1:A10)/ISNUMBER(MATCH(A1:A10,E1:E5,0)),ROWS(I$5:I5))

หมายถึงให้หาค่าจากช่วง ROW(A1:A10)/ISNUMBER(MATCH(A1:A10,E1:E5,0)) ที่น้อยที่สุดในลำดับที่เป็นผลลัพธ์ของ ROWS(I$5:I5) และ Aggregate จะไม่นำค่า Error มาพิจารณา

ส่วน

Xcelvba wrote: Mon Dec 26, 2022 12:04 pm =INDEX(A1:A10,MATCH(A1:A10,E1:E5,0)+Rows(H5:H5))

ไม่ได้มีความหมายว่าให้นำค่าที่น้อยที่สุดในลำดับที่เท่านั้นเท่านี้มาแสดง แต่เป็นการนำค่าในลำดับที่เป็นผลลัพธ์ของ MATCH(A1:A10,E1:E5,0)+Rows(H5:H5) มาแสดงพร้อมกันในคราวเดียว สูตรแบบนี้ทำงานได้ใน O365 แต่จะมีค่าผิดพลาดออกมาด้วยเนื่องจากไม่ได้กันค่าผิดพลาดออกไปเหมือนกับ Aggregate

การที่สูตรใดสามารถให้ผลลัพธ์มาเป็นค่าใดจะทราบได้จากการศึกษาความหมายและคุณสมบัติของฟังก์ชันต่าง ๆ ที่ประกอบเข้ามาเป็นสูตรนั้น ๆ ครับ

ขอบคุณ อาจารย์มาก ครับ ได้ความรู้เพิ่มมากขึ้นนิดนึงแล้วครับ

สอบถามเพิ่มอีกนิดครับ อ. ในกรณี Excel version เก่า ไม่มีสูตร AGGREGATE จะสามารถทำได้อย่างไรได้บ้างให้ผลลัพธ์
เท่ากับ =INDEX(A:A,AGGREGATE(15,6,ROW(A1:A10)/ISNUMBER(MATCH(A1:A10,E1:E5,0)),ROWS(I$5:I5))) ครับ

ใช้ Index Small If ได้ครับ

ตัวอย่างดูได้ที่ Link นี้ครับ viewtopic.php?t=16382&hilit=index+small+if#p97629

search.php?keywords=index+small+if&term ... mit=Search

ขอบคุณครับจะนำไปศึกษาต่อครับ