ในบทนี้ จะเป็นการนำตัวอย่างสั้น ๆ ของการใช้โปรแกรม Excel ในงานต่าง ๆ ซึ่งจะเป็นแนวทางในการนำไปประยุกต์ใช้กับงานของท่าน
การวิเคราะห์ข้อมูลเกี่ยวกับผู้ตอบแบบสอบถาม
ในการเก็บข้อมูลต่าง ๆ มักจะมีการถามคำถามเกี่ยวกับข้อมูลส่วนตัวของผู้ตอบ เช่น เพศ อายุ รายได้ เป็นต้น การวิเคราะห์หาจำนวนผู้ตอบที่เป็นเพศชาย หรือเพศหญิง สามารถทำได้ง่ายโดยใช้ ฟังก์ชั่น Count และวิธีการจากบทที่แล้ว แต่ถ้าให้ผู้ตอบเติมตัวเลข ในการวิเคราะห์จะมีความซับซ้อนขึ้นบ้าง ดังนี้
สมมติว่า ข้อมูลในการตอบแบบสอบถาม มีดังนี้ จากข้อมูลข้างต้น เราต้องการหาคำตอบ ดังต่อไปนี้
แสดงจำนวนผู้ตอบ จำแนกตามช่วงอายุ
จำนวน ผู้ที่อายุตั้งแต่ 25 ปีขึ้นไป และ มีเงินเดือน 20,000 บาทขึ้นไป
จากข้อมูลข้างต้น เราจะหาจำนวนของผู้ที่มีอายุตั้งแต่ 25 ปี และมีเงินเดือนตั้งแต่ 20,000 บาท ขึ้นไป จะเห็นว่า มีเงื่อนไข 2 เงื่อนไขคือ อายุตั้งแต่ 25 ปีขึ้นไป และอีกเงื่อนไขคือ รายได้ตั้งแต่ 20,000 บาทขึ้นไป เราจะใช้ฟังก์ชั่น DCOUNT เพื่อนับจำนวนที่มีหลายเงื่อนไข รูปแบบของ DCOUNT มีดังนี้
DCOUNT(database,field,criteria)
database คือตารางข้อมูลที่จะใช้ในฟังก์ชั่นนี้ จะระบุเป็นช่วง เช่น A5:D14
field คอร์ลัมข้อมูลที่จะนำมาใช้ในสูตรการคำนวน เริ่มจากคอร์ลัมน์ซ้ายสุด เป็น คอร์ลัมน์ที่ 1 ต้องการให้นับคอร์ลัมน์ใด ก็ระบุเลขที่ของคอร์ลัมน์นั้น criteria เงื่อนไขในการนับ
1. วิธีการใช้สูตรนับจำนวน DCOUNT มีดังนี้
เนื่องจากเงื่อนไขของเรามีสองเงื่อนไข คือ อายุ และเงินเดือน จึงต้องกำหนดเงื่อนไขไว้ใน Cell เสียก่อน เพื่อจะได้เรียกใช้ได้ภายหลัง ในช่อง B1:C2 ให้พิมพ์เงื่อนไข ดังนี้
2. พิมพ์ข้อมูลดังภาพข้างล่างนี้
3. ข้อมูล A4:D14 เป็นตารางข้อมูล ซึ่งจะต้องมีชื่อคอร์ลัมปรากฎอยู่ ในตัวอย่างนี้ คือ A4 ถึง D4
ในช่อง
4. B1:C2 เป็นเงื่อนไขในการนับจำนวน จะเห็นว่า มีชื่อคอร์ลัมน์ และเงื่อนไขในคอร์ลัมน์ จากตัวอย่างนี้ คือ ต้องการให้นับเฉพาะ ในช่องอายุ ให้มีอายุเท่ากับ 25 หรือ มากกว่า 25 หรือ พูดอีกอย่างหนึ่งก็คือ ตั้งแต่ 25 ปี ขึ้นไป ส่วนเงื่อนไขที่2 เป็นเงื่อนไขเกี่ยวกับเงินเดือน ว่า ต้องการเฉพาะ เงินเดือนตั้งแต่ 20,000 บาทขึ้นไปเช่นเดียวกัน จะต้องมีชื่อคอร์ลัมน์ และเงื่อนไขที่ต้องการ (ในที่นี้ ชื่อคอร์ลัมน์ คือ เงินเดือน และเงื่อนไข คือ >=20000)
5. เราจะใส่ผลที่ได้จากการนับ ตามเงื่อนไขที่กำหนด ใส่ลงในช่อง C18 คลิก
6. C18 เพื่อบอก Excel ว่า ต้องการนำผลที่ได้มาใส่ไว้ที่นี่
7. ไปที่เมนู Insert > Function... หรือ คลิกที่รูป บน formula bar จะเกิดหน้าจอให้เลือกฟังก์ชั่น
8.ไปที่ช่อง Or select a category ให้เลือก All เพื่อดูฟังก์ชั่นทั้งหมด จา่กนั้นจึงเลือกหา Dcount ในส่วน Select a function ดังภาพ
9. คลิก OK
10.จะเปิดหน้าต่าง ให้เติมค่าตัวเลือกต่าง ๆ ค่าที่เติมลงในช่องต่าง ๆ เหล่านี้ เราเรียกว่า parameters ให้เติมค่าต่าง ๆ ดังภาพ
Database คือตารางข้อมูลที่จะนำมาวิเคราะห์ จะบอกเป็นช่วง ในตัวอย่างนี้ ตารางข้อมูลอยู่ที่ A4:D14 การเลือกต้องให้ครอบคลุมหัวตาราง ซึ่งอยู่ที่ A4:D4 ด้วย
Field คือคอร์ลัมน์ที่จะนำมานับ ให้ใส่เป็นตัวเลขคอร์ลัมน์ที่เท่าไร ในตัวอย่างเราต้องการนับ คอร์ลัมน์ อายุ ซึ่งเป็นคอร์ลัมน์ที่ 3 (คอร์ลัมน์ 1 คือ เลขที่ คอร์ลัมน์ 2 คือ ชื่อ คอร์ลัมน์ 3 คือ อายุ คอร์ลัมน์ 4 คือ เงินเดือน)
Criteria คือเกณฑ์ในการนับ ในตัวอย่างนี้ เราได้บอกไว้แล้วที่ C1:B2 สำหรับ Database และ Criteria ถ้าไม่ต้องการพิมพ์เข้าไปโดยตรง อาจจะคลิกที่รูป และใช้เมาส์เลือกช่วงข้อมูลที่ต้องการ ก็ได้
11. เมื่อกดปุ่ม OK จะได้เท่ากับ 4 นั่นแสดงว่า มีคน จำนวน 4 คน ที่มีอายุตั้งแต่ 25 ปีขึ้นไป และมีเงินเดือนตั้งแต่ 20,000 บาทขึ้นไป จำนวน 4 คน
การหาช่วงอายุ
จากข้อมูลเดิม จะเห็นว่าผู้ตอบแบบสอบถามกรอกอายุจริืง ในการนำผลไปวิเคราะห์มักจะวิเคราะห์เป็นช่วงอายุ เช่น อายุต่ำกว่า 25 ปี กี่คน อายุ ระหว่าง 25-29 ปี กี่คน เป็นต้น ตัวอย่างต่อไปนี้ จะเป็นการนำข้อมูลเดิมมาวิเคราะห์อายุ เป็นช่วง ๆ ดังนี้
อายุ น้อยกว่า 25 ปี
อายุ 25-29 ปี
อายุ 30-39 ปี
อายุ 40 ปี ขึ้นไป (ไฟล์ exercise_dcount_age.xls)
หลักการ
ใช้ฟังก์ชั่น Dcount เหมือนข้างต้น แต่กำหนดเงื่อนไขเสียใหม่ โดยกำหนดเงื่อนไขเป็นช่วง ๆ ตามต้องการ
วิธีการ
1.เปิด Sheet ใหม่
2.พิมพ์ข้อมูล A4:D14 หรือจะ Copy มาก็ได้
3.ในช่วง A16:D24 ให้พิมพ์เกณฑ์การนับ และส่วนที่จะรายงานผล ดังนี้
4.คลิกที่ C21 และพิมพ์ที่ Formular ดังนี้ =DCOUNT(A4:D14,3,A16:A17)
5.คลิกที่ C22 และพิมพ์ที่ Formular ดังนี้ =DCOUNT(A4:D14,3,A16:B16:C17)
6.คลิกที่ C23 และพิมพ์ที่ Formular ดังนี้ =DCOUNT(A4:D14,3,A16:A18:ฺB19)
7.คลิกที่ C24 และพิมพ์ที่ Formular ดังนี้ =DCOUNT(A4:D14,3,C18:C19)
8.จะเห็นว่า เราใช้สูตรเดียวกัน ต่างกันที่เงื่อนไขเท่านั้น ผลลัพธ์ที่ได้ คืิอ
การตัดเกรดนักเรียน(grading.xls)
คุณครูสามารถใช้โปรแกรม Excel ในการกรอกคะแนน และรวมคะแนน โดยไม่ต้องใช้เครื่องคิดเลข นอกจากนี้ ยังสามารถใช้ Excel ทำอะไรได้อีกหลายอย่าง ตัวอย่างต่อไปนี้จะเป็นการใช้ Excel สำหรับตัดเกรดนักเรียน แบบอิงเกณฑ์ โดยการนำคะแนนไปเปรียบเทียบกับเกณฑ์ ว่า คะแนนตกอยู่ในเกณฑ์ใด ควรจะได้เกรดอะไร
หลักการ
ใช้ฟังก์ชั่น Vlookup เปรียบเทียบข้อมูล ฟังก์ชั่น Vlookup จะนำข้อมูลจาก cell ใด cell หนึ่ง ไปเปรียบเทียบกับข้อมูลในตาราง และถ้าพบ ก็จะคืนค่าในคอร์ลัมน์ ทางด้านขวามือ ของค่าที่ถูกเปรียบเทียบในตาราง และสามารถระบุว่า จะให้คืนค่าจากคอร์ลัมน์ใด การเปรียบเทียบทำได้สองอย่างคือ เปรียบเทียบแบบเหมือนกันทุกประการ และเปรียบเทียบแบบใกล้เคียง
ฟังก์ชั่น Vlookup มีรูปแบบการใช้ ดังนี้
Vlookup (lookup_value,table_array,col_index_num,range_lookup)
lookup_value
คือค่าที่จะนำไปเปรียบเทียบ ในที่นี้คือคะแนนของนักเรียนแต่ละคน การอ้างถึงใช้ตำแหน่งของ Cell เช่้น A3
table_array
คือตารางข้อมูลที่จะใช้เป็นเกณฑ์ในการเปรียบเทียบ ในที่นี้คือเกณฑ์ในการตัดเกรด เช่น คะแนนต่ำกว่า 50 ได้ 0 คะแนน 50-69 ได้ 1 เป็นต้น แต่ต้องเขียนอยู่ในรูปตาราง ในกรณีการตัดเกรด จะเป็นการเปรียบเทียบคะแนนแบบใกล้เคียง จะต้องมีการเรียงข้อมูล จากน้อยไปหามาก แต่ถ้าเป็นการเปรียบเทียบแบบเหมือนกันทุกประการ ก็ไม่จำเป็นต้องเรียงข้อมูลในตารางที่จะใช้เป็นเกณฑ์
col_index_num
เป็นตัวเลขตำแหน่งแถวที่จะคืนค่า ถ้าหากพบว่าเป็นไปตามเกณฑ์การเปรียบเทียบ คอร์ลัมน์แรกของตารางที่ใช้เป็นเกณฑ์การเปรียบเทียบคือ คอร์สัมนืที่ 1 ดังนั้น ค่าที่คืน จึงเป็นคอร์ลัมน์ที่ 2 หรือ 3 หรือ 4 ในกรณีที่ตารางมีหลายคอร์ลัมน์
range_lookup
มี 2 ค่า คือจริง หรือ TRUE และ เท็จ หรือ FALSE
ถ้าเป็นจริง หรือ TRUE คือต้องการให้เปรียบเทียบแบบใกล้เคียง นั่นคือ Excel จะนำค่ามาเปรียบเทียบกับค่าในตารางที่ใช้เป็นเกณฑ์ ถ้าไม่พบค่าที่เท่ากัน ก็จะถือเอาค่าตัวต่อไปที่ใกล้เคียงที่สุด ที่มีค่าน้อยกว่าค่าที่นำมาเปรียบเทียบ
ถ้าเป็นเท็จ หรือ FALSE คือต้องเป็นการเปรียบเทียบที่เหมือนกัน หรือ เท่ากันเท่านั้น
ถ้าไม่เติม จะถือว่ามีค่าเป็นจริง
วิธีการ
1.เปิดโปรแกรม Excel ใหม่ และพิมพ์ข้อมูลคะแนนนักเรียน ดังต่อไปนี้
2. ที่ E1:F6 ให้พิมพ์เกณฑ์ในการเปรียบเทียบ ดังนี้
เนื่องจากเป็นการเปรียบเทียบแบบใกล้เคียง การเปรียบเทียบจะใช้ ค่าที่ใกล้เคียงที่สุด ซึ่งน้อยกว่าค่าที่นำไปเปรียบเทียบ
สมมติว่า สุดา ได้คะแนน 58 เมื่อนำคะแนน 58 ไปเปรียบเทียบกับเกณฑ์ จะเห็นได้ว่า ค่าที่ใกล้เคียงกับ 58 ซึ่งจะต้องเป็นค่าที่น้อยกว่า 58 ก็คือ 50 เพราะถึงแม้ว่าจะใกล้กับ 60 ก็ตาม แต่ 60 มีค่ามากกว่า 58 ด้งนั้น ในการเปรียบเทียบจึงใช้ค่า 50 เพราะเป็นค่าที่ใกล้เคียงกับ 58 มากที่สุดและมีค่าน้อยกว่า 58 ด้วย เมื่อดูเกรด ก็จะพบว่า ได้เกรดเป็น 1 เพราะในการสั่ง VLOOKUP เราใช้ค่้าทางด้านขวามือ ของค่าที่ได้จากการเปรียบเทียบ
ดังนั้น จากเกณฑ์ในตาราง แสดงว่า มีการตัดเกรด ดังนี้
คะแนนต่ำกว่า 50 ได้เกรด 0
50-59 ได้เกรด 1
60-79 ได้เกรด 2
80-89 ได้เกรด 3
90-100 ได้เกรด 4
3. ต่อไปจะคิดเกรดของ สมถวิล ให้คลิกที่ C2 ซึ่งเป็นตำแหน่งที่จะนำเกรดมาแสดง
4. เพื่อความสะดวกในการอ้างอิงตำแหน่ง เราจะตั้งชื่อตำแหน่งของตารางว่าเป็น criteria โดยทำดังนี้
1. ลากดำ E2:F6 (ไม่รวม ชื่อคอร์ลัมน์)
2. ไปที่ Insert > Name > Define...
3. พิมพ์ชื่อ criteria
4. คลิก Add และคลิก OK ตามลำดับ
5. ต่อจากนี้ไป เราจะอ้างถึงตำแหน่งที่เป็นตารางเกณฑ์การคิดเกรดว่าเป็น criteria
6. ที่ช่อง Formula bar ให้พิมพ์ดังนี้
$B2เป็นการอ้างถึงตำแหน่งข้อมูล ที่เป็นคะแนนของนักเรียน ในกรณีนี้ เนื่้องจากว่าคะแนนอยู่ในคอร์ลัมน์ B จึงอ้างอิงแบบ Absolute Referencing เพื่อไม่ให้ค่าเปลี่ยนแปลงไปเมื่อมีการคัดลอกสูตร
criteriaคือข่วงตารางเกณฑ์ที่จะนำไปเปรียบเทียบ ตารางมี 2 คอร์ลัมน์ คอร์ลัมน์ที่ 1 เป็นค่าที่จะนำคะแนนมาเปรียบเทียบ ส่วนคอร์ลัมน์ที่ 2 เป็นค่าที่จะส่งคืนว่าได้เกรดอะไร
2เป็นตัวเลขตำแหน่งแถวที่จะคืนค่า คือ เกรด นั่นเอง
TRUEต้องการให้เปรียบเทียบแบบใกล้เคียง เพราะคะแนนของนักเรียนส่วนใหญ่จะไม่เท่ากับเกณฑ์หรือ จุดตัดคะแนนที่กำหนด ดังนั้นจึงต้องให้เป็นการเปรียบเทียบแบบใกล้เคียง คือ จะนำคะแนนมาเปรียบเทียบกับข้อมูลในตารางคอร์ลัมน์แรก ถ้าไม่พบ ก็จะเอาค่าที่ใกล้เคียงที่สุด ที่มีค่าน้อยกว่าคะแนนที่นำมาเปรียบเทียบ ดังนั้น สมมุติว่า นำคะแนน 75 มาหาเกรด จะพบว่า คะแนน 75 ใกล้เคียงกับ 60 มากที่สุด เพราะ 80 เป็นคะแนนที่มากกว่า 75 ดังนั้น เกรดที่ได้ หรือค่าที่ส่งคืนไป จึงเท่ากับ 2
6. กดเครื่องหมายถูก สีเขียว จะเห็นเกรด 1 ปรากฎที่ C2 ซึ่งเป็นตำแหน่งที่ระบุไว้ตั้งแต่แรก
7. ทำการคัดลอกสูตร มาไว้จาก C3 จนถึง C6
8. เมื่อปล่อยเมาส์ เกรดของนักเรียนทุกคนตามเกณฑ์ที่กำหนด จะปรากฎให้เห็นทันตา นี่คือการทำงานที่รวดเร็ว และทุ่นแรง ของ Excel