Microsoft Excel

Herbers Excel/VBA-Archiv

komplexe Formeln über mehrere Tabellenblätter

Betrifft: komplexe Formeln über mehrere Tabellenblätter von: Nadine
Geschrieben am: 19.10.2014 10:26:13

Guten Morgen :)
Mit Hilfe eines Makros habe ich verschiedene Tabellenblätter zusammengefügt und auf der ersten Seite "Inhalt" in der Spalte A2 bis A14 Verlinkungen zu den einzelnen Tabellenblättern erstellt. Nun wollte ich auf der ersten Seite die Auswertung machen. Die Tabellenblätter an sich haben doofe Namen bestehend aus vielen Zahlen, deshalb wollte ich mit den Verlinkungen A2 usw in meinen Befehlen arbeiten und nicht jeden einzelnen Namen raussuchen. Ich denke auch nicht, dass da der Fehler liegt. Das hatte nämlich weitestgehend funktioniert. Der letzte Schritt macht mir allerdings zu schaffen und ich hoffe mir kann hier jemand helfen.

=SUMMENPRODUKT(SUMMEWENN(INDIREKT("'"&A2&"'!BB:BB");"Begriff";INDIREKT("'"&A2&"'!CA:CA")))

Das ist die Formel an sich, wie sie noch funktioniert. Ich suche in der kompletten Spalte BB im Tabellenblatt, auf das in A2 verwiesen wird, nach einem Begriff und sobald dieser in einer Zeile vorliegt werden die jeweiligen Zahlen in den zugehörigen Zeilen in Spalte CA summiert.

Nun musste ich noch eine weitere Bedingung hinzuschalten. Es soll in dem Tabellenblatt noch eine weitere Spalte durchsucht werden und zwar nach einer Zahlengröße. Die Werte für die Summe in den Zeilen aus CA sollen nämlich nur verwendet werden, wenn in den zugehörigen Zeilen von P ein Wert kleiner 32 vorliegt.
ich hatte das so probiert:

=WENN("'"&A2&"'!P:Pkleineralszeichen32";SUMMENPRODUKT(SUMMEWENN(INDIREKT("'"&A2&"'!BB:BB");"Begriff"; INDIREKT("'"&A2&"'!CA:CA"))))

Allerdings bekomme ich da nur die Fehlermeldung #WERT.
Auch sämtliche andere Versuche mit drei Indirekt Befehlen von mir scheiterten.
Hat von euch noch jemand eine Idee?

Vielen Dank im Voraus und viele Grüße,
Nadine

  

Betrifft: AW: komplexe Formeln über mehrere Tabellenblätter von: Werner
Geschrieben am: 19.10.2014 10:54:22

Hallo Nadine,

kann es sein, dass nach dem wenn in der Formel ein indirekt fehlt? Außerdem scheint mir für den wenn-Teil das Sonst zu fehlen. Du hast wenn-prüfung-dann(dein summenprodukt)-sonst?

Bin jetzt aber auch nicht so der Experte.

Gruß Werner


  

Betrifft: AW: komplexe Formeln über mehrere Tabellenblätter von: Nadine
Geschrieben am: 19.10.2014 11:23:21

Hallo Werner,
danke für den Einwand, bisher habe ich das mit den Tips aber auch nicht hinbekommen. Im Prinzip soll Excel ja zusammen rechnen, wenn die Bedingung erfüllt ist und sonst soll es die Zahlen in den Zeilen ja ignorieren.
Viele Grüße, Nadine


  

Betrifft: SUMMEWENNS() reicht ... von: neopa C (paneo)
Geschrieben am: 19.10.2014 10:59:27

Hallo Nadine,

... in Deiner ersten Formel ist SUMMENPRODUKT() nicht notwendig. Die zweite ist nicht korrekt.

Hier anstelle SUMMEWENN() einfach SUMMEWENNS() so:

=SUMMEWENNS(INDIREKT("'"&A3&"'!CA1:CA99");INDIREKT("'"&A3&"'!BB1:BB99");"SuchWert"; INDIREKT("'"&A3&"'!P1:P99");"<32") 

Gruß Werner
.. , - ...





  

Betrifft: AW: SUMMEWENNS() reicht ... von: Nadine
Geschrieben am: 19.10.2014 11:21:25

Hallo Werner,
vielen Dank für das Beispiel, ich habe das grade mal ausprobiert, und in den Rohdaten die Werte über 32 vom ursprünglichen Ergebnis abgezogen und jetzt kommt leider ein viel zu kleiner Wert raus, das kann also leider auch nicht ganz stimmen... :(


  

Betrifft: hast Du schon berücksichtigt , dass von: neopa C (paneo)
Geschrieben am: 19.10.2014 11:45:24

Hallo Nadine,

... in meiner Formel nur der Zeilenbereich bis Zeile 99 ausgewertet wird? Du kannst den Bereich natürlich entsprechend der Notwendig erweitern. Ich würde diesen nicht über den gesamten Zeilenbereich auswerten lassen, was zwar ab Excel 2007 erlaubt ist. Ich werte immer nur über das max. wirklich notwendigen Bereich aus. Du hast noch nicht angegeben wie groß der bei Dir sein könnte.

Gruß Werner
.. , - ...


  

Betrifft: AW: hast Du schon berücksichtigt , dass von: Nadine
Geschrieben am: 19.10.2014 11:53:47

Hallo Werner,
ja das habe ich berücksichtigt. Da meine einzelnen Blätter unterschiedliche Größen haben, bin ich wieder auf den gesamten Zahlenbereich gegangen. Aber auch ein Anpassen an einen Zahlenbereich von einem Tabellenblatt hat in der entsprechenden Zelle nichts an der Summe geändert.
Viele Grüße,
Nadine


  

Betrifft: dann liegt z.B. ein Fehler im Datenformat vor ... von: neopa C (paneo)
Geschrieben am: 19.10.2014 12:31:12

Hallo Nadine,

... z.B. sind die zu addierenden eben keine echte Zahlenwert, wie im nachfolgenden Beispiel zu sehen. Dort ist der 4 in Zelle CA5 ein nicht sichtbares Hochkommata vorangestellt, was den Zahlenwert zu einem Text macht, welcher nicht addiert wird.

Tab8

 AB
1Tab99

Formeln der Tabelle
ZelleFormel
B1=SUMMEWENNS(INDIREKT("'"&A1&"'!CA1:CA99"); INDIREKT("'"&A1&"'!BB1:BB99"); "SuchWert";INDIREKT("'"&A1&"'!P1:P99"); "<32")


Tab9

 PQBABBBDBYCA
264  SuchWert  1
311  SuchWert  9
412  irgendwas  7
519  SuchWert  4
633  SuchWert  3
717  irgendwas  6
8       


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

Wenn das nicht hilft, solltest Du mal einen relevanten Dateiauszug (können Dummy-daten sein) hier einstellen. ich schau es mir dann heute Abend mal an.

Gruß Werner
.. , - ...


  

Betrifft: Arbeit für die Papierkorb von: Pedro Bugiardo
Geschrieben am: 19.10.2014 12:56:47

guckst du auch in andere Forum bei http://www.clever-excel-forum.de/Thread-komplexe-Formeln-ueber-mehrere-Tabellenblaetter?pid=7972#pid7972


  

Betrifft: AW: Arbeit für die Papierkorb von: Nadine
Geschrieben am: 19.10.2014 13:03:57

Hallo Werner,
ich hatte bei den Daten nichts fehlerhaftes gesehen, werde sie mir jetzt aber nochmal genauer anschauen. Ich wusste nicht, dass es daran liegen könnte.
Vielen Dank für deine Hilfe!
Viele Grüße,
Nadine


  

Betrifft: AW: dann liegt z.B. ein Fehler im Datenformat vor ... von: Nadine
Geschrieben am: 19.10.2014 13:04:40

Hallo Werner,
ich hatte bei den Daten nichts fehlerhaftes gesehen, werde sie mir jetzt aber nochmal genauer anschauen. Ich wusste nicht, dass es daran liegen könnte.
Vielen Dank für deine Hilfe!
Viele Grüße,
Nadine


  

Betrifft: DatenFormatfehler kann man abfangen, doch .... von: neopa C (paneo)
Geschrieben am: 19.10.2014 19:04:07

Hallo Nadine,

... zunächst: persönlich sehe ich es mit Crossposting auch nicht so eng. Allerdings fair wäre es gewesen, wenn Du auch hier mitgeteilt hättest, dass Du Deine Fragestellung in einem weiteren Forum eingestellt hättest.

Und bezogen auf meine Betreffzeile und meine Beispieldaten von heute Mittag, hätte das z.B. dann so ausgesehen:

Tab8

 ABC
1Tab9913

Formeln der Tabelle
ZelleFormel
B1=SUMMEWENNS(INDIREKT("'"&A1&"'!CA1:CA99"); INDIREKT("'"&A1&"'!BB1:BB99"); "SuchWert";INDIREKT("'"&A1&"'!P1:P99"); "<32")
C1{=SUMME(WENN(ISTZAHL(INDIREKT("'"&A1&"'!CA1:CA99")*1)*(INDIREKT("'"&A1&"'!BB1:BB99")="SuchWert")*(INDIREKT("'"&A1&"'!P1:P99")<32); 0+INDIREKT("'"&A1&"'!CA1:CA99")))}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

Gruß Werner
.. , - ...


  

Betrifft: AW: DatenFormatfehler kann man abfangen, doch .... von: Werner
Geschrieben am: 19.10.2014 19:21:16

Hallo Nadine,

ich hab auch mal ein wenig herumprobiert aber ich bin Neuling und sicher um Welten vom Wissen meines Namensvetters entfernt. Und von Matrixformeln hab ich schon mal gar keine Ahnung.
Ich hab nur mal die SUMMEWENNS Formel von Werner getestet, die funktioniert so. Natürlich nur wenn in deiner Spalte CA auch tatsächlich echte Zahlen stehen, was wohl teilweise bei dir nicht der Fall ist.

Ich habe mich dann mal an einem Makro versucht. Das Makro habe ich im Workbook open Ereignis stehen. Das Makro läuft durch alle Spalten CA aller Tabellenblätter außer Tabellenblatt1 (da bin ich mal davon ausgegangen, dass du da deine Zusammenfassung hast) und multipliziert alle Werte der Spalte CA mit 1. Damit werden auch nicht echte Zahlen in Zahlen gewandelt.

https://www.herber.de/bbs/user/93237.xlsm

Gruß Werner


  

Betrifft: AW: DatenFormatfehler kann man abfangen, doch .... von: Nadine
Geschrieben am: 19.10.2014 20:40:53

Auch an dich nochmal ein Danke Werner, das mit dem Makro schaue ich mir mal an. Ich hatte den Fehler jetzt ohne Makro behoben, der hat sich auch in Spalte P und nicht in CA versteckt gehabt...
Aber vielen Dank für eure Hilfe, ich bin froh, dass es jetzt funktioniert!
Viele Grüße,
Nadine


  

Betrifft: AW: DatenFormatfehler kann man abfangen, doch .... von: Nadine
Geschrieben am: 19.10.2014 20:36:03

Hallo ihr zwei!
Ich danke euch beiden nochmal vielmals für die Hilfe und möchte mich entschuldigen, dass ich nicht Bescheid gegeben hatte wegen meinem weiteren Post. Ich war vorher noch nie in so Foren unterwegs und wusste nicht, dass sowas auf Unmut stößt.
Nachdem ich euren Tips nachgegangen war, hab ich festgestellt, dass der Fehler nicht in Spalte CA lag, sondern in Spalte P. Ich konnte das beheben und nun funktioniert es so, wie es soll!
Also vielen Dank nochmal und viele Grüße,
Nadine


 

Beiträge aus den Excel-Beispielen zum Thema "komplexe Formeln über mehrere Tabellenblätter"