Excel – Jak wstawić entery (nowe wiersze) zamiast spacji, przecinków lub innych znaków

Piotr Majcher Excel

W tym odcinku zobaczyć jak wstawić entery (nowe wiersze) w komórkach zamiast dowolnego znaku, na przykład spacji, przecinka czy myślnika.

Efekt taki możesz osiągnąć zarówno przy pomocy mechanizmu Znajdź/Zamień jak i przy pomocy formuły. Pokazuję obydwa rozwiązania.

Użyta/e funkcja/e: PODSTAW, ZNAK, KOD (SUBSTITUE, CHAR, CODE)

Użyty mechanizm: Znajdź/Zamień

 

Pobierz plik do filmu: http://www.pmsocho.com/pobierz-pliki-do-filmow

Nazwa pliku: excel-893 – Jak wstawić entery (nowe wiersze) zamiast spacji, przecinków lub innych znaków.xlsx

 

Excel – Jak usunąć (zamienić) fragmenty z komórek na podstawie wartości z innych komórek

Piotr Majcher Excel

W tym odcinku pokazuję jak usunąć z komórek fragmenty (teksty, liczby) lub zamienić je na podstawie wartości znajdujących się w innych komórkach.

W takim przypadku nie sprawdzi się mechanizm Znajdź/Zamień ponieważ znajduje on tylko jedną wartość i zamienia ją na tylko jedną wartość.

Wykorzystuję więc funkcję PODSTAW, której jako argumenty można podać różne wartości z różnych komórek.

Użyta/e funkcja/e: PODSTAW

 

Pobierz plik do filmu: http://www.pmsocho.com/pobierz-pliki-do-filmow

Nazwa pliku: excel-892 – Jak usunąć (zamienić) fragmenty z komórek na podstawie wartości z innych komórek.xlsx

 

Najważniejsze funkcje Excela (edycja 2020) – wyniki ankiety

Piotr Majcher Excel, Przemyślenia

Dlaczego ten wpis jest ważny?

Dzięki niemu będziesz wiedział, które funkcje Excela najczęściej i najbardziej pomagają, a co za tym idzie warto poznać je w pierwszej kolejności!

Wstęp

Poniższe analizy oraz wnioski powstały na podstawie wyników ankiety przeprowadzonej w listopadzie 2020 roku. Udział w ankiecie był dobrowolny. Uczestnik ankiety mógł podać dowolną liczbę funkcji, które uznawał za najważniejsze w swojej pracy.

W ankiecie miała znaczenie kolejność podawania odpowiedzi. Funkcje wskazane w pierwszej kolejności były traktowane jako ważniejsze/częściej używane.
Każdej odpowiedzi przypisałem wagę. Funkcja podana jako pierwsza zdobywała 4pkt. Druga wskazana funkcja 3,6 itd. zgodnie z poniższą tabelką:

Pozycja odpowiedzi Punkty (waga)
1 4
2 3,6
3 3,2
4 2,8
5 2,4
6 2
7 1,6
8 1,2
9 0,8
10 0,4
11..100 0,3

Analizując dane brałem pod uwagę głównie punkty zdobyte przez daną funkcją a nie liczbę odpowiedzi oddanych na daną funkcję.

Ważność a popularność funkcji Excela

Można przyjąć, że Ważność funkcji = Popularność funkcji.

Najważniejsze funkcje Excela są jednocześnie najczęściej używane i na odwrót.

Taki wniosek nasuwa się na skutek analizy odpowiedzi. Ważność oddaje liczba punktów zdobytych przez daną funkcję w ankiecie. Jako popularność można przyjąć liczbę głosów oddanych na daną funkcję.

Ważność i popularność (liczba punktów i liczba głosów oddanych na daną funkcję) są bardzo mocno skorelowane: Współczynnik korelacji = 0,9683

Wyniki analizy

W ankiecie wzięło udział 488 osób.

Osoba wypełniająca ankietę podawała średnio 8 różnych funkcji.

Zdecydowana większość osób biorących udział w ankiecie dysponowała Excelem w wersji 2016 lub nowszej:

Wersja Excela Liczba ankietowanych Udział %
Excel dla Mac 2 0,41%
Excel dla Office 2007 10 2,05%
Excel dla Office 2010 27 5,53%
Excel dla Office 2013 38 7,79%
Excel dla Office 2016 120 24,59%
Excel dla Office 2019 71 14,55%
Excel dla Office 365 (Microsoft 365) 218 44,67%
Nie wiem jaką mam wersję 2 0,41%
Razem 488 100,00%


Prawie 90% osób biorących udział w ankiecie zadeklarowało co najmniej średni poziom zaawansowania:

Poziom wiedzy Liczba ankietowanych Udział %
Podstawowy 41 8,40%
Średnio zaawansowany 207 42,42%
Zaawansowany 177 36,27%
Ekspert 63 12,91%
Razem 488 100,00%

W ankiecie wskazano 147 unikatowych (różnych) funkcji – pierwsza grafika w tym poście.

Ranking TOP 50 najważniejszych funkcji Excela

Im większa czcionka tym większa ważność/popularność danej funkcji.

2020 - najważniejsze funkcje Excela - edycja 2020

Szczegółowe zestawienie dotyczące powyższej grafiki przedstawione jest poniżej.

Legenda:

Pozycja 2020 – miejsce uzyskane w rankingu na podstawie ankiety w 2020 roku

Punkty 2020 – liczba uzyskanych punktów przez daną funkcję

Liczba odpowiedzi – liczba głosów oddanych na funkcję

Pozycja 2017 – miejsce uzyskane w rankingu na podstawie ankiety przeprowadzonej w 2017 roku

Zmiana 2017/2020 – zmiana w rankingu w stosunku do 2017 roku

Pozycja 2020 Nazwa PL Nazwa EN Punkty 2020 Liczba odpowiedzi Pozycja 2017 Zmiana 2017/2020
1 WYSZUKAJ.PIONOWO VLOOKUP 1289 376 1
2 JEŻELI IF 1225 367 2
3 SUMA SUM 693 245 3
4 LICZ.JEŻELI COUNTIF 633 246 4
5 SUMA.JEŻELI SUMIF 457 188 5
6 SUMA.WARUNKÓW SUMIFS 415 165 7 +1
7 INDEKS INDEX 328 145 6 -1
8 PODAJ.POZYCJĘ MATCH 317 150 8
9 JEŻELI.BŁĄD IFERROR 303 157 9
10 FRAGMENT.TEKSTU MID 244 137 10
11 LEWY LEFT 203 132 11
12 ORAZ AND 181 102 16 +4
13 SUMA.ILOCZYNÓW SUMPRODUCT 141 106 15 +2
14 PRAWY RIGHT 140 70 12 -2
15 LICZ.WARUNKI COUNTIFS 131 72 14 -1
16 LUB OR 130 65 17 +1
17 ŚREDNIA AVERAGE 129 77 13 -4
18 SUMY.CZĘŚCIOWE SUBTOTAL 114 63 19 +1
19 ZŁĄCZ.TEKSTY CONCATENATE 102 59 22 +3
20 X.WYSZUKAJ XLOOKUP 89 31 b.d. x
21 ZAOKR ROUND 82 56 23 +2
22 MAX MAX 81 62 20 -2
23 WYSZUKAJ.POZIOMO HLOOKUP 78 35 44 +21
24 ILE.NIEPUSTYCH COUNTA 72 55 34 +10
25 DZIŚ TODAY 71 60 21 -4
26 ZNAJDŹ FIND 64 52 32 +6
27 FILTRUJ FILTER 63 27 b.d. x
28 DATA DATE 60 45 18 -10
29 MIN MIN 57 48 24 -5
30 PRZESUNIĘCIE OFFSET 53 44 29 -1
31 LEN 52 45 25 -6
32 ZŁĄCZ.TEKST CONCAT 39 20 37 +5
33 UNIKATOWE UNIQUE 38 23 b.d. x
34 CZY.BŁĄD ISERROR 36 21 26 -8
35 TEKST TEXT 33 34 36 +1
36 WYSZUKAJ LOOKUP 30 13 50 +14
37 WARUNKI IFS 27 13 b.d. x
38 ADR.POŚR INDIRECT 26 22 74 +36
39 JEŻELI.ND IFNA 24 23 71 +32
40 PODSTAW SUBSTITUTE 24 23 27 -13
41 USUŃ.ZBĘDNE.ODSTĘPY TRIM 22 20 30 -11
42 ROK YEAR 22 31 41 -1
43 MIESIĄC MONTH 21 34 31 -12
44 CZY.LICZBA ISNUMBER 21 24 51 +7
45 ILE.LICZB COUNT 20 15 b.d. x
46 CZY.PUSTA ISBLANK 18 20 40 -6
47 PORÓWNAJ EXACT 18 12 78 +31
48 SORTUJ SORT 18 11 b.d. x
49 POŁĄCZ.TEKSTY TEXTJOIN 17 9 b.d. x
50 WEŹDANETABELI GETPIVOTDATA 17 15 56 +6

b.d. – brak danych, nie pojawiła się w ankiecie z 2017 roku

x – nie ma odniesienia do ankiety z 2017 roku

Jak często głosowano na daną funkcję

Poniższe zestawienie pokazuje jak często ankietowani wskazywali daną funkcję.  Dla przykładu 77,05% wszystkich ankietowanych wymieniło WYSZUKAJ.PIONOWO jako jedną z najważniejszych funkcji Excela.

Legenda:

Pozycja 2020 – miejsce uzyskane w rankingu na podstawie ankiety w 2020 roku

Liczba odpowiedzi – liczba głosów oddanych na funkcję

% Liczba odpowiedzi – udział głosów oddanych na funkcję w stosunku do wszystkich głosów oddanych na wszystkie funkcje

% Liczba ankietowanych – udział osób wskazujących daną funkcję w ankiecie w stosunku to wszystkich osób wypełniających ankietę

Pozycja 2020 Nazwa PL Nazwa EN Liczba odpowiedzi % Liczba odpowiedzi % Liczba ankietowanych
1 WYSZUKAJ.PIONOWO VLOOKUP 376 8,83% 77,05%
2 JEŻELI IF 367 8,62% 75,20%
3 SUMA SUM 245 5,75% 50,20%
4 LICZ.JEŻELI COUNTIF 246 5,78% 50,41%
5 SUMA.JEŻELI SUMIF 188 4,42% 38,52%
6 SUMA.WARUNKÓW SUMIFS 165 3,88% 33,81%
7 INDEKS INDEX 145 3,41% 29,71%
8 PODAJ.POZYCJĘ MATCH 150 3,52% 30,74%
9 JEŻELI.BŁĄD IFERROR 157 3,69% 32,17%
10 FRAGMENT.TEKSTU MID 137 3,22% 28,07%
11 LEWY LEFT 132 3,10% 27,05%
12 ORAZ AND 102 2,40% 20,90%
13 SUMA.ILOCZYNÓW SUMPRODUCT 106 2,49% 21,72%
14 PRAWY RIGHT 70 1,64% 14,34%
15 LICZ.WARUNKI COUNTIFS 72 1,69% 14,75%
16 LUB OR 65 1,53% 13,32%
17 ŚREDNIA AVERAGE 77 1,81% 15,78%
18 SUMY.CZĘŚCIOWE SUBTOTAL 63 1,48% 12,91%
19 ZŁĄCZ.TEKSTY CONCATENATE 59 1,39% 12,09%
20 X.WYSZUKAJ XLOOKUP 31 0,73% 6,35%

Ranking tylko pierwszej odpowiedzi

Ciekawie wygląda analiza wyników ankiety, gdy pod uwagę zostanie wzięta tylko jedna funkcja – podawana jako pierwsza odpowiedź w  ankiecie. Funkcja podawana w ankiecie jako pierwsza była traktowana przeze mnie jako najważniejsza z najważniejszych 🙂

Na pierwszym miejscu (jako pierwsza odpowiedź w ankiecie) pojawiły się tylko 23 funkcje! Jeśli ranking byłby tworzony tylko na podstawie pierwszej odpowiedzi to wyglądałby on tak jak w poniższej tabeli.

Dla przykładu funkcja WSZUKAJ.PIONOWO została wskazana na pierwszym miejscu przez 36,27% wszystkich ankietowanych.

Legenda:

Pozycja 2020 – miejsce uzyskane w rankingu na podstawie ankiety w 2020 roku

Liczba odpowiedzi – liczba głosów oddanych na funkcję

% Liczba ankietowanych – udział osób wskazujących daną funkcję w ankiecie w stosunku to wszystkich osób wypełniających ankietę

Pozycja 2020 Nazwa PL Nazwa EN Ile razy pierwsze miejsce % Liczba ankietowanych
1 WYSZUKAJ.PIONOWO VLOOKUP 177 36,27%
2 JEŻELI IF 120 24,59%
3 SUMA SUM 75 15,37%
4 LICZ.JEŻELI COUNTIF 21 4,30%
5 SUMA.WARUNKÓW SUMIFS 15 3,07%
6 SUMA.JEŻELI SUMIF 12 2,46%
7 X.WYSZUKAJ XLOOKUP 12 2,46%
8 INDEKS INDEX 7 1,43%
9 PODAJ.POZYCJĘ MATCH 6 1,23%
10 SUMA.ILOCZYNÓW SUMPRODUCT 6 1,23%
11 SUMY.CZĘŚCIOWE SUBTOTAL 2 0,41%
12 WYSZUKAJ LOOKUP 2 0,41%
13 ZAOKR ROUND 2 0,41%
14 X.DOPASUJ XMATCH 1 0,20%
15 PORÓWNAJ EXACT 1 0,20%
16 AGREGUJ AGGREGATE 1 0,20%
17 ŚREDNIA AVERAGE 1 0,20%
18 WYSZUKAJ.POZIOMO HLOOKUP 1 0,20%
19 FILTRUJ FILTER 1 0,20%
20 JEŻELI.BŁĄD IFERROR 1 0,20%
21 WEŹDANETABELI GETPIVOTDATA 1 0,20%
22 WARUNKI IFS 1 0,20%
23 POŁĄCZ.TEKSTY TEXTJOIN 1 0,20%

Wnioski

W Excelu jest ponad 450 różnych funkcji. W wynikach ankiety pojawiła się tylko 1/3 z nich.

Biorąc pod uwagę liczbę oddanych głosów to 20% (30 funkcji)  spośród 147 funkcji, które pojawiły się w ankiecie, zebrało 80% wszystkich głosów!

Powyższe 20%  stanowi niecałe 6,6% wszystkich funkcji dostępnych w Excelu 🙂

Plik do pobrania

Wszystkie powyższe tabelki w formie pliku Excela o numerze 891 są do pobrania stąd >>

TOP 30 w czytelnej formie

Na zakończenie TOP 30 najważniejszych funkcji Excela, podzielonych na 3 „poziomy ważności” dla łatwiejszego odczytu 🙂

Excel – Najważniejsze funkcje Excela (edycja 2020) – wyniki ankiety

Piotr Majcher Excel

Szczegółowe wyniki ankiety: https://www.pmsocho.com/2020/11/24/najwazniejsze-funkcje-excela-edycja-2020-wyniki-ankiety/

W tym odcinku prezentuję w skróconej formie wyniki analizy ankiety dotyczącej najpopularniejszych funkcji w Excelu.

Czy wiesz, które funkcje Excela uznawane są za najważniejsze? Które funkcje musisz poznać w pierwszej kolejności aby wymiatać? – Obejrzyj ten odcinek i przekonaj się.

 

Pobierz plik do filmu: http://www.pmsocho.com/pobierz-pliki-do-filmow

Nazwa pliku: excel-891 – Najważniejsze funkcje Excela (edycja 2020) – wyniki ankiety.xlsx

 

Excel – Pomóż stworzyć ranking najważniejszych funkcji Excela edycja 2020

Piotr Majcher Excel

Link do ankiety:https://www.pmsocho.com/2020/11/04/najwazniejsze-funkcje-excela-edycja-2020-ankieta/

W Excelu jest ponad 450 funkcji ale wystarczy znać biegle 20-30 z nich, aby rozwiązać 90% problemów wymagających wykorzystania formuł.
Chcesz wiedzieć, które funkcje są najważniejsze?

Pomóż stworzyć aktualny ranking najważniejszych funkcji Excela! To tylko 3 minuty!
Wpisuj tylko nazwy funkcji (tabela przestawna, filtr, sortowanie czy Znajdź/Zamień to nie funkcje!).
Wpisuj nazwy funkcji poprawnie i tylko po polsku na przykład: LICZ.JEŻELI zamiast LICZ JEZELI
Wpisz tyle funkcji ile chcesz.
W każdym pytaniu wpisz tylko jedną funkcję.
W pierwszej kolejności wpisuj funkcje których używasz częściej.

 

Pobierz plik do filmu: http://www.pmsocho.com/pobierz-pliki-do-filmow

Nazwa pliku: excel-890 – Pomóż stworzyć ranking najważniejszych funkcji Excela edycja 2020.xlsx

 

Najważniejsze funkcje Excela – edycja 2020 – ankieta

Piotr Majcher Excel, Przemyślenia

W Excelu jest ponad 450 funkcji ale wystarczy znać biegle 20-30  z nich, aby rozwiązać 90% problemów wymagających wykorzystania formuł.

Chcesz wiedzieć, które funkcje są najważniejsze?

Pomóż stworzyć aktualny ranking najważniejszych funkcji Excela! To tylko 3 minuty!

  • Wpisuj tylko nazwy funkcji (tabela przestawna, filtr, sortowanie czy Znajdź/Zamień to nie funkcje!).
  • Wpisuj nazwy funkcji poprawnie i tylko po polsku na przykład: LICZ.JEŻELI zamiast LICZ JEZELI
  • Wpisz tyle funkcji ile chcesz.
  • W każdym pytaniu wpisz tylko jedną funkcję.
  • W pierwszej kolejności wpisuj funkcje których używasz częściej.

Excel – Importowanie danych z Arkuszy Google (Google Sheets) lub pliku Excela na Dysku Google

Piotr Majcher Excel

Kurs Power Query: http://www.pmsocho.com/automatyczne-laczenie-pobieranie-przeksztalcanie-danych-excelu-power-query/

W tym odcinku pokażę Ci jak w Excelu zaimportować dane z Arkusza Google lub z innego pliku Excela umieszczonego na dysku Google.

Import danych będzie oczywiście odświeżalny. Po zmianie danych na Dysku Google w Excelu wystarczy jedynie odświeżyć import.

Wykorzystam mechanizm Pobieranie i przekształcanie czyli Power Query.

Aby import z Dysk Google był możliwy trzeba włączyć udostępnianie pliku na Dysku Google a następnie pobrać link do udostępnionego pliku. Link ten trzeba delikatnie zmienić aby mógł on zostać wykorzystany w Excelu przy imporcie. Mając zmodyfikowany link dalej cała procedura idzie już łatwo i gładko 🙂

Użyte mechanizmy: Power Query, Import danych

 

Pobierz plik do filmu: http://www.pmsocho.com/pobierz-pliki-do-filmow

Nazwa pliku: excel-889 – Importowanie danych z Arkuszy Google (Google Sheets) lub pliku Excela na Dysku Google.xlsx

 

Excel – Importowanie danych z Arkuszy Google (Google Sheets) lub pliku Excela na Dysku Google

Piotr Majcher Excel

W tym odcinku pokażę Ci jak w Excelu zaimportować dane z Arkusza Google lub z innego pliku Excela umieszczonego na dysku Google.

Import danych będzie oczywiście odświeżalny. Po zmianie danych na Dysku Google w Excelu wystarczy jedynie odświeżyć import.

Wykorzystam mechanizm Pobieranie i przekształcanie czyli Power Query.

Aby import z Dysk Google był możliwy trzeba włączyć udostępnianie pliku na dysku googe a następnie pobrać link do udostępnionego pliku. Link ten trzeba delikatnie zmienić aby mógł on zostać wykorzystany w Excelu przy imporcie. Mając zmodyfikowany link dalej cała procedura idzie już łatwo i gładko 🙂

Użyte mechanizmy: Power Query, Import danych

 

Pobierz plik do filmu: http://www.pmsocho.com/pobierz-pliki-do-filmow

Nazwa pliku: excel-889 – Importowanie danych z Arkuszy Google (Google Sheets) lub pliku Excela na Dysku Google.xlsx

 

Najważniejsze funkcje Excela – edycja 2020 – ankieta

Piotr Majcher Excel, Przemyślenia

W Excelu jest ponad 450 funkcji ale wystarczy znać biegle 20-30  z nich, aby rozwiązać 90% problemów wymagających wykorzystania formuł.

Chcesz wiedzieć, które funkcje są najważniejsze?

W 2017 roku przeprowadziłem ankietę w której zapytałem jakie funkcje Excela są najważniejsze i najczęściej używane. W tym roku postanowiłem ponownie sprawdzić, które funkcje Excela są uważane za najbardziej użyteczne.

Wyniki obu edycji ankiety opublikuję w następnym poście.

Pomóż stworzyć aktualny ranking najważniejszych funkcji Excela! To tylko 3 minuty!

  • Wpisuj tylko nazwy funkcji (tabela przestawna, filtr, sortowanie czy Znajdź/Zamień to nie funkcje!).
  • Wpisuj nazwy funkcji poprawnie i tylko po polsku (np. SUMA, WYSZUKAJ.PIONOWO, USUŃ.ZBĘDNE.ODSTĘPY).
  • Wpisz tyle funkcji ile chcesz. Dwie, pięć, osiem – Twoja decyzja.
  • W każdym pytaniu wpisz tylko jedną funkcję.
  • Jeśli będziesz chciał podać więcej niż 10 funkcji wypełnij ankietę dwa razy.
  • W pierwszej kolejności wpisuj funkcje których używasz częściej.

Excel – Funkcja JEŻELI – mało znana właściwość

Piotr Majcher Excel

W tym odcinku pokażę Ci bardzo mało znaną właściwość funkcji JEŻELI.

Pierwszy argument funkcji JEŻELI nazywa się test_logiczny. Jest to warunek, który funkcja JEŻELI sprawdza i wykonuje jedną z dwóch czynności (operacji) w zależności od tego czy warunek jest spełniony czy nie.

Test_logiczny najczęściej zbudowany jest tak, aby generować wartość PRAWDA lub FAŁSZ. Mało osób wie, że testem logicznym może być także liczba. Jeśli liczba jest inna niż 0 wówczas test logiczny traktowany jest jako PRAWDA. Jeśli liczba jest równa 0 wówczas test logiczny traktowany jest jako FAŁSZ.

Użyta/e funkcja/e: JEŻELI

 

Pobierz plik do filmu: http://www.pmsocho.com/pobierz-pliki-do-filmow

Nazwa pliku: excel-888 – Funkcja JEŻELI – mało znana właściwość.xlsx