อยากเขียนสูตร countif ด้วย VBA และทำซ้ำอีก 1000 แถวโดยไม่ต้องวนลูปครับ

[zern]

ตามไฟล์แนบ ผมเขียนสูตร countif ใน VBA และต้องการให้สูตรที่เขียน input คำตอบทั้งหมดลงในช่อง H1:H1000 โดยไม่ใช้คำสั่งวนลูปน่ะครับ

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

แนบโค้ดที่เขียนมา เข้าใจว่าผิดตรง Range("G1:G1000").FormulaArray น่ะครับ

Code: Select all

Sub Macro2()
'
' Macro2 Macro
'
    Dim x As Integer
    NumRows = Excel.WorksheetFunction.CountA([G:G])
    
    
    Worksheets("Sheet1").Range("H1:H" & NumRows).FormulaArray = Excel.WorksheetFunction.CountIf(Worksheets("sheet1").[A:A], Range("G1:G1000").FormulaArray)
    'Next
'
End Sub

[snasui]

ตัวอย่าง Code ครับ

Code: Select all

 Worksheets("Sheet1").Range("H1:H" & NumRows).Formula = "=CountIf(a:a,g1)"

[zern]

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

อธิบายอาจงงๆ ผมก็ไม่แน่ใจว่าผมใช้คำเรียกได้ถูกต้องตามที่เค้าใช้ๆกันหรือเปล่า

อ้อ ไหนๆก็เห็นโค้ดในคำตอบ งั้นขอถามเพิ่มเลยละกันครับ

โค้ด .formula

Code: Select all

Worksheets("Sheet1").Range("H1:H" & NumRows).Formula = "=CountIf(a:a,g1)"

กับ

Code: Select all

Worksheets("Sheet1").Range("H1:H" & NumRows)= "=CountIf(a:a,g1)"

มีการใช้งานที่แตกต่างกันยังไงเหรอครับ เห็นในกรณีนี้ผลลัพธ์ได้มาเท่ากันน่ะครับ

[snasui]

ตัวอย่าง Code ครับ

Code: Select all

Dim y As Variant
With Application
    NumRows = .WorksheetFunction.CountA([G:G])
    y = .Index(.CountIf(Worksheets("sheet1").[A:A], [G1:G10000]), 0)
    Worksheets("Sheet1").Range("H1:H" & NumRows).Value = y
End With

.formula, .value คือ Property หนึ่งของ Object ความหมายก็ตรงตัวตามนั้น หากไม่ใส่โปรแกรมจะถือว่าเป็น .value ซึ่งเป็น Default ของ Range ครับ

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

[zern]

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

[snasui]

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

[zern]

อ้าวเหรอครับ แต่ข้อดีคือไม่ error หรือเปล่า หรือ error ได้เหมือนกัน T-T

[snasui]

ข้อดีคือไม่ต้อง Copy แล้ววางเป็น Value เท่านั้นครับ

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

[zern]

โอเคครับ กระจ่างมากเลย ขอบคุณครับ 555

[zern]

ลองเล่น code ที่คุณ snasui ให้มาเพิ่มแล้ว มีคำถามอีก 3 ข้อครับ
1. ทำไมถึงใส่ .index(...)ใน y = .Index(.CountIf(Worksheets("sheet1").[A:A], [G1:G10000]), 0) มาเหรอครับ ผมลองลบออก vba ก็ใช้งานได้ปกตินี่ครับ
2. ความแตกต่างของ Application. กับ Worksheetfunction.
3. การใช้ Excel. ควรใส่ในกรณีไหนครับ เช่น โค้ดที่ผมเขียน Excel.WorksheetFunction.CountA([G:G])

[snasui]

1. ทำไมถึงใส่ .index(...)ใน y = .Index(.CountIf(Worksheets("sheet1").[A:A], [G1:G10000]), 0) มาเหรอครับ ผมลองลบออก vba ก็ใช้งานได้ปกตินี่ครับ

หากลบ . ออก Code จะใช้ไม่ได้ครับ เป็นเรื่องของ Object Oriented Programming จะเห็นว่า Statement ใดที่นำหน้าด้วย . นั้น อยู่ภายใน With และ End With เพื่อย่อ Code ให้สั้นลงครับ

2. ความแตกต่างของ Application. กับ Worksheetfunction.

ดูที่ Link นี้ครับ viewtopic.php?t=8471#p53157

3. การใช้ Excel. ควรใส่ในกรณีไหนครับ เช่น โค้ดที่ผมเขียน Excel.WorksheetFunction.CountA([G:G])

ควรใช้ Excel. เมื่อเขียนด้วยโปรแกรมอื่น เช่น VB, C# ฯลฯ ควบคุม Excel และโปรแกรม Office อื่น ๆ เพื่อจะระบุว่ากำลังเขียน Code เพื่อจัดการ Excel แต่หากเขียนใน Excel ไม่จำเป็นต้องเขียน Excel นำหน้า เพราะโปรแกรมทราบอยู่แล้วว่า Parent คือ Excel

Object ต่างกันสามารถอ้างถึง Property เดียวกันหรืออ้างวนไปวนมาได้ เช่น Application.Range("a1").Application.Sheets(1).Range("b2").Select จะให้ผลลัพธ์ได้เช่นเดียวกันกับ Sheets(1).Range("b2").Select ต้องเรียนรู้และอาศัยประสบการณ์ อาจจะสับสนในช่วงแรก แต่เมื่อใช้ไปสักระยะ จะเกิดความเข้าใจและใช้งานได้อย่างกระชับเองครับ

[zern]

ข้อ ๑ ผมหมายถึง ใช้สูตร .Index(.CountIf(Worksheets("sheet1").[A:A], [G1:G10000]), 0) และ .CountIf(Worksheets("sheet1").[A:A], [G1:G10000]) มันได้ผลเหมือนกันในไฟล์นี้น่ะครับ เลยสงสัยว่าใส่ index(...) มาเพราะมีเหตุผลอะไรหรือเปล่าเผื่อมีทริคใหม่ๆ ^ ^
ส่วนข้ออื่นเข้าใจแล้วครับ
ข้อ ๒ มันคืนค่า error และพยายาม debug มาให้ด้วยนี่เอง ถึงได้ทำให้ vba วิ่งไม่ไป
ข้อ ๓ ตราบใดที่ยังเขียนแค่ในเอกเซล ก็ไม่ต้องมี excel. ก็ได้

[snasui]

ข้อ ๑ เป็นการทำให้เป็น Array แล้วไปกำหนดให้กับ y จะเห็นว่าผมได้ประกาศตัวแปร y ให้เป็น Varaint (Varaint รวมถึง Array ด้วย)

Index ที่ใส่เข้าไปด้วยความเคยชิน เป็นการช่วยทำให้เป็น Array ถ้าเขียนใน Worksheet โดยใช้ลำพังเพียง Countif โดยไม่ใช้ Index ช่วย มันจะไม่สามารถเป็น Array ได้ กรณีนี้ลบทิ้งได้ครับ

[zern]

อ่อ การใส่ 0 ทำให้เป็น array นี่เอง ผมเคยเจอกรณีเป็น 0 แล้วผลลัพธ์ของ index(A:A,0) มันเทียบเท่ากับ =index(A:A,row()) ก็คิดอยู่ว่าทำไมมันถึงดึงผลลัพธ์แบบนี้ แทนที่จะเป็น error ขอบคุณครับ จะได้จำไว้ใช้ประโยชน์

ปล.หวังว่าผมคงหมดประเด็นสงสัยละ ปัญหาเยอะจริง 555