Nhờ cộng và tách số liệu (1 người xem)

[vnmoney]

Chào mọi người!
Mình có file dạng:

CELL	CS CALL
Ha-Phong2-HLG_QNH1
KS-Huu-Nghi-HLG_QNH4
Ha-Phong2-HLG_QNH4
KS-Huu-Nghi-HLG_QNH5
Ha-Phong-HLG_QNH3
Cai-Lan-HLG_QNH3
Khu3-Yet-Kieu-HLG_QNH3
Ha-Khau-HLG_QNH5
Khu3-Yet-Kieu-HLG_QNH6
Ha-Khanh-A-HLG_QNH2
Lien-Co-IBS-HLG_QNH4
Ha-Khanh-A-HLG_QNH3
KS-Huu-Nghi-HLG_QNH1
KS-Huu-Nghi-HLG_QNH2
KS-Huu-Nghi-HLG_QNH3
Cai-Lan-HLG_QNH2
Ha-Phong2-HLG_QNH3
Ha-Khau-HLG_QNH6
To2-Hong-Ky-MCI_QNH5
Tra-Vi-MCI_QNH1
Khu2-Hai-Yen-MCI_QNH2
LT327-MCI_QNH2
Khu4-Hai-Yen2-MCI_QNH4

[TD="width: 98"]CS TRAFFIC[/TD]
[TD="width: 103"]PERCENT[/TD]

[TD="align: right"]1059[/TD]
[TD="align: right"]16.9595[/TD]
[TD="align: right"]3.4[/TD]

[TD="align: right"]4924[/TD]
[TD="align: right"]60.0335[/TD]
[TD="align: right"]10.1[/TD]

[TD="align: right"]1369[/TD]
[TD="align: right"]20.0215[/TD]
[TD="align: right"]3.7[/TD]

[TD="align: right"]8102[/TD]
[TD="align: right"]105.815[/TD]
[TD="align: right"]11.6[/TD]

[TD="align: right"]1059[/TD]
[TD="align: right"]17.384[/TD]
[TD="align: right"]39.6[/TD]

[TD="align: right"]5383[/TD]
[TD="align: right"]85.248[/TD]
[TD="align: right"]-6.6[/TD]

[TD="align: right"]2385[/TD]
[TD="align: right"]49.428[/TD]
[TD="align: right"]17.6[/TD]

[TD="align: right"]6546[/TD]
[TD="align: right"]76.732[/TD]
[TD="align: right"]-2.1[/TD]

[TD="align: right"]2227[/TD]
[TD="align: right"]34.84[/TD]
[TD="align: right"]-7.2[/TD]

[TD="align: right"]3366[/TD]
[TD="align: right"]61.4755[/TD]
[TD="align: right"]-3.1[/TD]

[TD="align: right"]8951[/TD]
[TD="align: right"]155.5835[/TD]
[TD="align: right"]483.1[/TD]

[TD="align: right"]2560[/TD]
[TD="align: right"]50.0155[/TD]
[TD="align: right"]-4[/TD]

[TD="align: right"]3723[/TD]
[TD="align: right"]59.0835[/TD]
[TD="align: right"]13.1[/TD]

[TD="align: right"]4253[/TD]
[TD="align: right"]72.334[/TD]
[TD="align: right"]8.8[/TD]

[TD="align: right"]948[/TD]
[TD="align: right"]19.1025[/TD]
[TD="align: right"]7.7[/TD]

[TD="align: right"]2952[/TD]
[TD="align: right"]48.485[/TD]
[TD="align: right"]-5.4[/TD]

[TD="align: right"]2673[/TD]
[TD="align: right"]43.478[/TD]
[TD="align: right"]14.7[/TD]

[TD="align: right"]886[/TD]
[TD="align: right"]11.0195[/TD]
[TD="align: right"]10.8[/TD]

[TD="align: right"]1461[/TD]
[TD="align: right"]17.2915[/TD]
[TD="align: right"]-2.2[/TD]

[TD="align: right"]742[/TD]
[TD="align: right"]19.459[/TD]
[TD="align: right"]50.1[/TD]

[TD="align: right"]2143[/TD]
[TD="align: right"]35.6265[/TD]
[TD="align: right"]15.5[/TD]

[TD="align: right"]389[/TD]
[TD="align: right"]5.0765[/TD]
[TD="align: right"]40.5[/TD]

[TD="align: right"]161[/TD]
[TD="align: right"]1.727[/TD]
[TD="align: right"]-14.8[/TD]

Quang-Ha-HHA_QNH1

[TD="align: right"]2169[/TD]
[TD="width: 98, align: right"]29.9325[/TD]
[TD="width: 103, align: right"]16.5[/TD]

Mình muốn xử lý 2 vấn đề :
1. Tai cột Cell nếu tên trùng nhau( không tính QNHx) thì tên tại Cột CELL sẽ bỏ đi giá trị QNHx và giá trị tại từng cột CS CALL CS TRAFFIC PERCENT được cộng tổng lại
Ví dụ: Ha-Phong2-HLG_QNH1, Ha-Phong2-HLG_QNH4 là trùng nhau Ha-Phong2-HLG ( không tính QNH1, QNH4) thì tên tại cột CELL chỉ giữ lại phần trùng là Ha-Phong2-HLG và giá trị tại từng CS CALL CS TRAFFIC PERCENT được cộng tổng lại.
2. Sau khi cộng được tổng thì tại cột CELL, tên nào có chữ HLG được tách sang 1 sheet riêng, tên nào có chữ MCI và HHA được tách thành 1 sheet riêng.

File đính kèm nhờ mọi người giúp đỡ.Xin cảm ơn.

 

[lenongree]

Chào mọi người!
Mình có file dạng:

CELL	CS CALL	CS TRAFFIC
Ha-Phong2-HLG_QNH1
KS-Huu-Nghi-HLG_QNH4
Ha-Phong2-HLG_QNH4
KS-Huu-Nghi-HLG_QNH5
Ha-Phong-HLG_QNH3
Cai-Lan-HLG_QNH3
Khu3-Yet-Kieu-HLG_QNH3
Ha-Khau-HLG_QNH5
Khu3-Yet-Kieu-HLG_QNH6
Ha-Khanh-A-HLG_QNH2
Lien-Co-IBS-HLG_QNH4
Ha-Khanh-A-HLG_QNH3
KS-Huu-Nghi-HLG_QNH1
KS-Huu-Nghi-HLG_QNH2
KS-Huu-Nghi-HLG_QNH3
Cai-Lan-HLG_QNH2
Ha-Phong2-HLG_QNH3
Ha-Khau-HLG_QNH6
To2-Hong-Ky-MCI_QNH5
Tra-Vi-MCI_QNH1
Khu2-Hai-Yen-MCI_QNH2
LT327-MCI_QNH2
Khu4-Hai-Yen2-MCI_QNH4

[TD="width: 103"]PERCENT[/TD]

[TD="align: right"]1059[/TD]
[TD="align: right"]16.9595[/TD]
[TD="align: right"]3.4[/TD]

[TD="align: right"]4924[/TD]
[TD="align: right"]60.0335[/TD]
[TD="align: right"]10.1[/TD]

[TD="align: right"]1369[/TD]
[TD="align: right"]20.0215[/TD]
[TD="align: right"]3.7[/TD]

[TD="align: right"]8102[/TD]
[TD="align: right"]105.815[/TD]
[TD="align: right"]11.6[/TD]

[TD="align: right"]1059[/TD]
[TD="align: right"]17.384[/TD]
[TD="align: right"]39.6[/TD]

[TD="align: right"]5383[/TD]
[TD="align: right"]85.248[/TD]
[TD="align: right"]-6.6[/TD]

[TD="align: right"]2385[/TD]
[TD="align: right"]49.428[/TD]
[TD="align: right"]17.6[/TD]

[TD="align: right"]6546[/TD]
[TD="align: right"]76.732[/TD]
[TD="align: right"]-2.1[/TD]

[TD="align: right"]2227[/TD]
[TD="align: right"]34.84[/TD]
[TD="align: right"]-7.2[/TD]

[TD="align: right"]3366[/TD]
[TD="align: right"]61.4755[/TD]
[TD="align: right"]-3.1[/TD]

[TD="align: right"]8951[/TD]
[TD="align: right"]155.5835[/TD]
[TD="align: right"]483.1[/TD]

[TD="align: right"]2560[/TD]
[TD="align: right"]50.0155[/TD]
[TD="align: right"]-4[/TD]

[TD="align: right"]3723[/TD]
[TD="align: right"]59.0835[/TD]
[TD="align: right"]13.1[/TD]

[TD="align: right"]4253[/TD]
[TD="align: right"]72.334[/TD]
[TD="align: right"]8.8[/TD]

[TD="align: right"]948[/TD]
[TD="align: right"]19.1025[/TD]
[TD="align: right"]7.7[/TD]

[TD="align: right"]2952[/TD]
[TD="align: right"]48.485[/TD]
[TD="align: right"]-5.4[/TD]

[TD="align: right"]2673[/TD]
[TD="align: right"]43.478[/TD]
[TD="align: right"]14.7[/TD]

[TD="align: right"]886[/TD]
[TD="align: right"]11.0195[/TD]
[TD="align: right"]10.8[/TD]

[TD="align: right"]1461[/TD]
[TD="align: right"]17.2915[/TD]
[TD="align: right"]-2.2[/TD]

[TD="align: right"]742[/TD]
[TD="align: right"]19.459[/TD]
[TD="align: right"]50.1[/TD]

[TD="align: right"]2143[/TD]
[TD="align: right"]35.6265[/TD]
[TD="align: right"]15.5[/TD]

[TD="align: right"]389[/TD]
[TD="align: right"]5.0765[/TD]
[TD="align: right"]40.5[/TD]

[TD="align: right"]161[/TD]
[TD="align: right"]1.727[/TD]
[TD="align: right"]-14.8[/TD]

Quang-Ha-HHA_QNH1

[TD="align: right"]2169[/TD]
[TD="align: right"]29.9325[/TD]
[TD="width: 103, align: right"]16.5[/TD]

Mình muốn xử lý 2 vấn đề :
1. Tai cột Cell nếu tên trùng nhau( không tính QNHx) thì tên tại Cột CELL sẽ bỏ đi giá trị QNHx và giá trị tại từng cột CS CALL CS TRAFFIC PERCENT được cộng tổng lại
Ví dụ: Ha-Phong2-HLG_QNH1, Ha-Phong2-HLG_QNH4 là trùng nhau Ha-Phong2-HLG ( không tính QNH1, QNH4) thì tên tại cột CELL chỉ giữ lại phần trùng là Ha-Phong2-HLG và giá trị tại từng CS CALL CS TRAFFIC PERCENT được cộng tổng lại.
2. Sau khi cộng được tổng thì tại cột CELL, tên nào có chữ HLG được tách sang 1 sheet riêng, tên nào có chữ MCI và HHA được tách thành 1 sheet riêng.

File đính kèm nhờ mọi người giúp đỡ.Xin cảm ơn.

Cái này làm bằng VBA sẽ hay hơn, mình sẽ làm bằng CT cho bạn tham khảo, bạn nhận file:

Mình thêm cột phụ ở bảng chính, sử dụng pivot để tính tổng lại
P/s: mình làm sheet HLG mấy sheet kia bạn làm tương tự nhe!!!

 

[quocgiacan]

Chào vnmoney,

Bạn lenongree đã thực hiện như yêu cầu của bạn. Tôi muốn bổ sung thêm cho 1 ít chỗ để hàm của lenongree mạnh hơn và sẽ tiện cho bạn khi copy sheet "HLG" để thành sheet "MCI". Cụ thể:

1. Sheet "T11072016", ô B2 (cột thêm của lenongree) đang là: =LEFT(A2,LEN(A2)-5), bổ sung: =LEFT(A2,SEARCH("_",A2,1)-1) phòng ngừa QNHx có thể lên đến số hạng 10,11,12...trở lên.
2. Sheet "HLG", ô A2:
=IFERROR(LOOKUP(2,1/(RIGHT(T11072016!$G$3:$G$46,3)="HLG")/(COUNTIF($A$1:A1,T11072016!$G$3:$G$46)=0),T11072016!$G$3:$G$46),"")
thay thế:
=IFERROR(LOOKUP(2,1/(RIGHT(T11072016!$G$3:$G$46,3)=RIGHT(CELL("filename",A1),3))/(COUNTIF($A$1:A1,T11072016!$G$3:$G$46)=0),T11072016!$G$3:$G$46),"")

Như vậy, bạn chỉ cần Move/copy (Create a copy) sheet "HLG" qua sheet mới "HLG (2)", rồi đổi tên đó thành "MCI" thì toàn bộ công thức trong bảng mới sẽ chạy theo tên này, và tương tự cho các sheet khác như: "CPA", "UBI", "HHA"....

Chúc bạn ngày vui

 

[lenongree]

Chào vnmoney,

Bạn lenongree đã thực hiện như yêu cầu của bạn. Tôi muốn bổ sung thêm cho 1 ít chỗ để hàm của lenongree mạnh hơn và sẽ tiện cho bạn khi copy sheet "HLG" để thành sheet "MCI". Cụ thể:

1. Sheet "T11072016", ô B2 (cột thêm của lenongree) đang là: =LEFT(A2,LEN(A2)-5), bổ sung: =LEFT(A2,SEARCH("_",A2,1)-1) phòng ngừa QNHx có thể lên đến số hạng 10,11,12...trở lên.
2. Sheet "HLG", ô A2:
=IFERROR(LOOKUP(2,1/(RIGHT(T11072016!$G$3:$G$46,3)="HLG")/(COUNTIF($A$1:A1,T11072016!$G$3:$G$46)=0),T11072016!$G$3:$G$46),"")
thay thế:
=IFERROR(LOOKUP(2,1/(RIGHT(T11072016!$G$3:$G$46,3)=RIGHT(CELL("filename",A1),3))/(COUNTIF($A$1:A1,T11072016!$G$3:$G$46)=0),T11072016!$G$3:$G$46),"")

Như vậy, bạn chỉ cần Move/copy (Create a copy) sheet "HLG" qua sheet mới "HLG (2)", rồi đổi tên đó thành "MCI" thì toàn bộ công thức trong bảng mới sẽ chạy theo tên này, và tương tự cho các sheet khác như: "CPA", "UBI", "HHA"....

Chúc bạn ngày vui

Mình muôn các CT nó ngắn đơn giản nên mới sử dụng vậy,đọc mấy bài phân tích của bạn hay ghê, thanks bạn!!!

 

[quocgiacan]

Mình muôn các CT nó ngắn đơn giản nên mới sử dụng vậy,đọc mấy bài phân tích của bạn hay ghê, thanks bạn!!!

Chào lenongree,

Bạn sử dụng hàm rất điêu luyện, người ngoài đôi khi tiếc cái đẹp chưa hoàn hảo nên "góp mây" 1 chút để thành bức tranh hoàn chỉnh mà.

Thật sự mới cảm ơn bạn nhiều.

Chúc bạn ngày vui

 

[huuthang_bd]

Một cách khác không dùng cột phụ. Copy sheet HLG và đổi tên thành MCI, HHA, CPA,... để có các sheet khác.

 

[lenongree]

Một cách khác không dùng cột phụ. Copy sheet HLG và đổi tên thành MCI, HHA, CPA,... để có các sheet khác.

Bạn chủ topic muốn một bảng tổng để gôm các mã trùng lại nữa thầy, file của thầy cột Cell có thể dùng CT này khỏi phải Ctrl+Shift+Enter

=IFERROR(LOOKUP(2,1/FIND(RIGHT(CELL("filename",$A$1)),T11072016!$A$2:$A$88)/(COUNTIF($A$1:A1,LEFT(T11072016!$A$2:$A$88,FIND("_",T11072016!$A$2:$A$88)-1))=0),LEFT(T11072016!$A$2:$A$88,FIND("_",T11072016!$A$2:$A$88)-1)),"")

 

[huuthang_bd]

Bạn chủ topic muốn một bảng tổng để gôm các mã trùng lại nữa thầy, file của thầy cột Cell có thể dùng CT này khỏi phải Ctrl+Shift+Enter

Tôi nghĩ là 2 bước của một yêu cầu.
Dùng LOOKUP thì kết quả sẽ bị ngược (lấy từ dưới lên).

 

[vnmoney]

Xin cảm ơn tất cả các bạn nhé. Bài toán đã được giải quyết nhanh chóng.

 

[Levanvi6497]

bạn ơi mình làm sẵn rồi nhé !

 

[vnmoney]

Cho mình hỏi lại khi mình dùng công thức của bạn huuthang_bd

=IFERROR(TRIM(LEFT(SUBSTITUTE(INDEX(T11072016!$A$2:$A$88,MATCH(0,COUNTIF(A1:A$1,LEFT(T11072016!$A$2:$A$88,FIND("_",T11072016!$A$2:$A$88)-1))+ISERROR(SEARCH(RIGHT(CELL("filename",$A$1),3)&"_",T11072016!$A$2:$A$88)),0)),"_",REPT(" ",100)),100)),"")

Khi mình chạy cho file lớn lên đến 3000 dòng, mình thay A$88 thành A$3000 thì công thức không cho ra kết quả
Các bạn giúp mình nhé.

 

[huuthang_bd]

Cho mình hỏi lại khi mình dùng công thức của bạn huuthang_bd

=IFERROR(TRIM(LEFT(SUBSTITUTE(INDEX(T11072016!$A$2:$A$88,MATCH(0,COUNTIF(A1:A$1,LEFT(T11072016!$A$2:$A$88,FIND("_",T11072016!$A$2:$A$88)-1))+ISERROR(SEARCH(RIGHT(CELL("filename",$A$1),3)&"_",T11072016!$A$2:$A$88)),0)),"_",REPT(" ",100)),100)),"")

Khi mình chạy cho file lớn lên đến 3000 dòng, mình thay A$88 thành A$3000 thì công thức không cho ra kết quả
Các bạn giúp mình nhé.

Hơn 3000 dòng thì thôi đừng dùng mấy công thức này. Nên dùng VBA.

 

[Ba Tê]

Cho mình hỏi lại khi mình dùng công thức của bạn huuthang_bd

=IFERROR(TRIM(LEFT(SUBSTITUTE(INDEX(T11072016!$A$2:$A$88,MATCH(0,COUNTIF(A1:A$1,LEFT(T11072016!$A$2:$A$88,FIND("_",T11072016!$A$2:$A$88)-1))+ISERROR(SEARCH(RIGHT(CELL("filename",$A$1),3)&"_",T11072016!$A$2:$A$88)),0)),"_",REPT(" ",100)),100)),"")

Khi mình chạy cho file lớn lên đến 3000 dòng, mình thay A$88 thành A$3000 thì công thức không cho ra kết quả
Các bạn giúp mình nhé.

Gợi ý khi dữ liệu lớn:
- Dùng VBA. Chọn Enable Macros khi mở file
- Chỉ 1 sheet Lọc.
Nếu bạn chịu thì xem file dưới.

 

[vnmoney]

Mình mở file nhưng thông báo lỗi
This workbook has lost its VBA project, ActiveX controls and any other programmability-related features.

 

[Ba Tê]

Mình mở file nhưng thông báo lỗi
This workbook has lost its VBA project, ActiveX controls and any other programmability-related features.

Tôi mở bình thường. Có lẽ Excel của bạn lúc cài đặt thiếu cái gì đó. Bạn xài Excel 2003 ?

 

[vnmoney]

Tôi mở bình thường. Có lẽ Excel của bạn lúc cài đặt thiếu cái gì đó. Bạn xài Excel 2003 ?

Mình đang dùng Excel 2013 bạn ạ

 

[vnmoney]

Tôi mở bình thường. Có lẽ Excel của bạn lúc cài đặt thiếu cái gì đó. Bạn xài Excel 2003 ?

Mình đã mở được macro, VBA của bạn chạy chuẩn và nhanh cho file lớn.
Bạn có thể bổ xung vào VBA giúp mình đoạn code khi lọc thì sắp xếp theo cột CS TRAFFIC theo giá trị từ thấp đến cao được ko.
Cảm ơn bạn nhiều!

[TD="width: 137"][/TD]

 

[quocgiacan]

Mình đã mở được macro, VBA của bạn chạy chuẩn và nhanh cho file lớn.
Bạn có thể bổ xung vào VBA giúp mình đoạn code khi lọc thì sắp xếp theo cột CS TRAFFIC theo giá trị từ thấp đến cao được ko.
Cảm ơn bạn nhiều!

Chào vnmoney, và các anh em đã tận tình giúp đỡ.

Trong khi chờ đợi "tác phẩm" của anh Ba Tê để hoàn thành chủ đề của bạn, mà thật sự tôi cũng đang háo hức trông chờ, tôi có thêm 1 gợi ý khác với vnmoney, và xin lỗi trước nếu như nó ngoài ý mong muốn của các bạn.

Xem lại các lệnh của cac anh lenongree, huuthang_bd tôi ngầm thán phục cách sử dụng điêu luyện hàm để trích lục dữ liệu mong muốn, cũng như giải pháp khôn ngoan bằng VBA để khắc phục sự cố dữ liệu có số dòng lớn của anh Ba Tê. Quả là tôi đã học thêm nhiều từ các bài học này.

Ngoài việc dùng hàm ra, thiết nghĩ trong thực tế quản lý số liệu, bạn nên thử thêm phương án dùng Pivot table cho chủ đề với dữ liệu lớn như bạn đưa ra. Pivot Table cũng sẽ thỏa mãn cho bạn bằng:

1. Dụng cụ hỗ trợ của nó như "Slicer": để không cần tách thêm nhiều sheet chi tiết theo từng nhóm như: "HLG", "MCI"...., tất cả chỉ cần tập họp vào 1 sheet và dùng "Slicer" (nó giống như một command button vậy) để thể hiện từng nhóm bạn mong muốn, chỉ cần bạn nhấn nút mà thôi.
2. Hoặc sort các cột theo ý bạn muốn: như thấp đến cao, từ cao xuống thấp.....

Vì đây là gia đình "Giải pháp", nên tôi cũng mạnh dạn góp 1 chút gợi ý về giải pháp, và xin bỏ quá cho tôi nếu như có gây phật lòng cho các bạn.

Nếu như bạn quan tâm đến đề nghị như vậy, vui lòng theo đường link dưới đây là file mà tôi đã thực hiện sẵn để bạn xem đã như ý chưa, cũng xin lỗi vì sự bất tiện này cũng vì tôi không còn hạn mức "upload" file từ máy tôi lên diễn đàn để phục vụ cho bạn.

https://drive.google.com/file/d/0B4LHoUy1L9LGUGptaF84dm5ORjA/view?usp=sharing

Chúc các bạn một ngày làm việc hiệu quả và vui vẻ.

 

[Ba Tê]

Mình đã mở được macro, VBA của bạn chạy chuẩn và nhanh cho file lớn.
Bạn có thể bổ xung vào VBA giúp mình đoạn code khi lọc thì sắp xếp theo cột CS TRAFFIC theo giá trị từ thấp đến cao được ko.
Cảm ơn bạn nhiều!

[TD="width: 137"][/TD]

Bạn ấn Alt+F11, trong Module1, thay dòng If K then .... bằng đoạn này:

If K Then
        .Range("A3").Resize(K, 4) = dArr
        .Range("A3").Resize(K, 4).Sort Key1:=.Range("C3")
End If

 

[vnmoney]

Xin cảm ơn tất cả anh em đã giúp đỡ nhiệt tình.

 

[vnmoney]

Chào anh Ba Tê

Hiện tại file của e có phát sinh tên trong cột CELL có các ký tự đầu khác nhau:
Ví dụ:
2G_Yen-Tu-UBI3_QNH
2G_Ga-Cap-Treo-Yen-Tu-UBI3_QNH
2G_Yen-Tu-UBI2_QNH
2G_Ga-Cap-Treo-Yen-Tu-UBI2_QNH
2G_Ga-Cap-Treo-Yen-Tu-UBI1_QNH
2G_BX-Yen-Tu-UBI3_QNH
2G_Yen-Tu-UBI1_QNH
BX-Yen-Tu-UBI_QNH2
BX-Yen-Tu-UBI_QNH4
Ga-Cap-Treo-Yen-Tu-UBI_QNH5
Ga-Cap-Treo-Yen-Tu-UBI_QNH2

Bài toán vẫn như trước, a đã xử lý giúp phần ký tự khác nhau ở cuối. Nhờ anh xử lý giúp e cả phần ký tự khác nhau ở phần đầu,trường hợp này là ký tự "2G_" .
Cảm ơn anh.