automatisches Ausfüllen meherer Informationen

[Kevvv]

Guten Abend Ihr Cracs da draussen.

Ich bin gerade dabei, meine Netzwerk-Technik zu Hause auf Stand zu bringen, habe hier 6 Patch-Panels und will aus einer Liste,
die im selben Dokument ist (anderes Tab) die Daten ausfüllen.

Zum Verständnis:
Ich habe eine Datei, die drei Tabellen hat:
- Devices - hier stehen alle Gerätedaten / IP / Anschlussdaten drin
- Netzwerk_Uebersicht - Hier sind alle Verbindungen innerhalb des Hauses etc. zusammen gefasst
- Patch-Panels - hier würde ich gern einfach eine automatisierte Ansicht der Patchfelder generieren

Ich habe in der Tabelle: "Netzwerk_Uebersicht" diese Info's, JEDE in einer eigenen Spalte:
- Kabel-Nummer
- Gebäudeteil
- Raum
- Position
- Dosen-Nummer
und ich gebe jedem dieser Daten die Info, auf welchem Panel und welche Port-Nummer das Kabel nun aufläuft - jeweils in einer eigenen Spalte.


erste Aufgabe:

Nun habe ich in der Tabelle "Patch-Panels" 6 Patch-Panels "gezeichnet" (also nur Rahmen erstellt) und möchte nun,
das je nach Information aus den Spalten "Panel" und "Port" in das jeweilige Kästchen (nur eines pro Port) untereinander die
a) Kabel-Nummer
b) Gebäudeteil | Raum
c) Position | Dosennummer
steht.

Beispiel:
- Kabel-Nummer: "1501"
- Gebäudeteil: "alt"
- Raum: "Wohnzimmer"
- Position: "neben Fenster"
- Dosennummer: "1"
- Patchfeld: "2"
- Port: 6

Dann wäre es toll, wenn nun in dem Kästchen für Patchfeld 2 / Port 6 diese Info's drinne stehen würden:
1501
alt | Wohnzimmer
neben Fenster | 1

Ich suche mir schon ne weile den Wolf im Netz, ich nutze normalerweise Wenn, Dann , und kann total super Zahlen zusammen rechnen und so...
Aber nach abhängigkeit der Zahlen einen Text in einer anderen Tabelle einfügen ... bekomme ich nicht hin...


und zweite Aufgabe:
Wenn ich nun in den Devices neben meinen Geräten wieder die Spalte Panel und welche Port-Nummer die Geräte nun angeschlossen sind.
Darüber hinaus gibts noch eine Spalte PoE mit der Option : ja oder nein

Ich würde mich nun freuen, wenn die Panels in dem Tab "Patch-Panels" diese drei Farben annehmen könnten, abhängig von diesen Regeln:
- wenn KEIN Gerät angeschlossen ist ==> dann den Port hellgrau hinterlegen
- wenn ein Gerät angeschlossen ist und kein PoE ==> dann den Port hellgrün hinterlegen
- wenn ein Gerät angeschlossen ist und MIT PoE ==> dann den Port hellorange hinterlegen


Ich weiss, das ist sicherlich nicht ganz einfach, aber ich bin mir sicher, das einige flauen Füchse unter Euch nur müde lächeln.
Ich stehe jedenfalls tierisch auf dem Schlauch...

Vielen DANK für Eure Unterstützung!!
beste Grüße aus dem Westerwald, Kevin

[Gast]

lade mal eine Demo-Datei hoch

[Kevvv]

Gerne, hier mal eine abgespeckte Version der Datei: https://www.dropbox.com/s/c6oexlshr6it2k9/IP-Netzwerk-Beispiel.xlsx?dl=1

[Raoul21]

Hi Kev

hast viel Arbeit in Deine Fragestellung gesteckt, aber: Deine Angaben passen nicht zu Deiner Datei:
Kabelnummern fehlen
Spalten "Panel" und "Port" passen nicht zu Port und Reihen.
Du schreibst von Raum: "Wohnzimmer". Gibt es aber nicht in Deiner Tabelle.
Wann ist kein Gerät angeschlossen, wann ist kein PoE?
usw.

Du hast sicher Hintergrundinfo, ich nicht. Daher müssen die Begriffe in Deiner Beschreibung richtig sein und mit der Musterdatei übereinstimmen.

Du willst ein richtiges Ergebnis, also musst Du genau beschreiben. Eine Beispiellösung würde schon viel erklären.

Deine Tabellen sollen keine durchgehende Leerspalten oder Leerzeilen haben, wie zB Blatt "Netzwerk_Uebersicht", Spalte F, oder "Devices", Zeile 5, Spalte I.

Bitte überarbeite deine Datei und passe daran die Begriffe in der Beschreibung an.

Gruss, Raoul

[Kevvv]

Hi Raoul , danke für die Rückmeldung.
In der Tat ist die Tabelle noch nicht wirklich konsistent gewesen ... ich hab viel mehr Zeit investiert um irgendwie die Verbindungen hin zubekommen ... leider ohne Erfolg.

Ich habe nun das Beispiel Dokument noch mal angepasst, habe mal das Patch-Panel mit meiner Zielvorstellung als Beispiel händisch "Verbunden" > also ausgefüllt.
Dabei ist mir aufgefallen, das ich noch ein paar minimale Anpassungen vornehmen möchte.
1.
Ich habe nun unter "Devices" die Farbgebung des jeweiligen Ports angegeben.
PoE nein = hell-Grün
PoE ja = Orange
RS232 = hell-Blau
DLS = gelb
kein device an Port = hell-Grau
Port nicht verwendet > also kein Kabel aufgelegt = weiß

2. wäre es toll, wenn die Kabel-Nummer größer dargestekkt werden könnte.


An sonsten hoffe ich, das dies nun so sehr viel mehr sinn macht.
Hier die neue Datei: https://www.dropbox.com/s/py8vf0cxpi7913z/IP-Netzwerk-Beispiel_neu.xlsx?dl=1

Sonnige Grüße, Kevin

[Raoul21]

Hi

hier mal eine Anleitung zum Formel-Erstellen.
Folgende Formeln funktonieren im Rasterbereich im Blatt "Patch-Panels".
Einfach in eine der Zellen kopieren und der richtige Wert wird angezeigt. Abhängig von der Position im Raster wird der passende Wert in den anderen Tabellen gesucht.

Die Kabelnummer liefert
=WENNFEHLER(INDEX(Netzwerk_Uebersicht!$A:$A;VERGLEICH(1;MMULT((Netzwerk_Uebersicht!$G:$G=ZEILE()-2)*(Netzwerk_Uebersicht!$H:$H=SPALTE()-1);1);0));"")

Die Position im Gebäude liefert:
=WENNFEHLER(INDEX(Netzwerk_Uebersicht!$B:$B;VERGLEICH(1;MMULT((Netzwerk_Uebersicht!$G:$G=ZEILE()-2)*(Netzwerk_Uebersicht!$H:$H=SPALTE()-1);1);0));"")

Den Raum liefert:
=WENNFEHLER(INDEX(Netzwerk_Uebersicht!$C:$C;VERGLEICH(1;MMULT((Netzwerk_Uebersicht!$G:$G=ZEILE()-2)*(Netzwerk_Uebersicht!$H:$H=SPALTE()-1);1);0));"")
usw.
Zusammenstellen, kopieren und formatieren kannst Du selbst.

Hier gibt es günstig Formeln.
https://www.herber.de/excelformeln/src/ ... la_list.pl
(Abschnitt "Verweisfunktionen")

Makro kann ich auch schreiben. Aber probier es mal mit den Formeln.

Gruass, Raoul

[Kevvv]

Wooohoooo! Danke Raoul!
Nachdem ich mir ein paar Minuten die Formel angesehen habe, fiel es mir wie Schuppen von den Augen.
Klar, die Fleißarbeit mach ich natürlich selber.

Was ich mich nun aber Frage, bzw. auch an Google gefragt habe:
1. wie kombiniere ich nun mehrere Formeln und schreibe diese in mit "ALT+Enter" (=Zeilenumbruch) in das selbe Feld
2. wie bekomme ich den Trenner | zwischen den einzelnen Ausgabewerten hin
und 3. wie kann ich die Ausgabe der Kabelnummer vom Font her Größer machen als den Rest?

Du siehst ... Du sprichst mit einem total blutiger Anfänger mit großen Träumen...

[Gast]

Ahhhh ... ich bin sooo happy!
Wie man sich doch freuen kann!!

Dies ist die Formel um Zeilenumbruch etc hin zu biegen. Die magischen Worte sind: Zeichen(10) = Zeilenumbruch, Zeichen(124) = | und Zeichen(32) = Leerzeichen

=WENNFEHLER(INDEX(Netzwerk_Uebersicht!$A:$A;VERGLEICH(1;MMULT((Netzwerk_Uebersicht!$G:$G=ZEILE()-2)*(Netzwerk_Uebersicht!$H:$H=SPALTE()-1);1);0));"")&ZEICHEN(10)&WENNFEHLER(INDEX(Netzwerk_Uebersicht!$B:$B;VERGLEICH(1;MMULT((Netzwerk_Uebersicht!$G:$G=ZEILE()-2)*(Netzwerk_Uebersicht!$H:$H=SPALTE()-1);1);0));"")&ZEICHEN(32)&ZEICHEN(124)&ZEICHEN(32)&WENNFEHLER(INDEX(Netzwerk_Uebersicht!$C:$C;VERGLEICH(1;MMULT((Netzwerk_Uebersicht!$G:$G=ZEILE()-2)*(Netzwerk_Uebersicht!$H:$H=SPALTE()-1);1);0));"")&ZEICHEN(10)&WENNFEHLER(INDEX(Netzwerk_Uebersicht!$D:$D;VERGLEICH(1;MMULT((Netzwerk_Uebersicht!$G:$G=ZEILE()-2)*(Netzwerk_Uebersicht!$H:$H=SPALTE()-1);1);0));"")&ZEICHEN(32)&ZEICHEN(124)&ZEICHEN(32)&WENNFEHLER(INDEX(Netzwerk_Uebersicht!$E:$E;VERGLEICH(1;MMULT((Netzwerk_Uebersicht!$G:$G=ZEILE()-2)*(Netzwerk_Uebersicht!$H:$H=SPALTE()-1);1);0));"")&ZEICHEN(10)&WENNFEHLER(INDEX(Devices!$B:$B;VERGLEICH(1;MMULT((Devices!$G:$G=ZEILE()-2)*(Devices!$H:$H=SPALTE()-1);1);0));"")

So, als nächstes gehts um die größe der Schrift und Farbe der Ports ... weiter gehts...

[Gast]

hmmm ... mit der Bedingten Formatierung komm ich nur so weit, das dann eine ganze Zeile die Farbe annimmt.
Es fehlt also eine Prüfung für Patchpanel und Port um die Farbe auch nur in dem jeweiligen Feld korrekt zu setzten ... hmmm ...

[Kevvv]

Ohhh jeeh, ich glaub, ich mach Feierabend... :-/

Ich habe nun diese Version versucht zu erstellen, jetzt klappt aber die Zuordnung der Zellen und Spalten nicht mehr.
Hintergrund ist der: da ich die Kabelnummer gerne größer dargestellt haben möchte und das wohl scheinbar nicht in einer Zelle geht,
habe ich einfach noch eine weitere Spalte pro Patchfeld beigefügt.
In die eine Zeile suche ich nun nur nach der Kabelnummer:
=WENNFEHLER(INDEX(Netzwerk_Uebersicht!$A:$A;VERGLEICH(1;MMULT((Netzwerk_Uebersicht!$G:$G=ZEILE()-2)*(Netzwerk_Uebersicht!$H:$H=SPALTE()-1);1);0));"")


und in der anderen Zeile dann jeweils den Rest:
=WENNFEHLER(INDEX(Netzwerk_Uebersicht!$B:$B;VERGLEICH(1;MMULT((Netzwerk_Uebersicht!$G:$G=ZEILE()-2)*(Netzwerk_Uebersicht!$H:$H=SPALTE()-1);1);0));"")&ZEICHEN(32)&ZEICHEN(124)&ZEICHEN(32)&WENNFEHLER(INDEX(Netzwerk_Uebersicht!$C:$C;VERGLEICH(1;MMULT((Netzwerk_Uebersicht!$G:$G=ZEILE()-2)*(Netzwerk_Uebersicht!$H:$H=SPALTE()-1);1);0));"")&ZEICHEN(10)&WENNFEHLER(INDEX(Netzwerk_Uebersicht!$D:$D;VERGLEICH(1;MMULT((Netzwerk_Uebersicht!$G:$G=ZEILE()-2)*(Netzwerk_Uebersicht!$H:$H=SPALTE()-1);1);0));"")&ZEICHEN(32)&ZEICHEN(124)&ZEICHEN(32)&WENNFEHLER(INDEX(Netzwerk_Uebersicht!$E:$E;VERGLEICH(1;MMULT((Netzwerk_Uebersicht!$G:$G=ZEILE()-2)*(Netzwerk_Uebersicht!$H:$H=SPALTE()-1);1);0));"")&ZEICHEN(10)&WENNFEHLER(INDEX(Devices!$B:$B;VERGLEICH(1;MMULT((Devices!$G:$G=ZEILE()-2)*(Devices!$H:$H=SPALTE()-1);1);0));"")


Nun stimmt aber quasi nix mehr. Die Werte stehen in den falschen Boxen ... und ich hab keine Idee warum...

Und ... ich hab Berechnungsprobleme: Wenn ich einen Wert ändere schreibt Excel: Berechnung wird ausgeführt (12 Prozessor(en) ... und das Ding schraubt sich nen Wolf...
Überlaste ich mit den 6 x 24 x 2 Formeln = 288 Berechnungen schon den Rechner??
Und ich hab noch garkeine Farbenauswahl dabei ...

Hier ist die aktuelle Tabelle: https://www.dropbox.com/s/drnt1nk36383lsa/IP-Netzwerk-Beispiel_neu2.xlsx?dl=1

Ich freue mich über jede Hilfe... !!

[HKindler]

Hi,

ich habe es selten erlebt, dass man es schafft mit 288 Formeln Excel bei einer Neuberechnung für über eine Minute zu beschäftigen. Respekt!

Kleine Anmerkung zu deinen Problemen:
Zeichen(32) und Zeichen(124) brauchst du nicht. Du kannst statt dessen auch " " und "|" verwenden. Aus deinem &ZEICHEN(32)&ZEICHEN(124)&ZEICHEN(32)& wird damit ein wesentlich einfacheres und auch verständlicheres &" | "&.

Die Ausgabe einer Formel lässt sich nicht teilweise mit einer anderen Schrift formatieren. Das kannst du nur für die gesamte Zelle machen. Daher musst du das, was du fett haben willst in einer eigenen Zelle Ausgeben.

In jeder deiner Formeln taucht gefühlte zweiunddrölfzig mal der Ausdruck VERGLEICH(1;MMULT((Netzwerk_Uebersicht!$G:$G=ZEILE()-2)*(Netzwerk_Uebersicht!$H:$H=SPALTE()-1);1);0) auf. Das ist auch der Grund wieso das so lange dauert. Weshalb berechnest du den Vergleich unnötig oft? Der gehört einmal in einer Zelle ausgerechnet, und dann wiederverwendet.

Und wieso gehst du bei dem Vergleich über komplette Spalten? Wenn du das auf die notwendigen Zellen einschränkst (nämlich auf G1:G22 und H1:H22 statt G:G und H:H), dann sinkt bei mir die Rechenzeit von ca. 64 Sekunden auf unter 10 Sekunden. Und das ohne sonstige Umstellungen nur durch Suchen und Ersetzen von Netzwerk_Uebersicht!$G:$G=ZEILE()-2)*(Netzwerk_Uebersicht!$H:$H durch Netzwerk_Uebersicht!$G1:$G22=ZEILE()-2)*(Netzwerk_Uebersicht!$H1:$H22 in allen Formeln.

Wenn du meinen Tipp mit den Hilfszellen auch noch mitnimmst, dann landet man bestimmt bei unter 1 Sekunde. Bin nur zu faul das alles selbst umzustellen.

Nachtrag:
Deine Probleme mit den falschen Werten rühren daher, dass ZEILE() die immer die aktuelle Zeilenummer zurückgibt. Wenn du nun eine zweite Zeile einfügst, dann musst du das natürlich berücksichtigen. Daher kommt ja auch das ZEILE()-2 in deiner Formel. Denn die Formel steht in Zeile 3, braucht an der stelle aber den Wert 1. Das selbe ergibt sich mit der Spalte: Du brauchst ein SPALTE()-1 weil deine Formeln in Spalte B (Spalte 2) starten, an der Stelle aber die 1 gebraucht wird.

[Kevvv]

Hallo Helmut,
vielen herzlichen Dank, das Du mir bei meinen Versuchen hilfst. Das Kompliment bezüglich der Rechendauer von Excel lass ich mal ... so im Raum stehen.

Das mit den Zeichen hab ich verstanden... Warum soll ich es einfach machen, wenns auch kompliziert geht ...

Um Ehrlich zu sein, lese ich deine Nachricht nun schon zum 5. Mal, versuche dahinter zu steigen ... scheitere aber an meinem beschränkten Verständnis.
Was meinst Du mit " Weshalb berechnest du den Vergleich unnötig oft? Der gehört einmal in einer Zelle ausgerechnet, und dann wiederverwendet. " ?
Also, ich verstehe, was Du schreibst, aber ich habe keinerlei Ahnung, wie ich das umsetzten muss... Im Grunde hab ich einfach das gemacht, was augenscheinlich funktionierte.

Wie Du in den Nachrichten vorher vielleicht mitbekommen hast, bin ich soweit nur gekommen, da mit Raoul schon richtig fett unter die Arme gegriffen hat (DANKEEE dafür!!)
Wie müsste ich denn nun die Formel umbauen, das dies alles funktioniert? Ich steh wie ein Ochs vor'm Berg...

Beste Grüße, Kevvv

[HKindler]

Hi,

ehrlich gesagt habe ich nicht versucht zu verstehen, was die Formel eigentlich ausrechnet. Dazu habe ich auch keine Lust - zumindest heute nicht mehr. Was ich versucht habe dir klar zu machen: wenn idueine Formel wie =Index(A:A;Vergleich(xxxx))&Index(b:b;Vergleich(xxx))&index(c:c;Vergleich(xxx)) hast, dann solltest du wissen, dass Vergleich(xxx) ziemlich rechenaufwändig ist. Du berechnest aber 3x denselben Wert. Daher ist es besser diese Berechnung in eine eigene Zelle auszulagern und dann einfach darauf zuzugreifen. Du hast dann also z.b. in X1 =Vergleich(xxx) stehen und in X2 dann =Index(a:a;x1)&index(b:b;x1):index(c:c;x) Damit hast du dann nur noch 1/3 der Rechenzeit.

Dein Problem ist jetzt nur noch, dass du (xxx) ändern musst, da die bisherige Formel davon abhängig ist, in welcher Zelle sie steht. Und ich habe heute Abend wirklich nicht mehr den Nerv, da groß drüber nachzudenken.

Und sorry falls sich Tippfehler. Eingeschlichen haben, ich schreibe das gerade unter iOS - da ist die Eingabe von Formeln etwas umständlich.

[Kevvv]

Hallo Gemeinde,

Hallo Helmut,
danke für die Reaktion, ich finds auch gut, das jemand schreibt, das er gerade keinen Bock hat.

Ich habe nun mehrfach versucht, die Formeln so umzustellen, das die Funktionen funktionieren, leider ohne erfolg. Ich bin scheinbar dazu zu blöde...
Vielleicht hast Du ja irgendwann noch einmal bock dich 5 min in das Thema einzufuchsen...

Hier die Tabelle mit meinen kläglichen Versuchen... https://www.dropbox.com/s/tn3vd7re2wmxz8r/IP-Netzwerk-Beispiel_neu3.xlsx?dl=1


Vielleicht hat ja sonst jemand Lust , hier mal drauf zu schauen... ?

Gruß Kevvv

[Raoul21]

Hallo Kevvv

habe Dir eine Formel geschrieben. Sieh dort nochmal nach. Darunter steht dann noch was: Der Hinweis, dass das auch mit Makros lösbar ist. Das wäre aber Einzelanfertigung und für die anderen hier nicht besonders interessant, ist dann aber blitzschnell. Habe Dir auch einen Link mit Zugang zu anderen Formeln geschrieben. Da könntes Du mal reinschauen. Mein Tipp: Versuche, diese Formel, und die anderen im Link, zu verstehen. Vielleicht ist da was dabei. Das hilft Dir auch später bei ähnlichen Problemen.

Gruss Raoul