Cần giúp đỡ lọc thẻ trùng BHYT (5 người xem)

[matrix_man999]

Hiện nay mình đang có danh sách BHYT do ngân sách nhà nước cấp, tuy nhiên nhiều đối tượng bị trùng nhau. Mình đã tham khảo trên "giaiphapexcel" để sàn loc từ 300.000 đối tượng xuống khoản 20.000 đối tượng nhưng phần sau phải làm bằng tay nhưng deadline sắp tới mà mình có 18 huyện như vậy.
Ở đây có các trường hợp như sau:
- Nguyễn Văn A có 2 thẻ 1 thẻ từ 1-1-2015 đến 30-6-2015, 1 thẻ từ 1-10-2015 đến 30-6-2016 thì không trùng.
- Nguyễn Văn B có 2 thẻ 1 thẻ từ 1-1-2015 đến 30-6-2015, 1 thẻ từ 1-4-2015 đến 30-6-2016 thì trùng nhau 3 tháng.
- Nguyễn Văn C có 3 thẻ 1 thẻ từ 1-1-2015 đến 30-6-2015, 1 thẻ từ 1-7-2015 đến 30-6-2016 và 1 thẻ từ 1-1-2015 đến 30-12-2015 thì trùng nhau 12 tháng.
Ai có cách nào giải quyết nhanh vấn đề này giúp mình với ạ, vì đối tượng quá nhiều mà deadline gần tới rồi.
Mình có đính kèm file (đoạn đầu mình làm tay dc 1500 đối tượng rồi) mọi người nghiên cứu dùm với ạ.

 

[lehuong1409]

Hiện nay mình đang có danh sách BHYT do ngân sách nhà nước cấp, tuy nhiên nhiều đối tượng bị trùng nhau. Mình đã tham khảo trên "giaiphapexcel" để sàn loc từ 300.000 đối tượng xuống khoản 20.000 đối tượng nhưng phần sau phải làm bằng tay nhưng deadline sắp tới mà mình có 18 huyện như vậy.
Ở đây có các trường hợp như sau:
- Nguyễn Văn A có 2 thẻ 1 thẻ từ 1-1-2015 đến 30-6-2015, 1 thẻ từ 1-10-2015 đến 30-6-2016 thì không trùng.
- Nguyễn Văn B có 2 thẻ 1 thẻ từ 1-1-2015 đến 30-6-2015, 1 thẻ từ 1-4-2015 đến 30-6-2016 thì trùng nhau 3 tháng.
- Nguyễn Văn C có 3 thẻ 1 thẻ từ 1-1-2015 đến 30-6-2015, 1 thẻ từ 1-7-2015 đến 30-6-2016 và 1 thẻ từ 1-1-2015 đến 30-12-2015 thì trùng nhau 12 tháng.
Ai có cách nào giải quyết nhanh vấn đề này giúp mình với ạ, vì đối tượng quá nhiều mà deadline gần tới rồi.
Mình có đính kèm file (đoạn đầu mình làm tay dc 1500 đối tượng rồi) mọi người nghiên cứu dùm với ạ.

bạn có thể lấy VD bằng 1 cái tên cụ thể trong danh sách được không, mình đang xem thử những mã trùng nhau thì thấy có người sao đóng bảo hiểm từ 01/7/15 đến 30/6/15 là sao b?

 

[matrix_man999]

Trường hợp người đóng bảo hiểm từ 01/7/15 đến 30/6/15 là do thẻ đó người nhập sai, họ ko được xóa nên họ điều chỉnh lại ngày để nó không ảnh hưởng (sau đó họ cấp lại thẻ đúng với cùng số thẻ với thẻ kia). Khi xuất phần mềm nó vẫn ra và hiển thị như vậy đó bạn. Đối với đoạn bạn chụp thì ko có 1 ai bị trùng thẻ. Mình có dùng công thức tại mục số ngày trùng =MIN(ABS(G9-F10);ABS(F9-G10)) nên nếu chỉ có 2 người trùng tên mà cái ô đầu tiên cho kết quả là 1 thì nó không trùng. (nhưng mình dùng cách này vẫn lâu quá)

 

[matrix_man999]

đây là 1 huyện khác mình cần thực hiện bằng tay để tính ra số tháng trùng và xóa những người không trùng đây ạ. Mọi người xem có cách nào ít phải làm thủ công ko ạ?

 

[matrix_man999]

Các trường hợp cụ thể đây bạn ơi. (vì mình chỉ tính trong năm 2015 thôi nên mặc dù có trùng nhau gần 1 năm nhưng mình chỉ tính 6 tháng thôi nhé bạn)

 

[lehuong1409]

Các trường hợp cụ thể đây bạn ơi. (vì mình chỉ tính trong năm 2015 thôi nên mặc dù có trùng nhau gần 1 năm nhưng mình chỉ tính 6 tháng thôi nhé bạn)

mình vừa có việc nên k để ý bạn trả lời. Tức là bạn cần theo dõi từng người đó đã đc cấp thẻ bảo hiểm như thế nào thôi đúng không, còn số thẻ trùng hay không không quan trọng chứ gì. VD người tên là Nguyễn Khuyến ở dòng thứ 3 là địa chỉ khác mà. hay chỉ quan trọng tên, ngày sinh và thời gian đóng bảo hiểm thôi?

 

[CarotNho]

Có gì đó không ổn anh chủ thớt ới
Trùng họ tên, trùng ngày sinh, trùng địa chỉ nơi cư trú là có xảy ra nên không thể dùng các thông tin đó làm căn cứ xác định được
phải lấy số CMND, số thẻ căn cước công dân làm căn cứ xác định

 

[matrix_man999]

Có gì đó không ổn anh chủ thớt ới
Trùng họ tên, trùng ngày sinh, trùng địa chỉ nơi cư trú là có xảy ra nên không thể dùng các thông tin đó làm căn cứ xác định được
phải lấy số CMND, số thẻ căn cước công dân làm căn cứ xác định

Cái này bên BHXH gửi cho mình chừng đó thông tin đó bạn (phần lớn là trẻ em và học sinh nên chưa có CMND), mình lọc thẻ trùng sau đó gửi lại cho họ. Họ có trách nhiệm chứng minh người đó không trùng, còn nếu không chứng minh được thì trừ ra trả về Ngân sách nhà nước.

 

[matrix_man999]

mình vừa có việc nên k để ý bạn trả lời. Tức là bạn cần theo dõi từng người đó đã đc cấp thẻ bảo hiểm như thế nào thôi đúng không, còn số thẻ trùng hay không không quan trọng chứ gì. VD người tên là Nguyễn Khuyến ở dòng thứ 3 là địa chỉ khác mà. hay chỉ quan trọng tên, ngày sinh và thời gian đóng bảo hiểm thôi?

Đúng rồi đó bạn, vì phần ngày sinh có khi họ chỉ nhập năm nên phần mềm nó sẽ hiển thị 1-1-năm sinh, phần địa chỉ thì họ ko ghi chuẩn lắm. Nên những trường hợp nghi ngờ mình vẫn báo trùng. BHXH họ sẽ kiểm tra 1 lần nữa rồi có trách nhiệm chứng minh nó ko trùng. Kết quả cuối cùng thì những thẻ trùng: sum(lấy số tháng trùng x số tiền đóng/tháng ) = số tiền trả về Ngân sách

 

[quocgiacan]

Hiện nay mình đang có danh sách BHYT do ngân sách nhà nước cấp, tuy nhiên nhiều đối tượng bị trùng nhau. Mình đã tham khảo trên "giaiphapexcel" để sàn loc từ 300.000 đối tượng xuống khoản 20.000 đối tượng nhưng phần sau phải làm bằng tay nhưng deadline sắp tới mà mình có 18 huyện như vậy.
Ở đây có các trường hợp như sau:
- Nguyễn Văn A có 2 thẻ 1 thẻ từ 1-1-2015 đến 30-6-2015, 1 thẻ từ 1-10-2015 đến 30-6-2016 thì không trùng.
- Nguyễn Văn B có 2 thẻ 1 thẻ từ 1-1-2015 đến 30-6-2015, 1 thẻ từ 1-4-2015 đến 30-6-2016 thì trùng nhau 3 tháng.
- Nguyễn Văn C có 3 thẻ 1 thẻ từ 1-1-2015 đến 30-6-2015, 1 thẻ từ 1-7-2015 đến 30-6-2016 và 1 thẻ từ 1-1-2015 đến 30-12-2015 thì trùng nhau 12 tháng.
Ai có cách nào giải quyết nhanh vấn đề này giúp mình với ạ, vì đối tượng quá nhiều mà deadline gần tới rồi.
Mình có đính kèm file (đoạn đầu mình làm tay dc 1500 đối tượng rồi) mọi người nghiên cứu dùm với ạ.

1. Theo ý của bạn trong file thì chỉ so sánh tên, mà không có tiêu chí khác để phân biệt hai người cùng tên là hai người hay là 1 người. Việc này về sau sai hay đúng là do bạn.
2. Công thức dưới đây chỉ căn cứ theo 'tên giống nhau và sau khi bạn đã sort lại trước rồi'.

• Tạo 2 name:

  DenCotF=OFFSET('Doi tuong trung'!F9,,,COUNTIF('Doi tuong trung'!B9:B50,'Doi tuong trung'!B9))
  Và
  DencotG=OFFSET('Doi tuong trung'!G9,,,COUNTIF('Doi tuong trung'!B9:B50,'Doi tuong trung'!B9))

• Công thức tính tháng trùng cho những đối tượng có tên giống nhau:

  H9=IF(B9<>B8,ROUND(SUMPRODUCT(COUNTIFS(F9:DenCotF,"<="&ROW(INDIRECT(MIN(F9:DenCotG)&":"&MAX(F9:DenCotG))),G9:DenCotG,">="&ROW(INDIRECT(MIN(F9:DenCotG)&":"&MAX(F9:DenCotG))))-1)/30,0),"")

  Enter fill xuống.

3. Do dữ liệu của bạn nhiều (>17000 dòng), nên nếu máy bạn có cấu hình yếu dùng công thức này sẽ chờ lâu. Tốt nhất là dùng VBA.
4. Công thức trên chỉ tạm hỗ trợ bạn trong chừng mực, vì Vùng lấy sẽ không chính xác nếu một đối tượng nào đó có hơn 50 dòng, nhưng nếu lấy hơn thì máy bạn sẽ ì ạch.

Chúc bạn ngày vui.

 

[chisinhvnn]

1. Theo ý của bạn trong file thì chỉ so sánh tên, mà không có tiêu chí khác để phân biệt hai người cùng tên là hai người hay là 1 người. Việc này về sau sai hay đúng là do bạn.
2. Công thức dưới đây chỉ căn cứ theo 'tên giống nhau và sau khi bạn đã sort lại trước rồi'.

• Tạo 2 name:

  DenCotF=OFFSET('Doi tuong trung'!F9,,,COUNTIF('Doi tuong trung'!B9:B50,'Doi tuong trung'!B9))
  Và
  DencotG=OFFSET('Doi tuong trung'!G9,,,COUNTIF('Doi tuong trung'!B9:B50,'Doi tuong trung'!B9))

• Công thức tính tháng trùng cho những đối tượng có tên giống nhau:

  H9=IF(B9<>B8,ROUND(SUMPRODUCT(COUNTIFS(F9:DenCotF,"<="&ROW(INDIRECT(MIN(F9:DenCotG)&":"&MAX(F9:DenCotG))),G9:DenCotG,">="&ROW(INDIRECT(MIN(F9:DenCotG)&":"&MAX(F9:DenCotG))))-1)/30,0),"")

  Enter fill xuống.

3. Do dữ liệu của bạn nhiều (>17000 dòng), nên nếu máy bạn có cấu hình yếu dùng công thức này sẽ chờ lâu. Tốt nhất là dùng VBA.
4. Công thức trên chỉ tạm hỗ trợ bạn trong chừng mực, vì Vùng lấy sẽ không chính xác nếu một đối tượng nào đó có hơn 50 dòng, nhưng nếu lấy hơn thì máy bạn sẽ ì ạch.

Chúc bạn ngày vui.

Giúp bạn ấy cái VBA đi.

 

[matrix_man999]

1. Theo ý của bạn trong file thì chỉ so sánh tên, mà không có tiêu chí khác để phân biệt hai người cùng tên là hai người hay là 1 người. Việc này về sau sai hay đúng là do bạn.
2. Công thức dưới đây chỉ căn cứ theo 'tên giống nhau và sau khi bạn đã sort lại trước rồi'.

• Tạo 2 name:

  DenCotF=OFFSET('Doi tuong trung'!F9,,,COUNTIF('Doi tuong trung'!B9:B50,'Doi tuong trung'!B9))
  Và
  DencotG=OFFSET('Doi tuong trung'!G9,,,COUNTIF('Doi tuong trung'!B9:B50,'Doi tuong trung'!B9))

• Công thức tính tháng trùng cho những đối tượng có tên giống nhau:

  H9=IF(B9<>B8,ROUND(SUMPRODUCT(COUNTIFS(F9:DenCotF,"<="&ROW(INDIRECT(MIN(F9:DenCotG)&":"&MAX(F9:DenCotG))),G9:DenCotG,">="&ROW(INDIRECT(MIN(F9:DenCotG)&":"&MAX(F9:DenCotG))))-1)/30,0),"")

  Enter fill xuống.

3. Do dữ liệu của bạn nhiều (>17000 dòng), nên nếu máy bạn có cấu hình yếu dùng công thức này sẽ chờ lâu. Tốt nhất là dùng VBA.
4. Công thức trên chỉ tạm hỗ trợ bạn trong chừng mực, vì Vùng lấy sẽ không chính xác nếu một đối tượng nào đó có hơn 50 dòng, nhưng nếu lấy hơn thì máy bạn sẽ ì ạch.

Chúc bạn ngày vui.

sao nó bị ### bạn ơi =IF(B9<>B8;ROUND(SUMPRODUCT(COUNTIFS(F9enCotF;"<="&ROW(INDIRECT(MIN(F9enCotG)&":"&MAX(F9enCotG)));G9enCotG;">="&ROW(INDIRECT(MIN(F9enCotG)&":"&MAX(F9enCotG))))-1)/30;0);"") (mình chỉ đổi , qua ; thôi)

 

[quocgiacan]

sao nó bị ### bạn ơi =IF(B9<>B8;ROUND(SUMPRODUCT(COUNTIFS(F9enCotF;"<="&ROW(INDIRECT(MIN(F9enCotG)&":"&MAX(F9enCotG)));G9enCotG;">="&ROW(INDIRECT(MIN(F9enCotG)&":"&MAX(F9enCotG))))-1)/30;0);"") (mình chỉ đổi , qua ; thôi)
View attachment 183856

Download file đính kèm ở bài #10, mở ra xem thử có bị lỗi không!?

 

[thuhuonglee]

1. Theo ý của bạn trong file thì chỉ so sánh tên, mà không có tiêu chí khác để phân biệt hai người cùng tên là hai người hay là 1 người. Việc này về sau sai hay đúng là do bạn.
2. Công thức dưới đây chỉ căn cứ theo 'tên giống nhau và sau khi bạn đã sort lại trước rồi'.

• Tạo 2 name:

  DenCotF=OFFSET('Doi tuong trung'!F9,,,COUNTIF('Doi tuong trung'!B9:B50,'Doi tuong trung'!B9))
  Và
  DencotG=OFFSET('Doi tuong trung'!G9,,,COUNTIF('Doi tuong trung'!B9:B50,'Doi tuong trung'!B9))

• .

Sao em không thấy tên "DenCotF" ở trong "namebox" vậy anh?và Em chưa hiểu về câu lệnh này,nhờ Anh diễn giải giúp em với .
Chúc mọi người vui
Cám ơn Anh nhiều.

 

[quocgiacan]

Sao em không thấy tên "DenCotF" ở trong "namebox" vậy anh?và Em chưa hiểu về câu lệnh này,nhờ Anh diễn giải giúp em với .
Chúc mọi người vui
Cám ơn Anh nhiều.

Bạn download file đính kèm tại bài #10 về xem thử. Nhấn Ctrl+F3 là sẽ thấy 'name box'.
Giải thuật bài này là:

• Chọn và lấy vào bộ nhớ một Mảng: tính từ ngày nhỏ nhất (tức lựa trong Từ ngày), đến ngày lớn nhất (tức lựa trong Đến ngày) của cùng đối tượng.
• Đếm theo điều kiện sao cho:
  • Mảng vừa tạo lớn hơn hay bằng Vùng của các 'Từ Ngày', đồng thời (và) nhỏ hơn hay bằng Vùng của các 'Đến ngày' của cùng đối tượng.
  • Chỉ đếm những giá trị nào > 1 (tức 1 thì không trùng, từ 2 trở đi là trùng bao nhiêu lần)
• Cộng các giá trị trùng đếm được chính là tổng các ngày bị trùng. Sau đó chia 30 ra tháng.

Ví dụ: Đối tượng 'Đàm Thị Kim Oanh' có ba dòng:

• Từ 01/09/2015 đến 31/08/2016
• Từ 01/09/2014 đến 31/08/2015
• Từ 01/09/2015 đến 31/08/2016
• Tức cô này đã đóng từ 01/09/2014 đến 31/08/2016, khoảng tháng trùng là từ 01/09/2015 đến 31/08/2016 (dòng đầu) tức bị trùng 12 tháng .
• 

Công thức này chia 30 nên mang tính tương đối, không chính xác lắm, nên tôi bảo là chỉ hỗ trợ là chính.

Chúc bạn ngày vui
p/s: làm luôn và đính kèm cho file bài #5, khủng khiếp >33000 dòng, chạy được cũng may lắm.

 

[matrix_man999]

Thật sự cảm ơn bạn "Quocgiacan" rất nhiều, mình chưa hiểu cái "DenCotF" là như thế nào, nhưng mình biến thể cái công thức của bạn lại như sau:
=IF(B11<>B10,ROUND(SUMPRODUCT(COUNTIFS(F11:F12,'"<="&ROW(INDIRECT(MIN(F11:G12)&":"&MAX(F11:G12))),G11:G12,">="&ROW(INDIRECT(MIN(F11:G12)&":"&MAX(F11:G12))))-1)/30,0);"") thì nó làm được khi có 2 tên trùng. Mình thử tăng lên 3 cột (F11:G13) (12 chuyển hết thành 13) thì nó cho kết quả không đúng. Bạn có thể hỗ trợ mình thêm 1 tý đối với các trường hợp nhiều tên trùng ko ạ.

 

[quocgiacan]

Thật sự cảm ơn bạn "Quocgiacan" rất nhiều, mình chưa hiểu cái "DenCotF" là như thế nào, nhưng mình biến thể cái công thức của bạn lại như sau:
=IF(B11<>B10,ROUND(SUMPRODUCT(COUNTIFS(F11:F12,'"<="&ROW(INDIRECT(MIN(F11:G12)&":"&MAX(F11:G12))),G11:G12,">="&ROW(INDIRECT(MIN(F11:G12)&":"&MAX(F11:G12))))-1)/30,0);"") thì nó làm được khi có 2 tên trùng. Mình thử tăng lên 3 cột (F11:G13) (12 chuyển hết thành 13) thì nó cho kết quả không đúng. Bạn có thể hỗ trợ mình thêm 1 tý đối với các trường hợp nhiều tên trùng ko ạ.

Name: 'DenCotF' và 'DenCotG' là hai name tạo Vùng dữ liệu 'động', nếu đối tượng có hai dòng thì hai 'bé' ấy chỉ lấy đúng hai dòng (tức lấy đúng dòng phát sinh của đối tượng), có 3 thì lấy 3, có bao nhiêu thì lấy bấy nhiêu, nhưng nếu nó có > 50 dòng thì 'bó tay'.

Chúc anh em ngày vui.

 

[thuhuonglee]

Bạn download file đính kèm tại bài #10 về xem thử. Nhấn Ctrl+F3 là sẽ thấy 'name box'.
Giải thuật bài này là:

• Chọn và lấy vào bộ nhớ một Mảng: tính từ ngày nhỏ nhất (tức lựa trong Từ ngày), đến ngày lớn nhất (tức lựa trong Đến ngày) của cùng đối tượng.
• Đếm theo điều kiện sao cho:
  • Mảng vừa tạo lớn hơn hay bằng Vùng của các 'Từ Ngày', đồng thời (và) nhỏ hơn hay bằng Vùng của các 'Đến ngày' của cùng đối tượng.
  • Chỉ đếm những giá trị nào > 1 (tức 1 thì không trùng, từ 2 trở đi là trùng bao nhiêu lần)
• Cộng các giá trị trùng đếm được chính là tổng các ngày bị trùng. Sau đó chia 30 ra tháng.

Ví dụ: Đối tượng 'Đàm Thị Kim Oanh' có ba dòng:

• Từ 01/09/2015 đến 31/08/2016
• Từ 01/09/2014 đến 31/08/2015
• Từ 01/09/2015 đến 31/08/2016
• Tức cô này đã đóng từ 01/09/2014 đến 31/08/2016, khoảng tháng trùng là từ 01/09/2015 đến 31/08/2016 (dòng đầu) tức bị trùng 12 tháng .
• View attachment 183861

Công thức này chia 30 nên mang tính tương đối, không chính xác lắm, nên tôi bảo là chỉ hỗ trợ là chính.

Chúc bạn ngày vui
p/s: làm luôn và đính kèm cho file bài #5, khủng khiếp >33000 dòng, chạy được cũng may lắm.

Dạ nhấn"Ctrl+F3" thì em có thấy ,nhưng công thức biểu diễn dài quá nên không xem hết được.
Ý em ở đây là không thấy ở trong ô "namebox" phía bên trên góc trái đó Anh.
Thông thường em thấy khi ta đặt tên một vùng nào đó,thì tên sẽ được hiển thị trong namebox.Còn "Ctrl+F3" là khi nào có nhu cầu chỉnh sửa,hoặc xóa thì mới cần vào đấy chứ Anh.
Dạ em cũng chưa biết gì Excel lắm,nhờ những người đi trước như anh nhiệt tình giải thích, em thấy như khám phá ra được thêm nhiều điều thú vị.Chúc anh và mọi người vui.

 

[quocgiacan]

Dạ nhấn"Ctrl+F3" thì em có thấy ,nhưng công thức biểu diễn dài quá nên không xem hết được.
Ý em ở đây là không thấy ở trong ô "namebox" phía bên trên góc trái đó Anh.
Thông thường em thấy khi ta đặt tên một vùng nào đó,thì tên sẽ được hiển thị trong namebox.Còn "Ctrl+F3" là khi nào có nhu cầu chỉnh sửa,hoặc xóa thì mới cần vào đấy chứ Anh.
Dạ em cũng chưa biết gì Excel lắm,nhờ những người đi trước như anh nhiệt tình giải thích, em thấy như khám phá ra được thêm nhiều điều thú vị.Chúc anh và mọi người vui.

Cái hộp nho nhỏ góc trái như bạn nói, chỉ thể hiện địa chỉ ô 'hiện hành', hoặc khi nhấn F2 vào công thức nó liệt kê danh sách các hàm, chứ nó không thể hiện danh sách các name mình đặt ra để sử dụng. Chỉ có cách nhấn Ctrl+F3 để tạo - xem - sửa các 'name' trong danh sách, và nhấn F3 khi bạn muốn dán các name ấy vào nơi cần dùng mà thôi.

Chúc bạn ngày vui.

 

[matrix_man999]

bạn có thể hướng dẫn cách dùng VBA luôn không bạn? máy cơ quan nhà nước core I3 nên chạy chậm quá