Excel Forum - Porady, Pomoc, Excel Help, Excel FAQ - Zestawienie ilość i wartość dla kilku arkuszy

adampuchal · słuchacz Posty: 8

Potrzebuje skonstruować tabelę, która zbiera informację o wartości i ilości, z podziałem na sklepy, dla towarów powyżej 3 lat.

Ważne, aby w tabeli zbiorczej nie powtarzały się towary.

W pliku przykład i moje "wyobrażenie" rozwiązania.

Sklep.rar

Pobierz Plik ściągnięto 18 raz(y) 3.93 KB

tkuchta1 · Excel Expert **Pomógł:** 1495 razy Posty: 2270

Wg mnie wystarczy Tabela Przestawna i konsolidacja Wielu zakresów danych. (dla E2007/10)

Konsolidowanie wielu arkuszy w jeden raport tabeli przestawnej
http://office.microsoft.c...A010226585.aspx

"Aby dodać Kreatora tabeli przestawnej i wykresu przestawnego do paska narzędzi Szybki dostęp, należy wykonać poniższe czynności:
1.Kliknij strzałkę obok tego paska narzędzi, a następnie kliknij polecenie Więcej poleceń.
2.W obszarze Wybierz polecenia z wybierz pozycję Wszystkie polecenia.
3.Wybierz z listy pozycję Kreator tabeli przestawnej i wykresu przestawnego, kliknij przycisk Dodaj, a następnie kliknij przycisk OK.

Po kliknięciu na utworzony w w.w. sposób skrót do kreatora.
1. Wybieramy opcję Wiele zakresów konsolidacji
2. "Jedno pole strony" / Dalej
3. Wskazujemy zakresy danych w poszczególnych Arkuszach klikająć dodaj po wskazaniu każdego zakresu (obraz1)
3. Pole: Wiersz do Etykiety wierszy; Pole Kolumna do Etykiety Kolumn; Wartość do Pola Wartości i dodatkowo: pole Strona1 do Etykiety Kolumn!
4. Filtr na Pole Kolumna (odkliknięcie: Ceny i okresu) / Ukrycie Sum części tabeli i voila :-)

(obraz2)

Tworzę to w wersji E2010 i zapisuję do wersji E2003. To może nie zadziałać ale w.w. procedura powinna wystarczyć

obraz1.png

Plik ściągnięto 223 raz(y) 28.94 KB

obraz2.png

Plik ściągnięto 2 raz(y) 18.98 KB

Sklep.zip

Pobierz Plik ściągnięto 13 raz(y) 6.18 KB

adampuchal · słuchacz Posty: 8

A jak wprowadzić ograniczenie dla towarów z okresu 3?

TOMEK1970 · ExcelSpec **Pomógł:** 182 razy Posty: 476

Już trzeci raz przerabiam dzisiaj tę samą formułę :mrgreen:

.
W załączniku rozwiązanie formułą tablicową (pomimo że plik zapisałeś jako .xls podałem dwa rozwiązania dla Excela 2003 - dłuższe i dla Excela 2007 - krótsze).
Rozwiązanie ma jedną wadę - drastycznie rośnie wielkość formuły, w miarę dodawania nowych sklepów i Excel 2003 może już nie pozwolić na dodanie następnego sklepu (ilość zagnieżdżeń funkcji) a Excel 2007 będzie strasznie mulił przy przeliczeniach dla większej ilości sklepów i towarów.
Zobacz czy takie rozwiązanie ci pasuje:

Sklep.zip

Pobierz Plik ściągnięto 9 raz(y) 6.74 KB

adampuchal · słuchacz Posty: 8

Problem mam z tą formułą, ponieważ mam w sumie 20 sklepów i dużo wierszy. Funkcja jeżeli musiałaby być zagnieżdżona zbyt wiele razy i formuła w jednej komórce musiałaby zająć 21 wierszy. Mam ogromną prośbę czy można to zrobić uniwersalnie dla 11 sklepów bo tyle mam najwięcej w największym regionie.

Jak uda się dla tych jedenastu to będę już w domu :)
Załączyłem plik z jedenastoma sklepami żeby już nie mącić:)
Zacząłem jeszcze wyszukiwać problemy z użyciem podaj pozycję i przesunięcia ale problem dalej polega na porównaniu tych sklepów.

Dziękuję za pomoc i pozdrawiam.

Sklepy analiza.rar

Pobierz Plik ściągnięto 4 raz(y) 106.52 KB

TOMEK1970 · ExcelSpec **Pomógł:** 182 razy Posty: 476

Trochę to trwało, ale temat mnie zaciekawił i zrobiłem makro, które myślę że spełni twoje oczekiwania.
Trochę warunków aby makro zadziałało:
1. Arkusze z nazwami sklepów muszą zaczynać się od litery "w", tak jak w twoim przykładzie, czyli np. w_1, w_5 itd - inaczej towar z tych sklepów nie będzie brany pod uwagę przy tworzeniu tabeli w arkuszu "Wynik" (możesz to ewentualnie zmienić w tym fragmencie kodu: Maska = "w*" ).
2. Ilość i wartość muszą być w arkuszach sklepów odpowiednio w kolumnach B i C a lata w D (można to zmienić w tej deklaracji

Kod:

If .Cells(i, 4).Value >= WiekMin.Value Then

oraz w formułach wewnątrz kodu, ale uważaj na znaczek ' (apostrof) bo jest on specyficznie brany w cudzysłów (cały zapis wygląda tak: ""'"" :lol:

) i jak tam zrobisz błąd, to może być ciężko dojść jak go naprawić
3. Dane w arkuszach sklepów nie mogą mieć pustych komórek, czyli nie może być ilości bez wartości i odwrotnie
4. Jeżeli będziesz kopiował makro do innego pliku, to musisz w nim utworzyć arkusz "Wynik", do którego będzie się wklejała tabela z wynikiem.
5. Makro zostało stworzone w Excelu 2007 i choć ograniczenia są założone dla Excela 2003 (65536 wierszy i ostatnia kolumna o symbolu IV), to nie daję gwarancji że wszystko będzie działać w Excelu 2003 bezproblemowo - musisz to sam przetestować.
6. Z uwagi na sposób obliczania przy dużej ilości danych może wystąpić błąd ze względu na zbyt małą ilość dostępnych wierszy (makro najpierw kopiuje do arkusza "Wynik" wszystkie towary spełniające warunek wiekowy ze wszystkich arkuszy, a dopiero później usuwa z tego duplikaty)
7. Makro przy większych ilościach danych może dosyć długo liczyć (nawet kilka minut) - jeżeli będzie problem ze zbyt długim przeliczaniem lub z tym, o czym pisałem w punkcie 7, spróbuj podzielić dane na mniejsze porcje.

OK, trochę postraszyłem :mrgreen:

ale myślę że wszystko będzie działać zgodnie z twoimi oczekiwaniami, zerknij do załącznika:

P.S.
W makrze wykorzystałem znaczne fragmenty kodów (niekiedy całe ;-)

) kolegów Hudibyk, Hurgadion i pośrednio Tkuchta1, z racji tego że raczkuję dopiero w temacie VBA, mam prośbę do wszystkich mających trochę pojęcia o makrach o zerknięcie na moje wypociny i ewentualne podpowiedzi odnośnie optymalizacji tego kodu. Zależy mi szczególnie na wypowiedziach odnośnie korzystania z ".FormulaLocal" w przypadku gdy jest ona potrzebna do wyliczeń, które i tak są później wklejane jako wartości (mam wrażenie że można to zrobić szybciej i wydajniej).

Sklepy analizaT.zip

Pobierz Plik ściągnięto 10 raz(y) 191.18 KB

Wormsek · Wysłany: 2012-03-20, 14:30

Jako, że nie mam dziś weny do pracy, to zajrzałem i ;-)

:

1.

Kod:

On Error GoTo koniec

Jak znajdzie błąd w kodzie to idzie na koniec makra....ale strasznie uogólniłeś to. Jak już wywołujemy taką obsługę błędu, to staramy się to robić świadomie dla jakiejś pojedynczej akcji, a potem wyłączamy obsługę błędów.
W twoim wypadku w dalszej części kodu może pojawić się kilka różnych błędów, a ty nie będziesz wiedział, który fragment kodu to zrobi.

2.

Kod:

Range("B1").Value = "<--Tu wpisz wiek"
Do
Set WiekMin = Range("A1")

Loop Until WiekMin.Value <> ""

Coś takiego spowoduje, że jeżeli komórka będzie pusta, to makro ci będzie działać do us...j śmierci ;-)

. (hihi nawet z ciekawości sobie excela przez to zawiesiłem ;-)

)
Podejrzewam, że wcześniejsze włączenie obsługi błędów spowoduje, że jeżeli się pojawi jakieś przeciążenie pamięci w tej pętli to odczyta to jako błąd, ale nie wiem ile na coś takiego byś musiał czekać ;-)

Dodatkowo przypisujesz komórkę do zmiennej obiektowej, a korzystasz tylko z jej wartości. Więc lepiej zadeklarować WiekMin jako np. Integer i przypisać od razu samą wartość.
Podsumowując:
Bardziej już się opłaca robić świadome sprawdzenie wartości poprzez przykładowo:

Kod:

Dim WiekMin As Integer

On Error GoTo WiekErr
WiekMin = Range("A1")
On Error GoTo 0

If WiekMin = 0 Then
MsgBox "AAAAAA ;)"
GoTo koniec
End If
WiekErr:
MsgBox "Wiek nie jest liczbą"
koniec:
Exit Sub

(ba, przydałoby się jeszcze sprawdzić, czy jest całkowitą, ale to jako praca domowa ;-)

)

Lecimy dalej ;-)

3.

Kod:

Set ArkuszWynik = ThisWorkbook.Worksheets("Wynik")

Tu też by się przydało obsłużyć błąd, bo co będzie jak takiego arkusza nie będzie w pliku? ;-)

4.

Kod:

.Range(.Cells(i, 1), .Cells(i, 1)).Copy

Coś takiego to już można było przez ;-)

Kod:

.Cells(i, 1).Copy

5. Olać punkt 4 ;-)

, można całe kopiowanie napisać szybciej ;-)

Kod:

ArkuszWynik.Cells(ostWiersz, 2).Value = .Cells(i, 1).Value

6.

Kod:

For d = Cells(Rows.Count, "B").End(xlUp).Row To 2 Step -1

To jest ok, ale brak konsekwencji ;-)

. Korzystasz wcześniej z funkcji LAST, a tutaj czemu nie? ;-)

.
Oraz dodatkowo, wcześniej bardzo ładnie pokazałeś, że kumasz bloki WITH. Tutaj bez kropek bez niczego odnosisz się do aktywnego arkusza. A co, jak aktywnym nie będzie ten co chcesz? ;-)

7.

Kod:

On Error Resume Next
Worksheets(ArkuszWynik).Select
On Error GoTo 0

ArkuszWynik już jest obiektem arkusza, nie traktuj go jako tekstu ;-)

.

8. Co do FormulaLocal, to strasznie ona długa i nie wiem czy mi się chce sprawdzać jej poprawność ;-)

. Sprawdź sam poprzez odpalenie makra i zobacz co ci wstawi do komórki ;-)

9.

Kod:

Range("C1:D1").MergeCells = True

Nie testowałem, ale podejrzewam, że jak będziesz scalał, to wyskoczy ci okienko z zapytaniem. W celu uniknięcia tego poczytaj o DisplayAlerts.

No to by było na tą chwilę tyle ;-)

.
Miłej lektury ;-)

TOMEK1970 · ExcelSpec **Pomógł:** 182 razy Posty: 476

Wormsek gdzieżeś znalazł taki stary temat ? :lol:

Nawet jego założyciel nie zajrzał już do niego, pomimo faktu, że wcześniej atakował mnie na P.W. z prośbą o pomoc (w moich wiadomościach oczekujących do dziś wisi info zwrotne do niego, że zrobiłem rozwiązanie do tematu).
Tak jak pisałem w poprzednim poście w dużej mierze korzystałem z kodów, które znalazłem między innymi na tym forum, stąd w niektórych fragmentach występuje pewna niekonsekwencja w rozwiązaniu niektórych problemów, chociaż niektóre rzeczy mimo że nie wyglądają zbyt dobrze:

Wormsek · Wysłany: 2012-03-21, 15:50

Temat może i stary, ale po prostu nadrabiałem zaległości i się trafiło ;-)

.