Hilfe beim erstellen einer Excel Vorlage für ein Lottobingo

[RedAdair]

Hallo zusammen,

ich habe folgendes vor - und scheitere gerade kläglich daran etwas in excel zusammenzubauen.

Also - wir sind eine private tipprunde. Jeder gibt 10 Zahlen zwischen 1 und 49 ab. Es sind 30 Mitspieler. Die 10 Zahlen sind sozusagen seine Lottozahlen.

Es werden jeden Samstag die Lottozahlen (nur die 6 Hauptzahlen) auf dem "großen schein" kontrolliert und jede gezogene Zahl wird markiert.
Das spiel geht so viele Wochen, bis einer (oder mehrere) seine persönlichen 10 Zahlen aus den vorausgegangenen Ziehungen komplett hat. Es ist eine Mischung zwischen Lotto und Bingo.

Jetzt ist es natürlich sehr müßig jeden Samstag die Lootzahlen mit der Tabelle abzugleichen. Sowas muss man doch mittlerweile automatisieren können? Mir schwebt eine Excel Tabelle oder eine Website vor.

Dort sind die Ganzen Spieler aufgelistet. Jeder Spieler eine Zeile.
Zeile mit lfd.Nr.; Name; Vorname ; und den 10 persönlichen Zahlen.

Irgendwo auf der Seite / dem Tabellenblatt einen bereich, in dem die wöchentlich gezogenen Lottozahlen untereinander eingetragen werden ( Nr. der Ziehung;Datum; die 6 Zahlen)

Nach dem Eintragen der gezogenen zahlen, soll sich die Tabelle automatisch überprüfen und bei einem Treffer soll sich die betroffene Zahl(en) rot einfärben (mit Schraffur).

Ich habe keine Ahnung ob es verständlich erklärt ist - hoffe es aber sehr. Macht man das mit Excel? Wie geht man da am besten vor?
Bedingte Formatierung kann man wohl nicht auf mehrere Bereich anwenden?

vielleicht hat ja jemand eine idee dazu

viele Grüße

 

[YCbCr]

Also wenn ich das richtig verstanden habe sollen in einem Bereich („Bingokarte“) alle Felder Eingefärbt / Formatiert werden, die in einem anderen Bereich („gezogene Lottozahlen“) mindestens einmal vorkommen.

Eine bedingte Formatierung wäre hier schon die richtige Herangehensweise, allerdings mit einer Formel als Bedingung. Dann wird die bedingte Formatierung angewendet, wenn die Formel ≠ 0 ist.

Die ZÄHLENWENN Funktion gibt zurück, wie oft ein Wert in einem Bereich vorkommt. Kombiniert sollte das um gewünschten Ergebnis führen.

Sagen wir mal der zu formatierende Bereich beginnt bei D2 (nach 3 Spalten für Nr., Name und Vorname und einer Zeile für Spaltenüberschriften) und lottozahlen ist ein benannter Bereich, in den die gezogenen Zahlen eingetragen werden. Dann würde die Formel für die bedingte Formatierung =ZÄHLENWENN(lottozahlen;D2) lauten.

 

[RedAdair]

Hallo @YCbCr und danke schonmal für deine Antwort.
Ich habe mal einen Screenshot beigefügt- damit wird es wahrscheinlich deutlicher was ich vorhabe.

Links stehen die "Wunschzahlen" der Spieler von 1-49 - und rechts werden die gezogenen Lottozahlen eingetragen.
In der linken Tabelle sollen sich die gezogenen Felder farblich abheben (wie in dem Beispiel)

Da habe ich hier mit der Bedingten Formatierung und dem Zellwert versucht. Aber da muss ich ja für jede Zelle eine eigene Formel erstellen - da werde ich ja noch bekloppeter als ich ohnehin schon bin ;-)

Kann ich dann den ganzen Bereich von P8 - U19 in meinem Fall als "lottozahlen" für die Formel definieren?

Ich steh da leider echt auf dem schlauch...

 

[YCbCr]

Ich hab das zur Veranschaulichung mal nachgebaut:

Aber da muss ich ja für jede Zelle eine eigene Formel erstellen - da werde ich ja noch bekloppeter als ich ohnehin schon bin ;-)

Eigentlich nicht. Zellenreferenzen in Formeln zur bedingten Formatierung sind relativ zum formatierten Bereich. D8 in der Formel in meinem Screenshot bezieht sich also für alle Zellen im formatierten Bereich D8:M15 immer auf die Zelle, die gerade formatiert wird. (Und E8 wäre immer die Zelle rechts daneben, usw.)

Kann ich dann den ganzen Bereich von P8 - U19 in meinem Fall als "lottozahlen" für die Formel definieren?

Ja; am einfachsten indem du den Bereich auswählst und dann oben links in der Ecke (wo in deinem Screenshot „A1“ steht) die gewünschte Bezeichnung eingibst. Benannte Bereiche gelten auch für die komplette Mappe, d.h. du kannst dann in allen Tabellen lottozahlen als alias für $Tabelle1.$P8:$U19 verwenden und wenn du du den Bereich mal änderst musst du nicht alle Formeln die ihn verwenden einzeln anpassen.

 

[RedAdair]

Unfassbar - großartig! vielen lieben Dank! damit hast du den Lottobingotipp am Leben gehalten

Es kann so einfach sein wenn man weiß wie - ich hab mir da wirklich einen abgebrochen- aber mit einer Formel alles zu machen ist schon SEHR elegant

Danke nochamls!

 

[RedAdair]

@YCbCr Hey - die erste Runde ist gestern zu Ende gegangen Deine Lösung hat prima geklappt!
Jetzt hätte ich aber noch 2 nachfragen...

1. ich würde gerne alles automatisch nach Alphabet sortieren lassen (Nachname). d.h. wenn ich einen Eintrag mache dass sich die komplette Zeile dann korrekt einsortiert. Wobei natürlich die Nummerierung in der ersten Spalte bleiben soll (1-44). Es kommen schonmal ein paar leute dazu, die dann mit Hand da einsortieren geht zwar - aber vielleicht kann man das mit excel auch automatisiert machen.

und 2. kann man hinter die zahlen quasi hier im Beispiel in der Spalte N es so einstellen, dass bei noch fehlenden 3 Zahlen - oder anders ausgedrückt - wenn man 7 zahlen hat (rot markiert) dass dann in der Spalte N eine 3 auftaucht. hat man schon 8 zahlen eine 2 usw. quasi einen countdown bis zur 10 richtigen Zahl.

Ich hoffe das war irgendwie verständlich erklärt.

Ansonsten bin ich wirklich super Happy mit der Lösung wie sie jetzt ist.

Vielleicht noch das sahnehäubchen... Wie würdet ihr vorgehen um dieses Excel Blatt zu veröffentlichen - aber dass nur ich änderungen vornehmen kann.
Würde gerne einen QR Code dort einfügen unter dem man das aktuelle Spiel (Arbeitsblatt) aufrufen kann, damit tatsächlich jeder den aktuellen stand sehen kann.

Hätte irgendwie an Microsoft One Drive gedacht - das abrufen soll ohne Passwort stattfinden. Also nur eine reine Lese Funktion. Hab das mal mit einem Blattschutz versucht - aber grandios gescheitert...

Wünsche euch noch einen schönen Sonntag!

gruß RedAdair

 

[YCbCr]

ich würde gerne alles automatisch nach Alphabet sortieren lassen (Nachname). d.h. wenn ich einen Eintrag mache dass sich die komplette Zeile dann korrekt einsortiert. Wobei natürlich die Nummerierung in der ersten Spalte bleiben soll (1-44).

Ich nehme mal an mit „die Nummerierung in der ersten Spalte“ ist eine zusätzliche Spalte gemeint, die in deinem alten Beispielscreenshot nicht enthalten ist und nicht die Zeilennummer von Excel selbst?
Jedenfalls kannst du zum Sortieren einfach den Bereich inkl. Überschriften markieren und dann einen Autofilter hinzufügen (das Excel Symbol dafür ist ein Trichter mit der Beschriftung „Filter“). Dann kannst du einfach auf den Dropdown Pfeil neben Name klicken und „Von A bis Z sortieren“ auswählen. Die restlichen Spalten im gefilterten Bereich werden dann automatisch mit sortiert. Zum filtern kannst du den Filter natürlich auch noch benutzen. Die zwei Leerzeilen zwischen Überschriften und Daten würde ich allerdings raus nehmen.

kann man hinter die zahlen quasi hier im Beispiel in der Spalte N es so einstellen, dass bei noch fehlenden 3 Zahlen - oder anders ausgedrückt - wenn man 7 zahlen hat (rot markiert) dass dann in der Spalte N eine 3 auftaucht. hat man schon 8 zahlen eine 2 usw. quasi einen countdown bis zur 10 richtigen Zahl.

Leider ist die einzige (simple) Lösung, die mir dafür einfiele eine Matrixformel:
{=10-ZÄHLENWENN(ZÄHLENWENN(lottozahlen;E6:N6);">0")}
Die {} gibst du dabei nicht selbst ein, sondern drückst Strg+Shift+Enter wenn du mit dem Eingeben der Formel fertig bist. Ziehen der Formel auf andere Felder geht auch nicht, weil das stattdessen den (in dem Fall irrelevanten) Ausgabebereich ändert (zumindest in LibreOffice Calc). Kopieren & Einfügen geht aber noch, inkl. automatischer Anpassung von Referenzen.

Das Problem mit Matrixformeln ist, dass sie in der Web-Version von Excel nicht unterstützt werden. Laut MS:

In Excel für das Web können Sie Arrayformeln anzeigen, wenn die arbeitsmappe, die Sie öffnen, diese bereits enthält. Sie können in dieser Version von Excel jedoch keine Arrayformel erstellen […]

In meinem Test hat auch das bloße anzeigen nicht (wirklich) funktioniert. Ich konnte die mit LibreOffice Calc bearbeitete .xlsx Datei nur im Lesemodus öffnen, mit dem Hinweis, dass sie beschädigt sei. Reparieren hat die Matrixformeln durch die aktuellen Ergebnisse als fixe Werte ersetzt. Könnte sein, dass das Microsofts Interpretation von „Arrayformeln anzeigen“ ist, oder es liegt daran, dass die Matrixformeln mit Calc statt Excel eingefügt wurden…

Vielleicht noch das sahnehäubchen... Wie würdet ihr vorgehen um dieses Excel Blatt zu veröffentlichen - aber dass nur ich änderungen vornehmen kann.
Würde gerne einen QR Code dort einfügen unter dem man das aktuelle Spiel (Arbeitsblatt) aufrufen kann, damit tatsächlich jeder den aktuellen stand sehen kann.

Hätte irgendwie an Microsoft One Drive gedacht - das abrufen soll ohne Passwort stattfinden. Also nur eine reine Lese Funktion. Hab das mal mit einem Blattschutz versucht - aber grandios gescheitert...

Wenn du in OneDrive (Webinterface) oder Excel (Web-Version) auf das Teilen Icon klickst kommt ja so ein „Link senden“ Popup. Dort kannst du auf „Jeder mit dem Link kann bearbeiten“ klicken, um ein Untermenü mit mehr Einstellungen zu öffnen. In diesem kannst du dann festlegen, ob der Link zum bearbeiten oder nur lesen freigegeben ist:

Wie das in den Desktop Anwendungen für Excel und OneDrive aussieht kann ich dir gerade mangels Windows und Office Abo / Lizenz nicht sagen.

Aber wie gesagt hast du dann immer noch das Problem, dass die Web-Version von Excel keine Matrixformeln unterstützt. Wenn du zum Bearbeiten die Desktop-Version verwendest und den Link nur lesend teilst ist das vielleicht kein Problem für dich? Ansonsten kannst du ja mal Alternativen wie Google Docs oder Collabora Online probieren.

 

[RedAdair]

@YCbCr - unglaublich - tausend dank dass du dich meinen Problemen so annimmst. Werde ich gleich mal testen. Die anzeige mit dem (noch offenen) zahlen ist nicht so dramatisch.

Werde eine Rückmeldung geben. Dir auf jeden fall noch einen schönen Sonntag Abend!

 

[YCbCr]

Ich hab das in der Zwischenzeit nochmal in ein paar online Spreadsheet Tools ausprobiert:

CyrptPad / ONLYOFFICE unterstützt zwar Matrixformeln, aber anscheinend nur eingeschränkt. Für die oben beschriebene bekomme ich nur #WERT! angezeigt. Außerdem muss man für Bedingte Formatierung englische Syntax verwenden, auch wenn der Rest des Dokuments deutsche verwendet.

Google Docs Tabellen unterstützt Matrixformeln, benutzt aber eine andere Syntax: Statt dass man die Formel extra als Matrixformel „Markiert“ ist ARRAYFORMULA explizit Teil der Formel:
=ARRAYFORMULA(10-ZÄHLENWENN(ZÄHLENWENN(lottozahlen;E6:N6);">0"))
das kann man dann auch nur auf den tatsächlichen Array-Teil beschränken:
=10-ZÄHLENWENN(ARRAYFORMULA(ZÄHLENWENN(lottozahlen;E6:N6));">0")

Allerdings sagt Google:

Beachten Sie, dass Array-Formeln nicht exportiert werden können.

Was sich aber als falsch herausstellt? Sowohl Export als Office Open XML Workbook (.xlsx) als auch OpenDocument Spreadsheet (.ods) haben Problemlos inklusive Array-Formeln funktioniert…

Ansonsten unterstützt Google wohl keine Benannten Bereiche in Formeln für bedingte Formatierung. INDIREKT dagegen schon.
Du müsstest also die Formel von
=ZÄHLENWENN(lottozahlen;E6)
zu
=ZÄHLENWENN(INDIREKT("lottozahlen");E6)
ändern.

Wenns eh nur um lesen geht würde ich ja fast dazu tendieren, die Tabelle einfach als PDF und/oder Website zu exportieren.

Dir auf jeden fall noch einen schönen Sonntag Abend!

Danke! Dir auch.