Dicas úteis

Como definir uma fórmula na tabela do Google Docs para que uma das variáveis ​​seja sempre obtida da mesma célula?

Pin
Send
Share
Send
Send


O artigo foi co-escrito com Renat Shagabutdinov.

Este artigo se concentrará em vários recursos muito úteis do Planilhas Google que o Excel não possui (CLASSIFICAÇÃO, junções de matriz, FILTRO, IMPORTRANGE, IMAGEM, GOOGLETRANSLATE, DETECTLANGUAGE)

Existem muitas cartas, mas há análises de casos interessantes, todos os exemplos, a propósito, podem ser examinados mais detalhadamente no documento Google goo.gl/cOQAd9 (arquivo-> crie uma cópia para copiar o arquivo para o Google Drive e poder editar).

Sumário:


Se o resultado da fórmula ocupar mais de uma célula

Primeiro, um recurso importante da exibição de resultados de fórmulas no Planilhas Google. Se sua fórmula retornar mais de uma célula, toda essa matriz será exibida imediatamente e ocupará quantas células e colunas forem necessárias (no Excel, para isso, você precisará inserir uma fórmula de matriz em todas essas células). No exemplo a seguir, veremos como isso funciona.

Ajudará a classificar o intervalo de dados por uma ou várias colunas e exibir o resultado imediatamente.

= SORT (dados classificáveis, coluna_for_sorting, ascendente, [column_for_sorting_2, ascending_2 ,.])

(daqui em diante - exemplos para configurações de tabela regional russa, as configurações de registro são alteradas no menu arquivo → configurações de tabela)



Como adicionar cabeçalhos de tabela ao SORT?

Usando colchetes <>, criamos uma matriz de dois elementos, os títulos da tabela A1: B1 e a função SORT, separamos elementos um do outro usando um ponto-e-vírgula.



Como combinar vários intervalos de dados e classificar (e não apenas)?

Vejamos como você pode combinar intervalos para uso em funções. Isso se aplica não apenas ao SORT, essa técnica pode ser usada em qualquer função em que seja possível, por exemplo, em VPR ou SEARCH.

Quem leu o exemplo anterior já descobriu o que fazer: abra a chave e colete as matrizes para combinar, separando-as uma da outra com um ponto-e-vírgula e feche a chave.

Você pode combinar matrizes e não usá-las na fórmula, mas simplesmente exibi-las em uma folha, por exemplo, coletando dados de várias folhas do seu livro. Para junção vertical, é necessário observar apenas o mesmo número de colunas em todos os fragmentos (temos duas colunas em todos os lugares).

E na captura de tela abaixo - um exemplo de união horizontal, ele usa uma barra invertida em vez de ponto e vírgula e é necessário que o número de linhas nos fragmentos coincida; caso contrário, a fórmula retornará um erro em vez do intervalo combinado.

(ponto-e-vírgula e barra invertida são separadores dos elementos da matriz nas configurações regionais russas, se os exemplos não funcionarem para você, no arquivo estão as configurações da tabela, verifique se você os possui)

Bem, agora vamos voltar à matriz horizontal e inseri-la na função SORT. Classificaremos os dados pela primeira coluna, em ordem decrescente.

A junção pode ser usada em qualquer função, o principal é observar o mesmo número de colunas para vertical ou linhas para junção horizontal.

Todos os exemplos analisados ​​podem ser considerados mais próximos
Google Doc.

Usando FILTER, podemos filtrar dados de acordo com uma ou várias condições e exibir o resultado em uma planilha ou usar o resultado em outra função como um intervalo de dados.

Por exemplo, temos uma tabela com vendas de nossos funcionários e dela derivamos dados para um funcionário.

Introduzimos a seguinte fórmula na célula E3:

Observe que a sintaxe é um pouco diferente das fórmulas comuns, como SUMMESLIN, onde o intervalo da condição e a própria condição seriam separados usando um ponto e vírgula.

A fórmula inserida em uma célula retorna uma matriz de 9 células com dados, mas após os exemplos com a função SORT, não somos mais surpreendidos por isso.

Além do sinal de igual (=) nas condições, você também pode usar>,> =, <> (diferente) e, para números ou datas, pode usar todos esses sinais.

Duas condições e trabalho com data

Vamos complicar a fórmula e adicionar mais uma condição, de acordo com a data de venda, deixaremos todas as vendas a partir de 01/02/17

É assim que a fórmula será exibida se você inserir os argumentos da condição imediatamente, preste atenção à conversão da entrada de texto da data usando DATEVALUE:



Gráfico interativo com FILTER e SPARKLINE

Você sabe como usar a função FILTRO? Não podemos exibir o resultado da função na planilha, mas usá-lo como dados para outra função, por exemplo, sparkline. Sparkline é uma função que constrói um gráfico em uma célula com base em nossos dados; um sparkline tem muitas configurações, como o tipo do gráfico, a cor dos elementos, mas agora não vamos nos concentrar neles e usar a função sem configurações adicionais. Vamos seguir para o exemplo.

Lista suspensa. Nossa programação será alterada dependendo do funcionário selecionado na lista suspensa. Fazemos a lista da seguinte maneira:

  • selecione a célula E2,
  • menu Dados → Validação de dados,
  • regras: valor do intervalo e, no intervalo, selecionamos uma coluna com funcionários a partir dos dados de origem, não se preocupe com a repetição dos nomes, apenas valores únicos permanecerão na lista suspensa,

Clique em "Salvar" e obtenha uma lista suspensa na célula selecionada:

Uma célula com uma lista suspensa se tornará uma condição para a fórmula FILTER, nós a escreveremos.

Portanto, parece em dinâmica:

Mas como o SPARKLINE inteligente com configurações adicionais pode parecer, no trabalho real, o gráfico exibe os resultados da atividade em um dia, as barras verdes são valores positivos, as barras rosa são negativas.



IMPORTRANGE

O Planilhas Google usa a função IMPORTRANGE para transferir dados de um arquivo para outro.

Quando pode ser útil?

  • Você precisa de dados atualizados do seu arquivo de mesmo nível.
  • Você deseja processar dados de um arquivo ao qual você tem acesso "Somente visualização".
  • Você deseja coletar tabelas de vários documentos em um único local para processá-las ou visualizá-las.

Essa fórmula permite que você obtenha uma cópia de um intervalo de outras planilhas do Google. Ao mesmo tempo, a formatação não é portátil - apenas dados (o que fazer com a formatação - veremos um pouco abaixo).

A sintaxe da fórmula é a seguinte:

IMPORTRANGE (chave da planilha, sequência de intervalos)
IMPORTRANGE (chave, intervalo)

spreadsheet_key (key) - uma sequência de caracteres do atributo "key =" (key) no link para a tabela (depois de "spreadsheets / ... /").

Um exemplo de uma fórmula com uma chave:

Seu arquivo exibirá o intervalo A1: CM500 da Folha1 do arquivo localizado no link apropriado.

Se o número de colunas ou linhas puder mudar no arquivo de origem, insira o intervalo aberto no segundo argumento da função (consulte também a subseção “Intervalos no formato A2: A”), por exemplo:

Sheet1! A1: CM (se as linhas serão adicionadas)
Sheet1! A1: 1000 (se forem adicionadas colunas)

! Lembre-se de que, se você carregar um intervalo aberto (por exemplo, A1: D), não poderá inserir manualmente nenhum dado nas colunas A: D no arquivo em que a fórmula IMPORTRANGE está localizada (ou seja, no local final em que os dados são carregados). Eles parecem ser "reservados" para todo o intervalo aberto - porque sua dimensão não é conhecida antecipadamente.

Você pode inserir um link para um arquivo e um link para um intervalo, não na fórmula, mas nas células do seu documento e consultá-los.

Portanto, se na célula A1 você inserir um link para um documento (sem aspas) a partir do qual deseja carregar dados, e na célula B1 - um link para uma planilha e um intervalo (também sem aspas), será possível importar dados usando a seguinte fórmula:

A opção com links de células é preferível no sentido de que você sempre pode acessar facilmente o arquivo de origem (clicando no link na célula) e / ou ver qual intervalo e de qual guia são importados.

Importar formatação da tabela de origem

Como já vimos, o IMPORTRANGE carrega apenas dados, mas não a formatação da tabela original. Como lidar com isso? Prepare o solo com antecedência, copiando a formatação da folha de origem. Para fazer isso, vá para a folha de origem e copie-a em seu livro:

Depois de pressionar o botão Copiar para ... Selecione o livro para o qual você importará os dados. Geralmente a tabela desejada está na guia Recentes (se você realmente trabalhou recentemente com ela).

Após copiar a planilha, selecione todos os dados (clicando no canto superior esquerdo):

E clique Excluir. Todos os dados desaparecerão e a formatação permanecerá. Agora você pode inserir a função IMPORTRANGE e obter a correspondência completa da planilha de origem - na parte de dados e na parte de formato:



IMPORTRANGE como argumento para outra função

IMPORTRANGE pode ser um argumento para outra função se o intervalo que você está importando se encaixa nessa função.

Considere um exemplo simples - o valor médio das vendas do intervalo encontrado em outro documento.

Este é o documento de origem. Deixe os dados serem adicionados e precisamos da média das vendas de 2016 (ou seja, da célula D2 até a parada).

Primeiro importamos esse intervalo:

IMAGEM: adicione imagens às células

A função IMAGEM permite adicionar imagens ao Planilhas Google.

A função possui a seguinte sintaxe:

Ou coloque um link para a célula na qual o link está armazenado:

A última opção é mais conveniente na maioria dos casos. Portanto, se você tiver uma lista de livros e links para capas, uma fórmula é suficiente para exibir todos eles:

Na prática, acontece que os links para imagens são armazenados em uma folha separada e você os obtém usando a função VLOOKUP ou de outra forma.

O argumento mode pode assumir quatro valores (se você pular, será o primeiro por padrão):

  1. a imagem é esticada para o tamanho da célula enquanto mantém a proporção,
  2. a imagem é esticada sem manter a proporção, preenchendo completamente
  3. a imagem é inserida com o tamanho original,
  4. você especifica o tamanho da imagem no terceiro e quarto argumentos para as funções [height] e [w>. Vamos ver como as imagens com quatro valores diferentes do argumento mode são exibidas na prática:

O quarto modo pode ser conveniente se você precisar selecionar o tamanho exato da imagem em pixels, alterando os parâmetros altura (altura) e largura (largura). A imagem será atualizada imediatamente.
Observe que em todos os modos, exceto no segundo, pode haver áreas em branco na célula e elas podem ser preenchidas com cores:



GOOGLETRANSLATE e DETECTLANGUAGE: traduzir texto nas células

O Planilhas Google possui uma divertida função GOOGLETRANSLATE que permite traduzir texto diretamente nas células:

A sintaxe da função é a seguinte:

GOOGLETRANSLATE (texto, [idioma da origem], [idioma da meta])

texto é o texto a ser traduzido. Você pode citar o texto e escrever diretamente na fórmula, mas é mais conveniente consultar a célula na qual o texto está escrito.
[source_language] - o idioma do qual traduzimos
[target_language] é o idioma para o qual estamos traduzindo.

Os segundo e terceiro argumentos são dados por um código de dois dígitos: es, fr, en, ru. Eles também podem ser especificados na própria função, mas podem ser obtidos de uma célula, e o idioma do texto de origem pode ser determinado automaticamente.

Mas e se quisermos traduzir para diferentes idiomas? E, ao mesmo tempo, não queremos especificar o idioma de origem manualmente todas as vezes?

A função DETECTLANGUAGE é útil aqui. Ela tem apenas um argumento - o texto cuja linguagem deve ser definida:

Como em qualquer outra função, a beleza aqui está na automação. Você pode alterar rapidamente o texto ou o idioma, traduzir rapidamente uma frase para 10 idiomas e assim por diante. Obviamente, entendemos que este é o texto de um tradutor on-line - a qualidade será apropriada.

Evgeny Namokonov e Renat Shagabutdinov, e também conduzimos um canal em um telegrama, onde analisamos casos diferentes com o Planilhas Google, se você estiver interessado - faça uma visita, o link pode ser encontrado no meu perfil.

Pin
Send
Share
Send
Send