Excel Forum - Porady, Pomoc,  Excel Help, Excel FAQ Strona Główna
 FAQ  RegulaminRegulamin  Szukaj   Użytkownicy   Grupy   Rejestracja   Profil   Twoje wiadomości   Zaloguj 


Poprzedni temat «» Następny temat
ID tematu: 64084 Skopiuj do schowka wyszukaj i zamień wartości
Autor Wiadomość
didul
Exceloholic


Posty: 134
Wysłany: 09-02-2019, 17:00   wyszukaj i zamień wartości

Witam,
Czy jest możliwość zrobienia makra tak aby działało od razu po wklejeniu danych do arkusza bez konieczności tworzenia nowego?
Dane zaczytywane są z baz SQL

Chciałbym aby została wyszukana pierwsza unikalna wartość z kolumny E (job ID) a do kolumny O (Calculated time) dla danego ID zostawiła prawdziwą wartość tylko w pierwszym wystąpieniu a pozostałe wyzerowała.

Przykład w Arkuszu 2

Załączam przykładowy plik

calculation.xlsx
Pobierz Plik ściągnięto 20 raz(y) 543.99 KB

ID posta: 362264 Skopiuj do schowka
 
 
Waldek 
ExcelSpec


Pomógł: 118 razy
Posty: 565
Wysłany: 10-02-2019, 01:08   Re: wyszukaj i zamień wartości

didul napisał/a:
... zostawiła prawdziwą wartość...
Jak rozpoznać, że wartość jest prawdziwa?
ID posta: 362289 Skopiuj do schowka
 
 
Artik 



Pomógł: 2602 razy
Posty: 8571
Wysłany: 10-02-2019, 03:03   

Waldek, po odświeżeniu tabeli mogą się pojawić duplikaty względem kolumny Job ID, dla których w kolumnie Calculated time występują te same wartości (różne od zera). Tzn. dla danego Job ID wszystkie Calculated time są takie same. Przynajmniej tak wynika z obserwacji załączonego pliku. Należy wyzerować wszystkie wystąpienia, oprócz pierwszego, dla każdego duplikatu Job ID.

didul
Rozwiązałem problem z wykorzystaniem filtra zaawansowanego.
W module Arkusz1 procedura zdarzeniowa Worksheet_TableUpdate wzbudzi się po aktualizacji tabeli. Wtenczas uruchomi się makro ZerujDuplikaty. Ponieważ potrwa to małą chwilę, dodałem okno informacyjne. Trwa to długo ponieważ zastosowana formuła LICZ.JEŻELI przeliczana jest dla każdego wiersza danych, mimo że występuje w kryteriach filtrowania tylko raz. Sama funkcja LICZ.JEŻELI do szybkich nie należy.
W kryteriach filtrowania mamy dwa kryteria. Jedno (w W2) ma wyszukać wszystkie duplikaty w kolumnie Job ID (zbiór A) z wykorzystaniem formuły:
Kod:
=LICZ.JEŻELI(_NAPM_Raport_DL_IDL[Job ID];$E2)>1
Drugie kryterium (w X2) ma wyszukać wszystkie wartości większe od zera w kolumnie Calculated time (zbiór B). Ponieważ oba kryteria są w jednym wierszu, zostanie wyfiltrowana tylko część wspólna zbiorów A i B. Potem już tylko pętelka po widocznych komórkach w kolumnie Job ID i wyzerowanie odpowiednich komórek.
Ale uwaga. Zakładam, że po filtrowaniu, a jeszcze przed zerowaniem, Job ID są pogrupowane. Jeżeli nie ma takiej pewności, należałoby przed filtrowaniem posortować dane względem kolumny Job ID.

Nie należy wykluczyć, że stosując inną metodę rozwiązania zadania uzyskamy lepsze wyniki czasowe. Ale ode mnie musi wystarczyć to co jest. :-)

Artik

Calculation didul.xlsm
Pobierz Plik ściągnięto 11 raz(y) 554.81 KB

_________________
Persistence is a virtue in the world of programming.
ID posta: 362295 Skopiuj do schowka
  Wersja Office-a: Win Office 365
 
didul
Exceloholic


Posty: 134
Wysłany: 10-02-2019, 09:00   

Witam,
Waldek to co opisał Artik jest dokładnie tym o co mnie chodziło. Dziękuję Panowie za sprawdzę na swoim pliku który ma ponad 20 tysięcy wierszy jak to działa i dam znać Póki co Artik plusik dla Ciebie.
ID posta: 362303 Skopiuj do schowka
 
 
didul
Exceloholic


Posty: 134
Wysłany: 11-02-2019, 07:11   

Witam,
Wszystko działa dobrze oprócz tego odświeżania tabeli.
Na jakiej zasadzie ma się to odbyć?
Po wklejeniu nowych wartość?
Po zaczytaniu nowych wartości z bazy SQL?

Póki co startuję z makrem manualnie.
ID posta: 362329 Skopiuj do schowka
 
 
Artik 



Pomógł: 2602 razy
Posty: 8571
Wysłany: 11-02-2019, 09:42   

didul napisał/a:
Po wklejeniu nowych wartość?
:niee
didul napisał/a:
Po zaczytaniu nowych wartości z bazy SQL?
Raczej :tak
Dokładniej, po użyciu przycisku [Odśwież] (tabelę).
Po to masz stworzone zapytanie i po to jest Tabela, by nie wklejać danych, czy budować zapytań za każdym razem.

Artik
_________________
Persistence is a virtue in the world of programming.
ID posta: 362336 Skopiuj do schowka
  Wersja Office-a: Win Office 365
 
didul
Exceloholic


Posty: 134
Wysłany: 11-02-2019, 20:37   

Witam,
Artik
Mam pytanie co masz na myśli mówiąc należy je pofiltrować mówiąc o danych? Nie mam dostępu do danych SQL więc jak to zrobić? . Po dzisiejszym zaczytaniu danych nie były w kolejność. I co wtedy? Da się przerobić może makro aby najpierw uszeregowło kolejność na dopiero później zerowało wartości jak w Twoim makro?
ID posta: 362380 Skopiuj do schowka
 
 
kuma 
Excel Expert


Pomógł: 1141 razy
Posty: 3443
Wysłany: 11-02-2019, 21:36   

Jeśli dobrze zrozumiałem to, po pobraniu nowych danych, uruchom poniższy kod
Kod:
Sub Zeruj()
    Dim d As Object
    Dim a()
    Dim i As Long
   
    With Sheets("Arkusz1")
        a = .Range("E2:O" & Last(.Range("A:A"))).Value
        Set d = CreateObject("scripting.dictionary")
        For i = 1 To UBound(a)
            If d.exists(a(i, 1)) Then
                a(i, 11) = Empty
            Else
                d(a(i, 1)) = Empty
            End If
        Next
        .[O2].Resize(UBound(a), 1) = Application.Index(a, 0, 11)
    End With
    Set d = Nothing
End Sub


Private Function Last(rng As Excel.Range) As Long
 ' wg. Ron de Bruin, 20 Feb 2007
 ' http://www.rondebruin.nl/last.htm
     On Error Resume Next
     Last = rng.Find(What:="*", _
                     After:=rng.Cells(1), _
                     Lookat:=xlPart, _
                     LookIn:=xlValues, _
                     SearchOrder:=xlByRows, _
                     SearchDirection:=xlPrevious, _
                     MatchCase:=False).Row
     On Error GoTo 0
 End Function
Pozdrawiam.
ID posta: 362383 Skopiuj do schowka
 
 
didul
Exceloholic


Posty: 134
Wysłany: 11-02-2019, 23:42   

Witam,
Dziękuję mogę go połączyć z kodem Artika tak Hop Siup kopiuj wklej?
Czy jest to kod zastępujący kod Artika co oznacza segregujący i usuwający duplikaty?

Artik w tej chwili dokonałem sprawdzenia Twojego kodu pod względem zaczytania nowej bazy i nie filtrowania kolejności w ID job wszystko zadziałało poprawnie, możesz się wypowiedzieć co miałeś na myśli pisząc musza być ułożone kolejno?
Trzecia próba i niestety nieudana, poflirtowało dobrze ale pod warunkiem że operator ten sam jak ktoś inny miał tą samą pracę niestety pozostawiło kalkulowany czas
Ostatnio zmieniony przez didul 12-02-2019, 00:12, w całości zmieniany 1 raz  
ID posta: 362393 Skopiuj do schowka
 
 
kuma 
Excel Expert


Pomógł: 1141 razy
Posty: 3443
Wysłany: 11-02-2019, 23:57   

Mój nie jest powiązany z kodem Artika, działa w inny sposób.
Pozdrawiam.
ID posta: 362395 Skopiuj do schowka
 
 
Bill Szysz 
Excel Expert


Pomógł: 779 razy
Posty: 3258
Wysłany: 12-02-2019, 00:46   

Skoro ciągniesz dane za pomocą Power Query to nie lepiej całość, tego co chcesz osiągnąć, zrobić w PQ?
Kod wyglądałby jakoś tak (nie mam dostępu do Twoich danych więc mam nadzieję, że się nie "rypnąłem" w którymś momencie)
Kod:
let

    fxPopraw = (t as table, min as number) =>
        let
            #"Added Conditional Column" = Table.AddColumn(t, "Pomoc", each if [Indeks] = min then [Calculated time] else 0),
            #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"Calculated time"}),
            #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Pomoc", "Calculated time"}}),
            #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",ColNames)
        in
            #"Reordered Columns",

    Source = Sql.Databases("AXSQLP01"),
    AX50_Prod_PL = Source{[Name="AX50_Prod_PL"]}[Data],
    dbo__NAPM_Raport_DL_IDL = AX50_Prod_PL{[Schema="dbo",Item="_NAPM_Raport_DL_IDL"]}[Data],

    #"Added Index" = Table.AddIndexColumn(dbo__NAPM_Raport_DL_IDL, "Indeks", 0, 1),
    ColNames = Table.ColumnNames(#"Added Index"),
    #"Grouped Rows" = Table.Group(#"Added Index", {"Job ID", "Calculated time"}, {{"Liczność", each Table.RowCount(_), type number}, {"tbl", each _, type table}, {"MinIdx", each List.Min([Indeks]), type number}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each [Job ID] <> null and [Job ID] <> ""),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Ok", each if [Calculated time] = 0 then [tbl] else if [Liczność] = 1 then [tbl] else fxPopraw([tbl], [MinIdx])),
    Custom1 = Table.Combine(#"Added Custom"[Ok]),
    #"Sorted Rows" = Table.Sort(Custom1,{{"Indeks", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Indeks"})
in
    #"Removed Columns"
_________________
Szkolenia z Power Query!!!

Pozdrawiam, były szbill62 aktualnie Bill Szysz
ID posta: 362398 Skopiuj do schowka
 
 
didul
Exceloholic


Posty: 134
Wysłany: 12-02-2019, 07:02   

Witam,
Bill Szysz Możemy lepiej 🙂 ale ... 😔Niestety musiałbyś opisać coś więcej co gdzie i jak klikać te tematy są mi póki co obce. Nigdy się tym nie bawiłem. Jeśli znajdziesz chwilę na objaśnienie chetnie sprawdzę co i jak.
Zaznaczam że w tej chwili wszystkie wyniki obrobione są w tym samym pliku w kolejnych arkuszach a tabelach przestawnych. O ile to wogole ma jakieś znaczenie dla tego co napisałeś.
ID posta: 362410 Skopiuj do schowka
 
 
Bill Szysz 
Excel Expert


Pomógł: 779 razy
Posty: 3258
Wysłany: 13-02-2019, 13:59   

No to załączam pliczek. Są w nim dwa zapytania - oryginalne (Twoje) i zmodyfikowane (moje).
Jedyne co musisz zrobić to załadować wynik mojego zapytania do arkusza (np do Arkusz2).

calculation_PQ_BS.xlsx
Pobierz Plik ściągnięto 9 raz(y) 537.97 KB

_________________
Szkolenia z Power Query!!!

Pozdrawiam, były szbill62 aktualnie Bill Szysz
ID posta: 362536 Skopiuj do schowka
 
 
didul
Exceloholic


Posty: 134
Wysłany: 14-02-2019, 20:20   

Witam
Bill Szysz dziękuję bardzo wygląda że wszystko działa prawidłowo Powiedz mi proszę tylko jak mogę to przerzucić do innego pliku? Byłoby dużo mniej pracy aniżeli wszystkie tabele przestawne robić od nowa.
Oczywiście plusik dla Ciebie za dotychczasową pracę.
ID posta: 362618 Skopiuj do schowka
 
 
Wyświetl posty z ostatnich:   
Odpowiedz do tematu
Nie możesz pisać nowych tematów
Nie możesz odpowiadać w tematach
Nie możesz zmieniać swoich postów
Nie możesz usuwać swoich postów
Nie możesz głosować w ankietach
Nie możesz załączać plików na tym forum
Możesz ściągać załączniki na tym forum
Dodaj temat do Ulubionych
Wersja do druku

Skocz do:  

Powered by phpBB modified by Przemo © 2003 phpBB Group
Theme xandgreen created by spleen& Programosy modified v0.3 by warna
Opieka techniczna www.marketingNET.pl

Archiwum

Strona używa plików cookies.

Kliknij tutaj, żeby dowiedzieć się jaki jest cel używania cookies oraz jak zmienić ustawienia cookie w przeglądarce.
Korzystając ze strony użytkownik wyraża zgodę na używanie plików cookies, zgodnie z bieżącymi ustawieniami przeglądarki.
Sprawdź, w jaki sposób przetwarzamy dane osobowe