Znajdź podobne dane w dwóch arkuszach i umieść je w trzecim arkuszu

Jaki jest najlepszy / najłatwiejszy sposób łączenia danych z dwóch różnych arkuszy kalkulacyjnych Excel w trzeci arkusz kalkulacyjny Excel? Arkusz kalkulacyjny 1 będzie miał tylko nazwę użytkownika. Arkusz kalkulacyjny 2 zawiera wiele informacji, w tym imię, nazwisko, dział, nazwę użytkownika itp. Potrzebny jest trzeci arkusz kalkulacyjny do wyświetlania nazw użytkowników z arkusza kalkulacyjnego 1 z imieniem, nazwiskiem i działem z arkusza kalkulacyjnego 2.

Funkcja, której chcesz użyć, to VLOOKUP. Sposób, w jaki to zrobisz, będzie nieco zależał od sposobu ułożenia arkuszy, ale wszystkie będą miały tę samą składnię:

= WYSZUKAJ.PIONOWO ( lookup value, table array, column index number, range lookup )

• lookup value to dane, które chcesz wyszukać.
• table array definiuje komórki, z których chcesz pobrać dane, w tym kolumnę zawierającą cel wyszukiwania
• column index number jest indeksem kolumny wewnątrz table array z którego chcesz pobierać informacje. (np .: dla tablicy A: E, kolumna D byłaby 4.)
• range lookup jest opcją PRAWDA / FAŁSZ, aby określić, czy przybliżone dopasowanie jest dopuszczalne, czy potrzebne jest dokładne dopasowanie. Aby zachować prostotę, zawsze ustawiałem to na FALSE. Naciśnij F1 w Excelu, jeśli potrzebujesz więcej szczegółów.

Format twojej formuły będzie się nieznacznie różnić w zależności od tego, czy wszystkie dane znajdują się w tym samym skoroszycie, czy nie. Podam przykłady dla każdego poniżej.

WAŻNA UWAGA: Wyszukiwany termin, którego używasz jako lookup value musi można znaleźć w pierwszej kolumnie table array dla WYSZUKAJ.PIONOWO do pracy.

Pierwszy przykład : Wszystkie dane będą w tym samym skoroszycie programu Excel, ale na różnych arkuszach. Pierwszy arkusz ma etykietę „Nazwy użytkownika” i zawiera tylko nazwy użytkowników. Drugi arkusz jest nazywany „danymi użytkownika” i zawiera wszystkie dane użytkownika. Nazwiemy trzeci arkusz „Wyniki wyszukiwania”. Arkusz „Dane użytkownika” zawiera pięć kolumn, A:E.

1. Upewnij się, że arkusz kalkulacyjny „Dane użytkownika” zawiera wszystkie nazwy użytkowników w kolumnie A.
2. Skopiuj wszystkie nazwy użytkowników z „Nazwy użytkowników” do „Wyniki wyszukiwania”.
   • Zakładam, że używasz wiersza nagłówka, więc pierwsza nazwa użytkownika w „wynikach wyszukiwania” wyląduje w A2.
3. Formuła B2 na „Wynik wyszukiwania” powinna być: =VLOOKUP(A2,'User Data'!A:B,2,FALSE)
4. Formuła dla B3 na „Wynik wyszukiwania” powinna być: =VLOOKUP(A3,'User Data'!A:B,2,FALSE)
5. Formuła C2 na „Wynik wyszukiwania” powinna być: =VLOOKUP(A2,'User Data'!A:C,3,FALSE)

Powinieneś teraz zobaczyć ten wzór. Dla każdej kolumny powinieneś po prostu napisać formułę WYSZUKAJ.PIONOWO w pierwszej komórce (np .: B2), a następnie wypełnić formułę resztą arkusza. Jednak wycinanie i wklejanie formuły przez kolumny nie są takie proste - musisz zaktualizować table array i column index number wartości.

Drugi przykład : Każdy zestaw danych jest przechowywany w swoim własnym skoroszycie programu Excel. Nazwy arkuszy w skoroszycie są domyślne (tj .: pierwszy arkusz to „Arkusz1”). Nazwy plików skoroszytu to „Usernames.xlsx”, „User Data.xlsx” i „Lookup Results.xlsx”. Wszystkie one znajdują się w folderze o nazwie „My Spreadsheets”, który znajduje się na pulpicie użytkownika o nazwie „Me”.

1. Upewnij się, że arkusz „User Data.xlsx” zawiera wszystkie nazwy użytkowników w kolumnie A.
2. Skopiuj wszystkie nazwy użytkowników z „Usernames.xlsx” do „Lookup Results.xlsx”.
   • Ponownie, zakładając, że używasz wiersza nagłówka, rozpocznie się on od A2.
3. 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)
4. 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)
5. Formuła 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ć ten wzór już teraz. Wytnij / wklej / dostosuj zgodnie z potrzebami w dół wierszy i kolumn, a zostaniesz ustawiony.

Należy pamiętać o tym, że arkusz nie zostanie automatycznie zaktualizowany o zmiany w danych „Nazwy użytkowników”. Za pomocą tej metody można pobrać zmiany w arkuszu „Dane użytkownika”, ale jeśli chcesz śledzić zmiany w „Nazwach użytkownika”, potrzebne będą bardziej zaawansowane techniki.

Vlookup i podobne działają tylko wtedy, gdy dane w obu arkuszach są identyczne.
Uważam, że potrzebny jest dodatek do logiki rozmytej dla programu Excel. Umożliwi to znalezienie podobnych wyników w oparciu o kilka różnych parametrów. Sprawdź strona pobierania .

WYSZUKAJ.PIONOWO / WYSZUKAJ.PIONOWO - użyj formuły, aby pobrać nazwę użytkownika z arkusza kalkulacyjnego 1, a następnie użyj nazwy użytkownika jako klucza i arkusza kalkulacyjnego 2 jako matrycy wyszukiwania dla jednego wystąpienia WYSZUKAJ.PIONOWO / WYSZUKAJ.PIONOWO (nie wiem, który z nich jest taki, jaki jestem używając wersji innej niż angielska).

Możesz użyć Query z plików Excel:

• Zdefiniuj nazwę zestawu danych w Arkuszu kalkulacyjnym 1 (karta Formuły - & gt; Zdefiniuj nazwę)
• Zdefiniuj nazwę zestawu danych w Arkuszu kalkulacyjnym 2
• Będąc w Arkuszu kalkulacyjnym 1, przejdź do karty Dane, wybierz „Z innych źródeł” iz rozwijanej listy wybierz „Z Microsoft Query”
• Wybierz inny plik arkusza kalkulacyjnego i potwierdź, że chcesz scalić kolumny ręcznie
• W następującym oknie „Query from Excel Files” przeciągnij i upuść kolumnę „username” pierwszego zestawu danych w kolumnie „username” drugiego zestawu danych - zostanie utworzone łącze między tymi kolumnami
• Przejdź do menu Plik, kliknij „Zwróć 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 scal arkusze za pomocą metody przeciągnij i upuść (Zastrzeżenie: Jestem autorem narzędzia).

Mam nadzieję że to pomoże.