Office: (Office 2007) Berechnungen in SVERWEIS o. Ä.

Hallo Leute,

ich hoffe, jemand kann mir helfen:

Ist es möglich, in einem SVERWEIS Berechnungen durchzuführen. Sinngemäß so:

=SVERWEIS(Gesamtübersicht!$A2;EDV!$A$8:$ag$35;9;FALSCH)

Statt den Spaltenindex (hier 9) möchte ich aber den Mittelwert aus dem Bereich J8:AG8 wiedergegeben haben. Dafür möchte ich aber keine separate Spalte einfügen.

Der SVERWEIS ist da bestimmt nicht die richtige Formel. Ich weiß aber auch nicht, welche ich nehmen soll.

Rauskommen soll:
Vergleiche den Wert im Feld Gesamtübersicht!$A2, gehe dann in das Tabellenblatt EDV zur der entsprechende Zeile und gib in dieser Zeile den Mittelwert des Bereiches J8:AG8 wieder.

:)

 

Also die Formel kannst beim Spaltenindex anwenden. das ist kein Problem, nur bei der letzten Zahl Null bzw. dem Falsch kannst nichts anderes einsetzen als WAHR, FALSCH, 1 oder 0.

Allerdings gibt die 9 den Spaltenindex wider.
Der Wert, der gefunden werden soll und der Spaltenindex geben zum Schluss dann quasi Koordinaten in einer Tabelle an, wo sich das Ergebnis befindet, sverweis gibt nicht das Ergebnis direkt an. Also wirst du mit der Mittelwert-Formel nicht weit kommen.

 

Stimmt. Mit Formel geht es nicht. Was gibt es denn da für eine Lösung, damit ich es so hinbekomme:

Vergleiche den Wert im Feld Gesamtübersicht!$A2, gehe dann in das Tabellenblatt EDV zur der entsprechende Zeile und gib in dieser Zeile den Mittelwert des Bereiches J8:AG8 wieder.

 

Also am einfachsten finde ich es, wenn du in der Detailtabelle eine extra Spalte anlegst, in der du den Mittelwert ausgibst und mit SVERWEIS dann auf die entsprechende Spalte verweist.

in einer einzigen Formel geht das glaub ich gar nicht.
An die anderen: korrigiert mich, wenn ich falsch liege.

 

Das mit der extra Spalte in der Detailtabelle möchte ich aber vermeiden. Ich beziehe mich bei diesen Berechnungen auf über 100 Tabellen in 20 Excel-Dateien (in einer Datei geht das nicht, um Fehleintragungen zu vermeiden!)

Es muss doch aber irgendeinen Weg geben?

 

Hallo ProNi,

das geht zum Beispiel so:

A B C D 1 a 1 4 5 2 b 2 5 6 3 c 3 6 7 4         5 b 4,33333333     Formeln der Tabelle B5 : =MITTELWERT(BEREICH.VERSCHIEBEN(INDEX($A:$A;VERGLEICH($A5;$A:$A;0));0;1; 1;3)) Gruß Ingolf   OfficeUser, 23. Januar 2008 #6 PRONI ProNi Danke für die Hilfe, kannst du mir noch weiterhelfen: =MITTELWERT(BEREICH.VERSCHIEBEN(INDEX(EDV!A8:A35;VERGLEICH($A2;EDV!A8:A35;0));0; 1; 1;3)) EDV!A8:A35 (bei INDEX) -> Bereich im Tabellenblatt EDV, in dem nach $A2 gesucht wird ??? EDV!A8:A35 (bei VERGLEICH) -> Bereich im Tabellenblatt EDV, in dem nach $A2 gesucht wird ??? 0;1; 1;3 -> [Zeile];[Spalte];[Höhe];[Breite] -> was ist damit gemeint? Wenn ich hier die Werte ändere, kommt jedes Mal ein anderes Ergebnis raus?   ProNi, 23. Januar 2008 #7 INGGI IngGi Berechnungen in SVERWEIS o. Ä. Hallo ProNi, wenn der gesuchte Eintrag zum Beispiel in Zelle A8 steht, benötigst du ja im Endeffekt die Formel =MITTELWERT(EDV!J8:AG8) Um den Bereich EDV!J8:AG8 zu bekommen, gehst du nun folgendermaßen vor. Zunächst suchst du mit der Funktion VERGLEICH den Wert der Zelle Gesamtübersicht!A2 im Bereich EDV!A8:A35. Im Beispiel liefert dir die Funktion =VERGLEICH(Gesamtübersicht!A2;EDV!A8:A35;0) den Wert 1, da der gesuchte Eintrag in der ersten Zelle des durchsuchten Bereiches, also in EDV!A8 gefunden wird. Mit Hilfe der Funktion INDEX erhälst du nun in einem 2. Schritt einen Bezug auf die entsprechende Zelle EDV!A8 aus dem durchsuchten Bereich: =INDEX(EDV!A8:A35;VERGLEICH(Gesamtübersicht!A2;EDV!A8:A35;0)) Jetzt fehlen noch 2 weitere Schritte bis zum Ziel. Zum einen musst du, ausgehend von der gefundenen Zelle noch um 9 Zellen nach rechts bis zur Spalte J rücken und zum anderen den Zellbereich von dort ausgehend auf 24 Zellen bis Spalte AG ausdehnen. Beides geht mit der Funktion BEREICH.VERSCHIEBEN. Mit dem Parameter Zeile kannst du den Bezug nach oben oder unten verschieben, mit dem Parameter Spalte nach links oder rechts, mit dem Parameter Höhe kannst du den Zellbereich nach oben oder unten erweitern und mit dem Parameter Breite nach links oder rechts. Für unser Beispiel müsste das dann so aussehen: =BEREICH.VERSCHIEBEN(INDEX(EDV!A8:A35;VERGLEICH(Gesamtübersicht!A2;EDV!A8:A35;0) );0;9;1;24) Die Parameter 0;9;1;24 bedeuten hier also - gehe 0 Zeilen nach unten - gehe 9 Spalten nach rechts - nimm einen Zellbereich, der 1 Zeile hoch ist - nimm einen Zellbereich, der 24 Spalten breit ist (Spalte J bis AG ==> Spalte 9 bis 33 ==> 24 Spalten) Jetzt fehlt nur noch die Mittelwertfunktion: =MITTELWERT(BEREICH.VERSCHIEBEN(INDEX(EDV!A8:A35;VERGLEICH(Gesamtübersicht!A2;ED V!A8:A35;0));0;9;1;24)) Gruß Ingolf   IngGi, 23. Januar 2008 #8 PRONI ProNi Klasse! Danke!   ProNi, 23. Januar 2008 #9 (Du musst angemeldet oder registriert sein, um eine Antwort erstellen zu können.) Ignorierte Inhalte anzeigen Thema: Berechnungen in SVERWEIS o. Ä. Die Seite wird geladen... Berechnungen in SVERWEIS o. Ä. - Similar Threads - Berechnungen SVERWEIS Positive und Negative Stunden berechnen in Microsoft Excel Hilfe Positive und Negative Stunden berechnen: Moin, ich komme einfach nicht weiter und ich habe auch gegoogelt ohne Ende. Kann mir einer helfen für eine Formel um Negative mit positiven Stunden zu Summieren? Ich habe diese Formel... Uhrzeiten berechnen in Microsoft Excel Hilfe Uhrzeiten berechnen: Hallo zusammen, ich hatte früher eine Tabelle wo ich mit Uhrzeiten gerechnet habe, Diese finde ich leider nicht mehr. Mein problem besteht darin dass ich Zeiten aus einem Erfassungs Programm in... Umsatzsteuer mit Dropdown berechnen - Rechnung in Microsoft Word Hilfe Umsatzsteuer mit Dropdown berechnen - Rechnung: Hallo zusammen, für eine Rechnung soll im Dropdown die Umsatzsteuer von 0%, 7% oder 19% ausgewählt werden und dann berechnet werden. Wie bekomme ich das am besten hin? Datei anbei. Danke und Grüße... Stunden aus Monat/Datumsbereich für jede Person berechnen in Microsoft Excel Hilfe Stunden aus Monat/Datumsbereich für jede Person berechnen: Hallo, wir haben jedes Jahr eine Excel-Datei für uns Mitarbeiter, wo wir unsere Tageszeiten eintragen. Wir sind nicht fest geplant und können von daher eigentlich kommen und gehen wann wir... Miettage berechnen abhängig von der Saison (SVERWEIS / VERGLEICH?) in Microsoft Excel Hilfe Miettage berechnen abhängig von der Saison (SVERWEIS / VERGLEICH?): Hallo, ich brauche eure Hilfe. Ich versuche gerade eine Excel Tabelle zu erstellen für einen Wohnmobilverleih. Die Tabelle soll nach Reisestart- und ende die gültigen Saisonwerte selber... Verwenden von Namen in Verweisformel durch Berechnen / Excel 2010 englisch in Microsoft Excel Hilfe Verwenden von Namen in Verweisformel durch Berechnen / Excel 2010 englisch: Hallo Forum, ich habe folgende funktionsfähige Formel konstruiert: =HLOOKUP(H_Key;Daten_200;VLOOKUP(Key;Daten_200;2;FALSE)+1;FALSE) (in... Hilfe bei einer Berechnung mit SVERWEIS in Microsoft Excel Hilfe Hilfe bei einer Berechnung mit SVERWEIS: Hallo, ich bräuchte mal dringend Hilfe :-D Ich soll die Note der Schüler anhand des SVERWEISes errechnen lassen, aber trotz der Hilfefunktion bekomme ich das nicht hin. Kann sich das mal... Benutzername oder E-Mail-Adresse: Besitzt du schon ein Benutzerkonto? Nein, erstelle jetzt ein Benutzerkonto. Ja, mein Passwort ist: Hast du dein Passwort vergessen? Angemeldet bleiben Anmelden oder registrieren Suche Nur die Titel durchsuchen Erstellt von: Trenne Benutzernamen durch Kommata. Neuer als: Nur dieses Thema durchsuchen Nur dieses Forum durchsuchen Die Ergebnisse als Themen anzeigen Nützliche Suchen Themen mit aktuellen Beiträgen Mehr Diese Seite verwendet Cookies, um Inhalte zu personalisieren, diese deiner Erfahrung anzupassen und dich nach der Registrierung angemeldet zu halten. Auf dieser Website werden Cookies für die Zugriffsanalyse und Anzeigenmessung verwendet. Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies. Akzeptieren Weitere Informationen... Information ausblenden