QUERY muaj nuj nqi cia koj rub cov ntaub ntawv los ntawm ntau yam lossis tag nrho cov ntaub ntawv siv cov lus nug yooj yim. Kawm paub siv Google Sheets QUERY muaj nuj nqi ua rau koj nkag mus rau lub cuab yeej zoo saib zoo.
Yog tias koj tau sau SQL cov lus nug kom tau txais cov ntaub ntawv tawm ntawm cov ntaub ntawv, ces koj yuav paub txog QUERY muaj nuj nqi. Yog tias koj tsis muaj kev paub hauv database, QUERY muaj nuj nqi tseem kawm tau yooj yim heev.
QUERY Function yog dab tsi?
Qhov kev ua haujlwm muaj peb qhov tseem ceeb:
=QUERY(cov ntaub ntawv, lus nug, headers)
Cov kev ntsuas no ncaj nraim ncaj nraim.
- Data: Qhov ntau ntawm cov hlwb uas muaj cov ntaub ntawv hauv
- Query: Daim ntawv tshawb fawb piav qhia yuav ua li cas rho tawm yam koj xav tau los ntawm cov ntaub ntawv qhov chaw
- Headers: Ib qho kev xaiv sib cav uas tso cai rau koj ua ke ntau lub hauv paus hauv qhov chaw mus rau hauv ib lub taub hau hauv daim ntawv lo lus
Qhov yooj thiab lub zog ntawm QUERY muaj nuj nqi los ntawm Cov Lus Nug, raws li koj yuav pom hauv qab no.
Yuav ua li cas los tsim ib qho yooj yim QUERY Formula
Cov qauv QUERY yog qhov tshwj xeeb tshaj yog thaum koj muaj cov ntaub ntawv loj heev uas koj xav tau rho tawm thiab lim cov ntaub ntawv.
Cov piv txwv hauv qab no siv US SAT cov ntawv txheeb xyuas kev ua haujlwm hauv tsev kawm theem siab. Hauv qhov piv txwv thawj no, koj yuav kawm paub sau cov qauv QUERY yooj yim uas xa rov qab txhua lub tsev kawm ntawv theem siab thiab lawv cov ntaub ntawv uas "New York" yog lub npe ntawm lub tsev kawm ntawv.
-
Tsim ib daim ntawv tshiab los tso cov lus nug. Nyob rau sab laug sab laug hom =Lus nug(. Thaum koj ua qhov no, koj yuav pom lub qhov rai pop-up uas xav tau kev sib cav, piv txwv, thiab cov ntaub ntawv muaj txiaj ntsig txog kev ua haujlwm.
Image -
Tom ntej no, xav tias koj muaj cov ntaub ntawv hauv Sheet1, sau cov haujlwm hauv qab no:
=Query(Sheet1!A1:F460, "SELECT B, C, D, E, F qhov twg yog qhov nyiam '%New York%'")
Cov qauv no suav nrog cov lus sib cav hauv qab no:
- Range of Cells: Qhov ntau ntawm cov ntaub ntawv hauv A1 txog F460 hauv Sheet1
- SELECT Nqe lus: Daim ntawv SELECT uas hu rau txhua cov ntaub ntawv hauv kab B, C, D, E, thiab F qhov twg kab B muaj cov ntawv uas muaj lo lus "New York " hauv nws.
Image Tus "%" tus cim yog ib daim ntawv qub uas koj tuaj yeem siv los tshawb nrhiav qhov chaw ntawm cov hlua lossis cov lej hauv txhua cov ntaub ntawv. Tawm "%" tawm ntawm sab xub ntiag ntawm txoj hlua yuav rov qab txhua lub tsev kawm ntawv lub npe uas pib nrog cov ntawv "New York".
-
Yog tias koj xav nrhiav lub npe ntawm lub tsev kawm ntawv tiag tiag los ntawm cov npe, koj tuaj yeem ntaus cov lus nug:
=Query(Sheet1!A1:F460, "SELECT B, C, D, E, F WHERE B='New York Harbor High School'")
Siv tus =tus neeg teb xov tooj pom qhov sib tw tiag tiag thiab tuaj yeem siv los nrhiav cov ntawv sib txuam lossis cov lej hauv txhua kab.
Image
Vim tias Google Sheets QUERY muaj nuj nqi yog qhov yooj yim rau kev nkag siab thiab siv, koj tuaj yeem rub cov ntaub ntawv tawm ntawm cov ntaub ntawv loj uas siv cov lus nug yooj yim xws li cov saum toj no.
Siv QUERY Function Nrog Tus Neeg Ua Haujlwm Sib Piv
Cov neeg ua haujlwm sib piv cia koj siv QUERY muaj nuj nqi los lim tawm cov ntaub ntawv uas tsis ua raws li qhov xwm txheej.
Koj tuaj yeem nkag mus rau txhua tus neeg ua haujlwm hauv qab no hauv QUERY muaj nuj nqi:
- =: Tus nqi sib tw nrog tus nqi tshawb nrhiav
- <: Tus nqi tsawg dua tus nqi tshawb nrhiav
- >: Tus nqi ntau dua tus nqi tshawb nrhiav
- <=: Tus nqi tsawg dua lossis sib npaug ntawm tus nqi tshawb nrhiav
- >=: Tus nqi ntau dua lossis sib npaug ntawm tus nqi tshawb nrhiav
- thiab !=: Nrhiav tus nqi thiab qhov txiaj ntsig tsis sib npaug
Siv tib yam SAT piv txwv cov ntaub ntawv teev saum toj no, cia peb saib seb lub tsev kawm ntawv twg muaj lej lej nruab nrab siab dua 500 ntsiab lus.
-
Nyob rau sab laug sab laug ntawm ib daim ntawv dawb paug, sau rau hauv QUERY muaj nuj nqi raws li hauv qab no:
=Query(Sheet1!A1:F460, "SELECT B, C, D, E, F WHERE E > 500")
Cov qauv no hu rau txhua cov ntaub ntawv uas kab E muaj tus nqi uas siab dua 500.
Image -
Koj tuaj yeem suav nrog cov neeg ua haujlwm muaj txiaj ntsig zoo li THIAB thiab LOSSIS txhawm rau tshawb nrhiav ntau yam xwm txheej. Piv txwv li, txhawm rau rub cov qhab nia tsuas yog rau cov tsev kawm ntawv uas muaj ntau dua 600 tus neeg xeem thiab qhov kev nyeem ntawv tseem ceeb ntawm 400 thiab 600, koj yuav ntaus QUERY muaj nuj nqi hauv qab no:
=Query(Sheet1!A1:F460, "SELECT B, C, D, E, F WHERE C > 600 THIAB D > 400 THIAB D < 600")
Image - Kev sib piv thiab cov tswv yim tsim nyog muab rau koj ntau txoj hauv kev los rub cov ntaub ntawv los ntawm cov ntawv nthuav qhia. Lawv cia koj lim tawm cov ntaub ntawv tseem ceeb los ntawm cov ntaub ntawv loj heev.
Kev siv Advanced QUERY Function
Muaj ob peb lwm yam uas koj tuaj yeem ntxiv rau QUERY muaj nuj nqi nrog qee cov lus txib ntxiv. Cov lus txib no tso cai rau koj sau cov txiaj ntsig, suav qhov muaj nuj nqis, xaj cov ntaub ntawv, thiab nrhiav cov txiaj ntsig siab tshaj plaws.
-
Siv GROUP hauv QUERY muaj nuj nqi tso cai rau koj los sau cov txiaj ntsig hauv ntau kab. Piv txwv li, koj tuaj yeem ntsuas cov qhab nia nruab nrab rau txhua tus tub ntxhais kawm siv GROUP muaj nuj nqi. Txhawm rau ua qhov no, ntaus:
=Query(Sheet1!A1:B24, "SELECT A, AVG(B) GROUP BY A")
Image -
Siv COUNT hauv QUERY muaj nuj nqi, koj tuaj yeem suav cov tsev kawm ntawv nrog cov qhab nia sau ntau dua 500 siv cov haujlwm QUERY hauv qab no:
=QUERY(Sheet1!A2:F460, "SELECT B, COUNT (F) GROUP BY B")
Image -
Siv ORDER BY hauv QUERY muaj nuj nqi, koj tuaj yeem nrhiav cov tsev kawm ntawv uas muaj cov qhab nia siab tshaj plaws thiab xaj cov npe los ntawm cov qhab nia ntawd.
=QUERY(Sheet1!A2:F460, "SELECT B, MAX (E) GROUP BY B ORDER BY MAX(E)")
Image
