Configurazione Windows Server 2016 con Dominio, DNS e DHCP [per esordienti totali]

Vediamo come configurare Windows Server 2016 impostando il server come controller di Dominio (Active Directory) e configurando DNS e DHCP. Gli argomenti trattati in questo articolo saranno i seguenti:

  1. Configurazione di VirtualBox
  2. Installazione di Windows Server 2016
  3. Configurazione DHCP
  4. Configurazione del DNS
  5. Configurazione Dominio su Active Directory

Per questa guida utilizzerò una macchina virtuale configurata con VirtualBox. Prima di procedere assicuriamoci quindi di disporre di:

  1. VirtualBox installato sul computer (se vogliamo replicare l’operazione su una macchina virtuale)
  2. Un disco di installazione di Windows Server 2016

Le modalità di configurazione, a esclusione della macchina virtuale ovviamente, sono valide per qualunque Windows Server 2016.

1. Configurazione di VirtualBox

Anzitutto creiamo una nuova macchina virtuale. Per farlo apriamo VirtualBox e sotto la voce Macchina selezioniamo Nuova, possiamo anche premere CTRL+N

Diamo un nome alla nostra macchina e selezioniamo, alla voce Versione, l’opzione Windows 2016 (64-bit):

Assegniamo 4096MB di RAM (ne sarebbero sufficienti 2048, dipende da quante risorse ha la nostra macchina host) e premiamo su successivo:

Lasciamo spuntata la voce Crea subito un nuovo disco fisso virtuale:

E come tipo di disco, alla pagina successiva, scegliamo VDI (VirtualBox Disk Image):

Scegliamo di allocare lo spazio dinamicamente e procediamo oltre:

Possiamo lasciare come dimensione predefinita 50GB:

A questo punto la macchina virtuale è pronta per essere avviata. Nell’elenco delle macchine disponibili dovremmo vedere qualcosa del genere:

Clicchiamo sul tasto Avvia. Ci verrà chiesto di selezionare il dispositivo per l’installazione, scegliamo il disco o l’ISO di installazione per Windows Server 2016.

2. Installazione di Windows Server 2016

Anzitutto ci verrà chiesta la lingua in cui vogliamo configurare Windows, io lascerò tutto in italiano nel modo seguente:

Premiamo Avanti. A questo punto scegliamo Installa.

Immettiamo il codice seriale se lo possediamo, altrimenti scegliamo Non ho un codice Product Key per installare la versione di prova.

Scegliamo a questo punto la versione da installare (questa opzione dipende anche dalla versione del disco di installazione che stiamo utilizzando). Io installerò la versione Windows Server 2016 Standard (Esperienza desktop). ATTENZIONE! Installando la versione senza Esperienza desktop, si avrà accesso al sistema operativo solo da PowerShell.

Premiamo avanti e accettiamo le condizioni di licenza.

Procediamo avanti e ci verrà chiesto il tipo di installazione che intendiamo fare. Scegliamo Personalizzata: installa solo Windows (opzione avanzata).

Scegliamo il disco sul quale intendiamo effettuare l’installazione. Nel mio caso ho un unico disco e posso utilizzarlo per intero (il disco virtuale da 50GB creato all’inizio).

A questo punto comincerà la copia dei file per l’installazione, aspettiamo.

Finita l’installazione Windows si riavvierà da solo e arriverà a chiederci una password per Administrator.

Fatto tutto questo si arriverà alla schermata di accesso.

Per usare ALT+CTRL+CANC dentro a VirtualBox premiamo i tasti CTRL (destro) + CANC. Inseriamo la password e premiamo Invio.

Lasciamo avviare Windows, mentre verrà aperto il Server Manager. Ci verrà chiesto se attivare l’individuazione della rete, diciamo di .

Nel caso ci siano dei servizi non avviati sotto il Server Locale, clicchiamo sulla voce Servizi.

Clicchiamo col destro su ciascun servizio in arresto e scegliamo l’opzione Avvia servizi.

Se tutti i servizi ripartono arriveremo ad una situazione come la seguente.

Potrebbe capitare comunque, a seguito di installazioni successive, che alcuni servizi non si avviino o tendano a disattivarsi automaticamente. Di solito si tratta di servizi secondari e non essenziali all’operatività del sistema operativo, in tal caso possiamo anche ignorarli.

3. Configurazione DHCP

Procediamo adesso a configurare sul server il servizio di DHCP, in modo che il nostro server si occupi dell’assegnazione degli indirizzi all’interno della rete. Questo servizio andrà poi disabilitato sul router, per evitare conflitti tra i due. Per chi volesse approfondire il funzionamento del DHCP rimando alla pagina dedicata all’argomento su Wikipedia:

In telecomunicazioni e informatica il Dynamic Host Configuration Protocol (DHCP) (protocollo di configurazione IP dinamica) è un protocollo di rete di livello applicativo che permette ai dispositivi o terminali di una certa rete locale di ricevere automaticamente ad ogni richiesta di accesso a una rete IP. Fonte: Wikipedia – DHCP

Apriamo dunque il Server Manager e andiamo su Gestione > Aggiungi ruoli e funzionalità

Andiamo su Ruoli Server e spuntiamo la voce Server DHCP. Clicchiamo su Aggiungi funzionalità.

Si verrà avvisati che dobbiamo assegnare un indirizzo IP statico al Server.

Premiamo su Annulla e premiamo WIN + R per aprire la finestra Esegui. Digitiamo CMD.

Nel prompt dei comandi digitiamo ipconfig. Scopriamo così i parametri della rete sulla quale si trova il server. Nel mio caso questa rete dipende dalla macchina virtuale di VirtualBox e appare configurata nel modo seguente:

A questo punto apriamo il Pannello di controllo dal menu Start.

Andiamo su Rete e Internet > Centro connessioni di rete e condivisione > Modifica impostazioni scheda. Individuiamo la nostra scheda di rete e clicchiamoci sopra col destro, andiamo su Proprietà.

Selezioniamo Protocollo Internet versione 4 (TCP/IPv4) e poi scegliamo Proprietà. Configuriamo i parametri nel modo seguente (ovviamente si dovranno mettere i parametri rispetto alla rete che si intende configurare):

Questo significa che ho posizionato il server all’indirizzo 10.0.2.100. Questa è una scelta assolutamente arbitraria. Dal momento che ci troviamo all’interno della rete 10.0.0.0/8 abbiamo a disposizione 16.777.214 di host assegnabili. Per valutare i parametri della rete rimando all’IP Calculator.

Premiamo OK. Una volta tornati sulla schermata con tutte le reti disabilitiamo e riabilitiamo la rete, cliccandoci sopra col destro e scegliendo l’opportuna voce.

Torniamo sul Server Manager e riprendiamo la configurazione del DHCP. Se l’errore dovesse ripresentarsi premiamo su Continua e ignoriamolo (ormai sappiamo di aver impostato un indirizzo statico). Clicchiamo su Avanti.

Alla voce Funzionalità clicchiamo ancora Avanti. Qui non selezioniamo alcunché.

Sotto la voce Server DHCP clicchiamo su Avanti.

Alla Conferma spuntiamo la voce Riavvia automaticamente il server di destinazione se necessario. Premiamo Installa.

Attendiamo la fine dell’installazione. Una volta conclusa clicchiamo su Completa configurazione DHCP.

Nella schermata successiva clicchiamo su Commit.

Se tutto è andato bene vedremo una schermata come la seguente. Clicchiamo su Chiudi.

Adesso sul Server Manager spostiamoci su Strumenti > DHCP.

Si aprirà una schermata come la seguente. Clicchiamo col destro sul nome del server e poi scegliamo Aggiungi/Rimuovi binding.

Nella schermata successiva confermiamo la scheda di rete interessata.

Adesso espandiamo la voce sotto al server e clicchiamo col destro su IPv4, dopodiché scegliamo Nuovo ambito…

Premiamo Avanti.

Diamo un nome all’ambito, tipo Generale. Il nome è a piacere. Poi premiamo su Avanti.

Configuriamo l’indirizzo IP iniziale e finale. Ricordiamoci i parametri di cui parlavamo prima. La nostra rete ha lunghezza 8 e subnet mask 255.0.0.0. Il primo indirizzo IP sarà al minimo 10.0.0.1 mentre il massimo indirizzo IP sarà 10.255.255.254. Possiamo ovviamente scegliere anche un intervallo minore. Io avrò una configurazione come la seguente:

Negli indirizzi esclusi inseriamo quello assegnato al server e al gateway. Per aggiungere un singolo indirizzo basta mettere quello iniziale e premere su Aggiungi. Altrimenti si può aggiungere un intervallo scegliendo l’indirizzo iniziale e quello finale.

Premiamo su Avanti. Scegliamo la durata del lease. Se non abbiamo particolari esigenze possiamo lasciare il valore predefinito.

Confermiamo di voler configurare le opzioni adesso.

Inseriamo ora l’indirizzo del router (gateway predefinito), nel mio caso 10.0.2.2.

Al DNS aggiungiamo quello di Google per ora 8.8.8.8

Alla voce Server WINS premiamo ancora Avanti.

Confermiamo l’attivazione dell’ambito.

Infine premiamo su Fine.

4. Configurazione del DNS

Procediamo adesso a configurare il server come server DNS. In questo modo il server si occuperà della traduzione dei nomi di dominio.

Andiamo nuovamente su Server Manager > Gestione > Aggiungi ruoli e funzionalità. Sotto la voce Ruoli server selezioniamo Server DNS.

Una volta spuntata la voce confermiamo Aggiungi funzionalità.

Premiamo Avanti fino alla voce Conferma. Selezioniamo Riavvia automaticamente il server di destinazione se necessario. Poi scegliamo Installa.

Attendiamo il completamento dell’installazione. Una volta completata premiamo su Chiudi. Adesso sempre andiamo ancora su Server Manager > Strumenti > DNS.

Si aprirà una finestra simile alla seguente.

Adesso clicchiamo col destro sul nome del nostro server e poi su Configurazione server DSN…

Scegliamo ancora Avanti.

Scegliamo Creazione di una zona di ricerca diretta e poi di nuovo Avanti.

Scegliamo Questo server gestirà la zona e premiamo ancora Avanti.

Diamo un nome alla zona, che sarà anche il nome del dominio che andremo a configurare in seguito. Nel mio caso metterò petarkaran.local. Ci possiamo mettere quello che preferiamo.

Confermiamo la creazione di un nuovo file DNS. Premiamo Avanti.

Selezioniamo Consenti aggiornamenti dinamici sicuri e non sicuri. Di nuovo Avanti.

Nella schermata successiva aggiungiamo i server DNS da cui prelevare le informazioni. Nel nostro caso utilizzerò quelli di Google: 8.8.8.8 e 8.8.4.4. Aggiungiamo anche quelli di OpenDNS: 208.67.222.222 e 208.67.220.220

Per aggiungerli è sufficiente digitare gli indirizzi e premere Invio.

Confermiamo la configurazione finale.

A questo punto dobbiamo fare solo un paio di modifiche. Anzitutto torniamo alla configurazione della scheda di rete. Impostiamo l’indirizzo del server come Server DNS preferito.

Confermiamo con OK. Adesso andiamo sul DHCP. Server Manager > Strumenti > DHCP

Sotto il nostro server, all’IPv4 sotto la voce Ambito > Opzioni Ambito individuiamo 006 Server DNS ed impostiamo l’indirizzo IP del nostro server.

Clicchiamo sopra 006 Server DNS e rimuoviamo il DNS di Google impostato prima, mettendoci l’indirizzo del nostro server. Questo sarà il valore per il Server DNS preferito per tutti i client attaccati alla rete gestita dal nostro Server.

Se abbiamo fatto tutto correttamente dovremmo arrivare ad una situazione del genere:

Premiamo OK e con questo abbiamo configurato anche il servizio DNS.

5. Configurazione Dominio su Active Directory

Infine configuriamo il dominio che gestirà il nostro server. Un dominio di Active Directory è esattamente la stessa cosa di un dominio web a cui siamo abituati comunemente. Per esempio petarkaran.it è un dominio e potrei utilizzarlo anche come dominio di AD. Inutile dire che se lo configurassi come tale, dopo dovrei provvedere ad indirizzare le chiamate sulla porta 80 e la porta 443 verso il mio server web dove si trova il sito internet, per non creare conflitti interni. In caso contrario, da dentro il dominio gestito da Windows, il sito risulterebbe irraggiungibile. Allo scopo di questo esercizio configurerò un dominio chiamato petarkaran.local. Non essendo *.local utilizzato dai siti web non rischio conflitti con la rete esterna. Inutile dire che il dominio può essere scelto in modo arbitrario e a piacere.

Prima di procedere voglio fare un altro piccolo approfondimento. Possiamo immaginare un dominio come il contenitore del nostro server, gestore del dominio, di tutti i client (gli utenti che possono accedere al dominio) e di tutti i computer (dispositivi che sono connessi al dominio). In sostanza un dominio contiene questi 3 elementi principali.

Il Server si occupa di amministrare i permessi e le relazioni tra client (utenti) e dispositivi (computer). Un gruppo di domini costituisce un albero.

A sua volta un gruppo di alberi costituisce una foresta.

Detto tutto questo cominciamo la configurazione del nostro server per elevarlo a gestore del Dominio.

Anzitutto voglio cambiare nome al mio server, chiamandolo SERVER (questo passaggio non è obbligatorio, lo faccio per motivi puramente estetici). Questo passaggio non è obbligatorio e può essere saltato.

Cliccando col tasto destro sul simbolo di Windows scegliamo Sistema.

Individuiamo il nome del computer e clicchiamo su Cambia impostazioni

Nella finestra che si aprirà scegliamo Cambia… Cambiamo il nome in SERVER.

Premiamo OK e di nuovo OK. Poi scegliamo di riavviare il server.

 

Una volta riavviato il server andiamo su Server Manager > Gestione > Aggiungi ruoli e funzionalità

Dalla sezione Ruoli server selezioniamo Servizi di dominio Active Directory.

Come al solito premiamo Avanti e configuriamo il Riavvio automatico. Premiamo infine su Installa.

Aspettiamo la conclusione dell’aggiornamento, dopodiché selezioniamo la voce Alza di livello il server a controller di dominio.

Essendo il primo dominio che configuriamo dobbiamo aggiungere una nuova foresta. Procediamo quindi nel modo seguente (il mio dominio sarà petarkaran.local):

Premiamo su Avanti. Creiamo adesso una password per le modalità di ripristino dei servizi di directory. E’ molto importante non dimenticare questa password, perché sarà necessaria in caso di necessità di ripristino della active directory. Premiamo su Avanti.

Confermiamo la creazione della Delega DNS.

Clicchiamo su Cambia… e inseriamo utente e password di amministratore.

Clicchiamo su Avanti. Confermiamo il nome di dominio NetBIOS (sono i vecchi nomi di dominio sostanzialmente). Clicchiamo su Avanti.

Alla schermata successiva clicchiamo ancora su Avanti.

Verrà mostrato il riepilogo di tutte le impostazioni, clicchiamo di nuovo su Avanti.

Se va tutto bene arriveremo ad una schermata come la seguente. L’importante è che compaia la spunta verde che Tutti i controlli dei prerequisiti sono riusciti.

Clicchiamo infine su Installa. Se tutto è andato bene il sistema verrà riavviato e al prossimo accesso apparirà una schermata simile a questa:

Faccio notare come questa volta l’amministratore non è più del SERVER ma del dominio PETARKARAN. Notiamo anche l’avviso sulla scheda di rete.

Entriamo dentro Windows e andiamo alle impostazioni della scheda di rete. Noteremo che l’indirizzo del DNS è stato spostato su 127.0.0.1. Benché funzioni anche così, facendo il loopback sull’indirizzo locale della macchina, che è anche il server DNS, per una serie di bug di Windows ci viene mostrato un errore.

Modifichiamo il server DNS preferito impostando l’indirizzo su quello del nostro SERVER, nel mio caso nel modo seguente:

Adesso andiamo su Server Manager > Strumenti > DHCP e aggiungiamo alle Opzioni ambito il parametro 015 Nome dominio DNS inserendo il nome del nostro dominio, nel modo seguente:

Premiamo OK. Se tutto è andato bene vedremo una configurazione come la seguente alla fine:

A questo punto il nostro Server è stato elevato a controller di dominio e abbiamo finito.

Vedi articolo

[excel] Generare una password casuale di lunghezza arbitraria usando il calcolo matriciale

Questa soluzione si ispira ad un precedente articolo a cui rimando per chi fosse curioso di approfondire: [excel] Come creare una password casuale di lunghezza arbitraria con un’unica formula

Il risultato che voglio ottenere sarà simile a questo:

Avendo una cella in input F2 indichiamo la lunghezza della password che desideriamo ottenere, nella cella sottostante una singola formula genera una password pseudo-casuale.

Prima di procedere oltre raccomando per password importanti l’uso di software più affidabile, con algoritmi di generazione di valori casuali superiori ad Excel, come per esempio KeePass.

Anzitutto voglio creare un set di caratteri da cui estrarre quelli della password. Immaginiamo di voler scrivere automaticamente una stringa contenente tutte le lettere maiuscole.

Come illustrato nell’articolo precedente partiamo dal presupposto di voler ricavare i caratteri da una tabella ASCII come la seguente:

Le lettere maiuscole vanno dal carattere 65 al carattere 90. Per ottenere l’intervallo di numeri desiderato usiamo:

Questo produrrà una matrice come questa:

Se volessimo trasformare ciascun numero in un carattere dovremmo usare la funzione CARATT.UNI nel modo seguente:

Il risultato sarà simile a questo:

Applichiamo ora CONCAT alla matrice così ottenuta e avremo tutti i caratteri in una singola riga.

Ripetiamo l’operazione per gli intervalli 97:122 e 33:47 e concateniamo il tutto ai numeri da 0 a 9, nella seguente unica formula:

Tutto questo produrrà una riga come la seguente:

La riga ha in tutto 77 caratteri. Adesso, dopo averla unita, vogliamo spezzarla di nuovo in ogni singolo carattere, usando, come nell’esercizio precedente, la funzione STRINGA.ESTRAI:

Infine usiamo INDICE sulla matrice così creata ed estraiamo per n volte un valore a caso tra 1 e 77. Concateniamo il tutto per ottenere una unica stringa di testo contenente la password.

Affinché la funzione CASUALE.TRA ricalcoli il valore per ogni singolo carattere desiderato, utilizziamo un piccolo trucchetto, inserendo una matrice all’interno della funzione CASUALE.TRA in questo modo:

Grazie al SE sommo sempre 0, per qualunque valore generato da RIF.RIGA(INDIRETTO("1:"&F2)) che produrrà un vettore di dimensione n quanto è la lunghezza desiderata della password.

Vedi articolo

[excel] Calcolare tutte le possibili combinazioni con ripetizione usando le matrici in Excel

Obiettivo: dato un insieme di elementi distinti n vogliamo calcolare tutte le possibili combinazioni che si possono formare con k degli n elementi dati, dove k \in \mathbb{N}

Secondo il calcolo combinatorio il numero di combinazioni con ripetizione che possiamo ottenere si calcola con la seguente formula:

C'_{(n,k)}=\left(\begin{matrix} n+k-1 \\ k \end{matrix} \right)=\frac{(n+k-1)!}{k!(n-1)!}

Con Excel vogliamo ottenere una matrice contenente tutti gli elementi, dove ogni elemento può essere testuale o numerico. A tale scopo utilizzeremo le caratteristiche sul calcolo matriciale introdotte recentemente in Office365 e le seguenti funzioni: INDICE, INDIRETTO, MATR.TRASPOSTA, RIPETI, UNICI, STRINGA.ESTRAI, FILTRO

Prima di procedere ricordiamo che UNICI e FILTRO sono per ora disponibili solo con Office365. Inoltre useremo il # (cancelletto) nella costruzione dei riferimenti, per prendere gli intervalli dinamici generati dalle matrici.

Definiamo quindi anzitutto gli elementi n e il numero di ripetizioni k in questo modo:

Nella riga 1 potremo aggiungere altri elementi a piacere, mentre in B2 inseriremo il numero di ripetizioni desiderate. Per praticità ho chiamato gli elementi A, B e C.

Cominciamo allora calcolando anzitutto il numero di elemento n nella cella B3:

Adesso vogliamo determinare il numero di disposizioni con ripetizione possibili, ovvero tutti i modi in cui possiamo disporre gli n elementi presi a gruppi di k, dove conta l’ordine.

Per chi non fosse pratico dell’argomento se abbiamo i suddetti 3 elementi, allora nelle combinazioni con ripetizione ABC e ACB sono la stessa combinazione (contengono ciascuna tutti e 3 gli elementi), mentre nelle disposizioni con ripetizione ABC e ACB sono elementi distinti.

Il numero totale di disposizioni con ripetizione sarà dato dalla formula:

D'_{(n,k)}=n^k

Che tradotta in Excel possiamo scrivere in B5 come:

Date queste informazioni calcoliamo tutti gli elementi possibili con la seguente formula in B11:

Questa genererà un risultato come il seguente:

L’idea è fondamentalmente quella di generare una matrice di riferimenti agli elementi dati in modo ordinato. Con 3 elementi presi 3 per volta avremo in tutto 27 disposizioni con ripetizione. Generiamo allora una matrice colonna con valori [1…27]. Nella prima colonna da [1…9] avremo il primo elemento, da [10…18] avremo il secondo elemento e così via. Siccome il primo elemento corrisponde a 1 e il secondo a 2, questo significa che presi i valori da [1…27] li dovremo dividere in 3 parti di 9 elementi ciascuna.

Questo lo potremmo ottenere scrivendo:

In questo modo generiamo [1…27] e dividiamo per 9, poi arrotondiamo per eccesso il risultato. Ne seguirà che, arrotondati per eccesso, 1/9 ⇒ 1, 9/9 ⇒ 1, 10/9 ⇒ 2, 18/9 ⇒ 2, 19/9 ⇒ 3, 27/9 ⇒ 3. Avremo insomma tre intervalli fatti, rispettivamente, di [1…1], [2…] e [3…3], ciascuno di 9 sottoelementi.

Nell’immagine di seguito vediamo la schematizzazione di ciò che vogliamo ottenere:

Per ciascun gruppo della prima colonna vorremo ottenere, nella seconda colonna, altri 3 sottogruppi, per ciascuno dei quali, nella terza colonna, vorremo ottenere altri 3 sottogruppi (questa volta di dimensione 1). Quindi i gruppi saranno 1, 3 e 9. Questi sono anche i valori per i quali dobbiamo sezionare gli intervalli delle colonne.

Per generare questi valori usiamo:

La matrice trasposta ci serve per metterli in riga, anziché in colonna. Dividiamo infine la prima colonna per questa riga.

Fatto questo spostiamoci in I10 e digitiamo:

In questo modo riportiamo semplicemente gli elementi scritti da B1 in poi.

In I11 calcoliamo quante volte appare ciascun elemento della matrice in B11#:

NB: grazie al cancelletto selezioniamo tutta la matrice generata da B11.

Il risultato che avremo sarà:

Nella prima riga A è presente 3 volte, gli altri due elementi 0 volte. Nella seconda riga abbiamo 2 A e 1 B, ecc.

In questo caso stiamo semplicemente usando la funzione CONTA.PIÙ.SE su righe successive, per ciascuno dei valori immessi.

A questo punto, sapendo quanti sono i singoli valori, possiamo usare la funzione RIPETI per riscriverli. Tale funzione ripete un dato valore per n volte. Quindi in P11 scriviamo:

Otterremo un risultato come il seguente:

Adesso facciamo una mossa un po’ astrusa. Siccome sappiamo che per ogni riga ci sono k elementi, ovvero 3 elementi, concateniamo tutta la matrice in una singola stringa e la spezziamo in pezzi da 3 elementi per volta. Verrà quindi una lunghissima stringa che comincerà con AAAAABAACAABABBABCAAC… e che verrà spezzata in AAA, AAB, AAC, AAB, ABB ecc.

Per farlo in W11 digitiamo:

La parte di formula con:

Genera una matrice colonna tipo:

La usiamo per spezzare la stringa in parti da 3 a partire dal 1° carattere, il 4° carattere, il 7° carattere ecc.

Il risultato che otterremo saranno tutte le disposizioni con ripetizione di cui parlavamo prima:

Per ottenere le combinazioni con ripetizione che stavamo cercando possiamo fare in due modi.

1. Utilizzando la funzione UNICI

In Z11 scriviamo:

E ricaviamo immediatamente l’elenco ridotto delle combinazioni in questo modo:

Questa è la maniera più semplice e veloce.

2. Utilizzando la funzione FILTRO

Questo è un modo un pizzico più complicato. Per ciascuno dei valori in W11# vogliamo calcolare la presenza del valore stesso nelle righe precedenti. Per farlo utilizzeremo la funzione SCARTO, aumentando la altezza della colonna della matrice di controllo via via che scendiamo. A questa applicheremo il CONTA.SE per vedere se il valore attuale, cioè ciascun valore della matrice, è presente nella sottomatrice precedente calcolata con SCARTO. Confrontando questo valore con 1, ovvero che debba comparire una sola volta, costruiremo la matrice colonna per il filtro da applicare alla funzione FILTRO.

La formula quindi sarà:

In entrambi i casi il risultato sarà identico:

Dal seguente link è possibile scaricare il file Excel con tutti i calcoli già fatti: combinazioni semplici

Vedi articolo

[excel] Sommare tariffe giornaliere diverse in base alla data utilizzando le matrici in Excel

Obiettivo: Immaginiamo di voler calcolare il costo dei pernottamenti in un albergo dove le tariffe variano in base alla stagione (nello specifico in base ai mesi) e si ha una data di ingresso e il numero di notti.

Una soluzione a questo esercizio l’ho già proposta sul blog di Mummu Academy, giocando un po’ con le matrici: [EXCEL] SOMMARE TARIFFE GIORNALIERE DIVERSE PER DATA USANDO LE MATRICI IN EXCEL

La soluzione che propongo qui è più semplice e sintetica e fa uso sempre del calcolo matriciale in Excel.

Come nel suddetto esercizio la nostra base di partenza è un foglio in Excel contenente i seguenti dati:

I dati che vogliamo generare sono quelli dell’intervallo G2:G6.

Anzitutto vogliamo ottenere una matrice contenente tutte le date di pernottamento. Per farlo prenderemo ciascuna data di partenza e sommeremo i giorni corrispondenti. Se dobbiamo sommare 5 notti allora alla data di partenza dovremo sommare il vettore [0, 1, 2, 3, 4]. Faccio notare come il vettore parta da 0. Questo perché, nel caso di Mario Rossi, i pernottamenti li avremo nei giorni [21/06/2019, 22/06/2019, 23/06/2019, 24/06/2019, 25/06/2019]. La prima data deve essere anche quella di arrivo.

Generiamo quindi anzitutto il vettore dei giorni come detto prima, sfruttando RIF.RIGA e INDIRETTO nel modo seguente:

Questo genererà un vettore simile a questo:

Adesso possiamo sommare a questo vettore la data di partenza:

Il vettore che potremo vedere a schermo (grazie anche alle nuove funzionalità di espansione di Excel) sarà come il seguente:

Dall’elenco delle date vogliamo adesso estrarre il mese corrispondente utilizzando la funzione MESE.

Il risultato dell’operazione sarà simile a questo:

Il numero 6 rappresenta il mese di giugno. Se applicassimo questa formula a Chiara Celesti vedremmo un risultato simile a questo:

Faccio notare come alla fine del mese di dicembre si passi da 12 a 1, ovvero a gennaio.

A questo punto possiamo usare il CERCA.VERT su ciascuno di questi valori per ricavare la tariffa che ci interessa. La formula diventerà dunque:

In questo modo otterremo un vettore contenente la tariffa per ogni singolo giorno. Non ci resta che usare la SOMMA.

La formula diventerà quindi:

A questo punto possiamo espandere la formula da G2 a G6. Se abbiamo fatto tutto correttamente otterremo i seguenti risultati:

Vedi articolo

[excel] Creare un grafico variabile che si adatti alla lunghezza della serie, sfruttando le matrici

Obiettivo: voglio realizzare un grafico che prenda in input n valori x e y, dove n è un valore arbitrario. Questo significa che se imposto n = 7 allora avrò 7 valori di x e y opportunamente calcolati. Per esempio potrebbe trattarsi del valore annuale delle rate di un mutuo calcolato su n anni; quindi se metto 10 anni mi aspetto un grafico con 10 colonne, se metto 20 anni mi aspetto un grafico con 20 colonne, ecc.

Nel caso specifico voglio realizzare qualcosa che dia il seguente risultato.

Per una simulazione con n = 25 anni

Per una simulazione con n = 30 anni

Faccio notare che il grafico è rimasto lo stesso, non ho dovuto rifarlo, ma è aumentato il numero di valori sull’asse delle ascisse in base al numero di anni prescelto.

Per l’esercizio voglio anche sfruttare le nuove possibilità offerte dal recente aggiornamento di Office 365 in merito al calcolo e alla visualizzazione delle matrici.

La versione di Excel che ho utilizzato è la Versione 1912 (build 12325.20298) e non tutte le funzionalità potrebbero essere disponibili nelle versioni precedenti.

Detto tutto questo cominciamo.

Anzitutto quello che voglio realizzare, a titolo di esercizio, è un simulatore semplificato di investimento con mutuo. Vogliamo cioè costruire il business plan per la realizzazione di un qualche progetto che avrà un rendimento annuale e per il quale vogliamo chiedere un finanziamento. I dati di cui avremo bisogno in input saranno quindi

  1. Valore complessivo del progetto (es. 250.000€)
  2. Valore del mutuo per il finanziamento iniziale (mutuo calcolato a tasso fisso su una percentuale del valore del progetto, per esempio l’80% al 3% in 15 anni con rate mensili)
  3. Entrate annuali per il progetto operativo (es. 30.000€ / anno)
  4. Durata del progetto (es. 30 anni)
  5. Tasso di inflazione (es. 2%)

Predisponiamo quindi i valori in input a partire dalla cella C4 nel modo seguente:

Di tutti i valori l’unico calcolato è il valore della rata, dove abbiamo la seguente formula:

Rinominiamo il foglio su cui stiamo lavorando in MUTUO, questo ci servirà dopo:

Adesso calcoliamo i valori di gestione disponendoli nel modo seguente a partire dalla cella H4.

Calcoliamo gli anni che partiranno da 0 fino all’anno indicato in D10. Se in D10 ci fosse 30 il numero totale di anni sarebbe quindi 31. Il numero di anni sarà quindi sempre n + 1. Dal punto di vista teorico l’anno 0 è quello degli investimenti pre-operativi; non è indispensabile ai fini dell’esercizio ma, sebbene stiamo facendo un esempio semplificato, facciamo le cose per bene.

Voglio che gli anni siano generati, come tutto il resto, in modo dinamico senza dover predisporre un numero massimo di valori possibili. Per farlo sfruttiamo le funzioni sulle matrici di excel nel modo seguente:

Inserendo questo valore in H5 nelle celle sottostanti verranno automaticamente completati i dati conseguenti. Selezionando la cella H5 ci verrà mostrata una selezione blu sottostante:

I dati evidenziati non sono inseriti nelle celle, ma generati automaticamente in quanto il risultato in H5 è una matrice colonna contenente i valori calcolati.

Procediamo in modo analogo per le colonne seguenti. Dal momento che vogliamo che ogni colonna sia generata per intero e non debba fare affidamento sulla colonna a fianco, procederemo col calcolo matriciale. Per le rate inseriremo in I5 la seguente formula:

Nel caso specifico dobbiamo usare un doppio se, perché la funzione =E() non è compatibile col calcolo matriciale. Con il primo SE verifichiamo che l’anno sia maggiore di 0, con il secondo SE verifichiamo che l’anno sia minore o uguale all’anno finale del mutuo, in tal caso inseriamo il valore della rata in D12, altrimenti lasciamo a 0.

Per la colonna dei costi, che, come nell’immagine precedente, avrà in realtà un unico valore e il resto a 0, inseriamo la seguente formula:

In pratica l’unico costo iniziale che abbiamo è la parte del progetto non coperta dal mutuo, calcolata con la formula -D4*(1-D7)

Nella colonna delle entrate inseriamo la seguente formula:

In modo analogo a prima quando l’anno è maggiore di 0 allora mettiamo il valore delle entrate annuali in D9. Il risultato lo dividiamo per (1+2%)^anno, dove il 2% è il tasso di inflazione e l’anno è l’anno corrente. Potremmo discutere se mettere al primo anno come anno 1 oppure 0, ma non è questa la sede. Nel mio caso lascerò il valore a 1.

A questo punto scriviamo la colonna della cassa. Questa è la più semplice da realizzare perché è semplicemente la somma delle precedenti tre formule, messe in fila una dopo l’altra.

Adesso vogliamo calcolare il cumulativo di cassa. Qui le cose si complicano perché lo vogliamo fare sulle matrici.

Per intendersi ipotizziamo di avere i seguenti valori: 1, 2, 3, 4, 5

Il valore cumulativo sarebbe dunque: 1, 3, 6, 10, 15

Ovvero la somma di tutti i valori precedenti al valore corrente.

Adesso noi sappiamo che l’istruzione RIF.RIGA(INDIRETTO("1:"&(D10+1))) ci dà dei valori che vanno da 1 a n+1. I valori della cassa si troveranno via via in L5:L[x] dove L[x] sarà in sequenza: L5, L6, L7 ecc.

Quindi possiamo sfruttare l’istruzione precedente sommandole un +4 per trovare tutti i valori di L[x] di cui abbiamo bisogno.

In questo modo verrà generata una serie di matrici contenenti ciascuna i valori da sommare. Per capirsi, riprendendo la sequenza numerica di prima, il risultato sarebbe simile a questo:

La funzione =SOMMA() applicata alla seconda matrice darebbe purtroppo un UNICO risultato. Per ottenere una matrice colonna con la somma delle singole righe della matrice quadrata dobbiamo usare la funzione =SOMMA.SE(). La condizione di somma sarà banalmente <>0

La formula che quindi andremo ad inserire sarà:

Fatte tutte queste belle cose avremo costruito la tabella come mostrato prima.

Questa tabella cambierà automaticamente di dimensione in base al numero di anni. Se per esempio mettessimo gli anni del mutuo a 8 e l’operatività complessiva a 15 passeremmo a qualcosa come questo:

A questo punto andiamo a realizzare il grafico. Per farlo sfrutteremo la definizione dei nomi.

Anzitutto andiamo in Formule > Gestione nomi

Da qui definiremo i nomi nel modo seguente:

Attribuiamo un nome e creiamo un riferimento. In tutti i casi sfrutteremo la funzione scarto che partirà sempre dalla riga 5 e si estenderà per una altezza di n – 1 valori, dal momento che la colonna possiede un titolo. Creiamo quindi i seguenti nomi:

CASSA

CUMULATIVO

ANNI

Andiamo adesso ad inserire un istogramma da Inserisci > Grafici

In seleziona dati aggiungiamo due voci di serie, nello specifico CASSA e CUMULATIVO

Nel nome della serie scriviamo via via la descrizione, per esempio Cassa, mentre nei valori scriviamo:

Ricordiamoci che MUTUO è il nome del foglio. E’ molto importante anteporre alla definizione del nome il nome del foglio, altrimenti non funzionerà.

Alla voce etichette inseriamo:

Il risultato che otterremo sarà simile a questo:

Fatto questo otterremo un grafico simile al seguente:

Per trasformare il flusso cumulativo in una linea, anziché nell’istogramma, clicchiamo col destro su una delle colonne arancioni e scegliamo Cambia tipo di grafico serie…

Nella finestra che si aprirà selezioniamo, dal menu a tendina, la voce Linee

Se abbiamo fatto tutto bene otterremo un grafico come il seguente:

Il grafico cambierà dimensione al variare dei valori in input.

Il vantaggio di questa soluzione è che potremmo inserire valori assolutamente arbitrari per la lunghezza complessiva dei dati, senza doverla predisporre in anticipo. Se per esempio scegliessimo la durata del progetto su 500 anni, andrebbe comunque bene.

Vedi articolo

[c#] Creare una classe per stampare con i font installati nella stampante

L’obiettivo è quello di inviare del testo alla stampante utilizzando i font nativi al suo interno e la libreria gdi32.dll di Windows. Questa volta integreremo tutto in una semplice applicazione in C#.

Anzitutto creiamo un semplice form con una TextBox chiamata txtTesto ed un Button chiamato btnStampa.

Aggiungiamo poi una nuova classe che estenda la classe System.Drawing.Printing.PrintDocument

Per usare la classe anzitutto dobbiamo implementare alcuni metodi e creare i corrispondenti per le funzioni della libreria GDI32.

A tale scopo utilizzeremo l’istruzione [DllImport("gdi32.dll")] prima delle specifiche definizioni.

Le funzioni che dobbiamo implementare sono rispettivamente: CreateFont, SelectObject, DrawText, DeleteObject

Per maggiori riferimenti alle singole funzioni consiglio di visionare il sito Pinvoke.net

Detto tutto questo costruiamo la classe nel modo seguente:

Eseguiamo il test della funzione nel form principale:

In questo modo stamperemo attraverso la stampante predefinita.

Nel mio caso una stampante Samsung M2070, che riconosce come font “Courier New“.

Vedi articolo

[Excel] Sommare tariffe giornaliere diverse per data usando le matrici in Excel

Per chiunque fosse interessato a lavorare sulla matrici in Excel ho pubblicato un simpatico esercizio sul sito di Mummu Academy al seguente link:

[Excel] Sommare tariffe giornaliere diverse per data usando le matrici in Excel

Vedi articolo

[excel] Come creare una password casuale di lunghezza arbitraria con un’unica formula

Mi sono chiesto se ci fosse un modo, utilizzando Excel, di generare una password casuale di lunghezza arbitraria con una singola formula.

Naturalmente utilizzando VBA sarebbe semplicissimo, si potrebbe creare una propria funziona ed utilizzarla all’interno della formula. In alternativa si potrebbe creare una tabella di riferimento e generare la password a partire da essa (qui c’è un esempio interessante).

Però mi sono impuntato a voler creare una singola formula, utilizzando le funzioni disponibili nativamente in Excel, per generare una password di lunghezza arbitraria.

Soluzione veloce

Per chi non avesse voglia di proseguire nell’analisi della questione la soluzione è la seguente (per 1 singolo carattere preso tra maiuscole, minuscole, numeri e caratteri speciali):

Per generare una password di 2 caratteri basta concatenare la formula precedente per 2 volte, di 3 caratteri per 3 volte ecc (di seguito l’esempio per 2 caratteri):

Spiegazione

Anzitutto partiamo dalla seguente idea. Se avessi un vettore di 3 caratteri, potrei estrarre casualmente uno dei tre caratteri?

Per farlo mi è sufficiente scrivere:

Ad ogni ricalcolo del foglio questa formula pescherà casualmente un valore tra 10, 20 e 30. Tra le parentesi graffe {} ho inserito una matrice (nello specifico un vettore colonna) di 3 valori. Il punto fa da separatore di riga, altrimenti il backslash farebbe da separatore di colonna.

In ogni caso la presente formula sarebbe equivalente alla seguente situazione:

In A4 in questo caso vedrei un valore casuale preso tra A1:A3.

Seconda idea. Sarebbe possibile generare, al posto dei suddetti valori, dei valori casuali che escano fuori da una funzione? Purtroppo Excel non consente di scrivere esplicitamente una matrice contenente a sua volta delle funzioni, senza passare dall’utilizzo delle celle.

Allora mi sono chiesto: ci sarà un modo per scrivere una stringa e trasformarla in un vettore mediante un separatore di stringa (una specie di str_split per intendersi nel PHP)? Se la stringa è composta di singoli caratteri che dovranno diventare elementi del vettore è effettivamente possibile.

Possiamo cioè trasformare il nome Noctis nel vettore {“N”.”o”.”c”.”t”.”i”.”s”}

Per farlo è sufficiente utilizzare:

Faccio notare che INDIRETTO prendere le righe dalla 1 alla 6. Con RIF.RIGA generiamo un vettore di riferimenti equivalente a {1.2.3.4.5.6}. Per inciso la funzione sarebbe potuta anche essere così:

Dal momento però che mi fa fatica scrivere tutto il vettore da 1 a 6 (più che altro se fosse stato più lungo sarebbe risultato abbastanza impensabile) preferisco generarlo con questo semplice trucchetto.

A questo punto abbiamo capito che possiamo spezzare una stringa arbitraria nel vettore dei caratteri che la compongono.

Adesso prendiamo in considerazione la seguente tabella ASCII.

Grazie alla funzione CARATT.UNI posso estrarre un carattere a partire dal suo riferimento numerico nella suddetta tabella (nello specifico qualunque carattere UNI-CODE, ma non è il momento di approfondire la cosa).

Per esempio se digito:

Ottengo la lettera p minuscola.

Quindi posso generare caratteri casuali mischiando questa funzione con CASUALE.TRA nel modo seguente.

Caratteri maiuscoli:

Caratteri minuscoli:

Caratteri speciali:

Per generare invece i numeri da 0 a 9 mi basta utilizzare:

Quindi usando la formula:

Posso generare una stringa di 4 caratteri contenente un numero casuale, una maiuscola casuale, una minuscola casuale e un carattere speciale casuale.

Inserendo questa dentro la suddetta formula con STRINGA.ESTRAI posso estrarre i quattro caratteri dalla stringa in un vettore di caratteri casuali con la seguente formula:

A questo punto inserendo il tutto dentro a INDICE ottengo la precedente formula e il gioco è fatto.

Per generare delle password casuali di 16 caratteri come queste:

!#lKTHLb0nx2Z(0H
7&x(128’#L#(t6B3
,77YJ”lu35%LKYVm
svkA.ur1Y””+#r*t
15I35t1Z”(/Lz#0a

Ci sarebbe sufficiente utilizzare la formula un po’ più lunga e ripetuta come detto all’inizio.

 

Vedi articolo

[vba] Utilizzare il dizionario nelle macro di Excel per calcolare la media dei voti, in diverse materie per diversi studenti

Propongo un veloce esercizio in VBA sull’utilizzo dei dizionari.

Immaginiamo di avere un elenco di studenti e voti in diverse materie come nell’esempio seguente:

Vogliamo calcolare la media dei voti per ciascun studente in ciascuna materia, mettendo tutto in una tabella tipo pivot nel modo seguente:

La cella gialla è in L10

A tale scopo realizziamo la seguente macro in VBA utilizzando i dizionari. Per usare il dizionario anzitutto aggiungiamo il relativo riferimento da Strumenti > Riferimenti…

Il riferimento che ci interessa è il Microsoft Scripting Runtime, libreria dalla quale andremo a prelevare Scripting.Dictionary

Fatto tutto questo vediamo il codice:

Faccio notare che nel mio esempio la tabella di scrittura parte dalla posizione L10 nel foglio di calcolo attivo.

Quello che abbiamo realizzato è un dizionario di dizionari. Per realizzarlo utilizziamo:

 

Vedi articolo

[vba] Creare una funzione che accetti un numero indefinito di parametri

Per questo esercizio vediamo come creare una funzione personalizzata simile (perché non andremo ad implementare tutte le alternative) al SOMMA.PIÙ.SE in VBA per Excel.

Anzitutto individuiamo le criticità:

  1. Avremo bisogno di accettare un range per la somma ed n coppie di parametri come intervalli e criteri, la nostra funzione avrà quindi la forma di FUNZIONE(somma, [intervallo 1], [criterio 1], …)
  2. I criteri possono essere numerici oppure stringhe di confronto, come per esempio “>2”

Anzitutto vediamo come creare una funzione che accetti n parametri come abbiamo definito di sopra. Per cui digitiamo in VBA:

In questo caso abbiamo inserito il minimo indispensabile, definendo il risultato come un Double, dichiarando internamente una variabile totale ed impostandola su 0. Questa variabile la utilizzeremo in seguito. L’argomento somma è di tipo Range (qui occhio agli indici, come vedremo tra poco!) e poi segue un vettore di parametri args definiti come Variant.

Per scorrere i vari parametri possiamo usare l’istruzione:

A questo punto vogliamo costruire un vettore di criteri lungo quanto l’intervallo della somma e che per ogni chiave contenga un valore equivalente a 0 oppure ad 1, in funzione che si debba o meno sommare il valore nell’intervallo somma.

Per fare questo definiamo:

Con il metodo somma.count misuriamo la dimensione del Range somma, attenzione perché il range si muove da 1 fino a somma.count, mentre il nostro vettore criteri andrà da 0 fino a somma.count – 1

Infine impostiamo tutto il vettore criteri() su 1, questo ci servirà tra poco per le operazioni logiche.

A questo punto non ci rimane che valutare tutti i criteri, tenendo presente che vanno a coppie di intervallo criteri e criterio. Perciò utilizzeremo l’istruzione:

Gli intervalli dei criteri si trovano in posizioni pari, mentre il criterio in posizione dispari.

Infine utilizzeremo il metodo Evaluate sfruttando la funzione SE di Excel per valutare ogni criterio, nel modo seguente:

Faccio notare che in Evaluate va scritta la funzione col nome in inglese. Dal momento che i criteri possono essere numerici oppure stringhe, come abbiamo detto all’inizio, dobbiamo distinguere tra le due cose e quindi scriveremo:

Infine ricordiamoci che tutti i criteri devono essere validi, quindi per modificare ogni posizione del vettore criteri() useremo il prodotto. In questo modo se sono tutti veri risulterà 1, un prodotto di tutti 1 insomma, mentre se anche un solo criterio sarà falso allora il prodotto risulterà 0, dal momento che basta uno 0 nel prodotto per azzerare tutto.

Detto tutto ciò la nostra funzione finale sarà simile a questa:

Inutile dire che la funzione potrebbe essere ulteriormente affinata per coprire tutti i possibili casi in cui può essere utilizzata SOMMA.PIÙ.SE 😉

Vedi articolo