GoldenLine
Zaloguj się
Piotr P.

Piotr P. "Sukces to drabina,
po której nie sposób
wspiąć się z ręk...

Temat: Wyszukiwanie początku numerów telefonu i przypasowywanie...

Witam wszystkich,

miałbym prośbę o podpowiedź, bo siedzę nad problemem, który raczej nie da się załatwić jedną prostą formułą.
Otóż ... mam Arkusz1 z cenami, Arkusz2 z bilingiem telefonicznym i teraz musiałbym zrobić Arkusz3 z wyliczeniami kosztów za każde połączenie. Problem polega na tym, że cennik w Arkuszu1 posiada następujące rozbicie cenowe (poniższe przykłady mają numerację i ceny nie związane z rzeczywistością, ale nie o to chodzi):
ARKUSZ1
Kraj | Nr kierunkowy | Cena (za 1min.)
Holandia | 49 | 1,20zł
Holandia | 495983 | 1,00zł
Holandia | 49456 | 1,30zł
Niemcy | 53 | 0,80zł
Niemcy | 531276 | 0,75zł

Zaś sam biling w Arkuszu2, wygląda mniej więcej tak:
ARKUSZ2
Nr telefonu | Czas rozmowy (w min.)
4959830987 | 3
4940946548 | 5
4945695038 | 2
5312762075 | 6

I teraz w Arkuszu3 musiałbym wstawić formułę, która wyszukiwałaby odpowiednie numery kierunkowe przypasowując je do nr telefonu, a jeśli zgadzają się to wówczas przelicza ceną x czas rozmowy.
Funkcja WYSZUKAJ.PIONOWO częściowo rozwiązywałaby sprawę, ale problem mam z:
- wyfiltrowaniem tylko początku numeru, aby móc go przypasować do Nr kierunkowego (szczególnie, że numery kierunkowe często są różnej długości)
- jeśli nawet uda mi się zrobić taki filtr jak powyżej, to jeśli numer kierunkowy nie zostanie znaleziony, wówczas funkcja powinna wskazywać na cenę z krótkiego nr kierunkowego np. 49 lub 53

Przykładowe wyliczenia dla Arkusza3
ARKUSZ 3
Nr telefonu | Czas rozmowy (w min.) | Koszt
4959830987 | 3 | 3zł (3min. x 1zł, bo nr kier. to 495983)
4940946548 | 5 | 6zł (5min. x 1,20zł, bo nr kier. to 49)
4945695038 | 2 | 2,60zł (2min. x 1,30zł, bo nr kier. to 49456)
5312762075 | 6 | 4,50zł (6min. x 0,75zł, bo nr kier. to 531276)

Zatem jak powinna wyglądać formuła dla każdego wiersza w kolumnie Koszt Arkusza3?

Dodam również, że cennik (Arkusz1) to przynajmniej kilkaset pozycji, a biling (Arkusz2) to zazwyczaj ok. 100 pozycji.
Link do wypowiedziLink
Oskar Shon

Oskar Shon Dodatki do Office
www.VBATools.pl

Temat: Wyszukiwanie początku numerów telefonu i przypasowywanie...

Coś mi się wydaje że należało by to oprogramować.
Link do wypowiedziLink
Andy L.

Andy L. ITM, VUB

Temat: Wyszukiwanie początku numerów telefonu i przypasowywanie...

Jeśli brak numeru kierunkowego 123456 to czy od razu ma szukać 12 czy kolejno 12345, 1234, 123 ?Ten post został edytowany przez Autora dnia 01.12.16 o godzinie 21:23
Link do wypowiedziLink
Piotr P.

Piotr P. "Sukces to drabina,
po której nie sposób
wspiąć się z ręk...

Temat: Wyszukiwanie początku numerów telefonu i przypasowywanie...

Andy L.:
Jeśli brak numeru kierunkowego 123456 to czy od razu ma szukać 12 czy kolejno 12345, 1234, 123 ?

Wydaje mi się, że logika mechanizmu powinna być taka, że wyszukiwarka najpierw bierze pod uwagę dwie pierwsze cyfry z numeru telefonu i znajduje je w numerach kierunkowych kolejno powiększając pulę, a jeśli nie znajdzie to wskazuje tylko numer dwucyfrowy.
Mając na uwadze powyższy przykład:
Numer telefonu to 123456789
Dwie pierwsze cyfry to 12, zatem wyszukiwanie jest po numerach kierunkowych zaczynających się od 12:
12
1234
12765
12863
Oznacza to, że wskazanie będzie na numer kierunkowy 1234.

Dodam jednak, że zdarzają się też numery kierunkowe trzycyfrowe, np. Kuwait ma 965, a Jordan 962, więc sprawa się komplikuje. Nie mniej można by wykorzystać logikę mechanizmu podaną przeze mnie powyżej i jeśli np. w przypadku Kuwaitu i Jordanu, mechanizm nie znajdzie dwucyfrowego numeru kierunkowego 96 (bo taki nie istnieje), to wówczas szukany jest numer trzycyfrowy brany z pierwszych trzech cyfr numeru telefonu. A dalej logika mechanizmu pozostaje zgodna z przykładem podanym przeze mnie powyżej (na liczbach 123456...).
Link do wypowiedziLink
Oskar Shon

Oskar Shon Dodatki do Office
www.VBATools.pl

Temat: Wyszukiwanie początku numerów telefonu i przypasowywanie...

Zleć wykonanie takiej roboty i już, bo mechanizm jest do napisania.
Jest to cel biznesowy, więc nie powinieneś mieć oporów.
Link do wypowiedziLink
Andy L.

Andy L. ITM, VUB

Temat: Wyszukiwanie początku numerów telefonu i przypasowywanie...

Popieram Oskara
(nie komentuje logiki mechanizmu :P )
Link do wypowiedziLink
Dawid Marcisz

Dawid Marcisz Kierownik zmiany /
Kierownik produkcji,
TEVA Operations P...

Temat: Wyszukiwanie początku numerów telefonu i przypasowywanie...

Banalnie prosta sprawa tylko trzeba umiejętnie wymyślić logikę mechanizmow) JEŻELI LEWY TO WYSZUKAJ PIONOWO TABELA 1 CENA NIE TO LEWY WYSZUKAJ PIONOWO TABELA DWA CENA itd. ale cennik powinien byc troche przebudowany by sprawe ulatwic lub zautymatyzowac cennik pod tabela 2. Potem tylko minuty z bilingu razy cena i gotowe :)Ten post został edytowany przez Autora dnia 02.12.16 o godzinie 18:41
Link do wypowiedziLink
Oskar Shon

Oskar Shon Dodatki do Office
www.VBATools.pl

Temat: Wyszukiwanie początku numerów telefonu i przypasowywanie...

I ile tych jeżeli(lewy(wyszukaj.pionowo())) chcesz osadzić w komórce, chyba że tablicówka, której sobie na tą chwilę nie wyobrażam?
Chciałem zaznaczyć że powinno się wychodzić od rachunku, a nie od cennika.
Rachunek ma, jak czytaliśmy, kilkaset pozycji, a cennik tylko/aż 100 wycen.

Zacząłem myśleć w ten sposób, ale wymaga to pracy i wiedzy, więc nie wyobrażam sobie tego bez zlecenia.


Obrazek


-Deklaracja pozycji cennika do pamięci dodatku, z możliwością dowolnego dodawania nowych wycen ręcznie lub przez pobieranie zdefiniowanych obszarów.
-Określenie stałej stawki dla rekordów pozycji nie objętych wyceną z listy
-Zastosowanie w arkuszu poprzez uzyskanie wyniku obliczonego kosztu formułą UDF, a więc możliwość pomnożenia przez ilość.
-Dodatkowo wyciąganie formułą nazwy zdefiniowanego koszu.
Link do wypowiedziLink
Piotr P.

Piotr P. "Sukces to drabina,
po której nie sposób
wspiąć się z ręk...

Temat: Wyszukiwanie początku numerów telefonu i przypasowywanie...

Oskar, chyba faktycznie będę musiał to zlecić, bo sam tego nie ogarnę.
W poniedziałek mam spotkanie w tej sprawie z kontrahentem i najwyżej się odezwę.
Link do wypowiedziLink
Dawid Marcisz

Dawid Marcisz Kierownik zmiany /
Kierownik produkcji,
TEVA Operations P...

Temat: Wyszukiwanie początku numerów telefonu i przypasowywanie...

Twoja formuła :)

=JEŻELI.BŁĄD(JEŻELI.BŁĄD(JEŻELI.BŁĄD(JEŻELI.BŁĄD(JEŻELI.BŁĄD(WYSZUKAJ.PIONOWO(WARTOŚĆ((LEWY(A2;6)));CENNIK!B2:C6;2;0);WYSZUKAJ.PIONOWO(WARTOŚĆ((LEWY(A2;5)));CENNIK!B2:C6;2;0));WYSZUKAJ.PIONOWO(WARTOŚĆ((LEWY(A2;4)));CENNIK!B2:C6;2;0));WYSZUKAJ.PIONOWO(WARTOŚĆ((LEWY(A2;3)));CENNIK!B2:C6;2;0));WYSZUKAJ.PIONOWO(WARTOŚĆ((LEWY(A2;2)));CENNIK!B2:C6;2;0));"brak")

Formuła szuka do pierwszego poprawnego wyniku zaczynając o "nr kierunkowego" sześciocyfrowego, jeśli takowego brak szuka 5-cio cyfrowego i analogicznie w dół. Jeśli nie ma także 2-dwu cyfrowego kierunkowego to wyświetla komunikat "brak".

Podaj mail to podeślę rozwiązanie w pliku .xlsx
Może rozwiązanie i prymitywne, ma ograniczenia w stosowaniu i trzeba byłoby dopracować pod względem automatyki to zupełnie zapewnia funkcjonalność której potrzebowałeś i jest darmowe i nie potrzebujesz na to zlecenia :)Ten post został edytowany przez Autora dnia 03.12.16 o godzinie 15:34
Link do wypowiedziLink
Andy L.

Andy L. ITM, VUB

Temat: Wyszukiwanie początku numerów telefonu i przypasowywanie...

w Excelu EN znakiem oddzielającym argumenty jest przecinek a nie średnik

a co jeśli:
kierunkowy:
543
54
numer:
5439999999 - kier. jest 543
5437777778 - kier. powinien być 54
(są to dwa numery w różnych lokalizacjach)Ten post został edytowany przez Autora dnia 03.12.16 o godzinie 13:36
Link do wypowiedziLink
Dawid Marcisz

Dawid Marcisz Kierownik zmiany /
Kierownik produkcji,
TEVA Operations P...

Temat: Wyszukiwanie początku numerów telefonu i przypasowywanie...

Sytuacja którą opisujesz nie ma miejsca w przypadku nr tel międzynarodowych, bo jeśli nr kierunkowy jest dwucyfrowy to żaden trzycyfrowy i więcej nie zaczyna się od tych cyfr.
http://www.orange.pl/portal/map/map/article?id=2707635

Stąd moje rozwiązanie banalne w swojej prostocie daje potrzebną funkcjonalność. Oczywiście będą sytuacje w których to nie zadziała ale na tą chwilę nie zawracałem sobie głowy bo nie jest to zlecenie tylko pomoc typu non profit, jak to na forach bywa.

Co Excela EN nie używam go, ale rozwiązanie napisane na wersji polskiej, otwierane na wersji angielskiej daje dokładnie taką formułę jak skopiowałem i działa :) więc nie neguje bo nie wiem, ale moja formuła też działa. Poza tym nie zajmuję się excelem zawodowo ale bardzo ułatwia mi prace, przez automatyzacje niektórych przeliczeń, procesów czy raportów.Ten post został edytowany przez Autora dnia 03.12.16 o godzinie 13:59
Link do wypowiedziLink
Andy L.

Andy L. ITM, VUB

Temat: Wyszukiwanie początku numerów telefonu i przypasowywanie...

formuła PL w wersji EN Excela - nie działa (oczywista oczywistość)
formuła EN w wersji EN Excela - nie działa
(nie stosując żandnych poprawek)

to tak poza konkursem :P

EDIT:
Żeby wpisać formułę PL a następnie otworzyć w wersji EN Excela trzeba posiadać dwie wersje językowe. Mało przydatne rozwiązanie.Ten post został edytowany przez Autora dnia 03.12.16 o godzinie 14:24
Link do wypowiedziLink
Dawid Marcisz

Dawid Marcisz Kierownik zmiany /
Kierownik produkcji,
TEVA Operations P...

Temat: Wyszukiwanie początku numerów telefonu i przypasowywanie...

Mam obie widocznie dlatego tak się wyświetla :) ale otwierana na kompie żony z wersją tylko angielska też działa bo automatycznie zamienia formuly polskie na angielskue i odwrotnie dlatego wydawało mi się ze będzie działać tu i tu, ale wklejona do złej wersji językowej faktycznie nie zadziała.Ten post został edytowany przez Autora dnia 03.12.16 o godzinie 14:31
Link do wypowiedziLink
Andy L.

Andy L. ITM, VUB

Temat: Wyszukiwanie początku numerów telefonu i przypasowywanie...

Nie chodzi o to, że Excel "tłumaczy" automatycznie formuły czy funkcje tylko o to, że podałeś w tekście formuły, które NIE zadziałają w wersji EN.
Nie znam się na Excelu więc kopiuję w dobrej wierze, że formuła EN jest poprawna i .... co? d...
Gdybyś dał link do pliku gdzie będzie formuła to wtedy ok. A tak niestety jest to bezużyteczne (lub ograniczone tylko do wersji PL)
Więc albo poprawka formuły w wersji EN w poście albo lepiej nie publikować błędnej wersji formuły.
To tak dla zasady poprawności zapisu.
Link do wypowiedziLink
Dawid Marcisz

Dawid Marcisz Kierownik zmiany /
Kierownik produkcji,
TEVA Operations P...

Temat: Wyszukiwanie początku numerów telefonu i przypasowywanie...

Andy L.: Masz rację i teraz rozumiem o co chodzi i intencje, usunąłem :)
Link do wypowiedziLink
Oskar Shon

Oskar Shon Dodatki do Office
www.VBATools.pl

Temat: Wyszukiwanie początku numerów telefonu i przypasowywanie...

No dobra. Rozwiązanie napisane: http://vbatools.pl/koszty-telefoniczne/

Plusy to:
- Baza niezależnie przekazywana do narzędzia (bez dubli) z zakresu jak i pojedynczo (z reki),
- Możliwość usunięcia zdefiniowanych wszystkich lub usunięcia jedynie zaznaczonych pozycji,
- Zwrot danych obliczeniowych jak i opisu przypisanego do kosztu prostą formuła UDF =tel_koszt() oraz =tel_opis(), gdzie na tym pierwszym można wykonywać dalsze obliczenia,
- Zwrot danych jako wartość (nie trzeba mieć bazy pod ręką, ani nie trzeba jej przekazywać nikomu).

Piotrze jeśli interesuje twoją firmę takie narzędzie to zapraszam.
Kontakt przez stronę (zakładka kontakt).
Link do wypowiedziLink
Oskar Shon

Oskar Shon Dodatki do Office
www.VBATools.pl

Temat: Wyszukiwanie początku numerów telefonu i przypasowywanie...

Piotr P.:
W poniedziałek mam spotkanie w tej sprawie z kontrahentem i najwyżej się odezwę.

No to czekam na kontakt.
Np mailem, który podaje przez stronę.
Link do wypowiedziLink
Piotr P.

Piotr P. "Sukces to drabina,
po której nie sposób
wspiąć się z ręk...

Temat: Wyszukiwanie początku numerów telefonu i przypasowywanie...

Oskar S.:
Piotr P.:
W poniedziałek mam spotkanie w tej sprawie z kontrahentem i najwyżej się odezwę.

No to czekam na kontakt.
Np mailem, który podaje przez stronę.

Na razie rozpatruję wersję Dawida (swoją drogą Dawid - jeszcze raz wielkie dzięki! :)) . Jak będę potrzebował czegoś więcej, to się odezwę.
Link do wypowiedziLink
Andy L.

Andy L. ITM, VUB

Temat: Wyszukiwanie początku numerów telefonu i przypasowywanie...

Pozostaje problem z krajami o tym samym numerze kierunkowym:

Kanada / USA - 1
Kazachstan / Rosja - 7
Hiszpania / Wyspy Kanaryjski - 34
Kongo / Nigeria - 234

Jeżeli jest ta sama stawka to problemu nie ma, jeśli nie to zaczynają się schody.Ten post został edytowany przez Autora dnia 07.12.16 o godzinie 13:02
Link do wypowiedziLink
« Wróć do tematów
Odpowiedz

Podobne tematy


Następna dyskusja:

Wyszukiwanie unikalnych war...




Wyślij zaproszenie do
Anuluj