Die Auswahl einer Zeile in excel, basierend auf bestimmten Werten in 2 Spalten

Daten etwa im format

A    B    C
ID1  ID2  0.5
ID1  ID3  0.7
ID2  ID3  0.9

Ich möchte erstellen Sie eine Korrelations-matrix (Spalte C die Korrelation zwischen der IDs in A und B). Es kann definitiv getan werden, mit einer pivot-Tabelle, wenn ich die Summe könnte riskant sein, wenn ein Duplikat existiert da ein Fehler vielleicht nicht ersichtlich sein. Ausgabeformat wäre:

    ID1 ID2 ID3
ID1  1  .5  .7
ID2 .5   1  .9
ID3 .7  .9   1

(die '1' ist leicht getan mit einer =IF(B$2=$A3,1,0) und ersetzen Sie 0 mit der Formel zu finden, die Korrelation)

Ich wollen im Grunde eine übereinstimmung (col a= ID1 && col b = ID2). Ich vermute, es könnte getan werden, durch Verkettung, aber ich bin nicht sicher, ob das eine gute Lösung? Match/SVERWEIS etc liefern nur die erste übereinstimmung [in der Spalte], ist das nicht gut für mich. Ode an eine 'where' - Klausel ich denke?

Meine Recherchen ergaben keine Seitenhöhe Hilfe, ich habe es schon berechnet der Korrelations-und sage es in excel aus SQL. Also ja, irgendwelche Ideen wäre super, eine pivot-Tabelle als eine Letzte resort.

Dank.

InformationsquelleAutor N t | 2012-02-02

  1. 1

    Vorausgesetzt, Ihre Quelle Datenbereich in Tabelle1 von A1 bis C3 und Ihre Ergebnisse Bereich in Tabelle2 von A1 bis D4.

    Du kannst diese Formel in B2:

    =SUMPRODUCT((Sheet1!$A$1:$A$3=Sheet2!B$1)*(Sheet1!$B$1:$B$3=Sheet2!$A2)*Sheet1!$C$1:$C$3)

    und dann ziehen Sie diese Formel auf das ganze Sortiment.

    Sie können auch eine ähnliche version aber mit LOOKUP, das hat den Vorteil, dass die Spalte C kann es sich um text oder Zahl, d.h. =LOOKUP(2,1/(Sheet1!$A$1:$A$3=Sheet2!B$1)/(Sheet1!$B$1:$B$3=Sheet2!$A2),Sheet1!$C$1:$C$3)
    interessante Lösung auch. Dies ist wohl eine Antwort Wert 🙂
    Barry, der lookup-version scheint nicht zu funktionieren, ich erhalte eine Fehler in excel. Die Werte werden immer zahlen - aber sumproduct läuft das gleiche Risiko wie eine pivot-Tabelle, wo die doppelten Daten führen würde, die Kick-out-falsche Informationen - aber nicht offensichtlich falsch. So oder so ist es eine geniale Lösung, vielen Dank also Jmax. Ich wusste nicht, könnten Sie verwenden, die Art der logischen Indizierung in Excel.
    Froh, dass es geholfen hat. Ich kann nicht sehen, eine einfache Möglichkeit zu prüfen, für die betrogenen in der gleichen Formel. Noch, könnte man hinzufügen, eine andere Formel zu speziell überprüfen, für die Duplikate in einer anderen Spalte, wenn Sie Sie wirklich benötigen 🙂
    Als ein beiseite, die LOOKUP-Formel funktioniert jetzt, aber nur, wenn ich löschen und dann manuell Eingabe " Tabelle1!$C$2:$C$4 " Haben keine Ahnung, warum. Ich bin auch etwas verwirrt durch die 2 zu haben scheint keine Auswirkung auf die Formel. Problem gut gelöst, obwohl.

    InformationsquelleAutor JMax

  2. 0

    Warum gehst du nicht erstellen Sie eine Dritte Spalte, die vereint die Werte aus den Spalten A und B mit =A1&B1 tun, und dann vlookup auf, Wert:

    A    B    C       D
ID1  ID2  ID1ID2  0.5
ID1  ID3  ID1ID3  0.7
ID2  ID3  ID2ID3  0.9

    InformationsquelleAutor ktdrv

Schreibe einen Kommentar