Anzahlleerezellen an Bedingung knüpfen
Dan
Ich ermittle die Anzahl der leeren Zellen in einer Spalte mit
=anzahlleerezellen, soweit so gut.
Gibt es eine Möglichkeit, dies an bestimmte Werte in einer anderen
Spalte zu knüpfen?
Beispiel:
Name Tier gefüttert
Bobo Affe x
Dori Affe x
Lala Affe
Henry Elch x
Lars Elch
Eugen Elch x
Insgesamt käme ich mit Anzahlleerezellen in Spalte "gefüttert" auf 2.
Ich möchte jetzt aber z. B. nur wissen wieviele (und was noch schöner
wäre: welche) Affen noch nicht gefüttert wurden.
1000 Dank für Tipps!
Dani
PS.: Bei der Erstellung dieser Tabelle sind keine Lebewesen zu Schaden
gekommen :)
Claus Busch
Am Fri, 1 Oct 2010 03:29:19 -0700 (PDT) schrieb Dan:
> Name Tier gefüttert
> Bobo Affe x
> Dori Affe x
> Lala Affe
> Henry Elch x
> Lars Elch
> Eugen Elch x
>
> Insgesamt käme ich mit Anzahlleerezellen in Spalte "gefüttert" auf 2.
> Ich möchte jetzt aber z. B. nur wissen wieviele (und was noch schöner
> wäre: welche) Affen noch nicht gefüttert wurden.
warum filterst du "gefüttert" nicht nach Leere?
Oder markiere A1:C Ende, gehe auf bedingte Formatierung und gebe dort
die Formel ein:
=$C1="" und weise eine Hintergrundfarbe zu.
Mit freundlichen Grüssen
Claus Busch
--
Win XP Prof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
Claus Busch
wer lesen kann, ist echt im Vorteil ;-)
Probiers mal so:
=SUMMENPRODUKT(-(B1:B100="Affe");-(C1:C100=""))
oder wenn du xl2007 aufwärts benutzt:
=ZÄHLENWENNS(B:B;"Affe";C:C;"<>"&"x")
Dan
vielen Dank schonmal für die schnellen Antworten!
> wer lesen kann, ist echt im Vorteil ;-)
Würde mich gerne mehr einlesen - hast mal ne Quelle. Hab mit Exce
leider bisher nicht viel am Hut gehabt :(
> Probiers mal so:
> =SUMMENPRODUKT(-(B1:B100="Affe");-(C1:C100=""))
Super! Kannst du mir noch verraten, wie ich da mehrere Argumente
reinbekomme ... also z. B. Affen, Elche, Fische aber nicht Kühe ;)
Und nochmal zu meiner erweiterten Frage: Krieg ich mit derartigen
Excel-Boardmitteln auch raus, welche Tiere noch nicht gefüttert sind?
Hab das jet mal mit VBA gelöst, aber Excel-Formeln find ich eigentlich
eleganter...
Dank und Gruß!
Claus Busch
Am Fri, 1 Oct 2010 05:39:43 -0700 (PDT) schrieb Dan:
> Super! Kannst du mir noch verraten, wie ich da mehrere Argumente
> reinbekomme ... also z. B. Affen, Elche, Fische aber nicht Kühe ;)
am einfachsten immer noch über das Filtern. Filtere "gefüttert" nach
Leere und dann "Tier" nach den gewünschten Arten. Damit bekommst du
Namen und Art. Und das ist doch das, was du erreichen wolltest.
Bei den Formeln kommt es auf deine Excel-Version an. xl2007 aufwärts
kannst du bei Zählenwenns mehrere Kriterien und Kriterienbereich
eingeben.
Wenn du nur Kühe ausschließen möchtest, geht auch:
=SUMMENPRODUKT(--(B1:B10<>"Kuh");--(C1:C10=""))
oder
=ZÄHLENWENNS(B1:B10;"<>"&"Kuh";C1:C10;"")
Claus Busch
noch ein Nachtrag:
Wenn du gefiltert hast, hast du Namen und Tierart. Um dann die Anzahl zu
sehen, probiere es mit:
=TEILERGEBNIS(3;B2:B100)
Du kannst auch mit dem Spezialfilter die Tierart (inklusive Überschrift)
ohne Duplikate z.B. nach H1 filtern. Dann in I2:
=SUMMENPRODUKT(--($B$1:$B$100=H2);--($C$1:$C$100=""))
und entlang den Tierarten nach unten ziehen.
Eberhard Funke
>
> Und nochmal zu meiner erweiterten Frage: Krieg ich mit derartigen
> Excel-Boardmitteln auch raus, welche Tiere noch nicht gefüttert sind?
> Hab das jet mal mit VBA gelöst, aber Excel-Formeln find ich eigentlich
> eleganter...
Hallo Dan,
Dein letzter Satz lässt mich zögern...
Im Prinzip so:
Deine Tabelle steht mit "Name" in A1
B1: Affe
B2:
=INDEX(A$2:A$10;KGRÖSSTE((B$2:B$10=$D$1)*(C$2:C$10="")*ZEILE($2:$10);ZEILE()-1)-1)
Das ist eine Matrixformel, die mit der Tastenkombination Shift+Strg+Enter
abgeschlossen werden muss (fügt ein Paar geschweifter Klammern um die
Formel, die nicht von Hand eingegeben werden können)
Diese Formel runterziehen bis #WERT! erscheint.
Letzteres kann man mit =istfehler(Ausdruck;"";Ausdruck) auch noch abfangen.
In C2 usw. kannst Du Deine weiteren Tiere eintragen und die Formeln
sinngemäß übertragen.
Wenn man sich Zeit nimmt, kann man das sicher auch noch eleganter
hinkriegen.
--
Mit freundlichen Grüssen Eberhard
XP home XL 2000
Dan
> am einfachsten immer noch über das Filtern. Filtere "gefüttert" nach
> Leere und dann "Tier" nach den gewünschten Arten. Damit bekommst du
> Namen und Art. Und das ist doch das, was du erreichen wolltest.
Filtern scheidet leider aus - ich möchte keine manuellen Aktionen an
der Tabelle vornehmen müssen - soll alles direkt ausgelesen werden....
> Bei den Formeln kommt es auf deine Excel-Version an. xl2007 aufwärts
> kannst du bei Zählenwenns mehrere Kriterien und Kriterienbereich
> eingeben.
Hab leider nur Office 2003 zur Verfügung :(
Dan
einfach durch Addition der Summenprodukte:
=SUMMENPRODUKT(-(B1:B100="Affe");-(C1:C100=""))+=SUMMENPRODUKT(-
(B1:B100="Elch");-(C1:C100=""))+...
Jetzt zum anderen:
Die 1. Tabelle würde ich gerne wie folgt fortschreiben:
Name Tier Montag gefüttert Dienstag gefüttert ....
Bobo Affe x
Dori Affe x x
Lala Affe x
Henry Elch x
Lars Elch x
Eugen Elch x
Daraus möchte ich per Knopfdruck (bisher sehr umständlich mit VBA
realisiert, durch einlesen der einzelnen Zellen in Arrays etc.)
folgende Tagesübersicht erstellen:
Montag
Gefütterte Affen:
Bobo
Dori
Gefütterte Elche:
Henry
Eugen
Ich hoffe, das macht mein Anliegen etwas verständlicher. Wie gesagt -
manuelles Filtern und Sortieren der Daten ist nicht gewünscht ;)
Gruß und einen guten Start ins WE!
Eberhard Funke
>
> Die 1. Tabelle würde ich gerne wie folgt fortschreiben:
>
> Name Tier Montag gefüttert Dienstag gefüttert ....
> Bobo Affe x
> Dori Affe x x
> Lala Affe x
> Henry Elch x
> Lars Elch x
> Eugen Elch x
>
> Daraus möchte ich per Knopfdruck (bisher sehr umständlich mit VBA
> realisiert, durch einlesen der einzelnen Zellen in Arrays etc.)
> folgende Tagesübersicht erstellen:
>
> Montag
> Gefütterte Affen:
> Bobo
> Dori
>
> Gefütterte Elche:
> Henry
> Eugen
Hallo Dan,
möglicherweise ist das (für einen Masochisten) mit Formeln zu machen.
Im Gegensatz zu bisher willst Du jetzt diejenigen Tiere angezeigt bekommen,
die *gefüttert* worden sind.
Die VBA-Lösung ist eigentlich nicht umständlich, siehe Makro "Gefüttert".
Ich bin jetzt aber für längere Zeit nicht da und kann keine Fragen
beantworten. Deshalb habe ich die Datei hier hochgeladen in der Hoffnung,
dass das alles selbsterklärich ist:
http://www.fileuploadx.de/273313
Ach ja, jetzt sehe ich das mit dem Knopfdruck. Da musst Du halt noch einen
Button aus der Symbolleiste "Formular" einfügen und ihn mit dem Makro
"Gefüttert" verküpfen
Eberhard Funke
> ...habe ich die Datei hier hochgeladen :
> http://www.fileuploadx.de/273313
>
> Ach ja, jetzt sehe ich das mit dem Knopfdruck. Da musst Du halt noch einen
> Button aus der Symbolleiste "Formular" einfügen und ihn mit dem Makro
> "Gefüttert" verküpfen
Natürlich müssen unten in der Tabelle die Namen bei erneuter Fütterung erst
gelöscht werden, bevor die neuen eingetragen werden können. Wenn z. B. am
letzen Montag 4 Affen gefüttert wurden, an diesem aber nur 2, dann stünden
da ohne vorhergehende Löschung 4 Affen als gefüttert.
Der folgende Code führt diese Löschung automatisch durch, bevor die Namen
der neuen Fütterung eingetragen werden.
Sub NewGefüttert()
Dim Tiere 'Array mit Aufzählung der Tierarten
Dim intTi As Integer 'Zählvariable
Dim intTag As Integer 'Spalte des gwählten Tages
Dim lngStart As Integer 'erste Zle der Namenseinträge (15,21,26)
Dim lngLR As Long 'letzte Zeile (11) der Fütterungstabelle
Dim rngTag As Range 'in diesem Beispiel B2:B11
Dim rngZelle As Range
Dim intIZ As Integer 'Anzahl der Affen bzw Elche usw.
Tiere = Array("Affe", "Elch", "Katze")
'letzte gefüllte Zeile in Spalte A:
lngLR = Range("A" & Rows.Count).End(xlUp).Row
'in welcher Spalte steht der (in Zeile 1) angeklickte Tag?
intTag = Application.InputBox("klicken Sie auf den zu " _
& "bearbeitenden Tag", Type:=8).Column
'Der Bereich, in dem die Fütterungen für diesen Tag
'mit einem x eingetragen werden:
Set rngTag = Range(Cells(2, intTag), Cells(lngLR, intTag))
For intTi = 0 To UBound(Tiere)
'Zeile, ab der die Namen unter Affe usw.
'eingetragen werden können, also in diesem
'Beispiel die Zeilen 15, 21 und 26
lngStart = Columns(intTag).Find(what:=Tiere(intTi)).Row + 1
'wieviele Individuen der Art Affe (Elch usw.) gibt es?
intIZ = CInt(WorksheetFunction. _
CountIf(Range("B2:B" & lngLR), Tiere(intTi)))
'Namen von der Fütterung vor einer Woche löschen
Range(Cells(lngStart, intTag), _
Cells(lngStart + intIZ, intTag)).ClearContents
'jetzt geht's eigentlich erst los:
For Each rngZelle In rngTag
'wenn der Affe (oder Elch usw.) gefüttert ist,
'dann Namen eintragen
If rngZelle = "x" And Range("B" & rngZelle.Row) = _
Tiere(intTi) Then
Cells(lngStart, intTag) = Range("A" & rngZelle.Row)
lngStart = lngStart + 1
End If
Next
Next
End Sub
Dan
Urlaub ;):
Vielen herzlichen Dank für deine Hilfe!
Gruß
D.