Microsoft Excel

Herbers Excel/VBA-Archiv

Anzahl unterstrichene Zahlen feststellen

Betrifft: Anzahl unterstrichene Zahlen feststellen von: Beat Kocher
Geschrieben am: 19.11.2014 20:44:13

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

  

Betrifft: wenns VBA sein darf ... von: Matthias L
Geschrieben am: 19.11.2014 21:08:54

Hallo

https://www.herber.de/bbs/user/93884.xlsm
Evtl. muss da noch ne Überprüfung rein, ob die Zelle einen Inhalt hat.

Gruß Matthias


  

Betrifft: AW: wenns VBA sein darf ... von: Beat Kocher
Geschrieben am: 19.11.2014 21:34:27

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


  

Betrifft: Mach ne Function draus... von: {Boris}
Geschrieben am: 19.11.2014 21:40:39

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


  

Betrifft: AW: Mach ne Function draus... von: Beat Kocher
Geschrieben am: 20.11.2014 09:56:09

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


  

Betrifft: AW: Mach ne Function draus... von: Daniel
Geschrieben am: 20.11.2014 10:14:51

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


  

Betrifft: ... And Rng <> "" von: Matthias L
Geschrieben am: 20.11.2014 10:17:47

Hallo

Ergänze mal die Zeile im Code

If Rng.Font.Underline <> xlUnderlineStyleNone And Rng <> "" Then
Gruß Matthias


  

Betrifft: Das ist für deine SubProz OK, ... von: Luc:-?
Geschrieben am: 20.11.2014 14:47:35

…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 :-?


  

Betrifft: OK ... von: Matthias L
Geschrieben am: 20.11.2014 16:26:54

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


  

Betrifft: Auch 'kleine Lichter' sollte man nicht 'unter ... von: Luc:-?
Geschrieben am: 20.11.2014 19:04:08

…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 :-?


  

Betrifft: So, jetzt schicke ich dir auch noch deine ... von: Luc:-?
Geschrieben am: 21.11.2014 00:27:18

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 :-?


  

Betrifft: Danke! owT von: Matthias L
Geschrieben am: 21.11.2014 09:55:18




 

Beiträge aus den Excel-Beispielen zum Thema "Anzahl unterstrichene Zahlen feststellen"