Wie löse ich dieses Problem mit Excel?

3 Antworten

in Deiner Frage ist mir einiges unklar: Meinst du mit Zeile Spalte? könnten Deine Daten also so aussehen?:

1.:

  • Sp.A, SpB, C, D,   E=Ergebnisformel
  • ABCD, 1 , 20, 20€,
  • AAAA,  1 , 20, 20€,

2. sind die 20€ ein Text oder ein Währungsformat von 20?

3. Gibt es eine Liste der erlaubten (alternativ verbotenen) Codes, also AAAA etc? Oder unterliegen die einer Systematik (alle AAAA, BBBB,CCCC etc?)

4. können die Kombi-Zahlen jeweils nur einmal vorkommen oder ist jede Zahl definitiv maximal einmal vertreten? Beispiel

jeweils oder 20, 21, 23, 32, 39, 40, 41 =>richtig bei 2

aber könnte zB 24, 26, 32, 35, 38, 43 richtig bei 4 ergeben? (32 erlaubt sowohl 2 als auch 4 ?)

Um den SVerweis zu verwenden müsstest Du alle diese Zahlen listen und ihnen in einer zweiten Spalte die 2 oder 3 oder... zuweisen, damit Du sie abfragen kannst - nicht umgekehrt.

Du müsstest also eine Liste mit 21*7 = 147 Zellen/unterschiedlicheZahlen erstellen (oder- wenn die Anzahl von Bedingung zu Bedingung variiert- entsprechend mehr oder weniger, das spielt dann keine Rolle, ob eine oder 8 Zahlen mit SpalteB kompatibel sind.

ich weiß noch nicht, ob und wann ich wieder dazu komme, das zu bearbeiten (erfordert für mich ungestörte Konzentration über eine gewisse Zeit), ich hoffe, bis übermorgen, aber vllt kannst du ja selber noch was draus machen, mindestens aber antworten.

Ok, also etwas ausführlicher ;)
Es geht im eine Liste, die unser (uraltes) Warenwirtschaftssystem ausspuckt. Unsere Aussendienstler arbeiten jetzt mit
Smartphones. Die Daten werden gesammelt und müssen dann als Ganzes geprüft werden.

In Spalte 1 steht der Auftragsgeber, da wir für Fremdfirmen arbeiten. Das ist durch je 4 Buchstaben abgekürzt. Bei allen
Auftragsgebern sind die Kennungen identisch, bis auf 2. Bei diesen gelten andere Regeln, so dass die Spalten 2+3 andere
Bedeutungen haben. Passen bei allen anderen die 1 und die 20 zusammen, wäre das bei dem Auftraggeber falsch. Dort kann
z.B. die 1 nur mit der 31 und 39 zusammenpassen, während bei allen anderen zusätzliche Varianten gelten.

In Spalte 4 muss ein Zahlenwert stehen, es dürfen keine leeren Zellen sein. Wenn keine Kosten entstanden sind,
muss 0,00 drin stehen. Ein €-Zeichen ist dort nicht zu finden.

Die Liste sieht ungefähr so aus:

Spalte 1 = Auftraggeber, Spalte 2 = Auftragsart, Spalte 3 = Tätigkeit, Spalte 4 = Zusatzkosten

  • AABB - 1 - 20 - 0,00 = wahr
  • AAAA - 1 -20 - 0,00 = falsch (weil bei AAAA 1-20 nicht passt)
  • AAAA - 1 - 21 - 20,00 = wahr
  • ABCD - 2 - 23 - 0,00 = wahr
  • BCDD - 2 - 16 - 0,00 = falsch (weil Auftragsart nicht zu Tätigkeit passt)
  • EFGH - 4 - 1 - 25,00 = wahr
  • XYZA - 4 - 1 - "leer" = falsch (weil kein Betrag)

In Spalte 1 gibt es 17 Werte, wobei man nur eine einzelne in die Formel bringen muss. Also nur AAAA müssen andere Werte zugeordnet werden.
In Spalte 2 gibt es 3 Werte und in Spalte 3 gibt es 13 verschiedene Werte
Diese Werte kommen in der Liste in verschiedenen Konstellationen vor, auch mehrfach. Es kann z.B. 40 mal "AAAA-1-20-0.00" vorkommen, oder auch gar nicht.

Hoffe das hilft ;)

0
@wildcarts2

Werde eine Weile brauchen, diese Vielzahl an Infos zu verarbeiten (erst im Kopf, dann in xl). schau trotzdem täglich rein,falls sich nochmal Rückfragen ergeben, im Moment sieht alles ausreichend definiert aus. Meine Schlussfolgerungen:

  • Also in Spalte B kann nur 1, 2 oder 4 Vorkommen?
  • Ebenso: es zählen alle Buchstabencodes ausser AAAA und leer als gültig.
  • Die Codes in Spalte 3 (Tätigkeiten 1...39) sind mehreren Auftragsarten(Sp.2) zugeordnet (was die Aufgabe erheblich erschwert, da muss ich mir echt noch was einfallen lassen, wenn es nicht eine Riesenliste aller Möglichkeiten zum Auswerten werden soll)


(wenn ich da falsch liege, korrigiere mich, ansonsten keine Aktion Deinerseits.)


0
@Iamiam

Bin jetzt auf Folgendes verfallen:

Du legst eine (Mini-)Verweistabelle an: links die Ziffer für die Auftragsart (hab mal eine Zeile mehr, also 4 Zeilen vorgesehen, sowie in Zeile 1 die Bezeichnungen(Überschriften). Das Blatt heisst bei mir MBlatt, ║ stellt das Spaltenwechselzeichen dar.

in die zweite Spalte schreibst Du -in der gezeigten Art aneinandergereiht- die zugehörigen Tätigkeiten, also so:

  • Auftr-A ║ Tätigk.
  • 1  ║ _20_21_31_
  • 2  ║ _20_21_23_32_39_40_41_
  • 3  ║ _?_?_
  • 4  ║ _?_16_?_

Diese Form erlaubt auch 1- und vielziffrige Zahlen eindeutig zuzuordnen.
Die Formel in zB E2 lautet dann:
=WENN(UND(LÄNGE(A2)=4;A2<>"AAAA";ISTZAHL(D2));WENN(SUCHEN(""&C2&"";INDEX(Mblatt!B:B;VERGLEICH(B2;Mblatt!A:A;0)))>0;"✔";"✖"))
Anstatt A:A und B:B kannst Du auch A$2:A$5 und B$2:B$5 schreiben, das entlastet den Prozessor.
Erklärung:
Zuerst werden die Rahmenbedingungen abgetestet: 4 Zeichen für Auftraggeber, nicht AAAA und ob in Sp. D eine Zahl steht. Trifft eine dieser Bedingungen nicht zu, erscheint FALSCH
Dann wird gesucht, ob die Tätigkeit zur genannten Auftragsart passt. Passt sie nicht, erscheint #WERT!, Du kannst also aus diesen beiden Fehlertypen auf die Art des Fehlers schließen.
Passt alles, erscheint der Haken ✔.
wird eigentlich gar nicht gebraucht, weil vorher der Fehler #WERT! auftritt (wenn die Ziffer nicht im String gefunden wird)

Ich hoffe, Du kommst so damit zurecht.

Ach ja, und anstatt SVerweis habe ich die Formelverschachtelung index-Vergleich genommem, dann sieht man am Indexbereich gleich, aus welcher Spalte der Wert kommt.

0
@Iamiam

die von mir der 4 zugeordnete 16 ist wohl falsch, aber Du musst ohnehin alles überprüfen.

Wenn Du noch Infos zu AAA schickst (welche Auftragsarten sind möglich und dazu jeweils welche Tätigkeiten -die eine Info ist ja wohl nicht die einzige Möglichkeit?- , dann kann ich noch eine zweite Wenn-Abfrage vor der letzten Klammer einfügen, damit auch AAAA geprüft werden kann. Die Formel erweitert sich dadurch allerdings erheblich und evtl brauchst du (bei mehr als 1 Auftragsart möglich) eine zweite, ebenfalls sehr kurze Verweistabelle.

Die Verweistabelle/n kannst Du auch aufs Arbeitsblatt versetzen, im Fall der Adressenformulierung A:A und B:B aber nur als Gesamtspalten.

0
@Iamiam

Vielen Dank schon mal für deine Mühen!
Leider funktioniert das so aber nicht.

Meine vollständige Bezugs-Tabelle sieht so aus:

Spalte A  || Spalte B
1 || _16_20_21_22_23_24_26_32_
2 || _16_20_21_22_23_24_26_32_39_40_41_
4 || _1_16_18_22_23_24_26_31_32_39_41_49_

Dies gilt für Alle außer AAAA und AABB.

Die zu prüfende Tabelle sieht so aus (beginnend in A5):

Spalte A || Spalte B || Spalte C || Spalte D ||  Spalte E
AAAA  || 1  ||  32  ||  0,00  || Wert  --> FALSCH
ABCD  || 1 ||  21 || 0,00  ||  Wert --> #NV
AAAA  || 1 ||  39 ||  0,00   ||  Wert  --> FALSCH
ABCD  || 1 ||  39 || 0,00   ||  Wert  --> #NV

Angepasst ist die Formel dann so:

=WENN(UND(LÄNGE(A5)=4;A5<>"GZST";ISTZAHL(D5));WENN(SUCHEN(""&C5&"";INDEX(Tabelle2!B3:B5;VERGLEICH(B5;Tabelle2!A3:A5;0)))>0;"✔";"✖"))

Demnach müsste Zeile 5 = wahr, 6 = wahr, 7 = falsch und 8 = falsch sein.

Für AAAA gelten ausschließlich die Kombos

  • AAAA || 1 ||  32 ||  >0  || Wert
  • AAAA || 4 || 1 + 31 + 32 + 41  || > 0 || Wert

Wenn bei AAAA etwas anderes steht, ist es falsch.

Zusätzlich darf Spalte E bei AAAA und auch nicht leer sein, wenn in Spalte C 1,31 od. 41 steht. Steht dort die 32 muss das Feld leer sein (hatte ich ganz vergessen). Dort steht ebenfalls eine Zahl, die bei der Eingabe durch den Mitarbeiter vergessen werden könnte. Bei allen anderen Kombos ist es egal ob dort etwas steht oder nicht.

Das ist nicht ganz so wichtig, da es schnell ins Auge fällt bei der Prüfung. Wenn man es aber integrieren könnte, um so besser ;)

Eine weitere Sonderregel, die sehr selten vorkommt, weswegen ich sie auch vergessen habe, ist bei AABB. Dann dürfen nur die folgenden Kombos als korrekt angegeben werden: 

  • AABB || 1 || 39 || >0 || Wert
  • AABB || 2 || 31 || >0 || Wert
  • AABB || 4 || 1 + 41 + 31 ||  >0 || Wert

Eine Vollständige Liste sähe so aus:

  • ABCD || 1 || 16_20_21_22_23_24_26_32_|| 0 || "egal"
  • ABCD || 2 || 16_20_21_22_23_24_26_32_39_40_41_|| 0 || "egal"
  • ABCD || 4 || 1_16_18_22_23_24_26_31_32_39_41_49_|| 0 || "egal"
  • AAAA || 1 ||  32 ||  >0  || Wert
  • AAAA || 4 || 1_31_32_41  || > 0 || Wert
  • AABB || 1 || 39 || >0 || Wert
  • AABB || 2 || 31 || >0 || Wert
  • AABB || 4 || 1_41_31 ||  >0 || Wert

Alle anderen Kombos müssen als Falsch angezeigt werden.

0
@wildcarts2

Puh, 2 Ausnahmen und noch dazu mit Sonderregeln (>0, mussschon / darfnicht machen das Ganze etwas länglich und schwer durchschaubar. Muss mir das durch den Kopf gehen lassen..

Funktioniert es bei den Standard-Auftraggebern, wenn Du die "_" entsprechend korrigierst? (s.u.)

Heisst Wert Zahl bzw als Zahl interpretierbarer Text? (Wert wird in xl zweideutig auch für Text"werte" verwendet). dann liefert =istzahl(..) natürlich immer falsch, müsste dann mit =Wennfehler(Wert(...);FALSCH) arbeiten oder Wennfehler((...)*1;#NV), wobei man hier die Art des Fehlers sogar definieren und zur Unterscheidung heranziehen könnte.

Und : sind die von mir als Zahl gelesenen Werte evtl nur Ziffernfolgen? also '0,00 und nicht 0 auf zwei Dezimalen formatiert. (zB SAP liefert(e?) sowas, vermutlich auch andere WaWiSys. Teste das mal mit =istzahl(..) sowohl in Spalte B als auch in C und D, ggf jetzt noch zusätzlich E. Sollten das Ziffernfolgen (also Texte) sein, dann schick mir die Beispielseinträge nochmal und mach es immer kenntlich mit einem ' davor (es sei denn, ALLES sind Zifferntexte), sollte nur bei D und E eine Rolle spielen, bin mir aber noch nicht sicher. (und Trennzeichen ist , nicht . ?)

Ausserdem muss vor die Tätigkeitsliste ebenfalls noch ein _ rein, sonst findet das die erste Zahl (bzw ersten Ziffernblock) nicht. (es wird ja immer "_"&Ziffern&"_" gesucht, so dass genau zwischen _1_, _31_ und zB _123_ unterschieden werden kann

Ebenso bei den Ausnahmen, muss dann so aussehen:
AAAA || 1 ||  _32_ ||  >0  || Wert als Ziffernfolge
AAAA || 4 || _1_31_32_41_  || > 0 || Wert -"-
AABB || 1 || _39_ || >0 || Wert -"-
AABB || 2 || _31_ || >0 || Wert -"-
AABB || 4 || _1_31_41_*) ||  >0 || Wert -"-

Ich werde dieses Wochenende nicht dazukommen, also frühestens Montag (wenn mir nicht heut nacht noch eine "Schnellerleuchtung" vom Traumbaum fällt.)

*): vorsichtshalber Zahlen immer steigend ordnen, falls ich mal mit VERWEIS() (nicht SVERWEIS()) arbeiten müsste (Verweis erfordert geordnete Listen)

0
@Iamiam

Vielen Dank für deine Mühen. Vielleicht wäre es einfacher, wenn wir Emailadressen austauschen und ich dir eine Beispieldatei schicke.

0
@wildcarts2

damit die Antwort auch hier steht:

Mit einer wesentlich umfangreicheren Verweistabelle (A2:Exx) lässt sich das Problem halbwegs durchschaubar lösen: Tabelle:

  • AAAA || 1 || _32_ || 20 || 22 || 
  • AAAA || 4 || _1_31_32_41_ || 20 || 22 ||
  • AABB || 1 || _31_ || 20 || 22 ||
  • AABB || 2 || _39_ || 20 || 22 ||
  • AABB || 4 || _1_31_41_ || 20 || 22 ||
  • ABCD || 1 || _16_20_21_22_23_24_26_32_ || || ||
  • ABCD || 2 || _1_16_18_22_23_24_26_31_32_39_41_49__ || 0 || ||
  • ABCD || 4 || _1_16_18_22_23_24_26_31_32_39_41_49_ || || ||
  • BCDD || 1 || _16_20_21_22_23_24_26_32_ || 0 || ||
  • … || …. || _....._ || …. || …. ||

Die Formel ist trotzdem recht lang bei mir in F16 und dann unterziehen:

=WENNFEHLER((SUCHEN("_"&C16&"_";INDIREKT("C"&SUMMENPRODUKT((($A$2:$A$13&$B$2:$B$13)=(A16&B16))*ZEILE($A$2:$A$13)));1)>0)*(LÄNGE(D16)>0)
*WENN(UND(A16<>"AAAA";A16<>"AABB");1;
WENN((LÄNGE(E16)>0)*ISTZAHL(D16)*(D16>0);1;0));"A&B passen nicht zusammen"):


0
@Iamiam

versehentlich zu früh abgeschickt:

Die Formel samt individuellen (Prüf-)Eintragungen auf ein anderes Blatt versetzen (ausschneiden und einfügen, nicht kopieren!), wo Du sie für weitere zu prüfende Zeilen nach unten kopieren kannst ("runterziehen").

Probiers aus!

0

Wenn in Spalte 2 = 1, dann darf in Spalte 3 nur 20, 21 oder 31 stehen. Steht dort was anderes, dann soll in der 5. Spalte ein "Falsch" stehen.

=NICHT(UND(B1=1;ISTNV(VERGLEICH(C1;{20;21;31};0))))

Die nächste Bedingung prüfst du analog.

Leer prüfst du mit ="" oder mit ISTLEER ab.

Die letzte Verbindung verstehe ich nicht, lässt sich aber meiner Einschätzung nach auch mit VERGLEICH erledigen.

Danke schon mal. Das würde klappen, aber dann muss ich 4 Kontrollzeilen erstellen.

Sähe dann so aus:

AAAA - 1 - 20 - 20,00 = wahr wahr wahr wahr

ABCD - 1 - 21 - "leer" = wahr wahr falsch wahr

Das ist dann zu aufwendig. Ich müsste das schon alles in einer Spalte habem

0

Wie bekomme ich bei Excel eine automatisch - aktualisierende Liste zustande?

Hallo. Wie oben schon beschrieben suche ich nach einer Möglichkeit eine automatische Liste zu erstellen. Ich trage Geräte in meine Excelliste ein und auch den Raum, in dem sie stehen. Jetzt möchte ich parallel dazu eine Liste erstellen, wo oben der Raum steht, aber unten drunter sollen die Geräte, die in diesem Raum stehen nach der Reihe aufgelistet werden. (z.B. "Garage" in der ersten Zeile; Zweite Zeile direkt darunter: "Bohrer";darunter "Hammer"; etc.)

Wenn ihr versteht was ich meine und eine Ahnung davon habt, wie ich dieses Problem löse, dann kommentiert mir das bitte. Vielen dank an euch im Vorraus

...zur Frage

Wie kann ich anhand eins Formulars einen einzigen Datensatz per Button ausdrucken?

Hallo zusammen Ich habe eine Datenbank, wo man in einem Formular eine Artikelnummer eingibt und dann wird das Bild dazu aus der selben Tabelle gezogen. Nun möchte ich das man per Button klick den ausgewählten Datensatz aus dem Formular in Berichtform ausdrucken kann... Leider kann ich fast kein VBA dafür ein wenig SQL. Vielen Dank für eure Hilfe!

...zur Frage

Excel Zeilen abfragen und Wert in eine Andere Zeile einfügen aber wie?

Hallo, ich habe folgendes Problem, ich möchte die Zeile K nach einem bestimmten Wert abfragen, Zeile K hat so an die 100 Spalten.Nun wenn eine Spalte z.B. K3 den Wert 10 hat ,soll in Spalte L3 der Wert 1 stehen und das für jede Spalte.Alle Spalten in K die < als 10 sind sollen in den jeweiligen Spalten in L den Wert 2 bekommen.

Ich hoffe, jemand kann mir helfen. Danke im voraus.

...zur Frage

Kann excel muster in zahlen oder wörtern erkennen?

Gibt es eine möglichkeit das excel muster wie "HALLO" oder "12338" erkennt und sie so aus einer reihe zahlen denen zuordnet die auch aus 4 verschiedenen ziffern bestehen und die 3. und die 4. gleich sind?

...zur Frage

Welche Formel für Exel , wenn Spalte A plus ,Spalte B minus und SpalteC das Ergebnis in jeder Zeile stehen soll, in Zeile 4 aber gar nichts steht?

Ich habe eine Tabelle mit Zeilen 1-5 und Spalten A-C. In Spalte C sollen meine Ergebnisse stehen. Spalte A soll zum jeweiligen Ergebnis der vorausgehenden Zeile addiert und Spalte B subtrahiert werden. Problem: In Zeile 4 steht gar nichts und da sollte auch in Spalte C nichts stehen. Bisher habe ich "Summenformel" und "Wenn Dann Formel" versucht. Da in Zeile 4 nichts stehen darf finde ich nicht die richtige Formel, damit in C5 auch noch ein Ergebnis steht.

...zur Frage

Excel: Spalte (hoch) in Zeile(quer) kopieren

Ich möchte aus Tabellenblatt 'Uno' die Zellen A1:A60 ins Tabellenblatt 'Due' kopieren, und zwar in die Zellen A1 bis BF1 kopieren.
Die Zellen sollen also nicht mehr übereinander in der Spalte, sondern nebeneinander in der Zeile zu stehen kommen.
Da gibt's doch irgendwo einen Trick. Kann mir jemand auf die Sprünge helfen? Herzlichen Dank.

...zur Frage

Was möchtest Du wissen?