[SOLVED] Mapowanie kolumn po tytule kolumny (nagłówka)

[jarecki2222]

Witam

Proszę o pomoc w temacie.

Próbowałem użyć funkcji "WYSZUKAJ.POZIOMO" ale nie wiem jak ją skopiować do komórek poniżej.

Może jest jakieś prostsze rozwiązanie na zmapowanie arkusza po tytule kolumny (nagłówka).

Załączam plik z przykładem i 2 rozwiązaniami mojego problemu wg wskazówek z poniższych postów.
Prawdopodobnie rozwiązania są poprawne...?

[Jermor]

Po pierwsze, co rozumiesz przez zmapowanie? Czy to aby w nowym arkuszu otrzymać kolumnę nazwisk i imion w takiej samej kolejności jak w arkuszu "Kolumny do zmapowania"?
Jeżeli tak, to wystarczy w arkuszu "Zmapowane" w komórce A2 wpisać formułę:

Kod: Zaznacz cały

=$'kolumny do zmapowania'.E2

a w komórce B2 formułę:

Kod: Zaznacz cały

=$'kolumny do zmapowania'.C2

i obie te komórki skopiować w dół.
Funkcja WYSZUKAJ.POZIOMO, jak i WYSZUKAJ.PIONOWO w pierwszym argumencie otrzymuje informacje CO ma zostać wyszukane, w pewnej tabeli, która jest drugim argumentem tych funkcji. To CO będzie wyszukiwane w pierwszym wierszu wspomnianej tabeli (gdy funkcją jest WYSZUKAJ.POZIOMO) albo w pierwszej kolumnie tej tabeli (gdy funkcją jest WYSZUKAJ.PIONOWO). Gdy CO zostanie znalezione, to funkcja ma zwrócić zawartość tego wiersza (albo tej kolumny) którego numer jest podany jako trzeci argument, na pozycji, na której znaleziono CO. Sposób wyszukiwania zależy od czwartego argumentu. Wartość 0 oznacza, ze wiersz (kolumna) nie są posortowane i poszukiwane CO musi zostać znalezione, gdyż jeśli go nie będzie funkcja zwróci błąd #N/D.
Jeśli z książki telefonicznej masz podać numer do Malinowskiego, to CO=Malinowski, Tabela to książka telefoniczna, W pierwszej kolumnie tej książki szukasz Malinowski, gdy znajdziesz to w kolumnie np. drugiej znajduje sie jego numer i to ma ci zwrócić funkcja, w tym przypadku WYSZUKAJ.PIONOWO("Malinowski";Książka telefoniczna; 2;0) Jeśli malinowsj=ki nie ma telefonu, otrzymasz #N/D.

[jarecki2222]

Dziękuję za odpowiedź i przepraszam, że nie umiem dokładnie wyrazić co mam na myśli.
Chodzi mi o co innego.
Mam na myśli, że w arkuszu źródłowym (kolumny do zmapowania) kolumna z nagłówkiem "Imię", "Nazwisko" będzie pojawiać sie w różnych miejscach (A, B, ..., X, Y, Z)
Oczekuję, że w arkuszu wyjściowym (zmapowane) kolumna z nagłówkiem "Imię", "Nazwisko" pojawi się pod wybranym prze ze mnie adresem (kolumna A "Nazwisko", B "Imię").

W zasadzie funkcja z mojego przykładu działa.
Nie wiem jak ją powielic o powiedzmy 400 wierszy w dół:

=WYSZUKAJ.POZIOMO(A$1;'kolumny do zmapowania'.$A$1:$G$400;2;0)
=WYSZUKAJ.POZIOMO(A$1;'kolumny do zmapowania'.$A$1:$G$400;3;0)
...
=WYSZUKAJ.POZIOMO(A$1;'kolumny do zmapowania'.$A$1:$G$400;399;0)
=WYSZUKAJ.POZIOMO(A$1;'kolumny do zmapowania'.$A$1:$G$400;400;0)

Czy duża ilośc komórek z taką funkcją (4000 - 10000) nie spowolni pracy na arkuszu?

[Jermor]

Nadal nie wiem, czy dobrze to rozumiem. W arkuszu "kolumny do zmapowania" będzie wstawiana tabela, która we wszystkich wierszach ma taki sam układ, lecz położenie kolumny "Nazwisko" oraz kolumny "Imię" może być w tych wstawianych tabelach w różnym miejscu. Należy odnaleźć te miejsca a następnie przenieść wszystkie nazwiska i imiona do tabeli "Zmapowane". Położenie kolumn zawierających nazwiska i imiona rozpoznaje się po znalezieniu nagłówka "Nazwisko" (dla nazwisk) i "Imię" (dla imion).
W twoim rozwiązaniu należy zapewnić to, aby w każdym kolejnym wierszu dane były pobierane z kolejnego wiersza danych do zmapowania. Zauważ, że musisz wstawiać kolejne liczby porządkowe; 2, 3, 4 itd. Takie kolejne liczby zapewni ci funkcja WIERSZ(). Zapis =WIERSZ(A2) zwróci liczbę 2 bo komórka A2 jest w drugim wierszu. Oznacza to, że ten numer wiersza może być jednocześnie indeksem potrzebnym w funkcji WYSZUKAJ.PIONOWO(). Czyli zamiast liczby 2, w tym pierwszym wierszu, wstaw funkcję WIERSZ(A2). Jeśli tę formułę skopiujesz w dół, to w funkcji WIERSZ() pojawią się argumenty A3, A4, A5 a to znaczy, że jej wynikiem będą liczby 3, 4, 5 itd.

[Rafkus]

Nie wiem, czy funkcja WYSZUKAJ.POZIOMO jest tutaj dobrym rozwiązaniem. Mam wrażenie, że chcesz tutaj "skopiować" dane z pewnego obszaru, a do takich celów lepiej nadaje się formuła macierzowa. W tekście pomocy można znaleźć:

Co to jest formuła macierzowa
Formuła macierzowa to formuła, w której są obliczane poszczególne wartości w zakresie komórek. W odróżnieniu od innych formuł w formule macierzowej jednocześnie obliczana jest nie jedna, ale wiele wartości.
Formuła macierzowa nie tylko oblicza wiele wartości, ale może także zwracać wiele wyników. Wynikiem formuły macierzowej jest także macierz.
Kiedy używa się formuł macierzowych
Formuł macierzowych używa się w celu powtórzenia obliczeń dla różnych wartości. Późniejsza zmiana metody obliczeń wymaga jedynie aktualizacji formuły macierzowej. Aby dodać formułę macierzową, należy zaznaczyć cały zakres komórek, a następnie dokonać odpowiedniej zmiany w formule macierzowej.
W przypadku obliczania wielu wartości formuły macierzowe pozwalają także oszczędzić miejsce, ponieważ nie wymagają dużej ilości pamięci.
Tworzenie formuł macierzowych
W przypadku tworzenia formuły macierzowej za pomocą Kreatora funkcji za każdym razem należy zaznaczyć pole wyboru Macierz, dzięki czemu wyniki są zwracane w postaci macierzy. W przeciwnym razie zostanie zwrócony wynik funkcji wyłącznie dla wartości w lewym górnym rogu macierzy.
Jeżeli formuła tablicowa wprowadzana jest bezpośrednio do komórki, należy użyć kombinacji klawiszy Shift + Ctrl + Enter, zamiast samego klawisza Enter. Tylko wówczas formuła będzie formułą tablicową.

Zatem Do komórki A2 w arkuszu zmapowane proponuję wpisać formułę:

Kod: Zaznacz cały

=INDEKS('kolumny do zmapowania'.A2:G4500;  ; PODAJ.POZYCJĘ(A1;  'kolumny do zmapowania'.A1:L1))

i zatwierdzić ją wciskając jednocześnie klawisze CTRL+SHIFT+ENTER
Funkcja PODAJ.POZYCJĘ powie w której kolumnie znajduje się wybrana kolumna, której nazwa została wpisana do komórki A1.
Funkcja INDEKS, w tym przypadku, zwróci z całego zakresu: 'kolumny do zmapowania'.A2:G4500 od razu jedną, całą kolumnę wybraną przez funkcję PODAJ.POZYCJĘ.
Więcej na temat zastosowanych funkcji znajdziesz w pomocy programu.

[jarecki2222]

Dziękuję za skuteczne rozwiązania. Szacun za wiedzę i inteligencje.

Jermor - Dziękuję za pokazanie zastosowania funkcji WIERSZ(). Przy okazji zauważyłem, że zapis =WIERSZ() zwróci numer wiersza w którym zapis został wprowadzony.

Rafkus - Dziękuję za świetny opis formuł macierzowych... Hmm no taką wiedzę wtłaczać dla 44 latka

Wypociłem kiedyś taki arkusz który pomaga mi eksportować dane adresowe, plikiem CSV na stronę angielskiej poczty czy na jakąś inną stronę kurierską.
A arkusz "kolumny do zmapowania" to łącza do pliku CSV zaimportowanego z eBay'a. Co jakiś czas coś kombinują i zmieniają układ kolumn lub nazwy nagłówków - dzięki temu nie będę się musiał tym tak przejmować.

[Jermor]

Nawiązując do odpowiedzi Rafkusa. Musisz rzeczywiście określić przewidywany zakres tabeli danych. Rafkus w przykładzie wpisał A2:G4500. Ponieważ jeśli zwiększysz liczbę danych np. o 50 następnych wierszy, to poprawienie formuły macierzowej, czyli zwiększenie zakresu do postaci A2:G4550 nic nie da. Wyniki i tak zajmą tylko poprzednie miejsce. Jeśli zmniejszysz zakres, np. do A2:G4450, to komórki w zakresie wierszy 4451 do 4500 będą zawierały nadal tę formułę ale z wynikiem #N/D. Aby ponownie dopasować formułę do żądanych rozmiarów należy usunąć formułę macierzową i wpisać ją ponownie z właściwymi argumentami. Dla osób, które nie pracowały z formułami macierzowymi może to być problem, bo dane w obszarze formuły macierzowej nie dają się usunąć. To właściwie dlatego nie napisałem o tym sposobie.
Co do zastosowanej funkcji PODAJ.POZYCJĘ(), sądzę, że wskazanym jest wpisanie jej trzeciego argumentu jako wartość 0, oznaczającego, że dane w wierszu A1:G1 nie są posortowane. W przeciwnym razie jeśli kolumna "Imię" wystąpi poi kolumnie "Nazwisko" dane mogą się nie dobrać. Przy wyszukiwaniu treści "Imię" napotkana zostanie treść "Nazwisko" i system uzna, że do Imienia najbardziej pasuje tytuł kolumny poprzedzającej "nazwisko". Ta funkcja nawiasem mówiąc może przyjąć ten argument jako 1 (dane są posortowane rosnąco i to jest wartość domyślna) albo -1 (dane są posortowane malejąco).
No i jeszcze taka uwaga dotycząca już samej funkcji INDEKS(), jeśli jeden z indeksów zostanie pominięty, to funkcja zwraca cały wskazany wiersz (jeśli pominięto numer kolumny) albo całą kolumnę (jeśli pominięto indeks wiersza). Tak więc np. INDEKS(A2:G4500;;3) zwraca wszystkie wiersze kolumny trzeciej.
Aby usunąć lub poprawić formułę macierzową należy zaznaczyć cały obszar, który ta formuła zajmuje. Często nie wiadomo jaki jest on duży. Najłatwiej zaznacza się taki obszar, umieszczając aktywną komórkę wewnątrz w dowolnej komórce z formułą a następnie korzystając ze skrótu klawiaturowego CTRL+/ (ukośnik).

[Rafkus]

@Jermor bardzo dziękuję o uzupełnienie mojej wypowiedzi, tak istotnymi faktami. Jakoś nie pomyślałem aby o nich wspomnieć...

[jarecki2222]

W pierwszym wątku załączyłem arkusz z rozwiązaniami, które mi podaliście.
Chyba zrobiłem to poprawnie.
Super... Pozdrawiam.

[Jermor]

Miałem problemy z kopiowaniem formuły, za pomocą autouzupełniania to jakieś cuda się działy

Kopiowanie formuł macierzowych ma pewne ograniczenia.

• Kopiowanie metodą Kopiuj i wklej.
  Jest to najpewniejsza metoda kopiowania.
  • Jeśli wynikowa formuła macierzowa zajmuje jedną komórkę, to można ja skopiować do wielu.
    Taką formułą może być np. =LICZ.JEŻELI(PRAWY(C2:C100;1);"0|2|4|6|8"), tutaj liczy ona ile imion w zakresie C2:C100 ma parzysty indeks liczbowy.
  • Jeśli wynikowa formuła macierzowa generuje zakres komórek, tak jak w twoim przykładzie powstała kolumna F, to skopiować ją można tylko jako cały obszar. Czyli skopiować F2:F100 i wkleić do G2 (albo do obszaru G2:J2). Ponieważ w formule tylko adres F1 jest względny, to po skopiowaniu zostanie zastąpiony adresem G1 (lub odpowiednio G1:J1).
• Kopiowanie za pomocą autouzupełniania, czyli przeciągania myszką dolnego prawego rogu.
  Przy próbie przeciągnięcia, po naciśnięciu prawego przycisku myszy, pojawia się "dymek" z podpowiedzią w rodzaju
  "Formuła macierzy 99 Z x 1 S". Jest to nie do końca przetłumaczony na polski tekst: "Z" pochodzi od Zeile (wiersz) a "S" od Spalte (kolumna).
  • Jeśli wynikowa formuła macierzowa zajmuje jedną komórkę, to można ją skopiować, lecz podczas przeciągania należy trzymać naciśnięty klawisz CTRL.
  • Jeśli wynikowa formuła macierzowa generuje zakres komórek, to po zaznaczeniu tego obszaru należy przeciągać dolny prawy róg zaznaczonego zakresu, także trzymając naciśniety klawisz CTRL.