VLOOKUP + MATCH (Exact)

VLOOKUP + MATCH (Exact)

เมื่อบทความที่แล้วเรารู้จัก Vlookup และ Drop_Down ไปแล้วว่าใช้ยังไง คราวนี้เราจะนำเจ้าฟังก์ชั่น MATCH
มาใช้คู่กับ Vlookup ดูบ้างว่ามันจะออกมาหน้าตาเป็นยังไง จะช่วยให้เราค้นหาข้อมูลได้ง่ายขนาดไหนไปดูกันเลยครับ
ขอเริ่มต้นที่ MATCH ก่อนแล้วกันครับ
MATCH เป็นฟังก์ชั่นที่ช่วยให้เราหาตำแหน่งของค่า พจน์ หรือ cell ว่าอยู่ในตำแหน่งที่เท่าไหร่ของช่วงข้อมูลที่เราต้องการหา
MATCH(lookup_value, lookup_array,[match_type})
  Lookup_value คือ ตัวเลข พจน์ หรือ cell ที่เราต้องการหาตำแหน่ง หรือใช้เป็นเกณฑ์
  Lookup_array คือ ช่วงของข้อมูลที่เราต้องการสืบค้น
  match_type คือ ชนิดของการประมวลผล มี 3 แบบ Less than, Exact, Greater than
Less than กับ Greater than จะสามารถดึงค่าเรียงลำดับในกรณีที่ช่วงของข้อมูลเป็นตัวเลข ส่วน Exact จะดึงค่าที่ตรงตัวเป๊ะๆ เท่านั้นมาแสดง
ดังนั้น Vlookup กับ Match (Exact) จึงเป็นสิ่งที่อยากนำเสนอ ไปดูตัวอย่างพร้อมๆกันเลยครับ

ตัวอย่าง จากตารางประกอบด้วยชุดข้อมูลที่บ่งบอกความเฉพาะตัวของพนักงานขายไม่ว่าจะเป็น Name, Region, Post code, PO, Volume, Grade
แต่ถ้าเราต้องการทราบข้อมูลของแต่ละคนก็ต้องมาคอยเลื่อนดูด้วยตาเปล่า ซึงอาจเกิดข้อผิดพลาดได้ ถ้าเป็นชุดข้อมูลน้อยๆก็ดีไป แต่ถ้าเกิดเราไปเจอกับชุดข้อมูลเป็นล้านๆcells จะทำอย่างไร
จากรูปภาพ เบื้องต้นเรามาเรื่องทำ Drop_Down เพื่อให้ง่านแก่การใช้กันก่อน D22 สีฟ้าเราใส่ชื่อแต่ละคนลงไป D23 สีเหลืองเราใส่ลักษณะเฉพาะลงไป (Region, Post code, PO, Volume, Grade)
คราวนี้มาเขียนสูตรกันใน F23 สีเขียว VLOOKUP+MATCH: (lookup_value, table_array, MATCH(lookup_value, lookup_array,[match_type}), [range_lookup])
ข้อสังเกตุง่ายๆ คือ เราจะแทรก MATCH เข้าไปแทน Col_index_num ในสูตร Vlookup นั่นเอง
=VLOOKUP($D$22,$B$2:$G$16,MATCH($D$23,$B$2:$G$2,0),FALSE)

เห็นมั้ยครับมันทำให้ชีวิตง่ายขึ้นจริงๆ ในรูปเราอยากรู้ว่า Tony ผลงานอยู่เกรดไหนก็แค่คลิ๊กเลือก Identity หรือถ้าเราอยากรู้ของคนอื่นๆก็คลิ๊กเลือก Name ได้เลย เพราะเราใส่ Drop_Down ไว้หมดแล้ว ผลลัพธ์จะปรากฎให้เราเห็นทันที
ผู้ที่ไม่มีพื้นฐาน excel มาก่อนอาจจะอ่านยังไม่รู้เรื่อง จะพยายามเข้ามาปรับปรุงบทความเรื่อยๆนะครับ

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

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.เชฟหมูตุ๋น

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 ช่วยให้เราทำงานได้ง่ายขึ้นและเร็วขึ้นจริงๆ
ขอบคุณที่ติดตามนะครับ