Office: Wenn / Dann

Hallo,
keine Ahnung ob das Excel überhaupt kann. Sollte Excel gar nicht in der Lage sein die Werte von - bis zu erkennen, dann habe ich Pech gehabt.
Ich möchte für unseren Gartenverein eine Liste (Vorplanung) erstellen, woraus man erkennen kann wieviel Teilnehmer es währen eines Arbeitseinsatz gibt. Es gibt eine Einteilung "von - bis" Gartennummern.
Fällt eine Gartennummer in diesen Bereich soll eine 1 geschrieben werden, ansonsten 0. Ist der Pächter im "Vorstand" oder andere Aufgaben, soll ebenfalls 0 erscheinen.
Habe in der Tabelle mal per Hand rein geschrieben wie ich es mir ungefähr vorstelle.

 

Benötigst Du denn eine Abfrage, ob die (feste) Gartennummer in Spalte A im Bereich der Spalte liegt?
Sind die Bereiche immer die gleichen, lässt Du die Formel einfach bei nicht passenden Nummern raus.
Formel in F4 =WENN(ANZAHL2($B4:$D4)=0;1;"X") kopieren in F4 bis F22, G14 bis G48, F49 bis F61 usw. (Siehe Tabelle1)

Verändern sich die Bereiche würde ich vorschlagen eine Spalte hinzuzufügen und jedem Garten die Spaltennummer zuweisen in die er gehört.
Formel in F4 =WENN($B4=SPALTE();WENN(ANZAHL2($C4:$E4)=0;1;"X");0) kopieren in F4 bis L130. (Siehe Tabelle2)

 

Hallo, erstmal recht herzlichen Dank für die Unterstützung. Nur das Problem ist, ich möchte in Zukunft die Einteilung der Gärten ändern. So wie es jetzt ist, sind in dem Bereich 1-19, 46-58, 23 Pächter verfügbar. In dem Bereich von 20-45 nur 15. Das kommt daher, das in diesen Bereich sehr viel ältere (über 75 Jahre) sind. Und diese Planung habe ich übernommen. Ich möchte in der Vorplanung schon ein ungefähres Gleichgewicht erhalten. Und da dachte ich, das wenn man man z.B. ober rein schreibt 1-11, 46-58 da komme ich auf 18 und die übrigen dann in eine andere Gruppe z.B. dann 12-45. Wenn es nicht klappen sollte, auch nicht schlimm.

 

Hallo,

ich habe dir in der beiliegenden Excel-Datei in einem allgemeinen Codemodul die benutzerdefinierte Funktion
IstInVonBisListe(Nr As Integer, Liste As String, Optional Tr$ = "") As Boolean
eingefügt.
Sie besitzt als 1.Parameter Nr die Nummer aus Spalte A, als 2. Parameter Liste deine Von-Bis-Liste aus Zelle F3 und als 3. (optionalen) Parameter das Zeilenumbruchzeichen aus Zelle F3, sodass der 1.Teil (Gartennummern) aus der VonBis-Liste entfernt wird.
Dann lautet die erste Formel in Zelle F4: =WENN(LÄNGE($B4&$C4&$D4);0; WENN(IstInVonBisListe($A4;F$3); 1;0))
Nachfolgend die ben.def.Funktion:

Code:

Option Explicit

'Liste: "[Anfangstext Tr] Von1[-Bis1], Von2[-Bis2], ..."

Public Function IstInVonBisListe(Nr As Integer, Liste As String, Optional Tr$ = "") As Boolean
  Dim VonBis As Variant, VB() As String
 
  IstInVonBisListe = False
 
  If Len(Tr$) = 0 Then Tr$ = Chr(10)
  Liste = Mid$(Liste, InStr(Liste, Tr$) + 1)
 
  For Each VonBis In Split(Liste, ",")
    VB = Split(VonBis, "-")
    Select Case UBound(VB)
      Case 0
         If VB(0) = Nr Then
            IstInVonBisListe = True
            Exit For
         End If
      Case 1
         If VB(0) <= Nr And Nr <= VB(1) Then
            IstInVonBisListe = True
            Exit For
         End If
    End Select
  Next VonBis

End Function

 

Das ist ja ganz Großes Kino. Hätte nie gedacht das es funktioniert.
Vielen lieben Dank Euch beiden.

 

Hallo,

im Posting #4 habe ich dir eine Lösung mit ben.def. VBA-Funktion übermittelt. Man kann dieses Problem auch ohne VBA lösen nur mit Hilfe von Arbeitsblattfunktionen.

Für diesen Zweck habe ich im Arbeitsblatt "Tabelle ohne VBA" eine zusätzliche Zeile 3 eingefügt. Diese Zeile kannst du ausblenden, damit man sie in deinem Arbeitsblatt nicht sieht.
Sie enthält Formeln, die die Zeichenketten der Zellen F4:K4 in XML-Zeichenketten (Zellen F3:K4) umwandeln.
Z.B. Formel in F3: ="<x><i>"&WECHSELN(WECHSELN(WECHSELN(WENN( WENNFEHLER( FINDEN(ZEICHEN(10); F$4);0); "";ZEICHEN(10)) & F$4; ZEICHEN(10);"</i><i><v>"); ",";"</v></i><i><v>");" - ";"</v><v>")&"</v></i></x>"

Dadurch wird aus der Zeichenkette in F4:
'Gartennummern
1 - 19, 46 - 58
folgende XML-Zeichenkette in F3:
<x><i>Gartennummern</i><i><v>1</v><v>19</v></i><i><v> 46</v><v>58</v></i></x>

Auf diese umgewandelte Zeichenkette wird mittels XPath-Abfragen in den Formeln ab Zeile 5 zugegriffen.
z.B. Formel in F5: =WENN(LÄNGE($B5&$C5&$D5);0; SUMMENPRODUKT( (XMLFILTERN(F$3; "//i/v[1]")<=$A5) *($A5<=XMLFILTERN(F$3; "//i/v[last()]"))))
um deine gewünschten Resultate zu erzielen.

Die Umwandlungsformel in F3 könnte wesentlich einfacher sein, wenn du auf die (eigentlich unnötigen) Zeilenumbruchstexte in Zelle F4 (und folgende) verzichten würdest - das kann ja auch in 2 getrennten Zeilen (bzw. Zellen) eingegeben werden, sodass z.B. in F4 nur mehr die Von-Bis-Intervalle stehen, z.B:
1 - 19, 25, 46 - 58
was mit der wesentlich einfacheren Umwandlungsformel in F3:
="<x><i>"&WECHSELN(WECHSELN(F4;",";"</v></i><i><v>");" - ";"</v><v>")&"</v></i></x>"
folgenden XML-String in F3 ergibt:
<x><i>1</v><v>19</v></i><i><v> 25</v></i><i><v> 46</v><v>58</v></i></x>
also ohne der anfänglichen "<i>Gartennummern</i>".

Datei liegt bei.

 

Hallo, das ist ja der Hammer. Solche Formeln habe ich vorher noch nie gesehen. Wieviel Zeit wurde denn in diese Arbeit gesteckt? In der Tabelle "Formeln" schaue ich rein und versteh nur Bahnhof. Ich getraue mir gar nicht noch eine Frage zu stellen. Ist es möglich, die Einträge von B4 - F4 (Projekt Namen wechseln von Jahr zu Jahr) nur dann in der zutreffenden Spalte einzutragen in dem die Gartennummer vorhanden ist. Bei meinem Versuch wird es in alle Spalten mit übernommen. Wäre auch nicht weiter schlimm wenn es nicht geht.