Zadania testowe na temat: „Arkusze kalkulacyjne Przewyższać ».
1. Arkusz kalkulacyjny to:
a) program aplikacyjny do przechowywania i przetwarzania danych ustrukturyzowanych w formie tabeli;
b) program aplikacyjny do przetwarzania tablic kodowych;
c) urządzenie komputer osobisty zarządzając swoimi zasobami podczas przetwarzania danych w formie tabelarycznej;
d) program systemowy zarządzający zasobami komputera osobistego podczas pracy z tabelami.
2. Podstawowa różnica między arkuszem kalkulacyjnym a zwykłym arkuszem polega na tym, że:
a) możliwość przetwarzania danych ustrukturyzowanych w formie tabeli;
b) możliwość automatycznego przeliczenia danych określonych wzorami w przypadku zmiany danych pierwotnych;
c) możliwość wizualnego przedstawienia powiązań pomiędzy przetwarzanymi danymi;
d) możliwość przetwarzania danych prezentowanych w postaci ciągów znaków różne rodzaje.
3. Wiersze arkusza kalkulacyjnego:
a) są nazwane przez użytkownika w sposób dowolny;
b) są oznaczone literami alfabetu rosyjskiego A...Z;
c) są oznaczone literami Alfabet łaciński;
d) są numerowane.
4.Adres komórki w Excelu składa się z:
a) nazwa pliku;
b) dany zestaw znaków;
c) nazwę kolumny i numer wiersza, na przecięciu której znajduje się komórka;
d) numer wiersza i nazwę kolumny, na przecięciu której znajduje się komórka.
5. Jeśli w Excelu aktywujesz komórkę i wciśniesz klawisz Delete, to:
a) zawartość komórki zostanie usunięta;
b) format komórki zostanie wyczyszczony;
c) komórka zostanie usunięta;
d) nazwa komórki zostanie usunięta.
6. Informacje zawarte w tabeli prezentowane są w sposób:
a) pliki; b) zapisy; c) tekst, liczby, wzory.
7. Szybka konwersja danych na arkusz dzieje się z powodu...
a) dystrybucja informacji pomiędzy komórkami;
b) obecność formuł łączących dane;
c) szybka praca procesora.
8.Aktywna komórka w Excelu to:
a) komórka o adresie A1; b) komórka wyróżniona ramką; c) komórkę, w której wprowadzane są dane.
9.Zakres komórek w arkuszu kalkulacyjnym wynosi...
a) zbiór wszystkich wypełnionych komórek tabeli;
b) zbiór wszystkich puste komórki;
c) zbiór komórek tworzących prostokątny obszar;
d) zbiór komórek tworzących obszar dowolna forma.
10. Podaj niepoprawną formułę dla komórki F1
a) =A1+B1*D1; b) =A1+B1/F1; c) =C1.
11. Podaj niepoprawną formułę do zapisania w komórce D1
a) =2A1+B2; b) =A1+B2+C3; c) =A1-C3; d) wszystkie wzory są dopuszczalne.
12. Nie możesz usuwać w arkuszu kalkulacyjnym.
a) linia; b) kolumna; c) nazwa komórki; d) zawartość komórki.
13. W arkuszu kalkulacyjnym podświetlona jest grupa komórekC 3: F 10. Ile komórek znajduje się w tej grupie?
a) 21; b) 24; c) 28; d) 32.
14. Wyrażenie , zapisywany zgodnie z zasadami przyjętymi w matematyce, w arkuszu kalkulacyjnym ma postać:
a)3*(A1+B1)/(5*(2*B1–3*A2));
b)3(A1+B1)/5(2B1–3A2);
c)3* (A1+B1)/ 5* (2* B1–3* A2);
d)3(A1+B1)/(5(2B1–3A2)).
15. Podczas przenoszenia lub kopiowania w arkuszu kalkulacyjnym linki absolutne:
a) nie zmieniać;
b) ulegają przekształceniu niezależnie od nowej pozycji wzoru;
c) ulegają przekształceniu w zależności od nowej pozycji wzoru;
d) są przekształcane w zależności od długości wzoru.
16 . Komórka arkusza kalkulacyjnego H5 zawiera formułę =$B$5*V5. Jaka formuła zostanie z niego uzyskana po skopiowaniu do komórki H7:
a)=$B$7*V7; b) = $B$5*V5; c)=5 B$*V7; d)=B$7*V7.
17. W arkuszu kalkulacyjnym komórka A1 zawiera liczbę 10, B1 zawiera formułę =A1/2, a C1 zawiera formułę =SUMA(A1:B1). Jaka jest wartość C1:
a) 10; b) 15; o 2; d)150.
18. Schemat jest następujący:
a) forma graficzna wartości liczbowe, co ułatwia interpretację danych liczbowych;
b) harmonogram;
c) przygotowany stół;
19. Wykres słupkowy to:
a) schemat, na którym indywidualne wartości reprezentowane przez paski o różnej długości umieszczone wzdłuż osi X;
b) diagram, którego poszczególne wartości są reprezentowane przez punkty w kartezjańskim układzie współrzędnych;
c) diagram, na którym poszczególne wartości są reprezentowane przez pionowe paski o różnej wysokości;
d) diagram przedstawiony w postaci koła podzielonego na sektory, w którym dozwolony jest tylko jeden wiersz danych.
20. Histogram jest najbardziej odpowiedni dla:
a) do wyświetlania rozkładów;
b) porównania różne elementy grupy;
c) wyświetlania dynamiki zmian danych;
d) aby wyświetlić określone współczynniki różne znaki.
Odpowiedzi:
Edytor tekstu ma kilka wbudowanych programów, które implementują różne obiekty w tworzonym dokumencie. Dostęp do nich jest możliwy poprzez Wstaw/Obiekt.... Przyjrzyjmy się dwóm najpopularniejszym z nich: kreatorowi wykresów i edytorowi formuł.
Budowanie wykresów
Aplikacja służy do tworzenia diagramów "Wykres Microsoftu Wykres".
Zaznacz komórki w tabeli, których dane zostaną użyte do zbudowania wykresu. Treść Górna linia a lewa kolumna wybranego obszaru służy do etykietowania znaczników na osiach współrzędnych.
Poprzez menu Wstaw/Obiekt.../Utwórz na liście „Typ obiektu” wybierz „Wykres graficzny Microsoftu”, po czym wyświetli się tabela z danymi i diagramem.
Ustaw etykiety w tabeli: etykiety z pierwszego wiersza tabeli, która się pojawi, służą do oznaczenia osi poziomej, a z pierwszej kolumny - do legendy. Legenda to rysunek objaśniający znajdujący się po prawej stronie diagramu.
Edytuj diagram. Aby to zrobić, kliknij prawym przyciskiem myszy edytowany element diagramu - pojawi się menu umożliwiające jego edycję.
Wyjdź z programu Microsoft Graph, klikając w oknie głównym poza wykresem.
Przesuń diagram w wybrane miejsce w dokumencie i dostosuj jego wymiary.
Przykład
Zbudujmy diagram ilustrujący dynamikę przychodów (patrz tabela powyżej). Aby to zrobić, wybierz w tabeli przedział A2:F5 i załaduj „Microsoft Graph”. Należy pamiętać, że w tym zakresie znajduje się wiersz z nazwami miesięcy oraz kolumna z nazwami miast. Używane są do etykiet osi poziomych i legend.
Jeśli chcesz edytować wykres, kliknij prawym przyciskiem myszy wewnątrz wykresu i wybierz pozycję menu „Opcje wykresu”. Otworzy się okno Opcje wykresu, umożliwiające wprowadzenie większości poprawek. Przykładowo, aby ustawić etykiety na osi poziomej należy otworzyć zakładkę „Osie” i zaznaczyć pole wyboru „Oś X (kategorie)” oraz etykietę „Automatycznie”. W przypadku konieczności zmiany typu wykresu należy wybrać z menu opcję „Typ wykresu”.
Aby ustawić orientację pionową etykiet pod osią X, kliknij prawym przyciskiem myszy dowolną z etykiet, wybierz menu „Format osi” i w zakładce „Wyrównanie” ustaw orientację pionową.
Jeśli przy aktywnym obiekcie „Microsoft Graph” przejdź do menu Dane/wiersze tworzą kolumny, wówczas na osi X zostaną wyświetlone dane z kolumn tabeli.
Wszystkie czynności związane z edycją wykresu można wykonać poprzez pasek menu, który zastępuje menu główne podczas działania programu Microsoft Graph.
Ostatnim etapem edycji diagramu jest zmiana jego rozmiaru i umieszczenie go w wybranym miejscu strony.
Praca z edytorem formuł
Edytor formuł „Microsoft Equation” to program instalowany podczas instalacji Edytor słów na żądanie użytkownika. Edytor ma duży zestaw symbole matematyczne i pozwala przedstawić dość złożone formuły. W przeciwieństwie do edytora TEX-a, w którym formuła jest najpierw kodowana, a następnie odtwarzana przez specjalny program, „Microsoft Equation” pozwala zobaczyć formułę tak, jak jest ona zapisana. Po zapisaniu wzoru można zmieniać jego wymiary jak w przypadku zwykłego rysunku.
Aby napisać formułę należy ustawić kursor w odpowiednim miejscu i wejść do menu Wstaw/Obiekt/Microsoft Równanie 3.0. W takim przypadku powinna pojawić się ramka do wpisania formuły oraz panel edytora formuł zawierający dwa rzędy przycisków. Decyduje górny rząd przycisków paleta symboli, niżej - paleta szablonów. Formułę można wypełnić, klikając poza polem wejściowym.
Ogólna kolejność zbioru formuł polega na wybraniu żądanego elementu w panelu edytora formuł i określeniu go w menu, które się pojawi. Znaki zwykłe wpisuje się z klawiatury w wyznaczone pola. Podczas przechodzenia z jednego pola do drugiego oraz podczas wpisywania nowych pól należy monitorować położenie i wielkość kursora. Na przykład kursor dla ułamek wspólny większy niż jego licznik lub mianownik. W ten sposób pokazane jest miejsce na wprowadzenie kolejnego znaku.
Wstawianie spacji we wzorach nie można wykonać poprzez zwykłe naciśnięcie odpowiedniego klawisza. W Palecie Znaków dostępnych jest kilka rodzajów spacji. Jeśli musisz często wstawiać spacje, wygodnie jest użyć kombinacji klawiszy pokazanych w tabeli.
Wyrównanie formuły może być wymagane np. po zmianie rozmiaru, mimo że w większości przypadków edytor formuł sam dopasowuje formułę względem linii, w której jest zapisana. Aby wyrównać całą formułę lub jej część należy wywołać formułę do edycji poprzez dwukrotne kliknięcie myszką, zaznaczyć część do wyrównywania i nacisnąć odpowiednią ilość razy kombinację klawisza Ctrl i jednego z klawiszy kursora , w zależności od kierunku wyrównania. Każde kliknięcie przesuwa wybraną część o 1 piksel.
Pytania kontrolne
- Jak uzyskać dostęp do wbudowanych programów, które implementują różne obiekty w utworzonym dokumencie?
- Jakiej aplikacji używasz do tworzenia diagramów?
- Jak wybrać aplikację do tworzenia wykresów?
- Jak zbudować diagram?
- Co to jest legenda na wykresie?
- Jak ustawić etykiety dla oznaczeń i legend osi poziomych?
- Jak edytować diagram?
- Jakie są możliwości edytora formuł?
- Jak napisać formułę w dokumencie?
- Jaka jest ogólna kolejność zbioru formuł?
- Jak wstawić spację do formuły?
- Jak wyrównać formułę?
To jest rozdział z książki: Michael Girvin. Ctrl+Shift+Enter. Opanowanie formuł tablicowych w programie Excel.
Ta notatka jest przeznaczona dla naprawdę zainteresowanych złożone formuły szyk. Jeśli potrzebujesz tylko raz wyodrębnić listę unikalnych wartości, znacznie łatwiej jest skorzystać z Filtra zaawansowanego lub tabeli przestawnej. Główne zalety stosowania formuł to: automatyczna aktualizacja przy zmianie/dodaniu danych źródłowych lub kryteriów wyboru. Przed lekturą warto odświeżyć pamięć o pomysłach zawartych w poprzednich materiałach:
- (Rozdział 11);
- (Rozdział 13);
- (Rozdział 15);
- (Rozdział 17).
Ryż. 19.1. Pobierz unikalne rekordy za pomocą opcji Zaawansowany filtr
Pobierz notatkę w formacie lub, przykłady w formacie
Pobieranie unikalnej listy z pojedynczej kolumny za pomocą opcji Zaawansowany filtr
Na ryc. Rysunek 19.1 przedstawia zbiór danych (zakres A1:C9). Twoim celem jest zdobycie listy unikalnych torów wyścigowych. Ponieważ musisz zapisać oryginalne dane, nie możesz skorzystać z tej opcji Usuń duplikaty(menu DANE –> Pracować z dane –> Usuń duplikaty). Ale możesz użyć Zaawansowany filtr. Aby otworzyć okno dialogowe Zaawansowany filtr, przejdź przez menu DANE –> Sortowanie i filtrowanie –> Dodatkowo lub naciśnij i przytrzymaj klawisz Alt, a następnie naciśnij S, L (w programie Excel 2007 lub nowszym).
W otwartym oknie dialogowym Zaawansowany filtr(Rys. 19.1) ustaw opcję skopiuj wynik do innej lokalizacji, zaznacz pole wyboru Tylko unikalne wpisy, określ obszar, z którego zostaną wyodrębnione unikalne wartości ($B$1:$B$9) oraz pierwszą komórkę, w której zostaną umieszczone wyodrębnione dane ($E$1). Na ryc. Rysunek 19.2 przedstawia wynikową listę unikatową (zakres E1:E6). Jeśli nie podasz nazwy pola w Oryginalny asortyment Okno dialogowe Zaawansowany filtr(zamiast wpisywać $B$2:$B$9 na rysunku 19.1), Excel potraktuje pierwszy wiersz zakresu jako nazwę pola, co grozi powstaniem duplikatu. Na ryc. Rysunek 19.3 pokazuje jedno z wielu możliwych zastosowań listy unikalnej.
Pobranie unikalnej listy na podstawie kryterium za pomocą opcji Zaawansowany filtr
W ostatnim przykładzie pobrałeś unikalną listę z pojedynczej kolumny. Zaawansowany filtr może także pobrać unikalny zestaw rekordów (tzn. całe wiersze z tabeli źródłowej) przy użyciu kryterium. Na ryc. Rysunki 19.4 i 19.5 przedstawiają sytuację, w której należy wyodrębnić unikalne rekordy z zakresu A1:D10, dla których nazwa firmy jest równa ABC. W dalszej części tego rozdziału zobaczysz, jak wykonać to zadanie za pomocą formuły. Jeśli jednak nie potrzebujesz, aby proces był automatyczny, możesz użyć Zaawansowany filtr, co z pewnością jest prostsze niż wzór.
Ryż. 19.4. Potrzebujesz unikalnych rekordów dla firmy ABC; Aby powiększyć obraz, kliknij go prawym przyciskiem myszy i wybierz Otwórz obraz w nowej karcie
Ryż. 19,5. Stosowanie Zaawansowany filtr pobieranie unikalnych rekordów w oparciu o kryteria jest znacznie łatwiejsze niż metoda formuły. Jednak pobrane rekordy nie zostaną automatycznie zaktualizowane w przypadku zmiany kryteriów lub danych źródłowych
Pobieranie unikalnej listy z pojedynczej kolumny przy użyciu tabeli przestawnej
Jeśli korzystasz już z tabel przestawnych, wiesz o tym za każdym razem, gdy wstawisz dowolne pole w tym obszarze Smyczki Lub Kolumny(Rys. 19.6), automatycznie otrzymasz unikalną listę. Na ryc. Rysunek 19.6 pokazuje, jak można szybko utworzyć unikalną listę torów wyścigowych, a następnie policzyć liczbę wizyt na każdym torze. Chociaż tabela przestawna jest przydatna do pobierania unikalnej listy z pojedynczej kolumny, nie będzie przydatna do wyszukiwania unikalnych rekordów na podstawie kryteriów.
Ryż. 19.6. Możesz użyć Stół obrotowy gdy potrzebujesz unikalnej listy i późniejszej kalkulacji na jej podstawie
Wyodrębnianie unikalnej listy z pojedynczej kolumny przy użyciu formuł i kolumny pomocniczej
Użycie kolumny pomocniczej ułatwia pobieranie unikalnych danych niż używanie formuł tablicowych (rysunek 19.7). W tym przykładzie wykorzystano metody, których nauczyłeś się w (Korzystanie z funkcji COUNTIF) i (Korzystanie z kolumny pomocniczej). Jeśli teraz zmienisz dane źródłowe w zakresie B2:B9, formuły automatycznie odzwierciedlą te zmiany w obszarze D15:D21.
Formuła tablicowa: Pobieranie unikalnej listy z pojedynczej kolumny za pomocą funkcji MAŁA
Ponieważ formuły tablicowe użyte w tej sekcji są dość skomplikowane w zrozumieniu, ich tworzenie podzielono na etapy: najpierw fragment zliczający unikalne wartości (rozdział 17); drugi to ekstrakcja danych w oparciu o kryteria (rozdział 15). Na ryc. Rysunek 19.8 pokazuje wzór na obliczanie unikalnych wartości (ponieważ jest to formuła tablicowa, wprowadza się ją naciskając Ctrl+Shift+Enter). Zwróć uwagę na następujące aspekty tej formuły:
- Funkcja FREQUENCY zwraca tablicę liczb (rysunek 19.9): przy pierwszym pojawieniu się toru wyścigowego zwracana jest liczba jego wystąpień w danych oryginalnych; Za każde kolejne pojawienie się toru wyścigowego zwracane jest zero (patrz ). Na przykład Sumner pojawia się na pierwszej i piątej pozycji tablicy. Na pierwszej pozycji funkcja CZĘSTOTLIWOŚĆ zwraca 2 – Łączna Lato w przedziale B2:B9, na piątej pozycji – 0.
- W argumencie umieszczana jest funkcja CZĘSTOTLIWOŚĆ wyrażenie_loga JEŻELI, zatem funkcja JEŻELI zwraca PRAWDA w przypadku wartości innej niż null i FAŁSZ w przypadku wartości zerowej.
- Argument wartość_jeśli_prawda Funkcja JEŻELI zawiera jedynki, więc funkcja SUMA zlicza liczbę takich jedynek.
Ryż. 19.8. W argumencie umieszczana jest funkcja CZĘSTOTLIWOŚĆ wyrażenie_loga Funkcje JEŻELI
Ryż. 19.9. (1) funkcja CZĘSTOTLIWOŚĆ zwraca tablicę liczb; (2) funkcja JEŻELI zwraca 1 dla liczb niezerowych i FAŁSZ dla zer.
Stwórzmy teraz formułę wyodrębnienia unikalnej listy. Na ryc. Rysunek 19.10 przedstawia tablicę względnych pozycji umieszczonych w argumencie szyk funkcje MAŁY.
W poprzednim przykładzie (ryc. 19.9) w argumencie wartość_jeśli_prawda Funkcja JEŻELI umieściła jedynkę, więc funkcja JEŻELI zwróciła jedynki i FAŁSZ. Tutaj (ryc. 19.10) argument wartość_jeśli_prawda zawiera: WIERSZ($B$2:$B$9)-ROW($B$2)+1. Dlatego funkcja JEŻELI (w ramach funkcji SMALL) zwraca względny numer pozycji w zakresie z unikalnym torem wyścigowym lub FAŁSZ w przypadku duplikatów (rysunek 19.11).
Ryż. 11.19. Funkcja JEŻELI zwraca względny numer pozycji w zakresie z unikalnym torem wyścigowym lub FAŁSZ w przypadku duplikatów
Na ryc. 19.12 pokaż wyniki wzoru. Na ryc. Rysunek 19.13 pokazuje, że gdy tylko dane początkowe uległy zmianie, wzory natychmiast odzwierciedliły te zmiany. A co jeśli dodasz nowe wpisy? Następnie zobaczysz, jak tworzyć formuły zakresu dynamicznego.
Ryż. 19.13. Jeśli dane źródłowe ulegną zmianie, formuła zostanie natychmiast zaktualizowana. Filtr i Filtr zaawansowany nie mogą być aktualizowane automatycznie bez pisania kodu VBA
Formuła tablicowa: pobieranie unikalnej listy z pojedynczej kolumny przy użyciu zakresu dynamicznego
Rozwińmy ostatni przykład o to, czego nauczyłeś się o formułach używających określonych nazw w oparciu o zakresy dynamiczne (). Na ryc. 19.14 pokazuje wzór na określenie nazwy Trasa. W tej formule zakłada się, że nigdy nie wprowadzisz wpisu po linii 51.
Ryż. 19.14. Definicja nazwy Trasa w oparciu o formułę
Po zdefiniowaniu nazwy można jej używać w dowolnej formule. Na ryc. Rysunek 19.15 pokazuje, jak używać nazwy do zliczania liczby unikalnych wartości (porównaj z rysunkiem 19.8). I na ryc. Rysunek 19.16 przedstawia formułę, która wyodrębnia same unikalne wartości z listy torów wyścigowych. Zauważ, że zamiast fragmentu zakres<>»» (tak jak to było na rys. 19.8 i 19.10) wykorzystywana jest funkcja ITEXT (dowolny tekst zwróci wartość PRAWDA). Jeśli w przypadku korzystania z ETEKST wprowadzisz liczbę (jak w komórce B11) lub inny tekst niebędący tekstem, formuła zignoruje tę wartość. Na ryc. Rysunek 19.17 pokazuje, że formuła automatycznie pobiera nowe nazwy ścieżek, ignorując liczby.
Ryż. 19.16. Ekstrakcja unikalna nazwaślady oparte na zakresie dynamicznym
Tworzenie unikalnej formuły wartości dla listy rozwijanej
Na podstawie omówionego przykładu zdefiniujmy drugie imię - Lista tras, również oparty na zakresie dynamicznym, ale teraz odnoszący się do listy unikalnych śladów (zakres E5:E14, rys. 19.18). Ponieważ zakres E5:E14 zawiera tylko tekst i puste wartości (ciągi testowe o zerowej długości - „”), argument wartość_wyszukiwania Funkcje DOPASUJ mogą używać symboli wieloznacznych *? (co oznacza przez co najmniej, jeden znak). I w kłótni typ_dopasowania Funkcja DOPASUJ powinna używać wartości -1, która znajdzie ostatni element tekstowy w kolumnie zawierającej co najmniej jeden znak. Jak pokazano na ryc. 19.18, wówczas możesz wpisać w polu konkretną nazwę Źródło okno Walidacja wprowadzonych wartości(Aby uzyskać więcej informacji na temat tworzenia listy rozwijanej, zobacz). Lista rozwijana może się rozszerzać i kurczyć w miarę dodawania lub usuwania nowych danych w kolumnie B.
Jeśli symbole wieloznaczne powinny być traktowane jak zwykłe znaki
Jak nauczyłeś się w , czasami symbole wieloznaczne należy traktować jak znaki. Na ryc. Rysunek 19.18 pokazuje, jak można modyfikować formuły dla takich przypadków. Przed zakresem argumentów dołączasz tyldę wartość_wyszukiwania MATCH i dołącz pusty ciąg znaków na końcu zakresu argumentu oglądana_tablica.
Użyj kolumny pomocniczej lub formuły tablicowej, aby pobrać unikalne rekordy na podstawie kryteriów
Na początku notatki wykazano, że w celu wyszukiwania unikalnych rekordów w oparciu o kryteria, Zaawansowany filtr. Jeśli jednak potrzebujesz natychmiastowej aktualizacji, możesz użyć kolumny pomocniczej (rysunek 19.20) lub formuł tablicowych (rysunek 19.21).
Dynamiczne formuły do wyodrębniania nazw klientów i wielkości sprzedaży
Formuły pokazano na ryc. 19.22. Na przykład, jeśli dodasz nowy wpis TTSamochody ciężarowe do linii 17 , formuła SUMIF w komórce F15 automatycznie doda nową wartość. Jeśli dodasz nowego klienta w kolumnie B, od razu pojawi się on w kolumnie E, a formuła SUMA w kolumnie F pokaże nową sumę.
Ryż. 19.22. Użycie konkretnej nazwy i dwóch formuł tablicowych w celu wyodrębnienia unikalnych klientów i wielkości sprzedaży
Należy pamiętać, że funkcja SUMA.JEŻELI ma argument suma_zakres zawiera jedną komórkę – $C$10. Oto, co na ten temat mówi odwołanie do formuły SUMSLI: argument suma_zakres może nie mieć tego samego rozmiaru co argument zakres. Podczas określania rzeczywistych komórek do zsumowania lewa górna komórka argumentu jest używana jako komórka początkowa suma_zakres, a następnie sumowane są komórki części zakresu odpowiadającej wielkości argumentowi zakres. Wzdłuż kolumn kopiowane są formuły wpisane w komórkach E15 i F15.
Sortowanie wartości liczbowych
Formuły sortowania liczb są dość proste, ale formuły sortowania danych mieszanych są niesamowicie złożone. Dlatego jeśli nie potrzebujesz natychmiastowej aktualizacji, lepiej obejść się bez formuł, korzystając z opcji Sortowanie. Na ryc. Rysunek 19.23 przedstawia dwie formuły sortowania.
Na ryc. Rysunek 19.24 pokazuje, jak można użyć kolumny pomocniczej do sortowania liczb. Ponieważ funkcja RANK nie sortuje jak liczby (nadając im tę samą rangę), dodano funkcję LICZ.JEŻELI, aby je rozróżnić. Należy pamiętać, że funkcja LICZ.JEŻELI ma rozszerzony zakres, który zaczyna się o jedną linię wyżej. Jest to konieczne, aby pierwsze pojawienie się dowolnej liczby nie dało wkładu. Drugie pojawienie się liczby zwiększa rangę o jeden. Ta numeracja sekwencyjna ustala kolejność, w jakiej funkcje INDEX i MATCH pobierają rekordy z zakresu A8:B12.
Jeśli stać Cię na utworzenie kolumny pomocniczej w obszarze ekstrakcji danych (zakres A10:A14 na rysunku 19.25), wygodnie jest zastosować opisane powyżej sortowanie liczb w oparciu o funkcję SMALL i na tej podstawie wyodrębnić nazwy za pomocą funkcja tablicowa.
Ryż. 19.25. Jeśli nie możesz użyć kolumny pomocniczej, użyj sortowania na podstawie funkcji SMALL (w komórce A11) i formuły tablicowej (w komórce B11)
Często w biznesie i sporcie konieczne jest wyodrębnienie N najlepsze wartości oraz nazwy powiązane z tymi wartościami. Rozpocznij rozwiązanie od formuły COUNTIF (komórka A11 na rysunku 19.26), która określi liczbę rekordów do wyświetlenia. Zauważ, że argument kryterium w funkcji LICZ.JEŻELI w komórce A11 – więcej lub równe wartość w komórce D8. Pozwala to wyświetlić wszystkie wartości graniczne (w naszym przykładzie, chociaż musimy wyświetlić Top 3, istnieją cztery odpowiednie wartości).
Ryż. 19.26. Wyodrębnianie trzech najlepszych wyników całkowitych i odpowiadających im nazw. Gdy N zmieni się w komórce D8, obszar A15:B21 zostanie zaktualizowany
Sortowanie wartości tekstowych
Jeśli dopuszczalne jest użycie kolumny pomocniczej, zadanie nie jest tak trudne (ryc. 19.27). Obsługa operatorów porównania znaki tekstowe na podstawie kodów numerycznych ASCII przypisanych do znaków. W komórce C3 pierwsza funkcja LICZ.JEŻELI zwraca zero, a druga dodaje jeden. W C4: 2+1, C5: 0+2, C6: 3+1.
Sortuj dane mieszane
Formuła pozwalająca wyodrębnić unikalne wartości z mieszanych danych, a następnie je posortować, jest bardzo duża (rysunek 19.28). Tworząc ją, korzystaliśmy z pomysłów, które napotkaliśmy wcześniej w tej książce. Zacznijmy naukę formuły od sprawdzenia, jak działa standardowa funkcja sortowania w programie Excel.
Excel sortuje wyniki w następne zamówienie: najpierw liczby, potem tekst (w tym ciągi o zerowej długości), FAŁSZ, PRAWDA, wartości błędów w kolejności występowania, puste komórki. Całość sortowania odbywa się zgodnie z kodami ASCII. Istnieje 255 kodów ASCII, każdy odpowiadający liczbie od 1 do 255:
Na przykład liczba 5 odpowiada kodowi ASCII 53, a znak S odpowiada kodowi ASCII 83. Jeśli posortujesz dwie wartości, 5 i S, od najmniejszej do największej, wówczas 5 będzie wyższe niż S, ponieważ 53 to mniej niż 83.
Zbiór danych z zakresu A2:A5 (rys. 29) jest konwertowany na zakres E2:E5 zgodnie z regułami sortowania. Aby lepiej zrozumieć zasady sortowania, rozważ wartości z zakresu C2:C5. Na przykład, jeśli zadasz pytanie „Ile osób przewyższa mnie rangą?” do identyfikatora w komórce A2 (54678) odpowiedź będzie wynosić zero, ponieważ na posortowanej liście identyfikator 54678 będzie na górze. SD-987-56 będzie miał nad sobą trzy identyfikatory. Potrzebujesz formuły, aby uzyskać wartości z zakresu C2:C5.
Aby rozpocząć, wybierz zakres E1:H1 i wpisz =TRANSP(A2:A5) w pasku formuły i wprowadź formułę, naciskając Ctrl+Shift+Enter (ryc. 19.30). Następnie w pasku formuły zaznaczamy zakres E2:H5, wpisujemy =A2:A5>E1:H1 i wprowadzamy formułę naciskając Ctrl+Shift+Enter (rys. 19.31). Na ryc. Rysunek 19.32 przedstawia wynik w postaci prostokątnej tablicy wartości PRAWDA i FAŁSZ, które odpowiadają każdej komórce wynikowej tablicy, jako odpowiedź na pytanie „Czy nagłówek wiersza jest większy niż nagłówek kolumny?”
Ryż. 19.30. Wybierz zakres E1:H1 i wprowadź formuły tablicowe
Ryż. 19.31. W zakresie E2:H5 wprowadź formułę tablicową =A2:A5>E1:H1
Ryż. 19.32. Każda komórka w zakresie E2:H5 zawiera odpowiedź na pytanie „Czy nagłówek wiersza jest większy od nagłówka kolumny?”
Na przykład w komórce E3 pytanie brzmi: SD-987-56 > 54678. Ponieważ 54678 jest mniejsze niż SD-987-56, odpowiedź brzmi PRAWDA. Należy pamiętać, że zakres E3:H3 zawiera trzy wartości PRAWDA i jedną wartość FAŁSZ. Patrząc wstecz na rys. 19.29 widać, że w komórce C3 znajduje się liczba trzy.
Jak pokazano na rysunkach 19.33 i 19.34, wartości PRAWDA i FAŁSZ można przekonwertować na jedynki i zera, dodając podwójną negację do formuły tablicowej. Ponieważ oryginalna tablica (E2:H5) ma wymiary 4x4 i chcesz, aby wynikiem była tablica 4x1, użyj funkcji MULTIPLE (patrz rysunek 19.35 i ). Funkcja MULTIPLE jest funkcją tablicową, dlatego wejdź do niej, naciskając Ctrl+Shift+Enter (ryc. 19.36). Teraz zamiast korzystać z zakresu E2:H5, dodaj odpowiednie elementy do formuły (rysunek 19.37).
Ryż. 19.36. Wybierając zakres C2:C5 i wpisując funkcję tablicy MULTIPLE, otrzymasz kolumnę liczb, która poinformuje Cię, ile identyfikatorów na posortowanej liście jest wyższych od wybranego
Ryż. 19.37. Zamiast korzystać z zakresu pomocniczego E2:H5, wewnątrz wzoru dodawane są odpowiednie elementy
Na ryc. Rysunek 19.38 pokazuje, jak można zastąpić tablicę stałych fragmentem ROW($A$2:$A$5)^0.
Ryż. 19.39. Aby poradzić sobie z potencjalnymi pustymi komórkami, wszystkie wystąpienia A2:A5 należy uzupełnić sprawdzeniem JEŻELI(A2:A5)<>„”, A2:A5); funkcja WIERSZ nie wymaga takiego dodatku, ponieważ funkcja działa z adresem komórki, a nie z jej zawartością
Ponieważ ostateczna formuła zostanie użyta gdzie indziej, wszystkie zakresy należy ustawić jako bezwzględne (rysunek 19.40). Na ryc. Rysunek 19.41 pokazuje otrzymane wartości.
Ryż. 19.40. Zakresy A2:A5 zamienione na bezwzględne
Ponieważ element ten zostanie później użyty dwukrotnie, możesz zapisać go pod określoną nazwą. Jak pokazano w oknie dialogowym (ryc. 19.42), formule nadano nazwę SZB - Ile wartości jest większych.
- Argument szyk Funkcja INDEX odnosi się do zakresu źródła A2:A5.
- Pierwsza funkcja MATCH poinformuje funkcję INDEX o względnej pozycji elementu w tablicy A2:A5.
- Podczas gdy argument wartość_wyszukiwania Funkcja DOPASUJ pozostaje pusta.
- Zdefiniowana nazwa (DSN) w argumencie oglądana_tablica pozwoli ci najpierw uzyskać dostęp do elementu, który ma wartość 0, następnie 2 i na końcu 3.
- Zero w argumentacji typ_dopasowania określa dokładne dopasowanie, co wyeliminuje potrzebę odwoływania się do duplikatów.
Ryż. 19.43. Uruchamiasz formułę wyodrębniającą i sortującą dane w komórce A11. Argument wartość_wyszukiwania Funkcje MATCH pozostaw na razie puste
Zanim stworzysz argument wartość_wyszukiwania Funkcje SZUKAJ, pamiętaj, czego tak naprawdę potrzebujesz. Istnieją trzy unikalne identyfikatory, które należy posortować, dlatego w argumencie potrzebne będą trzy liczby wartość_wyszukiwania podczas kopiowania formuły. Liczby te pozwolą Ci znaleźć względną pozycję w tablicy A2:A5, którą musisz podać funkcji INDEX:
- W komórce A11 funkcja DOPASUJ zwróci 0, co odpowiada względnej pozycji 1 w zdefiniowanej nazwie SZB.
- Po skopiowaniu formuły do komórki A12 funkcja DOPASUJ powinna zwrócić liczbę 2 i względną pozycję = 4 w MSB.
- W komórce A13 funkcja MATCH powinna zwrócić 3 i względną pozycję = 2 w obrębie MSB.
Obraz pojawia się, gdy pomyślisz o kłótni wartość_wyszukiwania podczas kopiowania formuły w dół powinien pojawić się monit: „Podaj minimalną wartość w ramach konkretnej nazwy SZB, która nie została jeszcze wykorzystana”. Jak pokazano na ryc. 19.44 element formuły MIN(IF(END(MATCH($A$2:$A$5,A$10:A10,0)),MSB)) zwraca minimalną wartość podczas kopiowania formuły, dokładnie odpowiadając na zapytanie. Dzieje się tak dlatego, że fragment UND(MATCH($A$2:$A$5,A$10:A10,0)) porównuje dwie listy (patrz ). Zwróć uwagę na rozszerzający się zakres A$10:A10 w argumencie oglądana_tablica. W komórce A11 kombinacja UNM i MATCH pomaga wyodrębnić wszystkie unikalne liczby z SZB i dostarczyć je do funkcji MIN. Po skopiowaniu formuły do komórki A12 identyfikator pobrany w komórce A11 ponownie pojawi się w rozwiniętym zakresie i ponownie zostanie znaleziony w zakresie $A$2:$A$5. Jednak UND zwraca FAŁSZ, a z SZB nie zostanie pobrana wartość 0. Aby to zobaczyć, wprowadź formułę tablicową na rysunku 19.44, naciskając Ctrl+Shift+Enter i skopiuj ją w dół.
Ryż. 19.44. Element formuły w argumencie wartość_wyszukiwania Funkcja MATCH odpowiada żądaniu: „Podaj minimalną wartość w ramach konkretnej nazwy SZB, która nie została jeszcze wykorzystana”
Na ryc. 19.45 pokazuje to w argumencie oglądana_tablica Druga funkcja MATCH rozszerzyła zakres A$10:A10 do A$10:A11. Aby zrozumieć, jak działa ta formuła, zaznaczaj kolejno jej fragmenty i kliknij F9 (ryc. 19.46–19.49).
Ryż. 19.45. Rozszerzający się zakres A$10:A11 zawiera teraz (w komórce A12) pierwszy identyfikator (54678)
Ryż. 19.46. Kombinacja funkcji UNM i drugiej funkcji MATCH dostarcza tablicę wartości logicznych; dwie wartości FALSE wykluczają wartości null ze zdefiniowanej nazwy SZB
Ryż. 19.47. Zera są usuwane i pozostają tylko cyfry 3 i 2; liczba 2 to minimum, więc należy ją wyodrębnić jako następną
Ryż. 19.48. Funkcja MIN wybiera liczbę 2; funkcja MATCH może teraz znaleźć prawidłową pozycję względną dla funkcji INDEX
Ryż. 19.49. Funkcja INDEX pobierze wartość 2, która odpowiada względnej czwartej pozycji identyfikatora w zakresie A2:A5
Teraz wracając do komórki A11, możesz dodać kolejny warunek, aby puste komórki nie miały wpływu na formułę (rysunek 19.50).
Ryż. 19.50. Wewnątrz funkcji MIN znajdują się dwa warunki; po pierwsze: „czy komórki są puste?”, po drugie: „czy wartość nie została jeszcze wykorzystana?”
Na ryc. 19.51 pokazuje ostateczną formułę. Dodano warunek zapewniający, że wiersze z zakresu A11:A15 pozostaną puste po wyodrębnieniu posortowanych unikalnych wartości. Na ryc. Rysunek 19.52 pokazuje, co się stanie, jeśli komórka A3 pozostanie pusta. Nasz dodatek do sprawdzania pustych komórek zadziałał.
To nie było łatwe. Ale jeśli doczytałeś aż do tego miejsca, mam nadzieję, że ci się podobało.