Cum se face o îmbinare interioară în Excel (de exemplu, folosind VLOOKUP) (Programare, Sql, Excel, Îmbinare Interioară, Vlookup)

Wabbage a intrebat.
a intrebat.

Există o modalitate de îmbinare interioară a două foi de calcul Excel diferite folosind VLOOKUP?

În SQL, aș face-o în felul următor:

SELECT id, name
FROM Sheet1
INNER JOIN Sheet2
ON Sheet1.id = Sheet2.id;

Foaie1:

+----+------+
| ID | Name |
+----+------+
|  1 | A    |
|  2 | B    |
|  3 | C    |
|  4 | D    |
+----+------+

Sheet2:

+----+-----+
| ID | Age |
+----+-----+
|  1 |  20 |
|  2 |  21 |
|  4 |  22 |
+----+-----+

Și rezultatul ar fi:

+----+------+
| ID | Name |
+----+------+
|  1 | A    |
|  2 | B    |
|  4 | D    |
+----+------+

Cum pot face acest lucru în VLOOKUP? Sau există o modalitate mai bună de a face acest lucru în afară de VLOOKUP?

Mulțumesc.

Comentarii

  • Ați încercat? =VLOOKUP(Sheet2!A1,Sheet1!A:B,2,False) În cazul în care coloana A:A din ambele foi deține id-ul, iar coloana B de pe foaia 1 are numele. Acesta ar merge în B1 în Foaia 2 și ar fi copiat în jos. –  > Por Scott Craner.
  • Există o modalitate prin care să returneze și coloanele cu ID-urile? –  > Por Wabbage.
  • Deci, doriți să populați o foaie nouă doar cu ID-urile găsite în ambele foi și cu numele corespunzător din foaia 1? Va exista vreodată un moment în care vor exista id-uri pe foaia 2 care nu se află pe foaia 1? –  > Por Scott Craner.
  • VLOOKUP nu va duplica rândurile. De exemplu, dacă două rânduri de „A” din tabelul 1 se potrivesc cu trei rânduri „A” din tabelul 2, o îmbinare internă va avea ca rezultat șase rânduri, iar VLOOKUP va avea ca rezultat două rânduri. În cazul în care nu există corespondențe, VLOOKUP are ca rezultat un rând, iar îmbinarea internă nu are niciunul –  > Por Nick.McDermaid.
  • @ScottCraner Da, și da. În cazul celui din urmă, ar arăta astfel acesta. –  > Por Wabbage.
3 răspunsuri
Scott Craner

În primul rând, să obținem o listă de valori care există în ambele tabele. Dacă folosiți excel 2010 sau mai târziu, atunci în foaia 3 A2 puneți următoarea formulă:

=IFERROR(AGGREGATE(15,6,Sheet2!$A$1:$A$5000/(COUNTIF(Sheet1!$A$1:$A$5000,Sheet2!$A$1:$A$5000)>0),ROW(1:1)),"")

Dacă folosiți 2007 sau mai devreme atunci folosiți această formulă de matrice:

=IFERROR(SMALL(IF(COUNTIF(Sheet1!$A$1:$A$5000,Sheet2!$A$1:$A$5000),Sheet2!$A$1:$A$5000),ROW(1:1)),"")

Fiind o formulă array, copiați și lipiți în bara de formule apoi apăsați Ctrl-Shift-Enter în loc de Enter sau Tab pentru a părăsi modul de editare.

Apoi copiați în jos câte rânduri doriți. Astfel se va crea o listă de ID-uri care se află în ambele liste. Acest lucru presupune că ID-ul este un număr și nu un text.

Apoi, cu această listă vom folosi vlookup:

=IF(A2<>"",VLOOKUP(A2,Sheet1!A:B,2,FALSE),"")

Aceasta va returna valoarea din foaia 1 care se potrivește.

Comentarii

  • Vă mulțumim. La ce vă referiți prin copierea rândurilor? –  > Por Wabbage.
  • @PuggyLongLegs Trageți formula în jos cât de mult doriți. Pe măsură ce trage va copia formulele în jos și va schimba referințele. –  > Por Scott Craner.
  • Am făcut acest lucru dar când am apăsat enter, nu s-a întâmplat nimic. Am exact aceleași Sheet1 și Sheet2. –  > Por Wabbage.
  • Ce excel folosești? @PuggyLongLegs –  > Por Scott Craner.
  • @PuggyLongLegs vezi editare, formula originală a fost pentru 2010 sau mai târziu, am adăugat o formulă pentru 2007 sau mai devreme. –  > Por Scott Craner.
Seb

Puteți obține acest rezultat folosind Microsoft Query.

Mai întâi, selectați Data > From other sources > From Microsoft Query

Apoi selectați „Excel Files*”.

În ferestrele „Select Workbook”, trebuie să selectați registrul de lucru curent.

Apoi, în ferestrele „Query Wizard”, selectați foaia1$ și foaia2$ și faceți clic pe butonul „>”.

Faceți clic pe Next (Următorul) și se va deschide editorul vizual al interogării.

Faceți clic pe butonul SQL și lipiți această interogare :

SELECT `Sheet1$`.ID, `Sheet1$`.Name, `Sheet2$`.Age
FROM`Sheet1$`, `Sheet2$`
WHERE `Sheet1$`.ID = `Sheet2$`.ID

În cele din urmă închideți editorul și puneți tabelul acolo unde aveți nevoie de el.

Rezultatul ar trebui să arate astfel :

Comentarii

  • Mulțumesc, dar problema mea este că trebuie să fac toate acestea prin Java (folosind JDBC &; Apache POI). Singurul lucru pe care Excel îl va face de la sine este să afișeze rezultatele calculelor și logica din codul meu Java. –  > Por Wabbage.
  • Puteți să puneți acest lucru în registrul de lucru șablon și apoi să apelați „update all” înainte de a afișa rezultatul calculelor –  > Por Seb.
  • Dar tabelele, coloanele și condițiile mele sunt dinamice. Doar dacă nu pot crea manual un Workbook șablon pe Java de fiecare dată când îmi execut programul. –  > Por Wabbage.
Revizuirea aplicației

Pentru Excel de bază Alăturați-vă fără formualuri sau macro-uri Excel. Vă rugăm să verificați site-ulhttp://exceljoins.blogspot.com/2013/10/excel-inner-join.html

Îmbinările pot Left Outer, Right Outer și Full Outer care se utilizează în ocazii rare, Dar putem realiza acest lucru pentru foile Excel, Pentru mai multe informații, verificați mai joshttp://exceljoins.blogspot.com/

Comentarii

  • Pentru o îmbinare Excel de bază fără formulare sau macro-uri Excel. Vă rugăm să consultați site-ul web exceljoins.blogspot.com/2013/10/excel-inner-join.html Join-urile pot fi Left Outer, Right Outer și Full Outer, care se folosesc în rare ocazii, dar putem realiza acest lucru pentru Excel Sheets, Pentru mai multe informații, consultați site-ul de mai jos exceljoins.blogspot.com –  > Por Revizuirea aplicației.
  • Vă rugăm să postați secțiunile relevante ale răspunsului chiar aici, deoarece legăturile se pot schimba în timp. –  > Por Harsh Wardhan.