{"id":4749,"date":"2023-07-08T08:26:23","date_gmt":"2023-07-08T08:26:23","guid":{"rendered":"https:\/\/statorials.org\/pt\/correlacao-cruzada-do-excel\/"},"modified":"2023-07-08T08:26:23","modified_gmt":"2023-07-08T08:26:23","slug":"correlacao-cruzada-do-excel","status":"publish","type":"post","link":"https:\/\/statorials.org\/pt\/correlacao-cruzada-do-excel\/","title":{"rendered":"Como calcular a correla\u00e7\u00e3o cruzada no excel"},"content":{"rendered":"<p><\/p>\n<hr>\n<p><span style=\"color: #000000;\"><strong>A correla\u00e7\u00e3o cruzada<\/strong> \u00e9 uma forma de medir o grau de similaridade entre uma s\u00e9rie temporal e uma vers\u00e3o defasada de outra s\u00e9rie temporal.<\/span><\/p>\n<p> <span style=\"color: #000000;\">Esse tipo de correla\u00e7\u00e3o \u00e9 \u00fatil para calcular porque pode nos dizer se os valores em uma s\u00e9rie temporal s\u00e3o preditivos de valores futuros em outra s\u00e9rie temporal.<\/span><\/p>\n<p> <span style=\"color: #000000;\">Em outras palavras, pode nos dizer se uma s\u00e9rie temporal \u00e9 um indicador avan\u00e7ado para outra s\u00e9rie temporal.<\/span><\/p>\n<p> <span style=\"color: #000000;\">O exemplo a seguir mostra como calcular a correla\u00e7\u00e3o cruzada entre duas s\u00e9ries temporais no Excel.<\/span><\/p>\n<h2> <span style=\"color: #000000;\"><strong>Exemplo: como calcular a correla\u00e7\u00e3o cruzada no Excel<\/strong><\/span><\/h2>\n<p> <span style=\"color: #000000;\">Digamos que temos as duas s\u00e9ries temporais a seguir no Excel que mostram o gasto total com marketing (em milhares) de uma determinada empresa, bem como a receita total (em milhares) por 12 meses consecutivos:<\/span> <\/p>\n<p><img decoding=\"async\" loading=\"lazy\" class=\" wp-image-37074 aligncenter\" src=\"https:\/\/statorials.org\/wp-content\/uploads\/2023\/08\/crosscor1.png\" alt=\"\" width=\"486\" height=\"452\" srcset=\"\" sizes=\"auto, \"><\/p>\n<p> <span style=\"color: #000000;\">Podemos calcular a correla\u00e7\u00e3o cruzada para diferentes defasagens entre as duas s\u00e9ries temporais digitando a seguinte f\u00f3rmula na c\u00e9lula <strong>E2<\/strong> :<\/span><\/p>\n<pre style=\"background-color: #ececec; font-size: 17px;\"> <span style=\"color: #000000;\"><strong>=CORREL(OFFSET( <span style=\"color: #3366ff;\">A$2:A$13<\/span> ,0,0,COUNT( <span style=\"color: #3366ff;\">A$2:A$13<\/span> )- <span style=\"color: #ff0000;\">D2<\/span> ,1),OFFSET( <span style=\"color: #800080;\">B$2:B$13<\/span> , <span style=\"color: #ff0000;\">D2<\/span> ,0,COUNT( <span style=\"color: #800080;\">B$2:B$13<\/span> )- <span style=\"color: #ff0000;\">D2,1<\/span> ))\n<\/strong><\/span><\/pre>\n<p> <span style=\"color: #000000;\">Podemos ent\u00e3o clicar e arrastar esta f\u00f3rmula para as c\u00e9lulas restantes na coluna E:<\/span> <\/p>\n<p><img decoding=\"async\" loading=\"lazy\" class=\"aligncenter wp-image-37075\" src=\"https:\/\/statorials.org\/wp-content\/uploads\/2023\/08\/crosscor2.png\" alt=\"correla\u00e7\u00e3o cruzada no Excel\" width=\"535\" height=\"434\" srcset=\"\" sizes=\"auto, \"><\/p>\n<p> <span style=\"color: #000000;\">Pelo resultado podemos ver:<\/span><\/p>\n<ul>\n<li> <span style=\"color: #000000;\">A correla\u00e7\u00e3o no atraso 0 (ou seja, a correla\u00e7\u00e3o normal entre as duas s\u00e9ries temporais sem atraso) \u00e9 <strong>0,77<\/strong> .<\/span><\/li>\n<li> <span style=\"color: #000000;\">A correla\u00e7\u00e3o no atraso 1 \u00e9 <strong>0,93<\/strong> .<\/span><\/li>\n<li> <span style=\"color: #000000;\">A correla\u00e7\u00e3o no atraso 2 \u00e9 <strong>0,95<\/strong> .<\/span><\/li>\n<\/ul>\n<p> <span style=\"color: #000000;\">E assim por diante.<\/span><\/p>\n<p> <span style=\"color: #000000;\">Observe que a correla\u00e7\u00e3o \u00e9 mais alta com uma defasagem de <strong>2<\/strong> entre as duas s\u00e9ries temporais.<\/span><\/p>\n<h2> <span style=\"color: #000000;\"><strong>Recursos adicionais<\/strong><\/span><\/h2>\n<p> <span style=\"color: #000000;\">Os tutoriais a seguir explicam como realizar outras tarefas comuns no Excel:<\/span><\/p>\n<p> <a href=\"https:\/\/statorials.org\/pt\/matriz-de-correlacao-do-excel\/\" target=\"_blank\" rel=\"noopener\">Como criar uma matriz de correla\u00e7\u00e3o no Excel<\/a><br \/> <a href=\"https:\/\/statorials.org\/pt\/correlacao-de-classificacao-de-lanceiro-excel\/\" target=\"_blank\" rel=\"noopener\">Como calcular a correla\u00e7\u00e3o de classifica\u00e7\u00e3o de Spearman no Excel<\/a><br \/> <a href=\"https:\/\/statorials.org\/pt\/correlacao-parcial-excel\/\" target=\"_blank\" rel=\"noopener\">Como calcular a correla\u00e7\u00e3o parcial no Excel<\/a><br \/> <a href=\"https:\/\/statorials.org\/pt\/correlacao-ponto-bisserial-excel\/\" target=\"_blank\" rel=\"noopener\">Como calcular a correla\u00e7\u00e3o ponto-bisserial no Excel<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>A correla\u00e7\u00e3o cruzada \u00e9 uma forma de medir o grau de similaridade entre uma s\u00e9rie temporal e uma vers\u00e3o defasada de outra s\u00e9rie temporal. Esse tipo de correla\u00e7\u00e3o \u00e9 \u00fatil para calcular porque pode nos dizer se os valores em uma s\u00e9rie temporal s\u00e3o preditivos de valores futuros em outra s\u00e9rie temporal. Em outras palavras, [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[11],"tags":[],"class_list":["post-4749","post","type-post","status-publish","format-standard","hentry","category-guia"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v21.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Como calcular a correla\u00e7\u00e3o cruzada no Excel - Statorials<\/title>\n<meta name=\"description\" content=\"Este tutorial explica como calcular a correla\u00e7\u00e3o cruzada no Excel, com um exemplo.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/statorials.org\/pt\/correlacao-cruzada-do-excel\/\" \/>\n<meta property=\"og:locale\" content=\"pt_PT\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Como calcular a correla\u00e7\u00e3o cruzada no Excel - Statorials\" \/>\n<meta property=\"og:description\" content=\"Este tutorial explica como calcular a correla\u00e7\u00e3o cruzada no Excel, com um exemplo.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/statorials.org\/pt\/correlacao-cruzada-do-excel\/\" \/>\n<meta property=\"og:site_name\" content=\"Statorials\" \/>\n<meta property=\"article:published_time\" content=\"2023-07-08T08:26:23+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/statorials.org\/wp-content\/uploads\/2023\/08\/crosscor1.png\" \/>\n<meta name=\"author\" content=\"Dr. benjamim anderson\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Escrito por\" \/>\n\t<meta name=\"twitter:data1\" content=\"Dr. benjamim anderson\" \/>\n\t<meta name=\"twitter:label2\" content=\"Tempo estimado de leitura\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutos\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/statorials.org\/pt\/correlacao-cruzada-do-excel\/\",\"url\":\"https:\/\/statorials.org\/pt\/correlacao-cruzada-do-excel\/\",\"name\":\"Como calcular a correla\u00e7\u00e3o cruzada no Excel - Statorials\",\"isPartOf\":{\"@id\":\"https:\/\/statorials.org\/pt\/#website\"},\"datePublished\":\"2023-07-08T08:26:23+00:00\",\"dateModified\":\"2023-07-08T08:26:23+00:00\",\"author\":{\"@id\":\"https:\/\/statorials.org\/pt\/#\/schema\/person\/e08f98e8db95e0aa9c310e1b27c9c666\"},\"description\":\"Este tutorial explica como calcular a correla\u00e7\u00e3o cruzada no Excel, com um exemplo.\",\"breadcrumb\":{\"@id\":\"https:\/\/statorials.org\/pt\/correlacao-cruzada-do-excel\/#breadcrumb\"},\"inLanguage\":\"pt-PT\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/statorials.org\/pt\/correlacao-cruzada-do-excel\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/statorials.org\/pt\/correlacao-cruzada-do-excel\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Lar\",\"item\":\"https:\/\/statorials.org\/pt\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Como calcular a correla\u00e7\u00e3o cruzada no excel\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/statorials.org\/pt\/#website\",\"url\":\"https:\/\/statorials.org\/pt\/\",\"name\":\"Statorials\",\"description\":\"O seu guia para a literacia estat\u00edstica!\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/statorials.org\/pt\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"pt-PT\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/statorials.org\/pt\/#\/schema\/person\/e08f98e8db95e0aa9c310e1b27c9c666\",\"name\":\"Dr. benjamim anderson\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"pt-PT\",\"@id\":\"https:\/\/statorials.org\/pt\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/statorials.org\/pt\/wp-content\/uploads\/2023\/10\/Dr.-Benjamin-Anderson-96x96.jpg\",\"contentUrl\":\"https:\/\/statorials.org\/pt\/wp-content\/uploads\/2023\/10\/Dr.-Benjamin-Anderson-96x96.jpg\",\"caption\":\"Dr. benjamim anderson\"},\"description\":\"Ol\u00e1, sou Benjamin, um professor aposentado de estat\u00edstica que se tornou professor dedicado na Statorials. Com vasta experi\u00eancia e conhecimento na \u00e1rea de estat\u00edstica, estou empenhado em compartilhar meu conhecimento para capacitar os alunos por meio de Statorials. Saber mais\",\"sameAs\":[\"https:\/\/statorials.org\/pt\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Como calcular a correla\u00e7\u00e3o cruzada no Excel - Statorials","description":"Este tutorial explica como calcular a correla\u00e7\u00e3o cruzada no Excel, com um exemplo.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/statorials.org\/pt\/correlacao-cruzada-do-excel\/","og_locale":"pt_PT","og_type":"article","og_title":"Como calcular a correla\u00e7\u00e3o cruzada no Excel - Statorials","og_description":"Este tutorial explica como calcular a correla\u00e7\u00e3o cruzada no Excel, com um exemplo.","og_url":"https:\/\/statorials.org\/pt\/correlacao-cruzada-do-excel\/","og_site_name":"Statorials","article_published_time":"2023-07-08T08:26:23+00:00","og_image":[{"url":"https:\/\/statorials.org\/wp-content\/uploads\/2023\/08\/crosscor1.png"}],"author":"Dr. benjamim anderson","twitter_card":"summary_large_image","twitter_misc":{"Escrito por":"Dr. benjamim anderson","Tempo estimado de leitura":"2 minutos"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/statorials.org\/pt\/correlacao-cruzada-do-excel\/","url":"https:\/\/statorials.org\/pt\/correlacao-cruzada-do-excel\/","name":"Como calcular a correla\u00e7\u00e3o cruzada no Excel - Statorials","isPartOf":{"@id":"https:\/\/statorials.org\/pt\/#website"},"datePublished":"2023-07-08T08:26:23+00:00","dateModified":"2023-07-08T08:26:23+00:00","author":{"@id":"https:\/\/statorials.org\/pt\/#\/schema\/person\/e08f98e8db95e0aa9c310e1b27c9c666"},"description":"Este tutorial explica como calcular a correla\u00e7\u00e3o cruzada no Excel, com um exemplo.","breadcrumb":{"@id":"https:\/\/statorials.org\/pt\/correlacao-cruzada-do-excel\/#breadcrumb"},"inLanguage":"pt-PT","potentialAction":[{"@type":"ReadAction","target":["https:\/\/statorials.org\/pt\/correlacao-cruzada-do-excel\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/statorials.org\/pt\/correlacao-cruzada-do-excel\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Lar","item":"https:\/\/statorials.org\/pt\/"},{"@type":"ListItem","position":2,"name":"Como calcular a correla\u00e7\u00e3o cruzada no excel"}]},{"@type":"WebSite","@id":"https:\/\/statorials.org\/pt\/#website","url":"https:\/\/statorials.org\/pt\/","name":"Statorials","description":"O seu guia para a literacia estat\u00edstica!","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/statorials.org\/pt\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"pt-PT"},{"@type":"Person","@id":"https:\/\/statorials.org\/pt\/#\/schema\/person\/e08f98e8db95e0aa9c310e1b27c9c666","name":"Dr. benjamim anderson","image":{"@type":"ImageObject","inLanguage":"pt-PT","@id":"https:\/\/statorials.org\/pt\/#\/schema\/person\/image\/","url":"https:\/\/statorials.org\/pt\/wp-content\/uploads\/2023\/10\/Dr.-Benjamin-Anderson-96x96.jpg","contentUrl":"https:\/\/statorials.org\/pt\/wp-content\/uploads\/2023\/10\/Dr.-Benjamin-Anderson-96x96.jpg","caption":"Dr. benjamim anderson"},"description":"Ol\u00e1, sou Benjamin, um professor aposentado de estat\u00edstica que se tornou professor dedicado na Statorials. Com vasta experi\u00eancia e conhecimento na \u00e1rea de estat\u00edstica, estou empenhado em compartilhar meu conhecimento para capacitar os alunos por meio de Statorials. Saber mais","sameAs":["https:\/\/statorials.org\/pt"]}]}},"yoast_meta":{"yoast_wpseo_title":"","yoast_wpseo_metadesc":"","yoast_wpseo_canonical":""},"_links":{"self":[{"href":"https:\/\/statorials.org\/pt\/wp-json\/wp\/v2\/posts\/4749","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/statorials.org\/pt\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/statorials.org\/pt\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/statorials.org\/pt\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/statorials.org\/pt\/wp-json\/wp\/v2\/comments?post=4749"}],"version-history":[{"count":0,"href":"https:\/\/statorials.org\/pt\/wp-json\/wp\/v2\/posts\/4749\/revisions"}],"wp:attachment":[{"href":"https:\/\/statorials.org\/pt\/wp-json\/wp\/v2\/media?parent=4749"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/statorials.org\/pt\/wp-json\/wp\/v2\/categories?post=4749"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/statorials.org\/pt\/wp-json\/wp\/v2\/tags?post=4749"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}