Wydobycie informacji z zagnieżdżonych komórek [SOLVED]

[zak2828]

Dzień dobry,
Obecnie w pliku np. w komórce B2, C2 i D2 "siedzą" dane, które są jakby zagnieżdżone.
Gdy chcę, żeby pojawiły się w wierszach używając formuły "Tekst jako kolumny" to uzupełnia mi w dół dane, ale nadpisuje mi komórki B3, C3 i D3.
Czy jest jakiś sposób, żeby wydobyć te dane nie nadpisując innych informacji?
Dla przykładu dałem plik z dwoma wierszami danych, ale docelowo mam około 4000 takich wierszy.
Z góry dziękuję za pomoc.

[Jan_J]

W helpie opis operacji „tekst jako kolumny” dotyczy sytuacji przekształcania pojedynczego wpisu o złożonej strukturze na wiersz/rekord obszaru komórek. Ani słowa o tym, że jeścli separatorem jest Enter, to transformacja powoduje powstawianie nowych wierszy. Z tego punktu widzenia jest to zachowanie nieudokumentowane; a czy przewidziane – sądzę że tak. Ale tylko sądzę.

W każdym razie

* jeżeli chcesz stworzyć nowe kolumny zamiast nowych wierszy, to w zaznaczonej kolumnie/kolumnach/obszarze wykonaj Znajdź i Zamień: \n (czyli koniec wiersza) na ; (jakiś sensowny separator – ważne żeby być to znak nie występujący w Twoich danych). Potem „Tekst na kolumny” podzieli Twoje dane rozszerzając je poziomo.

* możesz też zaznaczyć pojedynczą kolumnę jak jest, z Enterami w komórkach, i wykonać na niej „Tekst jako kolumny”. Wtedy zwiększy się liczba wierszy, ale nie zauważyłem, by któreś dane zostały nadpisane. Nie wiem co się stanie jeśli tym sposobem przekroczysz maksymalną dopuszczalną liczbę wierszy (10^20, jest to całkiem sporo). Ale to prawdopodobnie jest to, co próbowałeś robić.

[Jermor]

Jan_J ma rację. Fakt użycia kodu ENTER jako separatora nie jest opisany. Ale zastosowane rozwiązanie jest sensowne, działa bowiem tak, jak wstawianie tekstu z zewnętrznego źródła. W tych sytuacjach znak ENTER jest traktowany jak koniec rekordu. Nadpisywanie, o którym piszesz następuje tylko wówczas gdy przekształcasz tylko jedną komórkę.
Jeśli zależy ci na przekształceniu tej tabelki w rekordy danych, to sposób podany przez @Jan_J jest bardzo dobry. Jedynym problemem będzie pierwsza kolumna, gdyż ona zawiera kod dla całej grupy oznaczeń w komórkach kolumn B, C i D.
Jeżeli twój skromny opis tych danych dotyczy wszystkich zapisów, to oznaczałoby, że każda grupa danych składa się z 17 wierszy, które zostaną podzielone na poszczególne rekordy. Wówczas po przekształceniu zawartości kolumn B, C i D w pojedyncze rekordy, dodaj nową kolumnę A, do której w komórce A2 wpisz formułę:

Kod: Zaznacz cały

=PRZESUNIĘCIE($B$1;ZAOKR.GÓRA((WIERSZ()-1)/17;0);0)

i skopiuj ja do końca. W ten sposób powielisz kod znajdujący się w kolumnie B do odpowiadających wartości. Potem zamień wyniki na wartości i usuń kolumnę B. No ale to zadziała tylko wtedy, gdy każda komórka zawiera 17 "zagnieżdżonych" danych.

[zak2828]

Faktycznie "tekst jako kolumny" zadziałał, wmówiłem sobie, że nadpisuje dane ta funkcja, a one jedynie je przesuwa. Także dziękuję za potwierdzenie
Pojawił się jednak inny problem. Zagnieżdżone komórki posiadają różna liczbę wierszy w sobie, raz 17, raz 4, a raz 2, itp.
Udało mi się doprowadzić do sytuacji, że mam plik z pewnymi danymi, ale rozłożone w kolumnach.
Czy jest jakiś sposób, żeby zadziałało coś na takiej zasadzie:
Do kolumny T z pliku przykład2 wpisuj dane z każdych kolejnych wierszy między kolumnami A a R, jeżeli te kolumny są niepuste.
Chce doprowadzić do sytuacji, że każdy wiersz ma się skopiować z transponowaniem do kolumny T, ale nie wchodzi w grę robienie tego ręcznie dla każdego wiersza.
Z góry dziękuję za pomoc.

[Jan_J]

Szkoda zachodu na formuły. Są rzeczy, któwe łatwiej wykonać w pliku txt/csv niż w arkuszu.

Ja bym zrobił kopiuj region / wkleił do dobrego edytora nieformatującego (dla użytkowników windows polecam Notepad++), tam zamienił* tabulatory (\t) na entery (\n), skopiował całość i wkleił jako niesformatowany tekst do kolumny w Calcu.

[*] włącz wyrażenia regularne podczas zamiany. Wcześniej może zajść potrzeba usunięcia pustych wpisów. Zrobisz to zamieniając ciągi tabulatorów na tabulator \t+ na \t, a potem ew. usuwając tabulatory stojące na końcu wiersza: \t$ na pusty tekst.

Chyba że ta obróbka musi być przeprowadzana regularnie, np. co tydzień. Wtedy warto się zastanowić nad automatem.

[Jermor]

Kluczowym jest pytanie zadane przez @Jan_J, czy cała ta akcja ma charakter jednorazowy i jej celem jest uzyskanie układu tych danych w postaci rekordów bazy. Jeśli tak jest, to przedstawię rozwiązanie z wykorzystaniem formuł.
@zak2828 uściślił, że w poszczególnych wierszach tej tabeli będą różne ilości "zagnieżdżonych" wartości. Zakładam, że będą one jednakowe w obrębie jednego wiersza.
Potrzebne będą dwie dodatkowe kolumny. Dla przedstawionego przykładu problemem jest jedynie kolumna A, która zawiera pojedyncze wpisy.
Wstawiłem dwie nowe kolumny, przez co oryginalne dane przesunęły się w prawo.
Do komórki A2, w tej nowej kolumnie wstawiłem formułę macierzową:

Kod: Zaznacz cały

=MAKS(DŁ(D2:F2)-DŁ(PODSTAW(D2:F2;ZNAK(10);"")))

Wylicza ona ile maksymalnie znaków ENTER zostało wstawionych w komórkach od D2 do F2. Funkcja MAKS została użyta po to, aby uzyskać wynik w jednej komórce.
Do komórki B2 wstawiłem taką formułę:

Kod: Zaznacz cały

=C2&POWT(ZNAK(10)&C2;A2)

Tworzy ona odpowiednia ilość powtórzonych wartości.
Formułę z komórki A2, ze względu na to, że jest to formuła macierzowa, należy skopiować do kolejnych komórek w dół metodą "Skopiuj i wklej". Formułę z komórki B2 można skopiować także metoda przeciągania.
Teraz należy skopiować komórki B2:B4000 i wkleić je już jako wartości na siebie same. Po tej operacji można usunąć kolumnę C (zawierającą po jednej wartości) oraz kolumnę A. Otrzymane kolumny przekształcić w kolumny. W przypadku kolumny B zawierającej naprzemiennie liczby i teksty warto podczas przekształcenia wymusić przekształcenie na teksty.
W załączeniu przykład z wprowadzonymi już formułami.

[zak2828]

Dziękuję Jermor, te rozwiązanie bardzo mi pomogło.
Pozdrawiam

[Jermor]

Ciesze się, że pomogło. Uzupełnię tylko, że użycie formuły macierzowej nie było konieczne. Zastosowałem ją aby znaleźć ilość najwięcej postawionych znaków ENTER w wierszu. Zastanawiałem się wówczas nad ewentualnym znormalizowaniem wszystkich komórek w wierszu, w taki sposób aby miały tyle samo "linijek". Jednak gdy w wierszu we wszystkich komórkach jest tyle samo "linijek", to taka formuła jest zbędna i można ją zastąpić formułą tradycyjną:

Kod: Zaznacz cały

=DŁ(D2)-DŁ(PODSTAW(D2;ZNAK(10);""))

[zak2828]

Liczba "zagnieżdżonych" wierszy w danym wierszu niestety nie zawsze jest taka sama, zależy od kolumny, i dlatego potrzebowałem rozbite te wszystkie dane na wiersze, żeby widzieć, jakie id, przy jakim size_id ma puste code_producer i code_external.

Na pewno jednak kolumna "/sizes/size@id" determinuje ile wierszy powinno być we wszystkich.

Czyli nawet jeśli w kolumnach innych niż size_id jest mniej zagnieżdżonych wierszy to i tak powinno n-wierszy z id, n-wierszy z code_producer i n-wierszy z code_external. Nawet jeśli te wiersze będą puste, gdzie n to liczba zagnieżdżonych wierszy size_id

Czyli chciałbym uzyskać taki podział wierszy i kolumn.
21 U [puste] LSO117
21 X 425611111111 [puste]
21 Y 425677333333 LSO117.6

Wtedy widzę, jaką konkretnie rzecz muszę poprawić w pewnej bazie.

Tak czy siak poprzedni sposób znacząco mi pomógł, także jeszcze raz dziękuję.