เนื้อหา
คุณมีจอภาพขนาดใหญ่ แต่โต๊ะที่คุณทำงานด้วยนั้นใหญ่กว่า และเมื่อมองผ่านหน้าจอเพื่อค้นหาข้อมูลที่จำเป็น ก็มีโอกาสที่จะ “ละสายตา” ไปที่บรรทัดถัดไปและมองไปในทางที่ผิดได้เสมอ ฉันยังรู้จักคนที่มักจะถือไม้บรรทัดไว้ใกล้ตัวเพื่อติดเข้ากับเส้นบนจอภาพในโอกาสดังกล่าว เทคโนโลยีแห่งอนาคต!
และถ้าแถวและคอลัมน์ปัจจุบันถูกเน้นเมื่อเซลล์ที่ใช้งานอยู่เคลื่อนผ่านแผ่นงาน การเลือกพิกัดประเภทนี้:
ดีกว่าไม้บรรทัดใช่ไหม?
มีหลายวิธีของความซับซ้อนที่แตกต่างกันในการดำเนินการนี้ แต่ละวิธีมีข้อดีและข้อเสีย ลองดูที่รายละเอียด
วิธีที่ 1. ชัดเจน มาโครที่เน้นแถวและคอลัมน์ปัจจุบัน
วิธีที่ชัดเจนที่สุดในการแก้ปัญหาของเรา "บนหน้าผาก" - เราต้องการมาโครที่จะติดตามการเปลี่ยนแปลงในส่วนที่เลือกบนแผ่นงาน และเลือกแถวและคอลัมน์ทั้งหมดสำหรับเซลล์ปัจจุบัน นอกจากนี้ยังควรเปิดใช้งานและปิดใช้งานฟังก์ชันนี้หากจำเป็น เพื่อให้การเลือกรูปกากบาทดังกล่าวไม่ได้ป้องกันเราจากการป้อน ตัวอย่างเช่น สูตร แต่จะใช้ได้เฉพาะเมื่อเราดูรายการเพื่อค้นหาสิ่งที่จำเป็น ข้อมูล. สิ่งนี้นำเราไปสู่มาโครสามตัว (เลือก เปิดใช้งาน และปิดใช้งาน) ที่จะต้องเพิ่มในโมดูลชีต
เปิดแผ่นงานที่มีตารางที่คุณต้องการรับการเลือกพิกัดดังกล่าว คลิกขวาที่แท็บแผ่นงานและเลือกคำสั่งจากเมนูบริบท ข้อความที่มา (รหัสแหล่งที่มา).หน้าต่าง Visual Basic Editor ควรเปิดขึ้น คัดลอกข้อความของมาโครทั้งสามนี้ลงไป:
Dim Coord_Selection As Boolean 'ตัวแปรส่วนกลางสำหรับการเลือกเปิด/ปิด Sub Selection_On() 'มาโครในการเลือก Coord_Selection = True End Sub Selection_Off() 'มาโครปิดการเลือก Coord_Selection = False End Sub 'ขั้นตอนหลักที่ดำเนินการเลือก Private Sub Worksheet_SelectionChange (ByVal Target As ช่วง) Dim WorkRange As Range หาก Target.Cells.Count > 1 จากนั้นออกจาก Sub 'ถ้าเลือกมากกว่า 1 เซลล์ ให้ออกหาก Coord_Selection = False จากนั้นออกจาก Sub' หากการเลือกปิดอยู่ ให้ออกจาก Application.ScreenUpdating = False Set WorkRange = Range (" A6:N300") 'ที่อยู่ของช่วงการทำงานที่มองเห็นการเลือกได้ เปลี่ยนที่อยู่ของช่วงการทำงานเป็นของคุณเอง – อยู่ในช่วงนี้ที่การเลือกของเราจะใช้งานได้ จากนั้นปิด Visual Basic Editor แล้วกลับไปที่ Excel
กดแป้นพิมพ์ลัด ALT + F8เพื่อเปิดหน้าต่างที่มีรายการมาโครที่พร้อมใช้งาน มาโคร Selection_เปิดอย่างที่คุณอาจเดาได้ รวมถึงการเลือกพิกัดบนแผ่นงานปัจจุบันและมาโคร Selection_ปิด - ปิดเครื่อง ในหน้าต่างเดียวกันโดยคลิกที่ปุ่ม พารามิเตอร์ (ตัวเลือก) คุณสามารถกำหนดแป้นพิมพ์ลัดให้กับมาโครเหล่านี้เพื่อให้เปิดใช้ได้ง่าย
ข้อดีของวิธีนี้:
- ความสะดวกในการใช้งานสัมพัทธ์
- การเลือก – การดำเนินการไม่เป็นอันตรายและไม่เปลี่ยนแปลงเนื้อหาหรือการจัดรูปแบบของเซลล์แผ่นงานแต่อย่างใด ทุกอย่างยังคงเหมือนเดิม
ข้อเสียของวิธีนี้:
- การเลือกดังกล่าวทำงานไม่ถูกต้องหากมีเซลล์ที่ผสานบนแผ่นงาน - แถวและคอลัมน์ทั้งหมดที่รวมอยู่ในสหภาพจะถูกเลือกพร้อมกัน
- หากคุณกดปุ่ม Delete โดยไม่ตั้งใจ ไม่เพียงแต่จะล้างเซลล์ที่ใช้งานอยู่เท่านั้น แต่ยังรวมถึงพื้นที่ที่เลือกทั้งหมด เช่น ลบข้อมูลออกจากทั้งแถวและคอลัมน์
วิธีที่ 2. ต้นฉบับ CELL + ฟังก์ชันการจัดรูปแบบตามเงื่อนไข
วิธีนี้แม้ว่าจะมีข้อเสียอยู่บ้าง แต่สำหรับฉันแล้วมันดูสง่างามมาก ในการใช้งานบางอย่างโดยใช้เครื่องมือ Excel ในตัว การเขียนโปรแกรมใน VBA เพียงเล็กน้อยก็คือไม้ลอย 😉
วิธีการนี้ใช้ฟังก์ชัน CELL ซึ่งสามารถให้ข้อมูลที่แตกต่างกันมากมายในเซลล์ที่กำหนด เช่น ความสูง ความกว้าง หมายเลขแถว-คอลัมน์ รูปแบบตัวเลข เป็นต้น ฟังก์ชันนี้มีอาร์กิวเมนต์ XNUMX ตัว:
- คำรหัสสำหรับพารามิเตอร์ เช่น "คอลัมน์" หรือ "แถว"
- ที่อยู่ของเซลล์ที่เราต้องการกำหนดค่าของพารามิเตอร์นี้
เคล็ดลับคืออาร์กิวเมนต์ที่สองเป็นทางเลือก หากไม่ได้ระบุไว้ ระบบจะใช้เซลล์ที่ใช้งานอยู่ในปัจจุบัน
องค์ประกอบที่สองของวิธีนี้คือการจัดรูปแบบตามเงื่อนไข ฟีเจอร์ Excel ที่มีประโยชน์อย่างยิ่งนี้ช่วยให้คุณจัดรูปแบบเซลล์ได้โดยอัตโนมัติหากตรงตามเงื่อนไขที่ระบุ หากเรารวมแนวคิดทั้งสองนี้เป็นหนึ่งเดียว เราจะได้อัลกอริธึมต่อไปนี้สำหรับการนำการเลือกพิกัดของเราไปใช้ผ่านการจัดรูปแบบตามเงื่อนไข:
- เราเลือกตารางของเรา กล่าวคือ เซลล์ที่ควรแสดงการเลือกพิกัดในอนาคต
- ใน Excel 2003 และเก่ากว่า ให้เปิดเมนู รูปแบบ – การจัดรูปแบบตามเงื่อนไข – สูตร (รูปแบบ — การจัดรูปแบบตามเงื่อนไข — สูตร). ใน Excel 2007 และใหม่กว่า – คลิกที่แท็บ หน้าแรก (บ้าน)ปุ่ม การจัดรูปแบบตามเงื่อนไข – สร้างกฎ (การจัดรูปแบบตามเงื่อนไข — สร้างกฎ) และเลือกประเภทกฎ ใช้สูตรเพื่อกำหนดเซลล์ที่จะจัดรูปแบบ (ใช้สูตร)
- ป้อนสูตรสำหรับการเลือกพิกัดของเรา:
=OR(CELL("row")=ROW(A2),CELL("column")=COLUMN(A2))
=หรือ(เซลล์(«แถว»)=ROW(A1),CELL(«คอลัมน์»)=COLUMN(A1))
สูตรนี้จะตรวจสอบเพื่อดูว่าหมายเลขคอลัมน์ของแต่ละเซลล์ในตารางตรงกับหมายเลขคอลัมน์ของเซลล์ปัจจุบันหรือไม่ ในทำนองเดียวกันกับคอลัมน์ ดังนั้น เฉพาะเซลล์ที่มีหมายเลขคอลัมน์หรือหมายเลขแถวที่ตรงกับเซลล์ปัจจุบันเท่านั้นที่จะถูกเติม และนี่คือการเลือกพิกัดรูปกากบาทที่เราต้องการบรรลุ
- คลิกที่ปุ่ม กรอบ (รูปแบบ) และกำหนดสีเติม
ทุกอย่างเกือบจะพร้อม แต่มีความแตกต่างกันนิดหน่อย ความจริงก็คือ Excel ไม่ถือว่าการเปลี่ยนแปลงการเลือกเป็นการเปลี่ยนแปลงข้อมูลในแผ่นงาน และด้วยเหตุนี้ จึงไม่ทริกเกอร์การคำนวณใหม่ของสูตรและการเปลี่ยนสีของการจัดรูปแบบตามเงื่อนไขเฉพาะเมื่อตำแหน่งของเซลล์ที่ใช้งานอยู่เปลี่ยนแปลง ดังนั้น ให้เพิ่มมาโครอย่างง่ายให้กับโมดูลชีตที่จะทำสิ่งนี้ คลิกขวาที่แท็บแผ่นงานและเลือกคำสั่งจากเมนูบริบท ข้อความที่มา (รหัสแหล่งที่มา).หน้าต่าง Visual Basic Editor ควรเปิดขึ้น คัดลอกข้อความของมาโครแบบง่ายนี้ลงไป:
แผ่นงานย่อยส่วนตัว_SelectionChange(ByVal Target As Range) ActiveCell.Calculate End Sub
ตอนนี้ เมื่อการเลือกเปลี่ยนไป กระบวนการคำนวณสูตรใหม่ด้วยฟังก์ชันจะเริ่มขึ้น เซลล์ ในการจัดรูปแบบตามเงื่อนไขและท่วมแถวและคอลัมน์ปัจจุบัน
ข้อดีของวิธีนี้:
- การจัดรูปแบบตามเงื่อนไขจะไม่ทำลายการจัดรูปแบบตารางแบบกำหนดเอง
- ตัวเลือกการเลือกนี้ทำงานอย่างถูกต้องกับเซลล์ที่ผสาน
- ไม่มีความเสี่ยงที่จะลบข้อมูลทั้งแถวและคอลัมน์จากการคลิกโดยไม่ตั้งใจ ลบ.
- ใช้มาโครน้อยที่สุด
ข้อเสียของวิธีนี้:
- ต้องป้อนสูตรสำหรับการจัดรูปแบบตามเงื่อนไขด้วยตนเอง
- ไม่มีวิธีที่รวดเร็วในการเปิด/ปิดใช้งานการจัดรูปแบบดังกล่าว โดยจะเปิดใช้งานเสมอจนกว่ากฎจะถูกลบ
วิธีที่ 3 เหมาะสมที่สุด การจัดรูปแบบตามเงื่อนไข + มาโคร
ค่าเฉลี่ยสีทอง เราใช้กลไกในการติดตามการเลือกบนแผ่นงานโดยใช้มาโครจากวิธีที่ 1 และเพิ่มการเน้นอย่างปลอดภัยโดยใช้การจัดรูปแบบตามเงื่อนไขจากวิธีที่ 2
เปิดแผ่นงานที่มีตารางที่คุณต้องการรับการเลือกพิกัดดังกล่าว คลิกขวาที่แท็บแผ่นงานและเลือกคำสั่งจากเมนูบริบท ข้อความที่มา (รหัสแหล่งที่มา).หน้าต่าง Visual Basic Editor ควรเปิดขึ้น คัดลอกข้อความของมาโครทั้งสามนี้ลงไป:
Dim Coord_Selection As Boolean Sub Selection_On() Coord_Selection = True End Sub Sub Selection_Off() Coord_Selection = False End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim WorkRange As Range, CrossRange As Range Set WorkRange = Range("A7:N300") 'адрес рабочего диапазона с таблицей หาก Target.Count > 1 จากนั้นออกจาก Sub ถ้า Coord_Selection = False จากนั้น WorkRange.FormatConditions.Delete ออกจาก Sub End หาก Application.ScreenUpdating = False หากไม่ตัดกัน (Target Inter, Work SetRange) คือ (ไม่มีสิ่งใดข้ามแล้ว Work SetRange) WorkRange, Union(Target.EntireRow, Target.EntireColumn)) WorkRange.FormatConditions.Delete CrossRange.FormatConditions.Add Type:=xlExpression, Formula1:="=1" CrossRange.FormatConditions(1).Interior.ColorIndex = 33 Target.FormatConditions .Delete End If End Sub อย่าลืมเปลี่ยนที่อยู่ช่วงการทำงานเป็นที่อยู่ตารางของคุณ ปิด Visual Basic Editor และกลับไปที่ Excel หากต้องการใช้มาโครที่เพิ่ม ให้กดแป้นพิมพ์ลัด ALT + F8 และดำเนินการในลักษณะเดียวกับวิธีที่ 1
วิธีที่ 4. สวยงาม โปรแกรมเสริม FollowCellPointer
Excel MVP Jan Karel Pieterse จากเนเธอร์แลนด์แจกโปรแกรมเสริมฟรีบนเว็บไซต์ของเขา ติดตามCellPointer(36Kb) ซึ่งแก้ปัญหาเดียวกันได้ด้วยการวาดเส้นลูกศรแบบกราฟิกโดยใช้มาโครเพื่อเน้นแถวและคอลัมน์ปัจจุบัน:
ทางออกที่ดี ไม่ได้ไม่มีข้อบกพร่องในสถานที่ แต่คุ้มค่าที่จะลอง ดาวน์โหลดไฟล์เก็บถาวร แตกไฟล์ลงดิสก์ และติดตั้งโปรแกรมเสริม:
- ใน Excel 2003 และเก่ากว่า – ผ่านเมนู บริการ – ส่วนเสริม – ภาพรวม (เครื่องมือ — ส่วนเสริม — เรียกดู)
- ใน Excel 2007 และใหม่กว่า ผ่าน ไฟล์ – ตัวเลือก – ส่วนเสริม – ไป – เรียกดู (ไฟล์ — ตัวเลือก Excel — ส่วนเสริม — ไปที่ — เรียกดู)
- มาโครคืออะไร ตำแหน่งที่จะแทรกโค้ดแมโครใน Visual Basic