anbei eine Musterdatei (Excel 2013). Vorab: VBA Kenntnisse sind nicht vorhanden da ich bis jetzt mit Formeln zurechtgekommen bin. Außerdem könnte es sein, dass die Tabelle auch mal von einem Mac geöffnet und bearbeitet wird. Sollte eine Lösung möglich sein die VBA erfordert, nehme ich die auch.
Nun zu meinem Anliegen. Ich habe eine Tabelle mit Namen. Diese könnte sich ständig erweitern. Somit könnten irgendwann mehrere tausend Namen drinstehen. Ich würde gerne:
1. in B3 einen Namen eingeben. Wenn der Name vorhanden ist, soll in D3 der Inhalt des "passenden" Eintrages aus Spalte D erscheinen. Wenn der Name nicht vorhanden ist, soll der Cursor in die erste freie Zelle in Spalte B springen (in der Musterdatei wäre dies B28) oder den Eintrag von B3 automatisch dahin platzieren. Den Eintrag in Spalte D muss ich sowieso händisch machen, da es keine Regeln dafür gibt.
2. Sollte ich Aufgabe 1 erledigt haben, würde ich gerne die Eingabe in B3 mit folgenden Regeln erlauben. Es dürfen nur Großbuchstaben und keine Sonderbuchstaben (Ä,Ü,Ö, ...) eingegeben werden. Außerdem darf der Name nicht mehr als 10 Zeichen lang sein. Da ich (theoretisch) alleine mit der Liste arbeite, sollte ich das aber auch ohne Automatismus hinbekommen.
Ich arbeite täglich mit Excel. Allerdings mit Vorlagen, die ich mir über die Zeit (auch mit Hilfe von Foren) erstellt habe. SVERWEIS (auch Blattübergreifend), SUMMEWENN, INDIREKT und einige andere Funktionen sind darin vorhanden. Wenn ich eine Änderung machen muss, muss ich mich aber jedesmal wieder tief reindenken. Also wenn ich mir Vorlagen erstelle, kann ich mit den Funktionen umgehen - 1 Monat später habe ich das meistens aber wieder vergessen. Ich hoffe, dass das meine "Excelfähigkeiten" einigermaßen beschreibt.
Vielleicht könnt ihr mir ein paar Tipps geben, wie ich an die Sache rangehen muss. Ich traue mir schon zu, passende Funktionen anzupassen und anzuwenden (das habe ich bis jetzt immer geschafft). Und ich möchte verstehen, was Excel macht.
09.02.2024, 08:50 (Dieser Beitrag wurde zuletzt bearbeitet: 09.02.2024, 08:52 von HKindler.)
Hi,
ohne VBA wird das, was du schilderst nicht funktionieren. Aber ich schlage vor, dass dir mal die Eingabemaske von Excel anschaust. Funktional sollte sie dein Vorhaben abdecken. Da diese etwas versteckt ist und es mir zu viel Mühe macht dies für dich zu beschreiben, sollte du selbst mal danach suchen. Es gibt genug Anleitung dazu im Netz.
In Bezug auf nur Großbuchstaben ohne Sonderbuchstaben, fällt mit auf die Schnelle nicht wirklich etwas ohne VBA ein.
EDIT:
bei aktuellem Excel funktioniert diese Formel in der Datenüberprüfung
=SUMME(FINDEN(TEIL(A1;SEQUENZ(LÄNGE(A1));1);"ABCDEFGHIJKLMNOPQRSTUVWXYZ"))
Dabei ist A1 die Zelle für die die Überprüfung gilt und im String "ABC..." stehen alle zulässigen Zeichen. Wie gesagt funktioniert das nur, wenn dein Excel SEQUENZ() kennt.
EDIT2:
Bei mir funktioniert auch
=SUMME(FINDEN(TEIL(A1;ZEILE($1:$99);1);"ABCDEFGHIJKLMNOPQRSTUVWXYZ"))
Wobei bei ZEILE($1:$99) die 99 an die maximale Wortlänge anzupassen ist. Nur bis dahin wird überprüft, alles darüber hinaus kann ein beliebiges Zeichen sein.
Es müsste auch bei älterem Excel funktionieren, bin mir dabei aber nicht sicher. Eventuell muss es auch mit Strg-Shift-Enter abgeschlossen werden.
also SEQUENZ kann ich leider, wie du vermutet hast, nicht einsetzen.
Den ersten Teil meiner Aufgabe habe ich teilweise schon mit SVERWEIS umsetzen können. Also bekomme ich schonmal raus, ob der Name in der Liste vorhanden ist. Mit Großschreibung und Anzahl der Zeichen bekomme ich wahrscheinlich auch hin. Deine Summenformel muss ich mir erstmal in Ruhe anschauen. Hat auf Anhieb zwar nicht funktioniert, denke aber, dass ich zumindest Teile der Formel für Überprüfungen verwenden kann. Also Danke schonmal dafür. Ich werde jetzt den Weg
- umwandeln in Großbuchstaben
- Zeichenanzahl prüfen
- Prüfung auf Sonderzeichen
gehen und dann mit dem SVERWEIS einen möglichen Treffer anzeigen. Momentan ist die Liste noch übersichtlich. Also kann ich dann auch händisch runter und manuell eintragen. Mal sehen, wie weit ich komme.
09.02.2024, 10:31 (Dieser Beitrag wurde zuletzt bearbeitet: 09.02.2024, 10:31 von HKindler.)
Hi,
wenn sein Excel noch nicht einmal SEQUENZ() kennt, dann mit Sicherheit auch noch nicht das "durchsuchbare Dropdown", das noch nicht mal mein Excel 365 Version 2308 kennt Die Version 16.81 (24011420) auf meinem Mac (=aktueller Kanal) kennt diese Möglichkeit auch erst seit kurzem.
Ich denke immer noch, dass die Eingabemaske für ihn das einfachste Mittel ist.
warum eine Maske aufrufen? Man kann doch die Eingabe direkt in die Zelle B3 machen, oder?
Klug ist es, dann nicht nach unten zu scrollen, sondern die zweite Eingabe direkt in D3 machen!
Leider ist es eine alte Excel 2003 Datei, das Makro kannst du ja in deine Datei kopieren.
(09.02.2024, 06:34)PSMario schrieb: Vorab: VBA Kenntnisse sind nicht vorhanden
Daher dürfte Mario auf Dauer mit der Maske besser klar kommen. Obwohl, die Eingabemaske scheint es auf dem Mac nicht zu geben. Zumindest habe ich sie auf die schnelle nicht gefunden...
ich habe mir die Eingabemaske mal angeschaut. Ich glaube, für mein Vorhaben ist sie fast "zu mächtig". Aber: für eine andere Tabelle kann ich die eventuell nutzen.
Bei der Eingabemaske habe ich auch nichts gefunden, wie ich die Eingabebedingungen prüfen kann.
@HKindler: dein Vorschlag von "EDIT2" funktioniert. Wie du vermutet hast, muss es als Matrixformel abgeschlossen werden. Ich habe da nur noch ein Leerzeichen in die Liste mit aufgenommen. Und ich fange einen ungültigen Eintrag (#WERT!) mit ISTFEHLER so ab, dass dann ein "NEIN" erscheint. Grundsätzlich kann ich nachvollziehen, was die SUMME Formel macht. Allerdings detailiert nicht. Aber das hat wahrscheinlich damit zu tun, dass ich eigentlich nur Anwender bin...
Großschreibung erhalte ich mit GROSS(). Die Begrenzung auf 10 Zeichen konnte ich mit =WENN(LÄNGE(B3)>10;"ZU LANG";"OK") lösen. Somit erhalte ich in einer Zelle "OK" und in der Summenzelle von HKindler eine Zahl oder "NEIN". Und damit kann ich wahrscheinlich erstmal weitermachen. Der Rest ist dann nur noch "Optik" und Bedienfreundlichkeit.
Die VBA Geschichte schaue ich mir auch mal an. Aber wie ich mich kenne, werde ich an einer "für mich" funktionierenden Lösung nichts mehr ändern.
12.02.2024, 09:29 (Dieser Beitrag wurde zuletzt bearbeitet: 12.02.2024, 09:29 von HKindler.)
Hi,
da es immer gut ist, Formeln die man einsetzt, auch zu verstehen, versuche ich mal die Formel
=SUMME(FINDEN(TEIL(A1;ZEILE($1:$99);1);"ABCDEFGHIJKLMNOPQRSTUVWXYZ"))
zu erläutern.
Fangen wir mal an, von Innen nach Außen aufzudröseln. Es kann helfen, die Formelteile einfach in eine Zelle zu tippen und sich anzuschauen, was raus kommt. Oder man verwendet die Formelauswertung.
ZEILE($1:$99)
Das liefert dir die Zahlen 1 bis 99 als Spaltenvektor, also schön untereinander. Bei älterem Excel muss es zudem als Matrix-Formel mit Strg-Shift-Enter eingegeben werden (und hat dann die geschweiften Klammern drum rum außerdem sollte man vorher genügend Zellen markiert haben). Im Grunde ist es also dasselbe wie SEQUENZ(99) bei aktuellem Excel.
TEIL(A1;x;1)
Liefert dir das x-te Zeichen von A1. Die 1 steht für genau 1 Zeichen. Ist x größer als die Länge von A1, so bekommt man einen Leerstring.
In Verbindung mit dem Spaltenvektor von vorhin bekommt man so die einzelnen Zeichen von A1 wieder als Spaltenvektor schön untereinander.
FINDEN(x;"ABCD")
Sucht x in "ABCD" und gibt die Position von x zurück. Ist x leer, so wird 1 zurückgegeben. Wird x nicht gefunden, so erhält man einen #WERT!-Fehler. Die Groß-/Kleinschreibung wird beachtet. Ist Groß/Klein egal ("b" soll also auch in "ABCD" gefunden werden), so verwendet man SUCHEN().
In Verbindung mit der TEIL-Formel von vorher wird also jedes Zeichen (oder besser gesagt die ersten 99 Zeichen) von A1 untersucht und die Position zurück gegeben. Wird ein Zeichen nicht gefunden, so kommt an dieser Stelle der #WERT!-Fehler. Man hat jetzt also eine Menge Zahlen in einem Spaltenvektor. Hinten raus viele 1-en (also alle Werte jenseits der Länge von A1). Kommt ein Zeichen nicht in "ABCD" vor, so steht dort keine Zahl sondern ein Fehler.
SUMME(x)
Jetzt wird dieser Spaltenvektor summiert. Ist dort irgendwo ein Fehler, so vererbt sich dieser und die Summe ist ebenfalls #WERT!. Ist kein Fehler im Vektor, kommt eine Zahl raus, die größer 0 ist. Ja, in unserem Fall mit 99 Zeilen größer gleich 99 ist. Wir erinnern uns: Ein Leerstring ergibt 1. Somit würde ein leeres A1 99*1 ergeben. Ebenso z.B. "AAA", da ein "A" ebenfalls eine 1 gibt. Ein "B" gibt eine 2 etc.
Wenn alle Zeichen OK sind, haben wir also eine Zahl > 0. Eine Zahl ungleich 0 wird von der Datenüberprüfung als WAHR gewertet, alles andere (also auch ein Fehler!) wird als FALSCH gewertet. Somit haben wir unser Ziel erreicht.
Ich hoffe das hat geholfen, die Formel zu verstehen. Bei der Formel mit SEQUENZ wird halt noch berechnet, wie viele Zeichen A1 hat und es werden nur so viele Zeichen geprüft. Somit ist man nicht auf 99 (oder was auch immer) Zeichen beschränkt und prüft dabei immer nur so viele Zeichen, wie gerade nötig sind. Das spart natürlich enorm Rechenzeit.
Übereinstimmung finden und ggf. ergänzen
