Đăng ký học Excel và VBA cùng GPE tháng 11 - TPHCM

Mua sách "VBA trong Excel - Cải thiện và tăng tốc" tái bản

Nhập mã tự động xuất kết quả

Thảo luận trong 'Tìm kiếm, dò tìm và tham chiếu' bắt đầu bởi sep_hatxel, 20 Tháng tư 2017.

  1. sep_hatxel

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

    Mong các bạn xem giúp mình trường hợp này.
    Dữ liệu có từ cột G trở đi.
    - Cột B là cột nhập mã dữ liệu. Cột C và D là cột xuất ra kết quả.
    - Nhập 1 dữ liệu bất kì vào cột Nhập dữ liệu, kiểm tra xem trong cùng 1 hàng nhập dữ liệu đó ở phần các cột dữ liệu có côt nào chứa dữ liệu nhập vào hay không? Nếu có thì 2 dữ liệu bên dưới sẽ xuất tự động sang 2 cột KQ1 và KQ2 . ( Trong trường hợp dữ liệu nhập vào mà không có ở trong các cột dữ liệu thì KQ1 và KQ2 sẽ báo là #)
    - Ví dụ:
    + Nhập M vào ô B2: kiểm tra trong hàng 2 các cột dữ liệu từ trái qua phải, thấy M ở ô dữ liệu G2 => khi đó hai dữ liệu bên dưới ô G2 là ô G3 và ô H3 tự động xuất sang ô C3 và ô D3.
    + Nhập P445 vào ô B5: kiểm tra trong hàng 5 các cột dữ liệu từ trái qua phải thấy P445 ở ô dữ liệu T5 => khi đó hai dữ liệu ở bên dưới ô T5 là ô S6 và ô T6 tự động xuất sang ô C6 và ô D6
    + Nhập a vào ô B6 : trong hàng 6 từ trái qua phải không có ô dữ liệu nào chứa a thì KQ1 và KQ2 ở ô C7 và ô C8 tự động báo là #
    - Tương tự như vậy mình làm một vài ví dụ minh hoạ như file gửi kèm.
    Mong các bạn giúp phương án giải quyết. Trân trọng cảm ơn!
     

    Các file đính kèm:

  2. HieuCD

    HieuCD Thành viên gạo cội

    Mã:
    C3 =IFERROR(OFFSET($F3,,INT(MATCH($B2,$G2:$AX2,0)/3)*3+1),"#")
    D3 =IFERROR(OFFSET($F3,,INT(MATCH($B2,$G2:$AX2,0)/3)*3+2),"#")
    copy xuống
     
    sep_hatxel thích bài này.
  3. sep_hatxel

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

    Cảm ơn bạn rất nhiều! Mình xin lỗi vì bỏ xót một trường hợp ưu tiên:

    Mình thật sự xin lỗi khi bỏ xót mộttrường hợp ưu tiên:

    - Trong trường hợp khi nhập mã dữ liệu:ví dụ nhập M302 vào ô B9 thì hai dữ liệu M34 và P369 ở ô J10 và ô K10 tự động xuất sang ô C10 và ôD10. Và nếu Mã nhập tiếp theo vào ô B10 là nhập 1 trong 2 kết quả vừa tự độngxuất sang ở ô C10 và D10 (chẳng hạn như nhập M34) thì khi đó ưu tiên hai dữ liệu ở hai ô bên dướiô J10 là ô J11 và ô K11 tự động xuất sang ô C11 và ô D11.
    - Ví dụ như file gửi kèm ạ!
     

    Các file đính kèm:

  4. HieuCD

    HieuCD Thành viên gạo cội

    Mã:
    c3 =if($b2="","",iferror(offset($f3,,if(or($b2=$c2,$b2=$d2),int(match($b1,$g1:$ax1,0)/3),int(match($b2,$g2:$ax2,0)/3))*3+1),"#"))
    d3 =if($b2="","",iferror(offset($f3,,if(or($b2=$c2,$b2=$d2),int(match($b1,$g1:$ax1,0)/3),int(match($b2,$g2:$ax2,0)/3))*3+2),"#"))
     
    sep_hatxel thích bài này.
  5. quocgiacan

    quocgiacan Thành viên GPE

    Góp vui thêm cách khác:
    PHP:
    C3=IF(COUNTIF($G2:$AX2,$B2),OFFSET($G$1,ROW($B2),FLOOR(MATCH($B2,$G2:$AX2,0),3)+(C$1="KQ2")),"#")
    Copy qua phải rồi fill cà 2 xuống

    Chúc bạn ngày vui.
     
    sep_hatxel thích bài này.
  6. Ba Tê

    Ba Tê Cạo Rồi Vẫn Gội

    Dùng VBA nhé, bạn nhập mã vào cột B thử xem (Mẫu còn ở cột A)
     

    Các file đính kèm:

    sep_hatxel thích bài này.
  7. HieuCD

    HieuCD Thành viên gạo cội

    anh em mình hợp lực hoàn thiện công thức
    Mã:
    C3 =IFERROR(OFFSET($G3,,FLOOR(IF(OR($B2=$C2,$B2=$D2),MATCH($B1,$G1:$AX1,0),MATCH($B2,$G2:$AX2,0)),3)+(C$1="KQ2")),"#")
    copy ngang và xuống
    chúc bạn một ngày vui}}}}}}}}}}}}}}}
     
    sep_hatxel thích bài này.
  8. quocgiacan

    quocgiacan Thành viên GPE

    Đúng vậy bạn hiền! }}}}}}}}}}}}}}}

    Chúc bạn một ngày vui.
     
    sep_hatxel thích bài này.
  9. sep_hatxel

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

    Cảm ơn Thầy Ba Tê, bạn HieuCD và bạn quocgiacan rất nhiều! Chúc các bạn và GPE luôn thành công!
     
  10. sep_hatxel

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

    Mình được thầy Ba Tê có gửi cho code cho trường hợp nhập 2 dữ liệu vào 2 ô liên tiếp như sau:
    https://drive.google.com/file/d/0B29...ew?usp=sharing
    Mã:
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Arr(), Rng As Range, Txt1 As String, Txt2 As String, DK As Boolean, J As Long
    If Target.Column = 2 Then
        If Target.Count = 1 Then
            Arr = Target.Offset(-1, 5).Resize(3, 100).Value
            Txt1 = Target.Offset(-1).Value: Txt2 = Target.Value
            For J = 1 To 99 Step 3
                If Arr(1, J) = Txt1 Or Arr(1, J + 1) = Txt1 Then
                    If Arr(2, J) = Txt2 Or Arr(2, J + 1) = Txt2 Then
                        Target.Offset(1, 1).Value = Arr(3, J): Target.Offset(1, 2).Value = Arr(3, J + 1)
                        DK = True: Exit For
                    End If
                End If
            Next J
            If DK = False Then Target.Offset(1, 1).Value = "#": Target.Offset(1, 2).Value = "#"
        End If
    End If
    End Sub
    
    Mình có dựa vào đó để phát triển lên cho trường hợp nhập cho 3 hoặc 4 ô liên tiếp nhưng không biết điều chỉnh ở phần nào? Rất mong Thầy và các bạn hướng dẫn. Xin cảm ơn nhiều!
     
  11. HieuCD

    HieuCD Thành viên gạo cội

    bạn dùng hàm tự tạo, xem cách dùng hàm trong file
    Mã:
    Function GPE(ByVal Rng1 As Range, ByVal Rng2 As Range, ByVal Col As Byte) As Variant
    Dim i As Byte, j As Long, Srow As Byte
    If Not Rng1 Is Nothing And Not Rng1 Is Nothing Then
      Srow = Rng1.Rows.Count
      If Srow < Rng2.Rows.Count Then
        For j = 1 To Rng2.Columns.Count Step 3
          k = 0
          For i = 1 To Srow
            If Rng1(i, 1) = Rng2(i, j) Or Rng1(i, 1) = Rng2(i, j + 1) Then k = k + 1
          Next i
          If k = Srow Then GPE = Rng2(Srow + 1, j + Col):       Exit Function
        Next j
         GPE = "#"
      Else
        GPE = "Sai so dong"
      End If
    End If
    End Function
     
    sep_hatxel thích bài này.
  12. sep_hatxel

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

    Bạn ơi! Cảm ơn những sự giúp đỡ của bạn trong bài này. Thật sự vấn đề bạn đưa ra cách giải quyết mình thấy rất độc đáo và rất hay không biết nói gì hơn. Xin cảm ơn bạn rất nhiều!
    * Trong một thời gian mình làm có nảy sinh thêm tình huống mong bạn có thể xem giúp dựa vào cách làm trên của bạn có được không?
    - Dữ liệu vẫn như cũ nhưng ở sheet3.
    - Sheet4 là sheet nhập Mã dữ liệu vào cột B để có kết quả ở cột C và D như trước nhưng với tình huống như sau:
    + Khi nhập mã vào cột B: thì không phải chỉ lấy nguyên 1 kết quả thoả mãn từ trái sang phải như trước nữa mà lấy tất cả các kết quả thoả mãn ở trong tất cả các cột dữ liệu hiện lên ở các cột dữ liệu tương ứng => đếm xem dữ liệu nào xuất hiện nhiều nhất dán vào cột kết quả C và D (trong trường hợp mà số lần xuất hiện nhiều nhất mà của các dữ liệu kết quả như nhau thì ưu tiên lấy từ trái sang phải)
    Ví dụ trong trường hợp 1 dòng:
    - Khi nhập mã M vào cột B thì có 4 kết quả thoả mãn ( các kết quả này hiện ở vị trí tương ứng như ở sheet3 ): đếm số lần xuất hiện của từng dữ liệu trong các kết quả đó thì có dữ liệu M113 xuất hiện 2 lần, các dữ liệu còn lại đều xuất hiện 1 lần => Khi đó lấy kết quả dữ liệu M113 dán vào cột C, còn kết quả còn lại ưu tiên lấy từ trái sang phải là dữ liệu P255 dán vào cột D.
    ( tương tự như vậy cho trường hợp xét 2 dòng, 3 dòng,...)

    - Rất mong sự giúp đỡ của bạn. Trân trọng cảm ơn bạn! Chúc bạn những ngày nghỉ lễ nhiều niềm vui!
    - Còn đây là file minh hoạ ạ: https://drive.google.com/file/d/0Bx2...ew?usp=sharing
     

    Các file đính kèm:

Chia sẻ trang này