ปฏิบัติการที่ 8
การใช้โปรแกรมสํานักงาน Microsoft Excel
วัตถุประสงค์
1. รู้จักโปรแกรม Microsoft Excel ซึ่งเป็นโปรแกรมในชุดของ Microsoft Office 2. รู้จักคําสั่งพื้นฐานและฟังก์ชันต่าง ๆ ในโปรแกรม Excel
3. สามารถประยุกต์ใช้งานโปรแกรม Excel ให้เข้ากับลักษณะของงานด้านต่าง ๆ ได้ 1. โปรแกรม Microsoft Excel คืออะไร
โปรแกรม Microsoft Excel หรือ MS Excel เป็นโปรแกรมตารางคํานวณ (Spreadsheet) โปรแกรม หนึ่ง ที่จัดอยู่ในชุดของ Microsoft Office โปรแกรมตารางคํานวณนี้ได้ถูกสร้างขึ้นมาเพื่อวัตถุประสงค์หลักใน การช่วยในการกรอกข้อมูลลงในช่องตารางที่อยู่ใน “แผ่นกระดาษคํานวณ” (Worksheet) แล้วสามารถนํา ข้อมูลเหล่านั้นมาคํานวณหรือใช้ในการวิเคราะห์ข้อมูล เช่น การหาผลรวม การหาค่าที่สูงสุด และการหาค่าที่ ต่ําสุด เป็นต้น นอกจากนั้นยังสามารถใช้สร้างกราฟจากข้อมูลที่มีอยู่ได้เพื่อให้มองเห็นภาพของข้อมูลได้ชัดเจน ขึ้น ในขณะที่จัดทําเอกสารนี้โปรแกรม Excel ได้ออกรุ่น Excel 2010 ซึ่งเป็นรุ่น 14 (Version 14) แตกต่างไป จากรุ่น Excel 2003 และ Excel 2007 ในเรื่องรูปแบบ โดยเฉพาะปรับเปลี่ยนหน้าจอ จากการใช้เมนูชนิด Drop down เป็นการใช้ริบบิ้น (Ribbon) คําสั่งต่าง ๆ จะจัดอยู่บนแถบริบบิ้น แทนการใช้เมนูแบบเก่า
รูปที่ 8-1 เปรียบเทียบลักษณะหน้าตาของโปรแกรม Excel รุ่น 2003 (รูปซ้ายมือ) กับ 2010 (รูปขวามือ) 2. การเรียกใช้งานโปรแกรม Microsoft Excel
ทําโดยการคลิกปุ่ม Start แล้วเลือก All Programs จากนั้นเลือกโปรแกรม Microsoft Office แล้ว เลือกโปรแกรม Microsoft Office Excel 2003
3. ส่วนประกอบต่าง ๆ ของโปรแกรม Microsoft Excel เมื่อเปิดโปรแกรม MS Excel ครั้งแรก จะมีกรอบหน้าต่างสําหรับอํานวยความสะดวก เช่น ให้เปิดไฟล์ ใหม่ หรือกําหนดค่าต่าง ๆ ทําให้หน้าจอของ Excel ถูกกินพื้นที่ไป ในขั้นแรกนี้ ให้คลิกที่ปุ่ม X เพื่อปิดหน้าต่าง นี้ ดังรูป (ในวงรีสีแดง) แต่ละส่วนของหน้าจอมีรายละเอียด ดังนี้ • Title Bar ส่วนนี้อยู่บนสุดของหน้าจอ จะบอกชื่อไฟล์ที่กําลังทํางาน ถ้ายังไม่ได้บันทึกไฟล์ Excel จะตั้งขื่อให้เป็น Book และตามด้วยตัวเลข เช่น Book1.xls, Book2.xls เป็นต้น แต่ถ้ามีการบันทึกไฟล์ แล้ว Excel จะใช้ชื่อที่บันทึก
• Menu Bar หรือแถบเมนู จะอยู่ถัดลงมาจาก Title Bar เราใช้เมนูบาร์เพื่อบอกให้ Excel ทํางานตามที่เราต้องการ เช่น การบันทึกไฟล์ การจัดการเกี่ยวกับข้อความต่าง ๆ การจัดรูปแบบ Cell เป็นต้น
• Toolbars หรือแถบเครื่องมือ หมายถึงกลุ่มเครื่องมือเหล่านี้เป็นเครื่องมือที่ใช้บ่อย ๆ • Formula Bar หรือแถบสูตร ส่วนที่อยู่ซ้ายสุด คือ ชื่อของ cell และช่องถัดมา เป็นส่วนที่จะ ใส่ข้อมูลใน cell นี้ หรือใส่สูตรของ Excel ลงใน cell นี้
Title Bar
Menu Bar
Tool Bar
Status Bar Formula Bar
• Status Bar หรือแถบสถานะ เป็นแถบที่ใช้บอกสถานะของโปรแกรม คําว่า Ready ทาง ด้านซ้าย บอกว่าขณะนี้โปรแกรมพร้อมรับคําสั่งจากท่าน ส่วนทางด้านขวามือจะมีข้อมูลหลายอย่าง เช่น แสดง สถานะของปุ่ม Num Lock ถ้าปุ่มเปิดอยู่ จะเห็นคําว่า NUM ปุ่มนี้ ถ้าไม่เปิด จะไม่สามารถใช้ปุ่มกลุ่มตัวเลข ด้านขวามือบนแป้นพิมพ์ได้ แสดงสถานะของปุ่ม Caps Lock ถ้าปุ่มนี้ ถูกเปิดอยู่ จะเห็น CAPS ปรากฏ ปุ่มนี้ ใช้สําหรับพิมพ์อักษรแถวบน บนแป้นพิมพ์
4. ลักษณะของแผ่นงาน
เมื่อเริ่มต้นเปิดใช้งานโปรแกรม Excel เพื่อสร้างงานหนึ่งไฟล์ จะประกอบไปด้วย แผ่นกระดาษ คํานวณ (worksheet) ทั้งหมด 3 แผ่นงาน ได้แก่ Sheet1, Sheet2 และ Sheet3 ซึ่งชื่อจะแสดงอยู่ที่ Sheet Tab ถ้าต้องการมากกว่านั้น สามารถสร้างเพิ่มได้ เมื่อคลิกขวาที่ชื่อของแผ่นงาน จะปรากฏเมนูขึ้นมาให้เลือก คําสั่ง Insert หรือ เพิ่ม นอกจากนี้เราสามารถปรับแต่งชื่อและจัดการแผ่นงานได้ เช่น ลบแผ่นงาน เปลี่ยนชื่อ แผ่นงาน ระบายสีแผ่นงาน ตลอดจนย้ายตําแหน่งแผ่นงาน เป็นต้น ซึ่งใน 1 แผ่นงานจะประกอบด้วยแถว (row) ซึ่งจะถูกกํากับด้วยตัวเลข (จะมีแถวทั้งหมด 1,048,576 แถว) และคอลัมน์ (column) กํากับด้วย ตัวอักษรภาษาอังกฤษเช่น A-Z, AA-AZ – IV จํานวน 16,384 คอลัมน์ จุดตัดของแถวและคอลัมน์ทําให้เกิด ตารางเป็นช่อง ๆ เราจะเรียกว่าเซลล์ (Cell) แต่ละเซลล์จะมีชื่อกํากับ โดยชื่ออ้างอิงจากตัวอักษรชื่อคอลัมน์ ตามด้วยหมายเลขแถว ตัวอย่างเช่น C4 หมายถึง คอลัมน์ที่ C แถวที่ 4 รูปที่ 8-2 ตัวอย่างการเลือกเซล C4 5. การจัดเก็บและการเปิดเอกสาร การจัดเก็บเอกสาร
1. คลิกที่เมนู File แล้วเลือกเมนูย่อย “Save as” เพื่อบันทึกเอกสารนั้น หรือ เลือกเมนู “บันทึกแฟ้ม เป็น” เมื่อต้องการบันทึกเอกสารนั้นไปเป็นอีกเอกสารอื่นที่มีชื่อต่างกัน ปรากฏหน้าต่างดังต่อไปนี้
2. เมื่อจัดเก็บแฟ้มเอกสารตารางคํานวณจะมีนามสกุลเป็น .xls การเปิดเอกสาร 1. คลิกที่เมนู File แล้วเลือกเมนูย่อย Open เพื่อเลือกเปิดเอกสารที่มีอยู่แล้ว โดยเลือกที่เก็บเอกสาร เลือกแฟ้มเอกสาร แล้วคลิกปุ่ม Open 2. จะสังเกตว่า ชนิดของเอกสารจะ Default เป็น .xls 6. การป้อนข้อมูลลงในแผ่นงาน ข้อมูลที่ป้อนลงในเซลล์เป็นได้ทั้งตัวเลขและตัวอักษร โดยปกติตัวเลขจะจัดชิดขวาของเซลล์และ สามารถนําไปคํานวณได้ สําหรับข้อความหรือตัวอักษรจะจัดชิดซ้าย ในกรณีที่มีทั้งข้อความและตัวเลขภายใน เซลล์เดียวกัน Excel จะถือว่าเป็นข้อความ ไม่สามารถนาไปคํานวณได้ ดังนั้นถ้าต้องการนําตัวเลขไปคํานวณ ต้องพิมพ์ตัวเลขนั้น ๆ ในเซลล์ของตัวเอง และใช้การอ้างถึงชื่อของเซลล์ในการคํานวณ การป้อนข้อมูลลงในเซลล์ ทําได้ ดังนี้ 1. นําเคอร์เซอร์ไปที่เซลล์ A1 แล้วคลิก 1 ครั้ง ดังรูปต่อไปนี้ รูปที่ 8-3 ตัวอย่างการอ้างถึงเซลล์ A1 2 พิมพ์ข้อความว่า “ความรู้เบื้องต้นเกี่ยวกับคอมพิวเตอร์” จากตรงนี้จะสังเกตได้ว่าขณะที่ป้อน ข้อความที่เซลล์ A1 ข้อความที่พิมพ์จะปรากฏขึ้น 2 แห่ง คือ ในช่อง A1 และในแถบสูตร
3. หากข้อมูลถูกต้องสามารถปฏิบัติ ดังนี้ • กดปุ่ม <Enter> • หรือ กดปุ่มลูกศรเพื่อเลื่อนตําแหน่งตัวชี้เซลล์ • หรือ ใช้เมาส์คลิกที่ไอคอนเครื่องหมายถูก ในแถบสูตร 4. หรือ หากต้องการยกเลิกการป้อนข้อมูล ในขณะที่กําลังป้อนข้อมูล สามารถปฏิบัติ ดังนี้ • กดปุ่ม <ESC> • หรือ ใช้เมาส์ Click ที่ไอคอนเครื่องหมาย ในแถบสูตร รูปที่ 8-4 ตัวอย่างการพิมพ์ข้อความที่เซลล์ A1 รูปที่ 8-5 ตัวอย่างการพิมพ์ข้อความที่เซลล์ A1 ข้อสังเกตในการป้อนข้อมูล 1. ข้อมูลประเภทตัวหนังสือ (Text) จะถูกจัดชิดซ้ายของเซลล์
2. ข้อมูลประเภทตัวเลข (Number) วันที่-เวลา (Date & Time) ผลลัพธ์จากสูตรและฟังก์ชันการ คํานวณ (Formula & Function) จะถูกจัดชิดขวาของเซลล์
กว้างของเซลล์ไม่พอที่จะแสดงผล จะต้องทําการขยายขนาดความกว้างของเซลล์ คําแนะนําในการพิมพ์ 1. ในการทํางานกับ Excel มักจะมีการปรับความกว้างของคอลัมน์ให้เป็นไปตามรูปแบบที่ต้องการ เช่น แบบฟอร์ม ตารางต่าง ๆ เป็นต้น ถ้าต้องการขยายความกว้างหรือ ลดความกว้างของคอลัมน์ แต่ละ คอลัมน์ ให้นําเคอร์เซอร์ ไปวางไว้ที่เส้นขอบของคอลัมน์ที่ต้องการขยาย เมื่อเคอร์เซอร์เปลี่ยนรูปร่างเป็นลูกศร 2 หัว ให้กดเมาส์ค้างไว้ แล้วลากไปทางซ้ายหรือทางขวา ได้ตามต้องการ 2. การปรับความสูงของแถว ทําเช่นเดียวกับการปรับความกว้างของคอลัมน์ คือนําเคอร์เซอร์ไปวางไว้ ที่เส้นระหว่างแถวที่หัวแถวและลากเพื่อกําหนดความสูง
ฝึกปฏิบัติ 1. พิมพ์ข้อมูลต่อไปนี้ โดยการจัดความกว้างและความสูงของเซลล์ให้อยู่ในรูปแบบที่สวยงาม ชื่อ-สกุล อายุ อาชีพ สิริพร บางเหลือง 25 ทําสวนยางพารา เสกสิทธิ คูณศรี 30 รับราชการ พนิดา ทองอ่อน 56 แม่บ้าน 7. การแทรกคอลัมน์และการเพิ่มแถว ในบางครั้งเราอาจพิมพ์ข้อมูลตกหล่นไปแล้วต้องการเพิ่มข้อมูล สามารถทําได้โดยการแทรกคอลัมน์ เพิ่ม ดังนี้ 1. ถ้าต้องการเพิ่มข้อมูลระหว่างคอลัมน์ B และ C ทําโดยการคลิกที่ชื่อของคอลัมน์ C ซึ่งจะเกิด แถบสว่างยาวตลอดคอลัมน์ รูปที่ 8-6 2. คลิกเมาส์ขวาเพื่อแสดงคําสั่ง แล้วเลือกคําสั่ง Insert จะทําให้ข้อมูลเดิมที่อยู่ที่คอลัมน์ C ถูกย้ายไป อยู่ที่คอลัมน์ D ดังรูป รูปที่ 8-7 3. จากนั้นให้ใส่ข้อมูลที่ต้องการเพิ่ม 4. การแทรกแถวก็ทําเช่นเดียวกัน โดยคลิกที่หัวแถวที่ต้องการแทรก และคลิกเมาส์ขวาเพื่อแสดง คําสั่ง แล้วเลือกคําสั่ง Insert
8. การลบ/แก้ไขข้อมูลในแผ่นงาน ถ้าต้องการลบข้อมูลทั้งหมดในเซลล์ ให้ทําการเลือกเซลล์ที่ต้องการจะลบก่อนจากนั้นกดปุ่ม Delete ได้เลย หรือถ้าต้องการแก้ไขข้อมูล ให้ทําการเลือกเซลล์ที่ต้องการแก้ไข จากนั้นคลิกเมาส์ซ้าย 2 ครั้ง จึงจะ สามารถแก้ไขข้อมูลในเซลล์นั้นได้ หรือจะแก้ไขข้อมูลในแถบสูตรก็ได้ 9. การจัดรูปแบบของข้อมูล ใน Excel มีคําสั่งสําหรับจัดรูปแบบเซลล์โดยเฉพาะ เช่น การทําตัวอักษรแนวเฉียง 45 องศา หรือการ ทําให้ตัวเลขเมื่อป้อนลงในเซลล์นั้น ๆ แล้วจะต้องมีทศนิยม 2 ตําแหน่ง เป็นต้น ซึ่งมีวิธีการดังนี้ 1. เลือกเซลล์ที่ต้องการจะจัดรูปแบบ 2. คลิกเมาส์ขวาแล้วเลือกคําสั่ง Format Cell… หรือ จัดรูปแบบเซลล์ 3. จากนั้นจะปรากฏหน้าต่างดังรูป รูปที่ 8-8 4. เลือกจัดรูปแบบได้ตามต้องการ โดยเลือกป้ายเมนูต่าง ๆ ดังนี้ • Number หรือ ตัวเลข คือ การกําหนดรูปแบบตัวเลข เช่น จะให้มีทศนิยมกี่ตําแหน่ง จัดรูปแบบเป็นวันที่ หรือสกุลเงิน เป็นต้น • Alignment หรือ การจัดแนว คือ การสั่งชิดซ้าย ชิดขวา หรือ จัดกึ่งกลางของเซลล์ อักษร แนวตั้ง แนวเอียง หรือถ้าข้อความยาวเกินไปจะจัดการอย่างไร เช่น ย่อให้เล็กลง หรือผสานเซลล์ให้ครอบคลุม ข้อความทั้งหมด เป็นต้น
• Font หรือ แบบอักษร คือ การกําหนดชนิดตัวอักษร (Fonts) ลักษณะและขนาด ตลอดจนสี ของตัวอักษร • Border หรือ เส้นขอบ คือ การกําหนดลักษณะเส้นขอบของเซลล์นั้น ๆ ใช้สาหรับการ กําหนดเส้นตาราง หรือเส้นใต้ เป็นต้น • Fill หรือ การเติม คือ การกําหนดลวดลายพื้นหลัง และสีพื้นหลัง • Protection หรือ การป้องกัน คือ การกําหนดให้รับหรือไม่สามารถรับข้อมูลได้ และสามารถ กําหนดไม่ให้แสดงเซลล์นั้น ๆ ก็ได้ ซึ่งจะมีผลก็ต่อเมื่อมีการป้องกันแผ่นงานไว้แล้ว 10. การคํานวณอย่างง่าย เริ่มต้นจากกาคํานวณอย่างง่าย ๆ โดยการใช้ Excel เสมือนเป็นเครื่องคิดเลขเครื่องหนึ่ง ซึ่งการ คํานวณใน Excel ทําได้โดยการป้อนตัวเลขเข้าในเซลล์แต่ละเซลล์ จากนั้นสามารถนําตัวเลขที่อยู่ในเซลล์แต่ละ เซลล์มา บวก ลบ คูณ หาร ยกกําลัง (+, -, *, /, ^) กัน ซึ่งในการใส่สูตรจะต้องเริ่มต้นใส่เครื่องหมายเท่ากับ (=) ทุกครั้ง ลองดูตัวอย่าง และทําตามต่อไปนี้ 1. คลิกที่ตําแหน่ง A1 แล้วพิมพ์เลข 20 แล้วกด Enter 2. ที่ตําแหน่ง A2 พิมพ์เลข 30 แล้วกด Enter 3. ที่ตําแหน่ง A3 พิมพ์เลข 40 แล้วกด Enter 4. ที่ตําแหน่ง A4 พิมพ์สูตร =(A1+A2+A3)/3 5. เมื่อพิมพ์เสร็จแล้วกด Enter จะปรากฏผลลัพธ์ที่ตําแหน่ง A4 เท่ากับ 30 รูปที่ 8-9
รูปที่ 8-10 ข้อสังเกต • เราสามารถใส่เครื่องหมายดําเนินการทางคณิตศาสตร์กับข้อมูลได้หลายเซลล์พร้อมกัน โดย อาจใช้เครื่องหมายวงเล็บ ( ) เพื่อกําหนดลําดับการดําเนินการทางคณิตศาสตร์ เช่น= (C4+C5+F6)/S4 เป็น ต้น • เราสามารถใส่เครื่องหมายดําเนินการทางคณิตศาสตร์ กับข้อมูลที่เป็นตัวเลขได้ โดยไม่ต้อง อ้างอิงเซลล์ เช่น =6+5 เป็นต้น • เราสามารถคํานวณใส่เครื่องหมายดําเนินการทางคณิตศาสตร์ กับข้อมูลในเซลล์ ร่วมกับ ข้อมูลที่เป็นตัวเลขได้ เช่น =A6*5 เป็นต้น ฝึกปฏิบัติด้วยตนเอง ให้นิสิตคํานวณค่าล่วงเวลาในตารางต่อไปนี้ (ตอบเป็นทศนิยม 2 ตําแหน่ง) รูปที่ 8-11 11. การคัดลอกสูตรการคํานวณโดยใช้ Auto Fill 1. คลิกเมาส์ที่เซลล์ที่มีข้อมูล หรือ สูตรการคํานวณ 2. เลื่อนเมาส์มาชี้ที่มุมขวาด้านล่างของเซลล์ที่เซลล์ที่มีข้อมูล หรือ สูตรการคํานวณ จะเห็นเป็น เครื่องหมาย + 3. คลิกและลากเมาส์เซลล์ที่ต้องการและปล่อยเมาส์
4. จะได้ข้อมูล หรือ สูตรการคํานวณ ที่คัดลอกมาจากเซลล์ตั้งต้น 12. การใช้งานฟังก์ชันต่าง ๆ ฟังก์ชันของ Excel ก็คือสูตรสําเร็จรูปที่โปรแกรม Excel จัดทําไว้ให้แล้วพร้อมเรียกใช้งานได้เลย เพียงแต่เราใส่ค่าที่ต้องการคํานวณ โดยการระบุเซลล์ที่มีข้อมูลผ่านการคลิกเมาส์หรือพิมพ์โดยตรง เราสามารถ เรียกใช้งานฟังก์ชันต่าง ๆ ผ่านการเลือกสูตรผ่านเมนู fx ที่แถบสูตร เมื่อคลิกแล้วจะปรากฏหน้าต่างดังรูปที่ 8-13 ทําให้เราสามารถเลือกฟังก์ชันในการคํานวณได้หลากหลาย อีกทางเลือกหนึ่งคือผู้ใช้พิมพ์สูตรเองในเซลล์ที่ เราต้องการให้ผลลัพธ์ไปแสดง รูปที่ 8-12 รูปที่ 8-13 ฟังก์ชันเหล่านี้ช่วยให้ทํางานได้เร็วขึ้น ถ้าไม่มีฟังก์ชัน เราจะต้องเสียเวลาในการคํานวณเป็นอย่างมาก เช่น ถ้าเราต้องการรวมตัวเลขใน B5 ถึง B15 แล้วเก็บผลลัพธ์ที่ตําแหน่ง B16 เราต้องเขียนสูตรในตําแหน่ง B16 หรือแถบสูตรเป็น =B5+B6+B7+B8+B9+B10+B11+B12+B13+B14+B15 แต่ถ้าเราใช้ฟังก์ชัน SUM ซึ่งเป็นการรวมตัวเลขใน เซลล์ เราสามารถเรียกใช้สูตร และระบุข้อมูลได้ สะดวกขึ้น ดังนี้ =SUM(B5:B15) เมนูที่ใช้ในการเลือกสูตรการคํานวณ
โปรแกรม Excel มีฟังก์ชันให้เราเลือกใช้จํานวนมาก ทั้งฟังก์ชันทางคณิตศาสตร์ การเงิน การคิดค่า ทางสถิติ เป็นต้น ฟังก์ชันเหล่านี้ ทําให้เราสามารถหาค่าต่าง ๆ ได้สะดวกยิ่งขึ้น เช่น หาผลรวม หาค่าเฉลี่ย นับ จํานวนความถี่ หรือแม้แต่การหาผลรวมย่อย (Subtotal) การเรียงลําดับ ก็สามารถทําได้ การใช้ฟังก์ชัน จําเป็นต้องมีการอ้างอิงถึงข้อมูลที่จะนํามาใช้ในฟังก์ชัน จึงควรเรียนรู้การอ้างอิงข้อมูลในเซลล์ต่าง ๆ เสียก่อน 13. การอ้างถึงข้อมูลในเซลล์ การจะใช้ฟังก์ชันได้ถูกต้องนั้นต้องมีการระบุตําแหน่งของเซลล์ ว่าจะเลือกตําแหน่งใดบ้าง การอ้างอิง แบ่งออกเป็น 3 ลักษณะ คือ Relative, Absolute และ Mixed Referencing
1. การอ้างอิงเซลล์แบบสัมพัทธ์ (Relative Referencing) การอ้างชื่อเซลล์แบบสัมพัทธ์(Relative Reference) เป็นการอ้างถึงเซลล์หนึ่งเซลล์ใด(หรือ กลุ่มหนึ่งกลุ่มใด) โดยการอ้างอิงแบบสัมพัทธ์จะสัมพันธ์กับตําแหน่งกับเซลล์ที่อ้างถึงมัน ตัวอย่างเช่น o อ้างอิง แบบเซลล์เดียว เช่น A1 o อ้างอิงแบบช่วง หรือ range reference เป็นการอ้างอิงตําแหน่งข้อมูลในเซลล์ที่ต้องการ ตั้งแต่เซลล์แรกที่ระบุ จนถึงเซลล์สุดท้ายที่ระบุ รวมทั้งเซลล์อื่น ๆ ที่อยู่ในระหว่างนั้นด้วย รูปแบบในการเขียน ใช้เครื่องหมาย : คั่นกลางระหว่างชื่อตําแหน่งของเซลล์ เช่น การอ้างอิงเป็น A1:A3 หมายถึงเซลล์ A1, A2 และ A3 o อ้างอิงแบบยูเนี่ยน เป็นการอ้างอิงแบบผสมผสานกันของการอ้างอิงแบบช่วง การเขียนให้คั่น
ด้วยเครื่องหมายคอมม่า ( , ) เช่น ถ้าการอ้างอิง A1, A3, A5 หมายถึงเซลล์ A1, A3 และ A5 อีกหนึ่ง ตัวอย่างเช่น ถ้าการอ้างอิงเป็น (A1:C3), (D1:D3) หมายถึงเซลล์ A1, A2 , A3, B1, B2 , B3, C1, C2 , C3, D1, D2และ D3 เป็นต้น
ตัวอย่าง จากภาพข้างล่างนี้ จะเห็นรายการสิ่งของ ราคาต่อหน่วย จํานวน และรวมเงิน
o ในตําแหน่ง D2 เป็นการใช้สูตรคํานวณ โดยให้นําข้อมูลในตําแหน่ง B2 ไปคูณกับข้อมูลใน
รูปที่ 8-14 o จากนั้นให้ทํา Auto Fill เพิ่มเป็นการคัดลอกสูตรที่ตําแหน่ง D2 ไปยังเซลล์ D3 และ D4 o ถ้าตรวจสอบดูสูตรที่โปรแกรม Excel คัดลอกมาวางที่ตําแหน่ง D3 และ D4 จะเห็นว่า Excel เปลี่ยนสูตรเป็น =B3*C3 และ =B4*D4 ตามลาดับ รูปที่ 8-15 ที่เป็นเช่นนี้ เพราะ เซลล์ต้นแบบ คือ D2 ใช้การอ้างอิงแบบ Relative Referencing เมื่อเปลี่ยน ตําแหน่ง การอ้างอิงจึงเปลี่ยนไปด้วย การอ้างอิงแบบ Relative เป็นการอ้างอิงที่ยึดเซลล์ตําแหน่งปัจจุบันเป็น หลัก เมื่อมีการเปลี่ยนแปลงตําแหน่งหลัก การอ้างอิงจึงเปลี่ยนไปด้วย 2. การอ้างอิงแบบแน่นอน (Absolute Referencing) การอ้างอิงแบบ Absolute เป็นการระบุเซลล์ที่แน่นอนไม่ว่าจะมีการคัดลอกหรื copy สูตรไปไว้ที่ไหน ก็ตาม การอ้างอิงก็ยังอ้างอิงถึงเซลล์เดิม การอ้างอิงแบบนี้เราใช้เครื่องหมาย $ นําหน้าทั้งแนวนอนและแนวตั้ง ตัวอย่างเช่น $D$6, $E$4 เป็นต้น การใช้เทคนิค AutoFill ในการคัดลอกสูตรการคํานวณอ้างอิงแบบสัมบูรณ์ มีผลทําให้สูตรการคํานวณที่ปลายทางคงที่ ไม่มีการเปลี่ยนตัวเลขแถว และตัวอักษรคอลัมน์ เสมือนเป็นการ ล็อคตําแหน่งของเซลล์นั้น ๆ ตัวอย่าง จากตัวอย่างที่ผ่านมา ในการขายสินค้าทุกรายการจะต้องคิดภาษีมูลค่าเพิ่ม 7% ให้กับสินค้า ทุกรายการ เราสามารถพิมพ์ 7% ไว้ที่เซลล์ใดเซลล์หนึ่ง o ทดลองโดยการใส่สูตรที่มีการอ้างชื่อเซลล์แบบสัมพัทธ์ที่ตําแหน่ง E2 = D2*B5 ดังภาพ จากนั้นทําการ Auto Fill ไปยังตําแหน่ง E4 แล้วสังเกตผลลัพธ์ที่ได้ สูตรในตําแหน่ง D3
รูปที่ 8-16 o จากนั้นให้ทําการใส่สูตรแบบการอ้างอิงแบบ Absolute ที่ตําแหน่ง E2 ใหม่ เป็น =D2*$B$5 แล้วให้สังเกตผลลัพธ์ที่ได้ o จากนั้นให้ทําการ Auto Fill ไปถึงเซลล์ E4 แล้วตรวจสอบความถูกต้อง 3. การอ้างอิงแบบผสม (Mixed Referencing) การอ้างอิงแบบนี้เป็นการระบุค่าตายตัวคงที่ไม่เปลี่ยนแปลง โดยกําหนดเฉพาะด้านแนวตั้ง (Column) หรือ แนวนอน (Row) เพียงอย่างใดอย่างหนึ่งเท่านั้น เช่น ยึดเฉพาะ Column C เป็นหลัก เป็นต้น การอ้างอิง แบบนี้เราใช้เครื่องหมาย $ นําหน้าส่วนที่ต้องการล็อคหรือให้มีค่าคงที่ ไม่เปลี่ยนแปลง เช่น $C1 เป็นการล็อค คอลัมน์ C หรือ C$1 เป็นการล็อคแถวที่ 1 เป็นต้น 14. วิธีใช้งานสูตรของ Excel Excel มีสูตรหรือฟังก์ชันให้เลือกใช้จํานวนมาก การเรียกใช้งานสามารถพิมพ์เข้าโดยตรงที่แถบสูตร หรือพิมพ์สูตรได้เองผ่านแป้นพิมพ์ ตัวอย่างต่อไปนี้ จะใช้เป็นข้อมูลสําหรับในการเรียกใช้งานสูตรต่าง ๆ ของ Excel ก่อนอื่นให้เตรียม ข้อมูลดังนี้ 1. พิมพ์ 5 ในตําแหน่ง B1 แล้วกด Enter 2. พิมพ์ 2 ในตําแหน่ง B2 แล้วกด Enter 3. พิมพ์ 6 ในตําแหน่ง B3 แล้วกด Enter 4. พิมพ์ 4 ในตําแหน่ง B4 แล้วกด Enter 5. พิมพ์ 8 ในตําแหน่ง B5 แล้วกด Enter
• ฟังก์ชัน SUM เป็นการหาผลรวมของช่วงที่กําหนด โดยมีรูปแบบดังนี้ =SUM(เซลล์เริ่มต้น:เซลล์สุดท้าย) หรือ =SUM(เซลล์,เซลล์,เซลล์,...,เซลล์) ตัวอย่าง ที่ตําแหน่ง A6 พิมพ์คําว่า ผลรวมทั้งหมด ดังนั้นถ้าต้องการหาผลบวกของข้อมูลที่อยู่ในตําแหน่ง B1, B2, B3, B4 และ B5 เราสามารถใส่สูตรที่ ตําแหน่ง B6 ได้เป็น =SUM(B1:B5) หมายถึงต้องการบวกตัวเลขทั้งหมดที่อยู่ในเซลล์ B1, B2, B3, B4 และ B5 ซึ่งจะเหมือนกับ =B1+B2+B3+B4+B5 คําตอบที่ได้จะเท่ากับ 25 รูปที่ 8-17 แต่ถ้าต้องการบวกข้อมูลที่อยู่ในตําแหน่ง B1, B3, B5 จะต้องใส่สูตรเป็น =SUM(B1,B3,B5) ซึ่ง เหมือนกันกับ =B1+B3+B5 คําตอบที่ได้จะเท่ากับ 19 รูปที่ 8-18 ถ้าต้องการบวกข้อมูลที่ตําแหน่ง B1, B2, B4 จะต้องใส่สูตรอย่างไร • ฟังก์ชัน AVERAGE เป็นการหาค่าเฉลี่ยเลขคณิตของข้อมูลในช่วงที่กําหนด รูปแบบเป็นดังนี้ =AVERAGE(เซลล์เริ่มต้น:เซลล์สุดท้าย) หรือ =AVERAGE(เซลล์,เซลล์,เซลล์,...,เซลล์)
ตัวอย่าง ที่ตําแหน่ง A8 พิมพ์ข้อความว่า ค่าเฉลี่ย ดังนั้นถ้าต้องการหาค่าเฉลี่ยของข้อมูลที่ตําแหน่ง B1, B2, B3, B4 และ B5 เราสามารถใส่สูตรที่ตําแหน่ง B8 ได้เป็น =AVERAGE(B1:B5) ซึ่งหมายถึงการหา ค่าเฉลี่ยของข้อมูลที่อยู่ที่ตําแหน่ง B1, B2, B3, B4 และ B5 ซึ่งจะเหมือนกับ =(B1+B2+B3+B4+B5)/5 ซึ่ง เท่ากับ 5 นั่นเอง รูปที่ 8-19 • ฟังก์ชัน MIN เป็นการดูค่าต่ําที่สุดในชุดข้อมูลนั้น โดยมีรูปแบบดังนี้ =MIN(เซลล์เริ่มต้น:เซลล์สุดท้าย) หรือ =MIN(เซลล์,เซลล์,เซลล์,...,เซลล์) ตัวอย่าง ที่ตําแหน่ง A9 ให้พิมพ์ข้อความว่า ค่าต่ําสุด ดังนั้นถ้าต้องการหาค่าที่ต่ําที่สุดของข้อมูลที่ ตําแหน่ง B1, B2, B3, B4 และ B5 เราสามารถใส่สูตรที่ตําแหน่ง B9 ได้เป็น =MIN(B1:B5) ซึ่งหมายถึงการหา ค่าที่ต่ําที่สุดของข้อมูลที่อยู่ที่ตําแหน่ง B1, B2, B3, B4 และ B5 ซึ่งเท่ากับ 2 นั่นเอง รูปที่ 8-20 • ฟังก์ชัน MAX เป็นการดูค่าต่ําที่สุดในชุดข้อมูลนั้น โดยมีรูปแบบดังนี้ =MAX(เซลล์เริ่มต้น:เซลล์สุดท้าย) หรือ =MAX(เซลล์,เซลล์,เซลล์,...,เซลล์)
ตัวอย่าง ที่ตําแหน่ง A10 ให้พิมพ์ข้อความว่า ค่ามากสุด ดังนั้นถ้าต้องการหาค่าที่มากสุดของข้อมูลที่ ตําแหน่ง B1, B2, B3, B4 และ B5 เราสามารถใส่สูตรที่ตําแหน่ง B9 ได้เป็น =MAX(B1:B5) ซึ่งหมายถึง การหาค่าที่มากที่สุดของข้อมูลที่อยู่ที่ตําแหน่ง B1, B2, B3, B4 และ B5 ซึ่งเท่ากับ 8 นั่นเอง รูปที่ 8-21 • ฟังก์ชัน COUNT เป็นการนับจํานวนข้อมูลที่เป็นตัวเลข มีรูปแบบดังนี้ =COUNT(เซลล์เริ่มต้น:เซลล์สุดท้าย) ตัวอย่าง ที่ตําแหน่ง A11 พิมพ์ข้อความว่า จํานวนนับ จากนั้นให้ใส่สูตรที่ตําแหน่ง B11 เป็น =COUNT(B1:B5) รูปที่ 8-22 แต่ถ้าต้องการนับจํานวนข้อมูลที่เป็นตัวหนังสือให้ใช้ฟังก์ชัน COUNTA() ถ้าต้องการนับช่องว่าง ให้ใช้ ฟังก์ชัน COUNTBLANK() • ฟังก์ชัน ROUND การปัดเศษตัวเลขขึ้นเป็นจํานวนหลักที่ระบุ โดยมีรูปแบบดังนี้ = ROUND(ตัวเลข,จํานวนหลัก) หรือ =ROUND(เซลล์,จํานวนหลัก)
ตัวอย่าง 1. =ROUND(2.15, 1) จะให้ผลลัพธ์เท่ากับ 2.2 2. ที่ตําแหน่ง B12 ให้พิมพ์ตัวเลข 6.336 จากนั้นที่ A13 พิมพ์ข้อความว่า ปัดให้มีทศนิยม 2 ตําแหน่ง ดังนั้นถ้าต้องการทําให้ตัวเลขที่ตําแหน่ง B12 เป็นตัวเลขที่มีทศนิยม 2 ตําแหน่ง สามารถใส่สูตรที่ตําแหน่ง B13 เป็น =ROUND(B12,2) จะมีค่าเท่ากับ 6.34 รูปที่ 8-23 • ฟังก์ชัน INT ปัดเศษตัวเลขลงเป็นเลขจํานวนเต็มที่ใกล้ที่สุด โดยมีรูปแบบดังนี้ =INT(ตัวเลข) หรือ =INT(เซลล์) ตัวอย่าง 1. =INT(8.9) เท่ากับ 8 2. ที่ A14 พิมพ์ข้อความว่า ทําให้เป็นจํานวนเต็ม ดังนั้นถ้าต้องการทําให้ตัวเลขที่ตําแหน่ง B12 เป็นตัวเลขจํานวนเต็ม สามารถใส่สูตรที่ตําแหน่ง B14 เป็น =INT(B12) จะมีค่าเท่ากับ 6 • ฟังก์ชัน COUNTIF หลายครั้งที่เราต้องการนับจํานวน เช่น มีเพศชายกี่คน เพศหญิงกี่คน หรือผู้ที่มีอายุมากกว่า 40 ปี กี่คน เป็นต้น การนับในลักษณะนี้จําเป็นต้องระบุเงื่อนไขในการนับ ซึ่งมีรูปแบบ ดังนี้ =COUNTIF(range,criteria) range คือ ขอบเขตของเซลล์ที่ต้องการให้นับ เช่น B2:B5 หรือ C2:C5 เป็นต้น criteria คือ เกณฑ์หรือเงื่อนไขข้อกําหนดในการนับ เช่น นับเฉพาะผู้ชาย เป็นต้น และเงื่อนไขที่ กําหนดนั้น จะต้องอยู่ในเครื่องหมายคําพูด ตัวอย่าง ให้พิมพ์ข้อมูลต่อไปนี้ตามรูปด้านล่าง
รูปที่ 8-24 1. ถ้าต้องการนับว่ามีกี่คนที่อายุมากกว่า 20 ขึ้นไป ให้ใส่สูตรที่ตําแหน่ง B7 ดังนี้ =COUNTIF(B2:B5,">20") ซึ่งจะได้คําตอบเท่ากับ 3 คน 2. ถ้าต้องการนับว่าเป็นเพศชายกี่คน ให้ใส่สูตรที่ตําแหน่ง B8 ดังนี้ =COUNTIF(C2:C5,"ชาย") ซึ่งจะได้คําตอบเท่ากับ 2 คน 3. ถ้าต้องการนับว่าเป็นเพศหญิงกี่คน ให้ใส่สูตรที่ตําแหน่ง Be ดังนี้ =COUNTIF(C2:C5,"หญิง") ซึ่งจะได้คําตอบเท่ากับ 2 คน 15. การประยุกต์ใช้งานโปรแกรม Excel 15.1 การคํานวณยอดขายประจําวัน โดยให้พิมพ์ข้อมูลดังต่อไปนี้ รูปที่ 8-25 1. คํานวณหากําไรสุทธิโดยพิมพ์สูตร =C3-D3 ที่เซลล์ E3 และหาผลรวมโดยพิมพ์สูตร =SUM(E3:E8) ที่เซลล์ E9 2. คัดลอกสูตรจากเซลล์ D4 ไปยังเซลล์ D5 ถึง D8 ด้วยวิธีใช้ Auto Fill ดังนี้ o Click Mouse ที่เซลล์ E3 o เลื่อน Mouse มาชี้ที่มุมขวาด้านล่างของเซลล์ E3 จะเห็น Mouse เปลี่ยนเป็น เครื่องหมาย + 15.2 บริษัทของท่านในทุก ๆ สิ้นเดือนจะต้องคํานวณเงินเดือนที่จะต้องจ่ายให้กับพนักงานแต่ละคน จงหาเงิน สุทธิที่พนักงานแต่ละคนจะได้รับหลังจากที่หักค่าประกันสังคม 5% ออกไปแล้ว
รูปที่ 8-26 จงตอบคําถามต่อไปนี้
1. ถ้าต้องการคํานวณเงินประกันสังคมจะต้องใส่สูตรที่ตําแหน่ง E3 เป็น... ซึ่งเมื่อทําการ Auto Fill สูตรที่ E3 ไปจนถึง E10 จะต้องให้ผลลัพธ์ที่ถูกต้อง
2. ถ้าต้องการคํานวณเงินสุทธิที่นางสาวพิมพาจะได้รับจะต้องใช้สูตร... 15.3 สร้างตารางคํานวณคะแนนวิชาเทคโนโลยีสารสนเทศในชีวิตประจําวัน ดังต่อไปนี้ รูปที่ 8-27 จงคํานวณหาค่าต่าง ๆ ของเซลล์ต่อไปนี้ 1. เซลล์ F3 ใส่สูตร... 2. เซลล์ C14 ใส่สูตร... 3. เซลล์ C15 ใส่สูตร...
4. เซลล์ C16 ใส่สูตร... 5. เซลล์ C17 ใส่สูตร... 6. เซลล์ C18 ใส่สูตร... 7. เซลล์ C19 ใส่สูตร...
คณะวิทยาการสารสนเทศ มหาวิทยาลัยบูรพา รหัสประจําตัว...ชื่อ-สกุล... กลุ่ม...คณะ...อาจารย์ผู้สอน... คําถามท้ายปฏิบัติการ 1. จงอธิบายลักษณะเด่นของโปรแกรมตารางคํานวณ (Spreadsheet) ……….……… ……….……… ……….……… ……….……… ……….……… ……….……… ……….……… ……….……… 2. จากข้อมูลรายงานเกรดเฉลี่ยของนิสิต จงตอบคําถามต่อไปนี้ 2.1 จํานวนหน่วยกิตรวมที่นิสิตลงทะเบียนเทอมนี้ที่เซลล์ C11 คํานวณได้จากสูตร………... 2.2 ค่า GP ที่เซลล์ C12 คํานวณได้จากสูตร... 2.3 GPA ของเทอมนี้ที่เซลล์ C13 คํานวณได้จากสูตร... 2.4 ถ้าเปลี่ยนแปลงเกรดของวิชา Computer and the Internet เป็น A ผลจะเป็นอย่างไร……….……….. ………...
