GoldenLine
Zaloguj się

konto usunięte

Temat: Obliczenia w obębie duplikatów

Witam

Mam taki oto problem:

Posiadam dane w dwóch kolumnach A:numer indeksu i kolumna B: data. W kolumnie A występują czasem duplikaty (2 i więcej). Chciałbym za pomocą funkcji MIN (minimum) wyszukiwać najstarsze daty z obrębu duplikatu w kolumnie A.

Np:
Kolumna A Kolumna B
11 1.08.2014
12 7.08.2014
13 9.08.2014
13 4.08.2014
13 3.08.2014
14 22.08.2014
15 12.08.2014

Funkcja miałaby zwracać w kolumnie C najstarszą datę z obrębu duplikatu z kolumny A
czyli dla indeksu "13" - 3.08.2014 w kolumnie C itd...

Ktoś ma jakiś pomysł jak to rozwiązać?

Z góry dzięki
Link do wypowiedziLink
Oskar Shon

Oskar Shon Dodatki do Office
www.VBATools.pl

Temat: Obliczenia w obębie duplikatów

A nie lepiej wykonać tabelę przestawną.
Daty dodać do pola wierszy podobnie jak indeks a do pola obliczeń licznik z daty.
Po uporządkowaniu/sortowaniu masz duplikaty oraz informacje o hierarchii czasu. Możesz też dać min z daty w tedy wydaje mi się ze będzie to dokładnie to co chcesz.Ten post został edytowany przez Autora dnia 28.08.14 o godzinie 14:04
Link do wypowiedziLink

konto usunięte

Temat: Obliczenia w obębie duplikatów

Masz rację ale jednak nie mogę zastosować takiego rozwiązania ponieważ to co pokazałem to tylko mała część mojej tabeli. W kolejnych kolumnach będę wykonywał podobne operacje i porównywał dane....
Miałem na myśli raczej wynik w kolumnie C.
Link do wypowiedziLink

Wojciech Gardziński

Wypowiedzi autora zostały ukryte. Pokaż autora
Oskar Shon

Oskar Shon Dodatki do Office
www.VBATools.pl

Temat: Obliczenia w obębie duplikatów

Nie ma sprawy aby do kol C w prosty sposób dodać ilość - Tabela w arkuszu pomocniczym.


Obrazek

Oczywiście można też wpisać formułę tablicową, aby nie bawić się tabelą przestawną.
Link do wypowiedziLink
Artur D.

Artur D. Solution Architect,
Atos IT Services Sp.
z o.o.

Temat: Obliczenia w obębie duplikatów

Formuła tablicowa bez tabeli pomocniczej:

=LARGE(($B$2:$B$10)*($A$2:$A$10=$A2);SUMPRODUCT(($A$2:$A$10=$A2)*1))

=MAX.K(($B$2:$B$10)*($A$2:$A$10=$A2);SUMA.ILOCZYNÓW(($A$2:$A$10=$A2)*1))

Wprowadzona tablicowo, czyli ctrl+shift+enter

Jedyny warunek: daty w kolumnie B muszą być rozpoznawane jako liczba przez excela (trzeba poprawić format)
Link do wypowiedziLink
Zbigniew Szyszkowski

Zbigniew Szyszkowski sprzątacz,
Ministerstwo
Rolnictwa i Rozwoju
Wsi

Temat: Obliczenia w obębie duplikatów

Nie jestem pewien czy dobrze zrozumiałem pytającego ale wydaje mi się że chodzi o uzyskanie wyniku (najstarszej daty) w kolumnie C TYLKO dla duplikatów. Tak przynajmniej wynikałoby z pierwszego postu i nazwy wątku.
W takim przypadku proponowałbym coś takiego (formułka dla C2 i kopia w dół do końca danych - należy dostosować zakres, tu w przykładzie jest tylko do 15 wiersza)
=JEŻELI(ORAZ(A2=A3;A2=A1);"";JEŻELI(A2=A3;MIN(JEŻELI(A2:$A$15=A2;B2:$B$15));""))
Formuła tablicowa więc zatwierdzamy ją poprzez CTRL+SHIFT+ENTER. Formuła zwraca najstarszą datę dla danego indeksu na wysokości ( w wierszu) pierwszego wystąpienia zduplikowanego indeksu.
OShon, skoro już mamy TP to dlaczego nie wykorzystasz bezpośredniego odwołania do odpowiednich jej elementów (WEŹ.DANE.TABELI) zamiast wyszukiwania elementu? Byłoby szybciej :-)))
Artur Dalak... formułka będzie działać ale to najlepsza droga do "zarżnięcia procka" przy dużej ilości danych. Dla przykładu, podejrzewam że podana wyżej przeze mnie formułka będzie dla 1 000 wierszy jakieś 80 -100 razy szybsza i różnica w wydajności będzie wzrastać wraz ze zwiększaniem ilości danych. (trzeba by to sprawdzić ale wiele się nie mylę :-))). Niemniej Twoja formuła również bedzie działać :-)
I jeszcze jedno....skoro formuła z SUMĄ.ILOCZYNÓW potrzebuje zatwierdzenia tablicowego to znaczy że nie potrzebujemy SUMY.ILOCZYNÓW :-)))...zwykła SUMA wystarczy :-)

PozdrawiamTen post został edytowany przez Autora dnia 30.08.14 o godzinie 02:45
Link do wypowiedziLink
Artur D.

Artur D. Solution Architect,
Atos IT Services Sp.
z o.o.

Temat: Obliczenia w obębie duplikatów

Zbigniew S.:
Ok, ale należy zauważyć, że

1. Formuła MIN(JEŻELI(A2:$A$15=A2;B2:$B$15) nie sprawdza całego zakresu danych i nic dziwnego, że jest szybsza na większym zakresie wierszy. Takie podejście do formuły spowoduje jednak błędy w obliczeniach gdy nie będziemy operować na nie posortowanych danych, czyli np. na takim zakresie danych:

11 2014-08-21
11 2014-08-22
12 2014-08-23
11 2014-08-23
11 2014-08-23

Za pierwszym razem minimum dla 11 będzie ok, a za drugim nie.

2. Co do zażynania procka: oczywiście Twoja formuła, będzie szybsza, ale jak napisałem wcześniej nie będzie odporna na zaburzenia w kolejności danych. Poza tym po zablokowaniu w IF pierwszego wiersza excel przeliczał formułę prawie 2 x dłużej (10 000 wierszy). No i IF powoduje znaczący wzrost zużycia pamięci.

3. Oczywiście suma.iloczynów jest tam niekoniecznie najlepszym rozwiązaniem, a wprowadzenia tablicowego w tym układzie wymaga MAX.K. Po zastanowieniu się - o wiele lepiej sprawdzi się LICZ.JEŻELI na końcu w mojej formule.

4. Najszybszą możliwą formułą jest BD.MIN, ale jest bezużyteczna ze względu na sposób podawania kryteriów (dla większego zakresu danych)
Link do wypowiedziLink
Zbigniew Szyszkowski

Zbigniew Szyszkowski sprzątacz,
Ministerstwo
Rolnictwa i Rozwoju
Wsi

Temat: Obliczenia w obębie duplikatów

Panie Arturze
1 nie piszemy formuł do zastosowań ogólnych ale do konkretnych przypadków. Takie podejście w 99% gwarantuje nam uzyskanie możliwie szybkiej formuły robiącej to o co nam chodzi (wydajność tak napisanych formuł jest zwykle znacznie większa). W taki właśnie sposób jest zbudowana moja formułka... innymi słowy doskonale wiedziałem że zakres jest posortowany.
pkt 2... patrz pkt1, zakres nie jest zablokowany bo taka właśnie była intencja - po co przeliczać komórki o których wiemy na pewno że nie spełniają kryteriów? Dodam że formułka jest nawet na wyrost bo sprawdza komórki zawsze do końca zakresu. Jednak gdybyśmy wiedzieli że duplikatów nie ma więcej niż np 10 to zdecydowanie lepszym wyjściem byłoby zastosowanie adresowania względnego dla (powiedzmy) 20 komórek czyli gdyby nasz zakres był powiedzmy od A2 do A1000 to nie tak jak jest obecnie w mojej formułce A2:$A$1000 tylko A2:A21 (czyli 20 komórek). Takie podejście znów by nam skutkowało zwiększeniem ( i to znacznym) wydajności.
Zużycie pamięci przez JEŻELI (IF) jest takie jak rozmiar budowanych przy jej użyciu tablic...powiem szczerze że zupełnie nie rozumiem tego zarzutu ponieważ choćby Pana tablice zajmują o wiele więcej pamięci.

pkt 3 Nie MAX.K wymaga zatwierdzenia tablicowego tylko zastosowanie w pierwszym argumencie tej funkcji (tablica) budowanych (i mnożonych) tablic.Tak więc Pana formuła jeśli miałaby być zatwierdzana nietablicowo powinna wyglądać następująco
=MAX.K(INDEKS(($B$2:$B$15)*($A$2:$A$15=$A2);0);SUMA.ILOCZYNÓW(($A$2:$A$15=$A2)*1))
i w tym wypadku użycie SUMY.ILOCZYNÓW jest jak najbardziej na miejscu (oczywiście można ją zastąpić choćby przez LICZ.JEŻELI - kilka innych rozwiązań też jest możliwe)

pkt4....możliwe bo funkcje bazodanowe słyną z szybkości jednak ich zastosowanie bywa zwykle problematyczne. Niemniej nie sprawdzałem tego więc się nie wypowiadam :-)

Pozdrawiam.
Link do wypowiedziLink
« Wróć do tematów
Odpowiedz

Podobne tematy


Następna dyskusja:

Usuwanie duplikatów




Wyślij zaproszenie do
Anuluj