ทำความรู้จัก แผนภูมิวงกลมหรือโดนัท (Pie/Doughnut chart) ใน Excel

ในบทความนี้ เรามาทำความรู้จักกับแผนภูมิวงกลมต่อจาก แผนภูมิคอลัมน์ โดยเมื่อคุณอ่านบทความนี้จบ คุณจะได้เรียนรู้ประเภทของแผนภูมิวงกลม การ Plotting ข้อมูลให้เป็นแผนภูมิวงกลม  การสร้างแผนภูมิวงกลมให้สวยมากขึ้น  

แผนภูมิวงกลมหรือโดนัท (Pie/Doughnut chart) 
เป็นการแสดงข้อมูลเป็นวงกลมหรือโดนัท โดยที่แต่ละวงจะแทนข้อมูล 1 ชุด ที่ถูกแบ่งเป็นสัดส่วนไว้แล้ว  

โดยจุดสังเกต ถ้าคุณจะใช้แผนภูมิวงกลมนี้กับชุดข้อมูลหลายชุด จะถือว่าไม่หมาะสม เพราะจะทำให้สัดส่วนของ Doughnut ด้านนอกผิดไปจากขนาดข้อมูลจริง (เพราะ สัดส่วนข้อมูลของ Doughnut ด้านนอกจะมีขนาดใหญ่กว่า Doughnut ด้านใน) 

ดังนั้น หากต้องการจะทำการเปรียบเทียบชุดข้อมูลหลาย ๆ ชุด ควรจะไปใช้แผนภูมิคอลัมน์ (Column Chart) หรือแผนภูมิแท่ง (Bar Chart) หรือถ้าหากคุณจำเป็นต้องใช้แผนภูมินี้จริงๆ ควรมีการแสดงค่าข้อมูลหรือเปอร์เซ็นต์ ไว้ในแผนภูมิด้วย

ข้อกำหนดเบื้องต้นเมื่อต้องใช้แผนภูมิคอลัมน์หรือโดนัท
1. มีชุดข้อมูลอย่างน้อย 1 ชุดหรือมากกว่า 
2. ต้อง ไม่ มีค่าติดลบ หรือ เป็นศูนย์ ในข้อมูล
3. รายการย่อย (Item) ของข้อมูลต่อชุด ต้องไม่เกิน 7 รายการ

การสร้างแผนภูมิวงกลม
1. เลือกข้อมูลที่ต้องการจะสร้างแผนภูมิวงกลม 

2. คลิก Insert > Insert Pie or Doughnut Chart > Doughnut ดังรูปที่ 1 

รูปที่ 1 ช่องทางการสร้างแผนภูมิวงกลมหรือโดนัท

โดยแผนภูมิวงกลมหรือโดนััท สามารถแบ่งเป็น 3 ประเภททย่อย ดังนี้ 
3.1. แผนภูมิวงกลมหรือโดนัท (Pie Chart) ดังรูปที่ 2 

รูปที่ 2 แสดงตัวอย่างแผนภูมิวงกลม

3.2. แผนภูมิวงกลมแบบ 3 มิติ (Pie in 3-D Chart) ดังรูปที่ 4

รูปที่ 4 แสดงตัวอย่างแผนภูมิวงกลมแบบ 3 มิติ

3.3. แผนภูมิวงกลมโดนัท ดังรูปที่ 5

รูปที่ 5 แสดงตัวอย่างแผนภูมิโดนัท แบบข้อมูลชุดเดียว

สำหรับข้อมูลที่มีหลายชุด แผนภูมิโดนัท ก็สามารถนำเสนอได้ดี ดังรูปที่ 6

รูปที่ 6 แสดงตัวอย่างแผนภูมิโดนัท แบบข้อมูลหลายชุด

นอกจากนี้ ข้อกำหนดเบื้องต้นที่แนะว่าให้แผนภูมิมีรายการย่อย (Item) ไม่เกิน 7 รายการ ซึ่งถ้าหากจำเป็นต้องนำเสนอข้อมูลที่มากกว่านั้นละ เราจะหาทางออกให้กับแผนภูมิแบบนี้อย่างไร 

คำตอบก็คือ Pie of Pie Chart ดังรูปที่ 7 

รูปที่ 7 แสดงตัวอย่างการใช้แผนภูมิแบบ Pie of Pie Chart 

จากรูปที่ 7 จะพบว่า ข้อมูลดิบ (ตาราง) มีรายการย่อย (Item) มากกว่า 7 รายการ เราเลือกใช้ Pie of Pie Chart เพื่อนำเสนอข้อมูลรายการอื่นให้ได้รายละเอียดเพิ่มเติมนั้นเอง 

ถ้ายิ่งข้อมูลดิบมีจำนวนมากเท่าไร รายละเอียดของข้อมูลก็ยิ่งดูยากมากขึ้นตามไปด้วย จากรูปที่ 7 เราได้นำเสนอเป็นเปอร์เซ็น ซึ่งเป็นรูปแบบที่เหมาะสมมากกว่าตัวเลขตามตาราง 

รายละเอียดของแผนภูมิ เราจะอธิบาย เริ่มจาก ข้อมูลบรรทัดแรกของตาราง คือ
1. สีฟ้า-7% หมายถึง Chumphon วนไปทางซ้าย 
2. สีแดง-13% หมายถึง Amnat Charoen
3. สีเท่า-14% หมายถึง Lampang
4. สีเหลือง-14% หมายถึง Chanthaburi
5. สีน้ำเงิน-2% หมายถึง Samut Prakan
6. สีเขียว-9% หมายถึง Rayong
7, รวมข้อมูลจังหวัดอื่นที่เหลือ-41% ซึ่งมี Pie อีกอันเพื่ออธิบายรายละเอียดส่วนที่เหลือ Krabi / Samut Songkhram / Ratchaburi / Chiang Rai 

นอกจาก Pie of Pie Chart แล้วยังสามารถทำเป็น Bar of Pie Chart ดังรูปที่ 8

รูปที่ 8 แสดงตัวอย่างแผนภูมิแบบ Bar of Pie Chart

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

Read More

7 ประเภทแผนภูมิคอลัมน์ (Column Chart) ใน Excel

บทความก่อนหน้านี้ เรานำเสนอความรู้พื้นฐานเกี่ยวกับการสร้างแผนภูมิในแบบต่างๆ ไม่ว่าจะเป็นแผนภูมิเส้น (Line Chart) แผนภูมิคอลัมน์ (Column Chart) แผนภูมิวงกลม (Pie/Doughnut chart) แผนภูมิแท่ง (Bar Chart) แผนภูมิพื้นที่ (Area Chart)  แผนภูมิจุด (Scatter/Bubble Chart) เป็นต้น คุณสามารถไปทบทวนได้ที่  "ความรู้พื้นฐานเกี่ยวกับแผนภูมิ (Chart) ต่างๆ ใน Excel"
ในบทความนี้ เราจะอธิบายแผนภูมิคอลัมน์ ให้ละเอียดมากขึ้น เพื่อให้คุณนำเสนอมุมมองแผนภูมิคอลัมน์ ให้เหมาะสมกับข้อมูลเดิมนั้นเอง

แผนภูมิคอลัมน์ (Column Chart) 
เป็นแผนภูมิที่ใช้แสดงชุดข้อมูลอย่างน้อย 1 ชุด หรือ ใช้เปรียบเทียบข้อมูลหลายชุด ดังตารางข้างล่างนี้ เป็นข้อมูล 2 ชุด ที่จะนำมาเปรียบเทียบกันโดยใช้แผนภูมิคอลัมน์  

ตารางที่ 1 แสดงข้อมูลที่นำมาสร้างแผนภูมิ

ตารางที่ 1 สามารถนำข้อมูลที่มากกว่า 2 ชุด คือ รายได้ของเดือนหนึ่ง (Income of m1) และสอง (Income of m2) มาเปรียบเทียบกัน โดยแนวตั้ง (vertical axis : Y-axis) มักจะแสดงเป็นตัวเลข ส่วนแนวนอน (horizontal axis : X-axis) ก็จะแสดงเป็นข้อมูลประเภทอื่นๆ ดังรูปที่ 2

รูปที่ 2 ลักษณะของแผนภูมิคอลัมน์

ลักษณะของแผนภูมิคอลัมน์ สามารถแบ่งได้ 2 ประเภทใหญ่ คือ
แผนภูมิคอลัมน์แบบคลัสเตอร์ และ แบบซ้อนกัน ซึ่งแต่ละประเภทยังแบ่งออกเป็น 7 แบบย่อย ดังนี้

2.1. แผนภูมิคอลัมน์แบบคลัสเตอร์ (Clustered Column) เป็นการเปรียบเทียบข้อมูลชุดเดียว/หลายชุด โดยคอลัมน์จะเชื่อมต่อติดกันเป็นกลุ่มเป็นก้อน ดังรูปที่ 3

รูปที่ 3 แสดงแผนภูมิคอลัมน์แบบคลัสเตอร์

2.2. แผนภูมิคอลัมน์แบบ 3-D คลัสเตอร์ (3-D Clustered Column) เป็นการเปรียบเทียบข้อมูลชุดเดียว/หลายชุด โดยคอลัมน์เชื่อมกันเป็นกลุ่มเป็นก้อนแบบ 3 มิติ ดังรูปที่ 2

2.3. แผนภูมิคอลัมน์แบบ 3-D (3-D Column) เป็นการเปรียบเทียบข้อมูลชุดเดียว/หลายชุด โดยแต่ละชุดข้อมูลเป็นแผนภูมิคอลัมน์แบบ 3 มิติ ดังรูปที่ 4

รูปที่ 4 แสดงแผนภูมิคอลัมน์แบบ 3-D

2.4. แผนภูมิคอลัมน์ซ้อนกัน (Stacked Column) เหมาะกับการใช้เปรียบเทียบข้อมูลหลายชุด โดยแสดงในลักษณะคอลัมน์ที่ซ้อนกัน ดังรูปที่ 5

รูปที่ 5 แสดงแผนภูมิคอลัมน์ซ้อนกัน 

2.5. แผนภูมิคอลัมน์แบบ 3-D ซ้อนกัน (3-D Stacked Column) เหมาะกับการใช้เปรียบเทียบข้อมูลหลายชุด โดยมีลักษณะคอลัมน์ซ้อนกัน ดังรูปที่ 6

รูปที่ 6 แสดงแผนภูมิคอลัมน์แบบ 3-D ซ้อนกัน

2.6. แผนภูมิคอลัมน์ซ้อนกันแบบความสูงเท่ากัน (100% Stacked Column) เหมากับใช้เปรียบเทียบข้อมูลหลายชุด โดยแสดงในลักษณะคอลัมน์ที่ซ้อนกัน ขนาดเท่ากัน ดังรูปที่ 7

รูปที่ 7 แสดงแผนภูมิคอลัมน์ซ้อนกันแบบความสูงเท่ากัน

2.7. แผนภูมิคอลัมน์ 3-D ซ้อนกันแบบความสูงเท่ากัน  (3-D 100% Stacked Column) ดังรูปที่ 8

รูปที่ 8 แสดงแผนภูมิคอลัมน์แบบ 3-D ที่ซ้อนกันแล้วความสูงเต็ม 100%

ขั้นตอนการสร้าง
1. เลือกข้อมูลในตารางที่ต้องการนำมาแสดงแผนภูมิคอลัมน์ 
2. คลิกเมนู Insert ดังรูปที่ 9
3. เลือก Chart ribbon group หรือ Alt+F1 ดังรูปที่ 9

รูปที่ 9 แสดงขั้นตอนการสร้างแผนภูมิ

 4. เลือก Column Chart ที่ต้องการ ดังรูปที่ 10

รูปที่ 10 แสดงเมนู Column Chart

Read More

ความรู้พื้นฐานเกี่ยวกับแผนภูมิ (Chart) ต่างๆ ใน Excel

บทความนี้ เราขอนำเสนอความรู้พื้นฐานเกี่ยวกับการสร้างแผนภูมิในแบบต่างๆ ไม่ว่าจะเป็นแผนภูมิเส้น (Line Chart) แผนภูมิคอลัมน์ (Column Chart) แผนภูมิวงกลม (Pie/Doughnut chart) แผนภูมิแท่ง (Bar Chart) แผนภูมิพื้นที่ (Area Chart)  แผนภูมิจุด (Scatter/Bubble Chart) เป็นต้น

แผนภูมิ (Chart) คือ การแสดงข้อมูลตัวเลขแบบกราฟ เพื่อให้คนที่ไม่เคยเห็นข้อมูลตัวเลขเหล่านี้ สามารถทำความเข้าใจได้ง่าย และเร็วที่สุด 
โดยแผนภูมิส่วนมาก เช่น แผนภูมิคอลัมน์ (Column Chart) แผนภูมิแท่ง (Bar Chart) คุณๆ สามารถใส่ค่าข้อมูลในแถวหรือคอลัมน์ใส่ในแผนภูมิ ส่วนบางแผนภูมิ เช่น แผนภูมิวงกลม (Pie/Doughnut chart) คุณต้องทำการจัดเรียงข้อมูลเฉพาะก่อน

ประเภทแผนภูมิใน Excel
1. แผนภูมิเส้น (Line Chart) 
เป็นการแสดงแนวโน้มของข้อมูลอย่างน้อย 1 ชุด หรือใช้แสดงแนวโน้มของข้อมูลหลายชุดได้ แต่ละชุดข้อมูลจะแยกโดยใช้สีที่ต่างกัน โดยแนวตั้ง (vertical axis : Y-axis) มักจะแสดงเป็นตัวเลข ส่วนแนวนอน (horizontal axis : X-axis) ใช้แสดงข้อมูลประเภทอื่นๆ 
2. แผนภูมิคอลัมน์ (Column Chart) 
เป็นการแสดงข้อมูลเปรียบเทียบชุดข้อมูลอย่างน้อย 1 ชุด หรือ ใช้เปรียบเทียบข้อมูลหลายชุดได้ แต่ละชุดข้อมูลจะแยกโดยใช้สีที่ต่างกัน โดยแนวตั้ง (vertical axis : Y-axis) มักจะแสดงเป็นตัวเลข ส่วนแนวนอน (horizontal axis : X-axis) ก็จะแสดงเป็นข้อมูลประเภทอื่นๆ 
3. แผนภูมิวงกลม (Pie/Doughnut chart) 
เป็นการแสดงข้อมูลเพียง 1 ชุด ที่ถูกแบ่งเป็นสัดส่วนไว้แล้ว และนำมาแสดงในรูปแบบวงกลม 
4. แผนภูมิแท่ง (Bar Chart) 
เป็นการแสดงข้อมูลแท่งที่นอน ซึ่งแผนภูมิแบบนี้เหมาะสมที่จะแสดงหรือเปรียบเทียบชุดข้อมูลจำนวนมาก ได้ดีกว่าแผนภูมิแบบอื่น
5. แผนภูมิพื้นที่ (Area Chart) 
เป็นการแสดงแนวโน้มของข้อมูล คล้ายกับแผนภูมิเส้น ต่างกันเพียงพื้นที่ใต้เส้น
6. แผนภูมิจุด (Scatter/Bubble Chart) 
จุดประสงค์ของแผนภูมินี้ เป็นการแสดงค่า 2 ชุดมาเปรียบเทียบกัน ซึ่งแผนภูมิจุดนี้ จะคล้ายกับแผนภูมิเส้น โดยจุด (Scatter) ที่โปรแกรม Plot จะแสดงว่าตัวแปรหนึ่งตัวได้รับอิทธิพลจากตัวแปรอื่นมาก-น้อยเพียงใด ซึ่งความสัมพันธ์ระหว่างตัวแปรทั้ง 2 ตัวถูกเรียกว่า "ความสัมพันธ์" (correlation)
นอกจากนี้ ยังมีแผนภูมิอื่นๆ ในโปรแกรม Excel บางเวอร์ชั่น เช่น แผนภูมิคลัง (Stock Chart) แผนภูมิพื้นผิว (Surface Chart) แผนภูมิเรดาร์ (Radar Chart) และ แผนภูมิผสม (Combo Chart) ซึ่งแผนภูมิเหล่านี้ ไม่ค่อยมีคนนิยมใช้กัน

4 ขั้นตอนการสร้างกราฟแบบต่าง ๆ ด้วย Excel
1. ทำการ High Light ข้อมูล รวมทั้งชื่อแถวและชื่อคอลัมน์ ที่ต้องการมาสร้างแผนภูมิ (ซึ่งทั้งชื่อแถวและชื่อคอลัมน์ ไม่เกี่ยวกับชื่อของแผนภูมิ)
2. คลิกเมนู Insert tab 
3. เลือก Chart ribbon group หรือ Alt+F1
4. เลือก Chart ที่ต้องการ เช่น แผนภูมิเส้น (Line Chart) แผนภูมิคอลัมน์ (Column Chart) แผนภูมิแท่ง (Bar Chart) เป็นต้น

ส่วนประกอบของแผนภูมิ
ตอนนี้เราควรมาทำความรู้จักส่วนประกอบในแผนภูมิ เพื่อเวลาที่คุณๆ ต้องมาจัดการ/เปลี่ยนแปลง รูปแบบของแผนภูมิที่คุณเลือกไว้ภายหลังได้สะดวกขึ้น จากรูปที่ 1

รูปที่ 1 แสดงส่วนประกอบของแผนภูมิ

1. พื้นหลังของแผนภูมิ (chart area)
2. เค้าโครงของแผนภูมิ (plot area)
3. ข้อมูลที่แสดงในแผนภูมิ (the data points of the data series)
4. แกนแนวนอน (horizontal axis) และแกนแนวตั้ง (vertical axis)
5. คำนิยามที่ใช้แยกชุดข้อมูลในแผนภูมิ (legend)
6. ชื่อที่ใช้อธิบายแกนแนวนอน (Branch) ชื่อที่ใช้อธิบายแกนแนวตั้ง (Income) 
7. ชื่อของแผนภูมิ คุณสามารถตั้งชื่อแผนภูมิได้เอง
8. คำอธิบาย (data label) ใช้อธิบายรายละเอียดของรายการข้อมูล ซึ่งคำอธิบายจะปรากฏเมื่อวางเมาส์ลงบนรายการข้อมูลในแผนภูมินั้นเอง จากรูปที่ 1 คำอธิบายจะแสดงรายได้ที่สาขา Krabi ต่อเดือนเป็น 150,000 บาท

การปรับแต่งแผนภูมิ (Customizing Excel Charts)
1. เพิ่ม/ปรับแต่งส่วนประกอบของแผนภูมิ (chart elements) หรือโครงสร้างของแผนภูมิ (chart layout) และ รูปแบบของแผนภูมิ (chart style) ให้เหมือนกับมีผู้เชี่ยวชาญสร้างให้ 

โดยการเปลี่ยนส่วนประกอบของแผนภูมิ เช่น พื้นหลังของแผนภูมิ (chart area), เค้าโครงของแผนภูมิ (plot area), ข้อมูลที่แสดงในแผนภูมิ (data series), คำอธิบายในแผนภูมิ (legend of the chart)

ส่วนของโครงสร้างของแผนภูมิหรือส่วนประกอบที่เฉพาะเจาะจง เช่น ชื่อแผนภูมิ (titles), คำอธิบายของแผนภูมิ  (legend), ข้อมูลตาราง (data table)

ส่วนรูปแบบของแผนภูมิ เช่น เปลี่ยนสีพื้นหลัง ตัวหนังสือ เป็นต้น เพื่อให้แผนภูมิมีรูปแบบเหมือนกับองค์กรหรือหน่วยงานของคุณ 

2. ทำแผนภูมิให้มีจุดน่าสนใจอื่นๆ ไม่ว่าจะเป็น ลัญลักษณ์รูปแบบพิเศษ, WordArt, ใส่รูปแบบต่างๆให้กับตัวอักษรหรือตัวเลจ เช่น ใส่เงา (shadow), สะท้อน (reflection), ใส่แสง (glow), ปรับให้เอียง (bevel), ใส่แบบสามมิติ (3-D rotation) เป็นต้น  

ตอนนี้คุณน่าจะเข้าใจพื้นฐานของแผนภูมิในโปรแกรม Excel กันบ้างต่อไปเราจะไปทำความรู้จักแผนภูมิแต่ละประเภทให้มากขึ้น เพื่อให้คุณสามารถเลือกว่าจะนำเสนอข้อมูลในแผนภูมิแบบใดที่จะดีมากที่สุด

อ้างอิง

https://www.keynotesupport.com/excel-basics/excel-chart-types.shtml

https://support.office.com/en-us/article/Create-a-chart-from-start-to-finish-0baf399e-dd61-4e18-8a73-b3fd5d5680c2

https://www.keynotesupport.com/excel-basics/excel-charts-beginners.shtml

Read More

INDEX MATCH Functions ดีกว่า VLOOKUP Function อย่างไร

บทความก่อนหน้านี้ เรานำเสนอทั้งการทำงานและตัวอย่างของ Vlookup Function / Index Function และ Match Function ซึ่งถ้าคุณสามารถทบทวน vlookup Function ได้ที่ "การค้นหาข้อมูลด้วย vLookUp จาก Excel" ส่วน Index Function หรือ Match Function ได้ที่ "ผสานคุณสมบัติของ Index Function กับ Match Function เพื่อแทนคำสั่ง vlookup Function" 

ในบทความนี้ เรามาดูข้อดีต่างๆ ของการนำ Index&Match มาใช้ร่วมกัน แทน Vlookup ดีกว่า 

ข้อดีของการใช้ INDEX ร่วมกับ MATCH แทนการใช้ VLOOKUP 
1. สามารถแทรกหรือลบคอลัมน์ได้ภายหลัง เมื่อคุณใช้ Index Match ในการทำงานแทน Vlookup คุณสามารถแทรกคอลัมน์ระหว่าง(คอลัมน์เริ่มต้นของตาราง จนถึึง คอลัมน์ที่คุณนำมาใช้อ้างอิงในสูตร) แต่ ✋ถ้าคุณใช้ Vlookup ในสูตร คุณจะไม่สามารถแทรกคอลัมน์ได้ เพราะสูตรที่คุณเขียนไว้มันจะ Error ทันที 

รูปที่ 1 ตัวอย่างที่ใช้เปรียบเทียบระหว่าง Index&Match และ Vlookup

ตัวอย่าง การค้นหาข้อมูลในเซลล์ด้วย Index&Match จากรูปที่ 1 เราได้เขียนสูตรด้วย Index&Match ในคอลัมน์ C และ D เพื่อค้นหาข้อมูลสาขาและรายได้ของแต่ละร้าน จากตารางสีฟ้า 
ได้สูตรในคอลัมน์ C ดังนี้ 

=INDEX($F$2:$I$14,MATCH($A2,$F$2:$F$14,0),MATCH($C$1,$F$1:$I$1,0))

จากสูตรข้างบนนี้ สามารถทำงานได้คำตอบเดียวกับ vlookup ได้ โดยสูตรนี้ ประกอบด้วย Index&Match Function ซึ่งส่วนของ index function มี 3 arguments (แบ่งด้วยสีเป็น 3 สีเพื่อให้คุณสังเกตได้ง่ายขึ้น คือ ส้ม น้ำเงิน ม่วง) 

ในส่วนสีน้ำเงินแทน argument ตัวที่ 2 ของ Index Function ด้วยการใช้ฟังก์ชั่น Match Function เข้ามา ซึ่งเขียนให้นำค่าใน A2 (ค่าในเซลล์ คือ s001) มาค้นหาในช่วง F2:F14 (ค่าในเซลล์ คือ s014, s009, s002, s001, s008, s003, s015, s012, s004, s005, s006, s013, s010) และค้นหาแบบต้องสะกดตรงกันทั้งหมด ผลลัพธ์ของฟังก์ชั่นนี้ คือ 4

ในส่วนสีม่วงแทน argument ตัวที่ 3 ของ Index Function ด้วยการใช้ฟังก์ชั่น Match Function เข้ามา ซึ่งเขียนให้นำค่าใน C1 (ค่าในเซลล์ คือ Branch) มาค้นหาในช่วง F1:I1 (ค่าในเซลล์ คือ idShop, Branch, income, อัตราภาษี) และค้นหาแบบต้องสะกดตรงกันทั้งหมด ผลลัพธ์ของฟังก์ชั่นนี้ คือ 2 

ดังนั้น ช่วงค้นหา F2:I14 ใน Index Function จะมองหาข้อมูลในแถว 4 และคอลัมน์ที่ 2 ดังรูปที่ 2

รูปที่ 2 แสดงผลลัพธ์ของการใช้ Index&Match 

ได้สูตรในคอลัมน์ D ดังนี้

=INDEX($F$2:$I$14,MATCH($A2,$F$2:$F$14,0),MATCH($D$1,$F$1:$I$1,0))

จากสูตรนี้ สามารถทำงานได้คำตอบเหมือนกับ vlookup ได้ โดยสูตรนี้ ประกอบด้วย Index&Match Function ซึ่งส่วนของ index function มี 3 arguments (แบ่งด้วยสีเป็น 3 สีเพื่อให้คุณสังเกตได้ง่ายขึ้น คือ ส้ม น้ำเงิน ม่วง) 

ในส่วนสีน้ำเงินแทน argument ตัวที่ 2 เขียนเหมือน argument ตัวที่ 2 ในคอลัมน์ C


ในส่วนสีม่วงแทน argument ตัวที่ 3 ของ Index Function ด้วยการใช้ฟังก์ชั่น Match Function เข้ามา ซึ่งเขียนให้นำค่าใน C1 (ค่าในเซลล์ คือ Income) มาค้นหาในช่วง F1:I1 (ค่าในเซลล์ คือ idShop, Branch, income, อัตราภาษี) และค้นหาแบบต้องสะกดตรงกันทั้งหมด ผลลัพธ์ของฟังก์ชั่นนี้ คือ 3

ดังนั้น ช่วงค้นหา F2:I14 ใน Index Function จะมองหาข้อมูลในแถว 4 และคอลัมน์ที่ 3 ดังรูปที่ 2

หลังจากนั้น ทำการแทรกคอลัมน์ในตารางที่ใช้สำหรับค้นหา (ตารางสีฟ้า) ดังรูปที่ 3

รูปที่ 3 แสดงตัวอย่างเมื่อแทรกคอลัมน์ในตารางที่ใช้สำหรับค้นหา (ตารางสีฟ้า)

คุณจะเห็นได้ว่าสูตรในคอลัมน์ C มีการเปลี่ยนเซลล์อ้างอิงทำให้คำตอบของสูตรที่ใช้ Index&Match Function ยังคงได้คำตอบเดิม ทำให้เราไม่ต้องแก้ไขสูตรนั้นเอง ซึ่งถ้าคุณทำการค้นหาคอลัมน์ C และ D ด้วย Vlookup Function เมื่อคุณแทรกคอลัมน์แบบเดียวกับรูปที่ 3 คุณต้องกลับมาแก้ไขสูตรด้วย 

✻✼✽✻✼✽✻✼✽

2. สามารถค้นหาข้อมูลจากทิศทางใดก็ได้ เมื่อคุณต้องการค้นหาข้อมูลที่อยู่ทางด้านซ้ายของตารางอ้างอิง คุณจะไม่สามารถใช้ Vlookup ค้นหาได้ ดังนั้น ต้องใช้ Index&Match เข้ามาแทน

ตัวอย่าง จากรูปที่ 1 ที่ตารางสีขาว ให้ทำการค้นหา "ชื่อร้าน" (Name Shop) จากสาขา (Branch) "Lampang" ซึ่งแบบนี้จะใช้ vlookup ไม่ได้ เพราะชื่อร้าน (คอลัมน์ที่ค้นหา) อยู่ทางซ้ายของสาขา (คอลัมน์อ้างอิง) เมื่อคุณเขียนสูตรด้วย Index&Match สามารถเขียนได้ ดังนี้

=INDEX($A$2:$D$14,MATCH("Lampang",C2:C14,0),MATCH("NameShop",A1:D1,0))

จากสูตรนี้ ส่วนของ index function ประกอบด้วย 3 arguments (แบ่งด้วยสีเป็น 3 สี เพื่อให้คุณสังเกตได้ง่ายขึ้น คือ ส้ม น้ำเงิน ม่วง) 
โดย argument ตัวที่ 1 (สีส้ม) เป็นการกำหนดช่วงหรือตารางที่ทำการค้นหาข้อมูลมาแสดง

ส่วน argument ตัวที่ 2 (สีน้ำเงิน) เป็นการกำหนดเลขแถวให้กับ index function ค้นหา โดยใช้ match function มาหาเลขของเซลล์ที่เก็บ Lampang ไว้ ผลลัมน์ที่ได้ คือ 6

ส่วน argument ตัวที่ 3 (สีม่วง) เป็นการกำหนดเลขคอลัมน์ให้กับ index function ค้นหา โดยใช้ match function มาหาเลขของเซลล์ที่เก็บ NameShop ไว้ ผลลัมน์ที่ได้ คือ 2

✻✼✽✻✼✽✻✼✽

3. สามารถค้นหาค่าประมาณการได้ ในฟังก์ชั่น vlookup มีส่วนของการกำหนดวิธีการค้นหาได้ 2 แบบ คือ ค่าที่ตรงกัน (Exact Match) และ ค่าโดยประมาณ (Approximate Match) ซึ่งในตัวอย่างที่ผ่านมา เราได้อธิบายข้อดีที่ใช้วิธีการค้นหาแบบค่าที่ตรงกัน (Exact Match) แต่ในข้อดีข้อนี้ เราจะอธิบายข้อดีที่ใช้วิธีการค้นหาค่าโดยประมาณ (Approximate Match) นั้นเอง

ตัวอย่าง ค้นหาเกรด โดยให้วิธีการค้นหาเป็นแบบประมาณ (Approximate Match) ดังรูปที่ 4

รูปที่ 4 แสดงตัวอย่างการหาเกรดด้วย Index&Match

จากรูปนี้ เราเรียงคะแนนจากมากลงมาน้อย และคะแนนสูงสุดเราใส่ค่าที่เป็นคะแนนมากที่สุดที่ผู้สอบสามารถจะทำได้ เขียนสูตรได้ ดังนี้ 

=INDEX($B$2:$B$9,MATCH("99",$A$2:$A$9,-1))

จากสูตรนี้เราใช้ Index Function ที่มี 2 arguments (แบ่งด้วยสี 2 สี ตามลำดับ) โดย argument ที่ 1 (สีส้ม) ใส่ช่วงที่ต้องการค้นหา ซึ่งในที่นี้ต้องการค้นหาเกรด เราจึงใส่ B2:B9

ส่วน argument ที่ 2 เราใช้ Match Function เข้ามาช่วยในการค้นหาเลขของเซลล์ 

โดยใน Match Function มี 3 arguments ซึ่งใน argument ที่ 1 เรากำหนด "99" ลงไป, ใน argument ที่ 2 เรากำหนดช่วงที่จะเข้าไปค้นหา "A2:A9", ใน argument ที่ 3 เรากำหนดวิธีในการค้นหาเป็นแบบประมาณ (Approximate Match) โดยในตัวอย่างนี้ กำหนดเป็น -1 ซึ่งหมายถึง หาค่าที่มากกว่าหรือเท่ากับ นั้นเอง

ปล. ถ้าในรูปที่ 4 เป็นการเรียงคะแนนจากน้อยไปมาก การเขียนสูตรต้องกำหนดวิธีการค้นหาใน Match Function เป็น "1" หมายถึง หาค่าที่น้อยกว่าหรือเท่ากับ นั้นเอง

✻✼✽✻✼✽✻✼✽

4. ไม่ต้องนับคอลัมน์เพื่อระบุเลขคอลัมน์ที่ต้องดึงข้อมูลมาแสดงอีก เมื่อคุณใช้ Index&Match แทน Vlookup คุณไม่ต้องสนใจว่าค่าที่เราค้นหานั้นอยู่เลขคอลัมน์ใดอีก เพราะคุณสมบัติของ Match Function เข้ามาช่วยคุณแล้ว 

Read More

ผสานคุณสมบัติของ Index Function กับ Match Function เพื่อแทนคำสั่ง vlookup Function

บทความนี้ จะอธิบายข้อดีของ Index และ Match ที่แก้ไข จุดด้อยของ Vlookup ได้ หากใครยังไม่รู้จักการทำงานพื้นฐานของฟังก์ชั่น Vlookup สามารถกลับไปอ่าน "การค้นหาข้อมูลด้วย vLookUp จาก Excel" และ "HLookup ช่วยค้นหาข้อมูลได้ต่างกับ VLookup อย่างไร" หรือต้องการทบทวนฟังก์ชั่น Match ก็สามารถอ่าน "ค้นหาเลขคอลัมน์ หรือเลขแถว ของสิ่งที่ค้นหาด้วย Match Function" เพื่อทบทวนได้ค่ะ

INDEX Function 
        หมายถึง การค้นหาค่า จากตารางอ้างอิง ตามแถวหรือคอลัมน์ที่กำหนด หรือ ใช้แทน VLOOKUP ได้ 

ไวยากรณ์ =INDEX(array, row_num, [column_num])

หลักการใช้ฟังก์ชั่น INDEX

• Array คือ ช่วงของเซลล์ที่ต้องการค้นหา
• row_num คือ เลขแถวใน Array 
• [column_num] คือ เลขคอลัมน์ใน Array 

เมื่อนำ INDEX มาใช้ร่วมกับ MATCH จะทำให้สามารถค้นหาข้อมูลได้ โดยการหาตำแหน่งด้วย MATCH และ นำมาแสดงด้วย INDEX ซึ่งจะเทียบได้กับทั้ง 2 ฟังก์ชั่นนี้ ทำหน้าที่แทน VLOOKUP นั้นเอง

ตัวอย่างการใช้ฟังก์ชั่น INDEX
        จากบทความนี้ เราจะใช้ตัวอย่างเดียวกับฟังก์ชั่น VLOOKUP และ MATCH เพื่อให้คุณ ๆ เห็นความแตกต่างระหว่างทั้ง 3 ฟังก์ชั่น 

รูปที่ 1 ตารางที่ใช้แสดงตัวอย่างการนำฟังก์ชั่น INDEX และ MATCH มาทำงาน

จากรูปที่ 1 ถ้าเราใช้ฟังก์ชั่น INDEX ในการค้นหา จะคืนค่าภายในเซลล์นั้นออกมา เช่น ต้องการค้นแถวที่ 10 และคอลัมน์ที่ 2 ก็ต้องเขียนว่า =INDEX($A$1:$D$14,10,2) ผลลัพธ์ที่ได้คือ "Charming" นั้นเอง

ข้อควรระวัง !!! 
ถ้าคุณเขียน =INDEX($A$2:$D$14,10,2) จะเห็นว่าช่วงของเซลล์ที่ต้องการค้นหาเริ่มต้นไม่เหมือนกัน (A2:D14) ทำให้ผลลัพธ์ที่ได้ผิดไปจากเดิม คือ "Cupid" 

หลักการใช้ฟังก์ชั่น Index นี้มันดูง่าย ๆ ไม่ซับซ้อนอะไรเลยถูกไหมคะ 😉😉😊😊

แต่ในความเป็นจริง คุณไม่มีทางรู้ว่า Data ที่กำลังหาอยู่ในแถวหรือคอลัมน์ที่เท่าไรแน่นอน ดังนั้น เราจึงควรทำความรู้จักกับ คุณลักษณะของฟังก์ชั่น MATCH ไว้ด้วย 

คุณสามารถกลับไปทบทวนหลักการของ Match Function ได้ที่บทความ "ค้นหาเลขคอลัมน์ หรือเลขแถว ของสิ่งที่ค้นหาด้วย Match Function" 

ตัวอย่างการใช้ฟังก์ชั่น INDEX ร่วมกับ MATCH แบบที่ 1
        ต้องการค้นหารายได้ (Income) ของร้าน Cupid โดยให้คุณเขียนสูตรจากทั้ง 2 ฟังก์ชั่น คือ Index และ Match ร่วมกัน เพื่อค้นหาค่า (Value) แทนฟังก์ชั่น VLOOKUP ซึ่งสามารถเขียนได้ ดังนี้  

=INDEX($A$2:$D$14,MATCH("Cupid",$B$2:$B$14,0),MATCH("Income",$A$1:$D$1,0))

จากสูตรด้านบนนี้ ถ้าคุณสังเกตดีๆ เรายังใช้ฟังก์ชั่น Index อยู่นะค่ะ แต่ส่วนของ Argument ที่ให้ระบุเลขแถว และเลขคอลัมน์ ของ Index เราได้ใช้ฟังก์ชั่น Match เข้ามาช่วยนั้นเอง 

ซึ่ง MATCH("Cupid",$B$2:$B$14,0) ใช้ในการค้นหาเลขลำดับของข้อมูล โดยเมื่ออ่านความหมายของสูตรนี้ คือ ค้นหาคำว่า Cupid จากลำดับ B2:B14 (แถว) และวิธีหา Cupid ต้องคำตรงกัน ดังนั้น ผลลัพธ์ คือ แถวลำดับที่10 (จุดสังเกต ใครรู้สึกว่าต้องเป็น ลำดับที่ 11 ไหม ซึ่งจะไม่ถูกต้อง เพราะ สูตรที่เขียนกำหนดช่วงเป็น B2:B14 เมื่อนับแต่ละแถวลงมา Cupid จะอยู่ลำดับที่ 10 พอดีค่ะ)

ปล. ให้คุณนึกว่าถ้ามีข้อมูลใน Excel จำนวนมากเป็นพัน ๆ แถว/คอลัมน์ เราคงไม่รู้ว่า สิ่งที่เราค้นหามันอยู่ในเลขแถวหรือเลขคอลัมน์อะไรแน่นอน ดังนั้น ฟังก์ชั่น Match จึงกลายมาเป็นฮีโรของ Index ทันที 

ตัวอย่างการใช้ฟังก์ชั่น Index ร่วมกับ Match แบบที่ 2 
        เราจะใช้โจทย์เดิมตามแบบที่ 1 แต่คุณ ๆ สามารถเขียนได้ ดังนี้

=INDEX($D$2:$D$14,MATCH("Cupid",$B$2:$B$14,0))

ในตัวอย่างแบบที่ 2 นี้ เราใช้ Argument เพียงระบุเลขแถวอย่างเดียวได้ และ ช่วงของเซลล์ที่ต้องการค้นหา ให้ระบุช่วงของสิ่งที่ต้องการให้แสดงผลลัพธ์ นั้นก็คือ คอลัมน์รายได้ (Income) นั้นเอง 

ปล. ทั้งตัวอย่างแบบที่ 1-2 จะได้ผลลัพธ์เหมือนกัน คือ 150,000 บาท เหมือนกันเลย

Read More

ค้นหาเลขคอลัมน์ หรือเลขแถว ของสิ่งที่ค้นหาด้วย Match Function

บทความนี้ จะอธิบายการใช้ฟังก์ชั่น Match และแสดงตัวอย่างการประยุกต์ใช้ระหว่าง Match Function ร่วมกับ Vlookup Function

ฟังก์ชั่น Match
        เป็นฟังก์ชั่นหาเลขลำดับของข้อมูล ซึ่งหลักการคล้ายกับ VLOOKUP (หาสิ่งที่เราสนใจ) โดยที่ MATCH จะ ไม่ แสดงค่า (Value) จากตาราง แต่มันทำได้เพียงหาเลขลำดับของสิ่งที่ค้นหาว่าอยู่ในแถว (Row) หรือคอลัมน์ (Column) อะไรเท่านั้น 

ไวยาการณ์ =MATCH(lookup_value, lookup_array, [match_type])

หลักการใช้ฟังก์ชั่น Match

• lookup_value คือ ค่าที่ต้องการหา
• lookup_array คือ ลำดับคำที่ค้นหา ว่าอยู่ในแถวหรือคอลัมน์อะไร (เลือกได้แค่แถว หรือคอลัมน์ เท่านั้น จะเลือกทั้งคู่ไม่ได้)
• [match_type] คือ การเลือกประเภทการค้นหา ซึ่งมี 3 ประเภท คือ
• 0 : เป็นการหาค่าที่ต้องเหมือนกันทั้งหมด (เหมือนกับ VLOOKUP แบบ Exact Match) แต่ถ้าเจอเหมือนกันมากกว่า 1 ตัว โปรแกรมจะแสดงตัวแรก
• 1 หรือไม่ต้องใส่ค่า : ควรทำการเรียงข้อมูล lookup_array จากน้อยไปมากก่อน ซึ่งหลักการทำงาน จะหาค่าที่มากกว่า ค่าที่กำลังค้นหา แล้วแสดงค่าตัวก่อน 1 ช่อง 
• -1 : ควรทำการเรียงข้อมูล lookup_array จากมากไปน้อยก่อน ซึ่งหลักการทำงาน จะหาค่าที่น้อยกว่า ค่าที่กำลังค้นหา แล้วแสดงค่าตัวก่อน 1 ช่อง

ตัวอย่างการใช้ฟังก์ชั่น Match

เราจะใช้ตารางเดียวกับบทความ VLOOKUP เพื่อให้คุณ ๆ เห็นถึงความต่างของฟังก์ชั่น MATCH และ VLOOKUP 

รูปที่ 1 ตารางใช้แสดงผลการค้นหาด้วย MATCH

จากรูปที่ 1 สามารถหาลำดับคอลัมน์หรือแถว ของสิ่งที่ต้องการค้นหา ดังนี้

• การหาร้านค้าที่ชื่อ Ooh la la สามารถเขียนสูตร (หาแถว) ดังนี้ 

=MATCH("Ooh la la",B1:B14,0) 

หมายถึง ใช้คำสั่ง Match ทำการค้นหาชื่อร้าน Ooh la la โดยมองหาที่ช่วงของคอลัมน์ B1:B14 และต้องค้นหาโดยคำต้องตรงกันทั้งหมด

ผลลัพธ์ของสูตรนี้ คือ 13

• การหาว่ารหัสร้านค้า S009 สามารถเขียนสูตร (หาแถว) ดังนี้

=MATCH(A6,A1:A14,0)

หมายถึง ใช้คำสั่ง Match ทำการค้นหารหัสร้านในเซลล์ A6 โดยมองหาที่ช่วงของคอลัมน์ A1:A14 และต้องค้นหาโดยคำต้องตรงกันทั้งหมดผลลัพธ์ของสูตรนี้ คือ 6

• การหาคำว่า "Nameshop" สามารถเขียนสูตร (หาคอลัมน์) ดังนี้

=MATCH("nameshop",A1:D1,0)

หมายถึง ใช้คำสั่ง Match ทำการค้นหา "nameshop" โดยมองหาที่ช่วงของคอลัมน์ A1:D1 และต้องค้นหาโดยคำต้องตรงกันทั้งหมดผลลัพธ์ของสูตรนี้ คือ 2

• การหาอัตราภาษี 500,000 บาท สามารถเขียนสูตร ดังนี้

=MATCH(D2,D1:D14,1)

หมายถึง ใช้คำสั่ง Match ทำการค้นหาอัตราภาษี 500,000 บาท โดยมองหาที่ช่วงของคอลัมน์ D1:D14 และต้องค้นหาโดยเรียงข้อมูลก่อน 
ผลลัพธ์ของสูตรนี้ คือ 14

นอกจาก คำสั่ง Match สามารถทำให้เราทราบคอลัมน์ หรือแถว ของสิ่งที่เราค้นหา ซึ่งคุณ ๆ สามารถนำสูตร Match นี้ไปใช้รวมกับสูตรอื่น เช่น VLOOKUP 

ปล. ใครยังไม่เข้าใจการทำงานของ VLOOKUP Function สามารถเข้าไปอ่านทบทวนได้ที่ "การค้นหาข้อมูลด้วย vLookUp จาก Excel"

ตัวอย่างการใช้ฟังก์ชั่น Match ร่วมกับฟังก์ชั่น Vlookup

จากรููปที่ 1 จะแสดงวิธีการนำทั้ง 2 ฟังก์ชั่นมาค้นหาข้อมูลได้อย่างอย่าง แต่คุณอย่าพึ่งคิดว่า ข้อมูลเพียงแค่นี้ไม่เห็นต้องเขียนสูตรให้ยุ่งยาก 

เราใช้รูปที่ 1 แทนข้อมูลขนาดใหญ่ เพราะถ้าคุณต้องไปเจอทั้งคอลัมน์และแถวมีจำนวนมากๆ ก็สามารถใช้ทั้ง 2 ฟังก์ชั่น มาประยุกต์ เขียนสูตรเพื่อหาข้อมูลได้ง่าย ๆ ดังนี้

การค้นหาเลขคอลัมน์โดยใช้ฟังก์ชั่น match ร่วมกับฟังก์ชั่น vlookup

=VLOOKUP("S013",A1:C14,MATCH("Branch",A1:C1,0),FALSE)

จากคำสั่งที่เขียนด้านบน ส่วนของ Match หมายถึง หาเลขลำดับที่ตรงกับคำว่า "Branch" จากช่วง A1:C1 โดยทำต้องสะกดเหมือนกับคำที่กำลังหา คำตอบของสูตร (MATCH("Branch",A1:C1,0)) นี้ เป็นเลขคอลัมน์ คือ 3  

เมื่อนำมาร่วมกับ vlookup คำตอบของสูตร (=VLOOKUP("S013",A1:C14,MATCH("Branch",A1:C1,0),FALSE)) นี้ เป็น Krabi นั้นเอง

คุณ ๆ คงสงสัยว่า ใครจะอยากรู้ เลขคอลัมน์ หรือแถว ข้อมูล (Data) กันบ้างละ สิ่งที่คนใช้งาน Excel อยากจะรู้ ควรจะเป็น ข้อมูล (Data) ที่อยู่ในเซลล์นั้นๆ ซิ !!!!

คนที่จะอยากรู้ว่าเลขคอลัมน์ หรือเลขแถว ของสิ่งที่ค้นหาเป็นเลขอะไร ก็คือ คนที่เขียนสูตรด้วยฟังก์ชั่น INDEX นั้นเอง 

Read More