Excel VBA

Walter_W_Hofmann · 3. Oktober 2009 um 19:37

Liebe/-r Experte/-in,

ich habe ein relativ großes Problem und finde einfach nicht die richtige Lösung.
In einer normalen Excel Tabelle habe ich diverse Rechnungen mit Formel erledigt.
Das klappt alles Super.
Jetzt das Problem – in einer Zelle wird eine Stückzahl von Bauteilen eingetragen.
Diese Zahl geht von 1 – 35000, da es sich um eine Stichprobenprüfung nach AQL handelt variiert der Ausgabewert in einer anderen Zelle.
Es gibt 3 Prüfungsarten, vereinfacht, normal und verschärft.
Gibt man z.B. bei einer vereinfachten Prüfung 33 Bauteile an, soll in einer anderen Zelle der Wert 3 automatisch erscheinen. Bei einer normalen Prüfung (wieder 33 Stück) ist der Wert aber schon 8.
Je nachdem welche Prüfart und wie hoch die Stückzahl ist variiert der Ausgabewert der zu prüfenden Teile.
Bei der vereinfachten Prüfung habe ich das noch mit der WENN Funktion machen können, da ich nur 7 mal WENN hatte –siehe Formel unten.

=WENN(A6="";"";WENN(A6

Gerold_28f286 · 3. Oktober 2009 um 23:43

Hallo Walter

Wenn du das mit VBA lösen willst und noch nie ein Makro
geschrieben hast, so hast du dann andere Probleme…
Versuch mal das:

=SVERWEIS(A6;$F$5:blush:G$12;2;WAHR)

und dann diese Zahlen in die Zellen F5 bis G12

1 2
9 3
150 5
280 8
500 13
1200 20
3200 32
10000 50

Die Matrix kannst du natürlich irgendwo hinstellen, auch auf ein anderes Tab oder in versteckte Spalten.

Wichtig ist das Wahr in der Formel sonst sucht sich der sverweis eine genaue Übereinstimmung.

Wenn du dir das mit VBA doch antun willst,
so würde es mit VBA schon eine elegante Lösung geben

Hier ist eine Anleitung welche Schritte du machen musst.
http://excelwelt.de/Makros/funk.html

und die Funktion würde etwa so aussehen:

Function PrüfungVereinfacht(Anzahl)

Select Case Anzahl
Case 1 To 8
PrüfungVereinfacht = 2
Case 9 To 149
PrüfungVereinfacht = 3
Case 150 To 279
PrüfungVereinfacht = 5
Case 280 To 499
PrüfungVereinfacht = 8
Case 500 To 1199
PrüfungVereinfacht = 13
Case 1200 To 3199
PrüfungVereinfacht = 20
Case 3200 To 9999
PrüfungVereinfacht = 32
Case Else
PrüfungVereinfacht = 50
End Select
End Function

Viel Spass
Gruss
Gerold

Walter_W_Hofmann · 4. Oktober 2009 um 05:06

Hallo Gerold,

als erstes vielen Dank für die schnelle Hilfe.
Ich habe die Matrix in das Excel Blatt eingefügt und es hat sofort geklappt.
Ich hatte leider vergessen zu sagen, das es ja eigentlich noch etwas weiter geht.
Ich sagte ja es gibt 3 verschiedene Arten zu prüfen.

vereinfacht
normal
verschärfte Prüfung

bei den verschiedenen Prüfungen gibt es unterschiedliche Mengen die geprüft werden müssen.
Das haben wir ja in der Matrix erfasst.
Allerdings der nächste Schritt ist dieser.
Bei einer Anlieferung von 100 Bauteilen müssen 3 Prüflinge entnommen werden.
Bei dieser Prüfung darf keines Fehlerhaft sein, sonst geht die Lieferung zurück.
Das heißt – 100 Teile – 3 Proben – 0 Fehler erlaubt – 1 Fehler die Lieferung geht zurück.
(anderes Bsp.)
3200 Teile – 20 Proben – 1 Fehler erlaubt – bei 3 Fehler geht Lieferung zurück
Die Anzahl der Proben richtet sich nach der Anzahl der Liefermenge, daraus ergibt sich die Anzahl der erlaubten Fehler oder die Anzahl bei der die Lieferung zurück geht.
Das beste für mich wäre natürlich folgendes.

Ich gehe in eine Zelle und trage die Stückzahl der
Anlieferung ein.
Auswahl der Prüfungsart (einfach, normal oder
verschärft) – eventuell in einer anderen Zelle
Und das Programm schreibt in eine weitere Zelle –
Anzahl der zu entnehmenden Proben, erlaubte Fehler,
bei Überschreitung der erlaubten Fehler Lieferung
zurück senden.

Geht so etwas auch noch normal in Excel?
Das verknüpfen von 3 verschiedenen Prüfungsarten mit so viel Abfragen.

Oder ist das besser in VBA zu lösen?
Wie würde ein solches Makro mit all den Abfragen aussehen?
Wie könnte man ein solches Makro in einem Tabellenblatt bzw. in einer Zelle verankern?
Die Ausgabe müsste natürlich in einer anderen Zelle erfolgen.

Ich weiß viele Fragen – Sorry.

Eventuell hast du ja mein komplettes Problem erkannt, und hast eine gute Lösung.
Noch mal vielen Dank für die erste Hilfe.

Walter

Gerold_28f286 · 4. Oktober 2009 um 20:00

Hallo Walter
Man kann fast immer alles machen…

Mit Formeln würde ich das Ding einfach erweitern um 2Spalten und 3 sverweise verwenden.
die 3 Arten unterscheidet man am besten mit wenn Abfragen.

Matrix
G3 bis J34

vereinfacht
Anzahl Proben Fehler erlaubt Lieferung zurück
1 2 0 1
9 3 0 1
150 5 0 1
280 8 4 7
500 13 5 8
1200 20 6 9
3200 32 7 10
10000 50 8 11

normal
Anzahl Proben Fehler erlaubt Lieferung zurück
1 2 0 1
9 3 0 1
150 5 0 1
280 8 4 7
500 13 5 8
1200 20 6 9
3200 32 7 10
10000 50 8 11

verschärft
Anzahl Proben Fehler erlaubt Lieferung zurück
1 2 0 1
9 3 0 1
150 5 0 1
280 8 4 7
500 13 5 8
1200 20 6 9
3200 32 7 10
10000 50 8 11

L4 bis L6
vereinfacht
normal
verschärft

Diese 3 Zeilen kannst du dann auch verwenden für die Bedingte Formatierung in Zelle B6, so dass du da eine Auswahlliste mit den 3 Möglichkeiten hast.

A5 bis E5
Anzahl Prüfart Proben Fehler erlaubt Lieferung zurück

A6 --> Anzahl
B6 --> vereinfacht oder normal oder verschärft
C6
=WENN(B6=$L$4;SVERWEIS(A6;$G$5:blush:J$12;2;WAHR);WENN(B6=$L$5;SVERWEIS(A6;$G$16:blush:J$23;2;WAHR);WENN(B6=$L$6;SVERWEIS(A6;$G$27:blush:J$34;2;WAHR);"")))

D6
=WENN(B6=$L$4;SVERWEIS(A6;$G$5:blush:J$12;3;WAHR);WENN(B6=$L$5;SVERWEIS(A6;$G$16:blush:J$23;3;WAHR);WENN(B6=$L$6;SVERWEIS(A6;$G$27:blush:J$34;3;WAHR);"")))

E6
=WENN(B6=$L$4;SVERWEIS(A6;$G$5:blush:J$12;4;WAHR);WENN(B6=$L$5;SVERWEIS(A6;$G$16:blush:J$23;4;WAHR);WENN(B6=$L$6;SVERWEIS(A6;$G$27:blush:J$34;4;WAHR);"")))

In VBA würde das etwa so aussehen.
3 Funktionen. Für jede Ausgabezelle eine.
AnzProben
FehlerErlaubt
FehlerRueckweisung

in irgend eine Zelle
=AnzProben(A6;B6)
und
=FehlerErlaubt(A6;B6)
und das auch noch in eine Zelle
=FehlerRueckweisung(A6;B6)

und das Zeugs da unten in VBA Modul
nach dieser Anleitung
http://excelwelt.de/Makros/funk.html
… und dann musst du nur noch richtige Zahlen eintragen…

'---------------------------------------
Function AnzProben(Anzahl, Pruefart)
Select Case Pruefart
Case „vereinfacht“
Select Case Anzahl
Case 1 To 8
AnzProben = 2
Case 9 To 149
AnzProben = 3
Case 150 To 279
AnzProben = 5
Case 280 To 499
AnzProben = 8
Case 500 To 1199
AnzProben = 13
Case 1200 To 3199
AnzProben = 20
Case 3200 To 9999
AnzProben = 32
Case Else
AnzProben = 50
End Select

Case „normal“
Select Case Anzahl
Case 1 To 8
AnzProben = 2
Case 9 To 149
AnzProben = 3
Case 150 To 279
AnzProben = 5
Case 280 To 499
AnzProben = 8
Case 500 To 1199
AnzProben = 13
Case 1200 To 3199
AnzProben = 20
Case 3200 To 9999
AnzProben = 32
Case Else
AnzProben = 50
End Select

Case „verschärft“
Select Case Anzahl
Case 1 To 8
AnzProben = 2
Case 9 To 149
AnzProben = 3
Case 150 To 279
AnzProben = 5
Case 280 To 499
AnzProben = 8
Case 500 To 1199
AnzProben = 13
Case 1200 To 3199
AnzProben = 20
Case 3200 To 9999
AnzProben = 32
Case Else
AnzProben = 50
End Select
Case Else
AnzProben = „“
End Select
End Function

Function FehlerErlaubt(Anzahl, Pruefart)
Select Case Pruefart
Case „vereinfacht“
Select Case Anzahl
Case 1 To 8
FehlerErlaubt= 2
Case 9 To 149
FehlerErlaubt= 3
Case 150 To 279
FehlerErlaubt= 5
Case 280 To 499
FehlerErlaubt= 8
Case 500 To 1199
FehlerErlaubt= 13
Case 1200 To 3199
FehlerErlaubt= 20
Case 3200 To 9999
FehlerErlaubt= 32
Case Else
FehlerErlaubt= 50
End Select

Case „normal“
Select Case Anzahl
Case 1 To 8
FehlerErlaubt= 2
Case 9 To 149
FehlerErlaubt= 3
Case 150 To 279
FehlerErlaubt= 5
Case 280 To 499
FehlerErlaubt= 8
Case 500 To 1199
FehlerErlaubt= 13
Case 1200 To 3199
FehlerErlaubt= 20
Case 3200 To 9999
FehlerErlaubt= 32
Case Else
FehlerErlaubt= 50
End Select

Case „verschärft“
Select Case Anzahl
Case 1 To 8
FehlerErlaubt= 2
Case 9 To 149
FehlerErlaubt= 3
Case 150 To 279
FehlerErlaubt= 5
Case 280 To 499
FehlerErlaubt= 8
Case 500 To 1199
FehlerErlaubt= 13
Case 1200 To 3199
FehlerErlaubt= 20
Case 3200 To 9999
FehlerErlaubt= 32
Case Else
FehlerErlaubt= 50
End Select
Case Else
FehlerErlaubt= „“
End Select
End Function

Function FehlerRueckweisung(Anzahl, Pruefart)
Select Case Pruefart
Case „vereinfacht“
Select Case Anzahl
Case 1 To 8
FehlerRueckweisung= 2
Case 9 To 149
FehlerRueckweisung= 3
Case 150 To 279
FehlerRueckweisung= 5
Case 280 To 499
FehlerRueckweisung= 8
Case 500 To 1199
FehlerRueckweisung= 13
Case 1200 To 3199
FehlerRueckweisung= 20
Case 3200 To 9999
FehlerRueckweisung= 32
Case Else
FehlerRueckweisung= 50
End Select

Case „normal“
Select Case Anzahl
Case 1 To 8
FehlerRueckweisung= 2
Case 9 To 149
FehlerRueckweisung= 3
Case 150 To 279
FehlerRueckweisung= 5
Case 280 To 499
FehlerRueckweisung= 8
Case 500 To 1199
FehlerRueckweisung= 13
Case 1200 To 3199
FehlerRueckweisung= 20
Case 3200 To 9999
FehlerRueckweisung= 32
Case Else
FehlerRueckweisung= 50
End Select

Case „verschärft“
Select Case Anzahl
Case 1 To 8
FehlerRueckweisung= 2
Case 9 To 149
FehlerRueckweisung= 3
Case 150 To 279
FehlerRueckweisung= 5
Case 280 To 499
FehlerRueckweisung= 8
Case 500 To 1199
FehlerRueckweisung= 13
Case 1200 To 3199
FehlerRueckweisung= 20
Case 3200 To 9999
FehlerRueckweisung= 32
Case Else
FehlerRueckweisung= 50
End Select
Case Else
FehlerRueckweisung= „“
End Select
End Function

'---------------------------------------

Habe es nicht gross getestet sollte mit den richtigen Zahlen aber gehen.
Es geht sicher noch eleganter… aber du musst es ja auch noch anpassen können.
Viel Spass
Gerold

Walter_W_Hofmann · 5. Oktober 2009 um 17:05

Gerold,

als erstes vielen Dank für deine schnelle Hilfe.
Ich werde das gleich mal testen.
Ich fange wohl mit der Excel Formel an.
Sollte das alles Funktionieren, werde ich mich an das VBA rantasten
gefällt mir irgend wie besser.
Allerdings weiß ich noch nicht so genau wie das mit dem Editor und dem einbinden von dem Makro in die Excel Tabelle geht.

Aber eins nach dem anderen …

Nochmals vielen Dank für die Hilfe.

Walter

Walter_W_Hofmann · 10. Oktober 2009 um 18:51

Hallo Gerold,

ich arbeite gerade in einer Excel Tabelle.
Dort habe ich definierte Zellen mit den Namen:

Nr.
Bezeichnung
Zeichnung ID

Jetzt meine Frage, ist es möglich aus einer Tabelle einen Wert zu wählen und die anderen Werte werden automatisch eingetragen?

Als Beispiel ich wähle die Nr. 1 und in einer anderen Zelle erscheint dann die Bezeichnung in der nächsten Zelle die Zeichnungs- ID automatisch.

Ich hoffe du hast eine Lösung, da meine nicht so richtig funktioniert.

Gruss

Walter

Gerold_28f286 · 10. Oktober 2009 um 20:31

Hallo Walter

Wenn ich dich richtig verstehe so möchtest du Nr, Bezeichnung ODER Zeichnung ID wählen und dann die andern zwei fehlenden vervollständigen.

Nur mit 3 Zellen kann das so nicht funktionieren, da in den jeder eine Formel sein müsste… und du immer eine überschreiben würdest.

Habe dir aber eine Variante… und eine neue Formel.

Versuchs mal mit

Zellen E1 bis G6

Nr. Bezeichnung Zeichnung ID
1 a a1
2 b b2
3 c c3
4 d d4
5 e e5

Zelle A1: über Datengültigkeit / Liste machst du eine Auswahlliste mit Nr.,Bezeichnung und Zeichnung ID.

Zellen A6 bis C6

Nr. Bezeichnung Zeichnung ID

Zelle A7:
=DBAUSZUG($E$1:blush:G$6;A6;$A$1:blush:A$2)
Zelle B7:
=DBAUSZUG($E$1:blush:G$6;B6;$A$1:blush:A$2)
Zelle B7:
=DBAUSZUG($E$1:blush:G$6;C6;$A$1:blush:A$2)

Jetzt kannst du in Zelle A1 eine Auswahl treffen und
in Zelle A2 den Suchwert eingeben.

Viel Spass
Gerold

Walter_W_Hofmann · 11. Oktober 2009 um 11:42

Hallo Gerold,

danke für die schnelle Hilfe.
Habe die Formel in meine Tabelle eingefügt, einige Änderungen gemacht und es scheint zu klappen.
Sobald ich mit meiner Tabelle fertig bin und alles so funktioniert wie ich mir das vorstelle melde ich mich.

Gruss Walter