วันอังคารที่ 20 พฤศจิกายน พ.ศ. 2555

การใช้ SUMIF, SUMIFS รวมข้อมูลแบบมีเงื่อนไขใน excel

SUMIF, SUMIFS Function

คือค้นหาข้อมูลจากช่วงเซลล์ใดๆ และให้รวมข้อมูลของช่วงที่เราต้องการ ถ้าตรงตามเงื่อนไขที่ระบุ

ถ้าเปรียบเทียบการทำงานกับถนนทูเวย์
ถนนด้านหนึ่งคือ Vlookup ถนนอีกด้านคือ Sumif ด้วยคุณสมบัติการทำงานที่สวนทางกัน
  • Vlookup = one from many ดึงคำตอบหนึ่งข้อมูล จากแหล่งข้อมูลที่มากมายหลากหลาย
  • Sumif = many to one เป็นการรวมผลลัพท์จากข้อมูลจากหลายๆ ข้อมูล

แล้วเมื่อไรควรใช้ Sumif??

เพราะ Sumif ก็คือการรวมผลลัพท์ โดยกำหนดจากค่าอ้างอิงในฟังก์ชัน เช่น ต้องการหายอดรวมค่ารถโดยสาร จากรายการที่เก็บรวมรวมไว้ทั้งเดือน ดังนั้น sumif จึงเป็นฟังก์ชัน ที่มีคุณสมบัติเหมาะสม เมื่อต้องการรวมผลลัพท์หรือสรุปยอดตัวเลขจากฐานข้อมูลที่มีการบันทึกค่าซ้ำๆ กันมากมาย และสิ่งสำคัญที่จะทำให้ฟังก์ชันอย่าง Sumif เวริ์คสุดๆ คือต้องมีฟิลด์ข้อมูลหลัก ที่มีค่าไม่ซ้ำ (primary key) เพื่อให้สามารถใช้เป็นตัวอ้างอิงให้ดึงค่าจากฐานข้อมูลมารวมให้ถูกต้อง

ไวยากรณ์
SUMIF Function in Excel

=SUMIF (range, criteria, sum_range)

  • range: เซลล์หรือช่วงของเซลล์ที่เป็นโจทย์ เพื่อใช้หาผลรวมของ sum_range
  • criteria: เป็นเงื่อนไขที่ระบุ
  • sum_range: คือช่วงเซลล์ที่ต้องการหาผลรวม
ไวยากรณ์

SUMIFS(sum_range, criteria_range 1, criteria 1 criteria_range 2, criteria 2..)

  • sum_range: เหมือนกับ Sumif คือเซลล์ที่ต้องการหาผลรวม
  • criteria_range: range ที่เป็นเงื่อนไขสำหรับ SUMIFS
  • Sumif สามารถรวมข้อมูลจากหลายแหล่ง ด้วยเงื่อนไขที่แตกต่างได้เป็นอย่างดี

ตัวอย่างการใช้ SUMIF และ SUMIFS ให้คืนค่าผลรวม iPod ในสต็อค
  • Branch (Column A)คือสาขาสมมุติให้มีสาขา A, B และ C
  • Product (Column B) กับ Mem. (Column C)คือ ประเภทของสินค้าในสต็อคของแต่ละสาขา เช่น สาขา A มีสต็อคอยู่ 3 รายการ, สาขา B และ C มีอยู่ 2 รายการ
  • Qty. (Column D) เป็นจำนวนสต็อคของ iPod
เราจะใช้ตารางโจทย์เป็นฐานข้อมูลเพื่อหาผลลัพท์ในตารางแสดงฟังก์ชัน Sumif,Sumifs

ตาราง1 คือโจทย์ทดสอบฟังก์ชัน Sumif,Sumifs

RowColumn
ABCD
1BranchProductMemQty.
2AiPod touch32 GB.23
3AiPod touch64 GB.12
4AiPod Nano16 GB.10
5BiPod touch32 GB.15
6BiPod Nano16 GB.14
7CiPod Shuffle2 GB.32
8CiPod touch16 GB.11




SUMIFS Function in Excel
ตาราง2 แสดงการใช้ฟังก์ชัน SUMIF กับ SUMIFS
RowColumn
GHI
1หาผลรวมของผลลัพท์การใช้ฟังก์ชัน ใน Column G
2iPod Nano24
=SUMIF(B2:B8,G2,D2:D8)
3iPod touch61
=SUMIF(B2:B8,G4,D2:D8)
4iPod touch 32 GB.38
=SUMIFS(D2:D8,B2:B8,LEFT(G3,10),C2:C8,RIGHT(G3,6))
อธิบายการใช้ฟังก์ชัน Sumif กับ Sumifs ในตารางการใช้ฟังก์ชัน
  1. SUMIF ใช้หาผลรวมที่มีเงื่อนไขเดียว
    1. iPod Nano :
      1. range พื้นที่เซลล์เรนจ์ที่ใช้หาผลรวมของ iPod Nano คือ B2:B8 (จากตาราง1)
      2. criteria เงื่อนไขคือ G2 (จากตาราง2)
      3. sum_range ช่วงเซลล์เรนจ์ที่ต้องการให้คำนวณคือ D2:D8(จากตาราง1)
      4. คำตอบที่ได้คือ (10 +14)ผลลัพท์จึงได้ 24
    2. iPod touch: แทนค่าในฟังก์ชันเหมือนการหาผลรวมสต็อค iPod Nano ทุกอย่างยกเว้นเงื่อนไข(criteria)
      1. ผลลัพท์ได้(D2  +D3 + D5 + D8) หรือ (23 +12 + 15 + 11) = 61

  2. SUMIFS ใช้หาผลรวมที่มีหลายเงื่อนไขมากกว่า Sumif เราจะกำหนดเงื่อนไข 2 ตัว เพื่อหาผลสต็อคของ iPod touch 32 GB.ในตารางที่2
    1. กำหนดให้ Criteria_range1 คือ คอลัมท์ Product (B2:B8) จากตารางที่1
    2. ต้องทำให้ Criteria1 เหมือนกับค่าใน คอลัมท์ Product (B2:B8) ของตารางที่ 1 เราจึงต้องตัดคำในเซลล์ G4(ตารางที่2) โดยใช้  Left(G4,10) จะได้คำว่า iPod touch
    3. Criteria_range2 คือ คอลัมท์ Mem (C2:C8)
    4. หา Criteria2 ด้วยการตัดคำใน  G4(ตารางที่2) โดยใช้ Right(G4,6) คือแทนค่าด้วย Text function  จะได้คำว่า 32 GB.
    5. เมื่อแทนค่าทั้งหมดผลลัพท์จะได้ 38 หน่วยจากสองรายการของตาราง1
จากตัวอย่างจะเห็นว่า criteria ในฟังก์ชัน sumif ไม่จำเป็นต้องเป็นค่าใดค่าหนึ่ง สามารถใช้กับฟังก์ชันแทนที่ลงไปเลยก็ได้แต่ถ้าหากเป็นข้อมูลจำนวนมากๆ การจัดเก็บข้อมูลควรแบ่งเป็น 3 ส่วน คือ Input sheet, Record sheet และ Output sheet จะทำให้งาน flow ได้สะดวกและรวดเร็วขึ้น

Reactions:

0 comments:

แสดงความคิดเห็น