Lọc tên nước trong 1 ô dữ liệu có nhiều thông tin và cách tính dữ liệu cho từng nước sau khi đã tách

[Nanunana]

Em chào các anh chị trong diễn đàn ạ,

Em có một nhiệm vụ được giao, em mong các anh chị chỉ giáo giúp em ạ.

Em có 1 bảng dữ liệu khoảng 250 000 bản ghi. Trong đó chỉ có 2 cột là địa chỉ và số TC được tính cho nước trong ô địa chỉ đó.
Ô địa chỉ gồm nhiều nước, mỗi nước thì lại gồm các thành phần nhỏ hơn như tên trường, khoa. Em không cần dữ liệu tên trường hay khoa, mà chỉ cần nước.
Ý tưởng như sau ạ:
Liệu có cách nào tính TC cho từng nước trong cột địa chỉ, đối với những nước lặp nhau trong cùng 1 ô địa chỉ thì chỉ được tính một lần.
Em có nghĩ đến tách các địa chỉ ra: dùng text to column nhưng mà dữ liệu không đồng nhất, lọc 250 nghìn thì quá lớn ạ.
Em mong các anh/chị/bạn trên diễn đàn giúp em với ạ!

Em xin phép up lên đây 5 bản ghi để các anh/chị/bạn biết cấu trúc dữ liệu của em.

Em mong lắm ạ. Em xin chân thành cảm ơn ạ

 

[Ba Tê]

Ô địa chỉ gồm nhiều nước, mỗi nước thì lại gồm các thành phần nhỏ hơn như tên trường, khoa. Em không cần dữ liệu tên trường hay khoa, mà chỉ cần nước.

Cái gì để biết nó là tên nước, tên trường, tên khoa...?
Dò trên google?
Ít nhất bạn phải có 1 danh sách tên các nước, từ đó mà dò xem trong ô đó có tên các nước nào.

 

[huonglien1901]

Em chào các anh chị trong diễn đàn ạ,

Em có một nhiệm vụ được giao, em mong các anh chị chỉ giáo giúp em ạ.

Em có 1 bảng dữ liệu khoảng 250 000 bản ghi. Trong đó chỉ có 2 cột là địa chỉ và số TC được tính cho nước trong ô địa chỉ đó.
Ô địa chỉ gồm nhiều nước, mỗi nước thì lại gồm các thành phần nhỏ hơn như tên trường, khoa. Em không cần dữ liệu tên trường hay khoa, mà chỉ cần nước.
Ý tưởng như sau ạ:
Liệu có cách nào tính TC cho từng nước trong cột địa chỉ, đối với những nước lặp nhau trong cùng 1 ô địa chỉ thì chỉ được tính một lần.
Em có nghĩ đến tách các địa chỉ ra: dùng text to column nhưng mà dữ liệu không đồng nhất, lọc 250 nghìn thì quá lớn ạ.
Em mong các anh/chị/bạn trên diễn đàn giúp em với ạ!

Em xin phép up lên đây 5 bản ghi để các anh/chị/bạn biết cấu trúc dữ liệu của em.

Em mong lắm ạ. Em xin chân thành cảm ơn ạ

Bạn có thể giải thcihs rõ được không? mình chưa hiểu lắm.

 

[Phan Thế Hiệp]

Em chào các anh chị trong diễn đàn ạ,

Em có một nhiệm vụ được giao, em mong các anh chị chỉ giáo giúp em ạ.

Em có 1 bảng dữ liệu khoảng 250 000 bản ghi. Trong đó chỉ có 2 cột là địa chỉ và số TC được tính cho nước trong ô địa chỉ đó.
Ô địa chỉ gồm nhiều nước, mỗi nước thì lại gồm các thành phần nhỏ hơn như tên trường, khoa. Em không cần dữ liệu tên trường hay khoa, mà chỉ cần nước.
Ý tưởng như sau ạ:
Liệu có cách nào tính TC cho từng nước trong cột địa chỉ, đối với những nước lặp nhau trong cùng 1 ô địa chỉ thì chỉ được tính một lần.
Em có nghĩ đến tách các địa chỉ ra: dùng text to column nhưng mà dữ liệu không đồng nhất, lọc 250 nghìn thì quá lớn ạ.
Em mong các anh/chị/bạn trên diễn đàn giúp em với ạ!

Em xin phép up lên đây 5 bản ghi để các anh/chị/bạn biết cấu trúc dữ liệu của em.

Em mong lắm ạ. Em xin chân thành cảm ơn ạ

D2=SUMPRODUCT(N(COUNTIF(Sheet1!B2,"*"&Dsach!$B$2:$B$242&"*")>0))

Xem thử file kèm. Điều chỉnh lại Danh sách các quốc gia cho phù hợp với dữ liệu của bạn, Vd: United Kingdom = England; United state of America = USA....
Thân.

 

[Nanunana]

Đây là 1 bản ghi em down từ trang web thống kê của nước ngoài
File đó tổng hợp bài báo được đăng trên tạp chí quốc tế của các nước theo 1 chủ đề nhất định.

Cột mà em cần phân tích chính là địa chỉ của các tác giả và TC. Theo thông lệ quốc tế, khi bài báo đăng thì cần tên tác giả và địa chỉ của họ: khoa, trường, địa chỉ nước. Em cần thống kê theo nước chứ không cần khoa hay ngành.

Nếu các anh chỉ mở file đính kèm của em thì sẽ thấy đó là tên tác giả nằm trong cùng một ngoặc vuông, tên trường, khoa, địa chỉ của trường và nước.

Ý em là em hỏi có hàm hay cách nào tách nước riêng ra khỏi ô đó để tính TC cho từng nước ạ
Em cảm ơn ạ

Bài đã được tự động gộp: 16/7/18

D2=SUMPRODUCT(N(COUNTIF(Sheet1!B2,"*"&Dsach!$B$2:$B$242&"*")>0))

Xem thử file kèm. Điều chỉnh lại Danh sách các quốc gia cho phù hợp với dữ liệu của bạn, Vd: United Kingdom = England; United state of America = USA....
Thân.

Em cảm ơn anh Hiệp ạ

 

[phulien1902]

D2=SUMPRODUCT(N(COUNTIF(Sheet1!B2,"*"&Dsach!$B$2:$B$242&"*")>0))

Xem thử file kèm. Điều chỉnh lại Danh sách các quốc gia cho phù hợp với dữ liệu của bạn, Vd: United Kingdom = England; United state of America = USA....
Thân.

Đội bóng em yêu thích CROATIA lại ký hiệu là HR nhỉ?
Cho em Spam 1 chút nhé.

 

[Phan Thế Hiệp]

Đội bóng em yêu thích CROATIA lại ký hiệu là HR nhỉ?
Cho em Spam 1 chút nhé.

Vậy năm nay vui rồi, Vì là "Á quân" như U23 Việt Nam vậy.
Khà khà khà!
Chúc anh em ngày vui.
Thân

 

[Nanunana]

Dạ. Em đã xem file anh Hiệp gửi ạ.
Anh Hiệp cho em file đếm số nước trong cùng 1 ô. Lần trước em không biết dùng lệnh đó, em làm thủ công lắm. Chả dám kể ra đây

Cho em hỏi là có các nào, dùng trên excel mà hiện thị nước trong ô đó cho mình ra ô khác không ạ? Ví dụ, nếu ô 1 là 40 nước thì đẩy ra thành tên 40 nước, vì em sẽ phải tính TC cho mỗi 40 nước. Sau đó, sẽ tính xem Mĩ có bn TC, Pháp có bn TC ý ạ.

Em cảm ơn nhiều ạ.

 

[Hau151978]

Đội bóng em yêu thích CROATIA lại ký hiệu là HR nhỉ?
Cho em Spam 1 chút nhé.

HR chắc là Hrvatska theo tiếng nước họ. Mình spam ké.

 

[Phan Thế Hiệp]

Dạ. Em đã xem file anh Hiệp gửi ạ.
Anh Hiệp cho em file đếm số nước trong cùng 1 ô. Lần trước em không biết dùng lệnh đó, em làm thủ công lắm. Chả dám kể ra đây

Cho em hỏi là có các nào, dùng trên excel mà hiện thị nước trong ô đó cho mình ra ô khác không ạ? Ví dụ, nếu ô 1 là 40 nước thì đẩy ra thành tên 40 nước, vì em sẽ phải tính TC cho mỗi 40 nước. Sau đó, sẽ tính xem Mĩ có bn TC, Pháp có bn TC ý ạ.

Em cảm ơn nhiều ạ.

Xem file kèm. (Hơi nặng)

Nếu bạn muốn có thêm công thức để cộng cho từng nước, thì nên dùng file "Giải pháp excel 2.xlsx" nó sẽ nhẹ hơn. Nhưng phải thêm công thức cộng vào nhé!

Thân.

 

[Ba Tê]

Xem file kèm.
Thân.

Công thức "đắm đuối" thiệt.
F2, Enter, quay quay một hồi.

 

[Phan Thế Hiệp]

Công thức "đắm đuối" thiệt.
F2, Enter, quay quay một hồi.

Chờ anh Ba ra tay cho "em út" nhờ đó!
Chúc anh ngày vui.
Thân.

 

[Ba Tê]

Chờ anh Ba ra tay cho "em út" nhờ đó!
Chúc anh ngày vui.
Thân.

241 tên quốc gia (theo file của @Phan Thế Hiệp)
Nếu dữ liệu nhập khác thì với 250.000 dòng mà kiểm tra lại bằng mắt cũng "đắm đuối" luôn.
Hên xui!

Public Sub GPE()
Dim dArr(), sArr(), tArr(), Txt As String, Tem As String
Dim I As Long, J As Long, Col As Long, MaxCol As Long
    tArr = Sheets("Dsach").Range("B2:B242").Value
    sArr = Sheet1.Range("B2", Sheet1.Range("B2").End(xlDown)).Value
ReDim dArr(1 To UBound(sArr), 1 To 100)
For I = 1 To UBound(sArr)
    Col = 0
    Txt = UCase(sArr(I, 1))
    For J = 1 To UBound(tArr)
        Tem = UCase(tArr(J, 1))
        If Txt Like "*" & Tem & "*" Then
            Col = Col + 1
            dArr(I, Col) = Tem
            If MaxCol < Col Then MaxCol = Col
        End If
    Next J
Next I
If MaxCol Then Sheet1.Range("D2").Resize(I - 1, MaxCol) = dArr
End Sub

 

[Nanunana]

241 tên quốc gia (theo file của @Phan Thế Hiệp)
Nếu dữ liệu nhập khác thì với 250.000 dòng mà kiểm tra lại bằng mắt cũng "đắm đuối" luôn.
Hên xui!

Public Sub GPE()
Dim dArr(), sArr(), tArr(), Txt As String, Tem As String
Dim I As Long, J As Long, Col As Long, MaxCol As Long
    tArr = Sheets("Dsach").Range("B2:B242").Value
    sArr = Sheet1.Range("B2", Sheet1.Range("B2").End(xlDown)).Value
ReDim dArr(1 To UBound(sArr), 1 To 100)
For I = 1 To UBound(sArr)
    Col = 0
    Txt = UCase(sArr(I, 1))
    For J = 1 To UBound(tArr)
        Tem = UCase(tArr(J, 1))
        If Txt Like "*" & Tem & "*" Then
            Col = Col + 1
            dArr(I, Col) = Tem
            If MaxCol < Col Then MaxCol = Col
        End If
    Next J
Next I
If MaxCol Then Sheet1.Range("D2").Resize(I - 1, MaxCol) = dArr
End Sub

Em cảm ơn ạ. Không biết nói gì hơn ạ

Bài đã được tự động gộp: 16/7/18

Chờ anh Ba ra tay cho "em út" nhờ đó!
Chúc anh ngày vui.
Thân.

Em cảm ơn rất nhiều ạ