PDA

Vollständige Version anzeigen : formel für datenübernahme


marielou
15.03.2009, 09:37
hi :) ich bins mal wieder mit einer frage:

ich habe zwei tabellen (sh. anhang).

in der tab. 2 habe ich sämtliche artikel-nr. mit den zugehörigen betrieben aufgeführt. um nicht alles manuell eintragen zu müssen, soll in tab. 1 eine formel integriert werden, welche diese informationen in umgekehrter reihe aus der tab. 2 nimmt.

es sind also in tab. 1 die ganzen betriebe nach unten aufgeführt und es soll z.b. in der zelle b2 ein x stehen, wenn in tab. 2 zeile 2 dieser betrieb dort aufgeführt ist.

und das dann eben auch für die ganze tabelle.

ich hoffe man kanns verstehen :eek:

grüße, marie

jinx
15.03.2009, 10:28
Moin, marie,

eine Möglichkeit der Lösung:

<b>Tabelle1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:53px;" /><col style="width:92px;" /><col style="width:75px;" /><col style="width:75px;" /><col style="width:75px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >&nbsp;</td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:24px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-size:14pt; ">&nbsp;</td><td style="color:#ff0000; font-size:14pt; text-align:right; ">203130</td><td style="color:#ff0000; font-size:14pt; text-align:right; ">203122</td><td style="color:#ff0000; font-size:14pt; text-align:right; ">203123</td><td style="color:#ff0000; font-size:14pt; text-align:right; ">203126</td></tr><tr style="height:24px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="color:#008000; font-size:14pt; text-align:right; ">4001</td><td style="color:#008000; font-size:14pt; text-align:center; ">x</td><td style="color:#008000; font-size:14pt; text-align:center; ">x</td><td style="color:#008000; font-size:14pt; text-align:center; ">x</td><td style="color:#008000; font-size:14pt; text-align:center; ">x</td></tr><tr style="height:24px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="color:#008000; font-size:14pt; text-align:right; ">4002</td><td style="color:#008000; font-size:14pt; text-align:center; ">x</td><td style="color:#008000; font-size:14pt; text-align:center; ">x</td><td style="color:#008000; font-size:14pt; text-align:center; ">x</td><td style="color:#008000; font-size:14pt; text-align:center; ">x</td></tr><tr style="height:24px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="color:#008000; font-size:14pt; text-align:right; ">4003</td><td style="color:#008000; font-size:14pt; text-align:center; ">x</td><td style="color:#ff0000; font-size:14pt; text-align:center; ">./.</td><td style="color:#008000; font-size:14pt; text-align:center; ">x</td><td style="color:#ff0000; font-size:14pt; text-align:center; ">./.</td></tr><tr style="height:24px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="color:#008000; font-size:14pt; text-align:right; ">4004</td><td style="color:#008000; font-size:14pt; text-align:center; ">x</td><td style="color:#008000; font-size:14pt; text-align:center; ">x</td><td style="color:#008000; font-size:14pt; text-align:center; ">x</td><td style="color:#008000; font-size:14pt; text-align:center; ">x</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Formeln der Tabelle</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Zelle</td><td >Formel</td></tr><tr><td >B2</td><td >=WENN(Z&Auml;HLENWENN<span style=' color:008000; '>(INDIREKT<span style=' color:#0000ff; '>("Tabelle2!" & SPALTE<span style=' color:#ff0000; '>()</span> & ":" & SPALTE<span style=' color:#ff0000; '>()</span>)</span>;$A2)</span>&gt;0;"x";"./.")</td></tr><tr><td >C2</td><td >=WENN(Z&Auml;HLENWENN<span style=' color:008000; '>(INDIREKT<span style=' color:#0000ff; '>("Tabelle2!" & SPALTE<span style=' color:#ff0000; '>()</span> & ":" & SPALTE<span style=' color:#ff0000; '>()</span>)</span>;$A2)</span>&gt;0;"x";"./.")</td></tr><tr><td >D2</td><td >=WENN(Z&Auml;HLENWENN<span style=' color:008000; '>(INDIREKT<span style=' color:#0000ff; '>("Tabelle2!" & SPALTE<span style=' color:#ff0000; '>()</span> & ":" & SPALTE<span style=' color:#ff0000; '>()</span>)</span>;$A2)</span>&gt;0;"x";"./.")</td></tr><tr><td >E2</td><td >=WENN(Z&Auml;HLENWENN<span style=' color:008000; '>(INDIREKT<span style=' color:#0000ff; '>("Tabelle2!" & SPALTE<span style=' color:#ff0000; '>()</span> & ":" & SPALTE<span style=' color:#ff0000; '>()</span>)</span>;$A2)</span>&gt;0;"x";"./.")</td></tr><tr><td >B3</td><td >=WENN(Z&Auml;HLENWENN<span style=' color:008000; '>(INDIREKT<span style=' color:#0000ff; '>("Tabelle2!" & SPALTE<span style=' color:#ff0000; '>()</span> & ":" & SPALTE<span style=' color:#ff0000; '>()</span>)</span>;$A3)</span>&gt;0;"x";"./.")</td></tr><tr><td >C3</td><td >=WENN(Z&Auml;HLENWENN<span style=' color:008000; '>(INDIREKT<span style=' color:#0000ff; '>("Tabelle2!" & SPALTE<span style=' color:#ff0000; '>()</span> & ":" & SPALTE<span style=' color:#ff0000; '>()</span>)</span>;$A3)</span>&gt;0;"x";"./.")</td></tr><tr><td >D3</td><td >=WENN(Z&Auml;HLENWENN<span style=' color:008000; '>(INDIREKT<span style=' color:#0000ff; '>("Tabelle2!" & SPALTE<span style=' color:#ff0000; '>()</span> & ":" & SPALTE<span style=' color:#ff0000; '>()</span>)</span>;$A3)</span>&gt;0;"x";"./.")</td></tr><tr><td >E3</td><td >=WENN(Z&Auml;HLENWENN<span style=' color:008000; '>(INDIREKT<span style=' color:#0000ff; '>("Tabelle2!" & SPALTE<span style=' color:#ff0000; '>()</span> & ":" & SPALTE<span style=' color:#ff0000; '>()</span>)</span>;$A3)</span>&gt;0;"x";"./.")</td></tr><tr><td >B4</td><td >=WENN(Z&Auml;HLENWENN<span style=' color:008000; '>(INDIREKT<span style=' color:#0000ff; '>("Tabelle2!" & SPALTE<span style=' color:#ff0000; '>()</span> & ":" & SPALTE<span style=' color:#ff0000; '>()</span>)</span>;$A4)</span>&gt;0;"x";"./.")</td></tr><tr><td >C4</td><td >=WENN(Z&Auml;HLENWENN<span style=' color:008000; '>(INDIREKT<span style=' color:#0000ff; '>("Tabelle2!" & SPALTE<span style=' color:#ff0000; '>()</span> & ":" & SPALTE<span style=' color:#ff0000; '>()</span>)</span>;$A4)</span>&gt;0;"x";"./.")</td></tr><tr><td >D4</td><td >=WENN(Z&Auml;HLENWENN<span style=' color:008000; '>(INDIREKT<span style=' color:#0000ff; '>("Tabelle2!" & SPALTE<span style=' color:#ff0000; '>()</span> & ":" & SPALTE<span style=' color:#ff0000; '>()</span>)</span>;$A4)</span>&gt;0;"x";"./.")</td></tr><tr><td >E4</td><td >=WENN(Z&Auml;HLENWENN<span style=' color:008000; '>(INDIREKT<span style=' color:#0000ff; '>("Tabelle2!" & SPALTE<span style=' color:#ff0000; '>()</span> & ":" & SPALTE<span style=' color:#ff0000; '>()</span>)</span>;$A4)</span>&gt;0;"x";"./.")</td></tr><tr><td >B5</td><td >=WENN(Z&Auml;HLENWENN<span style=' color:008000; '>(INDIREKT<span style=' color:#0000ff; '>("Tabelle2!" & SPALTE<span style=' color:#ff0000; '>()</span> & ":" & SPALTE<span style=' color:#ff0000; '>()</span>)</span>;$A5)</span>&gt;0;"x";"./.")</td></tr><tr><td >C5</td><td >=WENN(Z&Auml;HLENWENN<span style=' color:008000; '>(INDIREKT<span style=' color:#0000ff; '>("Tabelle2!" & SPALTE<span style=' color:#ff0000; '>()</span> & ":" & SPALTE<span style=' color:#ff0000; '>()</span>)</span>;$A5)</span>&gt;0;"x";"./.")</td></tr><tr><td >D5</td><td >=WENN(Z&Auml;HLENWENN<span style=' color:008000; '>(INDIREKT<span style=' color:#0000ff; '>("Tabelle2!" & SPALTE<span style=' color:#ff0000; '>()</span> & ":" & SPALTE<span style=' color:#ff0000; '>()</span>)</span>;$A5)</span>&gt;0;"x";"./.")</td></tr><tr><td >E5</td><td >=WENN(Z&Auml;HLENWENN<span style=' color:008000; '>(INDIREKT<span style=' color:#0000ff; '>("Tabelle2!" & SPALTE<span style=' color:#ff0000; '>()</span> & ":" & SPALTE<span style=' color:#ff0000; '>()</span>)</span>;$A5)</span>&gt;0;"x";"./.")</td></tr></table></td></tr></table><br /><table style="font-family:Arial; font-size:10pt; background-color:#fffcf9; border-style: groove ;border-color:#ff0000"><tr><td ><b>Bedingte Formatierungen der Tabelle</b></td></tr><tr><td ><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial; font-size:10pt; padding-left:2pt; padding-right:2pt; "><tr><td >Zelle</td><td >Nr.: / Bedingung</td><td >Format</td></tr><tr><td >B2</td><td >1. / Formel ist =B2="./."</td><td style="color:#ff0000; ">Abc</td></tr><tr><td >C2</td><td >1. / Formel ist =C2="./."</td><td style="color:#ff0000; ">Abc</td></tr><tr><td >D2</td><td >1. / Formel ist =D2="./."</td><td style="color:#ff0000; ">Abc</td></tr><tr><td >E2</td><td >1. / Formel ist =E2="./."</td><td style="color:#ff0000; ">Abc</td></tr><tr><td >B3</td><td >1. / Formel ist =B3="./."</td><td style="color:#ff0000; ">Abc</td></tr><tr><td >C3</td><td >1. / Formel ist =C3="./."</td><td style="color:#ff0000; ">Abc</td></tr><tr><td >D3</td><td >1. / Formel ist =D3="./."</td><td style="color:#ff0000; ">Abc</td></tr><tr><td >E3</td><td >1. / Formel ist =E3="./."</td><td style="color:#ff0000; ">Abc</td></tr><tr><td >B4</td><td >1. / Formel ist =B4="./."</td><td style="color:#ff0000; ">Abc</td></tr><tr><td >C4</td><td >1. / Formel ist =C4="./."</td><td style="color:#ff0000; ">Abc</td></tr><tr><td >D4</td><td >1. / Formel ist =D4="./."</td><td style="color:#ff0000; ">Abc</td></tr><tr><td >E4</td><td >1. / Formel ist =E4="./."</td><td style="color:#ff0000; ">Abc</td></tr><tr><td >B5</td><td >1. / Formel ist =B5="./."</td><td style="color:#ff0000; ">Abc</td></tr><tr><td >C5</td><td >1. / Formel ist =C5="./."</td><td style="color:#ff0000; ">Abc</td></tr><tr><td >D5</td><td >1. / Formel ist =D5="./."</td><td style="color:#ff0000; ">Abc</td></tr><tr><td >E5</td><td >1. / Formel ist =E5="./."</td><td style="color:#ff0000; ">Abc</td></tr></table></td></tr></table> <br />

marielou
15.03.2009, 16:19
wow! :boah:

es funktioniert! auch wenn ich nicht kapier warum!

hab vielen dank!! :)

jinx
15.03.2009, 16:24
Moin, marie,,

über die Funktion Spalte wird die Nummer der jeweiligen Spalte angegeben (die rein zufällig identisch ist mit der Zeile, in der sich die Angaben in der anderen Tabelle befinden), per Indirekt wird eine Bereichsangabe aus dem Tabellennamen und den Spalten/Zeilen vorgegeben, die als erster Parameter an die Funktion ZÄHLENWENN (wo soll gezählt werden) übergeben wird. Das, was gezählt wird, befindet sich immer in Spalte A der Tabelle1. Und dann wird geprüft, ob der Suchbegriff im vorgegebenen Bereich vorhanden ist (WAHR, also x) oder nicht (FALSCH, also ./.)...

marielou
16.03.2009, 19:33
hm, du gibst dir ja alle mühe mit mir, aber sobald ich eine "große reale" datei nehme und die formel dort übertragen will, stimmen die "X" nicht mehr!

es liegt meiner meinung nach daran, dass die tabellen in den zeilen und spalten etwas anders sind?! also weiter unten bzw. nach rechts verschoben!
ich habe nochmals eine zweite beispiel datei im anhang angefügt. vielleicht kannst du mir hier nochmals die richtige formel machen?

vielen dank, marie

chris-kaiser
16.03.2009, 19:55
hiho

hier ein Versuch
ich habe nur die Zellbezüge von jinx Formel angepasst

marielou
16.03.2009, 21:27
merci! funktioniert natürlich wieder!

kann mir noch einer von euch erklären, wie ich im letzten beispiel in der formel auf "C1" komme? ich checks nicht!!!! :eek:

jinx
17.03.2009, 04:37
Moin, marielou,

C1 gibt die erste Spalte wieder, in der sich der Datenbereich befindet.

Ohen die genaue Angabe in A1-Schreibweise kann für die Zelle E5 die folgende Formel angepasst werden:
=WENN(ZÄHLENWENN(INDIREKT("Tabelle2!" & SPALTE()-2 & ":" & SPALTE()-2);$D5)>0;"x";"./.")
Hier liefert Spalte die Zahl der aufrufenden Spalte (E) mit der Formel den Wert 5, die Nachschlagedaten befinden sich in Zeile 3 in Tabelle2, daher ist der Wert um -2 zu "korrigieren". HTH.

marielou
17.03.2009, 11:51
habs probiert! es kommen auch tausend X, aber nicht immer im richtigen feld!

hier zum letzten mal noch die "fast, original" datei mit der bitte um die passende formel! ich selber gebs auf!

grazie

jinx
17.03.2009, 15:53
Moin, marielou,

für L9 der Tabelle2:

=WENN(ZÄHLENWENN(INDIREKT("Tabelle1!" & SPALTE()+15 & ":" & SPALTE()+15);$K9)>0;"x";"./.")

marielou
17.03.2009, 19:53
vielen Dank für eine Gedult! ;)