Elektroda.pl
Elektroda.pl
X
Proszę, dodaj wyjątek www.elektroda.pl do Adblock.
Dzięki temu, że oglądasz reklamy, wspierasz portal i użytkowników.

Excel dane losowe generowanie kodów

simon000 12 Cze 2011 10:20 13796 6
  • #1 12 Cze 2011 10:20
    simon000
    Poziom 2  

    Mam pytanie dotyczące liczb losowych w excel'u.

    Mianowicie, chciałbym wygenerować losowe "kody promocyjne", wiem jak je później zapisać, wiem też, że muszę użyć funkcji związanej z randomizacją. Chciałbym jednak do kodów dodać litery i chciałbym żeby kody miały tą samą długość np. ciąg 7 znaków.

    Jak powinna wyglądać funkcja tego typu?

    2 6
  • #2 12 Cze 2011 10:52
    xE5150
    Poziom 25  

    Witam.

    Ustal jak ma dokładnie wyglądać Twój kod.

    Ile ma mieć cyfr oraz ile liter i w jakiej kolejności.

    Losowanie cyfr standardowo, losowanie liter:

    1. losujesz cyfrę (przedział, w zależności od tego, ile liter z alfabetu wchodzi w grę).
    2. ustalasz jakiej literze odpowiada twoja cyfra.
    3. zwracasz odpowiednią literę.

    Coś takiego powinno wystarczyć.

    Oczywiście będziesz korzystać ze zła koniecznego, czyli VBA.

    Pozdrawiam.

    1
  • #3 12 Cze 2011 10:57
    adamas_nt
    Moderator Programowanie

    Jeśli chcesz wygenerować kody i wydrukować arkusz, to funkcja LOS() w kolumnie pomocniczej i w kolumnie wynikowej Np PODAJ.POZYCJĘ i jako argument podstawiasz MIN.K(zakres;WIERSZ()), aby wykluczyć powtórzenia. Ale: każde przeliczenie arkusza zmieni wyniki wszystkich komórek. Jeśli miałoby zostać to na stałe w arkuszu musiałbyś wyłączyć automatyczne przeliczanie i przejść na ręczne, lub wykorzystać funkcję Rnd VBA.

    0
  • #4 14 Cze 2011 10:18
    marek003
    Poziom 40  

    Ponieważ miałem chwilkę czasu pozwoliłem sobie zamieścić przykład jednego z rozwiązań w dwóch wariantach (z dowolnym rodzajem znaków w kodzie i z narzuconym rodzajem znaku w kodzie). Oczywiście konfiguracji może być wiele. [obok jest 100 kodów w pierwszej wersji i 100 w drugiej]

    xE5150 napisał:
    Oczywiście będziesz korzystać ze zła koniecznego, czyli VBA.


    Mój przykład nie korzysta z VBA ale wyłączyłem przeliczanie arkusza. (w związku z tym o czym wspomniał kolega adamas_nt)

    Aby wygenerować nowy kod (czy zaktualizować dane) należy nacisnąc F9.

    To tylko przykład.
    Jeżeli kody byłyby zbierane (zbiór np. stu kodów) to tak jak wspomniał kolega adamas_nt, dla pewności wybrany zbiór kodów trzeba byłoby "przetestować"/"przerzucić" by ewentualnie nie zdarzyły się powtórzenia. Niestety przy literach w kodzie funkcja max.k() czy min.k() się nie sprawdzi. Poza tym nawet gdyby nie było liter sama funkcja max.k() czy min.k() nie wyeliminuje powtórzeń a jedynie ustawi je pod sobą.
    Trzeba by było ją bardziej rozbudować.
    Ale najprościej poprzez filtr zaawansowany wybrany zbiór skopiować w inne miejsce z zaznaczeniem opcji "tylko unikatowe rekordy.


    Dodane po jakimś czasie
    -----------------------------------------------------------
    Dodałem "naoczne" sprawdzanie dubli kodów (dla celów pokazowych wymusiłem duble bo jakoś nie chciały mi się trafić)

    Dodane jeszcze po jakimś czasie
    -----------------------------------------------------------
    By automatycznie odrzucał duble zmieniłem trochę formułę zmieniając ją przy okazji na pomocniczą. kody2.xls (Oczywiście znów wymusiłem duble bo nie mogłem trafić :) )

    Nadmiar o 20 kodów myślę że wystarczy dla ewentualnych dubli które są zastępowane innymi kodami.

    0
  • #5 14 Cze 2011 15:26
    simon000
    Poziom 2  

    Witam poniżej zamieszczam odpowiedź skonstruowaną przez właściciela serwisu:

    http://office.info.pl/

    Przetestowałem wszystko działa jak należy, jeśli ktoś nie zna VBA jest to idealny sposób i bardzo prosty w wykonaniu.

    (...)"można to zrobić na dwa sposoby:

    1) zawsze za pomocą VBA - czyli oprogramować Makro;
    2) "sposobem/sztuczką" :) i ten przedstawię poniżej.

    Ad.2. Zakładam, że ma Pan minimum 2007 wersję Excela (w wersjach poprzednich nie zadziała jedna funkcja i wtedy trzeba by trochę przerobić formułę). Sposób ten pozwala na losowanie 7 znaków z, ustalonej przez nas ręcznie, tablicy znaków. To oznacza, że możemy zdefiniować ją jako same znaki od A-Z lub od A-Z i cyfry, etc.

    Żeby skonstruować taką formułę, która wyświetli nam zawsze 7 znaków losowych należy:

    1) zdefiniować tablicę znaków do losowania (zacznijmy od komórki A1 i każdy znak wpisujmy "pod sobą" czyli w A2, A3, ...), dla przykładu wpiszmy:

    A
    B
    C
    D
    E
    F
    G
    H
    1
    2
    3
    4
    5

    2) nazwijmy ten zakres jako: znaki (możemy to szybko zrobić klikając w Polu nazwy, tj. w polu po lewej stronie pola formuły) - klikamy to pole i wpisujemy: znaki, a następnie zatwierdzamy klawiszem Enter

    3) zaznaczamy WSZYSTKIE komórki w których chcemy mieć losowane zestawy znaków

    4) po zaznaczeniu komórek klikamy w pole formuły (tam gdzie wpisujemy formuły), a następnie kopiujemy poniższą formułę:

    =INDEKS(znaki;RANDBETWEEN(1;ILE.WIERSZY(znaki)))&INDEKS(znaki;RANDBETWEEN(1;ILE.WIERSZY(znaki)))&INDEKS(znaki;RANDBETWEEN(1;ILE.WIERSZY(znaki)))&INDEKS(znaki;RANDBETWEEN(1;ILE.WIERSZY(znaki)))&INDEKS(znaki;RANDBETWEEN(1;ILE.WIERSZY(znaki)))&INDEKS(znaki;RANDBETWEEN(1;ILE.WIERSZY(znaki)))&INDEKS(znaki;RANDBETWEEN(1;ILE.WIERSZY(znaki)))

    5) zatwierdzamy: Ctrl + Enter (WAŻNE żeby przed przyciśnięciem klawisza Enter przytrzymać klawisz Ctrl na klawiaturze i dodatkowo wcisnąć Enter)."(...)

    0
  • #6 15 Cze 2011 10:58
    marek003
    Poziom 40  

    Dla samej zasady

    simon000 napisał:
    5) zatwierdzamy: Ctrl + Enter (WAŻNE żeby przed przyciśnięciem klawisza Enter przytrzymać klawisz Ctrl na klawiaturze i dodatkowo wcisnąć Enter)."(...)

    WCALE NIE :)
    Nie trzeba w tym przypadku wymuszać funkcji tablicowej. Formuła indeks() jak i ile.wierszy() z założenia pracuje na tablicach więc zwykłe enter jak najbardziej wystarczy. Po co meczyc excela :)


    A wracając do wątku
    Przecież ten sam sposób postępowania proponowano ci wyżej.
    A ja jeszcze dodałem przykład - o ewentualnym zabezpieczeniu przed powtórzeniami nie wspomnę :).
    A w drugim przypadku nawet z założeniem że np trzy pierwsze znaki kodu mają być tylko literowe.
    Przeraziłem ilością rozwiązań?

    Wracając do tego co zamieściłeś z innego forum.
    Jeżeli to ma działać na wszystkich wersjach excela (poniżej 2007 też) to powyższa funkcja powinna wyglądać tak:

    =INDEKS(znaki;ZAOKR.DO.CAŁK(LOS()*ILE.WIERSZY(znaki))+1)&INDEKS(znaki;ZAOKR.DO.CAŁK(LOS()*ILE.WIERSZY(znaki))+1)&INDEKS(znaki;ZAOKR.DO.CAŁK(LOS()*ILE.WIERSZY(znaki))+1)&INDEKS(znaki;ZAOKR.DO.CAŁK(LOS()*ILE.WIERSZY(znaki))+1)&INDEKS(znaki;ZAOKR.DO.CAŁK(LOS()*ILE.WIERSZY(znaki))+1)&INDEKS(znaki;ZAOKR.DO.CAŁK(LOS()*ILE.WIERSZY(znaki))+1)&INDEKS(znaki;ZAOKR.DO.CAŁK(LOS()*ILE.WIERSZY(znaki))+1)

    0
  • #7 08 Lut 2013 12:15
    vidom2
    Poziom 1  

    Komurka: Wartość

    K1: QWER1234-TYUIOP5678-ASDFGH90123-JKLZXC4567-VBNM8901

    I1: =FRAGMENT.TEKSTU($K$1;LICZBA.CAŁK(LOS()*(DŁ($K$1)-1)+1);1)
    H1: =FRAGMENT.TEKSTU($K$1;LICZBA.CAŁK(LOS()*(DŁ($K$1)-1)+1);1)
    G1: =FRAGMENT.TEKSTU($K$1;LICZBA.CAŁK(LOS()*(DŁ($K$1)-1)+1);1)
    F1: =FRAGMENT.TEKSTU($K$1;LICZBA.CAŁK(LOS()*(DŁ($K$1)-1)+1);1)
    E1: =FRAGMENT.TEKSTU($K$1;LICZBA.CAŁK(LOS()*(DŁ($K$1)-1)+1);1)
    D1: =FRAGMENT.TEKSTU($K$1;LICZBA.CAŁK(LOS()*(DŁ($K$1)-1)+1);1)
    C1: =FRAGMENT.TEKSTU($K$1;LICZBA.CAŁK(LOS()*(DŁ($K$1)-1)+1);1)
    B1: =FRAGMENT.TEKSTU($K$1;LICZBA.CAŁK(LOS()*(DŁ($K$1)-1)+1);1)

    A1: =ZŁĄCZ.TEKSTY(B1;C1;D1;E1;F1;G1;H1;I1)

    Jak przeciągniemy kolumny A1:I1 w dół, to w kolumnie A nam wyjdzie coś takiego:
    -MM0EXC7
    6OG12--6
    66X0XNX9
    35OJM3X8
    F7-5F80L
    -7P882Q3
    -XK-6MO5
    C9603P2F
    JPH568IO

    Rzeczywiście zestaw znaków w komórce K1 każdy może sam sobie wymyślić. Aby np. częściej się spotykała literka A, należy je wpisać w tym ciągu znaków kilka razy.

    Excel dane losowe generowanie kodów

    2