{"id":10886,"date":"2025-07-15T13:34:50","date_gmt":"2025-07-15T13:34:50","guid":{"rendered":"https:\/\/functiontranslator.com\/filtrarxml\/"},"modified":"2021-02-25T17:19:12","modified_gmt":"2021-02-25T17:19:12","slug":"filterxml","status":"publish","type":"post","link":"https:\/\/functiontranslator.com\/pt-pt\/filterxml\/","title":{"rendered":"FILTRARXML"},"content":{"rendered":"

A fun\u00e7\u00e3o FILTERXML, ou FILTRARXML em portugu\u00eas, desempenha um papel crucial quando se trata de extrair informa\u00e7\u00f5es de dados estruturados em XML no Excel. Neste artigo, abordaremos como essa fun\u00e7\u00e3o pode ser aplicada no Excel e discutiremos as alternativas no Google Sheets, j\u00e1 que o Google Sheets n\u00e3o disp\u00f5e desta fun\u00e7\u00e3o de forma nativa.<\/p>\n

Entendendo a fun\u00e7\u00e3o FILTERXML<\/h2>\n

A fun\u00e7\u00e3o FILTRARXML permite a extra\u00e7\u00e3o de informa\u00e7\u00f5es espec\u00edficas de um conte\u00fado XML atrav\u00e9s de uma express\u00e3o XPath. Ela \u00e9 geralmente usada da seguinte maneira:<\/p>\n

=FILTERXML(xml, xpath)<\/code><\/pre>\n
    \n
  • xml:<\/strong> Uma string que cont\u00e9m o documento XML do qual as informa\u00e7\u00f5es ser\u00e3o retiradas.<\/li>\n
  • xpath:<\/strong> A express\u00e3o XPath utilizada para percorrer o documento XML e identificar quais informa\u00e7\u00f5es devem ser retornadas.<\/li>\n<\/ul>\n
    Exemplo pr\u00e1tico<\/h2>\n
    Consideremos um XML que inclui informa\u00e7\u00f5es sobre frutas e seus respectivos pre\u00e7os:<\/p>\n
    <frutas> <fruta> <nome>Ma\u00e7\u00e3<\/nome> <preco>1.20<\/preco> <\/fruta> <fruta> <nome>Banana<\/nome> <preco>0.80<\/preco> <\/fruta> <\/frutas><\/code><\/pre>\n
    Para obter o pre\u00e7o da ma\u00e7\u00e3, a fun\u00e7\u00e3o fica assim:<\/p>\n
    =FILTERXML(A1, \"\/frutas\/fruta[1]\/preco\")<\/code><\/pre>\n
    Assumindo que o conte\u00fado XML esteja armazenado na c\u00e9lula A1.<\/p>\n
    Aplica\u00e7\u00f5es pr\u00e1ticas da fun\u00e7\u00e3o<\/h2>\n
    Extra\u00e7\u00e3o de dados em documentos XML<\/h2>\n
    Suponha que voc\u00ea receba frequentemente arquivos XML com dados de vendas para an\u00e1lise no Excel. Uma maneira eficiente de extrair valores espec\u00edficos sem a necessidade de navega\u00e7\u00e3o manual \u00e9 utilizando a FILTRARXML. Por exemplo, para extrair o total de vendas do XML a seguir:<\/p>\n
    <vendas> <produto> <nome>Cadeira<\/nome> <quantidade>15<\/quantidade> <precoUnitario>45.00<\/precoUnitario> <\/produto> <\/vendas><\/code><\/pre>\n
    O c\u00e1lculo do total de vendas seria:<\/p>\n
    =FILTERXML(A1, \"sum(\/vendas\/produto[quantidade * precoUnitario])\")<\/code><\/pre>\n
    Monitoramento de feeds de dados<\/h2>\n
    Imagine que seja necess\u00e1rio monitorar um feed de dados XML de um site de not\u00edcias. Para extrair o t\u00edtulo da \u00faltima not\u00edcia publicada, voc\u00ea poderia se valer da seguinte express\u00e3o:<\/p>\n
    =FILTERXML(A1, \"\/noticias\/noticia[1]\/titulo\")<\/code><\/pre>\n
    Limita\u00e7\u00f5es e considera\u00e7\u00f5es<\/h2>\n
    No Google Sheets, para utilizar a funcionalidade semelhante \u00e0 FILTRARXML, ser\u00e1 preciso recorrer a uma solu\u00e7\u00e3o alternativa, como scripts do Google App Script para analisar XML, dado que o Google Sheets n\u00e3o oferece uma fun\u00e7\u00e3o nativa compar\u00e1vel \u00e0 FILTERXML do Excel.<\/p>\n
    Este guia visa ajud\u00e1-lo a compreender e iniciar o uso da fun\u00e7\u00e3o FILTERXML, de modo a aprimorar a manipula\u00e7\u00e3o de dados XML em seus projetos de planilhas. Com pr\u00e1ticas cont\u00ednuas e explora\u00e7\u00f5es, voc\u00ea se capacitar\u00e1 a extrair rapidamente dados necess\u00e1rios de documentos XML complexos.<\/p>\n","protected":false},"excerpt":{"rendered":"
    Devolve dados espec\u00edficos do conte\u00fado XML utilizando o XPath especificado<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[450],"tags":[],"class_list":["post-10886","post","type-post","status-publish","format-standard","hentry","category-web-9"],"acf":[],"yoast_head":"\nFun\u00e7\u00e3o FILTERXML (FILTRARXML) no Excel \u2013 como funciona, exemplos e sintaxe<\/title>\n<meta name=\"description\" content=\"Breve descri\u00e7\u00e3o da fun\u00e7\u00e3o FILTERXML (FILTRARXML) do Excel. Sintaxe, par\u00e2metros e exemplos pr\u00e1ticos de uso.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/functiontranslator.com\/pt-pt\/filterxml\/\" \/>\n<meta property=\"og:locale\" content=\"pt_PT\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Fun\u00e7\u00e3o FILTERXML (FILTRARXML) no Excel \u2013 como funciona, exemplos e sintaxe\" \/>\n<meta property=\"og:description\" content=\"Breve descri\u00e7\u00e3o da fun\u00e7\u00e3o FILTERXML (FILTRARXML) do Excel. Sintaxe, par\u00e2metros e exemplos pr\u00e1ticos de uso.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/functiontranslator.com\/pt-pt\/filterxml\/\" \/>\n<meta property=\"og:site_name\" content=\"Function Translator\" \/>\n<meta property=\"article:published_time\" content=\"2025-07-15T13:34:50+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2021-02-25T17:19:12+00:00\" \/>\n<meta name=\"author\" content=\"glavadmin\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:title\" content=\"Fun\u00e7\u00e3o FILTERXML (FILTRARXML) no Excel \u2013 como funciona, exemplos e sintaxe\" \/>\n<meta name=\"twitter:label1\" content=\"Escrito por\" \/>\n\t<meta name=\"twitter:data1\" content=\"glavadmin\" \/>\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:\/\/functiontranslator.com\/pt-pt\/filterxml\/\",\"url\":\"https:\/\/functiontranslator.com\/pt-pt\/filterxml\/\",\"name\":\"Fun\u00e7\u00e3o FILTERXML (FILTRARXML) no Excel \u2013 como funciona, exemplos e sintaxe\",\"isPartOf\":{\"@id\":\"https:\/\/functiontranslator.com\/no\/#website\"},\"datePublished\":\"2025-07-15T13:34:50+00:00\",\"dateModified\":\"2021-02-25T17:19:12+00:00\",\"author\":{\"@id\":\"https:\/\/functiontranslator.com\/no\/#\/schema\/person\/87663eece497ca58643166ecb09f1389\"},\"description\":\"Breve descri\u00e7\u00e3o da fun\u00e7\u00e3o FILTERXML (FILTRARXML) do Excel. Sintaxe, par\u00e2metros e exemplos pr\u00e1ticos de uso.\",\"breadcrumb\":{\"@id\":\"https:\/\/functiontranslator.com\/pt-pt\/filterxml\/#breadcrumb\"},\"inLanguage\":\"pt-PT\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/functiontranslator.com\/pt-pt\/filterxml\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/functiontranslator.com\/pt-pt\/filterxml\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Fun\u00e7\u00f5es do Excel\",\"item\":\"https:\/\/functiontranslator.com\/pt-pt\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Web\",\"item\":\"https:\/\/functiontranslator.com\/pt-pt\/web-9\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"FILTRARXML\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/functiontranslator.com\/no\/#website\",\"url\":\"https:\/\/functiontranslator.com\/no\/\",\"name\":\"Function Translator\",\"description\":\"Excel function translator\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/functiontranslator.com\/no\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"pt-PT\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/functiontranslator.com\/no\/#\/schema\/person\/87663eece497ca58643166ecb09f1389\",\"name\":\"glavadmin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"pt-PT\",\"@id\":\"https:\/\/functiontranslator.com\/no\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/03ccd9347c5339db75deede5a8c5b2bf1d40e0bce26a5251fcaac6331d381db8?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/03ccd9347c5339db75deede5a8c5b2bf1d40e0bce26a5251fcaac6331d381db8?s=96&d=mm&r=g\",\"caption\":\"glavadmin\"},\"sameAs\":[\"https:\/\/functiontranslator.com\"],\"url\":\"https:\/\/functiontranslator.com\/pt-pt\/author\/glavadmin\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Fun\u00e7\u00e3o FILTERXML (FILTRARXML) no Excel \u2013 como funciona, exemplos e sintaxe","description":"Breve descri\u00e7\u00e3o da fun\u00e7\u00e3o FILTERXML (FILTRARXML) do Excel. Sintaxe, par\u00e2metros e exemplos pr\u00e1ticos de uso.","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:\/\/functiontranslator.com\/pt-pt\/filterxml\/","og_locale":"pt_PT","og_type":"article","og_title":"Fun\u00e7\u00e3o FILTERXML (FILTRARXML) no Excel \u2013 como funciona, exemplos e sintaxe","og_description":"Breve descri\u00e7\u00e3o da fun\u00e7\u00e3o FILTERXML (FILTRARXML) do Excel. Sintaxe, par\u00e2metros e exemplos pr\u00e1ticos de uso.","og_url":"https:\/\/functiontranslator.com\/pt-pt\/filterxml\/","og_site_name":"Function Translator","article_published_time":"2025-07-15T13:34:50+00:00","article_modified_time":"2021-02-25T17:19:12+00:00","author":"glavadmin","twitter_card":"summary_large_image","twitter_title":"Fun\u00e7\u00e3o FILTERXML (FILTRARXML) no Excel \u2013 como funciona, exemplos e sintaxe","twitter_misc":{"Escrito por":"glavadmin","Tempo estimado de leitura":"2 minutos"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/functiontranslator.com\/pt-pt\/filterxml\/","url":"https:\/\/functiontranslator.com\/pt-pt\/filterxml\/","name":"Fun\u00e7\u00e3o FILTERXML (FILTRARXML) no Excel \u2013 como funciona, exemplos e sintaxe","isPartOf":{"@id":"https:\/\/functiontranslator.com\/no\/#website"},"datePublished":"2025-07-15T13:34:50+00:00","dateModified":"2021-02-25T17:19:12+00:00","author":{"@id":"https:\/\/functiontranslator.com\/no\/#\/schema\/person\/87663eece497ca58643166ecb09f1389"},"description":"Breve descri\u00e7\u00e3o da fun\u00e7\u00e3o FILTERXML (FILTRARXML) do Excel. Sintaxe, par\u00e2metros e exemplos pr\u00e1ticos de uso.","breadcrumb":{"@id":"https:\/\/functiontranslator.com\/pt-pt\/filterxml\/#breadcrumb"},"inLanguage":"pt-PT","potentialAction":[{"@type":"ReadAction","target":["https:\/\/functiontranslator.com\/pt-pt\/filterxml\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/functiontranslator.com\/pt-pt\/filterxml\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Fun\u00e7\u00f5es do Excel","item":"https:\/\/functiontranslator.com\/pt-pt\/"},{"@type":"ListItem","position":2,"name":"Web","item":"https:\/\/functiontranslator.com\/pt-pt\/web-9\/"},{"@type":"ListItem","position":3,"name":"FILTRARXML"}]},{"@type":"WebSite","@id":"https:\/\/functiontranslator.com\/no\/#website","url":"https:\/\/functiontranslator.com\/no\/","name":"Function Translator","description":"Excel function translator","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/functiontranslator.com\/no\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"pt-PT"},{"@type":"Person","@id":"https:\/\/functiontranslator.com\/no\/#\/schema\/person\/87663eece497ca58643166ecb09f1389","name":"glavadmin","image":{"@type":"ImageObject","inLanguage":"pt-PT","@id":"https:\/\/functiontranslator.com\/no\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/03ccd9347c5339db75deede5a8c5b2bf1d40e0bce26a5251fcaac6331d381db8?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/03ccd9347c5339db75deede5a8c5b2bf1d40e0bce26a5251fcaac6331d381db8?s=96&d=mm&r=g","caption":"glavadmin"},"sameAs":["https:\/\/functiontranslator.com"],"url":"https:\/\/functiontranslator.com\/pt-pt\/author\/glavadmin\/"}]}},"_links":{"self":[{"href":"https:\/\/functiontranslator.com\/pt-pt\/wp-json\/wp\/v2\/posts\/10886","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/functiontranslator.com\/pt-pt\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/functiontranslator.com\/pt-pt\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/functiontranslator.com\/pt-pt\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/functiontranslator.com\/pt-pt\/wp-json\/wp\/v2\/comments?post=10886"}],"version-history":[{"count":1,"href":"https:\/\/functiontranslator.com\/pt-pt\/wp-json\/wp\/v2\/posts\/10886\/revisions"}],"predecessor-version":[{"id":25715,"href":"https:\/\/functiontranslator.com\/pt-pt\/wp-json\/wp\/v2\/posts\/10886\/revisions\/25715"}],"wp:attachment":[{"href":"https:\/\/functiontranslator.com\/pt-pt\/wp-json\/wp\/v2\/media?parent=10886"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/functiontranslator.com\/pt-pt\/wp-json\/wp\/v2\/categories?post=10886"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/functiontranslator.com\/pt-pt\/wp-json\/wp\/v2\/tags?post=10886"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}