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.

vba excel - zaznaczanie zakresu liczb z odpowidniej grupy

krzysieks100 29 Sie 2012 20:39 3909 16
  • #1 29 Sie 2012 20:39
    krzysieks100
    Poziom 7  

    witam,
    mam problem z makrem. Męczę się meczę i nic :///
    Chcę, żeby makro zaznaczało kolumnę, sortowało ją rosnąco Np:
    a
    a
    a
    b
    b
    b
    b
    c
    c
    ...
    a następnie podawało mi zapisywało mi zmienne w ten sposób, żeby każda ze zmiennych definiowała pierwszy wiersz, w którym jest "wartość", a następnie ostatni wiersz, który zawiera wartość "a" i tak ze wszystkimi wartościami po kolei.
    Potem chciałbym użyć tych zmiennych do budowania innych funkcji.

    Mam nadzieję, że to da się zrobić.

    pozdrawiam
    krzysieks100

    W przyszłości proszę przed wysłaniem postu korzystać z przycisku Pisownia. Błędy poprawiłem.[adamas_nt]

    0 16
  • #2 29 Sie 2012 22:09
    adamas_nt
    Moderator Programowanie

    krzysieks100 napisał:
    Mam nadzieję, że to da się zrobić.
    Tak.
    krzysieks100 napisał:
    Męczę się meczę i nic :///
    Proszę przedstawić to, co kolega "wymęczył".

    0
  • #3 30 Sie 2012 21:21
    krzysieks100
    Poziom 7  

    W załączniku przesyłam plik, docelowo tak chciałbym, żeby to działało, ale szczerze się przyznam że nie wiem jak tego dokonać.

    W kolumnie, makro ma filtrować po wartości, następnie z kolumny b zaznaczać to co udało się wyfiltrować a w komórce C26, liczyć percentyl z tego zakresu. Następnie odfiltrowuje kolumnę a i powtarza tą samą czynność z wartością d, a docelowo wszystkie wartości jakie są w kolumnie a.

    0
  • #4 03 Wrz 2012 19:24
    krzysieks100
    Poziom 7  

    Witam,

    Bardzo proszę o pomoc w rozwiązaniu powyższego problemu, może ktoś na podobny problem, albo chociaż jakąś myśl lub schemacik do podrzucenia.
    Z góry dziękuję.

    Pozdrawiam,
    krzysieks100

    0
  • #6 03 Wrz 2012 20:16
    krzysieks100
    Poziom 7  

    W sumie to masz rację, nie będzie to robione za jednym kliknięciem przycisku, ale i tak super ułatwi sprawę, pewnie zaoszczędzę w ten sposób z pół dnia.
    Dzięki za pomoc

    0
  • #7 06 Wrz 2012 19:30
    krzysieks100
    Poziom 7  

    Niestety, chciałbym wrócić do tematu. Oczywiście rozwiązanie ze stosowaniem formuł tablicowych jest całkiem OK i w sumie pomógł mi nie tylko w tej sprawie.
    To jednak pojawia się kolejny problem.

    Jak jak napisałem w pierwszym poście, chciałbym aby kolumna z danymi "aa" została posortowana rosnąco, a następnie chciałbym żeby z kolumny "bb" dla wszystkich wartość, które w kolumnie "aa" odpowiadają na przykład wartości "s" utworzyły zakres, pomoże to nie tylko w ustaleniu wartości percentyla, ale też później (mam taką nadzieję) w ustawieniu lampek dla tego zakresu. Pisząc "lampek" mam na myśli formatowanie warunkowe.

    Jeżeli ktoś jest w stanie podrzucić jakiś pomysł to bardzo proszę o pomoc.

    pozdrawiam,
    krzysieks100

    0
  • #8 08 Wrz 2012 10:16
    krzysieks100
    Poziom 7  

    Jest w stanie ktoś pomóc w tej sprawie, może jakiś pomysł?

    0
  • #9 08 Wrz 2012 16:24
    marcinj12
    Poziom 40  

    Nie wiem czy to dokładnie o to chodzi, ale makrem można to załatwić w dwóch krokach:
    1. sortowanie,
    2. przeszukanie zakresu w celu ustalenia min i max.
    Oczywiście do ustalenia min i max sortowanie jest zbędne, ale piszesz że też ma być, więc jest...

    Kod: vb
    Zaloguj się, aby zobaczyć kod


    Myślę że za pomocą formuł ustalenie wartości min i max też jest do osiągnięcia:najpierw do kolumny obok za pomocą funkcji JEŻELI() dla kolumny B przenosisz z kolumny C te liczby, które spełniają zadany warunek, a potem na tej kolumnie stosujesz funkcje MIN() i MAX().

    0
  • #10 08 Wrz 2012 19:40
    krzysieks100
    Poziom 7  

    Wiesz, chyba źle to wytłumaczyłem, przepraszam tylko zamieszanie wprowadziłem, ale spróbuje jeszcze raz, bo nie byłbym sobą gdybym nie spróbował jeszcze raz, załączam też nowy plik, może tym razem będzie mi łatwiej wyjaśnić.

    W pierwszym kroku chciałbym posortować kolumnie "aa" po wartościach od A do Z (i to jak widać nie jest trudne)

    Potem chciałbym, żeby dla wartości z kolumny "bb" odpowiadającym wartości "a" z kolumny "aa" był liczony percentyl np, 0,6 i wstawiany do tabeli z percentylami powyżej. I tutaj pojawia się problem, za każdym razem, kiedy będę chciał użyć tego makra, ilość danych w kolumnach "aa" oraz "bb" będzie się za każdym razem zmieniała (za każdym razem będę coś tam doklejał). jedyne co się nie będzie zmieniało to ilość zmiennych w kolumnie "aa", tzn a, s, d, g, j, h, k, l.
    Docelowo percentyle są liczone dla każdej zmiennej w kolumnie "aa".

    I to chyba na tyle, mam nadzieje że to się da jakoś ustawić, bardzo też proszę o pomoc, bo za każdym razem się męczę z tym i generuje dużo błędów, których pewnie można by było uniknąć za pomocą makra.

    Będę wdzięczny za pomoc.

    0
  • #11 08 Wrz 2012 20:57
    marcinj12
    Poziom 40  

    Jeżeli użyjesz takiego sposobu jak opisałem (ze zmienną ost_wiersz), to zakres będzie się rozszerzał automatycznie... Natomiast nie kojarzę jak się liczy percentyle w takim wypadku, tym bardziej że zmienne w kolumnie aa się powtarzają - jeżeli podasz wzór lub - lepiej - opiszesz algorytm jego liczenia, to może coś się uda zaimplementować...

    0
  • #12 08 Wrz 2012 21:23
    krzysieks100
    Poziom 7  

    w excelu jest na to funkcja, cos takiego =percentyl(zakres;k)

    Percentyl 60 stopnia (0,6), zwróci wartość powyżej której znajdzie się 40% danych z zakresu, oraz poniżej, której znajdzie sie 60% wartości zakresu.
    Tak by wyglądało makro dla tego pliku który wrzuciłem poprzednio, tylko miałoby liczyć dla wszystkich zmiennych w kolumnie "aa", to makro działa ale się wysypuje kiedy dostawiam koleją porcje danych do kolumn.

    Kod: vb
    Zaloguj się, aby zobaczyć kod


    Range("B35").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWorkbook.Worksheets("Arkusz1").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Arkusz1").AutoFilter.Sort.SortFields.Add Key:= _
    Range("B35"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal
    With ActiveWorkbook.Worksheets("Arkusz1").AutoFilter.Sort
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With
    ActiveWindow.SmallScroll Down:=-9
    Range("C15").Select
    ActiveCell.FormulaR1C1 = "=PERCENTILE(R[20]C:R[24]C,R11C3)"
    Range("C16").Select

    Kod: vb
    Zaloguj się, aby zobaczyć kod

    0
  • #13 08 Wrz 2012 21:46
    marcinj12
    Poziom 40  

    OK, widziałem tą funkcję, tylko że ona przyjmuje dwa argumenty: jako tablicę jak rozumiem kolumnę C, a jako k - stopień np. 0,6. A Ty chcesz zrobić tak, żeby - po posortowaniu danych z Twojego ostatniego pliku - dla wartości a z kolumny aa wykonała się funkcja PERCENTYL("C35:C39";0,6), dla wartości s funkcja PERCENTYL("C70:C74";0,6) etc., dobrze to rozumiem??

    I pytanie uściślające - czy te wartości w tabelce B15:B22 są ustalone na stałe, czy będą dochodzić nowe w miarę dopisywania danych? Chodzi o to czy tabelka jest stała, czy będziesz rozszerzał ją ręcznie, czy może ma się ona tworzyć z automatu "analizując" dane z kolumny aa ?

    0
  • #14 08 Wrz 2012 21:57
    krzysieks100
    Poziom 7  

    "OK, widziałem tą funkcję, tylko że ona przyjmuje dwa argumenty: jako tablicę jak rozumiem kolumnę C, a jako k - stopień np. 0,6. A Ty chcesz zrobić tak, żeby - po posortowaniu danych z Twojego ostatniego pliku - dla wartości a z kolumny aa wykonała się funkcja PERCENTYL("C35:C39";0,6), dla wartości s funkcja PERCENTYL("C70:C74";0,6) etc., dobrze to rozumiem?? "

    Tak, tutaj dobrze rozumiesz, tylko ze w miarę dostawiania danych zakresy się będą zmieniały np: z PERCENTYL("C35:C39";0,6), może się zrobić na przykład PERCENTYL("C35:C60";0,6), i odpowiednio dla dalszych zmiennych.

    Co do pytania uściślającego, wartości w "tabelce B15:B22" , będą zawsze tożsame z kolumną "aa", zapewne z biegiem czasu coś się tam zmieni, tzn zostaną dodane nowe dane, ale to już powinny być niewielkie zmiany

    0
  • #15 08 Wrz 2012 22:26
    adamas_nt
    Moderator Programowanie

    Nie wiem dlaczego, ale ciągle mi się wydaje, że makro, a sortowanie to już zupełnie, nie jest potrzebne. Widać czegoś nie rozumiem... Ale może spróbować poskładać zakres w pętli warunkowej i użyć jako argument funkcji? Np

    Kod: vb
    Zaloguj się, aby zobaczyć kod
    Wtedy wartości unikatowe z kolumny B można by zapisać jako Array, podstawiać w dodatkowej, zewnętrznej pętli i liczyć percentyle dla wszystkich po kolei.

    Edit: Lub pobierać z zakresu B15:B22 (post niżej).

    0
  • #16 08 Wrz 2012 22:28
    marcinj12
    Poziom 40  

    Myślę, że kod z załącznika robi to co trzeba.
    Przypatrz się jedynie na miejsca, gdzie definiuje się zakresy: początkowe komórki czy zakres tabeli. W razie rozbudowy - musisz je odpowiednio w kodzie zmodyfikować...

    adamas_nt napisał:
    Nie wiem dlaczego, ale ciągle mi się wydaje, że makro, a sortowanie to już zupełnie, nie jest potrzebne.
    Tak jak ja zrozumiałem autora, to on nie chce policzyć jednego percentyla dla wszystkich danych z całego zakresu z kolumny C, bo ma tam przemieszane ze sobą kilka różnych wartości, a dla każdej z nich chce obliczyć percentyl osobno. W tym celu faktycznie musi je najpierw posortować, a następnie obliczyć funkcję percentyl dla poszczególnych grup, czyli zakres danych dla funkcji będzie jedynie fragmentem danych z kolumny C. :)
    Pewnie dało by się to obejść za pomocą kolumn dodatkowych i funkcji, ale skoro może być makro.. ;P

    0
  • #17 08 Wrz 2012 22:37
    adamas_nt
    Moderator Programowanie

    No tak. To samo osiągniesz funkcją tablicową. Np dla "a"

    Code:
    =PERCENTYL(JEŻELI(B35:B1000="a";C35:C1000);C11)
    (Ctrl+Shift+Enter) Wystarczy wpisać odpowiednio duży zakres w argumentach, no i nie ma potrzeby sortowania.

    vba excel - zaznaczanie zakresu liczb z odpowidniej grupy

    0