Microsoft Excel

Herbers Excel/VBA-Archiv

Hilfe für Anfänger | Herbers Excel-Forum


Betrifft: Hilfe für Anfänger von: Andreas Berg
Geschrieben am: 03.02.2010 12:08:04

Hallo,
Hab hier so meine Probleme.

Hier mal die Augangstabelle in Formelblatt1

Tabelle1

 ABCDE
1NameWert1Wert2Wert3Wert4
2Hans1244226443
3Peter17542223257
4Klaus2136523125
5Dieter346572317657543


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

Hier die Tabelle mit den benötigten Formeln in Tabellenblatt2

Tabelle2

 ABC
1NameRangWert4/Wert3
2Hans1292,9
3Dieter 29,9
4Klaus35,4
5Peter40,3


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

Bei Formelblatt 1 wird alles per Hand eingetragen.
Es können aber weitere Namen hinzukommen oder gelöscht werden.

Bei Formelblatt 2 soll alles mit Formeln auf Grundlage von Formelblatt1 ausgegeben werden, ausser die "Überschriften" Name, Rang, Wert4/Wert3.

Ich bräuchte also je eine Formel für Name, Rang und Wert4/Wert3
Leider weiß ich überhaubt nicht wie ich anfangen soll, oder ob das überhaubt funktioniert da ja alles in Beziehung miteinander und in Beziehung mit Tabellenblatt1 steht. Auch das in Tabellenblatt1 Datensätze gelöscht werden können und somit die Reihenfolge sich ändert bereitet mir Probleme.
Würde mich über einen Lösungsweg freuen

Grüße
Andi

  

Betrifft: ohne Hilfsspalten... von: Christian
Geschrieben am: 03.02.2010 13:00:06

Hallo Andi,

ist das schon eher was für Fortgeschrittene Benutzer!

ein Ansatz:

Tabelle2

 ABC
1NameRangWert4/Wert3
2Hans1292,864
3Dieter29,860
4Klaus35,435
5Peter40,280
6   
7   

Formeln der Tabelle
ZelleFormel
A2{=WENN(B2="";"";INDEX(Tabelle1!$A$2:$A$1000;VERGLEICH(KGRÖSSTE(wert_4/wert_3;ZEILE(A1)); wert_4/wert_3;0)))}
B2=WENN(ZEILE(A1)>ANZAHL2(Tabelle1!$A$2:$A$1000); "";ZEILE(A1))
C2{=WENN(B2="";"";KGRÖSSTE(wert_4/wert_3;ZEILE(A1)))}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen
Namen in Formeln
ZelleNameBezieht sich auf
A2wert_3=INDIREKT("Tabelle1!$D$2:$D$"&VERWEIS(2;1/(Tabelle1!$D$1:$D$65535<>"");ZEILE(Tabelle1!$D:$D)))
A2wert_4=INDIREKT("Tabelle1!$E$2:$E$"&VERWEIS(2;1/(Tabelle1!$E$1:$E$65535<>"");ZEILE(Tabelle1!$E:$E)))
C2wert_3=INDIREKT("Tabelle1!$D$2:$D$"&VERWEIS(2;1/(Tabelle1!$D$1:$D$65535<>"");ZEILE(Tabelle1!$D:$D)))
C2wert_4=INDIREKT("Tabelle1!$E$2:$E$"&VERWEIS(2;1/(Tabelle1!$E$1:$E$65535<>"");ZEILE(Tabelle1!$E:$E)))
Namen verstehen


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

MfG Christian


  

Betrifft: AW: ohne Hilfsspalten... von: Andreas Berg
Geschrieben am: 03.02.2010 13:27:07

Hallo,
danke für die schnelle Antwort
Hab das mal so probiert...
bei der Spalte Rang scheint es zu funktionieren
Bei der Spalte Name und Wert4/Wert3 leider nicht, dort steht dann "#NAME?"

Habe es kopiert so wie es in deiner Antwort steht ohne { und }.
Dann Strg+Shift+Enter, die Klammern sind aufjedenfall gesetzt wurden.

Habe auch mal in der Formel Wert_4 und Wert_3 durch Wert4 und Wert3 ersetzt weil es ja so in den Feldern bei Tabelle 1 steht... hat aber auch nicht funktioniert.

Du hast noch eine Tabelle geposetet Zelle|Name|Bezieht sich auf... muss ich die auch irgendwo einbinden? Versteh nicht ganz was ich damit machen soll.

Falls das noch wichtig ist.
In Tabelle 2 steht ja Name,Rang,Wert4/Wert3
Allerdings war das nur ein Beispiel in der Tabelle 1 stehen noch mehr Werte-Spalten und in der Tabelle 2 kommen auch noch mehr hin z.b:

Name|Rang|Wert4/Wert3|Name|Rang|Wert1*3|Name|Rang|(Wert2*Wert7)/Wert1| usw.

wenn ich dan mit Name|Rang|Wert4/Wert3 hinbekomme kann ich mir das restliche selbst ableiten


Grüße
Andi


  

Betrifft: AW: ohne Hilfsspalten... von: Christian
Geschrieben am: 03.02.2010 13:54:19

Hallo Andi,

die Namen musst Du natürlich noch vergeben, das geht mit Einfügen - Namen - Definieren und hier dann Namen und Bezug festlegen.
Das von mir gezeigte Bsp. ist schon etwas knifflig, nicht so richtig für Anfänger, deshalb wäre es gut, wenn Du Deine Datei hier reinstellst, die Daten vorher natürlich anonymisierst, da ein Nachbau und die Ergänzung eines Nachbaus nicht immer mit Deinen Daten übereinstimmen müssen ;-)

MfG Christian


  

Betrifft: AW: ohne Hilfsspalten... von: Andreas Berg
Geschrieben am: 03.02.2010 14:16:59

Hallo

ich hab gerade Excel 2007 auf, mit den gleichen Beispielen wie ich sie im ersten Beitrag gepostet habe
also Tabelle1 und Tabelle2

Habe in Tabelle 2 alles so eingefügt wie du es geschrieben hast, auch mit STRG+SHIFT+ENTER bei A2 und C2
Dann hab ich es nochmal mit Wert4,Wert3 statt wert_4,wert_3 versucht

Bei dem Beispiel funktioniert es jedenfalls nicht, bei Name und Wert4/Wert3 steht "#NAME?"

Die Tabellen bin ich gerade erst am aufbauen, daher kann ich die nicht posten.
Aber ich denke wenn es für das eine Beispiel klappt, kann ich dies auch auf weitere übertragen

grüße
andi


  

Betrifft: AW: ohne Hilfsspalten... von: Christian
Geschrieben am: 03.02.2010 14:49:17

Hallo Andi,

das mit den Namen vergeben ist hier beschrieben:
http://www.online-excel.de/excel/singsel.php?f=60

Die Namen erzeugen eine dynamische Liste, da Du ja immer wieder Einträge neu einpflegen willst und mit einem festen Bezug müsstest Du ja ständig die Formel anpassen. Das ganze kann man auch ohne die Namensvergabe mit flexiblen Bereichen berechnen, z. B. mit der Formel BEREICH.VERSCHIEBEN, was aber die Förmel auch länger und unübersichtlich macht.

Bsp:

Tabelle2

 ABC
1NameRangWert4/Wert3
2Hans1292,864
3Dieter29,860
4Klaus35,435
5Peter40,280
6   
7   
8   
9   
10   

Formeln der Tabelle
ZelleFormel
A2{=WENN(B2="";"";INDEX(Tabelle1!$A$2:$A$1000;VERGLEICH(KGRÖSSTE(BEREICH.VERSCHIEBEN(Tabelle1!E$2;;;VERWEIS(2;1/(Tabelle1!E$1:E$65535<>""); ZEILE(Tabelle1!E:E))-1;)/BEREICH.VERSCHIEBEN(Tabelle1!D$2;;;VERWEIS(2;1/(Tabelle1!D$1:D$65535<>""); ZEILE(Tabelle1!D:D))-1;); ZEILE(A1)); BEREICH.VERSCHIEBEN(Tabelle1!E$2;;;VERWEIS(2;1/(Tabelle1!E$1:E$65535<>""); ZEILE(Tabelle1!E:E))-1;)/BEREICH.VERSCHIEBEN(Tabelle1!D$2;;;VERWEIS(2;1/(Tabelle1!D$1:D$65535<>""); ZEILE(Tabelle1!D:D))-1;); 0)))}
B2=WENN(ZEILE(A1)>ANZAHL2(Tabelle1!$A$2:$A$1000); "";ZEILE(A1))
C2{=WENN(B2="";"";KGRÖSSTE(BEREICH.VERSCHIEBEN(Tabelle1!E$2;;;VERWEIS(2;1/(Tabelle1!E$1:E$65535<>""); ZEILE(Tabelle1!E:E))-1;)/BEREICH.VERSCHIEBEN(Tabelle1!D$2;;;VERWEIS(2;1/(Tabelle1!D$1:D$65535<>""); ZEILE(Tabelle1!D:D))-1;); ZEILE(A1)))}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

MfG Christian


  

Betrifft: AW: ohne Hilfsspalten... von: Andreas Berg
Geschrieben am: 03.02.2010 15:48:56

Hallo

ich hab jetzt die Namen vergeben, danke für den Hinweis.
Die lange Variante scheint mir erstmal zu kompliziert wenn ich daraus auf andere Rechnungen schließen will.
Aber die kurze Variante die du bei deiner ersten Antwort gepostet hast scheint jetzt zu funktionieren.

In deiner ersten Antwort hast du allerdings noch eine zweite Tabelle gepostet:

A2 wert_3 =INDIREKT("Tabelle1!$D$2:$D$"&VERWEIS(2;1/(Tabelle1!$D$1:$D$65535<>"");ZEILE(Tabelle1!$D:$D)))
A2 wert_4 =INDIREKT("Tabelle1!$E$2:$E$"&VERWEIS(2;1/(Tabelle1!$E$1:$E$65535<>"");ZEILE(Tabelle1!$E:$E)))
C2 wert_3 =INDIREKT("Tabelle1!$D$2:$D$"&VERWEIS(2;1/(Tabelle1!$D$1:$D$65535<>"");ZEILE(Tabelle1!$D:$D)))
C2 wert_4 =INDIREKT("Tabelle1!$E$2:$E$"&VERWEIS(2;1/(Tabelle1!$E$1:$E$65535<>"");ZEILE(Tabelle1!$E:$E)))

Brauche ich die auch?
Oder wozu soll die gut sein?

Grüße
Andi


  

Betrifft: AW: ohne Hilfsspalten... von: Andreas Berg
Geschrieben am: 03.02.2010 16:09:52

Übrigens, falls es so leichter geht:

Wenn ich in der Tabelle einen Datensatz lösche kann der auch leer bleiben
Bsp
Name | Wert1 | Wert2
Name1---3---------5-----
Name2---4---------6-----
Name3---5---------8-----

wird zu

Name | Wert1 | Wert2
Name1----3---------5----

Name3----5---------8----

Ich muss also nicht unbedingt den Datensatz Name3 nach oben schieben, wo vorher Name2 stand kann auch eine leere Zeile bleiben.
Wenn die Formel dadurch einfacher würde, wäre es natürlich besser beim Übertragen auf andere Beispiel.

grüße
Andi


  

Betrifft: mit Leerzeilen wird es dann.. von: Christian
Geschrieben am: 03.02.2010 16:14:59

Hallo Andi,

besser sein mit einer Hilfspalte zu arbeiten.
Es ist vorteilhaft, wenn Du eine Bsp.-Tabelle einstellst, sonst nimmt der Threat hier kein schnelles Ende ;-)

MfG Christian


  

Betrifft: AW: ohne Hilfsspalten... von: Christian
Geschrieben am: 03.02.2010 16:10:13

Hallo Andi,

genau diese Formeln gehören zu den vergebenen Namen, die den Bereich dynamisch begrenzen.

Wie man mit Namen arbeitet bzw. diese definiert ist hier dargestellt:

https://www.herber.de/xlfaq/workshop/namen/namen.html#nowhere

MfG Christian


  

Betrifft: AW: ohne Hilfsspalten... von: Andreas Berg
Geschrieben am: 03.02.2010 16:58:05

Ich hab mal ne Datei angehängt mit den 2 Tabellen wie es ungefähr aussehen soll.
https://www.herber.de/bbs/user/67735.xlsx

Leerzeilen sind wie gesagt kein Problem.
Die Formeln sollen nur so einfach sein das ich sie abgeändert wieder benutzen kann
Hilfstabellen sind in Tabelle1 auch kein Problem wenn es damit einfacher geht.

Grüße
Andi