Office: (Office 2016) Summen + Anzahl Wochentage

Dank Aloys78,

eine Hilfsspalte hilft nicht.
Denn sonst kann ich gleich die richtigen
Informationen in die rund 20 Tabellen
einpflegen.
Und da es noch mehr solcher Berechnungs-
schritte mit anderen Werten gibt müßte ich
zuviele Hilfsspalten verbauen und dann wird
es unübersichtlich und unzentral.

Windows 7: Ja und nein. Mit dem Auto gefährde ich ohne TÜV ganz
klar andere Verkehrsteilnehmer.
Wenn ich Windows 7 weiterhin benutze, dann gefährde ich ggf. maximal
meine PC-Umgebung.
Andererseits will ich nicht auf Windows 10 umsteigen.

Ich vermute jetzt mal ganz stark,
daß das Problem nicht durch zwei Formeln lösbar
ist und ich schliesse das Thema.

PS: Wenn jemand doch eine Lösung weiß,
bitte gerne per Nachricht mitteilen

 

Hallo Andreas,

Das kann ich nicht nachvollziehen, da Du diese Hilfsspalten ja ausblenden kannst.

Ich habe keine Vorstellung, wie man bei diesem Szenario überhaupt einen Formel-Vorschlag unterbreiten können sollte.

 

Ein Vergleich nach meinem Wissen:
Deine Hilfsspalte benötigt jeweils allein (es sind ca. 20 Tabellenblätter!)
rund 4.000 Hilfszellen bei 4.000 Zeilen mit Zwischenberechnungen. Und das ist unnötige Rechenkapazität für jeweils zwei gesuchte Ergebnisse.
Klar lassen sich Hilfsspalten ausblenden. Es werden dann auch mehrere Hilfsspalten in einem Tabellenblatt. Und bei jeder kleinen Änderung rechnet Excel dann ein-bis zwei Minuten.

Meine Tabellen enthalten sowieso schon sehr viele Zellen mit Berechnungen.

Ich habe mir nach meinem Kenntnisstand Gedanken gemacht, welche Formeln am Besten arbeiten. Und um genau diese Lösungen bitte ich seit Tagen.

Und ich suche die Lösung ohne Hilfsspalten (und wie das mit Hilfsspalte geht hat mir ein Experte im "Blatt A" schon per Formeln aufgezeigt und einen Sverweis kann ich auch schnell selber zusammenbasteln).

Das heißt es gibt keine zwei Formeln die zum Ziel führen ohne Hilfsspalten?
Es gibt zwei Möglichkeiten:
a) Es gibt zwei Formeln für E2016?
oder b) zwei Formeln sind dafür technisch nicht möglich?

Da mich eine klare Antwort sehr interessiert öffne ich jetzt wieder den Beitrag.

Vielen Dank

 

PS: Es müßte doch bitte eine Möglichkeit geben die Formeln von aloys78
miteinander zu kombinieren, so daß dann die zwei gesuchten Formeln genügen und es ganz ohne Hilfsspalten geht.
Wer schafft das bitte?

 

E10: =SUMMENPRODUKT(--(SVERWEIS(A4:A16;Quelle!A2:B14;2;0)=G1)*(B4:B16

 

Hallo Ralf,

Gerade Formel für E10 getestet auf aktuellen Win10 Rechnern mit Excel 2016 bzw Excel 2019. Ergebnis in beiden Fällen = 0.

Liegt es vielleicht am SVERWEIS mit einem Array als Suchbegriff, das so nur von MS 365 akzeptiert wird?

 

Ich vermute: ja
Auch ich habe es weder unter E2016, noch unter E2019 ans Laufen gebracht

Edit:
Wenn ich diese Datei in Excel 365 mit den Formeln abspeichere und unter E2016 oder E2019 öffne, so sehe ich

a) die Formeln, allerdings mit Matrix-Klammern
b) das korrekte (!) Ergebnis der Formeln

Aktiviere ich die Zellen und drücke Enter (oder auch STRG+SHIFT+ENTER), verschwindet das Ergebnis und wird zu 0

 

\@Ralf,

ein schönes und erfolgreiches Experiment.
Daraus ergibt sich ja ein möglicher Lösungsansatz für Andreas: MS 365 anschaffen.

 

Einen habe ich noch *Smilie

Es funktioniert in Excel 2016 mit der VERWEIS-Funktion

Hierfür muss die Suchmatrix (hier das Blatt Quelle) allerdings unbedingt aufsteigend sortiert sein !!!

E10: =SUMMENPRODUKT(--(VERWEIS(A4:A16;Quelle!D2:E14)=G1)*(B4:B16

 

Hallo Ralf,

Alle Achtung; es hat Dir offenbar keine Ruhe gelassen.

Ich würde allerdings diese Konstruktion nicht einsetzen. Aus einem einfachen Grunde: die aktuelle Dokumentation für Verweis sieht für den Lookup_value nur einen Wert, aber kein Array vor.

 

Ja, es hat mich interessiert, weil ich nach wie vor der Meinung bin, daß solche Matrix-Formeln mal funktioninuert haben

Deswegen wundert es mich ja, daß INDEX/VERGLEICH und SVERWEIS hier streikt...die VERWEIS Funktion (die ich eigentlich noch nie gebraucht habe) aber nicht...

Egal...Andreas hat jetzt eine mögliche Lösung, aber ob er sich noch mal äußert, weiß ich nicht, da ich ihn ja angeblich nicht ebenbürdig behandel, nur weil man mal 'nen Spruch losläßt...*rolleyes.gif*

 

Hallo Scorefun und aloys78,

an Euch Beide meinen Dank und im Insbesonderen
an Scorefun, welcher weiterhin mit der Lösungssuche
aktiv ist.

Ich bin weiterhin an Bord :-). Die zwei vergangenen Tage habe ich an
meiner privaten Excelliste an neuen Ideen gebastelt
und habe so eine Pause mit den gesuchten
Excelformeln hier gemacht, da ich die Hoffnung schon aufgab.

Ich habe alle neuen vier Excelformeln versucht einzubauen.
Als Gegenprobe eine Hilfsspalte, die ich letztes Jahr
in ein Tabellenblatt eingebaut habe und die sobald
die Formeln arbeiten wieder entfernt wird.

Vielleicht liegt es mir, keine Ahnung …
Habe auch die geschweiften Klammern eingebaut,
wobei da öffnet sich immer bei mir ein Fenster mit Speichern unter,
ob ich da alles mit strg + Größer + return richtig mache …
Die Ergebnisse sind jeweils mit den angepaßten Formeln aus „Blatt B“
E17 #NV
E18 #NV
E21 #WERT!
E22 0 (endlich mal eine Zahl, Null ist klar falsch.)

Wobei bei E21 und E22 wird bei mir das andere Tabellenblatt in ‘Quelle‘ geschrieben,
das natürlich bezogen auf mein Tabellenblatt. Mache ich es raus, dann ist dieses Zeichen
wieder drin bei Neuaktivierung der Formeln.
Bei E21 und E22 vermisse ich beim Sverweis dann auch aus welcher Spalte es kommen soll;
Bei E17 und E18 ist da die 2 in der Formel. Und da mein Quellenblatt einige Spalten hat
vermisse ich das.
Bei meinem analogen Quellenblatt sind die Datums aufsteigend,
also oben sind die neuesten Datums und nach unten werden sie dann älter …
das paßt als gewünschte Sortierung.
Die gleiche Tabelle habe ich anderswo im Einsatz und deshalb ist sie
für mich momentan keine Fehlerquelle.

Vielleicht helfen Euch die Angaben neue Diagnosen zu stellen
oder ein Anlaß an der lebenden Tabelle zu „operieren“.
Und ich bleibe dran und werde mir erneut die Formeln zu Gemüte
nehmen. Habe sie mir auch als Gegenüberstellung auf Papier
mit den Formelteilen aus „Blatt B“ und meinem Excel aufgeschrieben.

Bitte woran mag es liegen?

Viele Grüße, Andreas

 

\@Andreas,

die Tests von Ralf scheinen ja meine Vermutung zu bestätigen, dass
SVERWEIS / Verweis mit einem Array als Suchbegriff so nur von MS 365 akzeptiert wird!

Also gibt es m.E. für Dich folgende Alternativen:
- Verwendung von Hilfsspalten, oder
- Einsatz Office 365 (dort werden diese Formeln ja merkwürdigerweise akzeptiert), oder
- eine VBA-Lösung, oder
- Warten auf neue Vorschläge aus dem Forum.

 

\@Andreas:

Ich habe ausdrücklich geschrieben, daß die Quell-Daten AUFTSTEIGEND sortiert sein müssen
siehe auch in Deiner Beispieldatei - Blatt Quelle

Das ist ABSTEIGEND

 

Hi Scorefun:
Bei mir ist die Tabelle immer oben ab Zeile 1.
Und meine Tabellen sind meist so angelegt,
daß oben die neuesten Datums kommen.
Es stimmt die Sortierung muß genau andersherum sein,
so wie Du schreibst.

a) Ich habe weiter versucht die Formeln dann
zum Arbeiten zu bringen.
Ich gehe jetzt nach einem Minitest stark davon aus,
daß Deine Formel funktionieren.
Es gibt wohl einen Fehler in meiner
Tabelle mit den Datums, habe schon gesucht
und finde kein Problem.


b) Ich schrieb, daß ich im meiner Originaltabelle
jeweils mehrere Hilfsspalten benötigen würde und
diese mit den beiden E2016-Formel einsparen möchte.
Wie sehen dann bitte die diese Formeln aus,
wenn in dem Tabellenblatt „Quelle“
z.B. die Spalten F und G auch mit Werten gefüllt sind?
Wie passe ich bitte die Formeln an?