Užitočné tipy

Užitočné funkcie Tabuľiek Google sa nenachádzajú v Exceli

Článok bol napísaný spolu s Renatom Shagabutdinovom.

Tento článok sa zameriava na niekoľko veľmi užitočných funkcií Tabuľiek Google, ktoré Excel nemá (SORT, spojenia polí, FILTER, IMPORTRANGE, IMAGE, GOOGLETRANSLATE, DETECTLANGUAGE)

Existuje veľa listov, ale existujú analýzy zaujímavých prípadov, všetky príklady sa mimochodom dajú preskúmať bližšie v dokumente Google goo.gl/cOQAd9 (file-> create copy to copy file to your disk and your edit a).

Obsah:


Ak výsledok vzorca zaberá viac ako jednu bunku

Po prvé, dôležitá funkcia zobrazovania vzorca vedie k Tabuľkám Google. Ak váš vzorec vráti viac ako jednu bunku, celé toto pole sa okamžite zobrazí a obsadí toľko buniek a stĺpcov, koľko potrebujete (v Exceli by ste preto museli do všetkých týchto buniek zadať vzorec poľa). V nasledujúcom príklade uvidíme, ako to funguje.

Pomôže to zoradiť rozsah údajov podľa jedného alebo viacerých stĺpcov a okamžite zobraziť výsledok.

= SORT (údaje o mape, stĺpec pre_sorting, vzostupne, [column_for_sorting_2, vzostupne_2 ,.])

(ďalej - príklady nastavení ruskej regionálnej tabuľky, nastavenie reg. sa zmení v menu súboru → nastavenie tabuľky)



Ako pridať hlavičky tabuľky do systému SORT?

Pomocou zložených zátvoriek <> vytvárame pole dvoch prvkov, nadpisy tabuľky A1: B1 a funkcie SORT, oddeľujeme prvky od seba pomocou stredníka.



Ako skombinovať niekoľko rozsahov údajov a triedenie (nielen)?

Pozrime sa, ako môžete kombinovať rozsahy pre použitie vo funkciách. Toto sa netýka iba SORT, táto technika sa dá použiť vo všetkých funkciách, kde je to možné, napríklad vo VPR alebo SEARCH.

Kto prečítal predchádzajúci príklad, už prišiel na to, čo má robiť: otvorte zloženú zátvorku a zozbierajte polia, aby ste ich skombinovali. Oddeľte ich od seba bodkočiarkou a uzavrite zloženú zátvorku.

Môžete kombinovať polia a nepoužívať ich vo vzorci, ale jednoducho ich zobraziť na hárku, povedzme, zbierať údaje z niekoľkých hárkov vašej knihy. Pre zvislé spájanie je potrebné pozorovať iba rovnaký počet stĺpcov vo všetkých fragmentoch (všade máme dva stĺpce).

A na snímke nižšie - príklad horizontálneho spojenia, používa namiesto stredníka stredné lomítko a je potrebné, aby sa počet riadkov vo fragmentoch zhodoval, inak vzorec vráti chybu namiesto kombinovaného rozsahu.

(bodkočiarka a spätné lomítko sú oddeľovače prvkov poľa v ruských regionálnych nastaveniach, ak príklady pre vás nefungujú, potom prostredníctvom súboru sú nastavenia tabuľky, uistite sa, že ich máte)

Teraz sa vráťme k horizontálnemu usporiadaniu a vložíme ho do funkcie SORT. Údaje zoradíme podľa prvého stĺpca v zostupnom poradí.

Spájanie sa dá použiť v ľubovoľných funkciách, hlavným je pozorovanie rovnakého počtu stĺpcov pre zvislé alebo riadkov pre vodorovné spájanie.

Všetky analyzované príklady možno považovať za bližšie k
Dokument Google.

Pomocou FILTRA môžeme filtrovať údaje podľa jednej alebo viacerých podmienok a výsledok zobraziť na pracovnom hárku alebo použiť výsledok v inej funkcii ako rozsah údajov.

Napríklad máme tabuľku s predajom našich zamestnancov, z nej odvodíme údaje pre jedného zamestnanca.

Do bunky E3 uvádzame nasledujúci vzorec:

Všimnite si, že syntax sa mierne líši od obvyklých vzorcov ako SUMMESLIN, kde by rozsah podmienky a samotná podmienka boli oddelené pomocou bodkočiarky.

Vzorec zadaný do jednej bunky vráti pole 9 buniek s údajmi, ale po príkladoch s funkciou SORT už nás to neprekvapuje.

Okrem znamienka rovnosti (=) v podmienkach môžete použiť aj>,> =, <> (nerovnaké) a pre čísla alebo dátumy môžete použiť všetky tieto značky.

Dve podmienky a práca s dátumom

Poďme komplikovať vzorec a pridajte k nemu ešte jednu podmienku, podľa dátumu predaja ponecháme všetok predaj od 02.01.17

Takto bude vzorec vyzerať, ak doň ihneď zadáte argumenty podmienky, venujte pozornosť prevodu zadania textu dátumu pomocou DATEVALUE:



Interaktívna tabuľka s FILTER a SPARKLINE

Viete, ako ďalej používať funkciu FILTER? Nemôžeme zobraziť výsledok funkcie na pracovnom liste, ale použiť ho ako údaje pre inú funkciu, napríklad iskru. Sparkline je funkcia, ktorá zostavuje graf v bunke na základe našich údajov, iskra má mnoho nastavení, ako je typ grafu, farba prvkov, ale teraz sa nebudeme zdržiavať na nich a túto funkciu použijeme bez ďalších nastavení. Poďme k príkladu.

Rozbaľovací zoznam. Náš rozvrh sa zmení v závislosti od vybraného zamestnanca v rozbaľovacom zozname, zoznam robíme takto:

  • vyberte bunku E2,
  • menu Data → Validation Data,
  • pravidlá: hodnota z rozsahu av rozsahu, v ktorom vyberieme stĺpec so zamestnancami zo zdrojových údajov, nebojte sa, že sa názvy opakujú, v rozbaľovacom zozname zostanú iba jedinečné hodnoty,

Kliknite na tlačidlo Uložiť a získajte rozbaľovací zoznam vo vybranej bunke:

Bunka s rozbaľovacím zoznamom sa stane podmienkou pre vzorec FILTER, napíšeme ho.

Vyzerá to dynamicky:

Ale ako môže vyzerať inteligentný SPARKLINE s ďalšími nastaveniami, v reálnej práci graf zobrazuje výsledky aktivity za jeden deň, zelené stĺpce sú kladné hodnoty, ružové stĺpce sú záporné.



ImportRange

Tabuľky Google používajú funkciu IMPORTRANGE na prenos údajov z jedného súboru do druhého.

Kedy sa to hodí?

  • Potrebujete aktuálne údaje zo svojho súboru rovesníkov.
  • Chcete spracovať údaje zo súboru, ku ktorému máte prístup „Iba na prezeranie“.
  • Chcete zbierať tabuľky z niekoľkých dokumentov na jednom mieste, aby ste ich mohli spracovať alebo zobraziť.

Tento vzorec vám umožňuje získať kópiu rozsahu z iných Tabuľiek Google. Zároveň nie je formátovanie prenosné - iba údaje (čo robiť s formátovaním - trochu povieme nižšie).

Syntax vzorca je nasledovná:

DÔLEŽITÉ (kľúč tabuľky, reťazec rozsahu)
DÔLEŽITÉ (kľúč, rozsah)

spreadsheet_key (key) - postupnosť znakov atribútu "key =" (key) vo väzbe na tabuľku (po "spreadsheets / ... /").

Príklad vzorca s kľúčom:

Váš súbor zobrazí rozsah A1: CM500 od Sheet1 zo súboru, ktorý je umiestnený na príslušnom odkaze.

Ak sa počet zdrojových stĺpcov alebo riadkov v zdrojovom súbore môže zmeniť, do druhého argumentu funkcie zadajte otvorený rozsah (pozri tiež pododdiel „Rozsahy tvaru A2: A“), napríklad:

List 1! A1: CM (ak budú pridané riadky)
List1! A1: 1000 (ak budú pridané stĺpce)

! Majte na pamäti, že ak načítate otvorený rozsah (napríklad A1: D), potom nemôžete manuálne vložiť žiadne údaje do stĺpcov A: D v súbore, kde sa nachádza vzorec IMPORTRANGE (tj na konečnom mieste, kde sa údaje načítajú). Zdá sa, že sú „rezervované“ pre celý otvorený rozsah - pretože jeho rozmer nie je vopred známy.

Môžete zadať odkaz na súbor a odkaz na rozsah, ktorý nie je vo vzorci, ale v bunkách dokumentu a odkázať na ne.

Ak teda v bunke A1 zadáte odkaz na dokument (bez úvodzoviek), z ktorého chcete načítať údaje, av bunke B1 - odkaz na hárok a rozsah (aj bez úvodzoviek), môžete importovať údaje pomocou nasledujúceho vzorca:

Voľba s odkazmi na bunky je výhodnejšia v tom zmysle, že vždy môžete ľahko prejsť na zdrojový súbor (kliknutím na odkaz v bunke) a / alebo zistiť, ktorý rozsah a z ktorej karty sa importujú.

Importovanie formátovania zo zdrojovej tabuľky

Ako sme si už všimli, IMPORTRANGE načítava iba údaje, ale nie formátovanie pôvodnej tabuľky. Ako sa s tým vysporiadať? Pôdu pripravte vopred skopírovaním formátovania zo zdrojového listu. Ak to chcete urobiť, prejdite na zdrojový list a skopírujte ho do svojej knihy:

Po stlačení tlačidla Kopírovať do ... Vyberte knihu, do ktorej chcete údaje importovať. Požadovaná tabuľka sa zvyčajne nachádza na karte nedávny (ak ste s ňou skutočne nedávno pracovali).

Po skopírovaní hárku vyberte všetky údaje (kliknutím na ľavý horný roh):

A kliknite vymazať, Všetky údaje zmiznú a formátovanie zostane. Teraz môžete vstúpiť do funkcie IMPORTRANGE a získať úplnú korešpondenciu zdrojového listu - v dátovej aj vo formátovej časti:



DÔLEŽITÉ ako argument k inej funkcii

IMPORTRANGE môže byť argumentom pre inú funkciu, ak rozsah, ktorý importujete, vyhovuje tejto úlohe.

Zoberme si jednoduchý príklad - priemernú hodnotu predaja z rozsahu uvedeného v inom dokumente.

Toto je zdrojový dokument. Nechajte údaje pridať a potrebujeme priemerný predaj v roku 2016 (tj od bunky D2 po zastavenie nadol).

Najprv importujeme tento rozsah:

OBRÁZOK: pridajte obrázky do buniek

Funkcia IMAGE umožňuje pridávať obrázky do Tabúľ Google.

Funkcia má nasledujúcu syntax:

Alebo vložte odkaz do bunky, v ktorej je odkaz uložený:

Druhá možnosť je vo väčšine prípadov výhodnejšia. Ak teda máte zoznam kníh a odkazov na obálky, stačí jeden vzorec na zobrazenie všetkých:

V praxi sa stáva, že odkazy na obrázky sú uložené na samostatnom hárku a získate ich pomocou funkcie VLOOKUP alebo inak.

Argument režimu môže mať štyri hodnoty (ak ho preskočíte, bude predvolene prvá):

  1. obrázok sa roztiahne na veľkosť bunky pri zachovaní pomeru strán,
  2. obrázok sa roztiahne bez zachovania pomeru strán, úplne sa vyplní
  3. obrázok sa vloží s pôvodnou veľkosťou,
  4. určíte veľkosť obrázka v treťom a štvrtom argumente pre funkcie [výška] a [w>. Pozrime sa, ako v praxi vyzerajú obrázky so štyrmi rôznymi hodnotami argumentu režimu:

Štvrtý režim môže byť vhodný, ak potrebujete zvoliť presnú veľkosť obrázka v pixeloch a zmeniť výšku (výšku) a šírku (šírku). Obrázok bude okamžite aktualizovaný.
Upozorňujeme, že vo všetkých režimoch okrem druhého sa môžu v bunke nachádzať prázdne oblasti a je možné ich vyplniť farbou:



GOOGLETRANSLATE a DETECTLANGUAGE: preklad textu do buniek

Tabuľky Google majú zábavnú funkciu GOOGLETRANSLATE, ktorá vám umožňuje prekladať text priamo do buniek:

Syntax funkcie je takáto:

GOOGLETRANSLATE (text, [source_language], [target_language])

text je text, ktorý sa má preložiť. Môžete citovať text a písať priamo do vzorca, ale je pohodlnejšie odkázať na bunku, v ktorej je text napísaný.
[source_language] - jazyk, z ktorého prekladáme
[target_language] je jazyk, do ktorého prekladáme.

Druhý a tretí argument sa uvádza pomocou dvojciferného kódu: es, fr, en, ru. Môžu byť tiež špecifikované v samotnej funkcii, ale môžu byť prevzaté z bunky a jazyk zdrojového textu môže byť automaticky určený.

Čo ak však chceme preložiť do rôznych jazykov? A zároveň nechceme zadávať zdrojový jazyk ručne zakaždým?

Tu je užitočná funkcia DETECTLANGUAGE. Má iba jeden argument - text, ktorého jazyk musí byť definovaný:

Tak ako pri každej inej funkcii, aj tu je krása v automatizácii. Môžete rýchlo zmeniť text alebo jazyk, rýchlo preložiť jednu frázu do 10 jazykov atď. Chápeme, že toto je text prekladateľa online - kvalita bude primeraná.

Evgeny Namokonov a Renat Shagabutdinov. Vedieme tiež kanál v telegrame, kde analyzujeme rôzne prípady s Tabuľkami Google, ak vás zaujíma - pokles o návštevu, odkaz nájdete v mojom profile.