สูตร COUNTIFS ให้สั้นกระทัดรัดขึ้นหรือมีสูตรอื่นใช้แทน

[Totem]

สวัสดีครับ
วันนี้ปัญหาการใช้สูตร COUNTIFS ที่ทำให้ใช้สั้นกระทัดรัดลงได้หรือไม่ เนื่องจากข้อมูลมีมาก (ข้อมูลบางส่วน) ถ้าใส่สูตรตามตัวอย่างในช่องเซล G12 , G13 ถึง G17 ทำให้ประมวลผลช้าลงมากในแต่ละช่องเซล ซึ่งเกิดจาก การ +COUNTIFS..+COUNTIFS...+
อยากทราบว่ามีวิธีแก้ไขสูตร COUNTIFS ที่ใช้สั้นกระทัดรัดลง หรือมีสูตรอื่นทีใช้แทนได้ง่ายกว่าหรือไม่ครับ

ขอบคุณครับ

[joo]

ลองตามนี้ดูครับที่ G12 คีย์ =COUNTIFS($B$3:$B$8,$E12,$C$3:$C$8,$E4,$A$3:$A$8,G$11)Enter >Copy ไปทางขวาและลงล่างและ B1 กับ B2 ควรแยกอยู่กันคนละบรรทัดด้วยครับ

[Totem]

ยังไม่ใช่สิ่งที่ต้องการ ครับ เพราะ แผนก A อาจจะมีการจ่ายเงิน บัตรเครดิต a , b , e , f ได้ สูตรยังไม่่ครอบคลุม และ B1,B2 ต้องอยู่รวมกันครับ

ขอบคุณครับ คุณ joo

[nattasiray]

ผมแยกรหัสแผนกออกมาด้วยสูตร LEFT จากนั้นใช้ SUMPRODUCT เข้าช่วย

[Totem]

ข้อมูลช่องเซลที c2 ถึง c8 เกี่ยวข้องด้วยกับช่องเซล e4 - e9 ครับ อาจจะมีการจ่ายเงิน บัตรเครดิต a , b , e , f , g , h... ซึ่ง ตัวอย่าง แผนก A การจ่ายเงิน บัตรเครดิตอื่นที่ไม่ใช่ a , b , e , f อาจเป็น g , h ซึ่งไม่นับจำนวน เป็นต้น

ขอบคุณครับ คุณ nattasiray

[snasui]

ลองตามนี้ครับ

เซลล์ G12 คีย์

=SUMPRODUCT(--ISNUMBER(SEARCH($B$3:$B$8,$E12)),--($A$3:$A$8=G$11))

Enter > Copy ไปทางขวาและลงด้านล่าง

[Totem]

ขอบคุณครับอาจารย์ สูตรที่ให้มา ผมปรับสูตรนิดหน่อยได้ตามที่ต้องการแล้วครับ ดังนี้

=SUMPRODUCT(--ISNUMBER(SEARCH($B$3:$B$8,$E12)),--($A$3:$A$=G$11),--($C$3:$C$8=E$4)--($C$3:$C$8=E$5)--($C$3:$C$8=E$6)--($C$3:$C$8=E$7)--($C$3:$C$8=E$8)--($C$3:$C$8=E$9))

[snasui]

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

ที่ผิดคือตามที่ระบายสีไว้ครับ

=SUMPRODUCT(--ISNUMBER(SEARCH($B$3:$B$8,$E12)),--($A$3:$A$=G$11),--($C$3:$C$8=E$4)--($C$3:$C$8=E$5)--($C$3:$C$8=E$6)--($C$3:$C$8=E$7)--($C$3:$C$8=E$8)--($C$3:$C$8=E$9))

สูตรลักษณะนี้ที่ถูกต้องคือ ข้างหน้าเครื่องหมาย -- จะต้องมีเครื่องหมาย , เสมอ

หากปรับให้ถูกต้องแล้วแต่ไม่ได้คำตอบตามที่ต้องการ แสดงว่าเงื่อนไขที่ใส่เข้าไปในสูตรไม่ถูกต้องครับ

[Totem]

สูตรลักษณะนี้ที่ถูกต้องคือ ข้างหน้าเครื่องหมาย -- จะต้องมีเครื่องหมาย , เสมอ

ครับ ถ้าใส่เครื่องหมาย , ค่าที่ได้เป็น 0 ครับ จึงไม่ได้คำตอบที่ต้องการ ซึ่งในช่อง E$4 ถึง E$9 ต้องใช้ในเงื่อนไขด้วยครับ จะแก้ไขอย่างไรดีครับ

[snasui]

ช่วยเขียนเงื่อนไขมาทังหมดพร้อมแสดงคำตอบที่ต้องการมาในไฟล์แนบด้วย จะได้ทราบว่าเงื่อนไขคืออะไร ต้องการตอบเป็นเท่าใด จะได้เข้าใจตรงกันครับ

[Totem]

ช่วยเขียนเงื่อนไข มาทังหมดพร้อมแสดงคำตอบที่ต้องการมาในไฟล์แนบด้วย จะได้ทราบว่าเงื่อนไขคืออะไร ต้องการตอบเป็นเท่าใด จะได้เข้าใจตรงกันครับ

เงื่อนไข
ในส่วนของแบบฟอร์ม การจ่ายเงิน ต้องการการจ่ายเงินบัตรเครดิต เฉพาะ E4 ถึง E9 ส่วนของข้อมูล แผนก ช่อง B13 ถึง B15 อาจจะมีการจ่ายเงินบัตรเครดิตที่ไม่อยู่ให้เงือนไขแบบฟอร์ม เงื่อนไขต้องการให้ส่วนข้อมูลที่ระบายสีไว้ไม่ให้นับรวมมาลงในช่อง G12 คำตอบที่ต้องการเท่ากับ 1 ครับ
ดังเอกสารที่แนบเพิ่มเติม

ขอบคุณครับ

[snasui]

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

=SUMPRODUCT(--ISNUMBER(SEARCH($B$3:$B$15,$E12)),--($A$3:$A$15=G$11),--ISNUMBER(MATCH($C$3:$C$15,$E$4:$E$9,0)))

[Totem]

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

=SUMPRODUCT(--ISNUMBER(SEARCH($B$3:$B$15,$E12)),--($A$3:$A$15=G$11),--ISNUMBER(MATCH($C$3:$C$15,$E$4:$E$9,0)))

ทำได้ตามที่ต้องการแล้วครับ ขอบคุณครับอาจารย์

[Totem]

ขอสอบถามเพิ่มเติมครับ อาจารย์ ถ้าเกิดข้อมูลในช่อง $B$15 เป็นแผนก b1c และแบบฟอร์มในช่อง $E14 เป็นแผนก b1c หากเราใช้สูตร =SUMPRODUCT(--ISNUMBER(SEARCH($B$3:$B$15,$E14)),--($A$3:$A$15=G$11),--ISNUMBER(MATCH($C$3:$C$15,$E$4:$E$9,0))) คำตอบที่ได้จะเป็น 2 ที่ถูกต้องเป็น 1 จะปรับสูตรให้ได้ผลที่ถูกต้องได้หรือไม่ครับ
ขอบคุณครับ

[snasui]

ที่เครื่องผมผลลัพธ์ถูกต้องครับ

ช่วยแนบไฟล์ตัวอย่างที่เป็นปัญหามาด้วยจะได้เห็นว่ามีการกรอกข้อมูลถูกต้องตรงกันหรือไม่ อย่างไร จะได้ช่วยตรวจสอบได้ครับ

[Totem]

ที่เครื่องผมผลลัพธ์ถูกต้องครับ

ช่วยแนบไฟล์ตัวอย่างที่เป็นปัญหามาด้วยจะได้เห็นว่ามีการกรอกข้อมูลถูกต้องตรงกันหรือไม่ อย่างไร จะได้ช่วยตรวจสอบได้ครับ

เอกสารที่แนบเพิ่มเติม ครับ

[snasui]

อันนี้เป็นปัญหาที่ต้องเข้าใจก่อนว่าการใช้สูตร Sumproduct ตามที่ตอบไปเป็นการ Search

นั่นคือ หากรวม 3 แผนก คือ a, b1, b2 ลงในเซลล์เดียวกัน ค่าที่คีย์เช่นนี้ได้ผลลัพธ์เดียวกันกับ ab1b2 คือ แม้จะไม่คั่นด้วยเครื่องหมาย , ก็ให้ค่าได้เท่ากัน

การกรอก b1c จึงมีค่าเท่ากับ 2 แผนกคือแผนก b1 และ แผนก c

แต่หากในเซลล์ใด ๆ ในช่วง E12:E17 มีการกรอกแค่แผนกเดียวเท่านั้น สูตร Sumproduct จะต้องเปลี่ยนใหม่เป็น

=SUMPRODUCT(--($B$3:$B$15=$E12),--($A$3:$A$15=G$11),--ISNUMBER(MATCH($C$3:$C$15,$E$4:$E$9,0)))

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

[Totem]

อันนี้เป็นปัญหาที่ต้องเข้าใจก่อนว่าการใช้สูตร Sumproduct ตามที่ตอบไปเป็นการ Search

นั่นคือ หากรวม 3 แผนก คือ a, b1, b2 ลงในเซลล์เดียวกัน ค่าที่คีย์เช่นนี้ได้ผลลัพธ์เดียวกันกับ ab1b2 คือ แม้จะไม่คั่นด้วยเครื่องหมาย , ก็ให้ค่าได้เท่ากัน

การกรอก b1c จึงมีค่าเท่ากับ 2 แผนกคือแผนก b1 และ แผนก c

แต่หากในเซลล์ใด ๆ ในช่วง E12:E17 มีการกรอกแค่แผนกเดียวเท่านั้น สูตร Sumproduct จะต้องเปลี่ยนใหม่เป็น

=SUMPRODUCT(--($B$3:$B$15=$E12),--($A$3:$A$15=G$11),--ISNUMBER(MATCH($C$3:$C$15,$E$4:$E$9,0)))

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

ได้ผลตามที่ต้องการและเข้าใจชัดเจนครับ

ขอบคุณมากครับ