Ich brauche Hilfe beim Entwirren des Chaos, das ich seit einigen Jahren in LibreOffice Calc in einer Dateneingabe- und Übersichtstabelle habe. Der allgemeine Aufbau der relevanten Teile der Tabelle ist wie folgt:
Arbeitsblatt: „Dateneingabe“
|A |B |... |L |
|-----|-----|-----|-----|
|Date |Name |... |Value|
Blatt: „Zusammenfassung“
|A |... |E |G |
|-----|-----|------------|------------------------|
|Name |... |Total Values|Values from last 90 days|
Im Moment funktioniert alles, aber ich möchte dem Zusammenfassungsblatt (Spalte G) eine neue Funktion hinzufügen. Spalte E führt eine komplexe Formel aus, um das Dateneingabeblatt nach jeder Zeile zu durchsuchen, in der Zellen in der Zusammenfassungsspalte A mit Zellen in der Dateneingabespalte B übereinstimmen. Hier ist diese Formel:
=SUMPRODUCT(('Data Entry'.$B$3:INDIRECT("'Data Entry'.B"&'Data Entry'.$A$1+2)=A1)*ISNUMBER('Data Entry'.$L$3:INDIRECT("'Data Entry'.L"&'Data Entry'.$A$1+2)),('Data Entry'.$L$3:INDIRECT("'Data Entry'.L"&'Data Entry'.$A$1+2)))
Ich weiß, das ist ein absolut verwirrendes Durcheinander. Ich werde versuchen, die folgende Formel zu vereinfachen:
=SUMPRODUCT((B1:B100=A1)*ISNUMBER(L1:L100),(L1:L100))
Ich habe mich gefragt, wie ich diese Formel für die Zusammenfassungsspalte G ändern könnte, sodass die Suche nur auf die letzten 90 Tage beschränkt wäre.
Ein Beispiel dessen, was ich sehen sollte:
Arbeitsblatt: „Dateneingabe“
|A |B |...|L|
|--------|-----|---|-|
|19-08-13|Name1|...|2|
|19-07-25|Name2|...|1|
|19-01-01|Name1|...|3|
Blatt: „Zusammenfassung“
|A |...|E|G|
|-----|---|-|-|
|Name1|...|5|2|
|Name2|...|1|1|
BEARBEITEN: Folgendes führt bei mir zu einem 502-Fehler:
=SUMPRODUCT(('Data Entry'.$B$3:INDIRECT("'Data Entry'.B"&'Data Entry'.$A$1+2)=A6)*ISNUMBER('Data Entry'.$L$3:INDIRECT("'Data Entry'.L"&'Data Entry'.$A$1+2))*('Data Entry'.A3:"'Data Entry'.A"&'Data Entry'.$A$1+2>TODAY()-91),('Data Entry'.$L$3:INDIRECT("'Data Entry'.L"&'Data Entry'.$A$1+2))*('Data Entry'.A3:"'Data Entry'.A"&'Data Entry'.$A$1+2>TODAY()-91))
Antwort1
Die Frage beschreibt zwei Probleme. Das eine ist der Versuch, eine lange Formel zu debuggen. Im Allgemeinen besteht der Schlüssel dazu darin, die Formel in ihre Einzelteile zu zerlegen und jedes Teilstück zu testen, um zu sehen, was es bewirkt. Beginnen Sie damit, logische Teile intakt zu lassen (d. h. mehrere Ausdrücke, die zusammenarbeiten), um zu ermitteln, welcher Abschnitt der Formel nicht funktioniert. Wenn der Fehler dadurch nicht offensichtlich ist, zerlegen Sie den Abschnitt in seine Einzelteile.
Kopieren und fügen Sie dazu jeden Teil ein und passen Sie dann das eingefügte Stück an, um eine eigenständige Formel daraus zu machen (z. B. durch Hinzufügen des Gleichheitszeichens). Falls das Problem ungleiche Klammern sind, kopieren Sie den gesamten Ausdruck, der alle Klammern enthält, und löschen Sie dann andere interne Ausdrücke und deren Klammern, die leichter zu erkennen sind. Ungleiche Klammern im Rest können allein durch diese Übung offensichtlich werden. Durch Kopieren und Einfügen wird sichergestellt, dass das Problem in Ihren Test einbezogen wird. Durch erneutes Eintippen der Formel können Fehler behoben werden, und beim Test funktioniert alles, sodass es keinen Diagnosezwecken dient.
Das andere Problem war die Beschränkung der Ergebnisse auf die letzten 90 Tage. Dies kann ähnlich erfolgen, wie die Formel die Ergebnisse bereits auf den übereinstimmenden Namen beschränkt – fügen Sie der Liste der Ausdrücke in SUMPRODUCT einen Test hinzu. Der Einfachheit halber lasse ich die Komplexität der Verwendung von INDIRECT zum Erstellen der Bereiche weg und zeige nur feste Bereiche an. Im Kontext könnte das zusätzliche Argument folgendermaßen aussehen:
=SUMPRODUCT( ... (L1:L100), (A1:A100>TODAY()-91) ... )
Das (L1:L100) dient im vereinfachten Beispiel in der Frage nur zur Anzeige des Standorts.
Daten werden als Tagesanzahl gespeichert, daher sind die Werte in Tageseinheiten angegeben. Sie können Tage direkt addieren oder subtrahieren. Der Ausdruck >TODAY()-91prüft auf Daten, die nicht mehr als 90 Tage älter als heute sind. Dies setzt voraus, dass Ihre Daten keine zukünftigen Daten enthalten können, da diese eingeschlossen würden, wenn Sie die Formel nicht erweitern, um dies ebenfalls einzuschränken. Der gesamte Ausdruck ist ein logischer Test, der entweder TRUE ( 1) oder FALSE ( 0) zurückgibt. SUMPRODUCT multipliziert den Rest des Array-Ergebnisses mit diesen Werten, was entweder eine Null oder das Ergebnis der anderen Argumente in SUMPRODUCT ergibt.