EXCELer

É com muito gosto que informo da existência de um Grupo de Discussão sobre Microsoft Office no Brasil, do qual sou, a partir de ontem, dia 28-12-2005, membro: Microsoft Users Group Rio Grande do Sul - Brasil

Como é sabido, o caracter " / " (slash) é um caracter especial em formatos numéricos e que é usado em datas e fracções.

Mas de que modo podemos fazer com que o Excel trate tal caracter de um modo literal numa formatação, como por exemplo: 1234/56789?

O modo como o Excel "força" o caracter " / " (slash) a ser tratado literalmente, é precedendo-o com o caracter " \ " (backslash).

Claro que a introdução é efectuada como um número inteiro, ou seja, 123456789, para obter o resultado 1234/56789.

Vejamos o exemplo:



Utilizando a formatação personalizada,




Obteremos então:

Um dia destes, foi-me perguntado, por mail, como é que se consegue, numa coluna, obter uma sequência de datas coincidentes com o fim de cada mês, assim:
31-01-2005
28-02-2005
31-03-2005
etc .....

Tomemos o seguinte exemplo:

No Range A1:A12, introduzem-se os 12 meses do ano e em A13, insere-se o ano pretendido:




O Código, para uma possível solução, em C1:

=DAY(DATE($A$13;MONTH(DATEVALUE(A1&"-"&$A$13))+1;0))&"-"&MONTH(DATEVALUE(A1&"-"&$A$13))&"-"&$A$13

NOTA 1: Esta fórmula deve ser copiada até à célula C12, para indicar o último dia correspondente a cada mês.

NOTA 2: Alterando o ano em A13, por exemplo, para 2008, verificaremos que o último dia do mês de Fevereiro passará a ser 29, uma vez que 2008 é um ano bissexto.

Por norma, a ordenação ascendente ou descendente, é efectuada de cima para baixo (Top to Bottom):



mas também pode ser efectuada da esquerda para a direita (Left to Right):



No entanto, como fazer, de modo a efectuarmos múltiplos sorts Left to Right. como, por exemplo, no seguinte Range:



de maneira a, de uma só vez, ficar como segue?



Tom Ogilvy, mostrou, em 2001, num newsgroup de Excel, como se pode fazer este tipo de sort, através de VBE.

O Código, adaptado:

Private Sub CommandButton1_Click()
'baseado numa macro de Tom Ogilvy, 2001-03-24, em "Excel Programming"
Dim rw As Range
If Selection.Columns.Count = 1 Then
   MsgBox "Seleccionar mais do que 1 célula ou mais do que 1 coluna"
   Exit Sub
End If
For Each rw In Selection.Rows
    rw.Sort key1:=rw, Order1:=xlAscending, Header:=xlNo, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlLeftToRight
Next
End Sub

Se tivermos uma Tabela, da qual queiramos apurar quantas horas trabalhou um certo empregado num determinado dia, inserindo em duas células (E2 e E3) os dados correspondentes,como no exemplo:



podemos utilizar a Função SUM() associada ao asterisco (*), que aqui funciona como o operador AND.

NOTA: Como se trata de um Array, não esquecer de utilizar {}, através das teclas CTRL + Shift + Enter na fórmula activa.


A fórmula em E11:

{=SUM($C$1:$C$8*($A$1:$A$8=E2)*($B$1:$B$8=E3))}

Ontem, um amigo meu veio ter comigo e disse-me que pretendia uma macro de modo a que pudesse seleccionar um Range variável.

Por exemplo, numa tabela A1:B10, poder, a partir de um número variável numa determinada célula, seleccionar desde A1 até à linha correspondente a esse número na coluna B:



ou seja, no exemplo, digitar o nº 3 na célula D1 e executar a macro, de modo a ser seleccionado o Range "A1:B3":



ou, de igual modo, digitar o nº 8 na célula D1 e executar a macro, de modo a ser seleccionado o Range "A1:B8":




Eis, então, um possível Código:

Private Sub CommandButton1_Click()
    Dim var
    var = Range("D1").Value
    Range("A1", "B" & var).Select
End Sub

Há dias, num newsgroup de Excel, foi colocada a seguinte questão:

"Se tivermos a seguinte tabela no Excel dentro do intervalo A1:C4



e tendo definido [Inserir > Nome > Definir] os seguintes nomes:TMN=B3:C4; Econo=B3:B4; Norm=C3:C4; Mimo=B3:C3; Pako=B4:C4", como mostra o exemplo:



"Se colocarmos a seguinte formula na celula E10: =TMN Econo Pako, irá aparecer o valor da intersecção dos 3 nomes(ou seja 3 conjuntos) que é 25:"



Agora, a pergunta:

"Qual a fórmula que devo pôr nas células E10 e F10 para fazer a intersecção dos três nomes que eu colocar nas três linhas anteriores dessa coluna, ou seja, nos Ranges E7:E9 e F7:F9, de modo a dar os valores da intersecção - 25 e 35?"

Uma possível resposta:

Continuando a utilizar o caracter espaço (tecla de espaço) como o operador de intersecção, então, podemos socorrer-nos da Função INDIRECT() [em Português INDIRECTO()].

O Código:

Em E10:

=INDIRECT(E7) INDIRECT(E8) INDIRECT(E9)


Em F10:

=INDIRECT(F7) INDIRECT(F8) INDIRECT(F9)

A Fórmula em L10 (com Copy & Paste até L14):

=DAYS360($M$4;(M10&"-"&$O$6&"-"&$O$7))/7+1-5400

Nota: A célula M4 é uma célula vazia, apenas representando a data de início para a Função Days360(), ou seja, o valor 0 (zero):

Um calendário básico:



Em O6 - o mês

Em O7 - o ano

Em M9:
=WEEKDAY($O$6&"-"&$O$7)

Em N9:
=M9+1

Em O9:
=N9+1

O mesmo até S9.

Para mostrar o dia corrente noutra cor, como no exemplo, marca-se a tabela com os 31 algarismos correspondentes aos dias do mês:



Escolhe-se Formatar Condicionalmente:



e, no terceiro campo da condição, coloca-se a fórmula:

=DAY(TODAY())


Agora, para que os dias do mês coincidam com o mês em causa, ou seja, meses com 30 dias, meses com 31 dias e Fevereiro com 29 ou 28 dias, conforme seja ano bissexto ou comum, teremos que construir a seguinte fórmula, no exemplo, em S6:

=DAY(DATE(O7;MONTH(DATEVALUE(O6&"-"&O7))+1;0))

Por último, na célula onde deve ficar o algarismo 29, ou seja, no exemplo, em M14, colocamos a seguinte fórmula:

=IF(S6=28;"";29)

Na célula correspondente a 30:

=IF(S6<30;"";30)

E na célula correspondente a 31:

=IF(S6=31;31;"")

É com grande orgulho que torno público que a Microsoft me reconheceu como Microsoft Most Valuable Professional [MVP] - Excel

Para incrementar linearmente, iniciando num valor definido numa célula e indicando noutra o intervalo do incremento, ou seja, iniciando o incremento, por exemplo no valor 0 (em A1) e incrementando de 5 em 5 (valor dado em B1):



Podemos utilizar a seguinte fórmula em A2 (vista num newsgroups de Excel e apresentada por BenjieLop):

=IF(OR($A$1="";$B$1="");"";A1+$B$1)

Nota: a fórmula deve ser copiada para tantas células quantas as que se pretendam constituir como parte do incremento.

Se pretendermos utilizar outro tipo de fórmula para obtermos o número de anos, em vez de =DATEDIF(A1;A2;"Y"), podemos usar as Funções VALUE() e DAYS360():



NOTA: A célula que irá conter a fórmula indicada em baixo, deverá ser formatada como número inteiro.

A fórmula:

=VALUE(DAYS360(A1;A2)/360)-1

Para subtrair duas datas, de modo a que o resultado seja dado em anos, meses e dias, como no exemplo:




Podemos utilizar a seguinte fórmula com a função DATEDIF() :

=DATEDIF(A1;A2;"Y") & " ano(s), " & DATEDIF(A1;A2;"ym") & " mês(es) e " & DATEDIF(A1;A2;"md") & " dia(s) "

Se pretendermos criar uma sequência aleatória de letras e números, como no exemplo



podemos utilizar algumas Funções em VBE.

O Código:

Sub GerarLetrasNumeros()

    Dim obj As Object
    Dim linha As Integer, coluna As Integer
    Dim r As Integer, c As Integer

    Set obj = Range("A1")
    
    linha = obj.Row
    coluna = obj.Column
    
    For r = 0 To 10
        For c = 0 To 5
            Cells(linha + r, coluna + c).Value = Chr(65 + Int(10 * Rnd)) _
            & Chr(65 + Int(10 * Rnd)) & Chr(65 + Int(10 * Rnd)) _
            & Chr(48 + Int(10 * Rnd)) & Chr(48 + Int(10 * Rnd)) _
            & Chr(48 + Int(10 * Rnd))
        Next c
    Next r

End Sub

Se pretendermos somar dois tipos de ocorrências, como no exemplo



podemos criar uma pequena fórmula, utilizando as Funções SUM() e COUNTIF(), esta última, com um array.

O Código:

=SUM(COUNTIF(A1:A20;{"bom";"muito bom"}))

Se pretendermos que o utilizador digite, por exemplo, na célula "A1", valores que não sejam negativos ou zero, como no exemplo:





podemos socorrer-nos de um pouco de VBA.

O Código:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If IsEmpty(Range("A1")) Then
        Exit Sub
    End If

    If Range("A1") < 0 Then
        MsgBox "O valor não pode ser negativo! Tente outra vez!!!"
        Range("A1").ClearContents
    ElseIf Range("A1") = 0 Then
        MsgBox "O valor não pode ser zero! Tente outra vez!!!"
        Range("A1").ClearContents
    Else
        Exit Sub
    End If
    
End Sub

Se pretendermos efectuar cálculos com horas, em que a hora de início é superior à hora do fim (no pressuposto que esta última é já no dia seguinte) podemos utilizar a seguinte fórmula em Excel:

O Código:

Sub Auto_Open()
    Dim num As Integer

    num = ActiveWorkbook.Worksheets.Count
    ActiveWorkbook.Worksheets(num).Activate

End Sub

Se pretendermos que, em determinada coluna, os números negativos sejam apresentados a BOLD, como no exemplo,





podemos utilizar um pouco de VBA.

O Código:

Private Sub CommandButton1_Click()
Sheets("Sheet1").Select
    Columns("A:A").Select
    
    On Error Resume Next
    
    Call CheckCells(Selection.SpecialCells(xlConstants, 23))
    Call CheckCells(Selection.SpecialCells(xlFormulas, 23))
    
    Range("b1").Select
    
End Sub

Sub CheckCells(CurrRange As Range)

    For Each cell In CurrRange

        If cell.Value < 0 Then
            cell.Font.Bold = True
        End If

        If cell.Value >= 0 Then
            cell.Font.Bold = False
            Selection.Interior.ColorIndex = 0
        End If

    Next cell

End Sub

Se pretendermos criar uma mensagem de informação a partir de um botão de comando, como no exemplo seguinte,



podemos utilizar um pouco de VBA.

O Código:

Private Sub CommandButton1_Click()
    Dim Ops(1 To 3) As String
    Dim Msg As String
    Dim Texto As String

    Application.Cursor = xlNormal

    Ops(1) = "Elaborado por: "
    Ops(2) = "EXCELer, "
    Ops(3) = "Vilamoura, Julho de 2005"

    Texto = Ops(1) + vbCr
    Texto = Texto + Ops(2) + vbCr
    Texto = Texto + Ops(3)

    Msg = MsgBox(Texto, Buttons:=vbInformation, Title:="AUTOR")

    Select Case Msg
    End Select

End Sub

Por vezes, temos necessidade de criar um registo de entradas em determinado workbook, que, para além de conter o nome do utilizador, poderá ainda conter a data e a hora do acesso:



Resultado:



O Código:

Sub Auto_Open()
Dim Ops(1 To 5) As String
Dim msg As String

Ops(1) = Day(Date)
Ops(2) = Month(Date)
Ops(3) = Year(Date)
Ops(4) = Hour(Time)
Ops(5) = Minute(Time)

msg = Ops(3) & "-" & Ops(2) & "-" & Ops(1) & " " & Ops(4) & ":" & Ops(5)

Sheets("Sheet1").Select

Range("A65536").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select

Application.Cursor = xlNormal

Do While IsEmpty(ActiveCell)

ActiveCell.FormulaR1C1 = InputBox(Prompt:="Introduza o seu NOME:", _
Title:="Nome do Utilizador")
ActiveCell.FormulaR1C1 = UCase(ActiveCell)

Loop

Range("B65536").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = msg

End

End Sub

Se pretendermos que, ao abrir um workbook, nos seja pedido para digitarmos o nome do utilizador, podemos utilizar o seguinte exemplo:






O Código:

Sub Auto_Open()
Range("B1").Value = Application.InputBox("Escreva o seu nome")
End Sub

A função EOMONTH(), que se encontra no Analysis ToolPak add-in, devolve o número de série correspondente ao último dia do mês que é apurado somando ou diminuindo o número de meses pretendido ao mês que serve de base, sendo a sintaxe a seguinte: EOMONTH(start_date,months).
No entanto, se não se tiver o add-in mencionado, pode efectuar-se o mesmo tipo de cálculo, utilizando as funções DATE(), YEAR() e MONTH() em conjunto. A única diferença é que no segundo caso, temos que adicionar ou subtrair 1 mês, para podermos obter o mesmo resultado:






Nota: as células B1 e B3 estão formatadas como data, porque se estiverem formatadas como geral, a devolução é o número de série.

Marina Limeira (Forum brasileiro de Office), perguntou como seria possível escolher um fundo da folha de cálculo, de acordo com a seguinte alternativa:

Se fosse maior (>) que 50, o fundo seria uma imagem; se fosse inferior ou igual (<=) a 50, então a imagem seria outra.

Utlizando o Evento SelectionChange, podemos ter o seguinte resultado:

> 50:


<=50:


O Código:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Range("A1") > 50 Then
        Worksheets("Folha1").SetBackgroundPicture "E:\camaleão5.jpg"
    Else
        Worksheets("Folha1").SetBackgroundPicture "E:\butterflies.jpg"
    End If
End Sub

No post anterior, mostrei um exemplo com uma fórmula para contar o número de células com valor compreendido entre 0 e 15 dentro de um Range fixo ( no exemplo: A1:A6).

Mas como fazer, se pretendermos que o Range possa ser variável, ou seja, podendo ser A1:A6, ou A1:A8 ou A1:A12?. Então, o que teremos que fazer é construirmos a formula de modo a que à variável (x) em A1:A(x) seja atribuído um valor sem ser necessário estar sempre a alterar a fórmula:

Neste caso, sem termos que recorrer ao VBA, podemos utilizar uma célula de referência, a qual irá "informar" a fórmula de qual será o valor que irá receber, para que o Range pretendido seja avaliado.

No exemplo, a célula de referência é E1, que deve ser formatada como texto:
que pode ser formatada como número:



O Array em B1:

{=SUM((INDIRECT("A1:A"&VALUE(E1))>0)*(INDIRECT("A1:A"&VALUE(E1))<=15))}


=SUM((INDIRECT("A1:A"&E1)>0)*(INDIRECT("A1:A"&E1)<=15))

NOTA: Para obter o Array {} utilizar - CTRL + Shift + Enter

Num forum sobre Excel, foi colocada a seguinte questão:
"Tenho uma tabela e gostaria de criar uma fórmula para contar o número de células com valor entre 0 e 15".

Vejamos então o exemplo:



Na tabela, verifica-se que existem 5 células com valores > 0 e <= 15. Então, em B1 podemos escrever:



O Array:

={SOMA((A1:A6>0)*(A1:A6<=15))}

NOTA: Para obter o Array {} utilizar - CTRL + Shift + Enter

Num newsgroup de Excel, foi colocada a seguinte questão:

"Gostava que em determinadas àreas de uma worksheet só fosse possível escrever em maiúsculas. Existe alguma maneira de formatar uma célula dessa maneira?"

De notar que foi afirmado não se pretender usar do Evento WorkSheet_Change().

Bom, uma possível resposta a esta questão será socorrermo-nos de uma formatação de células em que a fonte a utilizar possua somente maiúsculas, como é o caso do exemplo seguinte:



No exemplo, utilizou-se uma TTF (Invite Engraved SF). Assim, ao escrever-se na célula A6 "exceler" (em minúsculas), o que aparece é o seu conteúdo em maiúsculas. De notar que o Excel assume internamente a função UPPER() ou MAIÚSCULAS(), ou seja, é como se tivessemos escrito na célula A6: =UPPER("exceler").

Leal Diogo, a propósito de um post meu, datado de 30 do passado mês de Abril, sobre uma UDF [NomeLF()] que devolve o nome da folha, (opção "F"), formulou a seguinte questão:

"Se se tiverem 2 folhas em que se faz uso desta função, sempre que se mude o nome de uma das folhas a outra também devolve o mesmo nome.
Como é que se poderá parar o efeito nas outras folhas?"

De facto, perante esta pergunta, a resposta só poderá ser esta: a UDF referida, não resolve esta questão, uma vez que, como é referido, mudando-se o nome de uma das folhas, a outra devolve igualmente o nome da folha modificada.

A resolução do problema assenta na inclusão da Propriedade Caller associada ao objecto Application, ou seja do código   Application.Caller numa outra função, a que chamaremos TesteNomedaFolha():



O Código:

Public Function TesteNomedaFolha()
    Application.Volatile
    Dim rng As Range
    Set rng = Application.Caller
    TesteNomedaFolha = rng.Parent.Name
End Function


Nota: Função elaborada a partir de código apresentado num forum por Tom Ogilvy - MVP Excel, em Maio de 2000.