Theo utilizza un foglio di lavoro Excel per tenere traccia delle prenotazioni nella sua azienda. I dati sono costituiti da sole tre colonne. Il primo è il nome di una persona, il secondo il numero della prima settimana (1-52) della prenotazione e il terzo il numero dell’ultima settimana della prenotazione.

Le persone possono essere prenotate per più settimane (ad esempio, la settimana di inizio è 15 e la settimana di fine è 19). Theo ha bisogno di un modo per inserire un numero di settimana e quindi una formula per determinare quale nome (colonna A) è associato a quel numero di settimana. I dati non sono ordinati in un ordine particolare e la società non permetterà a Theo di utilizzare una macro per ottenere il risultato (deve essere una formula).

La situazione di Theo sembra abbastanza semplice, ma è piena di insidie ​​quando si trova una soluzione. L’analisi dei dati potenziali (come mostrato nella figura seguente) illustra rapidamente perché questo è il caso. (Vedi figura 1.)

image

Figura 1. Potenziali dati per il problema di Theo.

Si noti che i dati (come ha detto Theo) non sono in un ordine particolare.

Nota, inoltre, che ci sono alcune settimane in cui non ci sono prenotazioni (come la settimana 5 o 6), settimane in cui ci sono più persone (come la settimana 11 o 16) e settimane in cui c’è qualcuno prenotato, ma la settimana il numero non viene visualizzato nella colonna B o C (come la settimana 12 o 17).

Prima di iniziare a esaminare le potenziali soluzioni, supponiamo che la settimana che vuoi conoscere sia la cella E1. Dovresti denominare questo intervallo come Query. Inoltre, nomina l’intervallo che contiene i nomi delle persone (in questo esempio, le celle A2: A10) come ResNames, le settimane iniziali (B2: B10) come StartWeeks e le settimane finali (C2: C10) come EndWeeks. Infine, definire un nome per l’intera tabella (A2: C10), ad esempio MyData. Questa denominazione, sebbene non strettamente necessaria, renderà la comprensione delle formule molto più semplice.

Una possibile soluzione è aggiungere quella che viene comunemente chiamata “colonna helper”. Aggiungere quanto segue alla cella D2:

=IF(AND(Query>=B2,Query<=C2),"RESERVED","")

Copia la formula verso il basso, per tante celle quanti sono i nomi nella tabella. (Ad esempio, copiarlo nella cella D10.) Quando si inserisce un numero di settimana nella cella E1, la parola “RISERVATO” appare a destra di qualsiasi prenotazione che coinvolge quel numero di settimana. È anche facile vedere se ci sono più persone prenotate per quella settimana o se non ci sono persone prenotate per quella settimana. Puoi anche applicare un filtro automatico e selezionare per mostrare solo quei record con la parola “RISERVATO” nella colonna D.

È possibile, se lo si desidera, rinunciare alla colonna helper e considerare l’utilizzo della formattazione condizionale per visualizzare chi è prenotato per una settimana desiderata.

Seleziona semplicemente i nomi nella colonna A e aggiungi una regola di formattazione condizionale che utilizza la seguente formula:

=AND(Query>=B2,Query<=C2)

(Il modo in cui inserisci le regole di formattazione condizionale è stato ampiamente descritto in altri numeri di ExcelTips.) Imposta la regola in modo che cambi l’ombreggiatura (motivo) applicato alla cella e sarai facilmente in grado di vedere quali prenotazioni si applicano al settimana che ti interessa.

Un altro approccio consiste nell’usare una formula di matrice. Seleziona alcune celle in più rispetto al numero di prenotazioni sovrapposte previste, quindi inserisci quanto segue in quelle celle premendo Ctrl + Maiusc + Invio:

=IFERROR(INDEX(ResNames,LARGE((StartWeeks<=Query)(EndWeeks>=Query)(ROW(ResNames)),ROW()-1)-1),"")

Quando scegli il numero di celle, vuoi che questa formula di matrice occupi, guarda, ad esempio, il numero di persone che possono essere prenotate per la settimana 11. Nell’esempio mostrato in questo suggerimento, sono 2 persone. Seleziona più di quel numero di celle e quindi inserisci la formula di matrice in quelle celle.

Se prevedi di avere potenzialmente 20 persone prenotate per la stessa settimana, allora ti consigliamo di scegliere un numero maggiore di celle, come 20 o 30. Seleziona semplicemente le celle, inserisci la formula nella barra della formula, quindi premi Ctrl + Maiusc + Invio.

Infine, dovresti davvero considerare di rivedere il modo in cui sono disposti i tuoi dati.

È possibile creare un foglio di lavoro con i numeri delle settimane nella colonna A (da 1 a 52 o 53) e quindi i nomi dei luoghi nella colonna B. Se una persona è stata prenotata per due settimane, il suo nome apparirà nella colonna B due volte, una accanto a ciascuna delle due settimane che hanno riservato.

Con i tuoi dati in questo formato, potresti facilmente scansionare i dati per vedere quali settimane sono disponibili, quali sono prese e da chi sono state prese.

Se vuoi fare una sorta di ricerca, è facile usare la funzione CERCA.VERT in base al numero della settimana, poiché è la prima colonna dei dati, in ordine ordinato.

ExcelTips è la tua fonte di formazione economica su Microsoft Excel.

Questo suggerimento (11078) si applica a Microsoft Excel 2007, 2010, 2013, 2016, 2019 ed Excel in Office 365. Puoi trovare una versione di questo suggerimento per l’interfaccia del menu precedente di Excel qui:

Determinazione di un nome per un numero di settimana.