Transporttaak in Excel. Het vinden van de beste transportmethode van de verkoper naar de koper

Excel is een zeer functioneel programma. Het kan worden gebruikt om een ​​enorme laag problemen op te lossen waarmee men in het bedrijfsleven te maken krijgt. Een van de meest voorkomende is vervoer. Stel je voor dat we moeten begrijpen welke transportmethode van de fabrikant naar de uiteindelijke koper het meest optimaal is in termen van tijd, geld en andere middelen. Dit probleem is vrij populair, ongeacht in welke branche het bedrijf zich bevindt. Laten we daarom eens nader bekijken hoe u het kunt implementeren met Excel.

Beschrijving van de transporttaak

We hebben dus twee tegenpartijen die voortdurend met elkaar communiceren. In ons geval is dit een koper en een verkoper. We moeten uitzoeken hoe we goederen zo kunnen vervoeren dat de kosten minimaal zijn. Om dit te doen, moet u alle gegevens in een schematische of matrixvorm presenteren. In Excel gebruiken we de laatste optie. Over het algemeen zijn er twee soorten transporttaken:

1. Gesloten. Vraag en aanbod zijn in dit geval in evenwicht.
2. Open. Er is hier geen gelijkheid tussen vraag en aanbod. Om een ​​oplossing voor dit probleem te krijgen, moet je het eerst naar het eerste type brengen, waarbij vraag en aanbod gelijk worden gemaakt. Om dit te doen, moet u een extra indicator introduceren: de aanwezigheid van een voorwaardelijke koper of verkoper. Daarnaast moet u bepaalde wijzigingen in de kostentabel doorvoeren.

Hoe de functie Oplossing zoeken in Excel in te schakelen

Om transportproblemen in Excel op te lossen, is er een speciale functie genaamd "Zoeken naar een oplossing". Het is standaard niet ingeschakeld, dus u moet de volgende stappen uitvoeren:

1. Open het menu "Bestand", dat zich in de linkerbovenhoek van het programmavenster bevindt.
2. Klik daarna op de knop met de parameters.
3. Vervolgens vinden we de subsectie "Instellingen" en gaan we naar het beheermenu van add-ons. Dit zijn kleine programma's die draaien binnen de Microsoft Excel-omgeving. We zien dat we eerst op het menu "Add-ins" hebben geklikt en vervolgens rechtsonder het item "Excel Add-ins" hebben ingesteld en op de knop "Go" hebben geklikt. Alle noodzakelijke acties zijn gemarkeerd met rode rechthoeken en pijlen.
4. Schakel vervolgens de invoegtoepassing "Zoeken naar een oplossing" in, waarna we onze acties bevestigen door op de OK-knop te drukken. Op basis van de beschrijving van de instelling kunnen we zien dat deze is ontworpen om complexe gegevens te analyseren, zoals wetenschappelijke en financiële.
5. Ga daarna naar het tabblad "Gegevens", waar we een nieuwe knop zien, die hetzelfde wordt genoemd als de invoegtoepassing. Het is te vinden in de toolgroep Analyse.

Het blijft alleen om op deze knop te klikken en we gaan verder met de oplossing van het transportprobleem. Maar daarvoor moeten we iets meer praten over de Oplosser-tool in Excel. Dit is een speciale Excel add-on die het mogelijk maakt om de snelste oplossing voor een probleem te vinden. Kenmerkend is de afweging van beperkingen die de gebruiker stelt in de voorbereidingsfase. Simpel gezegd is dit een subroutine die het mogelijk maakt om te bepalen hoe een bepaalde taak het beste kan worden uitgevoerd. Dergelijke taken kunnen het volgende omvatten:

1. Investeren, een magazijn laden of een andere soortgelijke activiteit. Inclusief de levering van goederen.
2. De beste manier. Denk hierbij aan doelen als het behalen van maximale winst tegen minimale kosten, hoe de beste kwaliteit te bereiken met beschikbare middelen, enzovoort.

Naast transporttaken wordt deze add-on ook gebruikt voor de volgende doeleinden:

1. Opstellen van een productieplan. Dat wil zeggen, hoeveel eenheden van een product moeten worden geproduceerd om een ​​maximaal inkomen te behalen.
2. Vind de verdeling van arbeid voor verschillende soorten werk, zodat de totale kosten voor het produceren van een product of dienst het kleinst zijn.
3. Stel de minimale tijd in die nodig is om al het werk te voltooien.

Zoals je kunt zien, zijn de taken heel verschillend. De universele regel voor het toepassen van deze invoegtoepassing is dat voordat het probleem wordt opgelost, er een model moet worden gemaakt dat overeenkomt met de belangrijkste kenmerken van het gestelde probleem. Een model is een verzameling functies die variabelen als hun argumenten gebruiken. Dat zijn waarden die kunnen veranderen.

Het is belangrijk op te merken dat de optimalisatie van een reeks waarden uitsluitend op één indicator wordt uitgevoerd, die de objectieve functie wordt genoemd.

De Oplosser-invoegtoepassing somt de verschillende waarden van de variabelen op die worden doorgegeven aan de doelfunctie zodanig dat deze het maximum, minimum of gelijk is aan een bepaalde waarde (dit is precies de beperking). Er is nog een functie die qua werkingsprincipe enigszins op elkaar lijkt en die vaak wordt verward met "Zoeken naar een oplossing". Het heet "Optie Selectie". Maar als je dieper graaft, is het verschil tussen beide enorm:

1. De functie Doel zoeken werkt niet met meer dan één variabele.
2. Het biedt niet de mogelijkheid om limieten voor variabelen in te stellen.
3. Het is in staat om alleen de gelijkheid van de objectieve functie tot een bepaalde waarde te bepalen, maar maakt het niet mogelijk om het maximum en minimum te vinden. Daarom is het niet geschikt voor onze taak.
4. Alleen in staat om efficiënt te berekenen als het model lineair type. Als het model niet-lineair is, vindt het de waarde die het dichtst bij de oorspronkelijke waarde ligt.

De transporttaak is veel gecompliceerder in zijn structuur, dus de add-on "Parameterselectie" is hiervoor niet voldoende. Laten we eens nader bekijken hoe de functie “Zoeken naar een oplossing” in de praktijk kan worden geïmplementeerd aan de hand van het voorbeeld van een transportprobleem.

Een voorbeeld van het oplossen van een transportprobleem in Excel

Om duidelijk aan te tonen hoe u transportproblemen in de praktijk in Excel kunt oplossen, laten we een voorbeeld geven.

Voorwaarden taken

Stel we hebben 6 verkopers en 7 kopers. Vraag en aanbod tussen hen zijn respectievelijk op de volgende manier verdeeld: 36, 51, 32, 44, 35 en 38 eenheden zijn verkopers en 33, 48, 30, 36, 33, 24 en 32 eenheden zijn kopers. Als je al deze waarden bij elkaar optelt, zie je dat vraag en aanbod in evenwicht zijn. Daarom is dit probleem van een gesloten type, dat heel eenvoudig wordt opgelost.

Daarnaast hebben we informatie over hoeveel u moet uitgeven aan vervoer van punt A naar punt B (in het voorbeeld zijn ze gemarkeerd in gele cellen).

Oplossing - stap voor stap algoritme

Nu, nadat we ons vertrouwd hebben gemaakt met de tabellen met de initiële gegevens, kunnen we het volgende algoritme gebruiken om dit probleem op te lossen:

1. Eerst maken we een tabel bestaande uit 6 rijen en 7 kolommen.
2. Daarna gaan we naar elke cel die geen waarden bevat en tegelijkertijd buiten de nieuw gemaakte tabel ligt en de functie invoegt. Klik hiervoor op de fx-knop, die zich links van de functie-invoerregel bevindt.
3. We hebben een venster waarin we de categorie "Wiskunde" moeten selecteren. In welke functie zijn we geïnteresseerd? Degene die is gemarkeerd in deze schermafbeelding. Functie SUMPRODUCT vermenigvuldigt bereiken of arrays onderling en telt ze op. Net wat we nodig hebben. Druk daarna op de OK-toets.
4. Vervolgens verschijnt er een venster op het scherm waarin u de functieparameters moet specificeren. Het zijn de volgende:
   1. Array 1. Dit is het eerste argument waarin we het bereik schrijven dat geel is gemarkeerd. U kunt de functieparameters instellen met behulp van het toetsenbord of door het juiste gebied te selecteren met de linkermuisknop.
   2. Array 2. Dit is het tweede argument, de nieuw gemaakte tabel. Acties worden op dezelfde manier uitgevoerd.

Bevestig uw actie door op de OK-knop te drukken.

1. Daarna klikken we met de linkermuisknop op de cel die dient als de linkerbovenhoek in de nieuw gemaakte tabel. Klik nu opnieuw op de functieknop invoegen.
2. We selecteren dezelfde categorie als in het vorige geval. Maar deze keer zijn we geïnteresseerd in de functie SOM.
3. Nu komt het stadium van het invullen van de argumenten. Als eerste argument schrijven we de bovenste rij van de tabel die we aan het begin hebben gemaakt. Op dezelfde manier als voorheen kan dit worden gedaan door deze cellen op het blad te selecteren, of handmatig. We bevestigen onze acties door op de OK-knop te drukken.
4. We zullen de resultaten zien in de cel met de functie. In dit geval is het nul. Verplaats vervolgens de cursor naar de rechter benedenhoek, waarna een autocomplete-markering verschijnt. Het ziet eruit als een klein zwart pluche. Als het verschijnt, houdt u de linkermuisknop ingedrukt en verplaatst u de cursor naar de laatste cel in onze tabel.
5. Dit geeft ons de mogelijkheid om de formule over te dragen naar alle andere cellen en de juiste resultaten te krijgen zonder extra berekeningen uit te voeren.
6. De volgende stap is om de cel linksboven te selecteren en de functie te plakken SOM in haar. Daarna voeren we de argumenten in en gebruiken we de autocomplete-markering om alle resterende cellen in te vullen.
7. Daarna gaan we direct verder met het oplossen van het probleem. Om dit te doen, gebruiken we de add-on die we eerder hebben toegevoegd. Ga naar het tabblad "Gegevens" en daar vinden we de tool "Zoeken naar een oplossing". Wij klikken op deze knop.
8. Nu is er een venster voor onze ogen verschenen, waardoor u de parameters van onze add-on kunt configureren. Laten we elk van deze opties eens bekijken:
   1. Optimaliseer de doelfunctie. Hier moeten we de cel selecteren die de functie bevat SUMPRODUCT. We zien dat deze optie het mogelijk maakt om een ​​functie te selecteren waarvoor naar een oplossing wordt gezocht.
   2. Voordat. Hier stellen we de optie “Minimum” in.
   3. Door de cellen van de variabelen te veranderen. Hier geven we het bereik aan dat overeenkomt met de tabel die we helemaal aan het begin hebben gemaakt (met uitzondering van de samenvattende rij en kolom).
   4. Onder voorbehoud van beperkingen. Hier moeten we beperkingen toevoegen door op de knop Toevoegen te klikken.
   5. We onthouden wat voor soort beperking we moeten creëren - de som van de waarden van de eisen van kopers en aanbiedingen van verkopers moet hetzelfde zijn.
9. De taak van beperkingen wordt als volgt uitgevoerd:
   1. Link naar cellen. Hier voeren we het bereik van de tabel in voor berekeningen.
   2. Voorwaarden. Dit is een wiskundige bewerking waarmee het in het eerste invoerveld gespecificeerde bereik wordt gecontroleerd.
   3. De waarde van de voorwaarde of beperking. Hier voeren we de juiste kolom in de brontabel in.
   4. Nadat alle stappen zijn voltooid, klikt u op de knop OK en bevestigt u onze acties.

We voeren precies dezelfde bewerkingen uit voor de bovenste rijen, waarbij we de volgende voorwaarde stellen: ze moeten gelijk zijn.

De volgende stap is het stellen van de voorwaarden. We moeten de volgende criteria instellen voor de som van de cellen in de tabel - groter dan of gelijk aan nul, een geheel getal. Daardoor hebben we zo'n lijst met voorwaarden waaronder het probleem wordt opgelost. Hier moet u ervoor zorgen dat het selectievakje naast de optie "Variabelen zonder limieten niet-negatief maken" is aangevinkt. In onze situatie is het ook vereist dat de methode voor het oplossen van het probleem wordt gekozen - "Zoeken naar een oplossing voor niet-lineaire problemen van OPG-methoden". Nu kunnen we gerust zeggen dat de instelling klaar is. Daarom blijft het alleen om de berekeningen uit te voeren. Klik hiervoor op de knop "Zoek een oplossing".

Daarna worden alle gegevens automatisch berekend en toont Excel een venster met de resultaten. Het is noodzakelijk om de werking van de computer dubbel te controleren, aangezien fouten mogelijk zijn als de voorwaarden eerder verkeerd waren ingesteld. Als alles correct is, klikt u op de knop "OK" en ziet u de voltooide tabel.

Als blijkt dat onze taak een open type is geworden, dan is dit slecht, omdat je de brontabel moet bewerken zodat de taak een gesloten wordt. Wanneer dit echter is gebeurd, zal het resterende algoritme hetzelfde zijn.

Conclusie

Zoals u ziet, kan Excel ook worden gebruikt voor zeer complexe berekeningen, die op het eerste gezicht niet beschikbaar zijn voor een eenvoudig computerprogramma dat bij bijna iedereen is geïnstalleerd. Hoe het ook is. Vandaag hebben we het geavanceerde gebruiksniveau al behandeld. Dit onderwerp is niet zo eenvoudig, maar zoals ze zeggen, de weg zal worden beheerst door de lopende. Het belangrijkste is om het actieplan te volgen en alle hierboven aangegeven acties nauwkeurig uit te voeren. Dan zijn er geen fouten en voert het programma zelfstandig alle benodigde berekeningen uit. U hoeft niet na te denken over welke functie u moet gebruiken enzovoort.