Excel Forum - Porady, Pomoc,  Excel Help, Excel FAQ Strona Główna
 FAQ  RegulaminRegulamin  Szukaj   Użytkownicy   Grupy   Rejestracja   Profil   Twoje wiadomości   Zaloguj 


Poprzedni temat «» Następny temat
ID tematu: 60477 Skopiuj do schowka Nieznany(?) sposób użycia funkcji baz danych (BD.funkcji)
Autor Wiadomość
Maciej Gonet
Excel Expert


Pomógł: 1183 razy
Posty: 4046
Wysłany: 14-02-2018, 15:42   Nieznany(?) sposób użycia funkcji baz danych (BD.funkcji)

Niedawno "nabyłem drogą kupna" książkę Mike'a Girvina pt. "Ctrl+Shift+Enter Mastering Excel Array Formulas". Książka jest ciekawie napisana, bardzo przejrzyście ilustrowana przykładami pokazującymi kolejność wykonywania działań i może być cenną pomocą dla tych, co nie tylko chcą wiedzieć jak?, ale również dlaczego tak?
Niemniej nie byłbym sobą, gdybym nie dostrzegł w niej pewnych nieścisłości, niedomówień, a nawet błędów. Jednym z takich mankamentów jest spłycenie opisu sposobu użycia funkcji baz danych. Autor w kilku miejscach podkreśla (i uznaje to za jedną z wad tych funkcji), że funkcje baz danych wymagają użycia nazw pól w wierszu nagłówkowym bazy danych oraz zakresu kryteriów.
Jest to najbardziej typowy i najpopularniejszy sposób użycia tych funkcji, ale przecież nie jedyny możliwy. Jeśli tak zacny autor pomija milczeniem inne warianty, to widocznie te informacje nie przebiły się do świadomości publicznej i chciałbym napisać na ten temat parę słów, aby przynajmniej polscy użytkownicy Excela nie pozostawali w nieświadomości.
Składnia BD.funkcji jest taka:
Kod:
= BD.funkcja(zakres_bazy; numer_lub_tekst_nazwy_pola; zakres_kryteriów)
Pierwszy i trzeci argument są odwołaniami, drugi argument jest zwykłą wartością.
To co jest faktycznie wymagane przez BD.funkcje, to jeden dodatkowy wiersz nad danymi zaliczony do zakresu bazy danych, ale niekoniecznie zawierający nagłówki pól. Może on pozostawać pusty lub zawierać cokolwiek.
Drugi argument identyfikuje pole bazy, do którego odnosi się operacja. Może to być tekst nazwy pola, ale alternatywnie (choćby wtedy gdy nazwy pola nie ma lub nie jest unikatowa) – numer pola (liczony od 1).
Trzeci argument to odwołanie do zakresu kryteriów. Musi to być odwołanie do co najmniej 2 komórek ułożonych pionowo. Taki pionowy zestaw komórek stanowi pojedyncze kryterium. Kryteria muszą tworzyć spójny obszar przylegających do siebie kolumn. Dopuszczalne są dwa rodzaje kryteriów:
a) z nagłówkiem kolumny
b) w postaci formuły.
Kryterium z nagłówkiem kolumny zawiera w pierwszym wierszu nagłówek odpowiedniej kolumny, a poniżej wyrażenia tekstowe z operatorem relacji lub bez operatora. Nagłówek połączony z wartościami wpisanymi poniżej tworzy dopiero pełne kryterium. Szczegóły interpretacji tych kryteriów są w sumie dość zawiłe, ale są dość dobrze opisane w materiałach Microsoftu, więc nie chcę się tu nad tym rozwodzić. Są one zupełnie analogiczne jak w przypadku filtra zaawansowanego.
Kryterium w postaci formuły jest mniej znane i rzadziej stosowane. Jest ono konstruowane podobnie jak formuły formatowania warunkowego, to znaczy powinno zwracać wartość logiczną PRAWDA lub FAŁSZ (albo równoważną wartość liczbową). Jeśli stosujemy kryterium w postaci formuły, to w pierwszym wierszu zakresu kryteriów (w danej kolumnie) powinna być pusta komórka lub dowolna wartość inna niż nagłówki kolumn w bazie danych. Samo kryterium może odwoływać się do pola bazy danych przez nagłówek tego pola, traktowany jak nazwa (musi spełniać kryteria nazwy Excela) – użycie niezdefiniowanej nazwy generuje błąd w zakresie kryteriów, ale to nie zakłóca jego działania. Alternatywnie kryterium może zawierać odwołanie względne (numer wiersza musi być bez $, litera kolumny - obojętne) do pierwszego wiersza (ważne!) danych w bazie, to znaczy do wiersza bezpośrednio pod wierszem nagłówkowym. Przy użyciu kryterium w postaci formuły nie ma znaczenia, czy pole w bazie danych ma nagłówek, czy nie. W jednym zakresie kryteriów można łączyć kryteria z nagłówkami pól i kryteria w postaci formuł. Kryteria w postaci formuł są bardzo elastyczne – jedno kryterium może odwoływać się równocześnie do kilku pól. Poza odwołaniami względnymi do pierwszego wiersza danych, kryteria mogą zawierać odwołania absolutne do dowolnych komórek.
Na koniec mało znana właściwość funkcji baz danych: drugi argument pole może wskazywać kilka pól jednocześnie przez zastosowanie odpowiedniej stałej tablicowej, zawierającej nagłówki lub numery pól. Ewentualnie można użyć zakresu, ale po dereferencji np.
Kod:
INDEKS(+zakres_nagłówków;)

Kilka przykładów użycia w załączniku.

BDFunkcje_forum.xlsx
Pobierz Plik ściągnięto 96 raz(y) 14.29 KB

ID posta: 340382 Skopiuj do schowka
 
 
Kaper 



Zaproszone osoby: 3
Pomógł: 3710 razy
Posty: 7388
Wysłany: 14-02-2018, 17:29   

Czy mogę lapidarnie:

:danke
:beer
_________________
Kaper Jej Królewskiej Mości :boss

Jestem leniwy, ale się staram
ID posta: 340391 Skopiuj do schowka
 
 
Bill Szysz 
Excel Expert


Pomógł: 774 razy
Posty: 3243
Wysłany: 15-02-2018, 00:36   

Świetne opracowanie tematu Maciej :clap :beer
Książkę Mike'a też mam choć przyznam się że nie czytałem ( pobieżnie przejrzałem tylko ).
Mike mi ją przysłał jakiś czas temu ( pewnie wysyłał książki wszystkim których wymienia w aknowledgement, bo tam akurat też jestem )
Dość często jestem z nim w kontakcie.
_________________
Szkolenia z Power Query!!!

Pozdrawiam, były szbill62 aktualnie Bill Szysz
ID posta: 340413 Skopiuj do schowka
 
 
Wormsek 



Zaproszone osoby: 2
Pomógł: 909 razy
Posty: 5262
Wysłany: 15-02-2018, 11:55   

Mega fajne. Ostatni raz funkcjami DB bawiłem się około 7 lat temu ;-) , ale pamiętam, że tylko standardowo je stosowałem i przez to mi jakoś nie przypasowały, bo te warunki obok rażą oczy ;-) . Najbardziej mi się podoba wersja z formułami w których są nagłówki jako zakres "pseudo-zdefiniowany" ;-) . Angielski excel pokazuje błąd "#NAME?" ale i tak poprawnie liczy :mrgreen:

name.PNG
Plik ściągnięto 20 raz(y) 17.09 KB

_________________
Pozdro
Worm

FAQ - Najczęściej zadawane pytania.
JAK KORZYSTAĆ Z SZUKAJKI
Słownik funkcji

Znajdź nas na Facebook'u

A może fajny dodatek do excela?
ID posta: 340447 Skopiuj do schowka
 
 
Maciej Gonet
Excel Expert


Pomógł: 1183 razy
Posty: 4046
Wysłany: 16-02-2018, 10:34   

A propos tych błędów. Do wersji 2003 była taka opcja: Przeliczanie/Akceptuj etykiety w formułach, domyślnie wyłączona, ale jak się ją włączyło, to w takiej sytuacji w zakresie kryteriów były zera zamiast błędów. Ale od wersji 2007 już tej opcji nie ma, była ona związana z inną jeszcze funkcjonalnością etykiet, z której zrezygnowano, i przy okazji "wylano dziecko z kąpielą".
ID posta: 340532 Skopiuj do schowka
 
 
Wyświetl posty z ostatnich:   
Odpowiedz do tematu
Nie możesz pisać nowych tematów
Nie możesz odpowiadać w tematach
Nie możesz zmieniać swoich postów
Nie możesz usuwać swoich postów
Nie możesz głosować w ankietach
Nie możesz załączać plików na tym forum
Możesz ściągać załączniki na tym forum
Dodaj temat do Ulubionych
Wersja do druku

Skocz do:  

Powered by phpBB modified by Przemo © 2003 phpBB Group
Theme xandgreen created by spleen& Programosy modified v0.3 by warna
Opieka techniczna www.marketingNET.pl

Archiwum

Strona używa plików cookies.

Kliknij tutaj, żeby dowiedzieć się jaki jest cel używania cookies oraz jak zmienić ustawienia cookie w przeglądarce.
Korzystając ze strony użytkownik wyraża zgodę na używanie plików cookies, zgodnie z bieżącymi ustawieniami przeglądarki.
Sprawdź, w jaki sposób przetwarzamy dane osobowe