Wyszukiwanie wartości w tabeli, zakresie

Jakiś czas temu napisałem wpis o wyszukiwaniu w tablicy dwu wymiarowej. Szukałem tam jaka była wartości sprzedaży Janka, produktów w kolorze czerwonym (na przykład).

Jeden z czytelników, zwrócił mi uwagę iż trafił na ten wpis szukając rozwiązania innego problemu. Może się zdarzyć, że będziemy chcieli znaleźć w tabeli adres pozycji o określonej wartości. Czyli gdzie znajduje się komórka o wartości, powiedzmy 358,82 (Janek, Czerwony). Na końcu wpisu znajduje się link do przykładowego pliku.

Poniżej posłużę się powyższą tabelą.

Zaczniemy prosto, stworzymy dwie formuły, do wyciągania wiersza i kolumny w której znajduje się poszukiwana wartość. Szukana wartość, będzie się znajdować w komórce A9.

Poszukiwanie wiersza:

=SUMA.ILOCZYNÓW((B2:F6=A9)*WIERSZ(B2:F6))

Poszukiwanie kolumny:

=SUMA.ILOCZYNÓW((B2:F6=A9)*NR.KOLUMNY(B2:F6))

Uzyskany efekt:

Należy pamiętać, że formuła zwraca absolutny numer wiersza. Jeżeli nasza tabela będzie przesunięta, to numery nie będą się zgadzać z względnymi kolumnami i wierszami tabeli. Poniżej okaże się, że nie jest to problem.

Problem jest natomiast inny, w tej chwili poszukujemy unikalnej wartości, takiej, która w tabeli występuje tylko raz. Jeżeli będę chciał w znaleźć wartość 649,74 to formuły zwrócą mi poniższe wyniki:

Są to sumy wszystkich znalezionych wierszy i kolumn. Spróbujmy więc rozwiązać ten problem i znaleźć pierwszą lub ostatnią wartość. Zaczniemy od prostszego przypadku, ostatnia wartość.

Numer wiersza:

=SUMA.ILOCZYNÓW(MAX((B2:F6=A9)*WIERSZ(B2:F6)))

Numer kolumny:

=SUMA.ILOCZYNÓW(MAX((B2:F6=A9)*NR.KOLUMNY(B2:F6)))

Formuły zwracają nam najwyższą wartość kolumny i wiersza.

Jak znaleźć pierwszą wartość? Na pierwszy rzut oka, można by się posłużyć funkcją MIN, należy jednak pamiętać, że w poszukiwanej tabeli mamy dużo zer i MIN zwróci na po prostu zero. Posłużę, się więc funkcją MIN.K, która zwróci mi n-tą najmniejszą wartość. Skąd wiem którą? Pierwsza, druga, trzecia itd. wartość to zera. Muszę więc policzyć ile wartości w tabeli nie równa się poszukiwanej liczbie i dodać 1.

Numer wiersza:

=SUMA.ILOCZYNÓW(MIN.K((B2:F6=A9)*WIERSZ(B2:F6);LICZ.WARUNKI(B2:F6;"<>"&A9)+1))

Numer kolumny:

=SUMA.ILOCZYNÓW(MIN.K((B2:F6=A9)*NR.KOLUMNY(B2:F6);LICZ.WARUNKI(B2:F6;"<>"&A9)+1))

Uzyskujemy poniższy efekt:

Mamy już prawidłowo wyznaczoną kolumnę i wiersz. Spróbujmy scalić wynik do jednej komórki i uzyskać adres. Posłużę się funkcją ADRES:

=ADRES(Numer wiersza, numer kolumny)

 

=ADRES(SUMA.ILOCZYNÓW(MIN.K((B2:F6=A9)*WIERSZ(B2:F6);LICZ.WARUNKI(B2:F6;"<>"&A9)+1));SUMA.ILOCZYNÓW(MIN.K((B2:F6=A9)*NR.KOLUMNY(B2:F6);LICZ.WARUNKI(B2:F6;"<>"&A9)+1))) Czytaj więcej...

To-Do – nowa aplikacja mobilna microsoft

Microsoft udostępnił w wersji „preview” nową aplikację do zarządzania zadaniami „To-Do”. Na pierwszy rzut oka wygląda bardzo przejrzyście. Od konkurencji ma się wyróżniać „inteligentnymi” algorytmami sugerującymi co wykonać w pierwszej kolejności. Osobiście do takiej „inteligencji” programów podchodzę sceptycznie.  W duecie komputer, użytkownik to ja staram się być tym inteligentnym.

Zaletą aplikacji jest integracja z outlookiem i interfejs webowy, przez co możemy zarządzać zadaniami przez telefon lub komputer. Czytaj więcej...

Skrót do szybkiego sumowania

Często używam Excel jako kalkulatora, przydaje mi się wtedy skrót alt + =. Po wpisaniu kilku wartości mogę je szybko zsumować.

Tablice VBA – Helion

Jeżeli już biorę się za recenzję, to żeby coś polecić. Dzisiaj chciałbym zwrócić uwagę na coś co mogę polecić bardzo szczerze, „Tablice informatyczne. VBA dla Excela” z Heliona. Oczywiście z takich tablic trudno będzie się nauczyć VBA, ale już na przykład po moim kursie takie tablice będą niezbędną pomocą dla programisty na każdym poziomie.

Nawet najbardziej doświadczony programista musi czasem poszukać w
internecie informacji o podstawowych elementach języka. Jak wyciągało się część ciągu znaków, jak pisze Case, jaki zakres ma Long itd. Co prawda można to szybko znaleźć w internecie, ale jeszcze szybsze (i wiarygodniejsze) są omawiane tutaj tablice. Czytaj więcej...

Kurs VBA – Klasy – konstruktory i desktruktory

Najważniejsze klasy modelu obiektowego Excela mają przypisane zestawy zdarzeń. Klasy użytkownika mają tylko dwa zdarzenia. Nazywają się konstuktor i destkruktor. Pierwsze uruchamia się w momencie inicjalizacji klasy, drugie w momencie jej deinicjalizacji.

Deklaruje się je następująco w module klasy:

Option Explicit

Private Sub Class_Initialize()
    MsgBox "Start"
End Sub

Private Sub Class_Terminate()
    MsgBox "Koniec"
End Sub

Public Sub PrintHello()
    MsgBox "Hello"
End Sub

Procedurę Class_Initialize nazywamy konstruktorem a procedurę Class_Terminate destruktorem.

Używam teraz moją klasę w krótkim programie.

Sub Zdarzenia()

    Dim lref_Test As cls_Test
    
    Set lref_Test = New cls_Test
    
    lref_Test.PrintHello
   
End Sub

Po uruchomieniu pokażą się kolejno trzy komunikaty:

  1. Start
  2. Hello
  3. Koniec

Poszczególne zdarzenia wykonują się w tych miejscach:

 

Do czego mogą być używane konstruktory i desktruktory?

Najlepiej używać je do inicjalizacji wartości atrybutów klasy. Niektóre atrybuty też są mogą być obiektami i należy im nadać wartość przy pomocy set..new.

Należy uważać, aby nie nadużywać konstruktorów, może to doprowadzić do zmniejszenia czytelności kodu. Zawartość konstruktora i destruktora jest w pewien sposób „ukryta” względem głównych procedur.

Option Explicit

Sub Zdarzenia()

    Dim lref_Raport As cls_Raport
    
    Set lref_Raport = New cls_Raport
    
    lref_Raport.WyswietlDane
   
End Sub

Widać powyżej tylko wywołanie procedury WyswietlDane, która prawdopodobnie wyświetla jakieś dane. Niestety programista klasy cls_Raport postanowił ukryć dużo funkcjonalności w konstruktorze i destruktorze. Jest tam pobieranie danych od użytkownika oraz zapisywanie na koniec do pliku. Gdyby umieścił ten kod w zwykłych metodach, użycie klasy byłoby bardziej przejrzyste:

Option Explicit Sub Zdarzenia() Dim lref_Raport As cls_Raport Set lref_Raport = New cls_Raport lref_Raport.PobierzDane lref_Raport.WyswietlDane lref_Raport.ZapiszDane End Sub Czytaj więcej...

UDF – formuły z funkcjami użytkownika

Za pomocą UDF (user defined functions, funkcje zdefiniowane przez użytkownika) programista może stworzyć nowe funkcje do użycia w formułach. W poniższy sposób używam moją nową funkcję:

Kod znajduje się w module VBA:

Option Explicit

Function SUMUJPARZYSTE(rng As Range)
  Dim lr_cell
  
  For Each lr_cell In rng
    If lr_cell.Value Mod 2 = 0 Then
      SUMUJPARZYSTE = SUMUJPARZYSTE + lr_cell.Value
    End If
  Next lr_cell
End Function

Tworzy je się tak samo jak zwykłe funkcje VBA. Umieszczone w module kodu mogą być używane w tym samym pliku. Można je też dodać do pliku personal  lub dodatku. Funkcje UDF podlegają jednak kilku ograniczeniom.

Ograniczenia

Dzięki UDF programista może rozszerzyć wachlarz funkcji dostarczanych przez Excel. Tak jak zwykłe funkcje mogą jedynie zwracać wartość do komórki w której się znajdują. Nie mogą więc robić poniższych:

  • Dodawać, usuwać i formatować komórki na arkuszu,
  • Edytować komórki w arkuszu,
  • Modyfikować zmienne środowiskowe, takie jak odświerzanie ekranu, czy przeliczanie automatyczne,
  • Ustawiać atrybuty czy wywołuwać większość metod modelu obiektowego Excela,

Dane wejściowe powinny  być zawsze pobierane przez argumenty, czyli bez odwoływania się do obiektu Range. Odwołanie do danych przez Range może doprowadzić do niespodziewanych rezultatów, ponieważ zaburza Excelowi drzewo przeliczania formuł. Nasza formuła może nie być przeliczona kiedy powinna.

Zwracanie błędów

UDF mogą zwracać błędy tak jak standardowe funkcje. Funkcja CVErr tworzy odpowiedni variant.

Function Podziel(A As Double, B As Double) As Variant
  If B = 0 Then
    Podziel = CVErr(xlErrDiv0)
  Else
    Podziel = A / B
  End If
End Function

Poniższe stałe mogą być używane do zwracania innych błędów.

  • xlErrDiv0 – #DIV/0
  • xlErrNA – #N/A
  • xlErrName – #NAME?
  • xlErrNull – #NULL
  • xlErrNum – #NUM
  • xlErrRef – #REF
  • xlErrValue – #VALUE

Argumenty opcjonalne

Poniżej znajduje się przykład w którym drugi argument jest opcjonalny. Funkcją IsMissing sprawdzam czy argument został przekazany.

Function sumuj_co_drugi(ref As Range, Optional jakie As Variant) As Variant Dim lref_cell As Range Dim lint_modulo As Integer If IsMissing(jakie) = True Then jakie = gc_Parzyste ElseIf jakie <> gc_Parzyste And jakie <> gc_NieParzyste Then sumuj_co_drugi = CVErr(xlErrValue) Return End If If jakie = gc_Parzyste Then lint_modulo = 0 Else lint_modulo = 1 For Each lref_cell In ref.Cells If lref_cell.Value Mod 2 = lint_modulo Then sumuj_co_drugi = sumuj_co_drugi + lref_cell.Value End If Next lref_cell End Function Czytaj więcej...

Jedna strona poziomo

Poniżej pokażę jak umieścić część dokumentu w poziomej orientacji. Po co? Mi się ta funkcja przydaje przy pisaniu długich dokumentacji z rysunkami i diagramami. Zdarza się, że jakiś diagram jest znacznie czytelniejszy poziomo. Mogę ustawić osobną sekcję na jedną stronę i zmienić orientację na poziomą tylko dla tej strony.
Wygląda to bardzo dobrze na komputerze i nie utrudnia w żaden sposób drukowania.

Instrukcja

Tworzę nowy dokument i zapisuję kilka zdań na pierwszej stronie. Dla testów można użyć losowy tekst lorem. Zaznaczam środkową część tekstu: Czytaj więcej...

Kurs VBA – Klasy – Atrybuty i właściwości

Zastanawiałem się przez jakiś czas czy atrybuty i właściwości podzielić na dwa osobne posty. Występują one bardzo często razem i poza klasą używa się je tak samo, jednak należy pamiętać, że są to dwie osobne funkcjonalności klas.

Atrybuty określają aktualny stan obiektu. Dla przykładowej klasy Samochód może to być:

  • Prędkość
  • Pojemność
  • Czy drzwi są zamknięte?
  • Czy światła są zapalone?
  • Stan oleju

Spójrzmy na taką (bardziej praktyczną) instrukcję:

If gr_plik.IsOpen = True Then

Mamy tutaj atrybut, zadeklarowany następująco w module klasy.

Public IsOpen As Boolean

Przybiera wartość True, jeżeli plik jest otwarty lub False, jeżeli zamknięty. W metodzie otwierającej plik ustawiamy ten atrybut na True. Mogę więc napisać gdzieś:

If gr_plik.IsOpen = True Then
   MsgBox "Plik jest wciąż otwarty"
End If

Czy ma w takim razie sens poniższa instrukcja?

gr_plik.IsOpen = False

Pozornie ma sens, jest jednak niebezpieczna. Ostatecznie zamykanie pliku nie może się ograniczać do ustawienia wartości jakieś zmiennej, plik należy zapisać. Z pomocą przychodzą właściwości, dzięki którym możemy tworzyć coś pomiędzy atrybutami a metodami. Poza klasą używa się je jak atrybuty. W środku klasy jest to jednak szereg wykonywanych instrukcji. Utworzę więc właściwość:

Property Let isOpen(iv_IsOpen As Boolean)
  If iv_IsOpen = False Then
    CloseFile
  End If
End Property

Dzięki temu powyższe przypisanie wartości False uruchamia instrukcje, które wywołują metodę CloseFile.

Wkrótce wyjaśnię to wszystko dokładniej. Przejdźmy więc do opisu używania atrybutów i właściwości

Atrybuty

Deklaruje się je jak każdą inną zmienną modułową. Mogą być prywatne i publiczne. Zadeklaruję dwie zmienne.

Private Wynagrodzenie As Double
Public Nazwisko As String

Atrybuty publiczne mogę używać poza klasą. Poniżej widać, że intelisense podpowiada mi atrybut publiczny. Prywatne oczywiście mogą być używane tylko w metodach tej klasy.

Właściwości

Tutaj wreszcie pojawia się jakaś nowość w stosunku do zwykłych modułów. Właściwości deklaruje się przy pomocy specjalnych słów kluczowych.

  1. Get – zwraca wartość lub obiekt
  2. Let – ustawia wartość
  3. Set – ustawia obiekt

Public Property Get wlasciwoscNazwa () As Type End Property Public Property Let wlasciwoscNazwa(argumentNazwa As Type ) End Property Public Property Set wlasciwoscNazwa(argumentNazwa As Type ) End Property Czytaj więcej...

Office lens – skanowanie telefonem

Natrafiłem ostatnio na ciekawą aplikację do skanowania dokumentów telefonem. Nazywa się Office Lens i jest dostępna za darmo w sklepach z aplikacjami. Zajmuje ledwo 50 mb, więc jeśli wolisz spróbować ją sam zamiast rozczytywać się nad moimi obserwacjami, to zapraszam do ściągnięcia, warto.

Ogólnie

Pierwsze co się rzuca w oczy, to uprawnienia, o które prosi aplikacja. Potrzebne jej do działanie jedynie dostęp do robienia i zapisywania zdjęć. Jest to miłe zaskoczenie. Nie lubię, kiedy małe aplikacje żądają dostępu do smsów, telefonu, tożsamości czy kontaktów. Przejmują wtedy praktycznie kontrolę nad telefonem.
Jakość działania mobilnego skanera z pewnością jest zależna od jakości aparatu w telefonie. Osobiście testowałem program na Xperi Z5 Compact z aparatem 23Mpx, czyli raczej dobrym sprzętem. Z drugiej jednak strony o sile aplikacji decydują algorytmy programu, a te działają świetnie. Czytaj więcej...

Organizowanie mejli

Dzisiaj chciałbym się podzielić kilkoma ustawieniami Outlook, które na co dzień bardzo ułatwiają mi ogarnięcie zalewu mejli. Dzięki temu rzeczy nie załatwione zawsze mam na górze listy. Wygląda to jak poniżej.

Na górze listy widać mejle oznaczone flagą, są tak oznaczone wszystkie nowe mejle. Po załatwieniu sprawy klikam flagę i mejl spada poniżej. Dzięki temu każda nie załatwiona sprawa jest na górze, nawet jeżeli mejl przyszedł miesiąc temu.

Powyższy efekt można ustawić dzięki dwóm ustawieniom. Czytaj więcej...