Page 1 of 1
Array และ aggregate ไม่สามารถแสดงค่าได้
Posted: Tue Mar 23, 2021 9:00 pm
by Supachok
เรียน ท่านอาจารย์ และผู้รู้
ปัญหาคือใช้ Array และ aggregate ไม่สามารถแสดงค่าได้
ต้องการแก้โจทย์
1. นับค่าซ้ำใน col A
2. ค่า SCCTGATH และ SCCTHRTH ต้องแสดงอย่างน้อย 1 ค่า ในค่าซ้ำ col A
คำตอบที่ต้องการ col : O
Re: Array และ aggregate ไม่สามารถแสดงค่าได้
Posted: Tue Mar 23, 2021 9:25 pm
by logic
คือแบบนี้หรือเปล่าครับ
M15 =SUMPRODUCT(--($A$15:$A$27=A15),--($J$15:$J$27=$J$16)) แต่ก็งงที่เอาชุดหลังไปเทียบกับ J16 คำตอบของเซลล์นี้ได้เท่าใดครับ
Re: Array และ aggregate ไม่สามารถแสดงค่าได้
Posted: Tue Mar 23, 2021 10:45 pm
by Supachok
ไม่ตรงครับ
คำตอบที่ต้อง ตย. อยู่ที col O.
อธิบายแบบนี้ครับ col : L
เป็นการนับ Col : A ว่ามีจำนวนซ้ำเท่าไร หลังจากนั้นหาว่าจำนวนที่ซ้ำมี
SCCTGATH และ SCCTGRTH ผสมอยู่ด้วยหรือไม่
หากมีเพียงค่าใดค่าหนึ่งไม่นับ = 0 ตย ใน Row 17-18
Re: Array และ aggregate ไม่สามารถแสดงค่าได้
Posted: Tue Mar 23, 2021 10:50 pm
by snasui
เซลล์ O16 มีค่าเป็น 1 ช่วยแจกแจกวิธีคำนวณว่ามีวิธีคำนวณอย่างไร จะได้เข้าใจตรงกันครับ
Re: Array และ aggregate ไม่สามารถแสดงค่าได้
Posted: Tue Mar 23, 2021 10:54 pm
by Supachok
ที่ผมสงสัยอย่างมาก มากกว่าคำตอบ
ผมใช้สูตร Array และ aggregate ผิดอย่างไร
เพราะผมตรวจสูตรที่ผม input ลงไปนั้นได้ค่าในขณะคำนวนตามต้องการแต่ output ที่แสดงเป็น #value
คือ ตั้งแต่ทำ Excel มาไม่เคยเจอแบบนี้มาก่อน
ท่านใดบอกสาเหตุได้ช่วยอธิบาย จุดบกพร่อง
Re: Array และ aggregate ไม่สามารถแสดงค่าได้
Posted: Tue Mar 23, 2021 10:57 pm
by 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
Re: Array และ aggregate ไม่สามารถแสดงค่าได้
Posted: Tue Mar 23, 2021 11:27 pm
by 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 นั่นคือประเมินเป็นค่าคงที่ไม่ใช่ช่วงเซลล์ตามนิยามของฟังก์ชั่นครับ
Re: Array และ aggregate ไม่สามารถแสดงค่าได้
Posted: Wed Mar 24, 2021 12:00 am
by 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
Re: Array และ aggregate ไม่สามารถแสดงค่าได้
Posted: Wed Mar 24, 2021 12:20 am
by Supachok
norkaz wrote: Wed Mar 24, 2021 12:00 am
ลองแบบนี้ดู
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
ขอบคุณครับง่ายๆ แต่นึกไม่ออกเลย
Re: Array และ aggregate ไม่สามารถแสดงค่าได้
Posted: Wed Mar 24, 2021 12:33 am
by Supachok
snasui wrote: Tue Mar 23, 2021 11:27 pm
โดยสรุป สูตรที่เขียนมานั้นไม่ถูกต้องครับ
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 ได้สูตรต้องเขียนอย่างไร
ช่วยยกตัวอย่าง แก้สูตร
Re: Array และ aggregate ไม่สามารถแสดงค่าได้
Posted: Wed Mar 24, 2021 4:57 am
by 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 ก็ต้องใช้ให้ตรงกับข้อบังคับใช้หรือตามไวยากรณ์ครับ
Re: Array และ aggregate ไม่สามารถแสดงค่าได้
Posted: Wed Mar 24, 2021 10:47 am
by 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
Re: Array และ aggregate ไม่สามารถแสดงค่าได้
Posted: Wed Mar 24, 2021 12:16 pm
by Supachok
ขอบคุณทุกท่านมากครับ
จะลองค่อยๆทำความเข้าใจ ตอนนี้ยัง (งองู 2 ตัว)
แล้วจะอธิบายถามมาใหม่ครับ.
