LibreOffice-Tipps

Besser arbeiten mit LibreOffice

Die LibreOffice-Tipps verraten in dieser Ausgabe u. a., wie Sie in Calc einen Preisvergleich für mehrere Anbieter automatisieren und mit Zellkommentaren arbeiten. Außerdem erfahren Sie, wie Sie eigene Grafiken in die LibreOffice-Gallery einfügen.

Tipp: Calc: Anbieter mit dem besten Angebot anzeigen

Sie erfassen in einer Tabelle die Preisangebote verschiedener Anbieter. In der ersten Spalte stehen die Artikel, und ab Spalte C folgen die Preise der Anbieter für jeden Artikel, wobei die entsprechenden Firmennamen als Spaltenüberschriften in C1, D1 usw. eingetragen sind. Nun soll in Spalte B, also direkt neben den Artikelnamen, der Name des günstigsten Anbieters angezeigt werden.

Um dies zu erreichen, müssen Sie mit einer VERGLEICH()-Funktion ermitteln, an welcher Position der kleinste Preis in der jeweiligen Artikelzeile auftritt. Anschließend lassen Sie sich mit der INDEX()-Funktion den Namen des Anbieters an dieser Position ausgeben.

Angenommen, es gibt vier Anbieter, deren Namen Sie in die Zellen C1:F1 als Spaltenüberschriften eingegeben haben. Darunter stehen die jeweiligen Preise: in Spalte C die Preise des ersten Anbieters, in Spalte D die Preise des zweiten Anbieters und so weiter. Nun soll in Spalte B bei jedem Artikel der Name des günstigsten Anbieters erscheinen (Abbildung 1). Dazu geben Sie in Zelle B2 (in der Zeile des ersten Artikels) folgende Formel ein:

=INDEX($C$1:$F$1;1;VERGLEICH(MIN(C2:F2);C2:F2;0))
Abbildung 1: Die günstigsten Anbieter ermittelt Calc mit einer verschachtelten "INDEX()"-Formel. Auf eine Auswahlmöglichkeit wird mit einer bedingten Formatierung hingewiesen.

Die Formel kopieren Sie nach unten in alle Artikelzeilen, um überall sofort den günstigsten Anbieter erkennen zu können.

Calc sucht in den Preisangeboten für einen einzelnen Artikel (C2:F2) nach dem Minimalwert und liefert über die VERGLEICH()-Funktion die entsprechende Position: Wenn der erste Anbieter den günstigsten Preis hat, ist die Position "1", beim zweiten Anbieter "2" usw.

Die INDEX()-Funktion verwendet die gefundene Position als Spaltenindex, um den entsprechenden Wert aus dem festen (absoluten) Bereich $C$1:$F$1 zurückzugeben. Das ist der Bereich der Spaltenüberschriften mit den Namen der Anbieter. Falls der günstigste Preis von mehreren Unternehmen angeboten wird, liefert die Formel allerdings immer nur den ersten Firmennamen, das heißt, den Namen der Firma aus der am weitesten links stehenden Spalte. Dieses Manko können Sie mit einer bedingten Formatierung umgehen, das die Formelzelle hervorhebt, für die es Preisalternativen gibt (Abbildung 2). Die bedingte Formatierung muss von einer Formel abhängig sein, in der Sie prüfen, ob es mehr als einen Minimalwert gibt. Die Formel der bedingten Formatierung für das oben verwendete Beispiel müsste wie folgt lauten:

ZÄHLENWENN(C2:F2;MIN(C2:F2))>1
Abbildung 2: Dank einer bedingten Formatierung kann Calc auf mehrere Treffer bei der Suche nach dem günstigsten Anbieter hinweisen.

Tipp: Calc: Alle Treffer für ein Suchkriterium zeigen

Die Verweisfunktionen von Calc sind zwar leistungsfähig und vielseitig einsetzbar, liefern aber nicht immer direkt die gewünschten Ergebnisse. Nehmen wir beispielsweise an, dass Sie ein Tabellenblatt für Zahlungseingänge angelegt haben. Darin notieren Sie unter anderem die Kundennamen und die bezahlten Beträge. Daneben sollen nun die Zahlungen eines bestimmten Kunden ausgewertet werden. Wenn Sie mit der Funktion SVERWEIS() in der Tabelle mit den Zahlungseingängen nach einem Kundennamen suchen, findet Calc immer nur die erste Zahlung – bei mehrfachem Vorkommen des Kundennamens können Sie nie die Datensätze für die zweite, dritte usw. Zahlung ausfindig machen.

Mit SVERWEIS() kommen Sie hier nicht weiter. Sie können aber mit einer Kombination aus den Funktionen INDEX() und KKLEINSTE() weitere Vorkommen eines Suchkriteriums in einer Liste finden.

Ein Beispiel: Angenommen, Sie haben die Zahlungseingänge in einer Tabelle mit folgendem Aufbau erfasst: Spalte A enthält die Kundennamen, Spalte B die eingegangenen Beträge und Spalte C das Eingangsdatum. Für die nun vorgestellte Formel ist es vorteilhaft, alle relevanten Bereiche per Einfügen / Namen / Festlegen mit Namen zu versehen:

  • Weisen Sie dem gesamten Bereich von der ersten Spaltenüberschrift in Spalte A bis zum letzten Zahlungsdatum in Spalte C den Namen Tabellenwerte zu.
  • Dem Spaltenbereich, in dem Sie nach einem Kriterium suchen wollen, geben Sie den Namen Suchspalte.

Im Beispiel müssten Sie also den Bereich von der ersten Spaltenüberschrift in Spalte A bis zum letzten Datensatz in Spalte A als Suchspalte benennen. Wichtig ist, dass der Bereich genauso hoch ist wie der mit Tabellenwerte benannte Bereich.

Gehen Sie dann zu dem Tabellenbereich, in dem Sie die Auswertungen vornehmen wollen, und weisen Sie der Zelle für das Suchkriterium (Name des Kunden), in diesem Beispiel der Zelle E3, den Namen Kriterium zu. Nun können Sie in einer Zelle neben dem Suchkriterium die Formeln eingeben, mit denen Sie die verschiedenen Zahlungsbeträge zur gesuchten Kundennummer ermitteln.

Den ersten Zahlungsbetrag, hier in der Zelle G2 (Abbildung 3), erhalten Sie mit folgender Formel:

=WENN(ZEILE(A1)<=ZÄHLENWENN(Suchspalte;Kriterium);INDEX(Tabellenwerte;KKLEINSTE(WENN(Suchspalte=Kriterium;ZEILE(INDIREKT("A$1:A$"&ZEILEN(Suchspalte))));ZEILE(A1));2);"")
Abbildung 3: Mit einer kombinierten Verweisfunktion können Sie alle Zahlungen eines Kunden aus einer Liste herausfiltern und aufsummieren.

Schließen Sie die Formeleingabe mit [Strg]+[Umschalt]+[Eingabe] ab, damit Calc die Formel als Matrixformel einträgt. In der Bearbeitungszeile wird die Formel dann automatisch mit geschweiften Klammern umgeben. Danach kopieren Sie die Formel in die darunter liegenden Zellen, um die weiteren Zahlungseingänge ausgeben zu lassen.

Die Formel beginnt mit einer WENN()-Prüfung, mit der sie prüft, ob es überhaupt eine Übereinstimmung mit dem Suchkriterium gibt. Dabei entspricht ZEILE(A1) dem aktuellen Zähler, in der ersten Formelzelle also dem Wert 1. Durch das Kopieren der Formel wird daraus in der nächsten Formelzelle ZEILE(A2), dann ZEILE(A3) usw., wodurch sich die Werte 2, 3 usw. ergeben. Die ZÄHLENWENN()-Funktion gibt Auskunft, wie oft das Suchkriterium in der Suchspalte vorkommt. Nur wenn der Zähler kleiner oder gleich dieser Anzahl ist, fährt die WENN()-Funktion mit der INDEX()-Funktion fort. Ansonsten gibt sie eine leere Zeichenfolge aus.

Die INDEX()-Funktion sucht eine bestimmte Position innerhalb des Bereichs Tabellenwerte und gibt in obiger Formel den Wert aus der zweiten Spalte zurück:

INDEX(Tabellenwerte;[...];2)

In Ihren Tabellen ändern Sie am Ende der Formel den festen Wert 2 dahingehend ändern, dass er auf die für Ihre Situation passende Spalte im Bereich Tabellenwerte verweist. Soll also, wie im Beispiel, in einer weiteren Spalte das zu den ausgewiesenen Beträgen zutreffende Einzahlungsdatum ausgewiesen werden, können Sie die gleiche Formel verwenden, ersetzen aber den Wert 2 durch 3, weil die Datumswerte in der dritten Spalte stehen.

Die Position für die INDEX()-Funktion liefert die Funktion KKLEINSTE(). Sie soll jeweils den x-kleinsten Wert aus einer fortlaufenden Zahlenfolge von 1 bis zur Anzahl der Zeilen im Bereich Suchspalte liefern. Diese Zahlenfolge erhalten Sie mit folgendem Teil der Formel:

ZEILE(INDIREKT("A$1:A$"&ZEILEN(Suchspalte)))

Die Zahlenfolge entspricht den Zeilennummern im Bereich der Tabellenwerte. Dabei interessieren aber nur die Nummern der Zeilen, in denen das Suchkriterium gefunden wird. Darum kümmert sich die WENN-Funktion

WENN(Suchspalte=Kriterium;[...])

die nur die relevanten Zeilennummern zurückgibt und alle anderen Positionen auf den Wert Falsch setzt. Da dies der Zahl 0 entspricht, kann KKLEINSTE() jeweils die x-kleinste Zeilennummer mit dem Suchkriterium ermitteln. Der wievielte kleinste Wert es sein soll, ergibt sich durch ZEILE(A1). Diese Funktion dient wie oben als Zähler, der durch das Kopieren der Formel die Werte 1, 2, 3 usw. annimmt.

Damit ermittelt Calc die Position für die INDEX()-Funktion, die dann den passenden Spaltenwert zurückgibt.

Diesen Artikel als PDF kaufen

Express-Kauf als PDF

Umfang: 5 Heftseiten

Preis € 0,99
(inkl. 19% MwSt.)

LinuxCommunity kaufen

Einzelne Ausgabe
 
Abonnements
 
TABLET & SMARTPHONE APPS
Bald erhältlich
Get it on Google Play

Deutschland

Ähnliche Artikel

  • LibreOffice-Tipps
    In dieser Ausgabe der LibreOffice-Tipps erfahren Sie, wie Sie Fließtext mit Bildern hinterlegen, Zahlenausgaben auf einen bestimmten Bereich reduzieren und Zellen automatisch numerieren.
  • OpenOffice-Tipps
  • LibreOffice-Tipps
    Wir zeigen diesmal in den LibreOffice-Tipps, wie Sie die Durchschnittsnote eines Schulfaches errechnen, Tabellentext automatisch formatieren, Sonderzeichen und Umlaute ersetzen und einiges mehr.
  • Besser arbeiten mit LibreOffice
    Die LibreOffice-Tipps dieser Ausgabe verraten unter anderem, wie Sie die Häufigkeit von Suchbegriffen in einer Tabelle ermitteln, bei doppelten Einträgen in einer Spalte eine Warnung erzeugen und in Writer automatische Textersetzungen in die Schranken weisen.
  • LibreOffice-Tipps
    In den LibreOffice-Tipps erfahren Sie diesmal, wie Sie einen unglaublich hohen Stundenlohn erzielen, Arbeitszeiten runden und eine Formel bequem in viele Zellen kopieren.
Kommentare

Infos zur Publikation

EL 08-10/2017: BACKUP

Digitale Ausgabe: Preis € 9,80
(inkl. 19% MwSt.)

EasyLinux erscheint vierteljährlich und kostet 9,80 Euro. Weitere Infos zum Heft finden Sie auf der Homepage.

Das Jahresabo kostet ab 33,30 Euro. Details dazu finden Sie im Computec-Shop.

Bei Google Play finden Sie digitale Ausgaben für Tablet & Smartphone.

HINWEIS ZU PAYPAL: Die Zahlung ist ohne Paypal-Konto ganz einfach per Kreditkarte oder Lastschrift möglich!      

Stellenmarkt

Aktuelle Fragen

Lieber Linux oder Windows- Betriebssystem?
Sina Kaul, 13.10.2017 16:17, 2 Antworten
Hallo, bis jetzt hatte ich immer nur mit
IT-Kurse
Alice Trader, 26.09.2017 11:35, 2 Antworten
Hallo liebe Community, ich brauche Hilfe und bin sehr verzweifelt. Ih bin noch sehr neu in eure...
Backup mit KUP unter Suse 42.3
Horst Schwarz, 24.09.2017 13:16, 3 Antworten
Ich möchte auch wieder unter Suse 42.3 mit Kup meine Backup durchführen. Eine Installationsmöglic...
kein foto, etc. upload möglich, wo liegt mein fehler?
kerstin brums, 17.09.2017 22:08, 5 Antworten
moin, zum erstellen einer einfachen wordpress website kann ich keine fotos uploaden. vom rechne...
Arch Linux Netzwerkkonfigurationen
Franziska Schley, 15.09.2017 18:04, 0 Antworten
Moin liebe Linux community, ich habe momentan Probleme mit der Einstellung des Lan/Wlan in Arc...