รบกวนสอบถามเกี่ยวกับสูตร Sumifs ครับ

[niwat2811]

รบกวนสอบถามเกี่ยวกับสูตร Sumifs ครับ เนื่องจากได้ลองใช้สูตร Sumifs เพื่อคำนวณหาค่า
แต่ปรากฏว่าได้คำตอบเป็น #VALUE! ซึ่งไม่ทราบว่าสูตรผิดตรงไหน รบกวนท่านผู้รู้ช่วยดูให้ด้วยครับ
ตัวอย่างอยู่ในไฟล์แนบ ที่ Sheet1 ครับ

[bank9597]

ใช้ Sumifs กับโจทย์ลักษณะนั้นไม่ได้ครับ ลองดูตามนี้ครับ

ที่ C36 คีย์ =SUM(IF($E$1:$T$1=$B36,IF($B$2:$B$31=C$35,$E$2:$T$31))) กด ctrl+shift+enter แล้วคัดลอกไปทางขวามือ แล้วคัดลอกลงล่างพร้อมกัน

ศึกษาการใช้ Sumifs ที่ http://office.microsoft.com/th-th/excel ... 47504.aspx

[niwat2811]

ขอบคุณ คุณ bank9597 มากครับ สูตรที่ให้มาใช้ได้ตรงตามความต้องการเลยครับ

[snasui]

แถมตัวอย่างการใช้ Sumif ร่วมกับ Index และ Match ครับ

ที่เซลล์ C36 คีย์

=SUMIF($B$2:$B$31,C$35,INDEX($E$2:$T$31,0,MATCH($B36,$E$1:$T$1,0)))

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

[bank9597]

แถมตัวอย่างการใช้ Sumif ร่วมกับ Index และ Match ครับ

ที่เซลล์ C36 คีย์

=SUMIF($B$2:$B$31,C$35,INDEX($E$2:$T$31,0,MATCH($B36,$E$1:$T$1,0)))

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

ผมไม่จำสูตรแนวนี้สักที อิอิ

[norkaz]

ท่าน Bank ไม่ต้องกังวล สูตรนี้ไม่ง่ายครับ ...ลึกเอาเรื่อง
เป็นการหยิบเอา SUMIF ซึ่งเป็น Array ธรรมชาติที่ Excel มีอยู่แล้ว
มาฉายในแบบ ฝีมือล้วนๆ ผมก็ไม่เคยนึกถึงวิธีนี้มาก่อน และไม่เคยเห็นมาก่อนด้วย

กลแบบนี้ ไม่ง่ายเลย การที่มีเฉลย มีคนแสดงออกมา ดูเหมือนง่าย แท้จริงแล้วต้องอาศัยความเข้าใจอย่างลึกซึ้ง
ผมลองถอดกลดู ... ต้องยอมรับว่าจินตนาการท่าน คนควน สุดยอดเหลือเชื่อจริงๆ !!

คุณคนควนแสดงได้ คลาสสิคมาก


นับถือ
Norkaz

[norkaz]

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

การที่ คุณ คนควนใช้ INDEX+ MATCH ในชุดแรกไปแล้วนั้น จังหวะนั้นบังคับให้มันติด Array ไปชั้นหนึ่งก่อน
แล้วใช้ SUMIF ไปครอบอีกชั้น

คำถาม
1.SUMIF สามารถใช้ในลักษณะ ซ้อนกับ Array แบบนี้ได้ทุกกรณีใช่หรือไม่ครับ .ถ้าไ่ม่ผิดเคยเห็นคุณคนควนแสดงโดยใช้ COUNTIF ในรูปแบบคล้ายๆกัน

2.ทำไมจึงเลือกใช้ INDEX + MATCH ในลักษณะนั้นก่อน (เพื่อจะใช้ SUMIF มาครอบ )

หากไม่เป็นการรบกวน ขอขอบคุณมากครับ
นับถือ

Norkaz

[bank9597]

ท่าน Bank ไม่ต้องกังวล สูตรนี้ไม่ง่ายครับ ...ลึกเอาเรื่อง
เป็นการหยิบเอา SUMIF ซึ่งเป็น Array ธรรมชาติที่ Excel มีอยู่แล้ว
มาฉายในแบบ ฝีมือล้วนๆ ผมก็ไม่เคยนึกถึงวิธีนี้มาก่อน และไม่เคยเห็นมาก่อนด้วย

กลแบบนี้ ไม่ง่ายเลย การที่มีเฉลย มีคนแสดงออกมา ดูเหมือนง่าย แท้จริงแล้วต้องอาศัยความเข้าใจอย่างลึกซึ้ง
ผมลองถอดกลดู ... ต้องยอมรับว่าจินตนาการท่าน คนควน สุดยอดเหลือเชื่อจริงๆ !!

คุณคนควนแสดงได้ คลาสสิคมาก


นับถือ
Norkaz

เห็นด้วยครับ ยากจริงๆ ผมคงไม่คิดจะใช้แน่นอนครับ อิอิ

[snasui]

Sumif สามารถใช้กับ Index+Match ในลักษณะนี้ได้เสมอไปครับ

จากสูตรด้านบนฟังก์ชั่นหลักคือ Sumif ครับ ส่วน Index+Match นั้นเป็นการแสดง Range หรือ Array ชุดที่เป็นเป้าหมายที่ต้องการรวมค่าครับ

จากสูตร INDEX($E$2:$T$31,0,MATCH($B36,$E$1:$T$1,0)) เป็นการแสดงช่วงข้อมูลในแนวคอลัมน์โดยแสดงทั้งคอลัมน์ในช่วง $E$2:$T$31 (เนื่องจากในแนวบรรทัดเราใส่ 0 เป็นลักษณะการใช้ Index แบบประยุกต์) โดยคอลัมน์ในช่วง $E$2:$T$31 ที่จะนำมาใช้หาผลรวมคือลำดับคอลัมน์ที่เป็นผลลัพธ์ของสูตร MATCH($B36,$E$1:$T$1,0) ครับ

[norkaz]

คำอธิบาย ได้ช่วยยืนยัน ตามสิ่งที่คาดการและต้องการครับ

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

Norkaz

[niwat2811]

ขออนุญาตสอบถามเพิ่มเติมนะครับ เนื่องจากว่าได้เพิ่มเงื่อนไขขึ้นมาอีก 1 เงื่อนไข โดยเพื่มเงื่อนไขใน Column C ตามไฟล์แนบ
และได้ปรับสูตรของคุณ Bank9597 เพิ่มเติม ซึ่งไฟล์แนบก็แสดงผลได้ถูกต้อง
แต่ว่าเมื่อผมลองใช้กับไฟล์งานจริง ปรากฎว่าไม่สามารถทำงานได้
โดย Excel ขึ้นฟ้องว่า Excel มีทรัพยากรไม่เพียงพอ
ไฟล์งานจริง ข้อมูลดิบมี 280 คอลัมภน์และมี 30,000 แถว
ไม่ทราบว่าควรปรับสูตรอย่างไรดีครับ

[bank9597]

เมื่อผมลองใช้กับไฟล์งานจริง ปรากฎว่าไม่สามารถทำงานได้
โดย Excel ขึ้นฟ้องว่า Excel มีทรัพยากรไม่เพียงพอ
ไฟล์งานจริง ข้อมูลดิบมี 280 คอลัมภน์และมี 30,000 แถว

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

ไม่ว่าสูตรไหนก็ช้าทั้งนั้นครับ เพราะมีข้อมูล 280 คอลัมภน์ 30,000 แถว วิธีแก้ทางเดียวคือ ดึงข้อมูลของแต่ล่ะ product มาแสดงทีละ product หลีกเลี่ยงการลากสูตรแบบเดิม

และหากว่ายังช้าอีก ก็จำเป็นต้องใช้ VBA ช่วยในเรื่องนี้ครับ หรือไม่ก็เปลี่ยนแปลงรูปแบบตารางข้อมูลเป็นแบบ Database ครับ

[snasui]

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

=SUMIFS(INDEX($F$2:$U$31,0,MATCH($B36,$F$1:$U$1,0)),$C$2:$C$31,C$35,$B$2:$B$31,LOOKUP(CHAR(255),$C$34:C$34))

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

กรณีต้องการใช้สูตรกับเซลล์จำนวนมาก ควรหลีกเลี่ยงการใช้สูตร Array เนื่องจากสูตร Array ต้องใช้ Memory ในการจัดการมากกว่าสูตรปกติ

[nopanan]

ขอถามต่อครับอาจารย์ ถ้าหากว่าในบรรทัดที่ A1 ซึ่งเป็น m15-->m30 นั้น สมมุติว่า มี ชื่อของคอลัมน์ที่เหมือนกัน ยกตัวอย่างเช่น m15 มี 3 คอลัมน์และเราต้องการให้รวมผลของทั้ง 3 นั่้นมาแสดงผมที่ A36 ด้วยสูตรจะเป็นอย่างไรครับ

[snasui]

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

[nopanan]

ขอโทษครับอาจารย์ผมลืมแนบไฟล์ ต้องการผลลัพท์ที่ E35 ครับ

[snasui]

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

=SUMPRODUCT(($B$2:$B$31=$D35)*($E$1:$V$1=E$34)*($E$2:$V$31))

Enter

[nopanan]

ขอบคุณครับอาจารย์ ได้ตามต้องการเลยครับ

[niwat2811]

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