Autor: marzatela

Suma czasu

Dawno nie było nic o czasie, pora na przykładowe rozwiązanie.  Tym bardziej, że zawsze warto przypominać, że Data i czas to liczba

Załóżmy, że mamy taką tabelkę:

kliknij, aby powiększyć

Kolumna B – to czas Start, kolumna C– czas Stop. 
Obie kolumny są sformatowane tak:

kliknij, aby powiększyć

Kolumna D to formuła:
D2=JEŻELI(C2<B2;1+C2-B2;C2-B2) 
formatowanie tej kolumny:

kliknij, aby powiększyć

Takie formatowanie pozwala na wyświetlanie czasu w formie godzin i minut, ale pozwala na wyświetlenie również czasu powyżej 24 godzin (widać to w wierszu Sumy).

Kolumna E to formuła:
E2=JEŻELI(C2<B2;24*(1+C2-B2);24*(C2-B2))
formatowanie tej kolumny:

Tu mamy po prostu czas w liczbie godzin. 

Kolumny D i E to kolumny, w których uwzględniono przypadek, gdy czas Stop jest mniejszy od czasu Start (czyli jest przejście przez północ). Gdyby nie było sprawdzenia tego warunku – czas byłby w tych przypadkach ujemny czyli w kolumnie E widoczna byłaby liczba ujemna, a w kolumnie D – widoczne były tylko „krzaczki” – czas nie może być ujemny. 

A tu możesz mi postawić kawę: 

buycoffee.to/marzatela

Funkcja Połącz.Teksty

Funkcja Połącz.Teksty() to  funkcja tekstowa Excela. W wyniku działania funkcji otrzymujemy wartość typu Tekst, będącą złączeniem poszczególnych argumentów, przedzielonych wspólnym separatorem. 

kliknij, aby powiększyć

Argumenty funkcji to:

    • Ogranicznik– ciąg tekstowy lub znak oddzielający poszczególne elementy, może to być również pusty tekst
    • Ignoruj_puste – wartość typu Prawda(domyśłna) lub Fałsz  – czy pomijać puste wartości
    • Tekst1 – zmienna typu tekstowego (może być to zakres komórek) 
    • ….
    • Tekst(n) – zmienna typu tekstowego (może być to zakres komórek) 

Funkcja Połącz.Teksty stanowi właściwie modyfikację funkcji Złącz.Tekst – 
różnica polega na tym, że tu z góry ustalamy ogranicznik rozdzielający poszczególne zmienne. Pozwala to na znaczne skrócenie i uproszczenie formuły, w tym zastąpienie adresów poszczególnych komórek zakresem komórek.
Na przykład:

kliknij, aby powiększyć

Nawet w bardziej złożonym przypadku, gdy np. chcemy zastosować rózne ograniczniki w różnych miejscach, wystarczy zastosować formułą zagnieżdżoną.
Na przykład: 

kliknij, aby powiększyć

Tu formuła dla komórki D2 i kolejnych w tej kolumnie może wyglądać tak: 
=POŁĄCZ.TEKSTY(” „;PRAWDA;A2:B2;POŁĄCZ.TEKSTY(„-„;PRAWDA;C2:D2))

Odpowiednikiem funkcji w VBA jest funkcja arkuszowa TextJoin().

Kurs Excel na rozmowie rekrutacyjnej

 

Funkcja Złącz.Tekst

Funkcja Złącz.Tekst() to jedna z funkcji tekstowych Excela. W wyniku działania funkcji otrzymujemy wartość typu Tekst, będącą złączeniem poszczególnych argumentów. 

Argumenty funkcji to:

    • Tekst1 – zmienna typu tekstowego
    • Tekst2 – zmienna typu tekstowego
    • ….
    • Tekst(n) – zmienna typu tekstowego

Działanie funkcji jest bardzo proste: łączy poszczególne argumenty w jeden ciąg. Nic wielkiego, a jednak często stosowana funkcja, przydaje się. 
Warto tu pamiętać także o separatorach, oddzielających poszczególne argumenty, żeby nie zlewały się w jeden tekst – czasem mam to znaczenie. 
Na przykład:

kliknij, aby powiększyć

Samą funkcję Złącz.Tekst można też zastąpić znakiem & – efekt będzie ten sam. 
Na przykład formuła dla komórki C2 może wyglądać tak: 
=A2 & ” ” & B2

Odpowiednikiem funkcji w VBA jest funkcja arkuszowa Concact().

 

 

Wykorzystany zakres arkusza

UsedRange to jedna z właściwości obiektu Worksheet.  Zwraca wartość typu Range wyznaczony przez pierwszą i ostatnią niepustą komórkę w arkuszu. 

kliknij, aby powiększyć

Jeżeli np. pierwsza niepusta komórka to B2, a ostatnia to D10, to właściwość UsedRange to zakres Range(„B2:D10”). 
Trzeba pamiętać też o tym, że nawet jeżeli komórka wydaje się pusta, wcale nie musi taka być – może tam być np. formuła zwracającą wartość tekstu o zerowej długości. Także wstawienie zwykłej spacji w komórce sprawia, że nie jest ona już pusta. 

Do czego można ją wykorzystać? Możliwości tu jest wiele: do zaznaczania, formatowania, czyszczenia itp.  
Jedną z ciekawszych możliwości może być też sprawdzenie, czy w arkuszu nie ma np. jakichś niepotrzebnych śmieci. Mogą się pojawić m.in.  przy wielokrotnym kopiowaniu i wstawianiu różnych danych z innych plików. 
Może to w efekcie spowodować znaczne i zupełnie niepotrzebne zwiększenie rozmiaru pliku i spowolnienie pracy. 
Np.

Public Sub ZaznaczUsedRange()
ActiveSheet.UsedRange.Select
End Sub

Po uruchomieniu tej procedury – w arkuszu zostanie zaznaczony cały wykorzystywany zakres komórek. 
Np.

kliknij, aby powiększyć

Tu akurat wstawiłam spację w komórce K19. Normalnie jej nie widać, ale  dla zakresu UseRange ma znaczenie. Czasem warto sprawdzić. 

A tu możesz mi postawić kawę: 

buycoffee.to/marzatela

Ukrywanie i odkrywanie arkuszy w skoroszycie

Jak można zabezpieczyć skoroszyt, aby różni użytkownicy mieli dostęp tylko do przeznaczonych dla siebie arkuszy? 
Możemy to zrobić na przykład korzystając z właściwości Visible arkusza. 

Załóżmy, że mamy skoroszyt z takimi arkuszami:

Każdy użytkownik może mieć dostęp tylko do arkusza Start – gdzie może się zalogować oraz do własnego arkusza. Admin widzi wszystkie arkusze, w tym arkusz Loginy. 

Arkusz Start może wyglądać np.tak: 

Wybór loginu z listy rozwijalnej w komórce F5. Hasło wpisywane do komórki F7. 

Do tego kilka procedur VBA: 

Procedura sprawdzająca prawidłowość wpisanego hasła i odkrywająca arkusz przypisany do użytkownika. 

Sub OdkryjArkusz()
Dim Login As String
Dim Haslo As String
Dim ZapisaneHaslo As String
Dim i As Integer
With Worksheets(„Start”)
Login = .Range(„F5”)
Haslo = .Range(„F7”)
End With
ZapisaneHaslo = WorksheetFunction.VLookup(Login, Worksheets(„Loginy”).Range(„A:B”), 2, False)
If Haslo = ZapisaneHaslo Then
   If Login = „admin” Then
     For i = 1 To Worksheets.Count
        Worksheets(i).Visible = xlSheetVisible
     Next i
  Else
       Worksheets(Login).Visible = xlSheetVisible
  End If
End If
End Sub

Procedura ta może być uruchamiana albo zdarzeniem arkusza albo przyciskiem w arkusz Start. Oczywiście powinno być także sprawdzenie, czy wybrano Login z listy, czy pole Hasło nie jest puste i ewentualnie jakieś komunikaty o błędnym wpisanym haśle – to tylko wersja podstawowa. 

Dodatkowo jeszcze w pliku powinna być procedura ukrywająca wszystkie arkusze z wyjątkiem Start i czyszcząca pola na Login i Hasło. 
Może to wyglądać np. tak:

Sub UkryjArkusze()
Dim i As Integer
For i = 1 To Worksheets.Count
If Worksheets(i).Name = „Start” Then
     Worksheets(i).Range(„F5”).ClearContents
     Worksheets(i).Range(„F7”).ClearContents
  Else
     Worksheets(i).Visible = xlSheetVeryHidden
End If
Next i
End Sub

Ta procedura powinna być uruchamiana zdarzeniem skoroszytu przed zamknięciem. 

A tu możesz mi postawić kawę: 

buycoffee.to/marzatela