Array และ aggregate ไม่สามารถแสดงค่าได้

[Supachok]

เรียน ท่านอาจารย์ และผู้รู้

ปัญหาคือใช้ Array และ aggregate ไม่สามารถแสดงค่าได้
ต้องการแก้โจทย์
1. นับค่าซ้ำใน col A
2. ค่า SCCTGATH และ SCCTHRTH ต้องแสดงอย่างน้อย 1 ค่า ในค่าซ้ำ col A
คำตอบที่ต้องการ col : O

[logic]

คือแบบนี้หรือเปล่าครับ

M15 =SUMPRODUCT(--($A$15:$A$27=A15),--($J$15:$J$27=$J$16)) แต่ก็งงที่เอาชุดหลังไปเทียบกับ J16 คำตอบของเซลล์นี้ได้เท่าใดครับ

[Supachok]

ไม่ตรงครับ
คำตอบที่ต้อง ตย. อยู่ที col O.

อธิบายแบบนี้ครับ col : L
เป็นการนับ Col : A ว่ามีจำนวนซ้ำเท่าไร หลังจากนั้นหาว่าจำนวนที่ซ้ำมี
SCCTGATH และ SCCTGRTH ผสมอยู่ด้วยหรือไม่

หากมีเพียงค่าใดค่าหนึ่งไม่นับ = 0 ตย ใน Row 17-18

[snasui]

เซลล์ O16 มีค่าเป็น 1 ช่วยแจกแจกวิธีคำนวณว่ามีวิธีคำนวณอย่างไร จะได้เข้าใจตรงกันครับ

[Supachok]

ที่ผมสงสัยอย่างมาก มากกว่าคำตอบ

ผมใช้สูตร Array และ aggregate ผิดอย่างไร
เพราะผมตรวจสูตรที่ผม input ลงไปนั้นได้ค่าในขณะคำนวนตามต้องการแต่ output ที่แสดงเป็น #value
คือ ตั้งแต่ทำ Excel มาไม่เคยเจอแบบนี้มาก่อน

ท่านใดบอกสาเหตุได้ช่วยอธิบาย จุดบกพร่อง

[Supachok]

อธิบายตามรูปแนบ

col A นับจำนวนซ้ำ
Col J นับเงื่อนไขกำหนดให้ต้องนับเจอ 2 ค่า คือ SCCTGATH และ SCCTGRTH
หากมีเพียงค่าใด ค่าหนึ่ง ไม่นับ

ค่าที่กำหนดขึ้น 1 ใน col O
อ้างอิงตาม cell col D ที่กำหนดนับค่าซ้ำมาก่อนแล้ว

คือ Col D ขึ้น 1 และจำนวนซ้ำที่นับ มีค่าSCCTGATH และ SCCTGRTH ให้นับ 1
คือ Col D ขึ้น 1 และจำนวนซ้ำที่นับ มีค่าSCCTGATH หรือ SCCTGRTH ค่าใดค่าหนึ่งเท่านั้น ให้นับ 0 หรือไม่นับตามตย. row 17-18

[snasui]

โดยสรุป สูตรที่เขียนมานั้นไม่ถูกต้องครับ

Aggregate มี 2 ไวยากรณ์

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

Ref.png

จึงไม่สามารถหาผลรวมของผลคูณด้วยสูตรในรูปแบบนี้ได้

ส่วนไวยากรณ์อีกแบบคือตามภาพ ส่วนประกอบที่ 3 จะเป็น Array สามารถใช้ช่วงเซลล์หรือสูตรเป็นส่วนประกอบตรงส่วนนี้ได้

Arr.png

สองภาพนี้ได้มาจากการคลิกปุ่ม FX บน Formula bar

ในส่วนของ =COUNTIF(IF(A15=A15:A27,J15:J27),$J$16) ก็ไม่ถูกต้องเช่นกัน สูตรพวก Countif(s), Sumif(s) จะต้องใช้ Range ดูได้จากไวยากรณ์ของฟังก์ชั่น ถ้าใช้ลักษณะนี้จะไม่ได้ถูกประเมินเป็น Range แต่จะประเมินเป็นค่าในเซลล์จากช่วงเซลล์ที่เป็นผลลัพธ์ของ IF นั่นคือประเมินเป็นค่าคงที่ไม่ใช่ช่วงเซลล์ตามนิยามของฟังก์ชั่นครับ

[norkaz]

ลองแบบนี้ดู

D15
=A15&J15
Copy ลงล่าง

O15
=IF(AND(COUNTIF($A$15:$A$27,A15)<>COUNTIF($D$15:$D$27,D15)=TRUE,COUNTIF(D15:$D$27,D15)=1,COUNTIF(A15:$A$27,A15)=1),1,"")

Copy ลงล่าง

Norkaz

[Supachok]

ลองแบบนี้ดู

D15
=A15&J15
Copy ลงล่าง

O15
=IF(AND(COUNTIF($A$15:$A$27,A15)<>COUNTIF($D$15:$D$27,D15)=TRUE,COUNTIF(D15:$D$27,D15)=1,COUNTIF(A15:$A$27,A15)=1),1,"")

Copy ลงล่าง

Norkaz

ขอบคุณครับง่ายๆ แต่นึกไม่ออกเลย

[Supachok]

โดยสรุป สูตรที่เขียนมานั้นไม่ถูกต้องครับ

Aggregate มี 2 ไวยากรณ์

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

Ref.png

จึงไม่สามารถหาผลรวมของผลคูณด้วยสูตรในรูปแบบนี้ได้

ส่วนไวยากรณ์อีกแบบคือตามภาพ ส่วนประกอบที่ 3 จะเป็น Array สามารถใช้ช่วงเซลล์หรือสูตรเป็นส่วนประกอบตรงส่วนนี้ได้

Arr.png

สองภาพนี้ได้มาจากการคลิกปุ่ม FX บน Formula bar

ในส่วนของ =COUNTIF(IF(A15=A15:A27,J15:J27),$J$16) ก็ไม่ถูกต้องเช่นกัน สูตรพวก Countif(s), Sumif(s) จะต้องใช้ Range ดูได้จากไวยากรณ์ของฟังก์ชั่น ถ้าใช้ลักษณะนี้จะไม่ได้ถูกประเมินเป็น Range แต่จะประเมินเป็นค่าในเซลล์จากช่วงเซลล์ที่เป็นผลลัพธ์ของ IF นั่นคือประเมินเป็นค่าคงที่ไม่ใช่ช่วงเซลล์ตามนิยามของฟังก์ชั่นครับ

ตย1.
=AGGREGATE(15,6,{1,2,3}/(A3=Sheet2!$F$3:$H$13),1)
ตย2.
=IFERROR(INDEX(FactGL!P:P,AGGREGATE(15,6,ROW(FactGL!$P$2:$P$3000)/(FactGL!$N$2:$N$3000=P2)/(FactGL!$P$2:$P$3000<>""),1)),"")
ตย3.
=AGGREGATE(9,6,((A15:A27=A15)*(J15:J27=$J$16)))
INDEX(A:A,AGGREGATE(15,6,ROW($E$2:$E$99)/($D$2:$D$99=$E2),COUNTIF($E$2:$E2,$E2)))

สูตรของผม
=AGGREGATE(9,6,((A15:A27=A15)*(J15:J27=$J$16)))

เรียน ท่านอาจารย์
ในความเข้าใจ ก็ยากที่จะเข้าใจได้
ที่เข้าใจ คือ ต้องใช้ range ที่ไม่ได้มาจากการคำนวน แต่ตามตย. ผมยังงงอยู่มาตามโครงสร้างสูตร
1. มีการเทียบค่า = เหมือนกัน
2. มีการรวมค่าโดย เครื่องหมาย * หรือ / เช่นกัน

แล้วอะไรไม่เหมือนกัน ทำให้ผลลัพธ์ error และหากแก้สูตรให้ใช้ aggregate ได้สูตรต้องเขียนอย่างไร
ช่วยยกตัวอย่าง แก้สูตร

[snasui]

ตัวอย่าง 1, 2 และ 3 เป็นการใช้แบบไวยากรณ์ตามรูปที่ 2 ในโพสต์ #7 คือแบบ Array หรือช่วงเซลล์ก็ได้ สูตรลักษณะนี้จะบังคับให้ใส่ส่วนประกอบที่ 4 ของสูตรเสมอ ถ้าดูในสูตรที่ยกตัวอย่างมา ส่วนประกอบที่ 4 คือ 1, 1 และ COUNTIF($E$2:$E2,$E2) ตามลำดับ

ส่วนสูตร =AGGREGATE(9,6,((A15:A27=A15)*(J15:J27=$J$16))) ถ้าจะให้ได้คำตอบ จะต้องใช้ในลักษณะเช่นด้านล่าง

=AGGREGATE(9,6,A15:A27) หรือ
=AGGREGATE(9,6,A15:A27,B28,C5:C100,D2)

เช่นนี้เป็นต้น

หากคีย์เป็น

=AGGREGATE(9,6,1,A15:A27)

ก็จะเกิดค่าผิดพลาด เพราะ 1 ไม่ใช่ Reference หรือไม่ได้มาจากการอ้างอิงตำแหน่งเซลล์ตามข้อกำหนดของไวยากรณ์

ส่วนการใช้ Aggregate หรือสูตรอื่นใด จะใม่ให้ Error ก็ต้องใช้ให้ตรงกับข้อบังคับใช้หรือตามไวยากรณ์ครับ

[norkaz]

เรียน ท่าน Supachok

การอธิบายของ อาจารย์ คนควน นั้นถูกต้อง ชัดเจนมากครับ

ในที่นี้ผมเสนอว่า กรณีที่ไม่ใช่คอลัมน์ไปช่วยในโจทย์ที่สอบถามมา
อาจจะไม่จำเป็นต้องใช้ Array แบบ Aggregate หรือ Array แบบเต็มรูป ( Ctrl + Shift + Enter)

ลองแบบนี้ดูครับ

เพราะสูตร ลงท้ายด้วย IF หรือ IFs ทั้งหลาย ล้วนเป็น Array แบบธรรมชาติอยู่แล้ว พอช่วยได้ ในยามจำเป็น

-- เพียงแต่ มิติของ Array ในตะกูลลงท้ายด้วย IF หรือ IFs จำเป็นต้องเท่ากันซึ่ง ต่างจาก Array เต็มรูป หรือ Array ซ่อนรูปแบบ Aggregate ที่ การอ้างอิง Range ไม่จำเป็นต้องเท่ากัน

O15

=IF(AND(COUNTIF(A15:$A$27,A15)=1,COUNTIF($A$15:$A$27,A15)<>COUNTIFS($A$15:$A$27,A15,$J$15:$J$27,J15)),1,"")

Copy ลงล่าง

Norkaz

[Supachok]

ขอบคุณทุกท่านมากครับ

จะลองค่อยๆทำความเข้าใจ ตอนนี้ยัง (งองู 2 ตัว)
แล้วจะอธิบายถามมาใหม่ครับ.