Supportnet / Forum / Tabellenkalkulation
Zellen zählen, die bestimmten Wert an irgendeiner Stelle enthalten
Frage
Hallo,
ich möchte in Excel alle Zellen in einer Spalte auf einem separaten Arbeitsblatt zählen, die den Text "OEM" enthalten (laut Autofilter nicht "entspricht" sondern "enthält"). Das Problem: der Text kann nicht nur ganz vorne, oder ganz hinten stehen (hier gibt es ja die Formeln RECHTS() und LINKS()), sondern auch irgendwo innerhalb des Zelltextes an einer völlig freien Stelle. Da hilft dann die Formel MID() auch nicht weiter.
Habt ihr irgendeinen Tipp? Die Formel muß so ähnlich lauten wie:
ZÄHLENWENN(´Tabelle2´!$A$1:$A$4698;XXXXXX(´OEM´)),
wobei es für XXXXX doch irgendeinen Trick geben muß, wenn man die genaue Position nicht weiß.
Wahrscheinlich ist diese Frage für die Spezialisten hier ein Klacks, aber ich komme alleine einfach nicht weiter.
Viele Grüsse
magrix
Antwort 1 von Saarbauer
Hallo,
in einem Drchgang kann ich dir auf die Schnelle keine Lösung anbieten, aber mi einer Hilfsspalte
In der Hilfsspalte 2. Zeile, habe als Hilfspalte C genommen (hier C2)
=SUCHEN("OEM";A2)
und nach unten ziehen.
Dann
=ZÄHLENWENN(C:C;">0")
in die entsprechende zelle stzen, hie ist C:C durch deine Hilfsspalte zu ersetzen
Gruß
Helmut
in einem Drchgang kann ich dir auf die Schnelle keine Lösung anbieten, aber mi einer Hilfsspalte
In der Hilfsspalte 2. Zeile, habe als Hilfspalte C genommen (hier C2)
=SUCHEN("OEM";A2)
und nach unten ziehen.
Dann
=ZÄHLENWENN(C:C;">0")
in die entsprechende zelle stzen, hie ist C:C durch deine Hilfsspalte zu ersetzen
Gruß
Helmut
Antwort 2 von magrix
Hi Helmut,
danke schon mal für den ersten Tipp. Ich habe nur das Problem, daß ich leider nicht nur nach "OEM" suchen muß sondern noch nach 14 weiteren Buchstabenkombinationen. Daß daraus 14 Formeln werden, ist mir klar, aber 14 Hilfsspalten??? Das ist doch ein wenig viel.
Die Tabelle hat jetzt schon fast 2 MB (bei 4500 Datensätzen) und deswegen möchte ich Hilfsspalten eigentlich vermeiden.
Trotzdem Danke für den Vorschlag - vielleicht hat ja jemand noch einen anderen Geistesblitz ?
magrix
danke schon mal für den ersten Tipp. Ich habe nur das Problem, daß ich leider nicht nur nach "OEM" suchen muß sondern noch nach 14 weiteren Buchstabenkombinationen. Daß daraus 14 Formeln werden, ist mir klar, aber 14 Hilfsspalten??? Das ist doch ein wenig viel.
Die Tabelle hat jetzt schon fast 2 MB (bei 4500 Datensätzen) und deswegen möchte ich Hilfsspalten eigentlich vermeiden.
Trotzdem Danke für den Vorschlag - vielleicht hat ja jemand noch einen anderen Geistesblitz ?
magrix
Antwort 3 von Saarbauer
Hallo,
dann würde ich in einer Suchtextzelle (hier $D$2) arbeiten
=SUCHEN($D$2;A2;1)
dann weiter wie AW 1
Dadurch werden alle zellen die diesen Text enthalten gezählt
Gruß
Helmut
dann würde ich in einer Suchtextzelle (hier $D$2) arbeiten
=SUCHEN($D$2;A2;1)
dann weiter wie AW 1
Dadurch werden alle zellen die diesen Text enthalten gezählt
Gruß
Helmut
Antwort 4 von magrix
Hallo Helmut,
ich traue es mich fast nicht zu sagen, aber der Wert, den ich ermittle, ist leider nicht statisch, sondern ändert sich während des Arbeitstages laufend.
Vielleicht hilft eine etwas genauere Beschreibung meiner Tabelle:
ich habe eine Arbeitsliste, in der pro Zeile ein Lieferanten steht. Zu manchen Lieferanten müssen für verschiedene Abteilungen Klärungen durchgeführt werden, die ich in EINER Spalte mit dem jeweiligen Abteilungskürzel hinterlegt habe - bei manchen Lieferanten ist das nur Abteilung, bei anderen sind in dieser Spalte zwei, drei oder noch mehr Kürzel (insgesamt gibt max 14 verschiedene)
In einer anderen Spalte erfasse ich den jeweiligen Status dieser Klärungen ("open", "ongoing" oder "finished") der sich nach aktuell einlaufenden eMail minütlich ändern kann.
Die Aufgabe ist es jetzt, für alle Bereiche (die sich hinter den zu suchenden Buchstabenkombinationen verbergen) jeweils die offenen, laufenden oder geschlossenen Vorgänge mit Zahlen darzustellen, also:
- Lieferanten, die in der Klärungsspalte "Abt. 1" stehen haben: insgesamt x, davon open: x, ongoing: x, finished: x
- Lieferanten, die in der Klärungsspalte "Abt. 2" stehen haben: usw.
Und diese Zahlen sollen ständig aktuell sein.
Wenn das Abteilungszeichen immer an derselben Stelle stehen würde, könnte ich mit RECHTS() oder LINKS() bei der Zellenauswertung arbeiten, aber da es manchmal eben nur eine und manchmal ganz viele Abteilungen gibt, muß ich die SUCHEN-Funktion irgendwie über die gesamte Spalte jagen können und nicht nur auf einzelne Zeilen.
Ist echt schwierig und wenn nichts anderes hilft, muß ich wohl oder übel 14 Abteilungsspalten einführen, in denen ich dann ein x für "Klärung notwendig" in jeder Lieferanten/Abteilungs-Kreuzung setze, aber da die Tabelle über 4500 Datensätze hat, das ist echt der allerletzte Ausweg
Vielleicht gibt es ja doch noch eine andere Lösung ???
Danke
magrix
ich traue es mich fast nicht zu sagen, aber der Wert, den ich ermittle, ist leider nicht statisch, sondern ändert sich während des Arbeitstages laufend.
Vielleicht hilft eine etwas genauere Beschreibung meiner Tabelle:
ich habe eine Arbeitsliste, in der pro Zeile ein Lieferanten steht. Zu manchen Lieferanten müssen für verschiedene Abteilungen Klärungen durchgeführt werden, die ich in EINER Spalte mit dem jeweiligen Abteilungskürzel hinterlegt habe - bei manchen Lieferanten ist das nur Abteilung, bei anderen sind in dieser Spalte zwei, drei oder noch mehr Kürzel (insgesamt gibt max 14 verschiedene)
In einer anderen Spalte erfasse ich den jeweiligen Status dieser Klärungen ("open", "ongoing" oder "finished") der sich nach aktuell einlaufenden eMail minütlich ändern kann.
Die Aufgabe ist es jetzt, für alle Bereiche (die sich hinter den zu suchenden Buchstabenkombinationen verbergen) jeweils die offenen, laufenden oder geschlossenen Vorgänge mit Zahlen darzustellen, also:
- Lieferanten, die in der Klärungsspalte "Abt. 1" stehen haben: insgesamt x, davon open: x, ongoing: x, finished: x
- Lieferanten, die in der Klärungsspalte "Abt. 2" stehen haben: usw.
Und diese Zahlen sollen ständig aktuell sein.
Wenn das Abteilungszeichen immer an derselben Stelle stehen würde, könnte ich mit RECHTS() oder LINKS() bei der Zellenauswertung arbeiten, aber da es manchmal eben nur eine und manchmal ganz viele Abteilungen gibt, muß ich die SUCHEN-Funktion irgendwie über die gesamte Spalte jagen können und nicht nur auf einzelne Zeilen.
Ist echt schwierig und wenn nichts anderes hilft, muß ich wohl oder übel 14 Abteilungsspalten einführen, in denen ich dann ein x für "Klärung notwendig" in jeder Lieferanten/Abteilungs-Kreuzung setze, aber da die Tabelle über 4500 Datensätze hat, das ist echt der allerletzte Ausweg
Vielleicht gibt es ja doch noch eine andere Lösung ???
Danke
magrix
Antwort 5 von nighty
hi ihr :))
versteh nur wenig von formeln daher ein versuch,wobei es bestimmt auch mit excel eigenen formeln geht :))
gruss nighty
beispiel
ZählenBereich("A1:A5";"DeinSuchBegriff")
Function ZählenBereich(bereich As Range, wort As String) As Double
Application.Volatile
Dim zelle As Range
For Each zelle In bereich
If zelle Like "*" & wort & "*" Then
ZählenBereich = ZählenBereich + 1
End If
Next
End Function
versteh nur wenig von formeln daher ein versuch,wobei es bestimmt auch mit excel eigenen formeln geht :))
gruss nighty
beispiel
ZählenBereich("A1:A5";"DeinSuchBegriff")
Function ZählenBereich(bereich As Range, wort As String) As Double
Application.Volatile
Dim zelle As Range
For Each zelle In bereich
If zelle Like "*" & wort & "*" Then
ZählenBereich = ZählenBereich + 1
End If
Next
End Function
Antwort 6 von nighty
hi all :))
oder so :))
gruss nighty
=ZÄHLENWENN(A1:A3;"*" & "a" & "*")
oder so :))
gruss nighty
=ZÄHLENWENN(A1:A3;"*" & "a" & "*")
Antwort 7 von Event
@nigthy..;-)=
Du meinst nicht etwa:
=ZÄHLENWENN(D1:D7;"*" & "oem" & "*")
Gruß
Du meinst nicht etwa:
=ZÄHLENWENN(D1:D7;"*" & "oem" & "*")
Gruß
Antwort 8 von rainberg
Hallo,
ich würde es so lösen
=ZÄHLENWENN(A:A;"*oem*")
Gruß
Rainer
ich würde es so lösen
=ZÄHLENWENN(A:A;"*oem*")
Gruß
Rainer
Antwort 9 von nighty
hi all :)
hui dreimal das selbe :)))
gruss nighty
hui dreimal das selbe :)))
gruss nighty
Antwort 10 von magrix
Danke, Danke, Danke
Auf die Idee mit dem Stern wäre ich nie gekommen, war schon zu versunken in all den Formeln.
Tolles Forum, nette Leute
Ich werde euch weiterempfehlen
Liebe Grüsse
Marion
Auf die Idee mit dem Stern wäre ich nie gekommen, war schon zu versunken in all den Formeln.
Tolles Forum, nette Leute
Ich werde euch weiterempfehlen
Liebe Grüsse
Marion