Weihnachten rückt immer näher und entgegen jeder Vernunft wird die Zeit immer hektischer. Daher gibt es heute wieder ein paar schnell umsetzbare Excel-Tipps, die dich nicht unnötig lange vom Geschenkekauf abhalten sollen.
In diesem Beitrag zeige ich dir Tipps zu folgenden Themen:
- Quickie 106: Der erste Tag zu einer Kalenderwoche
- Quickie 107: In welcher KW liegt der heutige Tag
- Quickie 108: Unnötige Leerzeichen entfernen
Da ist sicher auch für dich etwas dabei.
Excel-Quickie Nr. 106: Der erste Tag zu einer Kalenderwoche
Ich hatte vor längerer Zeit einen Artikel dazu geschrieben und dort eine relativ komplizierte Formel vorgestellt. Der heutige Tipp kommt von meinem Leser Armin Miedl, der eine deutlich einfachere Lösung für das Problem für uns hat.
Angenommen, das Jahr steht in B3 und die Kalenderwoche in B4. Dann liefert folgende Formel (bei mir in Zelle B7) ein Datum, das auf jeden Fall schon mal in der gewünschten Woche liegt:
=DATUM(B3;1;4)+7*(B4-1)
Der 4. Januar ist immer in KW1, deshalb ist der berechnete Tag immer in der gewünschten Woche.
Und nun lässt sich einfach der erste Tag in der betreffenden Woche ermitteln:
=B7-WOCHENTAG(B7;2)+1
Der erste Tag einer Kalenderwoche
Die übrigen Tage kann man mit +1 berechnen.
Vielen Dank an Armin für diese clevere Lösung!
Excel-Quickie Nr. 107: In welcher Kalenderwoche liegt der heutige Tag?
Du möchtest wissen, was wir gerade für eine Kalenderwoche haben, hast aber leider keinen Kalender zur Hand?
Zum Glück gibt’s ja Excel. Diese Frage lässt sich fast wortwörtlich in eine Formel packen:
=KALENDERWOCHE(HEUTE())
Die aktuelle Kalenderwoche
Kurz und schmerzlos. Ein echter Quickie eben!
Noch ein paar kleine Hinweise:
Der Funktion KALENDERWOCHE kann man als zusätzlichen Parameter einen Zahl-Typ übergeben. Damit wird gesteuert, mit welchem Wochentag die KW beginnen soll. Wer hier nach dem ISO-Standard rechnen möchte, nimmt den Zahltyp 21:
=KALENDERWOCHE(HEUTE();21)
Benutzern von Excel 2013 und 2016 steht darüberhinaus mit ISOKALENDERWOCHE eine weitere Funktion zur Verfügung, die ohne diesen Zusatzparameter das richtige Ergebnis liefert:
=ISOKALENDERWOCHE(HEUTE())
Damit wird die Kalenderwoche nach ISO-Norm bestimmt. Hier der Vergleich der beiden Funktionen und der Wirkung der Zahl-Typen:
Vergleich KALENDERWOCHE vs. ISOKALENDERWOCHE
Excel-Quickie Nr. 108: Unnötige Leerzeichen entfernen
Ein Ärgernis, das gerne im Umgang mit SVERWEIS & Co. auftritt, sind unnötige Leerzeichen in den abzugleichenden Spalten. Folgendes Szenario:
Die Werte aus zwei Tabellen sollen gegeneinander abgeglichen werden, z.B. um für eine Artikelnummer aus der einen Liste den Verkaufspreis aus einer anderen Liste zu finden. Die Daten dieser anderen Liste wurden jedoch nicht direkt in Excel erfasst, sondern stammen aus einem externen System und wurden in Excel importiert.
Ein Abgleich der Artikelnummern über einen SVERWEIS scheitert aber aus zunächst unerfindlichen Gründen, obwohl die gesuchte Artikelnummer definitiv in beiden Tabellen enthalten ist:
Wenn der SVERWEIS scheitert…
Wenn man für eine Artikelnummer oben in die Bearbeitungszeile klickt, sieht man auch die Wurzel des Übels. Am Ende der Artikelnummer hängen ein paar Leerzeichen:
…kann es an Leerzeichen liegen
Und damit sind für den SVERWEIS scheinbar identische Artikelnummern eben nicht mehr identisch.
Du kannst jetzt entweder alle Leerzeichen umständlich „zu Fuß“ löschen.
Oder du verwendest die Quickie-Lösung mit der GLÄTTEN-Funktion. Hiermit lassen sich kurz und schmerzlos sämtliche unnötigen Leerzeichen am Ende (und auch am Anfang!) entfernen. Jetzt muss der SVERWEIS nur noch auf die geglättete Artikelnummer angewendet werden, und schon funktioniert’s:
Leerzeichen entfernen mit der GLÄTTEN-Funktion
Und das war’s auch schon wieder für heute. Viel Spaß beim Ausprobieren und noch eine schöne Adventszeit!
P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)
P.P.S. Das Problem sitzt meistens vor dem Computer.
Hallo Martin,
bei solchen Aktionen haue ich nachher immer die Formeln raus, da sie in entsprechender Menge zur Bremse werden können – soll heißen: Datein importieren (wie auch immer) – Formel runterziehen – strg+c – rechtsklick – als Werte einfügen. Lässt man idealerweise in der ersten (Daten)-Zeile (wir haben natürlich eine eindeutige Spaltenüberschrift 😉 ) die Formel stehen und ersetzt nur Datenzeile 2-…), hat man sie später wieder zur Verfügung, denn eine Zeile selbst mit umfangreichen Sverweisen bemerkt man nicht, 100.000 dann schon.
@Kalenderwoche: Jaja, Excel kann man echt für fast alles nutzen *gg* Da ist die Liste an Dingen, die man NICHT mit Excel bewältigen kann vermutlich deutlich kürzer.
lg Andi
Hallo Andi,
ja, anschließend die Formeln durch Werte ersetzen ist eine gute Idee. Ist besser für die Performance und vermeidet Ärger, wenn man auf die Idee kommt, die Originalwerte zu löschen…
Vielleicht wäre eine solche Liste mal eine lustige Idee: Dinge, die Excel NICHT kann 🙂
Schöne Grüße,
Martin
Hallo Herr Weiß,
danke für Ihre tollen und aufwendig gestalteten Beiträge.
Zum Excel-Quickie 106 hier noch eine kürzere Version…
=DATUM(B3;1;7* B4-3-WOCHENTAG(DATUM(B3;;);3))
Viele Grüße
de
Hallo Herr Emonts,
vielen Dank für das Lob und vor allem für diese knackige Lösung. Funktioniert einwandfrei, aber da muss ich selbst erst ein wenig daran knappern.
Schöne Grüße,
Martin
Diese Version ist echt knackig! Ich wäre interessiert an einer kurzen Erläuterung à la Martin Weiss, warum die funktioniert.
Hallo,
nur als kleine Ergänzung….
Wenn man sich die Zwischenspalte mit „Glätten“ sparen möchte, funktioniert auch die folgende Matrix-Formel:
={SVERWEIS(B11;GLÄTTEN(A6:B8);2;0)}