ปฏ บ ต การท 9 การใช โปรแกรมสาน กงาน Microsoft Excel (ต อ)

(1)

ปฏิบัติการที่ 9

การใช้โปรแกรมส านักงาน Microsoft Excel (ต่อ) วัตถุประสงค์

1. รู้จักโปรแกรม Microsoft Excel ซึ่งเป็นโปรแกรมในชุดของ Microsoft Office 2. รู้จักฟังก์ชันต่าง ๆ ในโปรแกรม Excel 3. สามารถสร้างแผนภูมิได้ 4. สามารถประยุกต์ใช้งานโปรแกรม Excel ให้เข้ากับลักษณะของงานด้านต่าง ๆ ได้ จากปฏิบัติการที่ 8 ท าให้เราได้รู้จักหน้าตาและความสามารถของโปรแกรม Excel มากขึ้น แต่ โปรแกรม Excel ยังมีเครื่องมือที่น่าสนใจที่ท าให้เราสามารถน าไปประยุกต์ใช้กับงานด้านต่าง ๆ อีกมาก 1. วิธีใช้งานสูตรของ Excel (ต่อ)  ฟังก์ชัน IF ฟังก์ชัน IF() เป็นฟังก์ชันที่ใช้ในการก าหนดเงื่อนไข สามารถน าไปใช้ได้ในหลายสถานการณ์ ที่นิยม ได้แก่ น ามาใช้ในการค านวณเกรด เป็นต้น รูปแบบการใช้คือ

=IF ( logical_test, value_if_true, value_if_false )

logical_test เป็นการตรวจสอบเงื่อนไขในเซลล์ต่าง ๆ ว่าเป็นจริงหรือไม่ เช่น ตรวจสอบว่า ข้อมูลใน เซลล์ A5 มีค่ามากกว่า 50 หรือไม่ เป็นต้น value_if_true คือสิ่งที่จะให้แสดง ถ้าการตรวจสอบเงื่อนไขนั้นเป็นจริง value_if_false คือสิ่งที่จะให้แสดง ถ้าการตรวจสอบเงื่อนไขนั้นเป็นเท็จ ในการตรวจสอบเงื่อนไขจะต้องใช้เครื่องหมายการเปรียบเทียบดังนี้ เครื่องหมาย ความหมาย > มากกว่า < น้อยกว่า >= มากกว่าหรือเท่ากับ <= น้อยกว่าหรือเท่ากับ = เท่ากับ <> ไม่เท่ากับ ตัวอย่างที่ 1 จะใช้ในการตัดเกรดคะแนนว่าผ่านหรือไม่ผ่าน เงื่อนไขคือ ถ้าคะแนนมากกว่า 60 จะถือ ว่า ผ่าน ข้อมูลคะแนนที่จะน ามาตัดเกรด มีดังนี้

(2)

รูปที่ 9-1 การตรวจสอบเงื่อนไขคือ คะแนนที่ต าแหน่ง B2 มีค่ามากกว่า 60 หรือไม่ ถ้ามากกว่าจริง ให้แสดงที่ ต าแหน่ง C2 เป็น “ผ่าน” ถ้าไม่จริงให้แสดงที่ต าแหน่ง C2 เป็น “ไม่ผ่าน” ดังนั้นสามารถเขียนเงื่อนไขได้เป็น =IF(B2>60,“ผ่าน”, “ไม่ผ่าน”) ดังนั้นท าการใส่สูตร IF ที่ต าแหน่งเซลล์ C2 ตามเงื่อนไขดังกล่าว รูปที่ 9-2 หลังจากนั้นท าการ Auto Fill ให้ครอบคลุมถึงต าแหน่งเซลล์ C11 จะได้ผลลัพธ์ดังรูปต่อไปนี้ เงื่อนไขที่ต้องการทดสอบ สิ่งที่ต้องการแสดงเมื่อเงื่อนไขเป็นจริง สิ่งที่ต้องการแสดงเมื่อเงื่อนไขเป็นเท็จ

(3)

รูปที่ 9-3  ฝึกปฏิบัติด้วยตนเอง ถ้าเปลี่ยนเงื่อนไขเป็น B2<=60 จะต้องพิมพ์สูตรในต าแหน่งเซลล์ C2 ใหม่ หรือไม่ ถ้าเปลี่ยนจะต้องแก้ไขสูตรในต าแหน่ง C2 อย่างไร ตัวอย่างที่ 2 ถ้ามีการตัดหลายเกรด สามารถท าได้โดยก าหนดเงื่อนไขซ้อนเงื่อนไข การก าหนด เงื่อนไขเริ่มจากน้อยไปก่อน เมื่อตรวจสอบว่าไม่เป็นจริงจึงตรวจสอบเงื่อนไขต่อไปเรื่อย ๆ จนหมด  ตัวอย่างต่อไปนี้ เป็นการก าหนด 2 เงื่อนไข =IF(เงื่อนไขที่1,จริง,IF(เงื่อนไขที่2,จริง,เท็จ)) จะเห็นว่า เมื่อตรวจเงื่อนไขที่1 ครั้งแรกถ้าพบว่าเป็นจริงก็จะแสดงผลและไม่ตรวจสอบเงื่อนไขต่อไป แต่ถ้าไม่จริง จะตรวจสอบเงื่อนไขที่2 ต่อไป ถ้าจริงจะแสดงผลค่าจริง แต่ถ้าไม่จริงจะแสดงผลค่าที่เป็นเท็จ  ตัวอย่างต่อไปนี้ เป็นการก าหนด 3 เงื่อนไข =IF(เงื่อนไขที่1,จริง,IF(เงื่อนไขที่2,จริง,IF(เงื่อนไขที่3,จริง,เท็จ))) จะเห็นว่า เมื่อตรวจเงื่อนไขที่1 ครั้งแรกถ้าพบว่าเป็นจริงก็จะแสดงผลและไม่ตรวจสอบเงื่อนไขต่อไป แต่ถ้าไม่จริง จะตรวจสอบเงื่อนไขที่2 ต่อไป ถ้าจริงจะแสดงผลค่าจริง แต่ถ้าไม่จริง จะตรวจสอบเงื่อนไขที่3 ต่อไป ถ้าจริงจะแสดงผลค่าจริง แต่ถ้าไม่จริงจะแสดงผลค่าที่เป็นเท็จ  ถ้ามีเงื่อนไขที่ต้องพิจารณามากกว่า 3 เงื่อนไข ก็ให้ท าในลักษณะเดียวกัน ข้อสังเกต ถ้าสิ่งที่ต้องการให้แสดงเป็นข้อความหรือตัวอักษร ให้ใส่ข้อความหรือตัวอักษรนั้นอยู่ภายใน เครื่องหมายค าพูด (“”) เราใช้หลักการดังกล่าวข้างต้นส าหรับการตัดเกรดหลายเกรด สมมติว่าเกณฑ์ในการตัดเกรด มีดังนี้ 0-50 F 51-60 D 61-70 C 71-80 B 81-100 A

(4)

การตรวจสอบเงื่อนไขคือ ครั้งที่ 1 คะแนนน้อยกว่าหรือเท่ากับ 50 หรือไม่ ถ้าจริง ให้เกรดเป็น F ถ้าไม่จริงตรวจสอบต่อไป ครั้งที่ 2 คะแนนน้อยกว่าหรือเท่ากับ 60 หรือไม่ ถ้าจริง ให้เกรดเป็น D ถ้าไม่จริงตรวจสอบต่อไป ครั้งที่ 3 คะแนนน้อยกว่าหรือเท่ากับ 70 หรือไม่ ถ้าจริง ให้เกรดเป็น C ถ้าไม่จริงตรวจสอบต่อไป ครั้งที่ 4 คะแนนน้อยกว่าหรือเท่ากับ 80 หรือไม่ ถ้าจริง ให้เกรดเป็น B ถ้าไม่จริง ให้เกรดเป็น A (แสดงว่า คะแนนที่ก าลังตรวจมีค่ามากกว่า 80 จึงควรได้เกรดเป็น A จึงไม่จ าเป็นต้องมีการตรวจสอบเงื่อนไข ในครั้งที่ 5 อีกแล้ว) ดังนั้นจึงเขียนเงื่อนไขได้ดังนี้ =if(B2<=50,“F”,if(B2<=60,“D”,if(B2<=70,“C”,if(B2<80,“B”,“A”)))) รูปที่ 9-4 หลังจากนั้นท าการ Auto Fill ให้ครอบคลุมถึงต าแหน่งเซลล์ C11 ซึ่งจะให้ผลลัพธ์ดังรูปต่อไปนี้ รูปที่ 9-5 2. การสร้างแผนภูมิ โปรแกรม Excel มีแผนภูมิส าเร็จรูปเตรียมไว้พร้อมใช้งาน ซึ่งมีด้วยกันหลายประเภท เช่น คอลัมน์ เส้น วงกลม แท่ง เป็นต้น นอกจากนี้ยังสามารถปรับแต่งเพิ่มเติมเพื่อให้แผนภูมิให้ดูสวยงาม เช่น สามารถใช้ ภาพประกอบหรือลวดลายพื้นได้

(5)

วิธีการสร้างแผนภูมิมีดังนี้ 1. สร้างข้อมูลตามรูปข้างล่างนี้

รูปที่ 9-10

2. คลิกเซลล์ใดเซลล์หนึ่งในบริเวณ A3:D7 เพื่อระบุขอบเขตของข้อมูลที่จะน ามาจัดท าแผนภูมิ ข้อมูลที่จะ น ามาใช้ต้องอยู่ในลักษณะตาราง

3. ไปที่ Insert (tab) -> Charts (group) -> Column -> 2D Column -> Clustered Column จะ ได้แผนภูมิดังรูป

4. สามารถสลับ row กับ column โดยไปที่ Design (tab) -> Data (group) -> Switch Row/Column จะได้ผลลัพธ์ดังรูป

(6)

5. หากต้องการใส่ชื่อ chart และรายละเอียดต่างๆ ให้ลองเลือกที่ Design (tab) -> Chart Layouts แล้วลองเลือก Layout 9 จะได้ผลลัพธ์ดังรูป

6. สามารถใส่ชื่อ Chart Title และ Axis Title ทั้งสองแกนได้ตามความเหมาะสม

7. สามารถเปลี่ยนสีหรือตกแต่งลักษณะของแถบที่แสดงข้อมูลได้หลายชนิด เช่น เปลี่ยนสี ท าให้สีที่ ต้องการแสดงมีการไล่เฉดสี หรือการใส่ลวดลาย เป็นต้น ซึ่งสามารถท าโดยการ คลิกเม้าส์ขวาที่กราฟที่ต้องการ จะตกแต่ง -> Format Data Series...

9. สามารถตกแต่งพื้นหลังของ chart ได้ด้วยวิธีการเดียวกันคือ คลิกขวาบริเวณพื้นหลัง -> Format Chart Area.... -> Fill -> Gradient fill จะได้ผลลัพธ์ดังรูป

(7)

เราสามารถเลือกให้สีที่แสดงเป็นการไล่เฉดสีผ่านการเลือกป้าย Gradient โดยสามารถเลือก ที่จะไล่เฉดสีเดียว (One color) หรือสองสี (Two color) และสามารถเลือกลักษณะการไล่เฉดจาก Shading styles ถ้าต้องการใส่ลักษณะของพื้นผิวให้เลือกป้าย Texture แล้วเลือกลายพื้นผิวตามที่ต้องการ 3. การสร้างแผนภูมิวงกลม การสร้างแผนภูมิวงกลม ก็ท าเช่นเดียวกับการสร้างแผนภูมิคอลัมน์ แต่มีข้อระวังคือ การแสดงข้อมูล ของแผนภูมิวงกลม จะมีข้อมูลเพียง 2 มิติ เท่านั้น เช่น ตัวอย่างยอดขายของพนักงานแต่ละคน จะแสดง ทั้งหมด 3 เดือนภายในแผนภูมิวงกลมเดียวกันไม่ได้ ต้องแยกแสดงเป็นเดือน ๆ ในตัวอย่างต่อไปนี้ จะแสดงข้อมูลยอดขายของพนักงานทั้ง 4 คน ในเดือนต.ค.-04 ซึ่งท าได้ดังนี้ 1. จากข้อมูลเดิม ให้ลากแถบสว่างบริเวณข้อมูลที่จะน ามาท าเป็นแผนภูมิวงกลม คือบริเวณ A3:B7 ดังภาพ รูปที่ 9-15

2. จากนั้นเลือก Insert (tab) -> Charts (group) -> Pie -> 2-D Pie -> Pie ผลลัพธ์ที่ได้จะปรากฏ ดังภาพต่อไปนี้

(8)

4. เราสามารถเปลี่ยนสีหรือใส่ลวดลายต่าง ๆ ให้กับข้อมูลได้ โดยการท าเช่นเดียวกันกับการเปลี่ยนสี หรือลวดลายของแผนภูมิคอลัมน์

5. ถ้าต้องการแสดงกราฟวงกลมในลักษณะ 3 มิติ เราสามารถเปลี่ยนแปลงได้โดยการคลิกเมาส์ขวาที่ กราฟแล้วเลือกเมนู Change Chat Type… -> Pie -> Pie in 3-D จะท าให้ได้ผลลัพธ์ดังรูปต่อไปนี้

รูปที่ 9-25  ฝึกปฏิบัติด้วยตนเอง 1. จงสร้างข้อมูลต่อไปนี้ แล้วน ามาสร้างเป็นแผนภูมิคอลัมน์และตกแต่งให้สวยงาม รูปที่ 9-26 2. จงสร้างแผนภูมิวงกลมและตกแต่งให้สวยงาม เพื่อแสดงข้อมูลสถิติผู้ป่วยที่รับการรักษา ประจ าเดือน มกราคม

(9)

คณะวิทยาการสารสนเทศ มหาวิทยาลัยบูรพา รหัสประจ าตัว...ชื่อ-สกุล... กลุ่ม...คณะ...อาจารย์ผู้สอน... ค าถามท้ายปฏิบัติการ 1. จากภาพเป็นการจัดสรรโบนัสตามต าแหน่ง ซึ่งถ้าเป็นพนักงานประจ าจะได้โบนัสและถ้าเป็นพนักงาน Part Time จะไม่ได้โบนัส 1.1 ต าแหน่งเซลล์ C8 ต้องใส่สูตรอย่างไรให้ถูกต้อง เมื่อท าการ Auto Fill ไปจนถึงต าแหน่งเซลล์ C13 ……….. ……….. 2. จากข้อมูลเงินเดือนของพนักงาน จงตอบค าถามต่อไปนี้

(10)

2.1 รายได้รวมทั้งปีของพนักงานแต่ละคนที่เซลล์ G3 ค านวณมาจากสูตร... ... 2.2 โบนัสของพนักงานแต่ละคน (เงื่อนไขการจ่ายโบนัสตามข้อก าหนดด้านล่างที่ให้ไว้) ที่เซลล์ H3 ค านวณมา จากสูตร... ... 2.3 ค านวณหาผู้มีรายได้ทั้งปีต่ ากว่า 200,000 บาท เซลล์ G14 ได้จากสูตร... ... 2.4 ถ้าต้องการหาผลรวมของโบนัสทั้งหมดที่ต้องจ่ายให้กับพนักงานที่เซลล์ H13 ต้องใช้ฟังก์ชัน ...ในการค านวณ และรูปแบบการใช้คือ ...