Riferimento Dinamico Intervallo Denominato in Formula Excel

Ho una tabella in Excel con le intestazioni di colonna che corrisponde a una parte di una dinamica intervallo denominato altrove nella mia cartella di lavoro. Per esempio, io ho queste intestazioni di colonna: “10”, “20”, ecc…. e queste dinamiche intervalli denominati: “ExampleRange10”, “ExampleRange2”, etc. Mi piacerebbe entrare in un VLookup formula che fa riferimento a ExampleRange10 concatenando la stringa “ExampleRange” e l’intestazione della colonna “10”. Questo mi avrebbe permesso di estendere semplicemente la formula in tutte le colonne della tabella, invece di digitare manualmente “ExampleRange10”, “ExampleRange20”, etc. in ogni colonna della formula.

Sono consapevole della funzione INDIRETTA, e lo hanno utilizzato con successo in passato con intervalli denominati, ma non mi sembra di lavorare con la dinamica intervallo denominato in questo caso. Sto indovinando questo è una sfumatura che ha qualcosa a che fare con la dinamica intervalli definiti da Excel (che non si presentano nell’intervallo denominato a tendina a sinistra della barra della formula, e hanno alcune caratteristiche interessanti, in VBA, per esempio). C’è un modo posso usare la formula INDIRETTO in combinazione con una dinamica intervallo denominato, o c’è un altro modo che posso andare per risolvere questo problema?

Modifica: Qui sono le esatte formule utilizzate.

Questa è la formula principale: =VLOOKUP(B2,INDIRECT("ExampleRange"&C1),2,FALSE) dove C1 contiene “10” e la formula per il mio dinamica intervallo denominato “ExampleRange10” è: =OFFSET(Sheet1!$F$2,0,0,COUNTA(Sheet1!$F$2:$F$25),2). La formula principale restituisce “#RIF!”, ma funziona correttamente quando tolgo la dinamica intervallo denominato formula e definire semplicemente “ExampleRange10” statica gamma.

  • Si potrebbe si prega di fornire l’ESATTA formula che non funziona?
  • Si potrebbe forse utilizzare una combinazione di offset() e colonna(), a seconda del layout.
  • Vedere modifica.
  • =VLOOKUP(B5,INDIRECT("range"&C1),1,0) funziona bene per me in Excel 2007. Sei sicuro che il resto della formula non ha errori, per esempio, 3 ° parametro – numero di colonna in campo per il ritorno?
  • Eh. E la tua “range10” è dinamico? Ho ricontrollato il mio formule e gli intervalli e non riuscivo a trovare eventuali errori. Quando vado a Nome del Manager e fare clic sulla “si Riferisce a” casella, sia la gamma dinamica e la statica gamma di evidenziare la stessa regione, quindi non credo che il problema è con la formula scarto per la gamma dinamica…e come ho detto prima, esattamente la stessa formula VLOOKUP funziona quando ho sostituito l’inizio della statica gamma di testo per “ExampleRange”, che è l’inizio del testo per la gamma dinamica, in modo che il VLOOKUP formula sembra funzionare troppo…
  • hai ragione – doppio dinamiche non funzionano anzi… forse intermedio cella in cui si ottenere "ExampleRange"&C1 per ulteriori indiretta è un’opzione?
  • Ho provato, ma senza fortuna. Sembra che il problema di fondo è che INDIRECT non funziona con la dinamica di intervalli con nome… attualmente sto cercando nella scrittura di una funzione definita dall’utente in VBA come una soluzione alternativa. Vedere link

InformationsquelleAutor Kyle Wurtz | 2013-02-13



8 Replies
  1. 6

    Come ho potuto dire dopo aver fatto ulteriori ricerche, Excel INDIRECT funzione semplicemente non funziona con i campi dinamici. Ci potrebbe essere un modo intelligente per ottenere utilizzando INDIRECT e di attaccare per non VBA di Excel mondo, ma io sono a conoscenza di tale modo. Invece, ho finito per creare una funzione definita dall’utente molto simile a quella descritta qui. Ho modificato il mio principale formula di leggere =VLOOKUP(B2,DINDIRECT("ExampleRange"&C1),2,FALSE), dove DINDIRECT è il nome della funzione VBA che ho creato.

    L’unica nota stonata (che possono o non possono essere aspetti negativi a seconda di come la si guarda) per questa alternativa è che la cartella di lavoro deve essere salvato come un macro-enabled cartella di lavoro e l’utilizzo di una funzione personalizzata non è molto auto-documentazione e richiede un po ‘ di spiegazione per gli altri utenti. Tutto considerato, però, questa era una soluzione accettabile per me.

    Per il link-contrari, ecco il codice:

    Public Function DINDIRECT(sName As String) As Range
         Dim nName As Name
    
         On Error Resume Next
              Set nName = ActiveWorkbook.Names(sName)
              Set nName = ActiveSheet.Names(sName)
         On Error GoTo 0
    
         If Not nName Is Nothing Then
              Set DINDIRECT = nName.RefersToRange
         Else
              DINDIRECT = CVErr(xlErrName)
    End Function
    

    Nota: anche se questa soluzione ha funzionato, non ho intenzione di accettare la mia risposta, perché non voglio scoraggiare altri dal distacco di soluzioni migliori. Inoltre, io sono nuovo del sito, quindi scusa se ti sto rompendo etichetta codici rispondendo alla mia domanda…io ho pensato di condividere la soluzione esatta che ho usato nel caso in cui altri lo trovano utile.

  2. 5

    Mi ha colpito questo esatto muro di mattoni di recente e la risposta, come avrai già intuito, è semplicemente che non è possibile riferimento dinamico intervalli denominati INDIRETTA.

    Tuttavia, è possibile utilizzare la gamma dinamica formula stesso INDIRETTO ” dell’argomento, ma questo non serve per quello che vuoi fare. Un po ‘ di una PITA dal momento che è il tipo di funzionalità che sarebbe molto utile.

    • Grazie per la conferma che INDIRECT non funziona con dinamica intervalli denominati. Io sono pienamente d’accordo che questa funzionalità potrebbe essere utile, ed è particolarmente frustrante, dato come è semplice la soluzione codice (e quindi dovrebbe essere molto facile per loro di incorporare) e come spesso il problema viene in su (almeno per me).
    • Esattamente. È doppiamente frustrante quando non si hanno a venire con una soluzione che non uso, o Udf (o VBA in qualche modo), come sono spesso tenuti a fare.
  3. 1

    Se i tuoi dati con intestazioni, ad esempio 10, 20 ecc…. quindi non è necessario l’uso Indiretto. Perché non usare Indice/Match per selezionare i dati che avete bisogno di?

    Nome di tutta la tabella ExampleRanges, per esempio, e utilizzare questa formula:

    Index(ExampleRanges, match(B2, index(ExampleRanges, , 1), 0), match(C1, index(ExampleRanges, 1,), 0))
    
  4. 0

    Testato, ma penso che questo dovrebbe funzionare:

    funzione definita dall’utente per restituire l’indirizzo del tuo dinamicamente intervallo denominato:

    Function Named_Range_Address(Range_Name As Range, _ 
        Optional SheetName As Boolean) As String 
    
        Dim strName As String 
        Application.Volatile 
    
        If SheetName = True Then 
            strName = "'" & Range_Name.Parent.Name & "'!" & Range_Name.Address 
        Else 
            strName = Range_Name.Address 
        End If 
    
        Named_Range_Address = strName 
    End Function 
    

    allora si dovrebbe essere in grado di utilizzare la formula vlookup:

    =VLOOKUP(B2,INDIRECT(named_range_address("ExampleRange"&C1,TRUE)),2,FALSE)
    
    • Grazie per il suggerimento. Ho visto una funzione come questa da qualche parte, quando sono stato googling, ma questa particolare funzione richiede che il primo argomento sia di tipo Gamma, ma dato che io sono la concatenazione di stringhe “ExampleRange” e il testo in C1, ho un argomento di tipo Stringa. Tuttavia, il vostro suggerimento di utilizzare una funzione definita dall’utente è proprio sulla. Ho trovato un’altra funzione che prende il RangeName As String qui, e che ha funzionato bene per me.
  5. 0

    So che questo è abbastanza vecchio, ma ho appena imbattuto in questo e ho pensato di aggiungere una soluzione che evita il VBA di codifica nel caso in cui aiuta chiunque si imbatte in questo:

    =VLOOKUP(B2,CHOOSE(C1/10,example10,example20,example30,example40),2,0)
    

    Questo sta assumendo la convenzione di denominazione essere 10,20,30,ecc e non sarà l’ideale per centinaia di intervalli.

  6. 0

    Oggi sono stato armeggiare con Excel intervalli denominati, e ho scoperto che, se è vero che non è possibile calcolare il nome della gamma nel INDIRECT() chiamata, è ancora possibile ottenere un vero e proprio “Excel-way” con l’aggiunta di un passaggio intermedio: basta creare qualche cella nascosta in cui si calcola l’intervallo denominato.

    Per esempio, dire che A1 contiene la “dinamica” del nome di intervallo, poi nella A2 utilizzare la formula = "ExampleRange" & A1, e ora avete la gamma completa di nome, che è possibile utilizzare come = INDIRECT(A2).

    • Non è tanto il nome stesso dinamico, che è il problema, è quando l’intervallo a cui si riferisce è dinamico.
  7. 0

    Aggiunta di un tocco di nuovo, è possibile utilizzare un intervallo denominato con l’Indirizzo e Indiretti funzioni. Ho un caso in cui io sono l’impostazione di intervalli con nome per una serie di tabelle e sto usando il seguente:

    Named Range: WWDH-FF-PI which points to Linear!$A$19 (first cell in table)
    

    per ottenere l’indirizzo:
    $T$56: =INDIRIZZO(MATCH(S56,Lineare!A:Un,0),1,1,1,”Lineare”)

    Quindi utilizzare la funzione offset copiato più volte per creare una tabella pivot:

    =OFFSET(INDIRECT($T$56),C5,$T$57-1)
    

    Così, le funzioni di Indirizzo possono essere incorporati (o confezionati) in funzione Indiretta di creare una dinamica indirizzo di cella.

    • Che funziona, ma purtroppo il problema si verifica con dinamica intervalli denominati, cioè un intervallo denominato, che si riferisce ad un numero variabile di cellule, di solito a seconda del loro contenuto.
  8. 0

    So che questo è un vecchio thread, ma ho avuto lo stesso problema, quindi forse la mia soluzione può aiutare le persone in futuro.

    In pratica, ho creato una Macro che vuoi eliminare e ri-definire l’intervallo su salva, e dargli un nome. Pertanto, la funzione INDIRETTO, funzionerebbe come la gamma dinamica. Tutto quello che dovete fare è salvare la cartella di lavoro dopo l’aggiunta di eventuali valori a intervalli

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    
    Dim a, b, c, d, e, f As Integer
    Dim data As Worksheet
    
    Set data = ThisWorkbook.Worksheets("Data")
    
    a = data.Range("A" & Rows.count).End(xlUp).row
    b = data.Range("B" & Rows.count).End(xlUp).row
    c = data.Range("C" & Rows.count).End(xlUp).row
    d = data.Range("D" & Rows.count).End(xlUp).row
    e = data.Range("E" & Rows.count).End(xlUp).row
    f = data.Range("F" & Rows.count).End(xlUp).row
    
    
    
    ActiveWorkbook.Names("KP").Delete
    ActiveWorkbook.Names("KPT").Delete
    ActiveWorkbook.Names("AP").Delete
    ActiveWorkbook.Names("APT").Delete
    ActiveWorkbook.Names("DISC").Delete
    ActiveWorkbook.Names("SEATS").Delete
    
    ActiveWorkbook.Names.Add Name:="KP", RefersTo:="=Data!$A$2:$A$" & a
    ActiveWorkbook.Names.Add Name:="KPT", RefersTo:="=Data!$B$2:$B$" & b
    ActiveWorkbook.Names.Add Name:="AP", RefersTo:="=Data!$C$2:$C$" & c
    ActiveWorkbook.Names.Add Name:="APT", RefersTo:="=Data!$D$2:$D$" & d
    ActiveWorkbook.Names.Add Name:="DISC", RefersTo:="=Data!$E$2:$E$" & e
    ActiveWorkbook.Names.Add Name:="SEATS", RefersTo:="=Data!$F$2:$F$" & f
    
    End Sub
    

Lascia un commento