Ако трябва да манипулирате данни в Google Таблици, функцията QUERY може да помогне! Той предоставя мощно търсене в стил на база данни във вашата електронна таблица, така че можете да търсите и филтрирате данните си във всеки формат, който желаете. Ще ви преведем как да го използвате.
Съдържание
Използване на функцията QUERY
Функцията QUERY не е твърде трудна за овладяване, ако някога сте взаимодействали с база данни с помощта на SQL. Форматът на типичната функция QUERY е подобен на SQL и носи силата на търсенето в база данни в Google Sheets.
Форматът на формула, която използва функцията QUERY, е =QUERY(данни, заявка, заглавки). Заменяте „данни“ с диапазона от клетки (например „A2:D12“ или „A:D“) и „заявка“ със заявката си за търсене.
Незадължителният аргумент „заглавки“ задава броя на редовете за заглавки, които да бъдат включени в горната част на диапазона ви от данни. Ако имате заглавка, която се простира върху две клетки, като „Първа“ в A1 и „Име“ в A2, това би уточнявало, че QUERY използва съдържанието на първите два реда като комбинирано заглавие.
В примера по-долу лист (наречен „Списък на персонала“) на електронна таблица на Google Таблици включва списък на служителите. Той включва имената им, идентификационните номера на служителите, датите на раждане и дали са присъствали на задължителната им сесия за обучение на служители.
На втори лист можете да използвате формула QUERY, за да изтеглите списък на всички служители, които не са присъствали на задължителната сесия за обучение. Този списък ще включва идентификационни номера на служителите, собствени имена, фамилни имена и дали са присъствали на обучението.
За да направите това с данните, показани по-горе, можете да въведете =QUERY(‘Списък с персонал’!A2:E12, „ИЗБЕРЕТЕ A, B, C, E, КЪДЕТО E = ‘Не'“). Това прави заявка за данните от диапазон A2 до E12 в листа „Списък на персонала“.
Подобно на типична SQL заявка, функцията QUERY избира колоните за показване (SELECT) и идентифицира параметрите за търсене (WHERE). Той връща колони A, B, C и E, предоставяйки списък на всички съвпадащи редове, в които стойността в колона E („Присъствано обучение“) е текстов низ, съдържащ „Не“.
Както е показано по-горе, четирима служители от първоначалния списък не са присъствали на обучение. Функцията QUERY предостави тази информация, както и съответстващи колони, за да се покажат имената и идентификационните номера на служителите в отделен списък.
Този пример използва много специфичен диапазон от данни. Можете да промените това, за да поискате всички данни в колони от A до E. Това ще ви позволи да продължите да добавяте нови служители към списъка. Формулата QUERY, която сте използвали, също ще се актуализира автоматично всеки път, когато добавите нови служители или когато някой присъства на сесията за обучение.
Правилната формула за това е =QUERY(‘Списък на персонала’!A2:E, „Изберете A, B, C, E, КЪДЕТО E = ‘Не'“). Тази формула игнорира първоначалното заглавие „Служители“ в клетка A1.
Ако добавите 11-и служител, който не е присъствал на обучението, към първоначалния списък, както е показано по-долу (Кристин Смит), формулата QUERY също се актуализира и показва новия служител.
Разширени формули QUERY
Функцията QUERY е универсална. Позволява ви да използвате други логически операции (като И и ИЛИ) или функции на Google (като COUNT) като част от вашето търсене. Можете също да използвате оператори за сравнение (по-голямо от, по-малко от и т.н.), за да намерите стойности между две фигури.
Използване на оператори за сравнение с QUERY
Можете да използвате QUERY с оператори за сравнение (като по-малко от, по-голямо или равно на), за да стесните и филтрирате данни. За да направим това, ще добавим допълнителна колона (F) към нашия лист „Списък на персонала“ с броя награди, които всеки служител е спечелил.
С помощта на QUERY можем да търсим всички служители, които са спечелили поне една награда. Форматът на тази формула е =QUERY(‘Списък на персонала’!A2:F12, „ИЗБЕРЕТЕ A, B, C, D, E, F, КЪДЕТО F > 0“).
Това използва оператор по-голямо от сравнение (>) за търсене на стойности над нулата в колона F.
Примерът по-горе показва, че функцията QUERY е върнала списък с осем служители, които са спечелили една или повече награди. От общо 11 служители трима никога не са печелили награда.
Използване на И и ИЛИ с QUERY
Вложените логически операторни функции като AND и OR работят добре в рамките на по-голяма формула QUERY, за да добавите множество критерии за търсене към вашата формула.
Добър начин да тествате И е да търсите данни между две дати. Ако използваме нашия пример за списък със служители, можем да изброим всички служители, родени от 1980 до 1989 г.
Това също се възползва от операторите за сравнение, като по-голямо или равно на (>=) и по-малко или равно на (
Форматът на тази формула е =QUERY(‘Списък на персонала’!A2:E12, „ИЗБЕРЕТЕ A, B, C, D, E КЪДЕ D >= ДАТА ‘1980-1-1’ и D
Както е показано по-горе, трима служители, родени през 1980, 1986 и 1983 г., отговарят на тези изисквания.
Можете също да използвате ИЛИ за получаване на подобни резултати. Ако използваме едни и същи данни, но сменяме датите и използваме ИЛИ, можем да изключим всички служители, които са родени през 80-те години на миналия век.
Форматът за тази формула би бил =QUERY(‘Списък на персонала’!A2:E12, „ИЗБЕРЕТЕ A, B, C, D, E, КЪДЕ D >= ДАТА ‘1989-12-31’ или D
От първоначалните 10 служители трима са родени през 80-те години. Примерът по-горе показва останалите седем, които всички са родени преди или след датите, които изключихме.
Използване на COUNT с QUERY
Вместо просто да търсите и връщате данни, можете също да смесвате QUERY с други функции, като COUNT, за да манипулирате данни. Да приемем, че искаме да изчистим редица от всички служители в нашия списък, които са посещавали и не са посещавали задължителната сесия за обучение.
За да направите това, можете да комбинирате QUERY с COUNT по този начин =QUERY(‘Staff List’!A2:E12, „SELECT E, COUNT(E) group by E“).
Фокусирайки се върху колона E („Присъствано обучение“), функцията QUERY използва COUNT, за да преброи колко пъти е намерен всеки тип стойност (текстов низ „Да“ или „Не“). От нашия списък шестима служители са преминали обучението, а четирима не.
Можете лесно да промените тази формула и да я използвате с други типове функции на Google, като SUM.