Speisekarte
heimSonstig

Excel ermittelt den Mindestwert mit der Bedingung. Maximum und Minimum nach Bedingung in MS EXCEL. So funktioniert die Auswahl nach Bedingung

SONDERFÄLLE

Im Einzelfall kann die Lösung durch Aufzählung aller möglichen Kombinationen ermittelt werden.

Programm (C#):

Öffentliches statisches int FactGen(int n) ( int fact = new int; fact = 1; for (int i = 1; i< n + 1; i++) fact[i] = i * fact; return fact; } public static int PermsGenerator(int arr, int num, int fact) { int i, j, f, newind, newnum, newval, size = arr.Length; int result = new int; Array.Copy(arr, result, size); for (i = 0, newnum = num; i < size - 1; i++) { newind = newnum / (f = fact); newnum = newnum - newind * f; newval = result; for (j = i + newind; j >ich; j--) result[j] = result; result[i] = newval; ) Ergebnis zurückgeben; ) public static int SumAbs(int arr) ( int sum = 0, prev = arr; foreach (int value in arr) ( sum += Math.Abs(value - prev); prev = value; ) return sum; ) public static int MaxArr(int arr, bool detprn) ( int i, j, sum, smax = -1, size = arr.Length; int perm, result = new int, fact = FactGen(size), sorted = new int, Empire = new int;Array.Copy(arr, sorted, size);Array.Sort(sorted);for (i = 0; i< fact; i++) { if ((sum = SumAbs(perm = PermsGenerator(sorted, i, fact))) >smax) ( smax = sum; result = perm; ) if (detprn) ( Console.Write("\nPermutation: "); foreach (int value in perm) Console.Write(value + " "); Console.Write(" Summe: (0) Max. Summe: (1)", sum, smax); ) ) Console.Write("\nAnfangsauswahl: "); foreach (int value in arr) Console.Write(value + " "); Console.Write("\nSortierte Auswahl: "); foreach (int value in sorted) Console.Write(value + " "); Console.Write("\nBeste Permutation: "); foreach (int value in result) Console.Write(value + " "); Console.WriteLine("\nMaximaler Betrag: (0)", smax); Empire=sortiert; Empire=sortiert; Empire=sortiert; für (i = 2, j = 0; i< size - 1; i++, j = (size) - 1 + ((i + 1) & 1) - j) empire[i] = sorted[j]; Console.Write("Эвристический алгоритм: "); foreach (int value in empire) Console.Write(value + " "); Console.WriteLine("\nСумма: {0}", SumAbs(empire)); return result; } static void Main(string args) { bool binc; int i, j, n, nmax = 10; int arr; Random rand = new Random(); MaxArr(new int { 1, 2, 3, 4 }, false); MaxArr(new int { 1, 2, 3, 4, 5 }, false); for (n = 6; n < nmax + 1; n++) { arr = new int[n]; for (i = 0; i < n;) { arr[i] = rand.Next(1, 5 * n); binc = true; for (j = 0; j < i; j++) binc &= (arr[i] != arr[j]); if (binc) i++; } MaxArr(arr, false); } }

Ergebnisse:

Anfangsstichprobe: 1 2 3 4 Sortierte Stichprobe: 1 2 3 4 Beste Permutation: 2 4 1 3 Maximale Summe: 7 Heuristik: 2 4 1 3 Summe: 7 Anfangsstichprobe: 1 2 3 4 5 Sortierte Stichprobe: 1 2 3 4 5 Beste Permutation: 2 4 1 5 3 Maximale Summe: 11 Heuristischer Algorithmus: 2 4 1 5 3 Summe: 11 Originalstichprobe: 26 10 21 4 27 5 Sortierte Stichprobe: 4 5 10 21 26 27 Beste Permutation: 10 26 4 27 5 21 Maximale Summe: 99 Heuristik: 10 26 4 27 5 21 Summe: 99 Originalstichprobe: 34 3 32 16 28 27 26 Sortierte Stichprobe: 3 16 26 27 28 32 34 Beste Permutation: 26 28 3 32 16 34 27 Maximale Summe: 97 Heuristik : 26 28 3 34 16 32 27 Summe: 97 Originalstichprobe: 27 3 34 38 18 29 31 39 Sortierte Stichprobe: 3 18 27 29 31 34 38 39 Beste Permutation: 29 34 3 38 18 39 27 31 Maximale Summe: 128 Heuristik: 29 34 3 39 18 38 27 31 Summe: 128 Originalstichprobe: 40 27 4 9 32 35 41 39 2 Sortierte Stichprobe: 2 4 9 27 32 35 39 40 41 Beste Permutation: 32 2 39 4 40 9 41 27 35 Maximale Summe: 223 Heuristik: 27 35 2 41 4 40 9 39 32 Summe: 221 Anfangsstichprobe: 41 35 45 27 34 33 18 24 16 25 Sortierte Stichprobe: 16 18 24 25 27 33 34 35 41 45 Beste Permutation: 27 34 16 3 5 18 41 24 45 25 33 Maximale Summe: 150 Heuristik: 27 34 16 45 18 41 24 35 25 33 Summe: 150

Auf diese Weise:

  1. Für das ursprüngliche Array wurde eine bessere Kombination gefunden.
  2. Ein Gegenbeispiel für den heuristischen Algorithmus wurde nicht sofort gefunden.

ALLGEMEINE ENTSCHEIDUNG (10.02.2018)

Lassen
a = (a 0 = M 1 , a 1 = M 2 , …, a n-2 = M n-1 , a n-1 = M n ) - Anfangssequenz,
b = (b 0 , b 1 , …, b n-2 , b n-1 ) – die gleiche Sequenz in aufsteigender Reihenfolge,
с = (c 0 , c 1 , …, c n-2 , c n-1 ) – erforderliche Sequenz.

Betrachten Sie getrennt die Fälle von geradem und ungeradem n.

Fall n = 2k

S(a) = S c (a) - |a n-1 - a 0 |, wobei
S c a) = |a 0 - a 1 | + |a 1 - a 2 | + … + |a n-3 - a n-2 | + |a n-2 - a n-1 | + |a n-1 - a 0 |.

S c (a) ist eine algebraische Summe, die jedes ursprüngliche Element a i zweimal enthält und deren Maximalwert gleich ist

S c_max = 2∑ i = 0, …, k-1 (h i – b i), wobei h i = b i+k , i = 0…k-1.

Dieser Wert wird in zwei Permutationsvarianten erreicht:
1) c 2i h (alle größten Elemente haben gerade Indizes);
2) c 2i + 1 h (alle größten Elemente haben ungerade Indizes).

Gleichzeitig ist das Minimum |a n-1 - a 0 | = b k - b k-1 wird erreicht, indem ein Paar Medianelemente an den Rändern der Sequenz c platziert wird.

Die maximale Summe S ist gleich S max = 2∑ i = 0, …, k-2 (b i+k - b i) + b k-1 - b k , oder

S max = 2∑ i = 0, …, k-2 (b n-1-i - b i) + b n-k - b k-1 ,

und wird in den Fällen erreicht, in denen die Folge c die größten Elemente in einem Schachbrettmuster enthält und die mittleren Elemente b k-1 und b k an den Rändern der Folge liegen.
Die Anzahl solcher Permutationen für paarweise verschiedene a i beträgt 2(k-1)! 2.

Fall n = 2k+1

Eine ähnliche Überlegung zeigt, dass das Maximum von S gleich ist

S max = 2∑ i=0…k-2 (b k+2+i - b k) + b k+1 - b k-1 + max (b k+1 - b k , b k - b k-1), oder

S max = 2∑ i=0…k-2 (b n-1-i – b i) + b n-k – b k-1 + max (b k+1 – b k , b k – b k-1),

und wird in den Fällen erreicht, in denen Elemente mit Indizes größer als k gestaffelt sind und das Medianelement und das ihm am nächsten liegende Element an den Rändern der Sequenz liegen.
Die Anzahl solcher Permutationen für paarweise verschiedene a i ist nicht kleiner als (k-1)!k! (Wenn sich der Median um den gleichen Wert von den nächsten Nachbarn unterscheidet, gibt es doppelt so viele Permutationen).

PROGRAMM (C#):

Public static void T(string text, Stopwatch timer) ( TimeSpan ts = timer.Elapsed; string elapsedTime = String.Format("(0:00):(1:00):(2:00).(3:00) ", ts.Hours, ts.Minutes, ts.Seconds, ts.Milliseconds / 10); Console.Write(text + elapsedTime); ) public static int FactGen(int n) ( int fact = new int; fact = 1; für (int i = 1; i< n + 1; i++) fact[i] = i * fact; return fact; } public static int PermsGenerator(int arr, int num, int fact) { int i, j, f, newind, newnum, newval, size = arr.Length; int result = new int; Array.Copy(arr, result, size); for (i = 0, newnum = num; i < size - 1; i++) { f = fact; newind = i; while (newnum >= f) ( newind++; newnum -= f; ) newval = result; for (j = newind; j > i; j--) result[j] = result; result[i] = newval; ) Ergebnis zurückgeben; ) public static int SumAbs(int arr) ( int sum = 0, prev = arr; foreach (int value in arr) ( sum += Math.Abs(value - prev); prev = value; ) return sum; ) public static int CalcOptQuant (int n) ( int k = (n - 1) / 2, k2 = Math.Max(k-2, 0); int fact = FactGen(k + 2); return 2*fact[k] * ( (n-k-k< 2) ? fact : fact[k]); } public static int CalcMaxSum(int brr) { int i, size = brr.Length, k = size / 2, sum = 0; for (i = 0; i < k - 1; i++) sum += brr - brr[i]; sum = 2 * sum + brr - brr; if (size - 2 * k >0) sum += Math.Max(brr - brr[k], brr[k] - brr); Rückgabesumme; ) public static int MaxArr(int arr, bool detprn) ( int i, j, sum, smax = -1, size = arr.Length; int perm, result = new int, fact = FactGen(size), sorted = new int , Empire = new int; Stopwatch sw = new Stopwatch(); Console.WriteLine("\nInitial Selection: "); foreach (int value in arr) Console.Write(value + " "); sw.Restart(); for (i = 0; ich< fact; i++) { perm = PermsGenerator(arr, i, fact); sum = SumAbs(perm); if (sum >smax) ( smax = sum; result = perm; ) ) if (detprn) Console.Write("\nBest permutations"); für (i = 0, j = 0; i< fact; i++) { perm = PermsGenerator(arr, i, fact); sum = SumAbs(perm); if (sum == smax) { j++; if (detprn) { Console.Write("\n#{0}: ", j); foreach (int value in perm) Console.Write(value + " "); } } } Array.Copy(arr, sorted, size); Array.Sort(sorted); Console.WriteLine("\nОтсортированная выборка: "); foreach (int value in sorted) Console.Write(value + " "); Console.Write("\nФакт Наибольшая сумма: {0} Лучших перестановок: {1}" + "\nРасчёт Наибольшая сумма: {2} Лучших перестановок, не менее: {3}", smax, j, CalcMaxSum(sorted), CalcOptQuant(size)); T("\nRuntime = ", sw); sw.Reset(); return result; } static void Main(string args) { bool binc; int i, j, n, nmax = 12; int arr = new int; Random rand = new Random(); MaxArr(new int { 0, 1, 2, 3, 4, 5 }, true); MaxArr(new int { 5, 1, 3, 5, 7, 9 }, true); MaxArr(new int { 0, 1, 2, 3, 4 }, true); MaxArr(new int { 0, 1, 2, 3 }, true); MaxArr(new int { 0, 1, 2 }, true); for (n = 3; n <= nmax; n++) { Array.Resize(ref arr, n); for (i = 0; i < n;) { arr[i] = rand.Next(10, 99); binc = true; for (j = 0; j < i; j++) binc &= (arr[i] != arr[j]); if (binc) i++; } MaxArr(arr, false); } }

ERGEBNISSE:

Ausgangsstichprobe: 0 1 2 3 4 5 Beste Permutationen #1: 2 4 0 5 1 3 #2: 2 4 1 5 0 3 #3: 2 5 0 4 1 3 #4: 2 5 1 4 0 3 #5: 3 0 4 1 5 2 #6: 3 0 5 1 4 2 #7: 3 1 4 0 5 2 #8: 3 1 5 0 4 2 Sortierte Stichprobe: 0 1 2 3 4 5 Fakt Höchste Summe: 17 Beste Permutationen: 8 Berechnung Größte Summe: 17 Beste Permutationen, min: 8 Laufzeit = 00:00:00,01 Anfangsstichprobe: 5 1 3 5 7 9 Beste Permutationen #1: 5 1 7 3 9 5 #2: 5 1 9 3 7 5 # 3 : 5 3 7 1 9 5 #4: 5 3 9 1 7 5 #5: 5 7 1 9 3 5 #6: 5 7 3 9 1 5 #7: 5 9 1 7 3 5 #8: 5 9 3 7 1 5 #9: 5 1 7 3 9 5 #10: 5 1 9 3 7 5 #11: 5 3 7 1 9 5 #12: 5 3 9 1 7 5 #13: 5 7 1 9 3 5 #14 : 5 7 3 9 1 5 #15: 5 9 1 7 3 5 #16: 5 9 3 7 1 5 Sortierte Stichprobe: 1 3 5 5 7 9 Fakt Höchste Summe: 24 Beste Permutationen: 16 Berechnung Höchste Summe: 24 Beste Permutationen, mindestens: 8 Laufzeit = 00:00:00,01 Anfangsstichprobe: 0 1 2 3 4 Beste Permutationen #1: 1 3 0 4 2 #2: 1 4 0 3 2 #3: 2 0 4 1 3 #4: 2 1 4 0 3 #5: 2 3 0 4 1 #6: 2 4 0 3 1 #7: 3 0 4 1 2 #8: 3 1 4 0 2 Sortierte Stichprobe: 0 1 2 3 4 Fakt Höchste Summe: Beste 11 Permutationen : 8 Berechnung Maximalsumme: 11 Beste Permutationen, mindestens: 4 Laufzeit = 00:00:00,01 Anfangsstichprobe: 0 1 2 3 Beste Permutationen #1: 1 3 0 2 #2: 2 0 3 1 Sortierte Stichprobe: 0 1 2 3 Fakt Größte Summe: 7 Beste Permutationen: 2 Berechnung Größte Summe: 7 Beste Permutationen, mindestens: 2 Laufzeit = 00:00:00,00 Anfangsstichprobe: 0 1 2 Beste Permutationen #1: 0 2 1 #2: 1 0 2 # 3: 1 2 0 #4: 2 0 1 Sortierte Stichprobe: 0 1 2 Fakt Höchste Summe: 3 Beste Permutationen: 4 Berechnung Höchste Summe: 3 Beste Permutationen, mindestens: 2 Laufzeit = 00:00:00,01 Anfangsstichprobe: 25 51 87 Sortierte Stichprobe: 25 51 87 Fakt Höchste Summe: 98 Beste Permutationen: 2 Berechnung Höchste Summe: 98 Beste Permutationen, mindestens: 2 Laufzeit = 00:00:00,00 Anfangsstichprobe: 78 10 34 64 Sortierte Stichprobe: 10 34 64 78 Fakt Höchste Summe: 166 Beste Permutationen: 2 Berechnung Höchste Summe: 166 Beste Permutationen, mindestens: 2 Laufzeit = 00:00:00,00 Ausgangsstichprobe: 23 26 93 16 45 Sortierte Stichprobe: 16 23 26 45 93 Fakt Höchste Summe: 195 Beste Permutationen : 4 Berechnung Größte Summe: 195 Beste Permutationen, mindestens: 4 Laufzeit = 00:00:00,00 Anfangsstichprobe: 85 12 11 13 90 88 Sortierte Stichprobe: 11 12 13 85 88 90 Fakt Größte Summe: 382 Beste Permutationen: 8 Berechnung Höchste Summe: 382 Beste Permutationen, mindestens: 8 Laufzeit = 00:00:00,00 Anfangsstichprobe: 82 43 28 55 29 39 18 Sortierte Stichprobe: 18 28 29 39 43 55 82 Fakt Höchste Summe: 206 Beste Permutationen: 24 Berechnung der höchsten Summe: 206 Beste Permutationen, mindestens: 24 Laufzeit = 00:00:00. 00 Originalprobe: 64 20 43 89 47 94 52 71 Sortierte Probe: 20 43 47 52 64 71 89 94 00.02 Originalprobe: 45 53 81 17 76 97 26 50 51 Sortierte Probe: 17 26 45 50 51 53 76 81 97 0: 00:00.23 Originalprobe: 55 51 36 12 82 63 89 58 14 22 Sortierte Probe: 12 14 22 36 51 55 58 63 82 89 : 1152 Laufzeit = 00:00:02.46 Originalprobe: 96 95 67 49 65 69 53 78 35 55 94 Sortierte Stichprobe: 35 49 53 55 65 67 69 78 94 95 96 Tatsächliche Summe: 348 Beste Permutationen: 11520 Berechnung der größten Summe: 348 Beste Permutationen, mindestens: 5760 Laufzeit = 00:00:29,21 Ausgangsstichprobe: 50 43 39 64 59 18 81 16 44 12 15 77 Sortierte Stichprobe: 12 15 16 18 39 43 44 50 59 64 77 81 Fakt Größte Summe: 463 Lu beste Permutationen: 28800 Berechnung Größte Summe: 463 Beste Permutationen, min: 28800 Laufzeit = 0 0: 06:26.81

ANALYSE DER ERGEBNISSE

In allen betrachteten Fällen bestätigten die Tests die vorgeschlagene allgemeine Lösung vollständig.

Excel verfügt über eine Funktion zur Ermittlung des Mindestwerts. Es ist jedoch problematisch, den Mindestwert anhand der Bedingung zu ermitteln. Eine Funktion aus dem Add-In kann diese Aufgabe übernehmen. =MINCESLI

(ähnlich der Standard-Excel-SUMIF-Funktion).

In Excel-Version 2016 und darüber gibt es eine eingebaute Funktion MINESLI du kannst es benutzen. Wenn es sich bei Ihrem Excel um eine ältere Version handelt, kann diese Funktion durch die Installation des Add-Ins genutzt werden VBA-Excel.

Die Funktion hat die folgenden Argumente =MINESLI( RANGE;KRITERIEN;[SEARCH_RANGE])

    BEREICH- Bereich der überprüften Zellen.

  • KRITERIUM– Eine Bedingung im Format einer Zahl, eines Ausdrucks oder eines Textes, die die Mindestwertprüfung angibt.
  • [ SEARCH_RANGE ]- Tatsächlicher Bereich zur Bestimmung des Mindestwerts. Wenn dieser Parameter nicht angegeben ist, werden die durch den Parameter angegebenen Zellen verwendet. BEREICH.

Beispiel 1

Als Kriterium können Sie Werte und logische Ausdrücke angeben:

  1. Betrachten Sie das folgende Beispiel, das die Mindestpunktzahl in der Literatur bestimmt. Dazu im Parameter KRITERIUM der Wert „Literatur“ wird angegeben, und der Parameter BEREICH- Liste von Gegenständen.
  2. Wenn Sie als Kriterium einen logischen Ausdruck angeben «<>Russisch", dann wird in allen Fächern außer der russischen Sprache die Mindestnote ermittelt.

Beispiel 2

Im folgenden Beispiel ist der Parameter SEARCH_RANGE ist nicht festgelegt, daher wird der Mindestwert unter den im Parameter angegebenen Zellen bestimmt BEREICH.

Excel verfügt über eine Funktion zur Ermittlung des Mindestwerts. Es ist jedoch problematisch, den Mindestwert anhand der Bedingung zu ermitteln. Eine Funktion aus dem Add-In kann diese Aufgabe übernehmen. =MINCESLI(ähnlich der Standard-Excel-SUMIF-Funktion).

In Excel-Version 2016 und darüber gibt es eine eingebaute Funktion MINESLI du kannst es benutzen. Wenn es sich bei Ihrem Excel um eine ältere Version handelt, kann diese Funktion durch die Installation des Add-Ins genutzt werden VBA-Excel.

Die Funktion hat die folgenden Argumente =MINESLI( RANGE;KRITERIEN;[SEARCH_RANGE])

    BEREICH- Bereich der überprüften Zellen.

  • KRITERIUM– Eine Bedingung im Format einer Zahl, eines Ausdrucks oder eines Textes, die die Mindestwertprüfung angibt.
  • [ SEARCH_RANGE ]- Tatsächlicher Bereich zur Bestimmung des Mindestwerts. Wenn dieser Parameter nicht angegeben ist, werden die durch den Parameter angegebenen Zellen verwendet. BEREICH.

Beispiel 1

Als Kriterium können Sie Werte und logische Ausdrücke angeben:

  1. Betrachten Sie das folgende Beispiel, das die Mindestpunktzahl in der Literatur bestimmt. Dazu im Parameter KRITERIUM der Wert „Literatur“ wird angegeben, und der Parameter BEREICH- Liste von Gegenständen.
  2. Wenn Sie als Kriterium einen logischen Ausdruck angeben "<>Russisch", dann wird in allen Fächern außer der russischen Sprache die Mindestnote ermittelt.

Beispiel 2

Im folgenden Beispiel ist der Parameter SEARCH_RANGE ist nicht festgelegt, daher wird der Mindestwert unter den im Parameter angegebenen Zellen bestimmt BEREICH.

Erstellen Sie eine neue Datei, indem Sie in der Symbolleiste für den Schnellzugriff auf die Schaltfläche „Neu“ klicken. Benennen Sie Blatt 1 in „Kostenanalyse“ um. Wir entwickeln eine Tabelle zur Analyse der Investitionskosten.

Abbildung 23. Analyse der Investitionskosten

Übung: Berechnen Sie die Kostenspalte mithilfe der Formel. Wenden Sie für die Spalten „Stückpreis“ und „Kosten“ das Währungsformat an.

Berechnen Sie in Zelle F12 die Gesamtinvestitionskosten. Dies kann auf zwei Arten erfolgen:

1 Weg:

Zelle F12 aktivieren;

Geben Sie die Formel ein: =F5+F6+F7+F8+F9+F10+F11 und drücken Sie die Eingabetaste.

2 Methode mit Funktion:

Eine Funktion in MS Excel ist eine Formel, in der bestimmte mathematische Operationen durch Funktionsnamen ersetzt werden. Um beispielsweise mehrere Werte zu addieren, verwenden Sie die SUMME-Funktion.

Ersetzen wir die übliche Formel in Zelle F12 durch eine Formel mit SUM:

Übung: Finden Sie die maximalen Investitionskosten. Dafür:

Aktivieren Sie die Zelle, in der Sie das Ergebnis erhalten möchten (F13);

Rechts neben der Schaltfläche „Betrag“ in der Gruppe „Bearbeiten“ auf der Registerkarte heim Klicken Sie auf den Pfeil mit der Dropdown-Liste der Funktionen und wählen Sie die Funktion aus Maximal(wählt den größten Wert aus dem ausgewählten Bereich aus);

Die Formel = MAX(F5:F11) wird in Zelle F13 angezeigt, F12 muss jedoch aus diesem Bereich ausgeschlossen werden. Bewegen Sie den Mauszeiger in Form eines weißen, fetten Kreuzes in die Mitte der ersten Zelle im Bereich (F5) und ziehen Sie die Auswahl in Zelle F11.

Drücken Sie Enter.

Übung: Ermitteln Sie den Mindest- und Durchschnittswert der Investitionskosten.

Funktion Minimum (=MIN)– zeigt den kleinsten Wert des Bereichs an;

Funktion Durchschnitt (=DURCHSCHNITT)– Berechnen Sie den Durchschnittswert des Bereichs, d. h. Addiert alle Elemente und dividiert durch die Anzahl der Elemente im Additionsbereich.

Funktion "Wenn"

Die Unternehmensleitung hat nach Prüfung der Investitionskosten in diesem Monat beschlossen, nur die Positionen zu bezahlen, deren Kosten weniger als 45.000 Rubel betragen.

Fügen wir der Tabelle eine Spalte „Zahlung“ hinzu, in der Sie Folgendes angeben müssen:

Wenn die Kosten mehr als 45.000 Rubel betragen. - Verschieben;

Wenn die Kosten weniger als 45.000 Rubel betragen. - "In Bezahlung."

Aktivieren Sie Zelle G5 und öffnen Sie die Registerkarte Formeln, Drücken Sie den Knopf Funktion einfügen.

Wählen Sie im Dialogfeld eine Kategorie aus Rätsel, in der Liste der Funktionen - WENN.

Abbildung 24. IF-Funktion

Erweitern Sie den in Zelle G5 erhaltenen Wert bis zum Ende der Tabelle (bis G11).

Verwendung mehrerer Bedingungen bei der Anwendung der „IF“-Funktion

Gehen Sie zum zweiten Blatt Ihrer Arbeitsmappe und benennen Sie es in „Gehalt“ um. Geben Sie die folgenden Details ein:

Abbildung 25. Beispiel für die Berechnung des Verkaufsvolumenbonus

Die IF-Funktion besteht aus folgenden Elementen: Funktionsname, Bedingung, Werte, wenn die Bedingung wahr ist, Werte, wenn die Bedingung falsch ist. Ein Funktionseintrag in einer Zelle sieht folgendermaßen aus:

If(Bedingung; Wert, wenn Bedingungen wahr sind; Wert, wenn Bedingungen falsch sind)

Stellen Sie sich ein Beispiel vor, bei dem es zwei Bedingungen gibt: Wenn das Umsatzvolumen eines Mitarbeiters mehr als 50.000 Rubel beträgt, beträgt der Bonus 10 % des Umsatzvolumens, andernfalls 5 %.

Für unser Beispiel sieht dieser Eintrag so aus:

Abbildung 26. Beispiel für die Berechnung der Volumenprämie mit einer Kondition

Betrachten Sie das zweite Beispiel, bei dem der Mitarbeiterbonus für Verkaufsmengen wie folgt berechnet wird:

Wenn ein Mitarbeiter Waren für einen Betrag von mehr als oder gleich 90.000 Rubel verkauft hat, beträgt sein Bonus 10 % des Verkaufsvolumens, wenn die Menge der verkauften Waren mehr als oder gleich 30.000 Rubel beträgt, beträgt der Bonus 5 % des Verkaufsvolumens Verkaufsvolumen, wenn der Betrag weniger als 30.000 Rubel beträgt, beträgt der Bonus 3 %.

Abbildung 27. Beispiel für die Berechnung der Verkaufsprämie mit mehreren Bedingungen

In diesem Fall gibt es mehrere Bedingungen und der Funktionseintrag sieht folgendermaßen aus:

Bedingte Formatierung

Mit der bedingten Formatierung können Sie unterschiedliche Formate für eine Zelle unter unterschiedlichen Bedingungen festlegen. Zum Beispiel Füllzellen, in denen das Verkaufsvolumen mehr als 30.000 Rubel beträgt. muss rot sein, weniger als 30.000 Rubel. - Gelb.

Wählen Sie den Bereich С2:С5;

Auf der Registerkarte Zuhause in Gruppenstile klicken Sie auf die Schaltfläche Bedingte Formatierung und wähle ein Team Regeln für die Auswahl von Zellen... Andere Regeln.

Legen Sie das Format fest. Wenn der Zellenwert mehr als 30.000 beträgt, klicken Sie auf OK.

Abbildung 28. Bedingte Formatierung

Führen Sie die gleiche Aktion aus und legen Sie das Format für Zellen mit einem Wert von weniger als 30.000 Rubel fest.

Versuchen Sie es mit folgenden Befehlen: Balkendiagramme, Farbbalken und Symbolsätze vom Punkt "Bedingte Formatierung".

Um das bei der bedingten Formatierung angewendete Format zu entfernen, müssen Sie auf die Schaltfläche klicken Bedingte Formatierung…Regeln entfernen.

Berechnung der Kreditzahlung

Berechnen Sie die Höhe der Kreditzahlung mit der PMT-Funktion.

Gehen Sie zu Blatt 2 und benennen Sie es in „Credit“ um. Geben Sie die Ausgangsdaten für die Berechnung ein. Bitte beachten Sie, dass in Zelle B2 der jährliche Zinsbetrag für das Darlehen angezeigt wird. Zelle B5 enthält die Formel.

Abbildung 29. Berechnung der Kreditzahlung

So berechnen Sie Ihre monatliche Kreditrate:

    Aktivieren Sie Zelle B6;

    Auf der Registerkarte Formeln Drücken Sie den Knopf Funktion einfügen. Kategorie wählen Vollständige alphabetische Liste, und finden Sie die Funktion unten PMT.

Abbildung 30. Einfügen der PMT-Funktion

Auf dem Feld Gebot Geben Sie die monatliche Rate ein, d. h. B2/B4;

Auf dem Feld Kper– Sie müssen die Gesamtzahl der Perioden angeben, d. h. UM 5;

Auf dem Feld Ps (Barwert)– es ist notwendig, den beantragten Kreditbetrag anzugeben;

Сс - Barguthaben, zu erreichen nach der letzten Zahlung, in unserem Fall ist es 0;

Typ- 0 – Zahlungen erfolgen am Ende der Periode, 1 – Zahlungen erfolgen am Anfang der Periode.

Das Ergebnis, das Sie erhalten, muss mit einem Minuszeichen versehen sein, denn. Dabei handelt es sich um Zahlungen, nicht um Barbelege.

Absolute und relative Adressierung

Gehen Sie zu Blatt 3 im Buch und benennen Sie es Wasserverbrauch. Geben Sie diese Zählerstände ein, indem Sie eine Tabelle erstellen:

Abbildung 31. Tabellenbeispiel

Aufmerksamkeit! Die Werte der m3-Kosten für Kalt- und Warmwasser müssen in separate Zellen aus dem Text eingetragen werden, weil Sie werden in den Formeln zur Berechnung des zu zahlenden Betrags verwendet.

Wasserverbrauch wird als Differenz zwischen den Zählerständen des aktuellen Monats und des Vormonats berechnet:

Aktivieren Sie D9, geben Sie die Formel ein: =B9-B8 und drücken Sie die Eingabetaste.

Verwenden Sie den automatischen Füllpunkt, um Ihren Kaltwasserverbrauch für die verbleibenden Monate zu berechnen.

Berechnen Sie auf die gleiche Weise Warmwasserverbrauch.

Bezahlbarer Betrag pro HV für Januar wird als Produkt aus der verbrauchten Wassermenge und den Kosten für m 3 berechnet. Die Formel für unser Beispiel lautet:

    Zelle F9 aktivieren;

    Geben Sie die Formel ein: =D9*E4 und drücken Sie Enter%

    Versuchen wir, die Formel mithilfe des Autofill-Punkts zu erweitern:

Kehren Sie zu Zelle F9 zurück, bewegen Sie die Maus in die untere rechte Ecke der Zelle, fangen Sie den Mauszeiger in Form eines schwarzen Kreuzes ein, halten Sie die linke Maustaste gedrückt und ziehen Sie die Formel an das Ende der Tabelle.

Abbildung 32. Notwendigkeit einer absoluten Adressierung

Stellen Sie sicher, dass die automatische Vervollständigung nicht funktioniert hat. Mal sehen, warum:

Aktivieren Sie Zelle F9, die Formel sollte in der Formelleiste erscheinen: = D9 * E4;

Aktivieren Sie Zelle F10. Excel hat mithilfe der automatischen Vervollständigung die Formel geändert und berechnet nun den Wert der Zelle F10 als =D10*E5. Das heißt, alle Zellen, die in einer Formel verwendet werden, wenn die Formel durch die automatische Vervollständigung nach unten kopiert wird, werden ebenfalls nach unten verschoben. In unserem Fall ist es notwendig, Spalte D nach unten zu verschieben, weil für jeden Monat ein anderer Wert des Wasserverbrauchs, aber eine Bewegung entlang der Spalte E ist nicht notwendig, weil. mit E4 multiplizieren.

Lösung:

    Gehen Sie zurück zu Zelle F9 und sehen Sie in der Formelleiste nach: =D9*E4.

    In der Formel müssen Sie eine Zelle finden, die sich beim Kopieren nicht ändern soll. Das ist E4.

    Platzieren Sie in der Formelleiste den Cursor in der Formel zwischen E und 4. Drücken Sie dann die Taste F4 auf der Tastatur. Die Formel sieht dann wie folgt aus: =D*$E$4. Die $-Zeichen für Excel bedeuten das Fixieren dieser Zelle oder die absolute Adressierung. Beim Kopieren dieser Formel mit einem Autofill-Punkt wird diese Zelle nicht geändert.

    Drücken Sie die Eingabetaste, kehren Sie zu Zelle F9 zurück und ziehen Sie den Autofill-Punkt nach unten. Wählen Sie Zelle F10 aus – achten Sie auf die Formel.

Übung: Füllen Sie die Spalte aus Für GV zu zahlender Betrag mit absoluter Adressierung und Spalte Total bezahlbar- als Summe der Spalten F und G.

Speichern Sie die Datei unter einem Namen in Ihrem Ordner „Funktionen“.

Mit dieser Tabellenkalkulation können fast alle Berechnungen durchgeführt werden. Es ist ideal für die Buchhaltung. Für Berechnungen gibt es spezielle Werkzeuge – Formeln. Sie können auf einen Bereich oder auf einzelne Zellen angewendet werden. Um die minimale oder maximale Anzahl in einer Gruppe von Zellen herauszufinden, ist es nicht notwendig, selbst danach zu suchen. Nutzen Sie hierfür besser die vorgesehenen Möglichkeiten. Es wird auch nützlich sein, herauszufinden, wie man den Durchschnittswert in Excel berechnet.

Dies gilt insbesondere für Tabellen mit großen Datenmengen. Wenn die Spalte beispielsweise die Preise der Produkte des Einkaufszentrums angibt. Und Sie müssen herausfinden, welches Produkt das günstigste ist. Wenn Sie „manuell“ danach suchen, wird dies viel Zeit in Anspruch nehmen. Aber in Excel ist dies mit nur wenigen Klicks erledigt. Das Dienstprogramm berechnet auch das arithmetische Mittel. Schließlich handelt es sich um zwei einfache Operationen: Addition und Division.

Maximum und Minimum

So ermitteln Sie den Maximalwert in Excel:

  1. Platzieren Sie den Zellcursor an einer beliebigen Stelle.
  2. Gehen Sie zum Menü „Formeln“.
  3. Klicken Sie auf Funktion einfügen.
  4. Wählen Sie „MAX“ aus der Liste. Oder schreiben Sie dieses Wort in das Feld „Suchen“ und klicken Sie auf „Suchen“.
  5. Geben Sie im Fenster „Argumente“ die Adressen des Bereichs ein, dessen Maximalwert Sie wissen möchten. In Excel bestehen Zellennamen aus einem Buchstaben und einer Zahl („B1“, „F15“, „W34“). Und der Name des Bereichs besteht aus der ersten und letzten Zelle, die darin enthalten ist.
  6. Anstelle einer Adresse können Sie auch mehrere Nummern schreiben. Dann zeigt das System den größten davon an.
  7. OK klicken. Das Ergebnis erscheint in der Zelle, in der sich der Cursor befand.

Der nächste Schritt besteht darin, einen Wertebereich anzugeben

Jetzt wird es einfacher herauszufinden, wie man den Mindestwert in Excel findet. Der Aktionsalgorithmus ist völlig identisch. Wählen Sie einfach „MIN“ statt „MAX“.

Durchschnitt

Das arithmetische Mittel wird wie folgt berechnet: Addiere alle Zahlen aus der Menge und dividiere durch ihre Zahl. In Excel können Sie Summen berechnen, herausfinden, wie viele Zellen sich in einer Zeile befinden und so weiter. Aber es ist zu kompliziert und zu lang. Sie müssen viele verschiedene Funktionen nutzen. Behalten Sie die Informationen im Hinterkopf. Oder schreiben Sie etwas auf ein Blatt Papier. Der Algorithmus kann jedoch vereinfacht werden.

So ermitteln Sie den Durchschnitt in Excel:

  1. Platzieren Sie den Zellencursor an einer beliebigen freien Stelle in der Tabelle.
  2. Gehen Sie zur Registerkarte „Formeln“.
  3. Klicken Sie auf „Funktion einfügen“.
  4. Wählen Sie DURCHSCHNITT.
  5. Wenn sich dieses Element nicht in der Liste befindet, öffnen Sie es mit der Option „Suchen“.
  6. Geben Sie im Bereich Nummer1 die Adresse des Bereichs ein. Oder schreiben Sie mehrere Zahlen in verschiedene Felder „Nummer2“, „Nummer3“.
  7. OK klicken. Der gewünschte Wert wird in der Zelle angezeigt.

So können Sie Berechnungen nicht nur mit Positionen in der Tabelle, sondern auch mit beliebigen Mengen durchführen. Tatsächlich spielt Excel die Rolle eines fortgeschrittenen Taschenrechners.

andere Methoden

Das Maximum, das Minimum und der Durchschnitt können auf andere Weise ermittelt werden.

  1. Suchen Sie die Funktionsleiste mit der Bezeichnung „Fx“. Es befindet sich über dem Hauptarbeitsbereich des Tisches.
  2. Platzieren Sie den Cursor in einer beliebigen Zelle.
  3. Geben Sie im Feld „Fx“ ein Argument ein. Es beginnt mit einem Gleichheitszeichen. Dann kommt die Formel und die Adresse des Bereichs/der Zelle.
  4. Sie sollten so etwas wie „=MAX(B8:B11)“ (Maximum), „=MIN(F7:V11)“ (Minimum), „=AVERAGE(D14:W15)“ (Durchschnitt) erhalten.
  5. Klicken Sie auf das „Häkchen“ neben dem Funktionsfeld. Oder drücken Sie einfach die Eingabetaste. Der gewünschte Wert wird in der ausgewählten Zelle angezeigt.
  6. Die Formel kann direkt in die Zelle selbst kopiert werden. Der Effekt wird derselbe sein.

Das Excel-Tool „Autofunctions“ hilft beim Finden und Berechnen.

  1. Platzieren Sie den Cursor in der Zelle.
  2. Suchen Sie die Schaltfläche, deren Name mit „Auto“ beginnt. Dies hängt von der in Excel ausgewählten Standardoption ab (AutoSum, AutoNumber, AutoOffset, AutoIndex).
  3. Klicken Sie auf den schwarzen Pfeil darunter.
  4. Wählen Sie MIN (Minimum), MAX (Maximum) oder AVERAGE (Durchschnitt).
  5. Die Formel wird in der markierten Zelle angezeigt. Klicken Sie auf eine beliebige andere Zelle – sie wird der Funktion hinzugefügt. „Ziehen“ Sie das Kästchen darum herum, um den Bereich abzudecken. Oder klicken Sie bei gedrückter Strg-Taste auf das Raster, um jeweils ein Element auszuwählen.
  6. Wenn Sie fertig sind, drücken Sie die Eingabetaste. Das Ergebnis wird in einer Zelle angezeigt.

In Excel ist die Berechnung des Durchschnitts ziemlich einfach. Es ist nicht nötig, den Betrag zu addieren und dann zu dividieren. Hierfür gibt es eine eigene Funktion. Sie können auch das Minimum und das Maximum in einem Satz ermitteln. Es ist viel einfacher, als von Hand zu zählen oder Zahlen in einer riesigen Tabelle nachzuschlagen. Daher ist Excel in vielen Tätigkeitsbereichen beliebt, in denen Genauigkeit erforderlich ist: Wirtschaft, Wirtschaftsprüfung, Personalaktenverwaltung, Finanzen, Handel, Mathematik, Physik, Astronomie, Wirtschaft, Naturwissenschaften.