Microsoft Excel Tips and Tricks: หาค่าสุดท้าย

คิดว่าหลายท่านคงเจอปัญหากับการหาค่าสุดท้ายในบรรทัด หรือในคอลัมน์กันบ้าง ซึ่งคงต้องปวดหัวมากหากต้องมาหาด้วยตาเองทุกครั้่ง การหาค่าสุดท้ายในบรรทัดหรือคอลัมน์นั้นก็มีให้เลือกหลายสูตรครับ แต่ผมขอแนะนำเพื่อให้นำไปใช้งานได้แบบง่าย ๆ กันสัก 3 สูตรแล้วค่อยประยุกต์ให้เข้ากับงานของตนต่อไปครับการหาตัวเลขสุดท้าย=Lookup(9.99999999999999e307,A:A)เมื่อ A คือคอลัมน์ที่ต้องการหาค่าสุดท้าย และ=Lookup(9.99999999999999e307,5:5)เมื่อบรรทัดที่ 5 คือบรรทัดที่ต้องการหาค่าสุดท้ายการหาตัวอักษรสุดท้าย=Lookup(Char(255),A:A)เมื่อต้องการหาค่าสุดท้ายที่เป็นตัวอักษรในคอลัมน์...

Read More

Microsoft Excel Tips and Tricks: การป้องกันการคีย์ค่าซ้ำ

บางข้อมูลอาจจำเป็นต้องจัดเก็บไว้เพียง Record เดียวเท่านั้น หากเราไม่ป้องกันการคีย์ค่าซ้ำไว้ จะเสียเวลามาตรวจสอบเสมอว่ามีค่าที่กำลังบันทึกอยู่แล้วหรือไม่ แต่ถ้ากำหนดได้ว่าถ้าซ้ำแล้วให้มีข้อความเตือน จะทำให้ประหยัดเวลาในการตรวจสอบได้มากยกตัวอย่างต้องการป้องกันการคีย์ค่าซ้ำในเซลล์ A2:A20เราสามารถป้องกันการคีย์ค่าซ้ำได้ดังนี้คลุมช่วงข้อมูลที่ต้องคีย์ เช่น A2:A20เข้าเมนู Data เลือก Validationเลือกแถบ Settingsที่ Allow เลือก Customที่ Formula box คีย์สูตร=COUNTIF($A$2:$A$20,A2)=1เลือกแถบ Error Alertตรง Title box กรอก "คีย์รายการซ้ำ"ในช่อง Error message...

Read More

Microsoft Excel Tips and Tricks: แทรกบรรทัดผลรวมสูตรไม่ขยายตามจะทำอย่างไร?

ปกติเมื่อเราแทรกบรรทัดที่เป็นผลรวมแล้วมีการกรอกตัวเลขเพิ่ม จะต้องมาแก้ไขสูตรผลรวมใหม่เพื่อให้รวมบรรทัดที่แทรกด้วยเสมอ ยกตัวอย่างเช่น A1:A5 มีข้่อมูลดังนี้52348และที่ A6 เขียนสูตร=Sum(A1:A5)หากเราแทรกที่บรรทัด A6 แล้วคีย์ตัวเลขใด ๆ สูตรใน A7 จะไม่รวมตัวเลขที่เพิ่งคีย์ไปล่าสุด และยังคงแสดงเป็น=Sum(A1:A5)แต่ทั้งนี้เราต้องการให้แสดงเป็น=Sum(A1:A6)หากต้องการให้สูตรขยายตามและรวมยอดที่คีย์เข้ามาล่าสุดให้ด้วย ต้องแทรกในบรรทัดก่อนบรรทัดยอดรวมเสมอ ซึ่งอาจไม่สะดวกในการทำงานบางงาน และเนื่องจากมีการแทรกบรรทัดเพิ่มเข้ามาแล้วไม่ว่าจะแทรกบรรทัดใด...

Read More

Microsoft Excel Tips and Tricks: การปัดเศษตามจำนวนที่ต้องการ

การปัดเศษแบบปกติใน Excel สามารถใช้ฟังก์ชั่นเกี่ยวกับการปัดเศษ ซึ่งได้แก่RoundRoundupRounddownCeilingFloorIntTruncแต่หากต้องการปัดตามจำนวนที่ต้้องการ ยกตัวอย่างเช่นปัดคราวละ 5 หมายถึงว่าหากหลักหน่วยมากกว่าหรือเท่ากับ 2.5 ให้ปัดเป็น 5 ถ้าน้อยกว่านั้นให้ปัดเป็น 0 สามารถประยุกต์ใช้ฟังก์ชั่น Round ได้ดังนี้ครับสมมุติค่าที่ต้องการปัดอยู่ที่ A1 ที่เซลล์ B1 คีย์=Round(A1/5,0)*5สูตรด้านบนนี้ต่างจากสูตร=Ceiling(A1,5)เนื่องจาก Ceiling จะปัดขึ้นเป็นจำนวนเท่าของ 5 เสมอและต่างจาก=Floor(A1,5)เนื่องจาก Floor จะปัดลงเป็นจำนวนเท่าของ 5 เสมอสำหรับฟังก์ชั่นการปัดคราวละตามที่กำหนดสามารถใช้ Mround ได้อีกฟังก์ชั่นหนึ่ง...

Read More

Microsoft Excel Tips and Tricks: เทคนิคระดับเทพ

ผมทราบว่าหลายท่านรอคอยที่จะให้ผมเขียนทิป เทคนิคในการทำงานกับ Excel และนี่ก็เป็นบางส่วนที่ช่วยให้การทำงานกับ Excel เต็มไปด้วยความสนุกสนาน และหากใครต้องการเป็นผู้เชี่ยวชาญด้าน Microsoft Excel ความรู้ตามด้านล่างนี้เป็นสิ่งที่ท่านควรทราบ เพราะจะทำให้ท่านทำงานได้อย่างรวดเร็วในการจัดการกับข้อมูลใน "ระดับเทพ" สำหรับเทคนิคตามด้านล่างนี้ใช้กับ Excel 2003 เป็นหลักครับ การเลือกข้อมูลเราสามารถเลือกเซลล์ที่ไม่ติดกันโดยคลิกเซลล์แรกของข้อมูลแล้วกดแป้น...

Read More

Microsoft Excel Tips and Tricks: การรวมค่าจากการปัดเศษด้วยสูตรเดียว

ปกติหากเราต้องการหาค่ารวมจากการปัดเศษ จะต้องเขียนสูตร Round ก่อนแล้วค่อย Sum ค่าที่ได้ทั้งหมดออกมาอีกทีการใช้วิธีลัดเพื่อรวมค่าที่ปัดเศษทีเดียวทั้งหมดโดยไม่ต้องเขียนสูตร Round ไว้ก่อนจะทำให้ประหยัดเวลา ประหยัดเซลล์ได้มาก ยกตัวอย่างเช่น A2:A6 ประกอบด้วยA2: 2.354A3: 3.653A4: 3.765A5: 9.228A6: 4.773หากปัดให้เหลือทศนิยม 2 หลักทั้งหมดก่อนแล้วค่อยรวม ที่เซลล์ใด ๆ สามารถใช้สูตร Array ดังนี้=Sum(Round(A2:A6,2))Ctrl+Shift+Enterหรือหากไม่ต้องการใช้สูตร Array แบบกำหนดเอง ก็สามารถใช้สูตร Array ธรรมชาติแบบด้านล่างเพื่อไม่ต้องกด Ctrl+Shift+Enter=Sumproduct(...

Read More

Microsoft Excel Tips and Tricks: การเติมข้อมูลด้วยเซลล์ก่อนหน้า

การเติมช่องว่างด้วยเซลล์ก่อนหน้ามีประโยชน์มากกับท่านที่กำลังจะทำฐานข้อมูล เนื่องจากข้อมูลที่ได้มาอาจไม่อยู่ในรูปแบบฐานข้อมูลที่สมบูรณ์ อันได้แก่ข้อมูลที่ได้มาจากการดึง SAP, Oracle, Web หรือโปรแกรมอื่น ๆ ซึ่งได้จัดกลุ่มหรือดึงมาแบบมีเงื่อนไข ทำให้เกิดเซลล์ว่างอยู่ภายใต้หัวข้อ และเราจำเป็นต้องเติมช่องว่างด้วยหัวข้อดังกล่าว สามารถทำใน Excel ด้วยวิธีการตามด้านล่างสมมุติข้อมูล A1:A10 ดังนี้A1: AppleA3:A4: BananaA5:A6: CoconutA7:A8:A9: OrangeA10:A. วิธีการเติมข้อมูลด้วยเซลล์ก่อนหน้าเลือก A1:A10เข้าเมนู Edit > Go to หรือกดแป้น F5Special > Blanks...

Read More

Microsoft Excel Tips and Tricks: การสั่งให้คำนวณแผ่นงานหรือสมุดงาน

ในการสั่งให้ Excel ทำการคำนวณสามารถใช้ Shortcut ดังข้างล่างนี้กด F9 จะทำการคำนวณสูตรที่มีการเปลี่ยนแปลงหลังจากการคำนวณครั้งสุดท้ายในสมุดงานที่เปิดอยู่ทั้งหมด ถ้าสมุดงานถูกตั้งค่าเป็นการคำนวณอัตโนมัติ ก็ไม่จำเป็นต้องกดเป็น F9 เพื่อคำนวณกด SHIFT+F9 จะทำการคำนวณสูตรที่มีการเปลี่ยนแปลงหลังจากการคำนวณครั้งสุดท้ายบนแผ่นงานปัจจุบันกด CTRL+ALT+F9 จะทำการคำนวณสูตรทั้งหมดในสมุดงานทั้งหมดทุกสมุดงานที่กำลังทำงาน โดยไม่สนใจว่าจะมีการเปลี่ยนแปลงจากครั้งสุดท้ายที่มีการคำนวณหรือไม่กด CTRL+SHIFT+ALT+F9 จะทำการตรวจสอบสูตรที่อ้างถึงอีกครั้ง...

Read More

Microsoft Excel Tips and Tricks: การแสดงรายการโดยไม่รวมค่าที่ซ้ำ

กรณี ที่เรามีข้อมูลซึ่งประกอบด้วยค่าที่ซ้ำกันจำนวนมาก หากต้องการที่จะให้โปรแกรมแสดงรายการทั้งหมดโดยไม่รวมค่าที่ซ้ำ สามารถใช้ Advanced Filter ทำได้ดังนี้สมมุติข้อมูล A1:A10 มีดังต่อไปนี้A1: FruitsA2: AppleA3: CherryA4: PearA5: CherryA6: PlumA7: AppleA8: AppleA9: PearA10: Appleคลิกเมนู Data > Filter > Advanced Filterตรง Action เลือก Copy to another locationคลิกในช่อง List Range ลากคลุม $A$1:$A$10คลิก Unique records onlyคลิกในช่อง Copy to และคลิกเซลล์ B1OKโปรแกรมจะแสดงข้อมูลที่คอลัมน์ B ดังข้างล่างนี้B1: FruitsB2: AppleB3: CherryB4: PearB5:...

Read More

Microsoft Excel Tips and Tricks: เกี่ยวกับการคำนวณในสมุดงาน

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

Read More

Microsoft Excel Tips and Tricks: การ List รายชื่อ Sheet

เนื่อง จากมีคำถามเกี่ยวกับวิธีการ List รายชื่อ Sheet ค่อนข้างบ่อย และ Pantip เองก็มีการลบกระทู้ค่อนข้างบ่อย ผมก็เลยต้องตอบคำถามเดิม ๆ ค่อนข้างบ่อยเป็นลูกโซ่ เลยนำมาเขียนไว้ที่นี่เพื่อจะได้ใช้อ้างอิง ไม่ต้องเขียนใหม่ หรือ Copy มาแปะทุกครั้งที่เจอคำถาม สำหรับ วิธีการนี้ใช้ Excel 4 Macro Function มาช่วย ซึ่งต้องกำหนด Security ในเมนู Tools > Macro > Security เป็นระดับ Medium หรือ Low ถึงจะใช้ได้ วิธีการทำดังนี้ครับ เปิดไฟล์ที่ต้องการ...

Read More

Microsoft Excel Tips and Tricks: เราจะซ้อน IF มากกว่า 7 ชั้นได้อย่างไร?

Excel ตั้งแต่เวอร์ชั่น 2003 ลงไปสามารถซ้อน IF ได้แค่ 7 ชั้นซึ่งบางคนก็อยากได้มากกว่านั้น Excel 2007 จึงให้มาถึง 64 ชั้น และคิดว่าน่าจะเพียงพอแก่การงง และแม้ว่า Excel 2003 จะสามารถซ้อน IF ได้แค่ 7 ชั้นก็ตาม เรายังสามารถประยุกต์ให้สามารถซ้อนได้เกิน 7 ชั้นได้อย่างไม่ลำบาก แค่เพียงเราเข้าใจสิ่งเหล่านี้ก่อน ซึ่งขอยกมาเป็นเพียงตัวอย่างคือTrue+0 = 1False+0 = 0การใช้ &ข้อ 1. และ 2. เพิ่งรู้ใช่ไหมล่ะ อืม...ขอถามกันก่อนว่า แล้วเราจะใช้ IF เกิน 7 ชั้นไปทำไม...

Read More

Microsoft Excel Tips and Tricks: วัน เวลา ใน Excel

จั่วหัวเรื่องวันเวลาเป็นเรื่องที่น่าสนใจและไม่ใช่เรื่องง่าย ๆ สำหรับเราชาวพลพรรค Excel เพราะจะต้องเข้าใจตามหัวข้อเหล่านี้เป็นตัวอย่างวัน เวลา ถูกเก็บอยู่ในรูปแบบตัวเลขExcel จะเริ่มรับรู้วันแรกคือ 01/01/1900 ซึ่งเริ่มนับลำดับ 1 สำหรับวันปัจจุบันเป็นลำดับที่เท่าไร ทดสอบง่ายๆ คือในเซลล์ใดๆ คีย์ =Today() แล้วจัดรูปแบบเซลล์เป็น Generalการนำวัน เวลา มาบวกลบกันสามารถทำได้เหมือนตัวเลขธรรมดา แต่หากนำวันที่ที่น้อยกว่าเป็นตัวตั้ง แล้วนำวันที่ที่มากกว่ามาเป็นตัวหัก...

Read More

Microsoft Excel Tips and Tricks: ลำดับการคำนวณใน Excel

ตัวดำเนินการในการคำนวณการทราบถึงตัวดำเนินการและลำดับความสำคัญของตัวดำเนินการที่ใช้ในการคำนวณของ Excel จะทำให้เราใช้ฟังก์ชั่นและสูตรด้วยความเข้าใจ สามารถที่จะลดความยาวของสูตรหรือฟังก์ชั่นได้ เช่น ลดวงเล็บให้เหลือเท่าที่จำเป็น เป็นต้นตัวดำเนินการเป็น ตัวกำหนดรูปแบบของการคำนวณที่ใช้ในสูตร ซึ่ง Excel มีรูปแบบตัวดำเนินการ 4 รูปแบบที่แตกต่างกันคือ การคำนวณตัวเลข, การเปรียบเทียบ, อักขระ, และการอ้างอิงรูปแบบของตัวดำเนินการ รูปแบบในการคำนวณตัวเลขหรือตัวดำเนินการทางคณิตศาสตร์...

Read More

Microsoft Excel Tips and Tricks: ถ้ามีค่าผิดพลาดจะรวมยอดอย่างไร?

ปัญหานี้ก็น่าสนใจมาก ในการทำงานของเรามักพบว่า บ่อยครั้งที่ผลลัพธ์ตามฟังก์ชั่นที่เราเขียนขึ้นเป็น #N/A หรือ #DIV/0!การหาผลรวมโดยใช้ฟังก์ชั่น Sum ธรรมดาก็ไม่สามารถหาผลลัพธ์ได้ เอ... แล้วเราจะทำอย่างไรกันดี ในเมื่อไม่สามารถหาด้วยฟังก์ชั่น Sum ได้ เราก็ลองหาฟังก์ชั่นอื่นหรือหาทางประยุกต์ดูครับ ลองนึกสิครับว่าฟังก์ชั่นไหนบ้างที่เป็นการรวมตามเงื่อนไข หลังจากนึกถึงการประยุกต์ใช้ฟังก์ชั่นจนหน้ามืดได้ที่แล้ว ก็มาดูตัวอย่างการประยุกต์ฟังก์ชั่น Sum...

Read More

Microsoft Excel Tips and Tricks: การ Copy หลายๆ ครั้งแล้ว Paste

Microsoft Office 2003 เป็นต้นมา เพิ่มความสามารถที่โดดเด่นหลายประการ ประการหนึ่งที่สำคัญมาก คือ การเก็บข้อมูลไว้ใน Clipboard ซึ่งเป็นเสมือนความจำสำรอง โดยสามารถเก็บความจำที่ทำการ Copy ไว้ได้ถึง 24 ครั้ง เป็นประโยชน์ต่อการทำงาน ช่วยประหยัดเวลาในทำงานได้มากทีเดียวการใช้งานก็สามารถพลิกแพลงได้มากมาย เป็นต้นว่าสามารถ Copy จากการ Filter เฉพาะข้อมูลที่ต้องการ แล้วค่อยวางทีเดียว หรือ Copy จาก Microsoft Office ด้วยกัน เช่น Word แล้วไปวางใน PowerPoint หรือ...

Read More

Microsoft Excel Tips and Tricks: Edit > Go to > Special

สำหรับ Excel 2003 แล้ว เมนู Edit เป็นเมนูที่สำคัญทีเดียวสำหรับผู้ที่จะใช้ Ms Excel อย่างมืออาชีพ สำหรับ Edit > Go to > Special เป็นการเข้าถึงข้อมูลตามที่ต้องการ ได้แก่Comment หมายถึงการเข้าถึงเซลทุกเซลล์หรือเฉพาะเซลล์ในพื้นที่ที่ถูกเลือก ซึ่งมี Comment ปรากฏอยู่Constants หมายถึงการเข้าถึงค่าคงที่ในเซลล์ทุกเซลล์ หรือเฉพาะเซลล์ในพื้นที่ที่ถูกเลือก ซึ่งมีค่าคงที่ปรากฏอยู่ ค่าคงที่ดังกล่าวมี Option ให้เลือก 4 แบบว่าจะเลือกค่าคงที่ที่เป็นตัวเลขอักขระค่าตรรรกะค่าผิดพลาดFormulas หมายถึงการเข้าถึงเซลล์ทุกเซลล์หรือเฉพาะพื้นที่ที่ถูกเลือก ที่มีสูตรหรือที่มีฟังก์ชั่นปรากฏอยู่...

Read More

Microsoft Excel Tips and Tricks: การส่งอีเมลด้วย Excel

ปกติเวลาส่งอีเมล เรามักจะเปิดโปรแกรมสำหรับรับ-ส่งอีเมลโดยเฉพาะขึ้นมา แล้วค่อยส่งอีเมล หรือส่งอีเมลทางเวบเมลต่างๆใน Excel 2003 หรือ Ms Office 2003 อื่นๆ สามารถส่งเมลจากตัวโปรแกรมได้เลย ไม่ต้องเสียเวลาเปิดโปรแกรมรับ-ส่งเมล โดยเข้าไปที่เมนู File > Send To > เลือกว่าจะส่งวิธีไหนMail Receipient สามารถส่งแบบ Attach ทั้งไฟล์ หรือเฉพาะ Sheet ที่กำลังเปิด ซึ่งใช้ดูอย่างเดียวMail Receipient (For Review) เป็นการส่งเมลเพื่อให้ตรวจสอบ และแก้ไขได้ ซึ่งต้องกำหนดไฟล์ให้เป็นแบบ Share ไฟล์ก่อนMail Receipient (as Attachment) เป็นการส่งเป็นไฟล์...

Read More

Microsoft Excel Tips and Tricks: เทคนิคการกรอกเวลาที่มากกว่า 9999:59:59

ปกติการกรอกเวลาที่มากกว่า 9999:59:59 จะไม่สามารถแสดงเป็นเวลาได้ แต่แสดงเป็น Text แทน เนื่องจากเป็นข้อจำกัดหนึ่งของ Excel การกรอกเวลาที่เกินกว่านั้นจำเป็นต้องมีเทคนิคครับ เนื่องจากว่า Excel สามารถแสดงค่าที่มากกว่า 9999:59:59 ได้ เพียงแต่ค่านั้นต้องมาจากการคำนวณไม่ใช่กรอกเข้าไปโดยตรง เมื่อทราบเช่นนี้แล้ว เราก็สามารถประยุกต์การกรอกได้ง่าย ๆ เลย และต้องไม่ลืมจัดรูปแบบเซลล์เป้าหมายเป็น [h]:mm:ssเช่นต้องการให้แสดงเวลา 654321:40:35 ที่เซลล์ A1สามารถกรอกค่าที่ A1 ดังนี้=654321/24+40/1440+35/86400Enter24, 1440 และ 86400 มาจากไหน สามารถดูเพิ่มเติ็มได้ที่นี่ครับ วัน...

Read More

Microsoft Excel Tips and Tricks: การเชื่อมข้อความแล้วให้ขึ้นบรรทัดใหม่

การเชื่อมข้อความเข้าด้วยกันสามารถใช้เครื่องหมาย & ช่วยในการเชื่อมได้ แต่หากเชื่อมแล้วต้องการให้ข้อความที่เชื่อมกันนั้นขึ้นบรรทัดใหม่ สามารถใช้ Char(10) เข้ามาคั่นกลางระหว่างแต่ละข้อความอีกทีครับ เช่น ต้องการเชื่อมข้อความ A1, B1 และ C1 เข้าด้วยกัน และต้องการให้แต่ละข้อความขึ้นบรรทัดใหม่สามารถทำได้ดังนี้ที่เซลล์ใด ๆ คีย์=A1&Char(10)&B1&Char(10)&C1จากนั้นจัดข้อความโดยคลิกขวาลงในเซลล์ตามข้อ 1 เลือก Format cells แล้วคลิก Tab Alignment ทำเครื่องหมายที่ Wraptext > OKจากนั้นกำหนดความสูงและความกว้างเซลล์ตามต้อง...

Read More

Microsoft Excel Tips and Tricks: เทคนิคการลบเซลล์ด้วยเมาส์

ปกติเมื่อต้องการลบเนื้อหาในเซลล์สามารถกดแป้น Delete หรือคลิกเมนู Edit > Clear > Contentsเราสามารถลบเนื้อหาในเซลล์ด้วยเมาส์ได้เช่นกันครับ โดยคลุมข้อมูลที่ต้องการลบคลิกซ้ายค้างที่จุดจับเติม (มุมขวาล่างของเซลล์หรือกลุ่มเซลล์)ลากไปยังทางซ้ายหากต้องการลบเนื้อหาเซลล์ไปทางซ้าย ลากขึ้นด้านบนหากต้องการลบเนื้อหาเซลล์ข้างบนตามข้อ 3. หากกดแป้น Shift ร่วมด้วยในขณะลากไปทางซ้ายหรือขึ้นด้านบน จะเป็นการลบเซลล์และขยับเซลล์ด้านขวาหรือด้านล่างมาแทนเซลล์เดิม แต่ถ้าหากลากลงด้านล่างหรือด้านขวาจะเป็นการแทรกเซลล์...

Read More