exfunk 6 excel funkcje w przykladach ebook promocyjny helion pl 7UKDQ5BM2JW4OYZT7Q6SZGRD7MDNC4DOKXGR4AI

Arkusz kalkulacyjny Excel to jedno z najczêœciej wykorzystywanych narzêdzi
informatycznych. Z jego mo¿liwoœci korzystaj¹ nie tylko ksiêgowi, finansiœci
i handlowcy, ale tak¿e naukowcy, którzy stosuj¹ go do przeprowadzania z³o¿onych
obliczeñ i symulacji. Swoj¹ potêgê Excel pokazuje dopiero wtedy, gdy u¿ytkownik
siêgnie po funkcje — gotowe mechanizmy obliczeniowe pozwalaj¹ce na wykonanie
niemal dowolnych dzia³añ i operacji obliczeniowych. £¹cz¹c pojedyncze funkcje
w formu³y, mo¿na szybciej zrealizowaæ praktycznie ka¿de zadanie.

„Excel. Funkcje w przyk³adach” to ksi¹¿ka przedstawiaj¹ca zasady stosowania funkcji
w obliczeniach wykonywanych za pomoc¹ arkusza kalkulacyjnego Excel. Czytaj¹c j¹,
poznasz podstawy tworzenia w³asnych formu³ oraz nauczysz siê regu³ adresowania
komórek i odwo³ywania siê do nich w formu³ach. Dowiesz siê równie¿, jak przypisywaæ
nazwy komórkom i zakresom oraz jak korzystaæ z nich podczas obliczeñ. Znajdziesz tu
tak¿e szczegó³owe omówienie poszczególnych funkcji Excela.

• Kolejnoœæ operacji w formu³ach
• Wpisywanie i edycja formu³ w komórkach
• Tworzenie i u¿ywanie nazw
• Funkcje logiczne
• Funkcje daty i czasu
• Funkcje informacyjne
• Funkcje wyszukiwania
• Funkcje matematyczne
• Funkcje bazy danych
• Funkcje statystyczne
• Funkcje finansowe

Kalkulatory ju¿ dawno sta³y siê prze¿ytkiem.

Dziœ najdoskonalszym narzêdziem obliczeniowym jest Excel

Spis treści

Spis treści

Wstęp ............................................................................................ 13

Część I

Budowanie warsztatu ................................................... 15

Rozdział 1. Podstawy ....................................................................................... 17

Domyślna interpretacja wpisu do komórki ...................................................................... 17
Operatory i kolejność operacji w formułach ................................................................... 21

Operatory odwołania ................................................................................................. 21
Operatory arytmetyczne ............................................................................................ 22
Operator konkatenacji ............................................................................................... 23
Operatory porównania ............................................................................................... 23
Porównanie priorytetów operatorów używanych w formułach ................................ 25
Sprawdzanie kolejności wykonywania operacji w formułach .................................. 25

Dlaczego i do czego używamy funkcji? — przykłady .................................................... 27

Obliczenia w komórkach arkusza ............................................................................. 27
Formatowanie warunkowe ........................................................................................ 29
Sprawdzanie poprawności ......................................................................................... 31

Wprowadzanie formuł do komórek arkusza .................................................................... 32

Wpisywanie formuły z klawiatury ............................................................................ 33
Wprowadzanie formuły przez wskazywanie adresów .............................................. 33
Wprowadzanie funkcji do formuły ........................................................................... 34

Edytowanie formuł .......................................................................................................... 38
Wyświetlanie i znajdowanie formuł ................................................................................ 39

Znajdowanie formuł po ich wyświetleniu ................................................................. 39
Znajdowanie formuł za pomocą okna dialogowego Przejdź do ............................... 39

Obliczenia automatyczne i ręczne ................................................................................... 41
Odwołania do komórek w formułach .............................................................................. 42

Odwołania względne ................................................................................................. 42
Odwołania bezwzględne ........................................................................................... 43
Adresy mieszane ....................................................................................................... 44
Klawisz F4 ................................................................................................................. 45
Adresowanie w trzecim wymiarze ............................................................................ 45

Błędy sygnalizowane przez formuły i funkcje ................................................................ 47

Rozdział 2. Nazwy ............................................................................................ 49

Zasady tworzenia nazw ................................................................................................... 50
Nazwy z odwołaniem bezwzględnym ............................................................................. 50

Standardowe tworzenie nazw .................................................................................... 50
Przypisywanie nazw do istniejących odwołań .......................................................... 51

Excel. Funkcje w przykładach

Tworzenie nazw za pomocą paska formuły .............................................................. 52
Tworzenie nazw za pomocą tekstu wpisanego do sąsiednich komórek arkusza ...... 52

Nazwy z odwołaniem względnym ................................................................................... 53
Nazwy formuł .................................................................................................................. 54
Nazwy stałych .................................................................................................................. 55
Modyfikowanie i usuwanie nazw .................................................................................... 56

Modyfikowanie nazwy .............................................................................................. 56
Usuwanie nazw ......................................................................................................... 56

Nazwy na poziomie skoroszytu i nazwy na poziomie arkusza ....................................... 57

Nadawanie nazw na poziomie arkusza ...................................................................... 58
Analiza zależności między nazwami na poziomie arkusza

i na poziomie skoroszytu ........................................................................................ 59

Praktyczny sposób budowania skoroszytów z nazwami na poziomie arkuszy ......... 60

Znajdowanie nazw ........................................................................................................... 60

Wyszukiwanie na liście nazw definiowanych ........................................................... 61
Wyszukiwanie nazw za pomocą paska formuły ....................................................... 61
Znajdowanie nazw przez zmniejszanie skali wyświetlania ...................................... 62
Wklejanie listy nazw ................................................................................................. 62
Znajdowanie nazwanych zakresów za pomocą okna Przechodzenie do ................... 63

Menadżer nazw ................................................................................................................ 64
Rzadko używany operator części wspólnej zakresów ..................................................... 65
Wskazówki kończące część I .......................................................................................... 66

Część II Funkcje ........................................................................ 67

Rozdział 3. Funkcje logiczne ............................................................................ 71

PRAWDA — TRUE ....................................................................................................... 71
FAŁSZ — FALSE ........................................................................................................... 72
NIE — NOT .................................................................................................................... 72
JEŻELI — IF ................................................................................................................... 75
ORAZ — AND ................................................................................................................ 76
LUB — OR ...................................................................................................................... 78

Rozdział 4. Funkcje daty i czasu ....................................................................... 83

CZAS — TIME ............................................................................................................... 85
CZAS.WARTOŚĆ — TIMEVALUE ............................................................................. 87
DATA — DATE ............................................................................................................. 88
DATA.RÓŻNICA — DATEDIF .................................................................................... 89
DATA.WARTOŚĆ — DATEVALUE ........................................................................... 91
DNI.360 — DAYS360 .................................................................................................... 92
DZIEŃ — DAY ............................................................................................................... 94
DZIEŃ.TYG — WEEKDAY .......................................................................................... 95
DZIŚ — TODAY ............................................................................................................ 97
EDATE ............................................................................................................................ 98
EOMONTH ................................................................................................................... 101
GODZINA — HOUR .................................................................................................... 102
MIESIĄC — MONTH .................................................................................................. 105
MINUTA — MINUTE .................................................................................................. 106
NETWORKDAYS ........................................................................................................ 107
ROK — YEAR .............................................................................................................. 109
SEKUNDA — SECOND .............................................................................................. 110
TERAZ — NOW ........................................................................................................... 111
WEEKNUM .................................................................................................................. 113
WORKDAY .................................................................................................................. 115
YEARFRAC .................................................................................................................. 116

Spis treści

Rozdział 5. Funkcje informacyjne ................................................................... 117

BRAK — NA ................................................................................................................ 117
CZY.ADR — ISREF ..................................................................................................... 119
CZY.BŁ — ISERR ........................................................................................................ 120
CZY.BŁĄD — ISERROR ............................................................................................ 122
CZY.BRAK — ISNA .................................................................................................... 123
CZY.LICZBA — ISNUMBER ..................................................................................... 124
CZY.LOGICZNA — ISLOGICAL ............................................................................... 126
CZY.NIE.TEKST — ISNONTEXT .............................................................................. 127
CZY.PUSTA — ISBLANK .......................................................................................... 128
CZY.TEKST — ISTEXT .............................................................................................. 129
INFO .............................................................................................................................. 131
ISEVEN ......................................................................................................................... 133
ISODD — ISODD ......................................................................................................... 134
KOMÓRKA — CELL ................................................................................................... 135
L — N ............................................................................................................................ 139
NR.BŁĘDU — ERROR.TYPE ..................................................................................... 140
TYP — TYPE ................................................................................................................ 141

Rozdział 6. Funkcje tekstowe ........................................................................ 143

ASC ............................................................................................................................... 143
BAHTTEXT .................................................................................................................. 144
DŁ — LEN .................................................................................................................... 145
FRAGMENT.TEKSTU — MID ................................................................................... 146
KOD — CODE .............................................................................................................. 148
KWOTA — DOLLAR .................................................................................................. 149
LEWY — LEFT ............................................................................................................ 150
LITERY.MAŁE — LOWER ........................................................................................ 151
LITERY.WIELKIE — UPPER ..................................................................................... 152
OCZYŚĆ — CLEAN .................................................................................................... 153
PODSTAW — SUBSTITUTE ...................................................................................... 153
PORÓWNAJ — EXACT .............................................................................................. 155
POWT — REPT ............................................................................................................ 156
PRAWY — RIGHT ....................................................................................................... 157
SZUKAJ.TEKST — SEARCH ..................................................................................... 157
T — T ............................................................................................................................ 159
TEKST — TEXT ........................................................................................................... 160
USUŃ.ZBĘDNE.ODSTĘPY — TRIM ......................................................................... 162
WARTOŚĆ — VALUE ................................................................................................ 163
Z.WIELKIEJ.LITERY — PROPER ............................................................................. 164
ZAOKR.DO.TEKST — FIXED ................................................................................... 164
ZASTĄP — REPLACE ................................................................................................ 166
ZŁĄCZ.TEKSTY — CONCATENATE ...................................................................... 168
ZNAJDŹ — FIND ......................................................................................................... 168
ZNAK — CHAR ........................................................................................................... 170

Rozdział 7. Funkcje wyszukiwania i adresu ..................................................... 171

ADR.POŚR — INDIRECT ........................................................................................... 171
ADRES — ADDRESS .................................................................................................. 174
HIPERŁĄCZE — HYPERLINK .................................................................................. 176
ILE.WIERSZY — ROWS ............................................................................................. 178
INDEKS — INDEX ...................................................................................................... 179
LICZBA.KOLUMN — COLUMNS ............................................................................. 182

Excel. Funkcje w przykładach

NR.KOLUMNY — COLUMN ..................................................................................... 184
OBSZARY — AREAS ................................................................................................. 185
PODAJ.POZYCJĘ — MATCH .................................................................................... 185
PRZESUNIĘCIE — OFFSET ....................................................................................... 191
TRANSPONUJ — TRANSPOSE ................................................................................. 195
WIERSZ — ROW ......................................................................................................... 196
WYBIERZ — CHOOSE ............................................................................................... 197
WYSZUKAJ — LOOKUP ........................................................................................... 199
WYSZUKAJ.PIONOWO — VLOOKUP ..................................................................... 201
WYSZUKAJ.POZIOMO — HLOOKUP ..................................................................... 202

Rozdział 8. Funkcje matematyczne ................................................................. 205

ACOS — ACOS ............................................................................................................ 205
ACOSH — ACOSH ...................................................................................................... 206
ASIN — ASIN ............................................................................................................... 206
ASINH — ASINH ......................................................................................................... 207
ATAN — ATAN ........................................................................................................... 207
ATAN2 — ATAN2 ....................................................................................................... 207
ATANH — ATANH ..................................................................................................... 208
COS — COS .................................................................................................................. 209
COSH — COSH ............................................................................................................ 210
EXP — EXP .................................................................................................................. 211
FACTDOUBLE — FACTDOUBLE ............................................................................ 211
GCD — GCD ................................................................................................................ 212
ILOCZYN — PRODUKT ............................................................................................. 212
KOMBINACJE — COMBIN ....................................................................................... 213
LCM — LCM ................................................................................................................ 213
LICZBA.CAŁK — TRUNC ......................................................................................... 214
LN — LN ....................................................................................................................... 216
LOG — LOG ................................................................................................................. 217
LOG10 — LOG10 ......................................................................................................... 218
LOS — RAND .............................................................................................................. 218
MACIERZ.ILOCZYN — MMULT .............................................................................. 219
MACIERZ.ODW — MINVERSE ................................................................................ 220
MOD — MOD ............................................................................................................... 222
MODUŁ.LICZBY — ABS ........................................................................................... 226
MROUND — MROUND .............................................................................................. 227
MULTINOMIAL — MULTINOMIAL ........................................................................ 228
PI — PI .......................................................................................................................... 229
PIERWIASTEK — SQRT ............................................................................................ 233
POTĘGA — POWER ................................................................................................... 234
QUOTIENT — QUOTIENT ......................................................................................... 235
RADIANY — RADIANS ............................................................................................. 236
RANDBETWEEN — RANDBETWEEN .................................................................... 236
RZYMSKIE — ROMAN .............................................................................................. 238
SERIESSUM — SERIESSUM ..................................................................................... 239
SILNIA — FACT .......................................................................................................... 240
SIN — SIN .................................................................................................................... 240
SINH — SINH ............................................................................................................... 241
SQRTPI — SQRTPI ...................................................................................................... 241
STOPNIE — DEGREES ............................................................................................... 241
SUMA — SUM ............................................................................................................. 242

Spis treści

SUMA.ILOCZYNÓW — SUMPRODUCT ................................................................. 244
SUMA.JEŻELI — SUMIF ............................................................................................ 246
SUMA.KWADRATÓW — SUMSQ ............................................................................ 248
SUMA.X2.M.Y2 — SUMX2MY2 ............................................................................... 249
SUMA.X2.P.Y2 — SUMX2PY2 .................................................................................. 250
SUMA.XMY.2 — SUMXMY2 .................................................................................... 251
SUMY.POŚREDNIE — SUBTOTAL .......................................................................... 252
TAN — TAN ................................................................................................................. 260
TANH — TANH ........................................................................................................... 260
WYZNACZNIK.MACIERZY — MDETERM ............................................................ 261
ZAOKR — ROUND ..................................................................................................... 263
ZAOKR.DO.CAŁK — INT .......................................................................................... 264
ZAOKR.DO.NPARZ — ODD ...................................................................................... 265
ZAOKR.DO.PARZ — EVEN ....................................................................................... 265
ZAOKR.DÓŁ — ROUNDDOWN ................................................................................ 266
ZAOKR.GÓRA — ROUNDUP .................................................................................... 267
ZAOKR.W.DÓŁ — FLOOR ........................................................................................ 267
ZAOKR.W.GÓRĘ — CEILING ................................................................................... 268
ZNAK.LICZBY — SIGN ............................................................................................. 269

Rozdział 9. Funkcje bazy danych .................................................................... 273

Kryteria .......................................................................................................................... 274

Ogólne zasady tworzenia kryteriów ........................................................................ 274
Koniunkcja i alternatywa warunków ....................................................................... 275
Warunek w postaci formuły .................................................................................... 278
Równa się czy zaczyna się od? ............................................................................... 279
Znaki globalne ......................................................................................................... 280

BD.ILE.REKORDÓW — DCOUNT ............................................................................ 281
BD.ILE.REKORDÓW.A — DCOUNTA ..................................................................... 282
BD.ILOCZYN — DPRODUCT .................................................................................... 284
BD.MAX — DMAX ..................................................................................................... 286
BD.MIN — DMIN ........................................................................................................ 287
BD.ODCH.STANDARD — DSTDEV ......................................................................... 288
BD.ODCH.STANDARD.POPUL — DSTDEVP ......................................................... 289
BD.POLE — DGET ...................................................................................................... 289
BD.SUMA — DSUM .................................................................................................... 290
BD.ŚREDNIA — DAVERAGE ................................................................................... 291
BD.WARIANCJA — DVAR ........................................................................................ 293
BD.WARIANCJA.POPUL — DVARP ........................................................................ 294

Rozdział 10. Funkcje statystyczne .................................................................... 295

CZĘSTOŚĆ — FREQUENCY ..................................................................................... 296
ILE.LICZB — COUNT ................................................................................................. 299
ILE.NIEPUSTYCH — COUNTA ................................................................................ 301
KOWARIANCJA — COVAR ...................................................................................... 303
KURTOZA — KURT ................................................................................................... 306
KWARTYL — QUARTILE ......................................................................................... 307
LICZ.JEŻELI — COUNTIF ......................................................................................... 308
LICZ.PUSTE — COUNTBLANK ................................................................................ 312
MAX — MAX ............................................................................................................... 313
MAX.A — MAXA ........................................................................................................ 315
MAX.K — LARGE ....................................................................................................... 315

Excel. Funkcje w przykładach

MEDIANA — MEDIAN .............................................................................................. 317
MIN — MIN .................................................................................................................. 319
MIN.A — MINA ........................................................................................................... 320
MIN.K — SMALL ........................................................................................................ 320
NACHYLENIE — SLOPE ........................................................................................... 322
NORMALIZUJ — STANDARDIZE ............................................................................ 323
ODCH.KWADRATOWE — DEVSQ .......................................................................... 326
ODCH.STANDARD.POPUL — STDEVP .................................................................. 327
ODCH.STANDARD.POPUL.A — STDEVPA ............................................................ 328
ODCH.STANDARDOWE — STDEV ......................................................................... 329
ODCH.STANDARDOWE.A — STDEVA .................................................................. 330
ODCH.ŚREDNIE — AVEDEV .................................................................................... 330
ODCIĘTA — INTERCEPT .......................................................................................... 331
PEARSON — PEARSON ............................................................................................. 332
PERCENTYL — PERCENTILE .................................................................................. 333

Trochę teorii ............................................................................................................ 334

PERMUTACJE — PERMUT ....................................................................................... 335

Trochę teorii ............................................................................................................ 336

POZYCJA — RANK .................................................................................................... 338
PRAWDP — PROB ...................................................................................................... 340
PROCENT.POZYCJA — PERCENTRANK ............................................................... 341
PRÓG.ROZKŁAD.DWUM — CRITBINOM .............................................................. 343
R.KWADRAT — RSQ ................................................................................................. 344
Informacje ogólne o regresji. Funkcje regresji nieliniowej ........................................... 345
REGLINP — LINEST ................................................................................................... 345
REGLINW — TREND ................................................................................................. 347
REGLINX — FORECAST ........................................................................................... 349
ROZKŁAD.NORMALNY — NORMDIST ................................................................. 353
SKOŚNOŚĆ — SKEW ................................................................................................. 356
ŚREDNIA — AVERAGE ............................................................................................. 359
ŚREDNIA.A — AVERAGEA ...................................................................................... 360
ŚREDNIA.GEOMETRYCZNA — GEOMEAN .......................................................... 360
ŚREDNIA.HARMONICZNA — HARMEAN ............................................................. 361
ŚREDNIA.WEWN — TRIMMEAN ............................................................................ 363
TEST.CHI — CHITEST ............................................................................................... 363
TEST.F — FTEST ......................................................................................................... 364
TEST.T — TTEST ........................................................................................................ 364
TEST.Z — ZTEST ........................................................................................................ 365
UFNOŚĆ — CONFIDENCE ........................................................................................ 365
WARIANCJA — VAR ................................................................................................. 366
WARIANCJA.A — VARA .......................................................................................... 366
WARIANCJA.POPUL — VARP ................................................................................. 367
WARIANCJA.POPUL.A — VARPA ........................................................................... 367
WSP.KORELACJI — CORREL .................................................................................. 368
WYST.NAJCZĘŚCIEJ — MODE ................................................................................ 369

Rozdział 11. Funkcje finansowe ....................................................................... 371

Wbudowane funkcje finansowe ..................................................................................... 372

DB ........................................................................................................................... 372
DDB ........................................................................................................................ 374
FV ............................................................................................................................ 374
IPMT ....................................................................................................................... 376
IRR .......................................................................................................................... 378

Spis treści

ISPMT ..................................................................................................................... 379
MIRR ....................................................................................................................... 380
NPER ....................................................................................................................... 381
NPV ......................................................................................................................... 382
PMT ......................................................................................................................... 384
PPMT ...................................................................................................................... 385
PV ............................................................................................................................ 385
RATE ...................................................................................................................... 386
SLN ......................................................................................................................... 388
SYD ......................................................................................................................... 389
VDB ........................................................................................................................ 389

Funkcje finansowe z pakietu ATP ................................................................................. 390

Rozdział 12. Funkcje inżynierskie ..................................................................... 395

Skorowidz .................................................................................... 399

Rozdział 6.

Funkcje tekstowe

Funkcje tekstowe służą w zasadzie do działań na tekstach, ale Excel jest tu tolerancyjny
i często argumentami mogą być także liczby. Przekonamy się o tym, analizując podane
w tym rozdziale przykłady. Dzięki funkcjom tekstowym można łatwo między innymi:
wyszukiwać w tekście wybrane znaki, zamieniać je na inne, pozbywać się zbędnych spa-
cji lub zamieniać małe litery na wielkie bądź odwrotnie.

Nie wszystkie funkcje działające na tekstach lub tekstów dotyczące zostały włączone do
tej kategorii. Na przykład, opisana w poprzednim rozdziale, funkcja

CZY.TEKST

została

zaliczona do funkcji informacyjnych. Również w innych kategoriach znajdują się funk-
cje działające na tekstach, lecz o nich, zgodnie z porządkiem tej książki, będzie mowa
w innych rozdziałach.

Podstawowe funkcje tekstowe obsługują teksty, w których znaki są pojedynczymi baj-
tami. Funkcje te mają swoje odpowiedniki przeznaczone do obsługi tekstów pisanych
znakami dwubajtowymi (znakami pełnej szerokości), co ma miejsce w takich językach
jak tajski. Ich nazwy są rozszerzone o kropkę oddzielającą i literę

. Na przykład odpo-

wiednikiem funkcji

DŁ

jest funkcja

DŁ.B

. Pierwsza zwraca liczbę znaków jednobajtowych

w tekście, a druga liczbę bajtów w tekstach zapisanych znakami dwubajtowymi. Ponie-
waż przydatność funkcji obsługujących teksty zapisane znakami dwubajtowymi jest
w Polsce niewielka, nie będę ich opisywał. Opiszę jedynie kilka funkcji napisanych
specjalnie dla języków ze znakami dwubajtowymi i nie mających odpowiedników obsłu-
gujących zwykłe teksty. Przykładem takiej funkcji jest np.

BAHTTEXT

ASC

Funkcja wbudowana, dostępna w VBA.

W językach z dwubajtowym zestawem znaków (DBCS) zmienia znaki o pełnej szero-
kości (dwubajtowe) na znaki o połówkowej szerokości (jednobajtowe).

144

Część II

♦ Funkcje

Składnia:

tekst

— to tekst lub odwołanie do komórki zawierającej tekst. Tekst jest

zmieniany tylko wtedy, gdy zawiera znaki dwubajtowe — patrz rysunek 6.1
W przykładzie podanym w systemie pomocy Excel, słowo

„EXCEL”

, nie

zawierające znaków dwubajtowych, nie zostało zmienione.

Rysunek 6.1.
Działanie funkcji ASC

W VBA istnieje funkcja Asc zwracająca kod ASCII pierwszego znaku tekstu. Funkcja

VBA jest używana przez bezpośrednie użycie w procedurze; wywołanie arkuszowej

funkcji ASC musi być zgodne z metodą wywoływania funkcji arkuszowych, opisaną

we wstępie do części II.

BAHTTEXT

Funkcja wbudowana, dostępna w VBA.

Zamienia liczbę na tekst w języku tajskim, dodaje przyrostek „Baht”.

Składnia:

liczba

— to liczba lub odwołanie do komórki zawierającej liczbę, co

pokazuje przykład z systemu pomocy Excela pokazany na rysunku 6.2.

Rysunek 6.2.
Działanie funkcji
BAHTTEXT

Ciekawe, że odpowiedniej funkcji nie ma dla żadnego innego języka, nawet dla angiel-
skiego. John Walkenbach, jeden z najlepszych znawców Excela, przypuszcza, że w gru-
pie programistów tworzących ten program jest wielu miłośników kuchni tajskiej, którzy
w ten sposób wyrazili swą wdzięczność.

Rozdział 6.

♦ Funkcje tekstowe

145

DŁ — LEN

Funkcja wbudowana, niedostępna w VBA.

Oblicza liczbę znaków ciągu tekstowego.

Składnia:

tekst

— tekst lub odwołanie do komórki zawierającej tekst.

Przykład 6.1. Wyróżnianie zbędnych spacji

Plagą wielu list, zwłaszcza otrzymywanych ze źródeł zewnętrznych, są dodatkowe spa-
cje; utrudniają np. sortowanie i grupowanie wyników, tworzenie sum pośrednich itd.,
bowiem dla Excela

Jan Kowalski

z jedną spacją między imieniem i nazwiskiem to cał-

kiem inna osoba niż

Jan Kowalski

z dwiema spacjami. Najtrudniejsze do wyszukania są

komórki zawierające dodatkowe spacje na końcu tekstu, gdyż takich spacji nie widzimy
przy przeglądaniu arkusza (np. w komórce

na rysunku 6.3)

. Ale wyszukanie nawet

widocznych spacji nadmiarowych nie jest możliwe, jeżeli danych są tysiące.

Należy pamiętać, że programy baz danych nie tolerują takich niedokładności i przed
eksportem przygotowanej listy do pliku, który ma być czytany przez inne oprogramo-
wanie, należy dane bezwzględnie oczyścić ze wszystkich śmieci, w tym z dodatkowych
spacji. Łatwiej to zrobić, jeżeli komórki z dodatkowymi spacjami wyróżnimy za pomocą
formatowania warunkowego, tak jak na rysunku 6.3.

Rysunek 6.3.
Użycie formatowania
warunkowego pozwala
wyróżnić komórki,
w których tekst zawiera
dodatkowe spacje

Excel takie końcowe spacje traktuje dość tolerancyjnie i pomija np. przy automatycznym tworzeniu sum
pośrednich, ale dla spacji w środku tekstu nie jest wyrozumiały.

146

Część II

♦ Funkcje

Zauważ, że w formule warunku formatowania zostały użyte adresy względne. W komórce
A5 tekst

Anna Giza

został wyróżniony z powodu dodatkowej spacji na końcu, widocznej

po ustawieniu kursora po przejściu do edycji zawartości komórki.

Tekst jest wyróżniany, jeżeli jego długość po usunięciu zbędnych spacji (w wyniku dzia-
łania funkcji

USUŃ.ZBĘDNE.ODSTĘPY

) jest różna od pierwotnej.

FRAGMENT.TEKSTU — MID

Funkcja wbudowana, niedostępna w VBA.

Zwraca fragment ciągu tekstowego — określoną liczbę znaków, począwszy od poda-
nej pozycji.

Składnia:

tekst

— tekst lub odwołanie do komórki z tekstem zawierającym znaki, które

należy wyodrębnić. Excel zezwala tu na użycie liczby jako pierwszego argumentu
funkcji, ale zawsze uwzględnia jedynie jej rzeczywistą wartość, a nie postać
wynikającą z formatowania (patrz rysunek 6.4).

Rysunek 6.4.
Funkcja FRAGMENT.TEKSTU
zawsze używa rzeczywistej wartości liczby,
nie uwzględniając formatowania

liczba_początkowa

— określa pozycję pierwszego znaku, który należy wyodrębnić

(numeracja rozpoczyna się od 1).

liczba_znaków

— liczba znaków, które należy wyodrębnić.

Jeżeli:

funkcja zwraca

liczba_początkowa

jest > od długości tekstu

wartość "" (tekst pusty)

liczba_początkowa

jest < od długości tekstu, ale

liczba_początkowa

plus

liczba_znaków

jest ≥ od długości tekstu

znaki do końca tekstu

liczba_początkowa

jest < niż 1

wartość błędu

#ARG!

liczba_znaków

jest < 0

wartość błędu

#ARG!

Przykład 6.2. Wybieranie cyfr z liczby

Jako pierwszego argumentu funkcji

FRAGMENT.TEKSTU

można użyć liczby lub odwołania

do komórki zawierającej liczbę, jednakże otrzymany wówczas wynik może czasem zdzi-
wić niedoświadczonego użytkownika Excela. Przykład został pokazany na rysunku 6.4.

Rozdział 6.

♦ Funkcje tekstowe

147

W obu komórkach B1 i B2 jest zapisana ta sama liczba, którą w komórce B2 pozosta-
wiono w formacie ogólnym, zaś w komórce B1 nadano jej format daty. Widać, że funk-
cja

FRAGMENT.TEKSTU

tego nie uwzględnia, zwracając w obu przypadkach ciąg tekstowy

(zauważ wyrównanie do lewej) złożony z 4. i 5. cyfry pobranej z zapisu ogólnego.

Przykład 6.3. Konwersja nietypowego formatu daty

Często Excela używamy do obróbki danych otrzymywanych z zewnętrznych źródeł, np.
z systemów komputerowych typu mainframe. Daty w takich plikach mogą być zapisane
w różnych formatach niezrozumiałych dla Excela, co wymusza stosowanie konwersji.

Przykładem mogą być: data i czas zapisane w formacie

rrrrdddggmm

, gdzie

rrrr

ozna-

cza rok,

ddd

kolejny dzień roku od 001 do 366, a

odpowiednio godziny i minuty

w formacie 24-godzinnym.

Rysunek 6.5 pokazuje sposób poradzenia sobie z tym problemem.

Rysunek 6.5.
Użycie funkcji
FRAGMENT.TEKSTU
do konwersji daty
i czasu zapisanych
w niestandardowym
formacie

Jak widać na rysunku 6.5, całkowita formuła konwersji ma w komórce

postać:

=DATA(LEWY(A2;4);1;1)

+FRAGMENT.TEKSTU(A2;5;3)
+CZAS(FRAGMENT.TEKSTU(A2;8;2);PRAWY(A2;2);0)

gdzie:

LEWY(A2;4)

pobiera cztery pierwsze cyfry (

1999

), co

DATA(LEWY(A2;4);1;1)

zamienia na

36161

, czyli numer kolejny daty 1 stycznia 1999 roku;

FRAGMENT.TEKSTU(A2;5;3)

wycina z liczby zapisanej w

trzy kolejne cyfry,

począwszy od piątej, tworząc tekst "245";

dzięki „domyślności” Excela dodanie liczby i tekstu

36161+"245"

daje liczbę

36 406

;

FRAGMENT.TEKSTU(A2;8;2)

daje tekst

"01"

złożony z 8. i 9. cyfry, a

PRAWY(A2;2)

tekst

"12"

złożony z cyfr 10. i 11., zaś

CZAS("01";"12";0)=CZAS(1;12;0)

daje

kolejną liczbę czasu

0,05

;

406+0,05=36

406,05

to kolejna liczba daty i czasu, która przez nadanie formatu

dd-mm-rrrr gg:mm

jest widoczna w postaci

03-09-1999 01:12

, podając

w zrozumiały sposób datę i godzinę.

148

Część II

♦ Funkcje

KOD — CODE

Funkcja wbudowana, niedostępna w VBA.

Zwraca kod numeryczny pierwszego znaku ciągu tekstowego.

Składnia:

tekst

— tekst lub odwołanie do komórki zawierającej tekst.

Przykład 6.4. Użycie funkcji KOD do sprawdzania poprawności
wpisywanych danych

Często dane wpisywane do tabeli muszą spełniać określone warunki, być liczbami z od-
powiedniego zakresu albo określonymi ciągami znaków. Załóżmy, że tworzymy tabelę:
w pierwszej kolumnie znajduje się nazwa towaru, a w drugiej jej trzyznakowy kod,
rozpoczynający się od cyfry 5, z wielką literą na drugim miejscu (bez liter polskich)
i dowolną cyfrą na trzecim (np.

5A3

jest kodem prawidłowym, zaś

5ac

6A3

5A33

nie

spełniają założeń).

Należy zabezpieczyć się przed wpisywaniem błędnych kodów, co zrobimy za pomocą
wbudowanej w Excela procedury sprawdzania poprawności. Funkcji

KOD

użyjemy do

sprawdzenia, czy na drugim miejscu w ciągu znakowym znajduje się wielka litera. Do-
zwolone litery to

…

. Ich kody to odpowiednio:

…

— łatwo to sprawdzić,

wpisując np. do

formułę

=KOD(A1)

, a następnie do

litery, których kod chcemy

odczytać. Polskie litery

itd. mają kody spoza tego zakresu i ich uwzględnienie wyma-

gałoby rozbudowania, a więc wydłużenia formuły, a przy tym nie wniosłoby nic w istotę
rozumienia działania funkcji

KOD

Zaznacz zakres, który ma być objęty sprawdzaniem — na rysunku 6.6 został
on wyróżniony innym kolorem — i wydaj polecenie Dane/Sprawdzanie
poprawności.

Rysunek 6.6.
Wpisywanie formuły
sprawdzania
poprawności kodu
wpisywanego
do kolumny B

Rozdział 6.

♦ Funkcje tekstowe

149

Na karcie Ustawienia wybierz Dozwolone/Niestandardowe, a w polu Formuła
wpisz formułę:

=ORAZ(

DŁ(B2)=3;

LEWY(B2;1)="5";
KOD(FRAGMENT.TEKSTU(B2;2;1))>64;KOD(FRAGMENT.TEKSTU(B2;2;1))<91;
CZY.LICZBA(WARTOŚĆ(FRAGMENT.TEKSTU(B2;3;1)))

)

Na kartach Komunikat wejściowy i Alert o błędzie możesz dodatkowo wpisać
komunikaty objaśniające, np. takie jak na rysunku 6.7.

Rysunek 6.7.
Próba wpisania
błędnego kodu została
oprotestowana

Elementy formuły sprawdzające poszczególne znaki kodu zostały w punkcie 2. zapisane
w oddzielnych wierszach, aby ułatwić zrozumienie ich działania. Każdy pojedynczy ele-
ment formuły jest prosty, więc pomijam szczegółowe objaśnienia.

KWOTA — DOLLAR

Funkcja wbudowana, dostępna w VBA.

Zmienia liczbę na tekst i dodaje symbol waluty, przy czym następuje zaokrąglenie do
liczby miejsc dziesiętnych zgodnie z drugim argumentem funkcji. Stosowany format
to

# ##0,00 zł

Składnia:

liczba

— liczba lub odwołanie do komórki zawierającej liczbę (ew. formułę

zwracającą liczbę),

miejsca_dziesiętne

— liczba cyfr po przecinku dziesiętnym; pominięcie oznacza

wartość 2. Dopuszczalne jest podawanie wartości ujemnych, których znaczenie
pokazuje rysunek 6.8.

150

Część II

♦ Funkcje

Rysunek 6.8.
Zaokrąglenia
sterowane drugim
argumentem funkcji
KWOTA są
wykonywane zgodnie
z zasadami arytmetyki

Należy zwrócić uwagę na różnicę między działaniem funkcji

KWOTA a nadawaniem

liczbie formatu walutowego. Liczba, której nadano format walutowy, pozostaje liczbą
(na rysunku 6.8 w komórce

A7 — wyrównanie do prawej), zaś funkcja KWOTA zamie-

nia liczbę na tekst (w komórkach

A2:A6 — wyrównanie do lewej).

Przykład 6.5. Łączenie kwot walutowych z tekstem

Często musimy drukować rożnego rodzaju zestawienia, w których tekst łączymy z su-
mami pieniężnymi. Nadanie liczbie formatu walutowego zmienia jedynie sposób jej
wyświetlania, pozostawiając samą liczbę bez zmiany. Dlatego po połączeniu z tekstem
znak waluty nie jest widoczny, jak w komórce

w górnej części rysunku 6.9. Potrzebne

jest dołączenie dodatkowego członu tekstowego

" zł"

, jak w komórce

na tym samym

rysunku (formuły użyte w komórkach

są pokazane obok w kolumnie

Rysunek 6.9.
Porównanie rysunków
górnego i dolnego
pokazuje, że funkcja
KWOTA ułatwia
łączenie tekstu
z sumami pieniężnymi

Dołączania dodatkowego członu, złożonego ze spacji i symbolu waluty, można uniknąć,
jeżeli użyjemy funkcji

KWOTA

, jak w dolnej części rysunku 6.9 w komórkach

(użyta

formuła jest widoczna w komórce

LEWY — LEFT

Funkcja wbudowana, niedostępna w VBA.

Zwraca pierwszy znak lub pierwsze znaki ciągu tekstowego.

Składnia:

tekst

— tekst (ciąg tekstowy lub odwołanie do komórki zawierającej ciąg

tekstowy) zawierający znaki, które należy wyodrębnić.

Rozdział 6.

♦ Funkcje tekstowe

151

liczba_znaków

— określa, ile znaków ma zwrócić funkcja

LEWY

. Liczba ta musi

być większa lub równa zeru. Jeśli

liczba_znaków

jest większa niż długość tekstu,

wynikiem funkcji

LEWY

jest cały tekst. Pominięcie argumentu

liczba_znaków

jest równoznaczne z podaniem wartości 1.

Możliwości użycia funkcji

LEWY

zostały zaprezentowane w przykładach 6.3, 6.4, 6.6, 6.7

i 6.26. Jest często stosowana razem z funkcjami

FRAGMENT.TEKSTU

ZNAJDŹ

LITERY.MAŁE — LOWER

Funkcja wbudowana, niedostępna w VBA.

Zmienia w tekście litery wielkie na małe. Innych znaków nie zmienia.

Składnia:

tekst

— tekst (ciąg tekstowy lub odwołanie do komórki zawierającej ciąg

tekstowy).

Przykład 6.6. Tworzenie inicjałów pisanych małymi literami

Excel jest często używany jako narzędzie przygotowywania danych eksportowanych
później do baz danych, systemów księgowych itd. Niektóre takie systemy odróżniają
litery wielkie i małe i dla nich

Kowalski

kowalski

to dwie różne osoby, a

e1JK

e1jk

różne symbole. Dlatego w danych eksportowanych używanie liter wielkich bądź małych
powinno być ściśle kontrolowane.

Na podstawie listy pracowników i działów, w których pracują, należy utworzyć kody
pracowników. Każdy kod ma być pisany małymi literami i składać się z dwuznakowego
oznaczenia działu, pierwszej litery imienia i trzech pierwszych liter nazwiska. Rozwią-
zanie zostało pokazane na rysunku 6.10.

Rysunek 6.10.
Tworzenie kodów
pracowniczych
pisanych małymi
literami

Działanie formuły pokazanej na pasku edycji na rysunku 6.10 jest tak proste, że nie będę
go objaśniał.

152

Część II

♦ Funkcje

W kodach użyto trzech liter nazwiska, aby uniknąć powtarzania się kodów. Używanie

jedynie pierwszych liter imienia i nazwiska zwykle prowadzi do konfliktów, gdyż czę-
sto w jednej firmie, a nawet w jednym dziale pracują osoby, mające te same inicjały,

np. Jan Kowalski i Joanna Kącka.

LITERY.WIELKIE — UPPER

Funkcja wbudowana, niedostępna w VBA.

Zmienia w tekście litery małe na wielkie. Innych znaków nie zmienia.

Składnia:

tekst

— tekst (ciąg tekstowy lub odwołanie do komórki zawierającej ciąg

tekstowy).

Przykład 6.7. Sprawdzanie wprowadzonych danych. Modyfikacja

przykładu 6.4

W przykładzie 6.4 tworzyliśmy tabelę, której pierwsza kolumna zawierała nazwę towaru,
a drugiej — jej trzyznakowy kod, rozpoczynający się od cyfry 5, z wielką literą na dru-
gim miejscu (bez polskich liter) i dowolną cyfrą na trzecim (np.

5A3

jest kodem prawi-

dłowym, zaś

5ac

6A3

5A33

nie spełniają założeń).

Teraz chcemy zezwolić, aby drugim znakiem kodu była po prostu litera, obojętnie, czy
wielka, czy mała. Wymaga to zmiany formuły sprawdzającej dane na następującą:

=ORAZ(
DŁ(B2)=3;LEWY(B2;1)="5";

KOD(LITERY.WIELKIE(FRAGMENT.TEKSTU(B2;2;1)))>64;KOD(LITERY.WIELKIE

(FRAGMENT.TEKSTU(B2;2;1)))<91;

CZY.LICZBA(WARTOŚĆ(FRAGMENT.TEKSTU(B2;3;1)))
)

Formuła

FRAGMENT.TEKSTU(B2;2;1)

„wykroi” z komórki drugi wpisany znak. Może to

być litera mała lub jakiś inny znak (w tym litera wielka). W pierwszym przypadku funk-
cja

LITERY.WIELKIE

zamieni małą literę na wielką, w drugim nie nastąpi żadna zmiana.

Kody liter wielkich należą do przedziału od 65 (kod A) do 90 (kod Z), więc sprawdze-
nie, czy kod znaku zwróconego przez funkcję

LITERY.WIELKIE

jest > 64 oraz < 91, jest

równoznaczne ze sprawdzeniem, czy znakiem wpisanym na drugim miejscu jest litera
(niezależnie od tego, czy jest mała, czy wielka).

Oczywiście, jak zwykle w Excelu, ta formuła mogła być napisana w inny sposób, ale
to pozostawiam domyślności czytelników.

Rozdział 6.

♦ Funkcje tekstowe

153

OCZYŚĆ — CLEAN

Funkcja wbudowana, dostępna w VBA.

Usuwa z tekstu wszystkie znaki niedrukowane.

Składnia:

tekst

— tekst (ciąg tekstowy lub odwołanie do komórki zawierającej ciąg

tekstowy).

Przykład 6.8. Oczyszczanie importowanych danych

W danych importowanych ze źródeł zewnętrznych, np. z systemów mainframe lub z In-
ternetu, trafiają się znaki niedrukowalne (nierozpoznawalne), np. znaki o kodach z zakresu
1 – 31 oraz 129, 141, 143, 144 i 157. Często kilka takich różnych znaków Excel wyświe-
tla w arkuszu tak samo — np. znaki o kodach 11 i 129 są pokazywane jako kwadraciki,
widoczne na rysunku 6.11.

Rysunek 6.11.
Funkcja OCZYŚĆ usuwa
znaki o „złych” kodach,
tworząc zbitki, czasem
wymagające rozdzielenia,
jak na prawej części tego
rysunku

PODSTAW — SUBSTITUTE

Funkcja wbudowana, dostępna w VBA.

Zamienia w ciągu tekstowym

stary_tekst

nowy_tekst

Składnia:

tekst

— tekst (ciąg tekstowy lub odwołanie do komórki zawierającej ciąg

tekstowy),

stary_tekst

— to tekst lub odwołanie do komórki zawierającej tekst, którego

część znaków należy zastąpić,

nowy_tekst

— to tekst, który ma zastąpić

stary_tekst

154

Część II

♦ Funkcje

wystapienie_liczba

— jeżeli

stary_tekst

występuje w

tekst

kilkakrotnie,

wystapienie_liczba

określa, przy którym wystąpieniu dojdzie do zamiany.

Jeżeli ten argument jest pominięty, każdy znaleziony

stary_tekst

zostanie

zamieniony na

nowy_tekst

Funkcji

PODSTAW

należy używać, gdy określony ciąg znaków ma być zamieniony na inny,

np. formuła

=PODSTAW("mama";"m";"t")

zamienia

i daje tekst

tata

, przy czym miej-

sce wystąpienia

nie ma znaczenia.

Funkcja

ZASTĄP

służy do zamiany na inny dowolnego tekstu występującego w określo-

nym miejscu ciągu tekstowego, np. formuła

=ZASTĄP("mama";3;2;"terac")

dwa znaki

tekstu, zaczynając od trzeciego, zamienia na nowy tekst

terac

, przy czym istotne jest

miejsce rozpoczęcia zmiany i liczba zmienianych znaków, a nie ma znaczenia to, jakie
to są znaki.

Przykład 6.9. Zliczanie wystąpień wybranego znaku

Czasami chcemy sprawdzić, ilokrotnie wybrany znak występuje w tekście zapisanym
w komórce. Rysunek 6.12 przedstawia rozwiązanie.

Rysunek 6.12.
Zliczanie wystąpień
liter w tekście

Funkcja

PODSTAW

zamienia wybrany znak na tekst pusty, czyli praktycznie go usuwa

i zwraca tekst skrócony o tyle, ile wystąpień danego znaku zostało znalezionych. Odję-
cie długości tekstu skróconego od długości tekstu pełnego informuje, ile razy dany znak
został w tekście znaleziony.

Ponieważ funkcja podstaw odróżnia litery wielkie i małe, formuła w komórce

zliczyła

jedynie wystąpienia wielkiej litery

, a formuła w

jedynie wystąpienia małej litery

W zmienionej formule w komórce

=DŁ($A$1)-DŁ(PODSTAW(LITERY.WIELKIE($A$1);"Z";""))

najpierw funkcja

LITERY.WIELKIE

zamienia wszystkie litery na wielkie, po czym wyszu-

kuje w tekście wielką literę

. W tak zmienionym tekście wielkie litery

są zamieniane

na pusty tekst. Różnica długości tekstu pierwotnego i skróconego odpowiada liczbie
znalezionych liter

(razem wielkich i małych).

Formuła w

działa analogicznie; również zlicza wszystkie litery

(wielkie i małe),

dokonując przed podstawieniem tekstu pustego zamiany wszystkich liter na małe.

Rozdział 6.

♦ Funkcje tekstowe

155

PORÓWNAJ — EXACT

Funkcja wbudowana, niedostępna w VBA.

Porównuje dwa teksty i zwraca wartość

PRAWDA

, jeśli są takie same, lub

FAŁSZ

, gdy

są różne. Uwzględnia wielkość liter, ale ignoruje różnice w formatowaniu (patrz
rysunek 6.13).

Rysunek 6.13.
Porównanie
bezpośrednie komórek
a użycie funkcji
PORÓWNAJ

Składnia:

tekst1, tekst2

— porównywane teksty lub odwołania do komórek

zawierających teksty (ciągi tekstowe).

Przykład 6.10. Zliczanie tekstów pisanych małymi (wielkimi literami)

W kolumnie A mamy zapisane kody jakichś operacji. Są to ciągi tekstowe złożone z liter
i cyfr. W kodach operacji przedpołudniowych użyto liter małych, a w kodach operacji
popołudniowych — liter wielkich. Naszym zadaniem jest policzenie operacji przedpo-
łudniowych i popołudniowych. Rozwiązanie zostało pokazane na rysunku 6.14.

Rysunek 6.14.
Użycie funkcji
PORÓWNAJ
do zliczania tekstów
pisanych małymi bądź
wielkimi literami

Formuła użyta w kolumnie

(pokazana na rysunku 6.14 na pasku edycji)

=JEŻELI(PORÓWNAJ(LITERY.MAŁE(A2);A2);1;0)

daje

, gdy wszystkie litery występujące w tekście są małe.

Jeżeli w tekście w komórce A2:

nie ma liter wielkich, oba teksty

LITERY.MAŁE(A2)

będą identyczne, więc

formuła

PORÓWNAJ(LITERY.MAŁE(A2);A2)

zwróci wartość

PRAWDA

— wówczas

funkcja

JEŻELI

zwróci wartość drugiego argumentu, czyli

;

156

Część II

♦ Funkcje

są litery wielkie, to

teksty LITERY.MAŁE(A2)

będą różne i formuła

PORÓWNAJ(LITERY.MAŁE(A2);A2)

zwróci wartość

FAŁSZ

, a funkcja

JEŻELI

wartość trzeciego argumentu, czyli

W analogiczny sposób można przeanalizować działanie formuły

=JEŻELI(PORÓWNAJ(LITERY.WIELKIE(A2);A2);1;0)

użytej w kolumnie

do zliczania tekstów zapisanych wielkimi literami.

POWT — REPT

Funkcja wbudowana, dostępna w VBA.

Tworzy ciąg tekstowy, powtarzając podany tekst określoną liczbę razy.

Składnia:

tekst

— tekst, który ma być powtarzany,

ile_razy

— liczba dodatnia określająca liczbę powtórzeń tekstu.

Jeśli argument

ile_razy

ma wartość

, funkcja

POWT

zwraca

(pusty tekst),

nie jest liczbą całkowitą, jest do takiej liczby obcinany

Wynik funkcji

POWT

nie może być dłuższy niż

32 767

znaków. W przeciwnym wypadku

funkcja zwróci wartość błędu

#ARG!

Przykład 6.11. Tworzenie prostego histogramu

Funkcji

POWT

można użyć do prostej graficznej prezentacji danych, co zostało pokazane

na rysunku 6.15.

Rysunek 6.15.
Użycie funkcji POWT
do prostej graficznej
prezentacji danych

Obcinany, a nie zaokrąglany zgodnie z regułami arytmetyki, np.

ile_razy = 3,7

spowoduje trzykrotne

powtórzenie tekstu (przy zaokrągleniu byłyby to 4 powtórzenia).

Rozdział 6.

♦ Funkcje tekstowe

157

PRAWY — RIGHT

Funkcja wbudowana, niedostępna w VBA.

Zwraca ostatni znak lub znaki wyodrębnione z ciągu tekstowego.

Składnia:

tekst

— ciąg tekstowy lub odwołanie do komórki zawierającej ciąg tekstowy,

zawierający znaki, które mają być wyodrębnione.

liczba_znaków

— liczba znaków, jakie ma zwrócić funkcja

PRAWY

. Argument

liczba_znaków

musi być większy lub równy zeru. Argument opcjonalny

domyślnie równy 1.

Jeśli argument

liczba_znaków

jest większy od długości tekstu, to funkcja

PRAWY

zwraca

cały tekst.

O zastosowaniu funkcji

PRAWY

była już mowa w przykładzie 4.3.

SZUKAJ.TEKST — SEARCH

Funkcja wbudowana, dostępna w VBA.

Zwraca pozycję (numer znaku, licząc od lewej), na której po raz pierwszy szukany
znak lub ciąg znakowy (

szukany_tekst

) wystąpił w tekście przeszukiwanym (

obej-

mujący_tekst

Od funkcji

ZNAJDŹ

różni się nieodróżnianiem liczb wielkich i małych oraz dopuszczeniem

do stosowania znaków zastępczych.

Składnia:

szukany_tekst

— tekst, którego szukamy, lub odwołanie do komórki zawierającej

tekst szukany,

obejmujący_tekst

— tekst (odwołanie do komórki zawierającej tekst), w którym

szukany_tekst

jest szukany,

liczba_początkowa

— numer znaku w argumencie

obejmujący_tekst

, od którego

ma się zacząć przeszukiwanie. Pominiecie oznacza użycie domyślnej wartości

158

Część II

♦ Funkcje

Należy pamiętać, że funkcja

SZUKAJ.TEKST

Przeszukuje tekst, nie odróżniając liter wielkich od małych.

Pozwala na stosowanie w argumencie

szukany_tekst

znaków zastępczych:

znaku zapytania (

) i gwiazdki (

). Znak zapytania zastępuje dowolny

pojedynczy znak, a gwiazdka dowolną sekwencję znaków. Aby znaleźć
rzeczywisty znak zastępczy, należy przed nim wpisać znak tyldy (

Zwraca błąd

#ARG!

, jeżeli argument

szukany_tekst

nie zostanie znaleziony.

Zwraca błąd

#ARG!

, jeśli argument

liczba_początkowa

nie jest większy niż

0 lub jest większy niż długość argumentu

obejmujący_tekst

Przy szukaniu tekstu pustego (

) w:

dowolnym tekście niepustym zwraca wartość 1,

tekście pustym — zwraca błąd

#ARG!

Przykład 6.12. Wyróżnianie komórek zawierających szukany tekst

Mając długą listę nazw miejscowości lub towarów, tytułów filmów lub książek itp., często
chcemy szybko wyróżnić komórki zawierające jakiś fragment tekstu. Wystarczy użyć
odpowiedniego formatowania warunkowego, co zostało pokazane na rysunku 6.16.

Rysunek 6.16. Mechanizm wyróżniania komórek zawierających poszukiwany tekst — tekst „ar” jest
zawarty w nazwach Warszawa i Karpacz. Komórki zawierające te nazwy zostały wyróżnione

Po zaznaczeniu formatowanego zakresu (przeciągając myszą od

w dół, aby

było

komórką bieżącą)

, czyli tego, w którym potem ma być wyszukiwany tekst, wydaj pole-

cenie Format/Formatowanie warunkowe i w polu Warunek 1 wybierz opcję Formuła
jest. W polu tego warunku wpisz formułę:

=ORAZ(NIE(CZY.BŁĄD(SZUKAJ.TEKST($C$1;A1)));$C$1<>"")

Komórką, w której wpisujemy szukany tekst, jest

Formuła włączająca formatowanie warunkowe musi spełniać dwa warunki:

Przy innym zaznaczeniu tego zakresu lub zaznaczeniu w ogóle innego zakresu musisz podany tu adres
A1 odpowiednio zmodyfikować.

Rozdział 6.

♦ Funkcje tekstowe

159

zwracać wartość

PRAWDA

, gdy tekst w komórce formatowanej warunkowo

zawiera tekst wpisany do

— co jest sprawdzane przez pierwszy argument

funkcji

ORAZ

NIE(CZY.BŁĄD(SZUKAJ.TEKST($C$1;A1)))

zwracać wartość

FAŁSZ

(niezależnie od zawartości komórki formatowanej

warunkowo), jeżeli komórka

jest pusta, co jest sprawdzane przez drugi

argument funkcji

ORAZ

$C$1<>""

Jeżeli komórka

jest pusta, warunek

$C$1<>""

nie jest spełniony i zwraca wartość

FAŁSZ

— wówczas niezależnie od wartości pierwszego argumentu funkcja

ORAZ

zwraca wartość

FAŁSZ

i formatowanie warunkowe nie jest włączane,

nie jest pusta, warunek

$C$1<>""

jest spełniony i zwraca wartość

PRAWDA

— wówczas funkcja

ORAZ

zwraca wartość pierwszego argumentu, czyli

wyrażenia

NIE(CZY.BŁĄD(SZUKAJ.TEKST($C$1;A1)))

Przyjrzyjmy się teraz wyrażeniu

NIE(CZY.BŁĄD(SZUKAJ.TEKST($C$1;A1)))

. Gdy:

tekst w

zawiera tekst wpisany do

, to

SZUKAJ.TEKST($C$1;A1)

zwraca

pozycję szukanego tekstu (nie zwraca błędu), wtedy funkcja

CZY.BŁĄD

daje

wartość

FAŁSZ

, co zaprzeczenie

NIE

zamienia na

PRAWDA

— formatowanie

warunkowe zostaje włączone,

tekst w

nie zawiera teksu wpisanego do

, to

SZUKAJ.TEKST($C$1;A1)

zwraca błąd

#ARG!

, a funkcja

CZY.BŁĄD

daje wartość

PRAWDA

, co zaprzeczenie

NIE

zamienia na

FAŁSZ

— formatowanie warunkowe nie zostaje włączone,

komórka

jest pusta,

SZUKAJ.TEKST($C$1;A1)

zwraca wartość

, czyli nie

zwraca błędu, a funkcja

CZY.BŁĄD

daje wartość

FAŁSZ

, co zaprzeczenie

NIE

zamienia na

PRAWDA

— formatowanie warunkowe zostałoby włączone,

gdybyśmy się przed tym nie zabezpieczyli za pomocą funkcji

ORAZ

i jej

drugiego argumentu.

T — T

Funkcja wbudowana, niedostępna w VBA.

Jeżeli:

argument jest tekstem, zwraca jego wartość,

tekstem nie jest, zwraca tekst pusty.

Składnia:

wartość

— wartość testowana.

160

Część II

♦ Funkcje

Przykład 6.13. Wyróżnianie komórek nie zawierających liczb

Jeżeli otrzymujemy dane ze źródeł zewnętrznych, zwłaszcza gdy były wpisywane ręcz-
nie, zwykle pełno w nich błędów, tzw. literówek, wynikających z błędnego naciśnięcia
klawiszy. Jeżeli do kolumny wpisywano liczby, zwłaszcza z przecinkiem dziesiętnym,
zapewne część będzie błędna — zamiast przecinków zostaną wpisane kropki lub inne
znaki. Pół biedy, jeżeli kolumna (jak

na rysunku 6.17) jest szeroka i nie została wyrów-

nana, np. do prawej; wtedy teksty wyrównane do lewej wyraźnie odróżniają się od liczb
wyrównanych do prawej

. Jeżeli kolumna została wyrównana do jednej strony, np. pra-

wej, jak

na rysunku 6.17, odróżnienie liczb od tekstów może sprawić niemałe kłopoty.

Sprawę ułatwia proste formatowanie warunkowe, w którym użyto funkcji

Rysunek 6.17. Zastosowanie funkcji T w formatowaniu warunkowym, wyróżniającym teksty

Sposób wprowadzenia formatowania warunkowego został opisany w poprzednim przy-
kładzie 6.12, więc pozostaje jedynie zrozumieć działanie formuły włączającej formato-
wanie warunkowe.

Gdy komórka

zawiera tekst,

T(B2)

zwróci go, a zatem zwracana wartość będzie różna od

tekstu pustego; warunek

=T(B2)<>””

zwróci wartość

FAŁSZ

i formatowanie

warunkowe nie zostanie włączone;

nie zawiera tekstu,

T(B2)

zwróci tekst pusty; warunek

=T(B2)<>""

zwróci

wartość

PRAWDA

i formatowanie warunkowe zostanie włączone.

TEKST — TEXT

Funkcja wbudowana, dostępna w VBA.

Przekształca wartość na tekst zgodnie z podanym formatem liczbowym.

Ważny argument za tym, aby bez rzeczywistej potrzeby nie zmieniać domyślnego wyrównywania
zawartości komórek, czemu w zapamiętaniu oddają się początkujący użytkownicy arkusza, lubiący
„ładne i porządne” tabelki.

Rozdział 6.

♦ Funkcje tekstowe

161

Składnia:

wartość

— liczba lub formuła, zwracająca liczbę, albo odwołanie do komórki

zawierającej liczbę,

format_tekst

— format postaci tekstowej liczby.

Argument

format_tekst

nie powinien zawierać znaku gwiazdki (

Funkcja

TEKST

zamienia liczbę na sformatowany tekst, którego już nie będzie można

używać w obliczeniach.

Przykład 6.14. Łączenie tekstu z liczbami zamienionymi
na tekst sformatowany

Na rysunku 6.18 widać, w jaki sposób zadanie z przykładu 6.5 zostało rozwiązane za
pomocą funkcji

TEKST

(a nie jak poprzednio przez użycie funkcji

KWOTA

Rysunek 6.18. Przekształcanie liczb w tekst sformatowany za pomocą funkcji TEKST

Jak już wspominałem w ćwiczeniu 6.5, w komórce jest przechowywana wartość liczby,
nie zmieniana pomimo narzucenia formatu walutowego (bądź jakiegokolwiek innego).
Dlatego zwykłe połączenie tekstów wykonane w komórce

wyświetla liczbę bez for-

matu waluty.

Funkcja

TEKST

zamienia liczbę na tekst, formatując ją zgodnie z formatem podanym

jako drugi argument. Nie ma tu znaczenia sposób sformatowania liczby w komórce źró-
dłowej. W komórkach

mamy liczby w dwóch różnych formatach (odpowiednio

walutowym i ogólnym). Niezależnie od formatu źródłowego w komórkach

końcu tekstu zostały dołączone liczby w formacie walutowym, gdyż w obu przypadkach
został on użyty jako drugi argument funkcji

TEKST

(patrz postacie formuł pokazane

w kolumnie

W tym przypadku zamiast funkcji

TEKST

mogliśmy zastosować funkcję

KWOTA

(patrz

przykład 6.5), ale funkcja

TEKST

ma większe możliwości, gdyż pozwala na stosowanie

wszelkich dozwolonych w Excelu formatów liczbowych, co zostało pokazane na ry-
sunku 6.19.

Aby wpisać użyty w formacie znak stopnia (

), należy włączyć klawiaturę numeryczną,

po czym, trzymając wciśnięty lewy klawisz Alt, wpisać kod

0176

162

Część II

♦ Funkcje

Rysunek 6.19.
Użycie funkcji TEKST
z formatem
niestandardowym

Funkcja

KWOTA

to funkcja

TEKST

z wpisanym na stałe formatem walutowym. Dlatego

w tekstach finansowych używanie funkcji

KWOTA

jest prostsze i wygodniejsze, ale jeżeli

przy zamianie liczby na tekst chcemy użyć innego formatowania, musimy zastosować
funkcję

TEKST

USUŃ.ZBĘDNE.ODSTĘPY — TRIM

Funkcja wbudowana, dostępna w VBA.

Usuwa z tekstu wszystkie spacje poza pojedynczymi spacjami między wyrazami.

Składnia:

tekst

— tekst lub odwołanie do komórki zawierającej tekst, z którego należy

usunąć zbędne spacje.

Przykład 6.15. Porównanie nie uwzględniające dodatkowych spacji
i nie odróżniające liter wielkich od małych

Mamy porównać teksty w kolumnach

, ale interesują nas jedynie zasadnicze różnice;

utożsamiamy litery wielkie i małe i nie interesują nas dodatkowe spacje przed lub za tek-
stem, albo między słowami. Rysunek 6.20 przedstawia rozwiązanie.

Rysunek 6.20. Trzy różne sposoby porównywania tekstów

Wpis do komórki

"Anna "

(dodatkowa spacja za imieniem).

Porównanie proste w kolumnie

nie odróżnia liter wielkich od małych (

PRAWDA

w ko-

mórce

), ale odróżnia tekst z dodatkową spacją (

FAŁSZ

w komórce

Funkcja

PORÓWNAJ

w kolumnie

odróżnia litery wielkie od małych, a także uwzględnia

dodatkową spację.

Rozdział 6.

♦ Funkcje tekstowe

163

Właściwym rozwiązaniem jest proste porównanie wyników funkcji

USUŃ.ZBĘDNE.ODSTĘPY

wykonane w kolumnie

. W tym przypadku są porównywane teksty po usunięciu zbęd-

nych spacji, a porównanie bezpośrednie ze swej natury nie odróżnia liter wielkich od
małych. Dlatego w kolumnie

wynik

FAŁSZ

otrzymujemy tylko w komórce

, gdzie

porównywane są teksty

Anna

Anna M.

różniące się od siebie w sposób istotny.

WARTOŚĆ — VALUE

Funkcja wbudowana, niedostępna w VBA.

Tekst reprezentujący liczbę zamienia na liczbę.

Składnia:

tekst

— tekst zamknięty znakami cudzysłowu lub odwołanie do komórki

zawierającej tekst.

Przykład 6.16. Zamiana tekstu na liczby

Rysunek 6.21 pokazuje, jak funkcja wartość zamienia na liczby teksty odpowiadające
uznawanym przez Excela formatom liczbowym.

Rysunek 6.21.
Funkcja WARTOŚĆ
zamienia na liczby
teksty odpowiadające
akceptowanym przez
Excela formatom
liczbowym

W komórce

funkcja wartość zwraca błąd, gdyż zapisany w komórce

tekst

21 lipca 2006

, choć zgodny z normą języka polskiego, nie odpowiada żadnemu uzna-

nemu formatowi Excela. Natomiast w komórce

jest zwracana kolejna liczba daty,

gdyż zapis

21 lipiec 2006

w komórce

, choć niezgodny z normą językową, jest

uznawany przez Excela.

W komórce

mamy błąd, gdyż zapisany w komórce

tekst

13:21 am

nie odpowiada

żadnej rzeczywistej godzinie (nie istnieje godzina 13:21 rano).

164

Część II

♦ Funkcje

Z.WIELKIEJ.LITERY — PROPER

Funkcja wbudowana, niedostępna w VBA.

Zamienia na wielką pierwszą literę tekstu i wszystkie inne litery tekstu występujące po
znaku innym niż litera.

Składnia:

tekst

— tekst ujęty w znaki cudzysłowu, odwołanie do komórki zawierającej

tekst lub wynik formuły zwracającej tekst.

Funkcja ta stanowi komplet z funkcjami

LITERY.WIELKIE

LITERY.MAŁE

. Wszystkie trzy

służą do zamiany liter wielkich na małe lub odwrotnie. Wybór zależy od potrzeby.

Przykład 6.17. Poprawianie imion i nazwisk pobieranych z bazy danych

W danych wpisywanych ręcznie, a także otrzymywanych z niepewnych źródeł zewnętrz-
nych, często roi się od literówek. Jednymi z nich są przekłamania polegające na wpisy-
waniu liter wielkich zamiast małych lub odwrotnie. Jeżeli są to imiona i nazwiska lub
nazwy (np. geograficzne), w których słowa powinny rozpoczynać się od wielkiej litery,
do ich uporządkowania możemy użyć funkcji

Z.WIELKEJ.LITERY

, np. w sposób pokazany

na rysunku 6.22.

Rysunek 6.22.
Porządkowanie zapisu
imion i nazwisk
za pomocą funkcji
Z.WIELKIEJ.LITERY

Wyrażenie

A1&" "&B1

łączy w jeden tekst imię i nazwisko wraz z rozdzielającą spacją.

Funkcja

Z.WIELKEJ.LITERY

, działając na wynik tego połączenia, powoduje rozpoczęcie

imienia i nazwiska od wielkiej litery i zapisanie wszystkich pozostałych liter jako małych.

ZAOKR.DO.TEKST — FIXED

Funkcja wbudowana, dostępna w VBA.

Składnia:

Rozdział 6.

♦ Funkcje tekstowe

165

liczba

— liczba, która ma zostać zaokrąglona i zamieniona na tekst;

miejsca_dziesiętne

— liczba cyfr po prawej stronie przecinka dziesiętnego:

domyślną wartością jest 2,

wartość ujemna oznacza zaokrąglanie po lewej stronie przecinka dziesiętnego
(1 — do dziesiątek, 2 — do setek itd.);

bez_przecinka

— opcjonalna wartość logiczna:

PRAWDA

, zapobiega oddzielaniu tysięcy spacją,

FAŁSZ

lub pominięta powoduje oddzielanie tysięcy spacją.

W standardzie anglosaskim tysiące są oddzielane przecinkiem, w standardzie pol-

skim — spacją. Przetłumaczono dosłownie angielską nazwę argumentu, nie dbając

o przystosowanie do polskich realiów.

Liczby w programie Microsoft Excel nie mogą nigdy mieć więcej niż 15 cyfr znaczą-

cych, ale cyfr dziesiętnych może być aż 127.

Główna różnica między standardowym formatowaniem komórki zawierającej liczbę za
pomocą polecenia Format/Komórki/Liczby a formatowaniem za pomocą funkcji

ZAOKR.

DO.TEKST

jest taka, że funkcja

ZAOKR.DO.TEKST

konwertuje liczbę na tekst nieprzydatny

do obliczeń, a po sformatowaniu standardowym liczba sformatowana pozostaje liczbą,
przydatną w obliczeniach.

Przykład 6.18. Wyświetlanie liczb ze stałą liczbą cyfr znaczących

Naszym celem jest wyświetlanie liczb ze stałą liczbą cyfr znaczących, niezależnie od
położenia przecinka dziesiętnego. W Excelu nie ma formatu liczbowego, pozwalającego
zrealizować to zadanie.

Powiedzmy, że mamy wyświetlać zawsze 7 cyfr znaczących. Rozwiązanie jest widoczne
na rysunku 6.23.

Rysunek 6.23.
Użycie funkcji
ZAOKR.DO.TEKST
do wyświetlania stałej
liczby cyfr znaczących

Wystarczy przeanalizować działanie formuły w komórce

— ta sama formuła z adre-

sami względnymi została skopiowana w dół kolumny, więc działanie formuł w komór-
kach położonych poniżej jest analogiczne do działania formuły w

Formuła wpisana do

ma postać:

=ZAOKR.DO.TEKST(A1;
JEŻELI(MODUŁ.LICZBY(A1)<1;

166

Część II

♦ Funkcje

6-(ZAOKR.DO.CAŁK(LOG(A1))));

PRAWDA)

Funkcja

ZAOKR.DO.TEKST

pobiera wartość z

i zamienia ją na tekst z liczbą przecinków

wyznaczoną przez drugi argument, czyli wyrażenie

JEŻELI(MODUŁ.LICZBY(A1)<1;6;6-(ZAOKR.DO.CAŁK(LOG(A1))))

Ponieważ trzecim argumentem jest

PRAWDA

, tysiące w części całkowitej nie będą oddzie-

lane spacją.

Jak widać, jedyny kłopot sprawia zrozumienie działania wyrażenia, będącego drugim
argumentem funkcji

ZAOKR.DO.TEKST

. Gdy

MODUŁ.LICZBY(A2)<1

(wartość jest ułamkiem),

funkcja

JEŻELI

zwraca drugi argument, czyli

, i ułamek jest wyświetlany z zerem przed

przecinkiem dziesiętnym i sześcioma miejscami dziesiętnymi po przecinku, jak w komór-
kach

na rysunku 6.23.

Jeśli

MODUŁ.LICZBY(A2)

nie jest mniejszy od

, funkcja

JEŻELI

zwraca wynik obliczony

przez wyrażenie

6-(ZAOKR.DO.CAŁK(LOG(A1)))

. Kolejne etapy jego działania pokazuje

rysunek 6.24. Dla obeznanych z matematyką wyjaśnienie to jest zbędne, ale książka jest
również dla tych, którzy dawno zapomnieli, czym jest logarytm.

Rysunek 6.24.
Kolejne etapy
wyliczania liczby
miejsc po przecinku
dziesiętnym

Funkcje logarytm dziesiętny (

LOG

) i moduł liczby (

MODUŁ.LICZBY

) zostały opisane w dal-

szej części książki w rozdziale o funkcjach matematycznych. Logarytm dziesiętny to
wykładnik potęgi, do której należy podnieść podstawę, aby otrzymać liczbę logarytmo-
waną, co dla „niematematyków” brzmi strasznie, ale jest w istocie proste, np.

LOG(0,1)=

-1

, gdyż

-1

=100

LOG(10 000)=4

, gdyż

=10 000

itd.

Należy pamiętać, że wyniki funkcji

ZAOKR.DO.TEKST

, pokazane na rys. 6.24 w kolum-

nie

i na rysunku 6.24 w kolumnie

, są tekstami, a nie liczbami nie przeznaczonymi

do obliczeń

ZASTĄP — REPLACE

Funkcja wbudowana, dostępna w VBA.

Czasami Excel traktuje w obliczeniach postać tekstową liczby jak liczbę, co niekiedy powoduje błędy.
Używanie tekstów w obliczeniach wymaga ostrożności i doświadczenia.

Rozdział 6.

♦ Funkcje tekstowe

167

Zastępuje część ciągu tekstowego innym ciągiem tekstowym, zaczynając od wyznaczo-
nego znaku zamienia określoną liczbę znaków.

Składnia:

stary_tekst

— tekst, w którym należy zastąpić niektóre znaki,

liczba_początkowa

— pozycja znaku w tekście

stary_tekst

, od której należy

rozpocząć wstawianie nowego tekstu,

liczba_znaków

— liczba znaków w tekście

stary_teks

, które mają być zastąpione,

nowy_tekst

— tekst, który zastąpi wyznaczoną część tekstu

stary_tekst

Przykład 6.19. Wpisywanie czasu bez dwukropka
oddzielającego minuty i sekundy

Osoby wpisujące wiele danych liczbowych, np. numerów kont bankowych, sum pienięż-
nych, czasu (godzin i minut) itp., używają zwykle klawiatury numerycznej. Wpisywanie
dwukropka oddzielającego godziny i minuty jest wtedy niewygodne z powodu pozycji
jego klawisza na klawiaturze i konieczności dodatkowego naciśnięcia klawisza Shift.
W znacznym stopniu spowalnia to pracę. Przy wpisywaniu czasu dwukropek można
pomijać, po czym jednym ruchem wstawić go w całym zakresie, np. w całej kolumnie.
Potrzebna procedura została opisana poniżej i pokazana na rysunku 6.25.

Rysunek 6.25.
Wstawianie dwukropka
oddzielającego
godziny i minuty
w celu otrzymania
standardowego czasu

Kolumnie

, do której będzie wpisywany tekst, nadaj format tekstowy, po czym

wpisz dane, nie pomijając zer wiodących

W kolumnie

wpisz formułę pokazaną na rysunku 6.25 na pasku edycji.

Ponieważ formuła w kolumnie

zwraca kolejną liczbę czasu (ułamek doby),

nadaj komórkom tej kolumny format czasu.

Formuła

ZASTĄP(A1;3;0;":")

do tekstu pobranego z

wstawia na trzecim miejscu znak

dwukropka (

”:”

). Właściwie nie jest to zastępowanie, lecz wstawienie, gdyż zgodnie

z argumentem trzecim liczba zastępowanych znaków wynosi

Gdyby nie został użyty format tekstowy, zera wiodące byłyby pomijane i po wpisaniu do komórki
np.

0512

zobaczylibyśmy w niej liczbę

512

168

Część II

♦ Funkcje

Wynikiem jest tekst (

jest to

”12:03”

), który funkcja

CZAS.WARTOŚĆ

zamienia na kolejną

liczbę czasu (ułamek doby). Po nadaniu kolumnie B formatu czasowego widzimy wynik
pokazany na rysunku 6.25.

ZŁĄCZ.TEKSTY — CONCATENATE

Funkcja wbudowana, niedostępna w VBA.

Ciągi tekstowe, podane jako argumenty, łączy w jeden ciąg tekstowy.

Składnia:

tekst1; tekst2;...tekst30

— kolejne elementy tekstowe do połączenia w jeden

ciąg tekstowy. Wszystkie, poza pierwszym, są opcjonalne; maksymalnie może
ich być 30. Elementami tekstowymi mogą być ciągi tekstowe, liczby lub
odwołania do pojedynczych komórek.

Zamiast funkcji

ZŁĄCZ.TEKSTY

można stosować operator łączenia tekstów

, opisany

w rozdziale 1. w podrozdziale „Operator konkatenacji”. Stosowanie operatora

jest

wygodniejsze — tworzone za jego pomocą formuły są krótsze od tworzonych z użyciem
funkcji

ZŁĄCZ.TEKSTY

Operatora łączenia tekstów używaliśmy już w przykładach 4.19 i 6.17. W obu przypad-
kach można go zastąpić funkcją

ZŁĄCZ.TEKSTY

ZNAJDŹ — FIND

Funkcja wbudowana, dostępna w VBA.

Zwraca pozycję (numer znaku, licząc od lewej), na której po raz pierwszy szukany
znak lub ciąg znakowy (

szukany_tekst

) wystąpił w tekście przeszukiwanym (

obej-

mujący_tekst

Od funkcji

SZUKAJ TEKST

różni się odróżnianiem liczb wielkich od małych oraz niedo-

puszczaniem do stosowania znaków zastępczych.

Składnia:

Rozdział 6.

♦ Funkcje tekstowe

169

szukany_tekst

— tekst, którego szukamy, lub odwołanie do komórki

zawierającej tekst szukany,

w_tekście

— tekst (odwołanie do komórki zawierającej tekst), w którym

szukany_tekst

jest szukany,

liczba_początkowa

— numer znaku w argumencie

w_tekście

, od którego ma

się zacząć przeszukiwanie. Pominięcie oznacza użycie domyślnej wartości

Należy pamiętać, że funkcja

ZNAJDŹ

przeszukując tekst, odróżnia litery wielkie od małych,

gdy argument

szukany_tekst

ma wartość

(pusty tekst), odszukuje

w przeszukiwanym tekście znak, którego pozycję określa

liczba_początkowa

(lub domyślnie 1), czyli po prostu zwraca wartość argumentu

liczba_początkowa

(podaną lub domyślną),

nie pozwala na stosowanie w argumencie

szukany_tekst

znaków zastępczych:

znaku zapytania (

) i gwiazdki (

zwraca błąd

#ARG!

, jeżeli argument

szukany_tekst

nie zostanie znaleziony,

zwraca błąd

#ARG!

, jeśli argument

liczba_początkowa

nie jest większy niż

0 lub jest większy niż długość argumentu

w_tekście

zwraca błąd

#ARG!

przy szukaniu w tekście pustym (gdy

w_tekście

jest pusty).

Przykład 6.20. Rozdzielanie imion i nazwisk na oddzielne kolumny

Otrzymaliśmy zapisaną w jednej kolumnie listę nazwisk i imion wielu osób. Naszym za-
daniem jest oddzielenie imion od nazwisk i zapisanie ich w oddzielnych kolumnach.

Rozwiązanie zostało pokazane na rysunku 6.26.

Rysunek 6.26.
Funkcja ZNAJDŹ
pozwala na ustalanie
liczby imienia
i nazwiska

Przyjrzyjmy się formule pobierającej imię, zapisanej w komórce B2:

=PRAWY(A2;DŁ(A2)-ZNAJDŹ(" ";A2))

ZNAJDŹ(" ";A2)=9

określa pozycję spacji oddzielającej w

nazwisko

od imienia (ponieważ trzeci argument jest pominięty, wyszukiwanie
rozpoczyna się domyślnie od pierwszego znaku),

DŁ(A2)-ZNAJDŹ(" ";A2)=12-9=3

decyduje, ile znaków z tekstu w

ma pobrać

funkcja

PRAWY

na końcu

PRAWY(A2;DŁ(A2)-ZNAJDŹ(" ";A2))=PRAWY(A2;3)="Jan"

pobiera imię

z komórki

170

Część II

♦ Funkcje

W analogiczny sposób można zanalizować działanie, wpisanej do

, formuły

=LEWY(A2;

ZNAJDŹ(" ";A2)-1)

ZNAK — CHAR

Funkcja wbudowana, niedostępna w VBA.

Zwraca znak odpowiadający kodowi określonemu przez podaną liczbę.

Składnia:

liczba

— liczba od 1 do 255 będąca kodem znaku w używanym na danym

komputerze zestawie znaków.

W przypadku podania liczby spoza dopuszczalnego zakresu funkcja

ZNAK

zwraca war-

tość błędu

#ARG!

Przykład 6.21. Każde słowo w nowej linii

W kolumnie

mamy teksty złożone z kilku słów. Chcemy tak zmienić tekst i (lub) for-

matowanie, aby każde słowo znalazło się w nowej linii.

Można to zrobić ręcznie, kasując spację znajdującą się po każdym słowie i używając
kombinacji klawiszy Alt+Enter. Nie stanowi to problemu, jeżeli takich tekstów mamy
pięć; gorzej, gdy jest ich 5 tysięcy.

Należy użyć formuły pokazanej na rysunku 6.27 na pasku edycji, która zamienia spacje
na znaki nowej linii.

Rysunek 6.27.
Zamiana spacji
na znaki nowej linii

Jak widać, znaki nowej linii są widoczne w postaci kwadracików, ale nie powodują zawi-
nięcia tekstu. Należy zaznaczyć komórki zawierające te znaki (kolumnę

na rysunkach

6.27 i 6.28) i włączyć zawijanie tekstu w komórkach przez uaktywnienie opcji For-
mat/Komórki/Wyrównanie/Sterowanie tekstem: Zawijaj tekst. Rysunek 6.28 przedstawia
wynik tych działań.

Rysunek 6.28.
Każde słowo w nowej
linii, czyli zadanie
zostało wykonane

Wyszukiwarka

Podobne podstrony:

więcej podobnych podstron