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 - zaawansowane sortowanie

stec_piotr 05 Mar 2014 22:42 1722 5
  • #1 05 Mar 2014 22:42
    stec_piotr
    Poziom 7  

    Mam problem z ułożeniem formuły, która układałaby w kolejności pozycje w kolumnie A na podstawie wartości określonych w innych kolumnach (kolumna B i C). Samo sortowanie jest oczywiście banalnie proste :) mi chodzi o coś innego.

    Chcę aby najpierw ułożyć w kolejności wiersze, które zaczynają się od liczby całkowitej 3, później od 2, później od 1 i na końcu aby ułożyć w kolejności wiersze na podstawie wartości po przecinku.

    A więc np w przypadku KW1 aby formuła automatycznie wyfiltrowała nazwy w naspujacej kolejności:
    wiersz 5
    wiersz 6
    wiersz 11
    wiersz 5
    wiersz 1
    wiersz 12
    itd.

    Dla komplikacji dodam, że chciałbym określać liczbę kolejnych wartości do filtrowania, np 10 z KW1 i 15 z KW2.

    plik z wartościami liczbowymi jest w załączniku do posta

    Będę wdzięczny za pomoc!

    0 5
  • #2 06 Mar 2014 01:03
    Maciej Gonet
    Poziom 32  

    Opis mocno zagmatwany, a wygląda na to, że chodzi o uporządkowanie wierszy w kolejności malejącej wg danych w KW1 lub KW2, czy się mylę?
    Można to zrobić formułą, ale nie rozumiem, dlaczego sortowanie nie wchodzi w grę? Czy liczba wierszy do obróbki ma być pobierana z jakiejś komórki? Czy to zawsze będzie od pierwszego wiersza do n-tego, czy od m-tego do n-tego?

    0
  • #3 06 Mar 2014 01:50
    stec_piotr
    Poziom 7  

    wiem, że zagmatwany bardzo:( trudno to wyjaśnić.

    Spróbuję jeszcze raz.

    Nie da się zrobić tego sortowaniem, ponieważ najpierw należy ze zbioru wybrać "wiersze", które maja wartość >= 3, później >=2, ale również z uwzględnieniem wierszy z wartościami powyżej 3 po odjęciu od nich wartości całkowitej = 1. Następnie należy ze zbioru wybrać wiersze, w przedziale 1,0 do 1,999999999, ale z uwzględnieniem wartości powyżej 2 (po odjęciu od nich 1) i 3 (po odjęciu od nich 2). Na sam koniec uwzględniamy tylko wartości po przecinku, czyli odrzucamy całkowite 3, 2 i 1 i uwzględniamy reszty po przecinku.

    Generalnie w przypadku KW1 powinniśmy otrzymać taką kolejność:

    KW1
    wiersz 5 - 3,101
    wiersz 6 - 2,350
    wiersz 11 - 2,233
    wiersz 5 - 2,101 (bo, 3,101 - 1 = 2,101)
    wiersz 8 - 1,546
    wiersz 1 - 1,527
    wiersz 12 - 1,469
    wiersz 6 - 1,350 (bo, 2,350 - 1 = 1,350)
    wiersz 7 - 1,321
    wiersz 4 - 1,320
    wiersz 3 - 1,239
    wiersz 11 - 1,233 (bo 2,233 - 1 = 1,233)
    wiersz 10 - 1,140
    wiersz 5 - 1,101 (bo 3,101 - 2 = 1,101)
    wiersz 9 - 1,064
    wiersz 13 - 0,924
    wiersz 2 - 0,765
    wiersz 1 - 0,527 (1,527 po usunięciu 1)
    wiersz 12 - 0,469 (1,469 po usunięciu 1)
    wiersz 6 - 0,350 (2,350 po usunięciu 2)
    wiersz 7 - 0,321 (1,321 po usunięciu 1)
    wiersz 4 - 0,320 (1,320 po usunięciu 1)
    wiersz 3 - 0,239 (1,239 po usunięciu 1)
    wiersz 11 - 0,233 (2,233 po usunięciu 2)
    wiersz 10 - 0,140 (1,140 po usunięciu 1)
    wiersz 5 - 0,101 (3,101 po usunięciu 3)
    wiersz 9 - 0,064 (1,064 po usunięciu 1)

    Pomińmy już liczbę wierszy do filtrowania, bo to zbyt duża komplikacja chyba.

    0
  • #4 06 Mar 2014 08:34
    adamas_nt
    Moderator Programowanie

    stec_piotr napisał:
    Nie da się zrobić tego sortowaniem, ponieważ (...)
    Na pewno? Jeśli musisz wykonać jakieś działania, wykonaj je w kolumnie pomocniczej i sortuj/filtruj w tej kolumnie. Excel, chociaż nie jest doskonały, tak bardzo ubogi w narzędzia też nie jest:

    Excel - zaawansowane sortowanie

    Po drugie: Nie byłoby prościej wrzucić załącznik, zamiast przepisywać arkusz?

    0
  • #5 06 Mar 2014 14:25
    stec_piotr
    Poziom 7  

    Moim zdaniem sortowaniem tego się zrobić nie da.

    Ale prawie uporałem się z problemem :) zrobiłem to wprawdzie na innym pliku niż w załączniku i trochę naokoło, ale jestem już blisko rozwiązania.

    Nie wiem tylko dlaczego przy funkcji tablicowej tam gdzie powinna być wyszukiwana jedna wartość we wszystkich komórkach, których dotyczy funkcja tablicowa, pojawia się ta wartość.

    W pliku w załączniku są to kolumny J i AA. W komórce J17 powinna być wartość "małopolskie", a komórki J18:J46 powinny być puste. Analogiczna sytuacja z kolumną J. Co ciekawe, jeżeli np. w zakresie J2:J14 znajdą się co najmniej 2 wartości powyżej 3, wówczas filtrowanie działa poprawnie. Nie wiem gdzie jest błąd :(

    plik do pobrania: http://www.parr.com.pl/pub/zeszyt1.xlsx

    Aha, i ktoś może wpadnie jak w obszarze J17:AK29 (żółty kolor) w kamach każdego KW wyszukać tyle kolejnych nazw regionów ile jest w komórkach B16:H16

    0
  • #6 07 Mar 2014 00:26
    Maciej Gonet
    Poziom 32  

    Dobry wieczór,
    Problem wynika z użycia funkcji MAX.K w wywołaniu tablicowym. Jeżeli użyjemy tej funkcji w postaci =MAX.K(zakres; tablica) funkcja zwraca w wyniku tyle wartości ile liczy tablica. Jeżeli tablica ma tylko jeden element powinno się odczytywać wynik z jednej komórki. Jeżeli użyje się funkcji tablicowo i zaznaczony zakres na wynik jest większy niż tablica, funkcja zwróci w "nadmiarowych" komórkach błąd #N/D!. Ale tylko gdy tablica liczy co najmniej 2 elementy. Jeśli tablica liczy 1 element wówczas wynik jest powielany do wszystkich komórek zaznaczenia i to jest przypadek, który sprawił Panu problem. Rozwiązaniem jest unikanie sytuacji, w której tablica ma 1 element. Najprościej dodać 1 do rozmiaru tablicy, błędy zostaną zamienione na puste teksty.
    Nota bene, jeżeli używa Pan Excela >=2007, na co wskazuje typ pliku, to nie rozumiem dlaczego używa Pan konstrukcji JEŻELI(CZY.BŁĄD...) zamiast JEŻELI.BŁĄD?

    Co do drugiego zagadnienia, które Pan poruszył jest ono niejasne. Poza tym, że chyba pomylił Pan adresy, chodziło zapewne o komórki B17:H46, a nie J17:AK29, to nie wiadomo skąd wzięły się liczby w wierszu B16:H16, bo nie są to sumy z odpowiednich dalszych kolumn. Więc na jakiej zasadzie należy wybrać i uporządkować 20 regionów, gdy w sumie jest ich 13?
    Pozdrowienia.

    0
  Szukaj w 5mln produktów