Hỏi: Tự động lọc và sao chép dữ liệu từ các SHEET trong 1 file Excel (1 người xem)

[nms_qa]

Dear All,

Em đang gặp vấn đề với công việc hiện tại, tổng hợp điểm chất lượng của nhân viên và làm báo cáo theo 1 form có sẵn.

Vấn đề là việc copy và paste rất take time, em nghĩ vấn đề này các chuyên gia Excel có thể giúp được nên mạo muội viết lên đây mong được chỉ giáo ạ

Em có 1 file Excel, có 2 SHEET, Công việc của em là lấy 5 điểm theo cột từ SHEET Nhập Liệu, điền vào dòng Điểm tương ứng với tên nhân viên trong SHEET Lọc & sao chép

Em đính kèm file mong các Anh/Chị/Em hướng dẫn.

Thanks & Best Regards

 

[nhapmon]

làm bằng công thức trước, chép công thức này vào cell
C3=INDEX('Nhập liệu'!$C$2:$C$13;SMALL(IF('Nhập liệu'!$B$2:$B$13=$B3;ROW('Nhập liệu'!$C$2:$C$13)-1);COLUMN()-2)).
nhấn Ctrl Shift Enter (3 phím cùng lúc nha)
nếu có lỗi thì đổi ; bằng ,

nghi làm ko được quá, gởi file kèm lun

 

[nms_qa]

Cảm ơn Nhập Môn nhé, mình đã làm được rồi,

 

[Quang_Hải]

Dear All,

Em đang gặp vấn đề với công việc hiện tại, tổng hợp điểm chất lượng của nhân viên và làm báo cáo theo 1 form có sẵn.

Vấn đề là việc copy và paste rất take time, em nghĩ vấn đề này các chuyên gia Excel có thể giúp được nên mạo muội viết lên đây mong được chỉ giáo ạ

Em có 1 file Excel, có 2 SHEET, Công việc của em là lấy 5 điểm theo cột từ SHEET Nhập Liệu, điền vào dòng Điểm tương ứng với tên nhân viên trong SHEET Lọc & sao chép

Em đính kèm file mong các Anh/Chị/Em hướng dẫn.

Thanks & Best Regards

Muốn tự động thì có tự động đây

Sub loc()
Dim data(), i, k, kq(1 To 10000, 1 To 7), n
With Sheet2
   data = .Range(.[B2], .[B65536].End(3)).Resize(, 2).Value
End With
With CreateObject("scripting.dictionary")
   For i = 1 To UBound(data)
      If Not .exists(data(i, 1)) Then
         k = k + 1: n = 0
         .Add data(i, 1), ""
         kq(k, 1) = k
         kq(k, 2) = data(i, 1)
         kq(k, 3) = data(i, 2)
      Else
         n = n + 1
         kq(k, 3 + n) = data(i, 2)
      End If
   Next
End With
Sheet1.[A3].Resize(k, 7) = kq
End Sub

 

[nms_qa]

Cảm ơn các bạn đã Support nhiệt tình, mình tưởng đơn giản nhưng không hề

Mình gà Excel lắm, Pls có thể chỉ giúp mình:

Với hướng dẫn của nhapmon: Mình thử sao chép công thức đó qua 1 file khác & thay các biến tương ứng nhưng nó bị lỗi, Có thể mình chưa hiểu bản chất cấu trúc hàm đó, bạn có thể giải thích giúp mình với những biến được Highlight màu nhé:

=INDEX('Nhập liệu'!$C$2:$C$13;SMALL(IF('Nhập liệu'!$B$2:$B$13=$B3;ROW('Nhập liệu'!$C$2:$C$13)-1);COLUMN()-2))

Cảm ơn bạn.

Còn với hướng dẫn của quanghai1969, sử dụng Macro thì mình chưa thử, nhưng khi mình tải về máy nó k chạy, có lẽ do máy tính mình thiếu thư viện hàm nào đó chăng.

File gốc của mình đính kèm đây ạ, mình muốn lấy Điểm ở SHEET Score tổng hợp vào SHEET CSR Evaluations tương ứng với tên nhân viên.


Pls hỗ trợ mình nhé.

Thanks & Best Regards,

 

[nhapmon]

dữ liệu nhiều nên làm cách của anh QuangHai, nói thiệt mình đọc hồi sáng tới giờ vẫn chưa tiêu được, định hỏi lại ảnh nè
làm tạm bằng công thức trước
L10=IFERROR(INDEX(Score!$AA$8:$AA$872;SMALL(IF(Score!$B$8:$B$872=$B10;ROW(Score!$B$8:$B$872)-7);COLUMN()-11));"")
mình dùng cái này bẩy lổi, nếu bạn xài excel 2003 thì bỏ nó đi
L10=INDEX(Score!$AA$8:$AA$872;SMALL(IF(Score!$B$8:$B$872=$B10;ROW(Score!$B$8:$B$872)-7);COLUMN()-11))

 

[nms_qa]

Thanks nhapmon nhiều,


Pls giúp mình giải thích ý nghĩa của 2 giá trị (-7) & (-11) trong công thức nhé.


Thanks & Best Regards,

 

[Quang_Hải]

dữ liệu nhiều nên làm cách của anh QuangHai, nói thiệt mình đọc hồi sáng tới giờ vẫn chưa tiêu được, định hỏi lại ảnh nè
làm tạm bằng công thức trước
L10=IFERROR(INDEX(Score!$AA$8:$AA$872;SMALL(IF(Score!$B$8:$B$872=$B10;ROW(Score!$B$8:$B$872)-7);COLUMN()-11));"")
mình dùng cái này bẩy lổi, nếu bạn xài excel 2003 thì bỏ nó đi
L10=INDEX(Score!$AA$8:$AA$872;SMALL(IF(Score!$B$8:$B$872=$B10;ROW(Score!$B$8:$B$872)-7);COLUMN()-11))

Dạng dữ liệu dùng tên để xử lý thì công thức hay code gì cũng chết chắc vì ai biết được Nguyễn Ngọc Anh là (Lananh) hay là (Tramanh)

 

[nms_qa]

Vâng, em chỉ muốn mọi người giải thích rõ giúp em ý nghĩa và các biến của hàm, em sẽ tuỳ chỉnh cho phù hợp với mục đích của mình sau khi hiểu rõ bản chất của hàm ạ

Pls giải thích giúp em nhé

Em sẽ chạy hàm dựa trên ID của nhân viên.

 

[nhapmon]

Thanks nhapmon nhiều,
Pls giúp mình giải thích ý nghĩa của 2 giá trị (-7) & (-11) trong công thức nhé.Thanks & Best Regards,

số 7 được tính như sau: ở sheet score, bảng số liệu của bạn bắt đầu ở dòng số 8. vậy đem trừ 7 đi để cho nó về 1.(cứ ở số mấy thì bạn cứ trừ đi số nhỏ hơn nó một số để cho nó bắt bằng số 1)
số 11 được tính, do số liệu bạn bắt đầu cột 12, đen trừ đi 11 để cho nó về sồ một. hàm column()-11 =12-11=1. nó là hệ số k của hàm small, khi bạn kéo sang phải số này sẻ tăng dần 1,2,3....

 

[nms_qa]

Thanks All,

Mình đã làm được rồi.

Cảm ơn mọi người đã nhiệt tình giúp đỡ.

Công việc của mình nhẹ nhàng hơn rùi, hihi

 

[xuanhuye]

Muốn tự động thì có tự động đây

Sub loc()
Dim data(), i, k, kq(1 To 10000, 1 To 7), n
With Sheet2
   data = .Range(.[B2], .[B65536].End(3)).Resize(, 2).Value
End With
With CreateObject("scripting.dictionary")
   For i = 1 To UBound(data)
      If Not .exists(data(i, 1)) Then
         k = k + 1: n = 0
         .Add data(i, 1), ""
         kq(k, 1) = k
         kq(k, 2) = data(i, 1)
         kq(k, 3) = data(i, 2)
      Else
         n = n + 1
         kq(k, 3 + n) = data(i, 2)
      End If
   Next
End With
Sheet1.[A3].Resize(k, 7) = kq
End Sub

Cảm ơn Tiền Bối quanghai1969 em dang cần sử dụng File chạy giống như Code anh viết trên, nhưng tiếc quá, em lại muốn nó nhảy kết quả theo ròng mà không biết chỉnh thế nào, Tiền Bối bớt chút thời gian chỉ giáo em với, Em gửi File minh hoạ nhé >
Cảm ơn anh nhiều !
View attachment Copy of Ex.xls

 

[Quang_Hải]

Cảm ơn Tiền Bối quanghai1969 em dang cần sử dụng File chạy giống như Code anh viết trên, nhưng tiếc quá, em lại muốn nó nhảy kết quả theo ròng mà không biết chỉnh thế nào, Tiền Bối bớt chút thời gian chỉ giáo em với, Em gửi File minh hoạ nhé >
Cảm ơn anh nhiều !
View attachment 101861

Xem file có hiểu muôn gì đâu mà giúp chứ

 

[Hong.Van]

Xem file có hiểu muôn gì đâu mà giúp chứ

Hình như bạn ý muốn chuyển số liệu từ hàng dọc sang hàng hàng ngang ấy!
--------------
Nhưng từ hàng thứ 10 trở xuống thì kg fải???

 

[xuanhuye]

Sorry Huynh , em gủi File khác em cho ra kết quả mô phỏng nhé, cảm ơn anh nhiều! View attachment BaoCao.xls

 

[Quang_Hải]

Sorry Huynh , em gủi File khác em cho ra kết quả mô phỏng nhé, cảm ơn anh nhiều! View attachment 101899

Thử thế này xem sao nhá

Sub loc()
Dim nguon(), kq(), i, j, k, n
Sheet2.[B7].Resize(10000, 4).Clear
With Sheet1
   .Range(.[A3], .[A65536].End(3).Offset(-1)).Resize(, 4).Copy
End With
With Sheet2
   .[B7].PasteSpecial 1
   .Range(.[B7], .[B65536].End(3).Offset(-1)).Resize(, 4).Sort Sheet2.[B6]
   nguon = .Range(.[B7], .[B65536].End(3).Offset(-1)).Resize(, 4)
   ReDim kq(1 To UBound(nguon) * 2, 1 To 4)
   With CreateObject("scripting.dictionary")
      For i = 1 To UBound(nguon)
         If Not .exists(nguon(i, 1)) Then
            k = k + 3
            .Add nguon(i, 1), i
            kq(k - 1, 1) = nguon(i, 1)
            j = k - 1
         Else
            k = k + 1
         End If
         kq(j, 4) = kq(j, 4) + nguon(i, 4)
         For n = 2 To 4
            kq(k, n) = nguon(i, n)
         Next
      Next
   End With
   .[B7].Resize(k, 4) = kq
   .[B7].Resize(k, 4).Borders.Value = 1
End With
End Sub

 

[xuanhuye]

Hiii, tiền Bối QuangHai1969 cao thủ quá, em cảm ơn nhiều lắm, nhưng tiền bối thương cho chót giúp em sao khi em Total kết quả, bôi in đậm một số biểu tượng, rồi ghi thêm mục người lập để ký, rồi nhảy sang Sheet kia, lát quay lại thì biến mất hết rồi .hiii


Em mạo muội nhờ thêm anh tạo giúp em một thư THÔNG BÁO TRẢ NỢ tới các công ty đó theo một tuỳ chọn ( cho từng công ty theo những hoá đơn đã quá hạn 15 ngày ) giống như combobox ấy được không anh.( bằng module hoặc hàm)

Anh lập công thức giúp em, còn em tự trang trí lời văn sau, em cảm ơn,
Em gửi anh File mẫu nhé!
Chúc anh và gia đình cuối tuần vui vẻ và hạnh phúc .

 

[xuanhuye]

Bà con cho hỏi đây là lỗi gì vậy nhỉ
Xem File kem nhe, cam ơn!

 

[Thaiduc]

Xin giúp. Nếu muốn copy từ nhiều Sheets vào một Sheets với điều kiện sắp xếp theo ngày từ nhỏ (tham gia trước) đến lớn (tham gia sau) và bỏ cột C (năm sinh) thì làm như thế nào?
Mong Anh giúp. Cám ơn! (có File Ví dụ kèm theo)

 

[Thaiduc]

Gửi đã lâu mong GEP giúp nhưng chưa thấy. Rất mong các anh, chị xem hỗ trợ với. Xin cảm tạ.

 

[Ngoai Thanh]

Gửi đã lâu mong GEP giúp nhưng chưa thấy. Rất mong các anh, chị xem hỗ trợ với. Xin cảm tạ.

Bạn xem file mình giúp đã được chưa ?

 

[dhn46]

Xin giúp. Nếu muốn copy từ nhiều Sheets vào một Sheets với điều kiện sắp xếp theo ngày từ nhỏ (tham gia trước) đến lớn (tham gia sau) và bỏ cột C (năm sinh) thì làm như thế nào?
Mong Anh giúp. Cám ơn! (có File Ví dụ kèm theo)

Mình làm cho bạn bằng ADO, với dữ liệu của bạn là 3 sheet.

Option Explicit
Sub Copy()
    On Error GoTo Handle
    Dim cnn As Object, lsSQL As String, lrs As Object, FileFullName As String
    Set cnn = CreateObject("ADODB.Connection")
    Set lrs = CreateObject("ADODB.Recordset")
    FileFullName = Application.ThisWorkbook.FullName
    With cnn
        If Val(Application.Version) < 12 Then
            .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FileFullName & ";Extended Properties=""Excel 8.0;HDR=No"";"
        Else
            .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & FileFullName & ";Extended Properties=""Excel 12.0;HDR=No"";"
        End If
        .Open
    End With
    lsSQL = "Select f2,f4,f5 from [CO QUAN 1$A2:E65536] " _
            & "Union all " _
            & "Select f2,f4,f5 from [CO QUAN 2$A2:E65536] " _
            & "Union all " _
            & "Select f2,f4,f5 from [CO QUAN 3$A2:E65536] " _
            & "Order by f5 Asc"
    lrs.Open lsSQL, cnn, 3, 1
    Sheet1.[A2:E6000].ClearContents
    Sheet1.Range("B2").CopyFromRecordset lrs
    Set lrs = Nothing
    cnn.Close: Set cnn = Nothing
    Exit Sub
Handle:
    MsgBox Err.Description
End Sub