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

UDF, wenn x-te Stelle in Bereichen 0 u. eingeblen.

Forumthread: UDF, wenn x-te Stelle in Bereichen 0 u. eingeblen.

UDF, wenn x-te Stelle in Bereichen 0 u. eingeblen.
Simon
Guten Tag
Ich habe folgende Problemstellung, die ich gerne mit einem UDF lösten möchte:
- eine oder mehrere Spaltenbereiche mit gleichviel Zellen (im Beispiel benannt mit Bereich_A, Bereich_V, Bereich_X)
- wenn in der x-ten Zeile aller Bereiche ein Nullwert steht (oder eine der Wert der Formel Null ergibt), sollte diese Zeile ausgeblendet sein (manuell)
- ist eine solche Zeile nicht ausgeblendet (wie im Beispiel Zeile 3 und 21, dann sollte die UDF "Zeile 4;21" zurückgeben
- falls keine Zeile ausgeblendet werden muss, kann die Rückgabe "OK" sein.
Kann mir jemand da auf die Sprünge helfen?
Vielen Dank, Simon
Tabelle1

 ABCDEFG
20 1  1 
3444 444  444 
40 0  0 
541 15  15 
6       
7485 460  460 
8       
9       
10       
1113 39  39 
120 444  444 
1315 0  0 
1441 15  15 
15       
1669 498  498 
17       
18       
19       
200 1  1 
210 0  0 
220 13  13 
2341 15  15 
24       
2541 29  29 
26       
27       
28gelb =  grün =  blau = 
29Bereich_A Bereich_V  Bereich_X 
30       
31       
32Auswertung (UDF)      
33UDF_NUMM(Bereich_A;Bereich_V;Bereich_X)      
344;21      

Formeln der Tabelle
ZelleFormel
A7=SUMME(A2:A6)
C7=SUMME(C2:C6)
F7=SUMME(F2:F6)
A16=SUMME(A11:A15)
C16=SUMME(C11:C15)
F16=SUMME(F11:F15)
A25=SUMME(A20:A24)
C25=SUMME(C20:C24)
F25=SUMME(F20:F24)


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Anzeige

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

Betreff
Benutzer
Anzeige
UDF für Zeilennummern mit Bedingungen
06.05.2010 19:33:43
Erich
Hi PSimon,
probier mal Function UDF_NUMM(rngA As Range, rngB As Range, rngC As Range) As String Dim zz As Long If rngA.Columns.Count * rngB.Columns.Count * rngC.Columns.Count 1 Then UDF_NUMM = "Jeder Bereich muss einspaltig sein." ElseIf rngA.Row rngB.Row Or rngC.Row rngC.Row Then UDF_NUMM = "Die Bereiche müssen in der selben Zeile beginnen." ElseIf rngA.Count = rngB.Count And rngA.Count = rngC.Count Then For zz = 1 To rngA.Count If rngA(zz).EntireRow.Hidden Then ElseIf IsEmpty(rngA(zz)) Or IsEmpty(rngB(zz)) Or IsEmpty(rngC(zz)) Then ElseIf rngA(zz) = 0 And rngB(zz) = 0 And rngC(zz) = 0 Then If Len(UDF_NUMM) > 0 Then UDF_NUMM = UDF_NUMM & ";" UDF_NUMM = UDF_NUMM & CStr(zz) End If Next zz Else UDF_NUMM = "Alle Bereiche müssen gleiche Zeilenzahl haben." End If End Function Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort
Anzeige
Ergänzung
06.05.2010 19:38:20
Erich
Hi,
da fehlte noch das "OK"...
Bitte füge direkt unter der Zeile "Next zz" eine neue Zeile ein:
If UDF_NUMM = "" Then UDF_NUMM = "OK"
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort
AW: Ergänzung
06.05.2010 21:35:45
Simon
Hallo
Das ist grandios. Vielen Dank!
Jetzt habe ich noch zwei Anliegen.
Erstens wäre es hilfreich, wenn nicht die Zeilennummer des Bereichs sondern die "absolute" Zeilennummer der Tabelle zurückgegeben würde.
Ob sich auch der zweite Punkt umsetzen lässt? Gerne möchte ich bei der Bereichsdefinition zwischendurch mal eine oder mehrere überspringen (weil auf dieser Zeile z.B. tatsächlich Nullen sichtbar stehen müssen.
Der Bereich (als Beispiel für Spalte A) wäre dann
anstelle A2:A50
z.B. A2:A20;A25:A40;A45:A50
wobei das bei den anderen Spaltenbereichen dann synchron wäre.
Eine Lösung wäre genial!
Gruss, Simon
PS: Ich habe gestaunt, dass die UDF immer sofort die Änderungen in den Spalten verarbeitet - und dies ohne Application.volatile - weshalb ist das so?
Anzeige
AW: Ergänzung
06.05.2010 23:15:30
Erich
Hi PS,
der erste Punkt ist leicht erledigt - mit einer leicht geänderten Zeile:
UDF_NUMM = UDF_NUMM & CStr(zz + rngA.Row - 1)
Der zweite Punkt ginge sicher auch. Aber hier stellt sich die Frage nach der Sinnhaftigkeit.
Wie wären die Parameter der UDF zu deklarieren? Wie viele Bereiche gibt es?
Man kann so eine Fkt. auch mit einer variablen Parameterzahl definieren.
Wenn 7 Parameter vorgegeben werden, sollen dann die ersten 5 (als Teilbereiche) zur 1. Spalte gehören,
der 6. und 7. Parameter dann die 2. und 3. Spalte festlegen?
Ist es da nicht viel einfacher, dir Fkt. mehrmals für die Teilbereiche aufzurufen, etwa so (für die Spalten A, B und D)?
Tabelle1

 F
24
3OK
4OK

Formeln der Tabelle
ZelleFormel
F2=UDF_NUMM(A2:A20;BEREICH.VERSCHIEBEN(A2:A20;0;1); BEREICH.VERSCHIEBEN(A2:A20;0;3))
F3=UDF_NUMM(A25:A40;BEREICH.VERSCHIEBEN(A25:A40;0;1); BEREICH.VERSCHIEBEN(A25:A40;0;3))
F4=UDF_NUMM(A45:A50;BEREICH.VERSCHIEBEN(A45:A50;0;1); BEREICH.VERSCHIEBEN(A45:A50;0;3))

Da siehst man auch sofort, in welchem Teilbereich es noch klemmt.
Zu Volatile ein Zitat aus der VBA-Hilfe: 

Kennzeichnet eine benutzerdefinierte Funktion als veränderlich.
Solche Funktionen werden immer neu berechnet,
wenn in einer beliebigen Zelle des Arbeitsblattes eine Berechnung durchgeführt wird.
Nicht veränderliche Funktionen werden nur dann neu berechnet, wenn sich die Eingabevariablen ändern.
Die Eingabevariablen in UDF_NUMM sind Bereiche. Wenn sich in den Bereichen etwas ändert,
ändern sich die Eingabevariablen - und es wird auch ohne Volatile automatisch neu gerechnet.
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort
Anzeige
AW: Ergänzung
07.05.2010 07:03:39
Simon
Hallo Erich
Vielen Dank für die Rückmeldung.
Ich habe den ersten Punkt eingebaut, vielen Dank.
Vielleicht habe ich mich beim 2. Punkt nicht klar ausgedrückt, deshalb habe ich ein Beispile hochgeladen, wo ersichtlich ist, dass die Teilbereiche jeweils zur gleichen Spalte gehören und es pro Spalte auch gleichviele Teilbereiche gibt.
Intressant ist, dass die Zeilen 17,18,19,20 ausgegeben werden, nicht jedoch 26 und 27.
Vielleicht siehst du trotzdem noch eine Lösung für die Zusammensetzung der Spaltenbereiche aus mehrern Teilen.
Danke und Gruss, Simon
PS: Wie ist für dich ersichtlich, dass ich nicht nur Simon heisse (selbst bevor ich eine Arbeitsmappe hochgeladen habe ;-) ?
https://www.herber.de/bbs/user/69468.xls
Anzeige
mit mehreren Areas pro Spalte
07.05.2010 08:00:40
Erich
Hi Simon,
die Fkt. konnte bisher nur mit zusammenhängenden Bereichen umgehen.
Die neue hier funzt auch mit zusammengesetzten Bereichen
und gibt auch 26;27 richtig aus: 

Function UDF_NUMM(rngA As Range, rngB As Range, rngC As Range) As String
Dim zz As Long, mm As Long, rngT As Range, lngT As Long, lngB As Long, lngC As Long
If rngA.Columns.Count * rngB.Columns.Count * rngC.Columns.Count  1 Then
UDF_NUMM = "Jeder Bereich muss einspaltig sein."
ElseIf rngA.Areas.Count  rngB.Areas.Count Or _
rngB.Areas.Count  rngC.Areas.Count Then
UDF_NUMM = "Die Bereiche müssen gleich viele Teilbereiche haben."
ElseIf rngA.Row  rngB.Row Or rngB.Row  rngC.Row Then
UDF_NUMM = "Die Bereiche müssen in der selben Zeile beginnen."
ElseIf rngA.Count = rngB.Count And rngA.Count = rngC.Count Then
lngB = rngB.Column
lngC = rngC.Column
For Each rngT In rngA.Areas
For zz = 1 To rngT.Count
lngT = rngT.Rows(zz).Row
If rngT(zz).EntireRow.Hidden Then
ElseIf IsEmpty(rngT(zz)) Or _
IsEmpty(Cells(lngT, lngB)) Or IsEmpty(Cells(lngT, lngC)) Then
ElseIf rngT(zz) = 0 And _
Cells(lngT, lngB) = 0 And Cells(lngT, lngC) = 0 Then
If Len(UDF_NUMM) > 0 Then UDF_NUMM = UDF_NUMM & ";"
UDF_NUMM = UDF_NUMM & CStr(zz + rngT.Row - 1)
End If
mm = rngA(zz).Row
Next zz
Next rngT
If UDF_NUMM = "" Then UDF_NUMM = "OK"
Else
UDF_NUMM = "Alle Bereiche müssen gleiche Zeilenzahl haben."
End If
End Function
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort
Anzeige
AW: mit mehreren Areas pro Spalte
09.05.2010 17:27:41
Simon
Hallo Erich
Das hat prima funktioniert, vielen Dank!.
Ich war sogar in der Lage, die Funktion auf 4 Spalten zu erweitern.
Dann habe ich versucht, sie auf zwei Spalten zu kürzen - irgendwie klappt das noch nicht. Wäre super, wenn du feststellen könntest, wo noch eine Anpassung notwendig ist.
Beste Grüsse - heute aus dem saftigen (wörtlich!) Tessin!
Simon
Function NULL2(rngA As Range, rngB As Range) As String
Dim zz As Long, mm As Long, rngT As Range, lngT As Long, lngB As Long
If rngA.Columns.Count * rngB.Columns.Count  1 Then
NULL2 = "Jeder Bereich muss einspaltig sein."
ElseIf rngA.Areas.Count  rngB.Areas.Count Then
NULL2 = "Die Bereiche müssen gleich viele Teilbereiche haben."
ElseIf rngA.Row  rngB.Row Then
NULL2 = "Die Bereiche müssen in der selben Zeile beginnen."
ElseIf rngA.Count = rngB.Count Then
lngB = rngB.Column
For Each rngT In rngA.Areas
For zz = 1 To rngT.Count
lngT = rngT.Rows(zz).Row
If rngT(zz).EntireRow.Hidden Then
ElseIf IsEmpty(rngT(zz)) Or _
IsEmpty(Cells(lngT, lngB)) Then
ElseIf rngT(zz) = 0 And _
Cells(lngT, lngB) = 0 = 0 Then
If Len(NULL2) > 0 Then NULL2 = NULL2 & ";"
NULL2 = NULL2 & CStr(zz + rngT.Row - 1)
End If
mm = rngA(zz).Row
Next zz
Next rngT
If NULL2 = "" Then NULL2 = "OK"
Else
NULL2 = "Alle Bereiche müssen gleiche Zeilenzahl haben."
End If
End Function

Anzeige
Minifehler
09.05.2010 17:43:02
Erich
Hi Simon,
da ist nur ein kleiner Lapsus drin - sonst müsste das funzen.
In der Zeile 

ElseIf rngT(zz) = 0 And _
Cells(lngT, lngB) = 0 = 0 Then
solltest du "= 0 = 0" zu "= 0" kürzen.
Rückmeldung wäre nett! - Schonen Rest-Sonntag und Grüße ins Ticino von Erich aus Kamp-Lintfort
Anzeige
AW: Minifehler
09.05.2010 17:47:19
Simon
Hallo Erich
Das wars! Vielen Dank.
Simon
AW: mit mehreren Areas pro Spalte
09.05.2010 18:13:40
Gerd
Hi Erich!
Ein schönes Teil!
mm = rngA(zz).Row war wohl noch vom Testen drin u. könnte entfallen.
Gruß Gerd
Stimmt - weg mit dem mm - Danke! (owT)
09.05.2010 22:45:08
Erich
Anzeige
;

Forumthreads zu verwandten Themen

Wenn-Dann + SVERWEIS ???
B@sti

Hallo zusammen,
ich bräuchte zu folgendem Thema eure Hilfe:
und zwar geht es um den Abgleich zweier Arbeitmappen. In der ersten Arbeitsmappe befinden sich die Infos, aus der ein Ergebnis für die zweite Arbeitsmappe ausgelesen werden soll. Hab mal ne Beispieldatei angehängt:
https://...
21.08.2008 10:54:00
Thread-ID: 1003404
4 Beiträge
"Wenn Zeit > 17.30 Uhr dann"
Susanne99

Hallo,
wie formuliere und formatiere ich den o.g. Ausdruck als Formel?
Gruß
Susanne
14.09.2008 23:24:58
Thread-ID: 1009640
6 Beiträge
Mehr als 7 Wenn-Dann-Verschachtelungen
Bernd

Hallo Leute,
ich habe eine WENN DANN Formel mit mehr als 7 Verschachtelungen. Hat jemand eine Idee wie ich diese Formel so umschreiben kann, dass ich Sie nutzen kann?
Hier ist sie:
=WENN(Monat($D$2)=1;SVERWEIS($D$2;'01'!$G$7:$R$43;2);WENN(Monat($D$2)=2;SVERWEIS($D$2;'02'!$G$7:$R$43...
13.10.2008 17:23:11
Thread-ID: 1016134
4 Beiträge
Wenn-Dann
Jenny

Hallo,
also in meiner Tabelle habe ich mehrere Daten z.B. 01.12.1990. Excel soll mir nun alle Daten die vor dem 01.10.2005 waren als 01.10.2005 anzeigen...alle Daten die nach dem 01.10.2005 sind soll er mir auch genau anzeigen. Geht das?
Lieben Gruß
23.10.2008 10:31:00
Thread-ID: 1018545
7 Beiträge
Anzeige
Textbox - wenn nicht mehr als 3 stellen dann
Patrick

Hallo,
ich habe auf einer Userform eine Textbox in welche je nach Auswahl Kürzel (z. B. BE) eingetragen werden. Es soll nun realisiert werden, dass wenn ich auf einen commandbutton drücke, geprüft wird ob in der besagten Textbox nach wie vor nur "BE" steht oder diese Textbox bearbeitet wurd...
12.11.2008 16:39:47
Thread-ID: 1023910
5 Beiträge
WENN-DANN verschachteln
Stefan

Guten Morgen Zusammen
Hab da ein kleines WENN-DANN Problem. Da müßte ich (wahrscheinlich) schachteln. Aber mit Formeln und verschachteln hab ich so meine lieben Probleme.
Problem:
Ich möchte eine Auswertung machen. Hierzu steht in Zelle E18 der Wert - z.B. A für Aussenstelle und H f...
21.11.2008 08:11:00
Thread-ID: 1026592
2 Beiträge
Anzeige
Wenn-Dann-Funktion als Makro
Sulley

Hallo zusammen,
ich hänge an einem Makro fest.
Matthias L und fcs (Vielen dank noch mal an dieser Stelle) haben mir mal jeder ein super makro gebaut, mit welchem ich Daten von einer Tabelle ans Ende einer anderen Tabelle schicken kann. Hat auch super geklappt.
Hier nochmal das Makro...
24.11.2008 16:47:36
Thread-ID: 1027372
11 Beiträge
Formelproblem "wenn(text - beinhaltet, dann....)
Andi

Hallo Experten,
Ich steh bei folgender Excelformel an.
=WENN(D13=....folgender text beinhaltet ist (AA).....;"True";"false")
Ich würde gerne überprüfen ob im Feld D13 der Text "AA" vorkommt (z.B. Artikel "AA01"), falls dies zutrifft bitte "True" schreiben, ansonsten "false".
Da...
04.12.2008 11:42:00
Thread-ID: 1030117
3 Beiträge
WENN - dann nicht
anke2245

Hallo Excelfreunde,
wieder einmal ein Problem mit der Erstellung und der Eingabe einer Formel:
Beispiel:
befindet sich in der Zelle B12 eine 1; darf in der Zelle D12 keine 1 eingegeben werden;
befindet sich in der Zelle B12 eine 2; darf in der Zelle D12 keine 2 eingegeben werden...
12.12.2008 15:27:31
Thread-ID: 1032433
5 Beiträge
Zählenwenn Excel2000 vs Excel 2003
Peter


Guten Tag
In meinem Excel-Sheet sind die Spalten B1:B5 alle leer. Wenn ich nun mit der Formel
ZÄHLENWENN(B1:B5;"""")
abfrage, wieviele Zellen nicht leer sind, erhalte ich als Antwort 0 - Gerne hätte ich jedoch als Antwort 5 ....
Wenn ich die gleiche Formel in der Excel 2003...
10.02.2009 15:37:20
Thread-ID: 1049154
11 Beiträge
Wenn - dann Formel
Thorsten

Hi ,
ich habe folgende Datei hochgeladen und nun folgende Frage
https://www.herber.de/bbs/user/61311.xls
Im ersten Tabellenblatt soll oben die kalenderwoche eingetragen werden - heir als Bsp. die 7. Im Tabellenblatt (Date) stehen ID´s hierzu - diese sollen im Blatt Overview in der...
20.04.2009 15:10:35
Thread-ID: 1069854
2 Beiträge
Wenn-Dann-Abfrage mit mehr als 8 Argumenten
Uwe

Hallo an alle Nutzer dieses Forums,
ich habe folgendes Problem mit einer Wenn-Dann-Abfrage.
Wir haben 15 experimentell ermittelte Zahlenwerte die mit folgenden Werten verbunden sind ( 2,4,6,8,10... bis 30 ).
Wenn Wert 2,dann Wert A,wenn Wert 4, dann Wert B usw. bis Wert 30,dann Wer...
20.04.2009 16:42:20
Thread-ID: 1069883
8 Beiträge
Anzeige

Beliebteste Forumthreads (12 Monate)

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