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

excel - porównanie kolumn w excel

04 Wrz 2012 15:11 8853 30
  • Poziom 12  
    witam, proszę o pomoc bo tego nie ogarniam.

    Mam 3 cenniki, każdy posiada 2 wartości ID i cenę

    Teraz kłopot w tym że cenniki są z 3 różnych lat.

    I teraz tak:

    1.potrzebuję sprawdzić czy ceny we wszystkich 3 wersjach się zgadzają, jeśli nie to jakoś to podświetlić, żeby można to było wychwycić

    2. 3 cenniki różnią się dostępnością produktów.

    np. prdukt XYZ występuje tylko w cenniki nr3 a brakuje go w cennikach nr1 i nr2
    i j/w jakoś żeby to można było wychwycić, żebym miał info XYZ cennik nr1, brak nr2,nr3


    w załączniku dane z 3 cenników

    A- id
    B- cena

    z góry dziękuję za pomoc, bo męczę się z tym już 3 dni :-/


    sorry nie wiem czemu plik był pusty teraz jest ok
  • Pomocny post
    Poziom 39  
    Pusty załącznik - bardzo śmieszne. ;)

    A to co chcesz zrobić - najlepiej zrobisz w tabeli przestawnej.
    Najpierw doprowadź 3 cenniki do jednej formy (takie same kolumny).
    Potem połącz - (cennik pod cennikiem) dodając dodatkową kolumnę - numer_arkusza (1 dla pierwszego, 2 dla drugiego i 3 dla trzeciego), lub po prostu numer wskazujący rok cennika.
    Potem tabela przestawna - i masz co chcesz, różnice, sumy, średnie, wykresy itd..

    Zamieść poprawny załącznik to zrobimy co chcesz (podpowiemy).
  • Pomocny post
    Moderator Programowanie
    "Konkurencyjne" rozwiązanie oparte o WYSZUKAJ.PIONOWO, z dwoma zasadami formatowania warunkowego, wyborem cennika do porównania (poprawność danych/lista) i filtrowaniem, gdzie można odfiltrować wyniki na kilka sposobów:

    excel - porównanie kolumn w excel Ceny większe/mniejsze/brak w cenniku.
  • Poziom 12  
    OMG!!! to jest to!!! gdybyś mógł zobaczyć teraz moją radość :-D


    Dziękuje ślicznie, mógłbym prosić o link do jakiegoś tutoriala lub jeżeli masz czas to plik z opisem krok po kroku (taki dla topornych) jak to zrobiłeś.
  • Pomocny post
    Moderator Programowanie
    Cóż. Tabela przestawna pokaże Ci wszystko, ale dalej trzeba bystrego oka, żeby zauważyć różnice. Można zastosować "konkurencyjne rozwiązanie" we wszystkich arkuszach. W ostateczności napisać makro, które "wydłubie" wszystkie unikatowe indeksy/nazwy towarów z 3 arkuszy, oznaczy różnice w cenach, etc. i wpisze do osobnego arkusza wynikowego.
  • Poziom 12  
    no to j/w leże i kwicze :-) bo próbuje odtworzyć twoje działania w zakładce pcb. bo to co ty zrobiłeś tyczy się zakładki cennik. i to jest dobre przyjmując że cennik posiada wszystkie produkty ( tak nie jest) wieć wiemy czego brakuje w zakładce www i pcb względem zakładki cennik ale nie wiemy jak zawartość pcb ma się do www i cennika ( jeżeli są tam dodatkowe wpisy).

    I tak adamas_nt jest dla mnie bogiem :-). Jakby była opcja stworzenia mi takiego pliku to byłbym wdzięczny, ale nie nic nie oczekuje bo jak pisałem dla mnie to to jest jak budowa muru chińskiego.

    Szukam tutoriali i wciskam F1 :-) ale nie mogę dojść do ładu z tym.

    acha, nakreśle mój poziom znajomości excela - nie wiem jak zrobileś menu rozwijane, że można wybrać www i pcb.... no właśnie - czarna magia:-]


    A tak offtopic - to jest chyba jedyne forum na którym ludzie pomagają a nie gnoją na starcie (troche teraz wam słodze) - ale jednak jest to prawda.
  • Moderator Programowanie
    Skopiowałem arkusz "Cennik" (dwa razy), podstawiłem dane z "pcb" i "www", skorygowałem listy. usunąłem oryginały, w "pcb" przez Ctrl+H zmieniłem kropkę na przecinek w kolumnie B, i... voila

    P.S. Lista w C1: Poprawność danych > Lista
  • Poziom 39  
    To ja podpowiem mój sposób.
    W załączniku rozwiązanie - tabela przestawna + prosta funkcja kontrolna (do formatowania warunkowego).

    W dodatkowym arkuszu (Arkusz1) zgrupowałem w 1 tabelkę wszystkie dane z poprzednich 3 arkuszy, dodałem kolumnę która mówi, skąd dane pochodzą.
    W kolejnym arkuszu (tabela przestawna) zrobiłem z tych poprzednich danych tabelę przestawną - i dopisałem z boku prostą regułę sprawdzającą. Widać jak na dłoni - co jest i co jest źle.

    Nie ujmując nic koledze wyżej, ale metoda którą ja użyłem jest bardziej przejrzysta :)
  • Poziom 12  
    [quote="adamas_nt"]Skopiowałem arkusz "Cennik" (dwa razy), podstawiłem dane z "pcb" i "www", skorygowałem listy. usunąłem oryginały, w "pcb" przez Ctrl+H zmieniłem kropkę na przecinek w kolumnie B, i... voila

    skopiowałem arkusz cennika (2 razy) ale gdzie to mam wkleić?
    Skorygowałem listy - jak?
    usunąłem oryginały - dlaczego?
    zmiana kropki na przecinek - dlaczego?

    Lista!!! no tylko nie wiem co mam wpisać w źródle...

    powinienem zmienić link na czarna masa :-D

    aaaa a jeśli stworze dodatkową listę, w której znajdą się np. towary które są na stanie. to jest możliwość żeby na podstawie tych wpisów podświetliło mi na stałe wpisy w tych 3 cennikach ( cennik, pcb i www)


    i ostatnie: jak okaże się że mam jeszcze więcej towarów i chce je dodać do pliku, jak to zrobić żeby działało... bo przez kopiuj/wklej to raczej nie zadziała.
  • Pomocny post
    Poziom 39  
    otek84 napisał:
    zmiana kropki na przecinek - dlaczego?

    W jednym z arkuszy miałeś kropki jako separator dziesiętny, Excel traktował wartość jako tekst, a nie liczbę.
    W moim rozwiązaniu masz różnice i braki widoczne w 1 arkuszu.
  • Poziom 12  
    właśnie otwarłem twój plik mickpr i powiem tak mam już dwóch bogów na tym forum :-)

    (btw bóg z małej litery żeby nie było że bluźnie)


    Zrobię tak przeanalizuje sobie te pliki i spróbuje coś potworzyć sam ( z jakim skutkiem to odezwę się jutro)

    spróbuje również stworzyć sobie tą listę o której pisałem wyżej z produktami dostępnymi i odezwę się tutaj jak będę potrzebować pomocy ( a myślę że będę) ale na razie jestem optymistą:-)
  • Poziom 39  
    Taka dodatkowa informacja - twoje dane nie są spójne. Masz powtarzające się produkty (wiersze) w cennikach "cennik" i "pcb".
    Zmieniając w tabeli przestawnej ustawienia pola wartości z "suma z Cena" na "licznik z Cena" bez problemu odkryjesz w których przypadkach masz duplikaty.
    Przykładowo - wyrób 101-201/07-2 w "cennik"
  • Poziom 12  
    poradziłem sobie ze stworzeniem tabeli przestawnej... tak jakby

    zrobiłem wg instrukcji: wszystkie 3 cenniki do 1 arkusza dodać kolumnę z nazwą cennika, zaznaczyć utworzyć tabele przestawną.

    No i mam 3 wartości; ID, CENA i ARKUSZ

    Jak stworze tabele to mam kolumnę sumującą mi ceny z 3 arkuszy, której nie potrzebuje. Teraz jak chce wpisac funkcje jeżeli() to jak zaznaczam pola do funkcji to zamiast prostego np.=JEŻELI(B5=C5;0;1)+JEŻELI(C5=D5;0;1) mi wychodzi coś takiego:

    =JEŻELI(WEŹDANETABELI("cena";$A$3;"id";"100-101";"arkusz";"cennik")=WEŹDANETABELI("cena";$A$3;"id";"100-101";"arkusz";"pcb");0;1)+JEŻELI(WEŹDANETABELI("cena";$A$3;"id";"100-101";"arkusz";"pcb")=WEŹDANETABELI("cena";$A$3;"id";"100-101";"arkusz";"www");0;1)


    i wychodzi prawidłowa wartość, ale nie mogę tego skopiować do innych komórek nie zmienia mi wartości, więc musiałbym tworzyć funkcje dla każdej komórki osobno.


    Wiem że zawsze mogę wpisać ręcznie ale nie daje mi to spokoju dlaczego się tak dzieje, pewnie ma to związek z tym że te dane są w tabeli przestawnej - nie wiem nie znam się.


    i jeszcze mnie taka myśl naszła.


    mamy te 3 cenniki, teraz jeżeli założymy że arkusz CENNIK zawiera prawidłowe ceny to jak zrobić żeby znalazło mi złe cenny w arkuszu WWW i zamieniło je na te pasujące do cen z arkusza CENNIK


    TYLKO WAŻNE, te wartości muszą zmienić się w arkuszu WWW a nie w arkuszu tabeli przestawnej!

    czyli
    w arkuszu cennik mamy:

    101-100 200zł
    101-101 250zł
    101-102 150zł* brak takiego wpisu w arkuszu www
    101-103 300zł

    w arkuszu www mamy:

    101-100 200zł (cenna taka sama jak w arkuszu cennik)
    101-102 180zł (inna cena niż w arkuszu cennik)
    101-103 310zł (inna cena niż w arkuszu cennik)



    efekt końcowy w arkuszu www

    101-100 200zł
    101-101 250zł
    101-103 300zł

    ceny jak w arkuszu cennik, z zaznaczeniem że produkt 101-102 150zł nie został dodany.


    jeżeli rozumiecie o co mi chodzi to SZACUN :-] bo ja sam siebie nie rozumiem :-P
  • Pomocny post
    Poziom 39  
    otek84 napisał:
    Jak stworze tabele to mam kolumnę sumującą mi ceny z 3 arkuszy, której nie potrzebuje

    Sumę możesz usunąć. Prawym klawiszem na tej kolumnie -> Usuń "suma z cena".

    otek84 napisał:
    Teraz jak chce wpisac funkcje jeżeli() to jak zaznaczam pola do funkcji to zamiast prostego np.=JEŻELI(B5=C5;0;1)+JEŻELI(C5=D5;0;1) mi wychodzi coś takiego:
    ...
    Dlatego ja wpisałem ten warunek "z łapki" - czyli ręcznie. Oczywiście tamten jest poprawny, ale skopiowanie formuły do wierszy poniżej jest trudniejsze.

    otek84 napisał:
    mamy te 3 cenniki, teraz jeżeli założymy że arkusz CENNIK zawiera prawidłowe ceny to jak zrobić żeby znalazło mi złe cenny w arkuszu WWW i zamieniło je na te pasujące do cen z arkusza CENNIK

    Skopiuj z tabeli przestawnej wiersze tam, gdzie są różnice, będzie łatwiej wprowadzać zmiany.
    Potem niestety robota ręczna.

    Do takich rzeczy o których piszesz to już proponowałbym przeniesienie do bazy danych.
    Wtedy jedną kwerenda UPDATE mógłbyś aktualizować sobie ceny WWW z cennika (tam gdzie występują, bo tak chcesz, prawda?, a nie wszędzie?)

    Do takiego ręcznego uaktualniania najlepiej "nada się" pomysł przedstawiony przez kolegę adamas_nt.
  • Poziom 12  
    no odnośnie cen to dokładnie

    suma z tabeli usunięta (takie proste a jednak)

    jeszcze miałbym prośbę o pomoc ze zrobieniem takiego zestawienia.

    Teraz stworze sobie (mam nadzieje że skończę na jutro) arkusz z produktami, które nazwijmy to że mam w magazynie (nie chce mi się tłumaczy bo to zawiłe jest)

    Chciałbym aby produkty zawarte w tym arkuszu -( nazwijmy go magazyn ) zostały wyszczególnione (podświetlone) w tabeli przestawnej.

    czyli w pliku magazyn mam 100-101/50 i 100-101/70RS to w tabeli przestawnej oprócz widoku jaki już mam to kod artykułu był podświetlony

    100-101
    100-101/50
    100-101/70RS



    ale to jutro - oczy mi już wysiadają...

    dzięki wielkie!
  • Poziom 39  
    Nie do końca tak się da. Tabela przestawna ma duże, ale ograniczone możliwości.
    Nadaje się do porównywania, sumowania i ogólnie operacji - na "seriach danych".
    Serie danych to rekordy, które mają jakieś parametry i cechy, wg. których możemy je poukładać sumować, liczyć itd...
    Nie będę udawał nauczyciela - w każdym większym tutorialu będziesz miał opis.
    Łącznie z OLAP (kostki wielowymiarowe) - to już dla fanatyków tabel przestawnych.

    Aby (przykładowo) pogrubić określone elementy (np. z cennika "www") w tabeli którą wysłałem:
    1. kliknij przy strzałce ze słowem "Etykiety kolumn".
    2. odznacz w filtrze wszystko oprócz "www".
    3. pogrub zawartość tabelki
    4. z powrotem kliknij na strzałkę obok "Etykiety kolumn" i w filtrze "zaznacz wszystko"

    Rekordy z cennika WWW powinny pozostać pogrubione.
    Czy zadziała np. z kolorami - sprawdź.
  • Poziom 12  
    no nie wychodzi mi - pogrubia mi wszystkie wartości.

    Podpinam plik, z arkuszem, gdzie wypisane są produkty dostępne na magazynie.
    w sumie nie muszę tego mieć w tabeli przestawnej podświetlonego, może być w tych poszczególnych arkuszach (cennik, pcb i www). Tylko właśnie chodzi o to aby podświetlało tylko te wartości, które znajdują się w arkuszu magazyn, a mi podświetla wszystkie :-( albo wcale nie podświetla ;.-(
  • Poziom 39  
    otek84 napisał:
    no nie wychodzi mi - pogrubia mi wszystkie wartości.

    Zaznacz nazwę a nie cenę. Ceny - tak jak mówisz - podświetli wszystkie.
    otek84 napisał:
    Podpinam plik, z arkuszem, gdzie wypisane są produkty dostępne na magazynie.

    Czyli to jest 4-ty arkusz?
  • Poziom 12  
    Tak, to jest 4 arkusz. Ale w nim zawarte są tylko numery produktów bez cen, bo to jest ewidencja produktów dostępnych. Czyli jak mi klient zadzwoni to żebym mógł sprawdzić: o ten produkt mam wysyłam od razu, lub nie mam go na stanie więc powiadamiam klienta że czas realizacji jest dłuższy.


    edited/

    Jeszcze jedno pytanko. Teoretycznie, wprowadzam nowe produkty, powiedzmy do cennika pcb, czy dopisując je w arkuszu pcb one automatycznie pokażą mi się w tabeli przestawnej, czy muszę to robić od początku tzn. tworzyć nową tabele przestawną wg. nowych danych?


    dzięki
  • Poziom 39  
    Do takiego rozwiązania przydał by ci się jakiś prosty system magazynowy. Nawet w formie bazy Ms Access z podlinkowanymi obecnymi arkuszami Excel.
    Ja nie widzę możliwości połączenia odrębnej tabeli z tabelą przestawną w formie jaką zapewnia "INNER JOIN" w bazie danych.
  • Poziom 12  
    a jeżeli zaznaczę wartości z arkusza magazyn i dam wyszukaj pionowo w każdym z 3 cenników to znajdzie mi to.

    tylko tu znowu problem, bo ja wiem że istenieje coś takiego jak wyszukaj pionowo ale jak się tym posługiwać to nie


    bo robie tak:

    wyszukaj.pionowo
    jako szukana wartość zaznaczam produkty z arkusza magazyn
    jako tabela_tablica zaznaczam produkty z arkusza pcb
    jako numer_indekssu_kolumny wpisuje 1
    jako przeszukiwany zakres wpisuje FAŁSZ

    coś czuję że trzeba zrobić coś z funkcją jeżeli



    nie wychodzi a jak już coś się pokaże to i tak w nowej kolumnie a ja potrzebuje żeby mi podświetliło istniejącą kolumnę, ewentualnie kolumna obok wyświetlająca info TAK lub NIE
  • Poziom 12  
    sorry ze tak post pod postem ale...

    co by było gdybym zrobił kolejną tabele przestawną??

    tylko teraz wziął wartości z pierwszej tabeli przest+wartości z arkusza Magazyn?
  • Poziom 39  
    otek84 napisał:
    sorry ze tak post pod postem ale...
    co by było gdybym zrobił kolejną tabele przestawną??
    tylko teraz wziął wartości z pierwszej tabeli przest+wartości z arkusza Magazyn?

    Tabela przestawna nie jest remedium na wszystkie problemy (twoje i Excel'a).
    Nadawała się do porównania - dlatego ją zaproponowałem.
    Do tego, co chcesz teraz zrobić - się nie nadaje.

    Moje rady:
    1- doprowadź dane (arkusze) do poprawności (usuń duplikaty, zamień ceny na liczby, bo w jednym z arkuszy były jako tekst). Dodaj nagłówki.
    2- określ dokładnie końcowy efekt (wszystkie pożądane tabelki, zestawienia itd)
    3- wybierz narzędzie do tego celu.

    Jak widać Excel nadaje się do wprowadzania danych i prostych zestawień, ale nie nadaje się do przenoszenia cen z jednego arkusza do drugiego i do prowadzenia magazynu.
    Liczy się końcowy efekt, a nie skomplikowane twory tabela przestawna + druga tabela przestawna.
    Chodzi aby "dopaść króliczka, a nie o to żeby go gonić".
  • Poziom 12  
    co racja to racja.

    to chyba tyle w tym temacie.

    Jeszcze raz BARDZO dziękuję za pomoc!

    Dodano po 20 [minuty]:

    Znowu post pod postem (ostatni raz)

    Jest sens zakładania nowego tematu, bo w tym trochę namieszałem. Już tłumaczę:
    cenniki nie są z różnych lat. Podam stan faktyczny:

    arkusz cennik - odnosi się do cennika fizycznego (na papierze)

    arkusz pcb - to spis produktów (wszystkich) wyprodukowanych, dostępnych oraz projektów, czyli jest kod produktu ale nie ma jeszcze stworzonej technologi do jego wykonania (nie ma planów konstrukcyjnych).

    arkusz www - cennik ze sklepu internetowego.


    no i arkusz magazyn, który faktycznie jest spisem produktów które posiadają technologie (plany wykonania)

    teraz wkradł mi się chochlik i namieszał.

    Bo teoretycznie wszystko co dostępne+projekty powinny znajdować się w arkuszu PCB, jednak, wszedł zamysł na nowy projekt i ktoś ten projekt umieścił w cenniku www a nie wpisał go w bazę PCB. i teraz ktoś zamawia ten produkt przez internet, bo tam jest dostępny, oczekuje wysyłki do 5 dni a stworzenie technologi zajmuje od 2tygodni do 2 miesięcy

    temu potrzebuje zrobić to końcowe zestawienie.

    Sorry że tego nie opisałem na początku ale chciałem to zrobić ogólnikowo a potem to już brnąłem bo nie chciałem więcej mieszać.

    Więc ten arkusz z tabelą przestawną jest super, bo widze jak na dłoni gdzie mi się cena nie zgadza i którego produktu mi gdzie brakuje.


    Więc teraz na chłopski rozum jak skopiuje wszystkie kody produktów (to 101-102/50 itp) i zestawie je z kodami produktów w arkuszu magazyn. to powinno mi pokazać które produkty posiadają technologię.

    a napisałem powinno, bo mi to nie chce wyjść.


    Nie wiem czy rzucając nowe światło na tą sprawę coś zmienia.

    a odnośnie pytania czy jak dodam nowe produkty to czy mi się to odświeży... to co zamieściłem to 1/4 produktów, które muszę porównać, więc chciałem wiedzieć czy jak dokleje pozostałe czy mi się to automatycznie odświeży, czy też muszę robić tabelę przestawną od nowa.
  • Poziom 39  
    otek84 napisał:
    a odnośnie pytania czy jak dodam nowe produkty to czy mi się to odświeży... to co zamieściłem to 1/4 produktów, które muszę porównać, więc chciałem wiedzieć czy jak dokleje pozostałe czy mi się to automatycznie odświeży, czy też muszę robić tabelę przestawną od nowa.

    Musisz zrobić od nowa (zmieni się zakres danych). Jeśli nie zmieniłbyś zakresu - a tylko uaktualnił dane - wystarczyło by odświeżyć tabelę przestawną.

    Jedno jest pewne - porządek w danych to podstawa.
    Zrób to najpierw w Excel-u, potem przeniesiemy do do Access'a i chętnie pomogę (oczywiście za free).
  • Pomocny post
    Moderator Programowanie
    Widzę, że praca wre... Uchował mi się formularz z filtrowaniem, który jest gdzieś na forum (nie chce mi się szukać). Dostosowałem, dorobiłem wyszukiwanie. Przetestuj, a nuż się nada.

    Przy inicjacji pobierane są listy ze wszystkich arkuszy, po czym przy przepisywaniu tablic usuwane są duble. Funkcja WYSZUKAJ.PIONOWO znajdzie oczywiście pierwszą z góry w przypadku istnienia dubli w arkuszu.
  • Poziom 12  
    :D :D :D


    no wygląda na to że działa i to działa poprawnie.

    :?: Pytanko, jak wyedytować nazwy w formularzu. Bo chciałbym zmienić "nazwę na stanie:" na "Technologia"
  • Poziom 39  
    Alt-F11 i masz edytor. Po lewej wybierasz "userform1".
  • Poziom 12  
    mógłbym prosić o zrobienie takiego samego pliku na combobox ale na podstawie danych które zamieszczam w pliku poniżej.

    Tym razem plik MAGAZYN został zaimportowany do arkusza TECHNOLOGIA. Wszystko inne tak samo (tylko więcej danych)

    dzięki i myślę że będzie dobrze
  • Moderator Programowanie
    Dołożyłem sortowanie dla listy. Poprawiłem mały błąd. Reszta w załączniku.

    Plik zawiera makra, więc w razie komunikatów zmień ustawienia w "Centrum zaufania > Ustawienia makr".