2 Arbeitsblätter, fehlende Werte anzeigen

anon75357393 · 19. Juni 2008 um 02:29

Hi,
ich habe 2 Arbeitsblätter in meiner Exceltabelle. Im ersten Arbeitsblatt werden verschiedene Namen aufgelistet (Spalte A) und mit der dazugehörigen Häufigkeit auf dem Arbeitsblatt 2 angezeigt.

Gelöst habe ich das über eine ein Summenprodukt und einer Wenn-Abfrage multipliziert mit 1. Meine erste Frage ist nun, ob es vielleicht auch leichere Befehle gibt? (Einfach nur ob ich ein Befehl nicht kenne).

Wichtiger wäre für mich eine Anzeige im 1. Arbeitsblatt, welche Wörter aus dem 2. Arbeitsblatt nicht im 1. Arbeitsblatt vorkommt. Wie kann man das machen? Lösungen ohne VBA sind bevorzugt.

Vielen Dank
DuAK007

P.S.: Ich verwende Office 2007. Die Tabellen würden so aussehen:

Arbeitsblatt
================

Namen Häufigkeit fehlend: Name4
Name1 3
Name2 1
Name3 2

Arbeitsblatt
================
Name3 Name3 Name1
Name2 Name1 Name4
Name1

Reinhard_fb8cb7 · 19. Juni 2008 um 11:30

Wichtiger wäre für mich eine Anzeige im 1. Arbeitsblatt,
welche Wörter aus dem 2. Arbeitsblatt nicht im 1. Arbeitsblatt
vorkommt. Wie kann man das machen? Lösungen ohne VBA sind
bevorzugt.

Hi Duak,

nicht ganz das was du wolltest, vielleicht hilfts dir ja weiter.
Hilfsspalten D und E kannst du ja ausblenden oder auf ein anderes Blatt verlagern.

Tabellenblatt: [Mappe1]!Tabelle2
 │ A │ B │ C │
──┼───┼───┼───┤
1 │ a │ c │ c │
──┼───┼───┼───┤
2 │ c │ d │ │
──┼───┼───┼───┤
3 │ │ e │ a │
──┴───┴───┴───┘




Tabellenblatt: [Mappe1]!Tabelle1
 │ A │ B │ C │ D │ E │
──┼──────┼────────┼─────────┼─────┼───┤
1 │ Name │ Häufig │ fehlend │ │ │
──┼──────┼────────┼─────────┼─────┼───┤
2 │ a │ 2 │ b │ │ │
──┼──────┼────────┼─────────┼─────┼───┤
3 │ b │ │ f │ 997 │ b │
──┼──────┼────────┼─────────┼─────┼───┤
4 │ c │ 3 │ │ │ │
──┼──────┼────────┼─────────┼─────┼───┤
5 │ d │ 1 │ │ │ │
──┼──────┼────────┼─────────┼─────┼───┤
6 │ e │ 1 │ │ │ │
──┼──────┼────────┼─────────┼─────┼───┤
7 │ f │ │ │ 993 │ f │
──┴──────┴────────┴─────────┴─────┴───┘
Benutzte Formeln:
B2: =ZÄHLENWENN(Tabelle2!$A:blush:C;A2)
B3: =ZÄHLENWENN(Tabelle2!$A:blush:C;A3)
B4: =ZÄHLENWENN(Tabelle2!$A:blush:C;A4)
B5: =ZÄHLENWENN(Tabelle2!$A:blush:C;A5)
B6: =ZÄHLENWENN(Tabelle2!$A:blush:C;A6)
B7: =ZÄHLENWENN(Tabelle2!$A:blush:C;A7)
C2: =SVERWEIS(KGRÖSSTE(D:smiley:;ZEILE()-1);D:E;2;0)
C3: =SVERWEIS(KGRÖSSTE(D:smiley:;ZEILE()-1);D:E;2;0)
C4: =SVERWEIS(KGRÖSSTE(D:smiley:;ZEILE()-1);D:E;2;0)
C5: =SVERWEIS(KGRÖSSTE(D:smiley:;ZEILE()-1);D:E;2;0)
C6: =SVERWEIS(KGRÖSSTE(D:smiley:;ZEILE()-1);D:E;2;0)
C7: =SVERWEIS(KGRÖSSTE(D:smiley:;ZEILE()-1);D:E;2;0)
D1: =(1000-ZEILE())\*(E1"")
D2: =(1000-ZEILE())\*(E2"")
D3: =(1000-ZEILE())\*(E3"")
D4: =(1000-ZEILE())\*(E4"")
D5: =(1000-ZEILE())\*(E5"")
D6: =(1000-ZEILE())\*(E6"")
D7: =(1000-ZEILE())\*(E7"")
E1: =WENN(B1=0;A1;"")
E2: =WENN(B2=0;A2;"")
E3: =WENN(B3=0;A3;"")
E4: =WENN(B4=0;A4;"")
E5: =WENN(B5=0;A5;"")
E6: =WENN(B6=0;A6;"")
E7: =WENN(B7=0;A7;"")

A1:E7
haben das Zahlenformat: Standard

Tabellendarstellung erreicht mit dem Code in FAQ:2363

Gruß
Reinhard

anon75357393 · 19. Juni 2008 um 14:02

nicht ganz das was du wolltest, vielleicht hilfts dir ja
weiter.

Ich werde es mir mal ansehen

Benutzte Formeln:
B2: =ZÄHLENWENN(Tabelle2!$A:blush:C;A2)

Diese kann ich nicht übernehmen. Hab wohl vergessen zu sagen, dass ich die Funktion Glätten mit benutzen muss. Denn manchmal sind Leerzeichen oder ähnliche Zeichen hinter den Namen, die dadurch entfernt werden. Ich habe folgenden Code in C1:
=KALENDERWOCHE(JETZT()-3/96-7*(SPALTE()-3))

Und in C2:
=SUMMENPRODUKT((GLÄTTEN($A2)=GLÄTTEN(INDIREKT("’"&JAHR(JETZT()-3/96-SUMMENPRODUKT(SPALTE()-2;7))&"-"&C$1&"’!$A$2:blush:G$200")))*1)

Ich habe also für jede KW ein extra Datenblatt. Und die Namen kopiere ich aus einem Forum in einem Online Spiel, um zu überprüfen ob auch alle entsprechend häufig baden waren um auf meiner begrenzten Freundschaftsliste bleiben zu dürfen…

Und damit kommt auch mein Problem. Ist im Forum ein neuer Name oder einer Name falsch geschrieben, ist es schwierig unter 1000 Einträgen den einen Fehler zu finden. Daher wäre es cool, wenn mir dieser Name angezeigt wird. Bis jetzt konnte ich nur über die Summen feststellen, dass eine Eingabe fehlt. Aber nicht welche:
=SUMME(C2:C100)&" / „&ANZAHL2(INDIREKT(“’"&JAHR(HEUTE()-1-SUMMENPRODUKT(SPALTE()-2;7))&"-"&C$1&"’!$A$2:blush:G$200"))

Die Namen sind also nicht in Tabelle1 vorhanden.

Gruß DuAK007

anon75357393 · 19. Juni 2008 um 15:51

Nachtrag
So hab jetzt etwas meine Tabelle geändert. So steht jetzt in C1 der Tabellenname und in C2 steht
=SUMMENPRODUKT((GLÄTTEN($A2)=GLÄTTEN(INDIREKT("’"&C$1&"’!$A$2:blush:G$200")))*1)

Das macht die Sache etwas übersichtlicher. Wobei in der ausgelagerten Tabelle es wie gesagt eine Titelleiste gibt, die nicht mit dazu gezählt werden sollte.

Würde jetzt gerne in der Tabelle1 anzeigen lassen, welche Einträge in Tabelle2 vorhanden sind aber nicht in Tabelle1 aufgelistet wurden. Dabei soll das Glätten integriert sein.

Gruß DuAK007

Reinhard_fb8cb7 · 19. Juni 2008 um 16:12

Hi Duak,

nicht ganz das was du wolltest, vielleicht hilfts dir ja
weiter.

Ich werde es mir mal ansehen

scheinbar hast du es dir angeschauen aber es traf nicht dein Wohlwollen

Benutzte Formeln:
B2: =ZÄHLENWENN(Tabelle2!$A:blush:C;A2)

Diese kann ich nicht übernehmen. Hab wohl vergessen zu sagen,
dass ich die Funktion Glätten mit benutzen muss. Denn manchmal

Tja nun, meine Glaskugel ist in Reparatur *gg*

sind Leerzeichen oder ähnliche Zeichen hinter den Namen, die
dadurch entfernt werden. Ich habe folgenden Code in C1:
=KALENDERWOCHE(JETZT()-3/96-7*(SPALTE()-3))

Da hast du schon mal ein Problem. Kalenderwoche rechnet falsch, schau mal hier:

http://excelformeln.de/formeln.html?welcher=7

Und in C2:
=SUMMENPRODUKT((GLÄTTEN($A2)=GLÄTTEN(INDIREKT("’"&JAHR(JETZT()-3/96-SUMMENPRODUKT(SPALTE()-2;7))&"-"&C$1&"’!$A$2:blush:G$200")))*1)

Ich habe also für jede KW ein extra Datenblatt. Und die Namen
kopiere ich aus einem Forum in einem Online Spiel, um zu
überprüfen ob auch alle entsprechend häufig baden waren um auf
meiner begrenzten Freundschaftsliste bleiben zu dürfen…

Donnerlittchen, du brauchst eine Tabellenfunktion um alle die aufzuschreiben die mit dir in die Badewanne hüpfen? Respekt

Und damit kommt auch mein Problem. Ist im Forum ein neuer Name
oder einer Name falsch geschrieben, ist es schwierig unter
1000 Einträgen den einen Fehler zu finden. Daher wäre es cool,
wenn mir dieser Name angezeigt wird. Bis jetzt konnte ich nur
über die Summen feststellen, dass eine Eingabe fehlt. Aber
nicht welche:

Forum ?
Kannst du mal eine kleine Beispielmappe basteln und hochladen bei z.B.
http://www.badango.com o.ä.

Und grundsätzlich wäre das Problem (so 100% hab ichs noch nicht verstanden) mit einem Makro zu lösen was eine benutzerdefinierte Funktion darstellt.

D.h. in z.B. D1:smiley:1000 schreibst du jeweils rein
=Fehlend()
dann siehst du in Spalte D die fehlenden Namen aufgelistet…

Fehlend ist dann die Funktion die du genauso easy wie =Summe(…) benutzen kannst.

Gruß
Reinhard

anon75357393 · 19. Juni 2008 um 17:21

=KALENDERWOCHE(JETZT()-3/96-7*(SPALTE()-3))

Da hast du schon mal ein Problem. Kalenderwoche rechnet
falsch

Das ist mir egal, solange ich es sinnvoll in einem Wochenrhytmus einteilen kann.

Kannst du mal eine kleine Beispielmappe basteln

Ich glaube das würde es zu kompliziert machen, denn das mit Kalenderwochen… ist für mich gar kein Problem. Ich versuche es nochmal zu erklären:

In Tabelle2 werden alle die Namen untereinander eingetragen, jede Spalte für ein Wochentag. Da können natürlich neue Namen dazukommen, Schreibfehler vorkommen oder oder oder.

In Tabelle1 sind dann die bekannten Namen aufgelistet und es wird die Häufigkeit gezählt. Nun will ich in Tabelle1 noch eine Feld haben, wo die fehlenden Namen angezeigt werden.

Dein Lösungsansatz ist, dass in Tabelle2 direkt hinter den Namen angezeigt wird, wer fehlt oder bei welchen Namen ein Fehler auftritt. Und da ist der kleine Unterschied. Bei deiner Variante müsste ich alle Einträge überprüfen und habe nicht eine Zelle wo der Name steht.

anon75357393 · 19. Juni 2008 um 17:59

In Tabelle1 sind dann die bekannten Namen aufgelistet und es
wird die Häufigkeit gezählt. Nun will ich in Tabelle1 noch
eine Feld haben, wo die fehlenden Namen angezeigt werden.

Kann sogar sein, dass ich deine Lösung missverstanden habe. Mir sind die fehlenden Namen zu diesen Zeitpunkt noch nicht bekannt. D.h. sie können noch nicht in der Tabelle1 stehen. Sie müssen erst noch eingefügt werden. Und ich möchte halt wissen, welche Namen fehlen:

Welche® Name(n) fehlt/en in Tabelle1, die aber in Tabelle2 vorkommen?

anon75357393 · 19. Juni 2008 um 18:19

Habe mal eine Beispieldatei hochgeladen:
http://www.badongo.com/file/9966505

Reinhard_fb8cb7 · 19. Juni 2008 um 20:26

Habe mal eine Beispieldatei hochgeladen:
http://www.badongo.com/file/9966505

Hallo Duak,

ich hab aber kein XL2007. Mach bitte ne normale xls draus.

Gruß
Reinhard

anon75357393 · 19. Juni 2008 um 20:50

http://www.badongo.com/file/9968284

Tip: Es gibt doch auch Plug Ins für die Office 2007 Formate.

[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]

Reinhard_fb8cb7 · 19. Juni 2008 um 23:44

Hi Duak,

Tip: Es gibt doch auch Plug Ins für die Office 2007 Formate.

ja, meines Wissens nach gibts da was bei MS um sich auch mit älteren Versionen eine xlsx oder xlsm anzuschauen.
Aber ich sah noch keinen Handlungsbedarf mir das runterzuladen. Für die Anfrager hier ist es ein leichtes ihre Datei als xls und nicht als xlsx abzuspeichern und ggfs. hier hochzuladen. Warum sollten sich denn dann alle potentielle Helfer dieses Tool runterladen wenn es auch anders geht.
Und, danke für den Tip, war zwar in meinem fall unnötig, aber hätte ich das nicht vorher gewußt hätte ich mich sehr über den Tip gefreut.

So, jetzt zum Thema, schreib in F1: =NichtDa()

Vorher Alt+F11, Einfügen–Modul, dortrein den Code, Editor schließen.

Option Explicit
'
Function Nichtda() As String
Dim Zei As Long, Zelle As Range
With Worksheets("Tabelle1")
 For Each Zelle In Worksheets("Tabelle2").Range("A2:E1000")
 If Application.WorksheetFunction.CountIf(.Columns(1), Zelle) = 0 Then
 Nichtda = Nichtda & Zelle & " "
 End If
 Next Zelle
 If Len(Nichtda) \> 0 Then Nichtda = Left(Nichtda, Len(Nichtda) - 1)
End With
End Function

Gruß
Reinhard

anon75357393 · 19. Juni 2008 um 23:59

Hi,
folgende Probleme / Fragen hätte ich zu deinen Code:

Ich möchte auf die Tabelle zugreifen, die in der Zelle C1 der Tabelle1 steht. Geht das auch mit INDIREKT?
Wie kann man die Excelfunktion GLÄTTEN hinzufügen? Weil nur geglättete Wörter verglichen werden sollen.

Ansonsten vielen Dank
DuAK007

Option Explicit
’
Function Nichtda() As String
Dim Zei As Long, Zelle As Range
With Worksheets(„Tabelle1“)
For Each Zelle In Worksheets(„Tabelle2“).Range(„A2:E1000“)
If Application.WorksheetFunction.CountIf(.Columns(1),
Zelle) = 0 Then
Nichtda = Nichtda & Zelle & " "
End If
Next Zelle
If Len(Nichtda) > 0 Then Nichtda = Left(Nichtda,
Len(Nichtda) - 1)
End With
End Function

anon75357393 · 20. Juni 2008 um 00:36

Ich habe nochmal gesucht und jetzt erstmal am Ende meiner Kenntnisse (weil es für mich Neuland ist).

Zu 1.: range(„Zelle“).value scheint zu funktionieren
Zu 2.: Trim funktioniert

Aber 3.: Wie wird die Funtkion automatisch aufgerufen bei Veränderung des Tabelle?

Gruß DuAK007

[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]

Reinhard_fb8cb7 · 20. Juni 2008 um 00:38

Hallo Duak,

folgende Probleme / Fragen hätte ich zu deinen Code:

Ich möchte auf die Tabelle zugreifen, die in der Zelle C1
der Tabelle1 steht. Geht das auch mit INDIREKT?

? in Tabelle1!C1 steht „Prüfsumme“, ist das der Name eines Blattes?

Wie kann man die Excelfunktion GLÄTTEN hinzufügen? Weil nur
geglättete Wörter verglichen werden sollen.

Kannst du da mal eine Beispielmappe hochladen mit ungeglätteten Eintragungen, ich weiß grad nicht wo ich da was tun soll.

Gruß
Reinhard

Ansonsten vielen Dank
DuAK007

Option Explicit
’
Function Nichtda() As String
Dim Zei As Long, Zelle As Range
With Worksheets(„Tabelle1“)
For Each Zelle In Worksheets(„Tabelle2“).Range(„A2:E1000“)
If Application.WorksheetFunction.CountIf(.Columns(1),
Zelle) = 0 Then
Nichtda = Nichtda & Zelle & " "
End If
Next Zelle
If Len(Nichtda) > 0 Then Nichtda = Left(Nichtda,
Len(Nichtda) - 1)
End With
End Function

anon75357393 · 20. Juni 2008 um 00:54

Ich möchte auf die Tabelle zugreifen, die in der Zelle C1
der Tabelle1 steht. Geht das auch mit INDIREKT?

? in Tabelle1!C1 steht „Prüfsumme“, ist das der Name eines
Blattes?

Hm ne, das ist ja nur eine vereinfachte Version. Sagen wir in C1 stünde nicht Prüfsumme sondern „2008-25“, also Jahr und Kalenderwoche. Dann soll dieser Eintrag genommen werden.

Wie kann man die Excelfunktion GLÄTTEN hinzufügen? Weil nur
geglättete Wörter verglichen werden sollen.

Kannst du da mal eine Beispielmappe hochladen mit
ungeglätteten Eintragungen, ich weiß grad nicht wo ich da was
tun soll.

Füge einfach in Tabelle2 hinter einigen Namen ein Leerzeichen ein.
Dann merkst du, was ich meinte. Das benötigt also keine neue Datei.

Reinhard_fb8cb7 · 22. Juni 2008 um 14:22

Hm ne, das ist ja nur eine vereinfachte Version. Sagen wir in
C1 stünde nicht Prüfsumme sondern „2008-25“, also Jahr und
Kalenderwoche. Dann soll dieser Eintrag genommen werden.

Wie kann man die Excelfunktion GLÄTTEN hinzufügen? Weil nur
geglättete Wörter verglichen werden sollen.

Hi Duak,

Tabellenblatt: C:\DOKUME~1\ICHALS~1\LOKALE~1\Temp\[Mappe1.xls]!Tabelle1
 │ A │ B │ C │ D │ E │ F │
──┼───────────┼────────────┼──────────┼─────────┼─────────────────┼──────────────────────────────┤
1 │ Name │ Häufigkeit │ Tabelle2 │ 15 / 19 │ fehlender Name: │ Gustav Fret Fredz z jj │
──┼───────────┼────────────┼──────────┼─────────┼─────────────────┼──────────────────────────────┤
2 │ Fred │ 2 │ │ │ │ │
──┼───────────┼────────────┼──────────┼─────────┼─────────────────┼──────────────────────────────┤
3 │ Manfred │ 3 │ │ │ │ │
──┼───────────┼────────────┼──────────┼─────────┼─────────────────┼──────────────────────────────┤
4 │ Alfred │ 4 │ │ │ │ │
──┼───────────┼────────────┼──────────┼─────────┼─────────────────┼──────────────────────────────┤
5 │ Frettchen │ 2 │ │ │ │ │
──┼───────────┼────────────┼──────────┼─────────┼─────────────────┼──────────────────────────────┤
6 │ Maulwurf │ 4 │ │ │ │ │
──┴───────────┴────────────┴──────────┴─────────┴─────────────────┴──────────────────────────────┘
Benutzte Formeln:
B2: =ZÄHLENWENN(INDIREKT($C$1&"!$A$2:blush:E$6");A2)
B3: =ZÄHLENWENN(INDIREKT($C$1&"!$A$2:blush:E$6");A3)
B4: =ZÄHLENWENN(INDIREKT($C$1&"!$A$2:blush:E$6");A4)
B5: =ZÄHLENWENN(INDIREKT($C$1&"!$A$2:blush:E$6");A5)
B6: =ZÄHLENWENN(INDIREKT($C$1&"!$A$2:blush:E$6");A6)
D1: =SUMME(B2:B6)&" / "&ANZAHL2(Tabelle2!A2:E6)+JETZT()\*0
F1: =nichtda()





Tabellenblatt: C:\DOKUME~1\ICHALS~1\LOKALE~1\Temp\[Mappe1.xls]!Tabelle2
 │ A │ B │ C │ D │ E │
──┼───────────┼───────────┼───────────┼─────────────┼─────────┤
1 │ Montag │ Dienstag │ Mittwoch │ Donnerstag │ Freitag │
──┼───────────┼───────────┼───────────┼─────────────┼─────────┤
2 │ Fred │ Manfred │ Alfred │ Fred │ Manfred │
──┼───────────┼───────────┼───────────┼─────────────┼─────────┤
3 │ Manfred │ Alfred │ Frettchen │ Alfred │ Alfred │
──┼───────────┼───────────┼───────────┼─────────────┼─────────┤
4 │ Alfred │ Maulwurf │ Maulwurf │ Maulwurf │ Gustav │
──┼───────────┼───────────┼───────────┼─────────────┼─────────┤
5 │ Frettchen │ │ Fret │ Fredz │ │
──┼───────────┼───────────┼───────────┼─────────────┼─────────┤
6 │ Maulwurf │ │ │ │ │
──┼───────────┼───────────┼───────────┼─────────────┼─────────┤
7 │ │ │ │ │ │
──┼───────────┼───────────┼───────────┼─────────────┼─────────┤
8 │ │ │ z │ │ │
──┼───────────┼───────────┼───────────┼─────────────┼─────────┤
9 │ jj │ │ │ │ │
──┴───────────┴───────────┴───────────┴─────────────┴─────────┘
A1:E9
haben das Zahlenformat: Standard

Tabellendarstellung erreicht mit dem Code in FAQ:2363

Code in Modul1:

Option Explicit
'
Function Nichtda() As String
'Application.Volatile
Dim Zei As Long, Zelle As Range
With Worksheets("Tabelle1")
 For Each Zelle In Worksheets(.Range("C1").Value).UsedRange
 If Zelle.Row \> 1 Then
 If Application.WorksheetFunction.CountIf(.Columns(1), Trim(Zelle)) = 0 Then
 Nichtda = Nichtda & Zelle & " "
 End If
 End If
 Next Zelle
 If Len(Nichtda) \> 0 Then Nichtda = Left(Nichtda, Len(Nichtda) - 1)
End With
End Function

Code in Tablee2:

Option Explicit
'
Private Sub Worksheet\_Change(ByVal Target As Range)
Worksheets("Tabelle1").Calculate
End Sub

Gruß
Reinhard