ทำความเข้าใจกับเซลล์ Excel กับฟังก์ชันช่วงใน VBA

ทำความเข้าใจกับเซลล์ Excel กับฟังก์ชันช่วงใน VBA

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





ปัญหาในการใช้ฟังก์ชัน Cells and Range ใน VBA คือในระดับสูง คนส่วนใหญ่เข้าใจได้ยากว่าฟังก์ชันเหล่านี้ทำงานอย่างไร การใช้สิ่งเหล่านี้อาจทำให้เกิดความสับสนได้ ต่อไปนี้คือวิธีที่คุณสามารถใช้ประโยชน์ได้ในแบบที่คุณอาจคาดไม่ถึง



หน้าที่ของเซลล์

ฟังก์ชันเซลล์และช่วงช่วยให้คุณบอกได้ สคริปต์ VBA ของคุณ ตำแหน่งที่คุณต้องการรับหรือวางข้อมูลบนเวิร์กชีตของคุณ ความแตกต่างหลักระหว่างสองเซลล์คือสิ่งที่อ้างอิง





เซลล์ มักจะอ้างอิงเซลล์เดียวในแต่ละครั้ง ในขณะที่ พิสัย อ้างอิงกลุ่มเซลล์ในครั้งเดียว รูปแบบของฟังก์ชันนี้คือ เซลล์(แถว คอลัมน์) .

สิ่งนี้อ้างอิงทุกเซลล์ในแผ่นงานทั้งหมด เป็นตัวอย่างหนึ่งที่ฟังก์ชัน Cells ไม่ได้อ้างอิงเซลล์เดียว:



Worksheets('Sheet1').Cells

ข้อมูลนี้อ้างอิงเซลล์ที่สามจากด้านซ้ายของแถวบนสุด เซลล์ C1:

Worksheets('Sheet1').Cells(3)

รหัสต่อไปนี้อ้างอิงเซลล์ D15:



Worksheets('Sheet1').Cells(15,4)

หากต้องการ คุณยังสามารถอ้างอิงเซลล์ D15 ด้วย 'Cells(15,'D')'—คุณได้รับอนุญาตให้ใช้ตัวอักษรประจำคอลัมน์ได้

มีความยืดหยุ่นมากในการอ้างอิงเซลล์โดยใช้ตัวเลขสำหรับคอลัมน์และเซลล์ โดยเฉพาะอย่างยิ่งกับสคริปต์ที่สามารถ วนซ้ำ เซลล์จำนวนมาก (และทำการคำนวณกับเซลล์เหล่านั้น) อย่างรวดเร็ว เราจะไปที่รายละเอียดเพิ่มเติมด้านล่าง



ฟังก์ชันช่วง

ในหลาย ๆ ด้าน ฟังก์ชันช่วงมีประสิทธิภาพมากกว่าการใช้เซลล์ เนื่องจากช่วยให้คุณสามารถอ้างอิงเซลล์เดียวหรือช่วงเฉพาะของเซลล์ได้พร้อมกัน คุณคงไม่อยากวนรอบฟังก์ชัน Range เนื่องจากการอ้างอิงสำหรับเซลล์ไม่ใช่ตัวเลข (เว้นแต่คุณจะฝังฟังก์ชัน Cells ไว้ข้างใน)

รูปแบบของฟังก์ชันนี้คือ ช่วง (เซลล์ #1, เซลล์ #2) . แต่ละเซลล์สามารถกำหนดได้ด้วยตัวอักษร-ตัวเลข

มาดูตัวอย่างกัน

ถ้าคุณเลิกเป็นเพื่อนกับใครสักคนใน facebook คุณช่วยเพื่อนใหม่ได้ไหม

ในที่นี้ ฟังก์ชันช่วงกำลังอ้างอิงเซลล์ A5:

Worksheets('Sheet1').Range('A5')

ในที่นี้ ฟังก์ชันช่วงกำลังอ้างอิงเซลล์ทั้งหมดระหว่าง A1 ถึง E20:

Worksheets('Sheet1').Range('A1:E20')

ดังที่กล่าวไว้ข้างต้น คุณไม่จำเป็นต้องใช้การกำหนดเซลล์ที่เป็นตัวอักษรและตัวเลข คุณสามารถใช้ฟังก์ชัน Cells สองฟังก์ชันภายในฟังก์ชัน Range เพื่อระบุช่วงบนชีตได้ ดังนี้


With Worksheets('Sheet1')
.Range(.Cells(1, 1), _
.Cells(20, 5))
End With

โค้ดด้านบนอ้างอิงช่วงเดียวกันกับฟังก์ชัน Range('A1:E20') คุณค่าในการใช้งานคือมันจะช่วยให้คุณเขียนโค้ดที่ทำงานกับช่วงไดนามิกโดยใช้ลูปได้

เมื่อคุณเข้าใจวิธีจัดรูปแบบฟังก์ชัน Cells and Range แล้ว มาดูวิธีใช้งานฟังก์ชันเหล่านี้อย่างสร้างสรรค์ในโค้ด VBA ของคุณกัน

การประมวลผลข้อมูลด้วยฟังก์ชันเซลล์

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

ลองมาดูตัวอย่างง่ายๆ สมมติว่าคุณจัดการทีมขายจำนวน 11 คน และทุกเดือนคุณต้องการดูประสิทธิภาพของพวกเขา

คุณอาจมี แผ่นที่1 ที่ติดตามยอดขายและปริมาณการขายของพวกเขา

บน แผ่นที่2 เป็นที่ที่คุณติดตามคะแนนคำติชมของพวกเขาในช่วง 30 วันที่ผ่านมาจากลูกค้าของบริษัทของคุณ

หากคุณต้องการคำนวณโบนัสในชีตแรกโดยใช้ค่าจากสองชีต มีหลายวิธีในการทำเช่นนี้ คุณสามารถเขียนสูตรในเซลล์แรกที่ทำการคำนวณโดยใช้ข้อมูลในสองแผ่นงานแล้วลากลงมา ที่จะทำงาน

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

เหตุใดจึงไม่เรียกใช้การคำนวณสำหรับคอลัมน์โบนัสในสคริปต์เดียวกันในขณะนั้น

การทำงานของเซลล์ในการดำเนินการ

หากคุณไม่เคยเขียน VBA ใน Excel มาก่อน คุณจะต้องเปิดใช้งานรายการเมนูนักพัฒนา โดยไปที่ ไฟล์ > ตัวเลือก . คลิกที่ ปรับแต่งริบบิ้น . สุดท้าย เลือก Developer จากบานหน้าต่างด้านซ้าย เพิ่ม ไปที่บานหน้าต่างด้านขวา และตรวจสอบให้แน่ใจว่าได้เลือกช่องทำเครื่องหมายแล้ว

ตอนนี้เมื่อคุณคลิก ตกลง และกลับไปที่แผ่นงานหลัก คุณจะเห็นตัวเลือกเมนูนักพัฒนา

คุณสามารถใช้ แทรก เมนูเพื่อแทรกปุ่มคำสั่งหรือเพียงแค่คลิก ดูรหัส เพื่อเริ่มการเข้ารหัส

ในตัวอย่างนี้ เราจะทำให้สคริปต์ทำงานทุกครั้งที่เปิดเวิร์กบุ๊ก ในการดำเนินการนี้ เพียงคลิก ดูรหัส จากเมนูนักพัฒนาซอฟต์แวร์ และวางฟังก์ชันใหม่ต่อไปนี้ลงในหน้าต่างโค้ด

Private Sub Workbook_Open()
End Sub

หน้าต่างรหัสของคุณจะมีลักษณะดังนี้

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

โปรดจำไว้ว่า ฟังก์ชัน Cells มีแถวและคอลัมน์เป็นพารามิเตอร์เพื่อระบุแต่ละเซลล์ เราจะสร้าง 'x' เป็นแถว ใช้ตัวเลขเพื่อขอข้อมูลของแต่ละคอลัมน์ จำนวนแถวคือจำนวนพนักงาน ดังนั้นนี่จะเป็นตั้งแต่ 1 ถึง 11 ตัวระบุคอลัมน์จะเป็น 2 สำหรับยอดขาย 3 สำหรับปริมาณการขาย และ 2 จากชีต 2 สำหรับคะแนนคำติชม

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

  • (ยอดขาย/50) x 0.4
  • (ปริมาณการขาย/50,000) x 0.5
  • (คะแนนความคิดเห็น/10) x 0.1

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

ตอนนี้เรามาดูกันว่าตรรกะทั้งหมดนั้นสามารถดึงออกมาได้อย่างไรในสคริปต์ VBA สั้น ๆ ที่ง่ายมาก:

Private Sub Workbook_Open()
For x = 2 To 12
Worksheets('Sheet1').Cells(x, 4) = (Worksheets('Sheet1').Cells(x, 2).Value / 50) * 0.4 _
+ (Worksheets('Sheet1').Cells(x, 3).Value / 50000) * 0.5 _
+ (Worksheets('Sheet2').Cells(x, 2).Value / 10) * 0.1 _
Next x
End Sub

นี่คือผลลัพธ์ของสคริปต์นี้

วิธีทำให้โปรแกรมสำรวจไฟล์มืด

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

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

คุณสามารถดำเนินการกับเซลล์ได้ทุกประเภทโดยใช้ฟังก์ชัน 'เซลล์' เช่น การล้างเซลล์ การเปลี่ยนรูปแบบฟอนต์ และอื่นๆ อีกมากมาย

หากต้องการสำรวจทุกสิ่งที่คุณสามารถทำได้เพิ่มเติม โปรดดูที่ หน้า Microsoft MSDN สำหรับวัตถุ Cells

การจัดรูปแบบเซลล์ด้วยฟังก์ชันพิสัย

สำหรับการวนซ้ำหลาย ๆ เซลล์ทีละเซลล์ ฟังก์ชัน Cells นั้นสมบูรณ์แบบ แต่ถ้าคุณต้องการใช้บางอย่างกับช่วงของเซลล์ทั้งหมดพร้อมกัน ฟังก์ชัน Range จะมีประสิทธิภาพมากกว่ามาก

กรณีการใช้งานหนึ่งอาจเป็นการจัดรูปแบบช่วงของเซลล์โดยใช้สคริปต์ หากตรงตามเงื่อนไขบางประการ

ตัวอย่างเช่น สมมติว่าถ้ายอดรวมของยอดขายทั้งหมดของพนักงานขายทั้งหมดเกิน 0,000 คุณต้องการเน้นเซลล์ทั้งหมดในคอลัมน์โบนัสเป็นสีเขียวเพื่อแสดงว่าทีมได้รับโบนัสพิเศษของทีม

มาดูกันว่าคุณจะทำได้อย่างไรกับ คำสั่ง IF .

Private Sub Workbook_Open()
If Worksheets('Sheet1').Cells(13, 3).Value > 400000 Then
ActiveSheet.Range('D2:D12').Interior.ColorIndex = 4
End If
End Sub

เมื่อสิ่งนี้ทำงาน หากเซลล์อยู่เหนือเป้าหมายของทีม เซลล์ทั้งหมดในช่วงนั้นจะถูกเติมด้วยสีเขียว

นี่เป็นเพียงตัวอย่างง่ายๆ ของการดำเนินการต่างๆ ที่คุณสามารถทำได้กับกลุ่มเซลล์โดยใช้ฟังก์ชันช่วง สิ่งอื่น ๆ ที่คุณสามารถทำได้ ได้แก่ :

  • ใช้โครงร่างรอบกลุ่ม
  • ตรวจสอบการสะกดของข้อความภายในช่วงของเซลล์
  • ล้าง คัดลอก หรือตัดเซลล์
  • ค้นหาช่วงด้วยวิธี 'ค้นหา'
  • ล้นหลาม

อย่าลืมอ่าน หน้า Microsoft MSDN สำหรับวัตถุ Range เพื่อดูความเป็นไปได้ทั้งหมด

นำ Excel ไปสู่อีกระดับ

เมื่อคุณเข้าใจความแตกต่างระหว่างฟังก์ชัน Cells และ Range แล้ว ก็ถึงเวลาที่จะยกระดับการเขียนสคริปต์ VBA ของคุณไปอีกระดับ บทความของ Dann เกี่ยวกับการใช้ฟังก์ชันการนับและการเพิ่มใน Excel จะช่วยให้คุณสร้างสคริปต์ขั้นสูงที่สามารถสะสมค่าในชุดข้อมูลทั้งหมดของคุณได้อย่างรวดเร็ว

และถ้าคุณเพิ่งเริ่มต้นกับ VBA ใน Excel อย่าลืมว่าเรามีสิ่งที่ยอดเยี่ยม คำแนะนำเบื้องต้นเกี่ยวกับ Excel VBA สำหรับคุณเช่นกัน

แบ่งปัน แบ่งปัน ทวีต อีเมล Canon กับ Nikon: กล้องยี่ห้อไหนดีกว่ากัน?

Canon และ Nikon เป็นสองชื่อที่ใหญ่ที่สุดในอุตสาหกรรมกล้อง แต่แบรนด์ใดที่มีกล้องและเลนส์ให้เลือกดีกว่ากัน?

อ่านต่อไป หัวข้อที่เกี่ยวข้อง
  • ผลผลิต
  • การเขียนโปรแกรม
  • การเขียนโปรแกรม Visual Basic
  • Microsoft Excel
เกี่ยวกับผู้เขียน Ryan Dube(เผยแพร่บทความ 942 ฉบับ)

Ryan สำเร็จการศึกษาระดับปริญญาตรีสาขาวิศวกรรมไฟฟ้า เขาทำงาน 13 ปีในด้านวิศวกรรมระบบอัตโนมัติ 5 ปีในด้านไอที และปัจจุบันเป็นวิศวกรด้านแอป อดีตผู้จัดการบรรณาธิการของ MakeUseOf เขาพูดในการประชุมระดับชาติเรื่อง Data Visualization และได้รับการนำเสนอในโทรทัศน์และวิทยุแห่งชาติ

เพิ่มเติมจาก Ryan Dube

สมัครรับจดหมายข่าวของเรา

เข้าร่วมจดหมายข่าวของเราสำหรับเคล็ดลับทางเทคนิค บทวิจารณ์ eBook ฟรี และดีลพิเศษ!

คลิกที่นี่เพื่อสมัครสมาชิก