Crie totais ad-hoc para um conjunto de dados do Excel onde você filtrará os dados. Também neste vídeo: Usando SUBTOTAL (109,) para somar dados que foram linhas ocultas manualmente. Usando Ctrl + Ditto para copiar a fórmula exata uma linha abaixo.
Depois de aplicar um filtro, seria ótimo ver o total de células visíveis.
Selecione a célula em branco abaixo de cada uma de suas colunas numéricas. Clique no botão AutoSoma.
Em vez de inserir fórmulas SOMA, o Excel insere =SUBTOTAL(9,… )fórmulas. A fórmula abaixo mostra o total apenas das células visíveis.
Insira algumas linhas em branco acima de seus dados. Corte as fórmulas abaixo dos dados e cole na linha 1 com o rótulo Visível Total.
Agora, conforme você altera os filtros, mesmo se os dados ocuparem mais de uma tela inteira de dados, você verá os totais no topo da sua planilha.
Agradecemos a Sam Radakovitz da equipe do Excel para Filtrar por Seleção - não por sugerir Filtro por Seleção, mas por formalizar Filtro por Seleção!
Assistir vídeo
- Crie totais ad-hoc para um conjunto de dados do Excel onde você filtrará os dados. Também neste vídeo: Usando SUBTOTAL (109,) para somar dados que foram linhas ocultas manualmente. Usando Ctrl + Ditto para copiar a fórmula exata uma linha abaixo.
Transcrição de vídeo
O podcast é patrocinado por "MrExcel XL", um livro com mais de 40 dicas do Excel, desenhos animados, coquetéis, tweets e piadas do Excel.
Aprenda Excel com o podcast, episódio 1976 - Totalize as células visíveis!
Ei, bem-vindo de volta ao netcast, sou Bill Jelen. Claro, todo mundo, a primeira fórmula, que a maioria das pessoas cria, é o AutoSoma. Você está aqui embaixo de uma coluna de números, obtém o botão AutoSoma, e ele usará a função de soma para totalizar os números acima, até encontrar uma fórmula ou texto. Certo? Ou Alt + = faz exatamente a mesma coisa. Ou, melhor ainda, selecione várias células e depois acesse o AutoSoma, e ele vai colocar todas essas fórmulas, sem que você precise confirmar as fórmulas.
Mas aqui está o truque legal, certo, então vamos ligar o AutoFiltro. Então, vamos usar o truque do Filtro por Seleção, do podcast de ontem. Os dados são filtrados. Vou para a primeira linha em branco abaixo dos dados e, em seguida, clico no botão AutoSoma. Mas algo muito diferente está acontecendo, você vê. Em vez da função SOMA, obtemos = SUBTOTAL (9! E o que é isso, esse é o Total das células visíveis. Tudo bem, muito legal que funcione.
Agora é o seguinte, eu estava fazendo um seminário de Excel ao vivo há quase dez anos, em Fort Wayne, IN, para o Institute of Internal Auditors. E alguém me mostrou esse truque, e eu realmente adoro o truque. Eu nunca tinha visto o truque antes, era legal, eu disse que isso é ótimo, vou adicionar isso ao seminário. E então, no ano seguinte, eu estava de volta a Fort Wayne, e o empresário daquele cara apareceu, certo, ele não tinha vindo no primeiro ano, porque achava que sabia tudo sobre Excel. Mas ele voltou para reivindicar esse truque e disse: “Olha, ouvi que meu analista financeiro te contou esse truque, mas ele não te disse a melhor parte, sobe acima dos seus dados e insere duas linhas lá. então, uma vez que você tem o total de células visíveis na parte inferior, corte, CTRL + X, e cole, CTRL + V, tudo bem, o que isso faz é,se você mudar para outro cliente, "Tudo bem, então vou escolher mais alguns clientes aqui". E agora eu tenho mais clientes do que cabem na tela! E eu não tenho que rolar para baixo para ver os totais, os totais estão sempre lá no topo! "
Tudo bem agora, este truque, usando a AutoSoma abaixo dos dados de filtro, funciona quando você está ocultando as linhas com o filtro. E se você estiver ocultando manualmente as linhas? Então aqui, selecionei algumas linhas, já fui para Formatar, Ocultar e Reexibir, Ocultar Linhas! Portanto, agora temos linhas ocultas. E eu desço ao fundo e pressiono Alt + = ou AutoSoma, e não me dá a função SUBTOTAL, mas sim a função SUM.
Tudo bem agora, neste ponto, vou fazer uma coisa especial, vou fazer CTRL + '(idem, apóstrofo). Isso trará exatamente a mesma fórmula para baixo, sem alterar a referência da célula. Então é o E2: E8 alterado, ou não mudou, normalmente, se você copiaria e colaria para uma alteração no E3: E9. Vou mudar isso para SUBTOTAL (9, e você verá que não funciona, ainda nos dá todas as células. 1000 + 400 + 800 não é 4500, está incluindo as linhas ocultas. Mas aqui está o que você tem que fazer, entre em CTRL + 'e mude esse 9, para 109, e você obterá: O Verdadeiro Total Visível Baseado em Linhas Escondidas Manualmente!
Tudo bem, aí está, então alguns truques diferentes, AutoSum, Alt + = apenas nos dão a função SUM. Se você estiver abaixo dos dados e pressionar AutoSoma, você obterá o SUBTOTAL (9, que funciona muito bem para filtrado, ou você pode apenas digitar manualmente o SUBTOTAL (109, para obter o total do visível, quando você filtrou manualmente.
Tudo bem, aqui está uma recapitulação dos conceitos do vídeo de hoje: Essa, por falar nisso, não é nem a dica nº 3 do livro, é a dica bônus nº 2b, certo, entre as dicas 2 e 3. O livro cobre todos esses Itens. Bem, obrigado a todos que sugeriram isso, o pessoal do Capítulo de Fort Wayne Indiana do IIA foi onde eu ouvi pela primeira vez. É um ótimo livro, você pode comprá-lo impresso ou e-book, clique no "i" no canto superior direito. Você será levado a mais informações sobre o livro.
OK, quero agradecer a sua visita, nos vemos na próxima vez para outro netcast do!
