Excel: 'Sumifs' ละเว้น # n / a

Aug 27 2020

ฉันพยายามรวมค่าบางค่า (ทั้งบวกและลบ) ในคอลัมน์ แต่จะไม่รวมเนื่องจากมีค่า # n / a มีวิธีใดบ้างที่จะหลีกเลี่ยงสิ่งนี้? โปรดดูตัวอย่างด้านล่าง:

แต่ละเมืองมีพื้นที่ที่มีรายได้อาชญากรรมและการว่างงานต่ำกว่า (ไม่ว่าจะเป็นบวกหรือลบ) เป้าหมายของฉันคือการได้รับรายได้ทั่วเมืองอาชญากรรมและคะแนนการว่างงานโดยการรวมคะแนนของพื้นที่ที่ต่ำกว่า ฉันใช้ 'sumifs' ใน G2 เช่นเดียวกับใน = SUMIFS (C$2:C$10,$A$2:$A$10, $ A2) แล้วลากไปที่ I10 ในตัวอย่างของเล่นนี้มีเพียง 10 แถว แต่ข้อมูลของฉันมี 1 ล้านแถวดังนั้นฉันจึงลากไม่ได้จริงๆ ข้อเสนอแนะใด ๆ เกี่ยวกับเรื่องนี้ก็จะเป็นประโยชน์เช่นกัน!

แต่ที่สำคัญที่สุดคือปัญหาของฉันคือฉันไม่สามารถใช้ 'sumifs' ได้เนื่องจากค่า # N / A ฉันต้องการที่จะเพิกเฉยต่อพวกเขา

หรือเพียงแค่แทนที่ค่า # N / A ทั้งหมดด้วย 0 ใน 1 ล้านแถวก็เป็นตัวเลือกเช่นกัน

ps ฉันได้ทำการค้นคว้าเกี่ยวกับคำถามที่คล้ายกันก่อนหน้านี้ แต่ดูเหมือนว่าจะใช้สูตร 'sumifs' ที่แตกต่างกัน ...

คำตอบ

1 ed2 Aug 27 2020 at 00:28

การใช้ SUMIFS กับ <0 AND> 0

คุณสามารถใช้สิ่งนี้:

=SUMIFS(C$2:C$10,$A$2:$A$10,$A2,C$2:C$10,"<0")+SUMIFS(C$2:C$10,$A$2:$A$10,$A2,C$2:C$10,">0")

หลีกเลี่ยงศูนย์ (ซึ่งไม่มีความแตกต่างในกรณีนี้) นอกจากนี้ยังหลีกเลี่ยงเซลล์ที่มีค่าที่ไม่ใช่ตัวเลขเช่น NA ซึ่งมักจะป้องกันการสรุปผล (การหลีกเลี่ยงสิ่งเหล่านี้จะช่วยแก้ปัญหาของคุณได้ในกรณีนี้)

ถ้าฉันเข้าใจถูกต้องคุณจะต้องได้รับผลรวมของทั้งจำนวนบวกและจำนวนลบ (เช่นค่าที่มีอยู่ไม่ใช่ค่าสัมบูรณ์) อย่างไรก็ตามในขณะนี้ถูกป้องกันโดยใช้แนวทางที่คุณมีอยู่โดยการมีอยู่ของรายการ NA

หากไม่ถูกต้องโปรดแนะนำ

ใช้การค้นหาระหว่างกาลจากนั้นสรุปด้วย <0 และ> 0

คุณสามารถแทรกคอลัมน์การค้นหาซึ่งจะเชื่อมต่อค่าที่จะประเมินจากนั้นใช้ SUMIF

ตัวอย่างเช่นคอลัมน์ใหม่ J:

=$A2&C2

(สังเกต $ สำหรับ A และการไม่มี $ สำหรับ C)

จากนั้นเติม J1 ทางขวาไปยัง K1 และ L1 จากนั้นใน M1:

=SUMIF(J$2:J$10,"<0",C$2:C$10)+SUMIF(J$2:J$10,">0",C$2:C$10)

จากนั้นเติมทางขวาให้กับ N1 และ O1

ปัญหาที่อาจเกิดขึ้นกับวิธีนี้คือหากคุณกรอกข้อมูลหลายล้านแถวสเปรดชีตอาจช้าลงอย่างมาก

การเปลี่ยน NA

การแทนที่ NA ด้วย 0 จะได้ผลเช่นกัน แต่คุณอาจไม่ต้องการสูญเสียความแตกต่างระหว่าง "0" และ "ไม่แสดงในแหล่งข้อมูล"

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

มักเป็นที่นิยมเมื่อค้นหาข้อมูลเพื่อดักจับความเป็นไปได้ของ NA และส่งคืนผลลัพธ์อื่น ๆ ที่มีความหมายมากกว่า (หรือในกรณีนี้คือผลรวมที่เป็นมิตรมากกว่า)

BryanP Jan 19 2021 at 02:16

บางทีวิธีที่ง่ายที่สุดคือการใช้"<>#N/A"เกณฑ์ซึ่งจะละเว้น NA ในคอลัมน์ผลรวม คุณสามารถเพิ่มเกณฑ์เพิ่มเติมสำหรับคอลัมน์เงื่อนไขอื่น ๆ ได้หากมีการรบกวนการตรวจสอบเงื่อนไขที่เป็นตัวเลข=SUMIFS(C$2:C$10,$A$2:$A$10,$A2, C$2:C$10, "<>#N/A")