Wyszukiwanie z pominięciem pustych komórek

[Pysio]

Cześć, ponownie pojawiam się z pewną zagwozdką.

Sprawa wygląda tak, że chcę zrobić formularz dostawy.

Jest plik, w którym wprowadzamy nazwę firmy, miejscowość i towary jakie mamy dostarczyć.

Przykład
Kto/ Gdzie/ Jabłka / Marchewki/ Gruszki
Firma1/PcimDolny / /100/300
Firma2/ PcimGorny/ 100//100


Jak teraz pokazać coś takiego:

Firma1/ PcimDolny
Marchewki / 100
Gruszki/ 300

Firma2/ PcimGorny
Jabłka/ 100
Gruszki/ 300

Chodzi mi o wpisanie tylko tych wierszy i kolumn, które występują jako niepuste z pominięciem pustych.
Odszukiwanie firm i miejscowości rozwiązałem poprzez nadanie kluczka dla danych wartości (dynamicznie). Można go wykorzystać do wskazania wiersza produktów.

Oczywiście forma Jabłek i Gruszek jest mocno uproszczona, a sam formularz ma kilkadziesiąt asortymentów.
Myślałem aby to ew. rozwiązać poprzez filtrowanie wartości >1 ale nie wiem czy jest sens pakować wszystkie wartości do każdego miejsca dostawy. Zdarza się, że do jednego miejsca jedzie może 5-6 towarów różnych, a zazwyczaj 1-2.

Myślałem o wykorzystaniu funkcji wyszukaj.pionowo, ale nie wiem jak jej nadać aby pominęła puste pola.

Kod: Zaznacz cały

WYSZUKAJ.PIONOWO(NIE("");A1:DD100;??;0)

[Pysio]

Dodam tylko, że wydaje mi się, że tabela przestawna się tutaj nie koniecznie sprawdzi.
Wolałbym aby było to dynamicznie przydzielane przez funkcję.

W ostateczności zrobię to tabelą przestawną.

[Jermor]

Kolego @Pysio, pisząc zagwozdka miałeś zapewne nadzieję, że jest to drobny problem, który łatwo zostanie rozwiązany.
Tak niestety nie jest. Aby otrzymać pożądane przez ciebie zestawienie trzeba skonstruować dość złożony sposób przekształcenia danych do postaci, która umożliwi wykonanie stosownych zestawień albo napisać makro, które działając na twoich danych wygeneruje odpowiednie zestawienie. Samymi prostymi formułami tego nie da się zrobić. A już funkcja WYSZUKAJ.PIONOWO w ogóle nie jest do tego przydatna.
Także wspomniana przez ciebie tabela przestawna do niczego ci się nie przyda, bo dane zorganizowane w podany przez ciebie sposób nie nadają się do wygenerowania tabeli przestawnej.
Wydaje się, że powinieneś gruntownie przemyśleć sposób gromadzenia danych źródłowych. Z tego co można wnioskować z opisu, zbudowałeś piramidę a teraz chcesz z niej wyciągać poszczególne elementy bo są ci potrzebne do innych prac. Raczej powinieneś mieć swobodne elementy, z których możesz tworzyć potrzebne konstrukcje.
Wg mnie powinieneś gromadzić dane zamówień mniej więcej tak:
Data zamówienia, kto zamówił, gdzie, co, ile sztuk, Data dostawy, itd (nie znam przecież twojego problemu).
Z takich danych możesz uzyskać każdy rodzaj zestawienia.
Twoje dane wyglądają teraz mniej więcej tak:

Obecne dane źródłowe.

Trzeba je przekształcić do następującej postaci.

Dane przekształcone

Po to aby ostatecznie otrzymać tabelę przestawną:

Tabela przestawna

200729223429_8.jpg (19.01 KiB) Przejrzano 4013 razy

[Pysio]

Niestety nie rozwiąże to mojego problemu.
Jak się zapewne domyśliłeś, jabłka i gruszki są jedynie kroplą, tak samo jak i ilość firm.

Dodatkowo arkusz do wpisywania zamówień zaciąga dane na temat obecnego stanu towaru i odejmuje to co ma zjechać/ wyjechać.
Twoja propozycja niestety w tym przypadku się kompletnie nie sprawdzi.

Myślę,że zastosuję filtrowanie po kolumnach a tam ustawię, że dane komórki mają być większe od 0.
Na szczęście ten projekt akurat robię na Libre Office, a ten ma fajne opcje filtrowania.

Będę jeszcze dzisiaj kombinował z tym formularzem. Nie mniej, dziękuję za wsparcie.

[Jermor]

Jak się zapewne domyśliłeś, jabłka i gruszki są jedynie kroplą, tak samo jak i ilość firm.

No i własnie dlatego sądzę, że trzeba to zrobić inaczej.
Druga przedstawiona przeze mnie ilustracja, to efekt utworzenia odpowiednich formuł, które przekształciły postać zestawienia z pierwszego rysunku.
Ta z kolei postać nadaje się do wykorzystania w tabeli przestawnej. Podana przez ciebie przykładowa formuła sugeruje zakres A1:DD100, czyli 99 firm (każdy wiersz to jedna firma) oraz około 100 asortymentów (DD to 108. kolumna arkusza).
Takie zestawienie przekształcone do postaci zaproponowanej na drugiej ilustracji wymaga utworzenia około 10000 wierszy. Jak wspomniałem znalazłem metodę, czyli formuły, na uzyskanie takiej postaci danych z zestawienia źródłowego. Tylko, że: zestawienie dostaw jest wykonywane prawdopodobnie codziennie. Aby wypełnić źródło musisz poruszać się po tabeli o 10000 komórkach, po to aby utworzyć zestawienie dla dostawy dla np 15 odbiorców otrzymujących jak wspomniałeś około 3-4 produkty. Po wykorzystaniu zestawienia musisz wyzerować cały arkusz aby wprowadzić dane na dzień następny.
Filtrowanie, o którym piszesz też ci nic nie da. Jeśli ustawisz że dane komórki mają być większe od 0, to zobaczysz tylko takie wiersze, które w każdej komórce wiersza mają wartość większą niż 0. Wszystkie warunki w wierszu są łączone wyrażeniem logicznym I.

Posiedziałem nad twoim pomysłem godzinkę i podsyłam ci pomysł na rozwiązanie problemu. Zastrzegam jednak, że wiedzę o nim mam szczątkową, wynikającą tylko z twoich postów, a nie ze znajomości procedur obowiązujących w firmie.
Skoroszyt zawiera 6 arkuszy. Każdy realizuje pewną funkcję.
Arkusze "Odbiorcy" i "Dostawcy" zawierają tylko dane tych podmiotów. Ale nazwy w nich występujące są wykorzystane w polach nazwy arkuszy "Dostawy" i "Zamówienia" do wybierania tych nazw z listy. Arkusz "Stany" zawiera stany magazynowe obliczone jako suma dostawy minus suma zamówień. Nazwy artykułów z tego arkusza są wykorzystane w arkuszach "Dostawy" i "Zamówienia" do wyboru nazwy towaru z listy.
Ostatni arkusz "Do_klientów" tworzy na wybrany dzień zestawienie dostaw.
Przedstawiony plik to tylko wstępny pomysł, pokazujący jednak, że tą droga także można iść.

[Pysio]

Jermor bardzo dziękuję Ci za poświęcony czas i podzielenie się spostrzeżeniami.

Niestety, obawiam się, że taka forma w moim przypadku nie wypali.
Musi być mniej więcej taki układ jak w załączniku.

Zamówienia zrealizowane są usuwane (wiersze).

Założenie tego arkusza jest takie, że ma wspomagać logistykę w planowaniu załadunków (łączenie zamówień klientów wg wagi- tu dla przykładu ustalmy, że wg ilości).
Chodzi o to, by na końcu z takich połączonych zamówień, wypluło formularz załadunków na magazyn. Aby wiedzieli co spakować i na co wystawić fakturę.

Asortyment jest raczej stały, czasem coś trzeba zmienić, ale to mogę dorobić, aby był formularz pośredni gdzie będziemy mogli dodać coś ręcznie.

Formularz ma wyglądać mniej więcej tak:

REJON 1
FIrma1

Towar ILOŚĆ


Firma2

Towar ILOŚC

Firma3

Towar ILOŚĆ


Gdzie będę sobie filtrował po rejonie.

[Jermor]

No dobra, nie będę się już wchrzaniał w twoje zabawki.

Przyglądając się jednak załączonemu zrzutowi arkusza, nie widzę takiej prostej możliwości usunięcia wierszy zamówień. Powołam się tu tylko na kolumnę D. Zawiera ona w pierwszym wierszu stan jabłek po wywozie tych zamówionych. Ale to oznacza, że jest tam zapewne formuła typu zsumuj jabłka z dostaw i odejmij od tego jabłka z wyjazdów. Jeśli wyczyszczę wiersze zamówień to w tym miejscu pojawi się suma z dostaw, czyli 3000 bo wiersze wyjazdów zawierają zera. Tylko, że w takiej sytuacji powinna tam być wartość 2700, bo 300 już wyjechało. Nie wspominam już o usunięciu wszystkich wierszy sumowanego zakresu, gdyż wówczas formuła przestałaby działać, wyświetlając komunikat #REF!.
Może nie warto usuwać zamówień. Wykorzystując filtry można oglądać zapisy tylko np. z bieżącego miesiąca albo tygodnia albo dnia. Zawsze łatwo będzie zrobić analizę wydanych asortymentów zarówno pod kątem dat (sprzedaż zimą vs. latem) jak i odbiorców. W arkuszu można wykorzystać ponad 1000000 wierszy. Jeżeli średnie dzienne zestawienie zamówień ze wszystkich firm to np 200 pozycji, to miejsca starczy na 5000 dni. Przyjmując dalej, że w roku jest ok. 250 dni roboczych, wystarczy to na 20 lat. Analogicznie można pomyśleć o dostawach.
Założyłem, że projekt umożliwia zakup towarów tylko od zarejestrowanych dostawców. Dlatego na liście dostawców jest wpis "Z wolnej ręki" pozwalający zarejestrować zakup od dostawcy bez specjalnej rejestracji.
Sprzedaż jest możliwa tylko zarejestrowanym odbiorcom. Ten rejestr zawiera dodatkowo numer rejonu odbiorcy.
Sprzedawany lub przyjmowany towar musi być najpierw zarejestrowany pod swoja nazwą, w magazynie (arkuszu "Stany").
Taki lekko zmodyfikowany plik załączam, ale tylko jako wersję poglądową.
W skoroszycie występują nazwane obszary. Arkusze zablokowane nie maja hasła, więc można je odblokować.
Pozdrawiam i powodzenia.

[Rafkus]

Aż chciałoby się, do tego problemu, zaproponować użycie Base. Niestety jest zbyt niestabilny, żeby powierzyć mu ważne dane firmowe...
@Pysio, dane tworzone przez Ciebie są w miarę łatwe do wprowadzania i podsumowania, jednakże ich analizowanie może sprawiać pewien problem. Aby przedstawić je tak, jak Jermor zastosowałem funkcje macierzowe (działają one na tablicach danych, zatwierdza się je jednoczesnym wciśnięciem klawiszy <CTRL+SHIFT+ENTER>). Efekt w załączniku.

[Pysio]

Udało mi się to ogarnąć Co prawda idzie to z arkuszy google, ale działa.

Niestety nie mogę udostępnić arkusza ze względu na dane firm i inne tajemnice firmowe itp.

Natomiast po krótce wygląda to tak, że handlowiec dodaje klienta i co ten klient chce. Ma kilka kolumn a w wierszach wypisuje jaki towar chce i ile tego towaru.
Dlaczego w w kolumnach? Bo łatwiej ogarnąć to z komórki będąc u klienta.

Teraz to zamówienie za pomocą funkcji TRANSPONUJ przekłada w układ poziomy- lepszy do obrabiania na kopie.
Układ trafia do zbiorczego (kilku handlowców), który zaciąga dane od handlowców. Każdy z nich ma ok 50 wierszy na swoje potrzeby.

Teraz tematem zajmuje się dział logistyki, który wywiezie to w świat. Najczęściej zdarza się, że wywozi 2-3-4 klientów jednym transportem. Dlatego jest "zakładka" planowanie, gdzie może wybrać datę kiedy planuje to wywieźć i jakim autem (dodatkowo sprawdzanie, czy załadunek jest optymalny pod względem tonażu).

Dalej przechodzimy do dyspozycji. Czy to co było głównym problemem.
Do każdego zamówienia gdzie jest ustawiona data i auto, zrobiłem prosty kod- czyli połączyłem teksty i dodałem kolejny numer.
Czyli jeśli mamy datę (data pisana w formie liczby bo szybciej 909 to 9 września 1001 to 10 stycznia itd.)
Data Auto Kod
101 Auto1 101Auto11
101 Auto2 101Auto21
101 Auto1 101Auto12
201 Auto1 201Auto11

Dzięki temu jestem w stanie wyodrębnić dane zamówienia i za pomocą wyszukaj pionowo odnajdzie mi produkt, którego potrzebuje.
W dyspozycji wybiera logistyka dane z dostępnych w poprzednim arkuszu. Czyli datę i auto (wybiera, nie wpisuje więc prawdopodoieństwo popełnienia błędu zmniejsza się).

Na tej podstawie tworzony jest kod- klucz, za pomocą którego sprawdzamy w poprzednim arkuszu, co w tym zamówieniu jest.
W ten sposób wyświetla mi dane dla właściwego klienta.
Potem wystarczyło dodać filtrowanie, aby pokazywało tylko niepuste wiersza i ... mamy to

Dodane kilka gadżetów typu sprawdzanie poprawności kodu (może wybrać inną datę i inne auto, nie znam sposoby na filtrowanie wybieranych danych, aby ograniczyło ilość wyświetlonych rekordów), wskazanie ile jest miejsc do dyspozycji, zmiana kolejności rozładunku (u nas istotna rzecz).
Wygląda i działa ciekawie.

Spróbuję udostępnić sam mechanizm na odrębnym arkuszu, może komuś się przyda, a może ktoś go dodatkowo wzbogaci o jakieś funkcje

Głównie korzystałem z funkcji wyszukujących.