Tanya provider Hp

  1. 10 bulan yang lalu

    Kembali saya menayakan permasalahan ni telp seperti yg terlampir.
    Data yang kami inggunkan adalah brp jumkah pelanggan masing2 provider yang telah melakukan transaksi dicounter. Seperti tercantum dalam tampilan berikut. Saya ambil contoh apabila no telp lengkapnya jumlahnya seperti yg tertera, dan yg dibawah adalah 4 no masing2 procider. Jadi apabila melihat 4 no aqalan tsb. Maka langsung bisa dimasukan dalam kolom masing provider jumkah penggunanya.
    Karena data ada ribuan. Kami mengambil contoh sebagian kecil seperti yg kami lampirkan. Atas bantuannya kami ucapkan terimakasih.

    Untuk menghitung jumlah baris data Nomor Telepon yang tidak memiliki kode prefiks yang sesuai, maka ada 2 kemungkinan proses (konsep) yang bisa kita lakukan:

    Pertama: Dengan menyusun daftar kode prefiks. Untuk setiap nomor telepon yang kode prefiks tidak diketahui, maka kita susun dalam sebuah daftar khusus, misalnya ="479|905|999" atau bisa juga ="479 905 999". Perhatikan, pemisah antar kode prefiks bisa apa saja, namun sebaiknya gunakan karakter tertentu yang tidak terdapat dalam daftar kode prefiks. Tujuan pemisah ini adalah untuk memisahkan nilai kode prefiks dan memudahkan memeriksanya. Untuk proses perhitungannya bisa menggunakan formula yang digunakan bisa menggunakan formula seperti sebelumnya dengan mengubah notasi sel rujukan fungsi SEARCH, dalam hal ini merujuk ke sel L7:

    =SUMPRODUCT((ISNUMBER(SEARCH(LEFT($A$4:$A$24;3);$L$7)))*1)

    Kedua: Dengan menggabungkan seluruh kode prefiks yang diketahui providernya, namun untuk kalkulasinya kita menerapkan konsep nilai yang tidak ada di dalam daftar. Dengan asumsi sel L1 sampai dengan sel L6 merupakan daftar kode prefiks yang provider yang diketahui, maka pada sel L7 bisa kita susun formulanya:

    =L1&"|"&L2&"|"&L3&"|"&L4&"|"&L5&"|"&L6

    atau juga bisa berupa satu teks langsung misalkan:

    ="831|838|855|856|857|858|815|816|851|887|888|811|812|813|821|822|852|853|823|851|896|897|898|899"

    Untuk proses perhitungannya, dengan asumsi rujukan ke sel L7, kita harus menambahkan fungsi NOT agar nilai-nilai hasil dari fungsi SEARCH dan fungsi ISNUMBER dibalik, sehingga kode prefiks yang sesuai akan menjadi tidak sesuai dan demikian sebaliknya. Nilai yang telah dibalik ini kemudian dijumlahkan:

    =SUMPRODUCT(NOT(ISNUMBER(SEARCH(LEFT($A$4:$A$24;3);$L$7)))*1)

    Alasan saya menggunakan konsep yang ke-2 ini adalah dengan pertimbangan jika jumlah datanya ribuan, maka akan merepotkan untuk memilah dan mengambil satu persatu kode prefiks yang tidak diketahui providernya dan kemudian menyusunnya kembali menjadi satu daftar kode prefiks. Asumsi saya, jika ada data Nomor Telepon yang tidak ada dalam daftar kode prefiks yang diketahui, maka dianggap nomor luar negeri. Dan konsep ini jauh lebih ringkas... :D

  2. filenya belum terlampir @Mardana

  3. Padahal sudah dilamoirkan neee mas @Shintaro .sy coba upload ulang ne mas. Terinakasih

  4. Caton

    3 Des 2017 Terverifikasi Indonesia + 11.512 Poin

    Silahkan dipelajari dan dimodifikasi file terlampir @Mardana... Kunci pencarian datanya ada pada definisi nama kode prefiks provider-nya. Jika ada kode dari provider tertentu yang belum masuk ke dalam daftar, cukup ubah atau tambahkan pada definisi nama prefiks sesuai provider-nya. Demikian juga jika ingin menambahkan daftar kode prefiks provider baru, buat definisi nama sesuai keinginan, kemudian isi atau edit langsung kode prefiksnya. Baiknya setiap kode dipisahkan dengan karakter tertentu (misalnya, saya menggunakan karakter | -- karakter pipa atau pipe character). Contohnya:

    Shoot003.png

    Pada gambar di atas, nama Prefix.All merupakan gabungan seluruh kode prefiks provider lainnya. Tujuannya untuk mencari data kode prefiks provider yang tidak ada dalam daftar. Demikian yang bisa saya bantu dan saya jelaskan. Semoga sesuai... ;)

  5. Terimakasih kembali pada mas@Caton . maaf apakah penulisan di prefix tsb. Kalo diexcell 2003 tsb di format cell dan custom ya? Karena sudah saya coba googling di prefix excell 2003. Ditunjukannya dengan gambar custom ini mas. Mohon maaf banyak yg belum saya ketahui. Terimakasih mas

  6. Caton

    3 Des 2017 Terverifikasi Indonesia + 11.512 Poin

    Oh... Sori. Untuk Excel 2003, coba lihat pada menu INSERT > NAME > DEFINE. Gambarannya:

    -image-

  7. Caton

    3 Des 2017 Terverifikasi Indonesia + 11.512 Poin

    Jika bermasalah dengan Defined Name Range, solusinya bisa menggunakan sel biasa. Misalkan saja pada sheet DATA, di sel L1 untuk prefiks Telkomsel, sel L2 untuk prefiks Indosat dan seterusnya. Isi nilainya saja, yakni di sel L1 diisi:

    ="811|812|813|821|822|852|853|823|851"

    Demikian pula dengan definisi nama lainnya. Kemudian pada formula, diubah acuannya. Misalnya untuk sel D4 (kolom Telkomsel) formula sebelumnya:

    =SUMPRODUCT((ISNUMBER(SEARCH(LEFT(Table.Data[A];3);Prefix.Telkomsel)))*1)

    diubah menjadi:

    =SUMPRODUCT((ISNUMBER(SEARCH(LEFT(Table.Data[A];3);$L$1)))*1)

    Demikian pula dengan kolom provider lainnya... ;)

  8. Mas Caton...kok saya gagal paham. Padahal suda sy ikuti langkah yg mas@Caton berikan. Terlampir saya capture. Kesalahannya atau kekeliruannya ada dimana y mas.???? Semoga tidak merepotkan dan terimakasih atas sharing ilmunya yang sangat berharga????????

  9. Caton

    3 Des 2017 Terverifikasi Indonesia + 11.512 Poin

    Coba periksa kembali pada notasi fungsi LEFT(A:4). Seharusnya pada fungsi LEFT tersebut, argumen sumber teks dan argumen jumlah karakter yang diambil dipisahkan dengan tanda koma atau titik koma (tergantung kepada pengaturan Regional Setting pada komputer). Kemudian, perhatikan juga notasi range acuannya. Jika acuan teks sumbernya berupa sel, notasinya adalah indeks kolom disambung dengan indeks baris (contoh: A5, B100), atau jika acuannya berupa range, maka notasi sel asal dan sel akhir dari teks sumbernya dipisahkan oleh tanda titik dua (contoh: A5:A25, B1:B5). Jadi seharusnya:

    ... LEFT(A4,3) ... atau ... LEFT(A4;3) ...

    Kemudian, agar jumlah kode prefiks per provider dapat dihitung dengan tepat, acuan teks sumber pada fungsi LEFT yang digunakan di dalam setiap formula (misalkan formula pada sel D4) harus berupa range, buka sel. Jadi acuannya ke range data, tepatnya pada kolom Nomor Telepon, misalnya range datanya A4:A24 dan range rujukan Kode Prefiks-nya pada sel L1, maka formulanya akan menjadi:

    =SUMPRODUCT((ISNUMBER(SEARCH(LEFT($A$4:$A$24;3);$L$1)))*1)

    Perhatikan juga bahwa notasi sel dan range acuan harus dibuat absolut (ditandai dengan tanda dolar). Dan satu hal lagi, untuk nomor telepon yang kode prefiksnya tidak diketahui providernya (semisalnya nomor telepon luar negeri), tidak perlu dibuatkan definisinya (kecuali memang dibutuhkan). Coba lihat file yang saya lampirkan atau pada gambar Name Manager di atas. Nama Prefix.All merupakan gabungan dari nama kode prefiks provider lainnya. Jadi semisalnya kode prefiks dari masing-masing provider disusun pada sel (asumsikan mulai sel L1 sampai dengan sel L6, maka sel L7 merupakan gabungan isi dari range L1:L6 (bisa digabungkan dengan formula atau secara manual). Isi sel L7 ini nantinya digunakan di dalam formula untuk menghitung nomor luar negeri (pada gambar @Mardana di atas, pada sel I4).

    Silahkan coba disusun kembali formulanya dan lihat apakah kalkulasinya benar. Demikian yang bisa saya jelaskan... ;)

  10. Baik mas @Caton . terpecahkan. Rumus sudah saya perbaiki. Karena ternyata penulisan di tanda koma dan titik koma???? .setelah saya buat ternyata hasilnya nol . Terlampir dalam captured ini. Langkah yg sy lakukan

    1. Periksa Tulisan di kolom provider sudah sesuai dengan yg mas caton sampaikan. Penulisan ="811|812|813|dst..." begitu juga yg provider lain.
    2. Cek penulisan di kolom A. Sudah sesuai dengan format cell general. Jadi harusnya rumus berfungsi dengan menghitung sesuai left yg diambil dari nomor dikolom A yaitu 3 angka dari depan. Semakin penasaran saya .
    3. Sudah saya salin rumus seperti ini juga "=SUMPRODUCT((ISNUMBER(SEARCH(LEFT($A$4:$A$24;3);$L$1)))*1)" masih juga belum bisa. Hasilnya masih angka nol. Apakah ada yg masih kurang dari cara penulisan tsb. Karena saya lihat rumus sudah berfungsi. Semuanya menjadi huruf besar. Namun hasilnya masil nol.Terimakasih
  11. Eureka. Ternyata saya salah penerapan sumproduct harus ditulis sesuai jenis provider pada row berikutnya
    Yang belum bisa malah no telp Luar Negrinya tidak muncul. Harusnya ada 8 nilai. Seperti dalam contoh. Apakah haruss ditulis prefiksnya juga. Seperti yg sebelumnya. Karena mas Caton bilang bahwa tdk perlu dibuat untuk provider luar negri di row L7. Terimakasih. Maaf telah merepotkan????

  12. Caton

    4 Des 2017 Terverifikasi Indonesia + 11.512 Poin

    Saya coba jelaskan kembali agar mbak/mas @Mardana bisa lebih memahami formulanya. Formula yang digunakan intinya mencari kecocokan kode prefiks dari setiap nomor telepon terhadap kode prefiks setiap provider yang telah ditentukan.

    =SUMPRODUCT((ISNUMBER(SEARCH(LEFT($A$4:$A$24;3);$L$1)))*1)

    Oleh karena kode prefiks terdiri dari 3 karakter/angka, maka kita asumsikan bahwa setiap baris data nomor telepon dimulai dengan 3 karakter/angka kode prefiks tersebut. Oleh karena itu, ada 2 kelompok data yang akan kita bandingkan: data Nomor Telepon dan data Kode Prefiks Per Provider. Dalam hal ini, misalnya ditentukan data Nomor Telepon berada pada range A4:A24 dan data Kode Prefiks ditentukan pada sel L1 sampai L7.

    Untuk mengambil 3 karakter dari data Nomor Telepon, kita menggunakan fungsi LEFT. Normalnya, argumen Text pada fungsi LEFT hanya mengacu kepada sel tunggal atau diisi dengan teks langsung. Namun dalam kasus ini, karena kita ingin menghitung jumlah baris data Nomor Telepon yang sesuai dengan provider tertentu, maka argumen Text pada fungsi LEFT harus kita rujuk kepada range data Nomor Telepon, sehingga formulasinya menjadi:

    LEFT($A$4:$A$24;3)

    Formula di atas akan menghasilkan Array berupa 3 karakter/angka dari setiap baris data Nomor Telepon. Berikutnya, untuk setiap nilai yang dihasilkan oleh fungsi LEFT tersebut, akan diperiksa apakah ada di dalam daftar kode prefiks dari provider yang akan kita periksa.

    Oleh karena fungsi LEFT pada dasarnya akan menghasilkan nilai dengan tipe data Text, maka triknya kita bisa menggunakan fungsi SEARCH untuk mencari kecocokan setiap nilai yang dihasilkan fungsi LEFT di dalam teks daftar kode prefiks. Untuk itu, daftar kode prefiks dari setiap provider kita susun dalam sebuah teks dan diletakkan pada sebuah sel (atau Named Range) untuk masing-masing provider. Sederhananya: L1 untuk kode prefiks Telkomsel, L2 untuk kode prefiks Indosat, L3 untuk kode prefiks XL dan seterusnya. Jika ada 6 provider yang diketahui kode prefiksnya, maka berarti sel yang terisi adalah sel L1 sampai dengan sel L6.

    Lalu bagaimana dengan Nomor Telepon yang tidak ada kode prefiksnya (misalnya untuk nomor telepon luar negeri)?

  13. Caton

    4 Des 2017 Terverifikasi Jawaban Terpilih Indonesia + 11.512 Poin

    Untuk menghitung jumlah baris data Nomor Telepon yang tidak memiliki kode prefiks yang sesuai, maka ada 2 kemungkinan proses (konsep) yang bisa kita lakukan:

    Pertama: Dengan menyusun daftar kode prefiks. Untuk setiap nomor telepon yang kode prefiks tidak diketahui, maka kita susun dalam sebuah daftar khusus, misalnya ="479|905|999" atau bisa juga ="479 905 999". Perhatikan, pemisah antar kode prefiks bisa apa saja, namun sebaiknya gunakan karakter tertentu yang tidak terdapat dalam daftar kode prefiks. Tujuan pemisah ini adalah untuk memisahkan nilai kode prefiks dan memudahkan memeriksanya. Untuk proses perhitungannya bisa menggunakan formula yang digunakan bisa menggunakan formula seperti sebelumnya dengan mengubah notasi sel rujukan fungsi SEARCH, dalam hal ini merujuk ke sel L7:

    =SUMPRODUCT((ISNUMBER(SEARCH(LEFT($A$4:$A$24;3);$L$7)))*1)

    Kedua: Dengan menggabungkan seluruh kode prefiks yang diketahui providernya, namun untuk kalkulasinya kita menerapkan konsep nilai yang tidak ada di dalam daftar. Dengan asumsi sel L1 sampai dengan sel L6 merupakan daftar kode prefiks yang provider yang diketahui, maka pada sel L7 bisa kita susun formulanya:

    =L1&"|"&L2&"|"&L3&"|"&L4&"|"&L5&"|"&L6

    atau juga bisa berupa satu teks langsung misalkan:

    ="831|838|855|856|857|858|815|816|851|887|888|811|812|813|821|822|852|853|823|851|896|897|898|899"

    Untuk proses perhitungannya, dengan asumsi rujukan ke sel L7, kita harus menambahkan fungsi NOT agar nilai-nilai hasil dari fungsi SEARCH dan fungsi ISNUMBER dibalik, sehingga kode prefiks yang sesuai akan menjadi tidak sesuai dan demikian sebaliknya. Nilai yang telah dibalik ini kemudian dijumlahkan:

    =SUMPRODUCT(NOT(ISNUMBER(SEARCH(LEFT($A$4:$A$24;3);$L$7)))*1)

    Alasan saya menggunakan konsep yang ke-2 ini adalah dengan pertimbangan jika jumlah datanya ribuan, maka akan merepotkan untuk memilah dan mengambil satu persatu kode prefiks yang tidak diketahui providernya dan kemudian menyusunnya kembali menjadi satu daftar kode prefiks. Asumsi saya, jika ada data Nomor Telepon yang tidak ada dalam daftar kode prefiks yang diketahui, maka dianggap nomor luar negeri. Dan konsep ini jauh lebih ringkas... :D

  14. Caton

    4 Des 2017 Terverifikasi Indonesia + 11.512 Poin
    Di sunting 10 bulan yang lalu oleh Caton

    Fungsi SEARCH yang kita gunakan pada formula akan mencari apakah setiap nilai yang dihasilkan oleh fungsi LEFT ada di dalam teks daftar kode prefiks sesuai provider yang kita rujuk. Apabila kode prefiks ditemukan, maka fungsi SEARCH akan mengembalikan nilai berupa posisi awal teks yang dicari, jika tidak maka akan menghasilkan nilai kesalahan #VALUE! Pada dasarnya, kita tidak butuh nilai posisi awal teks tersebut. Kita hanya ingin tahu, apakah nilai kode prefiks yang diambil fungsi LEFT ada pada daftar kode prefiks dari povider yang kita rujuk. Oleh karena itu, triknya kita gunakan fungsi ISNUMBER untuk mendapatkan hasil apakah fungsi SEARCH menghasilkan nilai atau tidak.

    Fungsi ISNUMBER sendiri akan menghasilkan nilai TRUE atau FALSE, yang tidak dapat dihitung secara langsung. Agar dapat dihitung, maka kita kita paksa agar nilai yang dihasilkan fungsi ISNUMBER menjadi numerik dengan menambahkan notasi *1. Dengan demikian, TRUE*1 = 1 dan FALSE*1=0. Nah, sejauh ini kita sudah akan mendapatkan senarai (Array) nilai berupa 1 jika kode prefiks ditemukan atau 0 jika kode prefiks tidak ada di dalam daftar.

    Fungsi SUMPRODUCT merupakan proses akhir yang akan kita gunakan untuk menjumlahkan senarai nilai yang dihasilkan oleh fungsi ISNUMBER. Fungsi ini akan menghasilkan satu nilai saja yang merupakan hasil akhir proses kalkulasi... ;)

    Demikian penjelasan ringkas proses dari formula yang digunakan... Sedangkan untuk kasus terakhir yang mas/mbak @Mardana temui, mungkin karena formulanya menggunakan konsep proses pertama dari penjelasan di atas, sedangkan saya menggunakan konsep proses yang kedua. Lihat perbedaan formulanya. Konsep pertama:

    =SUMPRODUCT((ISNUMBER(SEARCH(LEFT($A$4:$A$24;3);$L$7)))*1)

    Sedangkan konsep kedua:

    =SUMPRODUCT(NOT(ISNUMBER(SEARCH(LEFT($A$4:$A$24;3);$L$7)))*1)

    Terlihat perbedaan pada penggunaan fungsi NOT. Jadi coba diperiksa kembali formula yang digunakan (gunakan salah satu konsep/proses saja) atau baca kembali penjelasan tahapan proses di atas agar dapat dilacak masalahanya. Demikian yang dapat saya jelaskan, mudah-mudahan dapat membantu mas/mbak @Mardana menyusun sebuah formula atau melacak kesalahan-kesalahan formula... ;)

  15. Caton

    4 Des 2017 Terverifikasi Indonesia + 11.512 Poin

    Satu hal lagi... dari gambar terakhir yang mas/mbak @Mardana lampirkan, rujukan untuk argumen Find In pada fungsi SEARCH tidak atau jangan berupa range (dalam hal ini, pada gambar terlihat rujukannya ke range L2:L6). Argumen tersebut harus merujuk ke salah satu sel saja, misalkan untuk provider Telkomsel, merujuk ke L2, provider Indosat merujuk ke L3 dan demikian dengan yang lainnya. Sehingga formula untuk mencari data provider Telkomsel (sel E4) adalah:

    =SUMPRODUCT((ISNUMBER(SEARCH(LEFT($A$4:$A$24;3);$L$2)))*1)

    Sedangkan formula untuk mencari data provider Indosat (sel F4) adalah:

    =SUMPRODUCT((ISNUMBER(SEARCH(LEFT($A$4:$A$24;3);$L$3)))*1)

    Demikian dengan yang lainnya... ;)

  16. Sungguh luar biasa. Penjelasannya benar2 memberikan dan membuka pemahaman saya. Gang masih newbie ini. Bahkan ilmunya melebihi expectasy saya. Sekali lagi terimakasih buat mas Caton. Yang telah dengan sabar dan gamblang memberikan penjelasannya. Terimakasih atas bantuannya selama ini. Sukses selalu mas Caton.

  17. Caton

    4 Des 2017 Terverifikasi Indonesia + 11.512 Poin

    Sama-sama mas/mbak @Mardana ... maaf, saya tidak tahu harus memanggil mas atau mbak... :) Saya hanya mencoba membantu dengan apa yang saya ketahui. Semoga bermanfaat. Dan silahkan diskusikan kembali jika ada masalah atau kendala... ;)

 

atau Mendaftar untuk ikut berdiskusi!