วิธีปรับกล่องคำสั่งผสมอัตโนมัติหรือรายการตามช่วงข้อมูลแบบไดนามิกใน Excel

แบ่งปันเลย:

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

สมมติว่าผู้อ่านมี Ribbon ของนักพัฒนาแสดงอยู่และคุ้นเคยกับ VBA Editor มิฉะนั้นโปรดใช้“ แท็บนักพัฒนา Excel” ของ Google หรือ“ หน้าต่างรหัส Excel”

วิธีการแสดงกล่องคำสั่งผสมแบบมืออาชีพคือการขยายช่วงหรือหดตัวตามต้องการ ตัวอย่างเช่น:วิธีการแสดงกล่องคำสั่งผสมแบบมืออาชีพ

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

ในการรักษาช่วงโดยอัตโนมัติต้องใช้ชื่อที่กำหนดเพื่อติดตามจำนวนแถวที่เติมข้อมูล ตัวอย่างเช่นเราใช้ eCol (end column) และ eRow (end row) เพื่อกำหนดขอบเขตของช่วงของเรา ยิ่งเราเติมแถวมากเท่าไหร่ค่า eRow ก็ยิ่งมากขึ้นเท่านั้นใช้ eCol และ eRow เพื่อกำหนดขอบเขตของช่วง

ชื่อที่กำหนดไว้ข้างต้นได้ตั้งค่ารายการให้กว้างหนึ่งคอลัมน์ (eCol = 1) ตามจำนวนแถวที่เติมใน eCol (eRow = 11)

สุดท้ายชื่อเรื่องจะปรากฏในช่วง“ A2: A” & eRow คุณจะสังเกตเห็นว่าฟังก์ชัน Index ใช้เพื่อสร้างเซลล์สุดท้ายในช่วงที่เรียกว่า“ Titles” ช่วง“ A2: A” และ eRow แปลเป็น“ A2: A11” ได้อย่างมีประสิทธิภาพในขั้นตอนนี้ฟังก์ชันดัชนี

เราสามารถตั้งค่าช่วงไดนามิกโดยอัตโนมัติเมื่อสมุดงานเปิดขึ้นโดยใช้ขั้นตอนย่อย Auto_open ซึ่งทำงานก่อนที่จะมองเห็นสมุดงาน

รหัส

เปิดสมุดงานและเติมข้อมูลด้วยกล่องคำสั่งผสมและข้อมูลบางอย่าง สามารถดูตัวอย่างสมุดงานที่ใช้ในแบบฝึกหัดนี้ได้ Good Farm Animal Welfare Awards.

เปิดหน้าต่างรหัส VBA และใส่โมดูล คัดลอกโค้ดด้านล่างลงในโมดูล

เหตุการณ์ Auto_Open จะตั้งค่าแถวสุดท้ายและคอลัมน์สุดท้ายสำหรับช่วงไดนามิก“ ชื่อเรื่อง” และบันทึกการเปลี่ยนแปลงที่เกิดขึ้นในภายหลัง

Sub auto_open()
     Dim eRow As Integer, eCol As Integer, i As Long
 
     On Error Resume Next
 
     'Clear the present define names, to avoid any duplications
     activeworkbook.Names("eCol").Delete
     activeworkbook.Names("eRow").Delete
     activeworkbook.Names("Titles").Delete
     Range("A1").Select
 
    'Titles will appear in the first column, A in this case
    eCol = 1
 
    'Find the last populated row
    eRow = Sheets("Main").Cells(Rows.Count, eCol).End(xlUp).Row
 
    'Define the names
     activeworkbook.Names.Add Name:="eCol", RefersTo:="=COUNTA($1:$1)"
     activeworkbook.Names.Add Name:="eRow", RefersToR1C1:="=COUNTA(C" & ColNo & ")"
     activeworkbook.Names.Add Name:="Titles", RefersTo:="=A2:INDEX($2:$200," & "eRow," & "eCol)"
End Sub

Sub DropDown1_Change()
     MsgBox "Directed by " & Cells(2, 5)
End Sub

หมายเหตุ: ทุกอย่างถูกจัดวางไว้ในหน้าเดียวเพื่อให้ดูง่ายขึ้น โดยปกติแล้วค่าในคอลัมน์ A และ B รวมถึง D และ E จะอยู่ในชีตอื่น ซึ่งอาจถูกซ่อนไว้ โปรดทราบด้วยว่าคอลัมน์ที่สอง คือ B ไม่ได้ถูกใช้ในการกำหนดช่วงในแบบฝึกหัดนี้ ค่าของคอลัมน์ B ได้มาด้วยวิธีปกติผ่านคุณสมบัติการเชื่อมโยงเซลล์ของกล่องคอมโบ (D2 สะท้อนถึงการเลือกองค์ประกอบที่สามในช่วง ซึ่งเริ่มต้นที่ A2 ควบคู่กับฟังก์ชันดัชนีใน “E3” เพื่อค้นหาไดเร็กเตอร์ ( =INDEX(B:B,D2+1,1))กล่องคำสั่งผสมจะเริ่มต้นโดย Auto_open

บันทึกเวิร์กบุ๊กแล้วเปิดใหม่ เริ่มต้นกล่องคำสั่งผสมจะถูกเติมโดย Auto_open เพิ่มรายการในคอลัมน์ A และ B และสังเกตการเปลี่ยนแปลงในกล่องคำสั่งผสม

กู้ไฟล์ Excel ที่เสียหาย

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

บทนำผู้เขียน:

Felix Hooker เป็นผู้เชี่ยวชาญด้านการกู้คืนข้อมูลใน DataNumen, Inc. ซึ่งเป็นผู้นำระดับโลกด้านเทคโนโลยีการกู้คืนข้อมูล ได้แก่ การซ่อมแซมที่หายาก และผลิตภัณฑ์ซอฟต์แวร์กู้คืน sql ดูข้อมูลเพิ่มเติมได้ที่ wwwdatanumenด้วย.

แบ่งปันเลย:

ความเห็นถูกปิด