AW: help needed :)
09.12.2015 11:50:47
ransi
Hallo Vicci,
Als UDF in D1 z.B. so:
' **********************************************************************
' Modul: Modul1 Typ: Allgemeines Modul
' **********************************************************************
Option Explicit
Function viccci(r As Range)
Dim I As Long, J As Long
On Error GoTo Errorhandler
Redim Out(1 To r.Rows.Count)
For I = 1 To r.Rows.Count
If r(I, 2) <> 0 Then
J = J + 1
Out(J) = r(I, 1) & "=" & r(I, 2)
End If
Next
Redim Preserve Out(1 To J)
viccci = Join(Out, "; ")
Errorhandler:
End Function
Ohne VBA in D2 so:
Tabelle1
| | A | B | C | D |
| 1 | A | 0% | | B=0,18; C=0,18; H=0,18; I=0,22; J=0,13; K=0,13 |
| 2 | B | 18% | | B=0,18; C=0,18; H=0,18; I=0,22; J=0,13; K=0,13 |
| 3 | C | 18% | | |
| 4 | D | 0% | | |
| 5 | E | 0% | | |
| 6 | F | 0% | | |
| 7 | G | 0% | | |
| 8 | H | 18% | | |
| 9 | I | 22% | | |
| 10 | J | 13% | | |
| 11 | K | 13% | | |
| 12 | L | 0% | | |
| Formeln der Tabelle |
| Zelle | Formel | | D1 | =viccci(A1:B12) | | D2 | =LINKS(WENN(B1=0;"";"A="& B1 & "; " ) &
WENN(B2=0;"";"B="& B2 & "; " ) &
WENN(B3=0;"";"C="& B3 & "; " ) &
WENN(B4=0;"";"D="& B4 & "; " ) &
WENN(B5=0;"";"E="& B5 & "; " ) &
WENN(B6=0;"";"F="& B6 & "; " ) &
WENN(B7=0;"";"G="& B7 & "; " ) &
WENN(B8=0;"";"H="& B8 & "; " ) &
WENN(B9=0;"";"I="& B9 & "; " ) &
WENN(B10=0;"";"J="& B10 & "; " ) &
WENN(B11=0;"";"K="& B11 & "; " ) &
WENN(B12=0;"";"L="& B12 & "; " );
LÄNGE(
WENN(B1=0;"";"A="& B1 & "; " ) &
WENN(B2=0;"";"B="& B2 & "; " ) &
WENN(B3=0;"";"C="& B3 & "; " ) &
WENN(B4=0;"";"D="& B4 & "; " ) &
WENN(B5=0;"";"E="& B5 & "; " ) &
WENN(B6=0;"";"F="& B6 & "; " ) &
WENN(B7=0;"";"G="& B7 & "; " ) &
WENN(B8=0;"";"H="& B8 & "; " ) &
WENN(B9=0;"";"I="& B9 & "; " ) &
WENN(B10=0;"";"J="& B10 & "; " ) &
WENN(B11=0;"";"K="& B11 & "; " ) &
WENN(B12=0;"";"L="& B12 & "; " ))-2) |
|
Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
ransi