EXCELer

Utilização de várias funções numa fórmula.

noreply@blogger.com (JRod - PORTUGAL) — Sun, 28 Jun 2015 22:40:00 +0000

Há dias, recebi uma mensagem a questionar sobre o seguinte:

"Viva, gostava de poder obter a vossa ajuda para resolver esta situação:
A 22
B 23
C 48
D 2
E 13
F 21
G 51
Necessitava de utilizar uma função para obter o maior valor da coluna B, ao qual devia fazer corresponder a referencia da coluna A. Ou seja neste exemplo, a função deveria dar o resultado G 51."

Foi então criada uma fórmula que contém as seguintes funções: DESLOCAMENTO(), INDIRECTO(), ENDEREÇO(), CORRESP(), MAIOR(), LIN(), COL() e o operador "&" - substituto da função CONCATENAR(), para dar o resultado pretendido.

O resultado foi o seguinte:

A fórmula em português: =DESLOCAMENTO(INDIRECTO(ENDEREÇO(CORRESP(MAIOR(B1:B7;1);B1:B7;0)+LIN(B1:B7)-1;COL(B1:B7)));0;-1)& " " & MAIOR(B1:B7;1)

A fórmula em inglês: =OFFSET(INDIRECT(ADDRESS(MATCH(LARGE(B1:B7;1);B1:B7;0)+ROW(B1:B7)-1;COLUMN(B1:B7)));0;-1)& " " & LARGE(B1:B7;1)

O procedimento Sub Workbook_Open(), com particularidades

noreply@blogger.com (JRod - PORTUGAL) — Sun, 16 Mar 2014 15:09:00 +0000

Há dias, tive necessidade de configurar o procedimento Workbook_Open() com algumas particularidades.

Deixo aqui o exemplo:

'---------------------------------------------------------------------------------------
' Procedure : Workbook_Open
' Author    : JRod
' Date      : 16/03/2014
' Purpose   : Activa determinada folha, tira a barra com o nome das folhas e abre folha sempre com o zoom a 85%
'---------------------------------------------------------------------------------------
'
Private Sub Workbook_Open()

    'Activa a folha PROGRESSIVO
    Sheets("PROGRESSIVO").Activate

    'Tira a barra com os nomes das folhas...
    ActiveWindow.DisplayWorkbookTabs = False

    'Abre s folha (planilha)"PROGRESSIVO" sempre com o zoom a 85%
    ActiveWindow.Zoom = 85

End Sub

As funções SOMA() e CONTAR.SE.S()

noreply@blogger.com (JRod - PORTUGAL) — Tue, 11 Mar 2014 02:34:00 +0000

Se pretendermos ver quantas vezes determinado nome é referido num range, que se encontra dependente de um outro, como no exemplo que a seguir se pode ver:

e partindo do princípio que o range da coluna C não pode conter células em branco, uma vez que não fará sentido, pois é para colocar lá um nome, podemos utilizar a seguinte fórmula, como matriz, sendo que a procura é baseada na célula C2 e a resposta dada (que contém a fórmula abaixo), na célula

E2:=SOMA(SE(C2=$B$5:$B$24;1/(CONTAR.SE.S($B$5:$B$24;C2;$C$5:$C$24;$C$5:$C$24));0))

Como matriz que é, a fórmula deverá ser inserida com CTRL + SHIFT + ENTER, para ficar assim:

{=SOMA(SE(C2=$B$5:$B$24;1/(CONTAR.SE.S($B$5:$B$24;C2;$C$5:$C$24;$C$5:$C$24));0))}

Congratulations 2011 Microsoft MVP!

noreply@blogger.com (JRod - PORTUGAL) — Sat, 01 Oct 2011 15:06:00 +0000

URRA!!!! URRA!!!! URRA!!!!! Amigos!!!! Estou, mais uma vez, orgulhoso com o facto de ter voltado a ser, em 2011 e pela 7ª vez consecutiva, agraciado com o Microsoft MVP Award !!!!

Obrigado, Cristina Gonzalez, pela confiança!!!! Bem haja e felicidades!!!!! Vou continuar, com todo o entusiasmo, a promover o EXCELer!!!

Obrigado, Bruno Castro, por me aturar sempre que preciso de algo novo para o EXCELer!!!!!

Obrigado a todos os EXCELerianos!!!!!! MUITO OBRIGADO!!!!!!

263: Excel–Guia rápido de referência–Excel 2010

noreply@blogger.com (JRod - PORTUGAL) — Sun, 31 Jul 2011 22:23:00 +0000

Para ver em formato PDF, aceder a:

Guia Rápido de referência - Excel 2010

262: Excel - Criar tabelas em Excel.

noreply@blogger.com (JRod - PORTUGAL) — Sun, 31 Jul 2011 20:31:00 +0000

Para saber como criar uma tabela em Excel, de maneira fácil. CLICAR AQUI

263: VBA - Criar e Enviar ficheiros em formato PDF a partir do Excel 2007 e Excel 2010

noreply@blogger.com (JRod - PORTUGAL) — Tue, 19 Jul 2011 00:41:00 +0000

Para saberem como fazer, vejam o texto escrito pelo Microsoft Excel MVP – Ron de Bruin:

Criar e enviar ficheiros em formato PDF

262: VBA - Filtrar e copiar para outra folha

noreply@blogger.com (JRod - PORTUGAL) — Mon, 18 Apr 2011 00:57:00 +0000

Exemplo para filtrar e copiar para outra folha:

Supondo que temos a seguinte tabela:

O que se pretende, é copiar para outra folha (ex. folha2) os registos que contenham apenas a sigla pretendida:

O exemplo de código possível:

Sub FiltrarECopiar()

Dim Msg As String

Sheets("Folha2").Select

Columns("A:C").Select

Selection.ClearContents

Sheets("Folha1").Select

Msg = InputBox(" Escreva as siglas para filtrar")

Msg = UCase(Msg)

Range("C2").AutoFilter Field:=3, Criteria1:=Msg

Range("A2").Resize(Range("A" & Rows.Count).End(xlUp).Row - 1).EntireRow.Copy _

Destination:=Sheets("Folha2").Range("A1")

ActiveSheet.ShowAllData

End Sub

Créditos para o MVP J.E. McGimpsey

261: Excel–Funções SOMARPRODUTO E SUMIF

noreply@blogger.com (JRod - PORTUGAL) — Fri, 29 Oct 2010 20:42:00 +0000

Há dias, no Fórum do Exceler, colocaram a seguinte questão:

“Eu tenho uma tabela onde a Coluna A4:A19 contem datas, e as colunas B4:G19 tenho dados.E no B2"Data" critério.
Eu quero SUMIF onde as colunas B4:G19 sejam somadas quando o critério for verdade.
Eu fiz =SUMIF(A4:A19,B2,B4:B19) mas esta somando somente a Coluna B ao invés de B4:G19
Por favor veja a foto em anexo.

A resposta possível poderá ser:

Uma das hipóteses, utilizando a função SUMIF (SOMA.SE),será a seguinte fórmula:

=SUMIF(A4:A19,B2,B4:B19)+SUMIF(A4:A19,B2,C4:C19)+SUMIF(A4:A19,B2,D4:D19)+SUMIF(A4:A19,B2,E4:E19)+SUMIF(A4:A19,B2,F4:F19)+SUMIF(A4:A19,B2,G4:G19)

A outra, melhor, na minha opinião, utilizando a função SUMPRODUCT ( SOMARPRODUTO), será:

=SUMPRODUCT((A4:A19=B2)*B4:G19)

Há mais opiniões? Serão, como sempre, muito bem vindas!

Tópicos relacionados:

260: Excel - MVP pelo 6º ano consecutivo!!!!! URRA!!!!!

noreply@blogger.com (JRod - PORTUGAL) — Sat, 02 Oct 2010 14:41:00 +0000

Recebi ontem a mensagem sobre a minha 6ª renomeação consecutiva como MVP Excel! Claro que estou satisfeitíssimo e vou tentar, como sempre, não desiludir a comunidade!!!

“Dear Jorge Rodrigues,
Congratulations! We are pleased to present you with the 2010 Microsoft® MVP Award! This award is given to exceptional technical community leaders who actively share their high quality, real world expertise with others. We appreciate your outstanding contributions in Excel technical communities during the past year.”

258: Excel – Curiosidades acerca do Excel 2010

noreply@blogger.com (JRod - PORTUGAL) — Tue, 27 Jul 2010 16:38:00 +0000

Para saber sobre funcionalidades descontinuadas e modificadas no Excel 2010, aceda a:

Conversão de ficheiros e compatibilidades 01

Para saber como adicionar ou remover suplementos no Excel 2010, aceda a:

Analisar dados

Tópicos relacionados:

Microsoft Office - Ajuda e Procedimentos do Excel 2010

257: Excel – Para leitura obrigatória…

noreply@blogger.com (JRod - PORTUGAL) — Mon, 26 Jul 2010 23:42:00 +0000

O site de David McRitchie é um local de consulta obrigatória, porque tem exemplos muito bons.

Ora vejam:

My Excel Pages -- David McRitchie

256: Excel – Transformar texto em data

noreply@blogger.com (JRod - PORTUGAL) — Mon, 26 Jul 2010 23:00:00 +0000

Tomemos, por exemplo, o seguinte:

O que se pretende, é criar uma fórmula que altere o texto de A1, para que as células B1 (com formatação de data personalizada, na forma aaaa-mm-dd) e B8 (sem formatação de data personalizada) se apresentem como na imagem.

Aqui fica, a título de exemplo, uma possível solução:

Em português: =DATA(DIREITA(A1;4);EXT.TEXTO(A1;3;2);ESQUERDA(A1;2))

255: Excel – A Função CONTAR.SE() – versão 2003/2007 ou CONT.SE() – versão 2010. Função SOMA() como Array {}.

noreply@blogger.com (JRod - PORTUGAL) — Sat, 17 Jul 2010 19:10:00 +0000

A propósito deste título, apresento 2 possíveis soluções para esta situação: Como saber quantas vezes o conteúdo de uma célula se encontra num determinado Range de células?

Vejamos o exemplo:

O que se pretende, é saber quantas vezes o número 3 (referenciado na célula A1) está contido no Range B1:B10. Ora, como se pode verificar, está contido por 3 (três) vezes.

Então,

1ª solução – Função CONTAR.SE() – versão 2003/2007 ou Função CONT.SE() – versão 2010:

=CONTAR.SE(B1:B10;A1)

=CONT.SE(B1:B10;A1)

2ª solução – Função SOMA(), como um Array {} (obtido com as teclas Ctrl + Shift + Enter):

{=SOMA((A1=B1:B10)*1)}

Tópicos relacionados:

254: Excel – Como criar listas no Excel 2003

noreply@blogger.com (JRod - PORTUGAL) — Tue, 22 Jun 2010 16:01:00 +0000

Para saber como criar listas no Excel 2003, aceda ao seguinte link:

Criar Listas no Excel 2003

253: Excel – Grupos Públicos de Discussão Microsoft

noreply@blogger.com (JRod - PORTUGAL) — Sun, 09 May 2010 18:39:00 +0000

Como já deve ser do vosso conhecimento, está em curso o processo de desactivação dos grupos públicos de discussão da Microsoft, através da migração dos denominados “Microsoft NNTP (Network News Transfer Protocol)”, para os fóruns da Microsoft (Web-based Microsoft community forums).

Para saberem mais detalhes sobre esta notícia, poderão aceder a:

http://www.microsoft.com/communities/newsgroups/default.mspx

Este site estará, brevemente, disponível na língua portuguesa.

252: Excel – MVP Open Day 2010 – 12/03/2010 - MADRID

noreply@blogger.com (JRod - PORTUGAL) — Sun, 21 Mar 2010 03:56:00 +0000

Mais uma vez, fui ao MVP Open Day!… Um espectáculo, como sempre!!!!!

E vi, pela primeira vez, o Power Pivot Add-In a funcionar:

251: Excel – As funções MÁXIMO(), ÍNDICE() e CORRESP()

noreply@blogger.com (JRod - PORTUGAL) — Sun, 21 Mar 2010 03:34:00 +0000

Há dias, no fórum do Exceler, foi colocada a seguinte questão (adaptada):

“ Tenho a seguinte tabela:

Queria que na coluna V.Max. e Loja, a fórmula fizesse o seguinte:

Procurasse o valor máximo de um determinado nome (ex. João) e juntasse a loja a que correspondesse esse valor máximo: ou seja, onde está João, escolheria das 3 linhas onde o nome se encontra, o valor máximo, neste caso, 13 e a Loja que lhe corresponde, ou seja, Loja2.”

Então, o que pretenderíamos, seria o seguinte:

Em G3, digitamos o nome pretendido (no exemplo, João);

Em D3, digitamos a seguinte fórmula de matriz (Array):

{=MÁXIMO(SE($A$3:$A$12=G3;$C$3:$C$12))}

Nota: O Array é conseguido com as { }, que se obtém através da combinação das teclas CTRL + SHIFT + ENTER.

Para finalizar, em E3, digitamos a seguinte fórmula:

=SE(G3="";0;ÍNDICE($B$3:$C$12;CORRESP(D3;$C$3:$C$12;0);1))

O resultado está, então, à vista!…

Tópicos relacionados:

250: Excel – Mickey Mouse

noreply@blogger.com (JRod - PORTUGAL) — Mon, 01 Mar 2010 03:21:00 +0000

Uma pequena (grande) curiosidade:

Desenhar a cara do rato Mickey, para ficar assim:

Querem saber como? Então, acedam a: Rato Mickey e divirtam-se!

249: Excel – Entrada de dados duplicados.Validação. Função CONTAR.SE()

noreply@blogger.com (JRod - PORTUGAL) — Tue, 16 Feb 2010 15:58:00 +0000

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

“

Será que é possível arranjar um aviso de cada vez que se colocar informação duplicada numa coluna.

Ex : Numa folha de Excel com uma tabela onde apliquei filtros nas colunas.
         A                 B
1     12456           José
2     12354          Maria
3     12456         Manuel

Portanto, ao introduzir o nº em A3 aparecer um aviso de que aquele numero já estava escrito.

“

O que se pretende será, então o seguinte:

Para obter o resultado, podemos optar pela denominada “Validação de Dados” e criar uma fórmula que nos permita saber se o valor digitado já consta no Range estabelecido e, nesse caso, apresentar a mensagem de aviso.

Seguem-se os passos a dar:

Marca-se o Range pretendido. No caso do exemplo, o Range será A1:A3, devendo iniciar-se a marcação sempre na célula de numeração mais baixa (ex: A1);
De seguida, clica-se no menu “DADOS” e escolhe-se o item “Validação de Dados”.
De seguida, escolhe-se o tabulador “Definições. É aqui que iremos escolher o critério de validação, no exemplo: “Personalizar” e inserirmos a fórmula:

=CONTAR.SE($A$1:$A$3;A1)=1
Depois, para termos o aviso de erro, escolhemos o tabulador com o mesmo nome

Assim, se digitarmos o valor duplicado em A3, irá aparecer a mensagem acima apresentada.

De notar que, na mensagem há duas opções relevantes: Ou para continuar (SIM) ou para não continuar (NÃO).

Se clicar,os em SIM:

o resultado será o da aceitação do valor duplicado

Se for NÃO, o resultado será a activação da célula em causa, para permitir a correcção

Tópicos relacionados:

248: Excel – As funções SE() e OU()

noreply@blogger.com (JRod - PORTUGAL) — Sun, 24 Jan 2010 17:08:00 +0000

Num grupo de discussão, foi colocada a seguinte questão (adaptada):

“

Tenho uma questão no Excel:
Numa coluna tenho várias leituras, uma por cada dia:

      Dia Leitura Consumo Dia
      1      150
      2      160              10
      3      165               5

O "problema" é que depois posso ter dia(s) sem leituras e isso baralha as contas do consumo:

      Dia Leitura Consumo Dia
      1      150
      2      160             10
      3      165              5
      4     -165
      5      180           180

Existe alguma maneira "simples" de dar a volta à questão? É que posso ter um dia ou vários sem serem preenchidos (a solução básica é dizer ao utilizador quando não há leitura para copiar a anterior mas estava a ver se conseguia dar a volta à questão). Alguma ideia?

“

Bom, o que se pretende é:

Para se conseguir este efeito, reflectido na coluna C (Consumo/Dia), a partir dos dados inseridos na coluna B (Leitura), procurou-se criar uma tabela na coluna E (a partir de E2), como segue:

Esta tabela, na coluna E, serve para ir dando os valores acumulados diariamente, iniciando em E2, como sendo a leitura do final do mês anterior. A coluna B, será a coluna onde serão inseridos os valores correspondentes às leituras diárias. Assim, se, por exemplo, no dia 3 a leitura for zero (0) ou sem valor (como no dia 8), sendo o consumo do dia igualmente zero (0), a tabela terá que reflectir que o valor acumulado se mantém o mesmo do(s) dia(s) anterior(es) (no exemplo, 162). Então, no dia 4, ao fazer-se uma leitura, que será sempre superior ao valor acumulado, irá dar um valor de consumo igual à diferença entre o último dia com consumo e o presente (no exemplo, 8), passando, assim, na tabela de referência, de 162, para 170 (162 + 8 = 170).

Agora, vamos às fórmulas necessárias para se conseguir o resultado pretendido:

Na coluna C, começando em C3 e depois copiando a fórmula até ao dia pretendido (normalmente 30 ou 31 – no exemplo, apenas até C12 – dia 10):

=SE(OU(E2=FALSO;B3=0);0;B3-E2)

Na coluna E, começando em E3 e depois copiando a fórmula até ao dia pretendido (normalmente 30 ou 31 – no exemplo, apenas até E12 – dia 10):

=SE(B3=0;-(B3-E2);B3)

247: VBA – As Funções Date(), Time() e Format()

noreply@blogger.com (JRod - PORTUGAL) — Tue, 22 Dec 2009 16:29:00 +0000

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:

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:

246: Excel – Somar sob determinados critérios

noreply@blogger.com (JRod - PORTUGAL) — Sun, 13 Dec 2009 19:27:00 +0000

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:

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).

245: Excel – Localizar e Substituir

noreply@blogger.com (JRod - PORTUGAL) — Sat, 05 Dec 2009 18:46:00 +0000

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”:

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))

O resultado esperado seria 14.

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

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

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:

A Função INDIRECTO() - (Contextures.com)

244: Excel – Formatação de células

noreply@blogger.com (JRod - PORTUGAL) — Thu, 03 Dec 2009 01:43:00 +0000

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:

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

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:

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

=TEXTO(A1;"d-m")