Porównanie 2 arkuszy i kopiowanie zawartości pomoc z regułą

[Vako99]

Witam,

Mam cennik arkusz A i zamówienie arkusz B
Różnica między plikami jest taka że w cenniku nie mam kolumny ilość - zamówienie,
którą muszę przenieść z zamówienia do cennika wyszukując produkty po kodach.
Tworze jeden plik i do zakładki list1 wklejam cennik a do drugiej zakładki list2 wklejam zamówienie.
Następnie potrzebuję aby skrypt wyszukał po kodzie produktu towar w cenniku na podstawie zamówienia
i następnie do cennika przeniósł zawartość pola ilość czyli nasze zamówienie. Dla przykładu

Zakładka List1 (nasz cennik) będzie mieć kolumny

A-kod
B-nazwa
C-cena

Zakładka list2 (nasze zamówienie) będzie mieć kolumny:

A-kod
B-nazwa
C-cena
D-ilość

Jaka powinna brzmieć reguła aby wykonała taką czynność.
Jeśli znajdziesz dowolny kod z kolumny A list2 w zakładce list1 w kolumnie A to
skopiuj zawartość pola D... z list2 i wklej do Pola D... w list 1.

[Rafkus]

To może być np. funkcja WYSZUKAJ.PIONOWO, do zakładki list2, komórki B2 wpisz:

Kod: Zaznacz cały

=WYSZUKAJ.PIONOWO(A2;  $list1.$A$2:$C$1000;  2;  0)

do zakładki list2, komórki C2 wpisz:

Kod: Zaznacz cały

=WYSZUKAJ.PIONOWO(A2;  $list1.$A$2:$C$1000;  3;  0)

W pomocy funkcja ta jest dobrze opisana, polecam ją przeczytać...
Możesz również zobaczyć poniższy link, a zwłaszcza plik abc:
https://forum.openoffice.org/pl/forum/v ... f=9&t=5199

[Vako99]

Witam,

Coś nie działa, nie mamy kopiowania z zakładki list2 wartości z kolumny D (ilość zamówienia) do zakładki list1 do kolumny D.
Po tym jak funkcja porówna kody z kolumny A powinna skopiować zawartość z kolumny D i przenieść do zakładki list 1
dla przykładu według schematu zakładka list1mamy kolumny A-kod , B-nazwa, C-cena - to jest nasz cennik

111;xxx;12zł
222;yyy,10zł
444,ccc,2zł

w zakładce list2 mamy kolumny A-kod , B-nazwa, C-cena, D-ilość - to nasze zamówienie gdzie mamy dodatkową kolumę D z ilością zamówionych produktów.

111;xxx;12zł;200
333;bbb,5zł;10
444,ccc,2zł;55

funkcja sprawdza po kodach i znajduje pierwszy produkt bo ma ten sam kod "111" następnie do zakładki list1 w kolumnie D1 dopisuje wartość 200 - ilość z zamówienia.
Następnie kod "333" nie znajduje więc nic nic nie dopisuje do D2. Dalej znajduje kod 444 więc w D3 dopisuje wartość 55. Chodzi mi o taki schemat. Oczywiście to najprostszy przykład opisany aby był widoczny schemat działania. W zamówieniu może być np 10 pozycji a cennik będzie miał 1000 więc zawsze szukamy po kodzie produktu z zamówienia i przenosimy ilość zamówionych produktów do cennika.

[Vako99]

Witam,

Coś nie działa, nie mamy kopiowania z zakładki list2 wartości z kolumny D (ilość zamówienia) do zakładki list1 do kolumny D.
Po tym jak funkcja porówna kody z kolumny A powinna skopiować zawartość z kolumny D i przenieść do zakładki list 1
dla przykładu według schematu zakładka list1mamy kolumny A-kod , B-nazwa, C-cena - to jest nasz cennik

111;xxx;12zł
222;yyy,10zł
444,ccc,2zł

w zakładce list2 mamy kolumny A-kod , B-nazwa, C-cena, D-ilość - to nasze zamówienie gdzie mamy dodatkową kolumę D z ilością zamówionych produktów.

111;xxx;12zł;200
333;bbb,5zł;10
444,ccc,2zł;55

funkcja sprawdza po kodach i znajduje pierwszy produkt bo ma ten sam kod "111" następnie do zakładki list1 w kolumnie D1 dopisuje wartość 200 - ilość z zamówienia.
Następnie kod "333" nie znajduje więc nic nic nie dopisuje do D2. Dalej znajduje kod 444 więc w D3 dopisuje wartość 55. Chodzi mi o taki schemat. Oczywiście to najprostszy przykład opisany aby był widoczny schemat działania. W zamówieniu może być np 10 pozycji a cennik będzie miał 1000 więc zawsze szukamy po kodzie produktu z zamówienia i przenosimy ilość zamówionych produktów do cennika.

[Jermor]

@Rafkus napisał ci formułę dodając uwagę abyś przeczytał opis funkcji. Gdybyś to zrobił, to zorientowałbyś się, że pomylił "kierunek" kopiowania. To w arkuszu List1 w komórce A2 powinna powstać formuła

Kod: Zaznacz cały

=WYSZUKAJ.PIONOWO(A2;  $list2.$A$2:$C$1000;  4;  0)

Pamiętaj, że funkcja zwróci wartość błędu #N/D gdy w cenniku będzie pozycja, której nie ma w zamówieniu. W przytoczonym przez ciebie przykładzie taki wynik pojawi się w pozycji 222, gdyż taka pozycja nie wystąpiła w zamówieniu.

[Rafkus]

że pomylił "kierunek" kopiowania.

To rzecz sporna, proponowałem ściągnięcie danych z bazy Cennika do Zamówienia, co według mnie jest poprawne.
Natomiast @ Vako99 chce dane z Zamówienia przekazać do bazy Cennika.
Teraz pytanie:
Czy w zamówieniu może pojawić się dwa razy ten sam produkt? Jeśli tak to którą ilość należy przekazać do Cennika??

[Vako99]

Dziękuję Panowie pomału zaczyna wszystko działać.
Na testowym pliku jest ok ale jak zacząłem pracować na cenniku i zamówieniach to pojawił się chyba problem z formatowaniem komórek i nie znajduje mi
kodów. Jak z list1 skopiuje kod i wyszukam go na list2 a następnie wkleję w to samo miejsce to reguła działa. W drugą stronę podobnie.
To problem z formatowaniem kolumn czy musi być jakaś kolejność działania i coś źle robię ? Próbowałem ponownie wklejać całe kolumny z kodami ale to nie działa.

Finalnie używam kodu

Kod: Zaznacz cały

 =WYSZUKAJ.PIONOWO(A2;  $list2.$A$2:$D$1000;  4;  0)

i wklejam go w list1 w kolumnie D2 gdzie ma pojawić się ilość z zamówienia.

[Jermor]

Do @Rafkus - oczywiście miałem tę samą wątpliwość. Po co do cennika dogrywać ilość? To raczej do zamówienia należy dograć cenę (bo to cena może się zmieniać, np. na skutek zmian kursu walut). Ale tak to napisał @Vako99.
Do @Vako99. Przyczyną takich zachowań jest to, że kody w zamówieniach różnią się od tych w cenniku. Nie napisałeś tego w poście, sugerując, że kodem jest prosty ciąg cyfr 111, 222 lub 333 będący w istocie liczbą. W rzeczywistości jest na pewno inaczej. Kod nie jest zapewne liczbą, lecz jakimś ciągiem w postaci 999-333-444 lub coś takiego. Taki ciąg nie jest liczbą. Cennik przygotował ktoś wpisując odpowiednie kody. Zamówienie przygotowuje ktoś inny. Skąd bierze kody? Z cennika? W jaki sposób je wstawia? Wystarczy, że zamiast kodu 111-11 wpisze: spacja 111-11 albo 111-11 i spacja. To będą już kody niewystępujące w cenniku. Oczywiście może być i tak, że w cenniku ktoś wpisał kod ze spacjami a w zamówieniu już nie. Musisz zatem pomyśleć nad normalizacją kodu. Jak to zrobić, to zależy od budowy samego kodu. Jedną z prostych metod jest pozbycie się wszystkich niepotrzebnych spacji. Jest do tego funkcja USUŃ.ZBĘDNE.ODSTĘPY. Usuwa ona wszystkie spację początkowe i końcowe znajdujące się w ciągu tekstowym oraz zamienia wszystkie wielokrotne spacje wewnątrz ciągu na pojedynczą spację.

[Rafkus]

Jakie jest to formatowanie?
Czy jest możliwość, że gdzieś przyplątała się jakaś dodatkowa spacja? np: kod: "111_" (_ - symbolizuje spację) nie będzie równy "111"
Czy wielkość liter jest jednakowa? np. kod: "abc" to nie to samo co "ABC"

Opis funkcji:
=WYSZUKAJ.PIONOWO(szukaj_wartość; Macierz; Indeks; Kolejność_sortowania)

szukaj_wartość - tu należy podać czego szukamy lub która komórka zawiera poszukiwaną wartość
Macierz - jest to zakres danych źródłowych. Zawsze w pierwszej kolumnie tej macierzy jest poszukiwana szukaj_wartość
Indeks - oznacza numer kolumny z macierzy zawierającej wartość, która ma być zwrócona. Pierwsza kolumna ma numer 1.
Kolejność_sortowania - 0 oznacza że macierz może nie być posortowana, i zwrócona ma zostać wartość dla dokładnego dopasowania szukaj_wartość

Przypuszczam, że raczej źle używasz tej funkcji. Czy mógłbyś pokazać (dołączyć plik z) dokładny schemat twojego arkusza z paroma przykładowymi danymi?

[Vako99]

kody są identyczne to są kody ean bez znaków specjalnych same liczby np 3401399694295
Ilości muszę przenosić do cennika ponieważ wiele firm ma cennik który jest od razu plikiem do importu i nie możemy nic zmieniać jedynie dopisać ilości zamawiane bo jak zmienimy strukturę pliku to nie nagramy go do zewnętrznego systemy. Może coś źle zrozumiałem czy powinny być 2 formuły użyte ? Ja użyłem tylko jedną i działa ale tak jak pisałem wcześniej dopiero reguła znajdzie kody kiedy skopiuję go z jednego arkusza do drugiego ponownie obojętnie w którą stronę. Musi być problem z formatowaniem kolumn albo coś źle robię.

[Jermor]

Moim zdaniem kody nie są takie same. Obstawiam taki scenariusz: cennik jest importowany i kody EAN są wstawiane jako teksty. Nie znam się na kodach EAN tak dogłębnie, ale jeśli pierwszą cyfra takiego kodu może być 0, to musi być wprowadzany jako tekst, gdyż prowadzące zera są usuwane..
Zamówienia prawdopodobnie mają ten kod wprowadzany jako liczbę.
Można to sprawdzić funkcjami =CZY.TEKST() lub =CZY.LICZBA()
Liczby wpisane jako tekst mogą być widziane na dwa sposoby:

• Gdy komórka ma formatowanie liczbowe, taki ciąg widoczny jest jako ciąg cyfr poprzedzony apostrofem. Widać to tylko w wierszu wprowadzania.
• Gdy komórka ma przypisany format tekstowy ciąg cyfr zachowuje się jak tekst. Gdy takiej komórce przywróci się format liczbowy, to zawarta w niej "tekstowa" liczba nadal pozostanie tekstem, tyle, że poprzedzonym znakiem apostrofu.

Aby porównać takie wartości należy jeden z kodów przekształcić na liczbę.
Jeśli cennik zawiera kody tekstowe a zamówienia liczbowe to formuła powinna brzmieć:

Kod: Zaznacz cały

=WYSZUKAJ.PIONOWO(WARTOŚĆ(A2);  $list2.$A$2:$D$1000;  4;  0)

Jeśli jest na odwrót:

Kod: Zaznacz cały

=WYSZUKAJ.PIONOWO(A2;  WARTOŚĆ($list2.$A$2:$D$1000);  4;  0)

Tę formułę należy zatwierdzić jako formułę macierzową i jako taką skopiować do pozostałych komórek w kolumnie.

[Vako99]

Jermor » miałeś rację, chodziło o formatowanie Miałem ustawione formatowanie niby właściwie ale pewne rzeczy robiłem w złej kolejności i kolega Rafkus pokazał mi gdzie są błędy.
Dziękuję Panowie za pomoc.

[Rafkus]

Pozwolę sobie wyjaśnić co było problemem, może pomoże to komuś innemu:
Otrzymałem od @Vako99 przykładowy plik z danymi i w nim było tak:

• - W arkuszu List1 prawie wszystkie kody EAN były zdefiniowane jako liczby
  - W arkuszu List2 prawie wszystkie kody EAN były zdefiniowane jako tekst

Poleciłem aby wyciąć te dane, a następnie wkleić je ponownie jako niesformatowany tekst, w oknie Import tekstu typ kolumny oznaczyć jako tekst.