Excell - formuła szukająca danych




Sharoop - 25 Lip 2007 12:31
Witam. Mam następujący problem. Potrzebuję formuły która z wyznaczonej kolumny będzie w stanie wyszukać komórkę (a najlepiej cały wiersz w którym ta komórka sie znajduję) za pomocą zawartości. Inaczej mówiąc: mamy listę zawierającą różne produkty (każdy wiersz to produkt i jego szczegóły) potrzebuję do innego arkusza ściągnąć niektóre wiersze z pierwszego korzystając z pola zawierającego nazwę lub nr. katalogowy. Nie jestem pewien czy trzeba to zrobić jako bazę danych, czy może jest funkcja szukająca konkretnego ciągu znaków
Za pomoc z góry dziękuję



marek003 - 25 Lip 2007 13:09
Do tego celu możesz wykorzystać dwie funkcje wyszukaj.pionowo i indeks.
Poradzisz sobie czy dać ci przykład ??? (najlepiej jak byś zamieścił kawałek twojej bazy wtedy byś miał na gotowo)


Sharoop - 25 Lip 2007 14:34
dokładna baza jest dopiero tworzona ale mniej więcej ma to wyglądać tak:
2 arkusze - w pierwszym duża ilość produktów, przy każdym jakiś nr. katalogowy itp., cena i kilka innych zbędnych kolumn. Drugi arkusz ma zawierać część produktów z pierwszego, dodatkowo linia będzie krótsza (mniejsza ilość kolumn) Chodzi mi o to aby excell zaktualizował 2 arkusz, wyszukując w pierwszym konkretny produkt i jeśli jego cena różni się od tej w arkuszu aktualizowanym podmienił tą drugą.
W Excellu jestem jak widać zielony wiec nie za bardzo wiem które konkretnie komórki mam adresować Konkretne adresy komórek jak przypuszczam dam radę dopasować, ale bardzo prosiłbym o przykład:
zakładam takie ułożenie arkusza
kol. A - nr. katalogowy B - nazwa produktu C - jakaś niepotrzebna kolumna D - cena
w kolejnych liniach umieszczane będą poszczególne produkty

Mam nadzieje że przedstawiłem to jako-tako jasno i czytelnie

Dodano po 2 [minuty]:

aha, no i zapomniałem. w 2 arkuszu: A - nr. katalogowy B - nazwa produktu C - cena
Jaka formuła porówna produkty wyznaczając powtarzający się w obu arkuszach i zaktualizuję np. cenę w drugim na podstawie pierwszego


marek003 - 26 Lip 2007 14:50
Taki na szybko przykład (łatwiej by było zrozumieć z cenami i nie tylko gdybyś ty zrobił przykład katalogu i główne dane).

Nie wiem czy dobrze cię zrozumiałem z tymi cenami,
ale funkcje wyszukiwania ustawione sa na cały arkusz 1. Mają też trochę zabezpieczeń jak głupotę wpiszesz. Możesz też kopiować przeciągając komórki - po to sa dolary przy odwołaniu do poszczególnych adresów.
Funkcje są w kolorowych komórkach

Jeżeli jest to skomplikowane skorzystaj z pomocy na temat samej funkcji: wyszukaj.pionowo.

Jeżeli wpiszesz numer w pierwszej kolumnie to "automatycznie" pojawią się dane w pozostałych (nie wiem czy o to ci chodziło).

W sumie jak by trzeba było porównywać 1 arkusz z drugim to wynik porównania warto jednak wyrzucić w trzecim arkuszu z pomocą tych samych formuł.



Sharoop - 27 Lip 2007 12:15
Właśnie o coś takiego chodziło!

Jeszcze tylko taki drobiazg, bo nie do końca mi pasują szczegóły. Zastosowana tam formuła:

=JEŻELI($A9="";"";JEŻELI(CZY.BŁĄD(WYSZUKAJ.PIONOWO($A9;$Arkusz1.$A$1:$IV$65536;B$2;0));"brak asortymentu";WYSZUKAJ.PIONOWO($A9;$Arkusz1.$A$1:$IV$65536;B$2;0)))

Fragment na czerwono... nie wiem dlaczego ale nie do końca pokazuje to co wydaje mi się że pokazać powinien.
tj. przy kolumnie np. B zwraca zawartość komórki z kolumny G. Gdzie tu jest haczyk ?

Aha, i do czego odnoszą sie te cudzysłowy na początku? ($A9="";""


marek003 - 30 Lip 2007 15:24
W wierszy drugim (2 na tym arkuszu)[ B$2, C$2.... ]wpisałem numery kolumn z jakich ma pobierać dane. Jeżeli zmienisz ten numer (w wierszu drugim) zmienią sie dane - będą pobierane z kolumny o numerze jaki podałeś. Jeżeli to ci przeszkadza wpisz na stałe zamiast B$2 C$2 itd numer kolumny z arkusza pierwszego. To było dodane tak dla zautomatyzowania.
Zauważ że jest on w dwóch miejscach całej formuły.

Przeczytam ci tą formułę po polsku to może będzie jaśniej.

Jeżeli A9 jest puste [A9=""] wtedy [;]wpisz puste ("") w przeciwnym wypadku[;] jeżeli błąd formuły wyszukującej (w dalszej części rozwinięcie tej formuły) -czyli nie znajdzie takiego indeksu- wtedy [;] wpisz "brak asortymentu" w przeciwnym wypadku [;] wyszukaj pionowo daną podaną w a9 [;] w pierwszej kolumnie tabeli obejmującej cały arkusz 1 [;] jak ją znajdziesz to z tego wiersza podaj dane z kolumny numer ... podanej w B2 [;] szukaj dokładnie (0) ))))

Mam nadzieje że jasno.

Jeżeli masz kłopot z daną formułą to stań na niej kliknij myszą w nią na pasku formuł i kliknij w przycisk przy pasku formuł "fx" . Wtedy wyświetli ci się dokładny opis tej formuły którą zaznaczyłeś myszką (w całej formule funkcja będzie pogrubiona) tam możesz też nacisnąć pomoc na temat tej funkcji z przykładami.

Dodano po 4 [godziny] 9 [minuty]:


tj. przy kolumnie np. B zwraca zawartość komórki z kolumny G. Gdzie tu jest haczyk ?



Tam gdzie jest wpisane B$2 zwraca zawartość drugiej kolumny [kolumny b] z arkusza 1. Ponieważ w komórce B2 arkusza 2 jest wpisane 2

Tam gdzie jest wynik z kolumny G arkusza1 czyli siódmej kolumny - w formule jest odwołania C$2. A w c2 arusza 2 jest wpisane 7. Sprawdź.


Sharoop - 01 Sie 2007 12:18
No teraz załapałem skąd co się bierze
Jednak staram się to przerobić na konkretny już przykład, jak na razie z mizernym efektem
Jak by ta formuła miała wyglądać w następującym przypadku (dodam że główny problem to to, iż program sprawdza zawartość podanej komórki i wyrzuca zawartość tej kolumny (z konkretnego wiersza) który jest podany w komórce. Co muszę wpisać aby od razu wskazać numer kolumny z której mają być brane dane. Wpisując np. 2 lub $2 wyskakuje błąd...
No i ostatnia sprawa, fajnie by było gdyby aktualizując cenę od razu dodało marżę powiedzmy 7%

Dorzucam przykładową bazę danych zawierającą arkusze aktualizujący i aktualizowany. Kolumny pozostaną już bez zmian, oczywiście przybędzie produktów, ale zakładam ze w takim przypadku wystarczy już tylko skopiować komórkę w dół


marek003 - 01 Sie 2007 12:47
Po obejżeniu twojej bazy nie za bardzo rozumiem o co ci chodzi.
W arkuszu1 masz zestawienie produktów a w arkuszu2 masz całkiem inne dane. W co chcesz wrzucić (w jaka kolumnę) dane z pierwszego arkusza do arkusza2? Co chcesz porównywac ze sobą?? Czy wyniki maja być w arkuszu3?
W przesłanym skoroszycie zrób na razie "ręcznie" jak chcesz żeby to wyglądało docelowo i zaznacz kolorem komórki które powinny sie automatycznie aktualizować - wtedy będzie prosciej zrozumieć.

Kolejna rzecz: czy może być unikalny numer produktu w arkuszu1 w pierwszej kolumnie ? Bo jeżeli nie to formuła sie bardziej skąplikuje niemniej też da sie zrobić.

Doliczenie 7% to nie probem tylko gdzie? w której kolumnie? opisz ją w nagłówku.

Jeszcze raz wytłumaczenie formuły

wyszukaj.pionowo(a;b;c;d) [masz to w helpie offica]

a - poszukiwana wartość
b - obszar danych do przeszukania przyczym poszukiwana wartość musi być w pierwszej kolumnie inaczej zwróci błąd.
c - numer kolumny z której pobrać dane.
d - precyzja szukania (1 - może byc coś podobnego do "wartości" podanej jako "a", 0- musi być dokładnie to co figuruje w wformule jako "a")

----------------------------------
Co do pytania o nr kolumny:
Zamiast $b2 wpisujesz cyfrę np 2 bez $. (nie skasuj czasem znaków ; )
Sprawdz przesłany arkusz - nie powinno być błędu.


Sharoop - 02 Sie 2007 11:51
No faktycznie zamieszałem...
To jeszcze raz po woli. Na czerwono zaznaczam komórki które mają być porównane (to co jest w 2 arkuszu musi być wyszukane w 1)
Żółte komórki to ceny, ta która jest w 1 arkuszu jest właściwa i powinna być skopiowana do arkusza 2) mam nadzieję że teraz jest jaśniej


Sharoop - 03 Sie 2007 13:32
Ok, z adresowaniem sobie poradziłem!!! dzięki za pomoc ale mam jeszcze kilka innych zagadek...
1. marża do ceny (powiedzmy 3%) w którym miejscu formuły =JEŻELI($A2="";"";JEŻELI(CZY.BŁĄD(WYSZUKAJ.PIONOWO($A2;$Arkusz1.$E$2:$IV$65536;3;0));"brak asortymentu";WYSZUKAJ.PIONOWO($A2;$Arkusz1.$E$2:$IV$65536;3;0))) trzeba coś dodać żeby podwyższał wpisaną wartość o 3% (przypuszczam ze ma to wyglądać ...*1,03 )

kolejny problem to sprawdzanie stanu magazynowego i wpisywanie "active"/"inactive" w odniesieniu do zawartości odpowiedniej komórki
Na załączonym przykładzie:
zielona komórka to podstawowa nazwa do porównania arkusza 1 z 2 (to już mam-działa) komórka żółta, po odszukaniu odpowiedniego wiersza w arkuszu1 powinna być podmieniona na na wartość z arkusza 1 (też żółta, też już działa) ale chciałbym dodać do niej np 3% marży (arkusz1 + 3%)
I najciekawsze. Komórka niebieska w arkuszu 2 może mieć 2 wartości - dobierane w odniesieniu do stanu magazynowego z arkusza1 (również niebieska komórka)
W arkuszu 1 możliwe wartości to: cyfry od 0 do 30 oraz "powyżej 30"
Jeżeli wartość w arkuszu 1 będzie równa 0 - w arkuszu 2 powinno być wstawione "inactive" w każdym innym przypadku "active"
Chyba tyle:) z góry dzięki za pomoc!!!


marek003 - 06 Sie 2007 12:48
Zaczne od tyłu:

Wprowadziłem ci dwa "rozwiązania" active i inactive. Zwróć uwagę na różnice w formule (jaka wartość jest sprawdzana) i wybierz odpowiednią dla siebie.

Dodałem wyszukiwanie nazwy z pierwszego arkusza za pomocą formuły "indeks" [indeks(a;b;c) gdzie a - przeszukiwana tabela; b - nr wiersza; c - numer kolumny.
Przy czym numer wiersza w tym przypadku pobierany jest za pomocą funkcji podaj.pozycje(d;e;f) gdzie d szukana wartość, e - kolumna (w tym przypadku), f - dokładność szukania ].

I najważniejsza rzecz:
wstawiłem ci w formułę 1,03 (marża na sztywno a może uzależnić od innej komórki gdzie byś ja zmieniał )
Ale....
Jeżeli chcesz cos liczyć to musi to być liczbą. Wartość podana z . (kropką) dla polskiego excela nie jest wartością. Musisz to zamienić na , (przecinek). Jeżeli jest to pobierane z jakiejś bazy zaznacz dwie kolumny z cenami w arkuszu 1 i wciśnij ctrl+H i zmień kropkę na przecinek.


Sharoop - 08 Sie 2007 12:15
działa dzięki za pomoc!!!