[Miedzcu]

Mam problem z błędem REF! . Sprawa wygląda, tak że w skoroszycie „Sheet1” mam kwerendę, która każdego dnia pobiera nowe dane z nową tabelą. Nie wszystkie dane w tej tabeli są dla mnie istotne, niektóre chciałbym sprawdzić, czy spełniają odpowiednie kryteria, więc w skoroszycie „Sheet2” napisałem spełniające moje potrzeby proste formuły przenoszące dane ze skoroszytu „Sheet1 w stylu: =IF(‘Sheet1'!A1="";""; Sheet1'!A1) i tak dalej i tak dalej i jakieś tam proste warunki czy jest mniejsze, większe, czy zawiera itp. itd. Wszystko sprawuje się pięknie aż do następnego dnia i odświeżenia danych ze strony, w „Sheet1” . Tabela czasem ma mniej wierszy, czasami więcej (kolumn z nagłówkami zawsze tyle samo i w tym samym miejscu) by ostatecznie w drugim skoroszycie „Sheet2”, który filtruje ściągnięte dane, w niektórych wierszach psuły mi się formuły wydalającą błąd REF!. Tak jakby kwerenda, zamiast czyścić dane, nadpisując nowymi, najpierw usuwała komórki np. „A2” -> psuła formuły, wywalając błąd REF!-> a następnie wstawiała ponownie komórkę A2, ale z nową wartością – niestety formuła w skoroszycie „Sheet2”, już po takim zabiegu się nie umie pozbierać. Tak dzieje się nie zależnie czy zrobię tak, że jest to prawdziwa tabela z perspektyw excla czy dane ułożone wizualnie w postaci „niby” tabeli. Skopiowanie ręczne danych (odświeżenie danych w innym pliku excelaz innym pliku excel ctrl+c i ctrl+v w "Sheet1" rozwiązuje ten problem, ale traci się proces automatyzacji zaciągania danych. Można też inaczej po prostu od zdrowych pokazujących dane komórek (bo nie wszystkie się psują, psuję się taka ilość, ile ubywa wierszy w tabeli względem tej z poprzedniego dnia) przeciągnąć formuły w dół (excel zamieni wartości REF! na właściwe), tylko że jest to kolejny dodatkowy krok. Mogę Was prosić o podpowiedzi jak z tego wybrnąć?

#excel #arkuszekalkulacyjne #office #pytanie

[Xune]

@Miedzcu: Skoro na sheet2 chcesz ładować niepuste dane z sheet1 to czemu po prostu nie załadujesz ich na sheet2 po przefiltrowaniu? Piszesz o kwerendzie i automacie, lecisz PowerQuery? Może możesz zrobić np. drugie query z niepustymi i ładować tam.
Jeśli tabela na sheet1 to wynik Power Query to kliknij prawym na nią, table -> external data properties i przestaw na overwrite, clear, może to pomoże

[Polinik]

@Miedzcu: toć zrób to przez power query a nie cudujesz z jakimiś formułami, które mają budować tabelę

[Miedzcu]

@Xune: @Polinik: Power Query to jest już coś poza moją wiedzom (doszkolę się). Precyzując tabela w sheet1 generuje się, dzięki plikowi .iqy (Internet Query), który przed pobraniem można konfigurować w portalu, który go wypluwa, ale nie jest to możliwości konfigurowania wszystkiego (przynajmniej zgodnie z moją najlepszą wiedzą nie da się już w Sheet2 wygenerować, za każdym uruchomieniem tylko to, co chcę). Dlatego Sheet2 dodatkowo analizuje dane formułkami, by ostatecznie generować

[Xune]

@Miedzcu: Nie znam formatu danych i źródeł z jakich korzystasz, ale prawie na pewno PowerQuery jest odpowiedzią na Twoje problemy.

[Miedzcu]

@Xune: tu podpowiem że nie sa to ogólno dostępne strony tylko zabezpieczone strony firmowe wymagające logowania, tokenów itd itp Jakby surowe dane są "zabezpieczone" i można jedynie uzyskiwać dostęp do wyplutego "gotowca" w postaci .csv lub właśnie .iqy

Dzięki za sugeste sprawdzę czym jest PowerQuery i czy poradzi sobie w środowisku firmowym gdzie są liczne zabezpieczania.

[Polinik]

@Miedzcu: power query ma minimalny próg wejscia - w pół godziny ogarniesz podstawy, w godzinę da się ogarnąć wszystko, co można wyklikać, a to co można wyklikać zaspokaja jakieś 95% potrzeb.
Jak ktoś przetwarza I analizuje dane w Excelu to PQ to Must Have.

[Miedzcu]

@Polinik: @Xune: Rozumiem co mi chcecie przekazać :) właśnie pobawiłem się trochę i:
1. Faktycznie mogę edytować kolumny ich kolejność itd itp!

jednakże wydaje mi się że funkcje w PowerQuery są mocno predefiniowane, gotowe pod statystke itd itp. Nie mam matematycznej swobody w predefiniowaniu własnych formuł jak w klasycznym excelu?

Dobra załóżmy na to, że pliczek będzie konstruowany na dwie zmiany:
Zmianę dzienną: 6:00-17:00 ( w ramach tego samego dnia)

[Xune]

@Miedzcu: Ja nadal nie rozumiem co Ty chcesz osiągnąć. Czy jest tak że:
1. Masz jakiś plik gdzieś w firmowym intranecie ze zmianami ludzi na 14 dni naprzód. Dane w tym pliku się zmieniają co jakiś czas, dlatego musisz z niego regularnie pobierać informacje, żeby być na bieżąco.
2. Z tego pliku chcesz 1-2 kliknięciami pobrać kto ma dziś (albo jutro, albo pojutrze...) zmianę dzienną i kto ma zmianę

[Polinik]

@Miedzcu: do tego co wyżej dodam jeszcze, że oprócz gotowych działań masz też możliwość dodania kolumny niestandardowej, gdzie możesz sobie odwzorować te zagnieżdżone JEŻELI.

[Miedzcu]

@Xune: @Polinik: Żebyście mnie źle nie zrozumieli! Ja już jestem Wam mega wdzięczny za Wasze porady – obawiałem się że po napisaniu mojego postu nikt nie odpisze! Poczułem że mogę za darmo wysępić od Was wiedzę wiec na zasadzie pytań "adwokat diabła PQ" chce zmienić swoje postrzeganie w projektowaniu narzędzi w Excelu. Zdaje sobie sprawę że przez moją niewiedzę komplikuje sobie wiele spraw, przez tą moją niewiedzę uciekam się do

[Xune]

@Miedzcu: O panie. Brzmi jak coś czym się zajmowałem przez jakiś czas, kiedyś w dzień-dwa zrobiłem półautomat oparty o PQ i bardzo proste VBA w Excelu który oszczędził ekwiwalent pracy 2 ludzi, a na tych narzędziach bo budżet miałem 0 groszy.


Z tego co opisujesz wszystko brzmi na wykonywalne przez PQ, ale jest tego tyle że trzeba by to sobie rozpisać na poszczególne wymagania. Czyli lista "ficzerów" jakie masz finalnie mieć,

[Miedzcu]

@Xune: Dokałanie tak! To korporacyjna robota kupa gdy w tle dzieją się poważniejsze rzezy za które jesteś odpowiedzialny. Raz doszło do zdarzenia i oczywiście musiałem 2 godziny się tłumaczyć innym zespołom, liderom, menadżerom z powodu opóźnień…wiedząc że kreują się następne opóźnienia bo marnuje czas na tłumaczenia a wystarczy proces zalgorytmizować i zautomatyzować w excelu.
Daliście mi Panowie nadzieje że PQ wyratuje mój projekt. Obecnie jest tak że formułki są przeciągnięte do

[DwaNiedzwiedzie]

@Miedzcu: Masz jeszcze adres do wykucia na pamięć, a gdybyś z czymś utknął, pytaj.

https://docs.microsoft.com/en-us/powerquery-m/power-query-m-function-reference