Dieses Forum nutzt Cookies
Dieses Forum verwendet Cookies, um deine Login-Informationen zu speichern, wenn du registriert bist, und deinen letzten Besuch, wenn du es nicht bist. Cookies sind kleine Textdokumente, die auf deinem Computer gespeichert werden. Die von diesem Forum gesetzten Cookies werden nur auf dieser Website verwendet und stellen kein Sicherheitsrisiko dar. Cookies aus diesem Forum speichern auch die spezifischen Themen, die du gelesen hast und wann du zum letzten Mal gelesen hast. Bitte bestätige, ob du diese Cookies akzeptierst oder ablehnst.

Ein Cookie wird in deinem Browser unabhängig von der Wahl gespeichert, um zu verhindern, dass dir diese Frage erneut gestellt wird. Du kannst deine Cookie-Einstellungen jederzeit über den Link in der Fußzeile ändern.

Summeprodukt / Zelleninhalt kopieren wenn
#1
Photo 
Hallo,

habe zwei Fragen/Probleme. Da ich öfter einen sverweis machen muss, habe ich versucht das etwas zu vereinfachen für mich, habe aber zwei Probleme.
Zum einen zähle ich mit Summeprodukt in der Splate H4 alle Zellen die einen Inhalt haben in Spalte C (der Inhalt dort kommt von einem sverweis) und vergleiche das dann per bedingte Formatierung mit dem Wert der in der Spalte F4 steht. Das Problem ist, sobald mir der Sverweis in Spalte C ein #NV zurück gibt, steht in der Spalte H4 dann das #NV eigentlich will ich aber das dort in dem Fall unten 897 steht, also das #NV nicht mitgezählt. Wie könnte ich das anders lösen?

Zweites Problem, ich hätte gerne, das mir Excel alle Spalten die ein #NV oder Falsch enthalten rechts rüber kopiert, so wie ich es hier per Hand gemacht habe mit den Einträgen C2121 und C2103, geht das irgendwie? habe hier übehraupt keine Ahnung wie ich da ansetzen soll.

Danke und  Gruß
Frankie


[url=
Dateiupload bitte im Forum! So geht es: Klick mich!
]


Angehängte Dateien
.xlsx   summewenn-zellenopieren.xlsx (Größe: 78,29 KB / Downloads: 4)
Antworten Top
#2
Hi Frankie,


Zitat:Das Problem ist, sobald mir der Sverweis in Spalte C ein #NV zurück gibt, steht in der Spalte H4 dann das #NV eigentlich will ich aber das dort in dem Fall unten 897 steht, also das #NV nicht mitgezählt.

schreibe in H4: =ANZAHL(C:C)

Wieso hast du deine Formel in F4 als Matrixformel ( {} ) ausgelegt? Das ist doch unnötig.
Gruß Günter
Jeder Fehler erscheint unglaublich dumm, wenn andere ihn begehen.
angebl. von Georg Christoph Lichtenberg (1742-1799)
Antworten Top
#3
Hi Frankie,

ich glaube, das wolltest du:

2587734

ABCDEFGHIJKLMNOP
3StücklisteProgrammWAHRFALSCHNV
4BU570018507841850784WAHR89889789611
5BU610017494471749447WAHR
6C101113700651370065WAHR
7C101214727551472755WAHR
8C170413061211306121WAHRC210313600861235638FALSCH
9C210013600861360086WAHRC21211360086#NV#NV
10C210113600861360086WAHR
11C210213600861360086WAHR
12C210313600861235638FALSCH12
13C210413700651370065WAHR
14C210513951221395122WAHR
15C210613951221395122WAHR
Formeln der Tabelle
ZelleFormel
C4=SVERWEIS(A4;Tabelle1!$L:$Q;6;FALSCH)
D4=IDENTISCH(C4;B4)
E4=WENN(ISTFEHLER(IDENTISCH(C4;B4));ZEILE();WENN(IDENTISCH(C4;B4)=FALSCH;ZEILE();""))
H4=ZÄHLENWENN($B:$B;">1")
J4=ANZAHL(C:C)
L4=ZÄHLENWENN($D:$D;Wahr)
N4=ZÄHLENWENN($D:$D;Falsch)
P4=ZÄHLENWENN($D:$D;#NV)
C5=SVERWEIS(A5;Tabelle1!$L:$Q;6;FALSCH)
D5=IDENTISCH(C5;B5)
E5=WENN(ISTFEHLER(IDENTISCH(C5;B5));ZEILE();WENN(IDENTISCH(C5;B5)=FALSCH;ZEILE();""))
C6=SVERWEIS(A6;Tabelle1!$L:$Q;6;FALSCH)
D6=IDENTISCH(C6;B6)
E6=WENN(ISTFEHLER(IDENTISCH(C6;B6));ZEILE();WENN(IDENTISCH(C6;B6)=FALSCH;ZEILE();""))
C7=SVERWEIS(A7;Tabelle1!$L:$Q;6;FALSCH)
D7=IDENTISCH(C7;B7)
E7=WENN(ISTFEHLER(IDENTISCH(C7;B7));ZEILE();WENN(IDENTISCH(C7;B7)=FALSCH;ZEILE();""))
C8=SVERWEIS(A8;Tabelle1!$L:$Q;6;FALSCH)
D8=IDENTISCH(C8;B8)
E8=WENN(ISTFEHLER(IDENTISCH(C8;B8));ZEILE();WENN(IDENTISCH(C8;B8)=FALSCH;ZEILE();""))
M8=INDEX(A:A;KKLEINSTE(E:E;ZEILE(A1)))
N8=INDEX(B:B;KKLEINSTE(E:E;ZEILE(A1)))
O8=INDEX(C:C;KKLEINSTE(E:E;ZEILE(A1)))
P8=INDEX(D:D;KKLEINSTE(E:E;ZEILE(A1)))
C9=SVERWEIS(A9;Tabelle1!$L:$Q;6;FALSCH)
D9=IDENTISCH(C9;B9)
E9=WENN(ISTFEHLER(IDENTISCH(C9;B9));ZEILE();WENN(IDENTISCH(C9;B9)=FALSCH;ZEILE();""))
M9=INDEX(A:A;KKLEINSTE(E:E;ZEILE(A2)))
N9=INDEX(B:B;KKLEINSTE(E:E;ZEILE(A2)))
O9=INDEX(C:C;KKLEINSTE(E:E;ZEILE(A2)))
P9=INDEX(D:D;KKLEINSTE(E:E;ZEILE(A2)))
C10=SVERWEIS(A10;Tabelle1!$L:$Q;6;FALSCH)
D10=IDENTISCH(C10;B10)
E10=WENN(ISTFEHLER(IDENTISCH(C10;B10));ZEILE();WENN(IDENTISCH(C10;B10)=FALSCH;ZEILE();""))
C11=SVERWEIS(A11;Tabelle1!$L:$Q;6;FALSCH)
D11=IDENTISCH(C11;B11)
E11=WENN(ISTFEHLER(IDENTISCH(C11;B11));ZEILE();WENN(IDENTISCH(C11;B11)=FALSCH;ZEILE();""))
G11=WENN(ODER(ISTNV(D11);D11=FALSCH);ZEILE();"")
C12=SVERWEIS(A12;Tabelle1!$L:$Q;6;FALSCH)
D12=IDENTISCH(C12;B12)
E12=WENN(ISTFEHLER(IDENTISCH(C12;B12));ZEILE();WENN(IDENTISCH(C12;B12)=FALSCH;ZEILE();""))
C13=SVERWEIS(A13;Tabelle1!$L:$Q;6;FALSCH)
D13=IDENTISCH(C13;B13)
E13=WENN(ISTFEHLER(IDENTISCH(C13;B13));ZEILE();WENN(IDENTISCH(C13;B13)=FALSCH;ZEILE();""))
N13=WENN(ISTZAHL(KKLEINSTE(F:F;ZEILE(A3)));KKLEINSTE(F:F;ZEILE(A3));"")
C14=SVERWEIS(A14;Tabelle1!$L:$Q;6;FALSCH)
D14=IDENTISCH(C14;B14)
E14=WENN(ISTFEHLER(IDENTISCH(C14;B14));ZEILE();WENN(IDENTISCH(C14;B14)=FALSCH;ZEILE();""))
N14=WENN(ISTZAHL(KKLEINSTE(F:F;ZEILE(A4)));KKLEINSTE(F:F;ZEILE(A4));"")
C15=SVERWEIS(A15;Tabelle1!$L:$Q;6;FALSCH)
D15=IDENTISCH(C15;B15)
E15=WENN(ISTFEHLER(IDENTISCH(C15;B15));ZEILE();WENN(IDENTISCH(C15;B15)=FALSCH;ZEILE();""))
N15=WENN(ISTZAHL(KKLEINSTE(F:F;ZEILE(A5)));KKLEINSTE(F:F;ZEILE(A5));"")

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Du musst lediglich die Hilfsspalte E anlegen, die du ja ausblenden kannst. Dann kannst du dir sicherlich die bedingte Formatierung sparen.
Gruß Günter
Jeder Fehler erscheint unglaublich dumm, wenn andere ihn begehen.
angebl. von Georg Christoph Lichtenberg (1742-1799)
Antworten Top
#4
Hallo,

danke für die Vorschläge, habe mal soweit alles umgesetzt und komme langsam dahin wo hin sein will :). Die Matrixformeln haben keinen besonderen Grund, einfach Standartmäßig.

Jetzt ist bei mir aber noch eine Frage aufgekommen. Kann man Formeln automatisch in die nächste Zeile ziehen lassen. Also wenn eine Formel in Spalte K1 ein Ergebniss ausgibt (z.b ein sverweis) möchte ich, dass Excel die Formel automatisch in die Spalte K2 zieht, solange bis eben die Formel kein Ergebniss mehr ausgibt. Und ich will das nicht mit VBA machen, habe jetzt etwas mit "Wenn" dann "Index" rumgespielt, aber das funktioniert alles nicht. Und bevor ich weitere 2 Stunden im Nichts versenke, frage ich lieber mal :).
Hintergendanke ist, dass ich nicht vorher die Formel per Hand nach unten ziehen will, da ich nicht weiß wieviele Spalten benötigt werden und das ganze dann zu aufgebläht ist.

Danke!
Gruß Frankie
Antworten Top
#5
Hi Frankie,

so ganz verstehe ich dein jetziges Anliegen nicht.


Zitat:Hintergendanke ist, dass ich nicht vorher die Formel per Hand nach unten ziehen will, da ich nicht weiß wieviele Spalten benötigt werden und das ganze dann zu aufgebläht ist.

Die Formeln zum Übertragen der fehlerhaften Daten kannst du doch einfach mal ein paar hundert oder tausend Zeilen runterziehen. Da das keine Matrixformeln sind, dürfte sich das in der Performance nicht sehr bemerkbar machen.

Bau einfach noch eine Fehlerabrage herum, dann bekommst du kein #ZAHL!


Code:
=WENN(ISTFEHL(INDEX(A:A;KKLEINSTE($E:$E;ZEILE($A1))));"";INDEX(A:A;KKLEINSTE($E:$E;ZEILE($A1))))
Gruß Günter
Jeder Fehler erscheint unglaublich dumm, wenn andere ihn begehen.
angebl. von Georg Christoph Lichtenberg (1742-1799)
Antworten Top
#6
Hallo,

Zitat:Kann man Formeln automatisch in die nächste Zeile ziehen lassen.

ich würde daraus eine Tabelle erstellen (in XL2010 über Einfügen - Tabelle) und dann sollten die Formeln automatisch mitgezogen werden.
Gruß
Peter
Antworten Top
#7
(21.06.2015, 11:40)WillWissen schrieb: Hi Frankie,

so ganz verstehe ich dein jetziges Anliegen nicht.



Zitat:Hintergendanke ist, dass ich nicht vorher die Formel per Hand nach unten ziehen will, da ich nicht weiß wieviele Spalten benötigt werden und das ganze dann zu aufgebläht ist.

Die Formeln zum Übertragen der fehlerhaften Daten kannst du doch einfach mal ein paar hundert oder tausend Zeilen runterziehen. Da das keine Matrixformeln sind, dürfte sich das in der Performance nicht sehr bemerkbar machen.

Bau einfach noch eine Fehlerabrage herum, dann bekommst du kein #ZAHL!



Code:
=WENN(ISTFEHL(INDEX(A:A;KKLEINSTE($E:$E;ZEILE($A1))));"";INDEX(A:A;KKLEINSTE($E:$E;ZEILE($A1))))

Hallo,

ich habe den Header mit den Formeln also drei Zeilen als Kopie immer in einer Ablage, ich mach also eine Stückliste auf, kopiere mir den Header dann per Tastenkombi rein und mache den Abgleich und es ist einfach nicht schön, wenn ich dann da eine Kopie mit 5000 Zeilen drin liegen habe, wovon ich in 80% der Fälle nur 3 brauche.
Antworten Top
#8
(21.06.2015, 11:59)Frankie schrieb: ich habe den Header mit den Formeln also drei Zeilen als Kopie immer in einer Ablage, ich mach also eine Stückliste auf, kopiere mir den Header dann per Tastenkombi rein und mache den Abgleich und es ist einfach nicht schön, wenn ich dann da eine Kopie mit 5000 Zeilen drin liegen habe, wovon ich in 80% der Fälle nur 3 brauche.

Zeige uns doch einmal, wie dein Original aussieht. Anonymisiere sensible Daten. Es reichen pro Tabellenblatt einige wenige Zeilen. Du musst also keine Mega-Datei einstellen.
Gruß Günter
Jeder Fehler erscheint unglaublich dumm, wenn andere ihn begehen.
angebl. von Georg Christoph Lichtenberg (1742-1799)
Antworten Top
#9
Hallo Frankie,

Du könntest, um bei der Variante ohne VBA zu bleiben, die benötigten Zeilen mit einer geeigneten Formel zählen und danach dann Deine Tabelle aufziehen. Wenn Du z.B. den Vorschlag von Peter nutzt, gibst Du dann einfach die Zeilennummer beim Erstellen ein.

Ich habe hier mal in Spalte E auf verschiedene Weise die Anzahl der Zeilen berechnet.

Arbeitsblatt mit dem Namen 'Tabelle1'
EFG
13 1
25 2
34 3
4 A
5

ZelleFormel
E1=ANZAHL(G:G)
E2=ANZAHL2(G:G)
E3=SUMMENPRODUKT(--(G:G<>""))
G5=""
Diese Tabelle wurde mit Tab2Html (v2.5.0) erstellt. ©Gerd alias Bamberg
.      \\\|///      Hoffe, geholfen zu haben.
       ( ô ô )      Grüße, André aus G in T  
  ooO-(_)-Ooo    (Excel 97-2019+365)
Antworten Top
#10
Hallo,

sorry für die lange Antwortzeit, war die Woche nicht da, danke für eure Antworten. Habe mich heute von der Thematik mit den Formeln runter ziehen auch verabschiedet. Da ich mir die Stückliste jetzt mit vba direkt in das active sheet importiere, die rumkopiererei mit dem header war doch irgendwie nicht das gelbe vom Ei. Daher hab ich jetzt den Gedanken von WillWissen umgesetzt und lass die Formeln einfach nen gutes Stück runter ziehen.

Bin heute aber auf ein weiteres Problem gestossen, habe den sverweis ebenfalls automatisiert und wollte am Ende mit  Selection.AutoFill  arbeiten, wenn ich nur die Zellen als Destination angebe geht es auch, aber sobald ich die variable lz einbaue kommt der debugger, sieht da einer den Syntaxfehler?




Sub DateiOeffnen()

  Dim Filename As String
  Dim Pfad As String
 
 
  ChDrive ("C:\")
  ChDir ("C:\DVD\")

  Filename = Application.GetOpenFilename
  If Filename <> "Falsch" Then
    Filename = Dir(Filename)
    Pfad = CurDir
 
    Range("C2").Select
     ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],'" & Pfad & "\[" & Filename & "]Prg'!c12:c17,6,FALSE)"

Application.CutCopyMode = False
lz = Cells(Rows.Count, 1).End(xlUp).Rows.Row
Selection.AutoFill Destination:=Range(.Cells(2, 3), .Cells(lz, 3)), Type:=xlFillDefault

    
  End If
End Sub




GRuß
Antworten Top


Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste