ms excel - function ดีๆ แต่ใช้ไม่เป็น
สวัสดีครับ บทความนี้ขอพูดถึงการใช้ function excel แบบรวบรัด เพื่อให้นำไปใช้งานได้ อย่างรวดเร็ว ขอพูดถึงเฉพาะในกลุ่มของ lookup and reference ก่อน ดังนี้
กลุ่มฟังก์ชั่นที่คืนค่าเป็นตำแหน่งอ้างอิง 1. ADDRESS 2. Match กลุ่มฟังก์ชั่นที่คืนค่าเป็นข้อมูลของ ROW , ROWS และ AREA 1. ROW 2. ROWS 3. COLUMN 4. COLUMNS 5. AREAS กลุ่มฟังข์ชั่นที่คืนค่าเป็นข้อมูลหรือตำแหน่งข้อมูล 3. HLOOKUP 4. VLOOKUP 5. LOOKUP 6. CHOOSE 7. INDEX
กลุ่มฟังก์ชัอื่น 1. GETPIVOTDATA 2. HYPERLINK 3. TRANSFOSE 4. RTD
โดยพื้นฐานแล้ว function เป็นการทำงานแบบรับค่าเอาไปทำอะไรสักอย่างแล้วคืนค่ากลับมา ฉนั้นการศึกษา function แต่ละตัวให้เข้าใจ ต้องดูว่า function แต่ละตัวต้องการรับค่าอะไรแล้วจะคืนค่าอะไร เพื่อให้เลือกนำไปใช้ได้ถูกต้องตามหน้าที่ แก้ปัญหาได้ตรงจุดที่สุด
จากที่ได้ศึกษาทำความเข้าใจ ฟังก์ชั่นต่างๆ ด้วยตนเอง รู้สึกว่าเข้าใจยาก ไม่ว่า web ไหนๆ ก็มักจะอธิบายทีละฟังก์ชั่น จึงไม่รู้ว่าจะนำไปใช้งานอย่างไร ฉนั้นบทความนี้จะมุ่งไปที่โจทย์ ตัวอย่างการนำไปใช้งาน ซึ่งจะเห็นการใช้ฟังก์ชั้นพร้อมกันหลายตัวในสูตรเดียวกัน ส่วนใครอยากรู้ที่มาแต่ละตัวเพิ่มเติมสามารถค้นหาได้ตาม web หรือตำราทั่วไป
รูปที่ 1 ตัวอย่างข้อมูล
การใช้ฟังก์ชั่นที่ง่ายที่สุดคือ ฟังก์ชั่น INDIRECT เช่นตัวอย่าง
จะเห็นว่า ฟังก์ชั่น INDIRECT จะทำหน้าที่ดึงข้อมูลจาก Cell ที่กำหนดไว้ต่อท้าย เช่น =INDIRECT("B7") ก็จะเป็นการดึงข้อมูลที่อยู่ใน B7 มาให้คือคำว่า "Myanmar"
INDIRECT() รับค่าตำแหล่งด้วยรูปแบบสตริง คืนค่าด้วยค่าข้อมูล
|
ต่อไปถ้าเราแก้ไขสูตรนิดหน่อย โดยใช้ฟังก์ชั่น ADDRESS มาแทนตรง("B7") ท้ายของคำสั่งเดิม =INDIRECT(ADDRESS(7,2)) จะเกิดอะไรขึ้น
จะเห็นว่าฟังก์ชั่น ADDRESS(row,column) จะคืนค่าเป็นตำแหน่ง Cell และค่าที่ได้ก็ส่งให้กับฟังก์ชั่น INDIRECT อีกที่นั่นเอง
ลองมาดูตัวอย่างกันอีกสักชุดสำหรับการใช้ INDIRECT ,ADDRESS และแถมด้วย OFFSET , COLUMN
ADDRESS() รับค่าตำแหล่งด้วยเลขแถวเลขคอลัมน์ คืนค่าตำแหน่งด้วยรูปแบบสตริง
| โจทย์คือ จากข้อมูลรูป 1 อยากให้นำข้อมูลจาก Column B ชื่อประเทศ ซึ่งเดิมเรียงเป็นแนวตั้ง มาเรียงตามแนวนอนด้วยการลากไปทางขวา (คล้ายกับการใช้คำสั่ง Transpose)
จงจำไว้ว่า INDIRECT ต้องการ ตำแหน่ง cell แล้วคืนค่าเป็น ข้อมูลใน cell นั้นๆ ADDRESS ต้องการ พิกัด แถว และ คอลัมน์ แล้วคืนค่าเป็นตำแหน่ง cell ในรูปแบบ Text ซึ่งส่งให้ ฟังก์ชั่น INDIRECT นำไปใช้ต่อได้ จากรูปด้านบนแสดงการใช้ฟังก์ชั่น INDIRECT กับ ADDRESS ให้สังเกตุตรงฟังก์ชั่น ADDRESS(E1,2) คือ E1 คืออ้างข้อมูลที่ E1 ซึ่งก็คือ row=1 ส่วน 2 คือ column=2 ถ้าเราลากสูตรไปทางขวา cell ถัดไปจะเป็น ADDRESS(F1,2) จะได้ว่า F1 คืออ้างข้อมูลที่ F1 ซึ่งก็คือ row=2 ส่วน 2 คือ column=2 เหมือนเดิม แต่จะเห็นว่าเราต้องใส่ตัวเลข 1 - 10 ไว้ข้างบนด้วย ตัวอย่างถัดไปจะได้ผลเหมือนกันแต่ไม่ต้องใส่ตัวเลข ซึ่งทั้งสองตัวอย่างสามารถนำไปประยุกต์ใช้งานได้ตามลักษณะงานต่างๆ ครับ
รูปด้านล่างเป็นตัวอย่างการใช้คำสั่ง Offset กับ Column แต่ทำงานคล้ายกับโจทย์ที่ผ่านมา
ตัวอย่างนี้เป็นการใช้ ฟังก์ชั่น OFFSET กับ COLUMN ความหมายคือการเอื้อมหรือขยับไปยังพิกัด cell ที่ต้องการ โดยต้องกำหนดจุดยืนหรือจุดอ้างอิงก่อน มีรูปแบบดังนี้ =OFFSET(refference point,offset row,offset column) หรือ =OFFSET(จุดยืน,จำนวนบรรทัดที่ขยับ, จำนวนคอลัมน์ที่ขยับ)
จากรูปด้านบนแสดงการใช้ฟังก์ชั่น OFFSET กับ COLUMN ให้สังเกตุว่าเราใส่อะไรให้กับ ฟังก์ชั่น OFFSET บ้าง ดังนี้ - จุดยืน = $B1 หมายถึง จุดยืน อันแนวแน่นั้นเอง - จำนวนบรรทัดที่ขยับ = COLUMN()-4 หมายถึง มีการขยับทางทางแถว ด้วย 5-4 = +1 คือขยับลง 1 บรรทัด (+ ขยับลง ,-ขยับขึ้น ) ที่บางท่านอาจจะสงสัยว่า 5 มาจากไหน 4 มาจากไหน 4 ก็มาจากสูตรไง เห็นอยู่ในสูตร ส่วน 5 นั้นมาจาก คอลัมน์ E ซึ่งคือคอลัมน์ที่ 5 นั้นเอง แต่เนื่องจากเราต้องการ Brunei ที่อยู่ถัดจากจุดยืนมา 1 บรรทัด ฉนั้นเราใช้ 5 ไม่ได้ต้องลบออก 4 จึงจะได้ 1 เพื่อให้มีการ Offset 1 บรรทัด - จำนวนคอลัมน์ที่ขยับ = 0 หมายถึง ไม่มีการขยับทาง คอลัมน์
การใช้ฟังก์ชั่น เรามีดูอีกตัวอย่างหนึ่ง ตัวอย่างนี้ผมได้เพิ่ม Data ขึ้นมาชุดหนึ่งเป็น country set B(column F) ตามรูป ซึ่งรายชื่อประเทศต่างจาก Country Set A (Column B) จะหาวิธีที่จะตรวจสอบว่ามีประเทศใดตรงกันบ้างสามารถสร้างสูตรได้ดังต่อไปนี้ =IF(ISERROR(OFFSET($B$1,MATCH(F2,B$2:B$11,0),0)),"-",OFFSET($B$1,MATCH(F2,B$2:B$11,0),0))
เห็นสูตรแล้วอย่าเพิ่งท้อนะครับหลักการไม่ยากเท่าไร แต่การเขียนอธิบายนี่ เรื่มมีอาการท้อ ค่อยๆดูไปแล้วกันสูตรยาวๆนี้ที่มีคำสั่งเพิ่มมา 3 ตัวคือ - IF(conditions , true-result , false-result) - ISERROR() - MATCH(desired value , data , matching type) ส่วน ฟังก์ชั่น OFFSET(reference point ,offset row , offset column) เราได้เห็นการใช้งานผ่านมาบ้างแล้วลองมาดูฟังก์ชั่น OFFSET ในสูตรซิว่ามีกี่ตัวอยู่ตรงไหนบ้างถูกต้องแล้วครับ มี 2 ตัว อยู่ในส่วน conditions กับ false-result ของคำสั้ง IF นั้นเอง 
ผลลัพธ์การจับคู่ข้อมูลหรือที่เรียกว่าการ data Matching หรือ data Comparison
ตัวอย่างการใช้ ฟังก์ชั่น OFFSET อีกตัวอย่างหนึ่ง ตามรูปข้างล่าง 
หมายเหตุต้องครอบด้วยคำสั่ง SUM ถ้าใช้ OFFSET ตามลำพังจะเดือดร้อน
ฟังก์ชั่น INDIRECT กับ INDEX ฟังก์ชั่น ทั้ง 2 ตัวนี้มองเผินๆเขียนคล้ายกันและมีคุณสมบัติในการคืนค่าเป็นข้อมูลเหมือนกันแต่การใช้งานต่างกันตรงที่ ฟังก์ชั่น INDIRECT(reference as text) ฟังก์ชั่น INDEX (data,row,column) ทั้ง INDEX() และ INDIRECT()ต่างก็คืนค่ากลับมาเป็นข้อมูลเหมือนกันทั้งคู่ ที่ผ่านมาได้กล่าวถึงINDIRECT() มาบ้างแล้วแต่ในส่วนของการรับค่าจะต่างกันเล็กน้อย ฟังก์ชั่น INDIRECT(ref_text,Type) จะรับค่าตำแหน่งและคืนค่าเป็นข้อมูล รูปแบบที่ INDIRECT()จะรับมี 2 แบบคือ A1 และ [R1C1]ขึ้นอยู่กับการกำหนด True และ False ตามลำดับ เช่น =INDIRECT(B5,True) หรือ =INDIRECT(R5C2,False) ฟังก์ชั่น INDEX(array,row,column) จะรับค่าพิกัดในพื้นที่กำหนดและคืนค่าเป็นข้อมูล ตัวอย่างเช่น ต้องการให้คืนค่าที่อยู่ในพื้นที่ A1:C11แถวที่ 4คอลัมน์ที่ 2 จะได้สูตรดังนี้=INDEX(A1:C11,4,2) ลองมาดูตัวอย่างการใช้งานกันหน่อยดีกว่าครับ
สูตร =INDEX(xData,MATCH(MAX(C2:C11),xPopulation,0),2) ตัวอย่างนี้ต้องการหาว่าประเทศอะไรมีประชากรมากสุดผมเริ่มด้วยฟังก์ชั่น MAX() หาค่าที่มากที่สุด ส่งให้ ฟังก์ชั่นMATCH() รับไปค้นหา แล้ว ฟังก์ชั่น MATCH คืนค่ามาเป็นเลขบรรทัด จากนั้นก็ใช้เลขบรรทัดดังกล่าว ส่งให้ ฟังก์ชั่น INDEXไปดำเนินการ ผลที่ได้คือข้อมูล Indonesia จากรูปด้านบนผมได้สร้างพื้นที่ให้กับข้อมูลเพื่อสะดวกในการเขียนสูตรและลดความผิดพลาด
ฟังก์ชั่น LOOKUP,HLOOKUP และ VLOOKUP
โปรดติดตามตอนต่อไป -------ขออภัยอยู่ระหว่างการปรับปรุง----- เราใช้งาน LOOKUP เพื่อคืนค่าข้อมูล โดยต้องระบุค่าเพื่อใช้ในการชี้ แล้วค่าที่ต้องการคือค่าที่อยู่บรรทัดเดียวกับตัวชี้ มีรูปแบบตังี้ =LOOKUP(ตัวชี้,คอลัมน์ที่ตัวชี้อยู่,คอลัมน์ที่ข้อมูลจะคืนค่าอยู่) เช่นตามตัวอย่าง =LOOKUP(A2,A$2:A$11,B$2:B$11) ข้อสำคัญก่อนการใช้งานต้อง - ต้องเรียงข้อมูลก่อน และการเรียงต้องเรียงจากค่าน้อยไปมาก
VLOOKUP() รูปด้านล่างแสดงการใช้ฟัก์ชั่น VLOOKUP
ข้อระวัง - ค่าตัวเปรียบเทียบต้องใกล้เคียงกับค่าตัวชี้ ถ้าต่างกันมาก จะได้ #NA - ต้องทำการ sort (smallest to largest) ใน arrey ที่คอลัมน์เปรียบเทียบเสมอ ส่วนที่คอลัมน์ตัวชี้ไม่ต้อง sort - คอลัมน์ตัวชี้อยู่นอก arrey ได้ ไม่จำเป็นต้องอยู่ใน arrey เดียวกับตัวเปรียบเทียบ และ ไม่จำเป็นต้อง sort
reference //www.excelfunctions.net/Excel-Lookup-And-Reference-Functions.html
| Create Date : 06 พฤศจิกายน 2555 |
| Last Update : 17 ธันวาคม 2555 13:13:58 น. |
|
3 comments
|
| Counter : 9905 Pageviews. |
 |
|
ฝากข้อความหลังไมค์
Rss Feed
ผู้ติดตามบล็อก : 4 คน [
