Microsoft Excel

Herbers Excel/VBA-Archiv

Zelle aus Tabelle suchen und umliegende Zellen auslesen

Betrifft: Zelle aus Tabelle suchen und umliegende Zellen auslesen von: Peter
Geschrieben am: 14.01.2020 15:38:41

Suche passende Formel um die gesuchten Zahlen in die angrenzenden Zahlen der Tabelle 1 auszulesrn und in Tabelle 2 einzufügen

Betrifft: AW: Zelle aus Tabelle suchen und umliegende Zellen auslesen
von: Hajo_Zi
Geschrieben am: 14.01.2020 16:56:38

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)

GrußformelHomepage

Betrifft: AW: Zelle aus Tabelle suchen und umliegende Zellen auslesen
von: Peter
Geschrieben am: 14.01.2020 17:57:35

Hier die Datei
https://www.herber.de/bbs/user/134401.xlsx

Betrifft: AW: ist realisierbar ...
von: neopa C
Geschrieben am: 14.01.2020 21:41:37

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
.. , - ...

Betrifft: AW: ist realisierbar ...
von: Peter
Geschrieben am: 14.01.2020 22:48:52

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



Betrifft: AW: ausreichend ist eine Formel für alle ...
von: neopa C
Geschrieben am: 15.01.2020 15:06:03

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
.. , - ...

Betrifft: AW: die benannte Formel: _Umgeb ...
von: neopa C
Geschrieben am: 15.01.2020 15:17:19

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
.. , - ...

Betrifft: AW: die benannte Formel: _Umgeb ...
von: Peter
Geschrieben am: 15.01.2020 18:25:59

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

Betrifft: AW: die benannte Formel ist so wie angegeben ...
von: neopa C
Geschrieben am: 15.01.2020 18:45:23

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
.. , - ...

Betrifft: AW: die benannte Formel: _Umgeb ...
von: Peter
Geschrieben am: 15.01.2020 19:37:21

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

Betrifft: AW: die benannte Formel: _Umgeb ...
von: Peter
Geschrieben am: 15.01.2020 20:15:28

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

Betrifft: AW: nun, ganz einfach ...
von: neopa C
Geschrieben am: 16.01.2020 09:32:33

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
.. , - ...

Betrifft: AW: die benannte Formel: _Umgeb ...
von: Peter
Geschrieben am: 16.01.2020 14:24:26

Hallo WErner,

Tausend Dank jetzt hab ich‘s kapiert funktioniert super

Peter

Betrifft: AW: bitteschön owT
von: neopa C
Geschrieben am: 16.01.2020 16:52:38

Gruß Werner
.. , - ...

Betrifft: mit (fast) der identischen Formel
von: WF
Geschrieben am: 14.01.2020 23:07:06

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.

Betrifft: AW: mit (fast) der identischen Formel
von: Peter
Geschrieben am: 15.01.2020 15:02:22

Hallo WF,

danke für deine Antwort

die Formeln sid genau richtig

ercht herzlichen Dank

Peter

Beiträge aus dem Excel-Forum zum Thema "Zelle aus Tabelle suchen und umliegende Zellen auslesen"