Fórmulas matriciais - Excel

Fórmula Matricial
Criando a tabela acima, digite a seguinte fórmula na célula 'F3':
=SOMA(SE(A2:A11=F2;SE(B2:B11=F1;C2:C11;0);0))
Não tecle 'Enter', para o Excel entender que se trata de uma fórmula matricial, tecle 'Ctrl + Shift + Enter'; irão aparecer automaticamente chaves* nas extremidades da fórmula. As chaves não devem ser digitadas, elas aparecem ao teclar 'Ctrl + Shift + Enter'

Tutorial Função SOMASE() - Excel

Função somse()

Intervalo e IntervaloSoma precisam obrigatoriamente ter o mesmo tamanho.

O resultado em 'D2' será 60 e em 'D3' será 65.

Mover dados da planilha usando matriz (array) - Excel

Precisava de uma maneira de transportar os dados de um lugar para outro da planilha do excel sem precisar gravar uma macro com CTRL+X e CTRL+V. O inconveniente de se usar esses comandos é que se precisava "colar especial" "somente valores" para não estragar a formatação da planilha, já que o local de origem dos dados tinha formato diferente do local de destino. Não é bom ficar complicando o código de macros gravadas pois algumas vezes tornam-se incompatíveis com versões diferentes do office. A solução foi desenvolver um código simples que fizesse o mesmo serviço. Esse exemplo exibe um inputbox perguntando a primeira célula de origem dos dados e a primeira célula de destino dos dados na planilha do excel. A macro carrega os dados num vetor, apaga da planilha e descarrega na nova posição dentro da planilha.
Segue o código em vba:

Sub transfere()
Dim matriz
Dim l, c, linha, coluna As Long
Dim origem, destino As String

origem = InputBox("Informe a célula de origem...", "Etapa 1", "A1")
destino = InputBox("Informe a célula de destino...", "Etapa 1", "A1")

Range(origem).Select
l = 0
c = 0
Do While Not ActiveCell.Offset(l, 0).Value = ""
l = l + 1
Loop

Do While Not ActiveCell.Offset(0, c).Value = ""
c = c + 1
Loop

ReDim matriz(1 To l, 1 To c)

For linha = 1 To l
For coluna = 1 To c
matriz(linha, coluna) = ActiveCell.Offset(linha - 1, coluna - 1)
ActiveCell.Offset(linha - 1, coluna - 1).Value = ""
Next
Next

Range(destino).Select

For linha = 1 To l
For coluna = 1 To c
ActiveCell.Offset(linha - 1, coluna - 1).Value = matriz(linha, coluna)
Next
Next

End Sub

Redimensionar matriz (array) em VBA - Excel

Em C e C++ usa-se a função "Malloc" para alocar memória dinamicamente, evitando assim declarar matrizes muito grande que nem sempre serão usadas totalmente. Algo similar em VB e VBA e a função  ReDim. Pode-se calcular através de variáveis a extensão que matriz precisa ter e atribuir isso a ela antes de carregá-la com os dados. Veja a seguir.

O nome da função que vamos usar é ReDim

Sub RedimensionarMatriz(x, y)
Dim MyMatriz
ReDim MyMatriz (1 To x, 1 To y) As Integer
End Sub

Declare a matriz sem tamanho e sem o tipo de dados, a função ReDim fará as atribuições conforme for especificado. A matriz pode ser redimensionada quantas vezes se achar necessário.

Declarar Matriz (Array) em VBA - Excel

Quando no vba lidamos com muitos dados de uma planilha do excel, torna-se essencial o uso de Array (Matriz). Para declarar esse tipo de variável o sintaxe é bem simples:

Dim MyArray (1 to 10)
'Obtém-se um vetor de 10 posições

Dim MyMatriz (1 to 10, 1 to 2)
'Obtém-se uma matriz de 10 linhas por 2 colunas
'Atribua na frente da matriz declarada o tipo de dados como em
'qualquer variável:
'... as Integer
'... as Double
'... as String
'... etc

Separar numeros de letras da célula - Excel

Escrevi essa função uma vez que precisei descobrir coluna e linha da célula ativa da planilha. É de boa ajuda para auxiliar projetos em VBA no Excel.

Function EspecificaCelula(Cel As String, Letra As Boolean)
Dim Vargs: Dim l, N, Aux As String

Vargs = Array("0", "1", "2", "3", "4", "5", "6", "7", "8", "9", "$"): Aux = Cel
For i = 0 To 10: Aux = Replace(Aux, Vargs(i), ""): Next
l = Aux: N = Replace(Replace(Cel, l, ""), "$", "")
If Letra = True Then EspecificaCelula = l Else EspecificaCelula = N

End Function

Excluir dados repetidos (duplicados) ordenados em ordem crescente em coluna - Excel

Um problema comum para quem trabalha com muitos dados no excel é ter que excluir dados duplicados de uma coluna para montar um relatorio por exemplo. A função a seguir tem esse objetivo, ela testa linha por linha se o valor já existe na coluna e remove apenas os repetidos. Supondo que os dados estejam na coluna "A" da planilha, o código ficaria assim:

Sub ExcluiDuplicadosOrdenados()
Dim l As Long
Dim str, linha As String

l = 1
Range("A1").Select
str = Range("A1").Value
Do While Not ActiveCell.Offset(l).Value = ""
If ActiveCell.Offset(l).Value = str Then
GoSub Excluir
Else
str = ActiveCell.Offset(l).Value
l = l + 1
End If
Loop

Exit Sub

Excluir:
linha = l + 1 & ":" & l + 1
Rows(linha).Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Return

End Sub

Formatar número em TextBox - Excel

Função de apenas 1 linha pra colocar no exit do text box formatando dados numéricos em formulários criados em VBA no Excel.

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Me.TextBox1.Value = Format((TextBox1.Text), "###,###,##0.00")
End Sub

Tutorial Fução SE - Excel

A função SE do Excel retorna um valor se uma condição que você especificou for considerada VERDADEIRO e um outro valor se essa condição for considerada FALSO. Exemplo:

=SE(A1>10,"Maior","Menor")

Se o valor na célula A1 for um número maior que 10, o resultado da fórmula sera o texto "Maior", mas se A1 for menor que 10, o resultado será o texto "Menor".

A função SE é muito útil, mas ela só faz um teste. Se precisar resolver um problema que exijam dois testes, por exemplo:

1 - Liberar a entrada de uma pessoa no cinema se ela for maior de idade ou tiver autorização dos pais.

2 - Liberar um caminhão na portaria se o peso não é menor que a nota fiscal e nem exceder mais de 1% do peso previsto.

Nesses casos a função SE pode ser composta de outras duas funções: =E() e =OU().

Falarei delas no próximo post.

Tutorial função SE composta com função E - Excel

Tutorial função SE com função OU - Excel

A função OU retorna VERDADEIRO se qualquer argumento for VERDADEIRO; retorna FALSO se todos os argumentos forem FALSOS.

Exemplo
Pense em um filme onde pode entrar apenas maiores de idade ou menores de idade com autorização:

A fórmula ficaria assim: =SE(OU(B1>=18;B2="Sim");"Liberado";"Barrado")

A função OU vai retornar VERDADEIRO se uma ou mais condições forem atendidas. Se a pessoa for maior de idade, o primeiro teste já é verdadeiro e ela não vai precisar executar o segundo teste. Mas se a pessoa for menor de idade (FALSO para o primeiro teste), o segundo teste é quem decide se ela entra ou não no cinema. Resumindo, a função OU extendeu a função E que antes suportava apenas um argumento e agora suporta quantos forem inseridos na função OU.

Truncar e Arredondar número - Excel

Converter número Binário, Decimal, Hexadecimal e Octal no Excel

Como copiar todas as fórmulas de uma determinada planilha para outra e criar e salvar um arquivo texto com essas mesmas fórmulas

Sub Copia_formula()
Dim stgFormula As String

stgFormula = Sheets("Plan1").Range("B14").Formula

Sheets("Plan2").Range("B1").Formula = stgFormula

Sheets("Plan2").Range("B2").Formula = _
Sheets("Plan1").Range("B14").Formula

'salva as formulas no txt
Open "Salvar_Formula.txt" For Output As 1
Write #1, stgFormula
Write #1, Sheets("Plan2").Range("B1").Formula
Close 1
End Sub

seleciona somente as células que contenham fórmulas, para que eu possa deletá-las, ou fazer outra coisa qualquer.

Sub Seleciona_todas_Celulas_Com_Formulas()
Selection.SpecialCells(xlCellTypeFormulas, 23).Select
'Selection.ClearContents
'Selection.Delete