Transportauftrag in Excel. Suche nach der besten Transportmethode vom Verkäufer zum Käufer

Excel ist ein sehr funktionales Programm. Es kann verwendet werden, um eine Vielzahl von Problemen zu lösen, denen man sich im Geschäft stellen muss. Eine der häufigsten ist der Transport. Stellen Sie sich vor, wir müssten verstehen, welche Transportmethode vom Hersteller zum Endabnehmer in Bezug auf Zeit, Geld und andere Ressourcen am optimalsten ist. Dieses Problem ist sehr beliebt, egal in welcher Branche das Unternehmen tätig ist. Schauen wir uns daher genauer an, wie es mit Excel implementiert werden kann.

Beschreibung der Transportaufgabe

Wir haben also zwei Kontrahenten, die ständig miteinander interagieren. In unserem Fall ist dies ein Käufer und ein Verkäufer. Wir müssen herausfinden, wie wir Waren so transportieren, dass die Kosten minimal sind. Dazu müssen Sie alle Daten in Schema- oder Matrixform darstellen. In Excel verwenden wir die letztere Option. Im Allgemeinen gibt es zwei Arten von Transportaufgaben:

1. Abgeschlossen. In diesem Fall sind Angebot und Nachfrage im Gleichgewicht.
2. Offen. Hier gibt es kein Gleichgewicht zwischen Angebot und Nachfrage. Um eine Lösung für dieses Problem zu erhalten, müssen Sie es zuerst zum ersten Typ bringen und Angebot und Nachfrage ausgleichen. Dazu müssen Sie einen zusätzlichen Indikator einführen – das Vorhandensein eines bedingten Käufers oder Verkäufers. Außerdem müssen Sie bestimmte Änderungen an der Kostentabelle vornehmen.

So aktivieren Sie die Funktion „Lösung suchen“ in Excel

Um Transportprobleme in Excel zu lösen, gibt es eine spezielle Funktion namens „Suche nach einer Lösung“. Es ist standardmäßig nicht aktiviert, daher müssen Sie die folgenden Schritte ausführen:

1. Öffnen Sie das Menü „Datei“, das sich in der oberen linken Ecke des Programmfensters befindet.
2. Klicken Sie danach auf die Schaltfläche mit den Parametern.
3. Als nächstes finden wir den Unterabschnitt „Einstellungen“ und gehen zum Add-Ons-Verwaltungsmenü. Dies sind kleine Programme, die in der Microsoft Excel-Umgebung ausgeführt werden. Wir sehen, dass wir zuerst auf das Menü „Add-Ins“ geklickt und dann im unteren rechten Teil den Punkt „Excel-Add-Ins“ eingestellt und auf die Schaltfläche „Los“ geklickt haben. Alle notwendigen Aktionen werden mit roten Rechtecken und Pfeilen hervorgehoben.
4. Schalten Sie als Nächstes das Add-In „Nach einer Lösung suchen“ ein. Danach bestätigen wir unsere Aktionen durch Drücken der OK-Taste. Anhand der Beschreibung der Einstellung können wir erkennen, dass sie für die Analyse komplexer Daten wie wissenschaftlicher und finanzieller Daten ausgelegt ist.
5. Gehen Sie danach auf die Registerkarte „Daten“, wo wir eine neue Schaltfläche sehen, die genauso heißt wie das Add-In. Es befindet sich in der Werkzeuggruppe Analyse.

Es bleibt nur noch auf diese Schaltfläche zu klicken, und wir fahren mit der Lösung des Transportproblems fort. Aber vorher sollten wir ein wenig mehr über das Solver-Tool in Excel sprechen. Dabei handelt es sich um ein spezielles Excel-Add-on, das es ermöglicht, die schnellste Lösung für ein Problem zu finden. Ein charakteristisches Merkmal ist die Berücksichtigung von Restriktionen, die der Benutzer in der Vorbereitungsphase festlegt. Einfach ausgedrückt ist dies ein Unterprogramm, das es ermöglicht, den besten Weg zur Lösung einer bestimmten Aufgabe zu ermitteln. Solche Aufgaben können Folgendes umfassen:

1. Investieren, Lager beladen oder ähnliche Aktivitäten. Einschließlich der Lieferung von Waren.
2. Der beste Weg. Dazu gehören Ziele wie das Erzielen eines maximalen Gewinns bei minimalen Kosten, das Erreichen der besten Qualität mit verfügbaren Ressourcen und so weiter.

Neben Transportaufgaben wird dieses Add-On auch für folgende Zwecke verwendet:

1. Entwicklung eines Produktionsplans. Das heißt, wie viele Einheiten eines Produkts hergestellt werden müssen, um ein maximales Einkommen zu erzielen.
2. Finden Sie die Arbeitsverteilung für verschiedene Arten von Arbeit so, dass die Gesamtkosten für die Herstellung eines Produkts oder einer Dienstleistung am geringsten sind.
3. Legen Sie die Mindestzeit fest, die benötigt wird, um alle Arbeiten abzuschließen.

Wie Sie sehen, sind die Aufgaben sehr unterschiedlich. Die allgemeine Regel für die Anwendung dieses Add-Ins lautet, dass vor der Lösung des Problems ein Modell erstellt werden muss, das den Schlüsselmerkmalen des gestellten Problems entspricht. Ein Modell ist eine Sammlung von Funktionen, die Variablen als Argumente verwenden. Das heißt, Werte, die sich ändern können.

Es ist wichtig zu beachten, dass die Optimierung einer Reihe von Werten ausschließlich auf einem Indikator durchgeführt wird, der als Zielfunktion bezeichnet wird.

Das Solver-Add-In listet die verschiedenen Werte der Variablen, die an die Zielfunktion übergeben werden, so auf, dass es sich um das Maximum, Minimum oder gleich einem bestimmten Wert handelt (genau dies ist die Einschränkung). Es gibt noch eine weitere Funktion, die im Prinzip etwas ähnlich ist und oft mit der „Suche nach einer Lösung“ verwechselt wird. Es heißt „Optionsauswahl“. Aber wenn Sie tiefer graben, ist der Unterschied zwischen ihnen enorm:

1. Die Goal Seek-Funktion funktioniert nicht mit mehr als einer Variablen.
2. Es bietet keine Möglichkeit, Grenzen für Variablen festzulegen.
3. Es kann nur die Gleichheit der Zielfunktion mit einem bestimmten Wert bestimmen, ermöglicht es jedoch nicht, das Maximum und Minimum zu finden. Daher ist es für unsere Aufgabe nicht geeignet.
4. Kann nur effizient berechnen, wenn der Modelltyp linear ist. Wenn das Modell nichtlinear ist, findet es den Wert, der dem ursprünglichen Wert am nächsten kommt.

Der Transportauftrag ist wesentlich komplizierter aufgebaut, dafür reicht das Add-On „Parameterauswahl“ nicht aus. Betrachten wir die praktische Umsetzung der Funktion „Suche nach einer Lösung“ am Beispiel eines Transportproblems genauer.

Ein Beispiel für die Lösung eines Transportproblems in Excel

Um anschaulich zu demonstrieren, wie Transportprobleme in der Praxis in Excel gelöst werden können, geben wir ein Beispiel.

Bedingungen Aufgaben

Angenommen, wir haben 6 Verkäufer und 7 Käufer. Nachfrage und Angebot zwischen ihnen sind jeweils wie folgt verteilt: 36, 51, 32, 44, 35 und 38 Einheiten sind Verkäufer und 33, 48, 30, 36, 33, 24 und 32 Einheiten sind Käufer. Wenn Sie all diese Werte zusammenfassen, werden Sie feststellen, dass sich Angebot und Nachfrage die Waage halten. Daher ist dieses Problem von einem geschlossenen Typ, der sehr einfach gelöst wird.

Darüber hinaus haben wir Informationen darüber, wie viel Sie für den Transport von Punkt A nach Punkt B ausgeben müssen (im Beispiel sind sie in gelben Zellen hervorgehoben).

Lösung – Schritt-für-Schritt-Algorithmus

Nachdem wir uns nun mit den Tabellen mit den Ausgangsdaten vertraut gemacht haben, können wir dieses Problem mit folgendem Algorithmus lösen:

1. Zuerst erstellen wir eine Tabelle, die aus 6 Zeilen und 7 Spalten besteht.
2. Danach gehen wir zu einer beliebigen Zelle, die keine Werte enthält und gleichzeitig außerhalb der neu erstellten Tabelle liegt und fügen die Funktion ein. Klicken Sie dazu auf den fx-Button, der sich links neben der Funktionseingabezeile befindet.
3. Wir haben ein Fenster, in dem wir die Kategorie „Mathe“ auswählen müssen. Welche Funktion interessiert uns? Der in diesem Screenshot hervorgehobene. Funktion SUMMENPRODUKT multipliziert Bereiche oder Arrays untereinander und summiert sie. Genau das, was wir brauchen. Drücken Sie danach die OK-Taste.
4. Als nächstes erscheint ein Fenster auf dem Bildschirm, in dem Sie die Funktionsparameter angeben müssen. Sie sind die folgenden:
   1. Array 1. Dies ist das erste Argument, in das wir den gelb markierten Bereich schreiben. Sie können die Funktionsparameter entweder über die Tastatur oder durch Auswahl des entsprechenden Bereichs mit der linken Maustaste einstellen.
   2. Array 2. Dies ist das zweite Argument, das die neu erstellte Tabelle ist. Aktionen werden auf die gleiche Weise ausgeführt.

Bestätigen Sie Ihre Aktion durch Drücken der OK-Taste.

1. Danach klicken wir mit der linken Maustaste auf die Zelle, die in der neu erstellten Tabelle oben links dient. Klicken Sie nun erneut auf die Funktionsschaltfläche Einfügen.
2. Wir wählen dieselbe Kategorie wie im vorherigen Fall. Uns interessiert diesmal aber die Funktion SUM.
3. Jetzt kommt die Phase des Ausfüllens der Argumente. Als erstes Argument schreiben wir die oberste Zeile der Tabelle, die wir zu Beginn erstellt haben. Dies kann auf die gleiche Weise wie zuvor durch Auswählen dieser Zellen auf dem Blatt oder manuell erfolgen. Wir bestätigen unsere Aktionen durch Drücken der OK-Taste.
4. Wir sehen die Ergebnisse in der Zelle mit der Funktion. In diesem Fall ist es null. Bewegen Sie als Nächstes den Cursor in die untere rechte Ecke, woraufhin eine Markierung für die automatische Vervollständigung angezeigt wird. Es sieht aus wie ein kleiner schwarzer Plüsch. Wenn es angezeigt wird, halten Sie die linke Maustaste gedrückt und bewegen Sie den Cursor in die letzte Zelle unserer Tabelle.
5. Dies gibt uns die Möglichkeit, die Formel auf alle anderen Zellen zu übertragen und die richtigen Ergebnisse zu erhalten, ohne zusätzliche Berechnungen durchführen zu müssen.
6. Der nächste Schritt besteht darin, die obere linke Zelle auszuwählen und die Funktion einzufügen SUM in sie hinein. Danach geben wir die Argumente ein und verwenden die Autocomplete-Markierung, um alle verbleibenden Zellen auszufüllen.
7. Danach gehen wir direkt zur Lösung des Problems über. Dazu verwenden wir das zuvor enthaltene Add-On. Gehen Sie auf die Registerkarte „Daten“, und dort finden wir das Tool „Suche nach einer Lösung“. Wir klicken auf diese Schaltfläche.
8. Jetzt ist vor unseren Augen ein Fenster erschienen, durch das Sie die Parameter unseres Add-Ons konfigurieren können. Werfen wir einen Blick auf jede dieser Optionen:
   1. Optimieren Sie die Zielfunktion. Hier müssen wir die Zelle auswählen, die die Funktion enthält SUMMENPRODUKT. Wir sehen, dass diese Option es ermöglicht, eine Funktion auszuwählen, für die eine Lösung gesucht wird.
   2. Vor. Hier stellen wir die Option „Minimum“ ein.
   3. Durch Ändern der Zellen der Variablen. Hier geben wir den Bereich an, der der Tabelle entspricht, die wir ganz am Anfang erstellt haben (mit Ausnahme der zusammenfassenden Zeile und Spalte).
   4. Einschränkungen vorbehalten. Hier müssen wir Einschränkungen hinzufügen, indem wir auf die Schaltfläche Hinzufügen klicken.
   5. Wir erinnern uns, welche Art von Beschränkung wir schaffen müssen – die Summe der Werte der Käufernachfrage und der Verkäuferangebote muss gleich sein.
9. Die Aufgabe der Beschränkungen wird wie folgt ausgeführt:
   1. Verknüpfung zu Zellen. Hier geben wir den Bereich der Tabelle für Berechnungen ein.
   2. Bedingungen. Dies ist eine mathematische Operation, gegen die der im ersten Eingabefeld angegebene Bereich geprüft wird.
   3. Der Wert der Bedingung oder Einschränkung. Hier tragen wir die entsprechende Spalte in die Quelltabelle ein.
   4. Nachdem alle Schritte abgeschlossen sind, klicken Sie auf die Schaltfläche OK, um unsere Aktionen zu bestätigen.

Wir führen genau die gleichen Operationen für die obersten Zeilen durch und setzen die folgende Bedingung: Sie müssen gleich sein.

Im nächsten Schritt werden die Bedingungen festgelegt. Wir müssen die folgenden Kriterien für die Summe der Zellen in der Tabelle festlegen – größer oder gleich Null, eine ganze Zahl. Als Ergebnis haben wir eine solche Liste von Bedingungen, unter denen das Problem gelöst wird. Hier müssen Sie sicherstellen, dass das Kontrollkästchen neben der Option „Variablen ohne Grenzen nicht-negativ machen“ aktiviert ist. Auch in unserer Situation ist es erforderlich, dass die Methode zur Lösung des Problems gewählt wird – „Suche nach einer Lösung für nichtlineare Probleme von OPG-Methoden“. Jetzt können wir mit Sicherheit sagen, dass die Einstellung abgeschlossen ist. Daher müssen nur noch die Berechnungen durchgeführt werden. Klicken Sie dazu auf die Schaltfläche „Lösung finden“.

Danach werden alle Daten automatisch berechnet und Excel zeigt ein Fenster mit den Ergebnissen an. Es ist notwendig, um den Betrieb des Computers zu überprüfen, da Fehler möglich sind, wenn die Bedingungen zuvor falsch eingestellt wurden. Wenn alles korrekt ist, klicken Sie auf die Schaltfläche „OK“ und sehen Sie sich die fertige Tabelle an.

Wenn sich herausstellt, dass unsere Aufgabe zu einem offenen Typ geworden ist, ist dies schlecht, da Sie die Quelltabelle bearbeiten müssen, damit die Aufgabe zu einer geschlossenen wird. Wenn dies jedoch geschehen ist, ist der verbleibende Algorithmus derselbe.

Zusammenfassung

Wie Sie sehen, kann Excel auch für sehr komplexe Berechnungen verwendet werden, die auf den ersten Blick einem einfachen Computerprogramm, das in fast jedem installiert ist, nicht zur Verfügung stehen. Wie auch immer es ist. Heute haben wir bereits die fortgeschrittene Nutzungsebene abgedeckt. Dieses Thema ist nicht so einfach, aber wie sie sagen, wird die Straße vom Gehen gemeistert. Die Hauptsache ist, den Aktionsplan zu befolgen und alle oben angegebenen Aktionen genau auszuführen. Dann treten keine Fehler auf und das Programm führt alle erforderlichen Berechnungen selbstständig durch. Sie müssen nicht darüber nachdenken, welche Funktion Sie verwenden möchten und so weiter.