Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
772to776
772to776
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Zellen mit Formel zählen
20.06.2006 09:02:44
Andreas
Hallo,
ich habe folgendes Problem. Ich muss eine Summierung von Prozentzahlen machen. Dazu habe ich einen Auszug aus der Tabelle:
Monatsberichte
 BCDE
452018030100,00%100,00%
46602120099,96%99,84%
4759040100,00%100,00%
484309_0100,00%100,00%
4943090100,00%99,98%
50  MonatMonat
51  99,99%99,86%
Formeln der Tabelle
C45 : =SVERWEIS(B45;Kunden;2;)
D45 : =WENN(JAZ_Kunden!C34="";100%;100%-(JAZ_Kunden!C34/(31*24*60)))
E45 : =WENN(JAZ_Kunden!D34="";100%;100%-(JAZ_Kunden!D34/(31*24*60)))
C46 : =SVERWEIS(B46;Kunden;2;)
D46 : =WENN(JAZ_Kunden!C35="";100%;100%-(JAZ_Kunden!C35/(31*24*60)))
E46 : =WENN(JAZ_Kunden!D35="";100%;100%-(JAZ_Kunden!D35/(31*24*60)))
C47 : =SVERWEIS(B47;Kunden;2;)
D47 : =WENN(JAZ_Kunden!C36="";100%;100%-(JAZ_Kunden!C36/(31*24*60)))
E47 : =WENN(JAZ_Kunden!D36="";100%;100%-(JAZ_Kunden!D36/(31*24*60)))
C48 : =SVERWEIS(B48;Kunden;2;)
D48 : =WENN(JAZ_Kunden!C37="";100%;100%-(JAZ_Kunden!C37/(31*24*60)))
E48 : =WENN(JAZ_Kunden!D37="";100%;100%-(JAZ_Kunden!D37/(31*24*60)))
C49 : =SVERWEIS(B49;Kunden;2;)
D49 : =WENN(JAZ_Kunden!C38="";100%;100%-(JAZ_Kunden!C38/(31*24*60)))
E49 : =WENN(JAZ_Kunden!D38="";100%;100%-(JAZ_Kunden!D38/(31*24*60)))
D51 : =SUMME(D2:D50)/48
E51 : =SUMME(E2:E50)/48
 
Diagramm - Grafik - Excel Tabellen einfach im Web darstellen    Excel Jeanie HTML  3.0    Download  
Wie man sieht, sind die Summierungen aus 48 Zellen generiert. Das ganze habe ich dann auch über 12 Spalten, Januar bis Dezember. Da ich gerne zwecks der Übersicht den Inhalt der mit 100% gefüllten Zellen ausblenden würde, ensteht das Problem dass ich monatlich die Anzahl der gefüllten Zellen zählen und die Formel händisch abändern muss (im Beispiel D51 und E51). Gibt es da eine Lösung, wie man so etwas automatisiert macht, also zählen der gefüllten Zellen
Ich würde mich freuen, wenn mir da jemand helfen kann bzw. eine Lösung gibt.
mfG
Andreas Müller

12
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Zellen mit Formel zählen
20.06.2006 12:47:04
TheDukeNT
Hallo Andreas!
Das kannst du über ein Macro machen.
z.B.:
Sub Summe() Dim lz, i As Integer lz = Sheets("Tabelle1").Range("D65536").End(xlUp).Row Cells(lz + 2, 4).Value = "=SUM(D3:D" & lz & ")/48" For i = 5 To 15 Cells(lz + 2, 4).Copy Destination:=Cells(lz + 2, i) Next i End Sub
Den Tabellennamen mußt du ggf. noch anpassen.
Gruß Niels
AW: Zellen mit Formel zählen
20.06.2006 13:22:27
Andreas
Hallo Niels,
danke für die Antwort vorab. Allerdings klappt es noch nicht, aber das liegt wohl an mir :-(. Ich habe die komplette Datei hochgeladen, https://www.herber.de/bbs/user/34489.xls
Wie du dort siehst, macht er das Ergebnis nach dem ausführen des Makros in die Zeile 53 hinein. Wenn ich nun einen 100% Wert verstecke bzw. wegnehme, verändert er auch die Prozentzahl im Summenfeld. Das genau sollte ja nicht passieren.
Weiterhin macht er bei jedem ausführen des Makros alle 2 Zeilen weiter eine neue Belegung. Das ergibt sich wohl aus der Zeile im Makro Cells(lz + 2, 4). Auch wenn ich "=SUM(D3:D" auf "=SUM(D2:D" abändere stimmen die Werte generell nicht mehr.
A.) Was mache ich falsch?
B.) Wie kann man das Makro fix in die Zeile 51 fixieren, sodass er die Werte immer dort hinein macht oder aktualisert wenn man das Makro ausführt?
Vielen Dank wenn du mir hier noch weiterhelfen kannst
mfG
Andreas Müller
Anzeige
AW: Zellen mit Formel zählen
20.06.2006 14:33:42
TheDukeNT
zu B)
Sorry ich dachte es kommen immer wieder neue Daten unter die Alten darum die Abfrage auf letzte benutzte Zelle in Spalte D
Wenn du die Formel immer in Spalte 53 haben möchtest ändere:
Cells(lz + 2, 4).Value = "=SUM(D3:D" & lz & ")/48"
Cells(lz + 2, 4).Copy Destination:=Cells(lz + 2, i)
in:
Cells(53, 4).Value = "=SUM(D2:D49)/48"
Cells(53, 4).Copy Destination:=Cells(53, i)
Das mit den ausgeblendeten bzw. gelöschten Zellen hab ich noch nicht ganz kapiert.
Meinst du vielleicht, das er den Wert der von "=SUMME(...)" angezeigt wird nicht ändern soll?
Gruß Niels
Anzeige
AW: Zellen mit Formel zählen
20.06.2006 15:01:29
Andreas
Hallo Niels,
musst dich nicht entschuldigen. Meistens liegt es ja an dem erklärenden :-). Also ich hab das Makro mal abgeändert es sieht so aus jetzt:
Sub Summe() Dim lz, i As Integer lz = Sheets("Monatsberichte").Range("D65536").End(xlUp).Row Cells(53, 4).Value = "=SUM(D2:D49" & lz & ")/48" For i = 5 To 15 Cells(53, 4).Copy Destination:=Cells(53, i) Next i End Sub
Jetzt erscheinen in der Zeile 53, Spalte D-O nur Nullen. Also er rechnet praktisch nicht mehr. Und wo im Makro sagts du ihm, dass er das in den Spalten bis O machen muss, mit dem & lz &?
Das mit den Prozenten ist wie folgt. Ursprünglich war ja der Ansatz, dass ich die unnötig mit 100% aufgefüllten Zellen zwecks Übersicht leer haben wollte. Dann muss ich aber immer die Formeln in Zeile 51 ändern, sprich nachzählen, wieviel Zellen belegt sind. Genau das wollte ich mit der Automatisierung erreichen. Vorgabe:
Summiere die Zellen D2-D49, und teile durch die Anzahl der belegten Zellen (D51 =SUMME(D2:D50)/48).
Wenn ich also jetzt die Formel in D49 =WENN(JAZ_Kunden!C38="";100%;100%-(JAZ_Kunden!C38/(31*24*60))) auf =WENN(JAZ_Kunden!C38="";""%;100%-(JAZ_Kunden!C38/(31*24*60))) abändere, ist die Zelle ja leer und der Wert in der Zelle D51 ändert sich sprich verfälscht den Wert. Schließlich steht ja in Zelle D51 =SUMME(D2:D50)/48 obwohl jetzt nur noch 47 Zellen belegt wären.
Habe ich das einigermaßen verständlich erklärt? Naja wenn nicht frag mich eben noch einmal. ich denke wir kommen da schon hin :-).
mfG
Andreas Müller
Anzeige
AW: Zellen mit Formel zählen
20.06.2006 15:03:55
Andreas
Hi,
hab einen kleinen Fehler gemacht in Beschreibung der Formel. So stimmts.
D49 =WENN(JAZ_Kunden!C38="";100%;100%-(JAZ_Kunden!C38/(31*24*60)))
D49 =WENN(JAZ_Kunden!C38="";"";100%-(JAZ_Kunden!C38/(31*24*60)))
Gruss Andreas
AW: Zellen mit Formel zählen
21.06.2006 09:38:32
TheDukeNT
moin Andreas!
Mir kommt das mit den 100% zwar noch ein bischen Spanisch vor aber ok...
Hab das Macro jetzt so abgeändert, das er jetzt jeden Monat von Zelle D2 bis D49 prüft ob leer oder nicht. Wenn nicht leer zählt er die Zelle für den Divisor. Dann trägt er in Zelle 53 die Formel mit dem entsprechenden Divisor ein und geht zum nächsten Monat.
Versuch mal ob das so geht.

Sub Summe()
Dim a, i, monat As Integer
Application.ScreenUpdating = False
For monat = 4 To 16
a = 0
For i = 2 To 49
If Cells(i, monat).Value <> "" Then a = a + 1
Next i
Cells(53, monat).Value = "=SUM(D2:D49)/" & a
Next monat
Application.ScreenUpdating = True
End Sub

Gruß Niels
Anzeige
AW: Zellen mit Formel zählen
21.06.2006 10:29:01
Andreas
Hallo Niels,
er rechnet es jetzt aus. Da ich die Makros nicht aufdröseln kann, frage ich mich wie ich erreiche, dass er anstatt wie jetzt 0,999880526 anzeigt eben 99,98 % anzeigt (nur 2 Nachkommastellen).
Noch einmal zurück zu den 100% und spanisch vorkommen :-). Ich habe dir mal ein Beispiel hochgeladen, da wirst du sehen was ich meine. https://www.herber.de/bbs/user/34517.xls
Ich habe die Tabelle wo das Makro integriert ist so gemacht, dass alle 100% Werte weg sind. Nun siehst, dass die Prozentwerte bei 37,xx oder 45,xx liegen. In Spalte D habe ich die Formel in D51 angepasst, damit der korrekte Wert angezeigt wird. Er summiert die Werte in der Spalte D und dividiert durch die Anzahl der belegten Zellen. Genau das ist mein Ansatz. Dann habe ich auch einen relevanten Wert. So besser und verständlicher?
Einzig noch, warum er mir in der Spalte H einige 100% Werte anzeigt, obwohl es Werte so um die 99,xx sein müssten. Woran das liegt weiss ich auch nicht. Formeln scheinen zu stimmen.
mfG
Andreas
Anzeige
AW: Zellen mit Formel zählen
21.06.2006 11:35:51
TheDukeNT
Markiere einfach alle Zellen von D53 bis P53 -&gt rechte Maustaste -&gt Zellen Formatieren
und da auf Prozent einstellen. Schon hast du nur noch 2 Stellen hinter dem Komma.
Wenn du in Spalte H die Nachkommastellen auf 3 oder 4 erhöhst bekommst du wieder den 99.xxx % Wert.
Wenn du die Summe in Zeile 51 haben möchtest dann änder das Macro bei:
Cells(53, monat).Value = "=SUM(D2:D49)/" & a
in:
Cells(51, monat).Value = "=SUM(D2:D49)/" & a"
Gruß Niels
AW: Zellen mit Formel zählen
21.06.2006 12:15:57
Andreas
Hallo Niels,
Wenn du die Summe in Zeile 51 haben möchtest dann änder das Macro bei:
Cells(53, monat).Value = "=SUM(D2:D49)/" & a
in:
Cells(51, monat).Value = "=SUM(D2:D49)/" & a"

dies war mir klar. Ein wenig habe ich das Makro jetzt schon verstanden. Aber jetzt macht er als letzten Fehler noch in der Zeile 51, Zellen D51 - O51 die Summe von D2:D49 rein. Das heißt das Makro muss erweitert werden um die Range E2:E49-P2:P49. Wie geht das noch, dann passt auch alles. Sorry ich weiss du hast sicherlich anderes zu tun aber ist jetzt blöd wenn ich jemanden anderen dafür anspreche.
Gruss und vielen Dank
Andreas
Anzeige
AW: Zellen mit Formel zählen
21.06.2006 15:02:41
TheDukeNT
Hallo Andreas!
Hab nochmal ein bischen rumgetüftelt sollte jetzt aber eigendlich so gehen.
Ich mußte eine Zwischenschritt über Zeile 52 für den Divisor machen. in Zeile 53 steht jetzt die Formel "=SUMME(D2:D49)" ohne Teiler. Zeile 51 errechnet sich aus Zeile 53/ Zeile 52
Das ganze sieht dann so aus:

Sub Summe()
Dim a, i, monat As Integer
Application.ScreenUpdating = False
For monat = 4 To 16
a = 0
For i = 2 To 49
If Cells(i, monat).Value <> "" Then a = a + 1
Next i
Cells(52, monat).Value = a
Next monat
Cells(53, 4).Value = "=SUM(D2:D49)"
For i = 5 To 15
Cells(53, 4).Copy Destination:=Cells(53, i)
Next i
For i = 4 To 15
Cells(51, i).Value = Cells(53, i) / Cells(52, i).Value
Next i
Application.ScreenUpdating = True
End Sub

Die Zeilen 52 und 53 kannst du ja einfach ausblenden wenn die Stören.
Gruß Niels
Anzeige
AW: Zellen mit Formel zählen
21.06.2006 15:25:43
Andreas
Hallo,
okay, er bringt ein Scriptfehler mit dem Ergebnis:
Laufzeitfehler6
Überlauf.
Beim anschließenden debuggen markiert er folgende Zeile gelb:
Cells(51, i).Value = Cells(53, i) / Cells(52, i).Value
Die Werte in Zeile 51 sind korrekt, in zeile 52 sind Werte wie 18, 9 etc und in Zeile 53 Werte wie 897,547 etc.
Das hängt wohl mit deinen Hilfszeilen zusammen. Wie kann man das verhindern?
Gruss Andreas
AW: Zellen mit Formel zählen
21.06.2006 16:26:22
TheDukeNT
Das Problem mit dem Laufzeitfehler kommt nur wenn in der ganzen Spalte kein einziger Wert steht.
Änder nur die oberste Formel so ab das da als Ergebniss wieder 100% steht.
Also:
=WENN(JAZ_Kunden!C2="";100%;100%-(JAZ_Kunden!C2/(31*24*60)))
und das halt für jeden Monat in der ersten Zeile.
Gruß Niels
Anzeige

318 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige