Frage von iMPerFekTioN, 68

Wie kann man in Excel in bestimmten Bereich nur eine gewisse Anzahl an Zahlen zulassen, sonst Fehlermeldung ausgeben und Programm anhalten?

Hallo Community :),

wollte mal was loswerden, bzw. erfragen... Beschäftigt mich schon etwas länger und ich hab bisher nichts gefunden, weil mir einfach nicht einfallen will, nach was man da genau suchen muss :)

Also folgendes Problem:

Ich habe eine Bedingte Formatierung in den Zellen B1:F1 Wenn in diesen Zellen eine 1 steht, werden diese (je nach Spalte) hintergründlich eingefärbt.

Spalte B Gelb, C Blau usw...

Wenn jetzt 3 in der Zeile 1 mehr als 3 1er vorkommen, also z.B. B1="" C1="1" D1=1 E1=1 F1=1, soll sobald die 1 eingetragen wurde eine Fehlermeldung kommen in der steht, "Sorry, hier gibts schon 3 1er!"

Das Problem ist nur, das die Eingabe in die Zellen per Formel gemacht wir, sprich die 1ner werden per =SVERWEIS() befüllt.

Formel:

=WENN(SVERWEIS(A1;Tabelle1.A1:B10;2;0)=1;"1";"")

Also keine Ahnung ob das geht, wäre aber echt cool, dass sobald dann die vierte 1 in einer Zeile steht, so eine Art Gültigkeits Fehlermeldung aufgerufen wird.

Danke für jede Hilfe :p

Grüße,

iMPerFekTioN

Hilfreichste Antwort - ausgezeichnet vom Fragesteller
von Oubyi, Community-Experte für Excel, 28

Wie entsteht denn die Liste in Tabelle1, also wie ändern sich dort die Daten?
Wenn z.B. Spalte A immer gleich bleibt und nur die Werte in B sich ändern, könntest Du DA, also in Tabelle1 Spalte B, eine Datenüberprüfung ---> Benutzerdefiniert, Formel:
=ZÄHLENWENN(Tabelle2!B1:F1;"1")<=3
Dann würde sobald in Spalte A der Wert aus A1 steht und dann in Spalte B eine 1 eingetragen werden soll, das verhindert, wenn dadurch in B1:F1 die Anzahl der 1en zu groß wird.
Wenn natürlich in B schon eine 1 steht und DANACH in A der Wert auf den Wert aus A1 geändert wird, klappt das nicht.
Dazu müsstest Du in Spalte A noch eine Gültigkeit einbauen, denke ich, werde aber erst näher darüber nachdenken, wenn ich weiß ob es nötig ist (:o)
Aber vielleicht hilft Dir ja meine Idee weiter, um selber eine Lösung zu finden.
Sonst lade vielleicht doch mal eine Beispieltabelle mit Beschreibung bei einem Upload-Service (z.B. http://www.file-upload.net) hoch.

P.S.: Ist es übrigens Absicht, dass Du im SVERWEIS die "1" als Text ausgibst und nicht als Zahl ( 1 ) ?

Kommentar von iMPerFekTioN ,

Ich zeig dir das mal an folgendem Aufbau:

Tabelle 1

A1: A7 stehen Datumsangaben, A1 steht z.B. 10.11.16,
in B1:B7 Trage ich 1. ein (manuell)

Sagen wir A1 steht 10.11.16 und in B1 steht eine 1.

In der Tabelle Suche steht in A1 wieder das Datum 10.11.16.

Jetzt kommt in B1 die Funktion:

=WENN(SVERWEIS(A1;Tabelle1.A1:B10;2;0)=1;"1";"")

So entstehen die 1er und so wird das abgefragt.

In C1 wird dann der nächste Bereich abgefragt

=WENN(SVERWEIS(A1;Tabelle1.D1:E10;2;0)=1;"1";"")

In Tabelle 1 steht dann in D1 wieder das Datum 10.11.16 und in E1 keine 1.

Also wird in der Tabelle Suche "" eingetragen usw.

Das geht in der Tabelle Suche dann von A bis G also 5 abfragen für 5 Bereiche.

Jetzt möchte ich, sobald in der Tabelle Suche in der 2 Zeile mehr als fünf 1er stehen,  (also, es dürfen nicht 5 mal die gleichen Daten mit 1 markiert sein in Tabelle1) das dann diese Fehlermeldung oder diese allgemeine Meldung kommt "Hier stehen schon fünf 1er"

Kommentar von Oubyi ,

Ok, dann müsstest Du, wie von mir beschrieben Gültigkeitsprüfungen nicht nur in Spalte B von Tabelle1 machen sondern auch in Spalte E und den weiteren Spalten, in denen Du die 1 manuell einträgst.
Sobald Du dann versuchst in eine der Spalten eine 1 einzutragen, die bewirken würde, dass in B1:F1 mehr als 3 (bzw. 5) 1 stehen würde das von der Datenüberprüfung mokiert.

P.S.: Muss jetzt zur Arbeit, schaue aber morgen früh wieder vorbei.

Kommentar von Oubyi ,

Danke fürs

Expertenantwort
von Iamiam, Community-Experte für Excel, 16

ich gehe mal davon aus, dass es auch für andere Zahlen als 1 automatisch so sein soll.

Die Hauptschwierigkeit ist die Vermeidung eines Zirkelbezugs, da die Formel ja in derselben Spalte das Einfügen bemerken soll.

Ich bin (leider mit anderen Bezügen, hab keine Zeit mehr, das umzuschreiben, aber Du bist ja versiert genug, das zu machen, "Andere" ist mein Suchargument, kannst Du ja durch i.e. $-Bezug ersetzen) auf Folgendes gekommen:

Formel nur gültig für Zeile 7, passt sich aber beim Kopieren an:

=WENN((SUMMENPRODUKT((N$2:N6=SVERWEIS("Andere";$L$2:$P$10;2;0))*1)+SUMMENPRODUKT((N8:N$8=SVERWEIS("Andere";$L$2:$P$10;2;0))*1))>2;"enthält diese Zahl schon mindestens 3x!";SVERWEIS("Andere";$L$2:$P$10;2;0))

oder, wenn auch das Verweisergebnis in die Meldung mit rein soll:

=WENN(SUMMENPRODUKT((N$2:N6=SVERWEIS("Andere";$L$2:$P$10;2;0))*1)+SUMMENPRODUKT((N8:N$10=SVERWEIS("Andere";$L$2:$P$10;2;0))*1)>2;"enthält die "&SVERWEIS("Andere";$L$2:$P$10;2;0)&" schon mindestens 3x!";SVERWEIS("Andere";$L$2:$P$10;2;0))

Beachte, dass in der Adresse N$2:N6 die Bereichsbegrenzung $, die Zelle davor/danach aber relativ gesetzt wird, ebenso im zweiten Summenprodukt N8:N$10.

So kannst Du die Formel über alle benötigten Zellen der Spalte kopieren, vorzugsweise erst ab Zeile 5, weil weiter oben keine 4.Nennung erfolgen kann (bei Start in Zeile 2).

Kommentar von Iamiam ,

so, jetzt hab ich mit allen Nacharbeiten und Korrekturen doch meinen (nicht allzu wichtigen) Termin versäumt, aber das Anpassen der Adressen überlasse ich trotzdem Dir, Mag jetzt eine Pause machen!

Übrigens: mit Index/Vergleich könntest Du die Spalten schadlos verschieben, aber die SVerweis-Benutzer, -diejenigen, die es anders könnten- gehören sowieso gestraft ;)

Kommentar von Iamiam ,

Kommando zurück! sehe grade, dass es beim Ausfüllen der Spalte mit derselben Formel auch Zirkelbezug ergibt, da bin ich jetzt erst mal ratlos!

 schadlos verschieben heißt, dass Du keine Spalte im SVerweis erst anpassen musst!

Kommentar von iMPerFekTioN ,

Also:) vielen Dank erstmal,

Es geht hier aber wirklich NUR um 1er und nichts anderes:p

Was meinst du denn mit schadlos verschieben?

Und die Pause kannst du klar machen, so wichtig hin ich nich😀😂

Kommentar von iMPerFekTioN ,

Ach jetzt! Ja egal;)

Ich schau mir das nachm arbeiten nochmal genauer an...

Ich wollte eigentlich erstmal wissen ob das überhaupt geht was ich vor hab:/

Expertenantwort
von Ninombre, Community-Experte für Excel, 32

Irgendwo gibst du aber doch was ein, das dann über sverweis zu einer 1 führt. Dort könntest du mit Datengültigkeitsprüfung arbeiten. Formel zählenwenn auf den Ergebnisbereich

Kommentar von iMPerFekTioN ,

Das dachte ich mir auch, nur wenn der Bereich per Formel die Grenze überschreitet, zieht es nicht.

Antwort
von asta311, 42

In der Bed. Format kannst du auch zwei bedingungen verknüpfen, anhalten geht damit aber nicht.


Keine passende Antwort gefunden?

Fragen Sie die Community