Live-Forum - Die aktuellen Beiträge
Datum
Titel
09.05.2024 08:18:18
Feldformatierung (Einzahl/Mehrzahl) ohne VBA
08.05.2024 23:37:20
Datenverbindung automatisiert aktualisieren
08.05.2024 21:22:24
In vorhandener Formel Jahreszahl mit einbeziehen
08.05.2024 21:15:37
Koordinaten im XY-Diagramm per Cursor ändern.
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1876to1880
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1876to1880
1876to1880
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Kopieren Bereich 1:1 inkl. bedingt.Forma

Kopieren Bereich 1:1 inkl. bedingt.Forma
18.04.2022 09:46:24
KarlHeinz
Hall liebe Excel-Freunde,
für einen Bekannten soll ich eine sog. Lottoauswertung erstellen. Ich habe auch eine Tabelle hier aus dem Forum gefunden und etwas umgebaut. Eigentlich klappt alles bisher.
Mein Problem an dem ich nun hänge: Sobald ich die Daten im Bereich B3 bis X24 über den Button nach unten kopiere wird zwar alles getätigt, aber leider bleiben einige Formeln absolut. Das gilt vorallem die bedingten Formatierungen im Bereich C4:H15 und I4, die werden nicht mit nach unten kopiert; weiterhini habe ich ein Problem mit den Formel im Bereich J4:K15, leider werden auch diese nicht so nach unten kopiert wie ich das möchte. Ich weiß es sind absolute Werte, aber ich finde keine Lösung wie man auch solche "absoluten Bezüge" entsprechend nach unten kopiert, die Zeilenanzahl bleibt ja immer gleich, aber ...
Eine Beispiedatei habe ich hier angehängt und hoffe auf Hilfe
Datei: https://www.herber.de/bbs/user/152507.xlsm
Danke und noch ein frohes Osterfest

18
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Kopieren Bereich 1:1 inkl. bedingt.Forma
18.04.2022 10:12:20
Herbert_Grom
Hallo KarlHeinz,
warum willst du die Daten nach unten kopieren?
Servus
AW: Kopieren Bereich 1:1 inkl. bedingt.Forma
18.04.2022 13:07:40
KarlHeinz
Hallo Herbert,
da mein Bekannter die ganzen Spielscheine und Ergebnisse/Quoten usw. als Gesamtübersicht aufgelistet haben möchte. Ganz einfach ;-)
Hallo neopa,
werde es heute nachmittag mal umsetzen und testen. Danke.
Gruß
AW: mit Hilfe zweier benannter Formeln möglich ...
18.04.2022 11:24:16
neopa
Hallo KarlHeinz,
... dazu aktiviere zunächst die Zelle C4 und danach den Namensmanager. Dort definiere:
_ZiehZ =INDEX($B:$B;AGGREGAT(15;6;ZEILE(B4:B18)/(LINKS($B4:$B18;1)="Z")+1;1)):INDEX($B:$B;AGGREGAT(15;6;ZEILE(B4:B18)/(LINKS($B4:$B18;1)="Z")+7;1);1)
_GKl =INDEX($T:$T;AGGREGAT(15;6;ZEILE(B4:B18)/(LINKS($M4:$M18;1)="G")-13;1)):INDEX($X:$X;AGGREGAT(15;6;ZEILE(B4:B18)/(LINKS($M4:$M18;1)="G")-5;1);1)
Danach aktiviere bei weiterhin aktivierter Zelle C4 die bedingte Formatierung und ändere Deine wie folgt ab:
=ZÄHLENWENN(_ZiehZ;C4)*(LINKS($B4;1)="T") und Deine Farbe für "Treffer"
=(C4="")*(LINKS($B4;1)="T") für unausgefüllt
Die Zellformeln in J und K sowie M und N kannst Du nun sicherlich auch analog entsprechend anpassen.
Gruß Werner
.. , - ...
Anzeige
AW: mit Hilfe zweier benannter Formeln möglich ...
18.04.2022 14:29:51
KarlHeinz
Hallo neopa,
leider kenne ich die Formel mit "Aggregat" nicht und kann mir keine logische Funktion daraus herleiten. Da bin ich zu ?
Was mir im ersten Moment aufgefallen ist, dass ich diese beiden Formeln für den Namensmanager in den Namensmanager bei "bezieht sich auf" kopiert habe, aber da passiert nichts.
Was muss ich tun, damit diese beiden Formeln funktionieren und gibt es eine andere Formel für die Bereiche J, K, M und N die ich nachvollziehen kann?
es grüßt KarlHeiniz
AW: nun ...
18.04.2022 14:53:02
neopa
Hallo KarlHeinz,
... kopiere Dir mal die erste beiden Formeln zunächst in eine beliebig freie Zelle (kannst Du später wieder löschen). Das Ergebnis dessen ist zunächst: #WERT! Wenn Du Dir dann aber die Formel in der Eingabezeile markierst und danch die [F9] Taste betätigst, erhältst Du dort eine Matrixanzeige der vorhandenen Datenwerte aus dem Bereich B19:B24.
Zum Nutzen und Handling des Namensmanager sieh mal hier: https://www.online-excel.de/excel/singsel.php?f=60 und ff.
Jetzt etwas verständlicher? Dann setz es so um, wie von mir vorgegeben.
Gruß Werner
.. , - ...
Anzeige
AW: Kopieren Bereich 1:1 inkl. bedingt.Forma
18.04.2022 15:52:10
KarlHeinz
Hallo neopa,
also wenn ich die beiden Formeln
_ZiehZ =INDEX($B:$B;AGGREGAT(15;6;ZEILE(B4:B18)/(LINKS($B4:$B18;1)="Z")+1;1)):INDEX($B:$B;AGGREGAT(15;6;ZEILE(B4:B18)/(LINKS($B4:$B18;1)="Z")+7;1);1)
_GKl =INDEX($T:$T;AGGREGAT(15;6;ZEILE(B4:B18)/(LINKS($M4:$M18;1)="G")-13;1)):INDEX($X:$X;AGGREGAT(15;6;ZEILE(B4:B18)/(LINKS($M4:$M18;1)="G")-5;1);1)
in eine Zelle kopiere, dann wird ja die erste wieder gelöscht und bei der zweiten steht kein #WERT sondern die Formel.
Ich verstehe das nicht, tut mir leid ...
Wahrscheinlich zuviele Ostereier gegessen.
KarlHeinz
AW: die erste zu kopierende Formel ...
18.04.2022 16:11:12
neopa
Hallo KarlHeinz.
... ist natürlich nur folgende:
=INDEX($B:$B;AGGREGAT(15;6;ZEILE(B4:B18)/(LINKS($B4:$B18;1)="Z")+1;1)):INDEX($B:$B;AGGREGAT(15;6;ZEILE(B4:B18)/(LINKS($B4:$B18;1)="Z")+7;1);1)
_ZiehZ ist lediglich der Name den ich im Namensmanager dieser Formel zugewiesen habe, wenn ich diese dort einkopiert habe. Deswegen hatte ich Dir auch den Link https://www.online-excel.de/excel/singsel.php?f=60 mitgegeben, damit Du Dich mit "benannten" Formeln vertraut machen kannst.
Gruß Werner
.. , - ...
Anzeige
AW: Kopieren Bereich 1:1 inkl. bedingt.Forma
18.04.2022 16:54:39
KarlHeinz
Hallo neopa,
ich gebe auf, das kapiere ich nicht.
Mache es auf die altgewohnte Art: Kopiere nach unten und ändere danach die Bezugsformeln auf die neuen Zellenwerte.
Ist zwar umständlich, aber für mich logisch, da ich mit dieser Formel und dem Aggregat nicht klar komme.
Danke
AW: dann ...
18.04.2022 17:40:40
neopa
Hallo KarlHeinz,
... weil heute noch Ostern ist, stell ich Dir mal Deine Datei (als xlsX!) mit meinen Formelanpassungen hier bereit.
Zu Testzwecken habe ich mal ein paar Änderungen der gezogenen Zahlen vorgenommen und den angegebenen Bereich nach unten (ohne VBA) kopiert. https://www.herber.de/bbs/user/152510.xlsx
In Z1 und Z2 stehen die beiden Formeln die ich Dir bereits mitgeteilt habe und so in den Namensmanager übernommen wurden. Z1:Z2 kannst Du löschen. Es gibt jetzt noch eine dritte Formel dort, weil ja auch die Formel und Spalte U angepasst werden musste.
Solltest Du demnächst einen großen Lottogewinn erzielen, kannst Du mir ja einen kleinen Teil davon spendieren ;-)
Gruß Werner
.. , - ...
Anzeige
AW: dann ...
18.04.2022 22:27:36
Luschi
Hallo Werner,
2 Anmerkungen zur Formel für den definierten Namen '_ZiehZ'
- dieser Name muß definiert werden wenn der BS-Cursor auf der Zelle 'J4' steht
- und man sollte in der Formel statt ZEILE(B4:B18) es so es so definieren: ZEILE($B4:$B18)
  ansonsten geht dieser Spaltenbereich-Bereich spazieren bei mir z.B: XEN4:XEN18
- wenn der Cursor bei der Namensdefinition nicht auf Zelle 'J4' zeigt. dann kann sowas passieren 

=INDEX('Lotto Auto'!$B:$B;AGGREGAT(15;6;ZEILE('Lotto Auto'!XEO1048546:XEO1048560) /(LINKS('Lotto Auto'!$B1048546:$B1048560;1)="Z")+1;1)):INDEX('Lotto Auto'!$B:$B;AGGREGAT(15;6; ZEILE('Lotto Auto'!XEO1048546:XEO1048560)/(LINKS('Lotto Auto'!$B1048546:$B1048560;1)="Z")+7;1);1)
  und im Auswertebereich steht überall #ZAHL!
- bei mir stand da der Cursor auf Zelle 'U41'
Gruß von Luschi
aus klein-Paris
Anzeige
AW: in meinem gestrigen Beitrag 11.24 steht ...
19.04.2022 09:00:30
neopa
Hallo Luschi,
... folgendes: "dazu aktiviere zunächst die Zelle C4 und danach den Namensmanager" und dies auch gestern ganz bewusst auch fett hervorgehoben. Bezogen auf C4, weil ich zuerst lediglich die bedingte Formatierung der Fragestellung entsprechend angepasst hatte. Die Formeln für J4:K4 und M4:N4 hab ich erst danach definiert. Und ja die Formel in einer anderen Zelle betrachtet "liest" sich besser, wenn ich den Namen bei aktivierter Zelle J4 definiert hätte bzw. den Spaltenbezug in der ZEILE()-Teilformel absolut gesetzt hätte. Aber ein "muss", wie Du schreibst, ist nicht notwendig, wie Du ja an meiner Datei erkennen kannst. Ein "muss" ist lediglich die Definition der benannten Formel bei einer aktivierten Zeile der Zeile4 und nicht in einer anderen Zeile, wie Du es getan hast.
Gruß Werner
.. , - ...
Anzeige
AW: dann ...
19.04.2022 13:54:32
KarlHeinz
Hallo neopa,
gerne werde ich was spenden, wenn ich gewinnen würde, aber leider ist diese Tabelle für jemanden anders und nicht für mich als "Spieler".
AW: dann ... Frage an neopa
22.04.2022 10:30:06
KarlHeinz
Hallo neopa,
nun habe ich Deine Datei mit meinem Bekannten zusammen getestet und er hat die Daten so eingegeben. Es hat eigentlch alles funktioniert bis zur Zeile 599 beim "kopieren" bzw. bei der Eingabe.
Nun kommen Fehlerhinweise, das betrifft der Bereich B600 bis X621, insbesondere Spalte M und N (#NV) und dann wieder B644 bis X665 in den Spalten M und N (#Zahl).
Mein Bekannter war eigentlich froh, dass es so klappt, aber warum nun diese Fehlermeldung?
HIer die Datei: https://www.herber.de/bbs/user/152587.xlsm
es grüßt KarlHeinz und Marco
Anzeige
AW: Kopieren Bereich 1:1 inkl. bedingt.Forma
19.04.2022 13:32:04
KarlHeinz
Hallo,
aus "Spaß und Freude" habe ich diese 'Oster-Beispieldatei' (siehe unten) heruntergeladen. Nun ganz einfach die Werte entsprechend einkopiert und leider leider keine Resultate. Ich habe eben diese Datei: https://www.herber.de/bbs/user/152510.xlsx genommen und über kopieren und einfügen (NUR WERTE) versucht, ob es klappt. Ne, leider nicht. Irgendwie klappt das nicht.
Also: bleibt doch das kopieren nach unten und händisch ändern.
Gruss KarlHeinz
AW: Kopieren Bereich 1:1 inkl. bedingt.Forma
19.04.2022 13:50:24
KarlHeinz
Hallo liebe Leute,
bevor hier "Excel-Auseinandersetzungen" entstehen:
Mir ging es rein darum, kann man mit dem "kopieren-makro" auch die gesamte Formelprozedur im Format 1 : 1 nach unten kopieren. Wahrscheinlich falsch verstanden oder ? Scheinbar geht es nicht, denn ich hatte es auch mit dieser Bsp-Datei probiert und ?
Wahrscheinlich "Rechnerabhängig"? Zukunft fragt nach!
Anzeige
AW: so nicht nachvollziehbar, denn ...
19.04.2022 14:26:18
neopa
Hallo KarlHeinz,
... die Arbeitsmappe belegt doch mE eindeutig, dass es funktioniert. Und zwar natürlich komplett mit Formeln. Dafür hatte ich ja auch die angepasste Formeldefinition vorgenommen. Markiere also B3:X24 und kopiere diese 1:1 komplett nach unten z.B. auch nach B54 (mit oder ohne VBA, ich hab es ohne gemacht).
Dort kannst Du die Werte der Ziehung in Spalte Q löschen und neu einschreiben oder einfach ändern. Meine Auswertung passt sich dort dem an. Die Zahlen der dortigen Tippreihen kannst Du natürlich ebenfalls auch beliebig ändern. Und natürlich kannst Du alles auch ein zweites, drittes ... mal komplett nach unten kopieren.
Gruß Werner
.. , - ...
Anzeige
AW: Frage nochmals an neopa
22.04.2022 15:36:11
KarlHeinz
Hallo neopa,
nun habe ich Deine Datei mit meinem Bekannten zusammen getestet und er hat die Daten so eingegeben. Es hat eigentlch alles funktioniert bis zur Zeile 599 beim "kopieren" bzw. bei der Eingabe.
Nun kommen Fehlerhinweise, das betrifft der Bereich B600 bis X621, insbesondere Spalte M und N (#NV) und dann wieder B644 bis X665 in den Spalten M und N (#Zahl).
Mein Bekannter war eigentlich froh, dass es so klappt, aber warum nun diese Fehlermeldung?
HIer die Datei: https://www.herber.de/bbs/user/152587.xlsm
es grüßt KarlHeinz und Marco
AW: Änderung der Ausgangsbedingungen ...
25.04.2022 15:45:41
neopa
Hallo KarlHeinz und Marco,
... führen natürlich zu Fehlermeldung(en).
Vorgegeben war, dass in Spalte M in der Zeile des Ziehungsdatums "Gewinnsumme" stand . Ihr habt daraus nachträglich "Summe Gewinn" gemacht. Damit funktioniert die Formel natürlich nicht mehr.
Gruß Werner
.. , - ...

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige