Uso queste 3 formule di Excel per automatizzare le mie noiose attività
La vita è frenetica e destreggiarsi tra decine di app per le faccende "da fare assolutamente" aumenta solo il caos. Poiché lavoro già quotidianamente in Excel, ne ho ampliato l'uso per automatizzare tre dei miei compiti più noiosi. Queste formule da impostare e dimenticare rendono la vita più facile e richiedono solo pochi minuti per essere create.
Poco tempo? Se vuoi saltare la fase di creazione e iniziare subito ad automatizzare la tua vita, puoi trovare un link al file Excel completato in fondo a questo articolo.
Il divisore di conto proporzionale
Funzioni richieste | Nessuno! |
|---|---|
|
Complessità | Basso—aritmetica di base e riferimenti standard. |
Nel mio foglio di calcolo qui sotto, quando inserisco una bolletta, la formula evita trattative imbarazzanti generando una divisione auto-regolante ponderata sul salario.
Il formato numero contabile è applicato alle celle A2 e B2, così come alle colonne Totale, Persona A e Persona B nella tabella. La tabella è stata rinominata "T_Expenses."
Per ottenere questo è necessario digitare una sola formula nella cella C5 e trascinarla nella cella D5:
=(A$2/($A$2+$B$2))*T_Expenses[@[Total]:[Total]]Quando aggiungo una spesa nella riga successiva, poiché i dati sono formattati come tabella di Excel, tutte le sue formule e la formattazione si espandono automaticamente.
Ecco come funziona la formula.
Parte 1: La proporzione
La prima parte della formula calcola la percentuale di persona A sul reddito totale della famiglia.
=(A$2/($A$2+$B$2))Il simbolo del dollaro nelle formule di Excel definisce se un riferimento deve cambiare quando copi o trascini una formula in una nuova cella. A$2 blocca solo la riga, quindi quando la formula viene applicata alle righe più in basso nella tabella, viene sempre utilizzata la riga degli stipendi. Tuttavia, poiché non c'è il simbolo del dollaro davanti alla A, la colonna è relativa, quindi quando trascini la formula dalla colonna C alla colonna D, la A cambierà correttamente in B. Nella parte della somma, tutte le righe e le colonne sono bloccate perché non devono cambiare per nessun calcolo.
Durante la digitazione della formula, premi F4 per scorrere tra le combinazioni del simbolo del dollaro (riferimenti assoluti, misti e relativi).
Parte 2: Il conto diviso
La proporzione della persona viene moltiplicata per il conto totale per calcolare l'importo finale.
*T_Expenses[@[Total]:[Total]]Questa sintassi garantisce che il riferimento alla colonna Totale non si sposti quando trascini la formula verso destra. Ecco una spiegazione di cosa sta succedendo:
- Il simbolo @: L'operatore di intersezione implicita indica a Excel di guardare solo nella riga corrente della colonna Totale.
- Le parentesi e i due punti: Questa è la parte che blocca il riferimento per assicurarsi che resti collegato alla colonna Totale.
- Il nome della tabella: Non appena passi da un riferimento strutturato semplice a uno più complesso, Excel richiede di aggiungere il nome della tabella.
Parte 3: L'altra persona
Infine, fai clic e trascina il quadratino di riempimento nella cella C5 fino alla cella D5.
La lista della spesa intelligente
Funzioni richieste | SE, O, DESTRA e l'ampersand (&) |
|---|---|
Complessità | Medio—logica standard, annidamento e trigger basati su testo. |
In questo foglio di lavoro, non appena aggiorno un numero nella colonna Stock, la colonna Azione mostra cosa comprare e quanti.
Ecco la formula che uso per farlo accadere:
=IF([@Target]>[@Stock],"Buy "&[@Target]-[@Stock]&" "&[@Unit]&IF([@Target]-[@Stock]>1,IF(OR(RIGHT([@Unit],1)="o",RIGHT([@Unit],1)="s",RIGHT([@Unit],1)="x"),"es","s"),"")&" of "&[@Item],"-")Sì, a prima vista sembra complicato, ma in realtà è logico e intuitivo.
Parte 1: Il divario dell'inventario
La formula inizia con una porta logica che decide se è necessaria qualsiasi azione. Se il tuo obiettivo è superiore al tuo stock, devi acquistare di più. Se non lo è, la formula salta direttamente alla fine e restituisce "-".
=IF([@Target]>[@Stock],...,"-")Parte 2: Il calcolo
Se sei sotto scorta, Excel costruisce la prima parte di una frase istruttiva. La matematica calcola la differenza tra ciò che vuoi e ciò che hai, quindi la abbina all'unità di misura. Gli e commerciali (&) agiscono come una colla, unendo numeri e parole in una singola stringa di testo.
"Buy "&[@Target]-[@Stock]&" "&[@Unit]Parte 3: La grammatica
Questa è la parte più lunga della formula. Il suo compito è decidere se aggiungere un suffisso plurale alla tua unità (come trasformare "Box" in "Boxes") o lasciarla invariata. Per prima cosa, verifica se stai acquistando più di uno. Se ne serve solo uno, aggiunge una stringa vuota ("") per mantenere la parola al singolare. Tuttavia, se ne servono due o più, guarda l'ultima lettera delle parole nella colonna Unit. Se finisce con o, s o x, la formula sceglie "es.". Altrimenti, aggiunge un semplice "s."
IF([@Target]-[@Stock]>1,IF(OR(RIGHT([@Unit],1)="o",RIGHT([@Unit],1)="s",RIGHT([@Unit],1)="x"),"es","s"),"")Questo gestisce la maggior parte dei plurali, anche se potrebbe non riconoscere quelli irregolari. Se è necessaria un'alta precisione, usa una tabella di riferimento o aggiungi più regole alla formula.
Parte 4: La stringa finale
Ora che la pluralità dell'unità è stata sistemata, la parte finale aggiunge la parola "di" e include il nome dell'oggetto:
&" of "&[@Item]Il tracker degli abbonamenti
Funzioni richieste | SE, DATA.MESE, GIORNO, MESE, ANNO e OGGI |
|---|---|
Complessità | Combinazioni complesse di funzioni di calcolo delle date. |
In questo foglio di calcolo, una volta inseriti i dettagli dell'abbonamento, Excel mi dice quanti giorni mancano al prossimo pagamento.
La formula è la seguente:
=(IF([@Period]="Monthly",EDATE([@First],(YEAR(TODAY())-YEAR([@First]))*12+MONTH(TODAY())-MONTH([@First])+(DAY(TODAY())>DAY([@First]))),EDATE([@First],(YEAR(TODAY())-YEAR([@First])+(DATE(YEAR(TODAY()),MONTH([@First]),DAY([@First]))<TODAY()))*12))-TODAY())La formattazione dei numeri contabili è applicata alla colonna Costo, la formattazione Data breve alla prima colonna e la formattazione Numero (con zero decimali) alla colonna Giorni. Ho anche aggiunto opzioni a discesa di formattazione condizionale (Annuale e Mensile) alla colonna Periodo.
Ecco come funziona.
Parte 1: L'interruttore
La formula inizia identificando il ciclo di fatturazione. Se il periodo è mensile, Excel esegue il primo calcolo. Se è qualsiasi altro (annuale), esegue il secondo.
Rapporto How-To Geek
Rapporto How-To Geek
Iscrivendoti, accetti di ricevere newsletter e email di marketing e accetti i Termini di utilizzo e l'Informativa sulla privacy di Valnet. Puoi annullare l'iscrizione in qualsiasi momento.
=IF([@Period]="Monthly"Parte 2: Il motore mensile
Questo calcola il prossimo anniversario mensile. Calcola il numero di mesi dal tuo primo pagamento e aggiunge un mese in più se la data odierna è già passata rispetto al giorno di fatturazione abituale. Questo assicura che la data trovata sia sempre nel futuro.
EDATE([@First],(YEAR(TODAY())-YEAR([@First]))*12+MONTH(TODAY())-MONTH([@First])+(DAY(TODAY())>DAY([@First])))
Parte 3: Il motore annuale
Simile al motore mensile, questo guarda agli anni trascorsi. Crea una data temporanea per l'anniversario di quest'anno: se quella data è già passata, aggiunge 12 mesi per trovare la data del prossimo anno.
EDATE([@First],(YEAR(TODAY())-YEAR([@First])+(DATE(YEAR(TODAY()),MONTH([@First]),DAY([@First]))<TODAY()))*12))Parte 4: Il conto alla rovescia
Una volta che la formula ha trovato la corretta data di rinnovo futura, sottrae la data attuale da essa. Questo trasforma una data del calendario in un numero di giorni.
-TODAY())
Anche se creare le formule da soli può essere gratificante, per iniziare subito, scarica una copia gratuita del foglio di lavoro completo. Dopo aver cliccato sul link, troverai il pulsante di download nell'angolo in alto a destra dello schermo. Quando aprirai il file, troverai ogni formula in una scheda separata del foglio di lavoro.