-->Halli Hallo,

mich beschäftigt mal wieder ein Excelproblem.

Kann ich"enthält" abfragen und in eine Formel stopfen?

Also zum Beispiel ich habe:

Auto Rot
Auto Grün
Auto Blau
großes Auto
Motorrad Rot
kleines Fahrrad
großes blaues Motorrad

Mal angenommen da stehen überall Zahlen dabei (Verbrauch pro Liter zum Beispiel).

Ich will nun Abfragen:

Summiere den Verbrauch pro Liter bei allen Autos

Mit SummeWenn komme ich nur bedingt weiter, weil ich nicht abfragen kann, ob eine Zelle neben anderen einen Wert enthält, sondern weil ich genau vorgeben muss, wie der Text heißt. Wenn ich also"Auto" abfrage, kommt gar nichts, wenn ich"Auto Rot" abfrage kommen nur die roten. Wenn ich nun 100 Farben habe, dann ist es auch zu kompliziert alles zu tippen.

Lösungsvorschläge?

Wahrscheinlich mal wieder ganz einfach - gell? Nur ich bin zu blöd.


Toby

-->Ahoi Toby,

für"enthält" kannst du die Textfunktion"Finden" verwenden. Allerdings nicht direkt für die SummeWenn-Funktion, du musst also mit einem Hilfsfeld arbeiten.

Wenn in Feld X99 dein Suchbegriff"Auto" stehen soll, und in A1 steht"Auto Rot", dann schreibst du in B1:"=(finden(a1;$x$99)>0)"

Dann beziehst du die SummeWenn-Funktion nicht mehr auf die Spalte A, wo die Werte direkt drin stehen, sondern auf die Spalte B, wo immer nur"wahr" oder"falsch" drinsteht, und als Vergleichswert nimmst du einfach"wahr".

HTH/NBK

-->Hallo, @Toby,

Deine Aufgabe läßt sich auf verschiedenen Wegen in EXCEL lösen, und @NaturalBornKieler hat Dir einen Weg gezeigt:

<ul> ~ Einsatz der FINDEN(Suchtext; Bereich; )-Funktion wenn Groß-/Kleinschreibung bachtet werden soll, SUCHEN(Suchtext; Bereich; )-Funktion wenn Groß-/Kleinschreibung nicht von Bedeutung ist.
~ Einsatz des Autofilters, eine Funktionpunkt, der unter Daten::Filter...::Autofilter aufrufen wird (flexibelste Möglichkeit, da hiermit z.B. auch die Summe der 10 Autos mit dem geringsten Verbrauch ermittelt werden kann).
~ VBA-Programm</ul>

Da ich Deinen Datensatz nur nach Deinem Beispiel einschätzen kann, nehme ich den Gedanken von @NaturalBornKieler auf, der so gestaltet werden kann, dass doch keine Hilfspalte erforderlich wird:

Tabelle1<br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt;"> <colgroup><col style="font-weight:bold; width:30px;" /><col style="width:149px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt;"><td > </td><td >A</td><td >B</td><td >C</td><td >D</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center;" >1</td><td >Benennung</td><td >Verbrauch</td><td > </td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center;" >2</td><td >Auto Rot</td><td style="text-align:right;">8</td><td style="text-align:right;">Auto</td><td style="text-align:right;">41,5</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center;" >3</td><td >Auto Grün</td><td style="text-align:right;">7,5</td><td style="text-align:right;">rad</td><td style="text-align:right;">12,5</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center;" >4</td><td >Auto Blau</td><td style="text-align:right;">9</td><td style="text-align:right;">rot</td><td style="text-align:right;">12</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center;" >5</td><td >großes Auto</td><td style="text-align:right;">17</td><td > </td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center;" >6</td><td >Motorrad Rot</td><td style="text-align:right;">4</td><td > </td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center;" >7</td><td >kleines Fahrrad</td><td style="text-align:right;">3,5</td><td > </td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center;" >8</td><td >großes blaues Motorrad</td><td style="text-align:right;">5</td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000;"><tr><td >Formeln der Tabelle</td></tr><tr><td ><table border ="1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Zelle</td><td >Formel</td></tr><tr><td >D2</td><td >{=SUMMENPRODUKT(WENN<span style=' color:008000; '>(ISTZAHL<span style=' color:#0000ff; '>(FINDEN<span style=' color:#ff0000; '>(C$2;A$2:A$8)</span>)</span>;1;0)</span>;B$2:B$8)}</td></tr><tr><td >D3</td><td >{=SUMMENPRODUKT(WENN<span style=' color:008000; '>(ISTZAHL<span style=' color:#0000ff; '>(FINDEN<span style=' color:#ff0000; '>(C3;A$2:A$8)</span>)</span>;1;0)</span>;B$2:B$8)}</td></tr><tr><td >D4</td><td >{=SUMMENPRODUKT(WENN<span style=' color:008000; '>(ISTZAHL<span style=' color:#0000ff; '>(SUCHEN<span style=' color:#ff0000; '>(C4;A$2:A$8)</span>)</span>;1;0)</span>;B$2:B$8)}</td></tr></table></td></tr><tr><td ><span style=' font-family:Arial,Arial; font-size:9pt; font-weight:bold;'>Enthält Matrixformel:</span><span style=' font-family:Arial,Arial; font-size:9pt;'><br />Umrandende </span><span style=' font-family:Arial,Arial; font-size:9pt; font-weight:bold;'>{ }</span><span style=' font-family:Arial,Arial; font-size:9pt;'> nicht miteingeben,<br />sondern Formel mit STRG+SHIFT+RETURN abschließen!</span></td></tr><tr><td >Matrix verstehen</td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000;">Excel Tabellen im Web darstellen >> </span><a style ="font-family:Arial; font-size:9pt; color:#fcf507; background-color:#800040; font-weight:bold;" href="http://www.excel-jeanie-html.de" target="_blank"> Excel Jeanie HTML 4 </a>

Hier wurden nur aus Vorstellungsgründen die Funktionen FINDEN(...) und SUCHEN(....) eingesetzt. Es ist natürlich auch Möglich, nur mit C2 und D2 zu arbeiten, wobei man in C2 den Suchtext einträgt. Auch wären Festtexte als Eingabe in die Formel der Zelle D2 möglich.

Besonders ist hier der Hinweis zu beachten, wie Formeln abzuschliesen sind, in denen die Elemente als Matrix zusammengefügt werden. Bei dieser Lösung wird die Funktionalität genutzt, dass die FINDEN/SUCHEN-Funktionen die Startpostion eines Suchstextes in der Zeichenkette zurückgibt, wenn der Suchtext in dieser enthalten ist. Diese Aussage wird mit einer Eins bewertet. Wird der Textteil nicht gefunden, liefern die Funktionen eine Fehlerwert, #WERT, der in eine Null umgewandelt wird. So werden die Elemente eines Vektors aus Einsen und Nullen aufgebaut, der mit dem Vektor der Spalte B zum Summenprodukt, SUM(a[i]*b[i]), vebunden wird.

Sofern Du weitere Angaben zur Umsetzung der Formeln benötigst oder aber auch zu den anderen Punkten die Lösungswege beschrieben haben möchtest, dann wäre es vorteilhaft, eine Beispielarbeitsmappe anzulegen, die Deinem gewünschtem Aufbau nahe kommt.

Gruß,
Uwe

-->Hallo Uwe und NBK,

danke für die Hilfe.
Bin erst ab Montag wieder im Büro und werde dann versuchen das umzusetzen.
In der Praxis ist es so - wenn wir bei dem Autobeispiel bleiben wollen - daß ich eine Mappe mit mehreren tausend Fahrzeugen habe, die verschiedenartig kategorisiert sind und ich eben nur einen Begriff aus der Kategorie brauchen kann - also eben von Auuto rot und Auto grün kann ich nur Auto brauchen, weil mich rot und grün nicht interessieren.
Ein weiteres Problem ist nun, daß eine Mappe zum Beispiel die Fahrleistung in einem Monat darstellt und ich habe 60 solcher Mappen.
Wenn nun alles gleich wäre, wäre es kein großes Problem. Nun kommt es aber zu Veränderungen in der Fahrzeugbranche - das heißt ich kann keine fixe Mappe erstellen, sonder muss auf jeden Fall mit der Hand eingreifen. Will aber den Arbeitsaufwand gering halten.

Zum Beispiel gab es vor 3 Jahren noch gar kein Quad und jetzt muss ich eine extra Kategorie Quad einführen oder die Kategorie Pferdekutsche gab es damals aber heute nicht mehr. Außerdem habe ich heute 20000 einzelne Objekt, während ich vor einiger Zeit nur 10000 hatte - ich kann also keine Fixe Mappe machen in die ich dann die Daten einfüttere, sondern ich muss mir einen"Auslesemechanismus" überlegen, den ich dann eben auf die verschiedenen Mappen anwenden kann.


Toby

-->Hallo, @Toby,

es scheint so, als wenn Du dich da n einem etwas größeren Projekt herangewagt hast. Wenn es sich dabei um eine datenbankähnliche Struktur handelt, dann sei von mir hier der Rat vorgebracht, ruhig etwas mehr an Arbeits- und Gedankenzeit in die Grundlagenermittlung und den Aufbau der Tabellen (Gestaltung) zu stecken, als diese Punkte als"wird sich schon finden" auf später zu verschieben. Zumeist

Insbesondere bei dynamischen Strukturen, die sowohl in der Entwicklung eines Tabellenblatts selber als auch in der Anzahl und Folge der Tabellenblätter einer Arbeitsmappe zum ausdruck kommen können, ist es wichtig, Formeln so zu ersellen, dass sie diesen Umstand rechnung tragen könne. Sobald eine wechselnde Antahl von Tabellenblättern in der Anwendung zu erwarten sind, lohnt es sich darüber nachzudenken, das Projekt gleich in VBA zu erstellen.

Wenn dann noch zudem unterschieliche Ausfüllungsstände der beteiligten Zellen zu beachten (siehe als gerade angewndetet Beispiel die Besonderheit der FINDEN/SUCHEN-Zellenfunktion) sind, dann sind m.E. VBA-Prozeduren leichter zu überschauen und zu pflegen, als Zellenformeln, die zudem den Dateiumfang einer Arbeitsmappe sehr schnell vergrößern können.

Darüber hinaus sollte man am Anfang der Planung sich überlegen, ob mit jedem Ã-ffnen der Arbeitsmappe sämtliche"Datenblätter" mit eingerichtet werden müssen, oder ob es nicht vorteilhafter ist, diese Werte bei Bedarf aus einer Datendatei zu lesen, was allerdings VBA erfordert.

In einem weiteren Hinweis möchte ich Deine Aufmerksamkeit auf"Pivot-Tabellen" lenken, die gerade dazu gedacht sind, Datenbestände in aktuell interessierender Gliederung darzustellen.

Es wird wohl deutlich, dass mit dem Erstellen einer Formel zumeist erst die Arbeit beginnt, wenn man sich nicht zuvor über den gesamten Zweck und die Umgebung, in der man diese Nutzen möchte im Klaren ist. Wie komme ich an das Datenblatt für alle Objekte, die"Auto" und"rot" sind, um daraus die Grunddaten zu laden? Können Bezüge (Tabelle3!$B$4 ist z.B. so ein Bezu auf die Zelle B3 in der Tabelle mit dem Namen"Tabelle3") in einer Tabelle auf noch nicht existierende Blätter erfolgen? Wie verhält sich eine Funktion, wenn ein Rückgabewert einen Fehler meldet?

Es wäre schade um die Zeit, wenn es sich herausstellen sollte, dass eine mühsam"gebastellte" Formel zu einem Entwicklungszeitpunkt nicht mehr weiter genutzt werden kann, da die erforderlichen Optionen nicht einfügbar sind.

Dir viel Erfolg und als Hilfeforum in Sachen EXCEL hier eine URL: http://xlforum.herber.de/ (wenn Wiederholung, möge man es mir nicht vorwerfen).

Gruß,
Uwe