Lookup

Opis formuły:
Służy do pobierania danych z listy, tabeli lub aliasu z dokumentów apek (jeśli chcesz pobrać pojedynczą wartość w komórce, użyj formuły LookupValue).

Kryteria wyszukiwania
Aby wyszukać odpowiednie dane należy określić kryteria. Formuła zwróci wartości z tych rekordów, dla których podany warunek jest spełniony. Jeśli nie podasz żadnych kryteriów, zostaną zwrócone wszystkie rekordy ze źródła danych. Kryteria składają się z warunków postaci:
nazwa_pola operator wartość
gdzie:
nazwa_pola – nazwa kolumny lub aliasu,
operator – możesz zastosować jeden z operatorów porównania: >, <, >=, <=, =, !=. Wartości tekstowe są porównywane w kolejności alfabetycznej.
wartość – może być podana bezpośrednio, pobrana ze wskazanej komórki lub obliczona za pomocą formuły. Typ wartości musi być taki sam jak typ pola.
Możesz połączyć kilka warunków za pomocą operatorów and (&&) i or (||). Zapytanie z operatorem and jest spełnione tylko wtedy, gdy oba warunki są prawdziwe. Natomiast zapytanie z operatorem or jest spełnione, gdy przynajmniej jeden z warunków jest prawdziwy. Możesz też użyć operatora not (!), aby zanegować warunek. W przypadku złożonych kryteriów pogrupuj warunki używając nawiasów, aby określić kolejność wykonywania działań.

 

Możesz również skonstruować kryterium, które będzie wyszukiwało elementy, które należą do wartości podanej listy. Postać kryterium: nazwa_pola = array(wartości)

Przykład:

=LOOKUP(SYSUSERINGROUP, GROUP = ARRAY('Administrators', 'Designers') - ta formuła zwróci wyniki tylko dla użytkowników, którzy są w grupie Administratorów lub Designerów 

Uwaga:

Możesz wypisać w liście maksimum 1000 elementów.

Grupowanie danych
Możesz też pogrupować zwrócone rekordy za pomocą specjalnych formuł – agregatów. Wpisz wybrany agregat jako trzeci argument formuły Lookup, zamiast identyfikatora zwracanego pola. Możesz użyć jednej z dostępnych formuł:
– sum(pole) – Sumuje wartości z podanego pola ze wszystkich rekordów w grupie
– average(pole) – Oblicza średnią z wartości z podanego pola ze wszystkich rekordów w grupie
– count(pole) – Zlicza ilość rekordów w grupie
– min(pole) – Zwraca najmniejszą wartość z podanego pola spośród rekordów w grupie
– max(pole) – Zwraca największą wartość z podanego pola spośród rekordów w grupie
– sumsqr(pole) – Oblicza sumę kwadratów wartości z podanego pola ze wszystkich rekordów w grupie
Wszystkie pola ze źródła danych (poza polem w formule agregatu), które zostaną użyte w sekcji stanowią kryteria grupowania. Rekordy, które mają takie same wartości tych pól zostaną połączone w jedną grupę. Dla każdej grupy będzie obliczona wartość wybranego agregatu.

Gdzie można użyć formuły Lookup?
1) sekcja typu Tabela lub Arkusz kalkulacyjny
Aby wypełnić sekcję danymi należy:
– wpisać formułę Lookup w polu „Filtr” we właściwościach sekcji
– przynajmniej w jednej kolumnie sekcji użyć pola ze źródła danych
W przypadku tabeli:

- dane z każdego pobranego rekordu zostaną wstawione do jednego wiersza tabeli, w przypadku arkusza - do sekcji zostaną wstawione dane z pojedynczego rekordu, który spełnia podane kryteria (jeśli jest wiele takich rekordów zostanie zwrócony pierwszy z nich).

Składnia:
lookup(źródło, [kryteria])

Parametry:

źródło

(typ: Identyfikator) Nazwa listy, tabeli lub apki, z której chcesz pobrać dane

kryteria

(typ: Logiczny) (argument opcjonalny) Warunki, które muszą spełniać zwrócone rekordy

Typ zwracanej wartości:

Rekord

Przykłady:
=lookup(products)
=lookup(products, category = section1.A1)

2) wykresy
Aby umieścić dane na wykresie należy:
– dodać nową serię danych
– wpisać formułę Lookup w polu „Formuła” we właściwościach serii

Składnia:
lookup(źródło, kryteria, wartości_X, wartości_Y)

Parametry:

źródło

(typ: Identyfikator) Nazwa listy, tabeli lub apki, z której chcesz pobrać dane

kryteria

(typ: Logiczny) (argument opcjonalny) Warunki, które muszą spełniać zwrócone rekordy

wartości_X

(typ: dowolny) Wartości na osi X – nazwa kolumny z listy lub tabeli albo nazwa aliasu z apki

wartości_Y

(typ: Liczba) Wartości na osi Y – nazwa kolumny z listy lub tabeli, nazwa aliasu z apki lub agregat

Typ zwracanej wartości:

Seria wartości

Przykłady:
=lookup(sales, region, average(price))

3) komórka typu Lista rozwijana lub Autouzupełnianie
Aby stworzyć listę wartości, które użytkownik będzie mógł wybrać w komórce należy:
– ustawić typ komórki na „Lista rozwijana” lub „Autouzupełnianie”
– wpisać formułę Lookup w polu „Elementy” we właściwościach komórki

Składnia:
lookup(źródło, kryteria, pole)

Parametry:

źródło

(typ: Identyfikator) Nazwa listy, tabeli lub apki, z której chcesz pobrać dane

kryteria

(typ: Logiczny) (argument opcjonalny) Warunki, które muszą spełniać zwrócone rekordy

pole

(typ: dowolny) Wartości, które mają się pojawić w liście elementów - nazwa kolumny z listy lub tabeli albo nazwa aliasu z apki

W przypadku komórki Autouzupełnianie pole ze źródła danych musi być typu Tekst. W Liście rozwijanej możesz użyć kolumny lub aliasu dowolnego typu, ale wartość wybrana z listy przez użytkownika i tak będzie w dalszych obliczeniach traktowana jak wartość tekstowa.
Jeśli chcesz, żeby elementy w liście były unikalne, użyj formuły Unique:
lookup(źródło, kryteria, unique(pole))

Typ zwracanej wartości:

Tablica tekstów

Przykłady:
=lookup(products, price>100, product_code)
=lookup(sysuser, isactive and age > 18, email)
=lookup(sales, region = 'western' or region = 'northern', type)

4) master-detail
Master-detail to połączenie dwóch sekcji – sekcji „master” typu Tabela, która zawiera listę elementów oraz sekcji „detail” typu Arkusz kalkulacyjny, która wyświetla szczegóły dla aktualnie zaznaczonego wiersza w sekcji „master”. Gdy użytkownik zaznaczy inny wiersz w tabeli, dane w sekcji „detail” zostaną automatycznie zamienione, żeby pokazywać szczegóły wybranego elementu.
Aby stworzyć master-detail należy:
– dodać sekcję typu Tabela i pobrać do niej dane za pomocą formuły Lookup (jak w pkt 1)
– dodać sekcję typu Arkusz kalkulacyjny i we właściwości „Filtr” wpisać formułę Lookup z nazwą sekcji „master”
– w przynajmniej jednej komórce sekcji „detail” użyć pola ze źródła danych podanego w formule Lookup sekcji „master”

Składnia:
lookup(sekcja_master)

Parametry:

sekcja_master

(typ: Identyfikator) Nazwa sekcji „master”, która zawiera listę elementów

W sekcji "detail" można używać tylko tych pól ze źródła danych, które zostały pobrane do sekcji "master". Nawet jeśli nie chcesz wyświetlać niektórych informacji w tabeli „master”, pobierz do niej wszystkie potrzebne dane, a następnie ukryj widoczność odpowiednich kolumn.

Typ zwracanej wartości:

Rekord

Przykłady:
=lookup(section1)