Archiwum kategorii: Excel

20 alternatyw dla WYSZUKAJ.PIONOWO

20 alternatyw dla WYSZUKAJ.PIONOWO

20 alternatyw dla WYSZUKAJ.PIONOWO

Ja używam angielskiej wersji pakietu Microsoft, w związku z tym używam angielskich nazw funkcji, niemniej podaję również odpowiednią nazwę polską. Ponadto budowa funkcji w wersji angielski rożni się od polskiej tym, że poszczególne argumenty oddzielone są przecinkami, w odróżnieniu do średników używanych w wersji np. polskiej. Wynika to z faktu, że w wersji angielskiej część całkowitą liczby oddzielamy od części dziesiętnej kropką, a w wersji polskiej – przecinkiem.

WYSZUKAJ.PIONOWO – funkcja, która znajduje i zwraca powiązaną wartość ze wskazanego zakresu danych. W zadaniu będę używał zakresów w formacie Tabel.

Pewnie każdy z was używa jednej z najbardziej popularnych funkcji Excela, jaką bez wątpienia jest WYSZUKAJ.PIONOWO. Czy wiesz jednak o tym, że można ją zastąpić innymi funkcjami czy też metodami, aby osiągnąć ten sam rezultat? Postanowiłem podzielić się 20 alternatywami, przy okazji pokazując możliwości innych formuł oraz metod stosowanych w Excelu.

W załączeniu dostępny jest plik z opisywanymi przykładami zastosowania 20 alternatyw dla WYSZUKAJ.PIONOWO. Ponadto można by jeszcze dodać kolejne rozwiązania z kombinacją nowej funkcji X.DOPASUJ XMATCH (365) zamiast DOPASUJ MATCH. Różnica między tymi funkcjami jest jednak stosunkowo nieznaczna. Najistotniejsza to ta, ze domyślnie ustawione jest dopasowanie dokładne.

[@Waluta] wyszukiwana wartość w kolumnie Waluta
KursyWalut tabela źródłowa
KursyWalut[Waluta] – kolumna z walutami
KursyWalut[Kurs] – kolumna z kursami

Tabela główna nazywa się Tabela_01. Natomiast tabeli źródłowej, zawierającej wybrane kursy walut w stosunku do GBP przypisałem nazwę KursyWalut Obydwie są w formacie Tabel. Jest to istotne podczas tworzenia formuł, gdyż odwołania funkcji będą odnosić się do nazw strukturalnych tych tabel.

KursyWalut zostały posortowane po wyszukiwanej nazwie ze względu na funkcję LOOKUP, która w przeciwnym wypadku nie działa poprawnie
• Zastosowałem w tym przykładzie dopasowanie dokładne.
• Gdyby zadanie polegało na dopasowaniu w przybliżeniu, przeszukiwane wartości musiałaby być liczbami a nie tekstem.
• Funkcje SUMOPRODUCT SUMIF(S), MAXIF(S), MINIF(S), AVERAGEIF(S) oraz FILTRUJ funkcjonują jedynie w dopasowaniu dokładnym.
• Tabela źródłowa zawiera tylko unikatowe rekordy. W przeciwnym wypadku rozwiązanie byłoby inne dla funkcji SUMIF(S), MAXIF(S), MINIF(S), AVERAGEIF(S)

#0. WYSZUKAJ.PIONOWO

=WYSZUKAJ.PIONOWO([@Waluta];KursyWalut;2;0)

#1. WYSZUKAJ LOOKUP Najstarsza funkcja z grupy funkcji wyszukiwania. Wymaga, aby tabela poboczna była posortowana rosnąco po wyszukiwanej wartości.

=WYSZUKAJ([@Waluta];KursyWalut)
=WYSZUKAJ([@Waluta];KursyWalut[Waluta];KursyWalut[Kurs])

#2. X.WYSZUKAJ XLOOKUP (365) – Najnowsza, najbardziej elastyczna, i wszechmogąca funkcja.

=X.WYSZUKAJ([@Waluta].KursyWalut[Waluta].KursyWalut[Kurs])

#3. INDEKS  + PODAJ.POZYCJĘ  INDEX + (X)MATCH

=INDEKS(KursyWalut[Kurs];PODAJ.POZYCJĘ([@Waluta];KursyWalut[Waluta];0))
=INDEKS(KursyWalut[Kurs];X.PODAJ.POZYCJĘ([@Waluta];KursyWalut[Waluta]))

#4. JEŻELI IF

=JEŻELI([@Waluta]=$V$5;$W$5;JEŻELI([@Waluta]=$V$4;$W$4;JEŻELI([@Waluta]=$V$3;$W$3;$W$6)))

#5. WARUNKI IFS (2016+)

=WARUNKI([@Waluta]=$V$6;$W$6;[@Waluta]=$V$5;$W$5;[@Waluta]=$V$4;$W$4;[@Waluta]=$V$3;$W$3)

#6. PRZEŁĄCZ SWITCH – (2016+)

=PRZEŁĄCZ(TRUE;[@Waluta]=$V$6;$W$6;[@Waluta]=$V$5;$W$5;[@Waluta]=$V$4;$W$4;[@Waluta]=$V$3;$W$3)

#7. WYBIERZ  + PODAJ.POZYCJĘ CHOSSE +(X)MATCH

=WYBIERZ(PODAJ.POZYCJĘ([@Waluta];$V$3:$V$6;0);$W$3;$W$4;$W$5;$W$6)

#8. ADR.POŚR + PODAJ.POZYCJĘ INDIRECT+MATCH

=@ADR.POŚR("$W$"&(PODAJ.POZYCJĘ([@Waluta];KursyWalut[Waluta];0)+2);TRUE)

#9. PRZESUNIĘCIE + PODAJ.POZYCJĘ OFFSET + MATCH

=OFFSET(KursyWalut[[#Headers];[Kurs]];PODAJ.POZYCJĘ ([@Waluta];KursyWalut[Waluta];0);0)

#10. POWT REPT

=POWT($W$3;[@Waluta]=$V$3)&REPT($W$4;[@Waluta]=$V$4)&REPT($W$5;[@Waluta]=$V$5)

#11. Algebra logiczna

=([@Waluta]=$V$4)*$W$4+([@Waluta]=$V$3)*$W$3+([@Waluta]=$V$5)*$W$5

#12. FILTRUJ (365) – Rozwiązanie występuje jedynie dla dokładnego dopasowania

=FILTRUJ(KursyWalut[Kurs];KursyWalut[Waluta]=[@Waluta])

#13. SUMA.ILOCZYNÓW SUMPRODUCT – Wynikiem jest rezultat działań arytmetycznego a wartościami wyszywanymi musi być tekst. Szukana wartość musi być liczbą, a przeszukiwany zakres musi zawierać wartości tekstowe. W przeciwnym wypadku występuje błąd, zastosowałem więc inną tabelę z kursami walut. Rozwiązanie występuje jedynie dla dokładnego dopasowania. W szczególnym wypadku w połączeniu z EXACT dopasowanie jest z uwzględnieniem wielkości liter

=SUMA.ILOCZYNÓW(--([@Waluta]=KursyWalut[Waluta]);(KursyWalut[Kurs]))

#14. SUMA.JEŻELI/SUMA.WARUNKÓW SUMIF(S), MAXIF(S), MINIF(S), AVERAGEIF(S)

=SUMA.JEŻELI(KursyWalut[Waluta];[@Waluta];KursyWalut[Kurs])

#15. BD.POLE (DGET) Jest to funkcja tzw. Database, która ze względu na swoja budowę tworzy jednostkowe rozwiązanie i nie można jej użyć w innych komórkach. Niemniej w takich okolicznościach może zastąpić VLOOKUP, choć jedynie dokładną wartość

=DGET(KursyWalut[#All];KursyWalut[[#Headers];[Kurs]];Criteria)

#16. Funkcja własna użytkownika MyLookup.
Charakteryzuje się tym, że jest stworzona w kodzie VBA, wymaga więc zapisania w pliku z rozszerzeniem .xlsm i włączenia makr.

=MyLookup([@CURRENCY];CurrencyTbl[CURRENCY];CurrencyTbl[VALUE])

Rozwiązanie inne niż za pomocą funkcji/ formuły to:
#17 Filtr zaawansowany

#18 POWER QUERY
#19 PIVOT TABLES
#20 POWER PIVOT

Bezpłatny plik z przykładami 20 alternatyw dla WYSZUKAJ.PIONOWO, dostępny jest do pobrania, klikając poniżej.

Pobierz 20 alternatyw dla WYSZUKAJ.PIONOWO

 

Jak złamać hasło w Excelu?

Jak złamać hasło w Excelu?

Jak złamać hasło w Excelu?
Arkusz kalkulacyjny Excel można zabezpieczać przed podglądem i edycją na wiele sposobów. Jednym z nich jest zabezpieczenie komórek wybranych zakresów arkusza przed edycją, zmianą formatowania itp. Bywają np. takie sytuacje, że zapomnieliśmy hasło do arkusza, który chcielibyśmy odblokować. Może się też zdarzyć, że wpisywane hasło, które pamiętamy, jest niepoprawne. W takim przypadku warto być może podjąć próbę wpisania poprawnego hasła.

Próba z włączonym i wyłączonym CapsLock-iem

Najczęstszym błędem w pisaniu niepoprawnego hasła jest niepotrzebnie włączony CapsLock. Zanim podejmiemy próbę złamania hasła w Excelu, sugeruję sprawdzenie wpisywanego hasła wielkimi jak i małymi literami.

  • Prawym klawiszem kliknij na nazwę arkusza
  • Wybierz Unprotect Sheet…

  • Wpisz hasło, które pamiętasz
  • Wciśnij CapsLock
  • Ponownie wpisz to samo hasło co poprzednio

Ponadto być może warto spróbować też sprawdzić często używane hasła, czyli np. 123456 lub 654321. Jeśli te próby nie pomogą, można podjąć się złamania hasła w danym arkuszu, używając jedną z trzech poniżej wymienionych przeze mnie metod poniżej.

Metoda 1.

Łamanie hasła za pomocą makra (kod VBA).

Dotyczy plików zapisanych z rozszerzeniem (.xls) czyli obowiazujacych w wersji 2003 i starszych.

  • Wywołaj okno dialogowe VBA Alt + F11
  • Prawym przyciskiem myszy klikij na odpowienim pliku
  • Wybierz Insert > Module

  • Pobierz/skopiuj kod z tego llinku
  • Wklej skopiowany kod do wyświetlonego okna.
  • Uruchom poprzez przycisk Run F5
  • Na zakończenie procesu powinien pojawić się okno z komunikatem jak poniżej, co oznacza, że hasło zostało zdjęte z danego arkusza.

Alternatywnie można stworzyć specjalny plik Excela na komputerze zawierający makro „Jak złamać hasło w Excelu?”, łatwo uruchamiane z paska szybkiego dostępu.

    • Pobierz/skopiuj kod z tego llinku
    • Wklej kod do nowo utworzonego pliku Excela
    • Zapisz ten plik jako Excel-In w domyślnej ścieżce dostępu i zamknij
    • Na pasku szybkiego dostępu, wybierz More Commands…
    • Wybierz Chooose command from: Macros
    • Wybierz odpowiednie makro o nazwie BreakPassword
    • Kliknij Add >>

    • Ewentualnie dobierz ikonkę poprzez przycisk Modify…
    • OK

W tym momencie makro łamiące hasło otwartego arkusza, dostępne jest na bieżąco i działa każdorazowo po klikniecie w odpowiednią ikonkę, którą własnie dodaliśmy .

Metoda 2.

Łamania hasła za pomocą usunięcia fragmentu kodu odpowiedzialnego za zabezpieczane arkusza

Dotyczy plików z rozszerzeniem .xlsx czyli obowiazujacych od wersji 2007 i nowszych.

Od wersji 2010 domyślny plik Excela zapisywany jest w formacie (.xlsx). Wynika to z faktu, że całkowity plik jest suma spakowanych plików w tym formacie.

  • w oknie dialogowym VBA Alt +F11, sprawdź nazwę kodową arkusza, który chcesz odbezpieczyć
  • Rozpakuj plik Excela (np. za pomoc bezpłatnego 7-Zip).  Klikając prawym przyciskiem myszy na pliku i wybierając z menu podręcznego 7-Zip — > Otwórz archiwum
  • W archiwum przejdź do katalogu xl –> worksheets i ustaw się na pliku xml o szukanej nazwie kodowej
  • Naciśnij F4, aby przejść do edycji w domyślnym edytorze plików tekstowych (najczęściej będzie to Notatnik)
  • Znajdź, zaznacz i usuń frazę odpowiedzialną za blokadę arkusza począwszy od <sheetProtection…/> a skończywszy przed <pageMargins…>

  • Zapisz zmiany w pliku, czyli spakuj na nowo.

Nowo powstały plik powinien być pozbawiony blokady w danym arkuszu.

Metoda 3.

Łamania hasła za pomocą użycia Google Sheet

Aby to zrobić, potrzebujesz konta na Dysku Google.

  • Otwórz swoje konto na Dysku Google, aby otworzyć nowy arkusz wybierz Nowy i wybierz Arkusz Google, .
  • W arkuszu wybierz Plik i wybierz Importuj.
  • W oknie dialogowym Importuj plik wybierz z menu Prześlij.
  • Kliknij Wybierz plik z urządzenia.
  • Przejdź do arkusza kalkulacyjnego programu Excel na komputerze. Wybierz go i kliknij Otwórz.
  • W oknie Importuj plik wybierz Zastąp arkusz kalkulacyjny i wybierz Importuj dane.

Spowoduje to zaimportowanie chronionego arkusza programu Excel do arkusza Arkuszy Google.

Alternatywnie – możesz także wstawić plik bezpośrednio na swój Dysk Google (np. przeciągając go z komputera) i otworzyć bezpośrednio na dysku. W Google Sheets blokada arkusza już nie działa. Możesz edytować wszystko, co chcesz.

Teraz, gdy nie jest on chroniony, możesz wyeksportować arkusz z powrotem do formatu Excel na komputer.

  • W niechronionym teraz Arkuszu Google wybierz Plik i Pobierz jako Wybierz Microsoft Excel (.xlsx)
  • Wybierz Zapisz jako. Nadaj plikowi nazwę i wybierz Zapisz.

Wyślij plik: Jak złamać hasło w Excelu? do sprawdzenia

Niemniej jednak, każda z tych metod może okazać się niewystarczająca w szczególnych przypadkach. Choćby dlatego, że zastosowano inne rodzaje zabezpieczeń i wymaga to dodatkowych złożonych działań. W tym wypadku możemy spróbować się podjąć zlecenia odblokowania Twojego arkusza kalkulacyjnego Excel. Wystarczy kliknąć link poniżej i wysłać do nas plik do sprawdzenia.

Request a quote

Zapraszam również do zapoznania się z moimi projektami w Excelu dostępnymi w zakładce Pobierz, być może okażą się przydatne.

English Version

Dynamiczne Formuły Tablicowe w Excelu

Dynamiczne Formuły Tablicowe w Excelu są dostępne obecnie (2020) dla użytkowników pakietu Microsoft 365. Wersje 2019 i starsze nie mają dostępu do tej funkcjonalności.

Dynamiczne Formuły Tablicowe

Dynamiczna formuła tablicowa może zwracać tablice o zmiennej wielkości. W odróżnieniu do klasycznej formuły tablicowej (nie „dynamicznej”), po wprowadzeniu do pojedynczej komórki zatwierdzana jest tylko klawiszem Enter.

Rozlanie – to sytuacja, w której zwracające wiele wyników dynamiczne formuły tablicowe automatycznie „rozlewają” je na wiele komórek. Rozlanie formuły występuje w komórkach sąsiadujących poniżej lub z prawej strony.

  • W momencie, gdy zaznaczysz dowolną komórkę w obszarze rozwiązania, program automatycznie wyróżni obramowaniem (kolor niebieski z cieniem) cały zakres. Obramowanie zniknie po zaznaczeniu komórki poza obszarem rozlania.

  • Formuła wprowadzona w oryginalnej komórce wyświetlana jest na czarno i podlega zmianom.

  • Formuła rozlana w pozostałych komórkach zakresu wyświetla się na szaro i nie można jej modyfikować.

  • Dynamicznych formuł tablicowych, podobnie jak starszych formuł tablicowych, nie można wprowadzać do Tabel w Excelu.
    Odwołania do innych skoroszytów są dopuszczalne, ale tylko wtedy, gdy są otwarte, W przypadku argumentów zawierających łącza do plików zamkniętych wynikiem działania formuły będzie błąd #ADR!

Nowe błędy:

  • #SPILL!– Błąd wynikający z faktu niemożliwości rozlania się dynamicznej tablicy, gdyż w napotkanym zakresie znajduje się niepusta komórka.
  • #CALC! – Błąd występuje, gdy formuła napotyka błąd obliczeniowy z tablicą.

Nowe funkcje:

  • FILTR (FILTRUJ) – filtruje zakres wg kryteriów
  • UNIQUE (UNIKATOWE) – zwraca unikatowe pozycje
  • SORT (SORTUJ) – sortuje wg kryteriów
  • SORTBY (SORTUJ.WEDŁUG) – umożliwia sortowanie wielopoziomowe
  • SEQUENCE (SEKWENCJA) – generuje sekwencje wartości wg kryteriów
  • RANDARRAY (LOSOWA.TABLICA) – zwraca tablicę liczb losowych

Przeprowadzam kompleksowe szkolenie w Londynie w Excelu jak również specjalne warsztaty z nowości w Excelu, na które serdecznie zapraszam.

 

Ekstraklasa w Excelu

Ekstraklasa w Excelu

Ekstraklasa w Excelu to plik do pobrania za darmo, dostępny na dole postu.

W arkuszu „terminarz” uzupełniamy wyniki meczów Ekstraklasy. Ponadto, możemy wybrać, drużynę , którą z różnych powodów chcielibyśmy śledzić. Klikając w komórkę S2, aktywujemy listę rozwijalną z nazwami zespołów Ekstraklasy sezonu 2020-21. Wskazana drużyna, dzięki walidacji danych, podświetli nam się w kalendarzu rozgrywek oraz w tabelach wszędzie tam, gdzie występuje.

Natomiast tabele w arkuszu „tabela”, uaktualniają się automatycznie, każdorazowo po wpisaniu wyniku meczu . Arkusz ten zawiera tabele główną, ponadto tabelę uwzględniające mecze rozegrane na własnym stadionie oraz na wyjeździe.

Pobieranie pliku Ekstraklasa w Excelu

Pobierz bezpłatną wersję, klikając poniżej:

Pobierz Ekstraklasa w Excelu

W zakładce Pobierz, dostępne są również pliki Excel-a z poprzednich sezonów Ekstraklasy, Premiere League oraz Mistrzostw Świata czy też Europy w piłce nożnej.

Pułap tlenowy oraz test Coopera w Excelu

Test Coopera w Excelu

W roku 1968, amerykański lekarz Kenneth H. Cooper na potrzeby armii USA, opracował test wytrzymałościowy, polegający na 12-minutowym nieprzerwanym biegu. Pomiar dystansu w tym czasie, przy uwzględnieniu płci oraz wieku, pozwala ocenić stan kondycji oraz wydolności fizycznej organizmu.

Test Coopera w Excelu

Aby zobrazować rezultat po teście Coopera, postanowiłem stworzyć w arkuszu kalkulacyjnym Excel, specjalny kalkulator. Obsługa arkusza jest bardzo prosta. Trzy białe pola należy wypełnić poprawnymi danymi.

  1. W polu Płeć do wyboru mamy z listy rozwijalnej:
    • Kobieta
    • Mezczyzna
  2. W polu Wiek wstawiamy wiek. (15-99)
  3. W polu Dystans wpisujemy dystans w metrach jaki pokonaliśmy w ciągu 12 minut . (Dla przykładu, jeśli przebiegniemy dwa i pół kilometra, należy wstawić 2500)

Jeśli dystans, który przebiegniemy, podany mamy w milach, należy przekonwertować na metry. Do tego służy wzór:

„Dystans w metrach” = „Dystans w milach” × 1609.344

Po wstawieniu danych w arkuszu automatycznie wyświetli nam się wskaźnik, który określi nam na ile udany był nasz występ.

Ponadto arkusz kalkulacyjny zlicza:

  • w jakim średnim tempie przebiegliśmy,
  • z jaką prędkością,
  • oraz obliczy nam pułap tlenowy, czyli maksymalną zdolność przysporzenia tlenu przez organizm.

Pułap tlenowy oznaczamy jako VO2 Max (Maximum Volume of oxygen), można obliczyć za pomocą wzoru:

VO2 Max = („Dystans w metrach” – 504.9) ÷ 44.73

Jednostką miary jest [ml/kg/min] czyli ilość w mililitrach tlenu jaką w stanie jest przysporzyć organizm, podczas intensywnego wysiłku, na kilogram masy ciała, na minutę. To jest tzw. „domowa” metoda pomiarowa, więc wynik jej należy traktować orientacyjnie. Niemniej tylko badania laboratoryjne wskażą nam dokładny pomiar pułap tlenowego.

Pobieranie pliku Test Coopera w Excelu

Bezpłatny plik Excel-a dostępny jest do pobrania, klikając poniżej.

Pobierz Test Coopera w Excelu