Excel: Eine Zahl pro Namen und Kalenderwoche via SVERWEIS/WVERWEIS wiedergeben?
Hallo Zusammen
Im Excel soll ich (dank einer Hilfstabelle) pro Namen eine Zahl wiedergeben. Dass soll sich aber jede Woche aktualisieren, heisst es hat einen Bezug auf eine Zelle mit der aktuellen Kalenderwoche (=KALENDERWOCHE(HEUTE();2)-1). Ich habe jetzt lange probiert, aber es nicht geschafft, mit einem Verweis die Zahl für die Woche wiederzugeben. bei den Bildern sehr ihr ein Beispiel, wie es etwa aufgebaut ist. Falls es noch helfen kann: Die Zahl bei den Namen bei "Fehlende Einträge" ist eine Summe, die aus einem anderen Dokument/Tabellenblatt kommt.
Vielen Dank für die Hilfe
Michael
3 Antworten
Hallo Michael,
verstehe ich richtig, dass in der Übersicht, bei aktuelle Tabelle, der Wert eingetragen werden soll, der sich aus der Kombination Name und Woche in der Hilfstabelle ergibt?
Im aktuellen Beispiel wäre das:
Albert: 5
Frank: 5
Lena: 0
Dann kommt es darauf an, wie die Überschrift in der Hilfstabelle formatiert ist. Du gibst die KW als Zahl aus, also muss auch die Überschrift als Zahl formatiert sein. Im Moment sieht es nach Text aus. Formatiere die Überschrift zuerst mit Rechtsklick im Benutzerdefinierten Format "KW 0"
Anschließend kannst Du Deine Abfrage mit einem "WVERWEIS" realisieren.
=WVERWEIS(F2;Tabelle2!B2:O5;2;0) für Albert
=WVERWEIS(F2;Tabelle2!B2:O5;3;0) für Frank
=WVERWEIS(F2;Tabelle2!B2:O5;4;0) für Lena
F2 = Bezug auf die Kalenderwoche, also Dein Suchwert
Tabelle2!B2:O5 = Der Suchbereich, wo sollen die Werte gefunden werden
2-4 = Zeilenindex, aus welcher Zeile soll der Wert zurückgegeben werden
0 = Wahr oder Falsch
Einen WVERWEIS mit zwei Argumenten kannst Du auch erstellen, dann wäre es aber eine Matrixformel, das wäre dann etwas komplizierter. Für diese Datenmenge sollte mein Beispiel ausreichen
Viel Erfolg
Matrixformel weil ein S- bzw. WVERWEIS mit zwei Argumenten nicht anders abzufragen ist.
Wwas spricht gegen den SVERWEIS? Das ist eine super Funktion, die bei mir die meisten Probleme löst und wie gesagt war meine Lösung für die geringe Datenmenge ;-)
Über die Datenmenge habe ich untertrieben. Es sind jetzt Total 26 Personen über 52 KW. Umbenannt sind sie (1 und Formatierung als "KW"). Leider kann ich die (zensierten) Bilder davon nicht mehr anhängen :)
Tabelle1!B4: =INDEX(Tabelle2!$B$3:$O$5;VERGLEICH($A4;Tabelle2!$A$3:$A$5;0);VERGLEICH("KW"&$F$2;Tabelle2!$B$2:$O$2;0))
--> runterziehen Fertig.
Vielen Dank
Könntest du mir vielleicht den Denkweg aufzeichnen, da ich es noch in die andere Tabelle übertragen sollte und eigentlich keine Erfahrung mit "Index" habe.
Vielen Dank, Michael
Hallo Michael,
wenn du es unbedingt mit der Excel-Funktion VERWEIS(..) machen willst/musst, dann so:
Trick: in Tabelle2 in der Zeile 2 die Kalenderwoche lediglich als Zahl von 1 bis 14 eintragen ohne den Zusatz "KW" und dann die Zellen so formatieren: "KW"0. Jetzt steht in den Zellen KW2 ... aber die Funktion VERWEIS kann jetzt mit dem Index der KW sehr einfach verwendet werden.
Dann in Tabelle1 in B4 diese Formel: =VERWEIS($F$3;Tabelle2!B3:O3)
wichtig ist dabei, dass F3 ein fixer Bezug mit den Dollarzeichen vor der Zeilenzahl (F$3) sein muss, jetzt die Formel in B4 runterkopieren bis B6 - FERTIG!
Distel
Vielleicht, weil die Lehrer es einmal so gelernt haben und nie (d)eine Ausführungen zu der eleganteren Lösung mit INDEX und VERGLEICH gelesen haben. ?
Gruß Distel,
(der seither auch nur noch die INDEX-VERGLEICH-Lösung benutzt)
Lehrer arbeiten nicht nach "hab ich mal so gelernt" sondern nach einem Rahmenlehrplan - dieser wird sicherlich weniger von praxisbezogenen Leuten im Bildungsministerien erstellt.
Dennoch selbst für die Microsoft eigene Prüfung MOS Excel bzw. MOS Excel Expert würde ich lieber nicht gehen ohne mir die Verweise nochmal angesehen zu haben, AUCH wenn ich sie selbst kaum Nutzen werden.
Übrigens bedeutet Praxis nicht unbedingt das man im Job der Guru wird und alles nach den eigenen Anforderungen sofort umgestellt wird. In der Praxis wird es extrem viel mehr Tabellen mit Sverweis als mit Index/Vergleich geben. Es ist nicht falsch zu wissen das es sowas gibt und wie es funktioniert.
Wenn man zudem die Lösung von Distel mit der Fullminanten Index/Vergleich Lösung gegenüberstellt sollte klar sein was einen Excelanfänger eher abschreckt. Seien wir ehrlich, in der Schule sind die "Excel-Cracks" diejenigen die eine 1x verschachtelte Wennfunktion mit gemischten Bezügen aufstellen können.
Sie Aggregatfunktion ersetzt seit 2013 auch die Summefunktion. Aggregat ist viel leistungsfähiger. Nur weil man mit Aggregat viel machen kann, bedeutet das nicht "Summe" sollte aus allen Lehrplänen und Tabellen verbannt werden oder?
Meine Indexformel ist nur deswegen so aufgebläht weil sie sowohl horizontal als auch Vertikal flexibel ist.
Genau darauf will ich ja hinaus. Ich kann mir sicherlich 99,9% aller Exceltabellen geben lassen und feststellen das irgendwo, irgendwie, irgendwas nicht voll flexibel programmiert ist...
Ockhams Rasiermesser, Sparsamkeitsprinzip...
Eine Lösung sollte nur die notwendige Komplexibilität aufweisen, nicht die mögliche. Für einen begrenzten Aufgabenbereich ist der Sverweis funktional - und eben keine Qual!
Quälend wird der Sverweis erst wenn man den begrenzten Aufgabenbereich verlassen will.
Ich finde ihn vor allem durch den Spaltenindex furchtbar unübersichtlich. Man darf erst mal 24 oder 56 Spalten des Angegebenen bereichs abzählen nur um zu erfahren aus welcher Spalte der Wert kommt. Über die eindimensionale Indexformel sehe ich das direkt. Ausserdem kann ich nach links und habe einen Ersatz sowohl für SVerweis als auch WVerweis und in dem Fall hier sogar beides zugleich.
Warum eine Matrixformel wenn es mit Bordmitteln auch geht. Es geht sogar über eine Kombination aus S- und W-Verweis, aber da weigere ich mich genauso wie gegen jeden anderen SVERWEIS (o. WVERWEIS)