Designing Excel Input - Calculation - Output Areas (th-TH) - TechNet Articles - United States (English)

ในชีทหนึ่งๆของ Excel 2007 หรือ Excel 2010 มีจำนวน row มากกว่า1 ล้าน row และมีจำนวน column มากกว่า 1 หมื่น column เปิดโอกาสให้ใช้ Excel เก็บข้อมูลได้มากขึ้นกว่าเดิมหลายเท่าตัว แต่โอกาสที่เปิดกว้างนี้กลับทำให้ผู้ที่ใช้ Excel บันทึกข้อมูลอย่างตามใจ สร้างปัญหารุนแรงมากขึ้น พอใช้งานไปได้สักพักก็จะพบว่าแฟ้มเริ่มมีขนาดใหญ่ขึ้นเรื่อยๆแล้วใช้เวลาในการคำนวณนานหลายชั่วโมง ครั้นจะแก้ไขก็ถึงกับต้องเริ่มต้นบันทึกข้อมูลกันใหม่สร้างสูตรกันใหม่ทั้งหมดทีเดียว

ผู้ใช้ Excel ส่วนใหญ่มักใช้ Excel แบบผิดเหมือนๆกัน นั่นคือสร้างตาราง Excel เลียนแบบตามหน้าตารายงานที่ต้องการ มีทั้งข้อมูลที่กรอกลงไปและมีสูตรคำนวณที่สร้างปะปนกันไว้ในตาราง ถ้ารายงานที่ต้องการมี 100 หน้า ก็จะสร้างตาราง Excel ให้มีพื้นที่แสดงทั้ง 100 หน้าลงไป ส่วนรายงานที่ต้องแสดงตามกำหนดเวลาทุกวัน ทุกเดือน ทุกปี ก็มักสร้างตารางแยกแสดงแต่ละวัน แต่ละเดือน แต่ละปี ออกจากกัน ในแฟ้มหนึ่งจึงมีชีทหลายชีท อย่างน้อยก็ต้องมี 12 ชีทตามรายเดือนทั้ง 12 เดือน พอขึ้นปีใหม่ก็เปิดแฟ้มใหม่ขึ้นอีกและในแฟ้มนั้นก็มี 12 ชีท นานไปจะมีแฟ้มที่ต้องเก็บไว้เต็มไปหมด ครั้นจะหายอดตัวเลขคำตอบ ต้องวุ่นวายคัดลอกตัวเลขจากแฟ้มนั้นชีทนี้มาแปะต่อๆกัน หากต้องการปรับสูตรคำนวณ ก็ต้องไล่แก้สูตรที่สร้างไว้ในทุกชีททุกแฟ้มให้เหมือนกันทั้งหมด ถ้าพลั้งเผลอไม่ได้แก้ไขสูตรครบทุกเซลล์ ย่อมทำให้ผลลัพธ์ที่คำนวณได้ผิดบ้างถูกบ้าง พอทำงานแบบเครียดไปว้าวุ่นใจไปได้สักพัก ลาออกก็กลายเป็นทางออกสุดท้าย

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

สมมติว่าในแต่ละหน้ามี 30 บรรทัดพอพิมพ์เลขหน้าที่ 1 ลงไป รายงานก็จะแสดงคำตอบของหน้าแรกตั้งแต่บรรทัดที่ 1 ถึง 30 พอเปลี่ยนเลขหน้าไปเป็นหน้า 2 ก็แสดงบรรทัดที่ 31 ถึง 60 พอเปลี่ยนเลขหน้าไปเป็นหน้า 3 ก็แสดงบรรทัดที่ 61 ถึง 90 แล้วให้ทำเช่นนี้เรื่อยไป โดยผู้ใช้มีหน้าที่เพียงอย่างเดียวคือพิมพ์เลขหน้าที่ต้องการ

(สำหรับผู้ที่สงสัยว่าจะหาเลขบรรทัดเริ่มต้นแต่ละหน้าได้อย่างไร ขอให้ใช้สูตรคำนวณหาเลขเริ่มแต่ละหน้า = ((เลขหน้า –1)*จำนวนบรรทัดต่อหน้า) + 1)

ดาวน์โหลดแฟ้มตัวอย่างที่แสดงวิธีใช้งานตามที่อธิบายนี้ได้จาก http://www.excelexperttraining.com/extreme/files/timeplan/productsummary.xls

องค์ประกอบสำคัญในการออกแบบพื้นที่การใช้ตาราง Excel ต้องเริ่มจากรู้จักแบ่งพื้นที่ตารางเป็น 3 ส่วน เรียกย่อๆว่า I-C-O ดังนี้

1.ตาราง Input หรือ ตารางฐานข้อมูล ใช้สำหรับบันทึกข้อมูลทั้งตัวเลขและตัวอักษรลงไป ห้ามสร้างสูตรลงไปในในตารางนี้ และต้องออกแบบโครงสร้างให้เป็นฐานข้อมูลที่ดี ตามที่ได้อธิบายไว้แล้วในตอนต้น

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

3.ตาราง Output หรือ ตารางรายงาน ใช้สำหรับแสดงผลลัพธ์ในแบบรายงานที่ต้องการให้ปรากฏบนหน้าจอหรือสั่งพิมพ์

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

ในระยะแรกช่วงที่ข้อมูลยังมีปริมาณไม่มากอาจใช้ชีทเดียวสำหรับ I-C-O ทั้งหมด โดยแบ่งให้ใช้พื้นที่ column ด้านซ้ายสำหรับเก็บข้อมูล แล้วใช้ column ส่วนกลางๆสำหรับคำนวณ แล้วส่งผลลัพธ์ออกไปสร้างรายงานจัดหน้าตาตามที่ต้องการไว้ใน column ด้านขวาสุด ต่อมาเมื่อปริมาณข้อมูลเริ่มมากขึ้น ก็ให้ย้ายแยกแต่ละส่วนของ I-C-O ไว้ในชีทของแต่ละส่วนเอง จนสุดท้ายเมื่อแฟ้มมีขนาดใหญ่ขึ้นส่งผลทำให้คำนวณช้าลง จึงย้ายชีทออกไปเป็นแฟ้มที่ลิงค์กันระหว่างแฟ้ม Input แฟ้ม Calculate และแฟ้ม Output

แทนที่จะปล่อยให้ผู้ใช้งานซึ่งอาจเป็นผู้สร้างงานเองหรือไม่ก็ตามมาเปิดใช้แฟ้มแล้วแก้ไปแก้มาจนสูตรที่สร้างไว้ถูกบันทึกข้อมูลทับเสียไป ผู้สร้างงานควรจัดเตรียมพื้นที่ I-C-O แต่ละส่วนให้พร้อมต่อการใช้งาน ดังนี้

1. ตาราง Input หรือ ตารางฐานข้อมูล ให้เลือกเฉพาะพื้นที่ซึ่งจะเปิดให้บันทึกค่าใหม่ทับลงไปได้ แล้วคลิกขวาสั่ง Format Cells > Protection > ตัดกาช่อง Locked ทิ้งไป จากนั้นสั่ง Review > Protect Sheet แล้วจะพบว่าเมื่อกดปุ่ม Tab จะเลื่อน cursor ไปตามเซลล์ที่ไม่ได้ lock ให้เองทีละเซลล์

2. ตาราง Calculate และตาราง Output ควรกำหนดให้พื้นที่ในตารางเหล่านี้ถูกป้องกันไม่ให้บันทึกค่าใหม่ทับลงไปและป้องกันไม่ให้สูตรในเซลล์ปรากฏให้เห็นในช่อง Formula Bar โดยเริ่มจากเลือกพื้นที่ตารางแล้วคลิกขวาสั่ง FormatCells > Protection > กาช่อง Locked ไว้ตามเดิมแล้วกาช่อง Hidden เพิ่มลงไป จากนั้นสั่ง Review > Protect Sheet

นอกจากนั้นควรกำหนดสีในพื้นที่แต่ละส่วนให้ต่างกันไปเช่น เซลล์ Input กำหนดให้ใช้ font สีชมพูส่วนเซลล์สูตรที่ลิงค์ข้ามชีทให้ใช้สีน้ำเงินเซลล์สูตรที่ลิงค์ข้ามแฟ้มมาให้ใช้สีเขียวเป็นต้น อีกทั้งอาจซ่อนชีทที่เป็นตาราง Calculateแล้วสั่ง Review > Protect Workbook ก็จะไม่มีใครสามารถเปิดดูชีทที่ถูกซ่อนไว้เว้นแต่จะกรอกรหัสผ่านได้ถูกต้องก่อน

หากต้องการค้นหาตำแหน่งของเซลล์ที่บันทึกค่าไว้เป็นตัวเลขหรือตัวอักษร หรือเซลล์ที่มีสูตร ให้ใช้วิธีกดปุ่ม F5 > Special แล้วเลือกกาช่องตามประเภทข้อมูลที่ต้องการค้นหา โดยถ้าเลือกเซลล์เดียวไว้ เมื่อใช้คำสั่งนี้จะทำการค้นหาให้ทุกเซลล์ในชีทนั้น หรือถ้าต้องการให้ค้นหาเฉพาะในพื้นที่ที่ต้องการ ต้องเลือกพื้นที่ตารางส่วนที่ต้องการไว้ก่อน