Функцията QUERY ви позволява да изтегляте информация от диапазон или цял лист с данни, като използвате гъвкави команди за заявки. Научаването как да използвате функцията QUERY на Google Таблици ви дава достъп до мощен инструмент за търсене.
Ако някога сте писали SQL заявки за извличане на данни от база данни, тогава ще разпознаете функцията QUERY. Ако нямате опит с бази данни, функцията QUERY все още е много лесна за научаване.
Какво представлява функцията QUERY?
Функцията има три основни параметъра:
=QUERY(данни, заявка, заглавки)
Тези параметри са доста ясни.
- Данни: Диапазонът от клетки, които съдържат изходните данни
- Query: Изявление за търсене, описващо как да извлечете това, което искате от изходните данни
- Headers: Незадължителен аргумент, който ви позволява да комбинирате множество заглавки в изходния диапазон в един хедър в целевия лист
Гъвкавостта и мощността на функцията QUERY идва от аргумента Query, както ще видите по-долу.
Как да създадете проста формула QUERY
Формулата QUERY е особено полезна, когато имате много голям набор от данни, от който трябва да извлечете и филтрирате данни.
Следващите примери използват статистически данни за представянето в средното училище SAT в САЩ. В този първи пример ще научите как да напишете проста формула QUERY, която връща всички гимназии и техните данни, където "Ню Йорк" е в името на училището.
-
Създайте нов лист за поставяне на резултатите от заявката. В горната лява клетка напишете =Query(. Когато направите това, ще видите изскачащ прозорец с необходими аргументи, пример и полезна информация за функцията.
Image -
След това, ако приемем, че имате изходните данни в Sheet1, попълнете функцията, както следва:
=Заявка(Sheet1!A1:F460, "SELECT B, C, D, E, F WHERE B LIKE '%New York%'")
Тази формула включва следните аргументи:
- Диапазон от клетки: Диапазонът от данни в A1 до F460 в Sheet1
- Изявление SELECT: Изявление SELECT, което извиква всякакви данни в колони B, C, D, E и F, където колона B съдържа текст с думата „Ню Йорк " в него.
Image Знакът "%" е заместващ знак, който можете да използвате за търсене на части от низове или числа във всеки набор от данни. Оставянето на "%" в началото на низа ще върне всяко име на училище, което започва с текста "Ню Йорк".
-
Ако искате да намерите името на точно училище от списъка, можете да въведете заявката:
=Заявка(Sheet1!A1:F460, "SELECT B, C, D, E, F WHERE B='New York Harbor High School'")
Използването на оператора =намира точно съвпадение и може да се използва за намиране на съответстващ текст или числа във всяка колона.
Image
Тъй като функцията QUERY на Google Таблици е много лесна за разбиране и използване, можете да изтеглите всякакви данни от всеки голям набор от данни, като използвате прости изрази за заявка като тези по-горе.
Използване на функцията QUERY с оператор за сравнение
Операторите за сравнение ви позволяват да използвате функцията QUERY, за да филтрирате данни, които не отговарят на условие.
Имате достъп до всички следните оператори във функция QUERY:
- =: Стойностите съответстват на търсената стойност
- <: Стойностите са по-малки от стойността за търсене
- >: Стойностите са по-големи от стойността за търсене
- <=: Стойностите са по-малки или равни на стойността за търсене
- >=: Стойностите са по-големи или равни на търсената стойност
- и !=: Търсената стойност и стойността на източника не са равни
Използвайки същия набор от примерни данни за SAT по-горе, нека да разгледаме как да видим кои училища са имали среден резултат по математика над 500 точки.
-
В горната лява клетка на празен лист попълнете функцията QUERY, както следва:
=Заявка(Лист1!A1:F460, "ИЗБЕРЕТЕ B, C, D, E, F WHERE E > 500")
Тази формула изисква всички данни, където колона E съдържа стойност, по-голяма от 500.
Image -
Можете също да включите логически оператори като И и ИЛИ за търсене на множество условия. Например, за да изтеглите резултати само за училища с над 600 полагащи теста и средна стойност за критично четене между 400 и 600, трябва да въведете следната функция QUERY:
=Заявка(Лист1!A1:F460, "ИЗБЕРЕТЕ B, C, D, E, F WHERE C > 600 И D > 400 И D < 600")
Image -
Операторите за сравнение и логическите оператори ви предоставят много различни начини за извличане на данни от изходна електронна таблица. Те ви позволяват да филтрирате важни части от информация дори от много големи набори от данни.
Разширено използване на функцията QUERY
Има няколко други функции, които можете да добавите към функцията QUERY с някои допълнителни команди. Тези команди ви позволяват да събирате стойности, да броите стойности, да подреждате данни и да намирате максимални стойности.
-
Използването на GROUP във функция QUERY ви позволява да събирате стойности в множество редове. Например можете да осредните тестовите оценки за всеки ученик, като използвате функцията GROUP. За да направите това, въведете:
=Заявка(Лист1!A1:B24, "ИЗБЕРЕТЕ A, СРЕДНО(B) ГРУПИРАНЕ ПО A")
Image -
Използвайки COUNT във функция QUERY, можете да преброите броя на училищата със среден резултат при писане над 500, като използвате следната функция QUERY:
=QUERY(Sheet1!A2:F460, "ИЗБЕРЕТЕ B, БРОЙ (F) ГРУПИРАНЕ ПО B")
Image -
Използвайки ORDER BY във функция QUERY, можете да намерите училища с максимални средни резултати по математика и да подредите списъка според тези резултати.
=QUERY(Sheet1!A2:F460, "SELECT B, MAX (E) GROUP BY B ORDER BY MAX(E)")
Image
