A QUERY függvény használata a Google Táblázatokban

Ha adatokat kell kezelnie a Google Táblázatokban, a QUERY funkció segíthet! Hatékony, adatbázis-szerű keresést hoz a táblázatba, így bármilyen formátumban megkeresheti és szűrheti adatait. Végigvezetjük, hogyan kell használni.

A QUERY függvény használata

A QUERY funkciót nem túl nehéz elsajátítani, ha valaha is kommunikált egy adatbázissal SQL használatával. Egy tipikus QUERY függvény formátuma hasonló az SQL-hez, és az adatbázis-keresések erejét hozza el a Google Táblázatokba.

A QUERY függvényt használó képlet formátuma =QUERY(adatok, lekérdezés, fejlécek). A „data” szót lecseréli a cellatartományára (például „A2:D12” vagy „A:D”), a „query” szót pedig a keresési lekérdezésére.

Az opcionális „fejlécek” argumentum beállítja az adattartomány tetején lévő fejlécsorok számát. Ha olyan fejléce van, amely két cellára terjed ki, mint például az A1-ben a „First” és az A2-ben a „Név”-ben, akkor ez megadja, hogy a QUERY az első két sor tartalmát használja kombinált fejlécként.

Az alábbi példában a Google Táblázatok táblázatának egyik lapja („Személyzetlista”) tartalmazza az alkalmazottak listáját. Tartalmazza a nevüket, az alkalmazottak azonosítószámát, a születési dátumukat, és azt, hogy részt vettek-e a kötelező alkalmazotti képzésen.

A második lapon a QUERY képlet segítségével összeállíthatja azon alkalmazottak listáját, akik nem vettek részt a kötelező képzésen. Ez a lista tartalmazza az alkalmazottak azonosítószámát, keresztnevét és vezetéknevét, valamint azt, hogy részt vettek-e a képzésen.

  Játssz a Chrome T-Rex futójátékkal a telefonodon

A fenti adatokkal ezt megteheti a =QUERY(‘Személyzetlista’!A2:E12, „SELECT A, B, C, E WHERE E = ‘Nem”) beírásával. Ez lekérdezi az A2-től E12-ig terjedő adatokat a „Személyzetlista” lapon.

Egy tipikus SQL lekérdezéshez hasonlóan a QUERY függvény kiválasztja a megjelenítendő oszlopokat (SELECT), és azonosítja a keresés paramétereit (WHERE). Az A, B, C és E oszlopot adja vissza, és felsorolja az összes egyező sort, amelyben az E oszlop értéke („Részt vett képzésen”) egy „Nem”-et tartalmazó szöveges karakterlánc.

Mint fentebb látható, a kezdeti listáról négy alkalmazott nem vett részt képzésen. A QUERY funkció biztosította ezt az információt, valamint a megfelelő oszlopokat, amelyek külön listában mutatják be a nevüket és az alkalmazottak azonosítószámát.

Ez a példa egy nagyon specifikus adattartományt használ. Módosíthatja ezt az A-tól E-ig terjedő oszlopok összes adatának lekérdezéséhez. Ez lehetővé teszi, hogy továbbra is új alkalmazottakat vegyen fel a listára. A használt QUERY képlet is automatikusan frissül, amikor új alkalmazottakat vesz fel, vagy ha valaki részt vesz a képzésen.

A helyes képlet erre a =QUERY(‘Személyzetlista’!A2:E, „Válasszon A, B, C, E WHERE E = ‘Nem’”). Ez a képlet figyelmen kívül hagyja a kezdeti „Alkalmazottak” címet az A1 cellában.

Ha hozzáad egy 11. alkalmazottat a kezdeti listához, aki nem vett részt a képzésen, ahogy az alább látható (Christine Smith), akkor a QUERY képlet is frissül, és megjeleníti az új alkalmazottat.

  A fordítás be- és kikapcsolása a Chrome-ban

Speciális QUERY képletek

A QUERY funkció sokoldalú. Lehetővé teszi más logikai műveletek (például ÉS és VAGY) vagy Google-függvények (például COUNT) használatát a keresés részeként. Használhat összehasonlító operátorokat is (nagyobb, mint, kisebb, és így tovább) két számjegy közötti értékek kereséséhez.

Összehasonlító operátorok használata a QUERY lekérdezéssel

A QUERY összehasonlító operátorokkal (például kisebb, nagyobb vagy egyenlő) használható az adatok szűkítésére és szűrésére. Ehhez egy további oszlopot (F) adunk a „Személyzetlista” laphoz, amely tartalmazza az egyes alkalmazottak által elnyert díjak számát.

A QUERY segítségével megkereshetjük az összes olyan alkalmazottat, aki legalább egy díjat nyert. A képlet formátuma =QUERY(‘Személyzetlista’!A2:F12, “SELECT A, B, C, D, E, F WHERE F > 0”).

Ez egy nagyobb, mint összehasonlítás operátort (>) használ a nulla feletti értékek kereséséhez az F oszlopban.

A fenti példa azt mutatja, hogy a QUERY függvény nyolc olyan alkalmazottat tartalmazó listát adott vissza, akik egy vagy több díjat nyertek. A 11 alkalmazottból három még soha nem kapott díjat.

Az ÉS és az OR használata a QUERY kifejezéssel

A beágyazott logikai operátorfüggvények, mint például az ÉS és a VAGY, jól működnek egy nagyobb QUERY képletben, így több keresési feltételt is hozzáadhat a képlethez.

Az ÉS tesztelésének jó módja, ha két dátum között keresünk adatokat. Ha az alkalmazotti listánkat használjuk, akkor az 1980 és 1989 között született összes alkalmazottat felsorolhatjuk.

  Virtuális gépek beállítása Linuxon Gnome Boxokkal

Ez kihasználja az összehasonlító operátorok előnyeit is, például nagyobb vagy egyenlő (>=) és kisebb vagy egyenlő (

A képlet formátuma =QUERY(‘Személyzetlista’!A2:E12, „SELECT A, B, C, D, E WHERE D >= DÁTUM ‘1980-1-1’ és D

Mint fentebb látható, három 1980-ban, 1986-ban és 1983-ban született alkalmazott felel meg ezeknek a követelményeknek.

A VAGY funkciót is használhatja hasonló eredmények eléréséhez. Ha ugyanazokat az adatokat használjuk, de megváltoztatjuk a dátumokat és a VAGY-t használjuk, akkor az 1980-as években született munkavállalókat kizárhatjuk.

A képlet formátuma =QUERY(‘Személyzetlista’!A2:E12, „SELECT A, B, C, D, E WHERE D >= DÁTUM ‘1989-12-31’ vagy D

Az eredeti 10 alkalmazottból három az 1980-as években született. A fenti példa a maradék hétet mutatja, akik mind az általunk kizárt dátumok előtt vagy után születtek.

COUNT használata a QUERY lekérdezéssel

Az adatok egyszerű keresése és visszaadása helyett a QUERY függvényt más függvényekkel is keverheti, például COUNT, az adatok kezeléséhez. Tegyük fel, hogy törölni szeretnénk a listánkon szereplő összes alkalmazott közül, akik részt vettek és nem vettek részt a kötelező képzésen.

Ehhez kombinálhatja a QUERY-t a COUNT-val, így a =QUERY(‘Személyzetlista’!A2:E12, „SELECT E, COUNT(E) group by E”).

Az E oszlopra összpontosítva („Részt vett képzés”), a QUERY függvény a COUNT segítségével megszámolta, hogy hányszor talált minden értéktípust („Igen” vagy „Nem” szöveges karakterlánc). A listánkból hat alkalmazott végezte el a képzést, négyen nem.

Könnyen módosíthatja ezt a képletet, és más típusú Google-függvényekkel, például a SUM-mal is használhatja.