Jak VLOOKUP z jiného listu v Tabulkách Google
můžete odemknout plný potenciál Vlookup (vertikální vyhledávání) v Tabulkách Google pomocí volání dat v různých tabulkách a sešitech.
funkce Vlookup je neuvěřitelně výkonná funkce, která dokáže proměnit hodiny práce na pouhé sekundy. I když je tato funkce užitečná pro analýzu dat na singulárním listu, můžete dále zvýšit použitelnost Vlookup voláním dat v různých tabulkách.
pomocí této funkce můžete rychle a efektivně vyhledávat a načítat konkrétní hodnoty z velkého množství dat.
možnost použití na více tabulkách usnadňuje udržování čistoty vašich dat a automatické aktualizace, pokud dojde ke změnám mezi listy.
tento článek se týká:
vzorec VLOOKUP
syntaxe Vlookup je velmi jednoduchá, ale může být použita ve velmi složité míře.
jak naznačuje „vertikální“ v jeho názvu, Vlookup prohledá sloupec úplně vlevo ve stanoveném rozsahu pro shodu řádků a vrátí hodnotu v buňce identifikované indexem.
prohledá sloupec a vrátí hodnotu z odpovídajícího řádku, obvykle z jiného sloupce.
jednou z největších slabin ve Vlookup je, že nemůže hledat shodu ve sloupci vpravo a přivést zpět výsledek ze sloupce vlevo. Jinými slovy, Vlookup může vypadat nebo odbočit vpravo.
takto vypadá vzorec, když je rozdělen podle části:
=Vlookup(search_key,range,index,is_sorted)
podívejme se na každou složku jednotlivě:
- =Vlookup (): Toto je samotná funkce bez jakýchkoli parametrů. To je to, co říká Tabulkám Google, aby jednaly.
- search_key: tento parametr definuje, jaké informace chceme porovnat. Může to být jméno, číslo, booleovská hodnota nebo něco jiného. Může to být statická hodnota, kterou jsme definovali, nebo relativní hodnota uložená v buňce. Pokud to nastavíme jako „A2“, vyhledá hodnotu v buňce „A2“. Pokud jej nastavíme na „true“, bude odpovídat textovému řetězci „true“.“
- rozsah: To řekne funkci Vlookup, kde hledat shodu a rozsah, ve kterém se nachází hodnota, kterou má vrátit. Pokud hledáme shodu hodnot ve sloupci B a chceme vrátit hodnotu ve sloupcích C nebo D, nastavili bychom rozsah jako B: D.
- index: tento parametr říká Vlookup, kterou hodnotu sloupce se má vrátit. Index je relativní k rozsahu, nikoli listu. Proto je to číslo místo písmene, jako jsou obvykle definovány sloupce. Pokud se naše vyhledávání shoduje ve sloupci B a vrací hodnotu ze sloupce C, hodnota indexu je 2. Pokud vracíme sloupec D ze shody sloupce B, hodnota indexu je 3.
- is_sorted: název tohoto parametru není tak jasný, protože vrací přesné shody, když je nastaven na „false“, a nejbližší shody, když je nastaven na „true“.“True je ve výchozím nastavení nastaven, ale false se doporučuje ve většině použití.
nyní se podívejme na to všechno dohromady:
=Vlookup(A2,A2:B5,2,false)
použití VLOOKUP ve stejném sešitu a v jiném sešitu
zatímco většinu času používáme vzorec VLOOKUP v Tabulkách Google na stejném listu, mnohokrát jej budete muset použít k VLOOKUP mezi dvěma listy ve stejném sešitu nebo dokonce v jiném sešitu.
například můžete chtít načíst data z konkrétních položek v listu, zatímco data vyhledávání jsou v jiném listu nebo jiném sešitu.
jak používáte funkci Vlookup k přenosu dat z jiného listu, je poněkud odlišné, když pracujete ve stejném sešitu nebo jiném sešitu.
vzorec pro stejný sešit Vlookup vypadá:
=vlookup(search_key,{sheet name}!{cell range},index,is_sorted)
Všimněte si, že existuje „!“mezi názvem listu a rozsahem buněk. Také neexistují žádné citace kolem rozsahu na stejném sešitu Vlookup.
v našem testu chceme nastavit rozsah na A3: B6 na našem listu s názvem “ volal.“Hledáme shodu ve sloupci A a vracíme hodnotu ve sloupci B.
níže je vzorec, který to provede:
=Vlookup(A2,Called!A2:B5,2,false)
výše uvedený vzorec načte hodnotu z druhého sloupce z listu s názvem „volal“ v aktuálním listu.
nyní se podívejme na příklad, kde musíme načíst hodnotu z jiného sešitu Tabulek Google
=vlookup(search_key,importrange("{sheetsURL}","{sheet name}!{cell range}"),index,is_sorted)
Tato verze používá nový příkaz s názvem „importrange ()“.
syntaxe tohoto příkazu vás požádá, abyste definovali adresu URL sešitu, ze kterého importujete data, definovali konkrétní list a nastavili rozsah.
vzorec se takto rozpadá:
Importrange("{sheetsURL}","{sheet name}!{cel range}")
- {sheetsURL}: v uvozovkách přidejte adresu URL souboru Tabulek Google, ke kterému chcete přistupovat. Příklad: „https://docs.google.com/spreadsheets/d/1AJcuVkYvdiW0NAlfuI“
- {název listu}!{cel range}: Nastavte to stejným způsobem, jakým jste jej nakonfigurovali v metodě stejného sešitu. Všimněte si však, že tentokrát jsou kolem názvu listu a rozsahu buněk uvozovky.
nyní se na to podívejte s přidanou hodnotou a všemi přidanými hodnotami :
=Vlookup(A2,importrange("https://docs.google.com/spreadsheets/d/18nsDPJ","Called!A2:B5"),2,false)
Chcete-li zopakovat, existují tři důležité hodnoty, které musíte nastavit v odkazu Vlookup cross-sheet:
- URL sešitu {sheetsURL}
- stránka listu {název listu}
- rozsah buněk {rozsah buněk}:
Běh procesem Vlookup
pojďme pochopit proces, jak VLOOKUP funguje při odkazování na jiné listy nebo sešity.
VLOOKUP z jiného listu ve stejném sešitu
použijme jednoduchý případ inventáře produktu, abychom předvedli, jak Vlookup funguje.
v tomto příkladu máme sestavu čtyř produktů: gadgety, Gizmos, Thingamabobs a widgety. V současné době nemáme žádné Gizmos na skladě. Náš první list, „aktivní“ nám dává seznam všech našich produktů a říká nám, zda je produkt skladem:
máme také druhý list, který jsme pojmenovali „volal“, který uvádí, kolik z každého produktu máme na skladě.
nyní řekněme, že chceme mít kartu „Aktivní“, která ukazuje, kolik produktů je na skladě, ale chceme se starat pouze o aktualizaci jedné stránky tabulky.
- zadejte náš vzorec Vlookup do nejvyšší buňky — v našem případě používáme
=Vlookup(A2,Called!A2:B5,2,false)in cell C2.
- přetáhněte indikátor vpravo dole dolů a použijte jej pro každý produkt (modrá šipka).
nyní naše“ aktivní „stránka Stahuje hodnoty ze stránky“ s názvem“. Jednou z výhod používání Vlookup je, že produkty se budou stále shodovat, pokud je obě tabulky uvedou v jiném pořadí.
také nebude vyhozen, pokud v odkazované tabulce chybí produkt.
VLOOKUP z jiného listu v jiném sešitu
proces odkazování dat z jiného sešitu je trochu komplikovanější.
použijeme stejnou produktovou řadu gadgetů, Gizmos, Thingamabobs a widgetů z předchozího příkladu.
Tentokrát chceme přinést stejné informace do listu s názvem „venku“ v jiném sešitu.
chceme přinést informace z“ volaného „listu v jiném sešitu:
nyní řekněme, že chceme mít kartu „mimo“, která ukazuje, kolik produktů je na skladě, ale chceme se starat pouze o aktualizaci stránky tabulky“ nazvané “ v původním sešitu.
zadejte náš vzorec Vlookup do nejvyšší buňky-v našem případě používáme
=Vlookup(A2,importrange("https://docs.google.com/spreadsheets/d/18nsDPJ-","Called!A2:B5"),2,false)in cell C2.
nyní náš druhý sešit odkazuje na počet zásob prvního sešitu:
to může být velmi užitečné pro někoho, kdo analyzuje informace v prvním listu bez rizika chyby k původním datům.
je také velmi užitečné pro uvedení zlomku informací v tabulce pro snadnější analýzu v jiném. Příkaz Vlookup usnadňuje přinést pouze požadované informace.
některé tipy při použití VLOOKUP odkazovat na jiný list / sešit
zde je několik tipů, které je třeba mít na paměti při odkazování na jiný list nebo sešity ve vzorci:
buďte konkrétní ohledně rozsahu
funkce Vlookup může být velmi náročná na výkon a způsobit procházení výkonu sešitu.
můžete se vyhnout pomalému výkonu tím, že budete specifičtí s rozsahy, které odkazujete.
- místo volání celých sloupců jako „A:B „odkazujte na konkrétní počáteční a koncové buňky jako „A1: B1000“. Tím se snižuje, kolik práce musí Tabulky Google udělat, aby přinesly stejné množství informací.
- pokud hledáte sloupec a pro informace a přinášíte zpět výsledek ve sloupci D, použijte odkaz jako „A1:D1000“ místo „A1: F1000“. Není třeba odkazovat na sloupce E A F, pokud jsou v rozsahu, pokud nejsou používány.
to je zvláště důležité, když voláte informace mezi různými sešity. Když děláte Cross-sešit Vlookup, to vyžaduje Internet šířku pásma pro přenos dat mezi těmito dvěma.
použijte podmíněné příkazy, abyste zabránili zbytečným hovorům
dalším způsobem, jak zabránit zpomalení pomocí Cross-sheet Vlookup, je použít podmíněný příkaz k určení, zda by Tabulky Google měly Vlookup vůbec spustit.
pokud jsou například na listu informace, které používáte volání Vlookup, které vám říká, že není třeba jej spouštět, použijte to ve svůj prospěch.
v našem příkladu produktu uvádí list „aktivní“, zda je produkt skladem. Protože víme, že produkt není na skladě, nemusíme používat Vlookup k definování počtu zásob.
k tomu použijeme funkci “ = if ()“. Tato funkce se zeptá, zda je daná podmínka pravdivá nebo nepravdivá, a pak pro každý případ udělá něco jiného. Syntaxe vypadá takto:
=if(logical_expression, value_if_true, value_if_false)
v jednoduchém případě použití jej můžeme použít k určení, zda je hodnota v buňce A1 větší než hodnota v buňce B1. Takže výraz A1>B1 by vypadal takto:
=if(A1>B1, "A1 is greater", "B1 is greater")
vzorec vrátí text „A1 je větší“, pokud A1 je větší číslo a „B1 je větší“, pokud B1 je větší číslo.
v případě našeho listu produktu, pokud je hodnota „na skladě“ „ne“ Nechceme spustit Vlookup. Takže jsme nastavili naše if prohlášení takhle:
- logical_expression: B2= “ Ano “ – spustí se „hodnota, pokud je pravda“, pokud jsou data v buňce B2 „Ano“.
- Value_if_true: Vlookup (A2, Volal!A2: B5, 2, false) – spustí se Vlookup, pokud logický výraz vrátí true.
- Value_if_false: „není skladem — – vrátí se text „není skladem“, pokud je hodnota v buňce B2 něco jiného než “ Ano.“
když to všechno dáme dohromady, vypadá to takto:
=IF(B2="YES",Vlookup(A2,Called!A2:B5,2,false),"out of stock")
Všimněte si, jak buňka C3 nyní říká „vyprodáno“ místo vrácení hodnoty „0“. V tomto případě jsme se vyhnuli spuštění Vlookup, protože jsme nemuseli vracet data.
zatímco zadáváte více informací do tabulek Google pro výpočet výsledků, vytváří méně práce pro program.
je mnohem méně práce spustit mnoho „if kontrol“ než jeden Vlookup. Použití této techniky vám pomůže urychlit Tabulky Google a zlepšit výkon.
ujistěte se, že máte oprávnění
ze zřejmých bezpečnostních důvodů vám Tabulky Google nedovolí stahovat data z jiného sešitu, pokud k tomu nemáte oprávnění.
Chcete-li odkazovat na jeden sešit od druhého pomocí Vlookup, musíte být buď tvůrcem obou, nebo mít oprávnění k použití obou. Můžete být přidáni jako autorizovaný uživatel buď pomocí účtu, nebo prostřednictvím adresy URL sdílení.
použití Vlookup odkazovat informace napříč různými listy a sešity je neuvěřitelně mocný nástroj mít k dispozici.
příkaz je obzvláště užitečný při použití křížového listu, protože bude odrážet všechny změny provedené v původním listu ve všech odkazovaných listech.