Gruppen
- Top Gruppen
- Betriebsysteme
- Windows classik
- Linux
- BS-Sonstige
- Software
- Datenbanken
- Bildbearbeitung
- Audio/mp3/Video
- Security/Viren
- SW-Sonstige
- Hardware
- Mainboard/CPU
- Grafikkarten
- Peripherie
- Laptop/Notebook
- HW-Sonstiges
- Netzwerk
- Telefon,ISDN,Modem
- W-Lan
- NW-Sonstiges
- Programmierung
- PHP,ASP,Perl...
- Java,C++...
- Webseiten/HTML
- Sonstiges
- PC-Sonstiges
- Plauderecke
- SN Intern
- Test
/ Forum / Tabellenkalkulation
Tabellenkalkulation
Fragevon rhawke vom 14.11.2019, 16:51 Options
Datenbankfunktion mit Suchkriterium NICHT als Bezug???
Hi ich hab folgendes Problem:
ich sollte aus einer Excel-Tabelle mit 5 Spalten den Wert aus der letzten Spalte holen, der ein TEXT ist. Dabei soll aber genau der Wert geholt werden, bei denen die 4 Spalten davor als Kriterium gelten
Bsp:
also theoretisch würde ich gerne herausfinden, welche Company in der Zeile: Austria 2006 TOM steht.
Das funktioniert mit der DB-Funktion sonst prächtig. Nur sollte ich da jetzt ca. 100 mal in einer zweiten Tabelle machen und ich kann da nicht für jedes einzelne so ein blödes DB-Kriterien-Feld einbauen. Besteht die möglichkeit die Kriterien irgendwie über Matrizen oder etwas derartiges einzugeben?
Ich habs auch schon ganz ohne DB-Funktion und nur mit Matrixfunktionen versucht
Summe(WENN((Country=D$6)*(Year=D$7)*(Company<>"Hilti")*(Attribute=$A9);Company))
allerdings würde das nur gehen wenn Company eben auch eine Zahl wäre. Die interne ergenismatrix nach meinem beispiel besteht wohl aus lauter FALSCH und dann genau einem text-eintrag. Nur kann ich den nicht mittels MAX, SUMME oder sonst was zum anzeigen bringen.
hat wer eine lösung? wäre wirklich äusserts hilfreich!
lg robert
ich sollte aus einer Excel-Tabelle mit 5 Spalten den Wert aus der letzten Spalte holen, der ein TEXT ist. Dabei soll aber genau der Wert geholt werden, bei denen die 4 Spalten davor als Kriterium gelten
Bsp:
Country Year Attribute Company
Germany 2007 TOM hfgh8
Germany 2007 TOM xxx
Germany 2007 TOM bbb
Austria 2006 TOM bbb
Germany 2006 TOM bbb
Great Britain 2006 TOM test34
also theoretisch würde ich gerne herausfinden, welche Company in der Zeile: Austria 2006 TOM steht.
Das funktioniert mit der DB-Funktion sonst prächtig. Nur sollte ich da jetzt ca. 100 mal in einer zweiten Tabelle machen und ich kann da nicht für jedes einzelne so ein blödes DB-Kriterien-Feld einbauen. Besteht die möglichkeit die Kriterien irgendwie über Matrizen oder etwas derartiges einzugeben?
Ich habs auch schon ganz ohne DB-Funktion und nur mit Matrixfunktionen versucht
Summe(WENN((Country=D$6)*(Year=D$7)*(Company<>"Hilti")*(Attribute=$A9);Company))
allerdings würde das nur gehen wenn Company eben auch eine Zahl wäre. Die interne ergenismatrix nach meinem beispiel besteht wohl aus lauter FALSCH und dann genau einem text-eintrag. Nur kann ich den nicht mittels MAX, SUMME oder sonst was zum anzeigen bringen.
hat wer eine lösung? wäre wirklich äusserts hilfreich!
lg robert
Antwort 1 von rainberg vom 14.11.2019, 17:42 Options
Hallo Robert,
folgende Situation:
Die von Dir gepostete Matrix befindet sich im Bereich A1:D7, wobei in Zeile 1 die Überschriften sind und in den restlichen Zeilen die relevanten Daten.
Die Jahreszahlen in Spalte B sind Zahlenwerte.
Hier zwei Formeln, die Dein gefordertes Ergebnis bringen.
=INDIREKT("D"&SUMMENPRODUKT(($A$1:$A$7="Austria")*($B$1:$B$7=2006)*($C$1:$C$7="TOM")*ZEILE(1:7)))
=INDEX(D1:D7;VERGLEICH("Austria"&2006&"TOM";A1:A7&B1:B7&C1:C7;0))
Die zweite Formel muss als Matrixformel eingegeben werden, also die Formeleingabe mit der Tastenkombination Strg+Shift+Enter abschließwn.
Gruß
Rainer
folgende Situation:
Die von Dir gepostete Matrix befindet sich im Bereich A1:D7, wobei in Zeile 1 die Überschriften sind und in den restlichen Zeilen die relevanten Daten.
Die Jahreszahlen in Spalte B sind Zahlenwerte.
Hier zwei Formeln, die Dein gefordertes Ergebnis bringen.
=INDIREKT("D"&SUMMENPRODUKT(($A$1:$A$7="Austria")*($B$1:$B$7=2006)*($C$1:$C$7="TOM")*ZEILE(1:7)))
=INDEX(D1:D7;VERGLEICH("Austria"&2006&"TOM";A1:A7&B1:B7&C1:C7;0))
Die zweite Formel muss als Matrixformel eingegeben werden, also die Formeleingabe mit der Tastenkombination Strg+Shift+Enter abschließwn.
Gruß
Rainer
Antwort 2 von rhawke vom 15.11.2019, 15:50 Options
Hallo Rainer!
Danke für die Antwort! Ich habe selbst auch ein bisschen rumgebastelt und noch selber eine Möglichkeit gefunden. Wollte jetzt noch fragen ob die performance mäßig einen Unterschied machen. Es kann nämlich gut sein, dass ich wenn mal alle Daten vorhanden sind bis zu 3000 Datensätze drin habe und da merkt mans dann schon ...
also
1)
2)
3)
4)deine variante
die texte sind bei mir definierte bereiche, jeweils eine spalte zb: a1 - a30 für all_country ... die bereiche werden nach dem hinzufügen von daten ständig erweitert
gibt es da jetzt zwischen den varianten performancemäßig einen unterschied?
und wie sieht es aus wenn ich zuvor noch eine fehlersuche mache, wird dann die matrix zwei mal durchgerechnet??
Vielen Dank für deine Hilfe!
Lg Robert
Danke für die Antwort! Ich habe selbst auch ein bisschen rumgebastelt und noch selber eine Möglichkeit gefunden. Wollte jetzt noch fragen ob die performance mäßig einen Unterschied machen. Es kann nämlich gut sein, dass ich wenn mal alle Daten vorhanden sind bis zu 3000 Datensätze drin habe und da merkt mans dann schon ...
also
1)
{=SUMME((Country=D$6)*(Year=D$7)*(Company="Hilti")*(Attribute=$A9)*Value)}2)
={SUMME(WENN(((Country=D$6)*(Year=D$7)*(Company="Hilti")*(Attribute=$A9));Value;"")){3)
=VERWEIS(1;1/(Country&Year&Company&Attribute=D$6&D$7&"Hilti"&$A9);Value)4)deine variante
die texte sind bei mir definierte bereiche, jeweils eine spalte zb: a1 - a30 für all_country ... die bereiche werden nach dem hinzufügen von daten ständig erweitert
gibt es da jetzt zwischen den varianten performancemäßig einen unterschied?
und wie sieht es aus wenn ich zuvor noch eine fehlersuche mache, wird dann die matrix zwei mal durchgerechnet??
=WENN(ISTFEHLER(variante-x);"";variante-x)
=WENN(ISTFEHLER(VERWEIS(1;1/(Country&Year&Company&Attribute=D$6&D$7&"Hilti"&$A9);Value);"";=VERWEIS(1;1/(Country&Year&Company&Attribute=D$6&D$7&"Hilti"&$A9);Value))Vielen Dank für deine Hilfe!
Lg Robert
Antwort 3 von rainberg vom 15.11.2019, 16:37 Options
Hallo Robert,
ich kann Dir nur bestätigen, dass Marixformeln immer mehr Resourcen benötigen als normale Formeln, wenn dann noch eine Fehlerabfrage hinzukommt wird logischerweise noch mehr Rechenzeit benötigt.
Es ist immer wichtig, die Bereiche dynamisch zu definieren um die Matrix nur so groß wie unbedingt erforderlich zu halten.
Für den Bereich "Country° (D2:Dxxx) würde ich folgende Formel nehmen
=BEREICH.VERSCHIEBEN(Tabelle1!$D$1;1;;ANZAHL2(Tabelle1!$D:$D)-1;)
Deine und mögliche andere Formeln kann ich leider nicht testen/erstellen, da ich Deine Datei nicht voreliegen habe.
Gruß
Rainer
ich kann Dir nur bestätigen, dass Marixformeln immer mehr Resourcen benötigen als normale Formeln, wenn dann noch eine Fehlerabfrage hinzukommt wird logischerweise noch mehr Rechenzeit benötigt.
Es ist immer wichtig, die Bereiche dynamisch zu definieren um die Matrix nur so groß wie unbedingt erforderlich zu halten.
Für den Bereich "Country° (D2:Dxxx) würde ich folgende Formel nehmen
=BEREICH.VERSCHIEBEN(Tabelle1!$D$1;1;;ANZAHL2(Tabelle1!$D:$D)-1;)
Deine und mögliche andere Formeln kann ich leider nicht testen/erstellen, da ich Deine Datei nicht voreliegen habe.
Gruß
Rainer
Antwort 4 von rhawke vom 15.11.2019, 16:59 Options
Hey coole Idee mit dem dynamischen Bereich. Muss ich mir merken.
Da ich aber so oder so schon ein VBA-Import Script für meine Matrixdaten habe, habe ich dort unten dran einfach 2 zeilen, welche die Bereiche neu definieren angehängt. funktioniert auch nicht schlecht
also ich hab mal getestet: ich hab den bereich einfach rießig (65000 zeilen) gemacht und geschaut was passiert:
die VERWEIS formel braucht jetzt ca. 6 sekunden wenn ich rein gehe un mit enter wieder bestätige ... die matrix formel hingegen gerade einmal 1 sekunde ...
nur hab ich jetzt ein problem. ich hätte gerne, dass wenn KEIN ergebnis gefunden wir nicht 0 sondern "" angezeigt wird. da es sich nämlich um abweichungen handelt ist eine abweichung von 0 ziemlich cool während kein ergebnis heißt, dass es keine zahl aus dem vorjahr gibt.
bei der verweis formel kommt da #NV raus. bei der Matrixformel einfach 0 . Gibts dafür ne möglichkeit?
lg Robert
Da ich aber so oder so schon ein VBA-Import Script für meine Matrixdaten habe, habe ich dort unten dran einfach 2 zeilen, welche die Bereiche neu definieren angehängt. funktioniert auch nicht schlecht
also ich hab mal getestet: ich hab den bereich einfach rießig (65000 zeilen) gemacht und geschaut was passiert:
die VERWEIS formel braucht jetzt ca. 6 sekunden wenn ich rein gehe un mit enter wieder bestätige ... die matrix formel hingegen gerade einmal 1 sekunde ...
nur hab ich jetzt ein problem. ich hätte gerne, dass wenn KEIN ergebnis gefunden wir nicht 0 sondern "" angezeigt wird. da es sich nämlich um abweichungen handelt ist eine abweichung von 0 ziemlich cool während kein ergebnis heißt, dass es keine zahl aus dem vorjahr gibt.
bei der verweis formel kommt da #NV raus. bei der Matrixformel einfach 0 . Gibts dafür ne möglichkeit?
lg Robert
Antwort 5 von rhawke vom 15.11.2019, 17:17 Options
hab auch noch die performance von deinen zwei oberen formeln getestet:
INDIREKT gleich schnell wie bei meinen Matrixformeln
INDEX so langsam wie bei mir die verweisformel
anscheinend steht er nicht so auf die verknüpfung der einzelnen argumente als text-string
ich werde also wahrscheinlich indirekt nehmen, weil dort kommt schön einfehler wenn NICHTS gefudnden wird. einziger wehrmutstropfen: die formel ist nicht so schön und intuitiv ... wie eine einfache Matrix-Summe
also nochmals vielen dank für eine hifle!
lg robert
INDIREKT gleich schnell wie bei meinen Matrixformeln
INDEX so langsam wie bei mir die verweisformel
anscheinend steht er nicht so auf die verknüpfung der einzelnen argumente als text-string
ich werde also wahrscheinlich indirekt nehmen, weil dort kommt schön einfehler wenn NICHTS gefudnden wird. einziger wehrmutstropfen: die formel ist nicht so schön und intuitiv ... wie eine einfache Matrix-Summe
also nochmals vielen dank für eine hifle!
lg robert
Antwort 6 von rainberg vom 15.11.2019, 17:49 Options
Hallo Robert,
wie gesagt ich kenne Deine Tabelle nicht, aber um die Fehlerabfrage einzusparen könntest Du die Fehlermeldung #NV durch die Bedingte Formatierung unterdrücken indem Du Schriftfarbe gleich Hintergrundfarbe setzt.
Die Null kannst Du ebenso unterdrücken oder durch das Zellformat "[<>0]" , aber beides wirkt nur optisch, der Zellwert bleibt trotzdem Null.
Gruß
Rainer
wie gesagt ich kenne Deine Tabelle nicht, aber um die Fehlerabfrage einzusparen könntest Du die Fehlermeldung #NV durch die Bedingte Formatierung unterdrücken indem Du Schriftfarbe gleich Hintergrundfarbe setzt.
Die Null kannst Du ebenso unterdrücken oder durch das Zellformat "[<>0]" , aber beides wirkt nur optisch, der Zellwert bleibt trotzdem Null.
Gruß
Rainer
Ähnliche Themen
Zeilen mit #BEZUG# löschen
Trudi 16.04.2007 - 148 Hits - 8 Antworten
SummenProdukt und Kalenderwoche
nighty 21.05.2007 - 129 Hits - 9 Antworten
excel summewenn suchkriterium von bis
evah 01.10.2007 - 256 Hits - 1 Antwort
DBANZAHL mit Datum als Suchkriterium
jojo5 05.10.2007 - 84 Hits - 2 Antworten
Sverweis, Suchkriterium steht nach Spaltenindex
AlexSocke 12.05.2008 - 43 Hits - 2 Antworten
Hinweis
Diese Frage ist schon etwas älter, Sie können daher nicht mehr auf sie antworten. Sollte Ihre Frage noch nicht gelöst sein, stellen Sie einfach eine neue Frage im Forum..
Neue Einträge
