Note: The other languages of the website are Google-translated. Back to English
Accedi Registrati

Come definire l'intervallo in base a un altro valore di cella in Excel?

Calcolare un intervallo di valori è facile per la maggior parte degli utenti di Excel, ma hai mai provato a calcolare un intervallo di valori in base al numero in una cella specifica? Ad esempio, c'è una colonna di valori nella colonna A e voglio calcolare il numero di valori nella colonna A in base al valore in B2, il che significa che se è 4 in B2, farò la media dei primi 4 valori in colonna A come mostrato nell'immagine sottostante. Ora introduco una semplice formula per definire rapidamente l'intervallo in base a un altro valore di cella in Excel.
doc definisce l'intervallo in base al valore della cella 1

Definisci l'intervallo in base al valore della cella

freccia blu freccia destra Definisci l'intervallo in base al valore della cella

Per eseguire il calcolo per un intervallo basato su un altro valore di cella, puoi utilizzare una formula semplice.

Seleziona una cella vuota in cui pubblicherai il risultato, inserisci questa formula = MEDIA (A1: INDIRETTO (CONCATENATO ("A", B2)))e premere Entra chiave per ottenere il risultato.
doc definisce l'intervallo in base al valore della cella 2

Nota:

1. Nella formula, A1 è la prima cella della colonna che si desidera calcolare, A è la colonna per la quale si calcola, B2 è la cella su cui si calcola in base. Puoi modificare questi riferimenti in base alle tue esigenze.

2. Se vuoi fare un riepilogo, puoi usare questa formula = SOMMA (A1: INDIRETTO (CONCATENA ("A", B2))).

3. Se i primi dati che si desidera definire non si trovano nella prima riga di Excel, ad esempio, nella cella A2, è possibile utilizzare la formula come questa: = MEDIA (A2: INDIRETTO (CONCATENA ("A", RIGA (A2) + B2-1))).
doc definisce l'intervallo in base al valore della cella 2

Conta / somma rapidamente le celle per colore di sfondo o formato in Excel
In alcuni casi, potresti avere un intervallo di celle con più colori e quello che vuoi è contare / sommare i valori in base allo stesso colore, come puoi calcolare rapidamente?
Con una lunga Kutools for Excel's Conta per colore, puoi eseguire rapidamente molti calcoli in base al colore e puoi anche generare un report del risultato calcolato.  Fai clic per una prova gratuita completa in 30 giorni!
conteggio documenti per colore 1
 
Kutools per Excel: con più di 300 utili componenti aggiuntivi di Excel, liberi di provare senza limitazioni in 30 giorni.

I migliori strumenti per la produttività in ufficio

Kutools per Excel risolve la maggior parte dei tuoi problemi e aumenta la tua produttività dell'80%

  • Riutilizzo: Inserisci rapidamente formule complesse, grafici e tutto ciò che hai usato prima; Crittografa celle con password; Crea mailing list e invia email ...
  • Bar Super Formula (modifica facilmente più righe di testo e formula); Layout di lettura (leggi e modifica facilmente un gran numero di celle); Incolla su intervallo filtrato...
  • Unisci celle / righe / colonne senza perdere dati; Contenuto delle celle divise; Combina righe / colonne duplicate... Impedisci celle duplicate; Confronta intervalli...
  • Seleziona Duplica o Unico Righe; Seleziona Righe vuote (tutte le celle sono vuote); Super Find e Fuzzy Find in molte cartelle di lavoro; Selezione casuale ...
  • Copia esatta Più celle senza modificare il riferimento della formula; Riferimenti di creazione automatica a più fogli; Inserisci punti elenco, Caselle di controllo e altro ...
  • Estrai testo, Aggiungi testo, Rimuovi per posizione, Rimuovi spazio; Creare e stampare totali parziali di paging; Converti contenuto e commenti tra celle...
  • Super filtro (salva e applica schemi di filtri ad altri fogli); Ordinamento avanzato per mese / settimana / giorno, frequenza e altro; Filtro speciale in grassetto, corsivo ...
  • Combina cartelle di lavoro e fogli di lavoro; Unisci tabelle in base a colonne chiave; Suddividi i dati in più fogli; Conversione in batch xls, xlsx e PDF...
  • Più di 300 potenti funzionalità. Supporta Office / Excel 2007-2019 e 365. Supporta tutte le lingue. Facile distribuzione nella tua azienda o organizzazione. Funzionalità complete Prova gratuita di 30 giorni. Garanzia di rimborso di 60 giorni.
scheda kte 201905

Scheda Office porta l'interfaccia a schede a Office e semplifica notevolmente il lavoro

  • Abilita la modifica e la lettura a schede in Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
  • Apri e crea più documenti in nuove schede della stessa finestra, piuttosto che in nuove finestre.
  • Aumenta la produttività del 50% e riduce centinaia di clic del mouse ogni giorno!
fondo officetab
Commenti (21)
Ancora nessuna valutazione. Puoi essere il primo a votare!
Jad
Jad
Questo commento è stato fatto dal moderatore sul sito
Ciao, grazie mille per il tuo aiuto, ma ho un piccolo problema che è che non ho solo una colonna ma diverse (circa 100 colonne) c'è un modo per cambiare la formula per accogliere la modifica nelle colonne quindi O può ottenere la somma per le colonne ABC e così via. Grazie per l'aiuto
Rispondi
Soleggiato
Soleggiato     Jad
Questo commento è stato fatto dal moderatore sul sito
Grazie per il tuo messaggio. Se vuoi sommare i primi n valori su più colonne continue, cambia semplicemente A nell'ultima colonna che usi. Ad esempio, somma i primi 5 valori dalla colonna A alla colonna D, applica questa formula =SUM(A1:INDIRECT(CONCATENATE("D",F2))).
Visualizza allegati (1 / 5)
Rispondi
Report
Ryan Sojak
Ryan Sojak
Questo commento è stato fatto dal moderatore sul sito
Posso usare questa sintassi mentre mi riferisco a un foglio diverso?
Rispondi
Narendra
Narendra     Ryan Sojak
Questo commento è stato fatto dal moderatore sul sito
Se vuoi sommare facendo riferimento ad uno Shet Differente nella formula CONCATENATE scrivi [ NOME FOGLIO+COLONNA+RIGA]
Formula=SOMMA(INDIRETTO(CONCATENATE("Foglio1!","D",1)):INDIRETTO(CONCATENATE("Foglio1!","D",5)))
Rispondi
Jared
Jared
Questo commento è stato fatto dal moderatore sul sito
Cosa succede se voglio contare verso l'alto da una cella anziché verso il basso? Nell'esempio sopra, la formula inizia con A1 e va verso il basso. Diciamo che voglio un intervallo di 4 celle, iniziando con Cella A10 e andando ad A6. Grazie!
Rispondi
Cathy
Cathy     Jared
Questo commento è stato fatto dal moderatore sul sito
Jared - Vedi il mio post più recente per come risolverlo.
Rispondi
Marco
Marco     Cathy
Questo commento è stato fatto dal moderatore sul sito
Ho anche questo problema verso l'alto ma non riesco a vedere l'altro tuo post per la soluzione. Mi dai il link del post per favore? Grazie molto!
Rispondi
Soleggiato
Soleggiato     Marco
Questo commento è stato fatto dal moderatore sul sito
Ciao, ecco una formula che può aiutarti a calcolare verso l'alto.
=MEDIA(INDIRETTO(CONCATENATE("A",COUNT(A:A)-B1+1)):INDIRETTO(CONCATENATE("A",COUNT(A:A)))))
A: A è la colonna di cui vuoi calcolare i valori medi, B1 è la cella che definisce l'intervallo nella colonna A da calcolare.
Visualizza allegati (1 / 5)
Rispondi
Report
Marco
Marco     Soleggiato
Questo commento è stato fatto dal moderatore sul sito
Grazie, ma posso sapere perché è necessario utilizzare "-B1 + 1" per favore?
Rispondi
Soleggiato
Soleggiato     Marco
Questo commento è stato fatto dal moderatore sul sito
Ciao, permettimi di fare un esempio dettagliato per spiegartelo. Ci sono 10 righe nella colonna A, voglio ottenere la media degli ultimi 2 valori, in altre parole, fare la media dei valori nella riga 9 e nella riga 10. Ora B1 contiene 2, =COUNT(A:A)-B1+1 lo farà ottieni il risultato 9, COUNT(A:A) otterrà il risultato 10 (l'ultima riga della colonna A). Ora combina altre funzioni per calcolare la media dei valori nella riga 9 e nella riga 10. Spero che questo possa aiutarti.
Report
Cathy
Cathy
Questo commento è stato fatto dal moderatore sul sito
Ottima formula ma perché funziona solo quando si fa riferimento alla prima cella. In altre parole, se si sposta l'intero intervallo verso il basso e la prima cella dell'intervallo è A6 anziché A1, la formula smette di funzionare. Sembra strano.
Rispondi
Cathy
Cathy
Questo commento è stato fatto dal moderatore sul sito
In precedenza ho postato: ottima formula ma perché funziona solo quando si fa riferimento alla prima cella. In altre parole, se si sposta l'intero intervallo verso il basso e la prima cella dell'intervallo è A5 anziché A1, la formula smette di funzionare.
Dopo averci giocato, ho capito perché la formula non funziona quando la riga iniziale non è la riga 1. Excel lo interpreta come l'intervallo da A1 a A4. Se stai iniziando con una riga diversa dalla riga 1, ecco come modificare la formula supponendo che l'intervallo inizi con A5:=MAX(A5:INDIRECT(CONCATENATE("A",ROW(A5)+B2)))
Funziona perché la concatenazione vede il riferimento di riga come fisso nel primo esempio, ma diventa relativo nel secondo esempio. Spero che sia d'aiuto!
Jared - Puoi semplicemente sottrarre 4 invece di aggiungere. =MAX(A5:INDIRECT(CONCATENATE("A",ROW(A5)-B2))) [Supponendo che tu abbia 4 nella cella B2, oppure potresti semplicemente lasciare la formula così com'è e inserire -4 nella cella B2.]
Rispondi
Soleggiato
Soleggiato     Cathy
Questo commento è stato fatto dal moderatore sul sito
Ciao, cathy, grazie per il tuo supplemento, ho testato la tua formula, penso che la formula corretta possa essere =MAX(A5:INDIRECT(CONCATENATE("A",ROW(A5)+B2-1))). Ho aggiornato il tutorial. Grazie ancora.
Rispondi
Report
Xander
Xander
Questo commento è stato fatto dal moderatore sul sito
Ciao domanda veloce. La mia formula =SUMIFS(DATA!$D$5:$D$13914,DATA!$E$5:INDIRECT(CONCATENATE("$E$",C3)),"<"&"1/1/2016") ha un intervallo che proviene da un'altra scheda chiamata "DATI". Continuo a ricevere #VALORE! errori per questa formula. Eventuali suggerimenti? Grazie!
Rispondi
Ibrahim
Ibrahim
Questo commento è stato fatto dal moderatore sul sito
E se la rabbia non fosse una colonna, ma una riga?
Rispondi
rapinare
rapinare     Ibrahim
Questo commento è stato fatto dal moderatore sul sito
È un po' più complicato come ho appena scoperto. È necessario utilizzare una combinazione della funzione CHAR per convertire un numero in una lettera, CONCAT per mettere insieme una lettera e un numero che comprende un indirizzo di cella e la funzione INDIRETTO per riconoscere le stringhe come indirizzi di cella.

Nel mio esempio ho scritto la seguente espressione:

=SUM(INDIRECT(CONCAT("F",ROW())):INDIRECT(CONCAT(CHAR(64+5+$B$31),ROW())))


dove:
Row() è la riga corrente
$B$31 è la cella in cui è memorizzato il numero di colonne da contare
5 è l'offset per la prima colonna da contare

se devi andare oltre la colonna Z, avresti bisogno di una variante meno intuitiva su quell'espressione usando la funzione INDIRIZZO:

=SUM(indirect(concat("F",row())):indirect(ADDRESS(row(),$B$31+5,4)))


dove:
4 indica un riferimento relativo
Rispondi
Francis
Francis
Questo commento è stato fatto dal moderatore sul sito
come si aggiorna l'intervallo di somma utilizzando la cella helper... Ad esempio: se l'intervallo di somma iniziale è = da C1 a M1, come modificare l'intervallo di somma se i nuovi dati sono fino a AB1 utilizzando una cella helper, invece di modificare la formula manualmente ?
Rispondi
Custode
Custode
Questo commento è stato fatto dal moderatore sul sito
C'è un modo per creare una visualizzazione dinamica a intervalli, che isoli tutti gli ordini da un cliente specifico all'interno di un elenco di clienti? Ho guardato più video e nessuno effettivamente fa quello che mi serve. C'è un modo per farlo o dovrei inserire le informazioni necessarie in una tabella controllata e utilizzare le informazioni da quella tabella?
Rispondi
Report
Soleggiato
Soleggiato     Custode
Questo commento è stato fatto dal moderatore sul sito
Ciao, custode. Non ho capito chiaramente la tua domanda. Potresti descrivere la tua domanda o caricare un file per mostrare la domanda in dettaglio? Ma ti consiglio un'utilità chiamata Data Association per te, forse può aiutarti, vai su questo web per i dettagli: https://www.extendoffice.com/product/kutools-for-excel/excel-click-cell-to-filter-based-on-another-workbook.html.
Rispondi
Report
Custode
Custode     Soleggiato
Questo commento è stato fatto dal moderatore sul sito
Grazie per la risposta, è una cosa piuttosto complicata da spiegare, ma alla fine sono riuscito ad arrivare proprio usando la formula di indicizzazione con la formula di corrispondenza.   =INDEX($B:$B;MATCH($C$2;$B:$B;0);):INDEX($N:$N;MATCH($C$2;$B:$B;0)+$C$3-1;)   all'inizio era confuso, ma ora sto bene 
Rispondi
Report
Syed Wasi
Syed Wasi
Questo commento è stato fatto dal moderatore sul sito
Ma se voglio sommare un intervallo di colonne (diciamo l7: l7800) fuori dall'intervallo d7: ct9000 in base alla condizione nella colonna a(a7:a9000) e nella colonna b(b7:b9000). Come faccio a farlo. si prega di avvisare
Rispondi
Non ci sono ancora commenti pubblicati qui
Lasciate i vostri commenti
GUEST
Pubblicazione come ospite
Accedi
×
Valuta questo post:
cancella
0   Personaggi
Posizioni suggerite