Supportnet / Forum / Tabellenkalkulation
WENN-Formel + ODER
Frage
Hallo zusammen,
ich habe ein kleines Problem. Ich möchte jedem Buchstaben eine Zahl zuordnen.
Das heißt, wenn ich in einem Feld einen Buchstaben eingebe,
soll darunter die zu dem Buchstaben zugehörige zahl erscheinen.
Die Zuordnung sieht wie folgt aus:
A I J Q Y = 1
B K R = 2
C G L S = 3
D M T = 4
E H N X = 5
U V W = 6
O Z = 7
F P = 8
Ich habe auch bereits eine Formel erstellt. Jedoch sind das wohl zufiele Funktionen.
=WENN(B6="";"";WENN(ODER(B6="a";B6="i";B6="j";B6="q";B6="y");1;
WENN(ODER(B6="b";B6="k";B6="r");2;WENN(ODER(B6="c";B6="g";B6="l";B6="s");3;
WENN(ODER(B6="d";B6="m";B6="t");4;WENN(ODER(B6="e";B6="h";B6="n";B6="x");5;
WENN(ODER(B6="u";B6="v";B6="w");6;WENN(ODER(B6="o";B6="z");7;
WENN(ODER(B6="f";B6="p");8;"")))))))
Kann ich das auch anders lösen?
Vielen Dank im voraus
Gruß Andreas
Antwort 1 von Primut
Hi Andreas,
mein Vorschlag aus die Schnelle wäre ein kleines Makro, das löst die Sache etwas eleganter, hängt aber auch davon ab, was überhaupt dein Ziel ist.
Das Makro fügst du so ein:
Mit Alt+F11in die VBA Entwicklungsumgebung, dort sind links im Projekt-explorer (falls nicht vorhanden unter Ansicht öffnen) deine Tabellenblätter und die Arbeitsmappe aufgeführt. Das Tabellenblatt, das diese Funktion erhalten soll (zB Tabelle1) doppelt anklicken und entsprechenden Code per Copy/paste einfügen. Das war´s schon.
Reagiert derzeit nur auf Groß - Buchstaben.
Besten Gruß
Primut
mein Vorschlag aus die Schnelle wäre ein kleines Makro, das löst die Sache etwas eleganter, hängt aber auch davon ab, was überhaupt dein Ziel ist.
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Value = "" Then Exit Sub
Select Case Target.Value
Case "A", "I", "J", "Q", "Y"
Target.Offset(1, 0).Value = 1
Case "B", "K", "R"
Target.Offset(1, 0).Value = 2
Case "C", "G", "L", "S"
Target.Offset(1, 0).Value = 3
Case "D", "M", "T"
Target.Offset(1, 0).Value = 4
Case "E", "H", "N", "X"
Target.Offset(1, 0).Value = 5
Case "U", "V", "W"
Target.Offset(1, 0).Value = 6
Case "O", "Z"
Target.Offset(1, 0).Value = 7
Case "F", "P"
Target.Offset(1, 0).Value = 8
Case Else
End Select
End Sub
Das Makro fügst du so ein:
Mit Alt+F11in die VBA Entwicklungsumgebung, dort sind links im Projekt-explorer (falls nicht vorhanden unter Ansicht öffnen) deine Tabellenblätter und die Arbeitsmappe aufgeführt. Das Tabellenblatt, das diese Funktion erhalten soll (zB Tabelle1) doppelt anklicken und entsprechenden Code per Copy/paste einfügen. Das war´s schon.
Reagiert derzeit nur auf Groß - Buchstaben.
Besten Gruß
Primut
Antwort 2 von CaroS
Hallo Tauchbaer82,
wenn die Buchstaben- und Zahlen-Zellen bekannt sind, dann kannst Du auch gut den SVERWEIS (oder WVERWEIS) einsetzen. Lege da, wo es Dich nicht stört, z. B. in X2:Y27, für den SVERWEIS zwei Hilfsspalten an, schreibe in die linke die Buchstaben und daneben die Zahlen.
Wenn in A1 ein Buchstabe eingegeben und in A2 die zugehörige Zahl angezeigt werden soll, dann ist die Formel für A2:
=SVERWEIS(A1; $X$2:$Y$27;2)
Beim Kopieren der Zelle A2 passt sich die Formel an.
Gruß,
CaroS
wenn die Buchstaben- und Zahlen-Zellen bekannt sind, dann kannst Du auch gut den SVERWEIS (oder WVERWEIS) einsetzen. Lege da, wo es Dich nicht stört, z. B. in X2:Y27, für den SVERWEIS zwei Hilfsspalten an, schreibe in die linke die Buchstaben und daneben die Zahlen.
Wenn in A1 ein Buchstabe eingegeben und in A2 die zugehörige Zahl angezeigt werden soll, dann ist die Formel für A2:
=SVERWEIS(A1; $X$2:$Y$27;2)
Beim Kopieren der Zelle A2 passt sich die Formel an.
Gruß,
CaroS
Antwort 3 von Tauchbaer82
Vielen, vielen Dank für eure Hilfe. Hat super geklappt. :o)
Nun hätte ich noch eine Frage.
Ich habe ein Feld mit einzelnen Zahlen, wie z.B.:
1 5 6 9 2 8 7 3 4 7 2 1 5
5 6 9 7 8 2 3 5 6 1 2
7 8 2 6 4 9 2 5 1
6 2 8 7 9 1 2
2 3 7 6 5
4 1 2
7
Das Feld hat eine bestimmte maximale Größe (A1 bis O15),
jedoch variiert die Anzahl der Zahlen
in dem Feld. Nun würde ich mir gerne die Häufigkeit der jeweiligen Zahlen
an einer anderen Stelle angeben lassen.
(wie oft die 1, die 2, die 3, usw. in dem Feld vertreten ist)
Währe es auch möglich, dass die unterste Zahl auch an einer anderen
Stelle angezeigt wird? Problem ist, dass die unterste Zahl nicht immer
an gleicher Stelle steht. Manchmal ist sie etwas höher oder tiefer.
Gruß,
Andreas
Nun hätte ich noch eine Frage.
Ich habe ein Feld mit einzelnen Zahlen, wie z.B.:
1 5 6 9 2 8 7 3 4 7 2 1 5
5 6 9 7 8 2 3 5 6 1 2
7 8 2 6 4 9 2 5 1
6 2 8 7 9 1 2
2 3 7 6 5
4 1 2
7
Das Feld hat eine bestimmte maximale Größe (A1 bis O15),
jedoch variiert die Anzahl der Zahlen
in dem Feld. Nun würde ich mir gerne die Häufigkeit der jeweiligen Zahlen
an einer anderen Stelle angeben lassen.
(wie oft die 1, die 2, die 3, usw. in dem Feld vertreten ist)
Währe es auch möglich, dass die unterste Zahl auch an einer anderen
Stelle angezeigt wird? Problem ist, dass die unterste Zahl nicht immer
an gleicher Stelle steht. Manchmal ist sie etwas höher oder tiefer.
Gruß,
Andreas
Antwort 4 von CaroS
Hallo Tauchbaer82,
alles kein Problem, wenn ich Dich richtig verstanden habe.
Der erste Schritt wäre, den Bereich, in dem die Zahlen stehen sollen, irgendwie zu fassen zu kriegen. Wenn A1 bis O15 nicht überschritten wird und sich in diesem "Quadrat" sonst nichts anderes abspielt, was stört, dann kannst Du ganz einfach mit A1:O15 arbeiten, auch wenn der Bereich nicht immer vollständig gefüllt ist. Hauptsache ist nur, dass da niemals was steht, was nicht mitgezählt werden soll.
Dann wäre zu überlegen, ob immer genau dieselben Dinge (z. B. die Zahlen 1 - 9) gezählt werden sollen oder ob dies variabel bleiben soll. Mit Variabilität schreibst Du die Zahlen 1 bis 9 (oder andere Werte, die gezählt werden sollen) in die Zellen P1:P9, die Formel =ZÄHLENWENN(A$1:O$15; P1) in Q1 und ziehst/kopierst Q1 runter bis Q9. Ohne Variabilität kannst Du Dir die Zahlen 1 bis 9 in P1:P9 sparen und lässt das P in den Formeln weg: =ZÄHLENWENN(A$1:O$15; 1) bis =ZÄHLENWENN(A$1:O$15; 9).
(Ein variabler oder ein zusammengesetzter Bereich würde mehr Aufwand erfordern.)
Gruß,
CaroS
alles kein Problem, wenn ich Dich richtig verstanden habe.
Der erste Schritt wäre, den Bereich, in dem die Zahlen stehen sollen, irgendwie zu fassen zu kriegen. Wenn A1 bis O15 nicht überschritten wird und sich in diesem "Quadrat" sonst nichts anderes abspielt, was stört, dann kannst Du ganz einfach mit A1:O15 arbeiten, auch wenn der Bereich nicht immer vollständig gefüllt ist. Hauptsache ist nur, dass da niemals was steht, was nicht mitgezählt werden soll.
Dann wäre zu überlegen, ob immer genau dieselben Dinge (z. B. die Zahlen 1 - 9) gezählt werden sollen oder ob dies variabel bleiben soll. Mit Variabilität schreibst Du die Zahlen 1 bis 9 (oder andere Werte, die gezählt werden sollen) in die Zellen P1:P9, die Formel =ZÄHLENWENN(A$1:O$15; P1) in Q1 und ziehst/kopierst Q1 runter bis Q9. Ohne Variabilität kannst Du Dir die Zahlen 1 bis 9 in P1:P9 sparen und lässt das P in den Formeln weg: =ZÄHLENWENN(A$1:O$15; 1) bis =ZÄHLENWENN(A$1:O$15; 9).
(Ein variabler oder ein zusammengesetzter Bereich würde mehr Aufwand erfordern.)
Gruß,
CaroS
Antwort 5 von Primut
Hi,
unterste Zahl bekommst du mit folgender Formel angezeigt:
Bedingung dafür ist allerdings:
-es treten nur Zahlen (und keine Textfelder) auf
-es treten zwischen den Zahlen keine Leerzeichen auf (sondern nur unter denZahlen)
- der gesamte untersuchte Bereich (inkl. letzter Zahl) ist A1:A15, müßte ansonsten angepasst werden
Gruß Primut
unterste Zahl bekommst du mit folgender Formel angezeigt:
=INDEX(A1:A15;ANZAHL(A1:A15);1)Bedingung dafür ist allerdings:
-es treten nur Zahlen (und keine Textfelder) auf
-es treten zwischen den Zahlen keine Leerzeichen auf (sondern nur unter denZahlen)
- der gesamte untersuchte Bereich (inkl. letzter Zahl) ist A1:A15, müßte ansonsten angepasst werden
Gruß Primut
Antwort 6 von CaroS
Hallo Tauchbaer82, hallo Primut,
ich habe eine ganze Weile rumgebastelt und es war nicht völlig umsonst!
Falls es bei diesem Problem überhaupt darauf ankommt, die Zeilennummer der letzten (= untersten) gefüllten bzw. mit einer Zahl gefüllten Zelle innerhalb eines vorgegebenen Spaltenbereichs (hier: A1:A15) zu ermitteln, dann geht das ohne Nebenbedingungen mit einer Matrixformel:
{=MAX(($A$1:$A$15<>"")*(ZEILE($A$1:$A$15)))} für beliebige Zeichen
{=MAX(ISTZAHL($A$1:$A$15)*(ZEILE($A$1:$A$15)))} für Zahlen; Text u. a. wird nicht beachtet.
Die Matrixformel muss ohne die {}-Klammern und mit Strg+Umsch+Eingabe eingegeben werden! Die absoluten Bezüge sind nicht notwendig.
Ein anderes, bisher noch nicht direkt angesprochenes Problem konnte ich noch nicht lösen. =ZÄHLENWENN() scheint ausschließlich in einfachen, rechteckigen Bereichen arbeiten zu wollen, nicht in zusammengesetzten und schon gar nicht in einem dreieckigen Gesamtbereich. Dies habe ich ihm u. a. mit einem Namen versucht unterzuschieben, hat aber nicht geklappt.
Gruß,
CaroS
ich habe eine ganze Weile rumgebastelt und es war nicht völlig umsonst!
Falls es bei diesem Problem überhaupt darauf ankommt, die Zeilennummer der letzten (= untersten) gefüllten bzw. mit einer Zahl gefüllten Zelle innerhalb eines vorgegebenen Spaltenbereichs (hier: A1:A15) zu ermitteln, dann geht das ohne Nebenbedingungen mit einer Matrixformel:
{=MAX(($A$1:$A$15<>"")*(ZEILE($A$1:$A$15)))} für beliebige Zeichen
{=MAX(ISTZAHL($A$1:$A$15)*(ZEILE($A$1:$A$15)))} für Zahlen; Text u. a. wird nicht beachtet.
Die Matrixformel muss ohne die {}-Klammern und mit Strg+Umsch+Eingabe eingegeben werden! Die absoluten Bezüge sind nicht notwendig.
Ein anderes, bisher noch nicht direkt angesprochenes Problem konnte ich noch nicht lösen. =ZÄHLENWENN() scheint ausschließlich in einfachen, rechteckigen Bereichen arbeiten zu wollen, nicht in zusammengesetzten und schon gar nicht in einem dreieckigen Gesamtbereich. Dies habe ich ihm u. a. mit einem Namen versucht unterzuschieben, hat aber nicht geklappt.
Gruß,
CaroS
Antwort 7 von nighty
hi all :)
versteh nur bahnhof :)
oder so
A1:A5 DeineBuchstaben
B1:B5 DeineZahlen
C1 DeineEingabe der Buchstabe
D1 Die folgende Formel
=SUMMENPRODUKT((A1:A5=C1)*(B1:B5))
gruss nighty
versteh nur bahnhof :)
oder so
A1:A5 DeineBuchstaben
B1:B5 DeineZahlen
C1 DeineEingabe der Buchstabe
D1 Die folgende Formel
=SUMMENPRODUKT((A1:A5=C1)*(B1:B5))
gruss nighty
Antwort 8 von CaroS
Hallo nighty,
bist Du im richtigen Thread? Ansonsten verstehe ich jetzt auch nur Bahnhof. Ich dachte, es geht seit AW3 um "die Häufigkeit der jeweiligen Zahlen" ?
Gruß,
CaroS
bist Du im richtigen Thread? Ansonsten verstehe ich jetzt auch nur Bahnhof. Ich dachte, es geht seit AW3 um "die Häufigkeit der jeweiligen Zahlen" ?
Gruß,
CaroS