Làm thế nào để lập công thức excel tính cước phí điện thoại!

Liên hệ QC
dữ liệu bạn Nirvana post lên là y nguyên từ tổng đài nó đổ số này ra file, hay là bạn có chỉnh sửa rồi? thường tổng đài nó đổ số ra file TXT, hay CSV thôi chứ nhỉ. Bạn cho biết thêm được không? nếu là từ file khác, bạn có thể upload file đó lên được không, không cần toàn bộ, nhưng có các dữ liệu đại diện càng nhiều càng tốt, bây giờ còn thêm cái thằng di động 11 số (168...) của Viettel nữa chứ. mệt nhỉ.!!!

Với lại thêm một ý nữa, tổng đài của bạn không lưu số đầu để quay ra à (Dial out extension thì phải, thường thì là 9,8,0, tùy hệ thống tổng đài) có nó thì mới phân biệt được số local với các dịch vụ khác được chứ (vd bạn có ext 8201, không có số đầu thì không phân biệt được các dịch vụ khác (1088, 1080..., 997,998, hay cách dịch vụ như 8030..)
 
Lần chỉnh sửa cuối:
Chào các bạn!
Mình xin đưa thêm một số thông tin chi tiết về dịch vụ tính cước ở công ty mình để các bạn có thể nghiên cứu giúp mình. Dịch vụ điện thoại ở công ty mình là dịch vụ gọi Quốc tế thông qua Internet hoặc dùng thiết bị đầu cuối nếu muốn gọi bằng điện thoại. Nếu gọi bằng điện thoại thì phải cài đặt thêm thiết bị đầu cuối như: Eztalk...Nếu ra Net để gọi thì phải có card như một số dịch vụ gọi đt quốc tế: voice777, Usvoiz...Vì thế ở đây chỉ tính cước ở các nước ngoài mà thôi, không tính cước cho Vietnam, và di động. Cỏn list các số của khách hàng gọi mình lấy source đó từ hệ thống CDR của công ty mình ( copy qua file excel, thường thì = file CSV). Trên hệ thống cũng không có số 0 nằm đầu. Những số 8201 (extension) mình sẽ tách ra tính cước riêng.
 
Lần chỉnh sửa cuối:
Các bạn có ai biết thì góp ý giúp mình giải bài toán này nhé. Tất nhiên không phải một sớm một chiều, nhưng hy vọng sẽ tìm ra được một hướng đi nào đó tốt hơn.
Thanks all,
 
Lần chỉnh sửa cuối:
@Nirvana list CDR này có bảo đảm là tất cả các số có trong danh sách ở đây đều là các cuộc gọi thành công? mình hỏi vậy vì nếu có người gọi sai số thì sao?
tiếp nữa, nếu cuộc gọi ra có gọi đến một extension ở đầu gọi đến thì nó có lưu số extension đó không? ví dụ như Nirvana có extension trong công ty là 8888, số điện thoại của công ty nirvana là 1234567, trụ sở Hà Nội. vậy thì nếu muốn gọi cho bạn, mình sẽ bấm 84.4.1234567.8888, tổng đài của bạn lưu số gì? 84.4.1234567.8888 hay (84.4.1234567).???
 
Tôi đã làm một số việc tương tự như bài toán của Bạn như xây dựng chương trình tìm người được nâng lương theo mã lương và thời hạn nâng lương. Trước hết Bạn phải xây dựng một vùng cố định có hai cột Mã vùng và Giá tiền(/giây/phút) để làm CSDL để hàm VLOOKUP tìm so sánh, sau đó dùng hàm VLOOKUP tham chiếu đến vùng đó để tìm giá theo giá trị của hàm LEFT tìm ra * với giá trị Thời gian gọi. Chúc Bạn thành công với phương pháp này và ứng dụng trong các yêu cầu tương tự khác. BDT-TTQLBMB.__--__
 
Lần chỉnh sửa cuối:
Tiger62 đã viết:
Tôi đã làm một số việc tương tự như bài toán của Bạn như xây dựng chương trình tìm người được nâng lương theo mã lương và thời hạn nâng lương. Trước hết Bạn phải xây dựng một vùng cố định có hai cột Mã vùng và Giá tiền(/giây/phút) để làm CSDL để hàm VLOOKUP tìm so sánh, sau đó dùng hàm VLOOKUP tham chiếu đến vùng đó để tìm giá theo giá trị của hàm LEFT tìm ra * với giá trị Thời gian gọi. Chúc Bạn thành công với phương pháp này và ứng dụng trong các yêu cầu tương tự khác. BDT-TTQLBMB.__--__
Bác chẳng chịu theo dõi kỹ đề tài từ đầu, nói chung chung thế tôi cũng làm được.
 
Mình xin nói thêm một chút về hệ thống CDR của công ty mình: Với các cuộc gọi ở ngoài gọi vào cho công ty >> sau khi bấm số đt của công ty, các bạn sẽ nghe: công ty MyA xin kính chào quý khách, xin vui lòng bấm số nội bộ hoặc thực hiện theo hướng dẫn sau (bấm phím 0 để gặp tiếp tân...). Nếu như các bạn biết chính xác số đt nội bộ của mình (vd: 6024) thì bấm trực tiếp, còn nếu bấm phím 0 để gặp tiếp tân, nv tiếp tân sẽ chuyển máy lên cho mình. Nhưng các cuộc gọi kiểu này không hiển thị số trên hệ thống CDR của công ty. Đối với những số đt nội bộ gọi cho nhau giữa các nhân viên trong công ty (dù là chi nhánh công ty nằm ở đâu) thì hệ thống CDR chỉ hiển thị duy nhất số ext đó (6024 >> 6025..). Chỉ có số ở công ty gọi đi đâu đó (gọi quốc tế), hoặc khách hàng của công ty gọi đi đâu đó thì trên hệ thống CDR mới hiển thị đầy đủ: mã quốc gia + mã vùng + số đt. Riêng với những số gọi sai, hoặc không trả lời, không kết nối, hay bận, thì khi copy sang file excel mình đã xóa đi hết rồi, chỉ còn lại những cuộc gọi thành công mà thôi.
 
Xin hỏi bạn ví dụ gọi đến 17149953118, thì đây là cuộc gọi 171 và số là 49953118 hay là gọi đến theo mã vùng 1, theo bạn là Canada, (hình như 1 là Mỹ mà).
Còn 8201 -> 6020 là gọi gì vậy, nội bộ??? vì đâu có số mà có 4 số.
Bài này thuộc loại đặc thù.
 
Nrivana nên cho tất cả yêu cầu vào trong file của em và upload lại file. Vì bài toán rất đa dạng nên cần nói kỹ yêu cầu và cung cấp thật nhiều thông tin, càng nhiều càng tốt...
 
17149953118 > là số gọi đi Mỹ. Canada cũng có mã quốc gia là 1, Mỹ và Canada đều có chung một giá cước nên có thể gộp lại. Còn 8201 -> 6020: là số đt nội bộ gọi cho nhau (8201 gọi cho 6020). Thường thì phải gọi vào số máy bàn của công ty rồi mới bấm tiếp số nội bộ, nhưng ở đây vì cùng là số nội bộ nên hệ thống của công ty các nhân viên IT đã cấu hình cho nó gọi trực tiếp. Mình sẽ upload lại file lên sau. Nhưng mà trên hệ thống CDR hiện tại chỉ thấy các cuộc gọi nội bộ và gọi đi Mỹ thôi, nếu muốn nghiên cứu để lập hàm thì tự mình phải đặt thêm vào list các cuộc gọi khác nhau từ nhiều quốc gia khác nhau. Dữ liệu ở đây không quan trọng, bởi vì sau này mình sẽ lọc riêng những số gọi nội bộ bỏ vào sheet khác để tính riêng. Chủ yếu là dựa vào bảng giá có các mã vùng chuẩn, rồi từ đó mình cũng có thể đặt tùy ý các số điện thoại cho các quốc gia khác nhau để nghiên cứu lập hàm excel. Danh sách các cuộc gọi trên CDR hiện tại khách hàng chỉ gọi có bấy nhiêu số đó thôi. Sau này có thể họ sẽ gọi thêm các quốc gia khác thì chưa biết được. Nhưng mình có thể tự ý đặt thêm list các cuộc gọi vào đó để đủ điều kiện dễ dàng hơn cho việc lập công thức.
 
Lần chỉnh sửa cuối:
nirvana842005 đã viết:
Nhưng các cuộc gọi kiểu này không hiển thị số trên hệ thống CDR của công ty. Đối với những số đt nội bộ gọi cho nhau giữa các nhân viên trong công ty (dù là chi nhánh công ty nằm ở đâu) thì hệ thống CDR chỉ hiển thị duy nhất số ext đó (6024 >> 6025..).

Phần này mình biết, mình chỉ hỏi phần dưới.

Chỉ có số ở công ty gọi đi đâu đó (gọi quốc tế), hoặc khách hàng của công ty gọi đi đâu đó thì trên hệ thống CDR mới hiển thị đầy đủ: mã quốc gia + mã vùng + số đt (có + với số extension của họ không?).

ý mình là hỏi bạn phần có màu đỏ.
 
Cái ông SoiBien này, mệt quá! Hình như ông hỏi câu này 2 lần rồi. Nếu mà có thì người ta đã trả lời cho ông. Số điện thoại gọi đi Mỹ có cấu trúc như sau:
1 XXX yyy yyyy (11 số)
1 là mã của Mỹ, XXX là mã vùng (bang), còn yyy yyyy (7 số) là số điện thoại.
Trong file bạn ấy gửi lên, những số dài (mà bắt đầu bằng 1) toàn là 11 số thôi. Đó là những cuộc gọi đi Mỹ.

Tôi theo dõi bài này từ đầu, quả thật là rất khó, chứ không chỉ là khó, nếu như tác giả không tách ra được một cột chỉ ghi mã quốc gia.
Ví dụ, riêng VN thôi, thì nếu
gọi tới TpHCM: 84 8 1234567 (10 số)
gọi vào di động ở VN: 84 9 82827762 (11 số)
gọi vào số máy ở Đồng Nai: 84 613 123456 (11 số)
v.v... Đó là chưa nói mai mốt Bưu chính viễn thông lại tăng thêm đầu số...
Kiểu này thì dùng hàm (Left hay Len chẳng hạn) là bó tay. Mà dùng VBA thì tôi nghĩ hoài chưa ra.
 
Lần chỉnh sửa cuối:
Tôi đã đọc toàn bộ bài này từ đầu. Bạn nirvana842005 hãy chuẩn bị một file dữ liệu có đầy đủ thông tin và yêu cầu tính toán, kết xuất dữ liệu. Chắc Bạn cũng hiểu một cơ sở dữ liệu phải có sắp xếp, phân tổ thống kê đầy đủ, có đầy đủ các trường hợp xảy ra, ý tôi muốn nói phải có đầy đủ thông tin về tổ chức CSDL. Việc phân tổ các loại số ĐT cũng phải đặt ra và dùng các hàm xử lý chuỗi để tách các thành phần của số dù có 2 đến 11,12,... chữ số đều tách ra được cả theo từng nhóm số. Tôi cũng hay sử dụng hàm VLOOKUP để tìm kiếm thông tin trong CSDL và tính toán, tôi sẽ lập bảng tính giải bài toán trên để Bạn tham khảo.
 
Để xử lý chuỗi, tôi thường phải kết hợp nhiều hàm với nhau kể cả các hàm logic, hàm đo đếm, tìm kiếm,... Ví dụ có thể tách tên tiếng Việt ( tên đứng sau cùng ) và tiếng Việt là một âm và hình như không có âm nào quá 7-8 chữ cái thì phải : B66=IF(CODE(RIGHT(A66,2))=32,RIGHT(A66,1),IF(CODE(RIGHT(A66,3))=32,RIGHT(A66,2),IF(CODE(RIGHT(A66,4))=32,RIGHT(A66,3),IF(CODE(RIGHT(A66,5))=32,RIGHT(A66,4),IF(CODE(RIGHT(A66,6))=32,RIGHT(A66,5),IF(CODE(RIGHT(A66,7))=32,RIGHT(A66,6),RIGHT(A66,7)))))))
=ccccccc . A66=aaaa bbbb ccccccc.
 
đây là file mình làm, bạn xem nhé.
 
Lần chỉnh sửa cuối:
Chào bạn SoiBien!
Bạn upload lên file tracuuMavung của bạn một lần nữa giúp mình với nhé. Vì mình không thể download được file của bạn về. Còn phần mà bạn hỏi về những số ext thì hệ thống CDR của mình có hiển thị số ext nhưng chỉ có số ext thôi, không có mã vùng đằng trước nó. (những ext này mình sẽ bỏ vào một sheet khác để tính riêng). Chủ yếu là làm thế nào đó để xác định được mã vùng các quốc trên thế giới nằm đằng trước số đt của khách hàng mà thôi. (ext mình sẽ bỏ riêng vào sheet khác). Hiện tại mình chưa download được file của bạn về. Mình cũng có một hướng đi cho bài toán của mình (file đính kèm bên dưới), nhưng vẫn chưa hoàn thành và không biết có đúng không?, mình upload lên đây nhờ các bạn góp ý và phát triễn thêm giúp mình.
Thanks
Huỳnh Diệp
 

File đính kèm

nirvana842005 đã viết:
Chào bạn SoiBien!
Bạn upload lên file tracuuMavung của bạn một lần nữa giúp mình với nhé. Vì mình không thể download được file của bạn về.

Kỳ nhỉ, sao thấy người khác vẫn load được mà!
 
Lần chỉnh sửa cuối:
Nirvana dù sao cũng là một band yêu thích của mình ngày còn bé. :dBác Anh Tuấn làm ơn gửi mail giúp em với, em đang ở ngoài vùng phủ sóng của Sfone, bó càng, vô internet khó quá.
 
Cảm ơn bạn SoiBien nhé! Mình đã nhận được file của bạn rồi, tiện đây cũng xin cảm ơn Bác anhtuan1066 đã gửi email cho nirvana. Bạn SoiBien oi! file của bạn mình đã đọc nhưng mình có một số vấn đề chưa hiểu muốn hỏi bạn nhờ bạn hướng dẫn thêm nhé. File của bạn hình như viết bằng VBA, có dùng macro trong đó. Và Công thức bạn sử dụng chỉ xử lý được dữ liệu trực tiếp mà không thể chạy tự động được. Mỗi khi mình thay số đt khác vào cột Số gọi thì nó báo lỗi #name, không load được dữ liệu ở cột mã vùng giá cước. Nếu như thế thì e rằng sẽ khó cho mình về sau này, vì mình muốn dùng công thức làm sao đó để có thể sử dụng mọi lúc, chỉ cần copy list các cuộc gọi của khách hàng vào là file excel tự động tính cước cho mình thôi. Bởi vì sau này sẽ có hàng ngàn khách hàng với vô số cuộc gọi. Mà VBA thì mình không biết gì cả, mà dùng VBA để làm bài toán này thì quả là khó. Bạn SoiBien ah! Bạn có thể dùng theo cách của mình để phát triễn thêm giúp mình được không. (file đính kèm mình đã up len ở trên đó).
Thanks
Huỳnh Diệp
 
Lỗi #name chắc có lẽ là do bạn chưa đặt excel của bạn ở chế độ enable macro, vậy bạn đóng file tracuu lại, rồi vào menu Tools/options/ chọn tab security, chọn mục Macro Security, chọn Medium, OK, OK

Rồi bạn mở ra lại lần nữa file tracuu, chọn enable Macro, thử nhập lại công thức xem sao?
trong công thức, =searchbycountrycode(D2,data,4)
thì d2 là số gọi,
data là vùng từ A2:E361 của sheet tinhcuoc,
4 là cột để lấy giá trị tương ứng
(hàm tương tự như hàm Vlookup).

Thử xem thế nào bạn?
 
Lần chỉnh sửa cuối:
Web KT

Bài viết mới nhất

Back
Top Bottom