Frage von Blacky2000, 113

Excel Tabelle durchsuchen?

Wie kann ich die Tabelle "Im Text" (siehe Bild) z.B. nach dem Bigramm (die Buchstabenpaare) welches mit "J" anfängt und den höchsten wert hat durchsuchen und mir ausgeben lassen?Ich glaube die ganz normale Filter oder Sortier Funktion hilft mir nicht da ich diesen Vorgang mind. 25 mal wiederholen muss und das immer mit anderen Buchstaben.Hoffe jemand kennt sich damit aus ;)PS: Wenn jemand weiß wie man mit Excel die Cäsar Verschlüsselung mit Schlüsselwort knackt, würde ich mich auch über eine Erklärung des gesamten Vorgangs freuen.LG feeniXy

Tabellen Link: https://mega.nz/#!I0USXahB!tmZWAx-aizQMprYYG5sNOX96O3M_fJF9CoxNs9cQlkM

Hilfreichste Antwort - ausgezeichnet vom Fragesteller
von Oubyi, Community-Experte für Excel, 48

Ich beziehe mich jetzt mal nur auf den ersten Teil Deiner Frage.
Angenommen in AY123 steht der gesuchte Anfangsbuchstabe, also J.
Und der höchste Wert wird nur in der Spalte AS gesucht (für beide Spalten wäre es komplizierter). Dann sollte die folgende Formel klappen:

{=INDEX(AR1:AR999;(MAX(WENN(LINKS(AR124:AR999;1)=AY123;AS124:AS999+ZEILE(AS124:AS999)*0,001))-GANZZAHL(MAX(WENN(LINKS(AR124:AR999;1)=AY123;AS124:AS999+ZEILE(AS124:AS999)*0,001))))*1000)}

**ACHTUNG!**
Das ist eine Matrixformel.
Das bedeutet: Die geschweiften Klammern {} NICHT mit eingeben, sondern die Eingabe der Formel NICHT mit ENTER abschließen, sondern mit:
STRG & SHIFT & ENTER (alle drei gleichzeitig).
DAS erzeugt die { } und macht die Formel zu einer Matrixformel.

Leider ist mir das etwa "monströs" geraten, aber ich komme im Moment auf keine schönere, sprich kürzere, Lösung. Vielleicht ist da ein anderer Experte hier fitter!? Funktionieren müsste es so jedenfalls.

Kommentar von Blacky2000 ,

Vielen Dank =)

funktioniert super, würde mich mal interessieren wie lange du gebraucht hast um darauf zu kommen oder hast du dir das nur angeguckt und wusstest was zu machen ist?

Kommentar von Oubyi ,

Gern geschehen ☼
Naja, zugegebenermaßen habe ich schon eine halbe Stunde lang dran "rumgedocktert " und bin immer noch nicht wirklich zufrieden damit.
Das Problem war, die Zeilennummer für den Maximalwert heraus zu bekommen.
Ist ein bisschen Trickserei, aber nach jahrzehntelanger Erfahrung mit Excel ist meine Trickkiste auch schon gut gefüllt (:o).

Kommentar von Blacky2000 ,

Hätte noch ne Frage :D

Was muss ich ändern, wenn ich nach dem zweiten Buchstaben suchen will? Also wenn ich nach "J" suche kommt "IJ"? Oder muss man dafür die ganze Formel ändern?

Kommentar von Oubyi ,

Habe jetzt keine Zeit mehr, das zu testen, aber eigentlich müsste das so klappen:

{=INDEX(AR1:AR999;(MAX(WENN(TEIL(AR124:AR999;2;1)=AY123;AS124:AS999+ZEILE(AS124:AS999)*0,001))-GANZZAHL(MAX(WENN(LINKS(AR124:AR999;1)=AY123;AS124:AS999+ZEILE(AS124:AS999)*0,001))))*1000)}

Klappt es?

Kommentar von Blacky2000 ,

Ne, klappt leider nicht, wenn ich nur einen Buchstaben eingebe (in AY123), kommt "#WERT!" und wenn ich zwei Buchstaben eingebe dann "0".

Falls du morgen Zeit hast könntest du mir vielleicht auch erklären was genau die Formel macht, verstehe sie nur teilweise?

Kommentar von Oubyi ,

Sorry, bei der Formel hatte ich nur EINE Links-Funktion durch die Teil-Funktion ersetzt, aber es müssen natürlich beide sein:

{=INDEX(AR1:AR999;(MAX(WENN(TEIL(AR124:AR999;2;1)=AY123;AS124:AS999+ZEILE(AS124:AS999)*0,001))-GANZZAHL(MAX(WENN(TEIL(AR124:AR999;2;1)=AY123;AS124:AS999+ZEILE(AS124:AS999)*0,001))))*1000)}

Erklärung der Formel folgt.

Kommentar von Oubyi ,

Ich versuche mal eine Erklärung, aber da ich nicht weiß welche Voraussetzungen Du mitbringst und die Formel - wie gesagt - etwas monströs, sprich umständlich geraten ist, wird es schwierig:

Komplette Formel:

=INDEX(AR1:AR999;(MAX(WENN(TEIL(AR124:AR999;2;1)=AY123;AS124:AS999+ZEILE(AS124:AS999)*0,001))-GANZZAHL(MAX(WENN(TEIL(AR124:AR999;2;1)=AY123;AS124:AS999+ZEILE(AS124:AS999)*0,001))))*1000)

Der Kern der Formel ist:
WENN(TEIL(AR124:AR999;2;1)=AY123;AS124:AS999+ZEILE(AS124:AS999)*0,001))
Die Bedingung dieser WENN-Funktion ist:
TEIL(AR124:AR999;2;1)=AY123
Die TEIL-Funktion gibt jeweils den 2ten Buchstaben aus dem Bereich AR123:AR999 als Matrix zurück und "schaut", ob das der Buchstabe ist, der in AY123 steht.
IST das der Buchstabe, dass ist die Bedingung WAHR, sonst ist sie FALSCH. Da der "Sonst"-Teil der WENN-Funktion nicht aufgeführt ist, wird bei falscher Bedingung auch einfach FALSCH zurückgegeben.
Ist die Bedingung WAHR, wird - Zelle für Zelle  -
AS124:AS999+ZEILE(AS124:AS999)*0,001)
zurückgegeben.
Also der Wert aus der Spalte AS, plus die Zeilennummer in der dieser Wert steht, mal 0,001.
Das führt dazu, dass, wenn z.B. in der Zelle AR135 das Bigramm ZJ steht und daneben in AS135 die Zahl 123, als Ergebnis 123,135 in die Matrix geschrieben wird. Dadurch weiß ich einmal den gesuchten Wert, und - anhand der Nachkommastellen - gleichzeitig die Zeilennummer, in der dieser Wert steht.
So!
Jetzt habe ich mit der WENN-Funktion eine Matrix erstellt, die z.B. so aussieht:
FALSCH
55,125
120,126
FALSCH
FALSCH
111,129
FALSCH
...
...
123,135 (aus AS135)
usw.

Aus dieser Reihe sucht jetzt das die WENN-Funktion umklammernde MAX den höchsten Wert:
MAX(WENN(TEIL(AR124:AR999;2;1)=AY123;AS124:AS999+ZEILE(AS124:AS999)*0,001))
Angenommen unser Wert aus AS135 ist der höchste Wert, dann muss ich jetzt herausbekommen, in welcher Zeile er steht.
Dass mache ich, indem ich von diesem Wert den ganzzahligen Anteil abziehe (123,135 - 123 = 0,135) und ihn mit 1000 multipliziere (0,135*1000 = 135).
Dieser Teil macht die Formel so monströs. Eigentlich steht in dem Teil nur:
(123,135-GANZZAHL(123,135))*1000.
Da ich aber hier die 123,135 zweimal benötige, muss ich den kompletten MAX-Teil auch zweimal einfügen/ Berechnen.
Wenn Du Dir die komplette Formel (oben) noch mal anschaust, siehst Du, dass der fett gezeigte Teil zweimal identisch vorkommt.
So! 135 ist also die Zeile, in der das gesuchte Bigramm steht.
Um das jetzt zu finden gibt es die INDEX-Funktion.
Im Prinzip lautet die in dem Beispiel:
=INDEX(AR1:AR999;135)
Also: Gib aus dem Bereich AR1:AR999 die 135te Zelle zurück.
Fertig.
Ich hoffe ich konnte das einigermaßen "rüberbringen".
Du darfst natürlich gerne nochmal gezielt nachfragen.

Kommentar von Blacky2000 ,

Vielen Dank, nach mehrmaligem durchlesen habe ich´s dann verstanden.

Aber habe noch ne Frage: Wenn ich beim ersten Durchsuchen mit "J" z.B. "JW" bekommen habe, dann nochmal nach "J" suche, will ich "JS" erhalten, was ja der zweithöchste Wert ist, bzw. einfach nicht "JW" ist, wie macht man das am besten?

Kommentar von Oubyi ,

Dann diese Formel und runterkopieren:

{=WENNFEHLER(INDEX($AR$1:$AR$999;(KGRÖSSTE(WENN(LINKS($AR$124:$AR$999;1)=$AY$123;$AS$124:$AS$999+ZEILE($AS$124:$AS$999)*0,001);ZEILE(A1))-GANZZAHL(KGRÖSSTE(WENN(LINKS($AR$124:$AR$999;1)=$AY$123;$AS$124:$AS$999+ZEILE($AS$124:$AS$999)*0,001);ZEILE(A1))))*1000);"")}

Bzw. für den zweiten Buchstaben:

=WENNFEHLER(INDEX($AR$1:$AR$999;(KGRÖSSTE(WENN(TEIL($AR$124:$AR$999;2;1)=$AY$123;$AS$124:$AS$999+ZEILE($AS$124:$AS$999)*0,001);ZEILE(A1))-GANZZAHL(KGRÖSSTE(WENN(TEIL($AR$124:$AR$999;2;1)=$AY$123;$AS$124:$AS$999+ZEILE($AS$124:$AS$999)*0,001);ZEILE(A1))))*1000);"")

Klappt es?

Kommentar von Oubyi ,

Danke fürs

Kommentar von Blacky2000 ,

Also ich weiß natürlich nicht ganz genau was die Formel machen soll (z.B. was in A1 soll oder spielt das keine Rolle?), zumindest gibt sie alle Kombinationen aus die mit "J" beginnen, was soweit auch gut ist und funktioniert.

Das heißt aber auch das ich selber auswählen muss, den wie vielten Wert ich nehme (bei dem Beispiel von oben müsste ich für "JS" den zweiten Wert nehmen und "JW" wäre der erste, welchen ich bereits verwendet habe), ich kann das zwar recht einfach ablesen, jedoch möchte ich das ganze ja automatisch haben.

Wenn ich jetzt einen anderen Text entschlüssel wird natürlich auch wieder der zweite Wert genommen, wie ich es ja für "JS" ausgewählt habe. Jedoch habe ich bei dem zweiten Text den ersten Wert noch nicht verwendet so das ich eine 1 hätte eintragen müssen.

Ich hoffe das man das alles einiger Maßen verstehen konnte. Punkt ist: Ich brauche eine Formel, welche mir das Bigramm welches mit "J" anfängt und den größten Wert hat, jedoch nicht in einer Zeile steht wo ich alle bisher entschlüsselten Bigramme rein schreiben würde ausgibt. Ist das auch möglich?

Expertenantwort
von Iamiam, Community-Experte für Excel, 55

nur zum Prinzip: Die Cäsar-Verschlüsselung ist die Einfachste, wenn man das Alphabet nimmt, aber natürlich auch die Unsicherste..

+3 zB: A=>D, B=>E, C=>F usw. und dann natürlich rückwärts entschlüsseln. Hat man mal ein einziges Buchstabenpaar, nimmt man einfach

=Zeichen(Code(Teil(A1;n;1)+3), allerdings ergeben sich Komplikationen am Übergang A=>Z. n ist eine Laufzahl, die man zB durch Spalte() simulieren kann.

Im Prinzip ist auch Rot13(...) eine Cäsar-Verschlüsselung

(diese Funktion  gibts glaube ich aber nur in OO/LO)


Zum ersten Teil der Frage: Beim benutzerdefinierten Filter gibts m.W die Oprton: beginnt mit zB "J"
Ansonsten eine Matrixformel, aber erst heut abend wieder, dann ausführlicher, wenn noch keine erschöpfende AW da wäre.

Kommentar von Blacky2000 ,

=Zeichen(Code(Teil(A1;n;1)+3), allerdings ergeben sich Komplikationen am Übergang A=>Z. n ist eine Laufzahl, die man zB durch Spalte() simulieren kann.

Wenn man mit 3 verschlüsseln will, kann man einfach die stelle im Alphabet bei A ist es 1, mit dem Schlüsseladdiert dann überprüft ob die Summe größer als 26 ist, wenn ja dann einfach 26 subtrahieren und diese Zahl dann mit 64 addiert hat man 68 dies dann über "ZEICHEN" umwandeln lässt, kann man alle Verschieben ohne das Komplikationen auftreten.

Aber du hast auch meine letzen Zeilen falsch verstanden, mit "Cäsar Verschlüsselung mit Schlüsselwort" ist gemeint das man ein Schlüsselwort an den Anfang des Alphabets schreibt, und es dann fortsetzt ohne Buchstaben zu wiederholen, was heißt das sich die Verschiebung immer mal wieder ändert.

Zum Filter: Wie soll ich das machen der muss (automatisch) 26 mal filtern und das gefilterte Bigramm immer in eine andere Zelle schreiben, da weiß ich nicht wie die Filter Funktion das machen sollte?

Ok, würde mich dann freuen wenn du mir das mit der Matrixformel dann erklärst, hört sich gut an ;)

Kommentar von Iamiam ,

{Fo} kommt noch. Erst mal: ich hab die Themen ergänzt um VBA und Java, vllt findet sich jemand, der diesen Link übersetzen kann:

www.jgiesen.de/javascript/JavaScript/JSBeispiele/JavaScript32/caesar\_wort4.html

So wenig verstehe ich von anderen Programmen, dass ich nicht mal Java und Javascript auseinanderhalten kann. Bin nur dank Oubyis Bemerkung draufgekommen. Werde Thema nochmals ergänzen.

Kommentar von Iamiam ,

so, hier die kürzere Matrixformel, in G2 (kannst später den ganzen Block so ver4schieben, wie Du ihn haben willst):

Text in F2: in Zeile

G2: {=KKLEINSTE(WENN(LINKS($AR$124:$AR$187;1)="J";ZEILE($AR$124:$AR$187));ZEILEN(H$2:H2))}

h2: steht:

i2: =INDEX($AR:$AR;G2)

optional:

J2: =ZÄHLENWENN($AR$124:$AR$187;"J*") <Anzahl der Treffer

und die Formel als Ganzes (eigentlich überflüssig)

K2: {=INDEX($AR:$AR;KKLEINSTE(WENN(LINKS($AR$124:$AR$187;1)="J";ZEILE($AR$124:$AR$187));ZEILEN(K$2:K2)))}

alles runterkopieren, bis Fehler #ZAHL! erscheint (≙Zählenwenn+1)

Erklärungen und Hinweise findest Du in meiner Antwort zu

www.gutefrage.net/frage/excel-zellenwert-uebersteigt-eine-zahl---wie-anzeigen-la...

das wiederhole ich hier jetzt nicht nochmals.

Kommentar von Iamiam ,

Du kannst Dir natürlich auch
=INDEX($AS:$AS;G2)
(zusätzlich) ausgeben lassen, sowie weitere Spalten.

Kommentar von Blacky2000 ,

OK, danke auch das hat funktioniert, habe es aber erst nach dem hochladen der Tabelle ausprobiert, aber auch an dich die Frage: wie kann ich nach dem zweiten Buchstaben suchen?

Kommentar von Iamiam ,

Genauso wie bei Oubyi: anstatt
LINKS($AR$124:$AR$187;1)="J"

Teil($AR$124:$AR$187;2;1)="J"
  (ein Argument mehr, da Start und Länge definiert werden müssen. Das geht auch bei längeren Texten.

Stattdessen bei Zweiergruppen RECHTS(..) zu verwenden, bringt hier kaum Vorteil.

Dein Problem interessiert mich zwar weiterhin, aber ich habe praktisch keine Kenntnisse in Kryptografie.

Dein Ansatz mit Zweiergruppen-wahrscheinlich-Häufigkeit ist vermutlich richtig, wenn Du entsprechende Häufigkeitstabellen hast.

Auch Dreiergruppen treten vermehrt auf für der die das ein und ich sie ihr wir von, wenn das Leerzeichen gesetzt ist, aber es gibt natürlich auch gab, sah, man, her, usw.

Kommentar von Blacky2000 ,

Ok, danke, aber warum funktionieren diese Formeln nur als Matrix Formel und nicht als ganz normale? Wo z. B. mit der Matrixformel SI rauskommt, kommt mit der normalen Formel LJ... 

Habe mal n bisschen gegooglet und habe nur gefunden das die mehrere Berechnungen gleichzeitig durchführen und mit mehreren Wertsätzen arbeiten, aber warum machen normale Formeln das nicht, bzw. was passiert mit anderen Funktionen, wenn man sie als Matrixformel eingibt?

Kann man das so beantworten oder soll ich dafür lieber eine eigene Frage stellen?

Kommentar von Iamiam ,

P. Haserodt hat das ausgezeichnet erklärt in

www.online-excel.de/excel/singsel.php?f=26

Das würde den Umfang einer einzelnen Frage bei weitem übersteigen, das solltest Du wirklich auf seine "Stationen" aufgeteilt lernen! (hab ich übrigens 20 xl-Jahre lang nicht gekannt und erst hier kennen- und schätzen gelernt!)

Antwort
von dkilli, 37

Die Cäsar-Verschlüsselung kannst du folgendermaßen machen:

In der Spalte A gibst du die Buchstaben A-Z ein. In D1 um wie viele Buchstaben verschoben werden soll. Kann positiv oder negativ sein!

In B1 gibst du ein:

=ZEICHEN(REST(CODE(A1)-65+$D$1;26)+65) 

Um wieder zu entschlüsseln in C1

=ZEICHEN(CODE(B1)-$D$1)

Diese Formeln nach unten ziehen bis Z

Wenn du die Zahl nicht kennen solltest, so verschlüssel einen langen Text. Im Deutschen kommt der Buchstabe E am häufigsten vor. Also im verschlüsselten Text nach den Buchstaben suchen, der am häufigsten vorkommt. 

Kommentar von Blacky2000 ,

Ich will "Cäsar Verschlüsselung mit Schlüsselwort" knacken! NIcht die einfache, die sollte jedes Kind hinkriegen, es reicht mir wenn du mir sagst wie ich durch eine Formel die Tabelle, nach Bigrammen filtern kann und diese dann ausgegeben werden

Mit "Cäsar Verschlüsselung mit Schlüsselwort" ist gemeint das man ein Schlüsselwort an den Anfang des Alphabets schreibt, und es dann fortsetzt ohne Buchstaben zu wiederholen, was heißt das sich die Verschiebung immer mal wieder ändert. Also wenn das Schlüsselwort "Montag" ist sieht das ganze so aus:

A B C D E F G H I J K L M N O P Q R S T U V W X Y Z
M O N T A G H I J K L P Q R S U V W X Y Z B C D E F

 

Kommentar von Iamiam ,

eine Anweisung für Java hab ich da gefunden:

http://www.jgiesen.de/javascript/JavaScript/JSBeispiele/JavaScript32/caesar\_wor...

Das nach VBA zu übersetzen, sollte sogar für mich möglich sein, wenn ich Stunden investiere. Vllt kanns jemand anderes aus dem ff, sozusagen instant?

Kommentar von Oubyi ,

Das ist JavaScript, nicht Java und das Programm entschlüsselt den Text nur, wenn das Schlüsselwort und die -zahl bekannt sind.
Ich habe Blacky aber so verstanden, dass er die Verschlüsselung "knacken" will, also ohne das Schlüsselwort zu kennen.

Kommentar von Blacky2000 ,

Ja, hast mich richtig verstanden Oubyi

Keine passende Antwort gefunden?

Fragen Sie die Community

Weitere Fragen mit Antworten