SUMIF & SUMIFS (ผลรวมแบบมีเงื่อนไข)

SUMIF & SUMIFS

เราเคยพูดถึงฟังก์ชั่น IF และ Vlookup ไปแล้วเมื่อคราวที่แล้ว ข้อจำกัดของ Vlookup คือ สามารถใช้ได้กับชุดข้อมูลที่cellที่เป็นเกณฑ์จะต้องมี 1 row เท่านั้น ถ้าหากมีมากกว่า 1 row ฟังก์ชั่น Vlookup จะดึงค่าที่มันหาเจอก่อนมาแสดง ดังนั้นข้อสรุปอาจผิดพลาดได้
แต่มีอีก 1 ฟังก์ชั่นที่ช่วยแก้ปัญหานี้ได้ นั่นคือ SUMIF&SUMIFS
1.SUMIF ผลรวมที่มีเงื่อนไขเดียว
2.SUMIFS ผลรวมที่มีหลายเงื่อนไข

SUMIF: (range, criteria, [sum_range])
range คือ ช่วงของข้อมูลที่จะใช้เป็นเกณฑ์ในการประมวลผล
criteria คือ ตัวเลข, cell, พจน์ ที่เป็นเกณ์หรือเงื่อนไข
sum_range คือ ช่วงของข้อมูลที่เราต้องการหาผลรวม

ตัวอย่างที่ 1 ในแต่ละ region มี sale หลายคนจึงทำให้ region มีหลาย row ที่ซ้ำกัน การใช้ Vlookup จึงทำไม่ได้
มาดูกันครับ ถ้าเราอยากรู้ว่า region Northern มียอดขายรวมเท่าไหร่กันแน่ ต้องทำยังไง
range = C3:C16
criteria = B20
sum_range = F3:F16

เมื่อเราเลือก Drop-Down Region ที่ cell B20 เราก็จะทราบเลยว่าแต่ละภาคยอดขายเป็นเท่าไหร่
ต้องการทราบภาคเหนือ เลือก Northern จะได้ยอดขายรวมเท่ากับ 118,200 ลองใช้เครื่องคิดเลขบวกดูว่าตรงมั้ย อิอิ


ทีนี้มาดูแบบหลายเงื่อนไขกันบ้าง
SUMIFS: (sum_range, criteria_range1, criteria1, criteria_range2, criteria2,...)
sum_range = F3:F16 = column sale's volume
criteria_range1 = C3:C16 = ช่วงของข้อมูลที่เป็นเกณฑื หรือเงื่อนไขที่1 (region)
criteria1 = B25 =  ตัวเลข, cell, พจน์ ที่เป็นเกณ์หรือเงื่อนไขที่1
criteria_range2 = E3:E16 = ช่วงของข้อมูลที่เป็นเกณฑื หรือเงื่อนไขที่2 (PO)
criteria2 = C25 = ตัวเลข, cell, พจน์ ที่เป็นเกณ์หรือเงื่อนไขที่2

ตัวอย่างที่ 2 จากข้อมูลชุดเดิม เราอยากทราบว่า Westhern มียอดขายรวมเท่าไหร่ก็คงใช้ SUMIF ช่วยได้แต่เราจะเพิ่มเงื่อนไขเข้าไปอีก คือ จำนวน PO ต้องมากกว่าหรือเท่ากับ 10 ตอนนี้เราก็ได้เงื่อไขมาแล้ว 2 อย่างคือ Region = Westhern, PO >= 10 ลองทำตามภาพประกอบครับ

sum_range = F3:F16
criteria_range1 = C3:C16
criteria1 = B25
criteria_range2 = E3:E16
criteria2 = C25

คำตอบที่ได้เท่ากับ 54,050 Region westhern มี sale 3 คน แต่มีเพียง 2 คนเท่านั้นที่มี PO >= 10 คือ John 27,000 และObama 27,050

ขอบคุณสำหรับท่านที่เข้ามาอ่านนะครับ

Cr.เชฟหมูตุ๋น

Break Even Point (จุดคุ้มทุน)

Break Even Point (จุดคุ้มทุน)
หากจะพูดถึงหลักการทางเศรษฐศาสตร์เกี่ยวกับการ Run ธุรกิจให้สามารถอยู่ได้ หลายๆครั้ง เรามักจะพูดถึง "Break Even Point" BEP. "จุดคุ้มทุน"
ต้นทุนทางเศรษฐศาสตร์กับต้นทุนทางบัญชีนั้นแตกต่างกัน ซึ่งผมจะไม่พูดถึงรายละเอียด ทางบัญชีจะถือว่ากำไรคือ รายรับ > รายจ่าย
แต่ในทางเศรษฐศาสตร์เราจะถือว่า รายรับ = รายจ่าย คือกำไรปกติ  ดังนั้นจุดที่ รายรับ = รายจ่าย จึงถือว่าเป็นจุดคุ้มทุน
ต่อไปเราก็ต้องมารู้จักคำศัพท์ทางเศรษฐศาสตร์คร่าวๆ ที่จำเป็นกันก่อนครับ
    1.Price ราคา (p)
    2.Quantity ปริมาณ (q)
    3.Fix Cost ต้นทุนคงที่ (FC)
    4.Valiable Cost ต้นทุนผันแปรต่อหน่วย (VC)
    5.Total Revenue รายรับ (TR)
    6.Total Cost รายจ่าย (TC)

คำนวณ Break Even Point จุดคุ้มทุน
    BEP.      TR = TC
               p*q = FC+VC

ตัวอย่างที่ 1 ธุรกิจผลิตเสื้อผ้า มีต้นทุนที่เป็นต้นทุนคงที่ 1,000,000 บาท มีต้นทุนผันแปรต่อหน่วย 35 บาท ตั้งราคาขายไว้ที่ 150 บาท/หน่วย
ถ้าเราอยากทราบว่าต้องขายเสื้อให้ได้กี่ตัวจึงจะคุ้มทุน หรือได้กำไรปกติในทางเศรษฐศาสตร์ เราลองมาดูไปพร้อมกันครับ
         จาก BEP.    TR = TC
                        p*q = FC+VC
                     150*q = 1,000,000 + (q*35)
                  150 - 35 = 1,000,000/q
         จะได้ว่า         q = 1,000,000/115 = 8,695.652 หรือ 8,696 ตัว

ตัวอย่างที่ 2 ธุรกิจผลิตเสื้อผ้า มีต้นทุนที่เป็นต้นทุนคงที่ 1,000,000 บาท มีต้นทุนผันแปรต่อหน่วย 35 บาท ตั้งเป้าไว้ว่าจะสามารถขายได้ 9,400 ตัว
ถ้าเราอยากทราบว่าต้องตั้งราคาขายกี่บาทจึงจะคุ้มทุน
         จาก BEP.    TR = TC
                        p*q = FC+VC
                  p*9,400 = 1,000,000 + (9,400*35)
                            p = 1,329,000/9,400
         จะได้ว่า         p = 141.383 หรือ 142 บาท/ตัว

เสร็จจากเรื่องตัวเลขไปแล้วเราลองมาดูกราฟเพื่อผ่อนคลายกันดีกว่าครับ

จากรูปจะเห็นได้ว่า จุดสัมผัสระหว่าง TR กับ TC ก็คือ BEP หรือจุดคุ้มทุนนั้นเอง ในระยะแรกจะเห็นได้ว่าธุรกิจขาดทุน คือ เส้น TR ต่ำกว่า TC
ขาดทุนในต้นทุนรวมพื้นที่ A และขาดทุนในต้นทุนผันแปรพื้นที่ B กำไรเกินปกติคือพื้นที่ C
เป็นไงบ้างครับพอจะเข้าใจคร่าวๆแล้วรึเปล่า

Cr : เชฟหมูตุ๋น
       

Vlookup และการทำ Drop-Down

Vlookup และการทำ Drop-Down

จากที่เราเคยลองจัดกลุ่มข้อมูลโดยใช้ IF มาแล้วในบทความที่แล้ว คราวนี้เราจะลองใช้ฟังก์ชั่นที่จะทำให้เราดึงค่าของข้อมูลตามเงื่อนไขได้ง่ายขึ้น
นั่นคือ Vlookup ในที่นี้หลักการทำงานของ Vlookup กับ Hlookup เหมือนกันทุกประการต่างกันตรง Vlookup จะใช้กับชุดข้อมูลในแนวตั้ง
ส่วน Hlookup จะใช้กับชุดข้อมูลในแนวนอน ดังนั้นผมจะพูดถึงเฉพาะ Vlookup เท่านั้น เนื่องจากชุดข้อมูลส่วนใหญ่มักจะเป็นแนวตั้งเสมอ

หลักการณ์ของ Vlookup เป็นดังนี้
VLOOKUP: (lookup_value, table_array, col_index_num, [range_lookup])
1.lookup_value คือ ตัวเลข, เซลล์, พจน์ ที่เราจะใช้เป็นเกณฑ์ในการประมวลผล
2.table_array คือ ช่วงเซลล์ทั้งหมดที่ครอบคลุบ  ตัวเลข, เซลล์, พจน์ ที่เราจะใช้เป็นเกณฑ์ในการประมวลผล และผลลัพธ์ที่ต้องการ
3.col_index_num คือ column number ที่เราต้องการให้ดึงค่าผลลัพธ์
4.range_lookup True,1 จะดึงค่าใกล้เคียงที่สุดมาหากไม่เจอตามเงื่อนไข  False,0 จะดึงเฉพาะค่าที่ตรงเงื่อนไขเพียงอย่างเดียวเท่านั้น

ตัวอย่างที่1 เราต้องการหาว่า Tony สามารถทำยอดขายได้เท่าไหร่  =VLOOKUP(B19,B2:F16,5,FALSE)ผลลัพธ์จะส่งไปยัง cell สีเหลือง
1.lookup_value คือ B:19
2.table_array คือ B2:F16 คลุมข้อมูลทั้งหมด
3.col_index_num คือ 5 นับจาก name เป็นcolumnที่1
4.range_lookup คือ False เพราะเราต้องการข้อมูลที่ตรงเป๊ะอย่างเดียว

Tony ทำยอดขายได้  31,500  จะเห็นว่าแม้จะเป็นข้อมูลที่มีขนาดใหญ่ก็ง่ายแก่การจัดการขึ้นมาทันที ต่อให้มีเป็นล้านๆCells ก็เอาอยู่

มีอีกสิ่งหนึ่งที่ปรากฎในตัวอย่างที่ 1 นั่นคือ Drop-Down ที่จะทำให้ผู้ใช้สะดวกสบายยิ่งขึ้นในการดึงดูข้อมูล คือ เราไม่ต้องลบและกรอกชื่อใหม่ใน B:19 หรือไม่ต้องเขียนสูตรใหม่เรื่อยๆ
มาลองดูวิธีสร้าง Drop-Down กับเลยครับ
1.คลิ๊กที่cell ที่จะให้มี Drop-Down
2.คลิ๊กที่ menu Data เลือก Data Validation
3.กำหนดค่าใน data validation box โดย Allow = List และ Sorce = ช่วงcellsที่มีข้อมมูลที่ต้องการ
ลองมาทำตามภาพประกอบเลยครับ

เห็นไหมครับ คูล มากๆ ชิกๆ ใสๆ
ติดตามตอนต่อไปนะครับ สวัสดีครับ

Excel, IF, IFซ้อน

Excel, IF, IFซ้อน

สวัสดีครับ

หากเราจะพูดถึงโปรแกรมสำเร็จรูปที่ช่วยให้การทำงานเกี่ยวกับข้อมูลจำนวนมากง่ายขึ้น ก็คงหนีไม่พ้น Excel
Excel เป็นโปรแกรมที่มีฟังก์ชั่นหลากหลายเอาไว้จัดการกับข้อมูลได้อย่างมีประสิทธิภาพ โดยส่วนตัวผมเองก็ใช้ Excel
ช่วยในการทำงานเช่นกัน แต่จะมีไม่กี่ฟังก์ชั่นที่เราใช้กันบ่อยๆ ในวันนี้ผมจะขอนำเสนอ IF เราไปดูกันเลยว่าหลักการทำงานมันเป็นยังไง

IF=(logical_test,[value_if_true],[value_if_false])
1.logical_test คือ ตัวเลข, เซลล์, พจน์, ที่เราต้องการใช้เป็นหลักเกณฑ์ในการประมวลผล
2.value_if_true คือ ตัวเลข, เซลล์, พจน์, ที่เราต้องการให้แสดงผล เมื่อ logical_test เป็นจริง
3.value_if_false คือ ตัวเลข, เซลล์, พจน์, ที่เราต้องการให้แสดงผล เมื่อ logical_test เป็นอื่น

ตัวอย่างที่ 1 =IF(A2=500,"Yes","No")
ถ้า cell A:2มีค่าเท่ากับ 500 ให้แสดงค่า "Yes" ในcell B:2 ถ้าเป็นอื่นให้แสดงค่า "No"

จะเห็นได้ว่า cell B:3 แสดงค่า "No" เพราะไม่เข้าเงื่อนไขที่เป็นจริง

ทีนี้เราลองมาทำ IF ซ้อนกันดูบ้าง

บางท่านอาจจะยังไม่รู้ว่า IF สามารถซ้อนได้ถึง 7 ชั้นเลยทีเดียว แต่เราจะลองซ้อน 2 ดูก่อนนะครับ

ตัวอย่างที่ 2 =IF(F3>=40000,"Hight",IF(F3>=25000,"Middle","Low")) ช่วยในการจัดกลุ่ม 

ถ้า cell F:3 มีค่ามากกว่าหรือเท่ากับ 40,000 เป็นจริงให้แสดงค่า "Hight" ถ้า F:3 มีค่ามากกว่าหรือเท่ากับ 25,000 เป็นจริงให้แสดงค่า "Middle" อื่นๆ ให้แสดงค่า "Low"

ด้วยหลักการเดียวกันนี้ เราสามารถใช้ IF ซ้อนกันหลายๆชั้นได้เพื่อจัดกลุ่มชุดข้อมูลที่มีหลายเงื่อนไข
เห็นไหมครับ การใช้ IF ช่วยให้เราทำงานได้ง่ายขึ้นและเร็วขึ้นจริงๆ
ขอบคุณที่ติดตามนะครับ