Forum elektroda.pl

Regulamin | Punkty | Dodaj... | Ostatnie | Szukaj | Rejestracja | Zaloguj

Ta strona używa cookie. Dowiedz się więcej o celu ich używania i zmianie ustawień cookie w przeglądarce.
Korzystając ze strony wyrażasz zgodę na używanie cookie, zgodnie z aktualnymi ustawieniami przeglądarki.

Excel - jak zrobić aby dwie kolumny przyporządkowały się indeksami


Napisz nowy temat  Odpowiedz do tematu      Strona Główna -> Forum elektroda.pl -> Komputery Serwis -> Komputery Software -> Excel - jak zrobić aby dwie kolumny przyporządkowały się indeksami
Autor
Wiadomość
okonda
Poziom 4
Poziom 4


Dołączył: 03 Lis 2010
Posty: 9
Miasto: Wrocław

Post#1 Post autora tematu 11 Sty 2013 15:59   

Excel - jak zrobić aby dwie kolumny przyporządkowały się indeksami


Witam.
Mam problem z dwoma tabelami (starą i nową) i za żadne skarby nie potrafię sobie poradzić.
Powiem prostymi słowami.
Mam cennik 2012 który ma 1200 artykułów, gdzie w kolumnie A mam nr katalogowe, w B nazwy i C ceny. Otrzymałem nowy cennik 2013 który ma 1226 artykułów i także składa się z trzech kolumn które wkleiłem w D;E;F i tak:
Cennik 2012 posiada kilka artykułów których nie ma w cenniku 2013, natomiast cennik ma o 26 artykułów więcej, z czego ok.40 jest nowych(gdzieś w środku), czyli kilku artykułów nie posiada już nowy cennik 2013.
Numery ułożone są od najmniejszego do największego od góry do dołu.

01E1007 01E1007
01E3007 01E3007
01E4007 01E4007
01E8007 01E8007
01EA125 01EA125
01M4051 01EA126
01M4061 01M4111
01M4071 01M8111
01M4081 01N4013
01M4091 01N4015
01M4101 01N4017
01M4111 01N408A
01M8051 01N4093
01M8061 01N4097
01M8071 01N8013
01M8081 01N8015
01M8091 01N8015D30
01M8101 01N8017
Potrzebuję aby kolumna A i D miały te same wartości.
Kiedy w kolumnie A w danym wierszu niema liczby identycznej jak w kolumnie D to aby wiersz w kolumnie A przesunął się w dół (z kolumną B i C) i jeśli w D niema wartości takiej jak w A to tak samo aby wiersze z kolumny D:E i F przesunęły się w dół, tak aby powstawały puste wiersze.
01E1007 01E1007
01E3007 01E3007
01E4007 01E4007
01E8007 01E8007
01EA125 01EA125
01EA126
01EA128
01M4111
01M8111
01N4013
01N4015 01N4015
01N4017 01N4017
01N408A 01N408A
01N4093
01N4097 01N4097
01N8013 01N8013

Dałem tu tylko kolumnę A i D ale są tu jeszcze opisy tak jak opisałem wcześniej.
Czy da się to w prosty sposób zrobić?
Jest to mi potrzebne, dlaczego.

Otrzymuję co roku inny cennik. Za każdym razem są, tzw. wymieniane artykuły. Niektóre znikają, a niektóre są nowe.
Cennik ten przychodzi w języku niemieckim lub angielskim i to jeszcze ubogi (bez dokładnych opisów artykułów). Ja ten cennik, artykuł po artykule przetłumaczyłem (częściowo) i teraz muszę ręcznie wklepywać ceny, a niestety jest tego trochę.

Posiadając taką tabelę:
raz - daję później kopiuj wklej ceny, a puste uzupełniam;
dwa - mogę zrobić porównanie procentowe, o ile KAŻDY artykuł zmienił cenę
trzy - mam wrażenie, że jeszcze w wielu innych sprawach będzie przydatne.

Proszę o pomoc.

W załączniku przesyłam przykładową małą tabelkę. Jeśli ktoś potrafi to wytłumaczyć, a dodatkowo przerobić tę tabelę to będę wdzięczny.

Załączniki:
Powrót do góry
   
Google


Google Adsense


Post# 11 Sty 2013 15:59   





Powrót do góry
   
yogi009
Poziom 24
Poziom 24


Dołączył: 27 Sty 2006
Posty: 4982
Miasto: Olsztyn

Post#2 11 Sty 2013 17:48   

Re: Excel - jak zrobić aby dwie kolumny przyporządkowały się indeksami


Ja bym tu zastosował taki (mniej więcej) algorytm:

1. wklejam oba cenniki jeden pod drugim, przy czym w czwartej kolumnie nowy rok zaznaczam (może to być wartość "2013", może też być jedynka albo "v" - nie ma znaczenia)
2. sortuję całość wg indeksu, wpisy o tych samych indeksach znajdą się jeden nad drugim - sąsiadująco,

3.Teraz program powinien przepisać indeks nazwę i cenę jeśli:

bezpośrednio po sobie nie następują dwa identyczne indeksy
lub
jeśli występują dwa identyczne, to przepisuje te wiersz, gdzie jest postawiony znak "v"

3. w kolumnie obok (po prawej, np. w kolumnie F) wpisuję warunek:

Da się to zrobić warunkiem jeżeli.

Pozdrawiam.

Moje artykuły: (Dodaj)
16 Maj 2014 Kolejny prosty do wykonania zegarek LED
Powrót do góry
   
okonda
Poziom 4
Poziom 4


Dołączył: 03 Lis 2010
Posty: 9
Miasto: Wrocław

Post#3 Post autora tematu 11 Sty 2013 18:19   

Re: Excel - jak zrobić aby dwie kolumny przyporządkowały się indeksami


A czy mógłbyś zrobić to na przykładzie który przesłałem i wkleić przerobiony?
Powrót do góry
   
Google


Google Adsense


Post# 11 Sty 2013 18:19   





Powrót do góry
   
yogi009
Poziom 24
Poziom 24


Dołączył: 27 Sty 2006
Posty: 4982
Miasto: Olsztyn

Post#4 11 Sty 2013 18:22   

Re: Excel - jak zrobić aby dwie kolumny przyporządkowały się indeksami


Wolisz rybę od wędki? Jeżeli to to chodzi, napisz w temacie nie "Pomoc", tylko "Kto za mnie to zrobi".

Moje artykuły: (Dodaj)
16 Maj 2014 Kolejny prosty do wykonania zegarek LED
Powrót do góry
   
Google


Google Adsense


Post# 11 Sty 2013 18:22   





Powrót do góry
   
okonda
Poziom 4
Poziom 4


Dołączył: 03 Lis 2010
Posty: 9
Miasto: Wrocław

Post#5 Post autora tematu 11 Sty 2013 18:32   

Re: Excel - jak zrobić aby dwie kolumny przyporządkowały się indeksami


Mam już wędkę i stoję nad stawem, tylko nigdy nie dobierałem żyłki, nie montowałem kołowrotka ani nie zakładałem spławika, a co dopiero przywiązanie haczyka. Chciałbym się przyjrzeć nie tyle jak to się robi, ale także jak jest założone i następnym razem sam będę próbował przygotować wędkowanie.

Póki co wiem jak dojechać nad staw, rozłożyć biwak i grilla na rybę i wiem jak łowić, ale nigdy nie składałem wędki od podstaw. Umiem co najwyżej ją złożyć.

Nie pisałem nigdy funkcji i jestem w tym laikiem, stąd proszę o taką pomoc.

Myślę, że wędkowanie od Excela jest znacznie prostsze, a nie uczyłem się Excel-a, nie miałem informatyki i tyle co potrafię, wystarczało mi, a z tym nie daję rady, chodź z Twojego opisu jest to "niby" proste.

Może po obejrzeniu wpisanej funkcji, będę miał pierwszy przykład. Przyznam, że posiedziałem i czytałem dużo pomocy w Excel-u, ale zakończyło się to fiaskiem.
Powrót do góry
   
yogi009
Poziom 24
Poziom 24


Dołączył: 27 Sty 2006
Posty: 4982
Miasto: Olsztyn

Post#6 11 Sty 2013 19:14   

Re: Excel - jak zrobić aby dwie kolumny przyporządkowały się indeksami


Masz standardową funckję jeżeli() - możesz przeczytać w help'ie jaka jest jej składnia i oczekiwane parametry: tu są akurat trzy - oddzielone średnikami. Pierwszy to warunek logiczny, drugi: akcja jeśli warunek został spełniony, drugi: akcja jeśli nie został spełniony. Spróbuj coś zapisać, może tak dojdziemy do celu i przy okazji się nieco podszkolisz. Potem następne funkcje "wejdą" Ci łatwiej.

Moje artykuły: (Dodaj)
16 Maj 2014 Kolejny prosty do wykonania zegarek LED
Powrót do góry
   
marek003
Poziom 23
Poziom 23


Dołączył: 10 Wrz 2002
Posty: 3556
Miasto: Lublin

Post#7 11 Sty 2013 23:49pomocny post - solucja   

Re: Excel - jak zrobić aby dwie kolumny przyporządkowały się indeksami


Można z Jeżeli() (w sumie najprościej raz zrobići mieć z głowy) ale można też spróbować za pomocą wyszukaj.pionowo()

Podstawę zrobić w 2013 roku. Podobnie (ale trońkę inaczej) w 2012 tylko że w 2012 posortować wg "brak w 2013" i przekopiować "braki" do 2013 i właściwie po wszystkim.

Tylko że jeżeli autor pisze:
Cytat:
Nie pisałem nigdy funkcji i jestem w tym laikiem, stąd proszę o taką pomoc.


to może mieć kłopot z moim przykładem (zrozumieniem go) gdyż tu już nie ma "prostej" funkcji jeżeli() więc może od jeżeli() należało by jednak zacząć.

Zaznacze by znów się kolega nie obraził:
Bez urazy ale do "poważniejszych" funkcji w excelu dochodzi się z czasem a nie tak od razu na głęboką wodę bo się zniechęcisz do excela, a excel jest cymuś, tak jak matematyka :) - tylko trzeba wiedzieć jak go używać.

Załączniki:
Powrót do góry
   
okonda
Poziom 4
Poziom 4


Dołączył: 03 Lis 2010
Posty: 9
Miasto: Wrocław

Post#8 Post autora tematu 12 Sty 2013 03:03   

Re: Excel - jak zrobić aby dwie kolumny przyporządkowały się indeksami


Dziękuję za załączony przykład. Ale faktycznie jest z tym "jazda". Myślałem, że może być to na jednej zakładce gdzie w cenniku 2013 wskoczą pola "brak w 2013" a w 2012, tak jak jest "brak w 2012", ale jest super.

Przyznam, że czytałem niejednokrotnie Pomoc i za chiny nie potrafię zrobić najprostszej funkcji, pt. "=Jeżeli...", a fakt jest faktem, że gdybym potrafił zrobić początki, to już tylko dalej rozwijać :)
Nie ukrywam, że nigdy nie potrzebowałem Excel-a, chodź zawsze mnie fascynowały tabele, tzw. rozwinięte w którą gdzieś coś tam się wpisało, tu wyliczyło, tam pokazało, itp., a nie jestem dobrym samoukiem i w niektórych wypadkach, jak nie wytłumaczy mi ktoś i nie pokaże na jakich zasadach funkcjonuje, np. funkcje w Excel0u, to sam nie dojdę.
Przyglądałem się temu co zrobiłeś i przyznam, że nie mogę dojść skąd się to bierze i co jest do czego(w funkcji).
Dziękuję bardzo, wkleiłem resztę, przeciągnąłem funkcję i wszystko działa, ale ile bym nie patrzył na to to nie mam pojęcia, co z czym się je, a działa :) :D

Zapiszę to sobie, aby nie zepsuć, a w kopi porozkładam na "części" tak aby dojść, co odpowiada której wartości.

Teraz będę mógł to po kopiować, wkleić wartości w tabelę w j. polskim i myślę, że po trzech latach :) dojdę do ładu.

Jeszcze raz dziękuję :)

A czy da się zrobić coś z takimi tabelami, gdzie tak samo indeksy są identyczne, tylko w jednej tabeli są wpisane w ciągu 12345678901234567........., a drugiej tabeli oddzielone kreskami 12-34-567-890-123-456-7..-...-...-..., itd.. Dałem przykład na długim ideksie, ale tak to są różne 7;9;10;13-sto cyfrowe tylko, że w tym układzie: dwie-dwie-trzy-trzy-trzy-trzy, itd, w zależności od ilości cyfr.

Pewnie znowu pierdoła, ale dla mnie ciemna magia.
Powrót do góry
   
yogi009
Poziom 24
Poziom 24


Dołączył: 27 Sty 2006
Posty: 4982
Miasto: Olsztyn

Post#9 12 Sty 2013 13:54   

Re: Excel - jak zrobić aby dwie kolumny przyporządkowały się indeksami


Jeżeli jesteś mocno początkujący, rób te funkcje krokami, w kolejnych kolumnach. Na zakończenie kolumny pośrednie (techniczne) ukrywasz i masz ładną tabelkę z wynikiem. W ten sposób możesz łatwiej zrozumieć logikę zapisanych funkcji.

Moje artykuły: (Dodaj)
16 Maj 2014 Kolejny prosty do wykonania zegarek LED
Powrót do góry
   
Google


Google Adsense


Post# 12 Sty 2013 13:54   





Powrót do góry
   
Maciej Gonet
Poziom 18
Poziom 18


Dołączył: 10 Sty 2013
Posty: 597
Miasto: Gliwice

Post#10 14 Sty 2013 11:32   

Re: Excel - jak zrobić aby dwie kolumny przyporządkowały się indeksami


Próba odpowiedzi na problem Pana okondy. Ponieważ napisał Pan, że słabo zna Excela starałem się opisać wszystko dość dokładnie. Niestety, nie napisał Pan, której wersji Excela Pan używa, ale ponieważ załączony przykład jest w formacie .xls, więc przyjąłem, że jest to wersja 2003 i moje wyjaśnienia odnoszą się do tej wersji. W innych wersjach szczegóły rozwiązania są nieco inne. Moja propozycja rozwiązania przedstawionego problemu opiera się wyłącznie na Excelu bez użycia Visual Basica, chociaż rozwiązanie z jego użyciem też jest oczywiście możliwe. Uwzględniłem również duże rozmiary danych, których zaznaczanie w tradycyjny sposób byłoby niewygodne.

Proponuję następujące etapy rozwiązania:
1. W arkuszu źródłowym (lub arkuszach jeśli dane z obu katalogów są osobno) tworzymy nagłówki kolumn (bezpośrednio nad danymi), na przykład Nr_artykułu1, Opis1, Cena1, Nr_artykułu2, Opis2, Cena2. Nagłówki muszą spełniać wymagania dla nazw w Excelu.
2. Nadajemy te nazwy odpowiednim kolumnom, osobno dla 3 pierwszych i 3 następnych kolumn (bo mogą mieć różne długości). W tym celu:
a) zaznaczamy trzy pierwsze nagłówki (Nr_artykułu1, Opis1, Cena1), wciskamy klawisz Shift i klikamy myszką dwukrotnie na dolnej krawędzi zaznaczenia. W wyniku powinny zostać zaznaczone wszystkie komórki z danymi w tych trzech kolumnach;
b) przypisujemy nazwy do kolumn – w tym celu z menu wybieramy Wstaw/Nazwa/Utwórz/Górny wiersz (tylko to pozostawić zaznaczone)/OK;
c) powtarzamy punkty a) i b) w odniesieniu do trzech pozostałych nagłówków (Nr_artykułu2, Opis2, Cena2).
3. Przechodzimy do miejsca. gdzie ma być utworzony połączony katalog. Może to być w tym samym arkuszu, ale lepiej wykorzystać nowy arkusz. Umieszczamy kursor w komórce, gdzie ma być początek zestawienia.
4. Przeprowadzamy połączenie (konsolidację) danych. Konsolidacja w Excelu została zaplanowana nieco inaczej niż tu potrzeba, więc jej wynik wykorzystamy tylko częściowo.
Z menu Dane wybieramy Konsoliduj i wypełniamy okno dialogowe. W pozycji Funkcja można pozostawić Suma (nie ma to znaczenia, bo te wyliczenia i tak później skasujemy). W pozycji Odwołanie wpisujemy kolejno Nr_artykułu1:Cena1 (w środku dwukropek) i klikamy Dodaj, potem Nr_artykułu2:Cena2 i znowu Dodaj. W polu Użyj etykiet w: zaznaczamy Lewa kolumna i klikamy OK. Ważne, aby konsolidowane zakresy zawierały kolumnę liczbową (tutaj jest Cena1 i Cena2) inaczej nic z tego nie wyjdzie.
W wyniku konsolidacji otrzymamy trzy kolumny: pierwsza będzie zawierała wszystkie kody z kolumn Nr_artykułu1 i Nr_artykułu2 uporządkowane tak, by nic się nie powtarzało, druga będzie pusta (teksty się nie sumują), a trzecia będzie zawierać sumy odpowiadających sobie cen (tę kolumnę trzeba będzie usunąć).
5. Aby usunąć trzecią kolumnę po konsolidacji zaznaczamy jej pierwszą komórkę, przy wciśniętym klawiszu Shift klikamy dwukrotnie dolną krawędź zaznaczonej komórki (to powinno spowodować zaznaczenie całej kolumny danych). Zanim wciśniemy klawisz Delete, aby skasować dane wykorzystamy to, że po zaznaczeniu widoczny jest koniec danych. Numer ostatniego wiersza należy zapisać lub zapamiętać, bo za chwilę będzie potrzebny. Teraz można już wcisnąć Delete.
6. W pierwszym wierszu zakresu wynikowego (obok pierwszego numeru artykułu) w czterech kolumnach wpisujemy formuły zwracające nazwy i ceny artykułów z obu cenników. Kolejność może być dowolna – założyłem, że będzie: Opis1, Cena1, Opis2, Cena2. Odpowiednie formuły mogą mieć jedną z dwu postaci (dają identyczne wyniki).
Wariant 1:
=INDEKS(Opis1;PODAJ.POZYCJĘ($A4;Nr_artykułu1;0))
=INDEKS(Cena1;PODAJ.POZYCJĘ($A4;Nr_artykułu1;0))
=INDEKS(Opis2;PODAJ.POZYCJĘ($A4;Nr_artykułu2;0))
=INDEKS(Cena2;PODAJ.POZYCJĘ($A4;Nr_artykułu2;0))
Wariant 2:
=WYSZUKAJ.PIONOWO($A4;Nr_artykułu1:Cena1;2;FAŁSZ)
=WYSZUKAJ.PIONOWO($A4;Nr_artykułu1:Cena1;3;FAŁSZ)
=WYSZUKAJ.PIONOWO($A4;Nr_artykułu2:Cena2;2;FAŁSZ)
=WYSZUKAJ.PIONOWO($A4;Nr_artykułu2:Cena2;3;FAŁSZ)
W powyższych formułach $A4 oznacza adres komórki zawierającej pierwszy Nr_artykułu (z tego samego wiersza do którego wpisano formuły) – musi to być oczywiście aktualny adres.
7. Formuły trzeba teraz powielić w całym zakresie wyników. W tym celu zaznaczamy wpisane w poprzednim punkcie cztery formuły i kopiujemy je do schowka na przykład przez Ctrl+C. Skopiowany zakres powinien być otoczony linią przerywaną.
8. Zaznaczamy obszar docelowy do kopiowania. Najpierw zaznaczamy jego lewą górną komórkę (pod pierwszą wpisaną formułą), następnie naciskamy Ctrl+G (przejdź do) i w polu Odwołanie wpisujemy adres prawej dolnej komórki (litera odpowiada czwartej kolumnie z formułami, a numer ostatniego wiersza zapisaliśmy w punkcie 5). Wciskamy Shift+OK, aby zaznaczyć cały obszar.
9. W końcu wklejamy formuły ze schowka na przykład przez Ctrl+V. Gdyby formuły kopiowane nie były otoczone linią przerywaną, operacja się nie uda i trzeba powtórzyć procedurę od punktu 7.

Zauważyłem, że mimo posortowania oryginalnych danych, ich kolejność po konsolidacji nie jest ściśle alfabetyczna, można więc poddać je jeszcze sortowaniu. Wystarczy posortować kolumnę z numerami artykułów. Można to zrobić zaraz po konsolidacji lub później po wpisaniu formuł.
W wyniku otrzymamy zestawienie pozycji z obu katalogów. W miejscach gdzie brak odpowiednich danych Excel wpisuje kod #N/D! Zamaskowanie tego kodu w Excelu 2003 jest możliwe, ale dość niewygodne. W Excelu 2007 jest już dostępna funkcja JEŻELI.BŁĄD, która pozwala zamaskować kody błędów.
W Excelu 2003 jest kilka sposobów zamaskowania kodów #N/D!. W każdej z nich jednak stosunek nakładu pracy do efektu jest niezbyt korzystny. Stosunkowo najłatwiej przeprowadzić takie maskowanie przy wydruku. Należy w tym celu ustawić odpowiednią opcję. Z menu Plik należy wybrać Ustawienia strony/Arkusz/Drukuj/Błędy komórek jako i tu do wyboru mamy <puste> (nic nie drukuje) albo – (kreski).
Druga możliwość to zamiana formuł z wynikami na wartości, a następnie zamiana tekstu #N/D! na tekst pusty. Trzecia możliwość to wykorzystanie formatowania warunkowego i wyświetlenie kodów błędu białą czcionką na białym tle. Czwarta możliwość to wykorzystanie funkcji CZY.BŁĄD, ale musi to prowadzić albo do dwukrotnego wydłużenia formuł, albo trzeba użyć formuł nazwanych (jest to dość egzotyczna, mało znana opcja) albo trzeba zduplikować każdą kolumnę wynikową (maskując błędy w kopii), a następnie ukryć wersje kolumn z kodami błędów.
W załączniku jest arkusz przykładowy, w którym wykonano opisane czynności. Szczegółów maskowania błędów nie opisuję, bo może nie będzie to istotne.

Załączniki:
Powrót do góry
   
Napisz nowy temat  Odpowiedz do tematu      Strona Główna -> Forum elektroda.pl -> Komputery Serwis -> Komputery Software -> Excel - jak zrobić aby dwie kolumny przyporządkowały się indeksami
Strona 1 z 1
Podobne tematy
Firefox i win XP. Jak zrobić, aby otwierały się linki z innych programów? (4)
Czytanie CD. Jak zrobić, aby przy włączaniu komputera wczytywała się płyta CD? (7)
Windows 2003. Jak zrobić, aby mogło się logować kilka osób? (2)
Excel - wiersze, które się nie przewijają - jak to zrobić??? (2)
PowerPoint. Jak zrobić, aby otwierał się sam pokaz slajdów? (4)
jak zrobić aby komputr bez dysku się włączył ... (9)
Jak zrobić aby komputer sam się włączył (4)
Jak zrobić aby logować się do stron z kontami. (3)
Jak zrobic aby w excel ponumerował rosnąco czasy (3)
excel jakiej użyć formuły aby obliczyć jak często występuje (3)


Administrator || Moderatorzy || Regulamin forum || Regulamin ogólny || Informacja o cookies || Reklama || Kontakt

Page generation time: 0.058 seconds

elektroda.pl temat RSS