☰

Wie erhält man den ganz rechten Wert einer Zeile in Excel?

tag-icon • tag-icon microsoft-excel
Wie erhält man den ganz rechten Wert einer Zeile in Excel?

Ich muss den Wert der am weitesten rechts stehenden, nicht leeren Zelle jeder Zeile in einem Array anzeigen. Wie kann dies in Excel erreicht werden?

In dieser Beispieltabelle hat die Spalte [Aktuell] das gewünschte Ergebnis:

+---------+----------+---------+----------+
|  2016   |   2017   |  2018   | Current  |
+---------+----------+---------+----------+
|     700 |          |   200   |     200  |
|         |          |         |          |
|         |     450  |         |     450  |
|         |          |  2,700  |   2,700  |
|         |          |         |          |
|  42,350 |  71,500  |         |  71,500  |
|  2,660  |          |         |   2,660  |
|         |   1,100  |         |   1,100  |
|         |          |         |          |
|    470  |          |         |     470  |
+---------+----------+---------+----------+

Variationen des Themas wären der Wert ganz links, ganz oben, ganz unten oder ein Wert größer alsN, usw. Desktop-Excel von Office 2016, sofern die Version relevant ist.

Antwort1

Bildbeschreibung hier eingeben

  • Geben Sie diese Formel ein E2und füllen Sie sie aus.

=LOOKUP(2,1/(A2:C2<>""),A2:C2)

Wie es funktioniert:

  • Die Formel erkennt, dass der Nachschlagewert 2 absichtlich größer ist als alle Werte, die im Nachschlagevektor erscheinen.
  • Der Ausdruck A2:C2<>""gibt ein Array von TrueWerten zurück False.
  • 1wird dann durch dieses Array geteilt und erstellt ein neues Array, das entweder aus Einsen oder Division-durch-Null-Fehlern (#DIV/0!) besteht: {1,0,1,...}.
  • Dieses Array ist der Nachschlagevektor.
  • Wenn die Formel den Nachschlagewert nicht finden kann,
    Lookupwird der nächstkleinere Wert als Übereinstimmung verwendet.
  • In diesem Fall ist der Nachschlagewert 2, aber der größte Wert im Nachschlage-Array ist , sodass die Suche mit dem letzten im Array 1übereinstimmt .1
  • LOOKUP gibt den entsprechenden Wert im Ergebnisvektor zurück, der der Wert an der gleichen Position ist.

:Bearbeitet:

  • Für Google Sheet ist folgende Formel zu verwenden:

    =(IFERROR(LOOKUP( 2, 1 / ( A2:C2 <> "" ), A2:C2 ),""))

  • Beenden Sie es mitStrg+Umschalt+Eingabe, Formel sieht so aus:

    =ArrayFormula(IFERROR(LOOKUP( 2, 1 / ( A2:C2 <> "" ), A2:C2 ),""))

Antwort2

Obwohl es für dieses Problem bereits mehrere Lösungen gibt, ist hier meine bevorzugte Lösung, da sie für mich der natürlichen Denkweise am nächsten kommt:

=INDEX(A2:C2,MAX(IF(A2:C2="","",COLUMN(A2:C2))))- dies ist eine Array-Formel, drücken Sie daher nach der Eingabe CTRL+ SHIFT+ .ENTER

Bildbeschreibung hier eingeben

Wie es funktioniert:

  • IF(A2:C2="","",COLUMN(A2:C2))- Gibt für jede Zelle in der Zeile einen leeren String zurück, wenn die Zelle leer ist, andernfalls die Spaltennummer
  • MAX( ... )- wählt die höchste zurückgegebene Spaltennummer aus
  • =INDEX(A2:C2, ... )- wählt die Zelle aus der Zeile basierend auf der höchsten Spaltennummer aus

Achtung: Es funktioniert nur richtig, wenn Ihr Bereich in der ersten Spalte beginnt, andernfalls muss die Verschiebung ausgeglichen werden, z. B. für einen Bereich, der in Spalte C beginnt:
=INDEX(C2:X2,MAX(IF(C2:X2="","",COLUMN(C2:X2)))-2)

Antwort3

Nehmen wir an, Ihre Tabelle ist in C2:F12 angelegt, wobei die Kopfzeile Zeile 2 und die Zusammenfassungsspalte F ist. Platzieren Sie die folgende Formel in F3 und kopieren Sie sie nach unten.

=IFERROR(INDEX(3:3,AGGREGATE(14,6,column($C3:$E3)/($C3:$E3<>""),1)),"")

POC

NOTIZ:

  • AGGREGATE führt Array-Operationen mit Formelauswahl 14 und 15 aus. Verwenden Sie daher keine vollständigen Spalten-/Zeilenreferenzen innerhalb der AGGREGATE-Funktion, da Ihr System durch die Anzahl der durchzuführenden Berechnungen überlastet werden oder abstürzen könnte. Die Verwendung vollständiger Spaltenreferenzen außerhalb von Array-Funktionen ist in Ordnung. Beachten Sie, dass für INDEX 3:3 verwendet wird.

  • Wenn Sie eine neue Spalte einfügen und Spalte F ausgewählt ist und das Einfügen durchgeführt wird, müssen Sie die Formel in F aktualisieren, sodass C3:F3 der neue Bereich ist. Wenn Sie Spalte E ausgewählt haben und eine neue Spalte einfügen, wird der Bereich automatisch aktualisiert, aber Ihre Daten befinden sich jetzt in der falschen Spalte. Wenn Sie Spalte F leer gelassen, die Formeln stattdessen in Spalte G eingefügt und C3:F3 als Ihren Bereich innerhalb von AGGREGATE verwendet haben, können Sie in Zukunft Spalte F zum Einfügen auswählen und Ihre Formeln werden aktualisiert und Sie können neue Daten in F eingeben. Sie hätten rechts noch eine leere Spalte zur Auswahl im nächsten Jahr, um den Vorgang zu wiederholen.

Antwort4

Ein anderer Ansatz, der uneleganter und brutaler, aber leicht verständlich ist, besteht darin, TEXTJOIN() zu verwenden, nachdem wir es jetzt haben.

Verwenden Sie A2:C2 für die erste Zeile, fügen Sie Folgendes in D2 ein und kopieren Sie es dann nach unten. Oder Füllen Sie aus, oder ... Sie verstehen schon:

FürTextjoin-ZeichenfolgeVerwenden Sie unten die Funktion TEXTJOIN(), um den gesamten Bereich der Zellen, die Sie untersuchen möchten, zu verketten. Verwenden Sie „TRUE“, um Leerzeichen wegzulassen und die Zeichenfolge zu verkürzen, und verwenden Sie als Trennzeichen ein Zeichen, das realistisch gesehen NIEMALS in Ihren Daten vorkommen wird. Ich verwende unten „Ŧ“ (und als Zeichen, um das letzte durch „Ų“ zu ersetzen). Die Verwendung von Kommas, wie dies häufig bei TEXTJOIN() und verwandten Funktionen der Fall ist, kann zu Problemen führen.

=RIGHT( Textjoin string,
LEN( Textjoin string ) -
FIND( "Ų", SUBSTITUTE( Textjoin string, "Ŧ", "Ų",
LEN( Textjoin string **with** delimiter ) - LEN( Textjoin string **without** delimiter )
)))

und es ist einfacher zu verstehen. SUBSTITUTE() kann seine Arbeit beginnend mit einemInstanznummerDamit finden Sie die letzte Verwendung Ihres Trennzeichens inTextjoin-Zeichenfolge mit Trennzeichen. In der letzten Zeile finden Sie die LEN() des Textjoin-Strings mit und ohne Trennzeichen und ermitteln die Differenz durch Subtraktion. Das ist die Anzahl der Trennzeichen und damit dieInstanznummerdu brauchst.

Ersetzen Sie diese Instanz in der vorletzten Zeile durch ein anderes Zeichen und verwenden Sie dann FIND(), um dessen Position in der Zeichenfolge abzurufen.

Die zweite Zeile subtrahiert diese Position von der Gesamtlänge der Zeichenfolge, um herauszufinden, wie viele Zeichen darauf folgen. Das sagt Ihnen, wie viele Zeichen rechts von der erstellten Zeichenfolge entfernt werden müssen.

Die erste Zeile tut genau das und übergibt Ihnen den Inhalt der letzten Zelle im Bereich.

Die von Excel verwendete Zeichenfolgenlänge variiert je nach Funktion, einige liegen beispielsweise im Bereich von 6.000 bis 7.000, andere eher bei 32.000. Wenn man das im Hinterkopf behält (deshalb geben Sie „TRUE“ an), kann man einen RIESIGEN Bereich anstelle von A2:C2 verwenden.

Beachten Sie, dass Sie dann mit der verbundenen Zeichenfolge und nicht mit Zellen arbeiten. Daher gilt:

  1. Sie müssen nie nach Zelladressen usw. suchen.
  2. Sie können es tatsächlich auf einen Bereich anwenden, der aus verbundenen „Unter“-Bereichen besteht, und auf Bereiche, die aus WIRKLICH getrennten Zellen bestehen. Diskontinuierliche Bereiche sind Ihre Freunde und Verbündeten.

Aufgrund der Art und Weise, wie die Daten in Teilen vorliegen, die von Excel innerhalb einer Formel ausgewertet werden, kann das Aufteilen von Teilen in benannte Bereiche Probleme verursachen, oder auch nicht, da die Zwischenergebnisse, die von Excel bei der Auswertung der Formel erstellt und verwendet werden, eine andere Form haben KÖNNEN als das Endergebnis, das ein benannter Bereich präsentiert. Manchmal kann man benannte Bereiche nicht für Teile verwenden, um die Logik einer Formel für zukünftige Zwecke zu vereinfachen. Aber nichts von dem oben Gesagten stellt dieses Problem dar, also können Sie benannte Bereiche beispielsweise für die TEXTJOINs erstellen und den Rest nativ eingeben, damit jeder, der auf die Zelle klickt, die Logik sehen kann. Oder teilen Sie die Teile in etwas Logisches wie „InstanceNumber“ (benannter Bereich) auf, damit es noch einfacher zu lesen ist. Erstellen Sie es und speichern Sie dann alles in einem benannten Bereich. Oder kümmern Sie sich überhaupt nicht um benannte Bereiche.

Wie gesagt, unelegant. Länger als manche Lösungen, aber nicht so „brutal“, wie manche Dinge sind. Keine Hilfsspalten oder andere Dinge, die die Leute oft nicht verwenden können. Oder nicht verwenden wollen. Keine {array}-Formeln.

(Und Sie können die diskontinuierlichen Bereiche bei Bedarf verwenden.) Mit diesem Ansatz können Sie auch einen Haufen Text und Daten nehmen, die eine Berichts-Engine in ein PDF-Format konvertiert und dann in Excel extrahiert, aber für jeden zugehörigen Satz unterschiedlich in Zellen aufgeteilt werden (also Informationen zu 10 Kunden, jeder Satz in einem Block mit 10 Spalten und 13 Zeilen, aber die Adresse des einen steht in Zelle 4,6 und die des anderen in Zelle 3,8, aber es folgt demselben Ablauf, nur werden beim Importieren andere Zellen gefüllt) und indem Sie daraus eine einzelne Zeichenfolge machen, können Sie die Teile formelhaft suchen. Zumindest oft. Oder nehmen Sie einen Zellenblock und prüfen Sie, ob irgendwo darin ein Datenstück auftaucht, indem Sie Funktionen verwenden, nicht Makros oder Arrays oder eine Hilfszelle für jede Zelle im Block.

verwandte Informationen