Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1504to1508
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Formelknobelei für spezifische Datengültigkeit ...

Formelknobelei für spezifische Datengültigkeit ...
03.08.2016 18:39:32
...
Hallo,
... gesucht ist eine möglichst "knackige" Formel (keine UDF) für folgende Problemstellung:
In einer Zelle soll eine Kombination von 1 bis zu max 3 Zeichen eingegeben werden können, für die an keiner Stelle Sonderzeichen erlaubt sind. Also erlaubt sind lediglich Buchstaben und Ziffern, die aber in beliebiger Anordnungskombination.
Habe zwar schon eine Formel, die mich jedoch auf Grund ihrer Länge wenig begeistert.
Ich werde auf Eure Beiträge frühesten wahrscheinlich erst Morgen gegen Mittag reagieren können. Vielen Dank deshalb schon jetzt fürs Nachdenken und einen schönen Abend.
Gruß Werner
.. , - ...

16
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Formelknobelei für spezifische Datengültigkeit
03.08.2016 19:00:53
Daniel
Hi
es wäre gut gewesen wenn du wenigstens die Länge deiner Formel angegeben hättest, damit man weiß ob sich überhaupt lohnt seine Idee hier vorzuschlagen.
aber ich eröffne den Wettberwerb mal hiermit:
=SUMMENPRODUKT(1*(ISTZAHL(SUCHEN(TEIL(A1&"---";ZEILE($A$1:$A$3);1); "1234567890abcdefghijklmnopqrstuvwxyz"))))=LÄNGE(A1) 
falls Groß, oder Kleinschreibung berücktsichtigt werden muss, das SUCHEN durch FINDEN ersetzen und im Textstring mit den zugelassenen Zeichen entsprechend ändern.
falls auch Umlaute oder bestimmte Zeichen zugelassen sein sollen, diese einfach an entsprechender Stelle ergänzen.
Gruß Daniel
Anzeige
Frage- und Multiplikationszeichen :-)
03.08.2016 19:13:20
WF
.
AW: eigentlich ganz einfach
03.08.2016 19:24:23
Daniel
auch kein Problem, dann eben FINDEN statt suchen.
FINDEN kennt keine Joker.
wenn dann Gross/Kleinschreibung nicht berücksichtig werden soll einfach den Text mit Gross oder Klein umwandeln:
=SUMMENPRODUKT(1*(ISTZAHL(FINDEN(TEIL(KLEIN(A1)&"---";ZEILE($A$1:$A$3);1); "1234567890abcdefghijklmnopqrstuvwxyz"))))=LÄNGE(A1)
Gruß Daniel
ist sogar ne Formel bei uns
03.08.2016 19:53:06
WF
http://www.excelformeln.de/formeln.html?welcher=467
da werden ? und * auch nicht erwähnt
für hier angepasst:
=ISTZAHL(SUMMENPRODUKT(FINDEN(TEIL(A1;ZEILE(X1:X3);1);"1234567890abcdefghijklmnopqrstuvwxyz";1))) *(LÄNGE(A1)<4)
WF
Anzeige
AW:Warum nicht?
03.08.2016 20:06:26
Daniel
Wäre es dann nicht spätstens jetzt an der Zeit, dass du die Seite mal überarbeitest und die Infos zum Thema Jokerzeichen mit aufnimmst?
Gruß Daniel
logisch mach ich das - aber
03.08.2016 20:30:16
WF
nicht innerhalb von ein paar Minuten.
WF
Sehe bei diesen Auflagen ebenfalls keinen ...
04.08.2016 04:01:41
Luc:-?
…besonders „knackigen“ Weg, Werner,
dazu ist Xl bei TextFktt wohl immernoch zu schwach bestückt. Außerdem ist bei der Gültigkeit ja auch nicht jede Fml möglich:
 FGHIJK
45A1xWAHRWAHRWAHRWAHRWAHR
46x+2FALSCHFALSCHFALSCHFALSCHFALSCH
47zB0WAHRWAHRWAHRWAHRWAHR
48kl?FALSCHFALSCHFALSCHFALSCHFALSCH
49*Q#FALSCHFALSCHFALSCHFALSCHFALSCH
50FmlLänge:2351 (+4)122116 (+4)193
51Formeln:G45[:G49]:=MaskOn(F45;"anu";1)=""
52(VLike-Vs1.3!)H45[:H49]: {=VLike(TEIL(F45;ZEILE(A1:A3);1);"[0-9A-ZÄÖÜa-zßäöü]")}
53(RepOpt nichtI45[:I49]:=WENNFEHLER(HEXINDEZ(RepOpt("substitute";40;1;2;F45;VSplit
54publiziert!)                      ("GHIJKLMNOPQRSTUVWXYZghijklmnopqrstuvwxyz";"");""))"";FALSCH)
55 J45[:J49]:=WENNFEHLER(SUMMENPRODUKT(FINDEN(KLEIN(TEIL(F45;SPALTE(A1:C1);1));
56                       "0123456789abcdefghijklmnopqrstuvwxyzßäöü"))>0;FALSCH)
57 K45[:K49]:=SUMMENPRODUKT((CODE(TEIL(GROSS(F45);SPALTE(A1:C1);1))>47)*
58                         (CODE(TEIL(GROSS(F45);SPALTE(A1:C1);1))<58)+(CODE(TEIL(GROSS(F45);
59                         SPALTE(A1:C1);1))>64)*(CODE(TEIL(GROSS(F45);SPALTE(A1:C1);1))<91))=3

So richtig „knackig“ wird's erst bei blau (mit UDF, deshalb hier ggf über benannte Fmln)! ;-)
Gruß, Luc :-?
Besser informiert mit …
Anzeige
Hinweis: In H50 hätte 48 (+7) stehen müssen. owT
04.08.2016 14:59:52
Luc:-?
:-?
mit ANZAHL oder SUMME
04.08.2016 20:30:40
Josef
Hallo Werner & @
Auf Summenprodukt kann in diesem Fall getrost verzichtet werden.
Hier genügt die Funktion Anzahl.
Und statt ZEILE(X1:X3 einfach {1;2;3}
=ANZAHL(FINDEN(TEIL(KLEIN(A1)&"---";{1;2;3};1); "1234567890abcdefghijklmnopqrstuvwxyz"))=LÄNGE(A1)
Das funktioniert natürlich bei allen gezeigten Varianten.
Wenn die Ansprüche betreff Ausschluss von Sonderzeichen etwas weniger hoch sind, reicht auch:
SUMME((TEIL(A1;{1;2;3};1)>="0")*(TEIL(A1;{1;2;3};1) Gruss Sepp
AW: Und statt ZEILE(X1:X3 einfach {1;2;3}
04.08.2016 21:04:13
Daniel
Hi Sepp
das funktioniert nicht, wenn die Formel in der Datengültigkeit eingesetzt werden soll (siehe Betreff der Frage)
dort sind Arraykonstanten nicht zulässig.
Gruß Daniel
Anzeige
also doch ZEILE(X1:X3 )
04.08.2016 22:21:24
Josef
Hallo Daniel
Du hast recht, {1;2;3} funktioniert in der Datengültigkeit nicht.
Gruss Sepp
AW: bin wieder online ... zumindest kurzzeitig ...
05.08.2016 20:07:36
...
Hallo an alle,
... war gestern und heute vom Netz "abgehangen".
Aber nun zunächst mein Dank nochmal an alle Antworter!
Zwischenzeitlich hatte ich selbst eine nur etwa halb so lange Formel gefunden, wie ich sie zunächst am Dienstag späten Nachmittag ursprünglich hatte (ähnlich wie Daniels Vorschlag, nur eben mit SUMME() anstelle SUMMENPRODUKT(), weil in der Datengültigkeit - wie auch in bedingter Formatierung - jede Formel schon wie eine Matrixformel ausgewertet wird).
Dann fiel mir auch ein, SUMME(ISTZAHL(...)) durch ANZAHL() zu ersetzen, wie es hier auch Sepp in seiner ersten Formel vorgeschlagen hat und bin dadurch zu einer fast identischen Formel gelangt, wie sie Sepp als 1. Formel eingestellt hat.
Allerdings reicht es, anstelle drei nur zwei unzulässiger Zeichen an den zu prüfenden Zellentext anzuketten, denn die Datengültigkeit kann sowieso keine Leerkette als falsch erkennen. Dies kann man nur mittels bedingter Formatierung visualisieren.
Meine zwischenzeitlich schon eingesetzte Lösungsformel als Datengültigkeit nutzt aber eine benannte Zelle Z_Dat mit ="1234567890abcdefghijklmnopqrstuvwxyz" und folgende benannte Formel (Name beliebig):
=ANZAHL(FINDEN(TEIL(KLEIN(!A1)&"?";{1;2;3};1);Z_Dat))=LÄNGE(!A1)
Damit erkennt ihr, dass man mittels benannten Formeln sehr wohl auch Matrixkonstanten in der Datengültigkeit einsetzen kann.
Die benannte Zelle Z_Dat hab ich eingesetzt, weil die spez. Datengültigkeit nicht nur in einer sondern in vielen Zellen und mehreren Tabellen der Mappe genutzt werden soll (diese nicht ganz unwesentliche Sache hatte ich aber vergessen anzugeben).
Gruß Werner
.. , - ...
Anzeige
Das meiste wusstest Du schon vor 7 Jahren.
05.08.2016 21:23:02
WF
Das steht alles in unserer Formel Nr. 467 und die ist von Dir.
Übrigens Deine einzige Formel als Urheber.
WF
AW: sehe es etwas anders ...
07.08.2016 11:43:30
...
Hallo WF,
... im Laufe der Zeit vergesse ich (aber sicherlich nicht nur ich) so manches (teils gewollt aber meistens ungewollt). So erinnerte ich mich am Mittwoch nicht, dass ich mal ein solch ähnliches Problem wie hier gelöst hatte, die dann als Formel Nr 467 in Eurer Seite eingestellt wurde.
Aber ich lernte immer und lerne noch immer dazu, mal weniger und mal mehr und sei es was ich schon mal wusste oder eben auch Neues. Das ist mir wichtig.
Gruß Werner
.. , - ...
Anzeige
für mich ist das Zeitverschwendung
07.08.2016 12:17:21
WF
Hi,
man sollte auf Kenntnisse, die man schon hatte (Archivierung) aufbauen und die dann optimieren.
Das nennt man Fortschritt.
Du erfindest zeitaufwendig das Rad zum zweiten und das ist manchmal eiförmig.
Das nennt man Rückschritt.
WF
AW: sehe es immer noch etwas anders owT
07.08.2016 12:57:42
...
Gruß Werner
.. , - ...

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige