A função MOD no Microsoft Excel permite descobrir se um número é divisível por outro. Se não for, diz o que resta. Por exemplo, se você dividir 3 por 2, a função Mod retornará 1. Neste guia, mostrarei como você pode usá-lo para resolver problemas no mundo real.
A sintaxe da função mod
Antes de mostrar a função MOD em ação, vamos reservar um momento para ver como funciona:
=MOD(n,d)
onde n é o número que você deseja dividir e d é o divisor. Portanto, se você deseja dividir 3 por 2, 3 é o número e 2 é o divisor.
Se o divisor for negativo, o resultado também será negativo. Por outro lado, se o divisor for 0, o Excel retornará o #div/0! Erro, pois você não pode dividir um número por zero.
No entanto, a função MOD raramente é usada por conta própria – seu valor verdadeiro realmente vem à tona quando é usado ao lado de outras funções e ferramentas do Excel.
Exemplo 1: Usando a função Mod para minimizar as sobras
Digamos que você tenha vários itens que deseja embalar e distribuir. A coluna A contém o ID do produto, a coluna B informa quantos de cada produto você possui e a linha 2 contém os diferentes tamanhos de embalagem. Seu objetivo é descobrir o tamanho ideal do pacote para cada produto, dependendo de quantos de cada produto você possui.
Nesse caso, você pode usar a função Mod para dizer se a contagem de produtos na coluna B é divisível pelo tamanho da embalagem na linha 2 e, se não for, quantos você sobrou.
Então, na célula C3, tipo:
=MOD($B3,C$2)
O símbolo do dólar ($) colocado antes da referência de uma linha ou coluna – também conhecida como um referência mista—Tells se destacam para travá -lo para que a fórmula possa ser aplicada a outras células no intervalo.
Ao pressionar Enter (ou Ctrl+Enter para ficar na mesma célula), você poderá ver que, se usar um pacote com 10 espaços para o produto 2805, quatro permanecerão descompactados.
Agora, quando você clica e arrasta a alça de preenchimento na célula C3 para a direita para concluir a linha, pode ver que o uso de pacotes com oito ou três espaços é ideal para este produto.
Em seguida, na célula G3, você deseja que o Excel lhe diga o melhor tamanho de pacote a ser usado para esse produto. Para fazer isso, use o índice e o xmatch:
=INDEX($C$2:$F$2,1,XMATCH(SMALL(C3:F3,1),C3:F3))
onde ÍNDICE ($ C $ 2: $ F $ 2,1 diz ao Excel para retornar um valor das células C2 a F2 e Xmatch (pequeno (C3: F3,1), C3: F3)) procure o menor valor nas células C3 a F3 para definir o número da coluna que alimenta a parte do índice da fórmula.
Por fim, selecione as células C3 a G3 e clique duas vezes na alça de preenchimento para duplicar todas as fórmulas às linhas restantes no intervalo.
Agora, graças à função MOD usada nas células C3 a F12, a coluna G informa os melhores tamanhos de embalagem a serem usados para cada produto, e você pode procurar nas colunas C para f para cada produto para ver quantos (se houver) você terá sobrado após o uso desse tamanho ideal do pacote.
Se o menor valor ocorrer duas vezes seguidas, o Xmatch usará a primeira instância da esquerda para a direita por padrão. Nesse caso, isso é ideal porque você deseja usar o mínimo de embalagem possível.
A beleza de usar essa combinação de funções é que, se a contagem de produtos nas alterações da coluna B, as atualizações ideais do tamanho do pacote de acordo com as fórmulas de mod que você usou nas células C3 a F12. Por exemplo, se você originalmente ergueu o produto 2805, e realmente tem 25 em vez de 24, quando atualiza o valor na célula B3, achará que o tamanho ideal do pacote muda para cinco.
Exemplo 2: Usando a função mod para destacar certas células
A função MOD do Excel não precisa ser usada para retornar um valor – também pode ser usada para determinar quais células devem ser formatadas através da formatação condicional.
Neste exemplo, imagine que você deseja criar atividades para dez grupos de pessoas. No entanto, diferentes atividades requerem diferentes números de subgrupos; portanto, você precisa determinar quais grupos podem realizar quais atividades, garantindo que todos os membros estejam envolvidos.
Primeiro, na célula B1, você deseja criar uma lista suspensa das atividades na coluna D. Para fazer isso, com a célula B1 selecionada, vá para a guia “Dados” na faixa de opções e clique na metade superior do botão “Validação de dados”.
Segundo, no campo de permitir a caixa de diálogo Validação de dados, escolha “Lista”. Em seguida, coloque seu cursor no campo de origem e selecione as células que contêm os valores que você deseja aparecer na lista suspensa. Em seguida, clique em “OK”.
Relacionado
Agora, quando você seleciona a célula B1, pode ver uma lista suspensa de atividades e está pronto para aplicar a formatação às células A4 a B13, que informa se cada grupo pode realizar cada atividade.
Para fazer isso, selecione as células A4 a B13 e, na guia Home na faixa de opções, clique em Formatação Condicional> Nova regra.
Relacionado
Em seguida, na nova caixa de diálogo de regra de formatação, selecione “Use uma fórmula para determinar quais células formatarem” e no campo da fórmula, digite:
=MOD($B4,XLOOKUP($B$1,$D$2:$D$9,$E$2:$E$9))=0
onde
- $ B4 é o primeiro das células na coluna B atuando como o número a ser dividido,
- Xlookup ($ B $ 1, $ D $ 2: $ D $ 9, $ E $ 2: $ E $ 9) procure o valor na célula B1 e retorna o tamanho do subgrupo correspondente da tabela de pesquisa (o divisor) e
- = 0 Aplica a formatação às células onde o número de membros em um grupo é divisível pelo número de pessoas necessárias no subgrupo de uma atividade.
Colocar o símbolo do dólar antes da referência da coluna ($ B4) nesta fórmula diz ao Excel que você deseja que a formatação esteja condicionada a todas as células selecionadas na coluna B.
Em outras palavras, você deseja que o Excel formate os grupos em que todo o tamanho do grupo é divisível pelo tamanho do subgrupo necessário para a atividade selecionada na célula B1.
Por fim, clique em “Formato” para escolher a formatação para as células onde essas condições são atendidas. No meu caso, fui para um fundo de célula verde clara.
Clique em “OK” para fechar a caixa de diálogo.
Agora, selecione uma atividade no menu suspenso na célula B1 e os grupos que podem participar dessa atividade serão destacados de acordo com a formatação que você selecionou. Neste exemplo, você pode ver que os grupos 1, 2, 3, 5 e 8 podem participar do teste porque o número total de membros nesses grupos é divisível pelo tamanho do subgrupo necessário para esta atividade.
Por outro lado, quando você seleciona “caminhadas” da lista, apenas o grupo 5 é identificado como capaz de fazer essa atividade, pois é o único grupo cujo número geral é divisível pelo tamanho do subgrupo.
Exemplo 3: Usando a função mod para cuspir valores de data hora
A função MOD do Microsoft Excel pode ser usada para extrair a parte decimal de um número. Neste exemplo, digitando:
=MOD(A2,1)
na célula B2 divide 10.2 por 1 e diz que o restante desta divisão é 0,2.
Você também pode extrair o número inteiro desse número usando a função int, que arredonda um valor para o número inteiro mais próximo. Então, digitando:
=INT(A2)
na célula C2 remove o decimal do valor na célula A2, retornando apenas o número inteiro.
Da mesma forma, você pode usar o MOD e o INT para dividir os valores de data de data.
Aqui, a célula A2 contém uma data e hora, e a célula A3 contém o número de série correspondente.
O Excel armazena as datas e os tempos como números de série, que podem ser vistos quando você altera o formato numérico para “geral”. Portanto, neste exemplo, os valores das células A2 e A3 são idênticos, mas o formato numérico para a célula A2 é um formato de número de data para data personalizado, enquanto o formato de número para a célula A3 é o formato de número geral.
No número de série na célula A3, 45782 representa a data e 0,4375 representa o tempo, e você pode usar o MOD e o INT para dividi -los.
Primeiro, selecione a célula B2 e altere o formato numérico para “tempo” no grupo numérico da guia Home na faixa de opções.
Relacionado
Em seguida, selecione a célula C2 e altere o formato número para “Data curta”.
Em seguida, volte para a célula B2 e digite:
=MOD(A2,1)
Isso extrai o decimal (tempo) a partir da representação do número de série do valor de data do tempo na célula A2 e, como você define o formato do número de células como o tempo, converte o decimal serial em um valor temporal.
Finalmente, volte para a célula C2 e digite:
=INT(A2)
Isso extrai o número inteiro (data) do número de série na célula A2, arredondando -o para o número inteiro mais próximo e, como você define isso como um formato de número de data curto, converte o número inteiro de série em um valor de data.
Metade da batalha com o uso de funções no Microsoft Excel é saber qual é o melhor para resolver seu problema. Por exemplo, se você deseja refinar seus cálculos, o Função agregada provavelmente fará o truque e, se você quiser criar uma lista de números, o Função de sequência é a sua melhor aposta. Felizmente, o Excel está aqui para ajudá -lo Encontre a função que você precisa– Você pode clicar no botão “FX” ao lado da barra de fórmula ou pesquisar uma função na caixa de diálogo Função Inserir, e as ferramentas de função do Excel o ajudarão ao longo do caminho enquanto você cria a fórmula perfeita.
Deixe um comentário