tabelle Valutare INDIRECT in un array in Excel




tabelle excel formule (3)

Stai solo cercando di ottenere la SLOPE dalla regressione lineare? In tal caso, è sufficiente utilizzare la funzione SLOPE dopo aver convertito il IFERROR #N/A in spazi vuoti (utilizzando IFERROR in una formula). SLOPE quindi eliminerà gli spazi vuoti. Se si desidera anche l'intercettazione, utilizzare le stesse formule riportate di seguito e sostituire INTERCEPT per SLOPE .

Immagine di intervalli

Le formule sono formule di matrice (utilizzare CTRL + MAIUSC + INVIO) e copiate. Data questa disposizione, la formula semplice (non-dianmica) sarebbe:

=SLOPE(IFERROR(B2:B15,""),$A$2:$A$15)

Se vuoi che siano dinamici, puoi usare INDEX e COUNTA per ottenere la gamma dinamica.

=SLOPE(IFERROR(B2:INDEX(B:B,COUNTA(B:B)),""),$A$2:INDEX($A:$A,COUNTA($A:$A)))

Usa invece un Table

Ancora meglio, è possibile definire questi dati all'interno di una Table e quindi utilizzare le intestazioni per inserire l'intera colonna. Quella formula sarebbe carina e facilmente copiabile.

Ancora usando una formula di matrice qui, ma l'unica variabile è l'intestazione della colonna che viene usata per esaminare la Table1 . Questo sarebbe più resistente agli spazi vuoti nei dati che interromperanno il COUNTA usato sopra.

=SLOPE(IFERROR(INDEX(Table1,,MATCH(M1,Table1[#Headers])),""),Table1[Date])

Sto provando a scrivere una funzione di regressione lineare che fa riferimento dinamicamente alle colonne, in grado di gestire i valori #N/A e funzionerà quando nel tempo verranno aggiunte ulteriori righe. Ecco un set di dati di esempio:

Date        Value 1 Value 2
1/2/1991    #N/A    #N/A
2/4/2002    276.36  346.31
1/7/2003    252     350
1/21/2004   232     345.5
1/6/2005    257     368
2/1/2006    278.24  390.11
2/23/2007   #N/A    380.46
2/11/2008   326.34  383.04
2/12/2009   #N/A    399.9
2/17/2009   334.39  #N/A
1/29/2010   344.24  400.83
1/27/2011   342.88  404.52
2/7/2012    379     417.91
1/23/2013   #N/A    433.35

Ecco la funzione che ho sviluppato finora, basata su questo post sul forum . Calcola la regressione lineare per il valore 1.

=TRANSPOSE(
  LINEST(
    N(
      OFFSET(
        INDIRECT("B2" & ":B" & COUNTA(B:B)),
          SMALL(
            IF(
              ISNUMBER(
                INDIRECT("A2:A" & COUNTA($A:$A)) *
                INDIRECT("B2" & ":B" & COUNTA(B:B))),
              ROW(INDIRECT("B2:B" & COUNTA(B:B))) - ROW(B2)),
            ROW(INDIRECT("1:" & MIN(
              COUNT(INDIRECT("A2:A" & COUNTA($A:$A))),
              COUNT(INDIRECT("B2:B" & COUNTA(B:B))))))), 0, 1)),
    N(
      OFFSET(
        INDIRECT("A2:A" & COUNTA($A:$A)),
          SMALL(
            IF(
              ISNUMBER(
                INDIRECT("A2:A" & COUNTA($A:$A)) *
                INDIRECT("B2:B" & COUNTA(B:B))),
              ROW(INDIRECT("B2:B" & COUNTA(B:B))) - ROW(B2)),
            ROW(INDIRECT("1:" & MIN(
              COUNT(INDIRECT("A2:A" & COUNTA($A:$A))),
              COUNT(INDIRECT("B2:B" & COUNTA(B:B))))))), 0, 1)),
    TRUE, FALSE))

Con il modo in cui è attualmente scritto, trascinando il mio array a destra per risolvere per Value 2 è necessario un aggiornamento manuale della formula. Tutto tra virgolette nelle formule INDIRECT deve essere modificato manualmente da B a C Ho 40 colonne di dati, quindi ho provato a rendere la formula completamente dinamica utilizzando ADDRESS , ROW e COLUMN :

=TRANSPOSE(
  LINEST(
    N(
      OFFSET(
        INDIRECT(ADDRESS(2, COLUMN(B2)) & ":" & ADDRESS(COUNTA(B:B), COLUMN(B2))),
        SMALL(
          IF(
            ISNUMBER(
              INDIRECT("A2:A" & COUNTA($A:$A)) *
              INDIRECT(ADDRESS(2, COLUMN(B2)) & ":" & ADDRESS(COUNTA(B:B), COLUMN(B2)))),
            ROW(INDIRECT(ADDRESS(2, COLUMN(B2)) & ":" & ADDRESS(COUNTA(B:B), COLUMN(B2)))) - ROW(B2)),
          ROW(INDIRECT("1:" & MIN(
            COUNT(INDIRECT("A2:A" & COUNTA($A:$A))),
            COUNT(INDIRECT(ADDRESS(2, COLUMN(B2)) & ":" & ADDRESS(COUNTA(B:B), COLUMN(B2)))))))), 0, 1)),
    N(
      OFFSET(
        INDIRECT("A2:A" & COUNTA($A:$A)),
          SMALL(
            IF(
              ISNUMBER(
                INDIRECT("A2:A" & COUNTA($A:$A)) *
                INDIRECT(ADDRESS(2, COLUMN(B2)) & ":" & ADDRESS(COUNTA(B:B), COLUMN(B2)))),
              ROW(INDIRECT(ADDRESS(2, COLUMN(B2)) & ":" & ADDRESS(COUNTA(B:B), COLUMN(B2)))) - ROW(B2)),
            ROW(INDIRECT("1:" & MIN(
              COUNT(INDIRECT("A2:A" & COUNTA($A:$A))),
              COUNT(INDIRECT(ADDRESS(2, COLUMN(B2)) & ":" & ADDRESS(COUNTA(B:B), COLUMN(B2)))))))), 0, 1)),
    TRUE, FALSE))

Questo mi dà #REF! . Quando eseguo una valutazione dettagliata della formula, sembra che il problema si verifichi quando Excel valuta COLUMN . Introduce le parentesi graffe alla formula, che si propagano attraverso il resto della valutazione INDIRECT . Ecco un rapido confronto:

Formula originale:

INDIRECT("B2:B15")

Formula dinamica:

INDIRECT({"$B$2:$B$15"})

Questo valore viene valutato come #VALUE e, a quel punto, il resto della formula viene interrotto. Esiste un modo per forzare Excel a non utilizzare le parentesi graffe in questa valutazione o esiste un modo migliore per eseguire questo calcolo?


Hai intenzione di voler eliminare il più possibile l'uso della funzione INDIRETTO ; certamente per quanto riguarda la sostituzione dei riferimenti di colonna per gli equivalenti di stringa. Sembra che molti possano essere sostituiti con una forma di coppie di funzioni INDICE / MATCH .

=TRANSPOSE(
  LINEST(
    N(
      OFFSET(B2:INDEX(B:B, MATCH(1E+99,$A:$A )),
          SMALL(
            IF(
              ISNUMBER(
                $A2:INDEX($A:$A, MATCH(1E+99,$A:$A )) *
                B2:INDEX(B:B, MATCH(1E+99,$A:$A ))),
              ROW(B2:INDEX(B:B, MATCH(1E+99,$A:$A ))) - ROW(B2)),
            ROW(INDIRECT("1:" & MIN(
              COUNT($A2:INDEX($A:$A, MATCH(1E+99,$A:$A ))),
              COUNT(B2:INDEX(B:B, MATCH(1E+99,$A:$A ))))))), 0, 1)),
    N(
      OFFSET(
        $A2:INDEX($A:$A, MATCH(1E+99,$A:$A )),
          SMALL(
            IF(
              ISNUMBER(
                $A2:INDEX($A:$A, MATCH(1E+99,$A:$A )) *
                B2:INDEX(B:B, MATCH(1E+99,$A:$A ))),
              ROW(B2:INDEX(B:B, MATCH(1E+99,$A:$A ))) - ROW(B2)),
            ROW(INDIRECT("1:" & MIN(
              COUNT($A2:INDEX($A:$A, MATCH(1E+99,$A:$A ))),
              COUNT(B2:INDEX(B:B, MATCH(1E+99,$A:$A ))))))), 0, 1)),
    TRUE, FALSE))

Riempire a destra se necessario e tenere bloccata la colonna A mentre i riferimenti dell'intervallo di celle della colonna B passano alla colonna C, D, ecc.

È probabile che la sostituzione di un'altra funzione possa scambiare almeno alcune delle funzioni OFFSET utilizzate per una funzione INDICE appropriata, ma la formula sembra funzionare bene come lo è ora.


Sembra che tu possa utilizzare la seguente formula di array più breve, non volatile ** :

=LINEST(INDEX(B:B,N(IF(1,MODE.MULT(IF(ISNUMBER(B2:B15),{1,1}*ROW(B2:B15)))))),INDEX($A:$A,N(IF(1,MODE.MULT(IF(ISNUMBER(B2:B15),{1,1}*ROW(B2:B15)))))))

B2: B15 può essere definito dinamicamente se desiderato come da soluzione di Jeeped.

Saluti





excel-formula