Les valeurs manquantes dans MS Excel la fonction DROITEREG, TENDANCE, LOGREG et fonctions de la CROISSANCE
Je suis en utilisant la CROISSANCE (ou la fonction DROITEREG ou TENDANCE ou LOGREG, font tous la même difficulté) fonction dans Excel 2003. Mais il ya un problème que si certaines données sont manquantes, la fonction refuse de donner suite:
Vous pouvez télécharger le fichier ici.
Est-il une solution de contournement? La recherche simple et élégant.
- Je ne veux pas être évident solution pour se débarrasser de la valeur manquante - que signifie pour supprimer la colonne et ce serait aussi dommage le graphique, et il pourrait créer des problèmes dans mes autres tables où j'ai plus de lignes et de données manquantes dans les différentes colonnes. Autre solution consiste à utiliser des données pour la régression et l'autre pour le graphique, mais encore une fois, c'est ennuyeux et ne fait désordre dans la feuille!!
- Est-il une manière de dire excell - cette valeur est NA?
- Une autre idée serait d'ignorer la valeur manquante(s) dans l'expression. Est-il possible de traiter un ensemble de cellules qui n'est pas continue? Comme au lieu de
=GROWTH($B2:$AH2; $B1:$AH1; B1)que dans mon exemple, utiliser quelque chose comme:=GROWTH({$B2:$I2,$K2:$AH2}; {$B1:$I1,$K1:$AH1}; B1) - J'aimerais bien évidemment d'éviter l'écriture de mes propres expressions. J'ai besoin d'expliquer cela à mes collègues comment faire tout cela, et il serait beaucoup plus compliqué. Je veux d'un simple et élégant.
OriginalL'auteur TMS | 2013-01-31
Vous devez vous connecter pour publier un commentaire.
Je sais que c'est vieux...mais dans le cas où vous ou quelqu'un d'autre pourrait encore être à la recherche d'une réponse, avez-vous essayé d'utiliser le
FORECASTfonction? Il va calculer la tendance avec des valeurs manquantes (tant qu'il n'y a pas "#N/A" cellules).Dans mon cas, j'avais besoin de créer une instantanés graphique avec des valeurs manquantes, mais j'ai aussi besoin de calculer une tendance à partir des données. Alors d'abord, j'avais lien du graphique pour un jeu de données qui a placé un #N/A pour chaque valeur manquante:
par exemple,
IF(ISBLANK(B2),NA(),B2)Mais je vais calculer les prévisions de chiffres avec les données d'origine:
=FORECAST(B1,$B2:$AH2,$B1:$AH1)À moins que je me manque quelque chose, qui doit prendre soin d'elle. En gros, vous avez jusqu'à la fin avec deux rangées de nombres identiques, mais on a des blancs pour la
FORECASTde calcul, et de l'autre la remplace chaque vide avec unNA()pour le graphique.OriginalL'auteur timdow
Cela s'avère être le "trivial" si vous savez le truc.
D'utiliser la fonction DROITEREG avec des valeurs manquantes, vous devez créer le X-matrice (r lignes par colonnes c) et Y le vecteur (r lignes sur une colonne) comme d'habitude. Vous devez également créer une colonne supplémentaire dans le X-matrice de servir comme un indicateur de la variable. Place cette colonne immédiatement à gauche de l'X de la matrice. Donc, si le X de la matrice de départ dans la colonne B, mettre la colonne supplémentaire dans la colonne A. Définissez cette valeur de l'indicateur à zéro pour chaque ligne que vous souhaitez omettre. Définissez cette valeur de l'indicateur pour chaque ligne que vous souhaitez inclure. Multipliez toutes les autres colonnes dans le X et la matrice de Y-vecteur par cet indicateur variable. De mettre cette nouvelle augmentée de X de la matrice et de nouvelles Y vecteur d'ailleurs dans la feuille de calcul. Vous devriez maintenant avoir un nouveau X-matrix (lignes r par c+1 colonnes) et Y-vecteur avec des lignes de zéros tout droit pour chaque ligne être omis. C'EST ESSENTIEL!
Maintenant utiliser la fonction DROITEREG comme d'habitude , la spécification de l'ensemble de la Y-vecteur et de l'expansion de r×(c+1) X-matrix (avec l'indicateur de la colonne inclus comme les deux premiers paramètres de la fonction, le "Faux" (zéro) comme troisième paramètre "TRUE" (on) ou "FALSE" (zéro) comme quatrième paramètre de la fonction. La bonne estimation des paramètres apparaissent dans la première ligne de la sortie de la fonction DROITEREG. Tous les autres DROITEREG les valeurs de sortie sont mal, sauf la valeur de la cinquième ligne de la deuxième colonne (somme résiduelle des carrés) si vous avez spécifié "VRAI" pour obtenir des statistiques.
Si vous avez spécifié le quatrième paramètre de la fonction en tant que "VRAI" pour obtenir des statistiques, vous devez corriger le fichier de sortie pour les valeurs incorrectes. Les valeurs des lignes 2,3 et 4 de l'étendue de sortie sont incorrectes; la valeur de la ligne 5, colonne 1, est également incorrect. Vous devez les fixer.
Faire une copie de la première ligne de la sortie de la fonction DROITEREG ailleurs dans la feuille de calcul. Si vous avez spécifié "VRAI" pour les statistiques, puis la réserve de quatre lignes vides en dessous de cette copie. Copie de la ligne 5 colonne 2 valeur de l'original de la sortie de la fonction DROITEREG à la ligne 5 colonne 2 de la nouvelle sortie de l'espace
Première étape: Calculer le bon nombre de degrés de liberté de remplacer la valeur de la ligne 4 colonne 2 de la sortie de la fonction DROITEREG. Trouver le nombre de paramètres dans le modèle; c'est c+1. Vous pouvez utiliser la fonction COUNT pour compter le nombre de colonnes dans la nouvelle X-matrice. Ajoutez ensuite toutes les valeurs dans la colonne " indicateur de la valeur de X de la matrice. Supposons que quatre lignes ont toutes les valeurs nulles. Utilisation de la fonction SOMME: cela donne r – 4 = nombre de lignes avec des "1" dans la colonne " indicateur. La bonne degrés de liberté est la différence: SOMME(indicateur de colonne) – COMTE(augmentée de X-colonnes de la matrice). C'est la valeur qui doit être placé sur la ligne 4 colonne 2 de la nouvelle sortie de l'espace.
Étape Deux: Correction de la ligne 2 et la ligne 3 de la colonne 2. Diviser le mal d.f. (ligne 4 colonne 2) à l'origine, la fonction DROITEREG sortie par le bon d.f. (ligne 4 colonne 2) dans le nouvel espace de sortie. Prendre la racine carrée de ce quotient. Plusieurs les valeurs de la ligne 2 et la ligne 3 de la colonne 2 dans l'original de la sortie de la fonction DROITEREG de l'espace par ce facteur de correction pour obtenir le corriger les erreurs-types des paramètres et le corriger l'erreur standard de Y.
Étape Trois: Corriger la somme des carrés de la régression. L'original de la sortie de la fonction DROITEREG a la valeur de la somme des carrés due à la régression non corrigées pour la moyenne de la ligne 5, colonne 1, de la sortie; nous voulons que la somme des carrés de la régression corrigé pour la moyenne. Nous avons besoin de calculer la correction de la moyenne. C'est la somme de la Y le vecteur des valeurs au carré, divisé par la somme de la colonne " indicateur de valeurs. Soustraire de la valeur de la ligne 5 colonne 1 de l'original de la sortie de la fonction DROITEREG et de mettre la réponse dans la ligne 5, colonne 1, de la nouvelle sortie de l'espace.
Étape Quatre: Corriger le F-ratio dans la ligne 4 colonne 1. Nous avons besoin de calculer la moyenne des carrés due à la régression et en raison de la forme de résidus. La moyenne des carrés due à la régression (numérateur dans le F-ratio) est la valeur de la ligne 5, colonne 1, de la nouvelle sortie de l'espace divisé par c, le nombre de colonnes dans l'original X de la matrice avant l'augmentation. Le carré moyen dû aux résidus (dénominateur dans le F-ratio) est de la ligne 5 colonne 2 de la nouvelle sortie de l'espace divisé par la ligne 4 colonne 2 de la nouvelle sortie de l'espace. Calculer le F-ratio à partir de ces deux valeurs intermédiaires et place le résultat dans la ligne 4 colonne 1 de la nouvelle sortie de l'espace.
Étape cinq: Corriger le R-carré de la valeur à la ligne 3 de la colonne 1. C'est la 1 - (ligne 5 colonne 2, divisée par la somme de la ligne 5, colonne 1 et de la ligne 5 colonne 2), en utilisant les valeurs de la nouvelle sortie de l'espace.
Vérifier votre travail: Faire une copie de l'accroissement de la matrice X et Y du vecteur ailleurs dans la feuille de calcul. Remplacer toutes les entrées avec zéro pour ces lignes qui ont zéro de l'indicateur de la variable. Supprimer toutes les cellules dans les lignes avec des zéros, le déplacement des cellules. Vous devriez maintenant avoir un X-matrice et Y du vecteur avec le moins de lignes, mais pas de valeurs manquantes. Supprimer la colonne " indicateur. Maintenant utiliser la fonction DROITEREG de sapin, un modèle de régression à cet ensemble réduit de données, mais cette fois-ci le troisième paramètre à TRUE (inclure une constante). Ces résultats doivent être identiques pour les résultats que vous avez dans le nouvel espace de sortie.
Ce peut-être une longue réponse, mais c'est un très bon produit. Merci
OriginalL'auteur Bill Porter
Ma solution se compose de 2 parties:
=NA()fonction - il produit#N/Aerreur, et ces types d'erreurs sont gérées par des graphiques exactement comme vous le souhaitez: la ligne est interpolée entre les points disponibles autour de la manque un. Lire la suite ici: http://www.j-walk.com/ss/excel/usertips/tip024.htmGROWTHvaleurs calculées. Et il gère correctement#N/A'. Assurez-vous que la tendance s'adapte à vos données, il suffit de remplacer#N/Atemporairement avec une moyenne de deux voisines (297 pour l'exemple) - il calculeGROWTHde la série alors, et vous voyez qu'il correspond exactement a ajouté une courbe de tendance. Lire ici sur les courbes de tendance: http://office.microsoft.com/en-001/excel-help/add-a-trendline-to-a-chart-HP005198462.aspx et http://www.computergaga.com/excel/2003/intermediate/charts/add_a_trendline.htmlVotre dossier avec les solutions appliquées est partagée: https://www.dropbox.com/s/j7htrk9ih2jtcq6/TrendlineNA.xls
De l'espoir qui a été utile!
1)
=NA()s'applique uniquement à la graphique, de ne PASGROWTHcalculs. La fonction DOIT toujours avoir aucune erreur dans la source de données 2) Pour votre initiale de l'échantillon lignes du fichier sont identiques, reportez-vous à l'écran (j'ai remplacé temporairement cellule vide avec 297): floomby.ru/s1/FZPGS Donc j'ai 2 options pour vous: 1) trouver la ligne de tendance de type 2) comprennent des vérifications d'erreur àGROWTHles arguments de la fonction, mais je suis sûr que les formules ne sera pas simple.ad 1) puis
=NA()est pas pour moi - je n'ai pas de problèmes avec le graphique! Seulement avecGROWTHcalcul ad 2) mais pourquoi les deux exp. les lignes de différer mon exemple ci-dessus? Il n'y a pas plus approprié de la ligne de tendance de type. Et qu'entendez-vous par "des vérifications d'erreur à la CROISSANCE"arguments de la fonction?le but de
=NA()est uniquement pour rendre le graphique en continu comme dans mon exemple de fichier. Et les "contrôles d'erreur à la CROISSANCE des arguments de la fonction" signifie pour les points manquants vous avez besoin pour calculer l'interpolation des valeurs à l'aide des formules - soit à l'intérieur deGROWTHou à l'aide d'autres données en ligne pour ces.1) je ne voulais pas faire de graphique en continu, au contraire; 2) ça ne ressemble pas à une solution, parce que, pour cela, vous avez besoin de régression de première et nous sommes dans un cercle..
OriginalL'auteur Peter L.