สร้างนาฬิกาดิจิตอลบน Excel (ตอนที่ 2) 3. การเตรียมหน้าปัทม์นาฬิกา ขั้นตอนต่อไปคือการสร้างส่วนแสดงผลนาฬิกา หรือหน้าปัทม์นาฬิกานั่นเอง อย่างที่กล่าวไว้ตั้งแต่ตอนแรก เราสามารถใช้แต่ละเซลของตารางงานมาจัดให้เป็นหน้าปัทม์นาฬิกาได้ ให้เราลองจัดความกว้างความยาวของเซลในแต่ละตำแหน่งของตัวเลขบอกเวลาบนหน้าปัทม์นาฬิกาที่แท็บ Display ดังรูปที่ 1 รูปที่ 1 จะเห็นว่าเลขเวลาแต่ละตำแหน่งจะใช้พื้นที่ความสูง 5 row คือสูงตั้งแต่ row ที่ 2 ถึง row ที่ 6 และจะกว้าง 3 คอลัมน์ เว้น 1 คอลัมน์ (ยกเว้นตำแหน่งที่เป็นจุดคั่นเลขชม.กับเลขนาที และจุดคั่นเลขนาทีกับเลขวินาทีที่จะใช้พื้นที่เพียงเซลเดียว) ในแต่ละเซลที่จะต้องมีการแสดงผล (คือมีการเปลี่ยนสีจากขาวไปดำ) เราจะใช้ Conditional Formatting เป็นตัวกำหนดการเปลี่ยนสี โดยเซลใดที่มีค่าตรรกกะเป็น TRUE เซลจะเปลี่ยนเป็นสีดำ แต่เซลใดมีค่าตรรกกะเป็น FALSE เซลจะเปลี่ยนเป็นสีขาว ซึ่งค่าตรรกกะดังกล่าวนั้นเราจะใช้ฟังก์ชัน VLOOKUP() ในการดึงมาจากตารางข้อมูลในแท็บ DisplayData นั่นเอง ให้ใส่สูตรข้างล่างลงไปที่เซล C2 (ดูรูปที่ 2) =VLOOKUP(Digit1,PatternData!$A$2:$H$11,2) รูปที่ 2 อย่างที่ทราบว่า VLOOKUP() เป็นฟังก์ชันในการดึงข้อมูลจากตารางข้อมูลในพารามิเตอร์ที่สอง (ซึ่งในกรณีนี้คือ PatternData!$A$2:$H$11) โดยใช้ข้อมูลจากพารามิเตอร์แรกเป็นตัวเปรียบเทียบ (Digit1) ส่วนพารามิเตอร์สุดท้ายเป็นลำดับของค่าตรรกกะที่ดึงมาจากแถวที่ค่า Digit1 ตรงกับตำแหน่งของเซลที่แสดงผล ซึ่งก็คือตำแหน่งที่ 2 (หรือเซลแรก) นั่นเอง ค่าในพารามิเตอร์สุดท้ายจะเปลี่ยนไปเรื่อยตั้งแต่ 2 จนถึง 8 ตามตำแหน่งของเซลในตัวเลขตำแหน่งที่ 1 (ดูรูปที่ 3) รูปที่ 3 ใส่สูตรในตัวเลขตำแหน่งอื่นๆด้วย แต่เปลี่ยนจาก Digit1 เป็น Digit2, Digit3, ... จนถึง Digit 6 ระวังพารามิเตอร์สุดท้ายจะต้องเปลี่ยนหมายเลขตามตำแหน่งเซลด้วย (ดูรูปที่ 4 แสดงตัวอย่างสูตรในบางเซล) เมื่อกำหนดสูตรหมดทุกเซล หน้าปัทม์นาฬิกาในขณะนี้จะดูคล้ายกับรูปที่ 5 ที่มีค่าตรรกกะในการแสดงผลเวลาแต่ละตำแหน่งอยู่ เพียงแต่ดูไม่ออกว่าเป็นรูปตัวเลขอะไรอะไร รูปที่ 4 รูปที่ 5 ขั้นตอนต่อไปคือการกำหนด Conditional Formatting ให้กับเซลหน้าปัทม์นาฬิกาทุกตำแหน่ง โดยเลือกเซลหน้าปัทม์นาฬิกาทั้งหมดแล้วใช้เมนูแท็บ Home - Conditional Formatting - Manage Rules... (ดูรูปที่ 6) รูปที่ 6 ที่หน้าจอ Conditional Formatting Rules Manager คลิกปุ่ม New Rule... แล้วเลือก Format Only Cells that Contain แล้วตั้งเงื่อนไข และรูปแบบของเซลตามรูปที่ 7 (จะเห็นว่าเราตั้ง Cell Value Equal to TRUE และทั้งสีพื้นของเซลและสี font เป็นสีดำเหมือนกัน) รูปที่ 7 คลิกปุ่ม New Rule... แล้วทำคล้ายๆกับรูปบน แต่คราวนี้เปลี่ยน Cell Value Equal to FALSE แทน และสีพื้นของเซลและสี font เป็นสีขาวเหมือนกัน เมื่อตั้งเงื่อนไขและรูปแบบเซลเสร็จแล้ว ที่หน้าจอ Conditional Formatting Rules Manager จะเป็นดังรูปที่ 8 รูปที่ 8 เมื่อคลิก OK ออกมา เราจะได้หน้าปัทม์นาฬิกาดังรูปที่ รูปที่ 9 ตรงตำแหน่ง J6 กับ T6 นั้นให้พิมพ์ค่า TRUE ลงไป มันจะเปลี่ยนเป็นสีดำให้ เราจะใช้ตำแหน่งนี้เป็นเครื่องหมายคั่นระหว่างตัวเลขเวลาครับ จากนี้เราได้หน้าปัทม์นาฬิกาที่แสดงเวลาจริงแล้ว แต่ยังไม่ดูเป็นนาฬิกาเท่าไรเนื่องจากว่ามันไม่ได้เปลี่ยนหน้าปัทม์ทุกวินาทีอย่างนาฬิกาทั่วๆไป ต่อจากนี้เราจะใช้ประโยชน์จาก VBA หรือ Visual Basic for Application สักเล็กน้อยเพื่อให้มันทำงานอย่างนาฬิกาทั่วไปได้ในขั้นตอนต่อไป 4. การเขียนโปรแกรมเพื่อให้แสดงเวลาอัตโนมัติ ขั้นตอนนี้เราต้องเปิดเมนูแท็บ Developer ขึ้นมาก่อน โดยคลิกที่เครื่องหมาย Microsoft Office แล้วเลือก Excel Options (ดูรูปที่ 10) จากนั้นเลือกเมนู Popular ด้านซ้าย แล้วคลิกเครื่องหมายถูกที่ "Show Developer tab in the Ribbon" (ดูรูปที่ 11) แล้วคลิก OK รูปที่ 10 รูปที่ 11 ต่อไปเราจะสร้าง VBA สั้นๆสองโปรแกรม ให้กดปุ่ม Alt-11 เพื่อเปิดหน้าจอเขียนโปรแกรม VBA จะปรากฎหน้าจอดังรูปที่ 12 ให้เลือกเมนู Insert และ Module (ดูรูปที่ 13) เพื่อสร้าง module ในการเขียนโปรแกรมให้กับไฟล์ excel ของเรา จากนั้นคีย์โปรแกรมต่อไปนี้ลงไปดังรูปที่ 14 Sub CalcClock() Calculate Application.OnTime Now + _ TimeValue("00:00:01"), "CalcClock" End Sub Sub StopClock() On Error Resume Next Application.OnTime Now + _ TimeValue("00:00:01"), "CalcClock", , False End Sub รูปที่ 12 รูปที่ 13 รูปที่ 14 ปิดหน้าจอเขียนโปรแกรมกลับมาที่แท็บ Display เลือกเมนูแท็บ Developer คลิกปุ่ม Insert ภายใต้ Form Controls เลือก Button (Form Controls) ตามรูปที่ 15 รูปที่ 15 เมื่อเคอร์เซอร์เปลี่ยนเป็นรูปกากบาท ให้สร้างปุ่มใต้หน้าปัทม์นาฬิกา (ตรงไหนก็ได้ อย่าใหญ่มากเพราะเราต้องสร้างสองปุ่ม) เมื่อลากขนาดของปุ่มเสร็จ จะปรากฎหน้าต่างให้กำหนดโปรแกรมแมคโครให้กับปุ่มที่เราเพิ่งสร้าง ให้เลือก CalcClock แล้วคลิก OK (ดูรูปที่ 16) จากนั้นให้เปลี่ยนข้อความบนปุ่มเป็น Start Clock รูปที่ 16 สร้างอีกปุ่มหนึ่งขึ้นมาโดยใช้วิธีการตามรูปที่ 15 และ 16 แต่คราวนี้ให้เลือกแมคโครเป็น StopClock และเปลี่ยนข้อความบนปุ่มเป็น Stop Clock เราจะได้หน้าปัทม์นาฬิกาตามรูปที่ 17 เป็นอันเสร็จสมบูรณ์ โดยเมื่อเราคลิกที่ปุ่ม Start Clock เวลาจะเดิน เมื่อคลิกปุ่ม Stop Clock เวลาจะหยุดครับ รูปที่ 17   Create Date : 13 ธันวาคม 2552     2 comment Last Update : 13 ธันวาคม 2552 18:56:52 น.     Counter : 2784 Pageviews.   Share Tweet

สร้างนาฬิกาดิจิตอลบน Excel (ตอนที่ 1) เทคนิคนี้คิดขึ้นมาในขณะที่ผมกำลังหาสมมติฐาณทางคณิตศาสตร์บางอย่างอยู่ แล้วคิดเลยเถิดต่อมาอีกว่า เราสามารถจะทำตารางงานของเราให้แสดงเป็นนาฬิกาแบบ Digital ได้หรือไม่ จึงเป็นที่มาของเทคนิคนี้ (ซึ่งจนบัดนี้ผมก็ยังไม่รู้ว่าจะเอาไปใช้งานอะไรต่อได้อีก (ใครอ่านแล้วเกิดไอเดียในการใช้งานใหม่ๆขึ้นลองเอามาแลกเปลี่ยนกันก็ดีเหมือนกันครับ) นาฬิกาบนตารางงานที่ผมกล่าวถึงนี้ จะมีหน้าตาคล้ายๆรูปที่ 1 รูปที่ 1 จะสังเกตุเห็นว่าผมจัดให้แต่ละเซลในตารางงานเรียงกันเป็นตำแหน่งต่างๆบนหน้าปัดนาฬิกา ซึ่งตัวเลขแต่ละหลักจะประกอบไปด้วยเซลการแสดงผล 7 เซลตามตำแหน่งในรูปที่ 2 รูปที่ 2 การดัดแปลงตารางงานเพื่อให้แสดงเวลาได้นั้นมีอยู่ 4 ขั้นตอนคือ 1. การเตรียมตารางรูปแบบของตัวเลขบนตารางงาน 2. การเตรียมตารางงานส่วนของการแสดงตัวเลขแสดงเวลา 3. การเตรียมหน้าปัทม์นาฬิกา 4. การเขียนโปรแกรมเพื่อให้แสดงเวลาอัตโนมัติ 1. การเตรียมตารางรูปแบบของตัวเลข การแสดงตัวเลขในแต่ละตำแหน่งบนตารางงานตามรูปที่ 1 นั้น ความจริงก็คือการให้แต่ละเซลในแต่ละตำแหน่งแสดงพื้นสีดำ หรือพื้นสีขาวต่างๆกันไป แล้วแต่ว่ากำลังแสดงตัวเลขอะไร โดยแต่ละเซลนั้นจะมีอยู่ 2 สถานะนั่นคือ แสดงเป็นสีดำ และไม่แสดงสี ซึ่งเราสามารถพูดได้ว่าแต่ละเซลนั้นมีค่าตรรกะ 2 ค่าคือ TRUE (แสดงเป็นสีดำ) และ FALSE (ไม่แสดงสี) ตามลำดับ ในขั้นตอนนี้เราต้องสร้างตารางรูปแบบของตัวเลขแต่ละตัวขึ้นมาก่อนโดยยึดหลักดังกล่าวข้างต้น โดยตามตัวอย่าง ผมได้สร้างตารางรูปแบบตัวเลขเก็บไว้ที่แท็บ PatternData (ดูรูปที่ 3) รูปที่ 3 ดังนั้นหากตำแหน่งนั้นแสดงเลข 2 แต่ละเซลในตำแหน่งนั้นจะมีค่าตรรกกะดังรูปที่ 4 รูปที่ 4 2. การเตรียมตารางงานส่วนของการแสดงตัวเลขแสดงเวลา ขั้นตอนต่อมา ให้สร้างตารางเพื่อแสดงตัวเลขแสดงเวลาในแต่ละตำแหน่งโดย โดยสร้างไว้ที่แท็บ DisplayData (ดูรูปที่ 5) ซึ่งการสร้างตารางตัวเลขแสดงเวลานั้น เราจะดึงรูปแบบของค่าตรรกกะในแต่ละเซลมาจากตาราง PatternData โดยใช้ตัวเลขเวลาแต่ละตำแหน่งที่คำนวณจากเวลาปัจจุบัน รูปที่ 5 ฟังดูแล้วอาจจะงง ลองทำตามขั้นตอนต่างๆตามรูปด้านล่างดีกว่า เริ่มจากขั้นตอนแรกให้สร้างตำแหน่ง ชั่วโมง นาที และวินาที จากเวลาปัจจุบันก่อน ซึ่งเราสามารถใช้ฟังก์ชัน NOW() ควบคู่กับฟังก์ชัน HOUR(), MINUTE() และ SECOND() ได้ โดยใส่ไว้ที่ตารางงานดังรูปที่ 6 รูปที่ 6 ขั้นตอนต่อไปคือการเอาเวลามาถอดเป็นตัวเลขแต่ละตำแหน่ง แล้วใส่ไว้ที่ตารางข้อมูลด้านบนดังรูปที่ 7 ซึ่งจะแสดงสูตรของแต่ละเซล รูปที่ 7 สูตรแต่ละเซลจะใช้ฟังก์ชัน TEXT(), LEFT() หรือ RIGHT() และ VALUE() ประกอบกัน โดยขั้นตอนแรก เราจะดึงตัวเลข 2 ตำแหน่งแรกซึ่งเป็นตัวเลขชั่วโมงมาก่อน เราใช้ฟังก์ชัน TEXT($B$11,"00") เพื่อดึงค่าตัวเลขชั่วโมงจากเซล B11 มาแปลงเป็นตัวเลข 2 ตำแหน่งในรูป text (ที่ต้องแปลงเป็นรูป text เพื่อให้เกิดตัวเลขได้ 2 ตำแหน่ง) จากนั้นใช้คำสั่ง LEFT(..., 1) หรือ RIGHT(..., 1) เพื่อดึงเฉพาะเลขด้านซ้าย หรือขวาตามลำดับออกมา จากนั้นใช้ฟังก์ชัน VALUE(...) เพื่อแปลงตัวเลข (ซึ่งอยู่ในรูป text ในขณะนั้น) กลับเป็นตัวเลขจริงๆอีกทีหนึ่ง การดึงตัวเลข 2 ตำแหน่งถัดมาซึ่งเป็นตัวเลขนาทีนั้น ใช้หลักการเดียวกับการดึงตัวเลขชั่วโมง แต่เปลี่ยนจากเซล $B$11 เป็น $C$11 แทนเท่านั้นเอง จากนั้นก็ดึงตัวเลขวินาทีจากเซล $D$11 ด้วยหลักการเดียวกัน จากนั้นให้ทำการตั้งชื่อแต่ละตำแหน่งเสียหน่อย เพราะเราจะใช้อ้างอิงอีกทีในการสร้างส่วนหน้าปัทม์นาฬิกา ให้เลือกทั้งชื่อ digit และตัวเลข ตามรูปที่ 7.1 แล้วคลิกเมนูแท็บ Formulas คลิกปุ่ม Create from Selection แล้วเลือก Left column จากนั้นคลิกปุ่ม Ok (ดูรูปที่ 7.1) รูปที่ 7.1 ขั้นตอนต่อไปคือการใช้ตัวเลขแต่ละตำแหน่งเพื่อดึงรูปแบบของค่าตรรกกะจากตาราง PatternData โดยใช้ฟังก์ชัน VLOOKUP() (ดูรูปที่ 8) รูปที่ 8 ในฟังก์ชันที่เราใช้ พารามิเตอร์แรกนั้น เราจะใช้ค่าจากเซลที่เก็บตัวเลขของตำแหน่งแรก (digit 1) ซึ่งอยู่ที่เซล B2 มาค้นหาในตารางข้อมูลรูปแบบตรรกกะที่อยู่ที่ตำแหน่ง PatternData!$A$2:$H$11 ซึ่งเมื่อเจอ ให้ดึงค่าจากตำแหน่งที่ตรงกับที่ระบุในเซล C1 แล้วบวก 1 เพื่อให้ตรงกับตำแหน่งคอลัมน์ที่ 2 ในตารางPatternData!$A$2:$H$11 สังเกตดูที่พารามิเตอร์แรก เราจะไม่ใส่ $ ที่หน้าตัวเลขแถว (ในตัวอย่างเป็น $B2) และในพารามิเตอร์สุดท้าย เราจะไม่ใส่ $ ที่หน้าคอลัมน์ (ในตัวอย่างเป็น C$1) เพื่อจะได้ไม่ตรึงคอลัมน์ ทั้งนี้เพื่อให้สามารถก๊อบปี้สูตรไปยังตำแหน่งอื่นๆในตาราง DisplayData ได้ในขั้นตอนต่อไป ขั้นตอนต่อไปคือก๊อบปี้สูตรไปทางขวาให้ครบ 7 เซลตามรูปแบบตรรกกะของตัวเลขแต่ละตำแหน่ง และก๊อบปี้สูตรทั้ง 7 เซลลงด้านล่างให้ครบ 6 ตำแหน่งของเวลาที่จะแสดง (ดูรูปที่ 9 และ 10 ตามลำดับ) รูปที่ 9 รูปที่ 10 <ยังมีต่อในตอนที่ 2>   Create Date : 09 ธันวาคม 2552     1 comment Last Update : 13 ธันวาคม 2552 18:57:57 น.     Counter : 4087 Pageviews.   Share Tweet

กราฟเลื่อนได้โดยใช้ปุ่ม Spin Button เทคนิคนี้ผมเขียนเพิ่มเติมขึ้นมาจาก เทคนิค "สร้างกราฟที่ปรับช่วงเวลาของการแสดงข้อมูลโดยอัตโนมัติ" ซึ่งอยู่ที่ลิงก์นี้ สร้างกราฟที่ปรับช่วงเวลาของการแสดงข้อมูลโดยอัตโนมัติ โดยเราจะปรับปรุงกราฟให้เปลี่ยนแปลงช่วงเวลาที่จะแสดงกราฟโดยการกดปุ่ม ซึ่งจะทำให้ย้อนกลับเพื่อดูกราฟได้อย่างง่ายๆตามตัวอย่างในรูปที่ 1 รูปที่ 1 เพื่อที่จะทำอย่างนั้นได้ เราต้องใช้ออปเจคในการออกแบบซึ่งจะอยู่ในแท็บ Developer โดยปรกติแท็บนี้จะไม่แสดงบนหน้าจอของ Excel 2007 เราจะต้องเปิดมันขึ้นมาเองโดยคลิกไปที่เครื่องหมาย Microsoft Office ที่มุมบนขวา แล้วคลิกที่ปุ่ม Excel Options (ดูรูปที่ 2) รูปที่ 2 ที่รายการเมนูของ Excel Options คลิกที่รายการ Popular แล้วติ๊กเครื่องหมายถูกที่ "Show Developer Tab in the Ribbon" (ดูรูปที่ 3) ซึ่งเมื่อคลิกปุ่ม OK เพื่อกลับมาที่หน้าตารางงาน เราจะพบว่ามีแท็บ Developer เกิดขึ้นมาที่บนเมนู (ดูรูปที่ 4) รูปที่ 3 รูปที่ 4 ให้คลิกแท็บ Developer แล้วคลิกปุ่ม Insert บนริบบอน เลือก Spin Button จากกลุ่ม Form Controls (ดูรูปที่ 5) ลูกศรของเมาส์จะเปลี่ยนเป็นกากบาท เพื่อให้เราใส่ Spin Button ลงในตำแหน่งที่ต้องการ ในตัวอย่างนี้ให้ใส่ที่เซล J1 ครับ ซึ่งจะเป้นเซลข้างๆคำว่า Today (ดูรูปที่ 6) จากนั้นจัดขนาดของคอลัมน์ตามความเหมาะสมปุ่ม Spin Button จะประขนาดตามขนาดเซลเอง รูปที่ 5 รูปที่ 6 คลิกขวาที่ปุ่ม Spin Button ที่เพิ่งใส่ลงไปแล้วเลือกเมนู Format Control... (ดูรูปที่ 7) จะปรากฎหน้าจอ Format Control ขึ้นมา ให้ตั้งค่าต่างๆตามรูปที่ 8 รูปที่ 7 รูปที่ 8 โปรดสังเกตุว่าเราจะตั้ง Current Value และ Maximum Value เป็น 365 เนื่องจากเราต้องการให้ปุ่มนี้เลื่อนวันย้อนหลังกลับได้ 1 ปี ถ้าต้องการให้ย้อนหลังได้น้อยลง ก็ให้ระบุเป็นจำนวนวันที่ย้อนหลังที่ต้องการ เช่น 30 วัน ส่วน Cell Link เป็นการกำหนดเซลที่จะเอาไว้เก็บ ค่าปัจจุบัน (Current Value) ที่เกิดจากการกดปุ่ม Spin Button ดังกล่าว ซึ่งเราตั้งค่าให้เริ่มที่ 365 และเป็นค่า Maximum Value ด้วย (หมายถึงกดปุ่มขึ้นก็จะเพิ่มค่าได้ไม่เกิน 365 และกดลงไปได้เรื่อยๆจนถึง 0) สาเหตุที่เราใส่ Cell Link เป็น $J$1 ซึ่งเป็นตำแหน่งเซลเดียวกับที่เราวางปุ่มก็เพื่อซ่อนค่ามันไว้ใต้ปุ่มเพื่อไม่ให้มองเห็นนั่นเอง ขั้นตอนต่อไปเป็นขั้นตอนที่สำคัญ โดยเราจะต้องเปลี่ยนสูตรในเซล I2 ซึ่งเดิมเป็นสูตร =Today() ให้เป็นสูตร =Today()-365+J1 (ดูรูปที่ 9) รูปที่ 9 การใส่ -365 ซึ่งเป็นจำนวนวันที่เราต้องการให้ย้อนหลังได้สูงสุดตามที่กำหนดใน Current Value และ Maximum Value ก็เพื่อให้ปุ่มที่กดนั้นคำนวณเวลาแบบย้อนกลับได้เมื่อกดลูกศรลง และเดินหน้าได้เมื่อกดลูกศรขึ้น แต่เดินหน้าได้ไม่เกินวันที่ปัจจุบัน ลองกดปุ่มขึ้นลงดูจะพบว่ากราฟจะเปลี่ยนแปลงโดยอัตโนมัติ โดยจะเดินหน้าและถอยหลังตามที่เรากดปุ่ม ซึ่งสะดวกกว่าการใส่ข้อมูลวันที่ลงเซล I2 โดยตรง (ดูรูปที่ 10) รูปที่ 10   Create Date : 30 พฤศจิกายน 2552     1 comment Last Update : 3 ธันวาคม 2552 12:06:29 น.     Counter : 1723 Pageviews.   Share Tweet

ใช้ Excel ทำ Workplan ที่มี Barchart กัน แม้จะมี Microsoft Project เป็นเครื่องมือช่วยในการทำ Workplan แต่เชื่อว่าหลายคนก็คงเหมือนผม คือถนัดที่จะใช้เครื่องมือที่มีอยู่เช่น Microsoft Excel สร้าง Workplan อย่างง่ายๆมาใช้ ซึ่งในบางกรณีนั้น สะดวก และรวดเร็วกว่าใช้ซอฟท์แวร์ที่เทอะทะอย่าง Microsoft Project เสียด้วยซ้ำ ผมมีเทคนิคที่จะทำให้ Workplan บน Excel ของเราทำงานได้ไกล้เคียงกับซอฟท์แวร์ที่ทำงาน Planning โดยตรงได้ (อย่างน้อยก็เรื่อง Barchart ของงานแหละครับ) ตัวอย่าง Workplan แบบง่ายๆบน Excel ก่อนอื่นต้องรู้จักคุณสมบัติของ Conditional Formatting ของ Excel ก่อน ประโยชน์ของมันก็คือ เราสามารถจัดฟอร์แมตของเซล หรือกลุ่มของเซลโดยใช้เงื่อนไขในเซลของมันเอง หรือจากเซลอื่นๆได้ ตัวอย่างเช่นข้อมูลอยู่ยอดขายอยู่ 5 บรรทัด อยากจะให้มีการ highlight ข้อมูลยอดขายตั้งแต่ 15000 บาทโดยอัตโนมัติเราก็เพียงแต่เลือกเซลข้อมูลทั้งหมด แล้วเลือกเมนู Home --> Conditional Formatting --> Highlight Cells Rules --> More Rules... Conditional Formatting 1 จากนั้นตั้งค่าตามรูปข้างล่าง แล้วคลิก OK Conditional Formatting 2 ตารางข้อมูลเราก็จะมีการ highlight สีตามที่ต้องการ Conditional Formatting 3 ทดลองเปลี่ยนค่าในตารางดูก็ได้ จะพบว่ามันจะ highlight ใหม่ตามเงื่อนไขที่เราตั้งไว้โดยอัตโนมัติ Conditional Formatting 4 คราวนี้เราอาจจะอยากให้มัน Highlight ทั้งบรรทัดในตาราง ก็สามารถทำได้แต่ต้องปรับปรุงการตั้งค่า Conditional Formatting นิดหน่อย เริ่มจากเลือกเซลแรกของคอลัมน์ พนักงานขาย แล้วเลือกเมนู Home --> Conditional Formatting --> Highlight Cells Rules --> More Rules... แล้วตั้งค่าตามรูป (สังเกตุให้ดีว่ามีการเปลี่ยน Rule Type ไปใช้ Use a formula to determine which cells to format) มีการใส่สูตรลงในช่อง Format values where this formula is true: ดังนี้ =$B2>=15000 Conditional Formatting 5 เมื่อคลิก OK จะพบว่าเซลแรกของคอลัมน์พนักงานขายจะเปลี่ยนรูปแบบตามที่ต้องการแล้ว *** สำคัญมาก *** สังเกตุการใช้สูตรให้ดีๆ เราจะใส่ $ ไว้ข้างหน้าเลขคอลัมน์ B เท่านั้น แต่ไม่ใส่ไว้ที่เลขแถว ทั้งนี้เพื่อใช้ในขั้นตอนต่อไปซึ่งเป้นการก๊อปปี้ฟอร์เมตของเซลนี้ไปที่บรรทัดอื่นๆ สูตรจะเปลี่ยนแปลงการเเปรียบเทียบเซลในคอลัมน์ยอดขายให้เราเอง (เช่นจาก $B2>=15000 เป็น $B3>=15000 และ $B4>=15000 ตามลำดับ) ขั้นตอนต่อไปเป็นการก๊อปปี้ฟอร์เมตของเซลแรกของคอลัมน์พนักงานขายไปยังแถวอื่นๆในคอลัมน์เดียวกัน ให้กดปุ่ม Ctrl-C เพื่อก๊อปปี้เซลที่เพิ่งตั้งค่า Format ข้างต้น แล้วเลือกเซลที่เหลือของคอลัมน์พนักงานขายแล้วคลิกเมาส์ขวา เลือก Paste Special --> Formats Conditional Formatting 6 เมื่อคลิก OK ทั้งบรรทัดก็จะ highlight ตามเงื่อนไขในค่าในคอลัมน์ยอดขาย Conditional Formatting 7 ขั้นตอนต่อไป เราจะใช้ Condition Formatting นี่แหละในการสร้าง Barchart ที่จะแสดงโดยอัตโนมัติเมื่อเรากำหนดช่วงเวลาของ Task ที่เราต้องการ แรกสุด ให้สร้างตารางรายการ Task ที่ต้องดำเนินการตามแผนงานของเรา ขึ้นมาก่อน โดยแต่ละ Task มีคอลัมน์ Manday ที่ต้องใช้ในแต่ละ Task วันเริ่มงาน (start date) และวันสิ้นสุดงานในแต่ละ Task ตามรูป Workplan 1 ขั้นตอนต่อไปคือ สร้างหัวตารางด้านบนให้เป็นวันที่ตามแผน โดยในตัวอย่างนี้ผมสร้างตารางแผนงานรายวัน เพื่อจะได้ง่ายต่อการทำความเข้าใจสูตรที่จะใช้ จากหัวตาราง ผมใส่วันเริ่มต้นของแผนงานที่เซล F2 เป็นวันที่ 31-Aug-09 ส่วนในเซลข้างขวาต่อไปก็เพียงแต่ใส่สูตร =F2+1 แล้วก๊อปปี้สูตรไปตามด้านขวาเรื่อยๆตามแต่ที่ต้องการจะสร้าง จัดรูปแบบเสียหน่อยให้แสดงแค่วันที่กับเดือนก็ได้ครับ ในตัวอย่างผมจะทำสัก 15 วัน Workplan 2 Workplan 3 ขั้นตอนต่อไป ลองใส่วันที่เริ่มทำงาน (Start Date) ที่บรรทัด Conceptual Design แล้วใส่จำนวนวันที่จะใช้ (Duration Day) สมมติว่าเป็น 31-Aug-09 และ 2 วันตามลำดับ ส่วนช่อง End Date ให้ใส่สูตร =C5+B5-1 Workplan 4 ขั้นตอนต่อไป เป็นการใช้ประโยชน์จาก Conditional Formatting โดยเราใช้หลักการที่ว่า ถ้าเซลไหนไม่เป็นเซลว่า เราจะให้กลายเป็นสีน้ำเงินทั้งเซล เลือกเซล F5 จนถึง U5 แล้วกำหนด Conditional Formatting ดังรูป Workplan 5 ช่องหลัง Containing นั้นความจริงไม่ใช่ช่องว่า แต่มีการเคาะ spacebar หนึ่งทีที่ช่องนี้ครับ (ความหมายคือมี space 1 ตัวอักษรนั่นเอง) กำหนดสูตรลงในเซล F5 ดังนี้ (ดุรูป Workplan 6) =IF(AND(F$2>=$C5,F$2<=$D5)," ","") ความหมายของสูตรนี้ก็คือ หากวันที่บนเซลหัวตาราง (row 2) มีค่าอยู่ระหว่า Start Date กับ End Date ก็ให้ใส่ space ไว้ 1 ตัวอักษร ถ้าไม่ก็ไม่ใส่อะไร ระวังการใช้เครื่องหมาย $ ในสูตรด้วย เพราะเราจะใช้การก๊อปปี้สูตรไปที่เซลอื่นทางขวาจนถึงเซล U5 (Workplan 7) และก๊อปปี้ลงไปทั้งแถวจนถึงเซล U9 (Workplan 8) Workplan 6 Workplan 7 Workplan 8 จะเห็นว่า ในบรรทัด ่Conceptual Design นั้น จะมีเซลที่เป็นสีน้ำเงินอยู 2 เซล ตรงตำแหน่งวันที่เดียวกับ Start Date และ End Date ลองกำหนดงานในบรรทัดอื่นๆเข้าไป โดยที่วัน Start Date ของบรรทัดถัดไป อาจจะใช้การคำนวนจาก End Date ของบรรทัดบน+1 ก็ได้ (ดูรูป Workplan 9) ส่วนคอลัมน์ End Date ของแต่ละแถว ให้ก๊อปปี้สูตรจากบรรทัดด้านบนคอลัมน์เดียวกันลงมา เราจะได้ Bar Chart ด้านขวามือโดยอัตโนมัติ (ดูรูป Workplan 10) Workplan 9 Workplan 10 ฺBarchart ด้านขวาในแผนนั้น จะเปลี่ยนตามวันที่เรากำหนด Start Date และ End Date ครับ Free TextEditor   Create Date : 10 พฤศจิกายน 2552     7 comment Last Update : 10 พฤศจิกายน 2552 0:22:58 น.     Counter : 7371 Pageviews.   Share Tweet