Jaki jest najlepszy/najprostszy sposób na połączenie danych z dwóch różnych arkuszy Excela w trzecim arkuszu Excela? Arkusz kalkulacyjny 1 będzie zawierał tylko nazwę użytkownika. Arkusz kalkulacyjny 2 będzie zawierał wiele informacji, w tym imię, nazwisko, dział, nazwę użytkownika itd. Trzeci arkusz powinien wyświetlać nazwy użytkowników z arkusza kalkulacyjnego 1 wraz z imieniem, nazwiskiem i działem z arkusza kalkulacyjnego 2.
Odpowiedzi (4)
Funkcja, której chcesz użyć, to VLOOKUP. Sposób, w jaki to zrobisz, zależy nieco od tego, jak ułożone są twoje arkusze, ale wszystkie będą miały taką samą składnię:
=VLOOKUP(lookup value, table array, column index number, range lookup)
lookup valueto dane, które chcesz wyszukać.table arraydefiniuje komórki, z których chcesz pobrać dane, w tym kolumnę zawierającą cel wyszukiwaniacolumn index numberjest indeksem kolumny w obrębietable array, z której chcesz pobrać informacje. (np.: Dla tablicy A:E, kolumna D będzie miała indeks4.)range lookupjest opcją TRUE/FALSE określającą, czy akceptowalne jest dopasowanie przybliżone, czy też wymagane jest dopasowanie dokładne. Aby zachować prostotę, zawsze ustawiam tę opcję naFALSE. Naciśnij F1 w Excelu, jeśli potrzebujesz więcej szczegółów.
Format Twojej formuły będzie się nieco różnił w zależności od tego, czy wszystkie Twoje dane znajdują się w tym samym skoroszycie, czy nie. Poniżej podam przykłady dla każdego z nich.
WAŻNA UWAGA: Aby funkcja VLOOKUP działała, szukane hasło, którego używasz jako lookup value musi_ znajdować się w pierwszej kolumnie table array.
Pierwszy przykład : Wszystkie dane będą znajdowały się w tym samym skoroszycie Excela, ale w różnych arkuszach. Pierwszy arkusz jest oznaczony jako “Nazwy użytkowników” i zawiera tylko nazwy użytkowników. Drugi arkusz nosi nazwę “Dane użytkowników” i zawiera wszystkie dane użytkowników. Trzeci arkusz nazwiemy “Wyniki wyszukiwania”. Arkusz “Dane użytkownika” zawiera pięć kolumn, A:E.
- Upewnij się, że w arkuszu “Dane użytkownika” wszystkie nazwy użytkowników znajdują się w kolumnie A.
- Skopiuj wszystkie nazwy użytkowników z zakładki “Nazwy użytkowników” do zakładki “Wyniki wyszukiwania”.
- Zakładam, że używasz wiersza nagłówkowego, więc pierwsza nazwa użytkownika w “Lookup Results” wyląduje w A2.
- Formuła dla B2 w “Lookup Result” powinna być następująca:
=VLOOKUP(A2,'User Data'!A:B,2,FALSE) - Formuła dla B3 na “Lookup Result” powinna być:
=VLOOKUP(A3,'User Data'!A:B,2,FALSE) - Formuła dla C2 na “Lookup Result” powinna być:
=VLOOKUP(A2,'User Data'!A:C,3,FALSE)
Powinieneś już widzieć tutaj wzór. Dla każdej kolumny powinieneś być w stanie po prostu wpisać formułę VLOOKUP w pierwszej komórce (np.: B2), a następnie wypełnić nią resztę arkusza. Jednak wycinanie i wklejanie formuły poprzez kolumny nie jest takie proste - musisz zaktualizować wartości table array i column index number.
Przykład drugi : Każdy zestaw danych przechowywany jest w osobnym skoroszycie Excela. Nazwy arkuszy w skoroszycie są domyślne (np.: Pierwszy arkusz to “Arkusz1”). Nazwy plików skoroszytu to “Usernames.xlsx”, “User Data.xlsx” oraz “Lookup Results.xlsx”. Wszystkie te pliki znajdują się w folderze o nazwie “Moje arkusze kalkulacyjne”, który znajduje się na pulpicie użytkownika o nazwie “Ja”.
- Upewnij się, że w arkuszu “Dane użytkownika.xlsx” wszystkie nazwy użytkowników znajdują się w kolumnie A.
- Skopiuj wszystkie nazwy użytkowników z arkusza “Usernames.xlsx” do arkusza “Lookup Results.xlsx”.
- Ponownie, zakładając, że używasz wiersza nagłówka, rozpocznie się to w A2.
- Formuła dla B2 w “Lookup Result.xlsx” powinna być
=VLOOKUP(A2,'C:\Users\Me\Desktop\My Spreadsheets\[User Data.xlsx]Sheet1'!A:B,2,FALSE) - Formuła dla B3 w “Lookup Result.xlsx” powinna być
=VLOOKUP(A3,'C:\Users\Me\Desktop\My Spreadsheets\[User Data.xlsx]Sheet1'!A:B,2,FALSE) - Formuła dla C2 w “Lookup Result.xlsx” powinna być
=VLOOKUP(A2,'C:\Users\Me\Desktop\My Spreadsheets\[User Data.xlsx]Sheet1'!A:C,3,FALSE)
Ponownie, powinieneś być w stanie zobaczyć wzór tutaj do teraz. Wytnij/wklej/przestaw, jak trzeba, w dół wierszy i w poprzek kolumn, i jesteś gotowy.
Jeszcze jedna rzecz, o której należy pamiętać, to fakt, że arkusz ten nie będzie automatycznie aktualizowany w przypadku zmian w danych “Nazwy użytkowników”. Zmiany w arkuszu “Dane użytkownika” mogą być śledzone za pomocą tej metody, ale bardziej zaawansowane techniki będą potrzebne, jeśli chcesz śledzić również zmiany w “Nazwach użytkowników”.
Vlookup i podobne będą działać tylko wtedy, gdy dane w obu arkuszach są identyczne.
Myślę, że to, czego potrzebujesz, to dodatek do Excela z logiką rozmytą. To pozwoli Ci znaleźć podobne wyniki na podstawie kilku różnych parametrów. Sprawdź na stronie download page .
HLOOKUP/VLOOKUP - użyj formuły, aby pobrać nazwę użytkownika z arkusza kalkulacyjnego 1, a następnie użyj nazwy użytkownika jako klucza, a arkusza kalkulacyjnego 2 jako macierzy wyszukiwania dla jednej instancji HLOOKUP/VLOOKUP (nie wiem, która jest która, ponieważ używam nieangielskiej wersji Excela).
Możesz użyć funkcji Query from Excel Files:
- Zdefiniować nazwę dla zbioru danych w Arkuszu kalkulacyjnym 1 (zakładka Formuły -> Zdefiniuj nazwę)
- Zdefiniować nazwę dla zbioru danych w Arkuszu kalkulacyjnym 2
- Będąc w Arkuszu kalkulacyjnym 1, przejdź do zakładki Dane, wybierz “Z innych źródeł”, a z rozwijanej listy wybierz “Z Microsoft Query”
- Wybierz drugi plik arkusza kalkulacyjnego i potwierdź, że chcesz połączyć kolumny ręcznie
- W kolejnym oknie “Query from Excel Files”, przeciągnij i upuść kolumnę “nazwa użytkownika” z pierwszego zbioru danych do kolumny “nazwa użytkownika” z drugiego zbioru danych - zostanie utworzone połączenie między tymi kolumnami
- Przejdź do menu Plik, kliknij “Przywróć dane do MS Office Excel”, pojawi się okno dialogowe Importuj dane
- Wybierz arkusz, do którego chcesz zaimportować dopasowane dane
- Kliknij OK - powinieneś zobaczyć dopasowane dane z kolumnami z obu arkuszy kalkulacyjnych
Lub jeśli nie masz nic przeciwko przesyłaniu plików do usługi online, możesz użyć na przykład http: //www. gridoc.com/join-tables i połączyć arkusze za pomocą drag&drop (Disclaimer: Jestem autorem tego narzędzia).
Mam nadzieję, że to pomoże.