Lấy giá trị tuần tự liên tục trong 1 cột!

alias1313 · 2/8/18

Em cần lấy giá trị trong một cột ( >1000 hàng) bằng cách sử dụng VBA, mong các anh giúp đỡ!

Qui luật để lấy như sau:
Tai cột E
- Giá trị đầu tiên > 50 lấy, gán giá trị ra ô excel cột G
- Giá trị tiếp theo < 50 lấy, gán giá trị ra ô excel tiếp theo ô trên
- Giá trị tiếp theo > 50 lấy
- Giá trị tiếp theo < 50 lấy.

Cứ lấy xen kẽ như vậy! Giá trị lấy được gán ra cột G.
Em viết 1 đoạn code như bên dưới, nhưng em không biết muốn lấy giá trị tiếp theo phải viết thế nào để hàm for nó hiểu.
Mong các anh giúp em! Em cảm ơn!

Mã:

Sub LocGiaTri()

   Dim range1 as range

For each range1 in Range("E1:E20")

If range1.value > 50 then

End if

Next range1

End sub

alias1313 · 3/8/18

befaint đã viết:
Gom 11 sheets rồi mới chạy code bài này. Tức là tối qua phải đăng bài gom 11 sheets trước, sáng nay đăng bài này.
Hoặc đăng một bài nêu hai yêu cầu luôn và ngồi chờ món súp cá thôi.

Hihi! Đúng ra em muốn tự làm phần đó! Vì em nghĩ mình làm được! nhưng qua đến giờ em làm mãi ko được nên đành phải nhờ Thầy và các anh giúp!
Em cảm ơn anh!

ndu96081631 · 3/8/18

alias1313 đã viết:
Hihi! Đúng ra em muốn tự làm phần đó! Vì em nghĩ mình làm được! nhưng qua đến giờ em làm mãi ko được nên đành phải nhờ Thầy và các anh giúp!
Em cảm ơn anh!

Chẳng phải bạn đã hỏi về việc tổng hợp 10 sheets vào 1 sheet ở đây sao:
https://www.giaiphapexcel.com/diend...thứ-tự-giảm-dần-vào-1-sheet-cuối-cùng.136668/
Giờ cứ thế mà áp dụng đi

VetMini · 3/8/18

alias1313 đã viết:
Mã:

Sub Test() Dim arr Dim idx As Long Dim bChk As Boolean arr = Sheet1.Range("E4:E1000").Value ReDim aRes(1 To UBound(arr), 1 To 1) For idx = 1 To UBound(arr) If arr(idx, 1) <> Empty Then If bChk = (arr(idx, 1) < 50) Then aRes(idx, 1) = arr(idx, 1) bChk = Not bChk End If End If Next Sheet1.Range("G4:G1000").Value = aRes End Sub

Code trên khi chạy trên 1 sheet thì không sao, nhưng khi chạy trên nhiều sheet 1 lúc, thì bị tình trạng:
- Giá trị: bChk - cuối cùng của sheet1 tính sang cho sheet2 ( khi sang sheet2 phải reset lại biến bChr) => giá trị đầu tiên lấy bị sai ( lúc >50, lúc < 50)
- Em mò mãi mới thấy cách sửa đc lỗi này bằng cách thêm bChk = false để reset. và thấy khi chạy nhiều sheet kết quả vẫn đúng.
-Em muốn hỏi: đó có phải là cách reset biến Bolean không và cách đúng nhất là gì ạ!
Em cảm ơn Thầy cùng các anh!

Với code trên, bạn làm cách nào chạy trên nhiều sheet 1 lúc?

befaint đã viết:
. Vậy biển quảng cáo và hàng bán khác nhau.
Tùy gia chủ vậy.

Trường hợp này ta chỉ cần 1 mảng để làm việc, vừa vào vừa ra.
(vì không còn yếu tố dòng lẻ >= 50, dòng chẵn <50 cho nên bắt buộc phải giữ biến lính canh)

befaint · 3/8/18

VetMini đã viết:
Trường hợp này ta chỉ cần 1 mảng để làm việc, vừa vào vừa ra.
(vì không còn yếu tố dòng lẻ >= 50, dòng chẵn <50 cho nên bắt buộc phải giữ biến lính canh)

Bớt được một biến mảng ạ.

PHP:

Sub vidu2()
    Const N As Long = 50
    Dim a(), s, i As Long, flag As Boolean
    a = Sheet1.Range("E1:E20").Value
    flag = True
    For i = 1 To UBound(a, 1) Step 1
        s = a(i, 1)
        If ((Len(s) > 0) * IsNumeric(s)) = 1 Then
            If (s >= N) = flag Then
                flag = Not flag
            Else
                a(i, 1) = ""
            End If
        End If
    Next i
    Sheet1.Range("G1").Resize(UBound(a, 1), 1) = a
End Sub

ndu96081631 · 3/8/18

befaint đã viết:

Bớt được một biến mảng ạ.

PHP:

Sub vidu2()
    Const N As Long = 50
    Dim a(), s, i As Long, flag As Boolean
    a = Sheet1.Range("E1:E20").Value
    flag = True
    For i = 1 To UBound(a, 1) Step 1
        s = a(i, 1)
        If ((Len(s) > 0) * IsNumeric(s)) = 1 Then
            If (s >= N) = flag Then
                flag = Not flag
            Else
                a(i, 1) = ""
            End If
        End If
    Next i
    Sheet1.Range("G1").Resize(UBound(a, 1), 1) = a
End Sub

Trường hợp thay gì lấy 3 phần tử trong 100 thì code này lại lấy tất cả rồi xóa 97 phần tử
???!!!

befaint · 3/8/18

ndu96081631 đã viết:
Trường hợp thay gì lấy 3 phần tử trong 100 thì code này lại lấy tất cả rồi xóa 97 phần tử
???!!!

Viết bài xong em cũng thấy hơi nghi nghi.

(Nhưng bù lại khoản chiếm bộ nhớ của RAM - trường hợp mảng có kích thước lớn).

ndu96081631 · 3/8/18

befaint đã viết:
Nhưng bù lại khoản chiếm bộ nhớ của RAM - trường hợp mảng có kích thước lớn).

Cái này thì tôi hơi... nghi. Cảm tính cho thấy rằng dù "dựng" 1 mảng 100 phần tử hay gán range 100 cells vào mảng thì.. cũng thế thôi. Sao lại có chuyện chiếm Ram nhiều hay ít
Bạn cho 1 ví dụ thử xem

befaint · 3/8/18

ndu96081631 đã viết:
Cái này thì tôi hơi... nghi. Cảm tính cho thấy rằng dù "dựng" 1 mảng 100 phần tử hay gán range 100 cells vào mảng thì.. cũng thế thôi. Sao lại có chuyện chiếm Ram nhiều hay ít
Bạn cho 1 ví dụ thử xem

Mới có bài hôm trước nói về mảng kích thước lớn thì lỗi Out of memory mà anh.
Bài này dựng một biến mảng (cho cả đầu vào và đầu ra) chiếm dung lượng RAM là x (MB).
Các bài trước dựng hai biến mảng (một cho đầu vào, một cho đầu ra) có kích thước bằng nhau => chiếm dung lượng RAM = 2*x (MB)

Anh thử từng dòng 1 và 2 xem.

PHP:

Sub viduRAM()
    Dim a(), b()
1    a = Range("A1:BZ1048576").Value2
2    ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
End Sub

VetMini · 3/8/18

befaint đã viết:
Mới có bài hôm trước nói về mảng kích thước lớn thì lỗi Out of memory mà anh.
Bài này dựng một biến mảng (cho cả đầu vào và đầu ra) chiếm dung lượng RAM là x (MB).
Các bài trước dựng hai biến mảng (một cho đầu vào, một cho đầu ra) có kích thước bằng nhau => chiếm dung lượng RAM = 2*x (MB)
...

Đối với tôi "tiết kiệm bộ nhớ" không quan trọng lắm. Trừ phi tôi biết trước là code cần làm việc với mảng MxN với M và N khá lớn. Hoặc trường hợp viết hàm đệ quy.
Lúc tôi nói chuyện "chỉ cần 1 mảng" là dụng ý khác. Giải thuật này ngầm ý là phần đầu vào sẽ đồng dạng với phần đầu ra.
Tương tự dụng ý ở bài #6 thì ngầm ý là kết quả lớn sẽ nằm ở các dòng lẻ trong khi kết quả nhỏ sẽ nằm ở các dòng chẵn.

Chủ ý: code hiệu quả là một chuyện. Tới trình độ của bạn thì nên học nhìn xa hơn nữa - điển hình là: cái dạng kết quả sẽ ra sao? ta sẽ đọc được gì từ chúng?. Bài này là một ví dụ không hay lắm, nhưng ở đây không có mấy dịp để tôi chỉ dẫn.

ndu96081631 · 3/8/18

befaint đã viết:
Mới có bài hôm trước nói về mảng kích thước lớn thì lỗi Out of memory mà anh.
Bài này dựng một biến mảng (cho cả đầu vào và đầu ra) chiếm dung lượng RAM là x (MB).
Các bài trước dựng hai biến mảng (một cho đầu vào, một cho đầu ra) có kích thước bằng nhau => chiếm dung lượng RAM = 2*x (MB)

Anh thử từng dòng 1 và 2 xem.

PHP:

Sub viduRAM() Dim a(), b() 1 a = Range("A1:BZ1048576").Value2 2 ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2)) End Sub

Ngay dòng lệnh 1 đã Out of memory rồi

befaint · 3/8/18

VetMini đã viết:
Chủ ý: code hiệu quả là một chuyện. Tới trình độ của bạn thì nên học nhìn xa hơn nữa - điển hình là: cái dạng kết quả sẽ ra sao? ta sẽ đọc được gì từ chúng?. Bài này là một ví dụ không hay lắm, nhưng ở đây không có mấy dịp để tôi chỉ dẫn.

Em thấy có phần hơi trừu tượng...

Mong có dịp anh chỉ dẫn trường hợp cụ thể.

VetMini · 3/8/18

befaint đã viết:
Em thấy có phần hơi trừu tượng...
Mong có dịp anh chỉ dẫn trường hợp cụ thể.

Điển hình:
Vì Excel chủ yếu là bảng tính trải rộng, tức là một vùng dữ liệu diễn ra thành một mảng 2 chiều.
Do đó, các bạn ở diễn đàn này quen với phương pháp 2 mảng. Một mảng đầu vào chép lại dữ liệu của bảng; và một mảng đầu ra ghi dữ liệu đã tính toán; ở giữa có thêm một phần đếm số đầu ra; làm xong thì chép lại vào bảng tính.
Nhắc lại, vì Excel chủ yếu là bảng 2 chiều cho nên phương pháp trên rất có hiệu quả.
Tuy nhiên, cũng chính vì sự hiệu quả ấy mà lâu dần các bạn, nhất là những người học code vài năm gần đây quên mất rằng đó không phải là cách duy nhất.

Hau151978 · 3/8/18

VetMini đã viết:
Trường hợp này ta chỉ cần 1 mảng để làm việc, vừa vào vừa ra.
(vì không còn yếu tố dòng lẻ >= 50, dòng chẵn <50 cho nên bắt buộc phải giữ biến lính canh)

Có thể dùng 1 mảng: duyệt từng phần tử đầu tiên tìm số >=50, nếu gặp số <50 thì gán = 0, gặp số đầu tiên >=50 thì giữ nguyên; tiếp tục tìm số <50 cũng tương tự, nếu gặp số >=50 thì xóa về 0, giữ lại số đầu tiên <50. (Em ý tưởng vậy thôi chứ chưa code).

ndu96081631 · 3/8/18

befaint đã viết:
Mới có bài hôm trước nói về mảng kích thước lớn thì lỗi Out of memory mà anh.
Bài này dựng một biến mảng (cho cả đầu vào và đầu ra) chiếm dung lượng RAM là x (MB).
Các bài trước dựng hai biến mảng (một cho đầu vào, một cho đầu ra) có kích thước bằng nhau => chiếm dung lượng RAM = 2*x (MB)

Anh thử từng dòng 1 và 2 xem.

PHP:

Sub viduRAM() Dim a(), b() 1 a = Range("A1:BZ1048576").Value2 2 ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2)) End Sub

Máy tôi yêu nên tôi test vầy:
- Mở Excel (bảng tính chưa có bất cứ dữ liệu nào) và chèn code

Mã:

Sub Test()
  Dim arr
  arr = Range("A1:Z1048576").Value
End Sub

- Mở Task Manager lên, chờ máy tính ổn định, kiểm tra Ram trên máy đang 62%
- Chạy code trên, kiểm tra thấy Ram tăng lên 82%
- Test khoảng 5 lần như thế, kết quả như nhau
- Tiếp theo đổi code khác:

Mã:

Sub Test()
  Dim arr
  ReDim arr(1 To 1048576, 1 To 26)
End Sub

- Mở Task Manager lên, chờ máy tính ổn định, kiểm tra Ram trên máy đang 62%
- Chạy code trên, kiểm tra thấy Ram tăng lên 72%
- Test khoảng 5 lần như thế, kết quả như nhau
----------------
Kết luận (riêng trên máy tôi thôi nha): Dựng 1 mảng tốn ít ram hơn so với gán range vào mảng.
Nói chung là vì thấy bạn có ý kiến nên tôi test thử xem sao thôi chứ trước giờ chẳng quan tâm vụ này. Tôi thực tế lắm, cứ thấy cái nào nhanh thì dùng

befaint · 3/8/18

ndu96081631 đã viết:
Máy tôi yêu nên tôi test vầy:
- Mở Excel (bảng tính chưa có bất cứ dữ liệu nào) và chèn code

Mã:

Sub Test() Dim arr arr = Range("A1:Z1048576").Value End Sub

- Mở Task Manager lên, chờ máy tính ổn định, kiểm tra Ram trên máy đang 62%
- Chạy code trên, kiểm tra thấy Ram tăng lên 82%
- Test khoảng 5 lần như thế, kết quả như nhau
- Tiếp theo đổi code khác:

Mã:

Sub Test() Dim arr ReDim arr(1 To 1048576, 1 To 26) End Sub

- Mở Task Manager lên, chờ máy tính ổn định, kiểm tra Ram trên máy đang 62%
- Chạy code trên, kiểm tra thấy Ram tăng lên 72%
- Test khoảng 5 lần như thế, kết quả như nhau
----------------
Kết luận (riêng trên máy tôi thôi nha): Dựng 1 mảng tốn ít ram hơn so với gán range vào mảng.
Nói chung là vì thấy bạn có ý kiến nên tôi test thử xem sao thôi chứ trước giờ chẳng quan tâm vụ này. Tôi thực tế lắm, cứ thấy cái nào nhanh thì dùng

Hình như anh hiểu nhầm ý của em chút ạ.
Ý của em là với trường hợp mảng kích thước lớn, dung lượng Ram giới hạn, vừa đủ cho ta dùng một biến mảng thì code trường hợp dùng một mảng dùng được, còn code dùng hai mảng (một mảng chứa đầu vào, một mảng chứa kết quả đầu ra) thì không dùng được.
Còn so sánh trường hợp mảng a copy range vào (cho dù range đều rỗng) và mảng b ta dựng (có kích thước bằng mảng a) thì a chiếm dung lượng Ram nhiều hơn b. Chắc do a còn động tác copy nữa, chi tiết chắc chờ anh nào đó vào giải thích cụ thể ạ. Hình như trước đây, có bài nói về kích thước mảng này rồi, từ bài anh @kieu manh hỏi...

ndu96081631 · 3/8/18

befaint đã viết:
Hình như anh hiểu nhầm ý của em chút ạ.
Ý của em là với trường hợp mảng kích thước lớn, dung lượng Ram giới hạn, vừa đủ cho ta dùng một biến mảng thì code trường hợp dùng một mảng dùng được, còn code dùng hai mảng (một mảng chứa đầu vào, một mảng chứa kết quả đầu ra) thì không dùng được.
Còn so sánh trường hợp mảng a copy range vào (cho dù range đều rỗng) và mảng b ta dựng (có kích thước bằng mảng a) thì a chiếm dung lượng Ram nhiều hơn b. Chắc do a còn động tác copy nữa, chi tiết chắc chờ anh nào đó vào giải thích cụ thể ạ. Hình như trước đây, có bài nói về kích thước mảng này rồi, từ bài anh @kieu manh hỏi...

Nói thật là trước giờ tôi cũng chưa từng làm việc với mảng quá lớn trên Excel. Quan điểm của tôi là: Nếu như có dữ liệu khủng như vậy thì ta phải tính đến việc thay đổi hệ quản trị CSDL chứ sao lại làm trên Excel
Ngoài ra (cũng là quan điểm cá nhân): Chẳng việc gì phải tiết kiệm 1 biến để đi dùng chung như thế. Cái nào ra cái đó để kiểm soát cho dễ

VetMini · 3/8/18

befaint đã viết:
...
Còn so sánh trường hợp mảng a copy range vào (cho dù range đều rỗng) và mảng b ta dựng (có kích thước bằng mảng a) thì a chiếm dung lượng Ram nhiều hơn b. Chắc do a còn động tác copy nữa, chi tiết chắc chờ anh nào đó vào giải thích cụ thể ạ. Hình như trước đây, có bài nói về kích thước mảng này rồi, từ bài anh @kieu manh hỏi...

So sánh khập khễnh.
Mảng ở đây là dạng variant.
Một trường hợp thì do copy từ range ra cho nên mỗi phần tử đều đã xác định (empty thì cũng là xác định - dù nhỏ hơn có dữ liệu 1 chút)
Trường hợp kia thì chỉ mới có chỗ cho mỗi phần tử. Chúng còn chưa có chỗ để chỉ vào.
Sau khi gán được từng ô trong bảng tính cho mảng rồi hãy tính đến chuyện so sánh.

Chú: tôi đã nói với bạn về cái nhìn ở một thớt khác rồi. Bạn đặt vấn đề tiết kiệm này nọ thì chỉ là vấn đề của hiện tại (chuyện trước mắt). Muốn nói chuyện về tương lai (chuyện kế tiếp) bạn phải biết nhìn kết quả (xem lại bài #29)
Giải quyết tốt vấn đề hiện tại chỉ đưa bạn đến trình độ chuyên nghiệp (cái địa vị mà nhiều người gọi là pro). Phải nhìn được tổng thể và xa hơn nữa mới có hy vọng tiến lên hàng thượng sư (cái địa vị mà tôi vẫn gọi là guru)

VetMini · 4/8/18

Bổ sung:
Cái vụ đếm bộ nhớ thì có (ít nhất) 1 người ở đây biết rất rõ. Vì anh ta (theo tôi nghĩ) có sở thích và khả năng tìm đến nơi đến chốn.
Tôi nhớ có một bài anh ta giải thích cặn kẽ về cách sử dụng bộ nhớ của loại biến variant, lần khác giải thích cặn kẽ về kỹ thuật truy cứu bộ nhớ của mảng (theo cột hay theo dòng)
Hình như mấy bữa nay anh ta đi vắng.

Hau151978 · 4/8/18

VetMini đã viết:
Bổ sung:
Cái vụ đếm bộ nhớ thì có (ít nhất) 1 người ở đây biết rất rõ. Vì anh ta (theo tôi nghĩ) có sở thích và khả năng tìm đến nơi đến chốn.
Tôi nhớ có một bài anh ta giải thích cặn kẽ về cách sử dụng bộ nhớ của loại biến variant, lần khác giải thích cặn kẽ về kỹ thuật truy cứu bộ nhớ của mảng (theo cột hay theo dòng)
Hình như mấy bữa nay anh ta đi vắng.

Bác đang nói bác @batman1 phải không? Ngoài ra, bác @nguyen Duy Tuân cũng thế, bác Tuân có bài viết tăng tốc copy mảng bằng api CopyMemory trong diễn đàn.
Khi khởi tạo mảng variant thì vùng nhớ cho các phần tử mảng =16bytes x số phần tử, khi gán nội dung của range trống vào cũng thế do nội dung phần tử mảng là null nằm hoàn toàn trong variant. Ở đây bộ nhớ tốn nhiều hơn do thao tác copy range vào mảng chứ không phải mảng phình to ra.

VetMini · 4/8/18

Hau151978 đã viết:
.... Ở đây bộ nhớ tốn nhiều hơn do thao tác copy range vào mảng chứ không phải mảng phình to ra.

Tôi dự định đề nghị phân tích page faults để xem có gì đăc biệt. Nhưng có bằng chứng này thì thôi khong cần. Vì như vậy đủ để chứng tỏ phương pháp so sánh trên hoàn toàn khập khễnh đến què quặt.

Hau151978 đã viết:
... Ngoài ra, bác @nguyen Duy Tuân cũng thế, bác Tuân có bài viết tăng tốc copy mảng bằng api CopyMemory trong diễn đàn....

Ngày xưa, MS dùng C/C++ để thực hiện các hàm Excel và một số API.
MemCopy và MemMove là hai Macro (macro máy chứ khong phải của Excel) nổi tiếng là nhanh của C/C++
Lưu ý rằng tôi dùng từ "ngày xưa" ở trên. Hiện nay, với kỹ thuật cải tiến của compiler (trình dịch), hầu hết các nên tảng đều có ngôn ngữ viết riêng của họ. Rất có thể MS đã có trình dịch và ngôn ngữ nào đó để viết các macro cho API's

Lấy giá trị tuần tự liên tục trong 1 cột!

Thành viên hoạt động

File đính kèm

Thành viên hoạt động

Huyền thoại GPE

Ăn cùng góc phố

|||||||||||||

Huyền thoại GPE

|||||||||||||

Huyền thoại GPE

|||||||||||||

Ăn cùng góc phố

Huyền thoại GPE

|||||||||||||

Ăn cùng góc phố

Thành viên tích cực

Huyền thoại GPE

|||||||||||||

Huyền thoại GPE

Ăn cùng góc phố

Ăn cùng góc phố

Thành viên tích cực

Ăn cùng góc phố

Facebook

Group