Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1392to1396
1392to1396
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Anzahl unterstrichene Zahlen feststellen

Forumthread: Anzahl unterstrichene Zahlen feststellen

Anzahl unterstrichene Zahlen feststellen
19.11.2014 20:44:13
Beat
Hallo Excelspezialisten
In einem Bereich von mehreren Zeilen und Spalten habe ich Zahlen, wovon einige unterstrichen sein können. Ich möchte in einer Zelle die Anzahl der unterstrichenen Zahlen im Bereich ausgeben.
Für Eure Hilfe danke ich sehr.
Freundliche Grüsse
Beat

Anzeige

11
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: wenns VBA sein darf ...
19.11.2014 21:34:27
Beat
Hallo Matthias
Das schaut vielversprechend aus, kämen aber noch ein paar Sonderwünsche dazu.
Mein Bereich sind Zellen D3 bis G3 horizontal und vertikal bis D20/G20, also 72 Zellen mit Zahlen.
Die Summe der unterstrichenen Zahlen sollte in der Zelle F1 ausgegeben werden, sobald alle Zellen ausgefüllt sind. Ein bisschen viel verlangt?
Besten Dank für Deine Antwort und Bemühungen
Gruss Beat

Anzeige
Mach ne Function draus...
19.11.2014 21:40:39
{Boris}
Hi Beat,
Option Explicit Function My_UnderlineNone(Bereich As Range) Dim Rng As Range, x& For Each Rng In Bereich If IsNumeric(Rng) Then If Rng.Font.Underline xlUnderlineStyleNone Then x = x + 1 End If End If Next My_UnderlineNone = x End Function Und in der Mappe anwenden mit:
=My_UnderlineNone(B3:C3)
bzw. für Mehrfachbereiche:
=My_UnderlineNone((B3:C3;C5:C7))
VG, Boris

Anzeige
AW: Mach ne Function draus...
20.11.2014 09:56:09
Beat
Hallo Boris
Danke für Deinen Tipp, habe das versucht und es funtkioniert noch nicht ganz zufriedenstellend. Im Gegensatz zur Funktion Summe (da wird mit Ausfüllen der Bezugszellen dynamisch zusammengezählt) funktioniert die My_UnderlineNone nicht dynamisch. Wenn ich am Schluss die Zahlen mittels Inhalte löschen (Zellen sind leer) werden anscheinend immer noch formatierte Zellen gefunden.
Gibts da noch nen Trick?
Gruss Beat

Anzeige
AW: Mach ne Function draus...
20.11.2014 10:14:51
Daniel
Hi
einen vollständig zufriedenstellenden Trick gibt es momentan noch nicht.
Execl ist nicht für das Rechnen mit Formaten ausgelegt, dh das Ändern eines Formates löst in Excel nicht das Bedürfnis aus, die Formeln die sich auf diese Zelle beziehen, neu zu berechnen.
Das passiert nur, wenn du einen Inhalt änderst.
Such mal nach dem Thema "Farbsumme", dort wurde die Problematik schon zur Genüge besprochen.
eine Verbesserung (aber noch keine vollständige Lösung) wäre, die Funktion volatil zu machen, dh sie wird dann bei jeder Änderung eines beliebigen Zellinhaltes neu berechnet (wie beispielsweise die Funktionen ZUFALLSZAHL() oder JETZT())
hierzu kannst du das in der UDF entsprechend angeben: 
Option Explicit
Function My_UnderlineNone(Bereich As Range)
Dim Rng As Range, x&
Application.Volatile
...
oder du kombinierst die UDF in der Zellformel mit einer automatisch volatilen Funktion (naürlich ohne den Wert zu verändern): 
=My_UnderlineNone(A1:A10)+0*Zufallszahl()
Volatile Funktionen in grosser Anzahl wirken sich negativ auf die Performance aus, weil sie bei jeder Änderung in Excel neu berechnet werden und damit sehr oft, auch wenn es nicht notwendig wäre.
um manuell eine neuberechnung anzustossen ohne einen relevanten Zellwert zu ändern, könntest du eine ansonsten unbenutze Zelle mit in den Zellbereich aufnehmen (z.B. die Zelle A1) und dann in dieser den Inhalt ändern, wenn du eine Aktualisierung benötigts.
Gruß Daniel
Gruß Daniel

Anzeige
... And Rng <> ""
20.11.2014 10:17:47
Matthias
Hallo
Ergänze mal die Zeile im Code
If Rng.Font.Underline  xlUnderlineStyleNone And Rng  "" Then
Gruß Matthias

Das ist für deine SubProz OK, ...
20.11.2014 14:47:35
Luc:-?
…Matti,
und sollte im Falle einer Löschung auch für Boris' UDF-Variante reichen, nur tritt dort ja zusätzlich das von Daniel erwähnte Format­Änderungs­Problem auf. Außerdem entspricht eine derart in ihrer Anwendbarkeit beschränkte UDF nicht meinen Vorstellungen von dem, was eine UDF leisten sollte. Außerdem sollte schon ihr Name ihrem Zweck entsprechen (es geht hier ja gerade um Werte mit Unter­strei­chungen, nicht ohne) wie bspw bei meiner UDF LtUpPart (LtUp für LightUp) für ähnliche Zwecke (allerdings ursprgl nur für Teil­Auszeich­nungen →Part← von Texten gedacht, wobei bei Änderungen von Text­Teil­Forma­tie­rungen das von Daniel ange­sprochene Problem nicht auftritt!), allerdings für verschiedene Auszeich­nungs­arten und auch einige Kombina­tionen derselben.
Allerdings decken deine SubProz und die UDF bereits alle Unterstreichungen von Zahlen summarisch ab. Die Nicht­Berück­sich­tigung von Texten ist also idR unproble­matisch. Bei Farben, Fett, Kursiv u.ä. handelt es sich aber auch um eine Art von Auszeichnung. MS hat bspw TEILERGEBNIS auch nicht nur für eine einzige Fkt ausgelegt, also könnte die UDF auch für weitere Auszeich­nungs­arten ausgelegt wdn, damit letztlich nicht ein ganzes UDF-Bündel benötigt wird (mag als Pgmier­Hilfs­mittel OK sein, aber kaum für Zell­Fml-UDFs)…
Mal nur als FmlBsp (ohne UDF-Code → nur bei angezeigtem Bedarf nachgereicht!), ich setze die o.g. UDF hier so ein (auf dein Bsp):
{=SUMME(Compute("if(LtUpPart(B#:B#,-4)="""",0,1)";"2:7");Compute("if(LtUpPart(C#:C#,-4)="""",0,1)"; "2:7")) }
Die UDF Compute ist hier erforderlich, weil LtUpPart nur mit Einzel­werten arbeitet. Man könnte aber auch mit …
{=WENN(LtUpPart(INDIREKT(ADRESSE(ZEILE(2:7);SPALTE(B:C);4));-4)="";0;1)}
…eine Hilfsmatrix erzeugen und die dann summieren (Text wird allerdings mit­ausge­wertet und muss ggf noch ausge­schlossen wdn).
Die UDF LtUpPart ist für folgd Argumente ausgelegt (mit [^A] ausgebbar):
LtUpPart(Bezug;AuszTyp;KombTyp;DarstTyp)
Sie gibt den, wie unter Arg2/3 angegeben, ausge­zeich­neten Text(-Teil) von Arg1, ggf in Form nach Arg4, wieder. Zahlen wdn also auch zu Text, was ggf berücksichtigt wdn muss. Da die Volatilität ursprgl nicht erforderlich war und bei ihrem HptZweck auch immer noch nicht ist, muss ggf T(JETZT()) hinzugefügt wdn. Dann reicht [F9] oder ein Klick in eine beliebige (Leer-)Zelle zur Neuberechnung.
Gruß, Luc :-?

Anzeige
OK ...
20.11.2014 16:26:54
Matthias
Hallo Luc
So lieber Luc
Jetzt musst Du mal fast genauso viel lesen wie ich in Deinen Beiträgen ;o)
Ich bin leider kein Programmierer, das darfst Du mir bitte nicht übelnehmen.
Das Du im Schreiben von UDF's einer der ganz Großen bist weiß ich ja.
Aber mir fehlt dann zum Nachvollziehen immer ein konkretes Beispiel
Verstehst Du, eins zum anfassen/zum spielen. Aber eben auch noch unkompliziert!
Ich kanns ja nur Step by Step
In Deinem Beitrag schreibst Du dann das Du es so in meinem Bsp einsetzt:
{=SUMME(Compute("if(LtUpPart(B#:B#,-4)="""",0,1)";"2:7");Compute("if(LtUpPart(C#:C#,-4)="""",0,1)"; "2:7")) }
Compute & LtUpPart sind ja wieder 2 UDFs die ich nicht kenne. Also Bedarf besteht!
Es ist sehr mühsam und zeitaufwendig dann im Forum etwas zu finden
in dem dann nicht wieder eine andere UDF mit eingebunden ist.
So geht das Suchen wieder los.
Das mit der UDF war ja ein Vorschlag von Boris und hat sich erst in der Beitragsfolge so entwickelt.
Ich bin eben leider noch Einer der nur Insellösungen anbieten kann.
Ich habe das mit der UDF jetzt mal so umgesetzt:
Klickt man auf die Zelle welche die Zellen mit Unterstrich zählt, wird ein Claculate ausgelöst.
Einen Hinweis darauf steht direkt unter dieser Zelle
Den Zusatz +0*Jetzt() habe ich ebenfalls eingepflegt.
 BCDEF
394482 18
4692435 28
5763389 11
6682260 41
7939542 32
8837231 3
9281835 62
10191839 86
11856090 46
12     
13     
149    
15zum Aktulisieren klicke auf B14    
16     
17Control-Claculate    
1820.11.2014 15:38:00    

Formeln der Tabelle
ZelleFormel
B14=My_UnderlineNone(B3:F11;B3:D11;F3:F11)+0*JETZT()
B18=JETZT()


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
https://www.herber.de/bbs/user/93917.xlsm
Nicht wundern, in der Datei hatte ich die Doppelklammer wg Mehrfachbereich nicht eingesetzt
Deshalb sieht es etwas anders aus. Habs aber schon korrigiert. Nur hatt ich die Datei bereits hochgeladen.
Wenn man jetzt einer Zelle den Unterstrich verpasst und auf die ZielFormelzelle(B14) klickt wird ja neu berechnet

Sorry, Luc das Du mich anscheinend immer überschätzt.
Ich bin was das Programmieren angeht eben noch ein ganz kleines Licht.
Gruß Matthias

Anzeige
Auch 'kleine Lichter' sollte man nicht 'unter ...
20.11.2014 19:04:08
Luc:-?
…den Scheffel' stellen, Matti… ;-]
Überschätzt? Vielleicht, aber ich habe wohl doch „zuviele“ recht bis sehr ordentliche BTe von dir gesehen… ;-)
Sei's drum, LtUpPart folgt nach. Compute ist im Archiv wohl nur erwähnt und ist „einfach“ nur eine etwas komplexere Anwendung der vbFkt Evaluate (habe noch 2 Varianten, eine sogar - bedingt - mit lokaler FmlText-Notation und etliche simple), weshalb ich die hier wohl nicht darstellen muss, da ihr Einsatz ja auch mit Hilfszellen umgangen wdn kann.
Ja, dass mit den UDFs in UDFs ist ein Problem, weshalb ich auch viele meiner UDFs hier nicht einstellen kann. Es war vor Jahren schon schwierig, eine Teilmenge für ein AddIn zu bestimmen, ohne dass da noch Verwendungen von UDFs vorkommen, die nicht im AddIn enthalten sind. Musste, um das zu vermeiden, extra ein Pgm schreiben, das diese Verwendungen pro UDF auflistet!
Deine neue Arbeit sehe ich mir später an, muss jetzt schnell noch mal weg.
Luc :-?

Anzeige
So, jetzt schicke ich dir auch noch deine ...
21.11.2014 00:27:18
Luc:-?
…neue Datei zurück, Matti;
darin enthalten ist neben deinen Pgmm die angekündigte UDF, nebst Anwendungs­Bspp, und eine BedingtFormatierung des Daten­Bereichs. Deswei­teren habe ich mir erlaubt, deine Boris-UDF auf das Ursprüngliche zurück­zusetzen, denn eine 3teilige Range-Angabe kann auch in der Form, auf die ich deine Ermittlungs­Fml geändert habe, gemacht wdn. For Each In … sorgt dafür, dass auch alle Zellen eines unzu­sammen­hängenden Bereichs durchlaufen wdn.
Wie meine UDF mit einem solchen Bereich zurecht­kommen kann, habe ich ebenfalls gezeigt. Dabei sorgt INDEX dafür, dass die MxFml in jeder Zelle des Ergebnisses einen anderen UDF-Arg1-Wert (den richtigen!) erhält. Dadurch entsteht eine passive MxFml-Fktionalität (→ -Tauglichkeit) der UDF, allerdings auch nur in einer Ergebnis­Matrix, nicht in einer 1zelligen MxFml (wie bei und nur wg INDEX; ähnlich ist es bei Anwendung von Compute u.ä., nur wird dann wie bei INDIREKT die ganze Matrix zV gestellt). Soll sie auch aktiv mxfml-fähig sein, müsste eine als Arg1 über­gebene Matrix in der UDF Zelle für Zelle bzw Wert für Wert abge­arbeitet wdn. Anders kann es auch bei Standard­XlFktt nicht sein.
Eines meiner Anliegen ist es auch, VBA-Bastler für die Möglich­keiten von UDFs zu sensi­bili­sieren. Dabei kommt es sehr darauf an, an alle möglichen Nutzungs­Situationen zu denken und hier eine zweck­mäßige, nicht zu enge Auswahl zu treffen. Um den Nutzungs­komfort der guten Standard­Fktt zu erreichen, ist es auch erforder­lich, nach Möglich­keit auch Daten­felder (zB aus Ergebnissen von Ausdrücken) als Argument zuzulassen (wäre hier aber nicht sonder­lich sinnvoll, da das Range-Objekt zwingend benötigt wird). Das UDF-Ergebnis sollte (im GgSatz zu Behaup­tungen manch angeb­licher VBA-Spezia­listen!) idR auch variant sein, weil nur so auch unter­schied­liche Fehler­werte zurück­gegeben wdn können.
Na dann, viel Erfolg, Spaß und schöWE, Luc :-?

Anzeige
Danke! owT
21.11.2014 09:55:18
Matthias
Anzeige

Infobox / Tutorial

Anzahl unterstrichene Zahlen in Excel feststellen

Schritt-für-Schritt-Anleitung

Um die Anzahl der unterstrichenen Zahlen in einem bestimmten Zellbereich in Excel zu zählen, kannst Du eine benutzerdefinierte Funktion (UDF) in VBA erstellen. Hier ist eine einfache Anleitung:

  1. Öffne den VBA-Editor:

    • Drücke ALT + F11, um den Visual Basic for Applications (VBA) Editor zu öffnen.

  2. Füge ein neues Modul hinzu:

    • Klicke im Menü auf Einfügen > Modul.

  3. Füge den folgenden Code ein:

    Option Explicit
Function My_UnderlineNone(Bereich As Range) As Long
   Dim Rng As Range
   Dim x As Long
   x = 0

   For Each Rng In Bereich
       If IsNumeric(Rng) Then
           If Rng.Font.Underline <> xlUnderlineStyleNone Then
               x = x + 1
           End If
       End If
   Next Rng

   My_UnderlineNone = x
End Function

  4. Schließe den VBA-Editor:

    • Schließe den Editor und gehe zurück zu Deiner Excel-Tabelle.

  5. Verwende die Funktion in einer Zelle:

    • Gib in einer Zelle ein: 
      =My_UnderlineNone(D3:G20)
    • Dies zählt die unterstrichenen Zahlen im Bereich D3 bis G20.

Häufige Fehler und Lösungen

  • Fehler: Funktion gibt falsche Ergebnisse zurück
    Lösung: Stelle sicher, dass die Zellen im gewählten Bereich tatsächlich Zahlen enthalten und richtig formatiert sind. Überprüfe auch, ob die Unterstreichung korrekt angewendet wurde.

  • Fehler: Funktion aktualisiert sich nicht
    Lösung: Du kannst die Funktion Application.Volatile hinzufügen, um die Funktion bei jeder Änderung in der Arbeitsmappe zu aktualisieren. Füge diese Zeile direkt nach Dim Rng As Range hinzu.

Alternative Methoden

Wenn Du keine VBA-Funktion verwenden möchtest, kannst Du die Anzahl der unterstrichenen Zahlen auch manuell zählen oder mit bedingten Formatierungen arbeiten, um die unterstrichenen Zellen hervorzuheben. Das ist jedoch weniger automatisiert und erfordert manuelle Eingaben.

Praktische Beispiele

Beispiel 1: Zähle unterstrichene Zahlen in einem benutzerdefinierten Bereich.

  • Definiere den Bereich in einer Formel: 
    =My_UnderlineNone(B3:F11)

Beispiel 2: Zähle unterstrichene Zahlen in mehreren nicht zusammenhängenden Bereichen.

  • Verwende: 
    =My_UnderlineNone((B3:C3;C5:C7))

Tipps für Profis

  • Verwende die Kombination +0*JETZT() am Ende Deiner UDF, um sicherzustellen, dass die Berechnung aktualisiert wird, wenn Du Änderungen in anderen Zellen vornimmst.
  • Achte darauf, dass die benutzerdefinierte Funktion nur auf die Zellen angewendet wird, die Du wirklich benötigst, da große Bereiche die Performance beeinträchtigen können.

FAQ: Häufige Fragen

1. Frage
Wie kann ich die UDF für andere Formate anpassen?
Antwort: Du kannst die Bedingungen im Code ändern, um auch andere Formatierungen zu berücksichtigen, wie z.B. fett oder kursiv.

2. Frage
Kann ich die Funktion auch in Excel Online verwenden?
Antwort: Nein, benutzerdefinierte Funktionen in VBA sind nur in Desktop-Versionen von Excel verfügbar. In Excel Online musst Du alternative Methoden verwenden.

3. Frage
Wie kann ich die Funktion dynamisch machen?
Antwort: Füge Application.Volatile in Deine Funktion ein, um sicherzustellen, dass sie sich bei jeder Änderung in der Arbeitsmappe aktualisiert.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Anzeige