Live-Forum - Die aktuellen Beiträge
Datum
Titel
26.04.2024 12:46:38
Nutzereingaben Speichern und Laden
26.04.2024 11:49:48
VAB und Bedingte Formatierung nimmt die Farbe im Makro nicht
26.04.2024 11:38:46
Acrobat PDF Formularfelder mit Excel ausfüllen
26.04.2024 11:23:04
zählen wenn - aber nur die selektierten Zeilen
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1180to1184
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1180to1184
1180to1184
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Sverweis bei verbundenen Zellen

Sverweis bei verbundenen Zellen
Stein
Hallo,
ich habe ein kleines Problem mit einer sverweis-Formel:
Beispiel:
In Tabelle TB1 sind jeweils 3 Zellen in Spalte A miteinander verbunden (A1-A3, A4-A6, usw.).
Wenn ich die Formel sverweis("A";'TB1!'A1:D15;3;falsch) schreibe und sich das Suchkriterium "A" beispielsweise in der Zelle A1 von Tabelle TB1 befindet, gibt er mir den Wert aus C1 zurück. Soweit so gut.
Doch wie muss die Formel aussehen, damit ich den Wert aus Zelle C3 zurück erhalte?
Gruss Stein
AW: Sverweis bei verbundenen Zellen
19.10.2010 12:45:18
BoskoBiati
Hallo,
=index(C:C;Vergleich("A";TB1!A1:D15;3;0)+2)
wobe ich lieber auf die verbundenen Zellen verzichten würde.
Gruß
Bosko
AW: Sverweis bei verbundenen Zellen
19.10.2010 13:18:17
Stein
Hallo Bosko
...wobei ich lieber auf die verbundenen Zellen verzichten würde
Werde ich wohl machen müssen, denn bei dem Versuch, Deinen Vorschlag umzusetzen, erhalte ich die Fehlermeldung, dass Arrayformeln nicht in verbundenen Zellen verwendet werden können.
Gruss Stein
wieso Arrayformel?
19.10.2010 13:36:51
Rudi
Hallo,
hat doch keiner von geschrieben.
Gruß
Rudi
AW: wieso Arrayformel?
19.10.2010 14:16:12
Stein
Stimmt! Wieso eigentlich?
Ich hatte vor kurzer Zeit im Zusammenhang mit der Index-Formel als Matrixformel zu tun. Wahrscheinlich deshalb nicht so genau hingesehen.
Gruss Stein
Anzeige
AW: Sverweis bei verbundenen Zellen
19.10.2010 13:15:04
Klaus
Hallo Stein,
wenn die Zellen C1-C3 verbunden sind, haben sie den identischen Inhalt.
Ansonsten geht das mit "BEREICH.VERSCHIEBEN" und / oder INDEX.
Alternative:
Verzichte auf verbundene Zellen.
Grüße,
Klaus M.vdT.
AW: Sverweis bei verbundenen Zellen
19.10.2010 13:26:35
Stein
Hallo Klaus,
wenn die Zellen C1-C3 verbunden sind, haben sie den identischen Inhalt.

Das hatte ich mir auch gedacht. Nur, selbst, wenn ich in allen drei Zellen untereinander denselben Inhalt einfüge (z.B. mit =C1), hält die sverweis-Funktion bei dem ersten Treffer (C1) an und geht in die entsprechende Zelle in der Spalte D. Alle weiteren (C2 und C3) werden ignoriert, sofern sie den gleichen Inhalt (Wert) wie C1 haben.
Aber vielleicht muss ich mir da noch eine Hilfestellung mit "Verketten" o.ä. basteln.
Ansonsten geht das mit "BEREICH.VERSCHIEBEN" und / oder INDEX.
Was meinst Du mit "BEREICH.VERSCHIEBEN"?
Einen Vorschlag mit INDEX hatte mir Bosko schon gemacht. Funktioniert aber nicht, da Arrayformeln nicht in verbundenen Zellen verwendet werden können (Fehlermeldung).
Gruss Stein
Anzeige
Mustertabelle bitte.
19.10.2010 13:39:46
Klaus
Hallo Stein,
ich kann mir deinen Tabellenaufbau nicht vorstellen. Lad mal ein Muster hoch, dann bekommen wir das sicher hin.
Achso: und verzichte in Zukunft auf verbundene Zellen!
Grüße,
Klaus M.vdT.
AW: Mustertabelle bitte.
19.10.2010 15:26:30
Stein
Hallo Klaus,
ich habe mal eine Tabelle in Kurzform eingestellt: 

https://www.herber.de/bbs/user/71970.xls

Es kann sein, dass die Datei Makros ausführen will. Aber die brauchst Du nicht.
Die Tabelle ist für eine Überprüfung von Bauteilen und deren Anschlüsse gedacht. Ist die Angabe unter C8, C12 oder C16 falsch, sollen in den entsprechenden Zeilen darunter (C10, C15 bzw. C19) die korrigierten Daten eingegeben werden und die falsche Angabe wird durchgestrichen.
Wie Du wahrscheinlich erkennen kannst, möchte ich die Daten aus dem ersten Tabellenblatt ("Bearbeitungsliste Vorort"), die sich in den Zeilen 8-11, 12-15 und 16-19 befinden, jeweils in die richtigen Zellen der jeweiligen Tabellenblätter "kopieren". Und das in Abhängigkeit vom Tabellenblattnamen (=Identnummer, => Tabellenblätter 2-4).
Dies gilt auch für die zusätzlichen Angaben zu den jeweiligen AKZs (z.B. "oben rechts") und den Anschlüssen/Kl.
Gruss Stein
Anzeige
AW: Mustertabelle bitte.
20.10.2010 09:35:16
Klaus
Hallo Stein,
den Tabellenaufbau könnte man ohne weiteres ohne verbundene Zellen gestalten.
- Überschriften kannst du auch mit "Horizontal über Auswahl zentrieren" ausrichten.
- die Zeilenverdoppelungen (zB in 11TI3459!B11:B12) sind unnötig, einfach die Zelle höher machen
- die Spaltenverdoppelung (zB 11TI3459!A24:B24) sind unnötig, einfach links formatieren
- in "Bearbeitungsliste Vorort!Zeile 3:4" sind teilweise sinnlos Zellen verbunden.
mit einem sauberem Tabellenaufbau hättest du die relativ simple SVERWEIS Formel nutzen können ...
Nun gut. Schreib mal in 11TI3459!C11:C12 folgende Formel:
=BEREICH.VERSCHIEBEN('Bearbeitungsliste Vorort'!D1;3-1+VERGLEICH(C10;'Bearbeitungsliste Vorort'!D:D);1)
das zieht die "zusätzliche Angabe" 01A - 04H aus der Bearbeitungsliste. Analog gehst du für die anderen Verweise vor.
Grüße,
Klaus M.vdT.
Anmerkung: massiver Einsatz von Bereich.Verschieben macht die Tabelle irre langsam.
Anzeige
AW: Mustertabelle bitte.
20.10.2010 12:04:49
Stein
hallo Klaus,
danke für die Formel. Funktioniert. Ich werde sie versuchen zu "analysieren", also ihre Funktionsweise zu verstehen, da ich von der Funktion "Bereich.Verschieben" bislang noch nicht gewusst habe. Aber dazu geht man ja in so ein Forum wie dieses hier, um dazu zu lernen, nicht wahr?
Ich habe die Mustertabelle aus dem Zusammenhang gerissen und die Daten ein bisschen angepasst, um die Problematik darzustellen. Für die Formelermittlung hat es ja gepasst, wie man an Deiner Antwort ersehen kann.
Eigentlich befinden sich im weiteren Verlauf dieser Tabellen noch weitere gesplittete Bereiche, weswegen diese Zell- und Spaltenverdopplungen notwendig waren. Dein Vorschlag bezüglich der "Horizontal über Auswahl zentrieren"-Funktion passt ja nur, wenn ich Überschriften nur über waagerechte oder nur neben senkrechte Zellen zentrieren will. Aber nicht im "Blockverbund", wenn also Zellen waagerecht und senkrecht zu einem "Block" zusammengefasst sind.
Gruss Stein
Anzeige
AW: Mustertabelle bitte.
20.10.2010 13:25:03
Klaus
Hallo Stein,
ausreden, nichts als ausreden ;-) Wenn du mehrere Zellen zu einem Blockverbund zusammengefasst haben willst, ist das ein Fall für Power Point und nicht für Excel!
Ich erklär dir mal die Formel soweit ....
BEREICH.VERSCHIEBEN(Bezug;Zeilen;Spalten;Höhe;Breite)
=BEREICH.VERSCHIEBEN('Bearbeitungsliste Vorort'!D1;3-1+VERGLEICH(C10;'Bearbeitungsliste Vorort'!D:D);1)
Die Formel im einzelnen:
=BEREICH.VERSCHIEBEN(
selbsterklärend. Die Formel halt.
'Bearbeitungsliste Vorort'!D1;
Der Bezug. Hier gehts los! Diese Zelle wird quasi als "Zelle 1 ganz links oben" angesehen.
3-1+VERGLEICH(C10;'Bearbeitungsliste Vorort'!D:D)
die Zeilen. Vom Bezug aus wird um X Zeilen nach unten verschoben. Die Verleichfunktion (muss ich nicht erklären, oder?) findet die Zeilennummer, in der das Suchwort steht. Von hier wollen wir noch drei Zeilen nach unten, darum die drei vorweg. Da wir bereits in Zeile 1 starten, ziehen wir nochmal 1 ab und landen schonmal in der richtigen Zeile.
(statt 3-1+Vergleich hätt man natürlich auch 2+Vergleich schreiben können, aber so ist es etwas übersichtlicher und nachvollziehbarer).
;1)
Die Spalten. Das Ziel befindet sich eine Spalte weiter rechts als der Ursprung, darum eins.
Abschließend könnte man noch ;1;1) angeben, damit die Zielmatrix 1 Zelle hoch und 1 Zelle breit ist (also insgesamt eine Zelle). Kann man hier aber getrost weglassen, da die 1 voreingestellt ist.
Lohnt sich aber zu merken, da man damit zB Summenformeln über variable Zellenanzahl erstellen kann ... ist hier aber nicht Thema.
Viel Spaß dabei, die übrigen Formeln zu erarbeiten! Ich wünsche dir von Herzen viel Erfolg, aber du wirst auch viel fluchen und dir die einfache SVERWEIS Formel ersehnen, die hier an den verbundenen Zellen scheitert *harhar*
Grüße,
Klaus M.vdT.
Anzeige
AW: Mustertabelle bitte.
20.10.2010 14:13:44
Stein
Danke, Klaus, für deine ausführlichen Erklärungen!
Das mit der 2 statt 3-1 hatte ich auch schon herausgefunden. Aber warum diese Konstellation, das hatte ich noch nicht ergründen können. Jetzt, nach deiner Erklärung, aber schon. Und es macht Sinn.
Aber es macht keinen Sinn, darüber zu streiten, ob man aus Gründen der Nachvollziehbarkeit (wegen der Reihenfolge der Abarbeitung der Formel) besser -1+3 hinter der Vergleichsfunktion hätte schreiben sollen! Das wäre nämlich unnötiger Quatsch. :-) 
..., aber du wirst auch viel fluchen und dir die einfache SVERWEIS Formel ersehnen, die hier an den verbundenen Zellen scheitert
Da kann ich dir nicht widersprechen. Es wäre sicherlich einfacher.
Aber trage mal in C11 im Tabellenblatt "Bearbeitungsliste" einen Wert (z.B. 11TI3458) ein und benenne das Tabellenblatt 11TI3459 auch entsprechend um (ebenfalls 11TI3458). Dann sollte eigentlich im Tabellenblatt 11TI3458 in C10 weiterhin 11BKj563 stehen. Tut es aber nicht. Um das aber zu erreichen kommst Du mit einer Sverweis-Formel allein nicht weit, weil die Suchzeile drei Zeile höher liegt. Und so muss du doch wieder Funktionen verschachteln. Unabhäng davon, ob verbundene Zellen mit im Spiel sind oder nicht. Dann kann man auch deine BEREICH.VERSCHIEBEN-Funktion verwenden.
Gruss Stein
Anzeige
Danke für die ausführliche Rückmeldung!
20.10.2010 14:40:24
Klaus
freut mich, geholfen zu haben!
(das mit dem "Blatt umbenennen" kann ich aber nicht nachvollziehen)
Funktioniert jetzt
20.10.2010 15:49:50
Stein 

(das mit dem "Blatt umbenennen" kann ich aber nicht nachvollziehen)

Hat sich erledigt. Ich hatte die Parameter in der Formel falsch angegeben, denn er muss ja jetzt in einer anderen Spalte suchen.
Funktioniert jetzt.
Gruss Stein
AW: Mustertabelle bitte.
21.10.2010 08:41:43
Stein
Hallo Klaus,
eine Frage habe ich aber doch noch:
Folgendes Problem:
In der Zelle E11 im Blatt "Bearbeitungsliste Vorort" steht beispielsweise die Zahl 25. Durch die BEREICH.VERSCHIEBEN-Funktion im Blatt 11TI3459 wird mir auch die Zahl 25 übermittelt. Nun möchte ich diese Zahl/Wert mathematisch weiter bearbeiten und 1 subtrahieren.
Gebe ich die Formel wie folgt ein 

=BEREICH.VERSCHIEBEN('Bearbeitungsliste Vorort'!D1;3-1+VERGLEICH(C10;'Bearbeitungsliste Vorort'!D:D); 1) -1
erhalte ich 24.
Soweit in Ordnung. Schreibe ich aber 

=summe(BEREICH.VERSCHIEBEN('Bearbeitungsliste Vorort'!D1;3-1+VERGLEICH(C10; 'Bearbeitungsliste Vorort'!D:D);1);-1)
bekomme ich -1 angezeigt. Warum?
Gruss Stein
Anzeige
Text oder Zahl?
21.10.2010 09:45:50
Klaus
Hallo Stein,
die gefundene 25 warscheinlich ist TEXT und keine Zahl. Behilf dir, indem du mit 1 multiplizierst.
=SUMME(1*Bereich.Verschieben(...);-1)
so sollte es gehen.
Grüße,
Klaus M.vdT.
(wenn ich den Fehler richtig vermute, hat das allerdings nichts mit Bereich.Verschieben zu tuen sondern ist ein generelles Problem. Hat die Zelle ein kleines grünes Fehler-Dreieck?
AW: Text oder Zahl?
21.10.2010 10:00:09
Stein
Hallo Klaus,
die gefundene 25 warscheinlich ist TEXT und keine Zahl.
War auch mein Lösungsansatz. Darum die Umwandlung mit der WERT-Funktion in meiner Lösung. 
Behilf dir, indem du mit 1 multiplizierst.
Funktioniert auch. 
Hat die Zelle ein kleines grünes Fehler-Dreieck?
Nein.
Gruss Stein
Anzeige
Lösung mit Text/Zahl-Umwandlung
21.10.2010 09:52:23
Stein
Habe eine Lösung gefunden:
Die BEREICH.VERSCHIEBEN-Funktion scheint grundsätzlich einen Text zurück zu geben, egal wie die Zelle, in der die Funktion steht, formatiert ist. Wandle ich diesen Test nun in eine Zahl um, kann damit in einer Summenfunktion auch gerechnet werden.
Ich habe die Formel angepasst und nun rechnet sie auch: 
=summe(wert(BEREICH.VERSCHIEBEN('Bearbeitungsliste Vorort'!D1;3-1+VERGLEICH(C10;  'Bearbeitungsliste Vorort'!D:D);1));-3)

Ich verstehe allerdings immer noch nicht, warum sie bei 

=BEREICH.VERSCHIEBEN('Bearbeitungsliste Vorort'!D1;3-1+VERGLEICH(C10; 'Bearbeitungsliste Vorort'!D:D) ;1) -3
richtig rechnet, obwohl dabei keine Umwandlung von Text in eine Zahl stattfindet.
Gruss Stein
Genau genommen ...
21.10.2010 10:17:23
Klaus
... gibt die Funktion weder TEXT noch ZAHL zurück, sondern eine Matrix.
In diesem Fall ist die Matrix halt nur eine Zelle groß.
Schreib mal in einem leeren Blatt:
A1: 1
A2: 2
A3: 3
und irgendwo die Formel
=BEREICH.VERSCHIEBEN(A1;0;0;3;1)
Diese wird 1 als Wert zurück geben.
Summe(1) = 1, oder? Test aufs Exempel: ´
=SUMME(BEREICH.VERSCHIEBEN(A1;0;0;3;1))
ergibt nicht die erwartete 1, sondern 6! Grund: es wird die gesamte Matrix A1:A3 zurück gegeben. Ohne die Summen-Umklammerung kann innerhalb einer Zelle nur der erste Wert der Matrix zurück gegeben werden.
Warum das allerdings mal Zahl und mal Text ist, kann ich dir nicht beantworten. Liegt warscheinlich an den verbundenen Zellen ;-) ;-)
Grüße,
Klaus M.vdT.
AW: Genau genommen ...
21.10.2010 12:01:44
Stein
Hallo Klaus,
ich habe deine Formel in 

=BEREICH.VERSCHIEBEN(A1;0;0;1;3)
geändert, da sonst #WERT! angezeigt wird, denn "BEREICH.VERSCHIEBEN(A1;0;0;Zeilen;Spalten)" :-) 
=BEREICH.VERSCHIEBEN(A1;0;0;3;1)
Diese wird 1 als Wert zurück geben.
(Korrektur: =BEREICH.VERSCHIEBEN(A1;0;0;1;3) )
Ja
Summe(1) = 1, oder?
Ja 
Test aufs Exempel: ´
=SUMME(BEREICH.VERSCHIEBEN(A1;0;0;3;1))
ergibt nicht die erwartete 1, sondern 6!
(Korrektur: =SUMME(BEREICH.VERSCHIEBEN(A1;0;0;1;3)) )
Ja
Warum das allerdings mal Zahl und mal Text ist, kann ich dir nicht beantworten. Liegt warscheinlich an den verbundenen Zellen
Ich habe die Verbindung mal aufgelöst. Ändert aber nicht an dem Problem.
Aber mit dem Umweg über die Umwandlung (WERT-Funktion oder Multiplikation mit 1) ist das Problem ja lösbar.
Gruss Stein

77 Forumthreads zu ähnlichen Themen

Zeilenumbruch bei verbundenen Zellen
oblivion

Hallo, ich habe mal eine Frage zum Zeilenumbruch. Wenn ich eine einzelne Zelle habe und über die Eigenschaften den Zeilenumbruch einstelle, dann wird automatisch die Zelle größer (nach unten) je mehr ich reinschreibe.
Wenn ich jetzt aber zwei oder mehr Zellen verbinde und die gleichen Einst...
22.08.2008 12:42:00
Thread-ID: 1003815
4 Beiträge
Anzeige
Spalten statt Zeilen filtern
Steffen

Hallo Forum,
den Autofilter kenne ich, aber ich brauche einen Filter der Spalten anstatt der Zeilen und von VBA habe ich leider keine Ahnung.
ich habe eine Tabelle mit Namen in A2:A10. Im Bereich B2:X10 steht in verschiedenen Zellen entweder ein "x" oder die Zellen sind leer.
Nun hä...
29.10.2008 16:41:00
Thread-ID: 1020355
13 Beiträge
vertikal und horizontal
ludmila

Hallo Spezialisten,
In der Tabelle "Liste" sind Daten horizontal eingetragen.
Diese sollen nun in der Tabelle "Projekt" vertikal eingetragen werden.
Nachstehendes makro ließt jedoch nur das Datum ab der Zelle A 63 ein.
wo liegt der Fehler?
Danke!
LG Ludmila
Sub Lie...
15.03.2009 10:58:27
Thread-ID: 1060364
3 Beiträge
Anzeige
Zellenhöhe bei verbundenen Zellen (VBA Code?)
Micha

Hallo liebe Spezialisten,
ich habe folgendes Problem und würde mich über einen VBA-Code riesig freuen.
In der Tabelle1 sind in dem Bereich B53:AI63 die Zellen verbunden.
Der in diesem Bereich von anderen Usern eingegebene Text ist unterschiedlich lang.
Der Zeilenumbruch funktion...
17.09.2009 13:18:38
Thread-ID: 1103550
2 Beiträge
Locked Eigenschaft bei verbundenen Zellen
Christoph

Hallo zusammen,
nachdem mir eben schonmal wunderbar geholfen wurde, wende ich mich mit einem anderen Problem an euch. Szenario schaut wie folgt aus:
Ich habe eine Arbeitsmappe mit mehreren Arbeitsblättern. Die Gesamte Mappe soll gesperrt werden, in einzelnen Arbeitsblättern gibt es jedo...
16.11.2010 14:38:41
Thread-ID: 1187125
5 Beiträge
Zwei Zellbezüge in einer Zelle
WillyJ

Hilferuf eines Laien:
In A1 steht "Aktuelles Datum"
In A2 steht =heute()
In A3 soll stehen: Aktuelles Datum 31.03.2011
mit Bezug zu A1 und A2
Wenn ich in A3 eingebe: =&"a1"& a2 erscheint statt des Datums der Datumswert 40633
Danke im voraus für eure Hilfe.
31.03.2011 12:02:57
Thread-ID: 1208457
4 Beiträge
zwei Ebenen in einer Zelle
siegfried

Hallo zusammen,
ich möchte auf einer Zelle eine zweite Ebene einfügen (ähnlich wie ein Kommentar).
Das spezielle soll sein, dass ich auf dieser zweiten Ebene variabel Zahlen einfügen will, die ich manipulieren kann.
Geht das überhaupt? Und Wie?
Gruß
Siegfried
24.06.2011 22:16:26
Thread-ID: 1219551
4 Beiträge
zwei Hintergrundfarben in einer Zelle
siegfried

Hallo zusammen,
ich habe entweder einen Text oder je nach Fall auch zwei Textbausteine in einer Zelle verknüpft.
Um den Text1 von Text2 besser zu unterscheiden, habe ich bisher per VBA den Text1 in der Farbe "rot" geschrieben und den Text2 in "dunkelblau".
Kommt nur ein Text in di...
15.11.2011 15:37:08
Thread-ID: 1237968
3 Beiträge
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige
Archiv - Verwandte Themen
Forumthread
Beiträge
3
1
12
2
4
1
4
3
3
2
1
2
7
15
2
2
1
7
11
8
4
1
1
26
4