☰

Datenbeispiel

tag-icon • tag-icon microsoft-excel worksheet-function
Datenbeispiel

Ich schreibe ein Organisationsblatt für ein junges E-Sport-Team und mein Ziel besteht darin, den durchschnittlichen In-Game-Rang der Spieler in meinem Team zu berechnen.

Zelle F2und jedeandereJede Zeile in der Spalte Fhat eine Rangkennung, etwa „S3“ oder „G1“ (für die Ränge Silber 3 oder Gold 1).

Ich habe ein weiteres Blatt, das numerische Zuordnungen zwischen diesen Rangkennungen enthält (I1 ist 1, I2 ist 2 usw.).

Also muss ich für jede andere Zeile in diesem Bereich ( F2:F100) die Kennung abrufen, sie mithilfe von in die numerische Form umwandeln VLOOKUP, Lookupseine des Ergebnisses durchführen FLOOR(AVERAGE()und diese Zahl durch Umkehren der wieder in eine Textform umwandeln VLOOKUP.

Ich habe einige Schritte bereits erledigt, bin mir aber nicht sicher, wie ich die Ergebnisse einer vorherigen Formel auf diese Weise mitteln kann.

Um die numerische Version eines Rangs aus seiner Textversion zu erhalten, lautet die Formel:

=VLOOKUP(F2, Lookups!A2:B29,2, FALSE)

Was ich nicht weiß ist:

  • So erstellen Sie einen Zellbezug, der jede andere Zelle auslässt
  • So berechnen Sie den Durchschnitt einer großen Anzahl von SVERWEIS-Ergebnissen

Datenbeispiel

Großblatt

Haupttabelle

Nachschlagwerk

Nachschlagetabelle

Jeder Satz von zwei Spalten stellt einen einzelnen Spieler dar, wobei der Rang dieses Spielers in der F-Zeile der geraden Spalte gespeichert ist. Aus dem Nachschlageblatt können wir ersehen, dass S3 eine numerische Punktzahl von 10 darstellt. Die G2 des nächsten Spielers hätte eine Punktzahl von 15 und so weiter.

Sobald ich die Summe dieser Zahlen habe, nehme ich den Durchschnitt und erhalte so eine Durchschnittspunktzahl für das Team. Zurück zur Nachschlagetabelle: Dies geschieht in umgekehrter Reihenfolge wie die vorherige Operation und ergibt eine einzelne Rangfolge, die der Durchschnittspunktzahl aller auf dem Blatt aufgeführten Spieler entspricht.

Wenn ich die drei Spieler nehme, die derzeit auf dem Blatt stehen, würde ich S3, G2 und NA nehmen. Das ergibt eine Punktzahl von 10, 15 und 10. Der Durchschnitt davon ist 11 (abgerundet), was in der Nachschlagetabelle einem Rang von S2 entspricht. Die Zelle, die diese hypothetische Formel enthält, würde „S2“ lauten.

Antwort1

Q:Bleiben F3, F5, F7 usw. leer?
A: Das werden sie. Ich habe das Feld ausgegraut, um anzuzeigen, dass dort nichts eingegeben werden sollte.

Am einfachsten wäre es, diese leeren Zellen zu verwenden, um die numerischen Werte für die einzelnen Ränge zu speichern. Sie können dann den Durchschnitt von F2:F101 berechnen und nur die tatsächlichen Zahlen werden berücksichtigt. Die Zahlen können mithilfe des Zellennummernformats aus der Anzeige ausgeblendet werden ;;;. Dadurch wird für alles, was eingegeben oder von einer Formel zurückgegeben wird, eine leere Zelle angezeigt, während der Rohwert erhalten bleibt.

Wenn Sie nicht 50 Mal kopieren und einfügen möchten, führen Sie diese kurze Unterprozedur aus, nachdem Sie den Namen des Hauptarbeitsblatts in der ersten Codezeile angepasst haben.

Option Explicit

Sub PopulateLookups()

    With Worksheets("sheet1")
        With .Range(.Cells(2, "F"), .Cells(.Rows.Count, "F").End(xlUp))
            With .SpecialCells(xlCellTypeConstants, xlTextValues)  '<~~ see footnote
                With .Offset(1, 0)

                    .FormulaR1C1 = "=vlookup(r[-1]c, lookups!r2c1:r29c2, 2, false)"
                    .NumberFormat = ";;;"

                End With
            End With
        End With
    End With

End Sub

Jetzt können Sie eine der folgenden Möglichkeiten verwenden, um einen Durchschnitt der Nachschlagewerte zu ermitteln.

=average(F2:F100)
=average(F:F)

Wenn Sie diese leeren Zellen nicht anderweitig verwenden möchten, ist eine „Hilfsspalte“ eine brauchbare Alternative. Ich habe die Methode mit leeren Zellen und die mit Hilfsspalten getestet und die Ergebnisse waren identisch.

¹ Wenn die vorhandenen Ränge per Formel zurückgegeben und nicht eingegeben werden, dannxlCellTypeConstantssollte ersetzt werden durchxlCellTypeFormulas.

Antwort2

Ich würde das erste Nachschlageverfahren, bei dem Sie in jeder zweiten Zeile einen Wert benötigen, dadurch lösen, dass ich das Sverweiseverfahren in eine iferror-Formel einfüge, die einfach leeren Text zurückgibt, wenn sie kein Ergebnis findet:

=IFERROR(VLOOKUP(F2,Lookups!A2:B29,2,0),"")

Als Nächstes können Sie einfach die Durchschnittsformel innerhalb einer Abrundungsformel verwenden, um die Dezimalstellen abzuschneiden. Die Durchschnittsformel ignoriert einfach die leeren Zeilen.

Schließlich funktioniert eine Vlookup-Formel nicht, um den „Rang“ aus diesem Durchschnitt zu ermitteln, da sie immer links beginnt und sich nach rechts bewegt – Sie müssen also nach rechts schauen und sich nach links bewegen. Dafür verwende ich eine Index/Match-Kombination:

=INDEX(Lookups!A2:B29,MATCH(E11,Lookups!B2:B29,1),1)

Für den Übereinstimmungsteil habe ich einen „1“-Parameter verwendet, damit der nächste Wert gefunden wird, ohne ihn zu überschreiten, da davon ausgegangen wird, dass es sich möglicherweise nicht um eine exakte Übereinstimmung handelt. Weitere Informationen und Optionen finden Sie in den Beschreibungen der Index- und Übereinstimmungsformeln in Excel.

Ich habe dies in ein Beispielblatt eingebaut, das die ausgeschriebenen Formeln zeigt. Hoffe, das hilft:Musterlösung mit dargestellten Formelbeispielen

Antwort3

Angenommen, Sie möchten den Durchschnitt der geraden Zeilennummern ermitteln, verwenden Sie die folgende Formel:

=IF(ROW()/2 = ROUND(ROW()/2,0),VLOOKUP(A1,H$1:I$5,2,FALSE),"")

Andernfalls verwenden Sie:

=IF(ROW()/2 <> ROUND(ROW()/2,0),VLOOKUP(A1,H$1:I$5,2,FALSE),"")

Verwenden Sie dann =AVERAGE(G1:G5), um den Durchschnitt zu erhalten

verwandte Informationen