Ich habe in meiner Excel-Tabelle eine Liste mit Elementen, die zusammen mit einer Gesamtzahl folgendermaßen nummeriert sind:
Beispiel:
| Column A (available) | Column B (missing) | Column C (total)
___|______________________|____________________|_________________
1 | 01 - 13 | ? | 20
2 | 02 - 09 | ? | 10
3 | 01 - 04, 06 - 11 | ? | 11
Nun möchte ich, dass in Spalte B die fehlenden Artikelnummern angezeigt werden, und zwar wie folgt:
| Column A (available) | Column B (missing) | Column C (total)
___|______________________|____________________|_________________
1 | 01 - 13 | 14 - 20 | 20
2 | 02 - 09 | 01, 10 | 10
3 | 01 - 04, 06 - 11 | 05 | 11
Es ist mir gelungen, für Fälle wie in Zeile 1 eine langwierige Formel zu erstellen, die jedoch nur funktioniert, indem die letzten beiden Ziffern einer Zelle in Spalte A überprüft, vom Wert der Spalte C in dieser Zeile abgezogen und dann Spalte B mit dem Ergebnis ausgefüllt werden.
(im Wesentlichen ungefähr so (mit einigen Sonderfällen, die nicht vom vorliegenden „Lückenproblem“ betroffen sind)): $C1-Right($A1;2)&" - "&$C1
)
Aber mein Ansatz kann (offensichtlich) die Lücken wie in den Zeilen 2 und 3 nicht bewältigen, und ich möchte wissen, ob es eine Möglichkeit gibt, mein Problem zu lösen.
PS: Ich war nicht sicher, welchen Titel ich diesem Problem geben sollte, also entschuldige ich mich, falls er irreführend ist.
Antwort1
Ihr Problem kann mithilfe der TREND-Funktion gelöst werden:
- Wenn Sie die erste Datenprobe in Spalte A nehmen, müssen Sie1. Sequenz von 1 bis 13Und2. von 1 bis 20.
- Schreiben Sie diese Array-Formel in die Zelle
C1
, schließen Sie mitCtrl+Shift+Enter
„ und füllen Sie nach unten“ ab.
{=TREND(A1:A13,B1:B13,B14:B20)}
- Sie erhalten Serien von
14 to 20
. Geben
D1
Sie diese Formel ein, um das zu erhalten1st Cell value
.=INDEX(C1:C7,MATCH(TRUE,INDEX((C1:C7<>0),0),0))
Geben Sie diese Formel ein,
D2
um zu erhaltenlast Cell value
.
=LOOKUP(2,1/(C1:C7<>""),C1:C7)
- Geben Sie in Zelle
D4
dieses ein=D1&"-"&D2
, Sie erhalten14-20
.
Notiz: Für die zweite Datenprobe müssen Sie zwei Reihen erstellen.
1- 2 to 9
.
2- 1 to 10
.
Die 3. Datenprobe benötigt 3 Reihen.
1- 1 to 4
.
2- 6 to 11
.
3- 1 to 11
.
- Wiederholen Sie die oben gezeigten Schritte für andere Datenproben.
- Passen Sie die Zellbezüge in der Formel nach Bedarf an.