Koristni nasveti

Kako nastaviti formulo v tabeli Google Dokumenti, tako da je ena od spremenljivk vedno vzeta iz iste celice?

Pin
Send
Share
Send
Send


Članek je bil soavtor z Renatom Shagabutdinov.

Ta članek se bo osredotočil na več zelo uporabnih funkcij Google Preglednic, ki jih Excel nima (SORT, array pridruži, FILTER, IMPORTRANGE, IMAGE, GOOGLETRANSLATE, DETECTLANGUAGE)

Pisem je veliko, vendar obstajajo analize zanimivih primerov, vse primere, mimogrede, si lahko podrobneje ogledate v Googlovem dokumentu goo.gl/cOQAd9 (datoteka-> ustvarite kopijo, da kopirate datoteko v Google Drive in jo lahko urejate).

Kazalo:


Če rezultat formule zaseda več kot eno celico

Prvič, pomembna značilnost prikazovanja rezultatov formule v Google Preglednicah. Če vaša formula vrne več kot eno celico, bo ta celotna matrika prikazana takoj in bo zasedla toliko celic in stolpcev, kolikor je potrebno (v Excelu bi za to morali vnesti matrično formulo v vse te celice). V naslednjem primeru bomo videli, kako to deluje.

Pomagala bo razvrstiti obseg podatkov po enem ali več stolpcih in takoj prikazati rezultat.

= SORT (razvrstljivi podatki, stolpec_za_dstopanje, naraščajoče, [stolpec_za_dodajanje_2, naraščajoče_2 ,.])

(v nadaljevanju - primeri za ruske nastavitve regionalne tabele in nastavitve reg. so spremenjeni v meniju datotek → nastavitve tabele)



Kako dodati glave tabel v SORT?

S pomočjo kodrastih oklepajev <> ustvarimo niz dveh elementov, naslovi tabele A1: B1 in funkcijo SORT, ločimo elemente drug od drugega s podpičjem.



Kako združiti več obsegov podatkov in jih razvrstiti (in ne samo)?

Poglejmo si, kako lahko kombinirate obsege za uporabo v funkcijah. To ne velja samo za SORT, to tehniko lahko uporabljate v vseh funkcijah, kjer je to mogoče, na primer v VPR ali ISKANJE.

Kdo je prebral prejšnji primer, je že ugotovil, kaj storiti: odpreti kodraste naramnice in zbrati matrike, ki jih je treba kombinirati, ločiti jih med seboj s podpičjem in zaviti navojne naramnice.

Niz lahko kombinirate in jih ne uporabljate v formuli, ampak jih preprosto prikažete na listu, recimo tako, da zbirajo podatke z več listov knjige. Za navpično spajanje je potrebno opazovati le enako število stolpcev v vseh fragmentih (povsod imamo dva stolpca).

In na spodnjem posnetku zaslona - primer horizontalne zveze, namesto podpičke pod črto uporablja povratno poševnico in potrebno je, da število vrstic v fragmentih sovpada, sicer bo formula namesto kombiniranega obsega vrnila napako.

(podpičje in poševnica sta ločila elementov matrike v ruskih regionalnih nastavitvah. Če primeri za vas ne delujejo, potem skozi datoteko potekajo nastavitve tabele, preverite, ali jih imate)

No, vrnimo se k horizontalnemu nizu in ga vstavimo v funkcijo SORT. Podatke bomo razvrstili po prvem stolpcu po padajočem vrstnem redu.

Združevanje je mogoče uporabiti v kateri koli funkciji, glavna stvar je opazovati enako število stolpcev za navpično ali vrstic za vodoravno spajanje.

Vse razčlenjene primere je mogoče obravnavati bližje
Google Dok.

S pomočjo FILTERja lahko filtriramo podatke v skladu z enim ali več pogoji in rezultat prikažemo na delovnem listu ali pa rezultat uporabimo v drugi funkciji kot obseg podatkov.

Primer: imamo tabelo s prodajo zaposlenih, iz nje bomo izpeljali podatke za enega zaposlenega.

V celico E3 vnesemo naslednjo formulo:

Upoštevajte, da se skladnja nekoliko razlikuje od običajnih formul, kot je SUMMESLIN, kjer bi razpon pogoja in sam pogoj ločili s podpičjem.

Formula, vnesena v eno celico, vrne matriko 9 celic s podatki, vendar nas po primerih s funkcijo SORT to ne preseneča več.

Poleg znakov enakosti (=) v pogojih lahko uporabite tudi>,> =, <> (ni enako), za številke ali datume pa lahko uporabite vse te znake.

Dva pogoja in delo z datumom

Zapletemo formulo in ji dodamo še en pogoj, glede na datum prodaje bomo vso prodajo zapustili od 01.01.17.

Tako bo izgledala formula, če vanj takoj vnesete argumente pogoja, bodite pozorni na pretvorbo vnosa besedila z datumom z uporabo DATEVALUE:



Interaktivni grafikon s FILTER in SPARKLINE

Ali veste, kako drugače uporabljati funkcijo FILTER? Rezultata funkcije ne moremo prikazati na delovnem listu, temveč ga uporabimo kot podatke za drugo funkcijo, na primer iskrišče. Sparkline je funkcija, ki gradi graf v celici na podlagi naših podatkov, iskrišče ima veliko nastavitev, kot so vrsta grafa, barva elementov, zdaj pa se na njih ne bomo zadržali in funkcijo uporabljali brez dodatnih nastavitev. Pojdimo na primer.

Spustni seznam. Naš urnik se bo spremenil glede na izbranega zaposlenega na spustnem seznamu, seznam naredimo takole:

  • izberite celico E2,
  • v meniju Podatki → Validacija podatkov,
  • pravila: vrednost iz obsega in v razponu izberemo stolpec z zaposlenimi iz izvornih podatkov, ne skrbite, da se imena ponavljajo, na spustnem seznamu bodo ostale samo edinstvene vrednosti,

Kliknite »Shrani« in v izbrani celici dobite spustni seznam:

Celica s spustnega seznama bo postala pogoj za formulo FILTER, zapisali jo bomo.

Tako je videti v dinamiki:

Kako pa lahko izgleda pametna SPARKLINE z dodatnimi nastavitvami, v resničnem delu grafikon prikaže rezultate aktivnosti v enem dnevu, zelene palice so pozitivne vrednosti, roza trakovi so negativne.



POMEMBNO

Google Preglednice uporablja funkcijo IMPORTRANGE za prenos podatkov iz ene datoteke v drugo.

Kdaj lahko pride v poštev?

  • Iz datoteke z vrstniki potrebujete najnovejše podatke.
  • Podatke želite obdelati iz datoteke, do katere imate dostop samo za ogled.
  • Tabele iz več dokumentov želite zbrati na enem mestu, da jih obdelate ali si ogledate.

Ta formula vam omogoča, da dobite kopijo obsega iz drugega Google Preglednic. Hkrati oblikovanje ni prenosljivo - samo podatki (kaj storiti s formatiranjem - povedali bomo nekoliko spodaj).

Sintaksa formule je naslednja:

IMPORTRANGE (ključ preglednice, niz razpona)
IMPORTRANGE (tipka, obseg)

Spreadsheet_key (ključ) - zaporedje znakov atributa "key =" (tipka) v povezavi do tabele (za "preglednice / ... /").

Primer formule s ključem:

Vaša datoteka bo prikazala obseg A1: CM500 iz Sheet1 iz datoteke, ki se nahaja na ustrezni povezavi.

Če se lahko število stolpcev ali vrstic v izvorni datoteki spremeni, vpišite odprto območje v drugi argument funkcije (glejte tudi pododdelek "Obsegi obrazca A2: A"), na primer:

Sheet1! A1: CM (če bodo dodane vrstice)
Sheet1! A1: 1000 (če bodo dodani stolpci)

! Upoštevajte, da če naložite odprt obseg (na primer A1: D), nobenih podatkov ne morete ročno vstaviti v stolpce A: D v datoteko, kjer se nahaja formula IMPORTRANGE (torej na končnem mestu, kjer se podatki naložijo). Zdi se, da so "rezervirani" za celotno odprto območje - ker njegova dimenzija ni vnaprej znana.

Lahko vnesete povezavo do datoteke in povezavo do obsega, ki ni v formuli, ampak v celicah vašega dokumenta in se sklicujete na njih.

Torej, če v celico A1 vnesete povezavo do dokumenta (brez narekovajev), iz katerega želite naložiti podatke, v celici B1 pa povezavo na list in obseg (tudi brez narekovajev), potem lahko podatke uvozite po naslednji formuli:

Možnost s povezavami v celicah je zaželena v smislu, da lahko vedno enostavno odprete izvorno datoteko (s klikom na povezavo v celici) in / ali vidite, kateri obseg in s katerega zavihka se uvozijo.

Uvozi oblikovanje iz izvorne tabele

Kot smo že opazili, IMPORTRANGE nalaga samo podatke, ne pa tudi oblikovanja izvirne tabele. Kako se spoprijeti s tem? Tla vnaprej pripravite tako, da kopirate oblikovanje z izvornega lista. Če želite to narediti, pojdite na izvorni list in ga kopirajte v svojo knjigo:

Po pritisku na gumb Kopiraj v ... Izberite knjigo, v katero boste uvozili podatke. Običajno je na zavihku želena tabela Zadnje (če ste pred kratkim resnično sodelovali z njo).

Po kopiranju lista izberite vse podatke (s klikom na zgornji levi kot):

In kliknite Izbriši. Vsi podatki bodo izginili, oblikovanje pa ostane. Zdaj lahko vstopite v funkcijo IMPORTRANGE in dobite popolno korespondenco izvornega lista - tako v podatkovnem delu kot v formatnem delu:



IMPORTRANGE kot argument druge funkcije

IMPORTRANGE je lahko argument druge funkcije, če obseg, ki ga uvažate, ustreza tej vlogi.

Vzemimo preprost primer - povprečna vrednost prodaje iz obsega, ki ga najdemo v drugem dokumentu.

To je izvorni dokument. Dodajmo podatke in potrebujemo povprečje prodaje v letu 2016 (torej od celice D2 do zaustavitve).

Najprej uvozimo to ponudbo:

SLIKA: dodajte slike celicam

Funkcija IMAGE omogoča dodajanje slik v Google Preglednice.

Funkcija ima naslednjo skladnjo:

Ali pa postavite povezavo do celice, v kateri je povezava shranjena:

Slednja možnost je v večini primerov bolj priročna. Če imate seznam knjig in povezave do platnic, je ena formula dovolj za prikaz vseh:

V praksi se zgodi, da so povezave do slik shranjene na ločenem listu in jih dobite s funkcijo VLOOKUP ali kako drugače.

Argument načina lahko sprejme štiri vrednosti (če ga preskočite, bo prva privzeto):

  1. slika se raztegne na velikost celice, hkrati pa ohrani razmerje stranic,
  2. slika se raztegne, ne da bi ohranili razmerje stranic in se popolnoma zapolnili
  3. slika se vstavi v izvirni velikosti,
  4. v tretjem in četrtem argumentu določite velikost slike v funkciji [višina] in [w>. Poglejmo, kako izgledajo slike s štirimi različnimi vrednostmi argumenta načina:

Četrti način je lahko priročen, če morate izbrati natančno velikost slike v pikah, pri čemer spremenite parametre višino (višino) in širino (širino). Slika bo takoj posodobljena.
Upoštevajte, da so lahko v vseh načinih, razen v drugem, v celici prazna območja in jih je mogoče napolniti z barvo:



GOOGLETRANSLATE in DETECTLANGUAGE: prevedite besedilo v celicah

Google Preglednice ima zabavno funkcijo GOOGLETRANSLATE, ki vam omogoča neposredno prevajanje besedila v celicah:

Sintaksa funkcije je naslednja:

GOOGLETRANSLATE (besedilo, [vir_language], [target_language])

besedilo je besedilo, ki ga je treba prevesti. Lahko citirate besedilo in pišete neposredno v formulo, vendar je bolj priročno sklicevati se na celico, v kateri je besedilo napisano.
[source_language] - jezik, iz katerega prevajamo
[target_language] je jezik, v katerega prevajamo.

Drugi in tretji argument sta podana z dvomestno kodo: es, fr, en, ru. Določeni so lahko tudi v sami funkciji, vendar jih je mogoče vzeti iz celice, jezik izvirnega besedila pa je mogoče samodejno določiti.

Kaj pa, če želimo prevesti v različne jezike? In hkrati ne želimo vsakokrat ročno določiti izvornega jezika?

Tu je uporabna funkcija DETECTLANGUAGE. Ima samo en argument - besedilo, katerega jezik mora biti opredeljen:

Kot pri vsaki drugi funkciji je tudi tukaj lepota v avtomatizaciji. Besedilo ali jezik lahko hitro spremenite, eno frazo hitro prevedete v 10 jezikov in podobno. Seveda razumemo, da je to besedilo spletnega prevajalca - kakovost bo ustrezna.

Evgeny Namokonov in Renat Shagabutdinov, vodimo pa tudi kanal v telegramu, kjer razdelimo različne primere z Google Sheets, če vas zanima - opustite obisk, povezavo najdete v mojem profilu.

Oglejte si video: 9. Kreiranje tabela u Word-u (Januar 2021).

Pin
Send
Share
Send
Send