Blog Archive

About Me

A minha foto
JRod - PORTUGAL
Microsoft [MVP] - Excel (10º ano consecutivo)
Ver o meu perfil completo
Com tecnologia do Blogger.

Seguidores

Estatisticas

Free Blog Counter

eXTReMe Tracker
2009-12-22

Há dias, num grupo de discussão, colocaram a seguinte questão (adaptada):

“ como posso fazer para criar uma tabela onde por cada entrada me indique a data e hora do momento, mas que de cada vez que  execute a folha, as datas das entradas anteriores não sejam todas  actualizadas?”

Vejamos o exemplo:

image

Podemos, então, construir o seguinte código, que colocaremos no Workbook, de modo a que, logo que o mesmo seja aberto, o execute, para criar uma entrada com data e hora de acesso:

Option Explicit

Private Sub Workbook_Open()
    Dim strDate As String

    strDate = Format(Date, "dd-mm-yyyy") & " / " & Format(Time, "hh:mm:ss")
    Range("A1").Select
    If Range("A2") = "" Then
        Range("A2") = strDate
    Else
        Selection.End(xlDown).Select
        ActiveCell.Offset(1, 0).Select
        ActiveCell.Value = strDate
    End If

End Sub

Nota: Este código deverá estar no workbook module (Este Livro ou This Workbook)

Tópicos relacionados:

2009-12-13

Num grupo de discussão do Excel, há já algum tempo, apresentaram a seguinte questão (adaptada):

“ Se eu digitar a seguinte fórmula numa célula = CONTAR.SE(E5:K5;”P”) e se existir algum “P” no range E5:K5, a célula é contada.

E se eu quiser contar nesse mesmo range as células que contenham “P”, “T” e “L”?

 

Vejamos o exemplo:

image

O resultado será 2.

Mas, se incorporarmos o “T” e o “L”, o resultado será 4.

Novamente o exemplo, com a fórmula adequada ao resultado pretendido. (mérito para o malogrado Frank Kabel - Permanent Link to Frank Kabel).

image

2009-12-05

Num grupo de discussão do Excel, foi apresentada a seguinte questão (adaptada):

“ Preciso saber como alterar o conteúdo  de um conjunto de células mas tenho a seguinte dificuldade:
Numa célula ou em várias tenho, por exemplo =soma(a5:a15), quero alterar o "5" para "6" passando a ter =soma(a6:a15) e o que obtenho é =soma(a6:a16), como posso apenas alterar o primeiro "5" sem mexer no segundo "5"?”

Para obstar tal situação, teremos que nos socorrer de algum expediente.

Assim, para obtermos o componente Range “A15”, sem que o mesmo possa ser alterado quando estamos a efectuar a substituição do algarismo “5”, podemos digitar o seguinte, por exemplo, na célula E200: ="A"&14+1, o que irá mostrar o conteúdo da célula como “A15”, apesar de não ter nenhum algarismo “5”:

image

Então, se pretendermos ter a soma(A5:A15) sem termos o segmento do range “A15”, mas sim, em sua substituição, o conteúdo de E200 (que será A15), digitaríamos, por exemplo em E5: =SOMA(A5:INDIRECTO(E200))

image

O resultado esperado seria 14.

Agora, se pretendermos utilizar o “Localizar e Substituir”:

image

Teríamos, então, o resultado esperado: 13

image

Ou seja, quando for para substituir "fórmulas" (em Opções>>), de 5 para 6, vai dar o resultado que se teria com a alteração da fórmula para =SOMA(A6:INDIRECTO(E200)), ou seja, o mesmo que  SOMA(A6:A15).

 

Tópicos relacionados:

2009-12-03

Há dias, num grupo de discussão, apareceu a seguinte questão (adaptada):

“venho solicitar a vossa ajuda para o seguinte:
copiei uma tabela da Net e na coluna que tem 1-1 ou 1-2 o Excel assume como um de Janeiro e um de Fevereiro respectivamente. Quando tento formatar a célula para texto, para que fique na forma original, ele coloca um numero 32 mil e qualquer coisa...
Alguém pode ajudar?”

Vejamos o exemplo dado:

image

image

Se tentarmos formatar as células como texto, irá aparecer o seguinte:

image

Então, para ultrapassarmos a situação e para que consigamos ter, por exemplo, nas células B1, B2 e B3, respectivamente os dados pretendidos a partir do conteúdo das células A1, A2 e A3, ou seja, 1-1, 1-2 e 1-3, para que fique assim:

image

Devemos construir a seguinte fórmula em B1, copiando-a até B3:

=TEXTO(A1;"d-m")

2009-11-12

Sam Howley (http://www.querycell.com/), enviou-me um e-mail, referindo um artigo que escreveu para ajudar os utilizadores do Add-In que ele criou: Excel Add-In QueryCell, o qual podem ler em Introduction to SQL tutorial for Excel users. QueryCell permite a utilização de SQL no Excel.

Aproveitem a leitura, porque é interessante.

2009-11-09

image

2009-11-04

Há dias um assíduo leitor do EXCELer, enviou-me um link relacionado com o título do presente post.

Pelo seu interesse, aqui o deixo, para vosso conhecimento:

Aprenda a usar mapas e coordenadas no Excel

2009-10-01

image

2009-09-20

Numa das minhas deambulações pelos excelentes bloggers do Excel, fui parar ao blog do MVP Glenn Lloyd - “General Office topics”: http://argeeoffice.spaces.live.com/.

Aí vi a alusão a um link para Peter Noneley's site, que contém uma ferramenta de trabalho para o Excel, denominada “Dicionário de Funções do Excel”. Como o download é grátis, aqui deixo a referência a este link, uma vez que, tenho a certeza, vão gostar do que vão ver.

2009-08-18

Se pretendermos colocar o resultado total, por exemplo de uma soma, no rodapé de uma impressão, podemos utilizar o seguinte código:

Private Sub Workbook_BeforePrint(Cancel As Boolean)

    Dim wkSht As Worksheet

    For Each wkSht In Me.Worksheets

        With wkSht.PageSetup
            .CenterFooter = "TOTAL: " & Format(Range("M55").Value, " #,##0.00")
        End With

    Next wkSht

End Sub

Nota: Este código deverá estar no workbook module (Este Livro ou This Workbook)

 

Tópicos relacionados:

2009-08-15

A propriedade PageSetup é utilizada para obter o objecto PageSetup, o qual contém todos os atributos do setup de uma folha – margem esquerda, fim de página, tamanho da folha, cabeçalho, rodapé, etc.

Exemplo:

 
'---------------------------------------------------------------------------------------
' Procedure : Workbook_BeforePrint
' DateTime  : 15-08-2009 00:15
' Author    : JorgeRod
' Purpose   :
'---------------------------------------------------------------------------------------
'
Private Sub Workbook_BeforePrint(Cancel As Boolean)
 
    Worksheets("Folha1").PageSetup.LeftHeader = Format(Date, "d mmmm,yyyy")
 
End Sub
 
 
Tópicos relacionados: 

 

2009-05-19

Se pretendermos que o tabulador de uma Worksheet obtenha o nome que colocarmos em determinada célula, podemos utilizar o seguinte código ( créditos para Juan Pablo Gonzalez ):

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Address = "$A$1" Then Sh.Name = Target
End Sub

E se pretendermos fazer exactamente o contrário, ou seja, obter numa determinada célula o nome do tabulador?

Então, aproveitando o código anterior, podemos alterá-lo para que o resultado seja o pretendido:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Range("$A$1") = Sh.Name
End Sub

2009-03-23

Há dias, num grupo de discussão de Excel, foi colocada a seguinte questão (adaptada):

Pressupostos:
- Tenho uma folha de cálculo "Clientes" com uma lista de clientes;
- Tenho uma folha de cálculo "Modelo" que é um modelo;

 
Problema:
- preciso criar folhas de cálculo iguais à folha "Modelo", mas com os nomes constantes da lista de clientes da folha "Clientes".

Alguém me pode ajudar?

 

Tomemos o seguinte exemplo ilustrativo:

O que temos:

a) Uma folha, denominada “Modelo”

image

b) Uma folha, denominada “Clientes”

image

O que pretendemos, será ter tantas folhas, quantos os nomes que se encontram na folha Clientes, com o nome de cada um deles no tabulador, mas com o conteúdo da folha “mestra” - “Modelo”.

Para uma melhor ilustração, suponhamos que dos 18 nomes, apenas pretendemos obter 6 folhas (de A1:A6), ou seja, de “Manuel a Jorge”. Então, o resultado seria:

 

image

Ou seja, todas as 6 folhas, criadas e já renomedas com os nomes pretendidos, teriam como conteúdo, o conteúdo da folha “Modelo”.

O Código, que executaremos em primeiro lugar e que dará para criar o número de folhas pretendido:

Private Sub Copia_Modelo()

    Dim sNum As Integer
    On Error Resume Next

    sNum = InputBox("Quantos Clientes?")

    For i = sNum To 1 Step -1
        
        Sheets("Modelo").Select
        Sheets("Modelo").Copy Before:=Sheets(1)

    Next

    Sheets("Clientes").Select
End Sub


Agora o código que executaremos em 2º lugar e que renomeará as folhas criadas anteriormente, com os nomes correspondentes ao número de clientes que estabelecemos com o Código anterior:

Private Sub Renomear_Folhas()


    On Error Resume Next


    For i = 1 To Worksheets.Count - 2
        Sheets(i).Name = Worksheets("Clientes").Cells(i, 1).Value
    Next i


End Sub

 

Por último, o Código que, por uma questão de comodidade, dá para apagar todas as folhas anteriormente criadas e renomeadas, ficando sempre e só, as folhas “Modelo” e “Clientes”:

Sub Delete_Sheets()


    Dim sNum As Integer
    On Error Resume Next

    Application.DisplayAlerts = False

    sNum = Worksheets.Count - 2

    For i = sNum To 1 Step -1
        Sheets(1).Select
        Sheets(1).Delete
    Next


    Application.DisplayAlerts = True

    Sheets("Modelo").Select

End Sub


Nota final: As folhas “Modelo” e “Clientes”, deverão ficar sempre na sequência em que se encontram no exemplo, ou seja, as duas últimas, à direita.

 

Tópicos relacionados:

2009-03-14

A pergunta original, continha algo diferente, ou seja, dependia o resultado com base na junção de 2 critérios: freguesia e sexo:

“Gostaria de saber qual a fórmula  para calcular o valor do peso do sexo masculino da freguesia de Bustos que mais se repete”.

Neste caso e para o resultado desejado, então o Código, poderia ser assim, tomando como células de controlo as células G2 (sexo) e H2 (Freguesia), como no exemplo do post anterior:

'----------------------------------------------------------
' Procedure : Validar_Click
' DateTime  : 14-03-2009 18:51
' Author    : Jorge Rodrigues
' Purpose   : Atribuir valores no Range L1:L20 retirados
'           : do Range D8:D17, a que correspondam,
'           : no Range C8:C17, o nome da Freguesia em H2
'           : e Sexo (M/F) em G2
'----------------------------------------------------------
'
Private Sub Validar_Click()
    Dim spRange As Range
    Dim cell As Range
    Dim nome As Variant
    Dim nome1 As Variant
    Dim nome2 As Variant
    Dim number As Integer

    number = 0
    nome = Range("H2").Value
    nome1 = Range("G2").Value
    nome2 = nome & nome1
    Range("L1:L20").ClearContents

    Set spRange = Range("C8:C17")
    For Each cell In spRange
        number = number + 1
        If cell & cell.Offset(0, -1) = nome2 Then
            Range("L" & number).Value = cell.Offset(0, 1)
        End If
    Next cell

End Sub

 

Já agora e para não aparecer, porventura, aquela sigla indesejável (#N/D), em I2, caso não exista qualquer repetição de valor, de acordo com os dois critérios, então a fórmula naquela célula, poderia ser assim:

=SE(É.ERRO(MODA(L1:L20));"";MODA(L1:L20))

Tópicos relacionados:

Há dias, colocaram-me, por e-mail, a seguinte questão (adaptada), que, igualmente foi posta num grupo de discussão:

“Gostaria de saber qual a fórmula  para mostrar o valor do peso da freguesia de Bustos que mais se repete (função estatistica Moda() [ing. Mode()])?


Exemplo:


Nome Sexo Freguesia Peso
   A        F         OIà      12
   B       M         BUSTOS 11
   C        F         OIà       12
   D       M         BUSTOS 11
   E        F         OIà       13
   F        M         BUSTOS 14
   G        F         OIà       15
   H       M         BUSTOS  16
   I         F         OIà        17
   J       M          BUSTOS 18”

 

Teríamos, então, como resposta para BUSTOS, o valor 11.

 

Ilustrando o exemplo:

 

image

Ora, o peso que mais se repete na Freguesia de BUSTOS, é o 11.

image

 

O que se pretende, assim, é coligir, em primeiro lugar, todos os valores de peso a que corresponda a Freguesia BUSTOS, ou seja:

11, 11, 14, 16, 18.

Podemos, então, escolher como fonte, onde serão colocados tais valores, o Range L1:L20.

Assim, em I2, colocaremos a seguinte fórmula, utilizando a função MODA():

=MODA(L1:L20)

 

Resta-nos, agora, criar o código, para colocar, dentro desse Range – L1:L20, os valores que tenham correspondência com a Freguesia pretendida, no caso do exemplo, BUSTOS, o que daria os seguintes dados:

image

 

Cria-se o Botão de Comando, a que, no caso do exemplo, daremos o nome de VALIDAR:

image

E, se seguida, atribuiremos a esse Botão de Comando - VALIDAR, o seguinte Código:


'----------------------------------------------------------
' Procedure : Validar_Click
' DateTime  : 14-03-2009 18:51
' Author    : Jorge Rodrigues
' Purpose   : Atribuir valores no Range L1:L20 retirados
'           : do Range D8:D17, a que correspondam,
'           : no Range C8:C17, ao nome da Freguesia BUSTOS
'----------------------------------------------------------
'
Private Sub Validar_Click()
    Dim spRange As Range
    Dim cell As Range
    Dim nome As Variant
    Dim number As Integer

    number = 1
    nome = Range("H2").Value
    
    Range("L1:L20").ClearContents
    
    Set spRange = Range("C8:C17")
    For Each cell In spRange
        number = number + 1
        If cell = nome Then
            Range("L" & number).Value = cell.Offset(0, 1)
        End If
    Next cell
    
End Sub


De notar que, como atribuímos para identificar o nome da Freguesia, a célula H2, então, com a simples modificação do conteúdo desta célula, para, por exemplo, OIÃ, que se encontra na tabela acima ficaríamos a saber, igualmente, o valor de peso que mais se repete nesta Freguesia, que seria o valor 12.

image

 

Tópicos relacionados:

2009-02-24

Há dias, num grupo de discussão, foi colocada a seguinte questão:

“Nao sei porque o meu Excel 2003 apresenta as linhas e as colunas "numeradas"
com números. Quando deviam ser as colunas com letras. Como rectifico isso ?”

Logo de seguida, quem colocou a questão, deu, igualmente, a resposta:

“Ok, ja descobri.


1. Abra o Excel
2. Clique em ferramentas
3. Clique em opções
4. Na guia GERAL desmarque a opção "Estilo de referência R1C1"

 

A imagem do “problema”:

 

Era assim:

image

E passou a ficar assim:

image

Mas, depois de se ter acedido como acima foi descrito:

image

Tudo voltou ao normal…

Certo!!!

Mas, já agora, convinha dizer mais qualquer coisa acerca do chamado estilo de referência “R1C1” ou, á portuguesa, “L1C1”, em que R quer dizer “Row” ou L “Linha” e C quer dizer “Column” ou “Coluna”.

Então, o melhor é ler o que já muito se escreveu sobre este assunto.

Assim, deixo aqui uns links, para, quem tiver interesse, ter uma melhor compreensão.

2009-01-20

Há dias, fizeram-me a seguinte pergunta (adaptada):


"O que preciso quando clico no comandButton, é que me forneça o menor valor da coluna C, mas em vez de aparecer só o valor 12.2 , apareça igualmente HE 100 AA, ou seja, o conteúdo correspondente na coluna B, na mesma linha."


Exemplo:

 

image

 

Resultado pretendido:

image

 

O Código (exemplo):

Private Sub CommandButton1_Click()
    Dim oRg As Range, iMin As Variant

    Set oRg = Range("C10:C100")

    'Para encontrar o valor mínimo
    iMin = Application.Min(oRg)

    'Apresenta o valor correspondente ao valor mínimo,
    'e na célula à esquerda, o conteúdo correspondente

    With Selection

        MsgBox "Valor encontrado: " & ActiveCell.Value & vbCrLf & _
        "Valor correspondente: " & ActiveCell.Offset(0, -1).Value

    End With

End Sub