СҰРАУ функциясы икемді сұрау пәрмендерін пайдаланып, деректер ауқымынан немесе бүкіл парақтан ақпаратты алуға мүмкіндік береді. Google Sheets QUERY функциясын пайдалануды үйрену қуатты іздеу құралына қол жеткізуге мүмкіндік береді.
Егер дерекқордан деректерді алу үшін SQL сұрауларын жазған болсаңыз, QUERY функциясын танисыз. Егер дерекқор тәжірибесі болмаса, QUERY функциясын үйрену әлі де өте оңай.
QUERY функциясы дегеніміз не?
Функцияның үш негізгі параметрі бар:
=QUERY(деректер, сұрау, тақырыптар)
Бұл параметрлер өте қарапайым.
- Деректер: бастапқы деректері бар ұяшықтар ауқымы
- Сұрау: бастапқы деректерден қалағаныңызды алу жолын сипаттайтын іздеу мәлімдемесі
- Тақырыптар: бастапқы ауқымдағы бірнеше тақырыптарды тағайындалған парақтағы бір тақырыпқа біріктіруге мүмкіндік беретін қосымша аргумент
QUERY функциясының икемділігі мен күші төменде көретіндей Сұрау аргументінен келеді.
Қарапайым QUERY формуласын қалай жасауға болады
QUERY формуласы әсіресе деректерді шығарып алу және сүзу қажет өте үлкен деректер жинағы болған кезде пайдалы.
Келесі мысалдарда АҚШ SAT орта мектебінің өнімділігі статистикасы пайдаланылады. Бұл бірінші мысалда сіз мектеп атауында "Нью-Йорк" орналасқан барлық орта мектептер мен олардың деректерін қайтаратын қарапайым QUERY формуласын жазуды үйренесіз.
-
Сұрау нәтижелерін орналастыру үшін жаңа парақ жасаңыз. Жоғарғы сол жақ ұяшықта =Сұрау() деп теріңіз. Мұны орындаған кезде талап ететін дәлелдер, мысал және функция туралы пайдалы ақпараты бар қалқымалы терезені көресіз.
Image -
Кейін, сізде бастапқы деректер Sheet1-де болса, функцияны келесідей толтырыңыз:
=Сұрау(1-парақ!A1:F460, "B, C, D, E, F ҚАЙДА ТАҢДАУ "%Нью-Йорк%'")
Бұл формула келесі аргументтерді қамтиды:
- Ұяшықтар диапазоны: Sheet1 ішіндегі A1-F460 деректер ауқымы
- SELECT мәлімдемесі: B, C, D, E және F бағандарындағы кез келген деректерді шақыратын SELECT мәлімдемесі, мұнда B бағанында «Нью-Йорк» сөзі бар мәтін бар " онда.
Image "%" таңбасы кез келген деректер жиынындағы жолдардың немесе сандардың бөліктерін іздеу үшін пайдалануға болатын қойылмалы таңба. Жолдың алдыңғы жағындағы "%" белгісін қалдыру "Нью-Йорк" мәтінінен басталатын кез келген мектеп атауын қайтарады.
-
Тізімнен нақты мектеп атын тапқыңыз келсе, сұрауды теріңіз:
=Сұрау(1-парақ!A1:F460, "B, C, D, E, F ҚАЙДА ТАҢДАҢЫЗДАР="Нью-Йорк Харбор орта мектебі")
=операторын пайдалану нақты сәйкестікті табады және оны кез келген бағандағы сәйкес мәтінді немесе сандарды табу үшін пайдалануға болады.
Image
Google Sheets QUERY функциясын түсіну және пайдалану өте оңай болғандықтан, жоғарыдағылар сияқты қарапайым сұрау мәлімдемелерін пайдаланып кез келген үлкен деректер жинағынан кез келген деректерді шығаруға болады.
Сұрау функциясын салыстыру операторымен пайдаланыңыз
Салыстыру операторлары шартқа сәйкес келмейтін деректерді сүзу үшін СҰРАУ функциясын пайдалануға мүмкіндік береді.
СҰРАҚ функциясындағы келесі операторлардың барлығына қол жеткізе аласыз:
- =: Мәндер іздеу мәніне сәйкес келеді
- <: Мәндер іздеу мәнінен аз
- >: Мәндер іздеу мәнінен үлкен
- <=: Мәндер іздеу мәнінен аз немесе оған тең
- >=: Мәндер іздеу мәнінен үлкен немесе оған тең
- және !=: Іздеу мәні мен бастапқы мәндер бірдей емес
Жоғарыдағы бірдей SAT мысалы деректерін пайдаланып, қай мектептердің орташа математикалық көрсеткіші 500 баллдан жоғары болғанын қалай көруге болатынын көрейік.
-
Бос парақтың жоғарғы сол жақ ұяшығына СҰРАУ функциясын келесідей толтырыңыз:
=Сұрау(1-парақ!A1:F460, "B, C, D, E, F ТАҢДАУ ҚАЙДА E > 500")
Бұл формула E бағанында 500-ден үлкен мән бар кез келген деректерді шақырады.
Image -
Бірнеше шарттарды іздеу үшін ЖӘНЕ және НЕМЕСЕ сияқты логикалық операторларды да қосуға болады. Мысалы, 600-ден астам тест тапсырушы және сыни көрсеткіші 400 мен 600 арасындағы мектептер үшін ғана ұпай жинау үшін келесі QUERY функциясын теру керек:
=Сұрау(1-парақ!A1:F460, "C > 600 ЖӘНЕ D > 400 ЖӘНЕ D < 600 B, C, D, E, F ТАҢДАУ")
Image - Салыстыру және логикалық операторлар бастапқы электрондық кестеден деректерді алудың көптеген түрлі жолдарын ұсынады. Олар тіпті өте үлкен деректер жиынынан маңызды ақпарат бөліктерін сүзуге мүмкіндік береді.
QUERY функциясының кеңейтілген пайдаланулары
Кейбір қосымша пәрмендер арқылы QUERY функциясына қосуға болатын бірнеше басқа мүмкіндіктер бар. Бұл пәрмендер мәндерді біріктіруге, мәндерді санауға, деректерге тапсырыс беруге және максималды мәндерді табуға мүмкіндік береді.
-
СҰРАУ функциясында GROUP функциясын пайдалану бірнеше жолдағы мәндерді біріктіруге мүмкіндік береді. Мысалы, GROUP функциясын пайдалана отырып, әрбір студент үшін орташа сынақ бағаларын алуға болады. Ол үшін теріңіз:
=Сұрау(1-парақ!A1:B24, "ТАҢДАУ A, AVG(B) GROUP BY A")
Image -
СҰРАҚ функциясында COUNT функциясын пайдалану арқылы келесі QUERY функциясын пайдаланып, жазудың орташа баллы 500-ден асатын мектептер санын санауға болады:
=QUERY(1-парақ!A2:F460, "B ТАҢДАУ, COUNT (F) B BY TOPU")
Image -
СҰРАУ функциясында ТАПСЫРЫС БОЙЫНША қолдану арқылы ең жоғары математикалық орташа ұпайлары бар мектептерді табуға және тізімді сол ұпайлар бойынша ретке келтіруге болады.
=QUERY(1-парақ!A2:F460, "B, MAX (E) ТОБЫ БОЙЫНША МАКС(Е) ТАҢДАУ")
Image
