Tìm ngày không nằm trong vùng được chọn (1 người xem)

Miccpro · 4/8/16

Em có 3 cột trong đó 2 cột dữ liệu ngày bắt đầu và ngày kết thúc, cột thứ 3 là cột từ Min(ngày bắt đầu) đến Max(ngày kết thúc). Giờ em muốn tìm những ngày ở cột thứ 3 mà không nằm trong 2 cột dữ liệu có được không ạ, giải bài toán bằng hàm hay VBA tiện hơn vì dữ liệu tương đối nhiều. Em xin cảm ơn cả nhà

giangleloi · 4/8/16

Miccpro đã viết:
Em có 3 cột trong đó 2 cột dữ liệu ngày bắt đầu và ngày kết thúc, cột thứ 3 là cột từ Min(ngày bắt đầu) đến Max(ngày kết thúc). Giờ em muốn tìm những ngày ở cột thứ 3 mà không nằm trong 2 cột dữ liệu có được không ạ, giải bài toán bằng hàm hay VBA tiện hơn vì dữ liệu tương đối nhiều. Em xin cảm ơn cả nhà

Tại I6 xài công thức sau rồi fill xuống. Sau đó Filter những giá trị bằng 0 là cái cần tìm. Thích có màu báo hiệu thì sử dụng Conditional Formatting.

Mã:

=COUNTIF($E$6:$F$11,H6)

P/s: Dữ liệu nhiều là bao nhiêu vậy bạn? Cứ xài công thức thử đi nè.

phulien1902 · 4/8/16

giangleloi đã viết:
Tại I6 xài công thức sau rồi fill xuống. Sau đó Filter những giá trị bằng 0 là cái cần tìm. Thích có màu báo hiệu thì sử dụng Conditional Formatting.

Mã:

=COUNTIF($E$6:$F$11,H6)

P/s: Dữ liệu nhiều là bao nhiêu vậy bạn? Cứ xài công thức thử đi nè.

Chưa đúng ý của chủ Topic rồi bạn ơi.
Trong File chỉ có 4 ngày thiếu thôi.

Miccpro · 4/8/16

giangleloi đã viết:
Tại I6 xài công thức sau rồi fill xuống. Sau đó Filter những giá trị bằng 0 là cái cần tìm. Thích có màu báo hiệu thì sử dụng Conditional Formatting.

Mã:

=COUNTIF($E$6:$F$11,H6)

P/s: Dữ liệu nhiều là bao nhiêu vậy bạn? Cứ xài công thức thử đi nè.

Công thức của bác chưa đúng với trường hợp của em ạ. Ví dụ: ngày BĐ: 01/01/2016; ngày KT: 03/01/2016 thì nó sẽ là ngày 01;02;03/01/2016 chứ không phải ngày 01 và 03 ạ

giangleloi · 4/8/16

phulien1902 đã viết:
Chưa đúng ý của chủ Topic rồi bạn ơi.
Trong File chỉ có 4 ngày thiếu thôi.

Xin lỗi. Giờ mới để ý. hehe

Miccpro · 4/8/16

Xin lỗi bác giangleloi vì câu hỏi không rõ ràng nhé

HieuCD · 4/8/16

bạn chạy code

Mã:

Sub GPE()
arr = Sheets("Sheet1").Range("H6", Sheets("Sheet1").Range("H6").End(xlDown))
darr = Sheets("Sheet1").Range("E6", Sheets("Sheet1").Range("F6").End(xlDown))
For i = 1 To UBound(arr)
    tmp = 0
    For j = 1 To UBound(darr)
        If arr(i, 1) >= darr(j, 1) And arr(i, 1) <= darr(j, 2) Then
            tmp = 1: Exit For
        End If
    Next
    If tmp = 0 Then Range("H" & i + 5).Interior.Color = 65535
Next
End Sub

lenongree · 4/8/16

Miccpro đã viết:
Em có 3 cột trong đó 2 cột dữ liệu ngày bắt đầu và ngày kết thúc, cột thứ 3 là cột từ Min(ngày bắt đầu) đến Max(ngày kết thúc). Giờ em muốn tìm những ngày ở cột thứ 3 mà không nằm trong 2 cột dữ liệu có được không ạ, giải bài toán bằng hàm hay VBA tiện hơn vì dữ liệu tương đối nhiều. Em xin cảm ơn cả nhà

Bạn dùng CT này tại I6:

Mã:

I6=IFERROR(LOOKUP(1,1/(H6>=$E$6:$E$11)/(H6<=$F$6:$F$11)),"Khong nam trong vung")

Fill xuống!!!

Miccpro · 4/8/16

HieuCD đã viết:

bạn chạy code

Mã:

Sub GPE()
arr = Sheets("Sheet1").Range("H6", Sheets("Sheet1").Range("H6").End(xlDown))
darr = Sheets("Sheet1").Range("E6", Sheets("Sheet1").Range("F6").End(xlDown))
For i = 1 To UBound(arr)
    tmp = 0
    For j = 1 To UBound(darr)
        If arr(i, 1) >= darr(j, 1) And arr(i, 1) <= darr(j, 2) Then
            tmp = 1: Exit For
        End If
    Next
    If tmp = 0 Then Range("H" & i + 5).Interior.Color = 65535
Next
End Sub

Code của bác khi thay đổi ngày tháng trong cột E;F (dữ liệu) thì lại phải bôi trắng cột H rồi chạy code mới hiệu quả. Có cách gì mỗi lần chạy code nó tự động xóa các màu đã bôi để bôi lại không bác?

giangleloi · 4/8/16

lenongree đã viết:
Bạn dùng CT này tại I6:

Mã:

I6=IFERROR(LOOKUP(1,1/(H6>=$E$6:$E$11)/(H6<=$F$6:$F$11)),"Khong nam trong vung")

Fill xuống!!!

Công thức trên sai ở ngày 08. Chắc như vầy mới được:

Mã:

=IFERROR(LOOKUP(2,1/(H6>=$E$6:$E$11)/(H6<=$F$6:$F$11)),0)

phulien1902 · 4/8/16

lenongree đã viết:
Bạn dùng CT này tại I6:

Mã:

I6=IFERROR(LOOKUP(1,1/(H6>=$E$6:$E$11)/(H6<=$F$6:$F$11)),"Khong nam trong vung")

Fill xuống!!!

Áp dụng công thức của bạn, thì ngày 08/1/2016 phải cho kết quả là 1, sao kết quả là 0 nhỉ ?

lenongree · 4/8/16

giangleloi đã viết:
Công thức trên sai ở ngày 08. Chắc như vầy mới được:

Mã:

=IFERROR(LOOKUP(2,1/(H6>=$E$6:$E$11)/(H6<=$F$6:$F$11)),0)

Hi, em đã chỉnh lại rồi, bác nhanh quá!!!! --=0

HieuCD · 4/8/16

Miccpro đã viết:
Code của bác khi thay đổi ngày tháng trong cột E;F (dữ liệu) thì lại phải bôi trắng cột H rồi chạy code mới hiệu quả. Có cách gì mỗi lần chạy code nó tự động xóa các màu đã bôi để bôi lại không bác?

thêm 2 dòng lệnh

Mã:

Sub GPE()
Sheets("Sheet1").Range("H6", Sheets("Sheet1").Range("H6").End(xlDown)).Select
Selection.Interior.Pattern = xlNone
arr = Sheets("Sheet1").Range("H6", Sheets("Sheet1").Range("H6").End(xlDown))
darr = Sheets("Sheet1").Range("E6", Sheets("Sheet1").Range("F6").End(xlDown))
For i = 1 To UBound(arr)
    tmp = 0
    For j = 1 To UBound(darr)
        If arr(i, 1) >= darr(j, 1) And arr(i, 1) <= darr(j, 2) Then
            tmp = 1: Exit For
        End If
    Next
    If tmp = 0 Then Range("H" & i + 5).Interior.Color = 65535
Next
End Sub

lenongree · 4/8/16

phulien1902 đã viết:
Áp dụng công thức của bạn, thì ngày 08/1/2016 phải cho kết quả là 1, sao kết quả là 0 nhỉ ?

Em chỉnh lại Ct rồi anh, bị nhầm dấu / thành dấu * thôi, hic, em chỉnh nhanh lắm mà ta!!

giangleloi · 4/8/16

Miccpro đã viết:
Code của bác khi thay đổi ngày tháng trong cột E;F (dữ liệu) thì lại phải bôi trắng cột H rồi chạy code mới hiệu quả. Có cách gì mỗi lần chạy code nó tự động xóa các màu đã bôi để bôi lại không bác?

Thêm dòng này:

Mã:

Range("H6:H" & UBound(arr, 1) + 5).Interior.Color = xlNone

dưới dòng này:

Mã:

darr = Sheets("Sheet1").Range("E6", Sheets("Sheet1").Range("F6").End(xlDown))

Miccpro · 4/8/16

Như vậy em quyết định dùng công thức của các bác kết hợp Conditional Formatting để bôi màu là nhanh nhất. Thanks all

giangleloi · 4/8/16

lenongree đã viết:
Hi, em đã chỉnh lại rồi, bác nhanh quá!!!!

GPE dạo này nhiều người nhàn rỗi lắm. Một người khó có thể nhanh hơn nhiều người được.

HieuCD · 4/8/16

thay 2 dòng lệnh
Sheets("Sheet1").Range("H6", Sheets("Sheet1").Range("H6").End(xlDown)).Select
Selection.Interior.Pattern = xlNone
bằng dòng lệnh sẽ đẹp hơn
Sheets("Sheet1").Range("H6", Sheets("Sheet1").Range("H6").End(xlDown)).Interior.Pattern = xlNone

Miccpro · 4/8/16

giangleloi đã viết:
Thêm dòng này:

Mã:

Range("H6:H" & UBound(arr, 1) + 5).Interior.Color = xlNone

dưới dòng này:

Mã:

darr = Sheets("Sheet1").Range("E6", Sheets("Sheet1").Range("F6").End(xlDown))

Bác thử kiểm tra giúp em trong trường hợp 2 cột E và F mà có ô rỗng thì có cách nào code vẫn chạy và bỏ qua ô rỗng không bác

giangleloi · 4/8/16

Miccpro đã viết:
Bác thử kiểm tra giúp em trong trường hợp 2 cột E và F mà có ô rỗng thì có cách nào code vẫn chạy và bỏ qua ô rỗng không bác

========================================================

Ba Tê · 4/8/16

Miccpro đã viết:
Bác thử kiểm tra giúp em trong trường hợp 2 cột E và F mà có ô rỗng thì có cách nào code vẫn chạy và bỏ qua ô rỗng không bác

Nếu ...... dữ liệu nhiều thì thử cái này coi sao.

PHP:

Public Sub DicBu()
Dim Dic As Object, sArr(), I As Long, J As Long, Rng As Range, Cll As Range
Set Dic = CreateObject("Scripting.Dictionary")
sArr = Range("E6", Range("F65536").End(xlUp)).Value
Set Rng = Range("H6", Range("H65536").End(xlUp))
For I = 1 To UBound(sArr)
    If sArr(I, 1) <> Empty Then
        For J = sArr(I, 1) To sArr(I, 2)
            Dic.Item(J) = ""
        Next J
    End If
Next I
Rng.Interior.ColorIndex = 0
For Each Cll In Rng
    If Not Dic.Exists(Cll.Value) Then Cll.Interior.ColorIndex = 36
Next Cll
Set Dic = Nothing: Set Rng = Nothing
End Sub

Miccpro · 4/8/16

Ba Tê đã viết:

Nếu ...... dữ liệu nhiều thì thử cái này coi sao.

PHP:

Public Sub DicBu()
Dim Dic As Object, sArr(), I As Long, J As Long, Rng As Range, Cll As Range
Set Dic = CreateObject("Scripting.Dictionary")
sArr = Range("E6", Range("F65536").End(xlUp)).Value
Set Rng = Range("H6", Range("H65536").End(xlUp))
For I = 1 To UBound(sArr)
    If sArr(I, 1) <> Empty Then
        For J = sArr(I, 1) To sArr(I, 2)
            Dic.Item(J) = ""
        Next J
    End If
Next I
Rng.Interior.ColorIndex = 0
For Each Cll In Rng
    If Not Dic.Exists(Cll.Value) Then Cll.Interior.ColorIndex = 36
Next Cll
Set Dic = Nothing: Set Rng = Nothing
End Sub

Chuẩn bác ạ, thanks bác nhiều

Miccpro · 4/8/16

Nhân tiện bác Ba Tê đang online em hỏi thêm bác tý ở bài hôm trước cũng giống cái này: Khi dữ liệu gặp ô rỗng thì trả về rỗng và chạy tiếp ô tiếp theo
http://www.giaiphapexcel.com/forum/showthread.php?96769-Help-code-vba-tìm-kiếm-và-thay-thế/page3

Ba Tê · 4/8/16

Miccpro đã viết:
Nhân tiện bác Ba Tê đang online em hỏi thêm bác tý ở bài hôm trước cũng giống cái này: Khi dữ liệu gặp ô rỗng thì trả về rỗng và chạy tiếp ô tiếp theo
http://www.giaiphapexcel.com/forum/showthread.php?96769-Help-code-vba-tìm-kiếm-và-thay-thế/page3

Bạn tìm dòng nào có sArr=... thay bằng cái này thử xem có chạy không.

PHP:

sArr = .Range("A2", .Range("A65536").End(xlUp)).Value

Miccpro · 4/8/16

Ba Tê đã viết:
Bạn tìm dòng nào có sArr=... thay bằng cái này thử xem có chạy không.

PHP:

sArr = .Range("A2", .Range("A65536").End(xlUp)).Value

Chạy đã thay và chạy rất nuột bác ạ, chỉ mỗi tội nếu chỉ mỗi A2 có giá trị thì bị lỗi bác ạ

Ba Tê · 4/8/16

Miccpro đã viết:
Chạy đã thay và chạy rất nuột bác ạ, chỉ mỗi tội nếu chỉ mỗi A2 có giá trị thì bị lỗi bác ạ

Chỉ có 1 dòng thì bạn xử thủ công đi. Tìm và thay thế làm gì cho "oải".
Tôi "chạy" với cái "nếu" của bạn rồi Từ đầu nói rõ dữ liệu có thể không có dòng nào hoặc nửa dòng, hoặc chỉ có 1 dòng...

Miccpro · 4/8/16

Ba Tê đã viết:
Chỉ có 1 dòng thì bạn xử thủ công đi. Tìm và thay thế làm gì cho "oải".
Tôi "chạy" với cái "nếu" của bạn rồi Từ đầu nói rõ dữ liệu có thể không có dòng nào hoặc nửa dòng, hoặc chỉ có 1 dòng...

Vâng ạ, cũng tại vì cái cột cần chuyển của em lấy dữ liệu từ địa chỉ khác nên em hơi cầu toàn tý bác ạ

Tìm ngày không nằm trong vùng được chọn (1 người xem)

Người dùng đang xem chủ đề này

Thành viên thường trực

File đính kèm

Thành viên gắn bó

GPE - My love

Thành viên thường trực

Thành viên gắn bó

Thành viên thường trực

Chuyên gia GPE

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

Thành viên thường trực

Thành viên gắn bó

GPE - My love

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

Chuyên gia GPE

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

Thành viên gắn bó

Thành viên thường trực

Thành viên gắn bó

Chuyên gia GPE

Thành viên thường trực

File đính kèm

Thành viên gắn bó

Cạo Rồi Khỏi Gội

Thành viên thường trực

Thành viên thường trực

File đính kèm

Cạo Rồi Khỏi Gội

Thành viên thường trực

File đính kèm

Cạo Rồi Khỏi Gội

Thành viên thường trực

Thời gian đếm ngược.