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 - jakie formuły do szukania i wyświetlania danch

irukh 19 Mar 2013 19:42 2865 16
  • #1 19 Mar 2013 19:42
    irukh
    Poziom 7  

    Witam pracuję w firmie w której zajmuję się flotą kilku pojazdów. Chciałbym opracować jeden skoroszyt w którym mógłbym ewidencjonować wszystkie ich dane
    w załączniku dodam uproszczony model.
    Najważniejsze dla mnie jest aby na innych arkuszach pojazdy były "rozpisywane" w kolejnych "linijkach".
    Na żółto wyróżniłem coś dodatkowego co też mogłoby mnie zainteresować czyli przenoszenie ostatniego stanu licznika do kolumny ze stanem początkowym dla następnej delegacji. Tu chyba mam pewne sugestie a mianowicie chyba można by przenosić stan licznika końcowy dla danego pojazdu z arkusza tego właśnie pojazdu.
    Proszę o pomoc. :D
    PS Może nie jestem całkowitym noobkiem w excelu ale chcę wiedzieć więcej. Pozdrawiam iRukh

    0 16
  • Pomocny post
    #2 20 Mar 2013 13:39
    marek003
    Poziom 40  

    Wg mnie bez makr się nie da (a to całkiem inna wiedza niż sam "excel"). Choć nie do końca rozumiem co autor wpisuje z ręki a co chce uzyskać "automatem".

    Podobny wątek (nie taki sam ale docelowo na podobnym mechanizmie zapewne by był oparty) :
    Link do wątku

    0
  • #3 21 Mar 2013 19:34
    irukh
    Poziom 7  

    Bardzo pomocny link muszę przyznać. W chwili obecnej staram się ogarnąć jak te całe makra działają ale myślę, że powinno być ok. W miarę postępu prac postaram się na bieżąco pytać we własnym wątku.

    No trochę móżdżyłem i starałem się rozgryźć te makra w podanym przykładzie. Opierając się na ostatnim możliwym do obejrzenia przez zwykłego usera (reszta skoroszytów zawiera już hasło) mam kilka pytań. Więc po kolei:
    I. Arkusz "ogólny rejestr umów".
    1. Makro "Aktualizuj" - jeśli dobrze rozumiem ma za zadanie dopisane nowe umowy posegregować po odpowiednich arkuszach z rodzajami umów ?
    2. Makro "Wprowadź" - no tu mam problem ze zrozumieniem co ono właściwie robi :D
    II. Arkusze rodzajów umów.
    3. Makro "Dodaj" - poniekąd chyba jest podobne w działaniu do "Aktualizuj", z tą różnicą, że wprowadzoną umowę w Arkuszu rodzaju umowy przenosi do ostatniego wiersza do "Ogólnego rejestru umów" ?

    Myślę, że na chwilę obecną odpowiedź na powyższe pytania spowoduje lawinę kolejnych nt. VBA, jednak z mojego ogólnego chaosu zaczyna wyłaniać się już malutki płomyczek wiedzy :D

    Na chwilę obecną mój plik excel'a został już trochę zmodyfikowany pod kątem przejrzystości. Zastosowanie funkcji JEŻELI, INDEKS np.
    Jest jednak kilka problemów, które myślę, że rozwiążą się dopiero po właściwym skonstruowaniu makra wysyłającego poszczególne dane z Arkusza "Paliwo Zużycie za 2013" do odpowiednich arkuszy jednostek sprzętu.

    Byłbym dodatkowo wdzięczny za skierowanie mnie - link do jakiegoś przejrzystego tematu, strony nt. VBA i makr. O tyle o ile zastosowane w pana przykładzie makra są przejrzyste o tyle pomimo niejakiej znajomości EN nie do końca potrafię zrozumieć poszczególne mechanizmy ich działania.

    0
  • #4 21 Mar 2013 20:32
    marek003
    Poziom 40  

    Na wstępie zaznaczę że jestem na forum jeszcze chwilkę i znów może mnie nie być dłużej więc zapewne nie pomogę za dużo. Ale może inni na forum poprowadzą.

    Wcześniej pisałem tylko że zasada będzie podobna.
    W tamtym przypadku:
    Aktualizuj - na podstawie umów ogółem tworzy arkusze i rozdziela umowy od początku dla każdego arkusza/umowy.
    Wprowadź - ostatnią linię z umów ogółem wprowadza do danego arkusza z podobnymi umowami
    Dodaj - odwrotność wprowadź czyli wprowadza ostatnią linię z danej umowy do umów ogółem


    W twoim przypadku jest inaczej tym bardziej że twój ostatni plik różni się znacznie od pierwszego przykładu.
    Jak widać danych w "Paliwo Zużycie za 2013" jest mniej niż chcesz mieć w poszczególnych kartach pojazdów. Tu też ma być automat?
    Może ja czegoś nie rozumiem.

    Jeszcze raz wytłumacz dokładnie i konkretnie co i jak ma robić "automat" po wciśnięciu przycisku.

    Co do budowy/pisania makr:
    Jeżeli nic nie wiesz na temat programowania to trochę za bardzo głęboką wodę sobie wybrałeś.
    Coś wiesz o jakimkolwiek programowaniu (w jakimkolwiek języku) ? pętlach ? Warunkach?
    Wiem że jesteś chłonny wiedzy ale jej przyswojenie musi trwać.
    Na sieci jest dużo kursów na temat VBA i warto na początku zacząć od teorii (Jest to ważne jeżeli nie miałeś do czynienia z programowaniem). Potem proste przykłady i tak dalej krok po kroku.

    0
  • #5 21 Mar 2013 22:00
    irukh
    Poziom 7  

    Najważniejszy jest arkusz "Paliwo Zużycie za 2013" zasadniczo wszystkie zmiany jakie będą dokonywane w skoroszycie są/będą wprowadzane w tym arkuszu.
    W pozostałych arkuszach będą (mogą być) zmianiane tylko dane mające znaczenie kosmetyczne bardziej niż powodujące działanie funkcji.
    Ogólny zamiar jest taki aby z arkusza "Paliwo Zużycie za 2013" po dopisaniu kolejnej (kolejnych) karty pracy w ostatnim wierszu, makro znalazło numer(y) rejestracyjny(e) pojazdu porównując je z numerami rejestracyjnymi w kolejnych arkuszach i w odpowiedni arkusz kopiowało odpowiednie dane jak:numer karty pracy, ważność od, ważność do, stan licznika początek, stan licznika koniec plus praca i pobrał paliwo w skrócie dane z kolumn D do L

    Dobrze byłoby też aby udało mi się wprowadzić sprawdzanie czy w "Paliwo Zużycie za 2013" nie powtarza się przez przypadek podwójnie numeru karty pracy.

    Na niebiesko zaznaczyłem w "Paliwo Zużycie za 2013" pobożne życzenie aby stan końcowy licznika pojazdu A po skopiowaniu do karty pojazdu A (kolejny arkusz), w przypadku ponownego wpisywania następnej karty pracy dla tego pojazdu ukazywał się w kolumnie stan licznika początek. Tylko że tu zastanawiałem się nad funkcją JEŻELI i OBLICZ.

    Bardzo zasugerowałem się podanym przez pana linkiem jednak, sam widzę że VBA nie jest tak proste. Mimo iż budowa teoretycznie jest prosta to dla laika w tej kwestii jest czarną magią.

    Cytat:
    Co do budowy/pisania makr:
    Jeżeli nic nie wiesz na temat programowania to trochę za bardzo głęboką wodę sobie wybrałeś.
    Coś wiesz o jakimkolwiek programowaniu (w jakimkolwiek języku) ? pętlach ? Warunkach?
    Wiem że jesteś chłonny wiedzy ale jej przyswojenie musi trwać.
    Na sieci jest dużo kursów na temat VBA i warto na początku zacząć od teorii (Jest to ważne jeżeli nie miałeś do czynienia z programowaniem). Potem proste przykłady i tak dalej krok po kroku.


    Niestety z tym mam poważne braki jedynie CSS i HTML kiedyś tam choć sporo w dyni się ostało :D

    0
  • #6 23 Mar 2013 11:56
    irukh
    Poziom 7  

    Przy pomocy kodu zaczerpniętego z powyższego przykładu oraz wydatnej pomocy z Link tej stronki. Zacząłem rozgryzać krok po kroku załączone makra jak się okazuje nie jest to super trudne tylko najtrudniej jes pojąć mechanizm ich działania :D
    Na chwilę obecną utknąłem na "adresowaniu".

    Kod: vb
    Zaloguj się, aby zobaczyć kod


    Może po kolei z czym mam wątpliwości:
    Dim w pierwszej linii "OstatniWiersz" - zmieniłem z ostW dla większej dla mnie czytelności jes niżej zdefiniowany wlaśnie tak jak się nazywa, jednak nie rozumiem definiowania pozostałych: wiersz, kod, x, arkusz co ma na celu definiowanie tylu ? Jednak chyba czytelniej będzie umieścić dręczące mnie wątpliwości w kodzie VBA.

    0
  • #7 23 Mar 2013 13:27
    marek003
    Poziom 40  

    No dobra chcesz się uczyć to proszę :)


    Kod: vb
    Zaloguj się, aby zobaczyć kod


    Podpowiem jeszcze że adresowanie możesz dokonać za pomocą:
    Range("A1")
    cells(nr_wiersza, nr kolumny) czyli np B4 to Cells(4,2) albo Cels(4,"B")
    Dla zakresów
    Range("A2:B5")
    lub to samo
    Można też Range(cells(2,1),cells(5,2))

    Trochę rozjaśniłem?

    Tu kompilum wiedzy co prawda o VB ale bardzo przydatne również w VBA bo to podstawa VBA
    Link Tu nic nie ma o excelu ale bardzo przydatne informacje Zmienne, Stałe, Funkcje Procedury itd.

    0
  • #8 23 Mar 2013 14:15
    irukh
    Poziom 7  

    'zdefiniowanie ostatniego wiersza w arkuszu "Inne"
    OstatniWiersz = Sheets("Inne").Range("D65535").End(xlUp).Row
    a właściwie Range D65535 definiuje mi maxymalny zasięg kolumny D ? , a 65535 jest to maxymalana ilość wierszy jaką potrafi "przetrawić" excel ?

    Cytat:
    Range("A2:B5")
    - z tej podpowiedzi wnioskuję że można stosować zapis excela w makrach "excelowych" ?

    jeśli tak to nawiązując do tego:
    Cytat:
    OstatniWiersz = Sheets("Inne").Range("D65535").End(xlUp).Row


    poprawny zapis może być też taki ?
    Cytat:
    OstatniWiersz = Sheets("Inne").Range("E:E").End(xlUp).Row

    właściwie sam sobie odpowiedziałem uruchamiając makro z takim zapisem :D
    tylko że coś chyba nie tak bo wprowadza mi pierwszy wiersz tylko
    lub jak to w moim przypadku:
    Cytat:
    OstatniWiersz = Sheets("Inne").Range("E3:M65535").End(xlUp).Row


    Kod: vb
    Zaloguj się, aby zobaczyć kod


    Prosiłbym o sugestię jak formułować pytania bo chyba przez niedoświadczenie wprowadzam śmietnik.

    0
  • #9 24 Mar 2013 13:23
    irukh
    Poziom 7  

    Przemyślałem dokładnie to co chcę otrzymać. Na dzień dzisiejszy VBA mocno mnie przerasta, a i czas deczko mnie goni.

    W związku z tym wróciłem do podstaw. Excel 2007 i tylko formuły w nim zawarte.

    Cytat:
    =JEŻELI(A3="";"";INDEKS('podział sprzętu'!$C$2:$C$60;PODAJ.POZYCJĘ(A3;'podział sprzętu'!$A$2:$A$60;0);0))


    myślę, że ta formułą zawiera zdecydowaną większość tego co potrzebuję osiągnąć, mam tylko wrażenie że zdecydowanie przegiąłem z zapętlaniem pomiędzy arkuszami.

    Na chwilę obecną po zastosowaniu tej funkcji w arkuszach odpowiednich dla rodzaju sprzętu wyświetla mi się tylko pierwsza karta pracy, a pozostałe działają dla mnie w jakiś „magiczny” sposób.

    Mógłby ktoś looknąć w te formuły i wytknąć mi błędy ? być może rozwiązaniem byłoby ustalenie wartości komórki K1 w arkuszach poszczególnych sprzętów – pojazdów ?

    0
  • #10 24 Mar 2013 13:55
    marek003
    Poziom 40  

    No właśnie wychodzi brak jakiejkolwiek teori dotyczącej budowy języka VB.
    Zostawmy na razie ostati wiersz bo to "specyficzny" przypadek.

    W twoim przypdaku kopiowanie (metodą copy) nie ma za bardzo ma sens
    wystarczy zwykłe przepisanie wartości danej komórki do innej komórki.

    Ale jeszcze raz od adresowania powoli przeczytaj ze zrozumieniem:
    Sheets(nazwa_arkusza_w cudzysłowiu) to arkusz więc np. Sheets("inne") to arkusz o nazwie inne

    Range(adres_obszaru_w_cudzysłowiu) to powidzmy obszar może być jednokomórkowy : Range("A1") lub wielokomórkowy: Range("A1:B4")

    Cells(numer_wiersza,numer_kolumny) to cel a w przypadku excela komórka czyli np cells(5,3) to komórka C5.

    Do adresowania wykorzystujesz albo range albo cells co ci lepiej pasuje Jednak dopuszcza się "zagnieżdzenie" cells w rangę jeżeli np adres ma wynikać z jakiś obliczeń bo cells zwraca adres komórki czyli dla A1 można tak Range(Cells(1,1)) a dla obszaru A1:B4 Range(Cells(1,1),Cells(4,2))

    Teraz chwilę przemyśl co jest wyżej i .... powidz mi co chciałeś osiągnąć (o którą komórkę/obszar ewentualnie ci chodziło) pisząc:
    "ActiveSheet.Cells.Range.Copy"

    Co do max wierszy
    Tak 65536 to max dla excela 2003 i niżej nowsze mają więcej dlatego też zmieniłem teraz funkcję (jeszcze utrudniłem zrozumienie :) )
    Ale wracając do starej funkcji:

    Sheets("Inne").Range("D65535").End(xlUp).Row

    Spróbuję to przeczytać może łatwiej będzie zrozumieć.
    Sheets("Inne") - arkuszu "inne"
    .Range("D65535") - przejdż/zauważ komórkę D65535
    End(xlUp). - "idź" do końca (xlUp) w górę aż do komórki innej niż pusta
    Row - i podaj nr wiersza

    Wracając do twojego pliku
    kod może wyglądać tak:

    Kod: vb
    Zaloguj się, aby zobaczyć kod

    0
  • Pomocny post
    #11 24 Mar 2013 14:03
    marek003
    Poziom 40  

    edit:
    Napisałem ci wcześniej że formułami będziesz miał ciężko.
    Byś musiał budować jakiś arkusz pomocniczy w którym należało by wykazać w jakich wierszach są dane dla danego sprzętu i dopiero na tej podstawie przenościć to do konkretnego arkusza. Samo wyszukaj i indeks tu będzie za mało.

    0
  • #12 25 Mar 2013 17:32
    irukh
    Poziom 7  

    wrócę trochę do tego adresowania

    dziś trochę rozgryzałem problem w pracy zaczyna to jakoś śmigać - rodzi się w ogromnych bólach :D

    Kod: vb
    Zaloguj się, aby zobaczyć kod


    jeśli dobrze rozkminiłem problem ten kod odpowiada za to gdzie w której linii / wierszu zapisze się ostatnia linijka z arkusza "głównego" nie potrafię tylko odpalić na jakiej zasadzie (podkreślam że dobrze to jest robione w tym momencie czyli zapisuje się od 36 wiersza w następnych arkuszach jeśli jest to pierwszy zapis w danym arkuszu a nie od wiersza 6 ?)

    Kod: vb
    Zaloguj się, aby zobaczyć kod


    no chyba że dalej tego jednak nie pojąłem co jest bardzo możliwe :D

    Dodano po 40 [minuty]:

    mój błąd NADmyślenia pisze przecież wyraźnie ostatniWierszArkuszaDocelowego :P
    ale i tak intryguje mnie ta cyfra 6 O_O

    Dodano po 17 [minuty]:

    znalazłem rozwiązanie cyfry 6 :D numer kolumny arkusza "docelowego" czy jest "pełna" jeśli jest to pisze podnią

    0
  • #13 26 Mar 2013 19:30
    irukh
    Poziom 7  

    prócz przycisku pomógł powinien być przycisk ciągle pomaga :D

    0
  • #14 26 Mar 2013 20:20
    marek003
    Poziom 40  

    Przyznam że nie wiem już jak inaczej pisać/tłumaczyć by to od razu do ciebie trafiało. Dwa razy ci tłumaczyłem argumenty cells():

    marek003 napisał:
    cells(nr_wiersza, nr kolumny) czyli np B4 to Cells(4,2) albo Cels(4,"B")
    marek003 napisał:
    Cells(numer_wiersza,numer_kolumny) to cel a w przypadku excela komórka czyli np cells(5,3) to komórka C5.

    Ale widocznie za słaby nauczyciel jestem.

    0
  • #15 26 Mar 2013 21:10
    irukh
    Poziom 7  

    tłumaczysz w porządku ja mam jakieś zapętlenie

    Cytat:
    Teraz chwilę przemyśl co jest wyżej i .... powidz mi co chciałeś osiągnąć (o którą komórkę/obszar ewentualnie ci chodziło) pisząc:
    "ActiveSheet.Cells.Range.Copy"


    chciałem skopiować ostatni zakres komórek z aktywnego wiersza odpowiadając na pytanie

    0
  • #16 27 Mar 2013 18:18
    marek003
    Poziom 40  

    W twoim przypadku nie korzystaj z "Copy"
    Wykorzystuj przypisanie "="
    Zobacz jak to jest zrobione (bez "copy") w ostatnim moim pliku z przykłądem.

    0
  • #17 28 Mar 2013 16:44
    irukh
    Poziom 7  

    Aby nie rozbijać wątku na PW - postanowiłem wkleić moją porażkę:

    Cytat:
    Witam,
    wiesz powiem wprost zagadnienie mnie przerosło i czas mnie sporo goni o tyle o ile rozumiem lub prawie rozumiem to co pisze to jednak nie rozumie tego co makro robi. Doskonale zdaję obie sprawę z moich braków w językach programowania i dlatego zwracam się do ciebie poprzez PW. Makro które TY mi już napisałeś jest super potrzebuję jednak rozbudować je o kilka opcji, a sam tego nie potrafię zrobić. Dlatego zwracam się o tzw. "gotowca".

    Dodatkowo do tego co już jest:
    1. Mam potrzebę dodawania więcej niż tylko jednej karty pracy sprzętu (zdarza się, że w ciągu dnia wpisuję ich około 30+), a trochę rozpraszające jest odrywanie łap od klawiatury po wprowadzeniu pojedynczego wiersza.
    2. Chciałbym aby karty już wpisane się nie powtarzały po kilkanaście razy w tej samej karcie pojazdu - sprzętu.
    3. To już w ogóle dla mnie bardzo wysoka szkoła jazdy
    - stan licznika koniec który jest wprowadzany do arkusza pojazdu, aby był wyświetlany w komórce stan licznika początek w arkuszu "paliwo zużycie za rok" w momencie kiedy w trzeciej kolumnie arkusza "paliwo zużycie za rok" pojawiłby się numer pojazdu pobrany z arkusza pojazdu który właśnie wpisuję

    Na dzień dzisiejszy zmodyfikowałem z poziomu excela nazewnictwo arkuszy i pobieranie danych do poszczególnych arkuszy z arkusza "Paliwo zużycie za rok" oraz ilość kolumn kopiowanych -> wklejanych więc teraz nic mi się nie sypie z poziomu excela oraz twojego makra "WPROWADŹ".
    Zmieniłem też nazwę arkusza "paliwo zużycie za 2013" na "paliwo zużycie za rok" co w moim przypadku jest rozwiązaniem na przyszłe lata bo nie będę musiał modyfikować


    marek003 odpowiedział i zdecydowanie mocno mi pomógł :D

    Cytat:
    Z reguły nie pomagam na pw ale masz bonus.
    Dodałem kolumnę Stan po której makro poznaje co zostało już wprowadzone a co nie. Za każdym naciśnięciem sprawdza od początku listę i jeżeli nie ma OK to prubuje wpisać dane do docelowego arkusza (jeżeli jest) jak nie ma tego arkusza wyrzuca komunikat i pozostawia stan pusty. jeżeli przepisze dane wstawia w kolumnie stan OK by na przyszłość nie kopiować już tego wiersza.

    Co do stanu licznika
    Ponieważ podczas wpisywania danych do głównego arkusza (bez wprowadzania ich do arkuszy urządzeń /przed przyciskiem) może się zdażyć tak że dany pojazd wprowadzisz dwa razy. Więc jego aktualny stan końcowy licznika jest w arkuszu głównym.
    Innymi słowy po wpisaniu numeru w kolumnie C makro szuka idąc w górę takiego samego numeru i jeżeli znajdzie to wpisze z tamtąd końcowy stan licznika pod warunkiem że komórka dotycząca początkowego stanu licznika we wpisywanym wierszu jest pusta. (jeżeli wcześniej wpiszesz początkowy stan licznika a potem numer (w tym samym wierszu) stan ten nie zostanie zmieniony. To takie zabezpieczenie by nie zmienić stanu w już wcześniej wpisanych wierszach.


    Na dzień dzisiejszy jest perfekcyjnie działające makro !
    Wiem że wolisz aby napisać CI że szukający pomocy ZROZUMIAŁ, niestety w moim wypadku jest to niemożliwe - nie będę sam siebie okłamywać.

    ale naprawdę mocno bardzo DZIĘKUJĘ

    PS. Od siebie pragnę dodać że na pewno wiem więcej niż wiedziałem - dałeś mi ogromnie cenne wskazówki oraz podwaliny pod przyszłą (myślę że na pewno VBA będę starć się kiedyś z niego korzystać).

    0