Excel-Formeln und -Funktionen: Lernen Sie mit einfachen BEISPIELEN

Formeln und Funktionen sind die Bausteine ​​für die Arbeit mit numerischen Daten in Excel. Dieser Artikel führt Sie in Formeln und Funktionen ein.

Tutorial-Daten

Für dieses Tutorial werden wir mit Folgendem arbeitenwing Datensätze.

Budget für Haushaltswaren

S / N ITEM ANZAHL PREIS Zwischensumme Ist es erschwinglich?
1 Mangos 9 600
2 Orangen 3 1200
3 Tomaten 1 2500
4 Speiseöl 5 6500
5 Tonic Wasser 13 3900

Zeitplan für Hausbauprojekte

S / N ITEM ANFANGSDATUM ENDDATUM DAUER (TAGE)
1 Vermessungsland 04/02/2015 07/02/2015
2 Laienstiftung 10/02/2015 15/02/2015
3 Dachdecken 27/02/2015 03/03/2015
4 Spritzspachtel auftragen 09/03/2015 21/03/2015

Was sind Formeln in Excel?

FORMELN IN EXCEL ist ein Ausdruck, der mit Werten in einem Bereich von Zelladressen und Operatoren arbeitet. Beispiel: =A1+A2+A3, wodurch die Summe des Wertebereichs von Zelle A1 bis Zelle A3 ermittelt wird. Ein Beispiel für eine Formel bestehend aus diskreten Werten wie =6*3.

=A2 * D2 / 2

HIER,

  • "=" teilt Excel mit, dass es sich um eine Formel handelt und diese auswerten soll.
  • "A2" * D2" verweist auf die Zelladressen A2 und D2 und multipliziert dann die in diesen Zelladressen gefundenen Werte.
  • "/" ist der arithmetische Divisionsoperator
  • "2" ist ein diskreter Wert

Formeln praktische Übung

Zur Berechnung der Zwischensumme verwenden wir die Beispieldaten für das Hausbudget.

  • Erstellen Sie eine neue Arbeitsmappe in Excel
  • Geben Sie die im Haushaltsbedarfsbudget oben aufgeführten Daten ein.
  • Ihr Arbeitsblatt sollte wie folgt aussehen.

Praktische Übung zu Formeln

Wir schreiben nun die Formel, die die Zwischensumme berechnet

Setzen Sie den Fokus auf Zelle E4

Geben Sie Folgendes einwing Formel.

=C4*D4

HIER,

  • "C4*D4" verwendet den arithmetischen Operator Multiplikation (*), um den Wert der Zelladressen C4 und D4 zu multiplizieren.

Drücken Sie die Eingabetaste

Sie erhalten Folgendeswing Folge

Praktische Übung zu Formeln

Die folgendenwing Das animierte Bild zeigt Ihnen, wie Sie die Zellenadresse automatisch auswählen und dieselbe Formel auf andere Zeilen anwenden.

Praktische Übung zu Formeln

Zu vermeidende Fehler beim Arbeiten mit Formeln in Excel

  1. Denken Sie an die Regeln von Brackets Division, Multiplikation, Addition und Subtraktion (BODMAS). Das heißt, Ausdrücke sind brackets werden zunächst ausgewertet. Bei arithmetischen Operatoren wird zuerst die Division ausgewertet, gefolgt von der Multiplikation, dann wird die Addition und Subtraktion als letztes ausgewertet. Mit dieser Regel können wir die obige Formel als =(A2 * D2) / 2 umschreiben. Dadurch wird sichergestellt, dass A2 und D2 zuerst ausgewertet und dann durch zwei geteilt werden.
  2. Excel-Tabellenformeln funktionieren normalerweise mit numerischen Daten; Sie können die Datenvalidierung nutzen, um den Datentyp anzugeben, der von einer Zelle akzeptiert werden soll, z. B. nur Zahlen.
  3. Um sicherzustellen, dass Sie mit den richtigen Zelladressen arbeiten, auf die in den Formeln verwiesen wird, können Sie F2 auf der Tastatur drücken. Dadurch werden die in der Formel verwendeten Zelladressen hervorgehoben, und Sie können überprüfen, ob es sich um die gewünschten Zelladressen handelt.
  4. Wenn Sie mit vielen Zeilen arbeiten, können Sie für alle Zeilen fortlaufende Nummern verwenden und am unteren Rand des Blattes eine Datensatzanzahl angeben. Sie sollten die Anzahl der Seriennummern mit der Gesamtzahl der Datensätze vergleichen, um sicherzustellen, dass Ihre Formeln alle Zeilen enthalten.

Auschecken
Die 10 besten Excel-Tabellenformeln

Was ist eine Funktion in Excel?

FUNKTION IN EXCEL ist eine vordefinierte Formel, die für bestimmte Werte in einer bestimmten Reihenfolge verwendet wird. Die Funktion wird für schnelle Aufgaben wie das Ermitteln der Summe, Anzahl, des Durchschnitts, des Maximalwerts und des Minimalwerts für einen Zellbereich verwendet. Zelle A3 unten enthält beispielsweise die SUM-Funktion, die die Summe des Bereichs A1:A2 berechnet.

  • SUM zur Summierung einer Reihe von Zahlen
  • DURCHSCHNITTLICH zur Berechnung des Durchschnitts eines bestimmten Zahlenbereichs
  • ANZAHL zum Zählen der Anzahl der Elemente in einem bestimmten Bereich

Die Bedeutung von Funktionen

Funktionen erhöhen die Benutzerproduktivität bei der Arbeit mit Excel. Nehmen wir an, Sie möchten die Gesamtsumme für das oben genannte Budget für Haushaltswaren erhalten. Zur Vereinfachung können Sie eine Formel verwenden, um die Gesamtsumme zu ermitteln. Mithilfe einer Formel müssten Sie die Zellen E4 bis E8 nacheinander referenzieren. Sie müssten Folgendes verwendenwing Formel.

= E4 + E5 + E6 + E7 + E8

Mit einer Funktion würden Sie die obige Formel wie folgt schreiben:

=SUM (E4:E8)

Wie Sie anhand der obigen Funktion zum Ermitteln der Summe eines Zellbereichs sehen können, ist es viel effizienter, eine Funktion zum Ermitteln der Summe zu verwenden, als die Formel zu verwenden, die auf viele Zellen verweisen muss.

Gemeinsame Funktionen

Schauen wir uns einige der am häufigsten verwendeten Funktionen in MS-Excel-Formeln an. Wir beginnen mit statistischen Funktionen.

S / N AUFGABE KATEGORIE BESCHREIBUNG ANWENDUNG
01 SUM Math & Trig Fügt alle Werte in einem Zellbereich hinzu = SUMME (E4: E8)
02 MIN Statistisch Ermittelt den Mindestwert in einem Zellbereich =MIN(E4:E8)
03 MAX Statistisch Ermittelt den Maximalwert in einem Zellbereich =MAX(E4:E8)
04 DURCHSCHNITTLICH Statistisch Berechnet den Durchschnittswert in einem Zellbereich = DURCHSCHNITTLICH (E4: E8)
05 ANZAHL Statistisch Zählt die Anzahl der Zellen in einem Zellbereich = COUNT (E4: E8)
06 LEN Text Gibt die Anzahl der Zeichen in einem Zeichenfolgentext zurück = LEN (B7)
07 SUMME Math & Trig Fügt alle Werte in einem Zellbereich hinzu, die einem angegebenen Kriterium entsprechen.
=SUMIF(Bereich,Kriterien,[Summenbereich])
=SUMIF(D4:D8,”>=1000″,C4:C8)
08 AVERAGEIF Statistisch Berechnet den Durchschnittswert in einem Bereich von Zellen, die die angegebenen Kriterien erfüllen.
= AVERAGEIF (Bereich, Kriterien, [durchschnittlicher Bereich])
=MITTELWENN(F4:F8,“Ja“,E4:E8)
09 TAGE Datum & Zeit Gibt die Anzahl der Tage zwischen zwei Daten zurück =TAGE(D4,C4)
10 JETZT Datum & Zeit Gibt das aktuelle Systemdatum und die aktuelle Systemzeit zurück = JETZT ()

Numerische Funktionen

Wie der Name schon sagt, verarbeiten diese Funktionen numerische Daten. Die folgendenwing Die Tabelle zeigt einige der häufigsten numerischen Funktionen.

S / N AUFGABE KATEGORIE BESCHREIBUNG ANWENDUNG
1 ISNUMBER Informationen Gibt True zurück, wenn der angegebene Wert numerisch ist, und False, wenn er nicht numerisch ist =ISNUMMER(A3)
2 RAND Math & Trig Erzeugt eine Zufallszahl zwischen 0 und 1 = RAND ()
3 ROUND Math & Trig Rundet einen Dezimalwert auf die angegebene Anzahl von Dezimalstellen ab = RUNDE (3.14455,2)
4 MEDIAN Statistisch Gibt die Zahl in der Mitte der Menge der angegebenen Zahlen zurück =MEDIAN(3,4,5,2,5)
5 PI Math & Trig Gibt den Wert der mathematischen Funktion PI(π) zurück. =PI()
6 MagXNUMX Math & Trig Gibt das Ergebnis einer potenzierten Zahl zurück.
POWER( Zahl, Leistung )
=LEISTUNG(2,4)
7 MOD Math & Trig Gibt den Rest zurück, wenn Sie zwei Zahlen dividieren =MOD(10,3)
8 ROMAN Math & Trig Wandelt eine Zahl in römische Ziffern um =RÖMISCHE(1984)

String-Funktionen

Diese grundlegenden Excel-Funktionen werden zum Bearbeiten von Textdaten verwendet. Die folgendenwing Die Tabelle zeigt einige der häufigsten String-Funktionen.

S / N AUFGABE KATEGORIE BESCHREIBUNG ANWENDUNG WIE
1 LINKS Text Gibt eine Anzahl angegebener Zeichen vom Anfang (linker Seite) einer Zeichenfolge zurück =LINKS(“GURU99”,4) 4 Zeichen von „GURU99“ übrig
2 RECHTS Text Gibt eine Anzahl angegebener Zeichen vom Ende (rechts) einer Zeichenfolge zurück =RECHTS(“GURU99”,2) Rechts 2 Zeichen von „GURU99“
3 MID Text Ruft eine Anzahl von Zeichen aus der Mitte einer Zeichenfolge ab einer angegebenen Startposition und Länge ab.
=MID (text, start_num, num_chars)
=MID(“GURU99”,2,3) Abrufen der Zeichen 2 bis 5
4 ISTEXT Informationen Gibt True zurück, wenn der angegebene Parameter Text ist =ISTEXT(Wert) value – Der zu überprüfende Wert.
5 FINDEN Text Gibt die Startposition einer Textzeichenfolge innerhalb einer anderen Textzeichenfolge zurück. Bei dieser Funktion wird die Groß-/Kleinschreibung beachtet.
=FIND(find_text, Within_text, [start_num])
=FIND(“oo”,“Roofing”,1) Finden Sie oo in „Dächer“, Ergebnis ist 2
6 REPLACE Text Ersetzt einen Teil einer Zeichenfolge durch eine andere angegebene Zeichenfolge.
=REPLACE (old_text, start_num, num_chars, new_text)
=REPLACE(„Dach“,2,2,xx“) Ersetzen Sie „oo“ durch „xx“

Datum-Uhrzeit-Funktionen

Diese Funktionen werden zum Bearbeiten von Datumswerten verwendet. Die folgendenwing Die Tabelle zeigt einige der gebräuchlichen Datumsfunktionen

S / N AUFGABE KATEGORIE BESCHREIBUNG ANWENDUNG
1 DATUM Datum & Zeit Gibt die Zahl zurück, die das Datum im Excel-Code darstellt = DATUM (2015,2,4)
2 TAGE Datum & Zeit Ermitteln Sie die Anzahl der Tage zwischen zwei Daten =TAGE(D6,C6)
3 MONAT Datum & Zeit Gibt den Monat aus einem Datumswert zurück =MONAT(“4”)
4 MINUTE Datum & Zeit Gibt die Minuten aus einem Zeitwert zurück =MINUTE(“12:31”)
5 JAHR Datum & Zeit Gibt das Jahr aus einem Datumswert zurück =JAHR(“04”)

VLOOKUP-Funktion

Der VLOOKUP-Funktion wird verwendet, um eine vertikale Suche in der Spalte ganz links durchzuführen und einen Wert in derselben Zeile aus einer von Ihnen angegebenen Spalte zurückzugeben. Lassen Sie uns das in einer Laiensprache erklären. Das Haushaltsbedarfsbudget verfügt über eine Seriennummernspalte, die jeden Posten im Budget eindeutig identifiziert. Angenommen, Sie haben die Seriennummer des Artikels und möchten die Artikelbeschreibung wissen, dann können Sie die VLOOKUP-Funktion verwenden. So würde die SVERWEIS-Funktion funktionieren.

VLOOKUP-Funktion

=VLOOKUP (C12, A4:B8, 2, FALSE)

HIER,

  • "=VLOOKUP" ruft die vertikale Suchfunktion auf
  • "C12" Gibt den Wert an, der in der Spalte ganz links gesucht werden soll
  • "A4:B8" Gibt das Tabellenarray mit den Daten an
  • "2" Gibt die Spaltennummer mit dem Zeilenwert an, der von der VLOOKUP-Funktion zurückgegeben werden soll
  • "FALSE," teilt der VLOOKUP-Funktion mit, dass wir nach einer genauen Übereinstimmung mit dem angegebenen Suchwert suchen

Das animierte Bild unten zeigt dies in Aktion

VLOOKUP-Funktion

Laden Sie den obigen Excel-Code herunter

Zusammenfassung

Mit Excel können Sie die Daten mithilfe von Formeln und/oder Funktionen bearbeiten. Funktionen sind im Allgemeinen produktiver als das Schreiben von Formeln. Funktionen sind im Vergleich zu Formeln auch genauer, da die Fehlerquote sehr gering ist.

Hier ist eine Liste wichtiger Excel-Formeln und -Funktionen

  • SUMME-Funktion = =SUM(E4:E8)
  • MIN-Funktion = =MIN(E4:E8)
  • MAX-Funktion = =MAX(E4:E8)
  • AVERAGE-Funktion = =AVERAGE(E4:E8)
  • COUNT-Funktion = =COUNT(E4:E8)
  • TAGE-Funktion = =DAYS(D4,C4)
  • SVERWEIS-Funktion = =VLOOKUP (C12, A4:B8, 2, FALSE)
  • DATE-Funktion = =DATE(2020,2,4)