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ń.
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:
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).
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
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
W przypadku komórki autouzupełnianie i liście rozwijanej
pole ze źródła danych
musi być typu tekst.
Jeśli chcesz, żeby elementy w liście były unikalne, użyj formuły Unique:
lookup(źródło, kryteria, unique(pole))
Po przeciągnięciu pola z zakładki "Dane" domyślna formuła lookup nie zawiera już unique(). Dzięki temu nie są wykonywane kosztowne zapytania zliczające, co poprawia wydajność dla dużych tabel. Jeśli potrzebne są unikalne wartości, unique należy dodać ręcznie.
Query w formule Lookup
Funkcja query() w formule Lookup przeszukuje całą tabelę, a nie tylko pierwsze 200 wyników, umożliwiając dokładniejsze filtrowanie wyników autouzupełniania. Więcek o tym w artykule query.
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”
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.
Składnia:
lookup(źródło, [kryteria])
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)
=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)
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))
=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)
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: tekst) Wartości, które mają się pojawić
w liście elementów - nazwa kolumny z listy
lub
tabeli albo nazwa aliasu z apki
Jeśli chcesz, żeby elementy w liście były unikalne, użyj formuły Unique:
lookup(źródło, kryteria, unique(pole))
Po przeciągnięciu pola z zakładki "Dane" domyślna formuła lookup nie zawiera już unique(). Dzięki temu nie są wykonywane kosztowne zapytania zliczające, co poprawia wydajność dla dużych tabel. Jeśli potrzebne są unikalne wartości, unique należy dodać ręcznie.
Query w formule Lookup
Funkcja query() w formule Lookup przeszukuje całą tabelę, a nie tylko pierwsze 200 wyników, umożliwiając dokładniejsze filtrowanie wyników autouzupełniania. Więcek o tym w artykule query.
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)
=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)
lookup(sekcja_master)
Parametry:
sekcja_master
(typ: Identyfikator) Nazwa sekcji „master”,
która
zawiera listę elementów
Typ zwracanej wartości:
Rekord
Przykłady:
=lookup(section1)
=lookup(section1)
0 Komentarze