Perguntas[excel](coding)

Tenho ajudado meu departamento de contabilidade, e eles dão horas extras para quem trabalha em turnos extras. Se você trabalhar 5 ou 7 dias seguidos em um determinado turno, você ganha um número extra de horas além das horas trabalhadas. Então, você pode ganhar 5*10 mais 4 horas adicionais, totalizando 54 horas, em vez de 50.

O problema é que a planilha utilizada registra os turnos de cada mês e soma os dias. Basicamente, em julho, por exemplo, uma pessoa trabalhou 5 e 7, então a célula de julho é marcada como "=5+7". Precisamos saber tanto o número de dias no turno quanto quantas horas de bônus conceder ao longo do ano.

Sei que existe uma maneira melhor de contabilizar isso, mas estamos presos à planilha antiga porque é assim que a contabilidade exige. Existe uma maneira de contar as vezes que uma célula contém 7 ou 5 e adicionar isso a uma nova coluna?

Uma determinada linha pode ter uma aparência semelhante a esta para um funcionário:

Então, para esse funcionário, eu gostaria de três colunas que exibissem o total de dias (31), o número de turnos de 5 dias consecutivos (2) e turnos de 7 dias consecutivos (3).

Atualmente, eles estão fazendo isso manualmente, mas é muita coisa para fazer manualmente para os mais de 100 funcionários, e erros são cometidos. Também ficou claro que eles querem fazer os cálculos dessa forma em cada célula, então não consigo contornar isso. Tentei usar a função Cell, mas não sei se funciona para várias células ao mesmo tempo. Acho que poderia fazer um countif e usar uma função cell para analisar todos os 12 meses, mas isso parece deselegante.

Existe uma maneira de copiar e colar em um comentário de célula? Só é possível fazer isso a partir da área de transferência, não de uma referência de célula ou intervalo. Ou existe uma maneira de usar o VBA para acessar as informações na área de transferência e depois colar?

Sub fillcomment()
    
    Sheets("Calendar").Select
    Application.Goto Reference:=Range(Range("Calendar!calendarRef").value)
    Application.CutCopyMode = False
    
    Sheets("FORMULAS").Select  'Surname
     Range("C8").Select
    Selection.Copy
    
    Sheets("Calendar").Select  'paste surname
    ActiveCell.Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
      Application.CutCopyMode = False
   
  
    'select and paste comment
        Sheets("Calendar").Select
        ActiveCell.ClearComments
        ActiveCell.AddComment
        ActiveCell.Comment.Visible = False
        ActiveCell.Comment.Text Text:=Range(Range("FORMULAS!calcom").value)

End Sub

Preciso das informações da variável do intervalo chamado "calcom" ou, se for mais fácil, das células concatenadas no B6 e copiar e colar no comentário

Considerando a tabela acima, qual é uma fórmula do Excel que me ajude a contar quantas colunas têm pelo menos uma célula maior que 1? Gostaria de ter uma fórmula que funcionasse se eu tivesse 1.000 colunas.

Neste caso, a resposta deve ser 2 (colunas b e c).

Eu estava tentando usar diferentes combinações de COUNTIFS e SUM, mas não encontrei a correta. Até agora tentei

=SUM(--(MAX(B2:B4)>1), --(MAX(C2:C4)>1), --(MAX(D2:D4)>1))

Isso funciona, mas não é viável com 1000 colunas.

Tenho uma coluna com datas no formato dd-mm-aaaa.

Estou tentando substituir "-" por "." para que todas as datas selecionadas pareçam 01.01.2025 em vez de 01-01-2025.

Funciona quando uso ctrl+H em uma planilha do Excel, mas o código VBA gravado não funciona.

Selection.Replace What:="-", Replacement:=".", LookAt:=xlPart, _
  SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
  ReplaceFormat:=False

Tenho aqui uma caixa de listagem que está funcionando bem com o código abaixo.

Caixa de Listagem 1

Código completo atualizado:

Option Explicit
Dim bInit As Boolean ' ** module-scoped variable

Private Sub UserForm_Initialize()
    bInit = True  ' ** set UserForm_Initialize mode **from Taller
    clearAll
    Me.cmbName.Value = "Nory"
    Dim ws As Worksheet: Set ws = Worksheets("Sheet1")
    Dim i As Long
    Dim arr: arr = ws.Range("B1").CurrentRegion.Value
    Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")

    For i = 2 To UBound(arr)
        dict(arr(i, 2)) = arr(i, 1)
    Next
   ' ***
     If dict.exists(Me.cmbName.Value) Then
        Me.cmbTeam.Value = dict(Me.cmbName.Value)
        If Not cmbTeam.Value = "" And Not cmbDate.Value = "" And Not cmbName.Value = "" Then
            Team
            forListBoxUpdate 'calling forListBoxUpdate
            forDateCombobox 'filling in date dropdowns
            forNamesCombobox 'filling in names combobox as long as 3 comboboxes are not blank
        Else
            Team 'calling Team dropdowns in case team is blank or default cmbName value has no team
            cmbDate.Value = ""
            cmbName.Value = ""
        End If
    Else
            Team 'calling Team dropdowns in case team is blank or default cmbName value has no team
            cmbDate.Value = ""
            cmbName.Value = ""
    End If
    bInit = False  ' ** reset **from Taller
End Sub

Private Sub cmbDate_Change()
    If Not cmbTeam = "" And Not cmbName = "" Then
        forListBoxUpdate 'calling to show info if team and name not blank
        forNamesCombobox 'filling in cmbname dropdowns
    Else
        cmbDate.Clear 'if cmbteam and cmbname blank, cmdate should also be blank
    End If
End Sub

Private Sub cmbName_Change()
    forListBoxUpdate
End Sub

Private Sub cmbTeam_Change()
    cmbDate.Value = ""
    'cmbName.Value = "" 'issue, during initialize, default cmbname is removed.
    clearAll 'used this instead
    forDateCombobox 'fills in date dropdowns
End Sub



Sub forListBoxUpdate() 'to show info from sheets and to be called after the 3 comboboxes are filled
    Dim ws As Worksheet, colList As Collection
    Dim arrData, arrList, i As Long, j As Long
    
    Set colList = New Collection
    Set ws = Worksheets("Sheet2")
    arrData = ws.Range("A1:C" & ws.Cells(ws.Rows.count, "A").End(xlUp).Row)
    
        For i = 2 To UBound(arrData)
            If Format(arrData(i, 1), "mmmm yyyy") = Me.cmbDate.Value And arrData(i, 3) = Me.cmbName.Value Then
                colList.Add i, CStr(i)
            End If
    Next

    ReDim arrList(1 To colList.count + 1, 1 To UBound(arrData))
    For j = 1 To 3
        arrList(1, j) = arrData(1, j) ' header
        For i = 1 To colList.count
                arrList(i + 1, j) = arrData(colList(i), j)
        Next
    Next

    With Me.ListBox1
        .Clear
        .ColumnCount = UBound(arrData, 2)
        .list = arrList
    End With
    
    
    labelCount.Caption = ListBox1.ListCount - 1
End Sub

Sub clearAll() 'to clear comboboxes except teams
    If Not bInit Then cmbName.Value = ""  ' ** doesn't run when calling from UserForm_Initialize **from Taller
    cmbDate.Clear
    cmbName.Clear
    'cmbName.Value = ""
    ListBox1.Clear
End Sub

Sub Team() 'for adding the teams dropdown in cmbTeam
    clearAll
    Dim ws As Worksheet, _
        Dic As Object, _
        rCell As Range, _
        Key
    
    Set ws = Worksheets("Sheet1")
    Set Dic = CreateObject("Scripting.Dictionary")
    
    
    For Each rCell In ws.Range("A2", ws.Cells(Rows.count, "A").End(xlUp))
            If Not Dic.exists(rCell.Value) And Not rCell = "" Then
                Dic.Add rCell.Value, Nothing
            End If
    Next rCell
    
    For Each Key In Dic
        cmbTeam.AddItem Key
    Next
End Sub

Sub forNamesCombobox() 'for adding the names dropdown in cmbName
Dim ws As Worksheet, _
    Dic As Object, _
    rCell As Range, _
    Key

Set ws = Worksheets("Sheet1")
Set Dic = CreateObject("Scripting.Dictionary")


For Each rCell In ws.Range("B2", ws.Cells(Rows.count, "B").End(xlUp))
        If Not Dic.exists(rCell.Value) And rCell.Offset(0, -1) = cmbTeam.Value Then
            Dic.Add rCell.Value, Nothing
        End If
Next rCell

For Each Key In Dic
    cmbName.AddItem Key
Next
End Sub

Sub forDateCombobox() 'for adding the date dropdown in cmbDate
            Dim date1 As Variant
            Dim date2 As Variant
            date1 = Format(Now, "mmmm yyyy")
            date2 = Format(DateAdd("m", -1, CDate(date1)), "mmmm yyyy")
            
            With cmbDate
            .Clear
            .AddItem Format(date2, "mmmm yyyy")
            .AddItem Format(date1, "mmmm yyyy")
            .Value = Format(date1, "mmmm yyyy")
            End With
            
End Sub

Folha1

Folha2

Agora, durante o evento de alteração da caixa de combinação de equipes, gostaria que a caixa de combinação de nomes fosse limpa ("Nory" ou qualquer valor para cmbNamedeve ser removido e eliminado).

A partir do código acima, o snippet do evento de alteração de equipes é:

Private Sub cmbTeam_Change()
    cmbDate.Value = ""
    'cmbName.Value = "" 'issue, during initialize, default cmbname is removed.
    clearAll 'used this instead
    forDateCombobox 'fills in date dropdowns
End Sub

Sub clearAll() 'to clear comboboxes except teams
    If Not bInit Then cmbName.Value = ""  ' ** doesn't run when calling from UserForm_Initialize **from Taller
    cmbDate.Clear
    cmbName.Clear
    'cmbName.Value = ""
    ListBox1.Clear
End Sub

Mesmo que eu insira cmbName.Value = ""em sub clearAll, durante a inicialização, "Nory"ele será removido, o que eu não quero que seja removido na inicialização.

Como corrigir o código acima, onde durante a inicialização, "Nory" permanecerá, assim como Equipe e Data, e quando houver uma mudança de Equipe, as caixas de combinação de Data e Nome ficarão em branco.

Sua ajuda é muito apreciada.

Folha1

Erro durante a inicialização:

O que deve ser corrigido no código abaixo para obter o nome exato da equipe preenchido no Me.cmbTeam.Value (combobox) se o membro da equipe estiver correspondendo na coluna 2.

Private Sub UserForm_Initialize()
    Me.cmbDev.Value = "Nory"

    Dim ws As Worksheet: Set ws = Worksheets("Sheet1")
    Dim i As Long
    Dim arr: arr = ws.Range("B1").CurrentRegion.Value
    Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")
    Dim teamName As Variant

    For i = 2 To UBound(arr)
        dict(arr(i, 2)) = Empty
    Next
   ' ***
   
    If dict.exists(Me.cmbDev.Value) Then
        teamName = Application.VLookup(Me.cmbDev.Value, ws.Range("B1").CurrentRegion.Value, 1, False)
        Me.cmbTeam.Value = teamName 'should get a result of George
    Else
        Me.cmbDev.Value = ""
        Me.cmbTeam.Value = ""
    End If

End Sub

Às vezes, o Excel se recusa terminantemente a criar gráficos de dispersão XY corretamente. Adota um formato padrão que plota linhas como linhas com legendas "série 1", "série 2" e "série 3", apesar de identificar os cabeçalhos das colunas, que ele cola no eixo X! A ideia era ser um gráfico de dispersão XY com pontos unidos por retas (foi o que foi selecionado), mas não foi entregue.

O problema que vejo intermitentemente parece estar possivelmente relacionado a este tópico anterior sobre gráficos de dispersão XY, onde o eixo X é, na verdade, um formato de tempo em tempo . Mas não vejo razão para que isso se aplique aqui. Os dados são todos claramente números inteiros.

Sei que se títulos ou dados estiverem malformados (contiverem NANs, INFS ou espaços em branco), isso acontecerá (justo). Mas às vezes, o erro ocorre sem motivo aparente quando são fornecidos dados numéricos perfeitamente válidos. Este é um exemplo particularmente pequeno que encontrei hoje, reduzido a um MRE que ainda falha. Normalmente, há muito mais colunas e linhas. Apresentado abaixo como um arquivo CSV:

log2N,N,valid,solutions
3,8,35,10
4,16,1365,119
5,32,31465,852

Se você copiar e colar este conjunto de dados aparentemente inocente no Excel e selecioná-lo, tente plotar o gráfico XY de N, soluções válidas e versus Log2N, e ele fará aquele gráfico insano e inútil. Na verdade, ele só fará um gráfico de dispersão XY adequado se você selecionar as colunas A e B para Log2Ne N. Uma vez que o formato correto do gráfico XY seja estabelecido, ele permitirá que o intervalo de dados seja expandido. Selecione os dados do gráfico e edite "=Sheet1!$A$1:$B$4"para, "=Sheet1!$A$1:$D$4"e o formato permanece como XY, mas isso é um PITA.

Estou usando o Excel 2021 MS Office Pro. Ele se identifica como: Microsoft® Excel® 2021 MSO (Versão 2503 Build 16.0.18623.20178) 64 bits

Tenho observado esse comportamento em todas as versões recentes do Excel desde 2007. Esta é apenas a primeira vez que encontro um pequeno MRE em que o comportamento se manifesta tão claramente. Acredito que seja um bug nas suposições do Excel sobre dados acionados pela faixa dinâmica, mas ficaria muito grato se alguém pudesse encontrar uma maneira de contornar esse problema para que a criação de gráficos de dispersão XY funcionasse corretamente em qualquer seleção de dados numéricos válidos, tratando a primeira coluna como o eixo X e a primeira linha como legendas para as linhas do gráfico.

Ou se for reproduzível e for um bug, como reportá-lo à MS para que seja corrigido.

Embora eu tenha uma solução alternativa, prefiro uma solução permanente. Se houver algo sutilmente incorreto nos meus dados que eu não tenha percebido, por favor, aponte. Obrigado.

Tenho uma tabela na qual quero contar quantas ocorrências de valores existem em duas colunas, com base no valor de uma terceira, e para facilitar a leitura, quero poder ver esses valores exclusivos com a contagem ao lado deles.

Por exemplo:

Quero ver quantas vezes o nome de cada pessoa aparece, com base no valor da terceira coluna. Como este é um documento fluido, as únicas colunas atualizadas são essas três, então quero uma nova coluna com os valores únicos de A e B, se o valor de C = 2.

O que eu descobri até agora é:

=UNIQUE(VSTACK(A2:A100,B2:B100))

O que funciona bem, mas contar as ocorrências com base no valor de C é onde estou tropeçando. Eu tentei

=COUNTIFS(A2:B100,E2,C2:C100,">1")

Mas ele continua apresentando a mensagem "Um valor é o tipo de dado errado". Eu me certifiquei de que as colunas são do tipo correto, por exemplo, C é um número, e também copiei os valores da função UNIQUE para uma coluna separada, para que ele não tente empilhar funções.

Tentei outras funções como, COUNT(UNIQUE(FILTER())mas isso não elimina as contagens inválidas, e outra solução, não sei se funciona porque não consigo entender a função:

=SUM(--(FREQUENCY(IF(C2:C18=2,MATCH(A2:A18,B2:B18,0)),ROW(A2:B18)-ROW(A2)+1)>1))

Tenho uma tabela dinâmica que retorna o mínimo de uma data por categoria. Posso ter um campo calculado que funcione assim: data - Hoje?

Tentei um campo calculado, mas não consegui selecionar o mínimo da data para então menos () Hoje....

É possível construir uma matriz no Excel da seguinte maneira:={1,2;3,4}

Usando esse método, quero calcular o inverso de uma matriz 5 x 5 de uma série de elementos em uma linha, em vez de distribuídos como em uma matriz 5 x 5 verdadeira em 5 linhas e 5 colunas.

É possível referenciar as células em sua localização atual para construir uma matriz 5 x 5 dentro da função MINVERSE, como =MINVERSE(A1:A5;A6:A10...A21:25)?