PDA

Vollständige Version anzeigen : Anzahl Formeln in einer Spalte berechnen


gizmo0375
11.04.2012, 06:36
Hallo zusammen,

ich habe folgende Aufgabenstellung: In einer Tabelle sind in jeder Spalte ca. 100 Formeln, ich muss allerdings in verschiedenen Zellen auch Textwerte eingeben. Da ich je Spalte aber einen Überblick haben möchte wieviele Textwerte und wieviele Formeln in der Spalte hinterlegt sind sollte ich die Anzahl der eingebenen Textwerte oder der Formeln berechnen. Leider habe ich trotz längerer Suche hier keine Lösung gefunden.
Gibts jemanden der mir hier weiterhelfen kann? wäre echt dankbar!

Gruß
Andreas

hary
11.04.2012, 07:10
Moin Andreas
Mein Versuch:

<b><em>Tabelle1</em></b><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "><colgroup><col style="font-weight:bold; width:40px;" /><col style="width:97px;" /><col style="width:97px;" /></colgroup><tr style="background-color:#99CCFF; text-align:center; font-weight:bold; "><td>&nbsp;</td><td>A</td><td>B</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">1</td><td style="color:#000000; background-color:#FFFFFF; ">&nbsp; </td><td style="color:#000000; background-color:#FFFFFF; ">&nbsp; </td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">2</td><td style="color:#000000; background-color:#FFFFFF; ">&nbsp; </td><td style="color:#000000; background-color:#FFFFFF; ">&nbsp; </td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">3</td><td style="color:#000000; background-color:#FFFFFF; text-align:right; " >1</td><td style="color:#000000; background-color:#FFFFFF; text-align:right; " >1</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">4</td><td style="color:#000000; background-color:#FFFFFF; ">&nbsp; </td><td style="color:#000000; background-color:#FFFFFF; ">&nbsp; </td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">5</td><td style="color:#000000; background-color:#FFFFFF; text-align:left; " >aa</td><td style="color:#000000; background-color:#FFFFFF; text-align:left; " >aa</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">6</td><td style="color:#000000; background-color:#FFFFFF; text-align:left; " >Anzahl Text</td><td style="color:#000000; background-color:#FFFFFF; ">&nbsp; </td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">7</td><td style="color:#000000; background-color:#FFFF00; text-align:center; " >1</td><td style="color:#000000; background-color:#FFFFFF; ">&nbsp; </td></tr></table><br/><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "><tr style="background-color:#FFCC66; text-align:center; font-weight:bold; "><td colspan="3" > verwendete Formeln </td></tr><tr valign="top" style="background-color:#FFCC66; text-align:center; font-weight:bold; "><td> Zelle </td><td> Formel </td><td> Bereich </td></tr><tr><td>A1 </td><td>=WENN(B1="";"";B1)</td><td>&nbsp;</td></tr><tr><td>A2 </td><td>=WENN(B2="";"";B2)</td><td>&nbsp;</td></tr><tr><td>A3 </td><td>=WENN(B3="";"";B3)</td><td>&nbsp;</td></tr><tr><td>A4 </td><td>=WENN(B4="";"";B4)</td><td>&nbsp;</td></tr><tr><td>A5 </td><td>=WENN(B5="";"";B5)</td><td>&nbsp;</td></tr><tr><td>A7 </td><td>{=SUMME(ISTTEXT(A1:A5)*(A1:A5&lt;&gt;"")*1)} </td><td>$A$7</td></tr></table><b>{} Matrixformel mit Strg+Umschalt+Enter abschließen</b><br/><br/><a href="http://c.excelhost.de/c_beverly/getfile.php?id=122" onclick="window.open(this.href);return false">Tabellendarstellung in Foren Version 5.30</a><br/><br/>
gruss hary

gizmo0375
11.04.2012, 07:54
Hallo Hary,

vielen Dank für die schnelle Antwort. Leider hat es nicht ganz das gewünschte Ergebnis gebracht. Lag aber wohl an meiner Beschreibung. Die Formeln in der Spalte bringen als Ergebnis auch einen Text und keine Zahl. Deshalb wird mit der Matrixformel sämtliche Zellen mitgezählt die als Formelergebnis auch einen Text bringen.
Gibt es da vielleicht noch eine andere Möglichkeit?
Danke!

Beverly
11.04.2012, 08:06
Hi,

du könntest es mit einer Hilfsspalte lösen:

<b><em>Tabelle1</em></b><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "><colgroup><col style="font-weight:bold; width:40px;" /><col style="width:106px;" /><col style="width:106px;" /><col style="width:106px;" /></colgroup><tr style="background-color:#99CCFF; text-align:center; font-weight:bold; "><td>&nbsp;</td><td>A</td><td>B</td><td>C</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">1</td><td style="color:#000000; background-color:#FFFFFF; text-align:left; " >a</td><td style="color:#000000; background-color:#FFFFFF; text-align:left; " >a</td><td style="color:#000000; background-color:#FFFFFF; text-align:right; " >WAHR</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">2</td><td style="color:#000000; background-color:#FFFFFF; text-align:right; " >10</td><td style="color:#000000; background-color:#FFFFFF; text-align:right; " >10</td><td style="color:#000000; background-color:#FFFFFF; text-align:right; " >WAHR</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">3</td><td style="color:#000000; background-color:#FFFFFF; text-align:left; " >b</td><td style="color:#000000; background-color:#FFFFFF; text-align:left; " >b</td><td style="color:#000000; background-color:#FFFFFF; text-align:right; " >WAHR</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">4</td><td style="color:#000000; background-color:#FFFFFF; ">&nbsp; </td><td style="color:#000000; background-color:#FFFFFF; text-align:right; " >1</td><td style="color:#000000; background-color:#FFFFFF; text-align:right; " >FALSCH</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">5</td><td style="color:#000000; background-color:#FFFFFF; ">&nbsp; </td><td style="color:#000000; background-color:#FFFFFF; text-align:right; " >2</td><td style="color:#000000; background-color:#FFFFFF; text-align:right; " >FALSCH</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">6</td><td style="color:#000000; background-color:#FFFFFF; text-align:left; " >AA</td><td style="color:#000000; background-color:#FFFFFF; text-align:left; " >AA</td><td style="color:#000000; background-color:#FFFFFF; text-align:right; " >WAHR</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">7</td><td style="color:#000000; background-color:#FFFFFF; text-align:left; " >ZZ</td><td style="color:#000000; background-color:#FFFFFF; text-align:left; " >ZZ</td><td style="color:#000000; background-color:#FFFFFF; text-align:right; " >WAHR</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">8</td><td style="color:#000000; background-color:#FFFFFF; text-align:right; " >33</td><td style="color:#000000; background-color:#FFFFFF; text-align:right; " >33</td><td style="color:#000000; background-color:#FFFFFF; text-align:right; " >WAHR</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">9</td><td style="color:#000000; background-color:#FFFFFF; ">&nbsp; </td><td style="color:#000000; background-color:#FFFFFF; text-align:right; " >3</td><td style="color:#000000; background-color:#FFFFFF; text-align:right; " >FALSCH</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">10</td><td style="color:#000000; background-color:#FFFFFF; ">&nbsp; </td><td style="color:#000000; background-color:#FFFFFF; text-align:right; " >4</td><td style="color:#000000; background-color:#FFFFFF; text-align:right; " >FALSCH</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">11</td><td style="color:#000000; background-color:#FFFFFF; text-align:right; " >7</td><td style="color:#000000; background-color:#FFFFFF; text-align:right; " >7</td><td style="color:#000000; background-color:#FFFFFF; text-align:right; " >WAHR</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">12</td><td style="color:#000000; background-color:#FFFFFF; text-align:left; " >D</td><td style="color:#000000; background-color:#FFFFFF; text-align:left; " >D</td><td style="color:#000000; background-color:#FFFFFF; text-align:right; " >WAHR</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">13</td><td style="color:#000000; background-color:#FFFFFF; text-align:left; " >F</td><td style="color:#000000; background-color:#FFFFFF; text-align:left; " >F</td><td style="color:#000000; background-color:#FFFFFF; text-align:right; " >WAHR</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">14</td><td style="color:#000000; background-color:#FFFFFF; ">&nbsp; </td><td style="color:#000000; background-color:#FFFFFF; ">&nbsp; </td><td style="color:#000000; background-color:#FFFF00; text-align:right; " >9</td></tr></table><br/><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "><tr style="background-color:#FFCC66; text-align:center; font-weight:bold; "><td colspan="3" > verwendete Formeln </td></tr><tr valign="top" style="background-color:#FFCC66; text-align:center; font-weight:bold; "><td> Zelle </td><td> Formel </td><td> Bereich </td></tr><tr><td>B1 </td><td>=A1</td><td>&nbsp;</td></tr><tr><td>C1 </td><td>=Formelzelle</td><td>&nbsp;</td></tr><tr><td>B2 </td><td>=A2</td><td>&nbsp;</td></tr><tr><td>C2 </td><td>=Formelzelle</td><td>&nbsp;</td></tr><tr><td>B3 </td><td>=A3</td><td>&nbsp;</td></tr><tr><td>C3 </td><td>=Formelzelle</td><td>&nbsp;</td></tr><tr><td>C4 </td><td>=Formelzelle</td><td>&nbsp;</td></tr><tr><td>C5 </td><td>=Formelzelle</td><td>&nbsp;</td></tr><tr><td>B6 </td><td>=A6</td><td>&nbsp;</td></tr><tr><td>C6 </td><td>=Formelzelle</td><td>&nbsp;</td></tr><tr><td>B7 </td><td>=A7</td><td>&nbsp;</td></tr><tr><td>C7 </td><td>=Formelzelle</td><td>&nbsp;</td></tr><tr><td>B8 </td><td>=A8</td><td>&nbsp;</td></tr><tr><td>C8 </td><td>=Formelzelle</td><td>&nbsp;</td></tr><tr><td>C9 </td><td>=Formelzelle</td><td>&nbsp;</td></tr><tr><td>C10 </td><td>=Formelzelle</td><td>&nbsp;</td></tr><tr><td>B11 </td><td>=A11</td><td>&nbsp;</td></tr><tr><td>C11 </td><td>=Formelzelle</td><td>&nbsp;</td></tr><tr><td>B12 </td><td>=A12</td><td>&nbsp;</td></tr><tr><td>C12 </td><td>=Formelzelle</td><td>&nbsp;</td></tr><tr><td>B13 </td><td>=A13</td><td>&nbsp;</td></tr><tr><td>C13 </td><td>=Formelzelle</td><td>&nbsp;</td></tr><tr><td>C14 </td><td>=ZÄHLENWENN(C1:C13;WAHR)</td><td>&nbsp;</td></tr></table><br/><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "><tr style="background-color:#FF99FF; text-align:center; font-weight:bold; "><td colspan="4" > definierte Namen </td></tr><tr valign="top" style="background-color:#FF99FF; text-align:center; font-weight:bold; "><td> Name </td><td> Bezieht sich auf </td><td> Tabelle </td><td> Z1S1-Formel </td></tr><tr><td>Formelzelle</td><td>=ZELLE.ZUORDNEN(48;INDIREKT("ZS(-1)";0))</td><td>&nbsp;</td><td>=ZELLE.ZUORDNEN(48;INDIREKT("ZS(-1)";0))</td></tr></table><br/><a href="http://excel-inn.de/dateien/vba_beispiele/tabellenanzeige_in_html_addin.zip" onclick="window.open(this.href);return false">Tabellendarstellung in Foren Version 5.48</a><br/><br/>
<hr width="20%" align="left"><img src="http://excel-inn.de/images/grusz.gif" height=35" align="left" alt="Grußformel"><a href="http://excel-inn.de/" onclick="window.open(this.href);return false"><img border="0" src="http://excel-inn.de/images/logo1.gif" height=35" align="middle" alt="Beverly's Excel - Inn"></a>

hary
11.04.2012, 08:12
Hallo
hier mal eine UDF.
Code in ein Modul. Aufgerufen benutzerdefinierte Function.
Option Explicit
Function nurtext(Bereich As Range)
Dim rng As Range
Dim zaehler As Long
For Each rng In Bereich
If Not rng.HasFormula And Not IsNumeric(rng) Then
zaehler = zaehler + 1
End If
Next
nurtext = zaehler
End Function
so sieht es aus:

<b><em>Tabelle1</em></b><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "><colgroup><col style="font-weight:bold; width:40px;" /><col style="width:97px;" /><col style="width:97px;" /></colgroup><tr style="background-color:#99CCFF; text-align:center; font-weight:bold; "><td>&nbsp;</td><td>A</td><td>B</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">1</td><td style="color:#000000; background-color:#FFFFFF; text-align:left; " >eee</td><td style="color:#000000; background-color:#FFFFFF; ">&nbsp; </td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">2</td><td style="color:#000000; background-color:#FFFFFF; text-align:left; " >dd</td><td style="color:#000000; background-color:#FFFFFF; text-align:left; " >dd</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">3</td><td style="color:#000000; background-color:#FFFFFF; text-align:right; " >1</td><td style="color:#000000; background-color:#FFFFFF; text-align:right; " >1</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">4</td><td style="color:#000000; background-color:#FFFFFF; text-align:left; " >aa</td><td style="color:#000000; background-color:#FFFFFF; text-align:left; " >aa</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">5</td><td style="color:#000000; background-color:#FFFFFF; text-align:left; " >bb</td><td style="color:#000000; background-color:#FFFFFF; text-align:left; " >aa</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">6</td><td style="color:#000000; background-color:#FFFFFF; text-align:left; " >Anzahl Text</td><td style="color:#000000; background-color:#FFFFFF; ">&nbsp; </td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">7</td><td style="color:#000000; background-color:#FFFFFF; text-align:right; " >2</td><td style="color:#000000; background-color:#FFFFFF; ">&nbsp; </td></tr></table><br/><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "><tr style="background-color:#FFCC66; text-align:center; font-weight:bold; "><td colspan="3" > verwendete Formeln </td></tr><tr valign="top" style="background-color:#FFCC66; text-align:center; font-weight:bold; "><td> Zelle </td><td> Formel </td><td> Bereich </td></tr><tr><td>A2 </td><td>=WENN(B2="";"";B2)</td><td>&nbsp;</td></tr><tr><td>A3 </td><td>=WENN(B3="";"";B3)</td><td>&nbsp;</td></tr><tr><td>A4 </td><td>=WENN(B4="";"";B4)</td><td>&nbsp;</td></tr><tr><td>A7 </td><td>=nurtext(A1:A5)</td><td>&nbsp;</td></tr></table><br/><a href="http://c.excelhost.de/c_beverly/getfile.php?id=122" onclick="window.open(this.href);return false">Tabellendarstellung in Foren Version 5.30</a><br/><br/>
gruss hary

gizmo0375
11.04.2012, 09:23
Ein Traum! hat funktioniert, vielen Dank für die schnelle und äusserst hilfreiche Unterstützung!
Gruß
Andreas