Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Zelle aus Tabelle suchen und umliegende Zellen auslesen

Zelle aus Tabelle suchen und umliegende Zellen auslesen
14.01.2020 15:38:41
Peter
Suche passende Formel um die gesuchten Zahlen in die angrenzenden Zahlen der Tabelle 1 auszulesrn und in Tabelle 2 einzufügen

15
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Zelle aus Tabelle suchen und umliegende Zellen auslesen
14.01.2020 16:56:38
Hajo_Zi
keine Ahnung qwas Du meinst.
Nur wenige schauen auf Deinen Rechner und sehen die Datei.
Ich möchte gerne den Fehler im Original sehen.
Ich baue keine Datei nach. Die Zeit hat schon jemand investiert.
Ein Nachbau sieht meist anders aus als das Original. Darum sollte das Original verlinkt werden.
Wenn du an Stelle einer Demomappe deine Originalmappe hochladen willst, diese aber sensible Daten enthält, kannst du diese Daten
http://www.ms-office-forum.de/forum/showthread.php?t=322895
änderrn.
Das ist nur meine Meinung zu dem Thema.
Benutze hier im Forum die Funktion zum hochladen. Falls Du die nicht benutzen möchtest beachte, von unsicheren Servern wie z.B. www.file-upload.net lade ich keine Datei runter. (lt. Einschätzung meines Virenprogramms)

Anzeige
AW: ist realisierbar ...
14.01.2020 21:41:37
neopa
Hallo Peter,
... doch dazu vorher noch festgestellt und nachgefragt:
Voraussetzung für eine korrekte Lösung ist, dass die Zahlen in D3:L11 eineindeutig sind. Ist das gewährleistet?
Sollen die Ergebniszahlen in der Reihenfolgen so gelistet werden, wie von Dir im Beispiel angegeben? Oder ist Dir die Reihenfolge egal?
Welche Excelversion hast Du denn genau im Einsatz?
Auf Deine Antwort(en) kann ich aber wahrscheinlich frühestens morgen Nachmittag antworten.
Gruß Werner
.. , - ...
Anzeige
AW: ist realisierbar ...
14.01.2020 22:48:52
Peter
Hallo Werner,
Vorerst herzlichen dank für deine Antwort.
Die Zahlen in Tabelle 1 D3:L11 sind eindeutig , werden nicht verändert
Die Reihenvolge der ergebniszahlen ist Tabelle 2 ist egal
Die Zahlen in A5; A6 dun A7 sind veränderbar
ich habe Excel 2007 und 2013
Gurß Peter
AW: ausreichend ist eine Formel für alle ...
15.01.2020 15:06:03
neopa
Hallo Peter,
... und damit die Formel übersichtlich bleibt, habe ich bei aktivierter Zelle D14 eine benannte Formel definiert.
Für Excel 2013 danach folgende Formel in D14 einkopiert und diese ziehend nach rechts und unten kopieren.
Arbeitsblatt mit dem Namen 'AGGR_200115_1'
 ABCDEFGHIJKL
3   37782970216213545
4Gesuchte ZahlKoor-
dinaten
 63879307122631446
57$E$5 47739803172235515
629$F$3 16488408132642456
746$L$4 57174994173336525
8   26581850142743466
9   67275910512437535
10   36681960115234476
11   77286920611253445
12            
13            
14Umgebende Zahlen
aufwärts sortiert
gelistet:
 68163839474879 
15 3038707879    
16 514155455    
17            

NameBezug
_Umgeb=INDEX(!$A:$L;MAX(SUMMENPRODUKT((!$D$3:$L$11=!$A1048570)*ZEILE(!$A$3:$A$11))-1;3);MAX(SUMMENPRODUKT((!$D$3:$L$11=!$A1048570)*SPALTE(!$D$1:$L$1))-1;4)):INDEX(!$A:$L;MIN(SUMMENPRODUKT((!$D$3:$L$11=!$A1048570)*ZEILE(!$A$3:$A$11))+1;11);MIN(SUMMENPRODUKT((!$D$3:$L$11=!$A1048570)*SPALTE(!$D$1:$L$1))+1;12))

ZelleFormel
D14=WENNFEHLER(AGGREGAT(15;6;_Umgeb/(_Umgeb>$A5);SPALTE(A1));"")
Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

Gruß Werner
.. , - ...
Anzeige
AW: die benannte Formel: _Umgeb ...
15.01.2020 15:17:19
neopa
Hallo,
... wurde offensichtlich falsch dargestellt.
Richtig muss diese so definiert werden:
Arbeitsblatt mit dem Namen 'Tab_2013'
 D
146

NameBezug
_Umgeb=INDEX(!$A:$L;MAX(SUMMENPRODUKT((!$D$3:$L$11=!$A5)*ZEILE(!$A$3:$A$11))-1;3);MAX(SUMMENPRODUKT((!$D$3:$L$11=!$A5)*SPALTE(!$D$1:$L$1))-1;4)):INDEX(!$A:$L;MIN(SUMMENPRODUKT((!$D$3:$L$11=!$A5)*ZEILE(!$A$3:$A$11))+1;11);MIN(SUMMENPRODUKT((!$D$3:$L$11=!$A5)*SPALTE(!$D$1:$L$1))+1;12))

ZelleFormel
D14=WENNFEHLER(AGGREGAT(15;6;_Umgeb/(_Umgeb>$A5);SPALTE(A1));"")
Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

Und für die Anwendung in Excel 2013 und 2007 so:
Arbeitsblatt mit dem Namen 'Tab_2007'
 D
146

NameBezug
_Umgeb=INDEX(!$A:$L;MAX(SUMMENPRODUKT((!$D$3:$L$11=!$A5)*ZEILE(!$A$3:$A$11))-1;3);MAX(SUMMENPRODUKT((!$D$3:$L$11=!$A5)*SPALTE(!$D$1:$L$1))-1;4)):INDEX(!$A:$L;MIN(SUMMENPRODUKT((!$D$3:$L$11=!$A5)*ZEILE(!$A$3:$A$11))+1;11);MIN(SUMMENPRODUKT((!$D$3:$L$11=!$A5)*SPALTE(!$D$1:$L$1))+1;12))

ZelleFormel
D14{=WENNFEHLER(KKLEINSTE(WENN(_Umgeb>$A5;_Umgeb);SPALTE(A1));"")}
Achtung, Matrixformel enthalten!
Die geschweiften Klammern{} werden nicht eingegeben.
Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.
Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

und ebenfalls nach rechts und unten kopieren.
Gruß Werner
.. , - ...
Anzeige
AW: die benannte Formel: _Umgeb ...
15.01.2020 18:25:59
Peter
Danke für deine antwort,
Wo muß ich die erste Formel _Umgeb = index.....eintragen?
könntest du eventuell die Datei hochladen
darüber währe ich die sehr dankbar
freundiche Grüße
Peter
AW: die benannte Formel ist so wie angegeben ...
15.01.2020 18:45:23
neopa
Hallo Peter,
... im Namensmanager (Mehr zum Namensmanager sieh mal hier: https://www.online-excel.de/excel/singsel.php?f=60) zu definieren. Dabei musst Du jedoch zuvor den Cursor in die Zelle D14 gesetzt haben. Anschließend kannst Du in D14 die Zellformel eingeben.
Die benannte Formel hat den Vorteil, dass die Formel in D14 übersichtlich bleibt.
Gruß Werner
.. , - ...
Anzeige
AW: die benannte Formel: _Umgeb ...
15.01.2020 19:37:21
Peter
Hab ein brett vorm Kopf !
1 Namen Definieren _umgeb
2 Feld Bezieht sich auf =Tabelle1!$D$14=INDEX(!$A:$L;MAX(SUMMENPRODUKT…..
Dann OK
Danach Formel in D14 =WENNFEHLER(AGGREGAT(15;6;_Umgeb/(_Umgeb$A5);SPALTE(A1));"")
Sind Diese schritte OK?
Besten Dank für deine Bemühungen
Peter
AW: die benannte Formel: _Umgeb ...
15.01.2020 20:15:28
Peter
Hallo Werner
Hab beide ausprobier bekomme aber bei beiden versuchen nur nullen angezeig
habe die Datei hochgeladen
https://www.herber.de/bbs/user/134437.xlsx
finde den Fehler nicht
Gruß
Peter
AW: nun, ganz einfach ...
16.01.2020 09:32:33
neopa
Hallo Peter,
... ich hatte nirgends geschrieben, dass Du in der benannten Formel noch einen Vergleich mit der aktuellen Zelle vornehmen sollst. Du musst also lediglich im Namensmanager vor der korrekten Formel Dein eingefügtes =Tabelle1!$D$14 löschen und schwupp die wupp solltest Du danach das korrekte Ergebnis haben.
Jedenfalls so bei mir.
Gruß Werner
.. , - ...
Anzeige
AW: die benannte Formel: _Umgeb ...
16.01.2020 14:24:26
Peter
Hallo WErner,
Tausend Dank jetzt hab ich‘s kapiert funktioniert super
Peter
AW: bitteschön owT
16.01.2020 16:52:38
neopa
Gruß Werner
.. , - ...
mit (fast) der identischen Formel
14.01.2020 23:07:06
WF
Hi,
=INDEX($A$1:$L$11;SUMMENPRODUKT((D3:L11=A5)*ZEILE(3:11))-1;SUMMENPRODUKT((D3:L11=A5)*SPALTE(D:L))-1)
ist die Zahl links oben
=INDEX($A$1:$L$11;SUMMENPRODUKT((D3:L11=A5)*ZEILE(3:11))-1;SUMMENPRODUKT((D3:L11=A5)*SPALTE(D:L))-0)
ist die Zahl genau drüber
-1 / +1 ist die Zahl rechts oben
-0 / -1 ist die Zahl links
-0 / +1 ist die Zahl rechts
+1 / -1 ist die Zahl links unten
usw.
Du musst anschließend nur noch die Zellen ohne Inhalt eliminieren.
WF
übrigens: in Zelle B7 steht bei Dir der falsche Bezug.
Anzeige
AW: mit (fast) der identischen Formel
15.01.2020 15:02:22
Peter
Hallo WF,
danke für deine Antwort
die Formeln sid genau richtig
ercht herzlichen Dank
Peter

391 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige