W tym samouczku nauczysz się w jakich sytuacjach, w których operujemy na danych, używać formuł LookUp i LookUpValue oraz jak to robić.
Dzięki dwóm przykładom zobaczysz której z formuł i kiedy najlepiej użyć.
Przykład 1
1.Stwórz listę z kursami walut na wzór poniższej i nazwij ją 'KURSY'
Możesz skorzystać z załączonego pliku 'exchange-rates.xlsx' (na dole strony).
Zwróć uwagę na nazwy pól i użyte typy.
KOD - typ tekst
NAZWA - typ tekst
KURS - typ waluta
| CHF | Frank szwajcarski | 3,86 |
| EUR | Euro | 4,34 |
| GBP | Funt szterling | 4,86 |
| NOK | Korona norweska | 0,45 |
| SEK | Korona szwedzka | 0,42 |
| USD | Dolar amerykański | 3,70 |
2.Następnie utwórz nową apkę i dodaj w niej widżet tabeli
a)
Nazwij nagłówki kolumn kolejno: cena w PLN, waluta, kurs, cena w walucie.
b)
Ustaw typ kolumn A, C i D na walutowy, kolumnę B jako listę rozwijaną oraz kolumnę A jako edytowalną.
c)
Teraz powiąż kolumnę B tabeli z wcześniej utworzoną listą. Wejdź w zakładkę dane i odnajdź w niej listę oraz pole z listy 'kod'. Pole 'kod' przeciągnij do kolumny B. Utworzy się w niej dzięki temu lista rozwijana z której będzie można wybrać skrótową nazwę waluty.
Zaznacz opcję modyfikacji wierszy w opcjach tabeli.
d)
Następnie w kolumnie C użyjemy formuły LookupValue aby pobrać kurs wybranej z listy waluty. Najpierw podajemy z jakiej listy pobieramy dane, następnie podajemy zapytanie - takie wartości dla których kod jest równy temu wybranego w kolumnie B, a jako ostatnie podajemy z której kolumny listy chcemy zwrócić wartość - w tym przypadku, z pola kurs.
e)
Na koniec w kolumnie D wpisz '=A/C'. Formuła ta przelicza wybraną wartość w złotówkach z kolumny A na wartość w wybranej walucie.
3.Sprawdź jak działa apka w trybie podglądu
Apka działa poprawnie, w każdym wierszu chwilę po wybraniu waluty pojawia się podana cena przeliczona na tę walutę.
Nie jest to jednak zalecane rozwiązanie, ze względu na to, że istnieją lepsze - szybsze i oszczędniejsze. Poprzez wielokrotne użycie formuły lookupValue w każdym wierszu musimy odwołać się do bazy i pobrać z niej dane.
Zastosowanie lepszej metody wymaga kilku prostych zmian w naszej aplikacji.
1.Dodaj drugą tabelę
a)
Twoja tabela powinna mieć dwie kolumny. Powiąż ją przy pomocy formuły Lookup z wcześniej używaną listą. Możesz to zrobić wpisując formułę lookup w polu 'filtr' w opcjach tabeli.
Następnie obie kolumny powiąż z interesującymi nas polami z listy (kod, kurs).
b)
Wróć teraz do pierwszej tabeli. Usuń formułę wcześniej użytą w kolumnie C. Zastąpimy ją formułą Nth. Jest to formuła która zwraca element z zakresu/tablicy występujący na podanej pozycji.
Jako zakres podamy kolumnę B drugiej tabeli (section2.B). Do podania pozycji użyjemy formuły 'find' która zwraca pozycję podanego elementu w danym zakresie. W formule find jako zakres podamy tablicę wartości z kolumny A (section2.A), a jako szukaną wartość kolumnę B z pierwszej tabeli.
Formuła w całości sprawia, że szukamy nią takiego kursu (z kolumny B w tabeli 2), którego kod waluty (z kolumny A w tabeli 2) jest taki jak ten wybrany wcześniej w kolumnie B w tabeli 1.
c)
Sprawdźmy teraz jak całość działa.
W odróżnieniu od pierwszego rozwiązania tutaj dane z bazy pobieramy tylko raz - do drugiej tabeli. Dzięki takiemu rozwiązaniu poprzez zapisanie danych z bazy na apce przyspieszamy działanie apki.
Dzięki temu przykładowi wiemy że projektując apkę która używa niedużej ilości wierszy z bazy, a jednocześnie dużej ilości wierszy tabeli w apce warto pobrać dane z bazy na apkę.
Przykład 2
1. Zacznij znowu od stworzenia listy i nazwaniu jej 'FAKTURY_REJESTR'
Możesz skorzystać z załączonego pliku 'invoice-register.xlsx' (na dole strony).
Powinna ona mieć stosunkowo dużo wierszy gdyż ma to być przykładowy rejestr faktur z całego roku.
Zwróć uwagę na nazwy pól i użyte typy:
- NR: typ tekst
- DATA: typ data
- WARTOSC: typ liczba
- DZIEN: typ liczba
- MIESIAC: typ liczba
2.Stwórz nową aplikację i dodaj w niej widżet tabeli
a)
Tabela powinna mieć dwie kolumny - pierwszą edytowalną o typie tekstowym a drugą nieedytowalną o typie 'waluta'. Nazwij nagłówki : nr faktury, czy zarejestrowana i dodaj opcję modyfikacji wierszy.
b)
Dodaj drugą tabelę i powiąż ją z listą faktur. Skorzystamy tu z rozwiązania analogicznego do tego w przykładzie pierwszym - ściągniemy dane z bazy do pomocniczej tabeli w apce.
Teraz wróć do pierwszej tabeli. Użyj w niej formuły Nth tak jak w przykładzie 1.
Włącz podgląd i sprawdź jak działa twoja apka. Zauważ, że w tabeli powiązanej z listą, nie ma wszystkich elementów. Domyślnie w ten sposób można wziąć 200 elementów z powiązanej tabeli lub listy. Aby w tabelce były wszystkie elementy, należy w opcjach tabeli powiązanej z listą zaznaczyć opcję Raport.
3. Formatowanie (krok opcjonalny)
a)
Ponieważ utworzona na początku lista to rejestr faktur, to w naszej apce szukamy po numerze faktury czy została zarejestrowana. Aby pokazać które faktury zostały zarejestrowane dodamy formatowanie warunkowe które będzie zmieniało komórkę jeśli szukana faktura nie została zarejestrowana. (czytaj o formatowaniu warunkowym).
b)
W tym momencie, kiedy szukanego numeru faktury nie ma w liście, formuła zwraca błąd. Aby błąd się nie pokazywał, należy dopisać przed formułą w kolumnie B pierwszej tabeli formułę iferror w ten sposób: =iferror(nth(section4.B,find(array(section4.A),A)),0)
Formuła iferror sprawdza czy dana formuła zwraca błąd, jeśli nie zwraca wynik formuły, jeśli tak zwraca wartość zastępczą (w tym przypadku 0)
Dodatkowo zaznacz dla tej komórki opcję 'Pusta jeśli zero'. Teraz kiedy danej faktury nie ma w rejestrze w tej komórce będzie się pojawiało puste pole.
c)
Dodaj arkusz kalkulacyjny. Stworzymy w nim bazę dla naszego formatowania. Wybierz jedną z komórek arkusza i zmień jej kolor na np. jasnoczerwony/ różowawy. Następnie, mając dalej zaznaczoną tę komórkę, wybierz w ustawieniach formatowania opcję 'zapisz jako', i nazwij utworzony styl. Po tym możesz usunąć tą sekcję arkusza kalkulacyjnego.
d)
Mając już styl, wróć do pierwszej tabelki z fakturami. Kliknij na kolumnę B tabeli i w ustawieniach formatowania w polu styl wybierz z listy 'wylicz styl używając formuły'.
W polu formuła stylu wpisz formułę if, w której w zależności od tego czy komórka w kolumnie B jest równa zero czy nie, będzie zmieniała kolor tej komórki.
not_reg to nazwa utworzonego stylu.
Input to jeden z domyślnych stylów możliwych do wyboru.
4. Sprawdź jak działa apka
Jak widać faktur które wybieramy jest stosunkowo niedużo - jedynie kilka, bo chcemy tylko sprawdzić czy już są w rejestrze. Natomiast rekordów w tabeli z bazy jest bardzo dużo - pobieramy cały rejestr.
W takiej sytuacji w której mamy dużo rekordów w bazie, a potrzebujemy wyszukać mało, nie powinniśmy pobierać całej bazy na apkę. Cała pobrana zawartość zostaje wówczas zapisana na instancji apki co czyni ją o wiele większą pamięciowo. W tego rodzaju przypadku lepiej zastosować poniższe rozwiązanie.
5.Wprowadź kilka zmian do apki
a)
W tym wypadku przyda nam się rozwiązanie odrzucone w przykładzie 1. Użyjemy zatem formuły lookupvalue. Możemy wówczas usunąć powiązanie tabeli 1 z tabelą 2 a także ukryć lub usunąć tabelę 2.
b)
Wpisz w kolumnie B poniższą formułę. Za pomocą lookupvalue będziemy szukali wartości faktury o takim numerze jak podany w kolumnie A.
Jeśli formuła nie znajdzie takiej faktury wówczas komórka w takim wierszu będzie pusta, więc trzeba zmienić formułę stylu na: =if(ISEMPTY(section1.B),'not_reg','Input')
Formuła Isempty zwraca czy dana komórka jest pusta.
c)
Sprawdź jak działa taka wersja apki.
Możemy zatem na podstawie tego przykładu wywnioskować, że przeszukiwanie bazy za każdym dodaniem wiersza nie jest złym rozwiązaniem pod warunkiem że tych przeszukań robimy stosunkowo niedużo, w dużej bazie.
0 Komentarze