Jak VLOOKUP z innego arkusza w Arkuszach Google
możesz odblokować pełny potencjał Vlookup (wyszukiwanie pionowe) w Arkuszach Google, używając go do wywoływania danych w różnych arkuszach kalkulacyjnych i skoroszytach.
funkcja Vlookup to niezwykle potężna funkcja, która może zmienić godziny pracy w zaledwie kilka sekund. Chociaż funkcja jest przydatna do analizy danych na pojedynczym arkuszu, możesz jeszcze bardziej zwiększyć użyteczność Vlookup, wywołując dane w różnych arkuszach kalkulacyjnych.
Dzięki tej funkcji możesz szybko i skutecznie wyszukiwać i pobierać określone wartości z dużych ilości danych.
możliwość korzystania z niego w wielu arkuszach ułatwia zachowanie czystości danych i automatycznie aktualizuje się w przypadku zmian między arkuszami.
Ten artykuł obejmuje:
formuła VLOOKUP
składnia VLOOKUP jest dość prosta, ale może być używana w bardzo złożonym stopniu.
jak sugeruje” pionowy ” w swojej nazwie, Vlookup przeszukuje lewą kolumnę w ustalonym zakresie w celu dopasowania wiersza i zwraca wartość w komórce zidentyfikowanej przez indeks.
przeszukuje kolumnę i zwraca wartość z pasującego wiersza, zwykle z innej kolumny.
jedną z największych słabości Vlookup jest to, że nie można wyszukać dopasowania w kolumnie po prawej stronie i przywrócić wynik z kolumny po lewej stronie. Innymi słowy, Vlookup może tylko patrzeć lub skręcić w prawo.
tak wygląda wzór w podziale na części:
=Vlookup(search_key,range,index,is_sorted)
spójrzmy na każdy komponent indywidualnie:
- =Vlookup (): jest to funkcja bez żadnych parametrów. To jest to, co mówi Arkusze Google do działania.
- search_key: ten parametr określa, jakie informacje chcemy dopasować. Może to być nazwa, liczba, wartość logiczna lub coś innego. Może to być wartość statyczna, którą zdefiniowaliśmy lub względna wartość przechowywana w komórce. Jeśli ustawimy to jako” A2″, wyszukamy wartość w komórce”A2″. Jeśli ustawimy go na „true”, będzie pasował do ciągu tekstowego ” true.”
- : Informuje to funkcję Vlookup, gdzie szukać dopasowania i zakres, w którym znajduje się wartość, którą ma zwrócić. Jeśli szukamy dopasowania wartości w kolumnie B i chcemy zwrócić wartość w kolumnach C lub D, ustawimy zakres jako B: D.
- index: ten parametr mówi Vlookup, którą wartość kolumny zwrócić. Indeks jest względny do zakresu, a nie arkusza. Stąd jest to liczba zamiast litery, jak kolumny są zwykle zdefiniowane. Jeśli nasze wyszukiwanie pasuje do kolumny B i zwraca wartość z kolumny C, wartość indeksu wynosi 2. Jeśli zwracamy kolumnę D z dopasowania do kolumny B, wartość indeksu wynosi 3.
- is_sorted: nazwa tego parametru nie jest tak jasna, jak zwraca dokładne dopasowanie, gdy jest ustawiony na „false”, a najbliższy dopasowanie, gdy jest ustawiony na „true”.”True jest ustawione domyślnie, ale false jest zalecane w większości zastosowań.
teraz rzućmy okiem na to wszystko razem wzięte:
=Vlookup(A2,A2:B5,2,false)
używanie VLOOKUP w tym samym skoroszycie i w innym skoroszycie
chociaż przez większość czasu używamy formuły VLOOKUP w Arkuszach Google na tym samym arkuszu, wiele razy może być konieczne użycie jej do wyszukiwania między dwoma arkuszami w tym samym skoroszycie lub nawet w innym skoroszycie.
na przykład możesz pobrać dane z określonych elementów w arkuszu roboczym, podczas gdy dane Wyszukiwania znajdują się w innym arkuszu lub innym skoroszycie.
sposób korzystania z funkcji Vlookup do wprowadzania danych z innego arkusza jest nieco inny, gdy pracujesz w tym samym skoroszycie lub innym skoroszycie.
wzór dla tego samego skoroszytu wygląda następująco:
=vlookup(search_key,{sheet name}!{cell range},index,is_sorted)
zauważ, że jest”!”między nazwą arkusza a zakresem komórek. Ponadto nie ma cudzysłowów w zakresie tego samego skoroszytu.
w naszym teście chcemy ustawić zakres Na A3:B6 na naszym arkuszu o nazwie „Called.”Szukamy dopasowania w kolumnie A i zwracamy wartość w kolumnie B.
Poniżej znajduje się formuła, która to zrobi:
=Vlookup(A2,Called!A2:B5,2,false)
powyższa formuła pobiera wartość z drugiej kolumny z arkusza o nazwie „Called” w bieżącym arkuszu.
teraz spójrzmy na przykład, w którym musimy pobrać wartość z innego skoroszytu Arkuszy Google
=vlookup(search_key,importrange("{sheetsURL}","{sheet name}!{cell range}"),index,is_sorted)
ta wersja używa nowego polecenia o nazwie „importrange()”.
składnia tego polecenia prosi o zdefiniowanie adresu URL skoroszytu, z którego importujesz dane, zdefiniowanie określonego arkusza i ustawienie zakresu.
formuła rozpada się tak:
Importrange("{sheetsURL}","{sheet name}!{cel range}")
- {sheetsURL}: w cudzysłowach Dodaj adres URL pliku Arkuszy Google, do którego chcesz uzyskać dostęp. Przykład: „https://docs.google.com/spreadsheets/d/1AJcuVkYvdiW0NAlfuI”
- {nazwa arkusza}!{cel range}: ustaw to tak samo, jak skonfigurowałeś to w tej samej metodzie skoroszytu. Zauważ jednak, że tym razem wokół nazwy arkusza i zakresu komórek znajdują się cudzysłowy.
teraz spójrz na to z wartością dodaną i wszystkimi wartościami dodanymi :
=Vlookup(A2,importrange("https://docs.google.com/spreadsheets/d/18nsDPJ","Called!A2:B5"),2,false)
aby powtórzyć, istnieją trzy ważne wartości, które należy ustawić w odwołaniu do przeszukiwania arkusza krzyżowego:
- Url skoroszytu {sheetsURL}
- strona arkusza {nazwa arkusza}
- zakres komórek {zakres komórek}:
przechodząc przez proces Vlookup
zobaczmy, jak działa VLOOKUP podczas odwoływania się do innych arkuszy lub skoroszytów.
VLOOKUP z innego arkusza roboczego w tym samym skoroszycie
użyjmy prostego przypadku inwentarza produktów, aby pokazać, jak działa Vlookup.
w tym przykładzie mamy cztery produkty: Gadżety, gadżety, Thingamabobs i widgety. Obecnie nie mamy żadnych gadżetów w magazynie. Nasz pierwszy arkusz, „aktywny”, zawiera listę wszystkich naszych produktów i mówi nam, czy produkt jest w magazynie:
mamy również drugi arkusz o nazwie „wywołany”, który zawiera listę, ile każdego produktu mamy w magazynie.
teraz powiedzmy, że chcemy mieć Kartę „Active” pokazującą, ile produktów jest w magazynie, ale chcemy martwić się tylko o aktualizację jednej strony arkusza kalkulacyjnego.
- wprowadź naszą formułę Vlookup w najwyższej komórce — w naszym przypadku używamy
=Vlookup(A2,Called!A2:B5,2,false)in cell C2.
- przeciągnij prawy dolny wskaźnik w dół, aby zastosować go dla każdego produktu (niebieska strzałka).
teraz nasza” aktywna „strona pobiera wartości ze strony „wywołane”. Jedną z zalet korzystania z Vlookup jest to, że produkty nadal będą pasować, jeśli oba arkusze kalkulacyjne wyświetlą je w innej kolejności.
również nie zostanie wyrzucony, jeśli brakuje produktu w odwołującym się arkuszu kalkulacyjnym.
wyszukaj z innego arkusza roboczego w innym skoroszycie
proces odwoływania się do danych z innego skoroszytu jest nieco bardziej skomplikowany.
użyjemy tego samego asortymentu gadżetów, gadżetów, gadżetów i widżetów z poprzedniego przykładu.
tym razem chcemy wprowadzić te same informacje do arkusza o nazwie „Na zewnątrz” w innym skoroszycie.
chcemy wprowadzić informacje z arkusza” o nazwie ” w innym skoroszycie:
teraz powiedzmy, że chcemy mieć zakładkę „Na Zewnątrz” pokazującą, ile produktów jest w magazynie, ale chcemy się martwić tylko o aktualizację strony „o nazwie” arkusza kalkulacyjnego w oryginalnym skoroszycie.
wpisz naszą formułę Vlookup w najwyższej komórce — w naszym przypadku używamy
=Vlookup(A2,importrange("https://docs.google.com/spreadsheets/d/18nsDPJ-","Called!A2:B5"),2,false)in cell C2.
teraz nasz drugi skoroszyt odnosi się do liczby inwentaryzacji pierwszego skoroszytu:
może to być bardzo przydatne dla kogoś, kto analizuje informacje w pierwszym arkuszu bez ryzyka błędu w oryginalnych danych.
jest to również bardzo przydatne do wprowadzenia ułamka informacji w arkuszu kalkulacyjnym w celu łatwiejszej analizy w innym. Polecenie Vlookup ułatwia wprowadzanie tylko tych informacji, które chcesz.
kilka wskazówek dotyczących korzystania z VLOOKUP do odwoływania się do innego arkusza/skoroszytu
oto kilka wskazówek, o których należy pamiętać, odwołując się do innego arkusza lub skoroszytów we wzorze:
bądź konkretny w zakresie
funkcja Vlookup może być bardzo wymagająca wydajności i spowodować, że wydajność skoroszytu dojdzie do indeksowania.
możesz uniknąć niskiej wydajności, określając zakresy, do których się odwołujesz.
- zamiast wywoływać całe kolumny jak ” A:B ” odnoszą się do konkretnych komórek początkowych i końcowych, takich jak „A1:B1000”. Zmniejsza to ilość pracy, jaką Arkusze Google muszą wykonać, aby dostarczyć taką samą ilość informacji.
- jeśli szukasz informacji w kolumnie A i przywracasz wynik w kolumnie D, użyj referencji w rodzaju „A1:D1000” zamiast „A1:F1000”. Nie ma potrzeby odwoływania się do kolumn E i F, jeśli znajdują się w zakresie, jeśli nie są używane.
jest to szczególnie ważne, gdy dzwonisz do informacji między różnymi skoroszytami. Podczas przeglądania skoroszytu między skoroszytami wymagana jest przepustowość Internetu do przesyłania danych między nimi.
użyj instrukcji warunkowych, aby zapobiec niepotrzebnym wywołaniom
innym sposobem zapobiegania spowolnieniu za pomocą przeszukiwania arkuszy krzyżowych jest użycie instrukcji warunkowej do określenia, czy Arkusze Google powinny w ogóle uruchamiać przeszukiwanie.
na przykład, jeśli na arkuszu używasz wywołania Vlookup znajdują się informacje, które mówią, że nie ma potrzeby jego uruchamiania, użyj tego na swoją korzyść.
w naszym przykładzie produktu „aktywny” arkusz zawiera listę, czy produkt jest dostępny w magazynie. Ponieważ wiemy, że produkt jest niedostępny, nie musimy używać Vlookup do definiowania liczby zapasów.
w tym celu użyjemy funkcji „=if ()”. Ta funkcja pyta, czy dany warunek jest true lub false, a następnie robi coś innego dla każdego przypadku. Składnia wygląda następująco:
=if(logical_expression, value_if_true, value_if_false)
w prostym przypadku użycia możemy go użyć do określenia, czy wartość w komórce A1 jest większa niż wartość w komórce B1. Tak więc wyrażenie A1> B1 wyglądałoby tak:
=if(A1>B1, "A1 is greater", "B1 is greater")
formuła zwróci tekst „A1 jest większe”, jeśli A1 jest większą liczbą i „B1 jest większe”, jeśli B1 jest większą liczbą.
w przypadku naszego arkusza roboczego produktu, jeśli wartość „w magazynie” wynosi „Nie”, Nie chcemy uruchamiać Vlookup. Więc ustawiliśmy nasze oświadczenie if W ten sposób:
- logical_expression: B2= ” YES „- spowoduje to uruchomienie” value if true”, jeśli dane w komórce B2 są”YES”.
- Value_if_true: Vlookup (A2, Called!A2: B5,2,false) –spowoduje to uruchomienie podglądu, jeśli wyrażenie logiczne zwróci true.
- Value_if_false: „out of stock ” – zwróci tekst” out of stock”, jeśli wartość w komórce B2 jest inna niż ” YES.”
jeśli poskładamy to wszystko do kupy, to wyjdzie to tak.:
=IF(B2="YES",Vlookup(A2,Called!A2:B5,2,false),"out of stock")
zauważ, jak komórka C3 mówi teraz „out of stock” zamiast zwracać wartość „0”. W tym przypadku uniknęliśmy uruchamiania Vlookupu, ponieważ nie musieliśmy przywracać danych.
podczas wprowadzania więcej informacji do Arkuszy Google w celu obliczenia wyników, tworzy to mniej pracy dla programu.
uruchamianie wielu „sprawdzeń if” jest znacznie mniej pracy niż pojedynczy Vlookup. Korzystanie z tej techniki pomoże Ci przyspieszyć Arkusze Google i poprawić wydajność.
upewnij się, że masz pozwolenie
z oczywistych względów bezpieczeństwa Arkusze Google nie pozwolą Ci pobrać danych z innego skoroszytu, chyba że masz do tego upoważnienie.
aby odwołać się do jednego skoroszytu z drugiego za pomocą Vlookup, musisz być twórcą obu lub mieć uprawnienia do korzystania z obu. Możesz zostać dodany jako autoryzowany użytkownik za pomocą konta lub adresu URL udostępniania.
używanie Vlookup do odwoływania się do informacji w różnych arkuszach i skoroszytach jest niezwykle potężnym narzędziem do twojej dyspozycji.
polecenie jest szczególnie przydatne przy użyciu arkuszy krzyżowych, ponieważ odzwierciedla wszelkie zmiany wprowadzone w oryginalnym arkuszu we wszystkich powiązanych arkuszach.