Attività di trasporto in Excel. Trovare il miglior metodo di trasporto dal venditore all'acquirente

Excel è un programma molto funzionale. Può essere utilizzato per risolvere un enorme livello di problemi che si deve affrontare negli affari. Uno dei più comuni è il trasporto. Immagina di dover capire quale metodo di trasporto dal produttore all'acquirente finale è il più ottimale in termini di tempo, denaro e altre risorse. Questo problema è abbastanza popolare, indipendentemente dal settore in cui si trova l'azienda. Pertanto, diamo un'occhiata più da vicino a come implementarlo utilizzando Excel.

Descrizione dell'attività di trasporto

Quindi, abbiamo due controparti che interagiscono costantemente tra loro. Nel nostro caso, questo è un acquirente e un venditore. Dobbiamo capire come trasportare le merci in modo tale che i costi siano minimi. Per fare ciò, è necessario presentare tutti i dati in forma schematica o matriciale. In Excel, utilizziamo quest'ultima opzione. In generale, ci sono due tipi di attività di trasporto:

1. Chiuso. In questo caso, domanda e offerta sono in equilibrio.
2. Aprire. Non c'è uguaglianza tra domanda e offerta qui. Per ottenere una soluzione a questo problema, devi prima portarlo al primo tipo, equalizzando domanda e offerta. Per fare ciò, è necessario introdurre un indicatore aggiuntivo: la presenza di un acquirente o venditore condizionato. Inoltre, è necessario apportare alcune modifiche alla tabella dei costi.

Come abilitare la funzione Trova soluzione in Excel

Per risolvere i problemi di trasporto in Excel, esiste una funzione speciale chiamata "Cerca una soluzione". Non è abilitato per impostazione predefinita, quindi è necessario eseguire i seguenti passaggi:

1. Apri il menu "File", che si trova nell'angolo in alto a sinistra della finestra del programma.
2. Successivamente, fare clic sul pulsante con i parametri.
3. Successivamente, troviamo la sottosezione "Impostazioni" e andiamo al menu di gestione dei componenti aggiuntivi. Questi sono piccoli programmi che vengono eseguiti all'interno dell'ambiente Microsoft Excel. Vediamo che dapprima abbiamo cliccato sul menu "Componenti aggiuntivi", quindi nella parte in basso a destra abbiamo impostato la voce "Componenti aggiuntivi di Excel" e fatto clic sul pulsante "Vai". Tutte le azioni necessarie sono evidenziate con rettangoli rossi e frecce.
4. Successivamente, attiva il componente aggiuntivo "Cerca una soluzione", dopodiché confermiamo le nostre azioni premendo il pulsante OK. Sulla base della descrizione dell'ambiente, possiamo vedere che è progettato per analizzare dati complessi, come scientifici e finanziari.
5. Successivamente, vai alla scheda "Dati", dove vediamo un nuovo pulsante, chiamato come il componente aggiuntivo. Può essere trovato nel gruppo di strumenti Analisi.

Resta solo da fare clic su questo pulsante e procediamo alla soluzione del problema di trasporto. Ma prima, dovremmo parlare un po' di più dello strumento Risolutore in Excel. Si tratta di uno speciale componente aggiuntivo di Excel che consente di trovare la soluzione più rapida a un problema. Una caratteristica è la considerazione delle restrizioni che l'utente imposta in fase di preparazione. In parole povere, questa è una subroutine che consente di determinare il modo migliore per ottenere un determinato compito. Tali compiti possono includere quanto segue:

1. Investire, caricare un magazzino o qualsiasi altra attività simile. Compresa la consegna della merce.
2. Il modo migliore. Ciò include obiettivi come ottenere il massimo profitto al minimo costo, come ottenere la migliore qualità con le risorse disponibili e così via.

Oltre alle attività di trasporto, questo componente aggiuntivo viene utilizzato anche per i seguenti scopi:

1. Sviluppo di un piano di produzione. Cioè, quante unità di un prodotto devono essere prodotte per ottenere il massimo reddito.
2. Trova la distribuzione del lavoro per diversi tipi di lavoro in modo che il costo totale di produzione di un prodotto o servizio sia il più piccolo.
3. Impostare il tempo minimo necessario per completare tutto il lavoro.

Come puoi vedere, i compiti sono molto diversi. La regola universale per l'applicazione di questo add-in è che prima di risolvere il problema è necessario creare un modello che corrisponda alle caratteristiche chiave del problema posto. Un modello è una raccolta di funzioni che utilizzano variabili come argomenti. Cioè, valori che possono cambiare.

È importante notare che l'ottimizzazione di un insieme di valori viene eseguita esclusivamente su un indicatore, chiamato funzione obiettivo.

Il componente aggiuntivo Risolutore enumera i diversi valori delle variabili che vengono passate alla funzione obiettivo in modo tale che sia il massimo, il minimo o uguale a un certo valore (questo è appunto il vincolo). C'è un'altra funzione che è in qualche modo simile nel suo principio di funzionamento e che viene spesso confusa con la "Cerca una soluzione". Si chiama "Selezione delle opzioni". Ma se scavi più a fondo, la differenza tra loro è enorme:

1. La funzione Goal Seek non funziona con più di una variabile.
2. Non prevede la possibilità di fissare limiti alle variabili.
3. È in grado di determinare solo l'uguaglianza della funzione obiettivo a un certo valore, ma non consente di trovare il massimo e il minimo. Pertanto, non è adatto al nostro compito.
4. In grado di calcolare in modo efficiente solo se modello di tipo lineare. Se il modello non è lineare, trova il valore più vicino al valore originale.

L'attività di trasporto è molto più complicata nella sua struttura, quindi il componente aggiuntivo "Selezione dei parametri" non è sufficiente per questo. Diamo un'occhiata più da vicino a come implementare in pratica la funzione "Cerca una soluzione" usando l'esempio di un problema di trasporto.

Un esempio di risoluzione di un problema di trasporto in Excel

Per dimostrare chiaramente come risolvere in pratica i problemi di trasporto in Excel, facciamo un esempio.

Condizioni compiti

Supponiamo di avere 6 venditori e 7 acquirenti. La domanda e l'offerta tra di loro sono distribuite rispettivamente nel modo seguente: 36, 51, 32, 44, 35 e 38 unità sono venditori e 33, 48, 30, 36, 33, 24 e 32 unità sono acquirenti. Se sommi tutti questi valori, scoprirai che domanda e offerta sono in equilibrio. Pertanto, questo problema è di tipo chiuso, che viene risolto in modo molto semplice.

Inoltre, abbiamo informazioni su quanto devi spendere per il trasporto dal punto A al punto B (sono evidenziati nelle celle gialle nell'esempio).

Soluzione: algoritmo passo dopo passo

Ora, dopo aver familiarizzato con le tabelle con i dati iniziali, possiamo utilizzare il seguente algoritmo per risolvere questo problema:

1. Innanzitutto, creiamo una tabella composta da 6 righe e 7 colonne.
2. Successivamente, andiamo in qualsiasi cella che non contenga alcun valore e allo stesso tempo si trova all'esterno della tabella appena creata e inseriamo la funzione. Per fare ciò, fare clic sul pulsante fx, che si trova a sinistra della riga di immissione della funzione.
3. Abbiamo una finestra in cui dobbiamo selezionare la categoria "Matematica". Quale funzione ci interessa? Quello evidenziato in questo screenshot. Funzione MATR.SOMMA.PRODOTTO moltiplica gli intervalli o gli array tra loro e li somma. Proprio quello di cui abbiamo bisogno. Successivamente, premere il tasto OK.
4. Successivamente, sullo schermo apparirà una finestra in cui è necessario specificare i parametri della funzione. Sono i seguenti:
   1. Array 1. Questo è il primo argomento in cui scriviamo l'intervallo evidenziato in giallo. È possibile impostare i parametri della funzione sia utilizzando la tastiera che selezionando l'apposita area con il tasto sinistro del mouse.
   2. Array 2. Questo è il secondo argomento, ovvero la tabella appena creata. Le azioni vengono eseguite allo stesso modo.

Conferma la tua azione premendo il pulsante OK.

1. Successivamente, facciamo clic con il pulsante sinistro del mouse sulla cella che funge da in alto a sinistra nella tabella appena creata. Ora fai nuovamente clic sul pulsante della funzione di inserimento.
2. Selezioniamo la stessa categoria del caso precedente. Ma questa volta ci interessa la funzione SUM.
3. Ora arriva la fase di compilazione degli argomenti. Come primo argomento, scriviamo la riga superiore della tabella che abbiamo creato all'inizio. Allo stesso modo di prima, questo può essere fatto selezionando queste celle sul foglio o manualmente. Confermiamo le nostre azioni premendo il pulsante OK.
4. Vedremo i risultati nella cella con la funzione. In questo caso è zero. Quindi, sposta il cursore nell'angolo in basso a destra, dopodiché apparirà un indicatore di completamento automatico. Sembra un piccolo peluche nero. Se appare, tieni premuto il tasto sinistro del mouse e sposta il cursore sull'ultima cella della nostra tabella.
5. Questo ci dà l'opportunità di trasferire la formula a tutte le altre celle e ottenere i risultati corretti senza dover eseguire calcoli aggiuntivi.
6. Il passaggio successivo consiste nel selezionare la cella in alto a sinistra e incollare la funzione SUM dentro di lei. Successivamente, inseriamo gli argomenti e utilizziamo l'indicatore di completamento automatico per riempire tutte le celle rimanenti.
7. Successivamente, procediamo direttamente alla risoluzione del problema. Per fare ciò, utilizzeremo il componente aggiuntivo che abbiamo incluso in precedenza. Vai alla scheda "Dati" e lì troviamo lo strumento "Cerca una soluzione". Facciamo clic su questo pulsante.
8. Ora è apparsa davanti ai nostri occhi una finestra, attraverso la quale è possibile configurare i parametri del nostro add-on. Diamo un'occhiata a ciascuna di queste opzioni:
   1. Ottimizza la funzione obiettivo. Qui dobbiamo selezionare la cella contenente la funzione MATR.SOMMA.PRODOTTO. Vediamo che questa opzione consente di selezionare una funzione per la quale verrà cercata una soluzione.
   2. Prima. Qui impostiamo l'opzione "Minimo".
   3. Modificando le celle delle variabili. Qui indichiamo l'intervallo corrispondente alla tabella che abbiamo creato all'inizio (ad eccezione della riga e della colonna di riepilogo).
   4. Soggetto a restrizioni. Qui è necessario aggiungere vincoli facendo clic sul pulsante Aggiungi.
   5. Ricordiamo che tipo di vincolo dobbiamo creare: la somma dei valori delle richieste degli acquirenti e delle offerte dei venditori deve essere la stessa.
9. Il compito di restrizione è svolto come segue:
   1. Collegamento alle celle. Qui entriamo nell'intervallo della tabella per i calcoli.
   2. Termini. Questa è un'operazione matematica rispetto alla quale viene verificato l'intervallo specificato nel primo campo di input.
   3. Il valore della condizione o del vincolo. Qui inseriamo la colonna appropriata nella tabella di origine.
   4. Dopo aver completato tutti i passaggi, fai clic sul pulsante OK, confermando così le nostre azioni.

Eseguiamo esattamente le stesse operazioni per le righe superiori, impostando la seguente condizione: devono essere uguali.

Il prossimo passo è impostare le condizioni. Abbiamo bisogno di impostare i seguenti criteri per la somma delle celle nella tabella: maggiore o uguale a zero, un numero intero. Di conseguenza, abbiamo un tale elenco di condizioni in cui il problema viene risolto. Qui devi assicurarti che la casella di controllo accanto all'opzione "Rendi variabili senza limiti non negative" sia selezionata. Inoltre, nella nostra situazione, è necessario che venga scelto il metodo per risolvere il problema: "Ricerca di una soluzione ai problemi non lineari dei metodi OPG". Ora possiamo tranquillamente dire che l'impostazione è fatta. Pertanto, resta solo da eseguire i calcoli. Per fare ciò, fai clic sul pulsante "Trova una soluzione".

Successivamente, tutti i dati verranno calcolati automaticamente, quindi Excel mostrerà una finestra con i risultati. È necessario per ricontrollare il funzionamento del computer, poiché sono possibili errori se le condizioni sono state precedentemente impostate in modo errato. Se tutto è corretto, fai clic sul pulsante "OK" e guarda la tabella finita.

Se si scopre che la nostra attività è diventata di tipo aperto, questo è negativo, perché è necessario modificare la tabella di origine in modo che l'attività diventi chiusa. Tuttavia, una volta eseguita questa operazione, l'algoritmo rimanente sarà lo stesso.

Conclusione

Come puoi vedere, Excel può essere utilizzato anche per calcoli molto complessi, che a prima vista non sono disponibili per un semplice programma per computer che è installato in quasi tutti. Tuttavia, lo è. Oggi abbiamo già trattato il livello avanzato di utilizzo. Questo argomento non è così semplice, ma come si suol dire, la strada sarà dominata da quella che cammina. La cosa principale è seguire il piano d'azione ed eseguire accuratamente tutte le azioni sopra indicate. Quindi non ci saranno errori e il programma eseguirà autonomamente tutti i calcoli necessari. Non sarà necessario pensare a quale funzione utilizzare e così via.