Tag Archives: bereich

#Störenfriede in #Excel in den Griff kriegen – #Summe ganz #modernisiert

Eine Excel-Liste. Und Sie brauchen die Summe irgendwelcher berechneten Werte. Die Funktion SUMME kennen Sie:

XTipp Aggregat1

Aber das Ergebnis stellt Sie nicht zufrieden:XTipp Aggregat2

Fehlerwerte! #DIV/0! … wie konnte das passieren? Eine Summe ist doch keine Division! Jaaaa…. aber: Sie sehen es schon: in dem Bereich, in dem die Zahlen stehen, die Sie summieren wollen, sind auch ein paar #DIV/0!-Werte, die sind die eigentlichen Übeltäter. Die SUMME kommt mit denen einfach nicht zurecht. Also was nun? Die Fehler loswerden, beispielsweise mit einer komplizierten WENN-Funktion? Ein andermal – heute machen wir’s uns leicht (falls Sie Excel in der Version 2010 oder höher benutzen), und verwenden eine recht junge Funktion:

XTipp Aggregat3

AGGREGAT ist sozusagen der jüngste Sprößling in der Familie der Zusammenfassungs-Funktionen. Sie möchte drei Informationen von Ihnen haben:

  • mit welcher Funktion soll sie Ihre Daten zusammenfassen? Das ist – wie schon im TEILERGEBNIS – verschlüsselt; zum Glück sind die Schlüssel gleich, und wir können für die Summe den Schlüssel 9 benutzen.
  • Option: Wie soll sie mit Störenfrieden umgehen? Schon wieder so eine Schlüsselsache. Ich schreib’s für Sie ab:
    0 – Verschachtelte TEILERGEBNIS- und AGGREGAT-Funktionen ignorieren
    1 – Ausgeblendete Zeilen, verschachtelte TEILERGESNIS- und AGGREGAT-Funktionen ignorieren
    2 – Fehlerwerte, verschachtelte TEILERGESNIS- und AGGREGAT-Funktionen ignorieren
    3 – Ausgeblendete Zeilen, Fehlerwerte, verschachtelte TEILERGESNIS- und AGGREGAT-Funktionen ignorieren
    4 – Leerwerte ignorieren
    5 – Ausgeblendete Zeilen ignorieren
    6 – Fehlerwerte ignorieren
    7 – Ausgeblendete Zeilen und Fehlerwerte ignorieren
    Am beliebtesten sind 6 und 7 – mit 6 reagiert AGGREGAT wie die zugrunde liegende Zusammenfassungsfunktion, allerdings tun Fehler nicht mehr weh; mit 7 reagiert AGGREGAT wie TEILERGEBNIS, nur ebenfalls ohne Fehlerschmerzen
  • Bereich, in dem die Werte stehen, die zusammengefasst werden sollen.

Das war’s schon. Die neue Summe ist fertig und funktioniert wunderbar. Bevor Sie den Beweis sehen dürfen, verwenden wir nur die Option 7 auch gleich – damit wir beim Filtern sowohl Gesamtsumme als auch die Summe der gerade sichtbaren Werte sehen:XTipp Aggregat4

Na, ist das nicht ein schönes Ergebnis?

XTipp Aggregat6

Übrigens: wie alle Zusammenfassungsfunktionen ignoriert auch AGGREGAT alle Texte und Leerzellen im ausgewählten Bereich. Das hat die Funktion von ihren älteren Geschwistern gelernt. Denen ist sie ganz schön über den Kopf gewachsen, finden Sie nicht auch?

ganz bestimmte Zahlen #summieren – #SUMMEWENN kann das in Excel

Sie möchten Zahlen aus Ihrem Excel-Arbeitsblatt addieren – und zwar nicht beliebige, sondern ganz bestimmte. Versuchen Sie’s doch einmal mit der Funktion SUMMEWENN. Mit ihr können Sie auf zweierlei Wegen addieren: In diesem ersten Beispiel will ich einfach die 100er addieren – die anderen Zahlen sind mir egal. SUMMEWENN($A$1:$A$8;100) macht genau das – es sucht im Bereich $A$1:$A$8 nach Einträgen, die 100 lauten und addiert diese. Alle anderen Zahlen werden ignoriert. Das Ergebnis lautet hier dann 400, denn in vier Zellen wurde 100 gefunden. Die…

Read More »

#Verweis auf #Tabelle in #Excel – #kochfest

Sie befürchteten, mit der Kocherei der letzten Tage ist schon Schluss? Aber nein! Jetzt geht’s erst richtig los: nun geht’s ans Mengen bestimmen. Eine weitere neue Tabelle entsteht, in der für jedes Menü die Mengen der verwendeten Produkte bestimmt werden; wir gehen sie Schritt für Schritt durch:

Menü: hier kommt die Bezeichnung der Speise hin, so wie sie (wichtig!) in der Speiseplan-Tabelle geschrieben ist. Pro Menü gibt es mehrere Zeilen.

was: das zu verwendende Produkt. Muss genau so geschrieben werden wie in der Produkte-Tabelle (wichtig!).

Einheit: wird gar nicht geschrieben, sondern aus der Produkte-Tabelle geholt, und zwar mit der Formel =SVERWEIS([@was];Produkte[#Alle];5;FALSCH). SVERWEIS schlägt nach, was in der fünften Spalte der Produkte-Tabelle steht, und zwar in der Zeile, in der in der ersten Spalte das steht, was hier in der Spalte mit der Überschrift was zu finden ist. FALSCH bedeutet nur, dass genau gesucht werden soll (also nicht die Einheit aus der Semmel-Zeile geliefert wird, wenn nach Semmelbröckerl gesucht wird) – und wenn ein Produkt nicht gefunden wird, dann sehen Sie den Fehler #NV und müssen das Produkt eben noch in der Produkte-Tabelle ergänzen.

5Pers: hier schreibe ich die Menge hinein, die ich verwende, wenn ich für fünf Personen koche (das ist meine Referenzmenge – mein Mann und ich und drei meiner Kinder. Der vierte ist schon erwachsen und sehr sportlich und isst für zwei und ist daher als Referenz ungeeignet.)

AFV: A = Alle, F = Fleischesser, V=Vegetarier kennzeichnet, wer aller diese Zutat essen wird. (Für die Vegetarier gibt’s nämlich zumeist eine Variante der Fleischesser-Speise, wenn die Speise nicht sowieso vegetarisch ist.)

Kategorie: Wieder etwas, was wir uns mit einer Formel ausfüllen lassen: =SVERWEIS([@was];KatListe;2;FALSCH). Dieses Mal suchen wir mit der Funktion SVERWEIS unser was in einem Bereich, den ich KatListe genannt habe*, und lassen uns den Inhalt der zweiten Spalte zurückliefern.

Für heute ist das genug, schaut schon recht schön aus, meine Mengentabelle. Morgen kommen noch ein paar neue Formeln dran, mit neuen Funktionen drin. Tipp Excel SVERWEIS #NV

*Einem Bereich einen Namen geben geht so: einfach den Bereich markieren, und dann im Namenfeld den gewünschten Namen eingeben. Dieser muss mit einem Buchtstaben beginnen, darf Buchstaben, Ziffern und den Unterstrich _ enthalten und kein reserviertes Wort sein (im Zweifelsfall hilft der Unterstrich da enorm, denn der kommt in keinem reservierten Wort vor 🙂 )

Tipp Excel benannter Bereich

#Excel #Basics: #Addieren

Sie haben ein paar Zellen mit Zahlen drin. Die möchten Sie gerne addieren. Dazu gibt es zwei Wege. Erstens: Sie basteln eine Formel, die alle Zellbezüge auf Ihre Zahlenzellen mit einem Pluszeichen verbindet. =A1+A2+A3+A4+A5 bedeutet den Auftrag an Excel (=), das, was in der Zelle A1 (also in der ersten Zeile der Spalte A) steht, mit dem, was in der Zelle A2 (also in der zweiten Zeile der Spalte A) steht, zu addieren, und dann noch mit dem, was in A3, A4, und A5 steht.…

Read More »

Daten in #Excel #zählen, aber nicht alle – #zählenwenn #flexibel

Gestern haben wir Daten gezählt, die einem bestimmten Kriterium genau entsprechen – Sie erinnern sich? Auf die Eingabe von meat hin, wurden alle Einträge gezählt, die eben meat entsprachen. Heute zählen wir Daten, die einem Wert in einer Zelle entsprechen. Das geht ganz leicht. Statt beim Kriterium einen festen Wert (wie meat) einzugeben, darf’s auch ein Zellbezug sein – voila. Mit ZÄHLENWENN($A$2:$A$20;N3) zählen Sie alle Zellen im Bereich A2:A20, in dem das steht, was in N3 steht. Der Zweck dahinter ist meist der, dass wir (wie…

Read More »

#wo und wie #sucht #outlook?

Dieses Outlook macht, was es will, nicht wahr? Einmal durchsucht es Ihr Postfach, ein anderes Mal nur den aktuellen Ordner … Wie wäre es, wenn Outlook das macht, was SIE wollen?

Nun, bei jedem Suchvorgang dürfen Sie frei entscheiden:

Tipp Outlook Suche Bereich2

Bestimmen Sie den Bereich, in dem aktuell gesucht werden soll, auf SUCHTOOLS > SUCHEN > Bereich.

  • Allen Postfächern: in all Ihren Postfächern wird gesucht (nur verfügbar, wenn Sie mit mehr als einem Postfach verbunden sind)
  • Aktuellem Postfach: im gesamten aktuellen Postfach wird gesucht (also in allen Ordnern und Unterordnern)
  • Aktuellem Ordner: nur der aktuell ausgewählte Ordner wird durchsucht
  • Unterordner: der aktuell ausgewählte Ordner und all seine Unterordner werden durchsucht (nur verfügbar, wenn Unterordner vorhanden sind)
  • Alle Outlook-Elemente: alle Outlook-Elemente (also auch Kontakte, Aufgaben, Kalendereinträge, …) werden durchsucht. Sie wissen, Sie müssen in die Simmeringer Hauptstraße, Sie haben irgendwo die Hausnummer notiert, aber nicht mehr, ob bei einem Kontakt, in einem Termin oder in einer E-Mail …

Und was ist da bitte der Standard?

Auch das bestimmen Sie: DATEI > Optionen > Suchen:

Tipp Outlook Suche Optionen

Im Abschnitt Ergebnisse steuern Sie, wo im Prinzip gesucht wird. Individuell dürfen Sie’s dann immer noch ändern.

#Bereichsbezug in #Excel

Brauchen Sie in Excel einen Bereichs-Zellbezug, so schreiben Sie ihn mit einem Doppelpunkt, also A1:A10 für alle Zellen von A1 bis A10. (Beispiel: =SUMME(A1:A10) addiert alle Zahlenwerte in den Zellen A1, A2, A3, A4, A5, A6, A7, A8, A9 und A10, ignoriert dabei leere Zellen ebenso wie Textwerte und stolpert lediglich über Fehlerwerte.) Wenn Ihnen der Doppelpunkt zu mühsam zu Tippen ist, probieren Sie’s doch einfach mal so: A1..A10 oder so: A1.A10 🙂 Excel „übersetzt“ beides beim Enter-Drücken wieder in den Doppelpunkt. 🙂

Read More »

#Excel #Filter #erweitert #wasistdas?

Tipp Excel Filter Erweitert Basis

In Excel gibt es auf der DATEN-Registerkarte in der Gruppe Sortieren und Filtern die Funktion Erweitert. Was tut die, wie funktioniert die?

Mit Hilfe des erweiterten Filters (auch Spezialfilter genannt, das klingt beeindruckender) können Sie Kriterien in einen Kriterienbereich eingeben und Ihre Daten noch differenzierter filtern, als es mit dem herkömmlichen Filter möglich ist.

Für ein erstes Verständnis genügt das hier gezeigte Beispiel. Die zu filternden Daten sind im Bereich A6:H17 – eine Überschriftszeile ist oben dabei, und das ist gut, denn die muss sein. Dann brauchen wir noch einen Kriterienbereich. In dem müssen sich die Überschriften der Spalten wiederfinden, nach denen gefiltert werden soll (es können auch noch weitere Überschriften da sein, das stört nicht). Darunter füllen Sie die Zellen mit Ihren Kriterien. Für etwas, das gleich sein soll, schreiben Sie den gesuchten Wert einfach in die Zelle (sweets) oder verformeln ihn für mehr Flexibilität mit Gleichheitszeichen (=“=“ & A8 für „soll gleich sein mit dem, was ich gerade in Zelle A8 geschrieben habe“). Sie können auch Vergleichsoperatoren anwenden (>8000) und auch die verformeln (=“>“ & A8).

Jetzt die Funktionalität: DATEN > Sortieren und Filtern > Erweitert, und im Dialogfeld geben Sie den Listenbereich an (also den Bereich, in dem Ihre Daten samt Überschrift stehen) und den Kriterienbereich ebenfalls (den Zusatzbereich mit benötigten Überschriften und Vergleichskriterien). Dann steht noch zur Auswahl, die Filterung vor Ort vorzunehmen oder das Ergebnis gleich in einen neuen Bereich zu kopieren – wählen Sie das Passende.

Geschafft!

Demnächst dazu noch mehr 🙂

 

#VBA Codeschnipsel füllt #Luecken in #Excel

Tipp VBA LueckenFuellenZur Abwechslung etwas VBA.

Folgende Prozedur füllt die Lücken in einem markierten einspaltigen Bereich in Excel nach unten aus – und zwar so lange, bis ein neuer Inhalt kommt, dann wird die nächste Lücke mit dem neuen Inhalt gefüllt, und so weiter, …

ach was, genug der Worte: siehe vorher-nachher-Bild!

 

 

Sub LueckenFuellen()

Dim sRng As Range
Set sRng = Selection
Dim aZeile As Long
Dim eZeile As Long
Dim Zeile As Long
Dim Spalte As Long

aZeile = sRng.Row
eZeile = sRng.Row + sRng.Rows.Count – 1
Spalte = sRng.Column
Dim zCont As Variant

For Zeile = aZeile To eZeile
If Not IsEmpty(Cells(Zeile, Spalte)) Then
zCont = Cells(Zeile, Spalte).Value
Else
Cells(Zeile, Spalte).Value = zCont
End If
Next Zeile
End Sub

(Lässt sich übrigens noch ein wenig aufpolieren, klar. Allerdings: weniger dirty bedeutet immer auch weniger quick. Dem Kunden genügt’s jedenfalls auch so 🙂 )

die #Länge der #Liste mit #gültigen #Daten ist #variabel

die #Länge der #Liste mit #gültigen #Daten ist #variabel

Sie verwenden die Gültigkeitsprüfung, um zu verhindern, dass BenutzerInnen einfach irgend etwas in Ihre Arbeitsmappe eintragen? Sie verwenden auch Listen, um die gültigen Eingaben zu definieren? Bravo! (Wenn nein: lesen Sie ein bisschen in der Vergangenheit dieses Blogs, probieren Sie DATEN > Datentools > Datenüberprüfung aus, oder fragen Sie mich.)

Oh – Sie haben dazu noch eine Spezialfrage: der Bereich, in den Sie die gültigen Werte eintragen, wird nicht immer zur Gänze gefüllt – oft bleiben am Ende ein paar leere Zellen. Die sehen im DropDown-Feld dann unschön aus.

Hier ist die Abhilfe: statt im Feld „Quelle“ auf den Bereich zu verweisen, benützen Sie die Funktion INDIREKT wie folgt:

=INDIREKT(„$A$1:$A$“ & (ZÄHLENWENN($A$1:$A$13;“>““)))

(davon ausgehend, dass Ihr Listenbereich der Bereich A1:A13 des aktuellen Blatts ist; sonst müssen Sie die Formel noch ein bisschen anpassen.)

Alles wird besser 😉