Page 1 of 1
อยากเขียนสูตร countif ด้วย VBA และทำซ้ำอีก 1000 แถวโดยไม่ต้องวนลูปครับ
Posted: Mon Jun 06, 2016 6:16 pm
by 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
Re: อยากเขียนสูตร countif ด้วย VBA และทำซ้ำอีก 1000 แถวโดยไม่ต้องวนลูปครับ
Posted: Mon Jun 06, 2016 6:34 pm
by snasui
ตัวอย่าง Code ครับ
Code: Select all
Worksheets("Sheet1").Range("H1:H" & NumRows).Formula = "=CountIf(a:a,g1)"
Re: อยากเขียนสูตร countif ด้วย VBA และทำซ้ำอีก 1000 แถวโดยไม่ต้องวนลูปครับ
Posted: Tue Jun 07, 2016 8:30 am
by 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)"
มีการใช้งานที่แตกต่างกันยังไงเหรอครับ เห็นในกรณีนี้ผลลัพธ์ได้มาเท่ากันน่ะครับ
Re: อยากเขียนสูตร countif ด้วย VBA และทำซ้ำอีก 1000 แถวโดยไม่ต้องวนลูปครับ
Posted: Tue Jun 07, 2016 5:39 pm
by 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 รู้จักว่าเป็นสูตรให้แสดงเป็นสูตรโดยอัตโนมัติครับ
Re: อยากเขียนสูตร countif ด้วย VBA และทำซ้ำอีก 1000 แถวโดยไม่ต้องวนลูปครับ
Posted: Tue Jun 07, 2016 7:18 pm
by zern
ใช้ได้ละครับ แบบที่ต้องการเลย แต่ยังไงก็ยังช้ากว่าใส่ formula ไปตรงๆอยู่ดี
แต่เดี๋ยวลองแกะๆพลิกๆดู อาจเจอวิธีที่ประยุกต์ให้เร็วขึ้นได้ ขอบคุณมากๆครับ
Re: อยากเขียนสูตร countif ด้วย VBA และทำซ้ำอีก 1000 แถวโดยไม่ต้องวนลูปครับ
Posted: Tue Jun 07, 2016 7:23 pm
by snasui
โอกาสที่เราจะเขียนสูตรด้วย VBA ด้วยเงื่อนไขเดียวกันแล้วให้คำนวณเร็วกว่าสูตรที่มาพร้อม Excel เป็นไปได้น้อยมากครับ
Re: อยากเขียนสูตร countif ด้วย VBA และทำซ้ำอีก 1000 แถวโดยไม่ต้องวนลูปครับ
Posted: Wed Jun 08, 2016 10:00 am
by zern
อ้าวเหรอครับ แต่ข้อดีคือไม่ error หรือเปล่า หรือ error ได้เหมือนกัน T-T
Re: อยากเขียนสูตร countif ด้วย VBA และทำซ้ำอีก 1000 แถวโดยไม่ต้องวนลูปครับ
Posted: Wed Jun 08, 2016 5:54 pm
by snasui
ข้อดีคือไม่ต้อง Copy แล้ววางเป็น Value เท่านั้นครับ
โดยทั่วไปสูตรที่คำนวณช้าสามารถจะคำนวณสำเร็จได้หากอดทนรอคอยซึ่งจะบอกไม่ได้ว่านานแค่ไหน ขึ้นกับปริมาณข้อมูลและเงื่อนไขที่ใช้และทรัพยากรของเครื่องครับ
Re: อยากเขียนสูตร countif ด้วย VBA และทำซ้ำอีก 1000 แถวโดยไม่ต้องวนลูปครับ
Posted: Wed Jun 08, 2016 6:41 pm
by zern
โอเคครับ กระจ่างมากเลย ขอบคุณครับ 555
Re: อยากเขียนสูตร countif ด้วย VBA และทำซ้ำอีก 1000 แถวโดยไม่ต้องวนลูปครับ
Posted: Thu Jun 09, 2016 9:52 am
by 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])
Re: อยากเขียนสูตร countif ด้วย VBA และทำซ้ำอีก 1000 แถวโดยไม่ต้องวนลูปครับ
Posted: Thu Jun 09, 2016 6:53 pm
by snasui
zern wrote:1. ทำไมถึงใส่ .index(...)ใน y = .Index(.CountIf(Worksheets("sheet1").[A:A], [G1:G10000]), 0) มาเหรอครับ ผมลองลบออก vba ก็ใช้งานได้ปกตินี่ครับ
หากลบ . ออก Code จะใช้ไม่ได้ครับ เป็นเรื่องของ Object Oriented Programming จะเห็นว่า Statement ใดที่นำหน้าด้วย . นั้น อยู่ภายใน
With และ
End With เพื่อย่อ Code ให้สั้นลงครับ
zern wrote:2. ความแตกต่างของ Application. กับ Worksheetfunction.
ดูที่ Link นี้ครับ
viewtopic.php?t=8471#p53157
zern wrote: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 ต้องเรียนรู้และอาศัยประสบการณ์ อาจจะสับสนในช่วงแรก แต่เมื่อใช้ไปสักระยะ จะเกิดความเข้าใจและใช้งานได้อย่างกระชับเองครับ
Re: อยากเขียนสูตร countif ด้วย VBA และทำซ้ำอีก 1000 แถวโดยไม่ต้องวนลูปครับ
Posted: Thu Jun 09, 2016 10:31 pm
by zern
ข้อ ๑ ผมหมายถึง ใช้สูตร .Index(.CountIf(Worksheets("sheet1").[A:A], [G1:G10000]), 0) และ .CountIf(Worksheets("sheet1").[A:A], [G1:G10000]) มันได้ผลเหมือนกันในไฟล์นี้น่ะครับ เลยสงสัยว่าใส่ index(...) มาเพราะมีเหตุผลอะไรหรือเปล่าเผื่อมีทริคใหม่ๆ ^ ^
ส่วนข้ออื่นเข้าใจแล้วครับ
ข้อ ๒ มันคืนค่า error และพยายาม debug มาให้ด้วยนี่เอง ถึงได้ทำให้ vba วิ่งไม่ไป
ข้อ ๓ ตราบใดที่ยังเขียนแค่ในเอกเซล ก็ไม่ต้องมี excel. ก็ได้
Re: อยากเขียนสูตร countif ด้วย VBA และทำซ้ำอีก 1000 แถวโดยไม่ต้องวนลูปครับ
Posted: Thu Jun 09, 2016 10:50 pm
by snasui
ข้อ ๑ เป็นการทำให้เป็น Array แล้วไปกำหนดให้กับ y จะเห็นว่าผมได้ประกาศตัวแปร y ให้เป็น Varaint (Varaint รวมถึง Array ด้วย)
Index ที่ใส่เข้าไปด้วยความเคยชิน เป็นการช่วยทำให้เป็น Array ถ้าเขียนใน Worksheet โดยใช้ลำพังเพียง Countif โดยไม่ใช้ Index ช่วย มันจะไม่สามารถเป็น Array ได้ กรณีนี้ลบทิ้งได้ครับ
Re: อยากเขียนสูตร countif ด้วย VBA และทำซ้ำอีก 1000 แถวโดยไม่ต้องวนลูปครับ
Posted: Fri Jun 10, 2016 9:42 am
by zern
อ่อ การใส่ 0 ทำให้เป็น array นี่เอง ผมเคยเจอกรณีเป็น 0 แล้วผลลัพธ์ของ index(A:A,0) มันเทียบเท่ากับ =index(A:A,row()) ก็คิดอยู่ว่าทำไมมันถึงดึงผลลัพธ์แบบนี้ แทนที่จะเป็น error ขอบคุณครับ จะได้จำไว้ใช้ประโยชน์
ปล.หวังว่าผมคงหมดประเด็นสงสัยละ ปัญหาเยอะจริง 555