Office: (Office 2016) Haushaltsplan umarbeiten

Liebe Experten,

unser Haushaltsprogramm gibt den Planentwurf nach Excel nur mit Zahlen, ohne Formeln aus.
Jede Haushaltsstelle hat einen Block nach dem folgenden Schema. (Könnt ihr das Bild sehen?) Ich hab auch eine Muster-Datei angehängt.

Ich möchte gern in den Zeilen mit "Summe Einnahmen" und "Summe Ausgaben" Formeln haben, die die Einnahmen und Ausgaben der jeweiligen HHStelle addieren.
Letztes Jahr habe ich das mit Summewenn und Bezug auf Spalte A (E/A = Einnahmen/Ausgaben) gelöst und die Zeilen-Bereiche der einzelnen Haushaltsstellen händisch angepasst. Das war sehr mühsam, deshalb suche ich jetzt nach einer automatisierten Lösung.
Ich fürchte, das geht nur mit VBA, und dafür weiß ich leider viel zu wenig.
Könnt ihr mir helfen?

Danke und viele Grüße

Ingrid

 

Hallo Ingrid,

wenn diese Ausgabe immer so aussieht - also Fix ist, braucht es kein VBA. Sollten vor (über) jeder Summenausgabe jedoch die Zeilen variieren und damit auch die Summe in einer anderen Zeile stehen, wäre die Berechnung via VBA sinnvoller.

Dann wäre noch eine weitere Frage zu klären. Schreibt das Haushaltsprogramm in eine bestehende Datei hinein oder wird einfach eine bestehende Datei überschrieben oder wird eine fortlaufend nummerierte neue Datei erzeugt.

Ich frage deshalb, da man hier verschieden vorgehen muss.

Gruß Uwe

 

Hallo Ingrid,

mir ist unklar, wozu Du die von euren "Haushaltsprogramm" ermittelten Summenwerte nachträglich durch Formeln ersetzen willst? Wenn Du das tust, um nachträglich in Excel Datenwerte zu ändern, dann brauchst Du auch euer Haushaltsprogramm nicht mehr und kannst die Datenerfassung gleich in Excel vornehmen. Dann allerdings gleich in einer geeigneteren Datenstruktur. Diese kann mit dann einfach mit Pivot auswerten, wie auch schon Uwe schreibt

Wenn Du aber weiterhin eurer "Haushaltsprogramm" nutzen willst.

Aber wenn es so sein soll wie Du schreibst, geht es mit Formeln und auch wenn mehrere verschiedenen HHStellen mit jeweils unterschiedlicher Anzahl an Pos. Die Werte der Spalte D dann allerdings in einer zusätzlichen Spalte z.B. Spalte J und die Spalte D einfach ausblenden. Notwendig ist noch eine ebenfalls ausblendbare Hilfsspalte z.B. in Spalte K.

Dazu in K7: =WENN(C7<>"HHStelle";K6;B7) und diese weit genug nach unten kopieren.

Dann für die Ergebniswerte in J7:
=WENN((D7="")+(ANZAHL2(B7:C7)=0);"";WENN(A7<>"";D7;WENN(CODE(TEIL(B7&1234567;7;1))>64;SUMMEWENNS(D:D;K:K;K7;A:A;TEIL(B7;7;1));WENNFEHLER(WENNFEHLER(VERWEIS(9;1/(K$1:K6=K7)/(C7>0)/SUCHEN(C7;B$1:B6)^0;D$1:D6);VERWEIS(9;1/SUCHEN(C7;B$1:B6)^0;D$1:D6));INDEX(J:J;ZEILE()-4)-INDEX(J:J;ZEILE()-2)))))
und diese entsprechend nach unten kopieren.

 

Hallo Ingrid,

ich würde die Datei ganz anders aufbauen. siehe ..

Gruß
Roger

 

Hallo Roger,

das die auszuwertenden Daten besser anders strukturiert werden sollten, hatte Uwe und auch ich schon geschrieben. Aber sie schreibt ja, sie bekommt dir Daten so aus ihren Haushaltsprogramm.
Es wäre da natürlich da zu prüfen, ob die Daten aus diesem Programm nicht gleich mittels PQ ausgelesen werden können oder eben auch nachträglich mit PQ entsprechend anders strukturiert zur Auswertung aufbereitet werden können.

 

Hallo Uwe, danke dir!

Die Zeilenzahlen variieren bei den verschiedenen HHStellen. Und das HH-Programm erzeugt jeweils eine komplett neue Datei.

Ingrid

 

Ich danke euch allen, die sich Gedanken machen!

Es wird wohl am besten sein, ich beantworte alle Fragen in einem Post.

Zum Hintergrund hole ich etwas weiter aus:
Unsere Verwaltungsangestellte muss zwingend mit dem HH-Programm arbeiten. Und muss unsere Änderungen dann dort wieder eingeben. Deshalb soll die Excel-Datei dieselbe Struktur behalten, wie die Kollegin sie im HHProgramm sieht (auch wenn es anders sehr viel einfacher wäre...)

Wir Ehrenamtler prüfen und verändern den Plan - deshalb will ich die Formeln drin haben, damit wir gleich sehen, welche Auswirkungen unsere Änderungsideen haben.

Auf dieses HH-Programm im Netz der Landeskirche können wir nur vom PC im Gemeindebüro aus zugreifen - für zu Hause und unsere Beratungen brauchen wir was anderes. Ich hab dafür Excel "eingeführt". Die anderen haben bisher Stift und Taschenrechner genutzt, weil keine mit PC und Excel viel am Hut hat.

Die Zeilenanzahlen bei den einzelnen HHStellen variieren.

Hab ich damit alle Fragen erstmal beantwortet?

Werners Formelbandwurm probiere ich heut Abend aus - vielen Dank dafür!
Stutzig macht mich der Anfang: WENN((D7="")+(ANZAHL2(B7:C7)=0);"";WENN(A7<>"";D7;...
Ist das "+" richtig, oder muss ich das durch UND ersetzen?

Danke euch und viele Grüße

Ingrid

 

Hallo Ingrid,

in solchen Konstellationen nutze ich immer + anstelle UND(). Mehr dazu sieh z.B. mal hier:
Online - Excel: UND * ODER +

 

Hallo Werner,

danke dir. Wieder was gelernt! "Gemein" ist nur, dass UND durch den * ersetzt werden kann und ODER durch das +...
Nicht so wirklich intuitiv...

Viele Grüße
Ingrid

 

Hallo Ingrid,

bitteschön.
Ist einfach Gewohnheitssache und zudem zumindest in Deiner Excelversion bei notwendigen klassischen Matrixformeln unabdingbar.

 

Hallo Werner,

dein Formelbandwurm funktioniert. Ich versteh ihn zwar nicht, macht aber nix ;-))
Die Prüfbedingung in Spalte J musste ich noch verändern, weil die Bezeichnungen in Spalte C variieren.
Das hab ich selber hingekriegt ;-)

Nun hab ich noch das "optische" Problem, dass det Janze ja dazu dienen soll, Auswirkungen von Veränderungen der Planzahlen zu erfassen. Auf händische Veränderungen in der Formelspalte K reagieren die Formeln (natürlich) nicht - sie zielen ja auf D.
Wir brauchen also weiterhin die Spalte D mit den Ursprungszahlen sichtbar - dort ändern sich aber die Summenzahlen nicht.
Wir müssen also dran denken, dass wir beim Bearbeiten die Veränderungen in Spalte K suchen und nicht in D...

Danke dir!

Ingrid

 

OT: zwei Fragen zur Bedienung des Forums:
- kann ich hier eine Frage als gelöst markieren?
- und gibts beim Antwortschreiben keine Vorschau-Möglichkeit? Oder seh ich sie nur nicht?

 

Hallo Ingrid,

zu:

Ersteres ist richtig, letzteres nicht. Veränderungen in Spalte K sollten nicht vorgenommen werden, denn diese sind abhängig von den HHStellen-Nrn in Spalte B. Änderungen der Planzahlen der Einzelplanzahlen in Spalte D, werden in Spalte J auch für die Summen berücksichtigt natürlich aber nicht Änderungen der Teilsummenzahlenwerte in Spalte D.

zu:

Ersteres ist mir nicht bekannt, ist hier aber möglicherweise nicht explizit vorgesehen.
Eine Vorschau (D)eines Beitrages vor dem Versenden, kannst Du nach der Aktivierung der Funktion: [Weitere Einstellungen] vornehmen.