Microsoft Excel

Herbers Excel/VBA-Archiv

Formelknobelei für spezifische Datengültigkeit ...


Betrifft: Formelknobelei für spezifische Datengültigkeit ... von: ... neopa C
Geschrieben am: 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
.. , - ...

  

Betrifft: AW: Formelknobelei für spezifische Datengültigkeit von: Daniel
Geschrieben am: 03.08.2016 19:00:53

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


  

Betrifft: Frage- und Multiplikationszeichen :-) von: WF
Geschrieben am: 03.08.2016 19:13:20

.


  

Betrifft: AW: eigentlich ganz einfach von: Daniel
Geschrieben am: 03.08.2016 19:24:23


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


  

Betrifft: ist sogar ne Formel bei uns von: WF
Geschrieben am: 03.08.2016 19:53:06

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


  

Betrifft: AW:Warum nicht? von: Daniel
Geschrieben am: 03.08.2016 20:06:26

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


  

Betrifft: logisch mach ich das - aber von: WF
Geschrieben am: 03.08.2016 20:30:16

nicht innerhalb von ein paar Minuten.

WF


  

Betrifft: Sehe bei diesen Auflagen ebenfalls keinen ... von: Luc:-?
Geschrieben am: 04.08.2016 04:01:41

…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 …


  

Betrifft: Hinweis: In H50 hätte 48 (+7) stehen müssen. owT von: Luc:-?
Geschrieben am: 04.08.2016 14:59:52

:-?


  

Betrifft: mit ANZAHL oder SUMME von: Josef B
Geschrieben am: 04.08.2016 20:30:40

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)<="z"))=LÄNGE(A1)

Gruss Sepp


  

Betrifft: AW: Und statt ZEILE(X1:X3 einfach {1;2;3} von: Daniel
Geschrieben am: 04.08.2016 21:04:13

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


  

Betrifft: also doch ZEILE(X1:X3 ) von: Josef B
Geschrieben am: 04.08.2016 22:21:24

Hallo Daniel

Du hast recht, {1;2;3} funktioniert in der Datengültigkeit nicht.

Gruss Sepp


  

Betrifft: AW: bin wieder online ... zumindest kurzzeitig ... von: ... neopa C
Geschrieben am: 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
.. , - ...


  

Betrifft: Das meiste wusstest Du schon vor 7 Jahren. von: WF
Geschrieben am: 05.08.2016 21:23:02

Das steht alles in unserer Formel Nr. 467 und die ist von Dir.
Übrigens Deine einzige Formel als Urheber.

WF


  

Betrifft: AW: sehe es etwas anders ... von: ... neopa C
Geschrieben am: 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
.. , - ...


  

Betrifft: für mich ist das Zeitverschwendung von: WF
Geschrieben am: 07.08.2016 12:17:21

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


  

Betrifft: AW: sehe es immer noch etwas anders owT von: ... neopa C
Geschrieben am: 07.08.2016 12:57:42

Gruß Werner
.. , - ...