Excel Forum

วันนี้ ขอรบกวน ผู้รู้ทุกท่านเลยนะคะ ช่วยบอกสูตรวิธีการนับข้อมูลที่ไม่ซ้ำกัน มีทั้งหมดเป็นจำนวนเท่าไหร่ แนบไฟล์ตัวอย่างมาด้วยค่ะ ต้องการนับแปลงที่ดินที่ไม่ซ้ำกัน โดยกรองข้อมูลเฉพาะสถานี S004 แล้วให้แสดงสจำนวนแปลงที่ดินที่ไม่ซ้ำกันในช่องที่ E36 ค่ะ ขอบคุณนะคะ
หมายเหตุ : ปอนด์ลองค้นหาใน Website ต่าง ๆ แล้วเจอ Website ของคุณ Santipong Nasui แล้วปรากฎว่า ภาพตัวอย่างมันไม่แสดง ก็เลยทำให้ไม่เข้าใจ สูตรที่เขียนบอกเอาไว้ ซึ่งสูตรเขียนไว้ ดังนี้ค่ะ


=SUM(IF(MATCH(IF(SUBTOTAL(3,OFFSET(B2,ROW(B2:B10)-ROW(B2),0)),A2:A10),IF(SUBTOTAL(3,OFFSET( B2,ROW( B2:B10)-ROW(B2),0)),A2:A10),0)=ROW(A2:A10)-ROW(A2)+1,SUBTOTAL(3,OFFSET(B2,ROW(B2:B10)-ROW(B2),0))))

Ctrl+Shift+Enter

ลองปรับสูตรเป็นตามด้านล่างครับ

=SUM(IF(MATCH(IF(SUBTOTAL(3,OFFSET(E10,ROW(E10:E31)-ROW(E10),0)),E10:E31),IF(SUBTOTAL(3,OFFSET(E10,ROW(E10:E31)-ROW(E10),0)),E10:E31),0)=ROW(E10:E31)-ROW(E10)+1,SUBTOTAL(3,OFFSET(E10,ROW(E10:E31)-ROW(E10),0))))

เนื่องจากสูตรนี้เป็นสูตร Array ดังนั้น การกดแป้นให้รับสูตรจะต้องกด 3 แป้น คือ Ctrl+Shift+Enter หากกดแป้นถูกต้องจะเห็นเครื่องหมายปีกกาครอบสูตร เช่น {=Formula(...)}

ขอบคุณมากนะคะ

snasui wrote: ลองปรับสูตรเป็นตามด้านล่างครับ

=SUM(IF(MATCH(IF(SUBTOTAL(3,OFFSET(E10,ROW(E10:E31)-ROW(E10),0)),E10:E31),IF(SUBTOTAL(3,OFFSET(E10,ROW(E10:E31)-ROW(E10),0)),E10:E31),0)=ROW(E10:E31)-ROW(E10)+1,SUBTOTAL(3,OFFSET(E10,ROW(E10:E31)-ROW(E10),0))))

เนื่องจากสูตรนี้เป็นสูตร Array ดังนั้น การกดแป้นให้รับสูตรจะต้องกด 3 แป้น คือ Ctrl+Shift+Enter หากกดแป้นถูกต้องจะเห็นเครื่องหมายปีกกาครอบสูตร เช่น {=Formula(...)}

********
ขอบคุณมากนะคะ ลองทำดูแล้ว ได้ผลลัพธ์ตามที่ต้องการแล้วค่ะ แต่มีข้อสงสัยอีกนิดหนึ่ง ค่ะ ถ้าหากว่า จำนวน Row เพิ่มขึ้น ปอนด์ก็ต้องเปลี่ยนช่วงข้อมูลตามไปด้วยใช่ไหมค่ะ เช่นมีข้อมูลเพิ่มขึ้ืนมาถึง Row35 ก็ต้องเปลี่ยนจาก E10:E31 ก็เปลี่ยนเป็น E10:E35 อย่างนี้ถูกต้องไหมค่ะ

ย่อมต้องเป็นเช่นนั้นครับ ช่วงข้อมูลในสูตรคือช่วงข้อมูลที่นำมาคำนวณครับ

หากไม่ต้องการเปลี่ยนช่วงข้อมูลอยู่เรื่อย ๆ สามารถลากสูตรเผื่อไว้เพียงเท่าที่ข้อมูลสามารถขยายไปถึง อีกทางเลือกคือใช้ Range Name ซึ่งต้องปรับสูตรใหม่หมด อีกทางเลือกลองใช้ Table เข้ามาช่วยครับ (Insert Table) เพื่อให้สูตรที่เกี่ยวข้องยืดหยุ่นตามปริมาณข้อมูลใน Table

ถ้าหากไม่เป็นการรบกวน เกินไป พอจะอธิบายความหมายของสูตรนี้ และแนวคิดให้หน่อยได้ไหมค่ะ ว่าหลักในการเขียนสูตรนี้คืออะไร ขอบคุณค่ะ

เป็นการรวมยอดตามเงื่อนไขครับ สูตรนี้เป็นสูตรซับซ้อน ทำความเข้าใจยากมาก ผมอธิบายหลัก ๆ ว่าสูตรทำงานอย่างไร ส่วนสูตรย่อย ๆ ขอให้ทำความเข้าใจตาม Link ด้านท้ายครับ

จากสูตร =SUM(IF(MATCH(IF(SUBTOTAL(3,OFFSET(E10,ROW(E10:E31)-ROW(E10),0)),E10:E31),IF(SUBTOTAL(3,OFFSET(E10,ROW(E10:E31)-ROW(E10),0)),E10:E31),0)=ROW(E10:E31)-ROW(E10)+1,SUBTOTAL(3,OFFSET(E10,ROW(E10:E31)-ROW(E10),0))))

เป็นการรวมยอดตามเงื่อนไข ถ้า MATCH(IF(SUBTOTAL(3,OFFSET(E10,ROW(E10:E31)-ROW(E10),0)),E10:E31),IF(SUBTOTAL(3,OFFSET(E10,ROW(E10:E31)-ROW(E10),0)),E10:E31),0)=ROW(E10:E31)-ROW(E10)+1 เป็นจริงแล้ว ให้นำผลลัพธ์ที่ได้จากสูตร SUBTOTAL(3,OFFSET(E10,ROW(E10:E31)-ROW(E10),0)) มาแสดง ถ้าไม่เป็นจริงก็ให้แสดงค่า False

จากสูตร MATCH(IF(SUBTOTAL(3,OFFSET(E10,ROW(E10:E31)-ROW(E10),0)),E10:E31),IF(SUBTOTAL(3,OFFSET(E10,ROW(E10:E31)-ROW(E10),0)),E10:E31),0) หมายถึงเป็นการหาว่าผลลัพธ์จากสูตร IF(SUBTOTAL(3,OFFSET(E10,ROW(E10:E31)-ROW(E10),0)),E10:E31) อยู่ในลำดับที่เท่าไรของผลลัพธ์จากสูตร IF(SUBTOTAL(3,OFFSET(E10,ROW(E10:E31)-ROW(E10),0)),E10:E31)

จากสูตร IF(SUBTOTAL(3,OFFSET(E10,ROW(E10:E31)-ROW(E10),0)),E10:E31) หมายถึง ถ้าผลลัพธ์จากสูตร SUBTOTAL(3,OFFSET(E10,ROW(E10:E31)-ROW(E10),0)) เป็นจริง (ไม่เป็น 0) ให้แสดงค่าในช่วงเซลล์ E10:E31 หากไม่เป็นจริงให้แสดงค่า False

ฟังก์ชั่น Offset ศึกษาได้จากที่นี่ http://snasui.blogspot.com/2009/03/offset.html
ฟังก์ชั่น Subtotal ศึกษาได้จากที่นี่ http://office.microsoft.com/th-th/excel ... aspx?CTT=1
การคำนวณของสูตร ROW(E10:E31)-ROW(E10)+1 ศึกษาได้จากที่นี่ viewtopic.php?p=17983#p17983

เข้ามามึนด้วยคน