Elektroda.pl
Elektroda.pl
X
Elektroda.pl
Proszę, dodaj wyjątek dla www.elektroda.pl do Adblock.
Dzięki temu, że oglądasz reklamy, wspierasz portal i użytkowników.

Makro wyszukujące i sumujące odpowiednie komórki

11 Kwi 2014 00:26 1752 10
  • Poziom 8  
    Witam forumowiczów,

    Próbuję rozwiązać problem nad którym pracuję, ale mi nie idzie mi i stąd prośba do Was o pomoc.
    Pracuję nad arkuszem, który zsumuje wartości w komórkach odpowiadających miesiącom dla indeksów jakie zostaną identyfikowane przez kod na te, które podlegają wymianie na inny indeks. W arkuszu baza znajdują się indeksy „z” i indeksy „na”, kod powinien w arkuszach 1,2,3 i 4 wyszukać indeks „z” i „w pamięci” (zez zmiany oryginalnego indeksu) podmienić na indeks „na” i wykorzystując np. sumę.jeżeli sumować wartości w danej kolumnie. W arkuszu baza może wystąpić sytuacja połączenia wiele do jednego i idąc dalej w kolejnych arkuszach może wystąpić sytuacja że w np. wierszu 3;9;15;138 są indeksy, które powinny być wymienione na indeks w wierszu 200 tak więc wiersze 3;9;15;138 pozostają bez zmian, a wiersz 200 jest sumą wierszy powyżej i wiersza 200 (200=3+9+15+138+200; i tak dla kolejnych miesięcy <<kolumn>>). Mam nadzieje, że dość jasno to wyjaśniłem.

    W załączniku plik z danymi.

    Niby sprawa prosta, ale już tak się zmieszałem że nie mogę ruszyć dalej a każda próba kończy się fiaskiem, może Wy coś pomożecie.

    Z góry dziękuję za pomoc.
  • Moderator Programowanie
    Hmm... Byłbyś świetnym szyfrantem :) Niby po polsku, ale... Albo jakiś zaćmiony jestem ;)
    Można to jakoś w dwóch zdaniach?
  • Poziom 23  
    Faktycznie strasznie zawile to tłumaczysz.
    Chyba się domyślam scenariusza, pewnie zmieniono indeksy magazynowe, a tym masz zrobić zestawienie z poprzedniego roku kiedy były jeszcze stare, tak aby można było porównać dane z tymi z roku bieżącego.

    Masz dwie operacje.

    1.Zamiana indeksów
    2.Sumowanie

    Uważam, że makro zbędne. No chyba, że będziesz to robił codziennie.

    Ad1. Dołóż po kolumnie, w każdym z arkuszy, w kolumnie funkcja wyszukaj.pionowo wyszukujące zamienniki indeksów na podstawie arkusza switch.

    Ad2. Skopiuj nowe indeksy do nowego arkusza, po przesortowaniu użyj Dane->usuń duplikaty. Robisz to po to aby mieć listę unikalnych indeksów. Jak już będziesz to mieć, to w każdym miesiącu użyj funkcji suma.jeżeli, gdzie argumentem będzie indeks z tego nowego arkusza a zbiór wartości będziesz mieć w kolumnach kolejnych miesięcy z kolejnych zakładek.

    Aha, przed całością operacji "skondycjonuj" dane. Masz wartości 1, które oczywiście excel traktuje jak tekst. Masz indeksy 0014255, które też potraktuje jak tekst.

    Pozdrawiam.
  • Poziom 8  
    W dwóch zdaniach: chodzi o to by z arkusza baza pobrać pierwszy indeks "z" a do obliczeń w arkuszach 1-4 stosować indeks "na" wszędzie tam gdzie pojawi się ten oryginalny "z". Arkusz mam za zadanie dodawanie stanów, sprzedaży, itd. do indeksów w arkuszach 1-4 stosując zamienniki ("na") ale jednocześnie nie usuwając oryginalnych indeksów jakie tam są.

    Przykład:

    indeks 100 wymienia się na indeks 200
    indeks 150 wymienia się na indeks 200
    indeks 250 wymienia się na indeks 200
    tak więc w arkuszach 1-4 wszędzie tam gdzie pojawi się 100, 150 lub 250 wiersz z wartościami pozostaje bez zmian, ale wiersz z indeksem 200 będzie skaldal się z sumy wartości wiersza z id 200+ id 100+ id 150+ id 250.

    Mam nadzieję że to trochę rozjaśni sprawę

    Dodano po 3 [minuty]:

    "Uważam, że makro zbędne. No chyba, że będziesz to robił codziennie."

    Makro wg mnie jest konieczne, bo operacja będzie robiona nawet kilka razy dziennie i nie tylko przeze mnie.

    Dodano po 2 [godziny] 23 [minuty]:

    Nowa koncepcja na rozwiązanie problemu, a mianowicie wystarczy wstawić nową kolumnę A przed nr indeksów w arkuszach 1-4 i tam w pętli makro sprawdzi indeks w danym wierszu w kol.B, porówna z "bazą" i jeżeli będzie zamiennik to w kolumnie A w tym wierszu wstawi id zamiennika, jeżeli nie to przepisze indeks z kol B, a potem w kolejnych obliczeniach wystarczy użyć funkcji suma.jeżeli i powinno działać :D No to teraz tylko to napisać w VBA :D
  • Moderator Programowanie
    No a warunkowe WYSZUKAJ.PIONOWO w kolumnie pomocniczej nie wystarczyłoby?
  • Poziom 8  
    Przy takim podejściu do sprawy jak powyżej pewnie by wystarczyło, ale arkusz musi być odporny na "kreatywnych" użytkowników i ich pomysły. Zaszycie procedury w makrze i uruchamianie jej na "przycisk" będzie bezpieczniejsze. Dodatkowo w arkuszu i tak jest już bardzo dużo formuł i wolałbym nie dodawać nowych by nie obciążać go nadmiernie.
  • Poziom 9  
    witam,
    moje umiejętności jeśli chodzi o zaawansowane funkcje pisane w VBA są zdecydowanie bardzo ograniczone, dlatego bardzo proszę o pomoc.
    Musze wyznaczyć średnie ceny dla transakcji giełdowych w interwale 4 minutowym na podstawie szczegółowych danych. Czyli: dla wszystkich transakcji zawartych przykładowo od 9:00 do 9:04 muszę policzyć coś takiego: suma.iloczynów(wolumeny;ceny)/suma(wolumeny) z danego okresu. Próbowałam zrobić coś z kodu podanego przy okazji innego wątku (https://www.elektroda.pl/rtvforum/viewtopic.php?p=13048779&highlight=#13048779
    ) ale nic z tego nie wyszło - nie wiem jak przekształcić "MyZakres" tak, żeby dotyczył wybranych wierszy ale innych kolumn(wolumen i cena). na ten moment moja funkcja wyglada tak:
    Kod: vb
    Zaloguj się, aby zobaczyć kod

    poniżej załączam przykładowy skoroszyt z arkuszem z danymi i arkuszem do którego będą trafiały wyznaczone wartości.
    Będę naprawdę bardzo wdzięczna za jakąkolwiek pomoc:)
    Pozdrawiam:)
  • Poziom 32  
    Dzień dobry,
    Do wykonania tych obliczeń nie jest konieczny VBA. W załączniku propozycja z zastosowaniem formuł. Ze względu na użycie funkcji JEŻELI.BŁĄD trzeba użyć Excela >= 2007. Formuły są trochę za bardzo rozbudowane, ale wynika to z niestandardowego zapisu czasu w arkuszu "spolka" w rozbiciu na godziny, minuty i sekundy. Gdyby zastosować standardowy format czasu wzory znacznie by się skróciły. Zakres danych w formułach można poszerzyć stosownie do potrzeb.
    Pozdrowienia.
  • Poziom 9  
    Dzień dobry,
    w zasadzie to godzina jest podawana w standardowym formacie (oryginalne dane dorzuciłam do arkusza), ale rozdzieliłam ją pod makro (nie radze sobie z działaniem na datach niestety). A makro chciałam zrobić dlatego, że mam takie dane dla całego kwartału i dla kilku spółek (praca magisterska) i nie widziałam żadnego innego "sensownego" rozwiązania, które nie byłoby czasochłonne.
    Czy mogłabym prosić o wytłumaczenie działania formuły? nie ukrywam ze jestem trochę skołowana jej strukturą przez te mnożenie przez minuty;)
  • Poziom 32  
    Witam raz jeszcze,
    Formuła jest tablicowa jednokomórkowa, tzn. trzeba ją zatwierdzać przez CTRL+Shift+Enter, ale tylko w jednej komórce. Potem taką formułę można kopiować w zwykły sposób.
    Kod: text
    Zaloguj się, aby zobaczyć kod
    W formule występują podobne fragmenty:
    spolka!$A2:$A250*3600+spolka!$B2:$B250*60+spolka!$C2:$C250 = tablica zawierająca czas notowania przeliczony na sekundy (w miejscach, gdzie są teksty wystąpi błąd) = czas
    docelowy!D$1*3600+docelowy!D$2*60 = początek okresu obliczeniowego, przeliczony na sekundy (adres mieszany, kolumna zmienna) = pocz
    docelowy!E$1*3600+docelowy!E$2*60 = koniec okresu obliczeniowego, przeliczony na sekundy (adres mieszany, kolumna zmienna) = kon
    spolka!$D2:$D250 = zakres notowań = notowania
    spolka!$F2:$F250 = wolumen transakcji = wolumen
    Po wprowadzeniu tych umownych nazw formuła będzie bardziej zrozumiała:
    Kod: text
    Zaloguj się, aby zobaczyć kod
    Jej działanie jest następujące: każda z relacji czas>=pocz i czas<kon zwraca tablicę wartości PRAWDA lub FAŁSZ w zależności od tego czy czas mieści się w badanym przedziale. Ponieważ wartości te są użyte w wyrażeniu arytmetycznym PRAWDA jest interpretowana jako 1, a FAŁSZ jako 0. W rezultacie wyrażenie:
    (czas>=pocz)*(czas<kon)*notowania*wolumen = wartości
    zwraca tablicę iloczynów notowania*wolumen jeżeli mieszczą się w przedziale czasu, w przeciwnym razie 0. Niestety w zakresie godzin występują również teksty, które przy obliczaniu czasu generują błędy. Dlatego trzeba użyć funkcji JEŻELI.BŁĄD, dostępnej od Excela 2007, aby te błędy zamienić na zera, co umożliwi późniejsze sumowanie.
    Wyrażenie
    JEŻELI.BŁĄD((czas>=pocz)*(czas<kon)*notowania*wolumen;0)
    zamienia w tablicy wartości błędy na zera.
    Teraz można już użyć funkcji SUMA do podsumowania wartości transakcji. Jednak ponieważ argumenty sumy są wyrażeniami, formułę trzeba zatwierdzić jako tablicową.
    W mianowniku występują te same zależności, nie ma tylko czynnika notowania.
    Gdyby czas był zapisany w standardowy sposób, to znaczy jako część doby wystarczyłyby zwykłe porównania zawartości komórek, czy zakresów komórek bez dodatkowych przeliczeń - to samo dotyczy VBA:
    czas>=pocz oraz czas<kon

    Mam nadzieję, że teraz jest to jasne.
    Pozdrowienia
  • Poziom 9  
    jak najbardziej jasne:)
    Dziękuję bardzo za pomoc:))
    Pozdrawiam:)