Contenuti
“Cerca una soluzione” è un componente aggiuntivo di Excel, attraverso il quale è possibile scegliere la migliore soluzione ai problemi in base alle restrizioni specificate. La funzione consente di programmare i dipendenti, distribuire costi o investimenti. Sapere come funziona questa funzione ti farà risparmiare tempo e fatica.
Cos'è la ricerca di soluzioni
In combinazione con varie altre opzioni in Excel, esiste una funzione meno popolare, ma estremamente necessaria "Cerca una soluzione". Nonostante trovarlo non sia facile, conoscerlo e utilizzarlo aiuta a risolvere molti problemi. L'opzione elabora i dati e fornisce la soluzione ottimale tra quelle consentite. Nell'articolo viene descritto come funziona direttamente la ricerca di una soluzione.
Come attivare la funzione "Cerca una soluzione".
Nonostante l'efficacia, l'opzione in questione non è in una posizione prominente nella barra degli strumenti o nel menu contestuale. La maggior parte degli utenti che lavorano in Excel non sono a conoscenza della sua presenza. Per impostazione predefinita, questa funzione è disabilitata, per visualizzarla eseguire le seguenti azioni:
- Aprire il "File" facendo clic sul nome appropriato.
- Fare clic sulla sezione "Impostazioni".
- Quindi seleziona la sottosezione "Componenti aggiuntivi". Tutti i componenti aggiuntivi del programma verranno visualizzati qui, la scritta "Gestione" apparirà di seguito. Sul lato destro ci sarà un menu a comparsa in cui dovresti selezionare "Componenti aggiuntivi di Excel". Quindi fare clic su "Vai".
1 - Sul monitor verrà visualizzata una finestra aggiuntiva "Componenti aggiuntivi". Selezionare la casella accanto alla funzione desiderata e fare clic su OK.
- La funzione desiderata apparirà sulla barra multifunzione a destra della sezione "Dati".
Informazioni sui modelli
Queste informazioni saranno estremamente utili per coloro che stanno appena familiarizzando con il concetto di "modello di ottimizzazione". Prima di utilizzare la "Cerca una soluzione", si consiglia di studiare i materiali sui metodi di costruzione dei modelli:
- l'opzione in esame consentirà di individuare la modalità migliore per stanziare fondi per investimenti, carico dei locali, fornitura di beni o altre azioni ove sia necessario trovare la soluzione migliore.
- Il "metodo ottimale" in una situazione del genere significherebbe: aumentare il reddito, ridurre i costi, migliorare la qualità, ecc.
Tipiche attività di ottimizzazione:
- Determinazione di un piano di produzione, durante il quale il profitto dalla vendita delle merci rilasciate sarà massimo.
- Determinazione delle mappe di trasporto, durante le quali i costi di trasporto sono ridotti al minimo.
- Cerca la distribuzione di più macchine per vari tipi di lavoro, in modo da ridurre i costi di produzione.
- Determinazione del tempo più breve per il completamento dei lavori.
Importante! Per formalizzare l'attività, è necessario creare un modello che rifletta i parametri principali dell'area disciplinare. In Excel, un modello è un insieme di formule che utilizzano variabili. L'opzione considerata cerca tali indicatori che la funzione obiettivo sia maggiore (minore) o uguale al valore specificato.
Fase preparatoria
Prima di posizionare una funzione sulla barra multifunzione, è necessario comprendere come funziona l'opzione. Ad esempio, ci sono informazioni sulla vendita di merci indicate nella tabella. Il compito è assegnare uno sconto per ogni articolo, che sarebbe di 4.5 milioni di rubli. Il parametro viene visualizzato all'interno di una cella denominata target. Sulla base di esso, vengono calcolati altri parametri.
Il nostro compito sarà calcolare lo sconto per il quale si moltiplicano le somme per la vendita dei vari prodotti. Questi 2 elementi sono collegati da una formula scritta in questo modo: =D13*$SOL$2. Dove in D13 viene scritta la quantità totale per l'implementazione e $G$2 è l'indirizzo dell'elemento desiderato.
Utilizzo della funzione e configurazione
Quando la formula è pronta, è necessario utilizzare direttamente la funzione stessa:
- Devi passare alla sezione "Dati" e fare clic su "Cerca una soluzione".
- Si apriranno le “Opzioni”, dove sono impostate le impostazioni richieste. Nella riga "Ottimizza la funzione obiettivo:" è necessario specificare la cella in cui viene visualizzata la somma degli sconti. È possibile prescrivere le coordinate da soli o scegliere dal documento.
- Successivamente, è necessario accedere alle impostazioni di altri parametri. Nella sezione “A:” è possibile impostare i limiti massimo e minimo o un numero esatto.
- Quindi viene compilato il campo "Modifica dei valori delle variabili:". Qui vengono inseriti i dati della cella desiderata, che contiene un valore specifico. Le coordinate vengono registrate in modo indipendente o si fa clic sulla cella corrispondente nel documento.
- Quindi viene modificata la scheda "Secondo le restrizioni:", dove vengono impostate le restrizioni sui dati applicati. Ad esempio, sono escluse le frazioni decimali o i numeri negativi.
- Successivamente, si apre una finestra che consente di aggiungere restrizioni nei calcoli. La riga iniziale contiene le coordinate di una cella o di un intero intervallo. A seguito delle condizioni dell'attività, vengono indicati i dati della cella desiderata, dove viene visualizzato l'indicatore di sconto. Quindi viene determinato il segno di confronto. È impostato su "maggiore o uguale a" in modo che il valore finale non sia con un segno meno. Il "Limite" impostato nella riga 3 è 0 in questa situazione. È anche possibile impostare un limite con “Aggiungi”. I prossimi passi sono gli stessi.
- Una volta completati i passaggi precedenti, il limite impostato viene visualizzato nella riga più grande. L'elenco può essere ampio e dipenderà dalla complessità dei calcoli, tuttavia, in una situazione particolare, 1 condizione è sufficiente.
- Inoltre, è possibile selezionare altre impostazioni avanzate. In basso a destra c'è un'opzione "Opzioni" che ti consente di farlo.
- Nelle impostazioni è possibile impostare "Precisione di limitazione" e "Limiti di soluzione". Nella nostra situazione, non è necessario utilizzare queste opzioni.
- Al termine delle impostazioni, la funzione stessa si avvia: fai clic su "Trova una soluzione".
- Dopo che il programma esegue i calcoli richiesti ed emette i calcoli finali nelle celle richieste. Quindi si apre una finestra con i risultati, dove i risultati vengono salvati/cancellati, oppure i parametri di ricerca vengono configurati secondo uno nuovo. Quando i dati soddisfano i requisiti, la soluzione trovata viene salvata. Se si seleziona in anticipo la casella "Ritorna alla finestra di dialogo delle opzioni di ricerca della soluzione", si aprirà una finestra con le impostazioni delle funzioni.
- Esiste la possibilità che i calcoli si siano rivelati errati o sia necessario modificare i dati iniziali per ottenere altri indicatori. In una situazione del genere, è necessario riaprire la finestra delle impostazioni e ricontrollare le informazioni.
- Quando i dati sono accurati, è possibile utilizzare un metodo alternativo. A tal fine, è necessario fare clic sull'opzione corrente e selezionare il metodo più adatto dall'elenco che appare:
- Trovare una soluzione utilizzando un gradiente generalizzato per problemi non lineari. Per impostazione predefinita, viene utilizzata questa opzione, ma è possibile utilizzarne altre.
- Trovare soluzioni per problemi lineari basati sul metodo del simplesso.
- Utilizzo della ricerca evolutiva per completare un compito.
Attenzione! Quando le opzioni precedenti non sono riuscite a far fronte all'attività, è necessario controllare nuovamente i dati nelle impostazioni, poiché questo è spesso l'errore principale in tali attività.
- Quando viene ricevuto lo sconto desiderato, resta da applicarlo per calcolare l'importo degli sconti per ciascun articolo. A tale scopo si evidenzia l'elemento iniziale della colonna “Importo Sconto”, si scrive la formula «=Re2*$Sol$2» e premere “Invio”. I segni del dollaro vengono abbassati in modo che quando la formula viene estesa alle linee adiacenti, G2 non cambia.
- L'importo dello sconto per l'articolo iniziale sarà ora ottenuto. Quindi dovresti spostare il cursore sull'angolo della cella, quando diventa un "più", viene premuto LMB e la formula viene allungata alle linee richieste.
- Dopodiché, la tavola sarà finalmente pronta.
Carica/Salva opzioni di ricerca
Questa opzione è utile quando si applicano varie opzioni di vincolo.
- Nel menu Opzioni di Solution Finder, fai clic su Carica/Salva.
- Immettere l'intervallo per l'area del modello e fare clic su Salva o Carica.
Quando si salva il modello, viene inserito un riferimento a 1 cella di una colonna vuota in cui verrà posizionato il modello di ottimizzazione. Durante il caricamento del modello, viene inserito un riferimento all'intero intervallo che contiene il modello di ottimizzazione.
Importante! Per salvare le ultime impostazioni nel menu Opzioni soluzione, viene salvata una cartella di lavoro. Ogni foglio al suo interno ha le proprie opzioni di aggiunta del Risolutore. Inoltre, è possibile impostare più di 1 attività per foglio facendo clic sul pulsante "Carica o salva" per salvare le singole attività.
Un semplice esempio di utilizzo del Risolutore
È necessario caricare il contenitore con contenitori in modo che la sua massa sia massima. La cisterna ha un volume di 32 mc. m. Una scatola piena ha un peso di 20 kg, il suo volume è di 0,15 metri cubi. m. Scatola – 80 kg e 0,5 cu. m. È necessario che il numero totale di contenitori sia di almeno 110 pezzi. I dati sono così organizzati:
Le variabili del modello sono contrassegnate in verde. La funzione obiettivo è evidenziata in rosso. Restrizioni: dal minor numero di contenitori (maggiore o uguale a 110) e dal peso (=SOMMAPRODOTTO(B8:C8,B6:C6) – tara totale nel contenitore.
Per analogia, consideriamo il volume totale: =SOMMAPRODOTTO(B7:C7,B8:C8). Tale formula è necessaria per fissare un limite al volume totale dei contenitori. Quindi, attraverso la “Cerca una soluzione”, si inseriscono link ad elementi con variabili, formule e gli indicatori stessi (o link a celle specifiche). Naturalmente, il numero di contenitori è un numero intero (è anche una limitazione). Premiamo "Trova una soluzione", per cui troviamo un tale numero di contenitori quando la massa totale è massima e vengono prese in considerazione tutte le restrizioni.
La ricerca della soluzione non è riuscita a trovare le soluzioni
Tale notifica compare quando la funzione in questione non ha trovato combinazioni di punteggi variabili che soddisfino ciascun vincolo. Quando si utilizza il metodo Simplex, è del tutto possibile che non ci sia una soluzione.
Quando si utilizza un metodo per la risoluzione di problemi non lineari, partendo in tutti i casi dagli indicatori iniziali delle variabili, ciò indica che la possibile soluzione è lontana da tali parametri. Se si esegue la funzione con altri indicatori iniziali delle variabili, probabilmente c'è una soluzione.
Ad esempio, quando si utilizza il metodo non lineare, gli elementi della tabella con le variabili non sono stati riempiti e la funzione non ha trovato soluzioni. Questo non significa che non ci sia una soluzione. Ora, tenendo conto dei risultati di una certa valutazione, negli elementi vengono inseriti altri dati con variabili vicine a quelle ricevute.
In ogni situazione, dovresti inizialmente esaminare il modello per l'assenza di un conflitto di vincoli. Spesso, questo è interconnesso con una selezione impropria del rapporto o dell'indicatore limitante.
Nell'esempio sopra, l'indicatore del volume massimo è di 16 metri cubi. m anziché 32, perché tale restrizione contraddice gli indicatori per il numero minimo di seggi, poiché corrisponderà al numero di 16,5 metri cubi. m.
Conclusione
Sulla base di ciò, l'opzione "Cerca una soluzione" in Excel aiuterà a risolvere problemi specifici che sono piuttosto difficili o impossibili da risolvere nei soliti modi. La difficoltà nell'applicare questo metodo è che inizialmente questa opzione è nascosta, motivo per cui la maggior parte degli utenti non è a conoscenza della sua presenza. Inoltre, la funzione è piuttosto difficile da apprendere e utilizzare, ma con una ricerca adeguata porterà grandi vantaggi e faciliterà i calcoli.