Dica nº 1 do livro XL - maneiras rápidas de copiar uma fórmula em uma coluna.
Você tem milhares de linhas de dados. Você adicionou uma nova fórmula na linha superior do seu conjunto de dados, algo assim =PROPER(A2&" "&B2), conforme mostrado abaixo. Agora você precisa copiar a fórmula para todas as linhas do seu conjunto de dados.
Muitas pessoas agarrarão a alça de preenchimento e começarão a arrastar para baixo. Mas, conforme você arrasta para baixo, o Excel começa a ficar cada vez mais rápido. A partir do Excel 2010, há uma pausa de 200 microssegundos na última linha de dados. 200 microssegundos é tempo suficiente para você notar a pausa, mas não o suficiente para você reagir e soltar o botão do mouse. Antes que você perceba, você arrastou a alça de preenchimento longe demais.
A solução é clicar duas vezes no indicador de preenchimento! Vá exatamente para o mesmo ponto onde você começou a arrastar a alça de preenchimento. O ponteiro do mouse muda para um sinal de mais preto. Duplo click.
O Excel examina os dados ao redor, encontra a última linha com dados hoje e copia a fórmula até a última linha do conjunto de dados.
No passado, as células vazias na coluna à esquerda faziam com que o truque “clique duas vezes no identificador de preenchimento” parasse de funcionar antes da célula vazia. Mas, como você pode ver abaixo, nomes como Madonna, Cher ou Pelé não causarão problemas. Desde que haja pelo menos um caminho diagonal (por exemplo, via B76-A77-B78), o Excel encontrará o verdadeiro fundo do conjunto de dados.
Em meus seminários Power Excel ao vivo, esse truque sempre provoca surpresa em metade das pessoas na sala. É o meu truque número um para economizar tempo.
Alternativas para clicar duas vezes na alça de preenchimento
Este truque é um truque incrível se tudo o que você fez até agora foi arrastar o indicador de preenchimento para a parte inferior do conjunto de dados. Mas existem maneiras ainda mais rápidas de resolver esse problema:
- Use tabelas. Se você selecionar uma célula em A1: B112 e pressionar Ctrl + T, o Excel formatará o intervalo como uma tabela. Assim que tiver uma tabela, basta inserir a fórmula em C2. Quando você pressiona Enter, ele é copiado para o final.
- Use um atalho de teclado complexo, mas eficaz. Este atalho requer que a coluna adjacente não tenha células vazias. Embora pareça complicado de explicar, as pessoas que me falam sobre esse atalho podem fazer tudo em um piscar de olhos.
Aqui estão as etapas:
- Na fórmula recém-inserida em C2, pressione a tecla de seta para a esquerda para mover para a célula B2.
- Pressione Ctrl + Seta para baixo para mover para a última linha com dados. Neste caso, B112.
-
Pressione a tecla de seta para a direita para retornar ao final da coluna C, quase vazia.
Usando atalhos de teclado - Na célula C112, pressione Ctrl + Shift + Seta para cima. Isso seleciona todas as células em branco próximas aos seus dados, mais a fórmula em C2.
-
Pressione Ctrl + D para preencher a fórmula em C2 para todos os espaços em branco na seleção. (Observe que Ctrl + R preenche corretamente, o que pode ser útil em outras situações.)
Ctrl + D para preencher
Como alternativa, você pode obter os mesmos resultados pressionando Ctrl + C antes da etapa 1 e substituindo a etapa 5 por Ctrl + V.
Obrigado às seguintes pessoas que sugeriram esta dica: D. Carmichael, Shelley Fishel, Dawn Gilbert, @Knutsford_admi, Francis Logan, Michael Ortenberg, Jon Paterson, Mike Sullivan e Greg Lambert Lane sugeriram Ctrl + D. Bill Hazlett, autor de Excel para a sala de aula de matemática, apontou Ctrl + R. Foi o desenho animado de Em DeMarco postado no Twitter que serviu de inspiração para a contratação de ilustradores para este livro.
Assistir vídeo
- Dica nº 1 do livro XL - maneiras rápidas de copiar uma fórmula em uma coluna. Também neste vídeo:
- Ctrl + Seta para baixo para mover para o final de um bloco de dados
- Ctrl + Shift + Seta para cima para selecionar o topo de um bloco de dados
- Ctrl + D para preencher a fórmula da primeira linha de uma seleção para baixo
- Clique duas vezes na alça de preenchimento
- Função PROPER
- & caractere para concatenação
- Função ROMANA
- Função ÁRABE
Transcrição de vídeo
O podcast é patrocinado por "MrExcel XL", um livro com mais de 40 dicas do Excel, além de desenhos animados, coquetéis tweets e piadas.
Aprenda Excel com o podcast, episódio 1974 - Fast Formula Copy!
Tudo bem, ei, todo este livro tem muitas ilustrações, e a que me fisgou foi essa ilustração de Emily DeMarco. Emily estava em uma espécie de seminário de Excel, onde eles mostravam como combinar dados usando o caractere de concatenação, e ela estava rabiscando isso, e alguém na platéia disse: "Mas isso não é o mesmo que = CONCATENAR?" Para quem está dando aulas, sim. Mas quem pode soletrar conCATenate? Eu vi isso, achei isso hilário.
Portanto, a questão hoje é como pegamos essa fórmula, não vou falar sobre essa fórmula, mas como pegamos essa fórmula, ou qualquer fórmula, e a copiamos para centenas ou milhares de linhas de dados? E antes de eu entrar no meu caminho, primeiro devemos reconhecer o Método Excel Olympics! Existem pessoas que podem executar essas cinco etapas como uma única tecla, leva menos de um segundo, e se for você, parabéns, parabéns, parabéns, parabéns, se houver uma Olimpíada do Excel, estou nomeando você para fazer parte do time. Então, se você é uma pessoa de atalho de teclado, a seta para a esquerda se move para B2, CTRL + seta para baixo, seta para a direita, CTRL + Shift, a seta para cima seleciona para o topo, e no meu favorito, aquele que eu realmente nunca usar antes, era CTRL + D. CTRL + D pega a fórmula na linha superior da seleção e a copia para baixo.Portanto, se você adora atalhos de teclado, sem dúvida, este é o caminho a percorrer.
Mas a maneira que a maioria das pessoas resolve esse problema é, eles criam aquela primeira fórmula lá na linha 2, e agarram a alça de preenchimento e começam a arrastar, certo, e começa a ir cada vez mais rápido e mais rápido e mais rápido. Antes que você saiba, você está centenas de linhas abaixo da parte inferior dos dados. E se você notar, eles realmente diminuem a velocidade ali mesmo. Eles não são longos o suficiente para você realmente reagir, apenas longos o suficiente para você ver a desaceleração. Portanto, se você descer muito e voltar a subir, eu chamo isso de dança do manípulo de preenchimento, e você vai adorar o próximo truque.
Então, aqui está a maneira mais rápida, começa exatamente da mesma maneira, você tem a cruz branca ali, ali está o cursor do mouse, quando você chega perto, ele muda para um + preto, aí mesmo quando você tem aquele + preto, duplo -clique! O Excel olha para a esquerda, calcula quantas linhas de dados temos hoje e copia a fórmula para o final! Quer seja 112 linhas neste caso, ou 1,1 milhão de linhas, de qualquer maneira, chega ao fim.
Tudo bem agora, para aqueles de vocês que já sabiam clicar duas vezes na alça de preenchimento, você sabe disso há muito tempo: Quando o clique duplo na alça de preenchimento não funciona? Isso mesmo, é quando alguém não tem sobrenome! A coluna à esquerda está em branco. Em vez de Cher, vamos colocar Prince. Normalmente, clique duas vezes na alça de preenchimento, ela olha para a esquerda e só vai copiar para a linha 8. Certo, por causa deste espaço em branco na linha 9. E é claro, nunca está na primeira tela, são sempre centenas de linhas para baixo nos dados. Bem, esse costumava ser o problema, mas, silenciosamente sem qualquer aviso no Excel 2010, eles agora olham em todas as colunas à esquerda, para descobrir até onde copiar a fórmula. Portanto, não se deixe enganar pela célula em branco na coluna à esquerda!Esta é de longe a minha dica favorita em todo o Excel, quando estou fazendo os seminários avançados do Excel, aqueles seminários ao vivo, e chego a isso cerca de 45 minutos no dia, quase sempre recebo um suspiro de algumas pessoas a sala, quando clico duas vezes na alça de preenchimento.
Agora, ei, só uma curiosidade aqui, o livro "MrExcel XL", você sabe, eu cresci naquela época, nos anos 70, quando havia todos os tipos de filmes, que saíam com algarismos romanos, então o Padrinho parte II foi o primeiro. Na história moderna, De Volta para o Futuro III, Star Wars IV, Rocky V, Star Trek VI e até Final Fantasy VII para 7. Você sabe, e é engraçado, o Excel realmente tem uma função ROMANA! Tudo bem, então você pode pedir o ROMANO de ROW, e isso lhe dará os algarismos romanos. O que é realmente estranho, porque no Excel 2013, se você colocar um numeral romano XL e pedir o ÁRABE, ele converterá esse numeral ROMANO de volta para um numeral regular. Então, quando eu terminei meu 39º livro, e eu sabia que o 40º livro estava chegando, apenas como uma dica de volta para todos esses filmes que usam algarismos romanos, eu sabia disso,ia ser apenas "MrExcel XL", porque é meu 40º livro, embora eu tenha colocado o slogan lá, = ROMAN (40), para que as pessoas entendessem a piada!
Tudo bem, apenas uma rápida recapitulação de todas as coisas que falamos neste episódio: E ESTA é apenas a dica nº 1 do livro. Bem, obrigado, a todos que escreveram sugerindo esta dica. Essas são todas as pessoas que me enviaram um tweet. Um que eu nem falei aqui, Bill Hazlett, aponta que CTRL + R é como CTRL + D, se você selecionar uma célula e arrastar para a direita ou selecionar para a direita, CTRL + R irá copiar à direita. Vá em frente e clique naquele botão de informações no canto superior direito agora, para ler sobre como você pode comprar este livro.
Obrigado pela visita, nos vemos na próxima vez para outro netcast de!
