Unterscheidung n. Anzahl der Leerstellen in Ausdru

Computer: Software & Programmierung Office Software
1

Hallo,

kann Excel eigentlich aus einem im Standardformat oder auch sonst wie formatierten Ausdruck die Zahl der darin enthaltenen Leerstellen herauslesen? Wäre interessant, weil ich die Zelle dann danach klassifizieren könnte. Z.B. hieße „02/02/12 Intershop“, daß an diesem Tag ein Un Zahlen veröffentlicht, „02/02/19 Quiagen Volkswagen“ dagegen, daß es 2 sind und „02/03/05 Dt.Telekom FMC Henkel“, daß es 3 sind. Vor jeder Bezeichnung eines Un stehlt im Begriff also eine Leerstelle.

Das ganze ginge auch nach Anzahl der Kommata, dann würde ich die Begriffe halt anders aufbauen. In jedem Fall könnte ich mich in einer anderen Spalte darauf beziehen und pro Un einen Punkt (wird dann in einer andere Schrift, z.B. Symbol formatiert) anzeigen lassen, damit ich auf einen Blick sehe, wie viele Un es sind, ohne daß ich die Spalte irrsinnig breit werden lassen muß.

Gruß
Jörg

2

Lösung mit Makro-Funktion
Das ist eine mögliche Lösung:

Gehe in den VBA-Editor (Alt-F11), klicke mit rechts im Projekt-Explorer oben links irgendwo auf eine Zeile, die zur aktuellen Exxel-Datei gehört, wähle „Einfügen / Modul“ und kopiere den folgenden Code in das Modul.

Im Excel-Blatt könnte dann z.B. stehen: „=ZaehleVorkommen(E15;E16)“, wenn in E15 „Hallo Hallo“ und in E16 „ll“ steht.

Function ZaehleVorkommen(String\_ As String, Suchstring\_ As String) As Integer
Dim i As Integer
Dim p As Integer
 ZaehleVorkommen = 0
 i = -Len(Suchstring\_)
 p = 1
 Do While p \> 0
 i = p + Len(Suchstring\_)
 p = InStr(i, String\_, Suchstring\_) 'i ist beim ersten Mal 1
 If p \> 0 Then ZaehleVorkommen = ZaehleVorkommen + 1
 Loop
End Function 'ZaehleVorkommen
 
 
Sub test()
 MsgBox ZaehleVorkommen("02/02/19 Quiagen Volkswagen", " ") 'ergibt 2
 MsgBox ZaehleVorkommen("Hallo Hallo Halllo", "ll") 'ergibt 3
 MsgBox ZaehleVorkommen("Hallo Hallo Hallllo", "ll") 'ergibt 4
End Sub 'test
3

ich kapiert’s nicht
Hallo Kristian,

ich kapiert’s nicht. Eingefügt in den VBA-Editor hab ich das, so wie du es beschrieben hast. Aber wie geht es dann weiter? Was hat „Im Excel-Blatt könnte dann z.B. stehen: „=ZaehleVorkommen(E15;E16)“, wenn in E15 „Hallo Hallo“ und in E16 „ll“ steht.“ zu bedeuten? Muß ich jetzt in das Arbeitsblatt doch noch eine Formel hinschreiben? Ich dachte Makros sind gerade eine Alternative zu Formelanwendungen? Am besten schicke ich dir eine Musterdatei mal zu.

Gruß
Jörg

[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]

4

Hallo Kristian,

ich kapiert’s nicht. Eingefügt in den VBA-Editor hab ich das,
so wie du es beschrieben hast. Aber wie geht es dann weiter?
Was hat „Im Excel-Blatt könnte dann z.B. stehen:
„=ZaehleVorkommen(E15;E16)“, wenn in E15 „Hallo Hallo“ und in
E16 „ll“ steht.“ zu bedeuten? Muß ich jetzt in das
Arbeitsblatt doch noch eine Formel hinschreiben? Ich dachte
Makros sind gerade eine Alternative zu Formelanwendungen? Am
besten schicke ich dir eine Musterdatei mal zu.

Jörg,

Du definierst in dem VBA-Modul eine neue Funktion, die sogar im Formel-Assistenten erscheint (Klick auf das Gleichheitszeichen neben der Eingabeleiste, dann „Alle Funktionen“ oderso auswählen), und Du rufst sie im Excel-Blatt genauso auf, als wäre es die Funktion „Summe()“ oder eine beliebige andere.

Da die Funktion nun zwei Parameter hat, schreibt man als Formel in die Zelle „=ZaehleVorkommen(E15;E16)“, wenn der erste Paramter in E15 und der zweite in E16 steht. Feste Parameter kann man natürlich auch verwenden: „=ZaehleVorkommen(„Hallo Hallo“;" ")“, was hier 1 ergeben würde (ein Leerzeichen ist enthalten).

Alles klar nun?

Kristian

5

Endgültige Lösung mit kurzer Erklärung
Moin Jörg,

ich schicke Dir die Excel-Datei nochmal zu, beantworte die Mail aber hier im Forum, damit erstens auch andere was davon haben und diese mich zweitens ggf. korrigieren oder ergänzen können.

> Ein Wunder, was der Mann so alles kann. Wenn ich dich richtig
> verstanden hab, hast du mit Hilfe des Makros selber eine Formel
> herstellt, die dann allerdings - wie in solchen Fällen üblich -
> auch nur in der Tabelle, in der das Makro enthalten ist,
> funktioniert und nicht z.B. per Kopieren auf andere Tabellen
> übertragbar ist. Keine schlechte Erfindung.

Naja, fast richtig. In VBA (Visual Basic for Applications - ein Ableger von Visual Basic, der speziell für die jeweilige Office-Applikation zugeschnitten ist) gibt es Prozeduren und Funktionen (wie in fast jeder anderen Programmiersprache auch).

Die Prozeduren führen irgendwelche Aktionen, Berechnungen etc. aus und werden danach einfach beendet. Als Parameter kann man benötigte Werte übergeben. Funktionen sind bis auf einen Unterschied mit Prozeduren identisch. Der Unterschied besteht darin, daß sie genau einen Wert zurückliefern.

Alle Formeln, die Excel bereitstellt, sind Funktionen, deren zurückgegebener Wert dann in der Zelle angezeigt wird, in der sie steht. Beispiel: =Summe(A1:A10). Summe ist die Funktion, A1:A10 ist der Parameter (in diesem Falle nur einer), und das Ergebnis erscheint dann in der entsprechenden Zelle.

Jede Formel von Excel ließe sich mit VBA nachbilden. Beispiel ist meine Funktion (mein Makro) „Vervielfaeltige()“ (siehe Posting weiter unten), die dasselbe macht wie die Excel-Formel „Wiederholen()“. Ebenso lassen sich also beliebige Formeln hinzufügen. Dabei gibt es zwei Bedingungen:

-1- Die Funktion muß in einem VBA-Modul der Excel-Datei oder eines globalen Add-Ins stehen. Sie darf also nicht im Code-Teil einer einzelnen Tabelle oder der Arbeitsmappe stehen, weil sie dort von Excel nicht gefunden wird. Diese Bereiche sind meiner Meinung nach nur für Code gedacht, der zu Steuerelementen in der jeweiligen Tabelle gehört (Knöpfe, Listenfelder, …). Da die Funktion also im VBA-Modul steht, ist sie in allen Tabellen der Arbeitsmappe ausführbar, nicht aber in anderen Arbeitsmappen, also anderen Excel-Dateien. Wenn man das will, muß man die Datei als Add-In abspeichern (*.xla) und in den entsprechenden Ordner verfrachten, damit Excel sie findet. Ich habe hier z.B. einige im Ordner „C:\Program Files\Microsoft Office\Office\Library“ gefunden (Windows 2000). Von nun an sollte die Funktion von allen offenen Arbeitsmappen genutzt werden können. Eine Sache ist aber noch zu beachten: Wenn Du eine Funktion lokal in das VBA-Modul der Datei schreibst, dann wird sie natürlich auch mit der Datei verschickt und kann dort sofort eingesetzt werden. Wenn Du aber eine Funktion aus einer *.xla nutzt, mußt Du auch die *.xla-Datei mitschicken, weil der Code ja nicht mehr in der Excel-Datei (*.xls) enthalten ist.

-2- Die Funktion liefert ein Ergebnis zurück, das in einer Excel-Zelle dargestellt werden kann, also üblicherweise eine Zahl oder eine Zeichenkette (String).

> Sicherlich gibt es da auch noch eine Möglichkeit, aus IIIII
> in Spalte C IIII zu machen und statt IIIIII an gleicher Stelle
> IIII I erscheinen zu lassen.

Logisch. Und Du hättest sogar fast selbst drauf kommen können. Erinnere Dich an die Formel von Reinhard aus dem Posting zur Strichzählung. Man muß doch jetzt nur das einfache Wiederholen eines Striches gegen das von Reinhard austauschen. Und wenn Dir diese Striche dort (┼┼┼┼ und │) doch nicht mehr gefallen, dann nimmst Du halt IIIII und I.

Aus =Vervielfaeltige(„I“;B7) wird also zunächst mal =Wiederholen(„I“;B7), um die Excel-Formel statt meines Makros zu nutzen, und dann wird das ersetzt durch die Formel von Reinhard: =WIEDERHOLEN(„┼┼┼┼“;B7/5) & WIEDERHOLEN(„│“;REST(B7;5)), wobei ich hier die Strichels direkt angegeben habe und sie nicht aus irgendeiner Zelle hole, wie es bei Reinhard noch war.
Der erste Teil wiederholt den 5er-Block, falls in B7 eine Zahl >= 5 steht entsprechend oft. Steht in B7 eine 3, dann ergibt 3/5=0.6, also wird Null mal wiederholt. Steht eine 12 drin, dann wird wegen 12/5=2.4 zweimal wiederholt.
Der zweite Teil arbeitet sozusagen auf der anderen Seite der Division B7/5, denn hier wird der Rest als Parameter, also als „Multiplikator“ für den einzelnen Strich verwendet.

So, und wenn wir nun noch alle in eine einzige verschachtelte Formel bringen möchten, dann schreiben wir folgendes: =WIEDERHOLEN(„┼┼┼┼“;ZaehleVorkommen(A7; „(“)/5) & WIEDERHOLEN(„│“;REST(ZaehleVorkommen(A7; „(“);5)) & ": " & A7
Nochmal zur Erinnerung: A7 enthält Deinen Text, in dem die Anzahl der öffnenden Klammern (vorher Leerzeichen) angibt, wieviele Striche wir brauchen. Zunächst werden also die Klammern gezählt (ZaehleVorkommen(A7; „(“)), dann werden mit dem Ergebnis die Striche zusammengebaut (Wiederholen(…) & Wiederholen(…), und dann wird der Originaltext mit einem trennenden Doppelpunkt hinten drangehängt ( & ": " & A7).

Noch einmal zu ZaehleVorkommen(A7; „(“): Hier kommt ja das Schöne an den Parametern zu tragen! Du brauchst im Code der Funktion (also im VBA-Makro) nichts zu ändern, wenn Du statt der Leerzeichen eine öffnende Klammer oderso zählen willst. Du gibst einfach beim Benutzen der Formel eine Klammer als Parameter ein, und schon werden die Klammern gezählt.

Alles klar?

Kristian