Ich habe eine Probe wie diese, bei der ich den nächstliegenden Wert zum Durchschnitt finden möchte
Stadt und Gewicht sind zwei separate Spalten
city weight
A 23
A 22
A 45
A 97
B 34
B 22
B 23
C 76
C 23
C 23
Ich habe eine Pivot-Funktion erstellt und den Durchschnittswert für A- berechnet, der 46,75 beträgt.
Ich muss die nächste Zahl für A finden, die in diesem Fall 45 ist
Ich glaube, ich muss Index und Match verwenden, aber wie würde ich das machen, wenn ich 17.000 Zeilen mit doppelten Städtenamen und unterschiedlichen Gewichtungswerten habe?
Ich wäre für jede Hilfe dankbar
die Antwort, die ich suche, ist
Row Labels Average of WEIGHT nearest number
A 46.75 45
B 38.75 34
C 23 23
Die meisten ähnlichen Antworten verwenden diesen Satz nicht. Bitte helfen Sie mir, diese Formel einzurichten, die ich ausprobiert habe:
INDEX(rawdata,MATCH(MIN(ABS(weight-$B2)),ABS(weight-$B2),0),2)
Aber es betrachtet die gesamte Gewichtsspanne von AC. Ich möchte nur, dass es die Werte für A betrachtet, wenn es den Durchschnitt von A vergleicht,
Und dann das Gewicht von B beim Vergleich des Durchschnitts von B,
UND SO WEITER....
Bitte lassen Sie mich wissen, was mit meiner Formel nicht stimmt.
Dank im Voraus
Antwort1
BEARBEITEN:
Entschuldigung, ich habe Ihre Frage nicht richtig gelesen und erst jetzt festgestellt, dass Sie klar gesagt haben, Sie möchten den WeightWert finden, der dem Durchschnitt am nächsten kommtzu den Werten für die Stadtfür die der Durchschnitt berechnet wurde. Daher habe ich die Antwort unten aktualisiert.
Es sieht so aus, als hätten Sie gefundenAntwort von XOR LXauf eine ähnliche Frage, und Sie sind ziemlich nah dran, die richtige Antwort zu haben.
XOR LX verwendet eine wirklich nette kleine Formel, die die Einschränkungen bei der Suche in ungeordneten Daten umgeht MATCH(). Ich erkläre weiter unten, wie sie funktioniert.
In der unten gezeigten Datentabelle habe ich die Durchschnittswerte wie folgt berechnet:
=AVERAGEIF(A$2:A$11,A14,B$2:B$11)(Ich bekomme andere Antworten als die, die Sie oben gezeigt haben).
und das Weightdem Durchschnitt am nächsten kommende mit:
=INDEX((A$2:A$11=A14)*(B$2:B$11),MATCH(TRUE,(A$2:A$11=A14)*ABS(B$2:B$11-B14)=MIN(IF(A$2:A$11=A14,ABS(B$2:B$11-B14))),0))
CTRLShiftEnterBeachten Sie, dass dies eine Array-Formel ist und daher mit und nicht nur eingegeben werden muss Enter.
______________________________________________________________________________
Wie es funktioniert:
ABS(B$2:B$11-B14)ist ein Array der Differenzen zwischen dem Durchschnitt und allen Zahlen in der WeightListe. Und (A$2:A$11=A14)ist ein Array von True/FalseWerten mit , Truewo immer Citygleich ist A14. Multipliziert man diese beiden miteinander, erhält man ein Array dieser Differenzen in den Positionen, die entsprechen City = A14, mit 0überall sonst.
Als nächstes möchten wir das Minimum dieser Unterschiede finden, müssen dazu aber ein leicht anderes Array erstellen, da MIN()es zurückgibt 0, ob sich welche 0'sim Array befinden.
IF(A$2:A$11=A14,ABS(B$2:B$11-B14))prüft, wo City = A14, und gibt die Unterschiede zwischen Weightund dem Durchschnitt für diese Positionen zurück, mit Falseallen anderen Orten.
Wenn man das Minimum dieses Arrays nimmt, MIN(IF(A$2:A$11=A14,ABS(B$2:B$11-B14)))erhält man den kleinsten Unterschiednur für die Positionen, in denen City = A14.
Jetzt ergibt die Gleichheit (A$2:A$11=A14)*ABS(B$2:B$11-B14)=MIN(IF(A$2:A$11=A14,ABS(B$2:B$11-B14)))ein Array von True/FalseWerten mit Truean der Position der kleinsten Differenz für den aktuellen City. MATCH()findet die Position von True, (das ist die Position der nächsten Zahl) und diese wird an eine weitergeleitet, INDEX()um den tatsächlichen Wert zurückzugeben.
Ich hoffe, das hilft, und viel Glück.