Excel S-Verweis.. wie kann ich #NV entfernen?
Ich habe eine Excel Datei über mehreren hunderten spalten sowie mehreren tausenden zellen... ich habe für jede spalte einen separaten sverweis machen müssen.. so jetzt habe ich sehr oft im Feld #NV stehen... ich möchte das diese komplett leer stehen... Wie kann ich auf einmal alle #nv´s löschen ????
Danke vorab für die antwort.
2 Antworten
=WENNFEHLER(SVERWEIS("a";A1:B10;2;0);"")
Falls Du noch Excel 2003 hast, melde Dich, da ist es etwas komplizierter.
also ich verstehe nicht, was Du mit dem Suchen anfangen willst?
Aber der Frager hat sich ohnehin nicht mehr gemeldet.
Ich hab meine AW nur deshalb noch berichtigt, damit nichts Falsches stehenbleibt!
Man kann bei SUCHEN unter Optionen "Werte" einstellen, statt Formeln. Das geht bei ERSETZEN - komischer Weise - NICHT.
Mit dieser Option wird #NV als Formelergebnis gefunden und man kann sich alle Funde gleichzeitig anzeigen lassen und dann diese Werte mit ENTF löschen.
ein Makro könnte das lösen, mal auf die Schnelle (noch ungetestet, sollte für alle Versionen gelten: im Ggs zu Wennfehler ist auf diese Art und Weise die spezifischere Abfrage istNV() nutzbar):
Sub FormelFehlerFreundlichGestalten()
Dim c
For each c in selection
c.formula = "=wenn(istNV(" & mid(c.formulalocal, 2, 99) & ");""-"";" & mid(c.formulalocal, 2, 99) & ")"
next
end sub
Wähle unbedingt erst mal eiine Zeile aus (oder die ersten SVerweisformeln da drin, aber vorsichtshalber mindestens 2) und prüfe das Ergebnis, mach dann den Rest der Zeile und dann die restlichen Zeilen, aber nur die Bereiche, die den SVerweis enthalten, falls es da Anderes gäbe.
Wird bei so vielen Zellen allerdings längere Zeit dauern (zB über Nacht laufen lassen, wenn positiv getestet)
Wenns nicht geht, melde Dich wieder mit einer genauen Beschreibung des Ergebnisses bzw evtl Fehlermeldungen! (Weiß nicht, ob ich heute selber noch zum Testen komme!)
konnte es doch grad testen: ganz links muss es ebenfalls heissen:
c.formulalocal = (ich schreibs nochmal von vorne):
Sub FormelFehlerFreundlichGestalten()Dim cFor each c in selectionc.formulalocal = "=wenn(istNV(" & mid(c.formulalocal, 2, 99) & ");""-"";" & mid(c.formulalocal, 2, 99) & ")"nextend sub
Bei mir funktioniert es, ich hoffe, bei Dir auch!
Makro einfügen: Alt+F11, Menü einfügen, Modul Dahinein unter ggf vorhandene Einträge kopieren
Das Ersatzzeichen kannst du natürlich frei wählen, zB auch den Saddy: ☹ anstelle des -
stelle heute erst fest, dass da sämtliche Umbrüche verlorengegangen sind!(Sch...-Editor, trotz aller Nach- Verschlimmbesserungen!)
Sub FormelFehlerFreundlichGestalten()
Dim c
For each c in selection
c.formulalocal = "=wenn(istNV(" & _
mid(c.formulalocal, 2, 99) & ");""-"";" & _
mid(c.formulalocal, 2, 99) & ")"
next
end sub
hoffe, dass das jetzt richtig rüberkommt!
Ich hab übrigens nicht die Einbettung in Wennfehler gewählt (wäre einfacher gewesen), damit es 1. auch in früheren Versionen funktioniert und -wahrscheinlich wichtiger- andere Fehler als #NV! nicht verschleiert werden.
(istNV ist m.W. leider die einzige spezifische Fehler-Erfassung in xl-Formeln.)
"Das Ersatzzeichen kannst du natürlich frei wählen, zB auch den Saddy: ☹ anstelle des -" : gas geht aber nicht im Makro selbst, sondern erst nachträglich. Da aber - (minus als Text) ein sehr unspezifisches Zeichen ist, das auch -aber nicht nur- in Formeln vorkommt, ist es in VBA evtl besser, das - (minus als Bindestrich-Text) durch eine andere seltene (exotische) Folge im Makro zu ersetzen, die in VBA-Texten akzeptiert wird, Beispiel æ³.
musste ich auch erst ausprobieren: der Saddy wird als ? dargestellt,während æ³ richtig so wiedergegeben wird. Es ginge aber genausogut "°²³" oder "!" etc, eben sehr unwahrscheinliche Folgen, die man später auch problemlos ersetzen kann, wenn man will (ggf auch von vornherein Leerzeichen, da geht aber dann später nichts mehr!!)
Ich habe da noch einen etwa komplizierten Weg gefunden, wie Du die Formeln, die #NV ergeben mithilfe von "Suchen" (nicht Ersetzen)
löschen kannst.
Falls meine Formel Dir nicht hilft, frage noch mal nach,.